[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [143] trunk/sql
- Subject: SF.net SVN: ledger-smb: [143] trunk/sql
- From: ..hidden..
- Date: Sun, 24 Sep 2006 14:58:21 -0700
Revision: 143
http://svn.sourceforge.net/ledger-smb/?rev=143&view=rev
Author: einhverfr
Date: 2006-09-24 14:58:19 -0700 (Sun, 24 Sep 2006)
Log Message:
-----------
Added defined primary keys to all tables
Modified Paths:
--------------
trunk/sql/Pg-tables.sql
trunk/sql/Pg-upgrade-2.6.18-2.6.19.sql
Modified: trunk/sql/Pg-tables.sql
===================================================================
--- trunk/sql/Pg-tables.sql 2006-09-24 17:44:51 UTC (rev 142)
+++ trunk/sql/Pg-tables.sql 2006-09-24 21:58:19 UTC (rev 143)
@@ -11,14 +11,16 @@
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 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 gifi (
- accno text,
+ accno text PRIMARY KEY,
description text
);
--
@@ -55,7 +57,7 @@
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 @@
glnumber text,
projectnumber text
);
-INSERT INTO defaults (version) VALUES ('2.6.18');
--
CREATE TABLE acc_trans (
trans_id int,
@@ -83,11 +84,12 @@
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 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 parts (
- id int DEFAULT nextval ( 'id' ),
+ id int DEFAULT nextval ( 'id' ) PRIMARY KEY,
partnumber text,
description text,
unit varchar(5),
@@ -174,11 +176,12 @@
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 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 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 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 @@
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 @@
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 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 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 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 @@
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 @@
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 @@
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 @@
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 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 @@
table_id INT REFERENCES custom_table_catalog,
field_name TEXT
);
+INSERT INTO defaults (version) VALUES ('2.6.18');
Modified: trunk/sql/Pg-upgrade-2.6.18-2.6.19.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.6.18-2.6.19.sql 2006-09-24 17:44:51 UTC (rev 142)
+++ trunk/sql/Pg-upgrade-2.6.18-2.6.19.sql 2006-09-24 21:58:19 UTC (rev 143)
@@ -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);
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.