Patch for sql.el (quote table names in sql-list-table)

Patch for sql.el (quote table names in sql-list-table)

[Matthew Carter]

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

Hello all,

The following patch quotes a table name when the :quotation value is set
for product features in sql.el mode, to prevent unanticipated SQL errors
in cases where the table name has something like a space (or other
reserved character) in it, as it may not be immediately apparent that
the user should have entered their quotation characters in the
sql-list-table prompt during sql-mode.

The previous behavior (no quoting) can be enabled in the user's config
file by adding something such as:

(sql-set-product-feature 'postgres :quotation nil)


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: Quote the table name --]
[-- Type: text/x-diff, Size: 1237 bytes --]

diff --git a/lisp/progmodes/sql.el b/lisp/progmodes/sql.el
index 65e94ba..88e1417 100644
--- a/lisp/progmodes/sql.el
+++ b/lisp/progmodes/sql.el
@@ -430,6 +430,7 @@ file.  Since that is a plaintext file, this could be dangerous."
      :prompt-regexp "^mysql> "
      :prompt-length 6
      :prompt-cont-regexp "^    -> "
+     :quotation "`"
      :syntax-alist ((?# . "< b"))
      :input-filter sql-remove-tabs-filter)
 
@@ -465,6 +466,7 @@ file.  Since that is a plaintext file, this could be dangerous."
      :prompt-regexp "^\\w*=[#>] "
      :prompt-length 5
      :prompt-cont-regexp "^\\w*[-(][#>] "
+     :quotation "\""
      :input-filter sql-remove-tabs-filter
      :terminator ("\\(^\\s-*\\\\g$\\|;\\)" . "\\g"))
 
@@ -3768,6 +3770,9 @@ ENHANCED, displays additional details about each column."
       (user-error "No SQL interactive buffer found"))
     (unless name
       (user-error "No table name specified"))
+    (when (sql-get-product-feature sql-product :quotation)
+      (let ((quote (sql-get-product-feature sql-product :quotation)))
+        (setf name (format "%s%s%s" quote name quote))))
     (sql-execute-feature sqlbuf (format "*List %s*" name)
                          :list-table enhanced name)))
 \f

[-- Attachment #3: Type: text/plain, Size: 55 bytes --]


-- 
Matthew Carter (m@ahungry.com)
http://ahungry.com

Re: Patch for sql.el (quote table names in sql-list-table)

[Michael R. Mauger]

I'd discourage this change. The List Tables prompt does not prevent use of
identifier enclosing characters and by not using them you get the default
case conversion behavior. There are also cases with MS SQL Server where the
enclosing character is not a single character but rather a [ and ] pair.

I'd also be concerned when I ask to list the table in another schema since
we'd now be enclosing the schema and table name within the quotes which is
clearly not what we want.



--
View this message in context: http://emacs.1067599.n5.nabble.com/Patch-for-sql-el-quote-table-names-in-sql-list-table-tp385543p385599.html
Sent from the Emacs - Dev mailing list archive at Nabble.com.

Re: Patch for sql.el (quote table names in sql-list-table)

[Matthew Carter]

"Michael R. Mauger" <michael@mauger.com> writes:

> I'd discourage this change. The List Tables prompt does not prevent use of
> identifier enclosing characters and by not using them you get the default
> case conversion behavior. There are also cases with MS SQL Server where the
> enclosing character is not a single character but rather a [ and ] pair.
>
> I'd also be concerned when I ask to list the table in another schema since
> we'd now be enclosing the schema and table name within the quotes which is
> clearly not what we want.
>
>
>
> --
> View this message in context: http://emacs.1067599.n5.nabble.com/Patch-for-sql-el-quote-table-names-in-sql-list-table-tp385543p385599.html
> Sent from the Emacs - Dev mailing list archive at Nabble.com.
>

The original problem/issue I had with this arised from the following
function in sql.el (the one that generates the postgres completion
candidate list):

(defun sql-postgres-completion-object (sqlbuf schema)

....<snip>....

      ;; Return the list of table names (public schema name can be omitted)
      (mapcar #'(lambda (tbl)
                  (if (string= (car tbl) "public")
                      (cadr tbl)
                    (format "%s.%s" (car tbl) (cadr tbl))))
              cl))))

As you can see, in a schema that includes tables with spaces in their
names, those become completion objects ("Foo Bar" for instance,
sans quotes of course).

When something that then goes to make use of the completion list tries
to pass along "Foo Bar" (sans quotes) to the function that I
patched, it causes an issue.

With a little modification, my original patch could account for
differing open/close quotations as you referenced (and the patch itself
does nothing if the quotation feature is not set).

Do you think a better fix would be to quote the postgres completion list
when necessary?  (if a space in one of the table names is found)?

Because as it is, it doesn't work well with some completion modes like
helm (which just display the completion list candidates verbatim, and
expect the user to press RET on one of them to select it).

