SQLiteFormatter.hpp

// SPDX-License-Identifier: Apache-2.0
#pragma once
 
#include "../SqlAdvisoryLock.hpp"
#include "../SqlQueryFormatter.hpp"
#include "../SqlRetryClassifier.hpp"
 
#include <reflection-cpp/reflection.hpp>
 
#include <algorithm>
#include <array>
#include <chrono>
#include <cstddef>
#include <format>
#include <limits>
#include <string_view>
 
namespace Lightweight
{
 
class SQLiteQueryFormatter: public SqlQueryFormatter
{
  protected:
    /// Formats a table name for use in a FROM clause.
    /// If the table name is already quoted (starts with " or [), returns it as-is.
    /// Otherwise, wraps it in double quotes.
    [[nodiscard]] static std::string FormatFromTable(std::string_view table)
    {
        if (!table.empty() && (table.front() == '"' || table.front() == '['))
            return std::string(table); // Already quoted/qualified
        return std::format(R"("{}")", table);
    }
 
  public:
    /// SQLite has no native `ALTER TABLE … ADD/DROP CONSTRAINT` and no `… ALTER COLUMN`, so foreign-key
    /// changes and column type/nullability changes route through the migration executor's table-rebuild
    /// path. The formatter signals that intent by emitting `-- LIGHTWEIGHT_SQLITE_GUARD:` sentinels and
    /// overriding this hook so the executor takes the rebuild branch instead of executing the
    /// (commented-out) sentinel script directly.
    [[nodiscard]] bool RequiresTableRebuildForSchemaChange() const noexcept override
    {
        return true;
    }
 
    [[nodiscard]] bool IdentifiersAreCaseInsensitive() const noexcept override
    {
        return true;
    }
 
    [[nodiscard]] bool ComparesFixedCharWithPadSemantics() const noexcept override
    {
        return false;
    }
 
    [[nodiscard]] bool BoundsBulkLoadTransactionSize() const noexcept override
    {
        return true;
    }
 
    [[nodiscard]] bool SupportsSchemas() const noexcept override
    {
        return false;
    }
 
    /// Builds the SQL query used to check whether a column exists on a SQLite table.
    ///
    /// The migration executor uses this to resolve the `-- LIGHTWEIGHT_SQLITE_GUARD:`
    /// sentinels emitted by @ref AlterTable for `AddColumnIfNotExists` / `DropColumnIfExists`.
    /// Keeping the pragma SQL here ensures the sentinel-emitting side and the
    /// runtime-presence-check side share a single source of truth.
    ///
    /// @param tableName Name of the table to inspect.
    /// @param columnName Name of the column whose existence to check.
    /// @return SQL string returning a single integer column: non-zero iff the column exists.
    [[nodiscard]] static std::string BuildColumnExistsQuery(std::string_view tableName, std::string_view columnName)
    {
        return std::format(R"(SELECT COUNT(*) FROM pragma_table_info('{}') WHERE name = '{}';)", tableName, columnName);
    }
 
    [[nodiscard]] std::string Insert(std::string_view intoTable,
                                     std::string_view fields,
                                     std::string_view values) const override
    {
        return std::format(R"(INSERT INTO "{}" ({}) VALUES ({}))", intoTable, fields, values);
    }
 
    [[nodiscard]] std::string Insert(std::string_view schema,
                                     std::string_view intoTable,
                                     std::string_view fields,
                                     std::string_view values) const override
    {
        // SQLite has no schemas and ignores it; a dialect that inherits this method names it.
        return std::format("INSERT INTO {} ({}) VALUES ({})",
                           FormatTableName(SupportsSchemas() ? schema : std::string_view {}, intoTable),
                           fields,
                           values);
    }
 
    [[nodiscard]] std::string QueryLastInsertId(std::string_view /*tableName*/) const override
    {
        // This is SQLite syntax. We might want to provide aspecialized SQLite class instead.
        return "SELECT LAST_INSERT_ROWID()";
    }
 
    [[nodiscard]] std::string_view BooleanLiteral(bool literalValue) const noexcept override
    {
        return literalValue ? "TRUE" : "FALSE";
    }
 
