SQLite COM database

The ALP Run-time library in Active Local Pages 1.2 and later includes a component named SQLite COM. This is a fully functional SQL database engine based on the SQLite free source code. The database engine and the programming interface to it is implemented in a single component and thus it is very easy to re-distribute with any application that uses it.

A SQLite COM databases is stored in single file - all the database objects are packed in that file, there are no indices or other side files you must care about when moving the database. The database file format is one for all the platforms supported by the component and the other the SQLite implementations - there is no need of conversion for instance.

Over the standard SQLite features SQLite COM adds also some features useful in Windows environment. Most of them are related to the database interface, but there are also some implemented as SQL functions - the OLE Date/time formatting and arithmetic support.

The SQLite COM requires no installation, no persistent configuration, nor depends on ADO, OLEDB, MS Jet/MS Access or any other 3-d party software. It is implemented over standard OS features available in all the Windows OS versions. This makes it perfect for any application that aims to avoid any complications related to the OS and its components versions.

Along with the typical database applications the fact that a single database file contains all the database objects opens unusual opportunities such as using a database as document file or exchange media. Even applications based on another database software my benefit of using SQLite COM as a mediator between them and other programs or as a way to construct tear-of applications exposing scraps of the main database without need of persistent connection to it.

SQLite COM also supports in-memory database functionality. You just pass an empty string to the Open method and this will open an in-memory database. Through SQL statements you can connect other databases and transfer data from/to the in-memory database.

The SQL support is fairly advanced. If compared to other lightweight databases SQLite can be positioned at the same level as MS Jet/MS Access for example. However, the week and the strong sides of SQLite and Jet are different - for example one of the strongest sides of SQLite is the support for triggers and full-scale non-nested transactions, but it does not support parameters from the external statements in nested SQL statements. For the most usages the differences would not matter, but it is recommended to take a look at the SQLites SQL language support reference in order to plan your work better.

The database interface supplied by SQLite COM is non-recordset based, but this is also a plus. Developers who use frequently the ADO Recordset's GetRows method will find it familiar, but much more convenient. The query results are presented in collections that can be addressed by numerical and named indices (i.e. field names) and enumerated using traditional For .. Next cycles. The field type information can be exposed in various ways which depends on a single property you can set to instruct the database return the information in the best form for the particular case. The data returned in result of a query execution can be converted to appropriate script manageable types automatically or presented as strings depending on your preference.

Listing a table looks like this:

<% Set r = g_db.Execute("SELECT * FROM Clients ORDER BY Name") 
   For I = 1 To r.Count %> 
     <%= r(I)("Name") %>, <%= r(I)("Email") %><BR>
<% Next %>

Paging the results/Extraction of partial query results is simple too. This is the above code changed to return only the first 10 records.

<% Set r = g_db.Execute("SELECT * FROM Clients ORDER BY Name",1,10)
   For I = 1 To r.Count %>
     <%= r(I)("Name") %>, <%= r(I)("Email") %><BR>
<% Next %>

The SQLite databases are type-less internally. Still, the database distinguishes numeric from text values in order to perform operations. You are free to specify whatever type names you want for the table columns which allows the applications implement databases that resemble other database engines closely. SQLite COM maintains a set of dynamically customizable type names that are treated as numeric types. This allows you configure which type names will imply numeric values by default and thus instruct the database convert them automatically when they are returned by queries. The default settings are selected in manner that will suit most applications.

The database performance is one of the best in its class. Provided you use transactions when inserting/updating SQLite outruns many popular databases - such as MySQL for example.

One connection - one thread. Single SQLite COM object implies one connection to the database, but you can open more SQLite COM connections to the same database - all the control is yours and is absolutely clear. The records locking is intelligent enough to allow several concurrent connections perform substantial tasks thus allowing you construct whatever database infrastructure you may need in your applications.

Further information:

See the SQLite COM reference in NDL.
See the database samples installed by ALP.
See the SQLite COM web page.