4 ms·

I have gone down the exact same line of thinking. I think there is value in creating a simple relational data structure to have simple select and insert functi

I have gone down the exact same line of thinking. I think there is value in creating a simple relational data structure to have simple select and insert functions but not the overhead of sqlite. Many times when I think about it, the state of most programs could use a handful of the same structure and be done.

TeMPOraL 7y ago
Yeah, I think so too. My goal after hopefully making a completely playable game running on SQLite is to eventually rip it out and replace with my own ECS, based on gained experience. I start with explicit SQL queries, and am slowly building an interface on top of it. Or rather, I'm moving towards the interface I abandoned prior to switching to SQL. Before SQLite, I was working on my (yet another) own implementation, and eventually realized that I don't like the "Systems" part of the ECS, given I planned a lot of logic that required inspecting multiple components of any given entity. Thinking about it, I figured out that a better abstraction for a "System" would be a piece of code executed on a set of entities, defined by some selection logic. That is, a query. I quickly ended up with this interface: (select-entities :components (has-position has-health (not player)) :where ((entity-id (has-health (hit-points hp))) (< hit-points 50)) :order-by (((lhs (has-health (lhs-hp hp)) &optional (targetable (lhs-priority priority))) (rhs (has-health (rhs-hp hp)) &optional (targetable (rhs-priority priority)))) (if (and lhs-priority rhs-priority (= lhs-hp rhs-hp)) (< lhs-priority rhs-priority) (< lhs-hp rhs-hp))) :into my-array) (The example selects non-player entities with position and health components which have less than 50 HP, and orders them by HP and, if both compared entities have a "targetable" component, also by targeting priority.) Which, as you can see, looks essentially like an SQL query. :components is select + join, :where is filtering condition, :order-by saves me from doing an explicit sort on the results, and the :into my-array part allows this query to not allocate new storage for results each frame. Now each time such call showed up in my code, some extra book keeping storage would be allocated, and underlying logic ensured that most of the query doesn't have to be recomputed every frame. For instance, the :where part was handled by tracking addition and deletion of components to/from an entity. Implementing similar optimizations for :order-by part, I quickly realized that most of my "book keeping" tricks are essentially equivalent to compiled queries and indices on tables. At which point I ditched it all and switched to SQLite. (I meant to write a blog post about this at some point. I guess I just wrote half of it here.)
- rstrduck 7y ago
  I’m intrigued by your approach! If you do end up writing that blog post and/or releasing the source, I’d be interested in learning more.
  - TeMPOraL 7y ago
    Drop me an e-mail (address in my profile), I'll let you know when the post is up.
