Changes regarding standard_conforming_strings in postgres

I found that my database recently started generating extra backslashes
on round-trips to the database.

The reason seems to be a change to standards_conforming_strings variable in postgresl
Beginning in PostgreSQL 9.1, the default is on (prior releases defaulted to off)

https://stackoverflow.com/a/3049139

 * I created a new protocol function (database-escape-backslashes database)
 * I changed the the generic mysql and postgres databases to return T
 * I made the postgresql-socket3 backend check for standards_conforming_string
   which is exposed in the underlying cl-postgres connection
 * It seems likely that postgresql-socket3 could always return NIL, as
   cl-postgres would probably handle this escaping anyway

This replaced some special case code in (database-output-sql (string
database))

diff --git a/ChangeLog b/ChangeLog
index 8b393e6e9c184ac9cb816ace6a75a5a1e718d3f7..d274bf49daf41d7f7dbcf64dadeebb057b60ef0d 100644 (file)
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,3 +1,21 @@
+2018-04-08 Russ Tyndall <russ@acceleration.net>
+        * sql/db-interface, expressions, fdml, generic-postgres
+       db-mysql/mysql-sql.lisp db-postgresql-socket3/sql.lisp
+
+        - I created a new protocol function (database-escape-backslashes
+          database)
+        - I changed the the generic mysql and postgres databases
+         to return T
+        - I made the postgresql-socket3 backend check for
+         standards_conforming_string which is exposed in the underlying
+         cl-postgres connection
+       - It seems likely that postgresql-socket3 could always return NIL, as
+         cl-postgres would probably handle this escaping anyway
+
+         This replaced some special case code in (database-output-sql (string
+         database))
+
+       
 2018-02-04 Russ Tyndall <russ@acceleration.net>
        * sql/generic-postgres.lisp:    
        Wall times default to being timestamptz in postgresql now.

diff --git a/db-mysql/mysql-sql.lisp b/db-mysql/mysql-sql.lisp
index 1f54d44c430e6f261a6f7a7e509c470e035b0f79..9bc0e52bbf712d74cb980df895916d053d4d7f8d 100644 (file)
--- a/db-mysql/mysql-sql.lisp
+++ b/db-mysql/mysql-sql.lisp
@@ -846,3 +846,6 @@
 
 (when (clsql-sys:database-type-library-loaded :mysql)
   (clsql-sys:initialize-database-type :database-type :mysql))
+
+(defmethod clsql-sys::database-escape-backslashes ((database mysql-database))
+  t)

diff --git a/db-postgresql-socket3/sql.lisp b/db-postgresql-socket3/sql.lisp
index 12929c6e85d3cc9ba20c46e9a2960a87cddf38bf..55547b63f44daa1e06db7106a19f8e9310c41445 100644 (file)
--- a/db-postgresql-socket3/sql.lisp
+++ b/db-postgresql-socket3/sql.lisp
@@ -331,3 +331,12 @@
   (clsql-sys:initialize-database-type :database-type :postgresql-socket3))
 
 
