ToC path:

Lua API Reference

The eXtremeDB Lua API consists of the functions and classes described in the following sections. (Please see the Lua Reference Manual for detailed information on the Lua language.)

Functions

The Lua functions are described in the following sections.

initRuntime()
This function loads and initializes eXtremeDB runtime.
 
  function initRuntime(t)
 
Parameters

t

A hash table with the possible keys defined below

Possible keys

disk

Boolean, defines if persistent runtime should be loaded. (Default is false, which means load IM runtime.)

tmgr

String, which transaction manager should be used. Possible values are 'mvcc', 'mursiw'. (Default value is 'mursiw')

shm

Boolean, specifies if shared memory support should be enabled. (Default value is false.)

debug

Boolean, specifies if debug libraries should be loaded. (Default value is false.)

cluster

Boolean, specifies if cluster support should be loaded. (Default value is false.)

ha

Boolean, specifies if HA support should be loaded. (Default value is false.)

disk_compression

Boolean, specifies if disk compression library should be loaded. (Default value is true.)

posix

Boolean, specifies if posix synchronization primitives should be loaded. (Default value is true.)
create()
This function loads and initializes the eXtremeDB runtime.
   
  function create(dbname, devs, params, schema, persistent, debug, 
           nosort, largeDatabase, compact, transient)
            
Parameters:

dbname

A string, name of the database.

devs

A list of tables, defines database devices. Each database device is described with table, as follows:

type
The type of the device. Possible values are: conv, named, file, multifile, raid.

conv Conventional memory device. Creates a device in conventional memory. In this case expected keys and values are: size, flags

named Shared memory device: Creates a device in shared memory. Expected values are: size, name, flags, hint

file This key has the same meaning as in the mco_device_t structure.

multifile This key has the same meaning as in the mco_device_t structure.

raid This key has the same meaning as in the mco_device_t structure.

size The size of the memory region. (The memory region will be created.)

name For type=named: the name of the shared memory region.

flags A bit mask of flags for device. These are the same flags as for C API mco_device_t structure.

hint For type=named: the same parameter as for the mco_device_t structure. It can be a desired address to map shared memory region or nil to map region to any possible address.

params

A table having the same keys with the same meaning as the corresponding fields in the db_params C API structure

schema

A string containing the database schema to pass to the mcocomp compiler. Can be nil if a schema is not used, e.g. for using a dynamic schema with SQL or to load a schema from a persistent database.

persistent

A Boolean parameter defining if the schema is to be compiled for a persistent database. (Please refer to the mcocomp page for more details.)

debug

A Boolean parameter defining if the schema is to be compiled with the debug flag. (Please refer to the mcocomp page for more details.)

nosort

A Boolean parameter specifying if the table (class) fields should be sorted. (Please refer to the mcocomp page for more details.)

largeDatabase

A Boolean parameter specifying that large database support is enabled. . (Please refer to the mcocomp page for more details.)

compact

A Boolean parameter specifying that compact object layout is used. (Please refer to the mcocomp page for more details.)

transient

A Boolean parameter defining if the schema is to be compiled for a transient (in-memory) database. (Please refer to the mcocomp page for more details.)
connect()
This function connects database engine. It returns an instance of the SqlEngine class which represents a connection; it behaves like a connection and should be treated as a connection. The parameters determine whether the SqlEngine is connected to a Local Database, as a Remote Client or Distributed Client.
 
  function connect( params )
 
Parameters

params

A table; for different scenarios a different set of keys must be set according to the usage scenarios described below

Usage Scenarios

The SqlEngine can connect to a local or remote database depending on the parameters specified.

Local Database

Create local in-process database and connect the local engine by specifying the following keys:

dbname

A string, name of the database.

dbsize

String, which transaction manager should be used. Possible values are 'mvcc', 'mursiw'. (Default value is 'mursiw')

pagesize

Boolean, specifies if shared memory support should be enabled. (Default value is false.)

Example:
 
    c = connect({dbname='testdb', pagesize=1024, dbsize=1024*1024*1024})
    print ('Connection is:', c)
     
Remote RSQL Client

Connect to a remote xSQL server using RSQL protocol by specifying the following keys:

host

