程序员的量化交易之路（14）--Cointrader数据表（2）Cointrader表结构

设置（setup)

install java

install maven

install mysql

create a database using

mysql -u root -e `create database cointrader;`

this is mysql root not system root

git clone https://github.com/timolson/cointrader.git

cd cointrader

build with maven

mvn

optional: create a file cointrader.properties in the current directory. you may configure additional settings here, like a database username and password. see cointrader-default.properties.

initialize the database with:

./cointrader.sh reset-database

you're done! now you might want to:

start a process to save market data in the db:

./cointrader.sh save-data &

run the console

./cointrader.sh console

登入数据库：

$ mysql -u root -p

查看database：

mysql> show databases;

+--------------------+

| database           |

| information_schema |

| cointrader         |

| db_test            |

| mysql              |

| openfire           |

| performance_schema |

| xoops              |

7 rows in set (0.00 sec)

切入cointrader：

mysql> use cointrader;

reading table information for completion of table and column names

you can turn off this feature to get a quicker startup with -a

database changed

查看表格：

mysql> show tables;

+--------------------------+

| tables_in_cointrader     |

| adjustment               |

| adjustment_deltas        |

| authorization            |

| book                     |

| currency                 |

| exchange                 |

| fill                     |

| fill_children            |

| general_order            |

| listing                  |

| market                   |

| market_data_gap_error    |

| offer                    |

| order_children           |

| order_update             |

| owner                    |

| owner_stakes             |

| portfolio                |

| portfolio_manager        |

| portfolio_stakes         |

| position                 |

| position_fills           |

| position_update          |

| prompt                   |

| specific_order           |

| stake                    |

| strategy_instance        |

| strategy_instance_config |

| tick                     |

| trade                    |

| transaction              |

31 rows in set (0.01 sec)

查看每一个表的表结构

mysql> desc adjustment;

+---------------+------------+------+-----+---------+-------+

| field         | type       | null | key | default | extra |

| id            | binary(16) | no   | pri | null    |       |

| version       | bigint(20) | yes  |     | null    |       |

| time_applied  | tinyblob   | yes  |     | null    |       |

| authorization | binary(16) | no   | uni | null    |       |

mysql> desc adjustment_deltas;

+------------+------------+------+-----+---------+-------+

| field      | type       | null | key | default | extra |

| adjustment | binary(16) | no   | mul | null    |       |

| deltas     | binary(16) | no   | pri | null    |       |

2 rows in set (0.00 sec)

mysql> desc  authorization    ;

+---------+--------------+------+-----+---------+-------+

| field   | type         | null | key | default | extra |

| id      | binary(16)   | no   | pri | null    |       |

| version | bigint(20)   | yes  |     | null    |       |

| notes   | varchar(255) | yes  |     | null    |       |

3 rows in set (0.00 sec)

mysql> desc  authorization  ;

mysql> desc  book ;

+----------------------+--------------+------+-----+---------+-------+

| field                | type         | null | key | default | extra |

| id                   | binary(16)   | no   | pri | null    |       |

| version              | bigint(20)   | yes  |     | null    |       |

| time                 | bigint(20)   | no   | mul | null    |       |

| remote_key           | varchar(255) | yes  |     | null    |       |

| time_received        | bigint(20)   | no   | mul | null    |       |

| market               | binary(16)   | no   | mul | null    |       |

| ask_deletions_blob   | longblob     | yes  |     | null    |       |

| ask_insertions_blob  | longblob     | yes  |     | null    |       |

| ask_price_as_double  | double       | yes  |     | null    |       |

| ask_volume_as_double | double       | yes  |     | null    |       |

| bid_deletions_blob   | longblob     | yes  |     | null    |       |

| bid_insertions_blob  | longblob     | yes  |     | null    |       |

| bid_price_as_double  | double       | yes  |     | null    |       |

| bid_volume_as_double | double       | yes  |     | null    |       |

| parent               | binary(16)   | yes  | mul | null    |       |

15 rows in set (0.00 sec)

mysql> desc currency ;

| basis   | double       | no   |     | null    |       |

| symbol  | varchar(255) | no   |     | null    |       |

| fiat    | bit(1)       | no   |     | null    |       |

5 rows in set (0.00 sec)

