Esempio n. 1
0
        //TODO: Should add SQL Server specific attribute here (last_value)
        public static DbIdentity check(Column column)
        {
            DbIdentity returnValue = null;

            Table table = column.table;
            string platform = table.database.platform;

            OdbcCommand command;
            OdbcDataReader reader;

            switch (table.database.dialect)
            {
                case Ddl.Dialect.db2:
                    command = new OdbcCommand(
                        "select cid.*, generated" +
                        " from syscat.columns col, syscat.colidentattributes cid" +
                        " where col.tabschema = '" +
                        table.schema +
                        "' and col.tabname = '" +
                        table.name +
                        "' and col.colname = '" +
                        column.name +
                        "'" +
                        " and cid.tabschema = col.tabschema" +
                        " and cid.tabname = col.tabname" +
                        " and cid.colname = col.colname",
                        table.database.connection);

                    reader = command.ExecuteReader();

                    if (reader.Read())
                    {
                        returnValue = new DbIdentity(
                            (decimal)reader["START"],
                            (decimal)reader["INCREMENT"],
                            (string)reader["GENERATED"]
                            );
                    }

                    reader.Close();
                    break;

                case Ddl.Dialect.sqlServer:
                    //TODO: Complete
                    //TODO: Check for version (pre-2005) can't get identity metrics
                    command = new OdbcCommand(
                        "select scm.name schemaName, " +
                            "tbl.name tableName, " +
                            "idc.name columnName, " +
                            "seed_value seedValue, " +
                            "increment_value incrementValue " +
                        "from sys.schemas scm, " +
                            "sys.tables tbl, " +
                            "sys.identity_columns idc " +
                        "where tbl.schema_id = scm.schema_id " +
                            "and idc.object_id = tbl.object_id " +
                            "and scm.name = '" + table.schema + "' " +
                            "and tbl.name = '" + table.name + "' " +
                            "and idc.name = '" + column.name + "' ",
                        table.database.connection);

                    reader = command.ExecuteReader();

                    if (reader.Read())
                    {
                        if( column.type == OdbcType.BigInt )
                            returnValue = new DbIdentity(
                                (decimal)(long)reader["seedValue"],
                                (decimal)(long)reader["incrementValue"],
                                ""
                                );
                        // MS SQL 2008 R2
                        else if (column.type == OdbcType.SmallInt)
                            returnValue = new DbIdentity(
                                (decimal)(Int16)reader["seedValue"],
                                (decimal)(Int16)reader["incrementValue"],
                                ""
                                );
                        // MS SQL 2008 R2
                        else if (column.type == OdbcType.TinyInt)
                            returnValue = new DbIdentity(
                                (decimal)(byte)reader["seedValue"],
                                (decimal)(byte)reader["incrementValue"],
                                ""
                                );
                        else
                            returnValue = new DbIdentity(
                                (decimal)(int)reader["seedValue"],
                                (decimal)(int)reader["incrementValue"],
                                ""
                                );
                    }

                    reader.Close();
                    break;
            }

            return returnValue;
        }
Esempio n. 2
0
        private static string defaultDefinition(Column column)
        {
            string sql = " DEFAULT ";

            switch( column.type )
            {
                    /*
                case OdbcType.Char:
                case OdbcType.Date:
                case OdbcType.DateTime:
                case OdbcType.NChar:
                case OdbcType.NText:
                case OdbcType.NVarChar:
                case OdbcType.SmallDateTime:
                case OdbcType.Text:
                case OdbcType.Time:
                case OdbcType.Timestamp:
                case OdbcType.VarChar:
                    sql += "'" + column.defaultValue + "'";
                    break;
                     */

                default:
                    sql += column.defaultValue.Replace("(","").Replace(")","");
                    break;
            }

            return sql;
        }
