dbcli/vcli

Vertica CLI with auto-completion and syntax highlighting

vcli: A REPL for Vertica

A Vertica client that does auto-completion and syntax highlighting, based on pgcli.

Installation

Just install it like a regular Python package:

$ pip install vcli

You'll probably see some permission errors if you're installing it on the system-wide Python. If that's what you want to do, you need to sudo:

$ sudo pip install vcli

To upgrade vcli, do:

$ pip install -U vcli

Usage

Connecting to a Database

$ vcli --help
Usage: vcli [OPTIONS] [DATABASE]

Options:
  -h, --host TEXT        Database server host address  [default: localhost]
  -p, --port INTEGER     Database server port  [default: 5433]
  -U, --user TEXT        Database username  [default: CURRENT_USER]
  -W, --prompt-password  Prompt for password  [default: False]
  -w, --password TEXT    Database password  [default: ]
  -v, --version          Print version and exit
  --vclirc TEXT          Location of .vclirc file  [default: ~/.vclirc]
  --help                 Show this message and exit.

Examples

Use URL to connect:

$ vcli vertica://dbadmin:pass@localhost:5433/mydb

Prompt for password:

$ vcli -h localhost -U dbadmin -W -p 5433 mydb

Don't prompt for password:

$ vcli -h localhost -U dbadmin -w pass -p 5433 mydb

Use VERTICA_URL environment variable:

$ VERTICA_URL=vertica://dbadmin:pass@localhost:5433/mydb vcli

Special Commands

vcli_demo=> \h
+---------------------+--------------------------------------+
| Command             | Description                          |
|---------------------+--------------------------------------|
| \#                  | Refresh auto-completions             |
| \?                  | Show help                            |
| \a                  | Aligned or unaligned                 |
| \c[onnect] [DBNAME] | Connect to a new database            |
| \d [PATTERN]        | List or describe tables              |
| \dS [PATTERN]       | List system tables                   |
| \dT [PATTERN]       | List data types                      |
| \df [PATTERN]       | List functions                       |
| \dj [PATTERN]       | List projections                     |
| \dn [PATTERN]       | List schemas                         |
| \dp [PATTERN]       | List access privileges               |
| \ds [PATTERN]       | List sequences                       |
| \dt [PATTERN]       | List tables                          |
| \dtv [PATTERN]      | List tables and views                |
| \du [PATTERN]       | List users                           |
| \dv [PATTERN]       | List views                           |
| \e [FILE]           | Edit the query with external editor  |
| \h                  | Show help                            |
| \i FILE             | Execute commands from file           |
| \l                  | List databases                       |
| \n[+] [NAME]        | List or execute named queries        |
| \nd [NAME]          | Delete a named query                 |
| \ns NAME QUERY      | Save a named query                   |
| \o [FILE]           | Output to file or stdout             |
| \q                  | Quit vcli                            |
| \refresh            | Refresh auto-completions             |
| \t                  | Toggle header                        |
| \timing             | Toggle timing of commands            |
| \x                  | Toggle expanded output               |
| \z [PATTERN]        | List access privileges (same as \dp) |
+---------------------+--------------------------------------+

Thanks

Thanks to pgcli. Most of the hard work, especially the auto-completion part, were already done well by the pgcli core team. vcli wouldn't be possible if it weren't for them.

vcli's Issues

\dv does not give me column info

Special command \dv VIEW should print the column list of the view, but it didn't.

"tuple index out of range"

Enter two consecutive semicolons at the end gives users a "tuple index out of range" error.

localdev=> select 1;;
tuple index out of range

Command run time is always 0

Same issue with dbcli/pgcli#394.

ImportError: No module named importlib with behave on Python 2.6

Seems like the latest behave (1.2.5) does not work with Python 2.6 well:
https://travis-ci.org/dbcli/vcli/jobs/87407092

@amjith I don't see pgcli had this issue. Did you guys ever run into this problem?

Schema names that are upper case do not work properly with autocompletion

