SF.net SVN: ledger-smb: [1405] trunk/sql/Pg-database.sql

[..hidden..]

Revision: 1405
          http://svn.sourceforge.net/ledger-smb/?rev=1405&view=rev
Author:   einhverfr
Date:     2007-07-16 11:33:04 -0700 (Mon, 16 Jul 2007)

Log Message:
-----------
Finalizing data integrity control checks for global sequence for 1.3

Modified Paths:
--------------
    trunk/sql/Pg-database.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2007-07-16 16:23:14 UTC (rev 1404)
+++ trunk/sql/Pg-database.sql	2007-07-16 18:33:04 UTC (rev 1405)
@@ -309,7 +309,7 @@
 );
 --
 CREATE TABLE gl (
-  id int DEFAULT nextval ( 'id' ) PRIMARY KEY,
+  id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id),
   reference text,
   description text,
   transdate date DEFAULT current_date,
@@ -546,7 +546,7 @@
 );
 --
 CREATE TABLE ar (
-  id int DEFAULT nextval ( 'id' ) PRIMARY KEY,
+  id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id),
   invnumber text,
   transdate date DEFAULT current_date,
   entity_id int REFERENCES entity(id),
@@ -579,7 +579,7 @@
 
 --
 CREATE TABLE ap (
-  id int DEFAULT nextval ( 'id' ) PRIMARY KEY,
+  id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id),
   invnumber text,
   transdate date DEFAULT current_date,
   entity_id int REFERENCES entity(id),
@@ -964,102 +964,38 @@
 
 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 ap;
 
-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, '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, '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 OR REPLACE FUNCTION track_global_sequence() RETURNS TRIGGER AS
+$$
+BEGIN
+	IF tg_op = 'INSERT' THEN
+		INSERT INTO transactions (id, table_name) 
+		VALUES (new.id, TG_RELNAME);
+	ELSEIF tg_op = 'UPDATE' THEN
+		IF new.id = old.id THEN
+			return new;
+		ELSE
+			UPDATE transactions SET id = new.id WHERE id = old.id;
+		END IF;
+	ELSE 
+		DELETE FROM transactions WHERE id = old_id;
+	END IF;
+	RETURN new;
+END;
+$$ LANGUAGE PLPGSQL;
 
-CREATE RULE gl_id_track_u AS ON update TO gl 
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
+CREATE TRIGGER ap_track_global_sequence before insert or update or delete on ap
+for each row execute procedure track_global_sequence();
 
-INSERT INTO transactions (id, table_name) SELECT id, 'oe' FROM oe;
+CREATE TRIGGER ar_track_global_sequence before insert or update or delete on ar
+for each row execute procedure track_global_sequence();
 
-CREATE RULE oe_id_track_i AS ON insert TO oe
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'oe');
+CREATE TRIGGER gl_track_global_sequence before insert or update or delete on gl
+for each row execute procedure track_global_sequence();
 
-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, '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,


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