The mainpage documentation

---

License

Boost Software License - Version 1.0 - 2016/10/06

Copyright (c) 2016 dorgon chang http://dorgon.horizon-studio.net/

Permission is hereby granted, free of charge, to any person or organization obtaining a copy of the software and accompanying documentation covered by this license (the "Software") to use, reproduce, display, distribute, execute, and transmit the Software, and to prepare derivative works of the Software, and to permit third-parties to whom the Software is furnished to do so, all subject to the following:

The copyright notices in the Software and this entire statement, including the above license grant, this restriction and the following disclaimer, must be included in all copies of the Software, in whole or in part, and all derivative works of the Software, unless such copies or derivative works are solely in the form of machine-executable object code generated by a source language processor.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, TITLE AND NON-INFRINGEMENT. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR ANYONE DISTRIBUTING THE SOFTWARE BE LIABLE FOR ANY DAMAGES OR OTHER LIABILITY, WHETHER IN CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

---

Introduction

The goal of this plugin is to provide ORM ability for database access.

Basically this Plugin is a wrapper for SOCI.

What is SOCI? It is a C++ Database Access Library, you can check detail here.

Please check DemoProject for how to use this plugin.

Support Email: dorgo.nosp@m.nman.nosp@m.@hotm.nosp@m.ail..nosp@m.com

Blog: http://dorgon.horizon-studio.net/

UE4 MarketPlace: https://www.unrealengine.com/marketplace/profile/dorgon%20chang

---

Current Database backend implementation status

DB2: not implemented

Firebird: not implemented

MySQL: not implemented

ODBC: not implemented

Oracle: not implemented

PostgreSQL: not implemented

Sqlite3: implemented

---

Tested Platform

Tested Engine veriosn: 4.13.

Windows: tested.

MACOSX: Failed. need modify engine(Engine/Source/Programs/UnrealBuildTool/Mac/MacToolChain.cs) to enable rtti.

Android: tested, need cherry pick this commit and rebuild engine source code.

iOS: not tested, need cherry pick this commit and rebuild engine source code.

Linux: not tested.

---

TestCase: ORMTestImplement

