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); } }