View source for AVA Database Management Reference

== SQL Extension for Database Management ==
All the database management commands starts with '''"."'''. For example: 
 ava>.load 'a'
 ava>.save a as 'a'

These functions include saving, loading, compressing tables, displaying schema, database status, etc. 

=== Loading data table ===
'''SYNTAX: .load 'LOCATION' <AS> TABLE_NAME'''

 ava> .load 'a'
 0.31 seconds elapsed.

For more details, please refer to [[ Quick Start Guide | Quick Start Guide ]].

=== Saving data table ===
'''SYNTAX: .save TABLE_NAME as "LOCATION" '''

 ava> .save a as 'a2'
 0.31 seconds elapsed.

For more details, please refer to [[ Quick Start Guide | Quick Start Guide ]].

=== List tables  ===
'''SYNTAX: .tables '''

 ava> .tables
 Number of tables: 1
 ----------------------
 a
 0 seconds elapsed.

=== List table schema ===
'''SYNTAX: .schema TABLE_NAME '''
 ava> .schema a
 TABLE NAME: a | NUMBER OF RECORDS: 3,252,871
 ---------------------------------------------------
          date |         sec_id |           open |           high |            low |          close |         volume |      adj_close |
    ---------- |     ---------- |     ---------- |     ---------- |     ---------- |     ---------- |     ---------- |     ---------- |
       DATE(1) |     VARCHAR(8) |       FLOAT(1) |       FLOAT(1) |       FLOAT(1) |       FLOAT(1) |        LONG(1) |       FLOAT(1) |

 0 seconds elapsed.

=== Sort a table by column(s) ===
'''SYNTAX: .sort TABLE_NAME order by COL1 ASC|DESC, COL2 ASC|DESC'''
 ava> .sort a order by date asc, sec_id desc
 0.683 seconds elapsed.
 ava> select date, sec_id, open from a
 TABLE NAME: a | NUMBER OF RECORDS: 3,252,871
 ---------------------------------------------------
       date |     sec_id |       open |
 ---------- | ---------- | ---------- |
 2000/10/16 |   sz399001 |   4,478.15 |
 2000/10/16 |   sz200992 |       2.77 |
 2000/10/16 |   sz200986 |       1.99 |
 2000/10/16 |   sz200160 |       2.49 |
 2000/10/16 |   sz200152 |       1.89 |
 2000/10/16 |   sz000999 |      19.10 |
 2000/10/16 |   sz000997 |      33.00 |
 2000/10/16 |   sz000996 |      18.80 |
 2000/10/16 |   sz000995 |      15.35 |
 2000/10/16 |   sz000993 |      13.40 |
 0.001 seconds elapsed.

=== Compress a table ===
The compression and decompression methods are all lost-less date compression. 
'''SYNTAX: .compress TABLE_NAME <by COMPRESSION METHOD>'''
The COMPRESSION METHODs are LZF, LZ4, LZMA. If the compression commands are repeated with different method, the later command will always convert previous the compression to the new compression. All methods are inline, in-memory. The LZF and LZ4 methods are high-speed methods with lower compression ratio. The LZMA method produces higher compression ratio, but the speed is much lower.
 ava> .compress a
 0.412 seconds elapsed.

=== Deompress a table ===
'''SYNTAX: .compress TABLE_NAME'''
 ava> .decompress a
 0.143 seconds elapsed.