1BEGIN; 2ALTER TABLE chart ADD PRIMARY KEY (id); 3-- linuxpoet: 4-- adding primary key to acc_trans 5-- We are using standard postgresql names for the sequence for consistency as we move forward 6-- Do everything in a transaction in case it blows up 7 8LOCK acc_trans in EXCLUSIVE mode; 9ALTER TABLE acc_trans ADD COLUMN entry_id bigint; 10CREATE SEQUENCE acc_trans_entry_id_seq; 11ALTER TABLE acc_trans ALTER COLUMN entry_id SET DEFAULT nextval('acc_trans_entry_id_seq'); 12UPDATE acc_trans SET entry_id = nextval('acc_trans_entry_id_seq'); 13ALTER TABLE acc_trans ADD PRIMARY key (entry_id); 14 15-- We should probably add a foreign key to chart.id 16ALTER TABLE acc_trans ADD FOREIGN KEY (chart_id) REFERENCES chart (id); 17 18-- Start changing floats 19ALTER TABLE acc_trans ALTER COLUMN amount TYPE NUMERIC; 20 21-- This may break someone if they for some reason have an actual float type in the qty column 22ALTER TABLE invoice ALTER COLUMN qty TYPE numeric; 23 24ALTER TABLE invoice ALTER COLUMN allocated TYPE numeric; 25ALTER TABLE invoice ALTER COLUMN sellprice TYPE NUMERIC; 26ALTER TABLE invoice ALTER COLUMN fxsellprice TYPE NUMERIC; 27 28ALTER TABLE customer ALTER COLUMN discount TYPE numeric; 29ALTER TABLE customer ALTER COLUMN creditlimit TYPE NUMERIC; 30 31ALTER TABLE parts ALTER COLUMN listprice TYPE NUMERIC; 32ALTER TABLE parts ALTER COLUMN sellprice TYPE NUMERIC; 33ALTER TABLE parts ALTER COLUMN lastcost TYPE NUMERIC; 34ALTER TABLE parts ALTER COLUMN weight TYPE numeric; 35ALTER TABLE parts ALTER COLUMN onhand TYPE numeric; 36ALTER TABLE parts ALTER COLUMN avgcost TYPE NUMERIC; 37 38ALTER TABLE assembly ALTER COLUMN qty TYPE numeric; 39 40ALTER TABLE ar ALTER COLUMN amount TYPE NUMERIC; 41ALTER TABLE ar ALTER COLUMN netamount TYPE NUMERIC; 42ALTER TABLE ar ALTER COLUMN paid TYPE NUMERIC; 43 44ALTER TABLE ap ALTER COLUMN amount TYPE NUMERIC; 45ALTER TABLE ap ALTER COLUMN netamount TYPE NUMERIC; 46ALTER TABLE ap ALTER COLUMN paid TYPE NUMERIC; 47 48ALTER TABLE tax ALTER COLUMN rate TYPE numeric; 49 50ALTER TABLE oe ALTER COLUMN amount TYPE NUMERIC; 51ALTER TABLE oe ALTER COLUMN netamount TYPE NUMERIC; 52 53ALTER TABLE orderitems ALTER COLUMN qty TYPE numeric; 54ALTER TABLE orderitems ALTER COLUMN sellprice TYPE NUMERIC; 55ALTER TABLE orderitems ALTER COLUMN discount TYPE numeric; 56ALTER TABLE orderitems ALTER COLUMN ship TYPE numeric; 57 58ALTER TABLE exchangerate ALTER COLUMN buy TYPE numeric; 59ALTER TABLE exchangerate ALTER COLUMN sell TYPE numeric; 60 61ALTER TABLE vendor ALTER COLUMN discount TYPE numeric; 62ALTER TABLE vendor ALTER COLUMN creditlimit TYPE numeric; 63 64ALTER TABLE project ALTER COLUMN production TYPE numeric; 65ALTER TABLE project ALTER COLUMN completed TYPE numeric; 66 67ALTER TABLE business ALTER COLUMN discount TYPE numeric; 68 69ALTER TABLE inventory ALTER COLUMN qty TYPE numeric; 70 71ALTER TABLE partsvendor ALTER COLUMN lastcost TYPE NUMERIC; 72 73ALTER TABLE partscustomer ALTER COLUMN pricebreak TYPE numeric; 74ALTER TABLE partscustomer ALTER COLUMN sellprice TYPE NUMERIC; 75 76ALTER TABLE jcitems ALTER COLUMN qty TYPE numeric; 77ALTER TABLE jcitems ALTER COLUMN allocated TYPE numeric; 78ALTER TABLE jcitems ALTER COLUMN sellprice TYPE NUMERIC; 79ALTER TABLE jcitems ALTER COLUMN fxsellprice TYPE NUMERIC; 80 81-- The query rewrite rule necessary to notify the email app that a new report 82-- needs to be sent to the designated administrator. 83-- By Chris Travers 84-- chris@metatrontech.com 85-- Licensed under the GNU GPL 2.0 or later at your option. See accompanying 86-- GPL.txt 87 88CREATE OR REPLACE FUNCTION trigger_parts_short() RETURNS TRIGGER 89AS 90' 91BEGIN 92 IF NEW.onhand >= NEW.rop THEN 93 NOTIFY parts_short; 94 END IF; 95 RETURN NEW; 96END; 97' LANGUAGE PLPGSQL; 98-- end function 99 100CREATE TRIGGER parts_short AFTER UPDATE ON parts 101FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short(); 102 103create table transactions ( 104 id int PRIMARY KEY, 105 table_name text 106); 107 108insert into transactions (id, table_name) SELECT id, 'ap' FROM ap; 109 110CREATE RULE ap_id_track_i AS ON insert TO ap 111DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ap'); 112 113CREATE RULE ap_id_track_u AS ON update TO ap 114DO UPDATE transactions SET id = new.id WHERE id = old.id; 115 116insert into transactions (id, table_name) SELECT id, 'ar' FROM ar; 117 118CREATE RULE ar_id_track_i AS ON insert TO ar 119DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ar'); 120 121CREATE RULE ar_id_track_u AS ON update TO ar 122DO UPDATE transactions SET id = new.id WHERE id = old.id; 123 124INSERT INTO transactions (id, table_name) SELECT id, 'business' FROM business; 125 126CREATE RULE business_id_track_i AS ON insert TO business 127DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'business'); 128 129CREATE RULE business_id_track_u AS ON update TO business 130DO UPDATE transactions SET id = new.id WHERE id = old.id; 131 132INSERT INTO transactions (id, table_name) SELECT id, 'chart' FROM chart; 133 134CREATE RULE chart_id_track_i AS ON insert TO chart 135DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'chart'); 136 137CREATE RULE chart_id_track_u AS ON update TO chart 138DO UPDATE transactions SET id = new.id WHERE id = old.id; 139 140INSERT INTO transactions (id, table_name) SELECT id, 'customer' FROM customer; 141 142CREATE RULE customer_id_track_i AS ON insert TO customer 143DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'customer'); 144 145CREATE RULE customer_id_track_u AS ON update TO customer 146DO UPDATE transactions SET id = new.id WHERE id = old.id; 147 148INSERT INTO transactions (id, table_name) SELECT id, 'department' FROM department; 149 150CREATE RULE department_id_track_i AS ON insert TO department 151DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'department'); 152 153CREATE RULE department_id_track_u AS ON update TO department 154DO UPDATE transactions SET id = new.id WHERE id = old.id; 155 156INSERT INTO transactions (id, table_name) SELECT id, 'employee' FROM employee; 157 158CREATE RULE employee_id_track_i AS ON insert TO employee 159DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee'); 160 161CREATE RULE employee_id_track_u AS ON update TO employee 162DO UPDATE transactions SET id = new.id WHERE id = old.id; 163 164INSERT INTO transactions (id, table_name) SELECT id, 'gl' FROM gl; 165 166CREATE RULE gl_id_track_i AS ON insert TO gl 167DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'gl'); 168 169CREATE RULE gl_id_track_u AS ON update TO gl 170DO UPDATE transactions SET id = new.id WHERE id = old.id; 171 172INSERT INTO transactions (id, table_name) SELECT id, 'oe' FROM oe; 173 174CREATE RULE oe_id_track_i AS ON insert TO oe 175DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'oe'); 176 177CREATE RULE oe_id_track_u AS ON update TO oe 178DO UPDATE transactions SET id = new.id WHERE id = old.id; 179 180INSERT INTO transactions (id, table_name) SELECT id, 'parts' FROM parts; 181 182CREATE RULE parts_id_track_i AS ON insert TO parts 183DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'parts'); 184 185CREATE RULE parts_id_track_u AS ON update TO parts 186DO UPDATE transactions SET id = new.id WHERE id = old.id; 187 188INSERT INTO transactions (id, table_name) SELECT id, 'partsgroup' FROM partsgroup; 189 190CREATE RULE partsgroup_id_track_i AS ON insert TO partsgroup 191DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'partsgroup'); 192 193CREATE RULE partsgroup_id_track_u AS ON update TO partsgroup 194DO UPDATE transactions SET id = new.id WHERE id = old.id; 195 196INSERT INTO transactions (id, table_name) SELECT id, 'pricegroup' FROM pricegroup; 197 198CREATE RULE pricegroup_id_track_i AS ON insert TO pricegroup 199DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'pricegroup'); 200 201CREATE RULE pricegroup_id_track_u AS ON update TO pricegroup 202DO UPDATE transactions SET id = new.id WHERE id = old.id; 203 204INSERT INTO transactions (id, table_name) SELECT id, 'project' FROM project; 205 206CREATE RULE project_id_track_i AS ON insert TO project 207DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'project'); 208 209CREATE RULE project_id_track_u AS ON update TO project 210DO UPDATE transactions SET id = new.id WHERE id = old.id; 211 212INSERT INTO transactions (id, table_name) SELECT id, 'vendor' FROM vendor; 213 214CREATE RULE vendor_id_track_i AS ON insert TO vendor 215DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'vendor'); 216 217CREATE RULE employee_id_track_u AS ON update TO vendor 218DO UPDATE transactions SET id = new.id WHERE id = old.id; 219 220INSERT INTO transactions (id, table_name) SELECT id, 'warehouse' FROM warehouse; 221 222CREATE RULE warehouse_id_track_i AS ON insert TO warehouse 223DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee'); 224 225CREATE RULE warehouse_id_track_u AS ON update TO warehouse 226DO UPDATE transactions SET id = new.id WHERE id = old.id; 227 228 229CREATE TABLE custom_table_catalog ( 230table_id SERIAL PRIMARY KEY, 231extends TEXT, 232table_name TEXT 233); 234 235CREATE TABLE custom_field_catalog ( 236field_id SERIAL PRIMARY KEY, 237table_id INT REFERENCES custom_table_catalog, 238field_name TEXT 239); 240CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR) 241RETURNS BOOL AS 242' 243DECLARE 244table_name ALIAS FOR $1; 245new_field_name ALIAS FOR $2; 246field_datatype ALIAS FOR $3; 247 248BEGIN 249 EXECUTE ''SELECT TABLE_ID FROM custom_table_catalog 250 WHERE extends = '''''' || table_name || '''''' ''; 251 IF NOT FOUND THEN 252 BEGIN 253 INSERT INTO custom_table_catalog (extends) VALUES (table_name); 254 EXECUTE ''CREATE TABLE custom_''||table_name || 255 '' (row_id INT)''; 256 EXCEPTION WHEN duplicate_table THEN 257 -- do nothing 258 END; 259 END IF; 260 EXECUTE ''INSERT INTO custom_field_catalog (field_name, table_id) 261 VALUES ( '''''' || new_field_name ||'''''', (SELECT table_id FROM custom_table_catalog 262 WHERE extends = ''''''|| table_name || ''''''))''; 263 EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN '' 264 || new_field_name || '' '' || field_datatype; 265 RETURN TRUE; 266END; 267' LANGUAGE PLPGSQL; 268-- end function 269 270CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR) 271RETURNS BOOL AS 272' 273DECLARE 274table_name ALIAS FOR $1; 275custom_field_name ALIAS FOR $2; 276BEGIN 277 DELETE FROM custom_field_catalog 278 WHERE field_name = custom_field_name AND 279 table_id = (SELECT table_id FROM custom_table_catalog 280 WHERE extends = table_name); 281 EXECUTE ''ALTER TABLE custom_'' || table_name || 282 '' DROP COLUMN '' || custom_field_name; 283 RETURN TRUE; 284END; 285' LANGUAGE PLPGSQL; 286-- end function 287 288UPDATE defaults SET version = '2.6.18'; 289COMMIT; 290