en fr

Sysquake Pro – Table of Contents

Sysquake – 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.

FunctionPurpose
sqlite_changesNumber of affected rows in the last command
sqlite_closeClose an SQLite database
sqlite_execExecute an SQL query
sqlite_last_insert_rowidIndex of tha last row inserted
sqlite_openOpen an SQLite database
sqlite_setOptions for sqlite_open
sqlite_shellSimple SQLite shell
sqlite_tablesGet the table names
sqlite_versionGet 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:
$ cd
$ mkdir sqlite-build
$ cd sqlite-buid
$ ext=extpath sqlite=sqlitepath
   make -f extpath/Makefile.lme-sqlite
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):
SQRLoadExtension extpath/sqlite.so
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.

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_open

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_exec, sqlite_changes

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_close, sqlite_set

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"):

NameDefaultMeaning
ExecResultClass'list'row type ('list' or 'struct')
ExecResultNumerictrueconversion 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_open

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_open, sqlite_exec

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_open, sqlite_exec

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