SqlSchema.hpp

// SPDX-License-Identifier: Apache-2.0
 
#pragma once
 
#include "Api.hpp"
#include "SqlQuery/MigrationPlan.hpp"
 
#include <cstdint>
#include <format>
#include <functional>
#include <optional>
#include <string>
#include <string_view>
#include <tuple>
#include <vector>
 
namespace Lightweight
{
 
class SqlStatement;
 
namespace SqlSchema
{
 
    namespace detail
    {
        // NOLINTNEXTLINE(readability-identifier-naming)
        constexpr std::string_view rtrim(std::string_view value) noexcept
        {
            // Cast to unsigned char before std::isspace — see SqlConnectInfo.cpp for rationale.
            while (!value.empty() && (std::isspace(static_cast<unsigned char>(value.back())) || value.back() == '\0'))
                value.remove_suffix(1);
            return value;
        }
 
        /// The @c sys.columns size/precision metrics that qualify a Microsoft SQL Server
        /// column type. Grouped into one struct so the three same-typed integers cannot be
        /// transposed at a call site.
        struct MssqlColumnMetrics
        {
            /// @c sys.columns.max_length (bytes; @c -1 means MAX/LOB).
            int maxLength = 0;
            /// @c sys.columns.precision (for numeric/decimal/money types).
            int precision = 0;
            /// @c sys.columns.scale (for numeric/decimal/money types).
            int scale = 0;
        };
 
        /// Maps a Microsoft SQL Server system type name (from @c sys.types.name) plus the
        /// column's @c max_length / @c precision / @c scale (from @c sys.columns) to the
        /// canonical @c SqlColumnTypeDefinition variant.
        ///
        /// This reproduces, for the batched MSSQL schema reader, exactly what the legacy
        /// per-table path (@c MakeColumnTypeFromNative plus the MSSQL fixups in
        /// @c SqlSchema.cpp) yields for the same column, so backup metadata stays
        /// byte-identical.
        ///
        /// @param sysTypeName The system type name, e.g. @c "int", @c "nvarchar", @c "decimal".
        /// @param metrics     The column's @c max_length / @c precision / @c scale from @c sys.columns.
        /// @return The mapped column type variant; defaults to @c Varchar for unknown names.
        [[nodiscard]] LIGHTWEIGHT_API SqlColumnTypeDefinition MakeColumnTypeFromMssqlSysType(std::string_view sysTypeName,
                                                                                             MssqlColumnMetrics metrics);
    } // namespace detail
 
    struct FullyQualifiedTableName
    {
        std::string catalog;
        std::string schema;
        std::string table;
 
        bool operator==(FullyQualifiedTableName const& other) const noexcept
        {
            return catalog == other.catalog && schema == other.schema && table == other.table;
        }
 
        bool operator!=(FullyQualifiedTableName const& other) const noexcept
        {
            return !(*this == other);
        }
 
        bool operator<(FullyQualifiedTableName const& other) const noexcept
        {
            return std::tie(catalog, schema, table) < std::tie(other.catalog, other.schema, other.table);
        }
    };
 
    namespace detail
    {
        /// A table as the catalog (@c SQLTables) spells it.
        struct TableWithSchema
        {
            /// The schema the catalog reports the table in; empty where the DBMS has none.
            std::string schema;
            /// The table name in the catalog's own spelling.
            std::string name;
        };
 
        /// Which step of @ref ResolveTable found the table.
        enum class TableResolutionStep : std::uint8_t
        {
            /// A catalog lookup of the name exactly as requested.
            Exact,
            /// A catalog lookup of the name folded the way the DBMS folds unquoted identifiers.
            Folded,
            /// A case-insensitive scan of every table in the catalog and schema.
            FullListing,
        };
 
        /// A table found by @ref ResolveTable.
        struct ResolvedTable
        {
            /// The table in the catalog's own spelling.
            TableWithSchema table;
            /// The resolution step that found it.
            TableResolutionStep step = TableResolutionStep::Exact;
        };
 
