sqlcomplete.vim

" Vim completion script
" Language:    SQL
" Maintainer:  David Fishburn <fishburn@ianywhere.com>
" Version:     1.0
" Last Change: Tue Mar 28 2006 4:39:49 PM

" Set completion with CTRL-X CTRL-O to autoloaded function.
" This check is in place in case this script is
" sourced directly instead of using the autoload feature. 
if exists('&omnifunc')
    " Do not set the option if already set since this
    " results in an E117 warning.
    if &omnifunc == ""
        setlocal omnifunc=sqlcomplete#Complete
    endif
endif

if exists('g:loaded_sql_completion')
    finish 
endif
let g:loaded_sql_completion = 1

" Maintains filename of dictionary
let s:sql_file_table             = ""
let s:sql_file_procedure         = ""
let s:sql_file_view              = ""

" Define various arrays to be used for caching
let s:tbl_name                   = []
let s:tbl_alias                  = []
let s:tbl_cols                   = []
let s:syn_list                   = []
let s:syn_value                  = []
 
" Used in conjunction with the syntaxcomplete plugin
let s:save_inc = ""
let s:save_exc = ""
if exists('g:omni_syntax_group_include_sql')
    let s:save_inc = g:omni_syntax_group_include_sql
endif
if exists('g:omni_syntax_group_exclude_sql')
    let s:save_exc = g:omni_syntax_group_exclude_sql
endif
 
" Used with the column list
let s:save_prev_table = ""

" Default the option to verify table alias
if !exists('g:omni_sql_use_tbl_alias')
    let g:omni_sql_use_tbl_alias = 'a'
endif

" This function is used for the 'omnifunc' option.
function! sqlcomplete#Complete(findstart, base)

    " Default to table name completion
    let compl_type = 'table'
    " Allow maps to specify what type of object completion they want
    if exists('b:sql_compl_type')
        let compl_type = b:sql_compl_type
    endif

    if a:findstart
        " Locate the start of the item, including "."
        let line = getline('.')
        let start = col('.') - 1
        let lastword = -1
        while start > 0
            if line[start - 1] =~ '\w'
                let start -= 1
            elseif line[start - 1] =~ '\.' && compl_type =~ 'column\|table'
                " If the completion type is table or column
                " Then assume we are looking for column completion
                " column_type can be either 'column' or 'column_csv'
                if lastword == -1
                    let lastword = start
                endif
                let start -= 1
                let b:sql_compl_type = 'column'
            else
                break
            endif
        endwhile

        " Return the column of the last word, which is going to be changed.
        " Remember the text that comes before it in s:prepended.
        if lastword == -1
            let s:prepended = ''
            return start
        endif
        let s:prepended = strpart(line, start, lastword - start)
        return lastword
    endif

    let base = s:prepended . a:base

    let compl_list = []

    " Default to table name completion
    let compl_type = 'table'
    " Allow maps to specify what type of object completion they want
    if exists('b:sql_compl_type')
        let compl_type = b:sql_compl_type
        unlet b:sql_compl_type
    endif

    if compl_type == 'tableReset'
        let compl_type = 'table'
        let base = ''
    endif

    if compl_type == 'table' ||
                \ compl_type == 'procedure' ||
                \ compl_type == 'view' 

        " This type of completion relies upon the dbext.vim plugin
        if s:SQLCCheck4dbext() == -1
            return []
        endif

        if s:sql_file_{compl_type} == ""
            let compl_type = substitute(compl_type, '\w\+', '\u&', '')
            let s:sql_file_{compl_type} = DB_getDictionaryName(compl_type)
        endif
        let s:sql_file_{compl_type} = DB_getDictionaryName(compl_type)
        if s:sql_file_{compl_type} != ""
            if filereadable(s:sql_file_{compl_type})
                let compl_list = readfile(s:sql_file_{compl_type})
            endif
        endif
    elseif compl_type == 'column'

        " This type of completion relies upon the dbext.vim plugin
        if s:SQLCCheck4dbext() == -1
            return []
        endif

        if base == ""
            " The last time we displayed a column list we stored
            " the table name.  If the user selects a column list 
            " without a table name of alias present, assume they want
            " the previous column list displayed.
            let base = s:save_prev_table
        endif

        if base != ""
            let compl_list        = s:SQLCGetColumns(base, '')
            let s:save_prev_table = base
            let base              = ''
        endif
    elseif compl_type == 'column_csv'

        " This type of completion relies upon the dbext.vim plugin
        if s:SQLCCheck4dbext() == -1
            return []
        endif

        if base == ""
            " The last time we displayed a column list we stored
            " the table name.  If the user selects a column list 
            " without a table name of alias present, assume they want
            " the previous column list displayed.
            let base = s:save_prev_table
        endif

        if base != ""
            let compl_list        = s:SQLCGetColumns(base, 'csv')
            let s:save_prev_table = base
            " Join the column array into 1 single element array
            " but make the columns column separated
            let compl_list        = [join(compl_list, ', ')]
            let base              = ''
        endif
    elseif compl_type == 'resetCache'
        " Reset all cached items
        let s:tbl_name  = []
        let s:tbl_alias = []
        let s:tbl_cols  = []
        let s:syn_list  = []
        let s:syn_value = []
        return []
    else
        " Default to empty or not found
        let compl_list = []
        " Check if we have already cached the syntax list
        let list_idx = index(s:syn_list, compl_type, 0, &ignorecase)
        if list_idx > -1
            " Return previously cached value
            let compl_list = s:syn_value[list_idx]
        else
            " Request the syntax list items from the 
            " syntax completion plugin
            if compl_type == 'syntax'
                " Handle this special case.  This allows the user
                " to indicate they want all the syntax items available,
                " so do not specify a specific include list.
                let g:omni_syntax_group_include_sql = ''
            else
                " The user has specified a specific syntax group
                let g:omni_syntax_group_include_sql = compl_type
            endif
            let g:omni_syntax_group_exclude_sql = ''
            let syn_value                       = OmniSyntaxList()
            let g:omni_syntax_group_include_sql = s:save_inc
            let g:omni_syntax_group_exclude_sql = s:save_exc
            " Cache these values for later use
            let s:syn_list  = add( s:syn_list,  compl_type )
            let s:syn_value = add( s:syn_value, syn_value )
            let compl_list  = syn_value
        endif
    endif

    if base != ''
        " Filter the list based on the first few characters the user
        " entered
        let expr = 'v:val =~ "^'.base.'"'
        let compl_list = filter(copy(compl_list), expr)
    endif

    return compl_list
