public void Write(DdlRules rules, StringWriter writer)
{
if (rules.TableCreation == CreationStyle.DropThenCreate)
{
writer.WriteLine("DROP TABLE IF EXISTS {0} CASCADE;", Table.QualifiedName);
writer.WriteLine("CREATE TABLE {0} (", Table.QualifiedName);
}
else
{
writer.WriteLine("CREATE TABLE IF NOT EXISTS {0} (", Table.QualifiedName);
}
var length = Columns.Select(x => x.Name.Length).Max() + 4;
Columns.Each(col =>
{
writer.Write($" {col.ToDeclaration(length)}");
if (col == Columns.Last())
{
writer.WriteLine();
}
else
{
writer.WriteLine(",");
}
});
writer.WriteLine(");");
rules.GrantToRoles.Each(role =>
{
writer.WriteLine($"GRANT SELECT ({Columns.Select(x => x.Name).Join(", ")}) ON TABLE {Table.QualifiedName} TO \"{role}\";");
});
}
public void Write(DdlRules rules, StringWriter writer)
{
if (rules.TableCreation == CreationStyle.DropThenCreate)
{
writer.WriteLine("DROP TABLE IF EXISTS {0} CASCADE;", Table.QualifiedName);
writer.WriteLine("CREATE TABLE {0} (", Table.QualifiedName);
}
else
{
writer.WriteLine("CREATE TABLE IF NOT EXISTS {0} (", Table.QualifiedName);
}
var length = Columns.Select(x => x.Name.Length).Max() + 4;
Columns.Each(col =>
{
writer.Write($" {col.ToDeclaration(length)}");
if (col == Columns.Last())
{
writer.WriteLine();
}
else
{
writer.WriteLine(",");
}
});
writer.WriteLine(");");
writer.WriteLine(this.OriginStatement());
}
public string ToDDL(DdlRules rules)
{
var writer = new StringWriter();
Write(rules, writer);
return writer.ToString();
}
public void write_ddl_in_create_if_not_exists_mode()
{
var users = DocumentMapping.For <User>();
var table = users.SchemaObjects.StorageTable();
var rules = new DdlRules
{
TableCreation = CreationStyle.CreateIfNotExists
};
var ddl = table.ToDDL(rules);
ddl.ShouldNotContain("DROP TABLE IF EXISTS public.mt_doc_user CASCADE;");
ddl.ShouldContain("CREATE TABLE IF NOT EXISTS public.mt_doc_user");
}
public FunctionDelta FetchDelta(NpgsqlConnection conn, DdlRules rules)
{
var cmd = conn.CreateCommand();
var builder = new CommandBuilder(cmd);
ConfigureQueryCommand(builder);
cmd.CommandText = builder.ToString();
using (var reader = cmd.ExecuteReader())
{
return(fetchDelta(reader, rules));
}
}
public SchemaDiff(SchemaObjects existing, DocumentMapping mapping, DdlRules rules)
{
if (existing.HasNone())
{
AllMissing = true;
}
else
{
var expectedTable = mapping.SchemaObjects.As<DocumentSchemaObjects>().StorageTable();
TableDiff = new TableDiff(expectedTable, existing.Table);
// TODO -- drop obsolete indices?
mapping.Indexes.Each(index =>
{
if (existing.ActualIndices.ContainsKey(index.IndexName))
{
var actualIndex = existing.ActualIndices[index.IndexName];
if (!index.Matches(actualIndex))
{
IndexChanges.Add($"drop index {expectedTable.Table.Schema}.{index.IndexName};{Environment.NewLine}{index.ToDDL()};");
IndexRollbacks.Add($"drop index {expectedTable.Table.Schema}.{index.IndexName};{Environment.NewLine}{actualIndex.DDL};");
}
}
else
{
IndexChanges.Add(index.ToDDL());
IndexRollbacks.Add($"drop index concurrently if exists {expectedTable.Table.Schema}.{index.IndexName};");
}
});
existing.ActualIndices.Values.Where(x => mapping.Indexes.All(_ => _.IndexName != x.Name)).Each(
index =>
{
IndexRollbacks.Add(index.DDL);
IndexChanges.Add($"drop index concurrently if exists {mapping.Table.Schema}.{index.Name};");
});
var expectedFunction = new UpsertFunction(mapping);
FunctionDiff = new FunctionDiff(expectedFunction.ToBody(rules), existing.Function);
var missingFKs = mapping.ForeignKeys.Where(x => !existing.ForeignKeys.Contains(x.KeyName));
MissingForeignKeys.AddRange(missingFKs);
}
_mapping = mapping;
}
public void write_ddl_in_default_drop_then_create_mode()
{
var users = DocumentMapping.For <User>();
var table = new DocumentTable(users);
var rules = new DdlRules
{
TableCreation = CreationStyle.DropThenCreate
};
var ddl = table.ToCreateSql(rules);
ddl.ShouldContain("DROP TABLE IF EXISTS public.mt_doc_user CASCADE;");
ddl.ShouldContain("CREATE TABLE public.mt_doc_user");
}
public void writing_out_updates_when_schema_object_is_invalid()
{
var migration = migrationFor(SchemaPatchDifference.Invalid);
var writer = new StringWriter();
var delta = migration.Deltas.Single();
var rules = new DdlRules();
migration.WriteAllUpdates(writer, rules, AutoCreate.All);
delta.DidNotReceive().WriteUpdate(rules, writer);
delta.SchemaObject.Received().WriteDropStatement(rules, writer);
delta.SchemaObject.Received().WriteCreateStatement(rules, writer);
}
public void can_generate_upsert_function_with_grants()
{
var writer = new StringWriter();
var rules = new DdlRules();
rules.GrantToRoles.Add("foo");
rules.GrantToRoles.Add("bar");
new UpsertFunction(theHierarchy).WriteFunctionSql(rules, writer);
var sql = writer.ToString();
sql.ShouldContain("GRANT EXECUTE ON public.mt_upsert_squad(doc JSONB, docDotNetType varchar, docId varchar, docType varchar, docVersion uuid) TO \"foo\";");
sql.ShouldContain("GRANT EXECUTE ON public.mt_upsert_squad(doc JSONB, docDotNetType varchar, docId varchar, docType varchar, docVersion uuid) TO \"bar\";");
}
public void write_transactional_script_with_a_role()
{
var rules = new DdlRules();
rules.Role = "OCD_DBA";
var writer = new StringWriter();
rules.WriteScript(writer, (r, w) =>
{
w.WriteLine("Hello.");
});
writer.ToString().ShouldContain("SET ROLE OCD_DBA;");
writer.ToString().ShouldContain("RESET ROLE;");
}
protected async Task CreateSchemaObjectInDatabase(ISchemaObject schemaObject)
{
var rules = new DdlRules();
var writer = new StringWriter();
schemaObject.WriteCreateStatement(rules, writer);
try
{
await theConnection.CreateCommand(writer.ToString())
.ExecuteNonQueryAsync();
}
catch (Exception e)
{
throw new Exception("DDL Execution Failure.\n" + writer.ToString(), e);
}
}
public void write_transactional_script_with_a_role()
{
var rules = new DdlRules();
rules.Role = "OCD_DBA";
var patch = new SchemaPatch(rules);
var writer = new StringWriter();
patch.WriteTransactionalScript(writer, w =>
{
w.WriteLine("Hello.");
});
writer.ToString().ShouldContain("SET ROLE OCD_DBA;");
writer.ToString().ShouldContain("RESET ROLE;");
}
public void WriteFunctionSql(DdlRules rules, StringWriter writer)
{
var ordered = OrderedArguments();
var argList = ordered.Select(x => x.ArgumentDeclaration()).Join(", ");
var systemUpdates = new string[] {$"{DocumentMapping.LastModifiedColumn} = transaction_timestamp()" };
var updates = ordered.Where(x => x.Column != "id" && x.Column.IsNotEmpty())
.Select(x => $"\"{x.Column}\" = {x.Arg}").Concat(systemUpdates).Join(", ");
var inserts = ordered.Where(x => x.Column.IsNotEmpty()).Select(x => $"\"{x.Column}\"").Concat(new [] {DocumentMapping.LastModifiedColumn}).Join(", ");
var valueList = ordered.Where(x => x.Column.IsNotEmpty()).Select(x => x.Arg).Concat(new [] { "transaction_timestamp()" }).Join(", ");
if (Arguments.Any(x => x is CurrentVersionArgument))
{
updates += $" where {_tableName.QualifiedName}.{DocumentMapping.VersionColumn} = current_version or current_version is null";
}
var securityDeclaration = rules.UpsertRights == SecurityRights.Invoker
? "SECURITY INVOKER"
: "SECURITY DEFINER";
writer.WriteLine($@"
CREATE OR REPLACE FUNCTION {_functionName.QualifiedName}({argList}) RETURNS UUID LANGUAGE plpgsql {securityDeclaration} AS $function$
DECLARE
final_version uuid;
BEGIN
INSERT INTO {_tableName.QualifiedName} ({inserts}) VALUES ({valueList})
ON CONFLICT ON CONSTRAINT {_primaryKeyConstraintName}
DO UPDATE SET {updates};
SELECT mt_version FROM {_tableName.QualifiedName} into final_version WHERE id = docId;
RETURN final_version;
END;
$function$;
");
rules.GrantToRoles.Each(role =>
{
writer.WriteLine($"GRANT EXECUTE ON {_functionName.QualifiedName}({argList}) TO \"{role}\";");
});
}
public void restore_previous_function_in_rollback()
{
writeFunction();
theMapping.Duplicate(x => x.FirstName);
writeTable();
var function = new UpsertFunction(theMapping);
var ddlRules = new DdlRules();
var delta = function.FetchDelta(_conn, ddlRules);
var patch = new SchemaPatch(ddlRules);
delta.WritePatch(patch);
patch.RollbackDDL.ShouldContain(delta.Actual.Body);
}
public void write_transactional_script_with_no_role()
{
var rules = new DdlRules();
rules.Role.ShouldBeNull();
var patch = new SchemaPatch(rules);
var writer = new StringWriter();
patch.WriteScript(writer, w =>
{
w.WriteLine("Hello.");
});
writer.ToString().ShouldNotContain("SET ROLE");
writer.ToString().ShouldNotContain("RESET ROLE;");
}
public static void RemoveAllObjects(this IFeatureSchema schema, DdlRules rules, NpgsqlConnection conn)
{
var writer = new StringWriter();
schema.WriteDropStatements(rules, writer);
var sql = writer.ToString();
var cmd = conn.CreateCommand(sql);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw MartenCommandExceptionFactory.Create(cmd, e);
}
}
public void write_transactional_script_with_a_role()
{
var rules = new DdlRules();
rules.Role = "OCD_DBA";
var patch = new SchemaPatch(rules);
var writer = new StringWriter();
patch.WriteScript(writer, w =>
{
w.WriteLine("Hello.");
});
SpecificationExtensions.ShouldContain(writer.ToString(), "SET ROLE OCD_DBA;");
SpecificationExtensions.ShouldContain(writer.ToString(), "RESET ROLE;");
}
public override void Write(DdlRules rules, StringWriter writer)
{
var ordered = OrderedArguments();
var argList = ordered.Select(x => x.ArgumentDeclaration()).Join(", ");
var systemUpdates = new string[] { $"{DocumentMapping.LastModifiedColumn} = transaction_timestamp()" };
var updates = ordered.Where(x => x.Column != "id" && x.Column.IsNotEmpty())
.Select(x => $"\"{x.Column}\" = {x.Arg}").Concat(systemUpdates).Join(", ");
var inserts = ordered.Where(x => x.Column.IsNotEmpty()).Select(x => $"\"{x.Column}\"").Concat(new [] { DocumentMapping.LastModifiedColumn }).Join(", ");
var valueList = ordered.Where(x => x.Column.IsNotEmpty()).Select(x => x.Arg).Concat(new [] { "transaction_timestamp()" }).Join(", ");
if (Arguments.Any(x => x is CurrentVersionArgument))
{
updates += $" where {_tableName.QualifiedName}.{DocumentMapping.VersionColumn} = current_version";
}
var securityDeclaration = rules.UpsertRights == SecurityRights.Invoker
? "SECURITY INVOKER"
: "SECURITY DEFINER";
writer.WriteLine($@"
CREATE OR REPLACE FUNCTION {Identifier.QualifiedName}({argList}) RETURNS UUID LANGUAGE plpgsql {securityDeclaration} AS $function$
DECLARE
final_version uuid;
BEGIN
INSERT INTO {_tableName.QualifiedName} ({inserts}) VALUES ({valueList})
ON CONFLICT ON CONSTRAINT {_primaryKeyConstraintName}
DO UPDATE SET {updates};
SELECT mt_version FROM {_tableName.QualifiedName} into final_version WHERE id = docId;
RETURN final_version;
END;
$function$;
");
}
public override void Write(DdlRules rules, StringWriter writer)
{
var ordered = OrderedArguments();
var argList = ordered.Select(x => x.ArgumentDeclaration()).Join(", ");
var systemUpdates = new string[] { $"{DocumentMapping.LastModifiedColumn} = transaction_timestamp()" };
var updates = ordered.Where(x => x.Column != "id" && x.Column.IsNotEmpty())
.Select(x => $"\"{x.Column}\" = {x.Arg}").Concat(systemUpdates).Join(", ");
var inserts = ordered.Where(x => x.Column.IsNotEmpty()).Select(x => $"\"{x.Column}\"").Concat(new [] { DocumentMapping.LastModifiedColumn }).Join(", ");
var valueList = ordered.Where(x => x.Column.IsNotEmpty()).Select(x => x.Arg).Concat(new [] { "transaction_timestamp()" }).Join(", ");
var whereClauses = new List <string>();
if (Arguments.Any(x => x is CurrentVersionArgument) && !_disableConcurrency)
{
whereClauses.Add($"{_tableName.QualifiedName}.{DocumentMapping.VersionColumn} = current_version");
}
if (Arguments.Any(x => x is TenantIdArgument))
{
whereClauses.Add($"{_tableName.QualifiedName}.{TenantIdColumn.Name} = {TenantIdArgument.ArgName}");
}
if (whereClauses.Any())
{
updates += " where " + whereClauses.Join(" and ");
}
var securityDeclaration = rules.UpsertRights == SecurityRights.Invoker
? "SECURITY INVOKER"
: "SECURITY DEFINER";
writeFunction(writer, argList, securityDeclaration, inserts, valueList, updates);
}
public override void Write(DdlRules rules, StringWriter writer)
{
var streamIdType = _events.GetStreamIdDBType();
var databaseSchema = _events.DatabaseSchemaName;
var tenancyStyle = _events.TenancyStyle;
var streamsWhere = "id = stream";
if (tenancyStyle == TenancyStyle.Conjoined)
{
streamsWhere += " AND tenant_id = tenantid";
}
writer.WriteLine($@"
CREATE OR REPLACE FUNCTION {Identifier}(stream {streamIdType}, stream_type varchar, tenantid varchar, event_ids uuid[], event_types varchar[], dotnet_types varchar[], bodies jsonb[]) RETURNS int[] AS $$
DECLARE
event_version int;
event_type varchar;
event_id uuid;
body jsonb;
index int;
seq int;
actual_tenant varchar;
return_value int[];
BEGIN
select version into event_version from {databaseSchema}.mt_streams where {streamsWhere}{(_events.UseAppendEventForUpdateLock ? " for update" : string.Empty)};
if event_version IS NULL then
event_version = 0;
insert into {databaseSchema}.mt_streams (id, type, version, timestamp, tenant_id) values (stream, stream_type, 0, now(), tenantid);
else
if tenantid IS NOT NULL then
select tenant_id into actual_tenant from {databaseSchema}.mt_streams where {streamsWhere};
if actual_tenant != tenantid then
RAISE EXCEPTION 'Marten: The tenantid does not match the existing stream';
end if;
end if;
end if;
index := 1;
return_value := ARRAY[event_version + array_length(event_ids, 1)];
foreach event_id in ARRAY event_ids
loop
seq := nextval('{databaseSchema}.mt_events_sequence');
return_value := array_append(return_value, seq);
event_version := event_version + 1;
event_type = event_types[index];
body = bodies[index];
insert into {databaseSchema}.mt_events
(seq_id, id, stream_id, version, data, type, tenant_id, {SchemaConstants.DotNetTypeColumn})
values
(seq, event_id, stream, event_version, body, event_type, tenantid, dotnet_types[index]);
index := index + 1;
end loop;
update {databaseSchema}.mt_streams set version = event_version, timestamp = now() where {streamsWhere};
return return_value;
END
$$ LANGUAGE plpgsql;
");
}
void IFeatureSchema.WritePermissions(DdlRules rules, TextWriter writer)
{
// Nothing
}
public virtual void WriteCreateStatement(DdlRules rules, TextWriter writer)
{
writer.WriteLine(_body);
}
public void WriteTemplate(DdlRules rules, DdlTemplate template, StringWriter writer)
{
var body = ToBody(rules);
body.WriteTemplate(template, writer);
}
public override void WriteUpdate(DdlRules rules, TextWriter writer)
{
if (Difference == SchemaPatchDifference.Invalid)
{
throw new InvalidOperationException($"TableDelta for {Expected.Identifier} is invalid");
}
if (Difference == SchemaPatchDifference.Create)
{
SchemaObject.WriteCreateStatement(rules, writer);
return;
}
// Extra indexes
foreach (var extra in Indexes.Extras)
{
writer.WriteDropIndex(Expected, extra);
}
// Different indexes
foreach (var change in Indexes.Different)
{
writer.WriteDropIndex(Expected, change.Actual);
}
// Missing columns
foreach (var column in Columns.Missing)
{
writer.WriteLine(column.AddColumnSql(Expected));
}
// Different columns
foreach (var change1 in Columns.Different)
{
writer.WriteLine(change1.Expected.AlterColumnTypeSql(Expected, change1.Actual));
}
writeForeignKeyUpdates(writer);
// Missing indexes
foreach (var indexDefinition in Indexes.Missing)
{
writer.WriteLine(indexDefinition.ToDDL(Expected));
}
// Different indexes
foreach (var change in Indexes.Different)
{
writer.WriteLine(change.Expected.ToDDL(Expected));
}
// Extra columns
foreach (var column in Columns.Extras)
{
writer.WriteLine(column.DropColumnSql(Expected));
}
switch (PrimaryKeyDifference)
{
case SchemaPatchDifference.Invalid:
case SchemaPatchDifference.Update:
writer.WriteLine($"alter table {Expected.Identifier} drop constraint {Actual.PrimaryKeyName};");
writer.WriteLine($"alter table {Expected.Identifier} add {Expected.PrimaryKeyDeclaration()};");
break;
case SchemaPatchDifference.Create:
writer.WriteLine($"alter table {Expected.Identifier} add {Expected.PrimaryKeyDeclaration()};");
break;
}
}
public SchemaPatch(DdlRules rules)
{
Rules = rules;
}
public override void WriteCreateStatement(DdlRules rules, TextWriter writer)
{
writer.WriteLine(GenerateFunction());
writer.WriteLine();
}
public SchemaPatch(DdlRules rules, StringWriter upWriter) : this(rules, new DDLRecorder(upWriter))
{
}
public SchemaPatch(DdlRules rules, IDDLRunner liveRunner) : this(rules)
{
_liveRunner = liveRunner;
}
public void write_ddl_in_create_if_not_exists_mode()
{
var users = DocumentMapping.For<User>();
var table = users.SchemaObjects.StorageTable();
var rules = new DdlRules
{
TableCreation = CreationStyle.CreateIfNotExists
};
var ddl = table.ToDDL(rules);
ddl.ShouldNotContain("DROP TABLE IF EXISTS public.mt_doc_user CASCADE;");
ddl.ShouldContain("CREATE TABLE IF NOT EXISTS public.mt_doc_user");
}
public void WritePermissions(DdlRules rules, TextWriter writer)
{
// No permissions!
}
public void WriteDropStatement(DdlRules rules, TextWriter writer)
{
var dropSql = toDropSql();
writer.WriteLine(dropSql);
}
public void WriteDropStatement(DdlRules rules, StringWriter writer)
{
writer.WriteLine($"DROP SEQUENCE IF EXISTS {Identifier};");
}
public override void WriteRollback(DdlRules rules, TextWriter writer)
{
if (Actual == null)
{
Expected.WriteDropStatement(rules, writer);
return;
}
foreach (var foreignKey in ForeignKeys.Missing)
{
foreignKey.WriteDropStatement(Expected, writer);
}
foreach (var change in ForeignKeys.Different)
{
change.Expected.WriteDropStatement(Expected, writer);
}
// Extra columns
foreach (var column in Columns.Extras)
{
writer.WriteLine(column.AddColumnSql(Expected));
}
// Different columns
foreach (var change1 in Columns.Different)
{
writer.WriteLine(change1.Actual.AlterColumnTypeSql(Actual, change1.Expected));
}
foreach (var change in ForeignKeys.Different)
{
change.Actual.WriteAddStatement(Expected, writer);
}
rollbackIndexes(writer);
// Missing columns
foreach (var column in Columns.Missing)
{
writer.WriteLine(column.DropColumnSql(Expected));
}
foreach (var foreignKey in ForeignKeys.Extras)
{
foreignKey.WriteAddStatement(Expected, writer);
}
switch (PrimaryKeyDifference)
{
case SchemaPatchDifference.Invalid:
case SchemaPatchDifference.Update:
writer.WriteLine($"alter table {Expected.Identifier} drop constraint if exists {Expected.PrimaryKeyName};");
writer.WriteLine($"alter table {Expected.Identifier} add {Actual.PrimaryKeyDeclaration()};");
break;
case SchemaPatchDifference.Create:
writer.WriteLine($"alter table {Expected.Identifier} drop constraint if exists {Expected.PrimaryKeyName};");
break;
}
}
public FunctionBody ToBody(DdlRules rules)
{
var argList = OrderedArguments().Select(x => x.PostgresType).Join(", ");
var dropSql = $"drop function if exists {_functionName.QualifiedName}({argList});";
var writer = new StringWriter();
WriteFunctionSql(rules, writer);
return new FunctionBody(_functionName, new string[] {dropSql}, writer.ToString());
}
public void write_ddl_in_default_drop_then_create_mode()
{
var users = DocumentMapping.For<User>();
var table = users.SchemaObjects.StorageTable();
var rules = new DdlRules
{
TableCreation = CreationStyle.DropThenCreate
};
var ddl = table.ToDDL(rules);
ddl.ShouldContain("DROP TABLE IF EXISTS public.mt_doc_user CASCADE;");
ddl.ShouldContain("CREATE TABLE public.mt_doc_user");
}
public override void Write(DdlRules rules, StringWriter writer)
{
writer.WriteLine(GenerateFunction());
writer.WriteLine();
}