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 m_iStageFixes;
  34 static TCHAR *m_pszStageMsg = NULL;
  35
  36 /**
  37  * Start stage
  38  */
  39 static void StartStage(const TCHAR *pszMsg)
  40 {
  41    if (pszMsg != NULL)
  42    {
  43       safe_free(m_pszStageMsg);
  44       m_pszStageMsg = _tcsdup(pszMsg);
  45    }
  46    WriteToTerminalEx(_T("\x1b[1m*\x1b[0m %-67s"), m_pszStageMsg);
  47 #ifndef _WIN32
  48    fflush(stdout);
  49 #endif
  50    m_iStageErrors = m_iNumErrors;
  51    m_iStageFixes = m_iNumFixes;
  52 }
  53
  54 /**
  55  * End stage
  56  */
  57 static void EndStage()
  58 {
  59    static const TCHAR *pszStatus[] = { _T("PASSED"), _T("FIXED "), _T("ERROR ") };
  60    static int nColor[] = { 32, 33, 31 };
  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    }
  73    WriteToTerminalEx(_T(" \x1b[37;1m[\x1b[%d;1m%s\x1b[37;1m]\x1b[0m\n"), nColor[nCode], pszStatus[nCode]);
  74 }
  75
  76 /**
  77  * Get object name from object_properties table
  78  */
  79 static TCHAR *GetObjectName(DWORD dwId, TCHAR *pszBuffer)
  80 {
  81         TCHAR szQuery[256];
  82         DB_RESULT hResult;
  83
  84         _sntprintf(szQuery, 256, _T("SELECT name FROM object_properties WHERE object_id=%d"), dwId);
  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
 104 /**
 105  * Check that given node is inside at least one container or cluster
 106  */
 107 static bool NodeInContainer(DWORD id)
 108 {
 109         TCHAR query[256];
 110         DB_RESULT hResult;
 111         bool result = false;
 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         }
 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
 132         return result;
 133 }
 134
 135 /**
 136  * Find subnet for unlinked node
 137  */
 138 static BOOL FindSubnetForNode(DWORD id, const TCHAR *name)
 139 {
 140         DB_RESULT hResult, hResult2;
 141         TCHAR query[256], buffer[32];
 142         int i, count;
 143         BOOL success = FALSE;
 144
 145         // Read list of interfaces of given node
 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);
 147         hResult = SQLSelect(query);
 148         if (hResult != NULL)
 149         {
 150                 count = DBGetNumRows(hResult);
 151                 for(i = 0; i < count; i++)
 152                 {
 153                         InetAddress addr = DBGetFieldInetAddr(hResult, i, 0);
 154          addr.setMaskBits(DBGetFieldLong(hResult, i, 1));
 155          InetAddress subnet = addr.getSubnetAddress();
 156
 157          _sntprintf(query, 256, _T("SELECT id FROM subnets WHERE ip_addr='%s'"), subnet.toString(buffer));
 158                         hResult2 = SQLSelect(query);
 159                         if (hResult2 != NULL)
 160                         {
 161                                 if (DBGetNumRows(hResult2) > 0)
 162                                 {
 163                                         UINT32 subnetId = DBGetFieldULong(hResult2, 0, 0);
 164                                         m_iNumErrors++;
 165                                         if (GetYesNo(_T("\rUnlinked node object %d (\"%s\") can be linked to subnet %d (%s). Link?"), id, name, subnetId, buffer))
 166                                         {
 167                                                 _sntprintf(query, 256, _T("INSERT INTO nsmap (subnet_id,node_id) VALUES (%d,%d)"), subnetId, id);
 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
 196 /**
 197  * Check zone objects
 198  */
 199 static void CheckZones()
 200 {
 201    DB_RESULT hResult, hResult2;
 202    DWORD i, dwNumObjects, dwId;
 203    TCHAR szQuery[1024];
 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
 227                                                 _uuid_generate(guid);
 228                   _sntprintf(szQuery, 1024,
 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,")
 234                                                                           _T("latitude,longitude,image,maint_mode,maint_event_id) VALUES ")
 235                              _T("(%d,'%s','lost_zone_%d',5,0,0,1,") TIME_T_FMT _T(",0,0,0,0,0,0,'00000000',0,")
 236                                                                           _T("'0.000000','0.000000','00000000-0000-0000-0000-000000000000','0',0)"),
 237                                                                           (int)dwId, _uuid_to_string(guid, guidText), (int)dwId, TIME_T_FCAST(time(NULL)));
 238                   if (SQLQuery(szQuery))
 239                      m_iNumFixes++;
 240                }
 241             }
 242             DBFreeResult(hResult2);
 243          }
 244       }
 245       DBFreeResult(hResult);
 246    }
 247    EndStage();
 248 }
 249
 250 /**
 251  * Check node objects
 252  */
 253 static void CheckNodes()
 254 {
 255    DB_RESULT hResult, hResult2;
 256    DWORD i, dwNumObjects, dwId;
 257    TCHAR szQuery[1024], szName[MAX_OBJECT_NAME];
 258    BOOL bResult, bIsDeleted = FALSE;
 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++;
 277                if (GetYesNo(_T("\rMissing node object %d properties. Create?"), dwId))
 278                {
 279                                                 uuid_t guid;
 280                                                 TCHAR guidText[128];
 281
 282                                                 _uuid_generate(guid);
 283                   _sntprintf(szQuery, 1024,
 284                              _T("INSERT INTO object_properties (object_id,guid,name,")
 285                              _T("status,is_deleted,is_system,inherit_access_rights,")
 286                              _T("last_modified,status_calc_alg,status_prop_alg,")
 287                              _T("status_fixed_val,status_shift,status_translation,")
 288                              _T("status_single_threshold,status_thresholds,location_type,")
 289                                                                           _T("latitude,longitude,location_accuracy,location_timestamp,")
 290                                                                           _T("image,submap_id,maint_mode,maint_event_id) VALUES ")
 291                              _T("(%d,'%s','lost_node_%d',5,0,0,1,") TIME_T_FMT _T(",0,0,0,0,0,0,'00000000',0,")
 292                                                                           _T("'0.000000','0.000000',0,0,'00000000-0000-0000-0000-000000000000',0,'0',0)"),
 293                                                                           (int)dwId, _uuid_to_string(guid, guidText), (int)dwId, TIME_T_FCAST(time(NULL)));
 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             {
 312                if ((DBGetNumRows(hResult2) == 0) && (!NodeInContainer(dwId)))
 313                {
 314                                                 if ((DBGetFieldIPAddr(hResult, i, 1) == 0) || (!FindSubnetForNode(dwId, szName)))
 315                                                 {
 316                                                         m_iNumErrors++;
 317                                                         if (GetYesNo(_T("\rUnlinked node object %d (\"%s\"). Delete it?"), dwId, szName))
 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                                                 }
 328                }
 329                DBFreeResult(hResult2);
 330             }
 331          }
 332       }
 333       DBFreeResult(hResult);
 334    }
 335    EndStage();
 336 }
 337
 338 /**
 339  * Check node component objects
 340  */
 341 static 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];
 346
 347    _sntprintf(szQuery, 1024, _T("Checking %s objects..."), pszDisplayName);
 348    StartStage(szQuery);
 349
 350    _sntprintf(szQuery, 1024, _T("SELECT id,node_id FROM %s"), pszTable);
 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
 360          _sntprintf(szQuery, 1024, _T("SELECT name,is_deleted FROM object_properties WHERE object_id=%d"), dwId);
 361          hResult2 = SQLSelect(szQuery);
 362          if (hResult2 != NULL)
 363          {
 364             if (DBGetNumRows(hResult2) == 0)
 365             {
 366                m_iNumErrors++;
 367                if (GetYesNo(_T("\rMissing %s object %d properties. Create?"), pszDisplayName, dwId))
 368                {
 369                                                 uuid_t guid;
 370                                                 TCHAR guidText[128];
 371
 372                                                 _uuid_generate(guid);
 373                   _sntprintf(szQuery, 1024,
 374                              _T("INSERT INTO object_properties (object_id,guid,name,")
 375                              _T("status,is_deleted,is_system,inherit_access_rights,")
 376                              _T("last_modified,status_calc_alg,status_prop_alg,")
 377                              _T("status_fixed_val,status_shift,status_translation,")
 378                              _T("status_single_threshold,status_thresholds,location_type,")
 379                                                                           _T("latitude,longitude,image,maint_mode,maint_event_id) VALUES ")
 380                              _T("(%d,'%s','lost_%s_%d',5,0,0,1,") TIME_T_FMT _T(",0,0,0,0,0,0,'00000000',0,")
 381                                                                           _T("'0.000000','0.000000','00000000-0000-0000-0000-000000000000','0',0)"),
 382                                                                           (int)dwId, _uuid_to_string(guid, guidText), pszDisplayName, (int)dwId, TIME_T_FCAST(time(NULL)));
 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);
 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);
 409                if (GetYesNo(_T("\rUnlinked %s object %d (\"%s\"). Delete it?"), pszDisplayName, dwId, szName))
 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
 428 /**
 429  * Check common object properties
 430  */
 431 static void CheckObjectProperties()
 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++;
 450             if (GetYesNo(_T("\rObject %d [%s] has invalid timestamp. Fix it?"),
 451                                              dwObjectId, DBGetField(hResult, i, 1, szQuery, 1024)))
 452             {
 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);
 455                if (SQLQuery(szQuery))
 456                   m_iNumFixes++;
 457             }
 458          }
 459       }
 460       DBFreeResult(hResult);
 461    }
 462    EndStage();
 463 }
 464
 465 /**
 466  * Check cluster objects
 467  */
 468 static void CheckClusters()
 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);
 482                         if (!IsDatabaseRecordExist(_T("nodes"), _T("id"), dwObjectId))
 483                         {
 484             m_iNumErrors++;
 485             dwId = DBGetFieldULong(hResult, i, 0);
 486             if (GetYesNo(_T("\rCluster object %s [%d] refers to non-existing node %d. Dereference?"),
 487                                              GetObjectName(dwId, szName), dwId, dwObjectId))
 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
 502 /**
 503  * Returns TRUE if SELECT returns non-empty set
 504  */
 505 static 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
 519 /**
 520  * Check event processing policy
 521  */
 522 static void CheckEPP()
 523 {
 524    DB_RESULT hResult;
 525    TCHAR szQuery[1024];
 526    int i, iNumRows;
 527    DWORD dwId;
 528
 529    StartStage(_T("Checking event processing policy..."));
 530
 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);
 539          _sntprintf(szQuery, 1024, _T("SELECT object_id FROM object_properties WHERE object_id=%d"), dwId);
 540          if (!CheckResultSet(szQuery))
 541          {
 542             m_iNumErrors++;
 543             if (GetYesNo(_T("\rInvalid object ID %d used in policy. Delete it from policy?"), dwId))
 544             {
 545                _sntprintf(szQuery, 1024, _T("DELETE FROM policy_source_list WHERE object_id=%d"), dwId);
 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)
 563             _sntprintf(szQuery, 1024, _T("SELECT id FROM event_groups WHERE id=%d"), dwId);
 564          else
 565             _sntprintf(szQuery, 1024, _T("SELECT event_code FROM event_cfg WHERE event_code=%d"), dwId);
 566          if (!CheckResultSet(szQuery))
 567          {
 568             m_iNumErrors++;
 569             if (GetYesNo(_T("\rInvalid event%s ID 0x%08X referenced in policy. Delete this reference?"), (dwId & GROUP_FLAG) ? _T(" group") : _T(""), dwId))
 570             {
 571                _sntprintf(szQuery, 1024, _T("DELETE FROM policy_event_list WHERE event_code=%d"), dwId);
 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);
 588          _sntprintf(szQuery, 1024, _T("SELECT action_id FROM actions WHERE action_id=%d"), dwId);
 589          if (!CheckResultSet(szQuery))
 590          {
 591             m_iNumErrors++;
 592             if (GetYesNo(_T("\rInvalid action ID %d referenced in policy. Delete this reference?"), dwId))
 593             {
 594                _sntprintf(szQuery, 1024, _T("DELETE FROM policy_action_list WHERE action_id=%d"), dwId);
 595                if (SQLQuery(szQuery))
 596                   m_iNumFixes++;
 597             }
 598          }
 599       }
 600       DBFreeResult(hResult);
 601    }
 602
 603    EndStage();
 604 }
 605
 606 /**
 607  * Create idata_xx table
 608  */
 609 BOOL CreateIDataTable(DWORD nodeId)
 610 {
 611    TCHAR szQuery[256], szQueryTemplate[256];
 612    DWORD i;
 613
 614    MetaDataReadStr(_T("IDataTableCreationCommand"), szQueryTemplate, 255, _T(""));
 615    _sntprintf(szQuery, 256, szQueryTemplate, nodeId);
 616    if (!SQLQuery(szQuery))
 617                 return FALSE;
 618
 619    for(i = 0; i < 10; i++)
 620    {
 621       _sntprintf(szQuery, 256, _T("IDataIndexCreationCommand_%d"), i);
 622       MetaDataReadStr(szQuery, szQueryTemplate, 255, _T(""));
 623       if (szQueryTemplate[0] != 0)
 624       {
 625          _sntprintf(szQuery, 256, szQueryTemplate, nodeId, nodeId);
 626          if (!SQLQuery(szQuery))
 627                                 return FALSE;
 628       }
 629    }
 630
 631         return TRUE;
 632 }
 633
 634 /**
 635  * Create tdata_xx table - pre V281 version
 636  */
 637 BOOL CreateTDataTable_preV281(DWORD nodeId)
 638 {
 639    TCHAR szQuery[256], szQueryTemplate[256];
 640    DWORD i;
 641
 642    MetaDataReadStr(_T("TDataTableCreationCommand"), szQueryTemplate, 255, _T(""));
 643    _sntprintf(szQuery, 256, szQueryTemplate, nodeId);
 644    if (!SQLQuery(szQuery))
 645                 return FALSE;
 646
 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
 662 /**
 663  * Create tdata_xx table
 664  */
 665 BOOL CreateTDataTable(DWORD nodeId)
 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       {
 676          _sntprintf(szQuery, 256, szQueryTemplate, nodeId, nodeId);
 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
 697 /**
 698  * Check collected data
 699  */
 700 static void CheckIData()
 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..."));
 709
 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);
 718                         _sntprintf(query, 1024, _T("SELECT count(*) FROM idata_%d WHERE idata_timestamp>") TIME_T_FMT, nodeId, TIME_T_FCAST(now));
 719                         hResult = SQLSelect(query);
 720                         if (hResult != NULL)
 721                         {
 722                                 if (DBGetFieldLong(hResult, 0, 0) > 0)
 723                                 {
 724                                         m_iNumErrors++;
 725                                         if (GetYesNo(_T("\rFound collected data for node [%d] with timestamp in the future. Delete them?"), nodeId))
 726                                         {
 727                                                 _sntprintf(query, 1024, _T("DELETE FROM idata_%d WHERE idata_timestamp>") TIME_T_FMT, nodeId, TIME_T_FCAST(now));
 728                                                 if (SQLQuery(query))
 729                                                         m_iNumFixes++;
 730                                         }
 731                                 }
 732                                 DBFreeResult(hResult);
 733                         }
 734                 }
 735                 DBFreeResult(hResultNodes);
 736         }
 737
 738         _sntprintf(query, 1024, _T("SELECT count(*) FROM raw_dci_values WHERE last_poll_time>") TIME_T_FMT, TIME_T_FCAST(now));
 739         hResult = SQLSelect(query);
 740         if (hResult != NULL)
 741         {
 742                 if (DBGetFieldLong(hResult, 0, 0) > 0)
 743                 {
 744                         m_iNumErrors++;
 745                         if (GetYesNo(_T("\rFound DCIs with last poll timestamp in the future. Fix it?")))
 746                         {
 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));
 748                                 if (SQLQuery(query))
 749                                         m_iNumFixes++;
 750                         }
 751                 }
 752                 DBFreeResult(hResult);
 753         }
 754
 755         EndStage();
 756 }
 757
 758 /**
 759  * Check if given data table exist
 760  */
 761 bool IsDataTableExist(const TCHAR *format, UINT32 id)
 762 {
 763    TCHAR table[256];
 764    _sntprintf(table, 256, format, id);
 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;
 771 }
 772
 773 /**
 774  * Check data tables for given o bject class
 775  */
 776 static 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
 800          if (!IsDataTableExist(_T("tdata_%d"), id))
 801          {
 802                                 m_iNumErrors++;
 803                                 if (GetYesNo(_T("\rData collection table (TDATA) for %s [%d] not found. Create? (Y/N) "), classDescr, id))
 804                                 {
 805                                         if (CreateTDataTable(id))
 806                                                 m_iNumFixes++;
 807                                 }
 808          }
 809       }
 810       DBFreeResult(hResult);
 811    }
 812 }
 813
 814 /**
 815  * Check data tables
 816  */
 817 static 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"));
 824    CheckDataTablesForClass(_T("access_points"), _T("access point"));
 825    CheckDataTablesForClass(_T("chassis"), _T("chassis"));
 826
 827         EndStage();
 828 }
 829
 830 /**
 831  * Check template to node mapping
 832  */
 833 static void CheckTemplateNodeMapping()
 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
 850          if (!IsDatabaseRecordExist(_T("nodes"), _T("id"), dwNodeId) &&
 851              !IsDatabaseRecordExist(_T("clusters"), _T("id"), dwNodeId) &&
 852              !IsDatabaseRecordExist(_T("mobile_devices"), _T("id"), dwNodeId))
 853          {
 854             m_iNumErrors++;
 855                                 GetObjectName(dwTemplateId, name);
 856             if (GetYesNo(_T("\rTemplate %d [%s] mapped to non-existent node %d. Delete this mapping?"), dwTemplateId, name, dwNodeId))
 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
 870 /**
 871  * Check network map links
 872  */
 873 static 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
 912 /**
 913  * Check database for errors
 914  */
 915 void CheckDatabase()
 916 {
 917    DB_RESULT hResult;
 918    LONG iVersion = 0;
 919    BOOL bCompleted = FALSE;
 920
 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"));
 925
 926    // Get database format version
 927    iVersion = DBGetSchemaVersion(g_hCoreDB);
 928    if ((iVersion < DB_FORMAT_VERSION) && !g_checkDataTablesOnly)
 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    {
 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"),
 937                 iVersion, DB_FORMAT_VERSION);
 938
 939    }
 940    else
 941    {
 942       TCHAR szLockStatus[MAX_DB_STRING], szLockInfo[MAX_DB_STRING];
 943       BOOL bLocked = FALSE;
 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          {
 951             DBGetField(hResult, 0, 0, szLockStatus, MAX_DB_STRING);
 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                {
 964                   DBGetField(hResult, 0, 0, szLockInfo, MAX_DB_STRING);
 965                   DecodeSQLString(szLockInfo);
 966                }
 967                DBFreeResult(hResult);
 968             }
 969          }
 970
 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                         }
 982
 983                         if (!bLocked)
 984                         {
 985                                 DBBegin(g_hCoreDB);
 986
 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             }
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                         }
1041       }
1042                 else
1043                 {
1044                         _tprintf(_T("Unable to get database lock status\n"));
1045                 }
1046    }
1047
1048    _tprintf(_T("Database check %s\n"), bCompleted ? _T("completed") : _T("aborted"));
1049 }