summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rw-r--r--sql/Pg-database.sql82
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);