Sign in Join
Wiki copytemporary

Revision 1 of 3, by Slawek on 11-16-2021. The page as it is now.

SQLite/ru

Поддержка SQLite и FPC/Lazarus

SQLite - это встроенная (не серверная) однопользовательская база данных, которую можно использовать в приложениях FPC и Lazarus. Для доступа к SQLite из программ FPC/Lazarus можно использовать различные драйверы. Все драйверы нуждаются в библиотеке SQLite/dll в исполняемом каталоге (который может быть каталогом вашего проекта или, например, (projectdir)/lib/architecture/ в зависимости от настроек вашего проекта Lazarus) (и распространяется вместе с вашим исполняемым файлом) для работы.

Это также может быть необходимо включить в ваш каталог Lazarus IDE. См. ветку форума и особенно внимательно прочитайте про TSQLite3Dataset и TSQLiteDataset ниже

В большинстве дистрибутивов Linux по умолчанию установлен sqlite3 (например, libsqlite3.so.0), но для дистрибутивов Ubuntu, по крайней мере, также требуется соответствующий пакет Dev. Оба должны быть установлены через системный менеджер пакетов и помечены как зависимость, а не распространяться вместе с вашим приложением.

Win64: см. предупреждение здесь о неиспользовании определенных версий FPC/Lazarus Win64.

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

Вы можете использовать простой способ подключения SQLite к Lazarus. Компоненты называются LiteDAC. Компоненты доступа к данным SQLite (LiteDAC) - это библиотека компонентов, которая обеспечивает встроенное подключение к SQLite из Lazarus (и Free Pascal) в Windows, macOS, iOS, Android, Linux и FreeBSD как для 32-разрядных, так и для 64-разрядных платформ. LiteDAC предназначен для программистов, которые могут разрабатывать действительно кроссплатформенные настольные и мобильные приложения баз данных SQLite без необходимости развертывания каких-либо дополнительных библиотек.

Вы можете скачать пробную версию этого коммерческого продукта по адресу _https://www.devart.com/litedac/download.html (LiteDAC 4.4 for Lazarus (FreePascal))

Встроенный SQLDB

FPC/Lazarus предлагает встроенные компоненты SQLDB, которые включают поддержку баз данных SQLite (TSQLite3Connection) с вкладки SQLdb в Палитре компонентов, которые позволяют, например, создавать графические интерфейсы пользователя с такими компонентами базы данных, как TDBGrid. Преимущество использования SQLDB заключается в том, что довольно легко перейти на другую базу данных, такую ​​как Firebird или PostgreSQL, без особых изменений в программе. Подробнее см. ниже.

Поддержка Spatialite

Spatialite - это ГИС-расширения SQLite, которые можно использовать из SQLDB. См. Spatialite.

Поддержка шифрования SQLite

В последних версиях FPC (реализованных в марте 2012 г.) SQLDB включал поддержку некоторых расширенных версий SQLite3, которые шифруют файл базы данных SQLite с использованием алгоритма AES. Используйте свойство пароля, чтобы установить ключ шифрования.

Примеры:

sqlite3backup

sqlite3backup - это модуль, поставляемый с FPC (не в Lazarus, но может использоваться программно), который обеспечивает функции резервного копирования/восстановления для SQLite3. Он использует SQLDB sqlite3conn.

Zeos

Zeos

SQLitePass

Компоненты SQLitePass. Последнее обновление кода в 2010. Последняя активность на форуме в 2011.

TSQLite3Dataset и TSQLiteDataset

Существуют также отдельные пакеты TSQLiteDataset (модуль sqlite) и TSQLite3Dataset (модуль sqlite3ds); см. ниже описание того, как их использовать. Посетите домашнюю страницу sqlite4fpc, чтобы найти справочник по API и другие руководства.

TSqliteDataset и TSqlite3Dataset являются потомками TDataSet, которые обращаются к базам данных sqlite 2.8.x и 3.x.x соответственно. Для новых проектов вы, вероятно, будете использовать TSQlite3Dataset, поскольку текущая версия SQLite 3.x.

