+++ /dev/null
--- 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;
projects / mfinanz.git / blobdiff