evan_tech

Previous Entry Share Next Entry
12:59 pm, 16 Mar 04

sql

After fixing ocaml-sqlite, I wrote the glue code for ocaml-dbi:
% examples/dbi_example.exe test.db
* Trying to connect to the database... CONNECTED.
* Pinging the database... the database is UP.
* Database type is 'sqlite'.    <----
* Creating some temporary tables... OK
* Inserting some data into the temporary tables... OK
* Making some queries on the data and checking their results... OK
* Closing connection to the database... CLOSED.  Exiting.

(That ".exe" in the filename is because their makefiles are weird.)

SQLite is really weird. Not only is it public domain(!), but then there's this:
SQLite is "typeless". This means that you can store any kind of data you want in any column of any table, regardless of the declared datatype of that column. This behavior is a feature, not a bug. A database is suppose to store and retrieve data and it should not matter to the database what format that data is in.
I'm not sure if I agree with their reasoning, but there it is. It seems it'd be harder to make fast (there are no fixed-length rows, for example) but their design space is different (all queries hit the disk once they're committed) and I bet they know more about the problem than I do.

That "typelessness" is especially odd to merge with OCaml, which is all about types... but there's already a disconnect between the SQL type system and OCaml's that is hard to fix.
For example, if I do something like:
let sth = dbh#prepare "SELECT * FROM user WHERE userid=?;"
I can execute that query with either an integer or a string (or a blob or...); the expansion of the question mark is independent of the type used in the table.
I suppose a string would be expanded by the DBI layer as a quoted value, so there'd be a run-time exception on a normal SQL database, but for most cases you know your database schema at compile time and this sort of thing could be verified.

I'm already imagining cool ways that could be made to work: you would extend the OCaml parser to understand SQL statements and only generate the textual queries once it's time to run them, and... I know a bunch of people have been writing papers on this subject. I ought to look around first.


As far as I can tell, ocaml-sqlite never worked without my makefile patch. I mailed the maintainer but haven't received a response yet. The page is dated from mid-2003, which makes me think I may need to just publish my version.
And I'd send a patch for ocaml-dbi but their site is down.