KFS Data Structure Naming Standards

Maintaining consistency with the prior work in the KFS database is important so that relationships of data elements is evident. It also helps with development effort as there will be fewer typos in the mapping layer if the column names are predictable.

Note About Database Independence

When designing database objects, please take into account the differences between database implementations. We can not make the application dependent upon a particular database's implementation. As such, proprietary database features, or features with proprietary implementations, such as stored procedures and triggers may not be used by KFS. Any actions which could be performed by proprietary database functionality should be performed within the KFS application code instead.

Sequences are one exception to this rule. They are such a necessary component of the application that we allow them even though the concept is (among our currently supported database) only an Oracle one. However, we have a viable alternative for MySQL which simulates the functionality and the Rice infrastructure nicely abstracts that implementation detail away from the code.

Database Object Names

In general, all database objects will start with the identifier of the module shown in the Database Object Module Prefixes section below.

Tables	(module prefix) + name + "_T"
Temporary Tables	(module prefix) + name + "_MT"
Document Tables	(module prefix) + name + "_DOC_T"
Sequences	(module prefix) + name + "_ID_SEQ"
Views	(module prefix) + name + "_V"
Primary Keys	table name + "P1"
Unique Index on OBJ_ID Column	table name + "C0"
Foreign Keys	table name + "R#" where "#" is an incremental number
Non-Unique Indexes	table name + "I#" where "#" is an incremental number
Unique Indexes / Check Constraints	table name + "C#" where "#" is an incremental number

Table Naming Notes

