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