kivitendo/sql/updateLedger-200.sql @ ca6a1606
| 4dbb0995 | Moritz Bunkus | -- Updatescript von Version SQLedger 2.x auf 2.00
|
|
-- H.Lindemann Lx-System GbR
|
|||
-- info@lx-system.de
|
|||
-- Version: 2.3.9
|
|||
BEGIN;
|
|||
LOCK TABLE gl IN ACCESS EXCLUSIVE MODE;
|
|||
LOCK TABLE ar IN ACCESS EXCLUSIVE MODE;
|
|||
LOCK TABLE ap IN ACCESS EXCLUSIVE MODE;
|
|||
LOCK TABLE vendor IN ACCESS EXCLUSIVE MODE;
|
|||
LOCK TABLE customer IN ACCESS EXCLUSIVE MODE;
|
|||
LOCK TABLE employee IN ACCESS EXCLUSIVE MODE;
|
|||
LOCK TABLE shipto IN ACCESS EXCLUSIVE MODE;
|
|||
CREATE SEQUENCE glid start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;
|
|||
CREATE FUNCTION _glid()
|
|||
RETURNS text
|
|||
AS 'DECLARE lv record;
|
|||
BEGIN
|
|||
SELECT INTO lv last_value from id;
|
|||
execute ''SELECT pg_catalog.setval(''''glid'''', '' || lv.last_value || '' , true)'';
|
|||
return cast(lv.last_value as text);
|
|||
END;'
|
|||
LANGUAGE 'plpgsql';
|
|||
select _glid();
|
|||
drop function _glid();
|
|||
\echo gl
|
|||
ALTER TABLE gl ALTER COLUMN id SET DEFAULT nextval('glid'::text);
|
|||
ALTER TABLE gl ADD COLUMN gldate date;
|
|||
ALTER TABLE gl ALTER COLUMN gldate SET DEFAULT date('now'::text);
|
|||
ALTER TABLE gl ADD COLUMN taxinxluded boolean;
|
|||
\echo chart
|
|||
ALTER TABLE chart ADD COLUMN taxkey_id integer;
|
|||
ALTER TABLE chart ADD COLUMN pos_ustva integer;
|
|||
ALTER TABLE chart ADD COLUMN pos_bwa integer;
|
|||
ALTER TABLE chart ADD COLUMN pos_bilanz integer;
|
|||
ALTER TABLE chart ADD COLUMN pos_eur integer;
|
|||
\echo defaults
|
|||
--ALTER TABLE defaults drop COLUMN audittrail;
|
|||
\echo acc_trans
|
|||
ALTER TABLE acc_trans ADD COLUMN taxkey integer;
|
|||
ALTER TABLE acc_trans ADD COLUMN gldate date;
|
|||
ALTER TABLE acc_trans ALTER COLUMN gldate SET DEFAULT date('now'::text);
|
|||
\echo vendor
|
|||
CREATE TABLE newvendor (
|
|||
id integer DEFAULT nextval('id'::text),
|
|||
name character varying(75),
|
|||
street character varying(75),
|
|||
zipcode character varying(10),
|
|||
city character varying(75),
|
|||
country character varying(75),
|
|||
contact character varying(75),
|
|||
phone character varying(30),
|
|||
fax character varying(30),
|
|||
homepage text,
|
|||
email text,
|
|||
notes text,
|
|||
terms smallint DEFAULT 0,
|
|||
taxincluded boolean,
|
|||
vendornumber text,
|
|||
cc text,
|
|||
bcc text,
|
|||
gifi_accno text,
|
|||
business_id integer,
|
|||
taxnumber text,
|
|||
sic_code text,
|
|||
discount real,
|
|||
creditlimit double precision
|
|||
);
|
|||
INSERT INTO newvendor (
|
|||
id, name, street,zipcode,city,country,contact,phone,fax,homepage,email,notes,terms,taxincluded,
|
|||
vendornumber,cc,bcc,gifi_accno,business_id,taxnumber,sic_code,discount,creditlimit)
|
|||
SELECT
|
|||
id, name, address1,zipcode,city,country,contact,phone, fax,null,email,notes,terms,taxincluded,
|
|||
vendornumber,cc,bcc,gifi_accno,business_id,taxnumber,sic_code,discount,creditlimit
|
|||
FROM vendor;
|
|||
--address2, state, iban, bic, employee_id, language_code, pricegroup_id, curr
|
|||
DROP TABLE vendor;
|
|||
ALTER TABLE newvendor RENAME TO vendor;
|
|||
\echo customer
|
|||
CREATE TABLE newcustomer (
|
|||
id integer DEFAULT nextval('id'::text),
|
|||
name character varying(75),
|
|||
street character varying(75),
|
|||
zipcode character varying(10),
|
|||
city character varying(75),
|
|||
country character varying(75),
|
|||
contact character varying(75),
|
|||
phone character varying(30),
|
|||
fax character varying(30),
|
|||
homepage text,
|
|||
email text,
|
|||
notes text,
|
|||
discount real,
|
|||
taxincluded boolean,
|
|||
creditlimit double precision DEFAULT 0,
|
|||
terms smallint DEFAULT 0,
|
|||
customernumber text,
|
|||
cc text,
|
|||
bcc text,
|
|||
business_id integer,
|
|||
taxnumber text,
|
|||
sic_code text
|
|||
);
|
|||
INSERT INTO newcustomer (
|
|||
id,name,street,zipcode,city,country,contact,phone,fax,homepage,email,notes,discount,taxincluded,creditlimit,
|
|||
terms,customernumber,cc,bcc,business_id,taxnumber,sic_code)
|
|||
SELECT
|
|||
id,name,address1,zipcode,city,country,contact,phone,fax,null,email,notes,discount,taxincluded,creditlimit,
|
|||
terms,customernumber,cc,bcc,business_id,taxnumber,sic_code
|
|||
FROM customer;
|
|||
--address2, state, iban, bic, employee_id, language_code, pricegroup_id, curr
|
|||
DROP TABLE customer;
|
|||
ALTER TABLE newcustomer RENAME TO customer;
|
|||
\echo contacts
|
|||
CREATE TABLE contacts (
|
|||
cp_id integer DEFAULT nextval('id'::text),
|
|||
cp_cv_id integer,
|
|||
cp_greeting character varying(75),
|
|||
cp_title character varying(75),
|
|||
cp_givenname character varying(75),
|
|||
cp_name character varying(75),
|
|||
cp_email text,
|
|||
cp_phone1 character varying(75),
|
|||
cp_phone2 character varying(75)
|
|||
);
|
|||
\echo parts
|
|||
ALTER TABLE parts ADD COLUMN shop boolean;
|
|||
ALTER TABLE parts ALTER COLUMN shop SET DEFAULT false;
|
|||
\echo ar
|
|||
ALTER TABLE ar ALTER COLUMN id SET DEFAULT nextval('glid'::text);
|
|||
ALTER TABLE ar ADD COLUMN gldate date;
|
|||
ALTER TABLE ar ALTER COLUMN gldate SET DEFAULT date('now'::text);
|
|||
--ALTER TABLE ar DROP COLUMN language_code;
|
|||
--ALTER TABLE ar DROP COLUMN till;
|
|||
\echo ap
|
|||
ALTER TABLE ap ALTER COLUMN id SET DEFAULT nextval('glid'::text);
|
|||
ALTER TABLE ap ADD COLUMN gldate date;
|
|||
ALTER TABLE ap ALTER COLUMN gldate SET DEFAULT date('now'::text);
|
|||
--ALTER TABLE ap DROP COLUMN language_code;
|
|||
--ALTER TABLE ap DROP COLUMN till;
|
|||
\echo tax
|
|||
ALTER TABLE tax ADD COLUMN taxkey integer;
|
|||
ALTER TABLE tax ADD COLUMN taxdescription text;
|
|||
\echo oe
|
|||
ALTER TABLE oe DROP COLUMN language_code;
|
|||
\echo employee
|
|||
CREATE TABLE newemployee (
|
|||
id integer DEFAULT nextval('id'::text),
|
|||
login text,
|
|||
name character varying(35),
|
|||
addr1 character varying(35),
|
|||
addr2 character varying(35),
|
|||
addr3 character varying(35),
|
|||
addr4 character varying(35),
|
|||
workphone character varying(20),
|
|||
homephone character varying(20),
|
|||
startdate date DEFAULT date('now'::text),
|
|||
enddate date,
|
|||
notes text,
|
|||
role text,
|
|||
sales boolean DEFAULT true
|
|||
);
|
|||
INSERT INTO newemployee (
|
|||
id,login,name,addr1,addr2,addr3,addr4,workphone,homephone,startdate,enddate,notes,role,sales)
|
|||
SELECT
|
|||
id,login,name,address1,zipcode,city,address2, workphone,homephone,startdate,enddate,notes,role,sales
|
|||
FROM employee;
|
|||
--address2,state, country, email, sin, iban, bic, managerid
|
|||
DROP TABLE employee;
|
|||
ALTER TABLE newemployee RENAME TO employee;
|
|||
\echo shipto
|
|||
CREATE TABLE newshipto (
|
|||
trans_id integer,
|
|||
shiptoname character varying(75),
|
|||
shiptostreet character varying(75),
|
|||
shiptozipcode character varying(75),
|
|||
shiptocity character varying(75),
|
|||
shiptocountry character varying(75),
|
|||
shiptocontact character varying(75),
|
|||
shiptophone character varying(30),
|
|||
shiptofax character varying(30),
|
|||
shiptoemail text
|
|||
);
|
|||
INSERT INTO newshipto(
|
|||
trans_id,shiptoname,shiptostreet,shiptozipcode,shiptocity,shiptocountry,shiptocontact,shiptophone,shiptofax,shiptoemail)
|
|||
SELECT
|
|||
trans_id,shiptoname,shiptoaddress1,shiptozipcode,shiptocity,shiptocountry,shiptocontact,shiptophone,shiptofax,shiptoemail
|
|||
FROM shipto;
|
|||
-- shiptoaddress2,shiptostate,
|
|||
DROP TABLE shipto;
|
|||
ALTER TABLE newshipto RENAME TO shipto;
|
|||
\echo sic
|
|||
ALTER TABLE sic ADD COLUMN newcode text;
|
|||
UPDATE sic set newcode=code;
|
|||
ALTER TABLE sic drop COLUMN code;
|
|||
ALTER TABLE sic RENAME COLUMN newcode TO code;
|
|||
\echo yearend
|
|||
--DROP TABLE yearend;
|
|||
\echo partsvendor
|
|||
--DROP TABLE partsvendor;
|
|||
\echo pricegroup
|
|||
--DROP TABLE pricegroup;
|
|||
\echo partscustomer
|
|||
--DROP TABLE partscustomer;
|
|||
\echo language
|
|||
--DROP TABLE language;
|
|||
\echo autittrail
|
|||
--DROP TABLE audittrail;
|
|||
\echo translation;
|
|||
--DROP TABLE translation;
|
|||
\echo indexe
|
|||
CREATE INDEX contact_name_key ON contacts USING btree (cp_name);
|
|||
update defaults set version = '2.0.0';
|
|||
end;
|