SQLite в Delphi. Обзор LiteDAC.

Про SQLite в Delphi я уже рассказывал в блоге достаточно много. А, учитывая то, что в сентябре нас ждет незабываемое приключение под названием «Разработка приложений для Android в Delphi» тема работы с SQLite в Delphi становится ещё более актуальной, т.к. в Android сплошь и рядом используется именно SQLite. В основном я ограничивался тем, что рассматривал либо бесплатные open source проекты типа вот этого, либо те возможности, которые уже имеются в самой Delphi, например, работу с SQLite в dbExpress. Сравнительно недавно, а именно 5 февраля, Embarcadero объявила о покупке AnyDAC — библиотеке, которая ныне носит «огненное» название FireDAC и также позволяет работать с SQLite. Но о работе с FireDAC Вам расскажет Александр Божко в своем блоге, а я постараюсь сделать небольшой обзор по компонентам от Devart для работы специально с SQLite — LiteDAC или, если называть эту библиотеку полностью, то о SQLite Data Access Components. Сразу скажу, что LiteDAC — это платные компоненты и рассматривать я буду LiteDAC Professional Edition v.2.0.1 в Delphi XE3. Но прежде всего несколько слов о линейке продуктов о Devart и почему именно LiteDAC попал в поле моего зрения.

Итак, основной (если я не ошибаюсь) продукт Devart для доступа к базам данных в Delphi носит название UniDAC. В состав UniDAC входят компоненты доступа к таким базам данных как Oracle, Microsoft SQL Server, MySQL, InterBase, Firebird, PostrgeSQL, SQLite, DB2, Microsoft Access, Advantage Database Server, Adaptive Server Enterprise и т.д. Для ознакомления с этим продуктом Вы можете бесплатно скачать UniDAC с официального сайта 30-ти дневный триал.

Кроме UniDAC Devart также предлагает ряд продуктов, которые «заточены» под работу с конкретной СУБД. Так, например, LiteDAC — это библиотека наиболее полно реализующая работу с SQLite, ODAC «специализируется» на Oracle, PgDAC — на PostgreSQL и т.д. Так как мне в 99% случаев приходится иметь дело с SQLite (1% спишем на MySQL, с которым работал лет эдак 10 назад), то я решил испытать в действии работу именно LiteDAC. Всё-таки UniDAC — это универсальное решение, а мне хотелось бы получить в свое распоряжение компоненты под работу с конкретной СУБД.

Итак, посмотрим какие компоненты входят в состав LiteDAC.

Состав LiteDAC

В базовой версии LiteDAC (Basic Edition) Вы сможете воспользоваться следующими компонентами для доступа к SQLite:

	TLiteConnection	Компонент для создания подключения к БД SQLite.
	TLiteQuery	Компонент для выполнения запросов и работы наборами данных.
	TLiteSQL	Компонент для выполнения SQL-запросов, которые не возвращают наборы данных
	TLiteTable	Компонент позволяет получать и изменять данные в одной таблице БД без написания SQL-запросов
	TLiteUpdateSQL	Компонент позволяет настроить операции обновления для наборов данных.
	TLiteDataSource	Компонент для создания связей между компонентами LiteDAC, содержащими наборы данных и элементами управления на форме.
	TLiteScript	Компонент для выполнения последовательности SQL-запросов
	TLiteSQLMonitor	Компонент для мониторинга выполнения SQL-запросов к БД SQLite
	TLiteConnectDialog	Компонент для создания пользовательских диалогов для запроса логинов/паролей и ключей шифрования БД.
	TVirtualTable	Компонент для хранения наборов данных в памяти.

Профессиональная версия LiteDAC (Professional Edition) расширяется следующими компонентами

	TLiteUserFunction	Компонент для определения пользовательских функций для будущего использования в SQL-запросах
	TLiteLoader	Компонент обеспечивает быструю загрузку внешних данных в базу
	TLiteDump	Компонент обеспечивает создание дампов для базы данных или отдельных её частей и восстановления БД из этих дампов.
	TLiteMetaData	Компонент для извлечения мета-данных из базы данных
	TLiteEncryptor	Компонент для шифрования/дешифрования базы данных SQLite
	TCRBatchMove	Компонент обеспечивает обмен данными между всеми потомками TDataSet.

