Cupcake - Simple SQL query runner

cupcake.txt

*cupcake.txt*	SQL query runner

Author:  Peter Rincker                                    *cupcake-author*
License: Same terms as Vim itself (see |license|)

This plugin is only available if 'compatible' is not set.

==============================================================================
*cupcake*

Simple SQL query tool. 

==============================================================================

							*:Cupcake*
:[range]Cupcake
			Run query of lines in [range].
:[range]Cupcake [cupcakeprg]
			Same as |:Cupcake| but use query program, [cupcakeprg].
			See |Cupcake-query-program|.

==============================================================================
*Cupcake-results-commands*

							*cupcake-:Refresh*
:Refresh		Rerun sql statement.

r			Same as |cupcake-:Refresh|.
q			Close query window.

==============================================================================
*Cupcake-sql-mappings*

Cupcake provides some convenient mappings for sql |'filetype'|.

							*cupcake-cr*
cr{motion}		Run sql indicated by {motion}.
							*cupcake-crr*
crr			Run sql for current line.
							*cupcake-v_gr*
{Visual}R		Run sql selected via {Visual}

==============================================================================
*Cupcake-query-program*

Cupcake requires a program (with possible agruments) to send SQL statements.

Examples: >
	mysql -v -v -v -t -uUser -pPassword db_name
<

Cupcake looks for query program setting in the following order:
	1. [cupcakeprg] supplied to |:Cupcake| [cupcakeprg].
	2. Modeline like setting
	3. Buffer variable, b:cupcakeprg
	4. Global variable, g:cupcakeprg

Modeline example: >
	-- cupcake: mysql -v -v -v -t -uUser -pPassword db_name
	/* cupcake: mysql -v -v -v -t -uUser -pPassword db_name: */
<

 vim:tw=78:ts=8:ft=help:norl:

cupcake.vim

if exists('g:loaded_cupcake') || &cp || v:version < 700
  finish
endif
let g:loaded_cupcake = 1

function! s:query(connection, sql)
  let cmd = a:connection !~ '^\s*$' ? a:connection : s:connection()
  if cmd == ''
      call s:warn('No connection available. Please see :h Cupcake-query-program')
    else
      call s:results(cmd, a:sql)
  endif
endfunction

function! s:warn(msg)
  echohl WarningMsg
  echo a:msg
  echohl None
endfunction

function! s:connection()
  if &modeline && &modelines > 0
    let modeline1 = '\V\S\s\+cupcake:\s\+\zs\.\+\S\$'
    let modeline2 = '\V\S\s\+cupcake:\s\+\zs\.\+\ze:\s*\S\.\*\$'
    let last = line('$')
    for i in range(1, min([&modelines, last])) + range(max([1, last - &modelines]), last)
      let line = getline(i)
      if line =~ modeline1
        return matchstr(line, modeline1)
      elseif line =~ modeline2
        return matchstr(line, modeline2)
      endif
    endfor
  endif
  return get(b:, 'cupcakeprg', get(g:, 'cupcakeprg', ''))
endfunction

function! s:yank()
  let reg = @@
  execute "normal! \<esc>gvy"
  let str = @@
  let @@ = reg
  return split(str, '\n')
endfunction

function! s:op(type, ...)
	  let sel_save = &selection
	  let &selection = "inclusive"
	  let reg_save = @@

	  if a:0 
	    silent exe "normal! `<" . a:type . "`>y"
	  elseif a:type == 'line'
	    silent exe "normal! '[V']y"
	  elseif a:type == 'block'
	    silent exe "normal! `[\<C-V>`]y"
	  else
	    silent exe "normal! `[v`]y"
	  endif

    call s:query('', split(@@, '\n'))

	  let &selection = sel_save
	  let @@ = reg_save
endfunction