endfunc

function! s:SQLCWarningMsg(msg)
    echohl WarningMsg
    echomsg a:msg 
    echohl None
endfunction
      
function! s:SQLCErrorMsg(msg)
    echohl ErrorMsg
    echomsg a:msg 
    echohl None
endfunction
      
function! s:SQLCCheck4dbext()
    if !exists('g:loaded_dbext')
        let msg = "The dbext plugin must be loaded for dynamic SQL completion"
        call s:SQLCErrorMsg(msg)
        " Leave time for the user to read the error message
        :sleep 2
        return -1
    elseif g:loaded_dbext < 210
        let msg = "The dbext plugin must be at least version 2.10 " .
                    \ " for dynamic SQL completion"
        call s:SQLCErrorMsg(msg)
        " Leave time for the user to read the error message
        :sleep 2
        return -1
    endif
    return 1
endfunction

function! s:SQLCAddAlias(table_name, table_alias, cols)
    let table_name  = a:table_name
    let table_alias = a:table_alias
    let cols        = a:cols

    if g:omni_sql_use_tbl_alias != 'n' 
        if table_alias == ''
            if 'da' =~? g:omni_sql_use_tbl_alias
                if table_name =~ '_'
                    " Treat _ as separators since people often use these
                    " for word separators
                    let save_keyword = &iskeyword
                    setlocal iskeyword-=_

                    " Get the first letter of each word
                    " [[:alpha:]] is used instead of \w 
                    " to catch extended accented characters
                    "
                    let table_alias = substitute( 
                                \ table_name, 
                                \ '\<[[:alpha:]]\+\>_\?', 
                                \ '\=strpart(submatch(0), 0, 1)', 
                                \ 'g'
                                \ )
                    " Restore original value
                    let &iskeyword = save_keyword
                elseif table_name =~ '\u\U'
                    let initials = substitute(
                                \ table_name, '\(\u\)\U*', '\1', 'g')
                else
                    let table_alias = strpart(table_name, 0, 1)
                endif
            endif
        endif
        if table_alias != ''
            " Following a word character, make sure there is a . and no spaces
            let table_alias = substitute(table_alias, '\w\zs\.\?\s*$', '.', '')
            if 'a' =~? g:omni_sql_use_tbl_alias && a:table_alias == ''
                let table_alias = inputdialog("Enter table alias:", table_alias)
            endif
        endif
        if table_alias != ''
            let cols = substitute(cols, '\<\w', table_alias.'&', 'g')
        endif
    endif

    return cols
endfunction

