SQLite в Delphi XE3.

До выхода Delphi XE3, используя в своих проектах БД SQLite мы также должны были использовать и различные сторонние компоненты и обёртки для доступа к методам sqlite3.dll. Про эти библиотеки, компоненты и просто обёртки можно было бы в свое время написать неплохой обзор, но суть не в этом. Думаю, что не только мне хотелось, чтобы SQLite поддерживалась в Delphi, что называется «из коробки», хотелось работать с этой БД, используя обычные DataSet’ы и при этом не переплачивать за пусть и хорошие, но всё-таки «не родные» компоненты. И вот с выходом Delphi XE3 на сообщили, что в DBExpress теперь появился новый драйвер для SQLite.

В своем знакомстве с Visual LiveBindings (VLB) я уже кратко рассказывал про то как можно организовать связь с SQLite, используя Data Explorer и компоненты с вкладки dbExpress, но там речь шла не столько о самой SQLite в Delphi XE3, сколько о возможностях Visual LiveBinding для связывания таблиц БД с гридом. Сегодня речь пойдет в несколько другом направлении — теперь VLB мы оставим «на потом» и сосредоточимся именно на SQLite.

Содержание

• Настройка соединения
• Параметры соединения
• Скорость работы
• Использование PRAGMA

Cамый первый пример, который нам продемонстрировали по части работы с SQLite в Delphi XE3 выглядел следующим образом:

procedure TForm1.Button1Click(Sender: TObject);
var
  Results: TDataSet;
begin
  // Set the database.
  SQLConnection1.Params.Add('Database=full_path_of_your_sqlite_database');
  try
    // Connect to the database.
    SQLConnection1.Connected := true;
    // Activate the monitor.
    SQLMonitor1.Active := true;
    // Create and populate a table.
    PopulateTable(SQLConnection1);
    SQLConnection1.Execute('SELECT * FROM ExampleTable', nil, Results);
    // Save the trace list to a file on disk.
    SQLMonitor1.SaveToFile('D:\\Log.txt');
  except
    on E: EDatabaseError do
      ShowMessage('Exception raised with message' + E.Message);
  end;
end;
 
procedure PopulateTable(Connection: TSQLConnection);
var
  Query: String;
begin
  Query := 'CREATE TABLE ExampleTable(id INT, name VARCHAR2(50))';
  try
    Connection.Execute(Query, nil);
    Query := 'INSERT INTO ExampleTable VALUES(1,"test1")';
    Connection.Execute(Query, nil);
    Query := 'INSERT INTO ExampleTable VALUES(2,"test2")';
    Connection.Execute(Query, nil);
    Query := 'INSERT INTO ExampleTable VALUES(3,"test3")';
    Connection.Execute(Query, nil);
  except
    on E: Exception do
      ShowMessage('Exception raised with message: ' + E.Message);
  end;
end;

Описание примера можно найти на сайте Embarcadero. Суть примера простая — по клику на кнопке создается соединение с БД SQLite, в базе создается таблица ExampleTable в которую заносятся три записи. И все эти действия с БД через SQLMonitor заносятся в лог-файл. В целом пример замечательный для того, чтобы разобраться с тем как коннектиться к SQLite. Посмотрим на этот примерчик более детально.

Настройка соединения

В представленном выше примере настройка соединения осуществлялась через Object Inspector. Что необходимо иметь в виду, если нам, например, необходимо создать и настроить SQLConnection в консоли?

Для этого в uses должны быть подключены следующие модули: Data.DB, Data.SqlExpr, Data.DbxSqlite. После того, как модули подключены, нам достаточно создать новый экземпляр TSQLConnection, определить у созданного объекта свойство DriverName и задать необходимые параметры. То есть код примера можно было бы сделать и таким:

var
 FSQLiteConnection: TSQLConnection;
 
implementation
 
procedure TForm4.FormCreate(Sender: TObject);
begin
  FSQLiteConnection:=TSQLConnection.Create(self);
  FSQLiteConnection.DriverName:='Sqlite';
  //параметры соединения//
