[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