SQLlite DB

[nvh86]

Question: As I got instances working now i wanted to use the VoxelStreamSQLite to save the state of the world. as the blockfiles and region files don't support the instances.

I'm not very experienced in reading and setting up the DB schema in SQLite. So i downloaded (DB Browser for SQLite) Made a new DB with 3 tables en tried to make it correspond to the schema specs. Altough i'm not sure how to interpret te page.

So i ended up with a DB file with 3 tables. db.zip

But when trying to use this file in the streamfile setting. My godot console starts erroring out. ERROR: Could not open database: unable to open database file at: VoxelStreamSQLiteInternal::open (modules\voxel\streams\sqlite\voxel_stream_sqlite.cpp:148) ERROR: Condition "con == nullptr" is true. at: VoxelStreamSQLite::load_instance_blocks (modules\voxel\streams\sqlite\voxel_stream_sqlite.cpp:700)

Tried to just reference the stream to an empty folder. but that didn't do the trick either.

I assume to use the SQL region files i have to make a DB file and reference to it. And most likely i'm doing something wrong in setting up the schema.

Sorry to bother you with my incompetence. :) Some help would be appreciated.

[Zylann]

You don't need to create a DB yourself, it should be created automatically, assuming the parent directory is correct. So if you want to have save.sqlite inside the saves folder relative to the executable, you should write saves/save.sqlite. saves/ should exist but save.sqlite does not have to exist. If the database you created matches the expected schema, it should have worked though.

Make sure to avoid using res:// or user://, these are not supported for now.

Also keep in mind that currently the stream does not expect concurrent accesses to the file so you should not have DB browser locking the database while it is in use. Similarly, if the editor runs with the database in use, it might prevent the running game from also using it, so the scene should be closed first.

[nvh86]

Thnks, got it working.

[spiro-angelakis]

im sorry to ask but what data path could we use other than res:// or user:// ? trying to find that out now

[spiro-angelakis]

i think i understand that part nevermind ^, ive got a folder in the game executables root for the database but it just says the database cannot be found in the debugger

E 0:00:00.536 VoxelStreamSQLiteInternal::open: Could not open database: unable to open database file <C++ Source> modules\voxel\streams\sqlite\voxel_stream_sqlite.cpp:158 @ VoxelStreamSQLiteInternal::open() E 0:00:00.540 VoxelStreamSQLite::load_all_blocks: Condition "con == nullptr" is true. <C++ Source> modules\voxel\streams\sqlite\voxel_stream_sqlite.cpp:827 @ VoxelStreamSQLite::load_all_blocks()

[spiro-angelakis]

nevermind, i got it all working sorry, i had the stream set up in the editor instead of having it empty and then assigning it with code

no problems

[NuclearPhoenixx]

On another note regarding the SQLite Stream, I noticed that a world with a single VoxelViewer (in my example 512 viewing distance) takes significantly more time to load the entire viewing distance (like a factor of 3 at least) when using the stream instead of going no stream and generating everything at run time. This is even with save_generator_output disabled, so it's still generating everything at run time (didn't place any voxels). I'm guessing this is just the overhead that comes with checking the file for the individual blocks?

I'm using the default generator and blocky mesher by the way. I could open up a new issue if you want, but I'm guessing this is expected behavior so I just wanted to ask that in this general issue about the SQLite DB.

[Zylann]

in my example 512 viewing distance

Just noting that this is quite high, also considering the engine uses cubic chunks (Minecraft doesn't), where each one triggers its own request. Also if you didn't change vertical bounds of your terrain, that means you're loading 64 chunks worth of terrain vertically (up+down), while Minecraft uses only 24. Also I think in Java edition Minecraft defaults to 16 chunks view distance, while 512 would be 32.

takes significantly more time to load the entire viewing distance (like a factor of 3 at least) when using the stream instead of going no stream and generating everything at run time

I don't know which generator you're using, but if it's a basic test one, there is obviously a high chance that it will be faster than a stream because they are so simple. A full-blown world generator will have much work work to do than that and likely take a lot more time. Streams have to do disk I/O. This is slower not only because of doing filesystem calls, but also because it is often single-threaded. Whether you use an SSD or not might play a role too, as well as your OS. However this is usually a relatively fixed cost which doesn't get affected by the complexity of world generation.

I'm using the default generator

There isn't one? I'm not sure which one you refer to, but if it's one of the default Waves or Noise they are known to be relatively fast.

This is even with save_generator_output disabled, so it's still generating everything at run time (didn't place any voxels)

