# 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