--- last_review: "2025-01-01" last_reviewer: "-" documented_code: [ ] --- ```{tags} explanation ``` # Tables :::{note} This page has been migrated from the old documentation, and has not yet been fully revised. There might be inconsistencies or errors when using with current LinkAhead versions. ::: % TODO: Issue: https://gitlab.indiscale.com/caosdb/src/linkahead-docs/-/issues/79 % TODO: Why is the isa table empty on the default example? % TODO: Which algorithm is used for file hashes? % TODO: Document [the other tables section](#other-tables) This document describes the SQL tables used by LinkAhead and how they represent the internal data structure. ## entities All entities (RecordTypes, Records, Properties, ...) have an entry here. The columns are: - `id` :: The (unique) ID of this entity. - `name` :: Name of the entity. - `description` :: A description of the entity. - `role` :: The role of the entity (e.g. data type, RecordType, Property, Record, ...). - `acl` :: Access control ID. ## isa Inheritance as in "A is a B" is stored in this table. The columns: - `child` :: The child entity. - `parent` :: The parent entity. - `type` :: The inheritance type (either `INHERITANCE` or `SUBTYPING` (what's "subtyping"?)). ## isa_cache Here, information about nested inheritance is cached (upon creation?). - `child` :: The child entity. - `parent` :: The parent entity. - `rpath` :: Possible intermediate steps on a path from `child` to `parent`. See the *Inheritance* section for more details. ## foo_data Property data is stored here, in tables named after the data type: - date_data - datetime_data - double_data - integer_data - text_data - enum_data - name_data - null_data - reference_data These tables share (generally) the same set of columns: - `domain_id` :: The domain of an item, see the *multipurpose subdomains* section. For "normal" data, this is 0. - `entity_id` :: The entity to which this property belongs. In most cases, this is a "normal" entity like a `Record` or `RecordType`. - `property_id` :: In most cases, the property to which the value belongs. - `value` :: The value of the property. - `status` :: The importance of the property (?). - `pidx` :: The property index, becomes nonzero when a property occurs more than once in an entity. - `real_property_id` :: If the data belongs to a *replacement entity* (see for example the "multipurpose subdomain" section below), the`property_id` column does not show the ID of the actual property, but of the replacement entity. The `real_property_id` column however always shows the ID of the actual property. ### `name_data` This table is used to name RecordTypes, Records, Properties etc. The column `property_id` has the value 20, because that is the entity ID for names: ```sql > SELECT * FROM entities WHERE id=20; +----+-------------------+----------+------+ | id | description | role | acl | +----+-------------------+----------+------+ | 20 | Name of an entity | PROPERTY | 0 | +----+-------------------+----------+------+ ``` ## data_type The type of properties is stored here. The columns are: - `domain_id` :: Property identification - `entity_id` :: Property identification - `property_id` :: Property identification - `datatype` :: The data type of the property, a reference to an entity. - % TODO: First three entries had ', same as for plain data?' at the end. If true, re-add without % TODO: question mark ## files - `file_id` :: ID. - `path` :: (Relative) path to the file. - `size` :: Size in bytes. - `hash` :: Hash of the file contents, as binary. - `checked_timestamp` :: Timestamp when last checked - % TODO: checked_timestamp had a question mark ## `feature_config` ``` +-------------------+---------+ | _key | _value | +-------------------+---------+ | ENTITY_VERSIONING | ENABLED | +-------------------+---------+ ``` A key-value store for features. Use the `is_feature_config` procedure to check for the status of specific features. ## Versioning tables There are a few extra tables for implementing versioning. Also, there is a special column `value_iversion` in the `reference_data` table which stores the version of the referenced entity at the time of the version. If `value_iversion` is NULL, no specific version is stored. The `_iversion` is an incremental version counter, starting implicitly at 1. Previous versions of entities are stored, along with their `_iversion`, in the `archive_*` tables. ### `transactions` Replacement for `transaction_log`, holds ID, user and time of transaction: - `srid` :: Server request ID, used to identify transactions - `username` :: User name - `realm` :: Realm for which the username is valid - `seconds` :: Time of transaction: seconds - `nanos` :: Time of transaction: sub-second time resolution ### `entity_version` Versioning info for entities: - `entity_id` :: persistent ID of the entity - `hash` :: Hash, for future use. - `version` :: External version string, may be globally unique. Should be used by API calls. - `_iversion` :: Version ID for this entity for internal use, typically an incremental counter, starting with 1 - `_ipparent` :: Primary parent ID (internal) for this version, i.e. predecessor - `srid` :: Server request / transaction ID which created this version ### `archive_footable` Older (not current) data, from previous versions of an entity which were stored in `footable`. - `domain_id` :: Same as in `footable`. - `entity_id` :: Same as in `footable`. - `property_id` :: Same as in `footable`. - `value` :: The value at the given version. - `status` :: The value at the given version. - `pidx` :: Same as in `footable`. - `_iversion` :: Version index of this version. - ... :: Data type specific columns may also be there. ## Other tables - `entity_acl` - `groups` - `logging` - `passwd` - `passwords` - `permissions` - `query_template_def` :: User-defined query templates - `roles` :: possible user roles - `stats` :: benchmarking - `transaction_log` :: benchmarking - `units_lin_con` :: fraction and unit conversion - `user_info` :: Metadata for (local?) user accounts - `user_roles` :: User permissions # Inheritance caching The `isa_cache` table does not only store direct inheritances, but also nested parentship. This is done by adding intermediate inheritance steps in the `rpath` column: - For *direct inheritance*, the `rpath` column contains the child itself. - For all other *possible paths* between the child and parent, it contains the entities between, separated by the `>` character. ## Example Consider this inheritance structure and the resulting table: ``` 0 Read this from top to bottom: "1 is a 0", / \ "2 is a 0", and so on. 1 2 \ / 3 | 4 | 5 ``` | child | parent | rpath | |---------|----------|---------| | 1 | 0 | 1 | | 2 | 0 | 2 | | 3 | 0 | 1 | | 3 | 0 | 2 | | 3 | 1 | 3 | | 3 | 2 | 3 | | 4 | 0 | 3>1 | | 4 | 0 | 3>2 | | 4 | 1 | 3 | | 4 | 2 | 3 | | 4 | 3 | 4 | | 5 | 0 | 4>3>1 | | 5 | 0 | 4>3>2 | | 5 | 1 | 4>3 | | 5 | 2 | 4>3 | | 5 | 3 | 4 | | 5 | 4 | 5 | |---------|----------|---------| # Multipurpose subdomains Multipurpose subdomains are generically used to work with composite properties. Currently implemented examples are: - Properties with units (if multiple Properties exist) - Lists ## Example Let's have a look at this *Record* (simplified XML): ```xml 0.123, 3.142 ``` ## How it works In the above example, the `double_data` would have the following rows: ``` | domain_id | entity_id | property_id | real_property_id | value | status | pidx | unit_sig | |-----------|-----------|-------------|------------------|-------|--------|------|-------------| | 0 | 365 | 114 | 130 | 0.123 | FIX | 1 | -1941227323 | | 0 | 365 | 114 | 130 | 3.142 | FIX | 0 | -1941227323 | ``` and the table `reference_data` looks like this: ``` | domain_id | entity_id | property_id | real_property_id | value | status | pidx | value_iversion | |-----------|-----------|-------------|------------------|-------|-------------|------|----------------| | 0 | 365 | 130 | 130 | 114 | REPLACEMENT | 2 | NULL | ``` This means that a replacement property/entity with id 114 was created and added to the reference table and in the data table it appears as if the entity had that property 114. This Property can be used for many instance properties and the row in the `entity` table would be ``` +-----+------------------------+--------------+------+ | id | description | role | acl | +-----+------------------------+--------------+------+ | 114 | Multipurpose subdomain | _REPLACEMENT | 0 | +-----+------------------------+--------------+------+ ```