在SQLite.Net中存储DateTimeOffset [英] Store DateTimeOffset in SQLite.Net

问题描述

在WinRT(Windows 8.1 Store App)项目中，我使用SQLite.Net-PCL和SQLiteNetExtensions NuGet包将数据存储在本地SQLite数据库文件中.

我的几个数据模型(又称表)包含类型为 DateTimeOffset 的属性.目的是在不丢失偏移量信息的情况下存储这些.(这样做的原因是，用户可以在指定日期/时间的同时输入时区信息，并且该信息必须存储在数据库中.)

我知道在创建 SQLiteConnection 时可以设置的 storeDateTimeAsTicks 参数，将其设置为 false 会强制所有DateTime 属性以ISO格式存储为文本-但是，这对 DateTimeOffset 属性没有影响，因为这些属性始终会自动转换为UTC并存储为代表刻度的数字.

我可以想到以下4种方法:

• 手动将 DateTimeOffset 转换为 string 属性并存储这些属性，或者
• 提取 DateTimeOffset 的 DateTime 和偏移量(作为 TimeSpan 日期类型)部分，并将它们存储在两个单独的列中，

=>，但是对于这两种方法，我都需要向数据模型添加其他属性，并使用 [Ignore] 属性标记原始的 DateTimeOffset 属性，并处理手动转换(双向)-由于我需要将此转换应用于许多不同的数据模型类，因此似乎很难维护.

• 将 DateTimeOffset 的 DateTime 和 TimeSpan (偏移)部分存储在单独表的两列中，并维护对此单独引用的引用桌子

=>，但在这种情况下，我需要定义一个自定义数据类型(以指定应如何存储 DateTime 和 TimeSpan 部分)，以及不能使用默认的.NET DateTimeOffset 类型

• 使用SQLiteNetExtensions的 TextBlob 属性以某种方式将 DateTimeOffset 序列化为单个文本列

=>但这有点怪异，我需要确保仅SQLiteNetExtensions的扩展方法用于数据库插入/更新，而我仍然需要在其上附加一个 string 属性所有数据模型类...

所以，我的问题是:我是否还有更进一步，更明显的解决方案?

解决方案

由于没有人想出可能的解决方案，但是这个问题仍然受到关注，所以我决定报告如何解决该问题:

方法1:

提出原始问题的场景包括一个移动应用，该移动应用由

组成

• API数据模型类(用于从JSON序列化到/从JSON序列化，并从后端REST服务上传到/下载)，
• DB数据模型类(代表SQLite表)，和
• 用于MVVM样式表示层的各种ViewModel类

API模型和DB模型几乎相同(除了JSON序列化和SQLite OR映射所必需的属性)，唯一的结构差异是表示日期/时间的属性在API中的类型为 string 类和数据库类中的 DateTimeOffset .从数据下载后和将数据上传到后端之前，使用Automapper将API和DB模型相互转换.

我只是从Automapper配置中删除了从 string 到 DateTimeOffset 的转换，并修改了DB数据模型类，以便表示 DateTimeOffset 值作为 string 的形式，这意味着它们以格式文本存储在SQLite中(幸运的是，不需要在DB层上进行日期/时间计算).由于从后端接收的JSON对象包含时区信息，因此我可以简单地将这些值传递给数据库模型，从而确保数据库表始终将日期/时间包含为完全格式的日期时间字符串，包括时区偏移.

现在，当从数据库数据模型中创建ViewModel类时，会发生从 string 到 DateTimeOffset 的转换.显然，这种情况比以前发生的次数要多(将API模型转换为DB模型时)，从而产生了一些开销，但是我可以忍受，因为我不再需要担心SQLite数据类型问题.

方法2:

由于方法#1可能不适用于所有情况，因此我提出了一个替代解决方案，该解决方案基于原始问题中提出的4个潜在解决方案中的第一个，但减少了人工工作:

我创建了一个自定义属性 [DateTimeOffsetSerialize] ，可以将其分配给SQLite数据模型类中的 DateTimeOffset 属性，并创建一个后构建任务，以在构建完成后反汇编程序集并扫描程序集中的所有类以找到那些标记的属性.对于这些标记的属性中的每一个，都会自动创建一个类型为 string 的重复属性，其中包含原始属性的序列化值，并且此新创建的 string 属性将用作SQLite表列(原始的 DateTimeOffset 属性自动标记为 [Ignore] 属性).

