-
-
Notifications
You must be signed in to change notification settings - Fork 2.6k
/
4.0.0-5.0.0.sql
178 lines (130 loc) · 8.71 KB
/
4.0.0-5.0.0.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
--
-- Be carefull to requests order.
-- This file must be loaded by calling /install/index.php page
-- when current version is 4.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);
-- VPGSQL8.2 ALTER TABLE llx_product_lot ALTER COLUMN entity SET DEFAULT 1;
ALTER TABLE llx_product_lot MODIFY COLUMN entity integer DEFAULT 1;
UPDATE llx_product_lot SET entity = 1 WHERE entity IS NULL;
ALTER TABLE llx_facturedet ADD INDEX idx_facturedet_fk_code_ventilation (fk_code_ventilation);
ALTER TABLE llx_facture_fourn_det ADD INDEX idx_facture_fourn_det_fk_code_ventilation (fk_code_ventilation);
ALTER TABLE llx_facture_fourn_det ADD INDEX idx_facture_fourn_det_fk_product (fk_product);
ALTER TABLE llx_facture_rec ADD COLUMN fk_user_modif integer;
ALTER TABLE llx_expedition ADD COLUMN fk_user_modif integer;
ALTER TABLE llx_adherent ADD COLUMN model_pdf varchar(255);
ALTER TABLE llx_don ADD COLUMN date_valid datetime;
DELETE FROM llx_menu where module='expensereport';
ALTER TABLE llx_user DROP COLUMN phenix_login;
ALTER TABLE llx_user DROP COLUMN phenix_pass;
ALTER TABLE llx_user ADD COLUMN dateemployment datetime;
ALTER TABLE llx_societe ADD COLUMN fk_account integer;
ALTER TABLE llx_commandedet ADD COLUMN fk_commandefourndet integer DEFAULT NULL after import_key; -- link to detail line of commande fourn (resplenish)
ALTER TABLE llx_commandedet MODIFY COLUMN fk_commandefourndet integer DEFAULT NULL;
ALTER TABLE llx_website ADD COLUMN virtualhost varchar(255) after fk_default_home;
ALTER TABLE llx_chargesociales ADD COLUMN fk_account integer after fk_type;
ALTER TABLE llx_chargesociales ADD COLUMN fk_mode_reglement integer after fk_account;
ALTER TABLE llx_ecm_files ADD COLUMN gen_or_uploaded varchar(12) after cover;
DROP TABLE llx_document_generator;
DROP TABLE llx_ecm_documents;
DROP TABLE llx_holiday_events;
DROP TABLE llx_holiday_types;
ALTER TABLE llx_notify ADD COLUMN type_target varchar(16) NULL;
ALTER TABLE llx_entrepot DROP COLUMN valo_pmp;
ALTER TABLE llx_notify_def MODIFY COLUMN fk_soc integer NULL;
-- VPGSQL8.2 ALTER TABLE llx_notify_def ALTER COLUMN fk_soc SET DEFAULT NULL;
create table llx_categorie_project
(
fk_categorie integer NOT NULL,
fk_project integer NOT NULL,
import_key varchar(14)
)ENGINE=innodb;
ALTER TABLE llx_categorie_project ADD PRIMARY KEY pk_categorie_project (fk_categorie, fk_project);
ALTER TABLE llx_categorie_project ADD INDEX idx_categorie_project_fk_categorie (fk_categorie);
ALTER TABLE llx_categorie_project ADD INDEX idx_categorie_project_fk_project (fk_project);
ALTER TABLE llx_categorie_project ADD CONSTRAINT fk_categorie_project_categorie_rowid FOREIGN KEY (fk_categorie) REFERENCES llx_categorie (rowid);
ALTER TABLE llx_categorie_project ADD CONSTRAINT fk_categorie_project_fk_project_rowid FOREIGN KEY (fk_project) REFERENCES llx_projet (rowid);
ALTER TABLE llx_societe_remise_except ADD COLUMN entity integer DEFAULT 1 NOT NULL after rowid;
ALTER TABLE llx_societe_remise ADD COLUMN entity integer DEFAULT 1 NOT NULL after rowid;
create table llx_expensereport_extrafields
(
rowid integer AUTO_INCREMENT PRIMARY KEY,
tms timestamp,
fk_object integer NOT NULL,
import_key varchar(14) -- import key
) ENGINE=innodb;
ALTER TABLE llx_expensereport_extrafields ADD INDEX idx_expensereport_extrafields (fk_object);
ALTER TABLE llx_cotisation RENAME TO llx_subscription;
ALTER TABLE llx_subscription ADD UNIQUE INDEX uk_subscription (fk_adherent,dateadh);
ALTER TABLE llx_subscription CHANGE COLUMN cotisation subscription real;
ALTER TABLE llx_adherent_type CHANGE COLUMN cotisation subscription varchar(3) NOT NULL DEFAULT 'yes';
CREATE TABLE llx_product_lot_extrafields
(
rowid integer AUTO_INCREMENT PRIMARY KEY,
tms timestamp,
fk_object integer NOT NULL,
import_key varchar(14) -- import key
) ENGINE=innodb;
ALTER TABLE llx_product_lot_extrafields ADD INDEX idx_product_lot_extrafields (fk_object);
ALTER TABLE llx_website_page MODIFY content MEDIUMTEXT;
CREATE TABLE llx_product_warehouse_properties
(
rowid integer AUTO_INCREMENT PRIMARY KEY,
tms timestamp,
fk_product integer NOT NULL,
fk_entrepot integer NOT NULL,
seuil_stock_alerte integer DEFAULT 0,
desiredstock integer DEFAULT 0,
import_key varchar(14) -- Import key
)ENGINE=innodb;
ALTER TABLE llx_accounting_bookkeeping ADD COLUMN entity integer DEFAULT 1 NOT NULL;
ALTER TABLE llx_accounting_bookkeeping ADD COLUMN fk_user_modif integer;
ALTER TABLE llx_accounting_bookkeeping ADD COLUMN date_creation datetime;
ALTER TABLE llx_accounting_bookkeeping ADD COLUMN tms timestamp;
-- VMYSQL4.3 ALTER TABLE llx_accounting_bookkeeping MODIFY COLUMN numero_compte varchar(32) NOT NULL;
-- VMYSQL4.3 ALTER TABLE llx_accounting_bookkeeping MODIFY COLUMN code_journal varchar(32) NOT NULL;
-- VPGSQL8.2 ALTER TABLE llx_accounting_bookkeeping ALTER COLUMN numero_compte SET NOT NULL;
-- VPGSQL8.2 ALTER TABLE llx_accounting_bookkeeping ALTER COLUMN code_journal SET NOT NULL;
ALTER TABLE llx_accounting_account ADD UNIQUE INDEX uk_accounting_account (account_number, entity, fk_pcg_version);
ALTER TABLE llx_expensereport_det ADD COLUMN fk_code_ventilation integer DEFAULT 0;
ALTER TABLE llx_c_payment_term change fdm type_cdr tinyint;
ALTER TABLE llx_facturedet ADD COLUMN vat_src_code varchar(10) DEFAULT '' AFTER tva_tx;
ALTER TABLE llx_facture_fourn_det ADD COLUMN vat_src_code varchar(10) DEFAULT '' AFTER tva_tx;
ALTER TABLE llx_commandedet ADD COLUMN vat_src_code varchar(10) DEFAULT '' AFTER tva_tx;
ALTER TABLE llx_commande_fournisseurdet ADD COLUMN vat_src_code varchar(10) DEFAULT '' AFTER tva_tx;
ALTER TABLE llx_propaldet ADD COLUMN vat_src_code varchar(10) DEFAULT '' AFTER tva_tx;
ALTER TABLE llx_supplier_proposaldet ADD COLUMN vat_src_code varchar(10) DEFAULT '' AFTER tva_tx;
ALTER TABLE llx_c_payment_term change fdm type_cdr tinyint;
ALTER TABLE llx_entrepot ADD COLUMN fk_parent integer DEFAULT 0;
create table llx_resource_extrafields
(
rowid integer AUTO_INCREMENT PRIMARY KEY,
tms timestamp,
fk_object integer NOT NULL,
import_key varchar(14) -- import key
) ENGINE=innodb;
ALTER TABLE llx_resource_extrafields ADD INDEX idx_resource_extrafields (fk_object);
INSERT INTO llx_const (name, value, type, note, visible) values ('MAIN_SIZE_SHORTLIST_LIMIT','3','chaine','Max length for small lists (tabs)',0);
ALTER TABLE llx_bank_account ADD COLUMN note_public text;
ALTER TABLE llx_bank_account ADD COLUMN model_pdf varchar(255);
ALTER TABLE llx_bank_account ADD COLUMN import_key varchar(14);
ALTER TABLE llx_overwrite_trans ADD COLUMN entity integer DEFAULT 1 NOT NULL AFTER rowid;