Что можно сказать после предварительного обзора набора компонентов LiteDAC Professional Edition? Радует, что в составе компонентов имеются компоненты для шифрования БД SQLite и удобного создания дампов. По-моему, возможность шифрования БД прямо «из коробки» достаточно большой плюс LiteDAC. Теперь посмотрим как работать с LiteDAC.

Практика использования LiteDAC

Использование TLiteConnection

Работа в режиме клиента

По умолчанию LiteDAC, как и большинство других библиотек для работы с SQLite использует клиентскую библиотеку SQLite:

• sqlite3.dll в Windos
• libsqlite3.dylib в MacOS
• libsqlite3.so в Linux

Такой режим работы с SQLite называется «подключение в режиме клиента» (connecting in Client mode). Это обычный и наиболее распространенный способ работы с SQLite. Все методы SQLite API хранятся во внешней библиотеке и, поэтому наше приложение «весит» меньше. Однако подобный способ работы с БД требует обязательного наличия динамической библиотеки на компьютере пользователя. Однако, подобный подход в работе может привести либо к несовместимости вашего приложения с уже имеющимися библиотеками SQLite на целевой платформе, либо вызовет необходимость вместе с приложением распространять и свою библиотеку для работы SQlite. Ради эксперимента, я провел поиск на своем компе поиск sqlite3.dll и обнаружил 12 различных копий библиотеки из которых только 2 относились непосредственно к моим Delphi-приложениям, а остальные — к прочим установленным на компьютере программам. Если Вас не смущает то, что вместе с Вашей программой надо будет таскать и библиотеки с SQLite API, то можете не задумываясь использовать этот режим работы с SQLite.

Прямой доступ к SQLite

LiteDAC Professional Edition включает в себя возможность подключения к SQLite напрямую, используя встроенный движок для работы с SQLite3. Такой режим работы называется режимом прямого доступа (Direct Mode). В чем преимущества прямого доступа? Во-первых, отпадает необходимость использовать sqlite3.dll, что, в свою очередь, решает проблему совместимости. Во-вторых, в режиме прямого доступа мы получаем возможность использовать шифрование БД SQLite, что, как я уже говорил выше, является большим плюсом (по крайней мере для меня).  Ну, а недостатком работы в режиме прямого доступа, как Вы догадываетесь, является то, что приложение в итоге будет «весить» больше, нежели при работе в режиме клиента (рост составит порядка 350 КБ).

Так как с первым режимом работы с SQLite (с использованием sqlite3.dll) я уже рассказывал кучу раз, то рассмотрим прямой доступ к БД.

В качестве примера я создал небольшую БД, содержащую информацию по различному «железу»: название, цена, основные характеристики. Сама таблица БД получилась такой:

CREATE TABLE [hardware] (
 [h_id] INTEGER PRIMARY KEY AUTOINCREMENT, 
 [h_name] TEXT, 
 [h_price] DOUBLE, 
 [h_charact] TEXT);

Теперь создадим новое подключение к базе данных в режиме прямого доступа. Создаем в Delphi новый проект и бросаем на форму компонент TLiteConnection.

Двойной клик по TLiteConnection откроет окно для создания и настройки нового подключения:

По умолчанию нам недоступно шифрование БД (т.к. используется режим клиента) и нам необходимо указать два значения: путь к самой базе данных и путь к динамической библиотеке. Мы же будем использовать Direct Mode, поэтому указываем путь к БД и ставим галочку «Direct»:

