src/server/tools/nxdbmgr/migrate.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: migrate.cpp
  20 **
  21 **/
  22
  23 #include "nxdbmgr.h"
  24
  25 /**
  26  * Tables to migrate
  27  */
  28 extern const TCHAR *g_tables[];
  29
  30 /**
  31  * Source config
  32  */
  33 static TCHAR s_dbDriver[MAX_PATH] = _T("");
  34 static TCHAR s_dbDrvParams[MAX_PATH] = _T("");
  35 static TCHAR s_dbServer[MAX_PATH] = _T("127.0.0.1");
  36 static TCHAR s_dbLogin[MAX_DB_LOGIN] = _T("netxms");
  37 static TCHAR s_dbPassword[MAX_PASSWORD] = _T("");
  38 static TCHAR s_dbName[MAX_DB_NAME] = _T("netxms_db");
  39 static TCHAR s_dbSchema[MAX_DB_NAME] = _T("");
  40 static NX_CFG_TEMPLATE m_cfgTemplate[] =
  41 {
  42    { _T("DBDriver"), CT_STRING, 0, 0, MAX_PATH, 0, s_dbDriver },
  43    { _T("DBDrvParams"), CT_STRING, 0, 0, MAX_PATH, 0, s_dbDrvParams },
  44    { _T("DBLogin"), CT_STRING, 0, 0, MAX_DB_LOGIN, 0, s_dbLogin },
  45    { _T("DBName"), CT_STRING, 0, 0, MAX_DB_NAME, 0, s_dbName },
  46    { _T("DBPassword"), CT_STRING, 0, 0, MAX_PASSWORD, 0, s_dbPassword },
  47    { _T("DBEncryptedPassword"), CT_STRING, 0, 0, MAX_PASSWORD, 0, s_dbPassword },
  48    { _T("DBSchema"), CT_STRING, 0, 0, MAX_PATH, 0, s_dbSchema },
  49    { _T("DBServer"), CT_STRING, 0, 0, MAX_PATH, 0, s_dbServer },
  50    { _T(""), CT_END_OF_LIST, 0, 0, 0, 0, NULL }
  51 };
  52
  53 /**
  54  * Source connection
  55  */
  56 static DB_DRIVER s_driver = NULL;
  57 static DB_HANDLE s_hdbSource = NULL;
  58 static int s_sourceSyntax = DB_SYNTAX_UNKNOWN;
  59
  60 /**
  61  * Connect to source database
  62  */
  63 static bool ConnectToSource()
  64 {
  65         s_driver = DBLoadDriver(s_dbDriver, s_dbDrvParams, false, NULL, NULL);
  66         if (s_driver == NULL)
  67    {
  68       _tprintf(_T("Unable to load and initialize database driver \"%s\"\n"), s_dbDriver);
  69       return false;
  70    }
  71    WriteToTerminalEx(_T("Database driver \x1b[1m%s\x1b[0m loaded\n"), s_dbDriver);
  72
  73         TCHAR errorText[DBDRV_MAX_ERROR_TEXT];
  74    s_hdbSource = DBConnect(s_driver, s_dbServer, s_dbName, s_dbLogin, s_dbPassword, s_dbSchema, errorText);
  75    if (s_hdbSource == NULL)
  76    {
  77                 _tprintf(_T("Unable to connect to database %s@%s as %s: %s\n"), s_dbName, s_dbServer, s_dbLogin, errorText);
  78       return false;
  79    }
  80    WriteToTerminalEx(_T("Connected to source database\n"));
  81
  82    // Get database syntax
  83         s_sourceSyntax = DBGetSyntax(s_hdbSource);
  84         if (s_sourceSyntax == DB_SYNTAX_UNKNOWN)
  85         {
  86       _tprintf(_T("Unable to determine source database syntax\n"));
  87       return false;
  88    }
  89
  90    // Check source schema version
  91    int version = DBGetSchemaVersion(s_hdbSource);
  92    if (version < DB_FORMAT_VERSION)
  93    {
  94       _tprintf(_T("Source database has format version %d, this tool is compiled for version %d.\nUse \"upgrade\" command to upgrade source database first.\n"),
  95                version, DB_FORMAT_VERSION);
  96       return false;
  97    }
  98    if (version > DB_FORMAT_VERSION)
  99    {
 100       _tprintf(_T("Source database has format version %d, this tool is compiled for version %d.\n")
 101                _T("You need to upgrade your server before using this database.\n"),
 102                version, DB_FORMAT_VERSION);
 103       return false;
 104    }
 105
 106    return true;
 107 }
 108
 109 /**
 110  * Migrate single database table
 111  */
 112 static bool MigrateTable(const TCHAR *table)
 113 {
 114         WriteToTerminalEx(_T("Migrating table \x1b[1m%s\x1b[0m\n"), table);
 115
 116         if (!DBBegin(g_hCoreDB))
 117         {
 118                 _tprintf(_T("ERROR: unable to start transaction in target database\n"));
 119       return false;
 120    }
 121
 122    bool success = false;
 123    TCHAR buffer[256], errorText[DBDRV_MAX_ERROR_TEXT];
 124    _sntprintf(buffer, 256, _T("SELECT * FROM %s"), table);
 125    DB_UNBUFFERED_RESULT hResult = DBSelectUnbufferedEx(s_hdbSource, buffer, errorText);
 126    if (hResult == NULL)
 127    {
 128                 _tprintf(_T("ERROR: unable to read data from source table (%s)\n"), errorText);
 129       DBRollback(g_hCoreDB);
 130       return false;
 131    }
 132
 133    // build INSERT query
 134    String query = _T("INSERT INTO ");
 135    query += table;
 136    query += _T(" (");
 137         int columnCount = DBGetColumnCount(hResult);
 138         for(int i = 0; i < columnCount; i++)
 139         {
 140                 DBGetColumnName(hResult, i, buffer, 256);
 141                 query += buffer;
 142                 query += _T(",");
 143         }
 144         query.shrink();
 145         query += _T(") VALUES (?");
 146         for(int i = 1; i < columnCount; i++)
 147       query += _T(",?");
 148    query += _T(")");
 149
 150    DB_STATEMENT hStmt = DBPrepareEx(g_hCoreDB, query, errorText);
 151    if (hStmt != NULL)
 152    {
 153       success = true;
 154       int rows = 0, totalRows = 0;
 155       while(DBFetch(hResult))
 156       {
 157                         for(int i = 0; i < columnCount; i++)
 158          {
 159             DBBind(hStmt, i + 1, DB_SQLTYPE_VARCHAR, DBGetField(hResult, i, NULL, 0), DB_BIND_DYNAMIC);
 160          }
 161          if (!SQLExecute(hStmt))
 162          {
 163             _tprintf(_T("Failed input record:\n"));
 164             for(int i = 0; i < columnCount; i++)
 165             {
 166                DBGetColumnName(hResult, i, buffer, 256);
 167                TCHAR *value = DBGetField(hResult, i, NULL, 0);
 168                _tprintf(_T("   %s = \"%s\"\n"), buffer, CHECK_NULL(value));
 169                safe_free(value);
 170             }
 171             success = false;
 172             break;
 173          }
 174
 175          rows++;
 176          if (rows >= g_migrationTxnSize)
 177          {
 178             rows = 0;
 179             DBCommit(g_hCoreDB);
 180             DBBegin(g_hCoreDB);
 181          }
 182
 183          totalRows++;
 184          if ((totalRows & 0xFF) == 0)
 185          {
 186             _tprintf(_T("%8d\r"), totalRows);
 187             fflush(stdout);
 188          }
 189       }
 190
 191       if (success)
 192          DBCommit(g_hCoreDB);
 193       else
 194          DBRollback(g_hCoreDB);
 195       DBFreeStatement(hStmt);
 196    }
 197    else
 198    {
 199                 _tprintf(_T("ERROR: cannot prepare INSERT statement (%s)\n"), errorText);
 200       DBRollback(g_hCoreDB);
 201    }
 202    DBFreeResult(hResult);
 203         return success;
 204 }
 205
 206 /**
 207  * Migrate data tables
 208  */
 209 static bool MigrateDataTables()
 210 {
 211         int i, count;
 212         TCHAR buffer[1024];
 213
 214         DB_RESULT hResult = SQLSelect(_T("SELECT id FROM nodes"));
 215         if (hResult == NULL)
 216                 return FALSE;
 217
 218         // Create and import idata_xx and tdata_xx tables for each node in "nodes" table
 219         count = DBGetNumRows(hResult);
 220         for(i = 0; i < count; i++)
 221         {
 222                 DWORD id = DBGetFieldULong(hResult, i, 0);
 223       if (!g_dataOnlyMigration)
 224       {
 225                    if (!CreateIDataTable(id))
 226                            break;       // Failed to create idata_xx table
 227       }
 228
 229       if (!g_skipDataMigration)
 230       {
 231                    _sntprintf(buffer, 1024, _T("idata_%d"), id);
 232                    if (!MigrateTable(buffer))
 233                            break;
 234       }
 235
 236       if (!g_dataOnlyMigration)
 237       {
 238          if (!CreateTDataTable(id))
 239                            break;       // Failed to create tdata tables
 240       }
 241
 242       if (!g_skipDataMigration)
 243       {
 244                    _sntprintf(buffer, 1024, _T("tdata_%d"), id);
 245                    if (!MigrateTable(buffer))
 246                            break;
 247       }
 248         }
 249
 250         DBFreeResult(hResult);
 251         return i == count;
 252 }
 253
 254 /**
 255  * Migrate database
 256  */
 257 void MigrateDatabase(const TCHAR *sourceConfig)
 258 {
 259    bool success = false;
 260
 261    // Load source config
 262         Config *config = new Config();
 263         if (!config->loadIniConfig(sourceConfig, _T("server")) || !config->parseTemplate(_T("server"), m_cfgTemplate))
 264    {
 265       _tprintf(_T("Error loading source configuration from %s\n"), sourceConfig);
 266       goto cleanup;
 267    }
 268
 269         // Decrypt password
 270    DecryptPassword(s_dbLogin, s_dbPassword, s_dbPassword, MAX_PASSWORD);
 271
 272    if (!ConnectToSource())
 273       goto cleanup;
 274
 275    if (!g_dataOnlyMigration)
 276    {
 277            if (!ClearDatabase(true))
 278                    goto cleanup;
 279
 280            // Migrate tables
 281            for(int i = 0; g_tables[i] != NULL; i++)
 282            {
 283                    if (!MigrateTable(g_tables[i]))
 284                            goto cleanup;
 285            }
 286    }
 287
 288    if (!g_skipDataSchemaMigration)
 289    {
 290       if (!MigrateDataTables())
 291          goto cleanup;
 292    }
 293
 294         success = true;
 295
 296 cleanup:
 297    if (s_hdbSource != NULL)
 298       DBDisconnect(s_hdbSource);
 299    if (s_driver != NULL)
 300       DBUnloadDriver(s_driver);
 301         delete config;
 302         _tprintf(success ? _T("Database migration complete.\n") : _T("Database migration failed.\n"));
 303 }