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