SQLite в Delphi XE3 #2.

В прошлый раз, когда я затрагивал тему работы с SQLite в Delphi XE3 мы остановились на том, что затронули тему использования PRAGMA для настройки нашей БД. После этого мне ещё несколько раз доводилось работать с SQLite и надо сказать, что мое отношение к SQLite нисколько не изменилось — отличная база данных, в особенности для тех, кому как и мне не требуется использования шифрования или чтобы БД выдерживала  какие-то запредельные нагрузки (не буду утверждать, что SQLite все выдержит т.к. не имел возможности проверить это на собственно опыте). Маленькая, шустрая, встраиваемая база данных — что ещё нужно для счастья простому разработчику? :) Как известно, начиная с RAD Studio XE3 SQLite официально поддерживается в Delphi и для работы с этой базой данных нам необходимо использовать DBExpress. Надо сказать, что мой переход со сторонней обертки для SQLite к DBExpress был вполне легким — никаких особых нареканий или, не дай бог, ошибок в работе обнаружено не было, но в отдельных местах кода пришлось немного покопаться по внутренностям DBExpress. И сегодня я немного расскажу о паре мелочей, которые могут пригодится вам при использовании SQLite в Delphi XE3. 

Итак, что имеется в наличии. В наличии есть готовая небольшая база данных SQLite, которая содержит пару таблиц с данными. Кодировка БД — UTF-8.

Первый момент — работа с датой и временем.

Таблица БД содержит следующие поля:

Таблица Person

Для создания такой таблицы можно использовать следующий запрос:

CREATE TABLE [person] (
  [id] INTEGER PRIMARY KEY, 
  [pName] TEXT, 
  [pFamily] TEXT, 
  [pBirthday] DATE);

Как видите, поле pBirthday имеет тип дата. В SQLite дата всегда пишется в формате YYYY-MM-DD.

Попробуем записать в нашу таблицу какие-нибудь данные, используя для этого методы TSQLConnection. Для примера воспользуемся запросом с параметрами, как это показано в официальной wiki Embarcadero. Т.е. INSERT у нас должен по идее быть таким таким:

procedure InsertPerson(const AName, AFamily: string; ABithday: TDate);
const
  cInsertQuery = 'INSERT INTO person (pName, pFamily, pBirthday) VALUES (:name, :family, :birthday)';
var Params: TParams;
begin
  Params:=TParams.Create;
  try
    Params.CreateParam(TFieldType.ftString,'name',ptInput);
    Params.ParamByName('name').AsString:=AName;
    Params.CreateParam(TFieldType.ftString,'family',ptInput);
    Params.ParamByName('family').AsString:=AFamily;
    Params.CreateParam(TFieldType.ftDate,'birthday',ptInput);
    Params.ParamByName('birthday').AsDate:=ABithday;
    SQLConnection1.Execute(cInsertQuery,Params,nil)
  finally
    Params.Free;
  end;
end;

Но DBExpress нам недвусмысленно скажет:

И, по большому счёту, DBExpress будет прав — нет у SQLite типа DATA. Поэтому, если нам необходимо записать в БД SQLite какую-либо дату, то сделать это можно, указав тип данных в параметре ftString, предварительно, естественно, переконвертировав эту дату в string в правильном формате:

procedure InsertPerson(const AName, AFamily: string; ABithday: TDate);
const
  cInsertQuery = 'INSERT INTO person (pName, pFamily, pBirthday) VALUES (:name, :family, :birthday)';
var Params: TParams;
    FS: TFormatSettings;
begin
  Params:=TParams.Create;
  try
    Params.CreateParam(TFieldType.ftString,'name',ptInput);
    Params.ParamByName('name').AsString:=AName;
    Params.CreateParam(TFieldType.ftString,'family',ptInput);
    Params.ParamByName('family').AsString:=AFamily;
    Params.CreateParam(TFieldType.ftString,'birthday',ptInput);
    //записываем строковый параметр с датой
    FS.ShortDateFormat:='YYYY-MM-DD';
    FS.DateSeparator:='-';
    Params.ParamByName('birthday').AsString:=DateToStr(ABithday,FS);
    SQLConnection1.Execute(cInsertQuery,Params,nil)
  finally
    Params.Free;
  end;