Что касается шифрования БД, то здесь имеется небольшой нюанс — т.к. нет никаких строгих требований к шифрованию данных в SQLite, то LiteDAC использует свои решения, которые не совместимы ни с какими другими. Поэтому использовать шифрование мы может исключительно в базах SQLite, созданных непосредственно с использованием LiteDAC. В свою очередь, никакие сторонние приложения не смогут использовать нашу базу данных без использования LiteDAC. Т.к. наша тестовая база была создана в SQLite Expert’е, то шифровать мы её пока не можем, поэтому будем работать с ней как есть, а шифрование рассмотрим чуть позже.

Итак, прямое подключение к БД создано, никаких DLL нам не потребовалось. Двигаемся далее. Для начала попробуем всё-таки создать зашифрованную копию БД. Благо, в профессиональной версии LiteDAC это можно сделать очень удобно — у нас же есть замечательный компонент TLiteDump.

Использование TLiteDamp и шифрование базы данных SQLite

Как я уже говорил выше, TLiteDump используется для создания дампов БД. Бросаем на форму этот компонент и кнопку TButton клик по которой и будет приводить к созданию новой копии, но уже зашифрованной базы данных.

Для работы TLiteDump’у необходимо указать соединение (TLiteConnection) с которым необходимо работать. Указываем в свойстве Connection LiteConnection1, в свойстве TableNames указываем название нашей таблицы — «hardware» а остальные свойства компонента оставляем как есть (некоторые из них мы рассмотрим чуть позже).

Для работы с новой зашифрованной базой используем ещё один компонент TLiteConnection. Поэтому бросаем ещё один компонент на форму и устанавливаем у него таки свойства:

• Options.EncriptionAlgorithm = laBlowfish
• Options.ForceCreateDatabase:=True

То есть для шифрования мы будем использовать алгоритм Blowfish и создавать новый файл БД, если указанный в настройках соединения файл будет отсутствовать.

Теперь пишем такой обработчик OnClick кнопки:

var DBStream: TMemoryStream;
begin
LiteConnection1.Open;//открываем соединение с незашифрованной БД
DBStream:=TMemoryStream.Create;
try
  LiteDump1.BackupToStream(DBStream);//создаем дамп и сохраняем его в поток
  LiteConnection2.Database:=ExtractFilePath(Application.ExeName)+'encrypted_db.db3';//указываем путь к БД
  LiteConnection2.EncryptionKey:='masterkey';//указываем ключ для шифрования
  LiteConnection2.Open;//открываем соединение
  //восстанавливаем БД из дампа
  LiteDump1.Connection:=LiteConnection2;
  LiteDump1.RestoreFromStream(DBStream);
finally
  DBStream.Free;
end;
end;

Проверяем. Запускаем приложение, кликаем по кнопке и смотрим, что в итоге создалось в директории с программой. Вот как выглядит незашифрованный файл БД SQLite с которым у нас работает компонент LiteConnection1:

А вот новый зашифрованный файл с которым работает LiteConnection2:

Как видите, даже по этим «кракозябрам» в файлах видно, что они (файлы) различаются и второй файл зашифрован. Следовательно цель шифрования БД достигнута и при этом мы не написали ни одного SQL-запроса — вся структура БД создалась из дампа.  Кстати, что касается других свойств TLiteDump, не используемых в примере выше. Компонент TLiteDump содержит следующие свойства:

К базе подключились, дамп создали, из дампа базы восстановили и зашифровали. Теперь попробуем поработать с зашифрованной базой и что-нибудь в неё записать.

Использование TLiteSQL для записи данных в базу SQLite

Для начала воспользуемся компонентом TLiteSQL. Это самый простой способ для записи данных в базу. Добавим на форму ещё одну кнопку и напишем для неё такой обработчик:

var iCounterPerSec: TLargeInteger;
    T1, T2: TLargeInteger;
    i:integer;
begin
    QueryPerformanceFrequency(iCounterPerSec);
    QueryPerformanceCounter(T1);
    for I := 0 to 100 do
      begin
        LiteSQL1.SQL.Text:=Format('INSERT INTO hardware VALUES(%d, "test", %d, "test")',[i,i]);
        LiteSQL1.Execute;
      end;
    QueryPerformanceCounter(T2);
    ShowMessage('На запись 101 элемента было потрачено '+FormatFloat('0.0000', (T2 - T1) / iCounterPerSec) + ' сек.');
