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