1" SQL filetype plugin file
2" Language:    SQL (Common for Oracle, Microsoft SQL Server, Sybase)
3" Version:     7.0
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
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 7.0
40" 
41" NF: Calls the sqlcomplete#ResetCacheSyntax() function when calling
42"     SQLSetType.
43"
44" Version 6.0
45" 
46" NF: Adds the command SQLGetType
47"
48" Version 5.0
49" 
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>'
55"
56" BF: format-options - Auto-wrap comments using textwidth was turned off 
57"                      by mistake.
58
59
60" Only do this when not done yet for this buffer
61if exists("b:did_ftplugin")
62  finish
63endif
64
65let s:save_cpo = &cpo
66set cpo=
67
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.
72setlocal formatoptions-=t
73setlocal formatoptions+=c
74
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.
78
79if !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)
84
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*')
90
91            let sqls = "\n".list_indent."\n".list_syntax."\n".list_ftplugin."\n"
92
93            " Strip out everything (path info) but the filename
94            " Regex
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
99            "
100            "    Replace it with just the filename (get rid of PATH)
101            "
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\@=', 
106                        \ '\1\n', 
107                        \ 'g'
108                        \ )
109
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')
114            while index > -1
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))
122            endwhile
123
124            " Sort the list if using version 7
125            if v:version >= 700
126                let mylist = split(sqls, "\n")
127                let mylist = sort(mylist)
128                let sqls   = join(mylist, "\n")
129            endif
130
131            let s:sql_list = sqls
132        endif
133
134        return s:sql_list
135
136    endfunction
137
138    function SQL_SetType(name)
139
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")
147            unlet b:did_ftplugin
148        endif
149        if exists("b:current_syntax")
150            " echomsg 'SQLSetType - clearing syntax'
151            syntax clear
152        endif
153        if exists("b:did_indent")
154            " echomsg 'SQLSetType - clearing indent'
155            unlet b:did_indent
156            " Set these values to their defaults
157            setlocal indentkeys&
158            setlocal indentexpr&
159        endif
160
161        " Ensure the name is in the correct format
162        let new_sql_type = substitute(a:name, 
163                    \ '\s*\([^\.]\+\)\(\.\w\+\)\?', '\L\1', '')
164
165        " Do not specify a buffer local variable if it is 
166        " the default value
167        if new_sql_type == 'sql'
168          let new_sql_type = 'sqloracle'
169        endif
170        let b:sql_type_override = new_sql_type
171
172        " Remove any cached SQL since a new sytax will have different
173        " items and groups
174        if !exists('g:loaded_sql_completion') || 100 == g:loaded_sql_completion
175            call sqlcomplete#ResetCacheSyntax()
176        endif
177
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'
184
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()
191        endif
192    endfunction
193    command! -nargs=* -complete=custom,SQL_GetList SQLSetType :call SQL_SetType(<q-args>)
194
195endif
196
197" Functions/Commands to allow the user determine current SQL syntax dialect
198" This works with both Vim 6 and 7.
199
200if !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
204        else
205            echomsg "Current SQL dialect in use:".g:sql_type_default        
206        endif
207    endfunction
208    command! -nargs=0 SQLGetType :call SQL_GetType()
209endif
210
211if 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'
215    " else
216    "     echomsg 'ftplugin/'.b:sql_type_override.' not exist, using default'
217    endif
218elseif 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'
222    " else
223    "     echomsg 'ftplugin/'.g:sql_type_default.'.vim not exist, using default'
224    endif
225endif
226
227" If the above runtime command succeeded, do not load the default settings
228if exists("b:did_ftplugin")
229  finish
230endif
231
232let b:undo_ftplugin = "setl comments<"
233
234" Don't load another plugin for this buffer
235let b:did_ftplugin     = 1
236let b:current_ftplugin = 'sql'
237
238" Win32 can filter files in the browse dialog
239if has("gui_win32") && !exists("b:browsefilter")
240    let b:browsefilter = "SQL Files (*.sql)\t*.sql\n" .
241	  \ "All Files (*.*)\t*.*\n"
242endif
243
244" Some standard expressions for use with the matchit strings
245let s:notend = '\%(\<end\s\+\)\@<!'
246let s:when_no_matched_or_others = '\%(\<when\>\%(\s\+\%(\%(\<not\>\s\+\)\?<matched\>\)\|\<others\>\)\@!\)'
247let s:or_replace = '\%(or\s\+replace\s\+\)\?'
248
249" Define patterns for the matchit macro
250if !exists("b:match_words")
251    " SQL is generally case insensitive
252    let b:match_ignorecase = 1
253
254    " Handle the following:
255    " if
256    " elseif | elsif
257    " else [if]
258    " end if
259    "
260    " [while condition] loop
261    "     leave
262    "     break
263    "     continue
264    "     exit
265    " end loop
266    "
267    " for
268    "     leave
269    "     break
270    "     continue
271    "     exit
272    " end loop
273    "
274    " do
275    "     statements
276    " doend
277    "
278    " case
279    " when 
280    " when
281    " default
282    " end case
283    "
284    " merge
285    " when not matched
286    " when matched
287    "
288    " EXCEPTION
289    " WHEN column_not_found THEN
290    " WHEN OTHERS THEN
291    "
292    " create[ or replace] procedure|function|event
293
294    let b:match_words =
295		\ '\<begin\>:\<end\>\W*$,'.
296		\
297                \ s:notend . '\<if\>:'.
298                \ '\<elsif\>\|\<elseif\>\|\<else\>:'.
299                \ '\<end\s\+if\>,'.
300                \
301                \ '\<do\>\|'.
302                \ '\<while\>\|'.
303                \ '\%(' . s:notend . '\<loop\>\)\|'.
304                \ '\%(' . s:notend . '\<for\>\):'.
305                \ '\<exit\>\|\<leave\>\|\<break\>\|\<continue\>:'.
306                \ '\%(\<end\s\+\%(for\|loop\>\)\)\|\<doend\>,'.
307                \
308                \ '\%('. s:notend . '\<case\>\):'.
309                \ '\%('.s:when_no_matched_or_others.'\):'.
310                \ '\%(\<when\s\+others\>\|\<end\s\+case\>\),' .
311                \
312                \ '\<merge\>:' .
313                \ '\<when\s\+not\s\+matched\>:' .
314                \ '\<when\s\+matched\>,' .
315                \
316                \ '\%(\<create\s\+' . s:or_replace . '\)\?'.
317                \ '\%(function\|procedure\|event\):'.
318                \ '\<returns\?\>'
319                " \ '\<begin\>\|\<returns\?\>:'.
320                " \ '\<end\>\(;\)\?\s*$'
321                " \ '\<exception\>:'.s:when_no_matched_or_others.
322                " \ ':\<when\s\+others\>,'.
323		"
324                " \ '\%(\<exception\>\|\%('. s:notend . '\<case\>\)\):'.
325                " \ '\%(\<default\>\|'.s:when_no_matched_or_others.'\):'.
326                " \ '\%(\%(\<when\s\+others\>\)\|\<end\s\+case\>\),' .
327endif
328
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
333let &l:define = '\c\<\(VARIABLE\|DECLARE\|IN\|OUT\|INOUT\)\>'
334
335
336" Mappings to move to the next BEGIN ... END block
337" \W - no characters or digits
338nmap <buffer> <silent> ]] :call search('\\c^\\s*begin\\>', 'W' )<CR>
339nmap <buffer> <silent> [[ :call search('\\c^\\s*begin\\>', 'bW' )<CR>
340nmap <buffer> <silent> ][ :call search('\\c^\\s*end\\W*$', 'W' )<CR>
341nmap <buffer> <silent> [] :call search('\\c^\\s*end\\W*$', 'bW' )<CR>
342vmap <buffer> <silent> ]] :<C-U>exec "normal! gv"<Bar>call search('\\c^\\s*begin\\>', 'W' )<CR>
343vmap <buffer> <silent> [[ :<C-U>exec "normal! gv"<Bar>call search('\\c^\\s*begin\\>', 'bW' )<CR>
344vmap <buffer> <silent> ][ :<C-U>exec "normal! gv"<Bar>call search('\\c^\\s*end\\W*$', 'W' )<CR>
345vmap <buffer> <silent> [] :<C-U>exec "normal! gv"<Bar>call search('\\c^\\s*end\\W*$', 'bW' )<CR>
346
347
348" By default only look for CREATE statements, but allow
349" the user to override
350if !exists('g:ftplugin_sql_statements')
351    let g:ftplugin_sql_statements = 'create'
352endif
353
354" Predefined SQL objects what are used by the below mappings using
355" the ]} style maps.
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}
360if !exists('g:ftplugin_sql_objects')
361    let g:ftplugin_sql_objects = 'function,procedure,event,' .
362                \ '\\(existing\\\\|global\\s\\+temporary\\s\\+\\)\\\{,1}' .
363                \ 'table,trigger' .
364                \ ',schema,service,publication,database,datatype,domain' .
365                \ ',index,subscription,synchronization,view,variable'
366endif
367
368" Key to trigger SQL completion
369if !exists('g:ftplugin_sql_omni_key')
370    let g:ftplugin_sql_omni_key = '<C-C>'
371endif
372" Key to trigger drill into column list
373if !exists('g:ftplugin_sql_omni_key_right')
374    let g:ftplugin_sql_omni_key_right = '<Right>'
375endif
376" Key to trigger drill out of column list
377if !exists('g:ftplugin_sql_omni_key_left')
378    let g:ftplugin_sql_omni_key_left = '<Left>'
379endif
380
381" Replace all ,'s with bars, except ones with numbers after them.
382" This will most likely be a \{,1} string.
383let s:ftplugin_sql_objects = 
384            \ '\\c^\\s*' .
385            \ '\\(\\(' .
386            \ substitute(g:ftplugin_sql_statements, ',\d\@!', '\\\\\\|', 'g') .
387            \ '\\)\\s\\+\\(or\\s\\+replace\\\s\+\\)\\{,1}\\)\\{,1}' .
388            \ '\\<\\(' .
389            \ substitute(g:ftplugin_sql_objects, ',\d\@!', '\\\\\\|', 'g') .
390            \ '\\)\\>' 
391
392" Mappings to move to the next CREATE ... block
393exec "nmap <buffer> <silent> ]} :call search('".s:ftplugin_sql_objects."', 'W')<CR>"
394exec "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
398exec 'vmap <buffer> <silent> ]} /'.s:ftplugin_sql_objects.'<CR>'
399exec 'vmap <buffer> <silent> [{ ?'.s:ftplugin_sql_objects.'<CR>'
400
401" Mappings to move to the next COMMENT
402"
403" Had to double the \ for the \| separator since this has a special
404" meaning on maps
405let b:comment_leader = '\\(--\\\|\\/\\/\\\|\\*\\\|\\/\\*\\\|\\*\\/\\)'
406" Find the start of the next comment
407let b:comment_start  = '^\\(\\s*'.b:comment_leader.'.*\\n\\)\\@<!'.
408            \ '\\(\\s*'.b:comment_leader.'\\)'
409" Find the end of the previous comment
410let b:comment_end = '\\(^\\s*'.b:comment_leader.'.*\\n\\)'.
411            \ '\\(^\\s*'.b:comment_leader.'\\)\\@!'
412" Skip over the comment
413let b:comment_jump_over  = "call search('".
414            \ '^\\(\\s*'.b:comment_leader.'.*\\n\\)\\@<!'.
415            \ "', 'W')"
416let b:comment_skip_back  = "call search('".
417            \ '^\\(\\s*'.b:comment_leader.'.*\\n\\)\\@<!'.
418            \ "', 'bW')"
419" Move to the start and end of comments
420exec 'nnoremap <silent><buffer> ]" :call search('."'".b:comment_start."'".', "W" )<CR>'
421exec 'nnoremap <silent><buffer> [" :call search('."'".b:comment_end."'".', "W" )<CR>'
422exec 'vnoremap <silent><buffer> ]" :<C-U>exec "normal! gv"<Bar>call search('."'".b:comment_start."'".', "W" )<CR>'
423exec 'vnoremap <silent><buffer> [" :<C-U>exec "normal! gv"<Bar>call search('."'".b:comment_end."'".', "W" )<CR>'
424
425" Comments can be of the form:
426"   /*
427"    *
428"    */
429" or
430"   --
431" or
432"   // 
433setlocal comments=s1:/*,mb:*,ex:*/,:--,://
434
435" Set completion with CTRL-X CTRL-O to autoloaded function.
436if 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
441
442    " This is used by the sqlcomplete.vim plugin
443    " Source it for it's global functions
444    runtime autoload/syntaxcomplete.vim 
445
446    setlocal omnifunc=sqlcomplete#Complete
447    " Prevent the intellisense plugin from loading
448    let b:sql_vis = 1
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.
472        " if has('win32')
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>'
475        " endif
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>'
478    endif
479
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()
487    endif
488endif
489
490let &cpo = s:save_cpo
491
492" vim:sw=4:
493
494