SF.net SVN: ledger-smb: [1229] trunk/sql/Pg-database.sql

[..hidden..]

Revision: 1229
          http://svn.sourceforge.net/ledger-smb/?rev=1229&view=rev
Author:   linuxpoet
Date:     2007-05-20 14:40:11 -0700 (Sun, 20 May 2007)

Log Message:
-----------
fixed broken relationships in new entity and some spelling errors, now need to bust through some rules

Modified Paths:
--------------
    trunk/sql/Pg-database.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2007-05-20 21:20:11 UTC (rev 1228)
+++ trunk/sql/Pg-database.sql	2007-05-20 21:40:11 UTC (rev 1229)
@@ -8,7 +8,7 @@
 -- 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 ('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 @@
 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 TABLE salutation (
+ id serial unique,
+ salutation text primary key);
 
-
-CREATE UNIQUE INDEX country_name_idx on country(lower(name));
-
 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 invoice (
-  id int serial PRIMARY KEY,
+  id serial PRIMARY KEY,
   trans_id int,
   parts_id int,
   description text,
@@ -347,6 +352,17 @@
   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 @@
   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 jcitems (
-  id int serial PRIMARY KEY,
+  id serial PRIMARY KEY,
   project_id int,
   parts_id int,
   description text,


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.