07df456b8853c23cc6d5f80bbd239a322c4d9c6e
[public/netxms.git] / src / server / tools / nxdbmgr / upgrade_v30.cpp
1 /*
2 ** nxdbmgr - NetXMS database manager
3 ** Copyright (C) 2004-2017 Victor Kirhenshtein
4 **
5 ** This program is free software; you can redistribute it and/or modify
6 ** it under the terms of the GNU General Public License as published by
7 ** the Free Software Foundation; either version 2 of the License, or
8 ** (at your option) any later version.
9 **
10 ** This program is distributed in the hope that it will be useful,
11 ** but WITHOUT ANY WARRANTY; without even the implied warranty of
12 ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 ** GNU General Public License for more details.
14 **
15 ** You should have received a copy of the GNU General Public License
16 ** along with this program; if not, write to the Free Software
17 ** Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
18 **
19 ** File: upgrade_v30.cpp
20 **
21 **/
22
23 #include "nxdbmgr.h"
24
25 /**
26 * Upgrade from 30.7 to 30.8 (changes also included into 22.1)
27 */
28 static bool H_UpgradeFromV7()
29 {
30 if (GetSchemaLevelForMajorVersion(22) < 1)
31 {
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));
34 }
35 CHK_EXEC(SetMinorSchemaVersion(8));
36 return true;
37 }
38
39 /**
40 * Upgrade from 30.6 to 30.7 (changes also included into 21.4)
41 */
42 static bool H_UpgradeFromV6()
43 {
44 if (GetSchemaLevelForMajorVersion(21) < 4)
45 {
46 DB_RESULT hResult = DBSelect(g_hCoreDB, _T("SELECT access_rights,object_id FROM acl WHERE user_id=-2147483647")); // Get group Admins object acl
47 if (hResult != NULL)
48 {
49 DB_STATEMENT hStmt = DBPrepare(g_hCoreDB, _T("UPDATE acl SET access_rights=? WHERE user_id=-2147483647 AND object_id=? "));
50 if (hStmt != NULL)
51 {
52 int nRows = DBGetNumRows(hResult);
53 UINT32 rights;
54 for(int i = 0; i < nRows; i++)
55 {
56 rights = DBGetFieldULong(hResult, i, 0);
57 if (rights & OBJECT_ACCESS_READ)
58 {
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));
62
63 if (!SQLExecute(hStmt))
64 {
65 if (!g_bIgnoreErrors)
66 {
67 DBFreeStatement(hStmt);
68 DBFreeResult(hResult);
69 return FALSE;
70 }
71 }
72 }
73 }
74
75 DBFreeStatement(hStmt);
76 }
77 else if (!g_bIgnoreErrors)
78 return FALSE;
79 DBFreeResult(hResult);
80 }
81 else if (!g_bIgnoreErrors)
82 return false;
83 CHK_EXEC(SetSchemaLevelForMajorVersion(21, 4));
84 }
85 CHK_EXEC(SetMinorSchemaVersion(7));
86 return true;
87 }
88
89 /**
90 * Upgrade from 30.5 to 30.6 (changes also included into 21.3)
91 */
92 static bool H_UpgradeFromV5()
93 {
94 if (GetSchemaLevelForMajorVersion(21) < 3)
95 {
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")
99 _T("<END>");
100 CHK_EXEC(SQLBatch(batch));
101
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));
105 }
106 CHK_EXEC(SetMinorSchemaVersion(6));
107 return true;
108 }
109
110 /**
111 * Upgrade from 30.4 to 30.5 (changes also included into 21.2)
112 */
113 static bool H_UpgradeFromV4()
114 {
115 if (GetSchemaLevelForMajorVersion(21) < 2)
116 {
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")
120 _T("<END>");
121 CHK_EXEC(SQLBatch(batch));
122 CHK_EXEC(SetSchemaLevelForMajorVersion(21, 2));
123 }
124 CHK_EXEC(SetMinorSchemaVersion(5));
125 return true;
126 }
127
128 /**
129 * Move object flags from old to new tables
130 */
131 static BOOL MoveFlagsFromOldTables(const TCHAR *tableName)
132 {
133 TCHAR query[256];
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=?"));
137 if (hResult != NULL)
138 {
139 if (hStmt != NULL)
140 {
141 int nRows = DBGetNumRows(hResult);
142 for(int i = 0; i < nRows; i++)
143 {
144 DBBind(hStmt, 1, DB_SQLTYPE_INTEGER, DBGetFieldULong(hResult, i, 1));
145 DBBind(hStmt, 2, DB_SQLTYPE_INTEGER, DBGetFieldULong(hResult, i, 0));
146
147 if (!SQLExecute(hStmt))
148 {
149 if (!g_bIgnoreErrors)
150 {
151 DBFreeStatement(hStmt);
152 DBFreeResult(hResult);
153 return FALSE;
154 }
155 }
156 }
157 DBFreeStatement(hStmt);
158 }
159 else if (!g_bIgnoreErrors)
160 {
161 return FALSE;
162 }
163 DBFreeResult(hResult);
164 }
165 else if (!g_bIgnoreErrors)
166 {
167 return FALSE;
168 }
169
170 CHK_EXEC(DBDropColumn(g_hCoreDB, tableName, _T("flags")));
171 return TRUE;
172 }
173
174 /**
175 * Move single flag
176 */
177 inline void MoveFlag(UINT32 oldVar, UINT32 *newVar, UINT32 oldFlag, UINT32 newFlag)
178 {
179 *newVar |= ((oldVar & oldFlag) != 0) ? newFlag : 0;
180 }
181
182 /**
183 * Move node flags
184 */
185 static void MoveNodeFlags(UINT32 oldFlag, UINT32 *flags)
186 {
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);
198 }
199
200 /**
201 * Move node capabilities flags
202 */
203 static void MoveNodeCapabilities(UINT32 oldFlag, UINT32 *capabilities)
204 {
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);
226 }
227
228 /**
229 * Move node state flags
230 */
231 static void MoveNodeState(UINT32 oldRuntime, UINT32 *state)
232 {
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);
239 }
240
241 /**
242 * Move sensor state flags
243 */
244 static void MoveSensorState(UINT32 oldFlag, UINT32 oldRuntime, UINT32 *status)
245 {
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);
251 }
252
253 /**
254 * Upgrade from 30.3 to 30.4
255 */
256 static bool H_UpgradeFromV3()
257 {
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")
263 _T("<END>");
264 CHK_EXEC(SQLBatch(batch));
265
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
273 //node
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=?"));
277 if (hResult != NULL)
278 {
279 if (stmtNetObj != NULL && stmtNode != NULL)
280 {
281 int nRows = DBGetNumRows(hResult);
282 for(int i = 0; i < nRows; i++)
283 {
284 UINT32 id = DBGetFieldULong(hResult, i, 0);
285 UINT32 oldFlags = 0;
286 UINT32 oldRuntime = DBGetFieldULong(hResult, i, 1);
287 UINT32 flags = 0;
288 UINT32 state = 0;
289 UINT32 capabilities = 0;
290 TCHAR query[256];
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)
294 {
295 oldFlags = DBGetFieldULong(flagResult, 0, 0);
296 }
297 else
298 {
299 if(!g_bIgnoreErrors)
300 {
301 DBFreeStatement(stmtNetObj);
302 DBFreeStatement(stmtNode);
303 DBFreeResult(hResult);
304 return FALSE;
305 }
306 }
307 MoveNodeFlags(oldFlags, &flags);
308 MoveNodeCapabilities(oldFlags, &capabilities);
309 MoveNodeState(oldRuntime, &state);
310
311 DBBind(stmtNetObj, 1, DB_SQLTYPE_INTEGER, flags);
312 DBBind(stmtNetObj, 2, DB_SQLTYPE_INTEGER, state);
313 DBBind(stmtNetObj, 3, DB_SQLTYPE_INTEGER, id);
314
315 DBBind(stmtNode, 1, DB_SQLTYPE_INTEGER, capabilities);
316 DBBind(stmtNode, 2, DB_SQLTYPE_INTEGER, id);
317
318 if (!(SQLExecute(stmtNetObj)))
319 {
320 if (!g_bIgnoreErrors)
321 {
322 DBFreeStatement(stmtNetObj);
323 DBFreeStatement(stmtNode);
324 DBFreeResult(hResult);
325 return FALSE;
326 }
327 }
328
329 if (!SQLExecute(stmtNode))
330 {
331 if (!g_bIgnoreErrors)
332 {
333 DBFreeStatement(stmtNetObj);
334 DBFreeStatement(stmtNode);
335 DBFreeResult(hResult);
336 return FALSE;
337 }
338 }
339 }
340 DBFreeStatement(stmtNetObj);
341 DBFreeStatement(stmtNode);
342 }
343 else
344 {
345 if(stmtNetObj != NULL)
346 DBFreeStatement(stmtNetObj);
347
348 if(stmtNode != NULL)
349 DBFreeStatement(stmtNode);
350 if (!g_bIgnoreErrors)
351 {
352 return FALSE;
353 }
354 }
355 DBFreeResult(hResult);
356 }
357 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("nodes"), _T("runtime_flags")));
358 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("nodes"), _T("node_flags")));
359
360 //sensor
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=?"));
363 if (hResult != NULL)
364 {
365 if (stmt != NULL)
366 {
367 int nRows = DBGetNumRows(hResult);
368 for(int i = 0; i < nRows; i++)
369 {
370 UINT32 status = 0;
371 MoveSensorState(DBGetFieldULong(hResult, i, 2), DBGetFieldULong(hResult, i, 1), &status);
372
373 DBBind(stmt, 1, DB_SQLTYPE_INTEGER, status);
374 DBBind(stmt, 2, DB_SQLTYPE_INTEGER, DBGetFieldULong(hResult, i, 0));
375
376 if (!(SQLExecute(stmt)))
377 {
378 if (!g_bIgnoreErrors)
379 {
380 DBFreeStatement(stmt);
381 DBFreeResult(hResult);
382 return FALSE;
383 }
384 }
385 }
386 DBFreeStatement(stmt);
387 }
388 else if (!g_bIgnoreErrors)
389 {
390 return FALSE;
391 }
392 DBFreeResult(hResult);
393 }
394 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("sensors"), _T("runtime_flags")));
395 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("sensors"), _T("flags")));
396
397 CHK_EXEC(SetMinorSchemaVersion(4));
398 return true;
399 }
400
401 /**
402 * Upgrade from 30.2 to 30.3
403 */
404 static bool H_UpgradeFromV2()
405 {
406 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("event_groups"), _T("range_start")));
407 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("event_groups"), _T("range_end")));
408
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")
413 _T("<END>");
414 CHK_EXEC(SQLBatch(batch));
415 DBSetNotNullConstraint(g_hCoreDB, _T("event_groups"), _T("guid"));
416
417 CHK_EXEC(SetMinorSchemaVersion(3));
418 return true;
419 }
420
421 /**
422 * Upgrade from 30.1 to 30.2
423 */
424 static bool H_UpgradeFromV1()
425 {
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")
431 _T("<END>");
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")));
435
436 CHK_EXEC(SetMinorSchemaVersion(2));
437 return true;
438 }
439
440 /**
441 * Upgrade from 30.0 to 30.1
442 */
443 static bool H_UpgradeFromV0()
444 {
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))")));
467
468 CHK_EXEC(SetMinorSchemaVersion(1));
469 return true;
470 }
471
472 /**
473 * Upgrade map
474 */
475 static struct
476 {
477 int version;
478 int nextMajor;
479 int nextMinor;
480 bool (* upgradeProc)();
481 } s_dbUpgradeMap[] =
482 {
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 },
491 { 0, 0, 0, NULL }
492 };
493
494 /**
495 * Upgrade database to new version
496 */
497 bool MajorSchemaUpgrade_V30()
498 {
499 INT32 major, minor;
500 if (!DBGetSchemaVersion(g_hCoreDB, &major, &minor))
501 return false;
502
503 while((major == 30) && (minor < DB_SCHEMA_VERSION_V30_MINOR))
504 {
505 // Find upgrade procedure
506 int i;
507 for(i = 0; s_dbUpgradeMap[i].upgradeProc != NULL; i++)
508 if (s_dbUpgradeMap[i].version == minor)
509 break;
510 if (s_dbUpgradeMap[i].upgradeProc == NULL)
511 {
512 _tprintf(_T("Unable to find upgrade procedure for version 30.%d\n"), minor);
513 return false;
514 }
515 _tprintf(_T("Upgrading from version 30.%d to %d.%d\n"), minor, s_dbUpgradeMap[i].nextMajor, s_dbUpgradeMap[i].nextMinor);
516 DBBegin(g_hCoreDB);
517 if (s_dbUpgradeMap[i].upgradeProc())
518 {
519 DBCommit(g_hCoreDB);
520 if (!DBGetSchemaVersion(g_hCoreDB, &major, &minor))
521 return false;
522 }
523 else
524 {
525 _tprintf(_T("Rolling back last stage due to upgrade errors...\n"));
526 DBRollback(g_hCoreDB);
527 return false;
528 }
529 }
530 return true;
531 }