UT_SQLORM.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_SQLORM.h
 *
 * COMMENTS:
 *
 *
 */

#ifndef __UT_SQLORM_H__
#define __UT_SQLORM_H__

#include "UT_API.h"

#include "UT_Array.h"
#include "UT_Debug.h"
#include "UT_Function.h"
#include "UT_Lock.h"
#include "UT_NonCopyable.h"
#include "UT_ORMField.h"
#include "UT_Optional.h"
#include "UT_SGuid.h"
#include "UT_SQL.h"
#include "UT_SharedPtr.h"
#include "UT_StringHolder.h"
#include "UT_ThreadSpecificValue.h"
#include "UT_UniquePtr.h"
#include "UT_WorkBuffer.h"
#include "UT_ORMMigrationResult.h"

#include <variant>
#include <utility>

class UT_SqlOrm;
class UT_IORMOperation;
class UT_IStream;
class UT_OStream;
class UT_ORMMigrationBuilder;

namespace UT
{
/// Error codes to describe ORM errors.
enum class SqlOrmError
{
    UT_ORM_OK,
    UT_ORM_NOT_SUPPORTED,
    UT_ORM_OPERATION_FAILED,
    UT_ORM_INCORRECT_TYPE,
    UT_ORM_BAD_MIGRATION,
    UT_ORM_MIG_MISSING_COL,
    UT_ORM_MIG_INV_COL_TYPE,
    UT_ORM_MIG_INV_COL_PROP,
    UT_ORM_MIG_MISSING
};

UT_API const UT_ErrorCategory& GetOrmErrorCategory();

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

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

template <typename T>
class UT_ORMModel
{
    friend class UT_ORMModelMeta;
public:
    UT_ORMModel() = default;
    virtual ~UT_ORMModel() = default;
    UT_ORMModel(const UT_ORMModel&) = default;
    UT_ORMModel& operator=(const UT_ORMModel&) = default;

    bool save(
            UT_ErrorCode& ec,
            bool force_insert = false,
            bool force_update = false)
    {
        auto&& meta_info = T::metaInfo();
        return meta_info.save(*this, ec, force_insert, force_update);
    }
    void remove(UT_ErrorCode& ec)
    {
        auto&& meta_info = T::metaInfo();
        return meta_info.remove(*this, ec);
    }
    bool update(UT_ErrorCode& ec)
    {
        auto&& meta_info = T::metaInfo();
        return meta_info.update(*this, ec);
    }

    template <typename PK>
    static UT_Optional<T> fetch(const PK& pk, UT_ErrorCode& ec)
    {
        auto&& meta_info = T::metaInfo();
        return meta_info.template fetch<T>(pk, ec);
    }

    static UT_Array<T> fetchAll(UT_ErrorCode& ec)
    {
        auto&& meta_info = T::metaInfo();
        return meta_info.template fetchAll<T>(ec);
    }

    bool hasSavedBefore() const { return myHasSavedBefore; }

protected:
    bool myHasSavedBefore = false;
};

class UT_API UT_ORMModelMeta
{
public:
    UT_ORMModelMeta(const UT_StringHolder& name, UT_SqlOrm* orm = nullptr) :
        myName(name),
        myORM(orm)
    {}
    virtual ~UT_ORMModelMeta() = default;
    UT_NON_COPYABLE(UT_ORMModelMeta);

    /// Must always be called PRIOR to running the application
    void migrate(UT_SqlOrm& orm, UT_ORMMigrationBuilder& builder) const;

    /// Call to build out all of your meta information.
    void build()
    {
        if (!myHasBuilt)
        {
            myHasBuilt = true;

            // Let the child build the meta info
            doBuild();

            // Now that we have the meta info go through it to set cache info.
            // This is to save on lookups and common queries like inserting.
            configureInternals_();
        }
    }
    virtual void doMigrate(UT_ORMMigrationBuilder& builder) const = 0;

    template <typename Cls, typename FieldT>
    void addAutoField(
            const UT_StringHolder& name,
            FieldT Cls::*field,
            unsigned props = UT_ORMColumn::Empty)
    {
        myColumns.emplace_back(
                UT_ORMBasicFieldAdapter<Cls, FieldT>::createColumn(
                        name, field, props | UT_ORMColumn::AutoIncrement));
    }
    template <typename Cls, typename FieldT>
    void addField(
            const UT_StringHolder& name,
            FieldT Cls::*field,
            unsigned props = UT_ORMColumn::Empty)
    {
        myColumns.emplace_back(
                UT_ORMBasicFieldAdapter<Cls, FieldT>::createColumn(
                        name, field, props));
    }
    template <typename Cls, typename ForeignModel>
    void addField(
            const UT_StringHolder& name,
            UT_ORMForeignKeyField<ForeignModel> Cls::*field,
            unsigned props = UT_ORMColumn::Empty,
            UT_ORMColumn::OnDelete ondelete_type
            = UT_ORMColumn::OnDelete::DoNothing)
    {
        using adapter_t = UT_ORMForeignKeyFieldAdapter<
                Cls, UT_ORMForeignKeyField<ForeignModel>>;
        myColumns.emplace_back(
                adapter_t::createColumn(name, field, props, ondelete_type));
    }