end;

В итоге, выполнив этот код я получил сообщение о том, что на запись 100 элементов было затрачено чуть больше 8 секунд. Ранее, когда я таким же образом тестировал работу с dbExpress, то получал значение около 11 секунд. Из-за чего SQLite работает так медленно я уже тоже говорил (надо использовать транзакции), поэтому сильно повторяться не буду. Лучше рассмотрим как использовать в TLiteSQL параметры.

Заходим в Object Inspector и жмем ссылочку «LiteSQL Editor»:

Перед нами откроется окно настройки свойств компонента. Вначале заходим на вкладку SQL и пишем такой SQL-запрос:

INSERT INTO hardware (h_name, h_price, h_charact) VALUES(:name, :price, :charact)

Теперь переходим на вкладку Parameters и видим, что компонент уже разобрал все наши параметры и нам остается только указать их свойства:

что мы и сделаем. На рисунке ниже показаны свойства параметра price:

Теперь возвращаемся в Delphi и переписываем обработчик кнопки следующим образом:

for I := 0 to 100 do
  begin
    LiteSQL1.Params.ParamByName('name').AsString:='test';
    LiteSQL1.Params.ParamByName('charact').AsString:='test';
    LiteSQL1.Params.ParamByName('price').AsFloat:=I;
    LiteSQL1.Execute;
  end;

Результат работы будет абсолютно тот же — в базу запишется 101 запись, но при этом код программы уже не содержит никаких SQL запросов, а вся работа ведется с использованием параметров.

Теперь попробуем использовать транзакции, чтобы уже поставить точку на записи данных в БД. Использовать транзакции в LiteDAC проще простого. Вот, например, как можно ускорить выполнение представленного выше кода с использованием транзакций:

LiteConnection2.StartTransaction;
    try
    for I := 0 to 100 do
      begin
        LiteSQL1.Params.ParamByName('name').AsString:='test';
        LiteSQL1.Params.ParamByName('charact').AsString:='test';
        LiteSQL1.Params.ParamByName('price').AsFloat:=I;
        LiteSQL1.Execute;
      end;
      LiteConnection2.Commit;
    except
      LiteConnection2.Rollback;
    end;

Теперь запись 101 элемента занимает всего чуть больше 0,1 секунды (аналогичную скорость показывал и dbExpress).

Пожалуй для первого раза работы с LiteDAC информации достаточно. Итак, что мы сегодня рассмотрели:

1. Подключение к БД SQLite с помощью TLiteConnection
2. Создание дампов и восстановление БД с помощью TLiteDump
3. Шифрование БД SQLite
4. Запись в БД данных с помощью LiteSQL и использование параметров в запросах
5. Использование транзакций при работе с LiteDAC

Это, конечно же, очень малая часть того, что позволяет нам сделать LiteDAC, но на сегодня, пожалуй, будет достаточно. В целом же библиотека оставляет очень положительное впечатление — все реализована очень удобно и понятно. В общем, могу сказать, что время затраченное на изучение LiteDAC было потрачено совсем не зря.

Книжная полка

	Автор: Дмитрий Осипов Название:Базы данных и Delphi. Теория и практика Описание Книга основана на материалах лекций и практических занятий, разработанных автором, и объединяет теоретические основы и практические аспекты разработки реляционных баз данных.	Купить на ЛитРес	383 руб.
	Автор: Анатолий Хомоненко, Владимир Гофман Название:Работа с базами данных в Delphi Описание: Рассматривается использование средств Delphi для разработки приложений баз данных. Даются понятия баз данных, характеризуются элементы и описываются этапы проектирования реляционных баз данных, изложена технология разработки информационных систем, освещаются приемы работы с данными, создание таблиц и приложений баз данных, подготовка отчетов.	Купить на ЛитРес	151 руб.