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