From 442966fe451dfe078d1fad7e13564377e1f37809 Mon Sep 17 00:00:00 2001 From: Russ Tyndall Date: Sun, 8 Apr 2018 11:44:51 -0400 Subject: [PATCH] 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)) --- ChangeLog | 18 ++++++++++++++++++ db-mysql/mysql-sql.lisp | 3 +++ db-postgresql-socket3/sql.lisp | 9 +++++++++ ...postgresql-standards-conforming-strings.txt | 15 +++++++++++++++ sql/db-interface.lisp | 5 +++++ sql/expressions.lisp | 14 ++++++-------- sql/fdml.lisp | 5 +++-- sql/generic-postgresql.lisp | 3 +++ 8 files changed, 62 insertions(+), 10 deletions(-) create mode 100644 notes/postgresql-standards-conforming-strings.txt diff --git a/ChangeLog b/ChangeLog index 8b393e6..d274bf4 100644 --- a/ChangeLog +++ b/ChangeLog @@ -1,3 +1,21 @@ +2018-04-08 Russ Tyndall + * 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 * 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 1f54d44..9bc0e52 100644 --- 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 12929c6..55547b6 100644 --- 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 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 3454a84..b46280a 100644 --- 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 4f0baf1..1b3b1a4 100644 --- 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 5e248ce..0a744cb 100644 --- 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 2464746..9971e4a 100644 --- 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) -- 2.34.1