[Tarantool-patches] [PATCH] Introduce fselect - formatted select
Aleksandr Lyapunov
alyapunov at tarantool.org
Fri Jul 10 13:03:54 MSK 2020
space:fselect and index:fselect fetchs data like ordinal select,
but formats the result like mysql does - with columns, column
names etc.
See test/engine/select.test.lua for examples.
Closes #5161
---
src/box/lua/schema.lua | 105 ++++++++++++++++++++++++++++++++
test/engine/select.result | 142 ++++++++++++++++++++++++++++++++++++++++++++
test/engine/select.test.lua | 25 ++++++++
3 files changed, 272 insertions(+)
diff --git a/src/box/lua/schema.lua b/src/box/lua/schema.lua
index 2edf25f..6d0a7a3 100644
--- a/src/box/lua/schema.lua
+++ b/src/box/lua/schema.lua
@@ -1400,6 +1400,107 @@ base_index_mt.bsize = function(index)
end
return tonumber(ret)
end
+-- index.fselect - formatted select.
+base_index_mt.fselect = function(index, key, opts)
+ -- options.
+ local max_width = 140
+ local min_col_width = 5
+ if opts and opts.max_width and type(opts.max_width) == 'number' then
+ max_width = opts.max_width
+ end
+
+ -- select and stringify.
+ local tab = { }
+ local json = require('json')
+ for _,t in index:pairs(key, opts) do
+ local row = { }
+ for _,f in t:pairs() do
+ table.insert(row, json.encode(f))
+ end
+ table.insert(tab, row)
+ end
+
+ local num_rows = #tab
+ local space = box.space[index.space_id]
+ local fmt = space:format()
+ local num_cols = math.max(#fmt, 1)
+ for i = 1,num_rows do
+ num_cols = math.max(num_cols, #tab[i])
+ end
+
+ local names = {}
+ for j = 1,num_cols do
+ table.insert(names, fmt[j] and fmt[j].name or 'col' .. tostring(j))
+ end
+ local widths = {}
+ local real_width = num_cols + 1 -- including '|' symbols
+ for j = 1,num_cols do
+ local width = math.max(names[j]:len(), min_col_width)
+ for i = 1,num_rows do
+ if tab[i][j] then
+ width = math.max(width, tab[i][j]:len())
+ end
+ end
+ real_width = real_width + width
+ table.insert(widths, width)
+ end
+
+ -- cut some columns if its width is too big
+ while (real_width > max_width) do
+ local max_j = 1
+ for j = 2,num_cols do
+ if widths[j] >= widths[max_j] then max_j = j end
+ end
+ widths[max_j] = widths[max_j] - 1
+ real_width = real_width - 1
+ end
+
+ -- I guess there's a bug and yaml treats string '+---' as not a string.
+ -- As a workaround let's prefix all strings with invisible space.
+ local prefix = string.char(0xE2) .. string.char(0x80) .. string.char(0x8B)
+
+ local delim_row = prefix .. '+'
+ for j = 1,num_cols do
+ delim_row = delim_row .. string.rep('-', widths[j]) .. '+'
+ end
+
+ -- format string - cut or fill with spaces to make is exactly n symbols.
+ -- also replace spaces with non-break spaces.
+ local fmt_str = function(x, n)
+ if not x then x = '' end
+ local str
+ if x:len() <= n then
+ local add = n - x:len()
+ local addl = math.floor(add/2)
+ local addr = math.ceil(add/2)
+ str = string.rep(' ', addl) .. x .. string.rep(' ', addr)
+ else
+ str = x:sub(1, n)
+ end
+ return str:gsub("%s",string.char(0xC2) .. string.char(0xA0))
+ end
+
+ local res = {}
+
+ -- insert into res a string with formatted row.
+ local res_insert = function(row)
+ local str_row = prefix .. '|'
+ for j = 1,num_cols do
+ str_row = str_row .. fmt_str(row[j], widths[j]) .. '|'
+ end
+ table.insert(res, str_row)
+ end
+
+ -- format result
+ table.insert(res, delim_row)
+ res_insert(names)
+ table.insert(res, delim_row)
+ for i = 1,num_rows do
+ res_insert(tab[i])
+ end
+ table.insert(res, delim_row)
+ return res
+end
-- Lua 5.2 compatibility
base_index_mt.__len = base_index_mt.len
-- min and max
@@ -1641,6 +1742,10 @@ space_mt.select = function(space, key, opts)
check_space_arg(space, 'select')
return check_primary_index(space):select(key, opts)
end
+space_mt.fselect = function(space, key, opts)
+ check_space_arg(space, 'select')
+ return check_primary_index(space):fselect(key, opts)
+end
space_mt.insert = function(space, tuple)
check_space_arg(space, 'insert')
return internal.insert(space.id, tuple);
diff --git a/test/engine/select.result b/test/engine/select.result
index 8f37ac9..15ea61e 100644
--- a/test/engine/select.result
+++ b/test/engine/select.result
@@ -3429,3 +3429,145 @@ index3:select{1235}
space:drop()
---
...
+--https://github.com/tarantool/tarantool/issues/5161
+--formatted select
+s = box.schema.space.create('test', { engine = engine })
+---
+...
+i1 = s:create_index('test1')
+---
+...
+i2 = s:create_index('test2', {parts={{2, 'unsigned'}}})
+---
+...
+_ = s:replace{1, 2, 3, 4, box.NULL, 5}
+---
+...
+_ = s:replace{3, 4, true, {1, 2, 3}}
+---
+...
+_ = s:replace{5, 6, false, {1, 2, 3, ['key']='value'}}
+---
+...
+_ = s:replace{3, 4, true, {1, {3, {aa=1,bb=2}}, 3}}
+---
+...
+_ = s:replace{7, 8, 'asdgsdgswegg', 'sdf', 'dsgfsdgsegasges' }
+---
+...
+s:fselect()
+---
+- - +-----+-----+--------------+---------------------------------+-----------------+-----+
+ - |col1 |col2 | col3 | col4 | col5 |col6 |
+ - +-----+-----+--------------+---------------------------------+-----------------+-----+
+ - | 1 | 2 | 3 | 4 | null | 5 |
+ - | 3 | 4 | true | [1,[3,{"aa":1,"bb":2}],3] | | |
+ - | 5 | 6 | false |{"1":1,"2":2,"3":3,"key":"value"}| | |
+ - | 7 | 8 |"asdgsdgswegg"| "sdf" |"dsgfsdgsegasges"| |
+ - +-----+-----+--------------+---------------------------------+-----------------+-----+
+...
+s:fselect({5}, {iterator='le'})
+---
+- - +-----+-----+-----+---------------------------------+-----+-----+
+ - |col1 |col2 |col3 | col4 |col5 |col6 |
+ - +-----+-----+-----+---------------------------------+-----+-----+
+ - | 5 | 6 |false|{"1":1,"2":2,"3":3,"key":"value"}| | |
+ - | 3 | 4 |true | [1,[3,{"aa":1,"bb":2}],3] | | |
+ - | 1 | 2 | 3 | 4 |null | 5 |
+ - +-----+-----+-----+---------------------------------+-----+-----+
+...
+s:fselect({5}, {iterator='le', max_width=40})
+---
+- - +-----+-----+-----+--------+-----+-----+
+ - |col1 |col2 |col3 | col4 |col5 |col6 |
+ - +-----+-----+-----+--------+-----+-----+
+ - | 5 | 6 |false|{"1":1,"| | |
+ - | 3 | 4 |true |[1,[3,{"| | |
+ - | 1 | 2 | 3 | 4 |null | 5 |
+ - +-----+-----+-----+--------+-----+-----+
+...
+i1:fselect({3})
+---
+- - +-----+-----+-----+-------------------------+
+ - |col1 |col2 |col3 | col4 |
+ - +-----+-----+-----+-------------------------+
+ - | 3 | 4 |true |[1,[3,{"aa":1,"bb":2}],3]|
+ - +-----+-----+-----+-------------------------+
+...
+i2:fselect({6})
+---
+- - +-----+-----+-----+---------------------------------+
+ - |col1 |col2 |col3 | col4 |
+ - +-----+-----+-----+---------------------------------+
+ - | 5 | 6 |false|{"1":1,"2":2,"3":3,"key":"value"}|
+ - +-----+-----+-----+---------------------------------+
+...
+i1:fselect({2})
+---
+- - +-----+
+ - |col1 |
+ - +-----+
+ - +-----+
+...
+i2:fselect({5})
+---
+- - +-----+
+ - |col1 |
+ - +-----+
+ - +-----+
+...
+s:format{{name='name', type='unsigned'}, {name='veeeeeeeery long name', type='unsigned'}}
+---
+...
+s:fselect()
+---
+- - +-----+---------------------+--------------+---------------------------------+-----------------+-----+
+ - |name |veeeeeeeery long name| col3 | col4 | col5 |col6 |
+ - +-----+---------------------+--------------+---------------------------------+-----------------+-----+
+ - | 1 | 2 | 3 | 4 | null | 5 |
+ - | 3 | 4 | true | [1,[3,{"aa":1,"bb":2}],3] | | |
+ - | 5 | 6 | false |{"1":1,"2":2,"3":3,"key":"value"}| | |
+ - | 7 | 8 |"asdgsdgswegg"| "sdf" |"dsgfsdgsegasges"| |
+ - +-----+---------------------+--------------+---------------------------------+-----------------+-----+
+...
+s:fselect({}, {max_width=40})
+---
+- - +-----+------+------+------+-----+-----+
+ - |name |veeeee| col3 | col4 |col5 |col6 |
+ - +-----+------+------+------+-----+-----+
+ - | 1 | 2 | 3 | 4 |null | 5 |
+ - | 3 | 4 | true |[1,[3,| | |
+ - | 5 | 6 |false |{"1":1| | |
+ - | 7 | 8 |"asdgs|"sdf" |"dsgf| |
+ - +-----+------+------+------+-----+-----+
+...
+i1:fselect({3})
+---
+- - +-----+---------------------+-----+-------------------------+
+ - |name |veeeeeeeery long name|col3 | col4 |
+ - +-----+---------------------+-----+-------------------------+
+ - | 3 | 4 |true |[1,[3,{"aa":1,"bb":2}],3]|
+ - +-----+---------------------+-----+-------------------------+
+...
+i2:fselect({6})
+---
+- - +-----+---------------------+-----+---------------------------------+
+ - |name |veeeeeeeery long name|col3 | col4 |
+ - +-----+---------------------+-----+---------------------------------+
+ - | 5 | 6 |false|{"1":1,"2":2,"3":3,"key":"value"}|
+ - +-----+---------------------+-----+---------------------------------+
+...
+i1:fselect({2})
+---
+- - +-----+---------------------+
+ - |name |veeeeeeeery long name|
+ - +-----+---------------------+
+ - +-----+---------------------+
+...
+i2:fselect({5})
+---
+- - +-----+---------------------+
+ - |name |veeeeeeeery long name|
+ - +-----+---------------------+
+ - +-----+---------------------+
+...
diff --git a/test/engine/select.test.lua b/test/engine/select.test.lua
index 7b34541..cfe3bc2 100644
--- a/test/engine/select.test.lua
+++ b/test/engine/select.test.lua
@@ -111,3 +111,28 @@ index2:get{104}
index3:get{9}
index3:select{1235}
space:drop()
+
+--https://github.com/tarantool/tarantool/issues/5161
+--formatted select
+s = box.schema.space.create('test', { engine = engine })
+i1 = s:create_index('test1')
+i2 = s:create_index('test2', {parts={{2, 'unsigned'}}})
+_ = s:replace{1, 2, 3, 4, box.NULL, 5}
+_ = s:replace{3, 4, true, {1, 2, 3}}
+_ = s:replace{5, 6, false, {1, 2, 3, ['key']='value'}}
+_ = s:replace{3, 4, true, {1, {3, {aa=1,bb=2}}, 3}}
+_ = s:replace{7, 8, 'asdgsdgswegg', 'sdf', 'dsgfsdgsegasges' }
+s:fselect()
+s:fselect({5}, {iterator='le'})
+s:fselect({5}, {iterator='le', max_width=40})
+i1:fselect({3})
+i2:fselect({6})
+i1:fselect({2})
+i2:fselect({5})
+s:format{{name='name', type='unsigned'}, {name='veeeeeeeery long name', type='unsigned'}}
+s:fselect()
+s:fselect({}, {max_width=40})
+i1:fselect({3})
+i2:fselect({6})
+i1:fselect({2})
+i2:fselect({5})
--
2.7.4
More information about the Tarantool-patches
mailing list