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.

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

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

Standard Suffixes

Standard Column Names / Definitions

Database Constraints

Primary Keys

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

1. Synthetic Keys - generated by the system and generally never shown to the user.
2. 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.

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

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

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

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

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

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

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

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

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

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

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

LiquiBase Notes

• The author name is set to the version of KFS in which it will be released. The ID follows the following syntax id="<JIRA>_<object name>". NOTE: The ID an be a maximum length of 63 characters.
• Scripts should be database agnostic. If written with Oracle datatypes, include <modifySql> section to convert to MySQL and vice versa.