static void ORMTestImplement(FAutomationTestBase* pTestCase, AHorizonDatabase* pDB) 
{
    pTestCase->AddLogItem("ORMTestImplement start");
    auto pStruct = FHorizonTestDBTable1::StaticStruct();


    //============================create database==============================
    pDB->DropTable(FHorizonTestDBTable1::StaticStruct()->GetName());
    UHorizonTestDBTable1FunctionLibrary::CreateTable(pDB);
    pTestCase->AddLogItem("ORMTestImplement CreateTable");
    //============================test data===================================
    FHorizonTestDBTable1BulkData bulkData;
    FHorizonTestDBTable1 a0;
    a0.Id = 0;
    a0.TestString = "test a0";
    a0.TestFloat = 1.333f;
    a0.bTest1 = true;
    FHorizonTestDBTable1 a1;
    a1.Id = 1;
    a1.TestString = "test a1";
    a1.TestFloat = 2.3333f;
    a1.bTest1 = false;
    UHorizonTestDBTable1FunctionLibrary::AddBulkData(bulkData, a0);
    UHorizonTestDBTable1FunctionLibrary::AddBulkData(bulkData, a1);
    //auto insertSQL = AHorizonDatabase::GetInsertSQLUseStmt(pStruct, false);

    pTestCase->AddLogItem("ORMTestImplement2 start insert row");
    //============================insert row==================================

    try {
        UHorizonTestDBTable1FunctionLibrary::InsertBulkData(pDB, bulkData);
    }
    catch (std::exception& e) {
        pTestCase->AddError(FString::Printf(TEXT("insertSQL exception: %s"), *FString(e.what())));
    }
    pTestCase->AddLogItem("end insert row");
    //============================select single row==================================
    {
        auto data0 = UHorizonTestDBTable1FunctionLibrary::QueryData(pDB, "WHERE Id = 0");
        pTestCase->TestEqual(TEXT("data0 == a0"), data0, a0);
        auto data1 = UHorizonTestDBTable1FunctionLibrary::QueryData(pDB, "WHERE Id = 1");
        pTestCase->TestEqual(TEXT("data1 == a1"), data1, a1);
    }
    //=======================================================================
    pTestCase->AddLogItem("end select single row");

    //============================select multi row==================================
    {
        auto rowSet = UHorizonTestDBTable1FunctionLibrary::QueryMultiData(pDB);
        pTestCase->TestEqual(TEXT("rowSet.Num() == 2"), rowSet.Num(), 2);
        pTestCase->TestEqual(TEXT("rowSet[0] == a0"), rowSet[0], a0);
        pTestCase->TestEqual(TEXT("rowSet[1] == a1"), rowSet[1], a1);
    }


    pTestCase->AddLogItem("end select multi row");
    //============================select single tuple: C++ only=======================================

    {
        enum { Id, TestString };
        std::tuple<int, FString> dataTuple;

        pDB->QueryData(FHorizonTestDBTable1::StaticStruct()->GetName(), "Id, TestString", dataTuple, "WHERE Id = 0");
        pTestCase->TestEqual(TEXT("Id should equal to a0.Id"), std::get<Id>(dataTuple), a0.Id);
        pTestCase->TestEqual(TEXT("TestString should equal to a0.TestString"), std::get<TestString>(dataTuple), a0.TestString);
    }

    pTestCase->AddLogItem("end select single tuple");
    //============================select multi tuple: C++ only=======================================

    {
        enum { Id, TestString };
        std::tuple<int, FString> dataTuple;

        auto rowSet = pDB->QueryMultiData<decltype(dataTuple)>(FHorizonTestDBTable1::StaticStruct()->GetName(), "Id, TestString", "");
        auto rowIt = rowSet.begin();
        pTestCase->TestEqual(TEXT("Id should equal to a0.Id"), std::get<Id>(*rowIt), a0.Id);
        pTestCase->TestEqual(TEXT("TestString should equal to a0.TestString"), std::get<TestString>(*rowIt), a0.TestString);
        rowIt++;
        pTestCase->TestEqual(TEXT("Id should equal to a1.Id"), std::get<Id>(*rowIt), a1.Id);
        pTestCase->TestEqual(TEXT("TestString should equal to a1.TestString"), std::get<TestString>(*rowIt), a1.TestString);

    }

    pTestCase->AddLogItem("end select multi tuple");
    //=============================UpdateData===============================================

    {
        UHorizonTestDBTable1FunctionLibrary::UpdateData(pDB, "TestString='abc', TestFloat=4.3333", "WHERE Id = 0");
        auto data0 = UHorizonTestDBTable1FunctionLibrary::QueryData(pDB, "WHERE Id = 0");
        pTestCase->TestNotEqual(TEXT("data0 != a0"), data0, a0);
    }

    pTestCase->AddLogItem("end UpdateData");
    //=================================DeleteData======================================

    {
        UHorizonTestDBTable1FunctionLibrary::DeleteData(pDB, "WHERE Id = 0");
        auto rowSet = UHorizonTestDBTable1FunctionLibrary::QueryMultiData(pDB);
        pTestCase->TestEqual(TEXT("rowSet.Num() == 1"), rowSet.Num(), 1);
    }
    {
        UHorizonTestDBTable1FunctionLibrary::DeleteData(pDB, "WHERE Id = 1");
        auto rowSet = UHorizonTestDBTable1FunctionLibrary::QueryMultiData(pDB);
        pTestCase->TestEqual(TEXT("rowSet.Num() == 0"), rowSet.Num(), 0);
    }

    pTestCase->AddLogItem("ORMTestImplement2 logitem");
    {
        try
        {
            { //insert test data
                UHorizonTestDBTable1FunctionLibrary::InsertBulkData(pDB, bulkData);
                auto rowSet = UHorizonTestDBTable1FunctionLibrary::QueryMultiData(pDB);
                pTestCase->TestEqual(TEXT("rowSet.Num() == 2"), rowSet.Num(), 2);
            }
            pDB->DeleteData(FHorizonTestDBTable1::StaticStruct()->GetName(), "");
            auto rowSet = UHorizonTestDBTable1FunctionLibrary::QueryMultiData(pDB);
            pTestCase->TestEqual(TEXT("rowSet.Num() == 0"), rowSet.Num(), 0);
        }
        catch (std::exception& e) {
            pTestCase->AddError(FString::Printf(TEXT("insertSQL exception: %s"), *FString(e.what())));
        }
    }
    pTestCase->AddLogItem("end DeleteData");
    //=================================TruncateTable======================================
    {
        { //insert test data
            UHorizonTestDBTable1FunctionLibrary::InsertBulkData(pDB, bulkData);
            auto rowSet = UHorizonTestDBTable1FunctionLibrary::QueryMultiData(pDB);
            pTestCase->TestEqual(TEXT("rowSet.Num() == 2"), rowSet.Num(), 2);
        }
        pDB->TruncateTable(FHorizonTestDBTable1::StaticStruct()->GetName());
        auto rowSet = UHorizonTestDBTable1FunctionLibrary::QueryMultiData(pDB);
        pTestCase->TestEqual(TEXT("rowSet.Num() == 0"), rowSet.Num(), 0);
    }

    pTestCase->AddLogItem("end TruncateTable");
    //================================DropTable============================================
    {
        try
        {
            pTestCase->TestEqual(TEXT("bTableExists == true"), pDB->IsTableExists(FHorizonTestDBTable1::StaticStruct()->GetName()), true);
            pDB->DropTable(FHorizonTestDBTable1::StaticStruct()->GetName());
            pTestCase->TestEqual(TEXT("bTableExists == false"), pDB->IsTableExists(FHorizonTestDBTable1::StaticStruct()->GetName()), false);
        }
        catch (std::exception& e) {
            pTestCase->AddError(FString::Printf(TEXT("drop table exception: %s"), *FString(e.what())));
        }
    }
    pTestCase->AddLogItem("end DropTable");
}

