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@lists.ximian.com http://lists.ximian.com/mailman/listinfo/mono-devel-list