end;

а про параметры немного слов отдельно.

Параметры соединения

В представленном выше примере используется всего один обязательный параметр — Database, в котором мы указываем расположение файла БД. Как Вы наверняка знаете все параметры соединения записываются в виде пар:

Параметр=Значение

а раз Params — это простой TStringList, то мы могли бы написать и так:

SQLConnection1.Params.Values['Database']='full_path_of_your_sqlite_database';

и ничего бы а примере не поменялось. Мелочь, но лично мне так удобнее писать параметры. Наряду с этим параметром (Database) для соединения с БД SQLite можно также указать и такие:

  SQLConnection1.Params.Values['FailIfMissing']='True/False';
  SQLConnection1.Params.Values['ColumnMetaDataSupported']='True/False';

FailIfMissing — указывает будет ли вызываться исключение в случае, если файл с БД отсутствует. По умолчанию этот параметр равен True. В случае, если FailIfMissing=False, то, при отсутствии файла БД он будет создан (пустой), а исключение не возникнет.

ColumnMetaDataSupported — вообще изменять этот параметр не стоит, т.к. ColumnMetaDataSupported=True лишь говорит нам о том, что библиотека sqlite собрана с макрокомандой SQLITE_ENABLE_COLUMN_METADATA. В Windows этот параметр будет равен True, в MAC OS — False.

В итоге, полный код, демонстрирующий настройку соединения и подключение к БД можно переделать так:

var
 FSQLiteConnection: TSQLConnection;
 
implementation
 
procedure TForm4.FormCreate(Sender: TObject);
begin
  FSQLiteConnection:=TSQLConnection.Create(self);
  FSQLiteConnection.DriverName:='Sqlite';
  //параметры соединения//
  FSQLiteConnection.Params.Values['Database']='full_path_of_your_sqlite_database';
  FSQLiteConnection.Params.Values['FailIfMissing']='False'
  FSQLiteConnection.Connected:=True
end;

Но это для случаев, когда нам требуется создавать соединение вручную в run-time. Но вообще, работать с SQLConnection удобно в design-time, например, когда нам надо по-быстрому соединиться с БД, организовать связь Visual LiveBindings и протестировать получившуюся связь не собирая при этом проект. Правда, если Вы никогда ранее не работали в design-time c SQLite, то вполне возможно, что при попытке соединения Вы получите сообщение о том, что sqlite3.dll не найден. Чтобы избежать такой ситуации я, пока работаю над проектом, держу sqlite3.dll прямо в директории с исходниками, а после того как проект собран удаляю ненужную dll-ку. Кстати, FailIfMissing нормально работает и в design-time.

Скорость работы

Следующее на что необходимо обратить внимание в примере от Embarcadero — это на метод PopulateTable. Для простого примера того, что написано в методе достаточно. Но на практике так лучше всё же не записывать данные в БД не стоит и потеря скорости работы программы при таком подходе — самое лучшее, что может случиться. Не смотря на то, что официальный документ, касающийся скорости работы SQLite сейчас помечен как «очень очень старый» и вроде бы не соответствует действительности, я все же решил повторить свой печальный опыт выполнения большого количества INSERT’ов. Итак, у меня в руках самая последняя сборка sqlite3.dll, сделаем PopulateTable такой:

procedure PopulateTable(Connection: TSQLConnection);
var
  Query: String;
  iCounterPerSec: TLargeInteger;
  T1, T2: TLargeInteger;
  i:integer;
begin
  Query := 'CREATE TABLE ExampleTable(id INT, name VARCHAR2(50))';
  try
    Connection.Execute(Query, nil);
 
    QueryPerformanceFrequency(iCounterPerSec);
    QueryPerformanceCounter(T1);
 
    for I := 0 to 100 do
      begin
        Query := Format('INSERT INTO ExampleTable VALUES(%d,"test")',[i]);
        Connection.Execute(Query, nil);
      end;
 
    QueryPerformanceCounter(T2);
    ShowMessage(FormatFloat('0.0000', (T2 - T1) / iCounterPerSec) + ' сек.');
 
  except
    on E: Exception do
      ShowMessage('Exception raised with message: ' + E.Message);
  end;
