Proof of concept: Saving UObjects to sqlite with reflection

Community Community & Industry Discussion
1

Hi All,

The code I am referring to can be accessed here:

Overview

While working with Unreal, I wanted to be able to save UObjects to a local database, sqlite, but I didn’t want to have to have to write custom code every time I created a new object. Looking at a modern web framework like the Entity Framework, you can apply attributes to a class that will allow the framework to access meta data about your object and save it. The team at Epic have built in a sweet reflection interface for developers and I decided to see if I could use it in a similar fashion.

I have no idea if this should be used in production code, so consider this more of a proof of concept :slight_smile:

The plugin in its current state can save a UObject to an sqlite database if it meets these requirements:

  1. It uses basic data types or a TArray.
  2. It contains a member Id of type int32
  3. The sqlite database used contains a table that matches the class name of the object

Here is an example class:



UCLASS()
class UTestObject : public UObject
{
    GENERATED_UCLASS_BODY()
    
public:
    UPROPERTY()
    int32 Id;
    
    UPROPERTY()
    int32 TestInt;
    
    UPROPERTY()
    float TestFloat;
    
    UPROPERTY()
    bool TestBool;
    
    UPROPERTY()
    FString TestString;
    
    UPROPERTY()
    TArray<int32> TestArray;
};

That class would have the following table created in sqlite:



CREATE TABLE TestObject ( 
  Id INTEGER PRIMARY KEY AUTOINCREMENT, 
  TestInt INTEGER, 
  TestFloat REAL, 
  TestBool NUMERIC, 
  TestString TEXT, 
  TestArray BLOB 
);

Usage

Here is a snippet of how to use the code:



TSharedPtr<SqliteDataResource> DataResource = MakeShareable(new SqliteDataResource(FString(FPaths::GameDir() + "/Data/Test.db")));
DataResource->Acquire();
TSharedPtr<IDataHandler> DataHandler = MakeShareable(new SqliteDataHandler(DataResource));

UTestObject* TestObj = NewObject<UTestObject>();

// Create a record
DataHandler->Create(TestObj);

// Read a record
DatHandler->Read(/**record id*/ 1, TestObj);

// Update a record
TestObj->SomeProperty = "some value";
DataHandler->Update(TestObj);

// Delete a record
DataHandler->Delete(TestObj);

// This shouldn't be necessary since this should be run when the TSharedPtr runs out of references
DataResource->Release();

Notes

Here are some interesting points of the project:

  • I used the testing framework that is in the Unreal Engine. See the test implementation if you are interested in looking at an example of that. To run the rest in the editor, add a sqlite database at $(PROJECT DIR)/Data/Test.db with the TestObject table inside of it. Unit-ish testing inside of a game engine editor. Kind of cool!
  • TArrays are stored as byte arrays in the database. In theory this should work with anything you can throw at it, but I haven’t tried pushing the limits too hard.
  • This has only been tested with sqlite 3.8.5

Enjoy!

2

hi,afuzzyllama .
I am quit intersting in your work on the sqlite, but I did not know who to get it work,would you give me some advice?

3

What problems are you having?

4

Because I use UE4 not long. there are some quit simple problem but very import to me.
1.“Plugins/DataAccess/Source/DataAccess/ThirdParty/Sqlite/3.8.5/” is to Engine or myself game ?
2.Your model file is set it into myself game floder “plugins/”?
3.If i use “SqliteDataResource”,what headfile should include and how ?

Thank you very much for your prompt reply ! :slight_smile:

And I download sqlite version is “3.8.6” .

5

Inside your project directory there should be a “Plugins” folder that was generated. The code in “DataAccess” on github should be into this directory inside of a folder called “DataAccess”.

[ProjectDir]/Plugins/DataAccess/.

The sample model I created is in DataAccess/Source/Private/Tests/TestObject.h. You can create your own object, but it has to contain the required fields as outlined in the README and has to be accessible by the plugin.

The includes you need:



#include "SqliteDataResource.h"
#include "SqliteDataHandler.h"
#include "<YOUR_MODEL>.h"

You also need to add this module as a dependency in your project’s “Build.cs” file. I’ve been putting mine in the “PrivateDependencyModuleNames”



PrivateDependencyModuleNames.AddRange(
                new string] {
                   // Other dependencies... like Core, UObject, Engine....
                    "DataAccess"
                }
            );

HTH!

6

First Thanks for your help,

I got this error:
SqlitDataOperate.cpp.obj : error LNK2019: unresolved external symbol “public: __cdecl SqliteDataResource::SqliteDataResource(class FString)” (??0SqliteDataResource@@QEAA@VFString@@@Z)，referenced in function “public: bool __cdecl SqlitDataOperate::AccessDatabase(void)” (?AccessDatabase@SqlitDataOperate@@QEAA_NXZ)

7

Thanks for your help.
It seems should add “DATAACCESS_API” into file : include “SqliteDataResource.h” include “SqliteDataHandler.h”

8

what should I do to add the ‘Test.db’ to a packaged game to make it work , Especially on mobile platform ?

9

I personally have not used it in a packaged game, so I cannot offer any advise on that. If you know where the db file will reside inside your package on a mobile platform (assuming sqlite works on that platform), you should just able to hard code it in your game or have some other way to configure the path in your product. Worst comes to worst, you can use a preprocessor directive for editor and build to make sure the engine know where exactly to look.

Sorry it isn’t very specific, but it is all I can think of at the moment.