summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--sql/Pg-database.sql133
1 files changed, 67 insertions, 66 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index 5fd33f58..abb5b713 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -2,6 +2,23 @@ begin;
CREATE SEQUENCE id;
-- As of 1.3 there is no central db anymore. --CT
+CREATE TABLE chart (
+ id serial PRIMARY KEY,
+ accno text NOT NULL,
+ description text,
+ charttype char(1) DEFAULT 'A',
+ category char(1),
+ link text,
+ gifi_accno text,
+ contra bool DEFAULT 'f'
+);
+--
+-- pricegroup added here due to references
+CREATE TABLE pricegroup (
+ id serial PRIMARY KEY,
+ pricegroup text
+);
+
-- BEGIN new entity management
CREATE TABLE entity_class (
id serial primary key,
@@ -44,40 +61,6 @@ CREATE TABLE entity_class_to_entity (
COMMENT ON TABLE entity_class_to_entity IS $$ Relation builder for classes to entity $$;
-CREATE TABLE entity_credit_account (
- id serial not null unique,
- entity_id int not null references entity(id) ON DELETE CASCADE,
- entity_class int not null references entity_class(id) check ( entity_class in (1,2) ),
- discount numeric,
- discount_terms int default 0,
- discount_account_id int references chart(id),
- taxincluded bool default 'f',
- creditlimit NUMERIC default 0,
- terms int2 default 0,
- meta_number varchar(32),
- cc text,
- bcc text,
- business_id int,
- language_code varchar(6),
- pricegroup_id int references pricegroup(id),
- curr char(3),
- startdate date DEFAULT CURRENT_DATE,
- enddate date,
- threshold numeric default 0,
- employee_id int references entity_employee(entity_id),
- primary_contact int references person(id),
- ar_ap_account_id int references chart(id),
- cash_account_id int references chart(id),
- PRIMARY KEY(entity_id, meta_number, entity_class)
-);
-
-CREATE UNIQUE INDEX entity_credit_ar_accno_idx_u
-ON entity_credit_account(meta_number)
-WHERE entity_class = 2;
-
-COMMENT ON INDEX entity_credit_ar_accno_idx_u IS
-$$This index is used to ensure that AR accounts are not reused.$$;
-
-- USERS stuff --
CREATE TABLE users (
id serial UNIQUE,
@@ -198,20 +181,6 @@ COMMENT ON TABLE company_to_location IS
$$ This table is used for locations generic to companies. For contract-bound
addresses, use eca_to_location instead $$;
-CREATE TABLE eca_to_location (
- location_id integer references location(id) not null,
- location_class integer not null references location_class(id),
- credit_id integer not null references entity_credit_account(id)
- ON DELETE CASCADE,
- PRIMARY KEY(location_id,credit_id));
-
-CREATE UNIQUE INDEX eca_to_location_billing_u ON eca_to_location(credit_id)
- WHERE location_class = 1;
-
-COMMENT ON TABLE eca_to_location IS
-$$ This table is used for locations bound to contracts. For generic contact
-addresses, use company_to_location instead $$;
-
CREATE TABLE salutation (
id serial unique,
salutation text primary key);
@@ -324,6 +293,40 @@ CREATE TABLE company_to_contact (
COMMENT ON TABLE company_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single company $$;
+CREATE TABLE entity_credit_account (
+ id serial not null unique,
+ entity_id int not null references entity(id) ON DELETE CASCADE,
+ entity_class int not null references entity_class(id) check ( entity_class in (1,2) ),
+ discount numeric,
+ discount_terms int default 0,
+ discount_account_id int references chart(id),
+ taxincluded bool default 'f',
+ creditlimit NUMERIC default 0,
+ terms int2 default 0,
+ meta_number varchar(32),
+ cc text,
+ bcc text,
+ business_id int,
+ language_code varchar(6),
+ pricegroup_id int references pricegroup(id),
+ curr char(3),
+ startdate date DEFAULT CURRENT_DATE,
+ enddate date,
+ threshold numeric default 0,
+ employee_id int references entity_employee(entity_id),
+ primary_contact int references person(id),
+ ar_ap_account_id int references chart(id),
+ cash_account_id int references chart(id),
+ PRIMARY KEY(entity_id, meta_number, entity_class)
+);
+
+CREATE UNIQUE INDEX entity_credit_ar_accno_idx_u
+ON entity_credit_account(meta_number)
+WHERE entity_class = 2;
+
+COMMENT ON INDEX entity_credit_ar_accno_idx_u IS
+$$This index is used to ensure that AR accounts are not reused.$$;
+
CREATE TABLE eca_to_contact (
credit_id integer not null references entity_credit_account(id)
ON DELETE CASCADE,
@@ -335,6 +338,20 @@ CREATE TABLE eca_to_contact (
COMMENT ON TABLE eca_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single vendor or customer account. For generic
contacts, use company_to_contact or person_to_contact instead.$$;
+CREATE TABLE eca_to_location (
+ location_id integer references location(id) not null,
+ location_class integer not null references location_class(id),
+ credit_id integer not null references entity_credit_account(id)
+ ON DELETE CASCADE,
+ PRIMARY KEY(location_id,credit_id));
+
+CREATE UNIQUE INDEX eca_to_location_billing_u ON eca_to_location(credit_id)
+ WHERE location_class = 1;
+
+COMMENT ON TABLE eca_to_location IS
+$$ This table is used for locations bound to contracts. For generic contact
+addresses, use company_to_location instead $$;
+
-- Begin rocking notes interface
-- Begin rocking notes interface
CREATE TABLE note_class(id serial primary key, class text not null check (class ~ '[[:alnum:]_]'));
@@ -386,17 +403,6 @@ CREATE TABLE gl (
department_id int default 0
);
--
-CREATE TABLE chart (
- id serial PRIMARY KEY,
- accno text NOT NULL,
- description text,
- charttype char(1) DEFAULT 'A',
- category char(1),
- link text,
- gifi_accno text,
- contra bool DEFAULT 'f'
-);
---
CREATE TABLE gifi (
accno text PRIMARY KEY,
description text
@@ -530,14 +536,9 @@ ALTER TABLE invoice_note ADD FOREIGN KEY (ref_key) REFERENCES invoice(id);
--
--- pricegroup added here due to references
-CREATE TABLE pricegroup (
- id serial PRIMARY KEY,
- pricegroup text
-);
-
-- THe following credit accounts are used for inventory adjustments.
-INSERT INTO entity (name, entity_class) values ('Inventory Entity', 1);
+INSERT INTO entity (name, entity_class, control_code)
+values ('Inventory Entity', 1, 'AUTO-01');
INSERT INTO company (legal_name, entity_id)
values ('Inventory Entity', currval('entity_id_seq'));