Boolean field nullable problem

[Ruslan Zasukhin]

On 7/21/15, 3:51 PM, "jda" <jda at his.com> wrote:

Hi Jon,

>> ALTER TABLE t1 ADD f1 Boolean NOT NULL;
>> 
> 
> Hi Ivan,
> 
> I can do that, but now I have to update hundreds of older databases to set
> .isnullable = false for all my boolean fields! This could break a lot of code.

Hmm, no.  please read below

> Letting the user set a boolean fields to nullable is fine, but I really think
> it should be NOT NULLABLE by default.

No Jon, because we follow to SQL standard.

Any field on default in CREATE TABLE  is nullable.
To make not nullable - developer MUST say  NOT NULL.


> The law of least astonishment says that
> if I create a boolean field I should be able to do a search on TRUE/FALSE. But
> now in Valentina you can¹t unless you explicitly set the field to NOT NULLABLE
> on creation (and I¹m guessing most people aren¹t going to know that). If you
> just create a new boolean field now and don¹t explicitly set it to true or
> false, searching for ³false² always returns an empty cursor becuase it¹s not
> ³false², it¹s ³null².

And this is right, expected by standard.
 
> BTW, it behaved that way at least through 5.5.2 (which was only 1.5 years old,
> January 2014). So the behavior change is relatively recent. I have to say this
> is one reason I only upgrade Valentina when I ablsolutely have to. So many
> upgrade break existing behavior!

Lets see how to "fix" that.

So far I do not see where is big problems.

You say 

I have a boolean field that I add to the database like this


      ALTER TABLE bibAndDoc ADD forCitation Boolean


So this is temporary field which you add runtime?

Just correct this command as:
      ALTER TABLE bibAndDoc ADD forCitation Boolean NOT NULL.




==========
2) Existed databases - already have all your bool fields as NOT NULL.
No problems here.


==========
3) And you need correct in CODE of your app
    all commands CREATE TABLE   to have NOT NULL for your boolean fields.

So when you will deploy to your users NEW version of your app,
This app will create new db on disk with boolean fields which are NOT NULL.





-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]