schema-related information (like schema version, DB syntax, etc.) moved from "config...
authorVictor Kirhenshtein <victor@netxms.org>
Mon, 16 Feb 2009 23:29:20 +0000 (23:29 +0000)
committerVictor Kirhenshtein <victor@netxms.org>
Mon, 16 Feb 2009 23:29:20 +0000 (23:29 +0000)
24 files changed:
doc/internal/db_format_change.txt
include/netxmsdb.h
sql/Makefile.am
sql/Makefile.w32
sql/dbinit.in
sql/metadata.in [new file with mode: 0644]
sql/schema.in
sql/setup.in
src/server/core/config.cpp
src/server/core/dcitem.cpp
src/server/core/hk.cpp
src/server/core/main.cpp
src/server/core/objects.cpp
src/server/core/session.cpp
src/server/include/nms_core.h
src/server/include/nxsrvapi.h
src/server/libnxsrv/db.cpp
src/server/tools/nxdbmgr/check.cpp
src/server/tools/nxdbmgr/clear.cpp
src/server/tools/nxdbmgr/export.cpp
src/server/tools/nxdbmgr/import.cpp
src/server/tools/nxdbmgr/nxdbmgr.cpp
src/server/tools/nxdbmgr/nxdbmgr.h
src/server/tools/nxdbmgr/upgrade.cpp

index 9a6abc6..4b3473f 100644 (file)
@@ -1,3 +1,20 @@
+*************
+* 86 ==> 87 *
+*************
+
+- New table "metadata" added for storing data like schema version,
+  database syntax, etc.
+       CREATE TABLE metadata
+       (
+               var_name varchar(63) not null,
+               var_value varchar(255) not null,
+               PRIMARY KEY(var_name)
+       ) TABLE_TYPE;
+- Configuration parameters DBFormatVersion, DBSyntax,
+  IDataTableCreationCommand, IDataIndexCreationCommand_* moved to
+  "metadata" table
+
+
 *************
 * 85 ==> 86 *
 *************
index adc4ace..4de65f3 100644 (file)
@@ -24,6 +24,6 @@
 #ifndef _netxmsdb_h
 #define _netxmsdb_h
 
-#define DB_FORMAT_VERSION   86
+#define DB_FORMAT_VERSION   87
 
 #endif
index 718290c..064d4f0 100644 (file)
@@ -16,7 +16,7 @@ sql_DATA = dbinit_mysql.sql \
            dbschema_sqlite.sql
 
 ROOT_FILE = dbinit.in
-SOURCE = $(ROOT_FILE) database.in schema.in setup.in events.in images.in policy.in objtools.in scripts.in maps.in
+SOURCE = $(ROOT_FILE) database.in schema.in metadata.in setup.in events.in images.in policy.in objtools.in scripts.in maps.in
 HEADER_FILES = ../include/netxmsdb.h ../include/nxevent.h ../include/nximage.h ../src/server/include/nms_locks.h
 COMMAND = $(CPP) -x c -P -Wundef -D_NETXMS_DB_SCHEMA_ -I@top_srcdir@/include -I@top_srcdir@/src/server/include
 SQUEEZE = perl -n -e "if (! /^[\s\t\n]*$$/) { print; }"
@@ -59,4 +59,5 @@ clean:
 EXTRA_DIST = \
        $(sql_DATA) \
        dbinit.in database.in events.in images.in \
-       policy.in schema.in setup.in traps.in objtools.in scripts.in maps.in
+       policy.in schema.in setup.in traps.in objtools.in scripts.in \
+       metadata.in maps.in
index 14fc580..8116fa1 100644 (file)
@@ -16,7 +16,7 @@ sql_SCRIPTS = dbinit_mysql.sql \
               dbschema_sqlite.sql
 
 ROOT_FILE = dbinit.in
-SOURCE = $(ROOT_FILE) database.in schema.in setup.in events.in images.in maps.in policy.in objtools.in scripts.in maps.in
+SOURCE = $(ROOT_FILE) database.in schema.in metadata.in setup.in events.in images.in maps.in policy.in objtools.in scripts.in maps.in
 HEADERS = ../include/netxmsdb.h ../include/nxevent.h ../include/nximage.h ../src/server/include/nms_locks.h
 COMMAND = $(CPP) -P -Wundef -D_NETXMS_DB_SCHEMA_ -I../include -I../src/server/include
 SQUEEZE = perl -e "while (<>) { if (! /^[\s\t\n]*$$/) { print; } }"
index 2659553..a3391b4 100644 (file)
@@ -24,13 +24,9 @@ BEGIN TRANSACTION;
 
 // Actual SQL queries
 #include "schema.in"
+#include "metadata.in"
 
-#ifdef SCHEMA_ONLY
-INSERT INTO config (var_name,var_value,is_visible,need_server_restart)
-       VALUES ('DBFormatVersion',DB_FORMAT_VERSION,0,1);
-INSERT INTO config (var_name,var_value,is_visible,need_server_restart)
-       VALUES ('DBSyntax',DB_SYNTAX,0,1);
-#else
+#ifndef SCHEMA_ONLY
 #include "setup.in"
 #include "events.in"
 #include "images.in"
diff --git a/sql/metadata.in b/sql/metadata.in
new file mode 100644 (file)
index 0000000..3ab00a1
--- /dev/null
@@ -0,0 +1,24 @@
+/*
+** Metadata
+**
+** ex: syntax=sql
+*/
+
+INSERT INTO metadata (var_name,var_value)
+       VALUES ('SchemaVersion',DB_FORMAT_VERSION);
+INSERT INTO metadata (var_name,var_value)
+       VALUES ('Syntax',DB_SYNTAX);
+INSERT INTO metadata (var_name,var_value)
+       VALUES ('IDataTableCreationCommand','CREATE TABLE idata_%d (item_id integer not null,idata_timestamp integer not null,idata_value varchar(255) not null)');
+INSERT INTO metadata (var_name,var_value)
+       VALUES ('IDataIndexCreationCommand_0','CREATE INDEX idx_idata_%d_item_id ON idata_%d(item_id)');
+#if defined(DB_MYSQL)
+INSERT INTO metadata (var_name,var_value)
+       VALUES ('IDataIndexCreationCommand_1','CREATE INDEX idx_idata_%d_id_timestamp ON idata_%d(item_id,idata_timestamp)');
+#elif defined(DB_POSTGRESQL)
+INSERT INTO metadata (var_name,var_value)
+       VALUES ('IDataIndexCreationCommand_1','CREATE INDEX idx_idata_%d_timestamp_id ON idata_%d(idata_timestamp,item_id)');
+#elif defined(DB_MSSQL)
+INSERT INTO metadata (var_name,var_value)
+       VALUES ('IDataIndexCreationCommand_1','CREATE CLUSTERED INDEX idx_idata_%d_id_timestamp ON idata_%d(item_id,idata_timestamp)');
+#endif
index 865495e..fabdb86 100644 (file)
@@ -5,6 +5,18 @@
 */
 
 
