SQLite and Boolean value of -1

[LacaK]

Finally, for all those having multiple values (1 and -1) for boolean already in their database, checking for ABS(boolean_value) <> 0 is the safest course.mention other programs querying the database and expecting 1 for TRUE)

Why "ABS(boolean_value) <> 0" ?
isn't enough "boolean_value <> 0"

[rvk]

Why "ABS(boolean_value) <> 0" ?
isn't enough "boolean_value <> 0"

Yeah, that was suposed to be: ABS(boolean_value) = 1

When using a parameterized query you want something like this:
SELECT * FROM table WHERE visible_for_me = :param
and
Query.Parambyname('visible_for_me').asBoolean := true; // or false;

When you have an existing database with values of 1 and -1 this doesn't work in SQLite.
Using this it will work:
SELECT * FROM table WHERE ABS(visible_for_me) = :param

Because this bug existed a long time in SQLite-connection this construction will still be necessary until all values are correct (or you correct the wrong values in one time).

[LacaK]

Good point. So it will break existing applications (and databases).
It makes patch bit problematic as it will require users change existing SQL statements.

[rvk]

Good point. So it will break existing applications (and databases).
It makes patch bit problematic as it will require users change existing SQL statements.

As it is now it's also breaks existing application if they use an existing database with records filled by other programs or even with the TSqlite3Dataset-component (because that one does write the 1 correctly).

So it's choosing the least bad option.

I, for one, now know of this incompatibility, so I will use ABS(boolean)=:param in the future, but the longer this incompatibility with SQLite-requirements exists, the harder it becomes to correct.

[madref]

Wow....this is an interesting discussion.
And only because i asked you a question RVK

[rvk]

Wow....this is an interesting discussion.
And only because i asked you a question RVK

Yep, what a small question can lead to 

[LacaK]

I did commit patch in trunk: rev.31086
And comment here: http://wiki.freepascal.org/User_Changes_Trunk#DB

[rvk]

I did commit patch in trunk: rev.31086
And comment here: http://wiki.freepascal.org/User_Changes_Trunk#DB

Yes, It's working. Thanks.

The comment is also clear. Let's hope others don't have problems with it, otherwise we can direct them to that comments.

[LacaK]

The comment is also clear. Let's hope others don't have problems with it, otherwise we can direct them to that comments.

I am still considering add conditional compiler define, which will allow relax old behavior.
Something like:
{$IFDEF SQLITE_OLD_BOOLEAN}
   // use P.AsInteger
{$ELSE}
  // use ord(P.AsBoolean)
{$ENDIF}
Not nice , but hard to say how many users will be affected with this change and if they will be happier with such solution.

[taazz]

The comment is also clear. Let's hope others don't have problems with it, otherwise we can direct them to that comments.

I am still considering add conditional compiler define, which will allow relax old behavior.
Something like:
{$IFDEF SQLITE_OLD_BOOLEAN}
   // use P.AsInteger
{$ELSE}
  // use ord(P.AsBoolean)
{$ENDIF}
Not nice , but hard to say how many users will be affected with this change and if they will be happier with such solution.

I wouldn't spend any more thought on the subject. The change is simple enough and it shouldn't affect to many people. Although SQLite is one of the most used data containers I would guess not many would use 1 or 0 and not true or false or even where X or where not X which I would expect to work regardless of the value of boolean in the database.

Then again I'm not using sqlite not even for demos.

[rvk]

Although SQLite is one of the most used data containers I would guess not many would use 1 or 0 and not true or false or even where X or where not X which I would expect to work regardless of the value of boolean in the database.

Well... the problem with SQLite is that you can't do something like
SELECT * FROM testtable WHERE bol = true
because SQLite does not know the term FALSE and TRUE.

Luckily you can use
SELECT * FROM testtable WHERE bol
and
SELECT * FROM testtable WHERE not bol
but the problem with that is you can't put it in a parameterized statement.
And this doesn't work again:
SELECT * FROM testtable WHERE bol = :param_boolean

But you are correct. I think the impact to existing users will be minimum and I think when they post the question about booleans in SQLite we can point them in the right direction.

I am still considering add conditional compiler define, which will allow relax old behavior.
Not nice , but hard to say how many users will be affected with this change and if they will be happier with such solution.

You could. But I think someone capable of recompiling FPC with this conditional compiler define is also capable of correcting his/her own program to act as it should according to SQLite-requirements. The largest problem that I see is that they initially don't know about this change and can stumble upon it and waste some time looking for a solution. But hey... that's what we are here for

[avra]

Are there any other DB-engines that have no real notion of TRUE and FALSE and store boolean as an integer?

Firebird has native boolean type since v3, which is still in beta. Interbase had the same problem and I don't know if it's fixed yet.
http://www.firebirdfaq.org/faq12
http://www.firebirdnews.org/boolean-datatype-implemented-in-fb-3

[LacaK]

Ok. Thanks for reply. So I will leave it as is. Hopping, that users will read http://wiki.freepascal.org/User_Changes_Trunk#DB when they will upgrade.

Btw. Interbase has already introduced native boolean datatype and it is supported by IBConnection (together with Firebirds boolean)

[garlar27]

Well, is hard to believe that none which has tested their app wouldn't have noted this problem.

Once I've been advised to do not use the boolean/logic field since it was not portable to other RDBS, and to use this instead:

Code: [Select]

  --   POSTGRES EXTRACT  --
  ENABLED character varying(1) NOT NULL DEFAULT '1'::character varying,
  CONSTRAINT MYTABLE_ENABLED_CHECK CHECK (upper(ENABLED ::text) = ANY (ARRAY['0'::text, '1'::text])),