We learn SQLite to work with the localized calendar

Part 1 - English version , Part 2


')
Recently I happened to port (more precisely, rewrite) an application for analyzing site traffic under iOS. Since the data fit the relational model, we decided to use SQLite. As far as I know, there are simply no other relational DBMS for iOS.

Long or short ... It's time to calculate the weekly efficiency for a specific date range.

So, we have a table that contains the values ​​of the useful and expended work, as well as the dates of execution of this work. The structure of the table is described in SQL as follows:

CREATE TABLE [ Usage ]
(
[FacetId] VARCHAR , -- ""
[ Value ] INTEGER , -- ""
[Visits ] INTEGER , -- ""
[ Date ] DATETIME --
);

* This source code was highlighted with Source Code Highlighter .

It is necessary for a certain range of dates to calculate the efficiency for each week. Okay, they wrote a request

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;

* This source code was highlighted with Source Code Highlighter .

However, the results for some reason did not agree with the reference implementation. It turned out the following. SQLite assumes that the week starts on Monday. While the reference implementation considers the beginning of the week Sunday, as is customary in the United States.

sqlite> SELECT strftime( '%Y-%W' , '2011-01-02' );
2011-01 ## 2011-02
sqlite> SELECT strftime( '%Y-%W' , '2011-01-01' );
2011-01

* This source code was highlighted with Source Code Highlighter .

I could not find a way to force the DBMS locale. I didn’t want to break the beautiful request into a few. In addition, I discovered the ability to add functions to SQLite using sqlite3_create_function.
Yes, I decided to write my own version of formatting dates with preference and courtesans. From strftime it will differ in the ability to take into account the locale passed from the request.

The benefits of this solution are obvious:

• we stay within SQL
• no need to write extra loops on Objective-C
• we will get potentially faster query execution
• And most importantly - this solution is designed for reuse.

So let's get started. Simplify the formulation of the problem by limiting the limits of the Gregorian calendar.

The SQLite extension function has a signature similar to the main () function.

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

* This source code was highlighted with Source Code Highlighter .

The difference is that it does not have a return flag. Instead, it is passed the context of the database from which it was called. This context is used to return a result or error.

In a SQL query, the function will accept the date format in the Objective-C style, in fact, the date and locale. This request correctly refers Saturday 2011-01-02 to the second week of 2011, as it should be in the United States.

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

* This source code was highlighted with Source Code Highlighter .

Thus, we need to do 4 things:

1. Register a function in SQLite so that it can be used in queries.
2. Convert parameters from argv_ to Foundation types. In our case, these will be [NSString, NSDate, NSString], respectively.
3. Perform date formatting with NSDateFormatter
4. Return the result

==============

0. Register SQLite function

This is done using sqlite3_create_function. www.sqlite.org/c3ref/create_function.html

sqlite3_create_function
(
db_, // HANDLE , sqlite3_open
"ObjcFormatAnsiDateUsingLocale" , //
3, // . SQLite
SQLITE_UTF8, // iOS
NULL,
&ObjcFormatAnsiDateUsingLocale, //
NULL, NULL // . .
);

* This source code was highlighted with Source Code Highlighter .

-

1. Conversion of parameters

SQLite independently checks the correspondence of the number of parameters. However, I recommend to keep checking for argc just in case.

Since SQLite will itself free up parameter resources, it is better to use the constructor NSString-> initWithBytesNoCopy: length: encoding: freeWhenDone:
-

2. Date formatting

At first glance, everything is simple.

inputFormatter_.dateFormat = @"yyyy-MM-dd" ;
NSDate* date_ = [ inputFormatter_ dateFromString: strDate_ ];
targetFormatter_.dateFormat = format_;
return [ targetFormatter_ stringFromDate: date_ ];

* This source code was highlighted with Source Code Highlighter .

However, there are some nuances.

• As you know, an instance of the NSLocale class is contained in both the NSCalendar object and the NSDateFormatter.
  It is very important that the condition "NSDateFormatter.calendar.locale == NSDateFormatter.locale" is met.
  
  
• inputFormatter_ should have the "en_US_POSIX" locale
• SQLite stores dates in ANSI format @ "yyyy-MM-dd". It is necessary to set it for inputFormatter_
• Creating an NSDateFormatter is a very expensive operation. Try not to call her again

-

3. Return of the result

For these purposes, the sqlite3_result_text function is used. It is important to use the SQLITE_TRANSIENT option so that SQLite makes a copy of the resources allocated in the Foundation Framework.
==============

That's all. The calculation came together.
The source code can be found on the github project page - dodikk / ESLocale
Code review and Pull request are welcome.

I hope my function will come in handy.
So I will take my leave.