diff options
Diffstat (limited to 'sql')
| -rwxr-xr-x | sql/Pg-functions.sql | 41 | ||||
| -rwxr-xr-x | sql/Pg-tables.sql | 53 | ||||
| -rw-r--r-- | sql/Pg-upgrade-2.6.17-2.6.18.sql | 96 |
3 files changed, 146 insertions, 44 deletions
diff --git a/sql/Pg-functions.sql b/sql/Pg-functions.sql index 4eea5011..e3689fd6 100755 --- a/sql/Pg-functions.sql +++ b/sql/Pg-functions.sql @@ -268,3 +268,44 @@ END; CREATE TRIGGER parts_short AFTER UPDATE ON parts FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short(); + +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; diff --git a/sql/Pg-tables.sql b/sql/Pg-tables.sql index f523bcba..e286c0a6 100755 --- a/sql/Pg-tables.sql +++ b/sql/Pg-tables.sql @@ -75,7 +75,7 @@ INSERT INTO defaults (version) VALUES ('2.6.17'); CREATE TABLE acc_trans ( trans_id int, chart_id int NOT NULL REFERENCES chart (id), - amount numeric(10,2), + amount NUMERIC, transdate date DEFAULT current_date, source text, cleared bool DEFAULT 'f', @@ -92,8 +92,8 @@ CREATE TABLE invoice ( description text, qty integer, allocated integer, - sellprice numeric(10,2), - fxsellprice numeric(10,2), + sellprice NUMERIC, + fxsellprice NUMERIC, discount float4, -- jd: check into this assemblyitem bool DEFAULT 'f', unit varchar(5), @@ -119,7 +119,7 @@ CREATE TABLE customer ( notes text, discount numeric, taxincluded bool default 'f', - creditlimit numeric(10,2) default 0, + creditlimit NUMERIC default 0, terms int2 default 0, customernumber varchar(32), cc text, @@ -143,9 +143,9 @@ CREATE TABLE parts ( partnumber text, description text, unit varchar(5), - listprice numeric(10,2), - sellprice numeric(10,2), - lastcost numeric(10,2), + listprice NUMERIC, + sellprice NUMERIC, + lastcost NUMERIC, priceupdate date DEFAULT current_date, weight numeric, onhand numeric DEFAULT 0, @@ -165,7 +165,7 @@ CREATE TABLE parts ( microfiche text, partsgroup_id int, project_id int, - avgcost numeric(10,2) + avgcost NUMERIC ); -- CREATE TABLE assembly ( @@ -182,9 +182,9 @@ CREATE TABLE ar ( transdate date DEFAULT current_date, customer_id int, taxincluded bool, - amount numeric(10,2), - netamount numeric(10,2), - paid numeric(10,2), + amount NUMERIC, + netamount NUMERIC, + paid NUMERIC, datepaid date, duedate date, invoice bool DEFAULT 'f', @@ -209,9 +209,9 @@ CREATE TABLE ap ( transdate date DEFAULT current_date, vendor_id int, taxincluded bool DEFAULT 'f', - amount numeric(10,2), - netamount numeric(10,2), - paid numeric(10,2), + amount NUMERIC, + netamount NUMERIC, + paid NUMERIC, datepaid date, duedate date, invoice bool DEFAULT 'f', @@ -258,8 +258,8 @@ CREATE TABLE oe ( transdate date default current_date, vendor_id int, customer_id int, - amount numeric(10,2), - netamount numeric(10,2), + amount NUMERIC, + netamount NUMERIC, reqdate date, taxincluded bool, shippingpoint text, @@ -283,7 +283,7 @@ CREATE TABLE orderitems ( parts_id int, description text, qty numeric, - sellprice numeric(10,2), + sellprice NUMERIC, discount numeric, unit varchar(5), project_id int, @@ -452,7 +452,7 @@ CREATE TABLE partsvendor ( parts_id int, partnumber text, leadtime int2, - lastcost numeric(10,2), + lastcost NUMERIC, curr char(3) ); -- @@ -466,7 +466,7 @@ CREATE TABLE partscustomer ( customer_id int, pricegroup_id int, pricebreak numeric, - sellprice numeric(10,2), + sellprice NUMERIC, validfrom date, validto date, curr char(3) @@ -526,8 +526,8 @@ CREATE TABLE jcitems ( description text, qty numeric, allocated numeric, - sellprice numeric(10,2), - fxsellprice numeric(10,2), + sellprice NUMERIC, + fxsellprice NUMERIC, serialnumber text, checkedin timestamp with time zone, checkedout timestamp with time zone, @@ -673,3 +673,14 @@ DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'employe CREATE RULE warehouse_id_track_u AS ON update TO warehouse DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id; +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 +); 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'; |