2 ** nxdbmgr - NetXMS database manager
3 ** Copyright (C) 2004-2017 Victor Kirhenshtein
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.
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.
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.
19 ** File: upgrade_v30.cpp
26 * Upgrade from 30.7 to 30.8 (changes also included into 22.1)
28 static bool H_UpgradeFromV7()
30 if (GetSchemaLevelForMajorVersion(22) < 1)
32 int count
= ConfigReadInt(_T("NumberOfDataCollectors"), 250);
34 _sntprintf(value
, 64,_T("%d"), std
::max(250, count
));
35 CHK_EXEC(CreateConfigParam(_T("DataCollector.ThreadPool.BaseSize"), _T("10"), _T("Base size for data collector thread pool."), 'I', true, true, false, false));
36 CHK_EXEC(CreateConfigParam(_T("DataCollector.ThreadPool.MaxSize"), value
, _T("Maximum size for data collector thread pool."), 'I', true, true, false, false));
37 CHK_EXEC(SQLQuery(_T("UPDATE config SET default_value='250' WHERE var_name='DataCollector.ThreadPool.MaxSize'")));
38 CHK_EXEC(SQLQuery(_T("DELETE FROM config WHERE var_name='NumberOfDataCollectors'")));
40 CHK_EXEC(SetMinorSchemaVersion(8));
45 * Upgrade from 30.6 to 30.7 (changes also included into 21.4)
47 static bool H_UpgradeFromV6()
49 if (GetSchemaLevelForMajorVersion(21) < 4)
51 DB_RESULT hResult
= DBSelect(g_hCoreDB
, _T("SELECT access_rights,object_id FROM acl WHERE user_id=-2147483647")); // Get group Admins object acl
54 DB_STATEMENT hStmt
= DBPrepare(g_hCoreDB
, _T("UPDATE acl SET access_rights=? WHERE user_id=-2147483647 AND object_id=? "));
57 int nRows
= DBGetNumRows(hResult
);
59 for(int i
= 0; i
< nRows
; i
++)
61 rights
= DBGetFieldULong(hResult
, i
, 0);
62 if (rights
& OBJECT_ACCESS_READ
)
64 rights
|= (OBJECT_ACCESS_READ_AGENT
| OBJECT_ACCESS_READ_SNMP
| OBJECT_ACCESS_SCREENSHOT
);
65 DBBind(hStmt
, 1, DB_SQLTYPE_INTEGER
, rights
);
66 DBBind(hStmt
, 2, DB_SQLTYPE_INTEGER
, DBGetFieldULong(hResult
, i
, 1));
68 if (!SQLExecute(hStmt
))
72 DBFreeStatement(hStmt
);
73 DBFreeResult(hResult
);
80 DBFreeStatement(hStmt
);
82 else if (!g_bIgnoreErrors
)
84 DBFreeResult(hResult
);
86 else if (!g_bIgnoreErrors
)
88 CHK_EXEC(SetSchemaLevelForMajorVersion(21, 4));
90 CHK_EXEC(SetMinorSchemaVersion(7));
95 * Upgrade from 30.5 to 30.6 (changes also included into 21.3)
97 static bool H_UpgradeFromV5()
99 if (GetSchemaLevelForMajorVersion(21) < 3)
101 static const TCHAR
*batch
=
102 _T("UPDATE nodes SET fail_time_snmp=0 WHERE fail_time_snmp IS NULL\n")
103 _T("UPDATE nodes SET fail_time_agent=0 WHERE fail_time_agent IS NULL\n")
105 CHK_EXEC(SQLBatch(batch
));
107 CHK_EXEC(DBSetNotNullConstraint(g_hCoreDB
, _T("nodes"), _T("fail_time_snmp")));
108 CHK_EXEC(DBSetNotNullConstraint(g_hCoreDB
, _T("nodes"), _T("fail_time_agent")));
109 CHK_EXEC(SetSchemaLevelForMajorVersion(21, 3));
111 CHK_EXEC(SetMinorSchemaVersion(6));
116 * Upgrade from 30.4 to 30.5 (changes also included into 21.2)
118 static bool H_UpgradeFromV4()
120 if (GetSchemaLevelForMajorVersion(21) < 2)
122 static const TCHAR
*batch
=
123 _T("ALTER TABLE nodes ADD fail_time_snmp integer\n")
124 _T("ALTER TABLE nodes ADD fail_time_agent integer\n")
126 CHK_EXEC(SQLBatch(batch
));
127 CHK_EXEC(SetSchemaLevelForMajorVersion(21, 2));
129 CHK_EXEC(SetMinorSchemaVersion(5));
134 * Move object flags from old to new tables
136 static BOOL
MoveFlagsFromOldTables(const TCHAR
*tableName
)
139 _sntprintf(query
, 256, _T("SELECT id,flags FROM %s"), tableName
);
140 DB_RESULT hResult
= DBSelect(g_hCoreDB
, query
);
141 DB_STATEMENT hStmt
= DBPrepare(g_hCoreDB
, _T("UPDATE object_properties SET flags=? WHERE object_id=?"));
146 int nRows
= DBGetNumRows(hResult
);
147 for(int i
= 0; i
< nRows
; i
++)
149 DBBind(hStmt
, 1, DB_SQLTYPE_INTEGER
, DBGetFieldULong(hResult
, i
, 1));
150 DBBind(hStmt
, 2, DB_SQLTYPE_INTEGER
, DBGetFieldULong(hResult
, i
, 0));
152 if (!SQLExecute(hStmt
))
154 if (!g_bIgnoreErrors
)
156 DBFreeStatement(hStmt
);
157 DBFreeResult(hResult
);
162 DBFreeStatement(hStmt
);
164 else if (!g_bIgnoreErrors
)
168 DBFreeResult(hResult
);
170 else if (!g_bIgnoreErrors
)
175 CHK_EXEC(DBDropColumn(g_hCoreDB
, tableName
, _T("flags")));
182 inline void MoveFlag(UINT32 oldVar
, UINT32
*newVar
, UINT32 oldFlag
, UINT32 newFlag
)
184 *newVar
|= ((oldVar
& oldFlag
) != 0) ? newFlag
: 0;
190 static void MoveNodeFlags(UINT32 oldFlag
, UINT32
*flags
)
192 MoveFlag(oldFlag
, flags
, 0x10000000, DCF_DISABLE_STATUS_POLL
);
193 MoveFlag(oldFlag
, flags
, 0x20000000, DCF_DISABLE_CONF_POLL
);
194 MoveFlag(oldFlag
, flags
, 0x80000000, DCF_DISABLE_DATA_COLLECT
);
195 MoveFlag(oldFlag
, flags
, 0x00000080, NF_REMOTE_AGENT
);
196 MoveFlag(oldFlag
, flags
, 0x00400000, NF_DISABLE_DISCOVERY_POLL
);
197 MoveFlag(oldFlag
, flags
, 0x00800000, NF_DISABLE_TOPOLOGY_POLL
);
198 MoveFlag(oldFlag
, flags
, 0x01000000, NF_DISABLE_SNMP
);
199 MoveFlag(oldFlag
, flags
, 0x02000000, NF_DISABLE_NXCP
);
200 MoveFlag(oldFlag
, flags
, 0x04000000, NF_DISABLE_ICMP
);
201 MoveFlag(oldFlag
, flags
, 0x08000000, NF_FORCE_ENCRYPTION
);
202 MoveFlag(oldFlag
, flags
, 0x40000000, NF_DISABLE_ROUTE_POLL
);
206 * Move node capabilities flags
208 static void MoveNodeCapabilities(UINT32 oldFlag
, UINT32
*capabilities
)
210 MoveFlag(oldFlag
, capabilities
, 0x00000001, NC_IS_SNMP
);
211 MoveFlag(oldFlag
, capabilities
, 0x00000002, NC_IS_NATIVE_AGENT
);
212 MoveFlag(oldFlag
, capabilities
, 0x00000004, NC_IS_BRIDGE
);
213 MoveFlag(oldFlag
, capabilities
, 0x00000008, NC_IS_ROUTER
);
214 MoveFlag(oldFlag
, capabilities
, 0x00000010, NC_IS_LOCAL_MGMT
);
215 MoveFlag(oldFlag
, capabilities
, 0x00000020, NC_IS_PRINTER
);
216 MoveFlag(oldFlag
, capabilities
, 0x00000040, NC_IS_OSPF
);
217 MoveFlag(oldFlag
, capabilities
, 0x00000100, NC_IS_CPSNMP
);
218 MoveFlag(oldFlag
, capabilities
, 0x00000200, NC_IS_CDP
);
219 MoveFlag(oldFlag
, capabilities
, 0x00000400, NC_IS_NDP
);
220 MoveFlag(oldFlag
, capabilities
, 0x00000800, NC_IS_LLDP
);
221 MoveFlag(oldFlag
, capabilities
, 0x00001000, NC_IS_VRRP
);
222 MoveFlag(oldFlag
, capabilities
, 0x00002000, NC_HAS_VLANS
);
223 MoveFlag(oldFlag
, capabilities
, 0x00004000, NC_IS_8021X
);
224 MoveFlag(oldFlag
, capabilities
, 0x00008000, NC_IS_STP
);
225 MoveFlag(oldFlag
, capabilities
, 0x00010000, NC_HAS_ENTITY_MIB
);
226 MoveFlag(oldFlag
, capabilities
, 0x00020000, NC_HAS_IFXTABLE
);
227 MoveFlag(oldFlag
, capabilities
, 0x00040000, NC_HAS_AGENT_IFXCOUNTERS
);
228 MoveFlag(oldFlag
, capabilities
, 0x00080000, NC_HAS_WINPDH
);
229 MoveFlag(oldFlag
, capabilities
, 0x00100000, NC_IS_WIFI_CONTROLLER
);
230 MoveFlag(oldFlag
, capabilities
, 0x00200000, NC_IS_SMCLP
);
234 * Move node state flags
236 static void MoveNodeState(UINT32 oldRuntime
, UINT32
*state
)
238 MoveFlag(oldRuntime
, state
, 0x000004, DCSF_UNREACHABLE
);
239 MoveFlag(oldRuntime
, state
, 0x000008, NSF_AGENT_UNREACHABLE
);
240 MoveFlag(oldRuntime
, state
, 0x000010, NSF_SNMP_UNREACHABLE
);
241 MoveFlag(oldRuntime
, state
, 0x000200, NSF_CPSNMP_UNREACHABLE
);
242 MoveFlag(oldRuntime
, state
, 0x008000, DCSF_NETWORK_PATH_PROBLEM
);
243 MoveFlag(oldRuntime
, state
, 0x020000, NSF_CACHE_MODE_NOT_SUPPORTED
);
247 * Move sensor state flags
249 static void MoveSensorState(UINT32 oldFlag
, UINT32 oldRuntime
, UINT32
*status
)
251 MoveFlag(oldFlag
, status
, 0x00000001, SSF_PROVISIONED
);
252 MoveFlag(oldFlag
, status
, 0x00000002, SSF_REGISTERED
);
253 MoveFlag(oldFlag
, status
, 0x00000004, SSF_ACTIVE
);
254 MoveFlag(oldFlag
, status
, 0x00000008, SSF_CONF_UPDATE_PENDING
);
255 MoveFlag(oldRuntime
, status
, 0x000004, DCSF_UNREACHABLE
);
259 * Upgrade from 30.3 to 30.4
261 static bool H_UpgradeFromV3()
263 static const TCHAR
*batch
=
264 _T("ALTER TABLE object_properties ADD flags integer null\n")
265 _T("ALTER TABLE object_properties ADD state integer null\n")
266 _T("ALTER TABLE nodes ADD capabilities integer null\n")
267 _T("UPDATE object_properties set flags=0,state=0\n")
269 CHK_EXEC(SQLBatch(batch
));
271 //move flags from old tables to the new one
272 CHK_EXEC(MoveFlagsFromOldTables(_T("interfaces")));
273 CHK_EXEC(MoveFlagsFromOldTables(_T("templates")));
274 CHK_EXEC(MoveFlagsFromOldTables(_T("chassis")));
275 CHK_EXEC(MoveFlagsFromOldTables(_T("object_containers")));
276 CHK_EXEC(MoveFlagsFromOldTables(_T("network_maps")));
277 //create special behavior for node and sensor, cluster
279 DB_RESULT hResult
= DBSelect(g_hCoreDB
, _T("SELECT id,runtime_flags FROM nodes"));
280 DB_STATEMENT stmtNetObj
= DBPrepare(g_hCoreDB
, _T("UPDATE object_properties SET flags=?, state=? WHERE object_id=?"));
281 DB_STATEMENT stmtNode
= DBPrepare(g_hCoreDB
, _T("UPDATE nodes SET capabilities=? WHERE id=?"));
284 if (stmtNetObj
!= NULL
&& stmtNode
!= NULL
)
286 int nRows
= DBGetNumRows(hResult
);
287 for(int i
= 0; i
< nRows
; i
++)
289 UINT32 id
= DBGetFieldULong(hResult
, i
, 0);
291 UINT32 oldRuntime
= DBGetFieldULong(hResult
, i
, 1);
294 UINT32 capabilities
= 0;
296 _sntprintf(query
, 256, _T("SELECT node_flags FROM nodes WHERE id=%d"), id
);
297 DB_RESULT flagResult
= DBSelect(g_hCoreDB
, query
);
298 if(DBGetNumRows(flagResult
) >= 1)
300 oldFlags
= DBGetFieldULong(flagResult
, 0, 0);
306 DBFreeStatement(stmtNetObj
);
307 DBFreeStatement(stmtNode
);
308 DBFreeResult(hResult
);
312 MoveNodeFlags(oldFlags
, &flags
);
313 MoveNodeCapabilities(oldFlags
, &capabilities
);
314 MoveNodeState(oldRuntime
, &state
);
316 DBBind(stmtNetObj
, 1, DB_SQLTYPE_INTEGER
, flags
);
317 DBBind(stmtNetObj
, 2, DB_SQLTYPE_INTEGER
, state
);
318 DBBind(stmtNetObj
, 3, DB_SQLTYPE_INTEGER
, id
);
320 DBBind(stmtNode
, 1, DB_SQLTYPE_INTEGER
, capabilities
);
321 DBBind(stmtNode
, 2, DB_SQLTYPE_INTEGER
, id
);
323 if (!(SQLExecute(stmtNetObj
)))
325 if (!g_bIgnoreErrors
)
327 DBFreeStatement(stmtNetObj
);
328 DBFreeStatement(stmtNode
);
329 DBFreeResult(hResult
);
334 if (!SQLExecute(stmtNode
))
336 if (!g_bIgnoreErrors
)
338 DBFreeStatement(stmtNetObj
);
339 DBFreeStatement(stmtNode
);
340 DBFreeResult(hResult
);
345 DBFreeStatement(stmtNetObj
);
346 DBFreeStatement(stmtNode
);
350 if(stmtNetObj
!= NULL
)
351 DBFreeStatement(stmtNetObj
);
354 DBFreeStatement(stmtNode
);
355 if (!g_bIgnoreErrors
)
360 DBFreeResult(hResult
);
362 CHK_EXEC(DBDropColumn(g_hCoreDB
, _T("nodes"), _T("runtime_flags")));
363 CHK_EXEC(DBDropColumn(g_hCoreDB
, _T("nodes"), _T("node_flags")));
366 hResult
= DBSelect(g_hCoreDB
, _T("SELECT id,runtime_flags,flags FROM sensors"));
367 DB_STATEMENT stmt
= DBPrepare(g_hCoreDB
, _T("UPDATE object_properties SET status=? WHERE object_id=?"));
372 int nRows
= DBGetNumRows(hResult
);
373 for(int i
= 0; i
< nRows
; i
++)
376 MoveSensorState(DBGetFieldULong(hResult
, i
, 2), DBGetFieldULong(hResult
, i
, 1), &status
);
378 DBBind(stmt
, 1, DB_SQLTYPE_INTEGER
, status
);
379 DBBind(stmt
, 2, DB_SQLTYPE_INTEGER
, DBGetFieldULong(hResult
, i
, 0));
381 if (!(SQLExecute(stmt
)))
383 if (!g_bIgnoreErrors
)
385 DBFreeStatement(stmt
);
386 DBFreeResult(hResult
);
391 DBFreeStatement(stmt
);
393 else if (!g_bIgnoreErrors
)
397 DBFreeResult(hResult
);
399 CHK_EXEC(DBDropColumn(g_hCoreDB
, _T("sensors"), _T("runtime_flags")));
400 CHK_EXEC(DBDropColumn(g_hCoreDB
, _T("sensors"), _T("flags")));
402 CHK_EXEC(SetMinorSchemaVersion(4));
407 * Upgrade from 30.2 to 30.3
409 static bool H_UpgradeFromV2()
411 CHK_EXEC(DBDropColumn(g_hCoreDB
, _T("event_groups"), _T("range_start")));
412 CHK_EXEC(DBDropColumn(g_hCoreDB
, _T("event_groups"), _T("range_end")));
414 static const TCHAR
*batch
=
415 _T("ALTER TABLE event_groups ADD guid varchar(36) null\n")
416 _T("UPDATE event_groups SET guid='04b326c0-5cc0-411f-8587-2836cb87c920' WHERE id=-2147483647\n")
417 _T("UPDATE event_groups SET guid='b61859c6-1768-4a61-a0cf-eed07d688f66' WHERE id=-2147483646\n")
419 CHK_EXEC(SQLBatch(batch
));
420 DBSetNotNullConstraint(g_hCoreDB
, _T("event_groups"), _T("guid"));
422 CHK_EXEC(SetMinorSchemaVersion(3));
427 * Upgrade from 30.1 to 30.2
429 static bool H_UpgradeFromV1()
431 static const TCHAR
*batch
=
432 _T("ALTER TABLE users ADD created integer null\n")
433 _T("ALTER TABLE user_groups ADD created integer null\n")
434 _T("UPDATE users SET created=0\n")
435 _T("UPDATE user_groups SET created=0\n")
437 CHK_EXEC(SQLBatch(batch
));
438 CHK_EXEC(DBSetNotNullConstraint(g_hCoreDB
, _T("users"), _T("created")));
439 CHK_EXEC(DBSetNotNullConstraint(g_hCoreDB
, _T("user_groups"), _T("created")));
441 CHK_EXEC(SetMinorSchemaVersion(2));
446 * Upgrade from 30.0 to 30.1
448 static bool H_UpgradeFromV0()
450 CHK_EXEC(CreateTable(
451 _T("CREATE TABLE sensors (")
452 _T(" id integer not null,")
453 _T(" proxy_node integer not null,")
454 _T(" flags integer not null,")
455 _T(" mac_address varchar(16) null,")
456 _T(" device_class integer not null,")
457 _T(" vendor varchar(128) null,")
458 _T(" communication_protocol integer not null,")
459 _T(" xml_config varchar(4000) null,")
460 _T(" xml_reg_config varchar(4000) null,")
461 _T(" serial_number varchar(256) null,")
462 _T(" device_address varchar(256) null,")
463 _T(" meta_type varchar(256) null,")
464 _T(" description varchar(512) null,")
465 _T(" last_connection_time integer not null,")
466 _T(" frame_count integer not null,")
467 _T(" signal_strenght integer not null,")
468 _T(" signal_noise integer not null,")
469 _T(" frequency integer not null,")
470 _T(" runtime_flags integer null,")
471 _T("PRIMARY KEY(id))")));
473 CHK_EXEC(SetMinorSchemaVersion(1));
485 bool (* upgradeProc
)();
488 { 7, 30, 8, H_UpgradeFromV7
},
489 { 6, 30, 7, H_UpgradeFromV6
},
490 { 5, 30, 6, H_UpgradeFromV5
},
491 { 4, 30, 5, H_UpgradeFromV4
},
492 { 3, 30, 4, H_UpgradeFromV3
},
493 { 2, 30, 3, H_UpgradeFromV2
},
494 { 1, 30, 2, H_UpgradeFromV1
},
495 { 0, 30, 1, H_UpgradeFromV0
},
500 * Upgrade database to new version
502 bool MajorSchemaUpgrade_V30()
505 if (!DBGetSchemaVersion(g_hCoreDB
, &major
, &minor
))
508 while((major
== 30) && (minor
< DB_SCHEMA_VERSION_V30_MINOR
))
510 // Find upgrade procedure
512 for(i
= 0; s_dbUpgradeMap
[i
].upgradeProc
!= NULL
; i
++)
513 if (s_dbUpgradeMap
[i
].version
== minor
)
515 if (s_dbUpgradeMap
[i
].upgradeProc
== NULL
)
517 _tprintf(_T("Unable to find upgrade procedure for version 30.%d\n"), minor
);
520 _tprintf(_T("Upgrading from version 30.%d to %d.%d\n"), minor
, s_dbUpgradeMap
[i
].nextMajor
, s_dbUpgradeMap
[i
].nextMinor
);
522 if (s_dbUpgradeMap
[i
].upgradeProc())
525 if (!DBGetSchemaVersion(g_hCoreDB
, &major
, &minor
))
530 _tprintf(_T("Rolling back last stage due to upgrade errors...\n"));
531 DBRollback(g_hCoreDB
);