path: root/runtime/doc/sql.txt

*sql.txt*   	For Vim version 7.0c.  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: