/// <summary>
/// Creates a TableSchema object using the specified SQL Server connection
/// and the name of the table for which we need to create the schema.
/// </summary>
/// <param name="conn">The SQL Server connection to use</param>
/// <param name="tableName">The name of the table for which we wants to create the table schema.</param>
/// <returns>A table schema object that represents our knowledge of the table schema</returns>
private static TableSchema CreateTableSchema(SqlConnection conn, string tableName, string tschma)
{
TableSchema res = new TableSchema();
res.TableName = tableName;
res.TableSchemaName = tschma;
res.Columns = new List<ColumnSchema>();
SqlCommand cmd = new SqlCommand(@"SELECT COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE, " +
@" (columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity')) AS [IDENT], "+
@"CHARACTER_MAXIMUM_LENGTH AS CSIZE "+
"FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tableName + "' ORDER BY " +
"ORDINAL_POSITION ASC", conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
object tmp = reader["COLUMN_NAME"];
if (tmp is DBNull)
continue;
string colName = (string)reader["COLUMN_NAME"];
tmp = reader["COLUMN_DEFAULT"];
string colDefault;
if (tmp is DBNull)
colDefault = string.Empty;
else
colDefault = (string)tmp;
tmp = reader["IS_NULLABLE"];
bool isNullable = ((string)tmp == "YES");
string dataType = (string)reader["DATA_TYPE"];
bool isIdentity = false;
if (reader["IDENT"] != DBNull.Value)
isIdentity = ((int)reader["IDENT"]) == 1 ? true : false;
int length = reader["CSIZE"] != DBNull.Value ? Convert.ToInt32(reader["CSIZE"]) : 0;
ValidateDataType(dataType);
// Note that not all data type names need to be converted because
// SQLite establishes type affinity by searching certain strings
// in the type name. For example - everything containing the string
// 'int' in its type name will be assigned an INTEGER affinity
if (dataType == "timestamp")
dataType = "blob";
else if (dataType == "datetime" || dataType == "smalldatetime")
dataType = "datetime";
else if (dataType == "decimal")
dataType = "numeric";
else if (dataType == "money" || dataType == "smallmoney")
dataType = "numeric";
else if (dataType == "binary" || dataType == "varbinary" ||
dataType == "image")
dataType = "blob";
else if (dataType == "tinyint")
dataType = "smallint";
else if (dataType == "bigint")
dataType = "integer";
else if (dataType == "sql_variant")
dataType = "blob";
else if (dataType == "xml")
dataType = "varchar";
else if (dataType == "uniqueidentifier")
dataType = "guid";
else if (dataType == "ntext")
dataType = "text";
else if (dataType == "nchar")
dataType = "char";
if (dataType == "bit" || dataType == "int")
{
if (colDefault == "('False')")
colDefault = "(0)";
else if (colDefault == "('True')")
colDefault = "(1)";
}
colDefault = FixDefaultValueString(colDefault);
ColumnSchema col = new ColumnSchema();
col.ColumnName = colName;
col.ColumnType = dataType;
col.Length = length;
col.IsNullable = isNullable;
col.IsIdentity = isIdentity;
col.DefaultValue = AdjustDefaultValue(colDefault);
res.Columns.Add(col);
} // while
} // using
// Find PRIMARY KEY information
SqlCommand cmd2 = new SqlCommand(@"EXEC sp_pkeys '" + tableName + "'", conn);
using (SqlDataReader reader = cmd2.ExecuteReader())
{
res.PrimaryKey = new List<string>();
while (reader.Read())
{
string colName = (string)reader["COLUMN_NAME"];
res.PrimaryKey.Add(colName);
} // while
} // using
// Find COLLATE information for all columns in the table
SqlCommand cmd4 = new SqlCommand(
@"EXEC sp_tablecollations '" + tschma + "." + tableName + "'", conn);
using (SqlDataReader reader = cmd4.ExecuteReader())
{
while (reader.Read())
{
bool? isCaseSensitive = null;
string colName = (string)reader["name"];
if (reader["tds_collation"] != DBNull.Value)
{
byte[] mask = (byte[])reader["tds_collation"];
if ((mask[2] & 0x10) != 0)
isCaseSensitive = false;
else
isCaseSensitive = true;
} // if
if (isCaseSensitive.HasValue)
{
// Update the corresponding column schema.
foreach (ColumnSchema csc in res.Columns)
{
if (csc.ColumnName == colName)
{
csc.IsCaseSensitivite = isCaseSensitive;
break;
}
} // foreach
} // if
} // while
} // using
try
{
// Find index information
SqlCommand cmd3 = new SqlCommand(
@"exec sp_helpindex '" + tschma + "." + tableName + "'", conn);
using (SqlDataReader reader = cmd3.ExecuteReader())
{
res.Indexes = new List<IndexSchema>();
while (reader.Read())
{
string indexName = (string)reader["index_name"];
string desc = (string)reader["index_description"];
string keys = (string)reader["index_keys"];
// Don't add the index if it is actually a primary key index
if (desc.Contains("primary key"))
continue;
IndexSchema index = BuildIndexSchema(indexName, desc, keys);
res.Indexes.Add(index);
} // while
} // using
}
catch (Exception ex)
{
_log.Warn("failed to read index information for table [" + tableName + "]");
} // catch
return res;
}
/// <summary>
/// Used when creating the CREATE TABLE DDL. Creates a single row
/// for the specified column.
/// </summary>
/// <param name="col">The column schema</param>
/// <returns>A single column line to be inserted into the general CREATE TABLE DDL statement</returns>
private static string BuildColumnStatement(ColumnSchema col, TableSchema ts, ref bool pkey)
{
StringBuilder sb = new StringBuilder();
sb.Append("\t\""+col.ColumnName + "\"\t\t");
// Special treatment for IDENTITY columns
if (col.IsIdentity)
{
if (ts.PrimaryKey.Count == 1 && (col.ColumnType == "tinyint" || col.ColumnType == "int" || col.ColumnType == "smallint" ||
col.ColumnType == "bigint" || col.ColumnType == "integer"))
{
sb.Append("integer PRIMARY KEY AUTOINCREMENT");
pkey = true;
}
else
sb.Append("integer");
}
else
{
if (col.ColumnType == "int")
sb.Append("integer");
else
{
sb.Append(col.ColumnType);
}
if (col.Length > 0)
sb.Append("(" + col.Length + ")");
}
if (!col.IsNullable)
sb.Append(" NOT NULL");
if (col.IsCaseSensitivite.HasValue && !col.IsCaseSensitivite.Value)
sb.Append(" COLLATE NOCASE");
string defval = StripParens(col.DefaultValue);
defval = DiscardNational(defval);
_log.Debug("DEFAULT VALUE BEFORE [" + col.DefaultValue + "] AFTER [" + defval + "]");
if (defval != string.Empty && defval.ToUpper().Contains("GETDATE"))
{
_log.Debug("converted SQL Server GETDATE() to CURRENT_TIMESTAMP for column ["+col.ColumnName+"]");
sb.Append(" DEFAULT (CURRENT_TIMESTAMP)");
}
else if (defval != string.Empty && IsValidDefaultValue(defval))
sb.Append(" DEFAULT " + defval);
return sb.ToString();
}
/// <summary>
/// Used in order to adjust the value received from SQL Servr for the SQLite database.
/// </summary>
/// <param name="val">The value object</param>
/// <param name="columnSchema">The corresponding column schema</param>
/// <returns>SQLite adjusted value.</returns>
private static object CastValueForColumn(object val, ColumnSchema columnSchema)
{
if (val is DBNull)
return null;
DbType dt = GetDbTypeOfColumn(columnSchema);
switch (dt)
{
case DbType.Int32:
if (val is short)
return (int)(short)val;
if (val is byte)
return (int)(byte)val;
if (val is long)
return (int)(long)val;
if (val is decimal)
return (int)(decimal)val;
break;
case DbType.Int16:
if (val is int)
return (short)(int)val;
if (val is byte)
return (short)(byte)val;
if (val is long)
return (short)(long)val;
if (val is decimal)
return (short)(decimal)val;
break;
case DbType.Int64:
if (val is int)
return (long)(int)val;
if (val is short)
return (long)(short)val;
if (val is byte)
return (long)(byte)val;
if (val is decimal)
return (long)(decimal)val;
break;
case DbType.Single:
if (val is double)
return (float)(double)val;
if (val is decimal)
return (float)(decimal)val;
break;
case DbType.Double:
if (val is float)
return (double)(float)val;
if (val is double)
return (double)val;
if (val is decimal)
return (double)(decimal)val;
break;
case DbType.String:
if (val is Guid)
return ((Guid)val).ToString();
break;
case DbType.Guid:
if (val is string)
return ParseStringAsGuid((string)val);
if (val is byte[])
return ParseBlobAsGuid((byte[])val);
break;
case DbType.Binary:
case DbType.Boolean:
case DbType.DateTime:
break;
default:
_log.Error("argument exception - illegal database type");
throw new ArgumentException("Illegal database type [" + Enum.GetName(typeof(DbType), dt) + "]");
} // switch
return val;
}
/// <summary>
/// Matches SQL Server types to general DB types
/// </summary>
/// <param name="cs">The column schema to use for the match</param>
/// <returns>The matched DB type</returns>
private static DbType GetDbTypeOfColumn(ColumnSchema cs)
{
if (cs.ColumnType == "tinyint")
return DbType.Byte;
if (cs.ColumnType == "int")
return DbType.Int32;
if (cs.ColumnType == "smallint")
return DbType.Int16;
if (cs.ColumnType == "bigint")
return DbType.Int64;
if (cs.ColumnType == "bit")
return DbType.Boolean;
if (cs.ColumnType == "nvarchar" || cs.ColumnType == "varchar" ||
cs.ColumnType == "text" || cs.ColumnType == "ntext")
return DbType.String;
if (cs.ColumnType == "float")
return DbType.Double;
if (cs.ColumnType == "real")
return DbType.Single;
if (cs.ColumnType == "blob")
return DbType.Binary;
if (cs.ColumnType == "numeric")
return DbType.Double;
if (cs.ColumnType == "timestamp" || cs.ColumnType == "datetime")
return DbType.DateTime;
if (cs.ColumnType == "nchar" || cs.ColumnType == "char")
return DbType.String;
if (cs.ColumnType == "uniqueidentifier" || cs.ColumnType == "guid")
return DbType.Guid;
if (cs.ColumnType == "xml")
return DbType.String;
if (cs.ColumnType == "sql_variant")
return DbType.Object;
if (cs.ColumnType == "integer")
return DbType.Int64;
_log.Error("illegal db type found");
throw new ApplicationException("Illegal DB type found (" + cs.ColumnType + ")");
}
/// <summary>
/// Creates a TableSchema object using the specified SQL Server connection
/// and the name of the table for which we need to create the schema.
/// </summary>
/// <param name="conn">The SQL Server connection to use</param>
/// <param name="tableName">The name of the table for which we wants to create the table schema.</param>
/// <returns>A table schema object that represents our knowledge of the table schema</returns>
private static TableSchema CreateTableSchema(OracleConnection conn, string tableName, string tschma)
{
TableSchema res = new TableSchema();
res.TableName = tableName;
res.TableSchemaName = tschma;
res.Columns = new List<ColumnSchema>();
OracleCommand cmd = new OracleCommand(@"SELECT CNAME, COLTYPE, WIDTH, NULLS, DEFAULTVAL FROM COL WHERE TNAME = '" + tableName + "' ORDER BY COLNO", conn);
using (OracleDataReader reader = cmd.ExecuteReader())
{
_log.Debug("begin:" + tableName);
while (reader.Read())
{
object tmp = reader["CNAME"];
if (tmp is DBNull)
continue;
string colName = (string)reader["CNAME"];
tmp = reader["DEFAULTVAL"];
string colDefault;
if (tmp is DBNull)
colDefault = string.Empty;
else
colDefault = (string)tmp;
tmp = reader["NULLS"];
bool isNullable = ((string)tmp == "NULL");
string dataType = (string)reader["COLTYPE"];
bool isIdentity = false;
// if (reader["IDENT"] != DBNull.Value)
// isIdentity = ((int)reader["IDENT"]) == 1 ? true : false;
int length = reader["WIDTH"] != DBNull.Value ? Convert.ToInt32(reader["WIDTH"]) : 0;
//colDefault = FixDefaultValueString(colDefault);
ColumnSchema col = new ColumnSchema();
col.ColumnName = colName;
col.ColumnType = GetDbTypeByOracleType(dataType);
col.Length = length;
col.IsNullable = isNullable;
col.IsIdentity = isIdentity;
col.DefaultValue = AdjustDefaultValue(colDefault);
res.Columns.Add(col);
} // while
_log.Debug("end:" + tableName);
} // using
// Find PRIMARY KEY information
/* OracleCommand cmd2 = new OracleCommand(@"select cu.column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name ='" + tableName + "'", conn);
using (OracleDataReader reader = cmd2.ExecuteReader())
{
res.PrimaryKey = new List<string>();
while (reader.Read())
{
string colName = (string)reader["COLUMN_NAME"];
res.PrimaryKey.Add(colName);
} // while
} // using
*/
// Find COLLATE information for all columns in the table
/* OracleCommand cmd4 = new OracleCommand(
@"EXEC sp_tablecollations '" + tschma + "." + tableName + "'", conn);
using (OracleDataReader reader = cmd4.ExecuteReader())
{
while (reader.Read())
{
bool? isCaseSensitive = null;
string colName = (string)reader["name"];
if (reader["tds_collation"] != DBNull.Value)
{
byte[] mask = (byte[])reader["tds_collation"];
if ((mask[2] & 0x10) != 0)
isCaseSensitive = false;
else
isCaseSensitive = true;
} // if
if (isCaseSensitive.HasValue)
{
// Update the corresponding column schema.
foreach (ColumnSchema csc in res.Columns)
{
if (csc.ColumnName == colName)
{
csc.IsCaseSensitivite = isCaseSensitive;
break;
}
} // foreach
} // if
} // while
} // using
*/
/*
try
{
// Find index information
OracleCommand cmd3 = new OracleCommand(
@"exec sp_helpindex '" + tschma + "." + tableName + "'", conn);
using (OracleDataReader reader = cmd3.ExecuteReader())
{
res.Indexes = new List<IndexSchema>();
while (reader.Read())
{
string indexName = (string)reader["index_name"];
string desc = (string)reader["index_description"];
string keys = (string)reader["index_keys"];
// Don't add the index if it is actually a primary key index
if (desc.Contains("primary key"))
continue;
IndexSchema index = BuildIndexSchema(indexName, desc, keys);
res.Indexes.Add(index);
} // while
} // using
}
catch (Exception ex)
{
_log.Warn("failed to read index information for table [" + tableName + "]");
} // catch
*/
return res;
}
