1 " Vim OMNI completion script for SQL
3 " Maintainer: David Fishburn <dfishburn dot vim at gmail dot com>
5 " Last Change: 2010 Jun 11
6 " Usage: For detailed help
8 " or ":help ft-sql-omni"
9 " or read $VIMRUNTIME/doc/sql.txt
13 " Updated PreCacheSyntax()
14 " - Now returns a List of the syntax items it finds.
15 " This allows other plugins / scripts to use this list for their own
16 " purposes. In this case XPTemplate can use them for a Choose list.
17 " - Verifies the parameters are the correct type and displays a
19 " - Verifies the parameters are the correct type and displays a
21 " Updated SQLCWarningMsg()
22 " - Prepends warning message with SQLComplete so you know who issued
24 " Updated SQLCErrorMsg()
25 " - Prepends error message with SQLComplete so you know who issued
29 " This change removes some of the support for tables with spaces in their
30 " names in order to simplify the regexes used to pull out query table
31 " aliases for more robust table name and column name code completion.
32 " Full support for "table names with spaces" can be added in again
36 " Incorrectly re-executed the g:ftplugin_sql_omni_key_right and g:ftplugin_sql_omni_key_left
37 " when drilling in and out of a column list for a table.
40 " Better handling of object names
43 " Supports object names with spaces "my table name"
45 " Set completion with CTRL-X CTRL-O to autoloaded function.
46 " This check is in place in case this script is
47 " sourced directly instead of using the autoload feature.
48 if exists('&omnifunc')
49 " Do not set the option if already set since this
50 " results in an E117 warning.
52 setlocal omnifunc=sqlcomplete#Complete
56 if exists('g:loaded_sql_completion')
59 let g:loaded_sql_completion = 100
61 " Maintains filename of dictionary
62 let s:sql_file_table = ""
63 let s:sql_file_procedure = ""
64 let s:sql_file_view = ""
66 " Define various arrays to be used for caching
73 " Used in conjunction with the syntaxcomplete plugin
76 if exists('g:omni_syntax_group_include_sql')
77 let s:save_inc = g:omni_syntax_group_include_sql
79 if exists('g:omni_syntax_group_exclude_sql')
80 let s:save_exc = g:omni_syntax_group_exclude_sql
83 " Used with the column list
84 let s:save_prev_table = ""
86 " Default the option to verify table alias
87 if !exists('g:omni_sql_use_tbl_alias')
88 let g:omni_sql_use_tbl_alias = 'a'
90 " Default syntax items to precache
91 if !exists('g:omni_sql_precache_syntax_groups')
92 let g:omni_sql_precache_syntax_groups = [
101 " Set ignorecase to the ftplugin standard
102 if !exists('g:omni_sql_ignorecase')
103 let g:omni_sql_ignorecase = &ignorecase
105 " During table completion, should the table list also
106 " include the owner name
107 if !exists('g:omni_sql_include_owner')
108 let g:omni_sql_include_owner = 0
109 if exists('g:loaded_dbext')
110 if g:loaded_dbext >= 300
111 " New to dbext 3.00, by default the table lists include the owner
112 " name of the table. This is used when determining how much of
113 " whatever has been typed should be replaced as part of the
115 let g:omni_sql_include_owner = 1
120 " This function is used for the 'omnifunc' option.
121 function! sqlcomplete#Complete(findstart, base)
123 " Default to table name completion
124 let compl_type = 'table'
125 " Allow maps to specify what type of object completion they want
126 if exists('b:sql_compl_type')
127 let compl_type = b:sql_compl_type
130 " First pass through this function determines how much of the line should
131 " be replaced by whatever is chosen from the completion list
133 " Locate the start of the item, including "."
134 let line = getline('.')
135 let start = col('.') - 1
138 " Check if the first character is a ".", for column completion
139 if line[start - 1] == '.'
143 " Additional code was required to handle objects which
144 " can contain spaces like "my table name".
145 if line[start - 1] !~ '\(\w\|\.\)'
146 " If the previous character is not a period or word character
148 " elseif line[start - 1] =~ '\(\w\|\s\+\)'
150 elseif line[start - 1] =~ '\w'
151 " If the previous character is word character continue back
153 elseif line[start - 1] =~ '\.' &&
154 \ compl_type =~ 'column\|table\|view\|procedure'
155 " If the previous character is a period and we are completing
156 " an object which can be specified with a period like this:
157 " table_name.column_name
158 " owner_name.table_name
160 " If lastword has already been set for column completion
161 " break from the loop, since we do not also want to pickup
162 " a table name if it was also supplied.
163 if lastword != -1 && compl_type == 'column'
166 " If column completion was specified stop at the "." if
167 " a . was specified, otherwise, replace all the way up
168 " to the owner name (if included).
169 if lastword == -1 && compl_type == 'column' && begindot == 1
172 " If omni_sql_include_owner = 0, do not include the table
173 " name as part of the substitution, so break here
175 \ compl_type =~ 'table\|view\|procedure\column_csv' &&
176 \ g:omni_sql_include_owner == 0
186 " Return the column of the last word, which is going to be changed.
187 " Remember the text that comes before it in s:prepended.
192 let s:prepended = strpart(line, start, lastword - start)
196 " Second pass through this function will determine what data to put inside
197 " of the completion list
198 " s:prepended is set by the first pass
199 let base = s:prepended . a:base
201 " Default the completion list to an empty list
204 " Default to table name completion
205 let compl_type = 'table'
206 " Allow maps to specify what type of object completion they want
207 if exists('b:sql_compl_type')
208 let compl_type = b:sql_compl_type
209 unlet b:sql_compl_type
212 if compl_type == 'tableReset'
213 let compl_type = 'table'
217 if compl_type == 'table' ||
218 \ compl_type == 'procedure' ||
219 \ compl_type == 'view'
221 " This type of completion relies upon the dbext.vim plugin
222 if s:SQLCCheck4dbext() == -1
226 " Allow the user to override the dbext plugin to specify whether
227 " the owner/creator should be included in the list
228 if g:loaded_dbext >= 300
229 let saveSetting = DB_listOption('dict_show_owner')
230 exec 'DBSetOption dict_show_owner='.(g:omni_sql_include_owner==1?'1':'0')
233 let compl_type_uc = substitute(compl_type, '\w\+', '\u&', '')
234 " Same call below, no need to do it twice
235 " if s:sql_file_{compl_type} == ""
236 " let s:sql_file_{compl_type} = DB_getDictionaryName(compl_type_uc)
238 let s:sql_file_{compl_type} = DB_getDictionaryName(compl_type_uc)
239 if s:sql_file_{compl_type} != ""
240 if filereadable(s:sql_file_{compl_type})
241 let compl_list = readfile(s:sql_file_{compl_type})
245 if g:loaded_dbext > 300
246 exec 'DBSetOption dict_show_owner='.saveSetting
248 elseif compl_type =~? 'column'
250 " This type of completion relies upon the dbext.vim plugin
251 if s:SQLCCheck4dbext() == -1
256 " The last time we displayed a column list we stored
257 " the table name. If the user selects a column list
258 " without a table name of alias present, assume they want
259 " the previous column list displayed.
260 let base = s:save_prev_table
267 " Check if the owner/creator has been specified
268 let owner = matchstr( base, '^\zs.*\ze\..*\..*' )
269 let table = matchstr( base, '^\(.*\.\)\?\zs.*\ze\..*' )
270 let column = matchstr( base, '.*\.\zs.*' )
272 " It is pretty well impossible to determine if the user
275 " table.column_prefix
276 " So there are a couple of things we can do to mitigate
278 " 1. Check if the dbext plugin has the option turned
279 " on to even allow owners
280 " 2. Based on 1, if the user is showing a table list
281 " and the DrillIntoTable (using <Right>) then
282 " this will be owner.table. In this case, we can
283 " check to see the table.column exists in the
284 " cached table list. If it does, then we have
285 " determined the user has actually chosen
286 " owner.table, not table.column_prefix.
288 if g:omni_sql_include_owner == 1 && owner == ''
289 if filereadable(s:sql_file_table)
290 let tbl_list = readfile(s:sql_file_table)
291 let found = index( tbl_list, ((table != '')?(table.'.'):'').column)
294 " If the table.column was found in the table list, we can safely assume
295 " the owner was not provided and shift the items appropriately.
297 " If the user has indicated not to use table owners at all and
298 " the base ends in a '.' we know they are not providing a column
299 " name, so we can shift the items appropriately.
300 if found != -1 || (g:omni_sql_include_owner == 0 && base !~ '\.$')
309 " Get anything after the . and consider this the table name
310 " If an owner has been specified, then we must consider the
311 " base to be a partial column name
312 " let base = matchstr( base, '^\(.*\.\)\?\zs.*' )
315 let s:save_prev_table = base
318 if compl_type == 'column_csv'
319 " Return one array element, with a comma separated
320 " list of values instead of multiple array entries
321 " for each column in the table.
322 let list_type = 'csv'
325 let compl_list = s:SQLCGetColumns(table, list_type)
327 " If no column prefix has been provided and the table
328 " name was provided, append it to each of the items
330 let compl_list = map(compl_list, "table.'.'.v:val")
332 " If an owner has been provided append it to each of the
334 let compl_list = map(compl_list, "owner.'.'.v:val")
340 if compl_type == 'column_csv'
341 " Join the column array into 1 single element array
342 " but make the columns column separated
343 let compl_list = [join(compl_list, ', ')]
346 elseif compl_type == 'resetCache'
347 " Reset all cached items
354 let msg = "All SQL cached items have been removed."
355 call s:SQLCWarningMsg(msg)
356 " Leave time for the user to read the error message
359 let compl_list = s:SQLCGetSyntaxList(compl_type)
363 " Filter the list based on the first few characters the user entered.
364 " Check if the text matches at the beginning
366 " Match to a owner.table or alias.column type match
368 " Handle names with spaces "my table name"
369 let expr = 'v:val '.(g:omni_sql_ignorecase==1?'=~?':'=~#').' "\\(^'.base.'\\|^\\(\\w\\+\\.\\)\\?'.base.'\\)"'
370 " let expr = 'v:val '.(g:omni_sql_ignorecase==1?'=~?':'=~#').' "\\(^'.base.'\\)"'
371 " let expr = 'v:val '.(g:omni_sql_ignorecase==1?'=~?':'=~#').' "\\(^'.base.'\\|\\(\\.\\)\\?'.base.'\\)"'
372 " let expr = 'v:val '.(g:omni_sql_ignorecase==1?'=~?':'=~#').' "\\(^'.base.'\\|\\([^.]*\\)\\?'.base.'\\)"'
373 let compl_list = filter(deepcopy(compl_list), expr)
376 if exists('b:sql_compl_savefunc') && b:sql_compl_savefunc != ""
377 let &omnifunc = b:sql_compl_savefunc
383 function! sqlcomplete#PreCacheSyntax(...)
384 let syn_group_arr = []
389 call s:SQLCWarningMsg("Parameter is not a list. Example:['syntaxGroup1', 'syntaxGroup2']")
392 let syn_group_arr = a:1
394 let syn_group_arr = g:omni_sql_precache_syntax_groups
396 " For each group specified in the list, precache all
398 if !empty(syn_group_arr)
399 for group_name in syn_group_arr
400 let syn_items = extend( syn_items, s:SQLCGetSyntaxList(group_name) )
407 function! sqlcomplete#ResetCacheSyntax(...)
408 let syn_group_arr = []
412 call s:SQLCWarningMsg("Parameter is not a list. Example:['syntaxGroup1', 'syntaxGroup2']")
415 let syn_group_arr = a:1
417 let syn_group_arr = g:omni_sql_precache_syntax_groups
419 " For each group specified in the list, precache all
421 if !empty(syn_group_arr)
422 for group_name in syn_group_arr
423 let list_idx = index(s:syn_list, group_name, 0, &ignorecase)
425 " Remove from list of groups
426 call remove( s:syn_list, list_idx )
427 " Remove from list of keywords
428 call remove( s:syn_value, list_idx )
434 function! sqlcomplete#Map(type)
435 " Tell the SQL plugin what you want to complete
436 let b:sql_compl_type=a:type
437 " Record previous omnifunc, if the SQL completion
438 " is being used in conjunction with other filetype
440 if &omnifunc != "" && &omnifunc != 'sqlcomplete#Complete'
441 " Record the previous omnifunc, the plugin
442 " will automatically set this back so that it
443 " does not interfere with other ftplugins settings
444 let b:sql_compl_savefunc=&omnifunc
446 " Set the OMNI func for the SQL completion plugin
447 let &omnifunc='sqlcomplete#Complete'
450 function! sqlcomplete#DrillIntoTable()
451 " If the omni popup window is visible
453 call sqlcomplete#Map('column')
454 " C-Y, makes the currently highlighted entry active
455 " and trigger the omni popup to be redisplayed
456 call feedkeys("\<C-Y>\<C-X>\<C-O>", 'n')
458 " If the popup is not visible, simple perform the normal
460 " Must use exec since they key must be preceeded by "\"
461 " or feedkeys will simply push each character of the string
462 " rather than the "key press".
463 exec 'call feedkeys("\'.g:ftplugin_sql_omni_key_right.'", "n")'
468 function! sqlcomplete#DrillOutOfColumns()
469 " If the omni popup window is visible
471 call sqlcomplete#Map('tableReset')
472 " Trigger the omni popup to be redisplayed
473 call feedkeys("\<C-X>\<C-O>")
475 " If the popup is not visible, simple perform the normal
477 " Must use exec since they key must be preceeded by "\"
478 " or feedkeys will simply push each character of the string
479 " rather than the "key press".
480 exec 'call feedkeys("\'.g:ftplugin_sql_omni_key_left.'", "n")'
485 function! s:SQLCWarningMsg(msg)
487 echomsg 'SQLComplete:'.a:msg
491 function! s:SQLCErrorMsg(msg)
493 echomsg 'SQLComplete:'.a:msg
497 function! s:SQLCGetSyntaxList(syn_group)
498 let syn_group = a:syn_group
501 " Check if we have already cached the syntax list
502 let list_idx = index(s:syn_list, syn_group, 0, &ignorecase)
504 " Return previously cached value
505 let compl_list = s:syn_value[list_idx]
507 " Request the syntax list items from the
508 " syntax completion plugin
509 if syn_group == 'syntax'
510 " Handle this special case. This allows the user
511 " to indicate they want all the syntax items available,
512 " so do not specify a specific include list.
513 let g:omni_syntax_group_include_sql = ''
515 " The user has specified a specific syntax group
516 let g:omni_syntax_group_include_sql = syn_group
518 let g:omni_syntax_group_exclude_sql = ''
519 let syn_value = syntaxcomplete#OmniSyntaxList()
520 let g:omni_syntax_group_include_sql = s:save_inc
521 let g:omni_syntax_group_exclude_sql = s:save_exc
522 " Cache these values for later use
523 let s:syn_list = add( s:syn_list, syn_group )
524 let s:syn_value = add( s:syn_value, syn_value )
525 let compl_list = syn_value
531 function! s:SQLCCheck4dbext()
532 if !exists('g:loaded_dbext')
533 let msg = "The dbext plugin must be loaded for dynamic SQL completion"
534 call s:SQLCErrorMsg(msg)
535 " Leave time for the user to read the error message
538 elseif g:loaded_dbext < 600
539 let msg = "The dbext plugin must be at least version 5.30 " .
540 \ " for dynamic SQL completion"
541 call s:SQLCErrorMsg(msg)
542 " Leave time for the user to read the error message
549 function! s:SQLCAddAlias(table_name, table_alias, cols)
550 " Strip off the owner if included
551 let table_name = matchstr(a:table_name, '\%(.\{-}\.\)\?\zs\(.*\)' )
552 let table_alias = a:table_alias
555 if g:omni_sql_use_tbl_alias != 'n'
557 if 'da' =~? g:omni_sql_use_tbl_alias
559 " Treat _ as separators since people often use these
560 " for word separators
561 let save_keyword = &iskeyword
562 setlocal iskeyword-=_
564 " Get the first letter of each word
565 " [[:alpha:]] is used instead of \w
566 " to catch extended accented characters
568 let table_alias = substitute(
570 \ '\<[[:alpha:]]\+\>_\?',
571 \ '\=strpart(submatch(0), 0, 1)',
574 " Restore original value
575 let &iskeyword = save_keyword
576 elseif table_name =~ '\u\U'
577 let table_alias = substitute(
578 \ table_name, '\(\u\)\U*', '\1', 'g')
580 let table_alias = strpart(table_name, 0, 1)
585 " Following a word character, make sure there is a . and no spaces
586 let table_alias = substitute(table_alias, '\w\zs\.\?\s*$', '.', '')
587 if 'a' =~? g:omni_sql_use_tbl_alias && a:table_alias == ''
588 let table_alias = inputdialog("Enter table alias:", table_alias)
592 let cols = substitute(cols, '\<\w', table_alias.'&', 'g')
599 function! s:SQLCGetObjectOwner(object)
600 " The owner regex matches a word at the start of the string which is
601 " followed by a dot, but doesn't include the dot in the result.
602 " ^ - from beginning of line
603 " \("\|\[\)\? - ignore any quotes
604 " \zs - start the match now
605 " .\{-} - get owner name
606 " \ze - end the match
607 " \("\|\[\)\? - ignore any quotes
608 " \. - must by followed by a .
609 " let owner = matchstr( a:object, '^\s*\zs.*\ze\.' )
610 let owner = matchstr( a:object, '^\("\|\[\)\?\zs\.\{-}\ze\("\|\]\)\?\.' )
614 function! s:SQLCGetColumns(table_name, list_type)
615 " Check if the table name was provided as part of the column name
616 let table_name = matchstr(a:table_name, '^["\[\]a-zA-Z0-9_ ]\+\ze\.\?')
621 let table_name = substitute(table_name, '\s*\(.\{-}\)\s*$', '\1', 'g')
623 " If the table name was given as:
625 let table_name = substitute(table_name, '^\c\(WHERE\|AND\|OR\)\s\+', '', '')
626 if g:loaded_dbext >= 300
627 let saveSettingAlias = DB_listOption('use_tbl_alias')
628 exec 'DBSetOption use_tbl_alias=n'
631 let table_name_stripped = substitute(table_name, '["\[\]]*', '', 'g')
633 " Check if we have already cached the column list for this table
635 let list_idx = index(s:tbl_name, table_name_stripped, 0, &ignorecase)
637 let table_cols = split(s:tbl_cols[list_idx], '\n')
639 " Check if we have already cached the column list for this table
640 " by its alias, assuming the table_name provided was actually
641 " the alias for the table instead
645 let list_idx = index(s:tbl_alias, table_name_stripped, 0, &ignorecase)
647 let table_alias = table_name_stripped
648 let table_name = s:tbl_name[list_idx]
649 let table_cols = split(s:tbl_cols[list_idx], '\n')
653 " If we have not found a cached copy of the table
654 " And the table ends in a "." or we are looking for a column list
655 " if list_idx == -1 && (a:table_name =~ '\.' || b:sql_compl_type =~ 'column')
656 " if list_idx == -1 && (a:table_name =~ '\.' || a:list_type =~ 'csv')
660 let saveWScan = &wrapscan
661 let curline = line(".")
662 let curcol = col(".")
664 " Do not let searchs wrap
666 " If . was entered, look at the word just before the .
667 " We are looking for something like this:
671 " So when . is pressed, we need to find 'c'
674 " Search backwards to the beginning of the statement
676 " exec 'silent! normal! v?\<\(select\|update\|delete\|;\)\>'."\n".'"yy'
677 exec 'silent! normal! ?\<\c\(select\|update\|delete\|;\)\>'."\n"
679 " Start characterwise visual mode
680 " Advance right one character
681 " Search foward until one of the following:
682 " 1. Another select/update/delete statement
683 " 2. A ; at the end of a line (the delimiter)
684 " 3. The end of the file (incase no delimiter)
685 " Yank the visually selected text into the "y register.
686 exec 'silent! normal! vl/\c\(\<select\>\|\<update\>\|\<delete\>\|;\s*$\|\%$\)'."\n".'"yy'
689 let query = substitute(query, "\n", ' ', 'g')
692 " if query =~? '^\c\(select\)'
693 if query =~? '^\(select\|update\|delete\)'
695 " \(\(\<\w\+\>\)\.\)\? -
696 " '\c\(from\|join\|,\).\{-}' - Starting at the from clause (case insensitive)
697 " '\zs\(\(\<\w\+\>\)\.\)\?' - Get the owner name (optional)
698 " '\<\w\+\>\ze' - Get the table name
699 " '\s\+\<'.table_name.'\>' - Followed by the alias
700 " '\s*\.\@!.*' - Cannot be followed by a .
701 " '\(\<where\>\|$\)' - Must be followed by a WHERE clause
702 " '.*' - Exclude the rest of the line in the match
703 " let table_name_new = matchstr(@y,
704 " \ '\c\(from\|join\|,\).\{-}'.
705 " \ '\zs\(\("\|\[\)\?.\{-}\("\|\]\)\.\)\?'.
706 " \ '\("\|\[\)\?.\{-}\("\|\]\)\?\ze'.
707 " \ '\s\+\%(as\s\+\)\?\<'.
708 " \ matchstr(table_name, '.\{-}\ze\.\?$').
711 " \ '\(\<where\>\|$\)'.
714 let table_name_new = matchstr(@y,
715 \ '\c\(\<from\>\|\<join\>\|,\)\s*'.
716 \ '\zs\(\("\|\[\)\?\w\+\("\|\]\)\?\.\)\?'.
717 \ '\("\|\[\)\?\w\+\("\|\]\)\?\ze'.
718 \ '\s\+\%(as\s\+\)\?\<'.
719 \ matchstr(table_name, '.\{-}\ze\.\?$').
722 \ '\(\<where\>\|$\)'.
726 if table_name_new != ''
727 let table_alias = table_name
728 let table_name = matchstr( table_name_new, '^\(.*\.\)\?\zs.*\ze' )
730 let list_idx = index(s:tbl_name, table_name, 0, &ignorecase)
732 let table_cols = split(s:tbl_cols[list_idx])
733 let s:tbl_name[list_idx] = table_name
734 let s:tbl_alias[list_idx] = table_alias
736 let list_idx = index(s:tbl_alias, table_name, 0, &ignorecase)
738 let table_cols = split(s:tbl_cols[list_idx])
739 let s:tbl_name[list_idx] = table_name
740 let s:tbl_alias[list_idx] = table_alias
746 " Simply assume it is a table name provided with a . on the end
752 let &wrapscan = saveWScan
754 " Return to previous location
755 call cursor(curline, curcol)
758 if g:loaded_dbext > 300
759 exec 'DBSetOption use_tbl_alias='.saveSettingAlias
762 " Not a SQL statement, do not display a list
768 " Specify silent mode, no messages to the user (tbl, 1)
769 " Specify do not comma separate (tbl, 1, 1)
770 let table_cols_str = DB_getListColumn(table_name, 1, 1)
772 if table_cols_str != ""
773 let s:tbl_name = add( s:tbl_name, table_name )
774 let s:tbl_alias = add( s:tbl_alias, table_alias )
775 let s:tbl_cols = add( s:tbl_cols, table_cols_str )
776 let table_cols = split(table_cols_str, '\n')
781 if g:loaded_dbext > 300
782 exec 'DBSetOption use_tbl_alias='.saveSettingAlias
785 " If the user has asked for a comma separate list of column
786 " values, ask the user if they want to prepend each column
787 " with a tablename alias.
788 if a:list_type == 'csv' && !empty(table_cols)
789 let cols = join(table_cols, ', ')
790 let cols = s:SQLCAddAlias(table_name, table_alias, cols)
791 let table_cols = [cols]