public static void Initialize(DatabaseManager dbManager) { Table table = new Table("_SysUris"); table.Columns.Add(new Column("Name", System.Data.SqlDbType.NVarChar, 128, false)); table.Columns.Add(new Column("Number", System.Data.SqlDbType.Int, nullable: false)); table.Columns.Add(new Column("Year", System.Data.SqlDbType.Int, nullable: false)); table.Constraints.Add(new UniqueConstraint(table.Name, "Name", "Number", "Year")); var sp = new StoredProcedure("_SysUris_GetNext", @" CREATE PROCEDURE [_SysUris_GetNext] @sequence nvarchar(128), @year int, @next int output AS BEGIN DECLARE @t TABLE([NUMBER] int); merge [_SysUris] T USING (select @sequence as [Name], @year as [Year]) S ON T.[Name] = S.[Name] AND T.[Year] = S.[Year] WHEN MATCHED THEN UPDATE SET [NUMBER] = [NUMBER]+1 WHEN NOT MATCHED THEN INSERT ([Name], [Number], [Year]) VALUES (S.[Name], 1, S.[Year]) OUTPUT inserted.[Number] into @t; select @next = [Number] from @t; END"); dbManager.Merge(new List<Table>() { table }, new List<StoredProcedure>() { sp }); }
public void Initialize() { using (var dbContext = _dbService.GetDatabaseContext(false)) { DatabaseManager mgr = new DatabaseManager(dbContext.Connection); var entityTbl = new Table("_History_EntityOperations") { FileGroup = "HISTORY" }; entityTbl.Columns.Add(new Column("Id", System.Data.SqlDbType.Int, identity: true, nullable: false)); entityTbl.Columns.Add(new Column("Entity", System.Data.SqlDbType.NVarChar, 128, false)); entityTbl.Columns.Add(new Column("EntityId", System.Data.SqlDbType.Int, nullable: false)); entityTbl.Columns.Add(new Column("Operation", System.Data.SqlDbType.TinyInt, nullable: false)); entityTbl.Columns.Add(new Column("ByUser", System.Data.SqlDbType.Int, nullable: false)); entityTbl.Columns.Add(new Column("OnDate", System.Data.SqlDbType.DateTime, nullable: false)); entityTbl.Constraints.Add(new Constraint("PK__History_EntityOperations", Constraint.PRIMARY_KEY, "Id")); var propChangeTbl = new Table("_History_PropertyChanges") { FileGroup = "HISTORY" }; propChangeTbl.Columns.Add(new Column("Id", System.Data.SqlDbType.Int, identity: true, nullable: false)); propChangeTbl.Columns.Add(new Column("OperationId", System.Data.SqlDbType.Int, nullable: false)); propChangeTbl.Columns.Add(new Column("Property", System.Data.SqlDbType.NVarChar, 128, nullable: false)); propChangeTbl.Columns.Add(new Column("Value", System.Data.SqlDbType.NVarChar, 1024)); propChangeTbl.Constraints.Add(new Constraint("PK__History_PropertyChanges", Constraint.PRIMARY_KEY, "Id")); propChangeTbl.Constraints.Add(new ForeignKeyConstraint("FK__History_PropertyChanges__History_EntityOperations", new string[] { "OperationId" }, "_History_EntityOperations", new string[] { "Id" })); var relChangeTbl = new Table("_History_RelationChanges") { FileGroup = "HISTORY" }; relChangeTbl.Columns.Add(new Column("Id", System.Data.SqlDbType.Int, identity: true, nullable: false)); relChangeTbl.Columns.Add(new Column("OperationId", System.Data.SqlDbType.Int, nullable: false)); relChangeTbl.Columns.Add(new Column("Entity", System.Data.SqlDbType.NVarChar, 128, nullable: false)); relChangeTbl.Columns.Add(new Column("Role", System.Data.SqlDbType.NVarChar, 128, false)); relChangeTbl.Columns.Add(new Column("EntityId", System.Data.SqlDbType.Int, nullable: false)); relChangeTbl.Columns.Add(new Column("RelationOperation", System.Data.SqlDbType.Int, nullable: false)); relChangeTbl.Constraints.Add(new Constraint("PK__History_RelationChanges", Constraint.PRIMARY_KEY, "Id")); relChangeTbl.Constraints.Add(new ForeignKeyConstraint("FK__History_RelationChanges__History_EntityOperations", new string[] { "OperationId" }, "_History_EntityOperations", new string[] { "Id" })); var relPropChangeTbl = new Table("_History_RelationPropertyChanges") { FileGroup = "HISTORY" }; relPropChangeTbl.Columns.Add(new Column("Id", System.Data.SqlDbType.Int, identity: true, nullable: false)); relPropChangeTbl.Columns.Add(new Column("RelationChangeId", System.Data.SqlDbType.Int, nullable: false)); relPropChangeTbl.Columns.Add(new Column("Property", System.Data.SqlDbType.NVarChar, 128, nullable: false)); relPropChangeTbl.Columns.Add(new Column("Value", System.Data.SqlDbType.NVarChar, 1024)); relPropChangeTbl.Constraints.Add(new Constraint("PK__History_RelationPropertyChanges", Constraint.PRIMARY_KEY, "Id")); relPropChangeTbl.Constraints.Add(new ForeignKeyConstraint("FK__History_RelationPropertyChanges__History_RelationChanges", new string[] { "RelationChangeId" }, "_History_RelationChanges", new string[] { "Id" })); mgr.Merge(new List<Table>() { entityTbl, propChangeTbl, relChangeTbl, relPropChangeTbl }, new List<StoredProcedure>() { new StoredProcedure(SqlConsts.StoredProcedures.LogOperation, GetContent("Sql.LogOperation.sql")), new StoredProcedure(SqlConsts.StoredProcedures.LogPropertyChange, GetContent("Sql.LogPropertyChange.sql")), new StoredProcedure(SqlConsts.StoredProcedures.LogRelationChange, GetContent("Sql.LogRelationChange.sql")), new StoredProcedure(SqlConsts.StoredProcedures.LogRelationPropertyChange, GetContent("Sql.LogRelationPropertyChange.sql")) });//TODO: HistoryModule - stored procedures dbContext.Complete(); } }
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(); }