1 /*
2 ** Zabbix
3 ** Copyright (C) 2001-2021 Zabbix SIA
4 **
5 ** This program is free software; you can redistribute it and/or modify
6 ** it under the terms of the GNU General Public License as published by
7 ** the Free Software Foundation; either version 2 of the License, or
8 ** (at your option) any later version.
9 **
10 ** This program is distributed in the hope that it will be useful,
11 ** but WITHOUT ANY WARRANTY; without even the implied warranty of
12 ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 ** GNU General Public License for more details.
14 **
15 ** You should have received a copy of the GNU General Public License
16 ** along with this program; if not, write to the Free Software
17 ** Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
18 **/
19
20 #include "common.h"
21
22 #include "db.h"
23 #include "log.h"
24 #include "sysinfo.h"
25 #include "zbxdbupgrade.h"
26 #include "dbupgrade.h"
27
28 typedef struct
29 {
30 zbx_dbpatch_t *patches;
31 const char *description;
32 }
33 zbx_db_version_t;
34
35 #ifdef HAVE_MYSQL
36 # define ZBX_DB_TABLE_OPTIONS " engine=innodb"
37 # define ZBX_DROP_FK " drop foreign key"
38 #else
39 # define ZBX_DB_TABLE_OPTIONS ""
40 # define ZBX_DROP_FK " drop constraint"
41 #endif
42
43 #if defined(HAVE_IBM_DB2)
44 # define ZBX_DB_ALTER_COLUMN " alter column"
45 #elif defined(HAVE_POSTGRESQL)
46 # define ZBX_DB_ALTER_COLUMN " alter"
47 #else
48 # define ZBX_DB_ALTER_COLUMN " modify"
49 #endif
50
51 #if defined(HAVE_IBM_DB2)
52 # define ZBX_DB_SET_TYPE " set data type"
53 #elif defined(HAVE_POSTGRESQL)
54 # define ZBX_DB_SET_TYPE " type"
55 #else
56 # define ZBX_DB_SET_TYPE ""
57 #endif
58
59 /* NOTE: Do not forget to sync changes in ZBX_TYPE_*_STR defines for Oracle with zbx_oracle_column_type()! */
60
61 #if defined(HAVE_IBM_DB2) || defined(HAVE_POSTGRESQL)
62 # define ZBX_TYPE_ID_STR "bigint"
63 #elif defined(HAVE_MYSQL)
64 # define ZBX_TYPE_ID_STR "bigint unsigned"
65 #elif defined(HAVE_ORACLE)
66 # define ZBX_TYPE_ID_STR "number(20)"
67 #endif
68
69 #ifdef HAVE_ORACLE
70 # define ZBX_TYPE_INT_STR "number(10)"
71 # define ZBX_TYPE_CHAR_STR "nvarchar2"
72 #else
73 # define ZBX_TYPE_INT_STR "integer"
74 # define ZBX_TYPE_CHAR_STR "varchar"
75 #endif
76
77 #if defined(HAVE_IBM_DB2)
78 # define ZBX_TYPE_FLOAT_STR "decfloat(16)"
79 # define ZBX_TYPE_UINT_STR "bigint"
80 #elif defined(HAVE_MYSQL)
81 # define ZBX_TYPE_FLOAT_STR "double(16,4)"
82 # define ZBX_TYPE_UINT_STR "bigint unsigned"
83 #elif defined(HAVE_ORACLE)
84 # define ZBX_TYPE_FLOAT_STR "number(20,4)"
85 # define ZBX_TYPE_UINT_STR "number(20)"
86 #elif defined(HAVE_POSTGRESQL)
87 # define ZBX_TYPE_FLOAT_STR "numeric(16,4)"
88 # define ZBX_TYPE_UINT_STR "numeric(20)"
89 #endif
90
91 #if defined(HAVE_IBM_DB2)
92 # define ZBX_TYPE_SHORTTEXT_STR "varchar(2048)"
93 #elif defined(HAVE_ORACLE)
94 # define ZBX_TYPE_SHORTTEXT_STR "nvarchar2(2048)"
95 #else
96 # define ZBX_TYPE_SHORTTEXT_STR "text"
97 #endif
98
99 #if defined(HAVE_IBM_DB2)
100 # define ZBX_TYPE_TEXT_STR "varchar(2048)"
101 #elif defined(HAVE_ORACLE)
102 # define ZBX_TYPE_TEXT_STR "nclob"
103 #else
104 # define ZBX_TYPE_TEXT_STR "text"
105 #endif
106
107 #define ZBX_FIRST_DB_VERSION 2010000
108
109 extern unsigned char program_type;
110
111
112 #ifndef HAVE_SQLITE3
DBfield_type_string(char ** sql,size_t * sql_alloc,size_t * sql_offset,const ZBX_FIELD * field)113 static void DBfield_type_string(char **sql, size_t *sql_alloc, size_t *sql_offset, const ZBX_FIELD *field)
114 {
115 switch (field->type)
116 {
117 case ZBX_TYPE_ID:
118 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ZBX_TYPE_ID_STR);
119 break;
120 case ZBX_TYPE_INT:
121 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ZBX_TYPE_INT_STR);
122 break;
123 case ZBX_TYPE_CHAR:
124 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "%s(%hu)", ZBX_TYPE_CHAR_STR, field->length);
125 break;
126 case ZBX_TYPE_FLOAT:
127 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ZBX_TYPE_FLOAT_STR);
128 break;
129 case ZBX_TYPE_UINT:
130 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ZBX_TYPE_UINT_STR);
131 break;
132 case ZBX_TYPE_SHORTTEXT:
133 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ZBX_TYPE_SHORTTEXT_STR);
134 break;
135 case ZBX_TYPE_TEXT:
136 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ZBX_TYPE_TEXT_STR);
137 break;
138 default:
139 assert(0);
140 }
141 }
142
143 #ifdef HAVE_ORACLE
144 typedef enum
145 {
146 ZBX_ORACLE_COLUMN_TYPE_NUMERIC,
147 ZBX_ORACLE_COLUMN_TYPE_CHARACTER,
148 ZBX_ORACLE_COLUMN_TYPE_UNKNOWN
149 }
150 zbx_oracle_column_type_t;
151
152 /******************************************************************************
153 * *
154 * Function: zbx_oracle_column_type *
155 * *
156 * Purpose: determine whether column type is character or numeric *
157 * *
158 * Parameters: field_type - [IN] column type in Zabbix definitions *
159 * *
160 * Return value: column type (character/raw, numeric) in Oracle definitions *
161 * *
162 * Comments: The size of a character or raw column or the precision of a *
163 * numeric column can be changed, whether or not all the rows *
164 * contain nulls. Otherwise in order to change the datatype of a *
165 * column all rows of the column must contain nulls. *
166 * *
167 ******************************************************************************/
zbx_oracle_column_type(unsigned char field_type)168 static zbx_oracle_column_type_t zbx_oracle_column_type(unsigned char field_type)
169 {
170 switch (field_type)
171 {
172 case ZBX_TYPE_ID:
173 case ZBX_TYPE_INT:
174 case ZBX_TYPE_FLOAT:
175 case ZBX_TYPE_UINT:
176 return ZBX_ORACLE_COLUMN_TYPE_NUMERIC;
177 case ZBX_TYPE_CHAR:
178 case ZBX_TYPE_SHORTTEXT:
179 case ZBX_TYPE_TEXT:
180 return ZBX_ORACLE_COLUMN_TYPE_CHARACTER;
181 default:
182 THIS_SHOULD_NEVER_HAPPEN;
183 return ZBX_ORACLE_COLUMN_TYPE_UNKNOWN;
184 }
185 }
186 #endif
187
DBfield_definition_string(char ** sql,size_t * sql_alloc,size_t * sql_offset,const ZBX_FIELD * field)188 static void DBfield_definition_string(char **sql, size_t *sql_alloc, size_t *sql_offset, const ZBX_FIELD *field)
189 {
190 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, ZBX_FS_SQL_NAME " ", field->name);
191 DBfield_type_string(sql, sql_alloc, sql_offset, field);
192 if (NULL != field->default_value)
193 {
194 char *default_value_esc;
195
196 #if defined(HAVE_MYSQL)
197 switch (field->type)
198 {
199 case ZBX_TYPE_BLOB:
200 case ZBX_TYPE_TEXT:
201 case ZBX_TYPE_SHORTTEXT:
202 case ZBX_TYPE_LONGTEXT:
203 /* MySQL: BLOB and TEXT columns cannot be assigned a default value */
204 break;
205 default:
206 #endif
207 default_value_esc = DBdyn_escape_string(field->default_value);
208 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, " default '%s'", default_value_esc);
209 zbx_free(default_value_esc);
210 #if defined(HAVE_MYSQL)
211 }
212 #endif
213 }
214
215 if (0 != (field->flags & ZBX_NOTNULL))
216 {
217 #if defined(HAVE_ORACLE)
218 switch (field->type)
219 {
220 case ZBX_TYPE_INT:
221 case ZBX_TYPE_FLOAT:
222 case ZBX_TYPE_BLOB:
223 case ZBX_TYPE_UINT:
224 case ZBX_TYPE_ID:
225 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, " not null");
226 break;
227 default: /* ZBX_TYPE_CHAR, ZBX_TYPE_TEXT, ZBX_TYPE_SHORTTEXT or ZBX_TYPE_LONGTEXT */
228 /* nothing to do */;
229 }
230 #else
231 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, " not null");
232 #endif
233 }
234 }
235
DBcreate_table_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const ZBX_TABLE * table)236 static void DBcreate_table_sql(char **sql, size_t *sql_alloc, size_t *sql_offset, const ZBX_TABLE *table)
237 {
238 int i;
239
240 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "create table %s (\n", table->table);
241
242 for (i = 0; NULL != table->fields[i].name; i++)
243 {
244 if (0 != i)
245 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ",\n");
246 DBfield_definition_string(sql, sql_alloc, sql_offset, &table->fields[i]);
247 }
248 if ('\0' != *table->recid)
249 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, ",\nprimary key (%s)", table->recid);
250
251 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, "\n)" ZBX_DB_TABLE_OPTIONS);
252 }
253
DBrename_table_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,const char * new_name)254 static void DBrename_table_sql(char **sql, size_t *sql_alloc, size_t *sql_offset, const char *table_name,
255 const char *new_name)
256 {
257 #ifdef HAVE_IBM_DB2
258 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "rename table " ZBX_FS_SQL_NAME " to " ZBX_FS_SQL_NAME,
259 table_name, new_name);
260 #else
261 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "alter table " ZBX_FS_SQL_NAME " rename to " ZBX_FS_SQL_NAME,
262 table_name, new_name);
263 #endif
264 }
265
DBdrop_table_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name)266 static void DBdrop_table_sql(char **sql, size_t *sql_alloc, size_t *sql_offset, const char *table_name)
267 {
268 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "drop table %s", table_name);
269 }
270
DBset_default_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,const ZBX_FIELD * field)271 static void DBset_default_sql(char **sql, size_t *sql_alloc, size_t *sql_offset,
272 const char *table_name, const ZBX_FIELD *field)
273 {
274 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "alter table %s" ZBX_DB_ALTER_COLUMN " ", table_name);
275
276 #if defined(HAVE_MYSQL)
277 DBfield_definition_string(sql, sql_alloc, sql_offset, field);
278 #elif defined(HAVE_ORACLE)
279 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "%s default '%s'", field->name, field->default_value);
280 #else
281 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "%s set default '%s'", field->name, field->default_value);
282 #endif
283 }
284
DBmodify_field_type_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,const ZBX_FIELD * field)285 static void DBmodify_field_type_sql(char **sql, size_t *sql_alloc, size_t *sql_offset,
286 const char *table_name, const ZBX_FIELD *field)
287 {
288 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "alter table " ZBX_FS_SQL_NAME ZBX_DB_ALTER_COLUMN " ",
289 table_name);
290
291 #ifdef HAVE_MYSQL
292 DBfield_definition_string(sql, sql_alloc, sql_offset, field);
293 #else
294 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "%s" ZBX_DB_SET_TYPE " ", field->name);
295 DBfield_type_string(sql, sql_alloc, sql_offset, field);
296 #ifdef HAVE_POSTGRESQL
297 if (NULL != field->default_value)
298 {
299 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ";\n");
300 DBset_default_sql(sql, sql_alloc, sql_offset, table_name, field);
301 }
302 #endif
303 #endif
304 }
305
DBdrop_not_null_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,const ZBX_FIELD * field)306 static void DBdrop_not_null_sql(char **sql, size_t *sql_alloc, size_t *sql_offset,
307 const char *table_name, const ZBX_FIELD *field)
308 {
309 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "alter table %s" ZBX_DB_ALTER_COLUMN " ", table_name);
310
311 #if defined(HAVE_MYSQL)
312 DBfield_definition_string(sql, sql_alloc, sql_offset, field);
313 #elif defined(HAVE_ORACLE)
314 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "%s null", field->name);
315 #else
316 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "%s drop not null", field->name);
317 #endif
318 }
319
DBset_not_null_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,const ZBX_FIELD * field)320 static void DBset_not_null_sql(char **sql, size_t *sql_alloc, size_t *sql_offset,
321 const char *table_name, const ZBX_FIELD *field)
322 {
323 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "alter table %s" ZBX_DB_ALTER_COLUMN " ", table_name);
324
325 #if defined(HAVE_MYSQL)
326 DBfield_definition_string(sql, sql_alloc, sql_offset, field);
327 #elif defined(HAVE_ORACLE)
328 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "%s not null", field->name);
329 #else
330 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "%s set not null", field->name);
331 #endif
332 }
333
DBadd_field_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,const ZBX_FIELD * field)334 static void DBadd_field_sql(char **sql, size_t *sql_alloc, size_t *sql_offset,
335 const char *table_name, const ZBX_FIELD *field)
336 {
337 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "alter table " ZBX_FS_SQL_NAME " add ", table_name);
338 DBfield_definition_string(sql, sql_alloc, sql_offset, field);
339 }
340
DBrename_field_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,const char * field_name,const ZBX_FIELD * field)341 static void DBrename_field_sql(char **sql, size_t *sql_alloc, size_t *sql_offset,
342 const char *table_name, const char *field_name, const ZBX_FIELD *field)
343 {
344 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "alter table " ZBX_FS_SQL_NAME " ", table_name);
345
346 #ifdef HAVE_MYSQL
347 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "change column " ZBX_FS_SQL_NAME " ", field_name);
348 DBfield_definition_string(sql, sql_alloc, sql_offset, field);
349 #else
350 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "rename column " ZBX_FS_SQL_NAME " to " ZBX_FS_SQL_NAME,
351 field_name, field->name);
352 #endif
353 }
354
DBdrop_field_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,const char * field_name)355 static void DBdrop_field_sql(char **sql, size_t *sql_alloc, size_t *sql_offset,
356 const char *table_name, const char *field_name)
357 {
358 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "alter table %s drop column %s", table_name, field_name);
359 }
360
DBcreate_index_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,const char * index_name,const char * fields,int unique)361 static void DBcreate_index_sql(char **sql, size_t *sql_alloc, size_t *sql_offset,
362 const char *table_name, const char *index_name, const char *fields, int unique)
363 {
364 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, "create");
365 if (0 != unique)
366 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, " unique");
367 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, " index %s on %s (%s)", index_name, table_name, fields);
368 }
369
DBdrop_index_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,const char * index_name)370 static void DBdrop_index_sql(char **sql, size_t *sql_alloc, size_t *sql_offset,
371 const char *table_name, const char *index_name)
372 {
373 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "drop index %s", index_name);
374 #ifdef HAVE_MYSQL
375 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, " on %s", table_name);
376 #else
377 ZBX_UNUSED(table_name);
378 #endif
379 }
380
DBrename_index_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,const char * old_name,const char * new_name,const char * fields,int unique)381 static void DBrename_index_sql(char **sql, size_t *sql_alloc, size_t *sql_offset, const char *table_name,
382 const char *old_name, const char *new_name, const char *fields, int unique)
383 {
384 #if defined(HAVE_IBM_DB2)
385 ZBX_UNUSED(table_name);
386 ZBX_UNUSED(fields);
387 ZBX_UNUSED(unique);
388 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "rename index %s to %s", old_name, new_name);
389 #elif defined(HAVE_MYSQL)
390 DBcreate_index_sql(sql, sql_alloc, sql_offset, table_name, new_name, fields, unique);
391 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ";\n");
392 DBdrop_index_sql(sql, sql_alloc, sql_offset, table_name, old_name);
393 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ";\n");
394 #elif defined(HAVE_ORACLE) || defined(HAVE_POSTGRESQL)
395 ZBX_UNUSED(table_name);
396 ZBX_UNUSED(fields);
397 ZBX_UNUSED(unique);
398 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "alter index %s rename to %s", old_name, new_name);
399 #endif
400 }
401
DBadd_foreign_key_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,int id,const ZBX_FIELD * field)402 static void DBadd_foreign_key_sql(char **sql, size_t *sql_alloc, size_t *sql_offset,
403 const char *table_name, int id, const ZBX_FIELD *field)
404 {
405 zbx_snprintf_alloc(sql, sql_alloc, sql_offset,
406 "alter table " ZBX_FS_SQL_NAME " add constraint c_%s_%d foreign key (" ZBX_FS_SQL_NAME ")"
407 " references " ZBX_FS_SQL_NAME " (" ZBX_FS_SQL_NAME ")", table_name, table_name,
408 id, field->name, field->fk_table, field->fk_field);
409 if (0 != (field->fk_flags & ZBX_FK_CASCADE_DELETE))
410 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, " on delete cascade");
411 }
412
DBdrop_foreign_key_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,int id)413 static void DBdrop_foreign_key_sql(char **sql, size_t *sql_alloc, size_t *sql_offset,
414 const char *table_name, int id)
415 {
416 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "alter table %s" ZBX_DROP_FK " c_%s_%d",
417 table_name, table_name, id);
418 }
419
DBreorg_table(const char * table_name)420 static int DBreorg_table(const char *table_name)
421 {
422 #ifdef HAVE_IBM_DB2
423 if (ZBX_DB_OK <= DBexecute("call sysproc.admin_cmd ('reorg table %s')", table_name))
424 return SUCCEED;
425
426 return FAIL;
427 #else
428 ZBX_UNUSED(table_name);
429 return SUCCEED;
430 #endif
431 }
432
DBcreate_table(const ZBX_TABLE * table)433 int DBcreate_table(const ZBX_TABLE *table)
434 {
435 char *sql = NULL;
436 size_t sql_alloc = 0, sql_offset = 0;
437 int ret = FAIL;
438
439 DBcreate_table_sql(&sql, &sql_alloc, &sql_offset, table);
440
441 if (ZBX_DB_OK <= DBexecute("%s", sql))
442 ret = SUCCEED;
443
444 zbx_free(sql);
445
446 return ret;
447 }
448
DBrename_table(const char * table_name,const char * new_name)449 int DBrename_table(const char *table_name, const char *new_name)
450 {
451 char *sql = NULL;
452 size_t sql_alloc = 0, sql_offset = 0;
453 int ret = FAIL;
454
455 DBrename_table_sql(&sql, &sql_alloc, &sql_offset, table_name, new_name);
456
457 if (ZBX_DB_OK <= DBexecute("%s", sql))
458 ret = DBreorg_table(new_name);
459
460 zbx_free(sql);
461
462 return ret;
463 }
464
DBdrop_table(const char * table_name)465 int DBdrop_table(const char *table_name)
466 {
467 char *sql = NULL;
468 size_t sql_alloc = 0, sql_offset = 0;
469 int ret = FAIL;
470
471 DBdrop_table_sql(&sql, &sql_alloc, &sql_offset, table_name);
472
473 if (ZBX_DB_OK <= DBexecute("%s", sql))
474 ret = SUCCEED;
475
476 zbx_free(sql);
477
478 return ret;
479 }
480
DBadd_field(const char * table_name,const ZBX_FIELD * field)481 int DBadd_field(const char *table_name, const ZBX_FIELD *field)
482 {
483 char *sql = NULL;
484 size_t sql_alloc = 0, sql_offset = 0;
485 int ret = FAIL;
486
487 DBadd_field_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
488
489 if (ZBX_DB_OK <= DBexecute("%s", sql))
490 ret = DBreorg_table(table_name);
491
492 zbx_free(sql);
493
494 return ret;
495 }
496
DBrename_field(const char * table_name,const char * field_name,const ZBX_FIELD * field)497 int DBrename_field(const char *table_name, const char *field_name, const ZBX_FIELD *field)
498 {
499 char *sql = NULL;
500 size_t sql_alloc = 0, sql_offset = 0;
501 int ret = FAIL;
502
503 DBrename_field_sql(&sql, &sql_alloc, &sql_offset, table_name, field_name, field);
504
505 if (ZBX_DB_OK <= DBexecute("%s", sql))
506 ret = DBreorg_table(table_name);
507
508 zbx_free(sql);
509
510 return ret;
511 }
512
513 #ifdef HAVE_ORACLE
DBmodify_field_type_with_copy(const char * table_name,const ZBX_FIELD * field)514 static int DBmodify_field_type_with_copy(const char *table_name, const ZBX_FIELD *field)
515 {
516 #define ZBX_OLD_FIELD "zbx_old_tmp"
517
518 char *sql = NULL;
519 size_t sql_alloc = 0, sql_offset = 0;
520 int ret = FAIL;
521
522 zbx_snprintf_alloc(&sql, &sql_alloc, &sql_offset, "alter table %s rename column %s to " ZBX_OLD_FIELD,
523 table_name, field->name);
524
525 if (ZBX_DB_OK > DBexecute("%s", sql))
526 goto out;
527
528 if (ZBX_DB_OK > DBadd_field(table_name, field))
529 goto out;
530
531 sql_offset = 0;
532 zbx_snprintf_alloc(&sql, &sql_alloc, &sql_offset, "update %s set %s=" ZBX_OLD_FIELD, table_name,
533 field->name);
534
535 if (ZBX_DB_OK > DBexecute("%s", sql))
536 goto out;
537
538 ret = DBdrop_field(table_name, ZBX_OLD_FIELD);
539 out:
540 zbx_free(sql);
541
542 return ret;
543
544 #undef ZBX_OLD_FIELD
545 }
546 #endif
547
DBmodify_field_type(const char * table_name,const ZBX_FIELD * field,const ZBX_FIELD * old_field)548 int DBmodify_field_type(const char *table_name, const ZBX_FIELD *field, const ZBX_FIELD *old_field)
549 {
550 char *sql = NULL;
551 size_t sql_alloc = 0, sql_offset = 0;
552 int ret = FAIL;
553
554 #ifndef HAVE_ORACLE
555 ZBX_UNUSED(old_field);
556 #else
557 /* Oracle cannot change column type in a general case if column contents are not null. Conversions like */
558 /* number -> nvarchar2 need special processing. New column is created with desired datatype and data from */
559 /* old column is copied there. Then old column is dropped. This method does not preserve column order. */
560 /* NOTE: Existing column indexes and constraints are not respected by the current implementation! */
561
562 if (NULL != old_field && zbx_oracle_column_type(old_field->type) != zbx_oracle_column_type(field->type))
563 return DBmodify_field_type_with_copy(table_name, field);
564 #endif
565 DBmodify_field_type_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
566
567 if (ZBX_DB_OK <= DBexecute("%s", sql))
568 ret = DBreorg_table(table_name);
569
570 zbx_free(sql);
571
572 return ret;
573 }
574
DBset_not_null(const char * table_name,const ZBX_FIELD * field)575 int DBset_not_null(const char *table_name, const ZBX_FIELD *field)
576 {
577 char *sql = NULL;
578 size_t sql_alloc = 0, sql_offset = 0;
579 int ret = FAIL;
580
581 DBset_not_null_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
582
583 if (ZBX_DB_OK <= DBexecute("%s", sql))
584 ret = DBreorg_table(table_name);
585
586 zbx_free(sql);
587
588 return ret;
589 }
590
DBset_default(const char * table_name,const ZBX_FIELD * field)591 int DBset_default(const char *table_name, const ZBX_FIELD *field)
592 {
593 char *sql = NULL;
594 size_t sql_alloc = 0, sql_offset = 0;
595 int ret = FAIL;
596
597 DBset_default_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
598
599 if (ZBX_DB_OK <= DBexecute("%s", sql))
600 ret = DBreorg_table(table_name);
601
602 zbx_free(sql);
603
604 return ret;
605 }
606
DBdrop_not_null(const char * table_name,const ZBX_FIELD * field)607 int DBdrop_not_null(const char *table_name, const ZBX_FIELD *field)
608 {
609 char *sql = NULL;
610 size_t sql_alloc = 0, sql_offset = 0;
611 int ret = FAIL;
612
613 DBdrop_not_null_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
614
615 if (ZBX_DB_OK <= DBexecute("%s", sql))
616 ret = DBreorg_table(table_name);
617
618 zbx_free(sql);
619
620 return ret;
621 }
622
DBdrop_field(const char * table_name,const char * field_name)623 int DBdrop_field(const char *table_name, const char *field_name)
624 {
625 char *sql = NULL;
626 size_t sql_alloc = 0, sql_offset = 0;
627 int ret = FAIL;
628
629 DBdrop_field_sql(&sql, &sql_alloc, &sql_offset, table_name, field_name);
630
631 if (ZBX_DB_OK <= DBexecute("%s", sql))
632 ret = DBreorg_table(table_name);
633
634 zbx_free(sql);
635
636 return ret;
637 }
638
DBcreate_index(const char * table_name,const char * index_name,const char * fields,int unique)639 int DBcreate_index(const char *table_name, const char *index_name, const char *fields, int unique)
640 {
641 char *sql = NULL;
642 size_t sql_alloc = 0, sql_offset = 0;
643 int ret = FAIL;
644
645 DBcreate_index_sql(&sql, &sql_alloc, &sql_offset, table_name, index_name, fields, unique);
646
647 if (ZBX_DB_OK <= DBexecute("%s", sql))
648 ret = SUCCEED;
649
650 zbx_free(sql);
651
652 return ret;
653 }
654
DBdrop_index(const char * table_name,const char * index_name)655 int DBdrop_index(const char *table_name, const char *index_name)
656 {
657 char *sql = NULL;
658 size_t sql_alloc = 0, sql_offset = 0;
659 int ret = FAIL;
660
661 DBdrop_index_sql(&sql, &sql_alloc, &sql_offset, table_name, index_name);
662
663 if (ZBX_DB_OK <= DBexecute("%s", sql))
664 ret = SUCCEED;
665
666 zbx_free(sql);
667
668 return ret;
669 }
670
DBrename_index(const char * table_name,const char * old_name,const char * new_name,const char * fields,int unique)671 int DBrename_index(const char *table_name, const char *old_name, const char *new_name, const char *fields,
672 int unique)
673 {
674 char *sql = NULL;
675 size_t sql_alloc = 0, sql_offset = 0;
676 int ret = FAIL;
677
678 DBrename_index_sql(&sql, &sql_alloc, &sql_offset, table_name, old_name, new_name, fields, unique);
679
680 if (ZBX_DB_OK <= DBexecute("%s", sql))
681 ret = SUCCEED;
682
683 zbx_free(sql);
684
685 return ret;
686 }
687
DBadd_foreign_key(const char * table_name,int id,const ZBX_FIELD * field)688 int DBadd_foreign_key(const char *table_name, int id, const ZBX_FIELD *field)
689 {
690 char *sql = NULL;
691 size_t sql_alloc = 0, sql_offset = 0;
692 int ret = FAIL;
693
694 DBadd_foreign_key_sql(&sql, &sql_alloc, &sql_offset, table_name, id, field);
695
696 if (ZBX_DB_OK <= DBexecute("%s", sql))
697 ret = SUCCEED;
698
699 zbx_free(sql);
700
701 return ret;
702 }
703
DBdrop_foreign_key(const char * table_name,int id)704 int DBdrop_foreign_key(const char *table_name, int id)
705 {
706 char *sql = NULL;
707 size_t sql_alloc = 0, sql_offset = 0;
708 int ret = FAIL;
709
710 DBdrop_foreign_key_sql(&sql, &sql_alloc, &sql_offset, table_name, id);
711
712 if (ZBX_DB_OK <= DBexecute("%s", sql))
713 ret = SUCCEED;
714
715 zbx_free(sql);
716
717 return ret;
718 }
719
DBcreate_dbversion_table(void)720 static int DBcreate_dbversion_table(void)
721 {
722 const ZBX_TABLE table =
723 {"dbversion", "", 0,
724 {
725 {"mandatory", "0", NULL, NULL, 0, ZBX_TYPE_INT, ZBX_NOTNULL, 0},
726 {"optional", "0", NULL, NULL, 0, ZBX_TYPE_INT, ZBX_NOTNULL, 0},
727 {NULL}
728 },
729 NULL
730 };
731 int ret;
732
733 DBbegin();
734 if (SUCCEED == (ret = DBcreate_table(&table)))
735 {
736 if (ZBX_DB_OK > DBexecute("insert into dbversion (mandatory,optional) values (%d,%d)",
737 ZBX_FIRST_DB_VERSION, ZBX_FIRST_DB_VERSION))
738 {
739 ret = FAIL;
740 }
741 }
742
743 return DBend(ret);
744 }
745
DBset_version(int version,unsigned char mandatory)746 static int DBset_version(int version, unsigned char mandatory)
747 {
748 char sql[64];
749 size_t offset;
750
751 offset = zbx_snprintf(sql, sizeof(sql), "update dbversion set ");
752 if (0 != mandatory)
753 offset += zbx_snprintf(sql + offset, sizeof(sql) - offset, "mandatory=%d,", version);
754 zbx_snprintf(sql + offset, sizeof(sql) - offset, "optional=%d", version);
755
756 if (ZBX_DB_OK <= DBexecute("%s", sql))
757 return SUCCEED;
758
759 return FAIL;
760 }
761
762 #endif /* not HAVE_SQLITE3 */
763
764 extern zbx_dbpatch_t DBPATCH_VERSION(2010)[];
765 extern zbx_dbpatch_t DBPATCH_VERSION(2020)[];
766 extern zbx_dbpatch_t DBPATCH_VERSION(2030)[];
767 extern zbx_dbpatch_t DBPATCH_VERSION(2040)[];
768 extern zbx_dbpatch_t DBPATCH_VERSION(2050)[];
769 extern zbx_dbpatch_t DBPATCH_VERSION(3000)[];
770 extern zbx_dbpatch_t DBPATCH_VERSION(3010)[];
771 extern zbx_dbpatch_t DBPATCH_VERSION(3020)[];
772 extern zbx_dbpatch_t DBPATCH_VERSION(3030)[];
773 extern zbx_dbpatch_t DBPATCH_VERSION(3040)[];
774 extern zbx_dbpatch_t DBPATCH_VERSION(3050)[];
775 extern zbx_dbpatch_t DBPATCH_VERSION(4000)[];
776
777 static zbx_db_version_t dbversions[] = {
778 {DBPATCH_VERSION(2010), "2.2 development"},
779 {DBPATCH_VERSION(2020), "2.2 maintenance"},
780 {DBPATCH_VERSION(2030), "2.4 development"},
781 {DBPATCH_VERSION(2040), "2.4 maintenance"},
782 {DBPATCH_VERSION(2050), "3.0 development"},
783 {DBPATCH_VERSION(3000), "3.0 maintenance"},
784 {DBPATCH_VERSION(3010), "3.2 development"},
785 {DBPATCH_VERSION(3020), "3.2 maintenance"},
786 {DBPATCH_VERSION(3030), "3.4 development"},
787 {DBPATCH_VERSION(3040), "3.4 maintenance"},
788 {DBPATCH_VERSION(3050), "4.0 development"},
789 {DBPATCH_VERSION(4000), "4.0 maintenance"},
790 {NULL}
791 };
792
DBget_version(int * mandatory,int * optional)793 static void DBget_version(int *mandatory, int *optional)
794 {
795 DB_RESULT result;
796 DB_ROW row;
797
798 *mandatory = -1;
799 *optional = -1;
800
801 result = DBselect("select mandatory,optional from dbversion");
802
803 if (NULL != (row = DBfetch(result)))
804 {
805 *mandatory = atoi(row[0]);
806 *optional = atoi(row[1]);
807 }
808 DBfree_result(result);
809
810 if (-1 == *mandatory)
811 {
812 zabbix_log(LOG_LEVEL_CRIT, "Cannot get the database version. Exiting ...");
813 exit(EXIT_FAILURE);
814 }
815 }
816
DBcheck_version(void)817 int DBcheck_version(void)
818 {
819 const char *__function_name = "DBcheck_version";
820 const char *dbversion_table_name = "dbversion";
821 int db_mandatory, db_optional, required, ret = FAIL, i;
822 zbx_db_version_t *dbversion;
823 zbx_dbpatch_t *patches;
824
825 #ifndef HAVE_SQLITE3
826 int total = 0, current = 0, completed, last_completed = -1, optional_num = 0;
827 #endif
828 zabbix_log(LOG_LEVEL_DEBUG, "In %s()", __function_name);
829
830 required = ZBX_FIRST_DB_VERSION;
831
832 /* find out the required version number by getting the last mandatory version */
833 /* of the last version patch array */
834 for (dbversion = dbversions; NULL != dbversion->patches; dbversion++)
835 ;
836
837 patches = (--dbversion)->patches;
838
839 for (i = 0; 0 != patches[i].version; i++)
840 {
841 if (0 != patches[i].mandatory)
842 required = patches[i].version;
843 }
844
845 DBconnect(ZBX_DB_CONNECT_NORMAL);
846
847 if (SUCCEED != DBtable_exists(dbversion_table_name))
848 {
849 #ifndef HAVE_SQLITE3
850 zabbix_log(LOG_LEVEL_DEBUG, "%s() \"%s\" does not exist",
851 __function_name, dbversion_table_name);
852
853 if (SUCCEED != DBfield_exists("config", "server_check_interval"))
854 {
855 zabbix_log(LOG_LEVEL_CRIT, "Cannot upgrade database: the database must"
856 " correspond to version 2.0 or later. Exiting ...");
857 goto out;
858 }
859
860 if (SUCCEED != DBcreate_dbversion_table())
861 goto out;
862 #else
863 zabbix_log(LOG_LEVEL_CRIT, "The %s does not match Zabbix database."
864 " Current database version (mandatory/optional): UNKNOWN."
865 " Required mandatory version: %08d.",
866 get_program_type_string(program_type), required);
867 zabbix_log(LOG_LEVEL_CRIT, "Zabbix does not support SQLite3 database upgrade.");
868
869 goto out;
870 #endif
871 }
872
873 DBget_version(&db_mandatory, &db_optional);
874
875 #ifndef HAVE_SQLITE3
876 for (dbversion = dbversions; NULL != (patches = dbversion->patches); dbversion++)
877 {
878 for (i = 0; 0 != patches[i].version; i++)
879 {
880 if (0 != patches[i].mandatory)
881 optional_num = 0;
882 else
883 optional_num++;
884
885 if (db_optional < patches[i].version)
886 total++;
887 }
888 }
889
890 if (required < db_mandatory)
891 #else
892 if (required != db_mandatory)
893 #endif
894 {
895 zabbix_log(LOG_LEVEL_CRIT, "The %s does not match Zabbix database."
896 " Current database version (mandatory/optional): %08d/%08d."
897 " Required mandatory version: %08d.",
898 get_program_type_string(program_type), db_mandatory, db_optional, required);
899 #ifdef HAVE_SQLITE3
900 if (required > db_mandatory)
901 zabbix_log(LOG_LEVEL_CRIT, "Zabbix does not support SQLite3 database upgrade.");
902 #endif
903 goto out;
904 }
905
906 zabbix_log(LOG_LEVEL_INFORMATION, "current database version (mandatory/optional): %08d/%08d",
907 db_mandatory, db_optional);
908 zabbix_log(LOG_LEVEL_INFORMATION, "required mandatory version: %08d", required);
909
910 ret = SUCCEED;
911
912 #ifndef HAVE_SQLITE3
913 if (0 == total)
914 goto out;
915
916 if (0 != optional_num)
917 zabbix_log(LOG_LEVEL_INFORMATION, "optional patches were found");
918
919 zabbix_log(LOG_LEVEL_WARNING, "starting automatic database upgrade");
920
921 for (dbversion = dbversions; NULL != dbversion->patches; dbversion++)
922 {
923 patches = dbversion->patches;
924
925 for (i = 0; 0 != patches[i].version; i++)
926 {
927 static sigset_t orig_mask, mask;
928
929 if (db_optional >= patches[i].version)
930 continue;
931
932 /* block signals to prevent interruption of statements that cause an implicit commit */
933 sigemptyset(&mask);
934 sigaddset(&mask, SIGTERM);
935 sigaddset(&mask, SIGINT);
936 sigaddset(&mask, SIGQUIT);
937
938 if (0 > sigprocmask(SIG_BLOCK, &mask, &orig_mask))
939 zabbix_log(LOG_LEVEL_WARNING, "cannot set sigprocmask to block the user signal");
940
941 DBbegin();
942
943 /* skipping the duplicated patches */
944 if ((0 != patches[i].duplicates && patches[i].duplicates <= db_optional) ||
945 SUCCEED == (ret = patches[i].function()))
946 {
947 ret = DBset_version(patches[i].version, patches[i].mandatory);
948 }
949
950 ret = DBend(ret);
951
952 if (0 > sigprocmask(SIG_SETMASK, &orig_mask, NULL))
953 zabbix_log(LOG_LEVEL_WARNING,"cannot restore sigprocmask");
954
955 if (SUCCEED != ret)
956 break;
957
958 current++;
959 completed = (int)(100.0 * current / total);
960
961 if (last_completed != completed)
962 {
963 zabbix_log(LOG_LEVEL_WARNING, "completed %d%% of database upgrade", completed);
964 last_completed = completed;
965 }
966 }
967
968 if (SUCCEED != ret)
969 break;
970 }
971
972 if (SUCCEED == ret)
973 zabbix_log(LOG_LEVEL_WARNING, "database upgrade fully completed");
974 else
975 zabbix_log(LOG_LEVEL_CRIT, "database upgrade failed");
976 #endif /* not HAVE_SQLITE3 */
977
978 out:
979 DBclose();
980
981 zabbix_log(LOG_LEVEL_DEBUG, "End of %s():%s", __function_name, zbx_result_string(ret));
982
983 return ret;
984 }
985