Submodule box.space
CRUD operations in Tarantool are implemented by the box.space
submodule.
It has the data-manipulation functions select
, insert
, replace
,
update
, upsert
, delete
, get
, put
. It also has members,
such as id, and whether or not a space is enabled. Submodule source code
is available in file
src/box/lua/schema.lua.
Below is a list of all box.space
functions and members.
Name | Use |
---|---|
space_object:alter() | Alter a space |
space_object:auto_increment() | Generate key + Insert a tuple |
space_object:bsize() | Get count of bytes |
space_object:count() | Get count of tuples |
space_object:create_index()
* Details about index field types * Index field types to use in space_object:create_index() * Allowing null for an indexed key * Using field names instead of field numbers * Using the path option for map fields (JSON-indexes) * Using the path option with [*] * Making a functional index with space_object:create_index() |
Create an index |
space_object:delete() | Delete a tuple |
space_object:drop() | Destroy a space |
space_object:format() | Declare field names and types |
space_object:frommap() | Convert from map to tuple or table |
space_object:get() | Select a tuple |
space_object:insert() | Insert a tuple |
space_object:len() | Get count of tuples |
space_object:on_replace() | Create a replace trigger with a function that cannot change the tuple |
space_object:before_replace() | Create a replace trigger with a function that can change the tuple |
space_object:pairs() | Prepare for iterating |
space_object:put() | Insert or replace a tuple |
space_object:rename() | Rename a space |
space_object:replace() | Insert or replace a tuple |
space_object:run_triggers() | Enable/disable a replace trigger |
space_object:select() | Select one or more tuples |
space_object:truncate() | Delete all tuples |
space_object:update() | Update a tuple |
space_object:upsert() | Update a tuple |
space_object:user_defined() | Any function / method that any user wants to add |
space_object:create_check_constraint() | Create a check constraint |
space_object.enabled | Flag, true if space is enabled |
space_object.field_count | Required number of fields |
space_object.id | Numeric identifier of space |
space_object.index | Container of space’s indexes |
box.space._cluster | (Metadata) List of replica sets |
box.space._func | (Metadata) List of function tuples |
box.space._index | (Metadata) List of indexes |
box.space._vindex | (Metadata) List of indexes accessible for the current user |
box.space._priv | (Metadata) List of privileges |
box.space._vpriv | (Metadata) List of privileges accessible for the current user |
box.space._schema | (Metadata) List of schemas |
box.space._sequence | (Metadata) List of sequences |
box.space._sequence_data | (Metadata) List of sequences |
box.space._space | (Metadata) List of spaces |
box.space._vspace | (Metadata) List of spaces accessible for the current user |
box.space._user | (Metadata) List of users |
box.space._ck_constraint | (Metadata) List of check constraints |
box.space._vuser | (Metadata) List of users accessible for the current user |
box.space._collation | (Metadata) List of collations |
box.space._vcollation | (Metadata) List of collations accessible for the current user |
box.space._session_settings | (Metadata) List of settings affecting behavior of the current session |
-
object
space_object
-
space_object:
alter
(options) Since version 2.5.2. Alter an existing space. This method changes certain space parameters.
Parameters: - options (table) –
field_count
,user
,format
,temporary
,is_sync
, andname
– the meaning of these parameters is the same as in box.schema.space.create()
Return: nothing in case of success; an error when fails
Example:
tarantool> s = box.schema.create_space('tester') --- ... tarantool> format = {{name = 'field1', type = 'unsigned'}} --- ... tarantool> s:alter({name = 'tester1', format = format}) --- ... tarantool> s.name --- - tester1 ... tarantool> s:format() --- - [{'name': 'field1', 'type': 'unsigned'}] ...
- options (table) –
-
space_object:
auto_increment
(tuple) Insert a new tuple using an auto-increment primary key. The space specified by space_object must have an ‘unsigned’ or ‘integer’ or ‘number’ primary key index of type
TREE
. The primary-key field will be incremented before the insert.Since version 1.7.5 this method is deprecated – it is better to use a sequence.
Parameters: - space_object (space_object) – an object reference
- tuple (table/tuple) – tuple’s fields, other than the primary-key field
Return: the inserted tuple.
Rtype: tuple
Complexity factors: Index size, Index type, Number of indexes accessed, WAL settings.
Possible errors:
- index has wrong type;
- primary-key indexed field is not a number.
Example:
tarantool> box.space.tester:auto_increment{'Fld#1', 'Fld#2'} --- - [1, 'Fld#1', 'Fld#2'] ... tarantool> box.space.tester:auto_increment{'Fld#3'} --- - [2, 'Fld#3'] ...
-
space_object:
bsize
() Parameters: - space_object (space_object) – an object reference
Return: Number of bytes in the space. This number, which is stored in Tarantool’s internal memory, represents the total number of bytes in all tuples, not including index keys. For a measure of index size, see index_object:bsize().
Example:
tarantool> box.space.tester:bsize() --- - 22 ...
-
space_object:
count
([key][, iterator]) Return the number of tuples. If compared with len(), this method works slower because
count()
scans the entire space to count the tuples.Parameters: - space_object (space_object) – an object reference
- key (scalar/table) – primary-key field values, must be passed as a Lua table if key is multi-part
- iterator – comparison method
Return: Number of tuples.
Example:
tarantool> box.space.tester:count(2, {iterator='GE'}) --- - 1 ...
-
space_object:
create_index
(index-name[, options]) Create an index. It is mandatory to create an index for a space before trying to insert tuples into it, or select tuples from it. The first created index, which will be used as the primary-key index, must be unique.
Parameters: - space_object (space_object) – an object reference
- index_name (string) – name of index, which should conform to the rules for object names
- options (table) – see “Options for space_object:create_index()”, below
Return: index object
Rtype: index_object
Options for space_object:create_index()
Name Effect Type Default type type of index string (‘HASH’ or ‘TREE’ or ‘BITSET’ or ‘RTREE’) Note re storage engine: vinyl only supports ‘TREE’ ‘TREE’ id unique identifier number last index’s id, +1 unique index is unique boolean true
if_not_exists no error if duplicate name boolean false
parts field-numbers + types {field_no, 'unsigned'
or'string'
or'integer'
or'number'
or'double'
or'decimal'
or'boolean'
or'varbinary'
or'uuid'
or'array'
or'scalar'
, and optional collation or is_nullable value or path}{1, 'unsigned'}
dimension affects RTREE only number 2 distance affects RTREE only string (‘euclid’ or ‘manhattan’) ‘euclid’ bloom_fpr affects vinyl only number vinyl_bloom_fpr
page_size affects vinyl only number vinyl_page_size
range_size affects vinyl only number vinyl_range_size
run_count_per_level affects vinyl only number vinyl_run_count_per_level
run_size_ratio affects vinyl only number vinyl_run_size_ratio
sequence see section regarding specifying a sequence in create_index() string or number not present func functional index string not present The options in the above chart are also applicable for index_object:alter().
Note re storage engine: vinyl has extra options which by default are based on configuration parameters vinyl_bloom_fpr, vinyl_page_size, vinyl_range_size, vinyl_run_count_per_level, and vinyl_run_size_ratio – see the description of those parameters. The current values can be seen by selecting from box.space._index.
Building or rebuilding a large index will cause occasional yields so that other requests will not be blocked. If the other requests cause an illegal situation such as a duplicate key in a unique index, the index building or rebuilding will fail.
Possible errors:
- too many parts;
- index ‘…’ already exists;
- primary key must be unique.
tarantool> s = box.space.tester --- ... tarantool> s:create_index('primary', {unique = true, parts = { {field = 1, type = 'unsigned'}, {field = 2, type = 'string'}} }) --- ...
Details about index field types:
The eleven index field types (unsigned | string | integer | number | double | boolean | decimal | uuid | varbinary | array | scalar) differ depending on what values are allowed, and what index types are allowed.
- unsigned: unsigned integers between 0 and 18446744073709551615, about 18 quintillion. May also be called ‘uint’ or ‘num’, but ‘num’ is deprecated. Legal in memtx TREE or HASH indexes, and in vinyl TREE indexes.
- string: any set of octets, up to the maximum length. May also be called ‘str’. Legal in memtx TREE or HASH or BITSET indexes, and in vinyl TREE indexes. A string may have a collation.
- integer: integers between -9223372036854775808 and 18446744073709551615. May also be called ‘int’. Legal in memtx TREE or HASH indexes, and in vinyl TREE indexes.
- number: integers between -9223372036854775808 and 18446744073709551615, single-precision floating point numbers, or double-precision floating point numbers, or exact numbers. Legal in memtx TREE or HASH indexes, and in vinyl TREE indexes.
- double: double-precision floating point numbers. Legal in memtx TREE or HASH indexes, and in vinyl TREE indexes.
- boolean: true or false. Legal in memtx TREE or HASH indexes, and in vinyl TREE indexes.
- decimal: exact number returned from a function in the decimal module. Legal in memtx TREE or HASH indexes, and in vinyl TREE indexes.
- uuid: stores a 128-bit quantity sequence of lower-case hexadecimal digits, representing Universally Unique Identifiers (UUID). Legal in memtx TREE or HASH indexes, and in vinyl TREE indexes.
- varbinary: any set of octets, up to the maximum length. Legal in memtx TREE or HASH indexes, and in vinyl TREE indexes. A varbinary byte sequence does not have a collation because its contents are not UTF-8 characters.
- array: array of numbers. Legal in memtx RTREE indexes.
- scalar: null (input with
msgpack.NULL
oryaml.NULL
orjson.NULL
), booleans (true or false), or integers between -9223372036854775808 and 18446744073709551615, or single-precision floating point numbers, or double-precision floating-point numbers, or exact numbers, or strings, or (varbinary) byte arrays. When there is a mix of types, the key order is: null, then booleans, then numbers, then strings, then byte arrays. Legal in memtx TREE or HASH indexes, and in vinyl TREE indexes.
Additionally, nil is allowed with any index field type if is_nullable=true is specified.
Index field types to use in space_object:create_index()
Index field type What can be in it Where is it legal Examples unsigned integers between 0 and 18446744073709551615 memtx TREE or HASH indexes,
vinyl TREE indexes123456 string strings – any set of octets memtx TREE or HASH indexes
vinyl TREE indexes‘A B C’
‘\65 \66 \67’varbinary byte sequences – any set of octets memtx TREE or HASH indexes
vinyl TREE indexes‘\65 \66 \67’ integer integers between -9223372036854775808 and 18446744073709551615 memtx TREE or HASH indexes,
vinyl TREE indexes-2^63 number integers between -9223372036854775808 and 18446744073709551615, single-precision floating point numbers, double-precision floating point numbers, exact (decimal) numbers memtx TREE or HASH indexes,
vinyl TREE indexes1.234
-44
1.447e+44double double-precision floating point numbers memtx TREE or HASH indexes,
vinyl TREE indexes1.234 boolean true or false memtx TREE or HASH indexes,
vinyl TREE indexesfalse
truedecimal exact numbers returned by a function in the decimal module memtx TREE or HASH indexes,
vinyl TREE indexesdecimal.new(1.2) uuid values returned by uuid.new() memtx TREE or HASH indexes,
vinyl TREE indexesuuid.fromstr(‘
64d22e4d-ac92-4a
23-899a-e59f34af5479’)array array of integers between -9223372036854775808 and 9223372036854775807 memtx RTREE indexes {10, 11}
{3, 5, 9, 10}scalar null, booleans (true or false), integers between -9223372036854775808 and 18446744073709551615, single-precision floating point numbers, double-precision floating point numbers, strings memtx TREE or HASH indexes,
vinyl TREE indexesnull
true
-1
1.234
‘’
‘ру’Allowing null for an indexed key: If the index type is TREE, and the index is not the primary index, then the
parts={...}
clause may includeis_nullable=true
oris_nullable=false
(the default). Ifis_nullable
is true, then it is legal to insertnil
or an equivalent such asmsgpack.NULL
(or it is legal to insert nothing at all for trailing nullable fields). Within indexes, such “null values” are always treated as equal to other null values, and are always treated as less than non-null values. Nulls may appear multiple times even in a unique index. Example:box.space.tester:create_index('I',{unique=true,parts={{field = 2, type = 'number', is_nullable = true}}})
Warning
It is legal to create multiple indexes for the same field with different
is_nullable
values, or to call space_object:format() with a differentis_nullable
value from what is used for an index. When there is a contradiction, the rule is: null is illegal unlessis_nullable=true
for every index and for the space format.Using field names instead of field numbers:
create_index()
can use field names and/or field types described by the optional space_object:format() clause. In the following example, we showformat()
for a space that has two columns named ‘x’ and ‘y’, and then we show five variations of theparts={}
clause ofcreate_index()
, first for the ‘x’ column, second for both the ‘x’ and ‘y’ columns. The variations include omitting the type, using numbers, and adding extra braces.box.space.tester:format({{name='x', type='scalar'}, {name='y', type='integer'}}) box.space.tester:create_index('I2',{parts={{'x', 'scalar'}}}) box.space.tester:create_index('I3',{parts={{'x','scalar'},{'y','integer'}}}) box.space.tester:create_index('I4',{parts={{1,'scalar'}}}) box.space.tester:create_index('I5',{parts={{1,'scalar'},{2,'integer'}}}) box.space.tester:create_index('I6',{parts={1}}) box.space.tester:create_index('I7',{parts={1,2}}) box.space.tester:create_index('I8',{parts={'x'}}) box.space.tester:create_index('I9',{parts={'x','y'}}) box.space.tester:create_index('I10',{parts={{'x'}}}) box.space.tester:create_index('I11',{parts={{'x'},{'y'}}})
Using the path option for map fields (JSON-indexes): To create an index for a field that is a map (a path string and a scalar value), specify the path string during index_create, that is,
parts={
field-number,'data-type',path = 'path-name'
}
. The index type must be'tree'
or'hash'
and the field’s contents must always be maps with the same path.-- Example 1 -- The simplest use of path: -- Result will be - - [{'age': 44}] box.schema.space.create('T') box.space.T:create_index('I',{parts={{field = 1, type = 'scalar', path = 'age'}}}) box.space.T:insert{{age=44}} box.space.T:select(44) -- Example 2 -- path plus format() plus JSON syntax to add clarity -- Result will be: - [1, {'FIO': {'surname': 'Xi', 'firstname': 'Ahmed'}}] s = box.schema.space.create('T') format = {{'id', 'unsigned'}, {'data', 'map'}} s:format(format) parts = {{'data.FIO["firstname"]', 'str'}, {'data.FIO["surname"]', 'str'}} i = s:create_index('info', {parts = parts}) s:insert({1, {FIO={firstname='Ahmed', surname='Xi'}}})
Note re storage engine: vinyl supports only the TREE index type, and vinyl secondary indexes must be created before tuples are inserted.
Using the path option with [*] The string in a path option can contain ‘[*]’ which is called an array index placeholder. Indexes defined with this are useful for JSON documents that all have the same structure. For example, when creating an index on field#2 for a string document that will start with
{'data': [{'name': '...'}, {'name': '...'}]
, the parts section in the create_index request could look like:parts = {{field = 2, type = 'str', path = 'data[*].name'}}
. Then tuples containing names can be retrieved quickly withindex_object:select({key-value})
. In fact a single field can have multiple keys, as in this example which retrieves the same tuple twice because there are two keys ‘A’ and ‘B’ which both match the request:s = box.schema.space.create('json_documents') s:create_index('primarykey') i = s:create_index('multikey', {parts = {{field = 2, type = 'str', path = 'data[*].name'}}}) s:insert({1, {data = {{name='A'}, {name='B'}}, extra_field = 1}}) i:select({''},{iterator='GE'})
The result of the select request looks like this:
tarantool> i:select({''},{iterator='GE'}) --- - - [1, {'data': [{'name': 'A'}, {'name': 'B'}], 'extra_field': 1}] - [1, {'data': [{'name': 'A'}, {'name': 'B'}], 'extra_field': 1}] ...
Some restrictions exist: () ‘[*]’ must be alone or must be at the end of a name in the path; () ‘[*]’ must not appear twice in the path; () if an index has a path with x[*] then no other index can have a path with x.component; () ‘[*]’ must not appear in the path of a primary-key ; () if an index has
unique=true
and has a path with ‘[*]’ then duplicate keys from different tuples are disallowed but duplicate keys for the same tuple are allowed; () As with Using the path option for map fields, the field’s value must have the structure that the path definition implies, or be nil (nil is not indexed).Making a functional index with space_object:create_index()
Functional indexes are indexes that call a user-defined function for forming the index key, rather than depending entirely on the Tarantool default formation. Functional indexes are useful for condensing or truncating or reversing or any other way that users want to customize the index.
The function definition must expect a tuple (which has the contents of fields at the time a data-change request happens) and must return a tuple (which has the contents that will actually be put in the index).
The space must have a memtx engine.
The function must be persistent and deterministic.
The key parts must not depend on JSON paths.
Thecreate_index
definition must include specification of all key parts, and the function must return a table which has the same number of key parts with the same types.
The function must access key-part values by index, not by field name.
Functional indexes must not be primary-key indexes.
Functional indexes cannot be altered and the function cannot be changed if it is used for an index, so the only way to change them is to drop the index and create it again.
Only sandboxed functions are suitable for functional indexes.Example:
A function could make a key using only the first letter of a string field.
-- Step 1: Make the space. -- The space needs a primary-key field, which is not the field that we -- will use for the functional index. box.schema.space.create('x', {engine = 'memtx'}) box.space.x:create_index('i',{parts={{field = 1, type = 'string'}}}) -- Step 2: Make the function. -- The function expects a tuple. In this example it will work on tuple[2] -- because the key source is field number 2 in what we will insert. -- Use string.sub() from the string module to get the first character. lua_code = [[function(tuple) return {string.sub(tuple[2],1,1)} end]] -- Step 3: Make the function persistent. -- Use the box.schema.func.create function for this. box.schema.func.create('F', {body = lua_code, is_deterministic = true, is_sandboxed = true}) -- Step 4: Make the functional index. -- Specify the fields whose values will be passed to the function. -- Specify the function. box.space.x:create_index('j',{parts={{field = 1, type = 'string'}},func = 'F'}) -- Step 5: Test. -- Insert a few tuples. -- Select using only the first letter, it will work because that is the key -- Or, select using the same function as was used for insertion box.space.x:insert{'a', 'wombat'} box.space.x:insert{'b', 'rabbit'} box.space.x.index.j:select('w') box.space.x.index.j:select(box.func.F:call({{'x', 'wombat'}}));
The results of the two
select
requests will look like this:tarantool> box.space.x.index.j:select('w') --- - - ['a', 'wombat'] ... tarantool> box.space.x.index.j:select(box.func.F:call({{'x','wombat'}})); --- - - ['a', 'wombat'] ...
Functions for functional indexes can return multiple keys.
Such functions are called “multikey” functions.
Thebox.func.create
options must includeopts = {is_multikey = true}
.
The return value must be a table of tuples.
If a multikey function returns N tuples, then N keys will be added to the index.Example:
s = box.schema.space.create('withdata') s:format({{name = 'name', type = 'string'}, {name = 'address', type = 'string'}}) pk = s:create_index('name', {parts = {{field = 1, type = '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 = {{field = 1, type = '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') -- Both tuples will be returned.
-
space_object:
delete
(key) Delete a tuple identified by a primary key.
Parameters: - space_object (space_object) – an object reference
- key (scalar/table) – primary-key field values, must be passed as a Lua table if key is multi-part
Return: the deleted tuple
Rtype: tuple
Complexity factors: Index size, Index type
Note re storage engine: vinyl will return
nil
, rather than the deleted tuple.Example:
tarantool> box.space.tester:delete(1) --- - [1, 'My first tuple'] ... tarantool> box.space.tester:delete(1) --- ... tarantool> box.space.tester:delete('a') --- - error: 'Supplied key type of part 0 does not match index part type: expected unsigned' ...
For more usage scenarios and typical errors see Example: using data operations further in this section.
-
space_object:
drop
() Drop a space. The method is performed in background and doesn’t block consequent requests.
Parameters: - space_object (space_object) – an object reference
Return: nil
Possible errors:
space_object
does not exist.Complexity factors: Index size, Index type, Number of indexes accessed, WAL settings.
Example:
box.space.space_that_does_not_exist:drop()
-
space_object:
format
([format-clause]) Declare field names and types.
Parameters: - space_object (space_object) – an object reference
- format-clause (table) – a list of field names and types
Return: nil, unless format-clause is omitted
Possible errors:
space_object
does not exist;- field names are duplicated;
- type is not legal.
Ordinarily Tarantool allows unnamed untyped fields. But with
format
users can, for example, document that the Nth field is the surname field and must contain strings. It is also possible to specify a format clause in box.schema.space.create().The format clause contains, for each field, a definition within braces:
{name='...',type='...'[,is_nullable=...]}
, where:- the
name
value may be any string, provided that two fields do not have the same name; - the
type
value may be any of allowed types: any | unsigned | string | integer | number | varbinary | boolean | double | decimal | uuid | array | map | scalar, but for creating an index use only indexed fields; - the optional
is_nullable
value may be eithertrue
orfalse
(the same as the requirement in “Options for space_object:create_index”). See also the warning notice in section Allowing null for an indexed key.
It is not legal for tuples to contain values that have the wrong type; for example after
box.space.tester:format({{' ',type='number'}})
the requestbox.space.tester:insert{'string-which-is-not-a-number'}
will cause an error.It is not legal for tuples to contain null values if
is_nullable=false
, which is the default; for example afterbox.space.tester:format({{' ',type='number',is_nullable=false}})
the requestbox.space.tester:insert{nil,2}
will cause an error.It is legal for tuples to have more fields than are described by a format clause. The way to constrain the number of fields is to specify a space’s field_count member.
It is legal for tuples to have fewer fields than are described by a format clause, if the omitted trailing fields are described with
is_nullable=true
; for example afterbox.space.tester:format({{'a',type='number'},{'b',type='number',is_nullable=true}})
the requestbox.space.tester:insert{2}
will not cause a format-related error.It is legal to use
format
on a space that already has a format, thus replacing any previous definitions, provided that there is no conflict with existing data or index definitions.It is legal to use
format
to change theis_nullable
flag; for example afterbox.space.tester:format({{' ',type='scalar',is_nullable=false}})
the requestbox.space.tester:format({{' ',type='scalar',is_nullable=true}})
will not cause an error – and will not cause rebuilding of the space. But going the other way and changingis_nullable
fromtrue
tofalse
might cause rebuilding and might cause an error if there are existing tuples with nulls.Example:
box.space.tester:format({{name='surname',type='string'},{name='IDX',type='array'}}) box.space.tester:format({{name='surname',type='string',is_nullable=true}})
There are legal variations of the format clause:
- omitting both ‘name=’ and ‘type=’,
- omitting ‘type=’ alone, and
- adding extra braces.
The following examples show all the variations, first for one field named ‘x’, second for two fields named ‘x’ and ‘y’.
box.space.tester:format({{'x'}}) box.space.tester:format({{'x'},{'y'}}) box.space.tester:format({{name='x',type='scalar'}}) box.space.tester:format({{name='x',type='scalar'},{name='y',type='unsigned'}}) box.space.tester:format({{name='x'}}) box.space.tester:format({{name='x'},{name='y'}}) box.space.tester:format({{'x',type='scalar'}}) box.space.tester:format({{'x',type='scalar'},{'y',type='unsigned'}}) box.space.tester:format({{'x','scalar'}}) box.space.tester:format({{'x','scalar'},{'y','unsigned'}})
The following example shows how to create a space, format it with all possible types, and insert into it.
tarantool> box.schema.space.create('t') --- - engine: memtx before_replace: 'function: 0x4019c488' on_replace: 'function: 0x4019c460' ck_constraint: [] field_count: 0 temporary: false index: [] is_local: false enabled: false name: t id: 534 - created ... tarantool> ffi = require('ffi') --- ... tarantool> decimal = require('decimal') --- ... tarantool> uuid = require('uuid') --- ... tarantool> box.space.t:format({{name = '1', type = 'any'}, > {name = '2', type = 'unsigned'}, > {name = '3', type = 'string'}, > {name = '4', type = 'number'}, > {name = '5', type = 'double'}, > {name = '6', type = 'integer'}, > {name = '7', type = 'boolean'}, > {name = '8', type = 'decimal'}, > {name = '9', type = 'uuid'}, > {name = 'a', type = 'scalar'}, > {name = 'b', type = 'array'}, > {name = 'c', type = 'map'}}) --- ... tarantool> box.space.t:create_index('i',{parts={2, type = 'unsigned'}}) --- - unique: true parts: - type: unsigned is_nullable: false fieldno: 2 id: 0 space_id: 534 type: TREE name: i ... tarantool> box.space.t:insert{{'a'}, -- any > 1, -- unsigned > 'W?', -- string > 5.5, -- number > ffi.cast('double', 1), -- double > -0, -- integer > true, -- boolean > decimal.new(1.2), -- decimal > uuid.new(), -- uuid > true, -- scalar > {{'a'}}, -- array > {val=1}} -- map --- - [['a'], 1, 'W?', 5.5, 1, 0, true, 1.2, 1f41e7b8-3191-483d-b46e-1aa6a4b14557, true, [['a']], {'val': 1}] ...
Names specified with the format clause can be used in space_object:get() and in space_object:create_index() and in tuple_object[field-name] and in tuple_object[field-path].
If the format clause is omitted, then the returned value is the table that was used in a previous
space_object:format(format-clause)
invocation. For example, afterbox.space.tester:format({{'x','scalar'}})
,box.space.tester:format()
will return[{'name': 'x', 'type': 'scalar'}]
.Formatting or reformatting a large space will cause occasional yields so that other requests will not be blocked. If the other requests cause an illegal situation such as a field value of the wrong type, the formatting or reformatting will fail.
-
space_object:
frommap
(map[, option]) Convert a map to a tuple instance or to a table. The map must consist of “field name = value” pairs. The field names and the value types must match names and types stated previously for the space, via space_object:format().
Parameters: - space_object (space_object) – an object reference
- map (field-value-pairs) – a series of “field = value” pairs, in any order.
- option (boolean) – the only legal option is
{table = true|false}
;
if the option is omitted or if{table = false}
, then return type will be ‘cdata’ (i.e. tuple);
if{table = true}
, then return type will be ‘table’.
Return: a tuple instance or table.
Rtype: tuple or table
Possible errors:
space_object
does not exist or has no format; “unknown field”.Example:
-- Create a format with two fields named 'a' and 'b'. -- Create a space with that format. -- Create a tuple based on a map consistent with that space. -- Create a table based on a map consistent with that space. tarantool> format1 = {{name='a',type='unsigned'},{name='b',type='scalar'}} --- ... tarantool> s = box.schema.create_space('test', {format = format1}) --- ... tarantool> s:frommap({b = 'x', a = 123456}) --- - [123456, 'x'] ... tarantool> s:frommap({b = 'x', a = 123456}, {table = true}) --- - - 123456 - x ...
-
space_object:
get
(key) Search for a tuple in the given space.
Parameters: - space_object (space_object) – an object reference
- key (scalar/table) – value to be matched against the index key, which may be multi-part.
Return: the tuple whose index key matches
key
, ornil
.Rtype: tuple
Possible errors:
space_object
does not exist.Complexity factors: Index size, Index type, Number of indexes accessed, WAL settings.
The
box.space...select
function returns a set of tuples as a Lua table; thebox.space...get
function returns at most a single tuple. And it is possible to get the first tuple in a space by appending[1]
. Thereforebox.space.tester:get{1}
has the same effect asbox.space.tester:select{1}[1]
, if exactly one tuple is found.Example:
box.space.tester:get{1}
Using field names instead of field numbers: get() can use field names described by the optional space_object:format() clause. This is true because the object returned by
get()
can be used with most of the features described in the Submodule box.tuple description, including tuple_object[field-name].For example, we can format the tester space with a field named x and use the name x in the index definition:
box.space.tester:format({{name='x',type='scalar'}}) box.space.tester:create_index('I',{parts={'x'}})
Then, if
get
orselect
retrieves a single tuple, we can reference the field ‘x’ in the tuple by its name:box.space.tester:get{1}['x'] box.space.tester:select{1}[1]['x']
-
space_object:
insert
(tuple) Insert a tuple into a space.
Parameters: - space_object (space_object) – an object reference
- tuple (tuple/table) – tuple to be inserted.
Return: the inserted tuple
Rtype: tuple
Possible errors:
ER_TUPLE_FOUND
if a tuple with the same unique-key value already exists.Example:
tarantool> box.space.tester:insert{5000,'tuple number five thousand'} --- - [5000, 'tuple number five thousand'] ...
For more usage scenarios and typical errors see Example: using data operations further in this section.
-
space_object:
len
() Return the number of tuples in the space. If compared with count(), this method works faster because
len()
does not scan the entire space to count the tuples.Parameters: - space_object (space_object) – an object reference
Return: Number of tuples in the space.
Example:
tarantool> box.space.tester:len() --- - 2 ...
Note re storage engine: vinyl supports
len()
but the result may be approximate. If an exact result is necessary then use count() or pairs():length().
-
space_object:
on_replace
([trigger-function[, old-trigger-function]]) Create a “replace trigger”. The
trigger-function
will be executed whenever areplace()
orinsert()
orupdate()
orupsert()
ordelete()
happens to a tuple in<space-name>
.Parameters: - trigger-function (function) – function which will become the trigger function; see Example #2 below for details about trigger function parameters
- old-trigger-function (function) – existing trigger function which
will be replaced by
trigger-function
Return: nil or function pointer
If the parameters are (nil, old-trigger-function), then the old trigger is deleted.
If both parameters are omitted, then the response is a list of existing trigger functions.
If it is necessary to know whether the trigger activation happened due to replication or on a specific connection type, the function can refer to box.session.type().
Details about trigger characteristics are in the triggers section.
See also space_object:before_replace().
Example #1:
tarantool> function f () > x = x + 1 > end tarantool> box.space.X:on_replace(f)
Example #2:
The
trigger-function
can have up to four parameters:- (tuple) old value which has the contents before the request started,
- (tuple) new value which has the contents after the request ended,
- (string) space name,
- (string) type of request which is ‘INSERT’, ‘DELETE’, ‘UPDATE’, or ‘REPLACE’.
For example, the following code causes nil and ‘INSERT’ to be printed when the insert request is processed, and causes [1, ‘Hi’] and ‘DELETE’ to be printed when the delete request is processed:
box.schema.space.create('space_1') box.space.space_1:create_index('space_1_index',{}) function on_replace_function (old, new, s, op) print(old) print(op) end box.space.space_1:on_replace(on_replace_function) box.space.space_1:insert{1,'Hi'} box.space.space_1:delete{1}
Example #3:
The following series of requests will create a space, create an index, create a function which increments a counter, create a trigger, do two inserts, drop the space, and display the counter value - which is 2, because the function is executed once after each insert.
tarantool> s = box.schema.space.create('space53') tarantool> s:create_index('primary', {parts = {{field = 1, type = 'unsigned'}}}) tarantool> function replace_trigger() > replace_counter = replace_counter + 1 > end tarantool> s:on_replace(replace_trigger) tarantool> replace_counter = 0 tarantool> t = s:insert{1, 'First replace'} tarantool> t = s:insert{2, 'Second replace'} tarantool> s:drop() tarantool> replace_counter
Note
You shouldn’t use in trigger-functions for
on_replace
andbefore_replace
- transactions,
- yield-operations (explicit or not),
- actions that are not allowed to be used in transactions (see rule #2)
because everything executed inside triggers is already in a transaction.
Example:
tarantool> box.space.test:on_replace(fiber.yield) tarantool> box.space.test:replace{1, 2, 3} 2020-02-02 21:22:03.073 [73185] main/102/init.lua txn.c:532 E> ER_TRANSACTION_YIELD: Transaction has been aborted by a fiber yield --- - error: Transaction has been aborted by a fiber yield ...
-
space_object:
before_replace
([trigger-function[, old-trigger-function]]) Create a “replace trigger”. The
trigger-function
will be executed whenever areplace()
orinsert()
orupdate()
orupsert()
ordelete()
happens to a tuple in<space-name>
.Parameters: - trigger-function (function) – function which will become the trigger function; for the trigger function’s optional parameters see the description of on_replace.
- old-trigger-function (function) – existing trigger function which
will be replaced by
trigger-function
Return: nil or function pointer
If the parameters are
(nil, old-trigger-function)
, then the old trigger is deleted.If both parameters are omitted, then the response is a list of existing trigger functions.
If it is necessary to know whether the trigger activation happened due to replication or on a specific connection type, the function can refer to box.session.type().
Details about trigger characteristics are in the triggers section.
See also space_object:on_replace().
Administrators can make replace triggers with
on_replace()
, or make triggers withbefore_replace()
. If they make both types, then allbefore_replace
triggers are executed before allon_replace
triggers. The functions for bothon_replace
andbefore_replace
triggers can make changes to the database, but only the functions forbefore_replace
triggers can change the tuple that is being replaced.Since a
before_replace
trigger function has the extra capability of making a change to the old tuple, it also can have extra overhead, to fetch the old tuple before making the change. Therefore anon_replace
trigger is better if there is no need to change the old tuple. However, this only applies for the memtx engine – for the vinyl engine, the fetch will happen for either kind of trigger. (With memtx the tuple data is stored along with the index key so no extra search is necessary; with vinyl that is not the case so the extra search is necessary.)Where the extra capability is not needed,
on_replace
should be used instead ofbefore_replace
. Usuallybefore_replace
is used only for certain replication scenarios – it is useful for conflict resolution.The value that a
before_replace
trigger function can return affects what will happen after the return. Specifically:- if there is no return value, then execution proceeds, inserting|replacing the new value;
- if the value is nil, then the tuple will be deleted;
- if the value is the same as the old parameter, then no
on_replace
function will be called and the data change will be skipped - if the value is the same as the new parameter, then it’s as if
the
before_replace
function wasn’t called; - if the value is some other tuple, then it is used for insert/replace.
However, if a trigger function returns an old tuple, or if a trigger function calls run_triggers(false), that will not affect other triggers that are activated for the same insert|update|replace request.
Example:
The following are
before_replace
functions that have no return value, or that return nil, or the same as the old parameter, or the same as the new parameter, or something else.function f1 (old, new) return end function f2 (old, new) return nil end function f3 (old, new) return old end function f4 (old, new) return new end function f5 (old, new) return box.tuple.new({new[1],'b'}) end
-
space_object:
pairs
([key[, iterator]]) Search for a tuple or a set of tuples in the given space, and allow iterating over one tuple at a time.
Parameters: - space_object (space_object) – an object reference
- key (scalar/table) – value to be matched against the index key, which may be multi-part
- iterator – see index_object:pairs
Return: iterator which can be used in a for/end loop or with totable()
Possible errors:
- no such space;
- wrong type.
Complexity factors: Index size, Index type.
For examples of complex
pairs
requests, where one can specify which index to search and what condition to use (for example “greater than” instead of “equal to”), see the later section index_object:pairs.For information about iterators’ internal structures see the “Lua Functional library” documentation.
Example:
tarantool> s = box.schema.space.create('space33') --- ... tarantool> -- index 'X' has default parts {1, 'unsigned'} tarantool> s:create_index('X', {}) --- ... tarantool> s:insert{0, 'Hello my '}, s:insert{1, 'Lua world'} --- - [0, 'Hello my '] - [1, 'Lua world'] ... tarantool> tmp = '' --- ... tarantool> for k, v in s:pairs() do > tmp = tmp .. v[2] > end --- ... tarantool> tmp --- - Hello my Lua world ...
-
space_object:
rename
(space-name) Rename a space.
Parameters: - space_object (space_object) – an object reference
- space-name (string) – new name for space
Return: nil
Possible errors:
space_object
does not exist.Example:
tarantool> box.space.space55:rename('space56') --- ... tarantool> box.space.space56:rename('space55') --- ...
-
space_object:
replace
(tuple) -
space_object:
put
(tuple) Insert a tuple into a space. If a tuple with the same primary key already exists,
box.space...:replace()
replaces the existing tuple with a new one. The syntax variantsbox.space...:replace()
andbox.space...:put()
have the same effect; the latter is sometimes used to show that the effect is the converse ofbox.space...:get()
.Parameters: - space_object (space_object) – an object reference
- tuple (table/tuple) – tuple to be inserted
Return: the inserted tuple.
Rtype: tuple
Possible errors:
ER_TUPLE_FOUND
if a different tuple with the same unique-key value already exists. (This will only happen if there is a unique secondary index.)Complexity factors: Index size, Index type, Number of indexes accessed, WAL settings.
Example:
box.space.tester:replace{5000, 'tuple number five thousand'}
For more usage scenarios and typical errors see Example: using data operations further in this section.
-
space_object:
run_triggers
(true|false) At the time that a trigger is defined, it is automatically enabled - that is, it will be executed. Replace triggers can be disabled with
box.space.space-name:run_triggers(false)
and re-enabled withbox.space.space-name:run_triggers(true)
.Return: nil Example:
The following series of requests will associate an existing function named F with an existing space named T, associate the function a second time with the same space (so it will be called twice), disable all triggers of T, and delete each trigger by replacing with
nil
.tarantool> box.space.T:on_replace(F) tarantool> box.space.T:on_replace(F) tarantool> box.space.T:run_triggers(false) tarantool> box.space.T:on_replace(nil, F) tarantool> box.space.T:on_replace(nil, F)
-
space_object:
select
([key[, options]]) Search for a tuple or a set of tuples in the given space. This method doesn’t yield (for details see Сooperative multitasking).
Parameters: - space_object (space_object) – an object reference
- key (scalar/table) – value to be matched against the index key, which may be multi-part.
- options (table/nil) –
none, any or all of the same options that index_object:select allows:
options.iterator
(type of iterator)options.limit
(maximum number of tuples)options.offset
(number of tuples to skip)
Return: the tuples whose primary-key fields are equal to the fields of the passed key. If the number of passed fields is less than the number of fields in the primary key, then only the passed fields are compared, so
select{1,2}
will match a tuple whose primary key is{1,2,3}
.Rtype: array of tuples
A
select
request can also be done with a specific index and index options, which are the subject of index_object:select.Possible errors:
- no such space;
- wrong type.
Complexity factors: Index size, Index type.
Example:
tarantool> s = box.schema.space.create('tmp', {temporary=true}) --- ... tarantool> s:create_index('primary',{parts = {{field = 1, type = 'unsigned'}, {field = 2, type = 'string'}} }) --- ... tarantool> s:insert{1,'A'} --- - [1, 'A'] ... tarantool> s:insert{1,'B'} --- - [1, 'B'] ... tarantool> s:insert{1,'C'} --- - [1, 'C'] ... tarantool> s:insert{2,'D'} --- - [2, 'D'] ... tarantool> -- must equal both primary-key fields tarantool> s:select{1,'B'} --- - - [1, 'B'] ... tarantool> -- must equal only one primary-key field tarantool> s:select{1} --- - - [1, 'A'] - [1, 'B'] - [1, 'C'] ... tarantool> -- must equal 0 fields, so returns all tuples tarantool> s:select{} --- - - [1, 'A'] - [1, 'B'] - [1, 'C'] - [2, 'D'] ... tarantool> -- the first field must be greater than 0, and tarantool> -- skip the first tuple, and return up to tarantool> -- 2 tuples. This example's options all tarantool> -- depend on index characteristics so see tarantool> -- more explanation in index_object:select(). tarantool> s:select({0},{iterator='GT',offset=1,limit=2}) --- - - [1, 'B'] - [1, 'C'] ...
As the last request in the above example shows: to make complex
select
requests, where you can specify which index to search and what condition to use (for example “greater than” instead of “equal to”) and how many tuples to return, it will be necessary to become familiar with index_object:select.Remember that you can get a field from a tuple both by field number and by field name which is more convenient. See example: using field names instead of field numbers.
For more usage scenarios and typical errors see Example: using data operations further in this section.
-
space_object:
truncate
() Deletes all tuples. The method is performed in background and doesn’t block consequent requests.
Parameters: - space_object (space_object) – an object reference
Complexity factors: Index size, Index type, Number of tuples accessed.
Return: nil The
truncate
method can only be called by the user who created the space, or from within asetuid
function created by the user who created the space. Read more about setuid functions in the reference for box.schema.func.create().The
truncate
method cannot be called from within a transaction.Example:
tarantool> box.space.tester:truncate() --- ... tarantool> box.space.tester:len() --- - 0 ...
-
space_object:
update
(key, {{operator, field_identifier, value}, ...}) Update a tuple.
The
update
function supports operations on fields — assignment, arithmetic (if the field is numeric), cutting and pasting fragments of a field, deleting or inserting a field. Multiple operations can be combined in a single update request, and in this case they are performed atomically and sequentially. Each operation requires specification of a field identifier, which is usually a number. When multiple operations are present, the field number for each operation is assumed to be relative to the most recent state of the tuple, that is, as if all previous operations in a multi-operation update have already been applied. In other words, it is always safe to merge multipleupdate
invocations into a single invocation, with no change in semantics.Possible operators are:
+
for addition. values must be numeric, e.g. unsigned or decimal-
for subtraction. values must be numeric&
for bitwise AND. values must be unsigned numeric|
for bitwise OR. values must be unsigned numeric^
for bitwise XOR. values must be unsigned numeric:
for string splice.!
for insertion of a new field.#
for deletion.=
for assignment.
Possible field_identifiers are:
- Positive field number. The first field is 1, the second field is 2, and so on.
- Negative field number. The last field is -1, the second-last field is -2, and so on. In other words: (#tuple + negative field number + 1).
- Name. If the space was formatted with space_object:format(), then this can be a string for the field ‘name’.
Parameters: - space_object (space_object) – an object reference
- key (scalar/table) – primary-key field values, must be passed as a Lua table if key is multi-part
- operator (string) – operation type represented in string
- field_identifier (number-or-string) – what field the operation will apply to.
- value (lua_value) – what value will be applied
Return: - the updated tuple
- nil if the key is not found
Rtype: tuple or nil
Possible errors: it is illegal to modify a primary-key field.
Complexity factors: Index size, Index type, number of indexes accessed, WAL settings.
Thus, in the instruction:
s:update(44, {{'+', 1, 55 }, {'=', 3, 'x'}})
the primary-key value is
44
, the operators are'+'
and'='
meaning add a value to a field and then assign a value to a field, the first affected field is field1
and the value which will be added to it is55
, the second affected field is field3
and the value which will be assigned to it is'x'
.Example:
Assume that initially there is a space named
tester
with a primary-key index whose type isunsigned
. There is one tuple, withfield[1]
=999
andfield[2]
='A'
.In the update:
box.space.tester:update(999, {{'=', 2, 'B'}})
The first argument istester
, that is, the affected space istester
. The second argument is999
, that is, the affected tuple is identified by primary key value = 999. The third argument is=
, that is, there is one operation — assignment to a field. The fourth argument is2
, that is, the affected field isfield[2]
. The fifth argument is'B'
, that is,field[2]
contents change to'B'
. Therefore, after this update,field[1]
=999
andfield[2]
='B'
.In the update:
box.space.tester:update({999}, {{'=', 2, 'B'}})
the arguments are the same, except that the key is passed as a Lua table (inside braces). This is unnecessary when the primary key has only one field, but would be necessary if the primary key had more than one field. Therefore, after this update,field[1]
=999
andfield[2]
='B'
(no change).In the update:
box.space.tester:update({999}, {{'=', 3, 1}})
the arguments are the same, except that the fourth argument is3
, that is, the affected field isfield[3]
. It is okay that, until now,field[3]
has not existed. It gets added. Therefore, after this update,field[1]
=999
,field[2]
='B'
,field[3]
=1
.In the update:
box.space.tester:update({999}, {{'+', 3, 1}})
the arguments are the same, except that the third argument is'+'
, that is, the operation is addition rather than assignment. Sincefield[3]
previously contained1
, this means we’re adding1
to1
. Therefore, after this update,field[1]
=999
,field[2]
='B'
,field[3]
=2
.In the update:
box.space.tester:update({999}, {{'|', 3, 1}, {'=', 2, 'C'}})
the idea is to modify two fields at once. The formats are'|'
and=
, that is, there are two operations, OR and assignment. The fourth and fifth arguments mean thatfield[3]
gets OR’ed with1
. The seventh and eighth arguments mean thatfield[2]
gets assigned'C'
. Therefore, after this update,field[1]
=999
,field[2]
='C'
,field[3]
=3
.In the update:
box.space.tester:update({999}, {{'#', 2, 1}, {'-', 2, 3}})
The idea is to deletefield[2]
, then subtract3
fromfield[3]
. But after the delete, there is a renumbering, sofield[3]
becomesfield[2]
before we subtract3
from it, and that’s why the seventh argument is2
, not3
. Therefore, after this update,field[1]
=999
,field[2]
=0
.In the update:
box.space.tester:update({999}, {{'=', 2, 'XYZ'}})
we’re making a long string so that splice will work in the next example. Therefore, after this update,field[1]
=999
,field[2]
='XYZ'
.In the update:
box.space.tester:update({999}, {{':', 2, 2, 1, '!!'}})
The third argument is':'
, that is, this is the example of splice. The fourth argument is2
because the change will occur infield[2]
. The fifth argument is 2 because deletion will begin with the second byte. The sixth argument is 1 because the number of bytes to delete is 1. The seventh argument is'!!'
, because'!!'
is to be added at this position. Therefore, after this update,field[1]
=999
,field[2]
='X!!Z'
.For more usage scenarios and typical errors see Example: using data operations further in this section.
Since Tarantool 2.3 a tuple can also be updated via JSON paths.
-
space_object:
upsert
({tuple}, {{operator, field_identifier, value}, ...}) Update or insert a tuple.
If there is an existing tuple which matches the key fields of
tuple
, then the request has the same effect as space_object:update() and the{{operator, field_identifier, value}, ...}
parameter is used. If there is no existing tuple which matches the key fields oftuple
, then the request has the same effect as space_object:insert() and the{tuple}
parameter is used. However, unlikeinsert
orupdate
,upsert
will not read a tuple and perform error checks before returning – this is a design feature which enhances throughput but requires more caution on the part of the user.Parameters: - space_object (space_object) – an object reference
- tuple (table/tuple) – default tuple to be inserted, if analogue isn’t found
- operator (string) – operation type represented in string
- field_identifier (number) – what field the operation will apply to
- value (lua_value) – what value will be applied
Return: null
Possible errors:
- It is illegal to modify a primary-key field.
- It is illegal to use upsert with a space that has a unique secondary index.
Complexity factors: Index size, Index type, number of indexes accessed, WAL settings.
Example:
box.space.tester:upsert({12,'c'}, {{'=', 3, 'a'}, {'=', 4, 'b'}})
For more usage scenarios and typical errors see Example: using data operations further in this section.
-
space_object:
user_defined
() Users can define any functions they want, and associate them with spaces: in effect they can make their own space methods. They do this by:
- creating a Lua function,
- adding the function name to a predefined global variable which has type = table, and
- invoking the function any time thereafter, as long as the server
is up, by saying
space_object:function-name([parameters])
.
The predefined global variable is
box.schema.space_mt
. Adding tobox.schema.space_mt
makes the method available for all spaces.Alternatively, user-defined methods can be made available for only one space, by calling
getmetatable(space_object)
and then adding the function name to the meta table. See also the example for index_object:user_defined().Parameters: - index_object (index_object) – an object reference.
- any-name (any-type) – whatever the user defines
Example:
-- Visible to any space, no parameters. -- After these requests, the value of global_variable will be 6. box.schema.space.create('t') box.space.t:create_index('i') global_variable = 5 function f(space_arg) global_variable = global_variable + 1 end box.schema.space_mt.counter = f box.space.t:counter()
-
space_object:
create_check_constraint
(check_constraint_name, expression) Create a check constraint. A check constraint is a requirement that must be met when a tuple is inserted or updated in a space. Check constraints created with
space_object:create_check_constraint
have the same effect as check constraints created with an SQL CHECK() clause in a CREATE TABLE statement.Parameters: - space_object (space_object) – an object reference
- check_constraint_name (string) – name of check constraint, which should conform to the rules for object names
- expression (string) – SQL code of an expression which must return a boolean result
Return: check constraint object
Rtype: check_constraint_object
The space must be formatted with space_object:format() so that the expression can contain field names. The space must be empty. The space must not be a system space.
The expression must return true or false and should be deterministic. The expression may be any SQL (not Lua) expression containing field names, built-in function names, literals, and operators. Not subqueries. If a field name contains lower case characters, it must be enclosed in “double quotes”.
Check constraints are checked before the request is performed, at the same time as Lua before_replace triggers. If there is more than one check constraint or before_replace trigger, then they are ordered according to time of creation. (This is a change from the earlier behavior of check constraints, which caused checking before the tuple was formed.)
Check constraints can be dropped with
space_object.ck_constraint.check_constraint_name:drop()
.Check constraints can be disabled with
space_object.ck_constraint.check_constraint_name:enable(false)
orcheck_constraint_object:enable(false)
. Check constraints can be enabled withspace_object.ck_constraint.check_constraint_name:enable(true)
orcheck_constraint_object:enable(true)
. By default a check constraint is ‘enabled’ which means that the check is performed whenever the request is performed, but can be changed to ‘disabled’ which means that the check is not performed.During the recovery process, for example when the Tarantool server is starting, the check is not performed unless force_recovery. is specified.
Example:
box.schema.space.create('t') box.space.t:format({{name = 'f1', type = 'unsigned'}, {name = 'f2', type = 'string'}, {name = 'f3', type = 'string'}}) box.space.t:create_index('i') box.space.t:create_check_constraint('c1', [["f2" > 'A']]) box.space.t:create_check_constraint('c2', [["f2"=UPPER("f3") AND NOT "f2" LIKE '__']]) -- This insert will fail, constraint c1 expression returns false box.space.t:insert{1, 'A', 'A'} -- This insert will fail, constraint c2 expression returns false box.space.t:insert{1, 'B', 'c'} -- This insert will succeed, both constraint expressions return true box.space.t:insert{1, 'B', 'b'} -- This update will fail, constraint c2 expression returns false box.space.t:update(1, {{'=', 2, 'xx'}, {'=', 3, 'xx'}})
A list of check constraints is in space_object._ck_constraint.
-
space_object.
enabled
Whether or not this space is enabled. The value is
false
if the space has no index.
-
space_object.
field_count
The required field count for all tuples in this space. The field_count can be set initially with:
box.schema.space.create(..., { ... , field_count = *field_count_value* , ... })
The default value is
0
, which means there is no required field count.Example:
tarantool> box.space.tester.field_count --- - 0 ...
-
-
box.space.
index
A container for all defined indexes. There is a Lua object of type box.index with methods to search tuples and iterate over them in predefined order.
To reset, use box.stat.reset().
Rtype: table Example:
# checking the number of indexes for space 'tester' tarantool> local counter=0; for i=0,#box.space.tester.index do if box.space.tester.index[i]~=nil then counter=counter+1 end end; print(counter) 1 --- ... # checking the type of index 'primary' tarantool> box.space.tester.index.primary.type --- - TREE ...
-
box.space.
_cluster
_cluster
is a system space for support of the replication feature.
-
box.space.
_func
_func
is a system space with function tuples made by box.schema.func.create() or box.schema.func.create(func-name [, {options-with-body}]).Tuples in this space contain the following fields:
- id (integer identifier),
- owner (integer identifier),
- the function name,
- the setuid flag,
- a language name (optional): ‘LUA’ (default) or ‘C’.
- the body
- the is_deterministic flag
- the is_sandboxed flag
- options
If the function tuple was made in the older way without specification of
body
, then the_func
space will contain default values for the body and the is_deterministic flag and the is_sandboxed flag. Such function tuples are called “not persistent”. You continue to create Lua functions in the usual way, by sayingfunction function_name () ... end
, without adding anything in the_func
space. The_func
space only exists for storing function tuples so that their names can be used within grant/revoke functions.If the function tuple was made the newer way with specification of
body
, then all the fields may contain non-default values. Such functions are called “persistent”. They should be invoked withbox.func.func-name:call([parameters])
.You can:
- Create a
_func
tuple with box.schema.func.create(), - Drop a
_func
tuple with box.schema.func.drop(), - Check whether a
_func
tuple exists with box.schema.func.exists().
Example:
In the following example, we create a function named ‘f7’, put it into Tarantool’s
_func
space and grant ‘execute’ privilege for this function to ‘guest’ user.tarantool> function f7() > box.session.uid() > end --- ... tarantool> box.schema.func.create('f7') --- ... tarantool> box.schema.user.grant('guest', 'execute', 'function', 'f7') --- ... tarantool> box.schema.user.revoke('guest', 'execute', 'function', 'f7') --- ...
-
box.space.
_index
_index
is a system space.Tuples in this space contain the following fields:
id
(= id of space),iid
(= index number within space),name
,type
,opts
(e.g. unique option), [tuple-field-no
,tuple-field-type
…].
Here is what
_index
contains in a typical installation:tarantool> box.space._index:select{} --- - - [272, 0, 'primary', 'tree', {'unique': true}, [[0, 'string']]] - [280, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned']]] - [280, 1, 'owner', 'tree', {'unique': false}, [[1, 'unsigned']]] - [280, 2, 'name', 'tree', {'unique': true}, [[2, 'string']]] - [281, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned']]] - [281, 1, 'owner', 'tree', {'unique': false}, [[1, 'unsigned']]] - [281, 2, 'name', 'tree', {'unique': true}, [[2, 'string']]] - [288, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned'], [1, 'unsigned']]] - [288, 2, 'name', 'tree', {'unique': true}, [[0, 'unsigned'], [2, 'string']]] - [289, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned'], [1, 'unsigned']]] - [289, 2, 'name', 'tree', {'unique': true}, [[0, 'unsigned'], [2, 'string']]] - [296, 0, 'primary', 'tree', {'unique': true}, [[0, 'unsigned']]] - [296, 1, 'owner', 'tree', {'unique': false}, [[1, 'unsigned']]] - [296, 2, 'name', 'tree', {'unique': true}, [[2, 'string']]] --- ...
-
box.space.
_vindex
_vindex
is a system space that represents a virtual view. The structure of its tuples is identical to that of _index, but permissions for certain tuples are limited in accordance with user privileges._vindex
contains only those tuples that are accessible to the current user. See Access control for details about user privileges.If the user has the full set of privileges (like ‘admin’), the contents of
_vindex
match the contents of_index
. If the user has limited access,_vindex
contains only tuples accessible to this user.Note
_vindex
is a system view, so it allows only read requests.- While the
_index
space requires proper access privileges, any user can always read from_vindex
.
-
box.space.
_priv
_priv
is a system space where privileges are stored.Tuples in this space contain the following fields:
- the numeric id of the user who gave the privilege (“grantor_id”),
- the numeric id of the user who received the privilege (“grantee_id”),
- the type of object: ‘space’, ‘index’, ‘function’, ‘sequence’, ‘user’, ‘role’, or ‘universe’,
- the numeric id of the object,
- the type of operation: “read” = 1, “write” = 2, “execute” = 4, “create” = 32, “drop” = 64, “alter” = 128, or a combination such as “read,write,execute”.
You can:
- Grant a privilege with box.schema.user.grant().
- Revoke a privilege with box.schema.user.revoke().
Note
- Generally, privileges are granted or revoked by the owner of the object (the user who created it), or by the ‘admin’ user.
- Before dropping any objects or users, make sure that all their associated privileges have been revoked.
- Only the ‘admin’ user can grant privileges for the ‘universe’.
- Only the ‘admin’ user or the creator of a space can drop, alter, or truncate the space.
- Only the ‘admin’ user or the creator of a user can change a different user’s password.
-
box.space.
_vpriv
_vpriv
is a system space that represents a virtual view. The structure of its tuples is identical to that of _priv, but permissions for certain tuples are limited in accordance with user privileges._vpriv
contains only those tuples that are accessible to the current user. See Access control for details about user privileges.If the user has the full set of privileges (like ‘admin’), the contents of
_vpriv
match the contents of_priv
. If the user has limited access,_vpriv
contains only tuples accessible to this user.Note
_vpriv
is a system view, so it allows only read requests.- While the
_priv
space requires proper access privileges, any user can always read from_vpriv
.
-
box.space.
_schema
_schema
is a system space.This space contains the following tuples:
version
tuple with version information for this Tarantool instance,cluster
tuple with the instance’s replica set ID,max_id
tuple with the maximal space ID,once...
tuples that correspond to specific box.once() blocks from the instance’s initialization file. The first field in these tuples contains thekey
value from the correspondingbox.once()
block prefixed with ‘once’ (e.g. oncehello), so you can easily find a tuple that corresponds to a specificbox.once()
block.
Example:
Here is what
_schema
contains in a typical installation (notice the tuples for twobox.once()
blocks,'oncebye'
and'oncehello'
):tarantool> box.space._schema:select{} --- - - ['cluster', 'b4e15788-d962-4442-892e-d6c1dd5d13f2'] - ['max_id', 512] - ['oncebye'] - ['oncehello'] - ['version', 1, 7, 2]
-
box.space.
_sequence
_sequence
is a system space for support of the sequence feature. It contains persistent information that was established by box.schema.sequence.create() or box.schema.sequence.alter().
-
box.space.
_sequence_data
_sequence_data
is a system space for support of the sequence feature.Each tuple in
_sequence_data
contains two fields:- the id of the sequence, and
- the last value that the sequence generator returned (non-persistent information).
There is no guarantee that this space will be updated immediately after every data-change request.
-
box.space.
_space
_space
is a system space. It contains all spaces hosted on the current Tarantool instance, both system ones and created by users.Tuples in this space contain the following fields:
id
,owner
(= id of user who owns the space),name
,engine
,field_count
,flags
(e.g. temporary),format
(as made by a format clause).
These fields are established by space.create().
Example #1:
The following function will display every simple field in all tuples of
_space
.function example() local ta = {} local i, line for k, v in box.space._space:pairs() do i = 1 line = '' while i <= #v do if type(v[i]) ~= 'table' then line = line .. v[i] .. ' ' end i = i + 1 end table.insert(ta, line) end return ta end
Here is what
example()
returns in a typical installation:tarantool> example() --- - - '272 1 _schema memtx 0 ' - '280 1 _space memtx 0 ' - '281 1 _vspace sysview 0 ' - '288 1 _index memtx 0 ' - '296 1 _func memtx 0 ' - '304 1 _user memtx 0 ' - '305 1 _vuser sysview 0 ' - '312 1 _priv memtx 0 ' - '313 1 _vpriv sysview 0 ' - '320 1 _cluster memtx 0 ' - '512 1 tester memtx 0 ' - '513 1 origin vinyl 0 ' - '514 1 archive memtx 0 ' ...
Example #2:
The following requests will create a space using
box.schema.space.create()
with a format clause, then retrieve the_space
tuple for the new space. This illustrates the typical use of theformat
clause, it shows the recommended names and data types for the fields.tarantool> box.schema.space.create('TM', { > id = 12345, > format = { > [1] = {["name"] = "field_1"}, > [2] = {["type"] = "unsigned"} > } > }) --- - index: [] on_replace: 'function: 0x41c67338' temporary: false id: 12345 engine: memtx enabled: false name: TM field_count: 0 - created ... tarantool> box.space._space:select(12345) --- - - [12345, 1, 'TM', 'memtx', 0, {}, [{'name': 'field_1'}, {'type': 'unsigned'}]] ...
-
box.space.
_vspace
_vspace
is a system space that represents a virtual view. The structure of its tuples is identical to that of _space, but permissions for certain tuples are limited in accordance with user privileges._vspace
contains only those tuples that are accessible to the current user. See Access control for details about user privileges.If the user has the full set of privileges (like ‘admin’), the contents of
_vspace
match the contents of_space
. If the user has limited access,_vspace
contains only tuples accessible to this user.Note
_vspace
is a system view, so it allows only read requests.- While the
_space
space requires proper access privileges, any user can always read from_vspace
.
-
box.space.
_user
_user
is a system space where user-names and password hashes are stored.Tuples in this space contain the following fields:
- the numeric id of the tuple (“id”),
- the numeric id of the tuple’s creator,
- the name,
- the type: ‘user’ or ‘role’,
- optional password.
There are five special tuples in the
_user
space: ‘guest’, ‘admin’, ‘public’, ‘replication’, and ‘super’.Name ID Type Description guest 0 user Default user when connecting remotely. Usually an untrusted user with few privileges. admin 1 user Default user when using Tarantool as a console. Usually an administrative user with all privileges. public 2 role Pre-defined role, automatically granted to new users when they are created with box.schema.user.create(user-name)
. Therefore a convenient way to grant ‘read’ on space ‘t’ to every user that will ever exist is withbox.schema.role.grant('public','read','space','t')
.replication 3 role Pre-defined role, which the ‘admin’ user can grant to users who need to use replication features. super 31 role Pre-defined role, which the ‘admin’ user can grant to users who need all privileges on all objects. The ‘super’ role has these privileges on ‘universe’: read, write, execute, create, drop, alter. To select a tuple from the
_user
space, usebox.space._user:select()
. For example, here is what happens with a select for user id = 0, which is the ‘guest’ user, which by default has no password:tarantool> box.space._user:select{0} --- - - [0, 1, 'guest', 'user'] ...
Warning
To change tuples in the
_user
space, do not use ordinarybox.space
functions for insert or update or delete. The_user
space is special, so there are special functions which have appropriate error checking.To create a new user, use box.schema.user.create():
box.schema.user.create(*user-name*) box.schema.user.create(*user-name*, {if_not_exists = true}) box.schema.user.create(*user-name*, {password = *password*})
To change the user’s password, use box.schema.user.password():
-- To change the current user's password box.schema.user.passwd(*password*) -- To change a different user's password -- (usually only 'admin' can do it) box.schema.user.passwd(*user-name*, *password*)
To drop a user, use box.schema.user.drop():
box.schema.user.drop(*user-name*)
To check whether a user exists, use box.schema.user.exists(), which returns
true
orfalse
:box.schema.user.exists(*user-name*)
To find what privileges a user has, use box.schema.user.info():
box.schema.user.info(*user-name*)
Note
The maximum number of users is 32.
Example:
Here is a session which creates a new user with a strong password, selects a tuple in the
_user
space, and then drops the user.tarantool> box.schema.user.create('JeanMartin', {password = 'Iwtso_6_os$$'}) --- ... tarantool> box.space._user.index.name:select{'JeanMartin'} --- - - [17, 1, 'JeanMartin', 'user', {'chap-sha1': 't3xjUpQdrt857O+YRvGbMY5py8Q='}] ... tarantool> box.schema.user.drop('JeanMartin') --- ...
-
box.space.
_ck_constraint
_ck_constraint
is a system space where check constraints are stored.Tuples in this space contain the following fields:
- the numeric id of the space (“space_id”),
- the name,
- whether the check is deferred (“is_deferred”),
- the language of the expression, such as ‘SQL’,
- the expression (“code”)
Example:
tarantool> box.space._ck_constraint:select() --- - - [527, 'c1', false, 'SQL', '"f2" > ''A'''] - [527, 'c2', false, 'SQL', '"f2" == UPPER("f3") AND NOT "f2" LIKE ''__'''] ...
This function will illustrate how to look at all the spaces, and for each
display: approximately how many tuples it contains, and the first field of
its first tuple. The function uses Tarantool box.space
functions len()
and pairs()
. The iteration through the spaces is coded as a scan of the
_space
system space, which contains metadata. The third field in
_space
contains the space name, so the key instruction
space_name = v[3]
means space_name
is the space_name
field in
the tuple of _space
that we’ve just fetched with pairs()
. The function
returns a table:
function example()
local tuple_count, space_name, line
local ta = {}
for k, v in box.space._space:pairs() do
space_name = v[3]
if box.space[space_name].index[0] ~= nil then
tuple_count = '1 or more'
else
tuple_count = '0'
end
line = space_name .. ' tuple_count =' .. tuple_count
if tuple_count == '1 or more' then
for k1, v1 in box.space[space_name]:pairs() do
line = line .. '. first field in first tuple = ' .. v1[1]
break
end
end
table.insert(ta, line)
end
return ta
end
And here is what happens when one invokes the function:
tarantool> example()
---
- - _schema tuple_count =1 or more. first field in first tuple = cluster
- _space tuple_count =1 or more. first field in first tuple = 272
- _vspace tuple_count =1 or more. first field in first tuple = 272
- _index tuple_count =1 or more. first field in first tuple = 272
- _vindex tuple_count =1 or more. first field in first tuple = 272
- _func tuple_count =1 or more. first field in first tuple = 1
- _vfunc tuple_count =1 or more. first field in first tuple = 1
- _user tuple_count =1 or more. first field in first tuple = 0
- _vuser tuple_count =1 or more. first field in first tuple = 0
- _priv tuple_count =1 or more. first field in first tuple = 1
- _vpriv tuple_count =1 or more. first field in first tuple = 1
- _cluster tuple_count =1 or more. first field in first tuple = 1
...
The objective is to display field names and field types of a system space – using metadata to find metadata.
To begin: how can one select the _space
tuple that describes _space
?
A simple way is to look at the constants in box.schema
,
which tell us that there is an item named SPACE_ID == 288,
so these statements will retrieve the correct tuple:
box.space._space:select{ 288 }
-- or --
box.space._space:select{ box.schema.SPACE_ID }
Another way is to look at the tuples in box.space._index
,
which tell us that there is a secondary index named ‘name’ for space
number 288, so this statement also will retrieve the correct tuple:
box.space._space.index.name:select{ '_space' }
However, the retrieved tuple is not easy to read:
tarantool> box.space._space.index.name:select{'_space'}
---
- - [280, 1, '_space', 'memtx', 0, {}, [{'name': 'id', 'type': 'num'}, {'name': 'owner',
'type': 'num'}, {'name': 'name', 'type': 'str'}, {'name': 'engine', 'type': 'str'},
{'name': 'field_count', 'type': 'num'}, {'name': 'flags', 'type': 'str'}, {
'name': 'format', 'type': '*'}]]
...
It looks disorganized because field number 7 has been formatted with recommended names and data types. How can one get those specific sub-fields? Since it’s visible that field number 7 is an array of maps, this for loop will do the organizing:
tarantool> do
> local tuple_of_space = box.space._space.index.name:get{'_space'}
> for _, field in ipairs(tuple_of_space[7]) do
> print(field.name .. ', ' .. field.type)
> end
> end
id, num
owner, num
name, str
engine, str
field_count, num
flags, str
format, *
---
...
-
box.space.
_vuser
_vuser
is a system space that represents a virtual view. The structure of its tuples is identical to that of _user, but permissions for certain tuples are limited in accordance with user privileges._vuser
contains only those tuples that are accessible to the current user. See Access control for details about user privileges.If the user has the full set of privileges (like ‘admin’), the contents of
_vuser
match the contents of_user
. If the user has limited access,_vuser
contains only tuples accessible to this user.To see how
_vuser
works, connect to a Tarantool database remotely vianet.box
and select all tuples from the_user
space, both when the ‘guest’ user is and is not allowed to read from the database.First, start Tarantool and grant the ‘guest’ user with read, write and execute privileges:
tarantool> box.cfg{listen = 3301} --- ... tarantool> box.schema.user.grant('guest', 'read,write,execute', 'universe') --- ...
Switch to the other terminal, connect to the Tarantool instance and select all tuples from the
_user
space:tarantool> conn = require('net.box').connect(3301) --- ... tarantool> conn.space._user:select{} --- - - [0, 1, 'guest', 'user', {}] - [1, 1, 'admin', 'user', {}] - [2, 1, 'public', 'role', {}] - [3, 1, 'replication', 'role', {}] - [31, 1, 'super', 'role', {}] ...
This result contains the same set of users as if you made the request from your Tarantool instance as ‘admin’.
Switch to the first terminal and revoke the read privileges from the ‘guest’ user:
tarantool> box.schema.user.revoke('guest', 'read', 'universe') --- ...
Switch to the other terminal, stop the session (to stop
tarantool
type Ctrl+C or Ctrl+D), start again, connect again, and repeat theconn.space._user:select{}
request. The access is denied:tarantool> conn.space._user:select{} --- - error: Read access to space '_user' is denied for user 'guest' ...
However, if you select from
_vuser
instead, the users’ data available for the ‘guest’ user is displayed:tarantool> conn.space._vuser:select{} --- - - [0, 1, 'guest', 'user', {}] ...
Note
_vuser
is a system view, so it allows only read requests.- While the
_user
space requires proper access privileges, any user can always read from_vuser
.
-
box.space.
_collation
_collation
is a system space with a list of collations. There are over 270 built-in collations and users may add more. Here is one example:tarantool> box.space._collation:select(239) --- - - [239, 'unicode_uk_s2', 1, 'ICU', 'uk', {'strength': 'secondary'}] ...
Explanation of the fields in the example: id = 239 i.e. Tarantool’s primary key is 239, name = ‘unicode_uk_s2’ i.e. according to Tarantool’s naming convention this is a Unicode collation + it is for the uk locale + it has secondary strength, owner = 1 i.e. the admin user, type = ‘ICU’ i.e. the rules are according to International Components for Unicode, locale = ‘uk’ i.e. Ukrainian, opts = ‘strength:secondary’ i.e. with this collation comparisons use both primary and secondary weights.
-
box.space.
_vcollation
_vcollation
is a system space with a list of collations. The structure of its tuples is identical to that of box.space._collation, but permissions for certain tuples are limited in accordance with user privileges.
-
box.space.
_session_settings
_session_settings
is a temporary system space with a list of settings that may affect behavior, particularly SQL behavior, for the current session. It uses a special engine named ‘service’. Every ‘service’ tuple is created on the fly, that is, new tuples are made every time_session_settings
is accessed. Every settings tuple has two fields:name
(the primary key) andvalue
. The tuples’ names and default values are:
error_marshaling_enabled
: whether error objects have a special structure. Default = false.
sql_default_engine
: default storage engine for new SQL tables. Default = ‘memtx’.
sql_defer_foreign_keys
: whether foreign-key checks can wait till commit. Default = false.
sql_full_column_names
: no effect at this time. Default = false.
sql_full_metadata
: whether SQL result set metadata will have more than just name and type. Default = false.
sql_parser_debug
: whether to show parser steps for following statements. Default = false.
sql_recursive_triggers
: whether a triggered statement can activate a trigger. Default = true.
sql_reverse_unordered_selects
: whether result rows are usually in reverse order if there is no ORDER BY clause. Default = false.
sql_select_debug
: whether to show execution steps during SELECT. Default = false.
sql_vdbe_debug
: for use by Tarantool’s developers. Default = false.
Three requests are possible: select and get and update. For example, afters = box.space._session_settings
,s:select('sql_default_engine')
probably returns{'sql_default_engine', 'memtx'}
, ands:update('sql_default_engine', {{'=', 'value', 'vinyl'}})
changes the default engine to ‘vinyl’.
Updatingsql_parser_debug
orsql_select_debug
orsql_vdbe_debug
has no effect unless Tarantool was built with -DCMAKE_BUILD_TYPE=Debug. To check if this is so, look atrequire('tarantool').build.target
.
This example demonstrates all legal scenarios – as well as typical errors – for each data operation in Tarantool: INSERT, DELETE, UPDATE, UPSERT, REPLACE, and SELECT.
-- Bootstrap the database --
box.cfg{}
format = {}
format[1] = {'field1', 'unsigned'}
format[2] = {'field2', 'unsigned'}
format[3] = {'field3', 'unsigned'}
s = box.schema.create_space('test', {format = format})
-- Create a primary index --
pk = s:create_index('pk', {parts = {{field = 'field1'}}})
-- Create a unique secondary index --
sk_uniq = s:create_index('sk_uniq', {parts = {{field = 'field2'}}})
-- Create a non-unique secondary index --
sk_non_uniq = s:create_index('sk_non_uniq', {parts = {{field = 'field3'}}, unique = false})
insert
accepts a well-formatted tuple and checks all keys for duplicates.
tarantool> -- Unique indexes: ok --
tarantool> s:insert({1, 1, 1})
---
- [1, 1, 1]
...
tarantool> -- Conflicting primary key: error --
tarantool> s:insert({1, 1, 1})
---
- error: Duplicate key exists in unique index 'pk' in space 'test'
...
tarantool> -- Conflicting unique secondary key: error --
tarantool> s:insert({2, 1, 1})
---
- error: Duplicate key exists in unique index 'sk_uniq' in space 'test'
...
tarantool> -- Key {1} exists in sk_non_uniq index, but it is not unique: ok --
tarantool> s:insert({2, 2, 1})
---
- [2, 2, 1]
...
tarantool> s:truncate()
---
...
delete
accepts a full key of any unique index.
space:delete
is an alias for “delete by primary key”.
tarantool> -- Insert some test data --
tarantool> s:insert{3, 4, 5}
---
- [3, 4, 5]
...
tarantool> s:insert{6, 7, 8}
---
- [6, 7, 8]
...
tarantool> s:insert{9, 10, 11}
---
- [9, 10, 11]
...
tarantool> s:insert{12, 13, 14}
---
- [12, 13, 14]
...
tarantool> -- Nothing done here: no {4} key in pk index --
tarantool> s:delete{4}
---
...
tarantool> s:select{}
---
- - [3, 4, 5]
- [6, 7, 8]
- [9, 10, 11]
- [12, 13, 14]
...
tarantool> -- Delete by a primary key: ok --
tarantool> s:delete{3}
---
- [3, 4, 5]
...
tarantool> s:select{}
---
- - [6, 7, 8]
- [9, 10, 11]
- [12, 13, 14]
...
tarantool> -- Explicitly delete by a primary key: ok --
tarantool> s.index.pk:delete{6}
---
- [6, 7, 8]
...
tarantool> s:select{}
---
- - [9, 10, 11]
- [12, 13, 14]
...
tarantool> -- Delete by a unique secondary key: ok --
s.index.sk_uniq:delete{10}
---
- [9, 10, 11]
...
s:select{}
---
- - [12, 13, 14]
...
tarantool> -- Delete by a non-unique secondary index: error --
tarantool> s.index.sk_non_uniq:delete{14}
---
- error: Get() doesn't support partial keys and non-unique indexes
...
tarantool> s:select{}
---
- - [12, 13, 14]
...
tarantool> s:truncate()
---
...
The key must be full: delete
cannot work with partial keys.
tarantool> s2 = box.schema.create_space('test2')
---
...
tarantool> pk2 = s2:create_index('pk2', {parts = {{field = 1, type = 'unsigned'}, {field = 2, type = 'unsigned'}}})
---
...
tarantool> s2:insert{1, 1}
---
- [1, 1]
...
tarantool> -- Delete by a partial key: error --
tarantool> s2:delete{1}
---
- error: Invalid key part count in an exact match (expected 2, got 1)
...
tarantool> -- Delete by a full key: ok --
tarantool> s2:delete{1, 1}
---
- [1, 1]
...
tarantool> s2:select{}
---
- []
...
tarantool> s2:drop()
---
...
Similarly to delete
, update
accepts a full key of any unique index,
and also the operations to execute.
space:update
is an alias for “update by primary key”.
tarantool> -- Insert some test data --
tarantool> s:insert{3, 4, 5}
---
- [3, 4, 5]
...
tarantool> s:insert{6, 7, 8}
---
- [6, 7, 8]
...
tarantool> s:insert{9, 10, 11}
---
- [9, 10, 11]
...
tarantool> s:insert{12, 13, 14}
---
- [12, 13, 14]
...
tarantool> -- Nothing done here: no {4} key in pk index --
s:update({4}, {{'=', 2, 400}})
---
...
tarantool> s:select{}
---
- - [3, 4, 5]
- [6, 7, 8]
- [9, 10, 11]
- [12, 13, 14]
...
tarantool> -- Update by a primary key: ok --
tarantool> s:update({3}, {{'=', 2, 400}})
---
- [3, 400, 5]
...
tarantool> s:select{}
---
- - [3, 400, 5]
- [6, 7, 8]
- [9, 10, 11]
- [12, 13, 14]
...
tarantool> -- Explicitly update by a primary key: ok --
tarantool> s.index.pk:update({6}, {{'=', 2, 700}})
---
- [6, 700, 8]
...
tarantool> s:select{}
---
- - [3, 400, 5]
- [6, 700, 8]
- [9, 10, 11]
- [12, 13, 14]
...
tarantool> -- Update by a unique secondary key: ok --
tarantool> s.index.sk_uniq:update({10}, {{'=', 2, 1000}})
---
- [9, 1000, 11]
...
tarantool> s:select{}
---
- - [3, 400, 5]
- [6, 700, 8]
- [9, 1000, 11]
- [12, 13, 14]
...
tarantool> -- Update by a non-unique secondary key: error --
tarantool> s.index.sk_non_uniq:update({14}, {{'=', 2, 1300}})
---
- error: Get() doesn't support partial keys and non-unique indexes
...
tarantool> s:select{}
---
- - [3, 400, 5]
- [6, 700, 8]
- [9, 1000, 11]
- [12, 13, 14]
...
tarantool> s:truncate()
---
...
upsert
accepts a well-formatted tuple and update operations.
If an old tuple is found by the primary key of the specified tuple, then the update operations are applied to the old tuple, and the new tuple is ignored.
If no old tuple is found, then the new tuple is inserted, and the update operations are ignored.
Indexes have no upsert
method - this is a method of a space.
tarantool> s.index.pk.upsert == nil
---
- true
...
tarantool> s.index.sk_uniq.upsert == nil
---
- true
...
tarantool> s.upsert ~= nil
---
- true
...
tarantool> -- As the first argument, upsert accepts --
tarantool> -- a well-formatted tuple, NOT a key! --
tarantool> s:insert{1, 2, 3}
---
- [1, 2, 3]
...
tarantool> s:upsert({1}, {{'=', 2, 200}})
---
- error: Tuple field count 1 is less than required by space format or defined indexes
(expected at least 3)
...
tarantool> s:select{}
---
- - [1, 2, 3]
...
tarantool> s:delete{1}
---
- [1, 2, 3]
...
upsert
turns into insert
when no old tuple is found by the primary key.
tarantool> s:upsert({1, 2, 3}, {{'=', 2, 200}})
---
...
tarantool> -- As you can see, {1, 2, 3} were inserted, --
tarantool> -- and the update operations were not applied. --
s:select{}
---
- - [1, 2, 3]
...
tarantool> -- Performing another upsert with the same primary key, --
tarantool> -- but different values in the other fields. --
s:upsert({1, 20, 30}, {{'=', 2, 200}})
---
...
tarantool> -- The old tuple was found by the primary key {1} --
tarantool> -- and update operations were applied. --
tarantool> -- The new tuple was ignored. --
tarantool> s:select{}
---
- - [1, 200, 3]
...
upsert
searches for an old tuple by the primary index,
NOT by a secondary index. This can lead to a duplication error
if the new tuple ruins the uniqueness of a secondary index.
tarantool> s:upsert({2, 200, 3}, {{'=', 3, 300}})
---
- error: Duplicate key exists in unique index 'sk_uniq' in space 'test'
...
s:select{}
---
- - [1, 200, 3]
...
tarantool> -- But this works, when uniqueness is preserved. --
tarantool> s:upsert({2, 0, 0}, {{'=', 3, 300}})
---
...
tarantool> s:select{}
---
- - [1, 200, 3]
- [2, 0, 0]
...
tarantool> s:truncate()
---
...
replace
accepts a well-formatted tuple and searches for an old tuple
by the primary key of the new tuple.
If the old tuple is found, then it is deleted, and the new tuple is inserted.
If the old tuple was not found, then just the new tuple is inserted.
tarantool> s:replace{1, 2, 3}
---
- [1, 2, 3]
...
tarantool> s:select{}
---
- - [1, 2, 3]
...
tarantool> s:replace{1, 3, 4}
---
- [1, 3, 4]
...
tarantool> s:select{}
---
- - [1, 3, 4]
...
tarantool> s:truncate()
---
...
replace
can ruin unique constraints, like upsert
does.
tarantool> s:insert{1, 1, 1}
---
- [1, 1, 1]
...
tarantool> s:insert{2, 2, 2}
---
- [2, 2, 2]
...
tarantool> -- This replace fails, because if the new tuple {1, 2, 0} replaces --
tarantool> -- the old tuple by the primary key from 'pk' index {1, 1, 1}, --
tarantool> -- this results in a duplicate unique secondary key in 'sk_uniq' index: --
tarantool> -- key {2} is used both in the new tuple and in {2, 2, 2}. --
tarantool> s:replace{1, 2, 0}
---
- error: Duplicate key exists in unique index 'sk_uniq' in space 'test'
...
tarantool> s:truncate()
---
...
select
works with any indexes (primary/secondary) and with any keys
(unique/non-unique, full/partial).
If a key is partial, then select
searches by all keys, where the prefix
matches the specified key part.
tarantool> s:insert{1, 2, 3}
---
- [1, 2, 3]
...
tarantool> s:insert{4, 5, 6}
---
- [4, 5, 6]
...
tarantool> s:insert{7, 8, 9}
---
- [7, 8, 9]
...
tarantool> s:insert{10, 11, 9}
---
- [10, 11, 9]
...
tarantool> s:select{1}
---
- - [1, 2, 3]
...
tarantool> s:select{}
---
- - [1, 2, 3]
- [4, 5, 6]
- [7, 8, 9]
- [10, 11, 9]
...
tarantool> s.index.pk:select{4}
---
- - [4, 5, 6]
...
tarantool> s.index.sk_uniq:select{8}
---
- - [7, 8, 9]
...
tarantool> s.index.sk_non_uniq:select{9}
---
- - [7, 8, 9]
- [10, 11, 9]
...