---

---

TestCase: SimpleTestImplement

static void SimpleTestImplement(FAutomationTestBase* pTestCase, AHorizonDatabase* pDB) 
{
    pDB->ExecuteSQL("drop table if exists TestCase1");
    pDB->ExecuteSQL("create table TestCase1("
        "id integer,"
        "name varchar(100))");
    pDB->ExecuteSQL("insert into TestCase1(id, name) values(7, \'John\')");
    // fundamental type
    {
        int myId = 0;
        std::string myName;
        pDB->QueryData("TestCase1", "id", myId, "");

        pTestCase->TestEqual(TEXT("myId should equal to 7"), myId, 7);
        pDB->QueryData("TestCase1", "name", myName, "");
        pTestCase->TestEqual(TEXT("myName should equal to John"), myName, std::string("John"));
    }



    //std::tuple
    {
        enum { id, name };
        std::tuple<int, std::string> dataTuple;

        pDB->QueryData("TestCase1", "id, name", dataTuple, "");
        pTestCase->TestEqual(TEXT("id should equal to 7"), std::get<id>(dataTuple), 7);
        pTestCase->TestEqual(TEXT("name should equal to John"), std::get<name>(dataTuple), std::string("John"));
    }

    {
        enum { id, name };
        std::tuple<int, FString> dataTuple;

        pDB->QueryData("TestCase1", "id, name", dataTuple, "");
        pTestCase->TestEqual(TEXT("id should equal to 7"), std::get<id>(dataTuple), 7);
        pTestCase->TestEqual(TEXT("name should equal to John"), std::get<name>(dataTuple), FString("John"));
    }

    pDB->ExecuteSQL("drop table if exists TestCase1");
}

