The Structured Query Language (SQL) is a standard which specifies statements that allow a user to interact with a relational database. Vim includes features for navigation, indentation and syntax highlighting.
1. SQL Dialects
- sql-dialects sql-types
- sybase TSQL Transact-SQL
- oracle plsql sqlj
- mysql postgress psql
For the people that work with many different databases, it is nice to be able to flip between the various vendors rules (indent, syntax) on a per buffer basis, at any time. The ftplugin/sql.vim file defines this function:
Executing this function without any parameters will set the indent and syntax scripts back to their defaults.
1.2 SQL Dialect Default
As mentioned earlier, the default syntax rules for Vim is based on Oracle (PL/SQL). You can override this default by placing one of the following in your vimrc:
let g:sql_type_default = 'sqlanywhere'
2. SQL Completion Tutorial
First, create a new buffer:
To take you through the various lists, simply enter insert mode, hit:
<C-C>s (show SQL statements)
At this point, you can page down through the list until you find “select”.
If you are familiar with the item you are looking for, for example you know
the statement begins with the letter “s”. You can type ahead (without the
quotes) “se” then press:
Assuming “select” is highlighted in the popup list press <Enter> to choose
the entry. Now type:
fr<C-C>a (show all syntax items)
choose “from” from the popup list.
When writing stored procedures using the “type” list is useful. It contains
a list of all the database supported types. This may or may not be true
depending on the syntax file you are using. The SQL Anywhere syntax file
(sqlanywhere.vim) has support for this: >
DECLARE customer_id <C-C>T <-- Choose a type from the list
To take advantage of the dynamic features you must first install the
dbext.vim plugin (http://vim.sourceforge.net/script.php?script_id=356). It
also comes with a tutorial.
Assuming you have followed the dbext-tutorial you can press <C-C>t to
display a list of tables. There is a delay while dbext is creating the table
list. After the list is displayed press <C-W>. This will remove both the
popup window and the table name already chosen when the list became active. >
2.1 Table Completion:
Press <C-C>t to display a list of tables from within the database you
have connected via the dbext plugin.
2.2 Column Completion:
The SQL completion plugin can also display a list of columns for particular
tables. The column completion is trigger via <C-C>c.
Example of using column completion:
- Press <C-C>t again to display the list of tables.
- When the list is displayed in the completion window, press <Right>, this will replace the list of tables, with a list of columns for the table highlighted (after the same short delay).
- If you press <Left>, this will again replace the column list with the list of tables. This allows you to drill into tables and column lists very quickly.
- Press <Right> again while the same table is highlighted. You will notice there is no delay since the column list has been cached. If you change the schema of a cached table you can press <C-C>R, which clears the SQL completion cache.
Lets look how we can build a SQL statement dynamically. A select statement requires a list of columns. There are two ways to build a column list using the SQL completion plugin.
One column at a time:
- After typing SELECT press <C-C>t to display a list of tables.
- Choose a table from the list.
- Press <Right> to display a list of columns.
- Choose the column from the list and press enter.
- Enter a “,” and press <C-C>c. Generating a column list generally requires having the cursor on a table name. The plugin uses this name to determine what table to retrieve the column list. In this step, since we are pressing <C-C>c without the cursor on a table name the column list displayed will be for the previous table. Choose a different column and move on.
- Repeat step 5 as often as necessary.
All columns for a table:
- After typing SELECT press <C-C>t to display a list of tables.
- Highlight the table you need the column list for.
- Press <Enter> to choose the table from the list.
- Press <C-C>l to request a comma separated list of all columns for this table.
- Based on the table name chosen in step 3, the plugin attempts to decide on a reasonable table alias. You are then prompted to either accept of change the alias. Press OK.
- The table name is replaced with the column list of the table is replaced with the comma separate list of columns with the alias prepended to each of the columns.
- Step 3 and 4 can be replaced by pressing <C-C>L, which has a <C-Y> embedded in the map to choose the currently highlighted table in the list.
There is a special provision when writing select statements. Consider the
from customer c,
department as dp,
In INSERT mode after typing the final “c.” which is an alias for the “customer” table, you can press either <C-C>c or <C-X><C-O>. This will popup a list of columns for the customer table. It does this by looking back
to the beginning of the select statement and finding a list of the tables specified in the FROM clause. In this case it notes that in the string “customer c”, “c” is an alias for the customer table. The optional “AS” keyword is also supported, “customer AS c”. >
3.0 SQL Maps
The default SQL maps have been described in other sections of this document in greater detail. Here is a list of the maps with a brief description of each.
These are maps which use populate the completion list using Vim’s syntax highlighting rules.
- <C-C>a – Displays all SQL syntax items.
- <C-C>k – Displays all SQL syntax items defined as ‘sqlKeyword’.
- <C-C>f – Displays all SQL syntax items defined as ‘sqlFunction.
- <C-C>o – Displays all SQL syntax items defined as ‘sqlOption’.
- <C-C>T – Displays all SQL syntax items defined as ‘sqlType’.
- <C-C>s – Displays all SQL syntax items defined as ‘sqlStatement’.
These are maps which use populate the completion list using the dbext.vim plugin.
- <C-C>t - Displays a list of tables.
- <C-C>p – Displays a list of procedures.
- <C-C>v – Displays a list of views.
- <C-C>c – Displays a list of columns for a specific table.
- <C-C>l – Displays a comma separated list of columns for a specific table.
- <C-C>L – Displays a comma separated list of columns for a specific table. This should only be used when the completion window is active.
- <Right> – Displays a list of columns for the table currently highlighted in the completion window. <Right> is not recognized on most Unix systems, so this maps is only created on the Windows platform. If you would like the same feature on Unix, choose a different key and make the same map in your vimrc. >
- <Left> – Displays the list of tables.
<Left> is not recognized on most Unix systems, so this maps is only created on the Windows platform. If you would like the same feature on Unix, choose a different key and make the same map in your vimrc.
- <C-C>R – This maps removes all cached items and forces the SQL completion to regenerate the list of items.