You are here: Foswiki>Service Web>DataBase (22 Aug 2017, SebastianMueller)Edit Attach

Liquibase import script:

This page will show the overview of the LSA-Liquibase-Importer-scripts which will help the developer to versioning the database. The importer has two parts. Part 1 is for the database-developer which run the different liquibase scripts on the different database (pro/next/dev). The part 2 is for the modler, which save the elements/makerules and transfer lines into a csv-file which the import use to update the database to the current csv-files.

Structure overview:

|-- lsa-db-scripts
|-- lsa-db-data-dev
|-- lsa-db-importer

Structure lsa-db-scripts:


|-- src  
    `-- main
        `-- liquibase       
            |-- current
            |   |-- c4o
            |   |-- lsa_common
            |   `-- lsa_gsi
            |-- future   
            |-- initial_setup 
            |   |-- c4o
            |   |-- lsa_common
            |   `-- lsa_gsi  
            |-- prototyping 
            |-- r009
            |   |-- c4o 
            |   |-- lsa_common 
            |   `-- lsa_gsi        
            |-- r009_1 
            |   |-- c4o 
            |   `-- lsa_gsi
            `-- repeatable
Current:

This is for testing. E.g. for the new release r010. And after testing the folder, move into the r010 folder, in this case.
Future:

Schema changes, which will apply in the future release cycle. Only relevant for GSI.
Initial_setup:

This is the files for the initial setup of the database. The first scripts check if the grants to the other database are validated. And the database has to be empty.
Release folder (r009, r009_1 [...])

Here are the schema changes for the current folder.
Repeatable:

in this folder are the repeatables. These changes will run every time you run liquibase, but with a separate script. See lsa-db-data-dev.
Master_logs:

In this file will store the xml-files in the current folder:

Here the master_log_dev:

<property name="flyway.placeholder.fesaUser" value="FESADB" />
<property name="flyway.placeholder.controlUser" value="CONTROLSDB" />
<property name="flyway.placeholder.context" value="COMMONS_CTX_LSA" />
<property name="flyway.placeholder.context_log" value="LOG_CTX_LSA" />

<include file="initial_setup/initial_setup_log.xml" />
<include file="r009/release_log.xml" />
<include file="r009_1/release_log.xml" />
<include file="current/current_log.xml" />

In the first will be declared the placeholder. I think this is only relevant for GSI.

On the second point the files will be included in descending way. That means the initial_setup_log.xml will execute on the first. But this xml-file can include more xml-files:

E.g. (initial_setup_log.xml)

<include file="initial_setup/c4o/c4o_log.xml" />
<include file="initial_setup/lsa_common/lsa_common_log.xml" />
<include file="initial_setup/lsa_gsi/lsa_gsi_log.xml" />

The execute order is: initial_setup/initial_setup_log.xml -> initial_setup/c4o/c4o_log.xml -> initial_setup/lsa_common/lsa_common_log.xml and so on...

Structure lsa-db-data-dev:

|-- src
    |-- main
        |-- liquibase 
        |   `-- db_specific_log.xml 
        `-- resources
            `-- de 
                `-- gsi
                    `-- lsa 
                        `-- db
                            `-- importer 
                                `-- spi
                                    |-- elements
                                    |   |-- elements_cryring.csv
                                    |   `-- elements_device_mapping_cryring.csv
                                    |-- makerules 
                                    |   |-- makerules_cryring.csv
                                    |   |-- makerules_default.csv
                                    |   |-- makerules_esr.csv
                                    |   |-- makerules_sis100.csv
                                    |   `-- makerules_sis18.csv
                                    |-- particletransferrelations
                                    |   `-- pt_relations.csv
                                    `-- transferlines
                                        |-- transferline_particle_transfer_mappings.csv
                                        `-- transferlines.csv

This structure depends on the lsa-db-scripts (see above). The csv-files will execute with the repetables structure from the lsa-db-scripts.

Structure lsa-db-importer:

|-- src
    |-- main
        |-- java
            `-- de
                `-- gsi
                    `-- lsa
                        `-- db
                            |-- importer
                            |   |-- AbstractDataImporter.java
                            |   |-- DataImporter.java
                            |   |-- ImporterException.java
                            |   `-- spi
                            |       |-- DefaultDataImporter.java
                            |       |-- DefaultLsaAwareDataImporter.java
                            |       |-- ElementsAndMappingImporterImpl.java
                            |       |-- MakeRuleImporterImpl.java
                            |       |-- ParticleTransferRelationImporterImpl.java
                            |       |-- singleDataSourceLsaApplicationContext.xml
                            |       `-- TransferLineImporterImpl.java
                            `-- liquibase
                                |-- ElementsAndMappingCustomTaskChange.java
                                |-- MakeRuleCustomTaskChange.java
                                |-- ParticleTransferRelationCustomTaskChange.java
                                |-- ResourceAccessorClassLoader.java
                                `-- !TransferLineCustomTaskChange.java 

For examples for the executing of the repeatables, see next paragraph
How does the importer works:

The importer has the following methods (Update, insert, initialize and delete). With this method the importer puts the csv-files into the database. After the liquibase run, the database has the same version like the csv-files.

