Add autocompletion capabilities to the kernel

[robertbindar]

As part of the Jupyter Messaging protocol, the Jupyter frontend sends a complete_request message to the MariaDB kernel when the user invokes the code completer in a Jupyter notebook.

This message is handled in the do_complete function from the MariaDBKernel class. In simpler words, whenever the user hits the key shortcut for code autocompletion in a notebook, the MariaDB kernel's do_complete function is called with a number of arguments that help the kernel understand what the user wants to autocomplete.

So the autocompletion infrastructure in the MariaDB kernel is already kindly provided by Jupyter, we only need to send back to Jupyter a list of suggestions based on the arguments that do_complete receives :-).

Ideally we should aim to enable at least database, table and column name completion and also SQL keyword completion.

We should try to not reinvent the wheel here and see if there are some tools around to help us provide this feature with less development effort. The MariaDB command-line client already has some autocompletion capability, we should first consider using that if possible. Also mycli has some nice autocompletion and their Python API seems quite nice, it is a good candidate as well. If no existing tools can help, we can consider implementing this from scratch.

[a97410985]

very rough ideas

background

Although I have experience in writing lexer and parser(have taken a compiler course and get 99 scores), but reinvent the wheel is risky, because it has very little use and probably has a lot of bugs. So what tool are good candidates for SQL autocomplete(The tool can easily integrate to this project is best😊)

the list of candidates

1. mycli Already has two class, CompletionRefresher and SQLCompleter

   1. CompletionRefresher class I'm not sure it can fit in mariadb_kernel. It wrapper an SQLCompleter with a connection to SQL database for executing some SQL command for getting information about database schema(and more...) for the smart suggestion, like suggest table's column name.

   2. SQLCompleter class Its constructor can set smart_completion value, if it is set false, would only provide basic keyword completions such as when typing sel would suggest select, otherwise, it can provide column name suggestion when selecting a column from the table(and more🎉)

      The best is its interface is simple, has get_completions function, gives arguments text and cursor_position then can get a list of candidates. Very match to mariadb_kernel's do_complete 😃. and also this class construction is simple🎉(don't pass weird and hard to create arguments).

      See unit test can better how to use and can get what result. For smart_completion == False : link can see it has very clean interface and result is a list of candidte

      For smart_completion == True : link It needs database information related to table schema, like users table has four columns id, email, first_name, last_name. When text is SELECT users. from users and the cursor is at the end of SELECT users., it would suggest *, email, first_name, id, last_name these words. The database-related information needs to do some database query to get it(maybe mariadb_kernel do it).

2. sql lexer : under the great project pygments has a module sql.py has SqlLexer and MySqlLexer

3. sql parser sqlparse

the choice of the tool

the priority as follows. 1 > 2 > 3

1. use mycli's CompletionRefresher class If didn't 「want another SQL connection to database」 or 「it can't easily integrate with mariadb_kernel」 may choose the second.
2. use mycli's SQLCompleter class I think it can easily integrate with mariadb_kernel. But if need custom autocomplete may rewrite this or use a third tool to implement our own 「SQLCompleter」
3. use pygments SQL lexer and sqlparse

In the end, I would try to use these tools to implement the auto-completion feature.