Lua eXtremeSQL Interface

Connections

A connection is used to access most of the database features. When a UDF is called, the database connection is already established as predefined variable con.

When a script or the console is used, it’s necessary to call function connect() to explicitly make a connection. The argument of the connect() function is a table with specified keys. This function can connect to local database, a remote RSQL database, or a distributed database depending on it’s arguments.

Examples:

The following code snippet creates a local in-memory (IM) database (in process ) and makes a connection to it.

     
    db = create("luadb", devices, dbparams)
    c = connect{db=db}
     

The following command will create an IM database named testdb with size of 100Mb and page size 512 bytes. (Parameter pagesize could be omitted as it has default value of 256 bytes).

 
    c = connect{dbname=”testdb”, dbsize=100*1024*1024, pagesize=512}
     

The following command will connect to remote xSQL server located on the same machine on port 5000.

     
    c = connect{host=”localhost”, port=5000}
     

The following command will create a distributed connection to 2 shards.

     
    c = connect{nodes={"localhost:5000", "localhost:5001"}}
     

The following additional parameters are supported for connections:

• nReplicas: The number of HA replics
• maxConnectAttempts: The number of times to attempt to connect
• connectTimeout: The timeout for connection attempts
• sql_login: Security login name
• sql_password: Security password

Running SQL queries

Once a connection is established, we can perform statements and queries using that connection. A statement does not return data from the database, while a query does.

The following is an example of statement:

 
    con:statement("create table MyTable (pk int, value string)")
     

This can be run from a UDF or as a script.

To make it possible to retrieve data from the query, the function con:query() returns a cursor object. Cursor objects allow iteration over their content, and return data represented as rows. The rows are Lua tables in the form key=value where key is the column name and value is the corresponding row value.

The following is an example of cursor iteration:

 
    for row in con:query("select pk,value from MyTable"):tuples() do
        return print row.pk, row.value
    end
     

Passing Parameters to an SQL Query

It is possible to pass parameters into a query or statement. The eXtremeSQL Lua interface is a thin layer on top of the eXtremeSQL C++ SQL API, and the LuaJIT FFI intreface is used to bind and pass parameters. All parameters of a query are passed "as is", without any checking or processing. Note the following rules regarding parameters:

• Parameters in the query are specified in the "%" form, exactly as in the C++ SQL API
• Parameter values are passed as FFI data types
• Passing parameters is unsafe. No extra checking is performed in eXtremeSQL layer and FFI translates parameters into low level C-compatible data and data structures. So, an error specifying parameters or parameter types will result in undefined behavior. Most likely it will cause a program (server) crash, but not necessarily. It could cause data corruption, data loss, etc.

The following is an example of passing parameters:

 
    for row in con:query("select k from t where i=%i", ffi.new("int", i)):tuples() do
        return row.k
    end
     

The supported format specifiers and corresponding FFI expressions are listed in the table below:

Format specifier	eXtremeDB data type	FFI expression
%b	bool	ffi.new("bool", true)
%i	int	ffi.new("int", 1)
%i8	int64_t	ffi.new("int64_t", 1000000000000)
%u	unsigned	ffi.new("unsigned", 2)
%l	int64_t	ffi.new("int64_t", 1000000000000)
%L	integer value, passed as double	Native Lua type, a lua value can be passed. 123456
%I1	Array of 1 byte integers. size_t,int8_t*	2 values expected – size and an array. Array is passed as zero-based, while lua array is 1-based, so actual size of allocated array should be size+1 ffi.new('size_t', 3), ffi.new('int8_t[4]', {1,2,3})
%I2	Array of 2-byte integers. size_t,int16_t*	Same as above. ffi.new('size_t', 3), ffi.new('int16_t[4]', {1,2,3})
%I4	Array of 4-byte integers. size_t,int32_t*	Same as above. ffi.new('size_t', 3), ffi.new('int32_t[4]', {1,2,3})
%I8	Array of 8-byte integers. size_t,int64_t*	Same as above. ffi.new('size_t', 3), ffi.new('int64_t[4]', {1,2,3})
%U1	Array of 1-byte unsigned integers. size_t,uint8_t*	ffi.new('size_t', 3), ffi.new('uint8_t[4]', {1,2,3})
%U2	Array of 2-byte unsigned integers. size_t,uint16_t*	Same as above. ffi.new('size_t', 3), ffi.new('uint16_t[4]', {1,2,3})
%U4	Array of 4-byte unsigned integers. size_t,uint32_t*	Same as above. ffi.new('size_t', 3), ffi.new('uint32_t[4]', {1,2,3})
%U8	Array of 8-byte unsigned integers. size_t,uint64_t*	Same as above. ffi.new('size_t', 3), ffi.new('uint64_t[4]', {1,2,3})
%F4	Array of floats. size_t,float*	Same as above. ffi.new('size_t', 3), ffi.new('float[4]', {1,2,3})
%F8	Array of doubles. size_t,double*	Same as above. ffi.new('size_t', 3), ffi.new('double[4]', {1,2,3})
%A	size_t nElems, size_t elemSize, char* body
%f	double	ffi.new("double", 10.1) or just Lua numeric value – 10.1
%t	mco_datetime. This is an internal representation of datetime, represented as uint64_t	ffi.new("uint64_t", ticks)
%s	char*, but Lua string is properly converted	'hello world'

How to query a row

Function query_row() returns a table with a single row as a result set.

Syntax

     
    row = con:query_row (sql_query, params1, param2, ... paramN, no_data_hdl, too_many_rows_hdl)
				 

Example:

Assume that we have following table and data:

     
    create table Employee(
        name char(25) primary key,
        dept_no unsigned(2)
    );

    INSERT INTO Employee(name, dept_no) VALUES (['Luke Skywalker', 'Han Solo', 'Darth Vader'], [1,1,2])
     

we can write the following Lua code:

 
    local row = con:query_row("select name, dept_no from Employee order by name limit 1")
    local name, dept_no = row.name, row.dept_no

    print ("Values returned: ", name, dept_no)
     

and this code will print:

 
    Values returned:    Darth Vader	2
				 

Note: This function is designed specifically for situations when it is needed to fetch exactly one row. So, if the query returns more than one row or no rows query_row() will try to call the handler passed via the too_many_rows_hdl or no_data_hdl optional parameters respectively. If the corresponding handler is not provided then lua_error will be called (refer to pcall() for details).

 
	local row = con:query_row(
	"select name, dept_no from Employee order by name limit 1", 
	-- No data
	function ( )
	.....
	end,
	-- TOO_MANY_ROWS
	function ()
	.....
	end
	)