[Mono-dev] Problem with sqlite in mono 1.1.13

[Aaron Bockover]

Actually, looking over the v2 data types:

http://sqlite.org/datatypes.html

I see "TIMESTAMP." I haven't tested it, but it's probably just a 64 bit
integer. However, if a column is of that type, it would be nice to
automatically convert to a System.DateTime using the appropriate
NativeConvert methods.

Just a thought.

--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