Table names should be singular. The table name describes what is contained in a row of a table. (No, KFS is not consistent on this, but it's a general recommendation which is followed by most tables.)

Database Object Module Prefixes

Module Package	Prefix
org.kuali.kfs.sys	FS_ (or SH_ - only used by legacy tables)
org.kuali.kfs.coa	CA_
org.kuali.kfs.fp	FP_
org.kuali.kfs.gl	GL_
org.kuali.kfs.vnd	PUR_VNDR_
org.kuali.kfs.sec	SEC_
org.kuali.kfs.pdp	PDP_
org.kuali.kfs.module.ar	AR_
org.kuali.kfs.module.bc	LD_BCN_
org.kuali.kfs.module.cab	CB_
org.kuali.kfs.module.cam	CM_
org.kuali.kfs.module.cg	CG_
org.kuali.kfs.module.ec	LD_A21_
org.kuali.kfs.module.endow	END_
org.kuali.kfs.module.external.kc	KC_ (earlier tables used the CA_ prefix)
org.kuali.kfs.module.ld	LD_
org.kuali.kfs.module.purap	Multiple prefixes are used based on the sub-portion of this module: General: PUR_ Purchase Order: PUR_PO_ Receiving: PUR_RCVNG_ Purchase Requisition: PUR_REQS_ Payable Documents: AP_
org.kuali.kfs.module.tem	TEM_

Column Naming

When naming columns, is it standard to end the column name with the type of data which the field holds. This makes it more clear to future developers and implementers what a column is supposed to hold. (In many cases the demo database is empty for tables, so there is no reference data upon which to make a determination, so the column name/type and the code are the only available means.)

Also, please avoid using part of the name to identify the object which it is for. (Consistency overrides this - please see below.) We know that by which table it is in. (E.g., ACCT_STATE_CD - We know it's for the account because it's in CA_ACCOUNT_T.)

Because it is the normal case that column references are prefixed by their tables, it is also acceptable (as you can see in some of the newer Rice tables) to simply use the suffix where no other label applies. (E.g., NM, DESC). However, if the column will be present in some other table, it should contain its full name so that the column names can remain the same between tables.

General Rule

Name it like other columns in the existing database. If you can, find more than one example. The KFS project team tries to monitor all changes, but some inconsistencies have slipped through over the years.

Standard Suffixes

Type of Field	Suffix
Alphanumeric code used to represent an object to the user.	_CD
Identifiers for which the term number is most often used. (E.g., Account Number)	_NBR
Unique identifier. Often one which is not seen/used by end-users. Generally, these values are pulled from database sequences.	_ID
Date	_DT
Names	_NM
Descriptions	_DESC
Amount, usually dollars.	_AMT
Other strings - that are not identified as names or descriptions	_TXT
Quantity, mostly used in purchasing, accounts receivable.	_QTY
Rate - used as a multiplier on other data.	_RATE
Percent fields. If used, data in this column should (normally) be a number between 0 and 100.	_PCT
boolean Y/N Indicator	_IND
Principal IDs	_PRNCPL_ID

Standard Column Names / Definitions

Field Description	Column Name	Column Type	Other Information
Active Indicator	ACTV_IND	VARCHAR2(1)	Default: 'Y'
Amount fields	(xxxx)_AMT	NUMBER(19,2)
OJB Locking "version number"	VER_NBR	NUMBER(8)	Default: 1
Object Unique Identifier	OBJ_ID	VARCHAR2(36)	No Default
Name fields	(xxxx)_NM	VARCHAR2(40)
KIM Identifiers (principal, role, group)		VARCHAR2(40)

Database Constraints

Primary Keys

There are a couple types of keys, and which you use depends on your database design philosophy.

Synthetic Keys - generated by the system and generally never shown to the user.
Functional Keys - entered by and known to the user.

They both have their uses and complexities when managing them at the user interface and ORM-tool level. KFS uses both. And, the KNS maintenance document framework, really expects keys to be user enterable when linking referenced objects. It's possible to use synthetic keys in these cases, but you sacrifice either development simplicity or user experience.

Order of Fields

When defining primary keys, the fields should be in order of more general to most specific. Additionally, if parts of your key are also fields from a "parent" objects primary key, those should come before the key fields which are specific to the current object. Additionally, they must be in the same order as the parent table. The tools (and in some cases the database) have problems if key fields are in a different order between related tables.

Not Null Constraints

In general, do not use not null constraints. They make changing required data fields later much more difficult if you have to coordinate a database change with a coding change. The application should be enforcing these types of requirements, not the database. (Since, if we want to report an error to the user, we need the validation rules to check this. On maintenance documents, the database is not used until final approval, so no error could be raised based on a not null constraint.)

Primary key fields do not need not null constraints. They are implied on Oracle and adding your own causes Oracle to check each key field twice - once for the PK and once for the not-null constraint.

The only columns we add not null constraints to as a standard are the OBJ_ID and VER_NBR columns.

Foreign Key Constraints

These should be added when possible to help enforce database integrity.

The exception to this is for 1:1 relationships. In many cases, OJB (our database mapping tool), assumes the relationship applies in the wrong direction. It then attempts to save the child object before the parent, resulting in a foreign key violation at the database level. In all other cases, the default behavior properly saves the parent before the children.

Also, the order of the fields in the referencing table must match the PK fields in the referenced table. MySQL is sometimes picky about this and reports RI problems when it should not.

Other Conventions

Do not use functions in column default values (such as Oracle SYS_GUID()). Not all databases (E.g., MySQL) allow functions in their default definitions.
The OBJ_ID and VER_NBR should be defined (in that order) just after all the primary key fields.
Do not name key columns exclusively with "generic" terms like "category", "principal", or "type". This becomes confusing when reviewing the database as you don't know what is contained in those fields. (What type of category is it? What is the functional role of that principal?)
Do not name tables with "generic" terms either. (E.g., AR_CATEGORIES_T does not tell you what it contains categories for, only that they belong to the AR module.)
Objects which will be edited on maintenance documents should have an "active" property (implement the MutableInactivable interface) and an ACTV_IND column. This allows referential integrity to be retained since no-longer needed reference objects can be inactivated to prevent further use without changing all tables referencing that object.

Liquibase Overview and Sample Scripts

LiquiBase Overview

With LiquiBase, developers store database changes in XML-based files on their local development machines and apply them to their local databases. As those changes get committed to the source control system and are distributed to other developers, changes are applied to those local developer databases, to the integration databases, staging databases, and even to live production databases. Changes may be applied through several methods, either via an Ant or Maven task, a command line program, or automatically during application or application server startup.

For more info on liquibase changesetLogs and changesets, see http://www.liquibase.org/manual/overview

Liquibase Version

Currently, we are using Liquibase 3.3.2.

Standards/Conventions

The author attribute of the changeSet element should be the KFS version, for example
```
author="KFS61"
```
The id attribute of the changeSet element should reference the JIRA related to the liquibase script, for example
```
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.

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:

<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:

<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>

OBJ_ID Columns

When inserting values into OBJ_ID column(s) do not use database functions (ex: SYS_GUID() (Oracle) and UUID() (MySQL)), used hard-coded values instead. These values could be made up of the JIRA number for the JIRA related to the changeSet with a suffix related to the type of object as necessary.

Correct OBJ_ID syntax

<column name="OBJ_ID" value="KFSMI9598-PRM1"/>

Data Type Conversions

Liquibase does a much better job of converting MySQL data types to Oracle data types: VARCHAR is converted to VARCHCAR2, and so on, automatically–without the need for modifySql commands. Because of this, the foundation team prefers that Liquibase scripts be written using MySQL data column types.

However, there are still a couple of cases where modifySql is still needed, listed here.

DATETIME

Liquibase converts the MySQL DATETIME type to Oracle TIMESTAMP. Oracle TIMESTAMP differs from Oracle DATE by holding fractions of seconds; in many cases, that much precision is unnecessary, so modifySQL will be needed to convert the field to an Oracle DATE:

<modifySql>
   <replace replace="TIMESTAMP" with="DATE"/>
</modifySql>

Note: when modifySQL is run, Liquibase has already converted MySQL's DATETIME into Oracle TIMESTAMP, so TIMESTAMP is what needs to be in the replace clause to match against.

Contexts

Liquibase has the concept of a context. When running liquibase, a context can be specified and only changes with this context will be loaded. All liquibase change files should have the appropriate context on them. All liquibase change sets should have the appropriate context specified. Multiple contexts can be specified by separating them with commas. Here is an example:

<changeSet author="KFS51" id="KFSCNTRB-734" context="bootstrap,demo,unit">
	<comment>KFSCNTRB-734</comment>
	<addColumn tableName="FS_DOC_HEADER_T">
		<column name="SECURE_FIELD" type="VARCHAR(255)" />
	</addColumn>
</changeSet>

This is a list of the contexts defined for financials.

Context	Purpose
bootstrap	This context will load the minimum data to start up and run the financials project
demo	This context will load demo data for financials.
unit	This context will setup a database with demo data from kfs and rice in the same database. It is used for unit tests.
kfskc_bootstrap	This context (when used along with bootstrap) will load the minimum data to start up financials with integration with Kuali Coeus.
kfskc_demo	This context (when used along with demo) will load the demo data for financials setup for integration with Kuali Coeus.

DBMS

The dbms setting is used to specify that a changeset is for a specific database. An example of where this is used is for sequences. A sequence is a feature specific to Oracle. Financials will require a table in place of the sequence when running against MySql. The following is an example that uses the dbms attribute.

<changeSet author="kfs" id="CA_A21_ICR_ACCT_SEQ" dbms="mysql" context="bootstrap,demo,unit">
  <createTable tableName="CA_A21_ICR_ACCT_SEQ">
    <column autoIncrement="true" name="id" type="BIGINT">
      <constraints primaryKey="true"/>
    </column>
  </createTable>
  <insert tableName="CA_A21_ICR_ACCT_SEQ">
    <column name="id" value="10020"/>
  </insert>
</changeSet>
<changeSet author="kfs" id="CA_A21_ICR_ACCT_SEQ" dbms="oracle" context="bootstrap,demo,unit">
  <createSequence sequenceName="CA_A21_ICR_ACCT_SEQ" startValue="10020"/>
</changeSet>

Sample Liquibase Scripts

Create Table

	<changeSet author="KFS50" id="KFSMI-6921_CA_ACCT_AUTODEF_ICR_T">
		<comment>KFSMI-6921 Create Account and A21SubAccount ICR collection tables</comment>
		<createTable tableName="CA_ACCT_AUTODEF_ICR_T">
			<column name="CA_ICR_ACCT_GNRTD_ID" type="DECIMAL(10,0)">
				<constraints primaryKey="true" primaryKeyName="CA_ACCT_AUTODEF_ICR_TP1" />
			</column>
			<column name="OBJ_ID" type="VARCHAR(36)">
				<constraints nullable="false" unique="true" uniqueConstraintName="CA_ACCT_AUTODEF_ICR_TC0" />
			</column>
			<column name="VER_NBR" type="DECIMAL(8,0)" defaultValueNumeric="1">
				<constraints nullable="false" />
			</column>
			<column name="ACCT_DFLT_ID" type="DECIMAL(10,0)" />
			<column name="ICR_FIN_COA_CD" type="VARCHAR(2)" />
			<column name="ICR_FIN_ACCT_NBR" type="VARCHAR(7)" />
			<column name="ACLN_PCT" type="DECIMAL(35,20)" />
			<column name="ACTV_IND" type="VARCHAR(1)" defaultValue="Y" />
		</createTable>
	</changeSet>

Create View

	<changeSet author="KFS50" id="KFSMI-6745_pur_po_itm_tot_amt_v">
		<createView viewName="PUR_PO_ITM_TOT_AMT_V" replaceIfExists="true">
		(select fdoc_nbr, sum(
                     CASE
                     WHEN ITM_ACTV_IND = 'Y' AND ITM_ORD_QTY IS NULL THEN ITM_UNIT_PRC
                     WHEN ITM_ACTV_IND = 'Y' AND ITM_ORD_QTY IS NOT NULL THEN ITM_ORD_QTY * ITM_UNIT_PRC
                     WHEN ITM_ACTV_IND IS NULL AND ITM_ORD_QTY IS NULL THEN ITM_UNIT_PRC
                     WHEN ITM_ACTV_IND IS NULL AND ITM_ORD_QTY IS NOT NULL THEN ITM_ORD_QTY * ITM_UNIT_PRC
                     WHEN ITM_ACTV_IND ='N' THEN 0
                     ELSE 0
                       END) as TOTAL_AMOUNT
       from PUR_PO_ITM_T group by fdoc_nbr)
	</createView>

Add Column

<addColumn tableName="CA_ACCOUNT_T">
	<column name="LBR_BEN_RT_CAT_CD" type="VARCHAR(2)" defaultValue="--" />
</addColumn>

Drop Column

<dropColumn tableName="FS_TAX_REGION_TYPE_T" columnName="ACTV_IND"/>

Alter Column Type

<modifyDataType tableName="PUR_PO_ACCT_T" columnName="ACLN_PCT" newDataType="NUMBER(35,20)" />

Create Sequence

	<changeSet author="KFS50" id="KFSMI-6921_CA_ACCT_AUTODEF_ICR_SEQ_oracle" dbms="oracle">
		<createSequence sequenceName="CA_ACCT_AUTODEF_ICR_SEQ"
			startValue="10000" />
	</changeSet>

	<changeSet author="KFS50" id="KFSMI-6921_CA_ACCT_AUTODEF_ICR_SEQ_mysql" dbms="mysql">
		<createTable tableName="CA_ACCT_AUTODEF_ICR_SEQ">
			<column name="ID" type="BIGINT(19)" autoIncrement="true">
				<constraints nullable="false" primaryKey="true" />
			</column>
		</createTable>
	  	<modifySql>
	  		<append value=" ENGINE = MyISAM AUTO_INCREMENT = 10000 "/>
	  	</modifySql>
	</changeSet>

Insert Record (parameter in this case)

	<changeSet author="KFS50" id="KFSCNTRB-771_PARM_PURAP_REQUIRED_INDICATORS">
		<insert tableName="KRCR_PARM_T">
			<column name="NMSPC_CD" value="KFS-PURAP" />
			<column name="CMPNT_CD" value="Document" />
			<column name="PARM_NM" value="PAYMENT_REQUEST_POSITIVE_APPROVAL_IND" />
			<column name="VAL" value="Y" />
			<column name="OBJ_ID" value="KFSCNTRB771-1" />
			<column name="VER_NBR" valueNumeric="1" />
			<column name="PARM_TYP_CD" value="CONFG" />
			<column name="PARM_DESC_TXT" value="Display the functionality on a Requisition and Purchase Order document to trigger Positive Approval indicator. If this parameter is set to &quot;N&quot;, the indicator will not be displayed on any document, and any functionality connected to the indicator will be turned off." />
			<column name="EVAL_OPRTR_CD" value="A" />
			<column name="APPL_ID" value="KFS" />
		</insert>
	</changeSet>

Update Record (parameter in this case)

	<changeSet author="KFS50" id="KFSMI-8710_MAXIMUM_ACCOUNT_RESPONSIBILITY_ID_TO_COA_MODULE">	
		<comment>KFSMI-8710 MAXIMUM_ACCOUNT_RESPONSIBILITY_ID parameter should be part of KFS-COA instead of KFS-CG</comment>
		<update tableName="KRCR_PARM_T">
			<column name="NMSPC_CD" value="KFS-COA" />
			<where>NMSPC_CD='KFS-CG' AND CMPNT_CD='All' AND PARM_NM='MAXIMUM_ACCOUNT_RESPONSIBILITY_ID' AND APPL_ID = 'KFS'</where>
		</update>
	</changeSet>

The KIM IDs below would be changed to use a single sequence prefixed by "KFS", ex: KFS10001, by the KFS Product Engineer after the contributing developer committed the liquibase script changes.

KIM Changes

<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>

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/phaseX/ (ex: kfs-ar/src/main/resources/org.kuali.kfs.module.ar/db/phaseX/).

LiquiRelational Properties

The following properties control how LiquiRelational operates:

Property	Default	Description
updateDatabaseOnStartup	false	Determines if LiquiRelational will be run on application startup (true to run, false to not).
updateDatabaseFullRebuild	false	Determines if LiquiRelational will process all liquibase files (true), or just updates (i.e. phase5) (false).
updateDatabaseContext	bootstrap	Determines which context LiquiRelational will pass to liquibase to process (typically bootstrap for clean bootstrap database or demo to include demo data).
updateDatabasePackages	org/kuali/kfs/core,org/kuali/kfs/module/ar ,org/kuali/kfs/module/bc ,org/kuali/kfs/module/cam ,org/kuali/kfs/module/cg ,org/kuali/kfs/module/ec ,org/kuali/kfs/module/kc ,org/kuali/kfs/module/ld ,org/kuali/kfs/module/purap,org/kuali/kfs/module/tem	Determines which packages will be processed for Financials liquibase files. By default, all modules are included.
updateDatabasePackagesRice	org/kuali/rice	Determines which packages will be processed for Rice liquibase files. By default, all is included.

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.

Property	Default	Notes
datasource.driver	com.mysql.jdbc.Driver
kfs.datasource.driver.name	${datasource.driver}
kfs.datasource.validating.query	select 1 from dual
kfs.datasource.pool.maxActive	50
kfs.datasource.pool.minIdle	5
kfs.datasource.pool.initialSize	5
kfs.datasource.pool.accessToUnderlyingConnectionAllowed	true
kfs.datasource.ddl.username	kfs	Separate from the normal kfs.datasource.username property in case that user doesn't have enough privileges to execute DDL.
kfs.datasource.ddl.password	${kfs.datasource.username}	Separate from the normal kfs.datasource.password property in case that user doesn't have enough privileges to execute DDL.
kfs.datasource.ddl.url	jdbc:mysql://localhost/${kfs.datasource.username}	Separate from the normal kfs.datasource.url property in case that user doesn't have enough privileges to execute DDL.
rice.datasource.driver.name	${datasource.driver}
rice.datasource.validating.query	select 1 from dual
rice.datasource.pool.maxActive	50
rice.datasource.pool.minIdle	5
rice.datasource.pool.initialSize	5
rice.datasource.pool.accessToUnderlyingConnectionAllowed	true
rice.datasource.ddl.url	${kfs.datasource.ddl.url}	Separate from the normal rice.datasource.username property in case that user doesn't have enough privileges to execute DDL.
rice.datasource.ddl.username	${kfs.datasource.username}	Separate from the normal rice.datasource.password property in case that user doesn't have enough privileges to execute DDL.
rice.datasource.ddl.password	${kfs.datasource.password}	Separate from the normal rice.datasource.url property in case that user doesn't have enough privileges to execute DDL.

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 {/path/to/liquinbase}.jar --logLevel=finest --defaultsFile={path/to}/liquibase.properties --changeLogFile={path/to}/liquibase.xml updateSQL