VALUES ('TDataTableCreationCommand_0','CREATE TABLE tdata_%d (item_id integer not null,tdata_timestamp integer not null,tdata_value ' CONCAT SQL_TEXT_TEXT CONCAT ' null)');
#if defined(DB_MSSQL)
INSERT INTO metadata (var_name,var_value)
- VALUES ('TDataIndexCreationCommand_0','CREATE CLUSTERED INDEX idx_tdata_%d_id_timestamp ON tdata_%d(item_id,tdata_timestamp)');
+ VALUES ('TDataIndexCreationCommand_0','CREATE CLUSTERED INDEX idx_tdata_%d ON tdata_%d(item_id,tdata_timestamp)');
#else
INSERT INTO metadata (var_name,var_value)
- VALUES ('TDataIndexCreationCommand_0','CREATE INDEX idx_tdata_%d_id_timestamp ON tdata_%d(item_id,tdata_timestamp)');
+ VALUES ('TDataIndexCreationCommand_0','CREATE INDEX idx_tdata_%d ON tdata_%d(item_id,tdata_timestamp)');
#endif
INSERT INTO metadata (var_name,var_value)
bin_PROGRAMS = nxdbmgr
nxdbmgr_SOURCES = nxdbmgr.cpp check.cpp clear.cpp export.cpp import.cpp \
init.cpp migrate.cpp mm.cpp reindex.cpp resetadmin.cpp \
- tables.cpp unlock.cpp upgrade.cpp
+ tables.cpp tdata_convert.cpp unlock.cpp upgrade.cpp
nxdbmgr_CPPFLAGS=-I@top_srcdir@/include -I@top_srcdir@/src/server/include
nxdbmgr_LDADD = ../../../libnetxms/libnetxms.la \
../../../db/libnxdb/libnxdb.la \
}
/**
- * Create tdata_*_xx tables
+ * Create tdata_xx table
*/
-BOOL CreateTDataTables(DWORD nodeId)
+BOOL CreateTDataTable(DWORD nodeId)
{
TCHAR szQuery[256], szQueryTemplate[256];
DWORD i;
EndStage();
}
-/**
- * Drop given data table
- */
-static BOOL DropDataTable(const TCHAR *table, DWORD id)
-{
- TCHAR query[256];
- _sntprintf(query, 256, _T("DROP TABLE %s_%d"), table, id);
- return SQLQuery(query);
-}
-
/**
* Check if given data table exist
*/
-static BOOL IsDataTableExist(const TCHAR *format, DWORD id)
+BOOL IsDataTableExist(const TCHAR *format, DWORD id)
{
TCHAR table[256];
_sntprintf(table, 256, format, id);
}
// TDATA
- BOOL tdata = IsDataTableExist(_T("tdata_%d"), id);
- BOOL tdataRecords = IsDataTableExist(_T("tdata_records_%d"), id);
- BOOL tdataRows = IsDataTableExist(_T("tdata_rows_%d"), id);
- if (!tdata || !tdataRecords || !tdataRows)
+ if (IsDataTableExist(_T("tdata_%d"), id))
{
m_iNumErrors++;
if (GetYesNo(_T("\rData collection table (TDATA) for %s [%d] not found. Create? (Y/N) "), classDescr, id))
{
- // Drop existing tables first
- if (tdataRows)
- DropDataTable(_T("tdata_rows"), id);
- if (tdataRecords)
- DropDataTable(_T("tdata_records"), id);
- if (tdata)
- DropDataTable(_T("tdata"), id);
-
- if (CreateTDataTables(id))
+ if (CreateTDataTable(id))
m_iNumFixes++;
}
}
CheckDataTablesForClass(_T("clusters"), _T("cluster"));
CheckDataTablesForClass(_T("mobile_devices"), _T("mobile device"));
CheckDataTablesForClass(_T("access_points"), _T("access point"));
+ CheckDataTablesForClass(_T("chassis"), _T("chassis"));
EndStage();
}
if (!ImportTable(db, buffer))
break;
- if (!CreateTDataTables(id))
+ if (!CreateTDataTable(id))
break; // Failed to create tdata tables
_sntprintf(buffer, 1024, _T("tdata_%d"), id);
if (!ImportTable(db, buffer))
break;
-
- _sntprintf(buffer, 1024, _T("tdata_records_%d"), id);
- if (!ImportTable(db, buffer))
- break;
-
- _sntprintf(buffer, 1024, _T("tdata_rows_%d"), id);
- if (!ImportTable(db, buffer))
- break;
}
DBFreeResult(hResult);
/*
** nxdbmgr - NetXMS database manager
-** Copyright (C) 2004-2015 Victor Kirhenshtein
+** Copyright (C) 2004-2016 Victor Kirhenshtein
**
** This program is free software; you can redistribute it and/or modify
** it under the terms of the GNU General Public License as published by
if (!g_dataOnlyMigration)
{
- if (!CreateTDataTables(id))
+ if (!CreateTDataTable(id))
break; // Failed to create tdata tables
}
_sntprintf(buffer, 1024, _T("tdata_%d"), id);
if (!MigrateTable(buffer))
break;
-
- _sntprintf(buffer, 1024, _T("tdata_records_%d"), id);
- if (!MigrateTable(buffer))
- break;
-
- _sntprintf(buffer, 1024, _T("tdata_rows_%d"), id);
- if (!MigrateTable(buffer))
- break;
}
}
{ _T(""), CT_END_OF_LIST, 0, 0, 0, 0, NULL }
};
static BOOL m_bForce = FALSE;
+static DB_DRIVER s_driver = NULL;
/**
* Show query if trace mode is ON
return true;
}
+/**
+ * Open database connection
+ */
+DB_HANDLE ConnectToDatabase()
+{
+ TCHAR errorText[DBDRV_MAX_ERROR_TEXT];
+ DB_HANDLE hdb = DBConnect(s_driver, s_dbServer, s_dbName, s_dbLogin, s_dbPassword, s_dbSchema, errorText);
+ if (hdb == NULL)
+ {
+ _tprintf(_T("Unable to connect to database %s@%s as %s: %s\n"), s_dbName, s_dbServer, s_dbLogin, errorText);
+ }
+ return hdb;
+}
+
/**
* Startup
*/
return 3;
}
- DB_DRIVER driver = DBLoadDriver(s_dbDriver, s_dbDrvParams, false, NULL, NULL);
- if (driver == NULL)
+ s_driver = DBLoadDriver(s_dbDriver, s_dbDrvParams, false, NULL, NULL);
+ if (s_driver == NULL)
{
_tprintf(_T("Unable to load and initialize database driver \"%s\"\n"), s_dbDriver);
return 3;
}
TCHAR errorText[DBDRV_MAX_ERROR_TEXT];
- g_hCoreDB = DBConnect(driver, s_dbServer, s_dbName, s_dbLogin, s_dbPassword, s_dbSchema, errorText);
+ g_hCoreDB = ConnectToDatabase();
if (g_hCoreDB == NULL)
{
- _tprintf(_T("Unable to connect to database %s@%s as %s: %s\n"), s_dbName, s_dbServer, s_dbLogin, errorText);
- DBUnloadDriver(driver);
+ DBUnloadDriver(s_driver);
return 4;
}
{
_tprintf(_T("Unable to determine database syntax\n"));
DBDisconnect(g_hCoreDB);
- DBUnloadDriver(driver);
+ DBUnloadDriver(s_driver);
return 5;
}
// Shutdown
DBDisconnect(g_hCoreDB);
- DBUnloadDriver(driver);
+ DBUnloadDriver(s_driver);
return 0;
}
// Functions
//
+DB_HANDLE ConnectToDatabase();
void CheckDatabase();
void InitDatabase(const char *pszInitFile);
bool ClearDatabase(bool preMigration);
bool CreateConfigParam(const TCHAR *name, const TCHAR *value, bool isVisible, bool needRestart, bool forceUpdate = false);
bool CreateConfigParam(const TCHAR *name, const TCHAR *value, const TCHAR *description, char dataType, bool isVisible, bool needRestart, bool isPublic, bool forceUpdate = false);
+BOOL IsDataTableExist(const TCHAR *format, DWORD id);
+
+bool RenameDatabaseTable(const TCHAR *oldName, const TCHAR *newName);
+
BOOL CreateIDataTable(DWORD nodeId);
-BOOL CreateTDataTables(DWORD nodeId);
+BOOL CreateTDataTable(DWORD nodeId);
BOOL CreateTDataTable_preV281(DWORD nodeId);
void ResetAdmin();
--- /dev/null
+/*
+** nxdbmgr - NetXMS database manager
+** Copyright (C) 2003-2016 Victor Kirhenshtein
+**
+** This program is free software; you can redistribute it and/or modify
+** it under the terms of the GNU General Public License as published by
+** the Free Software Foundation; either version 2 of the License, or
+** (at your option) any later version.
+**
+** This program is distributed in the hope that it will be useful,
+** but WITHOUT ANY WARRANTY; without even the implied warranty of
+** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+** GNU General Public License for more details.
+**
+** You should have received a copy of the GNU General Public License
+** along with this program; if not, write to the Free Software
+** Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
+**
+**/
+
+#include "nxdbmgr.h"
+
+/**
+ * Read table object from tdata_* tables
+ */
+static Table *ReadTable(UINT64 recordId, UINT32 tableId, UINT32 objectId)
+{
+ TCHAR query[1024];
+ _sntprintf(query, 1024,
+ _T("SELECT r.row_id,w.column_id,n.column_name,c.flags,c.display_name,w.value FROM tdata_records_%d r ")
+ _T("INNER JOIN tdata_rows_%d w ON w.row_id = r.row_id ")
+ _T("INNER JOIN dct_column_names n ON n.column_id=w.column_id ")
+ _T("LEFT OUTER JOIN dc_table_columns c ON c.table_id=%d AND c.column_name=n.column_name ")
+ _T("WHERE r.record_id=") UINT64_FMT _T(" ")
+ _T("ORDER BY r.row_id"), objectId, objectId, tableId, recordId);
+ DB_RESULT hResult = DBSelect(g_hCoreDB, query);
+ if (hResult == NULL)
+ return NULL;
+
+ int count = DBGetNumRows(hResult);
+ Table *table = NULL;
+ if (count > 0)
+ {
+ table = new Table();
+ UINT64 currRowId = 0;
+ for(int i = 0; i < count; i++)
+ {
+ TCHAR columnName[MAX_COLUMN_NAME];
+ DBGetField(hResult, i, 2, columnName, MAX_COLUMN_NAME);
+ int columnIndex = table->getColumnIndex(columnName);
+ if (columnIndex == -1)
+ {
+ TCHAR displayName[256];
+ DBGetField(hResult, i, 4, displayName, 256);
+ UINT16 flags = (UINT16)DBGetFieldULong(hResult, i, 3);
+ columnIndex = table->addColumn(columnName, TCF_GET_DATA_TYPE(flags), displayName, (flags & TCF_INSTANCE_COLUMN) ? true : false);
+ }
+
+ UINT64 rowId = DBGetFieldUInt64(hResult, i, 0);
+ if (rowId != currRowId)
+ {
+ currRowId = rowId;
+ table->addRow();
+ }
+
+ TCHAR value[MAX_RESULT_LENGTH];
+ DBGetField(hResult, i, 5, value, MAX_RESULT_LENGTH);
+ table->set(columnIndex, value);
+ }
+ }
+ DBFreeResult(hResult);
+ return table;
+}
+
+/**
+ * Convert tdata table for given object
+ */
+static bool ConvertTData(UINT32 id, int *skippedRecords)
+{
+ TCHAR oldName[64], newName[64];
+ _sntprintf(oldName, 64, _T("tdata_%d"), id);
+ _sntprintf(newName, 64, _T("tdata_temp_%d"), id);
+ if (!RenameDatabaseTable(oldName, newName))
+ return false;
+
+ bool success = false;
+ if (CreateTDataTable(id))
+ {
+ int total = 0x07FFFFFF;
+ TCHAR query[256];
+ _sntprintf(query, 256, _T("SELECT count(*) FROM tdata_temp_%d"), id);
+ DB_RESULT hCountResult = DBSelect(g_hCoreDB, query);
+ if (hCountResult != NULL)
+ {
+ total = DBGetFieldLong(hCountResult, 0, 0);
+ if (total <= 0)
+ total = 0x07FFFFFF;
+ DBFreeResult(hCountResult);
+ }
+
+ // Open second connection to database to allow unbuffered query in parallel with inserts
+ DB_HANDLE hdb = ConnectToDatabase();
+ if (hdb != NULL)
+ {
+ _sntprintf(query, 256, _T("SELECT item_id,tdata_timestamp,record_id FROM tdata_temp_%d"), id);
+ DB_UNBUFFERED_RESULT hResult = DBSelectUnbuffered(hdb, query);
+ if (hResult != NULL)
+ {
+ _sntprintf(query, 256, _T("INSERT INTO tdata_%d (item_id,tdata_timestamp,tdata_value) VALUES (?,?,?)"), id);
+ DB_STATEMENT hStmt = DBPrepare(g_hCoreDB, query);
+ if (hStmt != NULL)
+ {
+ success = true;
+ int converted = 0;
+ int skipped = 0;
+ DBBegin(g_hCoreDB);
+ while(DBFetch(hResult))
+ {
+ UINT32 tableId = DBGetFieldULong(hResult, 0);
+ UINT32 timestamp = DBGetFieldULong(hResult, 1);
+ UINT64 recordId = DBGetFieldUInt64(hResult, 2);
+ Table *value = ReadTable(recordId, tableId, id);
+ if (value != NULL)
+ {
+ DBBind(hStmt, 1, DB_SQLTYPE_INTEGER, tableId);
+ DBBind(hStmt, 2, DB_SQLTYPE_INTEGER, timestamp);
+ DBBind(hStmt, 3, DB_SQLTYPE_TEXT, DB_CTYPE_UTF8_STRING, value->createPackedXML(), DB_BIND_DYNAMIC);
+ if (!SQLExecute(hStmt))
+ {
+ delete value;
+ success = false;
+ break;
+ }
+ delete value;
+ }
+ else
+ {
+ skipped++;
+ }
+
+ converted++;
+ if (converted % 100 == 0)
+ {
+ int pct = (converted * 100) / total;
+ if (pct > 100)
+ pct = 100;
+ WriteToTerminalEx(_T("\b\b\b\b%3d%%"), pct);
+ fflush(stdout);
+ DBCommit(g_hCoreDB);
+ DBBegin(g_hCoreDB);
+ }
+ }
+ DBCommit(g_hCoreDB);
+ DBFreeStatement(hStmt);
+ *skippedRecords = skipped;
+ }
+ DBFreeResult(hResult);
+ }
+ DBDisconnect(hdb);
+ }
+ }
+
+ if (success)
+ {
+ TCHAR query[256];
+ _sntprintf(query, 256, _T("DROP TABLE tdata_rows_%d"), id);
+ SQLQuery(query);
+ _sntprintf(query, 256, _T("DROP TABLE tdata_records_%d"), id);
+ SQLQuery(query);
+ _sntprintf(query, 256, _T("DROP TABLE tdata_temp_%d"), id);
+ SQLQuery(query);
+ }
+ else
+ {
+ TCHAR query[256];
+ _sntprintf(query, 256, _T("DROP TABLE tdata_%d"), id);
+ SQLQuery(query);
+
+ _sntprintf(oldName, 64, _T("tdata_temp_%d"), id);
+ _sntprintf(newName, 64, _T("tdata_%d"), id);
+ RenameDatabaseTable(oldName, newName);
+ }
+
+ return success;
+}
+
+/**
+ * Check data tables for given o bject class
+ */
+static bool ConvertTDataForClass(const TCHAR *className)
+{
+ bool success = false;
+ TCHAR query[1024];
+ _sntprintf(query, 256, _T("SELECT id FROM %s"), className);
+ DB_RESULT hResult = SQLSelect(query);
+ if (hResult != NULL)
+ {
+ success = true;
+ int count = DBGetNumRows(hResult);
+ for(int i = 0; i < count; i++)
+ {
+ UINT32 id = DBGetFieldULong(hResult, i, 0);
+ if (IsDataTableExist(_T("tdata_%d"), id))
+ {
+ WriteToTerminalEx(_T("Converting table \x1b[1mtdata_%d\x1b[0m: 0%%"), id);
+ fflush(stdout);
+ int skippedRecords = 0;
+ if (ConvertTData(id, &skippedRecords))
+ {
+ if (skippedRecords == 0)
+ WriteToTerminalEx(_T("\b\b\b\b\x1b[32;1mdone\x1b[0m\n"));
+ else
+ WriteToTerminalEx(_T("\b\b\b\b\x1b[33;1mdone with %d records skipped\x1b[0m\n"), skippedRecords);
+ }
+ else
+ {
+ WriteToTerminalEx(_T("\b\b\b\b\x1b[31;1mfailed\x1b[0m\n"));
+ success = false;
+ break;
+ }
+ }
+ else
+ {
+ CreateTDataTable(id);
+ WriteToTerminalEx(_T("Created empty table \x1b[1mtdata_%d\x1b[0m\n"), id);
+ }
+ }
+ DBFreeResult(hResult);
+ }
+ return success;
+}
+
+/**
+ * Convert tdata tables into new format
+ */
+bool ConvertTDataTables()
+{
+ CHK_EXEC(ConvertTDataForClass(_T("nodes")));
+ CHK_EXEC(ConvertTDataForClass(_T("clusters")));
+ CHK_EXEC(ConvertTDataForClass(_T("mobile_devices")));
+ CHK_EXEC(ConvertTDataForClass(_T("access_points")));
+ CHK_EXEC(ConvertTDataForClass(_T("chassis")));
+ return true;
+}
* Externals
*/
BOOL MigrateMaps();
+bool ConvertTDataTables();
/**
* Pre-defined GUID mapping for GenerateGUID
return SQLQuery(query);
}
+/**
+ * Rename table
+ */
+bool RenameDatabaseTable(const TCHAR *oldName, const TCHAR *newName)
+{
+ TCHAR query[1024];
+ switch(g_dbSyntax)
+ {
+ case DB_SYNTAX_DB2:
+ case DB_SYNTAX_INFORMIX:
+ case DB_SYNTAX_MYSQL:
+ _sntprintf(query, 1024, _T("RENAME TABLE %s TO %s"), oldName, newName);
+ break;
+ case DB_SYNTAX_ORACLE:
+ case DB_SYNTAX_PGSQL:
+ _sntprintf(query, 1024, _T("ALTER TABLE %s RENAME TO %s"), oldName, newName);
+ break;
+ case DB_SYNTAX_MSSQL:
+ _sntprintf(query, 1024, _T("EXEC sp_rename '%s','%s'"), oldName, newName);
+ break;
+ default: // Unsupported DB engine
+ return false;
+ }
+ return SQLQuery(query);
+}
+
/**
* Drop primary key from table
*/
}
}
- if (!CreateTDataTables(id))
+ if (!CreateTDataTable(id))
{
if (!g_bIgnoreErrors)
{
return SQLQuery(query);
}
+/**
+ * Upgrade from V410 to V411
+ */
+static BOOL H_UpgradeFromV410(int currVersion, int newVersion)
+{
+ StringMap savedMetadata;
+ DB_RESULT hResult = SQLSelect(_T("SELECT var_name,var_value FROM metadata WHERE var_name LIKE 'TDataTableCreationCommand_%' OR var_name LIKE 'TDataIndexCreationCommand_%'"));
+ if (hResult != NULL)
+ {
+ int count = DBGetNumRows(hResult);
+ for(int i = 0; i < count; i++)
+ {
+ savedMetadata.setPreallocated(DBGetField(hResult, i, 0, NULL, 0), DBGetField(hResult, i, 1, NULL, 0));
+ }
+ DBFreeResult(hResult);
+ }
+ else if (!g_bIgnoreErrors)
+ {
+ return false;
+ }
+
+ static const TCHAR *batch =
+ _T("DELETE FROM metadata WHERE var_name LIKE 'TDataTableCreationCommand_%' OR var_name LIKE 'TDataIndexCreationCommand_%'\n")
+ _T("INSERT INTO metadata (var_name,var_value) VALUES ('TDataTableCreationCommand_0','CREATE TABLE tdata_%d (item_id integer not null,tdata_timestamp integer not null,tdata_value $SQL:TEXT null)')\n")
+ _T("<END>");
+ CHK_EXEC(SQLBatch(batch));
+
+ if (g_dbSyntax == DB_SYNTAX_MSSQL)
+ CHK_EXEC(SQLQuery(_T("INSERT INTO metadata (var_name,var_value) VALUES ('TDataIndexCreationCommand_0','CREATE CLUSTERED INDEX idx_tdata_%d ON tdata_%d(item_id,tdata_timestamp)')")));
+ else
+ CHK_EXEC(SQLQuery(_T("INSERT INTO metadata (var_name,var_value) VALUES ('TDataIndexCreationCommand_0','CREATE INDEX idx_tdata_%d ON tdata_%d(item_id,tdata_timestamp)')")));
+
+ // table conversion will require multiple commits
+ DBCommit(g_hCoreDB);
+ if (!ConvertTDataTables())
+ {
+ if (!g_bIgnoreErrors)
+ {
+ // Restore metadata
+ SQLQuery(_T("DELETE FROM metadata WHERE var_name LIKE 'TDataTableCreationCommand_%' OR var_name LIKE 'TDataIndexCreationCommand_%'"));
+ StringList *keys = savedMetadata.keys();
+ for(int i = 0; i < keys->size(); i++)
+ {
+ TCHAR query[4096];
+ _sntprintf(query, 4096, _T("INSERT INTO metadata (var_name,var_value) VALUES (%s,%s)"),
+ (const TCHAR *)DBPrepareString(g_hCoreDB, keys->get(i)),
+ (const TCHAR *)DBPrepareString(g_hCoreDB, savedMetadata.get(keys->get(i))));
+ SQLQuery(query);
+ }
+ return false;
+ }
+ }
+
+ DBBegin(g_hCoreDB);
+ CHK_EXEC(SetSchemaVersion(411));
+ return TRUE;
+}
+
/**
* Upgrade from V409 to V410
*/
{ 407, 408, H_UpgradeFromV407 },
{ 408, 409, H_UpgradeFromV408 },
{ 409, 410, H_UpgradeFromV409 },
+ { 410, 411, H_UpgradeFromV410 },
{ 0, 0, NULL }
};
git.netxms.org / public / netxms.git / commitdiff
