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 by simply typing xsql from directory target\bin:

 
    xsql
     
    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.

For example, the script in file compound.sql creates the simple table A and index idx, populates the table and performs a compound query and then executes the explain command to display the execution plan:

 
    create table A (a int, b int, c int, d int);
    create index idx on A (a, b, c);
    insert into A values (1,2,3,4);
    select * from A a1, A a2 where a1.a=a2.a and a1.c=a2.c;
    explain select * from A a1, A a2 where a1.a=a2.a and a1.c=a2.c;
     

The script can be executed in "batch" mode by starting xSQL with the following command:

 
    xsql -size 100m -b -f ..\..\samples\xsql\scripts\compound.sql
     

xSQL displays the following lines of output showing the results of the select statement and the explain command:

 
    a       b       c       d       a       b       c       d
    ------------------------------------------------------------------------------
    1       2       3       4       1       2       3       4
     
    Selected records: 1
    Plan
    ------------------------------------------------------------------------------
    INDEX-JOIN[3] with A using index A.idx(a, b, c) WHERE (Eq A.c A.c)
    .SEQ-SCAN[1] of table A
     
    Selected records: 1
     

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 could run the script and enter interactive mode with the following command:

 
    xsql -size 100m -i -f ..\..\samples\xsql\scripts\compound.sql
     

Now we can type an SQL select statement like the following to display the contents of table A:

 
    XSQL>select * from A;
    a       b       c       d
    ------------------------------------------------------------------------------
    1       2       3       4
     
    Selected records: 1
     

The script command

Script files can also be run from interactive mode using the script command. For example, the following command runs the script as.sql:

 
    XSQL>script ..\..\samples\xsql\scripts\as.sql
    bond.bond
    ------------------------------------------------------------------------------
    James Bond
     
    Selected records: 1
     

The file as.sql looks like this:

 
    create table bond (bond string);
    insert into bond values ('James Bond');
    select "bond" as "bond.bond" from bond;
     

The script command can also be used 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
    ------------------------------------------------------------------------------
    A       a       Int4
    A       b       Int4
    A       c       Int4
    A       d       Int4
    bond    bond    String
     
    Selected records: 5
     

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

 
    xsql -f ..\..\samples\xsql\scripts\outer.sql
     

Sample scripts directories

A number of sample SQL scripts and command files (shell scripts) are provided in the directory:

     
    eXtremeDB/samples/xsql/scripts 
     

These sample scripts demonstrate many specific uses of standard SQL and extended eXtremeSQL features.

Because xSQL automatically loads dynamic libraries located in eXtremeDB/target/bin.so it is recommended to run xSQL from the eXtremeDB/target/bin directory. (Alternatively the LD_LIBRARY_PATH environment variable can be exported (set) to indicate the directory from which to load dynamic libraries.) But, to simplify launching xSQL with these sample script files the Windows command file testxsql.bat (testxsql.sh in Unix-Linux packages) is provided. It eliminates the necessity to type a long relative directory path and invokes the specified script with the following lines:

 
    @echo off
    ..\..\..\target\bin\xsql.exe -c xsql.cfg -f %1.sql
     

For example, using this command file from directory eXtremeDB/samples/xsql/scripts (in a Windows console window), the following is a minimal command to run xSQL as a server and execute script array.sql:

 
    xsqltest array
     

or from a Linux console window:

 
    ./xsqltest.sh array
     

Running this command, xSQL comes up in server mode and displays the following output:

 
    a       v
    ------------------------------------------------------------------------------
    [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]  [one, two, three]
    [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] [abc, xyz]
    [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] []
     
    Selected records: 3
    press Enter to stop SQL server
     

We recommend experimenting with several of the samples provided.

It is often convenient to display output from select statements in a different format. The next step in this tutorial will demonstrate different display options.