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.

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), theproperty_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:

> 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.

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

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):

<R1 id=365>
<P3 id=130 comment="list of some doubles">
        0.123, 3.142
        </P3>
        </R1>

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 |
+-----+------------------------+--------------+------+