Sysquake Pro – Table of Contents
Sysquake for LaTeX – Table of Contents
Extension - SQLite
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_shell | Simple SQLite shell |
| sqlite_tables | Get the table names |
| sqlite_version | Get the version of SQLite |
Compiling the extension
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.
- Check your development tools
- Make sure you have the development tools required for compiling the extension. Typically, you need a C compiler chain like gcc. You can get it as free software from GNU.
- Get SQLite distribution
- Download the latest distribution from the site http://www.sqlite.org.
- Locate the required files
- To compile the extension, you will need the following files:
- LMESQLite.c, the main source code of the extension which defines new functions for LME.
- LME_Ext.h, the header file for LME extensions, which is provided with all LME applications which support extensions; it is typically stored in a directory named ExtDevel. Let extdevel be its path.
- The source code of SQLite, typically in the directory src of the SQLite distribution.
- Compile the extension
- Create a new directory, cd to it, and run the Make file of the SQLite extension. For example:
- Install the extension
- For most LME applications, just move or copy the extension (sqlite.so if you have used the command above) to the directory where LME looks for extensions (usually LMEExt). For Sysquake Remote, you also have to add the following line to the configuration file of Apache (please read Sysquake Remote documentation for more information):
- where extpath/sqlite.so is the absolute path of the extension.
- Start or restart the LME application
- To check that LME has loaded the extension successfully, check the information line starting with SQLite. You can also try to evaluate sqlite_version, which should display the version of SQLite.
$ cd $ mkdir sqlite-build $ cd sqlite-buid $ ext=extpath sqlite=sqlitepath make -f extpath/Makefile.lme-sqlite
SQRLoadExtension extpath/sqlite.so
Functions
sqlite_changes
Number of affected rows in the last command.
Syntax
n = sqlite_changes(c)
Description
sqlite_changes(c) gives the number of affected rows in the last UPDATE, DELETE, or INSERT command.
SQLite call
sqlite3_changes
See also
sqlite_exec, sqlite_last_insert_rowid
sqlite_close
Close an SQLite database.
Syntax
sqlite_close(c)
Description
sqlite_close(c) closes the SQLite database identified by c.
SQLite call
sqlite3_close
See also
sqlite_exec
Execute an SQL query against an SQLite database.
Syntax
sqlite_exec(c, query, ...) table = sqlite_exec(c, query, ...)
Description
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.
Additional input arguments are bound to placeholders (question mark character) in the query. With respect to building a query with string concatenation or sprintf, this has the advantage of preventing any syntax error for characters which have a special meaning in SQLite (with the security risk of code injection) and type conversion. Supported types include strings, arrays of uint8 (stored as blobs), and scalar floating-point and integer numbers (complex part is ignored).
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.
SQLite calls
sqlite3_prepare16_v2, sqlite3_bind_text16, sqlite3_bind_blob, sqlite3_bind_null, sqlite3_bind_int, sqlite3_bind_int64, sqlite3_bind_double, sqlite3_finalize, sqlite3_column_count, sqlite3_step, sqlite3_column_type, sqlite3_column_int, sqlite3_column_double, sqlite3_column_text16, sqlite3_column_blob, sqlite3_column_bytes, sqlite3_column_name
Examples
name = 'Joe'; age = 8; sqlite_exec(c, 'insert into persons (name, age) values (?,?);', name, age); r = sqlite_exec(c, 'select age from persons where name = ?;', name);
See also
sqlite_open, sqlite_set, sqlite_changes
sqlite_last_insert_rowid
Row ID of the last row inserted in a SQLite database.
Syntax
n = sqlite_last_insert_rowid(c)
Description
sqlite_last_insert_rowid(c) gives the last row inserted by the INSERT command with sqlite_exec.
SQLite call
sqlite3_last_insert_rowid
See also
sqlite_open
Open an SQLite database.
Syntax
c = sqlite_open(filename) c = sqlite_open(filename, options)
Description
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.
Example
c = sqlite_open('test.sqlite')
c =
0
rows = sqlite_exec(c, 'select * from person');
sqlite_close(c);
SQLite calls
sqlite_open, sqlite3_progress_handler
See also
sqlite_set
Options for SQLite.
Syntax
options = sqlite_set options = sqlite_set(name1, value1, ...) options = sqlite_set(options0, name1, value1, ...)
Description
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 [].
Examples
Default options:
sqlite_set ExecResultClass: 'list' ExecResultNumeric: true
See also
sqlite_shell
Simple SQLite shell.
Syntax
sqlite_shell(c)
Description
sqlite_shell(c) starts a simple shell where SQL statements can be typed and executed. Each line corresponds to a separate statement; the trailing semicolon can be omitted. In addition to SQL statements, quit exits the read-execute-print loop and returns to LME.
SQLite call
sqlite3_exec
See also
sqlite_tables
Get the names of tables in an SQLite database.
Syntax
tables = sqlite_tables(c)
Description
sqlite_tables(c) gives a list of table names defined in the SQLite database identified by c. The names are sorted.
SQLite call
sqlite3_exec
See also
sqlite_version
Get the version of SQLite.
Syntax
str = sqlite_version
Description
sqlite_version gives the version of SQLite compiled in the extension, as a string. No database is required.
SQLite call
sqlite3_version