---

---

TestCase: SociSqlite3, use soci api directly

IMPLEMENT_SIMPLE_AUTOMATION_TEST(FHorizonSqlite3RowID, "Horizon.SOCI.Sqlite3.UnitTest.[sqlite][rowid][oid]", EAutomationTestFlags::ApplicationContextMask | EAutomationTestFlags::ProductFilter)
bool FHorizonSqlite3RowID::RunTest(const FString& Parameters)
{
    bool bResult = true;

    soci::session sql(backEnd, connectString);

    try { sql << "drop table if exists test1"; }
    catch (soci::soci_error const &) {} // ignore if error

    sql <<
        "create table test1 ("
        "    id integer,"
        "    name varchar(100)"
        ")";

    sql << "insert into test1(id, name) values(7, \'John\')";

    soci::rowid rid(sql);
    sql << "select oid from test1 where id = 7", into(rid);

    int id;
    std::string name;

    sql << "select id, name from test1 where oid = :rid",
        soci::into(id), soci::into(name), soci::use(rid);

    FString name2;
    sql << "select id, name from test1 where oid = :rid",
        soci::into(id), soci::into(name2), soci::use(rid);


    TestEqual(TEXT("Id should equal to 7"), id, 7);
    TestEqual(TEXT("Name should equal to John"), name, std::string("John"));

    sql << "drop table test1";
    if (ExecutionInfo.Errors.Num() > 0)
    {
        bResult = false;

    }
    return bResult;
}


// BLOB test
struct blob_table_creator : public table_creator_base
{
    blob_table_creator(soci::session & sql)
        : table_creator_base(sql)
    {
        sql <<
            "create table soci_test ("
            "    id integer,"
            "    img blob"
            ")";
    }
};


IMPLEMENT_SIMPLE_AUTOMATION_TEST(FHorizonSqlite3Blob, "Horizon.SOCI.Sqlite3.UnitTest.[sqlite][blob]", EAutomationTestFlags::ApplicationContextMask | EAutomationTestFlags::ProductFilter)
bool FHorizonSqlite3Blob::RunTest(const FString& Parameters)
{
    bool bResult = true;
    soci::session sql(backEnd, connectString);

    blob_table_creator tableCreator(sql);

    char buf[] = "abcdefghijklmnopqrstuvwxyz";

    sql << "insert into soci_test(id, img) values(7, '')";

    {
        soci::blob b(sql);

        sql << "select img from soci_test where id = 7", soci::into(b);
        TestEqual(TEXT("b.get_len() == 0"), (int)b.get_len(), 0);
        b.write(0, buf, sizeof(buf));
        TestEqual(TEXT("b.get_len() == sizeof(buf)"), b.get_len(), sizeof(buf));
        sql << "update soci_test set img=? where id = 7", soci::use(b);

        b.append(buf, sizeof(buf));
        TestEqual(TEXT("b.get_len() == 2 * sizeof(buf)"), b.get_len(), 2 * sizeof(buf));
        sql << "insert into soci_test(id, img) values(8, ?)", soci::use(b);
    }
    {
        soci::blob b(sql);
        sql << "select img from soci_test where id = 8", soci::into(b);
        TestEqual(TEXT("b.get_len() == 2 * sizeof(buf)"), b.get_len(), 2 * sizeof(buf));
        char buf2[100];
        b.read(0, buf2, 10);
        TestEqual(TEXT("std::strncmp(buf2, \"abcdefghij\", 10) == 0"), std::strncmp(buf2, "abcdefghij", 10), 0);


        sql << "select img from soci_test where id = 7", into(b);
        TestEqual(TEXT("b.get_len() == sizeof(buf)"), b.get_len(), sizeof(buf));

    }

    if (ExecutionInfo.Errors.Num() > 0)
    {
        bResult = false;

    }
    return bResult;
}