    [[nodiscard]] std::string_view DateFunction() const noexcept override
    {
        return "date()";
    }
 
    [[nodiscard]] std::string StringLiteral(std::string_view value) const noexcept override
    {
        if (value.empty())
            return "''";
 
        std::string escaped;
        escaped.reserve(value.size() + 2);
        escaped += '\'';
        for (char const c: value)
        {
            if (c == '\'')
                escaped += "''";
            else
                escaped += c;
        }
        escaped += '\'';
        return escaped;
    }
 
    [[nodiscard]] std::string StringLiteral(char value) const noexcept override
    {
        if (value == '\'')
            return "''''";
        return std::format("'{}'", value);
    }
 
    [[nodiscard]] std::string BinaryLiteral(std::span<uint8_t const> data) const override
    {
        std::string result;
        result.reserve((data.size() * 2) + 3);
        result += "X'";
        for (uint8_t byte: data)
            result += std::format("{:02X}", byte);
        result += "'";
        return result;
    }
 
    [[nodiscard]] std::string QualifiedTableName(std::string_view schema, std::string_view table) const override
    {
        // SQLite doesn't use schemas in the same way - just return the quoted table name
        if (schema.empty())
            return std::format(R"("{}")", table);
        // For SQLite attached databases, use database.table syntax
        return std::format(R"("{}"."{}")", schema, table);
    }
 
    [[nodiscard]] std::string SelectCount(bool distinct,
                                          std::string_view fromTable,
                                          std::string_view fromTableAlias,
                                          std::string_view tableJoins,
                                          std::string_view whereCondition,
                                          std::string_view groupBy) const override
    {
        auto const formattedTable = FormatFromTable(fromTable);
        if (fromTableAlias.empty())
            return std::format("SELECT{} COUNT(*) FROM {}{}{}{}",
                               distinct ? " DISTINCT" : "",
                               formattedTable,
                               tableJoins,
                               whereCondition,
                               groupBy);
        else
            return std::format(R"(SELECT{} COUNT(*) FROM {} AS "{}"{}{}{})",
                               distinct ? " DISTINCT" : "",
                               formattedTable,
                               fromTableAlias,
                               tableJoins,
                               whereCondition,
                               groupBy);
    }
 
    [[nodiscard]] std::string SelectAll(bool distinct,
                                        // NOLINTNEXTLINE(bugprone-easily-swappable-parameters)
                                        std::string_view fields,
                                        std::string_view fromTable,
                                        std::string_view fromTableAlias,
                                        std::string_view tableJoins,
                                        std::string_view whereCondition,
                                        std::string_view orderBy,
                                        std::string_view groupBy) const override
    {
        std::stringstream sqlQueryString;
        sqlQueryString << "SELECT ";
        if (distinct)
            sqlQueryString << "DISTINCT ";
        sqlQueryString << fields;
        sqlQueryString << " FROM " << FormatFromTable(fromTable);
        if (!fromTableAlias.empty())
            sqlQueryString << " AS \"" << fromTableAlias << '"';
        sqlQueryString << tableJoins;
        sqlQueryString << whereCondition;
        sqlQueryString << groupBy;
        sqlQueryString << orderBy;
 
        return sqlQueryString.str();
    }
 
    [[nodiscard]] std::string SelectFirst(bool distinct,
                                          // NOLINTNEXTLINE(bugprone-easily-swappable-parameters)
                                          std::string_view fields,
                                          std::string_view fromTable,
                                          std::string_view fromTableAlias,
                                          std::string_view tableJoins,
                                          std::string_view whereCondition,
                                          std::string_view orderBy,
                                          std::string_view groupBy,
                                          size_t count) const override
    {
        std::stringstream sqlQueryString;
        sqlQueryString << "SELECT ";
        if (distinct)
            sqlQueryString << "DISTINCT ";
        sqlQueryString << fields;
        sqlQueryString << " FROM " << FormatFromTable(fromTable);
        if (!fromTableAlias.empty())
            sqlQueryString << " AS \"" << fromTableAlias << "\"";
        sqlQueryString << tableJoins;
        sqlQueryString << whereCondition;
        sqlQueryString << groupBy;
        sqlQueryString << orderBy;
        sqlQueryString << " LIMIT " << count;
        return sqlQueryString.str();
    }
 
