Topology database

The engine needs a database engine to store the necessary tables and execute the update logic. The following database are supported:

  • SQLite. Can be very fast if multiprocessing access is disabled. "Schema name" is used as a table prefix.

  • MS SQL server.

  • PostgreSQL.

  • MySQL. Has some performance penalty if propagation is configured due to the missing 'update with output' construct.

Database schema

The schema is being generated and maintained by the engine. The initialization of the engine creates or actualizes the schema. The database can be initialized without the other parts of the engine.

The schema consists of the following tables:

  • Entity types. List of enumerated values to define entity types.

  • Link types. List of enumeratued values to define link types.

  • Entities. Main table containing an entry for all known entities and also holding their main hierarchy. This is a snapshot about the current state of the entity.

  • Links. Holds a record for each temporal links between entities.

  • xxAttributes. One table for each attribute type. The respective table holds a record for each temporal attribute record of an entity or link.

Diagram illustrating the database schema relationships described in the previous list.

Foreign keys

  • Entity types is referred as a foreign key from the entities table.

  • Link types is referred as foreign key from the links table.

  • The origin (first entity) of links refers to the entities as foreign key and also cascades deletes upon deleting the entity.

  • The attributes are referring to their owning entity or link as a foreign key, with cascade delete.

  • The foreign keys serve the purpose that all attached (owned) items can be deleted from the database by deleting an entity (quick-purge, so the engine continues to perform).

  • If partial updates are set-up for certain attribute sets, then for the given attribute sets the engine won't set up cascade deletion.

Database initialization

The database is initialized and updated during the initialize phase of the engine lifetime.

If this happens outside engine usage, it is possible to initialize only the database without using the engine. It also makes the built-in database library available.

Database access

The database can be accessed from the built-in database library or by directly accessing it with the desired database framework.

Do

  • The schema (or prefix in the case of SQLite and MySQL) can be queried.

  • Columns and indexes can be added, but the columns must be nullable or have a default value.

  • Tables can be queries and joined to queries.

Don't

  • Do not define foreign keys for the tables.

  • Do not write or delete records to the tables outside of the sync engine or it's triggers.

  • Do not modify the schema by any means other than the sync engine or the provided tooling.