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 CHK_EXEC(CreateConfigParam(_T("DataCollector.ThreadPool.BaseSize"), _T("10"), _T("Base size for data collector thread pool."), 'I', true, true, false, false));
33 CHK_EXEC(CreateConfigParam(_T("DataCollector.ThreadPool.MaxSize"), _T("250"), _T("Maximum size for data collector thread pool."), 'I', true, true, false, false));
35 CHK_EXEC(SetMinorSchemaVersion(8));
40 * Upgrade from 30.6 to 30.7 (changes also included into 21.4)
42 static bool H_UpgradeFromV6()
44 if (GetSchemaLevelForMajorVersion(21) < 4)
46 DB_RESULT hResult
= DBSelect(g_hCoreDB
, _T("SELECT access_rights,object_id FROM acl WHERE user_id=-2147483647")); // Get group Admins object acl
49 DB_STATEMENT hStmt
= DBPrepare(g_hCoreDB
, _T("UPDATE acl SET access_rights=? WHERE user_id=-2147483647 AND object_id=? "));
52 int nRows
= DBGetNumRows(hResult
);
54 for(int i
= 0; i
< nRows
; i
++)
56 rights
= DBGetFieldULong(hResult
, i
, 0);
57 if (rights
& OBJECT_ACCESS_READ
)
59 rights
|= (OBJECT_ACCESS_READ_AGENT
| OBJECT_ACCESS_READ_SNMP
| OBJECT_ACCESS_SCREENSHOT
);
60 DBBind(hStmt
, 1, DB_SQLTYPE_INTEGER
, rights
);
61 DBBind(hStmt
, 2, DB_SQLTYPE_INTEGER
, DBGetFieldULong(hResult
, i
, 1));
63 if (!SQLExecute(hStmt
))
67 DBFreeStatement(hStmt
);
68 DBFreeResult(hResult
);
75 DBFreeStatement(hStmt
);
77 else if (!g_bIgnoreErrors
)
79 DBFreeResult(hResult
);
81 else if (!g_bIgnoreErrors
)
83 CHK_EXEC(SetSchemaLevelForMajorVersion(21, 4));
85 CHK_EXEC(SetMinorSchemaVersion(7));
90 * Upgrade from 30.5 to 30.6 (changes also included into 21.3)
92 static bool H_UpgradeFromV5()
94 if (GetSchemaLevelForMajorVersion(21) < 3)
96 static const TCHAR
*batch
=
97 _T("UPDATE nodes SET fail_time_snmp=0 WHERE fail_time_snmp IS NULL\n")
98 _T("UPDATE nodes SET fail_time_agent=0 WHERE fail_time_agent IS NULL\n")
100 CHK_EXEC(SQLBatch(batch
));
102 CHK_EXEC(DBSetNotNullConstraint(g_hCoreDB
, _T("nodes"), _T("fail_time_snmp")));
103 CHK_EXEC(DBSetNotNullConstraint(g_hCoreDB
, _T("nodes"), _T("fail_time_agent")));
104 CHK_EXEC(SetSchemaLevelForMajorVersion(21, 3));
106 CHK_EXEC(SetMinorSchemaVersion(6));
111 * Upgrade from 30.4 to 30.5 (changes also included into 21.2)
113 static bool H_UpgradeFromV4()
115 if (GetSchemaLevelForMajorVersion(21) < 2)
117 static const TCHAR
*batch
=
118 _T("ALTER TABLE nodes ADD fail_time_snmp integer\n")
119 _T("ALTER TABLE nodes ADD fail_time_agent integer\n")
121 CHK_EXEC(SQLBatch(batch
));
122 CHK_EXEC(SetSchemaLevelForMajorVersion(21, 2));
124 CHK_EXEC(SetMinorSchemaVersion(5));
129 * Move object flags from old to new tables
131 static BOOL
MoveFlagsFromOldTables(const TCHAR
*tableName
)
134 _sntprintf(query
, 256, _T("SELECT id,flags FROM %s"), tableName
);
135 DB_RESULT hResult
= DBSelect(g_hCoreDB
, query
);
136 DB_STATEMENT hStmt
= DBPrepare(g_hCoreDB
, _T("UPDATE object_properties SET flags=? WHERE object_id=?"));
141 int nRows
= DBGetNumRows(hResult
);
142 for(int i
= 0; i
< nRows
; i
++)
144 DBBind(hStmt
, 1, DB_SQLTYPE_INTEGER
, DBGetFieldULong(hResult
, i
, 1));
145 DBBind(hStmt
, 2, DB_SQLTYPE_INTEGER
, DBGetFieldULong(hResult
, i
, 0));
147 if (!SQLExecute(hStmt
))
149 if (!g_bIgnoreErrors
)
151 DBFreeStatement(hStmt
);
152 DBFreeResult(hResult
);
157 DBFreeStatement(hStmt
);
159 else if (!g_bIgnoreErrors
)
163 DBFreeResult(hResult
);
165 else if (!g_bIgnoreErrors
)
170 CHK_EXEC(DBDropColumn(g_hCoreDB
, tableName
, _T("flags")));
177 inline void MoveFlag(UINT32 oldVar
, UINT32
*newVar
, UINT32 oldFlag
, UINT32 newFlag
)
179 *newVar
|= ((oldVar
& oldFlag
) != 0) ? newFlag
: 0;
185 static void MoveNodeFlags(UINT32 oldFlag
, UINT32
*flags
)
187 MoveFlag(oldFlag
, flags
, 0x10000000, DCF_DISABLE_STATUS_POLL
);
188 MoveFlag(oldFlag
, flags
, 0x20000000, DCF_DISABLE_CONF_POLL
);
189 MoveFlag(oldFlag
, flags
, 0x80000000, DCF_DISABLE_DATA_COLLECT
);
190 MoveFlag(oldFlag
, flags
, 0x00000080, NF_REMOTE_AGENT
);
191 MoveFlag(oldFlag
, flags
, 0x00400000, NF_DISABLE_DISCOVERY_POLL
);
192 MoveFlag(oldFlag
, flags
, 0x00800000, NF_DISABLE_TOPOLOGY_POLL
);
193 MoveFlag(oldFlag
, flags
, 0x01000000, NF_DISABLE_SNMP
);
194 MoveFlag(oldFlag
, flags
, 0x02000000, NF_DISABLE_NXCP
);
195 MoveFlag(oldFlag
, flags
, 0x04000000, NF_DISABLE_ICMP
);
196 MoveFlag(oldFlag
, flags
, 0x08000000, NF_FORCE_ENCRYPTION
);
197 MoveFlag(oldFlag
, flags
, 0x40000000, NF_DISABLE_ROUTE_POLL
);
201 * Move node capabilities flags
203 static void MoveNodeCapabilities(UINT32 oldFlag
, UINT32
*capabilities
)
205 MoveFlag(oldFlag
, capabilities
, 0x00000001, NC_IS_SNMP
);
206 MoveFlag(oldFlag
, capabilities
, 0x00000002, NC_IS_NATIVE_AGENT
);
207 MoveFlag(oldFlag
, capabilities
, 0x00000004, NC_IS_BRIDGE
);
208 MoveFlag(oldFlag
, capabilities
, 0x00000008, NC_IS_ROUTER
);
209 MoveFlag(oldFlag
, capabilities
, 0x00000010, NC_IS_LOCAL_MGMT
);
210 MoveFlag(oldFlag
, capabilities
, 0x00000020, NC_IS_PRINTER
);
211 MoveFlag(oldFlag
, capabilities
, 0x00000040, NC_IS_OSPF
);
212 MoveFlag(oldFlag
, capabilities
, 0x00000100, NC_IS_CPSNMP
);
213 MoveFlag(oldFlag
, capabilities
, 0x00000200, NC_IS_CDP
);
214 MoveFlag(oldFlag
, capabilities
, 0x00000400, NC_IS_NDP
);
215 MoveFlag(oldFlag
, capabilities
, 0x00000800, NC_IS_LLDP
);
216 MoveFlag(oldFlag
, capabilities
, 0x00001000, NC_IS_VRRP
);
217 MoveFlag(oldFlag
, capabilities
, 0x00002000, NC_HAS_VLANS
);
218 MoveFlag(oldFlag
, capabilities
, 0x00004000, NC_IS_8021X
);
219 MoveFlag(oldFlag
, capabilities
, 0x00008000, NC_IS_STP
);
220 MoveFlag(oldFlag
, capabilities
, 0x00010000, NC_HAS_ENTITY_MIB
);
221 MoveFlag(oldFlag
, capabilities
, 0x00020000, NC_HAS_IFXTABLE
);
222 MoveFlag(oldFlag
, capabilities
, 0x00040000, NC_HAS_AGENT_IFXCOUNTERS
);
223 MoveFlag(oldFlag
, capabilities
, 0x00080000, NC_HAS_WINPDH
);
224 MoveFlag(oldFlag
, capabilities
, 0x00100000, NC_IS_WIFI_CONTROLLER
);
225 MoveFlag(oldFlag
, capabilities
, 0x00200000, NC_IS_SMCLP
);
229 * Move node state flags
231 static void MoveNodeState(UINT32 oldRuntime
, UINT32
*state
)
233 MoveFlag(oldRuntime
, state
, 0x000004, DCSF_UNREACHABLE
);
234 MoveFlag(oldRuntime
, state
, 0x000008, NSF_AGENT_UNREACHABLE
);
235 MoveFlag(oldRuntime
, state
, 0x000010, NSF_SNMP_UNREACHABLE
);
236 MoveFlag(oldRuntime
, state
, 0x000200, NSF_CPSNMP_UNREACHABLE
);
237 MoveFlag(oldRuntime
, state
, 0x008000, DCSF_NETWORK_PATH_PROBLEM
);
238 MoveFlag(oldRuntime
, state
, 0x020000, NSF_CACHE_MODE_NOT_SUPPORTED
);
242 * Move sensor state flags
244 static void MoveSensorState(UINT32 oldFlag
, UINT32 oldRuntime
, UINT32
*status
)
246 MoveFlag(oldFlag
, status
, 0x00000001, SSF_PROVISIONED
);
247 MoveFlag(oldFlag
, status
, 0x00000002, SSF_REGISTERED
);
248 MoveFlag(oldFlag
, status
, 0x00000004, SSF_ACTIVE
);
249 MoveFlag(oldFlag
, status
, 0x00000008, SSF_CONF_UPDATE_PENDING
);
250 MoveFlag(oldRuntime
, status
, 0x000004, DCSF_UNREACHABLE
);
254 * Upgrade from 30.3 to 30.4
256 static bool H_UpgradeFromV3()
258 static const TCHAR
*batch
=
259 _T("ALTER TABLE object_properties ADD flags integer null\n")
260 _T("ALTER TABLE object_properties ADD state integer null\n")
261 _T("ALTER TABLE nodes ADD capabilities integer null\n")
262 _T("UPDATE object_properties set flags=0,state=0\n")
264 CHK_EXEC(SQLBatch(batch
));
266 //move flags from old tables to the new one
267 CHK_EXEC(MoveFlagsFromOldTables(_T("interfaces")));
268 CHK_EXEC(MoveFlagsFromOldTables(_T("templates")));
269 CHK_EXEC(MoveFlagsFromOldTables(_T("chassis")));
270 CHK_EXEC(MoveFlagsFromOldTables(_T("object_containers")));
271 CHK_EXEC(MoveFlagsFromOldTables(_T("network_maps")));
272 //create special behavior for node and sensor, cluster
274 DB_RESULT hResult
= DBSelect(g_hCoreDB
, _T("SELECT id,runtime_flags FROM nodes"));
275 DB_STATEMENT stmtNetObj
= DBPrepare(g_hCoreDB
, _T("UPDATE object_properties SET flags=?, state=? WHERE object_id=?"));
276 DB_STATEMENT stmtNode
= DBPrepare(g_hCoreDB
, _T("UPDATE nodes SET capabilities=? WHERE id=?"));
279 if (stmtNetObj
!= NULL
&& stmtNode
!= NULL
)
281 int nRows
= DBGetNumRows(hResult
);
282 for(int i
= 0; i
< nRows
; i
++)
284 UINT32 id
= DBGetFieldULong(hResult
, i
, 0);
286 UINT32 oldRuntime
= DBGetFieldULong(hResult
, i
, 1);
289 UINT32 capabilities
= 0;
291 _sntprintf(query
, 256, _T("SELECT node_flags FROM nodes WHERE id=%d"), id
);
292 DB_RESULT flagResult
= DBSelect(g_hCoreDB
, query
);
293 if(DBGetNumRows(flagResult
) >= 1)
295 oldFlags
= DBGetFieldULong(flagResult
, 0, 0);
301 DBFreeStatement(stmtNetObj
);
302 DBFreeStatement(stmtNode
);
303 DBFreeResult(hResult
);
307 MoveNodeFlags(oldFlags
, &flags
);
308 MoveNodeCapabilities(oldFlags
, &capabilities
);
309 MoveNodeState(oldRuntime
, &state
);
311 DBBind(stmtNetObj
, 1, DB_SQLTYPE_INTEGER
, flags
);
312 DBBind(stmtNetObj
, 2, DB_SQLTYPE_INTEGER
, state
);
313 DBBind(stmtNetObj
, 3, DB_SQLTYPE_INTEGER
, id
);
315 DBBind(stmtNode
, 1, DB_SQLTYPE_INTEGER
, capabilities
);
316 DBBind(stmtNode
, 2, DB_SQLTYPE_INTEGER
, id
);
318 if (!(SQLExecute(stmtNetObj
)))
320 if (!g_bIgnoreErrors
)
322 DBFreeStatement(stmtNetObj
);
323 DBFreeStatement(stmtNode
);
324 DBFreeResult(hResult
);
329 if (!SQLExecute(stmtNode
))
331 if (!g_bIgnoreErrors
)
333 DBFreeStatement(stmtNetObj
);
334 DBFreeStatement(stmtNode
);
335 DBFreeResult(hResult
);
340 DBFreeStatement(stmtNetObj
);
341 DBFreeStatement(stmtNode
);
345 if(stmtNetObj
!= NULL
)
346 DBFreeStatement(stmtNetObj
);
349 DBFreeStatement(stmtNode
);
350 if (!g_bIgnoreErrors
)
355 DBFreeResult(hResult
);
357 CHK_EXEC(DBDropColumn(g_hCoreDB
, _T("nodes"), _T("runtime_flags")));
358 CHK_EXEC(DBDropColumn(g_hCoreDB
, _T("nodes"), _T("node_flags")));
361 hResult
= DBSelect(g_hCoreDB
, _T("SELECT id,runtime_flags,flags FROM sensors"));
362 DB_STATEMENT stmt
= DBPrepare(g_hCoreDB
, _T("UPDATE object_properties SET status=? WHERE object_id=?"));
367 int nRows
= DBGetNumRows(hResult
);
368 for(int i
= 0; i
< nRows
; i
++)
371 MoveSensorState(DBGetFieldULong(hResult
, i
, 2), DBGetFieldULong(hResult
, i
, 1), &status
);
373 DBBind(stmt
, 1, DB_SQLTYPE_INTEGER
, status
);
374 DBBind(stmt
, 2, DB_SQLTYPE_INTEGER
, DBGetFieldULong(hResult
, i
, 0));
376 if (!(SQLExecute(stmt
)))
378 if (!g_bIgnoreErrors
)
380 DBFreeStatement(stmt
);
381 DBFreeResult(hResult
);
386 DBFreeStatement(stmt
);
388 else if (!g_bIgnoreErrors
)
392 DBFreeResult(hResult
);
394 CHK_EXEC(DBDropColumn(g_hCoreDB
, _T("sensors"), _T("runtime_flags")));
395 CHK_EXEC(DBDropColumn(g_hCoreDB
, _T("sensors"), _T("flags")));
397 CHK_EXEC(SetMinorSchemaVersion(4));
402 * Upgrade from 30.2 to 30.3
404 static bool H_UpgradeFromV2()
406 CHK_EXEC(DBDropColumn(g_hCoreDB
, _T("event_groups"), _T("range_start")));
407 CHK_EXEC(DBDropColumn(g_hCoreDB
, _T("event_groups"), _T("range_end")));
409 static const TCHAR
*batch
=
410 _T("ALTER TABLE event_groups ADD guid varchar(36) null\n")
411 _T("UPDATE event_groups SET guid='04b326c0-5cc0-411f-8587-2836cb87c920' WHERE id=-2147483647\n")
412 _T("UPDATE event_groups SET guid='b61859c6-1768-4a61-a0cf-eed07d688f66' WHERE id=-2147483646\n")
414 CHK_EXEC(SQLBatch(batch
));
415 DBSetNotNullConstraint(g_hCoreDB
, _T("event_groups"), _T("guid"));
417 CHK_EXEC(SetMinorSchemaVersion(3));
422 * Upgrade from 30.1 to 30.2
424 static bool H_UpgradeFromV1()
426 static const TCHAR
*batch
=
427 _T("ALTER TABLE users ADD created integer null\n")
428 _T("ALTER TABLE user_groups ADD created integer null\n")
429 _T("UPDATE users SET created=0\n")
430 _T("UPDATE user_groups SET created=0\n")
432 CHK_EXEC(SQLBatch(batch
));
433 CHK_EXEC(DBSetNotNullConstraint(g_hCoreDB
, _T("users"), _T("created")));
434 CHK_EXEC(DBSetNotNullConstraint(g_hCoreDB
, _T("user_groups"), _T("created")));
436 CHK_EXEC(SetMinorSchemaVersion(2));
441 * Upgrade from 30.0 to 30.1
443 static bool H_UpgradeFromV0()
445 CHK_EXEC(CreateTable(
446 _T("CREATE TABLE sensors (")
447 _T(" id integer not null,")
448 _T(" proxy_node integer not null,")
449 _T(" flags integer not null,")
450 _T(" mac_address varchar(16) null,")
451 _T(" device_class integer not null,")
452 _T(" vendor varchar(128) null,")
453 _T(" communication_protocol integer not null,")
454 _T(" xml_config varchar(4000) null,")
455 _T(" xml_reg_config varchar(4000) null,")
456 _T(" serial_number varchar(256) null,")
457 _T(" device_address varchar(256) null,")
458 _T(" meta_type varchar(256) null,")
459 _T(" description varchar(512) null,")
460 _T(" last_connection_time integer not null,")
461 _T(" frame_count integer not null,")
462 _T(" signal_strenght integer not null,")
463 _T(" signal_noise integer not null,")
464 _T(" frequency integer not null,")
465 _T(" runtime_flags integer null,")
466 _T("PRIMARY KEY(id))")));
468 CHK_EXEC(SetMinorSchemaVersion(1));
480 bool (* upgradeProc
)();
483 { 7, 30, 8, H_UpgradeFromV7
},
484 { 6, 30, 7, H_UpgradeFromV6
},
485 { 5, 30, 6, H_UpgradeFromV5
},
486 { 4, 30, 5, H_UpgradeFromV4
},
487 { 3, 30, 4, H_UpgradeFromV3
},
488 { 2, 30, 3, H_UpgradeFromV2
},
489 { 1, 30, 2, H_UpgradeFromV1
},
490 { 0, 30, 1, H_UpgradeFromV0
},
495 * Upgrade database to new version
497 bool MajorSchemaUpgrade_V30()
500 if (!DBGetSchemaVersion(g_hCoreDB
, &major
, &minor
))
503 while((major
== 30) && (minor
< DB_SCHEMA_VERSION_V30_MINOR
))
505 // Find upgrade procedure
507 for(i
= 0; s_dbUpgradeMap
[i
].upgradeProc
!= NULL
; i
++)
508 if (s_dbUpgradeMap
[i
].version
== minor
)
510 if (s_dbUpgradeMap
[i
].upgradeProc
== NULL
)
512 _tprintf(_T("Unable to find upgrade procedure for version 30.%d\n"), minor
);
515 _tprintf(_T("Upgrading from version 30.%d to %d.%d\n"), minor
, s_dbUpgradeMap
[i
].nextMajor
, s_dbUpgradeMap
[i
].nextMinor
);
517 if (s_dbUpgradeMap
[i
].upgradeProc())
520 if (!DBGetSchemaVersion(g_hCoreDB
, &major
, &minor
))
525 _tprintf(_T("Rolling back last stage due to upgrade errors...\n"));
526 DBRollback(g_hCoreDB
);