Database tables for QuickFIX/J

Question

QuickFIX/J includes SQL scripts to create four database tables:

• sessions
• messages
• messages_log
• event_log

I cannot find any documentation that describes the purpose of each of these tables.

What are they for, when are they written to, do any of them grow indefinitely, etc...

Answer 1

Some tables are used for the store, others are used for logging (the *_log tables.) A store is required for QuickFIX/J to operate (it tracks session state and supports resends of messages) whereas the log is optional.

---

sessions

This table tracks active FIX sessions. Sessions have a composite key of the eight values shown in the primary key declaration below.

creation_time is used to determine which session this applies to.

*_seqnum columns track the current sequence numbers for the session, and are used for reliability along with resend requests.

create table sessions (
  beginstring  char(8) not null,
  sendercompid varchar(64) not null,
  sendersubid  varchar(64) not null,
  senderlocid  varchar(64) not null,
  targetcompid varchar(64) not null,
  targetsubid  varchar(64) not null,
  targetlocid  varchar(64) not null,
  session_qualifier varchar(64) not null,
  creation_time timestamp not null,
  incoming_seqnum integer not null,
  outgoing_seqnum integer not null,
  primary key (beginstring, sendercompid, sendersubid, senderlocid,
               targetcompid, targetsubid, targetlocid, session_qualifier)
);

---

messages

This table provides a persistent store of the FIX messages sent during an active session. If the party being communicated with requires messages to be resent, QuickFIX/J will use this table to determine the content of the messages.

At the start of each session, messages for the session_qualifier are removed. Thus this table will not grow indefinitely and the upper bound of its size depends upon how many messages may be sent during a session.

create table messages (
  beginstring char(8) not null,
  sendercompid varchar(64) not null,
  sendersubid varchar(64) not null,
  senderlocid varchar(64) not null,
  targetcompid varchar(64) not null,
  targetsubid varchar(64) not null,
  targetlocid varchar(64) not null,
  session_qualifier varchar(64) not null,
  msgseqnum integer not null,
  message text not null,
  primary key (beginstring, sendercompid, sendersubid, senderlocid,
               targetcompid, targetsubid, targetlocid, session_qualifier,
               msgseqnum)
);

---

messages_log

QuickFIX/J can log all inbound/outbound messages to a database. The table is write-only from the perspective of the library, so it is up to you if you wish to use this table or not.

Different tables may be specified in config for inbound and outbound message logs. By default, all messages are logged to a single table.

create sequence messages_log_sequence;

create table messages_log (
  id integer default nextval('messages_log_sequence'),
  time timestamp not null,
  beginstring char(8) not null,
  sendercompid varchar(64) not null,
  sendersubid varchar(64) not null,
  senderlocid varchar(64) not null,
  targetcompid varchar(64) not null,
  targetsubid varchar(64) not null,
  targetlocid varchar(64) not null,
  session_qualifier varchar(64),
  text text not null,
  primary key (id)
);

---

event_log

A log of events is written to this table. Examples include:

Session FIX.4.2:FOO->BAR schedule is daily, 07:00:00-UTC - 21:00:00-UTC

Created session: FIX.4.2:FOO->BAR

Initiated logon request

Received logon

create sequence event_log_sequence;

create table event_log (
  id integer default nextval('event_log_sequence'),
  time timestamp not null,
  beginstring char(8) not null,
  sendercompid varchar(64) not null,
  sendersubid varchar(64) not null,
  senderlocid varchar(64) not null,
  targetcompid varchar(64) not null,
  targetsubid varchar(64) not null,
  targetlocid varchar(64) not null,
  session_qualifier varchar(64),
  text text not null,
  primary key (id)
);

---

As @DumbCoder points out, the table names may be customised via config.