From d9fd4220ca9409d53b56a2fdc1280f348c40b3ba Mon Sep 17 00:00:00 2001
From: einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>
Date: Mon, 18 Sep 2006 03:35:21 +0000
Subject: Added stored procedures and catalogs for custom fields. No logic is
added to API yet.
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@107 4979c152-3d1c-0410-bac9-87ea11338e46
---
sql/Pg-functions.sql | 41 +++++++++++++++++
sql/Pg-tables.sql | 53 +++++++++++++---------
sql/Pg-upgrade-2.6.17-2.6.18.sql | 96 ++++++++++++++++++++++++++++++----------
3 files changed, 146 insertions(+), 44 deletions(-)
(limited to 'sql')
diff --git a/sql/Pg-functions.sql b/sql/Pg-functions.sql
index 4eea5011..e3689fd6 100755
--- a/sql/Pg-functions.sql
+++ b/sql/Pg-functions.sql
@@ -268,3 +268,44 @@ END;
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;
diff --git a/sql/Pg-tables.sql b/sql/Pg-tables.sql
index f523bcba..e286c0a6 100755
--- a/sql/Pg-tables.sql
+++ b/sql/Pg-tables.sql
@@ -75,7 +75,7 @@ INSERT INTO defaults (version) VALUES ('2.6.17');
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 @@ CREATE TABLE invoice (
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 @@ CREATE TABLE customer (
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 @@ CREATE TABLE parts (
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 @@ CREATE TABLE parts (
microfiche text,
partsgroup_id int,
project_id int,
- avgcost numeric(10,2)
+ avgcost NUMERIC
);
--
CREATE TABLE assembly (
@@ -182,9 +182,9 @@ CREATE TABLE ar (
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 @@ CREATE TABLE ap (
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 @@ CREATE TABLE oe (
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 @@ CREATE TABLE orderitems (
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 @@ CREATE TABLE partsvendor (
parts_id int,
partnumber text,
leadtime int2,
- lastcost numeric(10,2),
+ lastcost NUMERIC,
curr char(3)
);
--
@@ -466,7 +466,7 @@ CREATE TABLE partscustomer (
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 @@ CREATE TABLE jcitems (
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 @@ DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'employe
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
+);
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 2067074e..8144f1df 100644
--- a/sql/Pg-upgrade-2.6.17-2.6.18.sql
+++ b/sql/Pg-upgrade-2.6.17-2.6.18.sql
@@ -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 PRIMARY key (entry_id);
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 business ALTER COLUMN discount TYPE numeric;
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 @@ CREATE RULE warehouse_id_track_u AS ON update TO warehouse
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';
--
cgit v1.2.3