Functional index #1260

kostja · 2016-01-08T22:43:29Z

Implement functional indexes:

partial index, i.e. the index that only stores a subset of tuples in a space, for example, doesn't store tuples which have nil in the indexed field
index based on a stored function
multikey index

Implementation plan:

store function definition in _func and automatically load/define function on bootstrap (as well as replicate it)

Add an option to index - 'partial', false by default. Allow tuples with box.NULL fields that are contained only in partial indexes. Treat box.NULL fields as a user's desire not to add the particular tuple to partial indexes that contain this fields. Partially fixes #1260

Currently, Tarantool doesn't support partial indexes, so lets temporary remove their processing from SQL parser to avoid confusing situations. It can be returned after #1260 (implement functional indexes in Tarantool) is done. Closes #2165

Closes #1260

Drop indexes via index object metamethod call instead of direct _index space tuple deletion. With functional indexes patch drop metamethod would be redefined to make an extra service operations during index deletion. Need for #1260

Tarantool functional and multikey indexes allows you to create an index based on user-specified function. In a nutshell, this capability allows you to have case insensitive searches or sorts, search on complex equations, and extend the SQL language efficiently by implementing your own functions and operators and then searching on them. Feature use hidden space _i_{index_name}_{space_name} containing tuples of structure [{extractor_format}{pk_format}] and redefines on_replace trigger for target space to fill this space. Index object metamethods are monkeypatched to make indirect lookups in ispace to retrieve required tuple primary key. Closes #1260

Introduced a new object tuple_chunk: a memory allocation is associated with given tuple. tuple_format's vtab is extended with few new methods to manage tuple_chunks lifecycle. Implemented corresponding methid for memtx engine: a memory chunks are allocated with memtx's smalloc allocator. Needed for #1260

@TarantoolBot

Closes #1260 @TarantoolBot document Title: introduce functional indexes in memtx Now you can define a functional index using registered persistent function as key extractor. There is some restrictions for function and key definition for functional index: - referenced function must be persistent, deterministic and must return scalar type or array. - key parts describe the referenced routine's returned key (when the routine returned value type is array in meaning of multikey index key): all parts must be sequential and the first part's fieldno must be 1. - key parts can't use JSON paths. - you are not allowed to access tuple fields by names in function Functional index can't be primary. You are not allowed to change functional index extractor function while there are some functional indexes depends of it. To define a functional multikey index, just return multiple values from function. Each value (even when it is a single scalar must be returned as table i.e. {1}) and must follow functional index key definition. Example: s = box.schema.space.create('withdata') s:format({{name = 'name', type = 'string'}, {name = 'address', type = 'string'}}) pk = s:create_index('name', {parts = {1, 'string'}}) lua_code = [[function(tuple) local address = string.split(tuple[2]) local ret = {} for _, v in pairs(address) do table.insert(ret, {utf8.upper(v)}) end return unpack(ret) end]] box.schema.func.create('addr_extractor', {body = lua_code, is_deterministic = true, is_sandboxed = true}) idx = s:create_index('addr', {unique = false, func = 'addr_extractor', parts = {{1, 'string', collation = 'unicode_ci'}}}) s:insert({"James", "SIS Building Lambeth London UK"}) s:insert({"Sherlock", "221B Baker St Marylebone London NW1 6XE UK"}) idx:select('Uk') --- - - ['James', 'SIS Building Lambeth London UK'] - ['Sherlock', '221B Baker St Marylebone London NW1 6XE UK'] ...

@TarantoolBot