+/*
+** Metadata
+*/
+
+CREATE TABLE metadata
+(
+       var_name varchar(63) not null,
+       var_value varchar(255) not null,
+       PRIMARY KEY(var_name)
+) TABLE_TYPE;
+
+
 /*
 ** System configuration table
 */
index 39e65a0..3d0749e 100644 (file)
@@ -4,10 +4,6 @@
 ** ex: syntax=sql
 */
 
-INSERT INTO config (var_name,var_value,is_visible,need_server_restart)
-       VALUES ('DBFormatVersion',DB_FORMAT_VERSION,0,1);
-INSERT INTO config (var_name,var_value,is_visible,need_server_restart)
-       VALUES ('DBSyntax',DB_SYNTAX,0,1);
 INSERT INTO config (var_name,var_value,is_visible,need_server_restart)
        VALUES ('DBLockStatus','UNLOCKED',0,1);
 INSERT INTO config (var_name,var_value,is_visible,need_server_restart)
@@ -48,20 +44,6 @@ INSERT INTO config (var_name,var_value,is_visible,need_server_restart)
        VALUES ('NumberOfRoutingTablePollers','5',1,1);
 INSERT INTO config (var_name,var_value,is_visible,need_server_restart)
        VALUES ('NumberOfConditionPollers','10',1,1);
-INSERT INTO config (var_name,var_value,is_visible,need_server_restart)
-       VALUES ('IDataTableCreationCommand','CREATE TABLE idata_%d (item_id integer not null,idata_timestamp integer not null,idata_value varchar(255) not null)',0,1);
-INSERT INTO config (var_name,var_value,is_visible,need_server_restart)
-       VALUES ('IDataIndexCreationCommand_0','CREATE INDEX idx_idata_%d_item_id ON idata_%d(item_id)',0,1);
-#if defined(DB_MYSQL)
-INSERT INTO config (var_name,var_value,is_visible,need_server_restart)
-       VALUES ('IDataIndexCreationCommand_1','CREATE INDEX idx_idata_%d_id_timestamp ON idata_%d(item_id,idata_timestamp)',0,1);
-#elif defined(DB_POSTGRESQL)
-INSERT INTO config (var_name,var_value,is_visible,need_server_restart)
-       VALUES ('IDataIndexCreationCommand_1','CREATE INDEX idx_idata_%d_timestamp_id ON idata_%d(idata_timestamp,item_id)',0,1);
-#elif defined(DB_MSSQL)
-INSERT INTO config (var_name,var_value,is_visible,need_server_restart)
-       VALUES ('IDataIndexCreationCommand_1','CREATE CLUSTERED INDEX idx_idata_%d_id_timestamp ON idata_%d(item_id,idata_timestamp)',0,1);
-#endif
 INSERT INTO config (var_name,var_value,is_visible,need_server_restart)
        VALUES ('RunNetworkDiscovery','0',1,1);
 INSERT INTO config (var_name,var_value,is_visible,need_server_restart)
index 3a3acb8..79254bf 100644 (file)
@@ -97,6 +97,37 @@ BOOL NXCORE_EXPORTABLE LoadConfig(void)
 }
 
 
+//
+// Read string value from metadata table
+//
+
+BOOL NXCORE_EXPORTABLE MetaDataReadStr(const TCHAR *szVar, TCHAR *szBuffer, int iBufSize, const TCHAR *szDefault)
+{
+   DB_RESULT hResult;
+   TCHAR szQuery[256];
+   BOOL bSuccess = FALSE;
+
+   nx_strncpy(szBuffer, szDefault, iBufSize);
+   if (_tcslen(szVar) > 127)
+      return FALSE;
+
+   _sntprintf(szQuery, 256, _T("SELECT var_value FROM metadata WHERE var_name='%s'"), szVar);
+   hResult = DBSelect(g_hCoreDB, szQuery);
+   if (hResult == 0)
+      return FALSE;
+
+   if (DBGetNumRows(hResult) > 0)
+   {
+      DBGetField(hResult, 0, 0, szBuffer, iBufSize);
+      DecodeSQLString(szBuffer);
+      bSuccess = TRUE;
+   }
+
+   DBFreeResult(hResult);
+   return bSuccess;
+}
+
+
 //
 // Read string value from configuration table
 //
index 8b863ae..0cef3ae 100644 (file)
@@ -1180,7 +1180,7 @@ void DCItem::UpdateCacheSize(DWORD dwCondId)
          char szBuffer[MAX_DB_STRING];
          BOOL bHasData;
 
