Functions for SQL
The box
module contains some functions related to SQL:
box.schema.func.create
– for making Lua functions callable from SQL statements. See Calling Lua routines from SQL in the SQL Plus Lua section.box.execute
– for making SQL statements callable from Lua functions. See the SQL user guide.box.prepare
andbox.unprepare
.
Some SQL statements are illustrated in the SQL tutorial.
-
box.
execute
(sql-statement[, extra-parameters]) Execute the SQL statement contained in the sql-statement parameter.
Параметры: - sql-statement (string) – statement, which should conform to the rules for SQL grammar
- extra-parameters (table) – optional table for placeholders in the statement
возвращается: depends on statement
There are two ways to pass extra parameters for
box.execute()
:- The first way, which is the preferred way, is to put placeholders in the string,
and pass a second argument, an extra-parameters table.
A placeholder is either a question mark «?», or a colon «:» followed by a name.
An extra parameter is any Lua expression.
If placeholders are question marks, then they will be replaced by extra-parameter
values in corresponding positions, that is, the first ? will be replaced by the first
extra parameter, the second ? will be replaced by the second extra parameter, and so on.
If placeholders are :names, then they will be replaced by extra-parameter
values with corrresponding names.
For example this request which contains literal values 1 and „x“:
box.execute([[INSERT INTO tt VALUES (1, 'x');]]);
is the same as this request which contains two question-mark placeholders (?
and?
) and a two-element extra-parameters table:
x = {1,'x'}
box.execute([[INSERT INTO tt VALUES (?, ?);]], x);
and is the same as this request which contains two :name placeholders (:a
and:b
) and a two-element extra-parameters table with elements named «a» and «b»:
box.execute([[INSERT INTO tt VALUES (:a, :b);]], {{[':a']=1},{[':b']='x'}})
- The second way is to concatenate strings.
For example, this Lua script will insert 10 rows with different primary-key
values into table t:
for i=1,10,1 do
box.execute("insert into t values (" .. i .. ")")
end
When creating SQL statements based on user input, application developers should beware of SQL injection.
Since
box.execute()
is an invocation of a Lua function, it either causes an error message or returns a value.For some statements the returned value will contain a field named «rowcount». For example;
tarantool> box.execute([[CREATE TABLE table1 (column1 INT PRIMARY key, column2 VARCHAR(10));]]) --- - rowcount: 1 ... tarantool> box.execute([[INSERT INTO table1 VALUES (55,'Hello SQL world!');]]) --- - rowcount: 1 ...
For statements that cause generation of values for PRIMARY KEY AUTOINCREMENT columns, there will also be a field named «autoincrement_ids».
For SELECT or PRAGMA statements, the returned value will be a result set, containing a field named «metadata» (a table with column names and Tarantool/NoSQL type names) and a field named «rows» (a table with the contents of each row).
For example, for a statement
SELECT "x" FROM t WHERE "x"=5;
where"x"
is an INTEGER column and there is one row, a display on the Tarantool client will look like this:tarantool> box.execute([[SELECT "x" FROM t WHERE "x"=5;]]) --- - metadata: - name: x type: integer rows: - [5] ...
The data for the result set, in MsgPack format, will look like this
dd 00 00 00 01 1-element array 82 2-element map (for metadata + rows) a8 6d 65 74 61 64 61 74 61 string = "metadata" 91 1-element array (for column count) 82 2-element map (for name + type) a4 6e 61 6d 65 string = "name" a1 78 string = "x" a4 74 79 70 6 string = "type" a7 69 6e 74 65 67 65 72 string = "integer" a4 72 6f 77 73 string = "rows" 91 1-element array (for row count) 91 1-element array (for field count) 05 contents
For a look at the raw format see Binary protocol – illustration.
The order of components within a map is not guaranteed.
If
sql_full_metadata
in the _session_settings system table is TRUE, then result set metadata may include these things in addition toname
andtype
:collation
(present only if COLLATE clause is specified for a STRING) = «Collation».is_nullable
(present only if the select list specified a base table column and nothing else) = false if column was defined as NOT NULL, otherwise true. If this is not present, that implies that nullability is unknown.is_autoincrement
(present only if the select list specified a base table column and nothing else) = true if column was defined as PRIMARY KEY AUTOINCREMENT, otherwise false.span
(always present) = the original expression in a select list, which will often be the same asname
if the select list specifies a column name and nothing else, but otherwise will differ, for example afterSELECT x+55 AS x FROM t;
thename
is X and thespan
is x+55. Ifspan
andname
are the same then the content is MP_NIL.
Alternative: if you are using the Tarantool server as a client, you can switch languages thus:
\set language sql \set delimiter ;
Afterwards, you can enter any SQL statement directly without needing
box.execute()
.There is also an
execute()
function available via module net.box, for example afterconn = net_box.connect(url-string)
one can sayconn:execute(sql-statement])
.
-
box.
prepare
(sql-statement) Prepare the SQL statement contained in the sql-statement parameter. The syntax and requirements for
box.prepare
are the same as for box.execute.Параметры: - sql-statement (string) – statement, which should conform to the rules for SQL grammar
возвращается: prepared_table, with id and methods and metadata
тип возвращаемого значения: таблица
box.prepare
compiles an SQL statement into byte code and saves the byte code in a cache. Since compiling takes a significant amount of time, preparing a statement will enhance performance if the statement is executed many times.If
box.prepare
succeeds, prepared_table contains:stmt_id
: integer – an identifier generated by a hash of the statement stringexecute
: functionparams
: map [name : string, type : string] – parameter descriptionsunprepare
: functionmetadata
: map [name : string, type : string] (This is present only for SELECT or PRAGMA statements and has the same contents as the result set metadata forbox.execute
)param_count
: integer – number of parameters
This can be used by prepared_table:execute() and by prepared_table:unprepare().
The prepared statement cache (which is also called the prepared statement holder) is «shared», that is, there is one cache for all sessions. However, session X cannot execute a statement prepared by session Y.
For monitoring the cache, see box.info().sql.
For changing the cache, see (Configuration reference) sql_cache_size.Prepared statements will «expire» (become invalid) if any database object is dropped or created or altered – even if the object is not mentioned in the SQL statement, even if the create or drop or alter is rolled back, even if the create or drop or alter is done in a different session.
-
object
prepared_table
-
Execute a statement that has been prepared with box.prepare().
Parameter
prepared_table
should be the result frombox.prepare()
.
Parameterextra-parameters
should be an optional table to match placeholders or named parameters in the statement.There are two ways to execute: with the method or with the statement id. That is,
prepared_table:execute()
andbox.execute(prepared_table.stmt_id)
do the same thing.Example: here is a test. This function inserts a million rows in a table using a prepared INSERT statement.
function f() local p, start_time box.execute([[DROP TABLE IF EXISTS t;]]) box.execute([[CREATE TABLE t (s1 INTEGER PRIMARY KEY);]]) start_time = os.time() p = box.prepare([[INSERT INTO t VALUES (?);]]) for i=1,1000000 do p:execute({i}) end p:unprepare() end_time = os.time() box.execute([[COMMIT;]]) print(end_time - start_time) -- elapsed time end f()
Take note of the elapsed time. Now change the line with the loop to:
for i=1,1000000 do box.execute([[INSERT INTO t VALUES (?);]], {i}) end
Run the function again, and take note of the elapsed time again. The function which executes the prepared statement will be about 15% faster, though of course this will vary depending on Tarantool version and environment.Undo the result of an earlier box.prepare() request. This is equivalent to standard-SQL DEALLOCATE PREPARE.
Parameter
prepared_table
should be the result frombox.prepare()
.There are two ways to unprepare: with the method or with the statement id. That is,
prepared_table:unprepare()
andbox.unprepare(prepared_table.stmt_id)
do the same thing.Tarantool strongly recommends using
unprepare
as soon as the immediate objective (executing a prepared statement multiple times) is done, or whenever a prepared statement expires. There is no automatic eviction policy, although automaticunprepare
will happen when the session disconnects (the session’s prepared statements will be removed from the prepared-statment cache).