public static DBschema GetSchema()
{
DBschema schema = new DBschema();
List<DB_SQL_Compact_tables> tables_list = ViewModelLocator.Instance.DataBase.Database.SqlQuery<DB_SQL_Compact_tables>("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES").ToList();
List<DB_SQL_Compact_field> fields_list = ViewModelLocator.Instance.DataBase.Database.SqlQuery<DB_SQL_Compact_field>("SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS").ToList();
foreach(DB_SQL_Compact_tables tbl in tables_list)
{
if (tbl.TABLE_NAME == "__MigrationHistory") continue;
Table table = new Table() { name = tbl.TABLE_NAME };
schema.Tables.Add(table.name,table);
var fields = fields_list.Where(x=>x.TABLE_NAME == tbl.TABLE_NAME);
//List<DB_SQL_Compact_field> fields_list = ViewModelLocator.Instance.DataBase.Database.SqlQuery<DB_SQL_Compact_field>("SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS where TABLE_Name = '"+tbl.TABLE_NAME+"'").ToList();
foreach (DB_SQL_Compact_field fld in fields)
{
Field field = new Field() { name = fld.COLUMN_NAME,type = DBschema.GetDBType(fld.DATA_TYPE),IsNullable = fld.IS_NULLABLE == "YES"};
table.Fields.Add(field.name,field);
}
}
// Primary keys
List<Key> Keys = ViewModelLocator.Instance.DataBase.Database.SqlQuery<Key>(SQLqueries.PrimaryKeys).ToList();
foreach (Key key in Keys)
{
Table tbl = schema.GetTableByName(key.TABLE_NAME);
Field fld = tbl.GetFieldByName(key.COLUMN_NAME);
fld.IsItPrimaryKey = true;
tbl.primary_key_name = fld.name;
}
// Foreign key
List<ForeignKey> ForeignKeys = ViewModelLocator.Instance.DataBase.Database.SqlQuery<ForeignKey>(SQLqueries.ForieghKeys).ToList();
foreach (ForeignKey fkey in ForeignKeys)
{
Table tbl = schema.GetTableByName(fkey.TABLE_NAME);
Field fld = tbl.GetFieldByName(fkey.COLUMN_NAME);
Table primary_table = schema.GetTableByName(fkey.PRIMARY_TABLE_NAME.Replace("PK_dbo.",""));
//schema.ForeignKey.Add(new DB.ForeignKey() { Table = tbl,PrimaryTable=primary_table,field=fld });
schema.ForeignKey.Add(
new DB.ForeignKey() {
number = schema.ForeignKey.Count + 1,
Table_str = fkey.TABLE_NAME,
PrimaryTable_str =fkey.PRIMARY_TABLE_NAME.Replace("PK_dbo.",""),
field_str = fkey.COLUMN_NAME
,Table = tbl,field=fld,PrimaryTable = primary_table
}
);
}
return schema;
}
public static DBschema BuildSchema()
{
DBschema schema = new DBschema();
ObjectContext octx = ((IObjectContextAdapter)EFDbContext.DataBase).ObjectContext;
MetadataWorkspace mw = octx.MetadataWorkspace;
var OSpace = mw.GetItemCollection(DataSpace.OSpace).GetItems<EntityType>();
IReadOnlyCollection<EntityContainer> SSpace = mw.GetItemCollection(DataSpace.SSpace).GetItems<EntityContainer>();
EntityContainer cont = SSpace.First();
foreach(EntitySet es in cont.EntitySets){
Table table = new Table(){ Name = es.Table};
schema.Tables.Add(table.Name,table);
// classname
IEnumerable<EntityType> etype = OSpace.Where(x => x.Name == es.Name);
if(etype.Count() > 1)
{
throw new Exception("Class name conflict with "+es.Name);
}
table.ClrName = etype.First().FullName;
Type type = Type.GetType(table.ClrName);
if(type == null)
{
table.ClrName = table.ClrName.Replace("." + es.Name, "+" + es.Name);
type = Type.GetType(table.ClrName );
if(type == null) throw new Exception("Can't found type of class "+etype.First().FullName);
}
table.PrivateTable = type.CustomAttributes.Any(x => x.AttributeType.Name == "PrivateData");
// Properties
foreach(EdmProperty edmp in es.ElementType.DeclaredProperties)
{
Field field = new Field();
field.Name = edmp.Name;
field.IsNullable = edmp.Nullable;
switch (((PrimitiveType)edmp.UnderlyingPrimitiveType.BaseType).PrimitiveTypeKind)
{
case PrimitiveTypeKind.Int16:
case PrimitiveTypeKind.Int32:
case PrimitiveTypeKind.Int64:
case PrimitiveTypeKind.Byte:
field.type = DBTypes.Integer;
break;
case PrimitiveTypeKind.Boolean:
field.type = DBTypes.Boolean;
break;
case PrimitiveTypeKind.Guid:
field.type = DBTypes.Guid;
break;
case PrimitiveTypeKind.Double:
case PrimitiveTypeKind.Single:
field.type = DBTypes.Double;
break;
case PrimitiveTypeKind.String:
field.type = DBTypes.String;
break;
case PrimitiveTypeKind.DateTime:
field.type = DBTypes.Datetime;
break;
default:
throw new NotImplementedException();
}
PropertyInfo pi = GetPropertyInfo(type,field.Name);
if(pi == null) throw new NotImplementedException();
field.ClrName = pi.Name;
field.PrivateField = table.PrivateTable ? true : pi.CustomAttributes.Any(x => x.AttributeType.Name == "PrivateData");
table.Fields.Add(field.Name,field);
}
}
// foreign keys
foreach (var elm in SSpace.First().BaseEntitySets)
{
// if is it a primary key skip it
if (elm.BuiltInTypeKind == BuiltInTypeKind.EntitySet) continue;
if (elm.BuiltInTypeKind == BuiltInTypeKind.AssociationSet)
{
ReferentialConstraint Constraint = ((AssociationType)elm.ElementType).Constraint;
string tablename = GetTableNameByClrName(cont.EntitySets, Constraint.ToRole.Name);
Table tbl = schema.GetTableByName(tablename);
tablename = GetTableNameByClrName(cont.EntitySets, Constraint.FromRole.Name);
Table primary_table = schema.GetTableByName(tablename);
Field fld = tbl.GetFieldByName(Constraint.ToProperties.First().Name);
if (Constraint.ToRole.RelationshipMultiplicity != RelationshipMultiplicity.Many) throw new Exception();
if (Constraint.ToProperties.Count() != 1) throw new Exception();
schema.ForeignKey.Add(
new ForeignKey() {
number = schema.ForeignKey.Count + 1,
Table_str = tbl.Name,
PrimaryTable_str = primary_table.Name,
field_str = fld.Name,
Table = tbl, field = fld, PrimaryTable = primary_table
}
);
}
}
return schema;
}
private string GetFakeValue(DBConnect dbconnect,Table tbl, Field field,short version_of_data)
{
switch (field.type)
{
case DBTypes.String:
return "";
case DBTypes.Datetime:
return DateTime.Now.ToString(CultureInfo.InvariantCulture);
case DBTypes.Integer:
case DBTypes.Boolean:
case DBTypes.Double:
return "0";
case DBTypes.Guid:
ForeignKey key = schema.ForeignKey.FirstOrDefault(x => x.Table_str == tbl.Name && x.field_str == field.Name);
if (key == null) throw new ArgumentNullException(nameof(key));
Guid id = Guid.Empty;
var felm = FakeElements.FirstOrDefault(x=> x.tablename == key.PrimaryTable_str);
if (felm != null)
{
id = felm.guid;
}
else
{
string flds = "", values="";
foreach (var fld in key.PrimaryTable.Fields)
{
if (fld.Value.IsNullable) continue;
string value = "";
if (fld.Key == "id")
{
id = Guid.NewGuid();
value = id.ToString();
} else
{
value = GetFakeValue(dbconnect, key.PrimaryTable, fld.Value, version_of_data);
}
flds += (flds == "" ? "":",")+"["+fld.Key+"]"+Environment.NewLine;
values += (values==""?"":",") + FormatValueForSQL(fld.Value,value,version_of_data);
}
string sql = "INSERT INTO ["+key.PrimaryTable_str+"] ("+flds+" ) VALUES ("+values+")";
dbconnect.ExecuteSQL(sql);
FakeElements.Add(new Ref {guid=id,tablename = key.PrimaryTable_str});
}
return id.ToString();
default:
throw new NotImplementedException();
}
}