Exemplo n.º 1
0
 public Index(Table table, string name, bool nonUnique, short type)
 {
     this.table = table;
     this.name = name;
     this.nonUnique = nonUnique;
     this.type = type;
 }
Exemplo n.º 2
0
 public PrimaryKey(Table table)
 {
     this.table = table;
 }
Exemplo n.º 3
0
        public static string primaryKeyConstraint(Table table, Dialect dialect)
        {
            string sql = "";

            if (table.primaryKey.columnNames.Count > 0)
            {
                if (dialect == Dialect.db2 || dialect == Dialect.generic)
                {
                    sql += " CONSTRAINT " + table.primaryKey.constraintName + " PRIMARY KEY (";
                }
                else
                {
                    sql += "    CONSTRAINT [" + table.primaryKey.constraintName + "] PRIMARY KEY (";
                }
                //sql += "    PRIMARY KEY (";
                int count = 0;
                foreach (string columnName in table.primaryKey.columnNames)
                {
                    if (count++ > 0)
                        sql += ",";
                    sql += "\"" + columnName + "\"";
                }
                sql += ")";
            }

            return sql;
        }
Exemplo n.º 4
0
 public static string foreignKeyConstraint(Table table, Dialect dialect)
 {
     string sql = "";
     if (table.foreignKey.columnNames.Count > 0)
     {
         if (dialect == Dialect.db2 || dialect == Dialect.generic)
         {
             sql += " CONSTRAINT " + table.foreignKey.columnNames[0];
         }
         else
         {
             sql += "    CONSTRAINT [" + table.foreignKey.columnNames[0] + "]";
         }
         sql += "    FOREIGN KEY (";
         string columnName = "";
         int countForeignKeyTotal = 1;
         int countForeignKey = 1;
         for (int count = 1; count < table.foreignKey.columnNames.Count; count++)
         {
             columnName = table.foreignKey.columnNames[count];
             if (countForeignKey == 2)
             {
                 sql += ") REFERENCES ";
                 sql += "\"" + columnName.Trim() + "\"";
                 countForeignKey++;
                 countForeignKeyTotal++;
             }
             else if (countForeignKey == 3)
             {
                 sql += " (";
                 sql += "\"" + columnName.Trim() + "\"";
                 countForeignKey++;
                 countForeignKeyTotal++;
             }
             else if (countForeignKey == 4)
             {
                 if (countForeignKeyTotal < table.foreignKey.columnNames.Count)
                 {
                     if (dialect == Dialect.db2 || dialect == Dialect.generic)
                     {
                         sql += "), CONSTRAINT " + table.foreignKey.columnNames[countForeignKeyTotal];
                     }
                     else
                     {
                         sql += "), CONSTRAINT [" + table.foreignKey.columnNames[countForeignKeyTotal] + "]";
                     }
                     sql += " FOREIGN KEY (";
                 }
                 countForeignKey = 1;
                 countForeignKeyTotal++;
             }
             else
             {
                 sql += "\"" + columnName.Trim() + "\"";
                 countForeignKey++;
                 countForeignKeyTotal++;
             }
         }
     }
     sql += ") ";
     return sql;
 }
Exemplo n.º 5
0
        public static string ddlTemp(Table table, Dialect dialect)
        {
            //string sql = "CREATE TABLE " + table.schema + "." + table.name + "\r\n(\r\n";
            string sql = "CREATE TABLE " + table.name + "\r\n(\r\n";

            int ordinal = 0;

            foreach (Column column in table.columns.Values)
            {
                if (ordinal++ != 0)
                    sql += ",\r\n";

                sql += columnDefinition(column, dialect);
            }

            return sql;
        }
Exemplo n.º 6
0
        public static string ddl(Table table, Dialect dialect)
        {
            //string sql = "CREATE TABLE " + table.schema + "." + table.name + "\r\n(\r\n";
            string sql = "CREATE TABLE " + table.name + "\r\n(\r\n";

            int ordinal = 0;

            foreach (Column column in table.columns.Values)
            {
                if (ordinal++ != 0)
                    sql += ",\r\n";

                sql += columnDefinition(column, dialect);
            }

            if (table.primaryKey.columnNames.Count > 0)
                sql += ",\r\n" + primaryKeyConstraint(table,dialect);

            //if (table.foreignKey.columnNames.Count > 0)
            //    sql += ",\r\n" + foreignKeyConstraint(table, dialect); sql += "\r\n)\r\n";
            sql += "\r\n)\r\n";

            if (dialect != Dialect.db2)
            {
                sql = sql.Replace("VARCHAR(0)", "VARCHAR(max)");
                sql = sql.Replace("VARBINARY(0)", "VARBINARY(max)");
            }

            return sql;
        }
