This section describes functions which SQLite relational databases. SQLite is a public-domain relational database stored locally in a single file, which uses SQL as its query language. There are two main advantages of SQLite with respect to larger relational database systems: there is no need to install any additional software or to have access to a remote database, and the database file can be archived and restored extremely easily. On the other hand, it lacks concurrent access, stored procedures, etc. Its SQL compatibility permits the developer to port easily applications to other databases, should it be necessary.
This documentation assumes you have a basic knowledge of SQL. Even if you do not, the examples should help you to get started. For more informations about SQLite, please visit the Web site http://www.sqlite.org.
The creator of SQLite, D. Richard Hipp, is gratefully acknowledged.
The following functions are defined.
| Function | Purpose |
|---|---|
| sqlite_changes | Number of affected rows in the last command |
| sqlite_close | Close an SQLite database |
| sqlite_exec | Execute an SQL query |
| sqlite_last_insert_rowid | Index of tha last row inserted |
| sqlite_open | Open an SQLite database |
| sqlite_set | Options for sqlite_open |
| sqlite_version | Get the version of SQLite |
The extension is installed with Sysquake or LME and ready to use; but it is also provided as source code. If you want, you can check on the Web if there is a more recent version of SQLite and compile the extension again with it. The steps below show the simplest way to do it.
$ cd $ mkdir mysql-build $ cd mysql_buid $ ext=extpath sqlite=sqlitepath make -f extpath/Makefile.lme-sqlite
SQRLoadExtension extpath/sqlite.so
Number of affected rows in the last command.
n = sqlite_changes(c)
sqlite_changes(c) gives the number of affected rows in the last UPDATE, DELETE, or INSERT command.
sqlite3_changes
sqlite_exec, sqlite_last_insert_rowid
Close an SQLite database.
sqlite_close(c)
sqlite_close(c) closes the MYSQLite database identified by c.
sqlite3_close
Execute an SQL query against an SQLite database.
sqlite_exec(c, query) table = sqlite_exec(c, query)
sqlite_exec(c,query) executes a query given in SQL in a string, against the SQLite database identified by c. The number of modified rows can be obtained with sqlite_changes.
With an output argument, sqlite_exec returns the resulting table as a list of rows. Each row is given as a list of column values or as a structure, as specified in the option argument of sqlite_open created with sqlite_set.
sqlite3_exec
sqlite_open, sqlite_set, sqlite_changes
Row ID of the last row inserted in a SQLite database.
n = sqlite_last_insert_rowid(c)
sqlite_last_insert_rowid(c) gives the last row inserted by the INSERT command with sqlite_exec.
sqlite3_last_insert_rowid
Open an SQLite database.
c = sqlite_open(filename) c = sqlite_open(filename, options)
sqlite_open(filename) opens the database in the specified file. If the file does not exist, a new database is created. The result is an identifier which should be used in all other SQLite calls. The database is closed with sqlite_close.
sqlite_open(filename,options) specifies options in the second input argument, which is usually the result of sqlite_set.
c = sqlite_open('test.sqlite')
c =
0
rows = sqlite_exec(c, 'select * from person');
sqlite_close(c);
sqlite_open
Options for SQLite.
options = sqlite_set options = sqlite_set(name1, value1, ...) options = sqlite_set(options0, name1, value1, ...)
sqlite_set(name1,value1,...) creates the option argument used by sqlite_open. Options are specified with name/value pairs, where the name is a string which must match exactly the names in the table below. Case is significant. Options which are not specified have a default value. The result is a structure whose fields correspond to each option. Without any input argument, sqlite_set creates a structure with all the default options. Note that sqlite_open also interprets the lack of an option argument, or the empty array [], as a request to use the default values.
When its first input argument is a structure, sqlite_set adds or changes fields which correspond to the name/value pairs which follow.
Here is the list of permissible options (empty arrays mean "automatic"):
| Name | Default | Meaning |
|---|---|---|
| ExecResultClass | 'list' | row type ('list' or 'struct') |
| ExecResultNumeric | true | conversion of numeric columns to double |
SQLite is usually typeless. If ExecResultNumeric is true, columns are converted to numbers of class double unless they contain a non-numeric value, or the type name used during declaration contains BLOB, CHAR, CLOB, or TEXT. This is the same convention as what SQLite uses itself, for example when sorting rows. NULL values are always represented as the (double) empty array [].
Default options:
sqlite_set ExecResultClass: 'list' ExecResultNumeric: true
Get the version of SQLite.
str = sqlite_version
sqlite_version gives the version of SQLite compiled in the extension, as a string. No database is required.
sqlite3_version