        /// Escapes the search-pattern wildcards @c _ and @c % (and @p escape itself) in @p name, so
        /// that an ODBC catalog function taking a pattern argument matches @p name literally.
        ///
        /// @param name The literal name.
        /// @param escape The driver's @c SQL_SEARCH_PATTERN_ESCAPE string.
        /// @return The escaped pattern, or @c std::nullopt when @p escape is empty, i.e. the driver
        ///         cannot escape wildcards.
        [[nodiscard]] LIGHTWEIGHT_API std::optional<std::string> EscapeSearchPattern(std::string_view name,
                                                                                     std::string_view escape);
 
        /// Asks the catalog for the tables named @p name, rather than listing every table.
        ///
        /// The name is passed to @c SQLTables as a search pattern with its wildcards escaped (see
        /// @ref EscapeSearchPattern), so @c _ and @c % match only themselves. The DBMS's own name
        /// comparison applies: a case-insensitive collation also returns other spellings, while
        /// PostgreSQL matches the spelling exactly.
        ///
        /// @param stmt The statement to run the catalog call on.
        /// @param database The catalog; empty for the connection's default.
        /// @param schema The schema; empty for any schema.
        /// @param name The literal table name.
        /// @return The matching tables, or @c std::nullopt when the driver reports no search-pattern
        ///         escape, so the name cannot be matched literally.
        [[nodiscard]] LIGHTWEIGHT_API std::optional<std::vector<TableWithSchema>> FindTablesNamed(SqlStatement& stmt,
                                                                                                  std::string_view database,
                                                                                                  std::string_view schema,
                                                                                                  std::string_view name);
 
        /// Finds @p table in the catalog, matching its name case-insensitively.
        ///
        /// Tries, in order and stopping at the first hit: a catalog lookup of the name as given; one
        /// of the name folded as the DBMS folds unquoted identifiers (when that differs); and a scan
        /// of every table in the catalog and schema.
        ///
        /// @param stmt The statement to run the catalog calls on.
        /// @param table The table to find. An empty catalog or schema means the connection's default.
        /// @return The table as the catalog spells it, with the step that found it, or
        ///         @c std::nullopt when no such table exists.
        [[nodiscard]] LIGHTWEIGHT_API std::optional<ResolvedTable> ResolveTable(SqlStatement& stmt,
                                                                                FullyQualifiedTableName const& table);
    } // namespace detail
 
    /// Identifies a single column within a schema (catalog.schema.table.column).
    ///
    /// Schema-introspection layer only — distinct from the query-builder DSL type
    /// `Lightweight::SqlQualifiedTableColumnName`.
    struct ColumnIdentifier
    {
        /// Fully qualified table the column belongs to.
        FullyQualifiedTableName table;
        /// Column name within the table.
        std::string column;
 
        /// Equality compares both the owning table and the column name.
        bool operator==(ColumnIdentifier const& other) const noexcept
        {
            return table == other.table && column == other.column;
        }
 
        /// Inequality is the negation of equality.
        bool operator!=(ColumnIdentifier const& other) const noexcept
        {
            return !(*this == other);
        }
 
        /// Lexicographic ordering by (table, column) so ColumnIdentifier can be used as a key in ordered containers.
        bool operator<(ColumnIdentifier const& other) const noexcept
        {
            return std::tie(table, column) < std::tie(other.table, other.column);
        }
    };
 
    /// Identifies an ordered set of columns within a single table (for composite keys / indexes).
    struct ColumnIdentifierSequence
    {
        /// Fully qualified table the column sequence belongs to.
        FullyQualifiedTableName table;
        /// Ordered list of column names (order is significant for composite keys / indexes).
        std::vector<std::string> columns;
    };
 
    inline bool operator<(ColumnIdentifierSequence const& a, ColumnIdentifierSequence const& b) noexcept
    {
        return std::tie(a.table, a.columns) < std::tie(b.table, b.columns);
    }
 
    struct ForeignKeyConstraint
    {
        ColumnIdentifierSequence foreignKey;
        ColumnIdentifierSequence primaryKey;
    };
 
    inline bool operator<(ForeignKeyConstraint const& a, ForeignKeyConstraint const& b) noexcept
    {
        return std::tie(a.foreignKey, a.primaryKey) < std::tie(b.foreignKey, b.primaryKey);
    }
 
    /// Represents an index definition on a table.
    struct IndexDefinition
    {
        /// The name of the index.
        std::string name;
 
