This package provides a framework for completion of column and table names for SqlMode. It supports Oracle. For Mysql, see SqlCompletion
First, you have to set the data dictionary up. Call ‘M-x sql-oracle-data-dictionary’. This will send a select statement to the host that dumps the data dictionary. The data dictionary is then read and stored in a lisp variable. Based on this information, ‘M-x sql-complete’ now completes columns and tables. Bind ‘sql-complete’ to any key you like in ‘sql-mode-map’ and ‘sql-interactive-mode-map’.
If you want to add support for a new database vendor, proceed as follows:
‘sql-TYPE-data-dictionary’ similar to ‘sql-oracle-data-dictionary’. Its value must be a SQL statement which returns the data dictionary as a long list of `(“TABLE NAME” “COLUMN NAME”)’ rows. You don’t need to parse the resulting output – the framework does that for you.‘sql-TYPE-data-dictionary’ similar to ‘sql-oracle-data-dictionary’. There will be no name clash as variables and functions are in different name spaces. The function does nothing but save the parsed data dictionary in the ‘sql-data-dictionary’ variable. This effectively switches the data dictionary to use.Here is the Oracle example again:
(defcustom sql-oracle-data-dictionary
"select '(\"'||table_name||'\" \"'||column_name||'\")'
from user_tab_columns
order by table_name;"
"SQL Statement to determine all tables and columns."
:group 'SQL
:type 'string) (defun sql-oracle-data-dictionary ()
(interactive)
(setq sql-data-dictionary
(sql-data-dictionary sql-oracle-data-dictionary)))As you can see, it is not too difficult.
The parsed data dictionary as returned by the function ‘sql-data-dictionary’ and stored in the variable of the same name is very simple: It as an alist where the table name is the key and the list of columns is the value. Here is an example:
'(("PERSON" "PERSONID" "NAME" "FIRSTNAME" "JOBID")
("JOB" "JOBID" "TITLE" "DESCRIPTION" "BASE_SALARY"))You can bind the functions ‘sql-complete’ and ‘sql-complete-table’ (which only completes on table names to any key you want.
See also SqlCompletion (for Mysql).
Mysql has a problem with this setup. There are no SELECT’able metadata views. There are commands to retreive the metadata:
show tables;
show columns from some_table;But the format they return is fixed. I’m about to write a defun to parse & munge the results into the required format - but it seems like the interface might need to be modified. Ideas? - JoshRobb
Hi Josh - Mysql has an “information_schema” table you can select from. I got sql-complete.el to work with Mysql with a bit of hacking - added these two:
(defcustom sql-mysql-data-dictionary
"select concat('\\(', '\\\"', table_name, '\\\" \\\"', column_name, '\\\"', '\\)')
from information_schema.columns
order by table_name;"
"SQL Statement to determine all tables and columns."
:group 'SQL
:type 'string) (defun sql-mysql-data-dictionary ()
(interactive)
;; FIXME No cleanup
(setq sql-data-dictionary
(sql-data-dictionary sql-mysql-data-dictionary)))and also modified the parsing code to allow for tabular-format Mysql data:
- (when (looking-at "^(.*)$") - (let* ((entry (car (read-from-string (match-string 0)))) + (when (looking-at "^| *\\((.*)\\)" ) + (let* ((entry (car (read-from-string (match-string 1))))
Finally, it seems to be necessary to set ‘comint-prompt-regexp’ to something like “mysql>” and set ‘comint-use-prompt-regexp-instead-of-fields’ to t (does not work correctly with input and output fields, this could probably be fixed in sql.el) - CrazyYeti
And, right after doing that (d’oh!) I found this: SqlCompletion which provides more fully-functional completion support for Mysql (completes on sql keywords, not just tables/columns/etc). - CrazyYeti
What about completion for table names with schema prefix? I slightly customize sql-oracle-data-dictionary
select '("'||owner||'.'||table_name||'" "'||column_name||'")'
from all_tab_columns
order by table_name;But it seems that after dot completion looks only for columns names.