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

SQLite based approach to storing game world state

Post by urm »

With work on Lua scripts (viewtopic.php?f=6&t=7149) starting soon, it's time to revisit the way OpenMW handles game world state.

After discussion with ptmikheev and David C I came up with the following.

Issues with current OpenMW approach
  1. The only way to access data about cells which haven't been loaded is to read esp files
  2. Every cell touched during a session will stay loaded - potential for running out of RAM
  3. Can't search through the world state quickly
  4. No straightforward way to serialize data into network packets for multiplayer or external communication (relevant for Lua scripts)
Issues with current TES3MP approach
  1. A lot of code can't be reused between the server and the client
  2. A significant amount of boilerplate code related to Lua bindings and RakNet packet conversions
  3. Conversion from and to JSON is not efficient
  4. Client and server data formats are completely different
Goals for the new approach
  1. Universal API for reading the current game world state (esp and save data combined)
  2. Minimal extra dependencies
  3. Same or better performance
  4. Identical storage system for single- and multiplayer
  5. Support large multiplayer servers (at least 50 concurrent players)
Proposed approach

Overview
  1. Cache .esp data into an SQLite database
  2. Convert save data into an in-memory SQLite database
  3. Query through them with an ORM query builder
  4. Provide Lua bindings for the query builder
ESP/omwaddon data

Everything that's read from .esp files on startup, and is not changed during play
  1. Stored as an on-disk SQLite database
  2. Generated on the initial pass through the .esp files (game launch). Can be cached for future launches as long as load order doesn't change
  3. At runtime only allows reads
  4. SQLite allows simultaneous reads, so easily scales to multiple threads if necessary
  5. Can scale even further by creating multiple copies on different physical drives, so won't be a bottleneck in multiplayer regardless of player count
  6. If proves to be faster than parsing the esp format, can be used for loading cells as well
Save data

Changes compared to .esp game world state
  1. Stored as an in-memory SQLite database
  2. All changes to the game world are periodically written here
  3. A save is just a backup of this database
  4. If the filesize is acceptable (potentially after zip-ing), can replace the current OpenMW save file format
  5. When used in multiplayer, easy to make regular backups
It might also be worth experimenting with storing this database on-disk as well. That will allow to enable WAL mode (allows to simultaneously read and write) in SQLite, and reduce RAM usage. On systems with a lot of RAM we could increase SQLite page cache significantly and get performance that's close to in-memory.

Quickly changing data

Information that's unreasonable to save into in-memory database whenever it changes, such as actors' locations and some MWScript variables
  1. Stored in fully loaded models (current OpenMW world state storage)
  2. If the in-memory DB turns out to be fast enough, this will only have to include data for currently active cells
  3. Any changes to the game world are done on completely loaded models (the way OpenMW functions now), and are saved to the in-memory DB automatically (at regular intervals, or at cell unloads, ...)
Querying data

On any query, the data is read in order, first from fully loaded models loaded, then from the in-memory database, then from the on-disk esp database, and merged (loaded models have priority over in-memory db, in-memory has priority over on-disk).
We should probably have a way to specify only querying data about active cells, which means we can skip steps 2 and 3 for performance.
This way we can rely on SQLite to query most of the data efficiently, and only need to implement filtering on loaded models through a complete pass over them.

Dependencies
  1. sqlite: can likely just use the included API, but optionally there are many libraries (such as SQLite ORM)
  2. a query builder which works both with C++ data structures of our choice, and with SQL generation
Some implementation details

It appears a C++ query builder which perfectly fits our use case doesn't exist. There are many LINQ-like libraries, but they use lambdas, which won't allow string generation in C++. There are also multiple SQL generating libraries, but we would have to wrap them in our own query builder, or maintain a fork to add support for C++ data structures. So we will probably have to write our own query builder with string/enum based syntax. On the bright side, we might not need to implement all the intricacies of a general use library.