Closes #1260 @TarantoolBot document Title: introduce functional indexes in memtx Now you can define a functional index using a registered persistent function. There are restrictions for function and key definition for a functional index: - the referenced function must be persistent, deterministic and must return a scalar type or an array. - you must define key parts which describe the function return value - the function must return data which types match the defined key parts - the function may return multiple keys; this would be a multikey functional index; each key entry is indexed separately; - for multikey functional indexes, the key definition should start with part 1 and cover all returned key parts - key parts can't use JSON paths. - the function used for the functional index can not access tuple fields by name, only by index. Functional index can't be primary. It is not possible to change the used function after a functional index is defined on it. The index must be dropped first. To define a functional multikey index, just return multiple values from the function. Each value (even when it is a single scalar must be returned as a table i.e. {1}) and must match the key definition. Example: s = box.schema.space.create('withdata') s:format({{name = 'name', type = 'string'}, {name = 'address', type = 'string'}}) pk = s:create_index('name', {parts = {1, 'string'}}) lua_code = [[function(tuple) local address = string.split(tuple[2]) local ret = {} for _, v in pairs(address) do table.insert(ret, {utf8.upper(v)}) end return unpack(ret) end]] box.schema.func.create('address', {body = lua_code, is_deterministic = true, is_sandboxed = true}) idx = s:create_index('addr', {unique = false, func = 'address', parts = {{1, 'string', collation = 'unicode_ci'}}}) s:insert({"James", "SIS Building Lambeth London UK"}) s:insert({"Sherlock", "221B Baker St Marylebone London NW1 6XE UK"}) idx:select('Uk') --- - - ['James', 'SIS Building Lambeth London UK'] - ['Sherlock', '221B Baker St Marylebone London NW1 6XE UK'] ...

Introduced a new object tuple_chunk: a memory allocation is associated with given tuple. tuple_format's vtab is extended with few new methods to manage tuple_chunks lifecycle. Implemented corresponding methid for memtx engine: a memory chunks are allocated with memtx's smalloc allocator. Needed for #1260

@TarantoolBot

Closes #1260 @TarantoolBot document Title: introduce functional indexes in memtx Now you can define a functional index using a registered persistent function. There are restrictions for function and key definition for a functional index: - the referenced function must be persistent, deterministic and must return a scalar type or an array. - you must define key parts which describe the function return value - the function must return data which types match the defined key parts - the function may return multiple keys; this would be a multikey functional index; each key entry is indexed separately; - for multikey functional indexes, the key definition should start with part 1 and cover all returned key parts - key parts can't use JSON paths. - the function used for the functional index can not access tuple fields by name, only by index. Functional index can't be primary. It is not possible to change the used function after a functional index is defined on it. The index must be dropped first. To define a functional multikey index, just return multiple values from the function. Each value (even when it is a single scalar must be returned as a table i.e. {1}) and must match the key definition. Example: s = box.schema.space.create('withdata') s:format({{name = 'name', type = 'string'}, {name = 'address', type = 'string'}}) pk = s:create_index('name', {parts = {1, 'string'}}) lua_code = [[function(tuple) local address = string.split(tuple[2]) local ret = {} for _, v in pairs(address) do table.insert(ret, {utf8.upper(v)}) end return unpack(ret) end]] box.schema.func.create('address', {body = lua_code, is_deterministic = true, is_sandboxed = true}) idx = s:create_index('addr', {unique = false, func = 'address', parts = {{1, 'string', collation = 'unicode_ci'}}}) s:insert({"James", "SIS Building Lambeth London UK"}) s:insert({"Sherlock", "221B Baker St Marylebone London NW1 6XE UK"}) idx:select('Uk') --- - - ['James', 'SIS Building Lambeth London UK'] - ['Sherlock', '221B Baker St Marylebone London NW1 6XE UK'] ...

Introduced a new object tuple_chunk: a memory allocation is associated with given tuple. tuple_format's vtab is extended with few new methods to manage tuple_chunks lifecycle. Implemented corresponding methid for memtx engine: a memory chunks are allocated with memtx's smalloc allocator. Needed for #1260

@TarantoolBot

