implemented tdata tables conversion to new format
[public/netxms.git] / 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 }