/// <summary> /// Adds the constraint of any type (primary key, foreign key, unique key, check) /// </summary> /// <param name="con">The constraint.</param> public void AddConstraint(DatabaseConstraint con) { if (con == null) { throw new ArgumentNullException("con"); } switch (con.ConstraintType) { case ConstraintType.PrimaryKey: PrimaryKey = con; break; case ConstraintType.ForeignKey: _foreignKeys.Add(con); break; case ConstraintType.UniqueKey: _uniqueKeys.Add(con); break; case ConstraintType.Check: _checkConstraints.Add(con); break; case ConstraintType.Default: _defaultConstraints.Add(con); break; } AddConstraintColumns(con); }
/// <summary> /// Adds the constraint of any type (primary key, foreign key, unique key, check) /// </summary> /// <param name="con">The constraint.</param> public void AddConstraint(DatabaseConstraint con) { switch (con.ConstraintType) { case ConstraintType.PrimaryKey: PrimaryKey = con; break; case ConstraintType.ForeignKey: _foreignKeys.Add(con); break; case ConstraintType.UniqueKey: _uniqueKeys.Add(con); break; case ConstraintType.Check: _checkConstraints.Add(con); break; case ConstraintType.Default: _defaultConstraints.Add(con); break; } AddConstraintColumns(con); }
/// <summary> /// Removes the foreign key and cleans the column markers. /// </summary> /// <param name="foreignKey">The foreign key.</param> /// <exception cref="System.ArgumentNullException">foreignKey;foreignkey cannot be null</exception> /// <exception cref="System.InvalidOperationException">Must be a foreign key</exception> internal void RemoveForeignKey(DatabaseConstraint foreignKey) { if (foreignKey == null) { throw new ArgumentNullException("foreignKey", "foreignkey cannot be null"); } if (foreignKey.ConstraintType != ConstraintType.ForeignKey) { throw new InvalidOperationException("Must be a foreign key"); } if (!_foreignKeys.Contains(foreignKey)) { throw new InvalidOperationException("Foreign key not for this table or already removed"); } _foreignKeys.Remove(foreignKey); foreach (var columnName in foreignKey.Columns) { var column = FindColumn(columnName); if (column != null) { column.IsForeignKey = false; column.ForeignKeyTableName = null; column.ForeignKeyTable = null; } } }
private void AddConstraintFindColumn(DatabaseConstraint con, string name) { foreach (DatabaseColumn col in Columns) { if (col.Name.Equals(name, StringComparison.OrdinalIgnoreCase)) { switch (con.ConstraintType) { case ConstraintType.PrimaryKey: col.IsPrimaryKey = true; break; case ConstraintType.ForeignKey: if (!string.IsNullOrEmpty(con.RefersToTable)) { //ignore fk constraint to a pk without a table. col.IsForeignKey = true; col.ForeignKeyTableName = con.RefersToTable; } break; case ConstraintType.UniqueKey: col.IsUniqueKey = true; break; } break; } } }
/// <summary> /// Adds the column. /// </summary> /// <param name="databaseConstraint">The database constraint.</param> /// <param name="databaseColumn">The database column.</param> /// <returns></returns> /// <exception cref="System.ArgumentNullException">databaseConstraint</exception> public static DatabaseConstraint AddColumn(this DatabaseConstraint databaseConstraint, DatabaseColumn databaseColumn) { if (databaseConstraint == null) { throw new ArgumentNullException("databaseConstraint"); } if (databaseColumn == null) { throw new ArgumentNullException("databaseColumn"); } if (databaseColumn.TableName != databaseConstraint.TableName) { throw new InvalidOperationException("Constraint and column must belong to same table"); } databaseConstraint.Columns.Add(databaseColumn.Name); switch (databaseConstraint.ConstraintType) { case ConstraintType.ForeignKey: databaseColumn.IsForeignKey = true; databaseColumn.ForeignKeyTableName = databaseConstraint.RefersToTable; break; case ConstraintType.PrimaryKey: databaseColumn.IsPrimaryKey = true; break; case ConstraintType.UniqueKey: databaseColumn.IsUniqueKey = true; break; } return(databaseConstraint); }
public void TestReferencedTableViaConstraintName() { //create a schema var schema = new DatabaseSchema(null, SqlType.SqlServer); schema.AddTable("Products") .AddColumn("ProductId").AddPrimaryKey() .AddColumn("ProductName") .AddColumn("CategoryId") .AddTable("Categories") .AddColumn("CategoryId").AddPrimaryKey("CategoryPK") .AddColumn("CategoryName") ; //look at the schema var categories = schema.FindTableByName("Categories"); var products = schema.FindTableByName("Products"); //from the database we normally get a RefersToTable defined. //sometimes we don't- we just get the name of the pk constraint //so here we simulate that var fk = new DatabaseConstraint { ConstraintType = ConstraintType.ForeignKey, TableName = "Categories", RefersToConstraint = "CategoryPK" }; fk.Columns.Add("CategoryId"); products.AddConstraint(fk); //act var referencedTable = fk.ReferencedTable(schema); //assert Assert.AreEqual(categories, referencedTable); }
/// <summary> /// Adds a foreign key with a single column /// </summary> /// <param name="databaseColumn">The database column.</param> /// <param name="foreignKeyName">Name of the foreign key.</param> /// <param name="foreignTableName">Name of the foreign table.</param> /// <returns></returns> public static DatabaseColumn AddForeignKey(this DatabaseColumn databaseColumn, string foreignKeyName, string foreignTableName) { if (databaseColumn == null) { throw new ArgumentNullException("databaseColumn", "databaseColumn must not be null"); } if (string.IsNullOrEmpty(foreignTableName)) { throw new ArgumentNullException("foreignTableName", "foreignTableName must not be null"); } var table = databaseColumn.Table; var foreignKey = new DatabaseConstraint { ConstraintType = ConstraintType.ForeignKey, Name = foreignKeyName, TableName = table.Name, RefersToTable = foreignTableName }; foreignKey.Columns.Add(databaseColumn.Name); table.AddConstraint(foreignKey); databaseColumn.IsForeignKey = true; //add the inverse relationship var fkTable = table.DatabaseSchema.FindTableByName(foreignTableName); if (fkTable != null) { fkTable.ForeignKeyChildren.Add(table); } return(databaseColumn); }
public override string WritePrimaryKey(DatabaseConstraint constraint) { if (constraint == null) return null; var columnList = GetColumnList(constraint.Columns); var pkName = ConstraintName(constraint); var nonClustered = ""; if (constraint.Columns.Count == 1) { //UNIQUEIDENTIFIERs should have NON CLUSTERED indexes var colName = constraint.Columns[0]; var col = Table.FindColumn(colName); if (col != null) { colName = col.NetName; if (string.Equals(col.DbDataType, "UNIQUEIDENTIFIER", StringComparison.OrdinalIgnoreCase)) { nonClustered = "NONCLUSTERED "; } } if ("guid".Equals(colName, StringComparison.OrdinalIgnoreCase)) { nonClustered = "NONCLUSTERED "; } } return string.Format(CultureInfo.InvariantCulture, @"ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY {2}({3})", TableName(Table), EscapeName(pkName), nonClustered, columnList) + SqlFormatProvider().LineEnding(); }
public void TestSqlServerWithChangingDefault() { //arrange var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator(); var table = MigrationCommon.CreateTestTable("Orders"); table.SchemaOwner = "dbo"; var column = table.FindColumn("NAME"); column.Length = 40; column.DefaultValue = "'?'"; //create an "original" version of the column (by cloning) var originalColumn = column.Clone(); originalColumn.DefaultValue = "'UNKNOWN'"; //add a default constraint var df = new DatabaseConstraint { ConstraintType = ConstraintType.Default, Name = "DF_Orders_Name" }; df.Columns.Add("NAME"); table.AddConstraint(df); //act var sql = migration.AlterColumn(table, column, originalColumn); //assert //ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [DF_Orders_Name]; //-- Orders from [NAME] VARCHAR (40) NOT NULL to [NAME] VARCHAR (40) NOT NULL //ALTER TABLE [dbo].[Orders] ALTER COLUMN [NAME] VARCHAR (40) NOT NULL; //ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [DF_Orders_Name] DEFAULT '?' FOR [NAME]; Assert.IsTrue(sql.Contains("DROP CONSTRAINT [DF_Orders_Name]"), "drop constraint"); Assert.IsTrue(sql.Contains("ADD CONSTRAINT [DF_Orders_Name] DEFAULT '?' FOR [NAME]"), "add default constraint"); }
public void WithBidirectionalDepndencyTopologicalSort() { //arrange var schema = new DatabaseSchema(null, null); var orders = new DatabaseTable(); orders.Name = "countries"; var productsFk = new DatabaseConstraint(); productsFk.ConstraintType = ConstraintType.ForeignKey; productsFk.RefersToTable = "capitalcities"; orders.AddConstraint(productsFk); schema.Tables.Add(orders); var products = new DatabaseTable(); products.Name = "capitalcities"; var categoriesFk = new DatabaseConstraint(); categoriesFk.ConstraintType = ConstraintType.ForeignKey; categoriesFk.RefersToTable = "countries"; products.AddConstraint(categoriesFk); schema.Tables.Add(products); //a country has one capital city //a capital city is in one country //But bidirectional foreign keys is terrible database design - you really only need one direction. //(you have to save the country with a null capital, then the capital, then update the country again). //Topological sorts don't support cycles, so we should just get back the original list //act var sortedTables = SchemaTablesSorter.TopologicalSort(schema); //assert Assert.AreEqual(2, sortedTables.Count()); //non-deterministic order }
private static bool ExcludeCheckConstraint(DatabaseConstraint check) { //Oracle doesn't allow SYSDATE in check constraints if (check.Expression.IndexOf("getDate()", StringComparison.OrdinalIgnoreCase) != -1) return true; if (check.Expression.IndexOf("current_timestamp", StringComparison.OrdinalIgnoreCase) != -1) return true; return false; }
/// <summary> /// Returns the name of an inverse foreign key property. Uses <see cref="NameCollection"/> /// For single fks, it's a collection using the name of the fk table. /// For multiple fks, it's a collection using the name of the fk columns /// </summary> /// <param name="targetTable">The target table.</param> /// <param name="table">The table.</param> /// <param name="foreignKey">The foreign key.</param> /// <returns> /// Eg OrderLine has fk to Order. Order will have an ICollection<OrderLine> called "OrderLineCollection". /// Multiple fk eg Order has Delivery Address and Billing Address. /// Address will have an ICollection<Order> called "DeliveryAddressCollection", /// and another ICollection<Order> called "BillingAddressCollection" /// </returns> public virtual string ForeignKeyCollectionName(string targetTable, DatabaseTable table, DatabaseConstraint foreignKey) { var fksToTarget = table.ForeignKeys.Where(x => x.RefersToTable == targetTable).ToList(); string name = table.NetName; if (fksToTarget.Count > 1) name = string.Join("", foreignKey.Columns.Select(x => table.FindColumn(x).NetName).ToArray()); return NameCollection(name); }
protected override string WriteDefaultConstraint(DatabaseConstraint constraint) { var column = EscapeName(constraint.Columns.FirstOrDefault()); return string.Format(CultureInfo.InvariantCulture, @"ALTER TABLE {0} ADD CONSTRAINT {1} DEFAULT {2} FOR {3}", TableName(Table), EscapeName(constraint.Name), constraint.Expression, column) + SqlFormatProvider().LineEnding(); }
private void AddConstraintColumns(DatabaseConstraint con) { if (con == null) { return; } foreach (string name in con.Columns) { AddConstraintFindColumn(con, name); } }
private string WriteUniqueKey(DatabaseConstraint uniqueKey) { var columnList = GetColumnList(uniqueKey.Columns); var name = ConstraintName(uniqueKey); return string.Format(CultureInfo.InvariantCulture, AddUniqueConstraintFormat, TableName(Table), EscapeName(name), columnList) + SqlFormatProvider().LineEnding(); }
public virtual string WritePrimaryKey(DatabaseConstraint constraint) { if (constraint == null) return null; var columnList = GetColumnList(constraint.Columns); var pkName = ConstraintName(constraint); return string.Format(CultureInfo.InvariantCulture, @"ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY ({2})", TableName(Table), EscapeName(pkName), columnList) + SqlFormatProvider().LineEnding(); }
/// <summary> /// Adds a foreign key with a single column /// </summary> /// <param name="databaseColumn">The database column.</param> /// <param name="foreignKeyName">Name of the foreign key.</param> /// <param name="foreignTableName">Name of the foreign table.</param> /// <param name="foreignTableSchemaOwner">The foreign table schema owner.</param> /// <returns></returns> /// <exception cref="System.ArgumentNullException"> /// databaseColumn;databaseColumn must not be null /// or /// foreignTableName;foreignTableName must not be null /// </exception> public static DatabaseColumn AddForeignKey(this DatabaseColumn databaseColumn, string foreignKeyName, string foreignTableName, string foreignTableSchemaOwner) { if (databaseColumn == null) { throw new ArgumentNullException("databaseColumn", "databaseColumn must not be null"); } if (string.IsNullOrEmpty(foreignTableName)) { throw new ArgumentNullException("foreignTableName", "foreignTableName must not be null"); } var table = databaseColumn.Table; if (string.IsNullOrEmpty(foreignKeyName)) //no fk name, so we'll invent one (it may be too long e.g. for Oracle) { foreignKeyName = "FK_" + table.Name + "_" + foreignTableName; } var foreignKey = new DatabaseConstraint { ConstraintType = ConstraintType.ForeignKey, Name = foreignKeyName, TableName = table.Name, RefersToTable = foreignTableName, RefersToSchema = foreignTableSchemaOwner, }; foreignKey.Columns.Add(databaseColumn.Name); table.AddConstraint(foreignKey); databaseColumn.IsForeignKey = true; //add the inverse relationship DatabaseTable fkTable; if (string.IsNullOrEmpty(foreignTableSchemaOwner)) { fkTable = table.DatabaseSchema.FindTableByName(foreignTableName); } else { fkTable = table.DatabaseSchema.FindTableByName(foreignTableName, foreignTableSchemaOwner); } if (fkTable != null && !fkTable.ForeignKeyChildren.Contains(table)) { fkTable.ForeignKeyChildren.Add(table); databaseColumn.ForeignKeyTable = fkTable; } return(databaseColumn); }
private void WriteForeignKey(DatabaseTable table, DatabaseConstraint foreignKey, StringBuilder sb) { sb.AppendLine("ALTER TABLE [" + table.Name + "]"); var cols = string.Join(", ", foreignKey.Columns.ToArray()); var referencedTableName = foreignKey.RefersToTable; var referencedSchemaName = foreignKey.RefersToSchema; //find the referenced table's primary key var refTable = _schema.Tables.Find(t => t.Name == referencedTableName && t.SchemaOwner == referencedSchemaName); var refPrimaryKey = refTable.PrimaryKey; var refcols = string.Join(", ", refPrimaryKey.Columns.ToArray()); sb.AppendLine(" ADD CONSTRAINT [" + foreignKey.Name + "] FOREIGN KEY (" + cols + ") REFERENCES " + referencedTableName + "(" + refcols + ")"); }
private void AddConstraint(DatabaseConstraint foreignKey) { var command = Connection.CreateCommand(); var cols = string.Join(", ", foreignKey.Columns.ToArray()); var refPrimaryKey = _table.PrimaryKey; var refcols = string.Join(", ", refPrimaryKey.Columns.ToArray()); command.CommandText = string.Format(CultureInfo.InvariantCulture, "ALTER TABLE [{0}] ADD CONSTRAINT [{1}] FOREIGN KEY ({2}) REFERENCES [{0}]({3})", _table.Name, foreignKey.Name, cols, refcols); command.ExecuteNonQuery(); }
public void TestCompositeKeys() { //arrange var schema = new DatabaseSchema(null, null); schema.AddTable("Store") .AddColumn<int>("Store_Id").AddPrimaryKey("Store_PK") .AddColumn<string>("Name").AddLength(10) .AddTable("StoreSale") .AddColumn<int>("Store_Id").AddForeignKey("Store_FK", "Store") .AddColumn<int>("StoreSale_Id") .AddTable("StoreSaleDetail") .AddColumn<int>("Store_Id").AddForeignKey("Store_FK", "Store") .AddColumn<int>("StoreSale_Id") .AddColumn<int>("StoreSaleDetail_Id") ; var store = schema.FindTableByName("Store"); var storeSale = schema.FindTableByName("StoreSale"); var storeSaleDetail = schema.FindTableByName("StoreSaleDetail"); var pk1 = new DatabaseConstraint { ConstraintType = ConstraintType.PrimaryKey }; pk1.Columns.Add("Store_Id"); pk1.Columns.Add("StoreSale_Id"); storeSale.AddConstraint(pk1); var pk2 = new DatabaseConstraint { ConstraintType = ConstraintType.PrimaryKey }; pk2.Columns.Add("Store_Id"); pk2.Columns.Add("StoreSale_Id"); pk2.Columns.Add("StoreSaleDetail_Id"); storeSaleDetail.AddConstraint(pk2); var fk = new DatabaseConstraint { ConstraintType = ConstraintType.ForeignKey, RefersToTable = "StoreSale" }; fk.Columns.Add("Store_Id"); fk.Columns.Add("StoreSale_Id"); storeSaleDetail.AddConstraint(fk); //act DatabaseSchemaFixer.UpdateReferences(schema); //assert Assert.AreEqual(2, store.ForeignKeyChildren.Count, "Store is target of foreign keys from StoreSale and StoreSaleDetail"); Assert.AreEqual(1, storeSale.ForeignKeyChildren.Count, "StoreSale is target of foreign key from StoreSaleDetail"); var storeId = storeSaleDetail.FindColumn("Store_Id"); Assert.AreEqual(2, storeId.ForeignKeyTableNames.Count, "StoreSaleDetail.StoreId is fk to both Store and StoreSale"); var storeSaleId = storeSaleDetail.FindColumn("StoreSale_Id"); Assert.AreEqual(1, storeSaleId.ForeignKeyTableNames.Count, "StoreSaleDetail.StoreSale_Id is fk to StoreSale"); }
public static DatabaseTable CreateTestTable(string tableName) { //we only need a schema because MySQL foreign key references do not allow just the foreign key table name- they need the columns too var schema = new DatabaseSchema(null, null); var testTable = new DatabaseTable { Name = tableName, Description = "This is a test table" }; schema.Tables.Add(testTable); testTable.DatabaseSchema = schema; //the migration will discover this and know how to link the self referencing table var intDataType = new DataType("INT", "System.Int32"); var idColumn = new DatabaseColumn { Name = "Id", DbDataType = "int", DataType = intDataType, Nullable = false, Description = "Primary key", }; testTable.Columns.Add(idColumn); var parentColumn = new DatabaseColumn { Name = "Parent", //for a self-referencing foreign key DbDataType = "int", DataType = intDataType, Nullable = true, Description = "Self referencing foreign key", }; testTable.Columns.Add(parentColumn); var nameColumn = new DatabaseColumn { Name = "NAME", DbDataType = "VARCHAR", Length = 10, DataType = new DataType("VARCHAR", "string"), Description = "Simple varchar column", }; testTable.Columns.Add(nameColumn); var primaryKey = new DatabaseConstraint { ConstraintType = ConstraintType.PrimaryKey, Name = "PK_" + tableName }; primaryKey.Columns.Add("Id"); testTable.PrimaryKey = primaryKey; return testTable; }
/// <summary> /// Adds a unique key. /// </summary> /// <param name="databaseColumn">The database column.</param> /// <param name="uniqueKeyName">Name of the unique key.</param> /// <returns></returns> public static DatabaseColumn AddUniqueKey(this DatabaseColumn databaseColumn, string uniqueKeyName) { if (databaseColumn == null) { throw new ArgumentNullException("databaseColumn", "databaseColumn must not be null"); } var table = databaseColumn.Table; var uk = new DatabaseConstraint { ConstraintType = ConstraintType.UniqueKey, TableName = table.Name, Name = uniqueKeyName }; uk.Columns.Add(databaseColumn.Name); table.AddConstraint(uk); databaseColumn.IsUniqueKey = true; return(databaseColumn); }
/// <summary> /// Returns the name of a foreign key property for a given foreign key. /// </summary> /// <param name="table">The table.</param> /// <param name="foreignKey">The foreign key.</param> /// <returns></returns> /// <remarks> /// If it is a simple foreign key, it is the NetName of the column /// if it is a composite foreign key, it is the NetName of the foreign table /// if there is a collision with the class name, append "Key" /// If there are multiple foreign keys to one table, ensure they are unique. /// </remarks> public virtual string ForeignKeyName(DatabaseTable table, DatabaseConstraint foreignKey) { var refTable = foreignKey.ReferencedTable(table.DatabaseSchema); if (refTable == null) { //we can't find the foreign key table, so just write the columns return null; } //This is a name for the foreign key. Only used for composite keys. var propertyName = refTable.NetName; //if there is only one column (not composite) use the netName of that column if (foreignKey.Columns.Count == 1) { var columnName = foreignKey.Columns.Single(); var column = table.FindColumn(columnName); //if it is a primary key, we've used the original name for a scalar property if (!column.IsPrimaryKey) propertyName = column.NetName; } else //composite keys { // Check whether the referenced table is used in any other key. This ensures that the property names // are unique. if (table.ForeignKeys.Count(x => x.RefersToTable == foreignKey.RefersToTable) > 1) { // Append the key name to the property name. In the event of multiple foreign keys to the same table // This will give the consumer context. propertyName += foreignKey.Name; } } // Ensures that property name cannot be the same as class name if (propertyName == table.NetName) { propertyName += "Key"; } return propertyName; }
public void TestSqlServerCheckConstraint() { //arrange var table = new DatabaseTable {Name = "Orders"}; var check = new DatabaseConstraint { ConstraintType = ConstraintType.Check, Expression = "[Quantity] > 0", Name = "ValidationRule" }; table.AddConstraint(check); var writer = new ConstraintWriter(table); //act var txt = writer.WriteCheckConstraints(); //assert Assert.IsTrue(txt.Contains("ALTER TABLE [Orders] ADD CONSTRAINT [ValidationRule] CHECK ([Quantity] > 0);")); }
public void TestAccessDateCheckConstraint() { //arrange var table = new DatabaseTable {Name = "Orders"}; var check = new DatabaseConstraint { ConstraintType = ConstraintType.Check, Expression = ">Date()", Name = "[Orders].[OrderDate].ValidationRule" }; table.AddConstraint(check); var writer = new TableGenerator(table); //act var txt = writer.Write(); //assert Assert.IsTrue(txt.Contains("ALTER TABLE [Orders] ADD CONSTRAINT [Orders_OrderDate_ValidationRule] CHECK ([Orders].[OrderDate] >getdate());")); }
public IList<DatabaseConstraint> Execute(DbConnection connection) { var tables = new Tables(_tableName).Execute(connection); foreach (var table in tables) { var tableName = table.Name; using (var cmd = connection.CreateCommand()) { cmd.CommandText = string.Format(PragmaSql, tableName); using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { var refersToTable = dr.GetString("table"); var con = Result.FirstOrDefault(x => x.TableName == tableName && x.RefersToTable == refersToTable); if (con == null) { con = new DatabaseConstraint { TableName = tableName, ConstraintType = ConstraintType.ForeignKey, RefersToTable = refersToTable, UpdateRule = dr.GetString("on_update"), DeleteRule = dr.GetString("on_delete"), }; Result.Add(con); } con.Columns.Add(dr.GetString("from")); } } } } return Result; }
public void TestAddPrimaryKeyWithGuid() { //arrange var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator(); var schema = new DatabaseSchema(null, SqlType.SqlServer); var table = schema.AddTable("Test") .AddColumn<Guid>("Id") .AddColumn<string>("Name") .Table; var pk = new DatabaseConstraint { Name = "Test_PK", ConstraintType = ConstraintType.PrimaryKey }; pk.Columns.Add("Id"); //act var sql = migration.AddConstraint(table, pk); //assert Assert.IsTrue(sql.IndexOf("ADD CONSTRAINT [Test_PK] PRIMARY KEY NONCLUSTERED ([Id])", StringComparison.OrdinalIgnoreCase) != -1, "adding a primary key"); }
public void TestSqlServerAddDefaultConstraint() { //arrange var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator(); var table = MigrationCommon.CreateTestTable("Orders"); table.SchemaOwner = "dbo"; var column = table.FindColumn("NAME"); column.Length = 40; column.DefaultValue = "'?'"; //add a default constraint var df = new DatabaseConstraint { ConstraintType = ConstraintType.Default, Name = "DF_Orders_Name", Expression = "'?'" }; df.Columns.Add("NAME"); table.AddConstraint(df); //act var sql = migration.AddConstraint(table, df); //assert //ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [DF_Orders_Name] DEFAULT '?' FOR [NAME]; Assert.IsTrue(sql.Contains("ADD CONSTRAINT [DF_Orders_Name] DEFAULT '?' FOR [NAME]"), "add constraint"); }
protected override string ConstraintName(DatabaseConstraint constraint) { var name = constraint.Name; if (string.IsNullOrEmpty(name)) { var columnNames = string.Join("_", constraint.Columns.ToArray()); //suffixes - http://stackoverflow.com/questions/4107915/postgresql-default-constraint-names/4108266#4108266 //pkey for a Primary Key constraint //key for a Unique constraint //excl for an Exclusion constraint //idx for any other kind of index //fkey for a Foreign key //check for a Check constraint string suffix; switch (constraint.ConstraintType) { case ConstraintType.PrimaryKey: suffix = "pkey"; break; case ConstraintType.ForeignKey: suffix = "fkey"; break; case ConstraintType.UniqueKey: suffix = "key"; break; case ConstraintType.Check: suffix = "check"; break; default: suffix = "idx"; break; } return Table.Name + "_" + columnNames + "_" + suffix; } return name; }
public void TestSqlServerDropColumnWithDefault() { //arrange var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator(); var table = MigrationCommon.CreateTestTable("Orders"); table.SchemaOwner = "dbo"; var column = table.FindColumn("NAME"); column.Length = 40; column.DefaultValue = "'?'"; //add a default constraint var df = new DatabaseConstraint { ConstraintType = ConstraintType.Default, Name = "DF_Orders_Name" }; df.Columns.Add("NAME"); table.AddConstraint(df); //act var sql = migration.DropColumn(table, column); //assert //ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [DF_Orders_Name]; //ALTER TABLE [dbo].[Orders] DROP COLUMN [NAME]; Assert.IsTrue(sql.Contains("DROP CONSTRAINT [DF_Orders_Name]"), "drop constraint"); }
public void TestTopologicalSort() { //arrange var schema = new DatabaseSchema(null, null); var orders = new DatabaseTable(); orders.Name = "orders"; var productsFk = new DatabaseConstraint { ConstraintType = ConstraintType.ForeignKey, RefersToTable = "products" }; orders.AddConstraint(productsFk); schema.Tables.Add(orders); var categories = new DatabaseTable(); categories.Name = "categories"; schema.Tables.Add(categories); var products = new DatabaseTable(); products.Name = "products"; var categoriesFk = new DatabaseConstraint(); categoriesFk.ConstraintType = ConstraintType.ForeignKey; categoriesFk.RefersToTable = "categories"; products.AddConstraint(categoriesFk); schema.Tables.Add(products); //act var sortedTables = SchemaTablesSorter.TopologicalSort(schema); //assert var first = sortedTables.First(); var last = sortedTables.Last(); Assert.AreEqual(categories, first); Assert.AreEqual(orders, last); }
private static DatabaseTable CreateProductsTable() { var productsTable = new DatabaseTable { Name = "Products" }; var idColumn = new DatabaseColumn { Name = "Id", DbDataType = "int" }; productsTable.Columns.Add(idColumn); var pk = new DatabaseConstraint { ConstraintType = ConstraintType.PrimaryKey, Name = "PK_Orders" }; pk.Columns.Add("Id"); productsTable.PrimaryKey = pk; return productsTable; }
private static DatabaseSchema CreateSchema() { var schema = new DatabaseSchema(null, "System.Data.SqlClient"); var orderTable = new DatabaseTable { Name = "Orders" }; schema.Tables.Add(orderTable); var idColumn = new DatabaseColumn { Name = "Id", DbDataType = "int" }; orderTable.Columns.Add(idColumn); var nameColumn = new DatabaseColumn { Name = "Name", DbDataType = "VARCHAR" }; orderTable.Columns.Add(nameColumn); var descColumn = new DatabaseColumn { Name = "Desc", DbDataType = "VARCHAR" }; orderTable.Columns.Add(descColumn); var pk = new DatabaseConstraint { ConstraintType = ConstraintType.PrimaryKey, Name = "PK_Orders" }; pk.Columns.Add("Id"); orderTable.PrimaryKey = pk; return schema; }
private static DatabaseConstraint GetUniqueConstraint() { var constraint = new DatabaseConstraint { Name = "UK_NAME", ConstraintType = ConstraintType.UniqueKey, }; constraint.Columns.Add("Name"); return constraint; }
private void AddPrimaryKey(DatabaseTable table, XElement entityType) { var key = entityType.Element(_schema + "Key"); if (key == null) return; var pkColumns = key.Elements(_schema + "PropertyRef").Select(propertyRef => (string)propertyRef.Attribute("Name")); var primaryKey = new DatabaseConstraint { ConstraintType = ConstraintType.PrimaryKey }; primaryKey.Columns.AddRange(pkColumns); table.PrimaryKey = primaryKey; }
private void AddForeignKeys(XElement storageSchema, DatabaseSchema databaseSchema) { foreach (var association in storageSchema.Elements(_schema + "Association")) { var name = (string)association.Attribute("Name"); var referentialConstraint = association.Element(_schema + "ReferentialConstraint"); //referentialConstraint is optional if (referentialConstraint == null) continue; var principal = referentialConstraint.Element(_schema + "Principal"); var fkRole = (string)principal.Attribute("Role"); var fkTable = AssociationTypeToTableName(storageSchema, association, fkRole); var dependent = referentialConstraint.Element(_schema + "Dependent"); var role = (string)dependent.Attribute("Role"); var tableName = AssociationTypeToTableName(storageSchema, association, role); //the EF DDL generator (GenerateTSQL.Utility.ttinclude WriteFKConstraintName) adds the prefix if (!name.StartsWith("FK_", StringComparison.OrdinalIgnoreCase)) name = "FK_" + name; var fk = new DatabaseConstraint { ConstraintType = ConstraintType.ForeignKey, Name = name, TableName = tableName, RefersToTable = fkTable }; fk.Columns.AddRange( dependent.Elements(_schema + "PropertyRef") .Select(e => (string)e.Attribute("Name"))); var deleteRule = association.Elements(_schema + "End") .First(e => (string)e.Attribute("Role") == fkRole) .Element(_schema + "OnDelete"); if (deleteRule != null && (string)deleteRule.Attribute("Action") == "Cascade") { fk.DeleteRule = "Cascade"; } var databaseTable = databaseSchema.FindTableByName(tableName); databaseTable.AddConstraint(fk); var cols = fk.Columns.Select(col => databaseTable.FindColumn(col)); //SSDLToSQL10.tt creates a nonclustered index (IX_name) for the foreign key databaseTable.AddIndex("IX_" + name, cols); } }
private void AddConstraintFindColumn(DatabaseConstraint con, string name) { foreach (DatabaseColumn col in Columns) { if (col.Name.Equals(name, StringComparison.OrdinalIgnoreCase)) { switch (con.ConstraintType) { case ConstraintType.PrimaryKey: col.IsPrimaryKey = true; break; case ConstraintType.ForeignKey: if (!string.IsNullOrEmpty(con.RefersToTable)) { //ignore fk constraint to a pk without a table. col.IsForeignKey = true; col.ForeignKeyTableName = con.RefersToTable; } break; case ConstraintType.UniqueKey: col.IsUniqueKey = true; break; } break; } } }
private void AddConstraintColumns(DatabaseConstraint con) { if (con == null) return; foreach (string name in con.Columns) { AddConstraintFindColumn(con, name); } }
/// <summary> /// Removes the foreign key and cleans the column markers. /// </summary> /// <param name="foreignKey">The foreign key.</param> /// <exception cref="System.ArgumentNullException">foreignKey;foreignkey cannot be null</exception> /// <exception cref="System.InvalidOperationException">Must be a foreign key</exception> internal void RemoveForeignKey(DatabaseConstraint foreignKey) { if (foreignKey == null) throw new ArgumentNullException("foreignKey", "foreignkey cannot be null"); if (foreignKey.ConstraintType != ConstraintType.ForeignKey) throw new InvalidOperationException("Must be a foreign key"); if (!_foreignKeys.Contains(foreignKey)) throw new InvalidOperationException("Foreign key not for this table or already removed"); _foreignKeys.Remove(foreignKey); foreach (var columnName in foreignKey.Columns) { var column = FindColumn(columnName); if (column != null) { column.IsForeignKey = false; column.ForeignKeyTableName = null; column.ForeignKeyTable = null; } } }