Hi there,
I'm dealing with a problem and I'd like some enlightenment.
I've a table with text columns that can't be NULL. In my Lisp program, sometimes I'm assigning `nil' as value for this column, either via a direct query execution or via the DAO. What I would expect in this case, is to PostgreSQL to raise an error.
But this does not happen.
What happens is that (cl-postgres:to-sql-string nil) returns "false", and that the query is built to:
INSERT INTO mytable (mytextfield) VALUES (false);
PostgreSQL implicit type conversion kicks in and insert the string 'false' into the field, which does not trigger an error since this it not NULL.
The obvious fix here is to patch all my code to convert nil to :null, but this is a pain. I don't want to do the type checking I'm doing with PostgreSQL one more time in my application.
What I'd like is for cl-postgres to consider nil as being NULL. The boolean false value could be mapped as :false, which would also make sure no code is inserting false just because a value is actually nil.
The change is pretty trivial and I'm running with it right now, which allowed me to debug a lot of implicit nil conversion to 'false' in text fields.
I recognize this is something that would break compatibility, so that might be enabled via a defparameter or something.
Does that sounds like a good idea, or do I miss an obvious problem?
On Fri, Jul 26 2013, Julien Danjou wrote:
Does that sounds like a good idea, or do I miss an obvious problem?
For the record and people interested in that hack, I really find it quite handy in the end. And it's easy enough to change cl-postgres/s-sql behaviour:
(defmethod s-sql:sql-escape ((arg symbol)) "Overrides the s-sql provided function so it handles correctly the :false keyword and get it converted to false." (if (or (typep arg 'boolean) (eq arg :null) (eq arg :false)) (call-next-method) (s-sql:to-sql-name arg)))
(defmethod cl-postgres:to-sql-string ((arg (eql nil))) "Overrides the cl-postgres provided function so it handles nil as NULL instead of false." "NULL")
(defmethod cl-postgres:to-sql-string ((arg (eql :false))) "Overrides the cl-postgres provided function so it handles :false." "false")
The distinction between nil and :null in Postmodern is a conscious decision. It makes the CL type 'boolean neatly correspond to Postgres booleans, and introduces a new type to indicate nullability. I've also had to write code to convert nils to :nulls in deep data structures a few occasions, but I think that this is a less problematic situation than having to convert boolean nils to :false.
Add to that backwards incompatibility, and I just don't think this is a worthwhile change to make.
Best, Marijn
On Fri, Jul 26, 2013 at 5:06 PM, Julien Danjou julien@danjou.info wrote:
Hi there,
I'm dealing with a problem and I'd like some enlightenment.
I've a table with text columns that can't be NULL. In my Lisp program, sometimes I'm assigning `nil' as value for this column, either via a direct query execution or via the DAO. What I would expect in this case, is to PostgreSQL to raise an error.
But this does not happen.
What happens is that (cl-postgres:to-sql-string nil) returns "false", and that the query is built to:
INSERT INTO mytable (mytextfield) VALUES (false);
PostgreSQL implicit type conversion kicks in and insert the string 'false' into the field, which does not trigger an error since this it not NULL.
The obvious fix here is to patch all my code to convert nil to :null, but this is a pain. I don't want to do the type checking I'm doing with PostgreSQL one more time in my application.
What I'd like is for cl-postgres to consider nil as being NULL. The boolean false value could be mapped as :false, which would also make sure no code is inserting false just because a value is actually nil.
The change is pretty trivial and I'm running with it right now, which allowed me to debug a lot of implicit nil conversion to 'false' in text fields.
I recognize this is something that would break compatibility, so that might be enabled via a defparameter or something.
Does that sounds like a good idea, or do I miss an obvious problem?
-- Julien Danjou ;; Free Software hacker ; freelance consultant ;; http://julien.danjou.info
postmodern-devel@common-lisp.net
-
Julien Danjou -
Marijn Haverbeke