Broken formatting in \d

j-bennet commented 5 years ago

Here's one odd looking tabulate.

Litecli version: 1.0.0

~/repos/engineering/rebuilds.sqlite> \d
+------------------------------------------------------------------------------+
| sql                                                                          |
+------------------------------------------------------------------------------+
| CREATE TABLE abc(a text)                                                     |
| CREATE TABLE rebuild_jobs_local (                                            |
|       id INTEGER NOT NULL,                                                        |
|       source VARCHAR(9),                                                          |
|       started DATETIME,                                                           |
|       submitted DATETIME,                                                         |
|       finished DATETIME,                                                          |
|       start_date DATETIME NOT NULL,                                               |
|       end_date DATETIME NOT NULL,                                                 |
|       apikeys TEXT NOT NULL,                                                      |
|       args TEXT,                                                                  |
|       rq_job_id TEXT,                                                             |
|       updated DATETIME DEFAULT CURRENT_TIMESTAMP,                                 |
|       PRIMARY KEY (id),                                                           |
|       CONSTRAINT rebuildsource CHECK (source IN ('daily', 'autobuild', 'manual')) |
| )                                                                            |

zzl0 commented 5 years ago

I cannot reproduce it on my laptop. Can you repproduce this problem?

ex1> \d
+-------------------------------------------------------------------------------------+
| sql                                                                                 |
+-------------------------------------------------------------------------------------+
| CREATE TABLE abc(a text)                                                            |
| CREATE TABLE rebuild_jobs_local (                                                   |
|        id INTEGER NOT NULL,                                                         |
|        source VARCHAR(9),                                                           |
|        started DATETIME,                                                            |
|        submitted DATETIME,                                                          |
|        finished DATETIME,                                                           |
|        start_date DATETIME NOT NULL,                                                |
|        end_date DATETIME NOT NULL,                                                  |
|        apikeys TEXT NOT NULL,                                                       |
|        args TEXT,                                                                   |
|        rq_job_id TEXT,                                                              |
|        updated DATETIME DEFAULT CURRENT_TIMESTAMP,                                  |
|        PRIMARY KEY (id),                                                            |
|        CONSTRAINT rebuildsource CHECK (source IN ('daily', 'autobuild', 'manual'))) |
+-------------------------------------------------------------------------------------+
Time: 0.006s

j-bennet commented 5 years ago

Hmm, perhaps try with the whole schema (I only posted a part of it)? Here it is:

+------------------------------------------------------------------------------+
| sql                                                                          |
+------------------------------------------------------------------------------+
| CREATE TABLE abc(a text)                                                     |
| CREATE TABLE rebuild_jobs_local (                                            |
|   id INTEGER NOT NULL,                                                        |
|   source VARCHAR(9),                                                          |
|   started DATETIME,                                                           |
|   submitted DATETIME,                                                         |
|   finished DATETIME,                                                          |
|   start_date DATETIME NOT NULL,                                               |
|   end_date DATETIME NOT NULL,                                                 |
|   apikeys TEXT NOT NULL,                                                      |
|   args TEXT,                                                                  |
|   rq_job_id TEXT,                                                             |
|   updated DATETIME DEFAULT CURRENT_TIMESTAMP,                                 |
|   PRIMARY KEY (id),                                                           |
|   CONSTRAINT rebuildsource CHECK (source IN ('daily', 'autobuild', 'manual')) |
| )                                                                            |
| CREATE TABLE rebuild_statuses_local (                                        |
|   id INTEGER NOT NULL,                                                        |
|   job_id INTEGER NOT NULL,                                                    |
|   apikey VARCHAR NOT NULL,                                                    |
|   date DATETIME NOT NULL,                                                     |
|   success BOOLEAN,                                                            |
|   total_events INTEGER,                                                       |
|   invalid_events INTEGER,                                                     |
|   updated DATETIME DEFAULT CURRENT_TIMESTAMP,                                 |
|   generated DATETIME,                                                         |
|   indexing DATETIME,                                                          |
|   "indexed" DATETIME,                                                         |
|   index_ts DATETIME,                                                          |
|   PRIMARY KEY (id),                                                           |
|   FOREIGN KEY(job_id) REFERENCES rebuild_jobs_local (id),                     |
|   CHECK (success IN (0, 1))                                                   |
| )                                                                            |
+------------------------------------------------------------------------------+

j-bennet commented 5 years ago

Also here are my package versions that may be relevant:

cli-helpers==1.1.0
tabulate==0.8.2
terminaltables==3.1.0

zzl0 commented 5 years ago

Still works on my laptop.

