SQL PLUS LUA – Adding Tarantool/NoSQL to Tarantool/SQL

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

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.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')

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')

box.schema.func.drop('_VIEWS_DEFINITION',{if_exists = true})
box.schema.func.create('_VIEWS_DEFINITION',
    {language = 'LUA',
     returns = 'string',
     body = [[function (flags)
              -- If Tarantool version < 2.10.1, replace next line with
              -- return require('msgpack').decode(flags).sql
              return flags.sql
              end]],
     -- If Tarantool version < 2.10.1, replace next line with
     -- param_list = {'string'},
     param_list = {'map'},
     exports = {'LUA', 'SQL'},
     is_sandboxed = false,
     setuid = false,
     is_deterministic = false})
box.schema.role.grant('public', 'execute', 'function', '_VIEWS_DEFINITION')
pcall(function ()
    box.schema.role.revoke('public', 'read', 'space', '_VIEWS', {if_exists = true})
    end)
box.execute([[DROP VIEW IF EXISTS _views;]])
box.execute([[
CREATE VIEW _views AS SELECT
    CAST(NULL AS STRING) AS table_catalog,
    CAST(NULL AS STRING) AS table_schema,
    "name" AS table_name,
    CAST(_views_definition("flags") AS STRING) AS VIEW_DEFINITION,
    "id" AS id,
    (SELECT "name" FROM "_vuser" x WHERE x."id" = y."owner") AS owner,
    "field_count" AS field_count
    FROM "_vspace" y
    WHERE _tables_is_view("flags") = TRUE;
]])
box.schema.role.grant('public', 'read', 'space', '_VIEWS')

box.schema.func.drop('_TRIGGERS_OPTS_SQL',{if_exists = true})
box.schema.func.create('_TRIGGERS_OPTS_SQL',
    {language = 'LUA',
     returns = 'string',
     body = [[function (opts)
              -- If Tarantool version < 2.10.1, replace next line with
              -- return require('msgpack').decode(opts).sql
              return opts.sql
              end]],
     -- If Tarantool version < 2.10.1, replace next line with
     -- param_list = {'string'},
     param_list = {'map'},
     exports = {'LUA', 'SQL'},
     is_sandboxed = false,
     setuid = false,
     is_deterministic = false})
box.schema.role.grant('public', 'execute', 'function', '_TRIGGERS_OPTS_SQL')
pcall(function ()
    box.schema.role.revoke('public', 'read', 'space', '_TRIGGERS', {if_exists = true})
    end)
box.execute([[DROP VIEW IF EXISTS _triggers;]])
box.execute([[
CREATE VIEW _triggers AS SELECT
    CAST(NULL AS STRING) AS trigger_catalog,
    CAST(NULL AS STRING) AS trigger_schema,
    "name" AS trigger_name,
    CAST(_triggers_opts_sql("opts") AS STRING) AS opts_sql,
    "space_id" AS space_id
    FROM "_trigger";
]])
box.schema.role.grant('public', 'read', 'space', '_TRIGGERS')

pcall(function ()
    box.schema.role.revoke('public', 'read', 'space', '_REFERENTIAL_CONSTRAINTS', {if_exists = true})
    end)
box.execute([[DROP VIEW IF EXISTS _referential_constraints;]])
box.execute([[
CREATE VIEW _referential_constraints AS SELECT
    CAST(NULL AS STRING) AS constraint_catalog,
    CAST(NULL AS STRING) AS constraint_schema,
    "name" AS constraint_name,
    CAST(NULL AS STRING) AS unique_constraint_catalog,
    CAST(NULL AS STRING) AS unique_constraint_schema,
    '' AS unique_constraint_name,
    "on_update" AS update_rule,
    "on_delete" AS delete_rule,
    "match" AS match_option,
    (SELECT "name" FROM "_vspace" x WHERE x."id" = y."child_id") AS referencing,
    (SELECT "name" FROM "_vspace" x WHERE x."id" = y."parent_id") AS referenced,
    "is_deferred" AS is_deferred,
    "child_id" AS child_id,
    "parent_id" AS parent_id
    FROM "_fk_constraint" y;
]])
box.schema.role.grant('public', 'read', 'space', '_REFERENTIAL_CONSTRAINTS')

pcall(function ()
    box.schema.role.revoke('public', 'read', 'space', '_CHECK_CONSTRAINTS', {if_exists = true})
    end)
box.execute([[DROP VIEW IF EXISTS _check_constraints;]])
box.execute([[
CREATE VIEW _check_constraints AS SELECT
    CAST(NULL AS STRING) AS constraint_catalog,
    CAST(NULL AS STRING) AS constraint_schema,
    "name" AS constraint_name,
    "code" AS check_clause,
    (SELECT "name" FROM "_vspace" x WHERE x."id" = y."space_id") AS space_name,
    "language" AS language,
    "is_deferred" AS is_deferred,
    "space_id" AS space_id
    FROM "_ck_constraint" y;
]])
box.schema.role.grant('public', 'read', 'space', '_CHECK_CONSTRAINTS')

box.schema.func.drop('_TABLE_CONSTRAINTS_OPTS_UNIQUE',{if_exists = true})
function _TABLE_CONSTRAINTS_OPTS_UNIQUE (opts) return require('msgpack').decode(opts).unique end
box.schema.func.create('_TABLE_CONSTRAINTS_OPTS_UNIQUE',
    {language = 'LUA',
     returns = 'boolean',
     body = [[function (opts) return require('msgpack').decode(opts).unique end]],
     param_list = {'string'},
     exports = {'LUA', 'SQL'},
     is_sandboxed = false,
     setuid = false,
     is_deterministic = false})
box.schema.role.grant('public', 'execute', 'function', '_TABLE_CONSTRAINTS_OPTS_UNIQUE')
pcall(function ()
box.schema.role.revoke('public', 'read', 'space', '_TABLE_CONSTRAINTS', {if_exists = true})
end)
box.execute([[DROP VIEW IF EXISTS _table_constraints;]])
box.execute([[
CREATE VIEW _table_constraints AS SELECT
CAST(NULL AS STRING) AS constraint_catalog,
CAST(NULL AS STRING) AS constraint_schema,
"name" AS constraint_name,
(SELECT "name" FROM "_vspace" x WHERE x."id" = y."id") AS table_name,
CASE WHEN "iid" = 0 THEN 'PRIMARY' ELSE 'UNIQUE' END AS constraint_type,
CAST(NULL AS STRING) AS initially_deferrable,
CAST(NULL AS STRING) AS deferred,
CAST(NULL AS STRING) AS enforced,
"id" AS id,
"iid" AS iid,
"type" AS index_type
FROM "_vindex" y
WHERE _table_constraints_opts_unique("opts") = TRUE;
]])
box.schema.role.grant('public', 'read', 'space', '_TABLE_CONSTRAINTS')