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