From ac5b087ea2d9ba7428d367aaeb288534158fee9a Mon Sep 17 00:00:00 2001 From: christopherm Date: Fri, 1 Sep 2006 01:16:38 +0000 Subject: Initial Import git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/ledger-smb@1 4979c152-3d1c-0410-bac9-87ea11338e46 --- sql/Pg-upgrade-2.3.0-2.3.1.sql | 388 +++++++++++++++++++++++++++++++++++++++++ 1 file changed, 388 insertions(+) create mode 100755 sql/Pg-upgrade-2.3.0-2.3.1.sql (limited to 'sql/Pg-upgrade-2.3.0-2.3.1.sql') diff --git a/sql/Pg-upgrade-2.3.0-2.3.1.sql b/sql/Pg-upgrade-2.3.0-2.3.1.sql new file mode 100755 index 00000000..866774ad --- /dev/null +++ b/sql/Pg-upgrade-2.3.0-2.3.1.sql @@ -0,0 +1,388 @@ +-- function check_department +create function check_department() returns opaque as ' + +declare + dpt_id int; + +begin + + if new.department_id = 0 then + delete from dpt_trans where trans_id = new.id; + return NULL; + end if; + + select into dpt_id trans_id from dpt_trans where trans_id = new.id; + + if dpt_id > 0 then + update dpt_trans set department_id = new.department_id where trans_id = dpt_id; + else + insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id); + end if; +return NULL; + +end; +' language 'plpgsql'; +-- end function + +-- department transaction table +create table dpt_trans (trans_id int, department_id int); + +-- function del_department +create function del_department() returns opaque as ' +begin + delete from dpt_trans where trans_id = old.id; + return NULL; +end; +' language 'plpgsql'; +-- end function + +-- triggers +-- +create trigger check_department after insert or update on ar for each row execute procedure check_department(); +-- end trigger +create trigger check_department after insert or update on ap for each row execute procedure check_department(); +-- end trigger +create trigger check_department after insert or update on gl for each row execute procedure check_department(); +-- end trigger +create trigger check_department after insert or update on oe for each row execute procedure check_department(); +-- end trigger +-- +-- +create trigger del_department after delete on ar for each row execute procedure del_department(); +-- end trigger +create trigger del_department after delete on ap for each row execute procedure del_department(); +-- end trigger +create trigger del_department after delete on gl for each row execute procedure del_department(); +-- end trigger +create trigger del_department after delete on oe for each row execute procedure del_department(); +-- end trigger +-- + +-- business table +create table business (id int default nextval('id'), description text, discount float4); +-- +-- SIC +create table sic (code text, sictype char(1), description text); +-- +alter table vendor add column gifi_accno text; +alter table vendor add column business_id int; +alter table vendor add column taxnumber text; +alter table vendor add column sic_code text; +-- +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 ' +begin + delete from shipto where trans_id = old.id; + delete from customertax where customer_id = old.id; + return NULL; +end; +' language 'plpgsql'; +-- end function +-- +create function del_vendor() returns opaque as ' +begin + delete from shipto where trans_id = old.id; + delete from vendortax where vendor_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 trigger del_vendor after delete on vendor for each row execute procedure del_vendor(); +-- end trigger +-- +alter table acc_trans add column memo text; +-- +alter table employee add column sales bool; +alter table employee alter column sales set default 't'; +-- +alter table vendor add discount float4; +alter table vendor add creditlimit float; +-- +-- function del_exchangerate +create function del_exchangerate() returns opaque as ' + +declare + t_transdate date; + t_curr char(3); + t_id int; + d_curr text; + +begin + + select into d_curr substr(curr,1,3) from defaults; + + if TG_RELNAME = ''ar'' then + select into t_curr, t_transdate curr, transdate from ar where id = old.id; + end if; + if TG_RELNAME = ''ap'' then + select into t_curr, t_transdate curr, transdate from ap where id = old.id; + end if; + if TG_RELNAME = ''oe'' then + select into t_curr, t_transdate curr, transdate from oe where id = old.id; + end if; + + if d_curr != t_curr then + + select into t_id a.id from acc_trans ac + join ar a on (a.id = ac.trans_id) + where a.curr = t_curr + and ac.transdate = t_transdate + + except select a.id from ar a where a.id = old.id + + union + + select a.id from acc_trans ac + join ap a on (a.id = ac.trans_id) + where a.curr = t_curr + and ac.transdate = t_transdate + + except select a.id from ap a where a.id = old.id + + union + + select o.id from oe o + where o.curr = t_curr + and o.transdate = t_transdate + + except select o.id from oe o where o.id = old.id; + + if not found then + delete from exchangerate where curr = t_curr and transdate = t_transdate; + end if; + end if; +return old; + +end; +' language 'plpgsql'; +-- end function +-- +-- triggers +-- +create trigger del_exchangerate before delete on ar for each row execute procedure del_exchangerate(); +-- end trigger +-- +create trigger del_exchangerate before delete on ap for each row execute procedure del_exchangerate(); +-- end trigger +-- +create trigger del_exchangerate before delete on oe for each row execute procedure del_exchangerate(); +-- end trigger +-- +-- +alter table orderitems add ship float4; +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'); +-- +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; +alter table ap add shipvia text; +alter table oe add shipvia text; +-- +-- +alter table inventory add employee_id int; +-- +-- +create function check_inventory() returns opaque as ' + +declare + itemid int; + row_data inventory%rowtype; + +begin + + if not old.quotation then + for row_data in select * from inventory where oe_id = old.id loop + select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id; + + if itemid is null then + delete from inventory where oe_id = old.id and orderitems_id = row_data.orderitems_id; + end if; + end loop; + end if; + return old; +end; +' language 'plpgsql'; +-- end function +-- +create trigger check_inventory after update on oe for each row execute procedure check_inventory(); +-- end trigger +-- +-- +create table yearend ( + trans_id int, + transdate date +); +-- +-- function del_yearend +create function del_yearend() returns opaque as ' +begin + delete from yearend where trans_id = old.id; + return NULL; +end; +' language 'plpgsql'; +-- end function + +-- triggers +-- +create trigger del_yearend after delete on gl for each row execute procedure del_yearend(); +-- end trigger +-- +-- +create table temp ( + id int default nextval('id'), + name varchar(64), + addr1 varchar(64), + addr2 varchar(64), + addr3 varchar(64), + addr4 varchar(64), + contact varchar(64), + phone varchar(20), + fax varchar(20), + email text, + notes text, + discount float4, + taxincluded bool, + creditlimit float default 0, + terms int2 default 0, + customernumber varchar(64), + cc text, + bcc text, + business_id int, + taxnumber varchar(64), + sic_code varchar(6), + iban varchar(34), + bic varchar(11) +); +insert into temp (id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, customernumber, cc, bcc, business_id, taxnumber, sic_code) select id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, customernumber, cc, bcc, business_id, taxnumber, sic_code from customer; +-- +drop table customer; +-- +alter table temp rename to customer; +-- +create index customer_id_key on customer (id); +create index customer_customernumber_key on customer (customernumber); +create index customer_name_key on customer (name); +create index customer_contact_key on customer (contact); +-- +create trigger del_customer after delete on customer for each row execute procedure del_customer(); +-- end trigger +-- +create table temp ( + id int default nextval('id'), + name varchar(64), + addr1 varchar(64), + addr2 varchar(64), + addr3 varchar(64), + addr4 varchar(64), + contact varchar(64), + phone varchar(20), + fax varchar(20), + email text, + notes text, + terms int2 default 0, + taxincluded bool, + vendornumber varchar(64), + cc text, + bcc text, + gifi_accno varchar(30), + business_id int, + taxnumber varchar(64), + sic_code varchar(6), + discount float4, + creditlimit float default 0, + iban varchar(34), + bic varchar(11) +); +insert into temp (id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, vendornumber, cc, bcc, business_id, taxnumber, sic_code) select id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, vendornumber, cc, bcc, business_id, taxnumber, sic_code from vendor; +-- +drop table vendor; +-- +alter table temp rename to vendor; +-- +create index vendor_id_key on vendor (id); +create index vendor_name_key on vendor (name); +create index vendor_vendornumber_key on vendor (vendornumber); +create index vendor_contact_key on vendor (contact); +-- +create trigger del_vendor after delete on vendor for each row execute procedure del_vendor(); +-- end trigger +-- +create table temp ( + code varchar(6), + sictype char(1), + description text +); +insert into temp (code, sictype, description) select code, sictype, description from sic; +drop table sic; +alter table temp rename to sic; +-- +create table temp ( + trans_id int, + shiptoname varchar(64), + shiptoaddr1 varchar(64), + shiptoaddr2 varchar(64), + shiptoaddr3 varchar(64), + shiptoaddr4 varchar(64), + shiptocontact varchar(64), + shiptophone varchar(20), + shiptofax varchar(20), + shiptoemail text +); +insert into temp (trans_id, shiptoname, shiptoaddr1, shiptoaddr2, shiptoaddr3, shiptoaddr4, shiptocontact, shiptophone, shiptofax, shiptoemail) select trans_id, shiptoname, shiptoaddr1, shiptoaddr2, shiptoaddr3, shiptoaddr4, shiptocontact, shiptophone, shiptofax, shiptoemail from shipto; +drop table shipto; +alter table temp rename to shipto; +create index shipto_trans_id_key on shipto (trans_id); +-- +-- +create table temp ( + id int default nextval('id'), + login text, + name varchar(64), + addr1 varchar(64), + addr2 varchar(64), + addr3 varchar(64), + addr4 varchar(64), + workphone varchar(20), + homephone varchar(20), + startdate date default current_date, + enddate date, + notes text, + role varchar(20), + sales bool, + email text, + sin varchar(20), + iban varchar(34), + bic varchar(11) +); +insert into temp (id,login,name,addr1,addr2,addr3,addr4,workphone,homephone,startdate,enddate,notes,role,sales) select id,login,name,addr1,addr2,addr3,addr4,workphone,homephone,startdate,enddate,notes,role,sales from employee; +-- +drop table employee; +alter table temp rename to employee; +-- +create index employee_id_key on employee (id); +create unique index employee_login_key on employee (login); +create index employee_name_key on employee (name); +-- +update defaults set version = '2.3.1'; + -- cgit v1.2.3