Zen

Translation

English
box.execute([[CREATE TABLE tester (s1 INTEGER PRIMARY KEY, s2 STRING);]])

function string_function()
local random_number
local random_string
random_string = ""
for x = 1,10,1 do
random_number = math.random(65, 90)
random_string = random_string .. string.char(random_number)
end
return random_string
end

function main_function()
local string_value, t, sql_statement
for i = 1,1000000, 1 do
string_value = string_function()
sql_statement = "INSERT INTO tester VALUES (" .. i .. ",'" .. string_value .. "');"
box.execute(sql_statement)
end
end
start_time = os.clock()
main_function()
end_time = os.clock()
'insert done in ' .. end_time - start_time .. ' seconds'
705/7050 · 705
English Russian Actions
For example this will show the number of seconds since the epoch: |br| ``box.execute([[SELECT lua('return os.time()');]])`` |br| For example this will show a database configuration member: |br| ``box.execute([[SELECT lua('return box.cfg.memtx_memory');]])`` |br| For example this will return FALSE because Lua nil and box.NULL are the same as SQL NULL: |br| ``box.execute([[SELECT lua('return box.NULL') IS NOT NULL;]])``
 
Warning: the SQL statement must not invoke a Lua function, or execute a Lua chunk, that accesses a space that underlies any SQL table that the SQL statement accesses. For example, if function ``f()`` contains a request ``"box.space.TEST:insert{0}"``, then the SQL statement ``"SELECT f() FROM test;"`` will try to access the same space in two ways. The results of such conflict may include a hang or an infinite loop.
 
Example Sessions
 
Example Session -- Create, Insert, Select
 
Assume that the task is to create two tables, put some rows in each table, create a :ref:`view <sql_create_view>` that is based on a join of the tables, then select from the view all rows where the second column values are not null, ordered by the first column.
 
That is, the way to populate the table is |br| ``CREATE TABLE t1 (c1 INTEGER PRIMARY KEY, c2 STRING);`` |br| ``CREATE TABLE t2 (c1 INTEGER PRIMARY KEY, x2 STRING);`` |br| ``INSERT INTO t1 VALUES (1, 'A'), (2, 'B'), (3, 'C');`` |br| ``INSERT INTO t1 VALUES (4, 'D'), (5, 'E'), (6, 'F');`` |br| ``INSERT INTO t2 VALUES (1, 'C'), (4, 'A'), (6, NULL);`` |br| ``CREATE VIEW v AS SELECT * FROM t1 NATURAL JOIN t2;`` |br| ``SELECT * FROM v WHERE c2 IS NOT NULL ORDER BY c1;``
 
So the session looks like this: |br| ``box.cfg{}`` |br| ``box.execute([[CREATE TABLE t1 (c1 INTEGER PRIMARY KEY, c2 STRING);]])`` |br| ``box.execute([[CREATE TABLE t2 (c1 INTEGER PRIMARY KEY, x2 STRING);]])`` |br| ``box.execute([[INSERT INTO t1 VALUES (1, 'A'), (2, 'B'), (3, 'C');]])`` |br| ``box.execute([[INSERT INTO t1 VALUES (4, 'D'), (5, 'E'), (6, 'F');]])`` |br| ``box.execute([[INSERT INTO t2 VALUES (1, 'C'), (4, 'A'), (6, NULL);]])`` |br| ``box.execute([[CREATE VIEW v AS SELECT * FROM t1 NATURAL JOIN t2;]])`` |br| ``box.execute([[SELECT * FROM v WHERE c2 IS NOT NULL ORDER BY c1;]])``
 
If one executes the above requests with Tarantool as a client, provided the database objects do not already exist, the execution will be successful and the final display will be
 
tarantool> box.execute([[SELECT * FROM v WHERE c2 IS NOT NULL ORDER BY c1;]])
---
- - [1, 'A', 'C']
- [4, 'D', 'A']
- [6, 'F', null]
 
Example Session -- Get a List of Columns
 
Here is a function which will create a table that contains a list of all the columns and their Lua types, for all tables. It is not a necessary function because one can create a :ref:`_COLUMNS view <sql__columns_view>` instead. It merely shows, with simpler Lua code, how to make a base table instead of a view.
 
