[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [107] trunk/sql

Subject: SF.net SVN: ledger-smb: [107] trunk/sql
From: ..hidden..
Date: Sun, 17 Sep 2006 20:35:23 -0700
Revision: 107
          http://svn.sourceforge.net/ledger-smb/?rev=107&view=rev
Author:   einhverfr
Date:     2006-09-17 20:35:21 -0700 (Sun, 17 Sep 2006)

Log Message:
-----------
Added stored procedures and catalogs for custom fields.  No logic is added to
API yet.

Modified Paths:
--------------
    trunk/sql/Pg-functions.sql
    trunk/sql/Pg-tables.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 01:14:50 UTC (rev 106)
+++ trunk/sql/Pg-functions.sql	2006-09-18 03:35:21 UTC (rev 107)
@@ -268,3 +268,44 @@
 
 CREATE TRIGGER parts_short AFTER UPDATE ON parts 
 FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short();
+
+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
+		INSERT INTO custom_table_catalog (extends) VALUES (table_name);
+		EXECUTE ''CREATE TABLE custom_''||table_name || '' ()'';
+	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;
+
+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 '' || field_name;
+	RETURN TRUE;	
+END;
+' LANGUAGE PLPGSQL;

Modified: trunk/sql/Pg-tables.sql
===================================================================
--- trunk/sql/Pg-tables.sql	2006-09-18 01:14:50 UTC (rev 106)
+++ trunk/sql/Pg-tables.sql	2006-09-18 03:35:21 UTC (rev 107)
@@ -75,7 +75,7 @@
 CREATE TABLE acc_trans (
   trans_id int,
   chart_id int NOT NULL REFERENCES chart (id),
-  amount numeric(10,2),
+  amount NUMERIC,
   transdate date DEFAULT current_date,
   source text,
   cleared bool DEFAULT 'f',
@@ -92,8 +92,8 @@
   description text,
   qty integer,
   allocated integer,
-  sellprice numeric(10,2),
-  fxsellprice numeric(10,2),
+  sellprice NUMERIC,
+  fxsellprice NUMERIC,
   discount float4, -- jd: check into this
   assemblyitem bool DEFAULT 'f',
   unit varchar(5),
@@ -119,7 +119,7 @@
   notes text,
   discount numeric, 
   taxincluded bool default 'f',
-  creditlimit numeric(10,2) default 0,
+  creditlimit NUMERIC default 0,
   terms int2 default 0,
   customernumber varchar(32),
   cc text,
@@ -143,9 +143,9 @@
   partnumber text,
   description text,
   unit varchar(5),
-  listprice numeric(10,2),
-  sellprice numeric(10,2),
-  lastcost numeric(10,2),
+  listprice NUMERIC,
+  sellprice NUMERIC,
+  lastcost NUMERIC,
   priceupdate date DEFAULT current_date,
   weight numeric,
   onhand numeric DEFAULT 0,
@@ -165,7 +165,7 @@
   microfiche text,
   partsgroup_id int,
   project_id int,
-  avgcost numeric(10,2)
+  avgcost NUMERIC
 );
 --
 CREATE TABLE assembly (
@@ -182,9 +182,9 @@
   transdate date DEFAULT current_date,
   customer_id int,
   taxincluded bool,
-  amount numeric(10,2),
-  netamount numeric(10,2),
-  paid numeric(10,2),
+  amount NUMERIC,
+  netamount NUMERIC,
+  paid NUMERIC,
   datepaid date,
   duedate date,
   invoice bool DEFAULT 'f',
@@ -209,9 +209,9 @@
   transdate date DEFAULT current_date,
   vendor_id int,
   taxincluded bool DEFAULT 'f',
-  amount numeric(10,2),
-  netamount numeric(10,2),
-  paid numeric(10,2),
+  amount NUMERIC,
+  netamount NUMERIC,
+  paid NUMERIC,
   datepaid date,
   duedate date,
   invoice bool DEFAULT 'f',
@@ -258,8 +258,8 @@
   transdate date default current_date,
   vendor_id int,
   customer_id int,
-  amount numeric(10,2),
-  netamount numeric(10,2),
+  amount NUMERIC,
+  netamount NUMERIC,
   reqdate date,
   taxincluded bool,
   shippingpoint text,
@@ -283,7 +283,7 @@
   parts_id int,
   description text,
   qty numeric,
-  sellprice numeric(10,2),
+  sellprice NUMERIC,
   discount numeric,
   unit varchar(5),
   project_id int,
@@ -452,7 +452,7 @@
   parts_id int,
   partnumber text,
   leadtime int2,
-  lastcost numeric(10,2),
+  lastcost NUMERIC,
   curr char(3)
 );
 --
@@ -466,7 +466,7 @@
   customer_id int,
   pricegroup_id int,
   pricebreak numeric,
-  sellprice numeric(10,2),
+  sellprice NUMERIC,
   validfrom date,
   validto date,
   curr char(3)
@@ -526,8 +526,8 @@
   description text,
   qty numeric,
   allocated numeric,
-  sellprice numeric(10,2),
-  fxsellprice numeric(10,2),
+  sellprice NUMERIC,
+  fxsellprice NUMERIC,
   serialnumber text,
   checkedin timestamp with time zone,
   checkedout timestamp with time zone,
@@ -673,3 +673,14 @@
 CREATE RULE warehouse_id_track_u AS ON update TO warehouse 
 DO ALSO UPDATE transaction_ledger 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
+);

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 01:14:50 UTC (rev 106)
+++ trunk/sql/Pg-upgrade-2.6.17-2.6.18.sql	2006-09-18 03:35:21 UTC (rev 107)
@@ -3,7 +3,6 @@
 -- 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
 
