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 GetIndexNameReturnsValidIndexNameForSimpleIndex()
{
var expr = new CreateIndexExpression()
{
Index =
{
TableName = "Bacon",
Columns =
{
new IndexColumnDefinition {
Name = "BaconName",Direction = Direction.Ascending
}
}
}
};
var processed = expr.Apply(ConventionSets.NoSchemaName);
processed.Index.Name.ShouldBe("IX_Bacon_BaconName");
}
public ICreateColumnOptionSyntax Indexed(string indexName)
{
Expression.Column.IsIndexed = true;
var index = new CreateIndexExpression(_context.CurrentDatabaseProvider, _databaseProviders, Expression.SqlSyntax, new IndexDefinition
{
Name = indexName,
SchemaName = Expression.SchemaName,
TableName = Expression.TableName
});
index.Index.Columns.Add(new IndexColumnDefinition
{
Name = Expression.Column.Name
});
_context.Expressions.Add(index);
return(this);
}
/// <inheritdoc />
public ICreateTableColumnOptionBuilder Indexed(string indexName)
{
CurrentColumn.IsIndexed = true;
var index = new CreateIndexExpression(_context, new IndexDefinition
{
Name = indexName,
SchemaName = Expression.SchemaName,
TableName = Expression.TableName
});
index.Index.Columns.Add(new IndexColumnDefinition
{
Name = CurrentColumn.Name
});
Expression.Expressions.Add(index);
return(this);
}
public ICreateColumnOptionBuilder Unique(string?indexName)
{
Expression.Column.IsUnique = true;
var index = new CreateIndexExpression(_context, new IndexDefinition
{
Name = indexName,
TableName = Expression.TableName,
IndexType = IndexTypes.UniqueNonClustered
});
index.Index.Columns.Add(new IndexColumnDefinition
{
Name = Expression.Column.Name
});
Expression.Expressions.Add(index);
return(this);
}
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 static CreateIndexExpression GetCreateMultiIncludeIndexExpression()
{
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
});
var includes = expression.Index.GetAdditionalFeature(SqlServerExtensions.IncludesList, () => new List <IndexIncludeDefinition>());
includes.Add(new IndexIncludeDefinition {
Name = TestColumnName2
});
includes.Add(new IndexIncludeDefinition {
Name = TestColumnName3
});
return(expression);
}
public IAlterTableColumnOptionSyntax Unique(string indexName)
{
CurrentColumn.IsUnique = true;
var index = new CreateIndexExpression(_context.CurrentDatabaseProvider, _databaseProviders, Expression.SqlSyntax, new IndexDefinition
{
Name = indexName,
SchemaName = Expression.SchemaName,
TableName = Expression.TableName,
IndexType = IndexTypes.UniqueNonClustered
});
index.Index.Columns.Add(new IndexColumnDefinition
{
Name = CurrentColumn.Name
});
_context.Expressions.Add(index);
return(this);
}
public void CanCreateUniqueIndexWithNonDistinctNullsAlternativeSyntax()
{
var expression = new CreateIndexExpression()
{
Index =
{
Name = GeneratorTestHelper.TestIndexName,
}
};
var builder = new CreateIndexExpressionBuilder(expression);
builder
.OnTable(GeneratorTestHelper.TestTableName1)
.OnColumn(GeneratorTestHelper.TestColumnName1)
.Unique().NullsNotDistinct();
var result = _generator.Generate(expression);
result.ShouldBe("CREATE UNIQUE INDEX [TestIndex] ON [dbo].[TestTable1] ([TestColumn1] ASC) WHERE [TestColumn1] IS NOT NULL");
}
public void ShouldApplyIndexNameConventionWhenIndexNameIsNull()
{
var expr = new CreateIndexExpression()
{
Index =
{
TableName = "Table",
Columns =
{
new IndexColumnDefinition()
{
Name = "Name"
}
}
}
};
var processed = expr.Apply(ConventionSets.NoSchemaName);
Assert.AreEqual("IX_Table_Name", processed.Index.Name);
}
private static CreateIndexExpression GetCreateIndexExpression(Algorithm algorithm)
{
var expression = new CreateIndexExpression
{
Index =
{
Name = GeneratorTestHelper.TestIndexName,
TableName = GeneratorTestHelper.TestTableName1
}
};
expression.Index.Columns.Add(new IndexColumnDefinition {
Direction = Direction.Ascending, Name = GeneratorTestHelper.TestColumnName1
});
var definition = expression.Index.GetAdditionalFeature(PostgresExtensions.IndexAlgorithm, () => new PostgresIndexAlgorithmDefinition());
definition.Algorithm = algorithm;
return(expression);
}
public override string Generate(CreateIndexExpression expression)
{
var result = new StringBuilder("CREATE");
if (expression.Index.IsUnique)
{
result.Append(" UNIQUE");
}
result.AppendFormat(" INDEX{0} {1} ON{2} {3}{4} (",
GetAsConcurrently(expression),
Quoter.QuoteIndexName(expression.Index.Name),
GetAsOnly(expression),
Quoter.QuoteTableName(expression.Index.TableName, expression.Index.SchemaName),
GetUsingAlgorithm(expression));
var first = true;
foreach (var column in expression.Index.Columns)
{
if (first)
{
first = false;
}
else
{
result.Append(",");
}
result.Append(Quoter.QuoteColumnName(column.Name));
result.Append(column.Direction == Direction.Ascending ? " ASC" : " DESC");
}
result.Append(")")
.Append(GetIncludeString(expression))
.Append(GetFilter(expression))
.Append(";");
return(result.ToString());
}
public void CanCreateUniqueIndexWithDistinctNulls()
{
var expression = new CreateIndexExpression()
{
Index =
{
Name = GeneratorTestHelper.TestIndexName,
}
};
var builder = new CreateIndexExpressionBuilder(expression);
builder
.OnTable(GeneratorTestHelper.TestTableName1)
.OnColumn(GeneratorTestHelper.TestColumnName1)
.Ascending()
.WithOptions().UniqueNullsDistinct();
var result = Generator.Generate(expression);
result.ShouldBe("CREATE UNIQUE INDEX [TestIndex] ON [dbo].[TestTable1] ([TestColumn1] ASC) WITH NULLS DISTINCT");
}
public void CanCreateMultiColumnUniqueIndexWithNonDistinctNullsWithSingleColumnNullsDistinct()
{
var expression = new CreateIndexExpression()
{
Index =
{
Name = GeneratorTestHelper.TestIndexName,
}
};
var builder = new CreateIndexExpressionBuilder(expression);
builder
.OnTable(GeneratorTestHelper.TestTableName1)
.OnColumn(GeneratorTestHelper.TestColumnName1).Ascending().NullsDistinct()
.OnColumn(GeneratorTestHelper.TestColumnName2).Descending()
.WithOptions().UniqueNullsNotDistinct();
var result = _generator.Generate(expression);
result.ShouldBe("CREATE UNIQUE INDEX [TestIndex] ON [dbo].[TestTable1] ([TestColumn1] ASC, [TestColumn2] DESC) WHERE [TestColumn2] IS NOT NULL");
}
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
, CommandDelimiter));
}
private static IEnumerable <MigrationExpressionBase> GetUniqueKeys(Table table)
{
foreach (var uk in table.UniqueKeyIterator)
{
var indexDefinition = new IndexDefinition();
indexDefinition.SchemaName = table.Schema;
indexDefinition.TableName = table.Name;
indexDefinition.Name = uk.Name;
indexDefinition.IsUnique = true;
indexDefinition.IsClustered = false;
indexDefinition.Columns = uk.Columns.Select(c => new IndexColumnDefinition
{
Name = c.Name,
Direction = Direction.Ascending
}).ToList();
var createIndexExpression = new CreateIndexExpression
{
Index = indexDefinition
};
yield return(createIndexExpression);
}
}
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";
}
}
string[] indexIncludes = new string[expression.Index.Includes.Count];
IndexIncludeDefinition includeDef;
for (int i = 0; i < expression.Index.Includes.Count; i++)
{
includeDef = expression.Index.Includes.ElementAt(i);
indexIncludes[i] = Quoter.QuoteColumnName(includeDef.Name);
}
return(String.Format(CreateIndex
, GetUniqueString(expression)
, GetClusterTypeString(expression)
, Quoter.QuoteIndexName(expression.Index.Name)
, Quoter.QuoteSchemaName(expression.Index.SchemaName)
, Quoter.QuoteTableName(expression.Index.TableName)
, String.Join(", ", indexColumns)
, GetIncludeString(expression)
, String.Join(", ", indexIncludes)));
}
public IAlterColumnOptionSyntax Indexed(string indexName)
{
Expression.Column.IsIndexed = true;
var index = new CreateIndexExpression
{
Index = new IndexDefinition
{
Name = indexName,
SchemaName = Expression.SchemaName,
TableName = Expression.TableName
}
};
index.Index.Columns.Add(new IndexColumnDefinition
{
Name = Expression.Column.Name
});
_context.Expressions.Add(index);
return(this);
}
public string Generate(CreateIndexExpression expression)
{
var indexColumns = new string[expression.Index.Columns.Count];
for (var i = 0; i < expression.Index.Columns.Count; i++)
{
var 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(CreateIndexSqlTemplate
, GetUniqueString(expression)
, QuoteSchemaAndIdentifier(expression.Index.Name, expression.Index.SchemaName)
, Quoter.QuoteTableName(expression.Index.TableName, expression.Index.SchemaName)
, string.Join(", ", indexColumns)));
}
/// <summary>
/// Creates an index.
/// </summary>
/// <param name="dbIndexes">
/// The collection of database indexes.
/// </param>
/// <param name="indexName">
/// The index name.
/// </param>
/// <param name="columnName">
/// The column name.
/// </param>
private void CreateIndex(DbIndexDefinition[] dbIndexes, string indexName, string columnName)
{
//// make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals(indexName)) == false)
{
Logger.Info(typeof(AddIndexesToProductVariant), "Adding nonclustered index to " + columnName + " column on merchInvoice table.");
var sqlSyntax = ApplicationContext.Current.DatabaseContext.SqlSyntax;
var dbProvider = ApplicationContext.Current.DatabaseContext.DatabaseProvider;
var createExpression = new CreateIndexExpression(dbProvider, new[] { dbProvider }, sqlSyntax)
{
Index = { Name = indexName }
};
var builder = new CreateIndexBuilder(createExpression);
builder.OnTable("merchInvoice").OnColumn(columnName).Ascending().WithOptions().NonClustered();
ApplicationContext.Current.DatabaseContext.Database.Execute(createExpression.ToString());
}
}
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";
}
}
var withParts = GetWithOptions(expression);
var withPart = !string.IsNullOrEmpty(withParts)
? $" WITH ({withParts})"
: string.Empty;
var result = string.Format(
CreateIndex,
GetUniqueString(expression),
GetClusterTypeString(expression),
Quoter.QuoteIndexName(expression.Index.Name),
Quoter.QuoteTableName(expression.Index.TableName, expression.Index.SchemaName),
string.Join(", ", indexColumns),
GetFilterString(expression),
GetIncludeString(expression),
withPart);
return(result);
}
protected override string GetIncludeString(CreateIndexExpression column)
{
var includes = column.GetAdditionalFeature <IList <PostgresIndexIncludeDefinition> >(PostgresExtensions.IncludesList);
if (includes == null || includes.Count == 0)
{
return(string.Empty);
}
var result = new StringBuilder(" INCLUDE (");
result.Append(Quoter.QuoteColumnName(includes[0].Name));
for (var i = 1; i < includes.Count; i++)
{
result
.Append(", ")
.Append(Quoter.QuoteColumnName(includes[i].Name));
}
return(result
.Append(")")
.ToString());
}
public override string Generate(CreateConstraintExpression expression)
{
if (!expression.Constraint.IsUniqueConstraint)
{
return(CompatibilityMode.HandleCompatibilty("Only UNIQUE constraints are supported in SQLite"));
}
// Convert the constraint into a UNIQUE index
var idx = new CreateIndexExpression();
idx.Index.Name = expression.Constraint.ConstraintName;
idx.Index.TableName = expression.Constraint.TableName;
idx.Index.SchemaName = expression.Constraint.SchemaName;
idx.Index.IsUnique = true;
foreach (var col in expression.Constraint.Columns)
{
idx.Index.Columns.Add(new IndexColumnDefinition {
Name = col
});
}
return(Generate(idx));
}
public ICreateTableColumnOptionOrWithColumnSyntax Unique(string indexName)
{
CurrentColumn.IsUnique = true;
var index = new CreateIndexExpression
{
Index = new IndexDefinition
{
Name = indexName,
SchemaName = Expression.SchemaName,
TableName = Expression.TableName,
IsUnique = true
}
};
index.Index.Columns.Add(new IndexColumnDefinition
{
Name = CurrentColumn.Name
});
_context.Expressions.Add(index);
return(this);
}
public virtual void Process(CreateIndexExpression expression)
{
Process(Generator.Generate(expression));
}
public override string GetClusterTypeString(CreateIndexExpression column)
{
return(string.Empty);
}
//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 abstract string Generate(CreateIndexExpression expression);
public virtual string GetClusterTypeString(CreateIndexExpression column)
{
return(string.Empty);
}
public virtual string GetUniqueString(CreateIndexExpression column)
{
return(column.Index.IsUnique ? "UNIQUE " : string.Empty);
}