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