    [[nodiscard]] std::string SelectRange(bool distinct,
                                          // NOLINTNEXTLINE(bugprone-easily-swappable-parameters)
                                          std::string_view fields,
                                          std::string_view fromTable,
                                          std::string_view fromTableAlias,
                                          std::string_view tableJoins,
                                          std::string_view whereCondition,
                                          std::string_view orderBy,
                                          std::string_view groupBy,
                                          std::size_t offset,
                                          std::size_t limit) const override
    {
        std::stringstream sqlQueryString;
        sqlQueryString << "SELECT ";
        if (distinct)
            sqlQueryString << "DISTINCT ";
        sqlQueryString << fields;
        sqlQueryString << " FROM " << FormatFromTable(fromTable);
        if (!fromTableAlias.empty())
            sqlQueryString << " AS \"" << fromTableAlias << "\"";
        sqlQueryString << tableJoins;
        sqlQueryString << whereCondition;
        sqlQueryString << groupBy;
        sqlQueryString << orderBy;
        sqlQueryString << " LIMIT " << limit << " OFFSET " << offset;
        return sqlQueryString.str();
    }
 
    [[nodiscard]] std::string Update(std::string_view table,
                                     std::string_view tableAlias,
                                     std::string_view setFields,
                                     std::string_view whereCondition) const override
    {
        auto const formattedTable = FormatFromTable(table);
        if (tableAlias.empty())
            return std::format("UPDATE {} SET {}{}", formattedTable, setFields, whereCondition);
        else
            return std::format(R"(UPDATE {} AS "{}" SET {}{})", formattedTable, tableAlias, setFields, whereCondition);
    }
 
    [[nodiscard]] std::string Delete(std::string_view fromTable,
                                     std::string_view fromTableAlias,
                                     std::string_view tableJoins,
                                     std::string_view whereCondition) const override
    {
        auto const formattedTable = FormatFromTable(fromTable);
        if (fromTableAlias.empty())
            return std::format("DELETE FROM {}{}{}", formattedTable, tableJoins, whereCondition);
        else
            return std::format(R"(DELETE FROM {} AS "{}"{}{})", formattedTable, fromTableAlias, tableJoins, whereCondition);
    }
 
    [[nodiscard]] virtual std::string BuildColumnDefinition(SqlColumnDeclaration const& column) const
    {
        std::stringstream sqlQueryString;
 
        sqlQueryString << '"' << column.name << "\" ";
 
        if (column.primaryKey != SqlPrimaryKeyType::AUTO_INCREMENT)
            sqlQueryString << ColumnType(column.type);
        else
            sqlQueryString << ColumnType(SqlColumnTypeDefinitions::Integer {});
 
        if (column.required)
            sqlQueryString << " NOT NULL";
 
        if (column.primaryKey == SqlPrimaryKeyType::AUTO_INCREMENT)
            sqlQueryString << " PRIMARY KEY AUTOINCREMENT";
        else if (column.primaryKey == SqlPrimaryKeyType::NONE && !column.index && column.unique)
            sqlQueryString << " UNIQUE";
 
        if (!column.defaultValue.empty())
            sqlQueryString << " DEFAULT " << column.defaultValue;
 
        return sqlQueryString.str();
    }
 
    [[nodiscard]] static std::string BuildForeignKeyConstraint(std::string_view tableName,
                                                               std::string_view columnName,
                                                               SqlForeignKeyReferenceDefinition const& referencedColumn)
    {
        // Double-quote the constraint name so PostgreSQL preserves its case (otherwise
        // PG would fold to lowercase, breaking the matching `DROP CONSTRAINT "FK_<…>"`
        // emitted by `DropForeignKey`). The name is shared with the runtime rebuild
        // path (`SqliteRebuildAddForeignKey`) via `BuildForeignKeyConstraintName` so
        // CREATE-table and ALTER-table produce identical constraint names.
        return std::format(R"(CONSTRAINT "{}" FOREIGN KEY ("{}") REFERENCES "{}"("{}"))",
                           BuildForeignKeyConstraintName(tableName, std::array { columnName }),
                           columnName,
                           referencedColumn.tableName,
                           referencedColumn.columnName);
    }
 
