Migration from an ECS Architecture

[ImVexed]

Interested in trying to migrate a server authoritative tower defense game I made in bevy to Spacetime, and was curious if anyone has any material for how to reason about implementing game logic in Spacetime from an ECS?

I've seen the example repos, and how they use timers. But I'm struggling to grasp how, for example, one would handle multiple entities with a shared component. Like a player and mob both having a position/velocity to be ticked every 50ms or so. Do you just setup multiple timers like in the example? How do you mitigate duplicated code with many entity types? Do you foreign key and setup a position/velocity table?

Are there performance concerns in keeping all data structures an ECS would normally have in the DB, while using tables like individual components? like having an entity table with ids, then a velocity/position table with their relevant data and a foreign key into the entity table? Are there performance concerns for the big SQL joins you'd have to do to find all entities that share 5 or so components?

[cryo-warden]

I'm experimenting with this now. I have some experience with ECS, but I'm new to both Rust and SpacetimeDB.

The lack of querying power on the server side is an interesting obstacle. A server module doesn't query columns, but only the indexes you have defined. The server library cannot perform join queries at all. You are expected to write the join logic yourself. It may be reasonable to use SpacetimeDB with ECS principles in mind, but it does not currently offer the basics of ECS.

---

My current best guess for how SpacetimeDB could make early progress to better support ECS:

1. First, support querying of multiple indexes (or add a new attribute to auto-generate different multi-column indexes from every combination of fields with said attribute).
2. Second, support indexing and searching on optional columns, such that an entity can be defined as a struct containing optional components, and rows with the same presence/absence of any given optional column are clustered in the in-memory store.
   • You can't query on non-empty enums at all right now, even if they're indexed. Maybe a macro is needed to derive a queryable type from the non-empty enum? For instance Option<i32> would generate a query type of the form Option<Query<i32>>, where the Query<i32> would be anything that can filter an i32, such as a range or an exact value match.

But even if this approach worked, they may still need to do something (challenging) about the memory overhead of reserving unused space to hold many None columns. A more experienced Rust dev could likely improve immediately on my ideas here.

---

An alternate approach that comes to mind for better ECS support would be generating a different table for every possible archetype. Some attribute #[component(name = component_name, entity_type_for_grouping)] would mark the component structs. This would require designing a whole new way to query, since

1. a query would always start by covering all the archetype tables, and
2. instead of being able to filter by values, you would only be selecting some subset of archetypes compatible with your query, and
3. the result would be a struct that implements some generated trait for each component_name, to fetch that component value.

The underlying data layer might still be SpacetimeDB, but the interface would be completely different.

Example:

let query = ctx.ecs.entity_type_for_grouping.with_hp().with_position().with_hitbox().without_invulnerable();
for entity in query {
    let hp = entity.hp();
    let p = entity.position();
    // etc...
    ctx.ecs.update(entity).hp(new_hp);
}

[andrewbgross]

@cryo-warden (and @ImVexed, although my reply is written as if to @cryo-warden),

Please forgive me, I'm completely new to SpacetimeDB. It's also been over 30 years since I worked for Oracle and considered myself competent to hold any sort of weighty opinion on anything SQL DB related.

Could you elaborate on why you see "a server module doesn't query columns, but only the indexes you have defined" as being an obstacle? In general, you only ever want to query based on columns you have indexed, anyway; the moment you're using a non-indexed column as part of your SQL query WHERE clause, you've just tanked performance for that query. Unless things have changed dramatically over the last 30 years (when I type it out, that doesn't seem nearly as unlikely as when I had the original thought), querying on a non-indexed column would be unthinkable for something like an airline reservation system and also, presumably, for a real time MMORPG.

I'm mildly surprised that the libraries Clockwork Labs provide for accessing SpacetimeDB don't have syntactic sugar for emulating joins, since unless I'm missing something they should just be nested iterator or for loops, or maybe nested list comprehensions. Perhaps the concern is that the most efficient order to nest the loops in depends on knowing the expected ratio of rows that match the criteria for each table, so that there's no generalized way of providing this functionality as efficiently as possible? Or maybe I'm just missing something that makes it more complicated than returning the results of a query on Table A, then using one or more values within that result set to construct a query into Table B, and so on until you've "joined" every required table. If it were feasible for them to provide a general purpose "syntactic sugar JOIN API", I suspect it would be useful to establish a convention whereby the order in which tables are listed in the JOIN clause corresponds to the order that the API nests the loops, so the first table listed is the outermost; this would allow Clockwork Labs to provide a general purpose API as part of their libraries, while also giving the programmer the ability to leverage knowledge about how the program will be using the tables to nest the iterators in the most efficient way.

If for whatever reason Clockwork Labs wouldn't want to provide such an API, I don't think it would be a great chore to write that functionality oneself, as part of implementing an ECS system, maybe making it its own github repository and sharing that virtual-JOIN API with anyone who wants it on its own.

It's not clear to me if OP was suggesting or hinting at this or not, but rather than inventing a new ECS system, I would think a more desirable approach would be to make a version of Bevy that uses SpacetimeDB to store the data for its ECS, instead of whatever backing storage it's using now. I have no idea if they manage it all with files and memory allocations, or if they have some lightweight DB library, or something else; but whatever it is, it could presumably be replaced with SpacetimeDB, albeit with an unknown amount of design and implementation pain to get there. Since Bevy is Open Source, one could even fork it and make their own version of Bevy, submitting it back for consideration as an optional feature in the main project if it turned out well.

Again, my apologies if these thoughts wildly miss the mark due to my ignorance of current RDBS technology and nearly complete lack of knowledge of SpacetimeDB in particular; it's just that I was already thinking of whether it would make sense to make a SpacetimeDB-aware version of Bevy before I even saw this thread, and I'm excited to discuss the possibilities with someone.

[cryo-warden]

That's a great question. I wasn't clear. It's reasonable to only query over indexed columns. However, you can't query an index over certain column types, especially Option. (It might be reasonable to implement the FilterableValue trait, so I could be wrong.) This means you can't use denormalized ECS components, since you can't query over the presence/absence of components. You also may not get the main benefit of ECS because you wouldn't be iterating over contiguous chunks of memory without branching (unless SpacetimeDB moves the rows in its memory cache to group rows with the same index values, for frequently-used indexes).

Another issue is that you can't query more than one index at a time in SpacetimeDB, so querying for any given combination of components requires its own index for that combination.

Normalized components may be better. You can trivially iterate every component of a given type. Keeping the entity ID indexed in each component table allows you to directly look up the entity's other components. There's a lot of boilerplate, though. It may be best to design some helper macros for this approach.

The major problem I expect with normalized components is that you'd have a lot of joins, and they'd be wildly inefficient. As you said, each table joined would be another loop, or at least an index lookup. It also means a lot of branching. I don't expect the benefits of ECS to survive this approach.

I'm still running other experiments. I built some small systems with normalized components. Now I'm trying out an archetype-table approach, using macros to generate some query structs to iterate over every archetype which has a given component set. Entities with the same components are actually grouped into the same tables this way.

There are some drawbacks to archetypes, unfortunately:

• To allow different tables in the same iterator, I must either box the rows with a trait object or copy the components into a common result struct. I will need performance testing to determine which approach does the least harm.
• The logic for pushing updates is a bit messy.
• Changing the components on an entity would require moving the entire entity to another table. Shortlived components like flags and timers are still normalized, for exactly this reason. This suggests that the entire approach is fundamentally flawed, but maybe a compromise is exactly what's needed?
• Each archetype and query must be declared in the same DSL macro, or else the macro would need to auto generate every possibility, creating 2^n tables where n is the number of possible components. The macro would also output on the order of 2^n types for queries and archetypes, requiring a lot of generated names and some sane way to choose the type of query you want to use at call sites.
• Looking up an entity by entity ID alone requires additional logic to route to the correct archetype table. I have (kind of) solved this with an enum in the main entities table.

So far I'm only experimenting with developer experience. (Also I'm learning a lot about Rust.) Performance experiments can follow after I've found a workflow that's at least usable for each approach.

