111f98238c9841d153dacd0675ec86a8efe37026
[public/netxms.git] / src / server / tools / nxdbmgr / upgrade.cpp
1 /*
2 ** nxdbmgr - NetXMS database manager
3 ** Copyright (C) 2004-2013 Victor Kirhenshtein
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., 675 Mass Ave, Cambridge, MA 02139, USA.
18 **
19 ** File: upgrade.cpp
20 **
21 **/
22
23 #include "nxdbmgr.h"
24
25 /**
26 * Externals
27 */
28 BOOL MigrateMaps();
29
30 /**
31 * Create table
32 */
33 static BOOL CreateTable(const TCHAR *pszQuery)
34 {
35 BOOL bResult;
36 String query(pszQuery);
37
38 query.replace(_T("$SQL:TEXT"), g_pszSqlType[g_iSyntax][SQL_TYPE_TEXT]);
39 query.replace(_T("$SQL:TXT4K"), g_pszSqlType[g_iSyntax][SQL_TYPE_TEXT4K]);
40 query.replace(_T("$SQL:INT64"), g_pszSqlType[g_iSyntax][SQL_TYPE_INT64]);
41 if (g_iSyntax == DB_SYNTAX_MYSQL)
42 query += g_pszTableSuffix;
43 bResult = SQLQuery(query);
44 return bResult;
45 }
46
47 /**
48 * Create configuration parameter if it doesn't exist (unless bForceUpdate set to true)
49 */
50 BOOL CreateConfigParam(const TCHAR *pszName, const TCHAR *pszValue,
51 int iVisible, int iNeedRestart, BOOL bForceUpdate)
52 {
53 TCHAR szQuery[1024];
54 DB_RESULT hResult;
55 BOOL bVarExist = FALSE, bResult = TRUE;
56
57 // Check for variable existence
58 _sntprintf(szQuery, 1024, _T("SELECT var_value FROM config WHERE var_name='%s'"), pszName);
59 hResult = DBSelect(g_hCoreDB, szQuery);
60 if (hResult != 0)
61 {
62 if (DBGetNumRows(hResult) > 0)
63 bVarExist = TRUE;
64 DBFreeResult(hResult);
65 }
66
67 if (!bVarExist)
68 {
69 _sntprintf(szQuery, 1024, _T("INSERT INTO config (var_name,var_value,is_visible,")
70 _T("need_server_restart) VALUES (%s,%s,%d,%d)"),
71 (const TCHAR *)DBPrepareString(g_hCoreDB, pszName, 63),
72 (const TCHAR *)DBPrepareString(g_hCoreDB, pszValue, 255), iVisible, iNeedRestart);
73 bResult = SQLQuery(szQuery);
74 }
75 else if (bForceUpdate)
76 {
77 _sntprintf(szQuery, 1024, _T("UPDATE config SET var_value=%s WHERE var_name=%s"),
78 (const TCHAR *)DBPrepareString(g_hCoreDB, pszValue, 255), (const TCHAR *)DBPrepareString(g_hCoreDB, pszName, 63));
79 bResult = SQLQuery(szQuery);
80 }
81 return bResult;
82 }
83
84 /**
85 * Set primary key constraint
86 */
87 static BOOL SetPrimaryKey(const TCHAR *table, const TCHAR *key)
88 {
89 TCHAR query[4096];
90
91 if (g_iSyntax == DB_SYNTAX_SQLITE)
92 return TRUE; // SQLite does not support adding constraints
93
94 _sntprintf(query, 4096, _T("ALTER TABLE %s ADD PRIMARY KEY (%s)"), table, key);
95 return SQLQuery(query);
96 }
97
98 /**
99 * Drop primary key from table
100 */
101 static BOOL DropPrimaryKey(const TCHAR *table)
102 {
103 TCHAR query[1024];
104 DB_RESULT hResult;
105 BOOL success;
106
107 switch(g_iSyntax)
108 {
109 case DB_SYNTAX_ORACLE:
110 case DB_SYNTAX_MYSQL:
111 _sntprintf(query, 1024, _T("ALTER TABLE %s DROP PRIMARY KEY"), table);
112 success = SQLQuery(query);
113 break;
114 case DB_SYNTAX_PGSQL:
115 _sntprintf(query, 1024, _T("ALTER TABLE %s DROP CONSTRAINT %s_pkey"), table, table);
116 success = SQLQuery(query);
117 break;
118 case DB_SYNTAX_MSSQL:
119 success = FALSE;
120 _sntprintf(query, 1024, _T("SELECT name FROM sysobjects WHERE xtype='PK' AND parent_obj=OBJECT_ID('%s')"), table);
121 hResult = SQLSelect(query);
122 if (hResult != NULL)
123 {
124 if (DBGetNumRows(hResult) > 0)
125 {
126 TCHAR objName[512];
127
128 DBGetField(hResult, 0, 0, objName, 512);
129 _sntprintf(query, 1024, _T("ALTER TABLE %s DROP CONSTRAINT %s"), table, objName);
130 success = SQLQuery(query);
131 }
132 DBFreeResult(hResult);
133 }
134 break;
135 default: // Unsupported DB engine
136 success = FALSE;
137 break;
138 }
139 return success;
140 }
141
142 /**
143 * Convert strings from # encoded form to normal form
144 */
145 static BOOL ConvertStrings(const TCHAR *table, const TCHAR *idColumn, const TCHAR *idColumn2, const TCHAR *column, bool isStringId)
146 {
147 DB_RESULT hResult;
148 TCHAR *query;
149 int queryLen = 512;
150 BOOL success = FALSE;
151
152 query = (TCHAR *)malloc(queryLen * sizeof(TCHAR));
153
154 switch(g_iSyntax)
155 {
156 case DB_SYNTAX_MSSQL:
157 _sntprintf(query, queryLen, _T("UPDATE %s SET %s='' WHERE CAST(%s AS nvarchar(4000))=N'#00'"), table, column, column);
158 break;
159 case DB_SYNTAX_ORACLE:
160 _sntprintf(query, queryLen, _T("UPDATE %s SET %s='' WHERE to_char(%s)='#00'"), table, column, column);
161 break;
162 default:
163 _sntprintf(query, queryLen, _T("UPDATE %s SET %s='' WHERE %s='#00'"), table, column, column);
164 break;
165 }
166 if (!SQLQuery(query))
167 {
168 free(query);
169 return FALSE;
170 }
171
172 _sntprintf(query, queryLen, _T("SELECT %s,%s%s%s FROM %s WHERE %s LIKE '%%#%%'"),
173 idColumn, column, (idColumn2 != NULL) ? _T(",") : _T(""), (idColumn2 != NULL) ? idColumn2 : _T(""), table, column);
174 hResult = SQLSelect(query);
175 if (hResult == NULL)
176 {
177 free(query);
178 return FALSE;
179 }
180
181 int count = DBGetNumRows(hResult);
182 for(int i = 0; i < count; i++)
183 {
184 TCHAR *value = DBGetField(hResult, i, 1, NULL, 0);
185 if (_tcschr(value, _T('#')) != NULL)
186 {
187 DecodeSQLString(value);
188 String newValue = DBPrepareString(g_hCoreDB, value);
189 if ((int)newValue.getSize() + 256 > queryLen)
190 {
191 queryLen = newValue.getSize() + 256;
192 query = (TCHAR *)realloc(query, queryLen * sizeof(TCHAR));
193 }
194 if (isStringId)
195 {
196 TCHAR *id = DBGetField(hResult, i, 0, NULL, 0);
197 if (idColumn2 != NULL)
198 {
199 TCHAR *id2 = DBGetField(hResult, i, 2, NULL, 0);
200 _sntprintf(query, queryLen, _T("UPDATE %s SET %s=%s WHERE %s=%s AND %s=%s"),
201 table, column, (const TCHAR *)newValue,
202 idColumn, (const TCHAR *)DBPrepareString(g_hCoreDB, id),
203 idColumn2, (const TCHAR *)DBPrepareString(g_hCoreDB, id2));
204 }
205 else
206 {
207 _sntprintf(query, queryLen, _T("UPDATE %s SET %s=%s WHERE %s=%s"), table, column,
208 (const TCHAR *)newValue, idColumn, (const TCHAR *)DBPrepareString(g_hCoreDB, id));
209 }
210 free(id);
211 }
212 else
213 {
214 INT64 id = DBGetFieldInt64(hResult, i, 0);
215 if (idColumn2 != NULL)
216 {
217 INT64 id2 = DBGetFieldInt64(hResult, i, 2);
218 _sntprintf(query, queryLen, _T("UPDATE %s SET %s=%s WHERE %s=") INT64_FMT _T(" AND %s=") INT64_FMT,
219 table, column, (const TCHAR *)newValue, idColumn, id, idColumn2, id2);
220 }
221 else
222 {
223 _sntprintf(query, queryLen, _T("UPDATE %s SET %s=%s WHERE %s=") INT64_FMT, table, column,
224 (const TCHAR *)newValue, idColumn, id);
225 }
226 }
227 if (!SQLQuery(query))
228 goto cleanup;
229 }
230 }
231 success = TRUE;
232
233 cleanup:
234 DBFreeResult(hResult);
235 free(query);
236 return success;
237 }
238
239 static BOOL ConvertStrings(const TCHAR *table, const TCHAR *idColumn, const TCHAR *column)
240 {
241 return ConvertStrings(table, idColumn, NULL, column, false);
242 }
243
244 /**
245 * Set column nullable (currently only Oracle and PostgreSQL)
246 */
247 static BOOL SetColumnNullable(const TCHAR *table, const TCHAR *column)
248 {
249 TCHAR query[1024] = _T("");
250
251 switch(g_iSyntax)
252 {
253 case DB_SYNTAX_ORACLE:
254 _sntprintf(query, 1024, _T("DECLARE already_null EXCEPTION; ")
255 _T("PRAGMA EXCEPTION_INIT(already_null, -1451); ")
256 _T("BEGIN EXECUTE IMMEDIATE 'ALTER TABLE %s MODIFY %s null'; ")
257 _T("EXCEPTION WHEN already_null THEN null; END;"), table, column);
258 break;
259 case DB_SYNTAX_PGSQL:
260 _sntprintf(query, 1024, _T("ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL"), table, column);
261 break;
262 default:
263 break;
264 }
265
266 return (query[0] != 0) ? SQLQuery(query) : TRUE;
267 }
268
269 /**
270 * Resize varchar column
271 */
272 static BOOL ResizeColumn(const TCHAR *table, const TCHAR *column, int newSize)
273 {
274 TCHAR query[1024];
275
276 switch(g_iSyntax)
277 {
278 case DB_SYNTAX_DB2:
279 _sntprintf(query, 1024, _T("ALTER TABLE %s ALTER COLUMN %s SET DATA TYPE varchar(%d)"), table, column, newSize);
280 break;
281 case DB_SYNTAX_MSSQL:
282 _sntprintf(query, 1024, _T("ALTER TABLE %s ALTER COLUMN %s varchar(%d)"), table, column, newSize);
283 break;
284 case DB_SYNTAX_PGSQL:
285 _sntprintf(query, 1024, _T("ALTER TABLE %s ALTER COLUMN %s TYPE varchar(%d)"), table, column, newSize);
286 break;
287 case DB_SYNTAX_SQLITE:
288 /* TODO: add SQLite support */
289 query[0] = 0;
290 break;
291 default:
292 _sntprintf(query, 1024, _T("ALTER TABLE %s MODIFY %s varchar(%d)"), table, column, newSize);
293 break;
294 }
295
296 return (query[0] != 0) ? SQLQuery(query) : TRUE;
297 }
298
299 /**
300 * Create new event template
301 */
302 static BOOL CreateEventTemplate(int code, const TCHAR *name, int severity, int flags, const TCHAR *message, const TCHAR *description)
303 {
304 TCHAR query[4096];
305
306 _sntprintf(query, 4096, _T("INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description) VALUES (%d,'%s',%d,%d,%s,%s)"),
307 code, name, severity, flags, (const TCHAR *)DBPrepareString(g_hCoreDB, message),
308 (const TCHAR *)DBPrepareString(g_hCoreDB, description));
309 return SQLQuery(query);
310 }
311
312 /**
313 * Upgrade from V272 to V273
314 */
315 static BOOL H_UpgradeFromV272(int currVersion, int newVersion)
316 {
317 CHK_EXEC(CreateConfigParam(_T("DefaultDCIRetentionTime"), _T("30"), 1, 0));
318 CHK_EXEC(CreateConfigParam(_T("DefaultDCIPollingInterval"), _T("60"), 1, 0));
319 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='273' WHERE var_name='SchemaVersion'")));
320 return TRUE;
321 }
322
323 /**
324 * Upgrade from V271 to V272
325 */
326 static BOOL H_UpgradeFromV271(int currVersion, int newVersion)
327 {
328 CHK_EXEC(CreateConfigParam(_T("SNMPTrapLogRetentionTime"), _T("90"), 1, 0));
329 CHK_EXEC(SQLQuery(_T("ALTER TABLE nodes ADD driver_name varchar(32)\n")));
330 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='272' WHERE var_name='SchemaVersion'")));
331 return TRUE;
332 }
333
334 /**
335 * Upgrade from V270 to V271
336 */
337 static BOOL H_UpgradeFromV270(int currVersion, int newVersion)
338 {
339 static TCHAR batch[] =
340 _T("ALTER TABLE object_properties ADD location_accuracy integer\n")
341 _T("ALTER TABLE object_properties ADD location_timestamp integer\n")
342 _T("UPDATE object_properties SET location_accuracy=0,location_timestamp=0\n")
343 _T("<END>");
344 CHK_EXEC(SQLBatch(batch));
345
346 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='271' WHERE var_name='SchemaVersion'")));
347 return TRUE;
348 }
349
350 /**
351 * Upgrade from V269 to V270
352 */
353 static BOOL H_UpgradeFromV269(int currVersion, int newVersion)
354 {
355 static TCHAR batch[] =
356 _T("ALTER TABLE items ADD instd_method integer\n")
357 _T("ALTER TABLE items ADD instd_data varchar(255)\n")
358 _T("ALTER TABLE items ADD instd_filter $SQL:TEXT\n")
359 _T("UPDATE items SET instd_method=0\n")
360 _T("<END>");
361 CHK_EXEC(SQLBatch(batch));
362
363 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='270' WHERE var_name='SchemaVersion'")));
364 return TRUE;
365 }
366
367 /**
368 * Upgrade from V268 to V269
369 */
370 static BOOL H_UpgradeFromV268(int currVersion, int newVersion)
371 {
372 CHK_EXEC(ResizeColumn(_T("alarms"), _T("message"), 2000));
373 CHK_EXEC(ResizeColumn(_T("alarm_events"), _T("message"), 2000));
374 CHK_EXEC(ResizeColumn(_T("event_log"), _T("event_message"), 2000));
375 CHK_EXEC(ResizeColumn(_T("event_cfg"), _T("message"), 2000));
376 CHK_EXEC(ResizeColumn(_T("event_policy"), _T("alarm_message"), 2000));
377 CHK_EXEC(ResizeColumn(_T("items"), _T("name"), 1024));
378 CHK_EXEC(ResizeColumn(_T("dc_tables"), _T("name"), 1024));
379
380 CHK_EXEC(SetColumnNullable(_T("event_policy"), _T("alarm_key")));
381 CHK_EXEC(SetColumnNullable(_T("event_policy"), _T("alarm_message")));
382 CHK_EXEC(SetColumnNullable(_T("event_policy"), _T("comments")));
383 CHK_EXEC(SetColumnNullable(_T("event_policy"), _T("situation_instance")));
384 CHK_EXEC(SetColumnNullable(_T("event_policy"), _T("script")));
385 CHK_EXEC(ConvertStrings(_T("event_policy"), _T("rule_id"), _T("alarm_key")));
386 CHK_EXEC(ConvertStrings(_T("event_policy"), _T("rule_id"), _T("alarm_message")));
387 CHK_EXEC(ConvertStrings(_T("event_policy"), _T("rule_id"), _T("comments")));
388 CHK_EXEC(ConvertStrings(_T("event_policy"), _T("rule_id"), _T("situation_instance")));
389 CHK_EXEC(ConvertStrings(_T("event_policy"), _T("rule_id"), _T("script")));
390
391 CHK_EXEC(SetColumnNullable(_T("policy_situation_attr_list"), _T("attr_value")));
392 // convert strings in policy_situation_attr_list
393 DB_RESULT hResult = SQLSelect(_T("SELECT rule_id,situation_id,attr_name,attr_value FROM policy_situation_attr_list"));
394 if (hResult != NULL)
395 {
396 if (SQLQuery(_T("DELETE FROM policy_situation_attr_list")))
397 {
398 TCHAR name[MAX_DB_STRING], value[MAX_DB_STRING], query[1024];
399 int count = DBGetNumRows(hResult);
400 for(int i = 0; i < count; i++)
401 {
402 LONG ruleId = DBGetFieldLong(hResult, i, 0);
403 LONG situationId = DBGetFieldLong(hResult, i, 1);
404 DBGetField(hResult, i, 2, name, MAX_DB_STRING);
405 DBGetField(hResult, i, 3, value, MAX_DB_STRING);
406
407 DecodeSQLString(name);
408 DecodeSQLString(value);
409
410 if (name[0] == 0)
411 _tcscpy(name, _T("noname"));
412
413 _sntprintf(query, 1024, _T("INSERT INTO policy_situation_attr_list (rule_id,situation_id,attr_name,attr_value) VALUES (%d,%d,%s,%s)"),
414 ruleId, situationId, (const TCHAR *)DBPrepareString(g_hCoreDB, name), (const TCHAR *)DBPrepareString(g_hCoreDB, value));
415 if (!SQLQuery(query))
416 {
417 if (!g_bIgnoreErrors)
418 {
419 DBFreeResult(hResult);
420 return FALSE;
421 }
422 }
423 }
424 }
425 else
426 {
427 if (!g_bIgnoreErrors)
428 {
429 DBFreeResult(hResult);
430 return FALSE;
431 }
432 }
433 DBFreeResult(hResult);
434 }
435 else
436 {
437 if (!g_bIgnoreErrors)
438 return FALSE;
439 }
440
441 CHK_EXEC(SetColumnNullable(_T("event_cfg"), _T("description")));
442 CHK_EXEC(SetColumnNullable(_T("event_cfg"), _T("message")));
443 CHK_EXEC(ConvertStrings(_T("event_cfg"), _T("event_code"), _T("description")));
444 CHK_EXEC(ConvertStrings(_T("event_cfg"), _T("event_code"), _T("message")));
445
446 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='269' WHERE var_name='SchemaVersion'")));
447 return TRUE;
448 }
449
450 /**
451 * Upgrade from V267 to V268
452 */
453 static BOOL H_UpgradeFromV267(int currVersion, int newVersion)
454 {
455 CHK_EXEC(SetColumnNullable(_T("network_services"), _T("check_request")));
456 CHK_EXEC(SetColumnNullable(_T("network_services"), _T("check_responce")));
457 CHK_EXEC(ConvertStrings(_T("network_services"), _T("id"), _T("check_request")));
458 CHK_EXEC(ConvertStrings(_T("network_services"), _T("id"), _T("check_responce")));
459
460 CHK_EXEC(SetColumnNullable(_T("config"), _T("var_value")));
461 CHK_EXEC(ConvertStrings(_T("config"), _T("var_name"), NULL, _T("var_value"), true));
462
463 CHK_EXEC(SetColumnNullable(_T("config"), _T("var_value")));
464 CHK_EXEC(ConvertStrings(_T("config"), _T("var_name"), NULL, _T("var_value"), true));
465
466 CHK_EXEC(SetColumnNullable(_T("dci_schedules"), _T("schedule")));
467 CHK_EXEC(ConvertStrings(_T("dci_schedules"), _T("schedule_id"), _T("item_id"), _T("schedule"), false));
468
469 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='268' WHERE var_name='SchemaVersion'")));
470 return TRUE;
471 }
472
473 /**
474 * Upgrade from V266 to V267
475 */
476 static BOOL H_UpgradeFromV266(int currVersion, int newVersion)
477 {
478 CHK_EXEC(CreateEventTemplate(EVENT_NODE_UNREACHABLE, _T("SYS_NODE_UNREACHABLE"), EVENT_SEVERITY_CRITICAL,
479 EF_LOG, _T("Node unreachable because of network failure"),
480 _T("Generated when node is unreachable by management server because of network failure.\r\nParameters:\r\n No event-specific parameters")));
481 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='267' WHERE var_name='SchemaVersion'")));
482 return TRUE;
483 }
484
485 /**
486 * Upgrade from V265 to V266
487 */
488 static BOOL H_UpgradeFromV265(int currVersion, int newVersion)
489 {
490 // create index on root event ID in event log
491 switch(g_iSyntax)
492 {
493 case DB_SYNTAX_MSSQL:
494 case DB_SYNTAX_PGSQL:
495 CHK_EXEC(SQLQuery(_T("CREATE INDEX idx_event_log_root_id ON event_log(root_event_id) WHERE root_event_id > 0")));
496 break;
497 case DB_SYNTAX_ORACLE:
498 CHK_EXEC(SQLQuery(_T("CREATE OR REPLACE FUNCTION zero_to_null(id NUMBER) ")
499 _T("RETURN NUMBER ")
500 _T("DETERMINISTIC ")
501 _T("AS BEGIN")
502 _T(" IF id > 0 THEN")
503 _T(" RETURN id;")
504 _T(" ELSE")
505 _T(" RETURN NULL;")
506 _T(" END IF;")
507 _T("END;")));
508 CHK_EXEC(SQLQuery(_T("CREATE INDEX idx_event_log_root_id ON event_log(zero_to_null(root_event_id))")));
509 break;
510 default:
511 CHK_EXEC(SQLQuery(_T("CREATE INDEX idx_event_log_root_id ON event_log(root_event_id)")));
512 break;
513 }
514
515 CHK_EXEC(CreateTable(_T("CREATE TABLE mapping_tables (")
516 _T("id integer not null,")
517 _T("name varchar(63) not null,")
518 _T("flags integer not null,")
519 _T("description $SQL:TXT4K null,")
520 _T("PRIMARY KEY(id))")));
521
522 CHK_EXEC(CreateTable(_T("CREATE TABLE mapping_data (")
523 _T("table_id integer not null,")
524 _T("md_key varchar(63) not null,")
525 _T("md_value varchar(255) null,")
526 _T("description $SQL:TXT4K null,")
527 _T("PRIMARY KEY(table_id,md_key))")));
528
529 CHK_EXEC(SQLQuery(_T("DROP TABLE deleted_objects")));
530
531 CHK_EXEC(CreateConfigParam(_T("FirstFreeObjectId"), _T("100"), 0, 1, FALSE));
532
533 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='266' WHERE var_name='SchemaVersion'")));
534 return TRUE;
535 }
536
537 /**
538 * Upgrade from V264 to V265
539 */
540 static BOOL H_UpgradeFromV264(int currVersion, int newVersion)
541 {
542 CHK_EXEC(CreateTable(_T("CREATE TABLE alarm_events (")
543 _T("alarm_id integer not null,")
544 _T("event_id $SQL:INT64 not null,")
545 _T("event_code integer not null,")
546 _T("event_name varchar(63) null,")
547 _T("severity integer not null,")
548 _T("source_object_id integer not null,")
549 _T("event_timestamp integer not null,")
550 _T("message varchar(255) null,")
551 _T("PRIMARY KEY(alarm_id,event_id))")));
552 CHK_EXEC(SQLQuery(_T("CREATE INDEX idx_alarm_events_alarm_id ON alarm_events(alarm_id)")));
553 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='265' WHERE var_name='SchemaVersion'")));
554 return TRUE;
555 }
556
557 /**
558 * Upgrade from V263 to V264
559 */
560 static BOOL H_UpgradeFromV263(int currVersion, int newVersion)
561 {
562 CHK_EXEC(CreateTable(_T("CREATE TABLE mobile_devices (")
563 _T("id integer not null,")
564 _T("device_id varchar(64) not null,")
565 _T("vendor varchar(64) null,")
566 _T("model varchar(128) null,")
567 _T("serial_number varchar(64) null,")
568 _T("os_name varchar(32) null,")
569 _T("os_version varchar(64) null,")
570 _T("user_id varchar(64) null,")
571 _T("battery_level integer not null,")
572 _T("PRIMARY KEY(id))")));
573 CHK_EXEC(CreateConfigParam(_T("MobileDeviceListenerPort"), _T("4747"), 1, 1));
574 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='264' WHERE var_name='SchemaVersion'")));
575 return TRUE;
576 }
577
578 /**
579 * Upgrade from V262 to V263
580 */
581 static BOOL H_UpgradeFromV262(int currVersion, int newVersion)
582 {
583 CHK_EXEC(SQLQuery(_T("ALTER TABLE network_maps ADD radius integer")));
584 CHK_EXEC(SQLQuery(_T("UPDATE network_maps SET radius=-1")));
585 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='263' WHERE var_name='SchemaVersion'")));
586 return TRUE;
587 }
588
589 /**
590 * Upgrade from V261 to V262
591 */
592 static BOOL H_UpgradeFromV261(int currVersion, int newVersion)
593 {
594 CHK_EXEC(CreateConfigParam(_T("ApplyDCIFromTemplateToDisabledDCI"), _T("0"), 1, 1));
595 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='262' WHERE var_name='SchemaVersion'")));
596 return TRUE;
597 }
598
599 /**
600 * Upgrade from V260 to V261
601 */
602 static BOOL H_UpgradeFromV260(int currVersion, int newVersion)
603 {
604 CHK_EXEC(CreateConfigParam(_T("NumberOfBusinessServicePollers"), _T("10"), 1, 1));
605 CHK_EXEC(SQLQuery(_T("DELETE FROM config WHERE var_name='NumberOfEventProcessors'")));
606 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='261' WHERE var_name='SchemaVersion'")));
607 return TRUE;
608 }
609
610 /**
611 * Upgrade from V259 to V260
612 */
613 static BOOL H_UpgradeFromV259(int currVersion, int newVersion)
614 {
615 CHK_EXEC(CreateConfigParam(_T("UseFQDNForNodeNames"), _T("1"), 1, 1));
616 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='260' WHERE var_name='SchemaVersion'")));
617 return TRUE;
618 }
619
620 /**
621 * Upgrade from V258 to V259
622 */
623 static BOOL H_UpgradeFromV258(int currVersion, int newVersion)
624 {
625 // have to made these columns nullable again because
626 // because they was forgotten as NOT NULL in schema.in
627 // and so some databases can still have them as NOT NULL
628 CHK_EXEC(SetColumnNullable(_T("templates"), _T("apply_filter")));
629 CHK_EXEC(SetColumnNullable(_T("containers"), _T("auto_bind_filter")));
630
631 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='259' WHERE var_name='SchemaVersion'")));
632 return TRUE;
633 }
634
635 /**
636 * Upgrade from V257 to V258
637 */
638 static BOOL H_UpgradeFromV257(int currVersion, int newVersion)
639 {
640 static TCHAR batch[] =
641 _T("ALTER TABLE nodes ADD down_since integer\n")
642 _T("UPDATE nodes SET down_since=0\n")
643 _T("<END>");
644
645 CHK_EXEC(SQLBatch(batch));
646
647 CHK_EXEC(CreateConfigParam(_T("DeleteUnreachableNodesPeriod"), _T("0"), 1, 1));
648
649 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='258' WHERE var_name='SchemaVersion'")));
650 return TRUE;
651 }
652
653 /**
654 * Upgrade from V256 to V257
655 */
656 static BOOL H_UpgradeFromV256(int currVersion, int newVersion)
657 {
658 static TCHAR batch[] =
659 _T("ALTER TABLE network_maps ADD bg_color integer\n")
660 _T("ALTER TABLE network_maps ADD link_routing integer\n")
661 _T("UPDATE network_maps SET bg_color=16777215,link_routing=1\n")
662 _T("ALTER TABLE network_map_links ADD routing integer\n")
663 _T("ALTER TABLE network_map_links ADD bend_points $SQL:TXT4K\n")
664 _T("UPDATE network_map_links SET routing=0\n")
665 _T("<END>");
666
667 CHK_EXEC(SQLBatch(batch));
668
669 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='257' WHERE var_name='SchemaVersion'")));
670 return TRUE;
671 }
672
673 /**
674 * Upgrade from V255 to V256
675 */
676 static BOOL H_UpgradeFromV255(int currVersion, int newVersion)
677 {
678 CHK_EXEC(CreateConfigParam(_T("DefaultConsoleDateFormat"), _T("dd.MM.yyyy"), 1, 0));
679 CHK_EXEC(CreateConfigParam(_T("DefaultConsoleTimeFormat"), _T("HH:mm:ss"), 1, 0));
680
681 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='256' WHERE var_name='SchemaVersion'")));
682 return TRUE;
683 }
684
685 /**
686 * Upgrade from V254 to V255
687 */
688 static BOOL H_UpgradeFromV254(int currVersion, int newVersion)
689 {
690 static TCHAR batch[] =
691 _T("ALTER TABLE alarms ADD resolved_by integer\n")
692 _T("UPDATE alarms SET resolved_by=0\n")
693 _T("UPDATE alarms SET alarm_state=3 WHERE alarm_state=2\n")
694 _T("<END>");
695
696 CHK_EXEC(SQLBatch(batch));
697
698 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='255' WHERE var_name='SchemaVersion'")));
699 return TRUE;
700 }
701
702
703 //
704 // Upgrade from V253 to V254
705 //
706
707 static BOOL H_UpgradeFromV253(int currVersion, int newVersion)
708 {
709 static TCHAR batch[] =
710 _T("ALTER TABLE network_maps ADD flags integer\n")
711 _T("ALTER TABLE network_maps ADD link_color integer\n")
712 _T("UPDATE network_maps SET flags=1,link_color=-1\n")
713 _T("ALTER TABLE network_map_links ADD color integer\n")
714 _T("ALTER TABLE network_map_links ADD status_object integer\n")
715 _T("UPDATE network_map_links SET color=-1,status_object=0\n")
716 _T("<END>");
717
718 CHK_EXEC(SQLBatch(batch));
719
720 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='254' WHERE var_name='SchemaVersion'")));
721 return TRUE;
722 }
723
724 /**
725 * Upgrade from V252 to V253
726 */
727 static BOOL H_UpgradeFromV252(int currVersion, int newVersion)
728 {
729 CHK_EXEC(SetColumnNullable(_T("templates"), _T("apply_filter")));
730 CHK_EXEC(ConvertStrings(_T("templates"), _T("id"), _T("apply_filter")));
731
732 CHK_EXEC(SetColumnNullable(_T("containers"), _T("auto_bind_filter")));
733 CHK_EXEC(ConvertStrings(_T("containers"), _T("id"), _T("auto_bind_filter")));
734
735 static TCHAR batch[] =
736 _T("ALTER TABLE templates ADD flags integer\n")
737 _T("UPDATE templates SET flags=0 WHERE enable_auto_apply=0\n")
738 _T("UPDATE templates SET flags=3 WHERE enable_auto_apply<>0\n")
739 _T("ALTER TABLE templates DROP COLUMN enable_auto_apply\n")
740 _T("ALTER TABLE containers ADD flags integer\n")
741 _T("UPDATE containers SET flags=0 WHERE enable_auto_bind=0\n")
742 _T("UPDATE containers SET flags=3 WHERE enable_auto_bind<>0\n")
743 _T("ALTER TABLE containers DROP COLUMN enable_auto_bind\n")
744 _T("<END>");
745 CHK_EXEC(SQLBatch(batch));
746
747 CHK_EXEC(CreateEventTemplate(EVENT_CONTAINER_AUTOBIND, _T("SYS_CONTAINER_AUTOBIND"), EVENT_SEVERITY_NORMAL, 1,
748 _T("Node %2 automatically bound to container %4"),
749 _T("Generated when node bound to container object by autobind rule.\r\n")
750 _T("Parameters:#\r\n")
751 _T(" 1) Node ID\r\n")
752 _T(" 2) Node name\r\n")
753 _T(" 3) Container ID\r\n")
754 _T(" 4) Container name")
755 ));
756
757 CHK_EXEC(CreateEventTemplate(EVENT_CONTAINER_AUTOUNBIND, _T("SYS_CONTAINER_AUTOUNBIND"), EVENT_SEVERITY_NORMAL, 1,
758 _T("Node %2 automatically unbound from container %4"),
759 _T("Generated when node unbound from container object by autobind rule.\r\n")
760 _T("Parameters:#\r\n")
761 _T(" 1) Node ID\r\n")
762 _T(" 2) Node name\r\n")
763 _T(" 3) Container ID\r\n")
764 _T(" 4) Container name")
765 ));
766
767 CHK_EXEC(CreateEventTemplate(EVENT_TEMPLATE_AUTOAPPLY, _T("SYS_TEMPLATE_AUTOAPPLY"), EVENT_SEVERITY_NORMAL, 1,
768 _T("Template %4 automatically applied to node %2"),
769 _T("Generated when template applied to node by autoapply rule.\r\n")
770 _T("Parameters:#\r\n")
771 _T(" 1) Node ID\r\n")
772 _T(" 2) Node name\r\n")
773 _T(" 3) Template ID\r\n")
774 _T(" 4) Template name")
775 ));
776
777 CHK_EXEC(CreateEventTemplate(EVENT_TEMPLATE_AUTOREMOVE, _T("SYS_TEMPLATE_AUTOREMOVE"), EVENT_SEVERITY_NORMAL, 1,
778 _T("Template %4 automatically removed from node %2"),
779 _T("Generated when template removed from node by autoapply rule.\r\n")
780 _T("Parameters:#\r\n")
781 _T(" 1) Node ID\r\n")
782 _T(" 2) Node name\r\n")
783 _T(" 3) Template ID\r\n")
784 _T(" 4) Template name")
785 ));
786
787 TCHAR buffer[64];
788 _sntprintf(buffer, 64, _T("%d"), ConfigReadInt(_T("AllowedCiphers"), 15) + 16);
789 CreateConfigParam(_T("AllowedCiphers"), buffer, 1, 1, TRUE);
790
791 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='253' WHERE var_name='SchemaVersion'")));
792 return TRUE;
793 }
794
795
796 //
797 // Upgrade from V251 to V252
798 //
799
800 static BOOL H_UpgradeFromV251(int currVersion, int newVersion)
801 {
802 static TCHAR batch[] =
803 _T("ALTER TABLE interfaces ADD admin_state integer\n")
804 _T("ALTER TABLE interfaces ADD oper_state integer\n")
805 _T("UPDATE interfaces SET admin_state=0,oper_state=0\n")
806 _T("<END>");
807
808 CHK_EXEC(SQLBatch(batch));
809
810 CHK_EXEC(CreateEventTemplate(EVENT_INTERFACE_UNEXPECTED_UP, _T("SYS_IF_UNEXPECTED_UP"), EVENT_SEVERITY_MAJOR, 1,
811 _T("Interface \"%2\" unexpectedly changed state to UP (IP Addr: %3/%4, IfIndex: %5)"),
812 _T("Generated when interface goes up but it's expected state set to DOWN.\r\n")
813 _T("Please note that source of event is node, not an interface itself.\r\n")
814 _T("Parameters:#\r\n")
815 _T(" 1) Interface object ID\r\n")
816 _T(" 2) Interface name\r\n")
817 _T(" 3) Interface IP address\r\n")
818 _T(" 4) Interface netmask\r\n")
819 _T(" 5) Interface index")
820 ));
821
822 CHK_EXEC(CreateEventTemplate(EVENT_INTERFACE_EXPECTED_DOWN, _T("SYS_IF_EXPECTED_DOWN"), EVENT_SEVERITY_NORMAL, 1,
823 _T("Interface \"%2\" with expected state DOWN changed state to DOWN (IP Addr: %3/%4, IfIndex: %5)"),
824 _T("Generated when interface goes down and it's expected state is DOWN.\r\n")
825 _T("Please note that source of event is node, not an interface itself.\r\n")
826 _T("Parameters:#\r\n")
827 _T(" 1) Interface object ID\r\n")
828 _T(" 2) Interface name\r\n")
829 _T(" 3) Interface IP address\r\n")
830 _T(" 4) Interface netmask\r\n")
831 _T(" 5) Interface index")
832 ));
833
834 // Create rule pair in event processing policy
835 int ruleId = 0;
836 DB_RESULT hResult = SQLSelect(_T("SELECT max(rule_id) FROM event_policy"));
837 if (hResult != NULL)
838 {
839 ruleId = DBGetFieldLong(hResult, 0, 0) + 1;
840 DBFreeResult(hResult);
841 }
842
843 TCHAR query[1024];
844 _sntprintf(query, 1024,
845 _T("INSERT INTO event_policy (rule_id,flags,comments,alarm_message,alarm_severity,alarm_key,")
846 _T("script,alarm_timeout,alarm_timeout_event,situation_id,situation_instance) VALUES ")
847 _T("(%d,7944,'Show alarm when interface is unexpectedly up','%%m',5,'IF_UNEXP_UP_%%i_%%1',")
848 _T("'#00',0,%d,0,'#00')"), ruleId, EVENT_ALARM_TIMEOUT);
849 CHK_EXEC(SQLQuery(query));
850 _sntprintf(query, 1024, _T("INSERT INTO policy_event_list (rule_id,event_code) VALUES (%d,%d)"), ruleId, EVENT_INTERFACE_UNEXPECTED_UP);
851 CHK_EXEC(SQLQuery(query));
852 ruleId++;
853
854 _sntprintf(query, 1024,
855 _T("INSERT INTO event_policy (rule_id,flags,comments,alarm_message,alarm_severity,alarm_key,")
856 _T("script,alarm_timeout,alarm_timeout_event,situation_id,situation_instance) VALUES ")
857 _T("(%d,7944,'Acknowlege interface unexpectedly up alarms when interface goes down','%%m',")
858 _T("6,'IF_UNEXP_UP_%%i_%%1','#00',0,%d,0,'#00')"), ruleId, EVENT_ALARM_TIMEOUT);
859 CHK_EXEC(SQLQuery(query));
860 _sntprintf(query, 1024, _T("INSERT INTO policy_event_list (rule_id,event_code) VALUES (%d,%d)"), ruleId, EVENT_INTERFACE_EXPECTED_DOWN);
861 CHK_EXEC(SQLQuery(query));
862
863 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='252' WHERE var_name='SchemaVersion'")));
864 return TRUE;
865 }
866
867 /**
868 * Upgrade from V250 to V251
869 */
870 static BOOL H_UpgradeFromV250(int currVersion, int newVersion)
871 {
872 static TCHAR batch[] =
873 _T("ALTER TABLE thresholds ADD current_severity integer\n")
874 _T("ALTER TABLE thresholds ADD last_event_timestamp integer\n")
875 _T("UPDATE thresholds SET current_severity=0,last_event_timestamp=0\n")
876 _T("<END>");
877
878 CHK_EXEC(SQLBatch(batch));
879
880 CHK_EXEC(SetColumnNullable(_T("thresholds"), _T("fire_value")));
881 CHK_EXEC(SetColumnNullable(_T("thresholds"), _T("rearm_value")));
882 CHK_EXEC(ConvertStrings(_T("thresholds"), _T("threshold_id"), _T("fire_value")));
883 CHK_EXEC(ConvertStrings(_T("thresholds"), _T("threshold_id"), _T("rearm_value")));
884
885 CHK_EXEC(CreateConfigParam(_T("EnableNXSLContainerFunctions"), _T("1"), 1, 1));
886 CHK_EXEC(CreateConfigParam(_T("UseDNSNameForDiscoveredNodes"), _T("0"), 1, 0));
887 CHK_EXEC(CreateConfigParam(_T("AllowTrapVarbindsConversion"), _T("1"), 1, 1));
888
889 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='251' WHERE var_name='SchemaVersion'")));
890 return TRUE;
891 }
892
893 /**
894 * Upgrade from V249 to V250
895 */
896 static BOOL H_UpgradeFromV249(int currVersion, int newVersion)
897 {
898 CHK_EXEC(CreateTable(_T("CREATE TABLE licenses (")
899 _T("id integer not null,")
900 _T("content $SQL:TEXT null,")
901 _T("PRIMARY KEY(id))")));
902
903 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='250' WHERE var_name='SchemaVersion'")));
904 return TRUE;
905 }
906
907 /**
908 * Upgrade from V248 to V249
909 */
910 #define TDATA_CREATE_QUERY _T("CREATE TABLE tdata_%d (item_id integer not null,tdata_timestamp integer not null,tdata_row integer not null,tdata_column integer not null,tdata_value varchar(255) null)")
911 #define TDATA_INDEX_MSSQL _T("CREATE CLUSTERED INDEX idx_tdata_%d_id_timestamp ON tdata_%d(item_id,tdata_timestamp)")
912 #define TDATA_INDEX_PGSQL _T("CREATE INDEX idx_tdata_%d_timestamp_id ON tdata_%d(tdata_timestamp,item_id)")
913 #define TDATA_INDEX_DEFAULT _T("CREATE INDEX idx_tdata_%d_id_timestamp ON tdata_%d(item_id,tdata_timestamp)")
914
915 static BOOL CreateTData(DWORD nodeId)
916 {
917 TCHAR query[256];
918
919 _sntprintf(query, 256, TDATA_CREATE_QUERY, (int)nodeId);
920 CHK_EXEC(SQLQuery(query));
921
922 switch(g_iSyntax)
923 {
924 case DB_SYNTAX_MSSQL:
925 _sntprintf(query, 256, TDATA_INDEX_MSSQL, (int)nodeId, (int)nodeId);
926 break;
927 case DB_SYNTAX_PGSQL:
928 _sntprintf(query, 256, TDATA_INDEX_PGSQL, (int)nodeId, (int)nodeId);
929 break;
930 default:
931 _sntprintf(query, 256, TDATA_INDEX_DEFAULT, (int)nodeId, (int)nodeId);
932 break;
933 }
934 CHK_EXEC(SQLQuery(query));
935
936 return TRUE;
937 }
938
939 static BOOL H_UpgradeFromV248(int currVersion, int newVersion)
940 {
941 CHK_EXEC(SQLQuery(_T("INSERT INTO metadata (var_name,var_value) VALUES ('TDataTableCreationCommand','") TDATA_CREATE_QUERY _T("')")));
942
943 switch(g_iSyntax)
944 {
945 case DB_SYNTAX_MSSQL:
946 CHK_EXEC(SQLQuery(_T("INSERT INTO metadata (var_name,var_value) VALUES ('TDataIndexCreationCommand_0','") TDATA_INDEX_MSSQL _T("')")));
947 break;
948 case DB_SYNTAX_PGSQL:
949 CHK_EXEC(SQLQuery(_T("INSERT INTO metadata (var_name,var_value) VALUES ('TDataIndexCreationCommand_0','") TDATA_INDEX_PGSQL _T("')")));
950 break;
951 default:
952 CHK_EXEC(SQLQuery(_T("INSERT INTO metadata (var_name,var_value) VALUES ('TDataIndexCreationCommand_0','") TDATA_INDEX_DEFAULT _T("')")));
953 break;
954 }
955
956 CHK_EXEC(CreateTable(_T("CREATE TABLE dct_column_names (")
957 _T("column_id integer not null,")
958 _T("column_name varchar(63) not null,")
959 _T("PRIMARY KEY(column_id))")));
960
961 DB_RESULT hResult = SQLSelect(_T("SELECT id FROM nodes"));
962 if (hResult != NULL)
963 {
964 int count = DBGetNumRows(hResult);
965 for(int i = 0 ; i < count; i++)
966 {
967 if (!CreateTData(DBGetFieldULong(hResult, i, 0)))
968 {
969 if (!g_bIgnoreErrors)
970 {
971 DBFreeResult(hResult);
972 return FALSE;
973 }
974 }
975 }
976 DBFreeResult(hResult);
977 }
978 else
979 {
980 if (!g_bIgnoreErrors)
981 return FALSE;
982 }
983
984 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='249' WHERE var_name='SchemaVersion'")));
985 return TRUE;
986 }
987
988 /**
989 * Upgrade from V247 to V248
990 */
991 static BOOL H_UpgradeFromV247(int currVersion, int newVersion)
992 {
993 CHK_EXEC(CreateTable(_T("CREATE TABLE dc_tables (")
994 _T("item_id integer not null,")
995 _T("node_id integer not null,")
996 _T("template_id integer not null,")
997 _T("template_item_id integer not null,")
998 _T("name varchar(255) null,")
999 _T("instance_column varchar(63) null,")
1000 _T("description varchar(255) null,")
1001 _T("flags integer not null,")
1002 _T("source integer not null,")
1003 _T("snmp_port integer not null,")
1004 _T("polling_interval integer not null,")
1005 _T("retention_time integer not null,")
1006 _T("status integer not null,")
1007 _T("system_tag varchar(255) null,")
1008 _T("resource_id integer not null,")
1009 _T("proxy_node integer not null,")
1010 _T("perftab_settings $SQL:TEXT null,")
1011 _T("PRIMARY KEY(item_id))")));
1012
1013 CHK_EXEC(CreateTable(_T("CREATE TABLE dc_table_columns (")
1014 _T("table_id integer not null,")
1015 _T("column_name varchar(63) not null,")
1016 _T("snmp_oid varchar(1023) null,")
1017 _T("data_type integer not null,")
1018 _T("transformation_script $SQL:TEXT null,")
1019 _T("PRIMARY KEY(table_id,column_name))")));
1020
1021 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='248' WHERE var_name='SchemaVersion'")));
1022 return TRUE;
1023 }
1024
1025 /**
1026 * Upgrade from V246 to V247
1027 */
1028 static BOOL H_UpgradeFromV246(int currVersion, int newVersion)
1029 {
1030 static TCHAR insertQuery[] = _T("INSERT INTO object_custom_attributes (object_id,attr_name,attr_value) VALUES (?,?,?)");
1031
1032 CHK_EXEC(SetColumnNullable(_T("object_custom_attributes"), _T("attr_value")));
1033
1034 // Convert strings in object_custom_attributes table
1035 DB_RESULT hResult = SQLSelect(_T("SELECT object_id,attr_name,attr_value FROM object_custom_attributes"));
1036 if (hResult != NULL)
1037 {
1038 if (SQLQuery(_T("DELETE FROM object_custom_attributes")))
1039 {
1040 TCHAR errorText[DBDRV_MAX_ERROR_TEXT];
1041 DB_STATEMENT hStmt = DBPrepareEx(g_hCoreDB, insertQuery, errorText);
1042 if (hStmt != NULL)
1043 {
1044 TCHAR name[128], *value;
1045 int count = DBGetNumRows(hResult);
1046 for(int i = 0; i < count; i++)
1047 {
1048 DWORD id = DBGetFieldULong(hResult, i, 0);
1049 DBGetField(hResult, i, 1, name, 128);
1050 DecodeSQLString(name);
1051 value = DBGetField(hResult, i, 2, NULL, 0);
1052 DecodeSQLString(value);
1053
1054 DBBind(hStmt, 1, DB_SQLTYPE_INTEGER, id);
1055 DBBind(hStmt, 2, DB_SQLTYPE_VARCHAR, name, DB_BIND_STATIC);
1056 DBBind(hStmt, 3, DB_SQLTYPE_VARCHAR, value, DB_BIND_DYNAMIC);
1057 if (g_bTrace)
1058 ShowQuery(insertQuery);
1059 if (!DBExecuteEx(hStmt, errorText))
1060 {
1061 WriteToTerminalEx(_T("SQL query failed (%s):\n\x1b[33;1m%s\x1b[0m\n"), errorText, insertQuery);
1062 if (!g_bIgnoreErrors)
1063 {
1064 DBFreeStatement(hStmt);
1065 DBFreeResult(hResult);
1066 return FALSE;
1067 }
1068 }
1069 }
1070 DBFreeStatement(hStmt);
1071 }
1072 else
1073 {
1074 WriteToTerminalEx(_T("SQL query failed (%s):\n\x1b[33;1m%s\x1b[0m\n"), errorText, insertQuery);
1075 if (!g_bIgnoreErrors)
1076 {
1077 DBFreeResult(hResult);
1078 return FALSE;
1079 }
1080 }
1081 }
1082 else
1083 {
1084 if (!g_bIgnoreErrors)
1085 {
1086 DBFreeResult(hResult);
1087 return FALSE;
1088 }
1089 }
1090
1091 DBFreeResult(hResult);
1092 }
1093 else
1094 {
1095 if (!g_bIgnoreErrors)
1096 return FALSE;
1097 }
1098
1099 CHK_EXEC(SQLQuery(_T("CREATE INDEX idx_ocattr_oid ON object_custom_attributes(object_id)")));
1100 CHK_EXEC(CreateConfigParam(_T("AlarmHistoryRetentionTime"), _T("180"), 1, 0));
1101
1102 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='247' WHERE var_name='SchemaVersion'")));
1103 return TRUE;
1104 }
1105
1106 /**
1107 * Upgrade from V245 to V246
1108 */
1109 static BOOL H_UpgradeFromV245(int currVersion, int newVersion)
1110 {
1111 static TCHAR batch[] =
1112 _T("ALTER TABLE snmp_trap_pmap ADD flags integer\n")
1113 _T("UPDATE snmp_trap_pmap SET flags=0\n")
1114 _T("<END>");
1115
1116 CHK_EXEC(SQLBatch(batch));
1117
1118 CHK_EXEC(SetColumnNullable(_T("snmp_trap_pmap"), _T("description")));
1119 CHK_EXEC(ConvertStrings(_T("snmp_trap_pmap"), _T("trap_id"), _T("parameter"), _T("description"), false));
1120
1121 CHK_EXEC(SetColumnNullable(_T("cluster_resources"), _T("resource_name")));
1122 CHK_EXEC(ConvertStrings(_T("cluster_resources"), _T("cluster_id"), _T("resource_id"), _T("resource_name"), false));
1123
1124 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='246' WHERE var_name='SchemaVersion'")));
1125 return TRUE;
1126 }
1127
1128 /**
1129 * Upgrade from V244 to V245
1130 */
1131 static BOOL H_UpgradeFromV244(int currVersion, int newVersion)
1132 {
1133 static TCHAR batch[] =
1134 _T("ALTER TABLE nodes ADD runtime_flags integer\n")
1135 _T("UPDATE nodes SET runtime_flags=0\n")
1136 _T("<END>");
1137
1138 CHK_EXEC(SQLBatch(batch));
1139
1140 CHK_EXEC(SetColumnNullable(_T("actions"), _T("rcpt_addr")));
1141 CHK_EXEC(SetColumnNullable(_T("actions"), _T("email_subject")));
1142 CHK_EXEC(SetColumnNullable(_T("actions"), _T("action_data")));
1143
1144 CHK_EXEC(ConvertStrings(_T("actions"), _T("action_id"), _T("rcpt_addr")));
1145 CHK_EXEC(ConvertStrings(_T("actions"), _T("action_id"), _T("email_subject")));
1146 CHK_EXEC(ConvertStrings(_T("actions"), _T("action_id"), _T("action_data")));
1147
1148 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='245' WHERE var_name='SchemaVersion'")));
1149 return TRUE;
1150 }
1151
1152
1153 //
1154 // Upgrade from V243 to V244
1155 //
1156
1157 static BOOL H_UpgradeFromV243(int currVersion, int newVersion)
1158 {
1159 static TCHAR batch[] =
1160 _T("ALTER TABLE interfaces ADD dot1x_pae_state integer\n")
1161 _T("ALTER TABLE interfaces ADD dot1x_backend_state integer\n")
1162 _T("UPDATE interfaces SET dot1x_pae_state=0,dot1x_backend_state=0\n")
1163 _T("<END>");
1164
1165 CHK_EXEC(SQLBatch(batch));
1166
1167 CHK_EXEC(CreateEventTemplate(EVENT_8021X_PAE_STATE_CHANGED, _T("SYS_8021X_PAE_STATE_CHANGED"),
1168 EVENT_SEVERITY_NORMAL, 1, _T("Port %6 PAE state changed from %4 to %2"),
1169 _T("Generated when switch port PAE state changed.\r\nParameters:\r\n")
1170 _T(" 1) New PAE state code\r\n")
1171 _T(" 2) New PAE state as text\r\n")
1172 _T(" 3) Old PAE state code\r\n")
1173 _T(" 4) Old PAE state as text\r\n")
1174 _T(" 5) Interface index\r\n")
1175 _T(" 6) Interface name")));
1176
1177 CHK_EXEC(CreateEventTemplate(EVENT_8021X_BACKEND_STATE_CHANGED, _T("SYS_8021X_BACKEND_STATE_CHANGED"),
1178 EVENT_SEVERITY_NORMAL, 1, _T("Port %6 backend authentication state changed from %4 to %2"),
1179 _T("Generated when switch port backend authentication state changed.\r\nParameters:\r\n")
1180 _T(" 1) New backend state code\r\n")
1181 _T(" 2) New backend state as text\r\n")
1182 _T(" 3) Old backend state code\r\n")
1183 _T(" 4) Old backend state as text\r\n")
1184 _T(" 5) Interface index\r\n")
1185 _T(" 6) Interface name")));
1186
1187 CHK_EXEC(CreateEventTemplate(EVENT_8021X_PAE_FORCE_UNAUTH, _T("SYS_8021X_PAE_FORCE_UNAUTH"),
1188 EVENT_SEVERITY_MAJOR, 1, _T("Port %2 switched to force unauthorize state"),
1189 _T("Generated when switch port PAE state changed to FORCE UNAUTHORIZE.\r\nParameters:\r\n")
1190 _T(" 1) Interface index\r\n")
1191 _T(" 2) Interface name")));
1192
1193 CHK_EXEC(CreateEventTemplate(EVENT_8021X_AUTH_FAILED, _T("SYS_8021X_AUTH_FAILED"),
1194 EVENT_SEVERITY_MAJOR, 1, _T("802.1x authentication failed on port %2"),
1195 _T("Generated when switch port backend authentication state changed to FAIL.\r\nParameters:\r\n")
1196 _T(" 1) Interface index\r\n")
1197 _T(" 2) Interface name")));
1198
1199 CHK_EXEC(CreateEventTemplate(EVENT_8021X_AUTH_TIMEOUT, _T("SYS_8021X_AUTH_TIMEOUT"),
1200 EVENT_SEVERITY_MAJOR, 1, _T("802.1x authentication time out on port %2"),
1201 _T("Generated when switch port backend authentication state changed to TIMEOUT.\r\nParameters:\r\n")
1202 _T(" 1) Interface index\r\n")
1203 _T(" 2) Interface name")));
1204
1205 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='244' WHERE var_name='SchemaVersion'")));
1206 return TRUE;
1207 }
1208
1209
1210 //
1211 // Upgrade from V242 to V243
1212 //
1213
1214 static BOOL H_UpgradeFromV242(int currVersion, int newVersion)
1215 {
1216 static TCHAR batch[] =
1217 _T("ALTER TABLE items ADD snmp_raw_value_type integer\n")
1218 _T("UPDATE items SET snmp_raw_value_type=0\n")
1219 _T("ALTER TABLE items ADD flags integer\n")
1220 _T("UPDATE items SET flags=adv_schedule+(all_thresholds*2)\n")
1221 _T("ALTER TABLE items DROP COLUMN adv_schedule\n")
1222 _T("ALTER TABLE items DROP COLUMN all_thresholds\n")
1223 _T("<END>");
1224
1225 CHK_EXEC(SQLBatch(batch));
1226 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='243' WHERE var_name='SchemaVersion'")));
1227 return TRUE;
1228 }
1229
1230
1231 //
1232 // Upgrade from V241 to V242
1233 //
1234
1235 static BOOL H_UpgradeFromV241(int currVersion, int newVersion)
1236 {
1237 static TCHAR batch[] =
1238 _T("DROP TABLE business_service_templates\n")
1239 _T("ALTER TABLE dashboards ADD options integer\n")
1240 _T("UPDATE dashboards SET options=0\n")
1241 _T("<END>");
1242
1243 CHK_EXEC(SQLBatch(batch));
1244 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='242' WHERE var_name='SchemaVersion'")));
1245 return TRUE;
1246 }
1247
1248
1249 //
1250 // Upgrade from V240 to V241
1251 //
1252
1253 static BOOL H_UpgradeFromV240(int currVersion, int newVersion)
1254 {
1255 static TCHAR batch[] =
1256 _T("ALTER TABLE slm_checks ADD template_id integer\n")
1257 _T("ALTER TABLE slm_checks ADD current_ticket integer\n")
1258 _T("UPDATE slm_checks SET template_id=0,current_ticket=0\n")
1259 _T("<END>");
1260
1261 CHK_EXEC(SQLBatch(batch));
1262 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='241' WHERE var_name='SchemaVersion'")));
1263 return TRUE;
1264 }
1265
1266
1267 //
1268 // Upgrade from V239 to V240
1269 //
1270
1271 static BOOL H_UpgradeFromV239(int currVersion, int newVersion)
1272 {
1273 CHK_EXEC(SQLQuery(_T("ALTER TABLE raw_dci_values ADD transformed_value varchar(255)")));
1274 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='240' WHERE var_name='SchemaVersion'")));
1275 return TRUE;
1276 }
1277
1278
1279 //
1280 // Upgrade from V238 to V239
1281 //
1282
1283 static BOOL H_UpgradeFromV238(int currVersion, int newVersion)
1284 {
1285 CHK_EXEC(SQLQuery(
1286 _T("INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description) VALUES ")
1287 _T("(56,'SYS_IP_ADDRESS_CHANGED',1,1,'Primary IP address changed from %2 to %1',")
1288 _T("'Generated when primary IP address changed (usually because of primary name change or DNS change).#0D#0A")
1289 _T("Parameters:#0D#0A 1) New IP address#0D#0A 2) Old IP address#0D#0A 3) Primary host name')")));
1290
1291 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='239' WHERE var_name='SchemaVersion'")));
1292 return TRUE;
1293 }
1294
1295
1296 //
1297 // Upgrade from V232 to V238
1298 //
1299
1300 static BOOL H_UpgradeFromV232toV238(int currVersion, int newVersion)
1301 {
1302 CHK_EXEC(CreateTable(_T("CREATE TABLE slm_checks (")
1303 _T("id integer not null,")
1304 _T("type integer not null,")
1305 _T("content $SQL:TEXT null,")
1306 _T("threshold_id integer not null,")
1307 _T("reason varchar(255) null,")
1308 _T("is_template integer not null,")
1309 _T("PRIMARY KEY(id))")));
1310
1311 CHK_EXEC(CreateTable(_T("CREATE TABLE slm_tickets (")
1312 _T("ticket_id integer not null,")
1313 _T("service_id integer not null,")
1314 _T("check_id integer not null,")
1315 _T("create_timestamp integer not null,")
1316 _T("close_timestamp integer not null,")
1317 _T("reason varchar(255) null,")
1318 _T("PRIMARY KEY(ticket_id))")));
1319
1320 CHK_EXEC(CreateTable(_T("CREATE TABLE slm_service_history (")
1321 _T("record_id integer not null,")
1322 _T("service_id integer not null,")
1323 _T("change_timestamp integer not null,")
1324 _T("new_status integer not null,")
1325 _T("PRIMARY KEY(record_id))")));
1326
1327 CHK_EXEC(CreateTable(_T("CREATE TABLE report_results (")
1328 _T("report_id integer not null,")
1329 _T("generated integer not null,")
1330 _T("job_id integer not null,")
1331 _T("PRIMARY KEY(report_id,job_id))")));
1332
1333 CHK_EXEC(CreateTable(_T("CREATE TABLE reports (")
1334 _T("id integer not null,")
1335 _T("definition $SQL:TEXT null,")
1336 _T("PRIMARY KEY(id))")));
1337
1338 CHK_EXEC(CreateTable(_T("CREATE TABLE job_history (")
1339 _T("id integer not null,")
1340 _T("time_created integer not null,")
1341 _T("time_started integer not null,")
1342 _T("time_finished integer not null,")
1343 _T("job_type varchar(127) null,")
1344 _T("description varchar(255) null,")
1345 _T("additional_info varchar(255) null,")
1346 _T("node_id integer not null,")
1347 _T("user_id integer not null,")
1348 _T("status integer not null,")
1349 _T("failure_message varchar(255) null,")
1350 _T("PRIMARY KEY(id))")));
1351
1352 CHK_EXEC(CreateTable(_T("CREATE TABLE business_services (")
1353 _T("service_id integer not null,")
1354 _T("PRIMARY KEY(service_id))")));
1355
1356 CHK_EXEC(CreateTable(_T("CREATE TABLE business_service_templates (")
1357 _T("service_id integer not null,")
1358 _T("template_id integer not null,")
1359 _T("PRIMARY KEY(service_id,template_id))")));
1360
1361 CHK_EXEC(CreateTable(_T("CREATE TABLE node_links (")
1362 _T("nodelink_id integer not null,")
1363 _T("node_id integer not null,")
1364 _T("PRIMARY KEY(nodelink_id))")));
1365
1366 CHK_EXEC(CreateTable(_T("CREATE TABLE slm_agreements (")
1367 _T("agreement_id integer not null,")
1368 _T("service_id integer not null,")
1369 _T("org_id integer not null,")
1370 _T("uptime varchar(63) not null,")
1371 _T("period integer not null,")
1372 _T("start_date integer not null,")
1373 _T("notes varchar(255),")
1374 _T("PRIMARY KEY(agreement_id))")));
1375
1376 CHK_EXEC(CreateTable(_T("CREATE TABLE organizations (")
1377 _T("id integer not null,")
1378 _T("parent_id integer not null,")
1379 _T("org_type integer not null,")
1380 _T("name varchar(63) not null,")
1381 _T("description varchar(255),")
1382 _T("manager integer not null,")
1383 _T("PRIMARY KEY(id))")));
1384
1385 CHK_EXEC(CreateTable(_T("CREATE TABLE persons (")
1386 _T("id integer not null,")
1387 _T("org_id integer not null,")
1388 _T("first_name varchar(63),")
1389 _T("last_name varchar(63),")
1390 _T("title varchar(255),")
1391 _T("status integer not null,")
1392 _T("PRIMARY KEY(id))")));
1393
1394 CHK_EXEC(CreateConfigParam(_T("JobHistoryRetentionTime"), _T("90"), 1, 0));
1395
1396 CHK_EXEC(SQLQuery(_T("UPDATE event_cfg SET description=")
1397 _T("'Generated when threshold check is rearmed for specific data collection item.#0D#0A")
1398 _T("Parameters:#0D#0A")
1399 _T(" 1) Parameter name#0D#0A")
1400 _T(" 2) Item description#0D#0A")
1401 _T(" 3) Data collection item ID#0D#0A")
1402 _T(" 4) Instance#0D#0A")
1403 _T(" 5) Threshold value#0D#0A")
1404 _T(" 6) Actual value' WHERE event_code=18")));
1405
1406 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='238' WHERE var_name='SchemaVersion'")));
1407 return TRUE;
1408 }
1409
1410
1411 //
1412 // Upgrade from V237 to V238
1413 //
1414
1415 static BOOL H_UpgradeFromV237(int currVersion, int newVersion)
1416 {
1417 static TCHAR batch[] =
1418 _T("DROP TABLE slm_check_templates\n")
1419 _T("DROP TABLE node_link_checks\n")
1420 _T("DROP TABLE slm_checks\n")
1421 _T("DROP TABLE slm_tickets\n")
1422 _T("<END>");
1423
1424 CHK_EXEC(SQLBatch(batch));
1425
1426 CHK_EXEC(CreateTable(_T("CREATE TABLE slm_checks (")
1427 _T("id integer not null,")
1428 _T("type integer not null,")
1429 _T("content $SQL:TEXT null,")
1430 _T("threshold_id integer not null,")
1431 _T("reason varchar(255) null,")
1432 _T("is_template integer not null,")
1433 _T("PRIMARY KEY(id))")));
1434
1435 CHK_EXEC(CreateTable(_T("CREATE TABLE slm_tickets (")
1436 _T("ticket_id integer not null,")
1437 _T("service_id integer not null,")
1438 _T("check_id integer not null,")
1439 _T("create_timestamp integer not null,")
1440 _T("close_timestamp integer not null,")
1441 _T("reason varchar(255) null,")
1442 _T("PRIMARY KEY(ticket_id))")));
1443
1444 CHK_EXEC(CreateTable(_T("CREATE TABLE slm_service_history (")
1445 _T("record_id integer not null,")
1446 _T("service_id integer not null,")
1447 _T("change_timestamp integer not null,")
1448 _T("new_status integer not null,")
1449 _T("PRIMARY KEY(record_id))")));
1450
1451 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='238' WHERE var_name='SchemaVersion'")));
1452 return TRUE;
1453 }
1454
1455
1456 //
1457 // Upgrade from V236 to V237
1458 //
1459
1460 static BOOL H_UpgradeFromV236(int currVersion, int newVersion)
1461 {
1462 static TCHAR batch[] =
1463 _T("ALTER TABLE business_services DROP COLUMN name\n")
1464 _T("ALTER TABLE business_services DROP COLUMN parent_id\n")
1465 _T("ALTER TABLE business_services DROP COLUMN status\n")
1466 _T("ALTER TABLE slm_checks DROP COLUMN name\n")
1467 _T("<END>");
1468
1469 CHK_EXEC(SQLBatch(batch));
1470
1471 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='237' WHERE var_name='SchemaVersion'")));
1472 return TRUE;
1473 }
1474
1475
1476 //
1477 // Upgrade from V235 to V236
1478 //
1479
1480 static BOOL H_UpgradeFromV235(int currVersion, int newVersion)
1481 {
1482 CHK_EXEC(CreateTable(_T("CREATE TABLE report_results (")
1483 _T("report_id integer not null,")
1484 _T("generated integer not null,")
1485 _T("job_id integer not null,")
1486 _T("PRIMARY KEY(report_id,job_id))")));
1487
1488 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='236' WHERE var_name='SchemaVersion'")));
1489 return TRUE;
1490 }
1491
1492
1493 //
1494 // Upgrade from V234 to V235
1495 //
1496
1497 static BOOL H_UpgradeFromV234(int currVersion, int newVersion)
1498 {
1499 CHK_EXEC(CreateTable(_T("CREATE TABLE reports (")
1500 _T("id integer not null,")
1501 _T("definition $SQL:TEXT null,")
1502 _T("PRIMARY KEY(id))")));
1503
1504 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='235' WHERE var_name='SchemaVersion'")));
1505 return TRUE;
1506 }
1507
1508
1509 //
1510 // Upgrade from V233 to V234
1511 //
1512
1513 static BOOL H_UpgradeFromV233(int currVersion, int newVersion)
1514 {
1515 CHK_EXEC(CreateTable(_T("CREATE TABLE job_history (")
1516 _T("id integer not null,")
1517 _T("time_created integer not null,")
1518 _T("time_started integer not null,")
1519 _T("time_finished integer not null,")
1520 _T("job_type varchar(127) null,")
1521 _T("description varchar(255) null,")
1522 _T("additional_info varchar(255) null,")
1523 _T("node_id integer not null,")
1524 _T("user_id integer not null,")
1525 _T("status integer not null,")
1526 _T("failure_message varchar(255) null,")
1527 _T("PRIMARY KEY(id))")));
1528
1529 CHK_EXEC(CreateConfigParam(_T("JobHistoryRetentionTime"), _T("90"), 1, 0));
1530
1531 CHK_EXEC(SQLQuery(_T("UPDATE event_cfg SET description=")
1532 _T("'Generated when threshold check is rearmed for specific data collection item.#0D#0A")
1533 _T("Parameters:#0D#0A")
1534 _T(" 1) Parameter name#0D#0A")
1535 _T(" 2) Item description#0D#0A")
1536 _T(" 3) Data collection item ID#0D#0A")
1537 _T(" 4) Instance#0D#0A")
1538 _T(" 5) Threshold value#0D#0A")
1539 _T(" 6) Actual value' WHERE event_code=18")));
1540
1541 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='234' WHERE var_name='SchemaVersion'")));
1542 return TRUE;
1543 }
1544
1545
1546 //
1547 // Upgrade from V232 to V233
1548 //
1549
1550 static BOOL H_UpgradeFromV232(int currVersion, int newVersion)
1551 {
1552 CHK_EXEC(CreateTable(_T("CREATE TABLE business_services (")
1553 _T("service_id integer not null,")
1554 _T("name varchar(63) not null,")
1555 _T("parent_id integer not null,")
1556 _T("status integer not null,")
1557 _T("PRIMARY KEY(service_id))")));
1558
1559 CHK_EXEC(CreateTable(_T("CREATE TABLE business_service_templates (")
1560 _T("service_id integer not null,")
1561 _T("template_id integer not null,")
1562 _T("PRIMARY KEY(service_id,template_id))")));
1563
1564 CHK_EXEC(CreateTable(_T("CREATE TABLE slm_checks (")
1565 _T("check_id integer not null,")
1566 _T("name varchar(63) not null,")
1567 _T("type integer not null,")
1568 _T("content $SQL:TEXT,")
1569 _T("threshold_id integer not null,")
1570 _T("state integer not null,")
1571 _T("reason varchar(255) not null,")
1572 _T("PRIMARY KEY(check_id))")));
1573
1574 CHK_EXEC(CreateTable(_T("CREATE TABLE slm_check_templates (")
1575 _T("id integer not null,")
1576 _T("name varchar(63) not null,")
1577 _T("type integer not null,")
1578 _T("content $SQL:TEXT,")
1579 _T("threshold_id integer not null,")
1580 _T("PRIMARY KEY(id))")));
1581
1582 CHK_EXEC(CreateTable(_T("CREATE TABLE node_links (")
1583 _T("nodelink_id integer not null,")
1584 _T("node_id integer not null,")
1585 _T("PRIMARY KEY(nodelink_id))")));
1586
1587 CHK_EXEC(CreateTable(_T("CREATE TABLE node_link_checks (")
1588 _T("nodelink_id integer not null,")
1589 _T("check_id integer not null,")
1590 _T("PRIMARY KEY(nodelink_id,check_id))")));
1591
1592 CHK_EXEC(CreateTable(_T("CREATE TABLE slm_agreements (")
1593 _T("agreement_id integer not null,")
1594 _T("service_id integer not null,")
1595 _T("org_id integer not null,")
1596 _T("uptime varchar(63) not null,")
1597 _T("period integer not null,")
1598 _T("start_date integer not null,")
1599 _T("notes varchar(255),")
1600 _T("PRIMARY KEY(agreement_id))")));
1601
1602 CHK_EXEC(CreateTable(_T("CREATE TABLE slm_tickets (")
1603 _T("ticket_id integer not null,")
1604 _T("service_id integer not null,")
1605 _T("create_timestamp integer not null,")
1606 _T("close_timestamp integer not null,")
1607 _T("reason varchar(255) not null,")
1608 _T("PRIMARY KEY(ticket_id))")));
1609
1610 CHK_EXEC(CreateTable(_T("CREATE TABLE organizations (")
1611 _T("id integer not null,")
1612 _T("parent_id integer not null,")
1613 _T("org_type integer not null,")
1614 _T("name varchar(63) not null,")
1615 _T("description varchar(255),")
1616 _T("manager integer not null,")
1617 _T("PRIMARY KEY(id))")));
1618
1619 CHK_EXEC(CreateTable(_T("CREATE TABLE persons (")
1620 _T("id integer not null,")
1621 _T("org_id integer not null,")
1622 _T("first_name varchar(63),")
1623 _T("last_name varchar(63),")
1624 _T("title varchar(255),")
1625 _T("status integer not null,")
1626 _T("PRIMARY KEY(id))")));
1627
1628 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='233' WHERE var_name='SchemaVersion'")));
1629 return TRUE;
1630 }
1631
1632
1633 //
1634 // Upgrade from V231 to V232
1635 //
1636
1637 static BOOL H_UpgradeFromV231(int currVersion, int newVersion)
1638 {
1639 static TCHAR batch[] =
1640 _T("ALTER TABLE object_properties ADD submap_id integer\n")
1641 _T("UPDATE object_properties SET submap_id=0\n")
1642 _T("DROP TABLE maps\n")
1643 _T("DROP TABLE map_access_lists\n")
1644 _T("DROP TABLE submaps\n")
1645 _T("DROP TABLE submap_object_positions\n")
1646 _T("DROP TABLE submap_links\n")
1647 _T("<END>");
1648
1649 CHK_EXEC(SQLBatch(batch));
1650
1651 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='232' WHERE var_name='SchemaVersion'")));
1652 return TRUE;
1653 }
1654
1655
1656 //
1657 // Upgrade from V230 to V231
1658 //
1659
1660 static BOOL H_UpgradeFromV230(int currVersion, int newVersion)
1661 {
1662 static TCHAR batch[] =
1663 _T("ALTER TABLE nodes ADD bridge_base_addr varchar(15)\n")
1664 _T("UPDATE nodes SET bridge_base_addr='000000000000'\n")
1665 _T("<END>");
1666
1667 CHK_EXEC(SQLBatch(batch));
1668
1669 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='231' WHERE var_name='SchemaVersion'")));
1670 return TRUE;
1671 }
1672
1673
1674 //
1675 // Upgrade from V229 to V230
1676 //
1677
1678 static BOOL H_UpgradeFromV229(int currVersion, int newVersion)
1679 {
1680 static TCHAR batch1[] =
1681 _T("ALTER TABLE network_maps ADD bg_latitude varchar(20)\n")
1682 _T("ALTER TABLE network_maps ADD bg_longitude varchar(20)\n")
1683 _T("ALTER TABLE network_maps ADD bg_zoom integer\n")
1684 _T("ALTER TABLE dashboard_elements ADD layout_data $SQL:TEXT\n")
1685 _T("<END>");
1686
1687 CHK_EXEC(SQLBatch(batch1));
1688
1689 DB_RESULT hResult = SQLSelect(_T("SELECT dashboard_id,element_id,horizontal_span,vertical_span,horizontal_alignment,vertical_alignment FROM dashboard_elements"));
1690 if (hResult != NULL)
1691 {
1692 TCHAR query[1024], xml[1024];
1693
1694 int count = DBGetNumRows(hResult);
1695 for(int i = 0; i < count; i++)
1696 {
1697 _sntprintf(xml, 1024, _T("<layout><horizontalSpan>%d</horizontalSpan><verticalSpan>%d</verticalSpan><horizontalAlignment>%d</horizontalAlignment><verticalAlignment>%d</verticalAlignment></layout>"),
1698 (int)DBGetFieldLong(hResult, i, 2), (int)DBGetFieldLong(hResult, i, 3),
1699 (int)DBGetFieldLong(hResult, i, 4), (int)DBGetFieldLong(hResult, i, 5));
1700 _sntprintf(query, 1024, _T("UPDATE dashboard_elements SET layout_data=%s WHERE dashboard_id=%d AND element_id=%d"),
1701 (const TCHAR *)DBPrepareString(g_hCoreDB, xml), (int)DBGetFieldLong(hResult, i, 0), (int)DBGetFieldLong(hResult, i, 1));
1702 CHK_EXEC(SQLQuery(query));
1703 }
1704 DBFreeResult(hResult);
1705 }
1706 else
1707 {
1708 if (!g_bIgnoreErrors)
1709 return FALSE;
1710 }
1711
1712 static TCHAR batch2[] =
1713 _T("ALTER TABLE dashboard_elements DROP COLUMN horizontal_span\n")
1714 _T("ALTER TABLE dashboard_elements DROP COLUMN vertical_span\n")
1715 _T("ALTER TABLE dashboard_elements DROP COLUMN horizontal_alignment\n")
1716 _T("ALTER TABLE dashboard_elements DROP COLUMN vertical_alignment\n")
1717 _T("<END>");
1718
1719 CHK_EXEC(SQLBatch(batch2));
1720
1721 CreateConfigParam(_T("TileServerURL"), _T("http://tile.openstreetmap.org/"), 1, 0);
1722
1723 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='230' WHERE var_name='SchemaVersion'")));
1724 return TRUE;
1725 }
1726
1727
1728 //
1729 // Upgrade from V228 to V229
1730 //
1731
1732 static BOOL H_UpgradeFromV228(int currVersion, int newVersion)
1733 {
1734 CHK_EXEC(CreateTable(_T("CREATE TABLE dashboards (")
1735 _T(" id integer not null,")
1736 _T(" num_columns integer not null,")
1737 _T(" PRIMARY KEY(id))")));
1738
1739 CHK_EXEC(CreateTable(_T("CREATE TABLE dashboard_elements (")
1740 _T(" dashboard_id integer not null,")
1741 _T(" element_id integer not null,")
1742 _T(" element_type integer not null,")
1743 _T(" element_data $SQL:TEXT null,")
1744 _T(" horizontal_span integer not null,")
1745 _T(" vertical_span integer not null,")
1746 _T(" horizontal_alignment integer not null,")
1747 _T(" vertical_alignment integer not null,")
1748 _T(" PRIMARY KEY(dashboard_id,element_id))")));
1749
1750 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='229' WHERE var_name='SchemaVersion'")));
1751 return TRUE;
1752 }
1753
1754
1755 //
1756 // Upgrade from V227 to V228
1757 //
1758
1759 static BOOL H_UpgradeFromV227(int currVersion, int newVersion)
1760 {
1761 CHK_EXEC(SQLQuery(_T("DROP TABLE web_maps")));
1762 CHK_EXEC(MigrateMaps());
1763 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='228' WHERE var_name='SchemaVersion'")));
1764 return TRUE;
1765 }
1766
1767
1768 //
1769 // Upgrade from V226 to V227
1770 //
1771
1772 static BOOL H_UpgradeFromV226(int currVersion, int newVersion)
1773 {
1774 static TCHAR batch[] =
1775 _T("ALTER TABLE clusters ADD zone_guid integer\n")
1776 _T("UPDATE clusters SET zone_guid=0\n")
1777 _T("<END>");
1778
1779 CHK_EXEC(SQLBatch(batch));
1780
1781 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='227' WHERE var_name='SchemaVersion'")));
1782 return TRUE;
1783 }
1784
1785
1786 //
1787 // Upgrade from V225 to V226
1788 //
1789
1790 static BOOL H_UpgradeFromV225(int currVersion, int newVersion)
1791 {
1792 static TCHAR batch[] =
1793 _T("ALTER TABLE interfaces ADD flags integer\n")
1794 _T("UPDATE interfaces SET flags=0\n")
1795 _T("UPDATE interfaces SET flags=1 WHERE synthetic_mask<>0\n")
1796 _T("ALTER TABLE interfaces DROP COLUMN synthetic_mask\n")
1797 _T("<END>");
1798
1799 CHK_EXEC(SQLBatch(batch));
1800
1801 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='226' WHERE var_name='SchemaVersion'")));
1802 return TRUE;
1803 }
1804
1805
1806 //
1807 // Upgrade from V224 to V225
1808 //
1809
1810 static BOOL H_UpgradeFromV224(int currVersion, int newVersion)
1811 {
1812 static TCHAR batch[] =
1813 _T("ALTER TABLE interfaces ADD description varchar(255)\n")
1814 _T("UPDATE interfaces SET description=''\n")
1815 _T("<END>");
1816
1817 CHK_EXEC(SQLBatch(batch));
1818
1819 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='225' WHERE var_name='SchemaVersion'")));
1820 return TRUE;
1821 }
1822
1823
1824 //
1825 // Upgrade from V223 to V224
1826 //
1827
1828 static BOOL H_UpgradeFromV223(int currVersion, int newVersion)
1829 {
1830 static TCHAR batch[] =
1831 _T("DROP TABLE zone_ip_addr_list\n")
1832 _T("ALTER TABLE zones DROP COLUMN zone_type\n")
1833 _T("ALTER TABLE zones DROP COLUMN controller_ip\n")
1834 _T("ALTER TABLE zones ADD agent_proxy integer\n")
1835 _T("ALTER TABLE zones ADD snmp_proxy integer\n")
1836 _T("ALTER TABLE zones ADD icmp_proxy integer\n")
1837 _T("UPDATE zones SET agent_proxy=0,snmp_proxy=0,icmp_proxy=0\n")
1838 _T("<END>");
1839
1840 CHK_EXEC(SQLBatch(batch));
1841
1842 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='224' WHERE var_name='SchemaVersion'")));
1843 return TRUE;
1844 }
1845
1846
1847 //
1848 // Upgrade from V222 to V223
1849 //
1850
1851 static BOOL H_UpgradeFromV222(int currVersion, int newVersion)
1852 {
1853 static TCHAR batch[] =
1854 _T("DROP TABLE oid_to_type\n")
1855 _T("ALTER TABLE nodes DROP COLUMN node_type\n")
1856 _T("ALTER TABLE nodes ADD primary_name varchar(255)\n")
1857 _T("UPDATE nodes SET primary_name=primary_ip\n")
1858 _T("<END>");
1859
1860 CHK_EXEC(SQLBatch(batch));
1861
1862 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='223' WHERE var_name='SchemaVersion'")));
1863 return TRUE;
1864 }
1865
1866
1867 //
1868 // Upgrade from V221 to V222
1869 //
1870
1871 static BOOL H_UpgradeFromV221(int currVersion, int newVersion)
1872 {
1873 static TCHAR batch[] =
1874 _T("ALTER TABLE object_properties ADD image varchar(36)\n")
1875 _T("UPDATE object_properties SET image='00000000-0000-0000-0000-000000000000'\n")
1876 _T("<END>");
1877
1878 CHK_EXEC(SQLBatch(batch));
1879
1880 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='222' WHERE var_name='SchemaVersion'")));
1881 return TRUE;
1882 }
1883
1884
1885 //
1886 // Upgrade from V220 to V221
1887 //
1888
1889 static BOOL H_UpgradeFromV220(int currVersion, int newVersion)
1890 {
1891 static TCHAR batch[] =
1892 _T("ALTER TABLE network_maps DROP COLUMN background\n")
1893 _T("ALTER TABLE network_maps ADD background varchar(36)\n")
1894 _T("<END>");
1895
1896 CHK_EXEC(SQLBatch(batch));
1897
1898 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='221' WHERE var_name='SchemaVersion'")));
1899 return TRUE;
1900 }
1901
1902
1903 //
1904 // Upgrade from V219 to V220
1905 //
1906
1907 static BOOL H_UpgradeFromV219(int currVersion, int newVersion)
1908 {
1909 static TCHAR batch[] =
1910 _T("ALTER TABLE interfaces ADD bridge_port integer\n")
1911 _T("ALTER TABLE interfaces ADD phy_slot integer\n")
1912 _T("ALTER TABLE interfaces ADD phy_port integer\n")
1913 _T("ALTER TABLE interfaces ADD peer_node_id integer\n")
1914 _T("ALTER TABLE interfaces ADD peer_if_id integer\n")
1915 _T("UPDATE interfaces SET bridge_port=0,phy_slot=0,phy_port=0,peer_node_id=0,peer_if_id=0\n")
1916 _T("ALTER TABLE nodes ADD snmp_sys_name varchar(127)\n")
1917 _T("UPDATE nodes SET snmp_sys_name=''\n")
1918 _T("<END>");
1919
1920 CHK_EXEC(SQLBatch(batch));
1921
1922 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='220' WHERE var_name='SchemaVersion'")));
1923 return TRUE;
1924 }
1925
1926
1927 //
1928 // Upgrade from V218 to V219
1929 //
1930
1931 static BOOL H_UpgradeFromV218(int currVersion, int newVersion)
1932 {
1933 CHK_EXEC(CreateTable(_T("CREATE TABLE images (")
1934 _T(" guid varchar(36) not null,")
1935 _T(" mimetype varchar(64) not null,")
1936 _T(" name varchar(255) not null,")
1937 _T(" category varchar(255) not null,")
1938 _T(" protected integer default 0,")
1939 _T(" ")
1940 _T(" PRIMARY KEY(guid),")
1941 _T(" UNIQUE(name, category))")));
1942
1943 static TCHAR batch[] =
1944 _T("INSERT INTO images (guid, mimetype, name, category, protected) VALUES ")
1945 _T("('1ddb76a3-a05f-4a42-acda-22021768feaf', 'image/png', 'ATM', 'Network Objects', 1)\n")
1946 _T("INSERT INTO images (guid, mimetype, name, category, protected) VALUES ")
1947 _T("('b314cf44-b2aa-478e-b23a-73bc5bb9a624', 'image/png', 'HSM', 'Network Objects', 1)\n")
1948 _T("INSERT INTO images (guid, mimetype, name, category, protected) VALUES ")
1949 _T("('904e7291-ee3f-41b7-8132-2bd29288ecc8', 'image/png', 'Node', 'Network Objects', 1)\n")
1950 _T("INSERT INTO images (guid, mimetype, name, category, protected) VALUES ")
1951 _T("('f5214d16-1ab1-4577-bb21-063cfd45d7af', 'image/png', 'Printer', 'Network Objects', 1)\n")
1952 _T("INSERT INTO images (guid, mimetype, name, category, protected) VALUES ")
1953 _T("('bacde727-b183-4e6c-8dca-ab024c88b999', 'image/png', 'Router', 'Network Objects', 1)\n")
1954 _T("INSERT INTO images (guid, mimetype, name, category, protected) VALUES ")
1955 _T("('ba6ab507-f62d-4b8f-824c-ca9d46f22375', 'image/png', 'Server', 'Network Objects', 1)\n")
1956 _T("INSERT INTO images (guid, mimetype, name, category, protected) VALUES ")
1957 _T("('092e4b35-4e7c-42df-b9b7-d5805bfac64e', 'image/png', 'Service', 'Network Objects', 1)\n")
1958 _T("INSERT INTO images (guid, mimetype, name, category, protected) VALUES ")
1959 _T("('f9105c54-8dcf-483a-b387-b4587dfd3cba', 'image/png', 'Switch', 'Network Objects', 1)\n")
1960 _T("INSERT INTO images (guid, mimetype, name, category, protected) VALUES ")
1961 _T("('7cd999e9-fbe0-45c3-a695-f84523b3a50c', 'image/png', 'Unknown', 'Network Objects', 1)\n")
1962 _T("<END>");
1963
1964 CHK_EXEC(SQLBatch(batch));
1965
1966 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='219' WHERE var_name='SchemaVersion'")));
1967 return TRUE;
1968 }
1969
1970 /**
1971 * Upgrade from V217 to V218
1972 */
1973 static BOOL H_UpgradeFromV217(int currVersion, int newVersion)
1974 {
1975 CHK_EXEC(SetColumnNullable(_T("snmp_communities"), _T("community")));
1976 CHK_EXEC(ConvertStrings(_T("snmp_communities"), _T("id"), _T("community")));
1977
1978 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='218' WHERE var_name='SchemaVersion'")));
1979 return TRUE;
1980 }
1981
1982 /**
1983 * Upgrade from V216 to V217
1984 */
1985 static BOOL H_UpgradeFromV216(int currVersion, int newVersion)
1986 {
1987 static TCHAR batch[] =
1988 _T("ALTER TABLE nodes ADD snmp_port integer\n")
1989 _T("UPDATE nodes SET snmp_port=161\n")
1990 _T("ALTER TABLE items ADD snmp_port integer\n")
1991 _T("UPDATE items SET snmp_port=0\n")
1992 _T("<END>");
1993
1994 CHK_EXEC(SQLBatch(batch));
1995
1996 CHK_EXEC(SetColumnNullable(_T("nodes"), _T("community")));
1997 CHK_EXEC(ConvertStrings(_T("nodes"), _T("id"), _T("community")));
1998
1999 CHK_EXEC(SetColumnNullable(_T("nodes"), _T("usm_auth_password")));
2000 CHK_EXEC(ConvertStrings(_T("nodes"), _T("id"), _T("usm_auth_password")));
2001
2002 CHK_EXEC(SetColumnNullable(_T("nodes"), _T("usm_priv_password")));
2003 CHK_EXEC(ConvertStrings(_T("nodes"), _T("id"), _T("usm_priv_password")));
2004
2005 CHK_EXEC(SetColumnNullable(_T("nodes"), _T("snmp_oid")));
2006 CHK_EXEC(ConvertStrings(_T("nodes"), _T("id"), _T("snmp_oid")));
2007
2008 CHK_EXEC(SetColumnNullable(_T("nodes"), _T("secret")));
2009 CHK_EXEC(ConvertStrings(_T("nodes"), _T("id"), _T("secret")));
2010
2011 CHK_EXEC(SetColumnNullable(_T("nodes"), _T("agent_version")));
2012 CHK_EXEC(ConvertStrings(_T("nodes"), _T("id"), _T("agent_version")));
2013
2014 CHK_EXEC(SetColumnNullable(_T("nodes"), _T("platform_name")));
2015 CHK_EXEC(ConvertStrings(_T("nodes"), _T("id"), _T("platform_name")));
2016
2017 CHK_EXEC(SetColumnNullable(_T("nodes"), _T("uname")));
2018 CHK_EXEC(ConvertStrings(_T("nodes"), _T("id"), _T("uname")));
2019
2020 CHK_EXEC(SetColumnNullable(_T("items"), _T("name")));
2021 CHK_EXEC(ConvertStrings(_T("items"), _T("item_id"), _T("name")));
2022
2023 CHK_EXEC(SetColumnNullable(_T("items"), _T("description")));
2024 CHK_EXEC(ConvertStrings(_T("items"), _T("item_id"), _T("description")));
2025
2026 CHK_EXEC(SetColumnNullable(_T("items"), _T("transformation")));
2027 CHK_EXEC(ConvertStrings(_T("items"), _T("item_id"), _T("transformation")));
2028
2029 CHK_EXEC(SetColumnNullable(_T("items"), _T("instance")));
2030 CHK_EXEC(ConvertStrings(_T("items"), _T("item_id"), _T("instance")));
2031
2032 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='217' WHERE var_name='SchemaVersion'")));
2033 return TRUE;
2034 }
2035
2036 /**
2037 * Upgrade from V215 to V216
2038 */
2039 static BOOL H_UpgradeFromV215(int currVersion, int newVersion)
2040 {
2041 CHK_EXEC(SetColumnNullable(_T("ap_common"), _T("description")));
2042 CHK_EXEC(ConvertStrings(_T("ap_common"), _T("id"), _T("description")));
2043
2044 if (g_iSyntax != DB_SYNTAX_SQLITE)
2045 CHK_EXEC(SQLQuery(_T("ALTER TABLE ap_config_files DROP COLUMN file_name")));
2046
2047 CHK_EXEC(SetColumnNullable(_T("ap_config_files"), _T("file_content")));
2048 CHK_EXEC(ConvertStrings(_T("ap_config_files"), _T("policy_id"), _T("file_content")));
2049
2050 CHK_EXEC(SQLQuery(_T("ALTER TABLE object_properties ADD guid varchar(36)")));
2051
2052 // Generate GUIDs for all objects
2053 DB_RESULT hResult = SQLSelect(_T("SELECT object_id FROM object_properties"));
2054 if (hResult != NULL)
2055 {
2056 int count = DBGetNumRows(hResult);
2057 for(int i = 0; i < count; i++)
2058 {
2059 uuid_t guid;
2060 TCHAR query[256], buffer[64];
2061
2062 uuid_generate(guid);
2063 _sntprintf(query, 256, _T("UPDATE object_properties SET guid='%s' WHERE object_id=%d"),
2064 uuid_to_string(guid, buffer), DBGetFieldULong(hResult, i, 0));
2065 CHK_EXEC(SQLQuery(query));
2066 }
2067 DBFreeResult(hResult);
2068 }
2069
2070 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='216' WHERE var_name='SchemaVersion'")));
2071 return TRUE;
2072 }
2073
2074
2075 //
2076 // Upgrade from V214 to V215
2077 //
2078
2079 static BOOL H_UpgradeFromV214(int currVersion, int newVersion)
2080 {
2081 CHK_EXEC(CreateTable(_T("CREATE TABLE network_maps (")
2082 _T("id integer not null,")
2083 _T("map_type integer not null,")
2084 _T("layout integer not null,")
2085 _T("seed integer not null,")
2086 _T("background integer not null,")
2087 _T("PRIMARY KEY(id))")));
2088
2089 CHK_EXEC(CreateTable(_T("CREATE TABLE network_map_elements (")
2090 _T("map_id integer not null,")
2091 _T("element_id integer not null,")
2092 _T("element_type integer not null,")
2093 _T("element_data $SQL:TEXT not null,")
2094 _T("PRIMARY KEY(map_id,element_id))")));
2095
2096 CHK_EXEC(CreateTable(_T("CREATE TABLE network_map_links (")
2097 _T("map_id integer not null,")
2098 _T("element1 integer not null,")
2099 _T("element2 integer not null,")
2100 _T("link_type integer not null,")
2101 _T("link_name varchar(255) null,")
2102 _T("connector_name1 varchar(255) null,")
2103 _T("connector_name2 varchar(255) null,")
2104 _T("PRIMARY KEY(map_id,element1,element2))")));
2105
2106 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='215' WHERE var_name='SchemaVersion'")));
2107 return TRUE;
2108 }
2109
2110 /**
2111 * Upgrade from V213 to V214
2112 */
2113 static BOOL H_UpgradeFromV213(int currVersion, int newVersion)
2114 {
2115 CHK_EXEC(SetColumnNullable(_T("script_library"), _T("script_code")));
2116 CHK_EXEC(ConvertStrings(_T("script_library"), _T("script_id"), _T("script_code")));
2117
2118 CHK_EXEC(SetColumnNullable(_T("raw_dci_values"), _T("raw_value")));
2119 CHK_EXEC(ConvertStrings(_T("raw_dci_values"), _T("item_id"), _T("raw_value")));
2120
2121 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='CREATE TABLE idata_%d (item_id integer not null,idata_timestamp integer not null,idata_value varchar(255) null)' WHERE var_name='IDataTableCreationCommand'")));
2122
2123 DB_RESULT hResult = SQLSelect(_T("SELECT id FROM nodes"));
2124 if (hResult != NULL)
2125 {
2126 int count = DBGetNumRows(hResult);
2127 for(int i = 0; i < count; i++)
2128 {
2129 TCHAR table[32];
2130
2131 DWORD nodeId = DBGetFieldULong(hResult, i, 0);
2132 _sntprintf(table, 32, _T("idata_%d"), nodeId);
2133 CHK_EXEC(SetColumnNullable(table, _T("idata_value")));
2134 }
2135 DBFreeResult(hResult);
2136 }
2137
2138 // Convert values for string DCIs from # encoded to normal form
2139 hResult = SQLSelect(_T("SELECT node_id,item_id FROM items WHERE datatype=4"));
2140 if (hResult != NULL)
2141 {
2142 int count = DBGetNumRows(hResult);
2143 for(int i = 0; i < count; i++)
2144 {
2145 TCHAR query[512];
2146
2147 DWORD nodeId = DBGetFieldULong(hResult, i, 0);
2148 DWORD dciId = DBGetFieldULong(hResult, i, 1);
2149
2150 if (IsNodeExist(nodeId))
2151 {
2152 _sntprintf(query, 512, _T("SELECT idata_timestamp,idata_value FROM idata_%d WHERE item_id=%d AND idata_value LIKE '%%#%%'"), nodeId, dciId);
2153 DB_RESULT hData = SQLSelect(query);
2154 if (hData != NULL)
2155 {
2156 int valueCount = DBGetNumRows(hData);
2157 for(int j = 0; j < valueCount; j++)
2158 {
2159 TCHAR buffer[MAX_DB_STRING];
2160
2161 LONG ts = DBGetFieldLong(hData, j, 0);
2162 DBGetField(hData, j, 1, buffer, MAX_DB_STRING);
2163 DecodeSQLString(buffer);
2164
2165 _sntprintf(query, 512, _T("UPDATE idata_%d SET idata_value=%s WHERE item_id=%d AND idata_timestamp=%ld"),
2166 nodeId, (const TCHAR *)DBPrepareString(g_hCoreDB, buffer), dciId, (long)ts);
2167 CHK_EXEC(SQLQuery(query));
2168 }
2169 DBFreeResult(hData);
2170 }
2171 }
2172 }
2173 DBFreeResult(hResult);
2174 }
2175
2176 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='214' WHERE var_name='SchemaVersion'")));
2177 return TRUE;
2178 }
2179
2180 /**
2181 * Upgrade from V212 to V213
2182 */
2183 static BOOL H_UpgradeFromV212(int currVersion, int newVersion)
2184 {
2185 CHK_EXEC(SetColumnNullable(_T("items"), _T("custom_units_name")));
2186 CHK_EXEC(SetColumnNullable(_T("items"), _T("perftab_settings")));
2187
2188 CHK_EXEC(ConvertStrings(_T("items"), _T("item_id"), _T("custom_units_name")));
2189 CHK_EXEC(ConvertStrings(_T("items"), _T("item_id"), _T("perftab_settings")));
2190
2191 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='213' WHERE var_name='SchemaVersion'")));
2192
2193 return TRUE;
2194 }
2195
2196 /**
2197 * Upgrade from V211 to V212
2198 */
2199 static BOOL H_UpgradeFromV211(int currVersion, int newVersion)
2200 {
2201 CHK_EXEC(SetColumnNullable(_T("snmp_trap_cfg"), _T("snmp_oid")));
2202 CHK_EXEC(SetColumnNullable(_T("snmp_trap_cfg"), _T("user_tag")));
2203 CHK_EXEC(SetColumnNullable(_T("snmp_trap_cfg"), _T("description")));
2204
2205 CHK_EXEC(ConvertStrings(_T("snmp_trap_cfg"), _T("trap_id"), _T("user_tag")));
2206 CHK_EXEC(ConvertStrings(_T("snmp_trap_cfg"), _T("trap_id"), _T("description")));
2207
2208 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='212' WHERE var_name='SchemaVersion'")));
2209
2210 return TRUE;
2211 }
2212
2213 /**
2214 * Upgrade from V210 to V211
2215 */
2216 static BOOL H_UpgradeFromV210(int currVersion, int newVersion)
2217 {
2218 static TCHAR batch[] =
2219 _T("INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description) VALUES")
2220 _T(" (53,'SYS_DCI_UNSUPPORTED',2,1,'Status of DCI %1 (%5: %2) changed to UNSUPPORTED',")
2221 _T("'Generated when DCI status changed to UNSUPPORTED.#0D#0AParameters:#0D#0A")
2222 _T(" 1) DCI ID#0D#0A 2) DCI Name#0D#0A 3) DCI Description#0D#0A 4) DCI Origin code#0D#0A 5) DCI Origin name')\n")
2223 _T("INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description) VALUES")
2224 _T(" (54,'SYS_DCI_DISABLED',1,1,'Status of DCI %1 (%5: %2) changed to DISABLED',")
2225 _T("'Generated when DCI status changed to DISABLED.#0D#0AParameters:#0D#0A")
2226 _T(" 1) DCI ID#0D#0A 2) DCI Name#0D#0A 3) DCI Description#0D#0A 4) DCI Origin code#0D#0A 5) DCI Origin name')\n")
2227 _T("INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description) VALUES")
2228 _T(" (55,'SYS_DCI_ACTIVE',0,1,'Status of DCI %1 (%5: %2) changed to ACTIVE',")
2229 _T("'Generated when DCI status changed to ACTIVE.#0D#0AParameters:#0D#0A")
2230 _T(" 1) DCI ID#0D#0A 2) DCI Name#0D#0A 3) DCI Description#0D#0A 4) DCI Origin code#0D#0A 5) DCI Origin name')\n")
2231 _T("<END>");
2232
2233 CHK_EXEC(SQLBatch(batch));
2234 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='211' WHERE var_name='SchemaVersion'")));
2235
2236 return TRUE;
2237 }
2238
2239
2240 //
2241 // Upgrade from V209 to V210
2242 //
2243
2244 static BOOL H_UpgradeFromV209(int currVersion, int newVersion)
2245 {
2246 if (!SQLQuery(_T("DELETE FROM metadata WHERE var_name like 'IDataIndexCreationCommand_%'")))
2247 if (!g_bIgnoreErrors)
2248 return FALSE;
2249
2250 const TCHAR *query;
2251 switch(g_iSyntax)
2252 {
2253 case DB_SYNTAX_PGSQL:
2254 query = _T("INSERT INTO metadata (var_name,var_value) VALUES ('IDataIndexCreationCommand_0','CREATE INDEX idx_idata_%d_timestamp_id ON idata_%d(idata_timestamp,item_id)')");
2255 break;
2256 case DB_SYNTAX_MSSQL:
2257 query = _T("INSERT INTO metadata (var_name,var_value) VALUES ('IDataIndexCreationCommand_0','CREATE CLUSTERED INDEX idx_idata_%d_id_timestamp ON idata_%d(item_id,idata_timestamp)')");
2258 break;
2259 default:
2260 query = _T("INSERT INTO metadata (var_name,var_value) VALUES ('IDataIndexCreationCommand_0','CREATE INDEX idx_idata_%d_id_timestamp ON idata_%d(item_id,idata_timestamp)')");
2261 break;
2262 }
2263
2264 if (!SQLQuery(query))
2265 if (!g_bIgnoreErrors)
2266 return FALSE;
2267
2268 ReindexIData();
2269
2270 if (!SQLQuery(_T("UPDATE metadata SET var_value='210' WHERE var_name='SchemaVersion'")))
2271 if (!g_bIgnoreErrors)
2272 return FALSE;
2273
2274 return TRUE;
2275 }
2276
2277
2278 //
2279 // Upgrade from V208 to V209
2280 //
2281
2282 static BOOL H_UpgradeFromV208(int currVersion, int newVersion)
2283 {
2284 static TCHAR batch[] =
2285 _T("ALTER TABLE users ADD auth_failures integer\n")
2286 _T("ALTER TABLE users ADD last_passwd_change integer\n")
2287 _T("ALTER TABLE users ADD min_passwd_length integer\n")
2288 _T("ALTER TABLE users ADD disabled_until integer\n")
2289 _T("ALTER TABLE users ADD last_login integer\n")
2290 _T("ALTER TABLE users ADD password_history $SQL:TEXT\n")
2291 _T("UPDATE users SET auth_failures=0,last_passwd_change=0,min_passwd_length=-1,disabled_until=0,last_login=0\n")
2292 _T("<END>");
2293
2294 if (!SQLBatch(batch))
2295 if (!g_bIgnoreErrors)
2296 return FALSE;
2297
2298 if (!CreateConfigParam(_T("PasswordHistoryLength"), _T("0"), 1, 0))
2299 if (!g_bIgnoreErrors)
2300 return FALSE;
2301
2302 if (!CreateConfigParam(_T("IntruderLockoutThreshold"), _T("0"), 1, 0))
2303 if (!g_bIgnoreErrors)
2304 return FALSE;
2305
2306 if (!CreateConfigParam(_T("IntruderLockoutTime"), _T("30"), 1, 0))
2307 if (!g_bIgnoreErrors)
2308 return FALSE;
2309
2310 if (!CreateConfigParam(_T("MinPasswordLength"), _T("0"), 1, 0))
2311 if (!g_bIgnoreErrors)
2312 return FALSE;
2313
2314 if (!CreateConfigParam(_T("PasswordComplexity"), _T("0"), 1, 0))
2315 if (!g_bIgnoreErrors)
2316 return FALSE;
2317
2318 if (!CreateConfigParam(_T("PasswordExpiration"), _T("0"), 1, 0))
2319 if (!g_bIgnoreErrors)
2320 return FALSE;
2321
2322 if (!CreateConfigParam(_T("BlockInactiveUserAccounts"), _T("0"), 1, 0))
2323 if (!g_bIgnoreErrors)
2324 return FALSE;
2325
2326 if (!SQLQuery(_T("UPDATE metadata SET var_value='209' WHERE var_name='SchemaVersion'")))
2327 if (!g_bIgnoreErrors)
2328 return FALSE;
2329
2330 return TRUE;
2331 }
2332
2333
2334 //
2335 // Upgrade from V207 to V208
2336 //
2337
2338 static BOOL H_UpgradeFromV207(int currVersion, int newVersion)
2339 {
2340 if (!SQLQuery(_T("ALTER TABLE items ADD system_tag varchar(255)")))
2341 if (!g_bIgnoreErrors)
2342 return FALSE;
2343
2344 if (!SQLQuery(_T("UPDATE metadata SET var_value='208' WHERE var_name='SchemaVersion'")))
2345 if (!g_bIgnoreErrors)
2346 return FALSE;
2347
2348 return TRUE;
2349 }
2350
2351
2352 //
2353 // Upgrade from V206 to V207
2354 //
2355
2356 static BOOL H_UpgradeFromV206(int currVersion, int newVersion)
2357 {
2358 if (!CreateConfigParam(_T("RADIUSSecondaryServer"), _T("none"), 1, 0))
2359 if (!g_bIgnoreErrors)
2360 return FALSE;
2361
2362 if (!CreateConfigParam(_T("RADIUSSecondarySecret"), _T("netxms"), 1, 0))
2363 if (!g_bIgnoreErrors)
2364 return FALSE;
2365
2366 if (!CreateConfigParam(_T("RADIUSSecondaryPort"), _T("1645"), 1, 0))
2367 if (!g_bIgnoreErrors)
2368 return FALSE;
2369
2370 if (!CreateConfigParam(_T("ExternalAuditServer"), _T("none"), 1, 1))
2371 if (!g_bIgnoreErrors)
2372 return FALSE;
2373
2374 if (!CreateConfigParam(_T("ExternalAuditPort"), _T("514"), 1, 1))
2375 if (!g_bIgnoreErrors)
2376 return FALSE;
2377
2378 if (!CreateConfigParam(_T("ExternalAuditFacility"), _T("13"), 1, 1))
2379 if (!g_bIgnoreErrors)
2380 return FALSE;
2381
2382 if (!CreateConfigParam(_T("ExternalAuditSeverity"), _T("5"), 1, 1))
2383 if (!g_bIgnoreErrors)
2384 return FALSE;
2385
2386 if (!CreateConfigParam(_T("ExternalAuditTag"), _T("netxmsd-audit"), 1, 1))
2387 if (!g_bIgnoreErrors)
2388 return FALSE;
2389
2390 if (!SQLQuery(_T("UPDATE metadata SET var_value='207' WHERE var_name='SchemaVersion'")))
2391 if (!g_bIgnoreErrors)
2392 return FALSE;
2393
2394 return TRUE;
2395 }
2396
2397
2398 //
2399 // Upgrade from V205 to V206
2400 //
2401
2402 static BOOL H_UpgradeFromV205(int currVersion, int newVersion)
2403 {
2404 if (g_iSyntax == DB_SYNTAX_ORACLE)
2405 {
2406 static TCHAR oraBatch[] =
2407 _T("ALTER TABLE audit_log MODIFY message null\n")
2408 _T("ALTER TABLE event_log MODIFY event_message null\n")
2409 _T("ALTER TABLE event_log MODIFY user_tag null\n")
2410 _T("ALTER TABLE syslog MODIFY hostname null\n")
2411 _T("ALTER TABLE syslog MODIFY msg_tag null\n")
2412 _T("ALTER TABLE syslog MODIFY msg_text null\n")
2413 _T("ALTER TABLE snmp_trap_log MODIFY trap_varlist null\n")
2414 _T("<END>");
2415
2416 if (!SQLBatch(oraBatch))
2417 if (!g_bIgnoreErrors)
2418 return FALSE;
2419 }
2420
2421 bool clearLogs = GetYesNo(_T("This database upgrade requires log conversion. This can take significant amount of time ")
2422 _T("(up to few hours for large databases). If preserving all log records is not very important, it is ")
2423 _T("recommended to clear logs befor conversion. Clear logs?"));
2424
2425 if (clearLogs)
2426 {
2427 if (!SQLQuery(_T("DELETE FROM audit_log")))
2428 if (!g_bIgnoreErrors)
2429 return FALSE;
2430
2431 if (!SQLQuery(_T("DELETE FROM event_log")))
2432 if (!g_bIgnoreErrors)
2433 return FALSE;
2434
2435 if (!SQLQuery(_T("DELETE FROM syslog")))
2436 if (!g_bIgnoreErrors)
2437 return FALSE;
2438
2439 if (!SQLQuery(_T("DELETE FROM snmp_trap_log")))
2440 if (!g_bIgnoreErrors)
2441 return FALSE;
2442 }
2443 else
2444 {
2445 // Convert event log
2446 if (!ConvertStrings(_T("event_log"), _T("event_id"), _T("event_message")))
2447 if (!g_bIgnoreErrors)
2448 return FALSE;
2449 if (!ConvertStrings(_T("event_log"), _T("event_id"), _T("user_tag")))
2450 if (!g_bIgnoreErrors)
2451 return FALSE;
2452
2453 // Convert audit log
2454 if (!ConvertStrings(_T("audit_log"), _T("record_id"), _T("message")))
2455 if (!g_bIgnoreErrors)
2456 return FALSE;
2457
2458 // Convert syslog
2459 if (!ConvertStrings(_T("syslog"), _T("msg_id"), _T("msg_text")))
2460 if (!g_bIgnoreErrors)
2461 return FALSE;
2462
2463 // Convert SNMP trap log
2464 if (!ConvertStrings(_T("snmp_trap_log"), _T("trap_id"), _T("trap_varlist")))
2465 if (!g_bIgnoreErrors)
2466 return FALSE;
2467 }
2468
2469 if (!SQLQuery(_T("UPDATE metadata SET var_value='206' WHERE var_name='SchemaVersion'")))
2470 if (!g_bIgnoreErrors)
2471 return FALSE;
2472
2473 return TRUE;
2474 }
2475
2476
2477 //
2478 // Upgrade from V204 to V205
2479 //
2480
2481 static BOOL H_UpgradeFromV204(int currVersion, int newVersion)
2482 {
2483 if (!CreateTable(_T("CREATE TABLE usm_credentials (")
2484 _T("id integer not null,")
2485 _T("user_name varchar(255) not null,")
2486 _T("auth_method integer not null,")
2487 _T("priv_method integer not null,")
2488 _T("auth_password varchar(255),")
2489 _T("priv_password varchar(255),")
2490 _T("PRIMARY KEY(id))")))
2491 if (!g_bIgnoreErrors)
2492 return FALSE;
2493
2494 if (!SQLQuery(_T("UPDATE metadata SET var_value='205' WHERE var_name='SchemaVersion'")))
2495 if (!g_bIgnoreErrors)
2496 return FALSE;
2497
2498 return TRUE;
2499 }
2500
2501
2502 //
2503 // Upgrade from V203 to V204
2504 //
2505
2506 static BOOL H_UpgradeFromV203(int currVersion, int newVersion)
2507 {
2508 static TCHAR batch[] =
2509 _T("ALTER TABLE object_properties ADD location_type integer\n")
2510 _T("ALTER TABLE object_properties ADD latitude varchar(20)\n")
2511 _T("ALTER TABLE object_properties ADD longitude varchar(20)\n")
2512 _T("UPDATE object_properties SET location_type=0\n")
2513 _T("ALTER TABLE object_properties DROP COLUMN image_id\n")
2514 _T("<END>");
2515
2516 if (!SQLBatch(batch))
2517 if (!g_bIgnoreErrors)
2518 return FALSE;
2519
2520 if (!CreateConfigParam(_T("ConnectionPoolBaseSize"), _T("5"), 1, 1))
2521 if (!g_bIgnoreErrors)
2522 return FALSE;
2523
2524 if (!CreateConfigParam(_T("ConnectionPoolMaxSize"), _T("20"), 1, 1))
2525 if (!g_bIgnoreErrors)
2526 return FALSE;
2527
2528 if (!CreateConfigParam(_T("ConnectionPoolCooldownTime"), _T("300"), 1, 1))
2529 if (!g_bIgnoreErrors)
2530 return FALSE;
2531
2532 if (g_iSyntax == DB_SYNTAX_ORACLE)
2533 {
2534 if (!SQLQuery(_T("ALTER TABLE object_properties MODIFY comments null\n")))
2535 if (!g_bIgnoreErrors)
2536 return FALSE;
2537 }
2538
2539 if (!ConvertStrings(_T("object_properties"), _T("object_id"), _T("comments")))
2540 if (!g_bIgnoreErrors)
2541 return FALSE;
2542
2543 if (!SQLQuery(_T("UPDATE metadata SET var_value='204' WHERE var_name='SchemaVersion'")))
2544 if (!g_bIgnoreErrors)
2545 return FALSE;
2546
2547 return TRUE;
2548 }
2549
2550
2551 //
2552 // Upgrade from V202 to V203
2553 //
2554
2555 static BOOL H_UpgradeFromV202(int currVersion, int newVersion)
2556 {
2557 static TCHAR batch[] =
2558 _T("INSERT INTO object_tools (tool_id,tool_name,tool_type,tool_data,flags,matching_oid,description,confirmation_text)")
2559 _T(" VALUES (20,'&Info->Topology table (LLDP)',2,'Topology Table',1,' ','Show topology table (LLDP)','#00')\n")
2560 _T("INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr)")
2561 _T(" VALUES (20,0,'Chassis ID','.1.0.8802.1.1.2.1.4.1.1.5',0,0)\n")
2562 _T("INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr)")
2563 _T(" VALUES (20,1,'Local port','.1.0.8802.1.1.2.1.4.1.1.2',5,0)\n")
2564 _T("INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr)")
2565 _T(" VALUES (20,2,'System name','.1.0.8802.1.1.2.1.4.1.1.9',0,0)\n")
2566 _T("INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr)")
2567 _T(" VALUES (20,3,'System description','.1.0.8802.1.1.2.1.4.1.1.10',0,0)\n")
2568 _T("INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr)")
2569 _T(" VALUES (20,4,'Remote port ID','.1.0.8802.1.1.2.1.4.1.1.7',4,0)\n")
2570 _T("INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr)")
2571 _T(" VALUES (20,5,'Remote port description','.1.0.8802.1.1.2.1.4.1.1.8',0,0)\n")
2572 _T("INSERT INTO object_tools_acl (tool_id,user_id) VALUES (20,-2147483648)\n")
2573 _T("<END>");
2574
2575 if (!SQLBatch(batch))
2576 if (!g_bIgnoreErrors)
2577 return FALSE;
2578
2579 if (!SQLQuery(_T("UPDATE metadata SET var_value='203' WHERE var_name='SchemaVersion'")))
2580 if (!g_bIgnoreErrors)
2581 return FALSE;
2582
2583 return TRUE;
2584 }
2585
2586
2587 //
2588 // Upgrade from V201 to V202
2589 //
2590
2591 static BOOL H_UpgradeFromV201(int currVersion, int newVersion)
2592 {
2593 if (g_iSyntax == DB_SYNTAX_ORACLE)
2594 {
2595 static TCHAR oraBatch[] =
2596 _T("ALTER TABLE alarms MODIFY message null\n")
2597 _T("ALTER TABLE alarms MODIFY alarm_key null\n")
2598 _T("ALTER TABLE alarms MODIFY hd_ref null\n")
2599 _T("<END>");
2600
2601 if (!SQLBatch(oraBatch))
2602 if (!g_bIgnoreErrors)
2603 return FALSE;
2604 }
2605
2606 if (!ConvertStrings(_T("alarms"), _T("alarm_id"), _T("message")))
2607 if (!g_bIgnoreErrors)
2608 return FALSE;
2609 if (!ConvertStrings(_T("alarms"), _T("alarm_id"), _T("alarm_key")))
2610 if (!g_bIgnoreErrors)
2611 return FALSE;
2612 if (!ConvertStrings(_T("alarms"), _T("alarm_id"), _T("hd_ref")))
2613 if (!g_bIgnoreErrors)
2614 return FALSE;
2615
2616 if (!SQLQuery(_T("UPDATE metadata SET var_value='202' WHERE var_name='SchemaVersion'")))
2617 if (!g_bIgnoreErrors)
2618 return FALSE;
2619
2620 return TRUE;
2621 }
2622
2623
2624 //
2625 // Upgrade from V200 to V201
2626 //
2627
2628 static BOOL H_UpgradeFromV200(int currVersion, int newVersion)
2629 {
2630 static TCHAR batch[] =
2631 _T("ALTER TABLE nodes ADD usm_auth_password varchar(127)\n")
2632 _T("ALTER TABLE nodes ADD usm_priv_password varchar(127)\n")
2633 _T("ALTER TABLE nodes ADD usm_methods integer\n")
2634 _T("UPDATE nodes SET usm_auth_password='#00',usm_priv_password='#00',usm_methods=0\n")
2635 _T("<END>");
2636
2637 if (!SQLBatch(batch))
2638 if (!g_bIgnoreErrors)
2639 return FALSE;
2640
2641 if (!SQLQuery(_T("UPDATE metadata SET var_value='201' WHERE var_name='SchemaVersion'")))
2642 if (!g_bIgnoreErrors)
2643 return FALSE;
2644
2645 return TRUE;
2646 }
2647
2648
2649 //
2650 // Upgrade from V92 to V200
2651 // or from V93 to V201
2652 // or from V94 to V202
2653 // or from V95 to V203
2654 // or from V96 to V204
2655 // or from V97 to V205
2656 // or from V98 to V206
2657 // or from V99 to V207
2658 //
2659
2660 static BOOL H_UpgradeFromV9x(int currVersion, int newVersion)
2661 {
2662 if (!CreateTable(_T("CREATE TABLE ap_common (")
2663 _T("id integer not null,")
2664 _T("policy_type integer not null,")
2665 _T("version integer not null,")
2666 _T("description $SQL:TEXT not null,")
2667 _T("PRIMARY KEY(id))")))
2668 if (!g_bIgnoreErrors)
2669 return FALSE;
2670
2671 if (!CreateTable(_T("CREATE TABLE ap_bindings (")
2672 _T("policy_id integer not null,")
2673 _T("node_id integer not null,")
2674 _T("PRIMARY KEY(policy_id,node_id))")))
2675 if (!g_bIgnoreErrors)
2676 return FALSE;
2677
2678 if (!CreateTable(_T("CREATE TABLE ap_config_files (")
2679 _T("policy_id integer not null,")
2680 _T("file_name varchar(63) not null,")
2681 _T("file_content $SQL:TEXT not null,")
2682 _T("PRIMARY KEY(policy_id))")))
2683 if (!g_bIgnoreErrors)
2684 return FALSE;
2685
2686 TCHAR query[256];
2687 _sntprintf(query, 256, _T("UPDATE metadata SET var_value='%d' WHERE var_name='SchemaVersion'"), newVersion);
2688 if (!SQLQuery(query))
2689 if (!g_bIgnoreErrors)
2690 return FALSE;
2691
2692 return TRUE;
2693 }
2694
2695
2696 //
2697 // Upgrade from V100 to V214
2698 // or from V101 to V214
2699 // or from V102 to V214
2700 // or from V103 to V214
2701 // or from V104 to V214
2702 //
2703
2704 static BOOL H_UpgradeFromV10x(int currVersion, int newVersion)
2705 {
2706 if (!H_UpgradeFromV9x(currVersion, 207))
2707 return FALSE;
2708
2709 // Now database at V207 level
2710 // V100 already has changes V209 -> V210, but missing V207 -> V209 and V210 -> V214 changes
2711 // V101 already has changes V209 -> V211, but missing V207 -> V209 and V211 -> V214 changes
2712 // V102 already has changes V209 -> V212, but missing V207 -> V209 and V212 -> V214 changes
2713 // V103 already has changes V209 -> V213, but missing V207 -> V209 and V213 -> V214 changes
2714 // V104 already has changes V209 -> V214, but missing V207 -> V209 changes
2715
2716 if (!H_UpgradeFromV207(207, 208))
2717 return FALSE;
2718
2719 if (!H_UpgradeFromV208(208, 209))
2720 return FALSE;
2721
2722 if (currVersion == 100)
2723 if (!H_UpgradeFromV210(210, 211))
2724 return FALSE;
2725
2726 if (currVersion < 102)
2727 if (!H_UpgradeFromV211(211, 212))
2728 return FALSE;
2729
2730 if (currVersion < 103)
2731 if (!H_UpgradeFromV212(212, 213))
2732 return FALSE;
2733
2734 if (currVersion < 104)
2735 if (!H_UpgradeFromV213(213, 214))
2736 return FALSE;
2737
2738 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='214' WHERE var_name='SchemaVersion'")));
2739 return TRUE;
2740 }
2741
2742
2743 //
2744 // Upgrade from V105 to V217
2745 //
2746
2747 static BOOL H_UpgradeFromV105(int currVersion, int newVersion)
2748 {
2749 if (!H_UpgradeFromV10x(currVersion, 214))
2750 return FALSE;
2751
2752 // V105 already have V216 -> V217 changes, but missing V207 -> V209 and V214 -> V216 changes
2753 if (!H_UpgradeFromV214(214, 215))
2754 return FALSE;
2755
2756 if (!H_UpgradeFromV215(215, 216))
2757 return FALSE;
2758
2759 CHK_EXEC(SQLQuery(_T("UPDATE metadata SET var_value='217' WHERE var_name='SchemaVersion'")));
2760 return TRUE;
2761 }
2762
2763
2764 //
2765 // Upgrade from V91 to V92
2766 //
2767
2768 static BOOL H_UpgradeFromV91(int currVersion, int newVersion)
2769 {
2770 static TCHAR batch[] =
2771 _T("DROP TABLE images\n")
2772 _T("DROP TABLE default_images\n")
2773 _T("<END>");
2774
2775 if (!SQLBatch(batch))
2776 if (!g_bIgnoreErrors)
2777 return FALSE;
2778
2779 if (!SQLQuery(_T("UPDATE metadata SET var_value='92' WHERE var_name='SchemaVersion'")))
2780 if (!g_bIgnoreErrors)
2781 return FALSE;
2782
2783 return TRUE;
2784 }
2785
2786
2787 //
2788 // Upgrade from V90 to V91
2789 //
2790
2791 static BOOL H_UpgradeFromV90(int currVersion, int newVersion)
2792 {
2793 if (!CreateTable(_T("CREATE TABLE userdb_custom_attributes (")
2794 _T("object_id integer not null,")
2795 _T("attr_name varchar(255) not null,")
2796 _T("attr_value $SQL:TEXT not null,")
2797 _T("PRIMARY KEY(object_id,attr_name))")))
2798 if (!g_bIgnoreErrors)
2799 return FALSE;
2800
2801 if (!SQLQuery(_T("UPDATE metadata SET var_value='91' WHERE var_name='SchemaVersion'")))
2802 if (!g_bIgnoreErrors)
2803 return FALSE;
2804
2805 return TRUE;
2806 }
2807
2808
2809 //
2810 // Upgrade from V89 to V90
2811 //
2812
2813 static BOOL H_UpgradeFromV89(int currVersion, int newVersion)
2814 {
2815 static TCHAR m_szBatch[] =
2816 _T("ALTER TABLE items ADD base_units integer\n")
2817 _T("ALTER TABLE items ADD unit_multiplier integer\n")
2818 _T("ALTER TABLE items ADD custom_units_name varchar(63)\n")
2819 _T("ALTER TABLE items ADD perftab_settings $SQL:TEXT\n")
2820 _T("UPDATE items SET base_units=0,unit_multiplier=1,custom_units_name='#00',perftab_settings='#00'\n")
2821 _T("<END>");
2822
2823 if (!SQLBatch(m_szBatch))
2824 if (!g_bIgnoreErrors)
2825 return FALSE;
2826
2827 if (!SQLQuery(_T("UPDATE metadata SET var_value='90' WHERE var_name='SchemaVersion'")))
2828 if (!g_bIgnoreErrors)
2829 return FALSE;
2830
2831 return TRUE;
2832 }
2833
2834
2835 //
2836 // Upgrade from V88 to V89
2837 //
2838
2839 static BOOL H_UpgradeFromV88(int currVersion, int newVersion)
2840 {
2841 static TCHAR m_szBatch[] =
2842 _T("ALTER TABLE containers ADD enable_auto_bind integer\n")
2843 _T("ALTER TABLE containers ADD auto_bind_filter $SQL:TEXT\n")
2844 _T("UPDATE containers SET enable_auto_bind=0,auto_bind_filter='#00'\n")
2845 _T("ALTER TABLE cluster_resources ADD current_owner integer\n")
2846 _T("UPDATE cluster_resources SET current_owner=0\n")
2847 _T("INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description) VALUES (")
2848 _T("52,'SYS_DB_QUERY_FAILED',4,1,'Database query failed (Query: %1; Error: %2)',")
2849 _T("'Generated when SQL query to backend database failed.#0D#0A")
2850 _T("Parameters:#0D#0A 1) Query#0D#0A 2) Error message')\n")
2851 _T("<END>");
2852
2853 if (!SQLBatch(m_szBatch))
2854 if (!g_bIgnoreErrors)
2855 return FALSE;
2856
2857 if (!SQLQuery(_T("UPDATE metadata SET var_value='89' WHERE var_name='SchemaVersion'")))
2858 if (!g_bIgnoreErrors)
2859 return FALSE;
2860
2861 return TRUE;
2862 }
2863
2864
2865 //
2866 // Upgrade from V87 to V88
2867 //
2868
2869 static BOOL H_UpgradeFromV87(int currVersion, int newVersion)
2870 {
2871 static TCHAR m_szBatch[] =
2872 _T("ALTER TABLE templates ADD enable_auto_apply integer\n")
2873 _T("ALTER TABLE templates ADD apply_filter $SQL:TEXT\n")
2874 _T("UPDATE templates SET enable_auto_apply=0,apply_filter='#00'\n")
2875 _T("<END>");
2876
2877 if (!SQLBatch(m_szBatch))
2878 if (!g_bIgnoreErrors)
2879 return FALSE;
2880
2881 if (!SQLQuery(_T("UPDATE metadata SET var_value='88' WHERE var_name='SchemaVersion'")))
2882 if (!g_bIgnoreErrors)
2883 return FALSE;
2884
2885 return TRUE;
2886 }
2887
2888
2889 //
2890 // Upgrade from V86 to V87
2891 //
2892
2893 static BOOL MoveConfigToMetadata(const TCHAR *cfgVar, const TCHAR *mdVar)
2894 {
2895 TCHAR query[1024], buffer[256];
2896 DB_RESULT hResult;
2897 BOOL success;
2898
2899 _sntprintf(query, 1024, _T("SELECT var_value FROM config WHERE var_name='%s'"), cfgVar);
2900 hResult = SQLSelect(query);
2901 if (hResult != NULL)
2902 {
2903 if (DBGetNumRows(hResult) > 0)
2904 {
2905 DBGetField(hResult, 0, 0, buffer, 256);
2906 DecodeSQLString(buffer);
2907 _sntprintf(query, 1024, _T("INSERT INTO metadata (var_name,var_value) VALUES ('%s','%s')"),
2908 mdVar, buffer);
2909 DBFreeResult(hResult);
2910 success = SQLQuery(query);
2911 if (success)
2912 {
2913 _sntprintf(query, 1024, _T("DELETE FROM config WHERE var_name='%s'"), cfgVar);
2914 success = SQLQuery(query);
2915 }
2916 }
2917 else
2918 {
2919 success = TRUE; // Variable missing in 'config' table, nothing to move
2920 }
2921 }
2922 else
2923 {
2924 success = FALSE;
2925 }
2926 return success;
2927 }
2928
2929 static BOOL H_UpgradeFromV86(int currVersion, int newVersion)
2930 {
2931 if (!CreateTable(_T("CREATE TABLE metadata (")
2932 _T("var_name varchar(63) not null,")
2933 _T("var_value varchar(255) not null,")
2934 _T("PRIMARY KEY(var_name))")))
2935 if (!g_bIgnoreErrors)
2936 return FALSE;
2937
2938 if (!MoveConfigToMetadata(_T("DBFormatVersion"), _T("SchemaVersion")))
2939 if (!g_bIgnoreErrors)
2940 return FALSE;
2941
2942 if (!MoveConfigToMetadata(_T("DBSyntax"), _T("Syntax")))
2943 if (!g_bIgnoreErrors)
2944 return FALSE;
2945
2946 if (!MoveConfigToMetadata(_T("IDataTableCreationCommand"), _T("IDataTableCreationCommand")))
2947 if (!g_bIgnoreErrors)
2948 return FALSE;
2949
2950 if (!MoveConfigToMetadata(_T("IDataIndexCreationCommand_0"), _T("IDataIndexCreationCommand_0")))
2951 if (!g_bIgnoreErrors)
2952 return FALSE;
2953
2954 if (!MoveConfigToMetadata(_T("IDataIndexCreationCommand_1"), _T("IDataIndexCreationCommand_1")))
2955 if (!g_bIgnoreErrors)
2956 return FALSE;
2957
2958 if (!MoveConfigToMetadata(_T("IDataIndexCreationCommand_2"), _T("IDataIndexCreationCommand_2")))
2959 if (!g_bIgnoreErrors)
2960 return FALSE;
2961
2962 if (!MoveConfigToMetadata(_T("IDataIndexCreationCommand_3"), _T("IDataIndexCreationCommand_3")))
2963 if (!g_bIgnoreErrors)
2964 return FALSE;
2965
2966 if (!SQLQuery(_T("UPDATE metadata SET var_value='87' WHERE var_name='SchemaVersion'")))
2967 if (!g_bIgnoreErrors)
2968 return FALSE;
2969
2970 return TRUE;
2971 }
2972
2973
2974 //
2975 // Upgrade from V85 to V86
2976 //
2977
2978 static BOOL H_UpgradeFromV85(int currVersion, int newVersion)
2979 {
2980 static TCHAR m_szBatch[] =
2981 _T("DROP TABLE alarm_grops\n")
2982 _T("DROP TABLE alarm_group_map\n")
2983 _T("DROP TABLE alarm_change_log\n")
2984 _T("DROP TABLE lpp\n")
2985 _T("DROP TABLE lpp_associations\n")
2986 _T("DROP TABLE lpp_rulesets\n")
2987 _T("DROP TABLE lpp_rules\n")
2988 _T("DROP TABLE lpp_groups\n")
2989 _T("<END>");
2990
2991 if (!SQLBatch(m_szBatch))
2992 if (!g_bIgnoreErrors)
2993 return FALSE;
2994
2995 if (!SQLQuery(_T("UPDATE config SET var_value='86' WHERE var_name='DBFormatVersion'")))
2996 if (!g_bIgnoreErrors)
2997 return FALSE;
2998
2999 return TRUE;
3000 }
3001
3002
3003 //
3004 // Upgrade from V84 to V85
3005 //
3006
3007 static BOOL H_UpgradeFromV84(int currVersion, int newVersion)
3008 {
3009 static TCHAR m_szBatch[] =
3010 _T("ALTER TABLE nodes ADD use_ifxtable integer\n")
3011 _T("UPDATE nodes SET use_ifxtable=0\n")
3012 _T("<END>");
3013
3014 if (!SQLBatch(m_szBatch))
3015 if (!g_bIgnoreErrors)
3016 return FALSE;
3017
3018 if (!CreateConfigParam(_T("UseIfXTable"), _T("1"), 1, 0))
3019 if (!g_bIgnoreErrors)
3020 return FALSE;
3021
3022 if (!CreateConfigParam(_T("SMTPRetryCount"), _T("1"), 1, 0))
3023 if (!g_bIgnoreErrors)
3024 return FALSE;
3025
3026 if (!SQLQuery(_T("UPDATE config SET var_value='85' WHERE var_name='DBFormatVersion'")))
3027 if (!g_bIgnoreErrors)
3028 return FALSE;
3029
3030 return TRUE;
3031 }
3032
3033
3034 //
3035 // Upgrade from V83 to V84
3036 //
3037
3038 static BOOL H_UpgradeFromV83(int currVersion, int newVersion)
3039 {
3040 if (!CreateConfigParam(_T("EnableAgentRegistration"), _T("1"), 1, 0))
3041 if (!g_bIgnoreErrors)
3042 return FALSE;
3043
3044 if (!CreateConfigParam(_T("AnonymousFileAccess"), _T("0"), 1, 0))
3045 if (!g_bIgnoreErrors)
3046 return FALSE;
3047
3048 if (!CreateConfigParam(_T("EnableISCListener"), _T("0"), 1, 1))
3049 if (!g_bIgnoreErrors)
3050 return FALSE;
3051
3052 if (!CreateConfigParam(_T("ReceiveForwardedEvents"), _T("0"), 1, 0))
3053 if (!g_bIgnoreErrors)
3054 return FALSE;
3055
3056 if (!SQLQuery(_T("UPDATE config SET var_value='84' WHERE var_name='DBFormatVersion'")))
3057 if (!g_bIgnoreErrors)
3058 return FALSE;
3059
3060 return TRUE;
3061 }
3062
3063
3064 //
3065 // Upgrade from V82 to V83
3066 //
3067
3068 static BOOL H_UpgradeFromV82(int currVersion, int newVersion)
3069 {
3070 // Fix incorrect alarm timeouts
3071 if (!SQLQuery(_T("UPDATE alarms SET timeout=0,timeout_event=43")))
3072 if (!g_bIgnoreErrors)
3073 return FALSE;
3074
3075 if (!SQLQuery(_T("UPDATE config SET var_value='83' WHERE var_name='DBFormatVersion'")))
3076 if (!g_bIgnoreErrors)
3077 return FALSE;
3078
3079 return TRUE;
3080 }
3081
3082
3083 //
3084 // Upgrade from V81 to V82
3085 //
3086
3087 static BOOL H_UpgradeFromV81(int currVersion, int newVersion)
3088 {
3089 if (!CreateTable(_T("CREATE TABLE config_clob (")
3090 _T("var_name varchar(63) not null,")
3091 _T("var_value $SQL:TEXT not null,")
3092 _T("PRIMARY KEY(var_name))")))
3093 if (!g_bIgnoreErrors)
3094 return FALSE;
3095
3096 if (!SQLQuery(_T("UPDATE config SET var_value='82' WHERE var_name='DBFormatVersion'")))
3097 if (!g_bIgnoreErrors)
3098 return FALSE;
3099
3100 return TRUE;
3101 }
3102
3103
3104 //
3105 // Upgrade from V80 to V81
3106 //
3107
3108 static BOOL H_UpgradeFromV80(int currVersion, int newVersion)
3109 {
3110 DB_RESULT hResult;
3111 TCHAR query[1024], buffer[1024];
3112 int i;
3113
3114 // Update dci_schedules table
3115 hResult = SQLSelect(_T("SELECT item_id,schedule FROM dci_schedules"));
3116 if (hResult != NULL)
3117 {
3118 if (!SQLQuery(_T("DROP TABLE dci_schedules")))
3119 if (!g_bIgnoreErrors)
3120 return FALSE;
3121
3122 if (!CreateTable(_T("CREATE TABLE dci_schedules (")
3123 _T("schedule_id integer not null,")
3124 _T("item_id integer not null,")
3125 _T("schedule varchar(255) not null,")
3126 _T("PRIMARY KEY(item_id,schedule_id))")))
3127 if (!g_bIgnoreErrors)
3128 return FALSE;
3129
3130 for(i = 0; i < DBGetNumRows(hResult); i++)
3131 {
3132 _sntprintf(query, 1024, _T("INSERT INTO dci_schedules (item_id,schedule_id,schedule) VALUES(%d,%d,'%s')"),
3133 DBGetFieldULong(hResult, i, 0), i + 1, DBGetField(hResult, i, 1, buffer, 1024));
3134 if (!SQLQuery(query))
3135 if (!g_bIgnoreErrors)
3136 return FALSE;
3137 }
3138 DBFreeResult(hResult);
3139 }
3140 else
3141 {
3142 if (!g_bIgnoreErrors)
3143 return FALSE;
3144 }
3145
3146 // Update address_lists table
3147 hResult = SQLSelect(_T("SELECT list_type,community_id,addr_type,addr1,addr2 FROM address_lists"));
3148 if (hResult != NULL)
3149 {
3150 if (!SQLQuery(_T("DROP TABLE address_lists")))
3151 if (!g_bIgnoreErrors)
3152 return FALSE;
3153
3154 if (!CreateTable(_T("CREATE TABLE address_lists (")
3155 _T("list_type integer not null,")
3156 _T("community_id integer not null,")
3157 _T("addr_type integer not null,")
3158 _T("addr1 varchar(15) not null,")
3159 _T("addr2 varchar(15) not null,")
3160 _T("PRIMARY KEY(list_type,community_id,addr_type,addr1,addr2))")))
3161 if (!g_bIgnoreErrors)
3162 return FALSE;
3163
3164 for(i = 0; i < DBGetNumRows(hResult); i++)
3165 {
3166 _sntprintf(query, 1024, _T("INSERT INTO address_lists (list_type,community_id,addr_type,addr1,addr2) VALUES(%d,%d,%d,'%s','%s')"),
3167 DBGetFieldULong(hResult, i, 0), DBGetFieldULong(hResult, i, 1),
3168 DBGetFieldULong(hResult, i, 2), DBGetField(hResult, i, 3, buffer, 64),
3169 DBGetField(hResult, i, 4, &buffer[128], 64));
3170 if (!SQLQuery(query))
3171 if (!g_bIgnoreErrors)
3172 return FALSE;
3173 }
3174
3175 DBFreeResult(hResult);
3176 }
3177 else
3178 {
3179 if (!g_bIgnoreErrors)
3180 return FALSE;
3181 }
3182
3183 // Create new tables
3184 if (!CreateTable(_T("CREATE TABLE object_custom_attributes (")
3185 _T("object_id integer not null,")
3186 _T("attr_name varchar(127) not null,")
3187 _T("attr_value $SQL:TEXT not null,")
3188 _T("PRIMARY KEY(object_id,attr_name))")))
3189 if (!g_bIgnoreErrors)
3190 return FALSE;
3191
3192 if (!CreateTable(_T("CREATE TABLE web_maps (")
3193 _T("id integer not null,")
3194 _T("title varchar(63) not null,")
3195 _T("properties $SQL:TEXT not null,")
3196 _T("data $SQL:TEXT not null,")
3197 _T("PRIMARY KEY(id))")))
3198 if (!g_bIgnoreErrors)
3199 return FALSE;
3200
3201 if