# Vim documentation: sql

main help file

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

    top - main help file