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 30 31-- Missing in v12 or lower 32 33ALTER TABLE llx_payment_salary MODIFY COLUMN ref varchar(30) NULL; 34ALTER TABLE llx_payment_various MODIFY COLUMN ref varchar(30) NULL; 35 36ALTER TABLE llx_prelevement_bons ADD COLUMN type varchar(16) DEFAULT 'debit-order'; 37 38ALTER TABLE llx_prelevement_facture CHANGE COLUMN fk_facture_foun fk_facture_fourn integer NULL; 39 40ALTER TABLE llx_prelevement_facture_demande ADD COLUMN fk_facture_fourn INTEGER NULL; 41ALTER TABLE llx_prelevement_facture_demande ADD INDEX idx_prelevement_facture_demande_fk_facture (fk_facture); 42ALTER TABLE llx_prelevement_facture_demande ADD INDEX idx_prelevement_facture_demande_fk_facture_fourn (fk_facture_fourn); 43 44ALTER TABLE llx_document_model MODIFY COLUMN type varchar(64); 45 46ALTER TABLE llx_bom_bom MODIFY COLUMN duration double(24,8); 47 48ALTER TABLE llx_bom_bom_extrafields ADD INDEX idx_bom_bom_extrafields_fk_object (fk_object); 49 50create table llx_mrp_mo_extrafields 51( 52 rowid integer AUTO_INCREMENT PRIMARY KEY, 53 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 54 fk_object integer NOT NULL, 55 import_key varchar(14) -- import key 56) ENGINE=innodb; 57 58ALTER TABLE llx_mrp_mo_extrafields DROP INDEX idx_fk_object; 59 60ALTER TABLE llx_mrp_mo_extrafields ADD INDEX idx_mrp_mo_fk_object(fk_object); 61 62 63-- For v13 64insert into llx_c_tva(rowid,fk_pays,taux,recuperableonly,note,active) values (111,11, '0','0','No Sales Tax',1); 65insert into llx_c_tva(rowid,fk_pays,taux,recuperableonly,note,active) values (112,11, '4','0','Sales Tax 4%',1); 66insert into llx_c_tva(rowid,fk_pays,taux,recuperableonly,note,active) values (113,11, '6','0','Sales Tax 6%',1); 67 68ALTER TABLE llx_bom_bom ADD COLUMN bomtype integer DEFAULT 0; 69 70UPDATE llx_emailcollector_emailcollector SET ref = 'Collect_Ticket_Requests' WHERE ref = 'Collect_Ticket_Requets'; 71UPDATE llx_emailcollector_emailcollector SET ref = 'Collect_Responses_In' WHERE ref = 'Collect_Responses'; 72 73 74UPDATE llx_document_model set nom = 'standard' where nom = 'Standard' and type ='stock'; 75UPDATE llx_document_model set nom = 'stdmovement', type = 'movement' where nom = 'StdMouvement' and type ='mouvement'; 76 77 78UPDATE llx_const SET value = 0 WHERE name = 'FACTURE_TVAOPTION' and value = 'franchise'; 79UPDATE llx_const SET value = 1 WHERE name = 'FACTURE_TVAOPTION' and value <> 'franchise' AND value <> '0' AND value <> '1'; 80 81ALTER TABLE llx_commande MODIFY COLUMN date_livraison DATETIME; 82 83ALTER TABLE llx_website ADD COLUMN position integer DEFAULT 0; 84 85ALTER TABLE llx_establishment ADD COLUMN ref varchar(30); 86ALTER TABLE llx_establishment ADD COLUMN label varchar(128); 87UPDATE llx_establishment SET ref = rowid WHERE ref IS NULL; 88ALTER TABLE llx_establishment MODIFY COLUMN ref varchar(30) NOT NULL; 89ALTER TABLE llx_establishment MODIFY COLUMN label varchar(128); 90 91INSERT INTO llx_const (name, entity, value, type, visible) VALUES ('PRODUCT_PRICE_BASE_TYPE', 0, 'HT', 'string', 0); 92 93ALTER TABLE llx_subscription MODIFY COLUMN datef DATETIME; 94 95ALTER TABLE llx_loan_schedule ADD column fk_payment_loan INTEGER; 96 97 98ALTER TABLE llx_user DROP COLUMN jabberid; 99ALTER TABLE llx_user DROP COLUMN skype; 100ALTER TABLE llx_user DROP COLUMN twitter; 101ALTER TABLE llx_user DROP COLUMN facebook; 102ALTER TABLE llx_user DROP COLUMN linkedin; 103ALTER TABLE llx_user DROP COLUMN instagram; 104ALTER TABLE llx_user DROP COLUMN snapchat; 105ALTER TABLE llx_user DROP COLUMN googleplus; 106ALTER TABLE llx_user DROP COLUMN youtube; 107ALTER TABLE llx_user DROP COLUMN whatsapp; 108 109ALTER TABLE llx_user ADD COLUMN datelastpassvalidation datetime; 110ALTER TABLE llx_user ADD COLUMN datestartvalidity datetime; 111ALTER TABLE llx_user ADD COLUMN dateendvalidity datetime; 112 113ALTER TABLE llx_user ADD COLUMN idpers1 varchar(128); 114ALTER TABLE llx_user ADD COLUMN idpers2 varchar(128); 115ALTER TABLE llx_user ADD COLUMN idpers3 varchar(128); 116 117 118-- Intracomm Report 119CREATE TABLE llx_c_transport_mode ( 120 rowid integer AUTO_INCREMENT PRIMARY KEY, 121 entity integer DEFAULT 1 NOT NULL, -- multi company id 122 code varchar(3) NOT NULL, 123 label varchar(255) NOT NULL, 124 active tinyint DEFAULT 1 NOT NULL 125) ENGINE=innodb; 126ALTER TABLE llx_c_transport_mode ADD UNIQUE INDEX uk_c_transport_mode (code, entity); 127 128INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('MAR', 'Transport maritime (y compris camions ou wagons sur bateau)', 1); 129INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('TRA', 'Transport par chemin de fer (y compris camions sur wagon)', 1); 130INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('ROU', 'Transport par route', 1); 131INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('AIR', 'Transport par air', 1); 132INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('POS', 'Envois postaux', 1); 133INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('OLE', 'Installations de transport fixe (oléoduc)', 1); 134INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('NAV', 'Transport par navigation intérieure', 1); 135INSERT INTO llx_c_transport_mode (code, label, active) VALUES ('PRO', 'Propulsion propre', 1); 136 137ALTER TABLE llx_facture ADD COLUMN fk_transport_mode integer after location_incoterms; 138ALTER TABLE llx_facture_fourn ADD COLUMN fk_transport_mode integer after location_incoterms; 139 140ALTER TABLE llx_societe ADD COLUMN transport_mode tinyint after cond_reglement; 141ALTER TABLE llx_societe ADD COLUMN transport_mode_supplier tinyint after cond_reglement_supplier; 142 143CREATE TABLE llx_intracommreport 144( 145 rowid integer AUTO_INCREMENT PRIMARY KEY, 146 147 ref varchar(30) NOT NULL, -- report reference number 148 entity integer DEFAULT 1 NOT NULL, -- multi company id 149 type_declaration varchar(32), 150 periods varchar(32), 151 mode varchar(32), 152 content_xml text, 153 type_export varchar(10), 154 datec datetime, 155 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 156)ENGINE=innodb; 157 158ALTER TABLE llx_c_incoterms ADD COLUMN label varchar(100) NULL; 159 160CREATE TABLE llx_recruitment_recruitmentjobposition( 161 rowid integer AUTO_INCREMENT PRIMARY KEY NOT NULL, 162 ref varchar(128) DEFAULT '(PROV)' NOT NULL, 163 entity INTEGER DEFAULT 1 NOT NULL, 164 label varchar(255) NOT NULL, 165 qty integer DEFAULT 1 NOT NULL, 166 fk_soc integer, 167 fk_project integer, 168 fk_user_recruiter integer, 169 fk_user_supervisor integer, 170 fk_establishment integer, 171 date_planned date, 172 remuneration_suggested varchar(255), 173 description text, 174 note_public text, 175 note_private text, 176 date_creation datetime NOT NULL, 177 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 178 fk_user_creat integer NOT NULL, 179 fk_user_modif integer, 180 last_main_doc varchar(255), 181 import_key varchar(14), 182 model_pdf varchar(255), 183 status smallint NOT NULL 184) ENGINE=innodb; 185 186ALTER TABLE llx_recruitment_recruitmentjobposition ADD INDEX idx_recruitment_recruitmentjobposition_rowid (rowid); 187ALTER TABLE llx_recruitment_recruitmentjobposition ADD INDEX idx_recruitment_recruitmentjobposition_ref (ref); 188ALTER TABLE llx_recruitment_recruitmentjobposition ADD INDEX idx_recruitment_recruitmentjobposition_fk_soc (fk_soc); 189ALTER TABLE llx_recruitment_recruitmentjobposition ADD INDEX idx_recruitment_recruitmentjobposition_fk_project (fk_project); 190ALTER TABLE llx_recruitment_recruitmentjobposition ADD CONSTRAINT llx_recruitment_recruitmentjobposition_fk_user_recruiter FOREIGN KEY (fk_user_recruiter) REFERENCES llx_user(rowid); 191ALTER TABLE llx_recruitment_recruitmentjobposition ADD CONSTRAINT llx_recruitment_recruitmentjobposition_fk_user_supervisor FOREIGN KEY (fk_user_supervisor) REFERENCES llx_user(rowid); 192ALTER TABLE llx_recruitment_recruitmentjobposition ADD CONSTRAINT llx_recruitment_recruitmentjobposition_fk_establishment FOREIGN KEY (fk_establishment) REFERENCES llx_establishment(rowid); 193ALTER TABLE llx_recruitment_recruitmentjobposition ADD CONSTRAINT llx_recruitment_recruitmentjobposition_fk_user_creat FOREIGN KEY (fk_user_creat) REFERENCES llx_user(rowid); 194ALTER TABLE llx_recruitment_recruitmentjobposition ADD INDEX idx_recruitment_recruitmentjobposition_status (status); 195 196ALTER TABLE llx_recruitment_recruitmentjobposition ADD COLUMN email_recruiter varchar(255); 197ALTER TABLE llx_recruitment_recruitmentjobposition ADD COLUMN entity INTEGER DEFAULT 1 NOT NULL; 198ALTER TABLE llx_recruitment_recruitmentjobposition ADD COLUMN remuneration_suggested varchar(255); 199 200create table llx_recruitment_recruitmentjobposition_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 208ALTER TABLE llx_recruitment_recruitmentjobposition_extrafields ADD INDEX idx_recruitmentjobposition_fk_object(fk_object); 209 210 211 212CREATE TABLE llx_recruitment_recruitmentcandidature( 213 -- BEGIN MODULEBUILDER FIELDS 214 rowid integer AUTO_INCREMENT PRIMARY KEY NOT NULL, 215 entity integer NOT NULL DEFAULT 1, 216 fk_recruitmentjobposition INTEGER NULL, 217 ref varchar(128) DEFAULT '(PROV)' NOT NULL, 218 description text, 219 note_public text, 220 note_private text, 221 date_creation datetime NOT NULL, 222 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 223 fk_user_creat integer NOT NULL, 224 fk_user_modif integer, 225 import_key varchar(14), 226 model_pdf varchar(255), 227 status smallint NOT NULL, 228 firstname varchar(128), 229 lastname varchar(128), 230 email varchar(255), 231 phone varchar(64), 232 date_birth date, 233 remuneration_requested integer, 234 remuneration_proposed integer, 235 email_msgid varchar(255), 236 fk_recruitment_origin INTEGER NULL 237 -- END MODULEBUILDER FIELDS 238) ENGINE=innodb; 239 240ALTER TABLE llx_recruitment_recruitmentcandidature ADD COLUMN entity integer NOT NULL DEFAULT 1; 241ALTER TABLE llx_recruitment_recruitmentcandidature ADD COLUMN email_msgid varchar(255); 242ALTER TABLE llx_recruitment_recruitmentcandidature ADD COLUMN fk_recruitment_origin INTEGER NULL; 243ALTER TABLE llx_recruitment_recruitmentcandidature ADD COLUMN date_birth date; 244 245ALTER TABLE llx_recruitment_recruitmentcandidature ADD INDEX idx_recruitment_recruitmentcandidature_rowid (rowid); 246ALTER TABLE llx_recruitment_recruitmentcandidature ADD INDEX idx_recruitment_recruitmentcandidature_ref (ref); 247ALTER TABLE llx_recruitment_recruitmentcandidature ADD CONSTRAINT llx_recruitment_recruitmentcandidature_fk_user_creat FOREIGN KEY (fk_user_creat) REFERENCES llx_user(rowid); 248ALTER TABLE llx_recruitment_recruitmentcandidature ADD INDEX idx_recruitment_recruitmentcandidature_status (status); 249 250create table llx_recruitment_recruitmentcandidature_extrafields 251( 252 rowid integer AUTO_INCREMENT PRIMARY KEY, 253 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 254 fk_object integer NOT NULL, 255 import_key varchar(14) -- import key 256) ENGINE=innodb; 257 258ALTER TABLE llx_recruitment_recruitmentcandidature_extrafields ADD INDEX idx_recruitmentcandidature_fk_object(fk_object); 259 260ALTER TABLE llx_recruitment_recruitmentcandidature ADD UNIQUE INDEX uk_recruitmentcandidature_email_msgid(email_msgid); 261 262 263ALTER TABLE llx_product_attribute ADD COLUMN ref_ext VARCHAR(255) after ref; 264ALTER TABLE llx_product_attribute_combination ADD COLUMN variation_ref_ext varchar(255) AFTER variation_weight; 265 266 267CREATE TABLE llx_product_attribute_combination_price_level 268( 269 rowid INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, 270 fk_product_attribute_combination INTEGER DEFAULT 1 NOT NULL, 271 fk_price_level INTEGER DEFAULT 1 NOT NULL, 272 variation_price DOUBLE(24,8) NOT NULL, 273 variation_price_percentage INTEGER NULL 274)ENGINE=innodb; 275 276ALTER TABLE llx_product_attribute_combination_price_level ADD UNIQUE( fk_product_attribute_combination, fk_price_level); 277 278 279 280-- Add dictionary for prospect level and action commercial on contacts (Using this is not recommanded) 281 282create table llx_c_prospectcontactlevel 283( 284 code varchar(12) PRIMARY KEY, 285 label varchar(30), 286 sortorder smallint, 287 active smallint DEFAULT 1 NOT NULL, 288 module varchar(32) NULL 289) ENGINE=innodb; 290insert into llx_c_prospectcontactlevel (code,label,sortorder) values ('PL_NONE', 'None', 1); 291insert into llx_c_prospectcontactlevel (code,label,sortorder) values ('PL_LOW', 'Low', 2); 292insert into llx_c_prospectcontactlevel (code,label,sortorder) values ('PL_MEDIUM', 'Medium', 3); 293insert into llx_c_prospectcontactlevel (code,label,sortorder) values ('PL_HIGH', 'High', 4); 294 295create table llx_c_stcommcontact 296( 297 id integer PRIMARY KEY, 298 code varchar(12) NOT NULL, 299 libelle varchar(30), 300 picto varchar(128), 301 active tinyint default 1 NOT NULL 302)ENGINE=innodb; 303ALTER TABLE llx_c_stcommcontact ADD UNIQUE INDEX uk_c_stcommcontact(code); 304 305insert into llx_c_stcommcontact (id,code,libelle) values (-1, 'ST_NO', 'Do not contact'); 306insert into llx_c_stcommcontact (id,code,libelle) values ( 0, 'ST_NEVER', 'Never contacted'); 307insert into llx_c_stcommcontact (id,code,libelle) values ( 1, 'ST_TODO', 'To contact'); 308insert into llx_c_stcommcontact (id,code,libelle) values ( 2, 'ST_PEND', 'Contact in progress'); 309insert into llx_c_stcommcontact (id,code,libelle) values ( 3, 'ST_DONE', 'Contacted'); 310 311ALTER TABLE llx_socpeople ADD COLUMN fk_prospectcontactlevel varchar(12) AFTER priv; 312ALTER TABLE llx_socpeople ADD COLUMN fk_stcommcontact integer DEFAULT 0 NOT NULL AFTER fk_prospectcontactlevel; 313 314create table llx_c_recruitment_origin 315( 316 rowid integer AUTO_INCREMENT PRIMARY KEY, 317 code varchar(32) NOT NULL, 318 label varchar(64) NOT NULL, 319 active tinyint DEFAULT 1 NOT NULL 320)ENGINE=innodb; 321 322 323ALTER TABLE llx_recruitment_recruitmentcandidature ADD UNIQUE INDEX uk_recruitmentcandidature_email_msgid(email_msgid); 324 325 326ALTER TABLE llx_product MODIFY COLUMN seuil_stock_alerte float; 327ALTER TABLE llx_product MODIFY COLUMN desiredstock float; 328 329ALTER TABLE llx_product_warehouse_properties MODIFY COLUMN seuil_stock_alerte float; 330ALTER TABLE llx_product_warehouse_properties MODIFY COLUMN desiredstock float; 331 332ALTER TABLE llx_product ADD COLUMN fk_state integer DEFAULT NULL AFTER fk_country; 333 334ALTER TABLE llx_projet ADD COLUMN email_msgid varchar(255); 335ALTER TABLE llx_ticket ADD COLUMN email_msgid varchar(255); 336ALTER TABLE llx_actioncomm ADD COLUMN reply_to varchar(255); 337 338ALTER TABLE llx_paiement ADD pos_change DOUBLE(24,8) DEFAULT 0 AFTER fk_export_compta; 339 340insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('CONTACT_CREATE','Contact address created','Executed when a contact is created','contact',50); 341insert 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); 342insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('CONTACT_DELETE','Contact address deleted','Executed when a contact is deleted','contact',52); 343 344ALTER TABLE llx_opensurvey_sondage CHANGE COLUMN tms tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 345ALTER TABLE llx_ecm_directories CHANGE COLUMN date_m tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 346ALTER TABLE llx_ecm_files CHANGE COLUMN date_m tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 347 348INSERT 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); 349 350ALTER TABLE llx_c_action_trigger MODIFY COLUMN code varchar(64) NOT NULL; 351 352insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('RECRUITMENTJOBPOSITION_CREATE','Job created','Executed when a job is created','recruitment',7500); 353insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('RECRUITMENTJOBPOSITION_MODIFY','Job modified','Executed when a job is modified','recruitment',7502); 354insert 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); 355insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('RECRUITMENTJOBPOSITION_DELETE','Job deleted','Executed when a job is deleted','recruitment',7506); 356insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('RECRUITMENTCANDIDATURE_CREATE','Candidature created','Executed when a candidature is created','recruitment',7510); 357insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('RECRUITMENTCANDIDATURE_MODIFY','Candidature modified','Executed when a candidature is modified','recruitment',7512); 358insert 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); 359insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('RECRUITMENTCANDIDATURE_DELETE','Candidature deleted','Executed when a candidature is deleted','recruitment',7516); 360 361ALTER TABLE llx_actioncomm_reminder ADD COLUMN entity integer NOT NULL DEFAULT 1; 362ALTER TABLE llx_actioncomm_reminder ADD COLUMN fk_actioncomm integer NOT NULL; 363ALTER TABLE llx_actioncomm_reminder ADD COLUMN fk_email_template integer; 364ALTER TABLE llx_actioncomm_reminder ADD COLUMN lasterror varchar(128) NULL; 365 366ALTER TABLE llx_actioncomm_reminder DROP INDEX uk_actioncomm_reminder_unique; 367ALTER TABLE llx_actioncomm_reminder ADD UNIQUE uk_actioncomm_reminder_unique (fk_user, typeremind, offsetvalue, offsetunit, fk_actioncomm); 368 369ALTER TABLE llx_actioncomm_reminder ADD INDEX idx_actioncomm_reminder_status (status); 370 371ALTER TABLE llx_inventorydet ADD COLUMN fk_warehouse integer DEFAULT 0; 372ALTER TABLE llx_inventorydet ADD UNIQUE uk_inventorydet(fk_inventory, fk_warehouse, fk_product, batch); 373 374ALTER TABLE llx_commandedet ADD COLUMN ref_ext varchar(255) AFTER label; 375ALTER TABLE llx_facturedet ADD COLUMN ref_ext varchar(255) AFTER multicurrency_total_ttc; 376 377ALTER TABLE llx_c_ticket_category ADD COLUMN fk_parent integer DEFAULT 0 NOT NULL; 378ALTER TABLE llx_c_ticket_category ADD COLUMN force_severity varchar(32) NULL; 379 380ALTER TABLE llx_c_ticket_severity CHANGE color color VARCHAR(10) NULL; 381 382ALTER TABLE llx_expensereport ADD COLUMN fk_user_creat integer NULL; 383 384ALTER TABLE llx_expensereport_ik ADD COLUMN ikoffset double DEFAULT 0 NOT NULL; 385 386ALTER TABLE llx_paiement ADD COLUMN ref_ext varchar(255) AFTER ref; 387 388ALTER TABLE llx_bank ADD COLUMN origin_id integer; 389ALTER TABLE llx_bank ADD COLUMN origin_type varchar(64) NULL; 390ALTER TABLE llx_bank ADD COLUMN import_key varchar(14); 391 392ALTER TABLE llx_menu MODIFY COLUMN enabled text; 393 394CREATE TABLE llx_ecm_files_extrafields 395( 396 rowid integer AUTO_INCREMENT PRIMARY KEY, 397 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 398 fk_object integer NOT NULL, 399 import_key varchar(14) -- import key 400) ENGINE=innodb; 401 402ALTER TABLE llx_ecm_files_extrafields ADD INDEX idx_ecm_files_extrafields (fk_object); 403ALTER TABLE llx_ecm_files ADD COLUMN note_private text AFTER fk_user_m; 404ALTER TABLE llx_ecm_files ADD COLUMN note_public text AFTER note_private; 405 406CREATE TABLE llx_ecm_directories_extrafields 407( 408 rowid integer AUTO_INCREMENT PRIMARY KEY, 409 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 410 fk_object integer NOT NULL, 411 import_key varchar(14) -- import key 412) ENGINE=innodb; 413 414ALTER TABLE llx_ecm_directories_extrafields ADD INDEX idx_ecm_directories_extrafields (fk_object); 415ALTER TABLE llx_ecm_directories ADD COLUMN note_private text AFTER fk_user_m; 416ALTER TABLE llx_ecm_directories ADD COLUMN note_public text AFTER note_private; 417 418ALTER TABLE llx_website_page ADD COLUMN allowed_in_frames integer DEFAULT 0; 419ALTER TABLE llx_website_page ADD COLUMN object_type varchar(255); 420ALTER TABLE llx_website_page ADD COLUMN fk_object varchar(255); 421 422DELETE FROM llx_const WHERE name in ('MAIN_INCLUDE_ZERO_VAT_IN_REPORTS'); 423 424 425-- VMYSQL4.1 UPDATE llx_projet_task_time SET tms = null WHERE tms = 0; 426 427ALTER TABLE llx_projet_task_time CHANGE COLUMN tms tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 428 429ALTER TABLE llx_projet_task_time MODIFY COLUMN datec datetime; 430 431 432DELETE FROM llx_user_rights WHERE fk_id IN (SELECT id FROM llx_rights_def where module = 'holiday' and perms = 'lire_tous'); 433DELETE FROM llx_rights_def where module = 'holiday' and perms = 'lire_tous'; 434UPDATE llx_rights_def set perms = 'readall' WHERE perms = 'read_all' and module = 'holiday'; 435 436CREATE TABLE llx_c_product_nature ( 437 rowid integer AUTO_INCREMENT PRIMARY KEY, 438 code tinyint NOT NULL, 439 label varchar(100), 440 active tinyint DEFAULT 1 NOT NULL 441) ENGINE=innodb; 442 443 444ALTER TABLE llx_product DROP FOREIGN KEY fk_product_finished; 445 446-- VMYSQL4.1 DROP INDEX uk_c_product_nature on llx_c_product_nature; 447-- VPGSQL8.2 DROP INDEX uk_c_product_nature; 448 449ALTER TABLE llx_c_product_nature ADD UNIQUE INDEX uk_c_product_nature(code); 450 451INSERT INTO llx_c_product_nature (code, label, active) VALUES (0, 'RowMaterial', 1); 452INSERT INTO llx_c_product_nature (code, label, active) VALUES (1, 'Finished', 1); 453 454ALTER TABLE llx_product MODIFY COLUMN finished tinyint DEFAULT NULL; 455 456ALTER TABLE llx_product ADD CONSTRAINT fk_product_finished FOREIGN KEY (finished) REFERENCES llx_c_product_nature (code); 457 458-- MIGRATION TO DO AFTER RENAMING AN OBJECT 459 460-- drop constraint 461ALTER TABLE llx_livraison DROP FOREIGN KEY fk_livraison_fk_soc; 462ALTER TABLE llx_livraison DROP FOREIGN KEY fk_livraison_fk_user_author; 463ALTER TABLE llx_livraison DROP FOREIGN KEY fk_livraison_fk_user_valid; 464 465-- rename Table 466ALTER TABLE llx_livraison RENAME TO llx_delivery; 467ALTER TABLE llx_livraison_extrafields RENAME TO llx_delivery_extrafields; 468ALTER TABLE llx_livraisondet RENAME TO llx_deliverydet; 469ALTER TABLE llx_livraisondet_extrafields RENAME TO llx_deliverydet_extrafields; 470 471-- rename index 472ALTER TABLE llx_delivery DROP INDEX idx_livraison_uk_ref; 473ALTER TABLE llx_delivery ADD UNIQUE INDEX idx_delivery_uk_ref (ref, entity); 474ALTER TABLE llx_delivery DROP INDEX idx_livraison_fk_soc; 475ALTER TABLE llx_delivery ADD INDEX idx_delivery_fk_soc (fk_soc); 476ALTER TABLE llx_delivery DROP INDEX idx_livraison_fk_user_author; 477ALTER TABLE llx_delivery ADD INDEX idx_delivery_fk_user_author (fk_user_author); 478ALTER TABLE llx_delivery DROP INDEX idx_livraison_fk_user_valid; 479ALTER TABLE llx_delivery ADD INDEX idx_delivery_fk_user_valid (fk_user_valid); 480 481-- drop constraint 482ALTER TABLE llx_delivery DROP FOREIGN KEY fk_livraison_fk_soc; 483ALTER TABLE llx_delivery DROP FOREIGN KEY fk_livraison_fk_user_author; 484ALTER TABLE llx_delivery DROP FOREIGN KEY fk_livraison_fk_user_valid; 485 486-- add constraint 487ALTER TABLE llx_delivery ADD CONSTRAINT fk_delivery_fk_soc FOREIGN KEY (fk_soc) REFERENCES llx_societe (rowid); 488ALTER TABLE llx_delivery ADD CONSTRAINT fk_delivery_fk_user_author FOREIGN KEY (fk_user_author) REFERENCES llx_user (rowid); 489ALTER TABLE llx_delivery ADD CONSTRAINT fk_delivery_fk_user_valid FOREIGN KEY (fk_user_valid) REFERENCES llx_user (rowid); 490 491ALTER TABLE llx_deliverydet DROP FOREIGN KEY fk_livraisondet_fk_livraison; 492ALTER TABLE llx_deliverydet DROP INDEX idx_livraisondet_fk_expedition; 493ALTER TABLE llx_deliverydet CHANGE COLUMN fk_livraison fk_delivery integer; 494ALTER TABLE llx_deliverydet ADD INDEX idx_deliverydet_fk_delivery (fk_delivery); 495ALTER TABLE llx_deliverydet ADD CONSTRAINT fk_deliverydet_fk_delivery FOREIGN KEY (fk_delivery) REFERENCES llx_delivery (rowid); 496 497UPDATE llx_extrafields SET elementtype = 'delivery' WHERE elementtype = 'livraison'; 498UPDATE llx_extrafields SET elementtype = 'deliverydet' WHERE elementtype = 'livraisondet'; 499 500-- update llx_ecm_files 501UPDATE llx_ecm_files SET src_object_type = 'delivery' WHERE src_object_type = 'livraison'; 502 503-- update llx_links 504UPDATE llx_links SET objecttype = 'delivery' WHERE objecttype = 'livraison'; 505 506-- update llx_document_model 507UPDATE llx_document_model SET type = 'delivery' WHERE type = 'livraison'; 508 509-- update llx_object_lang 510UPDATE llx_object_lang SET type_object = 'delivery' WHERE type_object = 'livraison'; 511 512-- update llx_c_type_contact 513UPDATE llx_c_type_contact SET element = 'delivery' WHERE element = 'livraison'; 514 515-- update llx_c_email_template 516UPDATE llx_c_email_template SET type_template = 'delivery' WHERE type_template = 'livraison'; 517 518-- update llx_element_element 519UPDATE llx_element_element SET sourcetype = 'delivery' WHERE sourcetype = 'livraison'; 520UPDATE llx_element_element SET targettype = 'delivery' WHERE targettype = 'livraison'; 521 522-- update llx_actioncomm 523UPDATE llx_actioncomm SET element_type = 'delivery' WHERE element_type = 'livraison'; 524 525-- update llx_const 526UPDATE llx_const set name = 'DELIVERY_ADDON_NUMBER' WHERE name = 'LIVRAISON_ADDON_NUMBER'; 527UPDATE llx_const set value = 'mod_delivery_jade' WHERE value = 'mod_livraison_jade' AND name = 'DELIVERY_ADDON_NUMBER'; 528UPDATE llx_const set value = 'mod_delivery_saphir' WHERE value = 'mod_livraison_saphir' AND name = 'DELIVERY_ADDON_NUMBER'; 529 530-- update llx_rights_def 531UPDATE llx_rights_def set perms = 'delivery' WHERE perms = 'livraison' and module = 'expedition'; 532UPDATE llx_rights_def set perms = 'delivery_advance' WHERE perms = 'livraison_advance' and module = 'expedition'; 533 534 535ALTER TABLE llx_commande_fournisseurdet ADD INDEX idx_commande_fournisseurdet_fk_commande (fk_commande); 536ALTER TABLE llx_commande_fournisseurdet ADD INDEX idx_commande_fournisseurdet_fk_product (fk_product); 537 538 539CREATE TABLE llx_zapier_hook( 540 rowid integer AUTO_INCREMENT PRIMARY KEY, 541 entity integer DEFAULT 1 NOT NULL, 542 url varchar(255), 543 event varchar(255), 544 module varchar(128), 545 action varchar(128), 546 status integer, 547 date_creation datetime NOT NULL, 548 fk_user integer NOT NULL, 549 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 550 import_key varchar(14) 551) ENGINE=innodb; 552 553 554CREATE TABLE llx_session( 555 session_id varchar(50) PRIMARY KEY, 556 session_variable text, 557 last_accessed datetime NOT NULL, 558 fk_user integer NOT NULL, 559 remote_ip varchar(64) NULL, 560 user_agent varchar(128) NULL 561) ENGINE=innodb; 562 563INSERT INTO llx_c_socialnetworks (entity, code, label, url, icon, active) VALUES(1, 'github', 'Github', 'https://github.com/{socialid}', 'fa-github', 1); 564 565-- 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); 566-- 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); 567-- 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); 568 569ALTER TABLE llx_product_fournisseur_price ADD COLUMN packaging varchar(64); 570 571ALTER TABLE llx_projet ADD COLUMN fk_opp_status_end integer DEFAULT NULL; 572 573UPDATE llx_c_action_trigger SET elementtype = 'expensereport' where elementtype = 'expense_report' AND code like 'EXPENSE_%'; 574UPDATE llx_c_action_trigger SET code = 'EXPENSE_REPORT_PAID' where code = 'EXPENSE_REPORT_PAYED'; 575UPDATE llx_c_action_trigger SET code = 'EXPENSE_REPORT_DELETE' where code = 'EXPENSE_DELETE'; 576insert 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); 577insert 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); 578insert 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); 579insert 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); 580insert 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); 581 582-- Removed no more used function 583-- VPGSQL8.2 DROP FUNCTION IF EXISTS update_modified_column_date_m() CASCADE; 584-- VPGSQL8.2 DROP TRIGGER update_customer_modtime ON llx_ecm_directories; 585-- VPGSQL8.2 DROP TRIGGER update_customer_modtime ON llx_ecm_files; 586-- VPGSQL8.2 CREATE TRIGGER update_customer_modtime BEFORE UPDATE ON llx_ecm_directories FOR EACH ROW EXECUTE PROCEDURE update_modified_column_tms(); 587-- VPGSQL8.2 CREATE TRIGGER update_customer_modtime BEFORE UPDATE ON llx_ecm_files FOR EACH ROW EXECUTE PROCEDURE update_modified_column_tms(); 588 589 590insert into llx_c_actioncomm (id, code, type, libelle, module, active, position) values ( 6,'AC_EMAIL_IN','system','reception Email',NULL, 1, 4); 591 592 593-- VMYSQL4.3 ALTER TABLE llx_accounting_bookkeeping MODIFY COLUMN montant double(24,8) NULL; 594-- VPGSQL8.2 ALTER TABLE llx_accounting_bookkeeping ALTER COLUMN montant DROP NOT NULL; 595 596ALTER TABLE llx_export_model MODIFY COLUMN type varchar(64); 597 598