Added defined primary keys to all tables

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@143 4979c152-3d1c-0410-bac9-87ea11338e46

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);