2 --Update der Numeric-Spalten von 5 auf 10 Vorkommastellen
5 alter table ap rename column paid to paidold;
6 alter table ap add column paid numeric(15,5);
7 update ap set paid=paidold;
8 alter table ap drop column paidold;
10 alter table ap rename column netamount to netamountold;
11 alter table ap add column netamount numeric(15,5);
12 update ap set netamount=netamountold;
13 alter table ap drop column netamountold;
15 alter table ap rename column amount to amountold;
16 alter table ap add column amount numeric(15,5);
17 update ap set amount=amountold;
18 alter table ap drop column amountold;
21 alter table acc_trans rename column amount to amountold;
22 alter table acc_trans add column amount numeric(15,5);
23 update acc_trans set amount=amountold;
24 alter table acc_trans drop column amountold;
27 alter table ar rename column amount to amountold;
28 alter table ar add column amount numeric(15,5);
29 update ar set amount=amountold;
30 alter table ar drop column amountold;
32 alter table ar rename column netamount to netamountold;
33 alter table ar add column netamount numeric(15,5);
34 update ar set netamount=netamountold;
35 alter table ar drop column netamountold;
37 alter table ar rename column paid to paidold;
38 alter table ar add column paid numeric(15,5);
39 update ar set paid=paidold;
40 alter table ar drop column paidold;
43 alter table customer rename column creditlimit to creditlimitold;
44 alter table customer add column creditlimit numeric(15,5);
45 update customer set creditlimit=creditlimitold;
46 alter table customer drop column creditlimitold;
49 alter table exchangerate rename column buy to buyold;
50 alter table exchangerate add column buy numeric(15,5);
51 update exchangerate set buy=buyold;
52 alter table exchangerate drop column buyold;
54 alter table exchangerate rename column sell to sellold;
55 alter table exchangerate add column sell numeric(15,5);
56 update exchangerate set sell=sellold;
57 alter table exchangerate drop column sellold;
60 alter table invoice rename column sellprice to sellpriceold;
61 alter table invoice add column sellprice numeric(15,5);
62 update invoice set sellprice=sellpriceold;
63 alter table invoice drop column sellpriceold;
65 alter table invoice rename column fxsellprice to fxsellpriceold;
66 alter table invoice add column fxsellprice numeric(15,5);
67 update invoice set fxsellprice=fxsellpriceold;
68 alter table invoice drop column fxsellpriceold;
71 alter table oe rename column amount to amountold;
72 alter table oe add column amount numeric(15,5);
73 update oe set amount=amountold;
74 alter table oe drop column amountold;
76 alter table oe rename column netamount to netamountold;
77 alter table oe add column netamount numeric(15,5);
78 update oe set netamount=netamountold;
79 alter table oe drop column netamountold;
82 alter table orderitems rename column sellprice to sellpriceold;
83 alter table orderitems add column sellprice numeric(15,5);
84 update orderitems set sellprice=sellpriceold;
85 alter table orderitems drop column sellpriceold;
88 alter table parts rename column listprice to listpriceold;
89 alter table parts add column listprice numeric(15,5);
90 update parts set listprice=listpriceold;
91 alter table parts drop column listpriceold;
93 alter table parts rename column sellprice to sellpriceold;
94 alter table parts add column sellprice numeric(15,5);
95 update parts set sellprice=sellpriceold;
96 alter table parts drop column sellpriceold;
98 alter table parts rename column lastcost to lastcostold;
99 alter table parts add column lastcost numeric(15,5);
100 update parts set lastcost=lastcostold;
101 alter table parts drop column lastcostold;
104 alter table tax rename column rate to rateold;
105 alter table tax add column rate numeric(15,5);
106 update tax set rate=rateold;
107 alter table tax drop column rateold;
110 alter table vendor rename column creditlimit to creditlimitold;
111 alter table vendor add column creditlimit numeric(15,5);
112 update vendor set creditlimit=creditlimitold;
113 alter table vendor drop column creditlimitold;
116 --New Fields for customer and vendor
117 alter table vendor add column obsolete boolean;
118 alter table vendor alter column obsolete set default 'false';
119 alter table customer add column obsolete boolean;
120 alter table customer alter column obsolete set default 'false';
121 alter table customer add column ustid varchar(12);
122 alter table vendor add column ustid varchar(12);
124 alter table customer add column username varchar(50);
125 alter table vendor add column username varchar(50);
126 alter table customer add column user_password varchar(12);
127 alter table vendor add column user_password varchar(12);
128 alter table customer add column salesman_id integer;
129 alter table vendor add column salesman_id integer;
132 alter table shipto add column shiptodepartment_1 varchar(75);
133 alter table shipto add column shiptodepartment_2 varchar(75);
137 -- Addon for business
138 alter table business add column salesman boolean;
139 alter table business alter column salesman set default 'false';
140 alter table business add column customernumberinit text;
142 alter table parts add column ve integer;
143 alter table parts add column gv numeric(15,5);
146 -- Add table contrains
147 alter table customer alter name SET NOT NULL;
148 alter table vendor alter name set NOT NULL;
149 alter table chart alter accno set NOT NULL;
150 alter table parts alter partnumber set NOT NULL;
151 alter table ar alter invnumber set NOT NULL;
152 alter table ap alter invnumber set NOT NULL;
153 alter table oe alter ordnumber set NOT NULL;
155 alter table gl add primary key (id);
156 alter table chart add primary key (id);
157 alter table parts add primary key (id);
158 alter table invoice add primary key (id);
159 alter table vendor add primary key (id);
160 alter table customer add primary key (id);
161 alter table contacts add primary key (cp_id);
162 alter table ar add primary key (id);
163 alter table ap add primary key (id);
164 alter table oe add primary key (id);
165 alter table employee add primary key (id);
166 alter table warehouse add primary key (id);
167 alter table business add primary key (id);
168 alter table license add primary key (id);
169 alter table orderitems add primary key (id);
171 alter table acc_trans add foreign key (chart_id) references chart (id);
172 alter table invoice add foreign key (parts_id) references parts (id);
173 alter table ar add foreign key (customer_id) references customer (id);
174 alter table ap add foreign key (vendor_id) references vendor (id);
175 alter table orderitems add foreign key (parts_id) references parts (id);
177 --Modify the possible length of bank account numbers
178 alter table customer add column temp_account_number character varying(15);
179 update customer set temp_account_number=account_number;
180 alter table customer drop column account_number;
181 alter table customer rename temp_account_number to account_number;
183 alter table vendor add column temp_account_number character varying(15);
184 update vendor set temp_account_number=account_number;
185 alter table vendor drop column account_number;
186 alter table vendor rename temp_account_number to account_number;
189 alter table defaults add column audittrail bool;
190 CREATE TABLE audittrail (
196 transdate timestamp default current_timestamp,
202 CREATE TABLE "pricegroup" (
203 "id" integer DEFAULT nextval('id'::text),
204 "pricegroup" text not null,
208 CREATE TABLE "prices" (
209 "parts_id" integer REFERENCES parts(id),
210 "pricegroup_id" integer REFERENCES pricegroup(id),
211 "price" numeric(15,5)
214 ALTER TABLE customer ADD column klass integer;
215 ALTER TABLE customer ALTER column klass set default 0;
218 ALTER TABLE invoice ADD column pricegroup_id integer;
219 ALTER TABLE orderitems ADD column pricegroup_id integer;
221 update defaults set version = '2.1.2', audittrail = 't';