Difference between revisions of "SQLite"

From Lazarus wiki
Jump to navigationJump to search
(→‎TSQLite3Dataset and TSQLiteDataset: removed som adv: caching is done AFAIK; updating statements in laz sqldb doesn't need SQL either)
Line 38: Line 38:
  
 
* Flexible: programmers can choose to use or not to use the SQL language, allowing them to work with simple table layouts or any complex layout that SQL/sqlite allows
 
* Flexible: programmers can choose to use or not to use the SQL language, allowing them to work with simple table layouts or any complex layout that SQL/sqlite allows
* Automatic database update: no need to update the database manually with SQL statements, a single method take cares of it
 
* Fast: it caches the data in memory, making browsing in the dataset fast
 
 
Disadvantages
 
  
 +
Disadvantages:
 
* Changing to other databases is more difficult than if you use the SQLDB or Zeos components
 
* Changing to other databases is more difficult than if you use the SQLDB or Zeos components
 
  
 
== Using the SQLdb components with SQLite ==
 
== Using the SQLdb components with SQLite ==

Revision as of 10:03, 11 December 2012

SQLite and FPC/Lazarus support

SQLite is an embedded (non-server) single-user database that can be used in FPC and Lazarus applications. Various drivers can be used to access SQLite from FPC/Lazarus programs. All drivers do need the SQLite library/dll in the project directory and executable directory (and distributed with your executable) in order to work.

Built-in SQLDB

FPC/Lazarus offers the built-in SQLDB components that include support for SQLite databases, which allow you to e.g. create GUIs with database components such as dbgrids. The advantage of using SQLDB is that it is fairly easy to change to a different databse such as Firebird or PostgreSQL without changing your program too much. See below for details.

Spatialite support

Spatialite are GIS extensions to SQLite which you can use from within SQLDB. See Spatialite.

Support for SQLite encryption

In recent FPC versions (implemented March 2012), SQLDB included support for some extended versions of SQLite3 which encrypt the SQLite database file using the AES algorithm. Use the password property to set the encryption key.

Examples:

- SQLCipher: open source, e.g. Windows binaries not for free (you have to compile them yourself)

- System.Data.SQLite: open source, Windows (32, 64, CE) binaries available, download e.g on e of the Precompiled Binaries and rename SQLite.Interop.dll to sqlite3.dll (if you're using the Statically Linked ones, presumably you need to rename System.Data.SQLite.DLL to sqlite3.dll)

- wxSQLite3: open source, some binaries for Linux available (ex: https://launchpad.net/ubuntu/oneiric/+package/libwxsqlite3-2.8-0)

Zeos

Zeos

SQLitePass

SqlitePass components. Status: unknown.


TSQLite3Dataset and TSQLiteDataset

There are also separate TSQLiteDataset and TSQLite3Dataset packages; see below for a description on how to use them. Visit the sqlite4fpc homepage to find the API reference and more tutorials.

TSqliteDataset and TSqlite3Dataset are TDataset descendants that access, respectively, 2.8.x and 3.x.x sqlite databases. For new projects, you would presumably use TSQlite3Dataset as SQLite 3.x is the current version.

Below is a list of the principal advantages and disadvantages compared to other FPC/Lazarus SQLite drivers/access methods:

Advantages:

  • Flexible: programmers can choose to use or not to use the SQL language, allowing them to work with simple table layouts or any complex layout that SQL/sqlite allows

Disadvantages:

  • Changing to other databases is more difficult than if you use the SQLDB or Zeos components

Using the SQLdb components with SQLite

These instructions are focused on SQLDB (the TSQLite3Connection) specifics for SQLite. For a general overview, have a look at SqlDBHowto which has some useful information about the SQLdb components.

See SQLdb_Tutorial1 for a tutorial on creating a GUI database-enabled program that is written for SQLite/SQLDB (as well as for Firebird/SQLDB, PostgreSQL/SQLDB, basically any RDBMS SQLDB supports).

We will use a combination of three components from the Lazarus SQLdb tab: TSQLite3Connection, TSQLTransaction and TSQLQuery. The TSQLQuery acts as our TDataset; in the simplest case it just represents one of our tables. For the sake of simplicity: make sure you already have an existing SQLite database file and don't need to create a new one one now. TSQLite3Connection can be found in the sqlite3conn unit, if you want to declare it yourself or are working in FreePascal.

The three components are connected with each other as usual: In the TSQLQuery set the properties Database and Transaction, in the TSQLTransaction set the property Database. There is not much to do in the Transaction and Connection components, most interesting stuff will be done in the TSQLQuery. Configure the components as follows:

TSQLite3Connection:

  • DatabaseName: Set this property to the file name (absolute path!) of your SQLite file. Unfortunately you cannot simply use a relative path that works unchanged at designtime and at runtime ***is this still true? Can't you just copy the db file in a post-build shell script or symlink it?***. You should make sure that at application start the correct path to the file is always set programmatically, no matter what it contained at designtime.

Note: To set the full library path (if you place your sqlite dll/so/dylib in a place where the OS won't find it, like the application directory on Linux/OSX), you can set the SQLiteLibraryName property (BEFORE any connection is established e.g. in the OnCreate event of the main form), like this:

SQLiteLibraryName:='./sqlite3.so';

TSQLQuery:

  • SQL: Set it to some simple select query on one of your tables. For example if you have a table 'foo' and want this Dataset to represent this table then just use the following:
    SELECT * FROM foo
    
  • Active: Set this to True from within the IDE to test whether it is all set up correctly. This will also automatically activate the transaction and the connection objects. If you receive an error then either the DatabaseName of the connection is not correct or the SQL query is wrong. Later if we are done adding the fields (see below) set them all to inactive again, we don't want the IDE to lock the SQLite database (single user!) when testing the application.
  • Probably not necessary for proper operation - will need to be checked (June 2012) Now we can add Fields to our TSQLQuery. While the components are still set to active do a right click and "edit fields...". Click the "+" button and add fields. It will list all fields your SQL query returned. Add every field you will need, you can also add lookup fields here, in this case just make sure you have already defined all needed fields in the other datasets before you start adding lookup fields that refer to them. If your table has many columns and you don't need them all you can just leave them away, you can also make your SQL a bit more specific.
  • In your code you need to call SQLQuery.ApplyUpdates and SQLTransaction.Commit, TSQLQuery.AfterPost and AfterInsert events are a good place for this when using it with data aware controls but of course you can also postpone these calls to a later time. If you don't call them it will not update the database.
  • "Database is locked": The IDE might still be locking the database (SQLite is a single user database), you probably forgot to set the components to inactive and disconnected again after you were done defining all the fields of your TSQLQuery objects. Use the Form's OnCreate event to set the path and activate the objects at runtime only. Most of the things you set in the TSQLQuery from within the IDE don't require (and some don't even allow) them to be active at design time, the only exception is defining the fields where it wants to read the table design, so inactive at design time should be the normal state.
  • Your tables should all have a primary key and you must make sure that the corresponding field has pfInKey and nothing else in its PoviderFlags (these flags control how and where the field is used when automatically constructing the update and delete queries).
  • If you are using lookup fields
    • make sure the ProviderFlags for the lookup field is completely empty so it won't attempt to use it's name in an update query. The lookup field itself is not a data field, it only acts on the value of another field, the corresponding key field, and only this key field will later be used in the update queries. You can set the key field to hidden because usually you don't want to see it in your DBGrid but it needs to be defined.
    • LookupCache must be set to True. At the time of this writing for some reason the lookup field will not display anything otherwise (but still work) and strangely the exact opposite is the case when working with the TSQLite3Dataset or other TXXXDataset components, here it must be set to False. I'm not yet sure whether this is intended behavior or a bug.

After the above is all set up correctly you should now be able to use the TSQLQuery like any other TDataset, either by manipulating it's data programmatically or by placing a TDatasouce on the Form, connecting it to the TSQLQuery and then using data contols like TDBGrid etc.

Tips

  • Use the TSQLite3Connection.CreateDB procedure to create a new SQLite database.

Using TSQLite3Dataset

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


Requirements

  • For sqlite2 databases (legacy):
    • FPC 2.0.0 or higher
    • Lazarus 0.9.10 or higher
    • SQLite runtime library 2.8.15 or above*
  • Sqlite2 is not maintained anymore and the binary file cannot be found in the sqlite site
  • For sqlite3 databases:
    • FPC 2.0.2 or higher
    • Lazarus 0.9.11 (svn revision 8443) or higher
    • sqlite runtime library 3.2.1 or higer (get it from www.sqlite.org)

Before initiating a lazarus projects, ensure that:

  • the sqlite library is either
    • in the system PATH or
    • in the executable output directory and Lazarus (or current project) directories - this option might work on Windows only
  • under Linux, put cmem as the first unit in uses clause of the main program
    • In Debian, Ubuntu and other Debian-like distros, in order to build Lazarus IDE you must install the packages libsqlite-dev/libsqlite3-dev, not only sqlite/sqlite3 (Also applies to OpenSuSe)

How To Use (Basic Usage)

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

At design time set the following properties:

  • FileName: path of the sqlite file [required]
  • TableName: name of the table used in the sql statement [required]
  • SQL: a SQL select statement [optional]
  • SaveOnClose: The default value is false, which means that changes are not saved. One can change it to True. [optional]
  • Active: Needs to be set at design time or at program startup. [required]

Creating a Table (Dataset)

Double click in 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 show.

Here are all field types supported by TSqliteDataset and TSqlite3Dataset:

  • Integer
  • AutoInc
  • String
  • Memo
  • Bool
  • Float
  • Word
  • DateTime
  • Date
  • Time
  • LargeInt
  • Currency

Retrieving the data

After creating the table or with a previously created Table, open the dataset with 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 fills the requirements for a Primary Key (values must be UNIQUE and not NULL)

It's possible to do that in two ways:

  • Set PrimaryKey property to the name of a Primary Key field
  • Add an AutoInc field (This is easier since the TSqliteDataSet automatically handles it as a Primary Key)

If one of the two conditions is set then just call

ApplyUpdates;

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

PS2: 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

An example of implemening master/detail (customer/orders) using locate can be found here: TSqlite3 Master Detail Example

Remarks

  • Although it has been tested with 10000 records and worked fine, TSqliteDataset keeps all the data in memory, so remember to retrieve only the necessary data (principally with Memo Fields).
  • The same datafile (Filename property) can host several tables/datasets
  • Several datasets (different combinations of fields) can be created using the same table simultaneously
  • It's possible to filter the data using WHERE statements in the sql, closing and reopening the dataset (or calling RefetchData method). But in this case, the order and number of fields must remain the same
  • It's also possible to use complex SQL statements using aliases, joins, views in multiple tables (remember that they must reside in the same datafile), but in this case ApplyUpdates won't work. If someone wants to use complex queries and to apply the updates to the datafile, mail me and i will give some hints how to do that
  • Setting filename to a sqlite datafile not created by TSqliteDataset and opening it is allowed but some fields won't have the correct field type detected. These will be treated as string fields.

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

See also