SqlConnection.hpp

// SPDX-License-Identifier: Apache-2.0
 
#pragma once
 
// See SqlOdbcPrelude.hpp's header comment for why this replaces a direct <Windows.h> include.
#include "Api.hpp"
#include "Async/Fwd.hpp"
#include "SqlConnectInfo.hpp"
#include "SqlError.hpp"
#include "SqlLogger.hpp"
#include "SqlOdbcPrelude.hpp"
#include "SqlRetryPolicy.hpp"
#include "SqlServerType.hpp"
 
#include <atomic>
#include <chrono>
#include <cstdint>
#include <expected>
#include <format>
#include <functional>
#include <memory>
#include <optional>
#include <string>
#include <string_view>
#include <system_error>
 
#include <sql.h>
#include <sqlext.h>
#include <sqlspi.h>
#include <sqltypes.h>
 
/// @defgroup CoreApi Core API
/// @brief The low-level SQL API: connections, statements, result cursors and transactions.
///
/// This is the thin layer directly over ODBC. Use it when you want to write SQL yourself and
/// keep full control over binding and fetching. The higher-level @ref DataMapper and
/// @ref QueryBuilder layers are built on top of these types and interoperate with them.
 
namespace Lightweight
{
 
class SqlQueryBuilder;
class SqlMigrationQueryBuilder;
class SqlQueryFormatter;
class SqlPreparedStatementCache;
 
/// @ingroup CoreApi
/// @brief How a connection treats a string or binary value written into a column too small to hold it.
///
/// The mode is applied per connection. On Microsoft SQL Server it is realised by toggling the
/// session's @c ANSI_WARNINGS setting when the connection is established.
enum class SqlStringTruncationMode : uint8_t
{
    /// The value is silently shortened to the column width (@c SET @c ANSI_WARNINGS @c OFF on SQL
    /// Server). This matches the historical behaviour of some legacy data layers that clamp a value to
    /// the column width before sending it.
    ///
    /// @warning On SQL Server this is @c ANSI_WARNINGS @c OFF, which also turns an arithmetic overflow
    ///          into a NULL rather than an error and suppresses the NULL-in-aggregate warning, and
    ///          makes INSERT/UPDATE fail on tables carrying an indexed view, a filtered index, or an
    ///          index over a computed column. Prefer @ref Error unless bug-for-bug compatibility with
    ///          such a legacy layer is required.
    Truncate,
 
    /// The server rejects the write with an error (@c SET @c ANSI_WARNINGS @c ON on SQL Server, which
    /// is also the server default). No data is silently lost.
    Error,
};
 
/// @ingroup CoreApi
/// @brief The SQLite journal mode a connection requests when it is established.
///
/// Each enumerator other than @ref Unchanged maps onto the matching @c PRAGMA @c journal_mode value.
/// See https://sqlite.org/pragma.html#pragma_journal_mode for the semantics of each mode.
///
/// @note SQLite may decline a requested mode without reporting an error, e.g. an in-memory database
///       can only use @c MEMORY or @c OFF, so asking it for @ref Wal leaves it in @c MEMORY.
enum class SqliteJournalMode : uint8_t
{
    /// Leave the journal mode alone: whatever the database file (or the driver) already uses stays in
    /// effect. This is the default, so a connection that does not opt in behaves exactly as before.
    Unchanged,
 
    /// @c PRAGMA @c journal_mode=DELETE — the rollback journal, deleted at the end of each transaction
    /// (SQLite's own default).
    Delete,
 
    /// @c PRAGMA @c journal_mode=TRUNCATE — the rollback journal, truncated rather than deleted.
    Truncate,
 
    /// @c PRAGMA @c journal_mode=PERSIST — the rollback journal, kept and its header zeroed.
    Persist,
 
    /// @c PRAGMA @c journal_mode=MEMORY — the rollback journal, kept in memory.
    Memory,
 
    /// @c PRAGMA @c journal_mode=WAL — write-ahead logging, in which readers and writers do not block
    /// each other. The mode is persistent: it is recorded in the database file and stays in effect for
    /// every later connection to it. Requires a file database.
    Wal,
 
