diff options
| -rw-r--r-- | sql/Pg-database.sql | 424 |
1 files changed, 423 insertions, 1 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index 16615c3f..c2300457 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -1,4 +1,426 @@ -defaults ( +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, + class text check (class ~ '[[:alnum:]_]') NOT NULL, + active boolean not null default TRUE); + +COMMENT ON TABLE entity_class IS $$ Defines the class type such as vendor, customer, contact, employee $$; +COMMENT ON COLUMN entity_class.id IS $$ The first 7 values are reserved and permanent $$; + +CREATE index entity_class_idx ON entity_class(lower(class)); + +CREATE TABLE entity ( + id serial UNIQUE, + name text check (name ~ '[[:alnum:]_]'), + entity_class integer references entity_class(id) not null , + created date not null default current_date, + control_code text, + PRIMARY KEY(control_code, entity_class)); + +COMMENT ON TABLE entity IS $$ The primary entity table to map to all contacts $$; +COMMENT ON COLUMN entity.name IS $$ This is the common name of an entity. If it was a person it may be Joshua Drake, a company Acme Corp. You may also choose to use a domain such as commandprompt.com $$; + + +ALTER TABLE entity ADD FOREIGN KEY (entity_class) REFERENCES entity_class(id); + +INSERT INTO entity_class (id,class) VALUES (1,'Vendor'); +INSERT INTO entity_class (id,class) VALUES (2,'Customer'); +INSERT INTO entity_class (id,class) VALUES (3,'Employee'); +INSERT INTO entity_class (id,class) VALUES (4,'Contact'); +INSERT INTO entity_class (id,class) VALUES (5,'Lead'); +INSERT INTO entity_class (id,class) VALUES (6,'Referral'); + +SELECT setval('entity_class_id_seq',7); + +CREATE TABLE entity_class_to_entity ( + entity_class_id integer not null references entity_class(id) ON DELETE CASCADE, + entity_id integer not null references entity(id) ON DELETE CASCADE, + PRIMARY KEY(entity_class_id,entity_id) + ); + +COMMENT ON TABLE entity_class_to_entity IS $$ Relation builder for classes to entity $$; + +-- USERS stuff -- +CREATE TABLE users ( + id serial UNIQUE, + username varchar(30) primary key, + entity_id int not null references entity(id) on delete cascade +); + +COMMENT ON TABLE users IS $$username is the actual primary key here because we do not want duplicate users$$; + +-- Session tracking table + + +CREATE TABLE session( +session_id serial PRIMARY KEY, +token VARCHAR(32) CHECK(length(token) = 32), +last_used TIMESTAMP default now(), +ttl int default 3600 not null, +users_id INTEGER NOT NULL references users(id), +transaction_id INTEGER NOT NULL +); + +CREATE TABLE open_forms ( +id SERIAL PRIMARY KEY, +session_id int REFERENCES session(session_id) ON DELETE CASCADE +); + +-- +CREATE TABLE transactions ( + id int PRIMARY KEY, + table_name text, + locked_by int references "session" (session_id) ON DELETE SET NULL, + approved_by int references entity (id), + approved_at timestamp +); + +COMMENT on TABLE transactions IS +$$ This table tracks basic transactions across AR, AP, and GL related tables. +It provies a referential integrity enforcement mechanism for the financial data +and also some common features such as discretionary (and pessimistic) locking +for long batch workflows. $$; + +CREATE OR REPLACE FUNCTION lock_record (int, int) returns bool as +$$ +declare + locked int; +begin + SELECT locked_by into locked from transactions where id = $1; + IF NOT FOUND THEN + RETURN FALSE; + ELSEIF locked is not null AND locked <> $2 THEN + RETURN FALSE; + END IF; + UPDATE transactions set locked_by = $2 where id = $1; + RETURN TRUE; +end; +$$ language plpgsql; + +COMMENT ON column transactions.locked_by IS +$$ This should only be used in pessimistic locking measures as required by large +batch work flows. $$; + +-- LOCATION AND COUNTRY +CREATE TABLE country ( + id serial PRIMARY KEY, + name text check (name ~ '[[:alnum:]_]') NOT NULL, + short_name text check (short_name ~ '[[:alnum:]_]') NOT NULL, + itu text); + +COMMENT ON COLUMN country.itu IS $$ The ITU Telecommunication Standardization Sector code for calling internationally. For example, the US is 1, Great Britain is 44 $$; + +CREATE UNIQUE INDEX country_name_idx on country(lower(name)); + +CREATE TABLE location_class ( + id serial UNIQUE, + class text check (class ~ '[[:alnum:]_]') not null, + authoritative boolean not null, + PRIMARY KEY (class,authoritative)); + +CREATE UNIQUE INDEX lower_class_unique ON location_class(lower(class)); + +INSERT INTO location_class(id,class,authoritative) VALUES ('1','Billing',TRUE); +INSERT INTO location_class(id,class,authoritative) VALUES ('2','Sales',TRUE); +INSERT INTO location_class(id,class,authoritative) VALUES ('3','Shipping',TRUE); + +SELECT SETVAL('location_class_id_seq',4); + +CREATE TABLE location ( + id serial PRIMARY KEY, + line_one text check (line_one ~ '[[:alnum:]_]') NOT NULL, + line_two text, + line_three text, + city text check (city ~ '[[:alnum:]_]') NOT NULL, + state text check(state ~ '[[:alnum:]_]'), + country_id integer not null REFERENCES country(id), + mail_code text not null check (mail_code ~ '[[:alnum:]_]'), + created date not null default now(), + inactive_date timestamp default null, + active boolean not null default TRUE +); + +CREATE TABLE company ( + id serial UNIQUE, + entity_id integer not null references entity(id), + legal_name text check (legal_name ~ '[[:alnum:]_]'), + tax_id text, + created date default current_date not null, + PRIMARY KEY (entity_id,legal_name)); + +COMMENT ON COLUMN company.tax_id IS $$ In the US this would be a EIN. $$; + +CREATE TABLE company_to_location ( + location_id integer references location(id) not null, + location_class integer not null references location_class(id), + company_id integer not null references company(id) ON DELETE CASCADE, + PRIMARY KEY(location_id,company_id, location_class)); + +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 salutation ( + id serial unique, + salutation text primary key); + +INSERT INTO salutation (id,salutation) VALUES ('1','Dr.'); +INSERT INTO salutation (id,salutation) VALUES ('2','Miss.'); +INSERT INTO salutation (id,salutation) VALUES ('3','Mr.'); +INSERT INTO salutation (id,salutation) VALUES ('4','Mrs.'); +INSERT INTO salutation (id,salutation) VALUES ('5','Ms.'); +INSERT INTO salutation (id,salutation) VALUES ('6','Sir.'); + +SELECT SETVAL('salutation_id_seq',7); + +CREATE TABLE person ( + id serial PRIMARY KEY, + entity_id integer references entity(id) not null, + salutation_id integer references salutation(id), + first_name text check (first_name ~ '[[:alnum:]_]') NOT NULL, + middle_name text, + last_name text check (last_name ~ '[[:alnum:]_]') NOT NULL, + created date not null default current_date + ); + +COMMENT ON TABLE person IS $$ Every person, must have an entity to derive a common or display name. The correct way to get class information on a person would be person.entity_id->entity_class_to_entity.entity_id. $$; + +create table entity_employee ( + + person_id integer references person(id) not null, + entity_id integer references entity(id) not null unique, + startdate date not null default current_date, + enddate date, + role varchar(20), + ssn text, + sales bool default 'f', + manager_id integer references entity(id), + employeenumber varchar(32), + dob date, + PRIMARY KEY (person_id, entity_id) +); + +CREATE TABLE person_to_location ( + location_id integer not null references location(id), + location_class integer not null references location_class(id), + person_id integer not null references person(id) ON DELETE CASCADE, + PRIMARY KEY (location_id,person_id)); + +CREATE TABLE person_to_company ( + location_id integer references location(id) not null, + person_id integer not null references person(id) ON DELETE CASCADE, + company_id integer not null references company(id) ON DELETE CASCADE, + PRIMARY KEY (location_id,person_id)); + +CREATE TABLE entity_other_name ( + entity_id integer not null references entity(id) ON DELETE CASCADE, + other_name text check (other_name ~ '[[:alnum:]_]'), + PRIMARY KEY (other_name, entity_id)); + +COMMENT ON TABLE entity_other_name IS $$ Similar to company_other_name, a person may be jd, Joshua Drake, linuxpoet... all are the same person. $$; + +CREATE TABLE person_to_entity ( + person_id integer not null references person(id) ON DELETE CASCADE, + entity_id integer not null check (entity_id != person_id) references entity(id) ON DELETE CASCADE, + related_how text, + created date not null default current_date, + PRIMARY KEY (person_id,entity_id)); + +CREATE TABLE company_to_entity ( + company_id integer not null references company(id) ON DELETE CASCADE, + entity_id integer check (company_id != entity_id) not null references entity(id) ON DELETE CASCADE, + related_how text, + created date not null default current_date, + PRIMARY KEY (company_id,entity_id)); + +CREATE TABLE contact_class ( + id serial UNIQUE, + class text check (class ~ '[[:alnum:]_]') NOT NULL, + PRIMARY KEY (class)); + +CREATE UNIQUE INDEX contact_class_class_idx ON contact_class(lower(class)); + +INSERT INTO contact_class (id,class) values (1,'Primary Phone'); +INSERT INTO contact_class (id,class) values (2,'Secondary Phone'); +INSERT INTO contact_class (id,class) values (3,'Cell Phone'); +INSERT INTO contact_class (id,class) values (4,'AIM'); +INSERT INTO contact_class (id,class) values (5,'Yahoo'); +INSERT INTO contact_class (id,class) values (6,'Gtalk'); +INSERT INTO contact_class (id,class) values (7,'MSN'); +INSERT INTO contact_class (id,class) values (8,'IRC'); +INSERT INTO contact_class (id,class) values (9,'Fax'); +INSERT INTO contact_class (id,class) values (10,'Generic Jabber'); +INSERT INTO contact_class (id,class) values (11,'Home Phone'); +INSERT INTO contact_class (id,class) values (12,'Email'); + +SELECT SETVAL('contact_class_id_seq',12); + +CREATE TABLE person_to_contact ( + person_id integer not null references person(id) ON DELETE CASCADE, + contact_class_id integer references contact_class(id) not null, + contact text check(contact ~ '[[:alnum:]_]') not null, + PRIMARY KEY (person_id,contact_class_id,contact)); + +COMMENT ON TABLE person_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single individual $$; + +CREATE TABLE company_to_contact ( + company_id integer not null references company(id) ON DELETE CASCADE, + contact_class_id integer references contact_class(id) not null, + contact text check(contact ~ '[[:alnum:]_]') not null, + description text, + PRIMARY KEY (company_id, contact_class_id, 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_bank_account ( + id serial not null, + entity_id int not null references entity(id) ON DELETE CASCADE, + bic varchar, + iban varchar, + UNIQUE (id), + PRIMARY KEY (entity_id, bic, iban) +); + +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, + description text, + 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), + bank_account int references entity_bank_account(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, + contact_class_id integer references contact_class(id) not null, + contact text check(contact ~ '[[:alnum:]_]') not null, + description text, + PRIMARY KEY (credit_id, contact_class_id, 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:]_]')); +INSERT INTO note_class(id,class) VALUES (1,'Entity'); +INSERT INTO note_class(id,class) VALUES (2,'Invoice'); +INSERT INTO note_class(id,class) VALUES (3,'Entity Credit Account'); +CREATE UNIQUE INDEX note_class_idx ON note_class(lower(class)); + +CREATE TABLE note (id serial primary key, note_class integer not null references note_class(id), + note text not null, vector tsvector not null, + created timestamp not null default now(), + created_by text DEFAULT SESSION_USER, + ref_key integer not null); + +CREATE TABLE entity_note(entity_id int references entity(id)) INHERITS (note); +ALTER TABLE entity_note ADD CHECK (note_class = 1); +ALTER TABLE entity_note ADD FOREIGN KEY (ref_key) REFERENCES entity(id) ON DELETE CASCADE; +CREATE INDEX entity_note_id_idx ON entity_note(id); +CREATE UNIQUE INDEX entity_note_class_idx ON note_class(lower(class)); +CREATE INDEX entity_note_vectors_idx ON entity_note USING gist(vector); +CREATE TABLE invoice_note() INHERITS (note); +CREATE INDEX invoice_note_id_idx ON invoice_note(id); +CREATE UNIQUE INDEX invoice_note_class_idx ON note_class(lower(class)); +CREATE INDEX invoice_note_vectors_idx ON invoice_note USING gist(vector); + +CREATE TABLE eca_note() + INHERITS (note); +ALTER TABLE eca_note ADD CHECK (note_class = 3); +ALTER TABLE eca_note ADD FOREIGN KEY (ref_key) + REFERENCES entity_credit_account(id) + ON DELETE CASCADE; + +-- END entity + +-- +CREATE TABLE makemodel ( + parts_id int PRIMARY KEY, + make text, + model text +); +-- +CREATE TABLE gl ( + id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id), + reference text, + description text, + transdate date DEFAULT current_date, + person_id integer references person(id), + notes text, + approved bool default true, + department_id int default 0 +); +-- +CREATE TABLE gifi ( + accno text PRIMARY KEY, + description text +); +-- +CREATE TABLE defaults ( setting_key text primary key, value text ); |