kivitendo/sql/Pg-upgrade-2.1.1-2.1.2.sql @ 9ad9bed8
| 4dbb0995 | Moritz Bunkus | |||
--Update der Numeric-Spalten von 5 auf 10 Vorkommastellen
|
||||
--
|
||||
--TABLE ap
|
||||
alter table ap rename column paid to paidold;
|
||||
alter table ap add column paid numeric(15,5);
|
||||
update ap set paid=paidold;
|
||||
alter table ap drop column paidold;
|
||||
--
|
||||
alter table ap rename column netamount to netamountold;
|
||||
alter table ap add column netamount numeric(15,5);
|
||||
update ap set netamount=netamountold;
|
||||
alter table ap drop column netamountold;
|
||||
--
|
||||
alter table ap rename column amount to amountold;
|
||||
alter table ap add column amount numeric(15,5);
|
||||
update ap set amount=amountold;
|
||||
alter table ap drop column amountold;
|
||||
--
|
||||
--TABLE acc_trans
|
||||
alter table acc_trans rename column amount to amountold;
|
||||
alter table acc_trans add column amount numeric(15,5);
|
||||
update acc_trans set amount=amountold;
|
||||
alter table acc_trans drop column amountold;
|
||||
--
|
||||
--TABLE ar
|
||||
alter table ar rename column amount to amountold;
|
||||
alter table ar add column amount numeric(15,5);
|
||||
update ar set amount=amountold;
|
||||
alter table ar drop column amountold;
|
||||
--
|
||||
alter table ar rename column netamount to netamountold;
|
||||
alter table ar add column netamount numeric(15,5);
|
||||
update ar set netamount=netamountold;
|
||||
alter table ar drop column netamountold;
|
||||
--
|
||||
alter table ar rename column paid to paidold;
|
||||
alter table ar add column paid numeric(15,5);
|
||||
update ar set paid=paidold;
|
||||
alter table ar drop column paidold;
|
||||
--
|
||||
--TABLE customer
|
||||
alter table customer rename column creditlimit to creditlimitold;
|
||||
alter table customer add column creditlimit numeric(15,5);
|
||||
update customer set creditlimit=creditlimitold;
|
||||
alter table customer drop column creditlimitold;
|
||||
--
|
||||
--TABLE exchangerate
|
||||
alter table exchangerate rename column buy to buyold;
|
||||
alter table exchangerate add column buy numeric(15,5);
|
||||
update exchangerate set buy=buyold;
|
||||
alter table exchangerate drop column buyold;
|
||||
--
|
||||
alter table exchangerate rename column sell to sellold;
|
||||
alter table exchangerate add column sell numeric(15,5);
|
||||
update exchangerate set sell=sellold;
|
||||
alter table exchangerate drop column sellold;
|
||||
--
|
||||
--TABLE invoice
|
||||
alter table invoice rename column sellprice to sellpriceold;
|
||||
alter table invoice add column sellprice numeric(15,5);
|
||||
update invoice set sellprice=sellpriceold;
|
||||
alter table invoice drop column sellpriceold;
|
||||
--
|
||||
alter table invoice rename column fxsellprice to fxsellpriceold;
|
||||
alter table invoice add column fxsellprice numeric(15,5);
|
||||
update invoice set fxsellprice=fxsellpriceold;
|
||||
alter table invoice drop column fxsellpriceold;
|
||||
--
|
||||
--TABLE oe
|
||||
alter table oe rename column amount to amountold;
|
||||
alter table oe add column amount numeric(15,5);
|
||||
update oe set amount=amountold;
|
||||
alter table oe drop column amountold;
|
||||
--
|
||||
alter table oe rename column netamount to netamountold;
|
||||
alter table oe add column netamount numeric(15,5);
|
||||
update oe set netamount=netamountold;
|
||||
alter table oe drop column netamountold;
|
||||
--
|
||||
--TABLE orderitems
|
||||
alter table orderitems rename column sellprice to sellpriceold;
|
||||
alter table orderitems add column sellprice numeric(15,5);
|
||||
update orderitems set sellprice=sellpriceold;
|
||||
alter table orderitems drop column sellpriceold;
|
||||
--
|
||||
--TABLE parts
|
||||
alter table parts rename column listprice to listpriceold;
|
||||
alter table parts add column listprice numeric(15,5);
|
||||
update parts set listprice=listpriceold;
|
||||
alter table parts drop column listpriceold;
|
||||
--
|
||||
alter table parts rename column sellprice to sellpriceold;
|
||||
alter table parts add column sellprice numeric(15,5);
|
||||
update parts set sellprice=sellpriceold;
|
||||
alter table parts drop column sellpriceold;
|
||||
--
|
||||
alter table parts rename column lastcost to lastcostold;
|
||||
alter table parts add column lastcost numeric(15,5);
|
||||
update parts set lastcost=lastcostold;
|
||||
alter table parts drop column lastcostold;
|
||||
--
|
||||
--TABLE tax
|
||||
alter table tax rename column rate to rateold;
|
||||
alter table tax add column rate numeric(15,5);
|
||||
update tax set rate=rateold;
|
||||
alter table tax drop column rateold;
|
||||
--
|
||||
--TABLE vendor
|
||||
alter table vendor rename column creditlimit to creditlimitold;
|
||||
alter table vendor add column creditlimit numeric(15,5);
|
||||
update vendor set creditlimit=creditlimitold;
|
||||
alter table vendor drop column creditlimitold;
|
||||
--
|
||||
--New Fields for customer and vendor
|
||||
alter table vendor add column obsolete boolean;
|
||||
alter table vendor alter column obsolete set default 'false';
|
||||
alter table customer add column obsolete boolean;
|
||||
alter table customer alter column obsolete set default 'false';
|
||||
alter table customer add column ustid varchar(12);
|
||||
alter table vendor add column ustid varchar(12);
|
||||
alter table customer add column username varchar(50);
|
||||
alter table vendor add column username varchar(50);
|
||||
alter table customer add column user_password varchar(12);
|
||||
alter table vendor add column user_password varchar(12);
|
||||
alter table customer add column salesman_id integer;
|
||||
alter table vendor add column salesman_id integer;
|
||||
| 435a5be5 | Philip Reetz | -- Shipto
|
||
alter table shipto add column shiptodepartment_1 varchar(75);
|
||||
alter table shipto add column shiptodepartment_2 varchar(75);
|
||||
| 59c1d7e3 | Philip Reetz | |||
| 4dbb0995 | Moritz Bunkus | -- Addon for business
|
||
alter table business add column salesman boolean;
|
||||
alter table business alter column salesman set default 'false';
|
||||
alter table business add column customernumberinit text;
|
||||
alter table parts add column ve integer;
|
||||
alter table parts add column gv numeric(15,5);
|
||||
--
|
||||
-- Add table contrains
|
||||
alter table customer alter name SET NOT NULL;
|
||||
alter table vendor alter name set NOT NULL;
|
||||
alter table chart alter accno set NOT NULL;
|
||||
alter table parts alter partnumber set NOT NULL;
|
||||
alter table ar alter invnumber set NOT NULL;
|
||||
alter table ap alter invnumber set NOT NULL;
|
||||
alter table oe alter ordnumber set NOT NULL;
|
||||
alter table gl add primary key (id);
|
||||
alter table chart add primary key (id);
|
||||
alter table parts add primary key (id);
|
||||
alter table invoice add primary key (id);
|
||||
alter table vendor add primary key (id);
|
||||
alter table customer add primary key (id);
|
||||
alter table contacts add primary key (cp_id);
|
||||
alter table ar add primary key (id);
|
||||
alter table ap add primary key (id);
|
||||
alter table oe add primary key (id);
|
||||
alter table employee add primary key (id);
|
||||
alter table warehouse add primary key (id);
|
||||
alter table business add primary key (id);
|
||||
alter table license add primary key (id);
|
||||
alter table orderitems add primary key (id);
|
||||
alter table acc_trans add foreign key (chart_id) references chart (id);
|
||||
alter table invoice add foreign key (parts_id) references parts (id);
|
||||
alter table ar add foreign key (customer_id) references customer (id);
|
||||
alter table ap add foreign key (vendor_id) references vendor (id);
|
||||
alter table orderitems add foreign key (parts_id) references parts (id);
|
||||
--Modify the possible length of bank account numbers
|
||||
alter table customer add column temp_account_number character varying(15);
|
||||
update customer set temp_account_number=account_number;
|
||||
alter table customer drop column account_number;
|
||||
alter table customer rename temp_account_number to account_number;
|
||||
alter table vendor add column temp_account_number character varying(15);
|
||||
update vendor set temp_account_number=account_number;
|
||||
alter table vendor drop column account_number;
|
||||
alter table vendor rename temp_account_number to account_number;
|
||||
| 59c1d7e3 | Philip Reetz | -- audit
|
||
alter table defaults add column audittrail bool;
|
||||
CREATE TABLE audittrail (
|
||||
trans_id int,
|
||||
tablename text,
|
||||
reference text,
|
||||
formname text,
|
||||
action text,
|
||||
transdate timestamp default current_timestamp,
|
||||
employee_id int
|
||||
);
|
||||
| 195883fd | Stephan Köhler | -- pricegroups
|
||
CREATE TABLE "pricegroup" (
|
||||
"id" integer DEFAULT nextval('id'::text),
|
||||
"pricegroup" text not null,
|
||||
PRIMARY KEY (id)
|
||||
);
|
||||
CREATE TABLE "prices" (
|
||||
"parts_id" integer REFERENCES parts(id),
|
||||
"pricegroup_id" integer REFERENCES pricegroup(id),
|
||||
"price" numeric(15,5)
|
||||
);
|
||||
ALTER TABLE customer ADD column klass integer;
|
||||
ALTER TABLE customer ALTER column klass set default 0;
|
||||
--
|
||||
ALTER TABLE invoice ADD column pricegroup_id integer;
|
||||
ALTER TABLE orderitems ADD column pricegroup_id integer;
|
||||
| 59c1d7e3 | Philip Reetz | update defaults set version = '2.1.2', audittrail = 't';
|
||
| 4dbb0995 | Moritz Bunkus | --
|