git.netxms.org / public / netxms.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
implemented tdata tables conversion to new format
[public/netxms.git] / src / server / tools / nxdbmgr / check.cpp
CommitLineData
2589cc10 1/*
5039dede 2** nxdbmgr - NetXMS database manager
56c41b56 3** Copyright (C) 2004-2016 Victor Kirhenshtein
5039dede
AK		 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: check.cpp
20**
21**/
22
23#include "nxdbmgr.h"
24
25
26//
27// Static data
28//
29
30static int m_iNumErrors = 0;
31static int m_iNumFixes = 0;
32static int m_iStageErrors;
33static int m_iStageFixes;
34static TCHAR *m_pszStageMsg = NULL;
35
16a0cd88
VK		 36/**
37 * Start stage
38 */
5039dede
AK		 39static void StartStage(const TCHAR *pszMsg)
40{
41 if (pszMsg != NULL)
42 {
43 safe_free(m_pszStageMsg);
44 m_pszStageMsg = _tcsdup(pszMsg);
45 }
84ee0f9c 46 WriteToTerminalEx(_T("\x1b[1m*\x1b[0m %-67s"), m_pszStageMsg);
f669df41 47#ifndef _WIN32
5039dede
AK		 48 fflush(stdout);
49#endif
50 m_iStageErrors = m_iNumErrors;
51 m_iStageFixes = m_iNumFixes;
52}
53
16a0cd88
VK		 54/**
55 * End stage
56 */
f669df41 57static void EndStage()
5039dede
AK		 58{
59 static const TCHAR *pszStatus[] = { _T("PASSED"), _T("FIXED "), _T("ERROR ") };
f669df41 60 static int nColor[] = { 32, 33, 31 };
5039dede
AK		 61 int nCode, nErrors;
62
63 nErrors = m_iNumErrors - m_iStageErrors;
64 if (nErrors > 0)
65 {
66 nCode = (m_iNumFixes - m_iStageFixes == nErrors) ? 1 : 2;
67 StartStage(NULL); // redisplay stage message
68 }
69 else
70 {
71 nCode = 0;
72 }
2ac6ce86 73 WriteToTerminalEx(_T(" \x1b[37;1m[\x1b[%d;1m%s\x1b[37;1m]\x1b[0m\n"), nColor[nCode], pszStatus[nCode]);
5039dede
AK		 74}
75
16a0cd88
VK		 76/**
77 * Get object name from object_properties table
78 */
5039dede
AK		 79static TCHAR *GetObjectName(DWORD dwId, TCHAR *pszBuffer)
80{
81 TCHAR szQuery[256];
82 DB_RESULT hResult;
83
08b214c6 84 _sntprintf(szQuery, 256, _T("SELECT name FROM object_properties WHERE object_id=%d"), dwId);
5039dede
AK		 85 hResult = SQLSelect(szQuery);
86 if (hResult != NULL)
87 {
88 if (DBGetNumRows(hResult) > 0)
89 {
90 DBGetField(hResult, 0, 0, pszBuffer, MAX_OBJECT_NAME);
91 }
92 else
93 {
94 _tcscpy(pszBuffer, _T("<unknown>"));
95 }
96 }
97 else
98 {
99 _tcscpy(pszBuffer, _T("<unknown>"));
100 }
101 return pszBuffer;
102}
103
305c5d65
VK		 104/**
105 * Check that given node is inside at least one container or cluster
106 */
a6c1f07f 107static bool NodeInContainer(DWORD id)
4262c0dc
VK		 108{
109 TCHAR query[256];
110 DB_RESULT hResult;
a6c1f07f 111 bool result = false;
4262c0dc
VK		 112
113 _sntprintf(query, 256, _T("SELECT container_id FROM container_members WHERE object_id=%d"), id);
114 hResult = SQLSelect(query);
115 if (hResult != NULL)
116 {
117 result = (DBGetNumRows(hResult) > 0);
118 DBFreeResult(hResult);
119 }
a6c1f07f
VK		 120
121 if (!result)
122 {
123 _sntprintf(query, 256, _T("SELECT cluster_id FROM cluster_members WHERE node_id=%d"), id);
124 hResult = SQLSelect(query);
125 if (hResult != NULL)
126 {
127 result = (DBGetNumRows(hResult) > 0);
128 DBFreeResult(hResult);
129 }
130 }
131
4262c0dc
VK		 132 return result;
133}
134
305c5d65
VK		 135/**
136 * Find subnet for unlinked node
137 */
4262c0dc
VK		 138static BOOL FindSubnetForNode(DWORD id, const TCHAR *name)
139{
140 DB_RESULT hResult, hResult2;
141 TCHAR query[256], buffer[32];
142 int i, count;
4262c0dc
VK		 143 BOOL success = FALSE;
144
145 // Read list of interfaces of given node
c30c0c0f 146 _sntprintf(query, 256, _T("SELECT l.ip_addr,l.ip_netmask FROM interfaces i INNER JOIN interface_address_list l ON l.iface_id = i.id WHERE node_id=%d"), id);
4262c0dc
VK		 147 hResult = SQLSelect(query);
148 if (hResult != NULL)
149 {
150 count = DBGetNumRows(hResult);
151 for(i = 0; i < count; i++)
152 {
c30c0c0f
VK		 153 InetAddress addr = DBGetFieldInetAddr(hResult, i, 0);
154 addr.setMaskBits(DBGetFieldLong(hResult, i, 1));
155 InetAddress subnet = addr.getSubnetAddress();
2589cc10 156
c30c0c0f 157 _sntprintf(query, 256, _T("SELECT id FROM subnets WHERE ip_addr='%s'"), subnet.toString(buffer));
4262c0dc
VK		 158 hResult2 = SQLSelect(query);
159 if (hResult2 != NULL)
160 {
161 if (DBGetNumRows(hResult2) > 0)
162 {
c30c0c0f 163 UINT32 subnetId = DBGetFieldULong(hResult2, 0, 0);
4262c0dc 164 m_iNumErrors++;
c30c0c0f 165 if (GetYesNo(_T("\rUnlinked node object %d (\"%s\") can be linked to subnet %d (%s). Link?"), id, name, subnetId, buffer))
4262c0dc 166 {
c30c0c0f 167 _sntprintf(query, 256, _T("INSERT INTO nsmap (subnet_id,node_id) VALUES (%d,%d)"), subnetId, id);
4262c0dc
VK		 168 if (SQLQuery(query))
169 {
170 success = TRUE;
171 m_iNumFixes++;
172 break;
173 }
174 else
175 {
176 // Node remains unlinked, so error count will be
177 // incremented again by node deletion code or next iteration
178 m_iNumErrors--;
179 }
180 }
181 else
182 {
183 // Node remains unlinked, so error count will be
184 // incremented again by node deletion code
185 m_iNumErrors--;
186 }
187 }
188 DBFreeResult(hResult2);
189 }
190 }
191 DBFreeResult(hResult);
192 }
193 return success;
194}
195
7777b139
VK		 196/**
197 * Check zone objects
198 */
0d9b58ef
VK		 199static void CheckZones()
200{
201 DB_RESULT hResult, hResult2;
202 DWORD i, dwNumObjects, dwId;
7c2b8394 203 TCHAR szQuery[1024];
0d9b58ef
VK		 204
205 StartStage(_T("Checking zone objects..."));
206 hResult = SQLSelect(_T("SELECT id FROM zones"));
207 if (hResult != NULL)
208 {
209 dwNumObjects = DBGetNumRows(hResult);
210 for(i = 0; i < dwNumObjects; i++)
211 {
212 dwId = DBGetFieldULong(hResult, i, 0);
213
214 // Check appropriate record in object_properties table
215 _sntprintf(szQuery, 256, _T("SELECT name,is_deleted FROM object_properties WHERE object_id=%d"), (int)dwId);
216 hResult2 = SQLSelect(szQuery);
217 if (hResult2 != NULL)
218 {
219 if ((DBGetNumRows(hResult2) == 0) && (dwId != 4)) // Properties for built-in zone can be missing
220 {
221 m_iNumErrors++;
222 if (GetYesNo(_T("\rMissing zone object %d properties. Create?"), dwId))
223 {
224 uuid_t guid;
225 TCHAR guidText[128];
226
999945fa 227 _uuid_generate(guid);
2589cc10 228 _sntprintf(szQuery, 1024,
0d9b58ef
VK		 229 _T("INSERT INTO object_properties (object_id,guid,name,")
230 _T("status,is_deleted,is_system,inherit_access_rights,")
231 _T("last_modified,status_calc_alg,status_prop_alg,")
232 _T("status_fixed_val,status_shift,status_translation,")
233 _T("status_single_threshold,status_thresholds,location_type,")
56c41b56 234 _T("latitude,longitude,image,maint_mode,maint_event_id) VALUES ")
0d9b58ef 235 _T("(%d,'%s','lost_zone_%d',5,0,0,1,") TIME_T_FMT _T(",0,0,0,0,0,0,'00000000',0,")
56c41b56 236 _T("'0.000000','0.000000','00000000-0000-0000-0000-000000000000','0',0)"),
999945fa 237 (int)dwId, _uuid_to_string(guid, guidText), (int)dwId, TIME_T_FCAST(time(NULL)));
0d9b58ef
VK		 238 if (SQLQuery(szQuery))
239 m_iNumFixes++;
240 }
241 }
0d9b58ef
VK		 242 DBFreeResult(hResult2);
243 }
244 }
245 DBFreeResult(hResult);
246 }
247 EndStage();
248}
249
305c5d65
VK		 250/**
251 * Check node objects
252 */
035a4d73 253static void CheckNodes()
5039dede
AK		 254{
255 DB_RESULT hResult, hResult2;
256 DWORD i, dwNumObjects, dwId;
257 TCHAR szQuery[1024], szName[MAX_OBJECT_NAME];
7833a69f 258 BOOL bResult, bIsDeleted = FALSE;
5039dede
AK		 259
260 StartStage(_T("Checking node objects..."));
261 hResult = SQLSelect(_T("SELECT id,primary_ip FROM nodes"));
262 if (hResult != NULL)
263 {
264 dwNumObjects = DBGetNumRows(hResult);
265 for(i = 0; i < dwNumObjects; i++)
266 {
267 dwId = DBGetFieldULong(hResult, i, 0);
268
269 // Check appropriate record in object_properties table
270 _sntprintf(szQuery, 256, _T("SELECT name,is_deleted FROM object_properties WHERE object_id=%d"), dwId);
271 hResult2 = SQLSelect(szQuery);
272 if (hResult2 != NULL)
273 {
274 if (DBGetNumRows(hResult2) == 0)
275 {
276 m_iNumErrors++;
a4743a0f 277 if (GetYesNo(_T("\rMissing node object %d properties. Create?"), dwId))
5039dede 278 {
478d4ff4 279 uuid_t guid;
0d9b58ef 280 TCHAR guidText[128];
478d4ff4 281
999945fa 282 _uuid_generate(guid);
2589cc10 283 _sntprintf(szQuery, 1024,
478d4ff4
VK		 284 _T("INSERT INTO object_properties (object_id,guid,name,")
285 _T("status,is_deleted,is_system,inherit_access_rights,")
08b214c6
VK		 286 _T("last_modified,status_calc_alg,status_prop_alg,")
287 _T("status_fixed_val,status_shift,status_translation,")
478d4ff4 288 _T("status_single_threshold,status_thresholds,location_type,")
56c41b56
VK		 289 _T("latitude,longitude,location_accuracy,location_timestamp,")
290 _T("image,submap_id,maint_mode,maint_event_id) VALUES ")
0d9b58ef 291 _T("(%d,'%s','lost_node_%d',5,0,0,1,") TIME_T_FMT _T(",0,0,0,0,0,0,'00000000',0,")
56c41b56 292 _T("'0.000000','0.000000',0,0,'00000000-0000-0000-0000-000000000000',0,'0',0)"),
999945fa 293 (int)dwId, _uuid_to_string(guid, guidText), (int)dwId, TIME_T_FCAST(time(NULL)));
5039dede
AK		 294 if (SQLQuery(szQuery))
295 m_iNumFixes++;
296 }
297 }
298 else
299 {
300 DBGetField(hResult2, 0, 0, szName, MAX_OBJECT_NAME);
301 bIsDeleted = DBGetFieldLong(hResult2, 0, 1) ? TRUE : FALSE;
302 }
303 DBFreeResult(hResult2);
304 }
305
306 if (!bIsDeleted)
307 {
308 _sntprintf(szQuery, 1024, _T("SELECT subnet_id FROM nsmap WHERE node_id=%d"), dwId);
309 hResult2 = SQLSelect(szQuery);
310 if (hResult2 != NULL)
311 {
4262c0dc 312 if ((DBGetNumRows(hResult2) == 0) && (!NodeInContainer(dwId)))
5039dede 313 {
4262c0dc
VK		 314 if ((DBGetFieldIPAddr(hResult, i, 1) == 0) || (!FindSubnetForNode(dwId, szName)))
315 {
316 m_iNumErrors++;
5e5461a9 317 if (GetYesNo(_T("\rUnlinked node object %d (\"%s\"). Delete it?"), dwId, szName))
4262c0dc
VK		 318 {
319 _sntprintf(szQuery, 1024, _T("DELETE FROM nodes WHERE id=%d"), dwId);
320 bResult = SQLQuery(szQuery);
321 _sntprintf(szQuery, 1024, _T("DELETE FROM acl WHERE object_id=%d"), dwId);
322 bResult = bResult && SQLQuery(szQuery);
323 _sntprintf(szQuery, 1024, _T("DELETE FROM object_properties WHERE object_id=%d"), dwId);
324 if (SQLQuery(szQuery) && bResult)
325 m_iNumFixes++;
326 }
327 }
5039dede
AK		 328 }
329 DBFreeResult(hResult2);
330 }
331 }
332 }
333 DBFreeResult(hResult);
334 }
335 EndStage();
336}
337
305c5d65
VK		 338/**
339 * Check node component objects
340 */
5039dede
AK		 341static void CheckComponents(const TCHAR *pszDisplayName, const TCHAR *pszTable)
342{
343 DB_RESULT hResult, hResult2;
344 DWORD i, dwNumObjects, dwId;
345 TCHAR szQuery[1024], szName[MAX_OBJECT_NAME];
5039dede 346
08b214c6 347 _sntprintf(szQuery, 1024, _T("Checking %s objects..."), pszDisplayName);
5039dede
AK		 348 StartStage(szQuery);
349
08b214c6 350 _sntprintf(szQuery, 1024, _T("SELECT id,node_id FROM %s"), pszTable);
5039dede
AK		 351 hResult = SQLSelect(szQuery);
352 if (hResult != NULL)
353 {
354 dwNumObjects = DBGetNumRows(hResult);
355 for(i = 0; i < dwNumObjects; i++)
356 {
357 dwId = DBGetFieldULong(hResult, i, 0);
358
359 // Check appropriate record in object_properties table
08b214c6 360 _sntprintf(szQuery, 1024, _T("SELECT name,is_deleted FROM object_properties WHERE object_id=%d"), dwId);
5039dede
AK		 361 hResult2 = SQLSelect(szQuery);
362 if (hResult2 != NULL)
363 {
364 if (DBGetNumRows(hResult2) == 0)
365 {
366 m_iNumErrors++;
a4743a0f 367 if (GetYesNo(_T("\rMissing %s object %d properties. Create?"), pszDisplayName, dwId))
5039dede 368 {
478d4ff4 369 uuid_t guid;
0d9b58ef 370 TCHAR guidText[128];
478d4ff4 371
999945fa 372 _uuid_generate(guid);
2589cc10 373 _sntprintf(szQuery, 1024,
478d4ff4
VK		 374 _T("INSERT INTO object_properties (object_id,guid,name,")
375 _T("status,is_deleted,is_system,inherit_access_rights,")
08b214c6
VK		 376 _T("last_modified,status_calc_alg,status_prop_alg,")
377 _T("status_fixed_val,status_shift,status_translation,")
478d4ff4 378 _T("status_single_threshold,status_thresholds,location_type,")
56c41b56 379 _T("latitude,longitude,image,maint_mode,maint_event_id) VALUES ")
0d9b58ef 380 _T("(%d,'%s','lost_%s_%d',5,0,0,1,") TIME_T_FMT _T(",0,0,0,0,0,0,'00000000',0,")
56c41b56 381 _T("'0.000000','0.000000','00000000-0000-0000-0000-000000000000','0',0)"),
999945fa 382 (int)dwId, _uuid_to_string(guid, guidText), pszDisplayName, (int)dwId, TIME_T_FCAST(time(NULL)));
5039dede
AK		 383 if (SQLQuery(szQuery))
384 m_iNumFixes++;
385 szName[0] = 0;
386 }
387 }
388 else
389 {
390 DBGetField(hResult2, 0, 0, szName, MAX_OBJECT_NAME);
5039dede
AK		 391 }
392 DBFreeResult(hResult2);
393 }
394 else
395 {
396 szName[0] = 0;
397 }
398
399 // Check if referred node exists
400 _sntprintf(szQuery, 256, _T("SELECT name FROM object_properties WHERE object_id=%d AND is_deleted=0"),
401 DBGetFieldULong(hResult, i, 1));
402 hResult2 = SQLSelect(szQuery);
403 if (hResult2 != NULL)
404 {
405 if (DBGetNumRows(hResult2) == 0)
406 {
407 m_iNumErrors++;
408 dwId = DBGetFieldULong(hResult, i, 0);
5e5461a9 409 if (GetYesNo(_T("\rUnlinked %s object %d (\"%s\"). Delete it?"), pszDisplayName, dwId, szName))
5039dede
AK		 410 {
411 _sntprintf(szQuery, 256, _T("DELETE FROM %s WHERE id=%d"), pszTable, dwId);
412 if (SQLQuery(szQuery))
413 {
414 _sntprintf(szQuery, 256, _T("DELETE FROM object_properties WHERE object_id=%d"), dwId);
415 SQLQuery(szQuery);
416 m_iNumFixes++;
417 }
418 }
419 }
420 DBFreeResult(hResult2);
421 }
422 }
423 DBFreeResult(hResult);
424 }
425 EndStage();
426}
427
305c5d65
VK		 428/**
429 * Check common object properties
430 */
0d9b58ef 431static void CheckObjectProperties()
5039dede
AK		 432{
433 DB_RESULT hResult;
434 TCHAR szQuery[1024];
435 DWORD i, dwNumRows, dwObjectId;
436
437 StartStage(_T("Checking object properties..."));
438 hResult = SQLSelect(_T("SELECT object_id,name,last_modified FROM object_properties"));
439 if (hResult != NULL)
440 {
441 dwNumRows = DBGetNumRows(hResult);
442 for(i = 0; i < dwNumRows; i++)
443 {
444 dwObjectId = DBGetFieldULong(hResult, i, 0);
445
446 // Check last change time
447 if (DBGetFieldULong(hResult, i, 2) == 0)
448 {
449 m_iNumErrors++;
5e5461a9 450 if (GetYesNo(_T("\rObject %d [%s] has invalid timestamp. Fix it?"),
a4743a0f 451 dwObjectId, DBGetField(hResult, i, 1, szQuery, 1024)))
5039dede 452 {
0d9b58ef
VK		 453 _sntprintf(szQuery, 1024, _T("UPDATE object_properties SET last_modified=") TIME_T_FMT _T(" WHERE object_id=%d"),
454 TIME_T_FCAST(time(NULL)), (int)dwObjectId);
5039dede
AK		 455 if (SQLQuery(szQuery))
456 m_iNumFixes++;
457 }
458 }
459 }
460 DBFreeResult(hResult);
461 }
462 EndStage();
463}
464
305c5d65
VK		 465/**
466 * Check cluster objects
467 */
0d9b58ef 468static void CheckClusters()
5039dede
AK		 469{
470 DB_RESULT hResult;
471 TCHAR szQuery[256], szName[MAX_OBJECT_NAME];
472 DWORD i, dwNumRows, dwObjectId, dwId;
473
474 StartStage(_T("Checking cluster objects..."));
475 hResult = SQLSelect(_T("SELECT cluster_id,node_id FROM cluster_members"));
476 if (hResult != NULL)
477 {
478 dwNumRows = DBGetNumRows(hResult);
479 for(i = 0; i < dwNumRows; i++)
480 {
481 dwObjectId = DBGetFieldULong(hResult, i, 1);
06b83321 482 if (!IsDatabaseRecordExist(_T("nodes"), _T("id"), dwObjectId))
5039dede
AK		 483 {
484 m_iNumErrors++;
485 dwId = DBGetFieldULong(hResult, i, 0);
a4743a0f
VK		 486 if (GetYesNo(_T("\rCluster object %s [%d] refers to non-existing node %d. Dereference?"),
487 GetObjectName(dwId, szName), dwId, dwObjectId))
5039dede
AK		 488 {
489 _sntprintf(szQuery, 256, _T("DELETE FROM cluster_members WHERE cluster_id=%d AND node_id=%d"),dwId, dwObjectId);
490 if (SQLQuery(szQuery))
491 {
492 m_iNumFixes++;
493 }
494 }
495 }
496 }
497 DBFreeResult(hResult);
498 }
499 EndStage();
500}
501
305c5d65
VK		 502/**
503 * Returns TRUE if SELECT returns non-empty set
504 */
5039dede
AK		 505static BOOL CheckResultSet(TCHAR *pszQuery)
506{
507 DB_RESULT hResult;
508 BOOL bResult = FALSE;
509
510 hResult = SQLSelect(pszQuery);
511 if (hResult != NULL)
512 {
513 bResult = (DBGetNumRows(hResult) > 0);
514 DBFreeResult(hResult);
515 }
516 return bResult;
517}
518
305c5d65
VK		 519/**
520 * Check event processing policy
521 */
0d9b58ef 522static void CheckEPP()
5039dede
AK		 523{
524 DB_RESULT hResult;
525 TCHAR szQuery[1024];
526 int i, iNumRows;
527 DWORD dwId;
528
529 StartStage(_T("Checking event processing policy..."));
2589cc10 530
5039dede
AK		 531 // Check source object ID's
532 hResult = SQLSelect(_T("SELECT object_id FROM policy_source_list"));
533 if (hResult != NULL)
534 {
535 iNumRows = DBGetNumRows(hResult);
536 for(i = 0; i < iNumRows; i++)
537 {
538 dwId = DBGetFieldULong(hResult, i, 0);
08b214c6 539 _sntprintf(szQuery, 1024, _T("SELECT object_id FROM object_properties WHERE object_id=%d"), dwId);
5039dede
AK		 540 if (!CheckResultSet(szQuery))
541 {
542 m_iNumErrors++;
5e5461a9 543 if (GetYesNo(_T("\rInvalid object ID %d used in policy. Delete it from policy?"), dwId))
5039dede 544 {
08b214c6 545 _sntprintf(szQuery, 1024, _T("DELETE FROM policy_source_list WHERE object_id=%d"), dwId);
5039dede
AK		 546 if (SQLQuery(szQuery))
547 m_iNumFixes++;
548 }
549 }
550 }
551 DBFreeResult(hResult);
552 }
553
554 // Check event ID's
555 hResult = SQLSelect(_T("SELECT event_code FROM policy_event_list"));
556 if (hResult != NULL)
557 {
558 iNumRows = DBGetNumRows(hResult);
559 for(i = 0; i < iNumRows; i++)
560 {
561 dwId = DBGetFieldULong(hResult, i, 0);
562 if (dwId & GROUP_FLAG)
08b214c6 563 _sntprintf(szQuery, 1024, _T("SELECT id FROM event_groups WHERE id=%d"), dwId);
5039dede 564 else
08b214c6 565 _sntprintf(szQuery, 1024, _T("SELECT event_code FROM event_cfg WHERE event_code=%d"), dwId);
5039dede
AK		 566 if (!CheckResultSet(szQuery))
567 {
568 m_iNumErrors++;
5e5461a9 569 if (GetYesNo(_T("\rInvalid event%s ID 0x%08X referenced in policy. Delete this reference?"), (dwId & GROUP_FLAG) ? _T(" group") : _T(""), dwId))
5039dede 570 {
08b214c6 571 _sntprintf(szQuery, 1024, _T("DELETE FROM policy_event_list WHERE event_code=%d"), dwId);
5039dede
AK		 572 if (SQLQuery(szQuery))
573 m_iNumFixes++;
574 }
575 }
576 }
577 DBFreeResult(hResult);
578 }
579
580 // Check action ID's
581 hResult = SQLSelect(_T("SELECT action_id FROM policy_action_list"));
582 if (hResult != NULL)
583 {
584 iNumRows = DBGetNumRows(hResult);
585 for(i = 0; i < iNumRows; i++)
586 {
587 dwId = DBGetFieldULong(hResult, i, 0);
08b214c6 588 _sntprintf(szQuery, 1024, _T("SELECT action_id FROM actions WHERE action_id=%d"), dwId);
5039dede
AK		 589 if (!CheckResultSet(szQuery))
590 {
591 m_iNumErrors++;
5e5461a9 592 if (GetYesNo(_T("\rInvalid action ID %d referenced in policy. Delete this reference?"), dwId))
5039dede 593 {
08b214c6 594 _sntprintf(szQuery, 1024, _T("DELETE FROM policy_action_list WHERE action_id=%d"), dwId);
5039dede
AK		 595 if (SQLQuery(szQuery))
596 m_iNumFixes++;
597 }
598 }
599 }
600 DBFreeResult(hResult);
601 }
602
603 EndStage();
604}
605
85ae39bc
VK		 606/**
607 * Create idata_xx table
608 */
609BOOL CreateIDataTable(DWORD nodeId)
5039dede 610{
08b214c6 611 TCHAR szQuery[256], szQueryTemplate[256];
5039dede
AK		 612 DWORD i;
613
85ae39bc 614 MetaDataReadStr(_T("IDataTableCreationCommand"), szQueryTemplate, 255, _T(""));
08b214c6 615 _sntprintf(szQuery, 256, szQueryTemplate, nodeId);
5039dede
AK		 616 if (!SQLQuery(szQuery))
617 return FALSE;
618
619 for(i = 0; i < 10; i++)
620 {
08b214c6 621 _sntprintf(szQuery, 256, _T("IDataIndexCreationCommand_%d"), i);
85ae39bc 622 MetaDataReadStr(szQuery, szQueryTemplate, 255, _T(""));
5039dede
AK		 623 if (szQueryTemplate[0] != 0)
624 {
08b214c6 625 _sntprintf(szQuery, 256, szQueryTemplate, nodeId, nodeId);
5039dede
AK		 626 if (!SQLQuery(szQuery))
627 return FALSE;
628 }
629 }
630
631 return TRUE;
632}
633
85ae39bc 634/**
22aaa779 635 * Create tdata_xx table - pre V281 version
85ae39bc 636 */
22aaa779 637BOOL CreateTDataTable_preV281(DWORD nodeId)
85ae39bc
VK		 638{
639 TCHAR szQuery[256], szQueryTemplate[256];
640 DWORD i;
5039dede 641
85ae39bc
VK		 642 MetaDataReadStr(_T("TDataTableCreationCommand"), szQueryTemplate, 255, _T(""));
643 _sntprintf(szQuery, 256, szQueryTemplate, nodeId);
644 if (!SQLQuery(szQuery))
645 return FALSE;
5039dede 646
85ae39bc
VK		 647 for(i = 0; i < 10; i++)
648 {
649 _sntprintf(szQuery, 256, _T("TDataIndexCreationCommand_%d"), i);
650 MetaDataReadStr(szQuery, szQueryTemplate, 255, _T(""));
651 if (szQueryTemplate[0] != 0)
652 {
653 _sntprintf(szQuery, 256, szQueryTemplate, nodeId, nodeId);
654 if (!SQLQuery(szQuery))
655 return FALSE;
656 }
657 }
658
659 return TRUE;
660}
661
22aaa779 662/**
63604cda 663 * Create tdata_xx table
22aaa779 664 */
63604cda 665BOOL CreateTDataTable(DWORD nodeId)
22aaa779
VK		 666{
667 TCHAR szQuery[256], szQueryTemplate[256];
668 DWORD i;
669
670 for(i = 0; i < 10; i++)
671 {
672 _sntprintf(szQuery, 256, _T("TDataTableCreationCommand_%d"), i);
673 MetaDataReadStr(szQuery, szQueryTemplate, 255, _T(""));
674 if (szQueryTemplate[0] != 0)
675 {
1d4f7890 676 _sntprintf(szQuery, 256, szQueryTemplate, nodeId, nodeId);
22aaa779
VK		 677 if (!SQLQuery(szQuery))
678 return FALSE;
679 }
680 }
681
682 for(i = 0; i < 10; i++)
683 {
684 _sntprintf(szQuery, 256, _T("TDataIndexCreationCommand_%d"), i);
685 MetaDataReadStr(szQuery, szQueryTemplate, 255, _T(""));
686 if (szQueryTemplate[0] != 0)
687 {
688 _sntprintf(szQuery, 256, szQueryTemplate, nodeId, nodeId);
689 if (!SQLQuery(szQuery))
690 return FALSE;
691 }
692 }
693
694 return TRUE;
695}
696
85ae39bc
VK		 697/**
698 * Check collected data
699 */
0d9b58ef 700static void CheckIData()
5039dede
AK		 701{
702 int i, nodeCount;
703 time_t now;
704 DWORD nodeId;
705 TCHAR query[1024];
706 DB_RESULT hResultNodes, hResult;
707
708 StartStage(_T("Checking collected data..."));
2589cc10 709
5039dede
AK		 710 now = time(NULL);
711 hResultNodes = SQLSelect(_T("SELECT id FROM nodes"));
712 if (hResultNodes != NULL)
713 {
714 nodeCount = DBGetNumRows(hResultNodes);
715 for(i = 0; i < nodeCount; i++)
716 {
717 nodeId = DBGetFieldULong(hResultNodes, i, 0);
376e15e8 718 _sntprintf(query, 1024, _T("SELECT count(*) FROM idata_%d WHERE idata_timestamp>") TIME_T_FMT, nodeId, TIME_T_FCAST(now));
5039dede
AK		 719 hResult = SQLSelect(query);
720 if (hResult != NULL)
721 {
722 if (DBGetFieldLong(hResult, 0, 0) > 0)
723 {
724 m_iNumErrors++;
5e5461a9 725 if (GetYesNo(_T("\rFound collected data for node [%d] with timestamp in the future. Delete them?"), nodeId))
5039dede 726 {
376e15e8 727 _sntprintf(query, 1024, _T("DELETE FROM idata_%d WHERE idata_timestamp>") TIME_T_FMT, nodeId, TIME_T_FCAST(now));
5039dede
AK		 728 if (SQLQuery(query))
729 m_iNumFixes++;
730 }
731 }
732 DBFreeResult(hResult);
733 }
5039dede
AK		 734 }
735 DBFreeResult(hResultNodes);
736 }
737
376e15e8 738 _sntprintf(query, 1024, _T("SELECT count(*) FROM raw_dci_values WHERE last_poll_time>") TIME_T_FMT, TIME_T_FCAST(now));
5039dede
AK		 739 hResult = SQLSelect(query);
740 if (hResult != NULL)
741 {
742 if (DBGetFieldLong(hResult, 0, 0) > 0)
743 {
744 m_iNumErrors++;
5e5461a9 745 if (GetYesNo(_T("\rFound DCIs with last poll timestamp in the future. Fix it?")))
5039dede 746 {
376e15e8 747 _sntprintf(query, 1024, _T("UPDATE raw_dci_values SET last_poll_time=") TIME_T_FMT _T(" WHERE last_poll_time>") TIME_T_FMT, TIME_T_FCAST(now), TIME_T_FCAST(now));
5039dede
AK		 748 if (SQLQuery(query))
749 m_iNumFixes++;
750 }
751 }
752 DBFreeResult(hResult);
753 }
754
755 EndStage();
756}
757
daf3c104
VK		 758/**
759 * Check if given data table exist
760 */
63604cda 761BOOL IsDataTableExist(const TCHAR *format, DWORD id)
daf3c104
VK		 762{
763 TCHAR table[256];
764 _sntprintf(table, 256, format, id);
fdf1ed50
VK		 765 int rc = DBIsTableExist(g_hCoreDB, table);
766 if (rc == DBIsTableExist_Failure)
767 {
768 _tprintf(_T("WARNING: call to DBIsTableExist(\"%s\") failed\n"), table);
769 }
770 return rc != DBIsTableExist_NotFound;
daf3c104
VK		 771}
772
773/**
774 * Check data tables for given o bject class
775 */
776static void CheckDataTablesForClass(const TCHAR *className, const TCHAR *classDescr)
777{
778 TCHAR query[1024];
779 _sntprintf(query, 256, _T("SELECT id FROM %s"), className);
780 DB_RESULT hResult = SQLSelect(query);
781 if (hResult != NULL)
782 {
783 int count = DBGetNumRows(hResult);
784 for(int i = 0; i < count; i++)
785 {
786 DWORD id = DBGetFieldULong(hResult, i, 0);
787
788 // IDATA
789 if (!IsDataTableExist(_T("idata_%d"), id))
790 {
791 m_iNumErrors++;
792 if (GetYesNo(_T("\rData collection table (IDATA) for %s [%d] not found. Create? (Y/N) "), classDescr, id))
793 {
794 if (CreateIDataTable(id))
795 m_iNumFixes++;
796 }
797 }
798
799 // TDATA
63604cda 800 if (IsDataTableExist(_T("tdata_%d"), id))
daf3c104
VK		 801 {
802 m_iNumErrors++;
803 if (GetYesNo(_T("\rData collection table (TDATA) for %s [%d] not found. Create? (Y/N) "), classDescr, id))
804 {
63604cda 805 if (CreateTDataTable(id))
daf3c104
VK		 806 m_iNumFixes++;
807 }
808 }
809 }
810 DBFreeResult(hResult);
811 }
812}
813
814/**
815 * Check data tables
816 */
817static void CheckDataTables()
818{
819 StartStage(_T("Checking data tables..."));
820
821 CheckDataTablesForClass(_T("nodes"), _T("node"));
822 CheckDataTablesForClass(_T("clusters"), _T("cluster"));
823 CheckDataTablesForClass(_T("mobile_devices"), _T("mobile device"));
6cedcfc3 824 CheckDataTablesForClass(_T("access_points"), _T("access point"));
63604cda 825 CheckDataTablesForClass(_T("chassis"), _T("chassis"));
daf3c104
VK		 826
827 EndStage();
828}
829
85ae39bc
VK		 830/**
831 * Check template to node mapping
832 */
0d9b58ef 833static void CheckTemplateNodeMapping()
5039dede
AK		 834{
835 DB_RESULT hResult;
836 TCHAR name[256], query[256];
837 DWORD i, dwNumRows, dwTemplateId, dwNodeId;
838
839 StartStage(_T("Checking template to node mapping..."));
840 hResult = SQLSelect(_T("SELECT template_id,node_id FROM dct_node_map ORDER BY template_id"));
841 if (hResult != NULL)
842 {
843 dwNumRows = DBGetNumRows(hResult);
844 for(i = 0; i < dwNumRows; i++)
845 {
846 dwTemplateId = DBGetFieldULong(hResult, i, 0);
847 dwNodeId = DBGetFieldULong(hResult, i, 1);
848
849 // Check node existence
06b83321
VK		 850 if (!IsDatabaseRecordExist(_T("nodes"), _T("id"), dwNodeId) &&
851 !IsDatabaseRecordExist(_T("clusters"), _T("id"), dwNodeId) &&
852 !IsDatabaseRecordExist(_T("mobile_devices"), _T("id"), dwNodeId))
5039dede
AK		 853 {
854 m_iNumErrors++;
855 GetObjectName(dwTemplateId, name);
5e5461a9 856 if (GetYesNo(_T("\rTemplate %d [%s] mapped to non-existent node %d. Delete this mapping?"), dwTemplateId, name, dwNodeId))
5039dede
AK		 857 {
858 _sntprintf(query, 256, _T("DELETE FROM dct_node_map WHERE template_id=%d AND node_id=%d"),
859 dwTemplateId, dwNodeId);
860 if (SQLQuery(query))
861 m_iNumFixes++;
862 }
863 }
864 }
865 DBFreeResult(hResult);
866 }
867 EndStage();
868}
869
16a0cd88
VK		 870/**
871 * Check network map links
872 */
873static void CheckMapLinks()
874{
875 StartStage(_T("Checking network map links..."));
876
877 for(int pass = 1; pass <= 2; pass++)
878 {
879 TCHAR query[1024];
880 _sntprintf(query, 1024,
881 _T("SELECT network_map_links.map_id,network_map_links.element1,network_map_links.element2 ")
882 _T("FROM network_map_links ")
883 _T("LEFT OUTER JOIN network_map_elements ON ")
884 _T(" network_map_links.map_id = network_map_elements.map_id AND ")
885 _T(" network_map_links.element%d = network_map_elements.element_id ")
886 _T("WHERE network_map_elements.element_id IS NULL"), pass);
887
888 DB_RESULT hResult = SQLSelect(query);
889 if (hResult != NULL)
890 {
891 int count = DBGetNumRows(hResult);
892 for(int i = 0; i < count; i++)
893 {
894 m_iNumErrors++;
895 DWORD mapId = DBGetFieldULong(hResult, i, 0);
896 TCHAR name[MAX_OBJECT_NAME];
897 GetObjectName(mapId, name);
898 if (GetYesNo(_T("\rInvalid link on network map %s [%d]. Delete?"), name, mapId))
899 {
900 _sntprintf(query, 256, _T("DELETE FROM network_map_links WHERE map_id=%d AND element1=%d AND element2=%d"),
901 mapId, DBGetFieldLong(hResult, i, 1), DBGetFieldLong(hResult, i, 2));
902 if (SQLQuery(query))
903 m_iNumFixes++;
904 }
905 }
906 DBFreeResult(hResult);
907 }
908 }
909 EndStage();
910}
911
96e87c92
VK		 912/**
913 * Check database for errors
914 */
0d9b58ef 915void CheckDatabase()
5039dede
AK		 916{
917 DB_RESULT hResult;
918 LONG iVersion = 0;
919 BOOL bCompleted = FALSE;
920
daf3c104
VK		 921 if (g_checkDataTablesOnly)
922 _tprintf(_T("Checking database (data tables only):\n"));
923 else
924 _tprintf(_T("Checking database (%s collected data):\n"), g_checkData ? _T("including") : _T("excluding"));
5039dede
AK		 925
926 // Get database format version
28f5b9a4 927 iVersion = DBGetSchemaVersion(g_hCoreDB);
daf3c104 928 if ((iVersion < DB_FORMAT_VERSION) && !g_checkDataTablesOnly)
5039dede
AK		 929 {
930 _tprintf(_T("Your database has format version %d, this tool is compiled for version %d.\nUse \"upgrade\" command to upgrade your database first.\n"),
931 iVersion, DB_FORMAT_VERSION);
932 }
933 else if (iVersion > DB_FORMAT_VERSION)
934 {
08b214c6
VK		 935 _tprintf(_T("Your database has format version %d, this tool is compiled for version %d.\n")
936 _T("You need to upgrade your server before using this database.\n"),
5039dede
AK		 937 iVersion, DB_FORMAT_VERSION);
938
939 }
940 else
941 {
bf7f9d94 942 TCHAR szLockStatus[MAX_DB_STRING], szLockInfo[MAX_DB_STRING];
0d6a7062 943 BOOL bLocked = FALSE;
5039dede
AK		 944
945 // Check if database is locked
946 hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBLockStatus'"));
947 if (hResult != NULL)
948 {
949 if (DBGetNumRows(hResult) > 0)
950 {
bf7f9d94 951 DBGetField(hResult, 0, 0, szLockStatus, MAX_DB_STRING);
5039dede
AK		 952 DecodeSQLString(szLockStatus);
953 bLocked = _tcscmp(szLockStatus, _T("UNLOCKED"));
954 }
955 DBFreeResult(hResult);
956
957 if (bLocked)
958 {
959 hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBLockInfo'"));
960 if (hResult != NULL)
961 {
962 if (DBGetNumRows(hResult) > 0)
963 {
bf7f9d94 964 DBGetField(hResult, 0, 0, szLockInfo, MAX_DB_STRING);
5039dede
AK		 965 DecodeSQLString(szLockInfo);
966 }
967 DBFreeResult(hResult);
968 }
969 }
5039dede 970
96e87c92
VK		 971 if (bLocked)
972 {
973 if (GetYesNo(_T("Database is locked by server %s [%s]\nDo you wish to force database unlock?"), szLockStatus, szLockInfo))
974 {
975 if (SQLQuery(_T("UPDATE config SET var_value='UNLOCKED' where var_name='DBLockStatus'")))
976 {
977 bLocked = FALSE;
978 _tprintf(_T("Database lock removed\n"));
979 }
980 }
981 }
5039dede 982
96e87c92
VK		 983 if (!bLocked)
984 {
985 DBBegin(g_hCoreDB);
986
daf3c104
VK		 987 if (g_checkDataTablesOnly)
988 {
989 CheckDataTables();
990 }
991 else
992 {
993 CheckZones();
994 CheckNodes();
995 CheckComponents(_T("interface"), _T("interfaces"));
996 CheckComponents(_T("network service"), _T("network_services"));
997 CheckClusters();
998 CheckTemplateNodeMapping();
999 CheckObjectProperties();
1000 CheckEPP();
1001 CheckMapLinks();
1002 CheckDataTables();
1003 if (g_checkData)
1004 CheckIData();
1005 }
96e87c92
VK		 1006
1007 if (m_iNumErrors == 0)
1008 {
1009 _tprintf(_T("Database doesn't contain any errors\n"));
1010 DBCommit(g_hCoreDB);
1011 }
1012 else
1013 {
1014 _tprintf(_T("%d errors was found, %d errors was corrected\n"), m_iNumErrors, m_iNumFixes);
1015 if (m_iNumFixes == m_iNumErrors)
1016 _tprintf(_T("All errors in database was fixed\n"));
1017 else
1018 _tprintf(_T("Database still contain errors\n"));
1019 if (m_iNumFixes > 0)
1020 {
1021 if (GetYesNo(_T("Commit changes?")))
1022 {
1023 _tprintf(_T("Committing changes...\n"));
1024 if (DBCommit(g_hCoreDB))
1025 _tprintf(_T("Changes was successfully committed to database\n"));
1026 }
1027 else
1028 {
1029 _tprintf(_T("Rolling back changes...\n"));
1030 if (DBRollback(g_hCoreDB))
1031 _tprintf(_T("All changes made to database was cancelled\n"));
1032 }
1033 }
1034 else
1035 {
1036 DBRollback(g_hCoreDB);
1037 }
1038 }
1039 bCompleted = TRUE;
1040 }
5039dede 1041 }
96e87c92
VK		 1042 else
1043 {
1044 _tprintf(_T("Unable to get database lock status\n"));
1045 }
5039dede
AK		 1046 }
1047
1048 _tprintf(_T("Database check %s\n"), bCompleted ? _T("completed") : _T("aborted"));
1049}
NetXMS core