SQLite storage

[RicardRC]

Store and access data directly from a sqlite storage database.

Contingency points:

• Use prepared statements as much as humanly possible, the speed-up deserves the extra work.
• Create indexes depending on queries defined on compile time/(systems?/pipelines?): the time sensitive queries will be even faster.
• Can we manage to make this storage re-loadable between sessions? Would allow to use as saved data directly. (How do we keep consistency between database/historical Entity IDs and runtime entity IDs?)
• Give access to the database as a databag with as much of the c++ sqlite api as we want/can add to allow any other shenanigan the developer might want to do.
• Allow for custom insert, update, delete, replace, etc statements to override our default ones: Useful when the data is in tables that contain more than what is exposed to the component, or is more complex than what direct insertion/update would allow.
• Always use parametrized queries, we don't want injection, accidental or malicious, to happen.

[AndreaCatania]

Use prepared statements as much as humanly possible, the speed-up deserves the extra work.

In Godex we have the concept of QueryStorage, that fetches the specified component https://github.com/GodotECS/godex/blob/main/iterators/query.h#L1066 from its storage.

For example the Query: Query<Health, Shield>, under the hood is composed by two QueryStorages, that will fetch the Health and the Shield storage, The query will return the Entities with both components. In other words, the Query and the DynamicQuery already performs the matching Heavy lift.

In order to support the SQLite storage, all we need to do is to implement the storage interface, like was done for the DenseVectorStorage: https://github.com/GodotECS/godex/blob/main/storage/dense_vector_storage.h#L12-L58

Once such storage is supported, the Components using such storage will be handled normally by the Query and DynamicQuery. The prepare statements is super easy, and has to be implemented local to the storage: The matching is already implemented by the various queries.

[AndreaCatania]

To be a bit more specific: Query<Health, Shield, CombatStats>. Let's suppose the CombatStats is a component stored in a SQLiteStorage and so it's inside a SQLite table:

struct CombatStats {
    COMPONENT(CombatStats, SQLiteStorage)

    real_t fire_resistance = 0.5;
    real_t frost_resistance = 0.25;
    real_t earth_resistance = 0.9;
};

As you can see, from the point of view of the query it doesn't make any difference.

The storage is completely transparent to the query and systems; The fact that both are decoupled, allow to have any custom logic on the storage level:

• You may have a SQLiteStorage that re-loads everything stored in the previous session.
• You may have a FreshSQLiteStorage that refresh the table, each time the storage is created for the first time inside the world, so it never uses the same data.
• Or you may expose some additional API, to perform such task, so that a system can eventually clear the table, etc..