From d1eef00925161c3fc0a148a44bd2d88c864e81ef Mon Sep 17 00:00:00 2001 From: einhverfr Date: Wed, 1 Nov 2006 05:24:34 +0000 Subject: 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 --- sql/Pg-database.sql | 1200 ++++++++++++++++++++++++++++++++++++++++++++++++++ sql/Pg-functions.sql | 320 -------------- sql/Pg-indices.sql | 100 ----- sql/Pg-tables.sql | 780 -------------------------------- 4 files changed, 1200 insertions(+), 1200 deletions(-) create mode 100644 sql/Pg-database.sql delete mode 100755 sql/Pg-functions.sql delete mode 100755 sql/Pg-indices.sql delete mode 100755 sql/Pg-tables.sql (limited to 'sql') diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql new file mode 100644 index 00000000..e3e96552 --- /dev/null +++ b/sql/Pg-database.sql @@ -0,0 +1,1200 @@ +-- +CREATE SEQUENCE id start 10000; +SELECT nextval ('id'); +-- +CREATE SEQUENCE invoiceid; +SELECT nextval ('invoiceid'); +-- +CREATE SEQUENCE orderitemsid; +SELECT nextval ('orderitemsid'); +-- +CREATE SEQUENCE jcitemsid; +SELECT nextval ('jcitemsid'); +-- + +CREATE TABLE transactions ( + id int PRIMARY KEY, + table_name text +); +-- +CREATE TABLE makemodel ( + parts_id int PRIMARY KEY, + make text, + model text +); +-- +CREATE TABLE gl ( + id int DEFAULT nextval ( 'id' ) PRIMARY KEY, + reference text, + description text, + transdate date DEFAULT current_date, + employee_id int, + notes text, + department_id int default 0 +); +-- +CREATE TABLE chart ( + id int DEFAULT nextval ( 'id' ) PRIMARY KEY, + accno text NOT NULL, + description text, + charttype char(1) DEFAULT 'A', + category char(1), + link text, + gifi_accno text, + contra bool DEFAULT 'f' +); +-- +CREATE TABLE gifi ( + accno text PRIMARY KEY, + description text +); +-- +CREATE TABLE defaults ( + setting_key text primary key, + value text +); +/* + inventory_accno_id int, + income_accno_id int, + expense_accno_id int, + fxgain_accno_id int, + fxloss_accno_id int, +*/ +\COPY defaults FROM stdin WITH DELIMITER | +sinumber|1 +sonumber|1 +yearend|1 +businessnumber|1 +version|1.2.0 +closedto|\N +revtrans|1 +ponumber|1 +sqnumber|1 +rfqnumber|1 +audittrail|0 +vinumber|1 +employeenumber|1 +partnumber|1 +customernumber|1 +vendornumber|1 +glnumber|1 +projectnumber|1 +\. +-- */ +CREATE TABLE acc_trans ( + trans_id int, + chart_id int NOT NULL REFERENCES chart (id), + amount NUMERIC, + transdate date DEFAULT current_date, + source text, + cleared bool DEFAULT 'f', + fx_transaction bool DEFAULT 'f', + project_id int, + memo text, + invoice_id int, + entry_id SERIAL PRIMARY KEY +); +-- +CREATE TABLE invoice ( + id int DEFAULT nextval ( 'invoiceid' ) PRIMARY KEY, + trans_id int, + parts_id int, + description text, + qty integer, + allocated integer, + sellprice NUMERIC, + fxsellprice NUMERIC, + discount float4, -- jd: check into this + assemblyitem bool DEFAULT 'f', + unit varchar(5), + project_id int, + deliverydate date, + serialnumber text, + notes text +); +-- +CREATE TABLE customer ( + id int default nextval('id') PRIMARY KEY, + name varchar(64), + address1 varchar(32), + address2 varchar(32), + city varchar(32), + state varchar(32), + zipcode varchar(10), + country varchar(32), + contact varchar(64), + phone varchar(20), + fax varchar(20), + email text, + notes text, + discount numeric, + taxincluded bool default 'f', + creditlimit NUMERIC default 0, + terms int2 default 0, + customernumber varchar(32), + cc text, + bcc text, + business_id int, + taxnumber varchar(32), + sic_code varchar(6), + iban varchar(34), + bic varchar(11), + employee_id int, + language_code varchar(6), + pricegroup_id int, + curr char(3), + startdate date, + enddate date +); +-- +-- +CREATE TABLE parts ( + id int DEFAULT nextval ( 'id' ) PRIMARY KEY, + partnumber text, + description text, + unit varchar(5), + listprice NUMERIC, + sellprice NUMERIC, + lastcost NUMERIC, + priceupdate date DEFAULT current_date, + weight numeric, + onhand numeric DEFAULT 0, + notes text, + makemodel bool DEFAULT 'f', + assembly bool DEFAULT 'f', + alternate bool DEFAULT 'f', + rop float4, -- jd: what is this + inventory_accno_id int, + income_accno_id int, + expense_accno_id int, + bin text, + obsolete bool DEFAULT 'f', + bom bool DEFAULT 'f', + image text, + drawing text, + microfiche text, + partsgroup_id int, + project_id int, + avgcost NUMERIC +); +-- +CREATE TABLE assembly ( + id int, + parts_id int, + qty numeric, + bom bool, + adj bool, + PRIMARY KEY (id, parts_id) +); +-- +CREATE TABLE ar ( + id int DEFAULT nextval ( 'id' ) PRIMARY KEY, + invnumber text, + transdate date DEFAULT current_date, + customer_id int, + taxincluded bool, + amount NUMERIC, + netamount NUMERIC, + paid NUMERIC, + datepaid date, + duedate date, + invoice bool DEFAULT 'f', + shippingpoint text, + terms int2 DEFAULT 0, + notes text, + curr char(3), + ordnumber text, + employee_id int, + till varchar(20), + quonumber text, + intnotes text, + department_id int default 0, + shipvia text, + language_code varchar(6), + ponumber text +); +-- +CREATE TABLE ap ( + id int DEFAULT nextval ( 'id' ) PRIMARY KEY, + invnumber text, + transdate date DEFAULT current_date, + vendor_id int, + taxincluded bool DEFAULT 'f', + amount NUMERIC, + netamount NUMERIC, + paid NUMERIC, + datepaid date, + duedate date, + invoice bool DEFAULT 'f', + ordnumber text, + curr char(3), + notes text, + employee_id int, + till varchar(20), + quonumber text, + intnotes text, + department_id int DEFAULT 0, + shipvia text, + language_code varchar(6), + ponumber text, + shippingpoint text, + terms int2 DEFAULT 0 +); +-- +CREATE TABLE partstax ( + parts_id int, + chart_id int, + PRIMARY KEY (parts_id, chart_id) +); +-- +CREATE TABLE taxmodule ( + taxmodule_id serial PRIMARY KEY, + taxmodulename text NOT NULL +); +-- +CREATE TABLE tax ( + chart_id int PRIMARY KEY, + rate numeric, + taxnumber text, + validto date, + pass integer DEFAULT 0 NOT NULL, + taxmodule_id int DEFAULT 1 NOT NULL, + FOREIGN KEY (chart_id) REFERENCES chart (id), + FOREIGN KEY (taxmodule_id) REFERENCES taxmodule (taxmodule_id) +); +-- +CREATE TABLE customertax ( + customer_id int, + chart_id int, + PRIMARY KEY (customer_id, chart_id) +); +-- +CREATE TABLE vendortax ( + vendor_id int, + chart_id int, + PRIMARY KEY (vendor_id, chart_id) +); +-- +CREATE TABLE oe ( + id int default nextval('id') PRIMARY KEY, + ordnumber text, + transdate date default current_date, + vendor_id int, + customer_id int, + amount NUMERIC, + netamount NUMERIC, + reqdate date, + taxincluded bool, + shippingpoint text, + notes text, + curr char(3), + employee_id int, + closed bool default 'f', + quotation bool default 'f', + quonumber text, + intnotes text, + department_id int default 0, + shipvia text, + language_code varchar(6), + ponumber text, + terms int2 DEFAULT 0 +); +-- +CREATE TABLE orderitems ( + id int default nextval('orderitemsid') PRIMARY KEY, + trans_id int, + parts_id int, + description text, + qty numeric, + sellprice NUMERIC, + discount numeric, + unit varchar(5), + project_id int, + reqdate date, + ship numeric, + serialnumber text, + notes text +); +-- +CREATE TABLE exchangerate ( + curr char(3), + transdate date, + buy numeric, + sell numeric, + PRIMARY KEY (curr, transdate) +); +-- +create table employee ( + id int default nextval('id') PRIMARY KEY, + login text, + name varchar(64), + address1 varchar(32), + address2 varchar(32), + city varchar(32), + state varchar(32), + zipcode varchar(10), + country varchar(32), + workphone varchar(20), + homephone varchar(20), + startdate date default current_date, + enddate date, + notes text, + role varchar(20), + sales bool default 'f', + email text, + ssn varchar(20), + iban varchar(34), + bic varchar(11), + managerid int, + employeenumber varchar(32), + dob date +); +-- +create table shipto ( + trans_id int, + shiptoname varchar(64), + shiptoaddress1 varchar(32), + shiptoaddress2 varchar(32), + shiptocity varchar(32), + shiptostate varchar(32), + shiptozipcode varchar(10), + shiptocountry varchar(32), + shiptocontact varchar(64), + shiptophone varchar(20), + shiptofax varchar(20), + shiptoemail text, + entry_id SERIAL PRIMARY KEY +); +-- +CREATE TABLE vendor ( + id int default nextval('id') PRIMARY KEY, + name varchar(64), + address1 varchar(32), + address2 varchar(32), + city varchar(32), + state varchar(32), + zipcode varchar(10), + country varchar(32), + contact varchar(64), + phone varchar(20), + fax varchar(20), + email text, + notes text, + terms int2 default 0, + taxincluded bool default 'f', + vendornumber varchar(32), + cc text, + bcc text, + gifi_accno varchar(30), + business_id int, + taxnumber varchar(32), + sic_code varchar(6), + discount numeric, + creditlimit numeric default 0, + iban varchar(34), + bic varchar(11), + employee_id int, + language_code varchar(6), + pricegroup_id int, + curr char(3), + startdate date, + enddate date +); +-- +CREATE TABLE project ( + id int default nextval('id') PRIMARY KEY, + projectnumber text, + description text, + startdate date, + enddate date, + parts_id int, + production numeric default 0, + completed numeric default 0, + customer_id int +); +-- +CREATE TABLE partsgroup ( + id int default nextval('id') PRIMARY KEY, + partsgroup text +); +-- +CREATE TABLE status ( + trans_id int PRIMARY KEY, + formname text, + printed bool default 'f', + emailed bool default 'f', + spoolfile text +); +-- +CREATE TABLE department ( + id int default nextval('id') PRIMARY KEY, + description text, + role char(1) default 'P' +); +-- +-- department transaction table +CREATE TABLE dpt_trans ( + trans_id int PRIMARY KEY, + department_id int +); +-- +-- business table +CREATE TABLE business ( + id int default nextval('id') PRIMARY KEY, + description text, + discount numeric +); +-- +-- SIC +CREATE TABLE sic ( + code varchar(6) PRIMARY KEY, + sictype char(1), + description text +); +-- +CREATE TABLE warehouse ( + id int default nextval('id') PRIMARY KEY, + description text +); +-- +CREATE TABLE inventory ( + warehouse_id int, + parts_id int, + trans_id int, + orderitems_id int, + qty numeric, + shippingdate date, + employee_id int, + entry_id SERIAL PRIMARY KEY +); +-- +CREATE TABLE yearend ( + trans_id int PRIMARY KEY, + transdate date +); +-- +CREATE TABLE partsvendor ( + vendor_id int, + parts_id int, + partnumber text, + leadtime int2, + lastcost NUMERIC, + curr char(3), + entry_id SERIAL PRIMARY KEY +); +-- +CREATE TABLE pricegroup ( + id int default nextval('id') PRIMARY KEY, + pricegroup text +); +-- +CREATE TABLE partscustomer ( + parts_id int, + customer_id int, + pricegroup_id int, + pricebreak numeric, + sellprice NUMERIC, + validfrom date, + validto date, + curr char(3), + entry_id SERIAL PRIMARY KEY +); +-- +CREATE TABLE language ( + code varchar(6) PRIMARY KEY, + description text +); +-- +CREATE TABLE audittrail ( + trans_id int, + tablename text, + reference text, + formname text, + action text, + transdate timestamp default current_timestamp, + employee_id int, + entry_id BIGSERIAL PRIMARY KEY +); +-- +CREATE TABLE translation ( + trans_id int, + language_code varchar(6), + description text, + PRIMARY KEY (trans_id, language_code) +); +-- +CREATE TABLE recurring ( + id int PRIMARY KEY, + reference text, + startdate date, + nextdate date, + enddate date, + repeat int2, + unit varchar(6), + howmany int, + payment bool default 'f' +); +-- +CREATE TABLE recurringemail ( + id int PRIMARY KEY, + formname text, + format text, + message text +); +-- +CREATE TABLE recurringprint ( + id int PRIMARY KEY, + formname text, + format text, + printer text +); +-- +CREATE TABLE jcitems ( + id int default nextval('jcitemsid') PRIMARY KEY, + project_id int, + parts_id int, + description text, + qty numeric, + allocated numeric, + sellprice NUMERIC, + fxsellprice NUMERIC, + serialnumber text, + checkedin timestamp with time zone, + checkedout timestamp with time zone, + employee_id int, + notes text +); + + +insert into transactions (id, table_name) SELECT id, 'ap' FROM ap; + +CREATE RULE ap_id_track_i AS ON insert TO ap +DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ap'); + +CREATE RULE ap_id_track_u AS ON update TO ap +DO UPDATE transactions SET id = new.id WHERE id = old.id; + +insert into transactions (id, table_name) SELECT id, 'ar' FROM ap; + +CREATE RULE ar_id_track_i AS ON insert TO ar +DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ar'); + +CREATE RULE ar_id_track_u AS ON update TO ar +DO UPDATE transactions SET id = new.id WHERE id = old.id; + +INSERT INTO transactions (id, table_name) SELECT id, 'business' FROM business; + +CREATE RULE business_id_track_i AS ON insert TO business +DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'business'); + +CREATE RULE business_id_track_u AS ON update TO business +DO UPDATE transactions SET id = new.id WHERE id = old.id; + +INSERT INTO transactions (id, table_name) SELECT id, 'chart' FROM chart; + +CREATE RULE chart_id_track_i AS ON insert TO chart +DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'chart'); + +CREATE RULE chart_id_track_u AS ON update TO chart +DO UPDATE transactions SET id = new.id WHERE id = old.id; + +INSERT INTO transactions (id, table_name) SELECT id, 'customer' FROM customer; + +CREATE RULE customer_id_track_i AS ON insert TO customer +DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'customer'); + +CREATE RULE customer_id_track_u AS ON update TO customer +DO UPDATE transactions SET id = new.id WHERE id = old.id; + +INSERT INTO transactions (id, table_name) SELECT id, 'department' FROM department; + +CREATE RULE department_id_track_i AS ON insert TO department +DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'department'); + +CREATE RULE department_id_track_u AS ON update TO department +DO UPDATE transactions SET id = new.id WHERE id = old.id; + +INSERT INTO transactions (id, table_name) SELECT id, 'employee' FROM employee; + +CREATE RULE employee_id_track_i AS ON insert TO employee +DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee'); + +CREATE RULE employee_id_track_u AS ON update TO employee +DO UPDATE transactions SET id = new.id WHERE id = old.id; + +INSERT INTO transactions (id, table_name) SELECT id, 'gl' FROM gl; + +CREATE RULE gl_id_track_i AS ON insert TO gl +DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'gl'); + +CREATE RULE gl_id_track_u AS ON update TO gl +DO UPDATE transactions SET id = new.id WHERE id = old.id; + +INSERT INTO transactions (id, table_name) SELECT id, 'oe' FROM oe; + +CREATE RULE oe_id_track_i AS ON insert TO oe +DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'oe'); + +CREATE RULE oe_id_track_u AS ON update TO oe +DO UPDATE transactions SET id = new.id WHERE id = old.id; + +INSERT INTO transactions (id, table_name) SELECT id, 'parts' FROM parts; + +CREATE RULE parts_id_track_i AS ON insert TO parts +DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'parts'); + +CREATE RULE parts_id_track_u AS ON update TO parts +DO UPDATE transactions SET id = new.id WHERE id = old.id; + +INSERT INTO transactions (id, table_name) SELECT id, 'partsgroup' FROM partsgroup; + +CREATE RULE partsgroup_id_track_i AS ON insert TO partsgroup +DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'partsgroup'); + +CREATE RULE partsgroup_id_track_u AS ON update TO partsgroup +DO UPDATE transactions SET id = new.id WHERE id = old.id; + +INSERT INTO transactions (id, table_name) SELECT id, 'pricegroup' FROM pricegroup; + +CREATE RULE pricegroup_id_track_i AS ON insert TO pricegroup +DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'pricegroup'); + +CREATE RULE pricegroup_id_track_u AS ON update TO pricegroup +DO UPDATE transactions SET id = new.id WHERE id = old.id; + +INSERT INTO transactions (id, table_name) SELECT id, 'project' FROM project; + +CREATE RULE project_id_track_i AS ON insert TO project +DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'project'); + +CREATE RULE project_id_track_u AS ON update TO project +DO UPDATE transactions SET id = new.id WHERE id = old.id; + +INSERT INTO transactions (id, table_name) SELECT id, 'vendor' FROM vendor; + +CREATE RULE vendor_id_track_i AS ON insert TO vendor +DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'vendor'); + +CREATE RULE employee_id_track_u AS ON update TO vendor +DO UPDATE transactions SET id = new.id WHERE id = old.id; + +INSERT INTO transactions (id, table_name) SELECT id, 'warehouse' FROM warehouse; + +CREATE RULE warehouse_id_track_i AS ON insert TO warehouse +DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee'); + +CREATE RULE warehouse_id_track_u AS ON update TO warehouse +DO UPDATE transactions 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 +); + +INSERT INTO taxmodule ( + taxmodule_id, taxmodulename + ) VALUES ( + 1, 'Simple' +); + +-- USERS stuff -- +CREATE TABLE users (id serial UNIQUE, username varchar(30) primary key); +COMMENT ON TABLE users IS +$$username is the actual primary key here because we don't want duplicate users$$; +CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred, + acs text, + address text, + businessnumber text, + company text, + countrycode text, + currency text, + dateformat text, + dbconnect text, + dbdriver text default 'Pg', + dbhost text default 'localhost', + dbname text, + dboptions text, + dbpasswd text, + dbport text, + dbuser text, + email text, + fax text, + menuwidth text, + name text, + numberformat text, + password varchar(32) check(length(password) = 32), + print text, + printer text, + role text, + sid text, + signature text, + stylesheet text, + tel text, + templates text, + timeout numeric, + vclimit numeric); +COMMENT ON TABLE users_conf IS 'This is a completely dumb table that is a place holder to get usersconf into the database. Next major release will have a much more sane implementation'; +COMMENT ON COLUMN users_conf.id IS 'Yes primary key with a FOREIGN KEY to users(id) is correct'; +COMMENT ON COLUMN users_conf.password IS 'This means we have to get rid of the current password stuff and move to presumably md5()'; + +-- Per conversation with ChriseH, if the admin user has a null password a couple of things happen. +-- 1. It is implicit that this is an initial install +-- 2. If the admin password does not match the ledger-smb.conf admin password, we throw a hijack alert +-- The two below statements must be run from a single session +INSERT INTO users(username) VALUES ('admin'); +INSERT INTO users_conf(id,password) VALUES (currval('users_id_seq'),NULL); + + +CREATE FUNCTION create_user(text) RETURNS int4 AS $$ + INSERT INTO users(username) VALUES ('$1'); + SELECT currval('user_id_seq'); + $$ LANGUAGE 'SQL'; + +COMMENT ON FUNCTION create_user(text) IS $$ Function to create user. Returns users.id if successful, else it is an error. $$; + +CREATE FUNCTION update_user(int4,text) RETURNS int4 AS $$ + UPDATE users SET username = '$2' WHERE id = $1; + SELECT 1; + $$ LANGUAGE 'SQL'; + +COMMENT ON FUNCTION update_user(int4,text) IS $$ Takes int4 which is users.id and text which is username. Will update username based on id. Username is unique $$; + + +-- Session tracking table + + +CREATE TABLE session( +session_id serial PRIMARY KEY, +sl_login VARCHAR(50), +token VARCHAR(32) CHECK(length(token) = 32), +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); - diff --git a/sql/Pg-tables.sql b/sql/Pg-tables.sql deleted file mode 100755 index 92dfbf26..00000000 --- a/sql/Pg-tables.sql +++ /dev/null @@ -1,780 +0,0 @@ --- -CREATE SEQUENCE id start 10000; -SELECT nextval ('id'); --- -CREATE SEQUENCE invoiceid; -SELECT nextval ('invoiceid'); --- -CREATE SEQUENCE orderitemsid; -SELECT nextval ('orderitemsid'); --- -CREATE SEQUENCE jcitemsid; -SELECT nextval ('jcitemsid'); --- - -CREATE TABLE transactions ( - id int PRIMARY KEY, - table_name text -); --- -CREATE TABLE makemodel ( - parts_id int PRIMARY KEY, - make text, - model text -); --- -CREATE TABLE gl ( - id int DEFAULT nextval ( 'id' ) PRIMARY KEY, - reference text, - description text, - transdate date DEFAULT current_date, - employee_id int, - notes text, - department_id int default 0 -); --- -CREATE TABLE chart ( - id int DEFAULT nextval ( 'id' ) PRIMARY KEY, - accno text NOT NULL, - description text, - charttype char(1) DEFAULT 'A', - category char(1), - link text, - gifi_accno text, - contra bool DEFAULT 'f' -); --- -CREATE TABLE gifi ( - accno text PRIMARY KEY, - description text -); --- -CREATE TABLE defaults ( - setting_key text primary key, - value text -); -/* - inventory_accno_id int, - income_accno_id int, - expense_accno_id int, - fxgain_accno_id int, - fxloss_accno_id int, -*/ -\COPY defaults FROM stdin WITH DELIMITER | -sinumber|1 -sonumber|1 -yearend|1 -businessnumber|1 -version|1.2.0 -closedto|\N -revtrans|1 -ponumber|1 -sqnumber|1 -rfqnumber|1 -audittrail|0 -vinumber|1 -employeenumber|1 -partnumber|1 -customernumber|1 -vendornumber|1 -glnumber|1 -projectnumber|1 -\. --- */ -CREATE TABLE acc_trans ( - trans_id int, - chart_id int NOT NULL REFERENCES chart (id), - amount NUMERIC, - transdate date DEFAULT current_date, - source text, - cleared bool DEFAULT 'f', - fx_transaction bool DEFAULT 'f', - project_id int, - memo text, - invoice_id int, - entry_id SERIAL PRIMARY KEY -); --- -CREATE TABLE invoice ( - id int DEFAULT nextval ( 'invoiceid' ) PRIMARY KEY, - trans_id int, - parts_id int, - description text, - qty integer, - allocated integer, - sellprice NUMERIC, - fxsellprice NUMERIC, - discount float4, -- jd: check into this - assemblyitem bool DEFAULT 'f', - unit varchar(5), - project_id int, - deliverydate date, - serialnumber text, - notes text -); --- -CREATE TABLE customer ( - id int default nextval('id') PRIMARY KEY, - name varchar(64), - address1 varchar(32), - address2 varchar(32), - city varchar(32), - state varchar(32), - zipcode varchar(10), - country varchar(32), - contact varchar(64), - phone varchar(20), - fax varchar(20), - email text, - notes text, - discount numeric, - taxincluded bool default 'f', - creditlimit NUMERIC default 0, - terms int2 default 0, - customernumber varchar(32), - cc text, - bcc text, - business_id int, - taxnumber varchar(32), - sic_code varchar(6), - iban varchar(34), - bic varchar(11), - employee_id int, - language_code varchar(6), - pricegroup_id int, - curr char(3), - startdate date, - enddate date -); --- --- -CREATE TABLE parts ( - id int DEFAULT nextval ( 'id' ) PRIMARY KEY, - partnumber text, - description text, - unit varchar(5), - listprice NUMERIC, - sellprice NUMERIC, - lastcost NUMERIC, - priceupdate date DEFAULT current_date, - weight numeric, - onhand numeric DEFAULT 0, - notes text, - makemodel bool DEFAULT 'f', - assembly bool DEFAULT 'f', - alternate bool DEFAULT 'f', - rop float4, -- jd: what is this - inventory_accno_id int, - income_accno_id int, - expense_accno_id int, - bin text, - obsolete bool DEFAULT 'f', - bom bool DEFAULT 'f', - image text, - drawing text, - microfiche text, - partsgroup_id int, - project_id int, - avgcost NUMERIC -); --- -CREATE TABLE assembly ( - id int, - parts_id int, - qty numeric, - bom bool, - adj bool, - PRIMARY KEY (id, parts_id) -); --- -CREATE TABLE ar ( - id int DEFAULT nextval ( 'id' ) PRIMARY KEY, - invnumber text, - transdate date DEFAULT current_date, - customer_id int, - taxincluded bool, - amount NUMERIC, - netamount NUMERIC, - paid NUMERIC, - datepaid date, - duedate date, - invoice bool DEFAULT 'f', - shippingpoint text, - terms int2 DEFAULT 0, - notes text, - curr char(3), - ordnumber text, - employee_id int, - till varchar(20), - quonumber text, - intnotes text, - department_id int default 0, - shipvia text, - language_code varchar(6), - ponumber text -); --- -CREATE TABLE ap ( - id int DEFAULT nextval ( 'id' ) PRIMARY KEY, - invnumber text, - transdate date DEFAULT current_date, - vendor_id int, - taxincluded bool DEFAULT 'f', - amount NUMERIC, - netamount NUMERIC, - paid NUMERIC, - datepaid date, - duedate date, - invoice bool DEFAULT 'f', - ordnumber text, - curr char(3), - notes text, - employee_id int, - till varchar(20), - quonumber text, - intnotes text, - department_id int DEFAULT 0, - shipvia text, - language_code varchar(6), - ponumber text, - shippingpoint text, - terms int2 DEFAULT 0 -); --- -CREATE TABLE partstax ( - parts_id int, - chart_id int, - PRIMARY KEY (parts_id, chart_id) -); --- -CREATE TABLE taxmodule ( - taxmodule_id serial PRIMARY KEY, - taxmodulename text NOT NULL -); --- -CREATE TABLE tax ( - chart_id int PRIMARY KEY, - rate numeric, - taxnumber text, - validto date, - pass integer DEFAULT 0 NOT NULL, - taxmodule_id int DEFAULT 1 NOT NULL, - FOREIGN KEY (chart_id) REFERENCES chart (id), - FOREIGN KEY (taxmodule_id) REFERENCES taxmodule (taxmodule_id) -); --- -CREATE TABLE customertax ( - customer_id int, - chart_id int, - PRIMARY KEY (customer_id, chart_id) -); --- -CREATE TABLE vendortax ( - vendor_id int, - chart_id int, - PRIMARY KEY (vendor_id, chart_id) -); --- -CREATE TABLE oe ( - id int default nextval('id') PRIMARY KEY, - ordnumber text, - transdate date default current_date, - vendor_id int, - customer_id int, - amount NUMERIC, - netamount NUMERIC, - reqdate date, - taxincluded bool, - shippingpoint text, - notes text, - curr char(3), - employee_id int, - closed bool default 'f', - quotation bool default 'f', - quonumber text, - intnotes text, - department_id int default 0, - shipvia text, - language_code varchar(6), - ponumber text, - terms int2 DEFAULT 0 -); --- -CREATE TABLE orderitems ( - id int default nextval('orderitemsid') PRIMARY KEY, - trans_id int, - parts_id int, - description text, - qty numeric, - sellprice NUMERIC, - discount numeric, - unit varchar(5), - project_id int, - reqdate date, - ship numeric, - serialnumber text, - notes text -); --- -CREATE TABLE exchangerate ( - curr char(3), - transdate date, - buy numeric, - sell numeric, - PRIMARY KEY (curr, transdate) -); --- -create table employee ( - id int default nextval('id') PRIMARY KEY, - login text, - name varchar(64), - address1 varchar(32), - address2 varchar(32), - city varchar(32), - state varchar(32), - zipcode varchar(10), - country varchar(32), - workphone varchar(20), - homephone varchar(20), - startdate date default current_date, - enddate date, - notes text, - role varchar(20), - sales bool default 'f', - email text, - ssn varchar(20), - iban varchar(34), - bic varchar(11), - managerid int, - employeenumber varchar(32), - dob date -); --- -create table shipto ( - trans_id int, - shiptoname varchar(64), - shiptoaddress1 varchar(32), - shiptoaddress2 varchar(32), - shiptocity varchar(32), - shiptostate varchar(32), - shiptozipcode varchar(10), - shiptocountry varchar(32), - shiptocontact varchar(64), - shiptophone varchar(20), - shiptofax varchar(20), - shiptoemail text, - entry_id SERIAL PRIMARY KEY -); --- -CREATE TABLE vendor ( - id int default nextval('id') PRIMARY KEY, - name varchar(64), - address1 varchar(32), - address2 varchar(32), - city varchar(32), - state varchar(32), - zipcode varchar(10), - country varchar(32), - contact varchar(64), - phone varchar(20), - fax varchar(20), - email text, - notes text, - terms int2 default 0, - taxincluded bool default 'f', - vendornumber varchar(32), - cc text, - bcc text, - gifi_accno varchar(30), - business_id int, - taxnumber varchar(32), - sic_code varchar(6), - discount numeric, - creditlimit numeric default 0, - iban varchar(34), - bic varchar(11), - employee_id int, - language_code varchar(6), - pricegroup_id int, - curr char(3), - startdate date, - enddate date -); --- -CREATE TABLE project ( - id int default nextval('id') PRIMARY KEY, - projectnumber text, - description text, - startdate date, - enddate date, - parts_id int, - production numeric default 0, - completed numeric default 0, - customer_id int -); --- -CREATE TABLE partsgroup ( - id int default nextval('id') PRIMARY KEY, - partsgroup text -); --- -CREATE TABLE status ( - trans_id int PRIMARY KEY, - formname text, - printed bool default 'f', - emailed bool default 'f', - spoolfile text -); --- -CREATE TABLE department ( - id int default nextval('id') PRIMARY KEY, - description text, - role char(1) default 'P' -); --- --- department transaction table -CREATE TABLE dpt_trans ( - trans_id int PRIMARY KEY, - department_id int -); --- --- business table -CREATE TABLE business ( - id int default nextval('id') PRIMARY KEY, - description text, - discount numeric -); --- --- SIC -CREATE TABLE sic ( - code varchar(6) PRIMARY KEY, - sictype char(1), - description text -); --- -CREATE TABLE warehouse ( - id int default nextval('id') PRIMARY KEY, - description text -); --- -CREATE TABLE inventory ( - warehouse_id int, - parts_id int, - trans_id int, - orderitems_id int, - qty numeric, - shippingdate date, - employee_id int, - entry_id SERIAL PRIMARY KEY -); --- -CREATE TABLE yearend ( - trans_id int PRIMARY KEY, - transdate date -); --- -CREATE TABLE partsvendor ( - vendor_id int, - parts_id int, - partnumber text, - leadtime int2, - lastcost NUMERIC, - curr char(3), - entry_id SERIAL PRIMARY KEY -); --- -CREATE TABLE pricegroup ( - id int default nextval('id') PRIMARY KEY, - pricegroup text -); --- -CREATE TABLE partscustomer ( - parts_id int, - customer_id int, - pricegroup_id int, - pricebreak numeric, - sellprice NUMERIC, - validfrom date, - validto date, - curr char(3), - entry_id SERIAL PRIMARY KEY -); --- -CREATE TABLE language ( - code varchar(6) PRIMARY KEY, - description text -); --- -CREATE TABLE audittrail ( - trans_id int, - tablename text, - reference text, - formname text, - action text, - transdate timestamp default current_timestamp, - employee_id int, - entry_id BIGSERIAL PRIMARY KEY -); --- -CREATE TABLE translation ( - trans_id int, - language_code varchar(6), - description text, - PRIMARY KEY (trans_id, language_code) -); --- -CREATE TABLE recurring ( - id int PRIMARY KEY, - reference text, - startdate date, - nextdate date, - enddate date, - repeat int2, - unit varchar(6), - howmany int, - payment bool default 'f' -); --- -CREATE TABLE recurringemail ( - id int PRIMARY KEY, - formname text, - format text, - message text -); --- -CREATE TABLE recurringprint ( - id int PRIMARY KEY, - formname text, - format text, - printer text -); --- -CREATE TABLE jcitems ( - id int default nextval('jcitemsid') PRIMARY KEY, - project_id int, - parts_id int, - description text, - qty numeric, - allocated numeric, - sellprice NUMERIC, - fxsellprice NUMERIC, - serialnumber text, - checkedin timestamp with time zone, - checkedout timestamp with time zone, - employee_id int, - notes text -); - - -insert into transactions (id, table_name) SELECT id, 'ap' FROM ap; - -CREATE RULE ap_id_track_i AS ON insert TO ap -DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ap'); - -CREATE RULE ap_id_track_u AS ON update TO ap -DO UPDATE transactions SET id = new.id WHERE id = old.id; - -insert into transactions (id, table_name) SELECT id, 'ar' FROM ap; - -CREATE RULE ar_id_track_i AS ON insert TO ar -DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ar'); - -CREATE RULE ar_id_track_u AS ON update TO ar -DO UPDATE transactions SET id = new.id WHERE id = old.id; - -INSERT INTO transactions (id, table_name) SELECT id, 'business' FROM business; - -CREATE RULE business_id_track_i AS ON insert TO business -DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'business'); - -CREATE RULE business_id_track_u AS ON update TO business -DO UPDATE transactions SET id = new.id WHERE id = old.id; - -INSERT INTO transactions (id, table_name) SELECT id, 'chart' FROM chart; - -CREATE RULE chart_id_track_i AS ON insert TO chart -DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'chart'); - -CREATE RULE chart_id_track_u AS ON update TO chart -DO UPDATE transactions SET id = new.id WHERE id = old.id; - -INSERT INTO transactions (id, table_name) SELECT id, 'customer' FROM customer; - -CREATE RULE customer_id_track_i AS ON insert TO customer -DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'customer'); - -CREATE RULE customer_id_track_u AS ON update TO customer -DO UPDATE transactions SET id = new.id WHERE id = old.id; - -INSERT INTO transactions (id, table_name) SELECT id, 'department' FROM department; - -CREATE RULE department_id_track_i AS ON insert TO department -DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'department'); - -CREATE RULE department_id_track_u AS ON update TO department -DO UPDATE transactions SET id = new.id WHERE id = old.id; - -INSERT INTO transactions (id, table_name) SELECT id, 'employee' FROM employee; - -CREATE RULE employee_id_track_i AS ON insert TO employee -DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee'); - -CREATE RULE employee_id_track_u AS ON update TO employee -DO UPDATE transactions SET id = new.id WHERE id = old.id; - -INSERT INTO transactions (id, table_name) SELECT id, 'gl' FROM gl; - -CREATE RULE gl_id_track_i AS ON insert TO gl -DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'gl'); - -CREATE RULE gl_id_track_u AS ON update TO gl -DO UPDATE transactions SET id = new.id WHERE id = old.id; - -INSERT INTO transactions (id, table_name) SELECT id, 'oe' FROM oe; - -CREATE RULE oe_id_track_i AS ON insert TO oe -DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'oe'); - -CREATE RULE oe_id_track_u AS ON update TO oe -DO UPDATE transactions SET id = new.id WHERE id = old.id; - -INSERT INTO transactions (id, table_name) SELECT id, 'parts' FROM parts; - -CREATE RULE parts_id_track_i AS ON insert TO parts -DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'parts'); - -CREATE RULE parts_id_track_u AS ON update TO parts -DO UPDATE transactions SET id = new.id WHERE id = old.id; - -INSERT INTO transactions (id, table_name) SELECT id, 'partsgroup' FROM partsgroup; - -CREATE RULE partsgroup_id_track_i AS ON insert TO partsgroup -DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'partsgroup'); - -CREATE RULE partsgroup_id_track_u AS ON update TO partsgroup -DO UPDATE transactions SET id = new.id WHERE id = old.id; - -INSERT INTO transactions (id, table_name) SELECT id, 'pricegroup' FROM pricegroup; - -CREATE RULE pricegroup_id_track_i AS ON insert TO pricegroup -DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'pricegroup'); - -CREATE RULE pricegroup_id_track_u AS ON update TO pricegroup -DO UPDATE transactions SET id = new.id WHERE id = old.id; - -INSERT INTO transactions (id, table_name) SELECT id, 'project' FROM project; - -CREATE RULE project_id_track_i AS ON insert TO project -DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'project'); - -CREATE RULE project_id_track_u AS ON update TO project -DO UPDATE transactions SET id = new.id WHERE id = old.id; - -INSERT INTO transactions (id, table_name) SELECT id, 'vendor' FROM vendor; - -CREATE RULE vendor_id_track_i AS ON insert TO vendor -DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'vendor'); - -CREATE RULE employee_id_track_u AS ON update TO vendor -DO UPDATE transactions SET id = new.id WHERE id = old.id; - -INSERT INTO transactions (id, table_name) SELECT id, 'warehouse' FROM warehouse; - -CREATE RULE warehouse_id_track_i AS ON insert TO warehouse -DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee'); - -CREATE RULE warehouse_id_track_u AS ON update TO warehouse -DO UPDATE transactions 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 -); - -INSERT INTO taxmodule ( - taxmodule_id, taxmodulename - ) VALUES ( - 1, 'Simple' -); - --- USERS stuff -- -CREATE TABLE users (id serial UNIQUE, username varchar(30) primary key); -COMMENT ON TABLE users IS -$$username is the actual primary key here because we don't want duplicate users$$; -CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred, - acs text, - address text, - businessnumber text, - company text, - countrycode text, - currency text, - dateformat text, - dbconnect text, - dbdriver text default 'Pg', - dbhost text default 'localhost', - dbname text, - dboptions text, - dbpasswd text, - dbport text, - dbuser text, - email text, - fax text, - menuwidth text, - name text, - numberformat text, - password varchar(32) check(length(password) = 32), - print text, - printer text, - role text, - sid text, - signature text, - stylesheet text, - tel text, - templates text, - timeout numeric, - vclimit numeric); -COMMENT ON TABLE users_conf IS 'This is a completely dumb table that is a place holder to get usersconf into the database. Next major release will have a much more sane implementation'; -COMMENT ON COLUMN users_conf.id IS 'Yes primary key with a FOREIGN KEY to users(id) is correct'; -COMMENT ON COLUMN users_conf.password IS 'This means we have to get rid of the current password stuff and move to presumably md5()'; - --- Per conversation with ChriseH, if the admin user has a null password a couple of things happen. --- 1. It is implicit that this is an initial install --- 2. If the admin password does not match the ledger-smb.conf admin password, we throw a hijack alert --- The two below statements must be run from a single session -INSERT INTO users(username) VALUES ('admin'); -INSERT INTO users_conf(id,password) VALUES (currval('users_id_seq'),NULL); - - -CREATE FUNCTION create_user(text) RETURNS int4 AS $$ - INSERT INTO users(username) VALUES ('$1'); - SELECT currval('user_id_seq'); - $$ LANGUAGE 'SQL'; - -COMMENT ON FUNCTION create_user(text) IS $$ Function to create user. Returns users.id if successful, else it is an error. $$; - -CREATE FUNCTION update_user(int4,text) RETURNS int4 AS $$ - UPDATE users SET username = '$2' WHERE id = $1; - SELECT 1; - $$ LANGUAGE 'SQL'; - -COMMENT ON FUNCTION update_user(int4,text) IS $$ Takes int4 which is users.id and text which is username. Will update username based on id. Username is unique $$; - - --- Session tracking table - - -CREATE TABLE session( -session_id serial PRIMARY KEY, -sl_login VARCHAR(50), -token VARCHAR(32) CHECK(length(token) = 32), -last_used TIMESTAMP default now(), -users_id INTEGER -- NOT NULL references users(id) -); - -- cgit v1.2.3