SqlComplete

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:

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.


CategorySql