diff options
| -rwxr-xr-x | sql/Pg-tables.sql | 111 | ||||
| -rw-r--r-- | sql/Pg-upgrade-2.6.18-2.6.19.sql | 111 |
2 files changed, 174 insertions, 48 deletions
diff --git a/sql/Pg-tables.sql b/sql/Pg-tables.sql index 4e232798..8e49dcd4 100755 --- a/sql/Pg-tables.sql +++ b/sql/Pg-tables.sql @@ -11,14 +11,16 @@ SELECT nextval ('orderitemsid'); CREATE SEQUENCE jcitemsid; SELECT nextval ('jcitemsid'); -- + + CREATE TABLE makemodel ( - parts_id int, + parts_id int PRIMARY KEY, make text, model text ); -- CREATE TABLE gl ( - id int DEFAULT nextval ( 'id' ), + id int DEFAULT nextval ( 'id' ) PRIMAY KEY, reference text, description text, transdate date DEFAULT current_date, @@ -28,7 +30,7 @@ CREATE TABLE gl ( ); -- CREATE TABLE chart ( - id int DEFAULT nextval ( 'id' ), + id int DEFAULT nextval ( 'id' ) PRIMARY KEY, accno text NOT NULL, description text, charttype char(1) DEFAULT 'A', @@ -40,7 +42,7 @@ CREATE TABLE chart ( ); -- CREATE TABLE gifi ( - accno text, + accno text PRIMARY KEY, description text ); -- @@ -55,7 +57,7 @@ CREATE TABLE defaults ( yearend varchar(5), weightunit varchar(5), businessnumber text, - version varchar(8), + version varchar(8) PRIMARY KEY, curr text, closedto date, revtrans bool DEFAULT 't', @@ -71,7 +73,6 @@ CREATE TABLE defaults ( glnumber text, projectnumber text ); -INSERT INTO defaults (version) VALUES ('2.6.18'); -- CREATE TABLE acc_trans ( trans_id int, @@ -83,11 +84,12 @@ CREATE TABLE acc_trans ( fx_transaction bool DEFAULT 'f', project_id int, memo text, - invoice_id int + invoice_id int, + entry_id SERIAL PRIMARY KEY ); -- CREATE TABLE invoice ( - id int DEFAULT nextval ( 'invoiceid' ), + id int DEFAULT nextval ( 'invoiceid' ) PRIMARY KEY, trans_id int, parts_id int, description text, @@ -105,7 +107,7 @@ CREATE TABLE invoice ( ); -- CREATE TABLE customer ( - id int default nextval('id'), + id int default nextval('id') PRIMARY KEY, name varchar(64), address1 varchar(32), address2 varchar(32), @@ -140,7 +142,7 @@ CREATE TABLE customer ( -- -- CREATE TABLE parts ( - id int DEFAULT nextval ( 'id' ), + id int DEFAULT nextval ( 'id' ) PRIMARY KEY, partnumber text, description text, unit varchar(5), @@ -174,11 +176,12 @@ CREATE TABLE assembly ( parts_id int, qty numeric, bom bool, - adj bool -) WITH OIDS; + adj bool, + PRIMARY KEY (id, parts_id) +); -- CREATE TABLE ar ( - id int DEFAULT nextval ( 'id' ), + id int DEFAULT nextval ( 'id' ) PRIMARY KEY, invnumber text, transdate date DEFAULT current_date, customer_id int, @@ -205,7 +208,7 @@ CREATE TABLE ar ( ); -- CREATE TABLE ap ( - id int DEFAULT nextval ( 'id' ), + id int DEFAULT nextval ( 'id' ) PRIMARY KEY, invnumber text, transdate date DEFAULT current_date, vendor_id int, @@ -233,28 +236,32 @@ CREATE TABLE ap ( -- CREATE TABLE partstax ( parts_id int, - chart_id int + chart_id int, + PRIMARY KEY (parts_id, chart_id) ); -- CREATE TABLE tax ( - chart_id int, + chart_id int PRIMARY KEY, rate numeric, taxnumber text, - validto date + validto date, + FOREIGN KEY (chart_id) REFERENCES chart (id) ); -- CREATE TABLE customertax ( customer_id int, - chart_id int + chart_id int, + PRIMARY KEY (customer_id, chart_id) ); -- CREATE TABLE vendortax ( vendor_id int, - chart_id int + chart_id int, + PRIMARKY KEY (vendor_id, chart_id) ); -- CREATE TABLE oe ( - id int default nextval('id'), + id int default nextval('id') PRIMARY KEY, ordnumber text, transdate date default current_date, vendor_id int, @@ -279,7 +286,7 @@ CREATE TABLE oe ( ); -- CREATE TABLE orderitems ( - id int default nextval('orderitemsid'), + id int default nextval('orderitemsid') PRIMARY KEY, trans_id int, parts_id int, description text, @@ -292,17 +299,18 @@ CREATE TABLE orderitems ( ship numeric, serialnumber text, notes text -) WITH OIDS; +); -- CREATE TABLE exchangerate ( curr char(3), transdate date, buy numeric, - sell numeric + sell numeric, + PRIMARY KEY (curr, transdate) ); -- create table employee ( - id int default nextval('id'), + id int default nextval('id') PRIMARY KEY, login text, name varchar(64), address1 varchar(32), @@ -339,11 +347,12 @@ create table shipto ( shiptocontact varchar(64), shiptophone varchar(20), shiptofax varchar(20), - shiptoemail text + shiptoemail text, + entry_id SERIAL PRIMARY KEY ); -- CREATE TABLE vendor ( - id int default nextval('id'), + id int default nextval('id') PRIMARY KEY, name varchar(64), address1 varchar(32), address2 varchar(32), @@ -378,7 +387,7 @@ CREATE TABLE vendor ( ); -- CREATE TABLE project ( - id int default nextval('id'), + id int default nextval('id') PRIMARY KEY, projectnumber text, description text, startdate date, @@ -390,12 +399,12 @@ CREATE TABLE project ( ); -- CREATE TABLE partsgroup ( - id int default nextval('id'), + id int default nextval('id') PRIMARY KEY, partsgroup text ); -- CREATE TABLE status ( - trans_id int, + trans_id int PRIMARY KEY, formname text, printed bool default 'f', emailed bool default 'f', @@ -403,33 +412,33 @@ CREATE TABLE status ( ); -- CREATE TABLE department ( - id int default nextval('id'), + id int default nextval('id') PRIMARY KEY, description text, role char(1) default 'P' ); -- -- department transaction table CREATE TABLE dpt_trans ( - trans_id int, - department_id int + trans_id int PRIMARY KEY, + department_id int, ); -- -- business table CREATE TABLE business ( - id int default nextval('id'), + id int default nextval('id') PRIMARY KEY, description text, discount numeric ); -- -- SIC CREATE TABLE sic ( - code varchar(6), + code varchar(6) PRIMARY KEY, sictype char(1), description text ); -- CREATE TABLE warehouse ( - id int default nextval('id'), + id int default nextval('id') PRIMARY KEY, description text ); -- @@ -440,11 +449,12 @@ CREATE TABLE inventory ( orderitems_id int, qty numeric, shippingdate date, - employee_id int -) WITH OIDS; + employee_id int, + entry_id SERIAL PRIMARY KEY, +); -- CREATE TABLE yearend ( - trans_id int, + trans_id int PRIMARY KEY, transdate date ); -- @@ -454,11 +464,12 @@ CREATE TABLE partsvendor ( partnumber text, leadtime int2, lastcost NUMERIC, - curr char(3) + curr char(3), + entry_id SERIAL PRIMARY KEY ); -- CREATE TABLE pricegroup ( - id int default nextval('id'), + id int default nextval('id') PRIMARY KEY, pricegroup text ); -- @@ -470,11 +481,12 @@ CREATE TABLE partscustomer ( sellprice NUMERIC, validfrom date, validto date, - curr char(3) + curr char(3), + entry_id SERIAL PRIMARY KEY ); -- CREATE TABLE language ( - code varchar(6), + code varchar(6) SERIAL PRIMARY KEY, description text ); -- @@ -485,17 +497,19 @@ CREATE TABLE audittrail ( formname text, action text, transdate timestamp default current_timestamp, - employee_id int + employee_id int, + entry_id BIGSERIAL PRIMARY KEY ); -- CREATE TABLE translation ( trans_id int, language_code varchar(6), - description text + description text, + PRIMARY KEY (trans_id, language_code) ); -- CREATE TABLE recurring ( - id int, + id int PRIMARY KEY, reference text, startdate date, nextdate date, @@ -507,21 +521,21 @@ CREATE TABLE recurring ( ); -- CREATE TABLE recurringemail ( - id int, + id int PRIMARY KEY, formname text, format text, message text ); -- CREATE TABLE recurringprint ( - id int, + id int PRIMARY KEY, formname text, format text, printer text ); -- CREATE TABLE jcitems ( - id int default nextval('jcitemsid'), + id int default nextval('jcitemsid') PRIMARY KEY, project_id int, parts_id int, description text, @@ -685,3 +699,4 @@ field_id SERIAL PRIMARY KEY, table_id INT REFERENCES custom_table_catalog, field_name TEXT ); +INSERT INTO defaults (version) VALUES ('2.6.18'); diff --git a/sql/Pg-upgrade-2.6.18-2.6.19.sql b/sql/Pg-upgrade-2.6.18-2.6.19.sql index cf4abef2..5e338280 100644 --- a/sql/Pg-upgrade-2.6.18-2.6.19.sql +++ b/sql/Pg-upgrade-2.6.18-2.6.19.sql @@ -1 +1,112 @@ ALTER TABLE acc_trans ADD FOREIGN KEY trans_id REFERENCES transactions (id); + +ALTER TABLE ap ADD PRIMARY KEY (id); + +ALTER TABLE ar ADD PRIMARY KEY (id); + +ALTER TABLE assembly ADD PRIMARY KEY (id, parts_id); + +ALTER TABLE business ADD PRIMARY KEY (id); + +ALTER TABLE customer ADD PRIMARY KEY (id); + +ALTER TABLE customertax ADD PRIMARY KEY (customer_id, chart_id); + +ALTER TABLE defaults ADD PRIMARY KEY (version); + +ALTER TABLE department ADD PRIMARY KEY (id); + +ALTER TABLE dpt_trans ADD PRIMARY KEY (trans_id); + +ALTER TABLE employee ADD PRIMARY KEY (id); + +ALTER TABLE exchangerate ADD PRIMARY KEY (curr, transdate); + +ALTER TABLE gifi ADD PRIMARY KEY (accno); + +ALTER TABLE gl ADD PRIMARY KEY (id); + +ALTER TABLE invoice ADD PRIMARY KEY (id); + +ALTER TABLE jcitems ADD PRIMARY KEY (id); + +ALTER TABLE language ADD PRIMARY KEY (code); + +ALTER TABLE makemodel ADD PRIMARY KEY (parts_id); + +ALTER TABLE oe ADD PRIMARY KEY (id); + +ALTER TABLE orderitems ADD PRIMARY KEY (id); + +ALTER TABLE parts ADD PRIMARY KEY (id); + +ALTER TABLE partsgroup ADD PRIMARY KEY (id); + +ALTER TABLE partstax ADD PRIMARY KEY (parts_id, chart_id); + +ALTER TABLE pricegroup ADD PRIMARY KEY (id); + +ALTER TABLE project ADD PRIMARY KEY (id); + +ALTER TABLE recurringemail ADD PRIMARY KEY (id); + +ALTER TABLE recurring ADD PRIMARY KEY (id); + +ALTER TABLE recurringprint ADD PRIMARY KEY (id); + +ALTER TABLE sic ADD PRIMARY KEY (code); + +ALTER TABLE status ADD PRIMARY KEY (trans_id); + +ALTER TABLE tax ADD PRIMARY KEY (chart_id); +ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart (id); + +ALTER TABLE translation ADD PRIMARY KEY (trans_id, language_code); + +ALTER TABLE vendor ADD PRIMARY KEY (id); + +ALTER TABLE vendor_tax ADD PRIMARY KEY (vendor_id, chart_id); + +ALTER TABLE warehouse ADD PRIMARY KEY (id); + +ALTER TABLE yearend ADD PRIMARY KEY (trans_id); + +LOCK inventory in EXCLUSIVE mode; +ALTER TABLE inventory ADD COLUMN entry_id bigint; +CREATE SEQUENCE inventory_entry_id_seq; + +ALTER TABLE inventory ALTER COLUMN entry_id +SET DEFAULT nextval('inventory_entry_id_seq'); + +UPDATE inventory SET entry_id = nextval('inventory_entry_id_seq'); +ALTER TABLE inventory ADD PRIMARY key (entry_id); + +LOCK partscustomer IN EXCLUSIVE MODE; +ALTER TABLE partscustomer ADD COLUMN entry_id int; +CREATE SEQUENCE partscustomer_entry_id_seq; + +ALTER TABLE partscustomer ALTER COLUMN entry_id +SET DEFAULT nextval('partscustomer_entry_id_seq'); + +UPDATE partscustomer SET entry_id = nextval('partscustomer_entry_id_seq'); +ALTER TABLE partscustomer ADD PRIMARY KEY (entry_id); + +LOCK partsvendor IN EXCLUSIVE MODE; +ALTER TABLE partsvendor ADD COLUMN entry_id int; +CREATE SEQUENCE partsvendor_entry_id_seq; + +ALTER TABLE partsvendor ALTER COLUMN entry_id +SET DEFAULT nextval('partsvendor_entry_id_seq'); + +UPDATE partsvendor SET entry_id = nextval('partsvendor_entry_id_seq'); +ALTER TABLE partsvendor ADD PRIMARY KEY (entry_id); + +LOCK audit_trail IN EXCLUSIVE MODE; +ALTER TABLE audit_trail ADD COLUMN entry_id int; +CREATE SEQUENCE audit_trail_entry_id_seq ; + +ALTER TABLE audit_trail ALTER COLUMN entry_id +SET DEFAULT nextval('audit_trail_entry_id_seq'); + +UPDATE audit_trail SET entry_id = nextval('audit_trail_entry_id_seq'); +ALTER TABLE audit_trail ADD PRIMARY KEY (entry_id); |