-         switch(g_dwDBSyntax)
+         switch(g_nDBSyntax)
          {
             case DB_SYNTAX_MSSQL:
                sprintf(szBuffer, "SELECT TOP %d idata_value,idata_timestamp FROM idata_%d "
index e31ac60..a029d3e 100644 (file)
@@ -183,7 +183,7 @@ THREAD_RESULT THREAD_CALL HouseKeeper(void *pArg)
       RWLockUnlock(g_rwlockNodeIndex);
 
       // Run DB-specific maintenance tasks
-      if (g_dwDBSyntax == DB_SYNTAX_PGSQL)
+      if (g_nDBSyntax == DB_SYNTAX_PGSQL)
          PGSQLMaintenance();
    }
 
index 83c4aa2..dc9ae8d 100644 (file)
@@ -114,7 +114,7 @@ DWORD g_dwConditionPollingInterval;
 DWORD g_dwPingSize;
 DWORD g_dwAuditFlags;
 char g_szDataDir[MAX_PATH];
-DWORD g_dwDBSyntax = DB_SYNTAX_GENERIC;
+int g_nDBSyntax = DB_SYNTAX_UNKNOWN;
 QWORD g_qwServerId;
 RSA *g_pServerKey = NULL;
 time_t g_tServerStartTime = 0;
@@ -450,7 +450,7 @@ BOOL NXCORE_EXPORTABLE Initialize(void)
        DbgPrintf(1, "Successfully connected to database %s@%s", g_szDbName, g_szDbServer);
 
        // Check database version
-       iDBVersion = ConfigReadInt("DBFormatVersion", 0);
+       iDBVersion = DBGetSchemaVersion(g_hCoreDB);
        if (iDBVersion != DB_FORMAT_VERSION)
        {
                nxlog_write(MSG_WRONG_DB_VERSION, EVENTLOG_ERROR_TYPE, "dd", iDBVersion, DB_FORMAT_VERSION);
@@ -458,31 +458,7 @@ BOOL NXCORE_EXPORTABLE Initialize(void)
        }
 
        // Read database syntax
-       ConfigReadStr("DBSyntax", szInfo, 256, "");
-       if (!stricmp(szInfo, "MYSQL"))
-       {
-               g_dwDBSyntax = DB_SYNTAX_MYSQL;
-       }
-       else if (!stricmp(szInfo, "PGSQL"))
-       {
-               g_dwDBSyntax = DB_SYNTAX_PGSQL;
-       }
-       else if (!stricmp(szInfo, "MSSQL"))
-       {
-               g_dwDBSyntax = DB_SYNTAX_MSSQL;
-       }
-       else if (!stricmp(szInfo, "ORACLE"))
-       {
-               g_dwDBSyntax = DB_SYNTAX_ORACLE;
-       }
-       else if (!stricmp(szInfo, "SQLITE"))
-       {
-               g_dwDBSyntax = DB_SYNTAX_SQLITE;
-       }
-       else
-       {
-               g_dwDBSyntax = DB_SYNTAX_GENERIC;
-       }
+       g_nDBSyntax = DBGetSyntax(g_hCoreDB);
 
        // Read server ID
        ConfigReadStr("ServerID", szInfo, 256, "");
index 7656b7a..89c7ec3 100644 (file)
@@ -304,14 +304,14 @@ void NetObjInsert(NetObj *pObject, BOOL bNewObject)
          char szQuery[256], szQueryTemplate[256];
          DWORD i;
 
-         ConfigReadStr("IDataTableCreationCommand", szQueryTemplate, 255, "");
+         MetaDataReadStr("IDataTableCreationCommand", szQueryTemplate, 255, "");
          sprintf(szQuery, szQueryTemplate, pObject->Id());
          DBQuery(g_hCoreDB, szQuery);
 
          for(i = 0; i < 10; i++)
          {
             sprintf(szQuery, "IDataIndexCreationCommand_%d", i);
-            ConfigReadStr(szQuery, szQueryTemplate, 255, "");
+            MetaDataReadStr(szQuery, szQueryTemplate, 255, "");
             if (szQueryTemplate[0] != 0)
             {
                sprintf(szQuery, szQueryTemplate, pObject->Id(), pObject->Id());
index 80e826d..83b21a9 100644 (file)
@@ -1876,7 +1876,7 @@ void ClientSession::SendEventLog(CSCPMessage *pRequest)
 
    dwRqId = pRequest->GetId();
    dwMaxRecords = pRequest->GetVariableLong(VID_MAX_RECORDS);
-   wRecOrder = ((g_dwDBSyntax == DB_SYNTAX_MSSQL) || (g_dwDBSyntax == DB_SYNTAX_ORACLE)) ? RECORD_ORDER_REVERSED : RECORD_ORDER_NORMAL;
+   wRecOrder = ((g_nDBSyntax == DB_SYNTAX_MSSQL) || (g_nDBSyntax == DB_SYNTAX_ORACLE)) ? RECORD_ORDER_REVERSED : RECORD_ORDER_NORMAL;
 
    // Prepare confirmation message
    msg.SetCode(CMD_REQUEST_COMPLETED);
@@ -1885,7 +1885,7 @@ void ClientSession::SendEventLog(CSCPMessage *pRequest)
    MutexLock(m_mutexSendEvents, INFINITE);
 
    // Retrieve events from database
-   switch(g_dwDBSyntax)
+   switch(g_nDBSyntax)
    {
       case DB_SYNTAX_MYSQL:
       case DB_SYNTAX_PGSQL:
@@ -3226,7 +3226,7 @@ void ClientSession::GetCollectedData(CSCPMessage *pRequest)
             iType = ((Node *)pObject)->GetItemType(dwItemId);
 //DWORD s=GetTickCount();
             // Create database-dependent query for fetching N rows
-            switch(g_dwDBSyntax)
+            switch(g_nDBSyntax)
             {
                case DB_SYNTAX_MSSQL:
                   _stprintf(szQuery, _T("SELECT TOP %d idata_timestamp,idata_value FROM idata_%d WHERE item_id=%d%s ORDER BY idata_timestamp DESC"),
@@ -6818,7 +6818,7 @@ void ClientSession::SendSyslog(CSCPMessage *pRequest)
    TCHAR szQuery[1024], szBuffer[1024];
    WORD wRecOrder;
 
-   wRecOrder = ((g_dwDBSyntax == DB_SYNTAX_MSSQL) || (g_dwDBSyntax == DB_SYNTAX_ORACLE)) ? RECORD_ORDER_REVERSED : RECORD_ORDER_NORMAL;
+   wRecOrder = ((g_nDBSyntax == DB_SYNTAX_MSSQL) || (g_nDBSyntax == DB_SYNTAX_ORACLE)) ? RECORD_ORDER_REVERSED : RECORD_ORDER_NORMAL;
    dwMaxRecords = pRequest->GetVariableLong(VID_MAX_RECORDS);
 
    // Prepare confirmation message
@@ -6828,7 +6828,7 @@ void ClientSession::SendSyslog(CSCPMessage *pRequest)
    MutexLock(m_mutexSendSyslog, INFINITE);
 
    // Retrieve events from database
-   switch(g_dwDBSyntax)
+   switch(g_nDBSyntax)
    {
       case DB_SYNTAX_MYSQL:
       case DB_SYNTAX_PGSQL:
@@ -6948,7 +6948,7 @@ void ClientSession::SendTrapLog(CSCPMessage *pRequest)
    DB_ASYNC_RESULT hResult;
    WORD wRecOrder;
 
-   wRecOrder = ((g_dwDBSyntax == DB_SYNTAX_MSSQL) || (g_dwDBSyntax == DB_SYNTAX_ORACLE)) ? RECORD_ORDER_REVERSED : RECORD_ORDER_NORMAL;
+   wRecOrder = ((g_nDBSyntax == DB_SYNTAX_MSSQL) || (g_nDBSyntax == DB_SYNTAX_ORACLE)) ? RECORD_ORDER_REVERSED : RECORD_ORDER_NORMAL;
    dwMaxRecords = pRequest->GetVariableLong(VID_MAX_RECORDS);
 
    msg.SetCode(CMD_REQUEST_COMPLETED);
@@ -6964,7 +6964,7 @@ void ClientSession::SendTrapLog(CSCPMessage *pRequest)
       MutexLock(m_mutexSendTrapLog, INFINITE);
 
       // Retrieve trap log records from database
-      switch(g_dwDBSyntax)
+      switch(g_nDBSyntax)
       {
          case DB_SYNTAX_MYSQL:
          case DB_SYNTAX_PGSQL:
index d2238c5..6be52a6 100644 (file)
@@ -117,18 +117,6 @@ typedef void * HSNMPSESSION;
 #define CHECKPOINT_SNMP_PORT  260
 
 
-//
-// Database syntax codes
-//
-
-#define DB_SYNTAX_GENERIC     0
-#define DB_SYNTAX_MSSQL       1
-#define DB_SYNTAX_MYSQL       2
-#define DB_SYNTAX_PGSQL       3
-#define DB_SYNTAX_ORACLE      4
-#define DB_SYNTAX_SQLITE      5
-
-
 //
 // Unique identifier group codes
 //
@@ -608,6 +596,8 @@ BOOL NXCORE_EXPORTABLE ConfigWriteByteArray(const TCHAR *pszVar, int *pnArray,
 TCHAR NXCORE_EXPORTABLE *ConfigReadCLOB(const TCHAR *var, const TCHAR *defValue);
 BOOL NXCORE_EXPORTABLE ConfigWriteCLOB(const TCHAR *var, const TCHAR *value, BOOL bCreate);
 
+BOOL NXCORE_EXPORTABLE MetaDataReadStr(const TCHAR *szVar, TCHAR *szBuffer, int iBufSize, const TCHAR *szDefault);
+
 BOOL NXCORE_EXPORTABLE LoadConfig(void);
 
 void NXCORE_EXPORTABLE Shutdown(void);
@@ -772,6 +762,6 @@ extern int g_nRequiredPolls;
 extern DB_HANDLE NXCORE_EXPORTABLE g_hCoreDB;
 extern Queue *g_pLazyRequestQueue;
 
-extern DWORD NXCORE_EXPORTABLE g_dwDBSyntax;
+extern int NXCORE_EXPORTABLE g_nDBSyntax;
 
 #endif   /* _nms_core_h_ */
index ae258c4..c78b67b 100644 (file)
 #define DBEVENT_QUERY_FAILED           2
 
 
+//
+// Database syntax codes
+//
+
+#define DB_SYNTAX_MYSQL    0
+#define DB_SYNTAX_PGSQL    1
+#define DB_SYNTAX_MSSQL    2
+#define DB_SYNTAX_ORACLE   3
+#define DB_SYNTAX_SQLITE   4
+#define DB_SYNTAX_UNKNOWN      -1
+
+
 //
 // Database connection structure
 //
@@ -493,6 +505,9 @@ BOOL LIBNXSRV_EXPORTABLE DBCommit(DB_HANDLE hConn);
 BOOL LIBNXSRV_EXPORTABLE DBRollback(DB_HANDLE hConn);
 void LIBNXSRV_EXPORTABLE DBUnloadDriver(void);
 
+int LIBNXSRV_EXPORTABLE DBGetSchemaVersion(DB_HANDLE conn);
+int LIBNXSRV_EXPORTABLE DBGetSyntax(DB_HANDLE conn);
+
 TCHAR LIBNXSRV_EXPORTABLE *EncodeSQLString(const TCHAR *pszIn);
 void LIBNXSRV_EXPORTABLE DecodeSQLString(TCHAR *pszStr);
 
index 1d002e7..0c399ae 100644 (file)
@@ -1068,3 +1068,122 @@ void LIBNXSRV_EXPORTABLE DecodeSQLString(TCHAR *pszStr)
    }
    pszStr[iPosOut] = 0;
 }
+
+
+//
+// Get database schema version
+// Will return 0 for unknown and -1 in case of SQL errors
+//
+
+int LIBNXSRV_EXPORTABLE DBGetSchemaVersion(DB_HANDLE conn)
+{
+       DB_RESULT hResult;
+       int version = 0;
+
+       // Read schema version from 'metadata' table, where it should
+       // be stored starting from schema version 87
+       // We ignore SQL error in this case, because table 'metadata'
+       // may not exist in old schema versions
+   hResult = DBSelect(conn, _T("SELECT var_value FROM metadata WHERE var_name='SchemaVersion'"));
+   if (hResult != NULL)
+   {
+      if (DBGetNumRows(hResult) > 0)
+         version = DBGetFieldLong(hResult, 0, 0);
+      DBFreeResult(hResult);
+   }
+
+       // If database schema version is less than 87, version number
+       // will be stored in 'config' table
+       if (version == 0)
+       {
+               hResult = DBSelect(conn, _T("SELECT var_value FROM config WHERE var_name='DBFormatVersion'"));
+               if (hResult != NULL)
+               {
+                       if (DBGetNumRows(hResult) > 0)
+                               version = DBGetFieldLong(hResult, 0, 0);
+                       DBFreeResult(hResult);
+               }
+               else
+               {
+                       version = -1;
+               }
+       }
+
+       return version;
+}
+
+
+//
+// Get database syntax
+//
+
+int LIBNXSRV_EXPORTABLE DBGetSyntax(DB_HANDLE conn)
+{
+       DB_RESULT hResult;
+       TCHAR syntaxId[256];
+       BOOL read = FALSE;
+       int syntax;
+
+   // Get database syntax
+   hResult = DBSelect(conn, _T("SELECT var_value FROM metadata WHERE var_name='Syntax'"));
+   if (hResult != NULL)
+   {
+      if (DBGetNumRows(hResult) > 0)
+      {
+         DBGetField(hResult, 0, 0, syntaxId, sizeof(syntaxId));
+                       read = TRUE;
+      }
+      else
+      {
+         _tcscpy(syntaxId, _T("UNKNOWN"));
+      }
+      DBFreeResult(hResult);
+   }
+
+       // If database schema version is less than 87, syntax
+       // will be stored in 'config' table, so try it
+       if (!read)
+       {
+               hResult = DBSelect(conn, _T("SELECT var_value FROM config WHERE var_name='DBSyntax'"));
+               if (hResult != NULL)
+               {
+                       if (DBGetNumRows(hResult) > 0)
+                       {
+                               DBGetField(hResult, 0, 0, syntaxId, sizeof(syntaxId));
+                               read = TRUE;
+                       }
+                       else
+                       {
+                               _tcscpy(syntaxId, _T("UNKNOWN"));
+                       }
+                       DBFreeResult(hResult);
+               }
+       }
+
+   if (!_tcscmp(syntaxId, _T("MYSQL")))
+   {
+      syntax = DB_SYNTAX_MYSQL;
+   }
+   else if (!_tcscmp(syntaxId, _T("PGSQL")))
+   {
+      syntax = DB_SYNTAX_PGSQL;
+   }
+   else if (!_tcscmp(syntaxId, _T("MSSQL")))
+   {
+      syntax = DB_SYNTAX_MSSQL;
+   }
+   else if (!_tcscmp(syntaxId, _T("ORACLE")))
+   {
+      syntax = DB_SYNTAX_ORACLE;
+   }
+   else if (!_tcscmp(syntaxId, _T("SQLITE")))
+   {
+      syntax = DB_SYNTAX_SQLITE;
+   }
+   else
+   {
+               syntax = DB_SYNTAX_UNKNOWN;
+   }
+
+       return syntax;
+}
index c7f5920..3e4628a 100644 (file)
@@ -673,13 +673,7 @@ void CheckDatabase(void)
    _tprintf(_T("Checking database:\n"));
 
    // Get database format version
-   hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBFormatVersion'"));
-   if (hResult != NULL)
-   {
-      if (DBGetNumRows(hResult) > 0)
-         iVersion = DBGetFieldLong(hResult, 0, 0);
-      DBFreeResult(hResult);
-   }
+   iVersion = DBGetSchemaVersion(g_hCoreDB);
    if (iVersion < DB_FORMAT_VERSION)
    {
       _tprintf(_T("Your database has format version %d, this tool is compiled for version %d.\nUse \"upgrade\" command to upgrade your database first.\n"),
index 5137ab5..e5e511e 100644 (file)
 #include "nxdbmgr.h"
 
 
+//
+// Tables to clear
+//
+
+extern TCHAR *g_tables[];
+
+
+//
+// Delete idata_xx tables
+//
+
+static BOOL DeleteIData()
+{
+       DB_RESULT hResult;
+       TCHAR query[256];
+       int i, count;
+
+       hResult = SQLSelect(_T("SELECT id FROM nodes"));
+       if (hResult != NULL)
+       {
+               count = DBGetNumRows(hResult);
+               for(i = 0; i < count; i++)
+               {
+                       _sntprintf(query, 256, _T("DROP TABLE idata_%d"), DBGetFieldULong(hResult, i, 0));
+                       if (!SQLQuery(query))
+                               if (!g_bIgnoreErrors)
+                                       return FALSE;
+               }
+               DBFreeResult(hResult);
+       }
+       else
+       {
+               if (!g_bIgnoreErrors)
+                       return FALSE;
+       }
+
+       return TRUE;
+}
+
+
+//
+// Clear tables
+//
+
+static BOOL ClearTables()
+{
+       TCHAR query[256];
+       int i;
+
+       for(i = 0; g_tables[i] != NULL; i++)
+       {
+               _sntprintf(query, 256, _T("DELETE FROM %s"), g_tables[i]);
+               if (!SQLQuery(query))
+                       if (!g_bIgnoreErrors)
+                               return FALSE;
+       }
+       return TRUE;
+}
+
+
 //
 // Clear database
 //
 
 BOOL ClearDatabase()
 {
-       printf("WARNING!!!\nThis operation will clear all configuration and collected data from database.\nAre you sure (y/N)?");
+       if (!ValidateDatabase())
+               return FALSE;
+
+       printf("\n\nWARNING!!!\nThis operation will clear all configuration and collected data from database.\nAre you sure (y/N)?");
        if (!GetYesNo())
                return FALSE;
 
-       return TRUE;
+       BOOL success = FALSE;
+
+       if (DBBegin(g_hCoreDB))
+       {
+               if (DeleteIData() &&    ClearTables())
+               {
+                       success = DBCommit(g_hCoreDB);
+                       printf(success ? "Database successfully cleared\n" : "ERROR: cannot commit transaction\n");
+               }
+               else
+               {
+                       DBRollback(g_hCoreDB);
+               }
+       }
+       else
+       {
+               printf("ERROR: cannot start transaction\n");
+       }
+
+       return success;
 }
index d50762e..8529c92 100644 (file)
 #include "sqlite3.h"
 
 
+//
+// Tables for export/import/clear
+//
+
+TCHAR *g_tables[] = 
+{
+       "config",
+       "config_clob",
+       "users",
+       "user_groups",
+       "user_group_members",
+       "user_profiles",
+       "object_properties",
+       "object_custom_attributes",
+       "zones",
+       "zone_ip_addr_list",
+       "nodes",
+       "clusters",
+       "cluster_members",
+       "cluster_sync_subnets",
+       "cluster_resources",
+       "subnets",
+       "interfaces",
+       "network_services",
+       "vpn_connectors",
+       "vpn_connector_networks",
+       "containers",
+       "conditions",
+       "cond_dci_map",
+       "templates",
+       "dct_node_map",
+       "nsmap",
+       "container_members",
+       "container_categories",
+       "acl",
+       "trusted_nodes",
+       "items",
+       "thresholds",
+       "dci_schedules",
+       "raw_dci_values",
+       "event_cfg",
+       "event_log",
+       "actions",
+       "event_groups",
+       "event_group_members",
+       "time_ranges",
+       "event_policy",
+       "policy_source_list",
+       "policy_event_list",
+       "policy_action_list",
+       "policy_situation_attr_list",
+       "policy_time_range_list",
+       "deleted_objects",
+       "alarms",
+       "alarm_notes",
+       "images",
+       "default_images",
+       "oid_to_type",
+       "snmp_trap_cfg",
+       "snmp_trap_pmap",
+       "agent_pkg",
+       "object_tools",
+       "object_tools_acl",
+       "object_tools_table_columns",
+       "syslog",
+       "script_library",
+       "snmp_trap_log",
+       "maps",
+       "map_access_lists",
+       "submaps",
+       "submap_object_positions",
+       "submap_links",
+       "agent_configs",
+       "address_lists",
+       "graphs",
+       "graph_acl",
+       "certificates",
+       "audit_log",
+       "situations",
+       "snmp_communities",
+       "web_maps",
+       NULL
+};
+
+
 //
 // Export single database table
 //
@@ -131,18 +216,10 @@ void ExportDatabase(const char *file)
        _unlink(file);
        if (sqlite3_open(file, &db) != SQLITE_OK)
        {
-               _tprintf(_T("ERROR: unable to open output file\n"));
+               printf("ERROR: unable to open output file\n");
                return;
        }
 
-       // Create table to hold export metadata
-       if (sqlite3_exec(db, "CREATE TABLE metadata (label varchar(63), value varchar)", NULL, NULL, &errmsg) != SQLITE_OK)
-       {
-               _tprintf(_T("ERROR: unable to add metadata to output file: %s\n"), errmsg);
-               sqlite3_free(errmsg);
-               goto cleanup;
-       }
-
        // Setup database schema
 #ifdef _WIN32
        HKEY hKey;
@@ -209,103 +286,15 @@ void ExportDatabase(const char *file)
        // Check that dbschema_sqlite.sql and database have the same schema version
        /* TODO */
 
-       // Clear config table in destination database
-       if (sqlite3_exec(db, "DELETE FROM config", NULL, NULL, &errmsg) != SQLITE_OK)
-       {
-               _tprintf(_T("ERROR: unable to clear config table: %s\n"), errmsg);
-               sqlite3_free(errmsg);
-               goto cleanup;
-       }
-
        // Export tables
-       static TCHAR *tables[] = 
-       {
-               "config",
-               "config_clob",
-               "users",
-               "user_groups",
-               "user_group_members",
-               "user_profiles",
-               "object_properties",
-               "object_custom_attributes",
-               "zones",
-               "zone_ip_addr_list",
-               "nodes",
-               "clusters",
-               "cluster_members",
-               "cluster_sync_subnets",
-               "cluster_resources",
-               "subnets",
-               "interfaces",
-               "network_services",
-               "vpn_connectors",
-               "vpn_connector_networks",
-               "containers",
-               "conditions",
-               "cond_dci_map",
-               "templates",
-               "dct_node_map",
-               "nsmap",
-               "container_members",
-               "container_categories",
-               "acl",
-               "trusted_nodes",
-               "items",
-               "thresholds",
-               "dci_schedules",
-               "raw_dci_values",
-               "event_cfg",
-               "event_log",
-               "actions",
-               "event_groups",
-               "event_group_members",
-               "time_ranges",
-               "event_policy",
-               "policy_source_list",
-               "policy_event_list",
-               "policy_action_list",
-               "policy_situation_attr_list",
-               "policy_time_range_list",
-               "deleted_objects",
-               "alarms",
-               "alarm_notes",
-               "images",
-               "default_images",
-               "oid_to_type",
-               "snmp_trap_cfg",
-               "snmp_trap_pmap",
-               "agent_pkg",
-               "object_tools",
-               "object_tools_acl",
-               "object_tools_table_columns",
-               "syslog",
-               "script_library",
-               "snmp_trap_log",
-               "maps",
-               "map_access_lists",
-               "submaps",
-               "submap_object_positions",
-               "submap_links",
-               "agent_configs",
-               "address_lists",
-               "graphs",
-               "graph_acl",
-               "certificates",
-               "audit_log",
-               "situations",
-               "snmp_communities",
-               "web_maps",
-               NULL
-       };
-
-       for(i = 0; tables[i] != NULL; i++)
+       for(i = 0; g_tables[i] != NULL; i++)
        {
-               if (!ExportTable(db, tables[i]))
+               if (!ExportTable(db, g_tables[i]))
                        goto cleanup;
        }
 
        // Export tables with collected DCI data
-       hResult = SQLSelect(_T("SELECT var_value FROM config WHERE var_name='IDataTableCreationCommand'"));
+       hResult = SQLSelect(_T("SELECT var_value FROM metadata WHERE var_name='IDataTableCreationCommand'"));
        if (hResult == NULL)
                goto cleanup;
        DBGetField(hResult, 0, 0, queryTemplate, 256);
index 7c62c5f..d0180b1 100644 (file)
 #include "sqlite3.h"
 
 
+//
+// Tables to import
+//
+
+extern TCHAR *g_tables[];
+
+
+//
+// Callback for import table
+//
+
+static int ImportTableCB(void *arg, int cols, char **data, char **names)
+{
+       String query;
+       int i;
+
+       query.AddFormattedString("INSERT INTO %s (", arg);
+       for(i = 0; i < cols; i++)
+       {
+               query += names[i];
+               query += ",";
+       }
+       query.Shrink();
+       query += ") VALUES (";
+       for(i = 0; i < cols; i++)
+               query.AddFormattedString("'%s',", data[i]);
+       query.Shrink();
+       query += ")";
+
+       return SQLQuery(query) ? 0 : 1;
+}
+
+
+//
+// Import single database table
+//
+
+static BOOL ImportTable(sqlite3 *db, const char *table)
+{
+       char query[256], *errmsg;
+       int rc;
+
+       snprintf(query, 256, "SELECT * FROM %s", table);
+       rc = sqlite3_exec(db, query, ImportTableCB, (void *)table, &errmsg);
+       if (rc != SQLITE_OK)
+       {
+               printf("ERROR: SQL query \"%s\" on import file failed (%s)\n", query, errmsg);
+               sqlite3_free(errmsg);
+       }
+       return rc == SQLITE_OK;
+}
+
+
+//
+// Import idata_xx tables
+//
+
+static BOOL ImportIData(sqlite3 *db)
+{
+       DB_RESULT hResult;
+       int i, count;
+       TCHAR buffer[256];
+
+       hResult = SQLSelect(_T("SELECT id FROM nodes"));
+       if (hResult == NULL)
+               return FALSE;
+
+       count = DBGetNumRows(hResult);
+       for(i = 0; i < count; i++)
+       {
+               _sntprintf(buffer, 256, _T("idata_%d"), DBGetFieldULong(hResult, i, 0));
+               if (!ImportTable(db, buffer))
+                       break;
+       }
+
+       DBFreeResult(hResult);
+       return i == count;
+}
+
+
+//
+// Callback for getting schema version
+//
+
+static int GetSchemaVersionCB(void *arg, int cols, char **data, char **names)
+{
+       *((int *)arg) = strtol(data[0], NULL, 10);
+       return 0;
+}
+
+
 //
 // Import database
 //
 
 void ImportDatabase(const char *file)
 {
-       if (!ClearDatabase())
+       sqlite3 *db;
+       char *errmsg;
+       int i;
+
+       // Open SQLite database
+       if (sqlite3_open(file, &db) != SQLITE_OK)
+       {
+               printf("ERROR: unable to open output file\n");
                return;
+       }
+
+       // Check schema version
+       int version = 0;
+       if (sqlite3_exec(db, "SELECT var_value FROM metadata WHERE var_name='SchemaVersion'", GetSchemaVersionCB, &version, &errmsg) != SQLITE_OK)
+       {
+               printf("ERROR: SQL query failed (%s)\n", errmsg);
+               sqlite3_free(errmsg);
+               goto cleanup;
+       }
+
+       if (version != DB_FORMAT_VERSION)
+       {
+               printf("ERROR: Import file was created for database format version %d, but this tool was compiled for database format version %d\n", version, DB_FORMAT_VERSION);
+               goto cleanup;
+       }
+
+       if (!ClearDatabase())
+               goto cleanup;
+
+       // Import tables
+       for(i = 0; g_tables[i] != NULL; i++)
+       {
+               if (!ImportTable(db, g_tables[i]))
+                       goto cleanup;
+       }
+       ImportIData(db);
+
+cleanup:
+       sqlite3_close(db);
 }
index 3ce7bbf..6c3a485 100644 (file)
@@ -340,13 +340,7 @@ BOOL ValidateDatabase()
    TCHAR szLockStatus[MAX_DB_STRING], szLockInfo[MAX_DB_STRING];
 
    // Get database format version
-   hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBFormatVersion'"));
-   if (hResult != NULL)
-   {
-      if (DBGetNumRows(hResult) > 0)
-         nVersion = DBGetFieldLong(hResult, 0, 0);
-      DBFreeResult(hResult);
-   }
+   nVersion = DBGetSchemaVersion(g_hCoreDB);
    if (nVersion < DB_FORMAT_VERSION)
    {
       _tprintf(_T("Your database has format version %d, this tool is compiled for version %d.\nUse \"upgrade\" command to upgrade your database first.\n"),
@@ -406,8 +400,7 @@ int main(int argc, char *argv[])
 {
    BOOL bStart = TRUE, bForce = FALSE;
    int ch;
-   TCHAR szSyntaxId[16], szConfigFile[MAX_PATH] = DEFAULT_CONFIG_FILE;
-   DB_RESULT hResult;
+   TCHAR szConfigFile[MAX_PATH] = DEFAULT_CONFIG_FILE;
 #ifdef _WIN32
    HKEY hKey;
    DWORD dwSize;
@@ -504,6 +497,7 @@ int main(int argc, char *argv[])
    if (strcmp(argv[optind], "batch") && 
        strcmp(argv[optind], "check") && 
        strcmp(argv[optind], "export") && 
+       strcmp(argv[optind], "import") && 
        strcmp(argv[optind], "reindex") &&
        strcmp(argv[optind], "upgrade") &&
        strcmp(argv[optind], "unlock") &&
@@ -512,7 +506,7 @@ int main(int argc, char *argv[])
       _tprintf(_T("Invalid command \"%s\". Type nxdbmgr -h for command line syntax.\n"), argv[optind]);
       return 1;
    }
-   if ((!strcmp(argv[optind], "init") || !strcmp(argv[optind], "batch") || !strcmp(argv[optind], "export")) && (argc - optind < 2))
+   if ((!strcmp(argv[optind], "init") || !strcmp(argv[optind], "batch") || !strcmp(argv[optind], "export") || !strcmp(argv[optind], "import")) && (argc - optind < 2))
    {
       _tprintf("Required command argument missing\n");
       return 1;
@@ -569,56 +563,15 @@ int main(int argc, char *argv[])
    else
    {
       // Get database syntax
-      hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBSyntax'"));
-      if (hResult != NULL)
-      {
-         if (DBGetNumRows(hResult) > 0)
-         {
-            DBGetField(hResult, 0, 0, szSyntaxId, sizeof(szSyntaxId));
-            DecodeSQLString(szSyntaxId);
-         }
-         else
-         {
-            _tcscpy(szSyntaxId, _T("UNKNOWN"));
-         }
-         DBFreeResult(hResult);
-      }
-      else
-      {
+               g_iSyntax = DBGetSyntax(g_hCoreDB);
+               if (g_iSyntax == DB_SYNTAX_UNKNOWN)
+               {
          _tprintf(_T("Unable to determine database syntax\n"));
          DBDisconnect(g_hCoreDB);
          DBUnloadDriver();
          return 5;
       }
 
-      if (!_tcscmp(szSyntaxId, _T("MYSQL")))
-      {
-         g_iSyntax = DB_SYNTAX_MYSQL;
-      }
-      else if (!_tcscmp(szSyntaxId, _T("PGSQL")))
-      {
-         g_iSyntax = DB_SYNTAX_PGSQL;
-      }
-      else if (!_tcscmp(szSyntaxId, _T("MSSQL")))
-      {
-         g_iSyntax = DB_SYNTAX_MSSQL;
-      }
-      else if (!_tcscmp(szSyntaxId, _T("ORACLE")))
-      {
-         g_iSyntax = DB_SYNTAX_ORACLE;
-      }
-      else if (!_tcscmp(szSyntaxId, _T("SQLITE")))
-      {
-         g_iSyntax = DB_SYNTAX_SQLITE;
-      }
-      else
-      {
-         _tprintf(_T("Unknown database syntax %s\n"), szSyntaxId);
-         DBDisconnect(g_hCoreDB);
-         DBUnloadDriver();
-         return 6;
-      }
-
       // Do requested operation
       if (!strcmp(argv[optind], "batch"))
          ExecSQLBatch(argv[optind + 1]);
index 7c940c8..cb0f07b 100644 (file)
 #include <netxmsdb.h>
 
 
-//
-// Database syntax codes
-//
-
-#define DB_SYNTAX_MYSQL    0
-#define DB_SYNTAX_PGSQL    1
-#define DB_SYNTAX_MSSQL    2
-#define DB_SYNTAX_ORACLE   3
-#define DB_SYNTAX_SQLITE   4
-
-
 //
 // Non-standard data type codes
 //
index 7e32605..1f18b2c 100644 (file)
@@ -102,6 +102,91 @@ static BOOL SetPrimaryKey(const TCHAR *table, const TCHAR *key)
 }
 
 
+//
+// Upgrade from V86 to V87
+//
+
+static BOOL MoveConfigToMetadata(const TCHAR *cfgVar, const TCHAR *mdVar)
+{
+       TCHAR query[1024], buffer[256];
+       DB_RESULT hResult;
+       BOOL success;
+
+       _sntprintf(query, 1024, _T("SELECT var_value FROM config WHERE var_name='%s'"), cfgVar);
+       hResult = SQLSelect(query);
+       if (hResult != NULL)
+       {
+               if (DBGetNumRows(hResult) > 0)
+               {
+                       DBGetField(hResult, 0, 0, buffer, 256);
+                       DecodeSQLString(buffer);
+                       _sntprintf(query, 1024, _T("INSERT INTO metadata (var_name,var_value) VALUES ('%s','%s')"),
+                                                 mdVar, buffer);
+                       DBFreeResult(hResult);
+                       success = SQLQuery(query);
+                       if (success)
+                       {
+                               _sntprintf(query, 1024, _T("DELETE FROM config WHERE var_name='%s'"), cfgVar);
+                               success = SQLQuery(query);
+                       }
+               }
+               else
+               {
+                       success = TRUE; // Variable missing in 'config' table, nothing to move
+               }
+       }
+       else
+       {
+               success = FALSE;
+       }
+       return success;
+}
+
+static BOOL H_UpgradeFromV86(void)
+{
+       if (!CreateTable(_T("CREATE TABLE metadata (")
+                             _T("var_name varchar(63) not null,")
+                                                 _T("var_value varchar(255) not null,")
+                                                 _T("PRIMARY KEY(var_name))")))
+               if (!g_bIgnoreErrors)
+                       return FALSE;
+
+       if (!MoveConfigToMetadata(_T("DBFormatVersion"), _T("SchemaVersion")))
+               if (!g_bIgnoreErrors)
+                       return FALSE;
+
+       if (!MoveConfigToMetadata(_T("DBSyntax"), _T("Syntax")))
+               if (!g_bIgnoreErrors)
+                       return FALSE;
+
+       if (!MoveConfigToMetadata(_T("IDataTableCreationCommand"), _T("IDataTableCreationCommand")))
+               if (!g_bIgnoreErrors)
+                       return FALSE;
+
+       if (!MoveConfigToMetadata(_T("IDataIndexCreationCommand_0"), _T("IDataIndexCreationCommand_0")))
+               if (!g_bIgnoreErrors)
+                       return FALSE;
+
+       if (!MoveConfigToMetadata(_T("IDataIndexCreationCommand_1"), _T("IDataIndexCreationCommand_1")))
+               if (!g_bIgnoreErrors)
+                       return FALSE;
+
+       if (!MoveConfigToMetadata(_T("IDataIndexCreationCommand_2"), _T("IDataIndexCreationCommand_2")))
+               if (!g_bIgnoreErrors)
+                       return FALSE;
+
+       if (!MoveConfigToMetadata(_T("IDataIndexCreationCommand_3"), _T("IDataIndexCreationCommand_3")))
+               if (!g_bIgnoreErrors)
+                       return FALSE;
+
+       if (!SQLQuery(_T("UPDATE metadata SET var_value='87' WHERE var_name='SchemaVersion'")))
+      if (!g_bIgnoreErrors)
+         return FALSE;
+
+   return TRUE;
+}
+
+
 //
 // Upgrade from V85 to V86
 //
@@ -3789,6 +3874,7 @@ static struct
    { 83, H_UpgradeFromV83 },
    { 84, H_UpgradeFromV84 },
        { 85, H_UpgradeFromV85 },
+       { 86, H_UpgradeFromV86 },
    { 0, NULL }
 };
 
@@ -3807,13 +3893,7 @@ void UpgradeDatabase(void)
    _tprintf(_T("Upgrading database...\n"));
 
    // Get database format version
-   hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBFormatVersion'"));
-   if (hResult != NULL)
-   {
-      if (DBGetNumRows(hResult) > 0)
-         iVersion = DBGetFieldLong(hResult, 0, 0);
-      DBFreeResult(hResult);
-   }
+       iVersion = DBGetSchemaVersion(g_hCoreDB);
    if (iVersion == DB_FORMAT_VERSION)
    {
       _tprintf(_T("Your database format is up to date\n"));