function DBDropColumn moved to libnxdb from nxdbmgr
authorVictor Kirhenshtein <victor@netxms.org>
Mon, 17 Jul 2017 11:05:59 +0000 (14:05 +0300)
committerVictor Kirhenshtein <victor@netxms.org>
Mon, 17 Jul 2017 11:05:59 +0000 (14:05 +0300)
include/nxdbapi.h
src/db/libnxdb/util.cpp
src/server/tools/nxdbmgr/nxdbmgr.cpp
src/server/tools/nxdbmgr/nxdbmgr.h
src/server/tools/nxdbmgr/upgrade.cpp

index 51471c0..1bbf28e 100644 (file)
@@ -240,5 +240,6 @@ bool LIBNXDB_EXPORTABLE DBAddPrimaryKey(DB_HANDLE hdb, const TCHAR *table, const
 bool LIBNXDB_EXPORTABLE DBRemoveNotNullConstraint(DB_HANDLE hdb, const TCHAR *table, const TCHAR *column);
 bool LIBNXDB_EXPORTABLE DBSetNotNullConstraint(DB_HANDLE hdb, const TCHAR *table, const TCHAR *column);
 bool LIBNXDB_EXPORTABLE DBResizeColumn(DB_HANDLE hdb, const TCHAR *table, const TCHAR *column, int newSize, bool nullable);
+bool LIBNXDB_EXPORTABLE DBDropColumn(DB_HANDLE hdb, const TCHAR *table, const TCHAR *column);
 
 #endif   /* _nxsrvapi_h_ */
index 37e06a8..7d90d8d 100644 (file)
@@ -312,6 +312,7 @@ bool LIBNXDB_EXPORTABLE DBRenameTable(DB_HANDLE hdb, const TCHAR *oldName, const
          break;
       case DB_SYNTAX_ORACLE:
       case DB_SYNTAX_PGSQL:
+      case DB_SYNTAX_SQLITE:
          _sntprintf(query, 1024, _T("ALTER TABLE %s RENAME TO %s"), oldName, newName);
          break;
       case DB_SYNTAX_MSSQL:
@@ -502,3 +503,92 @@ bool LIBNXDB_EXPORTABLE DBResizeColumn(DB_HANDLE hdb, const TCHAR *table, const
 
    return (query[0] != 0) ? DBQuery(hdb, query) : true;
 }
+
+/**
+ * Drop column from the table
+ */
+bool LIBNXDB_EXPORTABLE DBDropColumn(DB_HANDLE hdb, const TCHAR *table, const TCHAR *column)
+{
+   int syntax = DBGetSyntax(hdb);
+
+   TCHAR query[1024];
+   bool success = false;
+   if (syntax != DB_SYNTAX_SQLITE)
+   {
+      _sntprintf(query, 1024, _T("ALTER TABLE %s DROP COLUMN %s"), table, column);
+      success = DBQuery(hdb, query);
+      if (syntax == DB_SYNTAX_DB2)
+      {
+         _sntprintf(query, 1024, _T("CALL Sysproc.admin_cmd('REORG TABLE %s')"), table);
+         success = DBQuery(hdb, query);
+      }
+   }
+   else
+   {
+      _sntprintf(query, 1024, _T("PRAGMA TABLE_INFO('%s')"), table);
+      DB_RESULT hResult = DBSelect(hdb, query);
+      if (hResult != NULL)
+      {
+         int rows = DBGetNumRows(hResult);
+         const int blen = 2048;
+         TCHAR buffer[blen];
+         // Intermediate buffers for SQLs
+         TCHAR columnList[1024], createList[1024];
+         // TABLE_INFO() columns
+         TCHAR tabColName[128], tabColType[64], tabColNull[10], tabColDefault[128];
+         columnList[0] = createList[0] = _T('\0');
+         for (int i = 0; i < rows; i++)
+         {
+            DBGetField(hResult, i, 1, tabColName, 128);
+            DBGetField(hResult, i, 2, tabColType, 64);
+            DBGetField(hResult, i, 3, tabColNull, 10);
+            DBGetField(hResult, i, 4, tabColDefault, 128);
+            if (_tcsnicmp(tabColName, column, 128))
+            {
+               _tcscat(columnList, tabColName);
+               if (columnList[0] != _T('\0'))
+                  _tcscat(columnList, _T(","));
+               _tcscat(createList, tabColName);
+               _tcscat(createList, tabColType);
+               if (tabColDefault[0] != _T('\0'))
+               {
+                  _tcscat(createList, _T("DEFAULT "));
+                  _tcscat(createList, tabColDefault);
+               }
+               if (tabColNull[0] == _T('1'))
+                  _tcscat(createList, _T(" NOT NULL"));
+               _tcscat(createList, _T(","));
+            }
+         }
+         DBFreeResult(hResult);
+         if (rows > 0)
+         {
+            int cllen = (int)_tcslen(columnList);
+            if (cllen > 0 && columnList[cllen - 1] == _T(','))
+               columnList[cllen - 1] = _T('\0');
+            // TODO: figure out if SQLite transactions will work here
+            _sntprintf(buffer, blen, _T("CREATE TABLE %s__backup__ (%s)"), table, columnList);
+            success = DBQuery(hdb, buffer);
+            if (success)
+            {
+               _sntprintf(buffer, blen, _T("INSERT INTO %s__backup__  (%s) SELECT %s FROM %s"),
+                  table, columnList, columnList, table);
+               success = DBQuery(hdb, buffer);
+            }
+            if (success)
+            {
+               _sntprintf(buffer, blen, _T("DROP TABLE %s"), table);
+               success = DBQuery(hdb, buffer);
+            }
+            if (success)
+            {
+               _sntprintf(buffer, blen, _T("ALTER TABLE %s__backup__ RENAME TO %s"), table, table);
+               success = DBQuery(hdb, buffer);
+            }
+         }
+      }
+      // TODO: preserve indices and constraints??
+   }
+
+   return success;
+}
index abac453..228f3cc 100644 (file)
@@ -251,7 +251,7 @@ bool SQLBatch(const TCHAR *pszBatch)
 
                if (_stscanf(pszQuery, _T("ALTER TABLE %128s DROP COLUMN %128s"), table, column) == 2)
                {
-                       if (!SQLDropColumn(table, column))
+                       if (!DBDropColumn(g_hCoreDB, table, column))
                        {
                                WriteToTerminalEx(_T("Cannot drop column \x1b[37;1m%s.%s\x1b[0m\n"), table, column);
                                if (!g_bIgnoreErrors)
@@ -286,88 +286,6 @@ bool SQLBatch(const TCHAR *pszBatch)
 }
 
 /**
- * Drop column from the table
- */
-bool SQLDropColumn(const TCHAR *table, const TCHAR *column)
-{
-       TCHAR query[1024];
-       DB_RESULT hResult;
-       bool success = false;
-
-       if (g_dbSyntax != DB_SYNTAX_SQLITE)
-       {
-               _sntprintf(query, 1024, _T("ALTER TABLE %s DROP COLUMN %s"), table, column);
-               success = SQLQuery(query);
-      if (g_dbSyntax == DB_SYNTAX_DB2)
-      {
-         _sntprintf(query, 1024, _T("CALL Sysproc.admin_cmd('REORG TABLE %s')"), table);
-         success = SQLQuery(query);
-      }
-       }
-       else
-       {
-               _sntprintf(query, 1024, _T("PRAGMA TABLE_INFO('%s')"), table);
-               hResult = SQLSelect(query);
-               if (hResult != NULL)
-               {
-                       int rows = DBGetNumRows(hResult);
-                       const int blen = 2048;
-                       TCHAR buffer[blen];
-                       // Intermediate buffers for SQLs
-                       TCHAR columnList[1024], createList[1024];
-                       // TABLE_INFO() columns
-                       TCHAR tabColName[128], tabColType[64], tabColNull[10], tabColDefault[128];
-                       columnList[0] = createList[0] = _T('\0');
-                       for (int i = 0; i < rows; i++)
-                       {
-                               DBGetField(hResult, i, 1, tabColName, 128);
-                               DBGetField(hResult, i, 2, tabColType, 64);
-                               DBGetField(hResult, i, 3, tabColNull, 10);
-                               DBGetField(hResult, i, 4, tabColDefault, 128);
-                               if (_tcsnicmp(tabColName, column, 128))
-                               {
-                                       _tcscat(columnList, tabColName);
-                                       if (columnList[0] != _T('\0'))
-                                               _tcscat(columnList, _T(","));
-                                       _tcscat(createList, tabColName);
-                                       _tcscat(createList, tabColType);
-                                       if (tabColDefault[0] != _T('\0'))
-                                       {
-                                               _tcscat(createList, _T("DEFAULT "));
-                                               _tcscat(createList, tabColDefault);
-                                       }
-                                       if (tabColNull[0] == _T('1'))
-                                               _tcscat(createList, _T(" NOT NULL"));
-                                       _tcscat(createList, _T(","));
-                               }
-                       }
-                       DBFreeResult(hResult);
-                       if (rows > 0)
-                       {
-                               int cllen = (int)_tcslen(columnList);
-                               if (cllen > 0 && columnList[cllen - 1] == _T(','))
-                                       columnList[cllen - 1] = _T('\0');
-                               // TODO: figure out if SQLite transactions will work here
-                               _sntprintf(buffer, blen, _T("CREATE TABLE %s__backup__ (%s)"), table, columnList);
-                               CHK_EXEC(SQLQuery(buffer));
-                               _sntprintf(buffer, blen, _T("INSERT INTO %s__backup__  (%s) SELECT %s FROM %s"),
-                                       table, columnList, columnList, table);
-                               CHK_EXEC(SQLQuery(buffer));
-                               _sntprintf(buffer, blen, _T("DROP TABLE %s"), table);
-                               CHK_EXEC(SQLQuery(buffer));
-                               _sntprintf(buffer, blen, _T("ALTER TABLE %s__backup__ RENAME to %s"), table, table);
-                               CHK_EXEC(SQLQuery(buffer));
-                               success = true;
-                       }
-               }
-       }
-
-       // TODO: preserve indices and constraints??
-
-       return success;
-}
-
-/**
  * Read string value from metadata table
  */
 BOOL MetaDataReadStr(const TCHAR *pszVar, TCHAR *pszBuffer, int iBufSize, const TCHAR *pszDefault)
index b0d0525..0581037 100644 (file)
@@ -64,7 +64,6 @@ DB_UNBUFFERED_RESULT SQLSelectUnbuffered(const TCHAR *pszQuery);
 bool SQLExecute(DB_STATEMENT hStmt);
 bool SQLQuery(const TCHAR *pszQuery);
 bool SQLBatch(const TCHAR *pszBatch);
-bool SQLDropColumn(const TCHAR *table, const TCHAR *column);
 bool GetYesNo(const TCHAR *format, ...);
 void ShowQuery(const TCHAR *pszQuery);
 bool ExecSQLBatch(const char *pszFile);
index c902cd3..5faaf3f 100644 (file)
@@ -379,7 +379,7 @@ static BOOL ConvertNetMasks(const TCHAR *table, const TCHAR *column, const TCHAR
    if (hResult == NULL)
       return FALSE;
 
-   BOOL success = SQLDropColumn(table, column);
+   bool success = DBDropColumn(g_hCoreDB, table, column);
 
    if (success)
    {
@@ -977,7 +977,7 @@ static BOOL H_UpgradeFromV445(int currVersion, int newVersion)
             }
          }
 
-         CHK_EXEC(SQLDropColumn(_T("network_maps"), _T("seed")));
+         CHK_EXEC(DBDropColumn(g_hCoreDB, _T("network_maps"), _T("seed")));
          DBFreeStatement(hStmt);
       }
       DBFreeResult(hResult);
@@ -1382,8 +1382,8 @@ static BOOL H_UpgradeFromV429(int currVersion, int newVersion)
 
    CHK_EXEC(SQLQuery(_T("DROP TABLE situations")));
    CHK_EXEC(SQLQuery(_T("DROP TABLE policy_situation_attr_list")));
-   CHK_EXEC(SQLDropColumn(_T("event_policy"), _T("situation_id")));
-   CHK_EXEC(SQLDropColumn(_T("event_policy"), _T("situation_instance")));
+   CHK_EXEC(DBDropColumn(g_hCoreDB, _T("event_policy"), _T("situation_id")));
+   CHK_EXEC(DBDropColumn(g_hCoreDB, _T("event_policy"), _T("situation_instance")));
    CHK_EXEC(SetSchemaVersion(430));
    return TRUE;
 }
@@ -3832,7 +3832,7 @@ static BOOL H_UpgradeFromV349(int currVersion, int newVersion)
          break;
        }
 
