schema-related information (like schema version, DB syntax, etc.) moved from "config...
[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);
4c4c9b03
VK
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
165DB_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);
5039dede
AK
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
193BOOL 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
224BOOL 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
274BOOL 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
305int 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
320DWORD 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
1e558daf
VK
331//
332// Check that database has correct schema version and is not locked
333//
334
335BOOL 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
28f5b9a4 343 nVersion = DBGetSchemaVersion(g_hCoreDB);
1e558daf
VK
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
5039dede
AK
395//
396// Startup
397//
398
399int main(int argc, char *argv[])
400{
401 BOOL bStart = TRUE, bForce = FALSE;
402 int ch;
28f5b9a4 403 TCHAR szConfigFile[MAX_PATH] = DEFAULT_CONFIG_FILE;
5039dede
AK
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"
4f23eecc
VK
439 " batch <file> : Run SQL batch file\n"
440 " check : Check database for errors\n"
441 " export <file> : Export database to file\n"
47912c44 442 " import <file> : Import database from file\n"
4f23eecc
VK
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"
5039dede 447 "Valid options are:\n"
4f23eecc
VK
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"
5039dede
AK
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") &&
4f23eecc 499 strcmp(argv[optind], "export") &&
28f5b9a4 500 strcmp(argv[optind], "import") &&
5039dede
AK
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 }
28f5b9a4 509 if ((!strcmp(argv[optind], "init") || !strcmp(argv[optind], "batch") || !strcmp(argv[optind], "export") || !strcmp(argv[optind], "import")) && (argc - optind < 2))
5039dede
AK
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
28f5b9a4
VK
566 g_iSyntax = DBGetSyntax(g_hCoreDB);
567 if (g_iSyntax == DB_SYNTAX_UNKNOWN)
568 {
5039dede
AK
569 _tprintf(_T("Unable to determine database syntax\n"));
570 DBDisconnect(g_hCoreDB);
571 DBUnloadDriver();
572 return 5;
573 }
574
5039dede
AK
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();
1e558daf 586 else if (!strcmp(argv[optind], "export"))
4f23eecc 587 ExportDatabase(argv[optind + 1]);
890a0930
VK
588 else if (!strcmp(argv[optind], "import"))
589 ImportDatabase(argv[optind + 1]);
5039dede
AK
590 }
591
592 // Shutdown
593 DBDisconnect(g_hCoreDB);
594 DBUnloadDriver();
595 return 0;
596}