summaryrefslogtreecommitdiff
path: root/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql
blob: 020738a17ce316a0beadc07b79439ec37e1ffc2b (plain)
  1. BEGIN;
  2. ALTER TABLE ap ADD PRIMARY KEY (id);
  3. ALTER TABLE ar ADD PRIMARY KEY (id);
  4. ALTER TABLE assembly ADD PRIMARY KEY (id, parts_id);
  5. ALTER TABLE business ADD PRIMARY KEY (id);
  6. ALTER TABLE customer ADD PRIMARY KEY (id);
  7. ALTER TABLE customertax ADD PRIMARY KEY (customer_id, chart_id);
  8. ALTER TABLE department ADD PRIMARY KEY (id);
  9. ALTER TABLE dpt_trans ADD PRIMARY KEY (trans_id);
  10. ALTER TABLE employee ADD PRIMARY KEY (id);
  11. ALTER TABLE exchangerate ADD PRIMARY KEY (curr, transdate);
  12. ALTER TABLE gifi ADD PRIMARY KEY (accno);
  13. ALTER TABLE gl ADD PRIMARY KEY (id);
  14. ALTER TABLE invoice ADD PRIMARY KEY (id);
  15. ALTER TABLE jcitems ADD PRIMARY KEY (id);
  16. ALTER TABLE language ADD PRIMARY KEY (code);
  17. ALTER TABLE makemodel ADD PRIMARY KEY (parts_id);
  18. ALTER TABLE oe ADD PRIMARY KEY (id);
  19. SELECT setval('orderitemsid', 1);
  20. UPDATE orderitems SET id = nextval('orderitemsid');
  21. ALTER TABLE orderitems ADD PRIMARY KEY (id);
  22. ALTER TABLE parts ADD PRIMARY KEY (id);
  23. ALTER TABLE partsgroup ADD PRIMARY KEY (id);
  24. ALTER TABLE partstax ADD PRIMARY KEY (parts_id, chart_id);
  25. ALTER TABLE partstax ADD FOREIGN KEY (chart_id) REFERENCES chart (id);
  26. ALTER TABLE partstax ADD FOREIGN KEY (parts_id) REFERENCES parts (id);
  27. ALTER TABLE pricegroup ADD PRIMARY KEY (id);
  28. ALTER TABLE project ADD PRIMARY KEY (id);
  29. ALTER TABLE recurringemail ADD PRIMARY KEY (id);
  30. ALTER TABLE recurring ADD PRIMARY KEY (id);
  31. ALTER TABLE recurringprint ADD PRIMARY KEY (id);
  32. ALTER TABLE sic ADD PRIMARY KEY (code);
  33. ALTER TABLE status ADD PRIMARY KEY (trans_id);
  34. -- Removing the primary key below since this is not quite the best way
  35. -- do this. The tax table could have multiple rows per chart_id.
  36. -- We need a better fix for 1.3 (perhaps changing date to timestamp and
  37. -- and defaulting to infinity.
  38. -- ALTER TABLE tax ADD PRIMARY KEY (chart_id);
  39. -- ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart (id);
  40. ALTER TABLE translation ADD PRIMARY KEY (trans_id, language_code);
  41. ALTER TABLE vendor ADD PRIMARY KEY (id);
  42. ALTER TABLE vendortax ADD PRIMARY KEY (vendor_id, chart_id);
  43. ALTER TABLE warehouse ADD PRIMARY KEY (id);
  44. ALTER TABLE yearend ADD PRIMARY KEY (trans_id);
  45. LOCK inventory in EXCLUSIVE mode;
  46. ALTER TABLE inventory ADD COLUMN entry_id bigint;
  47. CREATE SEQUENCE inventory_entry_id_seq;
  48. ALTER TABLE inventory ALTER COLUMN entry_id
  49. SET DEFAULT nextval('inventory_entry_id_seq');
  50. UPDATE inventory SET entry_id = nextval('inventory_entry_id_seq');
  51. ALTER TABLE inventory ADD PRIMARY key (entry_id);
  52. LOCK partscustomer IN EXCLUSIVE MODE;
  53. ALTER TABLE partscustomer ADD COLUMN entry_id int;
  54. CREATE SEQUENCE partscustomer_entry_id_seq;
  55. ALTER TABLE partscustomer ALTER COLUMN entry_id
  56. SET DEFAULT nextval('partscustomer_entry_id_seq');
  57. UPDATE partscustomer SET entry_id = nextval('partscustomer_entry_id_seq');
  58. ALTER TABLE partscustomer ADD PRIMARY KEY (entry_id);
  59. LOCK partsvendor IN EXCLUSIVE MODE;
  60. ALTER TABLE partsvendor ADD COLUMN entry_id int;
  61. CREATE SEQUENCE partsvendor_entry_id_seq;
  62. ALTER TABLE partsvendor ALTER COLUMN entry_id
  63. SET DEFAULT nextval('partsvendor_entry_id_seq');
  64. UPDATE partsvendor SET entry_id = nextval('partsvendor_entry_id_seq');
  65. ALTER TABLE partsvendor ADD PRIMARY KEY (entry_id);
  66. LOCK audittrail IN EXCLUSIVE MODE;
  67. ALTER TABLE audittrail ADD COLUMN entry_id int;
  68. CREATE SEQUENCE audittrail_entry_id_seq ;
  69. ALTER TABLE audittrail ALTER COLUMN entry_id
  70. SET DEFAULT nextval('audittrail_entry_id_seq');
  71. UPDATE audittrail SET entry_id = nextval('audittrail_entry_id_seq');
  72. ALTER TABLE audittrail ADD PRIMARY KEY (entry_id);
  73. LOCK shipto IN EXCLUSIVE MODE;
  74. ALTER TABLE shipto ADD COLUMN entry_id int;
  75. CREATE SEQUENCE shipto_entry_id_seq ;
  76. ALTER TABLE shipto ALTER COLUMN entry_id
  77. SET DEFAULT nextval('shipto_entry_id_seq');
  78. UPDATE shipto SET entry_id = nextval('shipto_entry_id_seq');
  79. ALTER TABLE shipto ADD PRIMARY KEY (entry_id);
  80. CREATE TABLE taxmodule (
  81. taxmodule_id serial PRIMARY KEY,
  82. taxmodulename text NOT NULL
  83. );
  84. INSERT INTO taxmodule (
  85. taxmodule_id, taxmodulename
  86. ) VALUES (
  87. 1, 'Simple'
  88. );
  89. CREATE TABLE taxcategory (
  90. taxcategory_id serial PRIMARY KEY,
  91. taxcategoryname text NOT NULL,
  92. taxmodule_id int NOT NULL REFERENCES taxmodule (taxmodule_id)
  93. );
  94. ALTER TABLE partstax ADD COLUMN taxcategory_id int REFERENCES taxcategory (taxcategory_id);
  95. LOCK tax IN EXCLUSIVE MODE;
  96. ALTER TABLE tax ADD COLUMN pass int DEFAULT 0;
  97. UPDATE tax SET pass = 0;
  98. ALTER TABLE tax ALTER COLUMN pass SET NOT NULL;
  99. ALTER TABLE tax ADD COLUMN taxmodule_id int REFERENCES taxmodule DEFAULT 1;
  100. UPDATE tax SET taxmodule_id = 1;
  101. ALTER TABLE tax ALTER COLUMN taxmodule_id SET NOT NULL;
  102. ALTER TABLE defaults RENAME TO old_defaults;
  103. CREATE TABLE defaults (
  104. setting_key TEXT PRIMARY KEY,
  105. value TEXT
  106. );
  107. COMMENT ON TABLE defaults IS $$This table replaces the old one column per value system with a simple key => value table$$;
  108. INSERT INTO defaults (setting_key, value)
  109. SELECT 'inventory_accno_id', inventory_accno_id::text FROM old_defaults
  110. UNION
  111. SELECT 'income_accno_id', income_accno_id::text FROM old_defaults
  112. UNION
  113. SELECT 'expense_accno_id', expense_accno_id::text FROM old_defaults
  114. UNION
  115. SELECT 'fxloss_accno_id', fxloss_accno_id::text FROM old_defaults
  116. UNION
  117. SELECT 'fxgain_accno_id', fxgain_accno_id::text FROM old_defaults
  118. UNION
  119. SELECT 'sinumber', sinumber::text FROM old_defaults
  120. UNION
  121. SELECT 'sonumber', sonumber::text FROM old_defaults
  122. UNION
  123. SELECT 'yearend', yearend::text FROM old_defaults
  124. UNION
  125. SELECT 'weightunit', weightunit::text FROM old_defaults
  126. UNION
  127. SELECT 'businessnumber', businessnumber::text FROM old_defaults
  128. UNION
  129. SELECT 'version', '1.2.0'::text
  130. UNION
  131. SELECT 'curr', curr::text FROM old_defaults
  132. UNION
  133. SELECT 'closedto', to_char(closedto, 'YYYY-MM-DD') FROM old_defaults
  134. UNION
  135. SELECT 'revtrans', (CASE WHEN revtrans IS NULL THEN NULL
  136. WHEN revtrans THEN '1'
  137. ELSE '0' END) FROM old_defaults
  138. UNION
  139. SELECT 'ponumber', ponumber::text FROM old_defaults
  140. UNION
  141. SELECT 'sqnumber', sqnumber::text FROM old_defaults
  142. UNION
  143. SELECT 'rfqnumber', rfqnumber::text FROM old_defaults
  144. UNION
  145. SELECT 'audittrail', (CASE WHEN audittrail IS NULL THEN NULL
  146. WHEN audittrail THEN '1'
  147. ELSE '0' END) FROM old_defaults
  148. UNION
  149. SELECT 'vinumber', vinumber::text FROM old_defaults
  150. UNION
  151. SELECT 'employeenumber', employeenumber::text FROM old_defaults
  152. UNION
  153. SELECT 'partnumber', partnumber::text FROM old_defaults
  154. UNION
  155. SELECT 'customernumber', customernumber::text FROM old_defaults
  156. UNION
  157. SELECT 'vendornumber', vendornumber::text FROM old_defaults
  158. UNION
  159. SELECT 'glnumber', glnumber::text FROM old_defaults
  160. UNION
  161. SELECT 'projectnumber', projectnumber::text FROM old_defaults
  162. UNION
  163. SELECT 'appname', 'LedgerSMB'::text;
  164. DROP TABLE old_defaults;
  165. CREATE OR REPLACE FUNCTION del_exchangerate() RETURNS TRIGGER AS '
  166. declare
  167. t_transdate date;
  168. t_curr char(3);
  169. t_id int;
  170. d_curr text;
  171. begin
  172. select into d_curr substr(value,1,3) from defaults where setting_key = ''curr'';
  173. if TG_RELNAME = ''ar'' then
  174. select into t_curr, t_transdate curr, transdate from ar where id = old.id;
  175. end if;
  176. if TG_RELNAME = ''ap'' then
  177. select into t_curr, t_transdate curr, transdate from ap where id = old.id;
  178. end if;
  179. if TG_RELNAME = ''oe'' then
  180. select into t_curr, t_transdate curr, transdate from oe where id = old.id;
  181. end if;
  182. if d_curr != t_curr then
  183. select into t_id a.id from acc_trans ac
  184. join ar a on (a.id = ac.trans_id)
  185. where a.curr = t_curr
  186. and ac.transdate = t_transdate
  187. except select a.id from ar a where a.id = old.id
  188. union
  189. select a.id from acc_trans ac
  190. join ap a on (a.id = ac.trans_id)
  191. where a.curr = t_curr
  192. and ac.transdate = t_transdate
  193. except select a.id from ap a where a.id = old.id
  194. union
  195. select o.id from oe o
  196. where o.curr = t_curr
  197. and o.transdate = t_transdate
  198. except select o.id from oe o where o.id = old.id;
  199. if not found then
  200. delete from exchangerate where curr = t_curr and transdate = t_transdate;
  201. end if;
  202. end if;
  203. return old;
  204. end;
  205. ' language 'plpgsql';
  206. CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR)
  207. RETURNS BOOL AS
  208. 'BEGIN
  209. EXECUTE ''SELECT TABLE_ID FROM custom_table_catalog
  210. WHERE extends = '''''' || table_name || '''''' '';
  211. IF NOT FOUND THEN
  212. BEGIN
  213. INSERT INTO custom_table_catalog (extends)
  214. VALUES (table_name);
  215. EXECUTE ''CREATE TABLE custom_''||table_name ||
  216. '' (row_id INT PRIMARY KEY)'';
  217. EXCEPTION WHEN duplicate_table THEN
  218. -- do nothing
  219. END;
  220. END IF;
  221. EXECUTE ''INSERT INTO custom_field_catalog (field_name, table_id)
  222. VALUES ( '''''' || new_field_name ||'''''', (SELECT table_id FROM custom_table_catalog
  223. WHERE extends = ''''''|| table_name || ''''''))'';
  224. EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN ''
  225. || new_field_name || '' '' || field_datatype;
  226. RETURN TRUE;
  227. END;
  228. ' LANGUAGE PLPGSQL;
  229. COMMIT;