UT_SQL.h

Go to the documentation of this file.

/*
 * PROPRIETARY INFORMATION.  This software is proprietary to
 * Side Effects Software Inc., and is not to be reproduced,
 * transmitted, or disclosed in any way without written permission.
 *
 * NAME:        UT_SQL.h
 *
 * COMMENTS:
 *      C++ Wrapper around sqlite
 */

#ifndef __UT_SQL_H__
#define __UT_SQL_H__

#include "UT_API.h"

#include "UT_Debug.h"
#include "UT_Error.h"
#include "UT_ErrorCode.h"
#include "UT_StringHolder.h"
#include "UT_IntrusivePtr.h"
#include "UT_UniquePtr.h"
#include "UT_SharedPtr.h"

#include <SYS/SYS_Compiler.h>
#include <SYS/SYS_Inline.h>

#include <map>
#include <tuple>
#include <optional>

class UT_SqlDatabase;
class UT_SqlBaseDriver;
class UT_Options;
class UT_SqlStatement;

class sqlite3;
class sqlite3_stmt;

#define NO_DISCARD SYS_NO_DISCARD_RESULT

// NB: An actual namespace is used here because std::error_code does an ADL
// lookup when creating std::error_code with just the enum class. This allows us
// to do `if (ec == UT::SqlError::UT_SQL_HAS_STEPPED)` instead of
// `if (ec == UTmakeErrorCode(UT_SqlError::UT_SQL_HAS_STEPPED))`
namespace UT
{
/// Error codes to describe errors as a result of UT_SQL
enum class SqlError
{
    UT_SQL_OK = 0,
    UT_SQL_HAS_STEPPED,
    UT_SQL_OUT_OF_RANGE
};

UT_API const UT_ErrorCategory &GetSqliteErrorCategory();
UT_API const UT_ErrorCategory &GetSqlErrorCategory();

inline UT_ErrorCode
make_error_code(UT::SqlError e)
{
    return UT_ErrorCode{static_cast<int>(e), GetSqlErrorCategory()};
}
} // namespace UT

namespace std
{
    template <> struct is_error_code_enum<UT::SqlError> : true_type
    {
    };
}

class UT_API UT_SqlStatementHandleId
{
public:
    UT_SqlStatementHandleId(const char* source_file, exint source_line) :
        mySourceFile(source_file), mySourceLine(source_line)
    {}

    bool operator<(const UT_SqlStatementHandleId& id) const
    {
        if (mySourceLine != id.mySourceLine)
            return mySourceLine < id.mySourceLine;
        return SYSstrcmp(mySourceFile, id.mySourceFile) < 0;
    }
private:
    const char* mySourceFile;
    exint mySourceLine;
};

#define UT_SQL_ID UT_SqlStatementHandleId(__FILE__, __LINE__)

class UT_API UT_SqlStatementImpl
{
public:
    struct null_tag_t {};
    struct Blob
    {
        const void* myPtr = nullptr;
        std::size_t mySize = 0;
    };
    enum DataType
    {
        kUnknown = -1,
        kInteger,
        kFloat,
        kBlob,
        kText,
        kNull
    };

    virtual ~UT_SqlStatementImpl() = default;
    UT_NON_COPYABLE(UT_SqlStatementImpl);

    const UT_SqlBaseDriver& driver() const
    {
        return myDriver;
    }

    virtual void reset(bool clear_bindings = false) = 0;

    virtual void prepare(const UT_StringRef &sql, UT_ErrorCode *ec = nullptr)
            = 0;

    NO_DISCARD virtual int columnAsInt(int idx) const = 0;
    NO_DISCARD virtual bool columnAsBool(int idx) const = 0;
    NO_DISCARD virtual int64 columnAsInt64(int idx) const = 0;
    NO_DISCARD virtual UT_StringHolder columnAsStr(int idx) const = 0;
    NO_DISCARD virtual double columnAsDouble(int idx) const = 0;
    NO_DISCARD virtual UT_IntArray columnAsIntArray(int idx) const = 0;
    NO_DISCARD virtual UT_Int64Array columnAsInt64Array(int idx) const = 0;
    // Stores size of blob in bytes in 'size'.
    NO_DISCARD virtual const void *columnAsBlob(int idx, int &size) const = 0;
    NO_DISCARD virtual Blob columnAsBlob(int idx) const = 0;
    NO_DISCARD virtual null_tag_t columnAsNull(int idx) const = 0;
    NO_DISCARD virtual UT_StringHolder columnName(int idx) const = 0;
    NO_DISCARD virtual int columnCount() const = 0;
    NO_DISCARD virtual DataType columnType(int idx) const = 0;
    NO_DISCARD virtual int columnBytes(int idx) const = 0;

    // The original text of the sql statement. Do not keep a pointer to it.
    virtual const char* sql() const = 0;

    // Add more binds as needed
    // This binds a NULL
    virtual bool bind(int idx, null_tag_t) = 0;
    virtual bool bind(int idx, const UT_StringRef &value) = 0;
    virtual bool bind(int idx, const char *value) = 0;
    virtual bool bind(int idx, int value) = 0;
    virtual bool bind(int idx, int64 value) = 0;
    virtual bool bind(int idx, bool value) = 0;
    virtual bool bind(int idx, double value) = 0;
    // The following methods bind arrays as strings of the form [#, #, ..., #]
    virtual bool bind(int idx, const UT_IntArray &value) = 0;
    virtual bool bind(int idx, const UT_Int64Array &value) = 0;
    virtual bool bind(int idx, const UT_StringArray &value) = 0;
    // For binding blobs. `size` is required in bytes. Note that this
    // function does not destroy `value`.
    virtual bool bind(int idx, const void *value, int size) = 0;
    bool bind(int idx, Blob blob)
    {
        return this->bind(idx, blob.myPtr, blob.mySize);
    }

    NO_DISCARD virtual bool isValid() const = 0;

    const UT_ErrorCode &getError() const { return myError; }

    // Called when there are rows to iterate over (i.e. SELECT).
    virtual bool step() = 0;
    virtual bool run() = 0;

    virtual int changes() const = 0; 

    virtual bool hasRow() = 0;

    NO_DISCARD virtual bool tableExists(
            const UT_StringRef &name,
            UT_ErrorCode *ec = nullptr) const
            = 0;

protected:
    UT_SqlStatementImpl(const UT_SqlBaseDriver& driver) :
        myDriver(driver)
    {}

    const UT_SqlBaseDriver &myDriver;
    mutable UT_ErrorCode myError;
};

class UT_API UT_SqliteStatementImpl final : public UT_SqlStatementImpl
{
public:
    UT_SqliteStatementImpl(
            const UT_SqlBaseDriver &driver,
            const UT_StringRef &sql);
    UT_SqliteStatementImpl(const UT_SqlBaseDriver &driver);
    ~UT_SqliteStatementImpl() override;
    UT_NON_COPYABLE(UT_SqliteStatementImpl);

    void reset(bool clear_bindings = false) override final;

    NO_DISCARD int columnAsInt(int idx) const override final;
    NO_DISCARD bool columnAsBool(int idx) const override final;
    NO_DISCARD int64 columnAsInt64(int idx) const override final;
    NO_DISCARD UT_StringHolder columnAsStr(int idx) const override final;
    NO_DISCARD double columnAsDouble(int idx) const override final;
    NO_DISCARD UT_IntArray columnAsIntArray(int idx) const override final;
    NO_DISCARD UT_Int64Array columnAsInt64Array(int idx) const override final;
    // Stores size of blob in bytes in 'size'.
    NO_DISCARD const void *columnAsBlob(int idx, int &size) const override final;
    NO_DISCARD Blob columnAsBlob(int idx) const override final;
    NO_DISCARD null_tag_t columnAsNull(int idx) const override final;
    NO_DISCARD UT_StringHolder columnName(int idx) const override final;
    NO_DISCARD int columnCount() const override final;
    NO_DISCARD DataType columnType(int idx) const override final;
    NO_DISCARD int columnBytes(int idx) const override final;

    // The original text of the sql statement. Do not keep a pointer to it.
    const char* sql() const override final;

    // Add more binds as needed
    // This binds a NULL
    bool bind(int idx, null_tag_t) override final;
    bool bind(int idx, const UT_StringRef &value) override final;
    bool bind(int idx, const char *value) override final;
    bool bind(int idx, int value) override final;
    bool bind(int idx, int64 value) override final;
    bool bind(int idx, bool value) override final;
    bool bind(int idx, double value) override final;
    // The following methods bind arrays as strings of the form [#, #, ..., #]
    bool bind(int idx, const UT_IntArray &value) override final;
    bool bind(int idx, const UT_Int64Array &value) override final;
    bool bind(int idx, const UT_StringArray &value) override final;
    // For binding blobs. `size` is required in bytes. Note that this
    // function does not destroy `value`.
    bool bind(int idx, const void *value, int size) override final;

    NO_DISCARD bool isValid() const override final;

    // Called when there are rows to iterate over (i.e. SELECT).
    bool step() override final;
    bool run() override final;

    int changes() const override final
    {
        return myChanges;
    }
    bool hasRow() override final
    {
        if (!myHasStepped)
        {
            if (!step())
            {
                return false;
            }
        }
        return myHasRow;
    }

    NO_DISCARD bool tableExists(const UT_StringRef& name,
            UT_ErrorCode *ec = nullptr) const override;

    void prepare(const UT_StringRef& sql, UT_ErrorCode* ec = nullptr) override;

private:
    void finalize_();
    bool verifyIndex_(int idx) const;
    bool verifyColumn_(int idx) const;
    bool verifyHasStepped_() const;
    int step_();

    sqlite3_stmt *myCtx = nullptr;
    int myColumnCount;

    unsigned myHasStepped : 1, myHasRow : 1;
    int myChanges = 0;
};

class UT_API UT_SqlResult
{
public:
    class UT_API iterator
    {
    public:
        using difference_type = exint;
        using iterator_category = std::forward_iterator_tag;
        using value_type = UT_SqlResult;
        using pointer = value_type*;
        using refernce = value_type&;

        iterator& operator++()
        {
            if (myResult)
                myResult->next();
            return *this;
        }

        bool operator==(const iterator& rhs) const
        {
            if (myResult != nullptr && rhs.myResult != nullptr)
                return myResult->myStmt == rhs.myResult->myStmt;
            else if (myResult == nullptr && rhs.myResult == nullptr)
                return true;
            return false;
        }
        bool operator!=(const iterator& rhs) const
        {
            return !(*this == rhs);
        }
        const UT_SqlResult& operator*() const{
            return *myResult;
        }
        UT_SqlResult& operator*()
        {
            return *myResult;
        }
        const UT_SqlResult* operator->() const
        {
            return myResult;
        }
        UT_SqlResult* operator->()
        {
            return myResult;
        }
        bool isDone() const
        {
            return myResult == nullptr || !myResult->hasResults();
        }
        
    private:
        friend class UT_SqlResult;

        iterator(UT_SqlResult* result = nullptr) :
            myResult(result)
        {}

        UT_SqlResult* myResult = nullptr;
    };

    iterator begin()
    {
        return iterator(this);
    }
    iterator end()
    {
        return iterator();
    }
    const iterator begin() const
    {
        return iterator(SYSconst_cast(this));
    }
    const iterator end() const
    {
        return iterator();
    }
    const iterator cbegin() const
    {
        return begin();
    }
    const iterator cend() const
    {
        return end();
    }

    const UT_ErrorCode& getError() const;
    void next();
    bool hasResults() const;

    template <typename... Args>
    std::tuple<Args...> fetchOne(UT_ErrorCode* ec = nullptr);
    template <typename... Args>
    UT_Array<std::tuple<Args...>> fetchAll(UT_ErrorCode* ec = nullptr);
    template <typename... Args>
    std::tuple<Args...> as(UT_ErrorCode* ec = nullptr);

private:
    friend class UT_SqlStatement;
    UT_SqlResult(UT_SqlStatement* stmt)
        : myStmt(stmt)
    {}

    UT_SqlStatement* myStmt;
};

class UT_API UT_SqlStatement
{
public:
    using null_tag_t = UT_SqlStatementImpl::null_tag_t;
    using Blob = UT_SqlStatementImpl::Blob;
    using DataType = UT_SqlStatementImpl::DataType;

    UT_SqlStatement(const UT_SqlDatabase& con, const UT_StringRef& sql);
    UT_SqlStatement(const UT_SqlDatabase& con);
    UT_SqlStatement(const UT_SqlBaseDriver &driver, const UT_StringRef &sql);
    UT_SqlStatement(const UT_SqlBaseDriver &driver);
    UT_SqlStatement(const UT_SharedPtr<UT_SqlStatementImpl> &impl);
    ~UT_SqlStatement();
    UT_NON_COPYABLE(UT_SqlStatement);

    void reset(bool clear_bindings = false)
    {
        myImpl->reset(clear_bindings);
    }

    NO_DISCARD int columnAsInt(int idx) const
    {
        return myImpl->columnAsInt(idx);
    }
    NO_DISCARD bool columnAsBool(int idx) const
    {
        return myImpl->columnAsBool(idx);
    }
    NO_DISCARD int64 columnAsInt64(int idx) const
    {
        return myImpl->columnAsInt64(idx);
    }
    NO_DISCARD UT_StringHolder columnAsStr(int idx) const
    {
        return myImpl->columnAsStr(idx);
    }
    NO_DISCARD double columnAsDouble(int idx) const
    {
        return myImpl->columnAsDouble(idx);
    }
    NO_DISCARD UT_IntArray columnAsIntArray(int idx) const;
    NO_DISCARD UT_Int64Array columnAsInt64Array(int idx) const;
    // Stores size of blob in bytes in 'size'.
    NO_DISCARD const void *columnAsBlob(int idx, int &size) const
    {
        return myImpl->columnAsBlob(idx, size);
    }
    NO_DISCARD Blob columnAsBlob(int idx) const
    {
        return myImpl->columnAsBlob(idx);
    }
    NO_DISCARD null_tag_t columnAsNull(int idx) const
    {
        return myImpl->columnAsNull(idx);
    }
    NO_DISCARD UT_StringHolder columnName(int idx) const
    {
        return myImpl->columnName(idx);
    }
    NO_DISCARD int columnCount() const
    {
        return myImpl->columnCount();
    }
    NO_DISCARD DataType columnType(int idx) const
    {
        return myImpl->columnType(idx);
    }
    NO_DISCARD int columnBytes(int idx) const
    {
        return myImpl->columnBytes(idx);
    }

