SQLiteDatabase

From Xojo Documentation
Jump to: navigation, search

Class (inherits from Database)

The SQLiteDatabase class provides access to the SQLite data source, a.k.a., database engine or database back-end.


Properties
DatabaseFile ErrorMessage ReadOnlyProperty.png ShortColumnNames
EncryptionKey LibraryVersion ReadOnlyProperty.png ThreadYieldInterval
Error ReadOnlyProperty.png LoadExtentions Timeout
ErrorCode ReadOnlyProperty.png MultiUser


Methods
AttachDatabase Decrypt OpenBlob
Backup DetachDatabase Prepare
Close Encrypt Rollback
Commit FieldSchema SQLExecute
Connect IndexSchema SQLSelect
CreateBlob InsertRecord TableSchema
CreateDatabaseFile LastRowID


Notes

Use this class to open or create SQLite databases programmatically. You can also directly add a SQLite database to your project using the Insert menu or button. You can define tables using the Database Editor. Refer to the User Guide for more information on the Database Editor.


NOTE: SQLite version used: 3.8.5  


SQLite supports a subset of SQL/92 and SQL/99, including queries that involve self-joins, aggregate functions and more.

For complete documentation of SQLite, refer to the official SQLite documentation: http://sqlite.org/docs.html

More specific topics:

A call to SQLSelect returns a dynamic RecordSet; you can move forward, backward, or jump to the beginning or end as much as you like.

SQLite supports transactions, both for schema changes and for data changes. A transaction is started automatically when you make any change to the database and is ended by calling either the Commit or Rollback methods of the database class.

All four RecordSet navigation methods are supported for SQLite: MoveFirst, MoveNext, MovePrevious, and MoveLast.

Threading

SQLSelect and SQLExecute statements do not block when called from within Threads.

Transactions

By default, SQLite does a Commit after each SQL command that changes the database or its data.

To ensure data integrity and to improve performance, you should create a transaction and do your database changes in the transaction. To start a transaction use this command:

db.SQLExecute("BEGIN TRANSACTION") // db is an instance of SQLiteDatabase

When you are finished with the database changes, you can close the transaction by calling Commit to make the changes permanent:

db.Commit

If you want to cancel the changes, you can use Rollback:

db.Rollback


Encrypted Databases

AES-128 encryption is used. For more information about the encryption, refer to the SQLite Encryption documentation.

To create an encrypted database, specify a value for the EncryptionKey property. The EncryptionKey property must be set before calling either Connect or CreateDatabaseFile. In other words, write something like this to create a new database:

Dim db As New SQLiteDatabase
db.DatabaseFile = GetFolderItem("db.sqlite")
db.EncryptionKey = "howdy+doody"
If Not db.CreateDatabaseFile Then
//handle error here
End If


When you open an encrypted database file, you need to supply the key:

Dim db As New SQLiteDatabase
db.DatabaseFile = GetFolderItem("db.sqlite")
db.EncryptionKey = "howdy+doody"
If Not db.Connect Then
//handle error here
End If

Result Codes

SQLite sets the database class's Error flag after each operation and returns values in the ErrorCode and ErrorMessage properties. When the Error flag is False, the ErrorCode is "0" and ErrorMessage is empty. If the Error flag is True, the following codes and messages are returned in ErrorMessage


SQLite error codes:

Error code Error Message
0 Not an error
1 SQL logic error or missing database
2 Internal SQLite implementation flaw
3 Access permission denied
4 Callback requested query abort
5 Database is locked
6 Database table is locked
7 Out of memory
8 Attempt to write a read/only database
9 Interrupted
10 Disk I/O error
11 Database disk image is malformed
12 Table or record not found
13 Database is full
14 Unable to open database file
15 Database locking protocol failure
16 Table contains no data
17 Database schema has changed
18 Too much data for one table row
19 Constraint failed
20 Datatype mismatch
21 Library routine called out of sequence
22 Kernel lacks large file support
23 Authorization denied
24 Auxiliary database format error
25 Bind or column index out of range
26 File is encrypted or is not a database
200 Not connected

Primary Keys

