1" SQL filetype plugin file 2" Language: SQL (Common for Oracle, Microsoft SQL Server, Sybase) 3" Version: 11.0 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 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 11.0 (May 2013) 40" 41" NF: Updated to use SyntaxComplete's new regex support for syntax groups. 42" 43" Version 10.0 (Dec 2012) 44" 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 50" worked. 51" 52" Version 9.0 53" 54" NF: Completes 'b:undo_ftplugin' 55" BF: Correctly set cpoptions when creating script 56" 57" Version 8.0 58" 59" NF: Improved the matchit plugin regex (Talek) 60" 61" Version 7.0 62" 63" NF: Calls the sqlcomplete#ResetCacheSyntax() function when calling 64" SQLSetType. 65" 66" Version 6.0 67" 68" NF: Adds the command SQLGetType 69" 70" Version 5.0 71" 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>' 77" 78" BF: format-options - Auto-wrap comments using textwidth was turned off 79" by mistake. 80 81 82" Only do this when not done yet for this buffer 83if exists("b:did_ftplugin") 84 finish 85endif 86 87let s:save_cpo = &cpo 88set cpo&vim 89 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. 94setlocal formatoptions-=t 95setlocal formatoptions+=c 96 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. 100 101if !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) 106 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*') 112 113 let sqls = "\n".list_indent."\n".list_syntax."\n".list_ftplugin."\n" 114 115 " Strip out everything (path info) but the filename 116 " Regex 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 121 " 122 " Replace it with just the filename (get rid of PATH) 123 " 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\@=', 128 \ '\1\n', 129 \ 'g' 130 \ ) 131 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') 136 while index > -1 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)) 144 endwhile 145 146 " Sort the list if using version 7 147 if v:version >= 700 148 let mylist = split(sqls, "\n") 149 let mylist = sort(mylist) 150 let sqls = join(mylist, "\n") 151 endif 152 153 let s:sql_list = sqls 154 endif 155 156 return s:sql_list 157 158 endfunction 159 160 function SQL_SetType(name) 161 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") 169 unlet b:did_ftplugin 170 endif 171 if exists("b:current_syntax") 172 " echomsg 'SQLSetType - clearing syntax' 173 syntax clear 174 endif 175 if exists("b:did_indent") 176 " echomsg 'SQLSetType - clearing indent' 177 unlet b:did_indent 178 " Set these values to their defaults 179 setlocal indentkeys& 180 setlocal indentexpr& 181 endif 182 183 " Ensure the name is in the correct format 184 let new_sql_type = substitute(a:name, 185 \ '\s*\([^\.]\+\)\(\.\w\+\)\?', '\L\1', '') 186 187 " Do not specify a buffer local variable if it is 188 " the default value 189 if new_sql_type == 'sql' 190 let new_sql_type = 'sqloracle' 191 endif 192 let b:sql_type_override = new_sql_type 193 194 " Remove any cached SQL since a new sytax will have different 195 " items and groups 196 if !exists('g:loaded_sql_completion') || g:loaded_sql_completion >= 100 197 call sqlcomplete#ResetCacheSyntax() 198 endif 199 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' 206 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() 213 endif 214 endfunction 215 command! -nargs=* -complete=custom,SQL_GetList SQLSetType :call SQL_SetType(<q-args>) 216 217endif 218 219" Functions/Commands to allow the user determine current SQL syntax dialect 220" This works with both Vim 6 and 7. 221 222if !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 226 else 227 echomsg "Current SQL dialect in use:".g:sql_type_default 228 endif 229 endfunction 230 command! -nargs=0 SQLGetType :call SQL_GetType() 231endif 232 233if 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' 237 " else 238 " echomsg 'ftplugin/'.b:sql_type_override.' not exist, using default' 239 endif 240elseif 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' 244 " else 245 " echomsg 'ftplugin/'.g:sql_type_default.'.vim not exist, using default' 246 endif 247endif 248 249" If the above runtime command succeeded, do not load the default settings 250if exists("b:did_ftplugin") 251 finish 252endif 253 254let b:undo_ftplugin = "setl comments< formatoptions< define< omnifunc<" . 255 \ " | unlet! b:browsefilter b:match_words" 256 257" Don't load another plugin for this buffer 258let b:did_ftplugin = 1 259let b:current_ftplugin = 'sql' 260 261" Win32 can filter files in the browse dialog 262if has("gui_win32") && !exists("b:browsefilter") 263 let b:browsefilter = "SQL Files (*.sql)\t*.sql\n" . 264 \ "All Files (*.*)\t*.*\n" 265endif 266 267" Some standard expressions for use with the matchit strings 268let s:notend = '\%(\<end\s\+\)\@<!' 269let s:when_no_matched_or_others = '\%(\<when\>\%(\s\+\%(\%(\<not\>\s\+\)\?<matched\>\)\|\<others\>\)\@!\)' 270let s:or_replace = '\%(or\s\+replace\s\+\)\?' 271 272" Define patterns for the matchit macro 273if !exists("b:match_words") 274 " SQL is generally case insensitive 275 let b:match_ignorecase = 1 276 277 " Handle the following: 278 " if 279 " elseif | elsif 280 " else [if] 281 " end if 282 " 283 " [while condition] loop 284 " leave 285 " break 286 " continue 287 " exit 288 " end loop 289 " 290 " for 291 " leave 292 " break 293 " continue 294 " exit 295 " end loop 296 " 297 " do 298 " statements 299 " doend 300 " 301 " case 302 " when 303 " when 304 " default 305 " end case 306 " 307 " merge 308 " when not matched 309 " when matched 310 " 311 " EXCEPTION 312 " WHEN column_not_found THEN 313 " WHEN OTHERS THEN 314 " 315 " create[ or replace] procedure|function|event 316 " \ '^\s*\<\%(do\|for\|while\|loop\)\>.*:'. 317 318 " For ColdFusion support 319 setlocal matchpairs+=<:> 320 let b:match_words = &matchpairs . 321 \ ',\<begin\>:\<end\>\W*$,'. 322 \ 323 \ s:notend . '\<if\>:'. 324 \ '\<elsif\>\|\<elseif\>\|\<else\>:'. 325 \ '\<end\s\+if\>,'. 326 \ 327 \ '\(^\s*\)\@<=\(\<\%(do\|for\|while\|loop\)\>.*\):'. 328 \ '\%(\<exit\>\|\<leave\>\|\<break\>\|\<continue\>\):'. 329 \ '\%(\<doend\>\|\%(\<end\s\+\%(for\|while\|loop\>\)\)\),'. 330 \ 331 \ '\%('. s:notend . '\<case\>\):'. 332 \ '\%('.s:when_no_matched_or_others.'\):'. 333 \ '\%(\<when\s\+others\>\|\<end\s\+case\>\),' . 334 \ 335 \ '\<merge\>:' . 336 \ '\<when\s\+not\s\+matched\>:' . 337 \ '\<when\s\+matched\>,' . 338 \ 339 \ '\%(\<create\s\+' . s:or_replace . '\)\?'. 340 \ '\%(function\|procedure\|event\):'. 341 \ '\<returns\?\>' 342 " \ '\<begin\>\|\<returns\?\>:'. 343 " \ '\<end\>\(;\)\?\s*$' 344 " \ '\<exception\>:'.s:when_no_matched_or_others. 345 " \ ':\<when\s\+others\>,'. 346 " 347 " \ '\%(\<exception\>\|\%('. s:notend . '\<case\>\)\):'. 348 " \ '\%(\<default\>\|'.s:when_no_matched_or_others.'\):'. 349 " \ '\%(\%(\<when\s\+others\>\)\|\<end\s\+case\>\),' . 350endif 351 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 356let &l:define = '\c\<\(VARIABLE\|DECLARE\|IN\|OUT\|INOUT\)\>' 357 358 359" Mappings to move to the next BEGIN ... END block 360" \W - no characters or digits 361nnoremap <buffer> <silent> ]] :call search('\c^\s*begin\>', 'W' )<CR> 362nnoremap <buffer> <silent> [[ :call search('\c^\s*begin\>', 'bW' )<CR> 363nnoremap <buffer> <silent> ][ :call search('\c^\s*end\W*$', 'W' )<CR> 364nnoremap <buffer> <silent> [] :call search('\c^\s*end\W*$', 'bW' )<CR> 365xnoremap <buffer> <silent> ]] :<C-U>exec "normal! gv"<Bar>call search('\c^\s*begin\>', 'W' )<CR> 366xnoremap <buffer> <silent> [[ :<C-U>exec "normal! gv"<Bar>call search('\c^\s*begin\>', 'bW' )<CR> 367xnoremap <buffer> <silent> ][ :<C-U>exec "normal! gv"<Bar>call search('\c^\s*end\W*$', 'W' )<CR> 368xnoremap <buffer> <silent> [] :<C-U>exec "normal! gv"<Bar>call search('\c^\s*end\W*$', 'bW' )<CR> 369 370 371" By default only look for CREATE statements, but allow 372" the user to override 373if !exists('g:ftplugin_sql_statements') 374 let g:ftplugin_sql_statements = 'create' 375endif 376 377" Predefined SQL objects what are used by the below mappings using 378" the ]} style maps. 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} 383if !exists('g:ftplugin_sql_objects') 384 let g:ftplugin_sql_objects = 'function,procedure,event,' . 385 \ '\(existing\\|global\s\+temporary\s\+\)\{,1}' . 386 \ 'table,trigger' . 387 \ ',schema,service,publication,database,datatype,domain' . 388 \ ',index,subscription,synchronization,view,variable' 389endif 390 391" Key to trigger SQL completion 392if !exists('g:ftplugin_sql_omni_key') 393 let g:ftplugin_sql_omni_key = '<C-C>' 394endif 395" Key to trigger drill into column list 396if !exists('g:ftplugin_sql_omni_key_right') 397 let g:ftplugin_sql_omni_key_right = '<Right>' 398endif 399" Key to trigger drill out of column list 400if !exists('g:ftplugin_sql_omni_key_left') 401 let g:ftplugin_sql_omni_key_left = '<Left>' 402endif 403 404" Replace all ,'s with bars, except ones with numbers after them. 405" This will most likely be a \{,1} string. 406let s:ftplugin_sql_objects = 407 \ '\c^\s*' . 408 \ '\(\(' . 409 \ substitute(g:ftplugin_sql_statements, ',\d\@!', '\\\\|', 'g') . 410 \ '\)\s\+\(or\s\+replace\s\+\)\{,1}\)\{,1}' . 411 \ '\<\(' . 412 \ substitute(g:ftplugin_sql_objects, ',\d\@!', '\\\\|', 'g') . 413 \ '\)\>' 414 415" Mappings to move to the next CREATE ... block 416exec "nnoremap <buffer> <silent> ]} :call search('".s:ftplugin_sql_objects."', 'W')<CR>" 417exec "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 421exec 'xnoremap <buffer> <silent> ]} /'.s:ftplugin_sql_objects.'<CR>' 422exec 'xnoremap <buffer> <silent> [{ ?'.s:ftplugin_sql_objects.'<CR>' 423 424" Mappings to move to the next COMMENT 425" 426" Had to double the \ for the \| separator since this has a special 427" meaning on maps 428let b:comment_leader = '\(--\\|\/\/\\|\*\\|\/\*\\|\*\/\)' 429" Find the start of the next comment 430let b:comment_start = '^\(\s*'.b:comment_leader.'.*\n\)\@<!'. 431 \ '\(\s*'.b:comment_leader.'\)' 432" Find the end of the previous comment 433let b:comment_end = '\(^\s*'.b:comment_leader.'.*\n\)'. 434 \ '\(^\s*'.b:comment_leader.'\)\@!' 435" Skip over the comment 436let b:comment_jump_over = "call search('". 437 \ '^\(\s*'.b:comment_leader.'.*\n\)\@<!'. 438 \ "', 'W')" 439let b:comment_skip_back = "call search('". 440 \ '^\(\s*'.b:comment_leader.'.*\n\)\@<!'. 441 \ "', 'bW')" 442" Move to the start and end of comments 443exec 'nnoremap <silent><buffer> ]" :call search('."'".b:comment_start."'".', "W" )<CR>' 444exec 'nnoremap <silent><buffer> [" :call search('."'".b:comment_end."'".', "W" )<CR>' 445exec 'xnoremap <silent><buffer> ]" :<C-U>exec "normal! gv"<Bar>call search('."'".b:comment_start."'".', "W" )<CR>' 446exec 'xnoremap <silent><buffer> [" :<C-U>exec "normal! gv"<Bar>call search('."'".b:comment_end."'".', "W" )<CR>' 447 448" Comments can be of the form: 449" /* 450" * 451" */ 452" or 453" -- 454" or 455" // 456setlocal comments=s1:/*,mb:*,ex:*/,:--,:// 457 458" Set completion with CTRL-X CTRL-O to autoloaded function. 459if 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 464 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 470 471 setlocal omnifunc=sqlcomplete#Complete 472 " Prevent the intellisense plugin from loading 473 let b:sql_vis = 1 474 if !exists('g:omni_sql_no_default_maps') 475 let regex_extra = '' 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*' 479 endif 480 endif 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. 503 " if has('win32') 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>' 506 " endif 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>' 509 endif 510 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() 518 endif 519endif 520 521let &cpo = s:save_cpo 522unlet s:save_cpo 523 524" vim:sw=4: 525