protected virtual void Generate(
[NotNull] CreateIndexOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder.Append("CREATE ");
if (operation.IsUnique)
{
builder.Append("UNIQUE ");
}
IndexTraits(operation, model, builder);
builder
.Append("INDEX ")
.Append(SqlGenerator.DelimitIdentifier(operation.Name))
.Append(" ON ")
.Append(SqlGenerator.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" (")
.Append(ColumnList(operation.Columns))
.Append(")");
}
protected override void Generate(
[NotNull] CreateIndexOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var method = (string)operation[NpgsqlAnnotationNames.Prefix + NpgsqlAnnotationNames.IndexMethod];
builder.Append("CREATE ");
if (operation.IsUnique)
{
builder.Append("UNIQUE ");
}
builder
.Append("INDEX ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" ON ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema));
if (method != null)
{
builder
.Append(" USING ")
.Append(method);
}
builder
.Append(" (")
.Append(ColumnList(operation.Columns))
.Append(")");
}
protected virtual void UniqueConstraint(
[NotNull] AddUniqueConstraintOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
if (operation.Name != null)
{
builder
.Append("CONSTRAINT ")
.Append(SqlGenerator.DelimitIdentifier(operation.Name))
.Append(" ");
}
builder
.Append("UNIQUE ");
IndexTraits(operation, model, builder);
builder.Append("(")
.Append(ColumnList(operation.Columns))
.Append(")");
}
protected virtual void ForeignKeyAction(
ReferentialAction referentialAction,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(builder, nameof(builder));
switch (referentialAction)
{
case ReferentialAction.Restrict:
builder.Append("RESTRICT");
break;
case ReferentialAction.Cascade:
builder.Append("CASCADE");
break;
case ReferentialAction.SetNull:
builder.Append("SET NULL");
break;
case ReferentialAction.SetDefault:
builder.Append("SET DEFAULT");
break;
default:
Debug.Assert(
referentialAction == ReferentialAction.NoAction,
"Unexpected value: " + referentialAction);
break;
}
}
protected virtual void Generate(
[NotNull] CreateTableOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("CREATE TABLE ")
.Append(SqlGenerator.DelimitIdentifier(operation.Name, operation.Schema))
.AppendLine(" (");
using (builder.Indent())
{
for (var i = 0; i < operation.Columns.Count; i++)
{
var column = operation.Columns[i];
ColumnDefinition(column, model, builder);
if (i != operation.Columns.Count - 1)
{
builder.AppendLine(",");
}
}
if (operation.PrimaryKey != null)
{
builder.AppendLine(",");
PrimaryKeyConstraint(operation.PrimaryKey, model, builder);
}
foreach (var uniqueConstraint in operation.UniqueConstraints)
{
builder.AppendLine(",");
UniqueConstraint(uniqueConstraint, model, builder);
}
foreach (var foreignKey in operation.ForeignKeys)
{
builder.AppendLine(",");
ForeignKeyConstraint(foreignKey, model, builder);
}
builder.AppendLine();
}
builder.Append(")");
}
protected virtual void ColumnDefinition(
[CanBeNull] string schema,
[CanBeNull] string table,
[NotNull] string name,
[NotNull] Type clrType,
[CanBeNull] string type,
bool nullable,
[CanBeNull] object defaultValue,
[CanBeNull] string defaultValueSql,
[CanBeNull] string computedColumnSql,
bool identity,
[NotNull] IAnnotatable annotatable,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotEmpty(name, nameof(name));
Check.NotNull(clrType, nameof(clrType));
Check.NotNull(annotatable, nameof(annotatable));
Check.NotNull(builder, nameof(builder));
if (computedColumnSql != null)
{
builder
.Append(SqlGenerationHelper.DelimitIdentifier(name))
.Append(" AS ")
.Append(computedColumnSql);
return;
}
base.ColumnDefinition(
schema,
table,
name,
clrType,
type,
nullable,
defaultValue,
defaultValueSql,
computedColumnSql,
annotatable,
model,
builder);
if (identity)
{
builder.Append(" IDENTITY");
}
}
public virtual void Generate(NpgsqlCreateDatabaseOperation operation, IModel model, RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("CREATE DATABASE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name));
if (operation.Template != null)
{
builder
.Append(" TEMPLATE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Template));
}
}
protected override void Generate(DropIndexOperation operation, IModel model, RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("DROP INDEX ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name));
}
protected override void Generate(
AlterColumnOperation operation,
IModel model,
RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
DropDefaultConstraint(operation.Schema, operation.Table, operation.Name, builder);
builder
.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" ALTER COLUMN ");
ColumnDefinition(
operation.Schema,
operation.Table,
operation.Name,
operation.ClrType,
operation.ColumnType,
operation.IsNullable,
/*defaultValue:*/ null,
/*defaultValueSql:*/ null,
operation.ComputedColumnSql,
/*identity:*/ false,
operation,
model,
builder);
if ((operation.DefaultValue != null) ||
(operation.DefaultValueSql != null))
{
builder
.AppendLine(";")
.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" ADD");
DefaultValue(operation.DefaultValue, operation.DefaultValueSql, builder);
builder
.Append(" FOR ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name));
}
}
protected virtual void Generate(
[NotNull] SqlOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder.Append(operation.Sql);
}
protected virtual void DropDefaultConstraint(
[CanBeNull] string schema,
[NotNull] string tableName,
[NotNull] string columnName,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotEmpty(tableName, nameof(tableName));
Check.NotEmpty(columnName, nameof(columnName));
Check.NotNull(builder, nameof(builder));
var variable = "@var" + _variableCounter++;
builder
.Append("DECLARE ")
.Append(variable)
.AppendLine(" sysname;")
.Append("SELECT ")
.Append(variable)
.AppendLine(" = [d].[name]")
.AppendLine("FROM [sys].[default_constraints] [d]")
.AppendLine("INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id]")
.Append("WHERE ([d].[parent_object_id] = OBJECT_ID(N'");
if (schema != null)
{
builder
.Append(SqlGenerationHelper.EscapeLiteral(schema))
.Append(".");
}
builder
.Append(SqlGenerationHelper.EscapeLiteral(tableName))
.Append("') AND [c].[name] = N'")
.Append(SqlGenerationHelper.EscapeLiteral(columnName))
.AppendLine("');")
.Append("IF ")
.Append(variable)
.Append(" IS NOT NULL EXEC(N'ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(tableName, schema))
.Append(" DROP CONSTRAINT [' + ")
.Append(variable)
.AppendLine(" + ']');");
}
protected override void Generate(CreateSequenceOperation operation, IModel model, RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
if (operation.ClrType != typeof(long))
{
throw new NotSupportedException("PostgreSQL sequences can only be bigint (long)");
}
builder
.Append("CREATE SEQUENCE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema));
builder
.Append(" START WITH ")
.Append(SqlGenerationHelper.GenerateLiteral(operation.StartValue));
SequenceOptions(operation, model, builder);
}
protected virtual void ForeignKeyConstraint(
[NotNull] AddForeignKeyOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
if (operation.Name != null)
{
builder
.Append("CONSTRAINT ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" ");
}
builder
.Append("FOREIGN KEY (")
.Append(ColumnList(operation.Columns))
.Append(") REFERENCES ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.PrincipalTable, operation.PrincipalSchema));
if (operation.PrincipalColumns != null)
{
builder
.Append(" (")
.Append(ColumnList(operation.PrincipalColumns))
.Append(")");
}
if (operation.OnUpdate != ReferentialAction.NoAction)
{
builder.Append(" ON UPDATE ");
ForeignKeyAction(operation.OnUpdate, builder);
}
if (operation.OnDelete != ReferentialAction.NoAction)
{
builder.Append(" ON DELETE ");
ForeignKeyAction(operation.OnDelete, builder);
}
}
protected virtual void Generate(
[NotNull] DropTableOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("DROP TABLE ")
.Append(SqlGenerator.DelimitIdentifier(operation.Name, operation.Schema));
}
protected virtual void DefaultValue(
[CanBeNull] object defaultValue,
[CanBeNull] string defaultValueSql,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(builder, nameof(builder));
if (defaultValueSql != null)
{
builder
.Append(" DEFAULT (")
.Append(defaultValueSql)
.Append(")");
}
else if (defaultValue != null)
{
builder
.Append(" DEFAULT ")
.Append(SqlGenerator.GenerateLiteral(defaultValue));
}
}
protected override void IndexTraits(MigrationOperation operation, IModel model, RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
var clustered = operation[SqlServerFullAnnotationNames.Instance.Clustered] as bool?;
if (clustered.HasValue)
{
builder.Append(clustered.Value ? "CLUSTERED " : "NONCLUSTERED ");
}
}
protected virtual void ColumnDefinition(
[CanBeNull] string schema,
[CanBeNull] string table,
[NotNull] string name,
[NotNull] Type clrType,
[CanBeNull] string type,
bool nullable,
[CanBeNull] object defaultValue,
[CanBeNull] string defaultValueSql,
[CanBeNull] string computedColumnSql,
[NotNull] IAnnotatable annotatable,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotEmpty(name, nameof(name));
Check.NotNull(clrType, nameof(clrType));
Check.NotNull(annotatable, nameof(annotatable));
Check.NotNull(builder, nameof(builder));
if (type == null)
{
var property = FindProperty(model, schema, table, name);
type = property != null
? TypeMapper.GetMapping(property).DefaultTypeName
: TypeMapper.GetMapping(clrType).DefaultTypeName;
}
builder
.Append(SqlGenerator.DelimitIdentifier(name))
.Append(" ")
.Append(type);
if (!nullable)
{
builder.Append(" NOT NULL");
}
DefaultValue(defaultValue, defaultValueSql, builder);
}
protected override void ForeignKeyAction(ReferentialAction referentialAction, RelationalCommandListBuilder builder)
{
Check.NotNull(builder, nameof(builder));
if (referentialAction == ReferentialAction.Restrict)
{
builder.Append("NO ACTION");
}
else
{
base.ForeignKeyAction(referentialAction, builder);
}
}
protected override void ColumnDefinition(
string schema,
string table,
string name,
Type clrType,
string type,
bool nullable,
object defaultValue,
string defaultValueSql,
string computedColumnSql,
IAnnotatable annotatable,
IModel model,
RelationalCommandListBuilder builder)
{
base.ColumnDefinition(
schema, table, name, clrType, type, nullable,
defaultValue, defaultValueSql, computedColumnSql, annotatable, model, builder);
var inlinePk = annotatable[SqliteFullAnnotationNames.Instance.InlinePrimaryKey] as bool?;
if (inlinePk == true)
{
var inlinePkName = annotatable[
SqliteFullAnnotationNames.Instance.InlinePrimaryKeyName] as string;
if (!string.IsNullOrEmpty(inlinePkName))
{
builder
.Append(" CONSTRAINT ")
.Append(SqlGenerationHelper.DelimitIdentifier(inlinePkName));
}
builder.Append(" PRIMARY KEY");
var autoincrement = annotatable[SqliteFullAnnotationNames.Instance.Autoincrement] as bool?;
if (autoincrement == true)
{
builder.Append(" AUTOINCREMENT");
}
}
}
protected virtual void Rename(
[NotNull] string name,
[NotNull] string newName,
[CanBeNull] string type,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotEmpty(name, nameof(name));
Check.NotEmpty(newName, nameof(newName));
Check.NotNull(builder, nameof(builder));
builder
.Append("EXEC sp_rename ")
.Append(SqlGenerationHelper.GenerateLiteral(name))
.Append(", ")
.Append(SqlGenerationHelper.GenerateLiteral(newName));
if (type != null)
{
builder
.Append(", ")
.Append(SqlGenerationHelper.GenerateLiteral(type));
}
}
protected virtual void Generate(
[NotNull] AlterSequenceOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("ALTER SEQUENCE ")
.Append(SqlGenerator.DelimitIdentifier(operation.Name, operation.Schema));
SequenceOptions(operation, model, builder);
}
protected virtual void Generate(
[NotNull] AddPrimaryKeyOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("ALTER TABLE ")
.Append(SqlGenerator.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" ADD ");
PrimaryKeyConstraint(operation, model, builder);
}
protected virtual void Generate(
[NotNull] DropUniqueConstraintOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("ALTER TABLE ")
.Append(SqlGenerator.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" DROP CONSTRAINT ")
.Append(SqlGenerator.DelimitIdentifier(operation.Name));
}
protected virtual void Generate(
[NotNull] RestartSequenceOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("ALTER SEQUENCE ")
.Append(SqlGenerator.DelimitIdentifier(operation.Name, operation.Schema))
.Append(" RESTART WITH ")
.Append(SqlGenerator.GenerateLiteral(operation.StartValue));
}
protected override void Generate(RenameTableOperation operation, IModel model, RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
if (operation.NewName != null)
{
builder
.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" RENAME TO ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.NewName));
}
}
protected virtual void Generate(
[NotNull] AddColumnOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" ADD ");
ColumnDefinition(operation, model, builder);
}
protected override void Generate(CreateIndexOperation operation, IModel model, RelationalCommandListBuilder builder)
{
base.Generate(operation, model, builder);
var clustered = operation[SqlServerFullAnnotationNames.Instance.Clustered] as bool?;
if (operation.IsUnique &&
(clustered != true))
{
builder.Append(" WHERE ");
for (var i = 0; i < operation.Columns.Length; i++)
{
if (i != 0)
{
builder.Append(" AND ");
}
builder
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Columns[i]))
.Append(" IS NOT NULL");
}
}
}
public virtual void Generate(NpgsqlCreatePostgresExtensionOperation operation, IModel model, RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("CREATE EXTENSION ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name));
if (operation.Schema != null)
{
builder
.Append(" SCHEMA ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Schema));
}
if (operation.Version != null)
{
builder
.Append(" VERSION ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Version));
}
}
protected virtual void Generate(
[NotNull] CreateSequenceOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder
.Append("CREATE SEQUENCE ")
.Append(SqlGenerator.DelimitIdentifier(operation.Name, operation.Schema));
if (operation.ClrType != typeof(long))
{
builder
.Append(" AS ")
.Append(TypeMapper.GetMapping(operation.ClrType).DefaultTypeName);
}
builder
.Append(" START WITH ")
.Append(SqlGenerator.GenerateLiteral(operation.StartValue));
SequenceOptions(operation, model, builder);
}
protected override void Generate(
[NotNull] RenameColumnOperation operation,
[CanBeNull] IModel model,
[NotNull] RelationalCommandListBuilder builder)
{
Check.NotNull(operation, nameof(operation));
Check.NotNull(builder, nameof(builder));
builder.Append("ALTER TABLE ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Table, operation.Schema))
.Append(" RENAME COLUMN ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.Name))
.Append(" TO ")
.Append(SqlGenerationHelper.DelimitIdentifier(operation.NewName));
}
