Moving Pg-upgrade* into sql/legacy directory

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@420 4979c152-3d1c-0410-bac9-87ea11338e46

1 files changed, 287 insertions, 0 deletions

diff --git a/sql/legacy/Pg-upgrade-2.6.17-2.6.18.sql b/sql/legacy/Pg-upgrade-2.6.17-2.6.18.sql
new file mode 100644
index 00000000..b73f4c35
--- /dev/null
+++ b/sql/legacy/Pg-upgrade-2.6.17-2.6.18.sql
@@ -0,0 +1,287 @@
+ALTER TABLE chart ADD PRIMARY KEY (id);
+-- linuxpoet:
+-- adding primary key to acc_trans
+-- 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
+
+LOCK acc_trans in EXCLUSIVE mode;
+ALTER TABLE acc_trans ADD COLUMN entry_id bigint;
+CREATE SEQUENCE acctrans_entry_id_seq;
+ALTER TABLE acc_trans ALTER COLUMN entry_id SET DEFAULT nextval('acctrans_entry_id_seq');
+UPDATE acc_trans SET entry_id = nextval('acctrans_entry_id_seq');
+ALTER TABLE acc_trans ADD PRIMARY key (entry_id);
+
+-- We should probably add a foreign key to chart.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;
+
+-- 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;
+ALTER TABLE invoice ALTER COLUMN fxsellprice TYPE NUMERIC;
+
+ALTER TABLE customer ALTER COLUMN discount TYPE numeric;
+ALTER TABLE customer ALTER COLUMN creditlimit TYPE NUMERIC;
+
+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;
+
+ALTER TABLE assembly ALTER COLUMN qty TYPE numeric;
+
+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;
+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;
+ALTER TABLE oe ALTER COLUMN netamount TYPE NUMERIC;
+
+ALTER TABLE orderitems ALTER COLUMN qty TYPE numeric;
+ALTER TABLE orderitems ALTER COLUMN sellprice TYPE NUMERIC;
+ALTER TABLE orderitems ALTER COLUMN discount TYPE numeric;
+ALTER TABLE orderitems ALTER COLUMN ship TYPE numeric;
+
+ALTER TABLE exchangerate ALTER COLUMN buy TYPE numeric;
+ALTER TABLE exchangerate ALTER COLUMN sell TYPE numeric;
+
+ALTER TABLE vendor ALTER COLUMN discount TYPE numeric;
+ALTER TABLE vendor ALTER COLUMN creditlimit TYPE numeric; 
+
+ALTER TABLE project ALTER COLUMN production TYPE numeric; 
+ALTER TABLE project ALTER COLUMN completed TYPE numeric;
+
+ALTER TABLE business ALTER COLUMN discount TYPE numeric;
+
+ALTER TABLE inventory ALTER COLUMN qty TYPE numeric;
+
+ALTER TABLE partsvendor ALTER COLUMN lastcost TYPE NUMERIC;
+
+ALTER TABLE partscustomer ALTER COLUMN pricebreak TYPE numeric;
+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;
+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.
+-- By Chris Travers
+-- chris@metatrontech.com
+-- Licensed under the GNU GPL 2.0 or later at your option.  See accompanying
+-- GPL.txt
+
+CREATE OR REPLACE FUNCTION trigger_parts_short() RETURNS TRIGGER
+AS
+'
+BEGIN
+  IF NEW.onhand >= NEW.rop THEN
+    NOTIFY parts_short;
+  END IF;
+  RETURN NEW;
+END;
+' LANGUAGE PLPGSQL;
+-- end function
+
+CREATE TRIGGER parts_short AFTER UPDATE ON parts 
+FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short();
+
+create table transactions (
+  id int PRIMARY KEY,
+  table_name text
+);
+
+insert into transactions (id, table_name) SELECT id, 'ap' FROM ap;
+
+CREATE RULE ap_id_track_i AS ON insert TO ap 
+DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ap');
+
+CREATE RULE ap_id_track_u AS ON update TO ap 
+DO UPDATE transactions SET id = new.id WHERE id = old.id;
+
+insert into transactions (id, table_name) SELECT id, 'ar' FROM ar;
+
+CREATE RULE ar_id_track_i AS ON insert TO ar 
+DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ar');
+
+CREATE RULE ar_id_track_u AS ON update TO ar 
+DO UPDATE transactions SET id = new.id WHERE id = old.id;
+
+INSERT INTO transactions (id, table_name) SELECT id, 'business' FROM business;
+
+CREATE RULE business_id_track_i AS ON insert TO business 
+DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'business');
+
+CREATE RULE business_id_track_u AS ON update TO business 
+DO UPDATE transactions SET id = new.id WHERE id = old.id;
+
+INSERT INTO transactions (id, table_name) SELECT id, 'chart' FROM chart;
+
+CREATE RULE chart_id_track_i AS ON insert TO chart 
+DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'chart');
+
+CREATE RULE chart_id_track_u AS ON update TO chart 
+DO UPDATE transactions SET id = new.id WHERE id = old.id;
+
+INSERT INTO transactions (id, table_name) SELECT id, 'customer' FROM customer;
+
+CREATE RULE customer_id_track_i AS ON insert TO customer
+DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'customer');
+
+CREATE RULE customer_id_track_u AS ON update TO customer 
+DO UPDATE transactions SET id = new.id WHERE id = old.id;
+
+INSERT INTO transactions (id, table_name) SELECT id, 'department' FROM department;
+
+CREATE RULE department_id_track_i AS ON insert TO department
+DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'department');
+
+CREATE RULE department_id_track_u AS ON update TO department 
+DO UPDATE transactions SET id = new.id WHERE id = old.id;
+
+INSERT INTO transactions (id, table_name) SELECT id, 'employee' FROM employee;
+
+CREATE RULE employee_id_track_i AS ON insert TO employee
+DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee');
+
+CREATE RULE employee_id_track_u AS ON update TO employee
+DO UPDATE transactions SET id = new.id WHERE id = old.id;
+
+INSERT INTO transactions (id, table_name) SELECT id, 'gl' FROM gl;
+
+CREATE RULE gl_id_track_i AS ON insert TO gl
+DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'gl');
+
+CREATE RULE gl_id_track_u AS ON update TO gl 
+DO UPDATE transactions SET id = new.id WHERE id = old.id;
+
+INSERT INTO transactions (id, table_name) SELECT id, 'oe' FROM oe;
+
+CREATE RULE oe_id_track_i AS ON insert TO oe
+DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'oe');
+
+CREATE RULE oe_id_track_u AS ON update TO oe 
+DO UPDATE transactions SET id = new.id WHERE id = old.id;
+
+INSERT INTO transactions (id, table_name) SELECT id, 'parts' FROM parts;
+
+CREATE RULE parts_id_track_i AS ON insert TO parts
+DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'parts');
+
+CREATE RULE parts_id_track_u AS ON update TO parts 
+DO UPDATE transactions SET id = new.id WHERE id = old.id;
+
+INSERT INTO transactions (id, table_name) SELECT id, 'partsgroup' FROM partsgroup;
+
+CREATE RULE partsgroup_id_track_i AS ON insert TO partsgroup
+DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'partsgroup');
+
+CREATE RULE partsgroup_id_track_u AS ON update TO partsgroup 
+DO UPDATE transactions SET id = new.id WHERE id = old.id;
+
+INSERT INTO transactions (id, table_name) SELECT id, 'pricegroup' FROM pricegroup;
+
+CREATE RULE pricegroup_id_track_i AS ON insert TO pricegroup
+DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'pricegroup');
+
+CREATE RULE pricegroup_id_track_u AS ON update TO pricegroup 
+DO UPDATE transactions SET id = new.id WHERE id = old.id;
+
+INSERT INTO transactions (id, table_name) SELECT id, 'project' FROM project;
+
+CREATE RULE project_id_track_i AS ON insert TO project
+DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'project');
+
+CREATE RULE project_id_track_u AS ON update TO project 
+DO UPDATE transactions SET id = new.id WHERE id = old.id;
+
+INSERT INTO transactions (id, table_name) SELECT id, 'vendor' FROM vendor;
+
+CREATE RULE vendor_id_track_i AS ON insert TO vendor
+DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'vendor');
+
+CREATE RULE employee_id_track_u AS ON update TO vendor 
+DO UPDATE transactions SET id = new.id WHERE id = old.id;
+
+INSERT INTO transactions (id, table_name) SELECT id, 'warehouse' FROM warehouse;
+
+CREATE RULE warehouse_id_track_i AS ON insert TO warehouse
+DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee');
+
+CREATE RULE warehouse_id_track_u AS ON update TO warehouse 
+DO UPDATE transactions 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
+);
+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
+		BEGIN
+			INSERT INTO custom_table_catalog (extends) VALUES (table_name);
+			EXECUTE ''CREATE TABLE custom_''||table_name || 
+				'' (row_id INT)'';
+		EXCEPTION WHEN duplicate_table THEN
+			-- do nothing
+		END;
+	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;
+-- end function
+
+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 '' || custom_field_name;
+	RETURN TRUE;	
+END;
+' LANGUAGE PLPGSQL;
+-- end function
+
+UPDATE defaults SET version = '2.6.18';