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