SQLite/ru

This is an old revision of SQLite/ru, edited by Zoltanleo at . The current revision is 142121.

/* Creating user defined functions */

Databases portal

References:

Tutorials/practical articles:

Databases

Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

Поддержка 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:

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

Недостатки:

Учитывая вышеизложенное, многие пользователи будут использовать 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. Настройте компоненты следующим образом:

TSQLite3Connection:

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

, например:

SQLiteLibraryName:='./sqlite3.so';

TSQLQuery:

SELECT * FROM foo

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

Creating a Database

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

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

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;

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

// 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)

// пример перегрузки функции 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 and Dates

http://www.sqlite.org/datatype3.html#datetime.

Default values in local time instead of UTC

CURRENT_TIME, CURRENT_DATE and CURRENT_TIMESTAMP return current UTC date and/or time. For local date and/or times we can use:

 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 And SQLite troubleshooting

interface
    procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload;
    procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);overload;  
 
implementation
 
procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);
//This procedure refreshes a dataset and positions cursor to last record
//To be used if Dataset is not guaranteed to be sorted by an autoincrement primary key
var
  vLastID: Integer;
  vUpdateStatus : TUpdateStatus;
begin
  vUpdateStatus := pDataset.UpdateStatus;
  //Get last inserted ID in the database
  pDataset.ApplyUpdates;
  vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
  //Now come back to respective row
  if vUpdateStatus = usInserted then begin
    pDataset.Refresh;
    //Refresh and go back to respective row
    pDataset.Locate(pKeyField,vLastID,[]);
  end;
end;
 
procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);
//This procedure refreshes a dataset and positions cursor to last record
//To be used only if DataSet is guaranteed to be sorted by an autoincrement primary key
var
  vLastID: Integer;
  vUpdateStatus : TUpdateStatus;
begin
  vUpdateStatus := pDataset.UpdateStatus;
  pDataset.ApplyUpdates;
  vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
  if vUpdateStatus = usInserted then begin
    pDataset.Refresh;
    //Dangerous!
    pDataSet.Last;
  end;
end;

procedure TDataModule1.SQLQuery1AfterPost(DataSet: TDataSet);
begin
  RefreshADatasetAfterInsert(Dataset as TSQLQuery); //If your dataset is sorted by primary key
end;  

procedure TDataModule1.SQLQuery2AfterPost(DataSet: TDataSet);
begin
  RefreshADatasetAfterInsert(Dataset as TSQLQuery, 'ID'); //if you are not sure that the dataset is always sorted by primary key
end;

Vacuum and other operations that must be done outside a transaction

SQLDB seems to always require a connection, but some operations like Pragma and Vacuum must be done outside a transaction. The trick is to end transaction, execute what you must and start transaction again (so that sqldb doesn't get confused:)

  // commit any pending operations or use a "fresh" sqlconnection
  Conn.ExecuteDirect('End Transaction');  // End the transaction started by SQLdb
  Conn.ExecuteDirect('Vacuum');
  Conn.ExecuteDirect('Begin Transaction'); //Start a transaction for SQLdb to use

Using TSQLite3Dataset

This section details how to use the TSQLite2Dataset and TSQLite3Dataset components to access SQlite databases. by Luiz Américo luizmed(at)oi(dot)com(dot)br

Requirements

Before initiating a lazarus project, ensure that:

How To Use (Basic Usage)

Install the package found at /components/sqlite directory (see instructions here)

At design time, set the following properties:

Creating a Table (Dataset)

Double-click the component icon or use the 'Create Table' item of the popup menu that appears when clicking the right mouse button. A simple self-explaining table editor will be shown.

Here are all field types supported by TSqliteDataset and TSqlite3Dataset:

Retrieving the data

After creating the table or with a previously created Table, open the dataset with the Open method. If the SQL property was not set then all records from all fields will be retrieved, the same if you set the SQL to:

SQL := 'Select * from TABLENAME';

Applying changes to the underlying datafile

To use the ApplyUpdates function, the dataset must contain at least one field that fulfills the requirements for a Primary Key (values must be UNIQUE and not NULL)

It's possible to do that in two ways:

If one of the two conditions is set, just call

ApplyUpdates;

If both conditions are set, the field corresponding to PrimaryKey is used to apply the updates.

Setting PrimaryKey to a field that is not a Primary Key will lead to loss of data if ApplyUpdates is called, so ensure that the chosen field contains not Null and Unique values before using it.

Master/detail example

Various examples of master/detail relations (e.g. the relation between customer and orders):

Remarks

Generic examples can be found at fpc/fcl-db/src/sqlite SVN directory

See also