diff options
Diffstat (limited to 'sql/Pg-upgrade-2.6.17-2.6.18.sql')
| -rw-r--r-- | sql/Pg-upgrade-2.6.17-2.6.18.sql | 96 |
1 files changed, 73 insertions, 23 deletions
diff --git a/sql/Pg-upgrade-2.6.17-2.6.18.sql b/sql/Pg-upgrade-2.6.17-2.6.18.sql index 2067074e..8144f1df 100644 --- a/sql/Pg-upgrade-2.6.17-2.6.18.sql +++ b/sql/Pg-upgrade-2.6.17-2.6.18.sql @@ -3,7 +3,6 @@ -- We are using standard postgresql names for the sequence for consistency as we move forward -- Do everything in a transaction in case it blows up -BEGIN; LOCK acc_trans in EXCLUSIVE mode; ALTER TABLE acc_trans ADD COLUMN entry_id bigint; CREATE SEQUENCE acctrans_entry_id_seq; @@ -15,42 +14,42 @@ ALTER TABLE acc_trans ADD PRIMARY key (entry_id); ALTER TABLE acc_trans ADD FOREIGN KEY (chart_id) REFERENCES chart (id); -- Start changing floats -ALTER TABLE acc_trans ALTER COLUMN amount TYPE numeric(10,2); +ALTER TABLE acc_trans ALTER COLUMN amount TYPE NUMERIC; -- This may break someone if they for some reason have an actual float type in the qty column ALTER TABLE invoice ALTER COLUMN qty TYPE numeric; ALTER TABLE invoice ALTER COLUMN allocated TYPE numeric; -ALTER TABLE invoice ALTER COLUMN sellprice TYPE numeric(10,2); -ALTER TABLE invoice ALTER COLUMN fxsellprice TYPE numeric(10,2); +ALTER TABLE invoice ALTER COLUMN sellprice TYPE NUMERIC; +ALTER TABLE invoice ALTER COLUMN fxsellprice TYPE NUMERIC; ALTER TABLE customer ALTER COLUMN discount TYPE numeric; -ALTER TABLE customer ALTER COLUMN creditlimit TYPE numeric(10,2); +ALTER TABLE customer ALTER COLUMN creditlimit TYPE NUMERIC; -ALTER TABLE parts ALTER COLUMN listprice TYPE numeric(10,2); -ALTER TABLE parts ALTER COLUMN sellprice TYPE numeric(10,2); -ALTER TABLE parts ALTER COLUMN lastcost TYPE numeric(10,2); +ALTER TABLE parts ALTER COLUMN listprice TYPE NUMERIC; +ALTER TABLE parts ALTER COLUMN sellprice TYPE NUMERIC; +ALTER TABLE parts ALTER COLUMN lastcost TYPE NUMERIC; ALTER TABLE parts ALTER COLUMN weight TYPE numeric; ALTER TABLE parts ALTER COLUMN onhand TYPE numeric; -ALTER TABLE parts ALTER COLUMN avgcost TYPE numeric(10,2); +ALTER TABLE parts ALTER COLUMN avgcost TYPE NUMERIC; ALTER TABLE assembly ALTER COLUMN qty TYPE numeric; -ALTER TABLE ar ALTER COLUMN amount TYPE numeric(10,2); -ALTER TABLE ar ALTER COLUMN netamount TYPE numeric(10,2); -ALTER TABLE ar ALTER COLUMN paid TYPE numeric(10,2); +ALTER TABLE ar ALTER COLUMN amount TYPE NUMERIC; +ALTER TABLE ar ALTER COLUMN netamount TYPE NUMERIC; +ALTER TABLE ar ALTER COLUMN paid TYPE NUMERIC; -ALTER TABLE ap ALTER COLUMN amount TYPE numeric(10,2); -ALTER TABLE ap ALTER COLUMN netamount TYPE numeric(10,2); -ALTER TABLE ap ALTER COLUMN paid TYPE numeric(10,2); +ALTER TABLE ap ALTER COLUMN amount TYPE NUMERIC; +ALTER TABLE ap ALTER COLUMN netamount TYPE NUMERIC; +ALTER TABLE ap ALTER COLUMN paid TYPE NUMERIC; ALTER TABLE tax ALTER COLUMN rate TYPE numeric; -ALTER TABLE oe ALTER COLUMN amount TYPE numeric(10,2); -ALTER TABLE oe ALTER COLUMN netamount TYPE numeric(10,2); +ALTER TABLE oe ALTER COLUMN amount TYPE NUMERIC; +ALTER TABLE oe ALTER COLUMN netamount TYPE NUMERIC; ALTER TABLE orderitems ALTER COLUMN qty TYPE numeric; -ALTER TABLE orderitems ALTER COLUMN sellprice TYPE numeric(10,2); +ALTER TABLE orderitems ALTER COLUMN sellprice TYPE NUMERIC; ALTER TABLE orderitems ALTER COLUMN discount TYPE numeric; ALTER TABLE orderitems ALTER COLUMN ship TYPE numeric; @@ -67,15 +66,15 @@ ALTER TABLE business ALTER COLUMN discount TYPE numeric; ALTER TABLE inventory ALTER COLUMN qty TYPE numeric; -ALTER TABLE partsvendor ALTER COLUMN lastcost TYPE numeric(10,2); +ALTER TABLE partsvendor ALTER COLUMN lastcost TYPE NUMERIC; ALTER TABLE partscustomer ALTER COLUMN pricebreak TYPE numeric; -ALTER TABLE partscustomer ALTER COLUMN sellprice TYPE numeric(10,2); +ALTER TABLE partscustomer ALTER COLUMN sellprice TYPE NUMERIC; ALTER TABLE jcitems ALTER COLUMN qty TYPE numeric; ALTER TABLE jcitems ALTER COLUMN allocated TYPE numeric; -ALTER TABLE jcitems ALTER COLUMN sellprice TYPE numeric(10,2); -ALTER TABLE jcitems ALTER COLUMN fxsellprice TYPE numeric(10,2); +ALTER TABLE jcitems ALTER COLUMN sellprice TYPE NUMERIC; +ALTER TABLE jcitems ALTER COLUMN fxsellprice TYPE NUMERIC; -- The query rewrite rule necessary to notify the email app that a new report -- needs to be sent to the designated administrator. @@ -224,5 +223,56 @@ CREATE RULE warehouse_id_track_u AS ON update TO warehouse DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id; -COMMIT; +CREATE TABLE custom_table_catalog ( +table_id SERIAL PRIMARY KEY, +extends TEXT, +table_name TEXT +); + +CREATE TABLE custom_field_catalog ( +field_id SERIAL PRIMARY KEY, +table_id INT REFERENCES custom_table_catalog, +field_name TEXT +); +CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR) +RETURNS BOOL AS +' +DECLARE +table_name ALIAS FOR $1; +new_field_name ALIAS FOR $2; +field_datatype ALIAS FOR $3; + +BEGIN + EXECUTE ''SELECT TABLE_ID FROM custom_table_catalog + WHERE extends = '''''' || table_name || '''''' ''; + IF NOT FOUND THEN + INSERT INTO custom_table_catalog (extends) VALUES (table_name); + EXECUTE ''CREATE TABLE custom_''||table_name || '' ()''; + END IF; + EXECUTE ''INSERT INTO custom_field_catalog (field_name, table_id) + VALUES ( '''''' || new_field_name ||'''''', (SELECT table_id FROM custom_table_catalog + WHERE extends = ''''''|| table_name || ''''''))''; + EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN '' + || new_field_name || '' '' || field_datatype; + RETURN TRUE; +END; +' LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR) +RETURNS BOOL AS +' +DECLARE +table_name ALIAS FOR $1; +custom_field_name ALIAS FOR $2; +BEGIN + DELETE FROM custom_field_catalog + WHERE field_name = custom_field_name AND + table_id = (SELECT table_id FROM custom_table_catalog + WHERE extends = table_name); + EXECUTE ''ALTER TABLE custom_'' || table_name || + '' DROP COLUMN '' || field_name; + RETURN TRUE; +END; +' LANGUAGE PLPGSQL; +UPDATE defaults SET version = '2.6.18'; |