After my archetype subproject, I'll be returning to my normalized subproject with some fresh proc macro knowledge.

But perhaps the real solution is what you suggested, creating a bevy integration. However, both projects are heavily invested in memory management logic. Marrying the two together seems like a huge challenge. I will need to check how bevy's components are laid out in memory and how its queries work.

---

Example of the archetype macro I'm working on:

ecs! {
  shared {
    #[primary_key]
    entity_id: u64,
  }

  components {
    location: LocationComponent,
    path: PathComponent,
    inventory: InventoryComponent,
    equipment: EquipmentComponent,
  }

  #[table(name = actor_archetypes)]
  archetype Actor table actor_archetypes {
    location,
    inventory,
    equipment,
  }

  #[table(name = path_archetypes)]
  archetype Path table path_archetypes { location, path }

  query WithLocation { location }
}

// ...

// for location_entity in WithLocation::iter(ctx) {
//     Do something involving location_entity.location and/or location_entity.entity_id
// }

[cryo-warden]

Experimenting with a macro to support ECS normalized tables: https://github.com/cryo-warden/trpg/blob/stdb-pilot/ecs%2Fsrc%2Ftests%2Fmod.rs

• This example builds along with spacetimedb macro expansions
• Feedback about features to implement if you please!
  • Will be adding some iteration impls to components and to the entity handle type.

use ecs_macro::entity;

pub type EntityId = u64;

entity! {
  entity Entity entity_id: EntityId tables();

  component LocationComponent [
    (location, location_components),
    (secondary_location, secondary_location_components),
  ] {
    pub location_entity_id: EntityId,
  }
  component PathComponent [
    (path, path_components),
    (excess_path, excess_path_components)
  ] {
    pub destination_entity_id: EntityId,
  }
}

impl LocationComponent {
    pub fn new() -> Self {
        Self {
            entity_id: 0,
            location_entity_id: 0,
        }
    }
}

impl<'a> EntityHandle<'a> {
    pub fn peek() {}
}

#[allow(unused_variables, dead_code)]
fn sandbox(ctx: &spacetimedb::ReducerContext) -> Option<()> {
    let e = EntityHandle { entity_id: 0, ctx };
    LocationComponent::new();
    EntityHandle::peek();
    let e = e.with_path()?.with_location()?.with_secondary_location()?;
    e.location();
    e.path();
    e.secondary_location();
    Some(())
}

[cryo-warden]

I added filter-by-component iteration to the macro I'm working on, which basically accomplishes joins via index lookups by shared entity ID. Whichever with calls follow an iterator over a "definitely has the component" wrapper, the results are those items which definitely also have the newly-mentioned component (in addition to any other components which were definitely present earlier in the chain).

If you don't filter by a certain component field, then you will instead get an Option when calling the component_name() method.

It is all static-dispatch only.

    for lp in LocationComponent::iter_location(ctx).with_path() {
        println!("{:?}", lp.path());
    }
    for pl in PathComponent::iter_path(ctx)
        .with_location()
        .with_excess_path()
        .with_secondary_location()
    {
        println!("{:?}", pl.location());
        println!("{:?}", pl.path());
        println!("{:?}", pl.excess_path());
        println!("{:?}", pl.secondary_location());
    }

[caniko]

Could you share the reasoning behind the migration? Just curious because it is a TD game.

In other words:
Most TD games are singleplayer, what is the motivation behind the use of a framework for an MMO?