        /// The columns in the index (in order for composite indexes).
        std::vector<std::string> columns;
 
        /// Whether the index enforces uniqueness.
        bool isUnique = false;
    };
 
    using KeyPair = std::pair<FullyQualifiedTableName /*fk table*/, FullyQualifiedTableName /*pk table*/>;
 
    inline bool operator<(KeyPair const& a, KeyPair const& b)
    {
        return std::tie(a.first, a.second) < std::tie(b.first, b.second);
    }
 
    /// @ingroup CoreApi
    /// Holds the definition of a column in a SQL table as read from the database schema.
    struct Column
    {
        /// The name of the column.
        std::string name = {};
        /// The SQL column type definition.
        SqlColumnTypeDefinition type = {};
        /// The dialect-dependent type string.
        std::string dialectDependantTypeString = {};
        /// Whether the column allows NULL values.
        bool isNullable = true;
        /// Whether the column has a UNIQUE constraint.
        bool isUnique = false;
        /// The size of the column (for character/binary types).
        size_t size = 0;
        /// The number of decimal digits (for numeric types).
        unsigned short decimalDigits = 0;
        /// Whether the column auto-increments.
        bool isAutoIncrement = false;
        /// Whether the column is a primary key.
        bool isPrimaryKey = false;
        /// Whether the column is a foreign key.
        bool isForeignKey = false;
        /// The foreign key constraint, if any.
        std::optional<ForeignKeyConstraint> foreignKeyConstraint {};
        /// The default value of the column.
        std::string defaultValue = {};
    };
 
    /// Callback interface for handling events while reading a database schema.
    class EventHandler
    {
      public:
        /// Default constructor.
        EventHandler() = default;
        /// Default move constructor.
        EventHandler(EventHandler&&) = default;
        /// Default copy constructor.
        EventHandler(EventHandler const&) = default;
        /// Default move assignment operator.
        EventHandler& operator=(EventHandler&&) = default;
        /// Default copy assignment operator.
        EventHandler& operator=(EventHandler const&) = default;
        virtual ~EventHandler() = default;
 
        /// Called when the names of all tables are read.
        virtual void OnTables(std::vector<std::string> const& tables) = 0;
 
        /// Called for each table. Returns true to process this table, false to skip it.
        /// @param schema The schema the table belongs to.
        /// @param table The name of the table.
        virtual bool OnTable(std::string_view schema, std::string_view table) = 0;
        /// Called when the primary keys of a table are read.
        virtual void OnPrimaryKeys(std::string_view table, std::vector<std::string> const& columns) = 0;
        /// Called when a foreign key constraint is read.
        virtual void OnForeignKey(ForeignKeyConstraint const& foreignKeyConstraint) = 0;
        /// Called for each column in a table.
        virtual void OnColumn(Column const& column) = 0;
        /// Called when an external foreign key referencing this table is read.
        virtual void OnExternalForeignKey(ForeignKeyConstraint const& foreignKeyConstraint) = 0;
        /// Called when the indexes of a table are read.
        virtual void OnIndexes(std::vector<IndexDefinition> const& indexes) = 0;
        /// Called when a table's schema reading is complete.
        virtual void OnTableEnd() = 0;
    };
 
    /// Reads all tables in the given database and schema and calls the event handler for each table.
    ///
    /// @param stmt The SQL statement to use for reading the database schema.
    /// @param database The name of the database to read the schema from.
    /// @param schema The name of the schema to read the schema from.
    /// @param eventHandler The SAX-style event handler to call for each table.
    ///
    /// @note The event handler is called for each table in the database and schema.
    LIGHTWEIGHT_API void ReadAllTables(SqlStatement& stmt,
                                       std::string_view database,
                                       std::string_view schema,
                                       EventHandler& eventHandler);
 
    /// @ingroup CoreApi
    /// Holds the definition of a table in a SQL database as read from the database schema.
    struct Table
    {
        // FullyQualifiedTableName name;
 
        /// The schema the table belongs to.
        std::string schema;
 
        /// The name of the table.
        std::string name;
 
        /// The columns of the table.
        std::vector<Column> columns {};
 
        /// The foreign keys of the table.
        std::vector<ForeignKeyConstraint> foreignKeys {};
 
