12.0.0-13.0.0.sql 31 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596
  1. --
  2. -- Be carefull to requests order.
  3. -- This file must be loaded by calling /install/index.php page
  4. -- when current version is 13.0.0 or higher.
  5. --
  6. -- To restrict request to Mysql version x.y minimum use -- VMYSQLx.y
  7. -- To restrict request to Pgsql version x.y minimum use -- VPGSQLx.y
  8. -- To rename a table: ALTER TABLE llx_table RENAME TO llx_table_new;
  9. -- To add a column: ALTER TABLE llx_table ADD COLUMN newcol varchar(60) NOT NULL DEFAULT '0' AFTER existingcol;
  10. -- To rename a column: ALTER TABLE llx_table CHANGE COLUMN oldname newname varchar(60);
  11. -- To drop a column: ALTER TABLE llx_table DROP COLUMN oldname;
  12. -- To change type of field: ALTER TABLE llx_table MODIFY COLUMN name varchar(60);
  13. -- To drop a foreign key: ALTER TABLE llx_table DROP FOREIGN KEY fk_name;
  14. -- To create a unique index ALTER TABLE llx_table ADD UNIQUE INDEX uk_table_field (field);
  15. -- To drop an index: -- VMYSQL4.1 DROP INDEX nomindex on llx_table
  16. -- To drop an index: -- VPGSQL8.2 DROP INDEX nomindex
  17. -- To make pk to be auto increment (mysql): -- VMYSQL4.3 ALTER TABLE llx_table CHANGE COLUMN rowid rowid INTEGER NOT NULL AUTO_INCREMENT;
  18. -- To make pk to be auto increment (postgres):
  19. -- -- VPGSQL8.2 CREATE SEQUENCE llx_table_rowid_seq OWNED BY llx_table.rowid;
  20. -- -- VPGSQL8.2 ALTER TABLE llx_table ADD PRIMARY KEY (rowid);
  21. -- -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN rowid SET DEFAULT nextval('llx_table_rowid_seq');
  22. -- -- VPGSQL8.2 SELECT setval('llx_table_rowid_seq', MAX(rowid)) FROM llx_table;
  23. -- To set a field as NULL: -- VMYSQL4.3 ALTER TABLE llx_table MODIFY COLUMN name varchar(60) NULL;
  24. -- To set a field as NULL: -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name DROP NOT NULL;
  25. -- To set a field as NOT NULL: -- VMYSQL4.3 ALTER TABLE llx_table MODIFY COLUMN name varchar(60) NOT NULL;
  26. -- To set a field as NOT NULL: -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name SET NOT NULL;
  27. -- To set a field as default NULL: -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name SET DEFAULT NULL;
  28. -- Note: fields with type BLOB/TEXT can't have default value.
  29. -- Missing in v12 or lower
  30. ALTER TABLE llx_payment_salary MODIFY COLUMN ref varchar(30) NULL;
  31. ALTER TABLE llx_payment_various MODIFY COLUMN ref varchar(30) NULL;
  32. ALTER TABLE llx_prelevement_bons ADD COLUMN type varchar(16) DEFAULT 'debit-order';
  33. ALTER TABLE llx_prelevement_facture CHANGE COLUMN fk_facture_foun fk_facture_fourn integer NULL;
  34. ALTER TABLE llx_prelevement_facture_demande ADD INDEX idx_prelevement_facture_demande_fk_facture (fk_facture);
  35. ALTER TABLE llx_prelevement_facture_demande ADD INDEX idx_prelevement_facture_demande_fk_facture_fourn (fk_facture_fourn);
  36. ALTER TABLE llx_document_model MODIFY COLUMN type varchar(64);
  37. ALTER TABLE llx_bom_bom MODIFY COLUMN duration double(24,8);
  38. ALTER TABLE llx_bom_bom_extrafields ADD INDEX idx_bom_bom_extrafields_fk_object (fk_object);
  39. create table llx_mrp_mo_extrafields
  40. (
  41. rowid integer AUTO_INCREMENT PRIMARY KEY,
  42. tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  43. fk_object integer NOT NULL,
  44. import_key varchar(14) -- import key
  45. ) ENGINE=innodb;
  46. ALTER TABLE llx_mrp_mo_extrafields DROP INDEX idx_fk_object;
  47. ALTER TABLE llx_mrp_mo_extrafields ADD INDEX idx_mrp_mo_fk_object(fk_object);
  48. -- For v13
  49. insert into llx_c_tva(rowid,fk_pays,taux,recuperableonly,note,active) values (111,11, '0','0','No Sales Tax',1);
  50. insert into llx_c_tva(rowid,fk_pays,taux,recuperableonly,note,active) values (112,11, '4','0','Sales Tax 4%',1);
  51. insert into llx_c_tva(rowid,fk_pays,taux,recuperableonly,note,active) values (113,11, '6','0','Sales Tax 6%',1);
  52. ALTER TABLE llx_bom_bom ADD COLUMN bomtype integer DEFAULT 0;
  53. UPDATE llx_emailcollector_emailcollector SET ref = 'Collect_Ticket_Requests' WHERE ref = 'Collect_Ticket_Requets';
  54. UPDATE llx_emailcollector_emailcollector SET ref = 'Collect_Responses_In' WHERE ref = 'Collect_Responses';
  55. UPDATE llx_document_model set nom = 'standard' where nom = 'Standard' and type ='stock';
  56. UPDATE llx_document_model set nom = 'stdmovement', type = 'movement' where nom = 'StdMouvement' and type ='mouvement';
  57. UPDATE llx_const SET value = 0 WHERE name = 'FACTURE_TVAOPTION' and value = 'franchise';
  58. UPDATE llx_const SET value = 1 WHERE name = 'FACTURE_TVAOPTION' and value <> 'franchise' AND value <> '0' AND value <> '1';
  59. ALTER TABLE llx_commande MODIFY COLUMN date_livraison DATETIME;
  60. ALTER TABLE llx_website ADD COLUMN position integer DEFAULT 0;
  61. ALTER TABLE llx_establishment ADD COLUMN ref varchar(30);
  62. ALTER TABLE llx_establishment ADD COLUMN label varchar(128);
  63. UPDATE llx_establishment SET ref = rowid WHERE ref IS NULL;
  64. ALTER TABLE llx_establishment MODIFY COLUMN ref varchar(30) NOT NULL;
  65. ALTER TABLE llx_establishment MODIFY COLUMN label varchar(128);
  66. INSERT INTO llx_const (name, entity, value, type, visible) VALUES ('PRODUCT_PRICE_BASE_TYPE', 0, 'HT', 'string', 0);
  67. ALTER TABLE llx_subscription MODIFY COLUMN datef DATETIME;
  68. ALTER TABLE llx_loan_schedule ADD column fk_payment_loan INTEGER;
  69. ALTER TABLE llx_user DROP COLUMN jabberid;
  70. ALTER TABLE llx_user DROP COLUMN skype;
  71. ALTER TABLE llx_user DROP COLUMN twitter;
  72. ALTER TABLE llx_user DROP COLUMN facebook;
  73. ALTER TABLE llx_user DROP COLUMN linkedin;
  74. ALTER TABLE llx_user DROP COLUMN instagram;
  75. ALTER TABLE llx_user DROP COLUMN snapchat;
  76. ALTER TABLE llx_user DROP COLUMN googleplus;
  77. ALTER TABLE llx_user DROP COLUMN youtube;
  78. ALTER TABLE llx_user DROP COLUMN whatsapp;
  79. ALTER TABLE llx_user ADD COLUMN datelastpassvalidation datetime;
  80. ALTER TABLE llx_user ADD COLUMN datestartvalidity datetime;
  81. ALTER TABLE llx_user ADD COLUMN dateendvalidity datetime;
  82. ALTER TABLE llx_user ADD COLUMN idpers1 varchar(128);
  83. ALTER TABLE llx_user ADD COLUMN idpers2 varchar(128);
  84. ALTER TABLE llx_user ADD COLUMN idpers3 varchar(128);
  85. -- Intracomm Report
  86. CREATE TABLE llx_c_transport_mode (
  87. rowid integer AUTO_INCREMENT PRIMARY KEY,
  88. entity integer DEFAULT 1 NOT NULL, -- multi company id
  89. code varchar(3) NOT NULL,
  90. label varchar(255) NOT NULL,
  91. active tinyint DEFAULT 1 NOT NULL
  92. ) ENGINE=innodb;
  93. ALTER TABLE llx_c_transport_mode ADD UNIQUE INDEX uk_c_transport_mode (code, entity);
  94. INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('MAR', 'Transport maritime (y compris camions ou wagons sur bateau)', 1);
  95. INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('TRA', 'Transport par chemin de fer (y compris camions sur wagon)', 1);
  96. INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('ROU', 'Transport par route', 1);
  97. INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('AIR', 'Transport par air', 1);
  98. INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('POS', 'Envois postaux', 1);
  99. INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('OLE', 'Installations de transport fixe (oléoduc)', 1);
  100. INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('NAV', 'Transport par navigation intérieure', 1);
  101. INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('PRO', 'Propulsion propre', 1);
  102. ALTER TABLE llx_facture ADD COLUMN fk_transport_mode integer after location_incoterms;
  103. ALTER TABLE llx_facture_fourn ADD COLUMN fk_transport_mode integer after location_incoterms;
  104. ALTER TABLE llx_societe ADD COLUMN transport_mode tinyint after cond_reglement;
  105. ALTER TABLE llx_societe ADD COLUMN transport_mode_supplier tinyint after cond_reglement_supplier;
  106. CREATE TABLE llx_intracommreport
  107. (
  108. rowid integer AUTO_INCREMENT PRIMARY KEY,
  109. ref varchar(30) NOT NULL, -- report reference number
  110. entity integer DEFAULT 1 NOT NULL, -- multi company id
  111. type_declaration varchar(32),
  112. periods varchar(32),
  113. mode varchar(32),
  114. content_xml text,
  115. type_export varchar(10),
  116. datec datetime,
  117. tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  118. )ENGINE=innodb;
  119. ALTER TABLE llx_c_incoterms ADD COLUMN label varchar(100) NULL;
  120. CREATE TABLE llx_recruitment_recruitmentjobposition(
  121. rowid integer AUTO_INCREMENT PRIMARY KEY NOT NULL,
  122. ref varchar(128) DEFAULT '(PROV)' NOT NULL,
  123. entity INTEGER DEFAULT 1 NOT NULL,
  124. label varchar(255) NOT NULL,
  125. qty integer DEFAULT 1 NOT NULL,
  126. fk_soc integer,
  127. fk_project integer,
  128. fk_user_recruiter integer,
  129. fk_user_supervisor integer,
  130. fk_establishment integer,
  131. date_planned date,
  132. remuneration_suggested varchar(255),
  133. description text,
  134. note_public text,
  135. note_private text,
  136. date_creation datetime NOT NULL,
  137. tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  138. fk_user_creat integer NOT NULL,
  139. fk_user_modif integer,
  140. last_main_doc varchar(255),
  141. import_key varchar(14),
  142. model_pdf varchar(255),
  143. status smallint NOT NULL
  144. ) ENGINE=innodb;
  145. ALTER TABLE llx_recruitment_recruitmentjobposition ADD INDEX idx_recruitment_recruitmentjobposition_rowid (rowid);
  146. ALTER TABLE llx_recruitment_recruitmentjobposition ADD INDEX idx_recruitment_recruitmentjobposition_ref (ref);
  147. ALTER TABLE llx_recruitment_recruitmentjobposition ADD INDEX idx_recruitment_recruitmentjobposition_fk_soc (fk_soc);
  148. ALTER TABLE llx_recruitment_recruitmentjobposition ADD INDEX idx_recruitment_recruitmentjobposition_fk_project (fk_project);
  149. ALTER TABLE llx_recruitment_recruitmentjobposition ADD CONSTRAINT llx_recruitment_recruitmentjobposition_fk_user_recruiter FOREIGN KEY (fk_user_recruiter) REFERENCES llx_user(rowid);
  150. ALTER TABLE llx_recruitment_recruitmentjobposition ADD CONSTRAINT llx_recruitment_recruitmentjobposition_fk_user_supervisor FOREIGN KEY (fk_user_supervisor) REFERENCES llx_user(rowid);
  151. ALTER TABLE llx_recruitment_recruitmentjobposition ADD CONSTRAINT llx_recruitment_recruitmentjobposition_fk_establishment FOREIGN KEY (fk_establishment) REFERENCES llx_establishment(rowid);
  152. ALTER TABLE llx_recruitment_recruitmentjobposition ADD CONSTRAINT llx_recruitment_recruitmentjobposition_fk_user_creat FOREIGN KEY (fk_user_creat) REFERENCES llx_user(rowid);
  153. ALTER TABLE llx_recruitment_recruitmentjobposition ADD INDEX idx_recruitment_recruitmentjobposition_status (status);
  154. ALTER TABLE llx_recruitment_recruitmentjobposition ADD COLUMN email_recruiter varchar(255);
  155. ALTER TABLE llx_recruitment_recruitmentjobposition ADD COLUMN entity INTEGER DEFAULT 1 NOT NULL;
  156. ALTER TABLE llx_recruitment_recruitmentjobposition ADD COLUMN remuneration_suggested varchar(255);
  157. create table llx_recruitment_recruitmentjobposition_extrafields
  158. (
  159. rowid integer AUTO_INCREMENT PRIMARY KEY,
  160. tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  161. fk_object integer NOT NULL,
  162. import_key varchar(14) -- import key
  163. ) ENGINE=innodb;
  164. ALTER TABLE llx_recruitment_recruitmentjobposition_extrafields ADD INDEX idx_recruitmentjobposition_fk_object(fk_object);
  165. CREATE TABLE llx_recruitment_recruitmentcandidature(
  166. -- BEGIN MODULEBUILDER FIELDS
  167. rowid integer AUTO_INCREMENT PRIMARY KEY NOT NULL,
  168. entity integer NOT NULL DEFAULT 1,
  169. fk_recruitmentjobposition INTEGER NULL,
  170. ref varchar(128) DEFAULT '(PROV)' NOT NULL,
  171. description text,
  172. note_public text,
  173. note_private text,
  174. date_creation datetime NOT NULL,
  175. tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  176. fk_user_creat integer NOT NULL,
  177. fk_user_modif integer,
  178. import_key varchar(14),
  179. model_pdf varchar(255),
  180. status smallint NOT NULL,
  181. firstname varchar(128),
  182. lastname varchar(128),
  183. email varchar(255),
  184. phone varchar(64),
  185. date_birth date,
  186. remuneration_requested integer,
  187. remuneration_proposed integer,
  188. email_msgid varchar(255),
  189. fk_recruitment_origin INTEGER NULL
  190. -- END MODULEBUILDER FIELDS
  191. ) ENGINE=innodb;
  192. ALTER TABLE llx_recruitment_recruitmentcandidature ADD COLUMN entity integer NOT NULL DEFAULT 1;
  193. ALTER TABLE llx_recruitment_recruitmentcandidature ADD COLUMN email_msgid varchar(255);
  194. ALTER TABLE llx_recruitment_recruitmentcandidature ADD COLUMN fk_recruitment_origin INTEGER NULL;
  195. ALTER TABLE llx_recruitment_recruitmentcandidature ADD COLUMN date_birth date;
  196. ALTER TABLE llx_recruitment_recruitmentcandidature ADD INDEX idx_recruitment_recruitmentcandidature_rowid (rowid);
  197. ALTER TABLE llx_recruitment_recruitmentcandidature ADD INDEX idx_recruitment_recruitmentcandidature_ref (ref);
  198. ALTER TABLE llx_recruitment_recruitmentcandidature ADD CONSTRAINT llx_recruitment_recruitmentcandidature_fk_user_creat FOREIGN KEY (fk_user_creat) REFERENCES llx_user(rowid);
  199. ALTER TABLE llx_recruitment_recruitmentcandidature ADD INDEX idx_recruitment_recruitmentcandidature_status (status);
  200. create table llx_recruitment_recruitmentcandidature_extrafields
  201. (
  202. rowid integer AUTO_INCREMENT PRIMARY KEY,
  203. tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  204. fk_object integer NOT NULL,
  205. import_key varchar(14) -- import key
  206. ) ENGINE=innodb;
  207. ALTER TABLE llx_recruitment_recruitmentcandidature_extrafields ADD INDEX idx_recruitmentcandidature_fk_object(fk_object);
  208. ALTER TABLE llx_recruitment_recruitmentcandidature ADD UNIQUE INDEX uk_recruitmentcandidature_email_msgid(email_msgid);
  209. ALTER TABLE llx_product_attribute ADD COLUMN ref_ext VARCHAR(255) after ref;
  210. ALTER TABLE llx_product_attribute_combination ADD COLUMN variation_ref_ext varchar(255) AFTER variation_weight;
  211. CREATE TABLE llx_product_attribute_combination_price_level
  212. (
  213. rowid INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
  214. fk_product_attribute_combination INTEGER DEFAULT 1 NOT NULL,
  215. fk_price_level INTEGER DEFAULT 1 NOT NULL,
  216. variation_price DOUBLE(24,8) NOT NULL,
  217. variation_price_percentage INTEGER NULL
  218. )ENGINE=innodb;
  219. ALTER TABLE llx_product_attribute_combination_price_level ADD UNIQUE( fk_product_attribute_combination, fk_price_level);
  220. -- Add dictionary for prospect level and action commercial on contacts (Using this is not recommanded)
  221. create table llx_c_prospectcontactlevel
  222. (
  223. code varchar(12) PRIMARY KEY,
  224. label varchar(30),
  225. sortorder smallint,
  226. active smallint DEFAULT 1 NOT NULL,
  227. module varchar(32) NULL
  228. ) ENGINE=innodb;
  229. insert into llx_c_prospectcontactlevel (code,label,sortorder) values ('PL_NONE', 'None', 1);
  230. insert into llx_c_prospectcontactlevel (code,label,sortorder) values ('PL_LOW', 'Low', 2);
  231. insert into llx_c_prospectcontactlevel (code,label,sortorder) values ('PL_MEDIUM', 'Medium', 3);
  232. insert into llx_c_prospectcontactlevel (code,label,sortorder) values ('PL_HIGH', 'High', 4);
  233. create table llx_c_stcommcontact
  234. (
  235. id integer PRIMARY KEY,
  236. code varchar(12) NOT NULL,
  237. libelle varchar(30),
  238. picto varchar(128),
  239. active tinyint default 1 NOT NULL
  240. )ENGINE=innodb;
  241. ALTER TABLE llx_c_stcommcontact ADD UNIQUE INDEX uk_c_stcommcontact(code);
  242. insert into llx_c_stcommcontact (id,code,libelle) values (-1, 'ST_NO', 'Do not contact');
  243. insert into llx_c_stcommcontact (id,code,libelle) values ( 0, 'ST_NEVER', 'Never contacted');
  244. insert into llx_c_stcommcontact (id,code,libelle) values ( 1, 'ST_TODO', 'To contact');
  245. insert into llx_c_stcommcontact (id,code,libelle) values ( 2, 'ST_PEND', 'Contact in progress');
  246. insert into llx_c_stcommcontact (id,code,libelle) values ( 3, 'ST_DONE', 'Contacted');
  247. ALTER TABLE llx_socpeople ADD COLUMN fk_prospectcontactlevel varchar(12) AFTER priv;
  248. ALTER TABLE llx_socpeople ADD COLUMN fk_stcommcontact integer DEFAULT 0 NOT NULL AFTER fk_prospectcontactlevel;
  249. create table llx_c_recruitment_origin
  250. (
  251. rowid integer AUTO_INCREMENT PRIMARY KEY,
  252. code varchar(32) NOT NULL,
  253. label varchar(64) NOT NULL,
  254. active tinyint DEFAULT 1 NOT NULL
  255. )ENGINE=innodb;
  256. ALTER TABLE llx_recruitment_recruitmentcandidature ADD UNIQUE INDEX uk_recruitmentcandidature_email_msgid(email_msgid);
  257. ALTER TABLE llx_product MODIFY COLUMN seuil_stock_alerte float;
  258. ALTER TABLE llx_product MODIFY COLUMN desiredstock float;
  259. ALTER TABLE llx_product_warehouse_properties MODIFY COLUMN seuil_stock_alerte float;
  260. ALTER TABLE llx_product_warehouse_properties MODIFY COLUMN desiredstock float;
  261. ALTER TABLE llx_product ADD COLUMN fk_state integer DEFAULT NULL AFTER fk_country;
  262. ALTER TABLE llx_projet ADD COLUMN email_msgid varchar(255);
  263. ALTER TABLE llx_ticket ADD COLUMN email_msgid varchar(255);
  264. ALTER TABLE llx_actioncomm ADD COLUMN reply_to varchar(255);
  265. ALTER TABLE llx_paiement ADD pos_change DOUBLE(24,8) DEFAULT 0 AFTER fk_export_compta;
  266. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('CONTACT_CREATE','Contact address created','Executed when a contact is created','contact',50);
  267. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('CONTACT_SENTBYMAIL','Mails sent from third party card','Executed when you send email from contact adress card','contact',51);
  268. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('CONTACT_DELETE','Contact address deleted','Executed when a contact is deleted','contact',52);
  269. ALTER TABLE llx_opensurvey_sondage CHANGE COLUMN tms tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
  270. ALTER TABLE llx_ecm_directories CHANGE COLUMN date_m tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
  271. ALTER TABLE llx_ecm_files CHANGE COLUMN date_m tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
  272. INSERT INTO llx_c_email_templates (entity,module,type_template,lang,private,fk_user,datec,label,position,enabled,active,topic,content,content_lines,joinfiles) VALUES (0,'recruitment','recruitmentcandidature_send','',0,null,null,'(AnswerCandidature)' ,100,'$conf->recruitment->enabled',1,'[__[MAIN_INFO_SOCIETE_NOM]__] __(YourCandidature)__', '__(Hello)__ __CANDIDATE_FULLNAME__,<br><br>\n\n__(YourCandidatureAnswer)__<br>\n<br><br>\n__(Sincerely)__<br>__USER_SIGNATURE__',null, 0);
  273. ALTER TABLE llx_c_action_trigger MODIFY COLUMN code varchar(64) NOT NULL;
  274. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('RECRUITMENTJOBPOSITION_CREATE','Job created','Executed when a job is created','recruitment',7500);
  275. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('RECRUITMENTJOBPOSITION_MODIFY','Job modified','Executed when a job is modified','recruitment',7502);
  276. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('RECRUITMENTJOBPOSITION_SENTBYMAIL','Mails sent from job record','Executed when you send email from job record','recruitment',7504);
  277. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('RECRUITMENTJOBPOSITION_DELETE','Job deleted','Executed when a job is deleted','recruitment',7506);
  278. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('RECRUITMENTCANDIDATURE_CREATE','Candidature created','Executed when a candidature is created','recruitment',7510);
  279. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('RECRUITMENTCANDIDATURE_MODIFY','Candidature modified','Executed when a candidature is modified','recruitment',7512);
  280. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('RECRUITMENTCANDIDATURE_SENTBYMAIL','Mails sent from candidature record','Executed when you send email from candidature record','recruitment',7514);
  281. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('RECRUITMENTCANDIDATURE_DELETE','Candidature deleted','Executed when a candidature is deleted','recruitment',7516);
  282. ALTER TABLE llx_actioncomm_reminder ADD COLUMN entity integer NOT NULL DEFAULT 1;
  283. ALTER TABLE llx_actioncomm_reminder ADD COLUMN fk_actioncomm integer NOT NULL;
  284. ALTER TABLE llx_actioncomm_reminder ADD COLUMN fk_email_template integer;
  285. ALTER TABLE llx_actioncomm_reminder ADD COLUMN lasterror varchar(128) NULL;
  286. ALTER TABLE llx_actioncomm_reminder DROP INDEX uk_actioncomm_reminder_unique;
  287. ALTER TABLE llx_actioncomm_reminder ADD UNIQUE uk_actioncomm_reminder_unique (fk_user, typeremind, offsetvalue, offsetunit, fk_actioncomm);
  288. ALTER TABLE llx_actioncomm_reminder ADD INDEX idx_actioncomm_reminder_status (status);
  289. ALTER TABLE llx_inventorydet ADD UNIQUE uk_inventorydet(fk_inventory, fk_warehouse, fk_product, batch);
  290. ALTER TABLE llx_commandedet ADD COLUMN ref_ext varchar(255) AFTER label;
  291. ALTER TABLE llx_facturedet ADD COLUMN ref_ext varchar(255) AFTER multicurrency_total_ttc;
  292. ALTER TABLE llx_c_ticket_category ADD COLUMN fk_parent integer DEFAULT 0 NOT NULL;
  293. ALTER TABLE llx_c_ticket_category ADD COLUMN force_severity varchar(32) NULL;
  294. ALTER TABLE llx_c_ticket_severity CHANGE color color VARCHAR(10) NULL;
  295. ALTER TABLE llx_expensereport ADD COLUMN fk_user_creat integer NULL;
  296. ALTER TABLE llx_expensereport_ik ADD COLUMN ikoffset double DEFAULT 0 NOT NULL;
  297. ALTER TABLE llx_paiement ADD COLUMN ref_ext varchar(255) AFTER ref;
  298. ALTER TABLE llx_bank ADD COLUMN origin_id integer;
  299. ALTER TABLE llx_bank ADD COLUMN origin_type varchar(64) NULL;
  300. ALTER TABLE llx_bank ADD COLUMN import_key varchar(14);
  301. ALTER TABLE llx_menu MODIFY COLUMN enabled text;
  302. CREATE TABLE llx_ecm_files_extrafields
  303. (
  304. rowid integer AUTO_INCREMENT PRIMARY KEY,
  305. tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  306. fk_object integer NOT NULL,
  307. import_key varchar(14) -- import key
  308. ) ENGINE=innodb;
  309. ALTER TABLE llx_ecm_files_extrafields ADD INDEX idx_ecm_files_extrafields (fk_object);
  310. ALTER TABLE llx_ecm_files ADD COLUMN note_private text AFTER fk_user_m;
  311. ALTER TABLE llx_ecm_files ADD COLUMN note_public text AFTER note_private;
  312. CREATE TABLE llx_ecm_directories_extrafields
  313. (
  314. rowid integer AUTO_INCREMENT PRIMARY KEY,
  315. tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  316. fk_object integer NOT NULL,
  317. import_key varchar(14) -- import key
  318. ) ENGINE=innodb;
  319. ALTER TABLE llx_ecm_directories_extrafields ADD INDEX idx_ecm_directories_extrafields (fk_object);
  320. ALTER TABLE llx_ecm_directories ADD COLUMN note_private text AFTER fk_user_m;
  321. ALTER TABLE llx_ecm_directories ADD COLUMN note_public text AFTER note_private;
  322. ALTER TABLE llx_website_page ADD COLUMN allowed_in_frames integer DEFAULT 0;
  323. ALTER TABLE llx_website_page ADD COLUMN object_type varchar(255);
  324. ALTER TABLE llx_website_page ADD COLUMN fk_object varchar(255);
  325. DELETE FROM llx_const WHERE name in ('MAIN_INCLUDE_ZERO_VAT_IN_REPORTS');
  326. -- VMYSQL4.1 UPDATE llx_projet_task_time SET tms = null WHERE tms = 0;
  327. ALTER TABLE llx_projet_task_time CHANGE COLUMN tms tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
  328. ALTER TABLE llx_projet_task_time MODIFY COLUMN datec datetime;
  329. DELETE FROM llx_user_rights WHERE fk_id IN (SELECT id FROM llx_rights_def where module = 'holiday' and perms = 'lire_tous');
  330. DELETE FROM llx_rights_def where module = 'holiday' and perms = 'lire_tous';
  331. UPDATE llx_rights_def set perms = 'readall' WHERE perms = 'read_all' and module = 'holiday';
  332. CREATE TABLE llx_c_product_nature (
  333. rowid integer AUTO_INCREMENT PRIMARY KEY,
  334. code tinyint NOT NULL,
  335. label varchar(100),
  336. active tinyint DEFAULT 1 NOT NULL
  337. ) ENGINE=innodb;
  338. ALTER TABLE llx_product DROP FOREIGN KEY fk_product_finished;
  339. -- VMYSQL4.1 DROP INDEX uk_c_product_nature on llx_c_product_nature;
  340. -- VPGSQL8.2 DROP INDEX uk_c_product_nature;
  341. ALTER TABLE llx_c_product_nature ADD UNIQUE INDEX uk_c_product_nature(code);
  342. INSERT INTO llx_c_product_nature (code, label, active) VALUES (0, 'RowMaterial', 1);
  343. INSERT INTO llx_c_product_nature (code, label, active) VALUES (1, 'Finished', 1);
  344. ALTER TABLE llx_product MODIFY COLUMN finished tinyint DEFAULT NULL;
  345. ALTER TABLE llx_product ADD CONSTRAINT fk_product_finished FOREIGN KEY (finished) REFERENCES llx_c_product_nature (code);
  346. -- MIGRATION TO DO AFTER RENAMING AN OBJECT
  347. -- drop constraint
  348. ALTER TABLE llx_livraison DROP FOREIGN KEY fk_livraison_fk_soc;
  349. ALTER TABLE llx_livraison DROP FOREIGN KEY fk_livraison_fk_user_author;
  350. ALTER TABLE llx_livraison DROP FOREIGN KEY fk_livraison_fk_user_valid;
  351. -- rename Table
  352. ALTER TABLE llx_livraison RENAME TO llx_delivery;
  353. ALTER TABLE llx_livraison_extrafields RENAME TO llx_delivery_extrafields;
  354. ALTER TABLE llx_livraisondet RENAME TO llx_deliverydet;
  355. ALTER TABLE llx_livraisondet_extrafields RENAME TO llx_deliverydet_extrafields;
  356. -- rename index
  357. ALTER TABLE llx_delivery DROP INDEX idx_livraison_uk_ref;
  358. ALTER TABLE llx_delivery ADD UNIQUE INDEX idx_delivery_uk_ref (ref, entity);
  359. ALTER TABLE llx_delivery DROP INDEX idx_livraison_fk_soc;
  360. ALTER TABLE llx_delivery ADD INDEX idx_delivery_fk_soc (fk_soc);
  361. ALTER TABLE llx_delivery DROP INDEX idx_livraison_fk_user_author;
  362. ALTER TABLE llx_delivery ADD INDEX idx_delivery_fk_user_author (fk_user_author);
  363. ALTER TABLE llx_delivery DROP INDEX idx_livraison_fk_user_valid;
  364. ALTER TABLE llx_delivery ADD INDEX idx_delivery_fk_user_valid (fk_user_valid);
  365. -- drop constraint
  366. ALTER TABLE llx_delivery DROP FOREIGN KEY fk_livraison_fk_soc;
  367. ALTER TABLE llx_delivery DROP FOREIGN KEY fk_livraison_fk_user_author;
  368. ALTER TABLE llx_delivery DROP FOREIGN KEY fk_livraison_fk_user_valid;
  369. -- add constraint
  370. ALTER TABLE llx_delivery ADD CONSTRAINT fk_delivery_fk_soc FOREIGN KEY (fk_soc) REFERENCES llx_societe (rowid);
  371. ALTER TABLE llx_delivery ADD CONSTRAINT fk_delivery_fk_user_author FOREIGN KEY (fk_user_author) REFERENCES llx_user (rowid);
  372. ALTER TABLE llx_delivery ADD CONSTRAINT fk_delivery_fk_user_valid FOREIGN KEY (fk_user_valid) REFERENCES llx_user (rowid);
  373. ALTER TABLE llx_deliverydet DROP FOREIGN KEY fk_livraisondet_fk_livraison;
  374. ALTER TABLE llx_deliverydet DROP INDEX idx_livraisondet_fk_expedition;
  375. ALTER TABLE llx_deliverydet CHANGE COLUMN fk_livraison fk_delivery integer;
  376. ALTER TABLE llx_deliverydet ADD INDEX idx_deliverydet_fk_delivery (fk_delivery);
  377. ALTER TABLE llx_deliverydet ADD CONSTRAINT fk_deliverydet_fk_delivery FOREIGN KEY (fk_delivery) REFERENCES llx_delivery (rowid);
  378. UPDATE llx_extrafields SET elementtype = 'delivery' WHERE elementtype = 'livraison';
  379. UPDATE llx_extrafields SET elementtype = 'deliverydet' WHERE elementtype = 'livraisondet';
  380. -- update llx_ecm_files
  381. UPDATE llx_ecm_files SET src_object_type = 'delivery' WHERE src_object_type = 'livraison';
  382. -- update llx_links
  383. UPDATE llx_links SET objecttype = 'delivery' WHERE objecttype = 'livraison';
  384. -- update llx_document_model
  385. UPDATE llx_document_model SET type = 'delivery' WHERE type = 'livraison';
  386. -- update llx_object_lang
  387. UPDATE llx_object_lang SET type_object = 'delivery' WHERE type_object = 'livraison';
  388. -- update llx_c_type_contact
  389. UPDATE llx_c_type_contact SET element = 'delivery' WHERE element = 'livraison';
  390. -- update llx_c_email_template
  391. UPDATE llx_c_email_template SET type_template = 'delivery' WHERE type_template = 'livraison';
  392. -- update llx_element_element
  393. UPDATE llx_element_element SET sourcetype = 'delivery' WHERE sourcetype = 'livraison';
  394. UPDATE llx_element_element SET targettype = 'delivery' WHERE targettype = 'livraison';
  395. -- update llx_actioncomm
  396. UPDATE llx_actioncomm SET element_type = 'delivery' WHERE element_type = 'livraison';
  397. -- update llx_const
  398. UPDATE llx_const set name = 'DELIVERY_ADDON_NUMBER' WHERE name = 'LIVRAISON_ADDON_NUMBER';
  399. UPDATE llx_const set value = 'mod_delivery_jade' WHERE value = 'mod_livraison_jade' AND name = 'DELIVERY_ADDON_NUMBER';
  400. UPDATE llx_const set value = 'mod_delivery_saphir' WHERE value = 'mod_livraison_saphir' AND name = 'DELIVERY_ADDON_NUMBER';
  401. -- update llx_rights_def
  402. UPDATE llx_rights_def set perms = 'delivery' WHERE perms = 'livraison' and module = 'expedition';
  403. UPDATE llx_rights_def set perms = 'delivery_advance' WHERE perms = 'livraison_advance' and module = 'expedition';
  404. ALTER TABLE llx_commande_fournisseurdet ADD INDEX idx_commande_fournisseurdet_fk_commande (fk_commande);
  405. ALTER TABLE llx_commande_fournisseurdet ADD INDEX idx_commande_fournisseurdet_fk_product (fk_product);
  406. CREATE TABLE llx_zapier_hook(
  407. rowid integer AUTO_INCREMENT PRIMARY KEY,
  408. entity integer DEFAULT 1 NOT NULL,
  409. url varchar(255),
  410. event varchar(255),
  411. module varchar(128),
  412. action varchar(128),
  413. status integer,
  414. date_creation datetime NOT NULL,
  415. fk_user integer NOT NULL,
  416. tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  417. import_key varchar(14)
  418. ) ENGINE=innodb;
  419. CREATE TABLE llx_session(
  420. session_id varchar(50) PRIMARY KEY,
  421. session_variable text,
  422. last_accessed datetime NOT NULL,
  423. fk_user integer NOT NULL,
  424. remote_ip varchar(64) NULL,
  425. user_agent varchar(128) NULL
  426. ) ENGINE=innodb;
  427. INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'github', 'Github', 'https://github.com/{socialid}', 'fa-github', 1);
  428. -- VMYSQL4.1 INSERT INTO llx_boxes_def (file, entity) SELECT 'box_funnel_of_prospection.php', 1 FROM DUAL WHERE NOT EXISTS (SELECT * FROM llx_boxes_def WHERE file = 'box_funnel_of_prospection.php' AND entity = 1);
  429. -- VMYSQL4.1 INSERT INTO llx_boxes_def (file, entity) SELECT 'box_customers_outstanding_bill_reached.php', 1 FROM DUAL WHERE NOT EXISTS (SELECT * FROM llx_boxes_def WHERE file = 'box_customers_outstanding_bill_reached.php' AND entity = 1);
  430. -- VMYSQL4.1 INSERT INTO llx_boxes_def (file, entity) SELECT 'box_scheduled_jobs.php', 1 FROM DUAL WHERE NOT EXISTS (SELECT * FROM llx_boxes_def WHERE file = 'box_scheduled_jobs.php' AND entity = 1);
  431. ALTER TABLE llx_product_fournisseur_price ADD COLUMN packaging varchar(64);
  432. ALTER TABLE llx_projet ADD COLUMN fk_opp_status_end integer DEFAULT NULL;
  433. UPDATE llx_c_action_trigger SET elementtype = 'expensereport' where elementtype = 'expense_report' AND code like 'EXPENSE_%';
  434. UPDATE llx_c_action_trigger SET code = 'EXPENSE_REPORT_PAID' where code = 'EXPENSE_REPORT_PAYED';
  435. UPDATE llx_c_action_trigger SET code = 'EXPENSE_REPORT_DELETE' where code = 'EXPENSE_DELETE';
  436. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('EXPENSE_REPORT_CREATE','Expense report created','Executed when an expense report is created','expensereport',201);
  437. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('EXPENSE_REPORT_VALIDATE','Expense report validated','Executed when an expense report is validated','expensereport',202);
  438. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('EXPENSE_REPORT_APPROVE','Expense report approved','Executed when an expense report is approved','expensereport',203);
  439. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('EXPENSE_REPORT_PAID','Expense report billed','Executed when an expense report is set as billed','expensereport',204);
  440. insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('EXPENSE_REPORT_DELETE','Expense report deleted','Executed when an expense report is deleted','expensereport',205);
  441. -- Removed no more used function
  442. -- VPGSQL8.2 DROP FUNCTION IF EXISTS update_modified_column_date_m() CASCADE;
  443. -- VPGSQL8.2 DROP TRIGGER update_customer_modtime ON llx_ecm_directories;
  444. -- VPGSQL8.2 DROP TRIGGER update_customer_modtime ON llx_ecm_files;
  445. -- VPGSQL8.2 CREATE TRIGGER update_customer_modtime BEFORE UPDATE ON llx_ecm_directories FOR EACH ROW EXECUTE PROCEDURE update_modified_column_tms();
  446. -- VPGSQL8.2 CREATE TRIGGER update_customer_modtime BEFORE UPDATE ON llx_ecm_files FOR EACH ROW EXECUTE PROCEDURE update_modified_column_tms();
  447. insert into llx_c_actioncomm (id, code, type, libelle, module, active, position) values ( 6,'AC_EMAIL_IN','system','reception Email',NULL, 1, 4);
  448. -- VMYSQL4.3 ALTER TABLE llx_accounting_bookkeeping MODIFY COLUMN montant double(24,8) NULL;
  449. -- VPGSQL8.2 ALTER TABLE llx_accounting_bookkeeping ALTER COLUMN montant DROP NOT NULL;
  450. ALTER TABLE llx_export_model MODIFY COLUMN type varchar(64);