SQLite based approach to storing game world state

Everything about development and the OpenMW source code.
User avatar
urm
Posts: 83
Joined: 02 Jun 2017, 16:05
Gitlab profile: https://gitlab.com/uramer

Re: SQLite based approach to storing game world state

Post by urm »

cc9cii wrote: 16 Nov 2020, 22:51 Ok, I skipped those parts :)

I still suspect you'll have some challenge to get this accepted until you can demonstrate a *killer* use-case. But I think you should still go ahead and implement it regardless. You'll have fun doing it and will learn more about OpenMW and game engines in general. I doubt I can be of much help other than some knowledge of ESM/ESP data, especially with later games (but they're already available elsewhere e.g. UESP).

EDIT: just to explain my interested in Lua scripting (by the way, I am not keen on Lua at all, having had some poor introduction to it on angband) - you may be aware that I'm trying to get later games (Oblivion, FO3, etc) to work. But I have little knowledge / experience in many subject areas, one of which is MODs and scripts. So I'm trying to understand more about them so that I make less fundamental errors that will lead to major re-writes.
Essentially, in singleplayer, the main advantage of using SQLite over just adding the query builder to existing storage system, is being able to run searches over unloaded cells at runtime. In singleplayer, most, or maybe all mods could get away with caching whatever they need on startup, and updating that cache as the session goes on. That's essentially a lot of code which will need to be rewritten by modders over and over. It might also result in inefficient RAM usage and save size increases. Combined with custom field support on OpenMW's standard models (planned by ptmikheev), the queries would take a lot of friction out of implementing such features (in cases where you don't need low latency, which is most of them I can think of). So while this might not be a 100% necessary addition, I feel like it would greatly improve modders' experience (especially the ones who do not happen to be programmers professionally), and would also allow us to reduce development time elsewhere (multiplayer, OpenMW-CS, etc).

As for suporting other games:
The current idea is to dehardcode as many game mechanics as possible. Which would allow to implement other games' gameplay as Lua "mods", which would either ship with OpenMW or come as separate downloads. Since it is also planned to re-implement MWScript in Lua, I imagine the same would be done for Oblivion script and Papyrus (or w/e the Skyrim's scripting system is).
So if you ask me, I'd focus on core engine functionality support - rendering techniques, physics, esp format parsing (which you have already been doing a lot) and reverse engineering the specifics of game mechanics (I think UESP is a better source in that regard for newer games compared to Morrowind, but needs to be verified). Many other features (e. g. UI) should probably be delayed until we have the Lua API.
Although specifically with UI, I have doubts that MyGUI will manage to gracefully render Skyrim's UI, or maybe even Fallout 3/NV pipboy. So that's something worth looking to as well, although worst case we don't really need the UI to be identical for the games to be playable.
elsid
Posts: 20
Joined: 01 Aug 2017, 08:20

Re: SQLite based approach to storing game world state

Post by elsid »

Why does OpenMW need an ORM to query database? It's possilbe to write SQL for every query required. I don't see where you considered this approach. Pros for this approach:
* No need to fight with ORM on how queries are generated
* Use all SQL features without cost of C++ code in ORM
* It's more clear what a query does

For prototyping I'd start with a schema definition first. OpenMW may not need a generic ORM.
User avatar
urm
Posts: 83
Joined: 02 Jun 2017, 16:05
Gitlab profile: https://gitlab.com/uramer

Re: SQLite based approach to storing game world state

Post by urm »

elsid wrote: 02 Jan 2021, 00:45 Why does OpenMW need an ORM to query database? It's possilbe to write SQL for every query required. I don't see where you considered this approach. Pros for this approach:
* No need to fight with ORM on how queries are generated
* Use all SQL features without cost of C++ code in ORM
* It's more clear what a query does

For prototyping I'd start with a schema definition first. OpenMW may not need a generic ORM.
The main purpose of this is to simplify data access for modders. Making modders learn SQL is not an option at all, in my opinion.

On the first stage we won't have a database at all, since that is mostly relevant for multiplayer, and also to global scripts. So this will just be an API for accessing the OpenMW containers, so something closer to LINQ, rather than an ORM.

At this point I don't intend to use an existing ORM, but rather write something simple for our use case.

Another reason to use this system over SQL, is that we have an abstraction layer, which will let us support different databases in the future. For example, SQLite might not be enough for large scale multiplayer servers, if we ever have need of those.
User avatar
urm
Posts: 83
Joined: 02 Jun 2017, 16:05
Gitlab profile: https://gitlab.com/uramer

Re: SQLite based approach to storing game world state

Post by urm »

I guess it's time for a progress update.

Current status:
1. Support models with one-to-one relations of any depth.
2. Constructing queries with SQL-like features: filters ("where"), sorting ("orderBy"), taking one of a group ("groupBy"), limit, offset, including relations ("with")
3. Query through iterators of models.
4. SQLite support: create tables, insert models into them, generate SQL to select with a given query.
5. A minimal set of models for ESM records to showcase functionality.

For now Lua API integration isn't included in the MR, you can find it here https://gitlab.com/uramer/openmw/-/comm ... _serialize.

Lua integration progress:
1. Bind query builder into Lua.
2. LiveCellRef model.
3. Convert ObjectId (aka RefNum) iterators into LiveCellRefModel iterators to be able to query through them.

You can find an example of already working Lua requests here https://gitlab.com/uramer/openmw/-/snippets/2072751.

Currently I have to construct field identifiers manually in Lua, however the plan is to pre-generate tables of fields for every model automatically from the ModelDefinitions. That will be the next thing I work on.

Remaining plans:
1. One-to-many relations.
2. Models for all ESM records and relevant runtime MWWorld records.
3. Proper tests with actual OpenMW records.

I've added SQLite to CMake and before scripts, which is mostly identical to the LZ4 setup, as AnyOldName3 suggested. I haven't tried making the Android pipeline work with it yet. However, we will probably not even need SQLite for the first phase of Lua API.

I've also kept as much code as possible in header files as I find it more convenient, I will move things to source files before it's ready for merging, of course.
Last edited by urm on 06 Feb 2021, 21:41, edited 2 times in total.
User avatar
psi29a
Posts: 5357
Joined: 29 Sep 2011, 10:13
Location: Belgium
Gitlab profile: https://gitlab.com/psi29a/
Contact:

Re: SQLite based approach to storing game world state

Post by psi29a »

I think it would be beneficial to make use example-suite assets when writing tests so that we don't need to rely on Morrowind itself, if that's necessary.
User avatar
urm
Posts: 83
Joined: 02 Jun 2017, 16:05
Gitlab profile: https://gitlab.com/uramer

Re: SQLite based approach to storing game world state

Post by urm »

psi29a wrote: 06 Feb 2021, 17:05 I think it would be beneficial to make use example-suite assets when writing tests so that we don't need to rely on Morrowind itself, if that's necessary.
When I said "actual OpenMW records", I meant the structs in the esm component, not necessarily actual Morrowind data.
I was planning to just hard code some values manually, but using OpenMW-Suite data is a much better idea.

One problem that I see with it is that some tests might have to be adjusted every time there are changes to the OpenMW Suite.
User avatar
psi29a
Posts: 5357
Joined: 29 Sep 2011, 10:13
Location: Belgium
Gitlab profile: https://gitlab.com/psi29a/
Contact:

Re: SQLite based approach to storing game world state

Post by psi29a »

For unit tests, hard coded values are good. For any end-to-end testing, we can could throw "real" data at it.
User avatar
urm
Posts: 83
Joined: 02 Jun 2017, 16:05
Gitlab profile: https://gitlab.com/uramer

Re: SQLite based approach to storing game world state

Post by urm »

I've updated the script example to feature the current syntax (stll here https://gitlab.com/uramer/openmw/-/snippets/2072751).
Now the relations and fields are nicely read from pre-generated tables, rather than constructed manually from Lua.
User avatar
urm
Posts: 83
Joined: 02 Jun 2017, 16:05
Gitlab profile: https://gitlab.com/uramer

Re: SQLite based approach to storing game world state

Post by urm »

Status update:

ptmikheev and I have agreed on a query builder implementation, and it is now included in his Lua MR. It also includes a temporary proof of concept implementation of queries written in Lua, so you can see them in action in his example mod here https://gitlab.com/ptmikheev/openmw/-/t ... y_monsters.

Currently I'm rewriting my MR to use that query builder. Most of the difficult code is the same, so I should be done within a couple of weekends. Also, as a nice side effect, the query syntax has become a lot more readable on the C++ side. For now that only affects test code, but maybe it will warm others to the idea of using queries outside of the Lua API.

Feature-wise, no progress has been made on my side so far, so the goals stay the same there.
darkbasic
Posts: 153
Joined: 18 Apr 2016, 15:45
Contact:

Re: SQLite based approach to storing game world state

Post by darkbasic »

urm wrote: 02 Jan 2021, 10:49 The main purpose of this is to simplify data access for modders. Making modders learn SQL is not an option at all, in my opinion.

Partially true, it depends on the complexity of the query. An ORM makes simple queries easy, but whenever you need anything remotely more complex it makes your life a hell. Doing complex queries with an ORM can be either extremely difficult or plain impossible.
urm wrote: 02 Jan 2021, 10:49Another reason to use this system over SQL, is that we have an abstraction layer, which will let us support different databases in the future. For example, SQLite might not be enough for large scale multiplayer servers, if we ever have need of those.
Don't count on it. We've already tried this path and failed many times. When you fail to scale then switching to a different database alone won't save your day: you will have to rewrite your queries to take advantage of its features and an ORM will only make your life more difficult.

Don't get me wrong, I like working with ORMs because they allow for fast prototyping and easy to read code while also lowering the barrier for junior developers, but overall they won't make your life easier. On the contrary they make it harder in the long term because you will waste much more time optimizing your queries/writing complex ones. I'm talking about complex web applications with lots of db needs, probably that won't be the case for openmw.
Post Reply