end;

Так запись с датой будет успешно добавлена в таблицу. Вообще, что касается работы с параметрами запросов SQLite в Delphi, то мы можем передавать в параметрах числа (integer, single, double и т.д.), строки (string, ansistring и т.д.), boolean (при этом при передаче параметра в SQLite будет использован тип integer), пустой параметр (null) и массивы байтов. При этом все связывания основаны на следующих методах SQLite:

sqlite3_bind_blob
sqlite3_bind_double
sqlite3_bind_int
sqlite3_bind_int64
sqlite3_bind_null
sqlite3_bind_text
sqlite3_bind_text16
sqlite3_bind_value
sqlite3_bind_zeroblob
sqlite3_blob_bytes

Было б, конечно, неплохо, если бы тот же тип TDate в параметрах передавался с использованием sqlite3_bind_text (как показано выше — преобразования нужны минимальные), но, пока что есть — то есть.

Двигаемся далее. Раз уж речь в предыдущем примере касалась INSERT’ов, то про них родимых и продолжим. Думаю, что нередко при работе с БД (любыми, не только SQLite) может возникать потребность в том, чтобы узнать идентификатор последней добавленной записи в БД. В обертке о которой я рассказывал, ещё работая в Delphi 2010, для этого случая был предусмотрен специальный метод. В DBExpress я пока такого метода не обнаружил. Можно было бы, конечно, втупую после каждого INSERT выполнить ещё один запрос в котором получить значение MAX(id) и, в принципе, это бы сработало, т.к. поле id у нас PRIMARY KEY и в SQLite наращивается автоматически. Но есть и другой способ — использовать метод sqlite3.dll, который так и называется sqlite3_last_insert_rowid.

В SQLite каждая запись БД содержит свой уникальный ключ (rowid) и метод sqlite3_last_insert_rowid как раз и предназначена для того, чтобы этот самый rowid возвращать. Однако, если в таблице есть поле PRIMARY KEY, то sqlite3_last_insert_rowid будет возвращать значение этого поля. У нас такое поле имеется и осталось только правильно вызвать необходимый нам метод.

Как можно вызывать методы SQLite3.dll? Во-первых, для этого нам потребуется подключить в uses модуль System.Sqlite для того, чтобы получить доступ к методам sqlite3.dll.

Посмотрим, что нам надо, чтобы вызвать необходимый нам метод:

function sqlite3_last_insert_rowid(DbConnection: sqlite3): sqlite3_int64; cdecl;
{$EXTERNALSYM sqlite3_last_insert_rowid}

Здесь тип sqlite3 есть ни что иное как обычный указатель:

type
  sqlite3 = Pointer;

Откуда этот указатель можно получить? в модуле Data.DbxSqlite можно обнаружить такой тип:

TDBXSqliteConnection = class(TDBXConnection)
  private
    FConnectionHandle: sqlite3;
    FTransactionId: Integer;
  protected
    [...]
  public
    [...]
    property ConnectionHandle: sqlite3 read FConnectionHandle;
  end;

У компонента TSQLConnection есть соответствующее свойство:

 property DBXConnection: TDBXConnection read FDBXConnection;

Все, что нам необходимо это сделать, например, так:

var LastID:integer;
 HSqlite: pointer;
begin
    [...]
    HSqlite:=TDBXSqliteConnection(SQLConnection1.DBXConnection).ConnectionHandle;
    LastID:=sqlite3_last_insert_rowid(HSqlite);
    ShowMessage(IntToStr(LastID));
end;

Аналогичным образом можно вызывать и другие методы sqlte3.dll для работы с базой данных, например, запрашивать общее количество изменений в базе, получать версию SQlite и т.д. Остается только отметить, что метод sqlite3_last_insert_rowid, несмотря на свою полезность имеет несколько ограничений с которыми Вы всегда можете ознакомиться на официальном сайте SQLite.

В целом, как я и говорил в начале статьи, переход к DBExpress для работы с SQLite был вполне себе комфортным и безболезненным. И могу сказать, что пока для моих целей DBExpress вполне хватает.

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

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