function create_information_schema_columns()
box.execute([[DROP TABLE IF EXISTS information_schema_columns;]])
box.execute([[CREATE TABLE information_schema_columns (
table_name STRING,
column_name STRING,
ordinal_position INTEGER,
data_type STRING,
PRIMARY KEY (table_name, column_name));]]);
local space = box.space._vspace:select()
local sqlstring = ''
for i = 1, #space do
for j = 1, #space[i][7] do
sqlstring = "INSERT INTO information_schema_columns VALUES ("
.. "'" .. space[i][3] .. "'"
.. ","
.. "'" .. space[i][7][j].name .. "'"
.. ","
.. j
.. ","
.. "'" .. space[i][7][j].type .. "'"
.. ");"
box.execute(sqlstring)
end
end
return
end
 
If you now execute the function by saying |br| ``create_information_schema_columns()`` |br| you will see that there is a table named information_schema_columns containing table_name and column_name and ordinal_position and data_type for everything that was accessible.
 
Example Session -- Million-Row Insert
 
Here is a variation of the Lua tutorial :ref:`"Insert one million tuples with a Lua stored procedure" <c_lua_tutorial-insert_one_million_tuples>`. The differences are: the creation is done with an SQL :ref:`CREATE TABLE statement <sql_create_table>`, and the inserting is done with an SQL :ref:`INSERT statement <sql_insert>`. Otherwise, it is the same. It is the same because Lua and SQL are compatible, just as Lua and NoSQL are compatible.
 
box.execute([[CREATE TABLE tester (s1 INTEGER PRIMARY KEY, s2 STRING);]])

function string_function()
local random_number
local random_string
random_string = ""
for x = 1,10,1 do
random_number = math.random(65, 90)
random_string = random_string .. string.char(random_number)
end
return random_string
end

function main_function()
local string_value, t, sql_statement
for i = 1,1000000, 1 do
string_value = string_function()
sql_statement = "INSERT INTO tester VALUES (" .. i .. ",'" .. string_value .. "');"
box.execute(sql_statement)
end
end
start_time = os.clock()
main_function()
end_time = os.clock()
'insert done in ' .. end_time - start_time .. ' seconds'
box.execute([[CREATE TABLE tester (s1 INTEGER PRIMARY KEY, s2 STRING);]])

function string_function()
local random_number
local random_string
random_string = ""
for x = 1,10,1 do
random_number = math.random(65, 90)
random_string = random_string .. string.char(random_number)
end
return random_string
end

function main_function()
local string_value, t, sql_statement
for i = 1,1000000, 1 do
string_value = string_function()
sql_statement = "INSERT INTO tester VALUES (" .. i .. ",'" .. string_value .. "');"
box.execute(sql_statement)
end
end
start_time = os.clock()
main_function()
end_time = os.clock()
'insert done in ' .. end_time - start_time .. ' seconds'
Limitations: The function takes more time than the original (Tarantool/NoSQL).
 
Lua functions to make views of metadata
 
Tarantool does not include all the standard-SQL `information_schema <https://en.wikipedia.org/wiki/information_schema>`_ views, which are for looking at metadata, that is, "data about the data". But here is the Lua code and SQL code for creating equivalents: |br| :ref:`_TABLES <sql__tables_view>` nearly equivalent to INFORMATION_SCHEMA.TABLES |br| :ref:`_COLUMNS <sql__columns_view>` nearly equivalent to INFORMATION_SCHEMA.COLUMNS |br| :ref:`_VIEWS <sql__views_view>` nearly equivalent to INFORMATION_SCHEMA.VIEWS |br| :ref:`_TRIGGERS <sql__triggers_view>` nearly equivalent to INFORMATION_SCHEMA.TRIGGERS |br| :ref:`_REFERENTIAL_CONSTRAINTS <sql__referential_constraints_view>` nearly equivalent to INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS |br| :ref:`_CHECK_CONSTRAINTS <sql__check_constraints_view>` nearly equivalent to INFORMATION_SCHEMA.CHECK_CONSTRAINTS |br| :ref:`_TABLE_CONSTRAINTS <sql__table_constraints_view>` nearly equivalent to INFORMATION_SCHEMA.TABLE_CONSTRAINTS. |br| For each view there will be an example of a SELECT from the view, and the code. Users who want metadata can simply copy the code. Use this code only with Tarantool version 2.3.0 or later. With an earlier Tarantool version, a :ref:`PRAGMA statement <sql_pragma>` may be useful.
 
_TABLES view
 
