1 -- @tag: acc_trans_without_oid
2 -- @description: Einführen einer ID-Spalte in acc_trans
3 -- @depends: release_2_4_3 cb_ob_transaction
5 CREATE SEQUENCE acc_trans_id_seq;
7 CREATE TABLE new_acc_trans (
8 acc_trans_id bigint DEFAULT nextval('acc_trans_id_seq'),
12 transdate date DEFAULT date('now'::text),
13 gldate date DEFAULT date('now'::text),
15 cleared boolean DEFAULT false,
16 fx_transaction boolean DEFAULT false,
17 ob_transaction boolean DEFAULT false,
18 cb_transaction boolean DEFAULT false,
22 itime timestamp without time zone DEFAULT now(),
23 mtime timestamp without time zone
26 INSERT INTO new_acc_trans (acc_trans_id, trans_id, chart_id, amount, transdate, gldate, source, cleared,
27 fx_transaction, ob_transaction, cb_transaction, project_id, memo, taxkey, itime, mtime)
28 SELECT oid, trans_id, chart_id, amount, transdate, gldate, source, cleared,
29 fx_transaction, ob_transaction, cb_transaction, project_id, memo, taxkey, itime, mtime
32 SELECT setval('acc_trans_id_seq', (SELECT COALESCE((SELECT MAX(oid::integer) FROM acc_trans), 0) + 1));
35 ALTER TABLE new_acc_trans RENAME TO acc_trans;
37 CREATE INDEX acc_trans_trans_id_key ON acc_trans USING btree (trans_id);
38 CREATE INDEX acc_trans_chart_id_key ON acc_trans USING btree (chart_id);
39 CREATE INDEX acc_trans_transdate_key ON acc_trans USING btree (transdate);
40 CREATE INDEX acc_trans_source_key ON acc_trans USING btree (lower(source));
42 ALTER TABLE ONLY acc_trans
43 ADD CONSTRAINT "$1" FOREIGN KEY (chart_id) REFERENCES chart(id);
45 CREATE TRIGGER mtime_acc_trans
46 BEFORE UPDATE ON acc_trans
48 EXECUTE PROCEDURE set_mtime();