Interactive shell tab completion for queries

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Interactive shell tab completion for queries

daveparrish
Hello,
I'm writing to ask if it is possible to have table completion for SQL queries in the interactive shell? If not, can this be included as a feature request in the ticket database?
David--
Securely sent with Tutanota. Claim your encrypted mailbox today!
https://tutanota.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Interactive shell tab completion for queries

Roger Binns
On 09/12/16 06:29, [hidden email] wrote:
> I'm writing to ask if it is possible to have table completion for SQL queries in the interactive shell?

The APSW shell (compatible with the SQLite one) has completion and
colouring:

  https://rogerbinns.github.io/apsw/shell.html

Disclaimer: I am the author

You don't need to know anything about python etc to use it.

Implementing completion is also quite fun if anyone wants to discuss it
further.

Roger


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (169 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Interactive shell tab completion for queries

Dominique Devienne
On Fri, Dec 9, 2016 at 11:05 PM, Roger Binns <[hidden email]> wrote:

> On 09/12/16 06:29, [hidden email] wrote:
> > I'm writing to ask if it is possible to have table completion for SQL
> queries in the interactive shell?
>
> The APSW shell (compatible with the SQLite one) has completion and
> colouring:
>
>   https://rogerbinns.github.io/apsw/shell.html
>
> Disclaimer: I am the author
>
> You don't need to know anything about python etc to use it.
>
> Implementing completion is also quite fun if anyone wants to discuss it
> further.
>

I'd love to have completion in the SQLite shell.
How did you implement completion in APSW?
Given that you typically don't know the context (tables) involved
when writing the select clause for example, do you just propose any column
or declared functions?
There even isn't a way to list declared functions, despite wishes for a
pragma for it, so can't even
provide completion in a generic way, especially with dynamically loaded
modules. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Interactive shell tab completion for queries

Roger Binns
On 09/12/16 14:18, Dominique Devienne wrote:
> How did you implement completion in APSW?

[Long answer] at bottom.

You get a callback from the readline or equivalent library when
completion is needed, with it expecting you to provide 0 or more
completions.  You can get the input line, the current token within that
line, and the beginning and end position of the token.

If the line starts with dot, then dot command completion code is called
which is fairly straight forward.

Otherwise SQL completion is used.  There is a cache of database, table,
column, collation, function etc names that is discarded before beginning
input of a new line.  This is because the previously executed line could
have caused changes in any of those.

> Given that you typically don't know the context (tables) involved
> when writing the select clause for example, do you just propose any column
> or declared functions?

I have a branch with an unfinished SQL parser in it, intended to handle
incomplete SQL such as when doing completions.  (Most parser solutions
require complete input, or they error.  What you really want to know is
what kind of tokens or grammar constructs are allowed at the cursor
position.)  Anyway that is not used, yet.[SQLite parser]

I have some code that deals with pragmas since they have a known
construct.  For everything else I just return everything from the cache
that matches the token so far.  Trying to be intelligent without a deep
knowledge parser as described in the previous paragraph isn't really
feasible since virtually anything is allowed anywhere.  Heck try to work
out what isn't allowed immediately after SELECT!  (Allowed includes
database, table, column, function names, some operators, strings,
numbers etc)  Also ponder AS/aliases.

I do make sure that the completions match the case of what was provided
so far: eg SEL has SELECT as completion while sel has select.  Functions
also have the opening parentheses as part of the completion - eg count(
- and closing if no arguments like in random().

It does mean that hitting tab after typing SELECT gives a lot of
choices, but even a few characters is enough to have a short list of
candidates and feel natural.

> There even isn't a way to list declared functions, despite wishes for a
> pragma for it, so can't even
> provide completion in a generic way, especially with dynamically loaded
> modules.

That and lint mode are my regular whines.  I do have a table of builtin
functions in the completion code, but there is no possible way for third
party code to get the function list from SQLite.

[Long answer]
https://github.com/rogerbinns/apsw/blob/master/tools/shell.py#L2550

[SQLite parser] The SQLite parser can't be used either because it
expects complete input, and the rules have code attached that aren't
helpful for this.  I'd dearly love to stay in sync with the SQLite
grammar, but using the canonical grammar file isn't practical.

Roger


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (169 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Interactive shell tab completion for queries

Roger Binns
In reply to this post by Dominique Devienne
On 09/12/16 14:18, Dominique Devienne wrote:
> How did you implement completion in APSW?

[Long answer] at bottom.

You get a callback from the readline or equivalent library when
completion is needed, with it expecting you to provide 0 or more
completions.  You can get the input line, the current token within that
line, and the beginning and end position of the token.

If the line starts with dot, then dot command completion code is called
which is fairly straight forward.

Otherwise SQL completion is used.  There is a cache of database, table,
column, collation, function etc names that is discarded before beginning
input of a new line.  This is because the previously executed line could
have caused changes in any of those.

> Given that you typically don't know the context (tables) involved
> when writing the select clause for example, do you just propose any column
> or declared functions?

I have a branch with an unfinished SQL parser in it, intended to handle
incomplete SQL such as when doing completions.  (Most parser solutions
require complete input, or they error.  What you really want to know is
what kind of tokens or grammar constructs are allowed at the cursor
position.)  Anyway that is not used, yet.[SQLite parser]

I have some code that deals with pragmas since they have a known
construct.  For everything else I just return everything from the cache
that matches the token so far.  Trying to be intelligent without a deep
knowledge parser as described in the previous paragraph isn't really
feasible since virtually anything is allowed anywhere.  Heck try to work
out what isn't allowed immediately after SELECT!  (Allowed includes
database, table, column, function names, some operators, strings,
numbers etc)  Also ponder AS/aliases.

I do make sure that the completions match the case of what was provided
so far: eg SEL has SELECT as completion while sel has select.  Functions
also have the opening parentheses as part of the completion - eg count(
- and closing if no arguments like in random().

It does mean that hitting tab after typing SELECT gives a lot of
choices, but even a few characters is enough to have a short list of
candidates and feel natural.

> There even isn't a way to list declared functions, despite wishes for a
> pragma for it, so can't even
> provide completion in a generic way, especially with dynamically loaded
> modules.

That and lint mode are my regular whines.  I do have a table of builtin
functions in the completion code, but there is no possible way for third
party code to get the function list from SQLite.

[Long answer]
https://github.com/rogerbinns/apsw/blob/master/tools/shell.py#L2550

[SQLite parser] The SQLite parser can't be used either because it
expects complete input, and the rules have code attached that aren't
helpful for this.  I'd dearly love to stay in sync with the SQLite
grammar, but using the canonical grammar file isn't practical.

Roger


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (169 bytes) Download Attachment