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();
}
