[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [1379] trunk/sql/Pg-database.sql
- Subject: SF.net SVN: ledger-smb: [1379] trunk/sql/Pg-database.sql
- From: ..hidden..
- Date: Tue, 10 Jul 2007 16:49:11 -0700
Revision: 1379
http://svn.sourceforge.net/ledger-smb/?rev=1379&view=rev
Author: einhverfr
Date: 2007-07-10 16:49:11 -0700 (Tue, 10 Jul 2007)
Log Message:
-----------
Got rid of del_customer and del_vendor triggers and moved to ON DELETE actions instead
Modified Paths:
--------------
trunk/sql/Pg-database.sql
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2007-07-10 22:36:10 UTC (rev 1378)
+++ trunk/sql/Pg-database.sql 2007-07-10 23:49:11 UTC (rev 1379)
@@ -116,8 +116,8 @@
SELECT setval('entity_class_id_seq',7);
CREATE TABLE entity_class_to_entity (
- entity_class_id integer not null references entity_class(id),
- entity_id integer not null references entity(id),
+ 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)
);
@@ -174,7 +174,7 @@
CREATE TABLE company_to_location (
location_id integer references location(id) not null,
- company_id integer references company(id) not null,
+ company_id integer not null references company(id) ON DELETE CASCADE,
PRIMARY KEY(location_id,company_id));
CREATE TABLE salutation (
@@ -204,31 +204,32 @@
CREATE TABLE person_to_location (
location_id integer not null references location(id),
- person_id integer not null references person(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 references person(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),
+ 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),
- entity_id integer not null references entity(id) check (entity_id != person_id),
+ 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),
- entity_id integer not null references entity(id) check (entity_id != company_id),
+ 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));
@@ -251,11 +252,12 @@
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 references person(id) not null,
+ 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));
@@ -263,7 +265,7 @@
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 references company(id) not null,
+ 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,
PRIMARY KEY (company_id,contact_class_id,contact));
@@ -283,7 +285,7 @@
CREATE TABLE entity_note() INHERITS (note);
ALTER TABLE entity_note ADD CHECK (id = 1);
-ALTER TABLE entity_note ADD FOREIGN KEY (ref_key) REFERENCES entity(id);
+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);
@@ -291,8 +293,11 @@
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);
+
+-- is this safe?
ALTER TABLE invoice_note ADD CHECK (id = 2);
+
-- END entity
--
@@ -402,7 +407,8 @@
--
CREATE TABLE entity_credit_account (
- entity_id int not null references entity(id),
+ 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,
taxincluded bool default 'f',
@@ -416,12 +422,14 @@
pricegroup_id int,
curr char(3),
startdate date DEFAULT CURRENT_DATE,
- enddate date
+ enddate date,
+ PRIMARY KEY(entity_id, meta_number)
);
+
CREATE TABLE entity_bank_account (
id serial not null,
- entity_id int references entity(id) not null,
+ entity_id int not null references entity(id) ON DELETE CASCADE,
bic varchar,
iban varchar,
UNIQUE (id),
@@ -631,24 +639,26 @@
);
--
CREATE TABLE customertax (
- customer_id int,
+ customer_id int references entity_credit_account(id) on delete cascade,
chart_id int,
PRIMARY KEY (customer_id, chart_id)
);
--
CREATE TABLE vendortax (
- vendor_id int,
+ vendor_id int references entity_credit_account(id) on delete cascade,
chart_id int,
PRIMARY KEY (vendor_id, chart_id)
);
--
CREATE TABLE oe_class (
- id smallint unique check(id IN (1,2)),
+ id smallint unique check(id IN (1,2,3,4)),
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');
+INSERT INTO oe_class(id,oe_class) values (3,'Quotation');
+INSERT INTO oe_class(id,oe_class) values (4,'RFQ');
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 $$;
@@ -706,7 +716,7 @@
);
--
create table employee (
- entity_id integer references entity(id) not null PRIMARY KEY,
+ entity_id integer not null references entity(id) on delete cascade primary key,
entity_class_id integer references entity_class(id) not null check (entity_class_id = 3),
login text,
startdate date default current_date,
@@ -851,7 +861,7 @@
);
--
CREATE TABLE partsvendor (
- entity_id int references entity(id) not null,
+ entity_id int not null references entity_credit_account(id) on delete cascade,
parts_id int,
partnumber text,
leadtime int2,
@@ -867,7 +877,7 @@
--
CREATE TABLE partscustomer (
parts_id int,
- customer_id int,
+ customer_id int not null references entity_credit_account(id) on delete cascade,
pricegroup_id int,
pricebreak numeric,
sellprice NUMERIC,
@@ -978,14 +988,7 @@
CREATE RULE chart_id_track_u AS ON update TO chart
DO UPDATE transactions SET id = new.id WHERE id = old.id;
-INSERT INTO transactions (id, table_name) SELECT id, 'customer' FROM customer;
-CREATE RULE customer_id_track_i AS ON insert TO customer
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'customer');
-
-CREATE RULE customer_id_track_u AS ON update TO customer
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
INSERT INTO transactions (id, table_name) SELECT id, 'department' FROM department;
CREATE RULE department_id_track_i AS ON insert TO department
@@ -994,8 +997,6 @@
CREATE RULE department_id_track_u AS ON update TO department
DO UPDATE transactions SET id = new.id WHERE id = old.id;
-INSERT INTO transactions (id, table_name) SELECT entity_id, 'employee' FROM employee;
-
INSERT INTO transactions (id, table_name) SELECT id, 'gl' FROM gl;
CREATE RULE gl_id_track_i AS ON insert TO gl
@@ -1044,11 +1045,7 @@
CREATE RULE project_id_track_u AS ON update TO project
DO UPDATE transactions SET id = new.id WHERE id = old.id;
-INSERT INTO transactions (id, table_name) SELECT entity_id, 'vendor' FROM vendor;
-CREATE RULE vendor_id_track_i AS ON insert TO vendor
-DO INSERT INTO transactions (id, table_name) VALUES (new.entity_id, 'vendor');
-
INSERT INTO transactions (id, table_name) SELECT id, 'warehouse' FROM warehouse;
CREATE RULE warehouse_id_track_i AS ON insert TO warehouse
@@ -1100,8 +1097,6 @@
create index chart_link_key on chart (link);
create index chart_gifi_accno_key on chart (gifi_accno);
--
-create index customer_id_key on customer (id);
-create index customer_customernumber_key on customer (customernumber);
create index customer_customer_id_key on customertax (customer_id);
--
create unique index employee_login_key on employee (login);
@@ -1133,8 +1128,6 @@
create index parts_description_key on parts (lower(description));
create index partstax_parts_id_key on partstax (parts_id);
--
-create index vendor_entity_id_key on vendor (entity_id);
-create index vendor_vendornumber_key on vendor (vendornumber);
--
create index shipto_trans_id_key on shipto (trans_id);
--
@@ -1445,32 +1438,6 @@
CREATE TRIGGER del_department AFTER DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_department();
-- end trigger
--
-CREATE FUNCTION del_customer() RETURNS TRIGGER AS '
-begin
- delete from shipto where trans_id = old.id;
- delete from customertax where customer_id = old.id;
- delete from partscustomer where customer_id = old.id;
- return NULL;
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER del_customer AFTER DELETE ON customer FOR EACH ROW EXECUTE PROCEDURE del_customer();
--- end trigger
---
-CREATE FUNCTION del_vendor() RETURNS TRIGGER AS '
-begin
- delete from shipto where trans_id = old.id;
- delete from vendortax where vendor_id = old.id;
- delete from partsvendor where vendor_id = old.id;
- return NULL;
-end;
-' language 'plpgsql';
--- end function
---
-CREATE TRIGGER del_vendor AFTER DELETE ON vendor FOR EACH ROW EXECUTE PROCEDURE del_vendor();
--- end trigger
---
CREATE FUNCTION del_exchangerate() RETURNS TRIGGER AS '
declare
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.