1 " SQL filetype plugin file
2 " Language: SQL (Common for Oracle, Microsoft SQL Server, Sybase)
4 " Maintainer: David Fishburn <fishburn at ianywhere dot com>
5 " Last Change: 2010 Jun 11
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: Calls the sqlcomplete#ResetCacheSyntax() function when calling
46 " NF: Adds the command SQLGetType
50 " NF: Adds the ability to choose the keys to control SQL completion, just add
51 " the following to your .vimrc:
52 " let g:ftplugin_sql_omni_key = '<C-C>'
53 " let g:ftplugin_sql_omni_key_right = '<Right>'
54 " let g:ftplugin_sql_omni_key_left = '<Left>'
56 " BF: format-options - Auto-wrap comments using textwidth was turned off
60 " Only do this when not done yet for this buffer
61 if exists("b:did_ftplugin")
68 " Disable autowrapping for code, but enable for comments
69 " t Auto-wrap text using textwidth
70 " c Auto-wrap comments using textwidth, inserting the current comment
71 " leader automatically.
72 setlocal formatoptions-=t
73 setlocal formatoptions+=c
75 " Functions/Commands to allow the user to change SQL syntax dialects
76 " through the use of :SQLSetType <tab> for completion.
77 " This works with both Vim 6 and 7.
79 if !exists("*SQL_SetType")
80 " NOTE: You cannot use function! since this file can be
81 " sourced from within this function. That will result in
82 " an error reported by Vim.
83 function SQL_GetList(ArgLead, CmdLine, CursorPos)
85 if !exists('s:sql_list')
86 " Grab a list of files that contain "sql" in their names
87 let list_indent = globpath(&runtimepath, 'indent/*sql*')
88 let list_syntax = globpath(&runtimepath, 'syntax/*sql*')
89 let list_ftplugin = globpath(&runtimepath, 'ftplugin/*sql*')
91 let sqls = "\n".list_indent."\n".list_syntax."\n".list_ftplugin."\n"
93 " Strip out everything (path info) but the filename
95 " From between two newline characters
96 " Non-greedily grab all characters
97 " Followed by a valid filename \w\+\.\w\+ (sql.vim)
98 " Followed by a newline, but do not include the newline
100 " Replace it with just the filename (get rid of PATH)
102 " Recursively, since there are many filenames that contain
103 " the word SQL in the indent, syntax and ftplugin directory
104 let sqls = substitute( sqls,
105 \ '[\n]\%(.\{-}\)\(\w\+\.\w\+\)\n\@=',
110 " Remove duplicates, since sqlanywhere.vim can exist in the
111 " sytax, indent and ftplugin directory, yet we only want
112 " to display the option once
113 let index = match(sqls, '.\{-}\ze\n')
115 " Get the first filename
116 let file = matchstr(sqls, '.\{-}\ze\n', index)
117 " Recursively replace any *other* occurrence of that
118 " filename with nothing (ie remove it)
119 let sqls = substitute(sqls, '\%>'.(index+strlen(file)).'c\<'.file.'\>\n', '', 'g')
120 " Move on to the next filename
121 let index = match(sqls, '.\{-}\ze\n', (index+strlen(file)+1))
124 " Sort the list if using version 7
126 let mylist = split(sqls, "\n")
127 let mylist = sort(mylist)
128 let sqls = join(mylist, "\n")
131 let s:sql_list = sqls
138 function SQL_SetType(name)
140 " User has decided to override default SQL scripts and
141 " specify a vendor specific version
142 " (ie Oracle, Informix, SQL Anywhere, ...)
143 " So check for an remove any settings that prevent the
144 " scripts from being executed, and then source the
145 " appropriate Vim scripts.
146 if exists("b:did_ftplugin")
149 if exists("b:current_syntax")
150 " echomsg 'SQLSetType - clearing syntax'
153 if exists("b:did_indent")
154 " echomsg 'SQLSetType - clearing indent'
156 " Set these values to their defaults
161 " Ensure the name is in the correct format
162 let new_sql_type = substitute(a:name,
163 \ '\s*\([^\.]\+\)\(\.\w\+\)\?', '\L\1', '')
165 " Do not specify a buffer local variable if it is
167 if new_sql_type == 'sql'
168 let new_sql_type = 'sqloracle'
170 let b:sql_type_override = new_sql_type
172 " Remove any cached SQL since a new sytax will have different
174 if !exists('g:loaded_sql_completion') || 100 == g:loaded_sql_completion
175 call sqlcomplete#ResetCacheSyntax()
178 " Vim will automatically source the correct files if we
179 " change the filetype. You cannot do this with setfiletype
180 " since that command will only execute if a filetype has
181 " not already been set. In this case we want to override
182 " the existing filetype.
183 let &filetype = 'sql'
185 if b:sql_compl_savefunc != ""
186 " We are changing the filetype to SQL from some other filetype
187 " which had OMNI completion defined. We need to activate the
188 " SQL completion plugin in order to cache some of the syntax items
189 " while the syntax rules for SQL are active.
190 call sqlcomplete#PreCacheSyntax()
193 command! -nargs=* -complete=custom,SQL_GetList SQLSetType :call SQL_SetType(<q-args>)
197 " Functions/Commands to allow the user determine current SQL syntax dialect
198 " This works with both Vim 6 and 7.
200 if !exists("*SQL_GetType")
201 function SQL_GetType()
202 if exists('b:sql_type_override')
203 echomsg "Current SQL dialect in use:".b:sql_type_override
205 echomsg "Current SQL dialect in use:".g:sql_type_default
208 command! -nargs=0 SQLGetType :call SQL_GetType()
211 if exists("b:sql_type_override")
212 " echo 'sourcing buffer ftplugin/'.b:sql_type_override.'.vim'
213 if globpath(&runtimepath, 'ftplugin/'.b:sql_type_override.'.vim') != ''
214 exec 'runtime ftplugin/'.b:sql_type_override.'.vim'
216 " echomsg 'ftplugin/'.b:sql_type_override.' not exist, using default'
218 elseif exists("g:sql_type_default")
219 " echo 'sourcing global ftplugin/'.g:sql_type_default.'.vim'
220 if globpath(&runtimepath, 'ftplugin/'.g:sql_type_default.'.vim') != ''
221 exec 'runtime ftplugin/'.g:sql_type_default.'.vim'
223 " echomsg 'ftplugin/'.g:sql_type_default.'.vim not exist, using default'
227 " If the above runtime command succeeded, do not load the default settings
228 if exists("b:did_ftplugin")
232 let b:undo_ftplugin = "setl comments<"
234 " Don't load another plugin for this buffer
235 let b:did_ftplugin = 1
236 let b:current_ftplugin = 'sql'
238 " Win32 can filter files in the browse dialog
239 if has("gui_win32") && !exists("b:browsefilter")
240 let b:browsefilter = "SQL Files (*.sql)\t*.sql\n" .
241 \ "All Files (*.*)\t*.*\n"
244 " Some standard expressions for use with the matchit strings
245 let s:notend = '\%(\<end\s\+\)\@<!'
246 let s:when_no_matched_or_others = '\%(\<when\>\%(\s\+\%(\%(\<not\>\s\+\)\?<matched\>\)\|\<others\>\)\@!\)'
247 let s:or_replace = '\%(or\s\+replace\s\+\)\?'
249 " Define patterns for the matchit macro
250 if !exists("b:match_words")
251 " SQL is generally case insensitive
252 let b:match_ignorecase = 1
254 " Handle the following:
260 " [while condition] loop
289 " WHEN column_not_found THEN
292 " create[ or replace] procedure|function|event
295 \ '\<begin\>:\<end\>\W*$,'.
297 \ s:notend . '\<if\>:'.
298 \ '\<elsif\>\|\<elseif\>\|\<else\>:'.
303 \ '\%(' . s:notend . '\<loop\>\)\|'.
304 \ '\%(' . s:notend . '\<for\>\):'.
305 \ '\<exit\>\|\<leave\>\|\<break\>\|\<continue\>:'.
306 \ '\%(\<end\s\+\%(for\|loop\>\)\)\|\<doend\>,'.
308 \ '\%('. s:notend . '\<case\>\):'.
309 \ '\%('.s:when_no_matched_or_others.'\):'.
310 \ '\%(\<when\s\+others\>\|\<end\s\+case\>\),' .
313 \ '\<when\s\+not\s\+matched\>:' .
314 \ '\<when\s\+matched\>,' .
316 \ '\%(\<create\s\+' . s:or_replace . '\)\?'.
317 \ '\%(function\|procedure\|event\):'.
319 " \ '\<begin\>\|\<returns\?\>:'.
320 " \ '\<end\>\(;\)\?\s*$'
321 " \ '\<exception\>:'.s:when_no_matched_or_others.
322 " \ ':\<when\s\+others\>,'.
324 " \ '\%(\<exception\>\|\%('. s:notend . '\<case\>\)\):'.
325 " \ '\%(\<default\>\|'.s:when_no_matched_or_others.'\):'.
326 " \ '\%(\%(\<when\s\+others\>\)\|\<end\s\+case\>\),' .
329 " Define how to find the macro definition of a variable using the various
330 " [d, [D, [_CTRL_D and so on features
331 " Match these values ignoring case
332 " ie DECLARE varname INTEGER
333 let &l:define = '\c\<\(VARIABLE\|DECLARE\|IN\|OUT\|INOUT\)\>'
336 " Mappings to move to the next BEGIN ... END block
337 " \W - no characters or digits
338 nmap <buffer> <silent> ]] :call search('\\c^\\s*begin\\>', 'W' )<CR>
339 nmap <buffer> <silent> [[ :call search('\\c^\\s*begin\\>', 'bW' )<CR>
340 nmap <buffer> <silent> ][ :call search('\\c^\\s*end\\W*$', 'W' )<CR>
341 nmap <buffer> <silent> [] :call search('\\c^\\s*end\\W*$', 'bW' )<CR>
342 vmap <buffer> <silent> ]] :<C-U>exec "normal! gv"<Bar>call search('\\c^\\s*begin\\>', 'W' )<CR>
343 vmap <buffer> <silent> [[ :<C-U>exec "normal! gv"<Bar>call search('\\c^\\s*begin\\>', 'bW' )<CR>
344 vmap <buffer> <silent> ][ :<C-U>exec "normal! gv"<Bar>call search('\\c^\\s*end\\W*$', 'W' )<CR>
345 vmap <buffer> <silent> [] :<C-U>exec "normal! gv"<Bar>call search('\\c^\\s*end\\W*$', 'bW' )<CR>
348 " By default only look for CREATE statements, but allow
349 " the user to override
350 if !exists('g:ftplugin_sql_statements')
351 let g:ftplugin_sql_statements = 'create'
354 " Predefined SQL objects what are used by the below mappings using
356 " This global variable allows the users to override it's value
357 " from within their vimrc.
358 " Note, you cannot use \?, since these patterns can be used to search
359 " backwards, you must use \{,1}
360 if !exists('g:ftplugin_sql_objects')
361 let g:ftplugin_sql_objects = 'function,procedure,event,' .
362 \ '\\(existing\\\\|global\\s\\+temporary\\s\\+\\)\\\{,1}' .
364 \ ',schema,service,publication,database,datatype,domain' .
365 \ ',index,subscription,synchronization,view,variable'
368 " Key to trigger SQL completion
369 if !exists('g:ftplugin_sql_omni_key')
370 let g:ftplugin_sql_omni_key = '<C-C>'
372 " Key to trigger drill into column list
373 if !exists('g:ftplugin_sql_omni_key_right')
374 let g:ftplugin_sql_omni_key_right = '<Right>'
376 " Key to trigger drill out of column list
377 if !exists('g:ftplugin_sql_omni_key_left')
378 let g:ftplugin_sql_omni_key_left = '<Left>'
381 " Replace all ,'s with bars, except ones with numbers after them.
382 " This will most likely be a \{,1} string.
383 let s:ftplugin_sql_objects =
386 \ substitute(g:ftplugin_sql_statements, ',\d\@!', '\\\\\\|', 'g') .
387 \ '\\)\\s\\+\\(or\\s\\+replace\\\s\+\\)\\{,1}\\)\\{,1}' .
389 \ substitute(g:ftplugin_sql_objects, ',\d\@!', '\\\\\\|', 'g') .
392 " Mappings to move to the next CREATE ... block
393 exec "nmap <buffer> <silent> ]} :call search('".s:ftplugin_sql_objects."', 'W')<CR>"
394 exec "nmap <buffer> <silent> [{ :call search('".s:ftplugin_sql_objects."', 'bW')<CR>"
395 " Could not figure out how to use a :call search() string in visual mode
396 " without it ending visual mode
397 " Unfortunately, this will add a entry to the search history
398 exec 'vmap <buffer> <silent> ]} /'.s:ftplugin_sql_objects.'<CR>'
399 exec 'vmap <buffer> <silent> [{ ?'.s:ftplugin_sql_objects.'<CR>'
401 " Mappings to move to the next COMMENT
403 " Had to double the \ for the \| separator since this has a special
405 let b:comment_leader = '\\(--\\\|\\/\\/\\\|\\*\\\|\\/\\*\\\|\\*\\/\\)'
406 " Find the start of the next comment
407 let b:comment_start = '^\\(\\s*'.b:comment_leader.'.*\\n\\)\\@<!'.
408 \ '\\(\\s*'.b:comment_leader.'\\)'
409 " Find the end of the previous comment
410 let b:comment_end = '\\(^\\s*'.b:comment_leader.'.*\\n\\)'.
411 \ '\\(^\\s*'.b:comment_leader.'\\)\\@!'
412 " Skip over the comment
413 let b:comment_jump_over = "call search('".
414 \ '^\\(\\s*'.b:comment_leader.'.*\\n\\)\\@<!'.
416 let b:comment_skip_back = "call search('".
417 \ '^\\(\\s*'.b:comment_leader.'.*\\n\\)\\@<!'.
419 " Move to the start and end of comments
420 exec 'nnoremap <silent><buffer> ]" :call search('."'".b:comment_start."'".', "W" )<CR>'
421 exec 'nnoremap <silent><buffer> [" :call search('."'".b:comment_end."'".', "W" )<CR>'
422 exec 'vnoremap <silent><buffer> ]" :<C-U>exec "normal! gv"<Bar>call search('."'".b:comment_start."'".', "W" )<CR>'
423 exec 'vnoremap <silent><buffer> [" :<C-U>exec "normal! gv"<Bar>call search('."'".b:comment_end."'".', "W" )<CR>'
425 " Comments can be of the form:
433 setlocal comments=s1:/*,mb:*,ex:*/,:--,://
435 " Set completion with CTRL-X CTRL-O to autoloaded function.
436 if exists('&omnifunc')
437 " Since the SQL completion plugin can be used in conjunction
438 " with other completion filetypes it must record the previous
439 " OMNI function prior to setting up the SQL OMNI function
440 let b:sql_compl_savefunc = &omnifunc
442 " This is used by the sqlcomplete.vim plugin
443 " Source it for it's global functions
444 runtime autoload/syntaxcomplete.vim
446 setlocal omnifunc=sqlcomplete#Complete
447 " Prevent the intellisense plugin from loading
449 if !exists('g:omni_sql_no_default_maps')
450 " Static maps which use populate the completion list
451 " using Vim's syntax highlighting rules
452 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'a <C-\><C-O>:call sqlcomplete#Map("syntax")<CR><C-X><C-O>'
453 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'k <C-\><C-O>:call sqlcomplete#Map("sqlKeyword")<CR><C-X><C-O>'
454 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'f <C-\><C-O>:call sqlcomplete#Map("sqlFunction")<CR><C-X><C-O>'
455 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'o <C-\><C-O>:call sqlcomplete#Map("sqlOption")<CR><C-X><C-O>'
456 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'T <C-\><C-O>:call sqlcomplete#Map("sqlType")<CR><C-X><C-O>'
457 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'s <C-\><C-O>:call sqlcomplete#Map("sqlStatement")<CR><C-X><C-O>'
458 " Dynamic maps which use populate the completion list
459 " using the dbext.vim plugin
460 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'t <C-\><C-O>:call sqlcomplete#Map("table")<CR><C-X><C-O>'
461 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'p <C-\><C-O>:call sqlcomplete#Map("procedure")<CR><C-X><C-O>'
462 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'v <C-\><C-O>:call sqlcomplete#Map("view")<CR><C-X><C-O>'
463 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'c <C-\><C-O>:call sqlcomplete#Map("column")<CR><C-X><C-O>'
464 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'l <C-\><C-O>:call sqlcomplete#Map("column_csv")<CR><C-X><C-O>'
465 " The next 3 maps are only to be used while the completion window is
466 " active due to the <CR> at the beginning of the map
467 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'L <C-Y><C-\><C-O>:call sqlcomplete#Map("column_csv")<CR><C-X><C-O>'
468 " <C-Right> is not recognized on most Unix systems, so only create
469 " these additional maps on the Windows platform.
470 " If you would like to use these maps, choose a different key and make
471 " the same map in your vimrc.
473 exec 'imap <buffer> '.g:ftplugin_sql_omni_key_right.' <C-R>=sqlcomplete#DrillIntoTable()<CR>'
474 exec 'imap <buffer> '.g:ftplugin_sql_omni_key_left.' <C-R>=sqlcomplete#DrillOutOfColumns()<CR>'
476 " Remove any cached items useful for schema changes
477 exec 'imap <buffer> '.g:ftplugin_sql_omni_key.'R <C-\><C-O>:call sqlcomplete#Map("resetCache")<CR><C-X><C-O>'
480 if b:sql_compl_savefunc != ""
481 " We are changing the filetype to SQL from some other filetype
482 " which had OMNI completion defined. We need to activate the
483 " SQL completion plugin in order to cache some of the syntax items
484 " while the syntax rules for SQL are active.
485 call sqlcomplete#ResetCacheSyntax()
486 call sqlcomplete#PreCacheSyntax()
490 let &cpo = s:save_cpo