public void Ensure(IEnumerable <EntityDefinition> definitions, IEnumerable <EntityRelation> relations)
{
foreach (var def in definitions)
{
EntityDefinition existing = Registry.GetDefintionByName(def.Name);
if (existing != null)
{
//Upgrade
foreach (var prop in def.Properties)
{
var existingProp = existing.Properties.Find(p => p.Name == prop.Name);
if (existingProp == null) //added
{
existing.Properties.Add(prop);
}
else //change
{
existing.Properties.Remove(existingProp);
existing.Properties.Add(prop);
}
}
}
else
{
//Create
//TODO: Think about adding the id prop to definitions
//var idProp = def.Properties.Find(prop => prop.Name.Equals("Id", StringComparison.InvariantCultureIgnoreCase));
//if (idProp == null)
//{
// idProp = new PropertyDefinition("Id", PropertyTypes.Integer, nullable: false);
//}
Registry.AddDefinition(def);
}
}
if (relations != null)
{
foreach (var relation in relations)
{
var existing = Registry.GetRelation(relation.LeftEntity, relation.Role);
if (existing != null && relation.Type != existing.Type)
{
throw new NotImplementedException("Upgrade of relations is not implemented yet!");
}
else if (existing == null) //new relation
{
Registry.AddRelation(relation);
}
}
}
List <StoredProcedure> procs = new List <StoredProcedure>();
var tables = new List <Table>();
foreach (var def in Registry.GetAllDefinitions())
{
var table = new Table(def.Name);
foreach (var prop in def.Properties)
{
var length = 0;
string computedDefinition = null;
if (prop is ComputedProperty)
{
computedDefinition = ((ComputedProperty)prop).Format;
}
if (prop is StringProperty)
{
length = ((StringProperty)prop).Length;
}
table.Columns.Add(new Column(prop.Name, EntityValueMapper.GetDbType(prop.Type), length, prop.Nullable, computed: computedDefinition));
if (prop.Unique)
{
table.Constraints.Add(new Sql.Constraint(string.Format("UK_{0}_{1}", def.Name, prop.Name), Sql.Constraint.UNIQUE, prop.Name));
}
//if(prop.HasDefault)
//{
// string defValue = null;
// if (prop.Type == PropertyTypes.Boolean)
// {
// defValue = bool.Parse(prop.DefaultValue) ? "1" : "0";
// }
// else
// throw new NotImplementedException(); //TODO: Default constraint!
// //table.Constraints.Add(new Sql.DefaultConstraint(string.Format("DFLT_{0}_{1}", def.Name, prop.Name), prop.Name, defValue));
//}
}
var idCol = new Column("Id", EntityValueMapper.GetDbType(PropertyTypes.Integer), nullable: false, identity: true);
table.Columns.Add(idCol);
table.Constraints.Add(new Sql.Constraint(string.Format("PK_{0}", def.Name), Sql.Constraint.PRIMARY_KEY, idCol.Name));
tables.Add(table);
procs.AddRange(BuildProcs(def));
}
foreach (var relation in Registry.GetAllRelations())
{
var table = new Table(string.Format("{0}_{1}_{2}", relation.LeftEntity, relation.RightEntity, relation.Role));
table.Columns.Add(new Column("Id", SqlDbType.Int, nullable: false, identity: true));
table.Columns.Add(new Column("LID", SqlDbType.Int, nullable: false));
table.Columns.Add(new Column("RID", SqlDbType.Int, nullable: false));
switch (relation.Type)
{
case RelationTypes.OneToOne:
table.Constraints.Add(new Sql.Constraint(string.Format("PK_{0}", table.Name), Sql.Constraint.PRIMARY_KEY, "LID", "RID"));
break;
case RelationTypes.OneToMany:
table.Constraints.Add(new Sql.Constraint(string.Format("PK_{0}", table.Name), Sql.Constraint.PRIMARY_KEY, "RID"));
break;
case RelationTypes.ManyToOne:
table.Constraints.Add(new Sql.Constraint(string.Format("PK_{0}", table.Name), Sql.Constraint.PRIMARY_KEY, "LID"));
break;
case RelationTypes.ManyToMany:
table.Constraints.Add(new Sql.Constraint(string.Format("PK_{0}", table.Name), Sql.Constraint.PRIMARY_KEY, "Id"));
break;
default:
throw new NotImplementedException("Unknown RelationType.");
}
table.Constraints.Add(new Sql.ForeignKeyConstraint(string.Format("FK_{0}_{1}", table.Name, relation.LeftEntity), new string[] { "LID" }, relation.LeftEntity, new string[] { "Id" }));
table.Constraints.Add(new Sql.ForeignKeyConstraint(string.Format("FK_{0}_{1}", table.Name, relation.RightEntity), new string[] { "RID" }, relation.RightEntity, new string[] { "Id" }));
tables.Add(table);
}
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EntityDB"].ConnectionString))
{
conn.Open();
//var tran = conn.BeginTransaction();
DatabaseManager du = new DatabaseManager(conn);
du.Merge(tables, procs);
//tran.Commit();
}
SaveRegistry();
}
private IEnumerable <StoredProcedure> BuildProcs(EntityDefinition def)
{
#region Build Create Proc
StringBuilder create = new StringBuilder();
create.AppendFormat("CREATE PROC [{0}_Create] \n", def.Name);
create.Append("@Id INT OUTPUT ");
def.Properties.ForEach(prop =>
{
if (prop.Type == PropertyTypes.Computed)
{
return;
}
var size = prop is StringProperty ? ((StringProperty)prop).Length : -1;
if (prop is StringProperty)
{
create.AppendFormat("\n,@{0} {1}({2}) ", prop.Name, SqlDbType.NVarChar, ((StringProperty)prop).Length);
}
else
{
create.AppendFormat("\n,@{0} {1} ", prop.Name, EntityValueMapper.GetDbType(prop.Type));
}
//parameters[idx++] = new ParameterInfo(prop.Name, prop.Type, size);
});
create.Append("\nAS\n");
create.AppendFormat("INSERT INTO [{0}] \n(", def.Name);
for (int i = 0; i < def.Properties.Count; i++)
{
if (def.Properties[i].Type != PropertyTypes.Computed)
{
create.AppendFormat("[{0}]", def.Properties[i].Name);
}
if (i < def.Properties.Count - 1 && def.Properties[i + 1].Type != PropertyTypes.Computed)
{
create.Append(",\n");
}
}
create.Append(") VALUES \n (");
for (int i = 0; i < def.Properties.Count; i++)
{
//Skip computed since they are not inserted
if (def.Properties[i].Type != PropertyTypes.Computed)
{
create.AppendFormat("@{0}", def.Properties[i].Name);
}
if (i < def.Properties.Count - 1 && def.Properties[i + 1].Type != PropertyTypes.Computed)
{
create.Append(",\n");
}
}
create.Append(")\n");
create.AppendFormat("\n SET @Id = SCOPE_IDENTITY();");
#endregion
#region Build Update Proc
StringBuilder update = new StringBuilder();
update.AppendFormat("CREATE PROC [{0}_Update] \n", def.Name);
update.Append("@Id INT ");
def.Properties.ForEach(prop =>
{
if (prop.Type == PropertyTypes.Computed)
{
return;
}
var size = prop is StringProperty ? ((StringProperty)prop).Length : -1;
if (prop is StringProperty)
{
update.AppendFormat("\n,@{0} {1}({2}) ", prop.Name, SqlDbType.NVarChar, ((StringProperty)prop).Length);
}
else
{
update.AppendFormat("\n,@{0} {1} ", prop.Name, EntityValueMapper.GetDbType(prop.Type));
}
});
update.Append("\n AS \n");
update.AppendFormat("UPDATE [{0}] \nSET \n", def.Name);
for (int i = 0; i < def.Properties.Count; i++)
{
if (def.Properties[i].Type != PropertyTypes.Computed)
{
update.AppendFormat("[{0}] = @{0}", def.Properties[i].Name);
}
//TODO: ugly code
if (i < def.Properties.Count - 1 && def.Properties[i + 1].Type != PropertyTypes.Computed)
{
update.Append(",\n");
}
}
update.AppendFormat("\nWHERE [Id] = @Id \n");
#endregion
#region Build Read Proc
StringBuilder read = new StringBuilder();
read.AppendFormat("CREATE PROC [{0}_Read] \n", def.Name);
read.Append("@Id INT ");
read.Append("\n AS \n");
read.AppendFormat("SELECT * FROM [{0}] WHERE [Id] = @Id", def.Name);
#endregion
#region Build Delete Proc
StringBuilder delete = new StringBuilder();
delete.AppendFormat("CREATE PROC [{0}_Delete] \n", def.Name);
delete.Append("@Id INT ");
delete.Append("\n AS \n");
delete.AppendFormat("DELETE FROM [{0}] WHERE Id = @Id", def.Name);
#endregion
return(new StoredProcedure[] {
new StoredProcedure(string.Format("{0}_Create", def.Name), create.ToString()),
new StoredProcedure(string.Format("{0}_Read", def.Name), read.ToString()),
new StoredProcedure(string.Format("{0}_Update", def.Name), update.ToString()),
new StoredProcedure(string.Format("{0}_Delete", def.Name), delete.ToString())
});
}
