1 " Vim OMNI completion script for SQL
3 " Maintainer: David Fishburn <fishburn@ianywhere.com>
5 " Last Change: Mon Jun 05 2006 3:30:04 PM
6 " Usage: For detailed help
8 " or ":help ft-sql-omni"
9 " or read $VIMRUNTIME/doc/sql.txt
11 " Set completion with CTRL-X CTRL-O to autoloaded function.
12 " This check is in place in case this script is
13 " sourced directly instead of using the autoload feature.
14 if exists('&omnifunc')
15 " Do not set the option if already set since this
16 " results in an E117 warning.
18 setlocal omnifunc=sqlcomplete#Complete
22 if exists('g:loaded_sql_completion')
25 let g:loaded_sql_completion = 50
27 " Maintains filename of dictionary
28 let s:sql_file_table = ""
29 let s:sql_file_procedure = ""
30 let s:sql_file_view = ""
32 " Define various arrays to be used for caching
39 " Used in conjunction with the syntaxcomplete plugin
42 if exists('g:omni_syntax_group_include_sql')
43 let s:save_inc = g:omni_syntax_group_include_sql
45 if exists('g:omni_syntax_group_exclude_sql')
46 let s:save_exc = g:omni_syntax_group_exclude_sql
49 " Used with the column list
50 let s:save_prev_table = ""
52 " Default the option to verify table alias
53 if !exists('g:omni_sql_use_tbl_alias')
54 let g:omni_sql_use_tbl_alias = 'a'
56 " Default syntax items to precache
57 if !exists('g:omni_sql_precache_syntax_groups')
58 let g:omni_sql_precache_syntax_groups = [
67 " Set ignorecase to the ftplugin standard
68 if !exists('g:omni_sql_ignorecase')
69 let g:omni_sql_ignorecase = &ignorecase
71 " During table completion, should the table list also
72 " include the owner name
73 if !exists('g:omni_sql_include_owner')
74 let g:omni_sql_include_owner = 0
75 if exists('g:loaded_dbext')
76 if g:loaded_dbext >= 300
77 " New to dbext 3.00, by default the table lists include the owner
78 " name of the table. This is used when determining how much of
79 " whatever has been typed should be replaced as part of the
81 let g:omni_sql_include_owner = 1
86 " This function is used for the 'omnifunc' option.
87 function! sqlcomplete#Complete(findstart, base)
89 " Default to table name completion
90 let compl_type = 'table'
91 " Allow maps to specify what type of object completion they want
92 if exists('b:sql_compl_type')
93 let compl_type = b:sql_compl_type
96 " First pass through this function determines how much of the line should
97 " be replaced by whatever is chosen from the completion list
99 " Locate the start of the item, including "."
100 let line = getline('.')
101 let start = col('.') - 1
104 " Check if the first character is a ".", for column completion
105 if line[start - 1] == '.'
109 if line[start - 1] =~ '\w'
111 elseif line[start - 1] =~ '\.' &&
112 \ compl_type =~ 'column\|table\|view\|procedure'
113 " If lastword has already been set for column completion
114 " break from the loop, since we do not also want to pickup
115 " a table name if it was also supplied.
116 if lastword != -1 && compl_type == 'column'
119 " If column completion was specified stop at the "." if
120 " a . was specified, otherwise, replace all the way up
121 " to the owner name (if included).
122 if lastword == -1 && compl_type == 'column' && begindot == 1
125 " If omni_sql_include_owner = 0, do not include the table
126 " name as part of the substitution, so break here
128 \ compl_type =~ 'table\|view\|procedure\column_csv' &&
129 \ g:omni_sql_include_owner == 0
139 " Return the column of the last word, which is going to be changed.
140 " Remember the text that comes before it in s:prepended.
145 let s:prepended = strpart(line, start, lastword - start)
149 " Second pass through this function will determine what data to put inside
150 " of the completion list
151 " s:prepended is set by the first pass
152 let base = s:prepended . a:base
154 " Default the completion list to an empty list
157 " Default to table name completion
158 let compl_type = 'table'
159 " Allow maps to specify what type of object completion they want
160 if exists('b:sql_compl_type')
161 let compl_type = b:sql_compl_type
162 unlet b:sql_compl_type
165 if compl_type == 'tableReset'
166 let compl_type = 'table'
170 if compl_type == 'table' ||
171 \ compl_type == 'procedure' ||
172 \ compl_type == 'view'
174 " This type of completion relies upon the dbext.vim plugin
175 if s:SQLCCheck4dbext() == -1
179 " Allow the user to override the dbext plugin to specify whether
180 " the owner/creator should be included in the list
181 let saved_dbext_show_owner = 1
182 if exists('g:dbext_default_dict_show_owner')
183 let saved_dbext_show_owner = g:dbext_default_dict_show_owner
185 let g:dbext_default_dict_show_owner = g:omni_sql_include_owner
187 let compl_type_uc = substitute(compl_type, '\w\+', '\u&', '')
188 if s:sql_file_{compl_type} == ""
189 let s:sql_file_{compl_type} = DB_getDictionaryName(compl_type_uc)
191 let s:sql_file_{compl_type} = DB_getDictionaryName(compl_type_uc)
192 if s:sql_file_{compl_type} != ""
193 if filereadable(s:sql_file_{compl_type})
194 let compl_list = readfile(s:sql_file_{compl_type})
195 " let dic_list = readfile(s:sql_file_{compl_type})
196 " if !empty(dic_list)
197 " for elem in dic_list
198 " let kind = (compl_type=='table'?'m':(compl_type=='procedure'?'f':'v'))
199 " let item = {'word':elem, 'menu':elem, 'kind':kind, 'info':compl_type}
200 " let compl_list += [item]
206 let g:dbext_default_dict_show_owner = saved_dbext_show_owner
207 elseif compl_type =~? 'column'
209 " This type of completion relies upon the dbext.vim plugin
210 if s:SQLCCheck4dbext() == -1
215 " The last time we displayed a column list we stored
216 " the table name. If the user selects a column list
217 " without a table name of alias present, assume they want
218 " the previous column list displayed.
219 let base = s:save_prev_table
226 " Check if the owner/creator has been specified
227 let owner = matchstr( base, '^\zs.*\ze\..*\..*' )
228 let table = matchstr( base, '^\(.*\.\)\?\zs.*\ze\..*' )
229 let column = matchstr( base, '.*\.\zs.*' )
231 " It is pretty well impossible to determine if the user
234 " table.column_prefix
235 " So there are a couple of things we can do to mitigate
237 " 1. Check if the dbext plugin has the option turned
238 " on to even allow owners
239 " 2. Based on 1, if the user is showing a table list
240 " and the DrillIntoTable (using <C-Right>) then
241 " this will be owner.table. In this case, we can
242 " check to see the table.column exists in the
243 " cached table list. If it does, then we have
244 " determined the user has actually chosen
245 " owner.table, not table.column_prefix.
247 if g:omni_sql_include_owner == 1 && owner == ''
248 if filereadable(s:sql_file_table)
249 let tbl_list = readfile(s:sql_file_table)
250 let found = index( tbl_list, ((table != '')?(table.'.'):'').column)
253 " If the table.column was found in the table list, we can safely assume
254 " the owner was not provided and shift the items appropriately.
256 " If the user has indicated not to use table owners at all and
257 " the base ends in a '.' we know they are not providing a column
258 " name, so we can shift the items appropriately.
259 if found != -1 || (g:omni_sql_include_owner == 0 && base !~ '\.$')
268 " Get anything after the . and consider this the table name
269 " If an owner has been specified, then we must consider the
270 " base to be a partial column name
271 " let base = matchstr( base, '^\(.*\.\)\?\zs.*' )
274 let s:save_prev_table = base
277 if compl_type == 'column_csv'
278 " Return one array element, with a comma separated
279 " list of values instead of multiple array entries
280 " for each column in the table.
281 let list_type = 'csv'
284 let compl_list = s:SQLCGetColumns(table, list_type)
286 " If no column prefix has been provided and the table
287 " name was provided, append it to each of the items
289 let compl_list = map(compl_list, "table.'.'.v:val")
291 " If an owner has been provided append it to each of the
293 let compl_list = map(compl_list, "owner.'.'.v:val")
299 if compl_type == 'column_csv'
300 " Join the column array into 1 single element array
301 " but make the columns column separated
302 let compl_list = [join(compl_list, ', ')]
305 elseif compl_type == 'resetCache'
306 " Reset all cached items
313 let msg = "All SQL cached items have been removed."
314 call s:SQLCWarningMsg(msg)
315 " Leave time for the user to read the error message
318 let compl_list = s:SQLCGetSyntaxList(compl_type)
322 " Filter the list based on the first few characters the user
324 let expr = 'v:val '.(g:omni_sql_ignorecase==1?'=~?':'=~#').' "\\(^'.base.'\\|\\([^.]*\\)\\?'.base.'\\)"'
325 let compl_list = filter(deepcopy(compl_list), expr)
328 if exists('b:sql_compl_savefunc') && b:sql_compl_savefunc != ""
329 let &omnifunc = b:sql_compl_savefunc
335 function! sqlcomplete#PreCacheSyntax(...)
336 let syn_group_arr = []
338 let syn_group_arr = a:1
340 let syn_group_arr = g:omni_sql_precache_syntax_groups
342 " For each group specified in the list, precache all
344 if !empty(syn_group_arr)
345 for group_name in syn_group_arr
346 call s:SQLCGetSyntaxList(group_name)
351 function! sqlcomplete#Map(type)
352 " Tell the SQL plugin what you want to complete
353 let b:sql_compl_type=a:type
354 " Record previous omnifunc, if the SQL completion
355 " is being used in conjunction with other filetype
357 if &omnifunc != "" && &omnifunc != 'sqlcomplete#Complete'
358 " Record the previous omnifunc, the plugin
359 " will automatically set this back so that it
360 " does not interfere with other ftplugins settings
361 let b:sql_compl_savefunc=&omnifunc
363 " Set the OMNI func for the SQL completion plugin
364 let &omnifunc='sqlcomplete#Complete'
367 function! sqlcomplete#DrillIntoTable()
368 " If the omni popup window is visible
370 call sqlcomplete#Map('column')
371 " C-Y, makes the currently highlighted entry active
372 " and trigger the omni popup to be redisplayed
373 call feedkeys("\<C-Y>\<C-X>\<C-O>")
376 " If the popup is not visible, simple perform the normal
377 " <C-Right> behaviour
378 exec "normal! \<C-Right>"
384 function! sqlcomplete#DrillOutOfColumns()
385 " If the omni popup window is visible
387 call sqlcomplete#Map('tableReset')
388 " Trigger the omni popup to be redisplayed
389 call feedkeys("\<C-X>\<C-O>")
392 " If the popup is not visible, simple perform the normal
394 exec "normal! \<C-Left>"
400 function! s:SQLCWarningMsg(msg)
406 function! s:SQLCErrorMsg(msg)
412 function! s:SQLCGetSyntaxList(syn_group)
413 let syn_group = a:syn_group
416 " Check if we have already cached the syntax list
417 let list_idx = index(s:syn_list, syn_group, 0, &ignorecase)
419 " Return previously cached value
420 let compl_list = s:syn_value[list_idx]
422 " Request the syntax list items from the
423 " syntax completion plugin
424 if syn_group == 'syntax'
425 " Handle this special case. This allows the user
426 " to indicate they want all the syntax items available,
427 " so do not specify a specific include list.
428 let g:omni_syntax_group_include_sql = ''
430 " The user has specified a specific syntax group
431 let g:omni_syntax_group_include_sql = syn_group
433 let g:omni_syntax_group_exclude_sql = ''
434 let syn_value = OmniSyntaxList()
435 let g:omni_syntax_group_include_sql = s:save_inc
436 let g:omni_syntax_group_exclude_sql = s:save_exc
437 " Cache these values for later use
438 let s:syn_list = add( s:syn_list, syn_group )
439 let s:syn_value = add( s:syn_value, syn_value )
440 let compl_list = syn_value
446 function! s:SQLCCheck4dbext()
447 if !exists('g:loaded_dbext')
448 let msg = "The dbext plugin must be loaded for dynamic SQL completion"
449 call s:SQLCErrorMsg(msg)
450 " Leave time for the user to read the error message
453 elseif g:loaded_dbext < 300
454 let msg = "The dbext plugin must be at least version 3.00 " .
455 \ " for dynamic SQL completion"
456 call s:SQLCErrorMsg(msg)
457 " Leave time for the user to read the error message
464 function! s:SQLCAddAlias(table_name, table_alias, cols)
465 " Strip off the owner if included
466 let table_name = matchstr(a:table_name, '\%(.\{-}\.\)\?\zs\(.*\)' )
467 let table_alias = a:table_alias
470 if g:omni_sql_use_tbl_alias != 'n'
472 if 'da' =~? g:omni_sql_use_tbl_alias
474 " Treat _ as separators since people often use these
475 " for word separators
476 let save_keyword = &iskeyword
477 setlocal iskeyword-=_
479 " Get the first letter of each word
480 " [[:alpha:]] is used instead of \w
481 " to catch extended accented characters
483 let table_alias = substitute(
485 \ '\<[[:alpha:]]\+\>_\?',
486 \ '\=strpart(submatch(0), 0, 1)',
489 " Restore original value
490 let &iskeyword = save_keyword
491 elseif table_name =~ '\u\U'
492 let table_alias = substitute(
493 \ table_name, '\(\u\)\U*', '\1', 'g')
495 let table_alias = strpart(table_name, 0, 1)
500 " Following a word character, make sure there is a . and no spaces
501 let table_alias = substitute(table_alias, '\w\zs\.\?\s*$', '.', '')
502 if 'a' =~? g:omni_sql_use_tbl_alias && a:table_alias == ''
503 let table_alias = inputdialog("Enter table alias:", table_alias)
507 let cols = substitute(cols, '\<\w', table_alias.'&', 'g')
514 function! s:SQLCGetObjectOwner(object)
515 " The owner regex matches a word at the start of the string which is
516 " followed by a dot, but doesn't include the dot in the result.
517 " ^ - from beginning of line
518 " "\? - ignore any quotes
519 " \zs - start the match now
520 " \w\+ - get owner name
521 " \ze - end the match
522 " "\? - ignore any quotes
523 " \. - must by followed by a .
524 let owner = matchstr( a:object, '^"\?\zs\w\+\ze"\?\.' )
528 function! s:SQLCGetColumns(table_name, list_type)
529 " Check if the table name was provided as part of the column name
530 let table_name = matchstr(a:table_name, '^[a-zA-Z0-9_]\+\ze\.\?')
535 if g:loaded_dbext >= 300
536 let saveSettingAlias = DB_listOption('use_tbl_alias')
537 exec 'DBSetOption use_tbl_alias=n'
540 " Check if we have already cached the column list for this table
542 let list_idx = index(s:tbl_name, table_name, 0, &ignorecase)
544 let table_cols = split(s:tbl_cols[list_idx])
546 " Check if we have already cached the column list for this table
547 " by its alias, assuming the table_name provided was actually
548 " the alias for the table instead
552 let list_idx = index(s:tbl_alias, table_name, 0, &ignorecase)
554 let table_alias = table_name
555 let table_name = s:tbl_name[list_idx]
556 let table_cols = split(s:tbl_cols[list_idx])
560 " If we have not found a cached copy of the table
561 " And the table ends in a "." or we are looking for a column list
562 " if list_idx == -1 && (a:table_name =~ '\.' || b:sql_compl_type =~ 'column')
563 " if list_idx == -1 && (a:table_name =~ '\.' || a:list_type =~ 'csv')
567 let saveWScan = &wrapscan
568 let curline = line(".")
569 let curcol = col(".")
571 " Do not let searchs wrap
573 " If . was entered, look at the word just before the .
574 " We are looking for something like this:
578 " So when . is pressed, we need to find 'c'
581 " Search backwards to the beginning of the statement
583 " exec 'silent! normal! v?\<\(select\|update\|delete\|;\)\>'."\n".'"yy'
584 exec 'silent! normal! ?\<\(select\|update\|delete\|;\)\>'."\n"
586 " Start characterwise visual mode
587 " Advance right one character
588 " Search foward until one of the following:
589 " 1. Another select/update/delete statement
590 " 2. A ; at the end of a line (the delimiter)
591 " 3. The end of the file (incase no delimiter)
592 " Yank the visually selected text into the "y register.
593 exec 'silent! normal! vl/\(\<select\>\|\<update\>\|\<delete\>\|;\s*$\|\%$\)'."\n".'"yy'
596 let query = substitute(query, "\n", ' ', 'g')
599 " if query =~? '^\(select\|update\|delete\)'
600 if query =~? '^\(select\)'
602 " \(\(\<\w\+\>\)\.\)\? -
603 " 'from.\{-}' - Starting at the from clause
604 " '\zs\(\(\<\w\+\>\)\.\)\?' - Get the owner name (optional)
605 " '\<\w\+\>\ze' - Get the table name
606 " '\s\+\<'.table_name.'\>' - Followed by the alias
607 " '\s*\.\@!.*' - Cannot be followed by a .
608 " '\(\<where\>\|$\)' - Must be followed by a WHERE clause
609 " '.*' - Exclude the rest of the line in the match
610 let table_name_new = matchstr(@y,
612 \ '\zs\(\(\<\w\+\>\)\.\)\?'.
614 \ '\s\+\%(as\s\+\)\?\<'.
615 \ matchstr(table_name, '.\{-}\ze\.\?$').
618 \ '\(\<where\>\|$\)'.
621 if table_name_new != ''
622 let table_alias = table_name
623 let table_name = table_name_new
625 let list_idx = index(s:tbl_name, table_name, 0, &ignorecase)
627 let table_cols = split(s:tbl_cols[list_idx])
628 let s:tbl_name[list_idx] = table_name
629 let s:tbl_alias[list_idx] = table_alias
631 let list_idx = index(s:tbl_alias, table_name, 0, &ignorecase)
633 let table_cols = split(s:tbl_cols[list_idx])
634 let s:tbl_name[list_idx] = table_name
635 let s:tbl_alias[list_idx] = table_alias
641 " Simply assume it is a table name provided with a . on the end
647 let &wrapscan = saveWScan
649 " Return to previous location
650 call cursor(curline, curcol)
653 if g:loaded_dbext > 300
654 exec 'DBSetOption use_tbl_alias='.saveSettingAlias
657 " Not a SQL statement, do not display a list
663 " Specify silent mode, no messages to the user (tbl, 1)
664 " Specify do not comma separate (tbl, 1, 1)
665 let table_cols_str = DB_getListColumn(table_name, 1, 1)
667 if table_cols_str != ""
668 let s:tbl_name = add( s:tbl_name, table_name )
669 let s:tbl_alias = add( s:tbl_alias, table_alias )
670 let s:tbl_cols = add( s:tbl_cols, table_cols_str )
671 let table_cols = split(table_cols_str)
676 if g:loaded_dbext > 300
677 exec 'DBSetOption use_tbl_alias='.saveSettingAlias
680 " If the user has asked for a comma separate list of column
681 " values, ask the user if they want to prepend each column
682 " with a tablename alias.
683 if a:list_type == 'csv' && !empty(table_cols)
684 let cols = join(table_cols, ', ')
685 let cols = s:SQLCAddAlias(table_name, table_alias, cols)
686 let table_cols = [cols]