[PLUGIN] USQLite

[BrUnO_XaVIeR]

What are the steps you’re doing to build?
Which platform are you building the binaries for?

The plugin should not need to build while you build the engine.
Plugin build is only needed when building project binaries.

[studiodonderslag]

I’m testing the plugin with my game and I can save to the database inside the editor without any issues (The results show up when the session is running). Whenever I stop the session the database is cleared and the rows are lost. How can I prevent the rows from clearing?

I’m on UE 4.27.2 source built and use Generate SQL from Object (Serializable Object) > Add command to Queue > Save All Data (not save Immediate) to save to DB.

[BrUnO_XaVIeR]

@studiodonderslag

How can I prevent the rows from clearing?

Use a persistent DBID for your instances.
You can search this topic for discussions about it.

---

[PLUGIN] USQLite

For spawned actors, you need to control IDs of instances stored in db files. I would not let Unreal use auto generated actor names for spawned houses, I would instead create custom IDs to store them in a db row. You can either use the “Make DB” functions to create a storage ID for a house blueprint, or directly store your own into a string property (of type Name) called "DBID ". [Imgur] I prefere to use the DBID directly from Unreal Editor instead of …

[studiodonderslag]

Thanks but I’m already using a custom DBID name variable in the blueprint and it won’t save. I see my custom DBID being used in the database but it still gets cleared after the session.

[BrUnO_XaVIeR]

@studiodonderslag

I see my custom DBID being used in the database but it still gets cleared after the session.

What is cleared?
The panel or the data in the file?

The in-editor table view only display data for objects that are currently spawned.
You can press “refresh” while the game is playing…

You can install SQLite DB Explorer (it’s free) and check the data outside the Unreal Editor as well, your files will be stored in Project/Saved/ folder while running on Editor.

[studiodonderslag]

@BrUnO_XaVIeR

@studiodonderslag

I see my custom DBID being used in the database but it still gets cleared after the session.

What is cleared?
The panel or the data in the file?

The in-editor table view only display data for objects that are currently spawned.
You can press “refresh” while the game is playing…

My mistake, I presumed the editor table view reflected the entire database at all times.
I checked the DB file in an external editor and all my data is there

Thank you for the quick response. All your plugins make my life a lot easier

[studiodonderslag]

Hi again,

How do i return a struct from the DB? Select Column has no option for structs.

@studiodonderslag

Also there is a strange issue where some rows are written to the wrong database. In my GameMode I write to two different databases but the last one that gets written to also ends up receiving some of the rows of the previously saved database.

Can I e-mail you some screenshots?

I managed to fix the rows being duplicated by forcing a garbage collect between each save. It seems some objects were left trailing in memory after I set them to null in my save routine.

[studiodonderslag]

How do i return a struct from the DB? Select Column has no option for structs.

Does anyone have an answer?

[BrUnO_XaVIeR]

There’s no node to manipulate generic structs on blueprints stored in db, although the loader can read and apply them to owning actor directly when loading data.

There’s just no node with “wildcard support” because that introduce a whole lot of other problems.

[Austin_WGames (Austin James)]

Apologies in the delay!

I’m just trying to do regular plugin build for my project w/ Custom Engine already built from Source. Also building it for LinuxServer

[BrUnO_XaVIeR]

The problem you are having is coming from the SQLite amalgamation file.
That log however is not enough for me to understand why this is happening to your setup.

You can find some info on the amalgamation here, if it helps:

The SQLite Amalgamation

[studiodonderslag]

Hi I’ve got another issue that I thought I fixed. For some reason all previously written row ID’s get copied to subsequent database writes, even to other database files.

This was previously fixed by forcing a garbage update between the saves but as of this morning that doesn’t work anymore. I run all saves sequentially and as a synchronous tasks.

Here is a log:

[2022.06.19-15.13.41:014][ 27]USQLog: {USQL}:: CREATE TABLE IF NOT EXISTS DB_DataStore (ID TEXT PRIMARY KEY NOT NULL,ItemRowName TEXT,NumItems INT,OwnerUID INT,CurrentDurability REAL,MaxDurability REAL,ContainerItemUID INT,ContainerItemRowName TEXT,ContainerItemNumItems INT);

