fixed incorrect sprintf format usage; fixed access to uninitialized memory in DB...
[public/netxms.git] / src / server / tools / nxdbmgr / export.cpp
CommitLineData
6d012d2f 1/*
1e558daf 2** nxdbmgr - NetXMS database manager
f17cf019 3** Copyright (C) 2004-2015 Victor Kirhenshtein
1e558daf
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: export.cpp
20**
21**/
22
23#include "nxdbmgr.h"
4f23eecc 24#include "sqlite3.h"
1e558daf 25
55285a3e
VK
26/**
27 * Tables to export
28 */
29d05345 29extern const TCHAR *g_tables[];
28f5b9a4 30
55285a3e
VK
31/**
32 * Escape string for SQLite
33 */
70d317d0
VK
34static 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
55285a3e
VK
67/**
68 * Export single database table
69 */
00d17c5a 70static BOOL ExportTable(sqlite3 *db, const TCHAR *name)
1e558daf 71{
00d17c5a
VK
72 String query;
73 TCHAR buffer[256];
70d317d0 74 char *errmsg;
00d17c5a 75 int i, columnCount = 0;
4f23eecc 76 BOOL success = TRUE;
d394f0ed 77
08b214c6 78 _tprintf(_T("Exporting table %s\n"), name);
d394f0ed 79
4c4c9b03 80 if (sqlite3_exec(db, "BEGIN", NULL, NULL, &errmsg) == SQLITE_OK)
4f23eecc 81 {
4c4c9b03 82 _sntprintf(buffer, 256, _T("SELECT * FROM %s"), name);
00d17c5a 83
f17cf019 84 DB_UNBUFFERED_RESULT hResult = SQLSelectUnbuffered(buffer);
4c4c9b03
VK
85 if (hResult != NULL)
86 {
87 while(DBFetch(hResult))
4f23eecc 88 {
08b214c6 89 query = _T("");
4c4c9b03
VK
90
91 // Column names
f17cf019 92 columnCount = DBGetColumnCount(hResult);
4e0e77e6 93 query.appendFormattedString(_T("INSERT INTO %s ("), name);
4c4c9b03
VK
94 for(i = 0; i < columnCount; i++)
95 {
f17cf019 96 DBGetColumnName(hResult, i, buffer, 256);
4c4c9b03 97 query += buffer;
08b214c6 98 query += _T(",");
4c4c9b03 99 }
0bbab9d3 100 query.shrink();
08b214c6 101 query += _T(") VALUES (");
4c4c9b03
VK
102
103 // Data
70d317d0 104 TCHAR data[8192];
4c4c9b03
VK
105 for(i = 0; i < columnCount; i++)
106 {
f17cf019 107 TCHAR *escapedString = EscapeString(DBGetField(hResult, i, data, 8192));
4e0e77e6 108 query.appendPreallocated(escapedString);
08b214c6 109 query += _T(",");
4c4c9b03 110 }
0bbab9d3 111 query.shrink();
08b214c6 112 query += _T(")");
4c4c9b03 113
08b214c6
VK
114 char *utf8query = query.getUTF8String();
115 if (sqlite3_exec(db, utf8query, NULL, NULL, &errmsg) != SQLITE_OK)
4c4c9b03 116 {
08b214c6 117 free(utf8query);
9f24efb3 118 _tprintf(_T("ERROR: SQLite query failed: %hs\n Query: %s\n"), errmsg, (const TCHAR *)query);
4c4c9b03
VK
119 sqlite3_free(errmsg);
120 success = FALSE;
121 break;
122 }
08b214c6 123 free(utf8query);
00d17c5a 124 }
f17cf019 125 DBFreeResult(hResult);
3783d300
VK
126
127 if (success)
00d17c5a 128 {
3783d300
VK
129 if (sqlite3_exec(db, "COMMIT", NULL, NULL, &errmsg) != SQLITE_OK)
130 {
9f24efb3 131 _tprintf(_T("ERROR: Cannot commit transaction: %hs"), errmsg);
3783d300
VK
132 sqlite3_free(errmsg);
133 success = FALSE;
134 }
4f23eecc 135 }
4c4c9b03
VK
136 else
137 {
3783d300
VK
138 if (sqlite3_exec(db, "ROLLBACK", NULL, NULL, &errmsg) != SQLITE_OK)
139 {
9f24efb3 140 _tprintf(_T("ERROR: Cannot rollback transaction: %hs"), errmsg);
3783d300
VK
141 sqlite3_free(errmsg);
142 }
4c4c9b03
VK
143 }
144 }
145 else
146 {
3783d300 147 success = FALSE;
4c4c9b03 148 if (sqlite3_exec(db, "ROLLBACK", NULL, NULL, &errmsg) != SQLITE_OK)
4f23eecc 149 {
9f24efb3 150 _tprintf(_T("ERROR: Cannot rollback transaction: %hs"), errmsg);
4f23eecc 151 sqlite3_free(errmsg);
4f23eecc
VK
152 }
153 }
4c4c9b03
VK
154 }
155 else
156 {
3783d300 157 success = FALSE;
9f24efb3 158 _tprintf(_T("ERROR: Cannot start transaction: %hs"), errmsg);
4c4c9b03 159 sqlite3_free(errmsg);
4f23eecc
VK
160 }
161
162 return success;
1e558daf
VK
163}
164
55285a3e
VK
165/**
166 * Callback for getting schema version
167 */
3783d300
VK
168static int GetSchemaVersionCB(void *arg, int cols, char **data, char **names)
169{
170 *((int *)arg) = strtol(data[0], NULL, 10);
171 return 0;
172}
173
55285a3e
VK
174/**
175 * Callback for getting idata_xx table creation query
176 */
3783d300
VK
177static int GetIDataQueryCB(void *arg, int cols, char **data, char **names)
178{
179 strncpy((char *)arg, data[0], MAX_DB_STRING);
180 ((char *)arg)[MAX_DB_STRING - 1] = 0;
181 return 0;
182}
183
55285a3e
VK
184/**
185 * Export database
186 */
cb093cc9 187void ExportDatabase(char *file, bool skipAudit, bool skipAlarms, bool skipEvent, bool skipSysLog, bool skipTrapLog)
1e558daf 188{
4f23eecc 189 sqlite3 *db;
e1d49d6e 190 char *errmsg, queryTemplate[11][MAX_DB_STRING], *data;
08b214c6 191 TCHAR idataTable[128];
da4b354a 192 int legacy = 0, major = 0, minor = 0;
4f23eecc
VK
193 BOOL success = FALSE;
194
1e558daf
VK
195 if (!ValidateDatabase())
196 return;
197
4f23eecc 198 // Create new SQLite database
8817f620 199 unlink(file);
4f23eecc
VK
200 if (sqlite3_open(file, &db) != SQLITE_OK)
201 {
9f24efb3 202 _tprintf(_T("ERROR: unable to open output file\n"));
4f23eecc
VK
203 return;
204 }
205
55285a3e
VK
206 if (sqlite3_exec(db, "PRAGMA page_size=65536", NULL, NULL, &errmsg) != SQLITE_OK)
207 {
208 _tprintf(_T("ERROR: cannot set page size for export file (%hs)\n"), errmsg);
209 sqlite3_free(errmsg);
210 goto cleanup;
211 }
212
4f23eecc 213 // Setup database schema
e1d49d6e
VK
214 TCHAR schemaFile[MAX_PATH];
215 GetNetXMSDirectory(nxDirShare, schemaFile);
216 _tcslcat(schemaFile, FS_PATH_SEPARATOR _T("sql") FS_PATH_SEPARATOR _T("dbschema_sqlite.sql"), MAX_PATH);
4f23eecc 217
967893bb 218 UINT32 size;
e1d49d6e 219 data = (char *)LoadFile(schemaFile, &size);
4f23eecc
VK
220 if (data == NULL)
221 {
e1d49d6e 222 _tprintf(_T("ERROR: cannot load schema file \"%s\"\n"), schemaFile);
4f23eecc
VK
223 goto cleanup;
224 }
225
226 if (sqlite3_exec(db, data, NULL, NULL, &errmsg) != SQLITE_OK)
227 {
9f24efb3 228 _tprintf(_T("ERROR: unable to apply database schema: %hs\n"), errmsg);
4f23eecc
VK
229 sqlite3_free(errmsg);
230 goto cleanup;
231 }
232
233 free(data);
234
235 // Check that dbschema_sqlite.sql and database have the same schema version
da4b354a
VK
236 if ((sqlite3_exec(db, "SELECT var_value FROM metadata WHERE var_name='SchemaVersion'", GetSchemaVersionCB, &legacy, &errmsg) != SQLITE_OK) ||
237 (sqlite3_exec(db, "SELECT var_value FROM metadata WHERE var_name='SchemaVersionMajor'", GetSchemaVersionCB, &major, &errmsg) != SQLITE_OK) ||
238 (sqlite3_exec(db, "SELECT var_value FROM metadata WHERE var_name='SchemaVersionMinor'", GetSchemaVersionCB, &minor, &errmsg) != SQLITE_OK))
3783d300 239 {
9f24efb3 240 _tprintf(_T("ERROR: SQL query failed (%hs)\n"), errmsg);
3783d300
VK
241 sqlite3_free(errmsg);
242 goto cleanup;
243 }
da4b354a
VK
244
245 INT32 dbmajor, dbminor;
246 if (!DBGetSchemaVersion(g_hCoreDB, &dbmajor, &dbminor))
247 {
248 _tprintf(_T("ERROR: Cannot determine database schema version. Please check that NetXMS server installed correctly.\n"));
249 goto cleanup;
250 }
251 if ((dbmajor != major) || (dbminor != minor))
3783d300 252 {
9f24efb3 253 _tprintf(_T("ERROR: Schema version mismatch between dbschema_sqlite.sql and your database. Please check that NetXMS server installed correctly.\n"));
3783d300
VK
254 goto cleanup;
255 }
4f23eecc 256
4f23eecc 257 // Export tables
4da7f408 258 for(int i = 0; g_tables[i] != NULL; i++)
00d17c5a 259 {
17da49af
EJ
260 if (skipAudit && !_tcscmp(g_tables[i], _T("audit_log")))
261 continue;
262 if (skipEvent && !_tcscmp(g_tables[i], _T("event_log")))
263 continue;
cb093cc9
EJ
264 if (skipAlarms && (!_tcscmp(g_tables[i], _T("alarms")) ||
265 !_tcscmp(g_tables[i], _T("alarm_notes")) ||
266 !_tcscmp(g_tables[i], _T("alarm_events"))))
267 continue;
268 if (skipTrapLog && !_tcscmp(g_tables[i], _T("snmp_trap_log")))
269 continue;
270 if (skipSysLog && !_tcscmp(g_tables[i], _T("syslog")))
271 continue;
17da49af 272 if (!ExportTable(db, g_tables[i]))
00d17c5a
VK
273 goto cleanup;
274 }
4f23eecc 275
4da7f408 276 if (!g_skipDataMigration || !g_skipDataSchemaMigration)
3783d300 277 {
4da7f408 278 int i;
971a00c1 279
4da7f408
VK
280 // Export tables with collected DCI data
281 memset(queryTemplate, 0, sizeof(queryTemplate));
4c4c9b03 282
4da7f408
VK
283 if (sqlite3_exec(db, "SELECT var_value FROM metadata WHERE var_name='IDataTableCreationCommand'",
284 GetIDataQueryCB, queryTemplate[0], &errmsg) != SQLITE_OK)
285 {
286 _tprintf(_T("ERROR: SQLite query failed (%hs)\n"), errmsg);
287 sqlite3_free(errmsg);
288 goto cleanup;
289 }
971a00c1 290
4da7f408 291 for(i = 0; i < 10; i++)
971a00c1 292 {
e1d49d6e
VK
293 char query[256];
294 sprintf(query, "SELECT var_value FROM metadata WHERE var_name='TDataTableCreationCommand_%d'", i);
295 if (sqlite3_exec(db, query, GetIDataQueryCB, queryTemplate[i + 1], &errmsg) != SQLITE_OK)
4da7f408
VK
296 {
297 _tprintf(_T("ERROR: SQLite query failed (%hs)\n"), errmsg);
298 sqlite3_free(errmsg);
299 goto cleanup;
300 }
301 if (queryTemplate[i + 1][0] == 0)
971a00c1 302 break;
971a00c1
VK
303 }
304
e1d49d6e
VK
305 IntegerArray<UINT32> *targets = GetDataCollectionTargets();
306 if (targets == NULL)
4da7f408 307 goto cleanup;
e1d49d6e 308 for(i = 0; i < targets->size(); i++)
4da7f408 309 {
e1d49d6e 310 UINT32 id = targets->get(i);
4da7f408
VK
311
312 if (!g_skipDataSchemaMigration)
313 {
314 for(int j = 0; j < 11; j++)
315 {
316 if (queryTemplate[j][0] == 0)
317 break;
318
e1d49d6e
VK
319 char query[1024];
320 snprintf(query, 1024, queryTemplate[j], id, id);
321 if (sqlite3_exec(db, query, NULL, NULL, &errmsg) != SQLITE_OK)
4da7f408 322 {
e1d49d6e 323 _tprintf(_T("ERROR: SQLite query failed: %hs (%hs)\n"), query, errmsg);
4da7f408 324 sqlite3_free(errmsg);
4da7f408
VK
325 goto cleanup;
326 }
327 }
328 }
329
330 if (!g_skipDataMigration)
331 {
332 _sntprintf(idataTable, 128, _T("idata_%d"), id);
333 if (!ExportTable(db, idataTable))
334 {
4da7f408
VK
335 goto cleanup;
336 }
337
338 _sntprintf(idataTable, 128, _T("tdata_%d"), id);
339 if (!ExportTable(db, idataTable))
340 {
4da7f408
VK
341 goto cleanup;
342 }
4da7f408
VK
343 }
344 }
4c4c9b03 345
e1d49d6e 346 delete targets;
4c4c9b03
VK
347 }
348
4f23eecc 349 success = TRUE;
1e558daf 350
4f23eecc
VK
351cleanup:
352 sqlite3_close(db);
9f24efb3 353 _tprintf(success ? _T("Database export complete.\n") : _T("Database export failed.\n"));
1e558daf 354}