6c584c39eff941f2dad1ba856204bc56f646b61e
[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, buffer[MAX_PATH], 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 #ifdef UNICODE
216 TCHAR tmp[MAX_PATH];
217 GetNetXMSDirectory(nxDirShare, tmp);
218 #ifdef _WIN32
219 _tcslcat(tmp, _T("\\sql\\dbschema_sqlite.sql"), MAX_PATH);
220 #else
221 _tcslcat(tmp, _T("/sql/dbschema_sqlite.sql"), MAX_PATH);
222 #endif
223 WideCharToMultiByte(CP_ACP, WC_COMPOSITECHECK | WC_DEFAULTCHAR, tmp, MAX_PATH, buffer, MAX_PATH, NULL, NULL);
224 #else
225 GetNetXMSDirectory(nxDirShare, buffer);
226 #ifdef _WIN32
227 strlcat(buffer, "\\sql\\dbschema_sqlite.sql", MAX_PATH);
228 #else
229 strlcat(buffer, "/sql/dbschema_sqlite.sql", MAX_PATH);
230 #endif
231 #endif
232
233 UINT32 size;
234 data = (char *)LoadFileA(buffer, &size);
235 if (data == NULL)
236 {
237 _tprintf(_T("ERROR: cannot load schema file \"%hs\"\n"), buffer);
238 goto cleanup;
239 }
240
241 if (sqlite3_exec(db, data, NULL, NULL, &errmsg) != SQLITE_OK)
242 {
243 _tprintf(_T("ERROR: unable to apply database schema: %hs\n"), errmsg);
244 sqlite3_free(errmsg);
245 goto cleanup;
246 }
247
248 free(data);
249
250 // Check that dbschema_sqlite.sql and database have the same schema version
251 if (sqlite3_exec(db, "SELECT var_value FROM metadata WHERE var_name='SchemaVersion'", GetSchemaVersionCB, &version, &errmsg) != SQLITE_OK)
252 {
253 _tprintf(_T("ERROR: SQL query failed (%hs)\n"), errmsg);
254 sqlite3_free(errmsg);
255 goto cleanup;
256 }
257 if (version != DBGetSchemaVersion(g_hCoreDB))
258 {
259 _tprintf(_T("ERROR: Schema version mismatch between dbschema_sqlite.sql and your database. Please check that NetXMS server installed correctly.\n"));
260 goto cleanup;
261 }
262
263 // Export tables
264 for(int i = 0; g_tables[i] != NULL; i++)
265 {
266 if (skipAudit && !_tcscmp(g_tables[i], _T("audit_log")))
267 continue;
268 if (skipEvent && !_tcscmp(g_tables[i], _T("event_log")))
269 continue;
270 if (skipAlarms && (!_tcscmp(g_tables[i], _T("alarms")) ||
271 !_tcscmp(g_tables[i], _T("alarm_notes")) ||
272 !_tcscmp(g_tables[i], _T("alarm_events"))))
273 continue;
274 if (skipTrapLog && !_tcscmp(g_tables[i], _T("snmp_trap_log")))
275 continue;
276 if (skipSysLog && !_tcscmp(g_tables[i], _T("syslog")))
277 continue;
278 if (!ExportTable(db, g_tables[i]))
279 goto cleanup;
280 }
281
282 if (!g_skipDataMigration || !g_skipDataSchemaMigration)
283 {
284 int i;
285
286 // Export tables with collected DCI data
287 memset(queryTemplate, 0, sizeof(queryTemplate));
288
289 if (sqlite3_exec(db, "SELECT var_value FROM metadata WHERE var_name='IDataTableCreationCommand'",
290 GetIDataQueryCB, queryTemplate[0], &errmsg) != SQLITE_OK)
291 {
292 _tprintf(_T("ERROR: SQLite query failed (%hs)\n"), errmsg);
293 sqlite3_free(errmsg);
294 goto cleanup;
295 }
296
297 for(i = 0; i < 10; i++)
298 {
299 sprintf(buffer, "SELECT var_value FROM metadata WHERE var_name='TDataTableCreationCommand_%d'", i);
300 if (sqlite3_exec(db, buffer, GetIDataQueryCB, queryTemplate[i + 1], &errmsg) != SQLITE_OK)
301 {
302 _tprintf(_T("ERROR: SQLite query failed (%hs)\n"), errmsg);
303 sqlite3_free(errmsg);
304 goto cleanup;
305 }
306 if (queryTemplate[i + 1][0] == 0)
307 break;
308 }
309
310 hResult = SQLSelect(_T("SELECT id FROM nodes"));
311 if (hResult == NULL)
312 goto cleanup;
313
314 rowCount = DBGetNumRows(hResult);
315 for(i = 0; i < rowCount; i++)
316 {
317 UINT32 id = DBGetFieldLong(hResult, i, 0);
318
319 if (!g_skipDataSchemaMigration)
320 {
321 for(int j = 0; j < 11; j++)
322 {
323 if (queryTemplate[j][0] == 0)
324 break;
325
326 snprintf(buffer, MAX_PATH, queryTemplate[j], id, id);
327 if (sqlite3_exec(db, buffer, NULL, NULL, &errmsg) != SQLITE_OK)
328 {
329 _tprintf(_T("ERROR: SQLite query failed: %hs (%hs)\n"), buffer, errmsg);
330 sqlite3_free(errmsg);
331 DBFreeResult(hResult);
332 goto cleanup;
333 }
334 }
335 }
336
337 if (!g_skipDataMigration)
338 {
339 _sntprintf(idataTable, 128, _T("idata_%d"), id);
340 if (!ExportTable(db, idataTable))
341 {
342 DBFreeResult(hResult);
343 goto cleanup;
344 }
345
346 _sntprintf(idataTable, 128, _T("tdata_%d"), id);
347 if (!ExportTable(db, idataTable))
348 {
349 DBFreeResult(hResult);
350 goto cleanup;
351 }
352 }
353 }
354
355 DBFreeResult(hResult);
356 }
357
358 success = TRUE;
359
360 cleanup:
361 sqlite3_close(db);
362 _tprintf(success ? _T("Database export complete.\n") : _T("Database export failed.\n"));
363 }