1" SQL filetype plugin file 2" Language: SQL (Common for Oracle, Microsoft SQL Server, Sybase) 3" Version: 10.0 4" Maintainer: David Fishburn <dfishburn dot vim at gmail dot com> 5" Last Change: 2012 Dec 04 6" Download: http://vim.sourceforge.net/script.php?script_id=454 7 8" For more details please use: 9" :h sql.txt 10" 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. 17 18 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 24" 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: 28" :SQLSetType <tab> 29" 30" To change the default dialect, add the following to your vimrc: 31" let g:sql_type_default = 'sqlanywhere' 32" 33" This file also creates a command, SQLGetType, which allows you to 34" determine what the current dialect is in use. 35" :SQLGetType 36" 37" History 38" 39" Version 10.0 (Dec 2012) 40" 41" NF: Changed all maps to use noremap instead of must map 42" NF: Changed all visual maps to use xnoremap instead of vnoremap as they 43" should only be used in visual mode and not select mode. 44" BF: Most of the maps were using doubled up backslashes before they were 45" changed to using the search() function, which meant they no longer 46" worked. 47" 48" Version 9.0 49" 50" NF: Completes 'b:undo_ftplugin' 51" BF: Correctly set cpoptions when creating script 52" 53" Version 8.0 54" 55" NF: Improved the matchit plugin regex (Talek) 56" 57" Version 7.0 58" 59" NF: Calls the sqlcomplete#ResetCacheSyntax() function when calling 60" SQLSetType. 61" 62" Version 6.0 63" 64" NF: Adds the command SQLGetType 65" 66" Version 5.0 67" 68" NF: Adds the ability to choose the keys to control SQL completion, just add 69" the following to your .vimrc: 70" let g:ftplugin_sql_omni_key = '<C-C>' 71" let g:ftplugin_sql_omni_key_right = '<Right>' 72" let g:ftplugin_sql_omni_key_left = '<Left>' 73" 74" BF: format-options - Auto-wrap comments using textwidth was turned off 75" by mistake. 76 77 78" Only do this when not done yet for this buffer 79if exists("b:did_ftplugin") 80 finish 81endif 82 83let s:save_cpo = &cpo 84set cpo&vim 85 86" Disable autowrapping for code, but enable for comments 87" t Auto-wrap text using textwidth 88" c Auto-wrap comments using textwidth, inserting the current comment 89" leader automatically. 90setlocal formatoptions-=t 91setlocal formatoptions+=c 92 93" Functions/Commands to allow the user to change SQL syntax dialects 94" through the use of :SQLSetType <tab> for completion. 95" This works with both Vim 6 and 7. 96 97if !exists("*SQL_SetType") 98 " NOTE: You cannot use function! since this file can be 99 " sourced from within this function. That will result in 100 " an error reported by Vim. 101 function SQL_GetList(ArgLead, CmdLine, CursorPos) 102 103 if !exists('s:sql_list') 104 " Grab a list of files that contain "sql" in their names 105 let list_indent = globpath(&runtimepath, 'indent/*sql*') 106 let list_syntax = globpath(&runtimepath, 'syntax/*sql*') 107 let list_ftplugin = globpath(&runtimepath, 'ftplugin/*sql*') 108 109 let sqls = "\n".list_indent."\n".list_syntax."\n".list_ftplugin."\n" 110 111 " Strip out everything (path info) but the filename 112 " Regex 113 " From between two newline characters 114 " Non-greedily grab all characters 115 " Followed by a valid filename \w\+\.\w\+ (sql.vim) 116 " Followed by a newline, but do not include the newline 117 " 118 " Replace it with just the filename (get rid of PATH) 119 " 120 " Recursively, since there are many filenames that contain 121 " the word SQL in the indent, syntax and ftplugin directory 122 let sqls = substitute( sqls, 123 \ '[\n]\%(.\{-}\)\(\w\+\.\w\+\)\n\@=', 124 \ '\1\n', 125 \ 'g' 126 \ ) 127 128 " Remove duplicates, since sqlanywhere.vim can exist in the 129 " sytax, indent and ftplugin directory, yet we only want 130 " to display the option once 131 let index = match(sqls, '.\{-}\ze\n') 132 while index > -1 133 " Get the first filename 134 let file = matchstr(sqls, '.\{-}\ze\n', index) 135 " Recursively replace any *other* occurrence of that 136 " filename with nothing (ie remove it) 137 let sqls = substitute(sqls, '\%>'.(index+strlen(file)).'c\<'.file.'\>\n', '', 'g') 138 " Move on to the next filename 139 let index = match(sqls, '.\{-}\ze\n', (index+strlen(file)+1)) 140 endwhile 141 142 " Sort the list if using version 7 143 if v:version >= 700 144 let mylist = split(sqls, "\n") 145 let mylist = sort(mylist) 146 let sqls = join(mylist, "\n") 147 endif 148 149 let s:sql_list = sqls 150 endif 151 152 return s:sql_list 153 154 endfunction 155 156 function SQL_SetType(name) 157 158 " User has decided to override default SQL scripts and 159 " specify a vendor specific version 160 " (ie Oracle, Informix, SQL Anywhere, ...) 161 " So check for an remove any settings that prevent the 162 " scripts from being executed, and then source the 163 " appropriate Vim scripts. 164 if exists("b:did_ftplugin") 165 unlet b:did_ftplugin 166 endif 167 if exists("b:current_syntax") 168 " echomsg 'SQLSetType - clearing syntax' 169 syntax clear 170 endif 171 if exists("b:did_indent") 172 " echomsg 'SQLSetType - clearing indent' 173 unlet b:did_indent 174 " Set these values to their defaults 175 setlocal indentkeys& 176 setlocal indentexpr& 177 endif 178 179 " Ensure the name is in the correct format 180 let new_sql_type = substitute(a:name, 181 \ '\s*\([^\.]\+\)\(\.\w\+\)\?', '\L\1', '') 182 183 " Do not specify a buffer local variable if it is 184 " the default value 185 if new_sql_type == 'sql' 186 let new_sql_type = 'sqloracle' 187 endif 188 let b:sql_type_override = new_sql_type 189 190 " Remove any cached SQL since a new sytax will have different 191 " items and groups 192 if !exists('g:loaded_sql_completion') || 100 == g:loaded_sql_completion 193 call sqlcomplete#ResetCacheSyntax() 194 endif 195 196 " Vim will automatically source the correct files if we 197 " change the filetype. You cannot do this with setfiletype 198 " since that command will only execute if a filetype has 199 " not already been set. In this case we want to override 200 " the existing filetype. 201 let &filetype = 'sql' 202 203 if b:sql_compl_savefunc != "" 204 " We are changing the filetype to SQL from some other filetype 205 " which had OMNI completion defined. We need to activate the 206 " SQL completion plugin in order to cache some of the syntax items 207 " while the syntax rules for SQL are active. 208 call sqlcomplete#PreCacheSyntax() 209 endif 210 endfunction 211 command! -nargs=* -complete=custom,SQL_GetList SQLSetType :call SQL_SetType(<q-args>) 212 213endif 214 215" Functions/Commands to allow the user determine current SQL syntax dialect 216" This works with both Vim 6 and 7. 217 218if !exists("*SQL_GetType") 219 function SQL_GetType() 220 if exists('b:sql_type_override') 221 echomsg "Current SQL dialect in use:".b:sql_type_override 222 else 223 echomsg "Current SQL dialect in use:".g:sql_type_default 224 endif 225 endfunction 226 command! -nargs=0 SQLGetType :call SQL_GetType() 227endif 228 229if exists("b:sql_type_override") 230 " echo 'sourcing buffer ftplugin/'.b:sql_type_override.'.vim' 231 if globpath(&runtimepath, 'ftplugin/'.b:sql_type_override.'.vim') != '' 232 exec 'runtime ftplugin/'.b:sql_type_override.'.vim' 233 " else 234 " echomsg 'ftplugin/'.b:sql_type_override.' not exist, using default' 235 endif 236elseif exists("g:sql_type_default") 237 " echo 'sourcing global ftplugin/'.g:sql_type_default.'.vim' 238 if globpath(&runtimepath, 'ftplugin/'.g:sql_type_default.'.vim') != '' 239 exec 'runtime ftplugin/'.g:sql_type_default.'.vim' 240 " else 241 " echomsg 'ftplugin/'.g:sql_type_default.'.vim not exist, using default' 242 endif 243endif 244 245" If the above runtime command succeeded, do not load the default settings 246if exists("b:did_ftplugin") 247 finish 248endif 249 250let b:undo_ftplugin = "setl comments< formatoptions< define< omnifunc<" . 251 \ " | unlet! b:browsefilter b:match_words" 252 253" Don't load another plugin for this buffer 254let b:did_ftplugin = 1 255let b:current_ftplugin = 'sql' 256 257" Win32 can filter files in the browse dialog 258if has("gui_win32") && !exists("b:browsefilter") 259 let b:browsefilter = "SQL Files (*.sql)\t*.sql\n" . 260 \ "All Files (*.*)\t*.*\n" 261endif 262 263" Some standard expressions for use with the matchit strings 264let s:notend = '\%(\<end\s\+\)\@<!' 265let s:when_no_matched_or_others = '\%(\<when\>\%(\s\+\%(\%(\<not\>\s\+\)\?<matched\>\)\|\<others\>\)\@!\)' 266let s:or_replace = '\%(or\s\+replace\s\+\)\?' 267 268" Define patterns for the matchit macro 269if !exists("b:match_words") 270 " SQL is generally case insensitive 271 let b:match_ignorecase = 1 272 273 " Handle the following: 274 " if 275 " elseif | elsif 276 " else [if] 277 " end if 278 " 279 " [while condition] loop 280 " leave 281 " break 282 " continue 283 " exit 284 " end loop 285 " 286 " for 287 " leave 288 " break 289 " continue 290 " exit 291 " end loop 292 " 293 " do 294 " statements 295 " doend 296 " 297 " case 298 " when 299 " when 300 " default 301 " end case 302 " 303 " merge 304 " when not matched 305 " when matched 306 " 307 " EXCEPTION 308 " WHEN column_not_found THEN 309 " WHEN OTHERS THEN 310 " 311 " create[ or replace] procedure|function|event 312 " \ '^\s*\<\%(do\|for\|while\|loop\)\>.*:'. 313 314 " For ColdFusion support 315 setlocal matchpairs+=<:> 316 let b:match_words = &matchpairs . 317 \ ',\<begin\>:\<end\>\W*$,'. 318 \ 319 \ s:notend . '\<if\>:'. 320 \ '\<elsif\>\|\<elseif\>\|\<else\>:'. 321 \ '\<end\s\+if\>,'. 322 \ 323 \ '\(^\s*\)\@<=\(\<\%(do\|for\|while\|loop\)\>.*\):'. 324 \ '\%(\<exit\>\|\<leave\>\|\<break\>\|\<continue\>\):'. 325 \ '\%(\<doend\>\|\%(\<end\s\+\%(for\|while\|loop\>\)\)\),'. 326 \ 327 \ '\%('. s:notend . '\<case\>\):'. 328 \ '\%('.s:when_no_matched_or_others.'\):'. 329 \ '\%(\<when\s\+others\>\|\<end\s\+case\>\),' . 330 \ 331 \ '\<merge\>:' . 332 \ '\<when\s\+not\s\+matched\>:' . 333 \ '\<when\s\+matched\>,' . 334 \ 335 \ '\%(\<create\s\+' . s:or_replace . '\)\?'. 336 \ '\%(function\|procedure\|event\):'. 337 \ '\<returns\?\>' 338 " \ '\<begin\>\|\<returns\?\>:'. 339 " \ '\<end\>\(;\)\?\s*$' 340 " \ '\<exception\>:'.s:when_no_matched_or_others. 341 " \ ':\<when\s\+others\>,'. 342 " 343 " \ '\%(\<exception\>\|\%('. s:notend . '\<case\>\)\):'. 344 " \ '\%(\<default\>\|'.s:when_no_matched_or_others.'\):'. 345 " \ '\%(\%(\<when\s\+others\>\)\|\<end\s\+case\>\),' . 346endif 347 348" Define how to find the macro definition of a variable using the various 349" [d, [D, [_CTRL_D and so on features 350" Match these values ignoring case 351" ie DECLARE varname INTEGER 352let &l:define = '\c\<\(VARIABLE\|DECLARE\|IN\|OUT\|INOUT\)\>' 353 354 355" Mappings to move to the next BEGIN ... END block 356" \W - no characters or digits 357nnoremap <buffer> <silent> ]] :call search('\c^\s*begin\>', 'W' )<CR> 358nnoremap <buffer> <silent> [[ :call search('\c^\s*begin\>', 'bW' )<CR> 359nnoremap <buffer> <silent> ][ :call search('\c^\s*end\W*$', 'W' )<CR> 360nnoremap <buffer> <silent> [] :call search('\c^\s*end\W*$', 'bW' )<CR> 361xnoremap <buffer> <silent> ]] :<C-U>exec "normal! gv"<Bar>call search('\c^\s*begin\>', 'W' )<CR> 362xnoremap <buffer> <silent> [[ :<C-U>exec "normal! gv"<Bar>call search('\c^\s*begin\>', 'bW' )<CR> 363xnoremap <buffer> <silent> ][ :<C-U>exec "normal! gv"<Bar>call search('\c^\s*end\W*$', 'W' )<CR> 364xnoremap <buffer> <silent> [] :<C-U>exec "normal! gv"<Bar>call search('\c^\s*end\W*$', 'bW' )<CR> 365 366 367" By default only look for CREATE statements, but allow 368" the user to override 369if !exists('g:ftplugin_sql_statements') 370 let g:ftplugin_sql_statements = 'create' 371endif 372 373" Predefined SQL objects what are used by the below mappings using 374" the ]} style maps. 375" This global variable allows the users to override it's value 376" from within their vimrc. 377" Note, you cannot use \?, since these patterns can be used to search 378" backwards, you must use \{,1} 379if !exists('g:ftplugin_sql_objects') 380 let g:ftplugin_sql_objects = 'function,procedure,event,' . 381 \ '\(existing\\|global\s\+temporary\s\+\)\{,1}' . 382 \ 'table,trigger' . 383 \ ',schema,service,publication,database,datatype,domain' . 384 \ ',index,subscription,synchronization,view,variable' 385endif 386 387" Key to trigger SQL completion 388if !exists('g:ftplugin_sql_omni_key') 389 let g:ftplugin_sql_omni_key = '<C-C>' 390endif 391" Key to trigger drill into column list 392if !exists('g:ftplugin_sql_omni_key_right') 393 let g:ftplugin_sql_omni_key_right = '<Right>' 394endif 395" Key to trigger drill out of column list 396if !exists('g:ftplugin_sql_omni_key_left') 397 let g:ftplugin_sql_omni_key_left = '<Left>' 398endif 399 400" Replace all ,'s with bars, except ones with numbers after them. 401" This will most likely be a \{,1} string. 402let s:ftplugin_sql_objects = 403 \ '\c^\s*' . 404 \ '\(\(' . 405 \ substitute(g:ftplugin_sql_statements, ',\d\@!', '\\\\|', 'g') . 406 \ '\)\s\+\(or\s\+replace\s\+\)\{,1}\)\{,1}' . 407 \ '\<\(' . 408 \ substitute(g:ftplugin_sql_objects, ',\d\@!', '\\\\|', 'g') . 409 \ '\)\>' 410 411" Mappings to move to the next CREATE ... block 412exec "nnoremap <buffer> <silent> ]} :call search('".s:ftplugin_sql_objects."', 'W')<CR>" 413exec "nnoremap <buffer> <silent> [{ :call search('".s:ftplugin_sql_objects."', 'bW')<CR>" 414" Could not figure out how to use a :call search() string in visual mode 415" without it ending visual mode 416" Unfortunately, this will add a entry to the search history 417exec 'xnoremap <buffer> <silent> ]} /'.s:ftplugin_sql_objects.'<CR>' 418exec 'xnoremap <buffer> <silent> [{ ?'.s:ftplugin_sql_objects.'<CR>' 419 420" Mappings to move to the next COMMENT 421" 422" Had to double the \ for the \| separator since this has a special 423" meaning on maps 424let b:comment_leader = '\(--\\|\/\/\\|\*\\|\/\*\\|\*\/\)' 425" Find the start of the next comment 426let b:comment_start = '^\(\s*'.b:comment_leader.'.*\n\)\@<!'. 427 \ '\(\s*'.b:comment_leader.'\)' 428" Find the end of the previous comment 429let b:comment_end = '\(^\s*'.b:comment_leader.'.*\n\)'. 430 \ '\(^\s*'.b:comment_leader.'\)\@!' 431" Skip over the comment 432let b:comment_jump_over = "call search('". 433 \ '^\(\s*'.b:comment_leader.'.*\n\)\@<!'. 434 \ "', 'W')" 435let b:comment_skip_back = "call search('". 436 \ '^\(\s*'.b:comment_leader.'.*\n\)\@<!'. 437 \ "', 'bW')" 438" Move to the start and end of comments 439exec 'nnoremap <silent><buffer> ]" :call search('."'".b:comment_start."'".', "W" )<CR>' 440exec 'nnoremap <silent><buffer> [" :call search('."'".b:comment_end."'".', "W" )<CR>' 441exec 'xnoremap <silent><buffer> ]" :<C-U>exec "normal! gv"<Bar>call search('."'".b:comment_start."'".', "W" )<CR>' 442exec 'xnoremap <silent><buffer> [" :<C-U>exec "normal! gv"<Bar>call search('."'".b:comment_end."'".', "W" )<CR>' 443 444" Comments can be of the form: 445" /* 446" * 447" */ 448" or 449" -- 450" or 451" // 452setlocal comments=s1:/*,mb:*,ex:*/,:--,:// 453 454" Set completion with CTRL-X CTRL-O to autoloaded function. 455if exists('&omnifunc') 456 " Since the SQL completion plugin can be used in conjunction 457 " with other completion filetypes it must record the previous 458 " OMNI function prior to setting up the SQL OMNI function 459 let b:sql_compl_savefunc = &omnifunc 460 461 " This is used by the sqlcomplete.vim plugin 462 " Source it for it's global functions 463 runtime autoload/syntaxcomplete.vim 464 465 setlocal omnifunc=sqlcomplete#Complete 466 " Prevent the intellisense plugin from loading 467 let b:sql_vis = 1 468 if !exists('g:omni_sql_no_default_maps') 469 " Static maps which use populate the completion list 470 " using Vim's syntax highlighting rules 471 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'a <C-\><C-O>:call sqlcomplete#Map("syntax")<CR><C-X><C-O>' 472 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'k <C-\><C-O>:call sqlcomplete#Map("sqlKeyword")<CR><C-X><C-O>' 473 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'f <C-\><C-O>:call sqlcomplete#Map("sqlFunction")<CR><C-X><C-O>' 474 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'o <C-\><C-O>:call sqlcomplete#Map("sqlOption")<CR><C-X><C-O>' 475 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'T <C-\><C-O>:call sqlcomplete#Map("sqlType")<CR><C-X><C-O>' 476 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'s <C-\><C-O>:call sqlcomplete#Map("sqlStatement")<CR><C-X><C-O>' 477 " Dynamic maps which use populate the completion list 478 " using the dbext.vim plugin 479 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'t <C-\><C-O>:call sqlcomplete#Map("table")<CR><C-X><C-O>' 480 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'p <C-\><C-O>:call sqlcomplete#Map("procedure")<CR><C-X><C-O>' 481 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'v <C-\><C-O>:call sqlcomplete#Map("view")<CR><C-X><C-O>' 482 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'c <C-\><C-O>:call sqlcomplete#Map("column")<CR><C-X><C-O>' 483 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'l <C-\><C-O>:call sqlcomplete#Map("column_csv")<CR><C-X><C-O>' 484 " The next 3 maps are only to be used while the completion window is 485 " active due to the <CR> at the beginning of the map 486 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'L <C-Y><C-\><C-O>:call sqlcomplete#Map("column_csv")<CR><C-X><C-O>' 487 " <C-Right> is not recognized on most Unix systems, so only create 488 " these additional maps on the Windows platform. 489 " If you would like to use these maps, choose a different key and make 490 " the same map in your vimrc. 491 " if has('win32') 492 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key_right.' <C-R>=sqlcomplete#DrillIntoTable()<CR>' 493 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key_left.' <C-R>=sqlcomplete#DrillOutOfColumns()<CR>' 494 " endif 495 " Remove any cached items useful for schema changes 496 exec 'inoremap <buffer> '.g:ftplugin_sql_omni_key.'R <C-\><C-O>:call sqlcomplete#Map("resetCache")<CR><C-X><C-O>' 497 endif 498 499 if b:sql_compl_savefunc != "" 500 " We are changing the filetype to SQL from some other filetype 501 " which had OMNI completion defined. We need to activate the 502 " SQL completion plugin in order to cache some of the syntax items 503 " while the syntax rules for SQL are active. 504 call sqlcomplete#ResetCacheSyntax() 505 call sqlcomplete#PreCacheSyntax() 506 endif 507endif 508 509let &cpo = s:save_cpo 510unlet s:save_cpo 511 512" vim:sw=4: 513 514