end;

Результат оказался даже хуже, чем в старых версиях sqlite:

Следовательно:

Ускорить работу мы можем двумя способами. Первый известен — это использовать транзакцию, например, так:

uses Data.DBXCommon;
 
{$R *.dfm}
 
procedure PopulateTable(Connection: TSQLConnection);
var
  {....}
  Transaction: TDBXSqliteTransaction;
begin
  {....}
    Transaction:=Connection.BeginTransaction as TDBXSqliteTransaction;
    for I := 0 to 100 do
      begin
        Query := Format('INSERT INTO ExampleTable VALUES(%d,"test")',[i]);
        Connection.Execute(Query, nil);
      end;
    Connection.CommitFreeAndNil(TDBXTransaction(Transaction));
  {....}
end;

В этом случае скорость записи будет такой:

В принципе, использование транзакции уже дает ощутимый прирост в скорости. Но, начиная с версии 3.7.1.х в SQLite появилась также возможность делать и такие запросы:

INSERT INTO table (f1, f2) VALUES (1, 1), (2,2), (3,3)....

То есть мы могли бы выполнить запрос и без транзакций:

const cInsert = 'INSERT INTO ExampleTable (id, name) VALUES %s;';
{....}
   for I := 0 to 100 do
      values:=values+Format('(%d,"test"),',[i]);
    Delete(values,length(values),1);
    Query:=Format(cInsert,[values]);
    Connection.Execute(Query, nil);
{....}

Выполнили бы всего один запрос и получили бы вот такое время выполнения:

Что, в принципе, тоже не плохо по скорости.

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

При работе с SQLite Вам не всегда будет хватать тех настроек (параметров) соединения, которые сейчас предоставляет нам Delphi при настройке драйвера. Например, Вам вероятно потребуется такая опция, как автоматическое сжатие БД (auto_vacuum) или настроить размер кэша. Для этих и других опций БД в SQLite используются PRAGMA-команды.

Большинство параметров PRAGMA-команд в качестве аргумента принимает True (которое равносильно 1 yes true on) и False (0 no false off). Некоторые PRAGMA задают количество или размер чего-либо, и поэтому принимают в качестве параметров числовое значение.

В Delphi PRAGMA-команды выполняются точно также как и любый другие SQL-запросы. Например, так можно узнать размер кэша:

  FSQLiteConnection.ExecuteDirect('PRAGMA cache_size');

А так — установить размер кэша:

  FSQLiteConnection.ExecuteDirect('PRAGMA cache_size=3000');

Установить авто-сжатие базы можно так:

  FSQLiteConnection.ExecuteDirect('PRAGMA auto_vacuum=1');

Весь список PRAGMA-команд можно найти на официальном сайте SQLite. При использовании PRAGMA следует учитывать следующее:

1. Действие PRAGMA-команды распространяется только до конца текущей сессии. Т.е. если мы установили авто-сжатие SQLite сразу после подключения к БД, а потом закрыли программу, то настройка сбрасывается в дефолтное значение, т.е. False
2. Доступность каких-либо PRAGMA-команд в будущих версиях SQLite не гарантирована. Так, например, count_changes устарела и существует только для обратной совместимости
3. Неизвестные PRAGMA-команды не вызывают исключений — они просто тихо игнорируются SQLite.

В остальном же работа с SQLite в Delphi XE3 ничем (за исключением, конечно, возможностей самой SQLite) не отличается от работы с любыми другими базами данных в Delphi — вы можете использовать те же компоненты (TDataSource, TSQLDataSet, TSQLTable и т.д.) или воспользоваться возможностями компонентов Visual LiveBindings — TBindSourceDB или  TBindSourceDBX. Ну, а сам языка запросов к SQLite так же прост и понятен, как тот же FoxPro или visual foxpro поэтому разобраться с ним Вам будет очень просто, если вы имеете хотя бы начальные навыки работы с базами данных.

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

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