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