A mandatory string specifying the host IP address.

port

The port number to connect to

sql_login

An optional string: the login username

sql_password

An optional string: the login password

ssl_params

An optional FFI-defined structure: the keys correspond to the C API structure mco_ssl_params_t

Example:
     
    sslparams = ffi.new('mco_ssl_params_t')
    ssl_params_init(sslparams)
    sslparams.verify_mode = bit.bor(SSLVerifyMode.VerifyPeer, SSLVerifyMode.VerifyFailIfNoPeerCert)
    sslparams.certificate_file = "../../thlib/certs/client.crt"
    sslparams.certificate_file_type = 1 -- SSL_FILETYPE_PEM / X509_FILETYPE_PEM
    sslparams.private_key_file = "../../thlib/certs/client.key"
    sslparams.private_key_file_type = 1 -- SSL_FILETYPE_PEM / X509_FILETYPE_PEM
     
    conn = connect{host='localhost', port=5010, sql_login=’john’, sql_password=’run_like_hell’, ssl_params=sslparams}
                
     
Distributed RSQL Client

Open several remote servers (shards) and create a distributed client by specifying the following keys:

nodes

A mandatory string specifying the array of nodes to create: each node specification consists of an IP address and port in the form "<address>:port". For example "localhost:5001".

sql_login

An optional string: the login username

sql_password

An optional string: the login password

ssl_params

An optional FFI-defined structure: the keys correspond to the C API structure mco_ssl_params_t

Example:
 
    sslparams = ffi.new('mco_ssl_params_t')
    ssl_params_init(sslparams)
    sslparams.verify_mode = bit.bor(SSLVerifyMode.VerifyPeer, SSLVerifyMode.VerifyFailIfNoPeerCert)
    sslparams.certificate_file = "../../thlib/certs/client.crt"
    sslparams.certificate_file_type = 1 -- SSL_FILETYPE_PEM / X509_FILETYPE_PEM
    sslparams.private_key_file = "../../thlib/certs/client.key"
    sslparams.private_key_file_type = 1 -- SSL_FILETYPE_PEM / X509_FILETYPE_PEM
     
    conn = connect{ nodes={"localhost:5010", "localhost:5011"},
                
             sql_login='user2',
             sql_password='hYfjdKK2',
             ssl_params=sslparams}
     
RSQL Client with Compression enabled

Open a remote SQL client with the maximum compression level
 
    c = connect({host='localhost', port=5010, sql_login=’john’, compressionLevel=9})
     
iseq()
This function takes a sequence handle argument and creates an iterator.
 
  function iseq(it)
 
Parameters

it

A sequence iterator

Example:
 
    for el in iseq(seqit) do
        print (el)
    end
     
isenq()
This function takes a number of sequence handles and creates an iterator which will return values for all of the sequences. The sequences will be iterated simultaneously, so each next() call will take one element from all the sequences. Sequences must have equal number of arguments. End of iteration is determined by the first element.
 
    funtion iseqn(it1, … itN)
     
Example:
 
    for price,vol in iseqn(priceit, volit) do
        print (price, vol)
    end
     
ResetLuaUDFs()
Sometimes it may be necessary to change the source code of a UDF. The Lua UDF is compiled at the time of its first run and then this compiled object is kept in the Lua interpreter. So any changes to the UDF source code will not actually be active; the SQL engine will continue to execute the old version.

This function will reset Lua's table of loaded compiled functions so that on the next call the new version of the UDF will be compiled, allowing reloading without having to restart the SQL engine, or in the case of xSQL, restart the xSQL server. This will incur a performance penalty, but reloads Lua UDFs without the inconvenience of restarting the server.

The following code snippet shows how ResetLuaUDFs() can be called after changing the source code to UDF get_data3(). Note that it returns a different value after being reloaded:
 
    XSQL>select get_data3();
    #1
    ------------------------------------------------------------------------------
    50
    Selected records: 1
    XSQL>select ResetLuaUDFs();
    #1
    ------------------------------------------------------------------------------
    null
    Selected records: 1
    XSQL>select get_data3();
    #1
    ------------------------------------------------------------------------------
    51
    Selected records: 1
 
Classes

The Lua classes are described in the following sections.