    const UT_StringHolder& tableName() const { return myName; }
    const UT_ORMFieldColumn* primaryKey() const
    {
        for (auto&& col : myColumns)
        {
            if (col.isPrimaryKey())
            {
                return &col;
                break;
            }
        }
        return nullptr;
    }
    const UT_Array<UT_ORMFieldColumn>& columns() const { return myColumns; }

    template <typename T>
    bool save(
            T& obj,
            UT_ErrorCode& ec,
            bool force_insert = false,
            bool force_update = false) const;
    template <typename T>
    void remove(T& obj, UT_ErrorCode& ec) const;
    template <typename T>
    bool update(T& obj, UT_ErrorCode& ec) const;
    template <typename T, typename PK>
    UT_Optional<T> fetch(const PK& pk, UT_ErrorCode& ec) const;
    template <typename T>
    UT_Array<T> fetchAll(UT_ErrorCode& ec) const;

protected:
    virtual void doBuild() = 0;

    template <typename T>
    bool loadObject_(T& obj, UT_SqlStatement& stmt, UT_ErrorCode& ec) const
    {
        for (int i = 0; i < myColumns.size(); i++)
        {
            const UT_ORMFieldColumn& column = myColumns[i];
            if (UT_IORMFieldAdapter* adapter = column.adapter(); adapter)
            {
                adapter->load(&obj, stmt, i, ec);
                if (ec)
                    return false;
            }
            else
            {
                ec = UTmakeErrorCode(UT::SqlOrmError::UT_ORM_OPERATION_FAILED);
                UT_ASSERT(!"Type not handled");
            }
        }

        // If the object is being loaded from the db then at some point it
        // was saved.
        obj.myHasSavedBefore = true;
        return true;
    }

    /// Configure any cacheable information that is used often.
    void configureInternals_();

    bool myHasBuilt = false;
    bool myIsUsingAutoIncPK = false;
    UT_StringHolder myName;
    UT_Array<UT_ORMFieldColumn> myColumns;
    UT_SqlOrm* myORM = nullptr;
};

class UT_API UT_SqlOrm
{
public:
    explicit UT_SqlOrm(const UT_StringHolder& db_path)
    {
        configure(db_path);
    }
    UT_SqlOrm() = default;
    virtual ~UT_SqlOrm() = default;
    UT_NON_COPYABLE(UT_SqlOrm);

    void configure(const UT_StringHolder& db_path)
    {
        myDBPath = db_path;
    }

    UT_SqlStatement cursor() const
    {
        SYSconst_cast(this)->ensureConnection_();
        return UT_SqlStatement(myDBLocal.local());
    }

    UT_SqlTransaction transaction(UT_ErrorCode* ec = nullptr)
    {
        ensureConnection_(ec);
        return UT_SqlTransaction(myDBLocal.local(), ec);
    }

    void migrate(UT_ORMModelMeta* meta, UT_ORMMigrationResult& result);
    void migrate(UT_ORMMigrationResult& result);

    void close(UT_ErrorCode* ec = nullptr);

    template <typename T>
    void registerModelMeta()
    {
        auto&& meta_info = T::metaInfo();
        for (auto&& m : myRegisteredMetas)
        {
            if (m == &meta_info)
                return;
        }

        myRegisteredMetas.emplace_back(&meta_info);
    }

protected:
    friend class UT_SqlOrmTable;

    void doMigrate_(
            UT_ORMMigrationBuilder& builder,
            UT_ORMMigrationResult& result);

    static UT_UniquePtr<UT_IORMOperation> createOperation(
            const UT_StringRef& type);

    void ensureConnection_(UT_ErrorCode* ec = nullptr);

    UT_StringHolder myDBPath;
    UT_ThreadSpecificValue<UT_SqlDatabase> myDBLocal;

    SYS_AtomicInt32 myHasConfiguredInternals;

