1" Vim completion script
2" Language:    SQL
3" Maintainer:  David Fishburn <[email protected]>
4" Version:     3.0
5" Last Change: Thu Apr 20 2006 8:47:12 PM
6
7" Set completion with CTRL-X CTRL-O to autoloaded function.
8" This check is in place in case this script is
9" sourced directly instead of using the autoload feature.
10if exists('&omnifunc')
11    " Do not set the option if already set since this
12    " results in an E117 warning.
13    if &omnifunc == ""
14        setlocal omnifunc=sqlcomplete#Complete
15    endif
16endif
17
18if exists('g:loaded_sql_completion')
19    finish
20endif
21let g:loaded_sql_completion = 30
22
23" Maintains filename of dictionary
24let s:sql_file_table        = ""
25let s:sql_file_procedure    = ""
26let s:sql_file_view         = ""
27
28" Define various arrays to be used for caching
29let s:tbl_name              = []
30let s:tbl_alias             = []
31let s:tbl_cols              = []
32let s:syn_list              = []
33let s:syn_value             = []
34
35" Used in conjunction with the syntaxcomplete plugin
36let s:save_inc              = ""
37let s:save_exc              = ""
38if exists('g:omni_syntax_group_include_sql')
39    let s:save_inc = g:omni_syntax_group_include_sql
40endif
41if exists('g:omni_syntax_group_exclude_sql')
42    let s:save_exc = g:omni_syntax_group_exclude_sql
43endif
44
45" Used with the column list
46let s:save_prev_table       = ""
47
48" Default the option to verify table alias
49if !exists('g:omni_sql_use_tbl_alias')
50    let g:omni_sql_use_tbl_alias = 'a'
51endif
52" Default syntax items to precache
53if !exists('g:omni_sql_precache_syntax_groups')
54    let g:omni_sql_precache_syntax_groups = [
55                \ 'syntax',
56                \ 'sqlKeyword',
57                \ 'sqlFunction',
58                \ 'sqlOption',
59                \ 'sqlType',
60                \ 'sqlStatement'
61                \ ]
62endif
63" Set ignorecase to the ftplugin standard
64if !exists('g:omni_sql_ignorecase')
65    let g:omni_sql_ignorecase = &ignorecase
66endif
67" During table completion, should the table list also
68" include the owner name
69if !exists('g:omni_sql_include_owner')
70    let g:omni_sql_include_owner = 0
71    if exists('g:loaded_dbext')
72        if g:loaded_dbext >= 300
73            " New to dbext 3.00, by default the table lists include the owner
74            " name of the table.  This is used when determining how much of
75            " whatever has been typed should be replaced as part of the
76            " code replacement.
77            let g:omni_sql_include_owner = 1
78        endif
79    endif
80endif
81
82" This function is used for the 'omnifunc' option.
83function! sqlcomplete#Complete(findstart, base)
84
85    " Default to table name completion
86    let compl_type = 'table'
87    " Allow maps to specify what type of object completion they want
88    if exists('b:sql_compl_type')
89        let compl_type = b:sql_compl_type
90    endif
91
92    " First pass through this function determines how much of the line should
93    " be replaced by whatever is chosen from the completion list
94    if a:findstart
95        " Locate the start of the item, including "."
96        let line = getline('.')
97        let start = col('.') - 1
98        let lastword = -1
99        while start > 0
100            if line[start - 1] =~ '\w'
101                let start -= 1
102            elseif line[start - 1] =~ '\.' &&
103                        \ compl_type =~ 'column\|table\|view\|procedure'
104                " If lastword has already been set for column completion
105                " break from the loop, since we do not also want to pickup
106                " a table name if it was also supplied.
107                if lastword != -1 && compl_type =~ 'column'
108                    break
109                endif
110                " Assume we are looking for column completion
111                " column_type can be either 'column' or 'column_csv'
112                if lastword == -1 && compl_type =~ 'column'
113                    let lastword = start
114                endif
115                " If omni_sql_include_owner = 0, do not include the table
116                " name as part of the substitution, so break here
117                if lastword == -1 &&
118                            \ compl_type =~ 'table\|view\|procedure' &&
119                            \ g:omni_sql_include_owner == 0
120                    let lastword = start
121                    break
122                endif
123                let start -= 1
124            else
125                break
126            endif
127        endwhile
128
129        " Return the column of the last word, which is going to be changed.
130        " Remember the text that comes before it in s:prepended.
131        if lastword == -1
132            let s:prepended = ''
133            return start
134        endif
135        let s:prepended = strpart(line, start, lastword - start)
136        return lastword
137    endif
138
139    " Second pass through this function will determine what data to put inside
140    " of the completion list
141    " s:prepended is set by the first pass
142    let base = s:prepended . a:base
143
144    " Default the completion list to an empty list
145    let compl_list = []
146
147    " Default to table name completion
148    let compl_type = 'table'
149    " Allow maps to specify what type of object completion they want
150    if exists('b:sql_compl_type')
151        let compl_type = b:sql_compl_type
152        unlet b:sql_compl_type
153    endif
154
155    if compl_type == 'tableReset'
156        let compl_type = 'table'
157        let base = ''
158    endif
159
160    if compl_type == 'table' ||
161                \ compl_type == 'procedure' ||
162                \ compl_type == 'view'
163
164        " This type of completion relies upon the dbext.vim plugin
165        if s:SQLCCheck4dbext() == -1
166            return []
167        endif
168
169        if s:sql_file_{compl_type} == ""
170            let compl_type = substitute(compl_type, '\w\+', '\u&', '')
171            let s:sql_file_{compl_type} = DB_getDictionaryName(compl_type)
172        endif
173        let s:sql_file_{compl_type} = DB_getDictionaryName(compl_type)
174        if s:sql_file_{compl_type} != ""
175            if filereadable(s:sql_file_{compl_type})
176                let compl_list = readfile(s:sql_file_{compl_type})
177                " let dic_list = readfile(s:sql_file_{compl_type})
178                " if !empty(dic_list)
179                "     for elem in dic_list
180                "         let kind = (compl_type=='table'?'m':(compl_type=='procedure'?'f':'v'))
181                "         let item = {'word':elem, 'menu':elem, 'kind':kind, 'info':compl_type}
182                "         let compl_list += [item]
183                "     endfor
184                " endif
185            endif
186        endif
187    elseif compl_type == 'column'
188
189        " This type of completion relies upon the dbext.vim plugin
190        if s:SQLCCheck4dbext() == -1
191            return []
192        endif
193
194        if base == ""
195            " The last time we displayed a column list we stored
196            " the table name.  If the user selects a column list
197            " without a table name of alias present, assume they want
198            " the previous column list displayed.
199            let base = s:save_prev_table
200        endif
201
202        if base != ""
203            let compl_list        = s:SQLCGetColumns(base, '')
204            let s:save_prev_table = base
205            let base              = ''
206        endif
207    elseif compl_type == 'column_csv'
208
209        " This type of completion relies upon the dbext.vim plugin
210        if s:SQLCCheck4dbext() == -1
211            return []
212        endif
213
214        if base == ""
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
220        endif
221
222        if base != ""
223            let compl_list        = s:SQLCGetColumns(base, 'csv')
224            let s:save_prev_table = base
225            " Join the column array into 1 single element array
226            " but make the columns column separated
227            let compl_list        = [join(compl_list, ', ')]
228            let base              = ''
229        endif
230    elseif compl_type == 'resetCache'
231        " Reset all cached items
232        let s:tbl_name  = []
233        let s:tbl_alias = []
234        let s:tbl_cols  = []
235        let s:syn_list  = []
236        let s:syn_value = []
237    else
238        let compl_list = s:SQLCGetSyntaxList(compl_type)
239    endif
240
241    if base != ''
242        " Filter the list based on the first few characters the user
243        " entered
244        let expr = 'v:val '.(g:omni_sql_ignorecase==1?'=~?':'=~#').' "^'.base.'"'
245        let compl_list = filter(deepcopy(compl_list), expr)
246    endif
247
248    if exists('b:sql_compl_savefunc') && b:sql_compl_savefunc != ""
249        let &omnifunc = b:sql_compl_savefunc
250    endif
251
252    return compl_list
253endfunc
254
255function! s:SQLCWarningMsg(msg)
256    echohl WarningMsg
257    echomsg a:msg
258    echohl None
259endfunction
260
261function! s:SQLCErrorMsg(msg)
262    echohl ErrorMsg
263    echomsg a:msg
264    echohl None
265endfunction
266
267function! sqlcomplete#PreCacheSyntax(...)
268    let syn_group_arr = []
269    if a:0 > 0
270        let syn_group_arr = a:1
271    else
272        let syn_group_arr = g:omni_sql_precache_syntax_groups
273    endif
274    if !empty(syn_group_arr)
275        for group_name in syn_group_arr
276            call s:SQLCGetSyntaxList(group_name)
277        endfor
278    endif
279endfunction
280
281function! sqlcomplete#Map(type)
282    " Tell the SQL plugin what you want to complete
283    let b:sql_compl_type=a:type
284    " Record previous omnifunc, if the SQL completion
285    " is being used in conjunction with other filetype
286    " completion plugins
287    if &omnifunc != "" && &omnifunc != 'sqlcomplete#Complete'
288        " Record the previous omnifunc, the plugin
289        " will automatically set this back so that it
290        " does not interfere with other ftplugins settings
291        let b:sql_compl_savefunc=&omnifunc
292    endif
293    " Set the OMNI func for the SQL completion plugin
294    let &omnifunc='sqlcomplete#Complete'
295endfunction
296
297function! s:SQLCGetSyntaxList(syn_group)
298    let syn_group  = a:syn_group
299    let compl_list = []
300
301    " Check if we have already cached the syntax list
302    let list_idx = index(s:syn_list, syn_group, 0, &ignorecase)
303    if list_idx > -1
304        " Return previously cached value
305        let compl_list = s:syn_value[list_idx]
306    else
307        " Request the syntax list items from the
308        " syntax completion plugin
309        if syn_group == 'syntax'
310            " Handle this special case.  This allows the user
311            " to indicate they want all the syntax items available,
312            " so do not specify a specific include list.
313            let g:omni_syntax_group_include_sql = ''
314        else
315            " The user has specified a specific syntax group
316            let g:omni_syntax_group_include_sql = syn_group
317        endif
318        let g:omni_syntax_group_exclude_sql = ''
319        let syn_value                       = OmniSyntaxList()
320        let g:omni_syntax_group_include_sql = s:save_inc
321        let g:omni_syntax_group_exclude_sql = s:save_exc
322        " Cache these values for later use
323        let s:syn_list  = add( s:syn_list,  syn_group )
324        let s:syn_value = add( s:syn_value, syn_value )
325        let compl_list  = syn_value
326    endif
327
328    return compl_list
329endfunction
330
331function! s:SQLCCheck4dbext()
332    if !exists('g:loaded_dbext')
333        let msg = "The dbext plugin must be loaded for dynamic SQL completion"
334        call s:SQLCErrorMsg(msg)
335        " Leave time for the user to read the error message
336        :sleep 2
337        return -1
338    elseif g:loaded_dbext < 300
339        let msg = "The dbext plugin must be at least version 3.00 " .
340                    \ " for dynamic SQL completion"
341        call s:SQLCErrorMsg(msg)
342        " Leave time for the user to read the error message
343        :sleep 2
344        return -1
345    endif
346    return 1
347endfunction
348
349function! s:SQLCAddAlias(table_name, table_alias, cols)
350    let table_name  = a:table_name
351    let table_alias = a:table_alias
352    let cols        = a:cols
353
354    if g:omni_sql_use_tbl_alias != 'n'
355        if table_alias == ''
356            if 'da' =~? g:omni_sql_use_tbl_alias
357                if table_name =~ '_'
358                    " Treat _ as separators since people often use these
359                    " for word separators
360                    let save_keyword = &iskeyword
361                    setlocal iskeyword-=_
362
363                    " Get the first letter of each word
364                    " [[:alpha:]] is used instead of \w
365                    " to catch extended accented characters
366                    "
367                    let table_alias = substitute(
368                                \ table_name,
369                                \ '\<[[:alpha:]]\+\>_\?',
370                                \ '\=strpart(submatch(0), 0, 1)',
371                                \ 'g'
372                                \ )
373                    " Restore original value
374                    let &iskeyword = save_keyword
375                elseif table_name =~ '\u\U'
376                    let initials = substitute(
377                                \ table_name, '\(\u\)\U*', '\1', 'g')
378                else
379                    let table_alias = strpart(table_name, 0, 1)
380                endif
381            endif
382        endif
383        if table_alias != ''
384            " Following a word character, make sure there is a . and no spaces
385            let table_alias = substitute(table_alias, '\w\zs\.\?\s*$', '.', '')
386            if 'a' =~? g:omni_sql_use_tbl_alias && a:table_alias == ''
387                let table_alias = inputdialog("Enter table alias:", table_alias)
388            endif
389        endif
390        if table_alias != ''
391            let cols = substitute(cols, '\<\w', table_alias.'&', 'g')
392        endif
393    endif
394
395    return cols
396endfunction
397
398function! s:SQLCGetColumns(table_name, list_type)
399    let table_name   = matchstr(a:table_name, '^\w\+')
400    let table_cols   = []
401    let table_alias  = ''
402    let move_to_top  = 1
403
404    if g:loaded_dbext >= 300
405        let saveSettingAlias = DB_listOption('use_tbl_alias')
406        exec 'DBSetOption use_tbl_alias=n'
407    endif
408
409    " Check if we have already cached the column list for this table
410    " by its name
411    let list_idx = index(s:tbl_name, table_name, 0, &ignorecase)
412    if list_idx > -1
413        let table_cols = split(s:tbl_cols[list_idx])
414    else
415        " Check if we have already cached the column list for this table
416        " by its alias, assuming the table_name provided was actually
417        " the alias for the table instead
418        "     select *
419        "       from area a
420        "      where a.
421        let list_idx = index(s:tbl_alias, table_name, 0, &ignorecase)
422        if list_idx > -1
423            let table_alias = table_name
424            let table_name  = s:tbl_name[list_idx]
425            let table_cols  = split(s:tbl_cols[list_idx])
426        endif
427    endif
428
429    " If we have not found a cached copy of the table
430    " And the table ends in a "." or we are looking for a column list
431    " if list_idx == -1 && (a:table_name =~ '\.' || b:sql_compl_type =~ 'column')
432    " if list_idx == -1 && (a:table_name =~ '\.' || a:list_type =~ 'csv')
433    if list_idx == -1
434         let saveY      = @y
435         let saveSearch = @/
436         let saveWScan  = &wrapscan
437         let curline    = line(".")
438         let curcol     = col(".")
439
440         " Do not let searchs wrap
441         setlocal nowrapscan
442         " If . was entered, look at the word just before the .
443         " We are looking for something like this:
444         "    select *
445         "      from customer c
446         "     where c.
447         " So when . is pressed, we need to find 'c'
448         "
449
450         " Search backwards to the beginning of the statement
451         " and do NOT wrap
452         " exec 'silent! normal! v?\<\(select\|update\|delete\|;\)\>'."\n".'"yy'
453         exec 'silent! normal! ?\<\(select\|update\|delete\|;\)\>'."\n"
454
455         " Start characterwise visual mode
456         " Advance right one character
457         " Search foward until one of the following:
458         "     1.  Another select/update/delete statement
459         "     2.  A ; at the end of a line (the delimiter)
460         "     3.  The end of the file (incase no delimiter)
461         " Yank the visually selected text into the "y register.
462         exec 'silent! normal! vl/\(\<select\>\|\<update\>\|\<delete\>\|;\s*$\|\%$\)'."\n".'"yy'
463
464         let query = @y
465         let query = substitute(query, "\n", ' ', 'g')
466         let found = 0
467
468         " if query =~? '^\(select\|update\|delete\)'
469         if query =~? '^\(select\)'
470             let found = 1
471             "  \(\(\<\w\+\>\)\.\)\?   -
472             " 'from.\{-}'  - Starting at the from clause
473             " '\zs\(\(\<\w\+\>\)\.\)\?' - Get the owner name (optional)
474             " '\<\w\+\>\ze' - Get the table name
475             " '\s\+\<'.table_name.'\>' - Followed by the alias
476             " '\s*\.\@!.*'  - Cannot be followed by a .
477             " '\(\<where\>\|$\)' - Must be followed by a WHERE clause
478             " '.*'  - Exclude the rest of the line in the match
479             let table_name_new = matchstr(@y,
480                         \ 'from.\{-}'.
481                         \ '\zs\(\(\<\w\+\>\)\.\)\?'.
482                         \ '\<\w\+\>\ze'.
483                         \ '\s\+\%(as\s\+\)\?\<'.table_name.'\>'.
484                         \ '\s*\.\@!.*'.
485                         \ '\(\<where\>\|$\)'.
486                         \ '.*'
487                         \ )
488             if table_name_new != ''
489                 let table_alias = table_name
490                 let table_name  = table_name_new
491
492                 let list_idx = index(s:tbl_name, table_name, 0, &ignorecase)
493                 if list_idx > -1
494                     let table_cols  = split(s:tbl_cols[list_idx])
495                     let s:tbl_name[list_idx]  = table_name
496                     let s:tbl_alias[list_idx] = table_alias
497                 else
498                     let list_idx = index(s:tbl_alias, table_name, 0, &ignorecase)
499                     if list_idx > -1
500                         let table_cols = split(s:tbl_cols[list_idx])
501                         let s:tbl_name[list_idx]  = table_name
502                         let s:tbl_alias[list_idx] = table_alias
503                     endif
504                 endif
505
506             endif
507         else
508             " Simply assume it is a table name provided with a . on the end
509             let found = 1
510         endif
511
512         let @y        = saveY
513         let @/        = saveSearch
514         let &wrapscan = saveWScan
515
516         " Return to previous location
517         call cursor(curline, curcol)
518
519         if found == 0
520             if g:loaded_dbext > 300
521                 exec 'DBSetOption use_tbl_alias='.saveSettingAlias
522             endif
523
524             " Not a SQL statement, do not display a list
525             return []
526         endif
527    endif
528
529    if empty(table_cols)
530        " Specify silent mode, no messages to the user (tbl, 1)
531        " Specify do not comma separate (tbl, 1, 1)
532        let table_cols_str = DB_getListColumn(table_name, 1, 1)
533
534        if table_cols_str != ""
535            let s:tbl_name  = add( s:tbl_name,  table_name )
536            let s:tbl_alias = add( s:tbl_alias, table_alias )
537            let s:tbl_cols  = add( s:tbl_cols,  table_cols_str )
538            let table_cols  = split(table_cols_str)
539        endif
540
541    endif
542
543    if g:loaded_dbext > 300
544        exec 'DBSetOption use_tbl_alias='.saveSettingAlias
545    endif
546
547    if a:list_type == 'csv' && !empty(table_cols)
548        let cols = join(table_cols, ', ')
549        let cols = s:SQLCAddAlias(table_name, table_alias, cols)
550        let table_cols = [cols]
551    endif
552
553    return table_cols
554endfunction
555
556