[ts-gen] Saving local history [Was: How do I use "post"?]

Bill Pippin pippin at owlriver.net
Wed Aug 5 13:41:35 EDT 2009


In reading this reply, if you want to cut to the chase, consider
reading the ruby scripts in sql/bin for examples of simple
downstream-controlled database inserts; and look at sql/perms.sql
to see what sql permissions the various default accounts have.

To consider your question directly, about taking 5 second price
data that has been locally aggregated to the one minute level
and storing it in the database:

> I would like to query Forex in real-time (5sec responses) ...

Evidently you already know about exs/bars, the select bars cmd,
and the IB request realtime bars api feature with resulting
open-high-low-close messages.

> ... build my own minute bars and store them in the HistoryBar table.

A ruby script to watch for the realtime bar messages --- event
type 3, msg index 50, version level 3, so would match on |3|50| 3|
--- and accumulate over the course of a minute should be straight
forward.  There are also time stamps as part of the log format
prefix, so that your code doesn't even have to independently track
time.  Presumably the task of downstream aggregation, then, isn't
too hard.

> By the look of src/post.c it looks like "post" is the way to go,
> but typing: help post; to the shim doesn't give me anything.
> Does anyone have an example of how "post" can be called from Ruby?

There is no "post" command, and so no such command to be called
from ruby, or anything else; the downstream has no direct control
over database writes.  The Poster singleton, which controls
database inserts by the shim, does indeed have much of its code
in post.c, but that code is triggered as a result of order commands
and certain predefined message events, more precisely the create,
modify, submit, cancel, and related commands; the journal messages,
that is open order, order status, portfolio, and execution report;
and history query answers.

You are free to write directly to the database from your downstream
process.  The /usr/bin/mysql interpreter accepts sql text from the
stdin once given the proper connection parameters on the command
line, so the key step is building the sql insert statement.  Feel
free to crib from post.c, which you're already familiar with, and
see also the ruby scripts in sql/bin.

The database setup script gives you four account names, by
default code, shim, data, and risk, with the first two meant for
interactive database admin and the shim, and the last two for
read-only data analysis and order-generating downstream scripts.

Looking in the sql directory at perms.sql in particular, and to some
degree names.sql as well, you'll see that for what you want to do,
you'll probably want to have your downstream local-bar script
connect as mysql user "code", since the script will be doing writes
to the database.

Of course the aggregation and inserts could also be performed by the
shim.  For now, however, I'll probably leave the set of post operations
unchanged, in part since there is no obvious one-size-fits-all
algorithm for local history aggregation.  If you decide to implement
such a change --- note that you can hardwire the computation for
your case, without adding a new command --- please feel free to post
the code to the list.

About new post operations in the shim, if you are deterimined to
add such: events flow from the cmd and msg readers, through the
router, and from there to the output channels of the Poster and

So, you'd need to add rules in proc.c to match the realtime bar
messages, and route them to the Poster; add a set of price/quantity
variables there to accumulate bar data; and provide some database
write code in post.c to insert the resulting HistoryBar record to
the database.

The realtime bar tick id object, which retains the symbol for that
particular subscription, is stored in the Bindings singleton tick
vector, shim.flow.base.factor.tables.bind.tick_vec, and you would
need to dispatch on the tick type to convert the generic SeqNum
object to a subscription id, SubsId, object.

Writing a downstream script is *much* the simpler approach, and
it has the tremendous advantage that if and when you want to modify
such a script to act on those prices, it already has them, without
having to query the database at the cost of the additional latency
and the race between shim inserts and downstream selects.

You might also want to use a distinct table, even if the structure
is identical to that of HistorBar, since duplicate records from
history query answers that follow your local bar computations will
replace them; see the HistoryBar delete statement in post.c, 
Poster::remove(), to determine the range limits for history


More information about the ts-general mailing list