1 alter table acc_trans rename column accno to chart_id;
2 update acc_trans set chart_id =
3 (select id from chart where accno = acc_trans.chart_id);
5 alter table parts rename column inventory_accno to inventory_accno_id;
6 alter table parts rename column income_accno to income_accno_id;
7 alter table parts rename column expense_accno to expense_accno_id;
8 alter table parts rename column number to partnumber;
9 update parts set inventory_accno_id =
10 (select id from chart where chart.accno = parts.inventory_accno_id);
11 update parts set income_accno_id =
12 (select id from chart where chart.accno = parts.income_accno_id);
13 update parts set expense_accno_id =
14 (select id from chart where chart.accno = parts.expense_accno_id);
16 create table assembly (id int, parts_id int, qty float);
18 alter table defaults rename column inventory_accno to inventory_accno_id;
19 alter table defaults rename column income_accno to income_accno_id;
20 alter table defaults rename column expense_accno to expense_accno_id;
21 alter table defaults add column businessnumber text;
22 alter table defaults add column version varchar(8);
23 update defaults set inventory_accno_id =
24 (select id from chart where chart.accno = defaults.inventory_accno_id);
25 update defaults set income_accno_id =
26 (select id from chart where chart.accno = defaults.income_accno_id);
27 update defaults set expense_accno_id =
28 (select id from chart where chart.accno = defaults.expense_accno_id);
29 update defaults set version = '1.6.0';
31 alter table invoice rename column inventory_accno to inventory_accno_id;
32 alter table invoice rename column income_accno to income_accno_id;
33 alter table invoice rename column expense_accno to expense_accno_id;
34 alter table invoice rename column number to partnumber;
35 alter table invoice add column assemblyitem bool;
36 update invoice set assemblyitem = 'f';
37 update invoice set inventory_accno_id =
38 (select id from chart where invoice.inventory_accno_id = chart.accno);
39 update invoice set income_accno_id =
40 (select id from chart where invoice.income_accno_id = chart.accno);
41 update invoice set expense_accno_id =
42 (select id from chart where invoice.expense_accno_id = chart.accno);
44 alter table gl rename column comment to description;
46 create table newvendor (
47 id int default nextval ( 'id' ),
61 insert into newvendor (
62 id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, terms,
65 id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, terms,
66 taxincluded from vendor;
68 alter table newvendor rename to vendor;
70 create table newcustomer (
71 id int default nextval ( 'id' ),
86 shiptoname varchar(35),
87 shiptoaddr1 varchar(35),
88 shiptoaddr2 varchar(35),
89 shiptoaddr3 varchar(35),
90 shiptoaddr4 varchar(35),
91 shiptocontact varchar(20),
92 shiptophone varchar(20),
93 shiptofax varchar(20),
96 insert into newcustomer (
97 id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, discount,
98 taxincluded, creditlimit, terms, shiptoname, shiptoaddr1, shiptoaddr2,
99 shiptoaddr3, shiptocontact, shiptophone, shiptofax, shiptoemail
102 id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, discount,
103 taxincluded, creditlimit, terms, shiptoname, shiptoaddr1, shiptoaddr2,
104 shiptoaddr3, shiptocontact, shiptophone, shiptofax, shiptoemail
107 alter table newcustomer rename to customer;
109 drop index chart_accno_key;
110 alter table chart rename to oldchart;
112 id int default nextval('id'),
120 insert into chart (id, accno, description, charttype, gifi, category, link)
121 select id, accno, description, type, gifi, category, link from oldchart;
124 alter table tax rename column number to taxnumber;