schema-related information (like schema version, DB syntax, etc.) moved from "config...
[public/netxms.git] / src / server / tools / nxdbmgr / export.cpp
1 /*
2 ** nxdbmgr - NetXMS database manager
3 ** Copyright (C) 2004-2009 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 //
28 // Tables for export/import/clear
29 //
30
31 TCHAR *g_tables[] =
32 {
33 "config",
34 "config_clob",
35 "users",
36 "user_groups",
37 "user_group_members",
38 "user_profiles",
39 "object_properties",
40 "object_custom_attributes",
41 "zones",
42 "zone_ip_addr_list",
43 "nodes",
44 "clusters",
45 "cluster_members",
46 "cluster_sync_subnets",
47 "cluster_resources",
48 "subnets",
49 "interfaces",
50 "network_services",
51 "vpn_connectors",
52 "vpn_connector_networks",
53 "containers",
54 "conditions",
55 "cond_dci_map",
56 "templates",
57 "dct_node_map",
58 "nsmap",
59 "container_members",
60 "container_categories",
61 "acl",
62 "trusted_nodes",
63 "items",
64 "thresholds",
65 "dci_schedules",
66 "raw_dci_values",
67 "event_cfg",
68 "event_log",
69 "actions",
70 "event_groups",
71 "event_group_members",
72 "time_ranges",
73 "event_policy",
74 "policy_source_list",
75 "policy_event_list",
76 "policy_action_list",
77 "policy_situation_attr_list",
78 "policy_time_range_list",
79 "deleted_objects",
80 "alarms",
81 "alarm_notes",
82 "images",
83 "default_images",
84 "oid_to_type",
85 "snmp_trap_cfg",
86 "snmp_trap_pmap",
87 "agent_pkg",
88 "object_tools",
89 "object_tools_acl",
90 "object_tools_table_columns",
91 "syslog",
92 "script_library",
93 "snmp_trap_log",
94 "maps",
95 "map_access_lists",
96 "submaps",
97 "submap_object_positions",
98 "submap_links",
99 "agent_configs",
100 "address_lists",
101 "graphs",
102 "graph_acl",
103 "certificates",
104 "audit_log",
105 "situations",
106 "snmp_communities",
107 "web_maps",
108 NULL
109 };
110
111
112 //
113 // Export single database table
114 //
115
116 static BOOL ExportTable(sqlite3 *db, const TCHAR *name)
117 {
118 String query;
119 TCHAR buffer[256];
120 char *errmsg, *data;
121 DB_ASYNC_RESULT hResult;
122 int i, columnCount = 0;
123 BOOL success = TRUE;
124
125 printf("Exporting table %s\n", name);
126
127 if (sqlite3_exec(db, "BEGIN", NULL, NULL, &errmsg) == SQLITE_OK)
128 {
129 _sntprintf(buffer, 256, _T("SELECT * FROM %s"), name);
130
131 hResult = SQLAsyncSelect(buffer);
132 if (hResult != NULL)
133 {
134 while(DBFetch(hResult))
135 {
136 query = "";
137
138 // Column names
139 columnCount = DBGetColumnCountAsync(hResult);
140 query.AddFormattedString("INSERT INTO %s (", name);
141 for(i = 0; i < columnCount; i++)
142 {
143 DBGetColumnNameAsync(hResult, i, buffer, 256);
144 query += buffer;
145 query += ",";
146 }
147 query.Shrink();
148 query += ") VALUES (";
149
150 // Data
151 for(i = 0; i < columnCount; i++)
152 {
153 query += "'";
154 data = (char *)malloc(8192);
155 query.AddDynamicString(DBGetFieldAsync(hResult, i, data, 8192));
156 query += "',";
157 }
158 query.Shrink();
159 query += ")";
160
161 if (sqlite3_exec(db, query, NULL, NULL, &errmsg) != SQLITE_OK)
162 {
163 printf("ERROR: SQLite query failed: %s\n Query: %s\n", errmsg, (const TCHAR *)query);
164 sqlite3_free(errmsg);
165 success = FALSE;
166 break;
167 }
168 }
169 DBFreeAsyncResult(g_hCoreDB, hResult);
170 if (sqlite3_exec(db, "COMMIT", NULL, NULL, &errmsg) == SQLITE_OK)
171 {
172 success = TRUE;
173 }
174 else
175 {
176 printf("ERROR: Cannot commit transaction: %s", errmsg);
177 sqlite3_free(errmsg);
178 }
179 }
180 else
181 {
182 if (sqlite3_exec(db, "ROLLBACK", NULL, NULL, &errmsg) != SQLITE_OK)
183 {
184 printf("ERROR: Cannot rollback transaction: %s", errmsg);
185 sqlite3_free(errmsg);
186 }
187 }
188 }
189 else
190 {
191 printf("ERROR: Cannot start transaction: %s", errmsg);
192 sqlite3_free(errmsg);
193 }
194
195 return success;
196 }
197
198
199 //
200 // Export database
201 //
202
203 void ExportDatabase(const char *file)
204 {
205 sqlite3 *db;
206 char *errmsg, buffer[MAX_PATH], queryTemplate[256], *data;
207 DWORD size;
208 int i, rowCount;
209 DB_RESULT hResult;
210 BOOL success = FALSE;
211
212 if (!ValidateDatabase())
213 return;
214
215 // Create new SQLite database
216 _unlink(file);
217 if (sqlite3_open(file, &db) != SQLITE_OK)
218 {
219 printf("ERROR: unable to open output file\n");
220 return;
221 }
222
223 // Setup database schema
224 #ifdef _WIN32
225 HKEY hKey;
226
227 // Read installation data from registry
228 if (RegOpenKeyEx(HKEY_LOCAL_MACHINE, _T("Software\\NetXMS\\Server"), 0,
229 KEY_QUERY_VALUE, &hKey) == ERROR_SUCCESS)
230 {
231 size = (MAX_PATH - 16) * sizeof(TCHAR);
232 if (RegQueryValueEx(hKey, _T("InstallPath"), NULL, NULL,
233 (BYTE *)buffer, &size) == ERROR_SUCCESS)
234 {
235 strcat(buffer, "\\lib\\sql\\dbschema_sqlite.sql");
236 success = TRUE;
237 }
238 RegCloseKey(hKey);
239 }
240
241 if (!success)
242 {
243 // Try to use path to nxdbmgr executable as base
244 if (GetModuleFileNameA(NULL, buffer, MAX_PATH - 32) != 0)
245 {
246 char *p;
247
248 p = strrchr(buffer, '\\');
249 if (p != NULL)
250 *p = 0;
251 p = strrchr(buffer, '\\');
252 if (p != NULL)
253 *p = 0;
254 strcat(buffer, "\\lib\\sql\\dbschema_sqlite.sql");
255 success = TRUE;
256 }
257 }
258
259 if (!success)
260 {
261 printf("ERROR: unable to determine path to schema file\n");
262 goto cleanup;
263 }
264
265 success = FALSE; // Reset success flag
266 #else
267 strcpy(buffer, DATADIR "/sql/dbschema_sqlite.sql");
268 #endif
269
270 data = (char *)LoadFile(buffer, &size);
271 if (data == NULL)
272 {
273 printf("ERROR: cannot load schema file \"%s\"\n", buffer);
274 goto cleanup;
275 }
276
277 if (sqlite3_exec(db, data, NULL, NULL, &errmsg) != SQLITE_OK)
278 {
279 _tprintf(_T("ERROR: unable to apply database schema: %s\n"), errmsg);
280 sqlite3_free(errmsg);
281 goto cleanup;
282 }
283
284 free(data);
285
286 // Check that dbschema_sqlite.sql and database have the same schema version
287 /* TODO */
288
289 // Export tables
290 for(i = 0; g_tables[i] != NULL; i++)
291 {
292 if (!ExportTable(db, g_tables[i]))
293 goto cleanup;
294 }
295
296 // Export tables with collected DCI data
297 hResult = SQLSelect(_T("SELECT var_value FROM metadata WHERE var_name='IDataTableCreationCommand'"));
298 if (hResult == NULL)
299 goto cleanup;
300 DBGetField(hResult, 0, 0, queryTemplate, 256);
301 DBFreeResult(hResult);
302
303 hResult = SQLSelect(_T("SELECT id FROM nodes"));
304 if (hResult == NULL)
305 goto cleanup;
306
307 rowCount = DBGetNumRows(hResult);
308 for(i = 0; i < rowCount; i++)
309 {
310 snprintf(buffer, MAX_PATH, queryTemplate, DBGetFieldLong(hResult, i, 0));
311 if (sqlite3_exec(db, buffer, NULL, NULL, &errmsg) != SQLITE_OK)
312 {
313 _tprintf(_T("ERROR: SQL query failed: %s (%s)\n"), buffer, errmsg);
314 sqlite3_free(errmsg);
315 DBFreeResult(hResult);
316 goto cleanup;
317 }
318
319 snprintf(buffer, MAX_PATH, "idata_%d", DBGetFieldLong(hResult, i, 0));
320 if (!ExportTable(db, buffer))
321 {
322 DBFreeResult(hResult);
323 goto cleanup;
324 }
325 }
326
327 DBFreeResult(hResult);
328
329 success = TRUE;
330
331 cleanup:
332 sqlite3_close(db);
333 printf(success ? "Database export complete.\n" : "Database export failed.\n");
334 }