Example:
Пример:
tarantool>SELECT * FROM _tables WHERE id > 340 LIMIT 5;
OK 5 rows selected (0.0 seconds)
+---------------+--------------+----------------+------------+-----+--------+-------+-------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ID | ENGINE | OWNER | FIELD_COUNT |
+---------------+--------------+----------------+------------+-----+--------+-------+-------------+
| NULL | NULL | _fk_constraint | BASE TABLE | 356 | memtx | admin | 0 |
| NULL | NULL | _ck_constraint | BASE TABLE | 364 | memtx | admin | 0 |
| NULL | NULL | _func_index | BASE TABLE | 372 | memtx | admin | 0 |
| NULL | NULL | _COLUMNS | VIEW | 513 | memtx | admin | 8 |
| NULL | NULL | _VIEWS | VIEW | 514 | memtx | admin | 7 |
+---------------+--------------+----------------+------------+-----+--------+-------+-------------+
 
Definition of the function and the CREATE VIEW statement:
 
box.schema.func.drop('_TABLES_IS_VIEW',{if_exists = true})
box.schema.func.create('_TABLES_IS_VIEW',
{language = 'LUA',
returns = 'boolean',
body = [[function (flags)
local view
-- If Tarantool version < 2.10.1, replace next line with
-- view = require('msgpack').decode(flags).view
view = flags.view
if view == nil then return false end
return view
end]],
is_sandboxed = false,
-- If Tarantool version < 2.10.1, replace next line with
-- param_list = {'string'},
param_list = {'map'},
exports = {'LUA', 'SQL'},
is_deterministic = true})
box.schema.role.grant('public', 'execute', 'function', '_TABLES_IS_VIEW')
pcall(function ()
box.schema.role.revoke('public', 'read', 'space', '_TABLES', {if_exists = true})
end)
box.execute([[DROP VIEW IF EXISTS _tables;]])
box.execute([[
CREATE VIEW _tables AS SELECT
CAST(NULL AS STRING) AS table_catalog,
CAST(NULL AS STRING) AS table_schema,
"name" AS table_name,
CASE
WHEN _tables_is_view("flags") = TRUE THEN 'VIEW'
ELSE 'BASE TABLE' END
AS table_type,
"id" AS id,
"engine" AS engine,
(SELECT "name" FROM "_vuser" x WHERE x."id" = y."owner") AS owner,
"field_count" AS field_count
FROM "_vspace" y;
]])
box.schema.role.grant('public', 'read', 'space', '_TABLES')
 
_COLUMNS view
 
This is also an example of how one can use :ref:`recursive views <sql_with>` to make temporary tables with multiple rows for each tuple in the original ``"_vspace"`` space. It requires a global variable, _G.box.FORMATS, as a temporary static variable.
Этот пример также показывает, как с помощью :ref:`рекурсивных представлений <sql_with>` создавать временные таблицы с несколькими строками для каждого кортежа в исходном спейсе ``"_vspace"``. Для этого потребуется глобальная переменная ``_G.box.FORMATS`` в качестве временной статической переменной.
Warning: Use this code only with Tarantool version 2.3.2 or later. Use with earlier versions will cause an assertion. See `Issue#4504 <https://github.com/tarantool/tarantool/issues/4504>`_.
 
tarantool>SELECT * FROM _columns WHERE ordinal_position = 9;
OK 6 rows selected (0.0 seconds)
+--------------+-------------+--------------------------+--------------+------------------+-------------+-----------+-----+
| CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | IS_NULLABLE | DATA_TYPE | ID |
+--------------+-------------+--------------------------+--------------+------------------+-------------+-----------+-----+
| NULL | NULL | _sequence | cycle | 9 | YES | boolean | 284 |
| NULL | NULL | _vsequence | cycle | 9 | YES | boolean | 286 |
| NULL | NULL | _func | returns | 9 YES string | 296 |
| NULL | NULL | _fk_constraint | parent_cols | 9 | YES | array | 356 |
| NULL | NULL | _REFERENTIAL_CONSTRAINTS | MATCH_OPTION | 9 | YES | string | 518 |
+--------------+-------------+--------------------------+--------------+------------------+-------------+-----------+-----+
 
box.schema.func.drop('_COLUMNS_FORMATS', {if_exists = true})
box.schema.func.create('_COLUMNS_FORMATS',
{language = 'LUA',
returns = 'scalar',
body = [[
function (row_number_, ordinal_position)
if row_number_ == 0 then
_G.box.FORMATS = {}
local vspace = box.space._vspace:select()
for i = 1, #vspace do
local format = vspace[i]["format"]
for j = 1, #format do
local is_nullable = 'YES'
if format[j].is_nullable == false then
is_nullable = 'NO'
end
table.insert(_G.box.FORMATS,
{vspace[i].name, format[j].name, j,
is_nullable, format[j].type, vspace[i].id})
end
end
return ''
end
if row_number_ > #_G.box.FORMATS then
_G.box.FORMATS = {}
return ''
end
return _G.box.FORMATS[row_number_][ordinal_position]
end
]],
param_list = {'integer', 'integer'},
exports = {'LUA', 'SQL'},
is_sandboxed = false,
setuid = false,
is_deterministic = false})
box.schema.role.grant('public', 'execute', 'function', '_COLUMNS_FORMATS')

