Spry/src/Authentication/SqlSessionRepository.vala

using Invercargill;
using Invercargill.DataStructures;
using InvercargillSql;

namespace Spry.Authentication {

    /**
     * SQL implementation of SessionRepository using InvercargillSql.
     */
    public class SqlSessionRepository : Object, SessionRepository {

        private Connection _connection;

        // =========================================================================
        // Constructor
        // =========================================================================

        public SqlSessionRepository(Connection connection) {
            _connection = connection;
        }

        // =========================================================================
        // Retrieval Operations
        // =========================================================================

        public async Session? get_by_id(string id) throws Error {
            var sql = "SELECT * FROM sessions WHERE id = :id";

            var results = yield _connection.create_command(sql)
                .with_parameter("id", id)
                .execute_query_async();

            var row = results.first_or_default();
            if (row == null) {
                return null;
            }

            return session_from_properties(row);
        }

        public async Vector<Session> get_by_user_id(string user_id) throws Error {
            var sql = """
                SELECT * FROM sessions
                WHERE user_id = :user_id
                ORDER BY created_at DESC
            """;

            var results = yield _connection.create_command(sql)
                .with_parameter("user_id", user_id)
                .execute_query_async();

            var sessions = new Vector<Session>();
            foreach (var row in results) {
                sessions.add(session_from_properties(row));
            }

            return sessions;
        }

        // =========================================================================
        // Mutation Operations
        // =========================================================================

        public async Session create(string id, string user_id, DateTime expires_at) throws Error {
            var now = new DateTime.now_utc();

            var sql = """
                INSERT INTO sessions (id, user_id, created_at, expires_at, last_accessed_at)
                VALUES (:id, :user_id, :created_at, :expires_at, :last_accessed_at)
            """;

            yield _connection.create_command(sql)
                .with_parameter("id", id)
                .with_parameter("user_id", user_id)
                .with_parameter("created_at", now.format_iso8601())
                .with_parameter("expires_at", expires_at.format_iso8601())
                .with_parameter("last_accessed_at", now.format_iso8601())
                .execute_non_query_async();

            var session = new Session();
            session.id = id;
            session.user_id = user_id;
            session.created_at = now;
            session.expires_at = expires_at;

            return session;
        }

        public async void update(Session session) throws Error {
            var sql = """
                UPDATE sessions SET
                    expires_at = :expires_at
                WHERE id = :id
            """;

            yield _connection.create_command(sql)
                .with_parameter("id", session.id)
                .with_parameter("expires_at", session.expires_at.format_iso8601())
                .execute_non_query_async();
        }

        public async void delete(string id) throws Error {
            var sql = "DELETE FROM sessions WHERE id = :id";

            yield _connection.create_command(sql)
                .with_parameter("id", id)
                .execute_non_query_async();
        }

        public async void delete_by_user_id(string user_id) throws Error {
            var sql = "DELETE FROM sessions WHERE user_id = :user_id";

            yield _connection.create_command(sql)
                .with_parameter("user_id", user_id)
                .execute_non_query_async();
        }

        // =========================================================================
        // Cleanup Operations
        // =========================================================================

        public async void delete_expired() throws Error {
            var now = new DateTime.now_utc();

            var sql = "DELETE FROM sessions WHERE expires_at < :now";

            yield _connection.create_command(sql)
                .with_parameter("now", now.format_iso8601())
                .execute_non_query_async();
        }

        // =========================================================================
        // Private Helpers
        // =========================================================================

        private Session session_from_properties(Properties props) {
            var session = new Session();

            // Required fields
            session.id = get_string_or_empty(props, "id");
            session.user_id = get_string_or_empty(props, "user_id");

            // created_at
            var created_str = get_string_or_empty(props, "created_at");
            if (created_str.length > 0) {
                session.created_at = new DateTime.from_iso8601(created_str, new TimeZone.utc());
            }

            // expires_at
            var expires_str = get_string_or_empty(props, "expires_at");
            if (expires_str.length > 0) {
                session.expires_at = new DateTime.from_iso8601(expires_str, new TimeZone.utc());
            }

            // ip_address (nullable)
            var ip_address = get_string_or_null(props, "ip_address");
            session.ip_address = ip_address;

            // user_agent (nullable)
            var user_agent = get_string_or_null(props, "user_agent");
            session.user_agent = user_agent;

            return session;
        }

        private string get_string_or_empty(Properties props, string key) {
            if (!props.has(key)) {
                return "";
            }
            var elem = props.get(key);
            if (elem == null) {
                return "";
            }
            var str = elem.as<string>();
            return str ?? "";
        }

        private string? get_string_or_null(Properties props, string key) {
            if (!props.has(key)) {
                return null;
            }
            var elem = props.get(key);
            if (elem == null) {
                return null;
            }
            return elem.as<string>();
        }
    }
}