From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng2.m.smailru.net (smtpng2.m.smailru.net [94.100.179.3]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 31255445320 for ; Fri, 10 Jul 2020 13:03:55 +0300 (MSK) From: Aleksandr Lyapunov Date: Fri, 10 Jul 2020 13:03:54 +0300 Message-Id: <1594375434-743-2-git-send-email-alyapunov@tarantool.org> In-Reply-To: <1594375434-743-1-git-send-email-alyapunov@tarantool.org> References: <1594375434-743-1-git-send-email-alyapunov@tarantool.org> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 8bit Subject: [Tarantool-patches] [PATCH] Introduce fselect - formatted select List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: tarantool-patches@dev.tarantool.org Cc: alexander.turenko@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