search

SQLite-DNA / SqliteDna

MIT License
4 stars 0 forks source link

Make provision for conversion options #3

Open govert opened 1 year ago

govert commented 1 year ago

In the databases I use (from .NET via System.Data.Sqlite provider), we set the following options in the connection string

DateTimeFormat=JulianDay;BinaryGuid=true

This means DateTimes are stored as doubles according to the SQLite julianday(...) function. And Guid values are stored as byte[] arrays.

If you download the help file for the .NET provider from here https://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki (then Unblock in file properties so that html shows), you can see a long list of options in the SQLiteConnectionStringBuilder members. I'm not sure which ones would be relevant, but let's start with the 

DateTimeFormat
DateTimeFormatString
DateTimeKind

BinaryGUID
UseUTF16Encoding (???)

I think we should have a way to configure such properties for an assembly or project, and then weave in converters in the type conversion code we generate. I'm not sure how best that configuration should look - maybe as an assembly attribute?

[assembly: SqliteOptions(DateTimeFormat = SqliteDateTimeFormats.JulianDay, BinaryGuid =true)]

I'm not sure at what granularity one would need this (e.g. for a particular function or a particular parameter?). For now maybe only for the whole project is fine.

Sergey-Vlasov commented 1 year ago

I’ve added DateTime construction from Julian day and unix timestamp formats. And I’ve added support for Guid that can be constructed from text and binary formats.

These different formats are supported by inspecting incoming SQLite value type: text for ISO-8601 DateTime, double for Julian day DateTime, int64 for unix timestamp DateTime, blob for binary Guid, text for text Guid.

Returned DateTime and Guid are converted to SQLite strings.

We are working on function level and I don’t think we should rely on connection string options, used by DB providers.

govert commented 1 year ago

OK - I'm going to leave this issue open for now, to see how things work out in practise. I appreciate that we might not want to fix these conversions in an extension, so controlling this with attributes at compile time probably doesn't make sense, and a function can decide which type to return if conversions are required. An extension could also expose some configuration function that lets the users set some of these format options at runtime.