-BEGIN;
 LOCK acc_trans in EXCLUSIVE mode;
 ALTER TABLE acc_trans ADD COLUMN entry_id bigint;
 CREATE SEQUENCE acctrans_entry_id_seq;
@@ -15,42 +14,42 @@
 ALTER TABLE acc_trans ADD FOREIGN KEY (chart_id) REFERENCES chart (id);
 
 -- Start changing floats
-ALTER TABLE acc_trans ALTER COLUMN amount TYPE numeric(10,2);
+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(10,2);
-ALTER TABLE invoice ALTER COLUMN fxsellprice TYPE numeric(10,2);
+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(10,2);
+ALTER TABLE customer ALTER COLUMN creditlimit TYPE NUMERIC;
 
-ALTER TABLE parts ALTER COLUMN listprice TYPE numeric(10,2);
-ALTER TABLE parts ALTER COLUMN sellprice TYPE numeric(10,2);
-ALTER TABLE parts ALTER COLUMN lastcost TYPE numeric(10,2);
+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(10,2);
+ALTER TABLE parts ALTER COLUMN avgcost TYPE NUMERIC;
 
 ALTER TABLE assembly ALTER COLUMN qty TYPE numeric;
 
-ALTER TABLE ar ALTER COLUMN amount TYPE numeric(10,2);
-ALTER TABLE ar ALTER COLUMN netamount TYPE numeric(10,2);
-ALTER TABLE ar ALTER COLUMN paid TYPE numeric(10,2);
+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(10,2);
-ALTER TABLE ap ALTER COLUMN netamount TYPE numeric(10,2);
-ALTER TABLE ap ALTER COLUMN paid TYPE numeric(10,2);
+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(10,2);
-ALTER TABLE oe ALTER COLUMN netamount TYPE numeric(10,2);
+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(10,2);
+ALTER TABLE orderitems ALTER COLUMN sellprice TYPE NUMERIC;
 ALTER TABLE orderitems ALTER COLUMN discount TYPE numeric;
 ALTER TABLE orderitems ALTER COLUMN ship TYPE numeric;
 
@@ -67,15 +66,15 @@
 
 ALTER TABLE inventory ALTER COLUMN qty TYPE numeric;
 
-ALTER TABLE partsvendor ALTER COLUMN lastcost TYPE numeric(10,2);
+ALTER TABLE partsvendor ALTER COLUMN lastcost TYPE NUMERIC;
 
 ALTER TABLE partscustomer ALTER COLUMN pricebreak TYPE numeric;
-ALTER TABLE partscustomer ALTER COLUMN sellprice TYPE numeric(10,2);
+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(10,2);
-ALTER TABLE jcitems ALTER COLUMN fxsellprice TYPE numeric(10,2);
+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.
@@ -224,5 +223,56 @@
 DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
 
 
-COMMIT;
+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
+		INSERT INTO custom_table_catalog (extends) VALUES (table_name);
+		EXECUTE ''CREATE TABLE custom_''||table_name || '' ()'';
+	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;
+
+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 '' || field_name;
+	RETURN TRUE;	
+END;
+' LANGUAGE PLPGSQL;
+
+UPDATE defaults SET version = '2.6.18';


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
Prev by Date: SF.net SVN: ledger-smb: [106] trunk/LedgerSMB/AM.pm
Next by Date: SF.net SVN: ledger-smb: [108] trunk/LedgerSMB/AM.pm
Previous by thread: SF.net SVN: ledger-smb: [106] trunk/LedgerSMB/AM.pm
Next by thread: SF.net SVN: ledger-smb: [108] trunk/LedgerSMB/AM.pm
Index(es):
- Date
- Thread