Exemplo n.º 7
0
        private void AlterComplex(Alteration alteration, Table masterTable, Table targetTable, Ddl.Dialect targetDialect, OdbcConnection targetConnection)
        {
            string PrimaryColumnName = "";
            if (config.type != 4)
            {
                bool failureDetected = false;
                // Make sure that the META_DB_TRANSFER_TEMP is deleted
                OdbcCommand command = new OdbcCommand("DROP TABLE META_DB_DEPLOY_TRANSFER_TEMP", target.connection);
                try
                {
                    command.CommandTimeout = 0;
                    command.ExecuteNonQuery();
                }
                catch {/*Do Nothing*/}

                // Drop the Primary key constraint in target table to create the Meta Temp table in SQL server database 20110811
                if (targetDialect == Ddl.Dialect.sqlServer)
                {
                    PrimaryColumnName = findPrimaryKeyColumnNameSqlServer(targetTable);
                    if (PrimaryColumnName != "")
                    {
                        command.CommandText = " ALTER TABLE " + targetTable.name + " DROP CONSTRAINT " + PrimaryColumnName;
                        command.CommandTimeout = 0;
                        command.ExecuteNonQuery();
                    }
                }

                // CREATE the META_DB_TRANSFER_TEMP table with the new target schema
                try
                {
                    command.CommandText = Ddl.ddl(masterTable, targetDialect).Replace("CREATE TABLE " + masterTable.name, "CREATE TABLE META_DB_DEPLOY_TRANSFER_TEMP");
                    if (targetDialect == Ddl.Dialect.sqlServer)
                    {
                        command.CommandText = command.CommandText.Replace("getdate", "getdate()");
                    }
                    //TableSapce Change
                    if (targetDialect == Ddl.Dialect.db2)
                    {
                        findTableSpaceSQL(masterTable.name);
                    }
                    //TableSapce Change

                    command.CommandTimeout = 0;
                    command.CommandText += TableSpaceSQL;
                    command.ExecuteNonQuery();
                    log.log(Logger.LogLevel.progress, "Transfer temporary table for complex alteration of " + masterTable.name + " created.");
                }
                catch (Exception ex)
                {
                    failureDetected = true;
                    log.log(Logger.LogLevel.error, "Exception occurred while trying to perform complex alteration of table " + masterTable.name + ".");
                    log.log(Logger.LogLevel.error, "CREATE TABLE META_DB_DEPLOY_TRANSFER_TEMP failed.");
                    log.log(Logger.LogLevel.error, ex.Message);
                }

                if (!failureDetected)
                {
                    try
                    {
                        command.CommandText = Ddl.ddl(alteration, "META_DB_DEPLOY_TRANSFER_TEMP");
                        if (targetDialect == Ddl.Dialect.sqlServer)
                        {
                            if (PrimaryColumnName != "")
                            {
                                command.CommandText = "SET IDENTITY_INSERT META_DB_DEPLOY_TRANSFER_TEMP ON;" + command.CommandText + "; SET IDENTITY_INSERT META_DB_DEPLOY_TRANSFER_TEMP OFF";
                            }
                            else
                            {
                                command.CommandText = command.CommandText + ";";
                            }
                            //command.CommandText = "SET IDENTITY_INSERT META_DB_DEPLOY_TRANSFER_TEMP ON;" + command.CommandText + "; SET IDENTITY_INSERT META_DB_DEPLOY_TRANSFER_TEMP OFF";
                        }

                        if (config.ddlLogging >= Configuration.DdlLogging.changes)
                            log.log(Logger.LogLevel.ddlChange, command.CommandText);

                        command.CommandTimeout = 0;
                        int insertCount = command.ExecuteNonQuery();
                        log.log(Logger.LogLevel.progress, insertCount + " rows were successfully transfered to temp table from table " + masterTable.name + ".");
                    }
                    catch (Exception ex)
                    {
                        failureDetected = true;
                        log.log(Logger.LogLevel.error, "Exception occurred while trying to perform complex alteration of table " + masterTable.name + ".");
                        log.log(Logger.LogLevel.error, "Transfer of data to tempory table failed.");
                        log.log(Logger.LogLevel.error, ex.Message);
                    }
                }

                if (!failureDetected)
                {
                    try
                    {
                        command.CommandText = "DROP TABLE " + targetTable.name;
                        command.CommandTimeout = 0;
                        command.ExecuteNonQuery();
                        log.log(Logger.LogLevel.progress, "Successfully dropped " + targetTable.name + ".");
                    }
                    catch (Exception ex)
                    {
                        failureDetected = true;
                        log.log(Logger.LogLevel.error, "Exception occurred while trying to perform complex alteration of table " + masterTable.name + ".");
                        log.log(Logger.LogLevel.error, "Drop of original table " + targetTable.name + " failed.");
                        log.log(Logger.LogLevel.error, ex.Message);
                    }
                }

                if (!failureDetected)
                {
                    try
                    {
                        if (targetDialect == Ddl.Dialect.db2)
                            command.CommandText = "RENAME TABLE META_DB_DEPLOY_TRANSFER_TEMP TO " + targetTable.name;
                        else if (targetDialect == Ddl.Dialect.sqlServer)
                            command.CommandText = "SP_RENAME 'META_DB_DEPLOY_TRANSFER_TEMP','" + targetTable.name + "'";

                        command.CommandTimeout = 0;
                        command.ExecuteNonQuery();
                        log.log(Logger.LogLevel.progress, "Successfully renamed temp table to " + targetTable.name + ".");
                    }
                    catch (Exception ex)
                    {
                        failureDetected = true;
                        log.log(Logger.LogLevel.error, "Exception occurred while trying to perform complex alteration of table " + masterTable.name + ".");
                        log.log(Logger.LogLevel.error, "RENAMING OF TEMP TABLE (META_DB_DEPLY_TRANSFER_TEMP) TO " + targetTable.name + " FAILED.");
                        log.log(Logger.LogLevel.error, "FAILURE TO FIX THE PROBLEM MANUALLY PRIOR TO THE NEXT RUN OF DB DEPLOY WILL RESULT IN ALL DATA THAT WAS IN " + targetTable.name + "!");
                        log.log(Logger.LogLevel.error, ex.Message);
                    }
                }

                if (!failureDetected)
                {
                    log.log(Logger.LogLevel.progress, "Complex alteration of " + targetTable.name + " completed successfully.");
                }
            }
        }
