incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Joe Thurbon <joe.thur...@intersect.org.au>
Subject Re: Inconsistent SQL generation ?
Date Tue, 17 Aug 2010 11:24:37 GMT
On 17/08/2010, at 9:01 PM, Rainer Döbele wrote:

> Hi Francis,
> 
> Thanks a lot for the wiki entry.
> And yes the use of the DBDatabase.EMPTY_STRING can be used everywhere and would solve
exxos' problem.
> 
> I cannot say how difficult it would be to make the empty-string behaviour optional but
I don't think it would simple.
> Still I am looking for someone to give me a reason why empty strings would ever make
sense (from a logical point of view).
> 

Hi Rainer,

From a logical point of view, I would consider the empty string as directly analogous to 0
for addition and 1 for multiplication. It's just another value from the domain, and has interesting
properties under some operations (like concatenation).

For the record, I was amazed when the "" to null translation happened under the hood in empire-db.

Cheers,
Joe



> Rainer
> 
> 
> Francis De Brabandere wrote:
>> re: Re: Inconsistent SQL generation ?
>> 
>> Added a section on the FAQ in the wiki about null / '' handling
>> 
>> https://cwiki.apache.org/confluence/display/empiredb/Frequently+asked+qu
>> estions
>> 
>> On Tue, Aug 17, 2010 at 10:50 AM, Francis De Brabandere
>> <francisdb@gmail.com> wrote:
>>> So the actual fix for exxos would be:
>>> 
>>> DBCommand cmd = db.createCommand();
>>> cmd.set(db.tab.col.to(DBDatabase.EMPTY_STRING));
>>> 
>>> Could we somehow make this '' -> null behavior optional, would that
>>> need a lot of refactoring? If a user wants empty strings he now needs
>>> to check all his values and replace them by DBDatabase.EMPTY_STRING
>>> where needed...
>>> 
>>> Cheers,
>>> Francis
>>> 
>>> On Tue, Aug 17, 2010 at 9:18 AM, Rainer Döbele <doebele@esteam.de>
>> wrote:
>>>> Hi everyone,
>>>> 
>>>> 
>>>> 
>>>> empire-db internally treats all empty strings as null and this
>> behavior is
>>>> by design.
>>>> 
>>>> Some databases accept empty strings as a valid value for a text
>> column,
>>>> others don't.
>>>> 
>>>> However allowing empty strings is a major reason for many database
>> problems
>>>> and changing the empire-db code here would have a major impact on
>> existing
>>>> projects.
>>>> 
>>>> We have thought about this a long time ago and we came to the
>> conclusion
>>>> that empty strings are evil and should be avoided completely.
>>>> 
>>>> In fact NULL has a logical meaning of a value not being supplied for
>> a field
>>>> but what different logical meaning would an empty string have
>> instead?
>>>> 
>>>> Also declaring a column as "not null" would be useless since this
>> rule could
>>>> easily be bypassed by an empty string, even though there is no real
>> value
>>>> given for the field.
>>>> 
>>>> (I guess we have had this discussion before a while ago and it keeps
>> coming
>>>> up).
>>>> 
>>>> 
>>>> 
>>>> So believe me that with avoiding empty strings altogether, your life
>> will be
>>>> much easier and your code will be a lot less error prone.
>>>> 
>>>> In some rare cases however you might need to explicitly use an empty
>> string
>>>> rather than null.
>>>> 
>>>> One of it is with existing databases when you want to search for
>> field that
>>>> contain an empty string.
>>>> 
>>>> In this case (and others) you must explicitly say that you want to
>> use an
>>>> empty string.
>>>> 
>>>> This is possible through the DBDatabase.EMPTY_STRING constant.
>>>> 
>>>> The following example shows how to replace all empty strings of a
>> column by
>>>> null:
>>>> 
>>>> 
>>>> 
>>>> DBCommand cmd = db.createCommand();
>>>> 
>>>> cmd.set  (db.EMPLOYEES.LASTNAME.to( null ));
>>>> 
>>>> cmd.where(db.EMPLOYEES.LASTNAME.is( DBDatabase.EMPTY_STRING ));
>>>> 
>>>> db.executeSQL(cmd.getUpdate(), conn);
>>>> 
>>>> 
>>>> 
>>>> This will generate:
>>>> 
>>>> UPDATE EMPLOYEES
>>>> 
>>>> SET LASTNAME=null
>>>> 
>>>> WHERE LASTNAME=''
>>>> 
>>>> 
>>>> 
>>>> Regards,
>>>> 
>>>> Rainer
>>>> 
>>>> 
>>>> 
>>>> 
>>>> 
>>>> from: exxos [mailto:hatufr@gmail.com]
>>>> to: empire-db-user@incubator.apache.org
>>>> re: Unconsistent SQL generation ?
>>>> 
>>>> 
>>>> 
>>>> Hello,
>>>> 
>>>> I noticed something probably not correct:
>>>> 
>>>> When you execute the following:
>>>> 
>>>> DBCommand cmd = db.createCommand();
>>>> cmd.set(db.tab.col.to(""));
>>>> 
>>>> You get the SQL below
>>>> 
>>>> INSERT INTO tab (col) VALUES (null);
>>>> 
>>>> whereas it is expected
>>>> 
>>>> INSERT INTO tab (col) VALUES ("");
>>>> 
>>>> In the table defintion there is:
>>>> col = addColumn("col", DataType.TEXT, 80, true);
>>>> 
>>>> Could you please advise why  you get "null" instead of empty "" ?
>>>> 
>>>> Regards,
>>>> exxos.
>>>> 
>>>> 
>>> 
>>> 
>>> 
>>> --
>>> http://www.somatik.be
>>> Microsoft gives you windows, Linux gives you the whole house.
>>> 
>> 
>> 
>> 
>> --
>> http://www.somatik.be
>> Microsoft gives you windows, Linux gives you the whole house.


Mime
View raw message