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