// This test was put in to fix a problem that occurs when there are both
// into and use elements in the same query and one of them (into) binds
// to a vector object.

struct test3_table_creator : table_creator_base
{
    test3_table_creator(soci::session & sql) : table_creator_base(sql)
    {
        sql << "create table soci_test( id integer, name varchar, subname varchar);";
    }
};


IMPLEMENT_SIMPLE_AUTOMATION_TEST(FHorizonSqlite3UseAndVectorInfo, "Horizon.SOCI.Sqlite3.UnitTest.[sqlite][use][into][vector]", EAutomationTestFlags::ApplicationContextMask | EAutomationTestFlags::ProductFilter)
bool FHorizonSqlite3UseAndVectorInfo::RunTest(const FString& Parameters)
{
    bool bResult = true;


    soci::session sql(backEnd, connectString);

    test3_table_creator tableCreator(sql);

    sql << "insert into soci_test(id,name,subname) values( 1,'john','smith')";
    sql << "insert into soci_test(id,name,subname) values( 2,'george','vals')";
    sql << "insert into soci_test(id,name,subname) values( 3,'ann','smith')";
    sql << "insert into soci_test(id,name,subname) values( 4,'john','grey')";
    sql << "insert into soci_test(id,name,subname) values( 5,'anthony','wall')";

    {
        std::vector<int> v(10);

        statement s(sql.prepare << "Select id from soci_test where name = :name");

        std::string name = "john";

        s.exchange(use(name, "name"));
        s.exchange(into(v));

        s.define_and_bind();
        s.execute(true);
        TestEqual(TEXT("v.size() == 2"), (int)v.size(), 2);
    }

    if (ExecutionInfo.Errors.Num() > 0)
    {
        bResult = false;

    }
    return bResult;
}


// Test case from Amnon David 11/1/2007
// I've noticed that table schemas in SQLite3 can sometimes have typeless
// columns. One (and only?) example is the sqlite_sequence that sqlite
// creates for autoincrement . Attempting to traverse this table caused
// SOCI to crash. I've made the following code change in statement.cpp to
// create a workaround:

struct test4_table_creator : table_creator_base
{
    test4_table_creator(soci::session & sql) : table_creator_base(sql)
    {
        sql << "create table soci_test (col INTEGER PRIMARY KEY AUTOINCREMENT, name char)";
    }
};


IMPLEMENT_SIMPLE_AUTOMATION_TEST(FHorizonSqlite3Sequence, "Horizon.SOCI.Sqlite3.UnitTest.[sqlite][sequence]", EAutomationTestFlags::ApplicationContextMask | EAutomationTestFlags::ProductFilter)
bool FHorizonSqlite3Sequence::RunTest(const FString& Parameters)
{
    bool bResult = true;
    // we need to have an table that uses autoincrement to test this.
    soci::session sql(backEnd, connectString);

    test4_table_creator tableCreator(sql);

    sql << "insert into soci_test(name) values('john')";
    sql << "insert into soci_test(name) values('james')";

    {
        int key;
        std::string name;
        sql << "select * from soci_test", into(key), into(name);

        TestEqual(TEXT("name == john"), name, std::string("john"));
        rowset<row> rs = (sql.prepare << "select * from sqlite_sequence");
        rowset<row>::const_iterator it = rs.begin();
        row const& r1 = (*it);

        TestEqual(TEXT("r1.get<std::string>(0) == \"soci_test\""), r1.get<std::string>(0), std::string("soci_test"));
        TestEqual(TEXT("r1.get<std::string>(1) == \"2\""), r1.get<std::string>(1), std::string("2"));
    }
    if (ExecutionInfo.Errors.Num() > 0)
    {
        bResult = false;

    }
    return bResult;
}

struct longlong_table_creator : table_creator_base
{
    longlong_table_creator(soci::session & sql)
        : table_creator_base(sql)
    {
        sql << "create table soci_test(val number(20))";
    }
};

