Step 2: Executing commands and scripts

Executing basic xSQL commands and scripts

As mentioned in Step 1, xSQL can be started in server or client mode. For the next group of exercises we will start xSQL with the command file xsql.bat directory /samples/xsql/scripts/financial with the default parameters to enter interactive SQL statements. xSQL displays the following lines indicating it is ready to accept interactive SQL statements:

 
    Simple interactive configuration is started
    To run as a server, type, for example: 'xsql -size 100m -p 5000'
    Then to connect as a client, use 'xsql @127.0.0.1:5000'
    Type 'xsql -h' for more details
     
    xsql started
    Runtime configuration
     Transaction manager  : MURSIW
     Storage (transient)  : Conventional memory, 100M
     Storage (persistent) : Not supported
     Runtime              : Release
    XSQL>
     

Executing SQL statements

To execute SQL statements interactively simply type the statement followed by a semicolon. For example, the following select statement queries the system table Metatable to show that there are as yet no tables in the database:

 
    XSQL>select TableName from Metatable;
    TableName
    ------------------------------------------------------------------------------
     
    Selected records: 0
    XSQL>
     

So to create and initialize a table we might enter statements like the following:

 
    XSQL>create table t(id int);
    XSQL>insert into t values( [1,2,3] );
    XSQL>select * from t;
    id
    ------------------------------------------------------------------------------
    1
    2
    3
     
    Selected records: 3
    XSQL>
     

Executing script files

But rather than type SQL statements interactively, it is often more convenient to read SQL script files into xSQL to preform SQL operations. To facilitate this exercise, several scripts and additional files are provided in the directory /samples/xsql/scripts/financial.

For example, the scripts in files quote.sql, insert.sql and count.sql create the table Quote, then populate it with some randomized trade data and perform a query. The quote.sql script creates the table with a time series consisting of the ordered sequence day associated with five sequences of values high, low, open, close and volume:

 
    create table Quote( symbol char(21) primary key, day sequence(unsigned(4) asc),
        low sequence(float), high sequence(float), open sequence(float), 
        close sequence(float), volume sequence(unsigned(4)) );
         

The insert.sql script inserts a number of values into the sequences with lines like the following:

 
    insert into Quote (symbol,day,low,high,open,close,volume) values ('SYM0','{20130101,20130104,...},{...}, ..., {...});
     

(Note the use of the '{ value list }' to insert a series of values into the sequence fields.)

Then script count.sql simply sets the format for output (more about this later) and selects the count of records inserted:

 
    format CSV
    select count(symbol) as "Quote records inserted" from Quote;
     

These three scripts can be executed in "batch" mode by starting xSQL with the following command:

 
    xsql -size 100m -b -f quote.sql -f insert.sql -f count.sql
    Quote records inserted
    10
     

Note that xSQL terminates after executing the scripts in "batch" mode. To use xSQL interactively we use the -i command line option. For example we can run the first two scripts to initialize the Quote table and enter interactive mode with the following command:

 
    xsql -size 100m -i -f quote.sql -f insert.sql
     

Now we can type an SQL select statement like the following to display the top performing stocks from this set of test data:

 
    XSQL>select symbol, seq_top_pos_max(close, 1) as top_index,
        close@top_index  as "top_Close",
        volume@top_index as "volume" from Quote;
         
    symbol  top_index      top_Close       volume
    ------------------------------------------------------------------------------
    SYM0    {34}    {97.2399978637695}      {732}
    SYM1    {57}    {96.5299987792969}      {406}
    SYM2    {2}      {94.870002746582}        {765}
    SYM3    {65}    {98.7399978637695}      {953}
    SYM4    {62}    {93.25}                               {312}
    SYM5    {76}    {92.5199966430664}      {829}
    SYM6    {59}    {94.8300018310547}      {761}
    SYM7    {98}    {99.3199996948242}      {583}
    SYM8    {37}    {97.5899963378906}      {593}
    SYM9    {81}    {93.7799987792969}      {257}
     
    Selected records: 10
     

Note that this example makes use of some eXtremeSQL extensions to standard SQL that operate on the Quote sequence fields close and volume. These SQL extensions and the powerful library of statistical functions provided for analytics are explained and demonstrated in the SQL analytics functions examples.

The script command

Again, rather than type complex SQL statements interactively, especially when they may be repeated, it is more convenient to store the statement(s) in a script file. In fact the above select statement is provided in file top_close.sql. We can run this script to obtain the same results from interactive mode using the script command:

 
    XSQL>script top_close.sql
     

The file top_close.sql looks like this:

 
    select symbol, seq_top_pos_max(close, 1) as top_index, 
        close@top_index  as "top_Close", volume@top_index as "volume" from Quote;
         

Note that the last line issues command input console to return input to STDIN (the keyboard). Without this line the file will be executed in batch mode and xSQL will close. Alternatively the script command can be used to remain in xSQL interactive mode, or to run one script from another script file. For example suppose we have the following two script files:

inner.sql:

 
    XSQL>select TableName, FieldName, FieldTypeName  from Metatable;
     

and outer.sql:

 
    select 1;
    script inner.sql
    select 2;
     

Now we could execute the script command in interactive mode to produce the following output:

 
    XSQL>script outer.sql
    #1
    ------------------------------------------------------------------------------
    1
     
    Selected records: 1
    TableName       FieldName       FieldTypeName
    ------------------------------------------------------------------------------
    Quote   symbol  String
    Quote   day     Sequence
    Quote   low     Sequence
    Quote   high    Sequence
    Quote   open    Sequence
    Quote   close   Sequence
    Quote   volume  Sequence
     
    Selected records: 7
    #1
    ------------------------------------------------------------------------------
    2
     
    Selected records: 1
    XSQL>
     

Or the script outer.sql can be run from the xSQL command line to produce the same result and remain in interactive mode:

 
    xsql -f outer.sql
     

It is often convenient to display output from select statements in a different format. For example the output from the top_close.sql script above displays the close price in full precision, i.e. with 13 digits to the right of the decimal point, eg. 97.2399978637695. It would be more readable with 2 or 3 digits of precision. The next step in this tutorial will demonstrate different display options.