Skip to end of metadata
Go to start of metadata

Defining a database table

Business object instances are typically java object representations of rows of a database table.

The addition of following columns to each database table is strongly suggested:

  • Object ID
  • Version number

Object ID

The Object ID is used as a globally unique identifier of each row across all database tables (see caveat below). That is, every row in every table should have a different object value. It is typically defined as a VARCHAR field of 36 characters, and should be named "OBJ_ID". A unique constraint should be applied to the object ID column, but must NOT be part of the primary key.

The object ID value is automatically stored by the framework and/or the database layer.

About the object ID

Icon

The value of the object ID is a Globally Unique Identifier (GUID). It is extremely likely that each row will have a unique value, and for practical purposes, we assume that each row will have a unique object ID.

Version number

KFS/Rice uses optimistic locking to provide concurrency control. Optimistic locking requires the use of a version number field, named "VER_NBR". On Oracle, the field is defined as a NUMBER(8,0). On MySQL, the field is defined as a DECIMAL(8). This column should NOT be part of the primary key.

About optimistic locking

Icon

Optimistic locking helps to prevent updates to stale data and consists of two steps:

  1. Retrieval of a row from a database, including the value of the version number column
  2. Updating/deleting a row from the database with the same primary key and version number criteria. If updating the table, the version number will be incremented by one.

The following series of steps demonstrates how optimistic locking works:

  1. User A retrieves the row for chart code "BL". Assume that the row has version number of 3.
  2. User A performs a update of the "BL" record. The SQL query that updates the record would read something like "UPDATE CA_CHART_T SET <some updates>, VER_NBR = 4 WHERE FIN_COA_CD = "BL" and VER_NBR = 3. (The "4" refers to the incremented version number.)
  3. User B retrieves the row for chart code "BL". The version number is now 4.
  4. User B performs a update of the "BL" record. The SQL query that updates the record would read something like "UPDATE CA_CHART_T SET <some updates>, VER_NBR = 5 WHERE FIN_COA_CD = "BL" and VER_NBR = 4. (The "5" refers to the incremented version number.)

The following series of steps demonstrates how optimistic locking prevents concurrency problems.

  1. User A retrieves the row for chart code "BL". Assume that the row has version number of 3.
  2. User B retrieves the row for chart code "BL". Like user A, the version number is 3.
  3. User A performs a update of the "BL" record. The SQL query that updates the record would read something like "UPDATE CA_CHART_T SET <some updates>, VER_NBR = 4 WHERE FIN_COA_CD = "BL" and VER_NBR = 3. (The "4" refers to the incremented version number.)
  4. User B performs a update of the "BL" record. The SQL query that updates the record would read something like what User A executed above (notice the version numbers). However, the previous step already updated the version number to 4 from 3, so this update does nothing (i.e. update row count = 0) because it was trying to update the BL chart with a version number of 3. The system detects the 0 update row count, and throws an OptimisticLockingException. This exception indicates that the system tried to update stale data.

Example Account table definition

Below is the definition of the simplified account table.

  • No labels