vim

Fork of vim, an acceptable text editor
git clone git://git.laack.co/vim.git
Log | Files | Refs | README | LICENSE

ft_sql.txt (30665B)


      1 *ft_sql.txt*	For Vim version 9.2.  Last change: 2026 Feb 14
      2 
      3 by David Fishburn
      4 
      5 This is a filetype plugin to work with SQL files.
      6 
      7 The Structured Query Language (SQL) is a standard which specifies statements
      8 that allow a user to interact with a relational database.  Vim includes
      9 features for navigation, indentation and syntax highlighting.
     10 
     11 1. Navigation					|sql-navigation|
     12     1.1 Matchit					|sql-matchit|
     13     1.2 Text Object Motions			|sql-object-motions|
     14     1.3 Predefined Object Motions		|sql-predefined-objects|
     15     1.4 Macros					|sql-macros|
     16 2. SQL Dialects					|sql-dialects|
     17     2.1 SQLSetType				|SQLSetType|
     18     2.2 SQLGetType				|SQLGetType|
     19     2.3 SQL Dialect Default			|sql-type-default|
     20 3. Adding new SQL Dialects			|sql-adding-dialects|
     21 4. OMNI SQL Completion				|sql-completion|
     22     4.1 Static mode				|sql-completion-static|
     23     4.2 Dynamic mode				|sql-completion-dynamic|
     24     4.3 Tutorial				|sql-completion-tutorial|
     25 	4.3.1 Complete Tables			|sql-completion-tables|
     26 	4.3.2 Complete Columns			|sql-completion-columns|
     27 	4.3.3 Complete Procedures		|sql-completion-procedures|
     28 	4.3.4 Complete Views			|sql-completion-views|
     29     4.4 Completion Customization		|sql-completion-customization|
     30     4.5 SQL Maps				|sql-completion-maps|
     31     4.6 Using with other filetypes		|sql-completion-filetypes|
     32 
     33 ==============================================================================
     34 1. Navigation					*sql-navigation*
     35 
     36 The SQL ftplugin provides a number of options to assist with file
     37 navigation.
     38 
     39 
     40 1.1 Matchit					*sql-matchit*
     41 -----------
     42 The matchit plugin (http://www.vim.org/scripts/script.php?script_id=39)
     43 provides many additional features and can be customized for different
     44 languages.  The matchit plugin is configured by defining a local
     45 buffer variable, b:match_words.  Pressing the % key while on various
     46 keywords will move the cursor to its match.  For example, if the cursor
     47 is on an "if", pressing % will cycle between the "else", "elseif" and
     48 "end if" keywords.
     49 
     50 The following keywords are supported: >
     51     if
     52     elseif | elsif
     53     else [if]
     54     end if
     55 
     56     [while condition] loop
     57 	leave
     58 	break
     59 	continue
     60 	exit
     61     end loop
     62 
     63     for
     64 	leave
     65 	break
     66 	continue
     67 	exit
     68     end loop
     69 
     70     do
     71 	statements
     72     doend
     73 
     74     case
     75     when
     76     when
     77     default
     78     end case
     79 
     80     merge
     81     when not matched
     82     when matched
     83 
     84     create[ or replace] procedure|function|event
     85     returns
     86 
     87 
     88 1.2 Text Object Motions				*sql-object-motions*
     89 -----------------------
     90 Vim has a number of predefined keys for working with text |object-motions|.
     91 This filetype plugin attempts to translate these keys to maps which make sense
     92 for the SQL language.
     93 
     94 The following |Normal| mode and |Visual| mode maps exist (when you edit a SQL
     95 file): >
     96     ]]		    move forward to the next 'begin'
     97     [[		    move backwards to the previous 'begin'
     98     ][		    move forward to the next 'end'
     99     []		    move backwards to the previous 'end'
    100 
    101 
    102 1.3 Predefined Object Motions			*sql-predefined-objects*
    103 -----------------------------
    104 Most relational databases support various standard features, tables, indices,
    105 triggers and stored procedures.  Each vendor also has a variety of proprietary
    106 objects.  The next set of maps have been created to help move between these
    107 objects.  Depends on which database vendor you are using, the list of objects
    108 must be configurable.  The filetype plugin attempts to define many of the
    109 standard objects, plus many additional ones.  In order to make this as
    110 flexible as possible, you can override the list of objects from within your
    111 |vimrc| with the following: >
    112     let g:ftplugin_sql_objects = 'function,procedure,event,table,trigger' ..
    113 		\ ',schema,service,publication,database,datatype,domain' ..
    114 		\ ',index,subscription,synchronization,view,variable'
    115 
    116 The following |Normal| mode and |Visual| mode maps have been created which use
    117 the above list: >
    118     ]}		    move forward to the next 'create <object name>'
    119     [{		    move backward to the previous 'create <object name>'
    120 
    121 Repeatedly pressing ]} will cycle through each of these create statements: >
    122     create table t1 (
    123 	...
    124     );
    125 
    126     create procedure p1
    127     begin
    128 	...
    129     end;
    130 
    131     create index i1 on t1 (c1);
    132 
    133 The default setting for g:ftplugin_sql_objects is: >
    134     let g:ftplugin_sql_objects = 'function,procedure,event,' ..
    135 		\ '\\(existing\\\\|global\\s\\+temporary\\s\\+\\)\\\{,1}' ..
    136 		\ 'table,trigger' ..
    137 		\ ',schema,service,publication,database,datatype,domain' ..
    138 		\ ',index,subscription,synchronization,view,variable'
    139 
    140 The above will also handle these cases: >
    141     create table t1 (
    142 	...
    143     );
    144     create existing table t2 (
    145 	...
    146     );
    147     create global temporary table t3 (
    148 	...
    149     );
    150 
    151 By default, the ftplugin only searches for CREATE statements.  You can also
    152 override this via your |vimrc| with the following: >
    153     let g:ftplugin_sql_statements = 'create,alter'
    154 
    155 The filetype plugin defines three types of comments: >
    156     1.  --
    157     2.  //
    158     3.  /*
    159 	 *
    160 	 */
    161 
    162 The following |Normal| mode and |Visual| mode maps have been created to work
    163 with comments: >
    164     ]"		    move forward to the beginning of a comment
    165     ["		    move forward to the end of a comment
    166 
    167 
    168 
    169 1.4 Macros					   *sql-macros*
    170 ----------
    171 Vim's feature to find macro definitions, 'define', is supported using this
    172 regular expression: >
    173     \c\<\(VARIABLE\|DECLARE\|IN\|OUT\|INOUT\)\>
    174 
    175 This addresses the following code: >
    176     CREATE VARIABLE myVar1 INTEGER;
    177 
    178     CREATE PROCEDURE sp_test(
    179 	IN myVar2 INTEGER,
    180 	OUT myVar3 CHAR(30),
    181 	INOUT myVar4 NUMERIC(20,0)
    182     )
    183     BEGIN
    184 	DECLARE myVar5 INTEGER;
    185 
    186 	SELECT c1, c2, c3
    187 	  INTO myVar2, myVar3, myVar4
    188 	  FROM T1
    189 	 WHERE c4 = myVar1;
    190     END;
    191 
    192 Place your cursor on "myVar1" on this line: >
    193 	 WHERE c4 = myVar1;
    194 		     ^
    195 
    196 Press any of the following keys: >
    197     [d
    198     [D
    199     [CTRL-D
    200 
    201 
    202 ==============================================================================
    203 2. SQL Dialects					*sql-dialects* *sql-types*
    204 						*sybase* *TSQL* *Transact-SQL*
    205 						*sqlanywhere*
    206 						*oracle* *plsql* *sqlj*
    207 						*sqlserver*
    208 						*mysql* *postgresql* *psql*
    209 						*informix*
    210 
    211 All relational databases support SQL.  There is a portion of SQL that is
    212 portable across vendors (ex. CREATE TABLE, CREATE INDEX), but there is a
    213 great deal of vendor specific extensions to SQL.  Oracle supports the
    214 "CREATE OR REPLACE" syntax, column defaults specified in the CREATE TABLE
    215 statement and the procedural language (for stored procedures and triggers).
    216 
    217 The default Vim distribution ships with syntax highlighting based on Oracle's
    218 PL/SQL.  The default SQL indent script works for Oracle and SQL Anywhere.
    219 The default filetype plugin works for all vendors and should remain vendor
    220 neutral, but extendable.
    221 
    222 Vim currently has support for a variety of different vendors, currently this
    223 is via syntax scripts. Unfortunately, to flip between different syntax rules
    224 you must either create:
    225     1.  New filetypes
    226     2.  Custom autocmds
    227     3.  Manual steps / commands
    228 
    229 The majority of people work with only one vendor's database product, it would
    230 be nice to specify a default in your |vimrc|.
    231 
    232 
    233 2.1 SQLSetType					*sqlsettype* *SQLSetType*
    234 --------------
    235 For the people that work with many different databases, it is nice to be
    236 able to flip between the various vendors rules (indent, syntax) on a per
    237 buffer basis, at any time.  The ftplugin/sql.vim file defines this function: >
    238     SQLSetType
    239 
    240 Executing this function without any parameters will set the indent and syntax
    241 scripts back to their defaults, see |sql-type-default|.  If you have turned
    242 off Vi's compatibility mode, 'compatible', you can use the <Tab> key to
    243 complete the optional parameter.
    244 
    245 After typing the function name and a space, you can use the completion to
    246 supply a parameter.  The function takes the name of the Vim script you want to
    247 source.  Using the |cmdline-completion| feature, the SQLSetType function will
    248 search the 'runtimepath' for all Vim scripts with a name containing 'sql'.
    249 This takes the guess work out of the spelling of the names.  The following are
    250 examples: >
    251     :SQLSetType
    252     :SQLSetType sqloracle
    253     :SQLSetType sqlanywhere
    254     :SQLSetType sqlinformix
    255     :SQLSetType mysql
    256 
    257 The easiest approach is to the use <Tab> character which will first complete
    258 the command name (SQLSetType), after a space and another <Tab>, display a list
    259 of available Vim script names: >
    260     :SQL<Tab><space><Tab>
    261 
    262 
    263 2.2 SQLGetType					*sqlgettype* *SQLGetType*
    264 --------------
    265 At anytime you can determine which SQL dialect you are using by calling the
    266 SQLGetType command.  The ftplugin/sql.vim file defines this function: >
    267     SQLGetType
    268 
    269 This will echo: >
    270     Current SQL dialect in use:sqlanywhere
    271 
    272 
    273 2.3 SQL Dialect Default				*sql-type-default*
    274 -----------------------
    275 As mentioned earlier, the default syntax rules for Vim is based on Oracle
    276 (PL/SQL).  You can override this default by placing one of the following in
    277 your |vimrc|: >
    278     let g:sql_type_default = 'sqlanywhere'
    279     let g:sql_type_default = 'sqlinformix'
    280     let g:sql_type_default = 'mysql'
    281 
    282 If you added the following to your |vimrc|: >
    283     let g:sql_type_default = 'sqlinformix'
    284 
    285 The next time edit a SQL file the following scripts will be automatically
    286 loaded by Vim: >
    287     ftplugin/sql.vim
    288     syntax/sqlinformix.vim
    289     indent/sql.vim
    290 >
    291 Notice indent/sqlinformix.sql was not loaded.  There is no indent file
    292 for Informix, Vim loads the default files if the specified files does not
    293 exist.
    294 
    295 
    296 ==============================================================================
    297 3. Adding new SQL Dialects			*sql-adding-dialects*
    298 
    299 If you begin working with a SQL dialect which does not have any customizations
    300 available with the default Vim distribution you can check http://www.vim.org
    301 to see if any customization currently exist.  If not, you can begin by cloning
    302 an existing script.  Read |filetype-plugins| for more details.
    303 
    304 To help identify these scripts, try to create the files with a "sql" prefix.
    305 If you decide you wish to create customizations for the SQLite database, you
    306 can create any of the following: >
    307     Unix
    308 	~/.vim/syntax/sqlite.vim
    309 	~/.vim/indent/sqlite.vim
    310     Windows
    311 	$VIM/vimfiles/syntax/sqlite.vim
    312 	$VIM/vimfiles/indent/sqlite.vim
    313 
    314 No changes are necessary to the SQLSetType function.  It will automatically
    315 pick up the new SQL files and load them when you issue the SQLSetType command.
    316 
    317 
    318 ==============================================================================
    319 4. OMNI SQL Completion				*sql-completion*
    320 						*omni-sql-completion*
    321 
    322 Vim 7 includes a code completion interface and functions which allows plugin
    323 developers to build in code completion for any language.  Vim 7 includes
    324 code completion for the SQL language.
    325 
    326 There are two modes to the SQL completion plugin, static and dynamic.  The
    327 static mode populates the popups with the data generated from current syntax
    328 highlight rules.  The dynamic mode populates the popups with data retrieved
    329 directly from a database.  This includes, table lists, column lists,
    330 procedures names and more.
    331 
    332 4.1 Static Mode					*sql-completion-static*
    333 ---------------
    334 The static popups created contain items defined by the active syntax rules
    335 while editing a file with a filetype of SQL.  The plugin defines (by default)
    336 various maps to help the user refine the list of items to be displayed.
    337 The defaults static maps are: >
    338     imap <buffer> <C-C>a <C-\><C-O>:call sqlcomplete#Map('syntax')<CR><C-X><C-O>
    339     imap <buffer> <C-C>k <C-\><C-O>:call sqlcomplete#Map('sqlKeyword')<CR><C-X><C-O>
    340     imap <buffer> <C-C>f <C-\><C-O>:call sqlcomplete#Map('sqlFunction')<CR><C-X><C-O>
    341     imap <buffer> <C-C>o <C-\><C-O>:call sqlcomplete#Map('sqlOption')<CR><C-X><C-O>
    342     imap <buffer> <C-C>T <C-\><C-O>:call sqlcomplete#Map('sqlType')<CR><C-X><C-O>
    343     imap <buffer> <C-C>s <C-\><C-O>:call sqlcomplete#Map('sqlStatement')<CR><C-X><C-O>
    344 
    345 The use of "<C-C>" can be user chosen by using the following in your |.vimrc|
    346 as it may not work properly on all platforms: >
    347     let g:ftplugin_sql_omni_key = '<C-C>'
    348 >
    349 The static maps (which are based on the syntax highlight groups) follow this
    350 format: >
    351     imap <buffer> <C-C>k <C-\><C-O>:call sqlcomplete#Map('sqlKeyword')<CR><C-X><C-O>
    352     imap <buffer> <C-C>k <C-\><C-O>:call sqlcomplete#Map('sqlKeyword\w*')<CR><C-X><C-O>
    353 
    354 This command breaks down as: >
    355     imap		   - Create an insert map
    356     <buffer>		   - Only for this buffer
    357     <C-C>k		   - Your choice of key map
    358     <C-\><C-O>		   - Execute one command, return to Insert mode
    359     :call sqlcomplete#Map( - Allows the SQL completion plugin to perform some
    360 			     housekeeping functions to allow it to be used in
    361 			     conjunction with other completion plugins.
    362 			     Indicate which item you want the SQL completion
    363 			     plugin to complete.
    364 			     In this case we are asking the plugin to display
    365 			     items from the syntax highlight group
    366 			     'sqlKeyword'.
    367 			     You can view a list of highlight group names to
    368 			     choose from by executing the
    369 				 :syntax list
    370 			     command while editing a SQL file.
    371     'sqlKeyword'	   - Display the items for the sqlKeyword highlight
    372 			     group
    373     'sqlKeyword\w*'	   - A second option available with Vim 7.4 which
    374 			     uses a regular expression to determine which
    375 			     syntax groups to use
    376     )<CR>		   - Execute the :let command
    377     <C-X><C-O>		   - Trigger the standard omni completion key stroke.
    378 			     Passing in 'sqlKeyword' instructs the SQL
    379 			     completion plugin to populate the popup with
    380 			     items from the sqlKeyword highlight group.  The
    381 			     plugin will also cache this result until Vim is
    382 			     restarted.  The syntax list is retrieved using
    383 			     the syntaxcomplete plugin.
    384 
    385 Using the 'syntax' keyword is a special case.  This instructs the
    386 syntaxcomplete plugin to retrieve all syntax items.  So this will effectively
    387 work for any of Vim's SQL syntax files.  At the time of writing this includes
    388 10 different syntax files for the different dialects of SQL (see section 3
    389 above, |sql-dialects|).
    390 
    391 Here are some examples of the entries which are pulled from the syntax files: >
    392      All
    393 	 - Contains the contents of all syntax highlight groups
    394      Statements
    395 	 - Select, Insert, Update, Delete, Create, Alter, ...
    396      Functions
    397 	 - Min, Max, Trim, Round, Date, ...
    398      Keywords
    399 	 - Index, Database, Having, Group, With
    400      Options
    401 	 - Isolation_level, On_error, Qualify_owners, Fire_triggers, ...
    402      Types
    403 	 - Integer, Char, Varchar, Date, DateTime, Timestamp, ...
    404 
    405 
    406 4.2 Dynamic Mode				*sql-completion-dynamic*
    407 ----------------
    408 Dynamic mode populates the popups with data directly from a database.  In
    409 order for the dynamic feature to be enabled you must have the dbext.vim
    410 plugin installed, (https://www.vim.org/scripts/script.php?script_id=356).
    411 
    412 Dynamic mode is used by several features of the SQL completion plugin.
    413 After installing the dbext plugin see the dbext-tutorial for additional
    414 configuration and usage.  The dbext plugin allows the SQL completion plugin
    415 to display a list of tables, procedures, views and columns. >
    416      Table List
    417 	 - All tables for all schema owners
    418      Procedure List
    419 	 - All stored procedures for all schema owners
    420      View List
    421 	 - All stored procedures for all schema owners
    422      Column List
    423 	 - For the selected table, the columns that are part of the table
    424 
    425 To enable the popup, while in INSERT mode, use the following key combinations
    426 for each group (where <C-C> means hold the CTRL key down while pressing
    427 the space bar):
    428      Table List		   - <C-C>t
    429 			   - <C-X><C-O> (the default map assumes tables)
    430      Stored Procedure List - <C-C>p
    431      View List		   - <C-C>v
    432      Column List	   - <C-C>c
    433 
    434      Drilling In / Out     - When viewing a popup window displaying the list
    435 			     of tables, you can press <Right>, this will
    436 			     replace the table currently highlighted with
    437 			     the column list for that table.
    438 			   - When viewing a popup window displaying the list
    439 			     of columns, you can press <Left>, this will
    440 			     replace the column list with the list of tables.
    441 			   - This allows you to quickly drill down into a
    442 			     table to view its columns and back again.
    443 			   - <Right> and <Left> can also be chosen via
    444 			     your |.vimrc| >
    445                                 let g:ftplugin_sql_omni_key_right = '<Right>'
    446                                 let g:ftplugin_sql_omni_key_left  = '<Left>'
    447 
    448 The SQL completion plugin caches various lists that are displayed in
    449 the popup window.  This makes the re-displaying of these lists very
    450 fast.  If new tables or columns are added to the database it may become
    451 necessary to clear the plugins cache.  The default map for this is: >
    452     imap <buffer> <C-C>R <C-\><C-O>:call sqlcomplete#Map('ResetCache')<CR><C-X><C-O>
    453 
    454 
    455 4.3 SQL Tutorial				*sql-completion-tutorial*
    456 ----------------
    457 
    458 This tutorial is designed to take you through the common features of the SQL
    459 completion plugin so that: >
    460      a) You gain familiarity with the plugin
    461      b) You are introduced to some of the more common features
    462      c) Show how to customize it to your preferences
    463      d) Demonstrate "Best of Use" of the plugin (easiest way to configure).
    464 
    465 First, create a new buffer: >
    466      :e tutorial.sql
    467 
    468 
    469 Static features
    470 ---------------
    471 To take you through the various lists, simply enter insert mode, hit:
    472     <C-C>s   (show SQL statements)
    473 At this point, you can page down through the list until you find "select".
    474 If you are familiar with the item you are looking for, for example you know
    475 the statement begins with the letter "s".  You can type ahead (without the
    476 quotes) "se" then press:
    477     <C-Space>t
    478 Assuming "select" is highlighted in the popup list press <Enter> to choose
    479 the entry.  Now type:
    480     * fr<C-C>a (show all syntax items)
    481 choose "from" from the popup list.
    482 
    483 When writing stored procedures using the "type" list is useful.  It contains
    484 a list of all the database supported types.  This may or may not be true
    485 depending on the syntax file you are using.  The SQL Anywhere syntax file
    486 (sqlanywhere.vim) has support for this: >
    487      BEGIN
    488 	DECLARE customer_id <C-C>T <-- Choose a type from the list
    489 
    490 
    491 Dynamic features
    492 ----------------
    493 To take advantage of the dynamic features you must first install the
    494 dbext.vim plugin (https://www.vim.org/scripts/script.php?script_id=356).  It
    495 also comes with a tutorial.  From the SQL completion plugin's perspective,
    496 the main feature dbext provides is a connection to a database.  dbext
    497 connection profiles are the most efficient mechanism to define connection
    498 information.  Once connections have been setup, the SQL completion plugin
    499 uses the features of dbext in the background to populate the popups.
    500 
    501 What follows assumes dbext.vim has been correctly configured, a simple test
    502 is to run the command, :DBListTable.  If a list of tables is shown, you know
    503 dbext.vim is working as expected.  If not, please consult the dbext.txt
    504 documentation.
    505 
    506 Assuming you have followed the dbext-tutorial you can press <C-C>t to
    507 display a list of tables.  There is a delay while dbext is creating the table
    508 list.  After the list is displayed press <C-W>.  This will remove both the
    509 popup window and the table name already chosen when the list became active.
    510 
    511  4.3.1 Table Completion:			*sql-completion-tables*
    512 
    513 Press <C-C>t to display a list of tables from within the database you
    514 have connected via the dbext plugin.
    515 NOTE: All of the SQL completion popups support typing a prefix before pressing
    516 the key map.  This will limit the contents of the popup window to just items
    517 beginning with those characters.
    518 
    519  4.3.2 Column Completion:			*sql-completion-columns*
    520 
    521 The SQL completion plugin can also display a list of columns for particular
    522 tables.  The column completion is triggered via <C-C>c.
    523 
    524 NOTE: The following example uses <Right> to trigger a column list while
    525       the popup window is active.
    526 
    527 Example of using column completion:
    528      - Press <C-C>t again to display the list of tables.
    529      - When the list is displayed in the completion window, press <Right>,
    530        this will replace the list of tables, with a list of columns for the
    531        table highlighted (after the same short delay).
    532      - If you press <Left>, this will again replace the column list with the
    533        list of tables.  This allows you to drill into tables and column lists
    534        very quickly.
    535      - Press <Right> again while the same table is highlighted.  You will
    536        notice there is no delay since the column list has been cached.  If you
    537        change the schema of a cached table you can press <C-C>R, which
    538        clears the SQL completion cache.
    539      - NOTE: <Right> and <Left> have been designed to work while the
    540        completion window is active.  If the completion popup window is
    541        not active, a normal <Right> or <Left> will be executed.
    542 
    543 Let's look at how we can build a SQL statement dynamically.  A select statement
    544 requires a list of columns.  There are two ways to build a column list using
    545 the SQL completion plugin. >
    546     One column at a time:
    547 <       1. After typing SELECT press <C-C>t to display a list of tables.
    548 	2. Choose a table from the list.
    549 	3. Press <Right> to display a list of columns.
    550 	4. Choose the column from the list and press enter.
    551 	5. Enter a "," and press <C-C>c.  Generating a column list
    552 	   generally requires having the cursor on a table name.  The plugin
    553 	   uses this name to determine what table to retrieve the column list.
    554 	   In this step, since we are pressing <C-C>c without the cursor
    555 	   on a table name the column list displayed will be for the previous
    556 	   table.  Choose a different column and move on.
    557 	6. Repeat step 5 as often as necessary. >
    558     All columns for a table:
    559 <	1. After typing SELECT press <C-C>t to display a list of tables.
    560 	2. Highlight the table you need the column list for.
    561 	3. Press <Enter> to choose the table from the list.
    562 	4. Press <C-C>l to request a comma-separated list of all columns
    563 	   for this table.
    564 	5. Based on the table name chosen in step 3, the plugin attempts to
    565 	   decide on a reasonable table alias.	You are then prompted to
    566 	   either accept of change the alias.  Press OK.
    567 	6. The table name is replaced with the column list of the table is
    568 	   replaced with the comma separate list of columns with the alias
    569 	   prepended to each of the columns.
    570 	7. Step 3 and 4 can be replaced by pressing <C-C>L, which has
    571 	   a <C-Y> embedded in the map to choose the currently highlighted
    572 	   table in the list.
    573 
    574 There is a special provision when writing select statements.  Consider the
    575 following statement: >
    576      select *
    577        from customer c,
    578 	    contact cn,
    579 	    department as dp,
    580 	    employee e,
    581 	    site_options so
    582       where c.
    583 
    584 In INSERT mode after typing the final "c." which is an alias for the
    585 "customer" table, you can press either <C-C>c or <C-X><C-O>.  This will
    586 popup a list of columns for the customer table.  It does this by looking back
    587 to the beginning of the select statement and finding a list of the tables
    588 specified in the FROM clause.  In this case it notes that in the string
    589 "customer c", "c" is an alias for the customer table.  The optional "AS"
    590 keyword is also supported, "customer AS c".
    591 
    592 
    593  4.3.3 Procedure Completion:			*sql-completion-procedures*
    594 
    595 Similar to the table list, <C-C>p, will display a list of stored
    596 procedures stored within the database.
    597 
    598  4.3.4 View Completion:				*sql-completion-views*
    599 
    600 Similar to the table list, <C-C>v, will display a list of views in the
    601 database.
    602 
    603 
    604 4.4 Completion Customization			*sql-completion-customization*
    605 ----------------------------
    606 
    607 The SQL completion plugin can be customized through various options set in
    608 your |vimrc|: >
    609     omni_sql_no_default_maps
    610 <       - Default: This variable is not defined
    611 	- If this variable is defined, no maps are created for OMNI
    612 	  completion.  See |sql-completion-maps| for further discussion.
    613 >
    614     omni_sql_use_tbl_alias
    615 <	- Default: a
    616 	- This setting is only used when generating a comma-separated
    617 	  column list.	By default the map is <C-C>l.  When generating
    618 	  a column list, an alias can be prepended to the beginning of each
    619 	  column, for example:	e.emp_id, e.emp_name.  This option has three
    620 	  settings: >
    621 		n - do not use an alias
    622 		d - use the default (calculated) alias
    623 		a - ask to confirm the alias name
    624 <
    625 	  An alias is determined following a few rules:
    626 	       1.  If the table name has an '_', then use it as a separator: >
    627 		   MY_TABLE_NAME --> MTN
    628 		   my_table_name --> mtn
    629 		   My_table_NAME --> MtN
    630 <	       2.  If the table name does NOT contain an '_', but DOES use
    631 		   mixed case then the case is used as a separator: >
    632 		   MyTableName --> MTN
    633 <	       3.  If the table name does NOT contain an '_', and does NOT
    634 		   use mixed case then the first letter of the table is used: >
    635 		   mytablename --> m
    636 		   MYTABLENAME --> M
    637 
    638     omni_sql_ignorecase
    639 <	- Default: Current setting for 'ignorecase'
    640 	- Valid settings are 0 or 1.
    641 	- When entering a few letters before initiating completion, the list
    642 	  will be filtered to display only the entries which begin with the
    643 	  list of characters.  When this option is set to 0, the list will be
    644 	  filtered using case sensitivity. >
    645 
    646     omni_sql_include_owner
    647 <	- Default: 0, unless dbext.vim 3.00 has been installed
    648 	- Valid settings are 0 or 1.
    649 	- When completing tables, procedure or views and using dbext.vim 3.00
    650 	  or higher the list of objects will also include the owner name.
    651 	  When completing these objects and omni_sql_include_owner is enabled
    652 	  the owner name will be replaced. >
    653 
    654     omni_sql_precache_syntax_groups
    655 <	- Default:
    656 	  ['syntax','sqlKeyword','sqlFunction','sqlOption','sqlType','sqlStatement']
    657 	- sqlcomplete can be used in conjunction with other completion
    658 	  plugins.  This is outlined at |sql-completion-filetypes|.  When the
    659 	  filetype is changed temporarily to SQL, the sqlcompletion plugin
    660 	  will cache the syntax groups listed in the List specified in this
    661 	  option.
    662 >
    663 
    664 4.5 SQL Maps					*sql-completion-maps*
    665 ------------
    666 
    667 The default SQL maps have been described in other sections of this document in
    668 greater detail.  Here is a list of the maps with a brief description of each.
    669 
    670 Static Maps
    671 -----------
    672 These are maps which use populate the completion list using Vim's syntax
    673 highlighting rules. >
    674     <C-C>a
    675 <       - Displays all SQL syntax items. >
    676     <C-C>k
    677 <       - Displays all SQL syntax items defined as 'sqlKeyword'. >
    678     <C-C>f
    679 <       - Displays all SQL syntax items defined as 'sqlFunction. >
    680     <C-C>o
    681 <       - Displays all SQL syntax items defined as 'sqlOption'. >
    682     <C-C>T
    683 <       - Displays all SQL syntax items defined as 'sqlType'. >
    684     <C-C>s
    685 <       - Displays all SQL syntax items defined as 'sqlStatement'. >
    686 
    687 Dynamic Maps
    688 ------------
    689 These are maps which use populate the completion list using the dbext.vim
    690 plugin. >
    691     <C-C>t
    692 <       - Displays a list of tables. >
    693     <C-C>p
    694 <       - Displays a list of procedures. >
    695     <C-C>v
    696 <       - Displays a list of views. >
    697     <C-C>c
    698 <       - Displays a list of columns for a specific table. >
    699     <C-C>l
    700 <       - Displays a comma-separated list of columns for a specific table. >
    701     <C-C>L
    702 <       - Displays a comma-separated list of columns for a specific table.
    703 	  This should only be used when the completion window is active. >
    704     <Right>
    705 <	- Displays a list of columns for the table currently highlighted in
    706 	  the completion window.  <Right> is not recognized on most Unix
    707 	  systems, so this maps is only created on the Windows platform.
    708 	  If you would like the same feature on Unix, choose a different key
    709 	  and make the same map in your vimrc. >
    710     <Left>
    711 <	- Displays the list of tables.
    712 	  <Left> is not recognized on most Unix systems, so this maps is
    713 	  only created on the Windows platform.  If you would like the same
    714 	  feature on Unix, choose a different key and make the same map in
    715 	  your vimrc. >
    716     <C-C>R
    717 <	- This maps removes all cached items and forces the SQL completion
    718 	  to regenerate the list of items.
    719 
    720 Customizing Maps
    721 ----------------
    722 You can create as many additional key maps as you like.  Generally, the maps
    723 will be specifying different syntax highlight groups.
    724 
    725 If you do not wish the default maps created or the key choices do not work on
    726 your platform (often a case on *nix) you define the following variable in
    727 your |vimrc|: >
    728     let g:omni_sql_no_default_maps = 1
    729 
    730 Do not edit ftplugin/sql.vim directly!  If you change this file your changes
    731 will be over written on future updates.  Vim has a special directory structure
    732 which allows you to make customizations without changing the files that are
    733 included with the Vim distribution.  If you wish to customize the maps
    734 create an after/ftplugin/sql.vim (see |after-directory|) and place the same
    735 maps from the ftplugin/sql.vim in it using your own key strokes.  <C-C> was
    736 chosen since it will work on both Windows and *nix platforms.  On the windows
    737 platform you can also use <C-Space> or ALT keys.
    738 
    739 
    740 4.6 Using with other filetypes			*sql-completion-filetypes*
    741 ------------------------------
    742 
    743 Many times SQL can be used with different filetypes.  For example Perl, Java,
    744 PHP, Javascript can all interact with a database.  Often you need both the SQL
    745 completion and the completion capabilities for the current language you are
    746 editing.
    747 
    748 This can be enabled easily with the following steps (assuming a Perl file): >
    749     1.  :e test.pl
    750     2.  :set filetype=sql
    751     3.  :set ft=perl
    752 
    753 Step 1
    754 ------
    755 Begins by editing a Perl file.  Vim automatically sets the filetype to
    756 "perl".  By default, Vim runs the appropriate filetype file
    757 ftplugin/perl.vim.  If you are using the syntax completion plugin by following
    758 the directions at |ft-syntax-omni| then the 'omnifunc' option has been set to
    759 "syntax#Complete".  Pressing <C-X><C-O> will display the omni popup containing
    760 the syntax items for Perl.
    761 
    762 Step 2
    763 ------
    764 Manually setting the filetype to 'sql' will also fire the appropriate filetype
    765 files ftplugin/sql.vim.  This file will define a number of buffer specific
    766 maps for SQL completion, see |sql-completion-maps|.  Now these maps have
    767 been created and the SQL completion plugin has been initialized.  All SQL
    768 syntax items have been cached in preparation.  The SQL filetype script detects
    769 we are attempting to use two different completion plugins.  Since the SQL maps
    770 begin with <C-C>, the maps will toggle the 'omnifunc' when in use.  So you
    771 can use <C-X><C-O> to continue using the completion for Perl (using the syntax
    772 completion plugin) and <C-C> to use the SQL completion features.
    773 
    774 Step 3
    775 ------
    776 Setting the filetype back to Perl sets all the usual "perl" related items back
    777 as they were.
    778 
    779 
    780 vim:tw=78:ts=8:noet:ft=help:norl: