MySQL Bugs: #34014: Feature Patch: context sensitive completion in mysql client

Bug #34014	Feature Patch: context sensitive completion in mysql client
Submitted:	23 Jan 2008 17:26	Modified:	18 Jan 2012 15:19
Reporter:	Shannon Weyrick (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S4 (Feature request)
Version:	5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
This patch adds some context sensitivity to the tab completion in the mysql command line client. It does some simple parsing of the current input to try to determine the tables used in the current query, and if available only completes on fields in those tables. It doesn't work for all queries, but falls back to the current "list all" method if it can't figure out context.

This patch was made to scratch a particular itch, namely:

---
SELECT foo FROM table1, table2 WHERE <tab><tab>

Display all 4189 possibilities? (y or n)
---

With this patch, it would list only the fields from table1 and table2.

I believe this patch applies to both 5.1 and 6.0 trees (or can be made to do so easily).

How to repeat:
N/A

mysql client context sensitive table completion

Attachment: mysql-client-tc.patch (application/octet-stream, text), 8.36 KiB.

Many thanks for writing a bug report.

I've discovered two more cases that would be nice for this patch to handle:

1) If you've already typed your query (including WHERE) and use the line editing to go back and edit the list of tables, it still uses the current table context and only completes on field names when you'd expect it to complete on table names

2) The ability to complete using full "table.field" notation, even with context. This is necessary when two tables contain the same field name and you need to specify them in the where, e.g. during a join. Currently it would complete on the field name only, and you would have to type in the different table names manually.

I think it's probably not hard to implement either of these, and I'll attach another patch if I get the time.

Hi Shannon -  Since you're already a CLA signatory, I thought you may be interested in knowing that MySQL is now using a new contributor agreement (i.e. the "Sun Contributor Agreement"). 

Once you send your signed SCA as described here: 
  http://forge.mysql.com/wiki/Sun_Contributor_Agreement , 
your signatory status will be reflected, along with other SCA signatories, for the MySQL project: 
   https://sca.dev.java.net/CA_signatories.htm 

This will enable and faciliate the review and interaction regarding your contributions to MySQL (as well as to any other Sun-managed Open-Source project).

I've submitted the SCA.

Something I'd like to see as a first step for this patch is to isolate the completion code in a way that makes it easy for us to write tests that don't rely on readline or connecting to a MySQL server. It would be great to have more clever completion code, but without a framework for testing, I'm afraid that we will eventually out-clever ourselves and inadvertently release something that causes people a lot of pain.

I've started a bzr branch for this and ported it to 5.5.
https://code.launchpad.net/~weyrick/mysql-server/34014-cs-complete

related work: http://pgcli.com/mysql-cli-kickstarter.html