summaryrefslogtreecommitdiff
path: root/sql/legacy/Pg-upgrade-2.6.17-2.6.18.sql
blob: 7d27c2a420a5b30fc5bad353304cd588efe4a952 (plain)
  1. BEGIN;
  2. ALTER 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. LOCK acc_trans in EXCLUSIVE mode;
  8. ALTER TABLE acc_trans ADD COLUMN entry_id bigint;
  9. CREATE SEQUENCE acctrans_entry_id_seq;
  10. ALTER TABLE acc_trans ALTER COLUMN entry_id SET DEFAULT nextval('acctrans_entry_id_seq');
  11. UPDATE acc_trans SET entry_id = nextval('acctrans_entry_id_seq');
  12. ALTER TABLE acc_trans ADD PRIMARY key (entry_id);
  13. -- We should probably add a foreign key to chart.id
  14. ALTER TABLE acc_trans ADD FOREIGN KEY (chart_id) REFERENCES chart (id);
  15. -- Start changing floats
  16. ALTER TABLE acc_trans ALTER COLUMN amount TYPE NUMERIC;
  17. -- This may break someone if they for some reason have an actual float type in the qty column
  18. ALTER TABLE invoice ALTER COLUMN qty TYPE numeric;
  19. ALTER TABLE invoice ALTER COLUMN allocated TYPE numeric;
  20. ALTER TABLE invoice ALTER COLUMN sellprice TYPE NUMERIC;
  21. ALTER TABLE invoice ALTER COLUMN fxsellprice TYPE NUMERIC;
  22. ALTER TABLE customer ALTER COLUMN discount TYPE numeric;
  23. ALTER TABLE customer ALTER COLUMN creditlimit TYPE NUMERIC;
  24. ALTER TABLE parts ALTER COLUMN listprice TYPE NUMERIC;
  25. ALTER TABLE parts ALTER COLUMN sellprice TYPE NUMERIC;
  26. ALTER TABLE parts ALTER COLUMN lastcost TYPE NUMERIC;
  27. ALTER TABLE parts ALTER COLUMN weight TYPE numeric;
  28. ALTER TABLE parts ALTER COLUMN onhand TYPE numeric;
  29. ALTER TABLE parts ALTER COLUMN avgcost TYPE NUMERIC;
  30. ALTER TABLE assembly ALTER COLUMN qty TYPE numeric;
  31. ALTER TABLE ar ALTER COLUMN amount TYPE NUMERIC;
  32. ALTER TABLE ar ALTER COLUMN netamount TYPE NUMERIC;
  33. ALTER TABLE ar ALTER COLUMN paid TYPE NUMERIC;
  34. ALTER TABLE ap ALTER COLUMN amount TYPE NUMERIC;
  35. ALTER TABLE ap ALTER COLUMN netamount TYPE NUMERIC;
  36. ALTER TABLE ap ALTER COLUMN paid TYPE NUMERIC;
  37. ALTER TABLE tax ALTER COLUMN rate TYPE numeric;
  38. ALTER TABLE oe ALTER COLUMN amount TYPE NUMERIC;
  39. ALTER TABLE oe ALTER COLUMN netamount TYPE NUMERIC;
  40. ALTER TABLE orderitems ALTER COLUMN qty TYPE numeric;
  41. ALTER TABLE orderitems ALTER COLUMN sellprice TYPE NUMERIC;
  42. ALTER TABLE orderitems ALTER COLUMN discount TYPE numeric;
  43. ALTER TABLE orderitems ALTER COLUMN ship TYPE numeric;
  44. ALTER TABLE exchangerate ALTER COLUMN buy TYPE numeric;
  45. ALTER TABLE exchangerate ALTER COLUMN sell TYPE numeric;
  46. ALTER TABLE vendor ALTER COLUMN discount TYPE numeric;
  47. ALTER TABLE vendor ALTER COLUMN creditlimit TYPE numeric;
  48. ALTER TABLE project ALTER COLUMN production TYPE numeric;
  49. ALTER TABLE project ALTER COLUMN completed TYPE numeric;
  50. ALTER TABLE business ALTER COLUMN discount TYPE numeric;
  51. ALTER TABLE inventory ALTER COLUMN qty TYPE numeric;
  52. ALTER TABLE partsvendor ALTER COLUMN lastcost TYPE NUMERIC;
  53. ALTER TABLE partscustomer ALTER COLUMN pricebreak TYPE numeric;
  54. ALTER TABLE partscustomer ALTER COLUMN sellprice TYPE NUMERIC;
  55. ALTER TABLE jcitems ALTER COLUMN qty TYPE numeric;
  56. ALTER TABLE jcitems ALTER COLUMN allocated TYPE numeric;
  57. ALTER TABLE jcitems ALTER COLUMN sellprice TYPE NUMERIC;
  58. ALTER TABLE jcitems ALTER COLUMN fxsellprice TYPE NUMERIC;
  59. -- The query rewrite rule necessary to notify the email app that a new report
  60. -- needs to be sent to the designated administrator.
  61. -- By Chris Travers
  62. -- chris@metatrontech.com
  63. -- Licensed under the GNU GPL 2.0 or later at your option. See accompanying
  64. -- GPL.txt
  65. CREATE OR REPLACE FUNCTION trigger_parts_short() RETURNS TRIGGER
  66. AS
  67. '
  68. BEGIN
  69. IF NEW.onhand >= NEW.rop THEN
  70. NOTIFY parts_short;
  71. END IF;
  72. RETURN NEW;
  73. END;
  74. ' LANGUAGE PLPGSQL;
  75. -- end function
  76. CREATE TRIGGER parts_short AFTER UPDATE ON parts
  77. FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short();
  78. create table transactions (
  79. id int PRIMARY KEY,
  80. table_name text
  81. );
  82. insert into transactions (id, table_name) SELECT id, 'ap' FROM ap;
  83. CREATE RULE ap_id_track_i AS ON insert TO ap
  84. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ap');
  85. CREATE RULE ap_id_track_u AS ON update TO ap
  86. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  87. insert into transactions (id, table_name) SELECT id, 'ar' FROM ar;
  88. CREATE RULE ar_id_track_i AS ON insert TO ar
  89. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ar');
  90. CREATE RULE ar_id_track_u AS ON update TO ar
  91. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  92. INSERT INTO transactions (id, table_name) SELECT id, 'business' FROM business;
  93. CREATE RULE business_id_track_i AS ON insert TO business
  94. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'business');
  95. CREATE RULE business_id_track_u AS ON update TO business
  96. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  97. INSERT INTO transactions (id, table_name) SELECT id, 'chart' FROM chart;
  98. CREATE RULE chart_id_track_i AS ON insert TO chart
  99. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'chart');
  100. CREATE RULE chart_id_track_u AS ON update TO chart
  101. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  102. INSERT INTO transactions (id, table_name) SELECT id, 'customer' FROM customer;
  103. CREATE RULE customer_id_track_i AS ON insert TO customer
  104. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'customer');
  105. CREATE RULE customer_id_track_u AS ON update TO customer
  106. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  107. INSERT INTO transactions (id, table_name) SELECT id, 'department' FROM department;
  108. CREATE RULE department_id_track_i AS ON insert TO department
  109. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'department');
  110. CREATE RULE department_id_track_u AS ON update TO department
  111. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  112. INSERT INTO transactions (id, table_name) SELECT id, 'employee' FROM employee;
  113. CREATE RULE employee_id_track_i AS ON insert TO employee
  114. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee');
  115. CREATE RULE employee_id_track_u AS ON update TO employee
  116. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  117. INSERT INTO transactions (id, table_name) SELECT id, 'gl' FROM gl;
  118. CREATE RULE gl_id_track_i AS ON insert TO gl
  119. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'gl');
  120. CREATE RULE gl_id_track_u AS ON update TO gl
  121. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  122. INSERT INTO transactions (id, table_name) SELECT id, 'oe' FROM oe;
  123. CREATE RULE oe_id_track_i AS ON insert TO oe
  124. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'oe');
  125. CREATE RULE oe_id_track_u AS ON update TO oe
  126. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  127. INSERT INTO transactions (id, table_name) SELECT id, 'parts' FROM parts;
  128. CREATE RULE parts_id_track_i AS ON insert TO parts
  129. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'parts');
  130. CREATE RULE parts_id_track_u AS ON update TO parts
  131. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  132. INSERT INTO transactions (id, table_name) SELECT id, 'partsgroup' FROM partsgroup;
  133. CREATE RULE partsgroup_id_track_i AS ON insert TO partsgroup
  134. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'partsgroup');
  135. CREATE RULE partsgroup_id_track_u AS ON update TO partsgroup
  136. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  137. INSERT INTO transactions (id, table_name) SELECT id, 'pricegroup' FROM pricegroup;
  138. CREATE RULE pricegroup_id_track_i AS ON insert TO pricegroup
  139. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'pricegroup');
  140. CREATE RULE pricegroup_id_track_u AS ON update TO pricegroup
  141. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  142. INSERT INTO transactions (id, table_name) SELECT id, 'project' FROM project;
  143. CREATE RULE project_id_track_i AS ON insert TO project
  144. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'project');
  145. CREATE RULE project_id_track_u AS ON update TO project
  146. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  147. INSERT INTO transactions (id, table_name) SELECT id, 'vendor' FROM vendor;
  148. CREATE RULE vendor_id_track_i AS ON insert TO vendor
  149. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'vendor');
  150. CREATE RULE employee_id_track_u AS ON update TO vendor
  151. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  152. INSERT INTO transactions (id, table_name) SELECT id, 'warehouse' FROM warehouse;
  153. CREATE RULE warehouse_id_track_i AS ON insert TO warehouse
  154. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee');
  155. CREATE RULE warehouse_id_track_u AS ON update TO warehouse
  156. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  157. CREATE TABLE custom_table_catalog (
  158. table_id SERIAL PRIMARY KEY,
  159. extends TEXT,
  160. table_name TEXT
  161. );
  162. CREATE TABLE custom_field_catalog (
  163. field_id SERIAL PRIMARY KEY,
  164. table_id INT REFERENCES custom_table_catalog,
  165. field_name TEXT
  166. );
  167. CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR)
  168. RETURNS BOOL AS
  169. '
  170. DECLARE
  171. table_name ALIAS FOR $1;
  172. new_field_name ALIAS FOR $2;
  173. field_datatype ALIAS FOR $3;
  174. BEGIN
  175. EXECUTE ''SELECT TABLE_ID FROM custom_table_catalog
  176. WHERE extends = '''''' || table_name || '''''' '';
  177. IF NOT FOUND THEN
  178. BEGIN
  179. INSERT INTO custom_table_catalog (extends) VALUES (table_name);
  180. EXECUTE ''CREATE TABLE custom_''||table_name ||
  181. '' (row_id INT)'';
  182. EXCEPTION WHEN duplicate_table THEN
  183. -- do nothing
  184. END;
  185. END IF;
  186. EXECUTE ''INSERT INTO custom_field_catalog (field_name, table_id)
  187. VALUES ( '''''' || new_field_name ||'''''', (SELECT table_id FROM custom_table_catalog
  188. WHERE extends = ''''''|| table_name || ''''''))'';
  189. EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN ''
  190. || new_field_name || '' '' || field_datatype;
  191. RETURN TRUE;
  192. END;
  193. ' LANGUAGE PLPGSQL;
  194. -- end function
  195. CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR)
  196. RETURNS BOOL AS
  197. '
  198. DECLARE
  199. table_name ALIAS FOR $1;
  200. custom_field_name ALIAS FOR $2;
  201. BEGIN
  202. DELETE FROM custom_field_catalog
  203. WHERE field_name = custom_field_name AND
  204. table_id = (SELECT table_id FROM custom_table_catalog
  205. WHERE extends = table_name);
  206. EXECUTE ''ALTER TABLE custom_'' || table_name ||
  207. '' DROP COLUMN '' || custom_field_name;
  208. RETURN TRUE;
  209. END;
  210. ' LANGUAGE PLPGSQL;
  211. -- end function
  212. UPDATE defaults SET version = '2.6.18';
  213. COMMIT;