public void AddColumnDublicateTest() { var idx = new Index("IDX_TEST", "testTable"); idx.AddColumn(new IndexColumn("Test")); Assert.Throws <DublicateColumnException>(() => idx.AddColumn(new IndexColumn("Test"))); }
public void CreateIndexWithSameColumnTwoTimes_GetCorrectException() { // Arrange var index = new Index("MyIndexName", "MyTable"); // Act index.AddColumn("MyColumn"); // Assert var ex = Assert.Throws<ArgumentException>(() => index.AddColumn("MyColumn")); Assert.Equal("The column MyColumn is already added to index MyIndexName on table MyTable", ex.Message); }
public void CreateIndexWithTwoColumns_GetValidCreateScript() { // Arrange var index = new Index("MyIndexName", "MyTable"); // Act index.AddColumn("MyColumn"); index.AddColumn("MyOtherColumn"); // Assert Assert.Equal(1, index.GetUpScript().Count); Assert.Equal(2, index.Columns.Count); Assert.Equal("CREATE INDEX MyIndexName \r\nON MyTable(MyColumn, MyOtherColumn)\r\n", index.GetUpScript().First()); }
public void AddColumnSameObjectTest() { var idx = new Index("IDX_TEST", "testTable"); var idx2 = idx.AddColumn(new IndexColumn("testcol")); Assert.AreSame(idx, idx2); }
public void AddColumnTest() { var idx = new Index("IDX_TEST", "testTable"); idx.AddColumn(new IndexColumn("Test")); Assert.AreEqual(1, idx.Columns.Count); }
public void The_Original_Index_Should_Be_Modified() { var db1 = TestDatabaseLoader.TestDatabase(); IIndex originalIndex = db1.Tables[0].Indexes[0]; originalIndex.Description = "old description"; IIndex newIndex = new Index(originalIndex.Name); newIndex.Parent = new Table("Table1"); newIndex.Parent.AddColumn(new Column("Column2")); newIndex.Description = "new description"; newIndex.AddColumn("Column2"); IndexChangeOperation op = new IndexChangeOperation(db1.Tables[0].Indexes[0], newIndex); op.RunOperation(); op.RunSecondStep(); IIndex index = db1.Tables[0].Indexes[0]; Assert.That(db1.Tables[0].Indexes, Has.Count(1), "No new indexes should have been added"); Assert.That(index, Is.SameAs(originalIndex), "Index object should still be the same"); Assert.That(index.Description, Is.EqualTo("old description")); Assert.That(index.Columns, Has.Count(1)); Assert.That(index.Columns[0].Name, Is.EqualTo("Column2")); Assert.That(index.Columns[0], Is.Not.SameAs(newIndex.Columns[0])); Assert.That(index.Columns[0], Is.SameAs(db1.Tables[0].Columns[1]), "The new Index should reference existing columns"); Assert.That(index.Parent, Is.SameAs(db1.Tables[0]), "The new Index's parent should be Table1 in the existing database"); }
public override List <Index> GetIndices(string schemaName) { var stmt = String.Format( @"select sta.table_schema as schema_name, sta.table_name, sta.index_name, sta.column_name, sta.seq_in_index as column_index, sta.non_unique from information_schema.statistics sta where sta.table_schema = '{0}'", schemaName); var ds = this.ExecuteSelect(stmt); var cList = new Utils.ChainedList <Index>(); foreach (DataRow dr in ds.Tables[0].Rows) { var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString()); index.IsUnique = !Convert.ToBoolean(dr["non_unique"]); index.AddColumn(Convert.ToInt32(dr["column_index"]), dr["column_name"].ToString()); Index tmp; if (!cList.Add(index, out tmp)) { tmp.AddCoumns(index.IndexOrderdColumnNames); } } return(cList.ToList()); }
public override List <Index> GetIndices(string schemaName) { var stmt = String.Format( @"select owner, index_name, ind.table_name, ind.uniqueness, col.column_name, col.column_position from all_indexes ind inner join (select * from all_ind_columns where index_owner = '{0}') col using (index_name) where ind.owner = '{0}' and ind.table_type = 'TABLE' order by index_name, column_position", schemaName); using (var ds = this.ExecuteSelect(stmt)) { var cList = new Utils.ChainedList <Index>(); foreach (DataRow dr in ds.Tables[0].Rows) { var index = new Index(dr["owner"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString()); index.IsUnique = (dr["uniqueness"].ToString() == "UNIQUE"); index.AddColumn(Convert.ToInt32(dr["column_position"]), dr["column_name"].ToString()); Index tmp; if (!cList.Add(index, out tmp)) { tmp.AddCoumns(index.IndexOrderdColumnNames); } } return(cList.ToList()); } }
public void CreateIndexWithOneColumn_GetValidCreateScript() { // Arrange var index = new Index<MyTable>("MyIndexName", "MyTable"); // Act index.AddColumn(m => m.MyColumn); // Assert Assert.Equal(1, index.GetUpScript().Count); Assert.Equal("CREATE INDEX MyIndexName \r\nON MyTable(MyColumn)\r\n", index.GetUpScript().First()); }
override internal void LoadAll() { try { string select = @"SELECT current_database() as table_catalog, tab.relname AS table_name, " + "n.nspname as TABLE_NAMESPACE, cls.relname as INDEX_NAME, idx.indisunique as UNIQUE, " + "idx.indisclustered as CLUSTERED, a.amname as TYPE, indkey AS columns FROM pg_index idx " + "JOIN pg_class cls ON cls.oid=indexrelid " + "JOIN pg_class tab ON tab.oid=indrelid AND tab.relname = '" + this.Table.Name + "' " + "JOIN pg_namespace n ON n.oid=tab.relnamespace AND n.nspname = '" + this.Table.Schema + "' " + "JOIN pg_am a ON a.oid = cls.relam " + "LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0') " + "LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid) " + "WHERE con.conname IS NULL ORDER BY cls.relname;"; NpgsqlConnection cn = new Npgsql.NpgsqlConnection(this.dbRoot.ConnectionString); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(select, cn); cn.Open(); cn.ChangeDatabase(this.Table.Tables.Database.Name); DataTable metaData = new DataTable(); adapter.Fill(metaData); cn.Close(); PopulateArrayNoHookup(metaData); for (int i = 0; i < this.Count; i++) { Index index = this[i] as Index; if (null != index) { string s = index._row["columns"] as string; string[] colIndexes = s.Split(' '); foreach (string colIndex in colIndexes) { if (colIndex != "0") { int id = Convert.ToInt32(colIndex); Column column = this.Table.Columns[id - 1] as Column; index.AddColumn(column.Name); } } } } } catch {} }
public override List <Index> GetIndices(string schemaName) { var stmt = String.Format( @"select ixs.schemaname as schema_name, ixs.tablename as table_name, ixs.indexname as index_name, jo.attname as column_name, jo.attnum as column_index, jo.indkey as index_order, jo.indisunique as is_unique from pg_catalog.pg_indexes as ixs inner join ( select a.attname, a.attnum, ix.indkey, ix.indisunique, i.relname from pg_catalog.pg_class as t, pg_catalog.pg_class as i, pg_catalog.pg_index as ix, pg_catalog.pg_attribute as a where a.attnum > 0 and t.oid = ix.indrelid and i.oid = ix.indexrelid and a.attrelid = t.oid and a.attnum = ANY(ix.indkey)) as jo on ixs.indexname = jo.relname where ixs.schemaname = '{0}'", schemaName); var ds = this.ExecuteSelect(stmt); var cList = new Utils.ChainedList <Index>(); foreach (DataRow dr in ds.Tables[0].Rows) { var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString()); index.IsUnique = Convert.ToBoolean(dr["is_unique"]); var indexPos = dr["index_order"].ToString().Split(' ').ToArray(); for (int i = 0; i < indexPos.Length; i++) { if (Convert.ToInt32(dr["column_index"].ToString()) == Convert.ToInt32(indexPos[i])) { index.AddColumn(i + 1, dr["column_name"].ToString()); break; } } Index tmp; if (!cList.Add(index, out tmp)) { tmp.AddCoumns(index.IndexOrderdColumnNames); } } return(cList.ToList()); }
public void CreateIndexWithOneColumn_GetOneColumnAdded() { // Arrange var index = new Index<MyTable>("MyIndexName", "MyTable"); // Act index.AddColumn(m => m.MyColumn); // Assert Assert.Equal("MyIndexName", index.Name); Assert.Equal("MyTable", index.TableName); Assert.Equal(1, index.Columns.Count); Assert.Equal("MyColumn", index.Columns.First()); }
internal void GetTableConstraintIndexes(Table table) { DataRow[] indexRows = connector.Indexes.Select(string.Format("TABLE_NAME = '{0}'", table.Name.Replace("'", "''"))); //foreach (DataRow indexRow in indexRows) for (int i = 0; i < indexRows.Length; i++) { DataRow indexRow = indexRows[i]; DatabaseIndexType indexType = GetIndexType(indexRow["CONSTRAINT_TYPE"].ToString()); var indexColumnRows = new List <DataRow>(); indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", table.Name.Replace("'", "''"), indexRow["COLUMN_NAME"]))); while ((i < indexRows.Length - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == table.Name && (string)indexRows[i + 1]["INDEX_NAME"] == (string)indexRow["INDEX_NAME"]) { i++; indexRow = indexRows[i]; indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", table.Name.Replace("'", "''"), indexRow["COLUMN_NAME"]))); } bool isUnique = (bool)indexRow["ISUNIQUE"]; bool isClustered = (bool)indexRow["ISCLUSTERED"]; Index index = new Index { Name = indexRow["INDEX_NAME"].ToString(), IsUserDefined = false, IndexType = indexType, Parent = table, IsUnique = isUnique, IsClustered = isClustered }; // Fill Columns foreach (DataRow indexColumnRow in indexColumnRows) { var column = index.AddColumn((string)indexColumnRow["COLUMN_NAME"]); if (index.IsUnique && indexColumnRows.Count == 1) { // Columns are only unique if the index has one column column.IsUnique = true; } } index.ResetDefaults(); table.AddIndex(index); } }
public void Should_Return_This() { const string expectedXML = IndexWithColumnsXml; Table table1 = new Table("Table1"); table1.AddColumn(new Column("Column1")); Index index = new Index("Index1"); index.Parent = table1; index.AddColumn("Column1"); string outputXML = index.Serialise(new DatabaseSerialisationScheme()); outputXML = XmlSqueezer.RemoveWhitespaceBetweenElements(outputXML); Assert.That(outputXML, Is.EqualTo(expectedXML)); }
public override List <DBObjects.Index> GetIndices(string schemaName) { var stmt = String.Format( @"SELECT i.rdb$relation_name AS TableName, s.rdb$field_name AS FieldName, i.rdb$index_name AS IndexName, rc.rdb$constraint_name AS AltIndexName, i.rdb$foreign_key AS IsForeignKey, i.rdb$unique_flag AS IsUnique, s.rdb$field_position+1 AS FieldPosition, rc.rdb$deferrable AS Deferrable, rc.rdb$initially_deferred AS InitiallyDeferred FROM rdb$indices i LEFT JOIN rdb$index_segments s ON (i.rdb$index_name = s.rdb$index_name) LEFT JOIN rdb$relation_constraints rc ON (i.rdb$index_name = rc.rdb$index_name) WHERE i.rdb$system_flag = 0 AND s.rdb$field_name IS NOT NULL"); using (var ds = this.ExecuteSelect(stmt)) { var cList = new Utils.ChainedList <Index>(); foreach (DataRow dr in ds.Tables[0].Rows) { string index_name; if (dr["AltIndexName"] == DBNull.Value) { index_name = dr["IndexName"].ToString().Trim(); } else { index_name = dr["AltIndexName"].ToString().Trim(); } var ix = new Index(this._schemaName, dr["TableName"].ToString().Trim(), index_name); ix.IsUnique = (dr["IsUnique"] != DBNull.Value) ? true : false; ix.AddColumn(Convert.ToInt32(dr["FieldPosition"]), dr["FieldName"].ToString().Trim()); Index tmp; if (!cList.Add(ix, out tmp)) { tmp.AddCoumns(ix.IndexOrderdColumnNames); } } return(cList.ToList()); } }
internal void GetTableConstraintIndexes(Table table) { List <DataRow> indexRows = connector.GetIndexRows(table.Schema, table.Name.Replace("'", "''")); for (int i = 0; i < indexRows.Count; i++) { DataRow indexRow = indexRows[i]; DatabaseIndexType indexType = GetIndexType(indexRow["CONSTRAINT_TYPE"].ToString()); var indexColumnRows = new List <DataRow>(); indexColumnRows.AddRange(connector.ColumnsBySchema[table.Schema][table.Name.Replace("'", "''")].Where(c => c[OrdColumn].ToString() == indexRow["COLUMN_NAME"].ToString())); while ((i < indexRows.Count - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == table.Name && (string)indexRows[i + 1]["INDEX_NAME"] == (string)indexRow["INDEX_NAME"]) { i++; indexRow = indexRows[i]; indexColumnRows.AddRange(connector.ColumnsBySchema[table.Schema][table.Name.Replace("'", "''")].Where(c => c[OrdColumn].ToString() == indexRow["COLUMN_NAME"].ToString())); } bool isUnique = Convert.ToInt32(indexRow["IS_UNIQUE"]) == 1; bool isClustered = Convert.ToInt32(indexRow["IS_CLUSTERED"]) == 1; Index index = new Index { Name = indexRow["INDEX_NAME"].ToString(), IsUserDefined = false, IndexType = indexType, Parent = table, IsUnique = isUnique, IsClustered = isClustered }; // Fill Columns foreach (DataRow indexColumnRow in indexColumnRows) { var column = index.AddColumn((string)indexColumnRow["COLUMN_NAME"]); if (index.IsUnique && indexColumnRows.Count == 1) { // Columns are only unique if the index has one column column.IsUnique = true; } } index.ResetDefaults(); table.AddIndex(index); } }
private void GetUniqueIndexes(Table table) { DataRow[] indexRows = connector.GetUniqueIndexes().Select(string.Format("TABLE_NAME = '{0}'", table.Name.Replace("'", "''"))); for (int i = 0; i < indexRows.Length; i++) { DataRow indexRow = indexRows[i]; Index index = new Index { Name = (string)indexRow["INDEX_NAME"], IsUserDefined = false, IsUnique = true, IsClustered = (bool)indexRow["ISCLUSTERED"], IndexType = DatabaseIndexType.Unique, Parent = table }; List <DataRow> indexColumnRows = new List <DataRow>(); indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", table.Name.Replace("'", "''"), indexRow["COLUMN_NAME"]))); while ((i < indexRows.Length - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == table.Name && (string)indexRows[i + 1]["INDEX_NAME"] == (string)indexRow["INDEX_NAME"]) { i++; indexRow = indexRows[i]; indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", table.Name.Replace("'", "''"), indexRow["COLUMN_NAME"]))); } // Fill Columns foreach (DataRow indexColumnRow in indexColumnRows) { var column = index.AddColumn((string)indexColumnRow["COLUMN_NAME"]); if (index.IsUnique && indexColumnRows.Count == 1) { // Columns are only unique if the index has one column column.IsUnique = true; } } index.ResetDefaults(); table.AddIndex(index); } }
public override List <Index> GetIndices(string schemaName) { var stmt = String.Format( @"select '{0}' as schema_name, tab.name as table_name, inx.name as index_name, col.name as column_name, ixcol.key_ordinal as column_index, inx.is_unique from sys.objects as obj inner join sys.indexes as inx on obj.object_id = inx.object_id inner join sys.index_columns as ixcol on inx.object_id = ixcol.object_id and inx.index_id = ixcol.index_id inner join sys.columns as col on obj.object_id = col.object_id and ixcol.column_id = col.column_id inner join sys.tables as tab on obj.object_id = tab.object_id where obj.schema_id = (select schema_id from sys.schemas where name = '{0}') and inx.is_disabled = 0 and inx.name is not Null and ixcol.is_included_column = 0", schemaName); var ds = this.ExecuteSelect(stmt); var cList = new Utils.ChainedList <Index>(); foreach (DataRow dr in ds.Tables[0].Rows) { var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString()); index.IsUnique = Convert.ToBoolean(dr["is_unique"]); index.AddColumn(Convert.ToInt32(dr["column_index"]), dr["column_name"].ToString()); Index tmp; if (!cList.Add(index, out tmp)) { tmp.AddCoumns(index.IndexOrderdColumnNames); } } return(cList.ToList()); }
public override List <DBObjects.Index> GetIndices(string schemaName) { var stmt = String.Format( @"select inx.tabschema as schema_name, inx.tabname as table_name, inx.indname as index_name, inxcol.colname as column_name, inxcol.colseq as column_position, case (inx.uniquerule) when 'P' then 'Y' when 'U' then 'Y' when 'D' then 'N' end as is_unique from syscat.indexes as inx, syscat.indexcoluse inxcol where inxcol.indschema = inx.tabschema and inxcol.indname = inx.indname and inx.tabschema = '{0}' and inx.indextype in ('BLOK', 'CLUS', 'DIM', 'REG') order by inxcol.indname, inxcol.colseq" , schemaName); var ds = this.ExecuteSelect(stmt); var cList = new Utils.ChainedList <Index>(); foreach (DataRow dr in ds.Tables[0].Rows) { var index = new Index(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["index_name"].ToString()); index.IsUnique = dr["is_unique"].ToString().ToLower() == "y"; index.AddColumn(Convert.ToInt32(dr["column_position"]), dr["column_name"].ToString()); Index tmp; if (!cList.Add(index, out tmp)) { tmp.AddCoumns(index.IndexOrderdColumnNames); } } return(cList.ToList()); }
public void The_Original_Database_Should_Contain_A_Copy_Of_The_New_Index() { var db1 = TestDatabaseLoader.TestDatabase(); // Setup new key information Table parentTable = new Table("Table1"); parentTable.AddColumn(new Column("Column3")); Index newIndex = new Index("UQ_Column3"); newIndex.Parent = parentTable; newIndex.AddColumn("Column3"); IndexAdditionOperation op = new IndexAdditionOperation(db1.Tables[0], newIndex); op.RunOperation(); op.RunSecondStep(); Assert.That(db1.Tables[0].Indexes, Has.Count(2)); Assert.That(db1.Tables[0].Indexes[1].Name, Is.EqualTo(newIndex.Name)); Assert.That(db1.Tables[0].Indexes[1], Is.Not.SameAs(newIndex), "The added Index should be a copy, not the original object."); Assert.That(db1.Tables[0].Indexes[1].Columns[0], Is.SameAs(db1.Tables[0].Columns[2]), "The new Index should reference existing columns, not the new ones"); Assert.That(db1.Tables[0].Indexes[1].Parent, Is.SameAs(db1.Tables[0]), "The new Index's parent should be Table1 in the existing database"); }
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); }
public void The_Original_Database_Should_Contain_A_Copy_Of_The_New_Index() { var db1 = TestDatabaseLoader.TestDatabase(); // Setup new key information Table parentTable = new Table("Table1"); parentTable.AddColumn(new Column("Column3")); Index newIndex = new Index("UQ_Column3"); newIndex.Parent = parentTable; newIndex.AddColumn("Column3"); IndexAdditionOperation op = new IndexAdditionOperation(db1.Tables[0], newIndex); op.RunOperation(); op.RunSecondStep(); Assert.That(db1.Tables[0].Indexes, Has.Count(2)); Assert.That(db1.Tables[0].Indexes[1].Name, Is.EqualTo(newIndex.Name)); Assert.That(db1.Tables[0].Indexes[1], Is.Not.SameAs(newIndex), "The added Index should be a copy, not the original object."); Assert.That(db1.Tables[0].Indexes[1].Columns[0], Is.SameAs(db1.Tables[0].Columns[2]), "The new Index should reference existing columns, not the new ones"); Assert.That(db1.Tables[0].Indexes[1].Parent, Is.SameAs(db1.Tables[0]), "The new Index's parent should be Table1 in the existing database"); }
public void The_Original_Index_Should_Be_Modified() { var db1 = TestDatabaseLoader.TestDatabase(); IIndex originalIndex = db1.Tables[0].Indexes[0]; originalIndex.Description = "old description"; IIndex newIndex = new Index(originalIndex.Name); newIndex.Parent = new Table("Table1"); newIndex.Parent.AddColumn(new Column("Column2")); newIndex.Description = "new description"; newIndex.AddColumn("Column2"); IndexChangeOperation op = new IndexChangeOperation(db1.Tables[0].Indexes[0], newIndex); op.RunOperation(); op.RunSecondStep(); IIndex index = db1.Tables[0].Indexes[0]; Assert.That(db1.Tables[0].Indexes, Has.Count(1), "No new indexes should have been added"); Assert.That(index, Is.SameAs(originalIndex), "Index object should still be the same"); Assert.That(index.Description, Is.EqualTo("old description")); Assert.That(index.Columns, Has.Count(1)); Assert.That(index.Columns[0].Name, Is.EqualTo("Column2")); Assert.That(index.Columns[0], Is.Not.SameAs(newIndex.Columns[0])); Assert.That(index.Columns[0], Is.SameAs(db1.Tables[0].Columns[1]), "The new Index should reference existing columns"); Assert.That(index.Parent, Is.SameAs(db1.Tables[0]), "The new Index's parent should be Table1 in the existing database"); }
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 GetUniqueIndexes(Table table) { DataRow[] indexRows = connector.GetUniqueIndexes().Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}'", table.Name.Replace("'", "''"), table.Schema)); for (int i = 0; i < indexRows.Length; i++) { DataRow indexRow = indexRows[i]; Index index = new Index { Name = (string)indexRow["INDEX_NAME"], IsUserDefined = false, IsUnique = true, IsClustered = (long)indexRow["IS_CLUSTERED"] == 1, IndexType = DatabaseIndexType.Unique, Parent = table }; List<DataRow> indexColumnRows = new List<DataRow>(); indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}' AND COLUMN_NAME = '{2}'", table.Name.Replace("'", "''"), table.Schema, indexRow["COLUMN_NAME"]))); while ((i < indexRows.Length - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == table.Name && (string)indexRows[i + 1]["INDEX_NAME"] == (string)indexRow["INDEX_NAME"]) { i++; indexRow = indexRows[i]; indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}' AND COLUMN_NAME = '{2}'", table.Name.Replace("'", "''"), table.Schema, indexRow["COLUMN_NAME"]))); } // Fill Columns foreach (DataRow indexColumnRow in indexColumnRows) { var column = index.AddColumn((string)indexColumnRow["COLUMN_NAME"]); if (index.IsUnique && indexColumnRows.Count == 1) { // Columns are only unique if the index has one column column.IsUnique = true; } } index.ResetDefaults(); table.AddIndex(index); } }
internal void GetTableConstraintIndexes(Table table) { DataRow[] indexRows = connector.Indexes.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}'", table.Name.Replace("'", "''"), table.Schema)); for (int i = 0; i < indexRows.Length; i++) { DataRow indexRow = indexRows[i]; DatabaseIndexType indexType = GetIndexType(indexRow["CONSTRAINT_TYPE"].ToString()); var indexColumnRows = new List<DataRow>(); indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}' AND COLUMN_NAME = '{2}'", table.Name.Replace("'", "''"), table.Schema, indexRow["COLUMN_NAME"]))); while ((i < indexRows.Length - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == table.Name && (string)indexRows[i + 1]["INDEX_NAME"] == (string)indexRow["INDEX_NAME"]) { i++; indexRow = indexRows[i]; indexColumnRows.AddRange(connector.Columns.Select(string.Format("TABLE_NAME = '{0}' AND TABLE_SCHEMA = '{1}' AND COLUMN_NAME = '{2}'", table.Name.Replace("'", "''"), table.Schema, indexRow["COLUMN_NAME"]))); } bool isUnique = (int)indexRow["IS_UNIQUE"] == 1; bool isClustered = Convert.ToInt32(indexRow["IS_CLUSTERED"]) == 1; Index index = new Index { Name = indexRow["INDEX_NAME"].ToString(), IsUserDefined = false, IndexType = indexType, Parent = table, IsUnique = isUnique, IsClustered = isClustered }; // Fill Columns foreach (DataRow indexColumnRow in indexColumnRows) { var column = index.AddColumn((string)indexColumnRow["COLUMN_NAME"]); if (index.IsUnique && indexColumnRows.Count == 1) { // Columns are only unique if the index has one column column.IsUnique = true; } } index.ResetDefaults(); table.AddIndex(index); } }
private void GetUniqueIndexes(Table table) { List<DataRow> indexRows = connector.GetIndexRows(table.Schema, table.Name.Replace("'", "''")); for (int i = 0; i < indexRows.Count; i++) { DataRow indexRow = indexRows[i]; Index index = new Index { Name = (string)indexRow["INDEX_NAME"], IsUserDefined = false, IsUnique = true, IsClustered = Convert.ToInt32(indexRow["IS_CLUSTERED"]) == 1, IndexType = DatabaseIndexType.Unique, Parent = table }; List<DataRow> indexColumnRows = new List<DataRow>(); indexColumnRows.AddRange(connector.ColumnsBySchema[table.Schema][table.Name.Replace("'", "''")].Where(c => c[OrdColumn].ToString() == indexRow["COLUMN_NAME"].ToString())); while ((i < indexRows.Count - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == table.Name && (string)indexRows[i + 1]["INDEX_NAME"] == (string)indexRow["INDEX_NAME"]) { i++; indexRow = indexRows[i]; indexColumnRows.AddRange(connector.ColumnsBySchema[table.Schema][table.Name.Replace("'", "''")].Where(c => c[OrdColumn].ToString() == indexRow["COLUMN_NAME"])); } // Fill Columns foreach (DataRow indexColumnRow in indexColumnRows) { var column = index.AddColumn((string)indexColumnRow["COLUMN_NAME"]); if (index.IsUnique && indexColumnRows.Count == 1) { // Columns are only unique if the index has one column column.IsUnique = true; } } index.ResetDefaults(); table.AddIndex(index); } }
public override sealed void Refresh() { using (IDbConnection cnx = GetConnection()) { cnx.Open(); Tables.Clear(); using (IDbCommand cmd = cnx.CreateCommand()) { cmd.CommandType = CommandType.Text; //Case Sensibility cmd.CommandText = CaseSensitivityTestQuery; using (IDataReader reader = cmd.ExecuteReader()) { reader.Read(); IsCaseSensitive = new CaseSensitivity(reader.Read()); } //Tables cmd.CommandText = TableQuery; using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Table table = CreateTable(reader); Tables.Add(table.ToString(), table); } } //Columns & Primary Keys foreach (Table table in Tables.Values.Cast <Table>()) { cmd.CommandText = string.Format(ColumnQuery, table.Name); using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Column column = CreateColumn(reader, table); table.AddColumn(column); int pkpos = (int)reader.GetInt64OrDefault(5); if (pkpos > 0) { if (table.PrimaryKey == null) { table.SetPrimaryKey(CreatePrimaryKey(table)); } PrimaryKey primaryKey = table.PrimaryKey as PrimaryKey; // ReSharper disable PossibleNullReferenceException primaryKey.AddColumn(pkpos, column); // ReSharper restore PossibleNullReferenceException } } } } //Indexes foreach (Table table in Tables.Values.Cast <Table>()) { cmd.CommandText = string.Format(IndexListQuery, table.Name); using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Index index = CreateIndex(reader, table); table.AddIndex(index); } } foreach (Index index in table.Indexes().Cast <Index>()) { cmd.CommandText = string.Format(IndexInfoQuery, index.Name); using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { IColumn column = table.GetColumn(reader.GetStringOrDefault(2)); index.AddColumn(new ColumnForIndex { Column = column, Position = (int)reader.GetInt64OrDefault(0) }); } } } } //ALERT: Foreign Key List TO BE CODED } } }
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); }
public IIndex ProcessIndexNode(XmlNode node, ITable parent, IDatabase db) { IIndex index = new Index(); index.Parent = parent; ProcessScriptBase(index, node); /* <IsUnique>True</IsUnique> <IsClustered>False</IsClustered> <Datatype>Unique</Datatype> <Columns> <ColumnName>ColumnT13</ColumnName> </Columns> */ NodeProcessor proc = new NodeProcessor(node); index.IsUnique = proc.GetBool("IsUnique"); index.IsClustered = proc.GetBool("IsClustered"); index.IndexType = proc.GetEnum<DatabaseIndexType>("Datatype"); var columnNodeList = node.SelectNodes("Columns/ColumnName"); if (columnNodeList != null) foreach (XmlNode columnRef in columnNodeList) index.AddColumn(columnRef.InnerText); return index; }
public void Should_Return_This() { const string expectedXML = IndexWithColumnsXml; Table table1 = new Table("Table1"); table1.AddColumn(new Column("Column1")); Index index = new Index("Index1"); index.Parent = table1; index.AddColumn("Column1"); string outputXML = index.Serialise(new DatabaseSerialisationScheme()); outputXML = XmlSqueezer.RemoveWhitespaceBetweenElements(outputXML); Assert.That(outputXML, Is.EqualTo(expectedXML)); }
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); }