String class refactored; background log writer option implemented; fixed incorrect...
[public/netxms.git] / src / server / tools / nxdbmgr / export.cpp
1 /*
2 ** nxdbmgr - NetXMS database manager
3 ** Copyright (C) 2004-2013 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: export.cpp
20 **
21 **/
22
23 #include "nxdbmgr.h"
24 #include "sqlite3.h"
25
26 /**
27 * Tables to export
28 */
29 extern const TCHAR *g_tables[];
30
31 /**
32 * Escape string for SQLite
33 */
34 static TCHAR *EscapeString(const TCHAR *str)
35 {
36 int len = (int)_tcslen(str) + 3; // + two quotes and \0 at the end
37 int bufferSize = len + 128;
38 TCHAR *out = (TCHAR *)malloc(bufferSize * sizeof(TCHAR));
39 out[0] = _T('\'');
40
41 const TCHAR *src = str;
42 int outPos;
43 for(outPos = 1; *src != 0; src++)
44 {
45 if (*src == _T('\''))
46 {
47 len++;
48 if (len >= bufferSize)
49 {
50 bufferSize += 128;
51 out = (TCHAR *)realloc(out, bufferSize * sizeof(TCHAR));
52 }
53 out[outPos++] = _T('\'');
54 out[outPos++] = _T('\'');
55 }
56 else
57 {
58 out[outPos++] = *src;
59 }
60 }
61 out[outPos++] = _T('\'');
62 out[outPos++] = 0;
63
64 return out;
65 }
66
67 /**
68 * Export single database table
69 */
70 static BOOL ExportTable(sqlite3 *db, const TCHAR *name)
71 {
72 String query;
73 TCHAR buffer[256];
74 char *errmsg;
75 DB_ASYNC_RESULT hResult;
76 int i, columnCount = 0;
77 BOOL success = TRUE;
78
79 _tprintf(_T("Exporting table %s\n"), name);
80
81 if (sqlite3_exec(db, "BEGIN", NULL, NULL, &errmsg) == SQLITE_OK)
82 {
83 _sntprintf(buffer, 256, _T("SELECT * FROM %s"), name);
84
85 hResult = SQLAsyncSelect(buffer);
86 if (hResult != NULL)
87 {
88 while(DBFetch(hResult))
89 {
90 query = _T("");
91
92 // Column names
93 columnCount = DBGetColumnCountAsync(hResult);
94 query.appendFormattedString(_T("INSERT INTO %s ("), name);
95 for(i = 0; i < columnCount; i++)
96 {
97 DBGetColumnNameAsync(hResult, i, buffer, 256);
98 query += buffer;
99 query += _T(",");
100 }
101 query.shrink();
102 query += _T(") VALUES (");
103
104 // Data
105 TCHAR data[8192];
106 for(i = 0; i < columnCount; i++)
107 {
108 TCHAR *escapedString = EscapeString(DBGetFieldAsync(hResult, i, data, 8192));
109 query.appendPreallocated(escapedString);
110 query += _T(",");
111 }
112 query.shrink();
113 query += _T(")");
114
115 char *utf8query = query.getUTF8String();
116 if (sqlite3_exec(db, utf8query, NULL, NULL, &errmsg) != SQLITE_OK)
117 {
118 free(utf8query);
119 _tprintf(_T("ERROR: SQLite query failed: %hs\n Query: %s\n"), errmsg, (const TCHAR *)query);
120 sqlite3_free(errmsg);
121 success = FALSE;
122 break;
123 }
124 free(utf8query);
125 }
126 DBFreeAsyncResult(hResult);
127
128 if (success)
129 {
130 if (sqlite3_exec(db, "COMMIT", NULL, NULL, &errmsg) != SQLITE_OK)
131 {
132 _tprintf(_T("ERROR: Cannot commit transaction: %hs"), errmsg);
133 sqlite3_free(errmsg);
134 success = FALSE;
135 }
136 }
137 else
138 {
139 if (sqlite3_exec(db, "ROLLBACK", NULL, NULL, &errmsg) != SQLITE_OK)
140 {
141 _tprintf(_T("ERROR: Cannot rollback transaction: %hs"), errmsg);
142 sqlite3_free(errmsg);
143 }
144 }
145 }
146 else
147 {
148 success = FALSE;
149 if (sqlite3_exec(db, "ROLLBACK", NULL, NULL, &errmsg) != SQLITE_OK)
150 {
151 _tprintf(_T("ERROR: Cannot rollback transaction: %hs"), errmsg);
152 sqlite3_free(errmsg);
153 }
154 }
155 }
156 else
157 {
158 success = FALSE;
159 _tprintf(_T("ERROR: Cannot start transaction: %hs"), errmsg);
160 sqlite3_free(errmsg);
161 }
162
163 return success;
164 }
165
166 /**
167 * Callback for getting schema version
168 */
169 static int GetSchemaVersionCB(void *arg, int cols, char **data, char **names)
170 {
171 *((int *)arg) = strtol(data[0], NULL, 10);
172 return 0;
173 }
174
175 /**
176 * Callback for getting idata_xx table creation query
177 */
178 static int GetIDataQueryCB(void *arg, int cols, char **data, char **names)
179 {
180 strncpy((char *)arg, data[0], MAX_DB_STRING);
181 ((char *)arg)[MAX_DB_STRING - 1] = 0;
182 return 0;
183 }
184
185 /**
186 * Export database
187 */
188 void ExportDatabase(const char *file)
189 {
190 sqlite3 *db;
191 char *errmsg, buffer[MAX_PATH], queryTemplate[11][MAX_DB_STRING], *data;
192 TCHAR idataTable[128];
193 int i, rowCount, version = 0;
194 DB_RESULT hResult;
195 BOOL success = FALSE;
196
197 if (!ValidateDatabase())
198 return;
199
200 // Create new SQLite database
201 unlink(file);
202 if (sqlite3_open(file, &db) != SQLITE_OK)
203 {
204 _tprintf(_T("ERROR: unable to open output file\n"));
205 return;
206 }
207
208 if (sqlite3_exec(db, "PRAGMA page_size=65536", NULL, NULL, &errmsg) != SQLITE_OK)
209 {
210 _tprintf(_T("ERROR: cannot set page size for export file (%hs)\n"), errmsg);
211 sqlite3_free(errmsg);
212 goto cleanup;
213 }
214
215 // Setup database schema
216 #ifdef _WIN32
217 HKEY hKey;
218
219 // Read installation data from registry
220 if (RegOpenKeyEx(HKEY_LOCAL_MACHINE, _T("Software\\NetXMS\\Server"), 0,
221 KEY_QUERY_VALUE, &hKey) == ERROR_SUCCESS)
222 {
223 DWORD size = MAX_PATH - 16;
224 if (RegQueryValueExA(hKey, "InstallPath", NULL, NULL,
225 (BYTE *)buffer, &size) == ERROR_SUCCESS)
226 {
227 strcat(buffer, "\\lib\\sql\\dbschema_sqlite.sql");
228 success = TRUE;
229 }
230 RegCloseKey(hKey);
231 }
232
233 if (!success)
234 {
235 // Try to use path to nxdbmgr executable as base
236 if (GetModuleFileNameA(NULL, buffer, MAX_PATH - 32) != 0)
237 {
238 char *p;
239
240 p = strrchr(buffer, '\\');
241 if (p != NULL)
242 *p = 0;
243 p = strrchr(buffer, '\\');
244 if (p != NULL)
245 *p = 0;
246 strcat(buffer, "\\lib\\sql\\dbschema_sqlite.sql");
247 success = TRUE;
248 }
249 }
250
251 if (!success)
252 {
253 _tprintf(_T("ERROR: unable to determine path to schema file\n"));
254 goto cleanup;
255 }
256
257 success = FALSE; // Reset success flag
258 #else
259 #ifdef UNICODE
260 WideCharToMultiByte(CP_ACP, WC_COMPOSITECHECK | WC_DEFAULTCHAR, DATADIR, -1, buffer, MAX_PATH - 32, NULL, NULL);
261 strcat(buffer, "/sql/dbschema_sqlite.sql");
262 #else
263 strcpy(buffer, DATADIR "/sql/dbschema_sqlite.sql");
264 #endif
265 #endif
266
267 UINT32 size;
268 data = (char *)LoadFileA(buffer, &size);
269 if (data == NULL)
270 {
271 _tprintf(_T("ERROR: cannot load schema file \"%hs\"\n"), buffer);
272 goto cleanup;
273 }
274
275 if (sqlite3_exec(db, data, NULL, NULL, &errmsg) != SQLITE_OK)
276 {
277 _tprintf(_T("ERROR: unable to apply database schema: %hs\n"), errmsg);
278 sqlite3_free(errmsg);
279 goto cleanup;
280 }
281
282 free(data);
283
284 // Check that dbschema_sqlite.sql and database have the same schema version
285 if (sqlite3_exec(db, "SELECT var_value FROM metadata WHERE var_name='SchemaVersion'", GetSchemaVersionCB, &version, &errmsg) != SQLITE_OK)
286 {
287 _tprintf(_T("ERROR: SQL query failed (%hs)\n"), errmsg);
288 sqlite3_free(errmsg);
289 goto cleanup;
290 }
291 if (version != DBGetSchemaVersion(g_hCoreDB))
292 {
293 _tprintf(_T("ERROR: Schema version mismatch between dbschema_sqlite.sql and your database. Please check that NetXMS server installed correctly.\n"));
294 goto cleanup;
295 }
296
297 // Export tables
298 for(i = 0; g_tables[i] != NULL; i++)
299 {
300 if (!ExportTable(db, g_tables[i]))
301 goto cleanup;
302 }
303
304 // Export tables with collected DCI data
305 memset(queryTemplate, 0, sizeof(queryTemplate));
306
307 if (sqlite3_exec(db, "SELECT var_value FROM metadata WHERE var_name='IDataTableCreationCommand'",
308 GetIDataQueryCB, queryTemplate[0], &errmsg) != SQLITE_OK)
309 {
310 _tprintf(_T("ERROR: SQLite query failed (%hs)\n"), errmsg);
311 sqlite3_free(errmsg);
312 goto cleanup;
313 }
314
315 for(int i = 0; i < 10; i++)
316 {
317 sprintf(buffer, "SELECT var_value FROM metadata WHERE var_name='TDataTableCreationCommand_%d'", i);
318 if (sqlite3_exec(db, buffer, GetIDataQueryCB, queryTemplate[i + 1], &errmsg) != SQLITE_OK)
319 {
320 _tprintf(_T("ERROR: SQLite query failed (%hs)\n"), errmsg);
321 sqlite3_free(errmsg);
322 goto cleanup;
323 }
324 if (queryTemplate[i + 1][0] == 0)
325 break;
326 }
327
328 hResult = SQLSelect(_T("SELECT id FROM nodes"));
329 if (hResult == NULL)
330 goto cleanup;
331
332 rowCount = DBGetNumRows(hResult);
333 for(i = 0; i < rowCount; i++)
334 {
335 UINT32 id = DBGetFieldLong(hResult, i, 0);
336
337 for(int j = 0; j < 11; j++)
338 {
339 if (queryTemplate[j][0] == 0)
340 break;
341
342 snprintf(buffer, MAX_PATH, queryTemplate[j], id, id);
343 if (sqlite3_exec(db, buffer, NULL, NULL, &errmsg) != SQLITE_OK)
344 {
345 _tprintf(_T("ERROR: SQLite query failed: %hs (%hs)\n"), buffer, errmsg);
346 sqlite3_free(errmsg);
347 DBFreeResult(hResult);
348 goto cleanup;
349 }
350 }
351
352 _sntprintf(idataTable, 128, _T("idata_%d"), id);
353 if (!ExportTable(db, idataTable))
354 {
355 DBFreeResult(hResult);
356 goto cleanup;
357 }
358
359 _sntprintf(idataTable, 128, _T("tdata_%d"), id);
360 if (!ExportTable(db, idataTable))
361 {
362 DBFreeResult(hResult);
363 goto cleanup;
364 }
365
366 _sntprintf(idataTable, 128, _T("tdata_records_%d"), id);
367 if (!ExportTable(db, idataTable))
368 {
369 DBFreeResult(hResult);
370 goto cleanup;
371 }
372
373 _sntprintf(idataTable, 128, _T("tdata_rows_%d"), id);
374 if (!ExportTable(db, idataTable))
375 {
376 DBFreeResult(hResult);
377 goto cleanup;
378 }
379 }
380
381 DBFreeResult(hResult);
382
383 success = TRUE;
384
385 cleanup:
386 sqlite3_close(db);
387 _tprintf(success ? _T("Database export complete.\n") : _T("Database export failed.\n"));
388 }