1-- 2-- Be carefull to requests order. 3-- This file must be loaded by calling /install/index.php page 4-- when current version is 5.0.0 or higher. 5-- 6-- To rename a table: ALTER TABLE llx_table RENAME TO llx_table_new; 7-- -- VPGSQL8.2 ALTER SEQUENCE IF EXISTS llx_table_rowid_seq RENAME TO llx_table_new_rowid_seq; 8-- To add a column: ALTER TABLE llx_table ADD COLUMN newcol varchar(60) NOT NULL DEFAULT '0' AFTER existingcol; 9-- To rename a column: ALTER TABLE llx_table CHANGE COLUMN oldname newname varchar(60); 10-- To drop a column: ALTER TABLE llx_table DROP COLUMN oldname; 11-- To change type of field: ALTER TABLE llx_table MODIFY COLUMN name varchar(60); 12-- To set a DEFAULT value: ALTER TABLE llx_table ALTER COLUMN name SET DEFAULT (0|NULL|...); 13-- To drop a foreign key: ALTER TABLE llx_table DROP FOREIGN KEY fk_name; 14-- To drop an index: -- VMYSQL4.0 DROP INDEX nomindex on llx_table 15-- To drop an index: -- VPGSQL8.0 DROP INDEX nomindex 16-- To restrict request to Mysql version x.y minimum use -- VMYSQLx.y 17-- To restrict request to Pgsql version x.y minimum use -- VPGSQLx.y 18-- To make pk to be auto increment (mysql): -- VMYSQL4.3 ALTER TABLE llx_c_shipment_mode CHANGE COLUMN rowid rowid INTEGER NOT NULL AUTO_INCREMENT; 19-- To make pk to be auto increment (postgres): -- VPGSQL8.2 NOT POSSIBLE. MUST DELETE/CREATE TABLE 20-- To set a field as NULL: -- VMYSQL4.3 ALTER TABLE llx_table MODIFY COLUMN name varchar(60) NULL; 21-- To set a field as NULL: -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name DROP NOT NULL; 22-- To set a field as NOT NULL: -- VMYSQL4.3 ALTER TABLE llx_table MODIFY COLUMN name varchar(60) NOT NULL; 23-- To set a field as NOT NULL: -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name SET NOT NULL; 24-- Note: fields with type BLOB/TEXT can't have default value. 25-- -- VPGSQL8.2 DELETE FROM llx_usergroup_user WHERE fk_user NOT IN (SELECT rowid from llx_user); 26-- -- VMYSQL4.1 DELETE FROM llx_usergroup_user WHERE fk_usergroup NOT IN (SELECT rowid from llx_usergroup); 27 28 29-- after changing const name, please insure that old constant was rename 30UPDATE llx_const SET name = __ENCRYPT('THIRDPARTY_DEFAULT_CREATE_CONTACT')__ WHERE name = __ENCRYPT('MAIN_THIRPARTY_CREATION_INDIVIDUAL')__; -- under 3.9.0 31UPDATE llx_const SET name = __ENCRYPT('THIRDPARTY_DEFAULT_CREATE_CONTACT')__ WHERE name = __ENCRYPT('MAIN_THIRDPARTY_CREATION_INDIVIDUAL')__; -- under 4.0.1 32 33-- VPGSQL8.2 ALTER TABLE llx_product_lot ALTER COLUMN entity SET DEFAULT 1; 34ALTER TABLE llx_product_lot MODIFY COLUMN entity integer DEFAULT 1; 35UPDATE llx_product_lot SET entity = 1 WHERE entity IS NULL; 36 37ALTER TABLE llx_bank_account ADD COLUMN extraparams varchar(255); 38 39ALTER TABLE llx_societe ALTER COLUMN fk_stcomm SET DEFAULT 0; 40 41ALTER TABLE llx_c_actioncomm ADD COLUMN picto varchar(48); 42 43ALTER TABLE llx_facturedet ADD INDEX idx_facturedet_fk_code_ventilation (fk_code_ventilation); 44ALTER TABLE llx_facture_fourn_det ADD INDEX idx_facture_fourn_det_fk_code_ventilation (fk_code_ventilation); 45 46ALTER TABLE llx_facture_fourn_det ADD INDEX idx_facture_fourn_det_fk_product (fk_product); 47 48ALTER TABLE llx_facture_rec ADD COLUMN fk_user_modif integer; 49ALTER TABLE llx_expedition ADD COLUMN fk_user_modif integer; 50ALTER TABLE llx_projet ADD COLUMN fk_user_modif integer; 51 52ALTER TABLE llx_adherent ADD COLUMN model_pdf varchar(255); 53 54ALTER TABLE llx_don ADD COLUMN date_valid datetime; 55 56DELETE FROM llx_menu where module='expensereport'; 57 58ALTER TABLE llx_facturedet ADD COLUMN fk_user_author integer after fk_unit; 59ALTER TABLE llx_facturedet ADD COLUMN fk_user_modif integer after fk_unit; 60 61ALTER TABLE llx_user DROP COLUMN phenix_login; 62ALTER TABLE llx_user DROP COLUMN phenix_pass; 63ALTER TABLE llx_user ADD COLUMN dateemployment datetime; 64 65ALTER TABLE llx_user MODIFY login varchar(50) NOT NULL; 66 67ALTER TABLE llx_societe ADD COLUMN fk_account integer; 68 69ALTER TABLE llx_commandedet ADD COLUMN fk_commandefourndet integer DEFAULT NULL after import_key; -- link to detail line of commande fourn (resplenish) 70ALTER TABLE llx_commandedet MODIFY COLUMN fk_commandefourndet integer DEFAULT NULL; 71 72ALTER TABLE llx_website ADD COLUMN virtualhost varchar(255) after fk_default_home; 73 74ALTER TABLE llx_chargesociales ADD COLUMN fk_account integer after fk_type; 75ALTER TABLE llx_chargesociales ADD COLUMN fk_mode_reglement integer after fk_account; 76ALTER TABLE llx_chargesociales ADD COLUMN fk_user_author integer; 77ALTER TABLE llx_chargesociales ADD COLUMN fk_user_modif integer; 78ALTER TABLE llx_chargesociales ADD COLUMN fk_user_valid integer; 79 80 81ALTER TABLE llx_ecm_files ADD COLUMN gen_or_uploaded varchar(12) after cover; 82 83DROP TABLE llx_document_generator; 84DROP TABLE llx_ecm_documents; 85DROP TABLE llx_holiday_events; 86DROP TABLE llx_holiday_types; 87 88ALTER TABLE llx_notify ADD COLUMN type_target varchar(16) NULL; 89 90ALTER TABLE llx_entrepot DROP COLUMN valo_pmp; 91 92ALTER TABLE llx_notify_def MODIFY COLUMN fk_soc integer NULL; 93-- VPGSQL8.2 ALTER TABLE llx_notify_def ALTER COLUMN fk_soc SET DEFAULT NULL; 94 95 96create table llx_categorie_project 97( 98 fk_categorie integer NOT NULL, 99 fk_project integer NOT NULL, 100 import_key varchar(14) 101)ENGINE=innodb; 102 103ALTER TABLE llx_categorie_project ADD PRIMARY KEY pk_categorie_project (fk_categorie, fk_project); 104ALTER TABLE llx_categorie_project ADD INDEX idx_categorie_project_fk_categorie (fk_categorie); 105ALTER TABLE llx_categorie_project ADD INDEX idx_categorie_project_fk_project (fk_project); 106 107ALTER TABLE llx_categorie_project ADD CONSTRAINT fk_categorie_project_categorie_rowid FOREIGN KEY (fk_categorie) REFERENCES llx_categorie (rowid); 108ALTER TABLE llx_categorie_project ADD CONSTRAINT fk_categorie_project_fk_project_rowid FOREIGN KEY (fk_project) REFERENCES llx_projet (rowid); 109 110ALTER TABLE llx_societe_remise_except ADD COLUMN entity integer DEFAULT 1 NOT NULL after rowid; 111ALTER TABLE llx_societe_remise ADD COLUMN entity integer DEFAULT 1 NOT NULL after rowid; 112 113 114create table llx_expensereport_extrafields 115( 116 rowid integer AUTO_INCREMENT PRIMARY KEY, 117 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 118 fk_object integer NOT NULL, 119 import_key varchar(14) -- import key 120) ENGINE=innodb; 121 122ALTER TABLE llx_expensereport_extrafields ADD INDEX idx_expensereport_extrafields (fk_object); 123 124ALTER TABLE llx_cotisation RENAME TO llx_subscription; 125-- VPGSQL8.2 ALTER SEQUENCE IF EXISTS llx_cotisation_rowid_seq RENAME TO llx_subscription_rowid_seq; 126 127ALTER TABLE llx_subscription ADD UNIQUE INDEX uk_subscription (fk_adherent,dateadh); 128ALTER TABLE llx_subscription CHANGE COLUMN cotisation subscription real; 129ALTER TABLE llx_adherent_type CHANGE COLUMN cotisation subscription varchar(3) NOT NULL DEFAULT '1'; 130 131UPDATE llx_adherent_type SET subscription = '1' WHERE subscription = 'yes'; 132 133CREATE TABLE llx_product_lot_extrafields 134( 135 rowid integer AUTO_INCREMENT PRIMARY KEY, 136 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 137 fk_object integer NOT NULL, 138 import_key varchar(14) -- import key 139) ENGINE=innodb; 140 141ALTER TABLE llx_product_lot_extrafields ADD INDEX idx_product_lot_extrafields (fk_object); 142 143ALTER TABLE llx_website_page MODIFY COLUMN content MEDIUMTEXT; 144 145CREATE TABLE llx_product_warehouse_properties 146( 147 rowid integer AUTO_INCREMENT PRIMARY KEY, 148 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 149 fk_product integer NOT NULL, 150 fk_entrepot integer NOT NULL, 151 seuil_stock_alerte integer DEFAULT 0, 152 desiredstock integer DEFAULT 0, 153 import_key varchar(14) -- Import key 154)ENGINE=innodb; 155 156ALTER TABLE llx_accounting_bookkeeping ADD COLUMN entity integer DEFAULT 1 NOT NULL; 157ALTER TABLE llx_accounting_bookkeeping ADD COLUMN fk_user_modif integer; 158ALTER TABLE llx_accounting_bookkeeping ADD COLUMN date_creation datetime; 159ALTER TABLE llx_accounting_bookkeeping ADD COLUMN tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 160-- VMYSQL4.3 ALTER TABLE llx_accounting_bookkeeping MODIFY COLUMN numero_compte varchar(32) NOT NULL; 161-- VMYSQL4.3 ALTER TABLE llx_accounting_bookkeeping MODIFY COLUMN code_journal varchar(32) NOT NULL; 162-- VPGSQL8.2 ALTER TABLE llx_accounting_bookkeeping ALTER COLUMN numero_compte SET NOT NULL; 163-- VPGSQL8.2 ALTER TABLE llx_accounting_bookkeeping ALTER COLUMN code_journal SET NOT NULL; 164 165ALTER TABLE llx_accounting_account ADD UNIQUE INDEX uk_accounting_account (account_number, entity, fk_pcg_version); 166 167ALTER TABLE llx_expensereport_det ADD COLUMN fk_code_ventilation integer DEFAULT 0; 168 169ALTER TABLE llx_c_payment_term CHANGE COLUMN fdm type_cdr tinyint; 170 171 172ALTER TABLE llx_facturedet ADD COLUMN vat_src_code varchar(10) DEFAULT '' AFTER tva_tx; 173ALTER TABLE llx_facturedet_rec ADD COLUMN vat_src_code varchar(10) DEFAULT '' AFTER tva_tx; 174ALTER TABLE llx_facture_fourn_det ADD COLUMN vat_src_code varchar(10) DEFAULT '' AFTER tva_tx; 175ALTER TABLE llx_commandedet ADD COLUMN vat_src_code varchar(10) DEFAULT '' AFTER tva_tx; 176ALTER TABLE llx_commande_fournisseurdet ADD COLUMN vat_src_code varchar(10) DEFAULT '' AFTER tva_tx; 177ALTER TABLE llx_propaldet ADD COLUMN vat_src_code varchar(10) DEFAULT '' AFTER tva_tx; 178ALTER TABLE llx_supplier_proposaldet ADD COLUMN vat_src_code varchar(10) DEFAULT '' AFTER tva_tx; 179ALTER TABLE llx_supplier_proposaldet ADD COLUMN fk_unit integer DEFAULT NULL; 180ALTER TABLE llx_contratdet ADD COLUMN vat_src_code varchar(10) DEFAULT '' AFTER tva_tx; 181 182ALTER TABLE llx_c_payment_term CHANGE COLUMN fdm type_cdr TINYINT; 183 184ALTER TABLE llx_entrepot ADD COLUMN fk_parent integer DEFAULT 0; 185 186create table llx_resource_extrafields 187( 188 rowid integer AUTO_INCREMENT PRIMARY KEY, 189 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 190 fk_object integer NOT NULL, 191 import_key varchar(14) -- import key 192) ENGINE=innodb; 193 194ALTER TABLE llx_resource_extrafields ADD INDEX idx_resource_extrafields (fk_object); 195 196INSERT INTO llx_const (name, value, type, note, visible, entity) values (__ENCRYPT('MAIN_SIZE_SHORTLIST_LIMIT')__, __ENCRYPT('3')__, 'chaine', 'Max length for small lists (tabs)', 0, 0); 197 198INSERT INTO llx_const (name, value, type, note, visible, entity) values (__ENCRYPT('EXPEDITION_ADDON_NUMBER')__, __ENCRYPT('mod_expedition_safor')__, 'chaine','Name for numbering manager for shipments',0,1); 199 200ALTER TABLE llx_bank_account ADD COLUMN note_public text; 201ALTER TABLE llx_bank_account ADD COLUMN model_pdf varchar(255); 202ALTER TABLE llx_bank_account ADD COLUMN import_key varchar(14); 203 204ALTER TABLE llx_projet ADD COLUMN import_key varchar(14); 205ALTER TABLE llx_projet_task ADD COLUMN import_key varchar(14); 206ALTER TABLE llx_projet_task_time ADD COLUMN import_key varchar(14); 207 208 209ALTER TABLE llx_overwrite_trans ADD COLUMN entity integer DEFAULT 1 NOT NULL AFTER rowid; 210 211ALTER TABLE llx_mailing_cibles ADD COLUMN error_text varchar(255); 212 213ALTER TABLE llx_c_actioncomm MODIFY COLUMN type varchar(50) DEFAULT 'system' NOT NULL; 214-- VPGSQL8.2 ALTER TABLE llx_c_actioncomm ALTER COLUMN type SET DEFAULT 'system'; 215-- VPGSQL8.2 ALTER TABLE llx_c_actioncomm ALTER COLUMN type SET NOT NULL; 216 217create table llx_user_employment 218( 219 rowid integer AUTO_INCREMENT PRIMARY KEY, 220 entity integer DEFAULT 1 NOT NULL, -- multi company id 221 ref varchar(50), -- reference 222 ref_ext varchar(50), -- reference into an external system (not used by dolibarr) 223 fk_user integer, 224 datec datetime, 225 tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 226 fk_user_creat integer, 227 fk_user_modif integer, 228 job varchar(128), -- job position. may be a dictionary 229 status integer NOT NULL, -- draft, active, closed 230 salary double(24,8), -- last and current value stored into llx_user 231 salaryextra double(24,8), -- last and current value stored into llx_user 232 weeklyhours double(16,8), -- last and current value stored into llx_user 233 dateemployment date, -- last and current value stored into llx_user 234 dateemploymentend date -- last and current value stored into llx_user 235)ENGINE=innodb; 236 237 238ALTER TABLE llx_expensereport ADD INDEX idx_expensereport_date_debut (date_debut); 239ALTER TABLE llx_expensereport ADD INDEX idx_expensereport_date_fin (date_fin); 240ALTER TABLE llx_expensereport ADD INDEX idx_expensereport_fk_statut (fk_statut); 241 242ALTER TABLE llx_expensereport ADD INDEX idx_expensereport_fk_user_author (fk_user_author); 243ALTER TABLE llx_expensereport ADD INDEX idx_expensereport_fk_user_valid (fk_user_valid); 244ALTER TABLE llx_expensereport ADD INDEX idx_expensereport_fk_user_approve (fk_user_approve); 245ALTER TABLE llx_expensereport ADD INDEX idx_expensereport_fk_refuse (fk_user_approve); 246 247DELETE FROM llx_actioncomm_resources WHERE fk_actioncomm not in (select id from llx_actioncomm); 248 249-- Sequence to removed duplicated values of llx_links. Use serveral times if you still have duplicate. 250DROP TABLE tmp_links_double; 251--select objectid, label, max(rowid) as max_rowid, count(rowid) as count_rowid from llx_links where label is not null group by objectid, label having count(rowid) >= 2; 252CREATE TABLE tmp_links_double AS (SELECT objectid, label, MAX(rowid) AS max_rowid, COUNT(rowid) AS count_rowid FROM llx_links WHERE label IS NOT NULL GROUP BY objectid, label HAVING COUNT(rowid) >= 2); 253--select * from tmp_links_double; 254DELETE FROM llx_links WHERE (rowid, label) IN (SELECT max_rowid, label FROM tmp_links_double); --update to avoid duplicate, delete to delete 255DROP TABLE tmp_links_double; 256 257ALTER TABLE llx_links ADD UNIQUE INDEX uk_links (objectid,label); 258 259ALTER TABLE llx_expensereport ADD UNIQUE INDEX idx_expensereport_uk_ref (ref, entity); 260 261UPDATE llx_projet_task SET ref = NULL WHERE ref = ''; 262ALTER TABLE llx_projet_task ADD UNIQUE INDEX uk_projet_task_ref (ref, entity); 263 264ALTER TABLE llx_contrat ADD COLUMN fk_user_modif integer; 265 266UPDATE llx_accounting_account SET account_parent = 0 WHERE account_parent = ''; 267 268-- VMYSQL4.3 ALTER TABLE llx_product_price MODIFY COLUMN date_price DATETIME NULL; 269-- VPGSQL8.2 ALTER TABLE llx_product_price ALTER COLUMN date_price DROP NOT NULL; 270ALTER TABLE llx_product_price ALTER COLUMN date_price SET DEFAULT NULL; 271 272ALTER TABLE llx_product_price ADD COLUMN default_vat_code varchar(10) after tva_tx; 273ALTER TABLE llx_product_customer_price ADD COLUMN default_vat_code varchar(10) after tva_tx; 274ALTER TABLE llx_product_customer_price_log ADD COLUMN default_vat_code varchar(10) after tva_tx; 275ALTER TABLE llx_product_fournisseur_price ADD COLUMN default_vat_code varchar(10) after tva_tx; 276 277ALTER TABLE llx_events MODIFY COLUMN ip varchar(250); 278 279UPDATE llx_bank SET label= '(SupplierInvoicePayment)' WHERE label= 'Règlement fournisseur'; 280UPDATE llx_bank SET label= '(CustomerInvoicePayment)' WHERE label= 'Règlement client'; 281 282