fixed collected data export/import for non-node data collection targets (issue #NX...
[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 rowCount, version = 0;
193 DB_RESULT hResult;
194 BOOL success = FALSE;
195
196 if (!ValidateDatabase())
197 return;
198
199 // Create new SQLite database
200 unlink(file);
201 if (sqlite3_open(file, &db) != SQLITE_OK)
202 {
203 _tprintf(_T("ERROR: unable to open output file\n"));
204 return;
205 }
206
207 if (sqlite3_exec(db, "PRAGMA page_size=65536", NULL, NULL, &errmsg) != SQLITE_OK)
208 {
209 _tprintf(_T("ERROR: cannot set page size for export file (%hs)\n"), errmsg);
210 sqlite3_free(errmsg);
211 goto cleanup;
212 }
213
214 // Setup database schema
215 TCHAR schemaFile[MAX_PATH];
216 GetNetXMSDirectory(nxDirShare, schemaFile);
217 _tcslcat(schemaFile, FS_PATH_SEPARATOR _T("sql") FS_PATH_SEPARATOR _T("dbschema_sqlite.sql"), MAX_PATH);
218
219 UINT32 size;
220 data = (char *)LoadFile(schemaFile, &size);
221 if (data == NULL)
222 {
223 _tprintf(_T("ERROR: cannot load schema file \"%s\"\n"), schemaFile);
224 goto cleanup;
225 }
226
227 if (sqlite3_exec(db, data, NULL, NULL, &errmsg) != SQLITE_OK)
228 {
229 _tprintf(_T("ERROR: unable to apply database schema: %hs\n"), errmsg);
230 sqlite3_free(errmsg);
231 goto cleanup;
232 }
233
234 free(data);
235
236 // Check that dbschema_sqlite.sql and database have the same schema version
237 if (sqlite3_exec(db, "SELECT var_value FROM metadata WHERE var_name='SchemaVersion'", GetSchemaVersionCB, &version, &errmsg) != SQLITE_OK)
238 {
239 _tprintf(_T("ERROR: SQL query failed (%hs)\n"), errmsg);
240 sqlite3_free(errmsg);
241 goto cleanup;
242 }
243 if (version != DBGetSchemaVersion(g_hCoreDB))
244 {
245 _tprintf(_T("ERROR: Schema version mismatch between dbschema_sqlite.sql and your database. Please check that NetXMS server installed correctly.\n"));
246 goto cleanup;
247 }
248
249 // Export tables
250 for(int i = 0; g_tables[i] != NULL; i++)
251 {
252 if (skipAudit && !_tcscmp(g_tables[i], _T("audit_log")))
253 continue;
254 if (skipEvent && !_tcscmp(g_tables[i], _T("event_log")))
255 continue;
256 if (skipAlarms && (!_tcscmp(g_tables[i], _T("alarms")) ||
257 !_tcscmp(g_tables[i], _T("alarm_notes")) ||
258 !_tcscmp(g_tables[i], _T("alarm_events"))))
259 continue;
260 if (skipTrapLog && !_tcscmp(g_tables[i], _T("snmp_trap_log")))
261 continue;
262 if (skipSysLog && !_tcscmp(g_tables[i], _T("syslog")))
263 continue;
264 if (!ExportTable(db, g_tables[i]))
265 goto cleanup;
266 }
267
268 if (!g_skipDataMigration || !g_skipDataSchemaMigration)
269 {
270 int i;
271
272 // Export tables with collected DCI data
273 memset(queryTemplate, 0, sizeof(queryTemplate));
274
275 if (sqlite3_exec(db, "SELECT var_value FROM metadata WHERE var_name='IDataTableCreationCommand'",
276 GetIDataQueryCB, queryTemplate[0], &errmsg) != SQLITE_OK)
277 {
278 _tprintf(_T("ERROR: SQLite query failed (%hs)\n"), errmsg);
279 sqlite3_free(errmsg);
280 goto cleanup;
281 }
282
283 for(i = 0; i < 10; i++)
284 {
285 char query[256];
286 sprintf(query, "SELECT var_value FROM metadata WHERE var_name='TDataTableCreationCommand_%d'", i);
287 if (sqlite3_exec(db, query, GetIDataQueryCB, queryTemplate[i + 1], &errmsg) != SQLITE_OK)
288 {
289 _tprintf(_T("ERROR: SQLite query failed (%hs)\n"), errmsg);
290 sqlite3_free(errmsg);
291 goto cleanup;
292 }
293 if (queryTemplate[i + 1][0] == 0)
294 break;
295 }
296
297 IntegerArray<UINT32> *targets = GetDataCollectionTargets();
298 if (targets == NULL)
299 goto cleanup;
300 for(i = 0; i < targets->size(); i++)
301 {
302 UINT32 id = targets->get(i);
303
304 if (!g_skipDataSchemaMigration)
305 {
306 for(int j = 0; j < 11; j++)
307 {
308 if (queryTemplate[j][0] == 0)
309 break;
310
311 char query[1024];
312 snprintf(query, 1024, queryTemplate[j], id, id);
313 if (sqlite3_exec(db, query, NULL, NULL, &errmsg) != SQLITE_OK)
314 {
315 _tprintf(_T("ERROR: SQLite query failed: %hs (%hs)\n"), query, errmsg);
316 sqlite3_free(errmsg);
317 DBFreeResult(hResult);
318 goto cleanup;
319 }
320 }
321 }
322
323 if (!g_skipDataMigration)
324 {
325 _sntprintf(idataTable, 128, _T("idata_%d"), id);
326 if (!ExportTable(db, idataTable))
327 {
328 DBFreeResult(hResult);
329 goto cleanup;
330 }
331
332 _sntprintf(idataTable, 128, _T("tdata_%d"), id);
333 if (!ExportTable(db, idataTable))
334 {
335 DBFreeResult(hResult);
336 goto cleanup;
337 }
338 }
339 }
340
341 delete targets;
342 }
343
344 success = TRUE;
345
346 cleanup:
347 sqlite3_close(db);
348 _tprintf(success ? _T("Database export complete.\n") : _T("Database export failed.\n"));
349 }