public async Task CreateMediaAsync(Guid id, string fileName, string contentType)
{
const string Sql = "insert into media ( id, file_name, content_type ) values ( @id, @fileName, @contentType )";
using (var connection = _settings.GetConnection())
{
await connection.ExecuteAsync(Sql, new { id, fileName, contentType });
}
}
public async Task TrackSessionActivityAsync(Guid session, string type, object data)
{
const string Sql = @"
insert into session ( id ) values ( @session ) on conflict ( id ) do update set last_activity_at = now();
insert into session_activity ( id, session_id, type, attributes ) values ( @id, @session, @type, @data::jsonb );";
using (var connection = _settings.GetConnection())
{
var id = Guid.NewGuid();
await connection.ExecuteAsync(Sql, new { id, session, type, data = JsonConvert.SerializeObject(data) });
}
}
public void InitialiseSchema(NpgsqlSettings settings)
{
_log.LogInformation("Checking database is initialised");
using (var connection = settings.GetConnection())
{
connection.Open();
var initialised = CheckDatabaseInitialised(connection);
_log.LogInformation($"Database is{(initialised ? string.Empty : " not")} initialised");
if (!initialised)
{
_log.LogInformation($"Initialising database");
var initTransaction = connection.BeginTransaction();
connection.Execute("create schema kasbah;");
connection.Execute("create table kasbah.setting ( key text, value jsonb );");
connection.Execute("insert into kasbah.setting values ( 'schema_version', '{\"version\":0}'::jsonb );");
initTransaction.Commit();
_log.LogInformation($"Database initialised");
}
_log.LogInformation("Checking for pending database migrations");
var currentSchemaVersion = GetSystemValue <SchemaVersion>(connection, SchemaVersion.Key);
var assembly = typeof(DatabaseUtility).GetTypeInfo().Assembly;
const string Prefix = "Kasbah.Provider.Npgsql.Resources.migration_";
var resources = assembly.GetManifestResourceNames()
.Where(ent => ent.StartsWith(Prefix))
.Select(ent => new
{
Name = ent,
ShortName = ent.Replace(".sql", string.Empty).Split('.').Last(),
Version = int.Parse(ent
.Replace(Prefix, string.Empty)
.Replace(".sql", string.Empty)
.TrimStart(new[] { '0' }))
})
.Where(ent => ent.Version > currentSchemaVersion.Version)
.OrderBy(ent => ent.Version);
if (resources.Any())
{
_log.LogInformation($"Pending database migrations: {string.Join(", ", resources.Select(ent => ent.ShortName))}");
var transaction = connection.BeginTransaction();
try
{
foreach (var resource in resources)
{
_log.LogInformation($"Running migrations in '{resource.ShortName}'");
using (var stream = assembly.GetManifestResourceStream(resource.Name))
{
var buffer = new byte[stream.Length];
stream.Read(buffer, 0, buffer.Length);
var content = Encoding.UTF8.GetString(buffer);
var statements = content.Split(';')
.Where(ent => !string.IsNullOrWhiteSpace(ent));
foreach (var sql in statements)
{
try
{
connection.Execute(sql);
}
catch (PostgresException ex)
{
_log.LogError($"Migration failed: '{resource.ShortName}'; statement: {sql}; error: {ex.Message}");
throw;
}
}
PutSystemValue(connection, SchemaVersion.Key, new SchemaVersion {
Version = resource.Version
});
_log.LogInformation($"Migration run successfully: '{resource.ShortName}'");
}
}
transaction.Commit();
_log.LogInformation("All migrations run successfully");
}
catch (PostgresException)
{
transaction.Rollback();
throw;
}
}
else
{
_log.LogInformation("No database migrations pending");
}
}
}
public async Task <Guid> CreateNodeAsync(Guid?parent, string alias, string type, string displayName = null)
{
const string Sql = @"
insert into kasbah.node
( id, parent_id, alias, type, display_name,
id_taxonomy,
alias_taxonomy )
values
( @id, @parent, @alias, @type, @displayName,
(select id_taxonomy from node where id = @parent) || @id::uuid,
(select alias_taxonomy from node where id = @parent) || @alias::varchar(512)
);";
using (var connection = _settings.GetConnection())
{
var id = Guid.NewGuid();
await connection.ExecuteAsync(Sql, new { id, parent, alias, type, displayName });
return(id);
}
}
