public async Task <List <TableInfo> > GetTableListFromDb()
{
const string sql = "SELECT * FROM ray_tablelist where prefix=@Table order by version asc";
using (var connection = SqlFactory.CreateConnection(storageConfig.Connection))
{
return((await connection.QueryAsync <TableInfo>(sql, new { Table = storageConfig.EventTable })).AsList());
}
}
public async Task CreateEventTable(TableInfo table)
{
const string sql = @"
create table {0} (
StateId varchar({1}) not null,
UniqueId varchar(250) null,
TypeCode varchar(100) not null,
Data jsonb not null,
Version int8 not null,
Timestamp int8 not null,
constraint {0}_id_unique unique(StateId,TypeCode,UniqueId)
) WITH (OIDS=FALSE);
CREATE UNIQUE INDEX {0}_Version ON {0} USING btree(StateId, Version);";
const string insertSql = "INSERT into ray_tablelist VALUES(@Prefix,@Name,@Version,@CreateTime)";
var key = $"{storageConfig.Connection}-{table.Name}-{storageConfig.StateIdLength}";
if (createEventTableListDict.TryAdd(key, true))
{
using (var connection = SqlFactory.CreateConnection(storageConfig.Connection))
{
await connection.OpenAsync();
using (var trans = connection.BeginTransaction())
{
try
{
await connection.ExecuteAsync(string.Format(sql, table.Name, storageConfig.StateIdLength), transaction : trans);
await connection.ExecuteAsync(insertSql, table, trans);
trans.Commit();
createEventTableListDict[key] = true;
}
catch (Exception e)
{
trans.Rollback();
if (e is Npgsql.PostgresException ne && ne.ErrorCode == -2147467259)
{
return;
}
else
{
createEventTableListDict.TryRemove(key, out var v);
throw;
}
}
public DbConnection CreateConnection()
{
return(SqlFactory.CreateConnection(Connection));
}
