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_Error.h"
#include "UT_ErrorCode.h"
#include "UT_StringHolder.h"
#include "UT_IntrusivePtr.h"

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

#include <map>

class UT_SqlDatabase;

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_SqlStatementHandle
    : public UT_IntrusiveRefCounter<UT_SqlStatementHandle>
{
public:
    UT_SqlStatementHandle(
            const UT_SqlDatabase& db,
            const UT_StringRef& sql,
            UT_ErrorCode& ec);
    ~UT_SqlStatementHandle();

    UT_SqlStatementHandle(const UT_SqlStatementHandle &) = delete;
    UT_SqlStatementHandle &operator=(const UT_SqlStatementHandle &) = delete;

    sqlite3_stmt *stmt() const { return myCtx; }
    const UT_SqlDatabase &db() const { return myDB; }

    bool isValid() const { return myCtx != nullptr; }

    int columnCount() const { return myColumnCount; }

private:
    void finalize();

    const UT_SqlDatabase &myDB;
    sqlite3_stmt *myCtx;
    int myColumnCount;
};

class UT_API UT_SqlStatement
{
public:
    UT_SqlStatement(
            const UT_SqlDatabase& db,
            const UT_StringRef& sql);
    UT_SqlStatement(const UT_IntrusivePtr<UT_SqlStatementHandle>& stmt);
    ~UT_SqlStatement();

    UT_SqlStatement(const UT_SqlStatement &) = delete;
    UT_SqlStatement &operator=(const UT_SqlStatement &) = delete;
    UT_SqlStatement(UT_SqlStatement &&stmt) = delete;
    UT_SqlStatement &operator=(UT_SqlStatement &&stmt) = delete;

    enum DataType
    {
        kUnknown = -1,
        kInteger,
        kFloat,
        kBlob,
        kText,
        kNull
    };

    struct null_tag_t {};

    void reset(bool clear_bindings = false);

    NO_DISCARD int columnAsInt(int idx) const;
    NO_DISCARD bool columnAsBool(int idx) const;
    NO_DISCARD int64 columnAsInt64(int idx) const;
    NO_DISCARD UT_StringHolder columnAsStr(int idx) const;
    NO_DISCARD double columnAsDouble(int idx) const;
    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;
    NO_DISCARD null_tag_t columnAsNull(int idx) const;
    NO_DISCARD UT_StringHolder columnName(int idx) const;
    NO_DISCARD int columnCount() const;
    NO_DISCARD DataType columnType(int idx) const;
    NO_DISCARD int columnBytes(int idx) const;

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

    // Add more binds as needed
    // This binds a NULL
    bool bind(int idx, null_tag_t);
    bool bind(int idx, const UT_StringRef &value);
    bool bind(int idx, const char *value);
    bool bind(int idx, int value);
    bool bind(int idx, int64 value);
    bool bind(int idx, bool value);
    bool bind(int idx, double value);
    // The following methods bind arrays as strings of the form [#, #, ..., #]
    bool bind(int idx, const UT_IntArray &value);
    bool bind(int idx, const UT_Int64Array &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);

    // 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;

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

    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();
    }

    // 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 myError; }

protected:
    // Verify that we have a stepped
    bool verifyHasStepped() const;
    // Verify that the index is a valid index.
    bool verifyIndex(int idx) const;
    bool verifyColumn(int idx) const;

private:
    friend class UT_SqlDatabase;

    int step_();

    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)...);
    }

    UT_IntrusivePtr<UT_SqlStatementHandle> myStmtRef;

    mutable UT_ErrorCode myError;
    bool myHasStepped;
};

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);
}

class UT_API UT_SqlDatabase
{
public:
    /// Open an sqlite object with the provided filename.
    ///  - See setBusyTimeout() for details about busy_timeout.
    ///  - no_raise will avoid raising an exception if an error occurred
    //      while opening the database.
    UT_SqlDatabase(const UT_StringRef &filename,
                   int busy_timeout = 0,
                   UT_ErrorCode *ec = nullptr);
    UT_SqlDatabase();
    ~UT_SqlDatabase();

    UT_SqlDatabase(const UT_SqlDatabase &) = delete;
    UT_SqlDatabase &operator=(const UT_SqlDatabase &) = delete;

    bool close(UT_ErrorCode *ec = nullptr);
    bool open(const UT_StringHolder& filename, UT_ErrorCode *ec = nullptr);

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

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

    /// 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(*this, sql);
        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;

    /// 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;
    NO_DISCARD bool indexExists(const UT_StringRef& name,
            UT_ErrorCode *ec = nullptr) const;
    NO_DISCARD bool viewExists(const UT_StringRef& name,
            UT_ErrorCode *ec = nullptr) const;
    NO_DISCARD bool columnExists(
            const UT_StringRef& table_name,
            const UT_StringRef& column_name,
            UT_ErrorCode *ec = nullptr) const;
    NO_DISCARD UT_StringHolder errorMessage() const;
    NO_DISCARD int errorCode() const;
    NO_DISCARD int extendedErrorCode() const;

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

    /// 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.
    bool setBusyTimeout(int timeout_ms, UT_ErrorCode *ec = nullptr);

    /// 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_SqlDatabase &destination, UT_ErrorCode *ec = nullptr) const;

    /// 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_IntrusivePtr<UT_SqlStatementHandle> cachedStatement(
            const UT_SqlStatementHandleId &id,
            const UT_StringRef& sql,
            UT_ErrorCode *ec = nullptr) const;

    /// 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_IntrusivePtr<UT_SqlStatementHandle> findCachedStatement(
            const UT_SqlStatementHandleId& id) const;

    /// 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);
    bool endTransaction(bool commit, UT_ErrorCode *ec = nullptr);

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);
    friend class UT_SqlStatementHandle;

    mutable std::map<UT_SqlStatementHandleId,
             UT_IntrusivePtr<UT_SqlStatementHandle>> myCachedStatements;
    UT_StringHolder myFilename;
    sqlite3 *myCtx;
    SYS_AtomicCounter myActiveTransactionDepth;
    bool myShouldCommitTransaction;
};

// 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 &db, UT_ErrorCode *ec = nullptr);
    ~UT_SqlTransaction();

    void commit(UT_ErrorCode *ec = nullptr);

private:
    bool myCommited;
    UT_SqlDatabase &myDB;
};

#endif // __UT_SQL_H__