Closes #1260 @TarantoolBot document Title: introduce functional indexes in memtx Now you can define a functional index using a registered persistent function. There are restrictions for function and key definition for a functional index: - the referenced function must be persistent, deterministic and must return a scalar type or an array. - you must define key parts which describe the function return value - the function must return data which types match the defined key parts - the function may return multiple keys; this would be a multikey functional index; each key entry is indexed separately; - for multikey functional indexes, the key definition should start with part 1 and cover all returned key parts - key parts can't use JSON paths. - the function used for the functional index can not access tuple fields by name, only by index. Functional index can't be primary. It is not possible to change the used function after a functional index is defined on it. The index must be dropped first. To define a functional multikey index, just return multiple values from the function. Each value (even when it is a single scalar must be returned as a table i.e. {1}) and must match the key definition. Example: s = box.schema.space.create('withdata') s:format({{name = 'name', type = 'string'}, {name = 'address', type = 'string'}}) pk = s:create_index('name', {parts = {1, 'string'}}) lua_code = [[function(tuple) local address = string.split(tuple[2]) local ret = {} for _, v in pairs(address) do table.insert(ret, {utf8.upper(v)}) end return unpack(ret) end]] box.schema.func.create('address', {body = lua_code, is_deterministic = true, is_sandboxed = true}) idx = s:create_index('addr', {unique = false, func = 'address', parts = {{1, 'string', collation = 'unicode_ci'}}}) s:insert({"James", "SIS Building Lambeth London UK"}) s:insert({"Sherlock", "221B Baker St Marylebone London NW1 6XE UK"}) idx:select('Uk') --- - - ['James', 'SIS Building Lambeth London UK'] - ['Sherlock', '221B Baker St Marylebone London NW1 6XE UK'] ...

@TarantoolBot

Closes #1260 @TarantoolBot document Title: introduce functional indexes in memtx Now you can define a functional index using a registered persistent function. There are restrictions for function and key definition for a functional index: - the referenced function must be persistent, deterministic and must return a scalar type or an array. - you must define key parts which describe the function return value - the function must return data which types match the defined key parts - the function may return multiple keys; this would be a multikey functional index; each key entry is indexed separately; - for multikey functional indexes, the key definition should start with part 1 and cover all returned key parts - key parts can't use JSON paths. - the function used for the functional index can not access tuple fields by name, only by index. Functional index can't be primary. It is not possible to change the used function after a functional index is defined on it. The index must be dropped first. To define a functional multikey index, just return multiple values from the function. Each value (even when it is a single scalar must be returned as a table i.e. {1}) and must match the key definition. Example: s = box.schema.space.create('withdata') s:format({{name = 'name', type = 'string'}, {name = 'address', type = 'string'}}) pk = s:create_index('name', {parts = {1, 'string'}}) lua_code = [[function(tuple) local address = string.split(tuple[2]) local ret = {} for _, v in pairs(address) do table.insert(ret, {utf8.upper(v)}) end return unpack(ret) end]] box.schema.func.create('address', {body = lua_code, is_deterministic = true, is_sandboxed = true}) idx = s:create_index('addr', {unique = false, func = 'address', parts = {{1, 'string', collation = 'unicode_ci'}}}) s:insert({"James", "SIS Building Lambeth London UK"}) s:insert({"Sherlock", "221B Baker St Marylebone London NW1 6XE UK"}) idx:select('Uk') --- - - ['James', 'SIS Building Lambeth London UK'] - ['Sherlock', '221B Baker St Marylebone London NW1 6XE UK'] ...

@TarantoolBot

Closes #1260 @TarantoolBot document Title: introduce functional indexes in memtx Now you can define a functional index using a registered persistent function. There are restrictions for function and key definition for a functional index: - the referenced function must be persistent, deterministic and must return a scalar type or an array. - you must define key parts which describe the function return value - the function must return data which types match the defined key parts - the function may return multiple keys; this would be a multikey functional index; each key entry is indexed separately; - for multikey functional indexes, the key definition should start with part 1 and cover all returned key parts - key parts can't use JSON paths. - the function used for the functional index can not access tuple fields by name, only by index. Functional index can't be primary. It is not possible to change the used function after a functional index is defined on it. The index must be dropped first. To define a functional multikey index, just return multiple values from the function. Each value (even when it is a single scalar must be returned as a table i.e. {1}) and must match the key definition. Example: s = box.schema.space.create('withdata') s:format({{name = 'name', type = 'string'}, {name = 'address', type = 'string'}}) pk = s:create_index('name', {parts = {1, 'string'}}) lua_code = [[function(tuple) local address = string.split(tuple[2]) local ret = {} for _, v in pairs(address) do table.insert(ret, {utf8.upper(v)}) end return unpack(ret) end]] box.schema.func.create('address', {body = lua_code, is_deterministic = true, is_sandboxed = true}) idx = s:create_index('addr', {unique = false, func = 'address', parts = {{1, 'string', collation = 'unicode_ci'}}}) s:insert({"James", "SIS Building Lambeth London UK"}) s:insert({"Sherlock", "221B Baker St Marylebone London NW1 6XE UK"}) idx:select('Uk') --- - - ['James', 'SIS Building Lambeth London UK'] - ['Sherlock', '221B Baker St Marylebone London NW1 6XE UK'] ...

@TarantoolBot

Needed for #1260 @TarantoolBot document Title: A new option is_multikey for function definition A new option is_multikey allows to specify wether new function returns multiple values packed in a table object. This is a native way to define multikey func_index.

Introduced a new object tuple_chunk: a memory allocation is associated with given tuple. tuple_format's vtab is extended with few new methods to manage tuple_chunks lifecycle. Implemented corresponding methid for memtx engine: a memory chunks are allocated with memtx's smalloc allocator. Needed for #1260

@TarantoolBot

Closes #1260 @TarantoolBot document Title: introduce func indexes in memtx Now you can define a func_index using a registered persistent function. There are restrictions for function and key definition for a func_index: - the referenced function must be persistent, deterministic and must return a scalar type or an array. - you must define key parts which describe the function return value - the function must return data which types match the defined key parts - the function may return multiple keys; this would be a multikey func_index; each key entry is indexed separately; - for multikey func_indexes, the key definition should start with part 1 and cover all returned key parts - key parts can't use JSON paths. - the function used for the func_index can not access tuple fields by name, only by index. Functional index can't be primary. It is not possible to change the used function after a func_index is defined on it. The index must be dropped first. Each key returned by func_index function (even when it is a single scalar) must be returned as a table i.e. {1} and must match the key definition. To define a multikey func_index, create a function with opts = {is_multikey = true} and return a table of keys. Example: s = box.schema.space.create('withdata') s:format({{name = 'name', type = 'string'}, {name = 'address', type = 'string'}}) pk = s:create_index('name', {parts = {1, 'string'}}) lua_code = [[function(tuple) local address = string.split(tuple[2]) local ret = {} for _, v in pairs(address) do table.insert(ret, {utf8.upper(v)}) end return ret end]] box.schema.func.create('address', {body = lua_code, is_deterministic = true, is_sandboxed = true, opts = {is_multikey = true}}) idx = s:create_index('addr', {unique = false, func = 'address', parts = {{1, 'string', collation = 'unicode_ci'}}}) s:insert({"James", "SIS Building Lambeth London UK"}) s:insert({"Sherlock", "221B Baker St Marylebone London NW1 6XE UK"}) idx:select('Uk') --- - - ['James', 'SIS Building Lambeth London UK'] - ['Sherlock', '221B Baker St Marylebone London NW1 6XE UK'] ...

@TarantoolBot

