diff options
| -rw-r--r-- | sql/Pg-database.sql | 82 |
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); |