HorizonDatabase.h

Go to the documentation of this file.

/******************************************************
* 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.
**********************************************************/

#pragma once

#include "Object.h"
#include "soci/soci.h"
#if (USE_SOCI_SQLITE3 == 1)
#include "soci/sqlite3/soci-sqlite3.h"
#endif //(USE_SOCI_SQLITE3 == 1)
#include "Horizon/Database/soci/std-type-conversion.h"
#include "Horizon/Database/soci/ue-type-conversion.h"
#include "Horizon/Database/HorizonDatabaseEnum.h"

#include "HorizonDatabase.generated.h"


static const FString HORIZON_SQL_CREATE_TABLE_IF_NOT_EXISTS = "CREATE TABLE IF NOT EXISTS ";
static const FString HORIZON_SQL_DELETE_FROM = "DELETE FROM ";
static const FString HORIZON_SQL_TRANCATE_TABLE_IF_EXISTS = "TRUNCATE TABLE IF EXISTS ";
static const FString HORIZON_SQL_DROP_TABLE_IF_EXISTS = "DROP TABLE IF EXISTS ";
static const FString HORIZON_SQL_INSERT_INTO = "INSERT INTO ";
static const FString HORIZON_SQL_INSERT_OR_REPLACE_INTO = "INSERT OR REPLACE INTO ";
static const FString HORIZON_SQL_SELECT = "SELECT ";
static const FString HORIZON_SQL_FROM = " FROM ";
static const FString HORIZON_SQL_SELECT_ALL_FROM = "SELECT * FROM ";
static const FString HORIZON_SQL_UPDATE = "UPDATE ";
static const FString HORIZON_SQL_SET = " SET ";

static const FString HORIZON_SQL_SELECT_COUNT_ALL_FROM = "SELECT COUNT(*) FROM ";



// All table struct should inherited from this USTRUCT
USTRUCT()
struct HORIZONDATABASE_API FHorizonDatabaseTable
{
    GENERATED_BODY()
};



UCLASS(AutoExpandCategories = "HorizonPlugin|HorizonDatabase")
class HORIZONDATABASE_API AHorizonDatabase : public AActor
{
    GENERATED_BODY()
public:
    AHorizonDatabase();
public:
    virtual void BeginPlay() override;
    virtual void EndPlay(const EEndPlayReason::Type EndPlayReason) override;

public:
    UPROPERTY(EditAnywhere, BlueprintReadWrite, Category = "HorizonPlugin|HorizonDatabase")
        TEnumAsByte<EHorizonDatabaseBackEnd::Type> BackEndType;


    UPROPERTY(EditAnywhere, BlueprintReadWrite, Category = "HorizonPlugin|HorizonDatabase")
    FString ConnectString;


    //Open database when BeginPlay()
    UPROPERTY(EditAnywhere, BlueprintReadWrite, Category = "HorizonPlugin|HorizonDatabase")
        bool bAutoOpen = true;

public://For blueprint
    UFUNCTION(BlueprintCallable, Category = "HorizonPlugin|HorizonDatabase")
        virtual bool Open();
    UFUNCTION(BlueprintCallable, Category = "HorizonPlugin|HorizonDatabase")
        virtual void Close();
    UFUNCTION(BlueprintCallable, Category = "HorizonPlugin|HorizonDatabase")
        bool IsMemoryDB();


    //DELETE FROM tableName condition
    UFUNCTION(BlueprintCallable, Category = "HorizonPlugin|HorizonDatabase")
    void DeleteData(const FString& tableName, const FString& condition = "");

    //TRUNCATE TABLE IF EXISTS tableName
    UFUNCTION(BlueprintCallable, Category = "HorizonPlugin|HorizonDatabase")
    void TruncateTable(const FString& tableName);



    //DROP TABLE IF EXISTS tableName
    UFUNCTION(BlueprintCallable, Category = "HorizonPlugin|HorizonDatabase")
    void DropTable(const FString& tableName);

    UFUNCTION(BlueprintCallable, Category = "HorizonPlugin|HorizonDatabase")
    bool IsTableExists(const FString& tableName);

    UFUNCTION(BlueprintCallable, Category = "HorizonPlugin|HorizonDatabase")
    void UpdateData(const FString& tableName, const FString& updateParam, const FString&  condition = "");



    UFUNCTION(BlueprintCallable, Category = "HorizonPlugin|HorizonDatabase")
    void ExecuteSQL(const FString& sqlStmt);

    UFUNCTION(BlueprintCallable, Category = "HorizonPlugin|HorizonDatabase")
    int GetTableRowCount(const FString& tableName, const FString& condition);

public: //C++
    void CreateTable(UStruct* pSchema);
    static FString GetCreateTableSqlStmt(AHorizonDatabase* pDB, UStruct* pSchema);

    static FString GetInsertBindingSqlStmt(UStruct* pSchema, bool bReplace = true);

    static FString GetUpdateBindingSqlStmt(UStruct* pSchema);
public: //For C++

    template <typename S>
    soci::rowset<S> QueryMultiData(const FString& tableName, const FString& condition)
    {
        FString sqlStmt = FString::Printf(TEXT("%s%s %s"), *HORIZON_SQL_SELECT_ALL_FROM, *tableName, *condition);
        return (*SessionPtr).prepare << TCHAR_TO_UTF8(*sqlStmt);
    }

    template <typename S>
    soci::rowset<S> QueryMultiData(const FString& tableName, const FString& parmName, const FString& condition)
    {
        FString sqlStmt = FString::Printf(TEXT("%s%s%s%s %s"), 
            *HORIZON_SQL_SELECT, *parmName, 
            *HORIZON_SQL_FROM, *tableName, *condition);

        return (*SessionPtr).prepare << TCHAR_TO_UTF8(*sqlStmt);
    }
    template <typename S>
    void QueryData(const FString& tableName, S &outData, const FString& condition)
    {
        FString sqlStmt = FString::Printf(TEXT("%s%s %s"), *HORIZON_SQL_SELECT_ALL_FROM, *tableName, *condition);

        (*SessionPtr) << TCHAR_TO_UTF8(*sqlStmt), soci::into(outData);
    }
    template <typename S>
    void QueryData(const FString& tableName, const FString& parmName, S &outData, const FString& condition)
    {
        if (SessionPtr.IsValid())
        {
            try {
                FString sqlStmt = FString::Printf(TEXT("%s%s%s%s %s"), *HORIZON_SQL_SELECT, *parmName, *HORIZON_SQL_FROM, *tableName, *condition);

                (*SessionPtr) << TCHAR_TO_UTF8(*sqlStmt), soci::into(outData);
            }
            catch (const std::exception& e)
            {
                ensureMsgf(TEXT("AHorizonDatabase::QueryData exception: %s"), *FString(e.what()));
                throw e;
            }
        }
    }

    soci::session& GetSession() { 
        verifyf(SessionPtr.IsValid(), TEXT("SessionPtr is not valid, database session not connected"));
        return *SessionPtr; 
    }

private:
    static FString ConvertToDBDataType(AHorizonDatabase* pDB, const FString& typeName);
private:
    TSharedPtr<soci::session> SessionPtr;
};