[ts-gen] An update on symbol additions

[R P Herrold]

For the last couple days, we have been working on adding the 
requested UK, Forex, Globex and Asia/Pacific Underlyings to 
the database, so that they might be manipulated without 
needing to wander through getting the inserts right.  I had 
been initially focusing on the needed additions to the 
Definition table, and in doing so, we here again revisited the 
difference between an Underlying, and its possibly multiple 
presences in the Definition table.

Putting to side the Globex and Forex matters, an insert 
presently might look like this:

insert into Definition (tid, home, name, Definition.desc)  values
( 1, 30, 'VOD', 'VODAFONE GROUP' ),
( 1, 30, 'DRX', 'DRAX GROUP PLC' ),
( 4, 33, 'Z', 'FINANCIAL TIMES STOCK EXCHANGE 100' ),
( 3, 33, 'Z', 'FINANCIAL TIMES STOCK EXCHANGE 100' ),
( 3, 33, 'TSB', 'LLOYDS TSB GROUP' ),
( 4, 39, 'DAX', 'DAX INDEX' ),
( 3, 39, 'DAX', 'DAX INDEX' ),
( 3, 48, 'SPI', 'S AND P/AUSTRALIAN STOCK EXCH 200 INDEX' ),
( 4, 48, 'SPI', 'S AND P/AUSTRALIAN STOCK EXCH 200 INDEX' ),
( 1, 52, 'SGX', 'SINGAPORE EXCHANGE LTD' ),
( 4, 52, 'SGX', 'USD NIKKEI 225 INDEX' ),
( 4, 49, 'HSI', 'HANG SENG STOCK INDEX' ),
( 3, 49, 'HSI', 'HANG SENG STOCK INDEX' );
[herrold at centos-4 database]$

We will be rolling something like that and another insert 
adding Forex and Globex tradables and Index Underlyings 
shortly.


Getting the 'Definition.desc' field with to stay under 40 
characters and yet remain human intelligible and 'queryable' 
in looking up what to subscribe to or to trade was a bit of a 
challenge.  There is also the editorial problem that the 
general description quality is 'all over the lot', and we have 
had to expend editorial (clerical) time making the 
descriptions more uniform between the various sources of 
Underlyings.

There is a helpful link in IB space to query into their 
opinion of what is mentioned, say at an enchange, at:

 	http://www.interactivebrokers.com/cgi-pub/symbolSearch.pl?
 		exchange=HKFE&symbol=HSI
 	[normal paste me back together rules apply]

More challenging was that this effort brought to the 'fore a 
lack of normalization on our part in the present database 
schema.  I constructed the insert to highlight the issue.

Also, we talked through supporting remote users with Exchanges 
and Underlying values whose databases will vary from, and 
which we will never be able to see directly.

Let us emphasize again, that for bug reporting purposes, and 
to get to a reproducer, having the dataset loaded afresh 
though a process something like this is an assumed starting 
point, before the running of the 'make test' scripts 
[substitute appropriate valuee for userid, hostname, and 
datasetname to taste]:

$ cd sql
$ cat - << END | mysql -u userid -h hostname datasetname
drop database datasetname;
create database datasetname;
quit;
END

followed by:

$ mysql -u userid -h hostname datasetname  < ./create.sql

and then:

$ cd ..
$ make test

and completely and cleanly passing that 'make test' is a 
starting point for every inquiry.  As always, we assume you 
are testing against a test dataset, and doing any production 
against a different one.

-- Russ Herrold