function! s:SQLCGetColumns(table_name, list_type)
    let table_name   = matchstr(a:table_name, '^\w\+')
    let table_cols   = []
    let table_alias  = ''
    let move_to_top  = 1

    if g:loaded_dbext >= 210
        let saveSettingAlias = DB_listOption('use_tbl_alias')
        exec 'DBSetOption use_tbl_alias=n'
    endif

    " Check if we have already cached the column list for this table
    " by its name
    let list_idx = index(s:tbl_name, table_name, 0, &ignorecase)
    if list_idx > -1
        let table_cols = split(s:tbl_cols[list_idx])
    else
        " Check if we have already cached the column list for this table 
        " by its alias, assuming the table_name provided was actually
        " the alias for the table instead
        "     select *
        "       from area a
        "      where a.
        let list_idx = index(s:tbl_alias, table_name, 0, &ignorecase)
        if list_idx > -1
            let table_alias = table_name
            let table_name  = s:tbl_name[list_idx]
            let table_cols  = split(s:tbl_cols[list_idx])
        endif
    endif

    " If we have not found a cached copy of the table
    " And the table ends in a "." or we are looking for a column list
    " if list_idx == -1 && (a:table_name =~ '\.' || b:sql_compl_type =~ 'column')
    " if list_idx == -1 && (a:table_name =~ '\.' || a:list_type =~ 'csv')
    if list_idx == -1 
         let saveY      = @y
         let saveSearch = @/
         let saveWScan  = &wrapscan
         let curline    = line(".")
         let curcol     = col(".")

         " Do not let searchs wrap
         setlocal nowrapscan
         " If . was entered, look at the word just before the .
         " We are looking for something like this:
         "    select * 
         "      from customer c
         "     where c.
         " So when . is pressed, we need to find 'c'
         "

         " Search backwards to the beginning of the statement
         " and do NOT wrap
         " exec 'silent! normal! v?\<\(select\|update\|delete\|;\)\>'."\n".'"yy'
         exec 'silent! normal! ?\<\(select\|update\|delete\|;\)\>'."\n"

         " Start characterwise visual mode
         " Advance right one character
         " Search foward until one of the following:
         "     1.  Another select/update/delete statement
         "     2.  A ; at the end of a line (the delimiter)
         "     3.  The end of the file (incase no delimiter)
         " Yank the visually selected text into the "y register.
         exec 'silent! normal! vl/\(\<select\>\|\<update\>\|\<delete\>\|;\s*$\|\%$\)'."\n".'"yy'

         let query = @y
         let query = substitute(query, "\n", ' ', 'g')
         let found = 0

         " if query =~? '^\(select\|update\|delete\)'
         if query =~? '^\(select\)'
             let found = 1
             "  \(\(\<\w\+\>\)\.\)\?   - 
             " 'from.\{-}'  - Starting at the from clause
             " '\zs\(\(\<\w\+\>\)\.\)\?' - Get the owner name (optional)
             " '\<\w\+\>\ze' - Get the table name 
             " '\s\+\<'.table_name.'\>' - Followed by the alias
             " '\s*\.\@!.*'  - Cannot be followed by a .
             " '\(\<where\>\|$\)' - Must be followed by a WHERE clause
             " '.*'  - Exclude the rest of the line in the match
             let table_name_new = matchstr(@y, 
                         \ 'from.\{-}'.
                         \ '\zs\(\(\<\w\+\>\)\.\)\?'.
                         \ '\<\w\+\>\ze'.
                         \ '\s\+\%(as\s\+\)\?\<'.table_name.'\>'.
                         \ '\s*\.\@!.*'.
                         \ '\(\<where\>\|$\)'.
                         \ '.*'
                         \ )
             if table_name_new != ''
                 let table_alias = table_name
                 let table_name  = table_name_new

                 let list_idx = index(s:tbl_name, table_name, 0, &ignorecase)
                 if list_idx > -1
                     let table_cols  = split(s:tbl_cols[list_idx])
                     let s:tbl_name[list_idx]  = table_name
                     let s:tbl_alias[list_idx] = table_alias
                 else
                     let list_idx = index(s:tbl_alias, table_name, 0, &ignorecase)
                     if list_idx > -1
                         let table_cols = split(s:tbl_cols[list_idx])
                         let s:tbl_name[list_idx]  = table_name
                         let s:tbl_alias[list_idx] = table_alias
                     endif
                 endif

             endif
         else
             " Simply assume it is a table name provided with a . on the end
             let found = 1
         endif

         let @y        = saveY
         let @/        = saveSearch
         let &wrapscan = saveWScan

         " Return to previous location
         call cursor(curline, curcol)
         
         if found == 0
             if g:loaded_dbext > 201
                 exec 'DBSetOption use_tbl_alias='.saveSettingAlias
             endif

             " Not a SQL statement, do not display a list
             return []
         endif
    endif 

    if empty(table_cols)
        " Specify silent mode, no messages to the user (tbl, 1)
        " Specify do not comma separate (tbl, 1, 1)
        let table_cols_str = DB_getListColumn(table_name, 1, 1)

        if table_cols_str != ""
            let s:tbl_name  = add( s:tbl_name,  table_name )
            let s:tbl_alias = add( s:tbl_alias, table_alias )
            let s:tbl_cols  = add( s:tbl_cols,  table_cols_str )
            let table_cols  = split(table_cols_str)
        endif

    endif

    if g:loaded_dbext > 201
        exec 'DBSetOption use_tbl_alias='.saveSettingAlias
    endif

    if a:list_type == 'csv' && !empty(table_cols)
        let cols = join(table_cols, ', ')
        let cols = s:SQLCAddAlias(table_name, table_alias, cols)
        let table_cols = [cols]
    endif

    return table_cols
endfunction