[Tarantool-patches] [PATCH] Formatted select in lua console

[Tarantool-patches] [PATCH] Formatted select in lua console

[Aleksandr Lyapunov]

Introduce space:fselect and index:fselect.
The example is better than explanation:

tarantool> _user:fselect{}
---
- - ​+-----+-----+-------------+------+--------------------------------------------+
  - ​| id  |owner|    name     | type |                    auth                    |
  - ​+-----+-----+-------------+------+--------------------------------------------+
  - ​|  0  |  1  |   "guest"   |"user"|{"chap-sha1":"vhvewKp0tNyweZQ+cFKAlsyphfg="}|
  - ​|  1  |  1  |   "admin"   |"user"|                     {}                     |
  - ​|  2  |  1  |  "public"   |"role"|                     {}                     |
  - ​|  3  |  1  |"replication"|"role"|                     {}                     |
  - ​| 31  |  1  |   "super"   |"role"|                     {}                     |
  - ​+-----+-----+-------------+------+--------------------------------------------+
...


Aleksandr Lyapunov (1):
  Introduce fselect - formatted select

 src/box/lua/schema.lua      | 105 ++++++++++++++++++++++++++++++++
 test/engine/select.result   | 142 ++++++++++++++++++++++++++++++++++++++++++++
 test/engine/select.test.lua |  25 ++++++++
 3 files changed, 272 insertions(+)

-- 
2.7.4

[Tarantool-patches] [PATCH] Introduce fselect - formatted select

[Aleksandr Lyapunov]

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

Re: [Tarantool-patches] [PATCH] Introduce fselect - formatted select

[Alexander Turenko]

I have not much to say here. It's nice. It is for humans and so unlikely
we should provide some guarantees except options meaning.

My first thoughts were how to generalize and allow to reuse it.
box.execute() also provides columns metainfo, but the format is a bit
different. I guess it'll be the first request to us: support such output
in SQL. Maybe just for box.execute(), via Lua. However we can be
consider it as a separate task.

I also had thoughts whether we should tweak formatting things on some
other level: say, provide an option for :select(), which sets a
metatable with particular __serialize function. So data will be
available as usual in a script, but will be formatted this way in the
interactive console. I'm not sure, because there is the donwside: it
surely not so short as 'fselect'.

I see that you continue to improve the patch on the branch: now 'sql',
'github' and 'jira' delimiters are supported. For me it looks like it
goes to be a separate module for formatting tables (like 'csv' we
already have). Not just 'formatted select' thing. However here I'm not
sure too.

That's all. It is nice and it is okay to push from my side.

Several nits are below.

WBR, Alexander Turenko.

> +-- index.fselect - formatted select.
> +base_index_mt.fselect = function(index, key, opts)
> +    -- options.
> +    local max_width = 140

We can obtain terminal width and use it as default:

 | tarantool> ffi = require('ffi')
 | tarantool> ffi.cdef('void rl_get_screen_size(int *rows, int *cols);')
 | tarantool> colsp = ffi.new('int[1]')
 | tarantool> ffi.C.rl_get_screen_size(nil, colsp)
 | tarantool> colsp[0]
 | ---
 | - 190
 | ...

It returns zero, when I run it from a file (not from the console), so a
default when there is no terminal size information is necessary anyway.

Not sure about portability of calling rl_get_screen_size(), though.

> +    local num_rows = #tab
> +    local space = box.space[index.space_id]
> +    local fmt = space:format() 

Nit: trailing whitespace.

> +    -- 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)

There is quite similar string.center() function (src/lua/string.lua).

Re: [Tarantool-patches] [PATCH] Formatted select in lua console

[Aleksandr Lyapunov]

GH Issue: https://github.com/tarantool/tarantool/issues/5161
GH branch: 
https://github.com/tarantool/tarantool/tree/alyapunov/gh-5161-fselect

On 10.07.2020 13:03, Aleksandr Lyapunov wrote:
> Introduce space:fselect and index:fselect.
> The example is better than explanation:
>
> tarantool> _user:fselect{}
> ---
> - - ​+-----+-----+-------------+------+--------------------------------------------+
>    - ​| id  |owner|    name     | type |                    auth                    |
>    - ​+-----+-----+-------------+------+--------------------------------------------+
>    - ​|  0  |  1  |   "guest"   |"user"|{"chap-sha1":"vhvewKp0tNyweZQ+cFKAlsyphfg="}|
>    - ​|  1  |  1  |   "admin"   |"user"|                     {}                     |
>    - ​|  2  |  1  |  "public"   |"role"|                     {}                     |
>    - ​|  3  |  1  |"replication"|"role"|                     {}                     |
>    - ​| 31  |  1  |   "super"   |"role"|                     {}                     |
>    - ​+-----+-----+-------------+------+--------------------------------------------+
> ...
>
>
> Aleksandr Lyapunov (1):
>    Introduce fselect - formatted select
>
>   src/box/lua/schema.lua      | 105 ++++++++++++++++++++++++++++++++
>   test/engine/select.result   | 142 ++++++++++++++++++++++++++++++++++++++++++++
>   test/engine/select.test.lua |  25 ++++++++
>   3 files changed, 272 insertions(+)
>

Re: [Tarantool-patches] [PATCH] Formatted select in lua console

[Oleg Babin]

Hi! Thanks for your patch!

It's not a review, but I just tried to use it. Works fine for default 
yaml console. But seems it completely broken for lua output.


```

tarantool> box.space.test:fselect()
---
- - ​+-----+-----+-----+-----+-----+
   - ​|col1 |col2 |col3 |col4 |col5 |
   - ​+-----+-----+-----+-----+-----+
   - ​|  1  |     |     |     |     |
   - ​|  2  |  3  |     |     |     |
   - ​|  4  |  5  |null |null | 123 |
   - ​+-----+-----+-----+-----+-----+
...

tarantool> \set output lua
true;
tarantool> box.space.test:fselect()
{"\xe2\x80\x8b+-----+-----+-----+-----+-----+", 
"\xe2\x80\x8b|col1\xc2\xa0|col2\xc2\xa0|col3\xc2\xa0|col4\xc2\xa0|col5\xc2\xa0|", 
"\xe2\x80\x8b+-----+-----+-----+-----+-----+", 
"\xe2\x80\x8b|\xc2\xa0\xc2\xa01\xc2\xa0\xc2\xa0|\xc2\xa0\xc2\xa0\xc2\xa0\xc2\xa0\xc2\xa0|\xc2\xa0\xc2\xa0\xc2\xa0\xc2\xa0\xc2\xa0|\xc2\xa0\xc2\xa0\xc2\xa0\xc2\xa0\xc2\xa0|\xc2\xa0\xc2\xa0\xc2\xa0\xc2\xa0\xc2\xa0|", 
"\xe2\x80\x8b|\xc2\xa0\xc2\xa02\xc2\xa0\xc2\xa0|\xc2\xa0\xc2\xa03\xc2\xa0\xc2\xa0|\xc2\xa0\xc2\xa0\xc2\xa0\xc2\xa0\xc2\xa0|\xc2\xa0\xc2\xa0\xc2\xa0\xc2\xa0\xc2\xa0|\xc2\xa0\xc2\xa0\xc2\xa0\xc2\xa0\xc2\xa0|", 
"\xe2\x80\x8b|\xc2\xa0\xc2\xa04\xc2\xa0\xc2\xa0|\xc2\xa0\xc2\xa05\xc2\xa0\xc2\xa0|null\xc2\xa0|null\xc2\xa0|\xc2\xa0123\xc2\xa0|", 
"\xe2\x80\x8b+-----+-----+-----+-----+-----+"};

```

May be we should throw an error for "lua output" case.

On 10/07/2020 13:03, Aleksandr Lyapunov wrote:
> Introduce space:fselect and index:fselect.
> The example is better than explanation:
>
> tarantool> _user:fselect{}
> ---
> - - ​+-----+-----+-------------+------+--------------------------------------------+
>    - ​| id  |owner|    name     | type |                    auth                    |
>    - ​+-----+-----+-------------+------+--------------------------------------------+
>    - ​|  0  |  1  |   "guest"   |"user"|{"chap-sha1":"vhvewKp0tNyweZQ+cFKAlsyphfg="}|
>    - ​|  1  |  1  |   "admin"   |"user"|                     {}                     |
>    - ​|  2  |  1  |  "public"   |"role"|                     {}                     |
>    - ​|  3  |  1  |"replication"|"role"|                     {}                     |
>    - ​| 31  |  1  |   "super"   |"role"|                     {}                     |
>    - ​+-----+-----+-------------+------+--------------------------------------------+
> ...
>
>
> Aleksandr Lyapunov (1):
>    Introduce fselect - formatted select
>
>   src/box/lua/schema.lua      | 105 ++++++++++++++++++++++++++++++++
>   test/engine/select.result   | 142 ++++++++++++++++++++++++++++++++++++++++++++
>   test/engine/select.test.lua |  25 ++++++++
>   3 files changed, 272 insertions(+)
>

Re: [Tarantool-patches] [PATCH] Formatted select in lua console

[Aleksandr Lyapunov]

Hello, thanks for comments!

Honestly speaking I didn't know about "lua output". As I see event 
"\help" is broken in lua output too )
As I understood lua output escapes almost all whitespaces and that's why 
is not suitable for formatting.
I'm not sure that it's possible to canonically catch output method in 
lua console. But if we could - we should not prohibit usage of fselect - 
it's still good for printing (via print() ), logging etc.

On 10.07.2020 18:37, Oleg Babin wrote:
> Hi! Thanks for your patch!
>
> It's not a review, but I just tried to use it. Works fine for default 
> yaml console. But seems it completely broken for lua output.
>