Exemplo n.º 8
0
 public Alteration(Table masterTable, Table targetTable, Ddl.Dialect dialect)
 {
     this.masterTable = masterTable;
     this.targetTable = targetTable;
     this.dialect = dialect;
 }
Exemplo n.º 9
0
        private bool AlterSimple(Alteration alteration, Table masterTable, Table targetTable, Ddl.Dialect targetDialect, OdbcConnection targetConnection)
        {
            bool success = false;

            string alterDdl = alteration.ddl;

            if (alterDdl == "")
            {
                AlterPrimaryKey(alteration, masterTable, targetTable, targetDialect, targetConnection);
                success = true;
            }
            else
            {
                try
                {
                    if (config.type == 4)
                    {
                        log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                        log.log(Logger.LogLevel.ddlChange, alteration.ddl + ";");
                        log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                    }
                    else
                    {
                        if (config.ddlLogging >= Configuration.DdlLogging.changes)
                        {
                            log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                            log.log(Logger.LogLevel.ddlChange, alteration.ddl + ";");
                            log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                        }

                        OdbcCommand alter = new OdbcCommand(alteration.ddl, target.connection);
                        alter.CommandTimeout = 0;
                        alter.ExecuteNonQuery();
                        log.log(Logger.LogLevel.change, "Table " + targetTable.name + " altered successfully.");
                        AlterPrimaryKey(alteration, masterTable, targetTable, targetDialect, targetConnection);
                        success = true;
                    }
                }
                catch (Exception ex)
                {
                    log.log(Logger.LogLevel.warning, "Exception occurred while trying to perform simple alteration of table " + targetTable.name + ".");
                    log.log(Logger.LogLevel.warning, ex.Message);
                }
            }
            return success;
        }
Exemplo n.º 10
0
        private void AlterPrimaryKey(Alteration alteration, Table masterTable, Table targetTable, Ddl.Dialect targetDialect, OdbcConnection targetConnection)
        {
            OdbcCommand alter = new OdbcCommand();
            alter.CommandType = CommandType.Text;
            alter.Connection = target.connection;

            if (alteration.ddlPrimaryKeyDrop != "")
            {
                try
                {
                    if (config.type == 4)
                    {
                        log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                        log.log(Logger.LogLevel.ddlChange, alteration.ddlPrimaryKeyDrop + ";");
                        log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                    }
                    else
                    {
                        if (config.ddlLogging >= Configuration.DdlLogging.changes)
                        {
                            log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                            log.log(Logger.LogLevel.ddlChange, alteration.ddlPrimaryKeyDrop + ";");
                            log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                        }

                        alter.CommandText = alteration.ddlPrimaryKeyDrop;
                        alter.CommandTimeout = 0;
                        alter.ExecuteNonQuery();
                        log.log(Logger.LogLevel.change, "Primary key constraint " + targetTable.primaryKey.constraintName + " dropped from table " + targetTable.name + " successfully.");
                    }
                }
                catch (Exception ex)
                {
                    log.log(Logger.LogLevel.error, "Exception occurred while trying to drop primary key constraint " + masterTable.primaryKey.constraintName + " from table " + targetTable.name + ".");
                    log.log(Logger.LogLevel.error, ex.Message);
                }
            }

            if (alteration.ddlPrimaryKeyAdd != "")
            {
                try
                {
                    if (config.type == 4)
                    {
                        log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                        log.log(Logger.LogLevel.ddlChange, alteration.ddlPrimaryKeyAdd + ";");
                        log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                    }
                    else
                    {
                        if (config.ddlLogging >= Configuration.DdlLogging.changes)
                        {
                            log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                            log.log(Logger.LogLevel.ddlChange, alteration.ddlPrimaryKeyAdd + ";");
                            log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                        }

                        alter.CommandText = alteration.ddlPrimaryKeyAdd;
                        alter.CommandTimeout = 0;
                        //Database["DB2"].ExecuteNonQuery("call SYSPROC.ADMIN_CMD ('REORG TABLE MyTable')");
                        if (targetDialect != Ddl.Dialect.sqlServer)
                        {
                            alter.CommandText = "CALL SYSPROC.ADMIN_CMD ('REORG TABLE " + targetTable.name + "'); " + alter.CommandText;
                        }
                        alter.ExecuteNonQuery();
                        log.log(Logger.LogLevel.change, "Primary key constraint " + masterTable.primaryKey.constraintName + " added to table " + targetTable.name + " successfully.");
                    }
                }
                catch (Exception ex)
                {
                    log.log(Logger.LogLevel.error, "Exception occurred while trying to add primary key constraint " + masterTable.primaryKey.constraintName + " from table " + targetTable.name + ".");
                    log.log(Logger.LogLevel.error, ex.Message);
                }
            }
        }
Exemplo n.º 11
0
        private void AlterForeignKey(Alteration alteration, Table masterTable, Table targetTable, Ddl.Dialect targetDialect, OdbcConnection targetConnection)
        {
            OdbcCommand alter = new OdbcCommand();
            alter.CommandType = CommandType.Text;
            alter.Connection = target.connection;

            if (alteration.ddlForeignKeyDrop != "")
            {
                try
                {
                    alter.CommandText = alteration.ddlForeignKeyDrop;
                    alter.CommandTimeout = 0;
                    alter.ExecuteNonQuery();
                    for (int count = 0; count < targetTable.foreignKey.columnNames.Count; count = count + 4)
                    {
                        log.log(Logger.LogLevel.change, "Foreign key constraint " + targetTable.foreignKey.columnNames[count] + " dropped from table " + targetTable.name + " successfully.");
                    }

                }
                catch (Exception ex)
                {
                    log.log(Logger.LogLevel.error, "Exception occurred while trying to drop foreign key constraint " + masterTable.foreignKey.columnNames[0] + " from table " + targetTable.name + ".");
                    log.log(Logger.LogLevel.error, ex.Message);
                }
            }

            if (alteration.ddlForeignKeyAdd != "")
            {
                try
                {
                    alter.CommandText = alteration.ddlForeignKeyAdd;
                    alter.CommandTimeout = 0;
                    alter.ExecuteNonQuery();
                    log.log(Logger.LogLevel.change, "Foreign key constraint " + masterTable.foreignKey.columnNames[0] + " added to table " + targetTable.name + " successfully.");
                }
                catch (Exception ex)
                {
                    log.log(Logger.LogLevel.error, "Exception occurred while trying to add foreign key constraint " + masterTable.primaryKey.columnNames[0] + " from table " + targetTable.name + ".");
                    log.log(Logger.LogLevel.error, ex.Message);
                }
            }
        }
Exemplo n.º 12
0
        private bool AlterEasy(Alteration alteration, Table masterTable, Table targetTable, Ddl.Dialect targetDialect, OdbcConnection targetConnection)
        {
            bool canDo = true;
            bool success = false;

            string alterDdl = "ALTER TABLE " + targetTable.name + "\r\n";
            string nochangeDdl = alterDdl;

            foreach (Column col in alteration.dropColumns.Values)
                alterDdl += "    DROP COLUMN " + col.name + "\r\n";

            foreach (Column col in alteration.addColumns.Values)
            {
                if (targetDialect == Ddl.Dialect.db2)
                {
                    alterDdl += "    ADD COLUMN " + Ddl.columnDefinition(col, targetDialect) + "\r\n";
                }
                else
                {
                    alterDdl += "    ADD " + Ddl.columnDefinition(col, targetDialect) + "\r\n";
                }
            }

            foreach (ColumnAlterCandidate candidate in alteration.alterColumns)
            {
                if (candidate.masterColumn.typeName.ToUpper() != candidate.targetColumn.typeName.ToUpper())
                {
                    canDo = false;
                    log.log(Logger.LogLevel.warning, "Complex alteration required because " + candidate.targetColumn.name + "'s type was changed.");
                    break;
                }

                if (candidate.masterColumn.columnSize != candidate.targetColumn.columnSize)
                {
                    if (candidate.masterColumn.typeName.ToUpper() != "VARCHAR")
                    {
                        canDo = false;
                        log.log(Logger.LogLevel.warning, "Complex alteration required because " + candidate.targetColumn.name + "'s length was changed whose type was not VARCHAR.");
                        break;
                    }

                    if (candidate.masterColumn.columnSize > candidate.targetColumn.columnSize)
                    {
                        canDo = false;
                        log.log(Logger.LogLevel.warning, "Complex alteration required because " + candidate.targetColumn.name + "'s length was changed to less than its current length.");
                        break;
                    }

                    if (canDo)
                    {
                        alterDdl += "    ALTER COLUMN " + candidate.targetColumn.name + " SET DATA TYPE VARCHAR(" + candidate.masterColumn.columnSize + ")\r\n";
                    }
                }

                if (candidate.masterColumn.decimalDigits != candidate.targetColumn.decimalDigits)
                {
                    canDo = false;
                    log.log(Logger.LogLevel.warning, "Complex alteration required because " + candidate.targetColumn.name + "'s decimal digits changed.");
                    break;
                }

                if (candidate.masterColumn.nullable != candidate.targetColumn.nullable)
                {
                    if (candidate.targetColumn.nullable)
                    {
                        canDo = false;
                        log.log(Logger.LogLevel.warning, "Complex alteration required because " + candidate.targetColumn.name + " that was defined as NOT NULL must be changed to NULL.");
                        break;
                    }

                    if (canDo)
                    {
                        //alterDdl += "    ALTER COLUMN " + candidate.targetColumn.name + " SET NOT NULL\r\n";
                        alterDdl += "    ALTER COLUMN " + candidate.targetColumn.name + " DROP NOT NULL\r\n";
                    }
                }

                if (candidate.masterColumn.defaultValue != candidate.targetColumn.defaultValue)
                {
                    // Alter the default value here!
                    if (canDo)
                    {
                        if (candidate.masterColumn.defaultValue == null)
                            alterDdl += "    ALTER COLUMN " + candidate.targetColumn.name + " DROP DEFAULT " + "\r\n";
                        else
                            alterDdl += "    ALTER COLUMN " + candidate.targetColumn.name + " SET DEFAULT " + candidate.masterColumn.defaultValue + "\r\n";
                    }
                }
            }

            if (canDo)
            {
                if (alterDdl == nochangeDdl)
                {
                    AlterPrimaryKey(alteration, masterTable, targetTable, targetDialect, targetConnection);
                    success = true;
                }
                else
                {
                    try
                    {
                        if (config.type == 4)
                        {
                            log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                            log.log(Logger.LogLevel.ddlChange, alterDdl + ";");
                            log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                        }
                        else
                        {
                            if (config.ddlLogging >= Configuration.DdlLogging.changes)
                            {
                                log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                                log.log(Logger.LogLevel.ddlChange, alterDdl + ";");
                                log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                            }

                            OdbcCommand alter = new OdbcCommand(alterDdl, target.connection);
                            alter.CommandTimeout = 0;
                            alter.ExecuteNonQuery();
                            log.log(Logger.LogLevel.change, "Table " + targetTable.name + " altered successfully.");
                            AlterPrimaryKey(alteration, masterTable, targetTable, targetDialect, targetConnection);
                            success = true;
                        }
                    }
                    catch (Exception ex)
                    {
                        log.log(Logger.LogLevel.warning, "Exception occurred while trying to alter table " + targetTable.name + ".");
                        log.log(Logger.LogLevel.warning, ex.Message);
                        log.log(Logger.LogLevel.warning, "Complex alteration will be attempted for table " + targetTable.name + ".");
                    }
                }
            }

            if (canDo)
            {
                if (alterDdl == nochangeDdl)
                {
                    //AlterForeignKey(alteration, masterTable, targetTable, targetDialect, targetConnection);
                    //success = true;
                }
                else
                {
                    try
                    {
                        if (config.type == 4)
                        {
                            log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                            log.log(Logger.LogLevel.ddlChange, alterDdl + ";");
                            log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                        }
                        else
                        {
                            if (config.ddlLogging >= Configuration.DdlLogging.changes)
                            {
                                log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                                log.log(Logger.LogLevel.ddlChange, alterDdl + ";");
                                log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                            }

                            OdbcCommand alter = new OdbcCommand(alterDdl, target.connection);
                            alter.CommandTimeout = 0;
                            alter.ExecuteNonQuery();
                            log.log(Logger.LogLevel.change, "Table " + targetTable.name + " altered successfully.");
                            //AlterForeignKey(alteration, masterTable, targetTable, targetDialect, targetConnection);
                            //success = true;
                        }
                    }
                    catch (Exception ex)
                    {
                        log.log(Logger.LogLevel.warning, "Exception occurred while trying to alter table " + targetTable.name + ".");
                        log.log(Logger.LogLevel.warning, ex.Message);
                        log.log(Logger.LogLevel.warning, "Complex alteration will be attempted for table " + targetTable.name + ".");
                    }
                }
            }
            return success;
        }
