| *sql.txt* For Vim version 7.0aa. Last change: Fri Jan 06 2006 8:09:25 AM |
| |
| 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| |
| |
| ============================================================================== |
| 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, indicies, |
| 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* *postgress* *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. |
| |
| |
| |
| |
| vim:tw=78:ts=8:ft=help:norl:ff=unix: |