Submodule box.schema
The box.schema
submodule has data-definition functions
for spaces, users, roles, function tuples, and sequences.
Below is a list of all box.schema
functions.
Name | Use |
---|---|
box.schema.space.create() or box.schema.create_space() | Create a space |
box.schema.upgrade | Upgrade a database |
box.schema.user.create() | Create a user |
box.schema.user.drop() | Drop a user |
box.schema.user.exists() | Check if a user exists |
box.schema.user.grant() | Grant privileges to a user or a role |
box.schema.user.revoke() | Revoke privileges from a user or a role |
box.schema.user.password() | Get a hash of a user’s password |
box.schema.user.passwd() | Associate a password with a user |
box.schema.user.info() | Get a description of a user’s privileges |
box.schema.role.create() | Create a role |
box.schema.role.drop() | Drop a role |
box.schema.role.exists() | Check if a role exists |
box.schema.role.grant() | Grant privileges to a role |
box.schema.role.revoke() | Revoke privileges from a role |
box.schema.role.info() | Get a description of a role’s privileges |
box.schema.func.create() | Create a function tuple |
box.schema.func.drop() | Drop a function tuple |
box.schema.func.exists() | Check if a function tuple exists |
box.schema.sequence.create() | Create a new sequence generator |
sequence_object:next() | Generate and return the next value |
sequence_object:alter() | Change sequence options |
sequence_object:reset() | Reset sequence state |
sequence_object:set() | Set the new value |
sequence_object:drop() | Drop the sequence |
space_object:create_index() | Create an index |
-
box.schema.space.
create
(space-name[, {options}]) -
box.schema.
create_space
(space-name[, {options}]) Create a space.
Parameters: - space-name (string) – name of space, which should conform to the rules for object names
- options (table) – see “Options for box.schema.space.create” chart, below
Return: space object
Rtype: userdata
You can use either syntax. For example,
s = box.schema.space.create('tester')
has the same effect ass = box.schema.create_space('tester')
.Options for box.schema.space.create
Name Effect Type Default engine ‘memtx’ or ‘vinyl’ string ‘memtx’ field_count fixed count of fields: for example if field_count=5, it is illegal to insert a tuple with fewer than or more than 5 fields number 0 i.e. not fixed format field names and types: See the illustrations of format clauses in the space_object:format() description and in the box.space._space example. Optional and usually not specified. table (blank) id unique identifier: users can refer to spaces with the id instead of the name number last space’s id, +1 if_not_exists create space only if a space with the same name does not exist already, otherwise do nothing but do not cause an error boolean false is_local space contents are replication-local: changes are stored in the write-ahead log of the local node but there is no replication. boolean false temporary space contents are temporary: changes are not stored in the write-ahead log and there is no replication. Note re storage engine: vinyl does not support temporary spaces. boolean false user name of the user who is considered to be the space’s owner for authorization purposes string current user’s name There are three syntax variations for object references targeting space objects, for example
box.schema.space.drop(space-id)
will drop a space. However, the common approach is to use functions attached to the space objects, for example space_object:drop().Example
tarantool> s = box.schema.space.create('space55') --- ... tarantool> s = box.schema.space.create('space55', { > id = 555, > temporary = false > }) --- - error: Space 'space55' already exists ... tarantool> s = box.schema.space.create('space55', { > if_not_exists = true > }) --- ...
After a space is created, usually the next step is to create an index for it, and then it is available for insert, select, and all the other box.space functions.
-
box.schema.user.
create
(user-name[, {options}]) Create a user. For explanation of how Tarantool maintains user data, see section Users and reference on _user space.
The possible options are:
if_not_exists
=true|false
(default =false
) - boolean;true
means there should be no error if the user already exists,password
(default = ‘’) - string; thepassword
= password specification is good because in a URI (Uniform Resource Identifier) it is usually illegal to include a user-name without a password.
Note
The maximum number of users is 32.
Parameters: - user-name (string) – name of user, which should conform to the rules for object names
- options (table) –
if_not_exists
,password
Return: nil
Examples:
box.schema.user.create('Lena') box.schema.user.create('Lena', {password = 'X'}) box.schema.user.create('Lena', {if_not_exists = false})
-
box.schema.user.
drop
(user-name[, {options}]) Drop a user. For explanation of how Tarantool maintains user data, see section Users and reference on _user space.
Parameters: Examples:
box.schema.user.drop('Lena') box.schema.user.drop('Lena',{if_exists=false})
-
box.schema.user.
exists
(user-name) Return
true
if a user exists; returnfalse
if a user does not exist. For explanation of how Tarantool maintains user data, see section Users and reference on _user space.Parameters: - user-name (string) – the name of the user
Rtype: bool
Example:
box.schema.user.exists('Lena')
-
box.schema.user.
grant
(user-name, privileges, object-type, object-name[, {options}]) -
box.schema.user.
grant
(user-name, privileges, 'universe'[, nil, {options}]) -
box.schema.user.
grant
(user-name, role-name[, nil, nil, {options}]) Grant privileges to a user or to another role.
Parameters: - user-name (string) – the name of the user.
- privileges (string) – ‘read’ or ‘write’ or ‘execute’ or ‘create’ or ‘alter’ or ‘drop’ or a combination.
- object-type (string) – ‘space’ or ‘function’ or ‘sequence’ or ‘role’.
- object-name (string) – name of object to grant permissions for.
- role-name (string) – name of role to grant to user.
- options (table) –
grantor
,if_not_exists
.
If
'function','object-name'
is specified, then a _func tuple with that object-name must exist.Variation: instead of
object-type, object-name
say ‘universe’ which means ‘all object-types and all objects’. In this case, object name is omitted.Variation: instead of
privilege, object-type, object-name
sayrole-name
(see section Roles).Variation: instead of
box.schema.user.grant('user-name','usage,session','universe',nil,
{if_not_exists=true})
saybox.schema.user.enable('user-name')
.The possible options are:
grantor
= grantor_name_or_id – string or number, for custom grantor,if_not_exists
=true|false
(default =false
) - boolean;true
means there should be no error if the user already has the privilege.
Example:
box.schema.user.grant('Lena', 'read', 'space', 'tester') box.schema.user.grant('Lena', 'execute', 'function', 'f') box.schema.user.grant('Lena', 'read,write', 'universe') box.schema.user.grant('Lena', 'Accountant') box.schema.user.grant('Lena', 'read,write,execute', 'universe') box.schema.user.grant('X', 'read', 'universe', nil, {if_not_exists=true}))
-
box.schema.user.
revoke
(user-name, privileges, object-type, object-name[, {options}]) -
box.schema.user.
revoke
(user-name, privileges, 'universe'[, nil, {options}]) -
box.schema.user.
revoke
(user-name, role-name[, nil, nil, {options}]) Revoke privileges from a user or from another role.
Parameters: - user-name (string) – the name of the user.
- privilege (string) – ‘read’ or ‘write’ or ‘execute’ or ‘create’ or ‘alter’ or ‘drop’ or a combination.
- object-type (string) – ‘space’ or ‘function’ or ‘sequence’.
- object-name (string) – the name of a function or space or sequence.
- options (table) –
if_exists
.
The user must exist, and the object must exist, but if the option setting is
{if_exists=true}
then it is not an error if the user does not have the privilege.Variation: instead of
object-type, object-name
say ‘universe’ which means ‘all object-types and all objects’.Variation: instead of
privilege, object-type, object-name
sayrole-name
(see section Roles).Variation: instead of
box.schema.user.revoke('user-name','usage,session','universe',nil,
{if_exists=true})
saybox.schema.user.disable('user-name')
.Example:
box.schema.user.revoke('Lena', 'read', 'space', 'tester') box.schema.user.revoke('Lena', 'execute', 'function', 'f') box.schema.user.revoke('Lena', 'read,write', 'universe') box.schema.user.revoke('Lena', 'Accountant')
-
box.schema.user.
password
(password) Return a hash of a user’s password. For explanation of how Tarantool maintains passwords, see section Passwords and reference on _user space.
Note
- If a non-‘guest’ user has no password, it’s impossible to connect to Tarantool using this user. The user is regarded as “internal” only, not usable from a remote connection. Such users can be useful if they have defined some procedures with the SETUID option, on which privileges are granted to externally-connectable users. This way, external users cannot create/drop objects, they can only invoke procedures.
- For the ‘guest’ user, it’s impossible to set a password: that would be misleading, since ‘guest’ is the default user on a newly-established connection over a binary port, and Tarantool does not require a password to establish a binary connection. It is, however, possible to change the current user to ‘guest’ by providing the AUTH packet with no password at all or an empty password. This feature is useful for connection pools, which want to reuse a connection for a different user without re-establishing it.
Parameters: - password (string) – password to be hashed
Rtype: Example:
box.schema.user.password('ЛЕНА')
-
box.schema.user.
passwd
([user-name, ]password) Associate a password with the user who is currently logged in, or with the user specified by user-name. The user must exist and must not be ‘guest’.
Users who wish to change their own passwords should use
box.schema.user.passwd(password)
syntax.Administrators who wish to change passwords of other users should use
box.schema.user.passwd(user-name, password)
syntax.Parameters: Example:
box.schema.user.passwd('ЛЕНА') box.schema.user.passwd('Lena', 'ЛЕНА')
-
box.schema.user.
info
([user-name]) Return a description of a user’s privileges.
-
box.schema.role.
create
(role-name[, {options}]) Create a role. For explanation of how Tarantool maintains role data, see section Roles.
Parameters: - role-name (string) – name of role, which should conform to the rules for object names
- options (table) –
if_not_exists
=true|false
(default =false
) - boolean;true
means there should be no error if the role already exists
Return: nil
Example:
box.schema.role.create('Accountant') box.schema.role.create('Accountant', {if_not_exists = false})
-
box.schema.role.
drop
(role-name[, {options}]) Drop a role. For explanation of how Tarantool maintains role data, see section Roles.
Parameters: Example:
box.schema.role.drop('Accountant')
-
box.schema.role.
exists
(role-name) Return
true
if a role exists; returnfalse
if a role does not exist.Parameters: - role-name (string) – the name of the role
Rtype: bool
Example:
box.schema.role.exists('Accountant')
-
box.schema.role.
grant
(role-name, privilege, object-type, object-name[, option]) -
box.schema.role.
grant
(role-name, privilege, 'universe'[, nil, option]) -
box.schema.role.
grant
(role-name, role-name[, nil, nil, option]) Grant privileges to a role.
Parameters: - role-name (string) – the name of the role.
- privilege (string) – ‘read’ or ‘write’ or ‘execute’ or ‘create’ or ‘alter’ or ‘drop’ or a combination.
- object-type (string) – ‘space’ or ‘function’ or ‘sequence’ or ‘role’.
- object-name (string) – the name of a function or space or sequence or role.
- option (table) –
if_not_exists
=true|false
(default =false
) - boolean;true
means there should be no error if the role already has the privilege.
The role must exist, and the object must exist.
Variation: instead of
object-type, object-name
say ‘universe’ which means ‘all object-types and all objects’. In this case, object name is omitted.Variation: instead of
privilege, object-type, object-name
sayrole-name
– to grant a role to a role.Example:
box.schema.role.grant('Accountant', 'read', 'space', 'tester') box.schema.role.grant('Accountant', 'execute', 'function', 'f') box.schema.role.grant('Accountant', 'read,write', 'universe') box.schema.role.grant('public', 'Accountant') box.schema.role.grant('role1', 'role2', nil, nil, {if_not_exists=false})
-
box.schema.role.
revoke
(role-name, privilege, object-type, object-name) Revoke privileges from a role.
Parameters: The role must exist, and the object must exist, but it is not an error if the role does not have the privilege.
Variation: instead of
object-type, object-name
say ‘universe’ which means ‘all object-types and all objects’.Variation: instead of
privilege, object-type, object-name
sayrole-name
.Example:
box.schema.role.revoke('Accountant', 'read', 'space', 'tester') box.schema.role.revoke('Accountant', 'execute', 'function', 'f') box.schema.role.revoke('Accountant', 'read,write', 'universe') box.schema.role.revoke('public', 'Accountant')
-
box.schema.role.
info
(role-name) Return a description of a role’s privileges.
Parameters: - role-name (string) – the name of the role.
Example:
box.schema.role.info('Accountant')
-
box.schema.func.
create
(func-name[, {options-without-body}]) Create a function tuple. without including the
body
option. (For functions created with thebody
option, see box.schema.func.create(func-name [, {options-with-body}]).This is called a “not persistent” function because functions without bodies are not persistent. This does not create the function itself – that is done with Lua – but if it is necessary to grant privileges for a function, box.schema.func.create must be done first. For explanation of how Tarantool maintains function data, see the reference for the box.space._func space.
The possible options are:
if_not_exists
=true|false
(default =false
) - boolean;true
means there should be no error if the_func
tuple already exists.setuid
=true|false
(default =false
) - boolean;true
means that Tarantool should treat the function’s caller as the function’s owner, with owner privileges.setuid
works only over binary ports,setuid
does not work if the function is invoked via an admin console or inside a Lua script.language
= ‘LUA’|’C’ (default = ‘LUA’) - string.
Parameters: - func-name (string) – name of function, which should conform to the rules for object names
- options (table) –
if_not_exists
,setuid
,language
.
Return: nil
Example:
box.schema.func.create('calculate') box.schema.func.create('calculate', {if_not_exists = false}) box.schema.func.create('calculate', {setuid = false}) box.schema.func.create('calculate', {language = 'LUA'})
-
box.schema.func.
create
(func-name[, {options-with-body}]) Create a function tuple. including the
body
option. (For functions created without thebody
option, see box.schema.func.create(func-name [, {options-without-body}]).This is called a “persistent” function because only functions with bodies are persistent. This does create the function itself, the body is a function definition. For explanation of how Tarantool maintains function data, see the reference for the box.space._func space.
The possible options are:
if_not_exists
=true|false
(default =false
) - boolean; same as for box.schema.func.create(func-name [, {options-without-body}]).setuid
=true|false
(default =false
) - boolean; same as for box.schema.func.create(func-name [, {options-without-body}]).language
= ‘LUA’|’C’ (default = ‘LUA’) - string. same as for box.schema.func.create(func-name [, {options-without-body}]).is_sandboxed
=true|false
(default =false
) - boolean; whether the function should be executed in a sandbox.is_deterministic
=true|false
(default =false
) - boolean;true
means that the function should be deterministic,false
means that the function may or may not be deterministic.body
= function definition (default = nil) - string; the function definition.
Parameters: - func-name (string) – name of function, which should conform to the rules for object names
- options (table) –
if_not_exists
,setuid
,language
,is_sandboxed
,is_deterministic
,body
.
Return: nil
C functions are imported from .so files, Lua functions can be defined within
body
. We will only describe Lua functions in this section.A function tuple with a body is “persistent” because the tuple is stored in a snapshot and is recoverable if the server restarts. All of the option values described in this section are visible in the box.space._func system space.
If
is_sandboxed
is true, then the function will be executed in an isolated environment: any operation that accesses the world outside the sandbox will be forbidden or will have no effect. Therefore a sandboxed function can only use modules and functions which cannot affect isolation: assert, error, ipairs, math.*, next, pairs, pcall, print, select, string.*, table.*, tonumber, tostring, type, unpack, utf8.*, xpcall. Also a sandboxed function cannot refer to global variables – they will be treated as local variables because the sandbox is established with setfenv. So a sandboxed function will happen to be stateless and deterministic.If
is_deterministic
is true, there is no immediate effect. Tarantool plans to use the is_deterministic value in a future version. A function is deterministic if it always returns the same outputs given the same inputs. It is the function creator’s responsibility to ensure that a function is truly deterministic.Using a persistent Lua function
After a persistent Lua function is created, it can be found in the box.space._func system space, and it can be shown with
box.func.func-name
and it can be invoked by any user with authorization to ‘execute’ it. The syntax for invoking is:
box.func.func-name:call([parameters])
or, if the connection is remote, the syntax is as in net_box:call().Example:
tarantool> lua_code = [[function(a, b) return a + b end]] tarantool> box.schema.func.create('sum', {body = lua_code}) tarantool> box.func.sum --- - is_sandboxed: false is_deterministic: false id: 2 setuid: false body: function(a, b) return a + b end name: sum language: LUA ... tarantool> box.func.sum:call({1, 2}) --- - 3 ...
-
box.schema.func.
drop
(func-name[, {options}]) Drop a function tuple. For explanation of how Tarantool maintains function data, see reference on _func space.
Parameters: Example:
box.schema.func.drop('calculate')
-
box.schema.func.
exists
(func-name) Return true if a function tuple exists; return false if a function tuple does not exist.
Parameters: - func-name (string) – the name of the function
Rtype: bool
Example:
box.schema.func.exists('calculate')
-
box.schema.func.
reload
([name]) Reload a C module with all its functions without restarting the server.
Under the hood, Tarantool loads a new copy of the module (
*.so
shared library) and starts routing all new request to the new version. The previous version remains active until all started calls are finished. All shared libraries are loaded withRTLD_LOCAL
(see “man 3 dlopen”), therefore multiple copies can co-exist without any problems.Note
Reload will fail if a module was loaded from Lua script with ffi.load().
Parameters: - name (string) – the name of the module to reload
Example:
-- reload the entire module contents box.schema.func.reload('module')
An introduction to sequences is in the Sequences section of the “Data model” chapter. Here are the details for each function and option.
All functions related to sequences require appropriate privileges.
-
box.schema.sequence.
create
(name[, options]) Create a new sequence generator.
Parameters: Return: a reference to a new sequence object.
Options:
start
– the STARTS WITH value. Type = integer, Default = 1.min
– the MINIMUM value. Type = integer, Default = 1.max
- the MAXIMUM value. Type = integer, Default = 9223372036854775807.There is a rule:
min
<=start
<=max
. For example it is illegal to say{start=0}
because then the specified start value (0) would be less than the default min value (1).There is a rule:
min
<= next-value <=max
. For example, if the next generated value would be 1000, but the maximum value is 999, then that would be considered “overflow”.There is a rule:
start
andmin
andmax
must all be <= 9223372036854775807 which is 2^63 - 1 (not 2^64).cycle
– the CYCLE value. Type = bool. Default = false.If the sequence generator’s next value is an overflow number, it causes an error return – unless
cycle == true
.But if
cycle == true
, the count is started again, at the MINIMUM value or at the MAXIMUM value (not the STARTS WITH value).cache
– the CACHE value. Type = unsigned integer. Default = 0.Currently Tarantool ignores this value, it is reserved for future use.
step
– the INCREMENT BY value. Type = integer. Default = 1.Ordinarily this is what is added to the previous value.
-
sequence_object:
next
() Generate the next value and return it.
The generation algorithm is simple:
- If this is the first time, then return the STARTS WITH value.
- If the previous value plus the INCREMENT value is less than the
MINIMUM value or greater than the MAXIMUM value, that is “overflow”,
so either raise an error (if
cycle
=false
) or return the MAXIMUM value (ifcycle
=true
andstep
< 0) or return the MINIMUM value (ifcycle
=true
andstep
> 0).
If there was no error, then save the returned result, it is now the “previous value”.
For example, suppose sequence ‘S’ has:
min
== -6,max
== -1,step
== -3,start
= -2,cycle
= true,- previous value = -2.
Then
box.sequence.S:next()
returns -5 because -2 + (-3) == -5.Then
box.sequence.S:next()
again returns -1 because -5 + (-3) < -6, which is overflow, causing cycle, andmax
== -1.This function requires a ‘write’ privilege on the sequence.
Note
This function should not be used in “cross-engine” transactions (transactions which use both the memtx and the vinyl storage engines).
To see what the previous value was, without changing it, you can select from the _sequence_data system space.
-
sequence_object:
alter
(options) The
alter()
function can be used to change any of the sequence’s options. Requirements and restrictions are the same as for box.schema.sequence.create().
-
sequence_object:
reset
() Set the sequence back to its original state. The effect is that a subsequent
next()
will return thestart
value. This function requires a ‘write’ privilege on the sequence.
-
sequence_object:
set
(new-previous-value) Set the “previous value” to
new-previous-value
. This function requires a ‘write’ privilege on the sequence.
-
sequence_object:
drop
() Drop an existing sequence.
Example:
Here is an example showing all sequence options and operations:
s = box.schema.sequence.create( 'S2', {start=100, min=100, max=tonumber64('9223372036854775807'), cache=100000, cycle=false, step=100 }) s:alter({step=6}) s:next() s:reset() s:set(150) s:drop()
-
space_object:
create_index
(... [sequence='...' option] ...) You can use the
sequence
option when creating or altering a primary-key index. The sequence becomes associated with the index, so that the nextinsert()
will put the next generated number into the primary-key field, if the field value would otherwise be nil.The syntax may be any of:
sequence = sequence identifier
orsequence = {id =
sequence identifier
}
orsequence = {field =
field number
}
orsequence = {id =
sequence identifier
, field =
field number
}
orsequence = true
orsequence = {}
.
The sequence identifier may be either a number (the sequence id) or a string (the sequence name). The field number may be the ordinal number of any field in the index; default = 1. Examples of all possibilities:sequence = 1
orsequence = 'sequence_name'
orsequence = {id = 1}
orsequence = {id = 'sequence_name'}
orsequence = {id = 1, field = 1}
orsequence = {id = 'sequence_name', field = 1}
orsequence = {field = 1}
orsequence = true
orsequence = {}
. Notice that the sequence identifier can be omitted, if it is omitted then a new sequence is created automatically with default name =space-name_seq
. Notice that the field number does not have to be 1, that is, the sequence can be associated with any field in the primary-key index.For example, if ‘Q’ is a sequence and ‘T’ is a new space, then this will work:
tarantool> box.space.T:create_index('Q',{sequence='Q'}) --- - unique: true parts: - type: unsigned is_nullable: false fieldno: 1 sequence_id: 8 id: 0 space_id: 514 name: Q type: TREE ...
(Notice that the index now has a
sequence_id
field.)And this will work:
tarantool> box.space.T:insert{box.NULL,0} --- - [1, 0] ...
Note
The index key type may be either ‘integer’ or ‘unsigned’. If any of the sequence options is a negative number, then the index key type should be ‘integer’.
Users should not insert a value greater than 9223372036854775807, which is 2^63 - 1, in the indexed field. The sequence generator will ignore it.
A sequence cannot be dropped if it is associated with an index. However, index_object:alter() can be used to say that a sequence is not associated with an index, for example
box.space.T.index.I:alter({sequence=false})
.If a sequence was created automatically because the sequence identifier was omitted, then it will be dropped automatically if the index is altered so that
sequence=false
, or if the index is dropped.index_object:alter()
can also be used to associate a sequence with an existing index, with the same syntax for options.When a sequence is used with an index based on a JSON path, inserted tuples must have all components of the path preceding the autoincrement field, and the autoincrement field. To achieve that use
box.NULL
rather thannil
. Example:s = box.schema.space.create('test') s:create_index('pk', {parts = {{'[1].a.b[1]', 'unsigned'}}, sequence = true}) s:replace{} -- error s:replace{{c = {}}} -- error s:replace{{a = {c = {}}}} -- error s:replace{{a = {b = {}}}} -- error s:replace{{a = {b = {nil}}}} -- error s:replace{{a = {b = {box.NULL}}}} -- ok