SQL Entry Code Completion 1.0 - Gerd Wagner

This plugin provides completion of SQL code much the same way modern IDEs do. To invoke code completion hit the ctrl + space shortcut.

Completion works on almost all SQL and DDL constructs:

• Key words, this includes SQL standard keywords as well as those key words delivered by the JDBC driver.

• Tables

• Columns

• Views

• Stored procedures, completion generates the complete JDBC call syntax including templates for parameters.

• Catalogs

• Schemas

Beyond this the plugin offers so called completion functions that can be used to generate SQL Joins. To explain completion functions we have look at the tables BEST, BEST_LAGPL and LAGPL in figure below. To generate the Join from BEST to LAGPL you'd write the following expression:

#i,BEST,BEST_LAGPL,LAGPL,

If the cursor is positioned at the end of this expression and Code completion is called by the Ctrl + Space shortcut the plugin generates the following code for you:

INNER JOIN BEST_LAGPL ON BEST.BESTID = BEST_LAGPL.BESTID

INNER JOIN LAGPL ON LAGPL.LAGPLID = BEST_LAGPL.LAGPLID

To preform this the plugin interprets the expression as follows: First the function #i which stands for 'INNER JOIN' is picked. Then the comma separated list of arguments is passed to the function. The function analyzes the foreign key relations between the tables in the list to generate the Join clause. If no foreign keys are found the clause is still generated just the columns are left blank to be provided later by the user. If there is more then one foreign key relation between two or more of the tables the possible Join clauses are shown in the completion pop up for the user to choose.

Besides #i the commands #l for Left Join, #r for Right Join and #j for Join exist. All of these commands expect a comma separated list of tables. Please note the comma at the end of the list. The command #j generates a Left Join if the foreign key field allows NULL values else an Inner Join.