    /// @c PRAGMA @c journal_mode=OFF — no rollback journal; a crash or ROLLBACK can corrupt the
    /// database.
    Off,
};
 
/// @brief Default SQLite busy timeout: how long a statement waits on a database file locked by another
/// connection before failing with "database is locked".
inline constexpr std::chrono::milliseconds SqliteBusyTimeoutDefault { 60'000 };
 
/// @ingroup CoreApi
/// @brief Connection-level settings applied when a connection to a SQLite database is established.
///
/// Inert on every other backend: no statement is issued for them there. The defaults reproduce the
/// behaviour Lightweight has always had, a 60 second busy timeout and an untouched journal mode.
///
/// @see SqlConnection::SetDefaultSqliteSettings, SqlConnection::SetSqliteSettings
struct SqliteConnectionSettings
{
    /// @brief How long a statement waits on a locked database file before failing, applied via
    /// @c PRAGMA @c busy_timeout.
    ///
    /// Zero disables the wait, so a contended statement fails at once; a negative value is treated as
    /// zero. Not to be confused with the ODBC login timeout of the connection string.
    std::chrono::milliseconds busyTimeout = SqliteBusyTimeoutDefault;
 
    /// The journal mode to switch the database to, see @ref SqliteJournalMode.
    SqliteJournalMode journalMode = SqliteJournalMode::Unchanged;
 
    /// Equality comparison operator.
    bool operator==(SqliteConnectionSettings const&) const noexcept = default;
};
 
/// @ingroup CoreApi
/// @brief How a connection relates to its ODBC handles.
enum class SqlHandleOwnership : uint8_t
{
    /// The connection allocated its handles, connects them and frees them on Close().
    Owned,
 
    /// Another component allocated and connected the handle and keeps owning it; the connection only
    /// runs statements on it (see SqlConnection::Borrow).
    Borrowed,
};
 
/// @ingroup CoreApi
/// @brief Represents a connection to a SQL database.
class SqlConnection final
{
  public:
    /// @brief Constructs a new SQL connection to the default connection.
    ///
    /// The default connection is set via SetDefaultConnectInfo.
    /// In case the default connection is not set, the connection will fail.
    /// And in case the connection fails, the last error will be set.
    LIGHTWEIGHT_API SqlConnection();
 
    /// @brief Constructs a new SQL connection to the given connect informaton.
    ///
    /// @param connectInfo The connection information to use. If `std::nullopt`,
    ///                    no connection is established and the object is left
    ///                    in an unconnected state — use `Connect(...)` later.
    ///                    If a value is provided and the connection fails,
    ///                    `SqlException` is thrown carrying the ODBC diagnostic
    ///                    read from the DBC handle.
    LIGHTWEIGHT_API explicit SqlConnection(std::optional<SqlConnectionString> connectInfo);
 
    /// @brief Constructs a new SQL connection with explicit SQLite connection settings.
    ///
    /// Behaves like the single-argument constructor, except that the connection uses
    /// @p sqliteSettings instead of a snapshot of @ref DefaultSqliteSettings. The settings are applied
    /// as part of establishing the connection, so the first statement already runs under them.
    ///
    /// @param connectInfo The connection information to use, or `std::nullopt` to stay unconnected.
    /// @param sqliteSettings The SQLite settings applied on this and every later Connect(); inert on
    ///                       other backends.
    LIGHTWEIGHT_API SqlConnection(std::optional<SqlConnectionString> connectInfo, SqliteConnectionSettings sqliteSettings);
 
    /// Move constructor.
    LIGHTWEIGHT_API SqlConnection(SqlConnection&& /*other*/) noexcept;
    /// Move assignment operator.
    LIGHTWEIGHT_API SqlConnection& operator=(SqlConnection&& /*other*/) noexcept;
    SqlConnection(SqlConnection const& /*other*/) = delete;
    SqlConnection& operator=(SqlConnection const& /*other*/) = delete;
 
    /// Destructs this SQL connection object,
    LIGHTWEIGHT_API ~SqlConnection() noexcept;
 
    /// @brief Wraps an ODBC connection handle that another component allocated, connected and keeps owning.
    ///
    /// Statements run on the borrowed connection share the owner's session, including any transaction
    /// it has open. The server type, query formatter and driver name are detected as Connect() does,
    /// but no session setting is applied: neither the string-truncation mode, nor the SQLite settings,
    /// nor the post-connected hook. StringTruncationMode() reports the mode the session already has
    /// (read from @c ANSI_WARNINGS on Microsoft SQL Server). Close() and the destructor detach from the
    /// handle without disconnecting or freeing it, and Connect() on a borrowed connection fails.
    ///
    /// @param connectedHandle A connected ODBC connection handle. It must stay valid until the returned
    ///                        connection is closed or destroyed, since both free the statement handles
    ///                        cached on it.
    /// @return A connection running on @p connectedHandle.
    /// @throws std::invalid_argument if @p connectedHandle is null.
    [[nodiscard]] LIGHTWEIGHT_API static SqlConnection Borrow(SQLHDBC connectedHandle);
 
    /// @brief Tells whether this connection owns its ODBC handles or borrowed them (see @ref Borrow).
    /// @return @ref SqlHandleOwnership::Borrowed for a connection made by @ref Borrow, else
    ///         @ref SqlHandleOwnership::Owned.
    [[nodiscard]] LIGHTWEIGHT_API SqlHandleOwnership HandleOwnership() const noexcept;
 
    /// Retrieves the default connection information.
    ///
    /// Thread-safe: the reference is to the calling thread's own snapshot of the default, refreshed
    /// when the application has replaced it since (see @ref SetDefaultConnectionString), so another
    /// thread replacing the default never changes the string under a reader. The same thread's next
    /// call may, so copy it if it must stay fixed across such a switch.
    ///
    /// @return The calling thread's snapshot of the current default connection string.
    [[nodiscard]] LIGHTWEIGHT_API static SqlConnectionString const& DefaultConnectionString();
 
    /// Identifies the current default connection string.
    ///
    /// Incremented by every @ref SetDefaultConnectionString and @ref SetDefaultDataSource, so anything
    /// that caches a connection made from the default - a connection pool, the migration manager's
    /// mapper - can tell that it now points at a database the application has switched away from.
    /// Compare for equality only; the counter wraps.
    ///
    /// Read it *before* reading @ref DefaultConnectionString(), since the setters publish the new string
    /// first and the new generation second, so a connection stamped that way can only ever be
    /// considered older than it is, never newer.
    ///
    /// @return The generation of the current default connection string.
    [[nodiscard]] LIGHTWEIGHT_API static std::uint32_t DefaultConnectionStringGeneration() noexcept;
 
    /// Sets the default connection information.
    ///
    /// Thread-safe. Connections already made from the previous default are not touched; pooled ones
    /// are retired as they are next borrowed or returned (see @ref DefaultConnectionStringGeneration).
    ///
    /// @param connectionString The connection information to use.
    LIGHTWEIGHT_API static void SetDefaultConnectionString(SqlConnectionString const& connectionString);
 
    /// Sets the default connection information as SqlConnectionDataSource.
    ///
    /// @param dataSource The data source to flatten into the default connection string.
    /// @see SetDefaultConnectionString
    LIGHTWEIGHT_API static void SetDefaultDataSource(SqlConnectionDataSource const& dataSource);
 
    /// Sets a callback to be called after each connection being established.
    LIGHTWEIGHT_API static void SetPostConnectedHook(std::function<void(SqlConnection&)> hook);
 
    /// Resets the post connected hook.
    LIGHTWEIGHT_API static void ResetPostConnectedHook();
 
    /// The string-truncation mode a newly established connection adopts. Defaults to
    /// @ref SqlStringTruncationMode::Truncate.
    [[nodiscard]] LIGHTWEIGHT_API static SqlStringTruncationMode DefaultStringTruncationMode() noexcept;
 
    /// Sets the string-truncation mode newly established connections adopt. Existing connections are
    /// unaffected; use @ref SetStringTruncationMode on those.
    LIGHTWEIGHT_API static void SetDefaultStringTruncationMode(SqlStringTruncationMode mode) noexcept;
 
    /// This connection's string-truncation mode. See @ref SqlStringTruncationMode.
    [[nodiscard]] SqlStringTruncationMode StringTruncationMode() const noexcept
    {
        return m_stringTruncationMode;
    }
 
    /// Sets this connection's string-truncation mode, applying it immediately when the connection is
    /// open (a no-op on backends other than Microsoft SQL Server).
    LIGHTWEIGHT_API void SetStringTruncationMode(SqlStringTruncationMode mode);
 
    /// @brief The SQLite settings a newly constructed connection adopts.
    ///
    /// Thread-safe. Defaults to a default-constructed @ref SqliteConnectionSettings (60 second busy
    /// timeout, journal mode unchanged).
    ///
    /// @return A copy of the current process-wide default.
    [[nodiscard]] LIGHTWEIGHT_API static SqliteConnectionSettings DefaultSqliteSettings();
 
    /// @brief Sets the SQLite settings newly constructed connections adopt.
    ///
    /// Thread-safe. This is the way to configure connections that Lightweight constructs on the
    /// application's behalf, such as those of a @c Pool or a default-constructed @c DataMapper.
    /// Connections that already exist keep their settings; use @ref SetSqliteSettings on those.
    ///
    /// @param settings The new process-wide default.
    LIGHTWEIGHT_API static void SetDefaultSqliteSettings(SqliteConnectionSettings const& settings);
 
    /// @brief This connection's SQLite settings.
    ///
    /// Captured from @ref DefaultSqliteSettings when the connection object is constructed (or given to
    /// the constructor), and re-applied on every Connect().
    ///
    /// @return The settings this connection applies when connected to SQLite.
    [[nodiscard]] LIGHTWEIGHT_API SqliteConnectionSettings const& SqliteSettings() const noexcept;
 
    /// @brief Changes this connection's SQLite settings, applying them immediately when the connection
    /// is open to a SQLite database.
    ///
    /// On other backends, or while unconnected, the settings are only stored, to be applied by the next
    /// Connect() to a SQLite database.
    ///
    /// @param settings The settings to store and apply.
    /// @return Nothing on success, or the diagnostic of the @c PRAGMA statement that failed. The
    ///         settings are stored either way.
    [[nodiscard]] LIGHTWEIGHT_API std::expected<void, SqlErrorInfo> SetSqliteSettings(
        SqliteConnectionSettings const& settings);
 
    /// @brief The connect-retry settings a newly constructed connection adopts.
    ///
    /// Thread-safe. Defaults to no retries (@c maxRetries of zero), so a failed connect fails at once
    /// unless the application opts in.
    ///
    /// @return A copy of the current process-wide default.
    [[nodiscard]] LIGHTWEIGHT_API static SqlRetrySettings DefaultConnectRetrySettings();
 
    /// @brief Sets the connect-retry settings newly constructed connections adopt.
    ///
    /// Thread-safe. This is the way to make the connections that Lightweight opens on the application's
    /// behalf (a @c Pool, a default-constructed @c DataMapper, migrations, backups) ride out a database
    /// that is briefly unreachable. Connections that already exist keep their settings; use
    /// @ref SetConnectRetrySettings on those.
    ///
    /// @param settings The new process-wide default.
    LIGHTWEIGHT_API static void SetDefaultConnectRetrySettings(SqlRetrySettings const& settings);
 
    /// @brief This connection's connect-retry settings.
    ///
    /// Captured from @ref DefaultConnectRetrySettings when the connection object is constructed, and
    /// applied by every Connect().
    ///
    /// @return The backoff configuration Connect() retries with.
    [[nodiscard]] LIGHTWEIGHT_API SqlRetrySettings const& ConnectRetrySettings() const noexcept;
 
    /// @brief Changes how this connection's Connect() retries a connect that failed transiently.
    ///
    /// Connect() retries only failures that the dialect-agnostic classifier (@ref GenericRetryOps)
    /// deems transient — a connection that could not be established or was dropped (SQLSTATE class
    /// @c 08), a timeout (@c HYT00 / @c HYT01) — and fails at once on anything else, such as rejected
    /// credentials. Each retry is reported through @ref SqlLogger::OnWarning.
    ///
    /// @param settings The backoff configuration; a @c maxRetries of zero disables retrying.
    /// @param sleeper How to wait between attempts; @c nullptr selects @ref ThreadSleeper(). It must
    ///                outlive the connection. Injected so tests need not wait out real delays.
    LIGHTWEIGHT_API void SetConnectRetrySettings(SqlRetrySettings const& settings,
                                                 SqlRetrySleeper* sleeper = nullptr) noexcept;
 
    /// @brief Retrieves the connection ID.
    ///
    /// This is a unique identifier for the connection, which is useful for debugging purposes.
    /// Note, this ID will not change if the connection is moved nor when it is reused via the connection pool.
    [[nodiscard]] uint64_t ConnectionId() const noexcept
    {
        return m_connectionId;
    }
 
    /// Closes the connection, freeing the underlying ODBC handles.
    ///
    /// A borrowed connection (see @ref Borrow) only detaches from its handle, leaving it connected.
    ///
    /// This does not return the connection to any pool: `SqlConnection` owns its handles outright.
    /// Connection pooling is provided separately by `Lightweight::Pool` in
    /// `DataMapper/Pool.hpp`, which recycles pooled `DataMapper` instances.
    LIGHTWEIGHT_API void Close() noexcept;
 
    /// Connects to the given database with the given username and password.
    ///
    /// This method can be called on a connection that has been closed via Close().
    /// If the ODBC handles have been freed, they will be automatically reallocated.
    /// A transient failure is retried as @ref SetConnectRetrySettings configures.
    /// It always fails on a borrowed connection (see @ref Borrow).
    ///
    /// @retval true if the connection was successful.
    /// @retval false if the connection failed. Use LastError() to retrieve the error information.
    LIGHTWEIGHT_API bool Connect(SqlConnectionDataSource const& info) noexcept;
 
    /// Connects to the given database with the given connection string.
    ///
    /// This method can be called on a connection that has been closed via Close().
    /// If the ODBC handles have been freed, they will be automatically reallocated.
    /// A transient failure is retried as @ref SetConnectRetrySettings configures.
    /// It always fails on a borrowed connection (see @ref Borrow).
    ///
    /// @retval true if the connection was successful.
    /// @retval false if the connection failed. Use LastError() to retrieve the error information.
    LIGHTWEIGHT_API bool Connect(SqlConnectionString sqlConnectionString) noexcept;
 
    /// Retrieves the last error information with respect to this SQL connection handle.
    [[nodiscard]] LIGHTWEIGHT_API SqlErrorInfo LastError() const;
 
    /// Retrieves the name of the database in use.
    [[nodiscard]] LIGHTWEIGHT_API std::string DatabaseName() const;
 
    /// Retrieves the name of the user.
    [[nodiscard]] LIGHTWEIGHT_API std::string UserName() const;
 
    /// Retrieves the name of the server.
    [[nodiscard]] LIGHTWEIGHT_API std::string ServerName() const;
 
    /// Retrieves the reported server version.
    [[nodiscard]] LIGHTWEIGHT_API std::string ServerVersion() const;
 
    /// Retrieves the type of the server.
    [[nodiscard]] SqlServerType ServerType() const noexcept;
 
    /// Retrieves the name of the driver used for this connection.
    [[nodiscard]] std::string const& DriverName() const noexcept;
 
    /// Tells whether text reaches the driver intact through the ODBC wide (W) entry points.
    ///
    /// Off Windows, a driver that implements only the ANSI entry points (such as the SQLite ODBC
    /// driver some Linux distributions ship) receives wide text from the driver manager narrowed one
    /// UTF-16 code unit to one byte, which corrupts every non-ASCII character. For such a driver the
    /// library hands over query text, catalog-function arguments and connection text as UTF-8
    /// through the ANSI entry points, and reads its diagnostics through them. Always true on Windows,
    /// whose driver manager converts wide text to the code page an ANSI driver expects.
    [[nodiscard]] bool AcceptsWideQueryText() const noexcept;
 
    /// Retrieves a query formatter suitable for the SQL server being connected.
    [[nodiscard]] SqlQueryFormatter const& QueryFormatter() const noexcept;
 
    /// @brief Whether this backend must rebuild a table to apply an `ALTER TABLE` schema change it
    /// cannot express in place (foreign-key add/drop, or column type/nullability change).
    ///
    /// The migration executor consults this to decide whether a `-- LIGHTWEIGHT_SQLITE_GUARD:` sentinel
    /// must be turned into a table rebuild (`true` for SQLite) or executed directly. Exposed on the
    /// connection so capability decisions go through the backend, delegating the dialect knowledge to
    /// the query formatter.
    [[nodiscard]] LIGHTWEIGHT_API bool RequiresTableRebuildForSchemaChange() const noexcept;
 
    /// @brief Whether this connection's ODBC driver supports native parameter-array binding
    /// (`SQL_ATTR_PARAMSET_SIZE` > 1) for batched row-wise execution.
    ///
    /// The batched `SqlStatement::ExecuteBatch(rows, accessors...)` consults this to decide whether it
    /// may submit the whole batch in a single row-wise `SQLExecute`, or must fall back to a single
    /// prepare followed by consecutive per-row executes. This is a driver/backend capability — not a
    /// SQL-dialect concern — so it belongs to the connection, which knows both the server type and the
    /// driver name (either of which a future carve-out can branch on).
    ///
    /// @return `true` if the driver honours parameter arrays.
    [[nodiscard]] bool SupportsNativeRowBatch() const noexcept;
 
    /// @brief Whether this connection's ODBC driver supports native row-array fetching
    /// (`SQL_ATTR_ROW_ARRAY_SIZE` > 1 with `SQLFetchScroll`) for block result retrieval.
    ///
    /// The fast retrieval path in @c SqlStatement::FetchAllRowWise consults this to decide whether
    /// it may bind the result columns row-wise over a record block and materialize whole row blocks per
    /// `SQLFetchScroll` round-trip, or must fall back to the per-row `SQLFetch` path. Like
    /// @ref SupportsNativeRowBatch this is a driver/backend capability — not a SQL-dialect concern — so
    /// it lives on the connection. Kept distinct from the parameter-array flag so a backend that honours
    /// one but not the other can be carved out independently.
    ///
    /// @return `true` if the driver honours row-array fetching.
    [[nodiscard]] bool SupportsNativeRowArrayFetch() const noexcept;
 
    /// @brief Server-type overload of @ref SupportsNativeRowArrayFetch, for callers that hold only the
    /// server type (e.g. the DataMapper result reader) and not the connection. Keeps the single source of
    /// truth for this capability on the connection rather than scattering a `switch (serverType)` into
    /// business logic.
    /// @param serverType The backend server type to test.
    /// @return `true` if that backend honours row-array fetching.
    [[nodiscard]] static bool SupportsNativeRowArrayFetch(SqlServerType serverType) noexcept;
 
    /// @brief Whether @p serverType's driver round-trips narrow (@c SQL_C_CHAR) character data
    /// byte-exact, so a fixed-capacity char string may be array-bound narrow on the row-wise fetch path.
    ///
    /// PostgreSQL's psqlODBC transcodes @c SQL_C_CHAR through the client codepage (cp1252 on Windows),
    /// mangling non-ASCII bytes — its single-row binder therefore reads narrow strings via @c SQL_C_WCHAR.
    /// That wide round-trip needs an external per-cell buffer + conversion, which cannot be expressed as an
    /// in-place row-wise array bind, so a record carrying a fixed-capacity string falls back to the per-row
    /// path on PostgreSQL. MS SQL Server and SQLite read @c SQL_C_CHAR verbatim and stay on the fast path.
    ///
    /// @param serverType The backend server type to test.
    /// @return `true` if narrow character data round-trips byte-exact on that backend.
    [[nodiscard]] static bool RoundTripsNarrowTextByteExact(SqlServerType serverType) noexcept;
 
    /// @brief The default block-prefetch depth applied to statements created on this connection.
    ///
    /// Classic per-row fetch loops (`while (cursor.FetchRow()) ...`, @c SqlRowIterator,
    /// @c SqlVariantRowCursor) transparently request this many rows per @c SQLFetchScroll round-trip
    /// instead of issuing one @c SQLFetch per row. A value <= 1 disables prefetch. Effective only when
    /// @ref SupportsNativeRowArrayFetch is true; otherwise statements fall back to per-row fetching.
    ///
    /// @return The configured default prefetch depth (defaults to @c PrefetchDepthDefault).
    [[nodiscard]] LIGHTWEIGHT_API std::size_t DefaultPrefetchDepth() const noexcept;
 
    /// @brief Sets the default block-prefetch depth for statements created on this connection.
    ///
    /// @param depth Rows to request per @c SQLFetchScroll round-trip on the transparent prefetch path;
    ///              a value <= 1 disables prefetch (restoring one @c SQLFetch per row).
    LIGHTWEIGHT_API void SetDefaultPrefetchDepth(std::size_t depth) noexcept;
 
    /// @brief Whether this connection's backend supports reusing an already-prepared statement handle
    /// for a later execution of the same SQL text.
    ///
    /// Gates the prepared-statement cache: a backend not known to keep a prepared statement
    /// re-executable across an intervening cursor close never pools handles, no matter what capacity
    /// was configured. Like @ref SupportsNativeRowBatch this is a driver/backend capability rather than
    /// a SQL-dialect concern, so it lives on the connection instead of a `switch` in the caller.
    ///
    /// @return `true` if prepared handles may be pooled and re-executed on this backend.
    [[nodiscard]] bool SupportsPreparedStatementReuse() const noexcept;
 
    /// @brief Server-type overload of @ref SupportsPreparedStatementReuse, for callers that hold only
    ///        the server type rather than a live connection.
    ///
    /// @param serverType The backend to answer for.
    /// @return `true` if prepared handles may be pooled and re-executed on @p serverType.
    [[nodiscard]] static bool SupportsPreparedStatementReuse(SqlServerType serverType) noexcept;
 
    /// @brief The capacity of this connection's prepared-statement cache.
    ///
    /// @return The number of already-prepared statement handles kept for reuse; `0` when the cache is
    ///         disabled (the default).
    [[nodiscard]] LIGHTWEIGHT_API std::size_t PreparedStatementCacheCapacity() const noexcept;
 
    /// @brief Enables (or resizes) this connection's prepared-statement cache.
    ///
    /// With a non-zero capacity, `SqlStatement::Prepare()` on this connection first looks for an idle
    /// handle already prepared for the same SQL text and reuses it, skipping the driver's `SQLPrepare`
    /// round-trip. Handles return to the cache when the statement is re-prepared or destroyed, and are
    /// evicted least-recently-used first once the bound is exceeded. Every layer built on
    /// `SqlStatement` — `DataMapper`, the query builders — benefits without call-site changes; a single
    /// statement can opt out via `SqlStatement::SetPreparedStatementCaching`.
    ///
    /// A capacity request on a backend without @ref SupportsPreparedStatementReuse is kept but stays
    /// inactive, so the same setup code is safe to run against any DBMS.
    ///
    /// @warning A pooled handle carries the query plan the driver derived when it was prepared. Call
    ///          @ref ClearPreparedStatementCache after DDL that a cached query touches; Lightweight's
    ///          own migration executor and `SqlStatement::MigrateDirect` already do so.
    ///
    /// @param capacity Maximum number of idle prepared handles to keep; `0` disables and clears the
    ///                 cache. @c PreparedStatementCacheCapacitySuggested is a reasonable starting point.
    LIGHTWEIGHT_API void SetPreparedStatementCacheCapacity(std::size_t capacity) noexcept;
 
    /// Frees every pooled prepared statement handle, e.g. after DDL invalidated the cached query plans.
    LIGHTWEIGHT_API void ClearPreparedStatementCache() noexcept;
 
    /// @brief Retrieves this connection's prepared-statement cache.
    ///
    /// Non-const because acquiring and releasing pooled handles mutates the cache. Mostly of interest
    /// for its @c SqlPreparedStatementCache::Stats counters.
    [[nodiscard]] LIGHTWEIGHT_API SqlPreparedStatementCache& PreparedStatementCache() noexcept;
 
    /// Creates a new query builder for the given table, compatible with the current connection.
    ///
    /// @param table The table to query.
    ///              If not provided, the query will be a generic query builder.
    [[nodiscard]] LIGHTWEIGHT_API SqlQueryBuilder Query(std::string_view const& table = {}) const;
 
    /// Creates a new query builder for the given table with an alias, compatible with the current connection.
    ///
    /// @param table The table to query.
    /// @param tableAlias The alias to use for the table.
    [[nodiscard]] LIGHTWEIGHT_API SqlQueryBuilder QueryAs(std::string_view const& table,
                                                          std::string_view const& tableAlias) const;
 
    /// Creates a new migration query builder, compatible the current connection.
    [[nodiscard]] LIGHTWEIGHT_API SqlMigrationQueryBuilder Migration() const;
 
    /// Tests if a transaction is active.
    [[nodiscard]] LIGHTWEIGHT_API bool TransactionActive() const noexcept;
 
    /// Rolls back a transaction left open on this connection and restores autocommit mode.
    ///
    /// Recognizes a transaction the ODBC way, by autocommit being off - as @ref SqlTransaction and
    /// any caller switching autocommit off leave it. A transaction opened by executing a raw
    /// @c BEGIN statement while autocommit is on is not visible to ODBC and is not ended here.
    ///
    /// The connection pool calls this on every connection returned to it.
    ///
    /// @return @c true when the connection is in autocommit mode afterwards (including when no
    ///         transaction was open); @c false when the rollback or the mode switch failed.
    [[nodiscard]] LIGHTWEIGHT_API bool RollbackOpenTransaction() noexcept;
 
    /// Tests if transactions are allowed.
    [[nodiscard]] LIGHTWEIGHT_API bool TransactionsAllowed() const noexcept;
 
    /// Tests if the connection is still active.
    [[nodiscard]] LIGHTWEIGHT_API bool IsAlive() const noexcept;
 
    /// Retrieves the connection information.
    [[nodiscard]] LIGHTWEIGHT_API SqlConnectionString const& ConnectionString() const noexcept;
 
    /// Retrieves the native handle.
    [[nodiscard]] SQLHDBC NativeHandle() const noexcept
    {
        return m_hDbc;
    }
 
    /// Retrieves the last time the connection was used.
    [[nodiscard]] LIGHTWEIGHT_API std::chrono::steady_clock::time_point LastUsed() const noexcept;
 
    /// Sets the last time the connection was used.
    LIGHTWEIGHT_API void SetLastUsed(std::chrono::steady_clock::time_point lastUsed) noexcept;
 
    /// Checks the result of an SQL operation, and throws an exception if it is not successful.
    LIGHTWEIGHT_API void RequireSuccess(SQLRETURN sqlResult,
                                        std::source_location sourceLocation = std::source_location::current()) const;
 
    /// Enables coroutine-based asynchronous methods on this connection.
    ///
    /// Wires the connection to an injected execution context: blocking ODBC work is offloaded to
    /// @p dbWorkers (serialized per connection so the ODBC handle is only ever used by one thread
    /// at a time) and the awaiting coroutine is resumed via @p resume (typically the application's
    /// run loop). A native driver-async backend is selected when the driver advertises support;
    /// otherwise the portable thread-offload backend is used.
    ///
    /// Both executors must outlive this connection and every coroutine driven through it.
    /// Must not be called while asynchronous operations are in flight on this connection (it
    /// replaces the backend); the connection pool calls @ref DisableAsync on return so each
    /// re-acquire is a fresh enable rather than a live replacement.
    ///
    /// @param dbWorkers The worker-thread pool used to run blocking ODBC calls.
    /// @param resume The scheduler used to resume coroutines after a blocking step completes.
    LIGHTWEIGHT_API void EnableAsync(Async::IExecutor& dbWorkers, Async::IResumeScheduler& resume);
 
    /// Enables the asynchronous API on this connection using an explicitly provided backend.
    ///
    /// This is the dependency-injection seam behind the convenience overload above: callers and
    /// tests can supply any @ref Async::IAsyncBackend implementation (a fake/inline backend for
    /// tests, or a future native event backend) instead of the default thread-offload backend.
    /// The same in-flight / lifetime constraints as the convenience overload apply.
    ///
    /// @param backend The async execution backend to install (consumed); must not be null.
    LIGHTWEIGHT_API void EnableAsync(std::unique_ptr<Async::IAsyncBackend> backend);
 
    /// Tears down the asynchronous backend, returning the connection to a non-async state.
    ///
    /// Called by the connection pool when a mapper is returned, so a recycled connection never
    /// carries a stale backend that references executors which may have been destroyed. Safe to
    /// call when async was never enabled.
    ///
    /// @warning Must not be called while async work is in flight on this connection: it destroys the
    /// backend (and the strand/executors an outstanding offloaded step still references), which would
    /// race the worker still touching the ODBC handle. Await every async operation on this connection
    /// to completion before disabling (or before returning the owning pooled @c DataMapper).
    LIGHTWEIGHT_API void DisableAsync() noexcept;
 
    /// @return true if @ref EnableAsync has been called on this connection (and not yet disabled).
    [[nodiscard]] LIGHTWEIGHT_API bool IsAsyncEnabled() const noexcept;
 
    /// Retrieves the connection's asynchronous execution backend.
    ///
    /// Non-const because using the backend schedules and serializes real ODBC work (via its strand and
    /// resume scheduler), which is an observable mutation of the connection's execution state.
    ///
    /// @pre @ref IsAsyncEnabled returns true.
    /// @throws std::logic_error if async has not been enabled (programmer error, fail-fast).
    /// @return The async backend used by this connection's async methods.
    [[nodiscard]] LIGHTWEIGHT_API Async::IAsyncBackend& AsyncBackend();
 
  private:
    /// Selects the private constructor that borrows a handle, keeping it out of overload resolution
    /// for the public constructors.
    struct BorrowTag
    {
    };
 
    /// Constructs a connection on a borrowed, already connected handle. Used by Borrow().
    SqlConnection(BorrowTag /*tag*/, SQLHDBC connectedHandle);
 
    /// Detects the server type, query formatter, driver name and text entry point of the connected handle.
    void DetectServer();
 
    /// Ensures ODBC handles are allocated. Called by Connect() methods.
    /// If handles were freed by Close(), this method reallocates them.
    void EnsureHandlesAllocated();
 
    /// Detects the server type and applies the connect-time session settings.
    /// @return `false` if a requested session setting could not be applied (the error is logged).
    [[nodiscard]] bool PostConnect();
 
    /// One attempt of Connect(SqlConnectionDataSource const&).
    [[nodiscard]] std::expected<void, SqlErrorInfo> ConnectOnce(SqlConnectionDataSource const& info) noexcept;
 
    /// One attempt of Connect(SqlConnectionString).
    [[nodiscard]] std::expected<void, SqlErrorInfo> ConnectOnce() noexcept;
 
    /// Runs @p attempt, retrying a transient failure as the connect-retry settings say.
    /// @return True once an attempt succeeded; false when the policy gave up.
    template <typename Attempt>
    [[nodiscard]] bool ConnectWithRetry(Attempt const& attempt) noexcept;
 
    /// Issues the statements the query formatter derives from this connection's SQLite settings; none
    /// unless connected to SQLite.
    /// @return Nothing on success, or the diagnostic of the statement that failed.
    [[nodiscard]] std::expected<void, SqlErrorInfo> ApplySqliteSettings();
 
    /// Re-evaluates the backend capability gate on the requested prepared-statement cache capacity.
    void ApplyPreparedStatementCacheCapacity() noexcept;
 
    /// Issues the SET statement that realises m_stringTruncationMode. A no-op unless connected to
    /// Microsoft SQL Server. Throws if the SET statement fails, like the other connect-time settings.
    void ApplyStringTruncationMode();
 
    /// Adopts the string-truncation mode the session already has, without changing it. Reads the
    /// @c ANSI_WARNINGS state on Microsoft SQL Server; keeps the current mode on every other backend.
    void ReadStringTruncationMode();
 
    // Private data members
    // Note: move/move assignment operators implemented manually
    // if adding new data members, make sure to update them accordingly.
    SQLHENV m_hEnv {};
    SQLHDBC m_hDbc {};
    uint64_t m_connectionId;
    SqlServerType m_serverType = SqlServerType::UNKNOWN;
    SqlQueryFormatter const* m_queryFormatter {};
    std::string m_driverName;
    bool m_acceptsWideQueryText = true;
    SqlStringTruncationMode m_stringTruncationMode = SqlStringTruncationMode::Truncate;
    SqlHandleOwnership m_handleOwnership = SqlHandleOwnership::Owned;
 
    struct Data;
    Data* m_data {};
};
 
inline SqlServerType SqlConnection::ServerType() const noexcept
{
    return m_serverType;
}
 
inline std::string const& SqlConnection::DriverName() const noexcept
{
    return m_driverName;
}
 
inline bool SqlConnection::AcceptsWideQueryText() const noexcept
{
    return m_acceptsWideQueryText;
}
 
inline SqlQueryFormatter const& SqlConnection::QueryFormatter() const noexcept
{
    return *m_queryFormatter;
}
 
inline bool SqlConnection::SupportsNativeRowBatch() const noexcept
{
    // Native ODBC parameter-array binding (SQL_ATTR_PARAMSET_SIZE > 1) is a per-driver capability.
    // Every backend Lightweight supports and tests against honours it; an unverified backend takes the
    // always-correct per-row path rather than risk a driver that silently ignores the parameter array.
    switch (ServerType())
    {
        case SqlServerType::MICROSOFT_SQL:
        case SqlServerType::POSTGRESQL:
        case SqlServerType::SQLITE:
            return true;
        case SqlServerType::MYSQL:
        case SqlServerType::UNKNOWN:
            return false;
    }
    return false;
}
 
inline bool SqlConnection::SupportsPreparedStatementReuse(SqlServerType serverType) noexcept
{
    // Re-executing a prepared statement after its cursor was closed is core ODBC, but pooling handles
    // across call sites is only claimed for the backends Lightweight tests against. An unverified
    // backend keeps the always-correct prepare-per-statement path.
    switch (serverType)
    {
        case SqlServerType::MICROSOFT_SQL:
        case SqlServerType::POSTGRESQL:
        case SqlServerType::SQLITE:
            return true;
        case SqlServerType::MYSQL:
        case SqlServerType::UNKNOWN:
            return false;
    }
    return false;
}
 
inline bool SqlConnection::SupportsPreparedStatementReuse() const noexcept
{
    return SupportsPreparedStatementReuse(ServerType());
}
 
inline bool SqlConnection::SupportsNativeRowArrayFetch(SqlServerType serverType) noexcept
{
    // Native ODBC row-array fetching (SQL_ATTR_ROW_ARRAY_SIZE > 1 + SQLFetchScroll) is a per-driver
    // capability. Every backend Lightweight supports and tests against honours it; an unverified backend
    // takes the always-correct per-row SQLFetch path rather than risk a driver that mis-handles the array.
    switch (serverType)
    {
        case SqlServerType::MICROSOFT_SQL:
        case SqlServerType::POSTGRESQL:
        case SqlServerType::SQLITE:
            return true;
        case SqlServerType::MYSQL:
        case SqlServerType::UNKNOWN:
            return false;
    }
    return false;
}
 
inline bool SqlConnection::SupportsNativeRowArrayFetch() const noexcept
{
    return SupportsNativeRowArrayFetch(ServerType());
}
 
inline bool SqlConnection::RoundTripsNarrowTextByteExact(SqlServerType serverType) noexcept
{
    switch (serverType)
    {
        case SqlServerType::MICROSOFT_SQL:
        case SqlServerType::SQLITE:
            return true;
        case SqlServerType::POSTGRESQL: // psqlODBC transcodes SQL_C_CHAR through the client codepage
        case SqlServerType::MYSQL:
        case SqlServerType::UNKNOWN:
            return false;
    }
    return false;
}
 
} // namespace Lightweight