For the past few months I’ve been working on a desktop application for a client that required a version for Mac and one for PC. I chose to write it using REALBasic 2007 because I don’t particularly like Java, and REALBasic allowed me to write source code once, and compile for OS X, Windows, and Linux which was a great time-saver.

My application had need of a database, and because of certain business requirements, a database server was out of the question. This left SQLite, a database engine that is compiled into the application itself, requiring only a data file to hold the database (SQLite also allows you to use an in-memory database by the way).

I guess I really should have read up on SQLite, but I didn’t. I just assumed it would function more or less like any other database. Well, there are a few annoying characteristics of SQLite that I hope get resolved in the near future. One is weak data-typing: I wrote the SQL script using integer, varchar, and boolean data types. This appeared to work at first, but then I noticed that I could store any string in a field of any type. This makes data corruption almost a certainty at some point.

I knew that I was going to be the only one writing code that accessed this database though, so I figured I could avoid this pitfall by being careful. I proceeded. I guess I wasn’t careful enough. I was having problems with booleans. I tried a statement to insert a boolean value into a table like this

INSERT INTO myTable (boolField) VALUES(true);

This caused a syntax error because of the symbol “true”. Ok, maybe I have to quote true.

INSERT INTO myTable (boolField) VALUES('true');

This seemed to work fine. I check the database using SQLiteManager from SQLabs, and see that the field does indeed hold the value true. Because of this apparent success I carry on writing code that uses ‘true’ and ‘false’ for booleans. Well, remember that SQLite is weakly typed. I had a field declared as a boolean, but I can store any old text in there. SQLite doesn’t care much.

It wasn’t until I stared editing boolean fields in SQLiteManager that I noticed ‘true’ was being stored as 1 and ‘false’ as 0. What a pain in the ass this is. I have to change all the code that interacts with booleans. If I have a boolean variable in my code, I need to make sure it gets cast as an integer before being inserted into the database, or I’ll get an error when I retrieve it and try to use it as a boolean. Oh, and in REALBasic, there is no way to cast a boolean as an integer, so I have to write a function to do it.

I wonder if there’s a better solution than SQLite out there. I’ve been spoiled developing web applications for so long. The logistics of a remote database server aren’t a problem, so I’ve always used Oracle, MySQL and MS SQL Server up until now.