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_POSTGRESQL)
44 # define ZBX_DB_ALTER_COLUMN " alter"
45 #else
46 # define ZBX_DB_ALTER_COLUMN " modify"
47 #endif
48
49 #if defined(HAVE_POSTGRESQL)
50 # define ZBX_DB_SET_TYPE " type"
51 #else
52 # define ZBX_DB_SET_TYPE ""
53 #endif
54
55 /* NOTE: Do not forget to sync changes in ZBX_TYPE_*_STR defines for Oracle with zbx_oracle_column_type()! */
56
57 #if defined(HAVE_POSTGRESQL)
58 # define ZBX_TYPE_ID_STR "bigint"
59 #elif defined(HAVE_MYSQL)
60 # define ZBX_TYPE_ID_STR "bigint unsigned"
61 #elif defined(HAVE_ORACLE)
62 # define ZBX_TYPE_ID_STR "number(20)"
63 #endif
64
65 #ifdef HAVE_ORACLE
66 # define ZBX_TYPE_INT_STR "number(10)"
67 # define ZBX_TYPE_CHAR_STR "nvarchar2"
68 #else
69 # define ZBX_TYPE_INT_STR "integer"
70 # define ZBX_TYPE_CHAR_STR "varchar"
71 #endif
72
73 #if defined(HAVE_MYSQL)
74 # define ZBX_TYPE_FLOAT_STR "double precision"
75 # define ZBX_TYPE_UINT_STR "bigint unsigned"
76 #elif defined(HAVE_ORACLE)
77 # define ZBX_TYPE_FLOAT_STR "binary_double"
78 # define ZBX_TYPE_UINT_STR "number(20)"
79 #elif defined(HAVE_POSTGRESQL)
80 # define ZBX_TYPE_FLOAT_STR "double precision"
81 # define ZBX_TYPE_UINT_STR "numeric(20)"
82 #endif
83
84 #if defined(HAVE_ORACLE)
85 # define ZBX_TYPE_SHORTTEXT_STR "nvarchar2(2048)"
86 #else
87 # define ZBX_TYPE_SHORTTEXT_STR "text"
88 #endif
89
90 #if defined(HAVE_ORACLE)
91 # define ZBX_TYPE_TEXT_STR "nclob"
92 #else
93 # define ZBX_TYPE_TEXT_STR "text"
94 #endif
95
96 #define ZBX_FIRST_DB_VERSION 2010000
97
98 extern unsigned char program_type;
99
100
101 #ifndef HAVE_SQLITE3
DBfield_type_string(char ** sql,size_t * sql_alloc,size_t * sql_offset,const ZBX_FIELD * field)102 static void DBfield_type_string(char **sql, size_t *sql_alloc, size_t *sql_offset, const ZBX_FIELD *field)
103 {
104 switch (field->type)
105 {
106 case ZBX_TYPE_ID:
107 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ZBX_TYPE_ID_STR);
108 break;
109 case ZBX_TYPE_INT:
110 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ZBX_TYPE_INT_STR);
111 break;
112 case ZBX_TYPE_CHAR:
113 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "%s(%hu)", ZBX_TYPE_CHAR_STR, field->length);
114 break;
115 case ZBX_TYPE_FLOAT:
116 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ZBX_TYPE_FLOAT_STR);
117 break;
118 case ZBX_TYPE_UINT:
119 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ZBX_TYPE_UINT_STR);
120 break;
121 case ZBX_TYPE_SHORTTEXT:
122 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ZBX_TYPE_SHORTTEXT_STR);
123 break;
124 case ZBX_TYPE_TEXT:
125 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ZBX_TYPE_TEXT_STR);
126 break;
127 default:
128 assert(0);
129 }
130 }
131
132 #ifdef HAVE_ORACLE
133 typedef enum
134 {
135 ZBX_ORACLE_COLUMN_TYPE_NUMERIC,
136 ZBX_ORACLE_COLUMN_TYPE_CHARACTER,
137 ZBX_ORACLE_COLUMN_TYPE_DOUBLE,
138 ZBX_ORACLE_COLUMN_TYPE_UNKNOWN
139 }
140 zbx_oracle_column_type_t;
141
142 /******************************************************************************
143 * *
144 * Function: zbx_oracle_column_type *
145 * *
146 * Purpose: determine whether column type is character or numeric *
147 * *
148 * Parameters: field_type - [IN] column type in Zabbix definitions *
149 * *
150 * Return value: column type (character/raw, numeric) in Oracle definitions *
151 * *
152 * Comments: The size of a character or raw column or the precision of a *
153 * numeric column can be changed, whether or not all the rows *
154 * contain nulls. Otherwise in order to change the datatype of a *
155 * column all rows of the column must contain nulls. *
156 * *
157 ******************************************************************************/
zbx_oracle_column_type(unsigned char field_type)158 static zbx_oracle_column_type_t zbx_oracle_column_type(unsigned char field_type)
159 {
160 switch (field_type)
161 {
162 case ZBX_TYPE_ID:
163 case ZBX_TYPE_INT:
164 case ZBX_TYPE_UINT:
165 return ZBX_ORACLE_COLUMN_TYPE_NUMERIC;
166 case ZBX_TYPE_CHAR:
167 case ZBX_TYPE_SHORTTEXT:
168 case ZBX_TYPE_TEXT:
169 return ZBX_ORACLE_COLUMN_TYPE_CHARACTER;
170 case ZBX_TYPE_FLOAT:
171 return ZBX_ORACLE_COLUMN_TYPE_DOUBLE;
172 default:
173 THIS_SHOULD_NEVER_HAPPEN;
174 return ZBX_ORACLE_COLUMN_TYPE_UNKNOWN;
175 }
176 }
177 #endif
178
DBfield_definition_string(char ** sql,size_t * sql_alloc,size_t * sql_offset,const ZBX_FIELD * field)179 static void DBfield_definition_string(char **sql, size_t *sql_alloc, size_t *sql_offset, const ZBX_FIELD *field)
180 {
181 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, ZBX_FS_SQL_NAME " ", field->name);
182 DBfield_type_string(sql, sql_alloc, sql_offset, field);
183 if (NULL != field->default_value)
184 {
185 char *default_value_esc;
186
187 #if defined(HAVE_MYSQL)
188 switch (field->type)
189 {
190 case ZBX_TYPE_BLOB:
191 case ZBX_TYPE_TEXT:
192 case ZBX_TYPE_SHORTTEXT:
193 case ZBX_TYPE_LONGTEXT:
194 /* MySQL: BLOB and TEXT columns cannot be assigned a default value */
195 break;
196 default:
197 #endif
198 default_value_esc = DBdyn_escape_string(field->default_value);
199 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, " default '%s'", default_value_esc);
200 zbx_free(default_value_esc);
201 #if defined(HAVE_MYSQL)
202 }
203 #endif
204 }
205
206 if (0 != (field->flags & ZBX_NOTNULL))
207 {
208 #if defined(HAVE_ORACLE)
209 switch (field->type)
210 {
211 case ZBX_TYPE_INT:
212 case ZBX_TYPE_FLOAT:
213 case ZBX_TYPE_BLOB:
214 case ZBX_TYPE_UINT:
215 case ZBX_TYPE_ID:
216 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, " not null");
217 break;
218 default: /* ZBX_TYPE_CHAR, ZBX_TYPE_TEXT, ZBX_TYPE_SHORTTEXT or ZBX_TYPE_LONGTEXT */
219 /* nothing to do */;
220 }
221 #else
222 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, " not null");
223 #endif
224 }
225 }
226
DBcreate_table_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const ZBX_TABLE * table)227 static void DBcreate_table_sql(char **sql, size_t *sql_alloc, size_t *sql_offset, const ZBX_TABLE *table)
228 {
229 int i;
230
231 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "create table %s (\n", table->table);
232
233 for (i = 0; NULL != table->fields[i].name; i++)
234 {
235 if (0 != i)
236 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ",\n");
237 DBfield_definition_string(sql, sql_alloc, sql_offset, &table->fields[i]);
238 }
239 if ('\0' != *table->recid)
240 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, ",\nprimary key (%s)", table->recid);
241
242 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, "\n)" ZBX_DB_TABLE_OPTIONS);
243 }
244
DBrename_table_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,const char * new_name)245 static void DBrename_table_sql(char **sql, size_t *sql_alloc, size_t *sql_offset, const char *table_name,
246 const char *new_name)
247 {
248 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "alter table " ZBX_FS_SQL_NAME " rename to " ZBX_FS_SQL_NAME,
249 table_name, new_name);
250 }
251
DBdrop_table_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name)252 static void DBdrop_table_sql(char **sql, size_t *sql_alloc, size_t *sql_offset, const char *table_name)
253 {
254 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "drop table %s", table_name);
255 }
256
DBset_default_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,const ZBX_FIELD * field)257 static void DBset_default_sql(char **sql, size_t *sql_alloc, size_t *sql_offset,
258 const char *table_name, const ZBX_FIELD *field)
259 {
260 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "alter table %s" ZBX_DB_ALTER_COLUMN " ", table_name);
261
262 #if defined(HAVE_MYSQL)
263 DBfield_definition_string(sql, sql_alloc, sql_offset, field);
264 #elif defined(HAVE_ORACLE)
265 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "%s default '%s'", field->name, field->default_value);
266 #else
267 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "%s set default '%s'", field->name, field->default_value);
268 #endif
269 }
270
DBdrop_default_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,const ZBX_FIELD * field)271 static void DBdrop_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 null", field->name);
280 #else
281 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "%s drop default", field->name);
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_MYSQL)
385 DBcreate_index_sql(sql, sql_alloc, sql_offset, table_name, new_name, fields, unique);
386 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ";\n");
387 DBdrop_index_sql(sql, sql_alloc, sql_offset, table_name, old_name);
388 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, ";\n");
389 #elif defined(HAVE_ORACLE) || defined(HAVE_POSTGRESQL)
390 ZBX_UNUSED(table_name);
391 ZBX_UNUSED(fields);
392 ZBX_UNUSED(unique);
393 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "alter index %s rename to %s", old_name, new_name);
394 #endif
395 }
396
DBadd_foreign_key_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,int id,const ZBX_FIELD * field)397 static void DBadd_foreign_key_sql(char **sql, size_t *sql_alloc, size_t *sql_offset,
398 const char *table_name, int id, const ZBX_FIELD *field)
399 {
400 zbx_snprintf_alloc(sql, sql_alloc, sql_offset,
401 "alter table " ZBX_FS_SQL_NAME " add constraint c_%s_%d foreign key (" ZBX_FS_SQL_NAME ")"
402 " references " ZBX_FS_SQL_NAME " (" ZBX_FS_SQL_NAME ")", table_name, table_name,
403 id, field->name, field->fk_table, field->fk_field);
404 if (0 != (field->fk_flags & ZBX_FK_CASCADE_DELETE))
405 zbx_strcpy_alloc(sql, sql_alloc, sql_offset, " on delete cascade");
406 }
407
DBdrop_foreign_key_sql(char ** sql,size_t * sql_alloc,size_t * sql_offset,const char * table_name,int id)408 static void DBdrop_foreign_key_sql(char **sql, size_t *sql_alloc, size_t *sql_offset,
409 const char *table_name, int id)
410 {
411 zbx_snprintf_alloc(sql, sql_alloc, sql_offset, "alter table %s" ZBX_DROP_FK " c_%s_%d",
412 table_name, table_name, id);
413 }
414
DBcreate_table(const ZBX_TABLE * table)415 int DBcreate_table(const ZBX_TABLE *table)
416 {
417 char *sql = NULL;
418 size_t sql_alloc = 0, sql_offset = 0;
419 int ret = FAIL;
420
421 DBcreate_table_sql(&sql, &sql_alloc, &sql_offset, table);
422
423 if (ZBX_DB_OK <= DBexecute("%s", sql))
424 ret = SUCCEED;
425
426 zbx_free(sql);
427
428 return ret;
429 }
430
DBrename_table(const char * table_name,const char * new_name)431 int DBrename_table(const char *table_name, const char *new_name)
432 {
433 char *sql = NULL;
434 size_t sql_alloc = 0, sql_offset = 0;
435 int ret = FAIL;
436
437 DBrename_table_sql(&sql, &sql_alloc, &sql_offset, table_name, new_name);
438
439 if (ZBX_DB_OK <= DBexecute("%s", sql))
440 ret = SUCCEED;
441
442 zbx_free(sql);
443
444 return ret;
445 }
446
DBdrop_table(const char * table_name)447 int DBdrop_table(const char *table_name)
448 {
449 char *sql = NULL;
450 size_t sql_alloc = 0, sql_offset = 0;
451 int ret = FAIL;
452
453 DBdrop_table_sql(&sql, &sql_alloc, &sql_offset, table_name);
454
455 if (ZBX_DB_OK <= DBexecute("%s", sql))
456 ret = SUCCEED;
457
458 zbx_free(sql);
459
460 return ret;
461 }
462
DBadd_field(const char * table_name,const ZBX_FIELD * field)463 int DBadd_field(const char *table_name, const ZBX_FIELD *field)
464 {
465 char *sql = NULL;
466 size_t sql_alloc = 0, sql_offset = 0;
467 int ret = FAIL;
468
469 DBadd_field_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
470
471 if (ZBX_DB_OK <= DBexecute("%s", sql))
472 ret = SUCCEED;
473
474 zbx_free(sql);
475
476 return ret;
477 }
478
DBrename_field(const char * table_name,const char * field_name,const ZBX_FIELD * field)479 int DBrename_field(const char *table_name, const char *field_name, const ZBX_FIELD *field)
480 {
481 char *sql = NULL;
482 size_t sql_alloc = 0, sql_offset = 0;
483 int ret = FAIL;
484
485 DBrename_field_sql(&sql, &sql_alloc, &sql_offset, table_name, field_name, field);
486
487 if (ZBX_DB_OK <= DBexecute("%s", sql))
488 ret = SUCCEED;
489
490 zbx_free(sql);
491
492 return ret;
493 }
494
495 #ifdef HAVE_ORACLE
DBmodify_field_type_with_copy(const char * table_name,const ZBX_FIELD * field)496 static int DBmodify_field_type_with_copy(const char *table_name, const ZBX_FIELD *field)
497 {
498 #define ZBX_OLD_FIELD "zbx_old_tmp"
499
500 char *sql = NULL;
501 size_t sql_alloc = 0, sql_offset = 0;
502 int ret = FAIL;
503
504 zbx_snprintf_alloc(&sql, &sql_alloc, &sql_offset, "alter table %s rename column %s to " ZBX_OLD_FIELD,
505 table_name, field->name);
506
507 if (ZBX_DB_OK > DBexecute("%s", sql))
508 goto out;
509
510 if (ZBX_DB_OK > DBadd_field(table_name, field))
511 goto out;
512
513 sql_offset = 0;
514 zbx_snprintf_alloc(&sql, &sql_alloc, &sql_offset, "update %s set %s=" ZBX_OLD_FIELD, table_name,
515 field->name);
516
517 if (ZBX_DB_OK > DBexecute("%s", sql))
518 goto out;
519
520 ret = DBdrop_field(table_name, ZBX_OLD_FIELD);
521 out:
522 zbx_free(sql);
523
524 return ret;
525
526 #undef ZBX_OLD_FIELD
527 }
528 #endif
529
DBmodify_field_type(const char * table_name,const ZBX_FIELD * field,const ZBX_FIELD * old_field)530 int DBmodify_field_type(const char *table_name, const ZBX_FIELD *field, const ZBX_FIELD *old_field)
531 {
532 char *sql = NULL;
533 size_t sql_alloc = 0, sql_offset = 0;
534 int ret = FAIL;
535
536 #ifndef HAVE_ORACLE
537 ZBX_UNUSED(old_field);
538 #else
539 /* Oracle cannot change column type in a general case if column contents are not null. Conversions like */
540 /* number -> nvarchar2 or nvarchar2 -> nclob need special processing. New column is created with desired */
541 /* datatype and data from old column is copied there. Then old column is dropped. This method does not */
542 /* preserve column order. */
543 /* NOTE: Existing column indexes and constraints are not respected by the current implementation! */
544
545 if (NULL != old_field && (zbx_oracle_column_type(old_field->type) != zbx_oracle_column_type(field->type) ||
546 ZBX_ORACLE_COLUMN_TYPE_DOUBLE == zbx_oracle_column_type(field->type) ||
547 (ZBX_TYPE_TEXT == field->type && (ZBX_TYPE_SHORTTEXT == old_field->type ||
548 ZBX_TYPE_CHAR == old_field->type))))
549 {
550 return DBmodify_field_type_with_copy(table_name, field);
551 }
552 #endif
553 DBmodify_field_type_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
554
555 if (ZBX_DB_OK <= DBexecute("%s", sql))
556 ret = SUCCEED;
557
558 zbx_free(sql);
559
560 return ret;
561 }
562
DBset_not_null(const char * table_name,const ZBX_FIELD * field)563 int DBset_not_null(const char *table_name, const ZBX_FIELD *field)
564 {
565 char *sql = NULL;
566 size_t sql_alloc = 0, sql_offset = 0;
567 int ret = FAIL;
568
569 DBset_not_null_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
570
571 if (ZBX_DB_OK <= DBexecute("%s", sql))
572 ret = SUCCEED;
573
574 zbx_free(sql);
575
576 return ret;
577 }
578
DBset_default(const char * table_name,const ZBX_FIELD * field)579 int DBset_default(const char *table_name, const ZBX_FIELD *field)
580 {
581 char *sql = NULL;
582 size_t sql_alloc = 0, sql_offset = 0;
583 int ret = FAIL;
584
585 DBset_default_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
586
587 if (ZBX_DB_OK <= DBexecute("%s", sql))
588 ret = SUCCEED;
589
590 zbx_free(sql);
591
592 return ret;
593 }
594
DBdrop_default(const char * table_name,const ZBX_FIELD * field)595 int DBdrop_default(const char *table_name, const ZBX_FIELD *field)
596 {
597 char *sql = NULL;
598 size_t sql_alloc = 0, sql_offset = 0;
599 int ret = FAIL;
600
601 DBdrop_default_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
602
603 if (ZBX_DB_OK <= DBexecute("%s", sql))
604 ret = SUCCEED;
605
606 zbx_free(sql);
607
608 return ret;
609 }
610
DBdrop_not_null(const char * table_name,const ZBX_FIELD * field)611 int DBdrop_not_null(const char *table_name, const ZBX_FIELD *field)
612 {
613 char *sql = NULL;
614 size_t sql_alloc = 0, sql_offset = 0;
615 int ret = FAIL;
616
617 DBdrop_not_null_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
618
619 if (ZBX_DB_OK <= DBexecute("%s", sql))
620 ret = SUCCEED;
621
622 zbx_free(sql);
623
624 return ret;
625 }
626
DBdrop_field(const char * table_name,const char * field_name)627 int DBdrop_field(const char *table_name, const char *field_name)
628 {
629 char *sql = NULL;
630 size_t sql_alloc = 0, sql_offset = 0;
631 int ret = FAIL;
632
633 DBdrop_field_sql(&sql, &sql_alloc, &sql_offset, table_name, field_name);
634
635 if (ZBX_DB_OK <= DBexecute("%s", sql))
636 ret = SUCCEED;
637
638 zbx_free(sql);
639
640 return ret;
641 }
642
DBcreate_index(const char * table_name,const char * index_name,const char * fields,int unique)643 int DBcreate_index(const char *table_name, const char *index_name, const char *fields, int unique)
644 {
645 char *sql = NULL;
646 size_t sql_alloc = 0, sql_offset = 0;
647 int ret = FAIL;
648
649 DBcreate_index_sql(&sql, &sql_alloc, &sql_offset, table_name, index_name, fields, unique);
650
651 if (ZBX_DB_OK <= DBexecute("%s", sql))
652 ret = SUCCEED;
653
654 zbx_free(sql);
655
656 return ret;
657 }
658
DBdrop_index(const char * table_name,const char * index_name)659 int DBdrop_index(const char *table_name, const char *index_name)
660 {
661 char *sql = NULL;
662 size_t sql_alloc = 0, sql_offset = 0;
663 int ret = FAIL;
664
665 DBdrop_index_sql(&sql, &sql_alloc, &sql_offset, table_name, index_name);
666
667 if (ZBX_DB_OK <= DBexecute("%s", sql))
668 ret = SUCCEED;
669
670 zbx_free(sql);
671
672 return ret;
673 }
674
DBrename_index(const char * table_name,const char * old_name,const char * new_name,const char * fields,int unique)675 int DBrename_index(const char *table_name, const char *old_name, const char *new_name, const char *fields,
676 int unique)
677 {
678 char *sql = NULL;
679 size_t sql_alloc = 0, sql_offset = 0;
680 int ret = FAIL;
681
682 DBrename_index_sql(&sql, &sql_alloc, &sql_offset, table_name, old_name, new_name, fields, unique);
683
684 if (ZBX_DB_OK <= DBexecute("%s", sql))
685 ret = SUCCEED;
686
687 zbx_free(sql);
688
689 return ret;
690 }
691
DBadd_foreign_key(const char * table_name,int id,const ZBX_FIELD * field)692 int DBadd_foreign_key(const char *table_name, int id, const ZBX_FIELD *field)
693 {
694 char *sql = NULL;
695 size_t sql_alloc = 0, sql_offset = 0;
696 int ret = FAIL;
697
698 DBadd_foreign_key_sql(&sql, &sql_alloc, &sql_offset, table_name, id, field);
699
700 if (ZBX_DB_OK <= DBexecute("%s", sql))
701 ret = SUCCEED;
702
703 zbx_free(sql);
704
705 return ret;
706 }
707
DBdrop_foreign_key(const char * table_name,int id)708 int DBdrop_foreign_key(const char *table_name, int id)
709 {
710 char *sql = NULL;
711 size_t sql_alloc = 0, sql_offset = 0;
712 int ret = FAIL;
713
714 DBdrop_foreign_key_sql(&sql, &sql_alloc, &sql_offset, table_name, id);
715
716 if (ZBX_DB_OK <= DBexecute("%s", sql))
717 ret = SUCCEED;
718
719 zbx_free(sql);
720
721 return ret;
722 }
723
DBcreate_dbversion_table(void)724 static int DBcreate_dbversion_table(void)
725 {
726 const ZBX_TABLE table =
727 {"dbversion", "", 0,
728 {
729 {"mandatory", "0", NULL, NULL, 0, ZBX_TYPE_INT, ZBX_NOTNULL, 0},
730 {"optional", "0", NULL, NULL, 0, ZBX_TYPE_INT, ZBX_NOTNULL, 0},
731 {NULL}
732 },
733 NULL
734 };
735 int ret;
736
737 DBbegin();
738 if (SUCCEED == (ret = DBcreate_table(&table)))
739 {
740 if (ZBX_DB_OK > DBexecute("insert into dbversion (mandatory,optional) values (%d,%d)",
741 ZBX_FIRST_DB_VERSION, ZBX_FIRST_DB_VERSION))
742 {
743 ret = FAIL;
744 }
745 }
746
747 return DBend(ret);
748 }
749
DBset_version(int version,unsigned char mandatory)750 static int DBset_version(int version, unsigned char mandatory)
751 {
752 char sql[64];
753 size_t offset;
754
755 offset = zbx_snprintf(sql, sizeof(sql), "update dbversion set ");
756 if (0 != mandatory)
757 offset += zbx_snprintf(sql + offset, sizeof(sql) - offset, "mandatory=%d,", version);
758 zbx_snprintf(sql + offset, sizeof(sql) - offset, "optional=%d", version);
759
760 if (ZBX_DB_OK <= DBexecute("%s", sql))
761 return SUCCEED;
762
763 return FAIL;
764 }
765
766 #endif /* not HAVE_SQLITE3 */
767
768 extern zbx_dbpatch_t DBPATCH_VERSION(2010)[];
769 extern zbx_dbpatch_t DBPATCH_VERSION(2020)[];
770 extern zbx_dbpatch_t DBPATCH_VERSION(2030)[];
771 extern zbx_dbpatch_t DBPATCH_VERSION(2040)[];
772 extern zbx_dbpatch_t DBPATCH_VERSION(2050)[];
773 extern zbx_dbpatch_t DBPATCH_VERSION(3000)[];
774 extern zbx_dbpatch_t DBPATCH_VERSION(3010)[];
775 extern zbx_dbpatch_t DBPATCH_VERSION(3020)[];
776 extern zbx_dbpatch_t DBPATCH_VERSION(3030)[];
777 extern zbx_dbpatch_t DBPATCH_VERSION(3040)[];
778 extern zbx_dbpatch_t DBPATCH_VERSION(3050)[];
779 extern zbx_dbpatch_t DBPATCH_VERSION(4000)[];
780 extern zbx_dbpatch_t DBPATCH_VERSION(4010)[];
781 extern zbx_dbpatch_t DBPATCH_VERSION(4020)[];
782 extern zbx_dbpatch_t DBPATCH_VERSION(4030)[];
783 extern zbx_dbpatch_t DBPATCH_VERSION(4040)[];
784 extern zbx_dbpatch_t DBPATCH_VERSION(4050)[];
785 extern zbx_dbpatch_t DBPATCH_VERSION(5000)[];
786 extern zbx_dbpatch_t DBPATCH_VERSION(5010)[];
787 extern zbx_dbpatch_t DBPATCH_VERSION(5020)[];
788 extern zbx_dbpatch_t DBPATCH_VERSION(5030)[];
789 extern zbx_dbpatch_t DBPATCH_VERSION(5040)[];
790
791 static zbx_db_version_t dbversions[] = {
792 {DBPATCH_VERSION(2010), "2.2 development"},
793 {DBPATCH_VERSION(2020), "2.2 maintenance"},
794 {DBPATCH_VERSION(2030), "2.4 development"},
795 {DBPATCH_VERSION(2040), "2.4 maintenance"},
796 {DBPATCH_VERSION(2050), "3.0 development"},
797 {DBPATCH_VERSION(3000), "3.0 maintenance"},
798 {DBPATCH_VERSION(3010), "3.2 development"},
799 {DBPATCH_VERSION(3020), "3.2 maintenance"},
800 {DBPATCH_VERSION(3030), "3.4 development"},
801 {DBPATCH_VERSION(3040), "3.4 maintenance"},
802 {DBPATCH_VERSION(3050), "4.0 development"},
803 {DBPATCH_VERSION(4000), "4.0 maintenance"},
804 {DBPATCH_VERSION(4010), "4.2 development"},
805 {DBPATCH_VERSION(4020), "4.2 maintenance"},
806 {DBPATCH_VERSION(4030), "4.4 development"},
807 {DBPATCH_VERSION(4040), "4.4 maintenance"},
808 {DBPATCH_VERSION(4050), "5.0 development"},
809 {DBPATCH_VERSION(5000), "5.0 maintenance"},
810 {DBPATCH_VERSION(5010), "5.2 development"},
811 {DBPATCH_VERSION(5020), "5.2 maintenance"},
812 {DBPATCH_VERSION(5030), "5.4 development"},
813 {DBPATCH_VERSION(5040), "5.4 maintenance"},
814 {NULL}
815 };
816
DBget_version(int * mandatory,int * optional)817 static void DBget_version(int *mandatory, int *optional)
818 {
819 DB_RESULT result;
820 DB_ROW row;
821
822 *mandatory = -1;
823 *optional = -1;
824
825 result = DBselect("select mandatory,optional from dbversion");
826
827 if (NULL != (row = DBfetch(result)))
828 {
829 *mandatory = atoi(row[0]);
830 *optional = atoi(row[1]);
831 }
832 DBfree_result(result);
833
834 if (-1 == *mandatory)
835 {
836 zabbix_log(LOG_LEVEL_CRIT, "Cannot get the database version. Exiting ...");
837 exit(EXIT_FAILURE);
838 }
839 }
840
DBcheck_version(void)841 int DBcheck_version(void)
842 {
843 const char *dbversion_table_name = "dbversion";
844 int db_mandatory, db_optional, required, ret = FAIL, i;
845 zbx_db_version_t *dbversion;
846 zbx_dbpatch_t *patches;
847
848 #ifndef HAVE_SQLITE3
849 int total = 0, current = 0, completed, last_completed = -1, optional_num = 0;
850 #endif
851 zabbix_log(LOG_LEVEL_DEBUG, "In %s()", __func__);
852
853 required = ZBX_FIRST_DB_VERSION;
854
855 /* find out the required version number by getting the last mandatory version */
856 /* of the last version patch array */
857 for (dbversion = dbversions; NULL != dbversion->patches; dbversion++)
858 ;
859
860 patches = (--dbversion)->patches;
861
862 for (i = 0; 0 != patches[i].version; i++)
863 {
864 if (0 != patches[i].mandatory)
865 required = patches[i].version;
866 }
867
868 DBconnect(ZBX_DB_CONNECT_NORMAL);
869
870 if (SUCCEED != DBtable_exists(dbversion_table_name))
871 {
872 #ifndef HAVE_SQLITE3
873 zabbix_log(LOG_LEVEL_DEBUG, "%s() \"%s\" does not exist", __func__, dbversion_table_name);
874
875 if (SUCCEED != DBfield_exists("config", "server_check_interval"))
876 {
877 zabbix_log(LOG_LEVEL_CRIT, "Cannot upgrade database: the database must"
878 " correspond to version 2.0 or later. Exiting ...");
879 goto out;
880 }
881
882 if (SUCCEED != DBcreate_dbversion_table())
883 goto out;
884 #else
885 zabbix_log(LOG_LEVEL_CRIT, "The %s does not match Zabbix database."
886 " Current database version (mandatory/optional): UNKNOWN."
887 " Required mandatory version: %08d.",
888 get_program_type_string(program_type), required);
889 zabbix_log(LOG_LEVEL_CRIT, "Zabbix does not support SQLite3 database upgrade.");
890
891 goto out;
892 #endif
893 }
894
895 DBget_version(&db_mandatory, &db_optional);
896
897 #ifndef HAVE_SQLITE3
898 for (dbversion = dbversions; NULL != (patches = dbversion->patches); dbversion++)
899 {
900 for (i = 0; 0 != patches[i].version; i++)
901 {
902 if (0 != patches[i].mandatory)
903 optional_num = 0;
904 else
905 optional_num++;
906
907 if (db_optional < patches[i].version)
908 total++;
909 }
910 }
911
912 if (required < db_mandatory)
913 #else
914 if (required != db_mandatory)
915 #endif
916 {
917 zabbix_log(LOG_LEVEL_CRIT, "The %s does not match Zabbix database."
918 " Current database version (mandatory/optional): %08d/%08d."
919 " Required mandatory version: %08d.",
920 get_program_type_string(program_type), db_mandatory, db_optional, required);
921 #ifdef HAVE_SQLITE3
922 if (required > db_mandatory)
923 zabbix_log(LOG_LEVEL_CRIT, "Zabbix does not support SQLite3 database upgrade.");
924 #endif
925 goto out;
926 }
927
928 zabbix_log(LOG_LEVEL_INFORMATION, "current database version (mandatory/optional): %08d/%08d",
929 db_mandatory, db_optional);
930 zabbix_log(LOG_LEVEL_INFORMATION, "required mandatory version: %08d", required);
931
932 ret = SUCCEED;
933
934 #ifndef HAVE_SQLITE3
935 if (0 == total)
936 goto out;
937
938 if (0 != optional_num)
939 zabbix_log(LOG_LEVEL_INFORMATION, "optional patches were found");
940
941 zabbix_log(LOG_LEVEL_WARNING, "starting automatic database upgrade");
942
943 for (dbversion = dbversions; NULL != dbversion->patches; dbversion++)
944 {
945 patches = dbversion->patches;
946
947 for (i = 0; 0 != patches[i].version; i++)
948 {
949 static sigset_t orig_mask, mask;
950
951 if (db_optional >= patches[i].version)
952 continue;
953
954 /* block signals to prevent interruption of statements that cause an implicit commit */
955 sigemptyset(&mask);
956 sigaddset(&mask, SIGTERM);
957 sigaddset(&mask, SIGINT);
958 sigaddset(&mask, SIGQUIT);
959
960 if (0 > sigprocmask(SIG_BLOCK, &mask, &orig_mask))
961 zabbix_log(LOG_LEVEL_WARNING, "cannot set sigprocmask to block the user signal");
962
963 DBbegin();
964
965 /* skipping the duplicated patches */
966 if ((0 != patches[i].duplicates && patches[i].duplicates <= db_optional) ||
967 SUCCEED == (ret = patches[i].function()))
968 {
969 ret = DBset_version(patches[i].version, patches[i].mandatory);
970 }
971
972 ret = DBend(ret);
973
974 if (0 > sigprocmask(SIG_SETMASK, &orig_mask, NULL))
975 zabbix_log(LOG_LEVEL_WARNING,"cannot restore sigprocmask");
976
977 if (SUCCEED != ret)
978 break;
979
980 current++;
981 completed = (int)(100.0 * current / total);
982
983 if (last_completed != completed)
984 {
985 zabbix_log(LOG_LEVEL_WARNING, "completed %d%% of database upgrade", completed);
986 last_completed = completed;
987 }
988 }
989
990 if (SUCCEED != ret)
991 break;
992 }
993
994 if (SUCCEED == ret)
995 zabbix_log(LOG_LEVEL_WARNING, "database upgrade fully completed");
996 else
997 zabbix_log(LOG_LEVEL_CRIT, "database upgrade failed");
998 #endif /* not HAVE_SQLITE3 */
999
1000 out:
1001 DBclose();
1002
1003 zabbix_log(LOG_LEVEL_DEBUG, "End of %s():%s", __func__, zbx_result_string(ret));
1004
1005 return ret;
1006 }
1007
DBcheck_double_type(void)1008 int DBcheck_double_type(void)
1009 {
1010 DB_RESULT result;
1011 DB_ROW row;
1012 char *sql = NULL;
1013 const int total_dbl_cols = 4;
1014 int ret = FAIL;
1015
1016 zabbix_log(LOG_LEVEL_DEBUG, "In %s()", __func__);
1017
1018 DBconnect(ZBX_DB_CONNECT_NORMAL);
1019
1020 #if defined(HAVE_MYSQL)
1021 sql = DBdyn_escape_string(CONFIG_DBNAME);
1022 sql = zbx_dsprintf(sql, "select count(*) from information_schema.columns"
1023 " where table_schema='%s' and column_type='double'", sql);
1024 #elif defined(HAVE_POSTGRESQL)
1025 sql = DBdyn_escape_string(NULL == CONFIG_DBSCHEMA || '\0' == *CONFIG_DBSCHEMA ? "public" : CONFIG_DBSCHEMA);
1026 sql = zbx_dsprintf(sql, "select count(*) from information_schema.columns"
1027 " where table_schema='%s' and data_type='double precision'", sql);
1028 #elif defined(HAVE_ORACLE)
1029 sql = zbx_strdup(sql, "select count(*) from user_tab_columns"
1030 " where data_type='BINARY_DOUBLE'");
1031 #elif defined(HAVE_SQLITE3)
1032 /* upgrade patch is not required for sqlite3 */
1033 ret = SUCCEED;
1034 goto out;
1035 #endif
1036
1037 if (NULL == (result = DBselect("%s"
1038 " and ((lower(table_name)='trends'"
1039 " and (lower(column_name) in ('value_min', 'value_avg', 'value_max')))"
1040 " or (lower(table_name)='history' and lower(column_name)='value'))", sql)))
1041 {
1042 zabbix_log(LOG_LEVEL_WARNING, "cannot select records with columns information");
1043 goto out;
1044 }
1045
1046 if (NULL != (row = DBfetch(result)) && total_dbl_cols == atoi(row[0]))
1047 ret = SUCCEED;
1048
1049 DBfree_result(result);
1050 out:
1051 DBclose();
1052 zbx_free(sql);
1053
1054 zabbix_log(LOG_LEVEL_DEBUG, "End of %s()", __func__);
1055
1056 return ret;
1057 }
1058