[2022.06.19-15.13.41:014][ 27]USQLog: {USQL}:: [DB_DataStore] DB Table : OK
[2022.06.19-15.13.41:014][ 27]USQLog: {USQL}:: SELECT ID FROM DB_DataStore

[2022.06.19-15.13.41:015][ 27]USQLog: {USQL}:: CREATE TABLE IF NOT EXISTS DB_DataStore (ID TEXT PRIMARY KEY NOT NULL,ItemRowName TEXT,NumItems INT,OwnerUID INT,CurrentDurability REAL,MaxDurability REAL,ContainerItemUID INT,ContainerItemRowName TEXT,ContainerItemNumItems INT);
[2022.06.19-15.13.41:015][ 27]USQLog: {USQL}:: [DB_DataStore] DB Table : OK

[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1793694’,‘UName>>CLOTHING_TSHIRT’,1,1337,0.000000,0.000000,0,‘UName>>None’,0);
[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1865382’,‘UName>>RESOURCE_WOOD’,666,1337,0.000000,0.000000,0,‘UName>>None’,0);
[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1124149’,‘UName>>CLOTHING_TSHIRT’,1,1337,0.000000,0.000000,0,‘UName>>None’,0);
[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1626026’,‘UName>>CLOTHING_TSHIRT’,1,1337,0.000000,0.000000,0,‘UName>>None’,0);
[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1110324’,‘UName>>DRUG_WEED’,5,1337,0.000000,0.000000,0,‘UName>>None’,0);
[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1058320’,‘UName>>RESOURCE_WOOD’,2000,1337,0.000000,0.000000,0,‘UName>>None’,0);
[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1485427’,‘UName>>RESOURCE_WOOD’,2000,1337,0.000000,0.000000,0,‘UName>>None’,0);
[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1594775’,‘UName>>RESOURCE_WOOD’,1300,1337,0.000000,0.000000,0,‘UName>>None’,0);
[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1326456’,‘UName>>RESOURCE_STONE’,1800,1337,0.000000,0.000000,0,‘UName>>None’,0);
[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1615558’,‘UName>>WEAPON_BLOWPIPE’,1,1337,0.000000,0.000000,1820276,‘UName>>AMMO_BLOWDART_SLOW’,1);
[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1820276’,‘UName>>AMMO_BLOWDART_SLOW’,1,1337,0.000000,0.000000,0,‘UName>>None’,0);
[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1202642’,‘UName>>TOOL_PICKAXE’,1,1337,100.000000,0.000000,0,‘UName>>None’,0);
[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1758110’,‘UName>>TOOL_HATCHET’,1,1337,100.000000,0.000000,0,‘UName>>None’,0);
[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1791955’,‘UName>>WEAPON_BLOWPIPE’,1,1337,100.000000,0.000000,1773796,‘UName>>AMMO_BLOWDART_SLOW’,8);
[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1773796’,‘UName>>AMMO_BLOWDART_SLOW’,8,1337,0.000000,0.000000,0,‘UName>>None’,0);
[2022.06.19-15.13.41:027][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1581621’,‘UName>>RIFLE_OK47’,1,1337,100.000000,0.000000,1136875,‘UName>>AMMO_556’,30);
[2022.06.19-15.13.41:029][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_DataStore (ID,ItemRowName,NumItems,OwnerUID,CurrentDurability,MaxDurability,ContainerItemUID,ContainerItemRowName,ContainerItemNumItems) VALUES (‘1136875’,‘UName>>AMMO_556’,30,1337,0.000000,0.000000,0,‘UName>>None’,0);

[2022.06.19-15.13.41:039][ 27]LogBlueprintUserMessages: [BP_GameMode_Server_C_0] Server: Prepare save: DB_DataStore

[2022.06.19-15.13.41:045][ 27]USQLog: {USQL}:: SAVE PROGRESS:: 100.00

[2022.06.19-15.13.41:070][ 27]LogBlueprintUserMessages: [BP_GameMode_Server_C_0] Server: Save Data Finish: DB_DataStore

[2022.06.19-15.13.41:074][ 27]USQLog: {USQL}:: CREATE TABLE IF NOT EXISTS DB_PlayerInventories (ID TEXT PRIMARY KEY NOT NULL,PlayerHotbar TEXT,PlayerInventory TEXT,PlayerEquipment TEXT,PlayerClothing TEXT);

[2022.06.19-15.13.41:074][ 27]USQLog: {USQL}:: [DB_PlayerInventories] DB Table : OK

[2022.06.19-15.13.41:074][ 27]USQLog: {USQL}:: SELECT ID FROM DB_PlayerInventories

[2022.06.19-15.13.41:074][ 27]USQLog: {USQL}:: CREATE TABLE IF NOT EXISTS DB_PlayerInventories (ID TEXT PRIMARY KEY NOT NULL,PlayerHotbar TEXT,PlayerInventory TEXT,PlayerEquipment TEXT,PlayerClothing TEXT);

[2022.06.19-15.13.41:075][ 27]USQLog: {USQL}:: [DB_PlayerInventories] DB Table : OK

[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1337’,‘UArray>>{
“PlayerHotbar”: [ 1581621, 1791955, 1758110, 1202642, 1615558, 0 ]
}’,‘UArray>>{
“PlayerInventory”: [ 1594775, 1485427, 1058320, 1110324, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
}’,‘UArray>>{
“PlayerEquipment”: [ 0, 0 ]
}’,‘UArray>>{
“PlayerClothing”: [ 1626026, 0, 0, 0, 0, 0, 0, 0 ]
}’);

[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1793694’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1865382’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1124149’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1626026’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1110324’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1058320’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1485427’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1594775’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1326456’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1615558’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1820276’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1202642’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1758110’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1791955’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1773796’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1581621’,NULL,NULL,NULL,NULL);
[2022.06.19-15.13.41:086][ 27]USQLog: {USQL}:: INSERT OR REPLACE INTO DB_PlayerInventories (ID,PlayerHotbar,PlayerInventory,PlayerEquipment,PlayerClothing) VALUES (‘1136875’,NULL,NULL,NULL,NULL);

[2022.06.19-15.13.41:096][ 27]LogBlueprintUserMessages: [BP_GameMode_Server_C_0] Server: Prepare save: DB_PlayerInventories

[2022.06.19-15.13.41:100][ 27]USQLog: {USQL}:: SAVE PROGRESS:: 100.00

As you can see in the log, the items in the DataStore db also get written to the PlayerInventories db with null values.

Maybe my way of serializing the objects from server memory is incorrect, can I e-mail you some screenshots?

EDIT: Everything seems to work fine if I create different functions for each database operation and don’t run them sequentially in the same blueprint. Using a delay between each save also seems to be working though it’s not a great solution.

[BrUnO_XaVIeR]

Yeah screenshots would be good, not sure what the setup you have there is doing.

[iddkfa]

[SOLVED]
Stupid me. Had a Texture2DArray inside my Struct. Replaced it with an Index and now it works perfectly fine.

Sorry for any inconvinience. Still learning.

@iddkfa

Hi Bruno,

is it possible to save a StructureArray in the DB?

If yes, i don’t seem to get it to work. Tried both, declaring it as STRUCT and ARRAY in the DB. As a StructureSingle it works perfectly fine.

Thanks in advance for your reply.

edit: I found some similar questions, but sadly the conversations were pretty fragmented.

[SWilliams-QQ]

Hi,
Sorry if this is a silly question, I haven’t been able to find an answer on it.
Is it possible to use this and import a pre-existing SQLite db for use? I currently have a large database of various tables that I would need to use get information out of in-game.

[BrUnO_XaVIeR]

That would require C++ knowledge and would still be a lot of work for you.

It’s not impossible, but it was not designed to import external data that doesn’t comply with Unreal’s data types.

[BrUnO_XaVIeR]

We are still waiting for Epic to update this plugin to 5.1.
Seems like they forgot about it…

[AndyZhengLL]

did this plugin support iOS with ue 4.27?

[uced]

Hello,
Is it possible to export/import the content of a DB into a file in a custom location ?
I need my players to be able to exchange their saves.
Thanks
Cedric

[BrUnO_XaVIeR]

I think I can add a BP node for that.
I will see Monday