        /// The foreign keys of other tables that reference this table.
        std::vector<ForeignKeyConstraint> externalForeignKeys {};
 
        /// The primary keys of the table.
        std::vector<std::string> primaryKeys {};
 
        /// The indexes on the table (excluding primary key index).
        std::vector<IndexDefinition> indexes {};
    };
 
    /// A list of tables.
    using TableList = std::vector<Table>;
 
    using ReadAllTablesCallback = std::function<void(std::string_view /*tableName*/, size_t /*current*/, size_t /*total*/)>;
 
    /// Callback invoked when a table's schema is fully read.
    ///
    /// This callback is called for each table as soon as its schema (columns, keys, constraints)
    /// is complete. Useful for streaming tables to consumers without waiting for all tables.
    using TableReadyCallback = std::function<void(Table&&)>;
 
    /// Predicate to filter tables before reading their full schema.
    ///
    /// @param schema The schema name.
    /// @param tableName The table name.
    /// @return true to include the table (read its full schema), false to skip it.
    ///
    /// When provided, tables that don't match the predicate will have their detailed
    /// schema (columns, keys, constraints) skipped, improving performance when only
    /// a subset of tables is needed.
    using TableFilterPredicate = std::function<bool(std::string_view /*schema*/, std::string_view /*tableName*/)>;
 
    /// Retrieves all tables in the given @p database and @p schema.
    ///
    /// @param stmt The SQL statement to use for reading.
    /// @param database The database name.
    /// @param schema The schema name (optional).
    /// @param callback Progress callback invoked for each table during scanning.
    /// @param tableReadyCallback Callback invoked when each table's schema is complete.
    /// @param tableFilter Optional predicate to filter tables before reading their full schema.
    ///                    If provided, only tables where the predicate returns true will have
    ///                    their columns, keys, and constraints read.
    LIGHTWEIGHT_API TableList ReadAllTables(SqlStatement& stmt,
                                            std::string_view database,
                                            std::string_view schema = {},
                                            ReadAllTablesCallback callback = {},
                                            TableReadyCallback tableReadyCallback = {},
                                            TableFilterPredicate tableFilter = {});
 
    /// Lists the schemas that hold at least one user table, sorted by name.
    ///
    /// System schemas are left out: `sys`, `INFORMATION_SCHEMA`, `guest`, and those starting with
    /// `db_` (SQL Server roles) or `pg_` (PostgreSQL catalogs). A dialect without schemas (SQLite)
    /// yields one empty name when the database holds a table.
    ///
    /// @param stmt The SQL statement to use for reading.
    /// @param database The database name.
    /// @return The schema names.
    [[nodiscard]] LIGHTWEIGHT_API std::vector<std::string> AllUserSchemas(SqlStatement& stmt, std::string_view database);
 
    /// How much of a table's description @ref ReadTable gathers.
    enum class TableReadScope : std::uint8_t
    {
        /// Columns, primary key, foreign keys in both directions, and indexes.
        Full,
        /// Columns (with their unique, identity and primary-key flags) and the primary key only.
        /// Foreign keys, external foreign keys and indexes are left empty, and the foreign-key
        /// target casing lookups are skipped with them. Because the foreign keys are not read, the
        /// per-column @c Column::isForeignKey (false) and @c Column::foreignKeyConstraint (empty)
        /// are not populated either.
        ColumnsAndPrimaryKey,
    };
 
    /// Reads the schema of a single table.
    ///
    /// Use this instead of @ref ReadAllTables when only one table is of interest: it *describes* only
    /// that table, rather than describing every table in the database. The table list is still
    /// enumerated on each call — that is how existence is settled, and where the name casing the
    /// foreign-key fixup needs comes from — but the per-table column, key and index queries, which
    /// dominate the cost, run once instead of once per table.
    ///
    /// A missing table is reported as @c std::nullopt rather than an error, because asking whether a
    /// table exists is a normal thing to do. Everything that genuinely went wrong — a broken
    /// connection, a catalog call the driver rejected — throws @ref SqlException, as everywhere else
    /// in @ref SqlSchema. So an empty result always means "absent", never "could not tell".
    ///
    /// @param stmt The SQL statement to use for reading.
    /// @param table The fully qualified name of the table to describe. An empty catalog or schema
    ///              means "the connection's default", exactly as in @ref ReadAllTables.
    /// @param scope How much to read. @ref TableReadScope::ColumnsAndPrimaryKey skips the foreign-key
    ///              and index queries for callers that only need columns and the primary key; in
    ///              that mode @c Column::isForeignKey and @c Column::foreignKeyConstraint are not
    ///              populated on any column.
    /// @retval std::nullopt No table of that name exists in the given catalog and schema.
    /// @throws SqlException The catalog could not be read. Absence is not an error and is reported
    ///         as @c std::nullopt; anything arriving as an exception is a genuine SQL failure.
    [[nodiscard]] LIGHTWEIGHT_API std::optional<Table> ReadTable(SqlStatement& stmt,
                                                                 FullyQualifiedTableName const& table,
                                                                 TableReadScope scope = TableReadScope::Full);
 
