mkm/dolibarr
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
dolibarr/htdocs/install/mysql/migration/6.0.0-7.0.0.sql
mkm 45b490eead init
2024-09-06 20:28:06 +08:00

733 lines
45 KiB
SQL
Raw Permalink Blame History

--
-- Be carefull to requests order.
-- This file must be loaded by calling /install/index.php page
-- when current version is 7.0.0 or higher.
--
-- To rename a table: ALTER TABLE llx_table RENAME TO llx_table_new;
-- To add a column: ALTER TABLE llx_table ADD COLUMN newcol varchar(60) NOT NULL DEFAULT '0' AFTER existingcol;
-- To rename a column: ALTER TABLE llx_table CHANGE COLUMN oldname newname varchar(60);
-- To drop a column: ALTER TABLE llx_table DROP COLUMN oldname;
-- To change type of field: ALTER TABLE llx_table MODIFY COLUMN name varchar(60);
-- To drop a foreign key: ALTER TABLE llx_table DROP FOREIGN KEY fk_name;
-- To drop an index: -- VMYSQL4.0 DROP INDEX nomindex on llx_table
-- To drop an index: -- VPGSQL8.0 DROP INDEX nomindex
-- To restrict request to Mysql version x.y minimum use -- VMYSQLx.y
-- To restrict request to Pgsql version x.y minimum use -- VPGSQLx.y
-- 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;
-- To make pk to be auto increment (postgres): -- VPGSQL8.2 NOT POSSIBLE. MUST DELETE/CREATE TABLE
-- To set a field as NULL: -- VMYSQL4.3 ALTER TABLE llx_table MODIFY COLUMN name varchar(60) NULL;
-- To set a field as NULL: -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name DROP NOT NULL;
-- To set a field as NOT NULL: -- VMYSQL4.3 ALTER TABLE llx_table MODIFY COLUMN name varchar(60) NOT NULL;
-- To set a field as NOT NULL: -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name SET NOT NULL;
-- To set a field as default NULL: -- VPGSQL8.2 ALTER TABLE llx_table ALTER COLUMN name SET DEFAULT NULL;
-- Note: fields with type BLOB/TEXT can't have default value.
-- -- VPGSQL8.2 DELETE FROM llx_usergroup_user WHERE fk_user NOT IN (SELECT rowid from llx_user);
-- -- VMYSQL4.1 DELETE FROM llx_usergroup_user WHERE fk_usergroup NOT IN (SELECT rowid from llx_usergroup);
-- Must be before the utf8 pagecode fix
ALTER TABLE llx_product ADD COLUMN accountancy_code_sell_intra varchar(32) AFTER accountancy_code_sell;
ALTER TABLE llx_product ADD COLUMN accountancy_code_sell_export varchar(32) AFTER accountancy_code_sell_intra;
-- Drop old key with old name
ALTER TABLE llx_accounting_account DROP FOREIGN KEY fk_accountingaccount_fk_pcg_version;
-- Drop foreign key, so next alter will be a success
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_accounting_account DROP FOREIGN KEY fk_accounting_account_fk_pcg_version;
-- VMYSQL4.1 SET sql_mode = 'ALLOW_INVALID_DATES';
-- VMYSQL4.1 update llx_accounting_account set tms = datec where DATE(STR_TO_DATE(tms, '%Y-%m-%d')) IS NULL;
-- VMYSQL4.1 SET sql_mode = 'NO_ZERO_DATE';
-- VMYSQL4.1 update llx_accounting_account set tms = datec where DATE(STR_TO_DATE(tms, '%Y-%m-%d')) IS NULL;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_accounting_account MODIFY fk_pcg_version VARCHAR(20) CHARACTER SET utf8;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_accounting_account MODIFY fk_pcg_version VARCHAR(20) COLLATE utf8_unicode_ci;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_accounting_system MODIFY pcg_version VARCHAR(20) CHARACTER SET utf8;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_accounting_system MODIFY pcg_version VARCHAR(20) COLLATE utf8_unicode_ci;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_accounting_account MODIFY account_number VARCHAR(20) CHARACTER SET utf8;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_accounting_account MODIFY account_number VARCHAR(20) COLLATE utf8_unicode_ci;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_accounting_bookkeeping MODIFY numero_compte VARCHAR(20) CHARACTER SET utf8;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_accounting_bookkeeping MODIFY numero_compte VARCHAR(20) COLLATE utf8_unicode_ci;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_accounting_journal MODIFY code VARCHAR(20) CHARACTER SET utf8;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_accounting_journal MODIFY code VARCHAR(20) COLLATE utf8_unicode_ci;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_bank_account MODIFY accountancy_journal VARCHAR(20) CHARACTER SET utf8;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_bank_account MODIFY accountancy_journal VARCHAR(20) COLLATE utf8_unicode_ci;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_stock_mouvement MODIFY batch VARCHAR(30) CHARACTER SET utf8;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_stock_mouvement MODIFY batch VARCHAR(30) COLLATE utf8_unicode_ci;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_product_lot MODIFY batch VARCHAR(30) CHARACTER SET utf8;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_product_lot MODIFY batch VARCHAR(30) COLLATE utf8_unicode_ci;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_expeditiondet_batch MODIFY batch VARCHAR(30) CHARACTER SET utf8;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_expeditiondet_batch MODIFY batch VARCHAR(30) COLLATE utf8_unicode_ci;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_product_batch MODIFY batch VARCHAR(30) CHARACTER SET utf8;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_product_batch MODIFY batch VARCHAR(30) COLLATE utf8_unicode_ci;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_product MODIFY accountancy_code_sell VARCHAR(32) CHARACTER SET utf8;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_product MODIFY accountancy_code_sell VARCHAR(32) COLLATE utf8_unicode_ci;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_product MODIFY accountancy_code_sell_intra VARCHAR(32) CHARACTER SET utf8;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_product MODIFY accountancy_code_sell_intra VARCHAR(32) COLLATE utf8_unicode_ci;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_product MODIFY accountancy_code_sell_export VARCHAR(32) CHARACTER SET utf8;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_product MODIFY accountancy_code_sell_export VARCHAR(32) COLLATE utf8_unicode_ci;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_product MODIFY accountancy_code_buy VARCHAR(32) CHARACTER SET utf8;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_product MODIFY accountancy_code_buy VARCHAR(32) COLLATE utf8_unicode_ci;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_c_type_fees MODIFY accountancy_code VARCHAR(32) CHARACTER SET utf8;
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_c_type_fees MODIFY accountancy_code VARCHAR(32) COLLATE utf8_unicode_ci;
-- Restore dropped foreign key
-- VMYSQLUTF8UNICODECI ALTER TABLE llx_accounting_account ADD CONSTRAINT fk_accounting_account_fk_pcg_version FOREIGN KEY (fk_pcg_version) REFERENCES llx_accounting_system (pcg_version);
-- Missing in 5.0
ALTER TABLE llx_user MODIFY login varchar(50) NOT NULL;
-- Missing in 6.0 ?
ALTER TABLE llx_product_price ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_product_price ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_product_price ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_product_price ADD COLUMN multicurrency_price double(24,8) DEFAULT NULL;
ALTER TABLE llx_product_price ADD COLUMN multicurrency_price_ttc double(24,8) DEFAULT NULL;
ALTER TABLE llx_product_customer_price_log ADD COLUMN default_vat_code varchar(10);
ALTER TABLE llx_product_price ADD COLUMN default_vat_code varchar(10) AFTER tva_tx;
ALTER TABLE llx_product_customer_price ADD COLUMN default_vat_code varchar(10) AFTER tva_tx;
ALTER TABLE llx_product_fournisseur_price ADD COLUMN default_vat_code varchar(10) AFTER tva_tx;
ALTER TABLE llx_website_page ADD COLUMN fk_user_create integer;
ALTER TABLE llx_website_page ADD COLUMN fk_user_modif integer;
ALTER TABLE llx_website_page ADD COLUMN type_container varchar(16) NOT NULL DEFAULT 'page';
-- For 7.0
delete from llx_c_action_trigger where code = 'MEMBER_SUBSCRIPTION';
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('MEMBER_SUBSCRIPTION_CREATE','Member subscribtion recorded','Executed when a member subscribtion is deleted','member',24);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('MEMBER_SUBSCRIPTION_MODIFY','Member subscribtion modified','Executed when a member subscribtion is modified','member',24);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('MEMBER_SUBSCRIPTION_DELETE','Member subscribtion deleted','Executed when a member subscribtion is deleted','member',24);
-- VPGSQL8.4 ALTER TABLE llx_product_attribute DROP CONSTRAINT unique_ref;
ALTER TABLE llx_product_attribute_value DROP INDEX unique_ref;
ALTER TABLE llx_product_attribute_value ADD UNIQUE INDEX uk_product_attribute_value (fk_product_attribute, ref);
ALTER TABLE llx_product_price_by_qty ADD COLUMN quantity double DEFAULT NULL;
ALTER TABLE llx_product_price_by_qty ADD COLUMN unitprice double(24,8) DEFAULT 0;
ALTER TABLE llx_product_price_by_qty ADD COLUMN price_base_type varchar(3) DEFAULT 'HT';
ALTER TABLE llx_product_price_by_qty ADD COLUMN fk_multicurrency integer;
ALTER TABLE llx_product_price_by_qty ADD COLUMN multicurrency_code varchar(255);
ALTER TABLE llx_product_price_by_qty ADD COLUMN multicurrency_tx double(24,8) DEFAULT 1;
ALTER TABLE llx_product_price_by_qty ADD COLUMN multicurrency_price double(24,8) DEFAULT NULL;
ALTER TABLE llx_product_price_by_qty ADD COLUMN multicurrency_price_ttc double(24,8) DEFAULT NULL;
-- VMYSQL4.0 DROP INDEX uk_product_price_by_qty_level on llx_product_price_by_qty;
-- VPGSQL8.0 DROP INDEX uk_product_price_by_qty_level;
ALTER TABLE llx_product_price_by_qty ADD UNIQUE INDEX uk_product_price_by_qty_level (fk_product_price, quantity);
ALTER TABLE llx_accounting_bookkeeping ADD INDEX idx_accounting_bookkeeping_fk_doc (fk_doc);
ALTER TABLE llx_c_revenuestamp ADD COLUMN revenuestamp_type varchar(16) DEFAULT 'fixed' NOT NULL;
UPDATE llx_contrat SET ref = rowid WHERE ref IS NULL OR ref = '';
ALTER TABLE llx_contratdet ADD COLUMN vat_src_code varchar(10) DEFAULT '';
INSERT INTO llx_c_type_contact(rowid, element, source, code, libelle, active ) values (42, 'propal', 'external', 'SHIPPING', 'Customer contact for delivery', 1);
ALTER TABLE llx_inventory ADD date_validation datetime DEFAULT NULL;
ALTER TABLE llx_inventory CHANGE COLUMN datec date_creation datetime DEFAULT NULL;
ALTER TABLE llx_inventory CHANGE COLUMN fk_user_author fk_user_creat integer;
ALTER TABLE llx_inventory ADD UNIQUE INDEX uk_inventory_ref (ref, entity);
ALTER table llx_entrepot CHANGE COLUMN label ref varchar(255);
UPDATE llx_paiementfourn SET ref = rowid WHERE ref IS NULL;
UPDATE llx_paiementfourn SET entity = 1 WHERE entity IS NULL;
UPDATE llx_website SET entity = 1 WHERE entity IS NULL;
-- VMYSQL4.3 ALTER TABLE llx_website MODIFY COLUMN entity integer NOT NULL DEFAULT 1;
-- VPGSQL8.2 ALTER TABLE llx_website ALTER COLUMN entity SET NOT NULL;
ALTER TABLE llx_user ADD COLUMN birth date;
-- VMYSQL4.1 ALTER TABLE llx_holiday_users DROP PRIMARY KEY;
ALTER TABLE llx_holiday_users ADD UNIQUE INDEX uk_holiday_users(fk_user, fk_type, nb_holiday);
ALTER TABLE llx_product_fournisseur_price ADD COLUMN localtax1_tx double(6,3) DEFAULT 0;
ALTER TABLE llx_product_fournisseur_price ADD COLUMN localtax1_type varchar(10) NOT NULL DEFAULT '0';
ALTER TABLE llx_product_fournisseur_price ADD COLUMN localtax2_tx double(6,3) DEFAULT 0;
ALTER TABLE llx_product_fournisseur_price ADD COLUMN localtax2_type varchar(10) NOT NULL DEFAULT '0';
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('MEMBER_SENTBYMAIL','Mails sent from member card','Executed when you send email from member card','member',23);
ALTER TABLE llx_ecm_files MODIFY label varchar(128) NOT NULL;
ALTER TABLE llx_ecm_files ADD COLUMN share varchar(128) NULL after label;
ALTER TABLE llx_ecm_files ADD COLUMN src_object_type varchar(32);
ALTER TABLE llx_ecm_files ADD COLUMN src_object_id integer;
ALTER TABLE llx_propal ADD COLUMN last_main_doc varchar(255);
ALTER TABLE llx_commande ADD COLUMN last_main_doc varchar(255);
ALTER TABLE llx_facture ADD COLUMN last_main_doc varchar(255);
ALTER TABLE llx_contrat ADD COLUMN last_main_doc varchar(255);
ALTER TABLE llx_expedition ADD COLUMN last_main_doc varchar(255);
ALTER TABLE llx_fichinter ADD COLUMN last_main_doc varchar(255);
ALTER TABLE llx_livraison ADD COLUMN last_main_doc varchar(255);
ALTER TABLE llx_supplier_proposal ADD COLUMN last_main_doc varchar(255);
ALTER TABLE llx_facture_fourn ADD COLUMN last_main_doc varchar(255);
ALTER TABLE llx_commande_fournisseur ADD COLUMN last_main_doc varchar(255);
ALTER TABLE llx_c_paiement ADD COLUMN position integer NOT NULL DEFAULT 0;
ALTER TABLE llx_c_payment_term ADD COLUMN position integer NOT NULL DEFAULT 0;
ALTER TABLE llx_product MODIFY COLUMN seuil_stock_alerte integer DEFAULT NULL;
-- VPGSQL8.2 ALTER TABLE llx_product ALTER COLUMN seuil_stock_alerte SET DEFAULT NULL;
ALTER TABLE llx_facture_rec ADD COLUMN suspended integer DEFAULT 0;
ALTER TABLE llx_facture_rec MODIFY COLUMN titre VARCHAR(100);
ALTER TABLE llx_contrat MODIFY COLUMN ref varchar(50);
ALTER TABLE llx_contrat MODIFY COLUMN ref_customer varchar(50);
ALTER TABLE llx_contrat MODIFY COLUMN ref_supplier varchar(50);
ALTER TABLE llx_contrat MODIFY COLUMN ref_ext varchar(50);
UPDATE llx_c_email_templates SET position = 0 WHERE position IS NULL;
UPDATE llx_c_email_templates SET lang = '' WHERE lang IS NULL;
ALTER TABLE llx_c_email_templates ADD COLUMN enabled varchar(255) DEFAULT '1';
ALTER TABLE llx_c_email_templates ADD COLUMN joinfiles varchar(255) DEFAULT '1';
ALTER TABLE llx_c_email_templates MODIFY COLUMN content mediumtext;
INSERT INTO llx_c_email_templates (entity,module,type_template,lang,private,fk_user,datec,label,position,enabled,active,topic,content,content_lines) VALUES (0,'adherent','member','',0,null,null,'(SendingAnEMailToMember)',1,1,1,'__(CardContent)__','__(Hello)__,<br><br>\n\n__(ThisIsContentOfYourCard)__<br>\n__(ID)__ : __ID__<br>\n__(Civiliyty)__ : __MEMBER_CIVILITY__<br>\n__(Firstname)__ : __MEMBER_FIRSTNAME__<br>\n__(Lastname)__ : __MEMBER_LASTNAME__<br>\n__(Fullname)__ : __MEMBER_FULLNAME__<br>\n__(Company)__ : __MEMBER_COMPANY__<br>\n__(Address)__ : __MEMBER_ADDRESS__<br>\n__(Zip)__ : __MEMBER_ZIP__<br>\n__(Town)__ : __MEMBER_TOWN__<br>\n__(Country)__ : __MEMBER_COUNTRY__<br>\n__(Email)__ : __MEMBER_EMAIL__<br>\n__(Birthday)__ : __MEMBER_BIRTH__<br>\n__(Photo)__ : __MEMBER_PHOTO__<br>\n__(Login)__ : __MEMBER_LOGIN__<br>\n__(Password)__ : __MEMBER_PASSWORD__<br>\n__(Phone)__ : __MEMBER_PHONE__<br>\n__(PhonePerso)__ : __MEMBER_PHONEPRO__<br>\n__(PhoneMobile)__ : __MEMBER_PHONEMOBILE__<br><br>\n__(Sincerely)__<br>__USER_SIGNATURE__',null);
INSERT INTO llx_c_email_templates (entity,module,type_template,lang,private,fk_user,datec,label,position,enabled,active,topic,content,content_lines) VALUES (0,'banque','thirdparty','',0,null,null,'(YourSEPAMandate)',1,1,0,'__(YourSEPAMandate)__','__(Hello)__,<br><br>\n\n__(FindYourSEPAMandate)__ :<br>\n__MYCOMPANY_NAME__<br>\n__MYCOMPANY_FULLADDRESS__<br><br>\n__(Sincerely)__<br>\n__USER_SIGNATURE__',null);
INSERT INTO llx_c_accounting_category (rowid, code, label, range_account, sens, category_type, formula, position, fk_country, active) VALUES ( 1, 'VENTES', 'Income of products/services', 'Exemple: 7xxxxx', 0, 0, '', '10', 1, 1);
INSERT INTO llx_c_accounting_category (rowid, code, label, range_account, sens, category_type, formula, position, fk_country, active) VALUES ( 2, 'DEPENSES', 'Expenses of products/services', 'Exemple: 6xxxxx', 0, 0, '', '20', 1, 1);
INSERT INTO llx_c_accounting_category (rowid, code, label, range_account, sens, category_type, formula, position, fk_country, active) VALUES ( 3, 'PROFIT', 'Balance', '', 0, 1, 'VENTES+DEPENSES', '30', 1, 1);
UPDATE llx_c_accounting_category set code = 'VENTES', range_account='7xxxxx' where code = 'VTE';
UPDATE llx_c_accounting_category set code = 'DEPENSES', range_account='6xxxxx' where code = 'MAR';
UPDATE llx_c_accounting_category set code = 'PROFIT', range_account='Balance', formula = 'VENTES+DEPENSES' where code = 'MARGE';
ALTER TABLE llx_menu MODIFY COLUMN perms text;
ALTER TABLE llx_mailing MODIFY COLUMN titre varchar(128);
ALTER TABLE llx_mailing MODIFY COLUMN sujet varchar(128);
ALTER TABLE llx_mailing MODIFY COLUMN langs varchar(64);
ALTER TABLE llx_facture_fourn ADD COLUMN date_pointoftax date DEFAULT NULL;
ALTER TABLE llx_facture_fourn ADD COLUMN date_valid date;
ALTER TABLE llx_bookmark DROP COLUMN fk_soc;
ALTER TABLE llx_website MODIFY COLUMN ref varchar(128);
ALTER TABLE llx_website_page MODIFY COLUMN pageurl varchar(255);
ALTER TABLE llx_website_page ADD COLUMN lang varchar(6);
ALTER TABLE llx_website_page ADD COLUMN fk_page integer;
ALTER TABLE llx_website_page ADD COLUMN grabbed_from varchar(255);
ALTER TABLE llx_website_page ADD COLUMN htmlheader mediumtext;
ALTER TABLE llx_website_page MODIFY COLUMN htmlheader mediumtext;
ALTER TABLE llx_website_page MODIFY COLUMN status INTEGER DEFAULT 1;
UPDATE llx_website_page set status = 1 WHERE status IS NULL;
ALTER TABLE llx_website ADD COLUMN import_key varchar(14);
ALTER TABLE llx_website_page ADD COLUMN import_key varchar(14);
ALTER TABLE llx_fichinter ADD COLUMN import_key varchar(14);
ALTER TABLE llx_livraison ADD COLUMN import_key varchar(14);
ALTER TABLE llx_livraison ADD COLUMN extraparams varchar(255);
ALTER TABLE llx_don ADD COLUMN extraparams varchar(255);
ALTER TABLE llx_accounting_account ADD COLUMN import_key varchar(14);
ALTER TABLE llx_accounting_account ADD COLUMN extraparams varchar(255);
ALTER TABLE llx_accounting_bookkeeping ADD COLUMN import_key varchar(14);
ALTER TABLE llx_accounting_bookkeeping ADD COLUMN extraparams varchar(255);
ALTER TABLE llx_accounting_bookkeeping_tmp ADD COLUMN extraparams varchar(255);
ALTER TABLE llx_accounting_bookkeeping ADD COLUMN date_lim_reglement datetime DEFAULT NULL;
ALTER TABLE llx_accounting_bookkeeping ADD COLUMN fk_user integer NULL;
ALTER TABLE llx_accounting_bookkeeping_tmp ADD COLUMN date_lim_reglement datetime DEFAULT NULL;
ALTER TABLE llx_accounting_bookkeeping_tmp ADD COLUMN fk_user integer NULL;
ALTER TABLE llx_menu MODIFY fk_mainmenu varchar(100);
ALTER TABLE llx_menu MODIFY fk_leftmenu varchar(100);
CREATE TABLE llx_website_extrafields
(
rowid integer AUTO_INCREMENT PRIMARY KEY,
tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
fk_object integer NOT NULL,
import_key varchar(14) -- import key
) ENGINE=innodb;
ALTER TABLE llx_website_extrafields ADD INDEX idx_website_extrafields (fk_object);
CREATE TABLE llx_website_account(
rowid integer AUTO_INCREMENT PRIMARY KEY NOT NULL,
login varchar(64) NOT NULL,
pass_encoding varchar(24) NOT NULL,
pass_crypted varchar(128),
pass_temp varchar(128), -- temporary password when asked for forget password
fk_soc integer,
fk_website integer NOT NULL,
note_private text,
date_last_login datetime,
date_previous_login datetime,
date_creation datetime NOT NULL,
tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
fk_user_creat integer NOT NULL,
fk_user_modif integer,
import_key varchar(14),
status integer
) ENGINE=innodb;
ALTER TABLE llx_website_account ADD INDEX idx_website_account_rowid (rowid);
ALTER TABLE llx_website_account ADD INDEX idx_website_account_login (login);
ALTER TABLE llx_website_account ADD INDEX idx_website_account_import_key (import_key);
ALTER TABLE llx_website_account ADD INDEX idx_website_account_status (status);
ALTER TABLE llx_website_account ADD INDEX idx_website_account_fk_soc (fk_soc);
ALTER TABLE llx_website_account ADD INDEX idx_website_account_fk_website (fk_website);
ALTER TABLE llx_website_account ADD UNIQUE INDEX uk_website_account_login_website_soc(login, fk_website, fk_soc);
ALTER TABLE llx_website_account ADD CONSTRAINT llx_website_account_fk_website FOREIGN KEY (fk_website) REFERENCES llx_website(rowid);
CREATE TABLE llx_website_account_extrafields
(
rowid integer AUTO_INCREMENT PRIMARY KEY,
tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
fk_object integer NOT NULL,
import_key varchar(14) -- import key
) ENGINE=innodb;
ALTER TABLE llx_website_account_extrafields ADD INDEX idx_website_account_extrafields (fk_object);
alter table llx_user add column pass_encoding varchar(24) NULL;
CREATE TABLE IF NOT EXISTS llx_expensereport_ik (
rowid integer AUTO_INCREMENT PRIMARY KEY,
datec datetime DEFAULT NULL,
tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
fk_c_exp_tax_cat integer DEFAULT 0 NOT NULL,
fk_range integer DEFAULT 0 NOT NULL,
coef double DEFAULT 0 NOT NULL,
ikoffset double DEFAULT 0 NOT NULL,
active integer DEFAULT 1
)ENGINE=innodb;
ALTER TABLE llx_expensereport_ik ADD COLUMN ikoffset double DEFAULT 0 NOT NULL;
CREATE TABLE IF NOT EXISTS llx_c_exp_tax_cat (
rowid integer AUTO_INCREMENT PRIMARY KEY,
label varchar(48) NOT NULL,
entity integer DEFAULT 1 NOT NULL,
active integer DEFAULT 1 NOT NULL
)ENGINE=innodb;
CREATE TABLE IF NOT EXISTS llx_c_exp_tax_range (
rowid integer AUTO_INCREMENT PRIMARY KEY,
fk_c_exp_tax_cat integer DEFAULT 1 NOT NULL,
range_ik double DEFAULT 0 NOT NULL,
entity integer DEFAULT 1 NOT NULL,
active integer DEFAULT 1 NOT NULL
)ENGINE=innodb;
INSERT INTO llx_c_type_fees (code, label, active, accountancy_code) VALUES
('EX_KME', 'ExpLabelKm', 1, '625100'),
('EX_FUE', 'ExpLabelFuelCV', 0, '606150'),
('EX_HOT', 'ExpLabelHotel', 0, '625160'),
('EX_PAR', 'ExpLabelParkingCV', 0, '625160'),
('EX_TOL', 'ExpLabelTollCV', 0, '625160'),
('EX_TAX', 'ExpLabelVariousTaxes', 0, '637800'),
('EX_IND', 'ExpLabelIndemnityTranspSub', 0, '648100'),
('EX_SUM', 'ExpLabelMaintenanceSupply', 0, '606300'),
('EX_SUO', 'ExpLabelOfficeSupplies', 0, '606400'),
('EX_CAR', 'ExpLabelCarRental', 0, '613000'),
('EX_DOC', 'ExpLabelDocumentation', 0, '618100'),
('EX_CUR', 'ExpLabelCustomersReceiving', 0, '625710'),
('EX_OTR', 'ExpLabelOtherReceiving', 0, '625700'),
('EX_POS', 'ExpLabelPostage', 0, '626100'),
('EX_CAM', 'ExpLabelMaintenanceRepairCV', 0, '615300'),
('EX_EMM', 'ExpLabelEmployeesMeal', 0, '625160'),
('EX_GUM', 'ExpLabelGuestsMeal', 0, '625160'),
('EX_BRE', 'ExpLabelBreakfast', 0, '625160'),
('EX_FUE_VP', 'ExpLabelFuelPV', 0, '606150'),
('EX_TOL_VP', 'ExpLabelTollPV', 0, '625160'),
('EX_PAR_VP', 'ExpLabelParkingPV', 0, '625160'),
('EX_CAM_VP', 'ExpLabelMaintenanceRepairPV', 0, '615300');
INSERT INTO llx_expensereport_ik (rowid, fk_c_exp_tax_cat, fk_range, coef, ikoffset) values (1,4, 1, 0.41, 0);
INSERT INTO llx_expensereport_ik (rowid, fk_c_exp_tax_cat, fk_range, coef, ikoffset) values (2,4, 2, 0.244, 824);
INSERT INTO llx_expensereport_ik (rowid, fk_c_exp_tax_cat, fk_range, coef, ikoffset) values (3,4, 3, 0.286, 0);
INSERT INTO llx_expensereport_ik (rowid, fk_c_exp_tax_cat, fk_range, coef, ikoffset) values (4,5, 4, 0.493, 0);
INSERT INTO llx_expensereport_ik (rowid, fk_c_exp_tax_cat, fk_range, coef, ikoffset) values (5,5, 5, 0.277, 1082);
INSERT INTO llx_expensereport_ik (rowid, fk_c_exp_tax_cat, fk_range, coef, ikoffset) values (6,5, 6, 0.332, 0);
INSERT INTO llx_expensereport_ik (rowid, fk_c_exp_tax_cat, fk_range, coef, ikoffset) values (7,6, 7, 0.543, 0);
INSERT INTO llx_expensereport_ik (rowid, fk_c_exp_tax_cat, fk_range, coef, ikoffset) values (8,6, 8, 0.305, 1180);
INSERT INTO llx_expensereport_ik (rowid, fk_c_exp_tax_cat, fk_range, coef, ikoffset) values (9,6, 9, 0.364, 0);
INSERT INTO llx_expensereport_ik (rowid, fk_c_exp_tax_cat, fk_range, coef, ikoffset) values (10,7, 10, 0.568, 0);
INSERT INTO llx_expensereport_ik (rowid, fk_c_exp_tax_cat, fk_range, coef, ikoffset) values (11,7, 11, 0.32, 1244);
INSERT INTO llx_expensereport_ik (rowid, fk_c_exp_tax_cat, fk_range, coef, ikoffset) values (12,7, 12, 0.382, 0);
INSERT INTO llx_expensereport_ik (rowid, fk_c_exp_tax_cat, fk_range, coef, ikoffset) values (13,8, 13, 0.595, 0);
INSERT INTO llx_expensereport_ik (rowid, fk_c_exp_tax_cat, fk_range, coef, ikoffset) values (14,8, 14, 0.337, 1288);
INSERT INTO llx_expensereport_ik (rowid, fk_c_exp_tax_cat, fk_range, coef, ikoffset) values (15,8, 15, 0.401, 0);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (1,'ExpAutoCat', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (2,'ExpCycloCat', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (3,'ExpMotoCat', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (4,'ExpAuto3CV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (5,'ExpAuto4CV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (6,'ExpAuto5CV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (7,'ExpAuto6CV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (8,'ExpAuto7CV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (9,'ExpAuto8CV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (10,'ExpAuto9CV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (11,'ExpAuto10CV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (12,'ExpAuto11CV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (13,'ExpAuto12CV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (14,'ExpAuto3PCV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (15,'ExpAuto4PCV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (16,'ExpAuto5PCV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (17,'ExpAuto6PCV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (18,'ExpAuto7PCV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (19,'ExpAuto8PCV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (20,'ExpAuto9PCV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (21,'ExpAuto10PCV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (22,'ExpAuto11PCV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (23,'ExpAuto12PCV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (24,'ExpAuto13PCV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (25,'ExpCyclo', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (26,'ExpMoto12CV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (27,'ExpMoto345CV', 1, 1);
INSERT INTO llx_c_exp_tax_cat (rowid, label, entity, active) values (28,'ExpMoto5PCV', 1, 1);
INSERT INTO llx_c_exp_tax_range (rowid,fk_c_exp_tax_cat,range_ik, entity, active) values (1,4, 0, 1, 1);
INSERT INTO llx_c_exp_tax_range (rowid,fk_c_exp_tax_cat,range_ik, entity, active) values (2,4, 5000, 1, 1);
INSERT INTO llx_c_exp_tax_range (rowid,fk_c_exp_tax_cat,range_ik, entity, active) values (3,4, 20000, 1, 1);
INSERT INTO llx_c_exp_tax_range (rowid,fk_c_exp_tax_cat,range_ik, entity, active) values (4,5, 0, 1, 1);
INSERT INTO llx_c_exp_tax_range (rowid,fk_c_exp_tax_cat,range_ik, entity, active) values (5,5, 5000, 1, 1);
INSERT INTO llx_c_exp_tax_range (rowid,fk_c_exp_tax_cat,range_ik, entity, active) values (6,5, 20000, 1, 1);
INSERT INTO llx_c_exp_tax_range (rowid,fk_c_exp_tax_cat,range_ik, entity, active) values (7,6, 0, 1, 1);
INSERT INTO llx_c_exp_tax_range (rowid,fk_c_exp_tax_cat,range_ik, entity, active) values (8,6, 5000, 1, 1);
INSERT INTO llx_c_exp_tax_range (rowid,fk_c_exp_tax_cat,range_ik, entity, active) values (9,6, 20000, 1, 1);
INSERT INTO llx_c_exp_tax_range (rowid,fk_c_exp_tax_cat,range_ik, entity, active) values (10,7, 0, 1, 1);
INSERT INTO llx_c_exp_tax_range (rowid,fk_c_exp_tax_cat,range_ik, entity, active) values (11,7, 5000, 1, 1);
INSERT INTO llx_c_exp_tax_range (rowid,fk_c_exp_tax_cat,range_ik, entity, active) values (12,7, 20000, 1, 1);
INSERT INTO llx_c_exp_tax_range (rowid,fk_c_exp_tax_cat,range_ik, entity, active) values (13,8, 0, 1, 1);
INSERT INTO llx_c_exp_tax_range (rowid,fk_c_exp_tax_cat,range_ik, entity, active) values (14,8, 5000, 1, 1);
INSERT INTO llx_c_exp_tax_range (rowid,fk_c_exp_tax_cat,range_ik, entity, active) values (15,8, 20000, 1, 1);
CREATE TABLE llx_expensereport_rules (
rowid integer AUTO_INCREMENT PRIMARY KEY,
datec datetime DEFAULT NULL,
tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dates datetime NOT NULL,
datee datetime NOT NULL,
amount numeric(24,8) NOT NULL,
restrictive tinyint NOT NULL,
fk_user integer DEFAULT NULL,
fk_usergroup integer DEFAULT NULL,
fk_c_type_fees integer NOT NULL,
code_expense_rules_type varchar(50) NOT NULL,
is_for_all tinyint DEFAULT 0,
entity integer DEFAULT 1
)ENGINE=innodb;
ALTER TABLE llx_expensereport_det ADD COLUMN rule_warning_message text;
ALTER TABLE llx_expensereport_det ADD COLUMN fk_c_exp_tax_cat integer;
ALTER TABLE llx_user ADD COLUMN default_range integer;
ALTER TABLE llx_user ADD COLUMN default_c_exp_tax_cat integer;
ALTER TABLE llx_extrafields ADD COLUMN fk_user_author integer;
ALTER TABLE llx_extrafields ADD COLUMN fk_user_modif integer;
ALTER TABLE llx_extrafields ADD COLUMN datec datetime;
ALTER TABLE llx_extrafields ADD COLUMN enabled varchar(255) DEFAULT '1';
ALTER TABLE llx_extrafields ADD COLUMN tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
-- We fix value of 'list' from 0 to 1 for all extrafields created before this migration
--VMYSQL4.1 UPDATE llx_extrafields SET list = 1 WHERE list = 0 AND fk_user_author IS NULL and fk_user_modif IS NULL and datec IS NULL;
--VMYSQL4.1 UPDATE llx_extrafields SET list = 3 WHERE type = 'separate' AND list <> 3;
--VPGSQL8.2 UPDATE llx_extrafields SET list = 1 WHERE list::integer = 0 AND fk_user_author IS NULL and fk_user_modif IS NULL and datec IS NULL;
--VPGSQL8.2 UPDATE llx_extrafields SET list = 3 WHERE type = 'separate' AND list::integer <> 3;
--VMYSQL4.1 ALTER TABLE llx_extrafields MODIFY COLUMN list integer DEFAULT 1;
--VPGSQL8.2 ALTER TABLE llx_extrafields MODIFY COLUMN list integer DEFAULT 1 USING list::integer;
--VPGSQL8.2 ALTER TABLE llx_extrafields ALTER COLUMN list SET DEFAULT 1;
ALTER TABLE llx_extrafields MODIFY COLUMN langs varchar(64);
ALTER TABLE llx_holiday_config MODIFY COLUMN name varchar(128) NOT NULL;
ALTER TABLE llx_holiday_config ADD UNIQUE INDEX idx_holiday_config (name);
ALTER TABLE llx_societe MODIFY COLUMN ref_ext varchar(255);
ALTER TABLE llx_socpeople MODIFY COLUMN ref_ext varchar(255);
ALTER TABLE llx_actioncomm MODIFY COLUMN ref_ext varchar(255);
ALTER TABLE llx_expedition MODIFY COLUMN ref_ext varchar(255);
ALTER TABLE llx_livraison MODIFY COLUMN ref_ext varchar(255);
ALTER TABLE llx_contrat MODIFY COLUMN ref_ext varchar(255);
ALTER TABLE llx_actioncomm MODIFY COLUMN label varchar(255) NOT NULL;
ALTER TABLE llx_actioncomm ADD INDEX idx_actioncomm_fk_user_action (fk_user_action);
ALTER TABLE llx_actioncomm ADD INDEX idx_actioncomm_fk_project (fk_project);
ALTER TABLE llx_actioncomm ADD INDEX idx_actioncomm_datep (datep);
ALTER TABLE llx_actioncomm ADD INDEX idx_actioncomm_datep2 (datep2);
ALTER TABLE llx_actioncomm ADD INDEX idx_actioncomm_recurid (recurid);
ALTER TABLE llx_actioncomm ADD INDEX idx_actioncomm_ref_ext (ref_ext);
ALTER TABLE llx_payment_various ADD COLUMN fk_projet integer DEFAULT NULL after accountancy_code;
UPDATE llx_const set name = __ENCRYPT('ONLINE_PAYMENT_MESSAGE_OK')__ where name = __ENCRYPT('PAYPAL_MESSAGE_OK')__;
UPDATE llx_const set name = __ENCRYPT('ONLINE_PAYMENT_MESSAGE_KO')__ where name = __ENCRYPT('PAYPAL_MESSAGE_KO')__;
UPDATE llx_const set name = __ENCRYPT('ONLINE_PAYMENT_CREDITOR')__ where name = __ENCRYPT('PAYPAL_CREDITOR')__;
UPDATE llx_const set name = __ENCRYPT('ONLINE_PAYMENT_CSS_URL')__ where name = __ENCRYPT('PAYPAL_CSS_URL')__;
UPDATE llx_const set name = __ENCRYPT('ONLINE_PAYMENT_NEWFORMTEXT')__ where name = __ENCRYPT('PAYPAL_NEWFORMTEXT')__;
UPDATE llx_const set name = __ENCRYPT('ONLINE_PAYMENT_LOGO')__ where name = __ENCRYPT('PAYPAL_LOGO')__;
ALTER TABLE llx_accounting_system ADD COLUMN fk_country integer;
UPDATE llx_accounting_account SET pcg_type = 'INCOME' where pcg_type = 'PROD';
UPDATE llx_accounting_account SET pcg_type = 'EXPENSE' where pcg_type = 'CHARGE';
UPDATE llx_accounting_account SET pcg_type = 'INCOME' where pcg_type = 'VENTAS_E_INGRESOS';
UPDATE llx_accounting_account SET pcg_type = 'EXPENSE' where pcg_type = 'COMPRAS_GASTOS';
ALTER TABLE llx_c_action_trigger MODIFY COLUMN elementtype varchar(24) NOT NULL;
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('CONTRACT_SENTBYMAIL','Contract sent by mail','Executed when a contract is sent by mail','contrat',18);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('PROPOSAL_SUPPLIER_VALIDATE','Price request validated','Executed when a commercial proposal is validated','proposal_supplier',10);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('PROPOSAL_SUPPLIER_SENTBYMAIL','Price request sent by mail','Executed when a commercial proposal is sent by mail','proposal_supplier',10);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('PROPOSAL_SUPPLIER_CLOSE_SIGNED','Price request closed signed','Executed when a customer proposal is closed signed','proposal_supplier',10);
insert into llx_c_action_trigger (code,label,description,elementtype,rang) values ('PROPOSAL_SUPPLIER_CLOSE_REFUSED','Price request closed refused','Executed when a customer proposal is closed refused','proposal_supplier',10);
DROP TABLE llx_projet_task_comment;
CREATE TABLE llx_comment (
rowid integer AUTO_INCREMENT PRIMARY KEY,
datec datetime DEFAULT NULL,
tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
description text NOT NULL,
fk_user_author integer DEFAULT NULL,
fk_element integer DEFAULT NULL,
element_type varchar(50) DEFAULT NULL,
entity integer DEFAULT 1,
import_key varchar(125) DEFAULT NULL
)ENGINE=innodb;
DELETE FROM llx_const where name = __ENCRYPT('MAIN_SHOW_WORKBOARD')__;
-- Adherent - Update old constants
UPDATE llx_const SET value = REPLACE(value, '%', '__') WHERE name LIKE 'ADHERENT%';
-- Accountancy - Remove old constants
DELETE FROM llx_const WHERE name = __ENCRYPT('ACCOUNTING_SELL_JOURNAL')__;
DELETE FROM llx_const WHERE name = __ENCRYPT('ACCOUNTING_PURCHASE_JOURNAL')__;
DELETE FROM llx_const WHERE name = __ENCRYPT('ACCOUNTING_SOCIAL_JOURNAL')__;
DELETE FROM llx_const WHERE name = __ENCRYPT('ACCOUNTING_MISCELLANEOUS_JOURNAL')__;
DELETE FROM llx_const WHERE name = __ENCRYPT('ACCOUNTING_GROUPBYACCOUNT')__;
DELETE FROM llx_const WHERE name = __ENCRYPT('ACCOUNTING_EXPORT_GLOBAL_ACCOUNT')__;
DELETE FROM llx_const WHERE name = __ENCRYPT('ACCOUNTING_EXPORT_LABEL')__;
DELETE FROM llx_const WHERE name = __ENCRYPT('ACCOUNTING_EXPORT_AMOUNT')__;
DELETE FROM llx_const WHERE name = __ENCRYPT('ACCOUNTING_EXPORT_DEVISE')__;
DELETE FROM llx_const WHERE name = __ENCRYPT('ACCOUNTING_EXPORT_PIECE')__;
DELETE FROM llx_const WHERE name = __ENCRYPT('ACCOUNTING_EXPENSEREPORT_JOURNAL')__;
-- VMYSQL4.1 ALTER TABLE llx_c_paiement DROP PRIMARY KEY;
ALTER TABLE llx_c_paiement ADD COLUMN entity integer DEFAULT 1 NOT NULL AFTER id;
ALTER TABLE llx_c_paiement DROP INDEX uk_c_paiement;
ALTER TABLE llx_c_paiement ADD UNIQUE INDEX uk_c_paiement(id, entity, code);
-- VMYSQL4.1 ALTER TABLE llx_c_payment_term DROP PRIMARY KEY;
ALTER TABLE llx_c_payment_term ADD COLUMN entity integer DEFAULT 1 NOT NULL AFTER rowid;
ALTER TABLE llx_c_payment_term ADD UNIQUE INDEX uk_c_payment_term(rowid, entity, code);
ALTER TABLE llx_projet CHANGE datec datec datetime;
create table llx_c_email_senderprofile
(
rowid integer AUTO_INCREMENT PRIMARY KEY,
entity integer DEFAULT 1 NOT NULL, -- multi company id
private smallint DEFAULT 0 NOT NULL, -- Template public or private
date_creation datetime,
tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
label varchar(255), -- Label of predefined email
email varchar(255), -- Email
signature text, -- Predefined signature
position smallint, -- Position
active tinyint DEFAULT 1 NOT NULL
)ENGINE=innodb;
ALTER TABLE llx_c_email_senderprofile ADD UNIQUE INDEX uk_c_email_senderprofile(entity, label, email);
-- Add new chart of account entries
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 67,'PC-MIPYME', 'The PYME accountancy Chile plan', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 7,'ENG-BASE', 'England plan', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 49,'SYSCOHADA-BJ', 'Plan comptable Ouest-Africain', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 60,'SYSCOHADA-BF', 'Plan comptable Ouest-Africain', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 24,'SYSCOHADA-CM', 'Plan comptable Ouest-Africain', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 65,'SYSCOHADA-CF', 'Plan comptable Ouest-Africain', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 71,'SYSCOHADA-KM', 'Plan comptable Ouest-Africain', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 72,'SYSCOHADA-CG', 'Plan comptable Ouest-Africain', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 21,'SYSCOHADA-CI', 'Plan comptable Ouest-Africain', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 16,'SYSCOHADA-GA', 'Plan comptable Ouest-Africain', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 87,'SYSCOHADA-GQ', 'Plan comptable Ouest-Africain', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES (147,'SYSCOHADA-ML', 'Plan comptable Ouest-Africain', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES (168,'SYSCOHADA-NE', 'Plan comptable Ouest-Africain', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 73,'SYSCOHADA-CD', 'Plan comptable Ouest-Africain', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 22,'SYSCOHADA-SN', 'Plan comptable Ouest-Africain', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 66,'SYSCOHADA-TD', 'Plan comptable Ouest-Africain', 1);
INSERT INTO llx_accounting_system (fk_country, pcg_version, label, active) VALUES ( 15,'SYSCOHADA-TG', 'Plan comptable Ouest-Africain', 1);
-- Update old chart of account entries
UPDATE llx_accounting_system SET fk_country = 1 WHERE pcg_version = 'PCG99-ABREGE';
UPDATE llx_accounting_system SET fk_country = 1 WHERE pcg_version = 'PCG99-BASE';
UPDATE llx_accounting_system SET fk_country = 1 WHERE pcg_version = 'PCG14-DEV';
UPDATE llx_accounting_system SET fk_country = 2 WHERE pcg_version = 'PCMN-BASE';
UPDATE llx_accounting_system SET fk_country = 4 WHERE pcg_version = 'PCG08-PYME';
UPDATE llx_accounting_system SET fk_country = 10 WHERE pcg_version = 'PCT';
UPDATE llx_accounting_system SET fk_country = 80 WHERE pcg_version = 'DK-STD';
UPDATE llx_accounting_system SET fk_country = 67 WHERE pcg_version = 'PC-MIPYME';
UPDATE llx_accounting_system SET fk_country = 6 WHERE pcg_version = 'PCG_SUISSE';
UPDATE llx_accounting_system SET fk_country =140 WHERE pcg_version = 'PCN-LUXEMBURG';
UPDATE llx_accounting_system SET fk_country = 12 WHERE pcg_version = 'PCG';
CREATE TABLE llx_actioncomm_reminder(
-- BEGIN MODULEBUILDER FIELDS
rowid integer AUTO_INCREMENT PRIMARY KEY NOT NULL,
dateremind datetime NOT NULL,
typeremind varchar(32) NOT NULL,
fk_user integer NOT NULL,
offsetvalue integer NOT NULL,
offsetunit varchar(1) NOT NULL,
status integer NOT NULL DEFAULT 0
-- END MODULEBUILDER FIELDS
) ENGINE=innodb;
ALTER TABLE llx_actioncomm_reminder ADD INDEX idx_actioncomm_reminder_rowid (rowid);
ALTER TABLE llx_actioncomm_reminder ADD INDEX idx_actioncomm_reminder_dateremind (dateremind);
ALTER TABLE llx_actioncomm_reminder ADD INDEX idx_actioncomm_reminder_fk_user (fk_user);
ALTER TABLE llx_actioncomm_reminder ADD UNIQUE INDEX uk_actioncomm_reminder_unique(fk_user, typeremind, offsetvalue, offsetunit);
UPDATE llx_tva SET datec = tms where datec IS NULL;
-- VPGSQL8.2 CREATE SEQUENCE llx_supplier_proposal_rowid_seq;
-- VPGSQL8.2 ALTER TABLE llx_supplier_proposal ALTER COLUMN rowid SET DEFAULT nextval('llx_supplier_proposal_rowid_seq');
-- VPGSQL8.2 ALTER TABLE llx_supplier_proposal ALTER COLUMN rowid SET NOT NULL;
-- VPGSQL8.2 SELECT setval('llx_supplier_proposal_rowid_seq', (SELECT MAX(rowid) FROM llx_supplier_proposal));
-- VPGSQL8.2 CREATE SEQUENCE llx_supplier_proposaldet_rowid_seq;
-- VPGSQL8.2 ALTER TABLE llx_supplier_proposaldet ALTER COLUMN rowid SET DEFAULT nextval('llx_supplier_proposaldet_rowid_seq');
-- VPGSQL8.2 ALTER TABLE llx_supplier_proposaldet ALTER COLUMN rowid SET NOT NULL;
-- VPGSQL8.2 SELECT setval('llx_supplier_proposaldet_rowid_seq', (SELECT MAX(rowid) FROM llx_supplier_proposaldet));
create table llx_onlinesignature
(
rowid integer AUTO_INCREMENT PRIMARY KEY,
entity integer DEFAULT 1 NOT NULL,
object_type varchar(32) NOT NULL,
object_id integer NOT NULL,
datec datetime NOT NULL,
tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
name varchar(255) NOT NULL,
ip varchar(128),
pathoffile varchar(255)
)ENGINE=innodb;
-- May have error due to duplicate keys
ALTER TABLE llx_resource ADD UNIQUE INDEX uk_resource_ref (ref, entity);
ALTER TABLE llx_facture_rec ADD COLUMN modelpdf varchar(255) AFTER note_public;
ALTER TABLE llx_facture_rec ADD COLUMN generate_pdf integer DEFAULT 1 AFTER auto_validate;
ALTER TABLE llx_blockedlog ADD COLUMN date_creation datetime;
ALTER TABLE llx_blockedlog ADD COLUMN user_fullname varchar(255);
ALTER TABLE llx_blockedlog MODIFY COLUMN ref_object varchar(255);
-- SPEC : use database type 'double' to store monetary values
ALTER TABLE llx_blockedlog MODIFY COLUMN amounts double(24,8) NOT NULL;
ALTER TABLE llx_chargesociales MODIFY COLUMN amount double(24,8);
ALTER TABLE llx_commande MODIFY COLUMN amount_ht double(24,8) default 0;
ALTER TABLE llx_commande_fournisseur MODIFY COLUMN amount_ht double(24,8);
ALTER TABLE llx_don MODIFY COLUMN amount double(24,8);
ALTER TABLE llx_expensereport_rules MODIFY COLUMN amount double(24,8);
ALTER TABLE llx_loan MODIFY COLUMN capital double(24,8);
ALTER TABLE llx_loan MODIFY COLUMN capital_position double(24,8);
ALTER TABLE llx_loan_schedule MODIFY COLUMN amount_capital double(24,8);
ALTER TABLE llx_loan_schedule MODIFY COLUMN amount_insurance double(24,8);
ALTER TABLE llx_loan_schedule MODIFY COLUMN amount_interest double(24,8);
ALTER TABLE llx_paiementcharge MODIFY COLUMN amount double(24,8);
ALTER TABLE llx_paiementfourn MODIFY COLUMN amount double(24,8);
ALTER TABLE llx_payment_donation MODIFY COLUMN amount double(24,8);
ALTER TABLE llx_payment_expensereport MODIFY COLUMN amount double(24,8);
ALTER TABLE llx_payment_loan MODIFY COLUMN amount_capital double(24,8);
ALTER TABLE llx_payment_loan MODIFY COLUMN amount_insurance double(24,8);
ALTER TABLE llx_payment_loan MODIFY COLUMN amount_interest double(24,8);
ALTER TABLE llx_payment_salary MODIFY COLUMN salary double(24,8);
ALTER TABLE llx_payment_salary MODIFY COLUMN amount double(24,8);
ALTER TABLE llx_prelevement_bons MODIFY COLUMN amount double(24,8);
ALTER TABLE llx_prelevement_facture_demande MODIFY COLUMN amount double(24,8);
ALTER TABLE llx_prelevement_lignes MODIFY COLUMN amount double(24,8);
ALTER TABLE llx_societe MODIFY COLUMN capital double(24,8);
ALTER TABLE llx_tva MODIFY COLUMN amount double(24,8);
ALTER TABLE llx_subscription MODIFY COLUMN subscription double(24,8);
ALTER TABLE llx_resource ADD fk_country integer DEFAULT NULL;
ALTER TABLE llx_resource ADD INDEX idx_resource_fk_country (fk_country);
ALTER TABLE llx_resource ADD CONSTRAINT fk_resource_fk_country FOREIGN KEY (fk_country) REFERENCES llx_c_country (rowid);
create table llx_facture_rec_extrafields
(
rowid integer AUTO_INCREMENT PRIMARY KEY,
tms timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
fk_object integer NOT NULL,
import_key varchar(14) -- import key
) ENGINE=innodb;
ALTER TABLE llx_facture_rec_extrafields ADD INDEX idx_facture_rec_extrafields (fk_object);
-- VMYSQL4.1 ALTER TABLE llx_product_association ADD COLUMN rowid integer AUTO_INCREMENT PRIMARY KEY;
-- drop very old table (bad name)
DROP TABLE llx_c_accountancy_category;
UPDATE llx_cronjob set entity = 1 where entity = 0 and label in ('RecurringInvoices', 'SendEmailsReminders');
UPDATE llx_cronjob set entity = 0 where entity = 1 and label in ('PurgeDeleteTemporaryFilesShort', 'MakeLocalDatabaseDumpShort');
-- VMYSQL4.3 ALTER TABLE llx_c_shipment_mode MODIFY COLUMN tracking varchar(255) NULL;
-- VPGSQL8.2 ALTER TABLE llx_c_shipment_mode ALTER COLUMN tracking DROP NOT NULL;
ALTER TABLE llx_paiementfourn ADD COLUMN fk_user_modif integer AFTER fk_user_author;
Reference in New Issue View Git Blame Copy Permalink