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

[..hidden..]

Revision: 115
          http://svn.sourceforge.net/ledger-smb/?rev=115&view=rev
Author:   einhverfr
Date:     2006-09-18 19:46:01 -0700 (Mon, 18 Sep 2006)

Log Message:
-----------
Committing db fixes from Seneca

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

Modified: trunk/sql/Pg-functions.sql
===================================================================
--- trunk/sql/Pg-functions.sql	2006-09-18 23:06:11 UTC (rev 114)
+++ trunk/sql/Pg-functions.sql	2006-09-19 02:46:01 UTC (rev 115)
@@ -281,8 +281,14 @@
 	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 || '' ()'';
+		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
@@ -305,7 +311,7 @@
 		table_id = (SELECT table_id FROM custom_table_catalog 
 			WHERE extends = table_name);
 	EXECUTE ''ALTER TABLE custom_'' || table_name || 
-		'' DROP COLUMN '' || field_name;
+		'' DROP COLUMN '' || custom_field_name;
 	RETURN TRUE;	
 END;
 ' LANGUAGE PLPGSQL;

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-18 23:06:11 UTC (rev 114)
+++ trunk/sql/Pg-upgrade-2.6.17-2.6.18.sql	2006-09-19 02:46:01 UTC (rev 115)
@@ -1,3 +1,4 @@
+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
@@ -110,7 +111,7 @@
 CREATE RULE ap_id_track_u AS ON update TO ap 
 DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
 
-insert into transaction_ledger (id, table_name) SELECT id, 'ar' FROM ap;
+insert into transaction_ledger (id, table_name) SELECT id, 'ar' FROM ar;
 
 CREATE RULE ar_id_track_i AS ON insert TO ar 
 DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'ar');
@@ -246,8 +247,13 @@
 	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 || '' ()'';
+		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
@@ -270,7 +276,7 @@
 		table_id = (SELECT table_id FROM custom_table_catalog 
 			WHERE extends = table_name);
 	EXECUTE ''ALTER TABLE custom_'' || table_name || 
-		'' DROP COLUMN '' || field_name;
+		'' DROP COLUMN '' || custom_field_name;
 	RETURN TRUE;	
 END;
 ' LANGUAGE PLPGSQL;


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