JournalRepository.cs

using SQLitePCL;

namespace SQLiteEncrypted.ConsoleJournal;

internal sealed record JournalEntry(
    long Id,
    string Title,
    string Body,
    string CreatedAt,
    string UpdatedAt);

internal sealed class JournalRepository
{
    private readonly EncryptedDatabase _database;

    public JournalRepository(EncryptedDatabase database)
    {
        _database = database;
        _database.Execute("""
            CREATE TABLE IF NOT EXISTS journal_entries (
                id INTEGER PRIMARY KEY,
                title TEXT NOT NULL,
                body TEXT NOT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )
            """);
    }

    public JournalEntry Add(string title, string body)
    {
        const string sql = """
            INSERT INTO journal_entries(title, body, created_at, updated_at)
            VALUES (?, ?, ?, ?)
            """;
        var now = DateTimeOffset.UtcNow.ToString("O");
        WithStatement(sql, statement =>
        {
            BindText(statement, 1, title);
            BindText(statement, 2, body);
            BindText(statement, 3, now);
            BindText(statement, 4, now);
            StepDone(statement, sql);
        });
        return Get(raw.sqlite3_last_insert_rowid(_database.Handle))
            ?? throw new InvalidOperationException("The inserted journal entry was not found.");
    }

    public IReadOnlyList<JournalEntry> List(string? search = null)
    {
        var sql = string.IsNullOrWhiteSpace(search)
            ? """
                SELECT id, title, body, created_at, updated_at
                FROM journal_entries ORDER BY updated_at DESC, id DESC
                """
            : """
                SELECT id, title, body, created_at, updated_at
                FROM journal_entries
                WHERE title LIKE ? ESCAPE '\' OR body LIKE ? ESCAPE '\'
                ORDER BY updated_at DESC, id DESC
                """;

        var entries = new List<JournalEntry>();
        WithStatement(sql, statement =>
        {
            if (!string.IsNullOrWhiteSpace(search))
            {
                var escaped = search.Replace("\\", "\\\\", StringComparison.Ordinal)
                    .Replace("%", "\\%", StringComparison.Ordinal)
                    .Replace("_", "\\_", StringComparison.Ordinal);
                BindText(statement, 1, $"%{escaped}%");
                BindText(statement, 2, $"%{escaped}%");
            }

            while (true)
            {
                var result = raw.sqlite3_step(statement);
                if (result == raw.SQLITE_DONE)
                {
                    break;
                }
                EncryptedDatabase.Check(result, _database.Handle, sql, raw.SQLITE_ROW);
                entries.Add(ReadEntry(statement));
            }
        });
        return entries;
    }

    public JournalEntry? Get(long id)
    {
        const string sql = """
            SELECT id, title, body, created_at, updated_at
            FROM journal_entries WHERE id = ?
            """;
        JournalEntry? entry = null;
        WithStatement(sql, statement =>
        {
            BindInt64(statement, 1, id);
            var result = raw.sqlite3_step(statement);
            if (result == raw.SQLITE_DONE)
            {
                return;
            }
            EncryptedDatabase.Check(result, _database.Handle, sql, raw.SQLITE_ROW);
            entry = ReadEntry(statement);
            EncryptedDatabase.Check(
                raw.sqlite3_step(statement), _database.Handle, sql, raw.SQLITE_DONE);
        });
        return entry;
    }

    public bool Update(long id, string title, string body)
    {
        const string sql = """
            UPDATE journal_entries
            SET title = ?, body = ?, updated_at = ?
            WHERE id = ?
            """;
        WithStatement(sql, statement =>
        {
            BindText(statement, 1, title);
            BindText(statement, 2, body);
            BindText(statement, 3, DateTimeOffset.UtcNow.ToString("O"));
            BindInt64(statement, 4, id);
            StepDone(statement, sql);
        });
        return raw.sqlite3_changes(_database.Handle) == 1;
    }

    public bool Delete(long id)
    {
        const string sql = "DELETE FROM journal_entries WHERE id = ?";
        WithStatement(sql, statement =>
        {
            BindInt64(statement, 1, id);
            StepDone(statement, sql);
        });
        return raw.sqlite3_changes(_database.Handle) == 1;
    }

    private void WithStatement(string sql, Action<sqlite3_stmt> action)
    {
        EncryptedDatabase.Check(
            raw.sqlite3_prepare_v2(_database.Handle, sql, out var statement),
            _database.Handle,
            sql);
        using (statement)
        {
            action(statement);
        }
    }

    private void BindText(sqlite3_stmt statement, int index, string value)
    {
        EncryptedDatabase.Check(
            raw.sqlite3_bind_text(statement, index, value),
            _database.Handle,
            $"bind text parameter {index}");
    }

    private void BindInt64(sqlite3_stmt statement, int index, long value)
    {
        EncryptedDatabase.Check(
            raw.sqlite3_bind_int64(statement, index, value),
            _database.Handle,
            $"bind integer parameter {index}");
    }

    private void StepDone(sqlite3_stmt statement, string sql)
    {
        EncryptedDatabase.Check(
            raw.sqlite3_step(statement), _database.Handle, sql, raw.SQLITE_DONE);
    }

    private static JournalEntry ReadEntry(sqlite3_stmt statement) => new(
        raw.sqlite3_column_int64(statement, 0),
        raw.sqlite3_column_text(statement, 1).utf8_to_string(),
        raw.sqlite3_column_text(statement, 2).utf8_to_string(),
        raw.sqlite3_column_text(statement, 3).utf8_to_string(),
        raw.sqlite3_column_text(statement, 4).utf8_to_string());
}