Closes #1260 @TarantoolBot document Title: introduce func indexes in memtx Now you can define a func_index using a registered persistent function. There are restrictions for function and key definition for a func_index: - the referenced function must be persistent, deterministic and must return a scalar type or an array. - you must define key parts which describe the function return value - the function must return data which types match the defined key parts - the function may return multiple keys; this would be a multikey func_index; each key entry is indexed separately; - for multikey func_indexes, the key definition should start with part 1 and cover all returned key parts - key parts can't use JSON paths. - the function used for the func_index can not access tuple fields by name, only by index. Functional index can't be primary. It is not possible to change the used function after a func_index is defined on it. The index must be dropped first. Each key returned by func_index function (even when it is a single scalar) must be returned as a table i.e. {1} and must match the key definition. To define a multikey func_index, create a function with opts = {is_multikey = true} and return a table of keys. Example: s = box.schema.space.create('withdata') s:format({{name = 'name', type = 'string'}, {name = 'address', type = 'string'}}) pk = s:create_index('name', {parts = {1, 'string'}}) lua_code = [[function(tuple) local address = string.split(tuple[2]) local ret = {} for _, v in pairs(address) do table.insert(ret, {utf8.upper(v)}) end return ret end]] box.schema.func.create('address', {body = lua_code, is_deterministic = true, is_sandboxed = true, opts = {is_multikey = true}}) idx = s:create_index('addr', {unique = false, func = 'address', parts = {{1, 'string', collation = 'unicode_ci'}}}) s:insert({"James", "SIS Building Lambeth London UK"}) s:insert({"Sherlock", "221B Baker St Marylebone London NW1 6XE UK"}) idx:select('Uk') --- - - ['James', 'SIS Building Lambeth London UK'] - ['Sherlock', '221B Baker St Marylebone London NW1 6XE UK'] ...

@TarantoolBot

Needed for #1260 @TarantoolBot document Title: A new option is_multikey for function definition A new option is_multikey allows to specify wether new function returns multiple values packed in a table object. This is a native way to define multikey func_index.

Introduced a new object tuple_chunk: a memory allocation is associated with given tuple. tuple_format's vtab is extended with few new methods to manage tuple_chunks lifecycle. Implemented corresponding methid for memtx engine: a memory chunks are allocated with memtx's smalloc allocator. Needed for #1260

@TarantoolBot

Closes #1260 @TarantoolBot document Title: introduce func indexes in memtx Now you can define a func_index using a registered persistent function. There are restrictions for function and key definition for a func_index: - the referenced function must be persistent, deterministic and must return a scalar type or an array. - you must define key parts which describe the function return value - the function must return data which types match the defined key parts - the function may return multiple keys; this would be a multikey func_index; each key entry is indexed separately; - for multikey func_indexes, the key definition should start with part 1 and cover all returned key parts - key parts can't use JSON paths. - the function used for the func_index can not access tuple fields by name, only by index. Functional index can't be primary. It is not possible to change the used function after a func_index is defined on it. The index must be dropped first. Each key returned by func_index function (even when it is a single scalar) must be returned as a table i.e. {1} and must match the key definition. To define a multikey func_index, create a function with opts = {is_multikey = true} and return a table of keys. Example: s = box.schema.space.create('withdata') s:format({{name = 'name', type = 'string'}, {name = 'address', type = 'string'}}) pk = s:create_index('name', {parts = {1, 'string'}}) lua_code = [[function(tuple) local address = string.split(tuple[2]) local ret = {} for _, v in pairs(address) do table.insert(ret, {utf8.upper(v)}) end return ret end]] box.schema.func.create('address', {body = lua_code, is_deterministic = true, is_sandboxed = true, opts = {is_multikey = true}}) idx = s:create_index('addr', {unique = false, func = 'address', parts = {{1, 'string', collation = 'unicode_ci'}}}) s:insert({"James", "SIS Building Lambeth London UK"}) s:insert({"Sherlock", "221B Baker St Marylebone London NW1 6XE UK"}) idx:select('Uk') --- - - ['James', 'SIS Building Lambeth London UK'] - ['Sherlock', '221B Baker St Marylebone London NW1 6XE UK'] ...

@TarantoolBot