Exemplo n.º 13
0
        private string findPrimaryKeyColumnNameSqlServer(Table table)
        {
            OdbcCommand command = new OdbcCommand();
            command.Connection = target.connection;
            command.CommandType = CommandType.Text;

            // GET Primary Key Name
            command.CommandText = " SELECT i.name AS IndexName FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic " +
                                  " ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id WHERE i.is_primary_key = 1 " +
                                  " and OBJECT_NAME(ic.OBJECT_ID) = '" + table.name + "' ";

            object value = command.ExecuteScalar();

            string pkName = "";
            //if (value == DBNull.Value)
            if (value == null)
                pkName = "";
            else
                pkName = (string)(value);

            return pkName;
        }
Exemplo n.º 14
0
        private string findPrimaryKeyColumnName(Table table)
        {
            OdbcCommand command = new OdbcCommand();
            command.Connection = target.connection;
            command.CommandType = CommandType.Text;

            // GET Primary Key Name
            command.CommandText = "  SELECT K.COLNAME " +
                                  "  FROM SYSCAT.KEYCOLUSE K, SYSCAT.TABCONST T  " +
                                  "  WHERE K.CONSTNAME = T.CONSTNAME AND K.TABNAME = T.TABNAME  " +
                                  "  AND K.TABSCHEMA = '" + table.schema + "'" +
                                  "  AND K.TABNAME = '" + table.name + "' " +
                                  "  AND T.TYPE = 'P'";

            object value = command.ExecuteScalar();

            string pkName = "";
            //if (value == DBNull.Value)
            if (value == null)
                pkName = "";
            else
                pkName = (string)(value);

            return pkName;
        }
Exemplo n.º 15
0
        private void DropAndCreate(Table masterTable, Table targetTable, Ddl.Dialect targetDialect, OdbcConnection targetConnection)
        {
            log.log(Logger.LogLevel.change, "Dropping and recreating table " + targetTable.name + ".");

            try
            {
                if (config.type == 4)
                {
                    log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                    log.log(Logger.LogLevel.ddlChange, "drop table " + targetTable.name + ";");
                    log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                }
                else
                {
                    if (config.ddlLogging >= Configuration.DdlLogging.changes)
                    {
                        log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                        log.log(Logger.LogLevel.ddlChange, "drop table " + targetTable.name + ";");
                        log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                    }

                    OdbcCommand drop = new OdbcCommand();
                    drop.Connection = targetConnection;
                    drop.CommandType = CommandType.Text;
                    drop.CommandText = "drop table " + targetTable.name;
                    drop.CommandTimeout = 0;
                    drop.ExecuteNonQuery();
                    log.log(Logger.LogLevel.change, "Table " + targetTable.name + " dropped successfully.");
                }
            }
            catch (Exception ex)
            {
                log.log(Logger.LogLevel.error, "Exception occurred while trying to drop table " + targetTable.name + ".");
                log.log(Logger.LogLevel.error, ex.Message);
            }

            try
            {
                //TableSapce Change
                if (targetDialect == Ddl.Dialect.db2)
                {
                    findTableSpaceSQL(masterTable.name);
                }
                //TableSapce Change

                if (config.type == 4)
                {
                    log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                    log.log(Logger.LogLevel.ddlChange, (Ddl.ddl(masterTable, Ddl.Dialect.generic) + TableSpaceSQL + ";").Replace("DATETIME", "TIMESTAMP"));
                    log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                }
                else
                {
                    if (config.ddlLogging >= Configuration.DdlLogging.changes)
                    {
                        log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                        log.log(Logger.LogLevel.ddlChange, (Ddl.ddl(masterTable, Ddl.Dialect.generic) + TableSpaceSQL + ";").Replace("DATETIME", "TIMESTAMP"));
                        log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
                    }

                    //OdbcCommand create = new OdbcCommand(Ddl.ddl(masterTable, targetDialect), targetConnection);
                    OdbcCommand create = new OdbcCommand();
                    if (targetDialect == Ddl.Dialect.sqlServer)
                        create = new OdbcCommand(Ddl.ddl(masterTable, targetDialect).Replace("getdate", "getdate()"), targetConnection);
                    else
                        create = new OdbcCommand(Ddl.ddl(masterTable, targetDialect), targetConnection);

                    create.CommandTimeout = 0;
                    create.CommandText += TableSpaceSQL;
                    create.ExecuteNonQuery();
                    log.log(Logger.LogLevel.change, "Table " + targetTable.name + " created successfully.");
                }
            }
            catch (Exception ex)
            {
                log.log(Logger.LogLevel.error, "Exception occurred while trying to create table " + masterTable.name + ".");
                log.log(Logger.LogLevel.error, ex.Message);
            }
        }