+
+;; TODO: there is a good chance this could always return nil as cl-postgres probably
+;; handles this nonsense anyway
+(defmethod clsql-sys::database-escape-backslashes ((database postgresql-socket3-database))
+  (let* ((it (gethash "standard_conforming_strings"
+                      (cl-postgres:connection-parameters
+                       (slot-value database 'connection))))
+         (sit (and it (string it))))
+    (and it (not (string-equal sit "on")))))

diff --git a/notes/postgresql-standards-conforming-strings.txt b/notes/postgresql-standards-conforming-strings.txt
new file mode 100644 (file)
index 0000000..fa3475b
--- /dev/null
+++ b/notes/postgresql-standards-conforming-strings.txt
@@ -0,0 +1,15 @@
+I found that my database recently started generating extra backslashes
+on roundtrips to the database.
+
+The reason seems to be a change to standards_conforming_strings variable in postgresl
+Beginning in PostgreSQL 9.1, the default is on (prior releases defaulted to off)
+
+
+https://stackoverflow.com/a/3049139
+
+ * I created a new protocol function (database-escape-backslashes database)
+ * I changed the the generic mysql and postgres databases to return T
+ * I made the postgresql-socket-3 backend check for standards_conforming_string
+   which is exposed in the underlying cl-postgres connection
+ * It seems likely that postgresql-socket-3 could always return NIL, as
+   cl-postgres would probably handle this escaping anyway

diff --git a/sql/db-interface.lisp b/sql/db-interface.lisp
index 3454a84ee8d992372e07ba3abf7a7c3d07258a1c..b46280a7d65c657f6b1eee7641ddb866a9aad949 100644 (file)
--- a/sql/db-interface.lisp
+++ b/sql/db-interface.lisp
@@ -498,3 +498,8 @@ for foreign libraries \(in addition to the default places).")
   "Adds the pathspec PATH \(which should denote a directory) to
 the list *FOREIGN-LIBRARY-SEARCH-PATHS*."
   (pushnew path *foreign-library-search-paths* :test #'equal))
+
+(defgeneric database-escape-backslashes (database)
+  (:documentation "Should backslases in a string be escaped? ")
+  (:method ( database )
+    nil))

diff --git a/sql/expressions.lisp b/sql/expressions.lisp
index 4f0baf1230b0ab32353dc69026645306adae11e0..1b3b1a486ca2f42ab44f586b4b358d5d7275c9e0 100644 (file)
--- a/sql/expressions.lisp
+++ b/sql/expressions.lisp
@@ -420,13 +420,13 @@
 
 (defmethod output-sql ((expr sql-assignment-exp) database)
   (with-slots (operator sub-expressions)
-    expr
-    (do ((sub sub-expressions (cdr sub)))
-        ((null (cdr sub)) (output-sql (car sub) database))
-      (output-sql (car sub) database)
+      expr
+    (output-sql (car sub-expressions) database)
+    (dolist (sub (cdr sub-expressions))
       (write-char #\Space *sql-stream*)
       (%write-operator operator database)
-      (write-char #\Space *sql-stream*)))
+      (write-char #\Space *sql-stream*)
+      (output-sql sub database)))
   t)
 
 (defclass sql-value-exp (%sql-expression)
@@ -1009,9 +1009,7 @@ uninclusive, and the args from that keyword to the end."
                         (setf (aref buf j) #\'))
                        ((and (char= char #\\)
                              ;; MTP: only escape backslash with pgsql/mysql
-                             (member (database-underlying-type database)
-                                     '(:postgresql :mysql)
-                                     :test #'eq))
+                             (database-escape-backslashes database))
                         (setf (aref buf j) #\\)
                         (incf j)
                         (setf (aref buf j) #\\))

diff --git a/sql/fdml.lisp b/sql/fdml.lisp
index 5e248ced0547c0eb66895d820493023b4f032f7b..0a744cb23a83a3b755afc10ebf750a66961870b2 100644 (file)
--- a/sql/fdml.lisp
+++ b/sql/fdml.lisp
@@ -54,8 +54,9 @@
   (values))
 
 (defmethod execute-command ((expr %sql-expression)
-                            &key (database *default-database*))
-  (execute-command (sql-output expr database) :database database)
+                            &key (database *default-database*)
+                            &aux (str-sql (sql-output expr database)))
+  (execute-command str-sql :database database)
   (values))
 
 (defmethod query ((query-expression string) &key (database *default-database*)

diff --git a/sql/generic-postgresql.lisp b/sql/generic-postgresql.lisp
index 2464746f11c9b4a2de764487a88cc68c5089858f..9971e4ac066824b836a140b79ef3b2801f815780 100644 (file)
--- a/sql/generic-postgresql.lisp
+++ b/sql/generic-postgresql.lisp
@@ -429,3 +429,6 @@
 
 (defmethod db-type-has-auto-increment? ((db-type (eql :postgresql)))
   t)
+
+(defmethod clsql-sys::database-escape-backslashes ((database generic-postgresql-database))
+  t)