ex1> \d
+-------------------------------------------------------------------------------------+
| sql                                                                                 |
+-------------------------------------------------------------------------------------+
| CREATE TABLE abc(a text)                                                            |
| CREATE TABLE rebuild_jobs_local (                                                   |
|        id INTEGER NOT NULL,                                                         |
|        source VARCHAR(9),                                                           |
|        started DATETIME,                                                            |
|        submitted DATETIME,                                                          |
|        finished DATETIME,                                                           |
|        start_date DATETIME NOT NULL,                                                |
|        end_date DATETIME NOT NULL,                                                  |
|        apikeys TEXT NOT NULL,                                                       |
|        args TEXT,                                                                   |
|        rq_job_id TEXT,                                                              |
|        updated DATETIME DEFAULT CURRENT_TIMESTAMP,                                  |
|        PRIMARY KEY (id),                                                            |
|        CONSTRAINT rebuildsource CHECK (source IN ('daily', 'autobuild', 'manual'))) |
| CREATE TABLE rebuild_statuses_local (                                               |
|    id INTEGER NOT NULL,                                                             |
|    job_id INTEGER NOT NULL,                                                         |
|    apikey VARCHAR NOT NULL,                                                         |
|    date DATETIME NOT NULL,                                                          |
|    success BOOLEAN,                                                                 |
|    total_events INTEGER,                                                            |
|    invalid_events INTEGER,                                                          |
|    updated DATETIME DEFAULT CURRENT_TIMESTAMP,                                      |
|    generated DATETIME,                                                              |
|    indexing DATETIME,                                                               |
|    "indexed" DATETIME,                                                              |
|    index_ts DATETIME,                                                               |
|    PRIMARY KEY (id),                                                                |
|    FOREIGN KEY(job_id) REFERENCES rebuild_jobs_local (id),                          |
|    CHECK (success IN (0, 1))                                                        |
|  )                                                                                  |
+-------------------------------------------------------------------------------------+
Time: 0.014s

I use the same versions of those 3 libraries, below is the ouput of pip freeze in my venv.

pip freeze

```text asn1crypto==0.24.0 atomicwrites==1.2.1 attrs==18.2.0 autopep8==1.3.3 behave==1.2.6 certifi==2018.8.24 cffi==1.11.5 chardet==3.0.4 cli-helpers==1.1.0 click==6.7 codecov==2.0.9 colorama==0.3.9 configobj==5.0.6 coverage==4.3.4 cryptography==2.3.1 docformatter==1.0 filelock==3.0.9 idna==2.7 -e git+https://github.com/dbcli/litecli.git@3fdc97dc26087a0894c36988702d711f706f84cf#egg=litecli mock==2.0.0 more-itertools==4.3.0 parse==1.9.0 parse-type==0.4.2 pbr==4.3.0 pep8radius==0.9.2 pexpect==4.6.0 pkginfo==1.4.2 pluggy==0.7.1 prompt-toolkit==2.0.7 ptyprocess==0.6.0 py==1.6.0 pycodestyle==2.4.0 pycparser==2.19 Pygments==2.2.0 pytest==3.8.2 pytest-cov==2.4.0 requests==2.19.1 requests-toolbelt==0.8.0 six==1.12.0 sqlparse==0.2.4 tabulate==0.8.2 terminaltables==3.1.0 toml==0.10.0 tox==3.5.1 tqdm==4.26.0 twine==1.10.0 untokenize==0.1.1 urllib3==1.23 virtualenv==16.0.0 wcwidth==0.1.7 yapf==0.24.0 ```

zzl0 commented 5 years ago

oh, I think I know the reason, it seems your output contains \t and begining of each attribute.

j-bennet commented 5 years ago

Why would that be?

zzl0 commented 5 years ago

It was just a guess. But I have found an easy way to reproduce it.

How to reproduce?

Since litecli doesn't allow me to type tab in the REPL, I used sqlite3 CLI to create a table as below

$ sqlite3 ex1
sqlite> create table aaa (
   ...>     bbb varchar not null
   ...> );

Verified the output in litecli:

$ litecli ex1
Version: 1.0.0
Mail: https://groups.google.com/forum/#!forum/litecli-users
Github: https://github.com/dbcli/litecli
ex2> \d
+-----------------------+
| sql                   |
+-----------------------+
| CREATE TABLE aaa (    |
|   bbb varchar not null |
| )                     |
+-----------------------+
Time: 0.013s

Why would that be?

It's a problem of TabularOutputFormatter in cli_helpers, because the table formatter treat tab as a normal character while the terminal treat it in a different way (e.g. 4 or 8 space wide), here is how to reproduce it with TabularOutputFormatter:

>>> from cli_helpers.tabular_output import TabularOutputFormatter
>>> formatter = TabularOutputFormatter('ascii')
>>> def format_table(table, headers):
...     for i in formatter.format_output(table, headers):
...         print(i)
...
>>> table = [["CREATE TABLE aaa ("], [" bbb varchar not null"]]
>>> headers = ["tables"]
>>> format_table(table, headers)
+-----------------------+
| tables                |
+-----------------------+
| CREATE TABLE aaa (    |
|   bbb varchar not null |
+-----------------------+

Possible way to fix it

>>> table2 = [[i.replace('\t', ' '*4) for i in row] for row in table]
>>> format_table(table2, headers)
+--------------------------+
| tables                   |
+--------------------------+
| CREATE TABLE aaa (       |
|     bbb varchar not null |
+--------------------------+

The is just a rough idea, we need to be careful to replace tab, because a default value might also contain tab.

If you agree with this approach, I can continue to think about how to fix it and submit a PR to cli_helpers.

meeuw commented 5 years ago

This should be fixed in cli_helpers (as a preprocessor) or even tabulate / terminal tables. Mycli/pgcli have the same issue with tabs.

We might also consider replacing tabs with \t or ↹

dbcli / litecli