If I attempt to query a schema that is in the format NAME_ID the autocompletion returns the schema name in double quotes, but then fails to return any additional suggestions. If I query a schema name that only contains lower case letters the autocompletion works as expected.

SELECT * FROM "SCHEMA_NAME_000".

Log:

2017-03-02 12:17:07,666 (56424/MainThread) vcli.key_bindings DEBUG - Detected <Tab> key.
2017-03-02 12:17:07,670 (56424/Thread-104) vcli.vcompleter DEBUG - Suggestion type: 'table'
2017-03-02 12:17:07,671 (56424/Thread-104) vcli.vcompleter DEBUG - Suggestion type: 'view'

Refresh auto-completion data in background

If we have a lot of tables and views in the database, the program takes some time on startup. I wonder if we can load the auto-completion data in background so that users don't have to wait.

@amjith I'm still not very familiar with the code structure. Do you think if it is doable?

Set up Travis CI

We can actually install Vertica on Travis. See https://github.com/wvanbergen/vertica/blob/master/.travis.yml.

Need to run query twice to reconnect

If the client doesn't know it's disconnected, you have to run the query twice for it to reconnect:

localdev=> select 1;

localdev=> select 1;
Connection reset. Reconnect (Y/n): y
Reconnected!
Try the command again.
localdev=> select 1;
+------------+
|   ?column? |
|------------|
|          1 |
+------------+
(1 row)
Time: 0.025s
localdev=>

Consider pulling keywords from database

Should the list of keywords be pulled from the database rather than maintaining a static list?

select keyword from v_catalog.keywords order by 1;

I wasn't sure if this was a design decision made based on the most common keywords, or if its an enhancement you would like added.

'Cursor' object has no attribute 'statusmessage' where runnig .sql files from vcli

I tested vcli, and have been using for a couple of hours already and it works great. However, I can't run .sql files where I have long queries. When I run a file I get:
'Cursor' object has no attribute 'statusmessage'

Here's an example of the problem I get:
Showterm: vcli failing to execute files

More humanized error message

vcli prints whatever error message returned by the Vertica server, which is not very pretty:

localdev=> select title from movie order title;
Severity: ERROR, Message: Syntax error at or near "title", Sqlstate: 42601, Position: 31, Routine: base_yyerror, File: /scratch_a/release/30493/vbuild/vertica/Parser/scan.l, Line: 1014, SQL: u'select title from movie order title'

We should make it prettier at least like vsql (the official Vertica command line client):

localdev=> select title from movie order title;
ERROR 4856:  Syntax error at or near "title" at character 31
LINE 1: select title from movie order title;
                                      ^

Auto-completion for file path

Auto-completion for file path in the COPY statement:

COPY table FROM LOCAL '/path/to/file.csv'

explain does not show output

I can't seem to get any output for explain or any of the variations - not sure if this is a known issue or not:

=> explain select 1;
Time: 0.038s
=>
=> explain local verbose select 1;
Time: 0.046s

There also seems to be the same issue with the PROFILE command

Queries with "with" statements don't return output

Hi! I am on the latest version of vcli with vertica 7.2.1 and here's what happens when using "with" statements:

db=> select * from (select 'test' as test) as test;
+--------+
| test   |
|--------|
| test   |
+--------+
(1 row)
Time: 0.162s
db=> with test as (select 'test' as test) select * from test;
Time: 0.328s

Essentially the same query, differently structured, but vcli fails to return anything, while vsql and pgcli don't.

"-1 rows"

The program prints "-1 rows" whenever it should print "0 rows".

localdev=> select 1 where 1 = 2;
+------------+
| ?column?   |
|------------|
+------------+
(-1 rows)

Hide command line password from ps

See dbcli/pgcli#355.

Python 3 support

#45 already fixed a partial Python 3 issues, but to support Python 3 fully, we have to:

• Fix code where the tests are failed on Python 3
• Update .travis.yml to make it test against Python 3.x
• Update setup.py so people on PyPI knows vcli supports Python 3