public OracleQueryExecutor(QueryConfiguration configuration, Akka.Serialization.Serialization serialization)
: base(configuration, serialization)
{
SelectSnapshotSql = $@"
SELECT *
FROM (
SELECT {Configuration.PersistenceIdColumnName},
{Configuration.SequenceNrColumnName},
{Configuration.TimestampColumnName},
{Configuration.ManifestColumnName},
{Configuration.PayloadColumnName},
{Configuration.SerializerIdColumnName}
FROM {Configuration.FullSnapshotTableName}
WHERE {Configuration.PersistenceIdColumnName} = :PersistenceId AND {Configuration.SequenceNrColumnName} <= :SequenceNr AND {Configuration.TimestampColumnName} <= :Timestamp
ORDER BY {Configuration.SequenceNrColumnName} DESC
)
WHERE ROWNUM = 1";
DeleteSnapshotSql = $@"
DELETE FROM {Configuration.FullSnapshotTableName}
WHERE {Configuration.PersistenceIdColumnName} = :PersistenceId AND {Configuration.SequenceNrColumnName} = :SequenceNr";
DeleteSnapshotRangeSql = $@"
DELETE FROM {Configuration.FullSnapshotTableName}
WHERE {Configuration.PersistenceIdColumnName} = :PersistenceId AND {Configuration.SequenceNrColumnName} <= :SequenceNr AND {Configuration.TimestampColumnName} <= :Timestamp";
InsertSnapshotSql = $@"
DECLARE
value_count integer;
BEGIN
SELECT COUNT(1) INTO value_count
FROM {configuration.FullSnapshotTableName}
WHERE {configuration.PersistenceIdColumnName} = :PersistenceId AND {configuration.SequenceNrColumnName} = :SequenceNr;
IF (value_count > 0) THEN
UPDATE {configuration.FullSnapshotTableName}
SET {configuration.TimestampColumnName} = :Timestamp, {configuration.ManifestColumnName} = :Manifest, {configuration.PayloadColumnName} = :Payload, {configuration.SerializerIdColumnName} = :SerializerId
WHERE {configuration.PersistenceIdColumnName} = :PersistenceId AND {configuration.SequenceNrColumnName} = :SequenceNr;
ELSE
INSERT INTO {Configuration.FullSnapshotTableName} ({configuration.PersistenceIdColumnName}, {configuration.SequenceNrColumnName}, {configuration.TimestampColumnName}, {configuration.ManifestColumnName}, {configuration.PayloadColumnName}, {configuration.SerializerIdColumnName})
VALUES (:PersistenceId, :SequenceNr, :Timestamp, :Manifest, :Payload, :SerializerId);
END IF;
END;";
CreateSnapshotTableSql = $@"
DECLARE
table_count integer;
BEGIN
SELECT COUNT (OBJECT_ID) INTO table_count
FROM USER_OBJECTS
WHERE EXISTS (SELECT OBJECT_NAME FROM USER_OBJECTS WHERE (OBJECT_NAME = UPPER('{configuration.SnapshotTableName}') AND OBJECT_TYPE = 'TABLE'));
IF table_count = 0 THEN
EXECUTE IMMEDIATE(
'CREATE TABLE {configuration.FullSnapshotTableName} (
{configuration.PersistenceIdColumnName} NVARCHAR2(255) NOT NULL,
{configuration.SequenceNrColumnName} NUMBER(19,0) NOT NULL,
{configuration.TimestampColumnName} TIMESTAMP(7) NOT NULL,
{configuration.ManifestColumnName} NVARCHAR2(500) NOT NULL,
{configuration.PayloadColumnName} BLOB NOT NULL,
{configuration.SerializerIdColumnName} NUMBER(10,0) NULL,
CONSTRAINT PK_{configuration.SnapshotTableName} PRIMARY KEY ({configuration.PersistenceIdColumnName}, {configuration.SequenceNrColumnName})
)'
);
EXECUTE IMMEDIATE ('CREATE INDEX IX_{configuration.SnapshotTableName}_{configuration.SequenceNrColumnName} ON {configuration.FullSnapshotTableName}({configuration.SequenceNrColumnName})');
EXECUTE IMMEDIATE ('CREATE INDEX IX_{configuration.SnapshotTableName}_{configuration.TimestampColumnName} ON {configuration.FullSnapshotTableName}({configuration.TimestampColumnName})');
END IF;
END;";
}
public ByteArrayJournalSerializer(IProviderConfig <JournalTableConfig> journalConfig, Akka.Serialization.Serialization serializer, string separator)
{
_journalConfig = journalConfig;
_serializer = serializer;
_separator = separator;
_separatorArray = new[] { _separator };
}
/// <summary>
/// TBD
/// </summary>
/// <param name="configuration">TBD</param>
/// <param name="serialization">TBD</param>
/// <param name="timestampProvider">TBD</param>
protected AbstractQueryExecutor(QueryConfiguration configuration, Akka.Serialization.Serialization serialization, ITimestampProvider timestampProvider)
{
TimestampProvider = timestampProvider;
Serialization = serialization;
Configuration = configuration;
var allEventColumnNames = $@"
e.{Configuration.PersistenceIdColumnName} as PersistenceId,
e.{Configuration.SequenceNrColumnName} as SequenceNr,
e.{Configuration.TimestampColumnName} as Timestamp,
e.{Configuration.IsDeletedColumnName} as IsDeleted,
e.{Configuration.ManifestColumnName} as Manifest,
e.{Configuration.PayloadColumnName} as Payload,
e.{Configuration.SerializerIdColumnName} as SerializerId";
AllPersistenceIdsSql = $@"
SELECT DISTINCT u.Id as PersistenceId
FROM (
SELECT DISTINCT e.{Configuration.PersistenceIdColumnName} as Id
FROM {Configuration.FullJournalTableName} e
WHERE e.{Configuration.OrderingColumnName} > @Ordering
UNION
SELECT DISTINCT e.{Configuration.PersistenceIdColumnName} as Id
FROM {Configuration.FullMetaTableName} e
) as u";
HighestSequenceNrSql = $@"
SELECT MAX(u.SeqNr) as SequenceNr
FROM (
SELECT e.{Configuration.SequenceNrColumnName} as SeqNr FROM {Configuration.FullJournalTableName} e WHERE e.{Configuration.PersistenceIdColumnName} = @PersistenceId
UNION
SELECT m.{Configuration.SequenceNrColumnName} as SeqNr FROM {Configuration.FullMetaTableName} m WHERE m.{Configuration.PersistenceIdColumnName} = @PersistenceId) as u";
DeleteBatchSql = $@"
DELETE FROM {Configuration.FullJournalTableName}
WHERE {Configuration.PersistenceIdColumnName} = @PersistenceId AND {Configuration.SequenceNrColumnName} <= @ToSequenceNr;
DELETE FROM {Configuration.FullMetaTableName}
WHERE {Configuration.PersistenceIdColumnName} = @PersistenceId AND {Configuration.SequenceNrColumnName} <= @ToSequenceNr;";
UpdateSequenceNrSql = $@"
INSERT INTO {Configuration.FullMetaTableName} ({Configuration.PersistenceIdColumnName}, {Configuration.SequenceNrColumnName})
VALUES (@PersistenceId, @SequenceNr);";
ByPersistenceIdSql =
$@"
SELECT {allEventColumnNames}
FROM {Configuration.FullJournalTableName} e
WHERE e.{Configuration.PersistenceIdColumnName} = @PersistenceId
AND e.{Configuration.SequenceNrColumnName} BETWEEN @FromSequenceNr AND @ToSequenceNr
ORDER BY {Configuration.SequenceNrColumnName} ASC;";
HighestTagOrderingSql =
$@"
SELECT MAX(e.{Configuration.OrderingColumnName}) as Ordering
FROM {Configuration.FullJournalTableName} e
WHERE e.{Configuration.OrderingColumnName} > @Ordering AND e.{Configuration.TagsColumnName} LIKE @Tag";
ByTagSql =
$@"
SELECT {allEventColumnNames}, e.{Configuration.OrderingColumnName} as Ordering
FROM {Configuration.FullJournalTableName} e
WHERE e.{Configuration.OrderingColumnName} > @Ordering AND e.{Configuration.TagsColumnName} LIKE @Tag
ORDER BY {Configuration.OrderingColumnName} ASC";
AllEventsSql =
$@"
SELECT {allEventColumnNames}, e.{Configuration.OrderingColumnName} as Ordering
FROM {Configuration.FullJournalTableName} e
WHERE e.{Configuration.OrderingColumnName} > @Ordering
ORDER BY {Configuration.OrderingColumnName} ASC";
HighestOrderingSql =
$@"
SELECT MAX(e.{Configuration.OrderingColumnName}) as Ordering
FROM {Configuration.FullJournalTableName} e";
InsertEventSql = $@"
INSERT INTO {Configuration.FullJournalTableName} (
{Configuration.PersistenceIdColumnName},
{Configuration.SequenceNrColumnName},
{Configuration.TimestampColumnName},
{Configuration.IsDeletedColumnName},
{Configuration.ManifestColumnName},
{Configuration.PayloadColumnName},
{Configuration.TagsColumnName},
{Configuration.SerializerIdColumnName}
) VALUES (
@PersistenceId,
@SequenceNr,
@Timestamp,
@IsDeleted,
@Manifest,
@Payload,
@Tag,
@SerializerId
)";
QueryEventsSql = $@"
SELECT {allEventColumnNames}
FROM {Configuration.FullJournalTableName} e
WHERE ";
}
public MongoDbSnapshotStore()
{
_settings = MongoDbPersistence.Get(Context.System).SnapshotStoreSettings;
_serialization = Context.System.Serialization;
}
public Internals(RemoteTransport transport, Akka.Serialization.Serialization serialization, IInternalActorRef remoteDaemon)
{
Transport = transport;
Serialization = serialization;
RemoteDaemon = remoteDaemon;
}
public PostgreSqlQueryExecutor(PostgreSqlQueryConfiguration configuration, Akka.Serialization.Serialization serialization, ITimestampProvider timestampProvider)
: base(configuration, serialization, timestampProvider)
{
var storedAs = configuration.StoredAs.ToString().ToUpperInvariant();
CreateEventsJournalSql = $@"
CREATE TABLE IF NOT EXISTS {Configuration.FullJournalTableName} (
{Configuration.OrderingColumnName} {(configuration.UseBigIntPrimaryKey ? "BIGINT GENERATED ALWAYS AS IDENTITY" : "BIGSERIAL")} NOT NULL PRIMARY KEY,
{Configuration.PersistenceIdColumnName} VARCHAR(255) NOT NULL,
{Configuration.SequenceNrColumnName} BIGINT NOT NULL,
{Configuration.IsDeletedColumnName} BOOLEAN NOT NULL,
{Configuration.TimestampColumnName} BIGINT NOT NULL,
{Configuration.ManifestColumnName} VARCHAR(500) NOT NULL,
{Configuration.PayloadColumnName} {storedAs} NOT NULL,
{Configuration.TagsColumnName} VARCHAR(100) NULL,
{Configuration.SerializerIdColumnName} INTEGER NULL,
CONSTRAINT {Configuration.JournalEventsTableName}_uq UNIQUE ({Configuration.PersistenceIdColumnName}, {Configuration.SequenceNrColumnName})
);";
CreateMetaTableSql = $@"
CREATE TABLE IF NOT EXISTS {Configuration.FullMetaTableName} (
{Configuration.PersistenceIdColumnName} VARCHAR(255) NOT NULL,
{Configuration.SequenceNrColumnName} BIGINT NOT NULL,
CONSTRAINT {Configuration.MetaTableName}_pk PRIMARY KEY ({Configuration.PersistenceIdColumnName}, {Configuration.SequenceNrColumnName})
);";
HighestSequenceNrSql = $@"
SELECT MAX(u.SeqNr) as SequenceNr
FROM (
SELECT MAX(e.{Configuration.SequenceNrColumnName}) as SeqNr FROM {Configuration.FullJournalTableName} e WHERE e.{Configuration.PersistenceIdColumnName} = @PersistenceId
UNION
SELECT MAX(m.{Configuration.SequenceNrColumnName}) as SeqNr FROM {Configuration.FullMetaTableName} m WHERE m.{Configuration.PersistenceIdColumnName} = @PersistenceId) as u";
// As per https://github.com/akkadotnet/Akka.Persistence.PostgreSql/pull/72, apparently PostgreSQL does not like
// it when you chain two deletes in a single command, so we have to split it into two.
// The performance penalty should be minimal, depending on the network speed
DeleteBatchSql = $@"
DELETE FROM {Configuration.FullJournalTableName}
WHERE {Configuration.PersistenceIdColumnName} = @PersistenceId AND {Configuration.SequenceNrColumnName} <= @ToSequenceNr;";
DeleteBatchSqlMetadata = $@"DELETE FROM {Configuration.FullMetaTableName}
WHERE {Configuration.PersistenceIdColumnName} = @PersistenceId AND {Configuration.SequenceNrColumnName} <= @ToSequenceNr;";
switch (configuration.StoredAs)
{
case StoredAsType.ByteA:
_serialize = e =>
{
var payloadType = e.Payload.GetType();
var serializer = Serialization.FindSerializerForType(payloadType, Configuration.DefaultSerializer);
// TODO: hack. Replace when https://github.com/akkadotnet/akka.net/issues/3811
var binary = Akka.Serialization.Serialization.WithTransport(Serialization.System, () => serializer.ToBinary(e.Payload));
return(new SerializationResult(NpgsqlDbType.Bytea, binary, serializer));
};
_deserialize = (type, payload, manifest, serializerId) =>
{
if (serializerId.HasValue)
{
// TODO: hack. Replace when https://github.com/akkadotnet/akka.net/issues/3811
return(Serialization.Deserialize((byte[])payload, serializerId.Value, manifest));
}
else
{
// Support old writes that did not set the serializer id
var deserializer = Serialization.FindSerializerForType(type, Configuration.DefaultSerializer);
// TODO: hack. Replace when https://github.com/akkadotnet/akka.net/issues/3811
return(Akka.Serialization.Serialization.WithTransport(Serialization.System, () => deserializer.FromBinary((byte[])payload, type)));
}
};
break;
case StoredAsType.JsonB:
_serialize = e => new SerializationResult(NpgsqlDbType.Jsonb, JsonConvert.SerializeObject(e.Payload, configuration.JsonSerializerSettings), null);
_deserialize = (type, serialized, manifest, serializerId) => JsonConvert.DeserializeObject((string)serialized, type, configuration.JsonSerializerSettings);
break;
case StoredAsType.Json:
_serialize = e => new SerializationResult(NpgsqlDbType.Json, JsonConvert.SerializeObject(e.Payload, 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");
}
}
private byte[] IActorRefSerializer(IActorRef actorRef)
{
var str = Serialization.SerializedActorPath(actorRef);
return(ObjectSerializer(str));
}
public BatchingOracleJournal(BatchingSqlJournalSetup setup)
: base(setup)
{
serialization = Context.System.Serialization;
var conventions = Setup.NamingConventions;
var allEventColumnNames = $@"
e.{conventions.PersistenceIdColumnName} AS PersistenceId,
e.{conventions.SequenceNrColumnName} AS SequenceNr,
e.{conventions.TimestampColumnName} AS Timestamp,
e.{conventions.IsDeletedColumnName} AS IsDeleted,
e.{conventions.ManifestColumnName} AS Manifest,
e.{conventions.PayloadColumnName} AS Payload,
e.{conventions.SerializerIdColumnName} AS SerializerId";
AllPersistenceIdsSql = $@"
SELECT DISTINCT e.{conventions.PersistenceIdColumnName} AS PersistenceId
FROM {conventions.FullJournalTableName} e";
HighestSequenceNrSql = $@"
SELECT MAX(u.SeqNr) AS SequenceNr
FROM (
SELECT e.{conventions.SequenceNrColumnName} AS SeqNr FROM {conventions.FullJournalTableName} e WHERE e.{conventions.PersistenceIdColumnName} = :PersistenceId
UNION
SELECT m.{conventions.SequenceNrColumnName} AS SeqNr FROM {conventions.FullMetaTableName} m WHERE m.{conventions.PersistenceIdColumnName} = :PersistenceId
) u
ORDER BY SequenceNr DESC";
DeleteBatchSql = $@"
DELETE FROM {conventions.FullJournalTableName}
WHERE {conventions.PersistenceIdColumnName} = :PersistenceId AND {conventions.SequenceNrColumnName} <= :ToSequenceNr";
UpdateSequenceNrSql = $@"
MERGE INTO {conventions.FullMetaTableName} USING DUAL ON ({conventions.PersistenceIdColumnName} = :PersistenceId)
WHEN MATCHED THEN UPDATE SET {conventions.SequenceNrColumnName} = :SequenceNr
WHEN NOT MATCHED THEN INSERT ({conventions.PersistenceIdColumnName}, {conventions.SequenceNrColumnName}) VALUES (:PersistenceId, :SequenceNr)";
ByPersistenceIdSql = $@"
SELECT {allEventColumnNames}
FROM {conventions.FullJournalTableName} e
WHERE e.{conventions.PersistenceIdColumnName} = :PersistenceId AND e.{conventions.SequenceNrColumnName} BETWEEN :FromSequenceNr AND :ToSequenceNr
ORDER BY e.{conventions.SequenceNrColumnName} ASC";
ByTagSql = $@"
SELECT *
FROM (
SELECT {allEventColumnNames}, e.{conventions.OrderingColumnName} AS Ordering
FROM { conventions.FullJournalTableName} e
WHERE e.{conventions.OrderingColumnName} > :Ordering AND e.{conventions.TagsColumnName} LIKE :Tag
ORDER BY {conventions.OrderingColumnName} ASC
)
WHERE ROWNUM <= :Take";
InsertEventSql = $@"
INSERT INTO {conventions.FullJournalTableName} (
{conventions.PersistenceIdColumnName},
{conventions.SequenceNrColumnName},
{conventions.TimestampColumnName},
{conventions.IsDeletedColumnName},
{conventions.ManifestColumnName},
{conventions.PayloadColumnName},
{conventions.TagsColumnName},
{conventions.SerializerIdColumnName}
) VALUES (:PersistenceId, :SequenceNr, :Timestamp, :IsDeleted, :Manifest, :Payload, :Tag, :SerializerId)";
Initializers = ImmutableDictionary.CreateRange(new[]
{
new KeyValuePair <string, string>("CreateJournalSql", $@"
DECLARE
table_count integer;
BEGIN
SELECT COUNT (OBJECT_ID) INTO table_count
FROM USER_OBJECTS
WHERE EXISTS (SELECT OBJECT_NAME FROM USER_OBJECTS WHERE (OBJECT_NAME = UPPER('{conventions.JournalEventsTableName}') AND OBJECT_TYPE = 'TABLE'));
IF table_count = 0 THEN
EXECUTE IMMEDIATE '
CREATE TABLE {conventions.FullJournalTableName} (
{conventions.OrderingColumnName} NUMBER(19,0) NOT NULL,
{conventions.PersistenceIdColumnName} NVARCHAR2(255) NOT NULL,
{conventions.SequenceNrColumnName} NUMBER(19,0) NOT NULL,
{conventions.TimestampColumnName} NUMBER(19,0) NOT NULL,
{conventions.IsDeletedColumnName} NUMBER(1,0) DEFAULT(0) NOT NULL CHECK (IsDeleted IN (0,1)),
{conventions.ManifestColumnName} NVARCHAR2(500) NOT NULL,
{conventions.PayloadColumnName} BLOB NOT NULL,
{conventions.TagsColumnName} NVARCHAR2(100) NULL,
{conventions.SerializerIdColumnName} NUMBER(10,0) NULL,
CONSTRAINT QU_{conventions.JournalEventsTableName} UNIQUE({conventions.PersistenceIdColumnName}, {conventions.SequenceNrColumnName})
)';
EXECUTE IMMEDIATE '
CREATE SEQUENCE {conventions.FullJournalTableName}_SEQ
START WITH 1
INCREMENT BY 1
CACHE 1000
ORDER
NOCYCLE
NOMAXVALUE
';
EXECUTE IMMEDIATE '
CREATE OR REPLACE TRIGGER {conventions.FullJournalTableName}_TRG
BEFORE INSERT ON {conventions.JournalEventsTableName}
FOR EACH ROW
BEGIN
:new.{conventions.OrderingColumnName} := {conventions.JournalEventsTableName}_SEQ.NEXTVAL;
END;
';
EXECUTE IMMEDIATE 'ALTER TRIGGER {conventions.FullJournalTableName}_TRG ENABLE';
EXECUTE IMMEDIATE 'CREATE INDEX IX_{conventions.JournalEventsTableName}_{conventions.SequenceNrColumnName} ON {conventions.FullJournalTableName}({conventions.SequenceNrColumnName})';
EXECUTE IMMEDIATE 'CREATE INDEX IX_{conventions.JournalEventsTableName}_{conventions.TimestampColumnName} ON {conventions.FullJournalTableName}({conventions.TimestampColumnName})';
END IF;
END;"),
new KeyValuePair <string, string>("CreateMetadataSql", $@"
DECLARE
table_count integer;
BEGIN
SELECT COUNT (OBJECT_ID) INTO table_count
FROM USER_OBJECTS
WHERE EXISTS (SELECT OBJECT_NAME FROM USER_OBJECTS WHERE (OBJECT_NAME = UPPER('{conventions.MetaTableName}') AND OBJECT_TYPE = 'TABLE'));
IF table_count = 0 THEN
EXECUTE IMMEDIATE(
'CREATE TABLE {conventions.FullMetaTableName} (
{conventions.PersistenceIdColumnName} NVARCHAR2(255) NOT NULL,
{conventions.SequenceNrColumnName} NUMBER(19,0) NOT NULL,
CONSTRAINT PK_{conventions.MetaTableName} PRIMARY KEY ({conventions.PersistenceIdColumnName}, {conventions.SequenceNrColumnName})
)'
);
END IF;
END;")
});
}
public StreamRefSerializer(ExtendedActorSystem system) : base(system)
{
_system = system;
_serialization = system.Serialization;
}
public OracleQueryExecutor(QueryConfiguration configuration, Akka.Serialization.Serialization serialization, ITimestampProvider timestampProvider)
: base(configuration, serialization, timestampProvider)
{
var allEventColumnNames = $@"
e.{Configuration.PersistenceIdColumnName} AS PersistenceId,
e.{Configuration.SequenceNrColumnName} AS SequenceNr,
e.{Configuration.TimestampColumnName} AS Timestamp,
e.{Configuration.IsDeletedColumnName} AS IsDeleted,
e.{Configuration.ManifestColumnName} AS Manifest,
e.{Configuration.PayloadColumnName} AS Payload,
e.{Configuration.SerializerIdColumnName} AS SerializerId";
AllPersistenceIdsSql = $@"
SELECT DISTINCT e.{Configuration.PersistenceIdColumnName} AS PersistenceId
FROM {Configuration.FullJournalTableName} e";
HighestSequenceNrSql = $@"
SELECT MAX(u.SeqNr) AS SequenceNr
FROM (
SELECT e.{Configuration.SequenceNrColumnName} AS SeqNr FROM {Configuration.FullJournalTableName} e WHERE e.{Configuration.PersistenceIdColumnName} = :PersistenceId
UNION
SELECT m.{Configuration.SequenceNrColumnName} AS SeqNr FROM {Configuration.FullMetaTableName} m WHERE m.{Configuration.PersistenceIdColumnName} = :PersistenceId
) u
ORDER BY SequenceNr DESC";
DeleteBatchSql = $@"
DELETE FROM {Configuration.FullJournalTableName}
WHERE {Configuration.PersistenceIdColumnName} = :PersistenceId AND {Configuration.SequenceNrColumnName} <= :ToSequenceNr";
UpdateSequenceNrSql = $@"
MERGE INTO {Configuration.FullMetaTableName} USING DUAL ON ({Configuration.PersistenceIdColumnName} = :PersistenceId)
WHEN MATCHED THEN UPDATE SET {Configuration.SequenceNrColumnName} = :SequenceNr
WHEN NOT MATCHED THEN INSERT ({Configuration.PersistenceIdColumnName}, {Configuration.SequenceNrColumnName}) VALUES (:PersistenceId, :SequenceNr)";
ByPersistenceIdSql = $@"
SELECT {allEventColumnNames}
FROM {Configuration.FullJournalTableName} e
WHERE e.{Configuration.PersistenceIdColumnName} = :PersistenceId AND e.{Configuration.SequenceNrColumnName} BETWEEN :FromSequenceNr AND :ToSequenceNr
ORDER BY e.{Configuration.SequenceNrColumnName} ASC";
ByTagSql = $@"
SELECT *
FROM (
SELECT {allEventColumnNames}, e.{Configuration.OrderingColumnName} AS Ordering
FROM { Configuration.FullJournalTableName} e
WHERE e.{Configuration.OrderingColumnName} > :Ordering AND e.{Configuration.TagsColumnName} LIKE :Tag
ORDER BY {Configuration.OrderingColumnName} ASC
)
WHERE ROWNUM <= :Take";
InsertEventSql = $@"
INSERT INTO {Configuration.FullJournalTableName} (
{Configuration.PersistenceIdColumnName},
{Configuration.SequenceNrColumnName},
{Configuration.TimestampColumnName},
{Configuration.IsDeletedColumnName},
{Configuration.ManifestColumnName},
{Configuration.PayloadColumnName},
{Configuration.TagsColumnName},
{Configuration.SerializerIdColumnName}
) VALUES (:PersistenceId, :SequenceNr, :Timestamp, :IsDeleted, :Manifest, :Payload, :Tag, :SerializerId)";
CreateEventsJournalSql = $@"
DECLARE
table_count integer;
BEGIN
SELECT COUNT (OBJECT_ID) INTO table_count
FROM USER_OBJECTS
WHERE EXISTS (SELECT OBJECT_NAME FROM USER_OBJECTS WHERE (OBJECT_NAME = UPPER('{configuration.JournalEventsTableName}') AND OBJECT_TYPE = 'TABLE'));
IF table_count = 0 THEN
EXECUTE IMMEDIATE '
CREATE TABLE {configuration.FullJournalTableName} (
{configuration.OrderingColumnName} NUMBER(19,0) NOT NULL,
{configuration.PersistenceIdColumnName} NVARCHAR2(255) NOT NULL,
{configuration.SequenceNrColumnName} NUMBER(19,0) NOT NULL,
{configuration.TimestampColumnName} NUMBER(19,0) NOT NULL,
{configuration.IsDeletedColumnName} NUMBER(1,0) DEFAULT(0) NOT NULL CHECK (IsDeleted IN (0,1)),
{configuration.ManifestColumnName} NVARCHAR2(500) NOT NULL,
{configuration.PayloadColumnName} BLOB NOT NULL,
{configuration.TagsColumnName} NVARCHAR2(100) NULL,
{configuration.SerializerIdColumnName} NUMBER(10,0) NULL,
CONSTRAINT QU_{configuration.JournalEventsTableName} UNIQUE({configuration.PersistenceIdColumnName}, {configuration.SequenceNrColumnName})
)';
EXECUTE IMMEDIATE '
CREATE SEQUENCE {configuration.FullJournalTableName}_SEQ
START WITH 1
INCREMENT BY 1
CACHE 1000
ORDER
NOCYCLE
NOMAXVALUE
';
EXECUTE IMMEDIATE '
CREATE OR REPLACE TRIGGER {configuration.FullJournalTableName}_TRG
BEFORE INSERT ON {configuration.JournalEventsTableName}
FOR EACH ROW
BEGIN
:new.{configuration.OrderingColumnName} := {configuration.JournalEventsTableName}_SEQ.NEXTVAL;
END;
';
EXECUTE IMMEDIATE 'ALTER TRIGGER {configuration.FullJournalTableName}_TRG ENABLE';
EXECUTE IMMEDIATE 'CREATE INDEX IX_{configuration.JournalEventsTableName}_{configuration.SequenceNrColumnName} ON {configuration.FullJournalTableName}({configuration.SequenceNrColumnName})';
EXECUTE IMMEDIATE 'CREATE INDEX IX_{configuration.JournalEventsTableName}_{configuration.TimestampColumnName} ON {configuration.FullJournalTableName}({configuration.TimestampColumnName})';
END IF;
END;";
CreateMetaTableSql = $@"
DECLARE
table_count integer;
BEGIN
SELECT COUNT (OBJECT_ID) INTO table_count
FROM USER_OBJECTS
WHERE EXISTS (SELECT OBJECT_NAME FROM USER_OBJECTS WHERE (OBJECT_NAME = UPPER('{configuration.MetaTableName}') AND OBJECT_TYPE = 'TABLE'));
IF table_count = 0 THEN
EXECUTE IMMEDIATE(
'CREATE TABLE {configuration.FullMetaTableName} (
{configuration.PersistenceIdColumnName} NVARCHAR2(255) NOT NULL,
{configuration.SequenceNrColumnName} NUMBER(19,0) NOT NULL,
CONSTRAINT PK_{configuration.MetaTableName} PRIMARY KEY ({configuration.PersistenceIdColumnName}, {configuration.SequenceNrColumnName})
)'
);
END IF;
END;";
}
public PostgreSqlQueryExecutor(PostgreSqlQueryConfiguration configuration, Akka.Serialization.Serialization serialization, ITimestampProvider timestampProvider)
: base(configuration, serialization, timestampProvider)
{
_configuration = configuration;
var storedAs = configuration.StoredAs.ToString().ToUpperInvariant();
CreateEventsJournalSql = $@"
CREATE TABLE IF NOT EXISTS {Configuration.FullJournalTableName} (
{Configuration.OrderingColumnName} BIGSERIAL NOT NULL PRIMARY KEY,
{Configuration.PersistenceIdColumnName} VARCHAR(255) NOT NULL,
{Configuration.SequenceNrColumnName} BIGINT NOT NULL,
{Configuration.IsDeletedColumnName} BOOLEAN NOT NULL,
{Configuration.TimestampColumnName} BIGINT NOT NULL,
{Configuration.ManifestColumnName} VARCHAR(500) NOT NULL,
{Configuration.PayloadColumnName} {storedAs} NOT NULL,
{Configuration.TagsColumnName} VARCHAR(100) NULL,
{Configuration.SerializerIdColumnName} INTEGER NULL,
CONSTRAINT {Configuration.JournalEventsTableName}_uq UNIQUE ({Configuration.PersistenceIdColumnName}, {Configuration.SequenceNrColumnName})
);
";
CreateMetaTableSql = $@"
CREATE TABLE IF NOT EXISTS {Configuration.FullMetaTableName} (
{Configuration.PersistenceIdColumnName} VARCHAR(255) NOT NULL,
{Configuration.SequenceNrColumnName} BIGINT NOT NULL,
CONSTRAINT {Configuration.MetaTableName}_pk PRIMARY KEY ({Configuration.PersistenceIdColumnName}, {Configuration.SequenceNrColumnName})
);";
switch (_configuration.StoredAs)
{
case StoredAsType.ByteA:
_serialize = e =>
{
var serializer = Serialization.FindSerializerFor(e.Payload);
return(new SerializationResult(NpgsqlDbType.Bytea, serializer.ToBinary(e.Payload), 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 = e => new SerializationResult(NpgsqlDbType.Jsonb, JsonConvert.SerializeObject(e.Payload, _configuration.JsonSerializerSettings), null);
_deserialize = (type, serialized, manifest, serializerId) => JsonConvert.DeserializeObject((string)serialized, type, _configuration.JsonSerializerSettings);
break;
case StoredAsType.Json:
_serialize = e => new SerializationResult(NpgsqlDbType.Json, JsonConvert.SerializeObject(e.Payload, _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");
}
}
public ByteArraySnapshotSerializer(Akka.Serialization.Serialization serialization, SnapshotConfig config)
{
_serialization = serialization;
_config = config;
}
/// <summary>
/// Serializes the given object into a byte array and uses the given address to decorate serialized ActorRef's
/// </summary>
/// <param name="address">The address to use when serializing local ActorRef´s</param>
/// <param name="obj">The object to serialize</param>
/// <returns></returns>
public byte[] ToBinaryWithAddress(Address address, object obj)
{
return(Serialization.SerializeWithTransport(system, address, () => ToBinary(obj)));
}
public MongoDbJournal()
{
_settings = MongoDbPersistence.Get(Context.System).JournalSettings;
_serialization = Context.System.Serialization;
}