Re: [Tarantool-patches] [PATCH 2/2] sql: introduce SQL built-in function UUID()

[Mergen Imeev via Tarantool-patches <tarantool-patches@dev.tarantool.org>]

Hi! Sorry, I missed one fix in old tests. Fixed. Branch updated. Diff:

diff --git a/test/box-py/bootstrap.result b/test/box-py/bootstrap.result
index ed7accea3..b2328487c 100644
--- a/test/box-py/bootstrap.result
+++ b/test/box-py/bootstrap.result
@@ -4,7 +4,7 @@ box.internal.bootstrap()
 box.space._schema:select{}
 ---
 - - ['max_id', 511]
-  - ['version', 2, 7, 1]
+  - ['version', 2, 9, 1]
 ...
 box.space._cluster:select{}
 ---

On Thu, May 27, 2021 at 07:43:06PM +0300, Mergen Imeev via Tarantool-patches wrote:
> This patch introduces a new SQL built-in function UUID().
> 
> Closes #5886
> 
> @TarantoolBot document
> Title: SQL built-in function UUID()
> 
> SQL built-in function UUID() takes zero or one argument. If no argument
> is specified, a UUID v4 is generated. If the version of the UUID to
> generate is specified as an argument, the function returns the new UUID
> of the given version. Currently only version 4 of UUID is supported.
> ---
>  src/box/bootstrap.snap         | Bin 5991 -> 6016 bytes
>  src/box/lua/upgrade.lua        |  19 ++++++++++++
>  src/box/sql/func.c             |  39 +++++++++++++++++++++++++
>  src/box/sql/parse.y            |   2 +-
>  test/box-py/bootstrap.result   |   1 +
>  test/box/access.result         |   2 +-
>  test/box/access.test.lua       |   2 +-
>  test/box/access_bin.result     |   2 +-
>  test/box/access_bin.test.lua   |   2 +-
>  test/box/access_sysview.result |   8 ++---
>  test/box/function1.result      |   6 ++--
>  test/sql-tap/uuid.test.lua     |  52 ++++++++++++++++++++++++++++++++-
>  test/wal_off/func_max.result   |   8 ++---
>  13 files changed, 126 insertions(+), 17 deletions(-)
> 
> diff --git a/src/box/bootstrap.snap b/src/box/bootstrap.snap
> index c4a70297aad138d426a24ee4447af485e3597536..57374decc0f9de140772d9809a227e3ba4ce61eb 100644
> 
> diff --git a/src/box/lua/upgrade.lua b/src/box/lua/upgrade.lua
> index 6fba260bd..97afc0b4d 100644
> --- a/src/box/lua/upgrade.lua
> +++ b/src/box/lua/upgrade.lua
> @@ -1000,6 +1000,24 @@ local function upgrade_to_2_7_1()
>      function_access()
>  end
>  
> +--------------------------------------------------------------------------------
> +-- Tarantool 2.9.1
> +--------------------------------------------------------------------------------
> +local function sql_builtin_function_uuid()
> +    local _func = box.space._func
> +    local _priv = box.space._priv
> +    local datetime = os.date("%Y-%m-%d %H:%M:%S")
> +    local t = _func:auto_increment({ADMIN, 'UUID', 1, 'SQL_BUILTIN', '',
> +                                    'function', {}, 'any', 'none', 'none',
> +                                    false, false, true, {}, setmap({}), '',
> +                                    datetime, datetime})
> +    _priv:replace{ADMIN, PUBLIC, 'function', t.id, box.priv.X}
> +end
> +
> +local function upgrade_to_2_9_1()
> +    sql_builtin_function_uuid()
> +end
> +
>  --------------------------------------------------------------------------------
>  
>  local handlers = {
> @@ -1015,6 +1033,7 @@ local handlers = {
>      {version = mkversion(2, 3, 0), func = upgrade_to_2_3_0, auto = true},
>      {version = mkversion(2, 3, 1), func = upgrade_to_2_3_1, auto = true},
>      {version = mkversion(2, 7, 1), func = upgrade_to_2_7_1, auto = true},
> +    {version = mkversion(2, 9, 1), func = upgrade_to_2_9_1, auto = true},
>  }
>  
>  -- Schema version of the snapshot.
> diff --git a/src/box/sql/func.c b/src/box/sql/func.c
> index 9c4480a92..f93ae867d 100644
> --- a/src/box/sql/func.c
> +++ b/src/box/sql/func.c
> @@ -66,6 +66,35 @@ mem_as_bin(struct Mem *mem)
>  	return s;
>  }
>  
> +static void
> +sql_func_uuid(struct sql_context *ctx, int argc, struct Mem **argv)
> +{
> +	if (argc > 1) {
> +		diag_set(ClientError, ER_FUNC_WRONG_ARG_COUNT, "UUID",
> +			 "one or zero", argc);
> +		ctx->is_aborted = true;
> +		return;
> +	}
> +	if (argc == 1) {
> +		uint64_t version;
> +		if (mem_get_uint(argv[0], &version) != 0) {
> +			diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
> +				 mem_str(argv[0]), "integer");
> +			ctx->is_aborted = true;
> +			return;
> +		}
> +		if (version != 4) {
> +			diag_set(ClientError, ER_UNSUPPORTED, "Function UUID",
> +				 "versions other than 4");
> +			ctx->is_aborted = true;
> +			return;
> +		}
> +	}
> +	struct tt_uuid uuid;
> +	tt_uuid_create(&uuid);
> +	mem_set_uuid(ctx->pOut, &uuid);
> +}
> +
>  /*
>   * Return the collating function associated with a function.
>   */
> @@ -2544,6 +2573,16 @@ static struct {
>  	 .call = UpperICUFunc,
>  	 .finalize = NULL,
>  	 .export_to_sql = true,
> +	}, {
> +	 .name = "UUID",
> +	 .param_count = -1,
> +	 .returns = FIELD_TYPE_UUID,
> +	 .aggregate = FUNC_AGGREGATE_NONE,
> +	 .is_deterministic = false,
> +	 .flags = 0,
> +	 .call = sql_func_uuid,
> +	 .finalize = NULL,
> +	 .export_to_sql = true,
>  	}, {
>  	 .name = "VERSION",
>  	 .param_count = 0,
> diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
> index 4c9cf475e..bd041e862 100644
> --- a/src/box/sql/parse.y
> +++ b/src/box/sql/parse.y
> @@ -268,7 +268,7 @@ columnlist ::= tcons.
>    CONFLICT DEFERRED END ENGINE FAIL
>    IGNORE INITIALLY INSTEAD NO MATCH PLAN
>    QUERY KEY OFFSET RAISE RELEASE REPLACE RESTRICT
> -  RENAME CTIME_KW IF ENABLE DISABLE
> +  RENAME CTIME_KW IF ENABLE DISABLE UUID
>    .
>  %wildcard ANY.
>  
> diff --git a/test/box-py/bootstrap.result b/test/box-py/bootstrap.result
> index ed7accea3..7fd4fd64d 100644
> --- a/test/box-py/bootstrap.result
> +++ b/test/box-py/bootstrap.result
> @@ -242,6 +242,7 @@ box.space._priv:select{}
>    - [1, 2, 'function', 65, 4]
>    - [1, 2, 'function', 66, 4]
>    - [1, 2, 'function', 67, 4]
> +  - [1, 2, 'function', 68, 4]
>    - [1, 2, 'space', 276, 2]
>    - [1, 2, 'space', 277, 1]
>    - [1, 2, 'space', 281, 1]
> diff --git a/test/box/access.result b/test/box/access.result
> index 27e636122..1a8730f1a 100644
> --- a/test/box/access.result
> +++ b/test/box/access.result
> @@ -703,7 +703,7 @@ box.schema.func.exists(1)
>  ---
>  - true
>  ...
> -box.schema.func.exists(68)
> +box.schema.func.exists(69)
>  ---
>  - false
>  ...
> diff --git a/test/box/access.test.lua b/test/box/access.test.lua
> index a62f87ad8..2bf772b7b 100644
> --- a/test/box/access.test.lua
> +++ b/test/box/access.test.lua
> @@ -282,7 +282,7 @@ box.schema.user.exists{}
>  box.schema.func.exists('nosuchfunc')
>  box.schema.func.exists('guest')
>  box.schema.func.exists(1)
> -box.schema.func.exists(68)
> +box.schema.func.exists(69)
>  box.schema.func.exists('box.schema.user.info')
>  box.schema.func.exists()
>  box.schema.func.exists(nil)
> diff --git a/test/box/access_bin.result b/test/box/access_bin.result
> index c58f331d3..aeb8b3bd8 100644
> --- a/test/box/access_bin.result
> +++ b/test/box/access_bin.result
> @@ -298,7 +298,7 @@ box.schema.user.grant('guest', 'execute', 'universe')
>  function f1() return box.space._func:get(1)[4] end
>  ---
>  ...
> -function f2() return box.space._func:get(68)[4] end
> +function f2() return box.space._func:get(69)[4] end
>  ---
>  ...
>  box.schema.func.create('f1')
> diff --git a/test/box/access_bin.test.lua b/test/box/access_bin.test.lua
> index 41d5f4245..954266858 100644
> --- a/test/box/access_bin.test.lua
> +++ b/test/box/access_bin.test.lua
> @@ -112,7 +112,7 @@ test:drop()
>  -- notice that guest can execute stuff, but can't read space _func
>  box.schema.user.grant('guest', 'execute', 'universe')
>  function f1() return box.space._func:get(1)[4] end
> -function f2() return box.space._func:get(68)[4] end
> +function f2() return box.space._func:get(69)[4] end
>  box.schema.func.create('f1')
>  box.schema.func.create('f2',{setuid=true})
>  c = net.connect(box.cfg.listen)
> diff --git a/test/box/access_sysview.result b/test/box/access_sysview.result
> index 6468a0709..d7a7b7534 100644
> --- a/test/box/access_sysview.result
> +++ b/test/box/access_sysview.result
> @@ -258,11 +258,11 @@ box.session.su('guest')
>  ...
>  #box.space._vpriv:select{}
>  ---
> -- 82
> +- 83
>  ...
>  #box.space._vfunc:select{}
>  ---
> -- 67
> +- 68
>  ...
>  #box.space._vcollation:select{}
>  ---
> @@ -290,11 +290,11 @@ box.session.su('guest')
>  ...
>  #box.space._vpriv:select{}
>  ---
> -- 82
> +- 83
>  ...
>  #box.space._vfunc:select{}
>  ---
> -- 67
> +- 68
>  ...
>  #box.space._vsequence:select{}
>  ---
> diff --git a/test/box/function1.result b/test/box/function1.result
> index 928cd5758..0166c828f 100644
> --- a/test/box/function1.result
> +++ b/test/box/function1.result
> @@ -97,7 +97,7 @@ box.func["function1.args"]
>    exports:
>      lua: true
>      sql: false
> -  id: 68
> +  id: 69
>    setuid: false
>    is_multikey: false
>    is_deterministic: false
> @@ -593,7 +593,7 @@ func
>    exports:
>      lua: true
>      sql: false
> -  id: 68
> +  id: 69
>    setuid: false
>    is_multikey: false
>    is_deterministic: false
> @@ -665,7 +665,7 @@ func
>    exports:
>      lua: true
>      sql: false
> -  id: 68
> +  id: 69
>    setuid: false
>    is_multikey: false
>    is_deterministic: false
> diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua
> index 4287f0921..83fcc3d0e 100755
> --- a/test/sql-tap/uuid.test.lua
> +++ b/test/sql-tap/uuid.test.lua
> @@ -3,7 +3,7 @@ local build_path = os.getenv("BUILDDIR")
>  package.cpath = build_path..'/test/sql-tap/?.so;'..build_path..'/test/sql-tap/?.dylib;'..package.cpath
>  
>  local test = require("sqltester")
> -test:plan(139)
> +test:plan(145)
>  
>  local uuid = require("uuid")
>  local uuid1 = uuid.fromstr("11111111-1111-1111-1111-111111111111")
> @@ -1266,6 +1266,56 @@ test:do_execsql_test(
>          3,2,1
>      })
>  
> +-- Check function uuid().
> +test:do_execsql_test(
> +    "uuid-16.1",
> +    [[
> +        SELECT typeof(uuid());
> +    ]], {
> +        "uuid"
> +    })
> +
> +test:do_execsql_test(
> +    "uuid-16.2",
> +    [[
> +        SELECT typeof(uuid(4));
> +    ]], {
> +        "uuid"
> +    })
> +
> +test:do_catchsql_test(
> +    "uuid-16.3",
> +    [[
> +        SELECT uuid(1);
> +    ]], {
> +        1, "Function UUID does not support versions other than 4"
> +    })
> +
> +test:do_catchsql_test(
> +    "uuid-16.4",
> +    [[
> +        SELECT uuid('asd');
> +    ]], {
> +        1, "Type mismatch: can not convert asd to integer"
> +    })
> +
> +test:do_catchsql_test(
> +    "uuid-16.5",
> +    [[
> +        SELECT uuid(4, 5);
> +    ]], {
> +        1, "Wrong number of arguments is passed to UUID(): expected one or zero, got 2"
> +    })
> +
> +-- Make sure the uuid() function generates a new UUID each time when called.
> +test:do_execsql_test(
> +    "uuid-16.6",
> +    [[
> +        SELECT uuid() != uuid();
> +    ]], {
> +        true
> +    })
> +
>  test:execsql([[
>      DROP TRIGGER t;
>      DROP VIEW v;
> diff --git a/test/wal_off/func_max.result b/test/wal_off/func_max.result
> index 78db38d6b..cc5bcc141 100644
> --- a/test/wal_off/func_max.result
> +++ b/test/wal_off/func_max.result
> @@ -42,11 +42,11 @@ test_run:cmd("setopt delimiter ''");
>  ...
>  func_limit()
>  ---
> -- error: 'Failed to create function ''func31934'': function id is too big'
> +- error: 'Failed to create function ''func31933'': function id is too big'
>  ...
>  drop_limit_func()
>  ---
> -- error: Function 'func31934' does not exist
> +- error: Function 'func31933' does not exist
>  ...
>  box.schema.user.create('testuser')
>  ---
> @@ -62,11 +62,11 @@ session.su('testuser')
>  ...
>  func_limit()
>  ---
> -- error: 'Failed to create function ''func31934'': function id is too big'
> +- error: 'Failed to create function ''func31933'': function id is too big'
>  ...
>  drop_limit_func()
>  ---
> -- error: Function 'func31934' does not exist
> +- error: Function 'func31933' does not exist
>  ...
>  session.su('admin')
>  ---
> -- 
> 2.25.1
>