    // NOLINTNEXTLINE(readability-function-cognitive-complexity)
    [[nodiscard]] StringList CreateTable(std::string_view schema,
                                         std::string_view tableName,
                                         std::vector<SqlColumnDeclaration> const& columns,
                                         std::vector<SqlCompositeForeignKeyConstraint> const& foreignKeys,
                                         bool ifNotExists = false) const override
    {
        auto sqlQueries = StringList {};
        // SQLite has no schemas and ignores it; a dialect that inherits this method (PostgreSQL) names it.
        auto const qualifiedTableName = FormatTableName(SupportsSchemas() ? schema : std::string_view {}, tableName);
 
        // NOLINTNEXTLINE(readability-function-cognitive-complexity)
        sqlQueries.emplace_back([&]() {
            std::stringstream sqlQueryString;
            sqlQueryString << "CREATE TABLE ";
            if (ifNotExists)
                sqlQueryString << "IF NOT EXISTS ";
            sqlQueryString << qualifiedTableName << " (";
            std::vector<SqlColumnDeclaration const*> pks;
            size_t currentColumn = 0;
            std::string foreignKeyConstraints;
            for (SqlColumnDeclaration const& column: columns)
            {
                if (currentColumn > 0)
                    sqlQueryString << ",";
                ++currentColumn;
                sqlQueryString << "\n    ";
                sqlQueryString << BuildColumnDefinition(column);
 
                if (column.primaryKey != SqlPrimaryKeyType::NONE)
                    pks.push_back(&column);
 
                if (column.foreignKey)
                {
                    foreignKeyConstraints += ",\n    ";
                    foreignKeyConstraints += BuildForeignKeyConstraint(tableName, column.name, *column.foreignKey);
                }
            }
 
            for (SqlCompositeForeignKeyConstraint const& fk: foreignKeys)
            {
                // Emit a deterministic CONSTRAINT name (`FK_<table>_<col1>_<col2>...`) so that
                // a later `DropForeignKey` / SQLite-rebuild can locate the constraint by name
                // instead of relying on the backend's auto-generated identifier (PostgreSQL
                // would otherwise pick `<table>_<col>_fkey`).
                foreignKeyConstraints += ",\n    CONSTRAINT \"";
                foreignKeyConstraints += BuildForeignKeyConstraintName(tableName, fk.columns);
                foreignKeyConstraints += "\" FOREIGN KEY (";
                for (size_t i = 0; i < fk.columns.size(); ++i)
                {
                    if (i > 0)
                        foreignKeyConstraints += ", ";
                    foreignKeyConstraints += '"' + fk.columns[i] + '"';
                }
                foreignKeyConstraints += ") REFERENCES \"";
                foreignKeyConstraints += fk.referencedTableName;
                foreignKeyConstraints += "\" (";
                for (size_t i = 0; i < fk.referencedColumns.size(); ++i)
                {
                    if (i > 0)
                        foreignKeyConstraints += ", ";
                    foreignKeyConstraints += '"' + fk.referencedColumns[i] + '"';
                }
                foreignKeyConstraints += ")";
            }
 
            // Filter out AUTO_INCREMENT from table-level PK constraint if it's the ONLY PK,
            // because SQLite handles it inline. But for composite keys involving auto-inc (if that's even valid/used),
            // or if we just want to be explicit.
            // SQLite restriction: "INTEGER PRIMARY KEY" must be on the column definition for auto-increment.
            // If we have AUTO_INCREMENT, it's already in BuildColumnDefinition.
 
            std::erase_if(
                pks, [](SqlColumnDeclaration const* col) { return col->primaryKey == SqlPrimaryKeyType::AUTO_INCREMENT; });
 
            if (!pks.empty())
            {
                std::ranges::sort(pks, [](SqlColumnDeclaration const* a, SqlColumnDeclaration const* b) {
                    // If both have index, use it. If one has 0 (no index), treat it as "after" indexed ones?
                    // Or just rely on stable sort?
                    // Let's assume indices are properly set for composite keys.
                    // 1-based index vs 0. 0 means "unordered".
                    if (a->primaryKeyIndex != 0 && b->primaryKeyIndex != 0)
                        return a->primaryKeyIndex < b->primaryKeyIndex;
                    if (a->primaryKeyIndex != 0)
                        return true; // a comes first
                    if (b->primaryKeyIndex != 0)
                        return false; // b comes first
                    return false;     // keep original order
                });
 
                sqlQueryString << ",\n    PRIMARY KEY (";
                for (size_t i = 0; i < pks.size(); ++i)
                {
                    if (i > 0)
                        sqlQueryString << ", ";
                    sqlQueryString << '"' << pks[i]->name << '"';
                }
                sqlQueryString << ")";
            }
 
            sqlQueryString << foreignKeyConstraints;
 
            sqlQueryString << "\n);";
            return sqlQueryString.str();
        }());
 
        for (SqlColumnDeclaration const& column: columns)
        {
            if (column.index && column.primaryKey == SqlPrimaryKeyType::NONE)
            {
                // primary keys are always indexed
                if (column.unique)
                    sqlQueries.emplace_back(std::format(R"(CREATE UNIQUE INDEX "{}_{}_index" ON {} ("{}");)",
                                                        tableName,
                                                        column.name,
                                                        qualifiedTableName,
                                                        column.name));
                else
                    sqlQueries.emplace_back(std::format(R"(CREATE INDEX "{}_{}_index" ON {} ("{}");)",
                                                        tableName,
                                                        column.name,
                                                        qualifiedTableName,
                                                        column.name));
            }
        }
 
        return sqlQueries;
    }
 