    UT_Array<const UT_ORMModelMeta*> myRegisteredMetas;
};

template <typename T>
bool
UT_ORMModelMeta::save(
        T& obj,
        UT_ErrorCode& ec,
        bool force_insert,
        bool force_update) const
{
    // If this model uses a auto increment pk and the model instance has already
    // been saved (ie. record exists and pk value is valid) then it only
    // makes sense to use update.
    if (obj.hasSavedBefore())
    {
        if (myIsUsingAutoIncPK)
            force_update = true;
    }
    else if (myIsUsingAutoIncPK)
        force_insert = true;

    if (force_update)
    {
        return update(obj, ec);
    }

    UT_WorkBuffer sql_query;
    if (force_insert)
    {
        sql_query = "INSERT INTO ";
    }
    else
    {
        sql_query = "INSERT OR REPLACE INTO ";
    }

    UT_Array<const UT_ORMFieldColumn*> values;
    UT_Array<const UT_ORMFieldColumn*> outputs;

    for (auto&& col : myColumns)
    {
        if (col.isAutoIncrement())
            outputs.emplace_back(&col);
        else
            // When the value is auto incremented we need to capture the value
            // as the current value is likely not correct to the DB.
            values.emplace_back(&col);
    }

    sql_query.append(tableName());
    sql_query.append(" (");
    for (exint i = 0; i < values.size(); i++)
    {
        const UT_ORMFieldColumn* col = values[i];
        if (!col->isAutoIncrement())
        {
            if (i != 0)
                sql_query.append(',');
            sql_query.append(col->name());
        }
    }
    sql_query.append(')');


    sql_query.append(" VALUES (");
    for (exint i = 0; i < values.size(); i++)
    {
        const UT_ORMFieldColumn* col = values[i];
        if (!col->isAutoIncrement())
        {
            if (i != 0)
                sql_query.append(',');
            sql_query.append('?');
        }
    }
    sql_query.append(')');

    if (outputs.size() > 0)
    {
        sql_query.append(" RETURNING ");
        // Add any outputs we might need to capture
        for (exint i = 0, c = outputs.size(); i < c; i++)
        {
            if (i != 0)
                sql_query.append(',');
            sql_query.appendFormat("{}", outputs[i]->name());
        }
    }
    sql_query.append(';');

    UT_ASSERT(myORM);
    UT_SqlStatement stmt(myORM->cursor());

    if (stmt.getError())
    {
        ec = stmt.getError();
        return false;
    }

    if (!stmt.prepare(sql_query, ec))
        return false;
    for (int i = 0; i < values.size(); i++)
    {
        const UT_ORMFieldColumn* col = values[i];
        if (!col->isAutoIncrement())
        {
            if (UT_IORMFieldAdapter* adapter = col->adapter(); adapter)
                adapter->bind(&obj, stmt, i+1, ec);
            else
            {
                // TODO: error
            }
            if (ec)
                return false;
        }
    }

    stmt.step();
    
    int changes = stmt.changes();

    // If the insert was a success load any outputs into the columns.
    if (changes > 0 && outputs.size() > 0)
    {
        for (exint i = 0, c= outputs.size(); i < c; i++)
        {
            const UT_ORMFieldColumn* output = outputs[i];
            if (UT_IORMFieldAdapter* adapter = output->adapter(); adapter)
            {
                adapter->load(&obj, stmt, i, ec);
            }
        }

        stmt.step();
    }

    if (stmt.getError())
        ec = stmt.getError();

    return changes > 0;
}

template <typename T>
void
UT_ORMModelMeta::remove(T& obj, UT_ErrorCode& ec) const
{
    const UT_ORMFieldColumn* column = primaryKey();

    UT_WorkBuffer sql_query;
    sql_query = "DELETE FROM ";
    sql_query.append(tableName());
    sql_query.append(" WHERE ");
    sql_query.append(column->name());
    sql_query.append("=?");

    UT_SqlStatement stmt(myORM->cursor());
    if (stmt.getError())
    {
        ec = stmt.getError();
        return;
    }

    if (!stmt.prepare(sql_query, ec))
        return;
    if (UT_IORMFieldAdapter* adapter = column->adapter(); adapter)
        adapter->bind(&obj, stmt, 1, ec);
    else
    {
        // TODO: error
    }
    stmt.run();
    if (stmt.getError())
    {
        ec = stmt.getError();
        return;
    }

    // Object has been removed from the db let the c++ object know in the
    // event it has to do some extra work
    for (auto&& meta_column : myColumns)
    {
        UT_IORMFieldAdapter* adapter = column->adapter();
        adapter->onDelete(&obj, meta_column.onDelete(), ec);

        if (ec)
            return;
    }
}

template <typename T>
bool
UT_ORMModelMeta::update(T& obj, UT_ErrorCode& ec) const
{
    UT_WorkBuffer sql_query;
    sql_query = "UPDATE ";
    sql_query.append(tableName());
    sql_query.append(" SET ");

    for (exint i = 0; i < myColumns.size(); i++)
    {
        if (myColumns[i].isPrimaryKey() || myColumns[i].isUnique())
            continue;

        if (i != 0)
            sql_query.append(',');
        sql_query.append(myColumns[i].name());
        sql_query.append("=?");
    }
    sql_query.append(" WHERE ");

    exint count = 0;
    for (exint i = 0; i < myColumns.size(); i++)
    {
        if (myColumns[i].isUnique() || myColumns[i].isPrimaryKey())
        {
            if (count > 0)
            {
                sql_query.append(" AND ");
            }
            sql_query.append(myColumns[i].name());
            sql_query.append("=?");
            count++;
        }
    }
    sql_query.append(";");

    UT_SqlStatement stmt(myORM->cursor());

    if (stmt.getError())
    {
        ec = stmt.getError();
        return false;
    }

    if (!stmt.prepare(sql_query, ec))
        return false;

    // Bind the values
    int index = 0;
    for (int i = 0; i < myColumns.size(); i++)
    {
        if (myColumns[i].isPrimaryKey() || myColumns[i].isUnique())
            continue;
        index++;
        if (UT_IORMFieldAdapter* adapter = myColumns[i].adapter(); adapter)
            adapter->bind(&obj, stmt, index, ec);
        else
        {
            // TODO: error
        }
        if (ec)
            return false;
    }
    // Bind the primary key conditions
    for (int i = 0; i < myColumns.size(); i++)
    {
        if (myColumns[i].isPrimaryKey() || myColumns[i].isUnique())
        {
            index++;
            if (UT_IORMFieldAdapter* adapter = myColumns[i].adapter(); adapter)
                adapter->bind(&obj, stmt, index, ec);
            else
            {
                // TODO: error
            }
            if (ec)
                return false;
        }
    }

    stmt.run();
    if (stmt.getError())
        ec = stmt.getError();

    if (stmt.changes() > 0)
        obj.myHasSavedBefore = true;

    return stmt.changes() > 0;
}

template <typename T, typename PK>
UT_Optional<T>
UT_ORMModelMeta::fetch(const PK& pk, UT_ErrorCode& ec) const
{
    UT_WorkBuffer sql_query;
    sql_query = "SELECT ";
    for (exint i = 0; i < myColumns.size(); i++)
    {
        if (i != 0)
            sql_query.append(',');
        sql_query.append(' ');
        sql_query.append(myColumns[i].name());
    }
    sql_query.append(" FROM ");
    sql_query.append(tableName());
    sql_query.append(" WHERE ");
    sql_query.append(primaryKey()->name());
    sql_query.append("=?");
    sql_query.append(';');

    UT_SqlStatement stmt(myORM->cursor());

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

    if (!stmt.prepare(sql_query, ec))
        return std::nullopt;

    stmt.bindAll(pk);

    stmt.step();

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

    if (!stmt.hasRow())
        return std::nullopt;

    T item;
    if (loadObject_(item, stmt, ec))
        return item;

    return std::nullopt;
}

template <typename T>
UT_Array<T>
UT_ORMModelMeta::fetchAll(UT_ErrorCode& ec) const
{
    UT_WorkBuffer sql_query;
    sql_query.append("SELECT");
    for (exint i = 0; i < myColumns.size(); i++)
    {
        if (i != 0)
            sql_query.append(',');
        sql_query.append(' ');
        sql_query.append(myColumns[i].name());
    }
    sql_query.append(" FROM ");
    sql_query.append(tableName());
    sql_query.append(';');

    UT_SqlStatement stmt(myORM->cursor());
    if (stmt.getError())
    {
        ec = stmt.getError();
        return UT_Array<T>{};
    }

    if (!stmt.prepare(sql_query, ec))
        return UT_Array<T>{};
    UT_Array<T> items;
    while (stmt.step() && !stmt.getError())
    {
        T item;
        if (loadObject_(item, stmt, ec))
            items.emplace_back(std::move(item));
        else
            return UT_Array<T>{};
    }

    return items;
}

#define UT_DECLARE_MODEL()                                                     \
public:                                                                        \
    class Meta;                                                                \
    friend class UT_ORMModelMeta;                                              \
    static const Meta& metaInfo();

#define UT_DEFINE_MODEL(_model_)                                               \
    const _model_::Meta& _model_::metaInfo()                                   \
    {                                                                          \
        static Meta _instance;                                                 \
        return _instance;                                                      \
    }
#endif // __UT_SQLORM_H__