Skip to main content
Version: Current

Tables - advanced

warning

This page is only for users of GSF version 7.1 or earlier.

Click here for details of how to define these details in the current version.

Automatically generated sequences

There are two keywords that enable you to define a field that automatically generates a sequence number (such as you would need for identifying a new trade or a new order):

  • autoIncrement, which simply generates an integer value in sequence; by default, the first number generated will be 1
  • sequence, which generates either a UUID or a value in a Genesis format (see below), depending on the database technology you are using
info

For all new development, we recommended that you use autoIncrement fields rather than sequence.

There is no known efficiency between one keyword and the other. But autoIncrementis easier to define and generates a simple integer with no padding or leading zeroes; it keeps things simple.

Default format when using sequence

When you use the keyword sequence to generate sequence numbers, the value generated is actually a combination of sequence number and other parameters defined in your application's System definition. For example, a table with the field TRADE_ID defined as:

table (name = "TRADE", id = 2000) {
sequence(TRADE_ID, "TR")
QUANTITY
PRICE
SYMBOL
DIRECTION

primaryKey {
TRADE_ID
}
}

will generate TRADE_ID fields in the following format: SEQUENTIAL_VALUE (padded by paddingSize) + SEQUENCE + LOCATION + 1 (fixed)

Using the default settings (see details below), the first value generated for the field defined above will be: 000000000000001TRLO1

Using sequence with an SQL database (UUID)

If you are using an SQL database, sequence behaves differently by default; it generates a UUID.

To switch off UUID generation and use the default Genesis format for sequence:

  1. Add or modify the following keys in the file site-specific/genesis-system-definition.kts:
ItemDefault ValueDescription
Location'LO'
SqlSequencePaddingSize15The number of zeros used to pad the sequential number.
SqlEnableSequenceGenerationfalseMust be set to true.
  1. Run 'genesisInstall` to apply the settings.

  2. If you have any new or changed sequences, run remap to generate any new database sequences.

  3. If there are existing fields using sequences, run the server command CreateMissingSqlSequences.

  4. If you need to adjust the initial value of the sequences (for example if you are migrating data), use the server command SetSequence.

Subtables

Within the body of the table definition, you can use subtables to define one or more subtables. A subtable provides a unique point of view on the data schema, which goes further than a simple join relationship. It gives extra functionality to a main table.

For example, you might have an EXECUTION_VENUE table to provide details of different exchanges and trading venues. This table on its own probably cannot represent all the possible symbologies for an exchange. So, you could add a subtable called ALT_VENUE_CODE, in which the relationship is one-to-many from EXECUTION_VENUE_ID to ALT_VENUE_CODE.

ALT_VENUE_CODE inherits key fields from the EXECUTION_VENUE table, and it simply acts as a lookup table for EXECUTION_VENUE records.

The example below shows this.

After the fields and the primary key have been defined, you can see the subtable ALT_VENUE_CODE.

  • The EXECUTION_VENUE_ID field is used to generate the join operation. This field is inherited automatically.
  • Then the additional fields ALT_VENUE_CODE and ALT_VENUE_CODE_TYPE are defined.
  • Then the key for the subtable is defined.
    table(name = "EXECUTION_VENUE", id = 5043) {
COUNTRY_CODE
OPERATING_MIC
DESCRIPTION
EXECUTION_VENUE_ID

primaryKey(name = "EXECUTION_VENUE_BY_EXECUTION_VENUE_ID", id = 1){
EXECUTION_VENUE_ID
}
subTables {
fields(EXECUTION_VENUE_ID)
.joiningNewTable(name = "ALT_VENUE_CODE", id = 5044) {
ALT_VENUE_CODE
ALT_VENUE_CODE_TYPE

primaryKey(name = "ALT_VENUE_CODE_BY_EXECUTION_VENUE_ID_ALT_VENUE_CODE_TYPE", id = 1) {
EXECUTION_VENUE_ID
ALT_VENUE_CODE_TYPE
}
}
}
}