pcall(function ()
box.schema.role.revoke('public', 'read', 'space', '_COLUMNS', {if_exists = true})
end)
box.execute([[DROP VIEW IF EXISTS _columns;]])
box.execute([[
CREATE VIEW _columns AS
WITH RECURSIVE r_columns AS
(
SELECT 0 AS row_number_,
'' AS table_name,
'' AS column_name,
0 AS ordinal_position,
'' AS is_nullable,
'' AS data_type,
0 AS id
UNION ALL
SELECT row_number_ + 1 AS row_number_,
_columns_formats(row_number_, 1) AS table_name,
_columns_formats(row_number_, 2) AS column_name,
_columns_formats(row_number_, 3) AS ordinal_position,
_columns_formats(row_number_, 4) AS is_nullable,
_columns_formats(row_number_, 5) AS data_type,
_columns_formats(row_number_, 6) AS id
FROM r_columns
WHERE row_number_ == 0 OR row_number_ <= lua('return #_G.box.FORMATS + 1')
)
SELECT CAST(NULL AS STRING) AS catalog_name,
CAST(NULL AS STRING) AS schema_name,
table_name,
column_name,
ordinal_position,
is_nullable,
data_type,
id
FROM r_columns
WHERE data_type <> '';
]])
box.schema.role.grant('public', 'read', 'space', '_COLUMNS')
 
_VIEWS view
 
tarantool>SELECT table_name, substr(view_definition,1,20), id, owner, field_count FROM _views LIMIT 5;
OK 5 rows selected (0.0 seconds)
+--------------------------+------------------------------+-----+-------+-------------+
| TABLE_NAME | SUBSTR(VIEW_DEFINITION,1,20) | ID | OWNER | FIELD_COUNT |
+--------------------------+------------------------------+-----+-------+-------------+
| _COLUMNS | CREATE VIEW _columns | 513 | admin | 8 |
| _TRIGGERS | CREATE VIEW _trigger | 515 | admin | 4 |
| _CHECK_CONSTRAINTS | CREATE VIEW _check_c | 517 | admin | 8 |
| _REFERENTIAL_CONSTRAINTS | CREATE VIEW _referen | 518 | admin | 12 |
| _TABLE_CONSTRAINTS | CREATE VIEW _table_c | 519 | admin | 11 |
+--------------------------+------------------------------+-----+-------+-------------+
 

Loading…

User avatar None

String updated in the repository

testing_project / Documentation: reference/reference_sql/sql_plus_lua — Russian

box.execute([[CREATE TABLE tester (s1 INTEGER PRIMARY KEY, s2 STRING);]])

function string_function()
local random_number
local random_string
random_string = ""
for x = 1,10,1 do
random_number = math.random(65, 90)
random_string = random_string .. string.char(random_number)
end
return random_string
end

function main_function()
local string_value, t, sql_statement
for i = 1,1000000, 1 do
string_value = string_function()
sql_statement = "INSERT INTO tester VALUES (" .. i .. ",'" .. string_value .. "');"
box.execute(sql_statement)
end
end
start_time = os.clock()
main_function()
end_time = os.clock()
'insert done in ' .. end_time - start_time .. ' seconds'
box.execute([[CREATE TABLE tester (s1 INTEGER PRIMARY KEY, s2 STRING);]])

function string_function()
local random_number
local random_string
random_string = ""
for x = 1,10,1 do
random_number = math.random(65, 90)
random_string = random_string .. string.char(random_number)
end
return random_string
end

function main_function()
local string_value, t, sql_statement
for i = 1,1000000, 1 do
string_value = string_function()
sql_statement = "INSERT INTO tester VALUES (" .. i .. ",'" .. string_value .. "');"
box.execute(sql_statement)
end
end
start_time = os.clock()
main_function()
end_time = os.clock()
'insert done in ' .. end_time - start_time .. ' seconds'
a year ago
Browse all string changes

Things to check

Unchanged translation

Source and translation are identical

Reset

Glossary

English Russian
No related strings found in the glossary.

String information

String age
a year ago
Last updated
a year ago
Source string age
a year ago
Translation file
locale/ru/LC_MESSAGES/reference/reference_sql/sql_plus_lua.po, string 71