export almost working; DB driver API change: column names now returned as char*,...
[public/netxms.git] / src / server / tools / nxdbmgr / nxdbmgr.cpp
CommitLineData
5039dede
AK
1/*
2** nxdbmgr - NetXMS database manager
1e558daf 3** Copyright (C) 2004-2009 Victor Kirhenshtein
5039dede
AK
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
34DB_HANDLE g_hCoreDB;
35BOOL g_bIgnoreErrors = FALSE;
36BOOL g_bTrace = FALSE;
37int g_iSyntax;
38const TCHAR *g_pszTableSuffix = _T("");
39const 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
53static TCHAR m_szCodePage[MAX_PATH] = ICONV_DEFAULT_CODEPAGE;
54static 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};
70static BOOL m_bForce = FALSE;
71
72
73//
74// Show query if trace mode is ON
75//
76
77void 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
95BOOL 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 {
5036c088 109 ch = _getch();
5039dede
AK
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
137DB_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 query and print error message on screen if query failed
163//
164
165BOOL SQLQuery(const TCHAR *pszQuery)
166{
167 BOOL bResult;
168 TCHAR errorText[DBDRV_MAX_ERROR_TEXT];
169
170 if (*pszQuery == 0)
171 return TRUE;
172
173 if (g_bTrace)
174 ShowQuery(pszQuery);
175
176 bResult = DBQueryEx(g_hCoreDB, pszQuery, errorText);
177 if (!bResult)
178 {
179#ifdef _WIN32
180 _tprintf(_T("SQL query failed (%s):\n"), errorText);
181 SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 0x0E);
182 _tprintf(_T("%s\n"), pszQuery);
183 SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 0x07);
184#else
185 _tprintf(_T("SQL query failed (%s):\n%s\n"), errorText, pszQuery);
186#endif
187 }
188 return bResult;
189}
190
191
192//
193// Execute SQL batch
194//
195
196BOOL SQLBatch(const TCHAR *pszBatch)
197{
198 TCHAR *pszBuffer, *pszQuery, *ptr;
199 TCHAR errorText[DBDRV_MAX_ERROR_TEXT];
200 BOOL bRet = TRUE;
201
202 pszBuffer = _tcsdup(pszBatch);
203 TranslateStr(pszBuffer, _T("$SQL:TEXT"), g_pszSqlType[g_iSyntax][SQL_TYPE_TEXT]);
204 TranslateStr(pszBuffer, _T("$SQL:INT64"), g_pszSqlType[g_iSyntax][SQL_TYPE_INT64]);
205
206 pszQuery = pszBuffer;
207 while(1)
208 {
209 ptr = _tcschr(pszQuery, _T('\n'));
210 if (ptr != NULL)
211 *ptr = 0;
212 if (!_tcscmp(pszQuery, _T("<END>")))
213 break;
214
215 if (g_bTrace)
216 ShowQuery(pszQuery);
217
218 if (!DBQueryEx(g_hCoreDB, pszQuery, errorText))
219 {
220#ifdef _WIN32
221 _tprintf(_T("SQL query failed (%s):\n"), errorText);
222 SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 0x0E);
223 _tprintf(_T("%s\n"), pszQuery);
224 SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 0x07);
225#else
226 _tprintf(_T("SQL query failed (%s):\n%s\n"), errorText, pszQuery);
227#endif
228 if (!g_bIgnoreErrors)
229 {
230 bRet = FALSE;
231 break;
232 }
233 }
234 ptr++;
235 pszQuery = ptr;
236 }
237 free(pszBuffer);
238 return bRet;
239}
240
241
242//
243// Read string value from configuration table
244//
245
246BOOL ConfigReadStr(const TCHAR *pszVar, TCHAR *pszBuffer, int iBufSize, const TCHAR *pszDefault)
247{
248 DB_RESULT hResult;
249 TCHAR szQuery[256];
250 BOOL bSuccess = FALSE;
251
252 nx_strncpy(pszBuffer, pszDefault, iBufSize);
253 if (_tcslen(pszVar) > 127)
254 return FALSE;
255
256 _sntprintf(szQuery, 256, _T("SELECT var_value FROM config WHERE var_name='%s'"), pszVar);
257 hResult = SQLSelect(szQuery);
258 if (hResult == 0)
259 return FALSE;
260
261 if (DBGetNumRows(hResult) > 0)
262 {
263 DBGetField(hResult, 0, 0, pszBuffer, iBufSize);
264 DecodeSQLString(pszBuffer);
265 bSuccess = TRUE;
266 }
267
268 DBFreeResult(hResult);
269 return bSuccess;
270}
271
272
273//
274// Read integer value from configuration table
275//
276
277int ConfigReadInt(const TCHAR *pszVar, int iDefault)
278{
279 TCHAR szBuffer[64];
280
281 if (ConfigReadStr(pszVar, szBuffer, 64, _T("")))
282 return _tcstol(szBuffer, NULL, 0);
283 else
284 return iDefault;
285}
286
287
288//
289// Read unsigned long value from configuration table
290//
291
292DWORD ConfigReadULong(const TCHAR *pszVar, DWORD dwDefault)
293{
294 TCHAR szBuffer[64];
295
296 if (ConfigReadStr(pszVar, szBuffer, 64, _T("")))
297 return _tcstoul(szBuffer, NULL, 0);
298 else
299 return dwDefault;
300}
301
302
1e558daf
VK
303//
304// Check that database has correct schema version and is not locked
305//
306
307BOOL ValidateDatabase()
308{
309 DB_RESULT hResult;
310 LONG nVersion = 0;
311 BOOL bLocked;
312 TCHAR szLockStatus[MAX_DB_STRING], szLockInfo[MAX_DB_STRING];
313
314 // Get database format version
315 hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBFormatVersion'"));
316 if (hResult != NULL)
317 {
318 if (DBGetNumRows(hResult) > 0)
319 nVersion = DBGetFieldLong(hResult, 0, 0);
320 DBFreeResult(hResult);
321 }
322 if (nVersion < DB_FORMAT_VERSION)
323 {
324 _tprintf(_T("Your database has format version %d, this tool is compiled for version %d.\nUse \"upgrade\" command to upgrade your database first.\n"),
325 nVersion, DB_FORMAT_VERSION);
326 return FALSE;
327 }
328 else if (nVersion > DB_FORMAT_VERSION)
329 {
330 _tprintf(_T("Your database has format version %d, this tool is compiled for version %d.\n"
331 "You need to upgrade your server before using this database.\n"),
332 nVersion, DB_FORMAT_VERSION);
333 return FALSE;
334 }
335
336 // Check if database is locked
337 hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBLockStatus'"));
338 if (hResult != NULL)
339 {
340 if (DBGetNumRows(hResult) > 0)
341 {
342 DBGetField(hResult, 0, 0, szLockStatus, MAX_DB_STRING);
343 DecodeSQLString(szLockStatus);
344 bLocked = _tcscmp(szLockStatus, _T("UNLOCKED"));
345 }
346 DBFreeResult(hResult);
347
348 if (bLocked)
349 {
350 hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBLockInfo'"));
351 if (hResult != NULL)
352 {
353 if (DBGetNumRows(hResult) > 0)
354 {
355 DBGetField(hResult, 0, 0, szLockInfo, MAX_DB_STRING);
356 DecodeSQLString(szLockInfo);
357 }
358 DBFreeResult(hResult);
359 }
360 }
361 }
362
363 if (bLocked)
364 {
365 _tprintf(_T("Database is locked by server %s [%s]\n"), szLockStatus, szLockInfo);
366 return FALSE;
367 }
368
369 return TRUE;
370}
371
372
5039dede
AK
373//
374// Startup
375//
376
377int main(int argc, char *argv[])
378{
379 BOOL bStart = TRUE, bForce = FALSE;
380 int ch;
381 TCHAR szSyntaxId[16], szConfigFile[MAX_PATH] = DEFAULT_CONFIG_FILE;
382 DB_RESULT hResult;
383#ifdef _WIN32
384 HKEY hKey;
385 DWORD dwSize;
386#else
387 char *pszEnv;
388#endif
389
390 InitThreadLibrary();
391
392 printf("NetXMS Database Manager Version " NETXMS_VERSION_STRING "\n\n");
393
394 // Check for alternate config file location
395#ifdef _WIN32
396 if (RegOpenKeyEx(HKEY_LOCAL_MACHINE, _T("Software\\NetXMS\\Server"), 0,
397 KEY_QUERY_VALUE, &hKey) == ERROR_SUCCESS)
398 {
399 dwSize = MAX_PATH * sizeof(TCHAR);
400 RegQueryValueEx(hKey, _T("ConfigFile"), NULL, NULL, (BYTE *)szConfigFile, &dwSize);
401 RegCloseKey(hKey);
402 }
403#else
404 pszEnv = getenv("NETXMSD_CONFIG");
405 if (pszEnv != NULL)
406 nx_strncpy(szConfigFile, pszEnv, MAX_PATH);
407#endif
408
409 // Parse command line
410 opterr = 1;
411 while((ch = getopt(argc, argv, "c:fhIMtvX")) != -1)
412 {
413 switch(ch)
414 {
415 case 'h': // Display help and exit
416 _tprintf(_T("Usage: nxdbmgr [<options>] <command>\n"
417 "Valid commands are:\n"
4f23eecc
VK
418 " batch <file> : Run SQL batch file\n"
419 " check : Check database for errors\n"
420 " export <file> : Export database to file\n"
421 " init <file> : Initialize database\n"
422 " reindex : Reindex database\n"
423 " unlock : Forced database unlock\n"
424 " upgrade : Upgrade database to new version\n"
5039dede 425 "Valid options are:\n"
4f23eecc
VK
426 " -c <config> : Use alternate configuration file. Default is " DEFAULT_CONFIG_FILE "\n"
427 " -f : Force repair - do not ask for confirmation.\n"
428 " -h : Display help and exit.\n"
429 " -I : MySQL only - specify TYPE=InnoDB for new tables.\n"
430 " -M : MySQL only - specify TYPE=MyISAM for new tables.\n"
431 " -t : Enable trace moded (show executed SQL queries).\n"
432 " -v : Display version and exit.\n"
433 " -X : Ignore SQL errors when upgrading (USE WITH CARE!!!)\n"
5039dede
AK
434 "\n"));
435 bStart = FALSE;
436 break;
437 case 'v': // Print version and exit
438 bStart = FALSE;
439 break;
440 case 'c':
441 nx_strncpy(szConfigFile, optarg, MAX_PATH);
442 break;
443 case 'f':
444 m_bForce = TRUE;
445 break;
446 case 't':
447 g_bTrace = TRUE;
448 break;
449 case 'I':
450 g_pszTableSuffix = _T(" TYPE=InnoDB");
451 break;
452 case 'M':
453 g_pszTableSuffix = _T(" TYPE=MyISAM");
454 break;
455 case 'X':
456 g_bIgnoreErrors = TRUE;
457 break;
458 case '?':
459 bStart = FALSE;
460 break;
461 default:
462 break;
463 }
464 }
465
466 if (!bStart)
467 return 1;
468
469 // Check parameter correctness
470 if (argc - optind == 0)
471 {
472 _tprintf(_T("Command missing. Type nxdbmgr -h for command line syntax.\n"));
473 return 1;
474 }
475 if (strcmp(argv[optind], "batch") &&
476 strcmp(argv[optind], "check") &&
4f23eecc 477 strcmp(argv[optind], "export") &&
5039dede
AK
478 strcmp(argv[optind], "reindex") &&
479 strcmp(argv[optind], "upgrade") &&
480 strcmp(argv[optind], "unlock") &&
481 strcmp(argv[optind], "init"))
482 {
483 _tprintf(_T("Invalid command \"%s\". Type nxdbmgr -h for command line syntax.\n"), argv[optind]);
484 return 1;
485 }
4f23eecc 486 if ((!strcmp(argv[optind], "init") || !strcmp(argv[optind], "batch") || !strcmp(argv[optind], "export")) && (argc - optind < 2))
5039dede
AK
487 {
488 _tprintf("Required command argument missing\n");
489 return 1;
490 }
491
492 // Read configuration file
493#if !defined(_WIN32) && !defined(_NETWARE)
494 if (!_tcscmp(szConfigFile, _T("{search}")))
495 {
496 if (access(PREFIX "/etc/netxmsd.conf", 4) == 0)
497 {
498 _tcscpy(szConfigFile, PREFIX "/etc/netxmsd.conf");
499 }
500 else if (access("/usr/etc/netxmsd.conf", 4) == 0)
501 {
502 _tcscpy(szConfigFile, "/usr/etc/netxmsd.conf");
503 }
504 else
505 {
506 _tcscpy(szConfigFile, "/etc/netxmsd.conf");
507 }
508 }
509#endif
510
511 if (NxLoadConfig(szConfigFile, _T(""), m_cfgTemplate, TRUE) != NXCFG_ERR_OK)
512 {
513 _tprintf(_T("Error loading configuration file\n"));
514 return 2;
515 }
516#ifndef _WIN32
517 SetDefaultCodepage(m_szCodePage);
518#endif
519
520 // Connect to database
521 if (!DBInit(FALSE, FALSE, FALSE, NULL))
522 {
523 _tprintf(_T("Unable to load and initialize database driver \"%s\"\n"), g_szDbDriver);
524 return 3;
525 }
526
527 g_hCoreDB = DBConnect();
528 if (g_hCoreDB == NULL)
529 {
530 _tprintf(_T("Unable to connect to database %s@%s as %s\n"), g_szDbName,
531 g_szDbServer, g_szDbLogin);
532 DBUnloadDriver();
533 return 4;
534 }
535
536 if (!strcmp(argv[optind], "init"))
537 {
538 InitDatabase(argv[optind + 1]);
539 }
540 else
541 {
542 // Get database syntax
543 hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBSyntax'"));
544 if (hResult != NULL)
545 {
546 if (DBGetNumRows(hResult) > 0)
547 {
548 DBGetField(hResult, 0, 0, szSyntaxId, sizeof(szSyntaxId));
549 DecodeSQLString(szSyntaxId);
550 }
551 else
552 {
553 _tcscpy(szSyntaxId, _T("UNKNOWN"));
554 }
555 DBFreeResult(hResult);
556 }
557 else
558 {
559 _tprintf(_T("Unable to determine database syntax\n"));
560 DBDisconnect(g_hCoreDB);
561 DBUnloadDriver();
562 return 5;
563 }
564
565 if (!_tcscmp(szSyntaxId, _T("MYSQL")))
566 {
567 g_iSyntax = DB_SYNTAX_MYSQL;
568 }
569 else if (!_tcscmp(szSyntaxId, _T("PGSQL")))
570 {
571 g_iSyntax = DB_SYNTAX_PGSQL;
572 }
573 else if (!_tcscmp(szSyntaxId, _T("MSSQL")))
574 {
575 g_iSyntax = DB_SYNTAX_MSSQL;
576 }
577 else if (!_tcscmp(szSyntaxId, _T("ORACLE")))
578 {
579 g_iSyntax = DB_SYNTAX_ORACLE;
580 }
581 else if (!_tcscmp(szSyntaxId, _T("SQLITE")))
582 {
583 g_iSyntax = DB_SYNTAX_SQLITE;
584 }
585 else
586 {
587 _tprintf(_T("Unknown database syntax %s\n"), szSyntaxId);
588 DBDisconnect(g_hCoreDB);
589 DBUnloadDriver();
590 return 6;
591 }
592
593 // Do requested operation
594 if (!strcmp(argv[optind], "batch"))
595 ExecSQLBatch(argv[optind + 1]);
596 else if (!strcmp(argv[optind], "check"))
597 CheckDatabase();
598 else if (!strcmp(argv[optind], "upgrade"))
599 UpgradeDatabase();
600 else if (!strcmp(argv[optind], "unlock"))
601 UnlockDatabase();
602 else if (!strcmp(argv[optind], "reindex"))
603 ReindexDatabase();
1e558daf 604 else if (!strcmp(argv[optind], "export"))
4f23eecc 605 ExportDatabase(argv[optind + 1]);
5039dede
AK
606 }
607
608 // Shutdown
609 DBDisconnect(g_hCoreDB);
610 DBUnloadDriver();
611 return 0;
612}