man that was more work that it should of been. there is no employee(id), vendor(id) anymore. Refer to key checks. Also fixed rules associated to compile, but may not be correct

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

1 files changed, 43 insertions, 39 deletions

diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index 3cbd08a8..cab04b00 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -5,39 +5,11 @@ CREATE TABLE transactions (
   table_name text
 );
 
-CREATE TABLE batch_class (
-  id serial unique,
-  class varchar primary key
-);
-
-insert into batch_class (batch_class) values ('ap');
-insert into batch_class (batch_class) values ('ar');
-insert into batch_class (batch_class) values ('payment');
-insert into batch_class (batch_class) values ('payment_reversal');
-insert into batch_class (batch_class) values ('gl');
-
-CREATE TABLE batch (
-  id serial unique,
-  batch_class_id references class(id) not null,
-  description text,
-  approved_on date default null,
-  approved_by int references employee(entity_id),
-  created_by int references employee(entity_id),
-  locked_by int references session(id),
-  created_on date default now(),
-);
-
-CREATE TABLE voucher (
-  trans_id int,
-  batch_id int,
-  id serial primary key
-);
-
 -- BEGIN new entity management
 CREATE TABLE entity (
   id serial PRIMARY KEY,
   name text check (name ~ '[[:alnum:]_]'),
-  entity_class integer not null,
+  entity_class integer not null UNIQUE,
   created date not null default current_date);
 
 COMMENT ON TABLE entity IS $$ The primary entity table to map to all contacts $$;
@@ -256,7 +228,7 @@ CREATE TABLE gl (
   transdate date DEFAULT current_date,
   person_id integer references person(id),
   notes text,
-  approved bool default true;
+  approved bool default true,
   department_id int default 0
 );
 --
@@ -440,7 +412,7 @@ CREATE TABLE ar (
   language_code varchar(6),
   ponumber text,
   on_hold bool default false,
-  approved bool default true;
+  approved bool default true
 );
 
 COMMENT ON COLUMN ar.entity_id IS $$ Used to be customer_id, but customer is now metadata. You need to push to entity $$;
@@ -471,7 +443,7 @@ CREATE TABLE ap (
   ponumber text,
   shippingpoint text,
   on_hold bool default false,
-  approved bool default true;
+  approved bool default true,
   terms int2 DEFAULT 0
 );
 
@@ -588,7 +560,7 @@ CREATE TABLE exchangerate (
 --
 create table employee (
   entity_id integer references entity(id) not null PRIMARY KEY,
-  entity_class integer references entity_class(id) not null check (entity_class = 3)),
+  entity_class_id integer references entity_class(id) not null check (entity_class_id = 3),
   login text,
   startdate date default current_date,
   enddate date,
@@ -605,6 +577,38 @@ create table employee (
 
 COMMENT ON TABLE employee IS $$ Is a metadata table specific to employees $$;
 
+-- batch stuff
+
+CREATE TABLE batch_class (
+  id serial unique,
+  class varchar primary key
+);
+
+insert into batch_class (id,class) values (1,'ap');
+insert into batch_class (id,class) values (2,'ar');
+insert into batch_class (id,class) values (3,'payment');
+insert into batch_class (id,class) values (4,'payment_reversal');
+insert into batch_class (id,class) values (5,'gl');
+
+SELECT SETVAL('batch_class_id_seq',6);
+
+CREATE TABLE batch (
+  id serial primary key,
+  batch_class_id integer references batch_class(id) not null,
+  description text,
+  approved_on date default null,
+  approved_by int references employee(entity_id),
+  created_by int references employee(entity_id),
+  locked_by int references session(session_id),
+  created_on date default now()
+);
+
+CREATE TABLE voucher (
+  trans_id int,
+  batch_id int references batch(id) not null,
+  id serial primary key
+);
+
 --
 create table shipto (
   trans_id int,
@@ -626,8 +630,8 @@ create table shipto (
 
 --
 CREATE TABLE vendor (
-  id serial PRIMARY KEY,
-  entity_id int references entity(id) not null,
+  entity_id int references entity(id) not null PRIMARY KEY,
+  entity_class_id int references entity(entity_class) not null check (entity_class_id = 1),
   terms int2 default 0,
   taxincluded bool default 'f',
   vendornumber varchar(32),
@@ -867,7 +871,7 @@ 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;
+INSERT INTO transactions (id, table_name) SELECT entity_id, 'employee' FROM employee;
 
 INSERT INTO transactions (id, table_name) SELECT id, 'gl' FROM gl;
 
@@ -917,10 +921,10 @@ 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;
+INSERT INTO transactions (id, table_name) SELECT entity_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');
+DO INSERT INTO transactions (id, table_name) VALUES (new.entity_id, 'vendor');
 
 INSERT INTO transactions (id, table_name) SELECT id, 'warehouse' FROM warehouse;
 
@@ -1006,7 +1010,7 @@ 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_entity_id_key on vendor (entity_id);
 create index vendor_vendornumber_key on vendor (vendornumber);
 --
 create index shipto_trans_id_key on shipto (trans_id);