[ts-gen] cross session orders, client ids

[pippin at owlriver.net]

Nils,

With respect to: 

> great features, great work!

Thanks.  It's nice to come back from Christmas break and see
enthusiasm on the list.

Part way through answering your question, I realized that it probably
didn't have anything to do with the new mark aka label aka brand aka
order_ref attribute.  The key point was probably the phrase:

> ... what might be the right candidate for a common key [?]

In brief, you need to match using (acc, client_id, order_tag)
triples.  Such triples occur in the tables CreateEvent,
OrderStatus, ActiveOrder, and OrderReport.  Records of the first
reflect commands to, and the other three, messages from, the api.
These three attributes --- the account code, connection id, and
order id --- together work to identify a unique order line item.
Although there are other unique keys for order line items, no
other unique key tuple is as widely available across the various
journal tables.

Now, for a literal answer to your question:

> Regarding the database structure, I wonder,
> how can I query something like ...

There is one record written to the CreateEvent table for each order
creation event, and the attribute named 'mark' of CreateEvent tracks
the 'create item' command label.  Possible queries of interest might
include:

    select distinct mark      from CreateEvent;
    select distinct order_ref from ActiveOrder;

    select *
      from CreateEvent
     order by acc, client_id, mark, order_tag;

    select *
      from CreateEvent, ActiveOrder
     where CreateEvent.acc       = ActiveOrder.acc
       and CreateEvent.client_id = ActiveOrder.client_id
       and CreateEvent.order_tag = ActiveOrder.order_tag
     order
        by CreateEvent.acc,
           CreateEvent.client_id,
           CreateEvent.mark,
           CreateEvent.order_tag;

    select mark
      from CreateEvent
     where not exists
   (select *
      from ActiveOrder
     where ActiveOrder.order_ref = CreateEvent.mark);

Feel free to add indices to the database as desired; the last
in particular might warrant:

    create index OrderLabels on ActiveOrder(order_ref);

As long as the IB tws continues to return the order ref in open
order messages, and your app is written to trigger open order
messages over the api, then you should see matching mark and
order_ref attributes in the natural join above; and otherwise, not.

The following is an excerpt from the NEWS:

> * Use the create order mark/brand value as the order_ref
>   attribute of the place order request, and, when that
>   value is returned as part of the open order message, store
>   it as part of the journal entry written to the ActiveOrder
>   table.  ...  Note that, although the IB tws
>   online docs indicate that the order_ref attribute is for
>   institutional investors only, it works fine for us in
>   testing. ...

The shim is designed to maintain a one-to-one mapping between logged
order-related events and database table records; one order event, 
one table record.  The only, and unavoidable exception is the first
occurrence of a ChangeOrder, which, along with the parent CreateEvent,
is grouped into a transaction, with both of its writes working with
downstream-provided data, that is commands.  The order status, open
order, execution report, and portfolio messages that are provided by
the api are fundamentally asynchronous, and the shim can not guarantee
that the implied foreign key relations check, or even that order
messages occur, or are received by the shim.

If I see that the order_ref attribute occurs somewhere in order status
or execution report messages, I'll be glad to include it as an attribute
in the related OrderStatus and OrderResult tables.  Until then, however,
the only feasible matching I see is on CreateEvent and ActiveOrder, as
shown above.

> ... I am rather wondering how to perform a left join CreateEvent
> <-> OrderStatus for example to aggregate fill statistics for a
> specific brand (or brand, could be to measure performance of a
> sub strategy) ...

You probably don't want to join via the mark/order_ref, that
is via an equation such as: 

    CreateEvent.mark = ActiveOrder.order_ref	-- wrong!

One, order_ref doesn't seem to be returned as part of order status
messages, although it does occur in the open order messages; two,
the (acc, client_id, order_tag) triples mentioned above are almost
certainly what you're looking for here; and three, you wouldn't
want to join CreateEvent and Activeorder using the constraint
above, for either of two reasons: If, for the intended use as a
set/group/position label, the mark would not be a unique key,
and the query count would have cross product size.  Otherwise,
if you constrained them to be unique per line item, the label
is pointless, since once given a join via the (acc, client_id,
order_tag) triple, the var name works just as well.

Thanks,

Bill