Most of the small query builder libraries I've found are under 1000 lines of code, so it isn't actually that much code to write and maintain. Although in our case I expect it will be roughly double that - we want to support both SQL and C++ data structures.

For every model (actor, cell, ingredient, cell object, ...) we will need a way to map its fields into SQLite types, and define relations between some of them (cell objects and cells, for instance).

This serialization into a collection primitive types might also be used for network packets used by TES3MP, which will make the synchronization code simpler and cleaner.

Justification

Lua Mod API

It should be straightforward to provide a Lua binding for the query builder. This will automatically give us many desireable features.

For a basic example, the scripting API will certainly need a way to get all the currently active players. Instead of implementing a separate function just for that, we should be able to allow something like this:

Code: Select all

players=world.Actors.Where("type", Actor.TYPE.PLAYER).Where("active", true).All()
Obviously, some of the most commonly used queries can still be provided as a simple function of the Lua API, but implementing them will be as easy as writing such a query in Lua, rather than adding a separate C++ function to bind.

More involved examples, which use most of the features of a query builder:
  1. A custom inventory UI, which groups/sorts/flters/searches for items in specific ways
  2. A multiplayer world building script (a commonly requested feature), which provides a catalogue of all items from the current load order, with search/filtering/etc.
  3. An automated armor and weapon balancer (already exists for MWSE) which searches through them on every load order change, and overrides them only in the current save
  4. Mods which operate on unloaded cells, such as enemies following players through doors or NPC scheduling
We could even allow scripts to create their own tables, and thus provide a convenient storage for complex script data.

Dehardcoding

One of the post 1.0 goals is to transfer as many Morrowind-specific game mechanics into Lua scripts as possible. Having a powerful data query API will definitely make it easier. Some examples:
  1. alchemy/enchanting will want to find relevant items in player's inventory
  2. finding all autocalc spells for a given NPC
  3. loading dialogue topics
OpenMW-CS

Queries through .esp data immediately give us most of the functionality of tes3cmd (a commonly used tool for automated .esp generation). It would be easy to add this to OpenMW-CS after the changes above are introduced.

We could also re-implement the existing search/filters in the editor to reduce the amount of code to maintain.

Custom records

One of the major features of TES3MP are custom records - a way to create new records (NPCs, weapons, cells...) at runtime, on the server side. Storing save games and world files in the same way will allow to override or add extra records only for the current save game, rather than load order, which makes it much easier to implement custom records for OpenMW (to be used both for multiplayer and singleplayer mods).

Some runtime-created records (particularly user-brewed potions) are too numerous to store them indefinitely. Currently tes3mp stores many-to-many relations between them and containers (players and cells) in a map on each container and record. With the database storage and advanced querying it will be trivial to find all unused records.

OpenMW as a general engine, and supporting other games

This approach would allows us to rapidly implement new game mechanics, and to provide flexible Lua access to any new object types necessary.
User avatar
akortunov
Posts: 899
Joined: 13 Mar 2017, 13:49
Location: Samara, Russian Federation

Re: SQLite based approach to storing game world state

Post by akortunov »

Personally I see no reasons to use SQL in OpenMW at all.
urm wrote: 09 Nov 2020, 18:38
  1. Stored as an on-disk SQLite database
  2. Generated on the initial pass through the .esp files (game launch). Can be cached for future launches as long as load order doesn't change
  3. At runtime only allows reads
  4. SQLite allows simultaneous reads, so easily scales to multiple threads if necessary
  5. Can scale even further by creating multiple copies on different physical drives, so won't be a bottleneck in multiplayer regardless of player count
  6. If proves to be faster than parsing the esp format, can be used for loading cells as well
