minor changes
[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 hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBFormatVersion'"));
344 if (hResult != NULL)
345 {
346 if (DBGetNumRows(hResult) > 0)
347 nVersion = DBGetFieldLong(hResult, 0, 0);
348 DBFreeResult(hResult);
349 }
350 if (nVersion < DB_FORMAT_VERSION)
351 {
352 _tprintf(_T("Your database has format version %d, this tool is compiled for version %d.\nUse \"upgrade\" command to upgrade your database first.\n"),
353 nVersion, DB_FORMAT_VERSION);
354 return FALSE;
355 }
356 else if (nVersion > DB_FORMAT_VERSION)
357 {
358 _tprintf(_T("Your database has format version %d, this tool is compiled for version %d.\n"
359 "You need to upgrade your server before using this database.\n"),
360 nVersion, DB_FORMAT_VERSION);
361 return FALSE;
362 }
363
364 // Check if database is locked
365 hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBLockStatus'"));
366 if (hResult != NULL)
367 {
368 if (DBGetNumRows(hResult) > 0)
369 {
370 DBGetField(hResult, 0, 0, szLockStatus, MAX_DB_STRING);
371 DecodeSQLString(szLockStatus);
372 bLocked = _tcscmp(szLockStatus, _T("UNLOCKED"));
373 }
374 DBFreeResult(hResult);
375
376 if (bLocked)
377 {
378 hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBLockInfo'"));
379 if (hResult != NULL)
380 {
381 if (DBGetNumRows(hResult) > 0)
382 {
383 DBGetField(hResult, 0, 0, szLockInfo, MAX_DB_STRING);
384 DecodeSQLString(szLockInfo);
385 }
386 DBFreeResult(hResult);
387 }
388 }
389 }
390
391 if (bLocked)
392 {
393 _tprintf(_T("Database is locked by server %s [%s]\n"), szLockStatus, szLockInfo);
394 return FALSE;
395 }
396
397 return TRUE;
398 }
399
400
401 //
402 // Startup
403 //
404
405 int main(int argc, char *argv[])
406 {
407 BOOL bStart = TRUE, bForce = FALSE;
408 int ch;
409 TCHAR szSyntaxId[16], szConfigFile[MAX_PATH] = DEFAULT_CONFIG_FILE;
410 DB_RESULT hResult;
411 #ifdef _WIN32
412 HKEY hKey;
413 DWORD dwSize;
414 #else
415 char *pszEnv;
416 #endif
417
418 InitThreadLibrary();
419
420 printf("NetXMS Database Manager Version " NETXMS_VERSION_STRING "\n\n");
421
422 // Check for alternate config file location
423 #ifdef _WIN32
424 if (RegOpenKeyEx(HKEY_LOCAL_MACHINE, _T("Software\\NetXMS\\Server"), 0,
425 KEY_QUERY_VALUE, &hKey) == ERROR_SUCCESS)
426 {
427 dwSize = MAX_PATH * sizeof(TCHAR);
428 RegQueryValueEx(hKey, _T("ConfigFile"), NULL, NULL, (BYTE *)szConfigFile, &dwSize);
429 RegCloseKey(hKey);
430 }
431 #else
432 pszEnv = getenv("NETXMSD_CONFIG");
433 if (pszEnv != NULL)
434 nx_strncpy(szConfigFile, pszEnv, MAX_PATH);
435 #endif
436
437 // Parse command line
438 opterr = 1;
439 while((ch = getopt(argc, argv, "c:fhIMtvX")) != -1)
440 {
441 switch(ch)
442 {
443 case 'h': // Display help and exit
444 _tprintf(_T("Usage: nxdbmgr [<options>] <command>\n"
445 "Valid commands are:\n"
446 " batch <file> : Run SQL batch file\n"
447 " check : Check database for errors\n"
448 " export <file> : Export database to file\n"
449 " import <file> : Import database from file\n"
450 " init <file> : Initialize database\n"
451 " reindex : Reindex database\n"
452 " unlock : Forced database unlock\n"
453 " upgrade : Upgrade database to new version\n"
454 "Valid options are:\n"
455 " -c <config> : Use alternate configuration file. Default is " DEFAULT_CONFIG_FILE "\n"
456 " -f : Force repair - do not ask for confirmation.\n"
457 " -h : Display help and exit.\n"
458 " -I : MySQL only - specify TYPE=InnoDB for new tables.\n"
459 " -M : MySQL only - specify TYPE=MyISAM for new tables.\n"
460 " -t : Enable trace moded (show executed SQL queries).\n"
461 " -v : Display version and exit.\n"
462 " -X : Ignore SQL errors when upgrading (USE WITH CARE!!!)\n"
463 "\n"));
464 bStart = FALSE;
465 break;
466 case 'v': // Print version and exit
467 bStart = FALSE;
468 break;
469 case 'c':
470 nx_strncpy(szConfigFile, optarg, MAX_PATH);
471 break;
472 case 'f':
473 m_bForce = TRUE;
474 break;
475 case 't':
476 g_bTrace = TRUE;
477 break;
478 case 'I':
479 g_pszTableSuffix = _T(" TYPE=InnoDB");
480 break;
481 case 'M':
482 g_pszTableSuffix = _T(" TYPE=MyISAM");
483 break;
484 case 'X':
485 g_bIgnoreErrors = TRUE;
486 break;
487 case '?':
488 bStart = FALSE;
489 break;
490 default:
491 break;
492 }
493 }
494
495 if (!bStart)
496 return 1;
497
498 // Check parameter correctness
499 if (argc - optind == 0)
500 {
501 _tprintf(_T("Command missing. Type nxdbmgr -h for command line syntax.\n"));
502 return 1;
503 }
504 if (strcmp(argv[optind], "batch") &&
505 strcmp(argv[optind], "check") &&
506 strcmp(argv[optind], "export") &&
507 strcmp(argv[optind], "reindex") &&
508 strcmp(argv[optind], "upgrade") &&
509 strcmp(argv[optind], "unlock") &&
510 strcmp(argv[optind], "init"))
511 {
512 _tprintf(_T("Invalid command \"%s\". Type nxdbmgr -h for command line syntax.\n"), argv[optind]);
513 return 1;
514 }
515 if ((!strcmp(argv[optind], "init") || !strcmp(argv[optind], "batch") || !strcmp(argv[optind], "export")) && (argc - optind < 2))
516 {
517 _tprintf("Required command argument missing\n");
518 return 1;
519 }
520
521 // Read configuration file
522 #if !defined(_WIN32) && !defined(_NETWARE)
523 if (!_tcscmp(szConfigFile, _T("{search}")))
524 {
525 if (access(PREFIX "/etc/netxmsd.conf", 4) == 0)
526 {
527 _tcscpy(szConfigFile, PREFIX "/etc/netxmsd.conf");
528 }
529 else if (access("/usr/etc/netxmsd.conf", 4) == 0)
530 {
531 _tcscpy(szConfigFile, "/usr/etc/netxmsd.conf");
532 }
533 else
534 {
535 _tcscpy(szConfigFile, "/etc/netxmsd.conf");
536 }
537 }
538 #endif
539
540 if (NxLoadConfig(szConfigFile, _T(""), m_cfgTemplate, TRUE) != NXCFG_ERR_OK)
541 {
542 _tprintf(_T("Error loading configuration file\n"));
543 return 2;
544 }
545 #ifndef _WIN32
546 SetDefaultCodepage(m_szCodePage);
547 #endif
548
549 // Connect to database
550 if (!DBInit(FALSE, FALSE, FALSE, NULL))
551 {
552 _tprintf(_T("Unable to load and initialize database driver \"%s\"\n"), g_szDbDriver);
553 return 3;
554 }
555
556 g_hCoreDB = DBConnect();
557 if (g_hCoreDB == NULL)
558 {
559 _tprintf(_T("Unable to connect to database %s@%s as %s\n"), g_szDbName,
560 g_szDbServer, g_szDbLogin);
561 DBUnloadDriver();
562 return 4;
563 }
564
565 if (!strcmp(argv[optind], "init"))
566 {
567 InitDatabase(argv[optind + 1]);
568 }
569 else
570 {
571 // Get database syntax
572 hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBSyntax'"));
573 if (hResult != NULL)
574 {
575 if (DBGetNumRows(hResult) > 0)
576 {
577 DBGetField(hResult, 0, 0, szSyntaxId, sizeof(szSyntaxId));
578 DecodeSQLString(szSyntaxId);
579 }
580 else
581 {
582 _tcscpy(szSyntaxId, _T("UNKNOWN"));
583 }
584 DBFreeResult(hResult);
585 }
586 else
587 {
588 _tprintf(_T("Unable to determine database syntax\n"));
589 DBDisconnect(g_hCoreDB);
590 DBUnloadDriver();
591 return 5;
592 }
593
594 if (!_tcscmp(szSyntaxId, _T("MYSQL")))
595 {
596 g_iSyntax = DB_SYNTAX_MYSQL;
597 }
598 else if (!_tcscmp(szSyntaxId, _T("PGSQL")))
599 {
600 g_iSyntax = DB_SYNTAX_PGSQL;
601 }
602 else if (!_tcscmp(szSyntaxId, _T("MSSQL")))
603 {
604 g_iSyntax = DB_SYNTAX_MSSQL;
605 }
606 else if (!_tcscmp(szSyntaxId, _T("ORACLE")))
607 {
608 g_iSyntax = DB_SYNTAX_ORACLE;
609 }
610 else if (!_tcscmp(szSyntaxId, _T("SQLITE")))
611 {
612 g_iSyntax = DB_SYNTAX_SQLITE;
613 }
614 else
615 {
616 _tprintf(_T("Unknown database syntax %s\n"), szSyntaxId);
617 DBDisconnect(g_hCoreDB);
618 DBUnloadDriver();
619 return 6;
620 }
621
622 // Do requested operation
623 if (!strcmp(argv[optind], "batch"))
624 ExecSQLBatch(argv[optind + 1]);
625 else if (!strcmp(argv[optind], "check"))
626 CheckDatabase();
627 else if (!strcmp(argv[optind], "upgrade"))
628 UpgradeDatabase();
629 else if (!strcmp(argv[optind], "unlock"))
630 UnlockDatabase();
631 else if (!strcmp(argv[optind], "reindex"))
632 ReindexDatabase();
633 else if (!strcmp(argv[optind], "export"))
634 ExportDatabase(argv[optind + 1]);
635 else if (!strcmp(argv[optind], "import"))
636 ImportDatabase(argv[optind + 1]);
637 }
638
639 // Shutdown
640 DBDisconnect(g_hCoreDB);
641 DBUnloadDriver();
642 return 0;
643 }