- gamdevcindy 7y ago
  I've never written a game where I needed to query for entities (except spatially). Have I just not created the right type of game? If I needed to find certain types of entities they'd get added/add themselves to some list on creation. Querying things at runtime seems anathema to game dev if you want perf which most game devs obsess over
  - TeMPOraL 7y ago
    > If I needed to find certain types of entities they'd get added/add themselves to some list on creation. From the point of view of the code operating on these entities, this is a query. That it happens to return results in constant time because it's being continuously executed in the background - that's just an implementation detail. The example I've pasted doesn't execute a full query every frame either. The first time it's called, it ensures that there exists an array within ECS system that stores entities which match the :components part of my query. That array is then continuously updated when appropriate components are added or removed from entities. Multiple different queries using the same :components part (which gets canonicalized, so you can write them in any equivalent order) reuse that array. The above is equivalent to a typical System in ECS, except I don't create a class or a global instance for it - I just shove a call to select-entities in the place I need it. This makes the architecture a bit more flexible conceptually, resolving some discomfort I had with Systems. The :where and :order-by parts get executed each time, on the aforementioned array. I could've made them execute when components are added or removed, at the cost of increased memory usage (no more sharing of the filtered entities array); I chose against it in this project. But this isn't really a problem; :where and :order-by subsume the code you'd typically put in your System - processing entities matching a particular condition (e.g. hp < 50), and processing them in particular order, respectively. There should also be a :limit part, allowing you to pick first N entities for processing, but I switched to SQLite before finishing that feature. Finally, if I pass an array as :into, the result set is stored in that array (which I've statically allocated previously), instead of allocating new memory. So in the end, this does the same stuff you'd write in your system, but it handles book keeping for you. The interface lends itself for extra optimizations (this is Lisp, I could parse and process the code I put in :where and :order-by clauses, though I don't do that right now). And because it's not a System, not a Thing, I end up using it more often. Say I need to code a spell that picked weak NPCs and healed them automatically. Writing a System that's responsible for handling this effect is something that wouldn't even cross my mind. But a query like in the example above? Sure, why not? So I use it, and it executes almost the same code that I'd written otherwise, except I get some optimizations handled to me automatically. It's a deep interface, and pretty clean conceptually. (There goes the other half of the blog post I wanted to write. I guess I have no excuse but to do it now.)

TeMPOraL 7y ago

Yeah, I think so too. My goal after hopefully making a completely playable game running on SQLite is to eventually rip it out and replace with my own ECS, based on gained experience. I start with explicit SQL queries, and am slowly building an interface on top of it. Or rather, I'm moving towards the interface I abandoned prior to switching to SQL. Before SQLite, I was working on my (yet another) own implementation, and eventually realized that I don't like the "Systems" part of the ECS, given I planned a lot of logic that required inspecting multiple components of any given entity. Thinking about it, I figured out that a better abstraction for a "System" would be a piece of code executed on a set of entities, defined by some selection logic. That is, a query. I quickly ended up with this interface: (select-entities :components (has-position has-health (not player)) :where ((entity-id (has-health (hit-points hp))) (< hit-points 50)) :order-by (((lhs (has-health (lhs-hp hp)) &optional (targetable (lhs-priority priority))) (rhs (has-health (rhs-hp hp)) &optional (targetable (rhs-priority priority)))) (if (and lhs-priority rhs-priority (= lhs-hp rhs-hp)) (< lhs-priority rhs-priority) (< lhs-hp rhs-hp))) :into my-array) (The example selects non-player entities with position and health components which have less than 50 HP, and orders them by HP and, if both compared entities have a "targetable" component, also by targeting priority.) Which, as you can see, looks essentially like an SQL query. :components is select + join, :where is filtering condition, :order-by saves me from doing an explicit sort on the results, and the :into my-array part allows this query to not allocate new storage for results each frame. Now each time such call showed up in my code, some extra book keeping storage would be allocated, and underlying logic ensured that most of the query doesn't have to be recomputed every frame. For instance, the :where part was handled by tracking addition and deletion of components to/from an entity. Implementing similar optimizations for :order-by part, I quickly realized that most of my "book keeping" tricks are essentially equivalent to compiled queries and indices on tables. At which point I ditched it all and switched to SQLite. (I meant to write a blog post about this at some point. I guess I just wrote half of it here.)

rstrduck 7y ago
I’m intrigued by your approach! If you do end up writing that blog post and/or releasing the source, I’d be interested in learning more.
- TeMPOraL 7y ago
  Drop me an e-mail (address in my profile), I'll let you know when the post is up.
gamdevcindy 7y ago
I've never written a game where I needed to query for entities (except spatially). Have I just not created the right type of game? If I needed to find certain types of entities they'd get added/add themselves to some list on creation. Querying things at runtime seems anathema to game dev if you want perf which most game devs obsess over
- TeMPOraL 7y ago
  > If I needed to find certain types of entities they'd get added/add themselves to some list on creation. From the point of view of the code operating on these entities, this is a query. That it happens to return results in constant time because it's being continuously executed in the background - that's just an implementation detail. The example I've pasted doesn't execute a full query every frame either. The first time it's called, it ensures that there exists an array within ECS system that stores entities which match the :components part of my query. That array is then continuously updated when appropriate components are added or removed from entities. Multiple different queries using the same :components part (which gets canonicalized, so you can write them in any equivalent order) reuse that array. The above is equivalent to a typical System in ECS, except I don't create a class or a global instance for it - I just shove a call to select-entities in the place I need it. This makes the architecture a bit more flexible conceptually, resolving some discomfort I had with Systems. The :where and :order-by parts get executed each time, on the aforementioned array. I could've made them execute when components are added or removed, at the cost of increased memory usage (no more sharing of the filtered entities array); I chose against it in this project. But this isn't really a problem; :where and :order-by subsume the code you'd typically put in your System - processing entities matching a particular condition (e.g. hp < 50), and processing them in particular order, respectively. There should also be a :limit part, allowing you to pick first N entities for processing, but I switched to SQLite before finishing that feature. Finally, if I pass an array as :into, the result set is stored in that array (which I've statically allocated previously), instead of allocating new memory. So in the end, this does the same stuff you'd write in your system, but it handles book keeping for you. The interface lends itself for extra optimizations (this is Lisp, I could parse and process the code I put in :where and :order-by clauses, though I don't do that right now). And because it's not a System, not a Thing, I end up using it more often. Say I need to code a spell that picked weak NPCs and healed them automatically. Writing a System that's responsible for handling this effect is something that wouldn't even cross my mind. But a query like in the example above? Sure, why not? So I use it, and it executes almost the same code that I'd written otherwise, except I get some optimizations handled to me automatically. It's a deep interface, and pretty clean conceptually. (There goes the other half of the blog post I wanted to write. I guess I have no excuse but to do it now.)