    // The original text of the sql statement. Do not keep a pointer to it.
    const char* sql() const
    {
        return myImpl->sql();
    }

    // Add more binds as needed
    // This binds a NULL
    bool bind(int idx, null_tag_t)
    {
        return myImpl->bind(idx, null_tag_t{});
    }
    bool bind(int idx, const UT_StringRef &value)
    {
        return myImpl->bind(idx, value);
    }
    bool bind(int idx, const char *value)
    {
        return myImpl->bind(idx, value);
    }
    bool bind(int idx, int value)
    {
        return myImpl->bind(idx, value);
    }
    bool bind(int idx, int64 value)
    {
        return myImpl->bind(idx, value);
    }
    bool bind(int idx, bool value)
    {
        return myImpl->bind(idx, value);
    }
    bool bind(int idx, double value)
    {
        return myImpl->bind(idx, value);
    }
    // The following methods bind arrays as strings of the form [#, #, ..., #]
    bool bind(int idx, const UT_IntArray &value)
    {
        return myImpl->bind(idx, value);
    }
    bool bind(int idx, const UT_Int64Array &value)
    {
        return myImpl->bind(idx, value);
    }
    bool bind(int idx, const UT_StringArray &value)
    {
        return myImpl->bind(idx, value);
    }
    // For binding blobs. `size` is required in bytes. Note that this
    // function does not destroy `value`.
    bool bind(int idx, const void *value, int size)
    {
        return myImpl->bind(idx, value, size);
    }
    bool bind(int idx, Blob blob)
    {
        return myImpl->bind(idx, blob);
    }

    template <typename T>
    T get(int idx) const
    {
        static_assert(
                !std::is_same_v<T, void> && std::is_same_v<T, void>,
                "Type not handled.");
        return T();
    }

    // TODO: Try redesigning this to work with the bind that accepts a blob.
    template <typename... Args>
    bool bindAll(Args &&... args)
    { return bindHelper(1, std::forward<Args>(args)...); }

    bool bindNull(int idx)
    { return bind(idx, null_tag_t()); }

    NO_DISCARD bool isValid() const
    {
        return myImpl && myImpl->isValid();
    }

    bool hasRow()
    {
        return myImpl->hasRow();
    }

    // Called when there is nothing to grab after (i.e. INSERT).
    bool run()
    {
        return myImpl->run();
    }
    // Called when there are rows to iterate over (i.e. SELECT).
    bool step()
    {
        return myImpl->step();
    }

    // Methods on this class will always test myError before doing their
    // work. If there is already an error, they will do nothing.
    const UT_ErrorCode &getError() const
    { return myImpl->getError(); }

    bool prepare(const UT_StringRef &sql, UT_ErrorCode &ec);
    bool prepare(
            const UT_SqlStatementHandleId &id,
            const UT_StringRef &sql,
            UT_ErrorCode &ec);

    /// Helper function to create a new statement to run a new sql statement
    template <typename... Args>
    UT_SqlResult execute(const UT_StringRef& sql, Args&&... args)
    {
        UT_ErrorCode ec;
        if (!prepare(sql, ec))
        {
            return UT_SqlResult(this);
        }

        bindAll(std::forward<Args>(args)...);

        step();

        return UT_SqlResult(this);
    }
    /// Helper function to create a new statement to run a new sql statement
    template <typename... Args>
    UT_SqlResult execute(
            const UT_SqlStatementHandleId &id,
            const UT_StringRef &sql,
            Args &&...args)
    {
        UT_ErrorCode ec;
        if (!prepare(id, sql, ec))
            return UT_SqlResult(this);
        bindAll(std::forward<Args>(args)...);

        step();

        return UT_SqlResult(this);
    }

    template <typename... Args>
    std::optional<std::tuple<Args...>> fetchOne(UT_ErrorCode* ec = nullptr)
    {
        constexpr int ColumnCount = sizeof...(Args);

        if (getError())
        {
            if (ec)
                *ec = getError();
            return std::nullopt;
        }

        if (!hasRow())
        {
            if (ec && getError())
                *ec = getError();
            return std::nullopt;
        }

        auto r = fetchRow_<Args...>(std::make_index_sequence<ColumnCount>{});

        if (getError())
        {
            if (ec)
                *ec = getError();
            return std::nullopt;
        }

        /// Move to the next row
        step();

        return r;
    }
    
    template <typename... Args>
    UT_Array<std::tuple<Args...>> fetchAll(UT_ErrorCode* ec = nullptr)
    {
        UT_Array<std::tuple<Args...>> rows;
        if (getError())
        {
            if (ec)
                *ec = getError();
            return rows;
        }

        if (!hasRow())
        {
            if (ec && getError())
                *ec = getError();
            return rows;
        }

        constexpr int ColumnCount = sizeof...(Args);
        auto idx_sequence = std::make_index_sequence<ColumnCount>{};
        do
        {
            rows.emplace_back(fetchRow_<Args...>(idx_sequence));
        } while (step());

        if (getError())
        {
            rows.clear();
            if (ec)
                *ec = getError();
        }

        return rows;
    }

