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