That can still be affected by the fact this process is single-threaded, and the engine doesn't know in advance if it will find blocks in the stream or not. That said, VoxelStreamSQLite has an option to cache all coordinates that contain a saved chunk, which might speed things up a little (i.e it will lookup this cache first before looking up the DB).

[NuclearPhoenixx]

Just noting that this is quite high, also considering the engine uses cubic chunks (Minecraft doesn't), where each one triggers its own request. Also if you didn't change vertical bounds of your terrain, that means you're loading 64 chunks worth of terrain vertically (up+down), while Minecraft uses only 24. Also I think in Java edition Minecraft defaults to 16 chunks view distance, while 512 would be 32.

Yeah I figured I'll try and see how far I can go. However, the view distance shouldn't make a difference for this comparison since it was the same between having an SQLite stream and having no stream. I was just looking at how fast the world was building upon start.

Streams have to do disk I/O. This is slower not only because of doing filesystem calls, but also because it is often single-threaded. Whether you use an SSD or not might play a role too, as well as your OS. However this is usually a relatively fixed cost which doesn't get affected by the complexity of world generation.

Ok, thanks for the explanation. I figured something like that was going on. And yes, I aggree a full-blown world generator will have a lot more to do than this simple example.

There isn't one? I'm not sure which one you refer to, but if it's one of the default Waves or Noise they are known to be relatively fast.

Oops, yeah I meant to say VoxelGeneratorNoise2D generator with default settings, sorry.

That said, VoxelStreamSQLite has an option to cache all coordinates that contain a saved chunk, which might speed things up a little (i.e it will lookup this cache first before looking up the DB).

That's a neat option, I wasn't aware of that! Is there any significant downside to this except for the added memory usage?

[Zylann]

That's a neat option, I wasn't aware of that! Is there any significant downside to this except for the added memory usage?

Memory usage is probably the only downside. If that ever becomes a problem there is room to optimize such memory.

[NuclearPhoenixx]

So how to actually use the set_key_cache_enabled function? I couldn't find anything about it in the documentation other than it exists and when I call stream.set_key_cache_enabled(true) in the VoxelTerrain Node's _ready() func, the scene just crashes silently without any clue about what happened. I was also able to replicate that in a min repro, so I'm missing something crucial here. Sorry for my confusion.

test.zip

[Zylann]

There is indeed no property associated to these methods. I didn't think of making one because it's quite niche to want this, and it's not clear yet to which extent queries will be considered slow, at least in my own project they are fast enough so using a key cache didn't make much of a difference.

It shouldn't crash, there is a bug, will fix in a moment.

[NuclearPhoenixx]

Ok, thanks. It's not important for me either, just wanted to give it a try and see if I could see any difference out of curiosity.

[Zylann]

Pushed the fix

[NuclearPhoenixx]

~Just to have it noted down somewhere here for when others might encounter the same issue. Calling stream.set_key_cache_enabled(true) actually results in the terrain not loading from the saved SQLite DB file at all (saving works though). So for now, the best thing is not to touch this specific function even if it's not supposed to do anything anyways.~

Was a bug, fixed with https://github.com/Zylann/godot_voxel/commit/67245c4b015b82117d4e365c2bebcdc44e7451ad, see below.

[Zylann]

stream.set_key_cache_enabled(true) should work though... if it doesn't, that's a bug and should be reported as an issue. However, before doing that, you should make sure to call it BEFORE the terrain starts to use it. Setting this to true AFTER the terrain already started processing will not work.

[NuclearPhoenixx]

I have this code in the _ready() function of the VoxelTerrain:

stream = VoxelStreamSQLite.new()
if stream:
    stream.set_key_cache_enabled(true) 
    stream.database_path = save_dir + "/world.sqlite"

This is just kind of a placeholder to support dynamic loading and saving of different worlds in the future. For now, the stream is not touched after this section of code. I manually call save_modified_blocks() before closing the game.

As for the code above, it doesn't seem to be loading any saved blocks at all -- the terrain just keeps generating new terrain at each start of the game. If I comment the line stream.set_key_cache_enabled(true), it works just as expected and loads changed blocks from the SQLite DB.

If you tell me this isn't a usage error on my side, I'll gladly open up an issue for this.

[Zylann]

That was a bug, you may try again with 67245c4b015b82117d4e365c2bebcdc44e7451ad

[NuclearPhoenixx]

It works, thanks!