---
last_review: "2025-01-01"
last_reviewer: "-"
documented_code: [ ]
---

```{tags} developer, explanation
```

(mysql-backend)=

# 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.
:::
% TODO: Issue: https://gitlab.indiscale.com/caosdb/src/linkahead-docs/-/issues/114

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](/reference/server/api/org/caosdb/server/database/backend/interfaces/package-index.rst)

: 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](/reference/server/api/org/caosdb/server/database/backend/implementation/MySQL/package-index.rst)

: 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](/reference/server/api/org/caosdb/server/utils/UndoHandler.rst).

[backend.transaction](/reference/server/api/org/caosdb/server/database/backend/transaction/package-index.rst)

: Subclasses of the abstract [BackendTransaction](/reference/server/api/org/caosdb/server/database/BackendTransaction.rst)
  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:

```{eval-rst}
.. uml::

  @startuml
  together {
    abstract BackendTransaction {
      HashMap impl // stores all implementations
      {abstract} execute()
    }
    note left of BackendTransaction::impl
      Stores the
      implementation
      for each
      interface."
    end note
    package "backend.interfaces" {
      interface GetIDByNameImpl {
        {abstract} execute(String name, String role, String limit)
      }
    }
  }
  together {
    package "backend.transaction" {
      class GetIDByName extends BackendTransaction {
        execute()
      }
    }
    package "backend.implementation.MySQL" {
      class MySQLGetIDByName implements GetIDByNameImpl {
        execute(String name, String role, String limit)
      }
    }
  }

  GetIDByName::execute --r-> MySQLGetIDByName
  @enduml

```

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

% TODO: How can we store a diffrent TODO??? For example, how to store that the datatype of this Property is different?

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)

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