File: Pg-upgrade-1.2.7-1.4.0.sql

package info (click to toggle)
sql-ledger 2.8.30-1
  • links: PTS, VCS
  • area: main
  • in suites: squeeze
  • size: 34,304 kB
  • ctags: 3,640
  • sloc: perl: 55,210; sql: 27,183; makefile: 129; sh: 38
file content (173 lines) | stat: -rw-r--r-- 3,953 bytes parent folder | download | duplicates (7)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
--
CREATE TABLE newap (
  id int DEFAULT nextval ( 'id' ),
  invnumber text,
  transdate date DEFAULT current_date,
  vendor int,
  taxincluded bool DEFAULT FALSE,
  amount float,
  netamount float,
  paid float,
  datepaid date,
  duedate date,
  invoice bool DEFAULT FALSE,
  ordnumber text
);
--
INSERT INTO newap (id, invnumber, transdate, vendor, amount, netamount, paid,
datepaid, duedate, invoice, ordnumber)
SELECT id, invnumber, transdate, vendor, amount, netamount, paid,
datepaid, duedate, invoice, ordnumber
FROM ap;
--
DROP TABLE ap;
ALTER TABLE newap RENAME TO ap;
--
CREATE TABLE newar (
  id int DEFAULT nextval ( 'id' ),
  invnumber text,
  transdate date DEFAULT current_date,
  customer int,
  taxincluded bool DEFAULT FALSE,
  amount float,
  netamount float,
  paid float,
  datepaid date,
  duedate date,
  invoice bool DEFAULT FALSE,
  shippingpoint text,
  terms int2,
  notes text
);
--
INSERT INTO newar (id, invnumber, transdate, customer, amount, netamount, paid,
datepaid, duedate, invoice, shippingpoint, terms, notes)
SELECT id, invnumber, transdate, customer, amount, netamount, paid,
datepaid, duedate, invoice, shippingpoint, terms, notes
FROM ar;
--
DROP TABLE ar;
ALTER TABLE newar RENAME TO ar;
--
CREATE TABLE newcustomer (
  id int DEFAULT nextval ( 'id' ),
  name varchar(35),
  addr1 varchar(35),
  addr2 varchar(35),
  addr3 varchar(35),
  contact varchar(35),
  phone varchar(20),
  fax varchar(20),
  email text,
  notes text,
  ytd float,
  discount float4,
  taxincluded bool,
  creditlimit float,
  terms int2,
  shiptoname varchar(35),
  shiptoaddr1 varchar(35),
  shiptoaddr2 varchar(35),
  shiptoaddr3 varchar(35),
  shiptocontact varchar(20),
  shiptophone varchar(20),
  shiptofax varchar(20),
  shiptoemail text
);
INSERT INTO newcustomer (
id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, ytd,
discount, creditlimit, terms, shiptoname, shiptoaddr1, shiptoaddr2,
shiptoaddr3, shiptocontact, shiptophone, shiptofax, shiptoemail )
SELECT id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, ytd,
discount, creditlimit, terms, shiptoname, shiptoaddr1, shiptoaddr2,
shiptoaddr3, shiptocontact, shiptophone, shiptofax, shiptoemail
FROM customer;
--
DROP TABLE customer;
ALTER TABLE newcustomer RENAME TO customer;
--
CREATE TABLE customertax (
  customer_id int,
  chart_id int
);
--
CREATE TABLE newdefaults (
  inventory_accno int,
  income_accno int,
  expense_accno int,
  invnumber text,
  ponumber text,
  yearend varchar(5),
  nativecurr varchar(3),
  weightunit varchar(5)
);
--
INSERT INTO newdefaults (
inventory_accno, income_accno, expense_accno, invnumber, ponumber)
SELECT inventory_accno, income_accno, expense_accno, invnumber, ponumber
FROM defaults;
--
DROP TABLE defaults;
ALTER TABLE newdefaults RENAME TO defaults;
UPDATE defaults SET yearend = '1/31', nativecurr = 'CAD', weightunit = 'kg';
--
CREATE TABLE partstax (
  parts_id int,
  chart_id int
);
--
CREATE TABLE tax (
  chart_id int,
  rate float,
  number text
);
--
CREATE TABLE newvendor (
  id int DEFAULT nextval ( 'id' ),
  name varchar(35),
  addr1 varchar(35),
  addr2 varchar(35),
  addr3 varchar(35),
  contact varchar(35),
  phone varchar(20),
  fax varchar(20),
  email text,
  notes text,
  ytd float,
  discount float4,
  taxincluded bool,
  creditlimit float,
  terms int2
);
--
INSERT INTO newvendor (
id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, ytd )
SELECT id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, ytd
FROM vendor;
--
DROP TABLE vendor;
ALTER TABLE newvendor RENAME TO vendor;
--
CREATE TABLE vendortax (
  vendor_id int,
  chart_id int
);
--
ALTER TABLE chart RENAME TO oldchart;
--
CREATE TABLE chart (
  id int DEFAULT nextval( 'id' ),
  accno int UNIQUE,
  description text,
  balance float,
  type char(1),
  gifi int,
  category char(1),
  link text
);
--
INSERT INTO chart SELECT * FROM oldchart;
--
DROP TABLE oldchart;
--