#include "data_sqlite.h"
#include <algorithm>
#include <array>
#include <cstdint>
#include <limits>
#include <mutex>
#include <optional>
#include <string>
#include <string_view>
#include <tuple>
#include <utility>
#include <vector>
#include <spdlog/spdlog.h>
#include "game_field.h"
namespace
{
std::int64_t gameVisibilityInteger(GameVisibility visibility)
{
return static_cast<std::int64_t>(visibility);
}
mw::E<GameVisibility> gameVisibilityFromInteger(std::int64_t value)
{
switch(value)
{
case 0:
return GameVisibility::PUBLIC;
case 1:
return GameVisibility::INTERNAL;
default:
return std::unexpected(mw::runtimeError(
"Database contains an invalid game visibility"));
}
}
std::string visibilityPredicate(GameContentScope scope)
{
return scope == GameContentScope::PUBLIC_ONLY
? " AND (card.game_short_name IS NULL OR game.visibility = 0)"
: "";
}
mw::E<void> rollbackWithError(
mw::SQLite& connection,
mw::Error error)
{
auto rollback = connection.execute("ROLLBACK;");
if(!rollback)
{
spdlog::error(
"Failed to roll back schema migration: {}",
rollback.error().msg());
}
return std::unexpected(std::move(error));
}
const std::vector<std::string_view> SCHEMA_VERSION_1_STATEMENTS = {
R"sql(
CREATE TABLE application_metadata(
key TEXT PRIMARY KEY,
value TEXT NOT NULL
) STRICT;
)sql",
R"sql(
CREATE TABLE users(
id INTEGER PRIMARY KEY,
email TEXT NOT NULL,
email_key TEXT NOT NULL UNIQUE,
username TEXT,
username_key TEXT UNIQUE,
role INTEGER NOT NULL CHECK(role BETWEEN 0 AND 2),
stored_pulls INTEGER NOT NULL CHECK(stored_pulls >= 0),
pull_refresh_day INTEGER NOT NULL,
created_at INTEGER NOT NULL,
CHECK((username IS NULL) = (username_key IS NULL))
) STRICT;
)sql",
R"sql(
CREATE UNIQUE INDEX users_one_administrator
ON users(role) WHERE role = 2;
)sql",
R"sql(
CREATE TABLE authentication_challenges(
id INTEGER PRIMARY KEY,
email TEXT NOT NULL,
email_key TEXT NOT NULL,
token_hash BLOB NOT NULL UNIQUE
CHECK(length(token_hash) = 32),
created_at INTEGER NOT NULL,
expires_at INTEGER NOT NULL,
delivered_at INTEGER,
consumed_at INTEGER,
CHECK(expires_at > created_at),
CHECK(delivered_at IS NULL OR delivered_at >= created_at),
CHECK(consumed_at IS NULL OR delivered_at IS NOT NULL)
) STRICT;
)sql",
R"sql(
CREATE INDEX authentication_challenges_email
ON authentication_challenges(email_key, expires_at);
)sql",
R"sql(
CREATE TABLE sessions(
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL
REFERENCES users(id) ON DELETE CASCADE,
token_hash BLOB NOT NULL UNIQUE
CHECK(length(token_hash) = 32),
csrf_token TEXT NOT NULL UNIQUE,
created_at INTEGER NOT NULL,
expires_at INTEGER NOT NULL,
CHECK(expires_at > created_at)
) STRICT;
)sql",
R"sql(
CREATE INDEX sessions_user ON sessions(user_id);
)sql",
R"sql(
CREATE INDEX sessions_expiry ON sessions(expires_at);
)sql",
R"sql(
CREATE TABLE authentication_email_limits(
email_key TEXT PRIMARY KEY,
next_allowed_at INTEGER NOT NULL
) STRICT;
)sql",
R"sql(
CREATE TABLE authentication_quota(
utc_day INTEGER PRIMARY KEY,
attempted_sends INTEGER NOT NULL
CHECK(attempted_sends >= 0)
) STRICT;
)sql",
R"sql(
CREATE TABLE games(
short_name TEXT PRIMARY KEY,
display_name TEXT NOT NULL,
description TEXT NOT NULL DEFAULT '',
visibility INTEGER NOT NULL DEFAULT 0
CHECK(visibility IN (0, 1)),
revision INTEGER NOT NULL DEFAULT 1 CHECK(revision >= 1),
CHECK(length(short_name) > 0),
CHECK(short_name NOT GLOB '*[^a-z0-9]*')
) STRICT;
)sql",
R"sql(
CREATE TABLE game_fields(
id INTEGER PRIMARY KEY AUTOINCREMENT,
game_short_name TEXT NOT NULL
REFERENCES games(short_name) ON DELETE RESTRICT,
key TEXT NOT NULL,
label TEXT NOT NULL,
type TEXT NOT NULL
CHECK(type IN ('INTEGER', 'STRING', 'CHOICE')),
position INTEGER NOT NULL CHECK(position >= 0),
UNIQUE(game_short_name, key),
UNIQUE(id, type),
CHECK(length(key) > 0)
) STRICT;
)sql",
R"sql(
CREATE INDEX game_fields_by_game
ON game_fields(game_short_name, position, id);
)sql",
R"sql(
CREATE TABLE game_field_choices(
field_id INTEGER NOT NULL
REFERENCES game_fields(id) ON DELETE CASCADE,
value TEXT NOT NULL CHECK(length(value) > 0),
position INTEGER NOT NULL CHECK(position >= 0),
PRIMARY KEY(field_id, value)
) WITHOUT ROWID, STRICT;
)sql",
R"sql(
CREATE TRIGGER game_choice_type_insert
BEFORE INSERT ON game_field_choices
WHEN NOT EXISTS(
SELECT 1 FROM game_fields
WHERE id = NEW.field_id AND type = 'CHOICE')
BEGIN
SELECT RAISE(ABORT, 'choices require a choice field');
END;
)sql",
R"sql(
CREATE TRIGGER game_choice_type_update
BEFORE UPDATE ON game_field_choices
WHEN NOT EXISTS(
SELECT 1 FROM game_fields
WHERE id = NEW.field_id AND type = 'CHOICE')
BEGIN
SELECT RAISE(ABORT, 'choices require a choice field');
END;
)sql",
R"sql(
CREATE TRIGGER games_identity_immutable
BEFORE UPDATE OF short_name ON games
BEGIN
SELECT RAISE(ABORT, 'game short name is immutable');
END;
)sql",
R"sql(
CREATE TRIGGER game_fields_identity_immutable
BEFORE UPDATE OF game_short_name, key, type ON game_fields
BEGIN
SELECT RAISE(ABORT, 'game field identity is immutable');
END;
)sql",
R"sql(
CREATE TRIGGER game_choices_identity_immutable
BEFORE UPDATE OF field_id, value ON game_field_choices
BEGIN
SELECT RAISE(ABORT, 'game choice identity is immutable');
END;
)sql",
R"sql(
CREATE TABLE cards (
id INTEGER PRIMARY KEY,
creator_user_id INTEGER NOT NULL
REFERENCES users(id) ON DELETE RESTRICT,
game_short_name TEXT
REFERENCES games(short_name) ON DELETE RESTRICT,
card_number INTEGER NOT NULL,
name TEXT NOT NULL,
short_description TEXT,
long_description TEXT,
rarity INTEGER NOT NULL DEFAULT 0 CHECK(rarity >= 0),
front_extension TEXT NOT NULL
CHECK(front_extension IN ('jpg', 'jpeg', 'webp', 'avif')),
foil_extension TEXT
CHECK(foil_extension IN
('jpg', 'jpeg', 'webp', 'avif')),
thumbnail_extension TEXT NOT NULL
CHECK(thumbnail_extension IN
('jpg', 'jpeg', 'webp', 'avif')),
revision INTEGER NOT NULL DEFAULT 1 CHECK(revision >= 1),
CHECK(
(game_short_name IS NULL
AND card_number >= 0
AND card_number <= 4294967295)
OR
(game_short_name IS NOT NULL AND card_number >= 1)
)
) STRICT;
)sql",
R"sql(
CREATE UNIQUE INDEX cards_game_number_unique
ON cards(game_short_name, card_number)
WHERE game_short_name IS NOT NULL;
)sql",
R"sql(
CREATE UNIQUE INDEX cards_loose_number_unique
ON cards(card_number)
WHERE game_short_name IS NULL;
)sql",
R"sql(
CREATE INDEX cards_creator ON cards(creator_user_id, id);
)sql",
R"sql(
CREATE INDEX cards_pool ON cards(rarity, id) WHERE rarity > 0;
)sql",
R"sql(
CREATE TABLE game_sequences (
game_short_name TEXT PRIMARY KEY
REFERENCES games(short_name) ON DELETE RESTRICT,
last_number INTEGER NOT NULL CHECK(last_number >= 0)
) STRICT;
)sql",
R"sql(
CREATE TABLE series (
id INTEGER PRIMARY KEY,
game_short_name TEXT NOT NULL
REFERENCES games(short_name) ON DELETE RESTRICT,
name TEXT NOT NULL,
description TEXT NOT NULL DEFAULT '',
UNIQUE(game_short_name, name)
) STRICT;
)sql",
R"sql(
CREATE TABLE card_series (
card_id INTEGER NOT NULL
REFERENCES cards(id) ON DELETE CASCADE,
series_id INTEGER NOT NULL
REFERENCES series(id) ON DELETE CASCADE,
PRIMARY KEY(card_id, series_id)
) WITHOUT ROWID, STRICT;
)sql",
R"sql(
CREATE TRIGGER card_series_same_game_insert
BEFORE INSERT ON card_series
BEGIN
SELECT CASE
WHEN (SELECT game_short_name
FROM cards
WHERE id = NEW.card_id) IS NULL
THEN RAISE(ABORT, 'loose card cannot belong to a series')
WHEN (SELECT game_short_name
FROM cards
WHERE id = NEW.card_id)
!=
(SELECT game_short_name
FROM series
WHERE id = NEW.series_id)
THEN RAISE(ABORT, 'card and series games differ')
END;
END;
)sql",
R"sql(
CREATE TRIGGER cards_identity_immutable
BEFORE UPDATE OF game_short_name, card_number ON cards
BEGIN
SELECT RAISE(ABORT, 'card identity is immutable');
END;
)sql",
R"sql(
CREATE TRIGGER series_game_immutable
BEFORE UPDATE OF game_short_name ON series
BEGIN
SELECT RAISE(ABORT, 'series game is immutable');
END;
)sql",
R"sql(
CREATE TABLE card_field_values(
card_id INTEGER NOT NULL
REFERENCES cards(id) ON DELETE CASCADE,
field_id INTEGER NOT NULL,
field_type TEXT NOT NULL,
integer_value INTEGER,
string_value TEXT,
choice_value TEXT,
PRIMARY KEY(card_id, field_id),
FOREIGN KEY(field_id, field_type)
REFERENCES game_fields(id, type) ON DELETE RESTRICT,
FOREIGN KEY(field_id, choice_value)
REFERENCES game_field_choices(field_id, value)
ON DELETE RESTRICT,
CHECK(
(field_type = 'INTEGER' AND integer_value IS NOT NULL
AND string_value IS NULL AND choice_value IS NULL)
OR
(field_type = 'STRING' AND integer_value IS NULL
AND string_value IS NOT NULL AND length(string_value) > 0
AND choice_value IS NULL)
OR
(field_type = 'CHOICE' AND integer_value IS NULL
AND string_value IS NULL AND choice_value IS NOT NULL)
)
) WITHOUT ROWID, STRICT;
)sql",
R"sql(
CREATE INDEX card_field_values_by_field
ON card_field_values(field_id);
)sql",
R"sql(
CREATE INDEX card_field_values_by_choice
ON card_field_values(field_id, choice_value);
)sql",
R"sql(
CREATE TRIGGER card_field_value_game_insert
BEFORE INSERT ON card_field_values
WHEN NOT EXISTS(
SELECT 1
FROM cards JOIN game_fields
ON cards.game_short_name = game_fields.game_short_name
WHERE cards.id = NEW.card_id
AND game_fields.id = NEW.field_id)
BEGIN
SELECT RAISE(ABORT, 'card and field games differ');
END;
)sql",
R"sql(
CREATE TRIGGER card_field_value_game_update
BEFORE UPDATE ON card_field_values
WHEN NOT EXISTS(
SELECT 1
FROM cards JOIN game_fields
ON cards.game_short_name = game_fields.game_short_name
WHERE cards.id = NEW.card_id
AND game_fields.id = NEW.field_id)
BEGIN
SELECT RAISE(ABORT, 'card and field games differ');
END;
)sql",
R"sql(
CREATE TABLE card_holdings(
user_id INTEGER NOT NULL
REFERENCES users(id) ON DELETE CASCADE,
card_id INTEGER NOT NULL
REFERENCES cards(id) ON DELETE CASCADE,
quantity INTEGER NOT NULL CHECK(quantity > 0),
PRIMARY KEY(user_id, card_id)
) WITHOUT ROWID, STRICT;
)sql",
};
mw::E<std::string> tokenBytes(const TokenHash& token_hash)
{
if(token_hash.size() != 32)
{
return std::unexpected(mw::runtimeError(
"Credential digest must contain 32 bytes"));
}
return std::string(
reinterpret_cast<const char*>(token_hash.data()),
token_hash.size());
}
mw::E<UserRole> userRole(std::int64_t value)
{
if(value < static_cast<std::int64_t>(UserRole::PLAYER) ||
value > static_cast<std::int64_t>(UserRole::ADMINISTRATOR))
{
return std::unexpected(mw::runtimeError(
"Database contains an invalid user role"));
}
return static_cast<UserRole>(value);
}
using UserRow = std::tuple<
std::int64_t,
std::string,
std::string,
std::optional<std::string>,
std::int64_t,
std::int64_t,
std::int64_t,
std::int64_t>;
mw::E<User> userFromRow(UserRow row)
{
auto [id, email, email_key, username, role_value, stored_pulls,
refresh_day, created_at] = std::move(row);
auto role = userRole(role_value);
if(!role || stored_pulls < 0 ||
stored_pulls > std::numeric_limits<std::uint32_t>::max())
{
return std::unexpected(mw::runtimeError(
"Database contains an invalid user record"));
}
return User{
id,
std::move(email),
std::move(email_key),
std::move(username),
*role,
static_cast<std::uint32_t>(stored_pulls),
refresh_day,
created_at};
}
using CardRow = std::tuple<
std::int64_t,
std::int64_t,
std::optional<std::string>,
std::int64_t,
std::string,
std::optional<std::string>,
std::optional<std::string>,
std::int64_t,
std::string,
std::optional<std::string>,
std::string,
std::int64_t>;
mw::E<Card> cardFromRow(CardRow row)
{
auto [id, creator_user_id, game_short_name, card_number, name,
short_description, long_description, rarity, front_extension,
foil_extension, thumbnail_extension, revision] = std::move(row);
const bool invalid_loose_number =
!game_short_name &&
card_number > std::numeric_limits<std::uint32_t>::max();
const bool invalid_game_number =
game_short_name &&
(game_short_name->empty() || card_number == 0);
if(creator_user_id <= 0 || card_number < 0 || invalid_loose_number ||
invalid_game_number || rarity < 0 || revision < 1)
{
return std::unexpected(mw::runtimeError(
"Database contains an invalid card record"));
}
return Card{
id,
{std::move(game_short_name),
static_cast<std::uint64_t>(card_number)},
std::move(name),
std::move(short_description),
std::move(long_description),
rarity,
std::move(front_extension),
std::move(foil_extension),
std::move(thumbnail_extension),
revision,
creator_user_id};
}
mw::E<std::vector<Card>> cardsFromRows(std::vector<CardRow> rows)
{
std::vector<Card> result;
result.reserve(rows.size());
for(CardRow& row : rows)
{
auto card = cardFromRow(std::move(row));
if(!card)
{
return std::unexpected(std::move(card.error()));
}
result.push_back(std::move(*card));
}
return result;
}
mw::E<std::optional<GameDefinitionSnapshot>> loadGameDefinition(
mw::SQLite& connection,
const std::string& short_name,
std::optional<GameContentScope> scope = std::nullopt)
{
auto game_statement = connection.statementFromStr(
"SELECT short_name, display_name, description, visibility, revision "
"FROM games WHERE short_name = ?;");
if(!game_statement)
{
return std::unexpected(std::move(game_statement.error()));
}
auto game_bind = game_statement->bind(short_name);
if(!game_bind)
{
return std::unexpected(std::move(game_bind.error()));
}
auto game_rows = connection.eval<
std::string, std::string, std::string, std::int64_t, std::int64_t>(
std::move(*game_statement));
if(!game_rows)
{
return std::unexpected(std::move(game_rows.error()));
}
if(game_rows->empty())
{
return std::optional<GameDefinitionSnapshot>{};
}
auto& [stored_short_name, display_name, description, visibility_value,
revision] = game_rows->front();
auto visibility = gameVisibilityFromInteger(visibility_value);
if(!visibility)
{
return std::unexpected(std::move(visibility.error()));
}
if(revision < 1)
{
return std::unexpected(mw::runtimeError(
"Database contains an invalid game revision"));
}
if(scope == GameContentScope::PUBLIC_ONLY &&
*visibility == GameVisibility::INTERNAL)
{
return std::optional<GameDefinitionSnapshot>{};
}
GameDefinitionSnapshot snapshot{
{std::move(stored_short_name), std::move(display_name),
std::move(description), *visibility, revision},
{}};
auto field_statement = connection.statementFromStr(
"SELECT id, game_short_name, key, label, type, position "
"FROM game_fields WHERE game_short_name = ? "
"ORDER BY position, id;");
if(!field_statement)
{
return std::unexpected(std::move(field_statement.error()));
}
auto field_bind = field_statement->bind(short_name);
if(!field_bind)
{
return std::unexpected(std::move(field_bind.error()));
}
auto field_rows = connection.eval<
std::int64_t, std::string, std::string, std::string,
std::string, std::int64_t>(std::move(*field_statement));
if(!field_rows)
{
return std::unexpected(std::move(field_rows.error()));
}
snapshot.fields.reserve(field_rows->size());
for(auto& [id, game_short_name, key, label, type_name, position] :
*field_rows)
{
auto type = parseGameFieldType(type_name);
if(!type || position < 0)
{
return std::unexpected(mw::runtimeError(
"Database contains an invalid game field"));
}
GameField field{
id, std::move(game_short_name), std::move(key),
std::move(label), *type, position, {}};
auto choice_statement = connection.statementFromStr(
"SELECT value, position FROM game_field_choices "
"WHERE field_id = ? ORDER BY position, value;");
if(!choice_statement)
{
return std::unexpected(std::move(choice_statement.error()));
}
auto choice_bind = choice_statement->bind(id);
if(!choice_bind)
{
return std::unexpected(std::move(choice_bind.error()));
}
auto choice_rows = connection.eval<std::string, std::int64_t>(
std::move(*choice_statement));
if(!choice_rows)
{
return std::unexpected(std::move(choice_rows.error()));
}
field.choices.reserve(choice_rows->size());
for(auto& [value, choice_position] : *choice_rows)
{
if(choice_position < 0)
{
return std::unexpected(mw::runtimeError(
"Database contains an invalid choice position"));
}
field.choices.push_back(
{std::move(value), choice_position});
}
snapshot.fields.push_back(std::move(field));
}
return std::optional<GameDefinitionSnapshot>(std::move(snapshot));
}
class DataSourceSQLiteTransaction final
: public DataSourceTransactionInterface
{
public:
/// Adopt an active immediate transaction and its connection lock.
DataSourceSQLiteTransaction(
mw::SQLite& connection,
std::unique_lock<std::mutex> lock)
: connection_(connection),
lock_(std::move(lock))
{}
/// Roll back an uncommitted transaction and release its connection lock.
~DataSourceSQLiteTransaction() override
{
if(!committed_)
{
auto rollback = connection_.execute("ROLLBACK;");
if(!rollback)
{
spdlog::error(
"Failed to roll back card transaction: {}",
rollback.error().msg());
}
}
}
/// Prevent copying transaction ownership and its connection lock.
DataSourceSQLiteTransaction(
const DataSourceSQLiteTransaction&) = delete;
/// Prevent copy assignment of transaction ownership.
DataSourceSQLiteTransaction& operator=(
const DataSourceSQLiteTransaction&) = delete;
/// Allocate and persist the next never-reused number for one game.
mw::E<std::uint64_t> allocateGameNumber(
const std::string& game_short_name) override
{
auto statement = connection_.statementFromStr(
"UPDATE game_sequences "
"SET last_number = last_number + 1 "
"WHERE game_short_name = ? "
"RETURNING last_number;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::string>(game_short_name);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_.eval<std::int64_t>(
std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
if(rows->size() != 1 || std::get<0>(rows->front()) <= 0)
{
return std::unexpected(mw::runtimeError(
"Game sequence is missing or invalid"));
}
return static_cast<std::uint64_t>(
std::get<0>(rows->front()));
}
/// Ensure a database-defined game has a persistent sequence row.
mw::E<void> ensureGameSequence(
const std::string& game_short_name) override
{
auto statement = connection_.statementFromStr(
"INSERT OR IGNORE INTO game_sequences "
"(game_short_name, last_number) VALUES (?, 0);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::string>(game_short_name);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
return connection_.execute(std::move(*statement));
}
/// Read a complete game definition under this transaction's lock.
mw::E<std::optional<GameDefinitionSnapshot>>
getGameDefinitionForUpdate(const std::string& short_name) override
{
return loadGameDefinition(connection_, short_name);
}
/// Return deletion-relevant usage for one game.
mw::E<GameUsage> getGameUsage(
const std::string& short_name) override
{
auto statement = connection_.statementFromStr(
"SELECT (SELECT COUNT(*) FROM cards "
"WHERE game_short_name = ?), "
"(SELECT COUNT(*) FROM series WHERE game_short_name = ?), "
"COALESCE((SELECT last_number FROM game_sequences "
"WHERE game_short_name = ?), -1);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(short_name, short_name, short_name);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_.eval<
std::int64_t, std::int64_t, std::int64_t>(
std::move(*statement));
if(!rows || rows->size() != 1)
{
return std::unexpected(
rows ? mw::runtimeError("Could not read game usage")
: std::move(rows.error()));
}
auto [cards, series, last_number] = rows->front();
return GameUsage{cards, series, last_number};
}
/// Insert a game and its number sequence.
mw::E<void> insertGame(const Game& game) override
{
auto statement = connection_.statementFromStr(
"INSERT INTO games(short_name, display_name, description, "
"visibility, revision) VALUES (?, ?, ?, ?, ?);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
game.short_name, game.display_name, game.description,
gameVisibilityInteger(game.visibility), game.revision);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto inserted = connection_.execute(std::move(*statement));
if(!inserted)
{
return std::unexpected(std::move(inserted.error()));
}
return ensureGameSequence(game.short_name);
}
/// Replace mutable game metadata and advance its revision.
mw::E<bool> updateGame(
const std::string& short_name,
const std::string& display_name,
const std::string& description,
GameVisibility visibility,
std::int64_t expected_revision) override
{
auto statement = connection_.statementFromStr(
"UPDATE games SET display_name = ?, description = ?, "
"visibility = ?, revision = revision + 1 "
"WHERE short_name = ? AND revision = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
display_name, description, gameVisibilityInteger(visibility),
short_name, expected_revision);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto updated = connection_.execute(std::move(*statement));
if(!updated)
{
return std::unexpected(std::move(updated.error()));
}
return connection_.changedRowsCount() == 1;
}
/// Delete an unused game and its definition rows.
mw::E<void> deleteGame(const std::string& short_name) override
{
auto fields = connection_.statementFromStr(
"DELETE FROM game_fields WHERE game_short_name = ?;");
if(!fields)
{
return std::unexpected(std::move(fields.error()));
}
auto fields_bind = fields->bind(short_name);
if(!fields_bind)
{
return std::unexpected(std::move(fields_bind.error()));
}
auto fields_deleted = connection_.execute(std::move(*fields));
if(!fields_deleted)
{
return std::unexpected(std::move(fields_deleted.error()));
}
for(const std::string table : {"game_sequences", "games"})
{
auto statement = connection_.statementFromStr(
"DELETE FROM " + table + " WHERE " +
(table == "games" ? "short_name" : "game_short_name") +
" = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(short_name);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto deleted = connection_.execute(std::move(*statement));
if(!deleted)
{
return std::unexpected(std::move(deleted.error()));
}
}
return {};
}
/// Insert a field and its initial choices.
mw::E<std::int64_t> insertGameField(
const GameField& field,
const std::vector<GameChoice>& choices) override
{
auto statement = connection_.statementFromStr(
"INSERT INTO game_fields(game_short_name, key, label, type, "
"position) VALUES (?, ?, ?, ?, ?);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
field.game_short_name, field.key, field.label,
std::string(gameFieldTypeName(field.type)), field.position);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto inserted = connection_.execute(std::move(*statement));
if(!inserted)
{
return std::unexpected(std::move(inserted.error()));
}
const std::int64_t field_id = connection_.lastInsertRowID();
for(const GameChoice& choice : choices)
{
auto choice_result = upsertChoice(field_id, choice);
if(!choice_result)
{
return std::unexpected(std::move(choice_result.error()));
}
}
return field_id;
}
/// Replace a field label and complete choice list.
mw::E<void> updateGameField(
std::int64_t field_id,
const std::string& label,
const std::vector<GameChoice>& choices) override
{
auto label_statement = connection_.statementFromStr(
"UPDATE game_fields SET label = ? WHERE id = ?;");
if(!label_statement)
{
return std::unexpected(std::move(label_statement.error()));
}
auto label_bind = label_statement->bind(label, field_id);
if(!label_bind)
{
return std::unexpected(std::move(label_bind.error()));
}
auto label_updated = connection_.execute(
std::move(*label_statement));
if(!label_updated)
{
return std::unexpected(std::move(label_updated.error()));
}
auto rows_statement = connection_.statementFromStr(
"SELECT value FROM game_field_choices WHERE field_id = ?;");
if(!rows_statement)
{
return std::unexpected(std::move(rows_statement.error()));
}
auto rows_bind = rows_statement->bind(field_id);
if(!rows_bind)
{
return std::unexpected(std::move(rows_bind.error()));
}
auto rows = connection_.eval<std::string>(
std::move(*rows_statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
for(auto& [existing] : *rows)
{
const bool retained = std::ranges::any_of(
choices,
[&existing](const GameChoice& choice)
{
return choice.value == existing;
});
if(!retained)
{
auto deleted = deleteChoice(field_id, existing);
if(!deleted)
{
return std::unexpected(std::move(deleted.error()));
}
}
}
for(const GameChoice& choice : choices)
{
auto updated = upsertChoice(field_id, choice);
if(!updated)
{
return std::unexpected(std::move(updated.error()));
}
}
return {};
}
/// Delete a field with no stored card values.
mw::E<void> deleteGameField(std::int64_t field_id) override
{
auto statement = connection_.statementFromStr(
"DELETE FROM game_fields WHERE id = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(field_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
return connection_.execute(std::move(*statement));
}
/// Return the number of cards using a field.
mw::E<std::int64_t> countFieldUsage(std::int64_t field_id) override
{
return countUsage(
"SELECT COUNT(*) FROM card_field_values WHERE field_id = ?;",
field_id, std::nullopt);
}
/// Return the number of cards using an exact choice.
mw::E<std::int64_t> countChoiceUsage(
std::int64_t field_id, const std::string& value) override
{
return countUsage(
"SELECT COUNT(*) FROM card_field_values "
"WHERE field_id = ? AND choice_value = ?;",
field_id, value);
}
/// Replace every field position for a game.
mw::E<void> updateGameFieldOrder(
const std::string& short_name,
const std::vector<std::int64_t>& field_ids) override
{
for(std::size_t position = 0; position < field_ids.size(); ++position)
{
auto statement = connection_.statementFromStr(
"UPDATE game_fields SET position = ? "
"WHERE id = ? AND game_short_name = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
static_cast<std::int64_t>(position), field_ids[position],
short_name);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto updated = connection_.execute(std::move(*statement));
if(!updated || connection_.changedRowsCount() != 1)
{
return std::unexpected(
updated ? mw::runtimeError("Invalid field order")
: std::move(updated.error()));
}
}
return {};
}
/// Advance a matching aggregate game revision.
mw::E<bool> incrementGameRevision(
const std::string& short_name,
std::int64_t expected_revision) override
{
auto statement = connection_.statementFromStr(
"UPDATE games SET revision = revision + 1 "
"WHERE short_name = ? AND revision = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(short_name, expected_revision);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto updated = connection_.execute(std::move(*statement));
if(!updated)
{
return std::unexpected(std::move(updated.error()));
}
return connection_.changedRowsCount() == 1;
}
/// Return whether every requested series belongs to one game.
mw::E<bool> seriesBelongToGame(
const std::string& short_name,
const std::vector<std::int64_t>& series_ids) override
{
for(std::int64_t series_id : series_ids)
{
auto statement = connection_.statementFromStr(
"SELECT EXISTS(SELECT 1 FROM series "
"WHERE id = ? AND game_short_name = ?);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(series_id, short_name);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto belongs = connection_.evalToValue<int>(
std::move(*statement));
if(!belongs)
{
return std::unexpected(std::move(belongs.error()));
}
if(*belongs == 0)
{
return false;
}
}
return true;
}
/// Return whether a loose-card number already exists.
mw::E<bool> looseNumberExists(std::uint32_t number) override
{
auto statement = connection_.statementFromStr(
"SELECT EXISTS("
"SELECT 1 FROM cards "
"WHERE game_short_name IS NULL AND card_number = ?);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::int64_t>(number);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto result = connection_.evalToValue<int>(
std::move(*statement));
if(!result)
{
return std::unexpected(std::move(result.error()));
}
return *result != 0;
}
/// Re-read a user while the transaction lock is held.
mw::E<std::optional<User>> getUserForUpdate(
std::int64_t user_id) override
{
auto statement = connection_.statementFromStr(
"SELECT id, email, email_key, username, role, stored_pulls, "
"pull_refresh_day, created_at FROM users WHERE id = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::int64_t>(user_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
return readUser(std::move(*statement));
}
/// Re-read a user by normalized email while the lock is held.
mw::E<std::optional<User>> getUserByEmailKeyForUpdate(
const std::string& email_key) override
{
auto statement = connection_.statementFromStr(
"SELECT id, email, email_key, username, role, stored_pulls, "
"pull_refresh_day, created_at FROM users WHERE email_key = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::string>(email_key);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
return readUser(std::move(*statement));
}
/// Return whether a user owns a card while the lock is held.
mw::E<bool> userOwnsCardForUpdate(
std::int64_t user_id, std::int64_t card_id) override
{
auto statement = connection_.statementFromStr(
"SELECT EXISTS(SELECT 1 FROM card_holdings "
"WHERE user_id = ? AND card_id = ?);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(user_id, card_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto result = connection_.evalToValue<int>(std::move(*statement));
if(!result)
{
return std::unexpected(std::move(result.error()));
}
return *result != 0;
}
/// Return the current positive-rarity pool under the lock.
mw::E<std::vector<Card>> getPoolCardsForUpdate() override
{
return readCards(
"LEFT JOIN games AS game "
"ON game.short_name = card.game_short_name "
"WHERE card.rarity > 0 AND (card.game_short_name IS NULL "
"OR game.visibility = 0) ORDER BY card.id");
}
/// Insert a newly confirmed account and return its internal ID.
mw::E<std::int64_t> insertUser(const User& user) override
{
if(user.id != 0)
{
return std::unexpected(mw::runtimeError(
"A new user cannot already have an internal ID"));
}
auto statement = connection_.statementFromStr(
"INSERT INTO users(email, email_key, username, username_key, "
"role, stored_pulls, pull_refresh_day, created_at) "
"VALUES (?, ?, ?, NULL, ?, ?, ?, ?);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
user.email,
user.email_key,
user.username,
static_cast<std::int64_t>(user.role),
static_cast<std::int64_t>(user.stored_pulls),
user.pull_refresh_day,
user.created_at);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto inserted = connection_.execute(std::move(*statement));
if(!inserted)
{
return std::unexpected(std::move(inserted.error()));
}
return connection_.lastInsertRowID();
}
/// Atomically replace a user's normalized username pair.
mw::E<bool> updateUsername(
std::int64_t user_id,
const std::string& username,
const std::string& username_key) override
{
auto statement = connection_.statementFromStr(
"UPDATE users SET username = ?, username_key = ? "
"WHERE id = ? RETURNING id;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(username, username_key, user_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_.eval<std::int64_t>(std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
return !rows->empty();
}
/// Conditionally promote one player to creator.
mw::E<bool> promoteUser(std::int64_t user_id) override
{
auto statement = connection_.statementFromStr(
"UPDATE users SET role = 1 WHERE id = ? AND role = 0 "
"RETURNING id;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::int64_t>(user_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_.eval<std::int64_t>(std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
return !rows->empty();
}
/// Reserve per-email and optional global delivery capacity.
mw::E<AuthenticationReservation> reserveAuthenticationEmail(
const std::string& email_key,
std::int64_t now,
bool use_global_quota,
std::int64_t utc_day,
std::uint32_t daily_limit) override
{
auto saved = connection_.execute("SAVEPOINT email_reservation;");
if(!saved)
{
return std::unexpected(std::move(saved.error()));
}
auto email_statement = connection_.statementFromStr(
"INSERT INTO authentication_email_limits"
"(email_key, next_allowed_at) VALUES (?, ?) "
"ON CONFLICT(email_key) DO UPDATE SET "
"next_allowed_at = excluded.next_allowed_at "
"WHERE authentication_email_limits.next_allowed_at <= ? "
"RETURNING next_allowed_at;");
if(!email_statement)
{
return rollbackReservation(std::move(email_statement.error()));
}
auto email_bind = email_statement->bind(email_key, now + 60, now);
if(!email_bind)
{
return rollbackReservation(std::move(email_bind.error()));
}
auto email_rows = connection_.eval<std::int64_t>(
std::move(*email_statement));
if(!email_rows)
{
return rollbackReservation(std::move(email_rows.error()));
}
if(email_rows->empty())
{
auto retry = emailRetryAfter(email_key, now);
rollbackReservationState();
if(!retry)
{
return std::unexpected(std::move(retry.error()));
}
return AuthenticationReservation{
AuthenticationReservationStatus::EMAIL_LIMITED,
*retry};
}
if(use_global_quota)
{
auto quota = connection_.statementFromStr(
"INSERT INTO authentication_quota"
"(utc_day, attempted_sends) VALUES (?, 1) "
"ON CONFLICT(utc_day) DO UPDATE SET "
"attempted_sends = attempted_sends + 1 "
"WHERE attempted_sends < ? RETURNING attempted_sends;");
if(!quota)
{
return rollbackReservation(std::move(quota.error()));
}
auto quota_bind = quota->bind(
utc_day, static_cast<std::int64_t>(daily_limit));
if(!quota_bind)
{
return rollbackReservation(std::move(quota_bind.error()));
}
auto quota_rows = connection_.eval<std::int64_t>(
std::move(*quota));
if(!quota_rows)
{
return rollbackReservation(std::move(quota_rows.error()));
}
if(quota_rows->empty())
{
rollbackReservationState();
return AuthenticationReservation{
AuthenticationReservationStatus::GLOBAL_LIMITED,
std::max<std::int64_t>(
1, (utc_day + 1) * 86400 - now)};
}
}
auto released = connection_.execute(
"RELEASE email_reservation;");
if(!released)
{
return std::unexpected(std::move(released.error()));
}
return AuthenticationReservation{
AuthenticationReservationStatus::RESERVED, 0};
}
/// Insert a pending authentication challenge and return its ID.
mw::E<std::int64_t> insertAuthenticationChallenge(
const std::string& email,
const std::string& email_key,
const TokenHash& token_hash,
std::int64_t created_at,
std::int64_t expires_at) override
{
auto bytes = tokenBytes(token_hash);
if(!bytes)
{
return std::unexpected(std::move(bytes.error()));
}
auto statement = connection_.statementFromStr(
"INSERT INTO authentication_challenges"
"(email, email_key, token_hash, created_at, expires_at) "
"VALUES (?, ?, CAST(? AS BLOB), ?, ?);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
email, email_key, *bytes, created_at, expires_at);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto inserted = connection_.execute(std::move(*statement));
if(!inserted)
{
return std::unexpected(std::move(inserted.error()));
}
return connection_.lastInsertRowID();
}
/// Activate a pending challenge after successful delivery.
mw::E<bool> markAuthenticationChallengeDelivered(
std::int64_t challenge_id,
std::int64_t delivered_at) override
{
auto statement = connection_.statementFromStr(
"UPDATE authentication_challenges SET delivered_at = ? "
"WHERE id = ? AND delivered_at IS NULL AND consumed_at IS NULL "
"RETURNING id;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(delivered_at, challenge_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_.eval<std::int64_t>(std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
return !rows->empty();
}
/// Delete one challenge after failed delivery.
mw::E<void> deleteAuthenticationChallenge(
std::int64_t challenge_id) override
{
auto statement = connection_.statementFromStr(
"DELETE FROM authentication_challenges "
"WHERE id = ? AND delivered_at IS NULL;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::int64_t>(challenge_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
return connection_.execute(std::move(*statement));
}
/// Atomically consume one valid delivered challenge.
mw::E<std::optional<AuthenticationChallenge>>
consumeAuthenticationChallenge(
const TokenHash& token_hash, std::int64_t now) override
{
auto bytes = tokenBytes(token_hash);
if(!bytes)
{
return std::unexpected(std::move(bytes.error()));
}
auto statement = connection_.statementFromStr(
"UPDATE authentication_challenges SET consumed_at = ? "
"WHERE token_hash = CAST(? AS BLOB) "
"AND delivered_at IS NOT NULL AND consumed_at IS NULL "
"AND expires_at > ? "
"RETURNING id, email, email_key, expires_at;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(now, *bytes, now);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_.eval<
std::int64_t,
std::string,
std::string,
std::int64_t>(std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
if(rows->empty())
{
return std::optional<AuthenticationChallenge>{};
}
auto& [id, email, email_key, expires_at] = rows->front();
return AuthenticationChallenge{
id, std::move(email), std::move(email_key), expires_at};
}
/// Invalidate every other outstanding challenge for an email key.
mw::E<void> invalidateAuthenticationChallenges(
const std::string& email_key,
std::int64_t except_challenge_id,
std::int64_t now) override
{
auto statement = connection_.statementFromStr(
"UPDATE authentication_challenges SET consumed_at = ? "
"WHERE email_key = ? AND id != ? "
"AND delivered_at IS NOT NULL AND consumed_at IS NULL;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
now, email_key, except_challenge_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
return connection_.execute(std::move(*statement));
}
/// Insert a four-week session and return its internal ID.
mw::E<std::int64_t> insertSession(
std::int64_t user_id,
const TokenHash& token_hash,
const std::string& csrf_token,
std::int64_t created_at,
std::int64_t expires_at) override
{
auto bytes = tokenBytes(token_hash);
if(!bytes)
{
return std::unexpected(std::move(bytes.error()));
}
auto statement = connection_.statementFromStr(
"INSERT INTO sessions(user_id, token_hash, csrf_token, "
"created_at, expires_at) "
"VALUES (?, CAST(? AS BLOB), ?, ?, ?);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
user_id, *bytes, csrf_token, created_at, expires_at);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto inserted = connection_.execute(std::move(*statement));
if(!inserted)
{
return std::unexpected(std::move(inserted.error()));
}
return connection_.lastInsertRowID();
}
/// Delete a session by its token digest.
mw::E<void> deleteSession(const TokenHash& token_hash) override
{
auto bytes = tokenBytes(token_hash);
if(!bytes)
{
return std::unexpected(std::move(bytes.error()));
}
auto statement = connection_.statementFromStr(
"DELETE FROM sessions WHERE token_hash = CAST(? AS BLOB);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::string>(*bytes);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
return connection_.execute(std::move(*statement));
}
/// Persist a lazily refreshed pull state.
mw::E<void> updatePullState(
std::int64_t user_id,
std::uint32_t stored_pulls,
std::int64_t refresh_day) override
{
auto statement = connection_.statementFromStr(
"UPDATE users SET stored_pulls = ?, pull_refresh_day = ? "
"WHERE id = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
static_cast<std::int64_t>(stored_pulls),
refresh_day,
user_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto updated = connection_.execute(std::move(*statement));
if(!updated)
{
return std::unexpected(std::move(updated.error()));
}
if(connection_.changedRowsCount() != 1)
{
return std::unexpected(mw::runtimeError(
"User disappeared while updating pull state"));
}
return {};
}
/// Insert or increment a holding and return its new quantity.
mw::E<std::int64_t> incrementHolding(
std::int64_t user_id, std::int64_t card_id) override
{
auto statement = connection_.statementFromStr(
"INSERT INTO card_holdings(user_id, card_id, quantity) "
"VALUES (?, ?, 1) ON CONFLICT(user_id, card_id) DO UPDATE "
"SET quantity = quantity + 1 "
"WHERE quantity < 9223372036854775807 RETURNING quantity;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(user_id, card_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_.eval<std::int64_t>(std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
if(rows->size() != 1)
{
return std::unexpected(mw::runtimeError(
"Card quantity cannot be incremented"));
}
return std::get<0>(rows->front());
}
/// Re-read a card while the transaction lock is held.
mw::E<std::optional<Card>> getCardForUpdate(
std::int64_t card_id) override
{
auto statement = connection_.statementFromStr(
"SELECT id, creator_user_id, game_short_name, card_number, name, "
"short_description, long_description, rarity, "
"front_extension, foil_extension, thumbnail_extension, "
"revision FROM cards WHERE id = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::int64_t>(card_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_.eval<
std::int64_t,
std::int64_t,
std::optional<std::string>,
std::int64_t,
std::string,
std::optional<std::string>,
std::optional<std::string>,
std::int64_t,
std::string,
std::optional<std::string>,
std::string,
std::int64_t>(std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
if(rows->empty())
{
return std::optional<Card>{};
}
auto& [
id,
creator_user_id,
game_short_name,
card_number,
name,
short_description,
long_description,
rarity,
front_extension,
foil_extension,
thumbnail_extension,
revision] = rows->front();
Card card = {
id,
{std::move(game_short_name),
static_cast<std::uint64_t>(card_number)},
std::move(name),
std::move(short_description),
std::move(long_description),
rarity,
std::move(front_extension),
std::move(foil_extension),
std::move(thumbnail_extension),
revision,
creator_user_id,
};
return std::optional<Card>(std::move(card));
}
/// Insert a card with generic custom values and memberships.
mw::E<std::int64_t> insertCard(
const Card& card,
const std::vector<GameFieldValue>& field_values,
const std::vector<std::int64_t>& series_ids) override
{
if(card.id != 0)
{
return std::unexpected(mw::runtimeError(
"A new card cannot already have an internal ID"));
}
if(!card.identity.game_short_name &&
(!field_values.empty() || !series_ids.empty()))
{
return std::unexpected(mw::runtimeError(
"A loose card cannot have game fields or series"));
}
auto statement = connection_.statementFromStr(
"INSERT INTO cards (creator_user_id, game_short_name, "
"card_number, name, short_description, long_description, "
"rarity, front_extension, foil_extension, "
"thumbnail_extension, revision) "
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
card.creator_user_id, card.identity.game_short_name,
static_cast<std::int64_t>(card.identity.card_number),
card.name, card.short_description, card.long_description,
card.rarity, card.front_extension, card.foil_extension,
card.thumbnail_extension, card.revision);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto inserted = connection_.execute(std::move(*statement));
if(!inserted)
{
return std::unexpected(std::move(inserted.error()));
}
const std::int64_t card_id = connection_.lastInsertRowID();
auto values = insertFieldValues(card_id, field_values);
if(!values)
{
return std::unexpected(std::move(values.error()));
}
auto memberships = insertMemberships(card_id, series_ids);
if(!memberships)
{
return std::unexpected(std::move(memberships.error()));
}
return card_id;
}
/// Replace a card and all generic custom values and memberships.
mw::E<void> updateCard(
const Card& card,
const std::vector<GameFieldValue>& field_values,
const std::vector<std::int64_t>& series_ids) override
{
if(card.id <= 0)
{
return std::unexpected(mw::runtimeError(
"An updated card requires an internal ID"));
}
if(!card.identity.game_short_name &&
(!field_values.empty() || !series_ids.empty()))
{
return std::unexpected(mw::runtimeError(
"A loose card cannot have game fields or series"));
}
auto statement = connection_.statementFromStr(
"UPDATE cards SET name = ?, short_description = ?, "
"long_description = ?, rarity = ?, front_extension = ?, "
"foil_extension = ?, thumbnail_extension = ?, revision = ? "
"WHERE id = ? AND "
"((? IS NULL AND game_short_name IS NULL) OR "
"game_short_name = ?) AND card_number = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
card.name, card.short_description, card.long_description,
card.rarity, card.front_extension, card.foil_extension,
card.thumbnail_extension, card.revision, card.id,
card.identity.game_short_name, card.identity.game_short_name,
static_cast<std::int64_t>(card.identity.card_number));
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto updated = connection_.execute(std::move(*statement));
if(!updated)
{
return std::unexpected(std::move(updated.error()));
}
if(connection_.changedRowsCount() != 1)
{
return std::unexpected(mw::runtimeError(
"The card disappeared while it was being updated"));
}
auto delete_values = deleteCardRelations(
"card_field_values", card.id);
if(!delete_values)
{
return std::unexpected(std::move(delete_values.error()));
}
auto values = insertFieldValues(card.id, field_values);
if(!values)
{
return std::unexpected(std::move(values.error()));
}
auto delete_memberships = deleteCardRelations(
"card_series", card.id);
if(!delete_memberships)
{
return std::unexpected(std::move(delete_memberships.error()));
}
return insertMemberships(card.id, series_ids);
}
/// Delete a card and its dependent database rows.
mw::E<void> deleteCard(
std::int64_t card_id) override
{
auto statement = connection_.statementFromStr(
"DELETE FROM cards WHERE id = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::int64_t>(card_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
return connection_.execute(std::move(*statement));
}
/// Insert a series and return its internal ID.
mw::E<std::int64_t> insertSeries(
const Series& series) override
{
if(series.id != 0)
{
return std::unexpected(mw::runtimeError(
"A new series cannot already have an internal ID"));
}
auto statement = connection_.statementFromStr(
"INSERT INTO series (game_short_name, name, description) "
"VALUES (?, ?, ?);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
series.game_short_name, series.name, series.description);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto inserted = connection_.execute(std::move(*statement));
if(!inserted)
{
return std::unexpected(std::move(inserted.error()));
}
return connection_.lastInsertRowID();
}
/// Replace a series name and description without changing its game.
mw::E<void> updateSeries(
const Series& series) override
{
auto statement = connection_.statementFromStr(
"UPDATE series SET name = ?, description = ? "
"WHERE id = ? AND game_short_name = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
series.name,
series.description,
series.id,
series.game_short_name);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
return connection_.execute(std::move(*statement));
}
/// Delete a series and its membership rows.
mw::E<void> deleteSeries(
std::int64_t series_id) override
{
auto statement = connection_.statementFromStr(
"DELETE FROM series WHERE id = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::int64_t>(series_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
return connection_.execute(std::move(*statement));
}
/// Commit the transaction and release its connection lock.
mw::E<void> commit() override
{
if(committed_)
{
return std::unexpected(mw::runtimeError(
"Transaction has already been committed"));
}
auto result = connection_.execute("COMMIT;");
if(!result)
{
return std::unexpected(std::move(result.error()));
}
committed_ = true;
lock_.unlock();
return {};
}
private:
mw::E<void> upsertChoice(
std::int64_t field_id, const GameChoice& choice)
{
auto statement = connection_.statementFromStr(
"INSERT INTO game_field_choices(field_id, value, position) "
"VALUES (?, ?, ?) ON CONFLICT(field_id, value) DO UPDATE "
"SET position = excluded.position;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
field_id, choice.value, choice.position);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
return connection_.execute(std::move(*statement));
}
mw::E<void> deleteChoice(
std::int64_t field_id, const std::string& value)
{
auto statement = connection_.statementFromStr(
"DELETE FROM game_field_choices "
"WHERE field_id = ? AND value = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(field_id, value);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
return connection_.execute(std::move(*statement));
}
mw::E<std::int64_t> countUsage(
const std::string& sql,
std::int64_t field_id,
const std::optional<std::string>& value)
{
auto statement = connection_.statementFromStr(sql);
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = value ? statement->bind(field_id, *value)
: statement->bind(field_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
return connection_.evalToValue<std::int64_t>(
std::move(*statement));
}
mw::E<void> deleteCardRelations(
std::string_view table, std::int64_t card_id)
{
auto statement = connection_.statementFromStr(
"DELETE FROM " + std::string(table) + " WHERE card_id = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(card_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
return connection_.execute(std::move(*statement));
}
mw::E<void> insertFieldValues(
std::int64_t card_id,
const std::vector<GameFieldValue>& field_values)
{
for(const GameFieldValue& field_value : field_values)
{
std::optional<std::int64_t> integer_value;
std::optional<std::string> string_value;
std::optional<std::string> choice_value;
if(field_value.type == GameFieldType::INTEGER)
{
const auto value = std::get_if<std::int64_t>(
&field_value.value);
if(value == nullptr)
{
return std::unexpected(mw::runtimeError(
"Integer field has a non-integer value"));
}
integer_value = *value;
}
else
{
const auto value = std::get_if<std::string>(
&field_value.value);
if(value == nullptr)
{
return std::unexpected(mw::runtimeError(
"Text field has a non-text value"));
}
if(field_value.type == GameFieldType::STRING)
{
string_value = *value;
}
else
{
choice_value = *value;
}
}
auto statement = connection_.statementFromStr(
"INSERT INTO card_field_values(card_id, field_id, "
"field_type, integer_value, string_value, choice_value) "
"VALUES (?, ?, ?, ?, ?, ?);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
card_id, field_value.field_id,
std::string(gameFieldTypeName(field_value.type)),
integer_value, string_value, choice_value);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto inserted = connection_.execute(std::move(*statement));
if(!inserted)
{
return std::unexpected(std::move(inserted.error()));
}
}
return {};
}
mw::E<void> insertMemberships(
std::int64_t card_id,
const std::vector<std::int64_t>& series_ids)
{
for(std::int64_t series_id : series_ids)
{
auto statement = connection_.statementFromStr(
"INSERT INTO card_series(card_id, series_id) "
"VALUES (?, ?);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(card_id, series_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto inserted = connection_.execute(std::move(*statement));
if(!inserted)
{
return std::unexpected(std::move(inserted.error()));
}
}
return {};
}
mw::E<std::optional<User>> readUser(mw::SQLiteStatement statement)
{
auto rows = connection_.eval<
std::int64_t,
std::string,
std::string,
std::optional<std::string>,
std::int64_t,
std::int64_t,
std::int64_t,
std::int64_t>(std::move(statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
if(rows->empty())
{
return std::optional<User>{};
}
auto& [id, email, email_key, username, role_value, stored_pulls,
refresh_day, created_at] = rows->front();
auto role = userRole(role_value);
if(!role || stored_pulls < 0 ||
stored_pulls > std::numeric_limits<std::uint32_t>::max())
{
return std::unexpected(mw::runtimeError(
"Database contains an invalid user record"));
}
return User{
id,
std::move(email),
std::move(email_key),
std::move(username),
*role,
static_cast<std::uint32_t>(stored_pulls),
refresh_day,
created_at};
}
mw::E<std::vector<Card>> readCards(const std::string& suffix)
{
auto rows = connection_.eval<
std::int64_t,
std::int64_t,
std::optional<std::string>,
std::int64_t,
std::string,
std::optional<std::string>,
std::optional<std::string>,
std::int64_t,
std::string,
std::optional<std::string>,
std::string,
std::int64_t>(
"SELECT card.id, card.creator_user_id, "
"card.game_short_name, card.card_number, card.name, "
"card.short_description, card.long_description, "
"card.rarity, card.front_extension, card.foil_extension, "
"card.thumbnail_extension, card.revision FROM cards AS card " +
suffix + ";");
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
std::vector<Card> result;
result.reserve(rows->size());
for(auto& [id, creator_user_id, game_short_name, card_number, name,
short_description, long_description, rarity,
front_extension, foil_extension, thumbnail_extension,
revision] : *rows)
{
result.push_back({
id,
{std::move(game_short_name),
static_cast<std::uint64_t>(card_number)},
std::move(name),
std::move(short_description),
std::move(long_description),
rarity,
std::move(front_extension),
std::move(foil_extension),
std::move(thumbnail_extension),
revision,
creator_user_id});
}
return result;
}
mw::E<std::int64_t> emailRetryAfter(
const std::string& email_key, std::int64_t now)
{
auto statement = connection_.statementFromStr(
"SELECT next_allowed_at FROM authentication_email_limits "
"WHERE email_key = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::string>(email_key);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto next = connection_.evalToValue<std::int64_t>(
std::move(*statement));
if(!next)
{
return std::unexpected(std::move(next.error()));
}
return std::max<std::int64_t>(1, *next - now);
}
void rollbackReservationState()
{
auto rolled_back = connection_.execute(
"ROLLBACK TO email_reservation;");
auto released = connection_.execute("RELEASE email_reservation;");
if(!rolled_back || !released)
{
spdlog::error("Failed to roll back email quota reservation");
}
}
mw::E<AuthenticationReservation> rollbackReservation(mw::Error error)
{
rollbackReservationState();
return std::unexpected(std::move(error));
}
mw::SQLite& connection_;
std::unique_lock<std::mutex> lock_;
bool committed_ = false;
};
} // namespace
DataSourceSQLite::DataSourceSQLite(
std::unique_ptr<mw::SQLite> connection)
: connection_(std::move(connection))
{}
mw::E<std::unique_ptr<DataSourceSQLite>> DataSourceSQLite::fromFile(
const std::filesystem::path& database_path)
{
auto connection = mw::SQLite::connectFile(database_path.string());
if(!connection)
{
return std::unexpected(std::move(connection.error()));
}
auto synchronous_result =
(*connection)->execute("PRAGMA synchronous = NORMAL;");
if(!synchronous_result)
{
return std::unexpected(std::move(synchronous_result.error()));
}
const std::array<std::string_view, 3> pragmas = {
"PRAGMA foreign_keys = ON;",
"PRAGMA journal_mode = WAL;",
"PRAGMA busy_timeout = 5000;",
};
for(std::string_view pragma : pragmas)
{
auto result = (*connection)->execute(std::string(pragma));
if(!result)
{
return std::unexpected(std::move(result.error()));
}
}
return std::unique_ptr<DataSourceSQLite>(
new DataSourceSQLite(std::move(*connection)));
}
mw::E<std::int64_t> DataSourceSQLite::getSchemaVersion() const
{
std::lock_guard lock(mutex_);
auto version = connection_->evalToValue<std::int64_t>(
"PRAGMA user_version;");
if(!version)
{
return std::unexpected(std::move(version.error()));
}
if(*version == 1)
{
auto current_shape = connection_->evalToValue<int>(
"SELECT EXISTS(SELECT 1 FROM sqlite_schema "
"WHERE type = 'table' AND name = 'users') AND "
"EXISTS(SELECT 1 FROM pragma_table_info('games') "
"WHERE name = 'visibility');");
if(!current_shape)
{
return std::unexpected(std::move(current_shape.error()));
}
if(*current_shape == 0)
{
return std::unexpected(mw::runtimeError(
"Database uses the obsolete prototype schema version 1; "
"delete and recreate this unreleased development database"));
}
}
return *version;
}
mw::E<void> DataSourceSQLite::migrateSchema0To1()
{
std::lock_guard lock(mutex_);
auto version = connection_->evalToValue<std::int64_t>(
"PRAGMA user_version;");
if(!version)
{
return std::unexpected(std::move(version.error()));
}
if(*version != 0)
{
return std::unexpected(mw::runtimeError(
"Schema version 0 to 1 migration requires version 0"));
}
auto begin = connection_->execute("BEGIN IMMEDIATE;");
if(!begin)
{
return std::unexpected(std::move(begin.error()));
}
for(std::string_view statement : SCHEMA_VERSION_1_STATEMENTS)
{
auto result = connection_->execute(std::string(statement));
if(!result)
{
return rollbackWithError(
*connection_, std::move(result.error()));
}
}
auto set_version = connection_->execute("PRAGMA user_version = 1;");
if(!set_version)
{
return rollbackWithError(
*connection_, std::move(set_version.error()));
}
auto commit = connection_->execute("COMMIT;");
if(!commit)
{
return rollbackWithError(*connection_, std::move(commit.error()));
}
return {};
}
mw::E<void> DataSourceSQLite::checkIntegrity() const
{
std::lock_guard lock(mutex_);
auto quick_check = connection_->eval<std::string>(
"PRAGMA quick_check;");
if(!quick_check)
{
return std::unexpected(std::move(quick_check.error()));
}
if(quick_check->size() != 1 ||
std::get<0>(quick_check->front()) != "ok")
{
return std::unexpected(mw::runtimeError(
"SQLite integrity check failed"));
}
auto foreign_key_errors = connection_->evalToValue<std::int64_t>(
"SELECT COUNT(*) FROM pragma_foreign_key_check;");
if(!foreign_key_errors)
{
return std::unexpected(std::move(foreign_key_errors.error()));
}
if(*foreign_key_errors != 0)
{
return std::unexpected(mw::runtimeError(
"Database contains foreign-key violations"));
}
auto missing_sequences = connection_->evalToValue<std::int64_t>(
"SELECT COUNT(*) FROM games AS game "
"LEFT JOIN game_sequences AS sequence "
"ON sequence.game_short_name = game.short_name "
"WHERE sequence.game_short_name IS NULL;");
if(!missing_sequences)
{
return std::unexpected(std::move(missing_sequences.error()));
}
if(*missing_sequences != 0)
{
return std::unexpected(mw::runtimeError(
"A game is missing its card-number sequence"));
}
return {};
}
mw::E<std::unique_ptr<DataSourceTransactionInterface>>
DataSourceSQLite::beginTransaction()
{
std::unique_lock lock(mutex_);
auto begin = connection_->execute("BEGIN IMMEDIATE;");
if(!begin)
{
return std::unexpected(std::move(begin.error()));
}
return std::unique_ptr<DataSourceTransactionInterface>(
new DataSourceSQLiteTransaction(*connection_, std::move(lock)));
}
mw::E<std::vector<Card>> DataSourceSQLite::getCards(
GameContentScope scope) const
{
std::lock_guard lock(mutex_);
const std::string query =
"SELECT card.id, card.creator_user_id, card.game_short_name, "
"card.card_number, card.name, card.short_description, "
"card.long_description, card.rarity, card.front_extension, "
"card.foil_extension, card.thumbnail_extension, card.revision "
"FROM cards AS card" +
(scope == GameContentScope::PUBLIC_ONLY
? std::string(" LEFT JOIN games AS game ON game.short_name = "
"card.game_short_name")
: std::string()) +
" WHERE 1 = 1" +
visibilityPredicate(scope) + " ORDER BY card.id;";
auto rows = connection_->eval<
std::int64_t,
std::int64_t,
std::optional<std::string>,
std::int64_t,
std::string,
std::optional<std::string>,
std::optional<std::string>,
std::int64_t,
std::string,
std::optional<std::string>,
std::string,
std::int64_t>(
query);
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
return cardsFromRows(std::move(*rows));
}
mw::E<std::vector<Card>> DataSourceSQLite::getCardsByCreator(
std::int64_t creator_user_id, GameContentScope scope) const
{
std::lock_guard lock(mutex_);
auto statement = connection_->statementFromStr(
"SELECT card.id, card.creator_user_id, card.game_short_name, "
"card.card_number, card.name, card.short_description, "
"card.long_description, card.rarity, card.front_extension, "
"card.foil_extension, card.thumbnail_extension, card.revision "
"FROM cards AS card" +
(scope == GameContentScope::PUBLIC_ONLY
? std::string(" LEFT JOIN games AS game ON game.short_name = "
"card.game_short_name")
: std::string()) +
" WHERE card.creator_user_id = ?" + visibilityPredicate(scope) +
" ORDER BY card.id;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::int64_t>(creator_user_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_->eval<
std::int64_t, std::int64_t, std::optional<std::string>,
std::int64_t, std::string, std::optional<std::string>,
std::optional<std::string>, std::int64_t, std::string,
std::optional<std::string>, std::string, std::int64_t>(
std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
return cardsFromRows(std::move(*rows));
}
mw::E<std::vector<Card>> DataSourceSQLite::getPoolCards() const
{
std::lock_guard lock(mutex_);
auto rows = connection_->eval<
std::int64_t, std::int64_t, std::optional<std::string>,
std::int64_t, std::string, std::optional<std::string>,
std::optional<std::string>, std::int64_t, std::string,
std::optional<std::string>, std::string, std::int64_t>(
"SELECT card.id, card.creator_user_id, card.game_short_name, "
"card.card_number, card.name, card.short_description, "
"card.long_description, card.rarity, card.front_extension, "
"card.foil_extension, card.thumbnail_extension, card.revision "
"FROM cards AS card LEFT JOIN games AS game "
"ON game.short_name = card.game_short_name "
"WHERE card.rarity > 0 AND (card.game_short_name IS NULL "
"OR game.visibility = 0) ORDER BY card.id;");
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
return cardsFromRows(std::move(*rows));
}
mw::E<std::optional<Card>> DataSourceSQLite::getCard(
const CardIdentity& identity, GameContentScope scope) const
{
std::lock_guard lock(mutex_);
auto statement = connection_->statementFromStr(
"SELECT card.id, card.creator_user_id, card.game_short_name, "
"card.card_number, card.name, card.short_description, "
"card.long_description, card.rarity, card.front_extension, "
"card.foil_extension, card.thumbnail_extension, card.revision "
"FROM cards AS card" +
(scope == GameContentScope::PUBLIC_ONLY
? std::string(" LEFT JOIN games AS game ON game.short_name = "
"card.game_short_name")
: std::string()) +
" WHERE "
"((? IS NULL AND card.game_short_name IS NULL) OR "
"card.game_short_name = ?) AND card.card_number = ?" +
visibilityPredicate(scope) + ";");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(
identity.game_short_name,
identity.game_short_name,
static_cast<std::int64_t>(identity.card_number));
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_->eval<
std::int64_t,
std::int64_t,
std::optional<std::string>,
std::int64_t,
std::string,
std::optional<std::string>,
std::optional<std::string>,
std::int64_t,
std::string,
std::optional<std::string>,
std::string,
std::int64_t>(std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
if(rows->empty())
{
return std::optional<Card>{};
}
auto& [
id,
creator_user_id,
game_short_name,
card_number,
name,
short_description,
long_description,
rarity,
front_extension,
foil_extension,
thumbnail_extension,
revision] = rows->front();
Card card = {
id,
{std::move(game_short_name),
static_cast<std::uint64_t>(card_number)},
std::move(name),
std::move(short_description),
std::move(long_description),
rarity,
std::move(front_extension),
std::move(foil_extension),
std::move(thumbnail_extension),
revision,
creator_user_id,
};
return std::optional<Card>(std::move(card));
}
mw::E<std::vector<Game>> DataSourceSQLite::getGames(
GameContentScope scope) const
{
std::lock_guard lock(mutex_);
const std::string query =
"SELECT short_name, display_name, description, visibility, revision "
"FROM games" +
(scope == GameContentScope::PUBLIC_ONLY
? std::string(" WHERE visibility = 0") : std::string()) +
" ORDER BY display_name, short_name;";
auto rows = connection_->eval<
std::string, std::string, std::string, std::int64_t, std::int64_t>(
query);
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
std::vector<Game> games;
games.reserve(rows->size());
for(auto& [short_name, display_name, description, visibility_value,
revision] : *rows)
{
auto visibility = gameVisibilityFromInteger(visibility_value);
if(!visibility)
{
return std::unexpected(std::move(visibility.error()));
}
if(revision < 1)
{
return std::unexpected(mw::runtimeError(
"Database contains an invalid game revision"));
}
games.push_back({
std::move(short_name), std::move(display_name),
std::move(description), *visibility, revision});
}
return games;
}
mw::E<std::optional<GameDefinitionSnapshot>>
DataSourceSQLite::getGameDefinition(
const std::string& short_name, GameContentScope scope) const
{
std::lock_guard lock(mutex_);
return loadGameDefinition(*connection_, short_name, scope);
}
mw::E<std::optional<CardGameFields>>
DataSourceSQLite::getCardFieldValues(
std::int64_t card_id, GameContentScope scope) const
{
std::lock_guard lock(mutex_);
auto card_statement = connection_->statementFromStr(
"SELECT card.game_short_name FROM cards AS card "
"LEFT JOIN games AS game "
"ON game.short_name = card.game_short_name "
"WHERE card.id = ?" + visibilityPredicate(scope) + ";");
if(!card_statement)
{
return std::unexpected(std::move(card_statement.error()));
}
auto card_bind = card_statement->bind(card_id);
if(!card_bind)
{
return std::unexpected(std::move(card_bind.error()));
}
auto card_rows = connection_->eval<std::optional<std::string>>(
std::move(*card_statement));
if(!card_rows)
{
return std::unexpected(std::move(card_rows.error()));
}
if(card_rows->empty() || !std::get<0>(card_rows->front()))
{
return std::optional<CardGameFields>{};
}
const std::string& short_name = *std::get<0>(card_rows->front());
auto definition = loadGameDefinition(*connection_, short_name, scope);
if(!definition)
{
return std::unexpected(std::move(definition.error()));
}
if(!*definition)
{
return std::unexpected(mw::runtimeError(
"Card references a missing game"));
}
auto value_statement = connection_->statementFromStr(
"SELECT value.field_id, value.field_type, "
"value.integer_value, value.string_value, value.choice_value "
"FROM card_field_values AS value "
"JOIN game_fields AS field ON field.id = value.field_id "
"WHERE value.card_id = ? ORDER BY field.position, field.id;");
if(!value_statement)
{
return std::unexpected(std::move(value_statement.error()));
}
auto value_bind = value_statement->bind(card_id);
if(!value_bind)
{
return std::unexpected(std::move(value_bind.error()));
}
auto value_rows = connection_->eval<
std::int64_t, std::string, std::optional<std::int64_t>,
std::optional<std::string>, std::optional<std::string>>(
std::move(*value_statement));
if(!value_rows)
{
return std::unexpected(std::move(value_rows.error()));
}
CardGameFields result{std::move(**definition), {}};
result.values.reserve(value_rows->size());
for(auto& [field_id, type_name, integer_value, string_value,
choice_value] : *value_rows)
{
auto type = parseGameFieldType(type_name);
if(!type)
{
return std::unexpected(mw::runtimeError(
"Database contains an invalid card field type"));
}
if(*type == GameFieldType::INTEGER && integer_value)
{
result.values.push_back({field_id, *type, *integer_value});
}
else if(*type == GameFieldType::STRING && string_value)
{
result.values.push_back(
{field_id, *type, std::move(*string_value)});
}
else if(*type == GameFieldType::CHOICE && choice_value)
{
result.values.push_back(
{field_id, *type, std::move(*choice_value)});
}
else
{
return std::unexpected(mw::runtimeError(
"Database contains an invalid card field value"));
}
}
return std::optional<CardGameFields>(std::move(result));
}
mw::E<std::optional<User>> DataSourceSQLite::getUser(
std::int64_t user_id) const
{
std::lock_guard lock(mutex_);
auto statement = connection_->statementFromStr(
"SELECT id, email, email_key, username, role, stored_pulls, "
"pull_refresh_day, created_at FROM users WHERE id = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::int64_t>(user_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_->eval<
std::int64_t, std::string, std::string,
std::optional<std::string>, std::int64_t, std::int64_t,
std::int64_t, std::int64_t>(std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
if(rows->empty())
{
return std::optional<User>{};
}
auto user = userFromRow(std::move(rows->front()));
if(!user)
{
return std::unexpected(std::move(user.error()));
}
return std::optional<User>(std::move(*user));
}
mw::E<std::optional<User>> DataSourceSQLite::getUserByEmailKey(
const std::string& email_key) const
{
std::lock_guard lock(mutex_);
auto statement = connection_->statementFromStr(
"SELECT id, email, email_key, username, role, stored_pulls, "
"pull_refresh_day, created_at FROM users WHERE email_key = ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::string>(email_key);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_->eval<
std::int64_t, std::string, std::string,
std::optional<std::string>, std::int64_t, std::int64_t,
std::int64_t, std::int64_t>(std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
if(rows->empty())
{
return std::optional<User>{};
}
auto user = userFromRow(std::move(rows->front()));
if(!user)
{
return std::unexpected(std::move(user.error()));
}
return std::optional<User>(std::move(*user));
}
mw::E<std::vector<User>> DataSourceSQLite::getUsers() const
{
std::lock_guard lock(mutex_);
auto rows = connection_->eval<
std::int64_t, std::string, std::string,
std::optional<std::string>, std::int64_t, std::int64_t,
std::int64_t, std::int64_t>(
"SELECT id, email, email_key, username, role, stored_pulls, "
"pull_refresh_day, created_at FROM users ORDER BY id;");
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
std::vector<User> users;
users.reserve(rows->size());
for(UserRow& row : *rows)
{
auto user = userFromRow(std::move(row));
if(!user)
{
return std::unexpected(std::move(user.error()));
}
users.push_back(std::move(*user));
}
return users;
}
mw::E<std::optional<SessionContext>> DataSourceSQLite::getSession(
const TokenHash& token_hash, std::int64_t now) const
{
auto bytes = tokenBytes(token_hash);
if(!bytes)
{
return std::unexpected(std::move(bytes.error()));
}
std::lock_guard lock(mutex_);
auto statement = connection_->statementFromStr(
"SELECT s.id, u.id, u.email, u.email_key, u.username, u.role, "
"u.stored_pulls, u.pull_refresh_day, u.created_at, s.csrf_token, "
"s.expires_at FROM sessions s JOIN users u ON u.id = s.user_id "
"WHERE s.token_hash = CAST(? AS BLOB) AND s.expires_at > ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(*bytes, now);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_->eval<
std::int64_t, std::int64_t, std::string, std::string,
std::optional<std::string>, std::int64_t, std::int64_t,
std::int64_t, std::int64_t, std::string, std::int64_t>(
std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
if(rows->empty())
{
return std::optional<SessionContext>{};
}
auto& [session_id, user_id, email, email_key, username, role_value,
stored_pulls, refresh_day, created_at, csrf_token,
expires_at] = rows->front();
auto user = userFromRow(UserRow{
user_id,
std::move(email),
std::move(email_key),
std::move(username),
role_value,
stored_pulls,
refresh_day,
created_at});
if(!user)
{
return std::unexpected(std::move(user.error()));
}
return SessionContext{
session_id, std::move(*user), std::move(csrf_token), expires_at};
}
mw::E<std::optional<AuthenticationChallenge>>
DataSourceSQLite::getAuthenticationChallenge(
const TokenHash& token_hash, std::int64_t now) const
{
auto bytes = tokenBytes(token_hash);
if(!bytes)
{
return std::unexpected(std::move(bytes.error()));
}
std::lock_guard lock(mutex_);
auto statement = connection_->statementFromStr(
"SELECT id, email, email_key, expires_at "
"FROM authentication_challenges "
"WHERE token_hash = CAST(? AS BLOB) "
"AND delivered_at IS NOT NULL AND consumed_at IS NULL "
"AND expires_at > ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(*bytes, now);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_->eval<
std::int64_t, std::string, std::string, std::int64_t>(
std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
if(rows->empty())
{
return std::optional<AuthenticationChallenge>{};
}
auto& [id, email, email_key, expires_at] = rows->front();
return AuthenticationChallenge{
id, std::move(email), std::move(email_key), expires_at};
}
mw::E<std::vector<CollectionEntry>> DataSourceSQLite::getCollection(
std::int64_t user_id, GameContentScope scope) const
{
std::lock_guard lock(mutex_);
auto statement = connection_->statementFromStr(
"SELECT c.id, c.creator_user_id, c.game_short_name, c.card_number, "
"c.name, c.short_description, c.long_description, c.rarity, "
"c.front_extension, c.foil_extension, c.thumbnail_extension, "
"c.revision, h.quantity FROM card_holdings h "
"JOIN cards c ON c.id = h.card_id "
"LEFT JOIN games AS game ON game.short_name = c.game_short_name "
"WHERE h.user_id = ?" +
(scope == GameContentScope::PUBLIC_ONLY
? std::string(
" AND (c.game_short_name IS NULL OR game.visibility = 0)")
: std::string()) +
" ORDER BY c.id;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::int64_t>(user_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_->eval<
std::int64_t, std::int64_t, std::optional<std::string>,
std::int64_t, std::string, std::optional<std::string>,
std::optional<std::string>, std::int64_t, std::string,
std::optional<std::string>, std::string, std::int64_t,
std::int64_t>(std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
std::vector<CollectionEntry> result;
result.reserve(rows->size());
for(auto& [id, creator_user_id, game_short_name, card_number, name,
short_description, long_description, rarity, front_extension,
foil_extension, thumbnail_extension, revision,
quantity] : *rows)
{
if(quantity <= 0)
{
return std::unexpected(mw::runtimeError(
"Database contains an invalid holding"));
}
auto card = cardFromRow(CardRow{
id,
creator_user_id,
std::move(game_short_name),
card_number,
std::move(name),
std::move(short_description),
std::move(long_description),
rarity,
std::move(front_extension),
std::move(foil_extension),
std::move(thumbnail_extension),
revision});
if(!card)
{
return std::unexpected(std::move(card.error()));
}
result.push_back({std::move(*card), quantity});
}
return result;
}
mw::E<bool> DataSourceSQLite::userOwnsCard(
std::int64_t user_id, std::int64_t card_id) const
{
std::lock_guard lock(mutex_);
auto statement = connection_->statementFromStr(
"SELECT EXISTS(SELECT 1 FROM card_holdings "
"WHERE user_id = ? AND card_id = ?);");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind(user_id, card_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto result = connection_->evalToValue<int>(std::move(*statement));
if(!result)
{
return std::unexpected(std::move(result.error()));
}
return *result != 0;
}
mw::E<User> DataSourceSQLite::reconcileAdministrator(
const std::string& email,
const std::string& email_key,
std::int64_t created_at,
std::int64_t pull_refresh_day)
{
std::lock_guard lock(mutex_);
const auto rollback = [this](mw::Error error) -> mw::E<User>
{
auto result = connection_->execute("ROLLBACK;");
if(!result)
{
spdlog::error(
"Failed to roll back administrator reconciliation: {}",
result.error().msg());
}
return std::unexpected(std::move(error));
};
auto begin = connection_->execute("BEGIN IMMEDIATE;");
if(!begin)
{
return std::unexpected(std::move(begin.error()));
}
auto metadata = connection_->eval<std::string>(
"SELECT value FROM application_metadata "
"WHERE key = 'administrator_email_key';");
if(!metadata)
{
return rollback(std::move(metadata.error()));
}
if(!metadata->empty() &&
std::get<0>(metadata->front()) != email_key)
{
return rollback(mw::runtimeError(
"Configured administrator email differs from the "
"initialized database"));
}
if(metadata->empty())
{
auto statement = connection_->statementFromStr(
"INSERT INTO application_metadata(key, value) "
"VALUES ('administrator_email_key', ?);");
if(!statement)
{
return rollback(std::move(statement.error()));
}
auto bind = statement->bind<std::string>(email_key);
if(!bind)
{
return rollback(std::move(bind.error()));
}
auto inserted = connection_->execute(std::move(*statement));
if(!inserted)
{
return rollback(std::move(inserted.error()));
}
}
auto administrator_rows = connection_->eval<
std::int64_t, std::string, std::string,
std::optional<std::string>, std::int64_t, std::int64_t,
std::int64_t, std::int64_t>(
"SELECT id, email, email_key, username, role, stored_pulls, "
"pull_refresh_day, created_at FROM users WHERE role = 2;");
if(!administrator_rows)
{
return rollback(std::move(administrator_rows.error()));
}
if(!administrator_rows->empty() &&
std::get<2>(administrator_rows->front()) != email_key)
{
return rollback(mw::runtimeError(
"Database administrator account has an unexpected email"));
}
if(administrator_rows->empty())
{
auto insert = connection_->statementFromStr(
"INSERT INTO users(email, email_key, username, username_key, "
"role, stored_pulls, pull_refresh_day, created_at) "
"VALUES (?, ?, NULL, NULL, 2, 1, ?, ?);");
if(!insert)
{
return rollback(std::move(insert.error()));
}
auto bind = insert->bind(
email, email_key, pull_refresh_day, created_at);
if(!bind)
{
return rollback(std::move(bind.error()));
}
auto inserted = connection_->execute(std::move(*insert));
if(!inserted)
{
return rollback(std::move(inserted.error()));
}
administrator_rows = connection_->eval<
std::int64_t, std::string, std::string,
std::optional<std::string>, std::int64_t, std::int64_t,
std::int64_t, std::int64_t>(
"SELECT id, email, email_key, username, role, stored_pulls, "
"pull_refresh_day, created_at FROM users WHERE role = 2;");
if(!administrator_rows)
{
return rollback(std::move(administrator_rows.error()));
}
}
if(administrator_rows->size() != 1)
{
return rollback(mw::runtimeError(
"Database must contain one administrator"));
}
auto administrator = userFromRow(
std::move(administrator_rows->front()));
if(!administrator)
{
return rollback(std::move(administrator.error()));
}
auto commit = connection_->execute("COMMIT;");
if(!commit)
{
return rollback(std::move(commit.error()));
}
return std::move(*administrator);
}
mw::E<void> DataSourceSQLite::cleanupAuthentication(std::int64_t now)
{
std::lock_guard lock(mutex_);
auto statement = connection_->statementFromStr(
"DELETE FROM sessions WHERE expires_at <= ?;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::int64_t>(now);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto sessions = connection_->execute(std::move(*statement));
if(!sessions)
{
return std::unexpected(std::move(sessions.error()));
}
const std::int64_t cutoff = now - 86400;
auto challenges = connection_->statementFromStr(
"DELETE FROM authentication_challenges "
"WHERE (consumed_at IS NOT NULL AND consumed_at < ?) "
"OR (consumed_at IS NULL AND expires_at < ?);");
if(!challenges)
{
return std::unexpected(std::move(challenges.error()));
}
auto challenge_bind = challenges->bind(cutoff, cutoff);
if(!challenge_bind)
{
return std::unexpected(std::move(challenge_bind.error()));
}
auto challenge_delete = connection_->execute(std::move(*challenges));
if(!challenge_delete)
{
return std::unexpected(std::move(challenge_delete.error()));
}
auto limits = connection_->statementFromStr(
"DELETE FROM authentication_email_limits "
"WHERE next_allowed_at < ?;");
if(!limits)
{
return std::unexpected(std::move(limits.error()));
}
auto limit_bind = limits->bind<std::int64_t>(cutoff);
if(!limit_bind)
{
return std::unexpected(std::move(limit_bind.error()));
}
auto limit_delete = connection_->execute(std::move(*limits));
if(!limit_delete)
{
return std::unexpected(std::move(limit_delete.error()));
}
const std::int64_t day = now >= 0 ? now / 86400 : 0;
auto quota = connection_->statementFromStr(
"DELETE FROM authentication_quota WHERE utc_day < ?;");
if(!quota)
{
return std::unexpected(std::move(quota.error()));
}
auto quota_bind = quota->bind<std::int64_t>(day - 1);
if(!quota_bind)
{
return std::unexpected(std::move(quota_bind.error()));
}
return connection_->execute(std::move(*quota));
}
mw::E<std::vector<Series>> DataSourceSQLite::getSeries(
GameContentScope scope) const
{
std::lock_guard lock(mutex_);
const std::string query =
"SELECT series.id, series.game_short_name, series.name, "
"series.description FROM series JOIN games AS game "
"ON game.short_name = series.game_short_name" +
(scope == GameContentScope::PUBLIC_ONLY
? std::string(" WHERE game.visibility = 0") : std::string()) +
" ORDER BY series.game_short_name, series.name, series.id;";
auto rows = connection_->eval<
std::int64_t,
std::string,
std::string,
std::string>(
query);
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
std::vector<Series> result;
result.reserve(rows->size());
for(auto& [id, game, name, description] : *rows)
{
result.push_back({
id,
std::move(game),
std::move(name),
std::move(description),
});
}
return result;
}
mw::E<std::optional<Series>> DataSourceSQLite::getSeries(
std::int64_t series_id, GameContentScope scope) const
{
std::lock_guard lock(mutex_);
auto statement = connection_->statementFromStr(
"SELECT series.id, series.game_short_name, series.name, "
"series.description FROM series JOIN games AS game "
"ON game.short_name = series.game_short_name "
"WHERE series.id = ?" +
(scope == GameContentScope::PUBLIC_ONLY
? std::string(" AND game.visibility = 0") : std::string()) +
";");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::int64_t>(series_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_->eval<
std::int64_t,
std::string,
std::string,
std::string>(std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
if(rows->empty())
{
return std::optional<Series>{};
}
auto& [id, game, name, description] = rows->front();
return std::optional<Series>(Series{
id,
std::move(game),
std::move(name),
std::move(description),
});
}
mw::E<std::vector<std::int64_t>> DataSourceSQLite::getCardSeries(
std::int64_t card_id) const
{
std::lock_guard lock(mutex_);
auto statement = connection_->statementFromStr(
"SELECT series_id FROM card_series "
"WHERE card_id = ? ORDER BY series_id;");
if(!statement)
{
return std::unexpected(std::move(statement.error()));
}
auto bind = statement->bind<std::int64_t>(card_id);
if(!bind)
{
return std::unexpected(std::move(bind.error()));
}
auto rows = connection_->eval<std::int64_t>(std::move(*statement));
if(!rows)
{
return std::unexpected(std::move(rows.error()));
}
std::vector<std::int64_t> result;
result.reserve(rows->size());
for(auto& [series_id] : *rows)
{
result.push_back(series_id);
}
return result;
}
mw::E<void> DataSourceSQLite::setSchemaVersion(
std::int64_t version)
{
if(version < 0)
{
return std::unexpected(mw::runtimeError(
"Database schema version cannot be negative"));
}
std::lock_guard lock(mutex_);
return connection_->execute(
"PRAGMA user_version = " + std::to_string(version) + ";");
}