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 conn = _dbService.GetSqlConnection())
{
var dbManager = new DatabaseManager(conn);
conn.Open();
SequenceProvider.Initialize(dbManager);
TemplateServiceImpl.Install(dbManager);
}
}
public static void Prepare(TestContext ctx)
{
ClearDatabase();
using (SqlConnection conn = getConnection())
{
conn.Open();
DatabaseManager mgr = new DatabaseManager(conn);
TemplateServiceImpl.Install(mgr);
}
}
private static void ClearDatabase()
{
using (SqlConnection conn = getConnection())
{
conn.Open();
DatabaseManager mgr = new DatabaseManager(conn);
mgr.LoadSchema();
while (mgr.Tables.Count > 0)
mgr.DropTable(mgr.Tables[0], true);
mgr.LoadSchema();
Assert.AreEqual(0, mgr.Tables.Count);
}
}
private static void ClearDatabase()
{
SqlConnectionStringBuilder b = new SqlConnectionStringBuilder();
b.DataSource = "localhost";
b.InitialCatalog = "TestingNbuLib";
b.IntegratedSecurity = true;
using (SqlConnection conn = new SqlConnection(b.ConnectionString))
{
conn.Open();
DatabaseManager mgr = new DatabaseManager(conn);
mgr.LoadSchema();
while (mgr.Tables.Count > 0)
mgr.DropTable(mgr.Tables[0], true);
mgr.LoadSchema();
Assert.AreEqual(0, mgr.Tables.Count);
}
}
public void Test_DomainModelService_Merge()
{
var dm = new DomainModel();
ModelBuilder ba = new ModelBuilder("Author");
ba.AddIdentity("Id");
ba.AddString("FirstName", 128);
ba.AddString("LastName", 128);
ba.AddComputed("FullName", "[LastName]+N', '+[FirstName]");
ba.AddBoolean("IsAlive", true);
ba.AddInteger("NumberOfAwards");
ba.AddDateTime("Born");
ba.AddDecimal("Rating");
ModelBuilder bb = new ModelBuilder("Book");
bb.AddIdentity("Id");
bb.AddString("Title", 256);
bb.AddString("ISBN", 20);
bb.AddDecimal("Price");
bb.AddEnum<Genre>("Genre", Genre.Fantasy);
bb.Rules.AddRequired("title");
bb.Rules.AddUnique("ISBN");
ba.AddRelationTo(bb.EntityModel, RelationType.OneToMany, "Author");
dm.Entities.Add(ba.EntityModel);
dm.Entities.Add(bb.EntityModel);
svc.Save(dm);
using (var conn = new TestDatabaseService().GetSqlConnection())
{
conn.Open();
DatabaseManager dbm = new DatabaseManager(conn);
dbm.LoadSchema();
var tableA = dbm.Tables.Find(t => t.Name == "Author");
Assert.IsNotNull(tableA);
var fnCol = tableA.Columns.Find(c => c.Name.Equals("FullName", StringComparison.InvariantCultureIgnoreCase));
Assert.IsNotNull(fnCol);
}
ModelBuilder bo = new ModelBuilder("Order");
bo.AddDecimal("Total");
bo.AddBoolean("Paid");
bo.AddDateTime("CreatedOn");
bo.AddDateTime("ShipmentDate");
bo.AddRelationTo(dm.Entities["book"], RelationType.ManyToMany, "Ordered");
dm.Entities.Add(bo.EntityModel);
ba.EntityModel.Properties.Remove(ba.EntityModel.Properties["IsAlive"]);
(ba.EntityModel.Properties["LastName"] as StringPropertyModel).Length = 256;
(ba.EntityModel.Properties["firstName"] as StringPropertyModel).Length = 256;
(bb.EntityModel.Properties["genre"] as EnumPropertyModel).DefaultValue = Genre.Horror;
ba.AddString("Nickname", 256);
svc.Merge(dm);
Assert.AreEqual(3, svc.Domain.Entities.Count);
//foreach (var em1 in dm.Entities)
//{
// var em2 = svc.Domain.Entities[em1.Name];
// Assert.AreEqual(em1.Properties.Count, em2.Properties.Count);
//}
//TODO-tests:finish domainmodelservice_merge test
//Computed columns are droped and recreated when upgrading the columns in their computed definition
using (var conn = new TestDatabaseService().GetSqlConnection())
{
conn.Open();
DatabaseManager dbm = new DatabaseManager(conn);
dbm.LoadSchema();
var tableA = dbm.Tables.Find(t => t.Name == "Author");
Assert.IsNotNull(tableA);
var fnCol = tableA.Columns.Find(c => c.Name.Equals("FullName", StringComparison.InvariantCultureIgnoreCase));
Assert.IsNotNull(fnCol);
}
}
public void Prepare()
{
SqlConnectionStringBuilder b = new SqlConnectionStringBuilder();
b.DataSource = "localhost";
b.InitialCatalog = "TestingNbuLib";
b.IntegratedSecurity = true;
using (SqlConnection conn = new SqlConnection(b.ConnectionString))
{
conn.Open();
DatabaseManager mgr = new DatabaseManager(conn);
mgr.LoadSchema();
while (mgr.Tables.Count > 0)
mgr.DropTable(mgr.Tables[0], true);
mgr.LoadSchema();
Assert.AreEqual(0, mgr.Tables.Count);
}
svc = new DomainModelService(new TestDatabaseService(), new IDomainChangeListener[] { new EntityRepositoryDomainListener() });
svc.Save(new DomainModel());
}
private static void CreateDomain()
{
var dm = new DomainModel();
ModelBuilder ba = new ModelBuilder("Author");
ba.AddIdentity("Id");
ba.AddString("FirstName", 128);
ba.AddString("LastName", 128);
ba.AddBoolean("IsAlive", true);
ba.AddInteger("NumberOfAwards");
ba.AddDateTime("Born");
ba.AddDecimal("Rating");
ModelBuilder bb = new ModelBuilder("Book");
bb.AddIdentity("Id");
bb.AddString("Title", 256);
bb.AddString("ISBN", 20);
bb.AddDecimal("Price");
bb.AddEnum<Genre>("Genre", Genre.Mistery);
bb.Rules.AddRequired("title");
bb.Rules.AddUnique("ISBN");
var rel = ba.AddRelationTo(bb.EntityModel, RelationType.OneToMany, "Author");
var br = new ModelBuilder(rel);
br.AddDateTime("WrittenOn");
dm.Entities.Add(ba.EntityModel);
dm.Entities.Add(bb.EntityModel);
ModelBuilder bo = new ModelBuilder("Order");
bo.AddDecimal("Total");
bo.AddBoolean("Paid");
bo.AddDateTime("CreatedOn");
bo.AddDateTime("ShipmentDate");
bo.AddRelationTo(dm.Entities["book"], RelationType.ManyToMany, "Ordered");
bo.AddUri("Number", "BO");
dm.Entities.Add(bo.EntityModel);
dms.Save(dm);
using (var conn = new TestDatabaseService().GetSqlConnection())
{
conn.Open();
var dbManager = new DatabaseManager(conn);
SequenceProvider.Initialize(dbManager);
}
}
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();
}
