SQL/SQLite.cpp

/*
 * Copyright(c) Sophist Solutions, Inc. 1990-2025.  All rights reserved
 */
#include "Stroika/Foundation/StroikaPreComp.h"
 
#include <system_error>
 
#include "Stroika/Foundation/Characters/Format.h"
#include "Stroika/Foundation/Characters/ToString.h"
#include "Stroika/Foundation/Debug/Trace.h"
#include "Stroika/Foundation/Time/Duration.h"
 
#include "SQLite.h"
 
using namespace Stroika::Foundation;
 
using namespace Characters;
using namespace Debug;
using namespace Database;
using namespace Database::SQL::SQLite;
using namespace Execution;
using namespace Time;
 
// Comment this in to turn on aggressive noisy DbgTrace in this module
//#define   USE_NOISY_TRACE_IN_THIS_MODULE_       1
 
//#if qStroika_HasComponent_sqlite && defined(_MSC_VER)
//// Use #pragma comment lib instead of explicit entry in the lib entry of the project file
//#pragma comment(lib, "sqlite.lib")
//#endif
 
using Database::SQL::EngineProperties;
 
#if qStroika_HasComponent_sqlite
namespace {
    struct ModuleShutdown_ {
        ~ModuleShutdown_ ()
        {
            Verify (::sqlite3_shutdown () == SQLITE_OK); // mostly pointless but avoids memory leak complaints
        }
    } sModuleShutdown_;
    [[noreturn]] void ThrowSQLiteError_ (int errCode, sqlite3* sqliteConnection)
    {
        Require (errCode != SQLITE_OK);
        optional<String> errMsgDetails;
        if (sqliteConnection != nullptr) {
            errMsgDetails = String::FromUTF8 (::sqlite3_errmsg (sqliteConnection));
        }
        switch (errCode) {
            case SQLITE_BUSY: {
                DbgTrace ("SQLITE_BUSY"_f); //  The database file is locked
                Throw (system_error{make_error_code (errc::device_or_resource_busy)});
            } break;
            case SQLITE_LOCKED: {
                DbgTrace ("SQLITE_LOCKED"_f); //  A table in the database is locked
                Throw (system_error{make_error_code (errc::device_or_resource_busy)});
            } break;
            case SQLITE_CONSTRAINT: {
                if (errMsgDetails) {
                    Throw (Exception{"SQLITE_CONSTRAINT: {}"_f(errMsgDetails)});
                }
                else {
                    static const auto kEx_ = Exception{"SQLITE_CONSTRAINT"sv};
                    Throw (kEx_);
                }
            } break;
            case SQLITE_TOOBIG: {
                static const auto kEx_ = Exception{"SQLITE_TOOBIG"sv};
                Throw (kEx_);
            } break;
            case SQLITE_FULL: {
                DbgTrace ("SQLITE_FULL"_f);
                Throw (system_error{make_error_code (errc::no_space_on_device)});
            } break;
            case SQLITE_READONLY: {
                static const auto kEx_ = Exception{"SQLITE_READONLY"sv};
                Throw (kEx_);
            } break;
            case SQLITE_MISUSE: {
                if (errMsgDetails) {
                    Throw (Exception{"SQLITE_MISUSE: {}"_f(errMsgDetails)});
                }
                else {
                    static const auto kEx_ = Exception{"SQLITE_MISUSE"sv};
                    Throw (kEx_);
                }
            } break;
            case SQLITE_ERROR: {
                if (errMsgDetails) {
                    Throw (Exception{"SQLITE_ERROR: {}"_f(errMsgDetails)});
                }
                else {
                    static const auto kEx_ = Exception{"SQLITE_ERROR"sv};
                    Throw (kEx_);
                }
            } break;
            case SQLITE_NOMEM: {
                DbgTrace ("SQLITE_NOMEM translated to bad_alloc"_f);
                Throw (bad_alloc{});
            } break;
        }
        if (errMsgDetails) {
            Throw (Exception{"SQLite Error: {} (code {})"_f(errMsgDetails, errCode)});
        }
        else {
            Throw (Exception{"SQLite Error: {}"_f(errCode)});
        }
    }
    void ThrowSQLiteErrorIfNotOK_ (int errCode, sqlite3* sqliteConnection)
    {
        static_assert (SQLITE_OK == 0);
        if (errCode != SQLITE_OK) [[unlikely]] {
            ThrowSQLiteError_ (errCode, sqliteConnection);
        }
    }
 
