Simple SQL code completion in source editor

A common request of many users I have talked to, through nbusers, interviews and surveys, is making it easier to write queries in-place in their source editors.

This feature proposes a very simple solution that can have a huge increase in developer productivity and "flow."

The example shown below uses PHP syntax, but this could be applied just as easily to Java, C/C++ and other languages working with SQL (it doesn't apply so well to Ruby, since they use ActiveRecord, and Tor has already done this for ActiveRecord objects, bless his heart).

• The user is working in their source editor and starts writing a query:

$sql = "SELECT

• They aren't sure of the table and column names, and/or they don't want to bother with all the typing

• The user presses a hot-key or chooses the menu item "View DB Tables"

• A popup menu similar to a code-completion menu is displayed, showing all the tables available for the current connection

$sql = "SELECT  -----------
               | AUTOS     |
               | PEOPLE    |
               | PARTS     |
               | PREORDERS |
               | STORES    |
               | ITEMS     |
                -----------

• The user can navigate with arrow keys or filter results by typing letters, and only matching tables are shown. For example, if they type "P", then only "PEOPLE", "PARTS" and "PREORDERS" are shown.

• If the user hits enter, then the selected table name is pasted at the point of the cursor

• If the user presses the right-arrow key, then the columns for the selected table are shown, e.g.

$sql = "SELECT  -----------
               | AUTOS     |____________
               | PEOPLE    |ID         |
               | PARTS     |FIRSTNAME  |
               | PREORDERS |LASTNAME   |
               | STORES    |ADDR1      |
               | ITEMS     |ADDR2      |
                -----------|CITY       |
                           |STATE      |
                           |ZIP        |
                            -----------

• The user hits <enter> or double-clicks on the column, then the table and column are inserted into the SQL text:

$sql = "SELECT PEOPLE.LASTNAME

• The user continues editing. The last table name is remembered as the default, so if they are ready for the next column, when they use the hot key, the last table used (e.g. "PEOPLE") is pre-selected.

This feature, particularly combined with the Test Query feature, will significantly improve productivity and flow for users working with SQL in their source code.