Closes #1260 @TarantoolBot document Title: introduce func indexes in memtx Now you can define a func_index using a registered persistent function. There are restrictions for function and key definition for a func_index: - the referenced function must be persistent, deterministic and must return a scalar type or an array. - you must define key parts which describe the function return value - the function must return data which types match the defined key parts - the function may return multiple keys; this would be a multikey func_index; each key entry is indexed separately; - for multikey func_indexes, the key definition should start with part 1 and cover all returned key parts - key parts can't use JSON paths. - the function used for the func_index can not access tuple fields by name, only by index. Functional index can't be primary. It is not possible to change the used function after a func_index is defined on it. The index must be dropped first. Each key returned by func_index function (even when it is a single scalar) must be returned as a table i.e. {1} and must match the key definition. To define a multikey func_index, create a function with opts = {is_multikey = true} and return a table of keys. Example: s = box.schema.space.create('withdata') s:format({{name = 'name', type = 'string'}, {name = 'address', type = 'string'}}) pk = s:create_index('name', {parts = {1, 'string'}}) lua_code = [[function(tuple) local address = string.split(tuple[2]) local ret = {} for _, v in pairs(address) do table.insert(ret, {utf8.upper(v)}) end return ret end]] box.schema.func.create('address', {body = lua_code, is_deterministic = true, is_sandboxed = true, opts = {is_multikey = true}}) idx = s:create_index('addr', {unique = false, func = 'address', parts = {{1, 'string', collation = 'unicode_ci'}}}) s:insert({"James", "SIS Building Lambeth London UK"}) s:insert({"Sherlock", "221B Baker St Marylebone London NW1 6XE UK"}) idx:select('Uk') --- - - ['James', 'SIS Building Lambeth London UK'] - ['Sherlock', '221B Baker St Marylebone London NW1 6XE UK'] ...

Introduced a new object tuple_chunk: a memory allocation is associated with given tuple. tuple_format's vtab is extended with few new methods to manage tuple_chunks lifecycle. Implemented corresponding methid for memtx engine: a memory chunks are allocated with memtx's smalloc allocator. Needed for #1260

@TarantoolBot

Closes #1260 @TarantoolBot document Title: introduce func indexes in memtx Now you can define a func_index using a registered persistent function. There are restrictions for function and key definition for a func_index: - the referenced function must be persistent, deterministic and must return a scalar type or an array. - you must define key parts which describe the function return value - the function must return data which types match the defined key parts - the function may return multiple keys; this would be a multikey func_index; each key entry is indexed separately; - for multikey func_indexes, the key definition should start with part 1 and cover all returned key parts - key parts can't use JSON paths. - the function used for the func_index can not access tuple fields by name, only by index. Functional index can't be primary. It is not possible to change the used function after a func_index is defined on it. The index must be dropped first. Each key returned by func_index function (even when it is a single scalar) must be returned as a table i.e. {1} and must match the key definition. To define a multikey func_index, create a function with opts = {is_multikey = true} and return a table of keys. Example: s = box.schema.space.create('withdata') s:format({{name = 'name', type = 'string'}, {name = 'address', type = 'string'}}) pk = s:create_index('name', {parts = {1, 'string'}}) lua_code = [[function(tuple) local address = string.split(tuple[2]) local ret = {} for _, v in pairs(address) do table.insert(ret, {utf8.upper(v)}) end return ret end]] box.schema.func.create('address', {body = lua_code, is_deterministic = true, is_sandboxed = true, opts = {is_multikey = true}}) idx = s:create_index('addr', {unique = false, func = 'address', parts = {{1, 'string', collation = 'unicode_ci'}}}) s:insert({"James", "SIS Building Lambeth London UK"}) s:insert({"Sherlock", "221B Baker St Marylebone London NW1 6XE UK"}) idx:select('Uk') --- - - ['James', 'SIS Building Lambeth London UK'] - ['Sherlock', '221B Baker St Marylebone London NW1 6XE UK'] ...

@TarantoolBot