    /*
     *  Simple utility to be able to use lambdas with arbitrary captures more easily with sqlite c API
     */
    template <invocable<int, char**, char**> CB>
    struct SQLiteCallback_ {
        CB fCallback_;
 
        using STATIC_FUNCTION_TYPE = int (*) (void*, int, char**, char**);
 
        SQLiteCallback_ (CB&& cb)
            : fCallback_{forward<CB> (cb)}
        {
        }
        STATIC_FUNCTION_TYPE GetStaticFunction ()
        {
            return STATICFUNC_;
        }
        void* GetData ()
        {
            return this;
        }
 
    private:
        static int STATICFUNC_ (void* SQLiteCallbackData, int argc, char** argv, char** azColName)
        {
            SQLiteCallback_* sqc = reinterpret_cast<SQLiteCallback_*> (SQLiteCallbackData);
            return sqc->fCallback_ (argc, argv, azColName);
        }
    };
}
 
/*
 ********************************************************************************
 *************************** SQLite::CompiledOptions ****************************
 ********************************************************************************
 */
namespace {
    struct VerifyFlags_ {
        VerifyFlags_ ()
        {
            Assert (CompiledOptions::kThe.ENABLE_NORMALIZE == !!::sqlite3_compileoption_used ("ENABLE_NORMALIZE"));
            Assert (CompiledOptions::kThe.THREADSAFE == !!::sqlite3_compileoption_used ("THREADSAFE"));
#if SQLITE_VERSION_NUMBER < 3038000
            Assert (CompiledOptions::kThe.ENABLE_JSON1 == !!::sqlite3_compileoption_used ("ENABLE_JSON1"));
#else
            Assert (CompiledOptions::kThe.ENABLE_JSON1 == !::sqlite3_compileoption_used ("OMIT_JSON1"));
#endif
        }
    } sVerifyFlags_;
}
 
