Skip to main content
Version: Previous

Tables - examples

Simple table with autoincrement​

This very simple table contains one field (POSITION_ID) that uses autoIncrement to generate the next sequence number. This field is also the primary key for the table.

tables {
    table(name = "POSITION", id = 11002) {
        field("ID", INT).autoIncrement().primaryKey()
        field("INSTRUMENT_ID")
        field("QUANTITY", INT)
        field("NOTIONAL", DOUBLE)

    }

}

Simple table with sequence​

This very simple table contains one field (POSITION_ID) that uses sequence to generate the next sequence number. A sequence must always have a unique two-character id - in this case, PS. The POSITION_ID field is also the primary key for the table.

tables {
    table(name = "POSITION", id = 11002) {
        field("ID", INT).sequence("PS").primaryKey()
        field("INSTRUMENT_ID")
        field("QUANTITY", INT)
        field("NOTIONAL", DOUBLE)

    }

}

Two tables and an audit table​

Our example below shows an application's tables-dictionary.kts file containing two tables. The first contains trades and the second contains simple position information for each instrument (as seen in the first example).

Note that the first table has been declared with an audit table, so this creates two tables: one called TRADE and one called TRADE_AUDIT. The TRADE_AUDIT table automatically logs all changes to the TRADE table. The schema will actually have three tables:

  • TRADE
  • TRADE_AUDIT
  • POSITION

Audit tables use sequence to generate the sequence number for each entry in the table. In this example, the sequence has the id TR.

tables {
    table(name = "TRADE", id = 11000, audit = details(id = 11001, sequence = "TR", tsKey = true)) {
        field("TRADE_ID", LONG).autoIncrement().primaryKey()
        field("INSTRUMENT_ID").notNull()
        field("COUNTERPARTY_ID").notNull()
        field("QUANTITY", INT).notNull()
        field("SIDE", ENUM("BUY", "SELL")).notNull()
        field("PRICE").notNull()
        field("TRADE_DATETIME", DATETIME).nonUniqueIndex()
        field("ENTERED_BY")
        field("TRADE_STATUS", ENUM("OPEN", "CLOSED"))

    }

    table(name = "POSITION", id = 11002) {
        field("ID", INT).autoIncrement().primaryKey()
        field("INSTRUMENT_ID")
        field("QUANTITY", INT)
        field("NOTIONAL", DOUBLE)

    }

}