mysql> desc  exchange    ;

+-------------------+--------------+------+-----+---------+-------+

| field             | type         | null | key | default | extra |

| id                | binary(16)   | no   | pri | null    |       |

| version           | bigint(20)   | yes  |     | null    |       |

| fee_method        | int(11)      | yes  |     | null    |       |

| fee_rate          | double       | no   |     | null    |       |

| margin            | int(11)      | no   |     | null    |       |

| margin_fee_method | int(11)      | yes  |     | null    |       |

| margin_fee_rate   | double       | no   |     | null    |       |

| symbol            | varchar(255) | no   |     | null    |       |

8 rows in set (0.00 sec)

mysql> desc  fill    ;

+--------------------+--------------+------+-----+---------+-------+

| field              | type         | null | key | default | extra |

| id                 | binary(16)   | no   | pri | null    |       |

| version            | bigint(20)   | yes  |     | null    |       |

| time               | bigint(20)   | no   |     | null    |       |

| remote_key         | varchar(255) | yes  |     | null    |       |

| time_received      | bigint(20)   | no   |     | null    |       |

| open_volume_count  | bigint(20)   | no   |     | null    |       |

| price_count        | bigint(20)   | no   |     | null    |       |

| stop_price_count   | bigint(20)   | no   |     | null    |       |

| target_price_count | bigint(20)   | no   |     | null    |       |

| volume_count       | bigint(20)   | no   |     | null    |       |

| market             | binary(16)   | yes  | mul | null    |       |

| order              | binary(16)   | yes  | mul | null    |       |

| portfolio          | binary(16)   | no   | mul | null    |       |

13 rows in set (0.01 sec)

mysql> desc fill_children  ;

+----------+------------+------+-----+---------+-------+

| field    | type       | null | key | default | extra |

| fill     | binary(16) | no   | mul | null    |       |

| children | binary(16) | no   | pri | null    |       |

mysql> desc general_order   ;

+-----------------------------+---------------+------+-----+---------+-------+

| field                       | type          | null | key | default | extra |

| id                          | binary(16)    | no   | pri | null    |       |

| version                     | bigint(20)    | yes  |     | null    |       |

| time                        | bigint(20)    | no   |     | null    |       |

| comment                     | varchar(255)  | yes  |     | null    |       |

| emulation                   | bit(1)        | no   |     | null    |       |

| entry_time                  | bigint(20)    | yes  |     | null    |       |

| expiration                  | tinyblob      | yes  |     | null    |       |

| fill_type                   | int(11)       | yes  | mul | null    |       |

| margin_type                 | int(11)       | yes  |     | null    |       |

| panic_force                 | bit(1)        | no   |     | null    |       |

| position_effect             | int(11)       | yes  |     | null    |       |

| market                      | binary(16)    | yes  | mul | null    |       |

| parent_order                | binary(16)    | yes  |     | null    |       |

| portfolio                   | binary(16)    | no   | mul | null    |       |

| limit_price_decimal         | decimal(19,2) | yes  |     | null    |       |

| stop_amount_decimal         | decimal(19,2) | yes  |     | null    |       |

| stop_price_decimal          | decimal(19,2) | yes  |     | null    |       |

| target_amount_decimal       | decimal(19,2) | yes  |     | null    |       |

| target_price_decimal        | decimal(19,2) | yes  |     | null    |       |

| trailing_stop_price_decimal | decimal(19,2) | yes  |     | null    |       |

| volume_decimal              | decimal(19,2) | yes  |     | null    |       |

| listing                     | binary(16)    | no   | mul | null    |       |

22 rows in set (0.00 sec)

mysql> desc listing   ;

+---------+------------+------+-----+---------+-------+

| field   | type       | null | key | default | extra |

| id      | binary(16) | no   | pri | null    |       |

| version | bigint(20) | yes  |     | null    |       |

| base    | binary(16) | no   | mul | null    |       |

| prompt  | binary(16) | yes  | mul | null    |       |

| quote   | binary(16) | no   | mul | null    |       |

mysql> desc market ;

+--------------+------------+------+-----+---------+-------+

| field        | type       | null | key | default | extra |

| id           | binary(16) | no   | pri | null    |       |

