===== Liquibase =====

Tool to track B changes

TO clean up the checksum after the change do
<code>
update databasechangelog set md5sum = null where filename = '2014_10_27.xml';
</code>

=== how it works===
Liquibase stores all metadata in 2 tables:
<code>
CREATE TABLE PUBLIC.DATABASECHANGELOGLOCK ...
CREATE TABLE PUBLIC.DATABASECHANGELOG ...
</code>


===Syntax of the call===

== H2 ==

  * There is the syntax of the liquibase call.
  * The Driver name: org.h2.Driver
  * THe location of the Driver is: c:\doj\materials\Liquibase\h2jar\h2-1.4.197.jar
<code>
liquibase --driver=org.h2.Driver --classpath="c:\doj\materials\Liquibase\h2jar\h2-1.4.197.jar"  --changeLogFile=../changelogs/extend.user.changelog.liquibase.xml --url="jdbc:h2:tcp://localhost:9092/mem:dbname" --username=mydbuser --password=mydbpass migrate
</code>

== Postgres ==
<code>
wget -O liquibase.tar.gz https://github.com/liquibase/liquibase/releases/download/v3.9.0/liquibase-3.9.0.tar.gz 
tar -xzvf liquibase.tar.gz



wget -O postgresql-jdbc4.jar https://jdbc.postgresql.org/download/postgresql-42.2.5.jre6.jar

chmod +x postgresql-jdbc4.jar

./liquibase --driver=org.postgresql.Driver --classpath="/tmp/liquibase/postgresql-jdbc4.jar"  --changeLogFile=dbchangelog.xml --url="jdbc:postgresql://localhost:5432/postgres" --username=postgres --password=postgres generateChangeLog
</code>



== Example changeset==
<code>
<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog 
        logicalFilePath="db-init-changelog"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd"  >

    <changeSet author="alf" id="add-nickname-to-users" logicalFilePath="add-nickname-to-users">
        <addColumn tableName="User">
            <column name="nickname" type="varchar(8)"/>
        </addColumn>
    </changeSet>

    <changeSet author="alf" id="sqlfile-usernames-add-nicknames" logicalFilePath="sqlfile-usernames-add-nicknames">
        <sqlFile dbms="h2, oracle"
                encoding="utf8"
                endDelimiter="\nGO"
                path="extend.user.changelog.liquibase.sql"
                relativeToChangelogFile="true"
                splitStatements="true"
                stripComments="true"/>
    </changeSet>

</databaseChangeLog>
</code>



== logicalFilePath ==

To avoid the repetition of already executed changeSets - explicitely set the **logicalFilePath** on the 
databaseChangeLog and every chagneSet. 


The table tracks each changeSet as a row, identified by a combination of the “id”, “author” and a “filename” column which stores the path to the changelog file.

Please note that the filename column stores the path to the changelog. This may be an absolute path or a relative path depending on how the changelog was passed to Liquibase. For best results, it should be a relative path.


<code>
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog logicalFilePath="does-not-matter" xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">

    <changeSet logicalFilePath="path-independent" author="authorId" id="1">
        ...
    </changeSet>

</databaseChangeLog>

https://www.liquibase.org/documentation/databasechangelog.html
</code>


== Debug ==
<code>
# create a tunnel to the STAGE ssh jump server and the postgres db on STAGE
sudo ssh -i /home/vagrant/.ssh/ssh.openssh.priv -NL 9000:privatedbdns.com:5432 myuser@11.222.33.111 -v

# connect the postgres database
psql -h localhost -p 9000 -U MyAdmin --dbname my_db

# PSQL password:
thepassword

# list schemas
select schema_name
from information_schema.schemata;

# list tables
\dt *.*

# show the liquibase history
SELECT * FROM databasechangelog;

# remove the liquibase history
DELETE FROM databasechangelog;



# MANUAL creation of the schema. DONT DO THAT
CREATE SCHEMA my_db;

# MANUAL REMOVAL of the schema. DONT DO THAT!!!!!!!!!!!!!!!!!!!
DROP SCHEMA my_db CASCADE;

</code>



=== With Spring Boot ===


== INit the DB at start ==

To init the table - you only need the core **org.liquibase:liquibase-core** (no plugin)

build.gradle
<sxh perl>
dependencies {
    // liquibase
    compile 'org.liquibase:liquibase-core:3.9.0'

    // get the to the classpath which is needed but not loaded with "ddl-auto=none" and liquibase enabled
    compile group: 'com.fasterxml.jackson.module', name: 'jackson-module-jaxb-annotations', version: '2.11.0'

}
</sxh>


application.properties
<sxh perl>
# https://docs.spring.io/spring-boot/docs/current/reference/html/appendix-application-properties.html
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.datasource.host=localhost
spring.datasource.port=5432
spring.datasource.database=postgres
spring.datasource.url=jdbc:postgresql://${spring.datasource.host}:${spring.datasource.port}/${spring.datasource.database}



# Liquibase without prefix
#spring.liquibase.enabled=false
spring.liquibase.enabled=true
spring.liquibase.change-log=classpath:/db/changelog/db.changelog-master.yaml
spring.liquibase.default-schema=public
spring.liquibase.user=postgres
spring.liquibase.password=postgres

# will be taken from spring.*  like spring.datasource.host
#spring.liquibase.host=localhost
#spring.liquibase.port=5432
#spring.liquibase.database=postgres
#spring.liquibase.driver=org.postgresql.Driver
</sxh>

resources/db/changelog/db.changelog-master.yaml
<code>
databaseChangeLog:
  - includeAll:
      path: db/changelog/generated/
</code>

resources/db/changelog/generated/dbchangelog-1589882779384.xml
<code>
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-3.9.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">
    <changeSet author="vagrant (generated)" id="1589882779384-1">
        <createTable tableName="tags">
            <column name="name" type="VARCHAR(255)">
                <constraints nullable="false" primaryKey="true" primaryKeyName="tags_pkey"/>
            </column>
        </createTable>
    </changeSet>
    
    ...
</code>