namespace {
    using Connection::Options;
    struct Rep_ final : Database::SQL::SQLite::Connection::IRep, private Debug::AssertExternallySynchronizedMutex {
        Rep_ (const Options& options)
        {
            TraceContextBumper ctx{"SQLite::Connection::Rep_::Rep_"};
 
            int flags = 0;
            // https://www.sqlite.org/threadsafe.html explains the thread-safety stuff. Not sure I have it right, but hopefully --LGP 2023-09-13
            switch (options.fThreadingMode.value_or (Options::kDefault_ThreadingMode)) {
                case Options::ThreadingMode::eSingleThread:
                    break;
                case Options::ThreadingMode::eMultiThread:
                    Require (CompiledOptions::kThe.THREADSAFE);
                    Require (::sqlite3_threadsafe ());
                    flags |= SQLITE_OPEN_NOMUTEX;
                    break;
                case Options::ThreadingMode::eSerialized:
                    Require (CompiledOptions::kThe.THREADSAFE);
                    Require (::sqlite3_threadsafe ());
                    flags |= SQLITE_OPEN_FULLMUTEX;
                    break;
            }
 
            if (options.fImmutable) {
                // NYI cuz requires uri syntax
                WeakAssertNotImplemented ();
                Require (options.fReadOnly);
            }
            flags |= options.fReadOnly ? SQLITE_OPEN_READONLY : SQLITE_OPEN_READWRITE;
 
            string uriArg;
            if constexpr (qStroika_Foundation_Debug_AssertionsChecked) {
                [[maybe_unused]] int n{};
                if (options.fDBPath) {
                    ++n;
                }
                if (options.fTemporaryDB) {
                    ++n;
                }
                if (options.fInMemoryDB) {
                    ++n;
                }
                Require (n == 1); // exactly one of fDBPath, fTemporaryDB, fInMemoryDB must be provided
            }
            if (options.fDBPath) {
                uriArg = options.fDBPath->generic_string ();
                if (uriArg[0] == ':') {
                    uriArg = "./" + uriArg; // sqlite docs warn to do this, to avoid issues with :memory or other extensions
                }
            }
            if (options.fTemporaryDB) {
                uriArg = string{};
                // According to https://sqlite.org/inmemorydb.html, temporary DBs appear to require empty name
                // @todo MAYBE fix to find a way to do named temporary DB? - or adjust API so no string name provided.
                Require (not options.fTemporaryDB->empty ());
            }
            if (options.fInMemoryDB) {
                // Not super clear why SQLITE_OPEN_URI needed, but the example in docs uses URI, and tracing through the sqlite open code
                // it appears to require a URI format, but not really documented as near as I can tell...--LGP 2025-03-31
                flags |= SQLITE_OPEN_MEMORY;
                flags |= SQLITE_OPEN_URI;
                flags |= SQLITE_OPEN_SHAREDCACHE;
                Require (not options.fReadOnly);
                Require (options.fCreateDBPathIfDoesNotExist);
                uriArg = options.fInMemoryDB->AsNarrowSDKString (); // often empty string
                if (uriArg.empty ()) {
                    uriArg = ":memory";
                }
                else {
                    u8string safeCharURI = IO::Network::UniformResourceIdentification::PCTEncode (u8string{uriArg.begin (), uriArg.end ()}, {});
                    uriArg = "file:" + string{safeCharURI.begin (), safeCharURI.end ()} + "?mode=memory&cache=shared";
                }
                // For now, it appears we ALWAYS create memory DBS when opening (so cannot find a way to open shared) - so always set created flag
            }
 
            int e;
            if ((e = ::sqlite3_open_v2 (uriArg.c_str (), &fDB_, flags, options.fVFS ? options.fVFS->AsNarrowSDKString ().c_str () : nullptr)) == SQLITE_CANTOPEN) {
                if (options.fCreateDBPathIfDoesNotExist) {
                    if (fDB_ != nullptr) {
                        Verify (::sqlite3_close (fDB_) == SQLITE_OK);
                        fDB_ = nullptr;
                    }
                    if ((e = ::sqlite3_open_v2 (uriArg.c_str (), &fDB_, SQLITE_OPEN_CREATE | flags,
                                                options.fVFS ? options.fVFS->AsNarrowSDKString ().c_str () : nullptr)) == SQLITE_OK) {
                        ; // if???
                    }
                }
            }
            if (e != SQLITE_OK) [[unlikely]] {
                [[maybe_unused]] auto&& cleanup = Finally ([this] () noexcept {
                    if (fDB_ != nullptr) {
                        Verify (::sqlite3_close (fDB_) == SQLITE_OK);
                    }
                });
                ThrowSQLiteError_ (e, fDB_);
            }
            if (options.fBusyTimeout) {
                SetBusyTimeout (*options.fBusyTimeout);
            }
            if (options.fJournalMode) {
                SetJournalMode (*options.fJournalMode);
            }
            EnsureNotNull (fDB_);
        }
        ~Rep_ ()
        {
            AssertNotNull (fDB_);
            Verify (::sqlite3_close (fDB_) == SQLITE_OK);
        }
        virtual shared_ptr<const EngineProperties> GetEngineProperties () const override
        {
            struct MyEngineProperties_ final : EngineProperties {
                virtual String GetEngineName () const override
                {
                    return "SQLite"sv;
                }
                virtual String GetSQL (NonStandardSQL n) const override
                {
                    switch (n) {
                        case NonStandardSQL::eDoesTableExist:
                            return "SELECT name FROM sqlite_master WHERE type='table' AND name="_k + SQL::EngineProperties::kDoesTableExistParameterName;
                    }
                    AssertNotReached ();
                    return String{};
                }
                virtual bool RequireStatementResetAfterModifyingStatmentToCompleteTransaction () const override
                {
                    return true;
                }
                virtual bool SupportsNestedTransactions () const override
                {
                    return false;
                }
            };
            static const shared_ptr<const EngineProperties> kProps_ = make_shared<const MyEngineProperties_> ();
            return kProps_;
        }
        virtual SQL::Statement mkStatement (const String& sql) override
        {
            Connection::Ptr conn = Connection::Ptr{Debug::UncheckedDynamicPointerCast<Connection::IRep> (shared_from_this ())};
            return Database::SQL::SQLite::Statement{conn, sql};
        }
        virtual SQL::Transaction mkTransaction () override
        {
            Connection::Ptr conn = Connection::Ptr{Debug::UncheckedDynamicPointerCast<Connection::IRep> (shared_from_this ())};
            return Database::SQL::SQLite::Transaction{conn};
        }
        virtual void Exec (const String& sql) override
        {
            Debug::AssertExternallySynchronizedMutex::WriteContext declareContext{*this};
            int e = ::sqlite3_exec (fDB_, sql.AsUTF8<string> ().c_str (), nullptr, nullptr, nullptr);
            if (e != SQLITE_OK) [[unlikely]] {
                ThrowSQLiteErrorIfNotOK_ (e, fDB_);
            }
        }
        virtual ::sqlite3* Peek () override
        {
            Debug::AssertExternallySynchronizedMutex::WriteContext declareContext{*this}; // not super helpful, but could catch errors - reason not very helpful is we lose lock long before we stop using ptr
            return fDB_;
        }
        virtual Duration GetBusyTimeout () const override
        {
            Debug::AssertExternallySynchronizedMutex::ReadContext declareContext{*this};
            optional<int>                                         d;
            auto callback = SQLiteCallback_{[&] ([[maybe_unused]] int argc, char** argv, [[maybe_unused]] char** azColName) {
                Assert (argc == 1);
                Assert (::strcmp (azColName[0], "timeout") == 0);
                int val = ::atoi (argv[0]);
                Assert (val >= 0);
                d = val;
                return SQLITE_OK;
            }};
            ThrowSQLiteErrorIfNotOK_ (::sqlite3_exec (fDB_, "pragma busy_timeout;", callback.GetStaticFunction (), callback.GetData (), nullptr), fDB_);
            Assert (d);
            return Duration{double (*d) / 1000.0};
        }
        virtual void SetBusyTimeout (const Duration& timeout) override
        {
            Debug::AssertExternallySynchronizedMutex::WriteContext declareContext{*this};
            ThrowSQLiteErrorIfNotOK_ (::sqlite3_busy_timeout (fDB_, (int)(timeout.As<float> () * 1000)), fDB_);
        }
        virtual JournalModeType GetJournalMode () const override
        {
            optional<string> d;
            auto             callback = SQLiteCallback_{[&] ([[maybe_unused]] int argc, char** argv, [[maybe_unused]] char** azColName) {
                Assert (argc == 1);
                Assert (::strcmp (azColName[0], "journal_mode") == 0);
                d = argv[0];
                return SQLITE_OK;
            }};
            ThrowSQLiteErrorIfNotOK_ (::sqlite3_exec (fDB_, "pragma journal_mode;", callback.GetStaticFunction (), callback.GetData (), nullptr), fDB_);
            Assert (d);
            if (d == "delete"sv) {
                return JournalModeType::eDelete;
            }
            if (d == "truncate"sv) {
                return JournalModeType::eTruncate;
            }
            if (d == "persist"sv) {
                return JournalModeType::ePersist;
            }
            if (d == "memory"sv) {
                return JournalModeType::eMemory;
            }
            if (d == "wal"sv) {
                return JournalModeType::eWAL;
            }
            if (d == "wal2"sv) {
                return JournalModeType::eWAL2;
            }
            if (d == "off"sv) {
                return JournalModeType::eOff;
            }
            AssertNotReached ();
            return JournalModeType::eDelete;
        }
        virtual void SetJournalMode (JournalModeType journalMode) override
        {
            Debug::AssertExternallySynchronizedMutex::WriteContext declareContext{*this};
            switch (journalMode) {
                case JournalModeType::eDelete:
                    ThrowSQLiteErrorIfNotOK_ (::sqlite3_exec (fDB_, "pragma journal_mode = 'delete';", nullptr, 0, nullptr), fDB_);
                    break;
                case JournalModeType::eTruncate:
                    ThrowSQLiteErrorIfNotOK_ (::sqlite3_exec (fDB_, "pragma journal_mode = 'truncate';", nullptr, 0, nullptr), fDB_);
                    break;
                case JournalModeType::ePersist:
                    ThrowSQLiteErrorIfNotOK_ (::sqlite3_exec (fDB_, "pragma journal_mode = 'persist';", nullptr, 0, nullptr), fDB_);
                    break;
                case JournalModeType::eMemory:
                    ThrowSQLiteErrorIfNotOK_ (::sqlite3_exec (fDB_, "pragma journal_mode = 'memory';", nullptr, 0, nullptr), fDB_);
                    break;
                case JournalModeType::eWAL:
                    ThrowSQLiteErrorIfNotOK_ (::sqlite3_exec (fDB_, "pragma journal_mode = 'wal';", nullptr, 0, nullptr), fDB_);
                    break;
                case JournalModeType::eWAL2:
                    if (GetJournalMode () == JournalModeType::eWAL) {
                        ThrowSQLiteErrorIfNotOK_ (::sqlite3_exec (fDB_, "pragma journal_mode = 'delete';", nullptr, 0, nullptr), fDB_);
                    }
                    ThrowSQLiteErrorIfNotOK_ (::sqlite3_exec (fDB_, "pragma journal_mode = 'wal2';", nullptr, 0, nullptr), fDB_);
                    break;
                case JournalModeType::eOff:
                    ThrowSQLiteErrorIfNotOK_ (::sqlite3_exec (fDB_, "pragma journal_mode = 'off';", nullptr, 0, nullptr), fDB_);
                    break;
            }
        }
 
