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