Closes #1260 @TarantoolBot document Title: introduce func indexes in memtx Now you can define a func_index using a registered persistent function. There are restrictions for function and key definition for a func_index: - the referenced function must be persistent, deterministic and must return a scalar type or an array. - you must define key parts which describe the function return value - the function must return data which types match the defined key parts - the function may return multiple keys; this would be a multikey func_index; each key entry is indexed separately; - for multikey func_indexes, the key definition should start with part 1 and cover all returned key parts - key parts can't use JSON paths. - the function used for the func_index can not access tuple fields by name, only by index. Functional index can't be primary. It is not possible to change the used function after a func_index is defined on it. The index must be dropped first. Each key returned by func_index function (even when it is a single scalar) must be returned as a table i.e. {1} and must match the key definition. To define a multikey func_index, create a function with opts = {is_multikey = true} and return a table of keys. Example: s = box.schema.space.create('withdata') s:format({{name = 'name', type = 'string'}, {name = 'address', type = 'string'}}) pk = s:create_index('name', {parts = {1, 'string'}}) lua_code = [[function(tuple) local address = string.split(tuple[2]) local ret = {} for _, v in pairs(address) do table.insert(ret, {utf8.upper(v)}) end return ret end]] box.schema.func.create('address', {body = lua_code, is_deterministic = true, is_sandboxed = true, opts = {is_multikey = true}}) idx = s:create_index('addr', {unique = false, func = 'address', parts = {{1, 'string', collation = 'unicode_ci'}}}) s:insert({"James", "SIS Building Lambeth London UK"}) s:insert({"Sherlock", "221B Baker St Marylebone London NW1 6XE UK"}) idx:select('Uk') --- - - ['James', 'SIS Building Lambeth London UK'] - ['Sherlock', '221B Baker St Marylebone London NW1 6XE UK'] ...

@TarantoolBot

Closes #1260 @TarantoolBot document Title: introduce func indexes in memtx Now you can define a func_index using a registered persistent function. There are restrictions for function and key definition for a func_index: - the referenced function must be persistent, deterministic and must return a scalar type or an array. - you must define key parts which describe the function return value - the function must return data which types match the defined key parts - the function may return multiple keys; this would be a multikey func_index; each key entry is indexed separately; - for multikey func_indexes, the key definition should start with part 1 and cover all returned key parts - key parts can't use JSON paths. - the function used for the func_index can not access tuple fields by name, only by index. Functional index can't be primary. It is not possible to change the used function after a func_index is defined on it. The index must be dropped first. Each key returned by func_index function (even when it is a single scalar) must be returned as a table i.e. {1} and must match the key definition. To define a multikey func_index, create a function with opts = {is_multikey = true} and return a table of keys. Example: s = box.schema.space.create('withdata') s:format({{name = 'name', type = 'string'}, {name = 'address', type = 'string'}}) pk = s:create_index('name', {parts = {1, 'string'}}) lua_code = [[function(tuple) local address = string.split(tuple[2]) local ret = {} for _, v in pairs(address) do table.insert(ret, {utf8.upper(v)}) end return ret end]] box.schema.func.create('address', {body = lua_code, is_deterministic = true, is_sandboxed = true, opts = {is_multikey = true}}) idx = s:create_index('addr', {unique = false, func = 'address', parts = {{1, 'string', collation = 'unicode_ci'}}}) s:insert({"James", "SIS Building Lambeth London UK"}) s:insert({"Sherlock", "221B Baker St Marylebone London NW1 6XE UK"}) idx:select('Uk') --- - - ['James', 'SIS Building Lambeth London UK'] - ['Sherlock', '221B Baker St Marylebone London NW1 6XE UK'] ...

@TarantoolBot

Needed for #1260 @TarantoolBot document Title: A new option is_multikey for function definition A new option is_multikey allows to specify wether new function returns multiple values packed in a table object. This is a native way to define multikey func_index.

Introduced a new object tuple_chunk: a memory allocation is associated with given tuple. tuple_format's vtab is extended with few new methods to manage tuple_chunks lifecycle. Implemented corresponding methid for memtx engine: a memory chunks are allocated with memtx's smalloc allocator. Needed for #1260

@TarantoolBot

