Problem (Why)

The entity manager currently supports only Class Table Inheritance (CTI), where each class in a hierarchy stores only its own attributes and queries require joins across all parent tables. For read-heavy workloads with deep hierarchies this introduces significant query complexity and performance overhead, as every retrieval must join multiple tables to reconstruct a full entity.

Additionally, there is no tooling to migrate existing data between inheritance strategies. A change in strategy would require manual schema and data manipulation — a risky and error-prone process that discourages experimentation and adoption.

The lack of an alternative strategy also forces all entity hierarchies into the same trade-off (normalized storage vs. query complexity), even when a specific hierarchy would clearly benefit from a denormalized, single-table-per-class approach.

Description (What)

Introduce a new Concrete Table Inheritance (CTI per-class) strategy that coexists alongside the existing Class Table Inheritance model. When an entity hierarchy opts into this strategy, each concrete (non-abstract) class produces a single table containing all attributes (own + inherited), eliminating the need for joins on read. The strategy should be selectable per entity hierarchy via a class-level attribute, preserving full backward compatibility with existing CTI entities.

Alongside the new strategy, a standalone migration script will be provided to safely convert existing entity hierarchies between class_table and concrete_table. The script will support automatic backup, validation, dry-run, and transactional execution — so that teams can adopt the new model on live databases without manual intervention.

The entire change must be thoroughly covered with unit tests to ensure no regressions in the existing CTI path and full correctness of the new strategy and migration tooling.

Implementation (How)

Strategy and core entity changes

• 1. Design the strategy selector — Add a class-level attribute (e.g. inheritance = "concrete_table") to EntityClass in structures.py that defaults to the current behavior ("class_table"). Add a helper get_inheritance_strategy() method.
• 2. Extend get_items() / add get_all_items() — Create a method (or mode) in structures.py that returns all attributes including inherited ones from concrete and abstract parents, flattened into a single dict. This will be used by the concrete-table code path.

Query generation

• 3. Update _create_definition_query — In system.py, branch on the inheritance strategy:
  • For concrete_table: emit a single CREATE TABLE with all inherited + own columns, always include the _class discriminator, and skip the "upper reference" FK to the parent table.
  • For class_table: keep current behavior unchanged.
• 4. Update create() flow — When strategy is concrete_table, skip recursive parent-table creation for the current hierarchy (parent tables are only created if they are themselves concrete and used independently).
• 5. Update _save_query — For concrete_table entities, generate a single INSERT into the entity's own table with all attributes (own + inherited) instead of multiple INSERTs across parent tables.
• 6. Update _update_query — For concrete_table entities, generate a single UPDATE targeting the entity's own table instead of multiple UPDATEs across parent tables.
• 7. Update _delete_query — For concrete_table entities, generate a single DELETE from the entity's own table.
• 8. Update retrieval / find queries — For concrete_table entities, generate SELECT from the single table without joins to parent tables. Polymorphic queries across a hierarchy should UNION ALL across sibling concrete tables when querying by a parent type.

Relations and engine support

• 9. Handle relation mapping — Ensure mapped relations (FKs) and unmapped relations (association tables) work correctly with the flattened table layout.
• 10. Update database engines if needed — Review entity_sqlite, entity_mysql, and entity_pgsql engine implementations for any assumptions about one-table-per-class-level and adjust accordingly.

Migration tooling

• 11. Add migration script — Create a standalone script (e.g. scripts/migrate_inheritance.py) that migrates an entire entity hierarchy between class_table and concrete_table strategies. The script should:
  • Accept the root entity class and target strategy as arguments.
  • Traverse the full hierarchy downward and migrate all concrete descendants.
  • CTI → Concrete Table: join parent tables into flat tables, copy data, update FK references from other entities, rebuild indexes.
  • Concrete Table → CTI: split flat tables into per-class-level tables, distribute columns and data accordingly, update FK references, rebuild indexes.
  • Run the entire migration inside a transaction (atomic rollback on failure).
  • Automatically create a backup before migrating (e.g. copy the DB file for SQLite, pg_dump for PostgreSQL, mysqldump for MySQL).
  • Support a validate mode that checks whether a hierarchy can be migrated (no unsupported mixed configurations, no orphaned references) without executing any SQL.
  • Support a dry-run mode that prints the SQL to be executed without committing changes.
  • Assume offline execution (application stopped, exclusive DB access).

Testing

• 12. Add test entities in mocks.py — Create a parallel set of test entity classes using inheritance = "concrete_table" (e.g. ConcreteRootEntity, ConcretePerson, ConcreteEmployee) to mirror the existing CTI test hierarchy.
• 13. Add unit tests — Cover at minimum: table creation, save, update, delete, single-entity retrieval, polymorphic retrieval across hierarchy, relation handling (1:1, 1:N, M:N), and mixed hierarchies (CTI parent with concrete-table child should raise or be documented).
• 14. Add migration tests — Using the existing mock entities, test both migration directions (CTI → Concrete Table and Concrete Table → CTI). Verify data integrity after migration (row counts, attribute values, relation consistency), validate mode behavior, dry-run output, backup creation, and transaction rollback on simulated failure.
• 15. Regression tests — Run the full existing test suite to verify zero regressions on the Class Table Inheritance path.

Documentation

• 16. Update CHANGELOG.md — Document the new feature under [Unreleased].