Feature Request: Enhance Foreign Key Lookup to Consider Current Database Context in `schemas.vim`

[ESSO0428]

Hello,

I've been using your tool and found it extremely useful for managing and navigating databases. However, I would like to suggest an enhancement related to the foreign key lookup functionality.

Currently, the tool performs foreign key lookups without specifically considering the current database context. This approach might inadvertently lead to jumping to a table with the same name in a different database, which could be confusing or misleading.

For example, in the case of MySQL, the current implementation in schemas.vim is as follows:

let s:mysql_foreign_key_query =  "
      \ SELECT referenced_table_name, referenced_column_name, referenced_table_schema
      \ from information_schema.key_column_usage
      \ where referenced_table_name is not null and column_name = '{col_name}' LIMIT 1"

To enhance the accuracy and context-awareness of foreign key navigation, I propose modifying the query to consider the current database. This could be achieved by adding a condition to check against the current database, as shown below:

let s:mysql_foreign_key_query =  "
      \ SELECT referenced_table_name, referenced_column_name, referenced_table_schema
      \ from information_schema.key_column_usage
      \ where referenced_table_name is not null and column_name = '{col_name}' 
      \ and table_schema = {database_name} LIMIT 1"

This modification ensures that the foreign key jump functionality is limited to the context of the current database, thus avoiding any cross-database confusion.

Additionally, for dbout.vim, the following code can be incorporated:

let content = join(readfile(b:db_input), "\n")
let content = substitute(content, '`', '', 'g')

let b:dbname = '(SELECT DATABASE())'
if match(content, '\vfrom\s+(\w+)\.\w+', 'i') >= 0
  let b:dbname = matchstr(content, '\vfrom\s+\zs\w+\ze\.\w+', 'i')
  let b:dbname = "'" . b:dbname . "'"
endif
let foreign_key_query = substitute(scheme.foreign_key_query, '{col_name}', field_name, '')
let foreign_key_query = substitute(foreign_key_query, '{database_name}', b:dbname, '')

to be added within db_ui#dbout#jump_to_foreign_table().

I believe this enhancement would significantly improve user experience, especially in environments with multiple databases having tables with identical names.

Thank you for considering this suggestion, and I look forward to any thoughts you might have on this proposal.

Best regards,