schema-related information (like schema version, DB syntax, etc.) moved from "config...
[public/netxms.git] / src / server / tools / nxdbmgr / nxdbmgr.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: nxdbmgr.cpp
20 **
21 **/
22
23 #include "nxdbmgr.h"
24
25 #ifdef _WIN32
26 #include <conio.h>
27 #endif
28
29
30 //
31 // Global variables
32 //
33
34 DB_HANDLE g_hCoreDB;
35 BOOL g_bIgnoreErrors = FALSE;
36 BOOL g_bTrace = FALSE;
37 int g_iSyntax;
38 const TCHAR *g_pszTableSuffix = _T("");
39 const TCHAR *g_pszSqlType[5][2] =
40 {
41 { _T("text"), _T("bigint") }, // MySQL
42 { _T("varchar"), _T("bigint") }, // PostgreSQL
43 { _T("text"), _T("bigint") }, // Microsoft SQL
44 { _T("clob"), _T("number(20)") }, // Oracle
45 { _T("varchar"), _T("number(20)") } // SQLite
46 };
47
48
49 //
50 // Static data
51 //
52
53 static TCHAR m_szCodePage[MAX_PATH] = ICONV_DEFAULT_CODEPAGE;
54 static NX_CFG_TEMPLATE m_cfgTemplate[] =
55 {
56 { "CodePage", CT_STRING, 0, 0, MAX_PATH, 0, m_szCodePage },
57 { "CreateCrashDumps", CT_IGNORE, 0, 0, 0, 0, NULL },
58 { "DBDriver", CT_STRING, 0, 0, MAX_PATH, 0, g_szDbDriver },
59 { "DBDrvParams", CT_STRING, 0, 0, MAX_PATH, 0, g_szDbDrvParams },
60 { "DBLogin", CT_STRING, 0, 0, MAX_DB_LOGIN, 0, g_szDbLogin },
61 { "DBName", CT_STRING, 0, 0, MAX_DB_NAME, 0, g_szDbName },
62 { "DBPassword", CT_STRING, 0, 0, MAX_DB_PASSWORD, 0, g_szDbPassword },
63 { "DBServer", CT_STRING, 0, 0, MAX_PATH, 0, g_szDbServer },
64 { "DumpDirectory", CT_IGNORE, 0, 0, 0, 0, NULL },
65 { "LogFailedSQLQueries", CT_IGNORE, 0, 0, 0, 0, NULL },
66 { "LogFile", CT_IGNORE, 0, 0, 0, 0, NULL },
67 { "Module", CT_IGNORE, 0, 0, 0, 0, NULL },
68 { "", CT_END_OF_LIST, 0, 0, 0, 0, NULL }
69 };
70 static BOOL m_bForce = FALSE;
71
72
73 //
74 // Show query if trace mode is ON
75 //
76
77 void ShowQuery(const TCHAR *pszQuery)
78 {
79 #ifdef _WIN32
80 SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 0x0F);
81 _tprintf(_T(">>> "));
82 SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 0x0A);
83 puts(pszQuery);
84 SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 0x07);
85 #else
86 _tprintf(_T(">>> %s\n"), pszQuery);
87 #endif
88 }
89
90
91 //
92 // Get Yes or No answer from keyboard
93 //
94
95 BOOL GetYesNo(void)
96 {
97 if (m_bForce)
98 {
99 printf("Y\n");
100 return TRUE;
101 }
102 else
103 {
104 #ifdef _WIN32
105 int ch;
106
107 while(1)
108 {
109 ch = _getch();
110 if ((ch == 'y') || (ch == 'Y'))
111 {
112 printf("Y\n");
113 return TRUE;
114 }
115 if ((ch == 'n') || (ch == 'N'))
116 {
117 printf("N\n");
118 return FALSE;
119 }
120 }
121 #else
122 TCHAR szBuffer[16];
123
124 fflush(stdout);
125 _fgetts(szBuffer, 16, stdin);
126 StrStrip(szBuffer);
127 return ((szBuffer[0] == 'y') || (szBuffer[0] == 'Y'));
128 #endif
129 }
130 }
131
132
133 //
134 // Execute SQL SELECT query and print error message on screen if query failed
135 //
136
137 DB_RESULT SQLSelect(const TCHAR *pszQuery)
138 {
139 DB_RESULT hResult;
140 TCHAR errorText[DBDRV_MAX_ERROR_TEXT];
141
142 if (g_bTrace)
143 ShowQuery(pszQuery);
144
145 hResult = DBSelectEx(g_hCoreDB, pszQuery, errorText);
146 if (hResult == NULL)
147 {
148 #ifdef _WIN32
149 _tprintf(_T("SQL query failed (%s):\n"), errorText);
150 SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 0x0E);
151 _tprintf(_T("%s\n"), pszQuery);
152 SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 0x07);
153 #else
154 _tprintf(_T("SQL query failed (%s):\n%s\n"), errorText, pszQuery);
155 #endif
156 }
157 return hResult;
158 }
159
160
161 //
162 // Execute SQL SELECT query via DBAsyncSelect and print error message on screen if query failed
163 //
164
165 DB_ASYNC_RESULT SQLAsyncSelect(const TCHAR *pszQuery)
166 {
167 DB_ASYNC_RESULT hResult;
168 TCHAR errorText[DBDRV_MAX_ERROR_TEXT];
169
170 if (g_bTrace)
171 ShowQuery(pszQuery);
172
173 hResult = DBAsyncSelectEx(g_hCoreDB, pszQuery, errorText);
174 if (hResult == NULL)
175 {
176 #ifdef _WIN32
177 _tprintf(_T("SQL query failed (%s):\n"), errorText);
178 SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 0x0E);
179 _tprintf(_T("%s\n"), pszQuery);
180 SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 0x07);
181 #else
182 _tprintf(_T("SQL query failed (%s):\n%s\n"), errorText, pszQuery);
183 #endif
184 }
185 return hResult;
186 }
187
188
189 //
190 // Execute SQL query and print error message on screen if query failed
191 //
192
193 BOOL SQLQuery(const TCHAR *pszQuery)
194 {
195 BOOL bResult;
196 TCHAR errorText[DBDRV_MAX_ERROR_TEXT];
197
198 if (*pszQuery == 0)
199 return TRUE;
200
201 if (g_bTrace)
202 ShowQuery(pszQuery);
203
204 bResult = DBQueryEx(g_hCoreDB, pszQuery, errorText);
205 if (!bResult)
206 {
207 #ifdef _WIN32
208 _tprintf(_T("SQL query failed (%s):\n"), errorText);
209 SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 0x0E);
210 _tprintf(_T("%s\n"), pszQuery);
211 SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 0x07);
212 #else
213 _tprintf(_T("SQL query failed (%s):\n%s\n"), errorText, pszQuery);
214 #endif
215 }
216 return bResult;
217 }
218
219
220 //
221 // Execute SQL batch
222 //
223
224 BOOL SQLBatch(const TCHAR *pszBatch)
225 {
226 TCHAR *pszBuffer, *pszQuery, *ptr;
227 TCHAR errorText[DBDRV_MAX_ERROR_TEXT];
228 BOOL bRet = TRUE;
229
230 pszBuffer = _tcsdup(pszBatch);
231 TranslateStr(pszBuffer, _T("$SQL:TEXT"), g_pszSqlType[g_iSyntax][SQL_TYPE_TEXT]);
232 TranslateStr(pszBuffer, _T("$SQL:INT64"), g_pszSqlType[g_iSyntax][SQL_TYPE_INT64]);
233
234 pszQuery = pszBuffer;
235 while(1)
236 {
237 ptr = _tcschr(pszQuery, _T('\n'));
238 if (ptr != NULL)
239 *ptr = 0;
240 if (!_tcscmp(pszQuery, _T("<END>")))
241 break;
242
243 if (g_bTrace)
244 ShowQuery(pszQuery);
245
246 if (!DBQueryEx(g_hCoreDB, pszQuery, errorText))
247 {
248 #ifdef _WIN32
249 _tprintf(_T("SQL query failed (%s):\n"), errorText);
250 SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 0x0E);
251 _tprintf(_T("%s\n"), pszQuery);
252 SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 0x07);
253 #else
254 _tprintf(_T("SQL query failed (%s):\n%s\n"), errorText, pszQuery);
255 #endif
256 if (!g_bIgnoreErrors)
257 {
258 bRet = FALSE;
259 break;
260 }
261 }
262 ptr++;
263 pszQuery = ptr;
264 }
265 free(pszBuffer);
266 return bRet;
267 }
268
269
270 //
271 // Read string value from configuration table
272 //
273
274 BOOL ConfigReadStr(const TCHAR *pszVar, TCHAR *pszBuffer, int iBufSize, const TCHAR *pszDefault)
275 {
276 DB_RESULT hResult;
277 TCHAR szQuery[256];
278 BOOL bSuccess = FALSE;
279
280 nx_strncpy(pszBuffer, pszDefault, iBufSize);
281 if (_tcslen(pszVar) > 127)
282 return FALSE;
283
284 _sntprintf(szQuery, 256, _T("SELECT var_value FROM config WHERE var_name='%s'"), pszVar);
285 hResult = SQLSelect(szQuery);
286 if (hResult == 0)
287 return FALSE;
288
289 if (DBGetNumRows(hResult) > 0)
290 {
291 DBGetField(hResult, 0, 0, pszBuffer, iBufSize);
292 DecodeSQLString(pszBuffer);
293 bSuccess = TRUE;
294 }
295
296 DBFreeResult(hResult);
297 return bSuccess;
298 }
299
300
301 //
302 // Read integer value from configuration table
303 //
304
305 int ConfigReadInt(const TCHAR *pszVar, int iDefault)
306 {
307 TCHAR szBuffer[64];
308
309 if (ConfigReadStr(pszVar, szBuffer, 64, _T("")))
310 return _tcstol(szBuffer, NULL, 0);
311 else
312 return iDefault;
313 }
314
315
316 //
317 // Read unsigned long value from configuration table
318 //
319
320 DWORD ConfigReadULong(const TCHAR *pszVar, DWORD dwDefault)
321 {
322 TCHAR szBuffer[64];
323
324 if (ConfigReadStr(pszVar, szBuffer, 64, _T("")))
325 return _tcstoul(szBuffer, NULL, 0);
326 else
327 return dwDefault;
328 }
329
330
331 //
332 // Check that database has correct schema version and is not locked
333 //
334
335 BOOL ValidateDatabase()
336 {
337 DB_RESULT hResult;
338 LONG nVersion = 0;
339 BOOL bLocked;
340 TCHAR szLockStatus[MAX_DB_STRING], szLockInfo[MAX_DB_STRING];
341
342 // Get database format version
343 nVersion = DBGetSchemaVersion(g_hCoreDB);
344 if (nVersion < DB_FORMAT_VERSION)
345 {
346 _tprintf(_T("Your database has format version %d, this tool is compiled for version %d.\nUse \"upgrade\" command to upgrade your database first.\n"),
347 nVersion, DB_FORMAT_VERSION);
348 return FALSE;
349 }
350 else if (nVersion > DB_FORMAT_VERSION)
351 {
352 _tprintf(_T("Your database has format version %d, this tool is compiled for version %d.\n"
353 "You need to upgrade your server before using this database.\n"),
354 nVersion, DB_FORMAT_VERSION);
355 return FALSE;
356 }
357
358 // Check if database is locked
359 hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBLockStatus'"));
360 if (hResult != NULL)
361 {
362 if (DBGetNumRows(hResult) > 0)
363 {
364 DBGetField(hResult, 0, 0, szLockStatus, MAX_DB_STRING);
365 DecodeSQLString(szLockStatus);
366 bLocked = _tcscmp(szLockStatus, _T("UNLOCKED"));
367 }
368 DBFreeResult(hResult);
369
370 if (bLocked)
371 {
372 hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBLockInfo'"));
373 if (hResult != NULL)
374 {
375 if (DBGetNumRows(hResult) > 0)
376 {
377 DBGetField(hResult, 0, 0, szLockInfo, MAX_DB_STRING);
378 DecodeSQLString(szLockInfo);
379 }
380 DBFreeResult(hResult);
381 }
382 }
383 }
384
385 if (bLocked)
386 {
387 _tprintf(_T("Database is locked by server %s [%s]\n"), szLockStatus, szLockInfo);
388 return FALSE;
389 }
390
391 return TRUE;
392 }
393
394
395 //
396 // Startup
397 //
398
399 int main(int argc, char *argv[])
400 {
401 BOOL bStart = TRUE, bForce = FALSE;
402 int ch;
403 TCHAR szConfigFile[MAX_PATH] = DEFAULT_CONFIG_FILE;
404 #ifdef _WIN32
405 HKEY hKey;
406 DWORD dwSize;
407 #else
408 char *pszEnv;
409 #endif
410
411 InitThreadLibrary();
412
413 printf("NetXMS Database Manager Version " NETXMS_VERSION_STRING "\n\n");
414
415 // Check for alternate config file location
416 #ifdef _WIN32
417 if (RegOpenKeyEx(HKEY_LOCAL_MACHINE, _T("Software\\NetXMS\\Server"), 0,
418 KEY_QUERY_VALUE, &hKey) == ERROR_SUCCESS)
419 {
420 dwSize = MAX_PATH * sizeof(TCHAR);
421 RegQueryValueEx(hKey, _T("ConfigFile"), NULL, NULL, (BYTE *)szConfigFile, &dwSize);
422 RegCloseKey(hKey);
423 }
424 #else
425 pszEnv = getenv("NETXMSD_CONFIG");
426 if (pszEnv != NULL)
427 nx_strncpy(szConfigFile, pszEnv, MAX_PATH);
428 #endif
429
430 // Parse command line
431 opterr = 1;
432 while((ch = getopt(argc, argv, "c:fhIMtvX")) != -1)
433 {
434 switch(ch)
435 {
436 case 'h': // Display help and exit
437 _tprintf(_T("Usage: nxdbmgr [<options>] <command>\n"
438 "Valid commands are:\n"
439 " batch <file> : Run SQL batch file\n"
440 " check : Check database for errors\n"
441 " export <file> : Export database to file\n"
442 " import <file> : Import database from file\n"
443 " init <file> : Initialize database\n"
444 " reindex : Reindex database\n"
445 " unlock : Forced database unlock\n"
446 " upgrade : Upgrade database to new version\n"
447 "Valid options are:\n"
448 " -c <config> : Use alternate configuration file. Default is " DEFAULT_CONFIG_FILE "\n"
449 " -f : Force repair - do not ask for confirmation.\n"
450 " -h : Display help and exit.\n"
451 " -I : MySQL only - specify TYPE=InnoDB for new tables.\n"
452 " -M : MySQL only - specify TYPE=MyISAM for new tables.\n"
453 " -t : Enable trace moded (show executed SQL queries).\n"
454 " -v : Display version and exit.\n"
455 " -X : Ignore SQL errors when upgrading (USE WITH CARE!!!)\n"
456 "\n"));
457 bStart = FALSE;
458 break;
459 case 'v': // Print version and exit
460 bStart = FALSE;
461 break;
462 case 'c':
463 nx_strncpy(szConfigFile, optarg, MAX_PATH);
464 break;
465 case 'f':
466 m_bForce = TRUE;
467 break;
468 case 't':
469 g_bTrace = TRUE;
470 break;
471 case 'I':
472 g_pszTableSuffix = _T(" TYPE=InnoDB");
473 break;
474 case 'M':
475 g_pszTableSuffix = _T(" TYPE=MyISAM");
476 break;
477 case 'X':
478 g_bIgnoreErrors = TRUE;
479 break;
480 case '?':
481 bStart = FALSE;
482 break;
483 default:
484 break;
485 }
486 }
487
488 if (!bStart)
489 return 1;
490
491 // Check parameter correctness
492 if (argc - optind == 0)
493 {
494 _tprintf(_T("Command missing. Type nxdbmgr -h for command line syntax.\n"));
495 return 1;
496 }
497 if (strcmp(argv[optind], "batch") &&
498 strcmp(argv[optind], "check") &&
499 strcmp(argv[optind], "export") &&
500 strcmp(argv[optind], "import") &&
501 strcmp(argv[optind], "reindex") &&
502 strcmp(argv[optind], "upgrade") &&
503 strcmp(argv[optind], "unlock") &&
504 strcmp(argv[optind], "init"))
505 {
506 _tprintf(_T("Invalid command \"%s\". Type nxdbmgr -h for command line syntax.\n"), argv[optind]);
507 return 1;
508 }
509 if ((!strcmp(argv[optind], "init") || !strcmp(argv[optind], "batch") || !strcmp(argv[optind], "export") || !strcmp(argv[optind], "import")) && (argc - optind < 2))
510 {
511 _tprintf("Required command argument missing\n");
512 return 1;
513 }
514
515 // Read configuration file
516 #if !defined(_WIN32) && !defined(_NETWARE)
517 if (!_tcscmp(szConfigFile, _T("{search}")))
518 {
519 if (access(PREFIX "/etc/netxmsd.conf", 4) == 0)
520 {
521 _tcscpy(szConfigFile, PREFIX "/etc/netxmsd.conf");
522 }
523 else if (access("/usr/etc/netxmsd.conf", 4) == 0)
524 {
525 _tcscpy(szConfigFile, "/usr/etc/netxmsd.conf");
526 }
527 else
528 {
529 _tcscpy(szConfigFile, "/etc/netxmsd.conf");
530 }
531 }
532 #endif
533
534 if (NxLoadConfig(szConfigFile, _T(""), m_cfgTemplate, TRUE) != NXCFG_ERR_OK)
535 {
536 _tprintf(_T("Error loading configuration file\n"));
537 return 2;
538 }
539 #ifndef _WIN32
540 SetDefaultCodepage(m_szCodePage);
541 #endif
542
543 // Connect to database
544 if (!DBInit(FALSE, FALSE, FALSE, NULL))
545 {
546 _tprintf(_T("Unable to load and initialize database driver \"%s\"\n"), g_szDbDriver);
547 return 3;
548 }
549
550 g_hCoreDB = DBConnect();
551 if (g_hCoreDB == NULL)
552 {
553 _tprintf(_T("Unable to connect to database %s@%s as %s\n"), g_szDbName,
554 g_szDbServer, g_szDbLogin);
555 DBUnloadDriver();
556 return 4;
557 }
558
559 if (!strcmp(argv[optind], "init"))
560 {
561 InitDatabase(argv[optind + 1]);
562 }
563 else
564 {
565 // Get database syntax
566 g_iSyntax = DBGetSyntax(g_hCoreDB);
567 if (g_iSyntax == DB_SYNTAX_UNKNOWN)
568 {
569 _tprintf(_T("Unable to determine database syntax\n"));
570 DBDisconnect(g_hCoreDB);
571 DBUnloadDriver();
572 return 5;
573 }
574
575 // Do requested operation
576 if (!strcmp(argv[optind], "batch"))
577 ExecSQLBatch(argv[optind + 1]);
578 else if (!strcmp(argv[optind], "check"))
579 CheckDatabase();
580 else if (!strcmp(argv[optind], "upgrade"))
581 UpgradeDatabase();
582 else if (!strcmp(argv[optind], "unlock"))
583 UnlockDatabase();
584 else if (!strcmp(argv[optind], "reindex"))
585 ReindexDatabase();
586 else if (!strcmp(argv[optind], "export"))
587 ExportDatabase(argv[optind + 1]);
588 else if (!strcmp(argv[optind], "import"))
589 ImportDatabase(argv[optind + 1]);
590 }
591
592 // Shutdown
593 DBDisconnect(g_hCoreDB);
594 DBUnloadDriver();
595 return 0;
596 }