Adding databases/views to drawer

[geg2102]

Love the plugin!

Has there been any thought about adding databases and views to the drawer? What I would like to do is connect to the server--say, jdbc://sqlserver.example, and then have a databases drop down menu that functions in a way similar to how Schemas functions now. So first choose server, then choose database, then choose schema. And similarly, it would also be hugely beneficial to see views. So the interface would be server --> databases --> schemas --> [tables, views]. In the case of sql server, for example, it would be SELECT name FROM sys.databases --> SELECT schema_name FROM information_schema.schemata --> [SELECT table_schema, table_name FROM information_schema.tables, SELECT name FROM sys.views]. Similarly for postgres/mysql/oracle. This is similar to functionality you may find in DataGrip.

[geg2102]

I was able to quickly put something together that implements this idea in a forked version of the repo. I've never written vimscript before, and it's very sloppily done, but it serves my purpose. For the time being, I only use postgres and sqlserver at work, and I'm not as worried about getting the schemas so long as I have the databases and all the tables and views (almost always dbo, except a few cases). For sqlsever the biggest change is having a query like:

SELECT TOP 1 * INTO ##test
FROM INFORMATION_SCHEMA.TABLES
DELETE FROM ##test·
EXEC sp_MSforeachdb 'USE [?] INSERT INTO ##test SELECT * FROM INFORMATION_SCHEMA.TABLES;'
SELECT * FROM ##test
WHERE TABLE_CATALOG NOT IN ('master', 'tempdb', 'msdb')
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME

Then in the populate_schemas function of drawer.vim, I use your same logic but I use the following to add tables:

let databases = scheme.parse_results(db_ui#schemas#query(a:db, scheme, scheme.all_tables_query), 4)
let schemas = scheme.parse_results(db_ui#schemas#query(a:db, scheme, scheme.database_query), 1)
let tables_by_schema = {}
for [table_catalog, table_schema, table_name, table_type] in databases
  if !has_key(tables_by_schema, scheme_name)
    let tables_by_schema[table_catalog] = []
  endif
  call add(tables_by_schema[table_catalog], table_name)
  call add(a:db.tables.list, table_name)
endfor

So, a definite hack, but at least it works for now (for my idiosyncratic purposes). I also added a databases key to the db object dictionary, with the intention of populating databases, schemas and tables/views in the future (my implementation is populating databases/tables now, and not schemas). There's a few other small changes, but that's the main thrust of how I went about adding all the tables/views/databases. If I get more time over the next few weeks, I'll clean it up and update you. Thanks again for the awesome plugin!

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[iabdelkareem]

+1 I was about to create an issue to add this feature. It'll be great to have.