[Mono-dev] [PATCH] Problem with sqlite in mono 1.1.13

[Aaron Bockover]

Attached is a patch that will return an Int64 for INTEGER columns unless
the value can fit in an Int32, in which case it will return an Int32.
Also changed from DateTime.Parse to DateTime.ParseExact. I'm not sure if
I'm doing the proper thing with the culture. I'm pretty sure it returns
UTC.

I'm also not 100% sure I like the ability of INTEGER columns to be
either Int64 or Int32. Maybe they should just always be Int64. Maybe it
doesn't much matter :)

I have also attached a test case showing how slow DateTime.Parse is.

Cheers,
Aaron

On Sun, 2006-01-22 at 18:41 -0500, Aaron Bockover wrote:
> 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
> 
> 
> 
> _______________________________________________
> Mono-devel-list mailing list
> Mono-devel-list at lists.ximian.com
> http://lists.ximian.com/mailman/listinfo/mono-devel-list
-------------- next part --------------
A non-text attachment was scrubbed...
Name: SqliteDataReader.cs-Int64.diff
Type: text/x-patch
Size: 2341 bytes
Desc: not available
Url : http://lists.ximian.com/pipermail/mono-devel-list/attachments/20060122/b89e2016/attachment.bin 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: DateTimeParseTest.cs
Type: text/x-csharp
Size: 1141 bytes
Desc: not available
Url : http://lists.ximian.com/pipermail/mono-devel-list/attachments/20060122/b89e2016/attachment-0001.bin