[ts-gen] Template table record uids

Bill Pippin pippin at owlriver.net
Wed Aug 12 13:10:11 EDT 2009


About your questions, most of which relate to Template table
uid indices, first, your cogent observation referring to the
relative population size of the testing and trading database
Template tables:

> I see that only [testing] Template is populated ... [trading]
> Template is empty.

The empty state of trading.Template is no accident.  You can't use
the shim to make trades without Template records, and we have no
intention of encouraging users to start out making trades to their
live account before they know exactly what they're doing, and take
full responsibility thereof.  It's up to *you* to add entries
to trading.Template .

I can state this now as policy: I have no intention of shipping
shim tarball setup scripts that preload trading.Template .
The setup.sql script should preload *only* testing.Template,
and that only as a means of testing, explanation, or demonstration.
If setup.sql puts Template records in the trading database, it's a
bug, we want to know about it, and you should delete them.

The Template table in your trading database is *all* yours,
including every countably finite non-zero uid.  You can't leave
gaps --- the shim will refuse to read past the first, and quit ---
but the definitions you give for the uids starting from 1 and
their related record contents are totally up to you.

> ... Template table ... create uid's that start from 100 ...

As suggested above, this won't work; the shim expects most tables
to have record uids numbered to provide a closed interval starting
from 1.  Internally within the shim those uids map identically to
vector indices, and so for correctness and safety the shim checks
for closure at startup.

> ... in case Bill adds more entries into the [testing.Template]
> table ... uid's don't clash. ...

As indicated above, I have no plans to predefine entries in 
testing.Template beyond the needs of the regression scripts.
As long as you don't mind breaking such scripts, e.g., risk.rb,
feel free to delete them.  And, to reiterate, trading.Template
is *all* yours.

You might like to look at the postscript foreign key hierarchy
diagrams in the sql directory.  It's true that tables near the
leaves of that graph and that are referred to by many others via
checked foreign key references may well have fixed contents, e.g.,
Exchange, and you can't easily change those without breaking the

It's also true that there may well be relationships and dependencies
for tables higher in the graph that you can't yet tamper with.  E.g.,
products are sorted by security type, with futures and other
derivatives at the end, and if you break the sort ordering that is
provided by sql/more.sql and the related symbol load files, again,
you'll have problems for now.

But that's symbols.  Order template records have no such symbolic
key, and, as long as you obey the foreign key relationships as
declared, the contents are up to you.  By the way, as far as the
other tables declared in sql/xact.sql, note also that the journal
records are one-to-one with the related events, whether command
or message, and that many of the foreign keys are implicit or
denormalized.  This relates to a basic distinction between the
symbols tables that the shim reads in at startup, and your trading
history OTOH; the former can easily be recreated by repopulating
the database from the load files, while you'll probably want to
save old entries from the journal even when you repopulate the

> ... I find, like almost everywhere else in the Mysql tables
> that uid is an auto_increment field ...

Yes.  As a general rule, natural number keys in the database --- IB
contract ids are the one important exception here --- are part of
the implementation domain, while symbolic keys are often part of
the application domain.  So, users generally learn numeric keys by 
selecting on the related attributes; their actual values typically
have no semantic significance in the application domain.

Since you'll be setting up your own Template entries, you'll care
about those uids when you load that table, and especially about
the same table key dependencies for child to parent records, but,
as I'll suggest below, your app code that *reads* those tables
arguably should remain ignorant of the literal values of those

>                  ...  Since these uid's are "hard-coded" into our
> Ruby code, I have to make sure they are invariant over time or it
> becomes a maintenance nightmare.

Perhaps, but not necessarily.  If you only have a limited number
of strategies, then you'll probably only have a limited number of
Template entries.  You can think carefully about what you want each
Template record to mean, select such records(s) via sql statements
from your ruby code that match on the attributes for their meaning,
check that the select statements give the appropriate number of
tuples, and bind symbolic ruby variable names to the resulting uids.

I realize that the demo scripts use hard-coded record uids in many
places.  This is poor practice, and I note here that their coding
leaves abundant room for improvement, that the bind command as well
as its planned future enhancements are meant to reduce the scope and
frequency of such manifest literals, and finally, that the new
value-based contract expressions are yet another, more successful
feature enhancement to eliminate numeric keys from application
domain source code. 

I see that you're aware of the sql/risk.sql script:

> ... the auto_increment doesn't preclude me from ...

and in particular the following text therein:

   insert into Template(side, pro, fid)      values (1, 37, 2);
   insert into Template(side, pro, fid, par) values (2, 37, 2, 3),
                                                    (2, 37, 2, 3),
                                                    (2, 37, 2, 3);

For any particular strategy, there probably won't be that many
bracket order skeletons.  Such insert statements may be ugly
and tedious to setup and debug, but they're remarkably stable
once completed, and reasonably concise.  Note above that you
need to think about the protocol and order side, and keep track
of the child -> parent links, and otherwise you can just accept
default values for the order Template.

> ... answers my own question ... spam.

Not at all.  You're reading the database scripts, learning about
the tables created thereby, and you have questions.  That's fine.



More information about the ts-general mailing list