When I work with SQL databases, I find myself adding "not null
default something" to the column definitions. I get the point
that null means "not known", and this is good when we want to
count and compute mean values etc, but I don't normally do
that.
Specially with a boolean value. Both null and false translates
to nil in Common Lisp. This is three values in SQL but only
two in Common Lisp (At least in pg.lisp where I am working now).
Also an integer column, I would like to be guaranteed that it
is some integer including 0, and not nil.
I rather like the abstraction of NIL being false and every-
thing else is true.
What do you think?
On 27 Aug 2006 17:32:56 +0200, Johan Ur Riise <·····@riise-data.no>:
>Specially with a boolean value. Both null and false translates
>to nil in Common Lisp. This is three values in SQL but only
>two in Common Lisp (At least in pg.lisp where I am working now).
Well, if a library transforms the set { yes no unknown } into the set
{ yes no-or-unknown }, it's broken.
Typically, when working with such tribools (triple booleans), there
are two structures:
1/
(if there-is-a-value
(if the-value-is-true do-yep do-nope))
2/
(cond (no-value do-no-value)
(value-is-true do-yep)
(value-is-false do-nope))
So, IMHO the library should provide:
1/ A boolean (as a function or as a variable) "There is a value"
+ A boolean "The value is true"
2/ A "sql-if" macro allowing the user to type
(sql-if (the-tribool) expression-if-true
expression-if-false
expression-if-undefined)
>I rather like the abstraction of NIL being false and
>everything else is true.
Well, since it's specific to Lisp, don't expect to get that with SQL.
Also, () looks like "no value", and NIL looks like "false".
Unfortunately, they're the same...
In CLSQL, a boolean is either t or nil--and thus NULL is conflated with
false. However, CLSQL is smart; you can have it return other types
instead.
SQL
===
create table test (index integer, val bool);
insert into test values (0, false);
insert into test values (1, true);
insert into test values (2, NULL);
select * from test;
index | val
-------+-----
0 | f
1 | t
3 |
(3 rows)
Lisp
====
> (select [*] :from 'test)
((0 "f") (1 "t") (3 nil))
("index" "val")
> (def-view-class test ()
((index
:type integer
:accessor index)
(val
:type boolean
:accessor val)))
> (mapcar #'(lambda (x)
(format nil "index: ~a; val: ~a"
(index x)
(val x)))
(select 'test :flatp t :refresh t))
("index: 0; val: nil" "index: 1; val: t" "index: 3; val: nil")
;; BUT
> (def-view-class test ()
((index
:type integer
:accessor index)
(val
:type character
:accessor val)))
> (mapcar #'(lambda (x)
(format nil "index: ~a; val: ~a"
(index x)
(val x)))
(select 'test :flatp t :refresh t))
("index: 0; val: f" "index: 1; val: t" "index: 3; val: nil")
You could also use strings instead of characters--depending on what you
want to do, one or the other could be more efficient/flexible.
--
Robert Uhl <http://public.xdi.org/=ruhl>
I may be tethering on the brink of the Godwin cliff on a unicycle with
a warped wheel here, but... --Edmund Roche-Kelly
Robert Uhl <·········@NOSPAMgmail.com> writes:
> In CLSQL, a boolean is either t or nil--and thus NULL is conflated with
> false. However, CLSQL is smart; you can have it return other types
> instead.
>
> SQL
> ===
>
> create table test (index integer, val bool);
> insert into test values (0, false);
> insert into test values (1, true);
> insert into test values (2, NULL);
> select * from test;
> index | val
> -------+-----
> 0 | f
> 1 | t
> 3 |
> (3 rows)
[...]
> ;; BUT
> > (def-view-class test ()
> ((index
> :type integer
> :accessor index)
> (val
> :type character
> :accessor val)))
> > (mapcar #'(lambda (x)
> (format nil "index: ~a; val: ~a"
> (index x)
> (val x)))
> (select 'test :flatp t :refresh t))
> ("index: 0; val: f" "index: 1; val: t" "index: 3; val: nil")
>
> You could also use strings instead of characters--depending on what you
> want to do, one or the other could be more efficient/flexible.
That is quite interesting didn't know that. Still, I am not
comfortable with three values. Why not use a character field with
T and F, not null and disallowing other values? Or boolean with
not null?
Johan Ur Riise <·····@riise-data.no> writes:
>
> That is quite interesting didn't know that. Still, I am not
> comfortable with three values. Why not use a character field with T
> and F, not null and disallowing other values? Or boolean with not
> null?
Well, I was just replying with a way to distinguish true, false and
null; obviously if you've a not-null constraint then you could just use
a boolean.
Nulls are not bad in a database system; it's not uncommon to need to be
able to say 'I don't know the value of this.' There's some debate over
whether there should be a general null like SQL provides, or if each
column or type should provide its own null value.
--
Robert Uhl <http://public.xdi.org/=ruhl>
Turns out that cute girl's A|X t-shirt didn't mean AIX.
Who would've thought?! --seen on /.
Robert Uhl <·········@NOSPAMgmail.com> writes:
> Johan Ur Riise <·····@riise-data.no> writes:
>
>>
>> That is quite interesting didn't know that. Still, I am not
>> comfortable with three values. Why not use a character field with T
>> and F, not null and disallowing other values? Or boolean with not
>> null?
>
> Well, I was just replying with a way to distinguish true, false and
> null; obviously if you've a not-null constraint then you could just use
> a boolean.
Well then you don't have booleans. You've got tooleans :-)
true <=> T
false <=> NIL
null <=> MAYBE
------------------------------------------------------------------------
;;;;**************************************************************************
;;;;FILE: toolean.lisp
;;;;LANGUAGE: Common-Lisp
;;;;SYSTEM: Common-Lisp
;;;;USER-INTERFACE: NONE
;;;;DESCRIPTION
;;;;
;;;; Ternary logic.
;;;;
;;;; not
;;;; t nil
;;;; maybe maybe
;;;; nil t
;;;;
;;;; and t maybe nil
;;;; t t maybe nil
;;;; maybe maybe maybe nil
;;;; nil nil nil nil
;;;;
;;;; or t maybe nil
;;;; t t maybe t
;;;; maybe maybe maybe maybe
;;;; nil t maybe nil
;;;;
;;;;AUTHORS
;;;; <PJB> Pascal Bourguignon <···@informatimago.com>
;;;;MODIFICATIONS
;;;; 2004-02-12 <PJB> Created
;;;;BUGS
;;;;LEGAL
;;;; GPL
;;;;
;;;; Copyright Pascal Bourguignon 2004 - 2006
;;;;
;;;; This program 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
;;;; 2 of the License, or (at your option) any later version.
;;;;
;;;; This program 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 this program; if not, write to the Free
;;;; Software Foundation, Inc., 59 Temple Place, Suite 330,
;;;; Boston, MA 02111-1307 USA
;;;;**************************************************************************
;; TODO: defpackage
(defconstant maybe 'maybe)
(deftype toolean () '(member nil maybe t))
(defun tnot (val)
(case val
((nil) t)
((maybe) maybe)
(otherwise nil)));;tnot
(defun ftand (&rest args)
(cond
((member nil args) nil)
((member maybe args) maybe)
(t t)));;ftand
(defun ftor (&rest args)
(cond
((member maybe args) maybe)
((some (lambda (x) (not x)) args) t)
(t nil)));;ftor
(defmacro tand (&rest args)
(cond
((null args) t)
((member nil args) nil)
(t
(let* ((found-maybe (gensym "found-maybe"))
(block-label (gensym "tand"))
(tests `((return-from ,block-label (if ,found-maybe maybe t)))))
(do ((args (reverse args) (cdr args)))
((null args) `(block ,block-label
(let ((,found-maybe nil)) ,@tests)))
(unless (eq t (car args))
(push `(let ((term ,(car args)))
(if (null term)
(return-from ,block-label nil)
(setf ,found-maybe (or ,found-maybe (eq maybe term)))))
tests)))))));;tand
(defmacro tor (&rest args)
(cond
((null args) nil)
((member maybe args) maybe)
(t
(let* ((found-t (gensym "found-t"))
(block-label (gensym "tor"))
(tests `((return-from ,block-label ,found-t))))
(do ((args (reverse args) (cdr args)))
((null args) `(block ,block-label
(let ((,found-t nil)) ,@tests)))
(unless (eq nil (car args))
(push `(let ((term ,(car args)))
(if (eq maybe term)
(return-from ,block-label maybe)
(setf ,found-t (or ,found-t term))))
tests)))))));;tor
(defmacro tif (tcondition &optional if-true if-maybe if-false)
(let ((condition (gensym "tif-condition")))
`(let ((,condition ,tcondition))
(cond
((eq maybe ,condition) ,if-maybe)
((null ,condition) ,if-false)
(t ,if-true)))));;tif
(defmacro twhile (tcondition &body body)
(let ((condition (gensym "twhile-condition")))
`(do ()
((let ((,condition ,tcondition))
(cond ((eq maybe ,condition) (< (random 100) 50))
((null ,condition) t)
(t nil))))
,@body)));;twhile
(twhile maybe (format t "lucky guy!~%"))
(defparameter p (tand t maybe))
(defparameter q (tor maybe nil))
(defparameter r (tnot maybe))
(tif (tand p (tor q r) (tnot r))
(format t "sure!~%")
(format t "maybe...~%")
(format t "definitely not!~%"))
------------------------------------------------------------------------
> Nulls are not bad in a database system; it's not uncommon to need to be
> able to say 'I don't know the value of this.' There's some debate over
> whether there should be a general null like SQL provides, or if each
> column or type should provide its own null value.
>
> --
> Robert Uhl <http://public.xdi.org/=ruhl>
> Turns out that cute girl's A|X t-shirt didn't mean AIX.
> Who would've thought?! --seen on /.
--
__Pascal Bourguignon__ http://www.informatimago.com/
"Our users will know fear and cower before our software! Ship it!
Ship it and let them flee like the dogs they are!"
Pascal Bourguignon <···@informatimago.com> writes:
> Robert Uhl <·········@NOSPAMgmail.com> writes:
>
>> Johan Ur Riise <·····@riise-data.no> writes:
>>
>>>
>>> That is quite interesting didn't know that. Still, I am not
>>> comfortable with three values. Why not use a character field with T
>>> and F, not null and disallowing other values? Or boolean with not
>>> null?
>>
>> Well, I was just replying with a way to distinguish true, false and
>> null; obviously if you've a not-null constraint then you could just use
>> a boolean.
>
> Well then you don't have booleans. You've got tooleans :-)
>
> true <=> T
> false <=> NIL
> null <=> MAYBE
Stephen Compall on irc://irc.freenode.org/#lisp suggested I made a
correction in the OR truth table. Here is a better version:
;;;;**************************************************************************
;;;;FILE: toolean.lisp
;;;;LANGUAGE: Common-Lisp
;;;;SYSTEM: Common-Lisp
;;;;USER-INTERFACE: NONE
;;;;DESCRIPTION
;;;;
;;;; Ternary logic.
;;;;
;;;;AUTHORS
;;;; <PJB> Pascal Bourguignon <···@informatimago.com>
;;;;MODIFICATIONS
;;;; 2006-08-30 <PJB> Changed the truth table to make it more "logical".
;;;; 2004-02-12 <PJB> Created
;;;;BUGS
;;;;LEGAL
;;;; GPL
;;;;
;;;; Copyright Pascal Bourguignon 2004 - 2006
;;;;
;;;; This program 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
;;;; 2 of the License, or (at your option) any later version.
;;;;
;;;; This program 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 this program; if not, write to the Free
;;;; Software Foundation, Inc., 59 Temple Place, Suite 330,
;;;; Boston, MA 02111-1307 USA
;;;;**************************************************************************
(in-package :common-lisp-user)
(defconstant maybe 'maybe)
(deftype toolean () '(member nil maybe t))
;; not
;; t nil
;; maybe maybe
;; nil t
;;
;; and t maybe nil
;; t t maybe nil
;; maybe maybe maybe nil
;; nil nil nil nil
;;
;; or t maybe nil
;; t t t t
;; maybe t maybe maybe
;; nil t maybe nil
;;
(defun tnot (val)
(case val
((nil) t)
((maybe) maybe)
(otherwise nil)))
(defun ftand (&rest args)
(cond
((member nil args) nil)
((member maybe args) maybe)
(t t)))
(defun ftor (&rest args)
(cond
((some (lambda (x) (not (or (null x) (maybep x)))) args) t)
((member maybe args) maybe)
(t nil)))
(defun maybep (x) (eql maybe x))
(defmacro tand (&rest expressions)
(if (null expressions)
'nil
(let ((vreturn (gensym "TAND"))
(vresult (gensym "RESULT"))
(vvalue (gensym "VALUE")))
`(block ,vreturn
(let ((,vresult nil))
,@(mapcar (lambda (expression)
`(let ((,vvalue ,expression))
(cond
((maybep ,vvalue) (setf ,vresult maybe))
((null ,vvalue) (return-from ,vreturn nil))
(t (unless (maybep ,vresult)
(setf ,vresult ,vvalue))))))
expressions)
,vresult)))))
(defmacro tor (&rest expressions)
(if (null expressions)
't
(let ((vreturn (gensym "TOR"))
(vresult (gensym "RESULT"))
(vvalue (gensym "VALUE")))
`(block ,vreturn
(let ((,vresult nil))
,@(mapcar (lambda (expression)
`(let ((,vvalue ,expression))
(cond
((maybep ,vvalue) (setf ,vresult maybe))
(,vvalue (return-from ,vreturn ,vvalue)))))
expressions)
,vresult)))))
(defmacro tif (tcondition &optional if-true if-maybe if-false)
(let ((condition (gensym "tif-condition")))
`(let ((,condition ,tcondition))
(cond
((eq maybe ,condition) ,if-maybe)
((null ,condition) ,if-false)
(t ,if-true)))))
(defmacro twhile (tcondition &body body)
(let ((condition (gensym "twhile-condition")))
`(do ()
((let ((,condition ,tcondition))
(cond ((eq maybe ,condition) (< (random 100) 50))
((null ,condition) t)
(t nil))))
,@body)))
(defun test ()
(loop for (mop fop) in '((tor ftor) (tand ftand)) do
(terpri)
(loop for a in '(42 t maybe nil) do
(loop for b in '(42 t maybe nil) do
(format t ";; ~5A ~5A ~5A = ~5A =t= ~5A~%"
a mop b (eval `(,mop ,a ,b)) (funcall fop a b))))))
(twhile maybe (format t "lucky guy!~%"))
(defparameter p (tand t maybe))
(defparameter q (tor maybe nil))
(defparameter r (tnot maybe))
(tif (tand p (tor q r) (tnot r))
(format t "sure!~%")
(format t "maybe...~%")
(format t "definitely not!~%"))
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; 42 TOR 42 = 42 =t= T
;; 42 TOR T = 42 =t= T
;; 42 TOR MAYBE = 42 =t= T
;; 42 TOR NIL = 42 =t= T
;; T TOR 42 = T =t= T
;; T TOR T = T =t= T
;; T TOR MAYBE = T =t= T
;; T TOR NIL = T =t= T
;; MAYBE TOR 42 = 42 =t= T
;; MAYBE TOR T = T =t= T
;; MAYBE TOR MAYBE = MAYBE =t= MAYBE
;; MAYBE TOR NIL = MAYBE =t= MAYBE
;; NIL TOR 42 = 42 =t= T
;; NIL TOR T = T =t= T
;; NIL TOR MAYBE = MAYBE =t= MAYBE
;; NIL TOR NIL = NIL =t= NIL
;; 42 TAND 42 = 42 =t= T
;; 42 TAND T = T =t= T
;; 42 TAND MAYBE = MAYBE =t= MAYBE
;; 42 TAND NIL = NIL =t= NIL
;; T TAND 42 = 42 =t= T
;; T TAND T = T =t= T
;; T TAND MAYBE = MAYBE =t= MAYBE
;; T TAND NIL = NIL =t= NIL
;; MAYBE TAND 42 = MAYBE =t= MAYBE
;; MAYBE TAND T = MAYBE =t= MAYBE
;; MAYBE TAND MAYBE = MAYBE =t= MAYBE
;; MAYBE TAND NIL = NIL =t= NIL
;; NIL TAND 42 = NIL =t= NIL
;; NIL TAND T = NIL =t= NIL
;; NIL TAND MAYBE = NIL =t= NIL
;; NIL TAND NIL = NIL =t= NIL
--
__Pascal Bourguignon__ http://www.informatimago.com/
WARNING: This product attracts every other piece of matter in the
universe, including the products of other manufacturers, with a
force proportional to the product of the masses and inversely
proportional to the distance between them.
Robert Uhl <·········@NOSPAMgmail.com> writes:
> Johan Ur Riise <·····@riise-data.no> writes:
>
> >
> > That is quite interesting didn't know that. Still, I am not
> > comfortable with three values. Why not use a character field with T
> > and F, not null and disallowing other values? Or boolean with not
> > null?
>
> Well, I was just replying with a way to distinguish true, false and
> null; obviously if you've a not-null constraint then you could just use
> a boolean.
>
> Nulls are not bad in a database system; it's not uncommon to need to be
> able to say 'I don't know the value of this.' There's some debate over
> whether there should be a general null like SQL provides, or if each
> column or type should provide its own null value.
Nulls *are* bad in a *relational* database system. Instead of sets,
you have swiss cheese. If you have an attribute which does not always
have a 1:1 correspondence with the rest of the attributes in a
relation, put it in its own relation. SQL's null is just an ugly,
aweful hack to allow users to get by with non-normal databases.
(In an object model, you want something like an unbound slot, but then we're
talking about object systems, not the relational model.)
On 30 Aug 2006 02:15:14 -0700, ···@conquest.OCF.Berkeley.EDU (Thomas
F. Burdick) wrote:
>Nulls *are* bad in a *relational* database system. Instead of sets,
>you have swiss cheese. If you have an attribute which does not always
>have a 1:1 correspondence with the rest of the attributes in a
>relation, put it in its own relation. SQL's null is just an ugly,
>aweful hack to allow users to get by with non-normal databases.
Non normalized databases are a necessary evil.
Decomposition to BCNF is impractical with a realistic data set. A
database in BCNF makes non-trivial queries ridiculously complex and
processing time scales exponentially with the size of the intermediate
joins.
You are correct that nulls are an efficiency hack ... but so is
selection (and for non-normalized data, projection as well). The
alternative to using them is to waste a lot of time waiting for
answers.
>(In an object model, you want something like an unbound slot, but then we're
> talking about object systems, not the relational model.)
SQL doesn't implement the relational model - it was simply inspired by
it. SQL implements a bastard hybrid of relational set algebra, tuple
calculus, and procedural logic. If you want a pure relational set
manipulation language look somewhere else.
George
--
for email reply remove "/" from address
On Wed, 06 Sep 2006 00:21:02 +0200, George Neuner <·········@comcast.net>
wrote:
> On 30 Aug 2006 02:15:14 -0700, ···@conquest.OCF.Berkeley.EDU (Thomas
> F. Burdick) wrote:
>
>> Nulls *are* bad in a *relational* database system. Instead of sets,
>> you have swiss cheese. If you have an attribute which does not always
>> have a 1:1 correspondence with the rest of the attributes in a
>> relation, put it in its own relation. SQL's null is just an ugly,
>> aweful hack to allow users to get by with non-normal databases.
>
> Non normalized databases are a necessary evil.
>
> Decomposition to BCNF is impractical with a realistic data set. A
> database in BCNF makes non-trivial queries ridiculously complex and
> processing time scales exponentially with the size of the intermediate
> joins.
>
> You are correct that nulls are an efficiency hack ... but so is
> selection (and for non-normalized data, projection as well). The
> alternative to using them is to waste a lot of time waiting for
> answers.
>
Sort of true. Never the less the original design should be normalized.
Then the tables can be collapsed if efficiency issues arise.
This allows you to see the null's and add contraints to avoid
update anomaleties.
>
>> (In an object model, you want something like an unbound slot, but then
>> we're
>> talking about object systems, not the relational model.)
>
> SQL doesn't implement the relational model - it was simply inspired by
> it. SQL implements a bastard hybrid of relational set algebra, tuple
> calculus, and procedural logic. If you want a pure relational set
> manipulation language look somewhere else.
>
True. But it is what most people think of as relational databases.
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
George Neuner <·········@comcast.net> writes:
> On 30 Aug 2006 02:15:14 -0700, ···@conquest.OCF.Berkeley.EDU (Thomas
> F. Burdick) wrote:
>
> >(In an object model, you want something like an unbound slot, but then we're
> > talking about object systems, not the relational model.)
>
> SQL doesn't implement the relational model - it was simply inspired by
> it. SQL implements a bastard hybrid of relational set algebra, tuple
> calculus, and procedural logic. If you want a pure relational set
> manipulation language look somewhere else.
>
Consistent with the sales pitch I got on it where its "english" nature
was supposed to allow the PHB's to roll their own queries against the
organization's enterprise database. Nice simple ad-hoc syntax to let
you arrange queries dynamically, sounds great until you run into Pro*SQL
or ODBC...
Greg
Johan Ur Riise <·····@riise-data.no> writes:
> When I work with SQL databases, I find myself adding "not null
> default something" to the column definitions. I get the point
> that null means "not known", and this is good when we want to
> count and compute mean values etc, but I don't normally do
> that.
>
> Specially with a boolean value. Both null and false translates
> to nil in Common Lisp. This is three values in SQL but only
> two in Common Lisp (At least in pg.lisp where I am working now).
[ Of all the places to allow NULL, why would you do so for a boolean
column?!?! ]
> Also an integer column, I would like to be guaranteed that it
> is some integer including 0, and not nil.
>
> I rather like the abstraction of NIL being false and every-
> thing else is true.
>
> What do you think?
I think SQL's NULL is an abomination, and you should always design
your databases to disallow NULLs. Fortunately, NULL is never actually
needed, it's just a foolish way of modeling a {0,1}:1 relationship,
which can just be modeled with ... uh, relations, like everything else
in a relational database :-)
If you insist on using NULL, I recommend writing a Lisp->Lisp compiler
where you propagate the silly three-valued boolean logic of SQL. So:
(if null t nil) => nil
(if (not null) t nil) => nil
(if (null? null) t nil) => t
···@conquest.OCF.Berkeley.EDU (Thomas F. Burdick) writes:
> Johan Ur Riise <·····@riise-data.no> writes:
>
> > When I work with SQL databases, I find myself adding "not null
> > default something" to the column definitions. I get the point
> > that null means "not known", and this is good when we want to
> > count and compute mean values etc, but I don't normally do
> > that.
> >
> > Specially with a boolean value. Both null and false translates
> > to nil in Common Lisp. This is three values in SQL but only
> > two in Common Lisp (At least in pg.lisp where I am working now).
>
> [ Of all the places to allow NULL, why would you do so for a boolean
> column?!?! ]
>
> > Also an integer column, I would like to be guaranteed that it
> > is some integer including 0, and not nil.
> >
> > I rather like the abstraction of NIL being false and every-
> > thing else is true.
> >
> > What do you think?
>
> I think SQL's NULL is an abomination, and you should always design
> your databases to disallow NULLs. Fortunately, NULL is never actually
> needed, it's just a foolish way of modeling a {0,1}:1 relationship,
> which can just be modeled with ... uh, relations, like everything else
> in a relational database :-)
So you agree then. Thats two for and two against nulls.
>
> If you insist on using NULL, I recommend writing a Lisp->Lisp compiler
> where you propagate the silly three-valued boolean logic of SQL. So:
>
> (if null t nil) => nil
> (if (not null) t nil) => nil
> (if (null? null) t nil) => t
(defmacro mostly-when (what then)
`(when (and ,what (> (random 5) 0)) ,then))