SqlEngine
The SqlEngine class is used to execute SQL statements and queries as well as to call xSQL functions providing the following methods:
query()
Execute a SQL query and return a Cursor.
 
  query(sql, ...)
 
Parameters:

sql An SQL query string with %b (bool), %s (string), %L (integer), %f (real) placeholders for parameters

... A varying list of string parameters to be substituted

Example:
 
  engine:query("select * from Customer where id=%s", cust_id)
     
statement()
Execute an insert, update, delete SQL statement and return the number of affected rows.
 
  statement(sql, ...)
 
Parameters:

sql An SQL statement with %[b (bool), %s (string), %L (integer), %f (real) placeholders for parameters

... A varying list of string parameters to be substituted

Example:
 
  engine:statement("insert into Customer values (%s,%s,%s)", 
            "ABC123", "Honjuan Inc.", "Shengen, po.1234")
     
call
Call an xSQL function with specified arguments and return the result of the function.
 
  call(func,...)
 
Parameters:

func An SQL statement with %[b (bool), %s (string), %L (integer), %f (real) placeholders for parameters

... A varying list of string parameters to be substituted

Example:
 
  engine:call("seq_avg",price)
   
close
Close the current session.
 
  close()
 
Cursor
The Cursor class represents a database cursor. It is created and returned as the result of the SqlEngine:method query(). Cursor provides the following methods:
moveNext()

Move the cursor to next record. This function can be used instead of next() if record fields are accessed using Cursor:get*() methods. It returns true if there is next record, false otherwise.

next()

Get the next record. It returns the next record or nil if end of the result set is reached.

getColumnName( column )

Get the result column name.

Parameters:

column The zero-based column index of the column

getColumnType( column )

Get the result column type: It returns the column type as: 'void', 'int', 'real', 'string', 'bool', 'array', 'struct', 'binary', 'raw', 'sequence'

extract( dst, size )

Extract the record in a C structure (created using FFI).

Parameters:

dst The address of the structure (created using ffi.new)

size The size of the structure (ffi.sizeof)

getInt( column )
Get an integer column value.

Parameters:

column The zero-based column index of the column

Example:
     
  local i0 = cursor:getInt(0)
     
getReal( column )
Get a floating point column value.

Parameters:

column The zero-based column index of the column

Example:
 
  local r0 = cursor:getReal(0)
     
getString( column )

Get a string column value.

Parameters:

column The zero-based column index of the column

getBinary( column )

Get a binary column value.

Parameters:

column The zero-based column index of the column

get( column )

Get a column value.

Parameters:

column The zero-based column index of the column

getSequence( column )

Get a sequence column value. It returns a raw sequence that can be passed to xSQL sequence functions or converted to a sequence iterator using the Sequence.iterator function.

Parameters:

column The zero-based column index of the column

getBool( column )
Get a Boolean column value.

Parameters:

column The zero-based column index of the column

Example:
 
  local b0 = cursor:getBool(0)
     
numberOfColumns()

Get the number of columns in the result set.

getRow()

Get the current record as a Lua table.

close()

Close the cursor.

reset()

Reset the cursor.

lazy()
Setup a cursor to lazily extract columns. When lazy columns extraction is active, the cursor will extract only columns which are requested by the program (UDF or script). Columns that are not specified, will not be extracted. Lazy extraction will speed up execution.

Example:
 
  for t in con:query("select * from eqtrade"):lazy():tuples() do
    if t.price >= 10 then
      acc.sum_size = (acc.sum_size or 0) + t.size
      acc.sum_price = (acc.sum_price or 0) + t.price
      count = count + 1
    end
  end
   

sql	An SQL query string with %b (bool), %s (string), %L (integer), %f (real) placeholders for parameters
...	A varying list of string parameters to be substituted

sql	An SQL statement with %[b (bool), %s (string), %L (integer), %f (real) placeholders for parameters
...	A varying list of string parameters to be substituted

func	An SQL statement with %[b (bool), %s (string), %L (integer), %f (real) placeholders for parameters
...	A varying list of string parameters to be substituted

dst	The address of the structure (created using `ffi.new`)
size	The size of the structure (`ffi.sizeof`)