[Mono-dev] Problem with sqlite in mono 1.1.13

[Aaron Bockover]

First of all, this says a lot (I just mentioned it in another reply):
http://sqlite.org/datatype3.html

On Sun, 2006-01-22 at 18:22 -0500, Joshua Tauberer wrote:
> Aaron Bockover wrote:
> > Wow. I'm not sure why this change was made, but in
> > Mono.Data.SqliteClient/SqliteDataReader.cs, this happens now for columns
> > declared as INT/INTEGER:
> 
> There was some confusion, I think either in bugzilla or on the mail
> lists, about DATETIME columns returning strings or something, so I
> figured that if you've declared a column as an INTEGER or DATETIME,
> that's probably the type of data you're going to be putting into it.
> Except, I realize now that there's no BIGINT equivalent in Sqlite.
> 
> It was sort of a compromise between doing what one would expect of any
> data adapter versus doing exactly what Sqlite does.

There should be no compromise here. If sqlite3 can store 8 bytes in an
INTEGER type, the bindings *must* account for it. Either always return
an Int64 or do some simple detection to see if the returned integer can
fit in an Int32, and then cast and return as such.

> So... I'll undo that.  Do you think I should also undo string conversion
> to DateTime for DATETIME columns?

This is a little tricky. I had some experience with this in October, and
we did some profiling on it. The problem is that DateTime.Parse (and
other various similar methods) is extremely slow. I think there were
something like 60 string allocations per invocation of that method. I
was using DATETIME in Banshee and parsing when pulling data from the
reader. Each row had two DATETIME columns, and on a 4k database, it took
about 20 seconds to read. The bottleneck was DateTime.Parse of course.

That being said, sqlite does store DATETIME values as strings, so there
are two options:

a) Parse the date in a more efficient manner, keeping in mind that in
sqlite, it will always be in the same format (YYYY-MM-DD HH:MM:SS)
(DateTime.Parse "guesses" at various formats).

b) Do some hackery like this (not sure how you would do this in the
reader, since it needs to be done at the statement level, before
committing):

SELECT strftime("%s", ColumnNameThatIsADATETIME);

The internal sqlite strftime function will convert the string to a unix
timestamp, normalized to UTC. You can pass "localtime" for example, as a
third argument to strftime if you don't want it in UTC.

I would go with option (a). That would keep compatibility with databases
that use DATETIME and would expect a System.DateTime in return. Just
avoid DateTime.Parse. But do to the fact that internally the value is a
string, just using a DATETIME column in your table definition is
immediately less efficient than storing a timestamp.

With all this in mind, here's probably the best way to handle dates in
sqlite: store them as INTEGERS as a unix timestamp and let the
application use Mono.Unix.Native.NativeConvert.ToDateTime/FromDateTime
to read/write the values.

When I changed to doing this in Banshee, the load time went from 20 to
1.5 seconds.

I wrote about this last month too, if you want more details:
http://lists.ximian.com/pipermail/mono-list/2005-December/029900.html

If you'd like I can take a look at the parsing issue and cook up a
patch, time permitting. It might also be nice to actually do detection
on INTEGER columns to see if it is appropriate to return an Int32.

Cheers!
--Aaron