Schemas but no tables in tray when using sqlcl

[walkabout21]

dadbod allows for changing the default Oracle binary to sqlcl "sql" instead of "sqlplus" by setting a global var g:dbext_default_ORA_bin . When this is set to run sqlcl, the db tray only shows schemas. This looks similar to issue #67 so it's probably a parsing issue.

[walkabout21]

Found one problem. It doesn't solve the issue but it's a start. The cell pattern looks for ---, but sqlcl for some unknown reason has replaced the sqlplus default --- with ___. Go figure. One thing I've noticed that I can't track down the root cause of is that the table count for sqlplus shows next to the schema/user, but with sqlcl it renders 25 spaces away from the schema name. @kristijanhusak any idea where I can find the ui function that determines that spacing between schema and table count? Is it determined by the min_len param?

[walkabout21]

The following config is working for me, but I just need to strip quotes from the CSV output from the table and schema output in the drawer. I haven't tackled the foreign key query yet.

let s:oracle_schemes_tables_query = "
      \SELECT /*csv*/ T.owner, T.table_name
      \ FROM (
      \ SELECT owner, table_name
      \ FROM all_tables
      \ UNION SELECT owner, view_name
      \ FROM all_views
      \ ) T
      \ JOIN all_users U ON T.owner = U.username
      \ WHERE U.common = 'NO'
      \ ORDER BY T.table_name"
let s:oracle = {
      \   'cell_line_number': 1,
      \   'cell_line_pattern': '^_\+\( \+_\+\)*',
      \   'default_scheme': '',
      \   'foreign_key_query': printf(s:oracle_args, s:oracle_foreign_key_query),
      \   'has_virtual_results': v:true,
      \   'parse_results': {results, min_len -> s:results_parser(results[15:-5], ',', min_len)},
      \   'parse_virtual_results': {results, min_len -> s:results_parser(results[15:-5], ',', min_len)},
      \   'requires_stdin': v:true,
      \   'quote': v:true,
      \   'schemes_query': "SELECT /*csv*/ username FROM all_users WHERE common = 'NO' ORDER BY username",
      \   'schemes_tables_query': s:oracle_schemes_tables_query,
      \   'select_foreign_key_query': printf(s:oracle_args, 'SELECT * FROM "%s"."%s" WHERE "%s" = %s'),
      \   'filetype': 'plsql',
      \ }

[walkabout21]

I am missing something here. If I format as csv and set the delimiter as ',' without any other changes I get schemas and tables with quotes around them. There is no option to remove the quote enclosures in sqlcl that I can find. So if I use the default I can replicate the output of sqlplus except the cell line uses instead of -, so I changed the cell line pattern to match and there is no change for the table counts. The only other thing that is different between the sqlplus output and the sqlcl output is that the sqlplus output autofolds, but I'm not sure why that would be if the output is identical, so I must be missing some formatting somewhere.

[walkabout21]

track down the issue sort of. sqlplus output is tab delimited and sqlcl is spaces with an overall fixed width. I'm not sure why and \s+ delimiter wouldn't catch that though. Maybe I'm misunderstanding the results_parser function?

[walkabout21]

@kristijanhusak how is the quotes property used? It's in the oracle definition dictionary but I can't find where it is used. I was hoping to leverage it for stripping quotes from the csv output. I've also tried adding quote stripping functionally to the results parser by using substitution on the row var in the mapping call, but it's not working as expected.

[kristijanhusak]

@walkabout21 you will probably have to change some of these https://github.com/kristijanhusak/vim-dadbod-ui/blob/36a67e67926345c0b11b32c378c057c7f9d9110d/autoload/db_ui/schemas.vim#L128-L142 depending on which ORA_bin is used, something like this:

      \   'cell_line_pattern': g:dbext_default_ORA_bin ==? 'sqlcl' ? 'fixed-pattern' : '^-\+\( \+-\+\)*',

I didn't give it a test since I don't really have any Oracle db experience. @Iron-E did an implementation for Oracle.

[walkabout21]

I created a function called strip_quotes that I used in the parse_results dictionary entry. That works for sqlcl. I just need to get the if logic right so it works for the default and g:dbext_default_ORA_bin == 'sql' , and I need to get the list indexes right so it's picking up all the tables.

function! s:strip_quotes(results) abort 
return split(substitute(join(a:results),'"','','g'))
endfunction

      \   'parse_results': {results, min_len -> s:results_parser(s:strip_quotes(results[15:-5]), ',', min_len)},
      \   'parse_virtual_results': {results, min_len -> s:results_parser(s:strip_quotes(results[15:-4]), ',', min_len)},

[walkabout21]

@kristijanhusak I submitted a PR. I tested pretty thoroughly, but it would be great if another Oracle dev could test it. The code is minimal since I just needed to add the strip quotes function and modify two dict entries if sql is the default ora binary. I did add a /csv/ hint to each query, but only sqlcl recognizes this as a formatting hint so it has no impact on the sqlplus side. I noticed a bug in the original in the current master branch that I thought I introduced. It looks like the table count is undercounting with sqlplus, but I can't figure out why since running the queries manually with both sqlcl and sqlplus returns the same higher count for one of my larger schemas.

[walkabout21]

Fixed by PR #173 closing issue