SF.net SVN: ledger-smb: [524] trunk/sql

[..hidden..]

Revision: 524
          http://svn.sourceforge.net/ledger-smb/?rev=524&view=rev
Author:   tetragon
Date:     2006-11-11 22:55:19 -0800 (Sat, 11 Nov 2006)

Log Message:
-----------
Adding tax category support to the db

Modified Paths:
--------------
    trunk/sql/Pg-database.sql
    trunk/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2006-11-12 06:49:19 UTC (rev 523)
+++ trunk/sql/Pg-database.sql	2006-11-12 06:55:19 UTC (rev 524)
@@ -241,17 +241,28 @@
   terms int2 DEFAULT 0
 );
 --
+CREATE TABLE taxmodule (
+  taxmodule_id serial PRIMARY KEY,
+  taxmodulename text NOT NULL
+);
+--
+CREATE TABLE taxcategory (
+  taxcategory_id serial PRIMARY KEY,
+  taxcategoryname text NOT NULL,
+  taxmodule_id int NOT NULL,
+  FOREIGN KEY taxmodule_id REFERENCES taxmodule (taxmodule_id)
+);
+--
 CREATE TABLE partstax (
   parts_id int,
   chart_id int,
-  PRIMARY KEY (parts_id, chart_id)
+  taxcategory_id int,
+  PRIMARY KEY (parts_id, chart_id),
+  FOREIGN KEY parts_id REFERENCES parts (id),
+  FOREIGN KEY chart_id REFERENCES chart (id),
+  FOREIGN KEY taxcategory_id REFERENCES taxcategory (taxcategory_id)
 );
 --
-CREATE TABLE taxmodule (
-  taxmodule_id serial PRIMARY KEY,
-  taxmodulename text NOT NULL
-);
---
 CREATE TABLE tax (
   chart_id int PRIMARY KEY,
   rate numeric,

Modified: trunk/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql
===================================================================
--- trunk/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql	2006-11-12 06:49:19 UTC (rev 523)
+++ trunk/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql	2006-11-12 06:55:19 UTC (rev 524)
@@ -43,6 +43,8 @@
 ALTER TABLE partsgroup ADD PRIMARY KEY (id);
 
 ALTER TABLE partstax ADD PRIMARY KEY (parts_id, chart_id);
+ALTER TABLE partstax ADD FOREIGN KEY (chart_id) REFERENCES chart (id);
+ALTER TABLE partstax ADD FOREIGN KEY (parts_id) REFERENCES parts (id);
 
 ALTER TABLE pricegroup ADD PRIMARY KEY (id);
 
@@ -132,6 +134,14 @@
   1, 'Simple'
 );
 
+CREATE TABLE taxcategory (
+  taxcategory_id serial PRIMARY KEY,
+  taxcategoryname text NOT NULL,
+  taxmodule_id int NOT NULL REFERENCES taxmodule (taxmodule_id)
+);
+
+ALTER TABLE partstax ADD COLUMN taxcategory_id int REFERENCES taxcategory (taxcategory_id);
+
 LOCK tax IN EXCLUSIVE MODE;
 ALTER TABLE tax ADD COLUMN pass int DEFAULT 0;
 UPDATE tax SET pass = 0;


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