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
If you want to add support for a new database vendor, proceed as follows:
‘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-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-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
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.