From: Aleksandr Lyapunov <alyapunov@tarantool.org> To: tarantool-patches@dev.tarantool.org Cc: alexander.turenko@tarantool.org Subject: [Tarantool-patches] [PATCH] Introduce fselect - formatted select Date: Fri, 10 Jul 2020 13:03:54 +0300 [thread overview] Message-ID: <1594375434-743-2-git-send-email-alyapunov@tarantool.org> (raw) In-Reply-To: <1594375434-743-1-git-send-email-alyapunov@tarantool.org> 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
next prev parent reply other threads:[~2020-07-10 10:03 UTC|newest] Thread overview: 6+ messages / expand[flat|nested] mbox.gz Atom feed top 2020-07-10 10:03 [Tarantool-patches] [PATCH] Formatted select in lua console Aleksandr Lyapunov 2020-07-10 10:03 ` Aleksandr Lyapunov [this message] 2020-09-14 19:02 ` [Tarantool-patches] [PATCH] Introduce fselect - formatted select Alexander Turenko 2020-07-10 10:08 ` [Tarantool-patches] [PATCH] Formatted select in lua console Aleksandr Lyapunov 2020-07-10 15:37 ` Oleg Babin 2020-07-13 8:52 ` Aleksandr Lyapunov
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox Avoid top-posting and favor interleaved quoting: https://en.wikipedia.org/wiki/Posting_style#Interleaved_style * Reply using the --to, --cc, and --in-reply-to switches of git-send-email(1): git send-email \ --in-reply-to=1594375434-743-2-git-send-email-alyapunov@tarantool.org \ --to=alyapunov@tarantool.org \ --cc=alexander.turenko@tarantool.org \ --cc=tarantool-patches@dev.tarantool.org \ --subject='Re: [Tarantool-patches] [PATCH] Introduce fselect - formatted select' \ /path/to/YOUR_REPLY https://kernel.org/pub/software/scm/git/docs/git-send-email.html * If your mail client supports setting the In-Reply-To header via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox