Примеры CRUD-операций
This section shows basic usage scenarios and typical errors for each data operation in Tarantool: INSERT, DELETE, UPDATE, UPSERT, REPLACE, and SELECT. Before trying out the examples, you need to bootstrap a Tarantool instance as shown below.
-- Create a space --
bands = box.schema.space.create('bands')
-- Specify field names and types --
box.space.bands:format({
{ name = 'id', type = 'unsigned' },
{ name = 'band_name', type = 'string' },
{ name = 'year', type = 'unsigned' }
})
-- Create a primary index --
box.space.bands:create_index('primary', { parts = { 'id' } })
-- Create a unique secondary index --
box.space.bands:create_index('band', { parts = { 'band_name' } })
-- Create a non-unique secondary index --
box.space.bands:create_index('year', { parts = { { 'year' } }, unique = false })
-- Create a multi-part index --
box.space.bands:create_index('year_band', { parts = { { 'year' }, { 'band_name' } } })
The space_object.insert method accepts a well-formatted tuple.
-- Insert a tuple with a unique primary key --
tarantool> bands:insert{1, 'Scorpions', 1965}
---
- [1, 'Scorpions', 1965]
...
insert
also checks all the keys for duplicates.
-- Try to insert a tuple with a duplicate primary key --
tarantool> bands:insert{1, 'Scorpions', 1965}
---
- error: Duplicate key exists in unique index "primary" in space "bands" with old
tuple - [1, "Scorpions", 1965] and new tuple - [1, "Scorpions", 1965]
...
-- Try to insert a tuple with a duplicate secondary key --
tarantool> bands:insert{2, 'Scorpions', 1965}
---
- error: Duplicate key exists in unique index "band" in space "bands" with old tuple
- [1, "Scorpions", 1965] and new tuple - [2, "Scorpions", 1965]
...
-- Insert a second tuple with unique primary and secondary keys --
tarantool> bands:insert{2, 'Pink Floyd', 1965}
---
- [2, 'Pink Floyd', 1965]
...
-- Delete all tuples --
tarantool> bands:truncate()
---
...
space_object.delete allows you to delete a tuple identified by the primary key.
-- Insert test data --
tarantool> bands:insert{1, 'Roxette', 1986}
bands:insert{2, 'Scorpions', 1965}
bands:insert{3, 'Ace of Base', 1987}
bands:insert{4, 'The Beatles', 1960}
-- Delete a tuple with an existing key --
tarantool> bands:delete{4}
---
- [4, 'The Beatles', 1960]
...
tarantool> bands:select()
---
- - [1, 'Roxette', 1986]
- [2, 'Scorpions', 1965]
- [3, 'Ace of Base', 1987]
...
You can also use index_object.delete to delete a tuple by the specified unique index.
-- Delete a tuple by the primary index --
tarantool> bands.index.primary:delete{3}
---
- [3, 'Ace of Base', 1987]
...
tarantool> bands:select()
---
- - [1, 'Roxette', 1986]
- [2, 'Scorpions', 1965]
...
-- Delete a tuple by a unique secondary index --
tarantool> bands.index.band:delete{'Scorpions'}
---
- [2, 'Scorpions', 1965]
...
tarantool> bands:select()
---
- - [1, 'Roxette', 1986]
...
-- Try to delete a tuple by a non-unique secondary index --
tarantool> bands.index.year:delete(1986)
---
- error: Get() doesn't support partial keys and non-unique indexes
...
tarantool> bands:select()
---
- - [1, 'Roxette', 1986]
...
-- Try to delete a tuple by a partial key --
tarantool> bands.index.year_band:delete('Roxette')
---
- error: Invalid key part count in an exact match (expected 2, got 1)
...
-- Delete a tuple by a full key --
tarantool> bands.index.year_band:delete{1986, 'Roxette'}
---
- [1, 'Roxette', 1986]
...
tarantool> bands:select()
---
- []
...
-- Delete all tuples --
tarantool> bands:truncate()
---
...
space_object.update allows you to update a tuple identified by the primary key.
Similarly to delete
, the update
method accepts a full key and also an operation to execute.
-- Insert test data --
tarantool> bands:insert{1, 'Roxette', 1986}
bands:insert{2, 'Scorpions', 1965}
bands:insert{3, 'Ace of Base', 1987}
bands:insert{4, 'The Beatles', 1960}
-- Update a tuple with an existing key --
tarantool> bands:update({2}, {{'=', 2, 'Pink Floyd'}})
---
- [2, 'Pink Floyd', 1965]
...
tarantool> bands:select()
---
- - [1, 'Roxette', 1986]
- [2, 'Pink Floyd', 1965]
- [3, 'Ace of Base', 1987]
- [4, 'The Beatles', 1960]
...
index_object.update updates a tuple identified by the specified unique index.
-- Update a tuple by the primary index --
tarantool> bands.index.primary:update({2}, {{'=', 2, 'The Rolling Stones'}})
---
- [2, 'The Rolling Stones', 1965]
...
tarantool> bands:select()
---
- - [1, 'Roxette', 1986]
- [2, 'The Rolling Stones', 1965]
- [3, 'Ace of Base', 1987]
- [4, 'The Beatles', 1960]
...
-- Update a tuple by a unique secondary index --
tarantool> bands.index.band:update({'The Rolling Stones'}, {{'=', 2, 'The Doors'}})
---
- [2, 'The Doors', 1965]
...
tarantool> bands:select()
---
- - [1, 'Roxette', 1986]
- [2, 'The Doors', 1965]
- [3, 'Ace of Base', 1987]
- [4, 'The Beatles', 1960]
...
-- Try to update a tuple by a non-unique secondary index --
tarantool> bands.index.year:update({1965}, {{'=', 2, 'Scorpions'}})
---
- error: Get() doesn't support partial keys and non-unique indexes
...
tarantool> bands:select()
---
- - [1, 'Roxette', 1986]
- [2, 'The Doors', 1965]
- [3, 'Ace of Base', 1987]
- [4, 'The Beatles', 1960]
...
-- Delete all tuples --
tarantool> bands:truncate()
---
...
space_object.upsert updates an existing tuple or inserts a new one:
- If the existing tuple is found by the primary key, Tarantool applies the update operation to this tuple and ignores the new tuple.
- If no existing tuple is found, Tarantool inserts the new tuple and ignores the update operation.
tarantool> bands:insert{1, 'Scorpions', 1965}
---
- [1, 'Scorpions', 1965]
...
-- As the first argument, upsert accepts a tuple, not a key --
tarantool> bands:upsert({2}, {{'=', 2, 'Pink Floyd'}})
---
- error: Tuple field 2 (band_name) required by space format is missing
...
tarantool> bands:select()
---
- - [1, 'Scorpions', 1965]
...
tarantool> bands:delete(1)
---
- [1, 'Scorpions', 1965]
...
upsert
acts as insert
when no existing tuple is found by the primary key.
tarantool> bands:upsert({1, 'Scorpions', 1965}, {{'=', 2, 'The Doors'}})
---
...
-- As you can see, {1, 'Scorpions', 1965} is inserted, --
-- and the update operation is not applied. --
tarantool> bands:select()
---
- - [1, 'Scorpions', 1965]
...
-- upsert with the same primary key but different values in other fields --
-- applies the update operation and ignores the new tuple. --
tarantool> bands:upsert({1, 'Scorpions', 1965}, {{'=', 2, 'The Doors'}})
---
...
tarantool> bands:select()
---
- - [1, 'The Doors', 1965]
...
upsert
searches for the existing tuple by the primary index,
not by the secondary index. This can lead to a duplication error
if the tuple violates a secondary index uniqueness.
tarantool> bands:upsert({2, 'The Doors', 1965}, {{'=', 2, 'Pink Floyd'}})
---
- error: Duplicate key exists in unique index "band" in space "bands" with old tuple
- [1, "The Doors", 1965] and new tuple - [2, "The Doors", 1965]
...
tarantool> bands:select()
---
- - [1, 'The Doors', 1965]
...
-- This works if uniqueness is preserved. --
tarantool> bands:upsert({2, 'The Beatles', 1960}, {{'=', 2, 'Pink Floyd'}})
---
...
tarantool> bands:select()
---
- - [1, 'The Doors', 1965]
- [2, 'The Beatles', 1960]
...
-- Delete all tuples --
tarantool> bands:truncate()
---
...
space_object.replace accepts a well-formatted tuple and searches for the existing tuple by the primary key of the new tuple:
- If the existing tuple is found, Tarantool deletes it and inserts the new tuple.
- If no existing tuple is found, Tarantool inserts the new tuple.
tarantool> bands:replace{1, 'Scorpions', 1965}
---
- [1, 'Scorpions', 1965]
...
tarantool> bands:select()
---
- - [1, 'Scorpions', 1965]
...
tarantool> bands:replace{1, 'The Beatles', 1960}
---
- [1, 'The Beatles', 1960]
...
tarantool> bands:select()
---
- - [1, 'The Beatles', 1960]
...
tarantool> bands:truncate()
---
...
replace
can violate unique constraints, like upsert
does.
tarantool> bands:insert{1, 'Scorpions', 1965}
- [1, 'Scorpions', 1965]
...
tarantool> bands:insert{2, 'The Beatles', 1960}
---
- [2, 'The Beatles', 1960]
...
tarantool> bands:replace{2, 'Scorpions', 1965}
---
- error: Duplicate key exists in unique index "band" in space "bands" with old tuple
- [1, "Scorpions", 1965] and new tuple - [2, "Scorpions", 1965]
...
tarantool> bands:truncate()
---
...
The space_object.select request searches for a tuple or a set of tuples in the given space
by the primary key.
To search by the specified index, use index_object.select.
These methods work with any keys, including unique and non-unique, full and partial.
If a key is partial, select
searches by all keys where the prefix matches the specified key part.
tarantool> bands:insert{1, 'Roxette', 1986}
bands:insert{2, 'Scorpions', 1965}
bands:insert{3, 'The Doors', 1965}
bands:insert{4, 'The Beatles', 1960}
tarantool> bands:select(1)
---
- - [1, 'Roxette', 1986]
...
tarantool> bands:select()
---
- - [1, 'Roxette', 1986]
- [2, 'Scorpions', 1965]
- [3, 'The Doors', 1965]
- [4, 'The Beatles', 1960]
...
tarantool> bands.index.primary:select(2)
---
- - [2, 'Scorpions', 1965]
...
tarantool> bands.index.band:select('The Doors')
---
- - [3, 'The Doors', 1965]
...
tarantool> bands.index.year:select(1965)
---
- - [2, 'Scorpions', 1965]
- [3, 'The Doors', 1965]
...
This example illustrates 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 the Tarantool’s 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
The output below shows what happens if you invoke this 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
...
This examples shows how to display field names and field types of a system space – using metadata to find metadata.
Для начала: как можно сделать выборку кортежа из _space
, который описывает _space
?
A simple way is to look at the constants in box.schema
,
which shows that there is an item named SPACE_ID == 288,
so these statements retrieve the correct tuple:
box.space._space:select{ 288 }
-- или --
box.space._space:select{ box.schema.SPACE_ID }
Another way is to look at the tuples in box.space._index
,
which shows that there is a secondary index named „name“ for a space
number 288, so this statement also retrieve the correct tuple:
box.space._space.index.name:select{ '_space' }
Однако непросто прочитать информацию из полученного кортежа:
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': '*'}]]
...
Информация подается бессистемно, поскольку по формату поле №7 содержит рекомендованные имена и типы данных. Как же получить эти данные? Поскольку очевидно, что поле №7 представляет собой ассоциативный массив, цикл for
проведет организацию данных:
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, *
---
...