    template <typename... Args>
    std::optional<std::tuple<std::tuple<Args...>>> as(UT_ErrorCode* ec = nullptr)
    {
        if (getError())
        {
            if (ec)
                *ec = getError();
            return std::nullopt;
        }

        if (!hasRow())
        {
            if (ec && getError())
                *ec = getError();
            return std::nullopt;
        }

        constexpr int ColumnCount = sizeof...(Args);
        return fetchRow_<Args...>(std::make_index_sequence<ColumnCount>{});
    }

    NO_DISCARD bool tableExists(const UT_StringRef& name,
            UT_ErrorCode *ec = nullptr) const
    {
        return myImpl->tableExists(name, ec);
    }

    int changes() const { return myImpl->changes(); }

protected:
    bool bindHelper(int)
    { return true; }
    template <typename T, typename... Args>
    bool bindHelper(int idx, T value, Args &&... args)
    {
        if (!bind(idx, value))
            return false;
        return bindHelper(idx + 1, std::forward<Args>(args)...);
    }

    template <class... Args, std::size_t... Idxs>
    std::tuple<Args...>
    fetchRow_(std::index_sequence<Idxs...>)
    {
        return std::make_tuple(get<Args>(Idxs)...);
    }

private:
    UT_SharedPtr<UT_SqlStatementImpl> myImpl;
    const UT_SqlBaseDriver& myDriver;
};

template <>
inline double
UT_SqlStatement::get<double>(int idx) const
{
    return columnAsDouble(idx);
}

template <>
inline UT_SqlStatement::null_tag_t
UT_SqlStatement::get<UT_SqlStatement::null_tag_t>(int idx) const
{
    return columnAsNull(idx);
}

template <>
inline UT_StringHolder
UT_SqlStatement::get<UT_StringHolder>(int idx) const
{
    return columnAsStr(idx);
}

template <>
inline int
UT_SqlStatement::get<int>(int idx) const
{
    return columnAsInt(idx);
}

template <>
inline int64
UT_SqlStatement::get<int64>(int idx) const
{
    return columnAsInt64(idx);
}

template <>
inline bool
UT_SqlStatement::get<bool>(int idx) const
{
    return columnAsBool(idx);
}

template <>
inline const void *
UT_SqlStatement::get<const void *>(int idx) const
{
    int size;
    return columnAsBlob(idx, size);
}

template <>
inline UT_SqlStatement::Blob
UT_SqlStatement::get<UT_SqlStatement::Blob>(int idx) const
{
    return columnAsBlob(idx);
}

inline const UT_ErrorCode &
UT_SqlResult::getError() const
{
    return myStmt->getError();
}

inline void
UT_SqlResult::next()
{
    myStmt->step();
}

inline bool
UT_SqlResult::hasResults() const
{
    return myStmt && myStmt->hasRow();
}

template <typename... Args>
inline std::tuple<Args...>
UT_SqlResult::fetchOne(UT_ErrorCode* ec)
{
    return myStmt->fetchOne<Args...>(ec);
}

template <typename... Args>
inline UT_Array<std::tuple<Args...>>
UT_SqlResult::fetchAll(UT_ErrorCode* ec)
{
    return myStmt->fetchAll<Args...>(ec);
}

template <typename... Args>
inline std::tuple<Args...>
UT_SqlResult::as(UT_ErrorCode* ec)
{
    return myStmt->as<Args...>(ec);
}

class UT_API UT_SqlBaseDriver
{
public:
    virtual ~UT_SqlBaseDriver() = default;
    UT_NON_COPYABLE(UT_SqlBaseDriver);

    virtual void setHostName(const UT_StringHolder& host)
    {
    }
    virtual void setPort(int port)
    {
    }
    virtual void setUserName(const UT_StringHolder& user)
    {
    }
    virtual void setPassword(const UT_StringHolder& password)
    {
    }
    virtual void setDatabaseName(const UT_StringHolder& db_name)
    {
    }
    virtual void setConnectOptions(const UT_Options& options)
    {
    }

    virtual bool connect(UT_ErrorCode* ec = nullptr) = 0;
    virtual bool close(UT_ErrorCode *ec = nullptr) = 0;
    NO_DISCARD virtual bool isValid() const = 0;
    NO_DISCARD virtual bool isReadOnly(
            const char *db = "main",
            UT_ErrorCode *ec = nullptr) const
            = 0;

    NO_DISCARD virtual void* nativeAPI() = 0;
    NO_DISCARD virtual void* nativeAPI() const = 0;