| version      | bigint(20) | yes  |     | null    |       |

| active       | bit(1)     | no   | mul | null    |       |

| price_basis  | double     | no   |     | null    |       |

| volume_basis | double     | no   |     | null    |       |

| exchange     | binary(16) | no   | mul | null    |       |

| listing      | binary(16) | no   | mul | null    |       |

mysql> desc market_data_gap_error;

+--------------------+------------+------+-----+---------+-------+

| field              | type       | null | key | default | extra |

| id                 | binary(16) | no   | pri | null    |       |

| version            | bigint(20) | yes  |     | null    |       |

| time               | bigint(20) | no   |     | null    |       |

| exception          | tinyblob   | yes  |     | null    |       |

| gap_interval_start | datetime   | yes  |     | null    |       |

| gap_interval_end   | datetime   | yes  |     | null    |       |

| market             | binary(16) | yes  | mul | null    |       |

mysql> desc offer  ;

+---------------+--------------+------+-----+---------+-------+

| field         | type         | null | key | default | extra |

| id            | binary(16)   | no   | pri | null    |       |

| version       | bigint(20)   | yes  |     | null    |       |

| time          | bigint(20)   | no   |     | null    |       |

| remote_key    | varchar(255) | yes  |     | null    |       |

| time_received | bigint(20)   | no   |     | null    |       |

| market        | binary(16)   | no   | mul | null    |       |

| price_count   | bigint(20)   | yes  |     | null    |       |

| volume_count  | bigint(20)   | yes  |     | null    |       |

mysql> desc order_children   ;

| order    | binary(16) | no   |     | null    |       |

mysql> desc order_update ;

+------------+------------+------+-----+---------+----------------+

| field      | type       | null | key | default | extra          |

| sequence   | int(11)    | no   | pri | null    | auto_increment |

| id         | binary(16) | no   | pri | null    |                |

| version    | bigint(20) | yes  |     | null    |                |

| time       | bigint(20) | no   |     | null    |                |

| last_state | int(11)    | yes  |     | null    |                |

| state      | int(11)    | yes  | mul | null    |                |

| order      | binary(16) | yes  |     | null    |                |

mysql> desc owner      ;

+-----------+--------------+------+-----+---------+-------+

| field     | type         | null | key | default | extra |

| id        | binary(16)   | no   | pri | null    |       |

| version   | bigint(20)   | yes  |     | null    |       |

| portfolio | binary(16)   | yes  | mul | null    |       |

| name      | varchar(255) | no   |     | null    |       |

4 rows in set (0.00 sec)

mysql> desc owner_stakes ;

+--------+------------+------+-----+---------+-------+

| field  | type       | null | key | default | extra |

| owner  | binary(16) | no   | mul | null    |       |

| stakes | binary(16) | no   | pri | null    |       |

mysql> desc portfolio  ;

+------------+--------------+------+-----+---------+-------+

| field      | type         | null | key | default | extra |

| id         | binary(16)   | no   | pri | null    |       |

| version    | bigint(20)   | yes  |     | null    |       |

| name       | varchar(255) | yes  |     | null    |       |

| base_asset | binary(16)   | yes  |     | null    |       |

mysql> desc portfolio_manager  ;

+-----------+------------+------+-----+---------+-------+

| field     | type       | null | key | default | extra |

| id        | binary(16) | no   | pri | null    |       |

| version   | bigint(20) | yes  |     | null    |       |

| portfolio | binary(16) | yes  | mul | null    |       |

mysql> desc portfolio_stakes ;

| portfolio | binary(16) | no   | mul | null    |       |

| stakes    | binary(16) | no   | pri | null    |       |

mysql> desc position ;

| asset     | binary(16) | yes  |     | null    |       |

| exchange  | binary(16) | yes  | mul | null    |       |

| market    | binary(16) | yes  | mul | null    |       |

6 rows in set (0.00 sec)

mysql> desc position_fills  ;

| position | binary(16) | no   | mul | null    |       |

| fills    | binary(16) | no   | pri | null    |       |

mysql> desc  position_update;

| time      | bigint(20) | no   |     | null    |       |

| last_type | int(11)    | yes  |     | null    |       |

| type      | int(11)    | yes  |     | null    |       |