function! s:results(cmd, sql)
  silent! pedit [QueryResults]
  wincmd P
  let b:cupcake = {'sql': a:sql, 'cmd': a:cmd}
  setlocal buftype=nofile
  setlocal bufhidden=delete
  setlocal noswapfile
  setlocal nobuflisted
  setlocal nowrap
  command! -buffer -nargs=0 -bar Refresh call s:refresh()
  nnoremap <buffer> <silent> q :<c-u>bdelete<cr>
  nnoremap <buffer> <silent> r :<c-u>Refresh<cr>
  nmap <buffer> <silent> >> :<c-u>call <SID>column_search('b')<cr>
  nmap <buffer> <silent> << :<c-u>call <SID>column_search('e')<cr>
  nmap <buffer> <silent> [[ :<c-u>call <SID>section_search('bW')<cr>
  nmap <buffer> <silent> ]] :<c-u>call <SID>section_search('W')<cr>
  Refresh
endfunction

function! s:refresh()
  setlocal modifiable
  setlocal noreadonly
  silent %d_
  call setline(1, b:cupcake.sql)
  silent! execute '%!' . b:cupcake.cmd
  setlocal nomodifiable
  setlocal readonly
  call search('\(^+[-+]\+$\|^\*.*\*$\|\ze.\+\_^-[- ]*-$\)')
  normal! zt
endfunction

function! s:column_search(flags)
  call search('\v%(^\||^\+|\s\|\s|-\+-|\|$|\+$)', a:flags, line('.'))
endfunction

function! s:section_search(flags)
  call search('\(^\W\+$\|^\*.*\*$\)', a:flags)
endfunction

function! cupcake#complete(finestart, base) abort
  if a:finestart
	    " locate the start of the word
	    let line = getline('.')
	    let start = col('.') - 1
	    while start > 0 && line[start - 1] =~ '\k'
	      let start -= 1
	    endwhile
	    return start
  else
    let base = '^' . a:base
    for o in s:sql_objects()
      if o =~ base
        call complete_add(o)
      endif
    endfor
    return []
  endif
endfunction

function! s:sql_objects() abort
  let objects = {}
  let sql = get(b:, 'cupcake_complete_sql')
  let connection = s:connection()
  if connection == ''
    return s:warn('No connection available. Please see :h Cupcake-query-program')
  endif
  let results = split(system(connection, sql), '\n')
  let flag = 0
  let style = 'table'
  for r in results
    if r =~ '^[*-]*.*\%(Record\|Row\).*\[*-]$' 
      let flag = 1
      let style = 'vertical'
    elseif flag && style == 'vertical' && (r =~ '^\s*column\s*:' || r =~ '^\s*table\s*:')
      let objects[matchstr(r, '[^:]*:\s*\zs.*$')] = 1
    elseif r =~ '^+[-+]\++$'
      let flag = 1
      let style = 'table'
    elseif flag && style == 'table' && r =~ '^|\s' && r =~ '\s|$'
      let [column, table] = split(r, '\s*|\s*', 0)
      let objects[s:trim(column)] = 1
      let objects[s:trim(table)] = 1
    endif
  endfor
  return sort(keys(objects))
endfunction

function! s:trim(str)
  return substitute(substitute(a:str, '^\s*', '', ''), '\s*$', '', '')
endfunction

command! -nargs=* -range Cupcake call s:query(<q-args>, getline(<line1>, <line2>))

nmap <silent> <script> <Plug>CupcakeQueryLine :<c-u>Cupcake<cr>
nmap <silent> <script> <Plug>CupcakeQueryOp :<c-u>set opfunc=<SID>op<cr>g@
vmap <silent> <script> <Plug>CupcakeQueryVisual :<c-u>call <SID>op(visualmode(), 1)<cr>

augroup CupcakeSql
  autocmd!
  autocmd FileType sql,mysql,pgsql call s:bind()
augroup END

function! s:bind()
  nmap <buffer> crr <Plug>CupcakeQueryLine
  nmap <buffer> cr  <Plug>CupcakeQueryOp
  xmap <buffer> R   <Plug>CupcakeQueryVisual
  if &filetype =~ 'mysql'
    set completefunc=cupcake#complete
    let b:cupcake_complete_sql = 'SELECT c.column_name, t.table_name FROM information_schema.tables AS t JOIN information_schema.columns AS c ON t.table_schema = c.table_schema AND t.table_name = c.table_name'
  endif
endfunction

Put cupcake.txt in ~/.vim/doc/ and cupcake.vim into ~/.vim/plugin/ to install. Then run :helptags