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: 2011 Apr 01
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.
41 " NF: Improved the matchit plugin regex (Talek)
45 " NF: Calls the sqlcomplete#ResetCacheSyntax() function when calling
50 " NF: Adds the command SQLGetType
54 " NF: Adds the ability to choose the keys to control SQL completion, just add
55 " the following to your .vimrc:
56 " let g:ftplugin_sql_omni_key = '<C-C>'
57 " let g:ftplugin_sql_omni_key_right = '<Right>'
58 " let g:ftplugin_sql_omni_key_left = '<Left>'
60 " BF: format-options - Auto-wrap comments using textwidth was turned off
64 " Only do this when not done yet for this buffer
65 if exists("b:did_ftplugin")
72 " Disable autowrapping for code, but enable for comments
73 " t Auto-wrap text using textwidth
74 " c Auto-wrap comments using textwidth, inserting the current comment
75 " leader automatically.
76 setlocal formatoptions-=t
77 setlocal formatoptions+=c
79 " Functions/Commands to allow the user to change SQL syntax dialects
80 " through the use of :SQLSetType <tab> for completion.
81 " This works with both Vim 6 and 7.
83 if !exists("*SQL_SetType")
84 " NOTE: You cannot use function! since this file can be
85 " sourced from within this function. That will result in
86 " an error reported by Vim.
87 function SQL_GetList(ArgLead, CmdLine, CursorPos)
89 if !exists('s:sql_list')
90 " Grab a list of files that contain "sql" in their names
91 let list_indent = globpath(&runtimepath, 'indent/*sql*')
92 let list_syntax = globpath(&runtimepath, 'syntax/*sql*')
93 let list_ftplugin = globpath(&runtimepath, 'ftplugin/*sql*')
95 let sqls = "\n".list_indent."\n".list_syntax."\n".list_ftplugin."\n"
97 " Strip out everything (path info) but the filename
99 " From between two newline characters
100 " Non-greedily grab all characters
101 " Followed by a valid filename \w\+\.\w\+ (sql.vim)
102 " Followed by a newline, but do not include the newline
104 " Replace it with just the filename (get rid of PATH)
106 " Recursively, since there are many filenames that contain
107 " the word SQL in the indent, syntax and ftplugin directory
108 let sqls = substitute( sqls,
109 \ '[\n]\%(.\{-}\)\(\w\+\.\w\+\)\n\@=',
114 " Remove duplicates, since sqlanywhere.vim can exist in the
115 " sytax, indent and ftplugin directory, yet we only want
116 " to display the option once
117 let index = match(sqls, '.\{-}\ze\n')
119 " Get the first filename
120 let file = matchstr(sqls, '.\{-}\ze\n', index)
121 " Recursively replace any *other* occurrence of that
122 " filename with nothing (ie remove it)
123 let sqls = substitute(sqls, '\%>'.(index+strlen(file)).'c\<'.file.'\>\n', '', 'g')
124 " Move on to the next filename
125 let index = match(sqls, '.\{-}\ze\n', (index+strlen(file)+1))
128 " Sort the list if using version 7
130 let mylist = split(sqls, "\n")
131 let mylist = sort(mylist)
132 let sqls = join(mylist, "\n")
135 let s:sql_list = sqls
142 function SQL_SetType(name)
144 " User has decided to override default SQL scripts and
145 " specify a vendor specific version
146 " (ie Oracle, Informix, SQL Anywhere, ...)
147 " So check for an remove any settings that prevent the
148 " scripts from being executed, and then source the
149 " appropriate Vim scripts.
150 if exists("b:did_ftplugin")
153 if exists("b:current_syntax")
154 " echomsg 'SQLSetType - clearing syntax'
157 if exists("b:did_indent")
158 " echomsg 'SQLSetType - clearing indent'
160 " Set these values to their defaults
165 " Ensure the name is in the correct format
166 let new_sql_type = substitute(a:name,
167 \ '\s*\([^\.]\+\)\(\.\w\+\)\?', '\L\1', '')
169 " Do not specify a buffer local variable if it is
171 if new_sql_type == 'sql'
172 let new_sql_type = 'sqloracle'
174 let b:sql_type_override = new_sql_type
176 " Remove any cached SQL since a new sytax will have different
178 if !exists('g:loaded_sql_completion') || 100 == g:loaded_sql_completion
179 call sqlcomplete#ResetCacheSyntax()
182 " Vim will automatically source the correct files if we
183 " change the filetype. You cannot do this with setfiletype
184 " since that command will only execute if a filetype has
185 " not already been set. In this case we want to override
186 " the existing filetype.
187 let &filetype = 'sql'
189 if b:sql_compl_savefunc != ""
190 " We are changing the filetype to SQL from some other filetype
191 " which had OMNI completion defined. We need to activate the
192 " SQL completion plugin in order to cache some of the syntax items
193 " while the syntax rules for SQL are active.
194 call sqlcomplete#PreCacheSyntax()
197 command! -nargs=* -complete=custom,SQL_GetList SQLSetType :call SQL_SetType(<q-args>)
201 " Functions/Commands to allow the user determine current SQL syntax dialect
202 " This works with both Vim 6 and 7.
204 if !exists("*SQL_GetType")
205 function SQL_GetType()
206 if exists('b:sql_type_override')
207 echomsg "Current SQL dialect in use:".b:sql_type_override
209 echomsg "Current SQL dialect in use:".g:sql_type_default
212 command! -nargs=0 SQLGetType :call SQL_GetType()
215 if exists("b:sql_type_override")
216 " echo 'sourcing buffer ftplugin/'.b:sql_type_override.'.vim'
217 if globpath(&runtimepath, 'ftplugin/'.b:sql_type_override.'.vim') != ''
218 exec 'runtime ftplugin/'.b:sql_type_override.'.vim'
220 " echomsg 'ftplugin/'.b:sql_type_override.' not exist, using default'
222 elseif exists("g:sql_type_default")
223 " echo 'sourcing global ftplugin/'.g:sql_type_default.'.vim'
224 if globpath(&runtimepath, 'ftplugin/'.g:sql_type_default.'.vim') != ''
225 exec 'runtime ftplugin/'.g:sql_type_default.'.vim'
227 " echomsg 'ftplugin/'.g:sql_type_default.'.vim not exist, using default'
231 " If the above runtime command succeeded, do not load the default settings
232 if exists("b:did_ftplugin")
236 let b:undo_ftplugin = "setl comments<"
238 " Don't load another plugin for this buffer
239 let b:did_ftplugin = 1
240 let b:current_ftplugin = 'sql'
242 " Win32 can filter files in the browse dialog
243 if has("gui_win32") && !exists("b:browsefilter")
244 let b:browsefilter = "SQL Files (*.sql)\t*.sql\n" .
245 \ "All Files (*.*)\t*.*\n"
248 " Some standard expressions for use with the matchit strings
249 let s:notend = '\%(\<end\s\+\)\@<!'
250 let s:when_no_matched_or_others = '\%(\<when\>\%(\s\+\%(\%(\<not\>\s\+\)\?<matched\>\)\|\<others\>\)\@!\)'
251 let s:or_replace = '\%(or\s\+replace\s\+\)\?'
253 " Define patterns for the matchit macro
254 if !exists("b:match_words")
255 " SQL is generally case insensitive
256 let b:match_ignorecase = 1
258 " Handle the following:
264 " [while condition] loop
293 " WHEN column_not_found THEN
296 " create[ or replace] procedure|function|event
297 " \ '^\s*\<\%(do\|for\|while\|loop\)\>.*:'.
300 \ '\<begin\>:\<end\>\W*$,'.
302 \ s:notend . '\<if\>:'.
303 \ '\<elsif\>\|\<elseif\>\|\<else\>:'.
306 \ '\(^\s*\)\@<=\(\<\%(do\|for\|while\|loop\)\>.*\):'.
307 \ '\%(\<exit\>\|\<leave\>\|\<break\>\|\<continue\>\):'.
308 \ '\%(\<doend\>\|\%(\<end\s\+\%(for\|while\|loop\>\)\)\),'.
310 \ '\%('. s:notend . '\<case\>\):'.
311 \ '\%('.s:when_no_matched_or_others.'\):'.
312 \ '\%(\<when\s\+others\>\|\<end\s\+case\>\),' .
315 \ '\<when\s\+not\s\+matched\>:' .
316 \ '\<when\s\+matched\>,' .
318 \ '\%(\<create\s\+' . s:or_replace . '\)\?'.
319 \ '\%(function\|procedure\|event\):'.
321 " \ '\<begin\>\|\<returns\?\>:'.
322 " \ '\<end\>\(;\)\?\s*$'
323 " \ '\<exception\>:'.s:when_no_matched_or_others.
324 " \ ':\<when\s\+others\>,'.
326 " \ '\%(\<exception\>\|\%('. s:notend . '\<case\>\)\):'.
327 " \ '\%(\<default\>\|'.s:when_no_matched_or_others.'\):'.
328 " \ '\%(\%(\<when\s\+others\>\)\|\<end\s\+case\>\),' .
331 " Define how to find the macro definition of a variable using the various
332 " [d, [D, [_CTRL_D and so on features
333 " Match these values ignoring case
334 " ie DECLARE varname INTEGER
335 let &l:define = '\c\<\(VARIABLE\|DECLARE\|IN\|OUT\|INOUT\)\>'
338 " Mappings to move to the next BEGIN ... END block
339 " \W - no characters or digits
340 nmap <buffer> <silent> ]] :call search('\\c^\\s*begin\\>', 'W' )<CR>
341 nmap <buffer> <silent> [[ :call search('\\c^\\s*begin\\>', 'bW' )<CR>
342 nmap <buffer> <silent> ][ :call search('\\c^\\s*end\\W*$', 'W' )<CR>
343 nmap <buffer> <silent> [] :call search('\\c^\\s*end\\W*$', 'bW' )<CR>
344 vmap <buffer> <silent> ]] :<C-U>exec "normal! gv"<Bar>call search('\\c^\\s*begin\\>', 'W' )<CR>
345 vmap <buffer> <silent> [[ :<C-U>exec "normal! gv"<Bar>call search('\\c^\\s*begin\\>', 'bW' )<CR>
346 vmap <buffer> <silent> ][ :<C-U>exec "normal! gv"<Bar>call search('\\c^\\s*end\\W*$', 'W' )<CR>
347 vmap <buffer> <silent> [] :<C-U>exec "normal! gv"<Bar>call search('\\c^\\s*end\\W*$', 'bW' )<CR>
350 " By default only look for CREATE statements, but allow
351 " the user to override
352 if !exists('g:ftplugin_sql_statements')
353 let g:ftplugin_sql_statements = 'create'
356 " Predefined SQL objects what are used by the below mappings using
358 " This global variable allows the users to override it's value
359 " from within their vimrc.
360 " Note, you cannot use \?, since these patterns can be used to search
361 " backwards, you must use \{,1}
362 if !exists('g:ftplugin_sql_objects')
363 let g:ftplugin_sql_objects = 'function,procedure,event,' .
364 \ '\\(existing\\\\|global\\s\\+temporary\\s\\+\\)\\\{,1}' .
366 \ ',schema,service,publication,database,datatype,domain' .
367 \ ',index,subscription,synchronization,view,variable'
370 " Key to trigger SQL completion
371 if !exists('g:ftplugin_sql_omni_key')
372 let g:ftplugin_sql_omni_key = '<C-C>'
374 " Key to trigger drill into column list
375 if !exists('g:ftplugin_sql_omni_key_right')
376 let g:ftplugin_sql_omni_key_right = '<Right>'
378 " Key to trigger drill out of column list
379 if !exists('g:ftplugin_sql_omni_key_left')
380 let g:ftplugin_sql_omni_key_left = '<Left>'
383 " Replace all ,'s with bars, except ones with numbers after them.
384 " This will most likely be a \{,1} string.
385 let s:ftplugin_sql_objects =
388 \ substitute(g:ftplugin_sql_statements, ',\d\@!', '\\\\\\|', 'g') .
389 \ '\\)\\s\\+\\(or\\s\\+replace\\\s\+\\)\\{,1}\\)\\{,1}' .
391 \ substitute(g:ftplugin_sql_objects, ',\d\@!', '\\\\\\|', 'g') .
394 " Mappings to move to the next CREATE ... block
395 exec "nmap <buffer> <silent> ]} :call search('".s:ftplugin_sql_objects."', 'W')<CR>"
396 exec "nmap <buffer> <silent> [{ :call search('".s:ftplugin_sql_objects."', 'bW')<CR>"
397 " Could not figure out how to use a :call search() string in visual mode
398 " without it ending visual mode
399 " Unfortunately, this will add a entry to the search history
400 exec 'vmap <buffer> <silent> ]} /'.s:ftplugin_sql_objects.'<CR>'
401 exec 'vmap <buffer> <silent> [{ ?'.s:ftplugin_sql_objects.'<CR>'
403 " Mappings to move to the next COMMENT
405 " Had to double the \ for the \| separator since this has a special
407 let b:comment_leader = '\\(--\\\|\\/\\/\\\|\\*\\\|\\/\\*\\\|\\*\\/\\)'
408 " Find the start of the next comment
409 let b:comment_start = '^\\(\\s*'.b:comment_leader.'.*\\n\\)\\@<!'.
410 \ '\\(\\s*'.b:comment_leader.'\\)'
411 " Find the end of the previous comment
412 let b:comment_end = '\\(^\\s*'.b:comment_leader.'.*\\n\\)'.
413 \ '\\(^\\s*'.b:comment_leader.'\\)\\@!'
414 " Skip over the comment
415 let b:comment_jump_over = "call search('".
416 \ '^\\(\\s*'.b:comment_leader.'.*\\n\\)\\@<!'.
418 let b:comment_skip_back = "call search('".
419 \ '^\\(\\s*'.b:comment_leader.'.*\\n\\)\\@<!'.
421 " Move to the start and end of comments
422 exec 'nnoremap <silent><buffer> ]" :call search('."'".b:comment_start."'".', "W" )<CR>'
423 exec 'nnoremap <silent><buffer> [" :call search('."'".b:comment_end."'".', "W" )<CR>'
424 exec 'vnoremap <silent><buffer> ]" :<C-U>exec "normal! gv"<Bar>call search('."'".b:comment_start."'".', "W" )<CR>'
425 exec 'vnoremap <silent><buffer> [" :<C-U>exec "normal! gv"<Bar>call search('."'".b:comment_end."'".', "W" )<CR>'
427 " Comments can be of the form:
435 setlocal comments=s1:/*,mb:*,ex:*/,:--,://
437 " Set completion with CTRL-X CTRL-O to autoloaded function.
438 if exists('&omnifunc')
439 " Since the SQL completion plugin can be used in conjunction
440 " with other completion filetypes it must record the previous
441 " OMNI function prior to setting up the SQL OMNI function
442 let b:sql_compl_savefunc = &omnifunc
444 " This is used by the sqlcomplete.vim plugin
445 " Source it for it's global functions
446 runtime autoload/syntaxcomplete.vim
448 setlocal omnifunc=sqlcomplete#Complete
449 " Prevent the intellisense plugin from loading
451 if !exists('g:omni_sql_no_default_maps')
452 " Static maps which use populate the completion list
453 " using Vim's syntax highlighting rules
454 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'a <C-\><C-O>:call sqlcomplete#Map("syntax")<CR><C-X><C-O>'
455 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'k <C-\><C-O>:call sqlcomplete#Map("sqlKeyword")<CR><C-X><C-O>'
456 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'f <C-\><C-O>:call sqlcomplete#Map("sqlFunction")<CR><C-X><C-O>'
457 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'o <C-\><C-O>:call sqlcomplete#Map("sqlOption")<CR><C-X><C-O>'
458 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'T <C-\><C-O>:call sqlcomplete#Map("sqlType")<CR><C-X><C-O>'
459 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'s <C-\><C-O>:call sqlcomplete#Map("sqlStatement")<CR><C-X><C-O>'
460 " Dynamic maps which use populate the completion list
461 " using the dbext.vim plugin
462 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'t <C-\><C-O>:call sqlcomplete#Map("table")<CR><C-X><C-O>'
463 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'p <C-\><C-O>:call sqlcomplete#Map("procedure")<CR><C-X><C-O>'
464 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'v <C-\><C-O>:call sqlcomplete#Map("view")<CR><C-X><C-O>'
465 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'c <C-\><C-O>:call sqlcomplete#Map("column")<CR><C-X><C-O>'
466 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'l <C-\><C-O>:call sqlcomplete#Map("column_csv")<CR><C-X><C-O>'
467 " The next 3 maps are only to be used while the completion window is
468 " active due to the <CR> at the beginning of the map
469 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'L <C-Y><C-\><C-O>:call sqlcomplete#Map("column_csv")<CR><C-X><C-O>'
470 " <C-Right> is not recognized on most Unix systems, so only create
471 " these additional maps on the Windows platform.
472 " If you would like to use these maps, choose a different key and make
473 " the same map in your vimrc.
475 exec 'imap <buffer> '.g:ftplugin_sql_omni_key_right.' <C-R>=sqlcomplete#DrillIntoTable()<CR>'
476 exec 'imap <buffer> '.g:ftplugin_sql_omni_key_left.' <C-R>=sqlcomplete#DrillOutOfColumns()<CR>'
478 " Remove any cached items useful for schema changes
479 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'R <C-\><C-O>:call sqlcomplete#Map("resetCache")<CR><C-X><C-O>'
482 if b:sql_compl_savefunc != ""
483 " We are changing the filetype to SQL from some other filetype
484 " which had OMNI completion defined. We need to activate the
485 " SQL completion plugin in order to cache some of the syntax items
486 " while the syntax rules for SQL are active.
487 call sqlcomplete#ResetCacheSyntax()
488 call sqlcomplete#PreCacheSyntax()
492 let &cpo = s:save_cpo