public void CreateTableOperation_with_cockroach_interleave_in_parent()
{
var op =
new CreateTableOperation
{
Name = "People",
Schema = "dbo",
Columns =
{
new AddColumnOperation
{
Name = "Id",
Table = "People",
Schema = "dbo",
ClrType = typeof(int),
IsNullable = false
},
},
PrimaryKey = new AddPrimaryKeyOperation
{
Columns = new[] { "Id" }
}
};
var interleaveInParent = new CockroachDbInterleaveInParent(op);
interleaveInParent.ParentTableSchema = "my_schema";
interleaveInParent.ParentTableName = "my_parent";
interleaveInParent.InterleavePrefix = new List <string> {
"col_a", "col_b"
};
Generate(op);
AssertSql(
@"CREATE TABLE dbo.""People"" (
""Id"" integer NOT NULL,
PRIMARY KEY (""Id"")
)
INTERLEAVE IN PARENT my_schema.my_parent (col_a, col_b);
");
}
public void CreateTableOperation_with_cockroach_interleave_in_parent()
{
var op =
new CreateTableOperation
{
Name = "People",
Schema = "dbo",
Columns =
{
new AddColumnOperation
{
Name = "Id",
Table = "People",
ClrType = typeof(int),
IsNullable = false
},
},
PrimaryKey = new AddPrimaryKeyOperation
{
Columns = new[] { "Id" }
}
};
var interleaveInParent = new CockroachDbInterleaveInParent(op);
interleaveInParent.ParentTableSchema = "my_schema";
interleaveInParent.ParentTableName = "my_parent";
interleaveInParent.InterleavePrefix = new List <string> {
"col_a", "col_b"
};
Generate(op);
Assert.Equal(
"CREATE TABLE \"dbo\".\"People\" (" + EOL +
" \"Id\" int4 NOT NULL," + EOL +
" PRIMARY KEY (\"Id\")" + EOL +
")" + EOL +
"INTERLEAVE IN PARENT \"my_schema\".\"my_parent\" (\"col_a\", \"col_b\");" + EOL,
Sql);
}
protected override void Generate(
CreateTableOperation operation,
IModel model,
MigrationCommandListBuilder builder,
bool terminate)
{
// Filter out any system columns
if (operation.Columns.Any(c => IsSystemColumn(c.Name)))
{
var filteredOperation = new CreateTableOperation
{
Name = operation.Name,
Schema = operation.Schema,
PrimaryKey = operation.PrimaryKey,
};
filteredOperation.Columns.AddRange(operation.Columns.Where(c => !_systemColumnNames.Contains(c.Name)));
filteredOperation.ForeignKeys.AddRange(operation.ForeignKeys);
filteredOperation.UniqueConstraints.AddRange(operation.UniqueConstraints);
operation = filteredOperation;
}
base.Generate(operation, model, builder, false);
// CockroachDB "interleave in parent" (https://www.cockroachlabs.com/docs/stable/interleave-in-parent.html)
if (operation[CockroachDbAnnotationNames.InterleaveInParent] is string)
{
var interleaveInParent = new CockroachDbInterleaveInParent(operation);
var parentTableSchema = interleaveInParent.ParentTableSchema;
var parentTableName = interleaveInParent.ParentTableName;
var interleavePrefix = interleaveInParent.InterleavePrefix;
builder
.AppendLine()
.Append("INTERLEAVE IN PARENT ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(parentTableName, parentTableSchema))
.Append(" (")
.Append(string.Join(", ", interleavePrefix.Select(c => Dependencies.SqlGenerationHelper.DelimitIdentifier(c))))
.Append(')');
}
var storageParameters = GetStorageParameters(operation);
if (storageParameters.Count > 0)
{
builder
.AppendLine()
.Append("WITH (")
.Append(string.Join(", ", storageParameters.Select(p => $"{p.Key}={p.Value}")))
.Append(')');
}
// Comment on the table
if (operation[NpgsqlAnnotationNames.Comment] is string comment && comment.Length > 0)
{
builder.AppendLine(';');
var stringTypeMapping = Dependencies.TypeMappingSource.GetMapping(typeof(string));
builder
.Append("COMMENT ON TABLE ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.Append(" IS ")
.Append(stringTypeMapping.GenerateSqlLiteral(comment));
}
// Comments on the columns
foreach (var columnOp in operation.Columns.Where(c => c[NpgsqlAnnotationNames.Comment] != null))
{
var columnComment = columnOp[NpgsqlAnnotationNames.Comment];
builder.AppendLine(';');
var stringTypeMapping = Dependencies.TypeMappingSource.GetMapping(typeof(string));
builder
.Append("COMMENT ON COLUMN ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
.Append('.')
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(columnOp.Name))
.Append(" IS ")
.Append(stringTypeMapping.GenerateSqlLiteral(columnComment));
}
if (terminate)
{
builder.AppendLine(';');
EndStatement(builder);
}
}
