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