Data model¶

Lua vs MsgPack¶

In Lua, a nil type has only one possible value, also called nil (displayed as null on Tarantool’s command line, since the output is in the YAML format). Nils may be compared to values of any types with == (is-equal) or ~= (is-not-equal), but other operations will not work. Nils may not be used in Lua tables; the workaround is to use msgpack.NULL

A boolean is either true or false.

A string is a variable-length sequence of bytes, usually represented with alphanumeric characters inside single quotes. In both Lua and MsgPack, strings are treated as binary data, with no attempts to determine a string’s character set or to perform any string conversion – unless there is an optional collation. So, usually, string sorting and comparison are done byte-by-byte, without any special collation rules applied. (Example: numbers are ordered by their point on the number line, so 2345 is greater than 500; meanwhile, strings are ordered by the encoding of the first byte, then the encoding of the second byte, and so on, so ‘2345’ is less than ‘500’.)

In Lua, a number is double-precision floating-point, but Tarantool ‘number’ may have both integer and floating-point values. Tarantool will try to store a Lua number as floating-point if the value contains a decimal point or is very large (greater than 100 trillion = 1e14), otherwise Tarantool will store it as an integer. To ensure that even very large numbers are stored as integers, use the tonumber64 function, or the LL (Long Long) suffix, or the ULL (Unsigned Long Long) suffix. Here are examples of numbers using regular notation, exponential notation, the ULL suffix and the tonumber64 function: -55, -2.7e+20, 100000000000000ULL, tonumber64('18446744073709551615').

The Tarantool/SQL double field type exists mainly so that there will be an equivalent to Tarantool/SQL’s DOUBLE data type. In MsgPack the storage type is MP_DOUBLE and the size of the encoded value is always 9 bytes. In Lua, ‘double’ fields can only contain non-integer numeric values and cdata values with double floating-point numbers. To avoid using the wrong kind of values inadvertently, use ffi.cast() when searching or changing ‘double’ fields. For example, instead of space_object:insert { value } say ffi = require('ffi') ... space_object:insert ({ffi.cast('double', value )}). Example:

s = box.schema.space.create('s', {format = {{'d', 'double'}}})
s:create_index('ii')
s:insert({1.1})
ffi = require('ffi')
s:insert({ffi.cast('double', 1)})
s:insert({ffi.cast('double', tonumber('123'))})
s:select(1.1)
s:select({ffi.cast('double', 1)})

Arithmetic with cdata ‘double’ will not work reliably, so for Lua it is better to use the ‘number’ type. This warning does not apply for Tarantool/SQL because Tarantool/SQL does implicit casting.

An ext (extension) value is an addition by Tarantool, not part of the formal MsgPack definition, for storage of decimal values. Values with the decimal type are not floating-point values although they may contain decimal points. They are exact.

A bin (binary) value is not directly supported by Lua but there is a Tarantool type VARBINARY which is encoded as MessagePack binary. For an (advanced) example showing how to insert VARBINARY into a database, see the Cookbook Recipe for ffi_varbinary_insert.

Lua tables with string keys are stored as MsgPack maps; Lua tables with integer keys starting with 1 – as MsgPack arrays. Nils may not be used in Lua tables; the workaround is to use msgpack.NULL

A tuple is a light reference to a MsgPack array stored in the database. It is a special type (cdata) to avoid conversion to a Lua table on retrieval. A few functions may return tables with multiple tuples. For more tuple examples, see box.tuple.

Examples of insert requests with different data types:

tarantool> box.space.K:insert{1,nil,true,'A B C',12345,1.2345}
---
- [1, null, true, 'A B C', 12345, 1.2345]
...
tarantool> box.space.K:insert{2,{['a']=5,['b']=6}}
---
- [2, {'a': 5, 'b': 6}]
...
tarantool> box.space.K:insert{3,{1,2,3,4,5}}
---
- [3, [1, 2, 3, 4, 5]]
...

Indexed field types¶

Indexes restrict values which Tarantool’s MsgPack may contain. This is why, for example, ‘unsigned’ is a separate indexed field type, compared to ‘integer’ data type in MsgPack: they both store ‘integer’ values, but an ‘unsigned’ index contains only non-negative integer values and an ‘integer’ index contains all integer values.

Here is how Tarantool indexed field types correspond to MsgPack data types.

Options for box.schema.sequence.create()¶

Once a sequence exists, it can be altered, dropped, reset, forced to generate the next value, or associated with an index.

For an initial example, we generate a sequence named ‘S’.

tarantool> box.schema.sequence.create('S',{min=5, start=5})
---
- step: 1
  id: 5
  min: 5
  cache: 0
  uid: 1
  max: 9223372036854775807
  cycle: false
  name: S
  start: 5
...

The result shows that the new sequence has all default values, except for the two that were specified, min and start.

Then we get the next value, with the next() function.

tarantool> box.sequence.S:next()
---
- 5
...

The result is the same as the start value. If we called next() again, we would get 6 (because the previous value plus the step value is 6), and so on.

Then we create a new table, and say that its primary key may be generated from the sequence.

tarantool> s=box.schema.space.create('T');s:create_index('I',{sequence='S'})
---
...

Then we insert a tuple, without specifying a value for the primary key.

tarantool> box.space.T:insert{nil,'other stuff'}
---
- [6, 'other stuff']
...

The result is a new tuple where the first field has a value of 6. This arrangement, where the system automatically generates the values for a primary key, is sometimes called “auto-incrementing” or “identity”.

For syntax and implementation details, see the reference for box.schema.sequence.

Data operations¶

The basic data operations supported in Tarantool are:

• five data-manipulation operations (INSERT, UPDATE, UPSERT, DELETE, REPLACE), and
• one data-retrieval operation (SELECT).

All of them are implemented as functions in box.space submodule.

Examples:

• INSERT: Add a new tuple to space ‘tester’.

  The first field, field[1], will be 999 (MsgPack type is integer).

  The second field, field[2], will be ‘Taranto’ (MsgPack type is string).

  tarantool> box.space.tester:insert{999, 'Taranto'}
  

• UPDATE: Update the tuple, changing field field[2].

  The clause “{999}”, which has the value to look up in the index of the tuple’s primary-key field, is mandatory, because update() requests must always have a clause that specifies a unique key, which in this case is field[1].

  The clause “{{‘=’, 2, ‘Tarantino’}}” specifies that assignment will happen to field[2] with the new value.

  tarantool> box.space.tester:update({999}, {{'=', 2, 'Tarantino'}})
  

• UPSERT: Upsert the tuple, changing field field[2] again.

  The syntax of upsert() is similar to the syntax of update(). However, the execution logic of these two requests is different. UPSERT is either UPDATE or INSERT, depending on the database’s state. Also, UPSERT execution is postponed until after transaction commit, so, unlike update(), upsert() doesn’t return data back.

  tarantool> box.space.tester:upsert({999, 'Taranted'}, {{'=', 2, 'Tarantism'}})
  

• REPLACE: Replace the tuple, adding a new field.

  This is also possible with the update() request, but the update() request is usually more complicated.

  tarantool> box.space.tester:replace{999, 'Tarantella', 'Tarantula'}
  

• SELECT: Retrieve the tuple.

  The clause “{999}” is still mandatory, although it does not have to mention the primary key.

  tarantool> box.space.tester:select{999}
  

• DELETE: Delete the tuple.

  In this example, we identify the primary-key field.

  tarantool> box.space.tester:delete{999}
  

Summarizing the examples:

• Functions insert and replace accept a tuple (where a primary key comes as part of the tuple).
• Function upsert accepts a tuple (where a primary key comes as part of the tuple), and also the update operations to execute.
• Function delete accepts a full key of any unique index (primary or secondary).
• Function update accepts a full key of any unique index (primary or secondary), and also the operations to execute.
• Function select accepts any key: primary/secondary, unique/non-unique, full/partial.

See reference on box.space for more details on using data operations.

Index operations¶

Index operations are automatic: if a data-manipulation request changes a tuple, then it also changes the index keys defined for the tuple.

The simple index-creation operation that we’ve illustrated before is:

box.space.space-name:create_index('index-name')