Closes #1260 @TarantoolBot document Title: introduce func indexes in memtx Now you can define a func_index using a registered persistent function. There are restrictions for function and key definition for a functional index: - the referenced function must be persistent, deterministic and must return a scalar type or an array. - you must define key parts which describe the function return value - the function must return data which types match the defined key parts - the function may return multiple keys; this would be a multikey functional index; each key entry is indexed separately; - for multikey functional indexes, the key definition should start with part 1 and cover all returned key parts - key parts can't use JSON paths. - the function used for the functional index can not access tuple fields by name, only by index. Functional index can't be primary. It is not possible to change the used function after a functional index is defined on it. The index must be dropped first. Each key returned by functional index function (even when it is a single scalar) must be returned as a table i.e. {1} and must match the key definition. To define a multikey functional index, create a function with opts = {is_multikey = true} and return a table of keys. Example: s = box.schema.space.create('withdata') s:format({{name = 'name', type = 'string'}, {name = 'address', type = 'string'}}) pk = s:create_index('name', {parts = {1, 'string'}}) lua_code = [[function(tuple) local address = string.split(tuple[2]) local ret = {} for _, v in pairs(address) do table.insert(ret, {utf8.upper(v)}) end return ret end]] box.schema.func.create('address', {body = lua_code, is_deterministic = true, is_sandboxed = true, opts = {is_multikey = true}}) idx = s:create_index('addr', {unique = false, func = 'address', parts = {{1, 'string', collation = 'unicode_ci'}}}) s:insert({"James", "SIS Building Lambeth London UK"}) s:insert({"Sherlock", "221B Baker St Marylebone London NW1 6XE UK"}) idx:select('Uk') --- - - ['James', 'SIS Building Lambeth London UK'] - ['Sherlock', '221B Baker St Marylebone London NW1 6XE UK'] ...

@TarantoolBot

Needed for #1260 @TarantoolBot document Title: A new option is_multikey for function definition A new option is_multikey allows to specify wether new function returns multiple values packed in a table object. This is a native way to define multikey func_index.

Introduced a new object tuple_chunk: a memory allocation is associated with given tuple. tuple_format's vtab is extended with few new methods to manage tuple_chunks lifecycle. Implemented corresponding methid for memtx engine: a memory chunks are allocated with memtx's smalloc allocator. Needed for #1260

kostja added the feature A new functionality label Feb 18, 2016

kostja added this to the 1.8 milestone Feb 18, 2016

kyukhin modified the milestones: 1.8.3, wishlist Jul 5, 2017

kostja modified the milestones: 1.8.4, wishlist Oct 27, 2017

kyukhin mentioned this issue Feb 19, 2018

Functional indexes are not working #2584

Open

Gerold103 self-assigned this Mar 7, 2018

Korablev77 mentioned this issue Mar 13, 2018

sql: confusing error message for functional indexes #3236

Closed

sharonovd added the customer label Jul 18, 2018

kyukhin modified the milestones: wishlist, 1.10.3 Jul 23, 2018

Gerold103 assigned kshcherbatov and unassigned Gerold103 Jul 23, 2018

Totktonada mentioned this issue Sep 9, 2018

Support deducible fields tarantool/graphql.0#101

Closed

kyukhin modified the milestones: 1.10.3, 2.1.1 Oct 25, 2018

kshcherbatov added a commit that referenced this issue Nov 13, 2018

box: functional and multikey indexes

e34f5d4

Closes #1260

kshcherbatov added a commit that referenced this issue Nov 14, 2018

box: functional and multikey indexes

8c8f0a7

Closes #1260

kyukhin closed this as completed in 4177fe1 Jul 26, 2019

kyukhin added the tmp label Aug 2, 2019

Totktonada removed the ready for review label May 14, 2020

Totktonada mentioned this issue May 6, 2024

custom user's index #9858

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Functional index #1260

Functional index #1260

kostja commented Jan 8, 2016 •

edited by mkostoevr

Functional index #1260

Functional index #1260

Comments

kostja commented Jan 8, 2016 • edited by mkostoevr

kostja commented Jan 8, 2016 •

edited by mkostoevr