Learn SQLite to work with a localized calendar.

    Learn SQLite to work with a localized calendar.



    Part 1 - English version , Part 2



    Recently, I happened to port (or rather, rewrite) an iOS application for analyzing site traffic to 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.

    How long, how short ... It's time to calculate the weekly efficiency for a certain date range.

    So, we have a table that contains the values ​​of useful and expended work, as well as the date of completion 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 some date range to calculate the efficiency for each week. Ok, 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, for some reason, the results did not agree with the reference implementation. It turned out the following. SQLite believes the week starts on Monday. While the reference implementation considers Sunday to be the beginning of the week as is customary in the USA.
    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 did not find a way to force the DBMS locale to be specified. I really did not want to break a beautiful request into several. In addition, I discovered the ability to add functions to SQLite using sqlite3_create_function.
    Yes, I decided to write my own option for formatting dates with preference and courtesans. It will differ from strftime in its 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 in Objective-C
    • we get potentially faster query execution
    • And most importantly - this solution is designed for reuse


    So let's get started. Simplify the statement of the problem, limiting ourselves to 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, the database context from which it was called is passed to it. This context is used to return a result or error.

    In an SQL query, the function will take an Objective-C-style date format, in fact, the date and locale. This request will correctly attribute Saturday 2011-01-02 to the second week of 2011 as it should be in the American regions.

    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, it will be [NSString, NSDate, NSString] respectively.
    3. Format date using NSDateFormatter
    4. Return 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 whether the number of parameters matches. However, I recommend that you leave checks to argc just in case.

    Since SQLite itself will free up parameter resources, it is better to use the NSString-> initWithBytesNoCopy constructor: 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 both in the NSCalendar object and in the NSDateFormatter.
      It is very important that the condition "NSDateFormatter.calendar.locale == NSDateFormatter.locale" is met.

    • inputFormatter_ must have the locale "en_US_POSIX"
    • SQLite stores dates in ANSI format @ "yyyy-MM-dd". It must be set for inputFormatter_
    • Creating an NSDateFormatter is a very expensive operation. Try not to call her again

    -

    3. Returning 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. Calculation converged.
    The source code is available on the github project page - dodikk / ESLocale
    Code review and Pull request are welcome.

    I hope my function is useful to someone.
    Then I take my leave.

    Also popular now: