Учим SQLite работать с локализированным календарем

Учим  SQLite работать с локализированным календарем

Недавно мне довелось портировать (точнее, переписывать) под iOS приложение для анализа посещаемости сайта. Поскольку данные подходили под реляционную модель, мы решили использовать SQLite. Насколько я знаю, других реляционных СУБД под iOS просто нет.

Долго ли, коротко ли… Пришло время подсчитать понедельный КПД для определенного диапазона дат.

Итак, имеем таблицу, которая содержит значения полезной и затраченной работы, а также даты исполнения оной работы. Структура таблица описывается на языке SQL следующим образом:

CREATE TABLE [Usage]
(
  [FacetId] VARCHAR, -- "исполнитель"
  [Value ] INTEGER, -- полезная "работа"
  [Visits ] INTEGER, -- затраченная "работа"
  [Date  ] DATETIME -- дата 
);

* This source code was highlighted with Source Code Highlighter [1].

Необходимо для некоторого диапазона дат посчитать КПД для каждой недели. Ладно, написали запрос

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 [1].

Однако результаты почему-то не сошлись с эталонной реализацией. Выяснилось следующее. SQLite полагает что неделя начинается с понедельника. В то время как эталонная реализация считает началом недели воскресенье как это принято в США.

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 [1].

Способа принудительно указать СУБД локаль я не нашел. Разбивать красивый запрос на несколько очень не хотелось. К тому же, я обнаружил возможность добавлять функции в SQLite с помощью sqlite3_create_function.
Да, я решил написать свой вариант форматирования дат с преферансом и куртизанками. От strftime он будет отличаться умением учитывать переданную из запроса локаль.

Выгоды такого решения очевидны:

• мы остаемся в рамках SQL
• не нужно писать лишние циклы на Objective-C
• мы получим потенциально более быстрое исполнение запросов
• И самое главное — это решение рассчитано на повторное использование

Итак, приступим. Упростим постановку задачи, ограничившись пределами григорианского календаря.

Функция-расширение SQLite имеет сигнатуру, подобную функции main().

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

* This source code was highlighted with Source Code Highlighter [1].

Отличие состоит в том, что она не имеет флага фозврата. Вместо этого в нее передается контекст базы данных, из которой она была вызвана. Этот контекст используется для возврата результата либо ошибки.

В SQL запросе функция будет принимать формат даты в стиле Objective-C, собственно, дату и локаль. Этот запрос верно отнесет субботу 2011-01-02 ко второй неделе 2011го года как и положено в американских краях.

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

* This source code was highlighted with Source Code Highlighter [1].

Таким образом, нам необходимо сделать 4 вещи:

1. Зарегистрировать функцию в SQLite дабы ее можно было использовать в запросах.
2. Преобразовать параметры из argv_ в Foundation типы. В нашем случае это будут [ NSString, NSDate, NSString ] соответственно.
3. Провести форматирование даты с помощью NSDateFormatter
4. Вернуть результат

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

0. Регистрируем SQLite функцию

Это делается с помощью sqlite3_create_function. www.sqlite.org/c3ref/create_function.html ^[2]

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].

—

1. Преобразование параметров

SQLite самостоятельно проверяет соответствие количества параметров. Однако я рекомендую на всякий случай оставлять проверки на argc.

Поскольку SQLite сам освободит ресурсы параметров, лучше использовать конструктор NSString->initWithBytesNoCopy:length:encoding:freeWhenDone:
—

2. Форматирование даты

На первый взгляд, тут все просто.

  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 [1].

Однако есть некоторые нюансы.

• Как известно, экземпляр класса NSLocale содержится как в объекте NSCalendar, так и в NSDateFormatter.
  Очень важно чтобы выполнялось условие «NSDateFormatter.calendar.locale == NSDateFormatter.locale».

  

• inputFormatter_ должен иметь локаль «en_US_POSIX»
• SQLite хранит даты в ANSI формате @«yyyy-MM-dd». Его и необходимо выставить для inputFormatter_
• Создание NSDateFormatter — весьма затратная операция. Старайтесь лишний раз ее не вызывать

—

3. Возврат результата

Для этих целей используется функция sqlite3_result_text. Важно использовать опцию SQLITE_TRANSIENT чтобы SQLite сделал копию ресурсов, выделенных в Foundation Framework
==============

Вот, собственно, и все. Расчет сошелся.
С исходным кодом можно ознакомиться на странице проекта github — dodikk/ESLocale ^[3]
Code review и Pull request приветствуются.

Надеюсь, моя функция кому-нибудь пригодится.
Засим откланяюсь.

Автор: moborb