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

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

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>

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

java -jar /{kfs home}/target/kfs/WEB-INF/lib/liquibase*.jar --logLevel=finest --defaultsFile=/{user home}/java/tools/liquibase/liquibase.properties --changeLogFile=rice/kns_upgrade.xml update

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}/java/tools/liquibase/liquibase-2.0.5.jar --logLevel=finest --defaultsFile=/{user home}/java/tools/liquibase/liquibase.properties --changeLogFile=rice/kns_upgrade.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.