A quite strange approach since ESM/ESP data does not have large size and it is fully loaded to RAM. ESM parsing speed does not really matter since we do it only once during startup. The same thing for save data - you read it only once from start to end during savegame loading.
Remember that in OpenMW data reading usually is not a bottleneck, a data processing is.
A separate issue which you can encounter - a data storing and search order actually matter in Morrowind, and even with Bethesda's assets we got situations when some content did not behave properly (e.g. scripts found wrong instances of objects). It is a huge can of worms, which I'd not like to open.
urm wrote: 09 Nov 2020, 18:38 Most of the small query builder libraries I've found are under 1000 lines of code, so it isn't actually that much code to write and maintain. Although in our case I expect it will be roughly double that - we want to support both SQL and C++ data structures.
Usually it means that ORM has a quite limited functionality, and you will more likely spend a time to extend it when it does not do what you want. Even complex projects (e.g. Entity Framework) have their limitations.
urm wrote: 09 Nov 2020, 18:38 OpenMW as a general engine, and supporting other games
This approach would allows us to rapidly implement new game mechanics, and to provide flexible Lua access to any new object types necessary.
I have a suspiction that SQL may actually kill that approach. Actually, I do not remember many RPGs which use SQL databases.
An only exception is Drakensang, but I suspect that devs just did not have enough time and money to develop their own data and save format.
If SQL is not that much widely used in gamedev, there are reasons for that.

As for query builders, it is a low level of backend. It is not actually a thing which most of content creators can deal with efficiently, so Lua bindings make no sense here. They expect a different approach - you hide tehnical details, so they can implement their features instead of studying how SQL queries work.
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 »

akortunov wrote: 09 Nov 2020, 19:56 Personally I see no reasons to use SQL in OpenMW at all.

A quite strange approach since ESM/ESP data does not have large size and it is fully loaded to RAM. ESM parsing speed does not really matter since we do it only once during startup. The same thing for save data - you read it only once from start to end during savegame loading.
Are you certain this is the case? I believe we load cell data from .esp files only when we need them immediately, which happens here : https://github.com/OpenMW/openmw/blob/0 ... s.cpp#L178
A separate issue which you can encounter - a data storing and search order actually matter in Morrowind, and even with Bethesda's assets we got situations when some content did not behave properly (e.g. scripts found wrong instances of objects). It is a huge can of worms, which I'd not like to open.
I think my approach covers that. I don't see much of a difference between reading .esp files sequentially and checking for collisions in std::vectors vs overwriting rows in an SQLite database. It's a very natural thing to do with an SQL database in general.
Usually it means that ORM has a quite limited functionality, and you will more likely spend a time to extend it when it does not do what you want. Even complex projects (e.g. Entity Framework) have their limitations.
I'm pretty sure even the most basic ORM gives way more options than searching in an std::vector or an std::map.
In fact, the worst case is you just query all the records of a given type in active cells, and essentially get the same result you would with the way OpenMW does things currently. So if you want something extremely specific, you can still do it, at the cost of just one extra vector pass.
I have a suspiction that SQL may actually kill that approach. Actually, I do not remember many RPGs which use SQL databases.
An only exception is Drakensang, but I suspect that devs just did not have enough time and money to develop their own data and save format.
If SQL is not that much widely used in gamedev, there are reasons for that.
Most games don't provide advanced support for mods with arbitrarily complex features. If you come up with actual specific reason why SQL databases are not suitable for games, I'm very interested to hear it.
As for query builders, it is a low level of backend. It is not actually a thing which most of content creators can deal with efficiently, so Lua bindings make no sense here.
How are query builders a low level backend? They are a very intuitive way to fetch data. There is a reason C# provides LINQ just to grab things from standard data structures. It is definitely nicer than iterating over a vector, or what (as far as I know) the MWSE API provides.
In any case, can you suggest a way to access data which you find more user friendly?
User avatar
akortunov
Posts: 899
Joined: 13 Mar 2017, 13:49
Location: Samara, Russian Federation

Re: SQLite based approach to storing game world state

Post by akortunov »

