diff options
| -rw-r--r-- | Changelog | 1 | ||||
| -rwxr-xr-x | LedgerSMB/User.pm | 9 | ||||
| -rw-r--r--[-rwxr-xr-x] | sql/Pg-database.sql (renamed from sql/Pg-tables.sql) | 420 | ||||
| -rwxr-xr-x | sql/Pg-functions.sql | 320 | ||||
| -rwxr-xr-x | sql/Pg-indices.sql | 100 |
5 files changed, 422 insertions, 428 deletions
@@ -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); - |