public async Task CreateEventTable(EventSubTable subTable)
{
var stateIdSql = stateIdIsString ? $"StateId varchar({stringStorageOptions.StateIdLength}) not null" : "StateId int8 not null";
var sql = $@"
create table {subTable.SubTable} (
{stateIdSql},
UniqueId varchar(250) null,
TypeCode varchar(300) not null,
Data json not null,
Version int8 not null,
Timestamp int8 not null,
constraint {subTable.SubTable}_id_unique unique(StateId,TypeCode,UniqueId)
);
CREATE UNIQUE INDEX IF NOT EXISTS {subTable.SubTable}_Version ON {subTable.SubTable} USING btree(StateId, Version);";
const string insertSql = "INSERT into SubTable_Records VALUES(@TableName,@SubTable,@Index,@StartTime,@EndTime)";
using var connection = storageOptions.CreateConnection();
await connection.OpenAsync();
using var trans = connection.BeginTransaction();
try
{
await connection.ExecuteAsync(sql, transaction : trans);
await connection.ExecuteAsync(insertSql, subTable, trans);
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
public async Task CreateEventTable(EventSubTable subTable)
{
var stateIdSql = stateIdIsString ? $"`StateId` varchar({stringStorageOptions.StateIdLength}) NOT NULL" : "`StateId` bigint(20) NOT NULL";
var sql = $@"
create table if not exists `{subTable.SubTable}` (
{stateIdSql},
`UniqueId` varchar(250) NULL DEFAULT NULL,
`TypeCode` varchar(300) NOT NULL,
`Data` json NOT NULL,
`Version` int8 NOT NULL,
`Timestamp` int8 NOT NULL,
UNIQUE INDEX `id_unique`(`StateId`, `TypeCode`, `UniqueId`) USING BTREE,
UNIQUE INDEX `Version`(`StateId`, `Version`) USING BTREE
);";
const string insertSql = "INSERT INTO SubTable_Records(TableName,SubTable,`Index`,StartTime,EndTime) VALUES(@TableName,@SubTable,@Index,@StartTime,@EndTime)";
using var connection = storageOptions.CreateConnection();
await connection.OpenAsync();
using var trans = connection.BeginTransaction();
try
{
await connection.ExecuteAsync(sql, transaction : trans);
await connection.ExecuteAsync(insertSql, subTable, trans);
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
public async Task CreateEventTable(EventSubTable subTable)
{
var stateIdSql = stateIdIsString ? $"StateId varchar({stringStorageOptions.StateIdLength}) not null" : "StateId bigint not null";
var sql = $@"
create table {subTable.SubTable} (
{stateIdSql},
UniqueId varchar(250) null,
TypeCode varchar(100) not null,
Data nvarchar(max) not null,
Version bigint not null,
Timestamp bigint not null,
INDEX account_event_id_unique unique(StateId,TypeCode,UniqueId),
INDEX account_event_Version unique(StateId, Version)
);";
const string insertSql = "INSERT into SubTable_Records VALUES(@TableName,@SubTable,@Index,@StartTime,@EndTime)";
using var connection = storageOptions.CreateConnection();
await connection.OpenAsync();
using var trans = connection.BeginTransaction();
try
{
await connection.ExecuteAsync(sql, transaction : trans);
await connection.ExecuteAsync(insertSql, subTable, trans);
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
public async ValueTask <EventSubTable> GetTable(long eventTimestamp)
{
var getTask = GetSubTables();
if (!getTask.IsCompletedSuccessfully)
{
await getTask;
}
var subTable = SubTableMillionSecondsInterval == 0 ? getTask.Result.LastOrDefault() : getTask.Result.SingleOrDefault(table => table.StartTime <= eventTimestamp && table.EndTime > eventTimestamp);
if (subTable == default)
{
await semaphore.WaitAsync();
subTable = SubTableMillionSecondsInterval == 0 ? getTask.Result.LastOrDefault() : getTask.Result.SingleOrDefault(table => table.StartTime <= eventTimestamp && table.EndTime > eventTimestamp);
try
{
if (subTable == default)
{
var lastSubTable = getTask.Result.LastOrDefault();
var startTime = lastSubTable != default ? (lastSubTable.EndTime == lastSubTable.StartTime ? DateTimeOffset.UtcNow.ToUnixTimeMilliseconds() : lastSubTable.EndTime) : eventTimestamp;
var index = lastSubTable == default ? 0 : lastSubTable.Index + 1;
subTable = new EventSubTable
{
TableName = EventTable,
SubTable = $"{EventTable}_{index}",
Index = index,
StartTime = startTime,
EndTime = startTime + SubTableMillionSecondsInterval
};
try
{
await BuildRepository.CreateEventTable(subTable);
_subTables.Add(subTable);
}
catch (Exception ex)
{
logger.LogCritical(ex, serializer.SerializeToString(subTable));
subTable = default;
_subTables = (await BuildRepository.GetSubTables()).OrderBy(table => table.EndTime).ToList();
}
}
}
finally
{
semaphore.Release();
}
}
if (subTable == default)
{
subTable = await GetTable(eventTimestamp);
}
return(subTable);
}
