2 ** nxdbmgr - NetXMS database manager
3 ** Copyright (C) 2004-2013 Victor Kirhenshtein
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.
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.
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.
34 BOOL g_bIgnoreErrors
= FALSE
;
35 BOOL g_bTrace
= FALSE
;
36 bool g_isGuiMode
= false;
37 bool g_checkData
= false;
39 const TCHAR
*g_pszTableSuffix
= _T("");
40 const TCHAR
*g_pszSqlType
[6][3] =
42 { _T("text"), _T("text"), _T("bigint") }, // MySQL
43 { _T("text"), _T("varchar(4000)"), _T("bigint") }, // PostgreSQL
44 { _T("text"), _T("varchar(4000)"), _T("bigint") }, // Microsoft SQL
45 { _T("clob"), _T("varchar(4000)"), _T("number(20)") }, // Oracle
46 { _T("varchar"), _T("varchar(4000)"), _T("number(20)") }, // SQLite
47 { _T("long varchar"), _T("varchar(4000)"), _T("bigint") } // DB/2
53 static char m_szCodePage
[MAX_PATH
] = ICONV_DEFAULT_CODEPAGE_A
;
54 static TCHAR s_encryptedDbPassword
[MAX_DB_STRING
] = _T("");
55 static TCHAR s_dbDriver
[MAX_PATH
] = _T("");
56 static TCHAR s_dbDrvParams
[MAX_PATH
] = _T("");
57 static TCHAR s_dbServer
[MAX_PATH
] = _T("127.0.0.1");
58 static TCHAR s_dbLogin
[MAX_DB_LOGIN
] = _T("netxms");
59 static TCHAR s_dbPassword
[MAX_DB_PASSWORD
] = _T("");
60 static TCHAR s_dbName
[MAX_DB_NAME
] = _T("netxms_db");
61 static TCHAR s_dbSchema
[MAX_DB_NAME
] = _T("");
62 static NX_CFG_TEMPLATE m_cfgTemplate
[] =
64 { _T("CodePage"), CT_MB_STRING
, 0, 0, MAX_PATH
, 0, m_szCodePage
},
65 { _T("CreateCrashDumps"), CT_IGNORE
, 0, 0, 0, 0, NULL
},
66 { _T("DBDriver"), CT_STRING
, 0, 0, MAX_PATH
, 0, s_dbDriver
},
67 { _T("DBDrvParams"), CT_STRING
, 0, 0, MAX_PATH
, 0, s_dbDrvParams
},
68 { _T("DBEncryptedPassword"), CT_STRING
, 0, 0, MAX_DB_STRING
, 0, s_encryptedDbPassword
},
69 { _T("DBLogin"), CT_STRING
, 0, 0, MAX_DB_LOGIN
, 0, s_dbLogin
},
70 { _T("DBName"), CT_STRING
, 0, 0, MAX_DB_NAME
, 0, s_dbName
},
71 { _T("DBPassword"), CT_STRING
, 0, 0, MAX_DB_PASSWORD
, 0, s_dbPassword
},
72 { _T("DBSchema"), CT_STRING
, 0, 0, MAX_PATH
, 0, s_dbSchema
},
73 { _T("DBServer"), CT_STRING
, 0, 0, MAX_PATH
, 0, s_dbServer
},
74 { _T("DataDirectory"), CT_IGNORE
, 0, 0, 0, 0, NULL
},
75 { _T("DumpDirectory"), CT_IGNORE
, 0, 0, 0, 0, NULL
},
76 { _T("LogFailedSQLQueries"), CT_IGNORE
, 0, 0, 0, 0, NULL
},
77 { _T("LogFile"), CT_IGNORE
, 0, 0, 0, 0, NULL
},
78 { _T("Module"), CT_IGNORE
, 0, 0, 0, 0, NULL
},
79 { _T(""), CT_END_OF_LIST
, 0, 0, 0, 0, NULL
}
81 static BOOL m_bForce
= FALSE
;
84 * Show query if trace mode is ON
86 void ShowQuery(const TCHAR
*pszQuery
)
88 WriteToTerminalEx(_T("\x1b[1m>>> \x1b[32;1m%s\x1b[0m\n"), pszQuery
);
92 * Get Yes or No answer from keyboard
94 bool GetYesNo(const TCHAR
*format
, ...)
104 va_start(args
, format
);
105 _vsntprintf(message
, 4096, format
, args
);
109 return MessageBox(NULL
, message
, _T("NetXMS Database Manager"), MB_YESNO
| MB_ICONQUESTION
) == IDYES
;
116 va_start(args
, format
);
117 _vtprintf(format
, args
);
119 _tprintf(_T(" (Y/N) "));
134 if ((ch
== 'y') || (ch
== 'Y'))
139 if ((ch
== 'n') || (ch
== 'N'))
149 _fgetts(szBuffer
, 16, stdin
);
151 return ((szBuffer
[0] == 'y') || (szBuffer
[0] == 'Y'));
158 * Execute SQL SELECT query and print error message on screen if query failed
160 DB_RESULT
SQLSelect(const TCHAR
*pszQuery
)
163 TCHAR errorText
[DBDRV_MAX_ERROR_TEXT
];
168 hResult
= DBSelectEx(g_hCoreDB
, pszQuery
, errorText
);
170 WriteToTerminalEx(_T("SQL query failed (%s):\n\x1b[33;1m%s\x1b[0m\n"), errorText
, pszQuery
);
175 * Execute SQL SELECT query via DBAsyncSelect and print error message on screen if query failed
177 DB_ASYNC_RESULT
SQLAsyncSelect(const TCHAR
*pszQuery
)
179 DB_ASYNC_RESULT hResult
;
180 TCHAR errorText
[DBDRV_MAX_ERROR_TEXT
];
185 hResult
= DBAsyncSelectEx(g_hCoreDB
, pszQuery
, errorText
);
187 WriteToTerminalEx(_T("SQL query failed (%s):\n\x1b[33;1m%s\x1b[0m\n"), errorText
, pszQuery
);
192 * Execute SQL query and print error message on screen if query failed
194 BOOL
SQLQuery(const TCHAR
*pszQuery
)
197 TCHAR errorText
[DBDRV_MAX_ERROR_TEXT
];
202 String
query(pszQuery
);
204 query
.replace(_T("$SQL:TEXT"), g_pszSqlType
[g_iSyntax
][SQL_TYPE_TEXT
]);
205 query
.replace(_T("$SQL:TXT4K"), g_pszSqlType
[g_iSyntax
][SQL_TYPE_TEXT4K
]);
206 query
.replace(_T("$SQL:INT64"), g_pszSqlType
[g_iSyntax
][SQL_TYPE_INT64
]);
211 bResult
= DBQueryEx(g_hCoreDB
, (const TCHAR
*)query
, errorText
);
213 WriteToTerminalEx(_T("SQL query failed (%s):\n\x1b[33;1m%s\x1b[0m\n"), errorText
, (const TCHAR
*)query
);
220 BOOL
SQLBatch(const TCHAR
*pszBatch
)
222 String
batch(pszBatch
);
223 TCHAR
*pszBuffer
, *pszQuery
, *ptr
;
224 TCHAR errorText
[DBDRV_MAX_ERROR_TEXT
];
226 TCHAR table
[128], column
[128];
228 batch
.replace(_T("$SQL:TEXT"), g_pszSqlType
[g_iSyntax
][SQL_TYPE_TEXT
]);
229 batch
.replace(_T("$SQL:TXT4K"), g_pszSqlType
[g_iSyntax
][SQL_TYPE_TEXT4K
]);
230 batch
.replace(_T("$SQL:INT64"), g_pszSqlType
[g_iSyntax
][SQL_TYPE_INT64
]);
232 pszQuery
= pszBuffer
= batch
.getBuffer();
235 ptr
= _tcschr(pszQuery
, _T('\n'));
238 if (!_tcscmp(pszQuery
, _T("<END>")))
241 if (_stscanf(pszQuery
, _T("ALTER TABLE %128s DROP COLUMN %128s"), table
, column
) == 2)
243 if (!SQLDropColumn(table
, column
))
245 WriteToTerminalEx(_T("Cannot drop column \x1b[37;1m%s.%s\x1b[0m\n"), table
, column
);
246 if (!g_bIgnoreErrors
)
258 if (!DBQueryEx(g_hCoreDB
, pszQuery
, errorText
))
260 WriteToTerminalEx(_T("SQL query failed (%s):\n\x1b[33;1m%s\x1b[0m\n"), errorText
, pszQuery
);
261 if (!g_bIgnoreErrors
)
276 * Drop column from the table
278 BOOL
SQLDropColumn(const TCHAR
*table
, const TCHAR
*column
)
282 BOOL success
= FALSE
;
284 if (g_iSyntax
!= DB_SYNTAX_SQLITE
)
286 _sntprintf(query
, 1024, _T("ALTER TABLE %s DROP COLUMN %s"), table
, column
);
287 success
= SQLQuery(query
);
291 _sntprintf(query
, 1024, _T("PRAGMA TABLE_INFO('%s')"), table
);
292 hResult
= SQLSelect(query
);
295 int rows
= DBGetNumRows(hResult
);
296 const int blen
= 2048;
298 // Intermediate buffers for SQLs
299 TCHAR columnList
[1024], createList
[1024];
300 // TABLE_INFO() columns
301 TCHAR tabColName
[128], tabColType
[64], tabColNull
[10], tabColDefault
[128];
302 columnList
[0] = createList
[0] = _T('\0');
303 for (int i
= 0; i
< rows
; i
++)
305 DBGetField(hResult
, i
, 1, tabColName
, 128);
306 DBGetField(hResult
, i
, 2, tabColType
, 64);
307 DBGetField(hResult
, i
, 3, tabColNull
, 10);
308 DBGetField(hResult
, i
, 4, tabColDefault
, 128);
309 if (_tcsnicmp(tabColName
, column
, 128))
311 _tcscat(columnList
, tabColName
);
312 if (columnList
[0] != _T('\0'))
313 _tcscat(columnList
, _T(","));
314 _tcscat(createList
, tabColName
);
315 _tcscat(createList
, tabColType
);
316 if (tabColDefault
[0] != _T('\0'))
318 _tcscat(createList
, _T("DEFAULT "));
319 _tcscat(createList
, tabColDefault
);
321 if (tabColNull
[0] == _T('1'))
322 _tcscat(createList
, _T(" NOT NULL"));
323 _tcscat(createList
, _T(","));
326 DBFreeResult(hResult
);
329 int cllen
= (int)_tcslen(columnList
);
330 if (cllen
> 0 && columnList
[cllen
- 1] == _T(','))
331 columnList
[cllen
- 1] = _T('\0');
332 // TODO: figure out if SQLite transactions will work here
333 _sntprintf(buffer
, blen
, _T("CREATE TABLE %s__backup__ (%s)"), table
, columnList
);
334 CHK_EXEC(SQLQuery(buffer
));
335 _sntprintf(buffer
, blen
, _T("INSERT INTO %s__backup__ (%s) SELECT %s FROM %s"),
336 table
, columnList
, columnList
, table
);
337 CHK_EXEC(SQLQuery(buffer
));
338 _sntprintf(buffer
, blen
, _T("DROP TABLE %s"), table
);
339 CHK_EXEC(SQLQuery(buffer
));
340 _sntprintf(buffer
, blen
, _T("ALTER TABLE %s__backup__ RENAME to %s"), table
, table
);
341 CHK_EXEC(SQLQuery(buffer
));
347 // TODO: preserve indices and constraints??
353 * Read string value from metadata table
355 BOOL
MetaDataReadStr(const TCHAR
*pszVar
, TCHAR
*pszBuffer
, int iBufSize
, const TCHAR
*pszDefault
)
359 BOOL bSuccess
= FALSE
;
361 nx_strncpy(pszBuffer
, pszDefault
, iBufSize
);
362 if (_tcslen(pszVar
) > 127)
365 _sntprintf(szQuery
, 256, _T("SELECT var_value FROM metadata WHERE var_name='%s'"), pszVar
);
366 hResult
= SQLSelect(szQuery
);
370 if (DBGetNumRows(hResult
) > 0)
372 DBGetField(hResult
, 0, 0, pszBuffer
, iBufSize
);
376 DBFreeResult(hResult
);
381 * Read string value from configuration table
383 BOOL
ConfigReadStr(const TCHAR
*pszVar
, TCHAR
*pszBuffer
, int iBufSize
, const TCHAR
*pszDefault
)
387 BOOL bSuccess
= FALSE
;
389 nx_strncpy(pszBuffer
, pszDefault
, iBufSize
);
390 if (_tcslen(pszVar
) > 127)
393 _sntprintf(szQuery
, 256, _T("SELECT var_value FROM config WHERE var_name='%s'"), pszVar
);
394 hResult
= SQLSelect(szQuery
);
398 if (DBGetNumRows(hResult
) > 0)
400 DBGetField(hResult
, 0, 0, pszBuffer
, iBufSize
);
404 DBFreeResult(hResult
);
410 // Read integer value from configuration table
413 int ConfigReadInt(const TCHAR
*pszVar
, int iDefault
)
417 if (ConfigReadStr(pszVar
, szBuffer
, 64, _T("")))
418 return _tcstol(szBuffer
, NULL
, 0);
425 // Read unsigned long value from configuration table
428 DWORD
ConfigReadULong(const TCHAR
*pszVar
, DWORD dwDefault
)
432 if (ConfigReadStr(pszVar
, szBuffer
, 64, _T("")))
433 return _tcstoul(szBuffer
, NULL
, 0);
440 // Check that database has correct schema version and is not locked
443 BOOL
ValidateDatabase()
447 BOOL bLocked
= FALSE
;
448 TCHAR szLockStatus
[MAX_DB_STRING
], szLockInfo
[MAX_DB_STRING
];
450 // Get database format version
451 nVersion
= DBGetSchemaVersion(g_hCoreDB
);
452 if (nVersion
< DB_FORMAT_VERSION
)
454 _tprintf(_T("Your database has format version %d, this tool is compiled for version %d.\nUse \"upgrade\" command to upgrade your database first.\n"),
455 nVersion
, DB_FORMAT_VERSION
);
458 else if (nVersion
> DB_FORMAT_VERSION
)
460 _tprintf(_T("Your database has format version %d, this tool is compiled for version %d.\n")
461 _T("You need to upgrade your server before using this database.\n"),
462 nVersion
, DB_FORMAT_VERSION
);
466 // Check if database is locked
467 hResult
= DBSelect(g_hCoreDB
, _T("SELECT var_value FROM config WHERE var_name='DBLockStatus'"));
470 if (DBGetNumRows(hResult
) > 0)
472 DBGetField(hResult
, 0, 0, szLockStatus
, MAX_DB_STRING
);
473 bLocked
= _tcscmp(szLockStatus
, _T("UNLOCKED"));
475 DBFreeResult(hResult
);
479 hResult
= DBSelect(g_hCoreDB
, _T("SELECT var_value FROM config WHERE var_name='DBLockInfo'"));
482 if (DBGetNumRows(hResult
) > 0)
484 DBGetField(hResult
, 0, 0, szLockInfo
, MAX_DB_STRING
);
486 DBFreeResult(hResult
);
493 _tprintf(_T("Database is locked by server %s [%s]\n"), szLockStatus
, szLockInfo
);
505 int main(int argc
, char *argv
[])
507 BOOL bStart
= TRUE
, bForce
= FALSE
, bQuiet
= FALSE
, bReplaceValue
= TRUE
;
509 TCHAR szConfigFile
[MAX_PATH
] = DEFAULT_CONFIG_FILE
;
519 // Check for alternate config file location
521 if (RegOpenKeyEx(HKEY_LOCAL_MACHINE
, _T("Software\\NetXMS\\Server"), 0,
522 KEY_QUERY_VALUE
, &hKey
) == ERROR_SUCCESS
)
524 dwSize
= MAX_PATH
* sizeof(TCHAR
);
525 RegQueryValueEx(hKey
, _T("ConfigFile"), NULL
, NULL
, (BYTE
*)szConfigFile
, &dwSize
);
529 pszEnv
= _tgetenv(_T("NETXMSD_CONFIG"));
531 nx_strncpy(szConfigFile
, pszEnv
, MAX_PATH
);
534 // Parse command line
536 while((ch
= getopt(argc
, argv
, "c:dfGhIMNqtvX")) != -1)
540 case 'h': // Display help and exit
541 _tprintf(_T("NetXMS Database Manager Version ") NETXMS_VERSION_STRING
_T("\n\n"));
542 _tprintf(_T("Usage: nxdbmgr [<options>] <command>\n")
543 _T("Valid commands are:\n")
544 _T(" batch <file> : Run SQL batch file\n")
545 _T(" check : Check database for errors\n")
546 _T(" export <file> : Export database to file\n")
547 _T(" get <name> : Get value of server configuration variable\n")
548 _T(" import <file> : Import database from file\n")
549 _T(" init <file> : Initialize database\n")
550 _T(" reindex : Reindex database\n")
551 _T(" set <name> <value> : Set value of server configuration variable\n")
552 _T(" unlock : Forced database unlock\n")
553 _T(" upgrade : Upgrade database to new version\n")
554 _T(" resetadmin : Unlock user \"admin\" and reset password to default (\"netxms\")\n")
555 _T("Valid options are:\n")
556 _T(" -c <config> : Use alternate configuration file. Default is ") DEFAULT_CONFIG_FILE
_T("\n")
557 _T(" -d : Check collected data (may take very long time).\n")
558 _T(" -f : Force repair - do not ask for confirmation.\n")
560 _T(" -G : GUI mode.\n")
562 _T(" -h : Display help and exit.\n")
563 _T(" -I : MySQL only - specify TYPE=InnoDB for new tables.\n")
564 _T(" -M : MySQL only - specify TYPE=MyISAM for new tables.\n")
565 _T(" -N : Do not replace existing configuration value (\"set\" command only).\n")
566 _T(" -q : Quiet mode (don't show startup banner).\n")
567 _T(" -t : Enable trace mode (show executed SQL queries).\n")
568 _T(" -v : Display version and exit.\n")
569 _T(" -X : Ignore SQL errors when upgrading (USE WITH CAUTION!!!)\n")
573 case 'v': // Print version and exit
574 _tprintf(_T("NetXMS Database Manager Version ") NETXMS_VERSION_STRING
_T("\n\n"));
579 MultiByteToWideChar(CP_ACP
, MB_PRECOMPOSED
, optarg
, -1, szConfigFile
, MAX_PATH
);
580 szConfigFile
[MAX_PATH
- 1] = 0;
582 nx_strncpy(szConfigFile
, optarg
, MAX_PATH
);
595 bReplaceValue
= FALSE
;
604 g_pszTableSuffix
= _T(" TYPE=InnoDB");
607 g_pszTableSuffix
= _T(" TYPE=MyISAM");
610 g_bIgnoreErrors
= TRUE
;
624 _tprintf(_T("NetXMS Database Manager Version ") NETXMS_VERSION_STRING
_T("\n\n"));
626 // Check parameter correctness
627 if (argc
- optind
== 0)
629 _tprintf(_T("Command missing. Type nxdbmgr -h for command line syntax.\n"));
632 if (strcmp(argv
[optind
], "batch") &&
633 strcmp(argv
[optind
], "check") &&
634 strcmp(argv
[optind
], "export") &&
635 strcmp(argv
[optind
], "get") &&
636 strcmp(argv
[optind
], "import") &&
637 strcmp(argv
[optind
], "init") &&
638 strcmp(argv
[optind
], "reindex") &&
639 strcmp(argv
[optind
], "set") &&
640 strcmp(argv
[optind
], "unlock") &&
641 strcmp(argv
[optind
], "upgrade") &&
642 strcmp(argv
[optind
], "resetadmin"))
644 _tprintf(_T("Invalid command \"%hs\". Type nxdbmgr -h for command line syntax.\n"), argv
[optind
]);
647 if (((!strcmp(argv
[optind
], "init") || !strcmp(argv
[optind
], "batch") || !strcmp(argv
[optind
], "export") || !strcmp(argv
[optind
], "import") || !strcmp(argv
[optind
], "get")) && (argc
- optind
< 2)) ||
648 (!strcmp(argv
[optind
], "set") && (argc
- optind
< 3)))
650 _tprintf(_T("Required command argument(s) missing\n"));
654 // Read configuration file
655 #if !defined(_WIN32) && !defined(_NETWARE)
656 if (!_tcscmp(szConfigFile
, _T("{search}")))
658 if (_taccess(PREFIX
_T("/etc/netxmsd.conf"), 4) == 0)
660 _tcscpy(szConfigFile
, PREFIX
_T("/etc/netxmsd.conf"));
662 else if (_taccess(_T("/usr/etc/netxmsd.conf"), 4) == 0)
664 _tcscpy(szConfigFile
, _T("/usr/etc/netxmsd.conf"));
668 _tcscpy(szConfigFile
, _T("/etc/netxmsd.conf"));
673 Config
*config
= new Config();
674 if (!config
->loadIniConfig(szConfigFile
, _T("server")) || !config
->parseTemplate(_T("server"), m_cfgTemplate
))
676 _tprintf(_T("Error loading configuration file\n"));
682 if (s_encryptedDbPassword
[0] != 0)
684 DecryptPassword(s_dbLogin
, s_encryptedDbPassword
, s_dbPassword
);
688 SetDefaultCodepage(m_szCodePage
);
691 // Connect to database
694 _tprintf(_T("Unable to initialize database library\n"));
698 DB_DRIVER driver
= DBLoadDriver(s_dbDriver
, s_dbDrvParams
, false, NULL
, NULL
);
701 _tprintf(_T("Unable to load and initialize database driver \"%s\"\n"), s_dbDriver
);
705 TCHAR errorText
[DBDRV_MAX_ERROR_TEXT
];
706 g_hCoreDB
= DBConnect(driver
, s_dbServer
, s_dbName
, s_dbLogin
, s_dbPassword
, s_dbSchema
, errorText
);
707 if (g_hCoreDB
== NULL
)
709 _tprintf(_T("Unable to connect to database %s@%s as %s: %s\n"), s_dbName
, s_dbServer
, s_dbLogin
, errorText
);
710 DBUnloadDriver(driver
);
714 if (!strcmp(argv
[optind
], "init"))
716 InitDatabase(argv
[optind
+ 1]);
720 // Get database syntax
721 g_iSyntax
= DBGetSyntax(g_hCoreDB
);
722 if (g_iSyntax
== DB_SYNTAX_UNKNOWN
)
724 _tprintf(_T("Unable to determine database syntax\n"));
725 DBDisconnect(g_hCoreDB
);
726 DBUnloadDriver(driver
);
730 // Do requested operation
731 if (!strcmp(argv
[optind
], "batch"))
732 ExecSQLBatch(argv
[optind
+ 1]);
733 else if (!strcmp(argv
[optind
], "check"))
735 else if (!strcmp(argv
[optind
], "upgrade"))
737 else if (!strcmp(argv
[optind
], "unlock"))
739 else if (!strcmp(argv
[optind
], "reindex"))
741 else if (!strcmp(argv
[optind
], "export"))
742 ExportDatabase(argv
[optind
+ 1]);
743 else if (!strcmp(argv
[optind
], "import"))
744 ImportDatabase(argv
[optind
+ 1]);
745 else if (!strcmp(argv
[optind
], "get"))
748 WCHAR
*var
= WideStringFromMBString(argv
[optind
+ 1]);
750 char *var
= argv
[optind
+ 1];
752 TCHAR buffer
[MAX_DB_STRING
];
753 ConfigReadStr(var
, buffer
, MAX_DB_STRING
, _T(""));
754 _tprintf(_T("%s\n"), buffer
);
759 else if (!strcmp(argv
[optind
], "set"))
762 WCHAR
*var
= WideStringFromMBString(argv
[optind
+ 1]);
763 WCHAR
*value
= WideStringFromMBString(argv
[optind
+ 2]);
765 char *var
= argv
[optind
+ 1];
766 char *value
= argv
[optind
+ 2];
768 CreateConfigParam(var
, value
, 1, 0, bReplaceValue
);
774 else if (!strcmp(argv
[optind
], "resetadmin"))
781 DBDisconnect(g_hCoreDB
);
782 DBUnloadDriver(driver
);