import sql-ledger 2.4.4

[freeside.git] / sql-ledger / sql / Pg-upgrade-2.3.4-2.3.5.sql

diff --git a/sql-ledger/sql/Pg-upgrade-2.3.4-2.3.5.sql b/sql-ledger/sql/Pg-upgrade-2.3.4-2.3.5.sql
new file mode 100644 (file)
index 0000000..f4ab90b
--- /dev/null
+++ b/sql-ledger/sql/Pg-upgrade-2.3.4-2.3.5.sql
@@ -0,0 +1,142 @@
+--
+create table temp (
+  id int default nextval('id'),
+  name varchar(64),
+  address1 varchar(32),
+  address2 varchar(32),
+  city varchar(32),
+  state varchar(32),
+  zipcode varchar(10),
+  country varchar(32),
+  contact varchar(64),
+  phone varchar(20),
+  fax varchar(20),
+  email text,
+  notes text,
+  discount float4,
+  taxincluded bool default 'f',
+  creditlimit float default 0,
+  terms int2 default 0,
+  customernumber varchar(32),
+  cc text,
+  bcc text,
+  business_id int,
+  taxnumber varchar(32),
+  sic_code varchar(6),
+  iban varchar(34),
+  bic varchar(11),
+  employee_id int
+);
+--
+insert into temp (id,name,address1,city,country,state,contact,phone,fax,email,notes,discount,taxincluded,creditlimit,terms,customernumber,cc,bcc,business_id,taxnumber,sic_code,iban,bic,employee_id) select id,name,substr(addr1,1,32),substr(addr2,1,32),substr(addr3,1,32),substr(addr4,1,32),contact,phone,fax,email,notes,discount,taxincluded,creditlimit,terms,substr(customernumber,1,32),cc,bcc,business_id,substr(taxnumber,1,32),sic_code,iban,bic,employee_id 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),
+  address1 varchar(32),
+  address2 varchar(32),
+  city varchar(32),
+  state varchar(32),
+  zipcode varchar(10),
+  country varchar(32),
+  contact varchar(64),
+  phone varchar(20),
+  fax varchar(20),
+  email text,
+  notes text,
+  terms int2 default 0,
+  taxincluded bool default 'f',
+  vendornumber varchar(32),
+  cc text,
+  bcc text,
+  gifi_accno varchar(30),
+  business_id int,
+  taxnumber varchar(32),
+  sic_code varchar(6),
+  discount float4,
+  creditlimit float default 0,
+  iban varchar(34),
+  bic varchar(11),
+  employee_id int
+);
+--
+insert into temp (id,name,address1,city,country,state,contact,phone,fax,email,notes,terms,taxincluded,vendornumber,cc,bcc,gifi_accno,business_id,taxnumber,sic_code,discount,creditlimit,iban,bic,employee_id) select id,name,substr(addr1,1,32),substr(addr2,1,32),substr(addr3,1,32),substr(addr4,1,32),contact,phone,fax,email,notes,terms,taxincluded,substr(vendornumber,1,32),cc,bcc,gifi_accno,business_id,substr(taxnumber,1,32),sic_code,discount,creditlimit,iban,bic,employee_id 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 (
+  trans_id int,
+  shiptoname varchar(64),
+  shiptoaddress1 varchar(32),
+  shiptoaddress2 varchar(32),
+  shiptocity varchar(32),
+  shiptostate varchar(32),
+  shiptozipcode varchar(10),
+  shiptocountry varchar(32),
+  shiptocontact varchar(64),
+  shiptophone varchar(20),
+  shiptofax varchar(20),
+  shiptoemail text
+);
+--
+insert into temp (trans_id,shiptoname,shiptoaddress1,shiptocity,shiptocountry,shiptostate,shiptocontact,shiptophone,shiptofax,shiptoemail) select trans_id,shiptoname,substr(shiptoaddr1,1,32),substr(shiptoaddr2,1,32),substr(shiptoaddr3,1,32),substr(shiptoaddr4,1,32),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),
+  address1 varchar(32),
+  address2 varchar(32),
+  city varchar(32),
+  state varchar(32),
+  zipcode varchar(10),
+  country varchar(32),
+  workphone varchar(20),
+  homephone varchar(20),
+  startdate date default current_date,
+  enddate date,
+  notes text,
+  role varchar(20),
+  sales bool default 'f',
+  email text,
+  sin varchar(20),
+  iban varchar(34),
+  bic varchar(11),
+  managerid int
+);
+--
+insert into temp (id,login,name,address1,city,country,state,workphone,homephone,startdate,enddate,notes,role,sales,email,sin,iban,bic,managerid) select id,login,name,substr(addr1,1,32),substr(addr2,1,32),substr(addr3,1,32),substr(addr4,1,32),workphone,homephone,startdate,enddate,notes,role,sales,email,sin,iban,bic,managerid 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.5';
+