implemented tdata tables conversion to new format
[public/netxms.git] / src / server / tools / nxdbmgr / migrate.cpp
CommitLineData
07ca7d19 1/*
84ee0f9c 2** nxdbmgr - NetXMS database manager
63604cda 3** Copyright (C) 2004-2016 Victor Kirhenshtein
84ee0f9c
VK
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 */
28extern const TCHAR *g_tables[];
29
30/**
31 * Source config
32 */
84ee0f9c
VK
33static TCHAR s_dbDriver[MAX_PATH] = _T("");
34static TCHAR s_dbDrvParams[MAX_PATH] = _T("");
35static TCHAR s_dbServer[MAX_PATH] = _T("127.0.0.1");
36static TCHAR s_dbLogin[MAX_DB_LOGIN] = _T("netxms");
07ca7d19 37static TCHAR s_dbPassword[MAX_PASSWORD] = _T("");
84ee0f9c
VK
38static TCHAR s_dbName[MAX_DB_NAME] = _T("netxms_db");
39static TCHAR s_dbSchema[MAX_DB_NAME] = _T("");
40static 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 },
84ee0f9c
VK
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 },
07ca7d19 46 { _T("DBPassword"), CT_STRING, 0, 0, MAX_PASSWORD, 0, s_dbPassword },
47 { _T("DBEncryptedPassword"), CT_STRING, 0, 0, MAX_PASSWORD, 0, s_dbPassword },
84ee0f9c
VK
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 */
56static DB_DRIVER s_driver = NULL;
57static DB_HANDLE s_hdbSource = NULL;
58static int s_sourceSyntax = DB_SYNTAX_UNKNOWN;
59
60/**
61 * Connect to source database
62 */
63static 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 */
112static 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);
f17cf019 125 DB_UNBUFFERED_RESULT hResult = DBSelectUnbufferedEx(s_hdbSource, buffer, errorText);
84ee0f9c
VK
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(" (");
f17cf019 137 int columnCount = DBGetColumnCount(hResult);
84ee0f9c
VK
138 for(int i = 0; i < columnCount; i++)
139 {
f17cf019 140 DBGetColumnName(hResult, i, buffer, 256);
84ee0f9c
VK
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;
971a00c1 154 int rows = 0, totalRows = 0;
84ee0f9c
VK
155 while(DBFetch(hResult))
156 {
157 for(int i = 0; i < columnCount; i++)
158 {
f17cf019 159 DBBind(hStmt, i + 1, DB_SQLTYPE_VARCHAR, DBGetField(hResult, i, NULL, 0), DB_BIND_DYNAMIC);
84ee0f9c
VK
160 }
161 if (!SQLExecute(hStmt))
162 {
6fde8617 163 _tprintf(_T("Failed input record:\n"));
9b4a2a0e
VK
164 for(int i = 0; i < columnCount; i++)
165 {
f17cf019
VK
166 DBGetColumnName(hResult, i, buffer, 256);
167 TCHAR *value = DBGetField(hResult, i, NULL, 0);
9b4a2a0e
VK
168 _tprintf(_T(" %s = \"%s\"\n"), buffer, CHECK_NULL(value));
169 safe_free(value);
170 }
84ee0f9c
VK
171 success = false;
172 break;
173 }
174
175 rows++;
09f1c9bf 176 if (rows >= g_migrationTxnSize)
84ee0f9c
VK
177 {
178 rows = 0;
179 DBCommit(g_hCoreDB);
180 DBBegin(g_hCoreDB);
181 }
971a00c1
VK
182
183 totalRows++;
184 if ((totalRows & 0xFF) == 0)
185 {
186 _tprintf(_T("%8d\r"), totalRows);
187 fflush(stdout);
188 }
84ee0f9c
VK
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 }
f17cf019 202 DBFreeResult(hResult);
84ee0f9c
VK
203 return success;
204}
205
206/**
971a00c1 207 * Migrate data tables
84ee0f9c 208 */
971a00c1 209static bool MigrateDataTables()
84ee0f9c 210{
971a00c1
VK
211 int i, count;
212 TCHAR buffer[1024];
84ee0f9c 213
971a00c1 214 DB_RESULT hResult = SQLSelect(_T("SELECT id FROM nodes"));
84ee0f9c
VK
215 if (hResult == NULL)
216 return FALSE;
217
971a00c1 218 // Create and import idata_xx and tdata_xx tables for each node in "nodes" table
84ee0f9c
VK
219 count = DBGetNumRows(hResult);
220 for(i = 0; i < count; i++)
221 {
971a00c1 222 DWORD id = DBGetFieldULong(hResult, i, 0);
ffaafdce
VK
223 if (!g_dataOnlyMigration)
224 {
225 if (!CreateIDataTable(id))
226 break; // Failed to create idata_xx table
227 }
84ee0f9c 228
ffaafdce
VK
229 if (!g_skipDataMigration)
230 {
231 _sntprintf(buffer, 1024, _T("idata_%d"), id);
232 if (!MigrateTable(buffer))
233 break;
234 }
971a00c1 235
ffaafdce
VK
236 if (!g_dataOnlyMigration)
237 {
63604cda 238 if (!CreateTDataTable(id))
ffaafdce
VK
239 break; // Failed to create tdata tables
240 }
971a00c1 241
ffaafdce
VK
242 if (!g_skipDataMigration)
243 {
244 _sntprintf(buffer, 1024, _T("tdata_%d"), id);
245 if (!MigrateTable(buffer))
246 break;
ffaafdce 247 }
84ee0f9c
VK
248 }
249
84ee0f9c
VK
250 DBFreeResult(hResult);
251 return i == count;
252}
253
254/**
255 * Migrate database
256 */
257void 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
07ca7d19 270 DecryptPassword(s_dbLogin, s_dbPassword, s_dbPassword, MAX_PASSWORD);
84ee0f9c
VK
271
272 if (!ConnectToSource())
273 goto cleanup;
274
ffaafdce
VK
275 if (!g_dataOnlyMigration)
276 {
9b4a2a0e 277 if (!ClearDatabase(true))
ffaafdce
VK
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 }
84ee0f9c 287
09f1c9bf
VK
288 if (!g_skipDataSchemaMigration)
289 {
290 if (!MigrateDataTables())
291 goto cleanup;
292 }
84ee0f9c
VK
293
294 success = true;
295
296cleanup:
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}