Ниже приведен список основных преимуществ и недостатков по сравнению с другими драйверами/методами доступа FPC/Lazarus SQLite:

Преимущества:

Недостатки:

Note: Учитывая вышеизложенное, многие пользователи будут использовать SQLDB или Zeos из-за преимуществ, если им не нужен низкоуровневый доступ к библиотеке SQLite.

Использование компонентов SQLdb со SQLite

Эти инструкции сосредоточены на особенностях SQLDB (TSQLite3Connection) для SQLite. Для общего обзора ознакомьтесь с SqlDB: Howto, где есть полезная информация о компонентах SQLdb.

См. Учебник1 по SQLdb для обучения создания программы с поддержкой базы данных с графическим интерфейсом пользователя, которая написана для SQLite/SQLDB (а также для Firebird/SQLDB, PostgreSQL/SQLDB, в основном любой СУБД, поддерживаемой SQLDB).

Мы будем использовать комбинацию из трех компонентов из вкладки Lazarus SQLdb: TSQLite3Connection, TSQLTransaction и TSQLQuery. TSQLQuery действует как наш TDataset; в простейшем случае это просто одна из наших таблиц. Для простоты: убедитесь, что у вас уже есть существующий файл базы данных SQLite, и вам не нужно создавать новый сейчас. TSQLite3Connection можно найти в модуле sqlite3conn, если вы хотите объявить его самостоятельно или работаете в FreePascal.

Эти три компонента связаны друг с другом как обычно: в TSQLQuery установите свойства Database и Transaction, в TSQLTransaction установите свойство Database. В компонентах Transaction и Connection особо нечего делать, большинство интересного будет сделано в TSQLQuery. Настройте компоненты следующим образом:

<b>TSQLite3Connection</b>:

Note: Чтобы установить полный путь к библиотеке (если вы поместите свою sqlite dll/so/dylib в место, где ОС ее не найдет, например каталог приложения в Linux/OSX), вы можете установить «SQLiteLibraryName» свойство (ДО того, как будет установлено какое-либо соединение, например, в событии OnCreate главной формы)

, например:

pascal
SQLiteLibraryName:='./sqlite3.so';

<b>TSQLQuery</b>:

После того, как все вышеперечисленное будет настроено правильно, вы сможете использовать TSQLQuery, как и любой другой TDataset, либо программно манипулируя его данными, либо помещая TDatasouce в форму, подключая его к TSQLQuery, а затем используя контуры данных, такие как TDBGrid и т.п.

Creating a Database

Метод TSQLite3Connection.CreateDB, унаследованный от родительского класса, на самом деле ничего не делает; чтобы создать базу данных, если файл еще не существует, вам просто нужно записать данные таблицы, как в следующем примере:

(Код, извлеченный из примера sqlite_encryption_pragma, который поставляется с Lazarus 1.3 и новее)

pascal
var
  newFile : Boolean;
begin

  SQLite3Connection1.Close; // Убедитесь, что соединение закрыто при запуске

  try
  //Поскольку мы делаем эту базу данных впервые,
  // проверяем, существует ли уже файл    
  newFile := not FileExists(SQLite3Connection1.DatabaseName);

    if newFile then
    begin
      // Создаем базу данных и таблицы
      try
        SQLite3Connection1.Open;
        SQLTransaction1.Active := true;

        // Здесь мы настраиваем таблицу с именем "DATA" в новой базе данных.
        SQLite3Connection1.ExecuteDirect('CREATE TABLE "DATA"('+
                    ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
                    ' "Current_Time" DateTime NOT NULL,'+
                    ' "User_Name" Char(128) NOT NULL,'+
                    ' "Info" Char(128) NOT NULL);');

        // Создание индекса на основе идентификатора в таблице "DATA"
        SQLite3Connection1.ExecuteDirect('CREATE UNIQUE INDEX "Data_id_idx" ON "DATA"( "id" );');

        SQLTransaction1.Commit;

        ShowMessage('База данных успешно создана.');
      except
        ShowMessage('Невозможно создать новую базу данных');
      end;
    end;
  except
    ShowMessage('Невозможно проверить, существует ли файл базы данных');
  end;
 end;

