1" Vim OMNI completion script for SQL
2" Language:    SQL
3" Maintainer:  David Fishburn <[email protected]>
4" Version:     4.0
5" Last Change: Wed Apr 26 2006 3:00:06 PM
6" Usage:       For detailed help
7"              ":help sql.txt"
8"              or ":help ft-sql-omni"
9"              or read $VIMRUNTIME/doc/sql.txt
10
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.
14if 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
20endif
21
22if exists('g:loaded_sql_completion')
23    finish
24endif
25let g:loaded_sql_completion = 40
26
27" Maintains filename of dictionary
28let s:sql_file_table        = ""
29let s:sql_file_procedure    = ""
30let s:sql_file_view         = ""
31
32" Define various arrays to be used for caching
33let s:tbl_name              = []
34let s:tbl_alias             = []
35let s:tbl_cols              = []
36let s:syn_list              = []
37let s:syn_value             = []
38
39" Used in conjunction with the syntaxcomplete plugin
40let s:save_inc              = ""
41let s:save_exc              = ""
42if exists('g:omni_syntax_group_include_sql')
43    let s:save_inc = g:omni_syntax_group_include_sql
44endif
45if exists('g:omni_syntax_group_exclude_sql')
46    let s:save_exc = g:omni_syntax_group_exclude_sql
47endif
48
49" Used with the column list
50let s:save_prev_table       = ""
51
52" Default the option to verify table alias
53if !exists('g:omni_sql_use_tbl_alias')
54    let g:omni_sql_use_tbl_alias = 'a'
55endif
56" Default syntax items to precache
57if !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                \ ]
66endif
67" Set ignorecase to the ftplugin standard
68if !exists('g:omni_sql_ignorecase')
69    let g:omni_sql_ignorecase = &ignorecase
70endif
71" During table completion, should the table list also
72" include the owner name
73if !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
84endif
85
86" This function is used for the 'omnifunc' option.
87function! sqlcomplete#Complete(findstart, base)
88
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
95
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'
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
138
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
148
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
153
154    " Default the completion list to an empty list
155    let compl_list = []
156
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
164
165    if compl_type == 'tableReset'
166        let compl_type = 'table'
167        let base = ''
168    endif
169
170    if compl_type == 'table' ||
171                \ compl_type == 'procedure' ||
172                \ compl_type == 'view'
173
174        " This type of completion relies upon the dbext.vim plugin
175        if s:SQLCCheck4dbext() == -1
176            return []
177        endif
178
179        if s:sql_file_{compl_type} == ""
180            let compl_type = substitute(compl_type, '\w\+', '\u&', '')
181            let s:sql_file_{compl_type} = DB_getDictionaryName(compl_type)
182        endif
183        let s:sql_file_{compl_type} = DB_getDictionaryName(compl_type)
184        if s:sql_file_{compl_type} != ""
185            if filereadable(s:sql_file_{compl_type})
186                let compl_list = readfile(s:sql_file_{compl_type})
187                " let dic_list = readfile(s:sql_file_{compl_type})
188                " if !empty(dic_list)
189                "     for elem in dic_list
190                "         let kind = (compl_type=='table'?'m':(compl_type=='procedure'?'f':'v'))
191                "         let item = {'word':elem, 'menu':elem, 'kind':kind, 'info':compl_type}
192                "         let compl_list += [item]
193                "     endfor
194                " endif
195            endif
196        endif
197    elseif compl_type == 'column'
198
199        " This type of completion relies upon the dbext.vim plugin
200        if s:SQLCCheck4dbext() == -1
201            return []
202        endif
203
204        if base == ""
205            " The last time we displayed a column list we stored
206            " the table name.  If the user selects a column list
207            " without a table name of alias present, assume they want
208            " the previous column list displayed.
209            let base = s:save_prev_table
210        endif
211
212        if base != ""
213            let compl_list        = s:SQLCGetColumns(base, '')
214            let s:save_prev_table = base
215            let base              = ''
216        endif
217    elseif compl_type == 'column_csv'
218
219        " This type of completion relies upon the dbext.vim plugin
220        if s:SQLCCheck4dbext() == -1
221            return []
222        endif
223
224        if base == ""
225            " The last time we displayed a column list we stored
226            " the table name.  If the user selects a column list
227            " without a table name of alias present, assume they want
228            " the previous column list displayed.
229            let base = s:save_prev_table
230        endif
231
232        if base != ""
233            let compl_list        = s:SQLCGetColumns(base, 'csv')
234            let s:save_prev_table = base
235            " Join the column array into 1 single element array
236            " but make the columns column separated
237            let compl_list        = [join(compl_list, ', ')]
238            let base              = ''
239        endif
240    elseif compl_type == 'resetCache'
241        " Reset all cached items
242        let s:tbl_name  = []
243        let s:tbl_alias = []
244        let s:tbl_cols  = []
245        let s:syn_list  = []
246        let s:syn_value = []
247
248        let msg = "All SQL cached items have been removed."
249        call s:SQLCWarningMsg(msg)
250        " Leave time for the user to read the error message
251        :sleep 2
252    else
253        let compl_list = s:SQLCGetSyntaxList(compl_type)
254    endif
255
256    if base != ''
257        " Filter the list based on the first few characters the user
258        " entered
259        let expr = 'v:val '.(g:omni_sql_ignorecase==1?'=~?':'=~#').' "^'.base.'"'
260        let compl_list = filter(deepcopy(compl_list), expr)
261    endif
262
263    if exists('b:sql_compl_savefunc') && b:sql_compl_savefunc != ""
264        let &omnifunc = b:sql_compl_savefunc
265    endif
266
267    return compl_list
268endfunc
269
270function! sqlcomplete#PreCacheSyntax(...)
271    let syn_group_arr = []
272    if a:0 > 0
273        let syn_group_arr = a:1
274    else
275        let syn_group_arr = g:omni_sql_precache_syntax_groups
276    endif
277    if !empty(syn_group_arr)
278        for group_name in syn_group_arr
279            call s:SQLCGetSyntaxList(group_name)
280        endfor
281    endif
282endfunction
283
284function! sqlcomplete#Map(type)
285    " Tell the SQL plugin what you want to complete
286    let b:sql_compl_type=a:type
287    " Record previous omnifunc, if the SQL completion
288    " is being used in conjunction with other filetype
289    " completion plugins
290    if &omnifunc != "" && &omnifunc != 'sqlcomplete#Complete'
291        " Record the previous omnifunc, the plugin
292        " will automatically set this back so that it
293        " does not interfere with other ftplugins settings
294        let b:sql_compl_savefunc=&omnifunc
295    endif
296    " Set the OMNI func for the SQL completion plugin
297    let &omnifunc='sqlcomplete#Complete'
298endfunction
299
300function! sqlcomplete#DrillIntoTable()
301    " If the omni popup window is visible
302    if pumvisible()
303        call sqlcomplete#Map('column')
304        " C-Y, makes the currently highlighted entry active
305        " and trigger the omni popup to be redisplayed
306        call feedkeys("\<C-Y>\<C-X>\<C-O>")
307    else
308        if has('win32')
309            " If the popup is not visible, simple perform the normal
310            " <C-Right> behaviour
311            exec "normal! \<C-Right>"
312        endif
313    endif
314    return ""
315endfunction
316
317function! sqlcomplete#DrillOutOfColumns()
318    " If the omni popup window is visible
319    if pumvisible()
320        call sqlcomplete#Map('tableReset')
321        " Trigger the omni popup to be redisplayed
322        call feedkeys("\<C-X>\<C-O>")
323    else
324        if has('win32')
325            " If the popup is not visible, simple perform the normal
326            " <C-Left> behaviour
327            exec "normal! \<C-Left>"
328        endif
329    endif
330    return ""
331endfunction
332
333function! s:SQLCWarningMsg(msg)
334    echohl WarningMsg
335    echomsg a:msg
336    echohl None
337endfunction
338
339function! s:SQLCErrorMsg(msg)
340    echohl ErrorMsg
341    echomsg a:msg
342    echohl None
343endfunction
344
345function! s:SQLCGetSyntaxList(syn_group)
346    let syn_group  = a:syn_group
347    let compl_list = []
348
349    " Check if we have already cached the syntax list
350    let list_idx = index(s:syn_list, syn_group, 0, &ignorecase)
351    if list_idx > -1
352        " Return previously cached value
353        let compl_list = s:syn_value[list_idx]
354    else
355        " Request the syntax list items from the
356        " syntax completion plugin
357        if syn_group == 'syntax'
358            " Handle this special case.  This allows the user
359            " to indicate they want all the syntax items available,
360            " so do not specify a specific include list.
361            let g:omni_syntax_group_include_sql = ''
362        else
363            " The user has specified a specific syntax group
364            let g:omni_syntax_group_include_sql = syn_group
365        endif
366        let g:omni_syntax_group_exclude_sql = ''
367        let syn_value                       = OmniSyntaxList()
368        let g:omni_syntax_group_include_sql = s:save_inc
369        let g:omni_syntax_group_exclude_sql = s:save_exc
370        " Cache these values for later use
371        let s:syn_list  = add( s:syn_list,  syn_group )
372        let s:syn_value = add( s:syn_value, syn_value )
373        let compl_list  = syn_value
374    endif
375
376    return compl_list
377endfunction
378
379function! s:SQLCCheck4dbext()
380    if !exists('g:loaded_dbext')
381        let msg = "The dbext plugin must be loaded for dynamic SQL completion"
382        call s:SQLCErrorMsg(msg)
383        " Leave time for the user to read the error message
384        :sleep 2
385        return -1
386    elseif g:loaded_dbext < 300
387        let msg = "The dbext plugin must be at least version 3.00 " .
388                    \ " for dynamic SQL completion"
389        call s:SQLCErrorMsg(msg)
390        " Leave time for the user to read the error message
391        :sleep 2
392        return -1
393    endif
394    return 1
395endfunction
396
397function! s:SQLCAddAlias(table_name, table_alias, cols)
398    " Strip off the owner if included
399    let table_name  = matchstr(a:table_name, '\%(.\{-}\.\)\?\zs\(.*\)' )
400    let table_alias = a:table_alias
401    let cols        = a:cols
402
403    if g:omni_sql_use_tbl_alias != 'n'
404        if table_alias == ''
405            if 'da' =~? g:omni_sql_use_tbl_alias
406                if table_name =~ '_'
407                    " Treat _ as separators since people often use these
408                    " for word separators
409                    let save_keyword = &iskeyword
410                    setlocal iskeyword-=_
411
412                    " Get the first letter of each word
413                    " [[:alpha:]] is used instead of \w
414                    " to catch extended accented characters
415                    "
416                    let table_alias = substitute(
417                                \ table_name,
418                                \ '\<[[:alpha:]]\+\>_\?',
419                                \ '\=strpart(submatch(0), 0, 1)',
420                                \ 'g'
421                                \ )
422                    " Restore original value
423                    let &iskeyword = save_keyword
424                elseif table_name =~ '\u\U'
425                    let table_alias = substitute(
426                                \ table_name, '\(\u\)\U*', '\1', 'g')
427                else
428                    let table_alias = strpart(table_name, 0, 1)
429                endif
430            endif
431        endif
432        if table_alias != ''
433            " Following a word character, make sure there is a . and no spaces
434            let table_alias = substitute(table_alias, '\w\zs\.\?\s*$', '.', '')
435            if 'a' =~? g:omni_sql_use_tbl_alias && a:table_alias == ''
436                let table_alias = inputdialog("Enter table alias:", table_alias)
437            endif
438        endif
439        if table_alias != ''
440            let cols = substitute(cols, '\<\w', table_alias.'&', 'g')
441        endif
442    endif
443
444    return cols
445endfunction
446
447function! s:SQLCGetColumns(table_name, list_type)
448    let table_name   = matchstr(a:table_name, '^\w\+')
449    let table_name   = matchstr(a:table_name, '^[a-zA-Z0-9_.]\+')
450    let table_cols   = []
451    let table_alias  = ''
452    let move_to_top  = 1
453
454    if g:loaded_dbext >= 300
455        let saveSettingAlias = DB_listOption('use_tbl_alias')
456        exec 'DBSetOption use_tbl_alias=n'
457    endif
458
459    " Check if we have already cached the column list for this table
460    " by its name
461    let list_idx = index(s:tbl_name, table_name, 0, &ignorecase)
462    if list_idx > -1
463        let table_cols = split(s:tbl_cols[list_idx])
464    else
465        " Check if we have already cached the column list for this table
466        " by its alias, assuming the table_name provided was actually
467        " the alias for the table instead
468        "     select *
469        "       from area a
470        "      where a.
471        let list_idx = index(s:tbl_alias, table_name, 0, &ignorecase)
472        if list_idx > -1
473            let table_alias = table_name
474            let table_name  = s:tbl_name[list_idx]
475            let table_cols  = split(s:tbl_cols[list_idx])
476        endif
477    endif
478
479    " If we have not found a cached copy of the table
480    " And the table ends in a "." or we are looking for a column list
481    " if list_idx == -1 && (a:table_name =~ '\.' || b:sql_compl_type =~ 'column')
482    " if list_idx == -1 && (a:table_name =~ '\.' || a:list_type =~ 'csv')
483    if list_idx == -1
484         let saveY      = @y
485         let saveSearch = @/
486         let saveWScan  = &wrapscan
487         let curline    = line(".")
488         let curcol     = col(".")
489
490         " Do not let searchs wrap
491         setlocal nowrapscan
492         " If . was entered, look at the word just before the .
493         " We are looking for something like this:
494         "    select *
495         "      from customer c
496         "     where c.
497         " So when . is pressed, we need to find 'c'
498         "
499
500         " Search backwards to the beginning of the statement
501         " and do NOT wrap
502         " exec 'silent! normal! v?\<\(select\|update\|delete\|;\)\>'."\n".'"yy'
503         exec 'silent! normal! ?\<\(select\|update\|delete\|;\)\>'."\n"
504
505         " Start characterwise visual mode
506         " Advance right one character
507         " Search foward until one of the following:
508         "     1.  Another select/update/delete statement
509         "     2.  A ; at the end of a line (the delimiter)
510         "     3.  The end of the file (incase no delimiter)
511         " Yank the visually selected text into the "y register.
512         exec 'silent! normal! vl/\(\<select\>\|\<update\>\|\<delete\>\|;\s*$\|\%$\)'."\n".'"yy'
513
514         let query = @y
515         let query = substitute(query, "\n", ' ', 'g')
516         let found = 0
517
518         " if query =~? '^\(select\|update\|delete\)'
519         if query =~? '^\(select\)'
520             let found = 1
521             "  \(\(\<\w\+\>\)\.\)\?   -
522             " 'from.\{-}'  - Starting at the from clause
523             " '\zs\(\(\<\w\+\>\)\.\)\?' - Get the owner name (optional)
524             " '\<\w\+\>\ze' - Get the table name
525             " '\s\+\<'.table_name.'\>' - Followed by the alias
526             " '\s*\.\@!.*'  - Cannot be followed by a .
527             " '\(\<where\>\|$\)' - Must be followed by a WHERE clause
528             " '.*'  - Exclude the rest of the line in the match
529             let table_name_new = matchstr(@y,
530                         \ 'from.\{-}'.
531                         \ '\zs\(\(\<\w\+\>\)\.\)\?'.
532                         \ '\<\w\+\>\ze'.
533                         \ '\s\+\%(as\s\+\)\?\<'.
534                         \ matchstr(table_name, '.\{-}\ze\.\?$').
535                         \ '\>'.
536                         \ '\s*\.\@!.*'.
537                         \ '\(\<where\>\|$\)'.
538                         \ '.*'
539                         \ )
540             if table_name_new != ''
541                 let table_alias = table_name
542                 let table_name  = table_name_new
543
544                 let list_idx = index(s:tbl_name, table_name, 0, &ignorecase)
545                 if list_idx > -1
546                     let table_cols  = split(s:tbl_cols[list_idx])
547                     let s:tbl_name[list_idx]  = table_name
548                     let s:tbl_alias[list_idx] = table_alias
549                 else
550                     let list_idx = index(s:tbl_alias, table_name, 0, &ignorecase)
551                     if list_idx > -1
552                         let table_cols = split(s:tbl_cols[list_idx])
553                         let s:tbl_name[list_idx]  = table_name
554                         let s:tbl_alias[list_idx] = table_alias
555                     endif
556                 endif
557
558             endif
559         else
560             " Simply assume it is a table name provided with a . on the end
561             let found = 1
562         endif
563
564         let @y        = saveY
565         let @/        = saveSearch
566         let &wrapscan = saveWScan
567
568         " Return to previous location
569         call cursor(curline, curcol)
570
571         if found == 0
572             if g:loaded_dbext > 300
573                 exec 'DBSetOption use_tbl_alias='.saveSettingAlias
574             endif
575
576             " Not a SQL statement, do not display a list
577             return []
578         endif
579    endif
580
581    if empty(table_cols)
582        " Specify silent mode, no messages to the user (tbl, 1)
583        " Specify do not comma separate (tbl, 1, 1)
584        let table_cols_str = DB_getListColumn(table_name, 1, 1)
585
586        if table_cols_str != ""
587            let s:tbl_name  = add( s:tbl_name,  table_name )
588            let s:tbl_alias = add( s:tbl_alias, table_alias )
589            let s:tbl_cols  = add( s:tbl_cols,  table_cols_str )
590            let table_cols  = split(table_cols_str)
591        endif
592
593    endif
594
595    if g:loaded_dbext > 300
596        exec 'DBSetOption use_tbl_alias='.saveSettingAlias
597    endif
598
599    " If the user has asked for a comma separate list of column
600    " values, ask the user if they want to prepend each column
601    " with a tablename alias.
602    if a:list_type == 'csv' && !empty(table_cols)
603        let cols       = join(table_cols, ', ')
604        let cols       = s:SQLCAddAlias(table_name, table_alias, cols)
605        let table_cols = [cols]
606    endif
607
608    return table_cols
609endfunction
610
611