# Setup of the CaosDB SQL back end

## Dependencies

* `MariaDB Client 10.1` or later, `MySQL Client >=5.5, <=5.7.36`.
* make

## Create the configuration
* Create an empty `.config` file. For the default values and the meaning of
  these default values see [config.defaults](config.defaults).  For each
  parameter that you want to change, add a corresponding line in your `.config`
  file. You probably want to change the passwords.  As the passwords are stored
  unencrypted in the `.config` file, make sure nobody else can read it.
    * If there is no `mysql-config-editor` (`MySQL 5.5`. and `MariaDB`) then the
      `MYSQL_USER_PASSWORD` must be provided, that is the password of the
      `MYSQL_USER`.
    * If you are using MariaDB and the `root` database user uses pam
      authentication, no pasword string is required.  You can set the
      `MYSQL_USER_PASSWORD` to an empty string.  But you need to be logged in as
      root for the installation and upgrade process.

## Setup the SQL database for CaosDB

* Run `make install`. If a there is a database with the name you have choosen
  during the configuration, you need to reconfigure or delete the database
  first.
  * *Required database privileges:*
    * If the user does not exist yet, you need the [appropriate global privileges](https://mariadb.com/kb/en/grant/#global-privileges), for example
      `CREATE USER` and the privileges to grant that user all global privileges.  The required
      privileges may be reduced in the future.
    * For normal usage, [database privileges](https://mariadb.com/kb/en/grant/#database-privileges) are required.

## Upgrade the SQL database

* Run `make upgrade`. This upgrades the database to the latest version.

## Drop Database

* If you want to delete your database, run `make drop-$DATABASE_NAME`. **Use
  this with caution!!!** If you did not backup your database, **everything will
  be lost afterwards.** And no, there is *no* additional prompt to ask if you
  are sure.  If you `make drop-...`, you *actually* delete the database.

## Versioning

The versioning feature is still experimental. Therefore it is possible to turn
if on and off with a patch file and a special property.

### Procedures and Functions

The procedures which need to behave differently if the versioning is on or off
check the return value of `is_feature_config("ENTITY_VERSIONING", "ENABLED")`.
The `is_feature_config` function checks the `feature_config` table, which is
a key-value store.

Turn off versioning: Run `UPDATE feature_config SET _value = "DISABLED" WHERE
_key = "ENTITY_VERSIONING";` on your database.

Turn on versioning again: Run `UPDATE feature_config SET _value = "ENABLED"
WHERE _key = "ENTITY_VERSIONING";` on your database.

### Data

When the versioning patch is installed, the versioning is turned on by default
and all old entities become versioned entities with their current version as
the oldest known version. That is, they all need an entry in the
`entity_version` table. These entries are generated by the `_fix_unversioned`
procedure.

If you want to turn off the versioning for the time being you can just turn it
off for the procedures as described above. You should also empty the
`entity_version` table because the `_fix_unversioned` procedure is only
designed to cope with entities which do not have any versioning information at
all. The already recorded versioning information is of course lost then! If you
switch on the versioning at some point in the future, the history begins anew
with the then current version of the stored entities.

## Unit tests

* We use [MyTAP-1.0](https://hepabolu.github.io/mytap/) for unit tests.
* Requirements:
    * mysqladmin
    * mysqldump
    * mysql client program
* Tests are in `tests/test_*.sql`.
* Run `make test`.
    * Alternatively, to run the tests in a containerized MariaDB instance, run `make test-docker`,
      followed by `make test-docker-stop`.


### Running in a Docker Container

You can use `.docker/docker-compose.yml` to start a docker container
(`docker-compose -f .docker/docker-compose.yml up -d`) that runs mariadb. You
need appropriate settings in `.config`:
* `MYSQL_OPTS="--protocol=TCP"` and
* `DATABASE_USER_HOST_LIST=%,`
After the first start, you need to install the database: `make install`.
Then, you can connect to it with `mariadb --protocol=TCP -u caosdb -prandom1234
caosdb`.


### Troubleshooting

#### MySQL has failing tests

Our test suite is developed with MariaDB. That is why some tests, those which
check constraints based on auto-generated names, fail because MySQL generates
other names.

Also some test fail with the message "Expected: NULL / Was: NULL" which is
obviously what was expected.

Please look carefully through the tests. If something more serious than that
comes up, please report a bug.

## Documentation #

Build documentation in `build/` with `make doc`.

## Requirements ##

- sphinx
- sphinx-autoapi
- python3-sqlparse
- doxygen
- python3-lxml
- breathe (Doxygen extension for Sphinx)
- recommonmark