public PostgreSqlQueryExecutor(PostgreSqlQueryConfiguration configuration, Akka.Serialization.Serialization serialization) : base(configuration, serialization)
{
CreateSnapshotTableSql = $@"
DO
$do$
BEGIN
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{Configuration.SchemaName}' AND TABLE_NAME = '{Configuration.SnapshotTableName}') THEN
CREATE TABLE {Configuration.FullSnapshotTableName} (
{Configuration.PersistenceIdColumnName} VARCHAR(255) NOT NULL,
{Configuration.SequenceNrColumnName} BIGINT NOT NULL,
{Configuration.TimestampColumnName} BIGINT NOT NULL,
{Configuration.ManifestColumnName} VARCHAR(500) NOT NULL,
{Configuration.PayloadColumnName} {configuration.StoredAs.ToString().ToUpperInvariant()} NOT NULL,
{Configuration.SerializerIdColumnName} INTEGER NULL,
CONSTRAINT {Configuration.SnapshotTableName}_pk PRIMARY KEY ({Configuration.PersistenceIdColumnName}, {Configuration.SequenceNrColumnName})
);
CREATE INDEX {Configuration.SnapshotTableName}_{Configuration.SequenceNrColumnName}_idx ON {Configuration.FullSnapshotTableName}({Configuration.SequenceNrColumnName});
CREATE INDEX {Configuration.SnapshotTableName}_{Configuration.TimestampColumnName}_idx ON {Configuration.FullSnapshotTableName}({Configuration.TimestampColumnName});
END IF;
END
$do$";
InsertSnapshotSql = $@"
WITH upsert AS (
UPDATE {Configuration.FullSnapshotTableName}
SET
{Configuration.TimestampColumnName} = @Timestamp,
{Configuration.PayloadColumnName} = @Payload
WHERE {Configuration.PersistenceIdColumnName} = @PersistenceId
AND {Configuration.SequenceNrColumnName} = @SequenceNr
RETURNING *)
INSERT INTO {Configuration.FullSnapshotTableName} (
{Configuration.PersistenceIdColumnName},
{Configuration.SequenceNrColumnName},
{Configuration.TimestampColumnName},
{Configuration.ManifestColumnName},
{Configuration.PayloadColumnName},
{Configuration.SerializerIdColumnName})
SELECT @PersistenceId, @SequenceNr, @Timestamp, @Manifest, @Payload, @SerializerId
WHERE NOT EXISTS (SELECT * FROM upsert)";
switch (configuration.StoredAs)
{
case StoredAsType.ByteA:
_serialize = ss =>
{
var serializer = Serialization.FindSerializerFor(ss);
return(new SerializationResult(NpgsqlDbType.Bytea, serializer.ToBinary(ss), serializer));
};
_deserialize = (type, serialized, manifest, serializerId) =>
{
if (serializerId.HasValue)
{
return(Serialization.Deserialize((byte[])serialized, serializerId.Value, manifest));
}
else
{
// Support old writes that did not set the serializer id
var deserializer = Serialization.FindSerializerForType(type, Configuration.DefaultSerializer);
return(deserializer.FromBinary((byte[])serialized, type));
}
};
break;
case StoredAsType.JsonB:
_serialize = ss => new SerializationResult(NpgsqlDbType.Jsonb, JsonConvert.SerializeObject(ss, configuration.JsonSerializerSettings), null);
_deserialize = (type, serialized, manifest, serializerId) => JsonConvert.DeserializeObject((string)serialized, type, configuration.JsonSerializerSettings);
break;
case StoredAsType.Json:
_serialize = ss => new SerializationResult(NpgsqlDbType.Json, JsonConvert.SerializeObject(ss, configuration.JsonSerializerSettings), null);
_deserialize = (type, serialized, manifest, serializerId) => JsonConvert.DeserializeObject((string)serialized, type, configuration.JsonSerializerSettings);
break;
default:
throw new NotSupportedException($"{configuration.StoredAs} is not supported Db type for a payload");
}
}