    NO_DISCARD virtual UT_SharedPtr<UT_SqlStatementImpl> createStatementImpl()
            = 0;
    NO_DISCARD virtual UT_SharedPtr<UT_SqlStatementImpl> createStatementImpl()
            const
            = 0;

    /// Check if the specified table exists.
    NO_DISCARD virtual bool tableExists(
            const UT_StringRef &name,
            UT_ErrorCode *ec = nullptr) const
            = 0;
    NO_DISCARD virtual bool indexExists(
            const UT_StringRef &name,
            UT_ErrorCode *ec = nullptr) const
            = 0;
    NO_DISCARD virtual bool viewExists(
            const UT_StringRef &name,
            UT_ErrorCode *ec = nullptr) const = 0;
    NO_DISCARD virtual bool columnExists(
            const UT_StringRef &table_name,
            const UT_StringRef &column_name,
            UT_ErrorCode *ec = nullptr) const
            = 0;
    NO_DISCARD virtual UT_StringHolder errorMessage() const = 0;
    NO_DISCARD virtual int errorCode() const = 0;
    NO_DISCARD virtual int extendedErrorCode() const = 0;

    /// These are primarily used by UT_SqlTransaction (but can be used by any
    /// client code) and provide an abstraction that resembles nested
    /// transations. This is inspired by (and follows the same behaviour as):
    /// https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#beginTransaction()
    virtual bool startTransaction(UT_ErrorCode *ec = nullptr) = 0;
    virtual bool endTransaction(bool commit, UT_ErrorCode *ec = nullptr) = 0;

    /// Get a sql statement that is retrieved from the cache. If the statement
    /// is not already cached then its compiled and then cached if the compiled
    /// statement is valid.
    virtual UT_SharedPtr<UT_SqlStatementImpl> cachedStatement(
            const UT_SqlStatementHandleId &id,
            const UT_StringRef &sql,
            UT_ErrorCode *ec = nullptr) const
            = 0;

    /// Find an sql handle based on its id. The sql statement must have already
    /// been added from cachedStatement(). This method is typically used when
    /// a statement has already been compiled and added to the cache but needs
    /// to be dynamically looked up some time later.
    virtual UT_SharedPtr<UT_SqlStatementImpl> findCachedStatement(
            const UT_SqlStatementHandleId &id) const
            = 0;

    NO_DISCARD virtual UT_StringHolder getSchema(
            UT_ErrorCode *ec = nullptr) const
            = 0;

    /// Helper function to run an sql statement with provided typed args.
    template <typename... Args>
    bool run(UT_ErrorCode *ec, const UT_StringRef &sql, Args &&...args)
    {
        UT_SqlStatement stmt(createStatementImpl());
        if (sizeof...(Args) > 0)
            stmt.bindAll(ec, std::forward<Args>(args)...);
        stmt.step();
        if (ec)
            *ec = stmt.getError();
        return !(bool)stmt.getError();
    }
    /// Returns the number of rows modified, inserted or deleted
    virtual int exec(const UT_StringRef &sql, UT_ErrorCode *ec = nullptr) const
            = 0;

protected:
    UT_SqlBaseDriver() = default;
};

class UT_API UT_SqliteDriver final : public UT_SqlBaseDriver
{
public:
    static constexpr UT_StringLit theFilenameOpt = "SQLITE_FILENAME";
    static constexpr UT_StringLit theBusyTimeoutOpt = "SQLITE_BUSY_TIMEOUT";

    UT_SqliteDriver();
    ~UT_SqliteDriver() override;

    UT_NON_COPYABLE(UT_SqliteDriver);

    void setDatabaseName(const UT_StringHolder& db_name) override
    {
        myFilename = db_name;
    }
    void setConnectOptions(const UT_Options& options) override;

    bool connect(UT_ErrorCode* ec = nullptr) override;
    bool close(UT_ErrorCode *ec = nullptr) override;

    NO_DISCARD SYS_FORCE_INLINE bool isValid() const override
    { return myCtx != nullptr && myFilename.isstring(); }

    NO_DISCARD bool isReadOnly(const char *db = "main",
            UT_ErrorCode *ec = nullptr) const override;

    NO_DISCARD void *nativeAPI() override { return myCtx; }
    NO_DISCARD void *nativeAPI() const override
    {
        return SYSconst_cast(this)->myCtx;
    }

    /// Check the database's "data_version"
    /// (see https://www.sqlite.org/pragma.html#pragma_data_version)
    NO_DISCARD int dataVersion(UT_ErrorCode *ec = nullptr) const;

    /// Get/set the database's "user_version"
    /// (see https://www.sqlite.org/pragma.html#pragma_user_version)
    NO_DISCARD int userVersion(UT_ErrorCode *ec = nullptr) const;
    void setUserVersion(int version, UT_ErrorCode *ec = nullptr) const;

