From 3f15acabf8761a9c2e2b9722e184a565262bc501 Mon Sep 17 00:00:00 2001 From: einhverfr Date: Wed, 13 Sep 2006 06:12:06 +0000 Subject: Added unique id tracking. git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@80 4979c152-3d1c-0410-bac9-87ea11338e46 --- sql/Pg-tables.sql | 125 ++++++++++++++++++++++++++++++++++++++ sql/Pg-upgrade-2.6.17-2.6.18.sql | 126 +++++++++++++++++++++++++++++++++++++++ 2 files changed, 251 insertions(+) (limited to 'sql') diff --git a/sql/Pg-tables.sql b/sql/Pg-tables.sql index c826d357..1490acfc 100755 --- a/sql/Pg-tables.sql +++ b/sql/Pg-tables.sql @@ -548,3 +548,128 @@ last_used TIMESTAMP default now() ); +create table id_tracker ( + id int PRIMARY KEY, + table_name text +); + +insert into id_tracker (id, table_name) SELECT id, 'ap' FROM ap; + +CREATE RULE ap_id_track_i AS ON insert TO ap +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'ap'); + +CREATE RULE ap_id_track_u AS ON update TO ap +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +insert into id_tracker (id, table_name) SELECT id, 'ar' FROM ap; + +CREATE RULE ar_id_track_i AS ON insert TO ar +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'ar'); + +CREATE RULE ar_id_track_u AS ON update TO ar +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'business' FROM business; + +CREATE RULE business_id_track_i AS ON insert TO business +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'business'); + +CREATE RULE business_id_track_u AS ON update TO business +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'chart' FROM chart; + +CREATE RULE chart_id_track_i AS ON insert TO chart +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'chart'); + +CREATE RULE chart_id_track_u AS ON update TO chart +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'customer' FROM customer; + +CREATE RULE customer_id_track_i AS ON insert TO customer +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'customer'); + +CREATE RULE customer_id_track_u AS ON update TO customer +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'department' FROM department; + +CREATE RULE department_id_track_i AS ON insert TO department +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'department'); + +CREATE RULE department_id_track_u AS ON update TO department +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'employee' FROM employee; + +CREATE RULE employee_id_track_i AS ON insert TO employee +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'employee'); + +CREATE RULE employee_id_track_u AS ON update TO employee +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'gl' FROM gl; + +CREATE RULE gl_id_track_i AS ON insert TO gl +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'gl'); + +CREATE RULE gl_id_track_u AS ON update TO gl +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'oe' FROM oe; + +CREATE RULE oe_id_track_i AS ON insert TO oe +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'oe'); + +CREATE RULE oe_id_track_u AS ON update TO oe +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'parts' FROM parts; + +CREATE RULE parts_id_track_i AS ON insert TO parts +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'parts'); + +CREATE RULE parts_id_track_u AS ON update TO parts +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'partsgroup' FROM partsgroup; + +CREATE RULE partsgroup_id_track_i AS ON insert TO partsgroup +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'partsgroup'); + +CREATE RULE partsgroup_id_track_u AS ON update TO partsgroup +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'pricegroup' FROM pricegroup; + +CREATE RULE pricegroup_id_track_i AS ON insert TO pricegroup +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'pricegroup'); + +CREATE RULE pricegroup_id_track_u AS ON update TO pricegroup +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'project' FROM project; + +CREATE RULE project_id_track_i AS ON insert TO project +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'project'); + +CREATE RULE project_id_track_u AS ON update TO project +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'vendor' FROM vendor; + +CREATE RULE vendor_id_track_i AS ON insert TO vendor +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'vendor'); + +CREATE RULE employee_id_track_u AS ON update TO vendor +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'warehouse' FROM warehouse; + +CREATE RULE warehouse_id_track_i AS ON insert TO warehouse +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'employee'); + +CREATE RULE warehouse_id_track_u AS ON update TO warehouse +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + 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 a0ad36d5..0ed4e0da 100644 --- a/sql/Pg-upgrade-2.6.17-2.6.18.sql +++ b/sql/Pg-upgrade-2.6.17-2.6.18.sql @@ -94,5 +94,131 @@ END; CREATE TRIGGER parts_short AFTER UPDATE ON parts FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short(); + +create table id_tracker ( + id int PRIMARY KEY, + table_name text +); + +insert into id_tracker (id, table_name) SELECT id, 'ap' FROM ap; + +CREATE RULE ap_id_track_i AS ON insert TO ap +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'ap'); + +CREATE RULE ap_id_track_u AS ON update TO ap +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +insert into id_tracker (id, table_name) SELECT id, 'ar' FROM ap; + +CREATE RULE ar_id_track_i AS ON insert TO ar +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'ar'); + +CREATE RULE ar_id_track_u AS ON update TO ar +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'business' FROM business; + +CREATE RULE business_id_track_i AS ON insert TO business +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'business'); + +CREATE RULE business_id_track_u AS ON update TO business +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'chart' FROM chart; + +CREATE RULE chart_id_track_i AS ON insert TO chart +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'chart'); + +CREATE RULE chart_id_track_u AS ON update TO chart +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'customer' FROM customer; + +CREATE RULE customer_id_track_i AS ON insert TO customer +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'customer'); + +CREATE RULE customer_id_track_u AS ON update TO customer +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'department' FROM department; + +CREATE RULE department_id_track_i AS ON insert TO department +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'department'); + +CREATE RULE department_id_track_u AS ON update TO department +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'employee' FROM employee; + +CREATE RULE employee_id_track_i AS ON insert TO employee +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'employee'); + +CREATE RULE employee_id_track_u AS ON update TO employee +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'gl' FROM gl; + +CREATE RULE gl_id_track_i AS ON insert TO gl +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'gl'); + +CREATE RULE gl_id_track_u AS ON update TO gl +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'oe' FROM oe; + +CREATE RULE oe_id_track_i AS ON insert TO oe +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'oe'); + +CREATE RULE oe_id_track_u AS ON update TO oe +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'parts' FROM parts; + +CREATE RULE parts_id_track_i AS ON insert TO parts +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'parts'); + +CREATE RULE parts_id_track_u AS ON update TO parts +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'partsgroup' FROM partsgroup; + +CREATE RULE partsgroup_id_track_i AS ON insert TO partsgroup +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'partsgroup'); + +CREATE RULE partsgroup_id_track_u AS ON update TO partsgroup +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'pricegroup' FROM pricegroup; + +CREATE RULE pricegroup_id_track_i AS ON insert TO pricegroup +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'pricegroup'); + +CREATE RULE pricegroup_id_track_u AS ON update TO pricegroup +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'project' FROM project; + +CREATE RULE project_id_track_i AS ON insert TO project +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'project'); + +CREATE RULE project_id_track_u AS ON update TO project +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'vendor' FROM vendor; + +CREATE RULE vendor_id_track_i AS ON insert TO vendor +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'vendor'); + +CREATE RULE employee_id_track_u AS ON update TO vendor +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + +INSERT INTO id_tracker (id, table_name) SELECT id, 'warehouse' FROM warehouse; + +CREATE RULE warehouse_id_track_i AS ON insert TO warehouse +DO ALSO INSERT INTO id_tracker (id, table_name) VALUES (new.id, 'employee'); + +CREATE RULE warehouse_id_track_u AS ON update TO warehouse +DO ALSO UPDATE id_tracker SET id = new.id WHERE id = old.id; + COMMIT; -- cgit v1.2.3