SQLite returns booleans as strings

[grisevg]

If you do a select query from a table with boolean field, that field will be returned to haxe as a string of either f or t.

[waneck]

That's pretty strange. We unit test those when working with SPOD. Was this database created by Haxe itself or it was created externally?

[grisevg]

It was actually created by Ruby on Rails. If I open this sqlite db in http://sqlitebrowser.org/ it shows thid DDL:

CREATE TABLE "exercise_contents" 
(
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
"exercise_id" integer, 
"content_id" integer, 
"content_type" varchar, 
"created_at" datetime NOT NULL, 
"updated_at" datetime NOT NULL, 
"correct_value" boolean
)

IntelliJ IDEA database viewer seems to think it's an INTEGER though

Here's haxe code I use

var rset = sys.db.Manager.cnx.request("SELECT * FROM exercise_contents");
for (lo in rset) trace(lo.correct_value + " - " + Type.typeof(lo.correct_value));

And it prints:

Main.hx:67: f - TClass(String)
Main.hx:67: f - TClass(String)
Main.hx:67: f - TClass(String)
Main.hx:67: t - TClass(String)
Main.hx:67: f - TClass(String)
Main.hx:67: f - TClass(String)
Main.hx:67: f - TClass(String)

[waneck]

Can you share a database file that shows this error?

[grisevg]

https://dl.dropboxusercontent.com/u/399575/foobar.sqlite3

[grisevg]

Could this be the issue? https://github.com/HaxeFoundation/hxcpp/blob/master/project/libs/sqlite/sqlite.cpp#L199 Shouldn't it be boolean instead of BOOL?

[grisevg]

I've printed dtype right after that line and here's output:

INTEGER
integer
integer
varchar
datetime
datetime
boolean

[grisevg]

sqlite3_column_type in https://github.com/HaxeFoundation/hxcpp/blob/master/project/libs/sqlite/sqlite.cpp#L247 return SQLITE_TEXT (3)

I really doubt it's a bug in actual sqlite3. Is it because the database is in the wrong format?

[waneck]

Okay, the problem is really that SQLite doesn't have enforce type information (and even if it did, it doesn't have a boolean type out of the box), and each framework deals with that differently; While Haxe makes the type as BOOL and sets it as 0/1, it seems that ruby on rails sets it to boolean and sets a 't'/'f' field - which seems strange at first glance, actually Anyway, if we do decide to support this, we'll need to support it directly into the driver (sqlite.cpp). This will be hard to enforce on all targets, though - for example, on Java - since we don't control the driver there.

[grisevg]

So let me get this right. SQLite3 sais that BOOLEAN is a NUMERIC which means that it can actually be any data type (https://www.sqlite.org/datatype3.html). Rails stores booleans as strings for some reason, which is a totally valid for SQLite. HXCPP SQLite driver loads them as strings, because they were stored as strings which is a correct behaviour.

From SQLIte3 docs:

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class. No attempt is made to convert NULL or BLOB values.

So f and t can't be converted to a number hence SQLite stores them as TEXT

[waneck]

Yes; And SQLite even says that Boolean fields should be represented with 0/1, like Haxe does. I have no idea why Rails uses String instead

[grisevg]

Ruby devs... everything is backwards. Thank you, it means there's no bugs, everything works as it should.