    /// Check if the specified table exists.
    NO_DISCARD bool tableExists(const UT_StringRef& name,
            UT_ErrorCode *ec = nullptr) const override;
    NO_DISCARD bool indexExists(const UT_StringRef& name,
            UT_ErrorCode *ec = nullptr) const override;
    NO_DISCARD bool viewExists(const UT_StringRef& name,
            UT_ErrorCode *ec = nullptr) const override;
    NO_DISCARD bool columnExists(
            const UT_StringRef& table_name,
            const UT_StringRef& column_name,
            UT_ErrorCode *ec = nullptr) const override;
    NO_DISCARD UT_StringHolder errorMessage() const override;
    NO_DISCARD int errorCode() const override;
    NO_DISCARD int extendedErrorCode() const override;

    NO_DISCARD UT_StringHolder getSchema(UT_ErrorCode *ec = nullptr) const override;

    /// This sets a busy handler that sleeps for a specified amount of time
    /// when a table is locked. The handler will sleep multiple times until at
    /// least milliseconds of sleeping have accumulated. After the timeout the
    /// handler returns 0 which causes the step() to return kSQLITE_BUSY.
    void setBusyTimeout(int timeout_ms)
    {
        myBusyTimeout = timeout_ms;
    }

    /// Copy the contents of this database into the provided destination
    /// database.  Return true on success and false otherwise.
    /// On failure, the error code is set in the destination database.
    bool copyTo(UT_SqliteDriver &destination, UT_ErrorCode *ec = nullptr)
            const;

    UT_SharedPtr<UT_SqlStatementImpl> createStatementImpl() override;
    UT_SharedPtr<UT_SqlStatementImpl> createStatementImpl() const override;

    /// Get a sql statement that is retrieved from the cache. If the statement
    /// is not already cached then its compiled and then cached if the compiled
    /// statement is valid.
    UT_SharedPtr<UT_SqlStatementImpl> cachedStatement(
            const UT_SqlStatementHandleId &id,
            const UT_StringRef& sql,
            UT_ErrorCode *ec = nullptr) const override;

    /// Find an sql handle based on its id. The sql statement must have already
    /// been added from cachedStatement(). This method is typically used when
    /// a statement has already been compiled and added to the cache but needs
    /// to be dynamically looked up some time later.
    UT_SharedPtr<UT_SqlStatementImpl> findCachedStatement(
            const UT_SqlStatementHandleId& id) const override;

    /// These are primarily used by UT_SqlTransaction (but can be used by any
    /// client code) and provide an abstraction that resembles nested
    /// transations. This is inspired by (and follows the same behaviour as):
    /// https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#beginTransaction()
    bool startTransaction(UT_ErrorCode *ec = nullptr) override;
    bool endTransaction(bool commit, UT_ErrorCode *ec = nullptr) override;

    int exec(const UT_StringRef &sql, UT_ErrorCode *ec = nullptr)
            const override;
protected:
    bool schemaItemExists(
            const UT_StringRef& type,
            const UT_StringRef& name,
            UT_ErrorCode *ec) const;

private:
    bool verifyOpen(
            const UT_StringHolder &filename,
            UT_ErrorCode *ec);

    mutable std::map<UT_SqlStatementHandleId,
             UT_SharedPtr<UT_SqlStatementImpl>> myCachedStatements;
    sqlite3 *myCtx = nullptr;
    SYS_AtomicCounter myActiveTransactionDepth;
    bool myShouldCommitTransaction;

    UT_StringHolder myFilename;
    int myBusyTimeout = -1;
};

UT_API extern UT_UniquePtr<UT_SqlBaseDriver> UTsqliteFactory();

class UT_API UT_SqlDatabase
{
public:
    using driver_factory_t = UT_UniquePtr<UT_SqlBaseDriver>(*)();

    UT_SqlDatabase(driver_factory_t factory = UTsqliteFactory) :
        myDriver(factory())
    {}

    UT_SqlBaseDriver* driver()
    {
        return myDriver.get();
    }
    const UT_SqlBaseDriver* driver() const
    {
        return myDriver.get();
    }

    void setHostName(const UT_StringRef& host)
    {
        myDriver->setHostName(host);
    }
    void setPort(int port)
    {
        myDriver->setPort(port);
    }
    void setUserName(const UT_StringRef& user)
    {
        myDriver->setUserName(user);
    }
    void setPassword(const UT_StringRef& password)
    {
        myDriver->setPassword(password);
    }
    void setDatabaseName(const UT_StringRef& db_name)
    {
        myDriver->setDatabaseName(db_name);
    }
    void setConnectOptions(const UT_Options& options)
    {
        myDriver->setConnectOptions(options);
    }

