public static DBSchema LoadSchema(string constr)
{
var csb = new FbConnectionStringBuilder(constr);
//csb.Pooling = false;
using (var con = new FbConnection(csb.ConnectionString))
{
con.Open();
var dbsc =
new DBSchema
{
Name = csb.Database,
Tables = GetTables(con)
};
foreach (var table in dbsc.Tables)
{
table.Keys = GetKeys(con, table).ToArray();
table.Indexes = GetIndexes(con, table).ToArray();
}
dbsc.Generators = GetGenerators(con);
return(dbsc);
}
}
/// <summary>
/// 清除所有缓存
/// </summary>
public override void Clear()
{
try
{
lock (lockObj)
{
DBSchema.Clear();
TableSchema.Clear();
theCache.Clear();
theTime.Clear();
theFileName.Clear();
theKeyTime.Clear();
for (int i = 0; i < theFolderWatcher.Count; i++)
{
theFolderWatcher[i].Changed -= new FileSystemEventHandler(fsy_Changed);
theFolderWatcher[i] = null;
}
theFolderWatcher.Clear();
theFolderKeys.Clear();
}
}
catch
{
errorCount++;
}
}
public CommonAccessProvider(DBSchema schema)
{
Schema = schema;
UserGroupTable = (DBTable <UserGroup>)Schema.Tables.FirstOrDefault(p => p.ItemType.Type == typeof(UserGroup));
UserTable = (DBTable <User>)Schema.Tables.FirstOrDefault(p => p.ItemType.Type == typeof(User));
CompanyTable = (DBTable <Company>)Schema.Tables.FirstOrDefault(p => p.ItemType.Type == typeof(Company));
}
/// <summary>
/// 删除一个Cache对象
/// </summary>
/// <param name="key">标识</param>
public override bool Remove(string key)
{
if (string.IsNullOrEmpty(key))
{
return(false);
}
DBSchema.Remove(key);
TableSchema.Remove(key);
if (key.StartsWith("CCache_"))
{
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
IOHelper.Delete(AppConfig.RunPath + AppConfig.DB.SchemaMapPath + key + ".ts");
//顺带移除数据库表、视图、存储过程 名称列表缓存。
string[] files = Directory.GetFiles(AppConfig.RunPath + AppConfig.DB.SchemaMapPath, "*.json");
if (files != null && files.Length > 0)
{
foreach (string file in files)
{
IOHelper.Delete(file);
}
}
}
}
return(theCache.Remove(key));//清除Cache，其它数据在定义线程中移除
}
/// <summary>
/// 清除所有缓存
/// </summary>
public override void Clear()
{
try
{
lock (lockObj)
{
DBSchema.Clear();
TableSchema.Clear();
theCache.Clear();
theTime.Clear();
theFileName.Clear();
theKeyTime.Clear();
for (int i = 0; i < theFolderWatcher.Count; i++)
{
theFolderWatcher[i].Changed -= new FileSystemEventHandler(fsy_Changed);
theFolderWatcher[i] = null;
}
theFolderWatcher.Clear();
theFolderKeys.Clear();
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
if (Directory.Exists(AppConfig.RunPath + AppConfig.DB.SchemaMapPath))
{
Directory.Delete(AppConfig.RunPath + AppConfig.DB.SchemaMapPath, true);
}
}
}
}
catch
{
errorCount++;
}
}
public static DBSchema LoadSchema(string constr)
{
var csb = new FbConnectionStringBuilder(constr);
//csb.Pooling = false;
using (var con = new FbConnection(csb.ConnectionString))
{
con.Open();
var dbsc =
new DBSchema
{
Name = csb.Database,
Tables = GetTables(con)
};
foreach (var table in dbsc.Tables)
{
table.Keys = GetKeys(con, table).ToArray();
table.Indexes = GetIndexes(con, table).ToArray();
}
dbsc.Generators = GetGenerators(con);
return dbsc;
}
}
public async Task Initialize()
{
Environment.CurrentDirectory = AppDomain.CurrentDomain.BaseDirectory;
var schema = new DBSchema("common_database");
schema.Generate(new[] {
typeof(Customer).Assembly,
typeof(User).Assembly
});
DBService.Schems.Add(schema);
Assert.IsNotNull(schema);
Assert.IsNotNull(Book.DBTable);
Assert.IsNotNull(UserGroup.DBTable);
Assert.IsNotNull(GroupPermission.DBTable);
Assert.IsNotNull(User.DBTable);
Assert.IsNotNull(Position.DBTable);
Assert.IsNotNull(UserReg.DBTable);
Assert.IsNotNull(Company.DBTable);
DBService.Save();
schema.Connection = new DBConnection
{
Name = "test.common",
System = DBSystem.SQLite,
DataBase = "test.common"
};
schema.DropDatabase();
schema.CreateDatabase();
var group = new UserGroup()
{
Number = "GP1",
Name = "Group1"
};
await group.Save();
var position = new Position()
{
Code = "PS1",
Name = "Position"
};
var user = new User()
{
Login = "******",
Name = "Test User",
Password = "******",
Position = position,
AuthType = UserAuthType.Internal,
Access = new AccessValue(new[] { new AccessItem(group, AccessType.Create) })
};
await user.Save();
await User.StartSession("test", "UserCommon1!");
await GroupPermission.CachePermission();
}
/// <summary>
/// 删除一个Cache对象
/// </summary>
/// <param name="key">标识</param>
public override bool Remove(string key)
{
if (string.IsNullOrEmpty(key))
{
return(false);
}
DBSchema.Remove(key);
TableSchema.Remove(key);
return(theCache.Remove(key));//清除Cache，其它数据在定义线程中移除
}
protected override void AfterSchemaComparision(DBSchema existingSchema, DBSchema targetSchema)
{
// По-хорошему, так делать нельзя.
// Тут нужно поменять консерваторию, чтобы конкретный движок
// мог сказать, что эту операцию он не поддерживает, но если тупо
// пересоздать эту таблицу и перелить в неё данные то всё будет ок.
// А пока что тупая затычка, чтобы колонки, которые должны быть
// autoincrement, но не являются таковыми, всё-таки стали
// autoincrement'ными.
DeleteDdlCommandsByFilter("ObjectType='KeyPrimary' OR ObjectType='KeyForeign'");
var i = 1;
foreach (var mTable in targetSchema.Tables)
{
var eTable = existingSchema.GetTable(mTable.Name);
if (eTable == null)
{
continue;
}
// ReSharper disable AccessToModifiedClosure
var recreate =
mTable
.Columns
.Select(mColumn => eTable.GetColumn(mColumn.Name))
.Where(eColumn => eColumn != null)
.Any(eColumn => !mTable.IsExactColumn(eColumn));
// ReSharper restore AccessToModifiedClosure
if (!recreate)
{
continue;
}
var tTable = mTable.Clone();
tTable.Name = mTable.Name + @"_tmp";
AddDdlCommand(i, tTable.Name, "RecreateTable", MakeDdlTableCreate(tTable, true));
foreach (var tIndex in tTable.Indexes)
{
AddDdlCommand(i, tTable.Name + @"." + tIndex.Name, "RecreateIndex", MakeDdlIndexCreate(tIndex, tTable));
}
AddDdlCommand(i, tTable.Name + @"." + tTable.Name, "RecreateInsert", MakeDdlTableCopy(tTable, mTable));
AddDdlCommand(i, tTable.Name, "RecreateRename", MakeDdlTableRename(tTable, mTable.Name));
AddDdlCommand(i, mTable.Name, "RecreateTable", MakeDdlTableDrop(mTable));
i++;
}
}
public DBSchema GetSchema(SqlDataConnection connection)
{
//Load DB Schema without loading columns
DBSchema defaultSchema = connection.GetDBSchema(false);
//Select only required tables/views/procedures
DBTable[] tables = defaultSchema.Tables.Where((table) => { return(!table.Name.StartsWith("Order")); }).ToArray();
DBTable[] views = defaultSchema.Views.ToArray();
DBStoredProcedure[] storedProcedures = defaultSchema.StoredProcedures.ToArray();
//Create a new schema
return(new DBSchema(tables, views, storedProcedures));
}
private void UpdateEntityInModel(MyProjectEntities projectContext, int databaseID, DatabaseFunctionDTO function, List <DBSchema> listAddedSchema)
{
DBSchema dbSchema = null;
var schema = function.RelatedSchema;
dbSchema = listAddedSchema.FirstOrDefault(x => x.DatabaseInformationID == databaseID && x.Name == function.RelatedSchema);
if (dbSchema == null)
{
dbSchema = new DBSchema()
{
DatabaseInformationID = databaseID, Name = schema
};
dbSchema.SecurityObject = new SecurityObject();
dbSchema.SecurityObject.Type = (int)DatabaseObjectCategory.Schema;
projectContext.DBSchema.Add(dbSchema);
listAddedSchema.Add(dbSchema);
}
var dbFunction = projectContext.DatabaseFunction.Include("DatabaseFunctionParameter").FirstOrDefault(x => x.FunctionName == function.Name && x.DBSchema.DatabaseInformationID == databaseID);
if (dbFunction == null)
{
dbFunction = new DatabaseFunction();
projectContext.DatabaseFunction.Add(dbFunction);
dbFunction.FunctionName = function.Name;
dbFunction.Enable = true;
}
dbFunction.DBSchema = dbSchema;
dbFunction.Type = (short)function.Type;
foreach (var column in function.DatabaseFunctionParameter)
{
var dbColumn = dbFunction.DatabaseFunctionParameter.Where(x => x.ParamName == column.ParameterName).FirstOrDefault();
if (dbColumn == null)
{
dbColumn = new DatabaseFunctionParameter();
dbColumn.ParamName = column.ParameterName;
dbColumn.Enable = true;
dbFunction.DatabaseFunctionParameter.Add(dbColumn);
}
dbColumn.InputOutput = (short)column.InputOutput;
dbColumn.DataType = column.DataType;
dbColumn.ParamName = column.ParameterName;
}
var columnNames = function.DatabaseFunctionParameter.Select(x => x.ParameterName).ToList();
foreach (var dbColumn in dbFunction.DatabaseFunctionParameter.Where(x => !columnNames.Contains(x.ParamName)))
{
dbColumn.Enable = false;
}
}
public void SetCommand(IDbCommand command, DBSchema schema, string name)
{
if (Query == null)
{
Query = new QResult()
{
Name = name
}
}
;
Query.Name = name;
this.schema = schema;
this.command = command;
}
/// <summary>
/// Create table in the database.
/// </summary>
/// <param name="sqls"></param>
/// <param name="ctx"></param>
/// <param name="currentModel"></param>
public virtual void ExecuteSqlInDb(List <string> sqls, ModelContext ctx, Model currentModel)
{
DbCreateType createType = ctx.AllModels.Settings.DbAction_Create;
DBSchema helper = new DBSchema(_conn);
string error = "Error executing sql for model : " + currentModel.Name + " table name : " + currentModel.TableName;
Try.CatchLog(error, () =>
{
foreach (string sql in sqls)
{
helper.ExecuteNonQuery(sql, CommandType.Text, null);
}
});
}
bool CreateTable()
{
// database created successfuly then create table
if (db.Exists)
{
// Create table :: schema
// you can create table when you want
DBSchema schema = new DBSchema("Users");
schema.AddField("Id", SQLiteDB.DB_DataType.DB_INT, 0, false, true, true);
schema.AddField("Name", SQLiteDB.DB_DataType.DB_VARCHAR, 100, false, false, false);
return(db.CreateTable(schema));
}
return(false);
}
protected override void AfterSchemaComparision(DBSchema existingSchema, DBSchema targetSchema)
{
// По-хорошему, так делать нельзя.
// Тут нужно поменять консерваторию, чтобы конкретный движок
// мог сказать, что эту операцию он не поддерживает, но если тупо
// пересоздать эту таблицу и перелить в неё данные то всё будет ок.
// А пока что тупая затычка, чтобы колонки, которые должны быть
// autoincrement, но не являются таковыми, всё-таки стали
// autoincrement'ными.
DeleteDdlCommandsByFilter("ObjectType='KeyPrimary' OR ObjectType='KeyForeign'");
var i = 1;
foreach (var mTable in targetSchema.Tables)
{
var eTable = existingSchema.GetTable(mTable.Name);
if (eTable == null)
continue;
// ReSharper disable AccessToModifiedClosure
var recreate =
mTable
.Columns
.Select(mColumn => eTable.GetColumn(mColumn.Name))
.Where(eColumn => eColumn != null)
.Any(eColumn => !mTable.IsExactColumn(eColumn));
// ReSharper restore AccessToModifiedClosure
if (!recreate)
continue;
var tTable = mTable.Clone();
tTable.Name = mTable.Name + @"_tmp";
AddDdlCommand(i, tTable.Name, "RecreateTable", MakeDdlTableCreate(tTable, true));
foreach (var tIndex in tTable.Indexes)
AddDdlCommand(i, tTable.Name + @"." + tIndex.Name, "RecreateIndex", MakeDdlIndexCreate(tIndex, tTable));
AddDdlCommand(i, tTable.Name + @"." + tTable.Name, "RecreateInsert", MakeDdlTableCopy(tTable, mTable));
AddDdlCommand(i, tTable.Name, "RecreateRename", MakeDdlTableRename(tTable, mTable.Name));
AddDdlCommand(i, mTable.Name, "RecreateTable", MakeDdlTableDrop(mTable));
i++;
}
}
private void ToolDBGenerateClick(object sender, EventArgs e)
{
var assemblyList = new SelectableList <AsseblyCheck>();
string[] asseblies = Directory.GetFiles(Helper.GetDirectory(), "*.dll");
foreach (string dll in asseblies)
{
AssemblyDefinition assemblyDefinition = null;
try { assemblyDefinition = AssemblyDefinition.ReadAssembly(dll); }
catch { continue; }
var moduleAttribute = assemblyDefinition.CustomAttributes
.Where(item => item.AttributeType.Name == nameof(AssemblyMetadataAttribute))
.Select(item => item.ConstructorArguments.Select(sitem => sitem.Value.ToString())
.ToArray());
if (moduleAttribute.Any(item => item[0] == "module"))
{
assemblyList.Add(new AsseblyCheck(Assembly.LoadFile(dll)));
}
}
var list = new LayoutList
{
AllowCheck = true,
CheckRecursive = true,
AutoToStringFill = true,
GenerateToString = true,
GenerateColumns = false,
ListSource = assemblyList
};
var window = new ToolWindow
{
Title = "Select Assembly",
Target = list
};
window.Show(this, Point.Zero);
window.ButtonAcceptClick += (s, a) =>
{
var schema = new DBSchema("NewSchema");
schema.Generate(assemblyList.Where(p => p.Check).Select(p => p.Assembly));
DBService.Schems.Add(schema);
ShowNewItem(schema);
};
}
protected override void AfterSchemaComparision(DBSchema existingSchema, DBSchema targetSchema)
{
var i = 1;
foreach (var mTable in targetSchema.Tables)
{
var eTable = existingSchema.GetTable(mTable.Name);
if (eTable != null)
{
//bool recreate = false;
//bool toident = false;
foreach (var mColumn in mTable.Columns)
{
var eColumn = eTable.GetColumn(mColumn.Name);
if (eColumn != null)
{
if (!mTable.IsExactColumn(eColumn))
{
foreach (var eTable1 in existingSchema.Tables)
{
var eKey1 = eTable1.GetKey2(ConstraintType.KeyForeign, eTable.Name, eColumn.Name);
if (eKey1 != null)
{
AddDdlCommand(i,
eTable1.Name + @"." + eKey1.Name,
eKey1.KeyType.ToString(),
MakeDdlKeyDrop(eKey1, eTable1));
AddDdlCommand(i,
eTable1.Name + @"." + eKey1.Name,
eKey1.KeyType.ToString(),
MakeDdlKeyCreateByAlter(eKey1, eTable1));
}
}
AddDdlCommand(i,
eTable.Name + @"." + mColumn.Name,
"Column",
MakeDdlColumnAlter(mColumn, eColumn, eTable));
}
}
}
}
i++;
}
}
/// <summary>
/// Create table in the database.
/// </summary>
/// <param name="sql"></param>
/// <param name="ctx"></param>
/// <param name="currentModel"></param>
public virtual void ExecuteSqlProcsInDb(BoolMessageItem <List <string> > procs, ModelContext ctx, Model currentModel)
{
DbCreateType createType = ctx.AllModels.Settings.DbAction_Create;
DBSchema helper = new DBSchema(_conn);
try
{
foreach (string sql in procs.Item)
{
helper.ExecuteNonQuery(sql, CommandType.Text, null);
}
}
catch (Exception ex)
{
Logger.Error("Error creating tables for model : " + currentModel.Name + " table name : " + currentModel.TableName);
Logger.Error(ex.Message);
}
}
/// <summary>
/// Create table in the database.
/// </summary>
/// <param name="sql"></param>
/// <param name="ctx"></param>
/// <param name="currentModel"></param>
public virtual void ExecuteSqlInDb(string sql, ModelContext ctx, Model currentModel)
{
DbCreateType createType = ctx.AllModels.Settings.DbAction_Create;
DBSchema helper = new DBSchema(_conn);
try
{
if (createType == DbCreateType.DropCreate)
{
helper.DropTable(currentModel.TableName);
}
helper.ExecuteNonQuery(sql, CommandType.Text, null);
}
catch (Exception ex)
{
Logger.Error("Error creating tables for model : " + currentModel.Name + " table name : " + currentModel.TableName);
Logger.Error(ex.Message);
}
}
private async void ORMTestClick(object sender, EventArgs e)
{
var list = new DataExplorer();
dock.Put(list);
await Task.Run(() =>
{
var schema = DBSchema.Generate(typeof(User).Assembly, "common_data");
schema.Connection = new DBConnection
{
Name = "test.common",
System = DBSystem.SQLite,
DataBase = "test.common.sqlite"
};
schema.DropDatabase();
schema.CreateDatabase();
}).ConfigureAwait(false);
}
public override Task CreateNew()
{
Schema = new DBSchema()
{
Name = schemaName,
Connection = new DBConnection
{
Name = schemaName,
System = DBSystem.SQLite,
DataBase = $"{schemaName}.sqlite"
}
};
Generate();
Schema.DropDatabase();
Schema.CreateDatabase();
Save();
return(Task.CompletedTask);
}
//public void IntDBSchema()
//{
// toolTable.DropDownItems.Clear();
// if (state == SearchState.Edit)
// foreach (DBSchema sc in DBService.Schems)
// toolTable.DropDownItems.Add(InitSchemaTool(sc));
// else if (state == SearchState.Reference && owner != null)
// if (owner.Column.IsPrimaryKey)
// {
// List<DBRelation> relations = owner.Column.Table.GetChildRelations();
// foreach (DBRelation relation in relations)
// toolTable.DropDownItems.Add(InitTableTool(relation.Table));
// }
// else if (owner.Column.ReferenceTable != null)
// {
// toolTable.DropDownItems.Add(InitTableTool(owner.Column.ReferenceTable));
// Table = owner.Column.ReferenceTable;
// toolTable.Sensitive = false;
// }
//}
protected ToolMenuItem InitSchemaTool(DBSchema schema)
{
var item = new ToolMenuItem {
Tag = schema, Name = schema.Name, Text = schema.ToString(), DropDown = new Menubar {
Name = schema.Name
}
};
var list = new SelectableList <DBTable>(schema.Tables);
list.ApplySortInternal("Name", ListSortDirection.Ascending);
foreach (DBTable ts in list)
{
if (ts.Access.GetFlag(AccessType.Admin, GuiEnvironment.User))
{
item.DropDown.Items.Add(InitTableTool(ts));
}
}
return(item);
}
private void UpdateDBSchema()
{
ImportTableComboBox.Items.Clear();
ExportTableComboBox.Items.Clear();
DBSchema.ConnectionString = $"{DBConnectionString}; DataProviderString={{{DBDataProviderString}}}; SerializedSchema={SerializedSchema}";
DBSchema.Analyze();
foreach (Table table in DBSchema.Tables)
{
ImportTableComboBox.Items.Add(table);
ExportTableComboBox.Items.Add(table);
}
if (ImportTableComboBox.Items.Count > 0)
{
ImportTableComboBox.SelectedIndex = 0;
}
}
public void SchemaSerialization()
{
var schem = DBSchema.Generate(GetType().Assembly, SchemaName);
var file = "data.xml";
Serialization.Serialize(DBService.Schems, file);
DBService.Schems.Clear();
Serialization.Deserialize(file, DBService.Schems);
Assert.AreEqual(2, DBService.Schems.Count);
Assert.AreEqual(2, schem.Tables.Count);
var table = schem.Tables[EmployerTableName];
Assert.IsNotNull(table);
Assert.IsInstanceOf <DBTable <Employer> >(table);
var column = table.Columns["id"];
Assert.IsNotNull(column);
Assert.AreEqual(typeof(int), column.DataType);
}
protected override void AfterSchemaComparision(DBSchema existingSchema, DBSchema targetSchema)
{
var i = 1;
foreach (var mTable in targetSchema.Tables)
{
var eTable = existingSchema.GetTable(mTable.Name);
if (eTable != null)
//bool recreate = false;
//bool toident = false;
foreach (var mColumn in mTable.Columns)
{
var eColumn = eTable.GetColumn(mColumn.Name);
if (eColumn != null)
if (!mTable.IsExactColumn(eColumn))
{
foreach (var eTable1 in existingSchema.Tables)
{
var eKey1 = eTable1.GetKey2(ConstraintType.KeyForeign, eTable.Name, eColumn.Name);
if (eKey1 != null)
{
AddDdlCommand(i,
eTable1.Name + @"." + eKey1.Name,
eKey1.KeyType.ToString(),
MakeDdlKeyDrop(eKey1, eTable1));
AddDdlCommand(i,
eTable1.Name + @"." + eKey1.Name,
eKey1.KeyType.ToString(),
MakeDdlKeyCreateByAlter(eKey1, eTable1));
}
}
AddDdlCommand(i,
eTable.Name + @"." + mColumn.Name,
"Column",
MakeDdlColumnAlter(mColumn, eColumn, eTable));
}
}
i++;
}
}
public static void ReloadTableSchema(SqlConnection conn, DBSchema schema, TableSchema table)
{
string[] restrict4 = { null, null, table.Name, "TABLE" };
var dtTables = SqlSchemaFactory.GetSchema(conn, "Tables", restrict4);
if (dtTables.Rows.Count == 0)
{
schema.Tables.Remove(table);
table.Name += "_FANTOM";
table.Columns = new TableColumnSchema[0];
table.Keys = new KeySchema[0];
table.Indexes = new IndexSchema[0];
return;
}
var freshTable = GetTable(conn, dtTables.Rows[0]);
table.Columns = freshTable.Columns;
table.Keys = GetKeys(conn, table).ToArray();
table.Indexes = GetIndexes(conn, table).ToArray();
}
public static DBSchema LoadSchema(string constr)
{
var csb = new SqlConnectionStringBuilder(constr);
using (var con = new SqlConnection(csb.ConnectionString))
{
con.Open();
var dbsc = new DBSchema
{
Name = csb.InitialCatalog,
Tables = GetTables(con)
};
foreach (var table in dbsc.Tables)
{
table.Keys = GetKeys(con, table).ToArray();
table.Indexes = GetIndexes(con, table).ToArray();
}
return dbsc;
}
}
public static DBSchema LoadSchema(string constr)
{
var csb = new SQLiteConnectionStringBuilder(constr);
using (var con = new SQLiteConnection(csb.ConnectionString))
{
con.Open();
var dbsc = new DBSchema
{
Name = csb.DataSource,
Tables = GetTables(con)
};
foreach (var eTable in dbsc.Tables)
{
eTable.Keys = GetKeys(con, eTable).ToArray();
eTable.Indexes = GetIndexes(con, eTable).ToArray();
}
return dbsc;
}
}
public static DBSchema LoadSchema(string constr)
{
var csb = new SQLiteConnectionStringBuilder(constr);
using (var con = new SQLiteConnection(csb.ConnectionString))
{
con.Open();
var dbsc = new DBSchema
{
Name = csb.DataSource,
Tables = GetTables(con)
};
foreach (var eTable in dbsc.Tables)
{
eTable.Keys = GetKeys(con, eTable).ToArray();
eTable.Indexes = GetIndexes(con, eTable).ToArray();
}
return(dbsc);
}
}
public static DBSchema LoadSchema(string constr)
{
var csb = new SqlConnectionStringBuilder(constr);
using (var con = new SqlConnection(csb.ConnectionString))
{
con.Open();
var dbsc = new DBSchema
{
Name = csb.InitialCatalog,
Tables = GetTables(con)
};
foreach (var table in dbsc.Tables)
{
table.Keys = GetKeys(con, table).ToArray();
table.Indexes = GetIndexes(con, table).ToArray();
}
return(dbsc);
}
}
protected override DBSchema DbscCopyPrepare(DBSchema schema)
{
return(schema.Copy());
}
public static DBSchema LoadSchema(string connStr)
{
var dbsc = new DBSchema();
var catalog = (Catalog)new CatalogClass();
var conn = new ConnectionClass();
try
{
var csb = new JetConnectionStringBuilder(connStr);
var keysList = new List<KeySchema>();
var indexesList = new List<IndexSchema>();
conn.Open(csb.ConnectionString, string.Empty, string.Empty, 0);
catalog.ActiveConnection = conn;
var slashPos = csb.DataSource.LastIndexOf(@"\");
dbsc.Name = csb.DataSource.Substring(slashPos == -1 ? 0 : slashPos + 1);
foreach (var xTable in GetTables(catalog))
{
keysList.Clear();
indexesList.Clear();
var eTable = new TableSchema
{
Name = xTable.Name,
Columns = GetColumns(xTable).ToArray(),
Keys = GetKeys(xTable).ToArray(),
Indexes = GetIndexes(xTable).ToArray()
};
// Фильтрация ключей
foreach (var key in eTable.Keys)
{
// Долбаный Аксес создает при создании внешних ключей уникальные индексы.
if (key.KeyType == ConstraintType.KeyForeign && !eTable.IsIndexExist(key.Name))
continue;
if (key.KeyType == ConstraintType.Unique &&
eTable.IsKeyExist(key.Name, ConstraintType.KeyForeign))
continue;
keysList.Add(key);
}
eTable.Keys = keysList.ToArray();
// Фильтрация индексов
foreach (var index in eTable.Indexes)
{
if (eTable.IsKeyExist(index.Name, ConstraintType.Unique) ||
eTable.IsKeyExist(index.Name, ConstraintType.KeyPrimary) ||
eTable.IsKeyExist(index.Name, ConstraintType.KeyForeign))
continue;
indexesList.Add(index);
}
eTable.Indexes = indexesList.ToArray();
dbsc.Tables.Add(eTable);
}
}
finally
{
Marshal.ReleaseComObject(conn);
Marshal.ReleaseComObject(catalog);
}
return dbsc;
}
public virtual int Fill(DBSchema.OrderDetailsDataTable dataTable) {
this.Adapter.SelectCommand = this.CommandCollection[0];
if ((this.ClearBeforeFill == true)) {
dataTable.Clear();
}
int returnValue = this.Adapter.Fill(dataTable);
return returnValue;
}
public virtual int Update(DBSchema.OrderDetailsDataTable dataTable) {
return this.Adapter.Update(dataTable);
}
public virtual int Update(DBSchema dataSet) {
return this.Adapter.Update(dataSet, "Customers");
}
protected override DBSchema DbscCopyPrepare(DBSchema schema)
{
var newSchema = schema.Copy();
return newSchema;
}
public abstract void ReloadTableSchema(IDbConnection conn, DBSchema schema, TableSchema table);
protected override DBSchema DbscCopyPrepare(DBSchema schema)
{
var newSchema = schema.Copy();
return(newSchema);
}
protected override DBSchema DbscCopyPrepare(DBSchema schema)
{
return schema.Copy();
}
public virtual int Update(DBSchema.EmployeesDataTable dataTable) {
return this.Adapter.Update(dataTable);
}
protected override void AfterSchemaComparision(DBSchema existingSchema, DBSchema targetSchema)
{
// Drop Generators
var i = 1;
foreach (var eGen in existingSchema.Generators)
{
if (!targetSchema.IsGeneratorExist(eGen.Name))
{
AddDdlCommand(i,
eGen.Name,
"Generator",
MakeDdlGeneratorDrop(eGen));
}
i++;
}
// Create Generators
i = 1;
foreach (var mGenerator in targetSchema.Generators)
{
if (!existingSchema.IsGeneratorExist(mGenerator.Name))
{
AddDdlCommand(i,
mGenerator.Name,
"Generator",
MakeDdlGeneratorCreate(mGenerator));
AddDdlCommand(i,
mGenerator.Name,
"Generator",
MakeDdlGeneratorSet(mGenerator));
}
i++;
}
foreach (var mTable in targetSchema.Tables)
{
var eTable = existingSchema.GetTable(mTable.Name);
if (eTable != null)
{
foreach (var mColumn in mTable.Columns)
{
var eColumn = eTable.GetColumn(mColumn.Name);
if (eColumn == null)
continue;
if (mTable.IsExactColumn(eColumn) || eColumn.Type == ColumnType.BlobSubtype1 ||
eColumn.Type == ColumnType.BinaryLargeObject)
continue;
if (mColumn.Type != eColumn.Type)
{
AddDdlCommand(i,
eTable.Name + @"." + eColumn.Name,
"Column",
MakeDdlColumnAlter(mColumn, eColumn, eTable));
}
if (mColumn.Nullable && !eColumn.Nullable)
{
AddDdlCommand(i,
eTable.Name + @"." + eColumn.Name,
"Column",
string.Format(@"
UPDATE RDB$RELATION_FIELDS
SET
RDB$NULL_FLAG = NULL
WHERE
(RDB$FIELD_NAME = '{0}') AND (RDB$RELATION_NAME = '{1}')",
eColumn.Name,
eTable.Name));
}
if (mColumn.DefaultValue == eColumn.DefaultValue)
continue;
if (mColumn.DefaultValue != null)
{
AddDdlCommand(i,
eTable.Name + @"." + eColumn.Name,
"Column",
string.Format(@"
ALTER TABLE {0}
ADD IBE$$TEMP_COLUMN {1} DEFAULT {2}",
MakeDdlElementName(eTable.Name),
FBSchemaLoader.TypeDbsmToFb(eColumn),
mColumn.DefaultValue));
AddDdlCommand(i++,
eTable.Name + @"." + eColumn.Name,
"Column",
string.Format(@"
UPDATE RDB$RELATION_FIELDS F1
SET
F1.RDB$DEFAULT_VALUE =
(SELECT
F2.RDB$DEFAULT_VALUE
FROM
RDB$RELATION_FIELDS F2
WHERE
(F2.RDB$RELATION_NAME = '{0}') AND (F2.RDB$FIELD_NAME = 'IBE$$TEMP_COLUMN')),
F1.RDB$DEFAULT_SOURCE =
(SELECT
F3.RDB$DEFAULT_SOURCE
FROM
RDB$RELATION_FIELDS F3
WHERE
(F3.RDB$RELATION_NAME = '{0}') AND (F3.RDB$FIELD_NAME = 'IBE$$TEMP_COLUMN'))
WHERE
(F1.RDB$RELATION_NAME = '{0}') AND (F1.RDB$FIELD_NAME = '{0}')",
eTable.Name));
AddDdlCommand(i++,
eTable.Name + @"." + eColumn.Name,
"Column",
string.Format(@"ALTER TABLE {0} DROP IBE$$TEMP_COLUMN",
MakeDdlElementName(eTable.Name)));
}
else
{
AddDdlCommand(i,
eTable.Name + @"." + eColumn.Name,
"Column",
string.Format(@"
UPDATE RDB$RELATION_FIELDS F1
SET
F1.RDB$DEFAULT_VALUE = NULL,
F1.RDB$DEFAULT_SOURCE = NULL
WHERE
(F1.RDB$RELATION_NAME = '{0}') AND (F1.RDB$FIELD_NAME = '{1}')",
eTable.Name,
eColumn.Name));
}
}
}
i++;
}
}
public virtual int Update(DBSchema.CustomersDataTable dataTable) {
return this.Adapter.Update(dataTable);
}
public static global::System.Xml.Schema.XmlSchemaComplexType GetTypedTableSchema(global::System.Xml.Schema.XmlSchemaSet xs) {
global::System.Xml.Schema.XmlSchemaComplexType type = new global::System.Xml.Schema.XmlSchemaComplexType();
global::System.Xml.Schema.XmlSchemaSequence sequence = new global::System.Xml.Schema.XmlSchemaSequence();
DBSchema ds = new DBSchema();
global::System.Xml.Schema.XmlSchemaAny any1 = new global::System.Xml.Schema.XmlSchemaAny();
any1.Namespace = "http://www.w3.org/2001/XMLSchema";
any1.MinOccurs = new decimal(0);
any1.MaxOccurs = decimal.MaxValue;
any1.ProcessContents = global::System.Xml.Schema.XmlSchemaContentProcessing.Lax;
sequence.Items.Add(any1);
global::System.Xml.Schema.XmlSchemaAny any2 = new global::System.Xml.Schema.XmlSchemaAny();
any2.Namespace = "urn:schemas-microsoft-com:xml-diffgram-v1";
any2.MinOccurs = new decimal(1);
any2.ProcessContents = global::System.Xml.Schema.XmlSchemaContentProcessing.Lax;
sequence.Items.Add(any2);
global::System.Xml.Schema.XmlSchemaAttribute attribute1 = new global::System.Xml.Schema.XmlSchemaAttribute();
attribute1.Name = "namespace";
attribute1.FixedValue = ds.Namespace;
type.Attributes.Add(attribute1);
global::System.Xml.Schema.XmlSchemaAttribute attribute2 = new global::System.Xml.Schema.XmlSchemaAttribute();
attribute2.Name = "tableTypeName";
attribute2.FixedValue = "CustomersDataTable";
type.Attributes.Add(attribute2);
type.Particle = sequence;
global::System.Xml.Schema.XmlSchema dsSchema = ds.GetSchemaSerializable();
if (xs.Contains(dsSchema.TargetNamespace)) {
global::System.IO.MemoryStream s1 = new global::System.IO.MemoryStream();
global::System.IO.MemoryStream s2 = new global::System.IO.MemoryStream();
try {
global::System.Xml.Schema.XmlSchema schema = null;
dsSchema.Write(s1);
for (global::System.Collections.IEnumerator schemas = xs.Schemas(dsSchema.TargetNamespace).GetEnumerator(); schemas.MoveNext(); ) {
schema = ((global::System.Xml.Schema.XmlSchema)(schemas.Current));
s2.SetLength(0);
schema.Write(s2);
if ((s1.Length == s2.Length)) {
s1.Position = 0;
s2.Position = 0;
for (; ((s1.Position != s1.Length)
&& (s1.ReadByte() == s2.ReadByte())); ) {
;
}
if ((s1.Position == s1.Length)) {
return type;
}
}
}
}
finally {
if ((s1 != null)) {
s1.Close();
}
if ((s2 != null)) {
s2.Close();
}
}
}
xs.Add(dsSchema);
return type;
}
protected override void AfterSchemaComparision(DBSchema existingSchema, DBSchema targetSchema)
{
var i = 1;
foreach (var mTable in targetSchema.Tables)
{
var eTable = existingSchema.GetTable(mTable.Name);
if (eTable != null)
{
var recreate = false;
var toident = false;
var notexact = false;
var extracolumns = false;
#region Columns scan
foreach (var mColumn in mTable.Columns)
{
var eColumn = eTable.GetColumn(mColumn.Name);
if (eColumn == null)
{
extracolumns = true;
continue;
}
if (mTable.IsExactColumn(eColumn))
continue;
notexact = true;
if (eColumn.Type == ColumnType.BlobSubtypeImage && eColumn.Type == ColumnType.BlobSubtypeText &&
eColumn.Type == ColumnType.BlobSubtypeNText)
{}
//else if (mColumn.computedBy != null && eColumn.computedBy == null)
//{
//}
else if (mColumn.DefaultValue != eColumn.DefaultValue)
{}
else if (mColumn.AutoIncrement != eColumn.AutoIncrement)
{
recreate = true;
toident = mColumn.AutoIncrement;
}
else
recreate = true;
}
if (!recreate)
{
if (notexact)
{
foreach (var mColumn in mTable.Columns)
{
var eColumn = eTable.GetColumn(mColumn.Name);
if (eColumn == null)
continue;
if (mTable.IsExactColumn(eColumn))
continue;
if (mColumn.DefaultValue == eColumn.DefaultValue)
continue;
if (mColumn.DefaultValue == null)
{
var eKey = eTable.GetKey(ConstraintType.Default, eColumn.Name);
if (eKey != null)
AddDdlCommand(i,
eTable.Name + @"." + eKey.Name,
eKey.KeyType.ToString(),
MakeDdlDefaultDrop(eKey, eTable));
}
else if (eColumn.DefaultValue == null)
{
var mKey = mTable.GetKey(ConstraintType.Default, mColumn.Name);
if (mKey != null)
AddDdlCommand(i,
mTable.Name + @"." + mKey.Name,
mKey.KeyType.ToString(),
MakeDdlDefaultCreate(mKey, eTable));
}
else
{
var eKey = eTable.GetKey(ConstraintType.Default, eColumn.Name);
eKey.Source = mColumn.DefaultValue;
AddDdlCommand(i,
eTable.Name + @"." + eKey.Name,
eKey.KeyType.ToString(),
MakeDdlDefaultDrop(eKey, eTable));
AddDdlCommand(i,
eTable.Name + @"." + eKey.Name,
eKey.KeyType.ToString(),
MakeDdlDefaultCreate(eKey, eTable));
}
}
}
if (extracolumns)
{
foreach (var mColumn in mTable.Columns)
{
if (eTable.IsColumnExist(mColumn.Name))
continue;
if (mColumn.DefaultValue == null)
continue;
var mKey = mTable.GetKey(ConstraintType.Default, mColumn.Name);
if (mKey != null)
AddDdlCommand(i,
mTable.Name + @"." + mKey.Name,
mKey.KeyType.ToString(),
MakeDdlDefaultCreate(mKey, eTable));
}
}
}
foreach (var eColumn in eTable.Columns)
{
var mColumn = mTable.GetColumn(eColumn.Name);
if (mColumn != null)
continue;
var eKey = eTable.GetKey(ConstraintType.Default, eColumn.Name);
if (eKey != null)
AddDdlCommand(i,
eTable.Name + @"." + eKey.Name,
eKey.KeyType.ToString(),
MakeDdlDefaultDrop(eKey, eTable));
}
#endregion
#region Keys scan
//foreach(DBKey eKey in eTable.Keys)
//{
// if(eKey.KeyType == ConstraintType.Default)
// {
// DBKey mKey = mTable.GetKey(eKey.Name);
// if(mKey == null)
// {
// AddDdlCommand(i, eTable.Name + "." + eKey.Name, eKey.KeyType.ToString(), MakeDDLDefaultDrop(eKey, eTable.Name));
// }
// else if(!DBKey.CompareKeys(eKey, mKey))
// {
// AddDdlCommand(i, eTable.Name + "." + eKey.Name, eKey.KeyType.ToString(), MakeDDLDefaultDrop(eKey, eTable.Name));
// AddDdlCommand(i, eTable.Name + "." + eKey.Name, eKey.KeyType.ToString(), MakeDDLDefaultCreate(mKey, eTable.Name));
// }
// }
//}
//foreach(DBKey mKey in mTable.Keys)
//{
// if(mKey.KeyType == ConstraintType.Default)
// {
// DBKey eKey = eTable.GetKey(mKey.Name);
// if(eKey == null)
// {
// AddDdlCommand(i, eTable.Name + "." + eKey.Name, eKey.KeyType.ToString(), MakeDDLDefaultCreate(mKey, eTable.Name));
// }
// }
//}
#endregion
#region Recreate block
if (recreate)
{
DeleteDdlCommandsByFilter($@"Name Like '{eTable.Name}.%'");
foreach (var eTable1 in existingSchema.Tables)
foreach (var t in eTable1.Keys)
if (t.KeyType == ConstraintType.KeyForeign && t.RelTable == eTable.Name)
{
AddDdlCommand(i,
eTable1.Name + @"." + t.Name,
@"Recreate" + t.KeyType,
MakeDdlKeyDrop(t, eTable1));
AddDdlCommand(i,
eTable1.Name + @"." + t.Name,
@"Recreate" + t.KeyType,
MakeDdlKeyCreateByAlter(t, eTable1));
}
var tTable = mTable.Clone();
tTable.Name = @"Tmp_" + eTable.Name;
AddDdlCommand(i,
tTable.Name,
"RecreateTable",
MakeDdlTableCreate(tTable, false));
foreach (var tIndex in tTable.Indexes)
AddDdlCommand(i,
tTable.Name + @"." + tIndex.Name,
"RecreateIndex",
MakeDdlIndexCreate(tIndex, tTable));
foreach (var tKey in tTable.Keys)
{
if (tKey.KeyType == ConstraintType.Default)
continue;
AddDdlCommand(i,
tTable.Name + @"." + tKey.Name,
@"Recreate" + tKey.KeyType,
MakeDdlKeyCreateByAlter(tKey, tTable));
}
if (toident)
AddDdlCommand(i,
tTable.Name,
"RecreateInsert",
$@"SET IDENTITY_INSERT {tTable.Name} ON");
// Подготовить набор колонок данных.
var eColumns = tTable
.Columns
.Select(col =>
{
if (eTable.IsColumnExist(col.Name))
return MakeDdlElementName(col.Name);
if (col.DefaultValue == null)
{
if (!col.Nullable)
throw new DBSchemaException(@"Default value is required but not provided.");
return @"NULL";
}
return @"''" + col.DefaultValue + @"''";
})
.JoinStrings(@", ");
AddDdlCommand(i++,
tTable.Name,
"RecreateInsert",
$@"
IF EXISTS(SELECT * FROM {eTable.Name})
EXEC('INSERT INTO {tTable.Name} ({tTable
.ColumnsList(MakeDdlElementName)}) SELECT {eColumns} FROM {eTable.Name} WITH (HOLDLOCK TABLOCKX)')");
if (toident)
AddDdlCommand(i++,
tTable.Name,
"RecreateInsert",
$@"SET IDENTITY_INSERT {tTable.Name} OFF");
AddDdlCommand(i,
eTable.Name,
"RecreateTable",
MakeDdlTableDrop(eTable));
AddDdlCommand(i,
tTable.Name,
"RecreateRename",
$@"EXECUTE sp_rename N'{tTable.Name}', N'{eTable.Name}', 'OBJECT'");
}
#endregion
}
i++;
}
}
/// <summary>
/// Сравнение схемы с эталонной и выдача во внутреннюю хеш таблицу DDL комманд,
/// выполнение общих для всех движков действий.
/// </summary>
public void CompareDbsc(DBSchema mbDbsc, string targetConnStr, DataSet schemaDataset)
{
// TODO : эту кашу надо откомментировать и отрефакторить по человечески.
var existingSchema = LoadExistingSchema(targetConnStr);
var targetSchema = DbscCopyPrepare(mbDbsc);
existingSchema.Normalize();
targetSchema.Normalize();
_ddlCmdTable.Rows.Clear();
#region Tables
var i = 1;
foreach (var eTable in existingSchema.Tables)
{
if (!targetSchema.IsTableExist(eTable.Name))
{
HelperTableDrop(eTable, existingSchema.Tables, i);
i++;
}
}
i = 1;
foreach (var mTable in targetSchema.Tables)
{
var eTable = existingSchema.GetTable(mTable.Name);
if (eTable == null)
{
HelperTableCreate(mTable, i);
}
else if (mTable.GetDiffColumnsCount(eTable) == eTable.Columns.Length)
{
HelperTableDrop(eTable, existingSchema.Tables, i);
HelperTableCreate(mTable, i);
}
else
{
#region Column scan
foreach (var eColumn in eTable.Columns)
{
if (!mTable.IsColumnExist(eColumn.Name))
{
AddDdlCommand(i,
eTable.Name + @"." + eColumn.Name,
"Column",
MakeDdlColumnDrop(eColumn, eTable));
}
}
foreach (var mColumn in mTable.Columns)
{
var eColumn = eTable.GetColumn(mColumn.Name);
if (eColumn == null)
{
AddDdlCommand(i,
mTable.Name + @"." + mColumn.Name,
"Column",
MakeDdlColumnCreate(mColumn, mTable));
}
// to post processing in all workers AlterColumn
}
#endregion
#region Indexes scan
foreach (var eIndex in eTable.Indexes)
{
if (!mTable.IsIndexExist(eIndex.Name))
{
AddDdlCommand(i,
eTable.Name + @"." + eIndex.Name,
"Index",
MakeDdlIndexDrop(eIndex, eTable));
if (CheckIndexTypeForRecreate(eIndex))
{
foreach (var eTable1 in existingSchema.Tables)
{
var eKey1 = eTable1.GetKey(ConstraintType.KeyForeign, eTable.Name,
ParseColumnListIndexClear(eIndex.Columns));
if (eKey1 != null)
{
AddDdlCommand(i,
eTable.Name + @"." + eKey1.Name,
eKey1.KeyType.ToString(),
MakeDdlKeyDrop(eKey1, eTable1));
AddDdlCommand(i,
eTable.Name + @"." + eKey1.Name,
eKey1.KeyType.ToString(),
MakeDdlKeyCreateByAlter(eKey1, eTable1));
}
}
}
}
}
foreach (var mIndex in mTable.Indexes)
{
if (eTable.IsIndexExist(mIndex.Name))
{
var eIndex = eTable.GetIndex(mIndex.Name);
if (!eIndex.Equals(mIndex))
{
AddDdlCommand(i,
eTable.Name + @"." + eIndex.Name,
"Index",
MakeDdlIndexDrop(eIndex, eTable));
AddDdlCommand(i,
eTable.Name + @"." + mIndex,
"Index",
MakeDdlIndexCreate(mIndex, mTable));
if (CheckIndexTypeForRecreate(eIndex))
{
foreach (var eTable1 in existingSchema.Tables)
{
var eKey1 = eTable1.GetKey(ConstraintType.KeyForeign, eTable.Name,
ParseColumnListIndexClear(eIndex.Columns));
if (eKey1 != null)
{
AddDdlCommand(i,
eTable1.Name + @"." + eKey1.Name,
eKey1.KeyType.ToString(),
MakeDdlKeyDrop(eKey1, eTable1));
AddDdlCommand(i,
eTable1.Name + @"." + eKey1.Name,
eKey1.KeyType.ToString(),
MakeDdlKeyCreateByAlter(eKey1, eTable1));
}
}
}
}
}
else
{
AddDdlCommand(i,
mTable.Name + @"." + mIndex.Name,
"Index",
MakeDdlIndexCreate(mIndex, mTable));
}
}
#endregion
#region Keys scan
foreach (var eKey in eTable.Keys)
{
if (eKey.KeyType == ConstraintType.Default)
{
continue;
}
if (!mTable.IsKeyExist(eKey.Name))
{
AddDdlCommand(i,
eTable.Name + @"." + eKey.Name,
eKey.KeyType.ToString(),
MakeDdlKeyDrop(eKey, eTable));
if (CheckKeyTypeForRecreate(eKey))
{
foreach (var eTable1 in existingSchema.Tables)
{
var eKey1 = eTable1.GetKey(ConstraintType.KeyForeign, eTable.Name, eKey.Columns);
if (eKey1 != null)
{
AddDdlCommand(i,
eTable1.Name + @"." + eKey1.Name,
eKey1.KeyType.ToString(),
MakeDdlKeyDrop(eKey1, eTable1));
AddDdlCommand(i,
eTable1.Name + @"." + eKey1.Name,
eKey1.KeyType.ToString(),
MakeDdlKeyCreateByAlter(eKey1, eTable1));
}
}
}
}
}
foreach (var mKey in mTable.Keys)
{
if (mKey.KeyType == ConstraintType.Default)
{
continue;
}
if (eTable.IsKeyExist(mKey.Name))
{
var eKey = eTable.GetKey(mKey.Name);
if (!eKey.Equals(mKey))
{
AddDdlCommand(i,
eTable.Name + @"." + mKey.Name,
mKey.KeyType.ToString(),
MakeDdlKeyDrop(eKey, eTable));
AddDdlCommand(i,
eTable.Name + @"." + mKey.Name,
mKey.KeyType.ToString(),
MakeDdlKeyCreateByAlter(mKey, eTable));
if (CheckKeyTypeForRecreate(eKey))
{
foreach (var eTable1 in existingSchema.Tables)
{
var eKey1 = eTable1.GetKey(ConstraintType.KeyForeign, eTable.Name, eKey.Columns);
if (eKey1 != null)
{
AddDdlCommand(i,
eTable1.Name + @"." + eKey1.Name,
eKey1.KeyType.ToString(),
MakeDdlKeyDrop(eKey1, eTable1));
AddDdlCommand(i,
eTable1.Name + @"." + eKey1.Name,
eKey1.KeyType.ToString(),
MakeDdlKeyCreateByAlter(eKey1, eTable1));
}
}
}
}
}
else
{
AddDdlCommand(i,
eTable.Name + @"." + mKey.Name,
mKey.KeyType.ToString(),
MakeDdlKeyCreateByAlter(mKey, eTable));
}
}
#endregion
}
i++;
}
#endregion
AfterSchemaComparision(existingSchema, targetSchema);
_ddlCommands = Reparse();
_ddlCmdTable.Rows.Clear();
}
public async void Generate(DBConnection connection)
{
Assert.AreEqual(true, connection.CheckConnection(), $"Connection Fail!");
schema = DBSchema.Generate(GetType().Assembly, SchemaName);
Assert.IsNotNull(schema, "Attribute Generator Fail. On Schema");
Assert.IsNotNull(Employer.DBTable, "Attribute Generator Fail. On Employer Table");
Assert.IsNotNull(Position.DBTable, "Attribute Generator Fail. On Position Table");
var idColumn = Employer.DBTable.Columns["id"];
Assert.IsNotNull(idColumn, "Attribute Generator Fail. On Column Employer Id");
var positionColumn = Employer.DBTable.Columns["positionid"];
Assert.IsNotNull(positionColumn, "Attribute Generator Fail. On Column Employer Position");
Assert.IsNotNull(positionColumn.ReferenceTable, "Attribute Generator Fail. On Column Employer Position Reference");
schema.Connection = connection;
schema.DropDatabase();
schema.CreateDatabase();
var result = schema.GetTablesInfo(connection.Schema, EmployerTableName);
Assert.IsTrue(result.Count() == 1, "Generate Sql Table / Get Information Fail.");
result = schema.GetTablesInfo(connection.Schema, PositionTableName);
Assert.IsTrue(result.Count() == 1, "Generate Sql Table / Get Information Fail.");
//Insert
var employer = new Employer()
{
Identifier = $"{1:8}",
Lodar = true,
Age = 40,
Height = 180,
LongId = 120321312321L,
Weight = 123.12333F,
DWeight = 123.1233433424434D,
Salary = 231323.32M,
Name = "Ivan",
Access = new AccessValue(new[]
{
new AccessItem(AccessValue.Groups.First(i => i.Id == 1), AccessType.Read),
new AccessItem(AccessValue.Groups.First(i => i.Id == 2), AccessType.Admin),
new AccessItem(AccessValue.Groups.First(i => i.Id == 3), AccessType.Create)
})
};
Assert.AreEqual(employer.Type, EmployerType.Type2, "Default Value & Enum");
employer.GenerateId();
Assert.NotNull(employer.Id, "Id Generator Fail");
await employer.Save();
var qresult = schema.Connection.ExecuteQResult($"select * from {EmployerTableName}");
Assert.AreEqual(1, qresult.Values.Count, "Insert sql Fail");
Assert.AreEqual(employer.Id, qresult.Get(0, "id"), "Insert sql Fail Int");
Assert.AreEqual(employer.Identifier, qresult.Get(0, "identifier"), "Insert sql Fail String");
Assert.AreEqual((int?)employer.Type, qresult.Get(0, "typeid"), "Insert sql Fail Enum");
Assert.AreEqual(employer.Age, qresult.Get(0, "age"), "Insert sql Fail Byte");
Assert.AreEqual(employer.Height, qresult.Get(0, "height"), "Insert sql Fail Short");
Assert.AreEqual(employer.LongId, qresult.Get(0, "longid"), "Insert sql Fail Long");
Assert.AreEqual(employer.Weight, qresult.Get(0, "weight"), "Insert sql Fail Float");
Assert.AreEqual(employer.DWeight, qresult.Get(0, "dweight"), "Insert sql Fail Double");
Assert.AreEqual(employer.Salary, qresult.Get(0, "salary"), "Insert sql Fail Decimal");
var lodar = qresult.Get(0, "lodar").ToString();
Assert.IsTrue(lodar == "1" || lodar == "True", "Insert sql Fail Bool");
Assert.IsInstanceOf <byte[]>(qresult.Get(0, "group_access"), "Insert sql Fail Byte Array");
var accessValue = new AccessValue((byte[])qresult.Get(0, "group_access"));
Assert.AreEqual(3, accessValue.Items.Count(), "Insert sql Fail Byte Array");
Assert.AreEqual(true, accessValue.Items.ElementAt(0).Read, "Insert sql Fail Byte Array");
Assert.AreEqual(true, accessValue.Items.ElementAt(1).Admin, "Insert sql Fail Byte Array");
Assert.AreEqual(false, accessValue.Items.ElementAt(2).Delete, "Insert sql Fail Byte Array");
Employer.DBTable.Clear();
Assert.AreEqual(0, Employer.DBTable.Count, "Clear table Fail");
//Insert Several
Position.DBTable.Add(new Position()
{
Code = "1", Name = "First Position"
});
Position.DBTable.Add(new Position()
{
Code = "2", Name = "Second Position"
});
var position = new Position()
{
Id = 0, Code = "3", Name = "Group Position"
};
position.Attach();
var sposition = new Position()
{
Code = "4", Parent = position, Name = "Sub Group Position"
};
sposition.Attach();
//Select from internal Index
Position.DBTable.Add(new Position()
{
Code = "t1", Name = "Null Index"
});
Position.DBTable.Add(new Position()
{
Code = "t2", Name = "Null Index"
});
Position.DBTable.Add(new Position()
{
Code = "t3", Name = "Null Index"
});
var nullIds = Position.DBTable.Select(Position.DBTable.PrimaryKey, CompareType.Is, null).ToList();
Assert.AreEqual(6, nullIds.Count, "Select by null Fail");
await Position.DBTable.Save();
Position.DBTable.Clear();
var positions = Position.DBTable.Load();
Assert.AreEqual(7, positions.Count(), "Insert/Read several Fail");
//GetById
employer = Employer.DBTable.LoadById(1);
Assert.IsNotNull(employer, "GetById Fail");
position = Position.DBTable.LoadById(4);
Assert.IsNotNull(position, "GetById Fail");
//Update
employer.Position = position;
await employer.Save();
qresult = schema.Connection.ExecuteQResult($"select * from {EmployerTableName}");
Assert.AreEqual(4, qresult.Get(0, "positionid"), "Update sql Fail");
connection.ExecuteQuery(@"create table test_table(
id int primary key,
test_date date,
test_varchar varchar(512),
test_numeric numeric(20,10))");
result = schema.GetTablesInfo(connection.Schema, "test_table");
schema.GenerateTablesInfo(result);
var table = schema.Tables["test_table"] as DBTable <DBItem>;
Assert.IsNotNull(table, "DBInformation Load Fail");
table.Load().LastOrDefault();
for (int i = 0; i < 1000; i++)
{
var row = table.NewItem();
row["id"] = i;
row["test_date"] = DateTime.Now.AddDays(-i);
row["test_varchar"] = "string value " + i;
row["test_numeric"] = i / 1000M;
table.Add(row);
}
await table.Save();
table.Clear();
int c = 0;
foreach (var item in table.Load(string.Empty))
{
if (++c == 5)
{
break;
}
}
}
public virtual int Update(DBSchema dataSet) {
return this.Adapter.Update(dataSet, "Employees");
}
public virtual int Update(DBSchema dataSet) {
return this.Adapter.Update(dataSet, "OrderDetails");
}
public static global::System.Xml.Schema.XmlSchemaComplexType GetTypedDataSetSchema(global::System.Xml.Schema.XmlSchemaSet xs) {
DBSchema ds = new DBSchema();
global::System.Xml.Schema.XmlSchemaComplexType type = new global::System.Xml.Schema.XmlSchemaComplexType();
global::System.Xml.Schema.XmlSchemaSequence sequence = new global::System.Xml.Schema.XmlSchemaSequence();
global::System.Xml.Schema.XmlSchemaAny any = new global::System.Xml.Schema.XmlSchemaAny();
any.Namespace = ds.Namespace;
sequence.Items.Add(any);
type.Particle = sequence;
global::System.Xml.Schema.XmlSchema dsSchema = ds.GetSchemaSerializable();
if (xs.Contains(dsSchema.TargetNamespace)) {
global::System.IO.MemoryStream s1 = new global::System.IO.MemoryStream();
global::System.IO.MemoryStream s2 = new global::System.IO.MemoryStream();
try {
global::System.Xml.Schema.XmlSchema schema = null;
dsSchema.Write(s1);
for (global::System.Collections.IEnumerator schemas = xs.Schemas(dsSchema.TargetNamespace).GetEnumerator(); schemas.MoveNext(); ) {
schema = ((global::System.Xml.Schema.XmlSchema)(schemas.Current));
s2.SetLength(0);
schema.Write(s2);
if ((s1.Length == s2.Length)) {
s1.Position = 0;
s2.Position = 0;
for (; ((s1.Position != s1.Length)
&& (s1.ReadByte() == s2.ReadByte())); ) {
;
}
if ((s1.Position == s1.Length)) {
return type;
}
}
}
}
finally {
if ((s1 != null)) {
s1.Close();
}
if ((s2 != null)) {
s2.Close();
}
}
}
xs.Add(dsSchema);
return type;
}