        ::sqlite3* fDB_{};
    };
}
 
/*
 ********************************************************************************
 *********************** SQL::SQLite::Connection::Ptr ***************************
 ********************************************************************************
 */
SQL::SQLite::Connection::Ptr::Ptr (const shared_ptr<IRep>& src)
    : inherited{src}
    , busyTimeout{[qStroika_Foundation_Common_Property_ExtraCaptureStuff] ([[maybe_unused]] const auto* property) {
                      const Ptr* thisObj = qStroika_Foundation_Common_Property_OuterObjPtr (property, &Ptr::busyTimeout);
                      RequireNotNull (thisObj->operator->());
                      return thisObj->operator->()->GetBusyTimeout ();
                  },
                  [qStroika_Foundation_Common_Property_ExtraCaptureStuff] ([[maybe_unused]] auto* property, auto timeout) {
                      Ptr* thisObj = qStroika_Foundation_Common_Property_OuterObjPtr (property, &Ptr::busyTimeout);
                      RequireNotNull (thisObj->operator->());
                      thisObj->operator->()->SetBusyTimeout (timeout);
                  }}
    , journalMode{[qStroika_Foundation_Common_Property_ExtraCaptureStuff] ([[maybe_unused]] const auto* property) {
                      const Ptr* thisObj = qStroika_Foundation_Common_Property_OuterObjPtr (property, &Ptr::journalMode);
                      RequireNotNull (thisObj->operator->());
                      return thisObj->operator->()->GetJournalMode ();
                  },
                  [qStroika_Foundation_Common_Property_ExtraCaptureStuff] ([[maybe_unused]] auto* property, auto journalMode) {
                      Ptr* thisObj = qStroika_Foundation_Common_Property_OuterObjPtr (property, &Ptr::journalMode);
                      RequireNotNull (thisObj->operator->());
                      thisObj->operator->()->SetJournalMode (journalMode);
                  }}
{
#if qStroika_Foundation_Debug_AssertExternallySynchronizedMutex_Enabled
    if (src != nullptr) {
        fAssertExternallySynchronizedMutex.SetAssertExternallySynchronizedMutexContext (src->fAssertExternallySynchronizedMutex.GetSharedContext ());
    }
#endif
}
 
