SF.net SVN: ledger-smb: [80] trunk/sql

[..hidden..]

Revision: 80
          http://svn.sourceforge.net/ledger-smb/?rev=80&view=rev
Author:   einhverfr
Date:     2006-09-12 23:12:06 -0700 (Tue, 12 Sep 2006)

Log Message:
-----------
Added unique id tracking.

Modified Paths:
--------------
    trunk/sql/Pg-tables.sql
    trunk/sql/Pg-upgrade-2.6.17-2.6.18.sql

Modified: trunk/sql/Pg-tables.sql
===================================================================
--- trunk/sql/Pg-tables.sql	2006-09-13 05:36:21 UTC (rev 79)
+++ trunk/sql/Pg-tables.sql	2006-09-13 06:12:06 UTC (rev 80)
@@ -548,3 +548,128 @@
 );
 
 
+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;
+

Modified: trunk/sql/Pg-upgrade-2.6.17-2.6.18.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.6.17-2.6.18.sql	2006-09-13 05:36:21 UTC (rev 79)
+++ trunk/sql/Pg-upgrade-2.6.17-2.6.18.sql	2006-09-13 06:12:06 UTC (rev 80)
@@ -94,5 +94,131 @@
 
 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;
 


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.