c640bf56de5be56ab96641b20d13d1baf2c177f2
[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 int count = ConfigReadInt(_T("NumberOfDataCollectors"), 250);
33 TCHAR value[64];
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'")));
39 }
40 CHK_EXEC(SetMinorSchemaVersion(8));
41 return true;
42 }
43
44 /**
45 * Upgrade from 30.6 to 30.7 (changes also included into 21.4)
46 */
47 static bool H_UpgradeFromV6()
48 {
49 if (GetSchemaLevelForMajorVersion(21) < 4)
50 {
51 DB_RESULT hResult = DBSelect(g_hCoreDB, _T("SELECT access_rights,object_id FROM acl WHERE user_id=-2147483647")); // Get group Admins object acl
52 if (hResult != NULL)
53 {
54 DB_STATEMENT hStmt = DBPrepare(g_hCoreDB, _T("UPDATE acl SET access_rights=? WHERE user_id=-2147483647 AND object_id=? "));
55 if (hStmt != NULL)
56 {
57 int nRows = DBGetNumRows(hResult);
58 UINT32 rights;
59 for(int i = 0; i < nRows; i++)
60 {
61 rights = DBGetFieldULong(hResult, i, 0);
62 if (rights & OBJECT_ACCESS_READ)
63 {
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));
67
68 if (!SQLExecute(hStmt))
69 {
70 if (!g_bIgnoreErrors)
71 {
72 DBFreeStatement(hStmt);
73 DBFreeResult(hResult);
74 return FALSE;
75 }
76 }
77 }
78 }
79
80 DBFreeStatement(hStmt);
81 }
82 else if (!g_bIgnoreErrors)
83 return FALSE;
84 DBFreeResult(hResult);
85 }
86 else if (!g_bIgnoreErrors)
87 return false;
88 CHK_EXEC(SetSchemaLevelForMajorVersion(21, 4));
89 }
90 CHK_EXEC(SetMinorSchemaVersion(7));
91 return true;
92 }
93
94 /**
95 * Upgrade from 30.5 to 30.6 (changes also included into 21.3)
96 */
97 static bool H_UpgradeFromV5()
98 {
99 if (GetSchemaLevelForMajorVersion(21) < 3)
100 {
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")
104 _T("<END>");
105 CHK_EXEC(SQLBatch(batch));
106
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));
110 }
111 CHK_EXEC(SetMinorSchemaVersion(6));
112 return true;
113 }
114
115 /**
116 * Upgrade from 30.4 to 30.5 (changes also included into 21.2)
117 */
118 static bool H_UpgradeFromV4()
119 {
120 if (GetSchemaLevelForMajorVersion(21) < 2)
121 {
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")
125 _T("<END>");
126 CHK_EXEC(SQLBatch(batch));
127 CHK_EXEC(SetSchemaLevelForMajorVersion(21, 2));
128 }
129 CHK_EXEC(SetMinorSchemaVersion(5));
130 return true;
131 }
132
133 /**
134 * Move object flags from old to new tables
135 */
136 static BOOL MoveFlagsFromOldTables(const TCHAR *tableName)
137 {
138 TCHAR query[256];
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=?"));
142 if (hResult != NULL)
143 {
144 if (hStmt != NULL)
145 {
146 int nRows = DBGetNumRows(hResult);
147 for(int i = 0; i < nRows; i++)
148 {
149 DBBind(hStmt, 1, DB_SQLTYPE_INTEGER, DBGetFieldULong(hResult, i, 1));
150 DBBind(hStmt, 2, DB_SQLTYPE_INTEGER, DBGetFieldULong(hResult, i, 0));
151
152 if (!SQLExecute(hStmt))
153 {
154 if (!g_bIgnoreErrors)
155 {
156 DBFreeStatement(hStmt);
157 DBFreeResult(hResult);
158 return FALSE;
159 }
160 }
161 }
162 DBFreeStatement(hStmt);
163 }
164 else if (!g_bIgnoreErrors)
165 {
166 return FALSE;
167 }
168 DBFreeResult(hResult);
169 }
170 else if (!g_bIgnoreErrors)
171 {
172 return FALSE;
173 }
174
175 CHK_EXEC(DBDropColumn(g_hCoreDB, tableName, _T("flags")));
176 return TRUE;
177 }
178
179 /**
180 * Move single flag
181 */
182 inline void MoveFlag(UINT32 oldVar, UINT32 *newVar, UINT32 oldFlag, UINT32 newFlag)
183 {
184 *newVar |= ((oldVar & oldFlag) != 0) ? newFlag : 0;
185 }
186
187 /**
188 * Move node flags
189 */
190 static void MoveNodeFlags(UINT32 oldFlag, UINT32 *flags)
191 {
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);
203 }
204
205 /**
206 * Move node capabilities flags
207 */
208 static void MoveNodeCapabilities(UINT32 oldFlag, UINT32 *capabilities)
209 {
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);
231 }
232
233 /**
234 * Move node state flags
235 */
236 static void MoveNodeState(UINT32 oldRuntime, UINT32 *state)
237 {
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);
244 }
245
246 /**
247 * Move sensor state flags
248 */
249 static void MoveSensorState(UINT32 oldFlag, UINT32 oldRuntime, UINT32 *status)
250 {
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);
256 }
257
258 /**
259 * Upgrade from 30.3 to 30.4
260 */
261 static bool H_UpgradeFromV3()
262 {
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")
268 _T("<END>");
269 CHK_EXEC(SQLBatch(batch));
270
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
278 //node
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=?"));
282 if (hResult != NULL)
283 {
284 if (stmtNetObj != NULL && stmtNode != NULL)
285 {
286 int nRows = DBGetNumRows(hResult);
287 for(int i = 0; i < nRows; i++)
288 {
289 UINT32 id = DBGetFieldULong(hResult, i, 0);
290 UINT32 oldFlags = 0;
291 UINT32 oldRuntime = DBGetFieldULong(hResult, i, 1);
292 UINT32 flags = 0;
293 UINT32 state = 0;
294 UINT32 capabilities = 0;
295 TCHAR query[256];
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)
299 {
300 oldFlags = DBGetFieldULong(flagResult, 0, 0);
301 }
302 else
303 {
304 if(!g_bIgnoreErrors)
305 {
306 DBFreeStatement(stmtNetObj);
307 DBFreeStatement(stmtNode);
308 DBFreeResult(hResult);
309 return FALSE;
310 }
311 }
312 MoveNodeFlags(oldFlags, &flags);
313 MoveNodeCapabilities(oldFlags, &capabilities);
314 MoveNodeState(oldRuntime, &state);
315
316 DBBind(stmtNetObj, 1, DB_SQLTYPE_INTEGER, flags);
317 DBBind(stmtNetObj, 2, DB_SQLTYPE_INTEGER, state);
318 DBBind(stmtNetObj, 3, DB_SQLTYPE_INTEGER, id);
319
320 DBBind(stmtNode, 1, DB_SQLTYPE_INTEGER, capabilities);
321 DBBind(stmtNode, 2, DB_SQLTYPE_INTEGER, id);
322
323 if (!(SQLExecute(stmtNetObj)))
324 {
325 if (!g_bIgnoreErrors)
326 {
327 DBFreeStatement(stmtNetObj);
328 DBFreeStatement(stmtNode);
329 DBFreeResult(hResult);
330 return FALSE;
331 }
332 }
333
334 if (!SQLExecute(stmtNode))
335 {
336 if (!g_bIgnoreErrors)
337 {
338 DBFreeStatement(stmtNetObj);
339 DBFreeStatement(stmtNode);
340 DBFreeResult(hResult);
341 return FALSE;
342 }
343 }
344 }
345 DBFreeStatement(stmtNetObj);
346 DBFreeStatement(stmtNode);
347 }
348 else
349 {
350 if(stmtNetObj != NULL)
351 DBFreeStatement(stmtNetObj);
352
353 if(stmtNode != NULL)
354 DBFreeStatement(stmtNode);
355 if (!g_bIgnoreErrors)
356 {
357 return FALSE;
358 }
359 }
360 DBFreeResult(hResult);
361 }
362 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("nodes"), _T("runtime_flags")));
363 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("nodes"), _T("node_flags")));
364
365 //sensor
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=?"));
368 if (hResult != NULL)
369 {
370 if (stmt != NULL)
371 {
372 int nRows = DBGetNumRows(hResult);
373 for(int i = 0; i < nRows; i++)
374 {
375 UINT32 status = 0;
376 MoveSensorState(DBGetFieldULong(hResult, i, 2), DBGetFieldULong(hResult, i, 1), &status);
377
378 DBBind(stmt, 1, DB_SQLTYPE_INTEGER, status);
379 DBBind(stmt, 2, DB_SQLTYPE_INTEGER, DBGetFieldULong(hResult, i, 0));
380
381 if (!(SQLExecute(stmt)))
382 {
383 if (!g_bIgnoreErrors)
384 {
385 DBFreeStatement(stmt);
386 DBFreeResult(hResult);
387 return FALSE;
388 }
389 }
390 }
391 DBFreeStatement(stmt);
392 }
393 else if (!g_bIgnoreErrors)
394 {
395 return FALSE;
396 }
397 DBFreeResult(hResult);
398 }
399 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("sensors"), _T("runtime_flags")));
400 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("sensors"), _T("flags")));
401
402 CHK_EXEC(SetMinorSchemaVersion(4));
403 return true;
404 }
405
406 /**
407 * Upgrade from 30.2 to 30.3
408 */
409 static bool H_UpgradeFromV2()
410 {
411 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("event_groups"), _T("range_start")));
412 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("event_groups"), _T("range_end")));
413
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")
418 _T("<END>");
419 CHK_EXEC(SQLBatch(batch));
420 DBSetNotNullConstraint(g_hCoreDB, _T("event_groups"), _T("guid"));
421
422 CHK_EXEC(SetMinorSchemaVersion(3));
423 return true;
424 }
425
426 /**
427 * Upgrade from 30.1 to 30.2
428 */
429 static bool H_UpgradeFromV1()
430 {
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")
436 _T("<END>");
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")));
440
441 CHK_EXEC(SetMinorSchemaVersion(2));
442 return true;
443 }
444
445 /**
446 * Upgrade from 30.0 to 30.1
447 */
448 static bool H_UpgradeFromV0()
449 {
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))")));
472
473 CHK_EXEC(SetMinorSchemaVersion(1));
474 return true;
475 }
476
477 /**
478 * Upgrade map
479 */
480 static struct
481 {
482 int version;
483 int nextMajor;
484 int nextMinor;
485 bool (* upgradeProc)();
486 } s_dbUpgradeMap[] =
487 {
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 },
496 { 0, 0, 0, NULL }
497 };
498
499 /**
500 * Upgrade database to new version
501 */
502 bool MajorSchemaUpgrade_V30()
503 {
504 INT32 major, minor;
505 if (!DBGetSchemaVersion(g_hCoreDB, &major, &minor))
506 return false;
507
508 while((major == 30) && (minor < DB_SCHEMA_VERSION_V30_MINOR))
509 {
510 // Find upgrade procedure
511 int i;
512 for(i = 0; s_dbUpgradeMap[i].upgradeProc != NULL; i++)
513 if (s_dbUpgradeMap[i].version == minor)
514 break;
515 if (s_dbUpgradeMap[i].upgradeProc == NULL)
516 {
517 _tprintf(_T("Unable to find upgrade procedure for version 30.%d\n"), minor);
518 return false;
519 }
520 _tprintf(_T("Upgrading from version 30.%d to %d.%d\n"), minor, s_dbUpgradeMap[i].nextMajor, s_dbUpgradeMap[i].nextMinor);
521 DBBegin(g_hCoreDB);
522 if (s_dbUpgradeMap[i].upgradeProc())
523 {
524 DBCommit(g_hCoreDB);
525 if (!DBGetSchemaVersion(g_hCoreDB, &major, &minor))
526 return false;
527 }
528 else
529 {
530 _tprintf(_T("Rolling back last stage due to upgrade errors...\n"));
531 DBRollback(g_hCoreDB);
532 return false;
533 }
534 }
535 return true;
536 }