/*
 ********************************************************************************
 ************************** SQL::SQLite::Connection *****************************
 ********************************************************************************
 */
auto SQL::SQLite::Connection::New (const Options& options) -> Ptr
{
    return Ptr{make_shared<Rep_> (options)};
}
 
/*
 ********************************************************************************
 ******************************* SQLite::Statement ******************************
 ********************************************************************************
 */
struct Statement::MyRep_ : IRep {
    MyRep_ (const Connection::Ptr& db, const String& query)
        : fConnectionPtr_{db}
    {
#if USE_NOISY_TRACE_IN_THIS_MODULE_
        TraceContextBumper ctx{L"SQLite::Statement::MyRep_::CTOR",
                               Stroika_Foundation_Debug_OptionalizeTraceArgs (L"db=%p, query='%s'", db.Peek (), query.As<wstring> ().c_str ())};
#endif
        RequireNotNull (db);
        RequireNotNull (db->Peek ());
#if qStroika_Foundation_Debug_AssertExternallySynchronizedMutex_Enabled
        _fAssertExternallySynchronizedMutex.SetAssertExternallySynchronizedMutexContext (
            fConnectionPtr_.fAssertExternallySynchronizedMutex.GetSharedContext ());
#endif
        string                                          queryUTF8 = query.AsUTF8<string> ();
        AssertExternallySynchronizedMutex::WriteContext declareContext{_fAssertExternallySynchronizedMutex};
        const char*                                     pzTail = nullptr;
        ThrowSQLiteErrorIfNotOK_ (::sqlite3_prepare_v2 (db->Peek (), queryUTF8.c_str (), -1, &fStatementObj_, &pzTail), db->Peek ());
        Assert (pzTail != nullptr);
        if (*pzTail != '\0') {
            // @todo possibly should allow 0 or string of whitespace and ignore that too? -- LGP 2021-04-29
            Throw (Exception{"Unexpected text after query"sv});
        }
        AssertNotNull (fStatementObj_);
        unsigned int colCount = static_cast<unsigned int> (::sqlite3_column_count (fStatementObj_));
        for (unsigned int i = 0; i < colCount; ++i) {
            const char* colTypeUTF8 = ::sqlite3_column_decltype (fStatementObj_, i);
            fColumns_.push_back (ColumnDescription{String::FromUTF8 (::sqlite3_column_name (fStatementObj_, i)),
                                                   (colTypeUTF8 == nullptr) ? optional<String>{} : String::FromUTF8 (colTypeUTF8)});
#if USE_NOISY_TRACE_IN_THIS_MODULE_
            DbgTrace (L"sqlite3_column_decltype(i) = %s", ::sqlite3_column_decltype (fStatementObj_, i) == nullptr
                                                              ? L"{nullptr}"
                                                              : String::FromUTF8 (::sqlite3_column_decltype (fStatementObj_, i)).c_str ());
#endif
        }
 
        // Default setting (not documented, but I assume) is null
        unsigned int paramCount = static_cast<unsigned int> (::sqlite3_bind_parameter_count (fStatementObj_));
        for (unsigned int i = 1; i <= paramCount; ++i) {
            const char* tmp = ::sqlite3_bind_parameter_name (fStatementObj_, i); // can be null
            fParameters_ += ParameterDescription{tmp == nullptr ? optional<String>{} : String::FromUTF8 (tmp), nullptr};
        }
    }
    ~MyRep_ ()
    {
        AssertExternallySynchronizedMutex::WriteContext declareContext{_fAssertExternallySynchronizedMutex};
        AssertNotNull (fStatementObj_);
        (void)::sqlite3_finalize (fStatementObj_);
    }
    virtual String GetSQL (WhichSQLFlag whichSQL) const override
    {
        AssertExternallySynchronizedMutex::ReadContext declareContext{_fAssertExternallySynchronizedMutex};
        switch (whichSQL) {
            case WhichSQLFlag::eOriginal:
                return String::FromUTF8 (::sqlite3_sql (fStatementObj_));
            case WhichSQLFlag::eExpanded: {
                auto tmp = ::sqlite3_expanded_sql (fStatementObj_);
                if (tmp != nullptr) {
                    String r = String::FromUTF8 (tmp);
                    ::sqlite3_free (tmp);
                    return r;
                }
                throw bad_alloc{};
            }
            case WhichSQLFlag::eNormalized:
                if constexpr (CompiledOptions::kThe.ENABLE_NORMALIZE) {
// This ifdef should NOT be needed (because if constexpr should prevent it from being evaluated), but
// that doesn't appear to work on MSVC 2k19 - and for now - more of a PITA than its worth for bug define
#ifdef SQLITE_ENABLE_NORMALIZE
                    return String::FromUTF8 (::sqlite3_normalized_sql (fStatementObj_));
#endif
                }
                RequireNotReached ();
                return String{};
            default:
                RequireNotReached ();
                return String{};
        }
    }
    virtual Sequence<ColumnDescription> GetColumns () const override
    {
        AssertExternallySynchronizedMutex::ReadContext declareContext{_fAssertExternallySynchronizedMutex};
        return Sequence<ColumnDescription>{fColumns_};
    };
    virtual Sequence<ParameterDescription> GetParameters () const override
    {
        AssertExternallySynchronizedMutex::ReadContext declareContext{_fAssertExternallySynchronizedMutex};
        return fParameters_;
    };
    virtual void Bind () override
    {
        AssertExternallySynchronizedMutex::WriteContext declareContext{_fAssertExternallySynchronizedMutex};
        ThrowSQLiteErrorIfNotOK_ (::sqlite3_clear_bindings (fStatementObj_), fConnectionPtr_->Peek ());
    }
    virtual void Bind (unsigned int parameterIndex, const VariantValue& v) override
    {
        AssertExternallySynchronizedMutex::WriteContext declareContext{_fAssertExternallySynchronizedMutex};
        fParameters_ (parameterIndex).fValue = v;
        switch (v.GetType ()) {
            case VariantValue::eDate:
            case VariantValue::eDateTime:
            case VariantValue::eString: {
                string u = v.As<String> ().AsUTF8<string> ();
                ThrowSQLiteErrorIfNotOK_ (::sqlite3_bind_text (fStatementObj_, parameterIndex + 1, u.c_str (), static_cast<int> (u.length ()), SQLITE_TRANSIENT),
                                          fConnectionPtr_->Peek ());
            } break;
            case VariantValue::eBoolean:
            case VariantValue::eInteger:
                ThrowSQLiteErrorIfNotOK_ (::sqlite3_bind_int64 (fStatementObj_, parameterIndex + 1, v.As<sqlite3_int64> ()), fConnectionPtr_->Peek ());
                break;
            case VariantValue::eFloat:
                ThrowSQLiteErrorIfNotOK_ (::sqlite3_bind_double (fStatementObj_, parameterIndex + 1, v.As<double> ()), fConnectionPtr_->Peek ());
                break;
            case VariantValue::eBLOB: {
                Memory::BLOB b = v.As<Memory::BLOB> ();
                ThrowSQLiteErrorIfNotOK_ (::sqlite3_bind_blob64 (fStatementObj_, parameterIndex + 1, b.begin (), b.size (), SQLITE_TRANSIENT),
                                          fConnectionPtr_->Peek ());
            } break;
            case VariantValue::eNull:
                ThrowSQLiteErrorIfNotOK_ (::sqlite3_bind_null (fStatementObj_, parameterIndex + 1), fConnectionPtr_->Peek ());
                break;
            default:
                AssertNotImplemented (); // add more types
                break;
        }
    }
    virtual void Bind (const String& parameterName, const VariantValue& v) override
    {
        Require (not parameterName.empty ());
        AssertExternallySynchronizedMutex::WriteContext declareContext{_fAssertExternallySynchronizedMutex};
        String                                          pn = parameterName;
        if (pn[0] != ':') {
            pn = ":"_k + pn;
        }
        for (unsigned int i = 0; i < fParameters_.length (); ++i) {
            if (fParameters_[i].fName == pn) {
                Bind (i, v);
                return;
            }
        }
        DbgTrace ("Statement::Bind: Parameter '{}' not found in list {}"_f, parameterName,
                  fParameters_.Map<Traversal::Iterable<String>> ([] (const auto& i) { return i.fName; }));
        RequireNotReached (); // invalid paramter name provided
    }
    virtual void Reset () override
    {
#if USE_NOISY_TRACE_IN_THIS_MODULE_
        TraceContextBumper ctx{"SQLite::Statement::MyRep_::Statement::Reset"};
#endif
        AssertExternallySynchronizedMutex::WriteContext declareContext{_fAssertExternallySynchronizedMutex};
        AssertNotNull (fStatementObj_);
        ThrowSQLiteErrorIfNotOK_ (::sqlite3_reset (fStatementObj_), fConnectionPtr_->Peek ());
    }
    virtual optional<Row> GetNextRow () override
    {
#if USE_NOISY_TRACE_IN_THIS_MODULE_
        TraceContextBumper ctx{"SQLite::Statement::MyRep_::Statement::GetNextRow"};
#endif
        AssertExternallySynchronizedMutex::WriteContext declareContext{_fAssertExternallySynchronizedMutex};
        // @todo MAYBE redo with https://www.sqlite.org/c3ref/value.html
        AssertNotNull (fStatementObj_);
        int rc = ::sqlite3_step (fStatementObj_);
        switch (rc) {
            case SQLITE_OK: {
                AssertNotReached (); // I think this should never happen with this API
            } break;
            case SQLITE_ROW: {
                Row row;
                for (unsigned int i = 0; i < fColumns_.size (); ++i) {
                    VariantValue v;
                    // The actual returned type may not be the same as the DECLARED type (for example if the column is declared variant)
                    switch (::sqlite3_column_type (fStatementObj_, i)) {
                        case SQLITE_INTEGER: {
                            v = VariantValue{::sqlite3_column_int (fStatementObj_, i)};
                        } break;
                        case SQLITE_FLOAT: {
                            v = VariantValue{::sqlite3_column_double (fStatementObj_, i)};
                        } break;
                        case SQLITE_BLOB: {
                            const byte* data      = reinterpret_cast<const byte*> (::sqlite3_column_blob (fStatementObj_, i));
                            size_t      byteCount = static_cast<size_t> (::sqlite3_column_bytes (fStatementObj_, i));
                            v                     = VariantValue{Memory::BLOB{data, data + byteCount}};
                        } break;
                        case SQLITE_NULL: {
                            // default to null value
                        } break;
                        case SQLITE_TEXT: {
                            // @todo redo as sqlite3_column_text16, but doesn't help unix case? Maybe just iff sizeof(wchart_t)==2?
                            AssertNotNull (::sqlite3_column_text (fStatementObj_, i));
                            v = VariantValue{String::FromUTF8 (reinterpret_cast<const char*> (::sqlite3_column_text (fStatementObj_, i)))};
                        } break;
                        default: {
                            AssertNotReached ();
                        } break;
                    }
                    row.Add (fColumns_[i].fName, v);
                }
                return row;
            } break;
            case SQLITE_DONE: {
                return nullopt;
            } break;
        }
        ThrowSQLiteError_ (rc, fConnectionPtr_->Peek ());
    }
 
