/// <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; }