| position  | binary(16) | yes  | mul | null    |       |

mysql> desc   prompt  ;

| contract_size     | double       | no   |     | null    |       |

| margin_method     | int(11)      | yes  |     | null    |       |

| price_basis       | double       | no   |     | null    |       |

| tick_size         | double       | no   |     | null    |       |

| tick_value        | double       | no   |     | null    |       |

| volume_basis      | double       | no   |     | null    |       |

| traded_currency   | binary(16)   | yes  |     | null    |       |

14 rows in set (0.01 sec)

mysql> desc specific_order ;

| time              | bigint(20)   | no   |     | null    |       |

| comment           | varchar(255) | yes  |     | null    |       |

| emulation         | bit(1)       | no   |     | null    |       |

| entry_time        | bigint(20)   | yes  |     | null    |       |

| expiration        | tinyblob     | yes  |     | null    |       |

| fill_type         | int(11)      | yes  | mul | null    |       |

| margin_type       | int(11)      | yes  |     | null    |       |

| panic_force       | bit(1)       | no   |     | null    |       |

| position_effect   | int(11)      | yes  |     | null    |       |

| market            | binary(16)   | yes  | mul | null    |       |

| parent_order      | binary(16)   | yes  |     | null    |       |

| portfolio         | binary(16)   | no   | mul | null    |       |

| limit_price_count | bigint(20)   | no   |     | null    |       |

| placement_count   | int(11)      | no   |     | null    |       |

| remote_key        | varchar(255) | yes  |     | null    |       |

| time_received     | bigint(20)   | yes  |     | null    |       |

| volume_count      | bigint(20)   | no   |     | null    |       |

19 rows in set (0.01 sec)

mysql> desc stake  ;

+-----------+----------------+------+-----+---------+-------+

| field     | type           | null | key | default | extra |

| id        | binary(16)     | no   | pri | null    |       |

| version   | bigint(20)     | yes  |     | null    |       |

| stake     | decimal(30,15) | yes  |     | null    |       |

| owner     | binary(16)     | yes  | mul | null    |       |

| portfolio | binary(16)     | no   | mul | null    |       |

mysql> desc strategy_instance ;

+-------------+--------------+------+-----+---------+-------+

| field       | type         | null | key | default | extra |

| id          | binary(16)   | no   | pri | null    |       |

| version     | bigint(20)   | yes  |     | null    |       |

| portfolio   | binary(16)   | yes  | mul | null    |       |

| module_name | varchar(255) | yes  |     | null    |       |

mysql> desc  strategy_instance_config;

| strategy_instance | binary(16)   | no   | pri | null    |       |

| config            | varchar(255) | yes  |     | null    |       |

| config_key        | varchar(255) | no   | pri |         |       |

mysql> desc  tick  ;

| start_instant | tinyblob     | yes  |     | null    |       |

| last_book     | binary(16)   | yes  | mul | null    |       |

10 rows in set (0.00 sec)

mysql> desc trade    ;

| time          | bigint(20)   | no   | mul | null    |       |

| time_received | bigint(20)   | no   | mul | null    |       |

mysql> desc  transaction  ;

+---------------------+------------+------+-----+---------+-------+

| field               | type       | null | key | default | extra |

| id                  | binary(16) | no   | pri | null    |       |

| version             | bigint(20) | yes  |     | null    |       |

| time                | bigint(20) | no   |     | null    |       |

| amount_count        | bigint(20) | yes  |     | null    |       |

| commission_count    | bigint(20) | yes  |     | null    |       |

| position_effect     | int(11)    | yes  |     | null    |       |

| price_count         | bigint(20) | yes  |     | null    |       |

| type                | int(11)    | yes  | mul | null    |       |

| asset               | binary(16) | yes  |     | null    |       |

| commission_currency | binary(16) | yes  |     | null    |       |

| currency            | binary(16) | no   |     | null    |       |

| exchange            | binary(16) | yes  | mul | null    |       |

| fill                | binary(16) | yes  | mul | null    |       |

| market              | binary(16) | yes  | mul | null    |       |

| order               | binary(16) | yes  |     | null    |       |

| portfolio           | binary(16) | no   | mul | null    |       |

16 rows in set (0.00 sec)