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