Is there a company backend for completion in sql-interactive-mode?

Question

I'm using sql-interactive-mode, and I'm in need for a Company backends that will complete SQL keywords, and preferably, also column/table names of the used database.

When looking around for any completion, to my surprise, there was no backend for SQL yet. I found this snippet, but that didn't worked properly.

It's possible it doesn't exist yet, and I might to create an own backend for SQL. But I find it hard to believe there is no Company backend for one of the most common languages.

Completing keywords should be simple enough with one of the dict-like backends. Database-specific data is much harder, especially if you want it to work for more than one database... — wasamasa, Aug 18 '16 at 16:48
iIf completing keywords would be simple enough, do you have any idea why there is no SQL-backend for Company yet? And I agree with you on the latter, it would be hard, but when you have column contents in your buffer, it would be completed with `company-dabbrev` I guess? — ReneFroger, Aug 18 '16 at 21:26
It would merely be a new entry to `company-keywords.el`, so feel free to contribute! And yes, the dabbrev backend would catch that (and everything else in your buffer)... — wasamasa, Aug 19 '16 at 17:07

Jonakand · Accepted Answer · 2016-08-20T14:38:11.863

I had a similar problem and decided to create my own backend. One of the existing backends (C++?) was used as a template and I modified it to create the new back end which behaves like a dictionary.

In my setup SQLi buffers are automatically named to match the database that is being connected to, eg. *DB:DBASE1DM*. The backend contains an alist for each database with the schemas, tables, and columns. When I want to complete something the name of the buffer is used to get the correct list of candidates for that database.

(defun ry/company-sql-upper-lower (&rest lst)
  (nconc (sort (mapcar 'upcase lst) 'string<) lst))

(defvar ry/company-sql-alist
  `(("DBASE1"               ;; Database name w/o environment suffix.
     "DBASE1DM" "DBASE1UM"  ;; Database name with environment suffix.
     "SCHEMA1" "SCHEMA2"
     "TABLE1" "TABLE2"
     "COLUMN1" "COLUMN2")
    ("DBASE2"
     "DBASE2DM" "DBASE2UM"
     "SCHEMA1" "SCHEMA2"
     "TABLE1" "TABLE2"
     "COLUMN1" "COLUMN2"))
    "Alist mapping sql-mode to candidates.")

(defun ry/company-sql (command &optional arg &rest ignored)
  "`company-mode' back-end for SQL mode based on database name."
  (interactive (list 'interactive))
  (cl-case command
    (interactive (company-begin-backend 'ry/company-sql))
    (prefix (and (assoc (substring (buffer-name (current-buffer)) 4 -3) ry/company-sql-alist)
                 (not (company-in-string-or-comment))
                 (or (company-grab-symbol) 'stop)))
    (candidates
     (let ((completion-ignore-case t)
           (symbols (cdr (assoc (substring (buffer-name (current-buffer)) 4 -3) ry/company-sql-alist))))       
       (all-completions arg (if (consp symbols)
                                symbols
                              (cdr (assoc symbols company-sql-alist))))))
    (sorted t)))

This has the downside that it's not a smart completion and that including new databases or making modifications to existing databases is a manual process. A couple queries can be used to gather the data and then it isn't very hard to massage it into the format needed for the backend.

The function below handles connecting to a database and changing the names of the buffers to match the database that is connected to.

(defun ry/sql-open-database (database username password)
  "Open a SQLI process and name the SQL statement window with the name provided."
  (interactive (list
                (read-string "Database: ")
                (read-string "Username: ")
                (read-passwd "Password: ")))
  (let ((u-dbname (upcase database)))
    (setq sql-set-product "db2")

    (sql-db2 u-dbname)
    (sql-rename-buffer u-dbname)
    (setq sql-buffer (current-buffer))
    (sql-send-string (concat "CONNECT TO " database " USER " username " USING " password ";"))

    (other-window 1)
    (switch-to-buffer (concat "*DB:" u-dbname "*"))
    (sql-mode)
    (sql-set-product "db2")
    (setq sql-buffer (concat "*SQL: " u-dbname "*"))))

thanks for your reply, it's really appreciated! However, I had some difficulties when trying your function. After adding to company with `(add-to-list 'company-backends 'ry/company-sql) (add-to-list 'company-backends 'ry/company-sql-alist)`, I got the following error in `M-x sql-mysql` after trying a word: `Company: An error occurred in auto-begin Args out of range: "*SQL*", 4, -3`. How could I interpret this error message? — ReneFroger, Aug 20 '16 at 11:38
I have updated the answer to include the function that I use to connect to a database. It handles changing the buffer names to match the database that is related to the buffers. Your buffer name is too short so the substring is failing. The substring is used to remove the `*DB:` and environment suffix from the buffer name in order to get the database name so the correct list of completions is used. The function assumes that the buffer name for completions will be in the form `*DB:ACCOUNTSDM*`. The substring would pull `ACCOUNTS` from the buffer name. — Jonakand, Aug 20 '16 at 14:46
Thanks for your reply. I clearly need to learn Lisp, since I couldn't figure out how I could modify it for `mysql` instead of `db2`. But your contribution is really appreciated, so I validated your reply. Thanks for that. — ReneFroger, Aug 20 '16 at 18:45

Is there a company backend for completion in sql-interactive-mode?

1 Answers1