    /// Close the sql connection to the db backend
    bool close(UT_ErrorCode *ec = nullptr) { return myDriver->close(ec); }
    /// Create a db connection with a custom db backend
    bool connect(UT_ErrorCode *ec = nullptr)
    {
        return myDriver && myDriver->connect(ec);
    }
    /// Check the underlying connection is valid and usable
    NO_DISCARD bool isValid() const
    {
        return myDriver && myDriver->isValid();
    }
    NO_DISCARD bool isReadOnly(
            const char *db = "main",
            UT_ErrorCode *ec = nullptr) const
    {
        return myDriver->isReadOnly(db, ec);
    }
    NO_DISCARD UT_SharedPtr<UT_SqlStatementImpl> createStatementImpl()
    {
        return myDriver->createStatementImpl();
    }
    NO_DISCARD UT_SharedPtr<UT_SqlStatementImpl> createStatementImpl() const
    {
        return myDriver->createStatementImpl();
    }
    /// Get a sql statement that is retrieved from the cache. If the statement
    /// is not already cached then its compiled and then cached if the compiled
    /// statement is valid.
    UT_SharedPtr<UT_SqlStatementImpl> cachedStatement(
            const UT_SqlStatementHandleId &id,
            const UT_StringRef& sql,
            UT_ErrorCode *ec = nullptr) const
    {
        return myDriver->cachedStatement(id, sql, ec);
    }

    /// Find an sql handle based on its id. The sql statement must have already
    /// been added from cachedStatement(). This method is typically used when
    /// a statement has already been compiled and added to the cache but needs
    /// to be dynamically looked up some time later.
    UT_SharedPtr<UT_SqlStatementImpl> findCachedStatement(
            const UT_SqlStatementHandleId& id) const
    {
        return myDriver->findCachedStatement(id);
    }
    /// Helper function to run an sql statement with provided typed args.
    template <typename... Args>
    bool run(UT_ErrorCode *ec, const UT_StringRef &sql, Args &&... args)
    {
        UT_SqlStatement stmt(createStatementImpl());
        if (sizeof...(Args) > 0)
            stmt.bindAll(ec, std::forward<Args>(args)...);
        stmt.step();
        if (ec)
            *ec = stmt.getError();
        return !(bool)stmt.getError();
    }
    /// Returns the number of rows modified, inserted or deleted
    int exec(const UT_StringRef &sql, UT_ErrorCode *ec = nullptr) const
    {
        return myDriver->exec(sql, ec);
    }

    /// Check if the specified table exists.
    NO_DISCARD bool tableExists(const UT_StringRef& name,
            UT_ErrorCode *ec = nullptr) const
    {
        return myDriver->tableExists(name, ec);
    }
    NO_DISCARD bool indexExists(const UT_StringRef& name,
            UT_ErrorCode *ec = nullptr) const
    {
        return myDriver->indexExists(name, ec);
    }
    NO_DISCARD bool viewExists(const UT_StringRef& name,
            UT_ErrorCode *ec = nullptr) const
    {
        return myDriver->viewExists(name, ec);
    }
    NO_DISCARD bool columnExists(
            const UT_StringRef& table_name,
            const UT_StringRef& column_name,
            UT_ErrorCode *ec = nullptr) const
    {
        return myDriver->columnExists(table_name, column_name, ec);
    }
    NO_DISCARD UT_StringHolder errorMessage() const
    {
        return myDriver->errorMessage();
    }
    NO_DISCARD int errorCode() const
    {
        return myDriver->errorCode();
    }
    NO_DISCARD int extendedErrorCode() const
    {
        return myDriver->extendedErrorCode();
    }

    /// These are primarily used by UT_SqlTransaction (but can be used by any
    /// client code) and provide an abstraction that resembles nested transations.
    /// This is inspired by (and follows the same behaviour as):
    /// https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#beginTransaction()
    bool startTransaction(UT_ErrorCode *ec = nullptr)
    {
        return myDriver->startTransaction(ec);
    }
    bool endTransaction(bool commit, UT_ErrorCode *ec = nullptr)
    {
        return myDriver->endTransaction(commit, ec);
    }

    NO_DISCARD UT_StringHolder getSchema(UT_ErrorCode *ec = nullptr) const
    {
        return myDriver->getSchema(ec);
    }

    NO_DISCARD void* nativeAPI() { return myDriver->nativeAPI(); }
    NO_DISCARD void* nativeAPI() const { return myDriver->nativeAPI(); }

protected:
    UT_UniquePtr<UT_SqlBaseDriver> myDriver;
};

// Transactions are a way to group SQL statements.
// This class ensures we properly setup and execute the group (or rollback if needed)
//
// NOTE: If commit() is not explicitly called before the object is destroyed
//       (e.g., goes out of scope), the transaction will be aborted (rolled back)
//
class UT_API UT_SqlTransaction
{
public:
    UT_SqlTransaction(UT_SqlDatabase &connection, UT_ErrorCode *ec = nullptr);
    ~UT_SqlTransaction();
    UT_NON_COPYABLE(UT_SqlTransaction);

    void commit(UT_ErrorCode *ec = nullptr);

private:
    bool myCommited;
    UT_SqlDatabase &myConnection;
};

#endif // __UT_SQL_H__