How to use Liquibase to make changes to the database

Project Structure

All LSA database related projects reside in the LSA Gitea Organisation https://git.acc.gsi.de/lsa, usually for configuring the database content (like makerules, element mappings etc.) it is not necessary to check out all of them. If you only want to change the configuration you do not need the 'EXPERT' projects, they are only needed for making structural changes to the datase and/or updating the importers that load the configuration.

These are the existing projects and their structure from 2017 (To see a live version visit: https://git.acc.gsi.de/lsa?sort=alphabetically&q=lsa-db&tab=):

https://www-acc.gsi.de/svn/lsa/db/
|-- lsa-db-scripts  - EXPERT, generic scripts for all databases like structural changes
|-- lsa-db-importer - EXPERT, java code for the importers
|-- lsa-db-data-dev - Data (makerule entries, devices, etc.) for the development database (LSA@AccDbU) 
|   |-- src
|   |   |-- main
|   |   |   |-- liquibase
|   |   |   `-- resources
|   |   |       `-- de
|   |   |           `-- gsi
|   |   |               `-- lsa
|   |   |                   `-- db
|   |   |                       `-- importer
|   |   |                           `-- spi
|   |   |                               |-- elements
|   |   |                               |-- makerules
|   |   |                               |-- particletransferrelations
|   |   |                               `-- transferlines
|-- lsa-db-data-int - Data for the integration/test database (LSA@AccDbT) 
|   `-- ... similar structure as dev
`-- lsa-db-data-pro - Data for the production database (LSA@AccDbP) 
    `-- ... similar structure as dev

The configuration data is stored in the src/main/resources folder of the maven project. As you can see in the structure each importer data resided in its own folder like elements, makrules etc.

The projects use a tool called Liquibase that keeps track which script were already applied on a database schema and which scripts are missing. It then only applies the missing scripts and updates data/configuration through repeatable (applied each time Liquibase runs) scripts.

Preparation

Configuring database passwords (maven authentication)

Since Liquibase needs access to the database and we configure everything through maven, we also manage the database passwords in maven. The IN Group maintains a Wiki page with a how-to configure authentication information in maven: https://www-acc.gsi.de/wiki/IN/Maven#Authentication

Please follow the guide to add an authentication section for the development and integration database servers to your $HOME/.m2/settings.xml:

  <server>
    <id>AccDbU-LSA</id>
    <username>LSA</username>
    <password> <REPLACE_WITH_ENCRYPTED_DB_PASSWORD> </password>
  </server>
  <server>
    <id>AccDbT-LSA</id>
    <username>LSA</username>
    <password> <REPLACE_WITH_ENCRYPTED_DB_PASSWORD> </password>
  </server>

The passwords are provided by the LSA team on request. Note that we will not provide a password for the production database, because in production the scripts will only be executed by the LSA team.

Checking out the projects in Eclipse

For maintaining the configuration data you only need to check out the lsa-db-data-* projects as maven projects from Git: https://git.acc.gsi.de/lsa?sort=alphabetically&q=lsa-db-data

- lsa-db-data-dev
- lsa-db-data-int 
- lsa-db-data-pro 

Making Changes

Changing the Configuration

To change the configuration, navigate in the resource folder of the respective data project to the appropriate CSV file that contains the information.

Usually the CSV files have a header that describe what is expected. For example, makerules_default.csv looks like this:

"PARTICLE_TRANSFER_NAME","SOURCE_PARAMETER_TYPE_NAME","DEPENDENT_PARAMETER_TYPE_NAME","MAKERULE_NAME"
"DEFAULT","I","I_POLYNOMIAL","DF2BPSMR"
"DEFAULT","FRF","FRF_POLYNOMIAL","DF2BPSMR"
...

Just open the appropriate CSV file with an editor and update the content. If a line starts with '#' it is treated as a comment.

Changes to the production database (LSA@AccDbP)

The configuration data in lsa-db-data-pro should only be changed during a release or for other reasons demanded by accelerator operations, like missing features or bug fixing. Any changes during operation must be communicated with the LSA team to ensure that they are still compatible with the LSA version currently used in production. Since this project will use the production release importer versions, the file format might be different from lsa-db-data-dev if the importers have been changed since the last production release.

Checking the Changes

To inquire through Liquibase which changes will be applied the maven targets are "clean install liquibase:status".

You can do this from the command line by navigating into the root of the respective projects and running:

mvn clean install liquibase:status

Or from Eclipse:

Right Click on the project
-> Run As
-> Maven Build ...
-> Enter in the 'Goals' field "clean install liquibase:status"
-> Click the 'Run' button

Executing the Changes

First, commit and push your changes to Git!

After that, executing Liquibase and applying your changes is in principle the same as checking for changes, just use different Maven targets:

mvn clean install liquibase:update

After running this command, the Maven output should contain the message "BUILD SUCCESS":

[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 9.036 s
[INFO] Finished at: 2017-08-09T17:10:43+02:00
[INFO] Final Memory: 47M/794M
[INFO] ------------------------------------------------------------------------

Expert Users

A section for database and importer developers will follow here later.