1 " SQL filetype plugin file
2 " Language: SQL (Common for Oracle, Microsoft SQL Server, Sybase)
4 " Maintainer: David Fishburn <dfishburn dot vim at gmail dot com>
5 " Last Change: 2013 May 13
6 " Download: http://vim.sourceforge.net/script.php?script_id=454
8 " For more details please use:
11 " This file should only contain values that are common to all SQL languages
12 " Oracle, Microsoft SQL Server, Sybase ASA/ASE, MySQL, and so on
13 " If additional features are required create:
14 " vimfiles/after/ftplugin/sql.vim (Windows)
15 " .vim/after/ftplugin/sql.vim (Unix)
16 " to override and add any of your own settings.
19 " This file also creates a command, SQLSetType, which allows you to change
20 " SQL dialects on the fly. For example, if I open an Oracle SQL file, it
21 " is color highlighted appropriately. If I open an Informix SQL file, it
22 " will still be highlighted according to Oracles settings. By running:
23 " :SQLSetType sqlinformix
25 " All files called sqlinformix.vim will be loaded from the indent and syntax
26 " directories. This allows you to easily flip SQL dialects on a per file
27 " basis. NOTE: you can also use completion:
30 " To change the default dialect, add the following to your vimrc:
31 " let g:sql_type_default = 'sqlanywhere'
33 " This file also creates a command, SQLGetType, which allows you to
34 " determine what the current dialect is in use.
39 " Version 11.0 (May 2013)
41 " NF: Updated to use SyntaxComplete's new regex support for syntax groups.
43 " Version 10.0 (Dec 2012)
45 " NF: Changed all maps to use noremap instead of must map
46 " NF: Changed all visual maps to use xnoremap instead of vnoremap as they
47 " should only be used in visual mode and not select mode.
48 " BF: Most of the maps were using doubled up backslashes before they were
49 " changed to using the search() function, which meant they no longer
54 " NF: Completes 'b:undo_ftplugin'
55 " BF: Correctly set cpoptions when creating script
59 " NF: Improved the matchit plugin regex (Talek)
63 " NF: Calls the sqlcomplete#ResetCacheSyntax() function when calling
68 " NF: Adds the command SQLGetType
72 " NF: Adds the ability to choose the keys to control SQL completion, just add
73 " the following to your .vimrc:
74 " let g:ftplugin_sql_omni_key = '<C-C>'
75 " let g:ftplugin_sql_omni_key_right = '<Right>'
76 " let g:ftplugin_sql_omni_key_left = '<Left>'
78 " BF: format-options - Auto-wrap comments using textwidth was turned off
82 " Only do this when not done yet for this buffer
83 if exists("b:did_ftplugin")
90 " Disable autowrapping for code, but enable for comments
91 " t Auto-wrap text using textwidth
92 " c Auto-wrap comments using textwidth, inserting the current comment
93 " leader automatically.
94 setlocal formatoptions-=t
95 setlocal formatoptions+=c
97 " Functions/Commands to allow the user to change SQL syntax dialects
98 " through the use of :SQLSetType <tab> for completion.
99 " This works with both Vim 6 and 7.
101 if !exists("*SQL_SetType")
102 " NOTE: You cannot use function! since this file can be
103 " sourced from within this function. That will result in
104 " an error reported by Vim.
105 function SQL_GetList(ArgLead, CmdLine, CursorPos)
107 if !exists('s:sql_list')
108 " Grab a list of files that contain "sql" in their names
109 let list_indent = globpath(&runtimepath, 'indent/*sql*')
110 let list_syntax = globpath(&runtimepath, 'syntax/*sql*')
111 let list_ftplugin = globpath(&runtimepath, 'ftplugin/*sql*')
113 let sqls = "\n".list_indent."\n".list_syntax."\n".list_ftplugin."\n"
115 " Strip out everything (path info) but the filename
117 " From between two newline characters
118 " Non-greedily grab all characters
119 " Followed by a valid filename \w\+\.\w\+ (sql.vim)
120 " Followed by a newline, but do not include the newline
122 " Replace it with just the filename (get rid of PATH)
124 " Recursively, since there are many filenames that contain
125 " the word SQL in the indent, syntax and ftplugin directory
126 let sqls = substitute( sqls,
127 \ '[\n]\%(.\{-}\)\(\w\+\.\w\+\)\n\@=',
132 " Remove duplicates, since sqlanywhere.vim can exist in the
133 " sytax, indent and ftplugin directory, yet we only want
134 " to display the option once
135 let index = match(sqls, '.\{-}\ze\n')
137 " Get the first filename
138 let file = matchstr(sqls, '.\{-}\ze\n', index)
139 " Recursively replace any *other* occurrence of that
140 " filename with nothing (ie remove it)
141 let sqls = substitute(sqls, '\%>'.(index+strlen(file)).'c\<'.file.'\>\n', '', 'g')
142 " Move on to the next filename
143 let index = match(sqls, '.\{-}\ze\n', (index+strlen(file)+1))
146 " Sort the list if using version 7
148 let mylist = split(sqls, "\n")
149 let mylist = sort(mylist)
150 let sqls = join(mylist, "\n")
153 let s:sql_list = sqls
160 function SQL_SetType(name)
162 " User has decided to override default SQL scripts and
163 " specify a vendor specific version
164 " (ie Oracle, Informix, SQL Anywhere, ...)
165 " So check for an remove any settings that prevent the
166 " scripts from being executed, and then source the
167 " appropriate Vim scripts.
168 if exists("b:did_ftplugin")
171 if exists("b:current_syntax")
172 " echomsg 'SQLSetType - clearing syntax'
175 if exists("b:did_indent")
176 " echomsg 'SQLSetType - clearing indent'
178 " Set these values to their defaults
183 " Ensure the name is in the correct format
184 let new_sql_type = substitute(a:name,
185 \ '\s*\([^\.]\+\)\(\.\w\+\)\?', '\L\1', '')
187 " Do not specify a buffer local variable if it is
189 if new_sql_type == 'sql'
190 let new_sql_type = 'sqloracle'
192 let b:sql_type_override = new_sql_type
194 " Remove any cached SQL since a new sytax will have different
196 if !exists('g:loaded_sql_completion') || g:loaded_sql_completion >= 100
197 call sqlcomplete#ResetCacheSyntax()
200 " Vim will automatically source the correct files if we
201 " change the filetype. You cannot do this with setfiletype
202 " since that command will only execute if a filetype has
203 " not already been set. In this case we want to override
204 " the existing filetype.
205 let &filetype = 'sql'
207 if b:sql_compl_savefunc != ""
208 " We are changing the filetype to SQL from some other filetype
209 " which had OMNI completion defined. We need to activate the
210 " SQL completion plugin in order to cache some of the syntax items
211 " while the syntax rules for SQL are active.
212 call sqlcomplete#PreCacheSyntax()
215 command! -nargs=* -complete=custom,SQL_GetList SQLSetType :call SQL_SetType(<q-args>)
219 " Functions/Commands to allow the user determine current SQL syntax dialect
220 " This works with both Vim 6 and 7.
222 if !exists("*SQL_GetType")
223 function SQL_GetType()
224 if exists('b:sql_type_override')
225 echomsg "Current SQL dialect in use:".b:sql_type_override
227 echomsg "Current SQL dialect in use:".g:sql_type_default
230 command! -nargs=0 SQLGetType :call SQL_GetType()
233 if exists("b:sql_type_override")
234 " echo 'sourcing buffer ftplugin/'.b:sql_type_override.'.vim'
235 if globpath(&runtimepath, 'ftplugin/'.b:sql_type_override.'.vim') != ''
236 exec 'runtime ftplugin/'.b:sql_type_override.'.vim'
238 " echomsg 'ftplugin/'.b:sql_type_override.' not exist, using default'
240 elseif exists("g:sql_type_default")
241 " echo 'sourcing global ftplugin/'.g:sql_type_default.'.vim'
242 if globpath(&runtimepath, 'ftplugin/'.g:sql_type_default.'.vim') != ''
243 exec 'runtime ftplugin/'.g:sql_type_default.'.vim'
245 " echomsg 'ftplugin/'.g:sql_type_default.'.vim not exist, using default'
249 " If the above runtime command succeeded, do not load the default settings
250 if exists("b:did_ftplugin")
254 let b:undo_ftplugin = "setl comments< formatoptions< define< omnifunc<" .
255 \ " | unlet! b:browsefilter b:match_words"
257 " Don't load another plugin for this buffer
258 let b:did_ftplugin = 1
259 let b:current_ftplugin = 'sql'
261 " Win32 can filter files in the browse dialog
262 if has("gui_win32") && !exists("b:browsefilter")
263 let b:browsefilter = "SQL Files (*.sql)\t*.sql\n" .
264 \ "All Files (*.*)\t*.*\n"
267 " Some standard expressions for use with the matchit strings
268 let s:notend = '\%(\<end\s\+\)\@<!'
269 let s:when_no_matched_or_others = '\%(\<when\>\%(\s\+\%(\%(\<not\>\s\+\)\?<matched\>\)\|\<others\>\)\@!\)'
270 let s:or_replace = '\%(or\s\+replace\s\+\)\?'
272 " Define patterns for the matchit macro
273 if !exists("b:match_words")
274 " SQL is generally case insensitive
275 let b:match_ignorecase = 1
277 " Handle the following:
283 " [while condition] loop
312 " WHEN column_not_found THEN
315 " create[ or replace] procedure|function|event
316 " \ '^\s*\<\%(do\|for\|while\|loop\)\>.*:'.
318 " For ColdFusion support
319 setlocal matchpairs+=<:>
320 let b:match_words = &matchpairs .
321 \ ',\<begin\>:\<end\>\W*$,'.
323 \ s:notend . '\<if\>:'.
324 \ '\<elsif\>\|\<elseif\>\|\<else\>:'.
327 \ '\(^\s*\)\@<=\(\<\%(do\|for\|while\|loop\)\>.*\):'.
328 \ '\%(\<exit\>\|\<leave\>\|\<break\>\|\<continue\>\):'.
329 \ '\%(\<doend\>\|\%(\<end\s\+\%(for\|while\|loop\>\)\)\),'.
331 \ '\%('. s:notend . '\<case\>\):'.
332 \ '\%('.s:when_no_matched_or_others.'\):'.
333 \ '\%(\<when\s\+others\>\|\<end\s\+case\>\),' .
336 \ '\<when\s\+not\s\+matched\>:' .
337 \ '\<when\s\+matched\>,' .
339 \ '\%(\<create\s\+' . s:or_replace . '\)\?'.
340 \ '\%(function\|procedure\|event\):'.
342 " \ '\<begin\>\|\<returns\?\>:'.
343 " \ '\<end\>\(;\)\?\s*$'
344 " \ '\<exception\>:'.s:when_no_matched_or_others.
345 " \ ':\<when\s\+others\>,'.
347 " \ '\%(\<exception\>\|\%('. s:notend . '\<case\>\)\):'.
348 " \ '\%(\<default\>\|'.s:when_no_matched_or_others.'\):'.
349 " \ '\%(\%(\<when\s\+others\>\)\|\<end\s\+case\>\),' .
352 " Define how to find the macro definition of a variable using the various
353 " [d, [D, [_CTRL_D and so on features
354 " Match these values ignoring case
355 " ie DECLARE varname INTEGER
356 let &l:define = '\c\<\(VARIABLE\|DECLARE\|IN\|OUT\|INOUT\)\>'
359 " Mappings to move to the next BEGIN ... END block
360 " \W - no characters or digits
361 nnoremap <buffer> <silent> ]] :call search('\c^\s*begin\>', 'W' )<CR>
362 nnoremap <buffer> <silent> [[ :call search('\c^\s*begin\>', 'bW' )<CR>
363 nnoremap <buffer> <silent> ][ :call search('\c^\s*end\W*$', 'W' )<CR>
364 nnoremap <buffer> <silent> [] :call search('\c^\s*end\W*$', 'bW' )<CR>
365 xnoremap <buffer> <silent> ]] :<C-U>exec "normal! gv"<Bar>call search('\c^\s*begin\>', 'W' )<CR>
366 xnoremap <buffer> <silent> [[ :<C-U>exec "normal! gv"<Bar>call search('\c^\s*begin\>', 'bW' )<CR>
367 xnoremap <buffer> <silent> ][ :<C-U>exec "normal! gv"<Bar>call search('\c^\s*end\W*$', 'W' )<CR>
368 xnoremap <buffer> <silent> [] :<C-U>exec "normal! gv"<Bar>call search('\c^\s*end\W*$', 'bW' )<CR>
371 " By default only look for CREATE statements, but allow
372 " the user to override
373 if !exists('g:ftplugin_sql_statements')
374 let g:ftplugin_sql_statements = 'create'
377 " Predefined SQL objects what are used by the below mappings using
379 " This global variable allows the users to override it's value
380 " from within their vimrc.
381 " Note, you cannot use \?, since these patterns can be used to search
382 " backwards, you must use \{,1}
383 if !exists('g:ftplugin_sql_objects')
384 let g:ftplugin_sql_objects = 'function,procedure,event,' .
385 \ '\(existing\\|global\s\+temporary\s\+\)\{,1}' .
387 \ ',schema,service,publication,database,datatype,domain' .
388 \ ',index,subscription,synchronization,view,variable'
391 " Key to trigger SQL completion
392 if !exists('g:ftplugin_sql_omni_key')
393 let g:ftplugin_sql_omni_key = '<C-C>'
395 " Key to trigger drill into column list
396 if !exists('g:ftplugin_sql_omni_key_right')
397 let g:ftplugin_sql_omni_key_right = '<Right>'
399 " Key to trigger drill out of column list
400 if !exists('g:ftplugin_sql_omni_key_left')
401 let g:ftplugin_sql_omni_key_left = '<Left>'
404 " Replace all ,'s with bars, except ones with numbers after them.
405 " This will most likely be a \{,1} string.
406 let s:ftplugin_sql_objects =
409 \ substitute(g:ftplugin_sql_statements, ',\d\@!', '\\\\|', 'g') .
410 \ '\)\s\+\(or\s\+replace\s\+\)\{,1}\)\{,1}' .
412 \ substitute(g:ftplugin_sql_objects, ',\d\@!', '\\\\|', 'g') .
415 " Mappings to move to the next CREATE ... block
416 exec "nnoremap <buffer> <silent> ]} :call search('".s:ftplugin_sql_objects."', 'W')<CR>"
417 exec "nnoremap <buffer> <silent> [{ :call search('".s:ftplugin_sql_objects."', 'bW')<CR>"
418 " Could not figure out how to use a :call search() string in visual mode
419 " without it ending visual mode
420 " Unfortunately, this will add a entry to the search history
421 exec 'xnoremap <buffer> <silent> ]} /'.s:ftplugin_sql_objects.'<CR>'
422 exec 'xnoremap <buffer> <silent> [{ ?'.s:ftplugin_sql_objects.'<CR>'
424 " Mappings to move to the next COMMENT
426 " Had to double the \ for the \| separator since this has a special
428 let b:comment_leader = '\(--\\|\/\/\\|\*\\|\/\*\\|\*\/\)'
429 " Find the start of the next comment
430 let b:comment_start = '^\(\s*'.b:comment_leader.'.*\n\)\@<!'.
431 \ '\(\s*'.b:comment_leader.'\)'
432 " Find the end of the previous comment
433 let b:comment_end = '\(^\s*'.b:comment_leader.'.*\n\)'.
434 \ '\(^\s*'.b:comment_leader.'\)\@!'
435 " Skip over the comment
436 let b:comment_jump_over = "call search('".
437 \ '^\(\s*'.b:comment_leader.'.*\n\)\@<!'.
439 let b:comment_skip_back = "call search('".
440 \ '^\(\s*'.b:comment_leader.'.*\n\)\@<!'.
442 " Move to the start and end of comments
443 exec 'nnoremap <silent><buffer> ]" :call search('."'".b:comment_start."'".', "W" )<CR>'
444 exec 'nnoremap <silent><buffer> [" :call search('."'".b:comment_end."'".', "W" )<CR>'
445 exec 'xnoremap <silent><buffer> ]" :<C-U>exec "normal! gv"<Bar>call search('."'".b:comment_start."'".', "W" )<CR>'
446 exec 'xnoremap <silent><buffer> [" :<C-U>exec "normal! gv"<Bar>call search('."'".b:comment_end."'".', "W" )<CR>'
448 " Comments can be of the form:
456 setlocal comments=s1:/*,mb:*,ex:*/,:--,://
458 " Set completion with CTRL-X CTRL-O to autoloaded function.
459 if exists('&omnifunc')
460 " Since the SQL completion plugin can be used in conjunction
461 " with other completion filetypes it must record the previous
462 " OMNI function prior to setting up the SQL OMNI function
463 let b:sql_compl_savefunc = &omnifunc
465 " Source it to determine it's version
466 runtime autoload/sqlcomplete.vim
467 " This is used by the sqlcomplete.vim plugin
468 " Source it for it's global functions
469 runtime autoload/syntaxcomplete.vim
471 setlocal omnifunc=sqlcomplete#Complete
472 " Prevent the intellisense plugin from loading
474 if !exists('g:omni_sql_no_default_maps')
476 if exists('g:loaded_syntax_completion') && exists('g:loaded_sql_completion')
477 if g:loaded_syntax_completion > 120 && g:loaded_sql_completion > 140
478 let regex_extra = '\\w*'
481 " Static maps which use populate the completion list
482 " using Vim's syntax highlighting rules
483 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'a <C-\><C-O>:call sqlcomplete#Map("syntax")<CR><C-X><C-O>'
484 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'k <C-\><C-O>:call sqlcomplete#Map("sqlKeyword'.regex_extra.'")<CR><C-X><C-O>'
485 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'f <C-\><C-O>:call sqlcomplete#Map("sqlFunction'.regex_extra.'")<CR><C-X><C-O>'
486 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'o <C-\><C-O>:call sqlcomplete#Map("sqlOption'.regex_extra.'")<CR><C-X><C-O>'
487 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'T <C-\><C-O>:call sqlcomplete#Map("sqlType'.regex_extra.'")<CR><C-X><C-O>'
488 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'s <C-\><C-O>:call sqlcomplete#Map("sqlStatement'.regex_extra.'")<CR><C-X><C-O>'
489 " Dynamic maps which use populate the completion list
490 " using the dbext.vim plugin
491 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'t <C-\><C-O>:call sqlcomplete#Map("table")<CR><C-X><C-O>'
492 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'p <C-\><C-O>:call sqlcomplete#Map("procedure")<CR><C-X><C-O>'
493 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'v <C-\><C-O>:call sqlcomplete#Map("view")<CR><C-X><C-O>'
494 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'c <C-\><C-O>:call sqlcomplete#Map("column")<CR><C-X><C-O>'
495 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'l <C-\><C-O>:call sqlcomplete#Map("column_csv")<CR><C-X><C-O>'
496 " The next 3 maps are only to be used while the completion window is
497 " active due to the <CR> at the beginning of the map
498 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'L <C-Y><C-\><C-O>:call sqlcomplete#Map("column_csv")<CR><C-X><C-O>'
499 " <C-Right> is not recognized on most Unix systems, so only create
500 " these additional maps on the Windows platform.
501 " If you would like to use these maps, choose a different key and make
502 " the same map in your vimrc.
504 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key_right.' <C-R>=sqlcomplete#DrillIntoTable()<CR>'
505 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key_left.' <C-R>=sqlcomplete#DrillOutOfColumns()<CR>'
507 " Remove any cached items useful for schema changes
508 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'R <C-\><C-O>:call sqlcomplete#Map("resetCache")<CR><C-X><C-O>'
511 if b:sql_compl_savefunc != ""
512 " We are changing the filetype to SQL from some other filetype
513 " which had OMNI completion defined. We need to activate the
514 " SQL completion plugin in order to cache some of the syntax items
515 " while the syntax rules for SQL are active.
516 call sqlcomplete#ResetCacheSyntax()
517 call sqlcomplete#PreCacheSyntax()
521 let &cpo = s:save_cpo