...

Currently, we are using Liquibase 23.03.5. Previously, we used Liquibase 1.9.5, and there is a mix of both versions in the db upgrade directories, currently. Note, that liquibase scripts are not always cross version compatible due to changes in the XML schema used by liquibase. There is a JIRA 

Standards/Conventions

...

The author attribute of the changeSet element should be the KFS version, for example

author="KFS61"

...

2. 

Standards/Conventions

• The author attribute of the changeSet element should be the KFS version, for example

  No Format
  author="KFS61"

• The id attribute of the changeSet element should reference the JIRA related to the liquibase script, for example

  No Format
  id="KFSMI-8710_MAXIMUM_ACCOUNT_RESPONSIBILITY_ID_TO_COA_MODULE"

• Liquibase scripts need to be valid for both Oracle and MySQL. The easiest way to do this is to write liquibase scripts for MySQL, and bypass converters. While the project team's preference is that contributors would submit liquibase scripts written for MySQL without converters, we are not currently enforcing this as a requirement. Note that in some cases a changeset needs to be database specific, when creating sequences for example, so that a changeset needs to be included for both Oracle and MySQL.

  Note
  Regarding datatype conversion for common datatypes: Liquibase will convert varchar to varchar2 for Oracle. Oracle will convert decimal to number. (Oracle will convert from ANSI datatypes to Oracle datatypes, see this document.) Even with a converter, it appears that datetime is converted to timestamp for Oracle.

• For KIM changes, IDs will eventually use a single sequence prefixed by "KFS" for all KIM data - permissions, roles everything, and everything will just be given a number such as: KFS{next number in sequence}, i.e. KFS10001, etc. In order to simplify the creation of liquibase scripts for contributors, contributors can put another value, such as the JIRA with a suffix indicating type type of KIM data for KIM ids, and then the KFS Tech Lead will change to use the common sequence once the contributing developer commits the liquibase script changes. (See also KIM Changes sample script below) For example:
  • KFSMI9598-PRM1 - Permission ID (PERM_ID)
  • KFSMI9598-PRM1ATT - Attribute Data ID (ATTR_DATA_ID) for attribute related to previously defined permission

  • KFSMI9598-RLPRM1 - Role Permission ID (ROLE_PERM_ID) relating previously defined permission to role

     

• Use liquibase tags rather than inline SQL wherever possible. 

  For example, this changeset:

  Code Block
  language html/xml
  <changeSet author="KFS51" id="KFSCNTRB-734"> <comment>KFSCNTRB-734</comment> <sql> ALTER TABLE FS_DOC_HEADER_T add SECURE_FIELD varchar(255) </sql> </changeSet>

  
  would be better as:

  Code Block
  language html/xml
  <changeSet author="KFS51" id="KFSCNTRB-734"> <comment>KFSCNTRB-734</comment> <addColumn tableName="FS_DOC_HEADER_T"> <column name="SECURE_FIELD" type="VARCHAR(255)" /> </addColumn> </changeSet>

...

<changeSet author="KFS501" id="KFSMI-9598_ADD_SUPERUSER_TAB_USE_PERMISIONS">
	<comment>Add the necessary KIM data for the Rice permission which is used to grant access to the superuser tab on documents.</comment>

	<insert tableName="KRIM_PERM_T">
		<column name="PERM_ID" value="KFSMI9598-PRM1" />
		<column name="OBJ_ID" value="KFSMI9598-PRM1" />
		<column name="VER_NBR" valueNumeric="1" />
		<column name="PERM_TMPL_ID" value="KR1000" />
		<column name="NMSPC_CD" value="KFS-SYS" />
		<column name="NM" value="Super User Approve Single Action Request KFS" />
		<column name="DESC_TXT" value="Allows users to super user approve single action requests on the super user tab for KFS Documents." />
		<column name="ACTV_IND" value="Y" />
	</insert>

	<insert tableName="KRIM_PERM_ATTR_DATA_T">
		<column name="ATTR_DATA_ID" value="KFSMI9598-PRM1ATT" />
		<column name="OBJ_ID" value="KFSMI9598-PRM1ATT" />
		<column name="VER_NBR" valueNumeric="1" />
		<column name="PERM_ID" value="KFSMI9598-PRM1" />
		<column name="KIM_TYP_ID" value="KR1000" />
		<column name="KIM_ATTR_DEFN_ID" value="13" />
		<column name="ATTR_VAL" value="KFS" />
	</insert>

	<insert tableName="KRIM_ROLE_PERM_T">
		<column name="ROLE_PERM_ID" value="KFSMI9598-RLPRM1" />
		<column name="OBJ_ID" value="KFSMI9598-RLPRM1" />
		<column name="VER_NBR" valueNumeric="1" />
		<column name="ROLE_ID" value="45" />
		<column name="PERM_ID" value="KFSMI9598-PRM1" />
		<column name="ACTV_IND" value="Y" />
	</insert>
		
	<insert tableName="KRIM_PERM_T">
		<column name="PERM_ID" value="KFSMI9598-PRM2" />
		<column name="OBJ_ID" value="KFSMI9598-PRM2" />
		<column name="VER_NBR" valueNumeric="1" />
		<column name="PERM_TMPL_ID" value="KR1001" />
		<column name="NMSPC_CD" value="KFS-SYS" />
		<column name="NM" value="Super User Approve Document KFS" />
		<column name="DESC_TXT" value="Allows users to super user approve documents on the super user tab for KFS Documents." />
		<column name="ACTV_IND" value="Y" />
	</insert>

	<insert tableName="KRIM_PERM_ATTR_DATA_T">
		<column name="ATTR_DATA_ID" value="KFSMI9598-PRM2ATT" />
		<column name="OBJ_ID" value="KFSMI9598-PRM2ATT" />
		<column name="VER_NBR" valueNumeric="1" />
		<column name="PERM_ID" value="KFSMI9598-PRM2" />
		<column name="KIM_TYP_ID" value="KR1000" />
		<column name="KIM_ATTR_DEFN_ID" value="13" />
		<column name="ATTR_VAL" value="KFS" />
	</insert>

	<insert tableName="KRIM_ROLE_PERM_T">
		<column name="ROLE_PERM_ID" value="KFSMI9598-RLPRM2" />
		<column name="OBJ_ID" value="KFSMI9598-RLPRM2" />
		<column name="VER_NBR" valueNumeric="1" />
		<column name="ROLE_ID" value="45" />
		<column name="PERM_ID" value="KFSMI9598-PRM2" />
		<column name="ACTV_IND" value="Y" />
	</insert>

	<insert tableName="KRIM_PERM_T">
		<column name="PERM_ID" value="KFSMI9598-PRM3" />
		<column name="OBJ_ID" value="KFSMI9598-PRM3" />
		<column name="VER_NBR" valueNumeric="1" />
		<column name="PERM_TMPL_ID" value="KR1002" />
		<column name="NMSPC_CD" value="KFS-SYS" />
		<column name="NM" value="Super User Disapprove Document KFS" />
		<column name="DESC_TXT" value="Allows users to super user disapprove documents on the super user tab for KFS Documents." />
		<column name="ACTV_IND" value="Y" />
	</insert>

	<insert tableName="KRIM_PERM_ATTR_DATA_T">
		<column name="ATTR_DATA_ID" value="KFSMI9598-PRM3ATT" />
		<column name="OBJ_ID" value="KFSMI9598-PRM3ATT" />
		<column name="VER_NBR" valueNumeric="1" />
		<column name="PERM_ID" value="KFSMI9598-PRM3" />
		<column name="KIM_TYP_ID" value="KR1000" />
		<column name="KIM_ATTR_DEFN_ID" value="13" />
		<column name="ATTR_VAL" value="KFS" />
	</insert>

	<insert tableName="KRIM_ROLE_PERM_T">
		<column name="ROLE_PERM_ID" value="KFSMI9598-RLPRM3" />
		<column name="OBJ_ID" value="KFSMI9598-RLPRM3" />
		<column name="VER_NBR" valueNumeric="1" />
		<column name="ROLE_ID" value="45" />
		<column name="PERM_ID" value="KFSMI9598-PRM3" />
		<column name="ACTV_IND" value="Y" />
	</insert>

</changeSet>

Running Liquibase Scripts

Run Changes Directly into Database

The easiest way to run liquibase scripts is to copy the liquibase-sample.properties file (from kfs/work/db/rice-data/) as liquibase.properties and fill in with the appropriate DB information. Then, run the following command, passing in the location of the file you want to execute as the "changeLogFile" parameter.

java -jar /{user home}/java/tools/liquibase/liquibase-2.0.5.jar --logLevel=finest --defaultsFile=/{user home}/java/tools/liquibase/liquibase.properties --changeLogFile=rice/kns_upgrade.xml update

This assumes, of course, that under your home directory, you have a java/tools/liquibase directory with both the liquibase-2.0.5.jar (which can be downloaded here: http://www.liquibase.org/download/) and with a liquibase.properties file set to point to your database.  A typical liquibase.properties file looks like this:

classpath=/java/drivers/mysql-connector-java-5.1.13.jar 
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/kfs
username=kfs
password=kfs

With those in place in the directories of your choice, it should be easy to run the liquibase command.

Alternatively, you can avoid downloading the liquibase jar yourself since liquibase-core is now declared as a maven dependency in the KFS pom. You could point to the liquibase jar in your local maven repository or in the target directory after running mvn package -DskipTests=true. The following examples show generally how you would reference the jar in this manner.

java -jar /{user home}/.m2/repository/org/liquibase/liquibase-core/3.3.2/liquibase-core-3.3.2.jar --logLevel=finest --defaultsFile=/{user home}/java/tools/liquibase/liquibase.properties --changeLogFile=rice/kns_upgrade.xml update

...

LiquiRelational 

LiquiRelational is a tool in the financials-datatools github repository that is a dependency for the main Financials application, and can be used to load all KFS and Rice data or just incremental updates. The tool can be run automatically on application startup or from the command line. This tool reads and executes liquibase xml files from the classpath.

Running LiquiRelational

To have LiquiRelational run automatically on application startup, set the updateDatabaseOnStartup property to true, and set the other properties to the values you want (see below).

To run LiquiRelational from the command line, the updateDatabaseStartup property is not required, but the other properties should be set to the values you want. Also, the classpath needs to include the financials-datatools classes as well as the liquibase xml to run (the easiest way to do this is to just include the classes needed to run KFS) as well as a jdbc driver. For example:

java -classpath /Users/bh79/IdeaProjects/financials/kfs-web/target/kfs-web/WEB-INF/lib/*:/Users/bh79/java/drivers/mysql-connector-java-5.1.25-bin.jar -Dadditional.kfs.config.locations=/Users/bh79/liquirelational-override-config.properties co.kuali.financials.datatools.liquirelational.LiquiRelational

Liquibase File Locations

Base structures and data are in the following locations in the financials project:

kfs-core/src/main/resources/org.kuali.kfs.core.db/phase1 - KFS Tables and Sequences

kfs-core/src/main/resources/org.kuali.kfs.core.db/phase2 - KFS Data

kfs-core/src/main/resources/org.kuali.kfs.core.db/phase3 - KFS Primary Keys

kfs-core/src/main/resources/org.kuali.kfs.core.db/phase4 - KFS Foreign Keys, Unique Constraints, Indexes and Views

kfs-core/src/main/resources/org.kuali.rice.db/phase1 - Rice Tables and Sequences 

kfs-core/src/main/resources/org.kuali.rice.db/phase2 - Rice Data

kfs-core/src/main/resources/org.kuali.rice.db/phase3 - Rice Primary Keys

kfs-core/src/main/resources/org.kuali.rice.db/phase4 - Rice Foreign Keys, Unique Constraints, Indexes and Views

Update liquibase files, if any, are located in a package corresponding to each module + /db/phase5/ (ex: kfs-ar/src/main/resources/org.kuali.kfs.module.ar/db/phase5/).

LiquiRelational Properties

The following properties control how LiquiRelational operates:

Since LiquiRelational needs a database to operate on, the following properties are also used to build a DataSource. Most of these properties are also described in Configuration Properties.

Run Changes to SQL Script

Another nice feature of Liquibase is that it can simply generate the SQL for you if you do not have a DBA who will run Liquibase scripts against your databases. The command below will run the script except that it will dump the SQL commands to the console. (Note that you still need a database against which to run, but it will not attempt to run any of the updates.)

java -jar /{user home}/javapath/tools/liquibase/liquibase-2.0.5.to/liquinbase}.jar --logLevel=finest --defaultsFile=/{user home}/java/tools/liquibase{path/to}/liquibase.properties --changeLogFile=rice/kns_upgrade{path/to}/liquibase.xml updateSQL 

LiquiBase Notes

...