Exemplo n.º 16
0
 public ForeignKey(Table table)
 {
     this.table = table;
 }
Exemplo n.º 17
0
        public Column(
            Table table, 
            string name, 
            //OdbcType type, 
            string typeName, 
            int columnSize,
            short decimalDigits,
            bool nullable,
            string defaultValue
            )
        {
            this.table = table;
            this.name = name;

            this.typeName = typeName;

            if (this.typeName == "CHAR () FOR BIT DATA") // DB2
            {
                this.typeName = "CHAR";
                this.specialTypeExtender = "FOR BIT DATA";
            }
            if (this.typeName == "VARCHAR () FOR BIT DATA") // DB2
            {
                this.typeName = "VARCHAR";
                this.specialTypeExtender = "FOR BIT DATA";
            }
            if (this.typeName == "INTEGER") // DB2
                this.typeName = "INT";
            else if (this.typeName == "CLOB") // DB2
                this.typeName = "TEXT";
            else if (this.typeName == "BLOB") // DB2
                this.typeName = "BINARY";
            else if (this.typeName == "TIMESTAMP" && table.database.dialect == Ddl.Dialect.db2)
                this.typeName = "DATETIME";
            else if (this.typeName == "int identity")  // MS SQL 2005
                this.typeName = "int";
            else if (this.typeName == "bigint identity")  // MS SQL 2005
                this.typeName = "bigint";
            else if (this.typeName == "float")  // MS SQL 2005
                this.typeName = "double";
            //else if (this.typeName == "money")  // MS SQL 2005
            //    this.typeName = "decimal";
            else if (this.typeName == "smallint identity")  // MS SQL 2008 R2
                this.typeName = "smallint";
            else if (this.typeName == "tinyint identity")  // MS SQL 2008 R2
                this.typeName = "tinyint";

            else if (this.typeName == "datetime2")  // MS SQL 2008 R2
            {
                this.typedateTime2 = true;
                this.typeName = "datetime";
            }

            else if (this.typeName == "datetimeoffset")  // MS SQL 2008 R2
            {
                this.typedateTimeoffset = true;
                this.typeName = "datetime";
            }

            else if (this.typeName == "geography")  // MS SQL 2008 R2
            {
                this.typegeography = true;
                this.typeName = "Text";
            }

            else if (this.typeName == "geometry")  // MS SQL 2008 R2
            {
                this.typegeometry = true;
                this.typeName = "Text";
            }

            else if (this.typeName == "hierarchyid")  // MS SQL 2008 R2
            {
                this.typehierarchyid = true;
                this.typeName = "Text";
            }

            else if (this.typeName == "smallmoney")  // MS SQL 2008 R2
            {
                this.typesmallmoney = true;
                this.typeName = "Double";
            }

            else if (this.typeName == "money")  // MS SQL 2008 R2
            {
                this.typemoney = true;
                this.typeName = "Double";
            }

            else if (this.typeName == "sql_variant")  // MS SQL 2008 R2
            {
                this.typesql_variant = true;
                this.typeName = "Text";
            }

            else if (this.typeName == "xml")  // MS SQL 2008 R2
            {
                this.typexml = true;
                this.typeName = "Text";
            }

            this.type = (OdbcType)Enum.Parse(typeof(OdbcType), this.typeName, true);

            this.columnSize = columnSize;
            this.decimalDigits = decimalDigits;

            this.nullable = nullable;
            this.defaultValue = defaultValue;

            parameter = new OdbcParameter("@" + this.name, this.type);
            if(this.type == OdbcType.Timestamp)
                parameter.DbType = DbType.DateTime;

            identity = DbIdentity.check(this);
        }
