summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authoreinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2006-11-01 05:24:34 +0000
committereinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2006-11-01 05:24:34 +0000
commitd1eef00925161c3fc0a148a44bd2d88c864e81ef (patch)
tree778c24ef679ce011e711055f4b595807ee9efa65
parent0f1d9536c4e929d963f135b7a37dd6572014f4d8 (diff)
Merged Pg-tables, functions, and indeces into Pg-database.sql
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@449 4979c152-3d1c-0410-bac9-87ea11338e46
-rw-r--r--Changelog1
-rwxr-xr-xLedgerSMB/User.pm9
-rw-r--r--[-rwxr-xr-x]sql/Pg-database.sql (renamed from sql/Pg-tables.sql)420
-rwxr-xr-xsql/Pg-functions.sql320
-rwxr-xr-xsql/Pg-indices.sql100
5 files changed, 422 insertions, 428 deletions
diff --git a/Changelog b/Changelog
index a429bf76..f24b9eac 100644
--- a/Changelog
+++ b/Changelog
@@ -5,6 +5,7 @@ Database:
* Added defined primary keys to all tables (Chris T)
* Database upgrades now use psql (Chris T)
* Defaults table now uses a simple key->value system (Chris T)
+* Merged Pg-tables, Pg-functions, and Pg-indeces into Pg-database (Chris T)
Security:
* Added whitelist of allowed directories to file editor (Seneca)
diff --git a/LedgerSMB/User.pm b/LedgerSMB/User.pm
index a16cf7d5..67249a04 100755
--- a/LedgerSMB/User.pm
+++ b/LedgerSMB/User.pm
@@ -367,13 +367,9 @@ sub dbcreate {
? 'Pg'
: $form->{dbdriver};
- my $filename = qq|sql/${dbdriver}-tables.sql|;
+ my $filename = qq|sql/Pg-database.sql|;
$self->process_query($form, $dbh, $filename);
- # create functions
- $filename = qq|sql/${dbdriver}-functions.sql|;
- $self->process_query($form, $dbh, $filename);
-
# load gifi
($filename) = split /_/, $form->{chart};
$filename =~ s/_//;
@@ -383,9 +379,6 @@ sub dbcreate {
$filename = qq|sql/$form->{chart}-chart.sql|;
$self->process_query($form, $dbh, $filename);
- # create indices
- $filename = qq|sql/${dbdriver}-indices.sql|;
- $self->process_query($form, $dbh, $filename);
# create custom tables and functions
my $item;
diff --git a/sql/Pg-tables.sql b/sql/Pg-database.sql
index 92dfbf26..e3e96552 100755..100644
--- a/sql/Pg-tables.sql
+++ b/sql/Pg-database.sql
@@ -778,3 +778,423 @@ last_used TIMESTAMP default now(),
users_id INTEGER -- NOT NULL references users(id)
);
+create index acc_trans_trans_id_key on acc_trans (trans_id);
+create index acc_trans_chart_id_key on acc_trans (chart_id);
+create index acc_trans_transdate_key on acc_trans (transdate);
+create index acc_trans_source_key on acc_trans (lower(source));
+--
+create index ap_id_key on ap (id);
+create index ap_transdate_key on ap (transdate);
+create index ap_invnumber_key on ap (invnumber);
+create index ap_ordnumber_key on ap (ordnumber);
+create index ap_vendor_id_key on ap (vendor_id);
+create index ap_employee_id_key on ap (employee_id);
+create index ap_quonumber_key on ap (quonumber);
+--
+create index ar_id_key on ar (id);
+create index ar_transdate_key on ar (transdate);
+create index ar_invnumber_key on ar (invnumber);
+create index ar_ordnumber_key on ar (ordnumber);
+create index ar_customer_id_key on ar (customer_id);
+create index ar_employee_id_key on ar (employee_id);
+create index ar_quonumber_key on ar (quonumber);
+--
+create index assembly_id_key on assembly (id);
+--
+create index chart_id_key on chart (id);
+create unique index chart_accno_key on chart (accno);
+create index chart_category_key on chart (category);
+create index chart_link_key on chart (link);
+create index chart_gifi_accno_key on chart (gifi_accno);
+--
+create index customer_id_key on customer (id);
+create index customer_customernumber_key on customer (customernumber);
+create index customer_name_key on customer (lower(name));
+create index customer_contact_key on customer (lower(contact));
+create index customer_customer_id_key on customertax (customer_id);
+--
+create index employee_id_key on employee (id);
+create unique index employee_login_key on employee (login);
+create index employee_name_key on employee (lower(name));
+--
+create index exchangerate_ct_key on exchangerate (curr, transdate);
+--
+create unique index gifi_accno_key on gifi (accno);
+--
+create index gl_id_key on gl (id);
+create index gl_transdate_key on gl (transdate);
+create index gl_reference_key on gl (reference);
+create index gl_description_key on gl (lower(description));
+create index gl_employee_id_key on gl (employee_id);
+--
+create index invoice_id_key on invoice (id);
+create index invoice_trans_id_key on invoice (trans_id);
+--
+create index makemodel_parts_id_key on makemodel (parts_id);
+create index makemodel_make_key on makemodel (lower(make));
+create index makemodel_model_key on makemodel (lower(model));
+--
+create index oe_id_key on oe (id);
+create index oe_transdate_key on oe (transdate);
+create index oe_ordnumber_key on oe (ordnumber);
+create index oe_employee_id_key on oe (employee_id);
+create index orderitems_trans_id_key on orderitems (trans_id);
+create index orderitems_id_key on orderitems (id);
+--
+create index parts_id_key on parts (id);
+create index parts_partnumber_key on parts (lower(partnumber));
+create index parts_description_key on parts (lower(description));
+create index partstax_parts_id_key on partstax (parts_id);
+--
+create index vendor_id_key on vendor (id);
+create index vendor_name_key on vendor (lower(name));
+create index vendor_vendornumber_key on vendor (vendornumber);
+create index vendor_contact_key on vendor (lower(contact));
+create index vendortax_vendor_id_key on vendortax (vendor_id);
+--
+create index shipto_trans_id_key on shipto (trans_id);
+--
+create index project_id_key on project (id);
+create unique index projectnumber_key on project (projectnumber);
+--
+create index partsgroup_id_key on partsgroup (id);
+create unique index partsgroup_key on partsgroup (partsgroup);
+--
+create index status_trans_id_key on status (trans_id);
+--
+create index department_id_key on department (id);
+--
+create index partsvendor_vendor_id_key on partsvendor (vendor_id);
+create index partsvendor_parts_id_key on partsvendor (parts_id);
+--
+create index pricegroup_pricegroup_key on pricegroup (pricegroup);
+create index pricegroup_id_key on pricegroup (id);
+--
+create index audittrail_trans_id_key on audittrail (trans_id);
+--
+create index translation_trans_id_key on translation (trans_id);
+--
+create unique index language_code_key on language (code);
+--
+create index jcitems_id_key on jcitems (id);
+
+CREATE LANGUAGE plpgsql;
+--
+CREATE FUNCTION del_yearend() RETURNS OPAQUE AS '
+begin
+ delete from yearend where trans_id = old.id;
+ return NULL;
+end;
+' language 'plpgsql';
+-- end function
+--
+CREATE TRIGGER del_yearend AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_yearend();
+-- end trigger
+--
+CREATE FUNCTION del_department() RETURNS OPAQUE AS '
+begin
+ delete from dpt_trans where trans_id = old.id;
+ return NULL;
+end;
+' language 'plpgsql';
+-- end function
+--
+CREATE TRIGGER del_department AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_department();
+-- end trigger
+CREATE TRIGGER del_department AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_department();
+-- end trigger
+CREATE TRIGGER del_department AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_department();
+-- end trigger
+CREATE TRIGGER del_department AFTER DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_department();
+-- end trigger
+--
+CREATE FUNCTION del_customer() RETURNS OPAQUE AS '
+begin
+ delete from shipto where trans_id = old.id;
+ delete from customertax where customer_id = old.id;
+ delete from partscustomer where customer_id = old.id;
+ return NULL;
+end;
+' language 'plpgsql';
+-- end function
+--
+CREATE TRIGGER del_customer AFTER DELETE ON customer FOR EACH ROW EXECUTE PROCEDURE del_customer();
+-- end trigger
+--
+CREATE FUNCTION del_vendor() RETURNS OPAQUE AS '
+begin
+ delete from shipto where trans_id = old.id;
+ delete from vendortax where vendor_id = old.id;
+ delete from partsvendor where vendor_id = old.id;
+ return NULL;
+end;
+' language 'plpgsql';
+-- end function
+--
+CREATE TRIGGER del_vendor AFTER DELETE ON vendor FOR EACH ROW EXECUTE PROCEDURE del_vendor();
+-- end trigger
+--
+CREATE FUNCTION del_exchangerate() RETURNS OPAQUE AS '
+
+declare
+ t_transdate date;
+ t_curr char(3);
+ t_id int;
+ d_curr text;
+
+begin
+
+ select into d_curr substr(curr,1,3) from defaults;
+
+ if TG_RELNAME = ''ar'' then
+ select into t_curr, t_transdate curr, transdate from ar where id = old.id;
+ end if;
+ if TG_RELNAME = ''ap'' then
+ select into t_curr, t_transdate curr, transdate from ap where id = old.id;
+ end if;
+ if TG_RELNAME = ''oe'' then
+ select into t_curr, t_transdate curr, transdate from oe where id = old.id;
+ end if;
+
+ if d_curr != t_curr then
+
+ select into t_id a.id from acc_trans ac
+ join ar a on (a.id = ac.trans_id)
+ where a.curr = t_curr
+ and ac.transdate = t_transdate
+
+ except select a.id from ar a where a.id = old.id
+
+ union
+
+ select a.id from acc_trans ac
+ join ap a on (a.id = ac.trans_id)
+ where a.curr = t_curr
+ and ac.transdate = t_transdate
+
+ except select a.id from ap a where a.id = old.id
+
+ union
+
+ select o.id from oe o
+ where o.curr = t_curr
+ and o.transdate = t_transdate
+
+ except select o.id from oe o where o.id = old.id;
+
+ if not found then
+ delete from exchangerate where curr = t_curr and transdate = t_transdate;
+ end if;
+ end if;
+return old;
+
+end;
+' language 'plpgsql';
+-- end function
+--
+CREATE TRIGGER del_exchangerate BEFORE DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
+-- end trigger
+--
+CREATE TRIGGER del_exchangerate BEFORE DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
+-- end trigger
+--
+CREATE TRIGGER del_exchangerate BEFORE DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
+-- end trigger
+--
+CREATE FUNCTION check_inventory() RETURNS OPAQUE AS '
+
+declare
+ itemid int;
+ row_data inventory%rowtype;
+
+begin
+
+ if not old.quotation then
+ for row_data in select * from inventory where trans_id = old.id loop
+ select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
+
+ if itemid is null then
+ delete from inventory where trans_id = old.id and orderitems_id = row_data.orderitems_id;
+ end if;
+ end loop;
+ end if;
+return old;
+end;
+' language 'plpgsql';
+-- end function
+--
+CREATE TRIGGER check_inventory AFTER UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_inventory();
+-- end trigger
+--
+--
+CREATE FUNCTION check_department() RETURNS OPAQUE AS '
+
+declare
+ dpt_id int;
+
+begin
+
+ if new.department_id = 0 then
+ delete from dpt_trans where trans_id = new.id;
+ return NULL;
+ end if;
+
+ select into dpt_id trans_id from dpt_trans where trans_id = new.id;
+
+ if dpt_id > 0 then
+ update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
+ else
+ insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
+ end if;
+return NULL;
+
+end;
+' language 'plpgsql';
+-- end function
+--
+CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ar FOR EACH ROW EXECUTE PROCEDURE check_department();
+-- end trigger
+CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ap FOR EACH ROW EXECUTE PROCEDURE check_department();
+-- end trigger
+CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON gl FOR EACH ROW EXECUTE PROCEDURE check_department();
+-- end trigger
+CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_department();
+-- end trigger
+--
+CREATE FUNCTION del_recurring() returns opaque as '
+BEGIN
+ DELETE FROM recurring WHERE id = old.id;
+ DELETE FROM recurringemail WHERE id = old.id;
+ DELETE FROM recurringprint WHERE id = old.id;
+ RETURN NULL;
+END;
+' language 'plpgsql';
+--end function
+CREATE TRIGGER del_recurring AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_recurring();
+-- end trigger
+CREATE TRIGGER del_recurring AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_recurring();
+-- end trigger
+CREATE TRIGGER del_recurring AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_recurring();
+-- end trigger
+--
+CREATE FUNCTION avgcost(int) RETURNS FLOAT AS '
+
+DECLARE
+
+v_cost float;
+v_qty float;
+v_parts_id alias for $1;
+
+BEGIN
+
+ SELECT INTO v_cost, v_qty SUM(i.sellprice * i.qty), SUM(i.qty)
+ FROM invoice i
+ JOIN ap a ON (a.id = i.trans_id)
+ WHERE i.parts_id = v_parts_id;
+
+ IF v_cost IS NULL THEN
+ v_cost := 0;
+ END IF;
+
+ IF NOT v_qty IS NULL THEN
+ IF v_qty = 0 THEN
+ v_cost := 0;
+ ELSE
+ v_cost := v_cost/v_qty;
+ END IF;
+ END IF;
+
+RETURN v_cost;
+END;
+' language 'plpgsql';
+-- end function
+--
+CREATE FUNCTION lastcost(int) RETURNS FLOAT AS '
+
+DECLARE
+
+v_cost float;
+v_parts_id alias for $1;
+
+BEGIN
+
+ SELECT INTO v_cost sellprice FROM invoice i
+ JOIN ap a ON (a.id = i.trans_id)
+ WHERE i.parts_id = v_parts_id
+ ORDER BY a.transdate desc, a.id desc
+ LIMIT 1;
+
+ IF v_cost IS NULL THEN
+ v_cost := 0;
+ END IF;
+
+RETURN v_cost;
+END;
+' language plpgsql;
+-- end function
+--
+
+CREATE OR REPLACE FUNCTION trigger_parts_short() RETURNS TRIGGER
+AS
+'
+BEGIN
+ IF NEW.onhand >= NEW.rop THEN
+ NOTIFY parts_short;
+ END IF;
+ RETURN NEW;
+END;
+' LANGUAGE PLPGSQL;
+-- end function
+
+CREATE TRIGGER parts_short AFTER UPDATE ON parts
+FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short();
+-- end function
+
+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
+ 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
+ WHERE extends = ''''''|| table_name || ''''''))'';
+ EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN ''
+ || new_field_name || '' '' || field_datatype;
+ RETURN TRUE;
+END;
+' LANGUAGE PLPGSQL;
+-- end function
+
+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 '' || custom_field_name;
+ RETURN TRUE;
+END;
+' LANGUAGE PLPGSQL;
+-- end function
diff --git a/sql/Pg-functions.sql b/sql/Pg-functions.sql
deleted file mode 100755
index b42e2083..00000000
--- a/sql/Pg-functions.sql
+++ /dev/null
@@ -1,320 +0,0 @@
-CREATE LANGUAGE plpgsql;
---
-CREATE FUNCTION del_yearend() RETURNS OPAQUE AS '
-begin
- delete from yearend where trans_id = old.id;
- return NULL;
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER del_yearend AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_yearend();
--- end trigger
---
-CREATE FUNCTION del_department() RETURNS OPAQUE AS '
-begin
- delete from dpt_trans where trans_id = old.id;
- return NULL;
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER del_department AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_department();
--- end trigger
-CREATE TRIGGER del_department AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_department();
--- end trigger
-CREATE TRIGGER del_department AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_department();
--- end trigger
-CREATE TRIGGER del_department AFTER DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_department();
--- end trigger
---
-CREATE FUNCTION del_customer() RETURNS OPAQUE AS '
-begin
- delete from shipto where trans_id = old.id;
- delete from customertax where customer_id = old.id;
- delete from partscustomer where customer_id = old.id;
- return NULL;
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER del_customer AFTER DELETE ON customer FOR EACH ROW EXECUTE PROCEDURE del_customer();
--- end trigger
---
-CREATE FUNCTION del_vendor() RETURNS OPAQUE AS '
-begin
- delete from shipto where trans_id = old.id;
- delete from vendortax where vendor_id = old.id;
- delete from partsvendor where vendor_id = old.id;
- return NULL;
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER del_vendor AFTER DELETE ON vendor FOR EACH ROW EXECUTE PROCEDURE del_vendor();
--- end trigger
---
-CREATE FUNCTION del_exchangerate() RETURNS OPAQUE AS '
-
-declare
- t_transdate date;
- t_curr char(3);
- t_id int;
- d_curr text;
-
-begin
-
- select into d_curr substr(curr,1,3) from defaults;
-
- if TG_RELNAME = ''ar'' then
- select into t_curr, t_transdate curr, transdate from ar where id = old.id;
- end if;
- if TG_RELNAME = ''ap'' then
- select into t_curr, t_transdate curr, transdate from ap where id = old.id;
- end if;
- if TG_RELNAME = ''oe'' then
- select into t_curr, t_transdate curr, transdate from oe where id = old.id;
- end if;
-
- if d_curr != t_curr then
-
- select into t_id a.id from acc_trans ac
- join ar a on (a.id = ac.trans_id)
- where a.curr = t_curr
- and ac.transdate = t_transdate
-
- except select a.id from ar a where a.id = old.id
-
- union
-
- select a.id from acc_trans ac
- join ap a on (a.id = ac.trans_id)
- where a.curr = t_curr
- and ac.transdate = t_transdate
-
- except select a.id from ap a where a.id = old.id
-
- union
-
- select o.id from oe o
- where o.curr = t_curr
- and o.transdate = t_transdate
-
- except select o.id from oe o where o.id = old.id;
-
- if not found then
- delete from exchangerate where curr = t_curr and transdate = t_transdate;
- end if;
- end if;
-return old;
-
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER del_exchangerate BEFORE DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
--- end trigger
---
-CREATE TRIGGER del_exchangerate BEFORE DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
--- end trigger
---
-CREATE TRIGGER del_exchangerate BEFORE DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
--- end trigger
---
-CREATE FUNCTION check_inventory() RETURNS OPAQUE AS '
-
-declare
- itemid int;
- row_data inventory%rowtype;
-
-begin
-
- if not old.quotation then
- for row_data in select * from inventory where trans_id = old.id loop
- select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
-
- if itemid is null then
- delete from inventory where trans_id = old.id and orderitems_id = row_data.orderitems_id;
- end if;
- end loop;
- end if;
-return old;
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER check_inventory AFTER UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_inventory();
--- end trigger
---
---
-CREATE FUNCTION check_department() RETURNS OPAQUE AS '
-
-declare
- dpt_id int;
-
-begin
-
- if new.department_id = 0 then
- delete from dpt_trans where trans_id = new.id;
- return NULL;
- end if;
-
- select into dpt_id trans_id from dpt_trans where trans_id = new.id;
-
- if dpt_id > 0 then
- update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
- else
- insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
- end if;
-return NULL;
-
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ar FOR EACH ROW EXECUTE PROCEDURE check_department();
--- end trigger
-CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ap FOR EACH ROW EXECUTE PROCEDURE check_department();
--- end trigger
-CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON gl FOR EACH ROW EXECUTE PROCEDURE check_department();
--- end trigger
-CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_department();
--- end trigger
---
-CREATE FUNCTION del_recurring() returns opaque as '
-BEGIN
- DELETE FROM recurring WHERE id = old.id;
- DELETE FROM recurringemail WHERE id = old.id;
- DELETE FROM recurringprint WHERE id = old.id;
- RETURN NULL;
-END;
-' language 'plpgsql';
---end function
-CREATE TRIGGER del_recurring AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_recurring();
--- end trigger
-CREATE TRIGGER del_recurring AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_recurring();
--- end trigger
-CREATE TRIGGER del_recurring AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_recurring();
--- end trigger
---
-CREATE FUNCTION avgcost(int) RETURNS FLOAT AS '
-
-DECLARE
-
-v_cost float;
-v_qty float;
-v_parts_id alias for $1;
-
-BEGIN
-
- SELECT INTO v_cost, v_qty SUM(i.sellprice * i.qty), SUM(i.qty)
- FROM invoice i
- JOIN ap a ON (a.id = i.trans_id)
- WHERE i.parts_id = v_parts_id;
-
- IF v_cost IS NULL THEN
- v_cost := 0;
- END IF;
-
- IF NOT v_qty IS NULL THEN
- IF v_qty = 0 THEN
- v_cost := 0;
- ELSE
- v_cost := v_cost/v_qty;
- END IF;
- END IF;
-
-RETURN v_cost;
-END;
-' language 'plpgsql';
--- end function
---
-CREATE FUNCTION lastcost(int) RETURNS FLOAT AS '
-
-DECLARE
-
-v_cost float;
-v_parts_id alias for $1;
-
-BEGIN
-
- SELECT INTO v_cost sellprice FROM invoice i
- JOIN ap a ON (a.id = i.trans_id)
- WHERE i.parts_id = v_parts_id
- ORDER BY a.transdate desc, a.id desc
- LIMIT 1;
-
- IF v_cost IS NULL THEN
- v_cost := 0;
- END IF;
-
-RETURN v_cost;
-END;
-' language plpgsql;
--- end function
---
-
-CREATE OR REPLACE FUNCTION trigger_parts_short() RETURNS TRIGGER
-AS
-'
-BEGIN
- IF NEW.onhand >= NEW.rop THEN
- NOTIFY parts_short;
- END IF;
- RETURN NEW;
-END;
-' LANGUAGE PLPGSQL;
--- end function
-
-CREATE TRIGGER parts_short AFTER UPDATE ON parts
-FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short();
--- end function
-
-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
- 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
- WHERE extends = ''''''|| table_name || ''''''))'';
- EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN ''
- || new_field_name || '' '' || field_datatype;
- RETURN TRUE;
-END;
-' LANGUAGE PLPGSQL;
--- end function
-
-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 '' || custom_field_name;
- RETURN TRUE;
-END;
-' LANGUAGE PLPGSQL;
--- end function
diff --git a/sql/Pg-indices.sql b/sql/Pg-indices.sql
deleted file mode 100755
index 1da593f6..00000000
--- a/sql/Pg-indices.sql
+++ /dev/null
@@ -1,100 +0,0 @@
-create index acc_trans_trans_id_key on acc_trans (trans_id);
-create index acc_trans_chart_id_key on acc_trans (chart_id);
-create index acc_trans_transdate_key on acc_trans (transdate);
-create index acc_trans_source_key on acc_trans (lower(source));
---
-create index ap_id_key on ap (id);
-create index ap_transdate_key on ap (transdate);
-create index ap_invnumber_key on ap (invnumber);
-create index ap_ordnumber_key on ap (ordnumber);
-create index ap_vendor_id_key on ap (vendor_id);
-create index ap_employee_id_key on ap (employee_id);
-create index ap_quonumber_key on ap (quonumber);
---
-create index ar_id_key on ar (id);
-create index ar_transdate_key on ar (transdate);
-create index ar_invnumber_key on ar (invnumber);
-create index ar_ordnumber_key on ar (ordnumber);
-create index ar_customer_id_key on ar (customer_id);
-create index ar_employee_id_key on ar (employee_id);
-create index ar_quonumber_key on ar (quonumber);
---
-create index assembly_id_key on assembly (id);
---
-create index chart_id_key on chart (id);
-create unique index chart_accno_key on chart (accno);
-create index chart_category_key on chart (category);
-create index chart_link_key on chart (link);
-create index chart_gifi_accno_key on chart (gifi_accno);
---
-create index customer_id_key on customer (id);
-create index customer_customernumber_key on customer (customernumber);
-create index customer_name_key on customer (lower(name));
-create index customer_contact_key on customer (lower(contact));
-create index customer_customer_id_key on customertax (customer_id);
---
-create index employee_id_key on employee (id);
-create unique index employee_login_key on employee (login);
-create index employee_name_key on employee (lower(name));
---
-create index exchangerate_ct_key on exchangerate (curr, transdate);
---
-create unique index gifi_accno_key on gifi (accno);
---
-create index gl_id_key on gl (id);
-create index gl_transdate_key on gl (transdate);
-create index gl_reference_key on gl (reference);
-create index gl_description_key on gl (lower(description));
-create index gl_employee_id_key on gl (employee_id);
---
-create index invoice_id_key on invoice (id);
-create index invoice_trans_id_key on invoice (trans_id);
---
-create index makemodel_parts_id_key on makemodel (parts_id);
-create index makemodel_make_key on makemodel (lower(make));
-create index makemodel_model_key on makemodel (lower(model));
---
-create index oe_id_key on oe (id);
-create index oe_transdate_key on oe (transdate);
-create index oe_ordnumber_key on oe (ordnumber);
-create index oe_employee_id_key on oe (employee_id);
-create index orderitems_trans_id_key on orderitems (trans_id);
-create index orderitems_id_key on orderitems (id);
---
-create index parts_id_key on parts (id);
-create index parts_partnumber_key on parts (lower(partnumber));
-create index parts_description_key on parts (lower(description));
-create index partstax_parts_id_key on partstax (parts_id);
---
-create index vendor_id_key on vendor (id);
-create index vendor_name_key on vendor (lower(name));
-create index vendor_vendornumber_key on vendor (vendornumber);
-create index vendor_contact_key on vendor (lower(contact));
-create index vendortax_vendor_id_key on vendortax (vendor_id);
---
-create index shipto_trans_id_key on shipto (trans_id);
---
-create index project_id_key on project (id);
-create unique index projectnumber_key on project (projectnumber);
---
-create index partsgroup_id_key on partsgroup (id);
-create unique index partsgroup_key on partsgroup (partsgroup);
---
-create index status_trans_id_key on status (trans_id);
---
-create index department_id_key on department (id);
---
-create index partsvendor_vendor_id_key on partsvendor (vendor_id);
-create index partsvendor_parts_id_key on partsvendor (parts_id);
---
-create index pricegroup_pricegroup_key on pricegroup (pricegroup);
-create index pricegroup_id_key on pricegroup (id);
---
-create index audittrail_trans_id_key on audittrail (trans_id);
---
-create index translation_trans_id_key on translation (trans_id);
---
-create unique index language_code_key on language (code);
---
-create index jcitems_id_key on jcitems (id);
-