fixed DB upgrade procedures
[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.6 to 30.7 (changes also included into 21.4)
27 */
28 static bool H_UpgradeFromV6()
29 {
30 if (GetSchemaLevelForMajorVersion(21) < 4)
31 {
32 DB_RESULT hResult = DBSelect(g_hCoreDB, _T("SELECT access_rights,object_id FROM acl WHERE user_id=-2147483647")); // Get group Admins object acl
33 if (hResult != NULL)
34 {
35 DB_STATEMENT hStmt = DBPrepare(g_hCoreDB, _T("UPDATE acl SET access_rights=? WHERE user_id=-2147483647 AND object_id=? "));
36 if (hStmt != NULL)
37 {
38 int nRows = DBGetNumRows(hResult);
39 UINT32 rights;
40 for(int i = 0; i < nRows; i++)
41 {
42 rights = DBGetFieldULong(hResult, i, 0);
43 if (rights & OBJECT_ACCESS_READ)
44 {
45 rights |= (OBJECT_ACCESS_READ_AGENT | OBJECT_ACCESS_READ_SNMP | OBJECT_ACCESS_SCREENSHOT);
46 DBBind(hStmt, 1, DB_SQLTYPE_INTEGER, rights);
47 DBBind(hStmt, 2, DB_SQLTYPE_INTEGER, DBGetFieldULong(hResult, i, 1));
48
49 if (!SQLExecute(hStmt))
50 {
51 if (!g_bIgnoreErrors)
52 {
53 DBFreeStatement(hStmt);
54 DBFreeResult(hResult);
55 return FALSE;
56 }
57 }
58 }
59 }
60
61 DBFreeStatement(hStmt);
62 }
63 else if (!g_bIgnoreErrors)
64 return FALSE;
65 DBFreeResult(hResult);
66 }
67 else if (!g_bIgnoreErrors)
68 return false;
69 CHK_EXEC(SetSchemaLevelForMajorVersion(21, 4));
70 }
71 CHK_EXEC(SetMinorSchemaVersion(7));
72 return true;
73 }
74
75 /**
76 * Upgrade from 30.5 to 30.6 (changes also included into 21.3)
77 */
78 static bool H_UpgradeFromV5()
79 {
80 if (GetSchemaLevelForMajorVersion(21) < 3)
81 {
82 static const TCHAR *batch =
83 _T("UPDATE nodes SET fail_time_snmp=0 WHERE fail_time_snmp IS NULL\n")
84 _T("UPDATE nodes SET fail_time_agent=0 WHERE fail_time_agent IS NULL\n")
85 _T("<END>");
86 CHK_EXEC(SQLBatch(batch));
87
88 CHK_EXEC(DBSetNotNullConstraint(g_hCoreDB, _T("nodes"), _T("fail_time_snmp")));
89 CHK_EXEC(DBSetNotNullConstraint(g_hCoreDB, _T("nodes"), _T("fail_time_agent")));
90 CHK_EXEC(SetSchemaLevelForMajorVersion(21, 3));
91 }
92 CHK_EXEC(SetMinorSchemaVersion(6));
93 return true;
94 }
95
96 /**
97 * Upgrade from 30.4 to 30.5 (changes also included into 21.2)
98 */
99 static bool H_UpgradeFromV4()
100 {
101 if (GetSchemaLevelForMajorVersion(21) < 2)
102 {
103 static const TCHAR *batch =
104 _T("ALTER TABLE nodes ADD fail_time_snmp integer\n")
105 _T("ALTER TABLE nodes ADD fail_time_agent integer\n")
106 _T("<END>");
107 CHK_EXEC(SQLBatch(batch));
108 CHK_EXEC(SetSchemaLevelForMajorVersion(21, 2));
109 }
110 CHK_EXEC(SetMinorSchemaVersion(5));
111 return true;
112 }
113
114 /**
115 * Move object flags from old to new tables
116 */
117 static BOOL MoveFlagsFromOldTables(const TCHAR *tableName)
118 {
119 TCHAR query[256];
120 _sntprintf(query, 256, _T("SELECT id,flags FROM %s"), tableName);
121 DB_RESULT hResult = DBSelect(g_hCoreDB, query);
122 DB_STATEMENT hStmt = DBPrepare(g_hCoreDB, _T("UPDATE object_properties SET flags=? WHERE object_id=?"));
123 if (hResult != NULL)
124 {
125 if (hStmt != NULL)
126 {
127 int nRows = DBGetNumRows(hResult);
128 for(int i = 0; i < nRows; i++)
129 {
130 DBBind(hStmt, 1, DB_SQLTYPE_INTEGER, DBGetFieldULong(hResult, i, 1));
131 DBBind(hStmt, 2, DB_SQLTYPE_INTEGER, DBGetFieldULong(hResult, i, 0));
132
133 if (!SQLExecute(hStmt))
134 {
135 if (!g_bIgnoreErrors)
136 {
137 DBFreeStatement(hStmt);
138 DBFreeResult(hResult);
139 return FALSE;
140 }
141 }
142 }
143 DBFreeStatement(hStmt);
144 }
145 else if (!g_bIgnoreErrors)
146 {
147 return FALSE;
148 }
149 DBFreeResult(hResult);
150 }
151 else if (!g_bIgnoreErrors)
152 {
153 return FALSE;
154 }
155
156 CHK_EXEC(DBDropColumn(g_hCoreDB, tableName, _T("flags")));
157 return TRUE;
158 }
159
160 /**
161 * Move single flag
162 */
163 inline void MoveFlag(UINT32 oldVar, UINT32 *newVar, UINT32 oldFlag, UINT32 newFlag)
164 {
165 *newVar |= ((oldVar & oldFlag) != 0) ? newFlag : 0;
166 }
167
168 /**
169 * Move node flags
170 */
171 static void MoveNodeFlags(UINT32 oldFlag, UINT32 *flags)
172 {
173 MoveFlag(oldFlag, flags, 0x10000000, DCF_DISABLE_STATUS_POLL);
174 MoveFlag(oldFlag, flags, 0x20000000, DCF_DISABLE_CONF_POLL);
175 MoveFlag(oldFlag, flags, 0x80000000, DCF_DISABLE_DATA_COLLECT);
176 MoveFlag(oldFlag, flags, 0x00000080, NF_REMOTE_AGENT);
177 MoveFlag(oldFlag, flags, 0x00400000, NF_DISABLE_DISCOVERY_POLL);
178 MoveFlag(oldFlag, flags, 0x00800000, NF_DISABLE_TOPOLOGY_POLL);
179 MoveFlag(oldFlag, flags, 0x01000000, NF_DISABLE_SNMP);
180 MoveFlag(oldFlag, flags, 0x02000000, NF_DISABLE_NXCP);
181 MoveFlag(oldFlag, flags, 0x04000000, NF_DISABLE_ICMP);
182 MoveFlag(oldFlag, flags, 0x08000000, NF_FORCE_ENCRYPTION);
183 MoveFlag(oldFlag, flags, 0x40000000, NF_DISABLE_ROUTE_POLL);
184 }
185
186 /**
187 * Move node capabilities flags
188 */
189 static void MoveNodeCapabilities(UINT32 oldFlag, UINT32 *capabilities)
190 {
191 MoveFlag(oldFlag, capabilities, 0x00000001, NC_IS_SNMP);
192 MoveFlag(oldFlag, capabilities, 0x00000002, NC_IS_NATIVE_AGENT);
193 MoveFlag(oldFlag, capabilities, 0x00000004, NC_IS_BRIDGE);
194 MoveFlag(oldFlag, capabilities, 0x00000008, NC_IS_ROUTER);
195 MoveFlag(oldFlag, capabilities, 0x00000010, NC_IS_LOCAL_MGMT);
196 MoveFlag(oldFlag, capabilities, 0x00000020, NC_IS_PRINTER);
197 MoveFlag(oldFlag, capabilities, 0x00000040, NC_IS_OSPF);
198 MoveFlag(oldFlag, capabilities, 0x00000100, NC_IS_CPSNMP);
199 MoveFlag(oldFlag, capabilities, 0x00000200, NC_IS_CDP);
200 MoveFlag(oldFlag, capabilities, 0x00000400, NC_IS_NDP);
201 MoveFlag(oldFlag, capabilities, 0x00000800, NC_IS_LLDP);
202 MoveFlag(oldFlag, capabilities, 0x00001000, NC_IS_VRRP);
203 MoveFlag(oldFlag, capabilities, 0x00002000, NC_HAS_VLANS);
204 MoveFlag(oldFlag, capabilities, 0x00004000, NC_IS_8021X);
205 MoveFlag(oldFlag, capabilities, 0x00008000, NC_IS_STP);
206 MoveFlag(oldFlag, capabilities, 0x00010000, NC_HAS_ENTITY_MIB);
207 MoveFlag(oldFlag, capabilities, 0x00020000, NC_HAS_IFXTABLE);
208 MoveFlag(oldFlag, capabilities, 0x00040000, NC_HAS_AGENT_IFXCOUNTERS);
209 MoveFlag(oldFlag, capabilities, 0x00080000, NC_HAS_WINPDH);
210 MoveFlag(oldFlag, capabilities, 0x00100000, NC_IS_WIFI_CONTROLLER);
211 MoveFlag(oldFlag, capabilities, 0x00200000, NC_IS_SMCLP);
212 }
213
214 /**
215 * Move node state flags
216 */
217 static void MoveNodeState(UINT32 oldRuntime, UINT32 *state)
218 {
219 MoveFlag(oldRuntime, state, 0x000004, DCSF_UNREACHABLE);
220 MoveFlag(oldRuntime, state, 0x000008, NSF_AGENT_UNREACHABLE);
221 MoveFlag(oldRuntime, state, 0x000010, NSF_SNMP_UNREACHABLE);
222 MoveFlag(oldRuntime, state, 0x000200, NSF_CPSNMP_UNREACHABLE);
223 MoveFlag(oldRuntime, state, 0x008000, DCSF_NETWORK_PATH_PROBLEM);
224 MoveFlag(oldRuntime, state, 0x020000, NSF_CACHE_MODE_NOT_SUPPORTED);
225 }
226
227 /**
228 * Move sensor state flags
229 */
230 static void MoveSensorState(UINT32 oldFlag, UINT32 oldRuntime, UINT32 *status)
231 {
232 MoveFlag(oldFlag, status, 0x00000001, SSF_PROVISIONED);
233 MoveFlag(oldFlag, status, 0x00000002, SSF_REGISTERED);
234 MoveFlag(oldFlag, status, 0x00000004, SSF_ACTIVE);
235 MoveFlag(oldFlag, status, 0x00000008, SSF_CONF_UPDATE_PENDING);
236 MoveFlag(oldRuntime, status, 0x000004, DCSF_UNREACHABLE);
237 }
238
239 /**
240 * Upgrade from 30.3 to 30.4
241 */
242 static bool H_UpgradeFromV3()
243 {
244 static const TCHAR *batch =
245 _T("ALTER TABLE object_properties ADD flags integer null\n")
246 _T("ALTER TABLE object_properties ADD state integer null\n")
247 _T("ALTER TABLE nodes ADD capabilities integer null\n")
248 _T("UPDATE object_properties set flags=0,state=0\n")
249 _T("<END>");
250 CHK_EXEC(SQLBatch(batch));
251
252 //move flags from old tables to the new one
253 CHK_EXEC(MoveFlagsFromOldTables(_T("interfaces")));
254 CHK_EXEC(MoveFlagsFromOldTables(_T("templates")));
255 CHK_EXEC(MoveFlagsFromOldTables(_T("chassis")));
256 CHK_EXEC(MoveFlagsFromOldTables(_T("object_containers")));
257 CHK_EXEC(MoveFlagsFromOldTables(_T("network_maps")));
258 //create special behavior for node and sensor, cluster
259 //node
260 DB_RESULT hResult = DBSelect(g_hCoreDB, _T("SELECT id,runtime_flags FROM nodes"));
261 DB_STATEMENT stmtNetObj = DBPrepare(g_hCoreDB, _T("UPDATE object_properties SET flags=?, state=? WHERE object_id=?"));
262 DB_STATEMENT stmtNode = DBPrepare(g_hCoreDB, _T("UPDATE nodes SET capabilities=? WHERE id=?"));
263 if (hResult != NULL)
264 {
265 if (stmtNetObj != NULL && stmtNode != NULL)
266 {
267 int nRows = DBGetNumRows(hResult);
268 for(int i = 0; i < nRows; i++)
269 {
270 UINT32 id = DBGetFieldULong(hResult, i, 0);
271 UINT32 oldFlags = 0;
272 UINT32 oldRuntime = DBGetFieldULong(hResult, i, 1);
273 UINT32 flags = 0;
274 UINT32 state = 0;
275 UINT32 capabilities = 0;
276 TCHAR query[256];
277 _sntprintf(query, 256, _T("SELECT node_flags FROM nodes WHERE id=%d"), id);
278 DB_RESULT flagResult = DBSelect(g_hCoreDB, query);
279 if(DBGetNumRows(flagResult) >= 1)
280 {
281 oldFlags = DBGetFieldULong(flagResult, 0, 0);
282 }
283 else
284 {
285 if(!g_bIgnoreErrors)
286 {
287 DBFreeStatement(stmtNetObj);
288 DBFreeStatement(stmtNode);
289 DBFreeResult(hResult);
290 return FALSE;
291 }
292 }
293 MoveNodeFlags(oldFlags, &flags);
294 MoveNodeCapabilities(oldFlags, &capabilities);
295 MoveNodeState(oldRuntime, &state);
296
297 DBBind(stmtNetObj, 1, DB_SQLTYPE_INTEGER, flags);
298 DBBind(stmtNetObj, 2, DB_SQLTYPE_INTEGER, state);
299 DBBind(stmtNetObj, 3, DB_SQLTYPE_INTEGER, id);
300
301 DBBind(stmtNode, 1, DB_SQLTYPE_INTEGER, capabilities);
302 DBBind(stmtNode, 2, DB_SQLTYPE_INTEGER, id);
303
304 if (!(SQLExecute(stmtNetObj)))
305 {
306 if (!g_bIgnoreErrors)
307 {
308 DBFreeStatement(stmtNetObj);
309 DBFreeStatement(stmtNode);
310 DBFreeResult(hResult);
311 return FALSE;
312 }
313 }
314
315 if (!SQLExecute(stmtNode))
316 {
317 if (!g_bIgnoreErrors)
318 {
319 DBFreeStatement(stmtNetObj);
320 DBFreeStatement(stmtNode);
321 DBFreeResult(hResult);
322 return FALSE;
323 }
324 }
325 }
326 DBFreeStatement(stmtNetObj);
327 DBFreeStatement(stmtNode);
328 }
329 else
330 {
331 if(stmtNetObj != NULL)
332 DBFreeStatement(stmtNetObj);
333
334 if(stmtNode != NULL)
335 DBFreeStatement(stmtNode);
336 if (!g_bIgnoreErrors)
337 {
338 return FALSE;
339 }
340 }
341 DBFreeResult(hResult);
342 }
343 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("nodes"), _T("runtime_flags")));
344 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("nodes"), _T("node_flags")));
345
346 //sensor
347 hResult = DBSelect(g_hCoreDB, _T("SELECT id,runtime_flags,flags FROM sensors"));
348 DB_STATEMENT stmt = DBPrepare(g_hCoreDB, _T("UPDATE object_properties SET status=? WHERE object_id=?"));
349 if (hResult != NULL)
350 {
351 if (stmt != NULL)
352 {
353 int nRows = DBGetNumRows(hResult);
354 for(int i = 0; i < nRows; i++)
355 {
356 UINT32 status = 0;
357 MoveSensorState(DBGetFieldULong(hResult, i, 2), DBGetFieldULong(hResult, i, 1), &status);
358
359 DBBind(stmt, 1, DB_SQLTYPE_INTEGER, status);
360 DBBind(stmt, 2, DB_SQLTYPE_INTEGER, DBGetFieldULong(hResult, i, 0));
361
362 if (!(SQLExecute(stmt)))
363 {
364 if (!g_bIgnoreErrors)
365 {
366 DBFreeStatement(stmt);
367 DBFreeResult(hResult);
368 return FALSE;
369 }
370 }
371 }
372 DBFreeStatement(stmt);
373 }
374 else if (!g_bIgnoreErrors)
375 {
376 return FALSE;
377 }
378 DBFreeResult(hResult);
379 }
380 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("sensors"), _T("runtime_flags")));
381 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("sensors"), _T("flags")));
382
383 CHK_EXEC(SetMinorSchemaVersion(4));
384 return true;
385 }
386
387 /**
388 * Upgrade from 30.2 to 30.3
389 */
390 static bool H_UpgradeFromV2()
391 {
392 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("event_groups"), _T("range_start")));
393 CHK_EXEC(DBDropColumn(g_hCoreDB, _T("event_groups"), _T("range_end")));
394
395 static const TCHAR *batch =
396 _T("ALTER TABLE event_groups ADD guid varchar(36) null\n")
397 _T("UPDATE event_groups SET guid='04b326c0-5cc0-411f-8587-2836cb87c920' WHERE id=-2147483647\n")
398 _T("UPDATE event_groups SET guid='b61859c6-1768-4a61-a0cf-eed07d688f66' WHERE id=-2147483646\n")
399 _T("<END>");
400 CHK_EXEC(SQLBatch(batch));
401 DBSetNotNullConstraint(g_hCoreDB, _T("event_groups"), _T("guid"));
402
403 CHK_EXEC(SetMinorSchemaVersion(3));
404 return true;
405 }
406
407 /**
408 * Upgrade from 30.1 to 30.2
409 */
410 static bool H_UpgradeFromV1()
411 {
412 static const TCHAR *batch =
413 _T("ALTER TABLE users ADD created integer null\n")
414 _T("ALTER TABLE user_groups ADD created integer null\n")
415 _T("UPDATE users SET created=0\n")
416 _T("UPDATE user_groups SET created=0\n")
417 _T("<END>");
418 CHK_EXEC(SQLBatch(batch));
419 CHK_EXEC(DBSetNotNullConstraint(g_hCoreDB, _T("users"), _T("created")));
420 CHK_EXEC(DBSetNotNullConstraint(g_hCoreDB, _T("user_groups"), _T("created")));
421
422 CHK_EXEC(SetMinorSchemaVersion(2));
423 return true;
424 }
425
426 /**
427 * Upgrade from 30.0 to 30.1
428 */
429 static bool H_UpgradeFromV0()
430 {
431 CHK_EXEC(CreateTable(
432 _T("CREATE TABLE sensors (")
433 _T(" id integer not null,")
434 _T(" proxy_node integer not null,")
435 _T(" flags integer not null,")
436 _T(" mac_address varchar(16) null,")
437 _T(" device_class integer not null,")
438 _T(" vendor varchar(128) null,")
439 _T(" communication_protocol integer not null,")
440 _T(" xml_config varchar(4000) null,")
441 _T(" xml_reg_config varchar(4000) null,")
442 _T(" serial_number varchar(256) null,")
443 _T(" device_address varchar(256) null,")
444 _T(" meta_type varchar(256) null,")
445 _T(" description varchar(512) null,")
446 _T(" last_connection_time integer not null,")
447 _T(" frame_count integer not null,")
448 _T(" signal_strenght integer not null,")
449 _T(" signal_noise integer not null,")
450 _T(" frequency integer not null,")
451 _T(" runtime_flags integer null,")
452 _T("PRIMARY KEY(id))")));
453
454 CHK_EXEC(SetMinorSchemaVersion(1));
455 return true;
456 }
457
458 /**
459 * Upgrade map
460 */
461 static struct
462 {
463 int version;
464 int nextMajor;
465 int nextMinor;
466 bool (* upgradeProc)();
467 } s_dbUpgradeMap[] =
468 {
469 { 6, 30, 7, H_UpgradeFromV6 },
470 { 5, 30, 6, H_UpgradeFromV5 },
471 { 4, 30, 5, H_UpgradeFromV4 },
472 { 3, 30, 4, H_UpgradeFromV3 },
473 { 2, 30, 3, H_UpgradeFromV2 },
474 { 1, 30, 2, H_UpgradeFromV1 },
475 { 0, 30, 1, H_UpgradeFromV0 },
476 { 0, 0, 0, NULL }
477 };
478
479 /**
480 * Upgrade database to new version
481 */
482 bool MajorSchemaUpgrade_V30()
483 {
484 INT32 major, minor;
485 if (!DBGetSchemaVersion(g_hCoreDB, &major, &minor))
486 return false;
487
488 while((major == 30) && (minor < DB_SCHEMA_VERSION_V30_MINOR))
489 {
490 // Find upgrade procedure
491 int i;
492 for(i = 0; s_dbUpgradeMap[i].upgradeProc != NULL; i++)
493 if (s_dbUpgradeMap[i].version == minor)
494 break;
495 if (s_dbUpgradeMap[i].upgradeProc == NULL)
496 {
497 _tprintf(_T("Unable to find upgrade procedure for version 30.%d\n"), minor);
498 return false;
499 }
500 _tprintf(_T("Upgrading from version 30.%d to %d.%d\n"), minor, s_dbUpgradeMap[i].nextMajor, s_dbUpgradeMap[i].nextMinor);
501 DBBegin(g_hCoreDB);
502 if (s_dbUpgradeMap[i].upgradeProc())
503 {
504 DBCommit(g_hCoreDB);
505 if (!DBGetSchemaVersion(g_hCoreDB, &major, &minor))
506 return false;
507 }
508 else
509 {
510 _tprintf(_T("Rolling back last stage due to upgrade errors...\n"));
511 DBRollback(g_hCoreDB);
512 return false;
513 }
514 }
515 return true;
516 }