此解决方案可以通过 NuGet包获得，并且已经在GitHub上开源(GitHub页面还包含详细的使用说明).

In a WinRT (Windows 8.1 Store App) project, I'm using the SQLite.Net-PCL and SQLiteNetExtensions NuGet packages to store data in a local SQLite database file.

Several of my data models (aka tables) contain properties of type DateTimeOffset. The aim is to store these without losing the offset information. (Reason for this is that the user can enter timezone information while specifying dates / times, and this information must be stored in the database.)

I'm aware of the storeDateTimeAsTicks parameter that can be set while creating the SQLiteConnection, setting this to false forces all DateTime properties to be stored as text in ISO format - however, this has no consequences on DateTimeOffset properties, as these are always automatically converted to UTC and stored as a number representing ticks.

I can think of the following 4 approaches:

• Manually convert DateTimeOffset to string properties and store these, or
• extract the DateTime and offset (as TimeSpan date type) parts of the DateTimeOffset, and store these in two separate columns,

=> but for both approaches, I'll need to add additional properties to the data model, flag the original DateTimeOffset properties with the [Ignore] attribute, and handle manual conversion (in both directions) - since I'd need to apply this to a lot of different data model classes, it seems too hard to maintain.

• Store the DateTime and TimeSpan (offset) parts of the DateTimeOffset in two columns of a separate table, and maintain references to this separate table

=> but in this case I'd need to define a custom data type (in order to specify how the DateTime and TimeSpan parts should be stored), and cannot use the default .NET DateTimeOffset type

• Use SQLiteNetExtensions' TextBlob attribute to somehow serialize the DateTimeOffset to a single text column

=> but this feels somewhat hacky, I'd need to make sure that only SQLiteNetExtensions' extension methods are used for DB insert / update, and I'd still need an additional string property on all the data model classes...

So, my question is: Is there a more straigtforward, obvious solution that I'm missing?

解决方案

Since nobody came up with potential solutions, but the question still received some attention, I decided to report how I solved the problem:

Approach #1:

The very scenario that raised the original question included a mobile app consisting of

• API data model classes (used for serialization to / from JSON and upload to / download from the backend REST service),
• DB data model classes (representing SQLite tables), and
• various ViewModel classes used for the MVVM-style presentation layer

API models and DB models were nearly identical (except for attributes necessary for JSON serialization and SQLite OR mapping), the only structural difference being that properties representing dates / times were of type string in the API classes and DateTimeOffset in the DB classes. After downloading data from, and before uploading data to the backend, API and DB models were converted into each other using Automapper.

I simply removed the string to DateTimeOffset conversion from the Automapper configuration, and modified the DB data model classes so that DateTimeOffset values are represented as string, which implies that they are stored as formatted text in SQLite (fortunately, no date / time calculations on the DB layer were required). Since JSON objects received from the backend include timezone information, I could simply pass through those values to the DB models, thus ensuring that DB tables always contain dates / times as fully formatted datetime strings including timezone offset.

Conversion from string to DateTimeOffset now happens when creating ViewModel classes out of DB data models. Obviously, this happens more often than before (when converting API models to DB models) resulting in a little overhead, but I can live with that since I don't need to worry about the SQLite data type issue any more.

Approach #2:

Since approach #1 may not be applicable to all scenarios, I came up with an alternative solution that is based on the first one of the 4 potential solutions proposed in the original question, but with reduced manual effort:

I created a custom attribute [DateTimeOffsetSerialize] that can be assigned to DateTimeOffset properties in SQLite data model classes, and a postbuild task that decompiles the assembly after build has finished and scans all classes within the assembly to find those flagged properties. For each of these flagged properties, a duplicate property of type string is created automatically that contains the original property's serialized value, and this newly created string property will be used as SQLite table column (the original DateTimeOffset property is automatically flagged with the [Ignore] attribute).

This solution is available as NuGet package, and has been open-sourced on GitHub (the GitHub page also contains details usage instructions).

这篇关于在SQLite.Net中存储DateTimeOffset的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！