IMPLEMENT_SIMPLE_AUTOMATION_TEST(FHorizonSqlite3LongLong, "Horizon.SOCI.Sqlite3.UnitTest.[sqlite][longlong]", EAutomationTestFlags::ApplicationContextMask | EAutomationTestFlags::ProductFilter)
bool FHorizonSqlite3LongLong::RunTest(const FString& Parameters)
{
    bool bResult = true;
    soci::session sql(backEnd, connectString);

    longlong_table_creator tableCreator(sql);

    long long v1 = 1000000000000LL;
    sql << "insert into soci_test(val) values(:val)", use(v1);

    long long v2 = 0LL;
    sql << "select val from soci_test", into(v2);
    TestEqual(TEXT("v2 == v1"), v2, v1);
    if (ExecutionInfo.Errors.Num() > 0)
    {
        bResult = false;

    }
    return bResult;
}

IMPLEMENT_SIMPLE_AUTOMATION_TEST(FHorizonSqlite3VectorLongLong, "Horizon.SOCI.Sqlite3.UnitTest.[sqlite][vector][longlong]", EAutomationTestFlags::ApplicationContextMask | EAutomationTestFlags::ProductFilter)
bool FHorizonSqlite3VectorLongLong::RunTest(const FString& Parameters)
{
    bool bResult = true;
    soci::session sql(backEnd, connectString);

    longlong_table_creator tableCreator(sql);

    std::vector<long long> v1;
    v1.push_back(1000000000000LL);
    v1.push_back(1000000000001LL);
    v1.push_back(1000000000002LL);
    v1.push_back(1000000000003LL);
    v1.push_back(1000000000004LL);

    sql << "insert into soci_test(val) values(:val)", use(v1);

    std::vector<long long> v2(10);
    sql << "select val from soci_test order by val desc", into(v2);



    TestEqual(TEXT("v2.size() == 5"), (int)v2.size(), 5);


    TestEqual(TEXT("v2[0] == 1000000000004LL"), v2[0], 1000000000004LL);
    TestEqual(TEXT("v2[1] == 1000000000003LL"), v2[1], 1000000000003LL);
    TestEqual(TEXT("v2[2] == 1000000000002LL"), v2[2], 1000000000002LL);
    TestEqual(TEXT("v2[3] == 1000000000001LL"), v2[3], 1000000000001LL);
    TestEqual(TEXT("v2[4] == 1000000000000LL"), v2[4], 1000000000000LL);
    if (ExecutionInfo.Errors.Num() > 0)
    {
        bResult = false;

    }
    return bResult;
}

struct table_creator_for_get_last_insert_id : table_creator_base
{
    table_creator_for_get_last_insert_id(soci::session & sql)
        : table_creator_base(sql)
    {
        sql << "create table soci_test(id integer primary key autoincrement)";
        sql << "insert into soci_test (id) values (41)";
        sql << "delete from soci_test where id = 41";
    }
};

IMPLEMENT_SIMPLE_AUTOMATION_TEST(FHorizonSqlite3LastInsert, "Horizon.SOCI.Sqlite3.UnitTest.[sqlite][last-insert-id]", EAutomationTestFlags::ApplicationContextMask | EAutomationTestFlags::ProductFilter)
bool FHorizonSqlite3LastInsert::RunTest(const FString& Parameters)
{
    bool bResult = true;
    soci::session sql(backEnd, connectString);
    table_creator_for_get_last_insert_id tableCreator(sql);
    sql << "insert into soci_test default values";
    long id;
    bool result = sql.get_last_insert_id("soci_test", id);

    TestEqual(TEXT("result == true"), result, true);
    TestEqual(TEXT("id == 42"), id, (long)42);

    if (ExecutionInfo.Errors.Num() > 0)
    {
        bResult = false;

    }
    return bResult;
}