/// <summary>
/// Used when creating the CREATE TABLE DDL. Creates a single row
/// for the specified column.
/// </summary>
/// <param name="columnSchema">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 columnSchema, TableSchema tableSchema, ref bool pkey)
{
var sb = new StringBuilder();
sb.Append("\t[" + columnSchema.ColumnName + "]\t");
// Special treatment for IDENTITY columns
if (columnSchema.IsIdentity)
{
if (tableSchema.PrimaryKey.Count == 1 && (columnSchema.ColumnType == "tinyint" || columnSchema.ColumnType == "int" || columnSchema.ColumnType == "smallint" || columnSchema.ColumnType == "bigint" || columnSchema.ColumnType == "integer"))
{
sb.Append("integer PRIMARY KEY AUTOINCREMENT");
pkey = true;
}
else
{
sb.Append("integer");
}
}
else
{
sb.Append(columnSchema.ColumnType == "int" ? "integer" : columnSchema.ColumnType);
if (columnSchema.Length > 0)
{
sb.Append("(" + columnSchema.Length + ")");
}
}
if (!columnSchema.IsNullable)
{
sb.Append(" NOT NULL");
}
if (columnSchema.IsCaseSensitive.HasValue && !columnSchema.IsCaseSensitive.Value)
{
sb.Append(" COLLATE NOCASE");
}
string defval = StripParens(columnSchema.DefaultValue);
defval = DiscardNational(defval);
_log.Debug("DEFAULT VALUE BEFORE [" + columnSchema.DefaultValue + "] AFTER [" + defval + "]");
if (defval != string.Empty && defval.ToUpper().Contains("GETDATE"))
{
_log.Debug("converted SQL Server GETDATE() to CURRENT_TIMESTAMP for column [" + columnSchema.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 Server 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) + "]");
}
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)
{
Dictionary<String, DbType> typeMapping = new Dictionary<String, DbType>
{
{ "tinyint", DbType.Byte },
{ "int", DbType.Int32 },
{ "smallint", DbType.Int16 },
{ "bigint", DbType.Int64 },
{ "bit", DbType.Boolean },
{ "nvarchar", DbType.String },
{ "varchar", DbType.String },
{ "text", DbType.String },
{ "ntext", DbType.String },
{ "float", DbType.Double },
{ "real", DbType.Single },
{ "blob", DbType.Binary },
{ "numeric", DbType.Double },
{ "timestamp", DbType.DateTime },
{ "datetime", DbType.DateTime },
{ "datetime2", DbType.DateTime },
{ "date", DbType.DateTime },
{ "time", DbType.DateTime },
{ "nchar", DbType.String },
{ "char", DbType.String },
{ "uniqueidentifier", DbType.Guid },
{ "guid", DbType.Guid },
{ "xml", DbType.String },
{ "sql_variant", DbType.Object },
{ "integer", DbType.Int64 },
};
var type = cs.ColumnType;
if (typeMapping.ContainsKey(type))
{
return typeMapping[type];
}
_log.Error("Illegal database type found");
throw new ApplicationException("Illegal database 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="tableName">The name of the table for which we want to create a table schema.</param>
/// <param name="tableSchemaName">The name of the schema containing the table for which we want to create a table schema.</param>
/// <returns>A table schema object that represents our knowledge of the table schema</returns>
private TableSchema CreateTableSchema(string tableName, string tableSchemaName)
{
TableSchema res = new TableSchema();
res.TableName = tableName;
res.TableSchemaName = tableSchemaName;
res.Columns = new List<ColumnSchema>();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
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);
}
int length = reader["CSIZE"] != DBNull.Value ? Convert.ToInt32(reader["CSIZE"]) : 0;
SqlServerToSQLite.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 == "date" || dataType == "datetime2" || dataType == "time")
{
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 = SqlServerToSQLite.FixDefaultValueString(colDefault);
ColumnSchema col = new ColumnSchema();
col.ColumnName = colName;
col.ColumnType = dataType;
col.Length = length;
col.IsNullable = isNullable;
col.IsIdentity = isIdentity;
col.DefaultValue = SqlServerToSQLite.AdjustDefaultValue(colDefault);
res.Columns.Add(col);
}
}
// 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);
}
}
// Find COLLATE information for all columns in the table
SqlCommand cmd4 = new SqlCommand(@"EXEC sp_tablecollations '" + tableSchemaName + "." + 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"];
isCaseSensitive = (mask[2] & 0x10) == 0;
}
if (isCaseSensitive.HasValue)
{
// Update the corresponding column schema.
foreach (ColumnSchema csc in res.Columns)
{
if (csc.ColumnName == colName)
{
csc.IsCaseSensitive = isCaseSensitive;
break;
}
}
}
}
}
try
{
// Find index information
SqlCommand cmd3 = new SqlCommand(@"exec sp_helpindex '" + tableSchemaName + "." + 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);
}
}
}
catch (Exception ex)
{
_log.Error("Error in \"CreateTableSchema\"", ex);
_log.Warn("failed to read index information for table [" + tableName + "]");
}
}
return res;
}