    Connection::Ptr                fConnectionPtr_;
    ::sqlite3_stmt*                fStatementObj_;
    vector<ColumnDescription>      fColumns_;
    Sequence<ParameterDescription> fParameters_;
};
 
Statement::Statement (const Connection::Ptr& db, const String& query)
    : inherited{make_unique<MyRep_> (db, query)}
{
}
 
/*
 ********************************************************************************
 ******************************* SQLite::Transaction ****************************
 ********************************************************************************
 */
struct Transaction::MyRep_ : public SQL::Transaction::IRep {
    MyRep_ (const Connection::Ptr& db, Flag f)
        : fConnectionPtr_{db}
    {
        switch (f) {
            case Flag::eDeferred:
                db->Exec ("BEGIN DEFERRED TRANSACTION;"sv);
                break;
            case Flag::eExclusive:
                db->Exec ("BEGIN EXCLUSIVE TRANSACTION;"sv);
                break;
            case Flag::eImmediate:
                db->Exec ("BEGIN IMMEDIATE TRANSACTION;"sv);
                break;
            default:
                RequireNotReached ();
        }
    }
    virtual void Commit () override
    {
        Require (not fCompleted_);
        fCompleted_ = true;
        fConnectionPtr_->Exec ("COMMIT TRANSACTION;"sv);
    }
    virtual void Rollback () override
    {
        Require (not fCompleted_);
        fCompleted_ = true;
        fConnectionPtr_->Exec ("ROLLBACK TRANSACTION;"sv);
    }
    virtual Disposition GetDisposition () const override
    {
        // @todo record more info so we can report finer grained status ; try/catch in rollback/commit and dbgtraces
        return fCompleted_ ? Disposition::eCompleted : Disposition::eNone;
    }
    Connection::Ptr fConnectionPtr_;
    bool            fCompleted_{false};
};
Transaction::Transaction (const Connection::Ptr& db, Flag f)
    : inherited{make_unique<MyRep_> (db, f)}
{
}
#endif