Exemplo n.º 18
0
        public static Alteration alter(Table masterTable, Table targetTable, Dialect dialect)
        {
            Alteration alteration = new Alteration(masterTable, targetTable, dialect);

            //string sql = "CREATE TABLE " + table.schema + "." + table.name + "\r\n(\r\n";

            string sqlCmn = "";
            string sql = "";
            if (dialect == Dialect.db2)
            {
                sql = "ALTER TABLE " + targetTable.name + "\r\n";
            }
            else
            {
                sqlCmn = "ALTER TABLE " + targetTable.name + "\r\n";
                sql = "";
            }

            string noChangeSql = sql;

            //int ordinal = 0;

            // Drop any "extra" columns in the target table
            foreach (Column column in targetTable.columns.Values)
            {
                if (masterTable.columns.ContainsKey(column.name))
                {
                    alteration.commonColumns.Add(column.name, column);
                }
                else
                {
                    if (dialect == Dialect.db2)
                    {
                        sql += "    DROP COLUMN " + column.name + "\r\n";
                    }
                    else
                    {
                        sql += sqlCmn + "    DROP COLUMN " + column.name + "\r\n";
                    }
                    alteration.dropColumns.Add(column.name,column);
                }
            }

            // Add any "new" columns in from the master table.
            // Also update any changes between to column data types
            string colDef;
            foreach (Column column in masterTable.columns.Values)
            {
                if (targetTable.columns.ContainsKey(column.name))
                {
                    colDef = columnDefinition(targetTable.columns[column.name], dialect);
                    if (colDef != columnDefinition(column, dialect))
                    {
                        if (dialect == Dialect.db2)
                        {
                            sql += "    ALTER COLUMN " + columnDefinition(column, dialect) + "\r\n";
                        }
                        else
                        {
                            sql += sqlCmn + "    ALTER COLUMN " + columnDefinition(column, dialect) + "\r\n";
                        }

                        alteration.alterColumns.Add(new ColumnAlterCandidate(column, targetTable.columns[column.name]));
                    }
                }
                else
                {
                    if (dialect == Dialect.db2)
                        sql += "    ADD COLUMN " + columnDefinition(column, dialect) + "\r\n";
                    else
                    {
                        //sql += "    ADD " + columnDefinition(column, dialect) + "\r\n";
                        sql += sqlCmn + "    ADD " + columnDefinition(column, dialect) + "\r\n";
                    }
                    alteration.addColumns.Add(column.name,column);
                }
            }

            // Check to see if primary keys are the same
            bool primaryKeySame = false;

            //if (masterTable.primaryKey.constraintName == targetTable.primaryKey.constraintName)
            //{
            if (masterTable.primaryKey.columnNames.Count == targetTable.primaryKey.columnNames.Count)
            {
                primaryKeySame = true;
                for (int index = 0; index < masterTable.primaryKey.columnNames.Count; index++)
                {
                    if (masterTable.primaryKey.columnNames[index] != targetTable.primaryKey.columnNames[index])
                    {
                        primaryKeySame = false;
                        break;
                    }
                    if (masterTable.primaryKey.constraintName != targetTable.primaryKey.constraintName)
                    {
                        primaryKeySame = false;
                        break;
                    }
                }
            }
            //}

            alteration.ddlPrimaryKeyDrop = "";
            alteration.ddlPrimaryKeyAdd = "";

            if (!primaryKeySame)
            {
                string sqlDrop = "";
                string sqlAdd = "";
                if (dialect == Dialect.db2)
                {
                    sqlDrop = "ALTER TABLE " + targetTable.name + "\r\n" + "    DROP PRIMARY KEY\r\n";
                    sqlAdd = "ALTER TABLE " + targetTable.name + "\r\n" + "    ADD " + primaryKeyConstraint(masterTable, dialect);
                }
                else
                {
                    if (masterTable.primaryKey.constraintName != "")
                    {
                        sqlDrop = "ALTER TABLE " + targetTable.name + "\r\n" + "    DROP CONSTRAINT " + targetTable.primaryKey.constraintName + "\r\n";
                        sqlAdd = "ALTER TABLE " + targetTable.name + "\r\n" + "    ADD " + primaryKeyConstraint(masterTable, dialect);
                    }
                    if (masterTable.primaryKey.constraintName == "")
                    {
                        sqlDrop = "ALTER TABLE " + targetTable.name + "\r\n" + "    DROP CONSTRAINT " + targetTable.primaryKey.constraintName + "\r\n";
                    }
                }

                //if (masterTable.primaryKey.constraintName == targetTable.primaryKey.constraintName)
                //{
                if (masterTable.primaryKey.columnNames.Count > 0)
                {
                    if (targetTable.primaryKey.columnNames.Count > 0)
                    {
                        alteration.ddlPrimaryKeyDrop = sqlDrop;
                        alteration.ddlPrimaryKeyAdd = sqlAdd;
                    }
                    else
                    {
                        alteration.ddlPrimaryKeyAdd = sqlAdd;
                    }
                }
                else
                {
                    if (targetTable.primaryKey.columnNames.Count > 0)
                    {
                        alteration.ddlPrimaryKeyDrop = sqlDrop;
                    }
                }
                //}
                //else
                //{
                //    alteration.ddlPrimaryKeyDrop = sqlDrop;
                //    alteration.ddlPrimaryKeyAdd = sqlAdd;
                //}
            }

            /*
            // Check to see if foreign keys are the same
            bool foreignKeySame = false;

            if (masterTable.foreignKey.columnNames.Count == targetTable.foreignKey.columnNames.Count)
            {
                foreignKeySame = true;
                for (int index = 0; index < masterTable.foreignKey.columnNames.Count; index++)
                {
                    if (masterTable.foreignKey.columnNames[index] != targetTable.foreignKey.columnNames[index])
                    {
                        foreignKeySame = false;
                        break;
                    }
                }
            }

            alteration.ddlForeignKeyDrop = "";
            alteration.ddlForeignKeyAdd = "";

            if (!foreignKeySame)
            {
                string sqlDrop = "";
                string sqlAdd = "";
                for (int count = 0; count < masterTable.foreignKey.columnNames.Count; count = count + 4)
                {
                    sqlDrop += "ALTER TABLE " + targetTable.name + "\r\n" + "    DROP FOREIGN KEY\r\n" + "\r\n" + masterTable.foreignKey.columnNames[count] + ";";
                    sqlAdd += "ALTER TABLE " + targetTable.name + "\r\n" + "    ADD " + foreignKeyConstraint(masterTable, dialect);
                }

                if (masterTable.foreignKey.columnNames.Count > 0)
                {
                    if (targetTable.foreignKey.columnNames.Count > 0)
                    {
                        alteration.ddlForeignKeyDrop = sqlDrop;
                        alteration.ddlForeignKeyAdd = sqlAdd;
                    }
                    else
                    {
                        alteration.ddlForeignKeyAdd = sqlAdd;
                    }
                }
                else
                {
                    if (targetTable.foreignKey.columnNames.Count > 0)
                    {
                        for (int count = 0; count < targetTable.foreignKey.columnNames.Count; count = count + 4)
                        {
                            sqlDrop += "ALTER TABLE " + targetTable.name + "\r\n" + "    DROP FOREIGN KEY\r\n" + "\r\n" + targetTable.foreignKey.columnNames[count] + ";";
                        }
                        alteration.ddlForeignKeyDrop = sqlDrop;
                    }
                }
            }
            */

            if (sql == noChangeSql)
                sql = "";

            alteration.ddl = sql;

            return alteration;
        }