-   CHK_EXEC(SQLDropColumn(_T("ap_common"), _T("description")));
+   CHK_EXEC(DBDropColumn(g_hCoreDB, _T("ap_common"), _T("description")));
    CHK_EXEC(SetSchemaVersion(350));
    return TRUE;
 }
@@ -4063,7 +4063,7 @@ static BOOL H_UpgradeFromV341(int currVersion, int newVersion)
          free(oid);
       }
    }
-   CHK_EXEC(SQLDropColumn(_T("object_tools"), _T("matching_oid"))); //delete old column
+   CHK_EXEC(DBDropColumn(g_hCoreDB, _T("object_tools"), _T("matching_oid"))); //delete old column
    CHK_EXEC(SetSchemaVersion(342));
    return TRUE;
 }
@@ -5055,7 +5055,7 @@ static BOOL H_UpgradeFromV289(int currVersion, int newVersion)
  */
 static BOOL H_UpgradeFromV288(int currVersion, int newVersion)
 {
-   CHK_EXEC(SQLDropColumn(_T("dct_thresholds"), _T("current_state")));
+   CHK_EXEC(DBDropColumn(g_hCoreDB, _T("dct_thresholds"), _T("current_state")));
    CHK_EXEC(SetSchemaVersion(289));
    return TRUE;
 }
@@ -5245,7 +5245,7 @@ static BOOL H_UpgradeFromV279(int currVersion, int newVersion)
          return FALSE;
    }
 
-   CHK_EXEC(SQLDropColumn(_T("dc_tables"), _T("instance_column")));
+   CHK_EXEC(DBDropColumn(g_hCoreDB, _T("dc_tables"), _T("instance_column")));
 
    CHK_EXEC(SetSchemaVersion(280));
    return TRUE;