[ts-gen] Unique Identifier for Orders

Ken Feng kfmfe04 at gmail.com
Sat Sep 12 08:58:57 EDT 2009


Looking at:

> 1420|35248|  0.000104|3| 3|6|2|Submitted|0|50000|0.000000|1310448314|0|0.000000|4|
> 13112|10457|  0.000052|3| 3|6|2|Filled|1|0|171.730000|1310448317|0|171.730000|3|

I can see that the 12th element is perm (1310448314 and 1310448317)
which lives in the OrderStatus table, but not in the CreateEvent
table.  Not only that, there may be multiple rows in the OrderStatus
table.  If I join the two tables on client_id and order_tag, order by
time DESC, and do a LIMIT 1, I can trace back to CreateEvent.var.
This seems quite convoluted and kind of ugly.

I am hoping  there a more direct approach... ...or am I doing it "the
right way"?

> 9490|01556|  0.000101|3| 3|6|56|ApiCancelled|0|50000|0.000000|0|0|0.000000|8|

I don't know how to handle map ApiCancelled back to CreateEvent.var yet.

On a side-note, I noticed in Bill mentioned in his detailed
description that I should not need to replicate CreateEvent.  I was
hoping to use what is already in the Shim tables, but I don't  have a
firm enough understanding of the tables to create a VIEW of what I
need from them.

SQL for my current table from the downstream
signal-generator/reporting perspective is below - I am almost certain
that I should be able to create a VIEW in place of this table, but I
am not sure how.  Anyways, from the application level, it's useful to
have information for any order all in one row for easy reporting and
checking purposes.  This is probably a higher-level requirement than
what Shim is doing, but I thought I would throw it out there so you
can understand what I am trying to get at.

- Ken

CREATE TABLE `orders` (
  `var` VARCHAR(96) NOT NULL,
  `ymdhms` DATETIME DEFAULT NULL,
  `account` CHAR(8) DEFAULT NULL,
  `uname` VARCHAR(40) DEFAULT NULL,
  `sname` VARCHAR(40) DEFAULT NULL,
  `strat_id` VARCHAR(40) DEFAULT NULL,
  `qty` DOUBLE NOT NULL,
  `qty_to_fill` DOUBLE NOT NULL,
  `prc_avg` DOUBLE NOT NULL,
  `instr` VARCHAR(256) NOT NULL,
  `order_type` INT(10) NOT NULL,
  `stp` DOUBLE DEFAULT NULL,
  `lmt` DOUBLE DEFAULT NULL,
  `oca_tag` VARCHAR(256) DEFAULT NULL,
  `do_cancel_open_orders` TINYINT(1) NOT NULL,
  `status` ENUM('Pending','Submitted','Partial','Closed','Cancelled') NOT NULL,
  `comment` VARCHAR(256) DEFAULT NULL,
  `ts_created` DATETIME NOT NULL,
  `ts_updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
  PRIMARY KEY  (`var`),
  KEY `idx_status` (`status`),
  KEY `idx_ymdhms` (`ymdhms`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1


More information about the ts-general mailing list