urm wrote: 09 Nov 2020, 20:40 I believe we load cell data from .esp files only when we need them immediately, which happens here
As I said, there are two steps:
1. We read ESM data during startup to DTOs.
2. When we need to work with given cell, we take loaded DTOs and process them - create cellrefs and pointers, create animation objects, create creature and NPC stats, resolve containers and levelled creatures, etc, and created state will go to save files. The code which you linked does this job.
The second step takes some time to do, and created objects use a lot of resources, that's why we load only needed cells, and object paging and groundcover work directly with ESM data - they do not need this overhead, and that's why ECLD caused a huge RAM usage and stuttering during cell change.
If you change a data source from ESM to SQL, you affect only step 1, which is not a bottleneck, and I am not sure if SQL will be much faster since we use sequential read for ESM data. It is valid for case if your SQL objects use ESM objects, so you do not need to create a separate object model for SQL and map SQL objects to ESM ones, so the rest of engine can work with them.
So the main issue here is how to process data in efficient way rather than how to store it, and SQL is not a silver bullet here.
urm wrote: 09 Nov 2020, 20:40 I don't see much of a difference between reading .esp files sequentially and checking for collisions in std::vectors vs overwriting rows.
It is sad. An example of when it matters is objects searching in the scripting system. There are many cases even in Bethesda's files when you need to search for a first object with given ID in all game cells. Every cell stores references in order in which they are stored in ESM file, so search respects this order.
If your SQL will store and provide records in a different order (and it will more likely will, since it is database management system's job to set order of records to store them in the efficient way), search queries will return wrong instances. There were releated bugreports in OpenMW in the past, but it works as in Morrowind now to avoid such kind of issues. This system is extremely fragile.
urm wrote: 09 Nov 2020, 20:40 If you come up with actual specific reason why SQL databases are not suitable for games, I'm very interested to hear it.
A first link from Google:
https://gamedev.stackexchange.com/quest ... sktop-game
urm wrote: 09 Nov 2020, 20:40 Most games don't provide advanced support for mods with arbitrarily complex features.
There is a lot of available general-purpose game engines with a lot customization, but they usually use their own data formats instead of SQL databases for some reason.
urm wrote: 09 Nov 2020, 20:40 We could even allow scripts to create their own tables, and thus provide a convenient storage for complex script data.
The main use case of SQL-based databases is when you have fixed data scheme with relations between records, so you can organize them as linked tables. Attempts to allow to store arbitrary data (e.g. custom user tables or entities with custom fields) usually lead to mess, and often it is better to use non-SQL databases instead (e.g. MongoDB, where every record is just a JSON document).
urm wrote: 09 Nov 2020, 20:40 How are query builders a low level backend? They are a very intuitive way to fetch data. There is a reason C# provides LINQ just to grab things from standard data structures.
When you use SQL queries to fetch data from database, you should know SQL well to do it in efficient way. If you use a simple wrapper for SQL to do the same, you should know SQL well and more-or-less know how this wrapper works, otherwise you can do a lot of stupid things.
In large projects there is a code review, which is supposed to detect such things, but we talk about mods, and many of them have just one script writer.
Even in your example, modmaker can do something like this:

Code: Select all

players=world.Actors.All().Where("type", Actor.TYPE.PLAYER).Where("active", true)
I suppose that in this case user will get all actor records from database, and filters will be applied on client side.
LINQ is not an exception here - you can, for example, write an implicit IQueryable->IEnumerable cast accidentally and read the whole table at once.

There are many ways to skin a cat, but if given well-known way is not widely used, more likely it is not very efficient way to skin the cat.
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 »

akortunov wrote: 10 Nov 2020, 05:29
As I said, there are two steps:
1. We read ESM data during startup to DTOs.
2. When we need to work with given cell, we take loaded DTOs and process them - create cellrefs and pointers, create animation objects, create creature and NPC stats, resolve containers and levelled creatures, etc, and created state will go to save files. The code which you linked does this job.
The second step takes some time to do, and created objects use a lot of resources, that's why we load only needed cells, and object paging and groundcover work directly with ESM data - they do not need this overhead, and that's why ECLD caused a huge RAM usage and stuttering during cell change.
If you change a data source from ESM to SQL, you affect only step 1, which is not a bottleneck, and I am not sure if SQL will be much faster since we use sequential read for ESM data. It is valid for case if your SQL objects use ESM objects, so you do not need to create a separate object model for SQL and map SQL objects to ESM ones, so the rest of engine can work with them.
So the main issue here is how to process data in efficient way rather than how to store it, and SQL is not a silver bullet here.
All of this is irrelevant, since I never claimed that the point of this DB is to speed up cell loads. The main point is to allow scripts to quickly access all the data about unloaded cells.
It is sad. An example of when it matters is objects searching in the scripting system. There are many cases even in Bethesda's files when you need to search for a first object with given ID in all game cells. Every cell stores references in order in which they are stored in ESM file, so search respects this order.
If your SQL will store and provide records in a different order (and it will more likely will, since it is database management system's job to set order of records to store them in the efficient way), search queries will return wrong instances. There were releated bugreports in OpenMW in the past, but it works as in Morrowind now to avoid such kind of issues. This system is extremely fragile.
I get an impression that you have you never used an SQL database. Anyway, this particular issue is easily solved by having a priority column on the cell object table, and using it for such queries. Overall, anything that we currently do by search through a linear storage (an std::vector or an .esp file) is easy to replicate with an SQL query.
Yes, this is obvious. However in my outlined approach any active cell data is stored in a way identical to current OpenMW anyway, so no performance loss there. It only needs to be saved to the SQLite database when cells are unloaded.
The main use case of SQL-based databases is when you have fixed data scheme with relations between records, so you can organize them as linked tables. Attempts to allow to store arbitrary data (e.g. custom user tables or entities with custom fields) usually lead to mess, and often it is better to use non-SQL databases instead (e.g. MongoDB, where every record is just a JSON document).
Again, I feel like you've never used SQL databases. There are plenty of reasons to put your data into a table, even if you do not have any relations to other tables. Such reasons include: fast search, grouping, sorting. Also particularly in SQLite's case, on many filesystems it's faster to read from a single SQLite file, compared to a bunch of loose files.
When you use SQL queries to fetch data from database, you should know SQL well to do it in efficient way. If you use a simple wrapper for SQL to do the same, you should know SQL well and more-or-less know how this wrapper works, otherwise you can do a lot of stupid things.
In large projects there is a code review, which is supposed to detect such things, but we talk about mods, and many of them have just one script writer.
The worst case scenario for SQL queries is still a single pass over the table, which is the best case scenario for something like std::vector.
Obiviously, some people will manage to write extremely stupid queries, which will be slow. But that is also easily doable with a primitive data structure over which you have to iterate manually. In fact, it is even easier to write an inefficient "query" through an array, since you have to manually write things like 'ordering and grouping.
Even in your example, modmaker can do something like this:

Code: Select all

players=world.Actors.All().Where("type", Actor.TYPE.PLAYER).Where("active", true)
I suppose that in this case user will get all actor records from database, and filters will be applied on client side.
LINQ is not an exception here - you can, for example, write an implicit IQueryable->IEnumerable cast and read the whole table at once.
This is in no way specific to SQL or a query builder. It will be an "issue" as long as we provide access to all esp data. No matter how we implement it, reading all of the records of a particular type will be expensive.
User avatar
akortunov
Posts: 899
Joined: 13 Mar 2017, 13:49
Location: Samara, Russian Federation

Re: SQLite based approach to storing game world state

Post by akortunov »

urm wrote: 10 Nov 2020, 09:21 All of this is irrelevant, since I never claimed that the point of this DB is to speed up cell loads.
It is your quote:
urm wrote: 09 Nov 2020, 18:38 If proves to be faster than parsing the esp format, can be used for loading cells as well
Also you linked a code which loads cells as en example if code which would benefit from SQL databases.
It is all about performance. From what I can tell, you expect to speedup two things:
1. Iterating over unloaded cells (due to SQL queries).
2. Cells loading (due to faster SQL result -> DTO transitions than ESM -> DTO transitions).

As about first thing, ESM data is stored and iterated in the Morrowind way (to avoid compatibility issues) rather than most efficent way.
Propably there is a possibility to optimize it if needed, but it never was a priority since it is fast enough if compare to other parts of the engine.
For example, groundcover mods have a lot more objects per cell than common mods (sometimes about 10x more), but even in this case iteration over dozens of cells is faster than their processing.
So you make an engine a lot more complex and error-prone just to speedup a part which usually is not a bottleneck.
As about second thing, again, reading from file usually is not a bottleneck, the bottleneck is when you need create a lot of business entites (which are in apps/openmw), and again, you probably can optimize it without introducing SQL databases.
urm wrote: 09 Nov 2020, 18:38 However in my outlined approach any active cell data is stored in a way identical to current OpenMW anyway, so no performance loss there.
As I understood, you discarded case 2, so SQL is solely to speedup read access for unloaded cells (because to modify an unloaded cell you need to load it first), right?
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 »

akortunov wrote: 10 Nov 2020, 10:29 Also you linked a code which loads cells as en example if code which would benefit from SQL databases.
It is all about performance. From what I can tell, you expect to speedup two things:
1. Iterating over unloaded cells (due to SQL queries).
2. Cells loading (due to faster SQL result -> DTO transitions than ESM -> DTO transitions).
The main point there isn't to improve performance, but to reuse code as much as possible. I guess I should have worded that differently.
As about first thing, ESM data is stored and iterated in the Morrowind way (to avoid compatibility issues) rather than most efficent way.
Propably there is a possibility to optimize it if needed, but it never was a priority since it is fast enough if compare to other parts of the engine.
For example, groundcover mods have a lot more objects per cell than common mods (sometimes about 10x more), but even in this case iteration over dozens of cells is faster than their processing.
So you make an engine a lot more complex and error-prone just to speedup a part which usually is not a bottleneck.
As about second thing, again, reading from file usually is not a bottleneck, the bottleneck is when you need create a lot of business entites (which are in apps/openmw), and again, you probably can optimize it without introducing SQL databases.
Yes, if we are talking purely about vanilla, then obviously we don't need any of these features. As soon as Lua mods and multiplayer are on the table, quick access to esp data becomes quite desirable, if not mandatory.
As I understood, you discarded case 2, so SQL is solely to speedup read acess for unloaded cells (because to modify an unloaded cell you need to load it first), right?
I'm not sure what you mean by case 2, but yes, any data changes would only happen on fully loaded models, similarly to how most ORMs work (C#'s Entity Framework, PHP's Eloquent etc)
User avatar
akortunov
Posts: 899
Joined: 13 Mar 2017, 13:49
Location: Samara, Russian Federation

Re: SQLite based approach to storing game world state

Post by akortunov »

urm wrote: 10 Nov 2020, 11:36 I guess I should have worded that differently.
Probably. From my understanding, in your initial post you stated that SQL database on disk should be faster that RAM buffers, while we (and not only we) generally avoid to cache data to disk if we can do not do it.
Also it would be nice to actually see use cases where the game (including singleplayer) actually would benefit a lot from this feature to excuse an additional dependence (SQLite) and an additional entity (SQL database).
As I understood, these cases are complex SELECT queries with a lot of data, which the engine should execute very often (e.g. every frame or by timer). I have a suspicion that most of such queries are actually just a bad script design, and they can be avoided.
urm wrote: 10 Nov 2020, 09:21 There are plenty of reasons to put your data into a table, even if you do not have any relations to other tables. Such reasons include: fast search, grouping, sorting.
It does not contradict the code you quoted. To allow user to have custom fields in SQL-based database, you need to make things a lot more complex, just for example:
1. Use a fixed set of custom text columns, e.g. CustomField1 and CustomField2. A quite limited approach.
2. Introduce a "user data" text column and put the whole custom data here, serialized, for example, to JSON or XML. The drawback is that it is hard to search over fields from this serialized data.
3. Introduce a generic "Property" table, every property has its name and a set of columns, which store property value depending on property type (a column for numeric value, a column for string value, etc.) and additional columns, to store links between properties and entities to which they belong. It is a quite slow approach with a messy detatbase and SQL queries.
So in cases when you allow to use custom fields in many types of records, non-SQL databases (e.g. MongoDB) may be preferable, because they are optimized for them. For example, every record in MongoDB is just a JSON object.
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 »

akortunov wrote: 10 Nov 2020, 12:04 Probably. From my understanding, in your initial post you stated that SQL database on disk should be faster that RAM buffers,
I never claimed that, if anything, I claimed the opposite. However there are many things we can't just dump into RAM, such as complete data about unloaded cells. One thing which I suggest we should probably dump to disk are cells which were visited in the current session, but are not active anymore. last time I tested this (0.45, AFAIK nothing changed), if someone would visit a significant amount of cells without restarting or reloading the game (about 1/3 of all Morrowind.esm interiors), the game would run out of RAM and die. Obviously, this is hardware dependent, so someone running on a phone / raspberry Pi could run into this limitation a lot faster. And it becomes an even bigger issue in multiplayer.
Also it would be nice to actually see use cases where the game (including singleplayer) actually would benefit a lot from this feature to excuse an additional dependence (SQLite) and an additional entity (SQL database).
As I understood, these cases a complex SELECT queries with a lot of data, which the engine should execute very often (e.g. every frame or by timer). I have a suspicion that most of such queries are actually just a bad script design, and they can be avoided.
The main use case are Lua mods. No, we would definitely not want to execute an SQL query every frame. However, if with well designed mods which only run their queries when truly necessary, we can't guarantee how much of such mods work simultaneously. SQLite supports concurrent reading, and that reading is easy to split into as many threads as we want, which is not really true for .esp and save game files. It might be possible to implement such access, but it will definitely not be as efficient as SQLite, and will probably be more work at that point.

The reason why I'm suggesting a query builder, is that I want to provide a uniform API to read data for modders. You don't want to use different code to read the same things from different places. It also gives us freedom in the future to change the storage (e. g. what is stored on disk, and what is stored in RAM) without breaking mod support.
In terms of performance, and limiting particularly terrible Lua mod designs, I've already had a discussion with ptmikheev, and it largely depends on the specifics of Lua mod implementation. If you've read that post, one of the simplest approaches is to limit local scripts to active cell data (is already in RAM, no SQL queries involved), while giving full access to the query system to global scripts as he calls them. There are many different ways to approach this, but that discussion is more relevant in the context of Lua script API, than it is here.
User avatar
akortunov
Posts: 899
Joined: 13 Mar 2017, 13:49
Location: Samara, Russian Federation

Re: SQLite based approach to storing game world state

Post by akortunov »

urm wrote: 10 Nov 2020, 12:29 there are many things we can't just dump into RAM, such as complete data about unloaded cells.
It depends on what you claim to be a "complete data". If you mean low-level data (including ESM records for visited cells), it is already stored in RAM (because it is valid only for current game session, and there is just no other places to store it).
If you mean all high-level objects from apps/openmw, they are present only for loaded cells by design (because they consume too many resources to have them for everything), and they nothing common to low-level storages, such as SQL database.
urm wrote: 10 Nov 2020, 12:29 However, if with well designed mods which only run their queries when truly necessary, we can't guarantee how much of such mods work simultaneously. SQLite supports concurrent reading, and that reading is easy to split into as many threads as we want, which is not really true for .esp and save game files.
It is nice if you can guarantee that your ESM data is really immutable (or at least server and only server can modify it in the single thread). For example, there are many mods which adjust base records during game start (you referred some of them) and during some game events (e.g. when you create a new item with arbitrary properties on the fly). I suppose that all such scripts are supposed to be server-side-only ones, right?
Post Reply