//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;
}
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;
}
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;
}
public Mapping(Column fromColumn, Column toColumn)
{
this.fromColumn = fromColumn;
this.toColumn = toColumn;
}
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;
}
public ColumnAlterCandidate(Column masterColumn, Column targetColumn)
{
this.masterColumn = masterColumn;
this.targetColumn = targetColumn;
}