Recently I was involved in an analytics application iOS port development. Since our model was relational we decided to use SQLite. As far as I know this is the only relational DBMS for iOS.
One of our tasks was weekly "Value per visit" report computation. Suppose we have a table described as:
CREATE TABLE [Usage]
[FacetId] VARCHAR, [Value ] INTEGER, [Visits ] INTEGER, [Date ] DATETIME );
In order to compute the report, I've composed a query below:
SELECT SUM( Value ) / SUM( Visits ),
strftime( '%Y-%W', Date ) AS week
WHERE Date BETWEEN @startDate AND @endDate
GROUP BY week
ORDER BY week;
For some reason, the query output did not match the reference implementation. The reason turned out to be simple. For SQLite, the week starts on Monday.
For reference implementation, the week starts on Sunday as it uses US locale.
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' );
The article below will explain how to overcome this problem.
You should have basic knowledge of SQL and ObjectiveC. You should also be familiar with week based calendar concepts.
Unfortunately, I have not found a way to force set the SQLite locale. Performing aggregation in ObjectiveC code isn't a good solution either.
Luckily, SQLite has the
sqlite3_create_function API that serves for adding custom DBMS extensions. So I've decided to implement a custom function that will perform
date formatting with respect to the locale. It would use the
NSDateFormatter API under the hood.
You'll benefit in:
- We can still use SQL to perform aggregation.
- No need to write loops and aggregation in Objective-C. Hence, you'll have less code and bugs.
- You'll get better performance as you'll iterate the dataset only once.
- This solution is easier to reuse.
Suppose we'll operate only with Gregorian calendars to simplify the code.
The SQLite extension function has a signature similar to C++
void ObjcFormatAnsiDateUsingLocale (sqlite3_context * ctx_, int argc_, sqlite3_value ** argv_);
It has no return value. Instead it receives a
sqlite3_context* handle which is used to return the result or an error.
Its SQL interface will take three arguments:
NSDateFormatter compatible date format
- Date string in ANSI format
NSLocale compatible locale identifier
This report will correctly detect that Sunday 2011-01-02 belongs to the second week of year 2011.
sqlite> SELECT ObjcFormatAnsiDateUsingLocale ('YYYY-ww', '2011-01-02 ',' en_US ');
Thus, we need to do four things:
- Register the SQLite custom function so that it can be used in queries.
argv_ parameters to
Foundation.framework compatible types. In our case, it will be
[NSString, NSDate, NSString], respectively.
- Conduct date formatting using
- Return the result.
0. Register the SQLite function
This is done with
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
&ObjcFormatAnsiDateUsingLocale, / / <<function implementation
NULL, NULL / / It's required as the function does not perform aggregation.
1. Input parameters processing
SQLite ensures that your function receives the correct amount of arguments. However, I recommend to check them anyway just in case.
Since SQLite will take care of allocated resources, I suggest using
NSString. You must pass "NO" to the "
2. Date formatting
With Foundation framework, the implementation is pretty straightforward.
NSDateFormatter* inputFormatter_ = nil;
NSDateFormatter* targetFormatter_ = nil;
inputFormatter_.dateFormat = @"yyyy-MM-dd";
targetFormatter_.dateFormat = format_;
NSDate * date_ = [inputFormatter_ dateFromString: strDate_];
return [targetFormatter_ stringFromDate: date_];
However, there are some nuances:
- Both the
NSDateFormatter objects contain
NSLocale instance. It's very important to ensure that
[ NSDateFormatter.locale isEqual: NSDateFormatter.calendar.locale ];.
Otherwise you'll experience some undefined behaviour and bugs.
- The input formatter must have the @"en_US_POSIX" locale and @"yyyy-MM-dd" format as SQLite stores dates in ANSI format.
- Creating a properly initialized
NSDateFormatter too often may result in poor performance. Don't call it too often.
3. Returning the result
You must use
sqlite3_result_text() to accomplish this. It's important to use
SQLITE_TRANSIENT to make SQLite obtain a copy of the result string.
If you use
SQITE_STATIC you'll get a crash after Foundation framework disposes resources.
You can obtain the source code and the text of this article at github.