protected override List <IIndexColumn> GetIndexColumnsForIndex(IIndexDefinition index)
{
string selectStmt = "";
selectStmt += "SELECT tc.name as col_name " + "\n";
selectStmt += "FROM sys.index_columns ic, " + "\n";
selectStmt += " sys.columns tc " + "\n";
selectStmt += "WHERE ic.column_id = tc.column_id " + "\n";
selectStmt += " AND ic.object_id = tc.object_id " + "\n";
selectStmt += " AND is_included_column = 0 " + "\n";
selectStmt += string.Format(" AND index_id = {0} ", index.IndexId) + "\n";
selectStmt += string.Format(" AND ic.object_id = Object_id('{0}') ", index.TableName) + "\n";
selectStmt += " AND is_included_column = 0 ";
List <IIndexColumn> columns = new List <IIndexColumn>();
IDataCursor cursor = DbContext.PowerPlant.CreateDataCursor();
try
{
IDataReader reader = cursor.ExecuteReader(selectStmt);
while (reader.Read())
{
string name = reader.GetString(0);
columns.Add(IndexColumnFactory.CreateInstance(name));
}
}
finally
{
cursor.Close();
}
return(columns);
}
protected override List <IIndexColumn> GetIndexColumnsForIndex(IIndexDefinition index)
{
var selectStmt = "";
selectStmt += "SELECT ic.column_name, " + "\n";
selectStmt += " Nvl(iexpr.column_position, -1) is_expression, " + "\n";
selectStmt += " iexpr.column_expression " + "\n";
selectStmt += "FROM user_ind_columns ic " + "\n";
selectStmt += " LEFT OUTER JOIN user_ind_expressions iexpr " + "\n";
selectStmt += " ON ( ic.index_name = iexpr.index_name " + "\n";
selectStmt += " AND ic.column_position = iexpr.column_position ) " + "\n";
selectStmt += string.Format("WHERE ic.index_name = Upper('{0}') ", index.IndexName);
selectStmt += "ORDER BY ic.column_position " + "\n";
var columns = new List <IIndexColumn>();
var cursor = DbContext.PowerPlant.CreateDataCursor();
try
{
var reader = cursor.ExecuteReader(selectStmt);
while (reader.Read())
{
var name = reader.GetString(0);
var isExpression = reader.GetInt32(1) != -1;
var expression = reader.IsDBNull(2) ? " " : reader.GetString(2);
columns.Add(IndexColumnFactory.CreateInstance(name, expression));
}
}
finally
{
cursor.Close();
}
return(columns);
}
private List <IIndexColumn> SplitColumns(string columnList)
{
return(columnList
.Split(
new[] { ',', ' ' },
StringSplitOptions.RemoveEmptyEntries)
.Select(
column => IsLegalName(column)
? IndexColumnFactory.CreateInstance(column)
: IndexColumnFactory.CreateInstance("expression", column)).ToList());
}
public void Test_MS_CreateClusteredIndex()
{
Initialize(_msContext);
_commands.ExecuteNonQuery(string.Format("create table {0} (id int, id2 int, id3 int)", _testTable));
IIndexDefinition index = _msContext.PowerPlant.CreateIndexDefinition("i1_" + _testTable, _testTable, "", true, 0, true);
index.Columns = new List <IIndexColumn> {
IndexColumnFactory.CreateInstance("id")
};
_dbSchema.CreateIndex(index);
CheckIndexIsClustered();
}
private void ClusteredIndexOnAgrtid(ITableDefinition tableDefinition)
{
if (CreateClusteredIndex && _dbContext.DbType == DbTypeName.SqlServer)
{
var index = _dbContext.PowerPlant.CreateIndexDefinition(
"aic_" + tableDefinition.Name,
tableDefinition.Name,
tableDefinition.Location,
true, /* is unique */
0, /* id */
true /* is clustered */);
index.Columns = new List <IIndexColumn> {
IndexColumnFactory.CreateInstance("agrtid")
};
_dbSchema.CreateIndex(index);
}
}
private void ReadIndexInfo(ITableDefinition tableDefinition, XmlNode index)
{
Debug.Assert(index != null, "index != null");
string indexName = index["IndexName"].InnerText;
string location = index["Location"].InnerText;
bool isUnique = Convert.ToBoolean(index["IsUnique"].InnerText);
string db = index["DbSpecific"].InnerText;
IIndexDefinition indexDefinition = _dbContext.PowerPlant.CreateIndexDefinition(indexName, tableDefinition.Name, location, isUnique);
indexDefinition.DbSpecific = TableDefinition.ConvertStringToDbType(db);
indexDefinition.Columns = new List <IIndexColumn>();
foreach (XmlNode indexCol in index["IndexColumns"].ChildNodes)
{
indexDefinition.Columns.Add(IndexColumnFactory.CreateInstance(indexCol.Name, indexCol.InnerText));
}
tableDefinition.Indexes.Add(indexDefinition);
}
private void Test_CreateIndex()
{
_commands.ExecuteNonQuery(string.Format("create table {0} (id int, id2 int, id3 int)", _testTable));
List <IIndexDefinition> indexes = new List <IIndexDefinition>
{
_msContext.PowerPlant.CreateIndexDefinition("i1_" + _testTable, _testTable, "", true)
};
indexes[0].Columns = new List <IIndexColumn> {
IndexColumnFactory.CreateInstance("id")
};
indexes.Add(_msContext.PowerPlant.CreateIndexDefinition("i2_" + _testTable, _testTable, "", false));
indexes[1].Columns = new List <IIndexColumn>
{
IndexColumnFactory.CreateInstance("id2"),
IndexColumnFactory.CreateInstance("id3")
};
_dbSchema.CreateIndexes(indexes);
CheckIndexes();
}
