Tags: developer, explanation

MariaDB Backend

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.

The MariaDB backend in CaosDB may be substituted by other backends, but at the time of writing this documentation, only MariaDB (often still named MySQL in the sources) is implemented. There are the following main packages which handle the backend:

backend.interfaces

Interfaces which backends may implement. The main method for most interfaces is execute(...) with arguments depending on the specific interface, and benchmarking methods (getBenchmark() and setTransactionBenchmark(b) may also be required.

backend.implementation.MySQL

MySQL implementations of the interfaces. Typical “simple” implementations create a prepared SQL statement from the arguments to execute(...) and send it to the SQL server. They may also have methods for undoing and cleanup, using an UndoHandler.

backend.transaction

Subclasses of the abstract BackendTransaction which implement the execute() method. These classes may use specific backend implementations (like for example the MySQL implementations) to interact with the backend database.

For example, the structure when getting an Entity ID by name looks like this:

Mapping of Objects to Database Tables

Mapping Entities with deep Properties

Understanding the problem

In LinkAhead, Properties of Entities may overwrite default datatypes of abstract Properties. The SQL representation of simple properties is a row in a data table. For example (simplified):

domain_id	entity_id	property_id	value
0	413	230	V1

Now the SQL representation of this property tree consists of two parts:

• The property_id and value columns describe the kind of property and of course the value.

• All the other columns describe where this property can be found:

  • “Direct” properties, i.e. those at the base of the tree (level 1), have domain_id=0 and the entity_id of their corresponding entity.

  • Sub-properties of direct properties (level 2) use the entity’s ID as domain_id.

  • Everything below level 2 needs a new way of denoting their place in the property tree, and that is where replacement properties come into play.

Simple examples

Two simple examples, which can be represented without replacement properties:

This entity with a text property (id=230)

<Entity id=413>
    <Property id=230>
        <value>V1</value>
    <Property/>
</Entity>

will be represented in the text_data table as

domain_id	entity_id	property_id	value	status
0	413	230	V1	FIX

The real_property_id column

Because the property_id column may contain the ID of a replacement, it is not obvious, what the ID of the Property at the Record is when looking at such a row in the data table. Therefore, there is an additional column with redundant information to simplify things and to speed up lookups: The real_property_id column. It always contains the ID of the Property of the Record, never the ID of a potential replacement entity.

In the example above, the first rows of the text_data table with the real_property_id column:

domain_id	entity_id	property_id	real_property_id	value	status
0	413	113	230	V1	FIX
0	413	114	230	V2	FIX
0	413	138	230	V3	FIX
0	413	230	230	V1b	FIX

The IDs 113, 114 and 138 belong to replacements, thus the real_property_id shows a different ID. In the fourth line, real_property_id is simply the same as property_id.