Per stage confirmation in database manager during database check
[public/netxms.git] / src / server / tools / nxdbmgr / check.cpp
1 /*
2 ** nxdbmgr - NetXMS database manager
3 ** Copyright (C) 2004-2016 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: check.cpp
20 **
21 **/
22
23 #include "nxdbmgr.h"
24
25
26 //
27 // Static data
28 //
29
30 static int m_iNumErrors = 0;
31 static int m_iNumFixes = 0;
32 static int m_iStageErrors;
33 static int s_stageErrorsUpdate;
34 static int m_iStageFixes;
35 static TCHAR *m_pszStageMsg = NULL;
36 static int s_stageWorkTotal = 0;
37 static int s_stageWorkDone = 0;
38
39 /**
40 * Start stage
41 */
42 static void StartStage(const TCHAR *pszMsg, int workTotal = 1)
43 {
44 if (pszMsg != NULL)
45 {
46 free(m_pszStageMsg);
47 m_pszStageMsg = _tcsdup(pszMsg);
48 m_iStageErrors = m_iNumErrors;
49 s_stageErrorsUpdate = m_iNumErrors;
50 m_iStageFixes = m_iNumFixes;
51 s_stageWorkTotal = workTotal;
52 s_stageWorkDone = 0;
53 ResetBulkYesNo();
54 }
55 WriteToTerminalEx(_T("\x1b[1m*\x1b[0m %-67s \x1b[37;1m[\x1b[0m 0%% \x1b[37;1m]\x1b[0m\b\b\b"), m_pszStageMsg);
56 #ifndef _WIN32
57 fflush(stdout);
58 #endif
59 SetOperationInProgress(true);
60 }
61
62 /**
63 * Set total work for stage
64 */
65 static void SetStageWorkTotal(int workTotal)
66 {
67 s_stageWorkTotal = workTotal;
68 if (s_stageWorkDone > s_stageWorkTotal)
69 s_stageWorkDone = s_stageWorkTotal;
70 }
71
72 /**
73 * Update stage progress
74 */
75 static void UpdateStageProgress(int installment)
76 {
77 if (m_iNumErrors - s_stageErrorsUpdate > 0)
78 {
79 StartStage(NULL); // redisplay stage message
80 s_stageErrorsUpdate = m_iNumErrors;
81 }
82
83 s_stageWorkDone += installment;
84 if (s_stageWorkDone > s_stageWorkTotal)
85 s_stageWorkDone = s_stageWorkTotal;
86 WriteToTerminalEx(_T("\b\b\b%3d"), s_stageWorkDone * 100 / s_stageWorkTotal);
87 #ifndef _WIN32
88 fflush(stdout);
89 #endif
90 }
91
92 /**
93 * End stage
94 */
95 static void EndStage()
96 {
97 static const TCHAR *pszStatus[] = { _T("PASSED"), _T("FIXED "), _T("ERROR ") };
98 static int nColor[] = { 32, 33, 31 };
99 int nCode, nErrors;
100
101 nErrors = m_iNumErrors - m_iStageErrors;
102 if (nErrors > 0)
103 {
104 nCode = (m_iNumFixes - m_iStageFixes == nErrors) ? 1 : 2;
105 if (m_iNumErrors - s_stageErrorsUpdate > 0)
106 StartStage(NULL); // redisplay stage message
107 }
108 else
109 {
110 nCode = 0;
111 }
112 WriteToTerminalEx(_T("\b\b\b\b\b\x1b[37;1m[\x1b[%d;1m%s\x1b[37;1m]\x1b[0m\n"), nColor[nCode], pszStatus[nCode]);
113 SetOperationInProgress(false);
114 ResetBulkYesNo();
115 }
116
117 /**
118 * Get object name from object_properties table
119 */
120 static TCHAR *GetObjectName(DWORD dwId, TCHAR *pszBuffer)
121 {
122 TCHAR szQuery[256];
123 DB_RESULT hResult;
124
125 _sntprintf(szQuery, 256, _T("SELECT name FROM object_properties WHERE object_id=%d"), dwId);
126 hResult = SQLSelect(szQuery);
127 if (hResult != NULL)
128 {
129 if (DBGetNumRows(hResult) > 0)
130 {
131 DBGetField(hResult, 0, 0, pszBuffer, MAX_OBJECT_NAME);
132 }
133 else
134 {
135 _tcscpy(pszBuffer, _T("<unknown>"));
136 }
137 }
138 else
139 {
140 _tcscpy(pszBuffer, _T("<unknown>"));
141 }
142 return pszBuffer;
143 }
144
145 /**
146 * Check that given node is inside at least one container or cluster
147 */
148 static bool NodeInContainer(DWORD id)
149 {
150 TCHAR query[256];
151 DB_RESULT hResult;
152 bool result = false;
153
154 _sntprintf(query, 256, _T("SELECT container_id FROM container_members WHERE object_id=%d"), id);
155 hResult = SQLSelect(query);
156 if (hResult != NULL)
157 {
158 result = (DBGetNumRows(hResult) > 0);
159 DBFreeResult(hResult);
160 }
161
162 if (!result)
163 {
164 _sntprintf(query, 256, _T("SELECT cluster_id FROM cluster_members WHERE node_id=%d"), id);
165 hResult = SQLSelect(query);
166 if (hResult != NULL)
167 {
168 result = (DBGetNumRows(hResult) > 0);
169 DBFreeResult(hResult);
170 }
171 }
172
173 return result;
174 }
175
176 /**
177 * Find subnet for unlinked node
178 */
179 static BOOL FindSubnetForNode(DWORD id, const TCHAR *name)
180 {
181 DB_RESULT hResult, hResult2;
182 TCHAR query[256], buffer[32];
183 int i, count;
184 BOOL success = FALSE;
185
186 // Read list of interfaces of given node
187 _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);
188 hResult = SQLSelect(query);
189 if (hResult != NULL)
190 {
191 count = DBGetNumRows(hResult);
192 for(i = 0; i < count; i++)
193 {
194 InetAddress addr = DBGetFieldInetAddr(hResult, i, 0);
195 addr.setMaskBits(DBGetFieldLong(hResult, i, 1));
196 InetAddress subnet = addr.getSubnetAddress();
197
198 _sntprintf(query, 256, _T("SELECT id FROM subnets WHERE ip_addr='%s'"), subnet.toString(buffer));
199 hResult2 = SQLSelect(query);
200 if (hResult2 != NULL)
201 {
202 if (DBGetNumRows(hResult2) > 0)
203 {
204 UINT32 subnetId = DBGetFieldULong(hResult2, 0, 0);
205 m_iNumErrors++;
206 if (GetYesNoEx(_T("Unlinked node object %d (\"%s\") can be linked to subnet %d (%s). Link?"), id, name, subnetId, buffer))
207 {
208 _sntprintf(query, 256, _T("INSERT INTO nsmap (subnet_id,node_id) VALUES (%d,%d)"), subnetId, id);
209 if (SQLQuery(query))
210 {
211 success = TRUE;
212 m_iNumFixes++;
213 break;
214 }
215 else
216 {
217 // Node remains unlinked, so error count will be
218 // incremented again by node deletion code or next iteration
219 m_iNumErrors--;
220 }
221 }
222 else
223 {
224 // Node remains unlinked, so error count will be
225 // incremented again by node deletion code
226 m_iNumErrors--;
227 }
228 }
229 DBFreeResult(hResult2);
230 }
231 }
232 DBFreeResult(hResult);
233 }
234 return success;
235 }
236
237 /**
238 * Check missing object properties
239 */
240 static void CheckMissingObjectProperties(const TCHAR *table, const TCHAR *className, UINT32 builtinObjectId)
241 {
242 TCHAR query[1024];
243 _sntprintf(query, 1024, _T("SELECT o.id FROM %s o LEFT OUTER JOIN object_properties p ON p.object_id = o.id WHERE p.name IS NULL"), table);
244 DB_RESULT hResult = SQLSelect(query);
245 if (hResult == NULL)
246 return;
247
248 int count = DBGetNumRows(hResult);
249 for(int i = 0; i < count; i++)
250 {
251 UINT32 id = DBGetFieldULong(hResult, i, 0);
252 if (id == builtinObjectId)
253 continue;
254 m_iNumErrors++;
255 if (GetYesNoEx(_T("Missing %s object %d properties. Create?"), className, id))
256 {
257 uuid_t guid;
258 _uuid_generate(guid);
259
260 TCHAR guidText[128];
261 _sntprintf(query, 1024,
262 _T("INSERT INTO object_properties (object_id,guid,name,")
263 _T("status,is_deleted,is_system,inherit_access_rights,")
264 _T("last_modified,status_calc_alg,status_prop_alg,")
265 _T("status_fixed_val,status_shift,status_translation,")
266 _T("status_single_threshold,status_thresholds,location_type,")
267 _T("latitude,longitude,location_accuracy,location_timestamp,image,submap_id,maint_mode,maint_event_id) VALUES ")
268 _T("(%d,'%s','lost_%s_%d',5,0,0,1,") TIME_T_FMT _T(",0,0,0,0,0,0,'00000000',0,")
269 _T("'0.000000','0.000000',0,0,'00000000-0000-0000-0000-000000000000',0,'0',0)"),
270 (int)id, _uuid_to_string(guid, guidText), className, (int)id, TIME_T_FCAST(time(NULL)));
271 if (SQLQuery(query))
272 m_iNumFixes++;
273 }
274 }
275 DBFreeResult(hResult);
276 }
277
278 /**
279 * Check zone objects
280 */
281 static void CheckZones()
282 {
283 StartStage(_T("Checking zone object properties"));
284 CheckMissingObjectProperties(_T("zones"), _T("zone"), 4);
285 EndStage();
286 }
287
288 /**
289 * Check node objects
290 */
291 static void CheckNodes()
292 {
293 StartStage(_T("Checking node object properties"));
294 CheckMissingObjectProperties(_T("nodes"), _T("node"), 0);
295 EndStage();
296
297 StartStage(_T("Checking node to subnet bindings"));
298 DB_RESULT hResult = SQLSelect(_T("SELECT n.id,p.name FROM nodes n INNER JOIN object_properties p ON p.object_id = n.id WHERE p.is_deleted=0"));
299 int count = DBGetNumRows(hResult);
300 SetStageWorkTotal(count);
301 for(int i = 0; i < count; i++)
302 {
303 UINT32 nodeId = DBGetFieldULong(hResult, i, 0);
304
305 TCHAR query[1024];
306 _sntprintf(query, 1024, _T("SELECT subnet_id FROM nsmap WHERE node_id=%d"), nodeId);
307 DB_RESULT hResult2 = SQLSelect(query);
308 if (hResult2 != NULL)
309 {
310 if ((DBGetNumRows(hResult2) == 0) && (!NodeInContainer(nodeId)))
311 {
312 TCHAR nodeName[MAX_OBJECT_NAME];
313 DBGetField(hResult, i, 1, nodeName, MAX_OBJECT_NAME);
314 if ((DBGetFieldIPAddr(hResult, i, 1) == 0) || (!FindSubnetForNode(nodeId, nodeName)))
315 {
316 m_iNumErrors++;
317 if (GetYesNoEx(_T("Unlinked node object %d (\"%s\"). Delete it?"), nodeId, nodeName))
318 {
319 _sntprintf(query, 1024, _T("DELETE FROM nodes WHERE id=%d"), nodeId);
320 bool success = SQLQuery(query);
321 _sntprintf(query, 1024, _T("DELETE FROM acl WHERE object_id=%d"), nodeId);
322 success = success && SQLQuery(query);
323 _sntprintf(query, 1024, _T("DELETE FROM object_properties WHERE object_id=%d"), nodeId);
324 if (success && SQLQuery(query))
325 m_iNumFixes++;
326 }
327 }
328 }
329 DBFreeResult(hResult2);
330 }
331 UpdateStageProgress(1);
332 }
333 DBFreeResult(hResult);
334 EndStage();
335 }
336
337 /**
338 * Check node component objects
339 */
340 static void CheckComponents(const TCHAR *pszDisplayName, const TCHAR *pszTable)
341 {
342 TCHAR stageName[256];
343 _sntprintf(stageName, 256, _T("Checking %s object properties"), pszDisplayName);
344 StartStage(stageName);
345 CheckMissingObjectProperties(pszTable, pszDisplayName, 0);
346 EndStage();
347
348 _sntprintf(stageName, 256, _T("Checking %s bindings"), pszDisplayName);
349 StartStage(stageName);
350
351 TCHAR query[256];
352 _sntprintf(query, 1024, _T("SELECT id,node_id FROM %s"), pszTable);
353 DB_RESULT hResult = SQLSelect(query);
354 if (hResult != NULL)
355 {
356 int count = DBGetNumRows(hResult);
357 SetStageWorkTotal(count);
358 for(int i = 0; i < count; i++)
359 {
360 UINT32 objectId = DBGetFieldULong(hResult, i, 0);
361
362 // Check if referred node exists
363 _sntprintf(query, 256, _T("SELECT name FROM object_properties WHERE object_id=%d AND is_deleted=0"),
364 DBGetFieldULong(hResult, i, 1));
365 DB_RESULT hResult2 = SQLSelect(query);
366 if (hResult2 != NULL)
367 {
368 if (DBGetNumRows(hResult2) == 0)
369 {
370 m_iNumErrors++;
371 TCHAR objectName[MAX_OBJECT_NAME];
372 if (GetYesNoEx(_T("Unlinked %s object %d (\"%s\"). Delete it?"), pszDisplayName, objectId, GetObjectName(objectId, objectName)))
373 {
374 _sntprintf(query, 256, _T("DELETE FROM %s WHERE id=%d"), pszTable, objectId);
375 if (SQLQuery(query))
376 {
377 _sntprintf(query, 256, _T("DELETE FROM object_properties WHERE object_id=%d"), objectId);
378 SQLQuery(query);
379 m_iNumFixes++;
380 }
381 }
382 }
383 DBFreeResult(hResult2);
384 }
385 }
386 DBFreeResult(hResult);
387 }
388 EndStage();
389 }
390
391 /**
392 * Check common object properties
393 */
394 static void CheckObjectProperties()
395 {
396 DB_RESULT hResult;
397 TCHAR szQuery[1024];
398 DWORD i, dwNumRows, dwObjectId;
399
400 StartStage(_T("Checking object properties"));
401 hResult = SQLSelect(_T("SELECT object_id,name,last_modified FROM object_properties"));
402 if (hResult != NULL)
403 {
404 dwNumRows = DBGetNumRows(hResult);
405 for(i = 0; i < dwNumRows; i++)
406 {
407 dwObjectId = DBGetFieldULong(hResult, i, 0);
408
409 // Check last change time
410 if (DBGetFieldULong(hResult, i, 2) == 0)
411 {
412 m_iNumErrors++;
413 if (GetYesNoEx(_T("Object %d [%s] has invalid timestamp. Fix it?"),
414 dwObjectId, DBGetField(hResult, i, 1, szQuery, 1024)))
415 {
416 _sntprintf(szQuery, 1024, _T("UPDATE object_properties SET last_modified=") TIME_T_FMT _T(" WHERE object_id=%d"),
417 TIME_T_FCAST(time(NULL)), (int)dwObjectId);
418 if (SQLQuery(szQuery))
419 m_iNumFixes++;
420 }
421 }
422 }
423 DBFreeResult(hResult);
424 }
425 EndStage();
426 }
427
428 /**
429 * Check container membership
430 */
431 static void CheckContainerMembership()
432 {
433 StartStage(_T("Checking container membership"));
434 DB_RESULT containerList = SQLSelect(_T("SELECT object_id,container_id FROM container_members"));
435 DB_RESULT objectList = SQLSelect(_T("SELECT object_id FROM object_properties"));
436 if (containerList != NULL && objectList != NULL)
437 {
438 int numContainers = DBGetNumRows(containerList);
439 int numObjects = DBGetNumRows(objectList);
440 bool match = false;
441 TCHAR szQuery[1024];
442
443 SetStageWorkTotal(numContainers);
444 for(int i = 0; i < numContainers; i++)
445 {
446 for(int n = 0; n < numObjects; n++)
447 {
448 if (DBGetFieldULong(containerList, i, 0) == DBGetFieldULong(objectList, n, 0))
449 {
450 match = true;
451 break;
452 }
453 }
454 if (!match)
455 {
456 m_iNumErrors++;
457 if (GetYesNoEx(_T("Container %d contains non-existing child %d. Fix it?"),
458 DBGetFieldULong(containerList, i, 1), DBGetFieldULong(containerList, i, 0)))
459 {
460 _sntprintf(szQuery, 1024, _T("DELETE FROM container_members WHERE object_id=%d AND container_id=%d"),
461 DBGetFieldULong(containerList, i, 0), DBGetFieldULong(containerList, i, 1));
462 if (SQLQuery(szQuery))
463 m_iNumFixes++;
464 }
465 }
466 match = false;
467 UpdateStageProgress(1);
468 }
469 DBFreeResult(containerList);
470 DBFreeResult(objectList);
471 }
472 EndStage();
473 }
474
475 /**
476 * Check cluster objects
477 */
478 static void CheckClusters()
479 {
480 StartStage(_T("Checking cluster object properties"));
481 CheckMissingObjectProperties(_T("clusters"), _T("cluster"), 0);
482 EndStage();
483
484 StartStage(_T("Checking cluster member nodes"));
485 DB_RESULT hResult = SQLSelect(_T("SELECT cluster_id,node_id FROM cluster_members"));
486 if (hResult != NULL)
487 {
488 int count = DBGetNumRows(hResult);
489 SetStageWorkTotal(count);
490 for(int i = 0; i < count; i++)
491 {
492 UINT32 nodeId = DBGetFieldULong(hResult, i, 1);
493 if (!IsDatabaseRecordExist(_T("nodes"), _T("id"), nodeId))
494 {
495 m_iNumErrors++;
496 UINT32 clusterId = DBGetFieldULong(hResult, i, 0);
497 TCHAR name[MAX_OBJECT_NAME];
498 if (GetYesNoEx(_T("Cluster object %s [%d] refers to non-existing node %d. Dereference?"),
499 GetObjectName(clusterId, name), clusterId, nodeId))
500 {
501 TCHAR query[256];
502 _sntprintf(query, 256, _T("DELETE FROM cluster_members WHERE cluster_id=%d AND node_id=%d"), clusterId, nodeId);
503 if (SQLQuery(query))
504 {
505 m_iNumFixes++;
506 }
507 }
508 }
509 UpdateStageProgress(1);
510 }
511 DBFreeResult(hResult);
512 }
513 EndStage();
514 }
515
516 /**
517 * Returns TRUE if SELECT returns non-empty set
518 */
519 static BOOL CheckResultSet(TCHAR *pszQuery)
520 {
521 DB_RESULT hResult;
522 BOOL bResult = FALSE;
523
524 hResult = SQLSelect(pszQuery);
525 if (hResult != NULL)
526 {
527 bResult = (DBGetNumRows(hResult) > 0);
528 DBFreeResult(hResult);
529 }
530 return bResult;
531 }
532
533 /**
534 * Check event processing policy
535 */
536 static void CheckEPP()
537 {
538 DB_RESULT hResult;
539 TCHAR szQuery[1024];
540 int i, iNumRows;
541 DWORD dwId;
542
543 StartStage(_T("Checking event processing policy"));
544
545 // Check source object ID's
546 hResult = SQLSelect(_T("SELECT object_id FROM policy_source_list"));
547 if (hResult != NULL)
548 {
549 iNumRows = DBGetNumRows(hResult);
550 for(i = 0; i < iNumRows; i++)
551 {
552 dwId = DBGetFieldULong(hResult, i, 0);
553 _sntprintf(szQuery, 1024, _T("SELECT object_id FROM object_properties WHERE object_id=%d"), dwId);
554 if (!CheckResultSet(szQuery))
555 {
556 m_iNumErrors++;
557 if (GetYesNoEx(_T("Invalid object ID %d used in policy. Delete it from policy?"), dwId))
558 {
559 _sntprintf(szQuery, 1024, _T("DELETE FROM policy_source_list WHERE object_id=%d"), dwId);
560 if (SQLQuery(szQuery))
561 m_iNumFixes++;
562 }
563 }
564 }
565 DBFreeResult(hResult);
566 }
567
568 // Check event ID's
569 ResetBulkYesNo();
570 hResult = SQLSelect(_T("SELECT event_code FROM policy_event_list"));
571 if (hResult != NULL)
572 {
573 iNumRows = DBGetNumRows(hResult);
574 for(i = 0; i < iNumRows; i++)
575 {
576 dwId = DBGetFieldULong(hResult, i, 0);
577 if (dwId & GROUP_FLAG)
578 _sntprintf(szQuery, 1024, _T("SELECT id FROM event_groups WHERE id=%d"), dwId);
579 else
580 _sntprintf(szQuery, 1024, _T("SELECT event_code FROM event_cfg WHERE event_code=%d"), dwId);
581 if (!CheckResultSet(szQuery))
582 {
583 m_iNumErrors++;
584 if (GetYesNoEx(_T("Invalid event%s ID 0x%08X referenced in policy. Delete this reference?"), (dwId & GROUP_FLAG) ? _T(" group") : _T(""), dwId))
585 {
586 _sntprintf(szQuery, 1024, _T("DELETE FROM policy_event_list WHERE event_code=%d"), dwId);
587 if (SQLQuery(szQuery))
588 m_iNumFixes++;
589 }
590 }
591 }
592 DBFreeResult(hResult);
593 }
594
595 // Check action ID's
596 ResetBulkYesNo();
597 hResult = SQLSelect(_T("SELECT action_id FROM policy_action_list"));
598 if (hResult != NULL)
599 {
600 iNumRows = DBGetNumRows(hResult);
601 for(i = 0; i < iNumRows; i++)
602 {
603 dwId = DBGetFieldULong(hResult, i, 0);
604 _sntprintf(szQuery, 1024, _T("SELECT action_id FROM actions WHERE action_id=%d"), dwId);
605 if (!CheckResultSet(szQuery))
606 {
607 m_iNumErrors++;
608 if (GetYesNoEx(_T("Invalid action ID %d referenced in policy. Delete this reference?"), dwId))
609 {
610 _sntprintf(szQuery, 1024, _T("DELETE FROM policy_action_list WHERE action_id=%d"), dwId);
611 if (SQLQuery(szQuery))
612 m_iNumFixes++;
613 }
614 }
615 }
616 DBFreeResult(hResult);
617 }
618
619 EndStage();
620 }
621
622 /**
623 * Check data tables for given object class
624 */
625 static void CollectObjectIdentifiers(const TCHAR *className, IntegerArray<UINT32> *list)
626 {
627 TCHAR query[1024];
628 _sntprintf(query, 256, _T("SELECT id FROM %s"), className);
629 DB_RESULT hResult = SQLSelect(query);
630 if (hResult != NULL)
631 {
632 int count = DBGetNumRows(hResult);
633 for(int i = 0; i < count; i++)
634 {
635 list->add(DBGetFieldULong(hResult, i, 0));
636 }
637 DBFreeResult(hResult);
638 }
639 }
640
641 /**
642 * Get all data collection targets
643 */
644 IntegerArray<UINT32> *GetDataCollectionTargets()
645 {
646 IntegerArray<UINT32> *list = new IntegerArray<UINT32>(128, 128);
647 CollectObjectIdentifiers(_T("nodes"), list);
648 CollectObjectIdentifiers(_T("clusters"), list);
649 CollectObjectIdentifiers(_T("mobile_devices"), list);
650 CollectObjectIdentifiers(_T("access_points"), list);
651 CollectObjectIdentifiers(_T("chassis"), list);
652 CollectObjectIdentifiers(_T("sensors"), list);
653 return list;
654 }
655
656 /**
657 * Create idata_xx table
658 */
659 BOOL CreateIDataTable(DWORD nodeId)
660 {
661 TCHAR szQuery[256], szQueryTemplate[256];
662 DWORD i;
663
664 MetaDataReadStr(_T("IDataTableCreationCommand"), szQueryTemplate, 255, _T(""));
665 _sntprintf(szQuery, 256, szQueryTemplate, nodeId);
666 if (!SQLQuery(szQuery))
667 return FALSE;
668
669 for(i = 0; i < 10; i++)
670 {
671 _sntprintf(szQuery, 256, _T("IDataIndexCreationCommand_%d"), i);
672 MetaDataReadStr(szQuery, szQueryTemplate, 255, _T(""));
673 if (szQueryTemplate[0] != 0)
674 {
675 _sntprintf(szQuery, 256, szQueryTemplate, nodeId, nodeId);
676 if (!SQLQuery(szQuery))
677 return FALSE;
678 }
679 }
680
681 return TRUE;
682 }
683
684 /**
685 * Create tdata_xx table - pre V281 version
686 */
687 BOOL CreateTDataTable_preV281(DWORD nodeId)
688 {
689 TCHAR szQuery[256], szQueryTemplate[256];
690 DWORD i;
691
692 MetaDataReadStr(_T("TDataTableCreationCommand"), szQueryTemplate, 255, _T(""));
693 _sntprintf(szQuery, 256, szQueryTemplate, nodeId);
694 if (!SQLQuery(szQuery))
695 return FALSE;
696
697 for(i = 0; i < 10; i++)
698 {
699 _sntprintf(szQuery, 256, _T("TDataIndexCreationCommand_%d"), i);
700 MetaDataReadStr(szQuery, szQueryTemplate, 255, _T(""));
701 if (szQueryTemplate[0] != 0)
702 {
703 _sntprintf(szQuery, 256, szQueryTemplate, nodeId, nodeId);
704 if (!SQLQuery(szQuery))
705 return FALSE;
706 }
707 }
708
709 return TRUE;
710 }
711
712 /**
713 * Create tdata_xx table
714 */
715 BOOL CreateTDataTable(DWORD nodeId)
716 {
717 TCHAR szQuery[256], szQueryTemplate[256];
718 DWORD i;
719
720 for(i = 0; i < 10; i++)
721 {
722 _sntprintf(szQuery, 256, _T("TDataTableCreationCommand_%d"), i);
723 MetaDataReadStr(szQuery, szQueryTemplate, 255, _T(""));
724 if (szQueryTemplate[0] != 0)
725 {
726 _sntprintf(szQuery, 256, szQueryTemplate, nodeId, nodeId);
727 if (!SQLQuery(szQuery))
728 return FALSE;
729 }
730 }
731
732 for(i = 0; i < 10; i++)
733 {
734 _sntprintf(szQuery, 256, _T("TDataIndexCreationCommand_%d"), i);
735 MetaDataReadStr(szQuery, szQueryTemplate, 255, _T(""));
736 if (szQueryTemplate[0] != 0)
737 {
738 _sntprintf(szQuery, 256, szQueryTemplate, nodeId, nodeId);
739 if (!SQLQuery(szQuery))
740 return FALSE;
741 }
742 }
743
744 return TRUE;
745 }
746
747 /**
748 * Check if DCI exists
749 */
750 static bool IsDciExists(UINT32 dciId, UINT32 nodeId, bool isTable)
751 {
752 TCHAR query[256];
753 if (nodeId != 0)
754 _sntprintf(query, 256, _T("SELECT count(*) FROM %s WHERE item_id=%d AND node_id=%d"), isTable ? _T("dc_tables") : _T("items"), dciId, nodeId);
755 else
756 _sntprintf(query, 256, _T("SELECT count(*) FROM %s WHERE item_id=%d"), isTable ? _T("dc_tables") : _T("items"), dciId);
757 DB_RESULT hResult = SQLSelect(query);
758 if (hResult == NULL)
759 return false;
760
761 int count = DBGetFieldLong(hResult, 0, 0);
762 DBFreeResult(hResult);
763 return count != 0;
764 }
765
766 /**
767 * Check collected data
768 */
769 static void CheckCollectedData(bool isTable)
770 {
771 StartStage(isTable ? _T("Checking table DCI history records") : _T("Checking DCI history records"));
772
773 time_t now = time(NULL);
774 IntegerArray<UINT32> *targets = GetDataCollectionTargets();
775 SetStageWorkTotal(targets->size());
776 for(int i = 0; i < targets->size(); i++)
777 {
778 UINT32 objectId = targets->get(i);
779 TCHAR query[1024];
780 _sntprintf(query, 1024, _T("SELECT count(*) FROM %s_%d WHERE %s_timestamp>") TIME_T_FMT,
781 isTable ? _T("tdata") : _T("idata"), objectId, isTable ? _T("tdata") : _T("idata"), TIME_T_FCAST(now));
782 DB_RESULT hResult = SQLSelect(query);
783 if (hResult != NULL)
784 {
785 if (DBGetFieldLong(hResult, 0, 0) > 0)
786 {
787 m_iNumErrors++;
788 if (GetYesNoEx(_T("Found collected data for node [%d] with timestamp in the future. Delete invalid records?"), objectId))
789 {
790 _sntprintf(query, 1024, _T("DELETE FROM %s_%d WHERE %s_timestamp>") TIME_T_FMT,
791 isTable ? _T("tdata") : _T("idata"), objectId, isTable ? _T("tdata") : _T("idata"), TIME_T_FCAST(now));
792 if (SQLQuery(query))
793 m_iNumFixes++;
794 }
795 }
796 DBFreeResult(hResult);
797 }
798 }
799
800 ResetBulkYesNo();
801
802 for(int i = 0; i < targets->size(); i++)
803 {
804 UINT32 objectId = targets->get(i);
805 TCHAR query[1024];
806 _sntprintf(query, 1024, _T("SELECT distinct(item_id) FROM %s_%d"), isTable ? _T("tdata") : _T("idata"), objectId);
807 DB_RESULT hResult = SQLSelect(query);
808 if (hResult != NULL)
809 {
810 int count = DBGetNumRows(hResult);
811 for(int i = 0; i < count; i++)
812 {
813 UINT32 id = DBGetFieldLong(hResult, i, 0);
814 if (!IsDciExists(id, objectId, isTable))
815 {
816 m_iNumErrors++;
817 if (GetYesNoEx(_T("Found collected data for non-existing DCI [%d] on node [%d]. Delete invalid records?"), id, objectId))
818 {
819 _sntprintf(query, 1024, _T("DELETE FROM %s_%d WHERE item_id=%d"), isTable ? _T("tdata") : _T("idata"), objectId, id);
820 if (SQLQuery(query))
821 m_iNumFixes++;
822 }
823 }
824 }
825 DBFreeResult(hResult);
826 }
827
828 UpdateStageProgress(1);
829 }
830 delete targets;
831
832 EndStage();
833 }
834
835 /**
836 * Check raw DCI values
837 */
838 static void CheckRawDciValues()
839 {
840 StartStage(_T("Checking raw DCI values table"));
841
842 time_t now = time(NULL);
843
844 DB_RESULT hResult = SQLSelect(_T("SELECT item_id FROM raw_dci_values"));
845 if (hResult != NULL)
846 {
847 int count = DBGetNumRows(hResult);
848 SetStageWorkTotal(count + 1);
849 for(int i = 0; i < count; i++)
850 {
851 UINT32 id = DBGetFieldLong(hResult, i, 0);
852 if (!IsDciExists(id, 0, false))
853 {
854 m_iNumErrors++;
855 if (GetYesNoEx(_T("Found raw value record for non-existing DCI [%d]. Delete it?"), id))
856 {
857 TCHAR query[256];
858 _sntprintf(query, 256, _T("DELETE FROM raw_dci_values WHERE item_id=%d"), id);
859 if (SQLQuery(query))
860 m_iNumFixes++;
861 }
862 }
863 UpdateStageProgress(1);
864 }
865 DBFreeResult(hResult);
866 }
867
868 ResetBulkYesNo();
869 TCHAR query[1024];
870 _sntprintf(query, 1024, _T("SELECT count(*) FROM raw_dci_values WHERE last_poll_time>") TIME_T_FMT, TIME_T_FCAST(now));
871 hResult = SQLSelect(query);
872 if (hResult != NULL)
873 {
874 if (DBGetFieldLong(hResult, 0, 0) > 0)
875 {
876 m_iNumErrors++;
877 if (GetYesNoEx(_T("Found DCIs with last poll timestamp in the future. Fix it?")))
878 {
879 _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));
880 if (SQLQuery(query))
881 m_iNumFixes++;
882 }
883 }
884 DBFreeResult(hResult);
885 }
886 UpdateStageProgress(1);
887
888 EndStage();
889 }
890
891 /**
892 * Check thresholds
893 */
894 static void CheckThresholds()
895 {
896 StartStage(_T("Checking DCI thresholds"));
897
898 DB_RESULT hResult = SQLSelect(_T("SELECT threshold_id,item_id FROM thresholds"));
899 if (hResult != NULL)
900 {
901 int count = DBGetNumRows(hResult);
902 SetStageWorkTotal(count);
903 for(int i = 0; i < count; i++)
904 {
905 UINT32 dciId = DBGetFieldULong(hResult, i, 1);
906 if (!IsDciExists(dciId, 0, false))
907 {
908 m_iNumErrors++;
909 if (GetYesNoEx(_T("Found threshold configuration for non-existing DCI [%d]. Delete?"), dciId))
910 {
911 TCHAR query[256];
912 _sntprintf(query, 256, _T("DELETE FROM thresholds WHERE threshold_id=%d AND item_id=%d"), DBGetFieldLong(hResult, i, 0), dciId);
913 if (SQLQuery(query))
914 m_iNumFixes++;
915 }
916 }
917 UpdateStageProgress(1);
918 }
919 DBFreeResult(hResult);
920 }
921
922 EndStage();
923 }
924
925 /**
926 * Check thresholds
927 */
928 static void CheckTableThresholds()
929 {
930 StartStage(_T("Checking table DCI thresholds"));
931
932 DB_RESULT hResult = SQLSelect(_T("SELECT id,table_id FROM dct_thresholds"));
933 if (hResult != NULL)
934 {
935 int count = DBGetNumRows(hResult);
936 SetStageWorkTotal(count);
937 for(int i = 0; i < count; i++)
938 {
939 UINT32 dciId = DBGetFieldULong(hResult, i, 1);
940 if (!IsDciExists(dciId, 0, true))
941 {
942 m_iNumErrors++;
943 if (GetYesNoEx(_T("Found threshold configuration for non-existing table DCI [%d]. Delete?"), dciId))
944 {
945 UINT32 id = DBGetFieldLong(hResult, i, 0);
946
947 TCHAR query[256];
948 _sntprintf(query, 256, _T("DELETE FROM dct_threshold_instances WHERE threshold_id=%d"), id);
949 if (SQLQuery(query))
950 {
951 _sntprintf(query, 256, _T("DELETE FROM dct_threshold_conditions WHERE threshold_id=%d"), id);
952 if (SQLQuery(query))
953 {
954 _sntprintf(query, 256, _T("DELETE FROM dct_thresholds WHERE id=%d"), id);
955 if (SQLQuery(query))
956 m_iNumFixes++;
957 }
958 }
959 }
960 }
961 UpdateStageProgress(1);
962 }
963 DBFreeResult(hResult);
964 }
965
966 EndStage();
967 }
968
969 /**
970 * Check if given data table exist
971 */
972 bool IsDataTableExist(const TCHAR *format, UINT32 id)
973 {
974 TCHAR table[256];
975 _sntprintf(table, 256, format, id);
976 int rc = DBIsTableExist(g_hCoreDB, table);
977 if (rc == DBIsTableExist_Failure)
978 {
979 _tprintf(_T("WARNING: call to DBIsTableExist(\"%s\") failed\n"), table);
980 }
981 return rc != DBIsTableExist_NotFound;
982 }
983
984 /**
985 * Check data tables
986 */
987 static void CheckDataTables()
988 {
989 StartStage(_T("Checking data tables"));
990
991 IntegerArray<UINT32> *targets = GetDataCollectionTargets();
992 SetStageWorkTotal(targets->size());
993 for(int i = 0; i < targets->size(); i++)
994 {
995 UINT32 objectId = targets->get(i);
996
997 // IDATA
998 if (!IsDataTableExist(_T("idata_%d"), objectId))
999 {
1000 m_iNumErrors++;
1001
1002 TCHAR objectName[MAX_OBJECT_NAME];
1003 GetObjectName(objectId, objectName);
1004 if (GetYesNoEx(_T("Data collection table (IDATA) for object %s [%d] not found. Create? (Y/N) "), objectName, objectId))
1005 {
1006 if (CreateIDataTable(objectId))
1007 m_iNumFixes++;
1008 }
1009 }
1010
1011 // TDATA
1012 if (!IsDataTableExist(_T("tdata_%d"), objectId))
1013 {
1014 m_iNumErrors++;
1015
1016 TCHAR objectName[MAX_OBJECT_NAME];
1017 GetObjectName(objectId, objectName);
1018 if (GetYesNoEx(_T("Data collection table (TDATA) for %s [%d] not found. Create? (Y/N) "), objectName, objectId))
1019 {
1020 if (CreateTDataTable(objectId))
1021 m_iNumFixes++;
1022 }
1023 }
1024
1025 UpdateStageProgress(1);
1026 }
1027
1028 delete targets;
1029 EndStage();
1030 }
1031
1032 /**
1033 * Check template to node mapping
1034 */
1035 static void CheckTemplateNodeMapping()
1036 {
1037 DB_RESULT hResult;
1038 TCHAR name[256], query[256];
1039 DWORD i, dwNumRows, dwTemplateId, dwNodeId;
1040
1041 StartStage(_T("Checking template to node mapping"));
1042 hResult = SQLSelect(_T("SELECT template_id,node_id FROM dct_node_map ORDER BY template_id"));
1043 if (hResult != NULL)
1044 {
1045 dwNumRows = DBGetNumRows(hResult);
1046 SetStageWorkTotal(dwNumRows);
1047 for(i = 0; i < dwNumRows; i++)
1048 {
1049 dwTemplateId = DBGetFieldULong(hResult, i, 0);
1050 dwNodeId = DBGetFieldULong(hResult, i, 1);
1051
1052 // Check node existence
1053 if (!IsDatabaseRecordExist(_T("nodes"), _T("id"), dwNodeId) &&
1054 !IsDatabaseRecordExist(_T("clusters"), _T("id"), dwNodeId) &&
1055 !IsDatabaseRecordExist(_T("mobile_devices"), _T("id"), dwNodeId))
1056 {
1057 m_iNumErrors++;
1058 GetObjectName(dwTemplateId, name);
1059 if (GetYesNoEx(_T("Template %d [%s] mapped to non-existent node %d. Delete this mapping?"), dwTemplateId, name, dwNodeId))
1060 {
1061 _sntprintf(query, 256, _T("DELETE FROM dct_node_map WHERE template_id=%d AND node_id=%d"),
1062 dwTemplateId, dwNodeId);
1063 if (SQLQuery(query))
1064 m_iNumFixes++;
1065 }
1066 }
1067 UpdateStageProgress(1);
1068 }
1069 DBFreeResult(hResult);
1070 }
1071 EndStage();
1072 }
1073
1074 /**
1075 * Check network map links
1076 */
1077 static void CheckMapLinks()
1078 {
1079 StartStage(_T("Checking network map links"));
1080
1081 for(int pass = 1; pass <= 2; pass++)
1082 {
1083 TCHAR query[1024];
1084 _sntprintf(query, 1024,
1085 _T("SELECT network_map_links.map_id,network_map_links.element1,network_map_links.element2 ")
1086 _T("FROM network_map_links ")
1087 _T("LEFT OUTER JOIN network_map_elements ON ")
1088 _T(" network_map_links.map_id = network_map_elements.map_id AND ")
1089 _T(" network_map_links.element%d = network_map_elements.element_id ")
1090 _T("WHERE network_map_elements.element_id IS NULL"), pass);
1091
1092 DB_RESULT hResult = SQLSelect(query);
1093 if (hResult != NULL)
1094 {
1095 int count = DBGetNumRows(hResult);
1096 for(int i = 0; i < count; i++)
1097 {
1098 m_iNumErrors++;
1099 DWORD mapId = DBGetFieldULong(hResult, i, 0);
1100 TCHAR name[MAX_OBJECT_NAME];
1101 GetObjectName(mapId, name);
1102 if (GetYesNoEx(_T("Invalid link on network map %s [%d]. Delete?"), name, mapId))
1103 {
1104 _sntprintf(query, 256, _T("DELETE FROM network_map_links WHERE map_id=%d AND element1=%d AND element2=%d"),
1105 mapId, DBGetFieldLong(hResult, i, 1), DBGetFieldLong(hResult, i, 2));
1106 if (SQLQuery(query))
1107 m_iNumFixes++;
1108 }
1109 }
1110 DBFreeResult(hResult);
1111 }
1112 }
1113 EndStage();
1114 }
1115
1116 /**
1117 * Check database for errors
1118 */
1119 void CheckDatabase()
1120 {
1121 if (g_checkDataTablesOnly)
1122 _tprintf(_T("Checking database (data tables only):\n"));
1123 else
1124 _tprintf(_T("Checking database (%s collected data):\n"), g_checkData ? _T("including") : _T("excluding"));
1125
1126 // Get database format version
1127 INT32 major, minor;
1128 if (!DBGetSchemaVersion(g_hCoreDB, &major, &minor))
1129 {
1130 _tprintf(_T("Unable to determine database schema version\n"));
1131 _tprintf(_T("Database check aborted\n"));
1132 return;
1133 }
1134 if ((major > DB_SCHEMA_VERSION_MAJOR) || ((major == DB_SCHEMA_VERSION_MAJOR) && (minor > DB_SCHEMA_VERSION_MINOR)))
1135 {
1136 _tprintf(_T("Your database has format version %d.%d, this tool is compiled for version %d.%d.\n")
1137 _T("You need to upgrade your server before using this database.\n"),
1138 major, minor, DB_SCHEMA_VERSION_MAJOR, DB_SCHEMA_VERSION_MINOR);
1139 _tprintf(_T("Database check aborted\n"));
1140 return;
1141 }
1142 if ((major < DB_SCHEMA_VERSION_MAJOR) || ((major == DB_SCHEMA_VERSION_MAJOR) && (minor < DB_SCHEMA_VERSION_MINOR)))
1143 {
1144 _tprintf(_T("Your database has format version %d.%d, this tool is compiled for version %d.%d.\nUse \"upgrade\" command to upgrade your database first.\n"),
1145 major, minor, DB_SCHEMA_VERSION_MAJOR, DB_SCHEMA_VERSION_MINOR);
1146 _tprintf(_T("Database check aborted\n"));
1147 return;
1148 }
1149
1150 TCHAR szLockStatus[MAX_DB_STRING], szLockInfo[MAX_DB_STRING];
1151 BOOL bLocked = FALSE;
1152 BOOL bCompleted = FALSE;
1153
1154 // Check if database is locked
1155 DB_RESULT hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBLockStatus'"));
1156 if (hResult != NULL)
1157 {
1158 if (DBGetNumRows(hResult) > 0)
1159 {
1160 DBGetField(hResult, 0, 0, szLockStatus, MAX_DB_STRING);
1161 DecodeSQLString(szLockStatus);
1162 bLocked = _tcscmp(szLockStatus, _T("UNLOCKED"));
1163 }
1164 DBFreeResult(hResult);
1165
1166 if (bLocked)
1167 {
1168 hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBLockInfo'"));
1169 if (hResult != NULL)
1170 {
1171 if (DBGetNumRows(hResult) > 0)
1172 {
1173 DBGetField(hResult, 0, 0, szLockInfo, MAX_DB_STRING);
1174 DecodeSQLString(szLockInfo);
1175 }
1176 DBFreeResult(hResult);
1177 }
1178 }
1179
1180 if (bLocked)
1181 {
1182 if (GetYesNo(_T("Database is locked by server %s [%s]\nDo you wish to force database unlock?"), szLockStatus, szLockInfo))
1183 {
1184 if (SQLQuery(_T("UPDATE config SET var_value='UNLOCKED' where var_name='DBLockStatus'")))
1185 {
1186 bLocked = FALSE;
1187 _tprintf(_T("Database lock removed\n"));
1188 }
1189 }
1190 }
1191
1192 if (!bLocked)
1193 {
1194 DBBegin(g_hCoreDB);
1195
1196 if (g_checkDataTablesOnly)
1197 {
1198 CheckDataTables();
1199 }
1200 else
1201 {
1202 CheckZones();
1203 CheckNodes();
1204 CheckComponents(_T("interface"), _T("interfaces"));
1205 CheckComponents(_T("network service"), _T("network_services"));
1206 CheckClusters();
1207 CheckTemplateNodeMapping();
1208 CheckObjectProperties();
1209 CheckContainerMembership();
1210 CheckEPP();
1211 CheckMapLinks();
1212 CheckDataTables();
1213 CheckRawDciValues();
1214 CheckThresholds();
1215 CheckTableThresholds();
1216 if (g_checkData)
1217 {
1218 CheckCollectedData(false);
1219 CheckCollectedData(true);
1220 }
1221 }
1222
1223 if (m_iNumErrors == 0)
1224 {
1225 _tprintf(_T("Database doesn't contain any errors\n"));
1226 DBCommit(g_hCoreDB);
1227 }
1228 else
1229 {
1230 _tprintf(_T("%d errors was found, %d errors was corrected\n"), m_iNumErrors, m_iNumFixes);
1231 if (m_iNumFixes == m_iNumErrors)
1232 _tprintf(_T("All errors in database was fixed\n"));
1233 else
1234 _tprintf(_T("Database still contain errors\n"));
1235 if (m_iNumFixes > 0)
1236 {
1237 if (GetYesNo(_T("Commit changes?")))
1238 {
1239 _tprintf(_T("Committing changes...\n"));
1240 if (DBCommit(g_hCoreDB))
1241 _tprintf(_T("Changes was successfully committed to database\n"));
1242 }
1243 else
1244 {
1245 _tprintf(_T("Rolling back changes...\n"));
1246 if (DBRollback(g_hCoreDB))
1247 _tprintf(_T("All changes made to database was cancelled\n"));
1248 }
1249 }
1250 else
1251 {
1252 DBRollback(g_hCoreDB);
1253 }
1254 }
1255 bCompleted = TRUE;
1256 }
1257 }
1258 else
1259 {
1260 _tprintf(_T("Unable to get database lock status\n"));
1261 }
1262
1263 _tprintf(_T("Database check %s\n"), bCompleted ? _T("completed") : _T("aborted"));
1264 }