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 Add(ForeignKeyConstraint fk, Table owner)
{
Owners.Add(owner);
Constraints.Add(fk);
}
private void CreateUniqueIndex(Table table, Constraint uc)
{
var nullableColumns = table.Columns.Where(c => uc.Columns.Contains(c.Name, StringComparer.InvariantCultureIgnoreCase) && c.IsNullable);
if (nullableColumns.Count() > 0)
{
StringBuilder sql = new StringBuilder("create unique index ");
var allCols = uc.Columns.ToArray();
Array.Sort(allCols);
sql.AppendFormat(uc.Name);
sql.AppendFormat(" ON [{0}] (", table.Name);
var it = allCols.GetEnumerator();
bool hasNext = it.MoveNext();
while (hasNext)
{
sql.AppendFormat("[{0}]", it.Current);
hasNext = it.MoveNext();
if (hasNext)
sql.Append(",");
}
sql.AppendFormat(") WHERE ");
var it2 = nullableColumns.GetEnumerator();
hasNext = it2.MoveNext();
while (hasNext)
{
sql.AppendFormat("[{0}] IS NOT NULL", it2.Current.Name);
hasNext = it2.MoveNext();
if (hasNext)
sql.Append(" AND ");
}
var addConstraint = conn.CreateCommand();
addConstraint.CommandText = sql.ToString();
addConstraint.ExecuteNonQuery();
}
}
private void DropConstraint(Table table, Constraint cnst)
{
if (cnst.Type == Constraint.UNIQUE)
{
var dropCmd = conn.CreateCommand();
dropCmd.CommandText = string.Format("ALTER TABLE [{0}] DROP CONSTRAINT [{1}]", table.Name, cnst.Name);
dropCmd.ExecuteNonQuery();
table.Constraints.Remove(cnst);
}
else if (cnst.Type == Constraint.FOREIGN_KEY)
{
var dropCmd = conn.CreateCommand();
dropCmd.CommandText = string.Format("ALTER TABLE [{0}] DROP CONSTRAINT [{1}]", table.Name, cnst.Name);
dropCmd.ExecuteNonQuery();
table.Constraints.Remove(cnst);
}
else
throw new NotImplementedException(string.Format("Dropping constraint of type {0} not implemented.", cnst.Type));
}
private void AddConstraint(Table table, Constraint cnst)
{
if (cnst.Type.Equals(Constraint.UNIQUE, StringComparison.InvariantCultureIgnoreCase))
{
var nullableColumns = table.Columns.Where(c => cnst.Columns.Contains(c.Name, StringComparer.InvariantCultureIgnoreCase) && c.IsNullable);
if (nullableColumns.Count() > 0)
{
CreateUniqueIndex(table, cnst as UniqueConstraint);
}
else
{
var addConstraint = conn.CreateCommand();
addConstraint.CommandText = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT [{1}] UNIQUE ({2})",
table.Name,
cnst.Name,
string.Join(",", cnst.Columns.Select(col => string.Format("[{0}]", col)))
);
addConstraint.ExecuteNonQuery();
}
table.Constraints.Add(cnst);
}
else if (cnst.Type.Equals(Constraint.FOREIGN_KEY, StringComparison.InvariantCultureIgnoreCase))
{
var fk = (ForeignKeyConstraint)cnst;
var addFKConstraint = conn.CreateCommand();
addFKConstraint.CommandText = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT [{1}] FOREIGN KEY ({2}) REFERENCES [{3}]({4})",
table.Name,
fk.Name,
string.Join(",", fk.Columns.Select(col => string.Format("[{0}]", col))),
fk.RefTable,
string.Join(",", fk.RefColumns.Select(refCol => string.Format("[{0}]", refCol)))
);
addFKConstraint.ExecuteNonQuery();
table.Constraints.Add(cnst);
}
else if (cnst.Type.Equals(Constraint.DEFAULT, StringComparison.InvariantCultureIgnoreCase))
{
var defCnstr = cnst as DefaultConstraint;
var addDefaultConstraint = conn.CreateCommand();
addDefaultConstraint.CommandText = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT DFLT_{0}_{1} DEFAULT ({2}) FOR [{1}]",
table.Name,
defCnstr.Column,
defCnstr.Value);
addDefaultConstraint.ExecuteNonQuery();
table.Constraints.Add(defCnstr);
}
else
throw new NotImplementedException(string.Format("Adding constraint of type {0} not implemented.", cnst.Type));
}
public void CreateTable(Table table)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat("CREATE TABLE [{0}] (\n", table.Name);
for (int i = 0; i < table.Columns.Count; i++)
{
var column = table.Columns[i];
if (column.IsComputed)
sql.AppendFormat(" [{0}] AS ({1})", column.Name, column.ComputedDefinition);
else if (column.DataType.Equals(SqlDbType.NVarChar.ToString(), StringComparison.InvariantCultureIgnoreCase))
sql.AppendFormat(" [{0}] {1}({2}) {3} {4}", column.Name, column.DataType, column.Length, column.IsNullable ? "NULL" : "NOT NULL", column.Identity ? "IDENTITY" : "");
else if (column.DataType.Equals(SqlDbType.Decimal.ToString(), StringComparison.InvariantCultureIgnoreCase))
sql.AppendFormat(" [{0}] {1} (28, 9) {2}", column.Name, column.DataType, column.IsNullable ? "NULL" : "NOT NULL");
else
sql.AppendFormat(" [{0}] {1} {2} {3}", column.Name, column.DataType, column.IsNullable ? "NULL" : "NOT NULL", column.Identity ? "IDENTITY" : "");
if (i < table.Columns.Count - 1)
sql.AppendFormat(",\n");
}
if (table.Constraints.Count > 0)
sql.AppendFormat(",\n");
List<Constraint> addAfterTableCreation = new List<Constraint>();
for (int i = 0; i < table.Constraints.Count; i++)
{
var cnst = table.Constraints[i];
if (cnst.Type.Equals(Constraint.UNIQUE, StringComparison.InvariantCultureIgnoreCase))
{
if (table.Columns.Count(c => cnst.Columns.Contains(c.Name, StringComparer.InvariantCultureIgnoreCase) && c.IsNullable) > 0)
{
addAfterTableCreation.Add(cnst);
continue;
}
else
{
sql.AppendFormat(" CONSTRAINT [{0}] UNIQUE ({1})",
cnst.Name,
string.Join(",", cnst.Columns.Select(col => string.Format("[{0}]", col)))
);
}
}
else if (cnst.Type.Equals(Constraint.FOREIGN_KEY, StringComparison.InvariantCultureIgnoreCase))
{
var fk = (ForeignKeyConstraint)cnst;
sql.AppendFormat(" CONSTRAINT [{0}] FOREIGN KEY ({1}) REFERENCES [{2}]({3})",
fk.Name,
string.Join(",", fk.Columns.Select(col => string.Format("[{0}]", col))),
fk.RefTable,
string.Join(",", fk.RefColumns.Select(refCol => string.Format("[{0}]", refCol)))
);
}
else if (cnst.Type.Equals(Constraint.PRIMARY_KEY, StringComparison.InvariantCultureIgnoreCase))
{
sql.AppendFormat(" CONSTRAINT [{0}] PRIMARY KEY ({1})",
cnst.Name,
string.Join(",", cnst.Columns.Select(col => string.Format("[{0}]", col)))
);
}
else if (cnst.Type.Equals(Constraint.DEFAULT, StringComparison.InvariantCultureIgnoreCase))
{
addAfterTableCreation.Add(cnst);
continue;
}
else
throw new NotImplementedException(string.Format("Creating constraint of type {0} not implemented.", cnst.Type));
if (i < table.Constraints.Count - 1)
sql.Append(",\n");
}
sql.Append("\n)");
if (!string.IsNullOrEmpty(table.FileGroup))
sql.AppendFormat(" ON [{0}]", table.FileGroup);
var createTbl = conn.CreateCommand();
createTbl.CommandText = sql.ToString();
createTbl.ExecuteNonQuery();
addAfterTableCreation.ForEach(cnst => { AddConstraint(table, cnst); });
}
public void MergeTable(Table exTable, Table table)
{
#region upgrade columns
table.Columns.ForEach(col =>
{
var exColumn = exTable.GetColumnByName(col.Name);
if (exColumn != null)
{
if (exColumn.IsComputed && col.IsComputed && exColumn.ComputedDefinition != col.ComputedDefinition)
{
DropColumn(table, col);
var addColumn = conn.CreateCommand();
addColumn.CommandText = string.Format("ALTER TABLE [{0}] ADD [{1}] AS ({2})", table.Name, col.Name, col.ComputedDefinition);
addColumn.ExecuteNonQuery();
}
else if (exColumn.IsNullable != col.IsNullable
|| exColumn.Length != col.Length
|| !exColumn.DataType.Equals(col.DataType.ToString(), StringComparison.InvariantCultureIgnoreCase))
{
//check for computed dependencies
var depComputeds = exTable.Columns.Where(c => c.IsComputed && c.ComputedDefinition.ToLower().Contains(string.Format("[{0}]", col.Name.ToLower())));
foreach (var depCol in depComputeds)
{
DropColumn(exTable, depCol);
}
if (exColumn.Length <= col.Length)
{
var alterColumn = conn.CreateCommand();
if (col.DataType.Equals(SqlDbType.NVarChar.ToString(), StringComparison.InvariantCultureIgnoreCase))
alterColumn.CommandText = string.Format("ALTER TABLE [{0}] ALTER COLUMN [{1}] {2}({3}) {4}", table.Name, col.Name, col.DataType, col.Length, col.IsNullable ? "NULL" : "NOT NULL");
else
alterColumn.CommandText = string.Format("ALTER TABLE [{0}] ALTER COLUMN [{1}] {2} {3}", table.Name, col.Name, col.DataType, col.IsNullable ? "NULL" : "NOT NULL");
alterColumn.ExecuteNonQuery();
}
else
{
var constraints = Tables.SelectMany(t => t.Constraints).Where(c => c.Columns.Contains(col.Name));
foreach (var cnst in constraints)
{
DropConstraint(exTable, cnst);
}
DropColumn(table, col);
var addColumn = conn.CreateCommand();
if (col.DataType.Equals(SqlDbType.NVarChar.ToString(), StringComparison.InvariantCultureIgnoreCase))
addColumn.CommandText = string.Format("ALTER TABLE [{0}] ADD [{1}] {2}({3}) {4}", table.Name, col.Name, col.DataType, col.Length, col.IsNullable ? "NULL" : "NOT NULL");
else
addColumn.CommandText = string.Format("ALTER TABLE [{0}] ADD [{1}] {2} {3}", table.Name, col.Name, col.DataType, col.IsNullable ? "NULL" : "NOT NULL");
AddColumn(table, col);
foreach (var cnst in constraints)
{
AddConstraint(exTable, cnst);
}
}
foreach (var depCol in depComputeds)
{
AddColumn(exTable, depCol);
}
}
}
else
{
//TODO: Default value of not nullable columns
AddColumn(table, col);
}
});
#endregion
table.Constraints.ForEach(cnst =>
{
var exCnst = exTable.GetConstraintByName(cnst.Name);
if (exCnst != null)
{
if (cnst.Type != exCnst.Type)
throw new NotSupportedException("Cannot change the type of constraint!");
bool diff = cnst.Columns.Count != exCnst.Columns.Count;
if (!diff)
cnst.Columns.ForEach(col => { if (!exCnst.Columns.Contains(col, StringComparer.InvariantCultureIgnoreCase)) diff = true; });
if (!diff && cnst.Type.Equals(Constraint.DEFAULT, StringComparison.InvariantCultureIgnoreCase))
{
diff = string.Format("({0})", (cnst as DefaultConstraint).Value).Equals((exCnst as DefaultConstraint).Value, StringComparison.InvariantCultureIgnoreCase);
}
if (diff) //constraing changed
throw new NotImplementedException(String.Format("Upgrading existing constraints is not supported({0})", cnst.Name));
}
else // create
{
AddConstraint(exTable, cnst);
}
});
}
private void AddColumn(Table table, Column column)
{
var addColumn = conn.CreateCommand();
if (column.IsComputed)
addColumn.CommandText = string.Format("ALTER TABLE [{0}] ADD [{1}] AS ({2})", table.Name, column.Name, column.ComputedDefinition);
else if (column.DataType.Equals(SqlDbType.NVarChar.ToString(), StringComparison.InvariantCultureIgnoreCase))
addColumn.CommandText = string.Format("ALTER TABLE [{0}] ADD [{1}] {2}({3}) {4}", table.Name, column.Name, column.DataType, column.Length, column.IsNullable ? "NULL" : "NOT NULL");
else
addColumn.CommandText = string.Format("ALTER TABLE [{0}] ADD [{1}] {2} {3}", table.Name, column.Name, column.DataType, column.IsNullable ? "NULL" : "NOT NULL");
addColumn.ExecuteNonQuery();
}
public void DropColumn(Table table, Column col)
{
var dropColumn = conn.CreateCommand();
dropColumn.CommandText = string.Format("ALTER TABLE [{0}] DROP COLUMN [{1}]", table.Name, col.Name);
dropColumn.ExecuteNonQuery();
}
public void DropTable(Table table, bool recursive)
{
var dropCmd = conn.CreateCommand();
dropCmd.CommandText = string.Format("DROP TABLE [{0}];", table.Name);
if (recursive)
{
BuildForeignKeysCache();
List<string> exludedCls = new List<string>();
foreach (var col in table.Columns)
{
if (exludedCls.Contains(col.Name))
continue;
string key = string.Format("{0}_{1}", table.Name, col.Name);
if (_fkCache.ContainsKey(key))
{
var entry = _fkCache[key];
for (int i = 0; i < entry.Constraints.Count; i++)
DropConstraint(entry.Owners[i], entry.Constraints[i]);
}
}
}
dropCmd.ExecuteNonQuery();
if (Tables.Contains(table))
Tables.Remove(table);
}
public void CreateTable(Table table)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat("CREATE TABLE [{0}] (\n", table.Name);
for (int i = 0; i < table.Columns.Count; i++)
{
var column = table.Columns[i];
if (column.IsComputed)
sql.AppendFormat(" [{0}] AS ({1})", column.Name, column.ComputedDefinition);
else if (column.DataType.Equals(SqlDbType.NVarChar.ToString(), StringComparison.InvariantCultureIgnoreCase))
sql.AppendFormat(" [{0}] {1}({2}) {3} {4}", column.Name, column.DataType, column.Length, column.IsNullable ? "NULL" : "NOT NULL", column.Identity ? "IDENTITY" : "");
else if (column.DataType.Equals(SqlDbType.Decimal.ToString(), StringComparison.InvariantCultureIgnoreCase))
sql.AppendFormat(" [{0}] {1} (28, 9) {2}", column.Name, column.DataType, column.IsNullable ? "NULL" : "NOT NULL");
else
sql.AppendFormat(" [{0}] {1} {2} {3}", column.Name, column.DataType, column.IsNullable ? "NULL" : "NOT NULL", column.Identity ? "IDENTITY" : "");
if (i < table.Columns.Count - 1)
sql.AppendFormat(",\n");
}
if (table.Constraints.Count > 0)
sql.AppendFormat(",\n");
List<Constraint> addAfterTableCreation = new List<Constraint>();
for (int i = 0; i < table.Constraints.Count; i++)
{
var cnst = table.Constraints[i];
if (cnst.Type.Equals(Constraint.UNIQUE, StringComparison.InvariantCultureIgnoreCase))
{
if (table.Columns.Count(c => cnst.Columns.Contains(c.Name, StringComparer.InvariantCultureIgnoreCase) && c.IsNullable) > 0)
{
addAfterTableCreation.Add(cnst);
continue;
}
else
{
sql.AppendFormat(" CONSTRAINT [{0}] UNIQUE ({1})",
cnst.Name,
string.Join(",", cnst.Columns.Select(col => string.Format("[{0}]", col)))
);
}
}
else if (cnst.Type.Equals(Constraint.FOREIGN_KEY, StringComparison.InvariantCultureIgnoreCase))
{
var fk = (ForeignKeyConstraint)cnst;
sql.AppendFormat(" CONSTRAINT [{0}] FOREIGN KEY ({1}) REFERENCES [{2}]({3})",
fk.Name,
string.Join(",", fk.Columns.Select(col => string.Format("[{0}]", col))),
fk.RefTable,
string.Join(",", fk.RefColumns.Select(refCol => string.Format("[{0}]", refCol)))
);
}
else if (cnst.Type.Equals(Constraint.PRIMARY_KEY, StringComparison.InvariantCultureIgnoreCase))
{
sql.AppendFormat(" CONSTRAINT [{0}] PRIMARY KEY ({1})",
cnst.Name,
string.Join(",", cnst.Columns.Select(col => string.Format("[{0}]", col)))
);
}
else if (cnst.Type.Equals(Constraint.DEFAULT, StringComparison.InvariantCultureIgnoreCase))
{
addAfterTableCreation.Add(cnst);
continue;
}
else
throw new NotImplementedException(string.Format("Creating constraint of type {0} not implemented.", cnst.Type));
if (i < table.Constraints.Count - 1)
sql.Append(",\n");
}
sql.Append("\n)");
if (!string.IsNullOrEmpty(table.FileGroup))
sql.AppendFormat(" ON [{0}]", table.FileGroup);
var createTbl = conn.CreateCommand();
createTbl.CommandText = sql.ToString();
createTbl.ExecuteNonQuery();
addAfterTableCreation.ForEach(cnst => { AddConstraint(table, cnst); });
}
public void Add(ForeignKeyConstraint fk, Table owner)
{
Owners.Add(owner);
Constraints.Add(fk);
}
public void DropColumn(Table table, Column col)
{
var dropColumn = conn.CreateCommand();
dropColumn.CommandText = string.Format("ALTER TABLE [{0}] DROP COLUMN [{1}]", table.Name, col.Name);
dropColumn.ExecuteNonQuery();
}
public void MergeTable(Table exTable, Table table)
{
#region upgrade columns
table.Columns.ForEach(col =>
{
var exColumn = exTable.GetColumnByName(col.Name);
if (exColumn != null)
{
if (exColumn.IsComputed && col.IsComputed && exColumn.ComputedDefinition != col.ComputedDefinition)
{
DropColumn(table, col);
var addColumn = conn.CreateCommand();
addColumn.CommandText = string.Format("ALTER TABLE [{0}] ADD [{1}] AS ({2})", table.Name, col.Name, col.ComputedDefinition);
addColumn.ExecuteNonQuery();
}
else if (exColumn.IsNullable != col.IsNullable
|| exColumn.Length != col.Length
|| !exColumn.DataType.Equals(col.DataType.ToString(), StringComparison.InvariantCultureIgnoreCase))
{
//check for computed dependencies
var depComputeds = exTable.Columns.Where(c => c.IsComputed && c.ComputedDefinition.ToLower().Contains(string.Format("[{0}]", col.Name.ToLower())));
foreach (var depCol in depComputeds)
{
DropColumn(exTable, depCol);
}
if (exColumn.Length <= col.Length)
{
var alterColumn = conn.CreateCommand();
if (col.DataType.Equals(SqlDbType.NVarChar.ToString(), StringComparison.InvariantCultureIgnoreCase))
alterColumn.CommandText = string.Format("ALTER TABLE [{0}] ALTER COLUMN [{1}] {2}({3}) {4}", table.Name, col.Name, col.DataType, col.Length, col.IsNullable ? "NULL" : "NOT NULL");
else
alterColumn.CommandText = string.Format("ALTER TABLE [{0}] ALTER COLUMN [{1}] {2} {3}", table.Name, col.Name, col.DataType, col.IsNullable ? "NULL" : "NOT NULL");
alterColumn.ExecuteNonQuery();
}
else
{
var constraints = Tables.SelectMany(t => t.Constraints).Where(c => c.Columns.Contains(col.Name));
foreach (var cnst in constraints)
{
DropConstraint(exTable, cnst);
}
DropColumn(table, col);
var addColumn = conn.CreateCommand();
if (col.DataType.Equals(SqlDbType.NVarChar.ToString(), StringComparison.InvariantCultureIgnoreCase))
addColumn.CommandText = string.Format("ALTER TABLE [{0}] ADD [{1}] {2}({3}) {4}", table.Name, col.Name, col.DataType, col.Length, col.IsNullable ? "NULL" : "NOT NULL");
else
addColumn.CommandText = string.Format("ALTER TABLE [{0}] ADD [{1}] {2} {3}", table.Name, col.Name, col.DataType, col.IsNullable ? "NULL" : "NOT NULL");
AddColumn(table, col);
foreach (var cnst in constraints)
{
AddConstraint(exTable, cnst);
}
}
foreach (var depCol in depComputeds)
{
AddColumn(exTable, depCol);
}
}
}
else
{
//TODO: Default value of not nullable columns
AddColumn(table, col);
}
});
#endregion
table.Constraints.ForEach(cnst =>
{
var exCnst = exTable.GetConstraintByName(cnst.Name);
if (exCnst != null)
{
if (cnst.Type != exCnst.Type)
throw new NotSupportedException("Cannot change the type of constraint!");
bool diff = cnst.Columns.Count != exCnst.Columns.Count;
if (!diff)
cnst.Columns.ForEach(col => { if (!exCnst.Columns.Contains(col, StringComparer.InvariantCultureIgnoreCase)) diff = true; });
if (!diff && cnst.Type.Equals(Constraint.DEFAULT, StringComparison.InvariantCultureIgnoreCase))
{
diff = string.Format("({0})", (cnst as DefaultConstraint).Value).Equals((exCnst as DefaultConstraint).Value, StringComparison.InvariantCultureIgnoreCase);
}
if (diff) //constraing changed
throw new NotImplementedException(String.Format("Upgrading existing constraints is not supported({0})", cnst.Name));
}
else // create
{
AddConstraint(exTable, cnst);
}
});
}
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();
}