<commit_before># Working with Kapua database

In current implementation of Kapua we hold device data in SQL database (it can MariaDB or H2 SQL Server). In order to keep SQL schema updated,
we decided to use [Liquibase](https://www.liquibase.org/).

## Liquibase in Kapua

Kapua broker on startup executes embedded Liquibase client which ensures that all database update scripts have been collected and
applied to your database. The coordinates of database URL connection are obtained from regular Kapua settings i.e. the following
system properties:

- `commons.db.username`
- `commons.db.password`
- `commons.db.schema`

Liquibase clients looks up for all scripts located in the classpath matching `liquibase/*.sql` pattern.

### Adding new SQL script to the project

If you would like a new SQL script to your service, just add new `*.sql` file to `src/main/resources/liquibase` directory of your Maven project. Just keep in mind that 
the name of your file should be unique, so the best way is to make it matching the name of your service module (for example `device.sql` for device management).

### Adding a new change set to existing SQL file

If you would like to add new change set to existing SQL file, append new `--changeset filename:number` line to your log file. The example:

```
--changeset device_event:2

CREATE TABLE my_new_table (
  scope_id             	    BIGINT(21) 	  UNSIGNED NOT NULL
  PRIMARY KEY (scope_id, id)
) CHARSET=utf8;
```

More details regarding Liquibase SQL file syntax, can be found on [Liquibase web page](http://www.liquibase.org/documentation/sql_format.html).<commit_msg>Liquibase documentation update after PR #788 Fixes #823<commit_after># Working with Kapua database

In current implementation of Kapua we hold device data in SQL database (it can MariaDB or H2 SQL Server). In order to keep SQL schema updated,
we decided to use [Liquibase](https://www.liquibase.org/).

## Liquibase in Kapua

Kapua REST APIs, Admin Console and Broker can use, upon their startup, the embedded Liquibase client which ensures that all database update scripts have been collected and
applied to your database. The coordinates of database URL connection are obtained from regular Kapua settings i.e. the following
system properties:

- `commons.db.username`
- `commons.db.password`
- `commons.db.schema`

Please note that the scripts execution is disabled by default. To enable it, set the system property `commons.db.schema.update` to true when running any of the applications.

Liquibase clients looks up for xml scripts located in the classpath matching `liquibase/(*-master.pre.xml|*-master.xml|*-master.post.xml)` pattern. 

Such files will be collected all together from the plugin, then sorted in three distinct sets: all the `*-master.pre.xml` files first, then the `*-master.xml` files and finally `*-master.post.xml` files. Every set will be sorted by path name; then the three sets will then be executed in the same same order (the `master.pre` first, then the `master` set and finally the `master.post` set.)

Every master file must contain at least one reference (via the `<include>` Liquibase XML tag) to a version-specific changelog file, usually contained in a folder named after the service version. Such changelog will, in turn, contain references to the actual Liquibase XML files that gets executed by the Liquibase client.

### Adding new XML script to the project

If you would like a new Liquibase script to your service, just add new `*.xml` file to `src/main/resources/liquibase/_service-version_` directory of your Maven project. Just keep in mind that 
the name of your file should be unique, so the best way is to at least reference the name of your service module (for example `device-new-creation-script.xml` for device management). Also, after you create the XML, be sure to add a reference to it in the main changelog XML for the version. If such file doesn't exist yet, because it's releated to a new version of the service, create it and add a reference to it in an appropriate master XML file.

More details regarding Liquibase XML file syntax, can be found on [Liquibase web page](http://www.liquibase.org/documentation/xml_format.html).
{'fraction_non_alphanumeric': 0.0656986431801952, 'fraction_numerical': 0.0028564627469650082, 'mean_word_length': 4.401028277634961, 'pattern_counts': {'":': 0, '<': 4, '<?xml version=': 0, '>': 4, 'https://': 2, 'lorem ipsum': 0, 'www.': 4, 'xml': 10}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2629886', 'n_tokens_mistral': 1140, 'n_tokens_neox': 1071, 'n_words': 600}