fixed incorrect sprintf format usage; fixed access to uninitialized memory in DB...
[public/netxms.git] / src / server / tools / nxdbmgr / export.cpp
1 /*
2 ** nxdbmgr - NetXMS database manager
3 ** Copyright (C) 2004-2015 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 int i, columnCount = 0;
76 BOOL success = TRUE;
77
78 _tprintf(_T("Exporting table %s\n"), name);
79
80 if (sqlite3_exec(db, "BEGIN", NULL, NULL, &errmsg) == SQLITE_OK)
81 {
82 _sntprintf(buffer, 256, _T("SELECT * FROM %s"), name);
83
84 DB_UNBUFFERED_RESULT hResult = SQLSelectUnbuffered(buffer);
85 if (hResult != NULL)
86 {
87 while(DBFetch(hResult))
88 {
89 query = _T("");
90
91 // Column names
92 columnCount = DBGetColumnCount(hResult);
93 query.appendFormattedString(_T("INSERT INTO %s ("), name);
94 for(i = 0; i < columnCount; i++)
95 {
96 DBGetColumnName(hResult, i, buffer, 256);
97 query += buffer;
98 query += _T(",");
99 }
100 query.shrink();
101 query += _T(") VALUES (");
102
103 // Data
104 TCHAR data[8192];
105 for(i = 0; i < columnCount; i++)
106 {
107 TCHAR *escapedString = EscapeString(DBGetField(hResult, i, data, 8192));
108 query.appendPreallocated(escapedString);
109 query += _T(",");
110 }
111 query.shrink();
112 query += _T(")");
113
114 char *utf8query = query.getUTF8String();
115 if (sqlite3_exec(db, utf8query, NULL, NULL, &errmsg) != SQLITE_OK)
116 {
117 free(utf8query);
118 _tprintf(_T("ERROR: SQLite query failed: %hs\n Query: %s\n"), errmsg, (const TCHAR *)query);
119 sqlite3_free(errmsg);
120 success = FALSE;
121 break;
122 }
123 free(utf8query);
124 }
125 DBFreeResult(hResult);
126
127 if (success)
128 {
129 if (sqlite3_exec(db, "COMMIT", NULL, NULL, &errmsg) != SQLITE_OK)
130 {
131 _tprintf(_T("ERROR: Cannot commit transaction: %hs"), errmsg);
132 sqlite3_free(errmsg);
133 success = FALSE;
134 }
135 }
136 else
137 {
138 if (sqlite3_exec(db, "ROLLBACK", NULL, NULL, &errmsg) != SQLITE_OK)
139 {
140 _tprintf(_T("ERROR: Cannot rollback transaction: %hs"), errmsg);
141 sqlite3_free(errmsg);
142 }
143 }
144 }
145 else
146 {
147 success = FALSE;
148 if (sqlite3_exec(db, "ROLLBACK", NULL, NULL, &errmsg) != SQLITE_OK)
149 {
150 _tprintf(_T("ERROR: Cannot rollback transaction: %hs"), errmsg);
151 sqlite3_free(errmsg);
152 }
153 }
154 }
155 else
156 {
157 success = FALSE;
158 _tprintf(_T("ERROR: Cannot start transaction: %hs"), errmsg);
159 sqlite3_free(errmsg);
160 }
161
162 return success;
163 }
164
165 /**
166 * Callback for getting schema version
167 */
168 static int GetSchemaVersionCB(void *arg, int cols, char **data, char **names)
169 {
170 *((int *)arg) = strtol(data[0], NULL, 10);
171 return 0;
172 }
173
174 /**
175 * Callback for getting idata_xx table creation query
176 */
177 static 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
184 /**
185 * Export database
186 */
187 void ExportDatabase(char *file, bool skipAudit, bool skipAlarms, bool skipEvent, bool skipSysLog, bool skipTrapLog)
188 {
189 sqlite3 *db;
190 char *errmsg, queryTemplate[11][MAX_DB_STRING], *data;
191 TCHAR idataTable[128];
192 int legacy = 0, major = 0, minor = 0;
193 BOOL success = FALSE;
194
195 if (!ValidateDatabase())
196 return;
197
198 // Create new SQLite database
199 unlink(file);
200 if (sqlite3_open(file, &db) != SQLITE_OK)
201 {
202 _tprintf(_T("ERROR: unable to open output file\n"));
203 return;
204 }
205
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
213 // Setup database schema
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);
217
218 UINT32 size;
219 data = (char *)LoadFile(schemaFile, &size);
220 if (data == NULL)
221 {
222 _tprintf(_T("ERROR: cannot load schema file \"%s\"\n"), schemaFile);
223 goto cleanup;
224 }
225
226 if (sqlite3_exec(db, data, NULL, NULL, &errmsg) != SQLITE_OK)
227 {
228 _tprintf(_T("ERROR: unable to apply database schema: %hs\n"), errmsg);
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
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))
239 {
240 _tprintf(_T("ERROR: SQL query failed (%hs)\n"), errmsg);
241 sqlite3_free(errmsg);
242 goto cleanup;
243 }
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))
252 {
253 _tprintf(_T("ERROR: Schema version mismatch between dbschema_sqlite.sql and your database. Please check that NetXMS server installed correctly.\n"));
254 goto cleanup;
255 }
256
257 // Export tables
258 for(int i = 0; g_tables[i] != NULL; i++)
259 {
260 if (skipAudit && !_tcscmp(g_tables[i], _T("audit_log")))
261 continue;
262 if (skipEvent && !_tcscmp(g_tables[i], _T("event_log")))
263 continue;
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;
272 if (!ExportTable(db, g_tables[i]))
273 goto cleanup;
274 }
275
276 if (!g_skipDataMigration || !g_skipDataSchemaMigration)
277 {
278 int i;
279
280 // Export tables with collected DCI data
281 memset(queryTemplate, 0, sizeof(queryTemplate));
282
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 }
290
291 for(i = 0; i < 10; i++)
292 {
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)
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)
302 break;
303 }
304
305 IntegerArray<UINT32> *targets = GetDataCollectionTargets();
306 if (targets == NULL)
307 goto cleanup;
308 for(i = 0; i < targets->size(); i++)
309 {
310 UINT32 id = targets->get(i);
311
312 if (!g_skipDataSchemaMigration)
313 {
314 for(int j = 0; j < 11; j++)
315 {
316 if (queryTemplate[j][0] == 0)
317 break;
318
319 char query[1024];
320 snprintf(query, 1024, queryTemplate[j], id, id);
321 if (sqlite3_exec(db, query, NULL, NULL, &errmsg) != SQLITE_OK)
322 {
323 _tprintf(_T("ERROR: SQLite query failed: %hs (%hs)\n"), query, errmsg);
324 sqlite3_free(errmsg);
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 {
335 goto cleanup;
336 }
337
338 _sntprintf(idataTable, 128, _T("tdata_%d"), id);
339 if (!ExportTable(db, idataTable))
340 {
341 goto cleanup;
342 }
343 }
344 }
345
346 delete targets;
347 }
348
349 success = TRUE;
350
351 cleanup:
352 sqlite3_close(db);
353 _tprintf(success ? _T("Database export complete.\n") : _T("Database export failed.\n"));
354 }