summaryrefslogtreecommitdiff
path: root/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql
blob: 9594f9a0890008f71c51b19065eee80b3562032a (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 defaults ADD PRIMARY KEY (version);
  9. ALTER TABLE department ADD PRIMARY KEY (id);
  10. ALTER TABLE dpt_trans ADD PRIMARY KEY (trans_id);
  11. ALTER TABLE employee ADD PRIMARY KEY (id);
  12. ALTER TABLE exchangerate ADD PRIMARY KEY (curr, transdate);
  13. ALTER TABLE gifi ADD PRIMARY KEY (accno);
  14. ALTER TABLE gl ADD PRIMARY KEY (id);
  15. ALTER TABLE invoice ADD PRIMARY KEY (id);
  16. ALTER TABLE jcitems ADD PRIMARY KEY (id);
  17. ALTER TABLE language ADD PRIMARY KEY (code);
  18. ALTER TABLE makemodel ADD PRIMARY KEY (parts_id);
  19. ALTER TABLE oe ADD PRIMARY KEY (id);
  20. ALTER TABLE orderitems ADD PRIMARY KEY (id);
  21. ALTER TABLE parts ADD PRIMARY KEY (id);
  22. ALTER TABLE partsgroup ADD PRIMARY KEY (id);
  23. ALTER TABLE partstax ADD PRIMARY KEY (parts_id, chart_id);
  24. ALTER TABLE partstax ADD FOREIGN KEY (chart_id) REFERENCES chart (id);
  25. ALTER TABLE partstax ADD FOREIGN KEY (parts_id) REFERENCES parts (id);
  26. ALTER TABLE pricegroup ADD PRIMARY KEY (id);
  27. ALTER TABLE project ADD PRIMARY KEY (id);
  28. ALTER TABLE recurringemail ADD PRIMARY KEY (id);
  29. ALTER TABLE recurring ADD PRIMARY KEY (id);
  30. ALTER TABLE recurringprint ADD PRIMARY KEY (id);
  31. ALTER TABLE sic ADD PRIMARY KEY (code);
  32. ALTER TABLE status ADD PRIMARY KEY (trans_id);
  33. ALTER TABLE tax ADD PRIMARY KEY (chart_id);
  34. ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart (id);
  35. ALTER TABLE translation ADD PRIMARY KEY (trans_id, language_code);
  36. ALTER TABLE vendor ADD PRIMARY KEY (id);
  37. ALTER TABLE vendortax ADD PRIMARY KEY (vendor_id, chart_id);
  38. ALTER TABLE warehouse ADD PRIMARY KEY (id);
  39. ALTER TABLE yearend ADD PRIMARY KEY (trans_id);
  40. LOCK inventory in EXCLUSIVE mode;
  41. ALTER TABLE inventory ADD COLUMN entry_id bigint;
  42. CREATE SEQUENCE inventory_entry_id_seq;
  43. ALTER TABLE inventory ALTER COLUMN entry_id
  44. SET DEFAULT nextval('inventory_entry_id_seq');
  45. UPDATE inventory SET entry_id = nextval('inventory_entry_id_seq');
  46. ALTER TABLE inventory ADD PRIMARY key (entry_id);
  47. LOCK partscustomer IN EXCLUSIVE MODE;
  48. ALTER TABLE partscustomer ADD COLUMN entry_id int;
  49. CREATE SEQUENCE partscustomer_entry_id_seq;
  50. ALTER TABLE partscustomer ALTER COLUMN entry_id
  51. SET DEFAULT nextval('partscustomer_entry_id_seq');
  52. UPDATE partscustomer SET entry_id = nextval('partscustomer_entry_id_seq');
  53. ALTER TABLE partscustomer ADD PRIMARY KEY (entry_id);
  54. LOCK partsvendor IN EXCLUSIVE MODE;
  55. ALTER TABLE partsvendor ADD COLUMN entry_id int;
  56. CREATE SEQUENCE partsvendor_entry_id_seq;
  57. ALTER TABLE partsvendor ALTER COLUMN entry_id
  58. SET DEFAULT nextval('partsvendor_entry_id_seq');
  59. UPDATE partsvendor SET entry_id = nextval('partsvendor_entry_id_seq');
  60. ALTER TABLE partsvendor ADD PRIMARY KEY (entry_id);
  61. LOCK audittrail IN EXCLUSIVE MODE;
  62. ALTER TABLE audittrail ADD COLUMN entry_id int;
  63. CREATE SEQUENCE audittrail_entry_id_seq ;
  64. ALTER TABLE audittrail ALTER COLUMN entry_id
  65. SET DEFAULT nextval('audittrail_entry_id_seq');
  66. UPDATE audittrail SET entry_id = nextval('audittrail_entry_id_seq');
  67. ALTER TABLE audittrail ADD PRIMARY KEY (entry_id);
  68. LOCK shipto IN EXCLUSIVE MODE;
  69. ALTER TABLE shipto ADD COLUMN entry_id int;
  70. CREATE SEQUENCE shipto_entry_id_seq ;
  71. ALTER TABLE shipto ALTER COLUMN entry_id
  72. SET DEFAULT nextval('shipto_entry_id_seq');
  73. UPDATE shipto SET entry_id = nextval('shipto_entry_id_seq');
  74. ALTER TABLE shipto ADD PRIMARY KEY (entry_id);
  75. CREATE TABLE taxmodule (
  76. taxmodule_id serial PRIMARY KEY,
  77. taxmodulename text NOT NULL
  78. );
  79. INSERT INTO taxmodule (
  80. taxmodule_id, taxmodulename
  81. ) VALUES (
  82. 1, 'Simple'
  83. );
  84. CREATE TABLE taxcategory (
  85. taxcategory_id serial PRIMARY KEY,
  86. taxcategoryname text NOT NULL,
  87. taxmodule_id int NOT NULL REFERENCES taxmodule (taxmodule_id)
  88. );
  89. ALTER TABLE partstax ADD COLUMN taxcategory_id int REFERENCES taxcategory (taxcategory_id);
  90. LOCK tax IN EXCLUSIVE MODE;
  91. ALTER TABLE tax ADD COLUMN pass int DEFAULT 0;
  92. UPDATE tax SET pass = 0;
  93. ALTER TABLE tax ALTER COLUMN pass SET NOT NULL;
  94. ALTER TABLE tax ADD COLUMN taxmodule_id int REFERENCES taxmodule DEFAULT 1;
  95. UPDATE tax SET taxmodule_id = 1;
  96. ALTER TABLE tax ALTER COLUMN taxmodule_id SET NOT NULL;
  97. -- Fixed session table and add users table --
  98. BEGIN;
  99. LOCK session in EXCLUSIVE MODE;
  100. ALTER TABLE session ADD CONSTRAINT session_token_check check (length(token::text) = 32);
  101. ALTER TABLE session ADD column user_id integer not null references users(id);
  102. -- comment this out when user db is working:
  103. ALTER TABLE session ALTER COLUMN user_id DROP NOT NULL;
  104. LOCK users in EXCLUSIVE MODE;
  105. CREATE TABLE users (id serial UNIQUE, username varchar(30) PRIMARY KEY);
  106. COMMENT ON TABLE users 'username is the primary key because we don\'t want duplicate users';
  107. LOCK users_conf in EXCLUSIVE MODE;
  108. CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred,
  109. acs text,
  110. address text,
  111. businessnumber text,
  112. company text,
  113. countrycode text,
  114. currency text,
  115. dateformat text,
  116. dbconnect text,
  117. dbdriver text default 'Pg',
  118. dbhost text default 'localhost',
  119. dbname text,
  120. dboptions text,
  121. dbpasswd text,
  122. dbport text,
  123. dbuser text,
  124. email text,
  125. fax text,
  126. menuwidth text,
  127. name text,
  128. numberformat text,
  129. password varchar(32) check(length(password) = 32),
  130. print text,
  131. printer text,
  132. role text,
  133. sid text,
  134. signature text,
  135. stylesheet text,
  136. tel text,
  137. templates text,
  138. timeout numeric,
  139. vclimit numeric);
  140. COMMENT ON TABLE users_conf IS 'This is a completely dumb table that is a place holder to get usersconf into the database. Next major release will have a much more sane implementation';
  141. COMMENT ON COLUMN users_conf.id IS 'Yes primary key with a FOREIGN KEY to users(id) is correct';
  142. COMMENT ON COLUMN users_conf.password IS 'This means we have to get rid of the current password stuff and move to presumably md5()';
  143. COMMIT;
  144. -- Admin user --
  145. BEGIN;
  146. INSERT INTO users(username) VALUES ('admin');
  147. INSERT INTO users_conf(id,password) VALUES (currval('users_id_seq'),NULL);
  148. COMMIT;
  149. -- Functions
  150. CREATE FUNCTION create_user(text) RETURNS int4 AS $$
  151. INSERT INTO users(username) VALUES ('$1');
  152. SELECT currval('user_id_seq');
  153. $$ LANGUAGE 'SQL';
  154. COMMENT ON FUNCTION create_user(text) IS $$ Function to create user Returns users.id if successful, else it is an error. $$;
  155. CREATE FUNCTION update_user(int4,text) RETURNS int4 AS $$
  156. UPDATE users SET username = '$2' WHERE id = $1;
  157. SELECT 1;
  158. $$ LANGUAGE 'SQL';
  159. COMMENT ON FUNCTION update_user(int4,text) IS $$ Takes int4 which is users.id and text which is username. Will update username based on id. Username is unique $$;
  160. ALTER TABLE defaults RENAME TO old_defaults;
  161. CREATE TABLE defaults (
  162. setting_key TEXT PRIMARY KEY,
  163. value TEXT
  164. );
  165. COMMENT ON TABLE defaults IS $$This table replaces the old one column per value system with a simple key => value table$$;
  166. INSERT INTO defaults (setting_key, value)
  167. SELECT 'inventory_accno_id', inventory_accno_id FROM old_defaults
  168. UNION
  169. SELECT 'income_accno_id', income_accno_id FROM old_defaults
  170. UNION
  171. SELECT 'expense_accno_id', expense_accno_id FROM old_defaults
  172. UNION
  173. SELECT 'fxloss_accno_id', fxloss_accno_id FROM old_defaults
  174. UNION
  175. SELECT 'fxgain_accno_id', fxgain_accno_id FROM old_defaults
  176. UNION
  177. SELECT 'sinumber', sinumber FROM old_defaults
  178. UNION
  179. SELECT 'sonumber', sonumber FROM old_defaults
  180. UNION
  181. SELECT 'yearend', yearend FROM old_defaults
  182. UNION
  183. SELECT 'weightunit', weightunit FROM old_defaults
  184. UNION
  185. SELECT 'businessnumber', businessnumber FROM old_defaults
  186. UNION
  187. SELECT 'version', '1.2.0'
  188. UNION
  189. SELECT 'curr', curr FROM old_defaults
  190. UNION
  191. SELECT 'closedto', closedto FROM old_defaults
  192. UNION
  193. SELECT 'revtrans', revtrans FROM old_defaults
  194. UNION
  195. SELECT 'ponumber', ponumber FROM old_defaults
  196. UNION
  197. SELECT 'sqnumber', sqnumber FROM old_defaults
  198. UNION
  199. SELECT 'rfqnumber', rfqnumber FROM old_defaults
  200. UNION
  201. SELECT 'audittrail', audittrail FROM old_defaults
  202. UNION
  203. SELECT 'vinumber', vinumber FROM old_defaults
  204. UNION
  205. SELECT 'employeenumber', employeenumber FROM old_defaults
  206. UNION
  207. SELECT 'partnumber', partnumber FROM old_defaults
  208. UNION
  209. SELECT 'customernumber', customernumber FROM old_defaults
  210. UNION
  211. SELECT 'vendornumber', vendornumber FROM old_defaults
  212. UNION
  213. SELECT 'glnumber', glnumber FROM old_defaults
  214. UNION
  215. SELECT 'projectnumber', projectnumber FROM old_defaults
  216. UNION
  217. SELECT 'appname', 'LedgerSMB';
  218. DROP TABLE old_defaults;
  219. CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR)
  220. RETURNS BOOL AS
  221. 'BEGIN
  222. EXECUTE ''SELECT TABLE_ID FROM custom_table_catalog
  223. WHERE extends = '''''' || table_name || '''''' '';
  224. IF NOT FOUND THEN
  225. BEGIN
  226. INSERT INTO custom_table_catalog (extends)
  227. VALUES (table_name);
  228. EXECUTE ''CREATE TABLE custom_''||table_name ||
  229. '' (row_id INT PRIMARY KEY)'';
  230. EXCEPTION WHEN duplicate_table THEN
  231. -- do nothing
  232. END;
  233. END IF;
  234. EXECUTE ''INSERT INTO custom_field_catalog (field_name, table_id)
  235. VALUES ( '''''' || new_field_name ||'''''', (SELECT table_id FROM custom_table_catalog
  236. WHERE extends = ''''''|| table_name || ''''''))'';
  237. EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN ''
  238. || new_field_name || '' '' || field_datatype;
  239. RETURN TRUE;
  240. ' LANGUAGE PLPGSQL;
  241. END;
  242. COMMIT;