All SQLite tables have an Integer Primary Key column. If you don't explicitly create such a column, one will be created for you with the name "rowid". If you create your own INTEGER PRIMARY KEY column, then rowid acts as an alias to that column. This means that a query that includes rowid will instead return the column that is the primary key.

This SQL will create an Employees table with "EmployeeID" as the primary key:

 CREATE TABLE Employees (EmployeeID INTEGER PRIMARY KEY, FirstName TEXT, LastName TEXT)

You can also use this SQL:

 CREATE TABLE Employees (EmployeeID INTEGER, FirstName TEXT, LastName TEXT, PRIMARY KEY (EmployeeID))

The above syntax is used when creating a table with a multi-part primary key.


Now consider the following queries:

 SELECT * FROM Employees
 SELECT rowid,* FROM Employees
 SELECT rowid, FirstName, LastName FROM Employees

In all of these cases, you will get the EmployeeID column in place of the rowid column.

If you have your own primary key column but still wish to get the rowid, you can do so using the SQL AS keyword. The following example returns the columns rowid, EmployeeID, FirstName, LastName:

 SELECT rowid AS rowid, * FROM tablename

If you don't explicitly define your own INTEGER PRIMARY KEY column, you won't get the 'rowid' column unless you specifically include it in the list of columns to include in your query:

 SELECT rowid, * FROM TableName


NOTE: You should always specifically define a primary key for your tables and not rely on the rowid column. When there is no specific primary key, the values for rowid are not guaranteed to remain the same which could cause problems if you use the rowid values in your apps or in foreign keys. In particular, the rowid values could be renumbered when data is deleted from a table and when the database is cleaned up using the VACUUM command.  


Foreign Keys

SQLite supports foreign keys, but they are not enabled by default. To enable them, you use the SQLite PRAGMA command each time you connect to the database:

 PRAGMA foreign_keys = ON;

You can send this PRAGMA command to SQLite from Xojo using SQLExecute:

Dim db As New SQLiteDatabase

If db.Connect Then
// Set page_size before creating any tables
db.SQLExecute("PRAGMA foreign_keys = ON")
End If

Data Types

SQLite handles data types differently than most other database.

In particular, it does not use strong data typing. Although a column and be defined using a data type (called a storage class or affinity with SQLite), you can still put any type of data in any column.

To start, SQLite only supports these data types:

  • NULL
  • INTEGER
  • REAL
  • TEXT
  • BLOB

Refer to the official SQLite Data Type page here: http://www.sqlite.org/datatype3.html

The following table describes how to use the SQLite data types to store data from built-in data types with DatabaseRecord or DatabaseField:

FieldType Description
Blob BLOB stands for Binary Large OBject. It is a column that contains large string data. This data is not altered in any way (no encodings are applied), so you can use a BLOB to store pictures, files or any other binary data. Use the DatabaseRecord.BlobColumn method or DatabaseField.StringValue to read or save BLOB data. Alternatively you can use the SQLiteBlob class to handle BLOB data incrementally.
Boolean SQLite does not have a BOOLEAN data type. Booleans are stored using 0 or 1 in an INTEGER column or as "true" or "false" in a TEXT column. DatabaseRecord.BooleanColumn and DatabaseField.BooleanValue will correctly convert the above values to a boolean. Other values in a column are undefined when attempting to convert to a boolean.
Currency Use the REAL data type to store Currency values in conjunction with DatabaseRecord.CurrencyColumn and DatabaseField.CurrencyValue.
Date SQLite does not have a DATE data type. Dates are stored as text using the format YYYY-MM-DD when using DatabaseRecord.DateColumn and DatabaseField.DateValue. To store a DateTime value, you should manually save Date.SQLDateTime as a string.
Double Use the REAL data type to store Double values in conjunction with DatabaseRecord.DoubleColumn and DatabaseField.DoubleValue.
Integer Use the INTEGER data type to store Integer values with DatabaseRecord.IntegerColumn and DatabaseField.IntegerValue. SQLite always stored 64-bit integer values.
Int64 Use the INTEGER data type to store Int64 values with DatabaseRecord.Int64Column and DatabaseField.Int64Value. SQLite always stored 64-bit integer values.
Picture Use the BLOB data type to store Pictures with DatabaseRecord.PictureColumn and DatabaseField.PictureValue.
String Use the TEXT data type to store String values with DatabaseRecord.Column and DatabaseField.StringValue. SQLite converts all text in TEXT columns to UTF-8 encoding. If you want to preserve the original encoding, use a BLOB column type instead.