-- 
Matthew Carter (m@ahungry.com)
http://ahungry.com

Re: Patch for sql.el (quote table names in sql-list-table)

[Matthew Carter]

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

Matthew Carter <m@ahungry.com> writes:

> "Michael R. Mauger" <michael@mauger.com> writes:
>
>> I'd discourage this change. The List Tables prompt does not prevent use of
>> identifier enclosing characters and by not using them you get the default
>> case conversion behavior. There are also cases with MS SQL Server where the
>> enclosing character is not a single character but rather a [ and ] pair.
>>
>> I'd also be concerned when I ask to list the table in another schema since
>> we'd now be enclosing the schema and table name within the quotes which is
>> clearly not what we want.
>>
>>
>>
>> --
>> View this message in context: http://emacs.1067599.n5.nabble.com/Patch-for-sql-el-quote-table-names-in-sql-list-table-tp385543p385599.html
>> Sent from the Emacs - Dev mailing list archive at Nabble.com.
>>
>
> The original problem/issue I had with this arised from the following
> function in sql.el (the one that generates the postgres completion
> candidate list):
>
> (defun sql-postgres-completion-object (sqlbuf schema)
>
> ....<snip>....
>
>       ;; Return the list of table names (public schema name can be omitted)
>       (mapcar #'(lambda (tbl)
>                   (if (string= (car tbl) "public")
>                       (cadr tbl)
>                     (format "%s.%s" (car tbl) (cadr tbl))))
>               cl))))
>
> As you can see, in a schema that includes tables with spaces in their
> names, those become completion objects ("Foo Bar" for instance,
> sans quotes of course).
>
> When something that then goes to make use of the completion list tries
> to pass along "Foo Bar" (sans quotes) to the function that I
> patched, it causes an issue.
>
> With a little modification, my original patch could account for
> differing open/close quotations as you referenced (and the patch itself
> does nothing if the quotation feature is not set).
>
> Do you think a better fix would be to quote the postgres completion list
> when necessary?  (if a space in one of the table names is found)?
>
> Because as it is, it doesn't work well with some completion modes like
> helm (which just display the completion list candidates verbatim, and
> expect the user to press RET on one of them to select it).

Attached is an alternate patch which at least fixes the postgres
completion list to quote both the schema and table names (I can't think
of a situation in which it would hurt for this to be quoted).


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: postgres completion object patch --]
[-- Type: text/x-diff, Size: 625 bytes --]

diff --git a/lisp/progmodes/sql.el b/lisp/progmodes/sql.el
index 65e94ba..fd59f46 100644
--- a/lisp/progmodes/sql.el
+++ b/lisp/progmodes/sql.el
@@ -4948,8 +4948,8 @@ Try to set `comint-output-filter-functions' like this:
       ;; Return the list of table names (public schema name can be omitted)
       (mapcar #'(lambda (tbl)
                   (if (string= (car tbl) "public")
-                      (cadr tbl)
-                    (format "%s.%s" (car tbl) (cadr tbl))))
+                      (format "\"%s\"" (cadr tbl))
+                    (format "\"%s\".\"%s\"" (car tbl) (cadr tbl))))
               cl))))
 
 \f

[-- Attachment #3: Type: text/plain, Size: 56 bytes --]



-- 
Matthew Carter (m@ahungry.com)
http://ahungry.com

Re: Patch for sql.el (quote table names in sql-list-table)

[Michael R. Mauger]

 

Matthew Carter-3 wrote
> Matthew Carter <

> m@

> > writes:
> 
> Attached is an alternate patch which at least fixes the postgres
> completion list to quote both the schema and table names (I can't think
> of a situation in which it would hurt for this to be quoted).
> 
> 
> diff --git a/lisp/progmodes/sql.el b/lisp/progmodes/sql.el
> index 65e94ba..fd59f46 100644
> --- a/lisp/progmodes/sql.el
> +++ b/lisp/progmodes/sql.el
> @@ -4948,8 +4948,8 @@ Try to set `comint-output-filter-functions' like
> this:
>        ;; Return the list of table names (public schema name can be
> omitted)
>        (mapcar #'(lambda (tbl)
>                    (if (string= (car tbl) "public")
> -                      (cadr tbl)
> -                    (format "%s.%s" (car tbl) (cadr tbl))))
> +                      (format "\"%s\"" (cadr tbl))
> +                    (format "\"%s\".\"%s\"" (car tbl) (cadr tbl))))
>                cl))))
>  

I think this is a better approach.  Ultimately, I'd like to make the
quotation conditional on the name string having an unusual character would
force the quoting.  For now I'd say this is probably the correct step that
will make completion work in your use case.

Thank you.  Feel free to apply the change.
Michael R. Mauger



--
View this message in context: http://emacs.1067599.n5.nabble.com/Patch-for-sql-el-quote-table-names-in-sql-list-table-tp385543p385718.html
Sent from the Emacs - Dev mailing list archive at Nabble.com.