  private:
    /// @brief Escape an identifier for embedding inside a `"..."` field of a `LIGHTWEIGHT_SQLITE_GUARD`
    /// sentinel by doubling embedded double-quotes, so a name containing `"` cannot desync the field
    /// parsing on the executor side (which decodes `""` back to `"`).
    /// @param identifier The raw identifier (table or column name).
    /// @return The escaped text to place between the surrounding sentinel quotes.
    [[nodiscard]] static std::string EscapeSentinelField(std::string_view identifier)
    {
        std::string out;
        out.reserve(identifier.size());
        for (auto const ch: identifier)
        {
            out += ch;
            if (ch == '"')
                out += '"';
        }
        return out;
    }
 
    [[nodiscard]] std::string FormatAlterTableCommand(std::string_view tableName, SqlAlterTableCommand const& command) const
    {
        auto const formatTable = [tableName]() {
            return std::format(R"("{}")", tableName);
        };
 
        using namespace SqlAlterTableCommands;
        return std::visit(
            detail::overloaded {
                [&formatTable](RenameTable const& actualCommand) -> std::string {
                    return std::format(R"(ALTER TABLE {} RENAME TO "{}";)", formatTable(), actualCommand.newTableName);
                },
                [&formatTable, this](AddColumn const& actualCommand) -> std::string {
                    return std::format(R"(ALTER TABLE {} ADD COLUMN "{}" {} {};)",
                                       formatTable(),
                                       actualCommand.columnName,
                                       ColumnType(actualCommand.columnType),
                                       actualCommand.nullable == SqlNullable::NotNull ? "NOT NULL" : "NULL");
                },
                [&formatTable, tableName, this](AlterColumn const& actualCommand) -> std::string {
                    // SQLite has no `ALTER TABLE … ALTER COLUMN`. Route through the migration
                    // executor's table-rebuild path: emit a sentinel carrying the new column
                    // definition (type + nullability) that the executor recognises and applies by
                    // recreating the table with the modified column. The commented-out MSSQL-style
                    // ALTER below keeps dry-run output readable. Identifier fields are `""`-escaped so a
                    // name containing a double-quote cannot desync the sentinel's field parsing.
                    return std::format(
                        R"(-- LIGHTWEIGHT_SQLITE_GUARD: ALTER_COLUMN "{0}" "{1}" "{2}" "{3}"
-- ALTER TABLE {4} ALTER COLUMN "{1}" {2} {3};)",
                        EscapeSentinelField(tableName),
                        EscapeSentinelField(actualCommand.columnName),
                        ColumnType(actualCommand.columnType),
                        actualCommand.nullable == SqlNullable::NotNull ? "NOT NULL" : "NULL",
                        formatTable());
                },
                [&formatTable](RenameColumn const& actualCommand) -> std::string {
                    return std::format(R"(ALTER TABLE {} RENAME COLUMN "{}" TO "{}";)",
                                       formatTable(),
                                       actualCommand.oldColumnName,
                                       actualCommand.newColumnName);
                },
                [&formatTable](DropColumn const& actualCommand) -> std::string {
                    return std::format(R"(ALTER TABLE {} DROP COLUMN "{}";)", formatTable(), actualCommand.columnName);
                },
                [tableName](AddIndex const& actualCommand) -> std::string {
                    using namespace std::string_view_literals;
                    auto const uniqueStr = actualCommand.unique ? "UNIQUE "sv : ""sv;
                    return std::format(R"(CREATE {2}INDEX "{0}_{1}_index" ON "{0}" ("{1}");)",
                                       tableName,
                                       actualCommand.columnName,
                                       uniqueStr);
                },
                [tableName](DropIndex const& actualCommand) -> std::string {
                    return std::format(R"(DROP INDEX "{0}_{1}_index";)", tableName, actualCommand.columnName);
                },
                [tableName](AddForeignKey const& actualCommand) -> std::string {
                    // SQLite cannot `ALTER TABLE … ADD CONSTRAINT`. The runtime executor
                    // recognizes this sentinel and rebuilds the table from sqlite_schema.
                    // All metadata the executor needs fits in the sentinel itself. We keep
                    // a commented-out equivalent MSSQL-style ALTER below so dry-run output
                    // stays readable.
                    return std::format(
                        R"(-- LIGHTWEIGHT_SQLITE_GUARD: ADD_FOREIGN_KEY "{0}" "{1}" "{2}" "{3}"
-- ALTER TABLE "{0}" ADD {4};)",
                        tableName,
                        actualCommand.columnName,
                        actualCommand.referencedColumn.tableName,
                        actualCommand.referencedColumn.columnName,
                        BuildForeignKeyConstraint(tableName, actualCommand.columnName, actualCommand.referencedColumn));
                },
                [tableName](DropForeignKey const& actualCommand) -> std::string {
                    // SQLite has no `DROP CONSTRAINT`. Executor rebuilds the table without
                    // the matching `CONSTRAINT FK_<tbl>_<col>` clause.
                    return std::format(
                        R"(-- LIGHTWEIGHT_SQLITE_GUARD: DROP_FOREIGN_KEY "{0}" "{1}"
-- ALTER TABLE "{0}" DROP CONSTRAINT "{2}";)",
                        tableName,
                        actualCommand.columnName,
                        BuildForeignKeyConstraintName(tableName,
                                                      std::array { std::string_view { actualCommand.columnName } }));
                },
                [tableName](AddCompositeForeignKey const& actualCommand) -> std::string {
                    // SQLite cannot `ALTER TABLE … ADD CONSTRAINT`. Mirror the single-column
                    // AddForeignKey path: emit a sentinel the runtime executor recognises and
                    // translates into a table rebuild with the composite FK clause appended.
                    // Column tuples are encoded as comma-joined lists inside the sentinel's
                    // quoted fields; SQL identifiers in the target corpora do not contain
                    // commas so the split-on-',' parse on the runtime side is unambiguous.
                    auto const joinComma = [](std::vector<std::string> const& v) {
                        std::string out;
                        for (size_t i = 0; i < v.size(); ++i)
                        {
                            if (i != 0)
                                out += ',';
                            out += v[i];
                        }
                        return out;
                    };
                    auto const joinQuoted = [](std::vector<std::string> const& v) {
                        std::string out;
                        for (size_t i = 0; i < v.size(); ++i)
                        {
                            if (i != 0)
                                out += ", ";
                            out += '"';
                            out += v[i];
                            out += '"';
                        }
                        return out;
                    };
                    auto const fkName = BuildForeignKeyConstraintName(tableName, actualCommand.columns);
                    return std::format(
                        R"(-- LIGHTWEIGHT_SQLITE_GUARD: ADD_COMPOSITE_FOREIGN_KEY "{0}" "{1}" "{2}" "{3}"
-- ALTER TABLE "{0}" ADD CONSTRAINT "{4}" FOREIGN KEY ({5}) REFERENCES "{2}"({6});)",
                        tableName,
                        joinComma(actualCommand.columns),
                        actualCommand.referencedTableName,
                        joinComma(actualCommand.referencedColumns),
                        fkName,
                        joinQuoted(actualCommand.columns),
                        joinQuoted(actualCommand.referencedColumns));
                },
                [&formatTable, tableName, this](AddColumnIfNotExists const& actualCommand) -> std::string {
                    // SQLite doesn't support IF NOT EXISTS for ADD COLUMN.
                    // Emit a sentinel comment so the migration executor can presence-check
                    // via pragma_table_info() before running the ALTER TABLE.
                    return std::format(
                        R"(-- LIGHTWEIGHT_SQLITE_GUARD: ADD_COLUMN_IF_NOT_EXISTS "{0}" "{1}"
ALTER TABLE {2} ADD COLUMN "{1}" {3} {4};)",
                        tableName,
                        actualCommand.columnName,
                        formatTable(),
                        ColumnType(actualCommand.columnType),
                        actualCommand.nullable == SqlNullable::NotNull ? "NOT NULL" : "NULL");
                },
                [&formatTable, tableName](DropColumnIfExists const& actualCommand) -> std::string {
                    // SQLite doesn't support IF EXISTS for DROP COLUMN; guarded like above.
                    return std::format(
                        R"(-- LIGHTWEIGHT_SQLITE_GUARD: DROP_COLUMN_IF_EXISTS "{0}" "{1}"
ALTER TABLE {2} DROP COLUMN "{1}";)",
                        tableName,
                        actualCommand.columnName,
                        formatTable());
                },
                [tableName](DropIndexIfExists const& actualCommand) -> std::string {
                    return std::format(R"(DROP INDEX IF EXISTS "{0}_{1}_index";)", tableName, actualCommand.columnName);
                },
            },
            command);
    }
 
  public:
    [[nodiscard]] StringList AlterTable(std::string_view /*schemaName*/,
                                        std::string_view tableName,
                                        std::vector<SqlAlterTableCommand> const& commands) const override
    {
        // SQLite has no native IF NOT EXISTS / IF EXISTS for columns. We emit each command
        // as its own result entry; guarded commands are prefixed with a sentinel comment
        // that the migration executor recognizes and uses to perform a pragma_table_info
        // presence check at runtime.
        StringList result;
        for (SqlAlterTableCommand const& command: commands)
        {
            auto sql = FormatAlterTableCommand(tableName, command);
            if (!sql.empty())
                result.push_back(std::move(sql));
        }
        return result;
    }
 
    [[nodiscard]] std::string ColumnType(SqlColumnTypeDefinition const& type) const override
    {
        using namespace SqlColumnTypeDefinitions;
        return std::visit(detail::overloaded {
                              [](Bigint const&) -> std::string { return "BIGINT"; },
                              [](Binary const&) -> std::string { return "BLOB"; },
                              [](Bool const&) -> std::string { return "BOOLEAN"; },
                              [](Char const& type) -> std::string { return std::format("CHAR({})", type.size); },
                              [](Date const&) -> std::string { return "DATE"; },
                              [](DateTime const&) -> std::string { return "DATETIME"; },
                              [](Decimal const& type) -> std::string {
                                  return std::format("DECIMAL({}, {})", type.precision, type.scale);
                              },
                              [](Guid const&) -> std::string { return "GUID"; },
                              [](Integer const&) -> std::string { return "INTEGER"; },
                              [](NChar const& type) -> std::string { return std::format("NCHAR({})", type.size); },
                              [](NVarchar const& type) -> std::string { return std::format("NVARCHAR({})", type.size); },
                              [](Real const&) -> std::string { return "REAL"; },
                              [](Smallint const&) -> std::string { return "SMALLINT"; },
                              [](Text const&) -> std::string { return "TEXT"; },
                              [](Time const&) -> std::string { return "TIME"; },
                              [](Timestamp const&) -> std::string { return "TIMESTAMP"; },
                              [](Tinyint const&) -> std::string { return "TINYINT"; },
                              [](VarBinary const& type) -> std::string { return std::format("VARBINARY({})", type.size); },
                              [](Varchar const& type) -> std::string { return std::format("VARCHAR({})", type.size); },
                          },
                          type);
    }
 
    [[nodiscard]] StringList DropTable(std::string_view /*schemaName*/,
                                       std::string_view const& tableName,
                                       bool ifExists = false,
                                       bool cascade = false) const override
    {
        // SQLite doesn't support CASCADE syntax, but if FK constraints are disabled
        // (PRAGMA foreign_keys = OFF), dropping works. The cascade flag is ignored.
        // SQLite doesn't support schemas - ignore schemaName parameter
        (void) cascade;
        if (ifExists)
            return { std::format(R"(DROP TABLE IF EXISTS "{}";)", tableName) };
        else
            return { std::format(R"(DROP TABLE "{}";)", tableName) };
    }
 
    [[nodiscard]] std::string QueryServerVersion() const override
    {
        return "SELECT sqlite_version()";
    }
 
    /// Emits @c PRAGMA @c busy_timeout, followed by @c PRAGMA @c journal_mode unless the journal mode
    /// is to stay unchanged. The busy timeout goes first so that the journal-mode switch, which needs an
    /// exclusive lock on the database file, already waits on a contended file instead of failing.
    [[nodiscard]] StringList SqliteSettingsStatements(SqliteConnectionSettings const& settings) const override
    {
        // PRAGMA busy_timeout takes a C int; clamp so an out-of-range duration cannot wrap around.
        auto const busyTimeoutMs =
            std::clamp<std::chrono::milliseconds::rep>(settings.busyTimeout.count(), 0, std::numeric_limits<int>::max());
 
        auto statements = StringList { std::format("PRAGMA busy_timeout = {}", busyTimeoutMs) };
        if (auto const mode = JournalModeKeyword(settings.journalMode); !mode.empty())
            statements.emplace_back(std::format("PRAGMA journal_mode = {}", mode));
        return statements;
    }
 
    /// Maps a journal mode onto its @c PRAGMA @c journal_mode keyword.
    /// @param mode The journal mode.
    /// @return The keyword, or an empty view for @c SqliteJournalMode::Unchanged.
    [[nodiscard]] static constexpr std::string_view JournalModeKeyword(SqliteJournalMode mode) noexcept
    {
        // Indexed by the enumerator value, in declaration order.
        constexpr auto keywords = std::array<std::string_view, 7> {
            "", "DELETE", "TRUNCATE", "PERSIST", "MEMORY", "WAL", "OFF",
        };
        auto const index = static_cast<std::size_t>(mode);
        return index < keywords.size() ? keywords[index] : std::string_view {};
    }
 
    /// SQLite has no native advisory-lock primitive, so the handler maintains
    /// a `_lightweight_locks` table guarded by a unique constraint. The
    /// override is intentionally inline-delegating: putting the body in
    /// `SqlQueryFormatter.cpp` would have made it the class's *key function*
    /// and forced the vtable into a single TU — fine on Windows, but on
    /// Linux with `CXX_VISIBILITY_PRESET=hidden` + `LIGHTWEIGHT_BUILD_SHARED=ON`
    /// the hidden vtable would fail to link from consumers of `Lightweight`.
    /// Inline + free-function delegation keeps the vtable weak.
    [[nodiscard]] SqlAdvisoryLockHandler const& AdvisoryLockOps() const override
    {
        return SqliteAdvisoryLockOps();
    }
 
    /// SQLite reports contention through the driver's message text rather than a SQLSTATE.
    /// Delegates to the free function for the same reason `AdvisoryLockOps()` does.
    [[nodiscard]] SqlRetryClassifier const& RetryOps() const noexcept override
    {
        return SqliteRetryOps();
    }
};
 
} // namespace Lightweight