basic integrated with mycli

[a97410985]

For issue #5

[robertbindar]

Hey @a97410985!

Let's work together here and build a list of minimal requirements we'd like to see in the feature. I've thought of some items, here's the list below, please feel free to add more.

Autcomoplete

1. SQL KEYWORDS and FUNCTIONS completion
2. Completion of database names in USE statements
3. Completion of database names in constructs like database_to_autocomplete.table_name
4. Completion of table_names in constructs like db.table_name_to_autocomplete
5. Completion of column names after WHERE clause
6. Completion of column names in SELECT c1, c2 from table;
   • If the user typed SELECT from table; and then returns with the cursor after SELECT, user should see the list of columns from table.
7. Completion of column names in INSERT INTO table VALUES(
   • User should see the columns from table once user types VALUES(, but ideally the column names here should be only for helping the user, autocomplete shouldn't complete (it doesn't make sense, user needs to type values in there).
8. Completion of column names in INSERT INTO table (col_to_be_completed1, col_to_be_completed2) VALUES (1, 2)
9. Resolving aliases and completion of column names in constructs like alias.column_to_autocomplete
10. Completion of SHOW variant_to_autocomplete statements.
11. Completion of username@hostname in constructs like ALTER USER user_to_be_completed ..
12. Completion of global and session variables in constructs such as SELECT @@variable_to_autocomplete or SELECT @@session.variable_to_autocomplete or SELECT @@global.variable_to_autocomplete
13. Ability to disable autocompletion with ClientConfig option

[a97410985]

Introspection

what is introspection

when the cursor is on the word and press SHIFT+TAB. It would pop up a window containing information about its type, arguments info, and document. Like image below

introspection about mariadb_kernel may implement

According to a different type of word show different info. like below table: type	show Infos
database	it contains which tables, and table’s schema
table	show its schema, and partial table rows
column	show the data type it stores, and some data in this column
function	show its documentation. for example, MAX() would show “The MAX() function returns the maximum value in a set of values”.
user	show user list.

for more details example list below(the bold word is the word be introspected):

1. a. select * from product would show the product table’s schema and some row

   schema : column name	datatype	constraint
   id	int(11)	primary key
   name	varchar(30)
   amount	int(11)
   price	int(11)

   real data: id	name	amount	price
   1	apple	10	30
   2	orange	20	40
   ...

2. select name from product would show the name’s data type and data in the name column. Like below

   datatype: varchar(30) real data: apple、orange...

3. ALTER USER user@localhost password expire would show the output of SHOW CREATE USER user@localhost (maybe more smarter or just output show create user)

[robertbindar]

Great ideas for introspection @a97410985! Here's what I propose as main components for this project.

SQLFetch

• uses a MariaDBClient passed during construction to talk to the server
• responsible for all SQL queries that this project will send to the server
• from this point on, all hardcoded SQL queries that the kernel will need to run will be in this class (includes current queries like the one in kernel.py: num_connected_clients() which will be refactored into this class)
• exposes functions for all hardcoded queries this project and the kernel need
• your model will be mycli.SQLExecute except it will become much simpler

SQLAnalyze

• it's basically mycli's parseutils.py + completion_engine.py
• this will be the most complicated part of the code, we shouldn't shy away from extensive code comments
• we should attempt a refactoring of the original code and divide it into more logically independent functions

Autocompleter

• exposes function: get_suggestions(code, cursor_pos) -> [list of suggestions
• exposes function: refresh() which should do what mycli completion_refresher does now, but I would say let's make it synchronous for now (i.e. get_suggestions calls .refresh() before returning, it should be easy to make it async once project is up and running)
• uses SQLFetch to get the relevant data whilst refreshing
• uses SQLAnalyze to get query insight and build the suggestions list

Introspector

• exposes function: inspect(code, cursor_pos) -> introspection_text or None
• exposes function: refresh() - similar to Autocompleter.refresh()
• Uses SQLFetch to get data needed to build introspection results
• Uses SQLAnalyze to get query insight and decide what result to return based on the type of the introspected element

This is what I can think of in terms of design for this project, please feel free to ask questions about anything above and to propose better solutions/alternatives .

[a97410985]

What feature list in minimal requirements that already on mycli

• [x] SQL KEYWORDS and FUNCTIONS completion

• [x] Completion of database names in USE statements

  

• [ ] Completion of database names in constructs like database_to_autocomplete.table_name

  

• [x] Completion of table_names in constructs like db.table_name_to_autocomplete

  

• [x] Completion of column names after WHERE clause

  

• [x] Completion of column names in SELECT c1, c2 from table;

  • If the user typed SELECT from table; and then returns with the cursor after SELECT, user should see the list of columns from table.

    

• [ ] Completion of column names in INSERT INTO table VALUES(

  • User should see the columns from table once user types VALUES(, but ideally the column names here should be only for helping the user, autocomplete shouldn't complete (it doesn't make sense, user needs to type values in there).

• [x] Completion of column names in INSERT INTO table (col_to_be_completed1, col_to_be_completed2) VALUES (1, 2)

  

• [x] Resolving aliases and completion of column names in constructs like alias.column_to_autocomplete

• [x] Completion of SHOW variant_to_autocomplete statements.

  

• [x] Completion of username@hostname in constructs like ALTER USER user_to_be_completed ..

  

• [ ] Completion of global and session variables in constructs such as SELECT @@variable_to_autocomplete or SELECT @@session.variable_to_autocomplete or SELECT @@global.variable_to_autocomplete