Создание определяемых пользователем порядка сортировки

pascal
// utf8-callback функция сравнения с учетом регистра
function UTF8xCompare(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
var S1, S2: AnsiString;
begin
  SetString(S1, data1, len1);
  SetString(S2, data2, len2);
  Result := UnicodeCompareStr(UTF8Decode(S1), UTF8Decode(S2));
end;

// utf8-callback функция сравнения без учета регистра
function UTF8xCompare_CI(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
var S1, S2: AnsiString;
begin
  SetString(S1, data1, len1);
  SetString(S2, data2, len2);
  Result := UnicodeCompareText(UTF8Decode(S1), UTF8Decode(S2));
end;

// регистрируем порядок сортировки с помощью SQLite3 API (требуется модуль sqlite3dyn):
sqlite3_create_collation(SQLite3.Handle, 'UTF8_CI', SQLITE_UTF8, nil, @UTF8xCompare_CI);
// или используем метод TSQLite3Connection:
CreateCollation('UTF8_CI',1,nil,@UTF8xCompare_CI);  

// теперь мы можем использовать порядок сортировки без учета регистра в SQL, например:
// SELECT * FORM table1 WHERE column1 COLLATE UTF8_CI = 'á'

// но это не работает для оператора LIKE
// чтобы поддерживать также оператор LIKE, мы должны перегрузить функцию LIKE по умолчанию, используя sqlite3_create_function ()
// http://www.sqlite.org/lang_corefunc.html#like

Создание пользовательских функций (UDF)

pascal
// пример перегрузки функции LOWER() по умолчанию на функцию, предоставляемую пользователем
// чтобы запустить эту демку, вы должны добавить модули sqlite3dyn и ctypes в ваше предложение uses
// и добавить константу 'SQLITE_DETERMINISTIC' со значением $800

procedure UTF8xLower(ctx: psqlite3_context; N: cint; V: ppsqlite3_value); cdecl;
var S: AnsiString;
begin
  SetString(S, sqlite3_value_text(V[0]), sqlite3_value_bytes(V[0]));
  S := UTF8Encode(AnsiLowerCase(UTF8Decode(S)));
  sqlite3_result_text(ctx, PAnsiChar(S), Length(S), sqlite3_destructor_type(SQLITE_TRANSIENT));
end;

// регистрируем функцию LOWER() с помощью SQLite3 API (требуется модуль sqlite3dyn):
sqlite3_create_function(SQLite3.Handle, 'lower', 1, SQLITE_UTF8 or SQLITE_DETERMINISTIC, nil, @UTF8xLower, nil, nil);

SQLite3 и даты

Значения по умолчанию для местного времени вместо UTC

CURRENT_TIME, CURRENT_DATE и CURRENT_TIMESTAMP возвращают текущую дату и/или время в формате UTC. Для местной даты и/или времени мы можем использовать:

 DEFAULT (datetime('now','localtime')) for datetime values formated YYYY-MM-DD HH:MM:SS
 DEFAULT (date('now','localtime')) for date value formated YYYY-MM-DD
 DEFAULT (time('now','localtime')) for time value formated HH:MM:SS

Устранение неполадок SQLDB и SQLite

pascal
interface
    procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload;
    procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);overload;  
 
implementation
 
procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);
// Эта процедура обновляет набор данных и помещает курсор на последнюю запись
// Используется, если Dataset не гарантирует сортировку набора данных по первичному ключу автоинкремента
var
  vLastID: Integer;
  vUpdateStatus : TUpdateStatus;
begin
  vUpdateStatus := pDataset.UpdateStatus;
  //Получаем последний вставленный ID в базе данных 
  pDataset.ApplyUpdates;
  vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
  //Теперь возвращаемся к соответствующей строке
  if vUpdateStatus = usInserted then begin
    pDataset.Refresh;
    //Обновляемся и возвращаемся к соответствующей строке
    pDataset.Locate(pKeyField,vLastID,[]);
  end;
end;
 
procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);
//Эта процедура обновляет набор данных и помещает курсор на последнюю запись
//Используется только в том случае, если DataSet гарантированно отсортирован по первичному ключу с автоинкрементом.
var
  vLastID: Integer;
  vUpdateStatus : TUpdateStatus;
begin
  vUpdateStatus := pDataset.UpdateStatus;
  pDataset.ApplyUpdates;
  vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
  if vUpdateStatus = usInserted then begin
    pDataset.Refresh;
    //Опасно!
    pDataSet.Last;
  end;
end;

procedure TDataModule1.SQLQuery1AfterPost(DataSet: TDataSet);
begin
  RefreshADatasetAfterInsert(Dataset as TSQLQuery); //Если ваш набор данных отсортирован по первичному ключу
end;  

procedure TDataModule1.SQLQuery2AfterPost(DataSet: TDataSet);
begin
  RefreshADatasetAfterInsert(Dataset as TSQLQuery, 'ID'); //если вы не уверены, что набор данных всегда сортируется по первичному ключу
end;

Vacuum и другие операции, которые необходимо выполнять вне транзакции

Кажется, что SQLDB всегда требует подключения, но некоторые операции, такие как Pragma и Vacuum, должны Быть сделано вне транзакции. Хитрость заключается в том, чтобы завершить транзакцию, выполнить то, что вы должны, и начать транзакцию еще раз (чтобы не запутать sqldb):

pascal
  // коммит любых ожидающих операций или использование «свежего» соединения sql
  Conn.ExecuteDirect('End Transaction');  // Завершение транзакции, начатой SQLdb
  Conn.ExecuteDirect('Vacuum');
  Conn.ExecuteDirect('Begin Transaction'); //Запуск транзакции для использования SQLdb

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

В этом разделе подробно описано, как использовать компоненты TSQLite2Dataset и TSQLite3Dataset для доступа к базам данных SQlite (от Luiz Américo luizmed(at)oi(dot)com(dot))br

Требования

Перед запуском проекта lazarus убедитесь, что:

Как использовать (базовое использование)

Установить пакет, который находится в каталоге /components/sqlite (см. инструкции здесь) (прим.перев.: на момент перевода статьи это можно сделать через стандартный установщик пакетов Лазаруса из меню Package(Пакеты) --> Install/Uninstall Package (Установка/Удаление пакетов) --> справа в окне Available for installation (Доступные для установки) выбираем sqlite3laz 0.4 --> ниже жмем кнопку Install Selection (Установить выбранное) --> жмем в самом низу кнопку Save and rebuild IDE (Сохранить и пересобрать IDE)).

Во время разработки установите следующие свойства:

Создание Table (Dataset)

Дважды щелкните значок компонента или используйте пункт 'Create Table'(Создать таблицу) во всплывающем меню, которое появляется при нажатии правой кнопки мыши. Будет показан простой редактор таблиц с пояснениями.

Вот все типы полей, поддерживаемые TSqliteDataset и TSqlite3Dataset:

Получение данных

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

pascal
SQL := 'Select * from TABLENAME';

Применение изменений к базовому файлу данных

Чтобы использовать функцию ApplyUpdates, набор данных должен содержать по крайней мере одно поле, которое удовлетворяет требованиям для первичного ключа (значения должны быть UNIQUE, а не NULL)

Сделать это можно двумя способами:

Если установлено одно из двух условий, просто вызовите

pascal
ApplyUpdates;

Note: Если заданы оба условия, поле, соответствующее PrimaryKey, используется для применения обновлений.

Note: Установка PrimaryKey в поле, которое не является первичным ключом, приведет к потере данных при вызове ApplyUpdates, поэтому перед его использованием убедитесь, что выбранное поле не содержит значений Null и Unique.

Пример Master/detail

Различные примеры отношений master/detail (например, отношения между клиентом и заказами):

Примечания

Общие примеры можно найти в SVN каталоге fpc/fcl-db/src/sqlite.

See also

From the Free Pascal wiki, page "SQLite/ru", as it stood on 2021-11-16 after a change by Slawek, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's Licensing page. If you have concerns about this material, message the admin.