public override string Generate(CreateIndexExpression expression)
{
var result = new StringBuilder("CREATE");
if (expression.Index.IsUnique)
result.Append(" UNIQUE");
result.Append(" INDEX {0} ON {1}.{2} (");
var first = true;
foreach (var column in expression.Index.Columns)
{
if (first)
first = false;
else
result.Append(",");
result.Append("\"" + column.Name + "\"");
result.Append(column.Direction == Direction.Ascending ? " ASC" : " DESC");
}
result.Append(")");
return String.Format(result.ToString(), Quoter.QuoteIndexName(expression.Index.Name), Quoter.QuoteSchemaName(expression.Index.SchemaName), Quoter.QuoteTableName(expression.Index.TableName));
/*
var idx = String.Format(result.ToString(), expression.Index.Name, Quoter.QuoteSchemaName(expression.Index.SchemaName), expression.Index.TableName);
if (!expression.Index.IsClustered)
return idx;
// Clustered indexes in Postgres do not cluster updates/inserts to the table after the initial cluster operation is applied.
// To keep the clustered index up to date run CLUSTER TableName periodically
return string.Format("{0}; CLUSTER {1}\"{2}\" ON \"{3}\"", idx, Quoter.QuoteSchemaName(expression.Index.SchemaName), expression.Index.TableName, expression.Index.Name);
*/
}
public void ErrorIsReturnedWhenColumnCountIsZero()
{
var expression = new CreateIndexExpression { Index = { Name = "IX", TableName = "test" } };
var errors = ValidationHelper.CollectErrors(expression);
errors.ShouldContain(ErrorMessages.IndexMustHaveOneOrMoreColumns);
}
public override string Generate(CreateIndexExpression expression)
{
var result = new StringBuilder("CREATE");
if (expression.Index.IsUnique)
result.Append(" UNIQUE");
//if (expression.Index.IsClustered)
// result.Append(" CLUSTERED");
//else
// result.Append(" NONCLUSTERED");
result.Append(" INDEX {0} ON {1} (");
bool first = true;
foreach (IndexColumnDefinition column in expression.Index.Columns)
{
if (first)
first = false;
else
result.Append(",");
result.Append(column.Name);
if (column.Direction == Direction.Ascending)
{
result.Append(" ASC");
}
else
{
result.Append(" DESC");
}
}
result.Append(")");
return String.Format(result.ToString(), expression.Index.Name, expression.Index.TableName);
}
public override string Generate(CreateIndexExpression expression)
{
//Firebird doesn't have particular asc or desc order per column, only per the whole index
// CREATE [UNIQUE] [ASC[ENDING] | [DESC[ENDING]] INDEX indexname
// ON tablename { (<col> [, <col> ...]) | COMPUTED BY (expression) }
// <col> ::= a column not of type ARRAY, BLOB or COMPUTED BY
//
// Assuming the first column's direction for the index's direction.
truncator.Truncate(expression);
StringBuilder indexColumns = new StringBuilder("");
Direction indexDirection = Direction.Ascending;
int columnCount = expression.Index.Columns.Count;
for (int i = 0; i < columnCount; i++)
{
IndexColumnDefinition columnDef = expression.Index.Columns.ElementAt(i);
if (i > 0)
indexColumns.Append(", ");
else indexDirection = columnDef.Direction;
indexColumns.Append(Quoter.QuoteColumnName(columnDef.Name));
}
return String.Format(CreateIndex
, GetUniqueString(expression)
, indexDirection == Direction.Ascending ? "ASC " : "DESC "
, Quoter.QuoteIndexName(expression.Index.Name)
, Quoter.QuoteTableName(expression.Index.TableName)
, indexColumns.ToString());
}
public void ErrorIsReturnedWhenTableNameIsNull()
{
var expression = new CreateIndexExpression { Index = { Name = "IX", TableName = null } };
expression.Index.Columns.Add(new IndexColumnDefinition());
var errors = ValidationHelper.CollectErrors(expression);
errors.ShouldContain(ErrorMessages.TableNameCannotBeNullOrEmpty);
}
public static CreateIndexExpression GetCreateIndexExpression()
{
var expression = new CreateIndexExpression();
expression.Index.Name = TestIndexName;
expression.Index.TableName = TestTableName1;
expression.Index.IsUnique = false;
expression.Index.Columns.Add(new IndexColumnDefinition { Direction = Direction.Ascending, Name = TestColumnName1 });
return expression;
}
public void ErrorIsNotReturnedWhenValidExpression()
{
var expression = new CreateIndexExpression { Index = { Name = "IX", TableName = "test" } };
expression.Index.Columns.Add(new IndexColumnDefinition{ Name = "Column1"});
var errors = ValidationHelper.CollectErrors(expression);
Assert.That(errors.Count, Is.EqualTo(0));
}
public void ShouldDelegateApplyConventionsToIndexDefinition()
{
var definitionMock = new Mock<IndexDefinition>();
var createIndexExpression = new CreateIndexExpression {Index = definitionMock.Object};
var migrationConventions = new Mock<IMigrationConventions>(MockBehavior.Strict).Object;
definitionMock.Setup(id => id.ApplyConventions(migrationConventions)).Verifiable();
createIndexExpression.ApplyConventions(migrationConventions);
definitionMock.VerifyAll();
}
public void CanCreateIndex()
{
var expression = new CreateIndexExpression();
expression.Index.Name = "IX_TEST";
expression.Index.TableName = "TEST_TABLE";
expression.Index.IsUnique = true;
expression.Index.IsClustered = true;
expression.Index.Columns.Add(new IndexColumnDefinition { Direction = Direction.Ascending, Name = "Column1" });
expression.Index.Columns.Add(new IndexColumnDefinition { Direction = Direction.Descending, Name = "Column2" });
string sql = generator.Generate(expression);
sql.ShouldBe("CREATE UNIQUE CLUSTERED INDEX IX_TEST ON TEST_TABLE (Column1 ASC,Column2 DESC)");
}
public void CanCreateAcendingIndex()
{
// Arrange
var columns = new List<ColumnDefinition> {new ColumnDefinition {Name = "Data", Type = DbType.String}};
var index = new CreateIndexExpression { Index = new IndexDefinition { Name = "IDX_Foo", TableName = "Foo" } };
index.Index.Columns.Add(new IndexColumnDefinition { Name = "Data", Direction = Direction.Ascending});
// Act
var table = CreateTable(columns, index);
// Assert
table.Indexes.Count.ShouldBe(1);
table.Indexes.First().Name.ShouldBe("IDX_FOO");
table.Indexes.First().Columns.Count.ShouldBe(1);
table.Indexes.First().Columns.First().Name.ShouldBe("DATA");
table.Indexes.First().Columns.First().Direction.ShouldBe(Direction.Ascending);
}
public void CanCreateCompositeIndex()
{
// Arrange
var columns = new List<ColumnDefinition>
{
new ColumnDefinition { Name = "Data", Type = DbType.String }
, new ColumnDefinition { Name = "Data2", Type = DbType.String }
};
var index = new CreateIndexExpression { Index = new IndexDefinition { Name = "IDX_Foo", TableName = "Foo", IsUnique = true } };
index.Index.Columns.Add(new IndexColumnDefinition { Name = "Data" });
index.Index.Columns.Add(new IndexColumnDefinition { Name = "Data2" });
// Act
var table = CreateTable(columns, index);
// Assert
table.Indexes.First().Columns.Count.ShouldBe(2);
}
public ICreateIndexForTableSyntax Index(string indexName)
{
var expression = new CreateIndexExpression { Index = { Name = indexName } };
_context.Expressions.Add(expression);
return new CreateIndexExpressionBuilder(expression);
}
public ICreateIndexForTableSyntax Index()
{
var expression = new CreateIndexExpression();
_context.Expressions.Add(expression);
return new CreateIndexExpressionBuilder(expression);
}
public override string Generate(CreateIndexExpression expression)
{
var result = new StringBuilder("CREATE");
if (expression.Index.IsUnique)
result.Append(" UNIQUE");
result.Append(" INDEX IF NOT EXISTS {0} ON {1} (");
bool first = true;
foreach (IndexColumnDefinition column in expression.Index.Columns)
{
if (first)
first = false;
else
result.Append(",");
result.Append(column.Name);
}
result.Append(")");
return FormatExpression(result.ToString(), expression.Index.Name, expression.Index.TableName);
}
//Not need for the nonclusted keyword as it is the default mode
public override string GetClusterTypeString(CreateIndexExpression column)
{
return column.Index.IsClustered ? "CLUSTERED " : string.Empty;
}
public virtual void Process(CreateIndexExpression expression)
{
Process(Generator.Generate(expression));
}
public void CanForeignKeyToUniqueColumn()
{
// Arrange
var create = new CreateTableExpression
{
TableName = "Foo",
Columns = new[]{
new ColumnDefinition {Name = "Id", Type = DbType.Int32, IsPrimaryKey = true}
, new ColumnDefinition {Name = "Type", Type = DbType.Int32}
}
};
var index = new CreateIndexExpression
{
Index =
new IndexDefinition()
{
Name = "IDX_FooType",
TableName = "Foo",
IsUnique = true,
Columns = new List<IndexColumnDefinition> { new IndexColumnDefinition { Name = "Type" } }
}
};
var secondTable = new CreateTableExpression
{
TableName = "Bar",
Columns = new[]{
new ColumnDefinition {Name = "Id", Type = DbType.Int32, IsPrimaryKey = true}
, new ColumnDefinition {Name = "FooType", Type = DbType.Int32}
}
};
var foreignKey = new CreateForeignKeyExpression
{
ForeignKey =
new ForeignKeyDefinition()
{
Name = "FK_FooType",
ForeignTable = "Bar",
ForeignColumns = new [] { "FooType" },
PrimaryTable= "Foo",
PrimaryColumns = new[] { "Type" }
}
};
// Act
MigrateToOracleWithData(new List<IMigrationExpression> { create, index, secondTable, foreignKey }, 2);
// Assert
}
public virtual void Indexed(string indexName)
{
_builder.Column.IsIndexed = true;
var index = new CreateIndexExpression
{
Index = new IndexDefinition
{
Name = indexName,
SchemaName = _builder.SchemaName,
TableName = _builder.TableName
}
};
index.Index.Columns.Add(new IndexColumnDefinition
{
Name = _builder.Column.Name
});
_context.Expressions.Add(index);
}
public void CanCreateUniqueIndex()
{
var expression = new CreateIndexExpression();
expression.Index.Name = "IX_TEST";
expression.Index.TableName = "TEST_TABLE";
expression.Index.IsUnique = true;
expression.Index.Columns.Add(new IndexColumnDefinition { Direction = Direction.Ascending, Name = "Column1" });
expression.Index.Columns.Add(new IndexColumnDefinition { Direction = Direction.Descending, Name = "Column2" });
string sql = generator.Generate(expression);
sql.ShouldBe("CREATE UNIQUE INDEX \"IX_TEST\" ON \"public\".\"TEST_TABLE\" (\"Column1\" ASC,\"Column2\" DESC)");
}
public abstract string Generate(CreateIndexExpression expression);
public void CanMigrateTableAndIndexWithOneColumnNameCaseSensitive()
{
// Arrange
var create = new CreateTableExpression
{
TableName = "Foo",
Columns = new[]{
new ColumnDefinition {Name = "id", Type = DbType.Int32}
, new ColumnDefinition {Name = "Data", Type = DbType.Int32}
}
};
var index = new CreateIndexExpression()
{
Index =
new IndexDefinition()
{
Name = "IDX_id",
TableName = "Foo",
Columns = new[] {new IndexColumnDefinition {Name = "id"}}
}
};
// Act
var context = GetDefaultContext();
context.CaseSenstiveColumns.Add("id");
context.PreMigrationTableUpdate = tables =>
{
tables.First().Columns.First().Name = "\\\"id\\\"";
tables.First().Indexes.First().Columns.First().Name = "\\\"id\\\"";
};
CreateTables(create);
CreateIndexes(index);
MigrateTable(context);
// Assert
}
public virtual string GetClusterTypeString(CreateIndexExpression column)
{
return string.Empty;
}
public override string Generate(CreateIndexExpression expression)
{
var result = new StringBuilder("CREATE");
if (expression.Index.IsUnique)
result.Append(" UNIQUE");
result.Append(expression.Index.IsClustered ? " CLUSTERED" : " NONCLUSTERED");
result.Append(" INDEX [{0}] ON {1}[{2}] (");
var first = true;
foreach (var column in expression.Index.Columns)
{
if (first)
first = false;
else
result.Append(",");
result.Append("[" + column.Name + "]");
result.Append(column.Direction == Direction.Ascending ? " ASC" : " DESC");
}
result.Append(")");
return String.Format(result.ToString(), expression.Index.Name, FormatSchema(expression.Index.SchemaName), expression.Index.TableName);
}
public override string GetClusterTypeString(CreateIndexExpression column)
{
return string.Empty;
}
public void CanMigrateTableIndex()
{
// Arrange
var create = new CreateTableExpression
{
TableName = "Foo",
Columns = new[]{
new ColumnDefinition {Name = "Id", Type = DbType.Int32}
}
};
var row = new InsertDataExpression
{
TableName = "Foo"
};
row.Rows.Add(new InsertionDataDefinition { new KeyValuePair<string, object>("Id", 1) });
var index = new CreateIndexExpression
{
Index =
new IndexDefinition()
{
Name = "IDX_Foo",
TableName = "Foo",
Columns = new List<IndexColumnDefinition> {new IndexColumnDefinition {Name = "Id"}}
}
};
// Act
MigrateToOracleWithData(new List<IMigrationExpression> { create, row, index }, 1, c => c.Type = c.Type | MigrationType.Indexes);
// Assert
}
public void Truncate(CreateIndexExpression expression)
{
Truncate(expression.Index);
}
public override string Generate(CreateIndexExpression expression)
{
string[] indexColumns = new string[expression.Index.Columns.Count];
IndexColumnDefinition columnDef;
for (int i = 0; i < expression.Index.Columns.Count; i++)
{
columnDef = expression.Index.Columns.ElementAt(i);
if (columnDef.Direction == Direction.Ascending)
{
indexColumns[i] = Quoter.QuoteColumnName(columnDef.Name) + " ASC";
}
else
{
indexColumns[i] = Quoter.QuoteColumnName(columnDef.Name) + " DESC";
}
}
return String.Format(CreateIndex
, GetUniqueString(expression)
, GetClusterTypeString(expression)
, Quoter.QuoteIndexName(expression.Index.Name)
, Quoter.QuoteTableName(expression.Index.TableName)
, String.Join(", ", indexColumns));
}
public void CanCreateAndDropIndex()
{
_processor.Process(new CreateTableExpression
{
TableName = "Foo",
Columns = { new ColumnDefinition { Name = "Bar", Type = DbType.Decimal } }
});
var index = new CreateIndexExpression
{
Index = new IndexDefinition
{
TableName = "Foo"
,Name = "IDX_Bar"
,Columns = new[] {new IndexColumnDefinition {Name = "Bar"}}
}
};
_processor.Process(index);
var dropIndex = new DeleteIndexExpression
{
Index = new IndexDefinition
{
TableName = "Foo"
,Name = "IDX_Bar"
}
};
_processor.Process(dropIndex);
}
public virtual string GetUniqueString(CreateIndexExpression column)
{
return column.Index.IsUnique ? "UNIQUE " : string.Empty;
}
public virtual string GetIncludeString(CreateIndexExpression column)
{
return column.Index.Includes.Count > 0 ? ") INCLUDE (" : string.Empty;
}