Esempio n. 3
0
        public static string columnDefinition(Column column, Dialect dialect)
        {
            string sql = "    \"" + column.name + "\"";

            if (column.typeName.ToUpper() == "TEXT" && dialect == Dialect.db2)
                sql += " CLOB"; // Convert CLOB for DB2 platform
            else if (column.typeName.ToUpper() == "BINARY" && dialect == Dialect.db2)
                sql += " BLOB"; // Convert BLOB for DB2 platform
            else if (column.typeName.ToUpper() == "DATETIME" && dialect == Dialect.db2)
                sql += " TIMESTAMP"; // Convert TIMESTAMP for DB2 platform
            else if (column.typeName.ToUpper() == "DATE" && (dialect == Dialect.generic || dialect == Dialect.sqlServer))
                sql += " DATE"; // Convert DATE for SQL Server platform
            else if (column.typeName.ToUpper() == "DATETIME" && (dialect == Dialect.generic || dialect == Dialect.sqlServer) && (!column.typedateTime2) && (!column.typedateTimeoffset))
                sql += " DATETIME"; // Convert DATETIME for SQL Server platform
            else if (column.typeName.ToUpper() == "TIME" && (dialect == Dialect.generic || dialect == Dialect.sqlServer))
                sql += " TIME"; // Convert TIME for SQL Server platform
            else if (column.typeName.ToUpper() == "DOUBLE" && dialect == Dialect.sqlServer && (!column.typesmallmoney) && (!column.typemoney))
                sql += " FLOAT"; // Convert FLOAT for SQL Server platform

            else if (column.typeName.ToUpper() == "TEXT" && (dialect == Dialect.generic || dialect == Dialect.sqlServer) && column.typexml)
                sql += " XML"; // Convert XML for SQL Server platform

            else if (column.typeName.ToUpper() == "DATETIME" && (dialect == Dialect.generic || dialect == Dialect.sqlServer) && column.typedateTime2)
                sql += " DATETIME2"; // Convert DATETIME2 for SQL Server platform

            else if (column.typeName.ToUpper() == "DATETIME" && (dialect == Dialect.generic || dialect == Dialect.sqlServer) && column.typedateTimeoffset)
                sql += " DATETIMEOFFSET"; // Convert DATETIMEOFFSET for SQL Server platform

            else if (column.typeName.ToUpper() == "TEXT" && (dialect == Dialect.generic || dialect == Dialect.sqlServer) && column.typegeography)
                sql += " GEOGRAPHY"; // Convert GEOGRAPHY for SQL Server platform

            else if (column.typeName.ToUpper() == "TEXT" && (dialect == Dialect.generic || dialect == Dialect.sqlServer) && column.typegeometry)
                sql += " GEOMETRY"; // Convert GEOMETRY for SQL Server platform

            else if (column.typeName.ToUpper() == "TEXT" && (dialect == Dialect.generic || dialect == Dialect.sqlServer) && column.typehierarchyid)
                sql += " HIERARCHYID"; // Convert HIERARCHYID for SQL Server platform

            else if (column.typeName.ToUpper() == "DOUBLE" && (dialect == Dialect.generic || dialect == Dialect.sqlServer) && column.typesmallmoney)
                sql += " SMALLMONEY"; // Convert SMALLMONEY for SQL Server platform

            else if (column.typeName.ToUpper() == "DOUBLE" && (dialect == Dialect.generic || dialect == Dialect.sqlServer) && column.typemoney)
                sql += " MONEY"; // Convert MONEY for SQL Server platform

            else if (column.typeName.ToUpper() == "TEXT" && (dialect == Dialect.generic || dialect == Dialect.sqlServer) && column.typesql_variant)
                sql += " SQL_VARIANT"; // Convert SQL_VARIANT for SQL Server platform

            else
                sql += " " + column.typeName.ToUpper();

            if (column.typeName.ToUpper() == "CHAR" || column.typeName.ToUpper() == "VARCHAR" || column.typeName.ToUpper() == "NVARCHAR" )
            {
                sql += "(" + column.columnSize + ")";
                if (dialect == Dialect.db2)
                {
                    sql += " " + column.specialTypeExtender;
                }
            }
            ////Added the else if condition to check the datatype varbinary
            //else  if (column.typeName.ToUpper() ==  "VARBINARY")
            //{
            //    if (column.columnSize != 0)
            //    {
            //        sql += "(" + column.columnSize + ")";
            //    }
            //    if (column.columnSize == 0) //If column size is max then taking as columnsize 0 to overcome that issue added this condition
            //    {
            //        sql += "(" + "Max" + ")";
            //    }
            //    if (dialect == Dialect.db2)
            //    {
            //        sql += " " + column.specialTypeExtender;
            //    }
            //}
            //else if (column.typeName.ToUpper() == "DECIMAL")
            //    sql += "(" + column.columnSize + "," + column.decimalDigits + ")";
            else if (column.typeName.ToUpper() == "DECIMAL")
                sql += "(" + column.columnSize + "," + column.decimalDigits + ")";
            else if (column.typeName.ToUpper() == "NUMERIC")
                sql += "(" + column.columnSize + "," + column.decimalDigits + ")";
            else if (column.typeName.ToUpper() == "BINARY")
                sql += "(" + column.columnSize + ")";
            else if (column.typeName.ToUpper() == "NCHAR")
                sql += "(" + column.columnSize + ")";
            else if (column.typeName.ToUpper() == "VARBINARY")
                sql += "(" + column.columnSize + ")";

            switch (dialect)
            {
                case Dialect.generic:
                case Dialect.db2:
                    if (!column.nullable)
                        sql += " NOT NULL";

                    if (column.defaultValue != null)
                        sql += defaultDefinition(column);

                    if (column.identity != null)
                    {
                        if (dialect == Dialect.db2)
                        {
                            if (column.identity.generated == "A")
                                sql += " GENERATED ALWAYS";
                            else if (column.identity.generated == "D")
                                sql += " GENERATED BY DEFAULT";
                        }

                        sql += " AS IDENTITY";

                        sql += "(START WITH " + column.identity.start;
                        sql += " INCREMENT BY " + column.identity.increment;

                        if (dialect == Dialect.db2)
                        {
                            //TODO: Complete by adding in minvalue, maxvalue, no cycle, cache, order
                        }

                        sql += ")";
                    }
                    break;

                case Dialect.sqlServer:
                    if (column.identity != null)
                    {
                        sql += " IDENTITY";
                        sql += " (" + column.identity.start + "," + column.identity.increment + ")";
                    }

                    if (!column.nullable)
                    {
                        sql += " NOT NULL";
                    }
                    else
                    {
                        sql += " NULL";
                    }

                    if (column.defaultValue != null)
                        sql += defaultDefinition(column);

                    break;
            }

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

            return sql;
        }
