Functions for SQL

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 and box.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 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
The second way is to put placeholder ? question-mark tokens inside the string, and pass a second argument extra-parameters, which must be a table containing values for each placeholder. For example these two requests are equivalent:
box.execute([[INSERT INTO tt VALUES (1, 'x');]]);
x = {1,'x'}; box.execute([[INSERT INTO tt VALUES (?, ?);]], x);
Or, put parameter names inside the string. A parameter name must start with : colon and must match a named component of the extra-parameters table. For example, this request is also equivalent:
box.execute([[INSERT INTO tt VALUES (:a, :b);]], {{[':a']=1},{[':b']='x'}})

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([[INSERT INTO tt VALUES (8,8),(9,9);]])
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 to name and type:

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 as name if the select list specifies a column name and nothing else, but otherwise will differ, for example after SELECT x+55 AS x FROM t; the name is X and the span is x+55. If span and name 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 after conn = net_box.connect(url-string) one can say conn: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 string
execute: function
params: map [name : string, type : string] – parameter descriptions
unprepare: function
metadata: map [name : string, type : string] (This is present only for SELECT or PRAGMA statements and has the same contents as the result set metadata for box.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¶

prepared_table:execute([extra-parameters])¶

Execute a statement that has been prepared with box.prepare().

Parameter prepared_table should be the result from box.prepare().
Parameter extra-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() and box.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.

prepared_table:unprepare()¶

Undo the result of an earlier box.prepare() request. This is equivalent to standard-SQL DEALLOCATE PREPARE.

Parameter prepared_table should be the result from box.prepare().

There are two ways to unprepare: with the method or with the statement id. That is, prepared_table:unprepare() and box.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 automatic unprepare will happen when the session disconnects (the session’s prepared statements will be removed from the prepared-statment cache).

Версия:

Functions for SQL

Functions for SQL¶