protected override string MakeParameterName(TableColumnSchema column) { // Бедный несчастный Jet умирает на Timestamp'ах с долями секунд. // Но при преобразовании в строку, дата обрезается до секунд, // что и требовалось. // if (column.Type == ColumnType.Timestamp) return @"CDate(" + base.MakeParameterName(column) + @")"; return base.MakeParameterName(column); }
public override IDbDataParameter ConvertToDbParameter(TableColumnSchema column, IDbDataParameter parameter) { if (column.Type == ColumnType.Timestamp) { // Бедный несчастный Jet умирает на Timestamp'ах с долями секунд. // Но при преобразовании в строку, дата обрезается до секунд, // что и требовалось. // parameter.DbType = DbType.String; parameter.ParameterName = base.MakeParameterName(column); return parameter; } return base.ConvertToDbParameter(column, parameter); }
private static List<TableSchema> GetTables(FbConnection con) { string[] restrict3 = {null, null, null}; string[] restrict4 = {null, null, null, null}; var aStore = new List<TableSchema>(); restrict4[0] = null; restrict4[1] = null; restrict4[2] = null; restrict4[3] = "TABLE"; var dtTables = con.GetSchema("Tables", restrict4); for (var i = 0; i < dtTables.Rows.Count; i++) { var tRow = dtTables.Rows[i]; var eTable = new TableSchema {Name = tRow["TABLE_NAME"].ToString()}; // Columns restrict3[0] = null; restrict3[1] = null; restrict3[2] = eTable.Name; var dtShema = con.GetSchema("Columns", restrict3); if (dtShema.Rows.Count > 0) eTable.Columns = new TableColumnSchema[dtShema.Rows.Count]; for (var j = 0; j < dtShema.Rows.Count; j++) { var cRow = dtShema.Rows[j]; var eColumn = new TableColumnSchema { Name = cRow["COLUMN_NAME"].ToString(), Size = Convert.ToInt32(cRow["COLUMN_SIZE"], CultureInfo.InvariantCulture), Type = TypeFbToDbsm(cRow["COLUMN_DATA_TYPE"].ToString()), Nullable = Convert.ToBoolean(cRow["IS_NULLABLE"], CultureInfo.InvariantCulture) }; eColumn.DefaultValue = HelpDbscColumnDefault(con, eColumn.Name, eTable.Name); eColumn.DefaultValue = string.IsNullOrEmpty(eColumn.DefaultValue) ? null : eColumn.DefaultValue; eColumn.DecimalPrecision = cRow["NUMERIC_PRECISION"] == DBNull.Value ? 0 : Convert.ToInt32(cRow["NUMERIC_PRECISION"], CultureInfo.InvariantCulture); eColumn.DecimalScale = Convert.ToInt32(cRow["NUMERIC_SCALE"], CultureInfo.InvariantCulture); eTable.Columns[j] = eColumn; } aStore.Add(eTable); } return aStore; }
protected virtual string MakeParameterName(TableColumnSchema column) { return(@"@" + column.Name); }
protected override string MakeDdlColumnDrop(TableColumnSchema column, TableSchema table) { return(string.Format(@"ALTER TABLE {0} DROP {1}", MakeDdlElementName(table.Name), MakeDdlElementName(column.Name))); }
public static string TypeDbsmToSql(TableColumnSchema eColumn) { switch (eColumn.Type) { case ColumnType.Boolean: return("BIT"); case ColumnType.BigInt: return("BIGINT"); case ColumnType.Binary: return("BINARY"); case ColumnType.Character: return("CHAR(" + eColumn.Size + ")"); case ColumnType.CharacterVaring: return("VARCHAR(" + eColumn.Size + ")"); case ColumnType.Cursor: return("CURSOR"); case ColumnType.Timestamp: return("DATETIME"); case ColumnType.Decimal: return("DECIMAL(" + eColumn.DecimalPrecision + ", " + eColumn.DecimalScale + ")"); case ColumnType.Integer: return("INT"); case ColumnType.BlobSubtypeImage: return("IMAGE"); case ColumnType.Float: return("FLOAT(" + eColumn.DecimalPrecision + ")"); case ColumnType.Money: return("MONEY"); case ColumnType.NCharacter: return("NCHAR(" + eColumn.Size + ")"); case ColumnType.NCharacterVaring: return("NVARCHAR(" + eColumn.Size + ")"); case ColumnType.Numeric: return("NUMERIC(" + eColumn.DecimalPrecision + ", " + eColumn.DecimalScale + ")"); case ColumnType.BlobSubtypeNText: return("NTEXT"); case ColumnType.Real: return("REAL"); case ColumnType.SmallMoney: return("SMALLMONEY"); case ColumnType.SmallDateTime: return("SMALLDATETIME"); case ColumnType.SmallInt: return("SMALLINT"); case ColumnType.SqlVariant: return("SQL_VARIANT"); case ColumnType.Table: return("TABLE"); case ColumnType.BlobSubtypeText: return("TEXT"); case ColumnType.MsTimestamp: return("TIMESTAMP"); case ColumnType.TinyInt: return("TINYINT"); case ColumnType.Guid: return("UNIQUEIDENTIFIER"); case ColumnType.BinaryVaring: return("VARBINARY"); default: throw new ArgumentException("Unsupported data type for " + MssqlDriver.DriverName); } }
/// <summary> /// IValueConverter.Convert DBType to Images /// </summary> /// <param name="value"></param> /// <param name="targetType"></param> /// <param name="parameter"></param> /// <param name="culture"></param> /// <returns></returns> public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture) { if (value == null) { return(new BitmapImage(new Uri("pack://application:,,,/Resources/Images/16x16/folders/folder_closed.png"))); } Type container = value.GetType(); if (container == typeof(DataSource)) { return(new BitmapImage(new Uri("pack://application:,,,/Resources/Images/16x16/database/db_server.png"))); } if (container == typeof(DatabaseSchema)) { return(new BitmapImage(new Uri("pack://application:,,,/Resources/Images/16x16/database/db.png"))); } if (container == typeof(TableSchema)) { return(new BitmapImage(new Uri("pack://application:,,,/Resources/Images/16x16/database/table.png"))); } if (container == typeof(ViewSchema)) { return(new BitmapImage(new Uri("pack://application:,,,/Resources/Images/16x16/database/view.png"))); } if (container == typeof(TableColumnSchema)) { TableColumnSchema info = value as TableColumnSchema; if (info.IsPrimaryKeyMember) { return(new BitmapImage(new Uri("pack://application:,,,/Resources/Images/16x16/database/key.png"))); } else if (info.IsForeignKeyMember) { return(new BitmapImage(new Uri("pack://application:,,,/Resources/Images/16x16/database/foreign_key.png"))); } else { return(new BitmapImage(new Uri("pack://application:,,,/Resources/Images/16x16/database/column.png"))); } } if (container == typeof(PrimaryKeySchema)) { return(new BitmapImage(new Uri("pack://application:,,,/Resources/Images/16x16/database/key.png"))); } if (container == typeof(TableKeySchema)) { return(new BitmapImage(new Uri("pack://application:,,,/Resources/Images/16x16/database/foreign_key.png"))); } if (container == typeof(ViewColumnSchema)) { return(new BitmapImage(new Uri("pack://application:,,,/Resources/Images/16x16/database/column.png"))); } if (container == typeof(IndexSchema)) { return(new BitmapImage(new Uri("pack://application:,,,/Resources/Images/16x16/database/index.png"))); } if (container == typeof(CommandSchema)) { return(new BitmapImage(new Uri("pack://application:,,,/Resources/Images/16x16/database/command.png"))); } if (container == typeof(ParameterSchema)) { return(new BitmapImage(new Uri("pack://application:,,,/Resources/Images/16x16/database/command_param.png"))); } return(new BitmapImage(new Uri("pack://application:,,,/Resources/Images/16x16/folders/folder_closed.png"))); }
protected override string ParseColumn(TableColumnSchema column) { return string.Format(@"{0} {1} {2} {3} {4}", MakeDdlElementName(column.Name), FBSchemaLoader.TypeDbsmToFb(column), string.Empty, // column.computedBy == null ? string.Empty : ("COMPUTED BY " + column.computedBy), column.DefaultValue.IsNullOrEmpty() ? string.Empty : @"DEFAULT {0}".FormatStr(column.DefaultValue), column.Nullable ? string.Empty : @" NOT NULL"); }
private static List <TableSchema> GetTables(SQLiteConnection con) { string[] restrict3 = { null, null, null }; string[] restrict4 = { null, null, null, "TABLE" }; var aStore = new List <TableSchema>(); // Текущая версия SQLite.Net (1.0.52.0) не позволяет узнать, // какие колонки являются autoincrement. // Вытаскиваем эту информацию вручную. // Для этого сначала проверяем наличие системной таблицы // sqlite_sequence (её может вообще не быть) и считываем // из неё имена всех autoincrement колонок. // var tablesWithAutoIncrementPKs = new List <string>(); restrict3[2] = "sqlite_sequence"; if (con.GetSchema("Columns", restrict3).Rows.Count > 0) { using (var cmd = con.CreateCommand()) { cmd.CommandTimeout = 0; cmd.CommandText = "SELECT name FROM sqlite_sequence"; using (IDataReader r = cmd.ExecuteReader()) while (r.Read()) { tablesWithAutoIncrementPKs.Add(r.GetString(0)); } } } var dtTables = con.GetSchema("Tables", restrict4); for (var i = 0; i < dtTables.Rows.Count; i++) { var tRow = dtTables.Rows[i]; var eTable = new TableSchema { Name = tRow["TABLE_NAME"].ToString() }; // Columns // restrict3[2] = eTable.Name; var dtShema = con.GetSchema("Columns", restrict3); if (dtShema.Rows.Count > 0) { eTable.Columns = new TableColumnSchema[dtShema.Rows.Count]; } for (var j = 0; j < dtShema.Rows.Count; j++) { var eColumn = new TableColumnSchema(); var cRow = dtShema.Rows[j]; eColumn.Name = cRow["COLUMN_NAME"].ToString(); eColumn.Size = Convert.ToInt32(cRow["CHARACTER_MAXIMUM_LENGTH"], CultureInfo.InvariantCulture); eColumn.Type = TypeSqliteToDbsm(cRow["DATA_TYPE"].ToString()); eColumn.Nullable = Convert.ToBoolean(cRow["IS_NULLABLE"], CultureInfo.InvariantCulture); eColumn.AutoIncrement = Convert.ToBoolean(cRow["PRIMARY_KEY"], CultureInfo.InvariantCulture) && tablesWithAutoIncrementPKs.Contains(eTable.Name); if (eColumn.AutoIncrement) { // В SQLite нет тонкой настройки автоинкремента // eColumn.Seed = 1; eColumn.Increment = 1; } var hasDefault = Convert.ToBoolean(cRow["COLUMN_HASDEFAULT"], CultureInfo.InvariantCulture); eColumn.DefaultValue = hasDefault ? cRow["COLUMN_DEFAULT"].ToString() : null; eColumn.DecimalPrecision = cRow["NUMERIC_PRECISION"] == DBNull.Value ? 0 : Convert.ToInt32(cRow["NUMERIC_PRECISION"], CultureInfo.InvariantCulture); eColumn.DecimalScale = cRow["NUMERIC_SCALE"] == DBNull.Value ? 0 : Convert.ToInt32(cRow["NUMERIC_SCALE"], CultureInfo.InvariantCulture); eTable.Columns[j] = eColumn; } aStore.Add(eTable); } return(aStore); }
/// <summary> /// Преобразование общего типа к firebird type, только однозначные отношения!!! /// </summary> /// <param name="column"></param> /// <returns></returns> public static string TypeDbsmToFb(TableColumnSchema column) { switch (column.Type) { case ColumnType.Array: return("ARRAY"); case ColumnType.BinaryLargeObject: return("BLOB"); case ColumnType.BlobSubtype1: return("BLOB SUB_TYPE 1"); case ColumnType.Character: return("CHAR(" + column.Size + ")"); case ColumnType.CharacterVaring: return("VARCHAR(" + column.Size + ")"); case ColumnType.NCharacterVaring: return("VARCHAR({0}) CHARACTER SET UNICODE_FSS".FormatStr(column.Size)); case ColumnType.SmallInt: case ColumnType.TinyInt: return("SMALLINT"); case ColumnType.Integer: return("INTEGER"); case ColumnType.Float: return("FLOAT"); case ColumnType.DoublePrecision: return("DOUBLE PRECISION"); case ColumnType.BigInt: return("BIGINT"); case ColumnType.Numeric: return("NUMERIC(" + column.DecimalPrecision + ", " + column.DecimalScale + ")"); case ColumnType.Decimal: return("DECIMAL(" + column.DecimalPrecision + ", " + column.DecimalScale + ")"); case ColumnType.Date: return("DATE"); case ColumnType.Time: return("TIME"); case ColumnType.Timestamp: return("TIMESTAMP"); case ColumnType.BlobSubtypeText: case ColumnType.BlobSubtypeNText: return("BLOB SUB_TYPE TEXT"); case ColumnType.Boolean: return("SMALLINT"); default: throw new ArgumentException("Unsupported data type for " + FBDriver.DriverName); } }
public virtual string MakeDdlColumnAlter(TableColumnSchema mColumn, TableColumnSchema eColumn, TableSchema table) { return(string.Format(@"ALTER TABLE {0} ALTER COLUMN {1}", MakeDdlElementName(table.Name), ParseColumnAlter(mColumn, eColumn))); }
public override string MakeDdlColumnRename(TableColumnSchema column, string newName, TableSchema table) { return(string.Format("sp_rename '{0}.{1}', '{2}', 'COLUMN'", MakeDdlElementName(table.Name), MakeDdlElementName(column.Name), newName)); }
private static List<TableColumnSchema> GetColumns(Table xTable) { var columns = new List<TableColumnSchema>(); for (var j = 0; j < xTable.Columns.Count; j++) { var xColumn = xTable.Columns[j]; var column = new TableColumnSchema { Name = xColumn.Name, Type = TypeJetToDbsm(xColumn.Type), Size = xColumn.DefinedSize, DecimalPrecision = xColumn.Precision, DecimalScale = xColumn.NumericScale, AutoIncrement = ((bool)xColumn.Properties["Autoincrement"].Value) }; //Hashtable hprops = new Hashtable(); //for (int x = 0; x < column.Properties.Count; x++) // hprops.Add(column.Properties[x].Name, column.Properties[x].Value); if (column.AutoIncrement) { column.Seed = 1; // (int)xTable.Columns[j].Properties["Seed"].Value; column.Increment = (int)xColumn.Properties["Increment"].Value; } //eColumn.FixedLength = (bool)xTable.Columns[j].Properties["Fixed Length"].Value; column.Nullable = (bool)xColumn.Properties["Nullable"].Value; if (xColumn.Properties["Default"].Value != null) column.DefaultValue = xColumn.Properties["Default"].Value.ToString(); if (column.Type == ColumnType.Boolean && column.DefaultValue == null) column.DefaultValue = "0"; columns.Add(column); } return columns; }
protected override string ParseColumnAlter(TableColumnSchema mColumn, TableColumnSchema eColumn) { return ParseColumn(mColumn); }
protected override string ParseColumn(TableColumnSchema ecolumn) { var stat = new StringBuilder(); stat.Append(@"[" + ecolumn.Name + @"]"); if (ecolumn.AutoIncrement) stat.Append(@" IDENTITY(" + ecolumn.Seed.ToString(CultureInfo.CurrentCulture) + @"," + ecolumn.Increment.ToString(CultureInfo.InvariantCulture) + @")"); else { stat.Append(@" " + JetSchemaLoader.TypeDbsmToJet(ecolumn)); if (!String.IsNullOrEmpty(ecolumn.DefaultValue)) stat.Append(@" DEFAULT " + ecolumn.DefaultValue); } if (!ecolumn.Nullable) stat.Append(@" NOT NULL"); return stat.ToString(); }
protected override string MakeDdlColumnAlter(TableColumnSchema mColumn, TableColumnSchema eColumn, TableSchema table) { return string.Format(@"ALTER TABLE {0} ALTER COLUMN {1}", MakeDdlElementName(table.Name), ParseColumnAlter(mColumn, eColumn)); }
public static string TypeDbsmToJet(TableColumnSchema dbc) { switch (dbc.Type) { case ColumnType.BinaryVaring: return "BINARY(" + dbc.Size + ")"; case ColumnType.Boolean: return "BIT"; case ColumnType.TinyInt: return "BYTE"; case ColumnType.NCharacter: return "CHAR(" + dbc.Size + ")"; case ColumnType.NCharacterVaring: return "TEXT(" + dbc.Size + ")"; case ColumnType.Timestamp: return "DATETIME"; case ColumnType.DoublePrecision: return "DOUBLE"; case ColumnType.Real: return "REAL"; case ColumnType.Guid: return "UNIQUEIDENTIFIER"; case ColumnType.BlobSubtypeImage: return "IMAGE"; case ColumnType.Integer: return "INTEGER"; case ColumnType.BlobSubtype1: case ColumnType.BlobSubtypeNText: return "MEMO"; case ColumnType.Money: return "MONEY"; case ColumnType.Decimal: return "DECIMAL(" + dbc.DecimalPrecision + ", " + dbc.DecimalScale + ")"; case ColumnType.SmallInt: return "SMALLINT"; default: throw new ArgumentException("Unsupported data type for " + JetDriver.DriverName); } }
/// <summary> /// Преобразование общего типа к firebird type, только однозначные отношения!!! /// </summary> /// <param name="column"></param> /// <returns></returns> public static string TypeDbsmToFb(TableColumnSchema column) { switch (column.Type) { case ColumnType.Array: return "ARRAY"; case ColumnType.BinaryLargeObject: return "BLOB"; case ColumnType.BlobSubtype1: return "BLOB SUB_TYPE 1"; case ColumnType.Character: return "CHAR(" + column.Size + ")"; case ColumnType.CharacterVaring: return "VARCHAR(" + column.Size + ")"; case ColumnType.NCharacterVaring: return $"VARCHAR({column.Size}) CHARACTER SET UNICODE_FSS"; case ColumnType.SmallInt: case ColumnType.TinyInt: return "SMALLINT"; case ColumnType.Integer: return "INTEGER"; case ColumnType.Float: return "FLOAT"; case ColumnType.DoublePrecision: return "DOUBLE PRECISION"; case ColumnType.BigInt: return "BIGINT"; case ColumnType.Numeric: return "NUMERIC(" + column.DecimalPrecision + ", " + column.DecimalScale + ")"; case ColumnType.Decimal: return "DECIMAL(" + column.DecimalPrecision + ", " + column.DecimalScale + ")"; case ColumnType.Date: return "DATE"; case ColumnType.Time: return "TIME"; case ColumnType.Timestamp: return "TIMESTAMP"; case ColumnType.BlobSubtypeText: case ColumnType.BlobSubtypeNText: return "BLOB SUB_TYPE TEXT"; case ColumnType.Boolean: return "SMALLINT"; default: throw new ArgumentException("Unsupported data type for " + FBDriver.DriverName); } }
public override string MakeDdlColumnRename(TableColumnSchema column, string newName, TableSchema table) { return string.Format("sp_rename '{0}.{1}', '{2}', 'COLUMN'", MakeDdlElementName(table.Name), MakeDdlElementName(column.Name), newName); }
protected override string ParseColumn(TableColumnSchema column) { return $@"{MakeDdlElementName(column.Name)} {FBSchemaLoader.TypeDbsmToFb(column)} {string.Empty} {(column.DefaultValue .IsNullOrEmpty() ? string.Empty : $"DEFAULT {column.DefaultValue}")} {(column.Nullable ? string.Empty : @" NOT NULL")}";
protected override string ParseColumnAlter(TableColumnSchema mColumn, TableColumnSchema eColumn) { return string.Format(@" [{0}] {1}{2} ", mColumn.Name, MssqlSchemaLoader.TypeDbsmToSql(mColumn), mColumn.Nullable ? string.Empty : @" NOT NULL"); }
public static string TypeDbsmToSqlite(TableColumnSchema eColumn) { switch (eColumn.Type) { case ColumnType.Boolean: return("BIT"); case ColumnType.BigInt: return("BIGINT"); case ColumnType.Binary: return("BINARY"); case ColumnType.Character: return("CHAR(" + eColumn.Size + ")"); case ColumnType.CharacterVaring: return("VARCHAR(" + eColumn.Size + ")"); case ColumnType.Timestamp: return("TIMESTAMP"); case ColumnType.Decimal: return("DECIMAL(" + eColumn.DecimalPrecision + ", " + eColumn.DecimalScale + ")"); case ColumnType.Integer: // There should be "INT" but column type Integer is widelly used as an autoincrement // type which must be Int64 for SQLite. So there is an implicit 32-64 bit conversion. // return("INTEGER"); case ColumnType.Float: return("REAL(" + eColumn.DecimalPrecision + ")"); case ColumnType.DoublePrecision: return("DOUBLE(" + eColumn.DecimalPrecision + ")"); case ColumnType.Money: return("MONEY"); case ColumnType.NCharacter: return("NCHAR(" + eColumn.Size + ")"); case ColumnType.NCharacterVaring: return("NVARCHAR(" + eColumn.Size + ")"); case ColumnType.Real: return("REAL"); case ColumnType.SmallInt: return("SMALLINT"); case ColumnType.TinyInt: return("TINYINT"); case ColumnType.Guid: return("UNIQUEIDENTIFIER"); case ColumnType.BinaryVaring: return("VARBINARY"); case ColumnType.BlobSubtypeNText: return("NTEXT"); case ColumnType.BlobSubtypeImage: return("IMAGE"); default: throw new NotSupportedException("Data type '" + eColumn.Type + "' is not supported by " + SqliteDriver.DriverName); } }
protected override string ParseColumn(TableColumnSchema column) { var stat = new StringBuilder(); stat.Append(MakeDdlElementName(column.Name) + MssqlSchemaLoader.TypeDbsmToSql(column)); if (column.AutoIncrement) stat.AppendFormat(@" IDENTITY ({0},{1})", column.Seed, column.Increment); else if (!string.IsNullOrEmpty(column.DefaultValue)) stat.Append(@" DEFAULT " + column.DefaultValue); if (!column.Nullable) stat.Append(@" NOT NULL"); return stat.ToString(); }
/// <summary> /// Return all columns for a given table /// </summary> /// <param name="connectionString"></param> /// <param name="table"></param> /// <returns></returns> public List <TableColumnSchema> GetTableColumns(string connectionString, TableSchema table) { DataTable table2 = new SchemaHelper().GetSchemaFromReader(connectionString, table.Name); List <TableColumnSchema> schemaArray = new List <TableColumnSchema>(table2.Rows.Count); foreach (DataRow columnRow in table2.Rows) { string name = SafeGetString(columnRow, "ColumnName"); string nativeType = SafeGetString(columnRow, "DataTypeName"); DbType dataType = _DataTypes.First(p => p.StringType == SafeGetString(columnRow, "DataTypeName")).eDbType; int size = SafeGetInt(columnRow, "ColumnSize"); byte precision = (byte)SafeGetInt(columnRow, "NumericPrecision"); int scale = SafeGetInt(columnRow, "NumericScale"); bool allowDBNull = SafeGetBool(columnRow, "AllowDBNull"); List <ExtendedProperty> list = new List <ExtendedProperty>(); TableColumnSchema tcs = new TableColumnSchema(table, name, dataType, nativeType, size, precision, scale, allowDBNull, list) { IsIdentity = SafeGetBool(columnRow, "IsIdentity"), IsKey = SafeGetBool(columnRow, "IsKey"), IsReadOnly = SafeGetBool(columnRow, "IsReadOnly"), IsAutoIncrement = SafeGetBool(columnRow, "IsAutoIncrement") }; schemaArray.Add(tcs); } //DataTable table2 = new SchemaHelper().GetTableColumns(connectionString, table.Database.Name, table.Owner, table.Name, null ); //List<TableColumnSchema> schemaArray = new List<TableColumnSchema>(table2.Rows.Count); //for (int i = 0; i < table2.Rows.Count; i++) //{ // string name = (string)table2.Rows[i]["COLUMN_NAME"]; // string nativeType = (string)table2.Rows[i]["DATA_TYPE"]; // DbType dataType = _DataTypes.First(p => p.StringType == ((string)table2.Rows[i]["DATA_TYPE"])).eDbType; // int size = table2.Rows[i].IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : ((int)table2.Rows[i]["CHARACTER_MAXIMUM_LENGTH"]); // byte precision = table2.Rows[i].IsNull("NUMERIC_PRECISION") ? ((byte)0) : ((byte)table2.Rows[i]["NUMERIC_PRECISION"]); // int scale = table2.Rows[i].IsNull("NUMERIC_SCALE") ? 0 : ((int)table2.Rows[i]["NUMERIC_SCALE"]); // bool allowDBNull = (bool)(((string)table2.Rows[i]["IS_NULLABLE"]) == "YES" ? true : false); // //bool isIdentity = ((int)table2.Rows[i][6]) == 1; // //bool isComputed = ((int)table2.Rows[i][7]) == 1; // //bool flag4 = ((int)table2.Rows[i][10]) == 1; // //bool flag5 = table2.Rows[i].IsNull(11) || (((int)table2.Rows[i][11]) == 1); // //int num4 = table2.Rows[i].IsNull(12) ? 0 : Convert.ToInt32(table2.Rows[i][12]); // //int num5 = table2.Rows[i].IsNull(13) ? 0 : Convert.ToInt32(table2.Rows[i][13]); // //string str4 = table2.Rows[i].IsNull(7) ? c.a("") : ((string)table2.Rows[i][7]); // //Select // //COLUMNPROPERTY(OBJECT_ID('Table1'), COLUMN_NAME, 'IsIdentity') as IS_IDENTITY, // //COLUMNPROPERTY(OBJECT_ID('Table1'), COLUMN_NAME, 'IsComputed') as IS_COMPUTED // //from INFORMATION_SCHEMA.COLUMNS // //Where COLUMN_NAME = 'money' // List<ExtendedProperty> list = new List<ExtendedProperty>(); // //list.Add(new ExtendedProperty("IsIdentity", isIdentity, DbType.Boolean)); // //list.Add(new ExtendedProperty("isComputed", isComputed, DbType.Boolean)); // //list.Add(new ExtendedProperty(c.a("\x0019\t\x0005\x0013)\x001957*/.?>"), flag4, DbType.Boolean)); // //list.Add(new ExtendedProperty(c.a("\x0019\t\x0005\x0013)\x001e?.?(7343).39"), flag5, DbType.Boolean)); // //list.Add(new ExtendedProperty(c.a("\x0019\t\x0005\x0013>?4.3.#\t??>"), num4, DbType.Int32)); // //list.Add(new ExtendedProperty(c.a("\x0019\t\x0005\x0013>?4.3.#\x001349(?7?4."), num5, DbType.Int32)); // //list.Add(new ExtendedProperty(c.a("\x0019\t\x0005\x001e?<;/6."), str4, DbType.String)); // schemaArray.Add( new TableColumnSchema(table, name, dataType, nativeType, size, precision, scale, allowDBNull, list) ); //} return(schemaArray); }
protected override string ParseColumnAlter(TableColumnSchema mColumn, TableColumnSchema eColumn) { return $@" [{mColumn.Name}] {MssqlSchemaLoader.TypeDbsmToSql(mColumn)}{(mColumn.Nullable ? string.Empty : @" NOT NULL")} "; }
protected override string MakeDdlColumnDrop(TableColumnSchema column, TableSchema table) { return string.Format(@"ALTER TABLE {0} DROP {1}", MakeDdlElementName(table.Name), MakeDdlElementName(column.Name)); }
private static TableSchema GetTable(SqlConnection con, DataRow tRow) { var eTable = new TableSchema {Name = tRow["TABLE_NAME"].ToString()}; // Columns string[] restrict3 = { null, null, null }; restrict3[0] = null; restrict3[1] = null; restrict3[2] = eTable.Name; var dtShema = SqlSchemaFactory.GetSchema(con, "Columns", restrict3); if (dtShema.Rows.Count > 0) { eTable.Columns = new TableColumnSchema[dtShema.Rows.Count]; for (var j = 0; j < dtShema.Rows.Count; j++) { var cRow = dtShema.Rows[j]; var eColumn = new TableColumnSchema { Name = cRow["COLUMN_NAME"].ToString(), Type = ColumnSchemaToSqlDataType(cRow), Nullable = Convert.ToBoolean(cRow["IS_NULLABLE"], CultureInfo.InvariantCulture), DefaultValue = cRow["COLUMN_DEFAULT"].ToString(), Increment = Convert.ToInt32(cRow["IDENTITY_INCREMENT"], CultureInfo.InvariantCulture), Seed = Convert.ToInt32(cRow["IDENTITY_SEED"], CultureInfo.InvariantCulture), AutoIncrement = Convert.ToBoolean(cRow["IS_IDENTITY"], CultureInfo.InvariantCulture), }; eColumn.DefaultValue = string.IsNullOrEmpty(eColumn.DefaultValue) ? null : UnBracket.ParseUnBracket(eColumn.DefaultValue); eTable.Columns[j] = eColumn; } } return eTable; }
protected override string ParseColumnAlter(TableColumnSchema mColumn, TableColumnSchema eColumn) { return string.Format(@"{0} TYPE {1}", MakeDdlElementName(eColumn.Name), FBSchemaLoader.TypeDbsmToFb(mColumn)); }
public static string TypeDbsmToSql(TableColumnSchema eColumn) { return new SqlTypeBuilder().BuildType(eColumn.Type); }
protected override string ParseColumnAlter(TableColumnSchema mColumn, TableColumnSchema eColumn) { return(string.Format(@"{0} TYPE {1}", MakeDdlElementName(eColumn.Name), FBSchemaLoader.TypeDbsmToFb(mColumn))); }
private static List<TableSchema> GetTables(SQLiteConnection con) { string[] restrict3 = {null, null, null}; string[] restrict4 = {null, null, null, "TABLE"}; var aStore = new List<TableSchema>(); // Текущая версия SQLite.Net (1.0.52.0) не позволяет узнать, // какие колонки являются autoincrement. // Вытаскиваем эту информацию вручную. // Для этого сначала проверяем наличие системной таблицы // sqlite_sequence (её может вообще не быть) и считываем // из неё имена всех autoincrement колонок. // var tablesWithAutoIncrementPKs = new List<string>(); restrict3[2] = "sqlite_sequence"; if (con.GetSchema("Columns", restrict3).Rows.Count > 0) using (var cmd = con.CreateCommand()) { cmd.CommandTimeout = 0; cmd.CommandText = "SELECT name FROM sqlite_sequence"; using (IDataReader r = cmd.ExecuteReader()) while (r.Read()) tablesWithAutoIncrementPKs.Add(r.GetString(0)); } var dtTables = con.GetSchema("Tables", restrict4); for (var i = 0; i < dtTables.Rows.Count; i++) { var tRow = dtTables.Rows[i]; var eTable = new TableSchema {Name = tRow["TABLE_NAME"].ToString()}; // Columns // restrict3[2] = eTable.Name; var dtShema = con.GetSchema("Columns", restrict3); if (dtShema.Rows.Count > 0) eTable.Columns = new TableColumnSchema[dtShema.Rows.Count]; for (var j = 0; j < dtShema.Rows.Count; j++) { var eColumn = new TableColumnSchema(); var cRow = dtShema.Rows[j]; eColumn.Name = cRow["COLUMN_NAME"].ToString(); eColumn.Size = Convert.ToInt32(cRow["CHARACTER_MAXIMUM_LENGTH"], CultureInfo.InvariantCulture); eColumn.Type = TypeSqliteToDbsm(cRow["DATA_TYPE"].ToString()); eColumn.Nullable = Convert.ToBoolean(cRow["IS_NULLABLE"], CultureInfo.InvariantCulture); eColumn.AutoIncrement = Convert.ToBoolean(cRow["PRIMARY_KEY"], CultureInfo.InvariantCulture) && tablesWithAutoIncrementPKs.Contains(eTable.Name); if (eColumn.AutoIncrement) { // В SQLite нет тонкой настройки автоинкремента // eColumn.Seed = 1; eColumn.Increment = 1; } var hasDefault = Convert.ToBoolean(cRow["COLUMN_HASDEFAULT"], CultureInfo.InvariantCulture); eColumn.DefaultValue = hasDefault ? cRow["COLUMN_DEFAULT"].ToString() : null; eColumn.DecimalPrecision = cRow["NUMERIC_PRECISION"] == DBNull.Value ? 0 : Convert.ToInt32(cRow["NUMERIC_PRECISION"], CultureInfo.InvariantCulture); eColumn.DecimalScale = cRow["NUMERIC_SCALE"] == DBNull.Value ? 0 : Convert.ToInt32(cRow["NUMERIC_SCALE"], CultureInfo.InvariantCulture); eTable.Columns[j] = eColumn; } aStore.Add(eTable); } return aStore; }
protected override CatalogSchema OnGetSchema(DbConnection connection) { var tables = connection.GetSchema("Tables"); var columns = connection.GetSchema("Columns"); var indexColumns = connection.GetSchema("IndexColumns"); var tableRows = tables.Rows.Cast <DataRow>().ToArray(); var catalog = new CatalogSchema { ConnectionString = connection.ConnectionString, Name = (string)tableRows.FirstOrDefault()?["TABLE_CATALOG"], AssemblyQualifiedName = typeof(PostgreSQLDataAccess).AssemblyQualifiedName }; #region Create Tables foreach (DataRow column in columns.Rows) { var tableSchema = (string)column["TABLE_SCHEMA"]; var tableName = (string)column["TABLE_NAME"]; var columnName = (string)column["COLUMN_NAME"]; var ordinalPosition = (int)column["ORDINAL_POSITION"]; var isNullable = (string)column["IS_NULLABLE"]; var dataType = (string)column["DATA_TYPE"]; var maxCharsLength = (int?)(column["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? column["CHARACTER_MAXIMUM_LENGTH"] : null); var maxBytesLength = (int?)(column["CHARACTER_OCTET_LENGTH"] != DBNull.Value ? column["CHARACTER_OCTET_LENGTH"] : null); var numericPrecision = (int?)(column["NUMERIC_PRECISION"] != DBNull.Value ? column["NUMERIC_PRECISION"] : null); var numericPrecisionRadix = (int?)(column["NUMERIC_PRECISION_RADIX"] != DBNull.Value ? column["NUMERIC_PRECISION_RADIX"] : null); if (tableSchema != "public") { continue; } var tableRow = tableRows.FirstOrDefault((dRow, tName) => (string)dRow["TABLE_NAME"] == tName, tableName); var table = catalog.Tables.FirstOrDefault((t, mTableName, mTableSchema) => t.Name == mTableName && t.Schema == mTableSchema, tableName, tableSchema); if (table is null) { table = new TableSchema { Name = tableName, Schema = tableSchema }; switch ((string)tableRow?["TABLE_TYPE"]) { case "BASE TABLE": table.Type = TableType.Table; break; case "VIEW": table.Type = TableType.View; break; } catalog.Tables.Add(table); } var tableColumn = table.Columns.FirstOrDefault((c, cName) => c.Name == cName, columnName); if (tableColumn is null) { tableColumn = new TableColumnSchema { Name = columnName, Position = ordinalPosition, IsNullable = isNullable == "YES", NumericPrecision = numericPrecision, NumericPrecisionRadix = numericPrecisionRadix }; #region DataType Switch switch (dataType) { case "uniqueidentifier": case "uuid": tableColumn.DataType = (tableColumn.IsNullable ? "Guid?" : "Guid"); break; case "char": case "bpchar": case "nchar": case "varchar": case "nvarchar": case "text": case "character": case "character varying": tableColumn.DataType = "string"; tableColumn.MaxLength = maxCharsLength; break; case "varbinary": case "binary": case "rowversion": case "bytea": tableColumn.DataType = "byte[]"; tableColumn.MaxLength = maxBytesLength; break; case "bigint": case "int8": tableColumn.DataType = (tableColumn.IsNullable ? "long?" : "long"); break; case "int": case "integer": case "int4": tableColumn.DataType = (tableColumn.IsNullable ? "int?" : "int"); break; case "smallint": case "int2": tableColumn.DataType = (tableColumn.IsNullable ? "short?" : "short"); break; case "tinyint": tableColumn.DataType = (tableColumn.IsNullable ? "byte?" : "byte"); break; case "bit": case "boolean": case "bool": tableColumn.DataType = (tableColumn.IsNullable ? "bool?" : "bool"); break; case "real": case "float4": tableColumn.DataType = (tableColumn.IsNullable ? "float?" : "float"); break; case "float": case "double precision": case "double": case "float8": tableColumn.DataType = (tableColumn.IsNullable ? "double?" : "double"); break; case "smallmoney": case "money": case "numeric": case "decimal": tableColumn.DataType = (tableColumn.IsNullable ? "decimal?" : "decimal"); break; case "date": case "smalldatetime": case "datetime": case "time": case "timetz": case "timestamp": case "timestamptz": tableColumn.DataType = (tableColumn.IsNullable ? "DateTime?" : "DateTime"); break; case "geography": tableColumn.DataType = "object"; break; case "interval": tableColumn.DataType = (tableColumn.IsNullable ? "TimeSpan?" : "TimeSpan"); break; } #endregion table.Columns.Add(tableColumn); } } #endregion #region Create Indexes foreach (DataRow indexColumn in indexColumns.Rows) { var tableSchema = (string)indexColumn["table_schema"]; var tableName = (string)indexColumn["table_name"]; var columnName = (string)indexColumn["column_name"]; var indexName = (string)indexColumn["index_name"]; var table = catalog.Tables.FirstOrDefault((t, mTableName, mTableSchema) => t.Name == mTableName && t.Schema == mTableSchema, tableName, tableSchema); if (table is null) { continue; } var tIndex = table.Indexes.FirstOrDefault((i, iName) => i.Name == iName, indexName); if (tIndex is null) { tIndex = new TableIndexSchema { Name = indexName, ConstraintName = indexName }; if (indexName.StartsWith("PK_")) { tIndex.Type = IndexType.PrimaryKey; } else if (indexName.StartsWith("UK_")) { tIndex.Type = IndexType.UniqueKey; } else if (indexName.StartsWith("IX_")) { tIndex.Type = IndexType.NonClusteredNonUniqueIndex; } else if (indexName.StartsWith("UX_")) { tIndex.Type = IndexType.UniqueIndex; } else if (indexName.StartsWith("AK_")) { tIndex.Type = IndexType.AlternateIndex; } else if (indexName.StartsWith("CLIX_")) { tIndex.Type = IndexType.ClusteredIndex; } else if (indexName.StartsWith("COVIX_")) { tIndex.Type = IndexType.CoveringIndex; } else if (indexName.StartsWith("UC_")) { tIndex.Type = IndexType.UniqueClusteredIndex; } table.Indexes.Add(tIndex); } var tColumn = new TableIndexColumnSchema { ColumnName = columnName }; tIndex.Columns.Add(tColumn); var column = table.Columns.FirstOrDefault((cName, colName) => cName.Name == colName, columnName); if (column != null && !column.IndexesName.Contains(indexName)) { column.IndexesName.Add(indexName); } } #endregion return(catalog); }
public static string TypeDbsmToSqlite(TableColumnSchema eColumn) { switch (eColumn.Type) { case ColumnType.Boolean: return "BIT"; case ColumnType.BigInt: return "BIGINT"; case ColumnType.Binary: return "BINARY"; case ColumnType.Character: return "CHAR(" + eColumn.Size + ")"; case ColumnType.CharacterVaring: return "VARCHAR(" + eColumn.Size + ")"; case ColumnType.Timestamp: return "TIMESTAMP"; case ColumnType.Decimal: return "DECIMAL(" + eColumn.DecimalPrecision + ", " + eColumn.DecimalScale + ")"; case ColumnType.Integer: // There should be "INT" but column type Integer is widelly used as an autoincrement // type which must be Int64 for SQLite. So there is an implicit 32-64 bit conversion. // return "INTEGER"; case ColumnType.Float: return "REAL(" + eColumn.DecimalPrecision + ")"; case ColumnType.DoublePrecision: return "DOUBLE(" + eColumn.DecimalPrecision + ")"; case ColumnType.Money: return "MONEY"; case ColumnType.NCharacter: return "NCHAR(" + eColumn.Size + ")"; case ColumnType.NCharacterVaring: return "NVARCHAR(" + eColumn.Size + ")"; case ColumnType.Real: return "REAL"; case ColumnType.SmallInt: return "SMALLINT"; case ColumnType.TinyInt: return "TINYINT"; case ColumnType.Guid: return "UNIQUEIDENTIFIER"; case ColumnType.BinaryVaring: return "VARBINARY"; case ColumnType.BlobSubtypeNText: return "NTEXT"; case ColumnType.BlobSubtypeImage: return "IMAGE"; default: throw new NotSupportedException("Data type '" + eColumn.Type + "' is not supported by " + SqliteDriver.DriverName); } }
public string MakeDdlColumnCreate(TableColumnSchema column, TableSchema table) { return(string.Format(@"ALTER TABLE {0} ADD {1}", MakeDdlElementName(table.Name), ParseColumn(column))); }
public static string TypeDbsmToSql(TableColumnSchema eColumn) { switch (eColumn.Type) { case ColumnType.Boolean: return "BIT"; case ColumnType.BigInt: return "BIGINT"; case ColumnType.Binary: return "BINARY"; case ColumnType.Character: return "CHAR(" + eColumn.Size + ")"; case ColumnType.CharacterVaring: return "VARCHAR(" + eColumn.Size + ")"; case ColumnType.Cursor: return "CURSOR"; case ColumnType.Timestamp: return "DATETIME"; case ColumnType.Decimal: return "DECIMAL(" + eColumn.DecimalPrecision + ", " + eColumn.DecimalScale + ")"; case ColumnType.Integer: return "INT"; case ColumnType.BlobSubtypeImage: return "IMAGE"; case ColumnType.Float: return "FLOAT(" + eColumn.DecimalPrecision + ")"; case ColumnType.Money: return "MONEY"; case ColumnType.NCharacter: return "NCHAR(" + eColumn.Size + ")"; case ColumnType.NCharacterVaring: return "NVARCHAR(" + eColumn.Size + ")"; case ColumnType.Numeric: return "NUMERIC(" + eColumn.DecimalPrecision + ", " + eColumn.DecimalScale + ")"; case ColumnType.BlobSubtypeNText: return "NTEXT"; case ColumnType.Real: return "REAL"; case ColumnType.SmallMoney: return "SMALLMONEY"; case ColumnType.SmallDateTime: return "SMALLDATETIME"; case ColumnType.SmallInt: return "SMALLINT"; case ColumnType.SqlVariant: return "SQL_VARIANT"; case ColumnType.Table: return "TABLE"; case ColumnType.BlobSubtypeText: return "TEXT"; case ColumnType.MsTimestamp: return "TIMESTAMP"; case ColumnType.TinyInt: return "TINYINT"; case ColumnType.Guid: return "UNIQUEIDENTIFIER"; case ColumnType.BinaryVaring: return "VARBINARY"; default: throw new ArgumentException("Unsupported data type for " + MssqlDriver.DriverName); } }
public abstract string MakeDdlColumnRename(TableColumnSchema column, string newName, TableSchema table);
private static List<TableSchema> GetTables(SqlConnection con) { var tables = new List<TableSchema>(); string[] restrict4 = {null, null, null, "TABLE"}; string[] restrict3 = {null, null, null}; var dtTables = SqlSchemaFactory.GetSchema(con, "Tables", restrict4); for (var i = 0; i < dtTables.Rows.Count; i++) { var tRow = dtTables.Rows[i]; var eTable = new TableSchema {Name = tRow["TABLE_NAME"].ToString()}; // Columns restrict3[0] = null; restrict3[1] = null; restrict3[2] = eTable.Name; var dtShema = SqlSchemaFactory.GetSchema(con, "Columns", restrict3); if (dtShema.Rows.Count > 0) { eTable.Columns = new TableColumnSchema[dtShema.Rows.Count]; for (var j = 0; j < dtShema.Rows.Count; j++) { var cRow = dtShema.Rows[j]; var eColumn = new TableColumnSchema { Name = cRow["COLUMN_NAME"].ToString(), Size = Convert.ToInt32(cRow["COLUMN_SIZE"], CultureInfo.InvariantCulture), Type = TypeSqlToDbsm(cRow["COLUMN_DATA_TYPE"].ToString()), Nullable = Convert.ToBoolean(cRow["IS_NULLABLE"], CultureInfo.InvariantCulture), DefaultValue = cRow["COLUMN_DEFAULT"].ToString(), Increment = Convert.ToInt32(cRow["IDENTITY_INCREMENT"], CultureInfo.InvariantCulture), Seed = Convert.ToInt32(cRow["IDENTITY_SEED"], CultureInfo.InvariantCulture), AutoIncrement = Convert.ToBoolean(cRow["IS_IDENTITY"], CultureInfo.InvariantCulture), DecimalPrecision = Convert.ToInt32(cRow["NUMERIC_PRECISION"], CultureInfo.InvariantCulture), DecimalScale = Convert.ToInt32(cRow["NUMERIC_SCALE"], CultureInfo.InvariantCulture) }; eColumn.DefaultValue = string.IsNullOrEmpty(eColumn.DefaultValue) ? null : UnBracket.ParseUnBracket(eColumn.DefaultValue); eTable.Columns[j] = eColumn; } } tables.Add(eTable); } return tables; }
protected override string MakeDdlColumnDrop(TableColumnSchema column, TableSchema table) { return $@"ALTER TABLE {MakeDdlElementName(table.Name)} DROP {MakeDdlElementName(column.Name)}"; }
protected override string ParseColumn(TableColumnSchema eColumn) { var stat = new StringBuilder(); stat.AppendFormat(@" [" + eColumn.Name + @"] " + SqliteSchemaLoader.TypeDbsmToSqlite(eColumn)); if (!String.IsNullOrEmpty(eColumn.DefaultValue)) stat.Append(@" DEFAULT " + eColumn.DefaultValue); if (!eColumn.Nullable) stat.Append(@" NOT NULL"); return stat.ToString(); }