Moving Pg-upgrade* into sql/legacy directory

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@420 4979c152-3d1c-0410-bac9-87ea11338e46

1 files changed, 388 insertions, 0 deletions

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
new file mode 100755
index 00000000..866774ad
--- /dev/null
+++ b/sql/legacy/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';
+