---

UI/Functional Spec for Testing a Query in a Code Editor

Problem Description

A common scenario for developers building a database application is that they are writing a query in SQL, and want to test to see whether the query works.

Currently there are two ways to do this:

• Copy the query into a query tool and execute it.
• Run the application and see if it does what you expect

Copy the query into a query tool

This approach works, but there are some things that make it a pain. First of all, it interrupts the "flow" - you have to keep going back and forth between your query tool and your ediort.

Your flow is interrupted even more because you are constantly having to translate

First of all, the formatting required by a SQL tool is different from what is required by a code editor.

For example, the query

SELECT person.id, person.lastname, person.firstname,
  person.city, person.state, person.zip
  FROM person
  WHERE person.lastname LIKE '%ouvering%'

needs to be represented in PHP as

$sql = "SELECT person.id, person.lastname, person.firstname, " .
  "person.city, person.state, person.zip " .
  "FROM person " .
  "WHERE person.lastname LIKE '%ouvering%'";

There is also the problem of substitution variables. The more likely way you would see the above query string in PHP would be either using a PHP substitution variable, or using a MYSQL prepared statement:

Substitution variable

$sql = "SELECT person.id, person.lastname, person.firstname, " .
  "person.city, person.state, person.zip " .
  "FROM person " .
  "WHERE person.lastname LIKE '%" . 
  mysql_real_escape_string($lastname) . "%'";

Prepared statement

$stmt =  $mysqli->stmt_init();
$sql = SELECT person.id, person.lastname, person.firstname, " .
  "person.city, person.state, person.zip " .
  "FROM person " .
  "WHERE person.lastname LIKE ?";

if ($stmt->prepare($sql)) {

    $stmt->bind_param("s", $lastname );

    /* execute query */
    $stmt->execute();

This means that when you copy and paste, you have to put in values for the substitution variables, and then when you paste back into the editor, you have to re-convert. A number of folks have complained about this on the user list, and Wade Chandler has even built a plugin that converts back and forth between Java and text.

Wade's plugin solves part of the problem, but it doesn't really handle the issue of substitution variables.

Proposed Solution

This feature significantly improves the flow and productivity for developers (PHP initially) by letting them quickly run an action to test a query. It would be implemented as a plugin module that works with the PHP editor.

Use Case: Associate a Connection with the Current Project

The editor toolbar will show a new item that displays the current connection, much like what is available in the SQL editor today. Initially it is empty, but the user can select it, and it is a drop-down of all available connections.

This allows the user to associate a particular connection with the current file, which is useful for this operation as well as other potential database-related operations.

Use Case: Test Query Written in PHP

• User composes a SQL query in PHP
• User selects the query string, including all newlines and substitution variables
• User uses a keyboard shortcut or right-clicks and chooses "Test SQL"
• The user is presented with a dialog to provide values for all substitution variables/parameters
• The currently associated connection is shown, and the user can pick another one if they want

-----------------------------------------------------------
| Connection: _____________________________V              |
|                                                         |
| Please enter values for query parameters                |
|                                                         |
| param1 : ____________                                   |
| param2 : ____________                                   |
|                                                         |
|                                   [Cancel] [Test Query] |
----------------------------------------------------------

• The user presses "Test Query"
• A new SQL Editor is brought up with the query text pasted in and the query is executed, showing the results, or any errors that may have occurred
• The user fixes, re-tests, and iterates this way until they are happy with the result
• The user closes the query window or otherwise returns focus to the original file
• If the query was changed, the user is prompted if they want to replace the query with the modified one

------------------------------------------------------
|                                                    |
| The query was modified, do you want to update the  |
| selected query string?                             |
|                                                    |
|                                        [No] [Yes]  |
------------------------------------------------------

• If the user chooses "Yes", then the selected string is replace with the new one, properly formatted. Substitution variables or prepared statement parameters are retained unless the system is unable to determine where they belong in the new query string.