    /// Retrieves all tables in the given database and schema that have a foreign key to the given table.
    LIGHTWEIGHT_API std::vector<ForeignKeyConstraint> AllForeignKeysTo(SqlStatement& stmt,
                                                                       FullyQualifiedTableName const& table);
 
    /// Retrieves all tables in the given database and schema that have a foreign key from the given table.
    LIGHTWEIGHT_API std::vector<ForeignKeyConstraint> AllForeignKeysFrom(SqlStatement& stmt,
                                                                         FullyQualifiedTableName const& table);
 
    /// Creats an SQL CREATE TABLE plan for the given table description.
    ///
    /// @param tableDescription The description of the table to create the plan for.
    ///
    /// @return An SQL CREATE TABLE plan for the given table description.
    LIGHTWEIGHT_API SqlCreateTablePlan MakeCreateTablePlan(Table const& tableDescription);
 
    /// Creates an SQL CREATE TABLE plan for all the given table descriptions.
    ///
    /// @param tableDescriptions The descriptions of the tables to create the plan for.
    ///
    /// @return An SQL CREATE TABLE plan for all the given table descriptions.
    LIGHTWEIGHT_API std::vector<SqlCreateTablePlan> MakeCreateTablePlan(TableList const& tableDescriptions);
 
} // namespace SqlSchema
 
} // namespace Lightweight
 
template <>
struct std::formatter<Lightweight::SqlSchema::FullyQualifiedTableName>: std::formatter<std::string>
{
    auto format(Lightweight::SqlSchema::FullyQualifiedTableName const& value, format_context& ctx) const
        -> format_context::iterator
    {
        string output = std::string(Lightweight::SqlSchema::detail::rtrim(value.schema));
        if (!output.empty())
            output += '.';
        auto const trimmedSchema = Lightweight::SqlSchema::detail::rtrim(value.catalog);
        output += trimmedSchema;
        if (!output.empty() && !trimmedSchema.empty())
            output += '.';
        output += Lightweight::SqlSchema::detail::rtrim(value.table);
        return formatter<string>::format(output, ctx);
    }
};
 
template <>
struct std::formatter<Lightweight::SqlSchema::ColumnIdentifier>: std::formatter<std::string>
{
    auto format(Lightweight::SqlSchema::ColumnIdentifier const& value, format_context& ctx) const -> format_context::iterator
    {
        auto const table = std::format("{}", value.table);
        if (table.empty())
            return formatter<string>::format(std::format("{}", value.column), ctx);
        else
            return formatter<string>::format(std::format("{}.{}", value.table, value.column), ctx);
    }
};
 
template <>
struct std::formatter<Lightweight::SqlSchema::ColumnIdentifierSequence>: std::formatter<std::string>
{
    auto format(Lightweight::SqlSchema::ColumnIdentifierSequence const& value, format_context& ctx) const
        -> format_context::iterator
    {
        auto const resolvedTableName = std::format("{}", value.table);
        string output;
        output += resolvedTableName;
        output += '(';
 
#if !defined(__cpp_lib_ranges_enumerate)
        int i { -1 };
        for (auto const& column: value.columns)
        {
            ++i;
#else
        for (auto const [i, column]: value.columns | std::views::enumerate)
        {
#endif
            if (i != 0)
                output += ", ";
            output += column;
        }
        output += ')';
 
        return formatter<string>::format(output, ctx);
    }
};