Esempio n. 4
0
 public Mapping(Column fromColumn, Column toColumn)
 {
     this.fromColumn = fromColumn;
     this.toColumn = toColumn;
 }
Esempio n. 5
0
        private long maxValue(Column column, string sequencename)
        {
            // Exceptions for this DB command are caught at the level above.

            OdbcCommand command = new OdbcCommand();
            command.Connection = target.connection;
            command.CommandType = CommandType.Text;

            // GET MAX VALUE
            command.CommandText = "SELECT MAX(" + column.name + ") " +
                "FROM " + column.table.name;

            if (sequencename == "SEQ_E_HIR_NODE_STATIC")
            {
                command.CommandText += " WHERE E_HIR_NODE < 1000000 ";
            }

            object value = command.ExecuteScalar();

            long maxValue = 0;
            if (value == DBNull.Value)
                maxValue = 0;
            else
            {
                // TODO: 4. Should handle smallint and decimal here as well
                if (column.type == OdbcType.BigInt)
                    maxValue = (long)(value);
                else
                    maxValue = (int)(value);
            }

            return maxValue;
        }
Esempio n. 6
0
 public ColumnAlterCandidate(Column masterColumn, Column targetColumn)
 {
     this.masterColumn = masterColumn;
     this.targetColumn = targetColumn;
 }