Add option for setting g:db from the menu

[idanarye]

Sometimes it's useful to run SQL commands from inside a source file - either an SQL string inside an host language or some lines from a .sql file - but dadbod-ui only supports running SQL queries from its own buffer queries. It would be nice if we the :DBUI panel had an option to set g:db so that we can use dadbod-ui to manage the database connections and regular dadbod's :DB to run queries from source files.

[kristijanhusak]

There's already a command that allows you to partially use this.
For example, if you have an sql file, you can use :DBUIFindBuffer. It will prompt you for db which you want to assign to it, or use the first one if there's only one in the list. From there you can use that buffer as it is a dbui buffer.

This is not working for non sql filetypes, because dbui converts it to sql filetype. I plan to improve that.

[kristijanhusak]

I did the improvement. Now you can assign any filetype to dbui with :DBUIFindBuffer. It will not mess up the filetype or anything.
It should also allow using vim-dadbod-completion completion, but you need to make sure that it's included for your non-sql filetype. So if you use coc, you must include this in your coc config:

coc.source.db.filetypes: ['sql', 'yourfiletypehere']

If you can give it a test and let me know how it looks.

[idanarye]

Unless there is something I'm missing dbui can only execute the entire content of the dbui buffer. This doesn't work very well for inline SQL, because most of the buffer is not SQL.

Say I have this pseudo-C:

void main() {
    run_sql(con, "SELECT * FROM foo;");
}

If I turn this buffer to a dbui buffer with :DBUIFindBuffer I can only execute its entire content - which is obviously not valid SQL. It's also not useful to have dbui automatically execute it as query on save...

[kristijanhusak]

You have a mapping for executing visual selection (https://github.com/kristijanhusak/vim-dadbod-ui/blob/master/doc/dadbod-ui.txt#L315), and by default it's mapped to <Leader>S in sql buffers. Problem is that it's not mapped for non-sql buffers, so you would have to do that manually. I think optimal setup for your case would be this:

augroup dbui_custom
 autocmd!
 autocmd FileType cpp vnoremap <buffer><silent><Leader>S <Plug>(DBUI_ExecuteQuery)
augroup END

" Do not execute whole buffer on save
let g:db_ui_execute_on_save = 0

With this, you do :DBUIFindBuffer in your c file, visual select SELECT * FORM foo;, and press <Leader>S. This should execute that selection only. If you use vim-dadbod-completion, you should also get completion for the selected database.

[idanarye]

Doesn't work...

Anyway, I managed to hack it it with this script:

function! s:getDbuiScriptNumber() abort
    " Make sure it's loaded:
    call db_ui#get_conn_info('')

    for l:line in split(execute('scriptnames'), '\n')
        if l:line =~ '\vautoload/db_ui.vim$'
            return str2nr(split(l:line, ':')[0])
        endif
    endfor
endfunction

function! s:getDbuiInstance() abort
    return function(printf('<SNR>%d_init', s:getDbuiScriptNumber()))()
endfunction

function! s:chooseDbuiConnection() abort
    let l:keys = keys(s:getDbuiInstance().dbs)
    call fzf#run({
                \ 'source': l:keys,
                \ 'sink': function('s:setDbConnection'),
                \ 'down': '~40%',
                \ })
endfunction

function! s:setDbConnection(key) abort
    let b:db = s:getDbuiInstance().dbs[a:key].url
endfunction

command! DBUISetDbConnection call s:chooseDbuiConnection()

[kristijanhusak]

Which part doesn't work?

[kristijanhusak]

I made a mistake in my comment, it shouldn't be vnoremap but vmap:

autocmd FileType cpp vmap <buffer><silent><Leader>S <Plug>(DBUI_ExecuteQuery)

I see that you are doing this nasty script id parsing to get all available connections. Do you want to get list of available connections in format [{ name: db_name, url: db_url }] through db_ui#get_conn_info so you can use your fzf?

[idanarye]

The mapping doesn't work. I see [DBUI] Executing query...Done after 0.042320 sec. but no result window is opened.

At any rate I'd still prefer to set b:db (or g:db) and keeping g:db_ui_execute_on_save on because it is useful in actual dbui buffers, so a db_ui#list_conns() or something would be nice...

[kristijanhusak]

That's odd. Does it work if you try to execute visual selection in dbui sql buffer?

I added db_ui#connections_list() function, which returns name, url, source and is_connected properties.

[idanarye]

Weird - I tried the mapping again after updating the plugin, and it worked...

Anyway db_ui#connections_list() works like a charm. Thanks!