From 9160ca97dfa0d864c417d89940f6b409eff73f0e Mon Sep 17 00:00:00 2001 From: linuxpoet Date: Sun, 20 May 2007 21:40:11 +0000 Subject: fixed broken relationships in new entity and some spelling errors, now need to bust through some rules git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1229 4979c152-3d1c-0410-bac9-87ea11338e46 --- sql/Pg-database.sql | 80 +++++++++++++++++++++++++++++------------------------ 1 file changed, 44 insertions(+), 36 deletions(-) diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index ac0f52d2..312501e4 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -8,7 +8,7 @@ CREATE TABLE transactions ( -- BEGIN new entity management CREATE TABLE entity ( id serial PRIMARY KEY, - name text check (name ~ '[[:alnum:]_]'),, + name text check (name ~ '[[:alnum:]_]'), entity_class integer not null); COMMENT ON TABLE entity IS $$ The primary entity table to map to all contacts $$; @@ -34,11 +34,30 @@ INSERT INTO entity_class (class) VALUES ('Contact'); INSERT INTO entity_class (class) VALUES ('Lead'); INSERT INTO entity_class (class) VALUES ('Referral'); +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 ( + id serial PRIMARY KEY, + line_one text check (line_one ~ '[[:alnum:]_]') NOT NULL, + line_two text, + line_three text, + city_province text check (city_province ~ '[[:alnum:]_]') NOT NULL, + country_id integer not null REFERENCES country(id)); + CREATE TABLE company ( id serial UNIQUE, legal_name text check (legal_name ~ '[[:alnum:]_]'), entity_class_id integer not null references entity_class(id), - primary_location_id integer references address(id), + primary_location_id integer references location(id), tax_id text, PRIMARY KEY (legal_name,primary_location_id)); @@ -46,34 +65,20 @@ CREATE TABLE company ( COMMENT ON COLUMN company.primary_location_id IS $$ This is the location that should show up by default for any forms $$; COMMENT ON COLUMN company.tax_id IS $$ In the US this would be a EIN. $$; -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 salutation ( + id serial unique, + salutation text primary key); CREATE TABLE person ( id serial PRIMARY KEY, - salutation_id integer references salution(id), + salutation_id integer references salutation(id), entity_class_id integer references entity_class(id), - first_name check (first_name ~ '[[:alnum:]_]') NOT NULL, + first_name text check (first_name ~ '[[:alnum:]_]') NOT NULL, middle_name text, - last_name check (last_name ~ '[[:alnum:]_]') NOT NULL, - primary_location_id integer references location(id), + last_name text check (last_name ~ '[[:alnum:]_]') NOT NULL, + primary_location_id integer references location(id)); -CREATE TABLE location ( - id serial PRIMARY KEY, - line_one text check (line_one ~ '[[:alnum:]_]') NOT NULL, - line_two text, - line_three text, - city_province text check (city_province ~ '[[:alnum:]_]') NOT NULL, - country_id integer not null REFERENCES country(id)); -- END entity @@ -157,7 +162,7 @@ CREATE TABLE acc_trans ( ); -- CREATE TABLE invoice ( - id int serial PRIMARY KEY, + id serial PRIMARY KEY, trans_id int, parts_id int, description text, @@ -347,6 +352,17 @@ CREATE TABLE vendortax ( PRIMARY KEY (vendor_id, chart_id) ); -- + +CREATE TABLE oe_class ( + id smallint unique check(id IN (1,2)), + oe_class text primary key); + +INSERT INTO oe_class(id,oe_class) values (1,'Sales Order'); +INSERT INTO oe_class(id,oe_class) values (2,'Purchase Order'); + +COMMENT ON TABLE oe_class IS $$ This could probably be done better. But I need to remove the customer_id/vendor_id relationship and instead rely on a classification $$; + + CREATE TABLE oe ( id serial PRIMARY KEY, ordnumber text, @@ -368,23 +384,15 @@ CREATE TABLE oe ( shipvia text, language_code varchar(6), ponumber text, - terms int2 DEFAULT 0 - oe_class_id int references oe_type(id) NOT NULL + terms int2 DEFAULT 0, + oe_class_id int references oe_class(id) NOT NULL ); -CREATE TABLE oe_class ( - id smallint unique check(id IN (1,2), - oe_class text primary key; - -INSERT INTO oe_class(id,oe_class) values (1,'Sales Order'); -INSERT INTO oe_class(id,oe_class) values (2,'Purchase Order'); - -COMMENT ON TABLE oe_class IS $$ This could probably be done better. But I need to remove the customer_id/vendor_id relationship and instead rely on a classification $$; -- CREATE TABLE orderitems ( - id int serial PRIMARY KEY, + id serial PRIMARY KEY, trans_id int, parts_id int, description text, @@ -625,7 +633,7 @@ CREATE TABLE recurringprint ( ); -- CREATE TABLE jcitems ( - id int serial PRIMARY KEY, + id serial PRIMARY KEY, project_id int, parts_id int, description text, -- cgit v1.2.3