*sql.txt* For Vim version 7.1. Last change: Wed Apr 26 2006 3:05:33 PM by David Fishburn This is a filetype plugin to work with SQL files. The Structured Query Language (SQL) is a standard which specifies statementsthat allow a user to interact with a relational database. Vim includesfeatures for navigation, indentation and syntax highlighting. 1. Navigation |sql-navigation| 1.1 Matchit |sql-matchit| 1.2 Text Object Motions |sql-object-motions| 1.3 Predefined Object Motions |sql-predefined-objects| 1.4 Macros |sql-macros|2. SQL Dialects |sql-dialects| 2.1 SQLSetType |SQLSetType| 2.2 SQL Dialect Default |sql-type-default|3. Adding new SQL Dialects |sql-adding-dialects|4. OMNI SQL Completion |sql-completion| 4.1 Static mode |sql-completion-static| 4.2 Dynamic mode |sql-completion-dynamic| 4.3 Tutorial |sql-completion-tutorial| 4.3.1 Complete Tables |sql-completion-tables| 4.3.2 Complete Columns |sql-completion-columns| 4.3.3 Complete Procedures |sql-completion-procedures| 4.3.4 Complete Views |sql-completion-views| 4.4 Completion Customization |sql-completion-customization| 4.5 SQL Maps |sql-completion-maps| 4.6 Using with other filetypes |sql-completion-filetypes| ==============================================================================1. Navigation *sql-navigation* The SQL ftplugin provides a number of options to assist with filenavigation. 1.1 Matchit *sql-matchit*-----------The matchit plugin (http://www.vim.org/scripts/script.php?script_id=39)provides many additional features and can be customized for differentlanguages. The matchit plugin is configured by defining a localbuffer variable, b:match_words. Pressing the % key while on variouskeywords will move the cursor to its match. For example, if the cursoris on an "if", pressing % will cycle between the "else", "elseif" and"end if" keywords. The following keywords are supported: > if elseif | elsif else [if] end if [while condition] loop leave break continue exit end loop for leave break continue exit end loop do statements doend case when when default end case merge when not matched when matched create[ or replace] procedure|function|event returns 1.2 Text Object Motions *sql-object-motions*-----------------------Vim has a number of predefined keys for working with text |object-motions|.This filetype plugin attempts to translate these keys to maps which make sensefor the SQL language. The following |Normal| mode and |Visual| mode maps exist (when you edit a SQLfile): > ]] move forward to the next 'begin' [[ move backwards to the previous 'begin' ][ move forward to the next 'end' [] move backwards to the previous 'end' 1.3 Predefined Object Motions *sql-predefined-objects*-----------------------------Most relational databases support various standard features, tables, indices,triggers and stored procedures. Each vendor also has a variety of proprietaryobjects. The next set of maps have been created to help move between theseobjects. Depends on which database vendor you are using, the list of objectsmust be configurable. The filetype plugin attempts to define many of thestandard objects, plus many additional ones. In order to make this asflexible as possible, you can override the list of objects from within your|vimrc| with the following: > let g:ftplugin_sql_objects = 'function,procedure,event,table,trigger' . \ ',schema,service,publication,database,datatype,domain' . \ ',index,subscription,synchronization,view,variable' The following |Normal| mode and |Visual| mode maps have been created which usethe above list: > ]} move forward to the next 'create <object name>' [{ move backward to the previous 'create <object name>' Repeatedly pressing ]} will cycle through each of these create statements: > create table t1 ( ... ); create procedure p1 begin ... end; create index i1 on t1 (c1); The default setting for g:ftplugin_sql_objects is: > let g:ftplugin_sql_objects = 'function,procedure,event,' . \ '\\(existing\\\\|global\\s\\+temporary\\s\\+\\)\\\{,1}' . \ 'table,trigger' . \ ',schema,service,publication,database,datatype,domain' . \ ',index,subscription,synchronization,view,variable' The above will also handle these cases: > create table t1 ( ... ); create existing table t2 ( ... ); create global temporary table t3 ( ... ); By default, the ftplugin only searches for CREATE statements. You can alsooverride this via your |vimrc| with the following: > let g:ftplugin_sql_statements = 'create,alter' The filetype plugin defines three types of comments: > 1. -- 2. // 3. /* * */ The following |Normal| mode and |Visual| mode maps have been created to workwith comments: > ]" move forward to the beginning of a comment [" move forward to the end of a comment 1.4 Macros *sql-macros*----------Vim's feature to find macro definitions, |'define'|, is supported using thisregular expression: > \c\<\(VARIABLE\|DECLARE\|IN\|OUT\|INOUT\)\> This addresses the following code: > CREATE VARIABLE myVar1 INTEGER; CREATE PROCEDURE sp_test( IN myVar2 INTEGER, OUT myVar3 CHAR(30), INOUT myVar4 NUMERIC(20,0) ) BEGIN DECLARE myVar5 INTEGER; SELECT c1, c2, c3 INTO myVar2, myVar3, myVar4 FROM T1 WHERE c4 = myVar1; END; Place your cursor on "myVar1" on this line: > WHERE c4 = myVar1; ^ Press any of the following keys: > [d [D [CTRL-D ==============================================================================2. SQL Dialects *sql-dialects* *sql-types* *sybase* *TSQL* *Transact-SQL* *sqlanywhere* *oracle* *plsql* *sqlj* *sqlserver* *mysql* *postgres* *psql* *informix* All relational databases support SQL. There is a portion of SQL that isportable across vendors (ex. CREATE TABLE, CREATE INDEX), but there is agreat deal of vendor specific extensions to SQL. Oracle supports the"CREATE OR REPLACE" syntax, column defaults specified in the CREATE TABLEstatement and the procedural language (for stored procedures and triggers). The default Vim distribution ships with syntax highlighting based on Oracle'sPL/SQL. The default SQL indent script works for Oracle and SQL Anywhere.The default filetype plugin works for all vendors and should remain vendorneutral, but extendable. Vim currently has support for a variety of different vendors, currently thisis via syntax scripts. Unfortunately, to flip between different syntax rulesyou must either create: 1. New filetypes 2. Custom autocmds 3. Manual steps / commands The majority of people work with only one vendor's database product, it wouldbe nice to specify a default in your |vimrc|. 2.1 SQLSetType *sqlsettype* *SQLSetType*--------------For the people that work with many different databases, it would be nice to beable to flip between the various vendors rules (indent, syntax) on a perbuffer basis, at any time. The ftplugin/sql.vim file defines this function: > SQLSetType Executing this function without any parameters will set the indent and syntaxscripts back to their defaults, see |sql-type-default|. If you have turnedoff Vi's compatibility mode, |'compatible'|, you can use the <Tab> key tocomplete the optional parameter. After typing the function name and a space, you can use the completion tosupply a parameter. The function takes the name of the Vim script you want tosource. Using the |cmdline-completion| feature, the SQLSetType function willsearch the |'runtimepath'| for all Vim scripts with a name containing 'sql'.This takes the guess work out of the spelling of the names. The following areexamples: > :SQLSetType :SQLSetType sqloracle :SQLSetType sqlanywhere :SQLSetType sqlinformix :SQLSetType mysql The easiest approach is to the use <Tab> character which will first completethe command name (SQLSetType), after a space and another <Tab>, display a listof available Vim script names: > :SQL<Tab><space><Tab> 2.2 SQL Dialect Default *sql-type-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 inyour |vimrc|: > let g:sql_type_default = 'sqlanywhere' let g:sql_type_default = 'sqlinformix' let g:sql_type_default = 'mysql' If you added the following to your |vimrc|: > let g:sql_type_default = 'sqlinformix' The next time edit a SQL file the following scripts will be automaticallyloaded by Vim: > ftplugin/sql.vim syntax/sqlinformix.vim indent/sql.vim>Notice indent/sqlinformix.sql was not loaded. There is no indent filefor Informix, Vim loads the default files if the specified files does notexist. ==============================================================================3. Adding new SQL Dialects *sql-adding-dialects* If you begin working with a SQL dialect which does not have any customizationsavailable with the default Vim distribution you can check http://www.vim.orgto see if any customization currently exist. If not, you can begin by cloningan existing script. Read |filetype-plugins| for more details. To help identify these scripts, try to create the files with a "sql" prefix.If you decide you wish to create customizations for the SQLite database, youcan create any of the following: > Unix ~/.vim/syntax/sqlite.vim ~/.vim/indent/sqlite.vim Windows $VIM/vimfiles/syntax/sqlite.vim $VIM/vimfiles/indent/sqlite.vim No changes are necessary to the SQLSetType function. It will automaticallypickup the new SQL files and load them when you issue the SQLSetType command. ==============================================================================4. OMNI SQL Completion *sql-completion* *omni-sql-completion* Vim 7 includes a code completion interface and functions which allows plugindevelopers to build in code completion for any language. Vim 7 includescode completion for the SQL language. There are two modes to the SQL completion plugin, static and dynamic. Thestatic mode populates the popups with the data generated from current syntaxhighlight rules. The dynamic mode populates the popups with data retrieveddirectly from a database. This includes, table lists, column lists,procedures names and more. 4.1 Static Mode *sql-completion-static*---------------The static popups created contain items defined by the active syntax ruleswhile editing a file with a filetype of SQL. The plugin defines (by default)various maps to help the user refine the list of items to be displayed.The defaults static maps are: > imap <buffer> <C-C>a <C-\><C-O>:call sqlcomplete#Map('syntax')<CR><C-X><C-O> imap <buffer> <C-C>k <C-\><C-O>:call sqlcomplete#Map('sqlKeyword')<CR><C-X><C-O> imap <buffer> <C-C>f <C-\><C-O>:call sqlcomplete#Map('sqlFunction')<CR><C-X><C-O> imap <buffer> <C-C>o <C-\><C-O>:call sqlcomplete#Map('sqlOption')<CR><C-X><C-O> imap <buffer> <C-C>T <C-\><C-O>:call sqlcomplete#Map('sqlType')<CR><C-X><C-O> imap <buffer> <C-C>s <C-\><C-O>:call sqlcomplete#Map('sqlStatement')<CR><C-X><C-O> The static maps (which are based on the syntax highlight groups) follow thisformat: > imap <buffer> <C-C>k <C-\><C-O>:call sqlcomplete#Map('sqlKeyword')<CR><C-X><C-O> This command breaks down as: > imap - Create an insert map <buffer> - Only for this buffer <C-C>k - Your choice of key map <C-\><C-O> - Execute one command, return to Insert mode :call sqlcomplete#Map( - Allows the SQL completion plugin to perform some housekeeping functions to allow it to be used in conjunction with other completion plugins. Indicate which item you want the SQL completion plugin to complete. In this case we are asking the plugin to display items from the syntax highlight group 'sqlKeyword'. You can view a list of highlight group names to choose from by executing the :syntax list command while editing a SQL file. 'sqlKeyword' - Display the items for the sqlKeyword highlight group )<CR> - Execute the :let command <C-X><C-O> - Trigger the standard omni completion key stroke. Passing in 'sqlKeyword' instructs the SQL completion plugin to populate the popup with items from the sqlKeyword highlight group. The plugin will also cache this result until Vim is restarted. The syntax list is retrieved using the syntaxcomplete plugin. Using the 'syntax' keyword is a special case. This instructs thesyntaxcomplete plugin to retrieve all syntax items. So this will effectivelywork for any of Vim's SQL syntax files. At the time of writing this includes10 different syntax files for the different dialects of SQL (see section 3above, |sql-dialects|). Here are some examples of the entries which are pulled from the syntax files: > All - Contains the contents of all syntax highlight groups Statements - Select, Insert, Update, Delete, Create, Alter, ... Functions - Min, Max, Trim, Round, Date, ... Keywords - Index, Database, Having, Group, With Options - Isolation_level, On_error, Qualify_owners, Fire_triggers, ... Types - Integer, Char, Varchar, Date, DateTime, Timestamp, ... 4.2 Dynamic Mode *sql-completion-dynamic*----------------Dynamic mode populates the popups with data directly from a database. Inorder for the dynamic feature to be enabled you must have the dbext.vimplugin installed, (http://vim.sourceforge.net/script.php?script_id=356). Dynamic mode is used by several features of the SQL completion plugin.After installing the dbext plugin see the dbext-tutorial for additionalconfiguration and usage. The dbext plugin allows the SQL completion pluginto display a list of tables, procedures, views and columns. > Table List - All tables for all schema owners Procedure List - All stored procedures for all schema owners View List - All stored procedures for all schema owners Column List - For the selected table, the columns that are part of the table To enable the popup, while in INSERT mode, use the following key combinationsfor each group (where <C-C> means hold the CTRL key down while pressingthe space bar): Table List - <C-C>t - <C-X><C-O> (the default map assumes tables) Stored Procedure List - <C-C>p View List - <C-C>v Column List - <C-C>c Windows platform only - When viewing a popup window displaying the list of tables, you can press <C-Right>, this will replace the table currently highlighted with the column list for that table. - When viewing a popup window displaying the list of columns, you can press <C-Left>, this will replace the column list with the list of tables. - This allows you to quickly drill down into a table to view it's columns and back again. The SQL completion plugin caches various lists that are displayed inthe popup window. This makes the re-displaying of these lists veryfast. If new tables or columns are added to the database it may becomenecessary to clear the plugins cache. The default map for this is: > imap <buffer> <C-C>R <C-\><C-O>:call sqlcomplete#Map('ResetCache')<CR><C-X><C-O> 4.3 SQL Tutorial *sql-completion-tutorial*---------------- This tutorial is designed to take you through the common features of the SQLcompletion plugin so that: > a) You gain familiarity with the plugin b) You are introduced to some of the more common features c) Show how to customize it to your preferences d) Demonstrate "Best of Use" of the plugin (easiest way to configure). First, create a new buffer: > :e tutorial.sql Static features---------------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 knowthe statement begins with the letter "s". You can type ahead (without thequotes) "se" then press: <C-Space>tAssuming "select" is highlighted in the popup list press <Enter> to choosethe 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 containsa list of all the database supported types. This may or may not be truedepending on the syntax file you are using. The SQL Anywhere syntax file(sqlanywhere.vim) has support for this: > BEGIN DECLARE customer_id <C-C>T <-- Choose a type from the list Dynamic features----------------To take advantage of the dynamic features you must first install thedbext.vim plugin (http://vim.sourceforge.net/script.php?script_id=356). Italso comes with a tutorial. From the SQL completion plugin's perspective,the main feature dbext provides is a connection to a database. dbextconnection profiles are the most efficient mechanism to define connectioninformation. Once connections have been setup, the SQL completion pluginuses the features of dbext in the background to populate the popups. What follows assumes dbext.vim has been correctly configured, a simple testis to run the command, :DBListTable. If a list of tables is shown, you knowdbext.vim is working as expected. If not, please consult the dbext.txtdocumentation. Assuming you have followed the dbext-tutorial you can press <C-C>t todisplay a list of tables. There is a delay while dbext is creating the tablelist. After the list is displayed press <C-W>. This will remove both thepopup window and the table name already chosen when the list became active. > 4.3.1 Table Completion: *sql-completion-tables* Press <C-C>t to display a list of tables from within the database youhave connected via the dbext plugin.NOTE: All of the SQL completion popups support typing a prefix before pressingthe key map. This will limit the contents of the popup window to just itemsbeginning with those characters. > 4.3.2 Column Completion: *sql-completion-columns* The SQL completion plugin can also display a list of columns for particulartables. The column completion is trigger via <C-C>c. Lines 1–500 of 763. Load more
*sql.txt* For Vim version 7.1. Last change: Wed Apr 26 2006 3:05:33 PM by David Fishburn This is a filetype plugin to work with SQL files. 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. Navigation |sql-navigation| 1.1 Matchit |sql-matchit| 1.2 Text Object Motions |sql-object-motions| 1.3 Predefined Object Motions |sql-predefined-objects| 1.4 Macros |sql-macros| 2. SQL Dialects |sql-dialects| 2.1 SQLSetType |SQLSetType| 2.2 SQL Dialect Default |sql-type-default| 3. Adding new SQL Dialects |sql-adding-dialects| 4. OMNI SQL Completion |sql-completion| 4.1 Static mode |sql-completion-static| 4.2 Dynamic mode |sql-completion-dynamic| 4.3 Tutorial |sql-completion-tutorial| 4.3.1 Complete Tables |sql-completion-tables| 4.3.2 Complete Columns |sql-completion-columns| 4.3.3 Complete Procedures |sql-completion-procedures| 4.3.4 Complete Views |sql-completion-views| 4.4 Completion Customization |sql-completion-customization| 4.5 SQL Maps |sql-completion-maps| 4.6 Using with other filetypes |sql-completion-filetypes| ============================================================================== 1. Navigation *sql-navigation* The SQL ftplugin provides a number of options to assist with file navigation. 1.1 Matchit *sql-matchit* ----------- The matchit plugin (http://www.vim.org/scripts/script.php?script_id=39) provides many additional features and can be customized for different languages. The matchit plugin is configured by defining a local buffer variable, b:match_words. Pressing the % key while on various keywords will move the cursor to its match. For example, if the cursor is on an "if", pressing % will cycle between the "else", "elseif" and "end if" keywords. The following keywords are supported: > if elseif | elsif else [if] end if [while condition] loop leave break continue exit end loop for leave break continue exit end loop do statements doend case when when default end case merge when not matched when matched create[ or replace] procedure|function|event returns 1.2 Text Object Motions *sql-object-motions* ----------------------- Vim has a number of predefined keys for working with text |object-motions|. This filetype plugin attempts to translate these keys to maps which make sense for the SQL language. The following |Normal| mode and |Visual| mode maps exist (when you edit a SQL file): > ]] move forward to the next 'begin' [[ move backwards to the previous 'begin' ][ move forward to the next 'end' [] move backwards to the previous 'end' 1.3 Predefined Object Motions *sql-predefined-objects* ----------------------------- Most relational databases support various standard features, tables, indices, triggers and stored procedures. Each vendor also has a variety of proprietary objects. The next set of maps have been created to help move between these objects. Depends on which database vendor you are using, the list of objects must be configurable. The filetype plugin attempts to define many of the standard objects, plus many additional ones. In order to make this as flexible as possible, you can override the list of objects from within your |vimrc| with the following: > let g:ftplugin_sql_objects = 'function,procedure,event,table,trigger' . \ ',schema,service,publication,database,datatype,domain' . \ ',index,subscription,synchronization,view,variable' The following |Normal| mode and |Visual| mode maps have been created which use the above list: > ]} move forward to the next 'create <object name>' [{ move backward to the previous 'create <object name>' Repeatedly pressing ]} will cycle through each of these create statements: > create table t1 ( ... ); create procedure p1 begin ... end; create index i1 on t1 (c1); The default setting for g:ftplugin_sql_objects is: > let g:ftplugin_sql_objects = 'function,procedure,event,' . \ '\\(existing\\\\|global\\s\\+temporary\\s\\+\\)\\\{,1}' . \ 'table,trigger' . \ ',schema,service,publication,database,datatype,domain' . \ ',index,subscription,synchronization,view,variable' The above will also handle these cases: > create table t1 ( ... ); create existing table t2 ( ... ); create global temporary table t3 ( ... ); By default, the ftplugin only searches for CREATE statements. You can also override this via your |vimrc| with the following: > let g:ftplugin_sql_statements = 'create,alter' The filetype plugin defines three types of comments: > 1. -- 2. // 3. /* * */ The following |Normal| mode and |Visual| mode maps have been created to work with comments: > ]" move forward to the beginning of a comment [" move forward to the end of a comment 1.4 Macros *sql-macros* ---------- Vim's feature to find macro definitions, |'define'|, is supported using this regular expression: > \c\<\(VARIABLE\|DECLARE\|IN\|OUT\|INOUT\)\> This addresses the following code: > CREATE VARIABLE myVar1 INTEGER; CREATE PROCEDURE sp_test( IN myVar2 INTEGER, OUT myVar3 CHAR(30), INOUT myVar4 NUMERIC(20,0) ) BEGIN DECLARE myVar5 INTEGER; SELECT c1, c2, c3 INTO myVar2, myVar3, myVar4 FROM T1 WHERE c4 = myVar1; END; Place your cursor on "myVar1" on this line: > WHERE c4 = myVar1; ^ Press any of the following keys: > [d [D [CTRL-D ============================================================================== 2. SQL Dialects *sql-dialects* *sql-types* *sybase* *TSQL* *Transact-SQL* *sqlanywhere* *oracle* *plsql* *sqlj* *sqlserver* *mysql* *postgres* *psql* *informix* All relational databases support SQL. There is a portion of SQL that is portable across vendors (ex. CREATE TABLE, CREATE INDEX), but there is a great deal of vendor specific extensions to SQL. Oracle supports the "CREATE OR REPLACE" syntax, column defaults specified in the CREATE TABLE statement and the procedural language (for stored procedures and triggers). The default Vim distribution ships with syntax highlighting based on Oracle's PL/SQL. The default SQL indent script works for Oracle and SQL Anywhere. The default filetype plugin works for all vendors and should remain vendor neutral, but extendable. Vim currently has support for a variety of different vendors, currently this is via syntax scripts. Unfortunately, to flip between different syntax rules you must either create: 1. New filetypes 2. Custom autocmds 3. Manual steps / commands The majority of people work with only one vendor's database product, it would be nice to specify a default in your |vimrc|. 2.1 SQLSetType *sqlsettype* *SQLSetType* -------------- For the people that work with many different databases, it would be 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: > SQLSetType Executing this function without any parameters will set the indent and syntax scripts back to their defaults, see |sql-type-default|. If you have turned off Vi's compatibility mode, |'compatible'|, you can use the <Tab> key to complete the optional parameter. After typing the function name and a space, you can use the completion to supply a parameter. The function takes the name of the Vim script you want to source. Using the |cmdline-completion| feature, the SQLSetType function will search the |'runtimepath'| for all Vim scripts with a name containing 'sql'. This takes the guess work out of the spelling of the names. The following are examples: > :SQLSetType :SQLSetType sqloracle :SQLSetType sqlanywhere :SQLSetType sqlinformix :SQLSetType mysql The easiest approach is to the use <Tab> character which will first complete the command name (SQLSetType), after a space and another <Tab>, display a list of available Vim script names: > :SQL<Tab><space><Tab> 2.2 SQL Dialect Default *sql-type-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' let g:sql_type_default = 'sqlinformix' let g:sql_type_default = 'mysql' If you added the following to your |vimrc|: > let g:sql_type_default = 'sqlinformix' The next time edit a SQL file the following scripts will be automatically loaded by Vim: > ftplugin/sql.vim syntax/sqlinformix.vim indent/sql.vim > Notice indent/sqlinformix.sql was not loaded. There is no indent file for Informix, Vim loads the default files if the specified files does not exist. ============================================================================== 3. Adding new SQL Dialects *sql-adding-dialects* If you begin working with a SQL dialect which does not have any customizations available with the default Vim distribution you can check http://www.vim.org to see if any customization currently exist. If not, you can begin by cloning an existing script. Read |filetype-plugins| for more details. To help identify these scripts, try to create the files with a "sql" prefix. If you decide you wish to create customizations for the SQLite database, you can create any of the following: > Unix ~/.vim/syntax/sqlite.vim ~/.vim/indent/sqlite.vim Windows $VIM/vimfiles/syntax/sqlite.vim $VIM/vimfiles/indent/sqlite.vim No changes are necessary to the SQLSetType function. It will automatically pickup the new SQL files and load them when you issue the SQLSetType command. ============================================================================== 4. OMNI SQL Completion *sql-completion* *omni-sql-completion* Vim 7 includes a code completion interface and functions which allows plugin developers to build in code completion for any language. Vim 7 includes code completion for the SQL language. There are two modes to the SQL completion plugin, static and dynamic. The static mode populates the popups with the data generated from current syntax highlight rules. The dynamic mode populates the popups with data retrieved directly from a database. This includes, table lists, column lists, procedures names and more. 4.1 Static Mode *sql-completion-static* --------------- The static popups created contain items defined by the active syntax rules while editing a file with a filetype of SQL. The plugin defines (by default) various maps to help the user refine the list of items to be displayed. The defaults static maps are: > imap <buffer> <C-C>a <C-\><C-O>:call sqlcomplete#Map('syntax')<CR><C-X><C-O> imap <buffer> <C-C>k <C-\><C-O>:call sqlcomplete#Map('sqlKeyword')<CR><C-X><C-O> imap <buffer> <C-C>f <C-\><C-O>:call sqlcomplete#Map('sqlFunction')<CR><C-X><C-O> imap <buffer> <C-C>o <C-\><C-O>:call sqlcomplete#Map('sqlOption')<CR><C-X><C-O> imap <buffer> <C-C>T <C-\><C-O>:call sqlcomplete#Map('sqlType')<CR><C-X><C-O> imap <buffer> <C-C>s <C-\><C-O>:call sqlcomplete#Map('sqlStatement')<CR><C-X><C-O> The static maps (which are based on the syntax highlight groups) follow this format: > imap <buffer> <C-C>k <C-\><C-O>:call sqlcomplete#Map('sqlKeyword')<CR><C-X><C-O> This command breaks down as: > imap - Create an insert map <buffer> - Only for this buffer <C-C>k - Your choice of key map <C-\><C-O> - Execute one command, return to Insert mode :call sqlcomplete#Map( - Allows the SQL completion plugin to perform some housekeeping functions to allow it to be used in conjunction with other completion plugins. Indicate which item you want the SQL completion plugin to complete. In this case we are asking the plugin to display items from the syntax highlight group 'sqlKeyword'. You can view a list of highlight group names to choose from by executing the :syntax list command while editing a SQL file. 'sqlKeyword' - Display the items for the sqlKeyword highlight group )<CR> - Execute the :let command <C-X><C-O> - Trigger the standard omni completion key stroke. Passing in 'sqlKeyword' instructs the SQL completion plugin to populate the popup with items from the sqlKeyword highlight group. The plugin will also cache this result until Vim is restarted. The syntax list is retrieved using the syntaxcomplete plugin. Using the 'syntax' keyword is a special case. This instructs the syntaxcomplete plugin to retrieve all syntax items. So this will effectively work for any of Vim's SQL syntax files. At the time of writing this includes 10 different syntax files for the different dialects of SQL (see section 3 above, |sql-dialects|). Here are some examples of the entries which are pulled from the syntax files: > All - Contains the contents of all syntax highlight groups Statements - Select, Insert, Update, Delete, Create, Alter, ... Functions - Min, Max, Trim, Round, Date, ... Keywords - Index, Database, Having, Group, With Options - Isolation_level, On_error, Qualify_owners, Fire_triggers, ... Types - Integer, Char, Varchar, Date, DateTime, Timestamp, ... 4.2 Dynamic Mode *sql-completion-dynamic* ---------------- Dynamic mode populates the popups with data directly from a database. In order for the dynamic feature to be enabled you must have the dbext.vim plugin installed, (http://vim.sourceforge.net/script.php?script_id=356). Dynamic mode is used by several features of the SQL completion plugin. After installing the dbext plugin see the dbext-tutorial for additional configuration and usage. The dbext plugin allows the SQL completion plugin to display a list of tables, procedures, views and columns. > Table List - All tables for all schema owners Procedure List - All stored procedures for all schema owners View List - All stored procedures for all schema owners Column List - For the selected table, the columns that are part of the table To enable the popup, while in INSERT mode, use the following key combinations for each group (where <C-C> means hold the CTRL key down while pressing the space bar): Table List - <C-C>t - <C-X><C-O> (the default map assumes tables) Stored Procedure List - <C-C>p View List - <C-C>v Column List - <C-C>c Windows platform only - When viewing a popup window displaying the list of tables, you can press <C-Right>, this will replace the table currently highlighted with the column list for that table. - When viewing a popup window displaying the list of columns, you can press <C-Left>, this will replace the column list with the list of tables. - This allows you to quickly drill down into a table to view it's columns and back again. The SQL completion plugin caches various lists that are displayed in the popup window. This makes the re-displaying of these lists very fast. If new tables or columns are added to the database it may become necessary to clear the plugins cache. The default map for this is: > imap <buffer> <C-C>R <C-\><C-O>:call sqlcomplete#Map('ResetCache')<CR><C-X><C-O> 4.3 SQL Tutorial *sql-completion-tutorial* ---------------- This tutorial is designed to take you through the common features of the SQL completion plugin so that: > a) You gain familiarity with the plugin b) You are introduced to some of the more common features c) Show how to customize it to your preferences d) Demonstrate "Best of Use" of the plugin (easiest way to configure). First, create a new buffer: > :e tutorial.sql Static features --------------- 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: <C-Space>t 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: > BEGIN DECLARE customer_id <C-C>T <-- Choose a type from the list Dynamic features ---------------- 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. From the SQL completion plugin's perspective, the main feature dbext provides is a connection to a database. dbext connection profiles are the most efficient mechanism to define connection information. Once connections have been setup, the SQL completion plugin uses the features of dbext in the background to populate the popups. What follows assumes dbext.vim has been correctly configured, a simple test is to run the command, :DBListTable. If a list of tables is shown, you know dbext.vim is working as expected. If not, please consult the dbext.txt documentation. 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. > 4.3.1 Table Completion: *sql-completion-tables* Press <C-C>t to display a list of tables from within the database you have connected via the dbext plugin. NOTE: All of the SQL completion popups support typing a prefix before pressing the key map. This will limit the contents of the popup window to just items beginning with those characters. > 4.3.2 Column Completion: *sql-completion-columns* The SQL completion plugin can also display a list of columns for particular tables. The column completion is trigger via <C-C>c.