bug#24646: [PATCH] Complete the name of PostgreSQL databases

["Simen Heggestøyl" <simenheg@gmail.com>]

[-- Attachment #1: Type: text/plain, Size: 223 bytes --]

Hi.

I recently started using sql.el with PostgreSQL, and thought it would
be nice if `sql-postgres' would provide completion of available
PostgreSQL databases.

Is something like the attached patch sufficient?

-- Simen



[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: 0001-Complete-the-name-of-PostgreSQL-databases.patch --]
[-- Type: text/x-patch, Size: 4757 bytes --]

From 564096195e41ef00e09fbfbaadfe8c7bf769773f Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Simen=20Heggest=C3=B8yl?= <simenheg@gmail.com>
Date: Thu, 6 Oct 2016 20:42:53 +0200
Subject: [PATCH] Complete the name of PostgreSQL databases

* lisp/progmodes/sql.el (sql-postgres-login-params): Complete database
name.
(sql-postgres-list-databases): New function returning a list of
available PostgreSQL databases.
(sql-get-login-ext): Don't require an exact match from completion.

* test/lisp/progmodes/sql-tests.el: New file with tests for sql.el.
---
 lisp/progmodes/sql.el            | 20 ++++++++++++----
 test/lisp/progmodes/sql-tests.el | 50 ++++++++++++++++++++++++++++++++++++++++
 2 files changed, 66 insertions(+), 4 deletions(-)
 create mode 100644 test/lisp/progmodes/sql-tests.el

diff --git a/lisp/progmodes/sql.el b/lisp/progmodes/sql.el
index a11d456..a1710e5 100644
--- a/lisp/progmodes/sql.el
+++ b/lisp/progmodes/sql.el
@@ -1072,14 +1072,26 @@ sql-postgres-options
   :version "20.8"
   :group 'SQL)
 
-(defcustom sql-postgres-login-params `((user :default ,(user-login-name))
-                                       (database :default ,(user-login-name))
-                                       server)
+(defcustom sql-postgres-login-params
+  `((user :default ,(user-login-name))
+    (database :default ,(user-login-name)
+              :completion ,(completion-table-dynamic
+                            (lambda (_) (sql-postgres-list-databases))))
+    server)
   "List of login parameters needed to connect to Postgres."
   :type 'sql-login-params
   :version "24.1"
   :group 'SQL)
 
+(defun sql-postgres-list-databases ()
+  "Return a list of available PostgreSQL databases."
+  (when (executable-find "psql")
+    (let ((res '()))
+      (dolist (row (process-lines "psql" "-l"))
+        (when (string-match "^ \\([[:alnum:]-_]+\\) +|.*" row)
+          (push (substring row (match-beginning 1) (match-end 1)) res)))
+      res)))
+
 ;; Customization for Interbase
 
 (defcustom sql-interbase-program "isql"
@@ -2951,7 +2963,7 @@ sql-get-login-ext
                               (file-name-nondirectory f)))))))
 
       ((plist-member plist :completion)
-       (completing-read prompt-def (plist-get plist :completion) nil t
+       (completing-read prompt-def (plist-get plist :completion) nil nil
                         last-value history-var default))
 
       ((plist-get plist :number)
diff --git a/test/lisp/progmodes/sql-tests.el b/test/lisp/progmodes/sql-tests.el
new file mode 100644
index 0000000..9dc92c2
--- /dev/null
+++ b/test/lisp/progmodes/sql-tests.el
@@ -0,0 +1,50 @@
+;;; sql-tests.el --- Tests for sql.el  -*- lexical-binding: t; -*-
+
+;; Copyright (C) 2016  Simen Heggestøyl
+
+;; Author: Simen Heggestøyl <simenheg@gmail.com>
+;; Keywords:
+
+;; This file is part of GNU Emacs.
+
+;; GNU Emacs is free software: you can redistribute it and/or modify
+;; it under the terms of the GNU General Public License as published by
+;; the Free Software Foundation, either version 3 of the License, or
+;; (at your option) any later version.
+
+;; GNU Emacs is distributed in the hope that it will be useful,
+;; but WITHOUT ANY WARRANTY; without even the implied warranty of
+;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+;; GNU General Public License for more details.
+
+;; You should have received a copy of the GNU General Public License
+;; along with GNU Emacs.  If not, see <http://www.gnu.org/licenses/>.
+
+;;; Commentary:
+
+;;
+
+;;; Code:
+
+(require 'cl-lib)
+(require 'ert)
+(require 'sql)
+
+(ert-deftest sql-tests-postgres-list-databases ()
+  "Test that output from `psql -l' is parsed correctly."
+  (cl-letf
+      (((symbol-function 'executable-find)
+        (lambda (_command) t))
+       ((symbol-function 'process-lines)
+        (lambda (_program &rest _args)
+          '("                                  List of databases                                  "
+            "   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   "
+            "-----------+----------+----------+-------------+-------------+-----------------------"
+            " db-name-1 | foo-user | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       "
+            " db_name_2 | foo-user | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       "
+            "(2 rows)                                                                             "))))
+    (should (equal (sort (sql-postgres-list-databases) #'string<)
+                   '("db-name-1" "db_name_2")))))
+
+(provide 'sql-tests)
+;;; sql-tests.el ends here
-- 
2.9.3