使用 SQLite 执行本地化周基日历计算

• 下载演示项目
• 下载源代码

引言

最近我参与了一个分析应用程序的 iOS 端口开发。由于我们的模型是关系型的，我们决定使用 SQLite。据我所知，这是 iOS 唯一的 relational DBMS。

我们的任务之一是每周的“每次访问价值”报告计算。假设我们有一个表，描述如下：

CREATE TABLE [Usage]
(
  [FacetId] VARCHAR, -- "Visit kind"
  [Value  ] INTEGER, -- useful "work amount"
  [Visits ] INTEGER, -- spent "work amount"
  [Date   ] DATETIME -- date
);

为了计算报告，我编写了以下查询：

SELECT SUM( Value ) / SUM( Visits ),
    strftime( '%Y-%W', Date ) AS week
FROM Usage
WHERE Date BETWEEN @startDate AND @endDate
GROUP BY week
ORDER BY week;

出于某种原因，查询输出与参考实现不匹配。原因很简单。对于 SQLite，一周从星期一开始。对于参考实现，一周从星期日开始，因为它使用美国区域设置。

sqlite> SELECT strftime( '%Y-%W', '2011-01-02' );
2011-01 ## expecting to receive 2011-02 for US locale
sqlite> SELECT strftime( '%Y-%W', '2011-01-01' );
2011-01

下面的文章将解释如何克服这个问题。

背景

您应该具备 SQL 和 ObjectiveC 的基本知识。您还应该熟悉基于周的日历概念。

解决方案

不幸的是，我没有找到强制设置 SQLite 区域设置的方法。在 ObjectiveC 代码中执行聚合也不是一个好的解决方案。幸运的是，SQLite 具有 sqlite3_create_function API，用于添加自定义 DBMS 扩展。所以我决定实现一个自定义函数，该函数将根据区域设置执行日期格式化。它会在底层使用 NSCalendar 和 NSDateFormatter API。

您将受益于：

1. 我们仍然可以使用 SQL 来执行聚合。
2. 无需在 Objective-C 中编写循环和聚合。因此，您将拥有更少的代码和错误。
3. 您将获得更好的性能，因为您只会迭代一次数据集。
4. 此解决方案更易于重用。

假设我们只使用公历来简化代码。

SQLite 扩展函数的签名类似于 C++ main()。

void ObjcFormatAnsiDateUsingLocale (sqlite3_context * ctx_, int argc_, sqlite3_value ** argv_);

它没有返回值。相反，它接收一个 sqlite3_context* 句柄，该句柄用于返回结果或错误。

它的 SQL 接口将接受三个参数：

1. NSDateFormatter 兼容的日期格式
2. ANSI 格式的日期字符串
3. NSLocale 兼容的区域设置标识符

该报告将正确检测到 2011-01-02 星期日属于 2011 年的第二周。

sqlite> SELECT ObjcFormatAnsiDateUsingLocale ('YYYY-ww', '2011-01-02 ',' en_US ');
2011-02

因此，我们需要做四件事：

1. 注册 SQLite 自定义函数，以便可以在查询中使用它。
2. 将 argv_ 参数转换为 Foundation.framework 兼容的类型。在我们的例子中，它将分别是 [NSString, NSDate, NSString]。
3. 使用 NSDateFormatter 执行日期格式化。
4. 返回结果。

0. 注册 SQLite 函数

这是通过 sqlite3_create_function() 完成的。

sqlite3_create_function
(
db_, / / <<database HANDLE received from sqlite3_open
"ObjcFormatAnsiDateUsingLocale", / / <<name of the function to be used in queries
3, / / <<number of parameters. SQLite will ensure that their count matches
SQLITE_UTF8, / / <<the encoding for iOS enough
NULL,
&ObjcFormatAnsiDateUsingLocale, / / <<function implementation
NULL, NULL / / It's required as the function does not perform aggregation.
);

1. 输入参数处理

SQLite 确保您的函数接收到正确数量的参数。但是，我建议无论如何都要检查它们，以防万一。由于 SQLite 将处理分配的资源，我建议使用 @selector(initWithBytesNoCopy:length:encoding:freeWhenDone:) 来初始化 NSString。您必须将 "NO" 传递给 "freeWhenDone" 参数。

2. 日期格式化

使用 Foundation framework，该实现非常简单。

NSDateFormatter* inputFormatter_  = nil;
NSDateFormatter* targetFormatter_ = nil;

//... initialize date formatters

inputFormatter_.dateFormat = @"yyyy-MM-dd";
targetFormatter_.dateFormat = format_;

NSDate * date_ = [inputFormatter_ dateFromString: strDate_];
return [targetFormatter_ stringFromDate: date_];

但是，有一些细微差别：

1. NSCalendar 和 NSDateFormatter 对象都包含一个 NSLocale 实例。确保 [ NSDateFormatter.locale isEqual: NSDateFormatter.calendar.locale ]; 非常重要。否则，您将遇到一些未定义的行为和错误。



2. 输入格式化程序必须具有 @"en_US_POSIX" 区域设置和 @"yyyy-MM-dd" 格式，因为 SQLite 以 ANSI 格式存储日期。
3. 过于频繁地创建正确初始化的 NSDateFormatter 可能会导致性能下降。不要太频繁地调用它。

3. 返回结果

您必须使用 sqlite3_result_text() 来完成此操作。使用 SQLITE_TRANSIENT 使 SQLite 获取结果字符串的副本非常重要。如果使用 SQITE_STATIC，您将在 Foundation framework 释放资源后崩溃。

就是这样。

您可以在 github 上 获取源代码和本文的文本。