Some tables provided by the platform have subtables. The example below shows the GENESIS_PROCESS monitoring table, which includes a subtable called GENESIS_PROCESS_MONITOR.

table(name = "GENESIS_PROCESS", id = 12) {
PROCESS_NAME
PROCESS_STATUS
PROCESS_STATUS_MESSAGE
PROCESS_STATE_TEXT
PROCESS_RESOURCES
PROCESS_HOSTNAME
PROCESS_CPU_USAGE
PROCESS_MEM_USAGE
PROCESS_SECURE
PROCESS_PORT
LOG_LEVEL
DATADUMP
START_TIME
RESOURCE_TYPES
primaryKey {
PROCESS_NAME
PROCESS_HOSTNAME
}
subTables {
fields(PROCESS_HOSTNAME, PROCESS_NAME)
.joiningNewTable(name = "GENESIS_PROCESS_MONITOR", id = 20) {
MONITOR_NAME
MONITOR_MESSAGE
MONITOR_STATE

primaryKey(name = "GENESIS_PROCESS_MONITOR_BY_HOSTNAME", id = 1) {
PROCESS_HOSTNAME
PROCESS_NAME
MONITOR_NAME
}
}
}
}

Audit tables

If you want to be able to track the changes made to a table, you need an audit table. This is useful, for example, if you want to:

  • keep track of the changes to trades, with times and modifications, to provide an accurate record of events
  • be able to roll back to a specific time following an outage

Audit tables don't exist in isolation. They exist to audit activity on a source table. To create an audit table, you simply need to include the parameter audit = details() when you define the source table itself. The syntax is:

table (name = <TABLE_NAME>, id = <TABLE_ID>, audit = details(id = <TABLE_NAME_AUDIT_ID>, sequence = <TABLE_SEQUENCE>, tskey = <TRUE_OR_FALSE>))
...

The following details can be added when you use the audit = details()parameter:

Parameter nameTypeDescription
idIntegerUnique id to identify the audit table
sequenceSTRINGUnique name to identify its sequence
tskeybooleanSet a timestamp index

The audit table that is created has the same name as the source table, plus the suffix _AUDIT. Here is an example table definition, which creates a table called EUR_TRADES and an audit table called EUR_TRADES_AUDIT:

table (name = "EUR_TRADE", id = 2000, audit = details(id = 2100, sequence = "EA")) {
sequence(TRADE_ID, "TR")
COUNTERPARTY_ID
INSTRUMENT_ID not null
QUANTITY
PRICE not null
TRADE_DATE
TRADE_STATUS

primaryKey {
TRADE_ID
}
}

Whenever an eventHandler interacts with the table EUR_TRADES, a record is automatically inserted to the audit table EUR_TRADES_AUDITto log the event.

So, to be clear:

  • An audit table monitors and logs details of changes on a specific table.
  • If the table and the eventHandlers that interact with it are set up correctly, then the auditing is performed automatically.
  • Every change to the table is automatically logged and timestamped on the audit table.

Structure of audit tables

When you create an audit table, it has all the same fields as the source table, plus the following fields:

Field nameData TypeDescription
AUDIT_EVENT_DATETIMEDATETIMEAutogenerated date and time of the event
AUDIT_EVENT_TEXTSTRINGOptional “REASON” value sent as part of the event message
AUDIT_EVENT_TYPESTRINGEvent that wrote on the source table
AUDIT_EVENT_USERSTRINGUser on the event message

ACID

Every eventHandler that interacts with the audited table must be transactional. This ensures ACID compliance.

Here is a simple example of a transactional eventHandler:


eventHandler<Company>(name = "COMPANY_INSERT", transactional = true) {
onValidate {
ack()
}
onCommit { event ->
val company = event.details
entityDb.insert(company)
ack(listOf(mapOf("VALUE" to "SUCCESS!")))
}
}
warning

Make sure your database supports transactions.