Because of the way SQLite stores all data, you can use DatabaseRecord.Column and DatabaseField.StringValue to get the values of data in any column, even if is not specifically a TEXT column.

Creating a SQLite Database

You can create a SQLite via the IDE by using the Insert menu or button. Use the save-file dialog box to name the database and specify the folder in which it will be stored.

When you click OK, a SQLite Database is added to your project using the name you entered in the dialog. You can double-click the item to open the Database Editor for adding tables, fields, and indexes. For more information, see the refer to User Guide Book 3: Framework, Chapter 4: Databases, Section 3: SQLite.

In-Memory Database

An "in-memory database" is a SQLite database that exists only in memory; there is no related file on disk. It works exactly as a SQLite database except it is very fast and completely temporary.

To create an in-memory database, just create a new SQLiteDatabase instance and connect to it:

Dim inMemoryDB As New SQLiteDatabase
If inMemoryDB.Connect Then
// Can now use inMemoryDB
End If

You can use the Backup method to save an in-memory database to a file on disk.

Modified Rows

To find out the number of rows that were modified by an UPDATE or INSERT command, you can call the SQLite changes function:

SELECT changes()

Extensions

Extentions are not loaded by default. Use the LoadExtensions property to enable them as necessary.

Performance Tips

To improve the performance of SQLite DB access, you can turn on Write-Ahead Logging by setting the MultiUser property to True. Additionally, to increase the user's perception of performance, you may want to place long-running queries within a Thread.

You can also increase the page size that SQLite uses by changing the page_size setting using the PRAGMA command. The default page_size is 1024, but a larger size can help reduce I/O. To change the setting, give it a new value after you create a database but before you create the first table. The page_size is permanently retained for the database after it is set:

Dim db As New SQLiteDatabase

If db.Connect Then
// Set page_size before creating any tables
db.SQLExecute("PRAGMA page_size = 4096")
db.SQLExecute("CREATE TABLE Foo (c1, c2)")
End If

For more information about PRAGMA commands, visit the official SQLite documentation:

Periodically use the ANALYZE command to update the internal information that SQLite uses to make better query planning choices. You'll probably want to do this any time you add or remove large amounts of data from a table or when the SQLite database version is updated.

db.SQLExecute("ANALYZE")

Examples

The following example creates a new SQLite database:

Dim f As FolderItem
f = New FolderItem("MyDB.sqlite")

Dim db As New SQLiteDatabase
db.DatabaseFile = f
If db.CreateDatabaseFile Then
//proceed with database operations...
Else
MsgBox("Database not created. Error: " + db.ErrorMessage)
End If


The following example opens an existing SQLite database.

Dim dbFile As FolderItem
Dim db As New SQLiteDatabase
dbFile = GetFolderItem("MyDB.sqlite")
db.DatabaseFile = dbFile
If db.Connect Then
//proceed with database operations here..
Else
MsgBox("The database couldn't be opened. Error: " + db.ErrorMessage)
End If


The following example adds a record to a table.

Dim dbFile As FolderItem
Dim db As New SQLiteDatabase
dbFile = GetFolderItem("Employees.sqlite")
db.DatabaseFile = dbFile
If db.Connect Then
db.SQLExecute("BEGIN TRANSACTION")
db.SQLExecute ("INSERT INTO Employees (Name,Job,YearJoined) VALUES "_
+"('Dr.Strangelove','Advisor',1962)")
If db.Error then
MsgBox("Error: " + db.ErrorMessage)
db.Rollback
Else
db.Commit
End If
Else
MsgBox("The database couldn't be opened. Error: " + db.ErrorMessage)
End If


See Also

Database Class, DatabaseRecord, PreparedSQLStatement, SQLitePreparedStatement, RecordSet classes.

Personal tools
Namespaces

Variants
Actions
Main
Documentation
Community
Toolbox
Help