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 return DBmodify_field_type_with_copy(table_name, field);
549 #endif
550 DBmodify_field_type_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
551
552 if (ZBX_DB_OK <= DBexecute("%s", sql))
553 ret = SUCCEED;
554
555 zbx_free(sql);
556
557 return ret;
558 }
559
DBset_not_null(const char * table_name,const ZBX_FIELD * field)560 int DBset_not_null(const char *table_name, const ZBX_FIELD *field)
561 {
562 char *sql = NULL;
563 size_t sql_alloc = 0, sql_offset = 0;
564 int ret = FAIL;
565
566 DBset_not_null_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
567
568 if (ZBX_DB_OK <= DBexecute("%s", sql))
569 ret = SUCCEED;
570
571 zbx_free(sql);
572
573 return ret;
574 }
575
DBset_default(const char * table_name,const ZBX_FIELD * field)576 int DBset_default(const char *table_name, const ZBX_FIELD *field)
577 {
578 char *sql = NULL;
579 size_t sql_alloc = 0, sql_offset = 0;
580 int ret = FAIL;
581
582 DBset_default_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
583
584 if (ZBX_DB_OK <= DBexecute("%s", sql))
585 ret = SUCCEED;
586
587 zbx_free(sql);
588
589 return ret;
590 }
591
DBdrop_default(const char * table_name,const ZBX_FIELD * field)592 int DBdrop_default(const char *table_name, const ZBX_FIELD *field)
593 {
594 char *sql = NULL;
595 size_t sql_alloc = 0, sql_offset = 0;
596 int ret = FAIL;
597
598 DBdrop_default_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
599
600 if (ZBX_DB_OK <= DBexecute("%s", sql))
601 ret = SUCCEED;
602
603 zbx_free(sql);
604
605 return ret;
606 }
607
DBdrop_not_null(const char * table_name,const ZBX_FIELD * field)608 int DBdrop_not_null(const char *table_name, const ZBX_FIELD *field)
609 {
610 char *sql = NULL;
611 size_t sql_alloc = 0, sql_offset = 0;
612 int ret = FAIL;
613
614 DBdrop_not_null_sql(&sql, &sql_alloc, &sql_offset, table_name, field);
615
616 if (ZBX_DB_OK <= DBexecute("%s", sql))
617 ret = SUCCEED;
618
619 zbx_free(sql);
620
621 return ret;
622 }
623
DBdrop_field(const char * table_name,const char * field_name)624 int DBdrop_field(const char *table_name, const char *field_name)
625 {
626 char *sql = NULL;
627 size_t sql_alloc = 0, sql_offset = 0;
628 int ret = FAIL;
629
630 DBdrop_field_sql(&sql, &sql_alloc, &sql_offset, table_name, field_name);
631
632 if (ZBX_DB_OK <= DBexecute("%s", sql))
633 ret = SUCCEED;
634
635 zbx_free(sql);
636
637 return ret;
638 }
639
DBcreate_index(const char * table_name,const char * index_name,const char * fields,int unique)640 int DBcreate_index(const char *table_name, const char *index_name, const char *fields, int unique)
641 {
642 char *sql = NULL;
643 size_t sql_alloc = 0, sql_offset = 0;
644 int ret = FAIL;
645
646 DBcreate_index_sql(&sql, &sql_alloc, &sql_offset, table_name, index_name, fields, unique);
647
648 if (ZBX_DB_OK <= DBexecute("%s", sql))
649 ret = SUCCEED;
650
651 zbx_free(sql);
652
653 return ret;
654 }
655
DBdrop_index(const char * table_name,const char * index_name)656 int DBdrop_index(const char *table_name, const char *index_name)
657 {
658 char *sql = NULL;
659 size_t sql_alloc = 0, sql_offset = 0;
660 int ret = FAIL;
661
662 DBdrop_index_sql(&sql, &sql_alloc, &sql_offset, table_name, index_name);
663
664 if (ZBX_DB_OK <= DBexecute("%s", sql))
665 ret = SUCCEED;
666
667 zbx_free(sql);
668
669 return ret;
670 }
671
DBrename_index(const char * table_name,const char * old_name,const char * new_name,const char * fields,int unique)672 int DBrename_index(const char *table_name, const char *old_name, const char *new_name, const char *fields,
673 int unique)
674 {
675 char *sql = NULL;
676 size_t sql_alloc = 0, sql_offset = 0;
677 int ret = FAIL;
678
679 DBrename_index_sql(&sql, &sql_alloc, &sql_offset, table_name, old_name, new_name, fields, unique);
680
681 if (ZBX_DB_OK <= DBexecute("%s", sql))
682 ret = SUCCEED;
683
684 zbx_free(sql);
685
686 return ret;
687 }
688
DBadd_foreign_key(const char * table_name,int id,const ZBX_FIELD * field)689 int DBadd_foreign_key(const char *table_name, int id, const ZBX_FIELD *field)
690 {
691 char *sql = NULL;
692 size_t sql_alloc = 0, sql_offset = 0;
693 int ret = FAIL;
694
695 DBadd_foreign_key_sql(&sql, &sql_alloc, &sql_offset, table_name, id, field);
696
697 if (ZBX_DB_OK <= DBexecute("%s", sql))
698 ret = SUCCEED;
699
700 zbx_free(sql);
701
702 return ret;
703 }
704
DBdrop_foreign_key(const char * table_name,int id)705 int DBdrop_foreign_key(const char *table_name, int id)
706 {
707 char *sql = NULL;
708 size_t sql_alloc = 0, sql_offset = 0;
709 int ret = FAIL;
710
711 DBdrop_foreign_key_sql(&sql, &sql_alloc, &sql_offset, table_name, id);
712
713 if (ZBX_DB_OK <= DBexecute("%s", sql))
714 ret = SUCCEED;
715
716 zbx_free(sql);
717
718 return ret;
719 }
720
DBcreate_dbversion_table(void)721 static int DBcreate_dbversion_table(void)
722 {
723 const ZBX_TABLE table =
724 {"dbversion", "", 0,
725 {
726 {"mandatory", "0", NULL, NULL, 0, ZBX_TYPE_INT, ZBX_NOTNULL, 0},
727 {"optional", "0", NULL, NULL, 0, ZBX_TYPE_INT, ZBX_NOTNULL, 0},
728 {NULL}
729 },
730 NULL
731 };
732 int ret;
733
734 DBbegin();
735 if (SUCCEED == (ret = DBcreate_table(&table)))
736 {
737 if (ZBX_DB_OK > DBexecute("insert into dbversion (mandatory,optional) values (%d,%d)",
738 ZBX_FIRST_DB_VERSION, ZBX_FIRST_DB_VERSION))
739 {
740 ret = FAIL;
741 }
742 }
743
744 return DBend(ret);
745 }
746
DBset_version(int version,unsigned char mandatory)747 static int DBset_version(int version, unsigned char mandatory)
748 {
749 char sql[64];
750 size_t offset;
751
752 offset = zbx_snprintf(sql, sizeof(sql), "update dbversion set ");
753 if (0 != mandatory)
754 offset += zbx_snprintf(sql + offset, sizeof(sql) - offset, "mandatory=%d,", version);
755 zbx_snprintf(sql + offset, sizeof(sql) - offset, "optional=%d", version);
756
757 if (ZBX_DB_OK <= DBexecute("%s", sql))
758 return SUCCEED;
759
760 return FAIL;
761 }
762
763 #endif /* not HAVE_SQLITE3 */
764
765 extern zbx_dbpatch_t DBPATCH_VERSION(2010)[];
766 extern zbx_dbpatch_t DBPATCH_VERSION(2020)[];
767 extern zbx_dbpatch_t DBPATCH_VERSION(2030)[];
768 extern zbx_dbpatch_t DBPATCH_VERSION(2040)[];
769 extern zbx_dbpatch_t DBPATCH_VERSION(2050)[];
770 extern zbx_dbpatch_t DBPATCH_VERSION(3000)[];
771 extern zbx_dbpatch_t DBPATCH_VERSION(3010)[];
772 extern zbx_dbpatch_t DBPATCH_VERSION(3020)[];
773 extern zbx_dbpatch_t DBPATCH_VERSION(3030)[];
774 extern zbx_dbpatch_t DBPATCH_VERSION(3040)[];
775 extern zbx_dbpatch_t DBPATCH_VERSION(3050)[];
776 extern zbx_dbpatch_t DBPATCH_VERSION(4000)[];
777 extern zbx_dbpatch_t DBPATCH_VERSION(4010)[];
778 extern zbx_dbpatch_t DBPATCH_VERSION(4020)[];
779 extern zbx_dbpatch_t DBPATCH_VERSION(4030)[];
780 extern zbx_dbpatch_t DBPATCH_VERSION(4040)[];
781 extern zbx_dbpatch_t DBPATCH_VERSION(4050)[];
782 extern zbx_dbpatch_t DBPATCH_VERSION(5000)[];
783
784 static zbx_db_version_t dbversions[] = {
785 {DBPATCH_VERSION(2010), "2.2 development"},
786 {DBPATCH_VERSION(2020), "2.2 maintenance"},
787 {DBPATCH_VERSION(2030), "2.4 development"},
788 {DBPATCH_VERSION(2040), "2.4 maintenance"},
789 {DBPATCH_VERSION(2050), "3.0 development"},
790 {DBPATCH_VERSION(3000), "3.0 maintenance"},
791 {DBPATCH_VERSION(3010), "3.2 development"},
792 {DBPATCH_VERSION(3020), "3.2 maintenance"},
793 {DBPATCH_VERSION(3030), "3.4 development"},
794 {DBPATCH_VERSION(3040), "3.4 maintenance"},
795 {DBPATCH_VERSION(3050), "4.0 development"},
796 {DBPATCH_VERSION(4000), "4.0 maintenance"},
797 {DBPATCH_VERSION(4010), "4.2 development"},
798 {DBPATCH_VERSION(4020), "4.2 maintenance"},
799 {DBPATCH_VERSION(4030), "4.4 development"},
800 {DBPATCH_VERSION(4040), "4.4 maintenance"},
801 {DBPATCH_VERSION(4050), "5.0 development"},
802 {DBPATCH_VERSION(5000), "5.0 maintenance"},
803 {NULL}
804 };
805
DBget_version(int * mandatory,int * optional)806 static void DBget_version(int *mandatory, int *optional)
807 {
808 DB_RESULT result;
809 DB_ROW row;
810
811 *mandatory = -1;
812 *optional = -1;
813
814 result = DBselect("select mandatory,optional from dbversion");
815
816 if (NULL != (row = DBfetch(result)))
817 {
818 *mandatory = atoi(row[0]);
819 *optional = atoi(row[1]);
820 }
821 DBfree_result(result);
822
823 if (-1 == *mandatory)
824 {
825 zabbix_log(LOG_LEVEL_CRIT, "Cannot get the database version. Exiting ...");
826 exit(EXIT_FAILURE);
827 }
828 }
829
DBcheck_version(void)830 int DBcheck_version(void)
831 {
832 const char *dbversion_table_name = "dbversion";
833 int db_mandatory, db_optional, required, ret = FAIL, i;
834 zbx_db_version_t *dbversion;
835 zbx_dbpatch_t *patches;
836
837 #ifndef HAVE_SQLITE3
838 int total = 0, current = 0, completed, last_completed = -1, optional_num = 0;
839 #endif
840 zabbix_log(LOG_LEVEL_DEBUG, "In %s()", __func__);
841
842 required = ZBX_FIRST_DB_VERSION;
843
844 /* find out the required version number by getting the last mandatory version */
845 /* of the last version patch array */
846 for (dbversion = dbversions; NULL != dbversion->patches; dbversion++)
847 ;
848
849 patches = (--dbversion)->patches;
850
851 for (i = 0; 0 != patches[i].version; i++)
852 {
853 if (0 != patches[i].mandatory)
854 required = patches[i].version;
855 }
856
857 DBconnect(ZBX_DB_CONNECT_NORMAL);
858
859 if (SUCCEED != DBtable_exists(dbversion_table_name))
860 {
861 #ifndef HAVE_SQLITE3
862 zabbix_log(LOG_LEVEL_DEBUG, "%s() \"%s\" does not exist", __func__, dbversion_table_name);
863
864 if (SUCCEED != DBfield_exists("config", "server_check_interval"))
865 {
866 zabbix_log(LOG_LEVEL_CRIT, "Cannot upgrade database: the database must"
867 " correspond to version 2.0 or later. Exiting ...");
868 goto out;
869 }
870
871 if (SUCCEED != DBcreate_dbversion_table())
872 goto out;
873 #else
874 zabbix_log(LOG_LEVEL_CRIT, "The %s does not match Zabbix database."
875 " Current database version (mandatory/optional): UNKNOWN."
876 " Required mandatory version: %08d.",
877 get_program_type_string(program_type), required);
878 zabbix_log(LOG_LEVEL_CRIT, "Zabbix does not support SQLite3 database upgrade.");
879
880 goto out;
881 #endif
882 }
883
884 DBget_version(&db_mandatory, &db_optional);
885
886 #ifndef HAVE_SQLITE3
887 for (dbversion = dbversions; NULL != (patches = dbversion->patches); dbversion++)
888 {
889 for (i = 0; 0 != patches[i].version; i++)
890 {
891 if (0 != patches[i].mandatory)
892 optional_num = 0;
893 else
894 optional_num++;
895
896 if (db_optional < patches[i].version)
897 total++;
898 }
899 }
900
901 if (required < db_mandatory)
902 #else
903 if (required != db_mandatory)
904 #endif
905 {
906 zabbix_log(LOG_LEVEL_CRIT, "The %s does not match Zabbix database."
907 " Current database version (mandatory/optional): %08d/%08d."
908 " Required mandatory version: %08d.",
909 get_program_type_string(program_type), db_mandatory, db_optional, required);
910 #ifdef HAVE_SQLITE3
911 if (required > db_mandatory)
912 zabbix_log(LOG_LEVEL_CRIT, "Zabbix does not support SQLite3 database upgrade.");
913 #endif
914 goto out;
915 }
916
917 zabbix_log(LOG_LEVEL_INFORMATION, "current database version (mandatory/optional): %08d/%08d",
918 db_mandatory, db_optional);
919 zabbix_log(LOG_LEVEL_INFORMATION, "required mandatory version: %08d", required);
920
921 ret = SUCCEED;
922
923 #ifndef HAVE_SQLITE3
924 if (0 == total)
925 goto out;
926
927 if (0 != optional_num)
928 zabbix_log(LOG_LEVEL_INFORMATION, "optional patches were found");
929
930 zabbix_log(LOG_LEVEL_WARNING, "starting automatic database upgrade");
931
932 for (dbversion = dbversions; NULL != dbversion->patches; dbversion++)
933 {
934 patches = dbversion->patches;
935
936 for (i = 0; 0 != patches[i].version; i++)
937 {
938 static sigset_t orig_mask, mask;
939
940 if (db_optional >= patches[i].version)
941 continue;
942
943 /* block signals to prevent interruption of statements that cause an implicit commit */
944 sigemptyset(&mask);
945 sigaddset(&mask, SIGTERM);
946 sigaddset(&mask, SIGINT);
947 sigaddset(&mask, SIGQUIT);
948
949 if (0 > sigprocmask(SIG_BLOCK, &mask, &orig_mask))
950 zabbix_log(LOG_LEVEL_WARNING, "cannot set sigprocmask to block the user signal");
951
952 DBbegin();
953
954 /* skipping the duplicated patches */
955 if ((0 != patches[i].duplicates && patches[i].duplicates <= db_optional) ||
956 SUCCEED == (ret = patches[i].function()))
957 {
958 ret = DBset_version(patches[i].version, patches[i].mandatory);
959 }
960
961 ret = DBend(ret);
962
963 if (0 > sigprocmask(SIG_SETMASK, &orig_mask, NULL))
964 zabbix_log(LOG_LEVEL_WARNING,"cannot restore sigprocmask");
965
966 if (SUCCEED != ret)
967 break;
968
969 current++;
970 completed = (int)(100.0 * current / total);
971
972 if (last_completed != completed)
973 {
974 zabbix_log(LOG_LEVEL_WARNING, "completed %d%% of database upgrade", completed);
975 last_completed = completed;
976 }
977 }
978
979 if (SUCCEED != ret)
980 break;
981 }
982
983 if (SUCCEED == ret)
984 zabbix_log(LOG_LEVEL_WARNING, "database upgrade fully completed");
985 else
986 zabbix_log(LOG_LEVEL_CRIT, "database upgrade failed");
987 #endif /* not HAVE_SQLITE3 */
988
989 out:
990 DBclose();
991
992 zabbix_log(LOG_LEVEL_DEBUG, "End of %s():%s", __func__, zbx_result_string(ret));
993
994 return ret;
995 }
996
DBcheck_double_type(void)997 int DBcheck_double_type(void)
998 {
999 DB_RESULT result;
1000 DB_ROW row;
1001 char *sql = NULL;
1002 const int total_dbl_cols = 4;
1003 int ret = FAIL;
1004
1005 zabbix_log(LOG_LEVEL_DEBUG, "In %s()", __func__);
1006
1007 DBconnect(ZBX_DB_CONNECT_NORMAL);
1008
1009 #if defined(HAVE_MYSQL)
1010 sql = DBdyn_escape_string(CONFIG_DBNAME);
1011 sql = zbx_dsprintf(sql, "select count(*) from information_schema.columns"
1012 " where table_schema='%s' and column_type='double'", sql);
1013 #elif defined(HAVE_POSTGRESQL)
1014 sql = DBdyn_escape_string(NULL == CONFIG_DBSCHEMA || '\0' == *CONFIG_DBSCHEMA ? "public" : CONFIG_DBSCHEMA);
1015 sql = zbx_dsprintf(sql, "select count(*) from information_schema.columns"
1016 " where table_schema='%s' and data_type='double precision'", sql);
1017 #elif defined(HAVE_ORACLE)
1018 sql = zbx_strdup(sql, "select count(*) from user_tab_columns"
1019 " where data_type='BINARY_DOUBLE'");
1020 #elif defined(HAVE_SQLITE3)
1021 /* upgrade patch is not required for sqlite3 */
1022 ret = SUCCEED;
1023 goto out;
1024 #endif
1025
1026 if (NULL == (result = DBselect("%s"
1027 " and ((lower(table_name)='trends'"
1028 " and (lower(column_name) in ('value_min', 'value_avg', 'value_max')))"
1029 " or (lower(table_name)='history' and lower(column_name)='value'))", sql)))
1030 {
1031 zabbix_log(LOG_LEVEL_WARNING, "cannot select records with columns information");
1032 goto out;
1033 }
1034
1035 if (NULL != (row = DBfetch(result)) && total_dbl_cols == atoi(row[0]))
1036 ret = SUCCEED;
1037
1038 DBfree_result(result);
1039 out:
1040 DBclose();
1041 zbx_free(sql);
1042
1043 zabbix_log(LOG_LEVEL_DEBUG, "End of %s()", __func__);
1044
1045 return ret;
1046 }
1047