From 2290cdcd56f56b76cc980761c61e94bbac49a085 Mon Sep 17 00:00:00 2001 From: einhverfr Date: Wed, 6 Dec 2006 05:22:19 +0000 Subject: Applying Mads' patch to upgrade scripts git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/branches/1.2@734 4979c152-3d1c-0410-bac9-87ea11338e46 --- sql/legacy/Pg-upgrade-2.3.0-2.3.1.sql | 24 +++++++++--------------- sql/legacy/Pg-upgrade-2.3.9-2.4.2.sql | 4 ++-- sql/legacy/Pg-upgrade-2.4.4-2.5.0.sql | 2 +- sql/legacy/Pg-upgrade-2.5.0-2.5.2.sql | 2 +- sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql | 1 - 5 files changed, 13 insertions(+), 20 deletions(-) (limited to 'sql') diff --git a/sql/legacy/Pg-upgrade-2.3.0-2.3.1.sql b/sql/legacy/Pg-upgrade-2.3.0-2.3.1.sql index 866774ad..04cfe1b7 100755 --- a/sql/legacy/Pg-upgrade-2.3.0-2.3.1.sql +++ b/sql/legacy/Pg-upgrade-2.3.0-2.3.1.sql @@ -1,5 +1,5 @@ -- function check_department -create function check_department() returns opaque as ' +create function check_department() returns trigger as ' declare dpt_id int; @@ -28,7 +28,7 @@ end; create table dpt_trans (trans_id int, department_id int); -- function del_department -create function del_department() returns opaque as ' +create function del_department() returns trigger as ' begin delete from dpt_trans where trans_id = old.id; return NULL; @@ -73,7 +73,7 @@ alter table customer add column business_id int; alter table customer add column taxnumber text; alter table customer add column sic_code text; -- -create function del_customer() returns opaque as ' +create function del_customer() returns trigger as ' begin delete from shipto where trans_id = old.id; delete from customertax where customer_id = old.id; @@ -82,7 +82,7 @@ end; ' language 'plpgsql'; -- end function -- -create function del_vendor() returns opaque as ' +create function del_vendor() returns trigger as ' begin delete from shipto where trans_id = old.id; delete from vendortax where vendor_id = old.id; @@ -105,7 +105,7 @@ alter table vendor add discount float4; alter table vendor add creditlimit float; -- -- function del_exchangerate -create function del_exchangerate() returns opaque as ' +create function del_exchangerate() returns trigger as ' declare t_transdate date; @@ -180,19 +180,13 @@ alter table orderitems add serialnumber text; -- -- create sequence orderitemsid maxvalue 100000 cycle; -alter table orderitems add id int; -alter table orderitems alter id set default nextval('orderitemsid'); +-- add id to orderitems, fill in id +alter table orderitems add id int default nextval('orderitemsid'); -- create table warehouse (id int default nextval('id'), description text); -- create table inventory (warehouse_id int, parts_id int, oe_id int, orderitems_id int, qty float4, shippingdate date); -- --- update orderitems, fill in id -create table temp (id int default nextval('orderitemsid'), tempid oid); -insert into temp (tempid) select oid from orderitems; -update orderitems set id = temp.id from temp where orderitems.oid = temp.tempid; -drop table temp; --- create index orderitems_id_key on orderitems (id); -- alter table ar add shipvia text; @@ -203,7 +197,7 @@ alter table oe add shipvia text; alter table inventory add employee_id int; -- -- -create function check_inventory() returns opaque as ' +create function check_inventory() returns trigger as ' declare itemid int; @@ -235,7 +229,7 @@ create table yearend ( ); -- -- function del_yearend -create function del_yearend() returns opaque as ' +create function del_yearend() returns trigger as ' begin delete from yearend where trans_id = old.id; return NULL; diff --git a/sql/legacy/Pg-upgrade-2.3.9-2.4.2.sql b/sql/legacy/Pg-upgrade-2.3.9-2.4.2.sql index 4e91e473..92096fbe 100755 --- a/sql/legacy/Pg-upgrade-2.3.9-2.4.2.sql +++ b/sql/legacy/Pg-upgrade-2.3.9-2.4.2.sql @@ -4,7 +4,7 @@ drop trigger del_vendor on vendor; drop function del_customer(); drop function del_vendor(); -- -create function del_customer() returns opaque as ' +create function del_customer() returns trigger as ' begin delete from shipto where trans_id = old.id; delete from customertax where customer_id = old.id; @@ -17,7 +17,7 @@ end; create trigger del_customer after delete on customer for each row execute procedure del_customer(); -- end trigger -- -create function del_vendor() returns opaque as ' +create function del_vendor() returns trigger as ' begin delete from shipto where trans_id = old.id; delete from vendortax where vendor_id = old.id; diff --git a/sql/legacy/Pg-upgrade-2.4.4-2.5.0.sql b/sql/legacy/Pg-upgrade-2.4.4-2.5.0.sql index 5d3b9b9d..82da5cd6 100755 --- a/sql/legacy/Pg-upgrade-2.4.4-2.5.0.sql +++ b/sql/legacy/Pg-upgrade-2.4.4-2.5.0.sql @@ -10,7 +10,7 @@ create table recurring (id int, reference text, startdate date, nextdate date, e create table recurringemail (id int, formname text, format text, message text); create table recurringprint (id int, formname text, format text, printer text); -- -create function del_recurring() returns opaque as ' +create function del_recurring() returns trigger as ' begin delete from recurring where id = old.id; delete from recurringemail where id = old.id; diff --git a/sql/legacy/Pg-upgrade-2.5.0-2.5.2.sql b/sql/legacy/Pg-upgrade-2.5.0-2.5.2.sql index c6c9d641..b575bdd8 100755 --- a/sql/legacy/Pg-upgrade-2.5.0-2.5.2.sql +++ b/sql/legacy/Pg-upgrade-2.5.0-2.5.2.sql @@ -76,7 +76,7 @@ alter table ap add terms int2; -- drop trigger check_inventory on oe; drop function check_inventory(); -create function check_inventory() returns opaque as ' +create function check_inventory() returns trigger as ' declare itemid int; diff --git a/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql b/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql index a7f4a3f1..5bed6ec9 100644 --- a/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql +++ b/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql @@ -198,7 +198,6 @@ ALTER TABLE session ADD column user_id integer not null references users(id); ALTER TABLE session ALTER COLUMN user_id DROP NOT NULL; -- Admin user -- -BEGIN; INSERT INTO users(username) VALUES ('admin'); INSERT INTO users_conf(id,password) VALUES (currval('users_id_seq'),NULL); -- cgit v1.2.3