public void The_Change_Is_Reflected_In_Its_MappedColumns() { var table1 = new Table("Table1"); table1.AddColumn(new Column("Column1")); table1.AddColumn(new Column("Column2")); var table2 = new Table("Table2"); table2.AddColumn(new Column("Column1")); table2.AddColumn(new Column("Column2")); var key1 = new Key("PrimaryKey") { Keytype = DatabaseKeyType.Primary }; table1.AddKey(key1); key1.AddColumn("Column1"); var key2 = new Key("ForeignKey") { Keytype = DatabaseKeyType.Foreign }; table2.AddKey(key2); key2.AddColumn("Column1"); var relationship = new RelationshipImpl(); relationship.PrimaryTable = table1; relationship.ForeignTable = table2; relationship.PrimaryKey = key1; relationship.ForeignKey = key2; Assert.That(relationship.MappedColumns.Count(), Is.EqualTo(1)); MappedColumn mappedColumn = relationship.MappedColumns.ElementAt(0); Assert.That(mappedColumn.Source, Is.SameAs(table1.Columns[0])); Assert.That(mappedColumn.Target, Is.SameAs(table2.Columns[0])); key1.AddColumn("Column2"); key2.AddColumn("Column2"); Assert.That(relationship.MappedColumns.Count(), Is.EqualTo(2), "The new column was not added."); mappedColumn = relationship.MappedColumns.ElementAt(0); Assert.That(mappedColumn.Source, Is.SameAs(table1.Columns[0]), "The source column is wrong on item 0"); Assert.That(mappedColumn.Target, Is.SameAs(table2.Columns[0]), "The target column is wrong on item 0"); mappedColumn = relationship.MappedColumns.ElementAt(1); Assert.That(mappedColumn.Source, Is.SameAs(table1.Columns[1]), "The source column is wrong on item 1"); Assert.That(mappedColumn.Target, Is.SameAs(table2.Columns[1]), "The target column is wrong on item 1"); }
private void PostProcessEntity(MappingSet set, Entity entity) { if (entity.HasParent == false) { return; } var childTable = entity.MappedTables().First(); var parentTable = entity.Parent.MappedTables().First(); // Create foreign key for parent relationship // This code makes a major assumption: That the primary key of the child has the same columns // as the primary key of the parent. var name = GetNextKeyName("FK_" + parentTable.Name + "_" + childTable.Name, set); var foreignKey = new Key(name, DatabaseKeyType.Foreign); var primaryKey = parentTable.FirstPrimaryKey; var childPrimaryKey = childTable.FirstPrimaryKey; foreignKey.ReferencedKey = primaryKey; childTable.AddKey(foreignKey); foreach (var column in childPrimaryKey.Columns) { foreignKey.AddColumn(column.Name); } }
private void GetForeignKeys(IDatabase db) { DataTable keysTable = connector.GetForeignKeyConstraints(); ITable foreignTable; for (int i = 0; i < keysTable.Rows.Count; i++) { var keyRow = keysTable.Rows[i]; string primaryTableSchema = (string)keyRow["PrimaryTableSchema"]; ITable parentTable = db.GetTable((string)keyRow["PrimaryTable"], primaryTableSchema); string foreignTableSchema = (string)keyRow["ForeignTableSchema"]; foreignTable = db.GetTable((string)keyRow["ForeignTable"], foreignTableSchema); var currentKeyName = keyRow["ForeignKeyName"].ToString(); // Don't process keys of tables that the user hasn't selected if (parentTable == null || foreignTable == null) { continue; } Key key = new Key { Name = currentKeyName, IsUserDefined = false, Keytype = DatabaseKeyType.Foreign, Parent = parentTable }; ////////////////// key.IsUnique = connector.GetUniqueIndexes().Select(string.Format("INDEX_NAME = '{0}' AND TABLE_SCHEMA = '{1}'", currentKeyName, primaryTableSchema)).Length > 0; ////////////////// foreignTable.AddKey(key); // Get Foreign Key columns DataRow[] foreignKeyColumns = connector.GetForeignKeyColumns().Select(string.Format("CONSTRAINT_NAME = '{0}' AND TABLE_SCHEMA = '{1}' AND TABLE_NAME = '{2}'", key.Name.Replace("'", "''"), foreignTableSchema, foreignTable.Name)); foreach (DataRow row in foreignKeyColumns) { key.AddColumn((string)row["COLUMN_NAME"]); } key.ReferencedKey = parentTable.GetKey((string)keyRow["PrimaryKeyName"]); bool isUnique = false; if (foreignTable.FirstPrimaryKey != null) { isUnique = true; foreach (var col in foreignTable.FirstPrimaryKey.Columns) { if (!key.Columns.Contains(col)) { isUnique = false; break; } } } key.IsUnique = isUnique; } }
public void The_Corresponding_Relationship_Should_Also_Have_The_Column() { // Create database and relationships Database db = RelationshipDatabaseLoader.GetDb(); Relationship relationship = db.Tables[1].Relationships[0]; Assert.That(relationship.PrimaryKey, Is.SameAs(db.Tables[0].Keys[0])); Assert.That(relationship.ForeignKey, Is.SameAs(db.Tables[1].Keys[0])); Assert.That(relationship.ForeignKey.Columns[0].Name, Is.EqualTo("Column1")); IKey originalKey = db.Tables[0].Keys[0]; IKey newKey = new Key(originalKey.Name); newKey.Parent = new Table("Table1"); newKey.Parent.AddColumn(new Column("Column2")); newKey.Description = "new description"; newKey.AddColumn("Column2"); KeyChangeOperation op = new KeyChangeOperation(db.Tables[0].Keys[0], newKey); op.RunOperation(); op.RunSecondStep(); // Make sure the relationship is still the same, and has the same references. Relationship relationship2 = db.Tables[1].Relationships[0]; Assert.That(relationship2, Is.SameAs(relationship)); Assert.That(relationship2.PrimaryKey, Is.SameAs(db.Tables[0].Keys[0])); Assert.That(relationship2.ForeignKey, Is.SameAs(db.Tables[1].Keys[0])); Assert.That(relationship2.ForeignKey.Columns[0].Name, Is.EqualTo("Column1")); }
//private void ProcessTable(ITable table, Entity entity) //{ // foreach (var column in table.ColumnsInPrimaryKey) // entity.Key.AddProperty(entity.GetProperty(column.Name.ToTitleCase())); //} public ITable CreateTable(Entity entity) { ITable table = new Table(entity.Name.Pluralize().GetNextName(currentlyUsedNames), entity.Schema); foreach (var property in entity.ConcreteProperties) { var column = CreateColumn(property); table.AddColumn(column); } // Create primary key var key = new Key("PK_" + entity.Name, DatabaseKeyType.Primary); table.AddKey(key); var keyProperties = entity.Key.Properties.ToList(); foreach (var property in keyProperties) { var name = property.Name; // Don't create columns for parent key columns. We will do this in the PostProcess step. if (table.Columns.Any(c => c.Name == name) == false) { continue; } key.AddColumn(name); } return(table); }
public void The_Corresponding_Relationship_Should_Also_Have_The_Column_Removed() { // Create database and relationships Database db = RelationshipDatabaseLoader.GetDb(); Relationship relationship = db.Tables[1].Relationships[0]; Assert.That(relationship.PrimaryKey, Is.SameAs(db.Tables[0].Keys[0])); Assert.That(relationship.ForeignKey, Is.SameAs(db.Tables[1].Keys[0])); Assert.That(relationship.PrimaryKey.Columns[0].Name, Is.EqualTo("Column1")); Assert.That(relationship.PrimaryKey.Columns[1].Name, Is.EqualTo("Column2")); IKey originalKey = db.Tables[1].Keys[0]; IKey newKey = new Key(originalKey.Name, originalKey.Keytype); newKey.Parent = new Table("Table2"); newKey.Parent.AddColumn(new Column("Column2")); newKey.Description = "new description"; newKey.AddColumn("Column2"); KeyChangeOperation op = new KeyChangeOperation(db.Tables[1].Keys[0], newKey); op.RunOperation(); op.RunSecondStep(); // Make sure the relationship is still the same, and has the same references. Relationship relationship2 = db.Tables[1].Relationships[0]; Assert.That(relationship2, Is.SameAs(relationship)); Assert.That(relationship2.PrimaryKey, Is.SameAs(db.Tables[0].Keys[0])); Assert.That(relationship2.ForeignKey, Is.SameAs(db.Tables[1].Keys[0])); Assert.That(relationship2.PrimaryKey.Columns, Has.Count(2)); Assert.That(relationship2.ForeignKey.Columns, Has.Count(1)); Assert.That(relationship2.ForeignKey.Columns[0].Name, Is.EqualTo("Column2")); }
public void The_Original_Primary_Key_Should_Be_Modified() { var db1 = TestDatabaseLoader.TestDatabase(); IKey originalKey = db1.Tables[0].Keys[0]; originalKey.Description = "old description"; IKey newKey = new Key(originalKey.Name); newKey.Parent = new Table("Table1"); newKey.Parent.AddColumn(new Column("Column2")); newKey.Description = "new description"; newKey.AddColumn("Column2"); KeyChangeOperation op = new KeyChangeOperation(db1.Tables[0].Keys[0], newKey); op.RunOperation(); op.RunSecondStep(); IKey key = db1.Tables[0].Keys[0]; Assert.That(db1.Tables[0].Keys, Has.Count(1)); Assert.That(key, Is.SameAs(originalKey), "Key object should still be the same"); Assert.That(key.Description, Is.EqualTo("old description"), "Should not change user set properties."); Assert.That(key.ReferencedKey, Is.Null); Assert.That(key.Columns, Has.Count(1)); Assert.That(key.Columns[0].Name, Is.EqualTo("Column2")); Assert.That(key.Columns[0], Is.Not.SameAs(newKey.Columns[0])); Assert.That(key.Columns[0], Is.SameAs(db1.Tables[0].Columns[1]), "The new key should reference existing columns"); Assert.That(key.Parent, Is.SameAs(db1.Tables[0]), "The new key's parent should be Table1 in the existing database"); }
internal static Database GetDb() { var db = TestDatabaseLoader.TestDatabase(); Table table2 = new Table("Table2"); table2.AddColumn(new Column("Column1")); table2.AddColumn(new Column("Column2")); table2.AddColumn(new Column("Column3")); Key foreignKey = new Key("FK_Table2", DatabaseKeyType.Foreign); foreignKey.Parent = table2; foreignKey.ReferencedKey = db.Tables[0].Keys[0]; foreignKey.AddColumn("Column1"); foreignKey.AddColumn("Column2"); table2.AddKey(foreignKey); db.AddTable(table2); new DatabaseProcessor().CreateRelationships(db); return(db); }
private static void CreateUniqueKeyFor(Key key) { IKey uniqueKey = new Key("Unique_" + key.Name, DatabaseKeyType.Unique); key.Parent.AddKey(uniqueKey); foreach (var column in key.Columns) { uniqueKey.AddColumn(column.Name); } }
private void GetKeys(ITable table) { DataRow[] keyRows = connector.Indexes.Select(string.Format("TABLE_NAME = '{0}'", table.Name.Replace("'", "''"))); for (int i = 0; i < keyRows.Length; i++) { DataRow keyRow = keyRows[i]; DatabaseKeyType keyType; string indexKeyType = keyRow["CONSTRAINT_TYPE"].ToString(); if (indexKeyType == "PRIMARY KEY") { keyType = DatabaseKeyType.Primary; } else if (indexKeyType == "UNIQUE") { keyType = DatabaseKeyType.Unique; } else { continue; } Key key = new Key { Name = keyRow["INDEX_NAME"].ToString(), IsUserDefined = false, Keytype = keyType, Parent = table, IsUnique = true }; // Fill Columns List <DataRow> keyColumnRows = new List <DataRow>(); keyColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", table.Name.Replace("'", "''"), keyRow["COLUMN_NAME"]))); while ((i < keyRows.Length - 1) && (string)keyRows[i + 1]["TABLE_NAME"] == table.Name && (string)keyRows[i + 1]["INDEX_NAME"] == (string)keyRow["INDEX_NAME"]) { i++; keyRow = keyRows[i]; keyColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", table.Name.Replace("'", "''"), keyRow["COLUMN_NAME"]))); } // Fill Columns foreach (DataRow keyColumnRow in keyColumnRows) { key.AddColumn((string)keyColumnRow["COLUMN_NAME"]); } key.ResetDefaults(); table.AddKey(key); } }
private static Key CreateForeignKey(MappingSet set, ITable primaryTable, ITable foreignTable) { var foreignKey = new Key(GetNextKeyName("FK_" + primaryTable.Name + "_" + foreignTable.Name, set), DatabaseKeyType.Foreign); var primaryKey = primaryTable.FirstPrimaryKey; foreignKey.ReferencedKey = primaryKey; foreignTable.AddKey(foreignKey); // Create the columns for the foreign keys foreach (var column in primaryKey.Columns) { var newColumn = CloneColumn(primaryTable, foreignTable, column, "Foreign_"); foreignKey.AddColumn(newColumn.Name); } return(foreignKey); }
public void Should_Return_This() { const string expectedXML = KeyWithColumnsXml; Table table1 = new Table("Table1"); table1.AddColumn(new Column("Column1")); Key key = new Key("PrimaryKey"); key.Parent = table1; key.AddColumn("Column1"); string outputXML = key.Serialise(new DatabaseSerialisationScheme()); outputXML = XmlSqueezer.RemoveWhitespaceBetweenElements(outputXML); Assert.That(outputXML, Is.EqualTo(expectedXML)); }
private void GetForeignKeys(IDatabase db) { DataTable keysTable = connector.GetForeignKeyConstraints(); foreach (DataRow keyRow in keysTable.Rows) { ITable parentTable = db.GetTable((string)keyRow["PrimaryTable"], ""); ITable foreignTable = db.GetTable((string)keyRow["ForeignTable"], ""); // Don't process keys of tables that the user hasn't selected if (parentTable == null || foreignTable == null) { continue; } Key key = new Key { Name = keyRow["PrimaryKeyName"].ToString(), IsUserDefined = false, Keytype = DatabaseKeyType.Foreign, Parent = parentTable }; ////////////////// key.IsUnique = connector.GetUniqueIndexes().Select(string.Format("INDEX_NAME = '{0}'", key.Name)).Length > 0; //foreignTable.AddKey(key); ////////////////// // Get Foreign Key columns DataRow[] foreignKeyColumns = connector.GetForeignKeyColumns().Select(string.Format("CONSTRAINT_NAME = '{0}' AND TABLE_NAME = '{1}'", key.Name.Replace("'", "''"), foreignTable.Name)); foreach (DataRow row in foreignKeyColumns) { key.AddColumn((string)row["COLUMN_NAME"]); } DataRow[] keyReferencedColumnRows = connector.IndexReferencedColumns.Select(string.Format("ForeignKey = '{0}'", keyRow["CONSTRAINT_NAME"])); DataRow firstKeyReferencedColumnRow = keyReferencedColumnRows[0]; // Fill References key.ReferencedKey = foreignTable.GetKey((string)firstKeyReferencedColumnRow["ReferencedKey"]); parentTable.AddKey(key); } return; }
private static ITable CreateTable(IDatabase db, string tableName) { var table = new Table(tableName); table.AddColumn(new Column("Column1")); table.AddColumn(new Column("Column2")); table.AddColumn(new Column("Column3")); var primaryKey = new Key("PrimaryKey", DatabaseKeyType.Primary); table.AddKey(primaryKey); var foreignKey = new Key("ForeignKey", DatabaseKeyType.Foreign); table.AddKey(foreignKey); primaryKey.AddColumn("Column1"); foreignKey.AddColumn("Column1"); db.AddTable(table); return table; }
private static ITable CreateTable(IDatabase db, string tableName) { var table = new Table(tableName); table.AddColumn(new Column("Column1")); table.AddColumn(new Column("Column2")); table.AddColumn(new Column("Column3")); var primaryKey = new Key("PrimaryKey", DatabaseKeyType.Primary); table.AddKey(primaryKey); var foreignKey = new Key("ForeignKey", DatabaseKeyType.Foreign); table.AddKey(foreignKey); primaryKey.AddColumn("Column1"); foreignKey.AddColumn("Column1"); db.AddTable(table); return(table); }
public void The_Original_Database_Should_Contain_A_Copy_Of_The_New_Key() { var db1 = TestDatabaseLoader.TestDatabase(); // Setup new key information Table parentTable = new Table("Table1"); parentTable.AddColumn(new Column("Column3")); Key newKey = new Key("UQ_Column3"); newKey.Parent = parentTable; newKey.AddColumn("Column3"); KeyAdditionOperation op = new KeyAdditionOperation(db1.Tables[0], newKey); op.RunOperation(); op.RunSecondStep(); Assert.That(db1.Tables[0].Keys, Has.Count(2)); Assert.That(db1.Tables[0].Keys[1].Name, Is.EqualTo(newKey.Name)); Assert.That(db1.Tables[0].Keys[1], Is.Not.SameAs(newKey), "The added Key should be a copy, not the original object."); Assert.That(db1.Tables[0].Keys[1].Columns[0], Is.SameAs(db1.Tables[0].Columns[2]), "The new key should reference existing columns, not the new ones"); Assert.That(db1.Tables[0].Keys[1].Parent, Is.SameAs(db1.Tables[0]), "The new key's parent should be Table1 in the existing database"); }
public void Should_Return_This() { const string expectedXML = KeyWithColumnsXml; Table table1 = new Table("Table1"); table1.AddColumn(new Column("Column1")); Key key = new Key("PrimaryKey"); key.Parent = table1; key.AddColumn("Column1"); string outputXML = key.Serialise(new DatabaseSerialisationScheme()); outputXML = XmlSqueezer.RemoveWhitespaceBetweenElements(outputXML); Assert.That(outputXML, Is.EqualTo(expectedXML)); }
private void GetKeys(ITable table) { DataRow[] keyRows = connector.Indexes.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}'", table.Name.Replace("'", "''"), table.Schema)); bool hasPrimaryKey = false; for (int i = 0; i < keyRows.Length; i++) { DataRow keyRow = keyRows[i]; DatabaseKeyType keyType; string indexKeyType = keyRow["CONSTRAINT_TYPE"].ToString(); if (indexKeyType == "PRIMARY KEY") { keyType = DatabaseKeyType.Primary; hasPrimaryKey = true; } else if (indexKeyType == "UNIQUE") { keyType = DatabaseKeyType.Unique; } else { continue; } Key key = new Key { Name = keyRow["INDEX_NAME"].ToString(), IsUserDefined = false, Keytype = keyType, Parent = table, IsUnique = true }; // Fill Columns List <DataRow> keyColumnRows = new List <DataRow>(); keyColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}' AND COLUMN_NAME = '{2}'", table.Name.Replace("'", "''"), table.Schema, keyRow["COLUMN_NAME"]))); while ((i < keyRows.Length - 1) && (string)keyRows[i + 1]["TABLE_NAME"] == table.Name && (string)keyRows[i + 1]["INDEX_NAME"] == (string)keyRow["INDEX_NAME"]) { i++; keyRow = keyRows[i]; keyColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}' AND COLUMN_NAME = '{2}'", table.Name.Replace("'", "''"), table.Schema, keyRow["COLUMN_NAME"]))); } // Fill Columns foreach (DataRow keyColumnRow in keyColumnRows.OrderBy(r => r["ORDINAL_POSITION"])) { key.AddColumn((string)keyColumnRow["COLUMN_NAME"]); } key.ResetDefaults(); table.AddKey(key); } if (!hasPrimaryKey) { Key key = new Key { Name = "PK_" + table.Name, IsUserDefined = true, Keytype = DatabaseKeyType.Primary, Parent = table, IsUnique = true, Description = "Added by Visual NHibernate" }; key.ResetDefaults(); table.AddKey(key); } }
private void PostProcessEntity(MappingSet set, Entity entity) { if (entity.HasParent == false) return; var childTable = entity.MappedTables().First(); var parentTable = entity.Parent.MappedTables().First(); // Create foreign key for parent relationship // This code makes a major assumption: That the primary key of the child has the same columns // as the primary key of the parent. var name = GetNextKeyName("FK_" + parentTable.Name + "_" + childTable.Name, set); var foreignKey = new Key(name, DatabaseKeyType.Foreign); var primaryKey = parentTable.FirstPrimaryKey; var childPrimaryKey = childTable.FirstPrimaryKey; foreignKey.ReferencedKey = primaryKey; childTable.AddKey(foreignKey); foreach (var column in childPrimaryKey.Columns) { foreignKey.AddColumn(column.Name); } }
private void GetKeys(ITable table) { DataRow[] keyRows = connector.Indexes.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}'", table.Name.Replace("'", "''"), table.Schema)); for (int i = 0; i < keyRows.Length; i++) { DataRow keyRow = keyRows[i]; DatabaseKeyType keyType; string indexKeyType = keyRow["CONSTRAINT_TYPE"].ToString(); if (indexKeyType == "PRIMARY KEY") { keyType = DatabaseKeyType.Primary; } else if (indexKeyType == "UNIQUE") { keyType = DatabaseKeyType.Unique; } else { continue; } Key key = new Key { Name = keyRow["INDEX_NAME"].ToString(), IsUserDefined = false, Keytype = keyType, Parent = table, IsUnique = true }; // Fill Columns List<DataRow> keyColumnRows = new List<DataRow>(); keyColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}' AND COLUMN_NAME = '{2}'", table.Name.Replace("'", "''"), table.Schema, keyRow["COLUMN_NAME"]))); while ((i < keyRows.Length - 1) && (string)keyRows[i + 1]["TABLE_NAME"] == table.Name && (string)keyRows[i + 1]["INDEX_NAME"] == (string)keyRow["INDEX_NAME"]) { i++; keyRow = keyRows[i]; keyColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}' AND COLUMN_NAME = '{2}'", table.Name.Replace("'", "''"), table.Schema, keyRow["COLUMN_NAME"]))); } // Fill Columns foreach (DataRow keyColumnRow in keyColumnRows) { key.AddColumn((string)keyColumnRow["COLUMN_NAME"]); } key.ResetDefaults(); table.AddKey(key); } }
public void The_Original_Database_Should_Contain_A_Copy_Of_The_New_Key() { var db1 = TestDatabaseLoader.TestDatabase(); // Setup new key information Table parentTable = new Table("Table1"); parentTable.AddColumn(new Column("Column3")); Key newKey = new Key("UQ_Column3"); newKey.Parent = parentTable; newKey.AddColumn("Column3"); KeyAdditionOperation op = new KeyAdditionOperation(db1.Tables[0], newKey); op.RunOperation(); op.RunSecondStep(); Assert.That(db1.Tables[0].Keys, Has.Count(2)); Assert.That(db1.Tables[0].Keys[1].Name, Is.EqualTo(newKey.Name)); Assert.That(db1.Tables[0].Keys[1], Is.Not.SameAs(newKey), "The added Key should be a copy, not the original object."); Assert.That(db1.Tables[0].Keys[1].Columns[0], Is.SameAs(db1.Tables[0].Columns[2]), "The new key should reference existing columns, not the new ones"); Assert.That(db1.Tables[0].Keys[1].Parent, Is.SameAs(db1.Tables[0]), "The new key's parent should be Table1 in the existing database"); }
public void The_Original_Foreign_Key_Should_Be_Modified() { var db1 = RelationshipDatabaseLoader.GetDb(); IKey originalKey = db1.Tables[1].Keys[0]; originalKey.Description = "old description"; IKey newPrimaryKey = new Key(db1.Tables[0].Keys[0].Name); newPrimaryKey.Parent = new Table("Table1"); newPrimaryKey.Parent.AddColumn(new Column("Column1")); newPrimaryKey.Description = "new description"; newPrimaryKey.AddColumn("Column1"); IKey newForeignKey = new Key(originalKey.Name); newForeignKey.Parent = new Table("Table2"); newForeignKey.Parent.AddColumn(new Column("Column2")); newForeignKey.Parent.AddColumn(new Column("Column3")); newForeignKey.Description = "new description"; newForeignKey.AddColumn("Column3"); // This is the change. newForeignKey.ReferencedKey = newPrimaryKey; KeyChangeOperation op = new KeyChangeOperation(db1.Tables[1].Keys[0], newForeignKey); op.RunOperation(); op.RunSecondStep(); IKey key = db1.Tables[1].Keys[0]; Assert.That(db1.Tables[1].Keys, Has.Count(1)); Assert.That(key, Is.SameAs(originalKey), "Key object should still be the same"); Assert.That(key.Description, Is.EqualTo("old description"), "Should not change user set properties."); Assert.That(key.ReferencedKey, Is.SameAs(db1.Tables[0].Keys[0])); Assert.That(key.Columns, Has.Count(1)); Assert.That(key.Columns[0].Name, Is.EqualTo("Column3")); Assert.That(key.Columns[0], Is.Not.SameAs(newForeignKey.Columns[0])); Assert.That(key.Columns[0], Is.SameAs(db1.Tables[1].Columns[2]), "The new key should reference existing columns"); Assert.That(key.Parent, Is.SameAs(db1.Tables[1]), "The new key's parent should be Table1 in the existing database"); }
public IKey ProcessKeyNode(XmlNode node, ITable parent, IDatabase db) { IKey key = new Key(); key.Parent = parent; ProcessScriptBase(key, node); /* <Keytype>Primary</Keytype> <Columns> <ColumnName>ColumnT11</ColumnName> </Columns> <ReferencedKey> <KeyName>ForeignKey</KeyName> <TableName>Table2</TableName> </ReferencedKey> */ NodeProcessor proc = new NodeProcessor(node); key.Keytype = proc.GetEnum<DatabaseKeyType>("Keytype"); key.IsUnique = proc.GetBool("IsUnique"); var columnNodeList = node.SelectNodes("Columns/ColumnName"); if (columnNodeList != null) { foreach (XmlNode columnRef in columnNodeList) { key.AddColumn(columnRef.InnerText); } } return key; }
private Model.Table GetNewTable(string schema, string tableName) { Interfaces.Events.RaiseObjectBeingProcessedEvent(tableName, "Table"); Model.Table table = new Model.Table(tableName, false); #region Columns DataRow[] columnRows = Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", schema, tableName)); foreach (DataRow columnRow in columnRows) { bool isReadOnly = false; if (!columnRow.IsNull("IsIdentity") && (int)columnRow["IsIdentity"] == 1) { isReadOnly = true; } else if (!columnRow.IsNull("IsComputed") && (int)columnRow["IsComputed"] == 1) { isReadOnly = true; } else if (Slyce.Common.Utility.StringsAreEqual((string)columnRow["DATA_TYPE"], "timestamp", false)) { isReadOnly = true; } Column column = new Column( (string)columnRow["COLUMN_NAME"], false, table, (int)columnRow["ORDINAL_POSITION"], Slyce.Common.Utility.StringsAreEqual((string)columnRow["IS_NULLABLE"], "YES", false), (string)columnRow["DATA_TYPE"], columnRow.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : Convert.ToInt32(columnRow["CHARACTER_MAXIMUM_LENGTH"]), (int)columnRow["InPrimaryKey"] == 1, columnRow.IsNull("IsIdentity") ? false : Convert.ToInt32(columnRow["IsIdentity"]) == 1, columnRow.IsNull("COLUMN_DEFAULT") ? "" : (string)columnRow["COLUMN_DEFAULT"], isReadOnly, columnRow.IsNull("IsComputed") ? false : Convert.ToInt32(columnRow["IsComputed"]) == 1, columnRow.IsNull("NUMERIC_PRECISION") ? 0 : Convert.ToInt32(columnRow["NUMERIC_PRECISION"]), columnRow.IsNull("NUMERIC_SCALE") ? 0 : Convert.ToInt32(columnRow["NUMERIC_SCALE"])); if (IsSupported(column)) { table.AddColumn(column); } } #endregion #region Indexes DataRow[] indexRows = Indexes.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", schema, tableName)); //foreach (DataRow indexRow in indexRows) for (int i = 0; i < indexRows.Length; i++) { DataRow indexRow = indexRows[i]; string indexType; string indexKeyType = indexRow["CONSTRAINT_TYPE"].ToString(); if (indexKeyType == "PRIMARY KEY") { indexType = DatabaseConstant.IndexType.PrimaryKey; //continue; } else if (indexKeyType == "FOREIGN KEY") { indexType = DatabaseConstant.IndexType.ForeignKey; //continue; } else if (indexKeyType == "UNIQUE") { //continue; indexType = DatabaseConstant.IndexType.Unique; } else if (indexKeyType == "CHECK") { indexType = DatabaseConstant.IndexType.Check; } else if (indexKeyType == "NONE") //TODO check is NONE { indexType = DatabaseConstant.IndexType.None; } else { //continue; throw new Exception("IndexType " + indexKeyType + " Not Defined"); } List <DataRow> indexColumnRows = new List <DataRow>();// = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'", tableName, indexRow["CONSTRAINT_NAME"])); indexColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", schema, tableName, indexRow["COLUMN_NAME"]))); while ((i < indexRows.Length - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == tableName && (string)indexRows[i + 1]["CONSTRAINT_NAME"] == (string)indexRow["CONSTRAINT_NAME"]) { i++; indexRow = indexRows[i]; indexColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", schema, tableName, indexRow["COLUMN_NAME"]))); } bool isUnique = (int)indexRow["IS_UNIQUE"] == 1 ? true : false; bool isClustered = (int)indexRow["IS_CLUSTERED"] == 1 ? true : false; Index index = new Index(indexRow["CONSTRAINT_NAME"].ToString(), false, indexType, table, isUnique, isClustered); // Fill Columns foreach (DataRow indexColumnRow in indexColumnRows) { Column indexColumn = new Column(indexColumnRow["COLUMN_NAME"].ToString(), false); index.AddColumn(indexColumn); } index.ResetDefaults(); table.AddIndex(index); } // Indexes -- that should be keys DataRow[] keyRows = Indexes.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", schema, tableName)); //foreach (DataRow keyRow in indexRows) for (int i = 0; i < keyRows.Length; i++) { DataRow keyRow = keyRows[i]; string keyType; string indexKeyType = keyRow["CONSTRAINT_TYPE"].ToString(); if (indexKeyType == "PRIMARY KEY") { keyType = DatabaseConstant.KeyType.Primary; } else if (indexKeyType == "FOREIGN KEY") { keyType = DatabaseConstant.KeyType.Foreign; } else if (indexKeyType == "UNIQUE") { keyType = DatabaseConstant.KeyType.Unique; } else { continue; } Key key = new Key(keyRow["CONSTRAINT_NAME"].ToString(), false, keyType, table, false); // Fill Columns List <DataRow> keyColumnRows = new List <DataRow>(); keyColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", schema, tableName, keyRow["COLUMN_NAME"]))); while ((i < keyRows.Length - 1) && (string)keyRows[i + 1]["TABLE_NAME"] == tableName && (string)keyRows[i + 1]["CONSTRAINT_NAME"] == (string)keyRow["CONSTRAINT_NAME"]) { i++; keyRow = keyRows[i]; keyColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", schema, tableName, keyRow["COLUMN_NAME"]))); } // Fill Columns foreach (DataRow keyColumnRow in keyColumnRows) { Column keyColumn = new Column(keyColumnRow["COLUMN_NAME"].ToString(), false); keyColumn.DataType = (string)keyColumnRow["DATA_TYPE"]; key.AddColumn(keyColumn); } if (keyType == DatabaseConstant.KeyType.Foreign) { DataRow[] keyReferencedColumnRows = IndexReferencedColumns.Select(string.Format("ForeignKeySchema = '{0}' AND ForeignKeyTable = '{1}' AND ForeignKey = '{2}'", schema, tableName, keyRow["CONSTRAINT_NAME"])); DataRow firstKeyReferencedColumnRow = keyReferencedColumnRows[0]; // Fill References key.ReferencedTable = new Model.Table(firstKeyReferencedColumnRow["ReferencedTable"].ToString(), false) { Schema = firstKeyReferencedColumnRow["ReferencedSchema"].ToString() }; key.ReferencedKey = new Key(firstKeyReferencedColumnRow["ReferencedKey"].ToString(), false, true); // Fill Referenced Columns foreach (DataRow keyReferencedColumnRow in keyReferencedColumnRows) { Column keyReferencedColumn = new Column(keyReferencedColumnRow["ReferencedColumn"].ToString(), false); key.AddReferencedColumn(keyReferencedColumn); } } key.ResetDefaults(); table.AddKey(key); } #endregion return(table); }
private Model.Table GetNewTable(string tableName) { ArchAngel.Interfaces.ProjectHelper.RaiseObjectBeingProcessedEvent(tableName, "Table"); //_columns = null; // Reset the columns //_indexColumns = null; //_indexes = null; //_indexReferencedColumns = null; //_referencedColumns = null; Model.Table table = new Model.Table(tableName, false); #region Columns DataRow[] columnRows = Columns.Select(string.Format("TABLE_NAME = '{0}'", tableName)); foreach (DataRow columnRow in columnRows) { Column column = new Column( (string)columnRow["COLUMN_NAME"], false, table, (int)columnRow["ORDINAL_POSITION"], Slyce.Common.Utility.StringsAreEqual((string)columnRow["IS_NULLABLE"], "YES", false), (string)columnRow["DATA_TYPE"], columnRow.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : (int)columnRow["CHARACTER_MAXIMUM_LENGTH"], (int)columnRow["InPrimaryKey"] == 1, columnRow.IsNull("IsIdentity") ? false : (int)columnRow["IsIdentity"] == 1, columnRow.IsNull("COLUMN_DEFAULT") ? "" : (string)columnRow["COLUMN_DEFAULT"], columnRow.IsNull("IsComputed") ? false : (int)columnRow["IsComputed"] == 1); if (IsSupported(column)) { table.AddColumn(column); } } #endregion #region Indexes DataRow[] indexRows = Indexes.Select(string.Format("TABLE_NAME = '{0}'", tableName)); foreach (DataRow indexRow in indexRows) { string indexType; string indexKeyType = indexRow["CONSTRAINT_TYPE"].ToString(); if (indexKeyType == "PRIMARY KEY") { continue; } else if (indexKeyType == "FOREIGN KEY") { continue; } else if (indexKeyType == "UNIQUE") { continue; //indexType = DatabaseConstant.IndexType.Unique; } else if (indexKeyType == "CHECK") { indexType = DatabaseConstant.IndexType.Check; } else if (indexKeyType == "NONE") //TODO check is NONE { indexType = DatabaseConstant.IndexType.None; } else { //continue; throw new Exception("IndexType " + indexKeyType + " Not Defined"); } DataRow[] indexColumnRows;// = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'", tableName, indexRow["CONSTRAINT_NAME"])); if (indexKeyType == "NONE") { indexColumnRows = Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", tableName, indexRow["ColumnName"])); } else { indexColumnRows = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'", tableName, indexRow["CONSTRAINT_NAME"])); } Index index = new Index(indexRow["CONSTRAINT_NAME"].ToString(), false, indexType, table); // Fill Columns foreach (DataRow indexColumnRow in indexColumnRows) { Column indexColumn = new Column(indexColumnRow["COLUMN_NAME"].ToString(), false); index.AddColumn(indexColumn); } index.ResetDefaults(); table.AddIndex(index); } // Indexes -- that should be keys foreach (DataRow keyRow in indexRows) { string keyType; string indexKeyType = keyRow["CONSTRAINT_TYPE"].ToString(); if (indexKeyType == "PRIMARY KEY") { keyType = DatabaseConstant.KeyType.Primary; } else if (indexKeyType == "FOREIGN KEY") { keyType = DatabaseConstant.KeyType.Foreign; } else if (indexKeyType == "UNIQUE") { keyType = DatabaseConstant.KeyType.Unique; } else if (indexKeyType == "CHECK") { continue; } else if (indexKeyType == "NONE") { continue; //keyType = DatabaseConstant.KeyType.None; } else { //continue; throw new Exception("KeyType " + indexKeyType + " Not Defined"); } // Create Alias string keyAlias = keyType + "_"; DataRow[] keyColumnRows = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'", tableName, keyRow["CONSTRAINT_NAME"])); Key key = new Key(keyRow["CONSTRAINT_NAME"].ToString(), false, keyType, table, false); // Fill Columns foreach (DataRow keyColumnRow in keyColumnRows) { Column keyColumn = new Column(keyColumnRow["COLUMN_NAME"].ToString(), false); keyColumn.DataType = (string)keyColumnRow["DATA_TYPE"]; key.AddColumn(keyColumn); } if (keyType == DatabaseConstant.KeyType.Foreign) { DataRow[] keyReferencedColumnRows = IndexReferencedColumns.Select(string.Format("ForeignKey = '{0}'", keyRow["CONSTRAINT_NAME"])); DataRow firstKeyReferencedColumnRow = keyReferencedColumnRows[0]; // Fill References key.ReferencedTable = new Model.Table(firstKeyReferencedColumnRow["ReferencedTable"].ToString(), false); //if (dmoKey.ReferencedKey != null) //{ key.ReferencedKey = new Key(firstKeyReferencedColumnRow["ReferencedKey"].ToString(), false, true); //} // Fill Referenced Columns foreach (DataRow keyReferencedColumnRow in keyReferencedColumnRows) { Column keyReferencedColumn = new Column(keyReferencedColumnRow["ReferencedColumn"].ToString(), false); key.AddReferencedColumn(keyReferencedColumn); } } key.ResetDefaults(); table.AddKey(key); } #endregion return(table); }
private void GetKeys(ITable table) { List <DataRow> keyRows = connector.GetIndexRows(table.Schema, table.Name.Replace("'", "''")); for (int i = 0; i < keyRows.Count; i++) { DataRow keyRow = keyRows[i]; DatabaseKeyType keyType; string indexKeyType = keyRow["CONSTRAINT_TYPE"].ToString(); /*if (indexKeyType == "PRIMARY KEY") * keyType = DatabaseKeyType.Primary; * else*/ if (indexKeyType == "UNIQUE") { keyType = DatabaseKeyType.Unique; } else { continue; } Key key = new Key { Name = keyRow["INDEX_NAME"].ToString(), IsUserDefined = false, Keytype = keyType, Parent = table, IsUnique = true }; // Fill Columns List <DataRow> keyColumnRows = new List <DataRow>(); keyColumnRows.AddRange(connector.ColumnsBySchema[table.Schema][table.Name.Replace("'", "''")].Where(r => r[OrdColumn].ToString() == keyRow["COLUMN_NAME"].ToString())); while ((i < keyRows.Count - 1) && (string)keyRows[i + 1]["TABLE_NAME"] == table.Name && (string)keyRows[i + 1]["INDEX_NAME"] == (string)keyRow["INDEX_NAME"]) { i++; keyRow = keyRows[i]; keyColumnRows.AddRange(connector.ColumnsBySchema[table.Schema][table.Name.Replace("'", "''")].Where(c => c[OrdColumn].ToString() == keyRow["COLUMN_NAME"].ToString())); } // Fill Columns foreach (DataRow keyColumnRow in keyColumnRows) { key.AddColumn((string)keyColumnRow["COLUMN_NAME"]); } key.ResetDefaults(); table.AddKey(key); } // Add Primary Keys var primaryKeyNames = connector.PrimaryKeyNames; if (primaryKeyNames.ContainsKey(table.Schema) && primaryKeyNames[table.Schema].ContainsKey(table.Name)) { Key key = new Key { Name = primaryKeyNames[table.Schema][table.Name], IsUserDefined = false, Keytype = DatabaseKeyType.Primary, Parent = table, IsUnique = true }; // Fill Columns foreach (var column in table.ColumnsInPrimaryKey) { key.AddColumn(column.Name); } key.ResetDefaults(); table.AddKey(key); } }
private void GetForeignKeys(IDatabase db) { DataTable keysTable = connector.GetForeignKeyConstraints(); foreach (DataRow keyRow in keysTable.Rows) { ITable parentTable = db.GetTable((string)keyRow["PrimaryTable"], ""); ITable foreignTable = db.GetTable((string)keyRow["ForeignTable"], ""); // Don't process keys of tables that the user hasn't selected if (parentTable == null || foreignTable == null) continue; Key key = new Key { Name = keyRow["PrimaryKeyName"].ToString(), IsUserDefined = false, Keytype = DatabaseKeyType.Foreign, Parent = parentTable }; ////////////////// key.IsUnique = connector.GetUniqueIndexes().Select(string.Format("INDEX_NAME = '{0}'", key.Name)).Length > 0; //foreignTable.AddKey(key); ////////////////// // Get Foreign Key columns DataRow[] foreignKeyColumns = connector.GetForeignKeyColumns().Select(string.Format("CONSTRAINT_NAME = '{0}' AND TABLE_NAME = '{1}'", key.Name.Replace("'", "''"), foreignTable.Name)); foreach (DataRow row in foreignKeyColumns) key.AddColumn((string)row["COLUMN_NAME"]); DataRow[] keyReferencedColumnRows = connector.IndexReferencedColumns.Select(string.Format("ForeignKey = '{0}'", keyRow["CONSTRAINT_NAME"])); DataRow firstKeyReferencedColumnRow = keyReferencedColumnRows[0]; // Fill References key.ReferencedKey = foreignTable.GetKey((string)firstKeyReferencedColumnRow["ReferencedKey"]); parentTable.AddKey(key); } return; }
private void GetKeys(ITable table) { List<DataRow> keyRows = connector.GetIndexRows(table.Schema, table.Name.Replace("'", "''")); for (int i = 0; i < keyRows.Count; i++) { DataRow keyRow = keyRows[i]; DatabaseKeyType keyType; string indexKeyType = keyRow["CONSTRAINT_TYPE"].ToString(); /*if (indexKeyType == "PRIMARY KEY") keyType = DatabaseKeyType.Primary; else*/ if (indexKeyType == "UNIQUE") keyType = DatabaseKeyType.Unique; else continue; Key key = new Key { Name = keyRow["INDEX_NAME"].ToString(), IsUserDefined = false, Keytype = keyType, Parent = table, IsUnique = true }; // Fill Columns List<DataRow> keyColumnRows = new List<DataRow>(); keyColumnRows.AddRange(connector.ColumnsBySchema[table.Schema][table.Name.Replace("'", "''")].Where(r => r[OrdColumn].ToString() == keyRow["COLUMN_NAME"].ToString())); while ((i < keyRows.Count - 1) && (string)keyRows[i + 1]["TABLE_NAME"] == table.Name && (string)keyRows[i + 1]["INDEX_NAME"] == (string)keyRow["INDEX_NAME"]) { i++; keyRow = keyRows[i]; keyColumnRows.AddRange(connector.ColumnsBySchema[table.Schema][table.Name.Replace("'", "''")].Where(c => c[OrdColumn].ToString() == keyRow["COLUMN_NAME"].ToString())); } // Fill Columns foreach (DataRow keyColumnRow in keyColumnRows) key.AddColumn((string)keyColumnRow["COLUMN_NAME"]); key.ResetDefaults(); table.AddKey(key); } // Add Primary Keys var primaryKeyNames = connector.PrimaryKeyNames; if (primaryKeyNames.ContainsKey(table.Schema) && primaryKeyNames[table.Schema].ContainsKey(table.Name)) { Key key = new Key { Name = primaryKeyNames[table.Schema][table.Name], IsUserDefined = false, Keytype = DatabaseKeyType.Primary, Parent = table, IsUnique = true }; // Fill Columns foreach (var column in table.ColumnsInPrimaryKey) key.AddColumn(column.Name); key.ResetDefaults(); table.AddKey(key); } }
private Model.Table GetNewTable(string schema, string tableName) { Interfaces.Events.RaiseObjectBeingProcessedEvent(tableName, "Table"); Model.Table table = new Model.Table(tableName, false); #region Columns DataRow[] columnRows = Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", schema, tableName)); foreach (DataRow columnRow in columnRows) { bool isReadOnly = false; if (!columnRow.IsNull("IsIdentity") && (int)columnRow["IsIdentity"] == 1) { isReadOnly = true; } else if (!columnRow.IsNull("IsComputed") && (int)columnRow["IsComputed"] == 1) { isReadOnly = true; } else if (Slyce.Common.Utility.StringsAreEqual((string)columnRow["DATA_TYPE"], "timestamp", false)) { isReadOnly = true; } Column column = new Column( (string)columnRow["COLUMN_NAME"], false, table, (int)columnRow["ORDINAL_POSITION"], Slyce.Common.Utility.StringsAreEqual((string)columnRow["IS_NULLABLE"], "YES", false), (string)columnRow["DATA_TYPE"], columnRow.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : Convert.ToInt32(columnRow["CHARACTER_MAXIMUM_LENGTH"]), (int)columnRow["InPrimaryKey"] == 1, columnRow.IsNull("IsIdentity") ? false : Convert.ToInt32(columnRow["IsIdentity"]) == 1, columnRow.IsNull("COLUMN_DEFAULT") ? "" : (string)columnRow["COLUMN_DEFAULT"], isReadOnly, columnRow.IsNull("IsComputed") ? false : Convert.ToInt32(columnRow["IsComputed"]) == 1, columnRow.IsNull("NUMERIC_PRECISION") ? 0 : Convert.ToInt32(columnRow["NUMERIC_PRECISION"]), columnRow.IsNull("NUMERIC_SCALE") ? 0 : Convert.ToInt32(columnRow["NUMERIC_SCALE"])); if (IsSupported(column)) { table.AddColumn(column); } } #endregion #region Indexes DataRow[] indexRows = Indexes.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", schema, tableName)); //foreach (DataRow indexRow in indexRows) for (int i = 0; i < indexRows.Length; i++) { DataRow indexRow = indexRows[i]; string indexType; string indexKeyType = indexRow["CONSTRAINT_TYPE"].ToString(); if (indexKeyType == "PRIMARY KEY") { indexType = DatabaseConstant.IndexType.PrimaryKey; //continue; } else if (indexKeyType == "FOREIGN KEY") { indexType = DatabaseConstant.IndexType.ForeignKey; //continue; } else if (indexKeyType == "UNIQUE") { //continue; indexType = DatabaseConstant.IndexType.Unique; } else if (indexKeyType == "CHECK") { indexType = DatabaseConstant.IndexType.Check; } else if (indexKeyType == "NONE") //TODO check is NONE { indexType = DatabaseConstant.IndexType.None; } else { //continue; throw new Exception("IndexType " + indexKeyType + " Not Defined"); } List<DataRow> indexColumnRows = new List<DataRow>();// = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'", tableName, indexRow["CONSTRAINT_NAME"])); indexColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", schema, tableName, indexRow["COLUMN_NAME"]))); while ((i < indexRows.Length - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == tableName && (string)indexRows[i + 1]["CONSTRAINT_NAME"] == (string)indexRow["CONSTRAINT_NAME"]) { i++; indexRow = indexRows[i]; indexColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", schema, tableName, indexRow["COLUMN_NAME"]))); } bool isUnique = (int)indexRow["IS_UNIQUE"] == 1 ? true : false; bool isClustered = (int)indexRow["IS_CLUSTERED"] == 1 ? true : false; Index index = new Index(indexRow["CONSTRAINT_NAME"].ToString(), false, indexType, table, isUnique, isClustered); // Fill Columns foreach (DataRow indexColumnRow in indexColumnRows) { Column indexColumn = new Column(indexColumnRow["COLUMN_NAME"].ToString(), false); index.AddColumn(indexColumn); } index.ResetDefaults(); table.AddIndex(index); } // Indexes -- that should be keys DataRow[] keyRows = Indexes.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", schema, tableName)); //foreach (DataRow keyRow in indexRows) for (int i = 0; i < keyRows.Length; i++) { DataRow keyRow = keyRows[i]; string keyType; string indexKeyType = keyRow["CONSTRAINT_TYPE"].ToString(); if (indexKeyType == "PRIMARY KEY") { keyType = DatabaseConstant.KeyType.Primary; } else if (indexKeyType == "FOREIGN KEY") { keyType = DatabaseConstant.KeyType.Foreign; } else if (indexKeyType == "UNIQUE") { keyType = DatabaseConstant.KeyType.Unique; } else { continue; } Key key = new Key(keyRow["CONSTRAINT_NAME"].ToString(), false, keyType, table, false); // Fill Columns List<DataRow> keyColumnRows = new List<DataRow>(); keyColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", schema, tableName, keyRow["COLUMN_NAME"]))); while ((i < keyRows.Length - 1) && (string)keyRows[i + 1]["TABLE_NAME"] == tableName && (string)keyRows[i + 1]["CONSTRAINT_NAME"] == (string)keyRow["CONSTRAINT_NAME"]) { i++; keyRow = keyRows[i]; keyColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", schema, tableName, keyRow["COLUMN_NAME"]))); } // Fill Columns foreach (DataRow keyColumnRow in keyColumnRows) { Column keyColumn = new Column(keyColumnRow["COLUMN_NAME"].ToString(), false); keyColumn.DataType = (string)keyColumnRow["DATA_TYPE"]; key.AddColumn(keyColumn); } if (keyType == DatabaseConstant.KeyType.Foreign) { DataRow[] keyReferencedColumnRows = IndexReferencedColumns.Select(string.Format("ForeignKeySchema = '{0}' AND ForeignKeyTable = '{1}' AND ForeignKey = '{2}'", schema, tableName, keyRow["CONSTRAINT_NAME"])); DataRow firstKeyReferencedColumnRow = keyReferencedColumnRows[0]; // Fill References key.ReferencedTable = new Model.Table(firstKeyReferencedColumnRow["ReferencedTable"].ToString(), false) { Schema = firstKeyReferencedColumnRow["ReferencedSchema"].ToString() }; key.ReferencedKey = new Key(firstKeyReferencedColumnRow["ReferencedKey"].ToString(), false, true); // Fill Referenced Columns foreach (DataRow keyReferencedColumnRow in keyReferencedColumnRows) { Column keyReferencedColumn = new Column(keyReferencedColumnRow["ReferencedColumn"].ToString(), false); key.AddReferencedColumn(keyReferencedColumn); } } key.ResetDefaults(); table.AddKey(key); } #endregion return table; }
private Model.Table GetNewTable(Microsoft.SqlServer.Management.Smo.Table smoTable) { Model.Table table = new Model.Table(smoTable.Name, Script.GetSingluar(smoTable.Name), false); #region Columns int ordinalPosition = 0; List <Microsoft.SqlServer.Management.Smo.Column> smoColumns = new List <Microsoft.SqlServer.Management.Smo.Column>(); foreach (Microsoft.SqlServer.Management.Smo.Column smoColumn in smoTable.Columns) { smoColumns.Add(smoColumn); } foreach (Microsoft.SqlServer.Management.Smo.Column smoColumn in smoColumns) { if (UnsupportedDataTypes.ToLower().IndexOf("'" + smoColumn.DataType.Name.ToLower() + "'") >= 0) { continue; } // Some columns do not have default values string defaultValue = null; try { defaultValue = smoColumn.Default; } catch { } Column column = new Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), false, smoColumn.Name, table, ordinalPosition, smoColumn.Nullable, smoColumn.DataType.Name, smoColumn.DataType.MaximumLength, smoColumn.InPrimaryKey, smoColumn.Identity, defaultValue, smoColumn.Computed); table.AddColumn(column); ordinalPosition++; } #endregion #region Indexes //foreach (Microsoft.SqlServer.Management.Smo.Index smoIndex in smoTable.Indexes) //{ // string indexType; // string indexKeyType = smoIndex.IndexKeyType.ToString(); // if (indexKeyType == "DriPrimaryKey") // { // continue; // } // else if (indexKeyType == "DriUniqueKey") // { // continue; // } // else if (indexKeyType == "None") // { // continue; // //indexType = DatabaseConstant.IndexType.None; // } // else // { // throw new Exception("IndexType " + indexKeyType + " Not Defined"); // } // // Create Alias // string indexAlias = indexType + "_"; // for (int i = 0; i <= smoIndex.IndexedColumns.Count - 1; i++) // { // Microsoft.SqlServer.Management.Smo.IndexedColumn smoIndexedColumn = smoIndex.IndexedColumns[i]; // indexAlias += smoIndexedColumn.Name; // if (i < smoIndex.IndexedColumns.Count - 1) // { // indexAlias += "And"; // } // } // Index index = new Index(smoIndex.Name, Script.GetSingluar(indexAlias), false, indexType, table); // // Fill Columns // foreach (Microsoft.SqlServer.Management.Smo.IndexedColumn smoColumn in smoIndex.IndexedColumns) // { // Column indexColumn = new Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), false); // index.AddColumn(indexColumn); // } // table.AddIndex(index); //} // Indexes -- that should be keys foreach (Microsoft.SqlServer.Management.Smo.Index smoIndex in smoTable.Indexes) { string keyType; string indexKeyType = smoIndex.IndexKeyType.ToString(); if (indexKeyType == "DriPrimaryKey") { keyType = DatabaseConstant.KeyType.Primary; } else if (indexKeyType == "DriUniqueKey") { keyType = DatabaseConstant.KeyType.Unique; } else if (indexKeyType == "None") { keyType = DatabaseConstant.KeyType.None; continue; } else { throw new Exception("KeyType " + indexKeyType + " Not Defined"); } // Create Alias string keyAlias = keyType + "_"; for (int i = 0; i <= smoIndex.IndexedColumns.Count - 1; i++) { Microsoft.SqlServer.Management.Smo.Column smoColumn = smoTable.Columns[smoIndex.IndexedColumns[i].Name]; keyAlias += smoColumn.Name; if (i < smoIndex.IndexedColumns.Count - 1) { keyAlias += "And"; } } Key key = new Key(smoIndex.Name, Script.GetSingluar(keyAlias), false, keyType, table); // Fill Columns foreach (Microsoft.SqlServer.Management.Smo.IndexedColumn smoColumn in smoIndex.IndexedColumns) { Column keyColumn = new Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), false); key.AddColumn(keyColumn); } table.AddKey(key); } #endregion #region Keys foreach (Microsoft.SqlServer.Management.Smo.ForeignKey smoForeignKey in smoTable.ForeignKeys) { string keyType = DatabaseConstant.KeyType.Foreign; // Create Alias string keyAlias = keyType + "_"; for (int i = 0; i <= smoForeignKey.Columns.Count - 1; i++) { Microsoft.SqlServer.Management.Smo.Column smoColumn = smoTable.Columns[smoForeignKey.Columns[i].Name]; keyAlias += smoColumn.Name; if (i < smoForeignKey.Columns.Count - 1) { keyAlias += "And"; } } Key key = new Key(smoForeignKey.Name, Script.GetSingluar(keyAlias), false, keyType, table); // Fill Columns foreach (Microsoft.SqlServer.Management.Smo.ForeignKeyColumn smoColumn in smoForeignKey.Columns) { Column keyColumn = new Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), false); key.AddColumn(keyColumn); } // Fill References key.ReferencedTable = new Model.Table(smoForeignKey.ReferencedTable, Script.GetSingluar(smoForeignKey.ReferencedTable), false); key.ReferencedKey = new Key(smoForeignKey.ReferencedKey, Script.GetSingluar(smoForeignKey.ReferencedKey), false); // Fill Referenced Columns foreach (Microsoft.SqlServer.Management.Smo.ForeignKeyColumn smoColumn in smoForeignKey.Columns) { Column referencedKeyColumn = new Column(smoColumn.ReferencedColumn, Script.GetSingluar(smoColumn.ReferencedColumn), false); key.AddReferencedColumn(referencedKeyColumn); } table.AddKey(key); } #endregion return(table); }
private void Save() { if (RelatedTable == null) { MessageBox.Show(this, "You need to select a foreign table.", "Foreign table missing", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } if (RelatedColumnCombos.Count == 0) { MessageBox.Show(this, "No columns selected.", "Columns missing", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } bool mainColsAreUnique;// = checkBoxPrimaryUnique.Checked ? true : ColumnsAreUnique(GetMainColumns()); bool relatedColsAreUnique;// = checkBoxForeignUnique.Checked ? true : ColumnsAreUnique(GetRelatedColumns()); // Check if the Main columns are unique if (checkBoxPrimaryUnique.Checked) mainColsAreUnique = true; else { List<Column> mainColumns = GetMainColumns(); bool selectedColsContainAllPrimaryCols = MainTable.ColumnsInPrimaryKey.Count() > 0; foreach (var col in MainTable.ColumnsInPrimaryKey) { if (!mainColumns.Contains(col)) { selectedColsContainAllPrimaryCols = false; break; } } if (selectedColsContainAllPrimaryCols) mainColsAreUnique = true; else mainColsAreUnique = ColumnsAreUnique(mainColumns); } // Check if the Realted columns are unique if (checkBoxForeignUnique.Checked) relatedColsAreUnique = true; else { List<Column> relatedColumns = GetRelatedColumns(); bool selectedColsContainAllPrimaryCols = RelatedTable.ColumnsInPrimaryKey.Count() > 0; foreach (var col in RelatedTable.ColumnsInPrimaryKey) { if (!relatedColumns.Contains(col)) { selectedColsContainAllPrimaryCols = false; break; } } if (selectedColsContainAllPrimaryCols) relatedColsAreUnique = true; else relatedColsAreUnique = ColumnsAreUnique(relatedColumns); } IKey mainTableKey = null; foreach (var key in MainTable.Keys) { if (MainColumnCombos.Count == key.Columns.Count) { bool keyMatches = true; foreach (var combo in MainColumnCombos) { if (!key.Columns.Contains((Column)((DisplayItem)combo.SelectedItem).Value)) { keyMatches = false; break; } } if (keyMatches) { // Key is found mainTableKey = key; break; } } } IKey relatedTableKey = null; foreach (var key in RelatedTable.Keys) { if (RelatedColumnCombos.Count == key.Columns.Count) { bool keyMatches = true; foreach (var combo in RelatedColumnCombos) { // Can't have two primary keys, but there might be a foreign key on the same columns, so skip for now. Otherwise we'll need to create a new foreign key anyway. if (!key.Columns.Contains((Column)((DisplayItem)combo.SelectedItem).Value) && !((mainTableKey != null && mainTableKey.Keytype == DatabaseKeyType.Primary) && key.Keytype == DatabaseKeyType.Primary)) { keyMatches = false; break; } } if (keyMatches) { // Key is found relatedTableKey = key; break; } } } if (mainTableKey != null && relatedTableKey != null) { // Both keys exist, so let's check whether this relationship already exists foreach (var relationship in MainTable.Relationships) { if ((relationship.PrimaryKey == mainTableKey && relationship.ForeignKey == relatedTableKey) || (relationship.PrimaryKey == relatedTableKey && relationship.ForeignKey == mainTableKey)) { MessageBox.Show(this, "This relationship already exists.", "Relationship exists", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } } } if (mainTableKey != null && relatedTableKey != null && (mainTableKey.Keytype == DatabaseKeyType.Primary && relatedTableKey.Keytype == DatabaseKeyType.Primary)) { // A relationship can't have two primary keys, so we'll need to create a new 'foreign-key' relatedTableKey = null; } RelatedTable = (Table)comboBoxForeignTable.SelectedItem; if (mainTableKey == null) { mainTableKey = new Key { Name = string.Format("FK_{0}_{1}", MainTable.Name, RelatedTable.Name).GetNextName(MainTable.Relationships.Select(r => r.Name).ToList()), IsUserDefined = true, Keytype = DatabaseKeyType.Foreign, Parent = MainTable, IsUnique = mainColsAreUnique }; foreach (Column column in GetMainColumns()) mainTableKey.AddColumn(column.Name); } if (relatedTableKey == null) { relatedTableKey = new Key { Name = string.Format("FK_{0}_{1}", RelatedTable.Name, MainTable.Name).GetNextName(RelatedTable.Relationships.Select(r => r.Name).ToList()), IsUserDefined = true, Keytype = DatabaseKeyType.Foreign, Parent = RelatedTable, IsUnique = relatedColsAreUnique }; foreach (Column column in GetRelatedColumns()) relatedTableKey.AddColumn(column.Name); } mainTableKey.ReferencedKey = relatedTableKey; relatedTableKey.ReferencedKey = mainTableKey; if (Relationship == null) { Relationship newRelationship = new RelationshipImpl(); newRelationship.Database = MainTable.Database; newRelationship.IsUserDefined = true; newRelationship.Schema = MainTable.Schema; if (relatedTableKey.Keytype == DatabaseKeyType.Primary) { newRelationship.AddThisTo(RelatedTable, MainTable); newRelationship.Name = string.Format("FK_NH_{0}_{1}", RelatedTable, MainTable).GetNextName(RelatedTable.Relationships.Select(r => r.Name).ToList()); //newRelationship.PrimaryCardinality = mainColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; //newRelationship.ForeignCardinality = relatedColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; newRelationship.ForeignCardinality = mainColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; newRelationship.PrimaryCardinality = relatedColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; newRelationship.PrimaryKey = relatedTableKey; newRelationship.ForeignKey = mainTableKey; if (!newRelationship.PrimaryTable.Keys.Contains(relatedTableKey)) newRelationship.PrimaryTable.AddKey(relatedTableKey); if (!newRelationship.ForeignTable.Keys.Contains(mainTableKey)) newRelationship.ForeignTable.AddKey(mainTableKey); } else { newRelationship.AddThisTo(MainTable, RelatedTable); newRelationship.Name = string.Format("FK_NH_{0}_{1}", MainTable, RelatedTable); //newRelationship.PrimaryCardinality = mainColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; //newRelationship.ForeignCardinality = relatedColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; newRelationship.ForeignCardinality = mainColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; newRelationship.PrimaryCardinality = relatedColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; newRelationship.PrimaryKey = mainTableKey; newRelationship.ForeignKey = relatedTableKey; if (!newRelationship.PrimaryTable.Keys.Contains(mainTableKey)) newRelationship.PrimaryTable.AddKey(mainTableKey); if (!newRelationship.ForeignTable.Keys.Contains(relatedTableKey)) newRelationship.ForeignTable.AddKey(relatedTableKey); } //MainTable.Database.AddRelationship(newRelationship); OneToOneEntityProcessor proc = new OneToOneEntityProcessor(MainTable.Database.MappingSet.EntitySet.Entities.Select(e => e.Name)); //List<ITable> tables = new List<ITable>(); //tables.Add((ITable)MainTable); //proc.CreateOneToOneMappingsFor(tables, MainTable.Database.MappingSet); //proc.CreateReference(newRelationship, MainTable.Database.MappingSet.EntitySet); MappingProcessor.ProcessRelationshipInternal(MainTable.Database.MappingSet, newRelationship, proc); if (RelationshipAdded != null) RelationshipAdded(null, null); } this.Visible = false; }
private Model.Table GetNewTable(SQLDMO.Table dmoTable) { Model.Table table = new Model.Table(dmoTable.Name, Script.GetSingluar(dmoTable.Name), false); // Columns int ordinalPosition = 0; List <SQLDMO.Column> dmoColumns = new List <SQLDMO.Column>(); foreach (SQLDMO.Column dmoColumn in dmoTable.Columns) { dmoColumns.Add(dmoColumn); } foreach (SQLDMO.Column dmoColumn in dmoColumns) { if (UnsupportedDataTypes.ToLower().IndexOf("'" + dmoColumn.PhysicalDatatype.ToLower() + "'") >= 0) { continue; } Column column = new Column(dmoColumn.Name, Script.GetSingluar(dmoColumn.Name), false, dmoColumn.Name, table, ordinalPosition, dmoColumn.AllowNulls, dmoColumn.PhysicalDatatype, dmoColumn.Length, dmoColumn.InPrimaryKey, dmoColumn.Identity, dmoColumn.Default, dmoColumn.IsComputed); table.AddColumn(column); ordinalPosition++; } // Index foreach (SQLDMO.Index dmoIndex in dmoTable.Indexes) { string indexType; if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIPrimaryKey) { continue; } if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIUniqueKey) { continue; } else { continue; //throw new Exception("IndexType " + dmoIndex.Type + " Not Defined"); } // Create Alias string indexAlias = indexType + "_"; SQLDMO.SQLObjectList indexes = dmoIndex.ListIndexedColumns(); for (int i = 1; i <= indexes.Count; i++) { SQLDMO._Column dmoColumn = (SQLDMO._Column)indexes.Item(i); indexAlias += dmoColumn.Name; if (i < indexes.Count) { indexAlias += "And"; } } Index index = new Index(dmoIndex.Name, Script.GetSingluar(indexAlias), false, indexType, table); // Fill Columns for (int i = 1; i <= indexes.Count; i++) { SQLDMO._Column dmoColumn = (SQLDMO._Column)indexes.Item(i); Column indexColumn = new Column(dmoColumn.Name, Script.GetSingluar(dmoColumn.Name), false); index.AddColumn(indexColumn); } table.AddIndex(index); } // Indexes -- that should be keys foreach (SQLDMO.Index dmoIndex in dmoTable.Indexes) { string keyType; if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIPrimaryKey) { keyType = DatabaseConstant.KeyType.Primary; } if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIUniqueKey) { keyType = DatabaseConstant.KeyType.Unique; } else { continue; //throw new Exception("KeyType " + dmoIndex.Type + " Not Defined"); } // Create Alias string keyAlias = keyType + "_"; SQLDMO.SQLObjectList indexes = dmoIndex.ListIndexedColumns(); for (int i = 1; i <= indexes.Count; i++) { SQLDMO._Column dmoColumn = dmoTable.Columns.Item(indexes.Item(i)); keyAlias += dmoColumn.Name; if (i < indexes.Count) { keyAlias += "And"; } } Key key = new Key(dmoIndex.Name, Script.GetSingluar(keyAlias), false, keyType, table); // Fill Columns for (int i = 1; i <= indexes.Count; i++) { Column keyColumn = new Column(indexes.Item(i).ToString(), Script.GetSingluar(indexes.Item(i).ToString()), false); key.AddColumn(keyColumn); } table.AddKey(key); } // Keys foreach (SQLDMO.Key dmoKey in dmoTable.Keys) { string keyType; if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary) { keyType = DatabaseConstant.KeyType.Primary; } else if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Foreign) { keyType = DatabaseConstant.KeyType.Foreign; } else if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Unique) { keyType = DatabaseConstant.KeyType.Unique; } else if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Unknown) { continue; } else { throw new Exception("KeyType " + dmoKey.Type.ToString() + " Not Defined"); } // Create Alias string keyAlias = keyType + "_"; for (int i = 1; i <= dmoKey.KeyColumns.Count; i++) { SQLDMO._Column dmoColumn = dmoTable.Columns.Item(dmoKey.KeyColumns.Item(i)); keyAlias += dmoColumn.Name; if (i < dmoKey.KeyColumns.Count) { keyAlias += "And"; } } Key key = new Key(dmoKey.Name, Script.GetSingluar(keyAlias), false, keyType, table); // Fill Columns for (int i = 1; i <= dmoKey.KeyColumns.Count; i++) { Column keyColumn = new Column(dmoKey.KeyColumns.Item(i), Script.GetSingluar(dmoKey.KeyColumns.Item(i)), false); key.AddColumn(keyColumn); } if (keyType == DatabaseConstant.KeyType.Foreign) { // Fill References key.ReferencedTable = new Model.Table(dmoKey.ReferencedTable, Script.GetSingluar(dmoKey.ReferencedTable), false); if (dmoKey.ReferencedKey != null) { key.ReferencedKey = new Key(dmoKey.ReferencedKey, Script.GetSingluar(dmoKey.ReferencedKey), false); } // Fill Referenced Columns for (int i = 1; i <= dmoKey.KeyColumns.Count; i++) { Column referencedKeyColumn = new Column(dmoKey.ReferencedColumns.Item(i), Script.GetSingluar(dmoKey.ReferencedColumns.Item(i)), false); key.AddReferencedColumn(referencedKeyColumn); } } table.AddKey(key); } return(table); }
internal static Database GetDb() { var db = TestDatabaseLoader.TestDatabase(); Table table2 = new Table("Table2"); table2.AddColumn(new Column("Column1")); table2.AddColumn(new Column("Column2")); table2.AddColumn(new Column("Column3")); Key foreignKey = new Key("FK_Table2", DatabaseKeyType.Foreign); foreignKey.Parent = table2; foreignKey.ReferencedKey = db.Tables[0].Keys[0]; foreignKey.AddColumn("Column1"); foreignKey.AddColumn("Column2"); table2.AddKey(foreignKey); db.AddTable(table2); new DatabaseProcessor().CreateRelationships(db); return db; }
private static Key CreateForeignKey(MappingSet set, ITable primaryTable, ITable foreignTable) { var foreignKey = new Key(GetNextKeyName("FK_" + primaryTable.Name + "_" + foreignTable.Name, set), DatabaseKeyType.Foreign); var primaryKey = primaryTable.FirstPrimaryKey; foreignKey.ReferencedKey = primaryKey; foreignTable.AddKey(foreignKey); // Create the columns for the foreign keys foreach (var column in primaryKey.Columns) { var newColumn = CloneColumn(primaryTable, foreignTable, column, "Foreign_"); foreignKey.AddColumn(newColumn.Name); } return foreignKey; }
private void GetForeignKeys(IDatabase db) { DataTable keysTable = connector.GetForeignKeyConstraints(); ITable foreignTable; for (int i = 0; i < keysTable.Rows.Count; i++) { var keyRow = keysTable.Rows[i]; ITable parentTable = db.GetTable((string)keyRow["PrimaryTable"], (string)keyRow["PrimaryTableSchema"]); foreignTable = db.GetTable((string)keyRow["ForeignTable"], (string)keyRow["ForeignTableSchema"]); var currentKeyName = keyRow["ForeignKeyName"].ToString(); // Don't process keys of tables that the user hasn't selected if (parentTable == null || foreignTable == null) continue; Key key = new Key { Name = currentKeyName, IsUserDefined = false, Keytype = DatabaseKeyType.Foreign, Parent = parentTable }; ////////////////// key.IsUnique = connector.GetUniqueIndexes().Select(string.Format("INDEX_NAME = '{0}'", currentKeyName)).Length > 0; ////////////////// foreignTable.AddKey(key); // Get Foreign Key columns DataRow[] foreignKeyColumns = connector.GetForeignKeyColumns().Select(string.Format("CONSTRAINT_NAME = '{0}' AND TABLE_NAME = '{1}'", key.Name.Replace("'", "''"), foreignTable.Name)); foreach (DataRow row in foreignKeyColumns) key.AddColumn((string)row["COLUMN_NAME"]); key.ReferencedKey = parentTable.GetKey((string)keyRow["PrimaryKeyName"]); bool isUnique = false; if (foreignTable.FirstPrimaryKey != null) { isUnique = true; foreach (var col in foreignTable.FirstPrimaryKey.Columns) { if (!key.Columns.Contains(col)) { isUnique = false; break; } } } key.IsUnique = isUnique; } }
private static void CreateUniqueKeyFor(Key key) { IKey uniqueKey = new Key("Unique_" + key.Name, DatabaseKeyType.Unique); key.Parent.AddKey(uniqueKey); foreach (var column in key.Columns) { uniqueKey.AddColumn(column.Name); } }
private Model.Table GetNewTable(string tableName) { ArchAngel.Interfaces.Events.RaiseObjectBeingProcessedEvent(tableName, "Table"); //_columns = null; // Reset the columns //_indexes = null; //_dtReferencedColumns = null; //return new Model.Table(); Model.Table table = new Model.Table(tableName, false); #region Columns DataRow[] columnRows = Columns.Select(string.Format("TABLE_NAME = '{0}'", tableName)); foreach (DataRow row in columnRows) { bool isReadOnly = false; if (!row.IsNull("IsIdentity") && (int)row["IsIdentity"] == 1) { isReadOnly = true; } else if (!row.IsNull("IsComputed") && (int)row["IsComputed"] == 1) { isReadOnly = true; } else if (Slyce.Common.Utility.StringsAreEqual((string)row["DATA_TYPE"], "timestamp", false)) { isReadOnly = true; } // Check whether we have added this column before. Columns are repeated if they are both a PRIMARY_KEY and a FOREIGN_KEY Column column = new Column( (string)row["COLUMN_NAME"], false, table, (int)row["ORDINAL_POSITION"], Slyce.Common.Utility.StringsAreEqual((string)row["IS_NULLABLE"], "YES", false), (string)row["DATA_TYPE"], row.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : Convert.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"]), (int)row["InPrimaryKey"] == 1, row.IsNull("IsIdentity") ? false : Convert.ToInt32(row["IsIdentity"]) == 1, row.IsNull("COLUMN_DEFAULT") ? "" : (string)row["COLUMN_DEFAULT"], isReadOnly, row.IsNull("IsComputed") ? false : Convert.ToInt32(row["IsComputed"]) == 1, row.IsNull("NUMERIC_PRECISION") ? 0 : Convert.ToInt32(row["NUMERIC_PRECISION"]), row.IsNull("NUMERIC_SCALE") ? 0 : Convert.ToInt32(row["NUMERIC_SCALE"])); table.AddColumn(column); //ordinalPosition++; } #endregion #region Indexes DataRow[] indexRows = Indexes.Select(string.Format("TABLE_NAME = '{0}'", tableName)); foreach (DataRow indexRow in indexRows) { string indexType; string indexKeyType = indexRow["CONSTRAINT_TYPE"].ToString(); if (indexKeyType == "PRIMARY KEY") { continue; } else if (indexKeyType == "FOREIGN KEY") { continue; } else if (indexKeyType == "UNIQUE") { continue; //indexType = DatabaseConstant.IndexType.Unique; } else if (indexKeyType == "CHECK") { indexType = DatabaseConstant.IndexType.Check; } else if (indexKeyType == "NONE") //TODO check is NONE { indexType = DatabaseConstant.IndexType.None; } else { //continue; throw new Exception("IndexType " + indexKeyType + " Not Defined"); } DataRow[] indexColumnRows;// = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'", tableName, indexRow["CONSTRAINT_NAME"])); if (indexKeyType == "NONE") { indexColumnRows = Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", tableName, indexRow["ColumnName"])); } else { indexColumnRows = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'", tableName, indexRow["CONSTRAINT_NAME"])); } Index index = new Index(indexRow["CONSTRAINT_NAME"].ToString(), false, indexType, table, (bool)indexRow["IS_UNIQUE"], (bool)indexRow["IS_CLUSTERED"]); // Fill Columns foreach (DataRow indexColumnRow in indexColumnRows) { Column indexColumn = new Column(indexColumnRow["COLUMN_NAME"].ToString(), false); index.AddColumn(indexColumn); } index.ResetDefaults(); table.AddIndex(index); } // Indexes -- that should be keys foreach (DataRow keyRow in indexRows) { string keyType; string indexKeyType = keyRow["CONSTRAINT_TYPE"].ToString(); if (indexKeyType == "PRIMARY KEY") { keyType = DatabaseConstant.KeyType.Primary; } else if (indexKeyType == "FOREIGN KEY") { keyType = DatabaseConstant.KeyType.Foreign; } else if (indexKeyType == "UNIQUE") { keyType = DatabaseConstant.KeyType.Unique; } else if (indexKeyType == "CHECK") { continue; } else if (indexKeyType == "NONE") { continue; //keyType = DatabaseConstant.KeyType.None; } else { //continue; throw new Exception("KeyType " + indexKeyType + " Not Defined"); } Key key = new Key(keyRow["CONSTRAINT_NAME"].ToString(), false, keyType, table, false); DataRow[] keyColumnRows = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'", tableName, keyRow["CONSTRAINT_NAME"])); // Fill Columns foreach (DataRow keyColumnRow in keyColumnRows) { Column keyColumn = new Column(keyColumnRow["COLUMN_NAME"].ToString(), false); keyColumn.DataType = (string)keyColumnRow["DATA_TYPE"]; key.AddColumn(keyColumn); } if (keyType == DatabaseConstant.KeyType.Foreign) { DataRow[] keyReferencedColumnRows = IndexReferencedColumns.Select(string.Format("ForeignKey = '{0}'", keyRow["CONSTRAINT_NAME"])); DataRow firstKeyReferencedColumnRow = keyReferencedColumnRows[0]; // Fill References key.ReferencedTable = new Model.Table(firstKeyReferencedColumnRow["ReferencedTable"].ToString(), false); //if (dmoKey.ReferencedKey != null) //{ key.ReferencedKey = new Key(firstKeyReferencedColumnRow["ReferencedKey"].ToString(), false, true); //} // Fill Referenced Columns foreach (DataRow keyReferencedColumnRow in keyReferencedColumnRows) { Column keyReferencedColumn = new Column(keyReferencedColumnRow["ReferencedColumn"].ToString(), false); key.AddReferencedColumn(keyReferencedColumn); } } key.ResetDefaults(); table.AddKey(key); } #endregion //#region Indexes -- that should be keys //string prevConstraintName = ""; //Key key = null; //DataRow[] indexRows = DtIndexes.Select(string.Format("TABLE_NAME = '{0}'", tableName)); //for (int rowCounter = 0; rowCounter < indexRows.Length; rowCounter++) //{ // DataRow row = indexRows[rowCounter]; // for (int colCounter = 0; colCounter < DtIndexes.Columns.Count; colCounter++) // { // string colName = DtIndexes.Columns[colCounter].ColumnName; // } // string keyType; // string indexKeyType = row.IsNull("Constraint_Type") ? "NONE" : (string)row["Constraint_Type"]; // string constraintName = row.IsNull("Constraint_Name") ? "" : (string)row["Constraint_Name"]; // string columnName = (string)row["COLUMN_NAME"]; // if (indexKeyType == "NONE") // { // keyType = DatabaseConstant.KeyType.None; // } // else if (indexKeyType == "PRIMARY KEY") // { // keyType = DatabaseConstant.KeyType.Primary; // } // else if (indexKeyType == "UNIQUE") // { // keyType = DatabaseConstant.KeyType.Unique; // } // else if (indexKeyType == "CHECK") // TODO: was 'None' for SMO // { // keyType = DatabaseConstant.KeyType.None; // } // else if (indexKeyType == "FOREIGN KEY") // TODO: was 'None' for SMO // { // keyType = DatabaseConstant.KeyType.Foreign; // } // else // { // throw new Exception("KeyType " + indexKeyType + " Not Defined"); // } // // Create Alias // if (string.Format("{0}{1}", constraintName, keyType) != prevConstraintName) // { // if (key != null) // { // // Reset the alias, because it is based on the Columns collection which has just finished being modified. // key.ResetDefaults(); // } // // Create a new Key // key = new Key(constraintName, false, keyType, table, false); // table.AddKey(key); // prevConstraintName = string.Format("{0}{1}", constraintName, keyType); // Column keyColumn = new Column(columnName, false); // key.AddColumn(keyColumn); // } // else // { // // We are processing another column of the same Index as the previous index // Column keyColumn = new Column(columnName, false); // key.AddColumn(keyColumn); // } // if (keyType == DatabaseConstant.KeyType.Foreign) // { // DataRow[] referencedColumnRows = DtReferencedColumns.Select(string.Format("FOREIGN_KEY = '{0}'", key.Name)); // foreach (DataRow refColRow in referencedColumnRows) // { // // Fill References // if (key.ReferencedTable == null) // { // string referencedTableName = (string)refColRow["Referenced_Table_Name"]; // string referencedKeyName = (string)refColRow["Referenced_Key"]; // key.ReferencedTable = new Model.Table(referencedTableName, false); // key.ReferencedKey = new Key(referencedKeyName, false, true); // } // string referencedColumnName = (string)refColRow["Referenced_Column_Name"]; // // Fill Referenced Columns // Column referencedKeyColumn = new Column(referencedColumnName, false); // key.AddReferencedColumn(referencedKeyColumn); // } // } // key.ResetDefaults(); //} //#endregion return(table); }
private void Save() { if (RelatedTable == null) { MessageBox.Show(this, "You need to select a foreign table.", "Foreign table missing", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } if (RelatedColumnCombos.Count == 0) { MessageBox.Show(this, "No columns selected.", "Columns missing", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } bool mainColsAreUnique; // = checkBoxPrimaryUnique.Checked ? true : ColumnsAreUnique(GetMainColumns()); bool relatedColsAreUnique; // = checkBoxForeignUnique.Checked ? true : ColumnsAreUnique(GetRelatedColumns()); // Check if the Main columns are unique if (checkBoxPrimaryUnique.Checked) { mainColsAreUnique = true; } else { List <Column> mainColumns = GetMainColumns(); bool selectedColsContainAllPrimaryCols = MainTable.ColumnsInPrimaryKey.Count() > 0; foreach (var col in MainTable.ColumnsInPrimaryKey) { if (!mainColumns.Contains(col)) { selectedColsContainAllPrimaryCols = false; break; } } if (selectedColsContainAllPrimaryCols) { mainColsAreUnique = true; } else { mainColsAreUnique = ColumnsAreUnique(mainColumns); } } // Check if the Realted columns are unique if (checkBoxForeignUnique.Checked) { relatedColsAreUnique = true; } else { List <Column> relatedColumns = GetRelatedColumns(); bool selectedColsContainAllPrimaryCols = RelatedTable.ColumnsInPrimaryKey.Count() > 0; foreach (var col in RelatedTable.ColumnsInPrimaryKey) { if (!relatedColumns.Contains(col)) { selectedColsContainAllPrimaryCols = false; break; } } if (selectedColsContainAllPrimaryCols) { relatedColsAreUnique = true; } else { relatedColsAreUnique = ColumnsAreUnique(relatedColumns); } } IKey mainTableKey = null; foreach (var key in MainTable.Keys) { if (MainColumnCombos.Count == key.Columns.Count) { bool keyMatches = true; foreach (var combo in MainColumnCombos) { if (!key.Columns.Contains((Column)((DisplayItem)combo.SelectedItem).Value)) { keyMatches = false; break; } } if (keyMatches) { // Key is found mainTableKey = key; break; } } } IKey relatedTableKey = null; foreach (var key in RelatedTable.Keys) { if (RelatedColumnCombos.Count == key.Columns.Count) { bool keyMatches = true; foreach (var combo in RelatedColumnCombos) { // Can't have two primary keys, but there might be a foreign key on the same columns, so skip for now. Otherwise we'll need to create a new foreign key anyway. if (!key.Columns.Contains((Column)((DisplayItem)combo.SelectedItem).Value) && !((mainTableKey != null && mainTableKey.Keytype == DatabaseKeyType.Primary) && key.Keytype == DatabaseKeyType.Primary)) { keyMatches = false; break; } } if (keyMatches) { // Key is found relatedTableKey = key; break; } } } if (mainTableKey != null && relatedTableKey != null) { // Both keys exist, so let's check whether this relationship already exists foreach (var relationship in MainTable.Relationships) { if ((relationship.PrimaryKey == mainTableKey && relationship.ForeignKey == relatedTableKey) || (relationship.PrimaryKey == relatedTableKey && relationship.ForeignKey == mainTableKey)) { MessageBox.Show(this, "This relationship already exists.", "Relationship exists", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } } } if (mainTableKey != null && relatedTableKey != null && (mainTableKey.Keytype == DatabaseKeyType.Primary && relatedTableKey.Keytype == DatabaseKeyType.Primary)) { // A relationship can't have two primary keys, so we'll need to create a new 'foreign-key' relatedTableKey = null; } RelatedTable = (Table)comboBoxForeignTable.SelectedItem; if (mainTableKey == null) { mainTableKey = new Key { Name = string.Format("FK_{0}_{1}", MainTable.Name, RelatedTable.Name).GetNextName(MainTable.Relationships.Select(r => r.Name).ToList()), IsUserDefined = true, Keytype = DatabaseKeyType.Foreign, Parent = MainTable, IsUnique = mainColsAreUnique }; foreach (Column column in GetMainColumns()) { mainTableKey.AddColumn(column.Name); } } if (relatedTableKey == null) { relatedTableKey = new Key { Name = string.Format("FK_{0}_{1}", RelatedTable.Name, MainTable.Name).GetNextName(RelatedTable.Relationships.Select(r => r.Name).ToList()), IsUserDefined = true, Keytype = DatabaseKeyType.Foreign, Parent = RelatedTable, IsUnique = relatedColsAreUnique }; foreach (Column column in GetRelatedColumns()) { relatedTableKey.AddColumn(column.Name); } } mainTableKey.ReferencedKey = relatedTableKey; relatedTableKey.ReferencedKey = mainTableKey; if (Relationship == null) { Relationship newRelationship = new RelationshipImpl(); newRelationship.Database = MainTable.Database; newRelationship.IsUserDefined = true; newRelationship.Schema = MainTable.Schema; if (relatedTableKey.Keytype == DatabaseKeyType.Primary) { newRelationship.AddThisTo(RelatedTable, MainTable); newRelationship.Name = string.Format("FK_NH_{0}_{1}", RelatedTable, MainTable).GetNextName(RelatedTable.Relationships.Select(r => r.Name).ToList()); //newRelationship.PrimaryCardinality = mainColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; //newRelationship.ForeignCardinality = relatedColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; newRelationship.ForeignCardinality = mainColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; newRelationship.PrimaryCardinality = relatedColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; newRelationship.PrimaryKey = relatedTableKey; newRelationship.ForeignKey = mainTableKey; if (!newRelationship.PrimaryTable.Keys.Contains(relatedTableKey)) { newRelationship.PrimaryTable.AddKey(relatedTableKey); } if (!newRelationship.ForeignTable.Keys.Contains(mainTableKey)) { newRelationship.ForeignTable.AddKey(mainTableKey); } } else { newRelationship.AddThisTo(MainTable, RelatedTable); newRelationship.Name = string.Format("FK_NH_{0}_{1}", MainTable, RelatedTable); //newRelationship.PrimaryCardinality = mainColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; //newRelationship.ForeignCardinality = relatedColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; newRelationship.ForeignCardinality = mainColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; newRelationship.PrimaryCardinality = relatedColsAreUnique ? ArchAngel.Interfaces.Cardinality.One : ArchAngel.Interfaces.Cardinality.Many; newRelationship.PrimaryKey = mainTableKey; newRelationship.ForeignKey = relatedTableKey; if (!newRelationship.PrimaryTable.Keys.Contains(mainTableKey)) { newRelationship.PrimaryTable.AddKey(mainTableKey); } if (!newRelationship.ForeignTable.Keys.Contains(relatedTableKey)) { newRelationship.ForeignTable.AddKey(relatedTableKey); } } //MainTable.Database.AddRelationship(newRelationship); OneToOneEntityProcessor proc = new OneToOneEntityProcessor(MainTable.Database.MappingSet.EntitySet.Entities.Select(e => e.Name)); //List<ITable> tables = new List<ITable>(); //tables.Add((ITable)MainTable); //proc.CreateOneToOneMappingsFor(tables, MainTable.Database.MappingSet); //proc.CreateReference(newRelationship, MainTable.Database.MappingSet.EntitySet); MappingProcessor.ProcessRelationshipInternal(MainTable.Database.MappingSet, newRelationship, proc); if (RelationshipAdded != null) { RelationshipAdded(null, null); } } this.Visible = false; }