| blob | eecb3260febc888a12a2493c908bf7f641ca4fb4 |
1 " Vim OMNI completion script for SQL
2 " Language: SQL
3 " Maintainer: David Fishburn <fishburn@ianywhere.com>
4 " Version: 6.0
5 " Last Change: Thu 03 Apr 2008 10:37:54 PM Eastern Daylight Time
6 " Usage: For detailed help
7 " ":help sql.txt"
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.
17 if &omnifunc == ""
18 setlocal omnifunc=sqlcomplete#Complete
19 endif
20 endif
22 if exists('g:loaded_sql_completion')
23 finish
24 endif
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
33 let s:tbl_name = []
34 let s:tbl_alias = []
35 let s:tbl_cols = []
36 let s:syn_list = []
37 let s:syn_value = []
39 " Used in conjunction with the syntaxcomplete plugin
40 let s:save_inc = ""
41 let s:save_exc = ""
42 if exists('g:omni_syntax_group_include_sql')
43 let s:save_inc = g:omni_syntax_group_include_sql
44 endif
45 if exists('g:omni_syntax_group_exclude_sql')
46 let s:save_exc = g:omni_syntax_group_exclude_sql
47 endif
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'
55 endif
56 " Default syntax items to precache
57 if !exists('g:omni_sql_precache_syntax_groups')
58 let g:omni_sql_precache_syntax_groups = [
59 \ 'syntax',
60 \ 'sqlKeyword',
61 \ 'sqlFunction',
62 \ 'sqlOption',
63 \ 'sqlType',
64 \ 'sqlStatement'
65 \ ]
66 endif
67 " Set ignorecase to the ftplugin standard
68 if !exists('g:omni_sql_ignorecase')
69 let g:omni_sql_ignorecase = &ignorecase
70 endif
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
80 " code replacement.
81 let g:omni_sql_include_owner = 1
82 endif
83 endif
84 endif
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
94 endif
96 " First pass through this function determines how much of the line should
97 " be replaced by whatever is chosen from the completion list
98 if a:findstart
99 " Locate the start of the item, including "."
100 let line = getline('.')
101 let start = col('.') - 1
102 let lastword = -1
103 let begindot = 0
104 " Check if the first character is a ".", for column completion
105 if line[start - 1] == '.'
106 let begindot = 1
107 endif
108 while start > 0
109 if line[start - 1] =~ '\(\w\|\s\+\)'
110 let start -= 1
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'
117 break
118 endif
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
123 let lastword = start
124 endif
125 " If omni_sql_include_owner = 0, do not include the table
126 " name as part of the substitution, so break here
127 if lastword == -1 &&
128 \ compl_type =~ 'table\|view\|procedure\column_csv' &&
129 \ g:omni_sql_include_owner == 0
130 let lastword = start
131 break
132 endif
133 let start -= 1
134 else
135 break
136 endif
137 endwhile
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.
141 if lastword == -1
142 let s:prepended = ''
143 return start
144 endif
145 let s:prepended = strpart(line, start, lastword - start)
146 return lastword
147 endif
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
155 let compl_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
163 endif
165 if compl_type == 'tableReset'
166 let compl_type = 'table'
167 let base = ''
168 endif
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
176 return []
177 endif
179 " Allow the user to override the dbext plugin to specify whether
180 " the owner/creator should be included in the list
181 if g:loaded_dbext >= 300
182 let saveSetting = DB_listOption('dict_show_owner')
183 exec 'DBSetOption dict_show_owner='.(g:omni_sql_include_owner==1?'1':'0')
184 endif
186 let compl_type_uc = substitute(compl_type, '\w\+', '\u&', '')
187 if s:sql_file_{compl_type} == ""
188 let s:sql_file_{compl_type} = DB_getDictionaryName(compl_type_uc)
189 endif
190 let s:sql_file_{compl_type} = DB_getDictionaryName(compl_type_uc)
191 if s:sql_file_{compl_type} != ""
192 if filereadable(s:sql_file_{compl_type})
193 let compl_list = readfile(s:sql_file_{compl_type})
194 endif
195 endif
197 if g:loaded_dbext > 300
198 exec 'DBSetOption dict_show_owner='.saveSetting
199 endif
200 elseif compl_type =~? 'column'
202 " This type of completion relies upon the dbext.vim plugin
203 if s:SQLCCheck4dbext() == -1
204 return []
205 endif
207 if base == ""
208 " The last time we displayed a column list we stored
209 " the table name. If the user selects a column list
210 " without a table name of alias present, assume they want
211 " the previous column list displayed.
212 let base = s:save_prev_table
213 endif
215 let owner = ''
216 let column = ''
218 if base =~ '\.'
219 " Check if the owner/creator has been specified
220 let owner = matchstr( base, '^\zs.*\ze\..*\..*' )
221 let table = matchstr( base, '^\(.*\.\)\?\zs.*\ze\..*' )
222 let column = matchstr( base, '.*\.\zs.*' )
224 " It is pretty well impossible to determine if the user
225 " has entered:
226 " owner.table
227 " table.column_prefix
228 " So there are a couple of things we can do to mitigate
229 " this issue.
230 " 1. Check if the dbext plugin has the option turned
231 " on to even allow owners
232 " 2. Based on 1, if the user is showing a table list
233 " and the DrillIntoTable (using <C-Right>) then
234 " this will be owner.table. In this case, we can
235 " check to see the table.column exists in the
236 " cached table list. If it does, then we have
237 " determined the user has actually chosen
238 " owner.table, not table.column_prefix.
239 let found = -1
240 if g:omni_sql_include_owner == 1 && owner == ''
241 if filereadable(s:sql_file_table)
242 let tbl_list = readfile(s:sql_file_table)
243 let found = index( tbl_list, ((table != '')?(table.'.'):'').column)
244 endif
245 endif
246 " If the table.column was found in the table list, we can safely assume
247 " the owner was not provided and shift the items appropriately.
248 " OR
249 " If the user has indicated not to use table owners at all and
250 " the base ends in a '.' we know they are not providing a column
251 " name, so we can shift the items appropriately.
252 if found != -1 || (g:omni_sql_include_owner == 0 && base !~ '\.$')
253 let owner = table
254 let table = column
255 let column = ''
256 endif
257 else
258 let table = base
259 endif
261 " Get anything after the . and consider this the table name
262 " If an owner has been specified, then we must consider the
263 " base to be a partial column name
264 " let base = matchstr( base, '^\(.*\.\)\?\zs.*' )
266 if table != ""
267 let s:save_prev_table = base
268 let list_type = ''
270 if compl_type == 'column_csv'
271 " Return one array element, with a comma separated
272 " list of values instead of multiple array entries
273 " for each column in the table.
274 let list_type = 'csv'
275 endif
277 let compl_list = s:SQLCGetColumns(table, list_type)
278 if column != ''
279 " If no column prefix has been provided and the table
280 " name was provided, append it to each of the items
281 " returned.
282 let compl_list = map(compl_list, "table.'.'.v:val")
283 if owner != ''
284 " If an owner has been provided append it to each of the
285 " items returned.
286 let compl_list = map(compl_list, "owner.'.'.v:val")
287 endif
288 else
289 let base = ''
290 endif
292 if compl_type == 'column_csv'
293 " Join the column array into 1 single element array
294 " but make the columns column separated
295 let compl_list = [join(compl_list, ', ')]
296 endif
297 endif
298 elseif compl_type == 'resetCache'
299 " Reset all cached items
300 let s:tbl_name = []
301 let s:tbl_alias = []
302 let s:tbl_cols = []
303 let s:syn_list = []
304 let s:syn_value = []
306 let msg = "All SQL cached items have been removed."
307 call s:SQLCWarningMsg(msg)
308 " Leave time for the user to read the error message
309 :sleep 2
310 else
311 let compl_list = s:SQLCGetSyntaxList(compl_type)
312 endif
314 if base != ''
315 " Filter the list based on the first few characters the user
316 " entered
317 let expr = 'v:val '.(g:omni_sql_ignorecase==1?'=~?':'=~#').' "\\(^'.base.'\\|\\([^.]*\\)\\?'.base.'\\)"'
318 let compl_list = filter(deepcopy(compl_list), expr)
319 endif
321 if exists('b:sql_compl_savefunc') && b:sql_compl_savefunc != ""
322 let &omnifunc = b:sql_compl_savefunc
323 endif
325 return compl_list
326 endfunc
328 function! sqlcomplete#PreCacheSyntax(...)
329 let syn_group_arr = []
330 if a:0 > 0
331 let syn_group_arr = a:1
332 else
333 let syn_group_arr = g:omni_sql_precache_syntax_groups
334 endif
335 " For each group specified in the list, precache all
336 " the sytnax items.
337 if !empty(syn_group_arr)
338 for group_name in syn_group_arr
339 call s:SQLCGetSyntaxList(group_name)
340 endfor
341 endif
342 endfunction
344 function! sqlcomplete#Map(type)
345 " Tell the SQL plugin what you want to complete
346 let b:sql_compl_type=a:type
347 " Record previous omnifunc, if the SQL completion
348 " is being used in conjunction with other filetype
349 " completion plugins
350 if &omnifunc != "" && &omnifunc != 'sqlcomplete#Complete'
351 " Record the previous omnifunc, the plugin
352 " will automatically set this back so that it
353 " does not interfere with other ftplugins settings
354 let b:sql_compl_savefunc=&omnifunc
355 endif
356 " Set the OMNI func for the SQL completion plugin
357 let &omnifunc='sqlcomplete#Complete'
358 endfunction
360 function! sqlcomplete#DrillIntoTable()
361 " If the omni popup window is visible
362 if pumvisible()
363 call sqlcomplete#Map('column')
364 " C-Y, makes the currently highlighted entry active
365 " and trigger the omni popup to be redisplayed
366 call feedkeys("\<C-Y>\<C-X>\<C-O>")
367 else
368 if has('win32')
369 " If the popup is not visible, simple perform the normal
370 " <C-Right> behaviour
371 exec "normal! \<C-Right>"
372 endif
373 endif
374 return ""
375 endfunction
377 function! sqlcomplete#DrillOutOfColumns()
378 " If the omni popup window is visible
379 if pumvisible()
380 call sqlcomplete#Map('tableReset')
381 " Trigger the omni popup to be redisplayed
382 call feedkeys("\<C-X>\<C-O>")
383 else
384 if has('win32')
385 " If the popup is not visible, simple perform the normal
386 " <C-Left> behaviour
387 exec "normal! \<C-Left>"
388 endif
389 endif
390 return ""
391 endfunction
393 function! s:SQLCWarningMsg(msg)
394 echohl WarningMsg
395 echomsg a:msg
396 echohl None
397 endfunction
399 function! s:SQLCErrorMsg(msg)
400 echohl ErrorMsg
401 echomsg a:msg
402 echohl None
403 endfunction
405 function! s:SQLCGetSyntaxList(syn_group)
406 let syn_group = a:syn_group
407 let compl_list = []
409 " Check if we have already cached the syntax list
410 let list_idx = index(s:syn_list, syn_group, 0, &ignorecase)
411 if list_idx > -1
412 " Return previously cached value
413 let compl_list = s:syn_value[list_idx]
414 else
415 " Request the syntax list items from the
416 " syntax completion plugin
417 if syn_group == 'syntax'
418 " Handle this special case. This allows the user
419 " to indicate they want all the syntax items available,
420 " so do not specify a specific include list.
421 let g:omni_syntax_group_include_sql = ''
422 else
423 " The user has specified a specific syntax group
424 let g:omni_syntax_group_include_sql = syn_group
425 endif
426 let g:omni_syntax_group_exclude_sql = ''
427 let syn_value = OmniSyntaxList()
428 let g:omni_syntax_group_include_sql = s:save_inc
429 let g:omni_syntax_group_exclude_sql = s:save_exc
430 " Cache these values for later use
431 let s:syn_list = add( s:syn_list, syn_group )
432 let s:syn_value = add( s:syn_value, syn_value )
433 let compl_list = syn_value
434 endif
436 return compl_list
437 endfunction
439 function! s:SQLCCheck4dbext()
440 if !exists('g:loaded_dbext')
441 let msg = "The dbext plugin must be loaded for dynamic SQL completion"
442 call s:SQLCErrorMsg(msg)
443 " Leave time for the user to read the error message
444 :sleep 2
445 return -1
446 elseif g:loaded_dbext < 600
447 let msg = "The dbext plugin must be at least version 5.30 " .
448 \ " for dynamic SQL completion"
449 call s:SQLCErrorMsg(msg)
450 " Leave time for the user to read the error message
451 :sleep 2
452 return -1
453 endif
454 return 1
455 endfunction
457 function! s:SQLCAddAlias(table_name, table_alias, cols)
458 " Strip off the owner if included
459 let table_name = matchstr(a:table_name, '\%(.\{-}\.\)\?\zs\(.*\)' )
460 let table_alias = a:table_alias
461 let cols = a:cols
463 if g:omni_sql_use_tbl_alias != 'n'
464 if table_alias == ''
465 if 'da' =~? g:omni_sql_use_tbl_alias
466 if table_name =~ '_'
467 " Treat _ as separators since people often use these
468 " for word separators
469 let save_keyword = &iskeyword
470 setlocal iskeyword-=_
472 " Get the first letter of each word
473 " [[:alpha:]] is used instead of \w
474 " to catch extended accented characters
475 "
476 let table_alias = substitute(
477 \ table_name,
478 \ '\<[[:alpha:]]\+\>_\?',
479 \ '\=strpart(submatch(0), 0, 1)',
480 \ 'g'
481 \ )
482 " Restore original value
483 let &iskeyword = save_keyword
484 elseif table_name =~ '\u\U'
485 let table_alias = substitute(
486 \ table_name, '\(\u\)\U*', '\1', 'g')
487 else
488 let table_alias = strpart(table_name, 0, 1)
489 endif
490 endif
491 endif
492 if table_alias != ''
493 " Following a word character, make sure there is a . and no spaces
494 let table_alias = substitute(table_alias, '\w\zs\.\?\s*$', '.', '')
495 if 'a' =~? g:omni_sql_use_tbl_alias && a:table_alias == ''
496 let table_alias = inputdialog("Enter table alias:", table_alias)
497 endif
498 endif
499 if table_alias != ''
500 let cols = substitute(cols, '\<\w', table_alias.'&', 'g')
501 endif
502 endif
504 return cols
505 endfunction
507 function! s:SQLCGetObjectOwner(object)
508 " The owner regex matches a word at the start of the string which is
509 " followed by a dot, but doesn't include the dot in the result.
510 " ^ - from beginning of line
511 " \("\|\[\)\? - ignore any quotes
512 " \zs - start the match now
513 " .\{-} - get owner name
514 " \ze - end the match
515 " \("\|\[\)\? - ignore any quotes
516 " \. - must by followed by a .
517 " let owner = matchstr( a:object, '^\s*\zs.*\ze\.' )
518 let owner = matchstr( a:object, '^\("\|\[\)\?\zs\.\{-}\ze\("\|\]\)\?\.' )
519 return owner
520 endfunction
522 function! s:SQLCGetColumns(table_name, list_type)
523 " Check if the table name was provided as part of the column name
524 let table_name = matchstr(a:table_name, '^["\[\]a-zA-Z0-9_ ]\+\ze\.\?')
525 let table_cols = []
526 let table_alias = ''
527 let move_to_top = 1
529 let table_name = substitute(table_name, '\s*\(.\{-}\)\s*$', '\1', 'g')
531 " If the table name was given as:
532 " where c.
533 let table_name = substitute(table_name, '^\c\(WHERE\|AND\|OR\)\s\+', '', '')
534 if g:loaded_dbext >= 300
535 let saveSettingAlias = DB_listOption('use_tbl_alias')
536 exec 'DBSetOption use_tbl_alias=n'
537 endif
539 let table_name_stripped = substitute(table_name, '["\[\]]*', '', 'g')
541 " Check if we have already cached the column list for this table
542 " by its name
543 let list_idx = index(s:tbl_name, table_name_stripped, 0, &ignorecase)
544 if list_idx > -1
545 let table_cols = split(s:tbl_cols[list_idx], '\n')
546 else
547 " Check if we have already cached the column list for this table
548 " by its alias, assuming the table_name provided was actually
549 " the alias for the table instead
550 " select *
551 " from area a
552 " where a.
553 let list_idx = index(s:tbl_alias, table_name_stripped, 0, &ignorecase)
554 if list_idx > -1
555 let table_alias = table_name_stripped
556 let table_name = s:tbl_name[list_idx]
557 let table_cols = split(s:tbl_cols[list_idx], '\n')
558 endif
559 endif
561 " If we have not found a cached copy of the table
562 " And the table ends in a "." or we are looking for a column list
563 " if list_idx == -1 && (a:table_name =~ '\.' || b:sql_compl_type =~ 'column')
564 " if list_idx == -1 && (a:table_name =~ '\.' || a:list_type =~ 'csv')
565 if list_idx == -1
566 let saveY = @y
567 let saveSearch = @/
568 let saveWScan = &wrapscan
569 let curline = line(".")
570 let curcol = col(".")
572 " Do not let searchs wrap
573 setlocal nowrapscan
574 " If . was entered, look at the word just before the .
575 " We are looking for something like this:
576 " select *
577 " from customer c
578 " where c.
579 " So when . is pressed, we need to find 'c'
580 "
582 " Search backwards to the beginning of the statement
583 " and do NOT wrap
584 " exec 'silent! normal! v?\<\(select\|update\|delete\|;\)\>'."\n".'"yy'
585 exec 'silent! normal! ?\<\(select\|update\|delete\|;\)\>'."\n"
587 " Start characterwise visual mode
588 " Advance right one character
589 " Search foward until one of the following:
590 " 1. Another select/update/delete statement
591 " 2. A ; at the end of a line (the delimiter)
592 " 3. The end of the file (incase no delimiter)
593 " Yank the visually selected text into the "y register.
594 exec 'silent! normal! vl/\(\<select\>\|\<update\>\|\<delete\>\|;\s*$\|\%$\)'."\n".'"yy'
596 let query = @y
597 let query = substitute(query, "\n", ' ', 'g')
598 let found = 0
600 " if query =~? '^\(select\|update\|delete\)'
601 if query =~? '^\(select\)'
602 let found = 1
603 " \(\(\<\w\+\>\)\.\)\? -
604 " 'from.\{-}' - Starting at the from clause
605 " '\zs\(\(\<\w\+\>\)\.\)\?' - Get the owner name (optional)
606 " '\<\w\+\>\ze' - Get the table name
607 " '\s\+\<'.table_name.'\>' - Followed by the alias
608 " '\s*\.\@!.*' - Cannot be followed by a .
609 " '\(\<where\>\|$\)' - Must be followed by a WHERE clause
610 " '.*' - Exclude the rest of the line in the match
611 let table_name_new = matchstr(@y,
612 \ 'from.\{-}'.
613 \ '\zs\(\("\|\[\)\?.\{-}\("\|\]\)\.\)\?'.
614 \ '\("\|\[\)\?.\{-}\("\|\]\)\ze'.
615 \ '\s\+\%(as\s\+\)\?\<'.
616 \ matchstr(table_name, '.\{-}\ze\.\?$').
617 \ '\>'.
618 \ '\s*\.\@!.*'.
619 \ '\(\<where\>\|$\)'.
620 \ '.*'
621 \ )
623 if table_name_new != ''
624 let table_alias = table_name
625 let table_name = table_name_new
627 let list_idx = index(s:tbl_name, table_name, 0, &ignorecase)
628 if list_idx > -1
629 let table_cols = split(s:tbl_cols[list_idx])
630 let s:tbl_name[list_idx] = table_name
631 let s:tbl_alias[list_idx] = table_alias
632 else
633 let list_idx = index(s:tbl_alias, table_name, 0, &ignorecase)
634 if list_idx > -1
635 let table_cols = split(s:tbl_cols[list_idx])
636 let s:tbl_name[list_idx] = table_name
637 let s:tbl_alias[list_idx] = table_alias
638 endif
639 endif
641 endif
642 else
643 " Simply assume it is a table name provided with a . on the end
644 let found = 1
645 endif
647 let @y = saveY
648 let @/ = saveSearch
649 let &wrapscan = saveWScan
651 " Return to previous location
652 call cursor(curline, curcol)
654 if found == 0
655 if g:loaded_dbext > 300
656 exec 'DBSetOption use_tbl_alias='.saveSettingAlias
657 endif
659 " Not a SQL statement, do not display a list
660 return []
661 endif
662 endif
664 if empty(table_cols)
665 " Specify silent mode, no messages to the user (tbl, 1)
666 " Specify do not comma separate (tbl, 1, 1)
667 let table_cols_str = DB_getListColumn(table_name, 1, 1)
669 if table_cols_str != ""
670 let s:tbl_name = add( s:tbl_name, table_name )
671 let s:tbl_alias = add( s:tbl_alias, table_alias )
672 let s:tbl_cols = add( s:tbl_cols, table_cols_str )
673 let table_cols = split(table_cols_str, '\n')
674 endif
676 endif
678 if g:loaded_dbext > 300
679 exec 'DBSetOption use_tbl_alias='.saveSettingAlias
680 endif
682 " If the user has asked for a comma separate list of column
683 " values, ask the user if they want to prepend each column
684 " with a tablename alias.
685 if a:list_type == 'csv' && !empty(table_cols)
686 let cols = join(table_cols, ', ')
687 let cols = s:SQLCAddAlias(table_name, table_alias, cols)
688 let table_cols = [cols]
689 endif
691 return table_cols
692 endfunction