Examples for Change-sets :

1. <changeSet id="2017-07-13_13-15_001" author="jfitzek" runAlways="true">
2.   <validCheckSum>any</validCheckSum>
3.     <comment>Updating Particle Transfer Relations.</comment>
4.     <customChange
5.       class="de.gsi.lsa.db.liquibase.ParticleTransferRelationCustomTaskChange">
6.       <param name="fileName">/de/gsi/lsa/db/importer/spi/particletransferrelations/pt_relations.csv</param>
7.     </customChange>
8. </changeSet>

In line number 1. the id of this changeSet is like YYYY-MM-DD_HH-mm_xxx where xxx is a number which is helpfully when you have a couple changes with on change set. At the end of this line the flag "runAlways" says liquibase that this change set will run after all migrations.

Line number 2. normally liquibase checks the checksum with the changeset and write it the databasechangelog table. And if the Changeset or the migrations-file has change liquibase will throw an error.

Line number 3. is for comments. This field will save liquibase into the table databasechangelog

Line 4 - 7 in this line looks liquibase for changes for a special change which liquibase execute on the database without parsing. In this case a java class (ParticleTransferRelationCustomTaskChange) will execute (Line 5) with parameters ( Line 6 - parameter name "fileName"). Liquibase can handle multiple param(eters) in one change set

<include file="initial_setup/c4o/c4o_log.xml" />

An example for including xml-files for liquibase
1.<changeSet id="2017-07-24_14-05_001" author="smueller">
2.    <comment>  Update Commons 1.15 to 1.16 </comment> 
3.      <sqlFile  splitStatements="false"  path="r009_1/c4o/2017-07-25-14-40_001__PB_com_period.sql"/> 
4.      <sqlFile  path="r009_1/lsa_gsi/2017-07-25_12-30_002__SC_SCHEMA_WIDGET_DROP_TABLES_AND_SEQUENCES.sql" />
5.      <sqlFile  splitStatements="true" endDelimiter="/"   path="r009_1/c4o/2017-07-25-14-40_001__SC_UPDATE_1_15_0_to_1_16.sql"/>       
6.  </changeSet>

In line number 3-5 three sql-file will execute in one change set. In the databasechangelog this change set will be setup as one insert.

The flag spliteStatements (default: true) is for the splitting. When it is true liquibase reads a file till ";" and after this it will execute the order. For a single pl/sql file you can say splitStatements=false .Liquibase takes the whole file and execute it. If in one file multiple pl/sql (with begin ... end;) then you can set a special end delimter (Line 5) with endDelimiter="/".

One special case: When in the pl/sql file a calculation like "40/5" is written, Liquibase will split it up and try to execute the command. For this case liquibase accepts regex like endDelimiter="\n/\s*\n|\n/\s*$"

Examples for maven:

<profile>
   <id>database-dev-new</id>
   <activation>
      <property>
         <name>lsa.database</name>
         <value>gsi-dev-new</value>
      </property>
   </activation>
   <build>
      <plugins>
         <plugin>
            <groupId>org.liquibase</groupId>
            <artifactId>liquibase-maven-plugin</artifactId>
            <version>${pluginversion.liquibase}</version>
            <configuration>
               <server>AccDbU-LSANEU</server>
               <url>jdbc:oracle:thin:@//dbla0ab.acc.gsi.de:1521/AccDbU.acc.gsi.de</url>
               <changeLogFile>master_log_new.xml</changeLogFile>
            </configuration>
         </plugin>
      </plugins>
   </build>
</profile>

In the example above, the maven profile "gsi-dev-new" database is shown. For this database the following command is needed.

mvn -Dlsa.database=gsi-dev-new install liquibase:migrate

Open issues:

Status:

Liquibase has two commands to show the status of the database between the migration which runs on the database and the upcoming changes.

Liquibase status command output is like:

2 change sets have not been applied to LSANEU@jdbc:oracle:thin:@//dbla0ab.acc.gsi.de:1521/AccDbU.acc.gsi.de
lsa-db-scripts::2017-07-21_12-40_002::rmueller
lsa-db-scripts::2017-08-15_09-56_001::rmueller

This output is nor very informative. The second command "dbDoc" generates a website which looks like:

pending.PNG

This website does not show the repeatables properly. In this case the repeatables are shown (NOT RUN YET) but when the script is run once the website shows no entry.

Relative-paths

In the change-log file you have setup the relative path to the files. Like:
<sqlFile  path="r009_1/lsa_gsi/2017-07-25_12-30_002__SC_SCHEMA_WIDGET_DROP_TABLES_AND_SEQUENCES.sql" />

When you copy the xml-files the paths must be adjusted.

-- SebastianMueller - 16 Aug 2017
Topic attachments
I Attachment Action Size Date Who Comment
pending.PNGPNG pending.PNG manage 134 K 21 Aug 2017 - 07:17 SebastianMueller liquibase pending
pending2.PNGPNG pending2.PNG manage 21 K 21 Aug 2017 - 09:05 SebastianMueller pending post
Topic revision: r4 - 22 Aug 2017, SebastianMueller
This site is powered by FoswikiCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki? Send feedback