This creates a unique TREE index on the first field of all tuples (often called “Field#1”), which is assumed to be numeric.

The simple SELECT request that we’ve illustrated before is:

box.space.space-name:select(value)

This looks for a single tuple via the first index. Since the first index is always unique, the maximum number of returned tuples will be: one. You can call select() without arguments, causing all tuples to be returned.

Let’s continue working with the space ‘tester’ created in the “Getting started” exercises:

-- Let's modify our space 'tester'
tarantool> box.space.tester:format({
         > {name = 'id', type = 'unsigned'},
         > {name = 'band_name', type = 'string'},
         > {name = 'year', type = 'unsigned'},
         > {name = 'rate', type = 'unsigned', is_nullable=true}})
---
...
-- Create an index with three parts
tarantool> box.space.tester:create_index('tertiary', {parts = {{field = 2, type = 'string'},{field=3, type='unsigned'}, {field=4, type='unsigned'}}})
---
- unique: true
  parts:
  - type: string
    is_nullable: false
    fieldno: 2
  - type: unsigned
    is_nullable: false
    fieldno: 3
  - type: unsigned
    is_nullable: true
    fieldno: 4
  id: 6
  space_id: 513
  type: TREE
  name: tertiary
...
-- And make sure it has three parts
tarantool> box.space.tester.index.tertiary.parts
---
- - type: string
    is_nullable: false
    fieldno: 2
  - type: unsigned
    is_nullable: false
    fieldno: 3
  - type: unsigned
    is_nullable: true
    fieldno: 4
...
-- Add the rate to the tuple #1
tarantool> box.space.tester:update(1, {{'=', 4, 5}})
---
- [1, 'Roxette', 1986, 5]
...
-- And insert another tuple
tarantool> box.space.tester:insert({4, 'Roxette', 2016, 5})
---
- [4, 'Roxette', 2016, 5]
...

The existing SELECT variations:

1. The search can use comparisons other than equality.

tarantool> box.space.tester:select(1, {iterator = 'GT'})
---
- - [2, 'Scorpions', 2015]
  - [3, 'Ace of Base', 1993]
  - [4, 'Roxette', 2016, 5]
...

The comparison operators are LT, LE, EQ, REQ, GE, GT (for “less than”, “less than or equal”, “equal”, “reversed equal”, “greater than or equal”, “greater than” respectively). Comparisons make sense if and only if the index type is ‘TREE’.

This type of search may return more than one tuple; if so, the tuples will be in descending order by key when the comparison operator is LT or LE or REQ, otherwise in ascending order.

2. The search can use a secondary index.

tarantool> box.space.tester.index.secondary:select({'Ace of Base'})
---
- - [3, 'Ace of Base', 1993]
...

For a primary-key search, it is optional to specify an index name. For a secondary-key search, it is mandatory.

3. The search may be for some or all key parts but should contain the prefix of the key. Notice that partial key searches are available only in TREE indexes.

tarantool> box.space.tester.index.tertiary:select({'Roxette', 2016})
---
- - [1, 'Roxette', 2016, 5]
...
tarantool> box.space.tester.index.tertiary:select({'Roxette', nil, 5})
---
- - [1, 'Roxette', 1986, 5]
  - [4, 'Roxette', 2016, 5]
...

4. The search may be for all fields, using a table for the value:

tarantool> box.space.tester.index.tertiary:select({'Roxette', 2016 ,5})
---
- - [4, 'Roxette', 2016, 5]
...

or the search can be for one field, using a table or a scalar:

tarantool> box.space.tester.index.tertiary:select('Roxette')
---
- - [1, 'Roxette', 1986, 5]
  - [4, 'Roxette', 2016, 5]
...

Working with BITSET and RTREE¶

BITSET example:

tarantool> box.schema.space.create('bitset_example')
tarantool> box.space.bitset_example:create_index('primary')
tarantool> box.space.bitset_example:create_index('bitset',{unique=false,type='BITSET', parts={2,'unsigned'}})
tarantool> box.space.bitset_example:insert{1,1}
tarantool> box.space.bitset_example:insert{2,4}
tarantool> box.space.bitset_example:insert{3,7}
tarantool> box.space.bitset_example:insert{4,3}
tarantool> box.space.bitset_example.index.bitset:select(2, {iterator='BITS_ANY_SET'})

The result will be:

---
- - [3, 7]
  - [4, 3]
...

because (7 AND 2) is not equal to 0, and (3 AND 2) is not equal to 0.

RTREE example:

tarantool> box.schema.space.create('rtree_example')
tarantool> box.space.rtree_example:create_index('primary')
tarantool> box.space.rtree_example:create_index('rtree',{unique=false,type='RTREE', parts={2,'ARRAY'}})
tarantool> box.space.rtree_example:insert{1, {3, 5, 9, 10}}
tarantool> box.space.rtree_example:insert{2, {10, 11}}
tarantool> box.space.rtree_example.index.rtree:select({4, 7, 5, 9}, {iterator = 'GT'})

The result will be:

---
- - [1, [3, 5, 9, 10]]
...

because a rectangle whose corners are at coordinates 4,7,5,9 is entirely within a rectangle whose corners are at coordinates 3,5,9,10.

Additionally, there exist index iterator operations. They can only be used with code in Lua and C/C++. Index iterators are for traversing indexes one key at a time, taking advantage of features that are specific to an index type, for example evaluating Boolean expressions when traversing BITSET indexes, or going in descending order when traversing TREE indexes.

See also other index operations like alter() (modify index) and drop() (delete index) in reference for box.index submodule.

Complexity factors¶

In reference for box.space and box.index submodules, there are notes about which complexity factors might affect the resource usage of each function.