Учим SQLite работать с локализированным календарем
Часть1 — Английская версия, Часть2
Недавно мне довелось портировать (точнее, переписывать) под iOS приложение для анализа посещаемости сайта. Поскольку данные подходили под реляционную модель, мы решили использовать SQLite. Насколько я знаю, других реляционных СУБД под iOS просто нет.
Долго ли, коротко ли… Пришло время подсчитать понедельный КПД для определенного диапазона дат.
Итак, имеем таблицу, которая содержит значения полезной и затраченной работы, а также даты исполнения оной работы. Структура таблица описывается на языке SQL следующим образом:
CREATE TABLE [Usage]
(
[FacetId] VARCHAR, -- "исполнитель"
[Value ] INTEGER, -- полезная "работа"
[Visits ] INTEGER, -- затраченная "работа"
[Date ] DATETIME -- дата
);
* This source code was highlighted with Source Code Highlighter.Необходимо для некоторого диапазона дат посчитать КПД для каждой недели. Ладно, написали запрос
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.Однако результаты почему-то не сошлись с эталонной реализацией. Выяснилось следующее. 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.Способа принудительно указать СУБД локаль я не нашел. Разбивать красивый запрос на несколько очень не хотелось. К тому же, я обнаружил возможность добавлять функции в 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.Отличие состоит в том, что она не имеет флага возврата. Вместо этого в нее передается контекст базы данных, из которой она была вызвана. Этот контекст используется для возврата результата либо ошибки.
В 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.Таким образом, нам необходимо сделать 4 вещи:
- Зарегистрировать функцию в SQLite дабы ее можно было использовать в запросах.
- Преобразовать параметры из argv_ в Foundation типы. В нашем случае это будут [ NSString, NSDate, NSString ] соответственно.
- Провести форматирование даты с помощью NSDateFormatter
- Вернуть результат
==============
0. Регистрируем SQLite функцию
Это делается с помощью 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. Преобразование параметров
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.Однако есть некоторые нюансы.
- Как известно, экземпляр класса 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
Code review и Pull request приветствуются.
Надеюсь, моя функция кому-нибудь пригодится.
Засим откланяюсь.