Exemplo n.º 19
0
 public Trigger(Table table, string name, string TriggerTable)
 {
     this.table = table;
     this.name = name;
     this.TriggerTable = TriggerTable;
 }
Exemplo n.º 20
0
        private void Alter(Table masterTable, Table targetTable, Ddl.Dialect targetDialect, OdbcConnection targetConnection)
        {
            //TODO: 1. Check for appropriate return value here.
            Alteration alteration = Ddl.alter(masterTable, targetTable, targetDialect);

            // Check for simple alteration
            if (alteration.alterColumns.Count == 0 &&
                ((alteration.addColumns.Count > 0 && alteration.dropColumns.Count == 0) ||
                (alteration.addColumns.Count == 0 && alteration.dropColumns.Count > 0))
            )
            {
                if (!AlterSimple(alteration, masterTable, targetTable, targetDialect, targetConnection))
                {
                    AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
                }
            }
            else
            {
                // Complex alteration
                // Are we moving data from the source?
                //if (ProcessData(masterTable.name))
                if (ProcessData(masterTable.name) && (!config.filterTables.ContainsKey(masterTable.name)))
                {
                    // Yes - drop the current table and recreate it, data will be copied over later via standard process
                    DropAndCreate(masterTable, targetTable, targetDialect, targetConnection);
                }
                else
                {
                    // No - need to copy the data in the current table to the newly created one...
                    // See if we need to get a mapping from the user...
                    int mappedCount = 0;
                    if (alteration.addColumns.Count > 0 && alteration.dropColumns.Count > 0)
                    {
                        MappingForm form = new MappingForm();

                        form.tableNameText.Text = masterTable.name;

                        foreach (Column column in alteration.dropColumns.Values)
                            form.oldColumnList.Items.Add(column);

                        foreach (Column column in alteration.addColumns.Values)
                            form.newColumnList.Items.Add(column);

                        if (form.ShowDialog() != DialogResult.OK)
                        {
                            log.log(Logger.LogLevel.error, "User cancelled out of a required mapping.  Alteration of table " + targetTable.name + " skipped.");
                            return;
                        }
                        mappedCount = form.mappedColumnList.Items.Count;

                        ////Update the alteration structure from the mapping form editor
                        //alteration.dropColumns.Clear();
                        //foreach (Column column in form.oldColumnList.Items)
                        //    alteration.dropColumns.Add(column.name, column);

                        //alteration.addColumns.Clear();
                        //foreach (Column column in form.newColumnList.Items)
                        //    alteration.addColumns.Add(column.name, column);

                        alteration.mappings.Clear();
                        foreach (Mapping mapping in form.mappedColumnList.Items)
                            alteration.mappings.Add(mapping);
                    }

                    if (mappedCount == 0 && alteration.alterColumns.Count == 0)
                    {
                        if (Ddl.ddlTemp(masterTable, Ddl.Dialect.generic) == Ddl.ddlTemp(targetTable, Ddl.Dialect.generic))
                        {
                            if (!AlterSimple(alteration, masterTable, targetTable, targetDialect, targetConnection))
                            {
                                AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
                            }
                        }
                        else
                        {
                            AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
                        }
                    }
                    else
                    {
                        if (targetDialect == Ddl.Dialect.sqlServer)
                        {
                            if (Ddl.ddlTemp(masterTable, Ddl.Dialect.generic) == Ddl.ddlTemp(targetTable, Ddl.Dialect.generic))
                            {
                                if (!AlterSimple(alteration, masterTable, targetTable, targetDialect, targetConnection))
                                {
                                    AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
                                }
                            }
                            else
                            {
                                AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
                            }
                        }

                        //else if (mappedCount != 0 && alteration.mappings.Count != 0)
                        //{
                        //    if (!AlterEasy(alteration, masterTable, targetTable, targetDialect, targetConnection))
                        //    {
                        //        AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
                        //    }
                        //}

                        else if (mappedCount >= 0)
                        {
                            if (Ddl.ddlTemp(masterTable, Ddl.Dialect.generic) == Ddl.ddlTemp(targetTable, Ddl.Dialect.generic))
                            {
                                if (!AlterEasy(alteration, masterTable, targetTable, targetDialect, targetConnection))
                                {
                                    AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
                                }
                            }
                            else
                            {
                                AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
                            }
                        }
                    }
                }
            }
        }