C H A P T E R  8

Language Extensions

SQLite is written in C and has its own C API, which makes C the “native language” to some degree. The open source community, however, has provided many extensions for SQLite that make it accessible to many programming languages and libraries such as Perl, Python, Ruby, Java, .NET/C#, Qt, and ODBC. In many cases, there are multiple extensions to choose from for a given language, developed by different people to meet different needs.

Many extensions conform to various API standards. For instance, one of the SQLite Perl extensions follows the Perl DBI—Perl's standard interface for database access. Similarly, one of the Python extensions conforms to Python's DB API specification, as does at least one of the Java extensions to JDBC. Regardless of their particular APIs, internally all extensions work on top of the same SQLite C API, which you've already explored in Chapters 5, 6, and 7. To some degree, all extensions reflect the C foundation of the SQLite design. Some extensions provide both a standard interface that conforms to their particular API standard as well as an alternative interface that better reflects the design of the SQLite API. Therefore, in such cases, you can choose which interface works best for you based on your requirements.

In general, all extensions have the same comparative anatomy regardless of their particular API. They follow a similar pattern. Once you understand this pattern, every interface will start to look similar in many respects. There is some kind of connection object representing a single connection to the database, and some kind of cursor object representing a SQL query from which you can both execute commands and iterate over results. Conceptually, it is quite simple. Internally, you are looking at a sqlite3 structure and a sqlite3_stmt structure, both well-known structures from our earlier explanation and examples. The same rules apply for language extensions as for the C API.

This chapter covers language extensions for six popular languages: Perl, Python, Ruby, Java, Tcl, and PHP. The intent is to provide you with a convenient introduction with which to quickly get started using SQLite in a variety of different languages. We'll highlight right now that we won't be teaching you those languages themselves—so some knowledge of Perl, or Python, or Ruby, and so on, is expected.

Coverage for each language follows a common outline composed of the common topics of questions from people starting out with language extensions for SQLite:

• Connecting to databases
• Executing queries
• Using bound parameters
• Implementing user-defined functions or aggregates

Together these topics constitute the vast majority of what you will ever use in any particular SQLite extension. You might find some “developer tension” over developing user-defined functions in SQLite versus implementing the logic in your chosen language. Although we can't make that decision for you, we can give you the knowledge and expertise to help make that choice yourself.

Not every extension supports all of these topics. For instance, some don't offer bound parameters. Conversely, some extensions offer unique features that are outside the topics covered here. The aim here is to provide a consistent, straightforward process with which to easily get started using SQLite in a wide variety of languages. Once you get started with any extension, it is usually easy to pick up any special features available in that extension.

Where appropriate, this chapter addresses how different extensions use various parts of the C API in order to help you understand what is going on under the hood. If you have not read the preceding chapters on the C API, we strongly recommend that you do so. No matter what language you program in, it will almost always be helpful to understand how SQLite works in order to make the most of it and thus write good code. It will help you select the most suitable query methods, understand the scope of transactions, and know how to anticipate, deal with, or avoid locks, among other things.

Selecting an Extension

For many of today's popular languages, you are spoilt for choice when it comes to language bindings for SQLite. Choosing the extension that suits you is often more than a straight technical decision. To help, we cover the interface that best fits the following criteria:

• Support for the latest SQLite 3 versions
• Good documentation
• Stability
• Portability

Despite these criteria, there were multiple candidates in some cases that met all the qualifications. In such cases, the tiebreaker was more a matter of personal preference than anything else. But the purpose here is not to favor a particular extension. It is to teach concepts so that you can easily pick any extension in any language and quickly put it and SQLite to good use.

The reasons we chose a particular extension may not be the reasons you would. Some additional points to consider include the following:

• License and license compatibility: The extension's license can directly affect how you can use it. Is it open source, and if so, under which license? If you are writing code for a commercial product, you definitely need to take this into consideration.
• Data type mapping: How does the extension map SQLite's storage classes to the language's native types. Are all values returned as text? Is some kind of crazy casting scheme used? If not, is there an easy way to determine the mappings? Do you have any control over how the mapping is done?
• Query methods: Three different query methods are supported in the C API. Which one does the extension use? Ideally it supports all three.
• API coverage: How well does the extension cover other areas that don't easily map to many standard database interfaces? For example, does it allow you to call the SQL trace function or operational security functions? Do you actually need these?
• Linkage and distribution: How easy is it to use the extension with a particular version of SQLite? Does it include a version of SQLite in the distribution? Does it use a shared library, or is it statically linked to a particular version? What if you need to upgrade SQLite; how easily can you do this with the extension? Will anything break?

There are other considerations as well, such as community support, mailing list activity, maintainer support and responsiveness, regression testing, code quality...the list goes on. All of these things may play an important role in your decision to use a particular extension. Only you can answer these questions for yourself.

The SQLite wiki has a page that provides an exhaustive list of language interfaces, located at www.sqlite.org/cvstrac/wiki?p=SqliteWrappers. All of the interfaces covered here were taken from this list. The source code for all of the examples in this chapter is located in the ch8 directory of the examples zip file, available at the Apress web site.

Perl

There are two SQLite extensions for Perl. The first is a modeled on the Perl DBI standard for database connectivity; therefore, if you understand DBI, you will have no trouble using the extension. For those unfamiliar with the Perl DBI, you can review full documentation on the CPAN web site at http://search.cpan.org/search?module=DBI. The second extension is a SWIG-based wrapper for use with Perl. We'll examine the DBI interface in the following sections.

Installation

The current version of the SQLite DBI driver at the time of this writing is DBD-SQLite-1.29. You can install it by using CPAN or by manually building the package from source. The prerequisites for the SQLite DBD module are, of course, Perl, a C compiler, and the DBI module. The latest versions of the SQLite DBI driver also require YAML (“Yet another markup language” or “YAML Ain't Markup Language,” depending on whom you believe), used for various configuration files. To install DBD::SQLite using CPAN, invoke the CPAN shell from the command line as follows:

To install from source in Linux/Unix, change to a temporary directory and do the following:

The SQLite Perl extension includes its own copy of the SQLite3 binaries, so there is no need to compile and install SQLite beforehand. SQLite is embedded in the extension.

Connecting

To check the SQLite driver is installed, use the DBI function available_drivers() within Perl:

You connect to a database using the standard DBI::connect function, as follows:

The second and third arguments correspond to the driver name and database name. The third and fourth correspond to username and password, which are not applicable to SQLite.

The function returns a database handle object representing the database connection. Internally, this corresponds to a single sqlite3 structure. You can create in-memory databases by passing :memory: for the name of the database. In this case, all tables and database objects will then reside in memory for the duration of the session and will be destroyed when the connection is closed. You close the database using the database handle's disconnect method.

Query Processing

Queries are performed using the standard DBI interface as well. You can use the prepare(), execute(), fetch(), or selectrow() functions of the database connection handle. All of these are fully documented in the DBI man page. Listing 8-1 shows an example of using queries.

Internally, the prepare() method corresponds to the sqlite3_prepare_v2() method. Similarly, execute() calls the first sqlite3_step() method. It automatically figures out whether there is data to be returned. If there is no data (for example, non-select statements), it automatically finalizes the query. The fetchrow_array(), fetchrow_hashref(), and fetch() methods call sqlite3_step() as well, returning a single row in the result set until the results are exhausted.

Additionally, you can run non-select statements in one step using the database object's do() method. Listing 8-2 illustrates this using an in-memory database.

The statement handle object's finish() method will call sqlite3_finalize() on the query object, if it has not already done so. This program produces the following output:

---

---

Parameter Binding

Parameter binding follows the method defined in the DBI specification. Although SQLite supports both positional and named parameters, the Perl interface uses only positional parameters, demonstrated in Listing 8-3.

The execute() method takes the parameter values defined in prepare(). Running this against the evolving foods database we've used for many of our examples returns 73 rows (depending on how much fun you had playing with chocolate bobka in previous chapters).

User-Defined Functions

User-defined functions and aggregates are implemented using private methods of the driver. They follow closely to their counterparts in the SQLite C API. Functions are registered using the following private method:

Here $name specifies the SQL function name, $argc specifies the number of arguments, and $func_ref specifies a reference to the Perl function that provides the implementation. Listing 8-4 illustrates an implementation of hello_newman() in Perl.

If you provide -1 as the number of arguments, then the function will accept a variable number of arguments.

Aggregates

User-defined aggregates are implemented as packages. Each package implements a step function and a finalize function. The step function is called for each row that is selected. The first value supplied to the function is the context, which is a persistent value maintained between calls to the function. The remaining values are the arguments to the aggregate function supplied in the SQL statement. The finalize function is provided the same context as the step function. The following example implements a simple SUM aggregate called perlsum. The aggregate is implemented in a package called perlsum.pm, shown in Listing 8-5.

The init function is used to register the aggregate in the database connection.

Python

There are numerous approaches to working with SQLite in Python. These can be grouped into two camps. The first is the group of SQLite wrappers, such as PySQLite and APSW, that act as straightforward wrapper interfaces to the underlying SQLite API calls (though with varying degrees of depth). The second group is the all-encompassing framework approach, such as SQL Alchemy. These are typically designed as complete object-relational mapping systems, seeking not only to provide access to the underlying database API but to also follow object-oriented philosophies in the design and use of database-related code.

At last count, there were at least nine different wrappers or object-relational frameworks for SQLite in Python. That's almost enough for a book in its own right. We'll cover one of the more popular wrappers, PySQLite, principally because it has been one of the most enduring and also maps to the Python DB API.

Michael Owens (the author of the first edition of this book) wrote the original version of PySQLite and started the project in 2002, which then was written using the SQLite 2.x API. Gerhard Häring has since taken over the project and completely rewritten it for PySQLite version 2, which supports SQLite version 3. The project has also evolved and moved several times and now refers to itself as pysqlite (all lowercase). You can find project information for PySQLite on Google Code at http://code.google.com/p/pysqlite/. Both the source code and native Windows binaries are available for download.

Installation

From Python 2.5 onward, pysqlite is included as a standard module for Python and is simply referred to as the sqlite3 module when used from the standard library. There are still some uses to separately acquiring and compiling/using the pysqlite stand-alone module, because it tracks bug fixes and new features more closely than the Python distribution itself.

To source your own pysqlite package, Windows users are encouraged to use the provided binaries rather than building them by hand. On Linux, Unix, Mac OS X, and other systems, you build and install pysqlite using Python's distutils package, or you source the rpm or deb file from your local package repository. The prerequisites for compiling your own PySQLite are a C compiler, Python 2.3 or newer, and SQLite 3.1 or newer. After unpacking the source code, compile the code with the following command:

distutils should normally be able to figure out where everything is on your system. If not, you will get an error, in which case you need to edit the setup.cfg file in the main directory to point out where things are. If everything goes well, you should see “Creating library...” in the output, indicating a successful build. Next install the library:

You should not see any errors. If you do, report them to the community through the quite active group at http://groups.google.com/group/python-sqlite.

For Windows systems, simply download the binary distribution and run the installer. The only prerequisite in this case is that SQLite 3.2 or newer be installed on your system.

Connecting

As mentioned, the built-in module in Python 2.5 and newer is named sqlite3. If using the separately sourced pysqlite package, it is in pysqlite2. The DB API module is located in dbapi2. This is sometimes confusing when you are thinking of PySQLite version numbers and SQLite version numbers simultaneously. You connect to a database using the module's connect() function, passing in the relative name or complete file path of the database file you want to open.

For the built-in pysqlite in Python 2.5+, a connection would be as follows:

With the separately sourced package for pysqlite, your connect changes to the following:

The usual rules apply: you can use in-memory databases by passing in the name :memory: as the database name; new databases are created for new files; and so forth. You can see the only real variation in the included module and separately sourced module for pysqlite is in import semantics. From here on, we'll assume you're happy switching to the technique that best suits your preferred way of using pysqlite.

Query Processing

Query execution is done according to the Python DB API Specification 2. You create a cursor object with which to run the query and obtain results. Internally, a cursor object holds a sqlite3_stmt handle. Listing 8-6 illustrates executing a basic query.

Remember, Python code is white-space sensitive, so be careful not to introduce additional spaces or tabs when copying this code. Running this code produces the following output:

---

---

Query compilation and the first step of execution are performed together in the cursor's execute() method. It calls sqlite3_prepare() followed by sqlite3_step(). For queries that modify data rather than return results, this completes the query (short of finalizing the statement handle). For select statements, it fetches the first row of the result. The close() method finalizes the statement handle.

PySQLite 2 supports iterator-style result sets, similar to other Python database wrappers. For example, Listing 8-6 could be rewritten as shown in Listing 8-7.

Parameter Binding

PySQLite supports parameter binding by both position and name. This can also be done using the cursor's execute() method, which we introduced in Listing 8-6. Compilation, binding, and the first step of execution are performed in the one call to execute(). You specify positional parameters by passing a tuple as the second argument to execute(). You specify named parameters by passing a dictionary rather than a tuple. Listing 8-8 illustrates both forms of parameter binding.

This model does not support the reuse of compiled queries (sqlite3_reset()). To do this, use Cursor.executemany(). While the DB API specifies that executemany should take a list of parameters, PySQLite extends it to accommodate iterators and generators as well. For example, the canonical form is shown in Listing 8-9.

But you could just as easily use a generator, as shown in Listing 8-10.

In both Listing 8-9 and Listing 8-10, we use the power of parameter binding with a single compilation of the query in question. The query is reused for each item in the sequence or iterator, improving the overall performance for such batches of identical queries.

TRANSACTION HANDLING IN PYSQLITE

User-Defined Functions

You register user-defined functions using create_function(), which takes the following form:

Here, name is the SQL name of the function, args is the number of arguments accepted by the function, and pyfync is the Python function that implements the SQL function. Listing 8-11 shows a Python implementation of hello_newman() using PySQLite.

If you use -1 as the number of arguments, then the function will accept a variable number of arguments.

Aggregates

PySQLite implements user-defined aggregates as Python classes. These classes must implement step() and finalize() methods. You register aggregates using Connection::create_aggregate(), which takes three arguments: the function's SQL name, the number of arguments, and the class name that implements the aggregate. Listing 8-12 implements a simple SUM aggregate called pysum.

APSW as an Alternative Python Interface

APSW is written and maintained by Roger Binns. Detailed information on APSW can be found at http://code.google.com/p/apsw/. Although APSW and pysqlite share some common capabilities and a very similar syntactical style, APSW goes further in providing a range of additional features and API mapping points. Rather than repeating very similar Python code samples, we'll outline the key benefits of APSW and allow you to explore them as you want.

Support for virtual tables, VFS, blob I/O, backups, and file control: APSW provides comprehensive support for the latest features provided by SQLite, as well as database management tasks that aren't provided through pysqlite's narrower focus on DBAPI conformity.

APSW Python Shell: APSW incorporates an extensible shell that can be used in place of the SQLite command interface.  This includes great features such as color-coded syntax, CSV formatting and handling, strictly correct error handling, and additional formatting options for data and messages.

Consistent transaction handling: APSW always performs transactions as you would expect, rather than mimicking pysqlite's attempt to intuitively handle them without your involvement.

There are also a host of other nice little (and even major) features that make APSW a joy to work with. You can review all of these capabilities at http://apidoc.apsw.googlecode.com/hg/pysqlite.html.

Ruby

The Ruby extension was written by Jamis Buck of 37 Signals and is now maintained by a group of developers. You can obtain detailed information on the extension as well as the source code at http://rubyforge.org/projects/sqlite-ruby. The complete documentation for the SQLite 3 extension can be found at http://sqlite-ruby.rubyforge.org/sqlite3/, though the layout is a little daunting.

Installation

The extension can be built in two ways: with or without SWIG. The Ruby configuration script will automatically figure out whether SWIG is available on your system. You should build the extension with SWIG (the C extension), because that implementation is more stable. If your operating system uses a well-developed package management and repository system (like Debian, Ubuntu, and so on), you'll also find packages available for your system there.

At the time of this writing, the current version of sqlite-ruby is 1.3.1. To build from source, fetch the tarball from http://rubyforge.org/projects/sqlite-ruby. Unpack the tarball, and run three setup commands:

If you have Ruby gems installed, you can get Ruby to do everything for you in one step:

If you are installing from a repository, ensure you check your installed version of Ruby and match the right version from the repository. Because old versions of SQLite also enjoy support, ensure you install the libsqlite3-ruby or equivalent package for your platform. For example, under Ubuntu:

Connecting

To load the SQLite extension, you must import the sqlite module, using either load or require, as follows:

You connect to a database by instantiating a SQLite::Database object, passing in the name of the database file. By default, columns in result sets are accessible by their ordinal. However, they can be accessed by column name by setting Database::results_as_hash to true:

Query Processing

The Ruby extension follows the SQLite API quite closely. It offers both prepared queries and wrapped queries.

Prepared queries are performed via Database::prepare(), which passes back a Statement object, which holds a sqlite3_stmt structure. You execute the query using Statement's execute method, which produces a ResultSet object. You can pass the Statement a block, in which case it will yield the ResultSet object to the block. If you don't use a block, then the Statement will provide the ResultSet object as a return value. ResultSet is used to iterate over the returned rows. Internally, it uses sqlite3_step(). You can get at the rows in ResultSet either through a block using the each() iterator or by using a conventional loop with the next() method, which returns the next record in the form of an array or nil if it has reached the end of the set. Listing 8-13 shows prepared queries in action in Ruby.

It is important to call Statement.close() when you are done to finalize the query. Because a Statement object holds a sqlite3_stmt structure internally, each subsequent call to execute thus reuses the same query, avoiding the need to recompile the query.

Parameter Binding

The Ruby extension supports both positional and named parameter binding. You bind parameters using Statement::bind_param() and/or Statement::bind_params(). bind_param() has the following form:

If param is a Fixnum, then it represents the position (index) of the parameter. Otherwise, it is used as the name of the parameter. bind_params() takes a variable number of arguments. If the first argument is a hash, then it uses it to map parameter names to values. Otherwise, it uses each argument as a positional parameter. Listing 8-14 illustrates both forms of parameter binding.

If you don't need to use parameters, a shorter way to process queries is using Database::query(), which cuts out the Statement object and just returns a ResultSet, as shown in Listing 8-14.

Listing 8-14. Using the Database::query() Method in Ruby

Like Statement objects, Result objects are also thin wrappers over statement handles and therefore represent compiled queries. They can be rerun with a call to reset, which calls sqlite3_reset() internally and reexecutes the query. Unlike Statement objects however, they cannot be used for bound parameters.

Other, even shorter, query methods include Database::get_first_row(), which returns the first row of a query, and Database::get_first_value(), which returns the first column of the first row of a query.

User-Defined Functions

User-defined functions are implemented using Database::create_function(), which has the following form:

Here, name is the name of the SQL function, args is the number of arguments (-1 is variable), and text_rep corresponds to the UTF encoding. Values are UTF8, UTF16LE, UTF16BE, UTF16, and ANY. Finally, the function implementation is defined in the block. Listing 8-15 illustrates a Ruby implementation of hello_newman().

This program produces the following output:

---

---

Java

Like Python mentioned earlier, a wealth of Java extensions are available for SQLite. These fall into two broad camps: the JDBC drivers (sometimes with added goodies) and complete reimplementations of SQLite in Java. We'll cover the javasqlite wrapper written by Christian Werner, who wrote the SQLite ODBC driver as well. It includes both a JDBC driver and a native JNI extension, which closely shadows the SQLite C API. You can download the extension from www.ch-werner.de/javasqlite/overview-summary.html#jdbc_driver.

The main class in the JNI extension is SQLiteDatabase. Most of its methods are implemented using callbacks that reference the following interfaces:

• SQLite.Callback
• SQLite.Function
• SQLite.Authorizer
• SQLite.Trace
• SQLite.ProgressHandler

The SQLite.Callback interface is used to process result sets through row handlers, as well as column and type information. SQLite.Authorizer is a thin wrapper over the SQLite C API function sqlite3_set_authorizer(), with which you can intercept database events before they happen. SQLite.Trace is used to view SQL statements as they are compiled, and it wraps sqlite3_trace(). SQLite.ProgressHandler wraps sqlite3_progress_handler(), which is used to issue progress events after a specified number of VDBE instructions have been processed.

Installation

The current version requires JDK 1.4 or newer. The extension uses GNU Autoconf, so building and installing requires only three steps:

The configure script will look for SQLite and the JDK in several default locations. However, to explicitly specify where to look for SQLite and the JDK, several command-line options are available.

--with-sqlite=DIR to specify the sqlite v2 location

--with-sqlite3=DIR to specify the sqlite v3 location

--with-jdk=DIR to specify the JDK location.

To specify the place where the resulting library should be installed (libsqlite_jni.so file), use the --prefix=DIR option. The default location is /usr/lib/jvm/java-6-sun/jre/lib/i386 (though this may vary depending on your distribution of Linux or use of another Unix flavor or Mac OS X, such as /usr/local/lib, for example.) To specify where the sqlite.jar is to be installed, use the --with-jardir=DIR option. The default is /usr/lib/jvm/java-6-sun-1.6.0.20/jre/lib/ext/sqlite.jar (again, this may vary depending on the age and type of your distribution). This file contains the high-level part and the JDBC driver. At runtime, it is necessary to tell the JVM both places with the -classpath and -Djava.library.path=.. command-line options.

For Windows, the makefiles javasqlite.mak and javasqlite3.mak are provided in the distribution. They contain some build instructions and use the J2SE 1.4.2 from Sun and Microsoft Visual C++ 6.0. A DLL with the native JNI part (including SQLite 3.7.0) and the JAR file with the Java part can be downloaded from the web site.

Connecting

You connect to a database using SQLiteDatabase::open(), which takes the name of the database and the file mode, as shown in Listing 8-16. The file mode is an artifact from SQLite 2 API and is no longer used. You can provide any value to satisfy the function. The code in Listing 8-16 is taken from the example file SQLiteJNIExample.java. The full code from this example illustrates all the main facets of database operations: connecting, querying, using functions, and so forth.

Query Processing

Your queries are issued using SQLiteDatabase::compile(). This function can process a string containing multiple SQL statements. It returns a VM (virtual machine) object that holds all the statements.

The VM object parses each individual SQL statement on each call to compile(), returning true if a complete SQL statement was compiled and false otherwise. You can therefore iterate through all SQL statements in a loop, breaking when the VM has processed the last statement.

When the VM has compiled a statement, you can execute it using VM::step(). This function takes a single object, which implements a SQLite.Callback interface. The example uses a class called Row for this purpose, which is shown in Listing 8-17.

The SQLite.Callback interface has three methods: columns(), types(), and newrow(). They process the column names, column types, and row data, respectively. Each call to VM::step() updates all of the column, type, and row information.

The use of VM is illustrated in the query() function of the example, which is listed in Listing 8-18.

The SQLite.Database::exec() performs self-contained queries and has the following form:

The params array corresponds to %q or %Q parameters in the SQL statement. An example is shown in Listing 8-19.

Note that this is not the same thing as parameter binding. Rather, this is sprintf style substitution using sqlite3_vmprintf() in the SQLite C API.

User-Defined Functions and Aggregates

The SQLite.Function interface is used to implement both user-defined functions and user-defined aggregates. The hello_newman() function in Java is illustrated in Listing 8-20.

Notice that the step() and last_step() functions, while specific to aggregates, must also be implemented even though they do nothing in user-defined functions. This is because the SQLite.Function interface defines methods for both functions and aggregates. This class is registered using SQLite.Database.create_function(). The function's return type must also be registered using SQLite.Database.function_type(). Listing 8-21 illustrates using the Java implementation of hello_newman().

JDBC

The Java extension also includes support for JDBC. To use the driver, specify SQLite.JDBCDriver as the JDBC driver's class name. Also, make sure you have sqlite.jar in your class path and the native library in your Java library path. The JDBC URLs to connect to a SQLite database have the format jdbc:sqlite:/path, where path has to be specified as the path name to the SQLite database, for example.

Currently, the supported data types on SQLite tables are java.lang.String, short, int, float, and double. Some support exists for java.sql.Date, java.sql.Time, and java.sql.Timestamp. A connection property daterepr is used to define behavior for these data types. A value of daterepr=Julian sets SQLite to interpret all insert/updates of these data types as a floating-point conversion to the equivalent Julian Day. Any other value switches behavior to use the string formats YYYY-mm-dd for date, HH:MM:SS for time, and YYYY-mm-dd HH:MM:SS.f for timestamp.

Other data type mapping depends mostly on the availability of the SQLite pragmas show_datatypes and table_info. Enough basic database metadata methods are implemented such that it is possible to access SQLite databases with JDK 1.4 or newer and the iSQL-Viewer tool.

Listing 8-22 is a simple example (located in SQLiteJDBCExample.java) of using the JDBC driver to query the foods table.

This example produces the following output:

---

---

Tcl

SQLite's author wrote and maintains the Tcl extension. All of SQLite's testing code is implemented in Tcl and thus uses the SQLite Tcl extension. It is safe to say that this extension is both stable and well tested itself. The Tcl extension is included as part of the SQLite source distribution. You can find complete documentation for this extension on the SQLite web site: www.sqlite.org/tclsqlite.html.

Installation

The SQLite GNU Autoconf script will automatically search for Tcl and build the Tcl extension if it finds it. The recommended way to build the TCL interface is to use one of the tarballs or precompiled bindings for the Tcl extension for either Linux and Windows available on the SQLite web site. A simple “configure && make” invocation should see the TCL extension compiled and installed in the default location, so TCL and TCL/Tk scripts can load the extension normally. You'll also find the requisite libraries in the package repositories of various distributions. For example, under Debian or Ubuntu, you can add the Tcl bindings as follows:

As this chapter is being written, there is also talk of including SQLite by default in TCL version 8.6, which will make the process even easier!

Connecting

The SQLite extension is located in the sqlite3 package, which must be loaded using the package require directive. To connect to a database, use the sqlite3 command to create a database handle. This command takes two arguments: the first is the name of the database handle to be created, and the second is the path to the database file. The following example illustrates connecting to a database:

The usual database connection rules apply. Passing the value :memory: instead of a file name will create an in-memory database. Passing in the name of a new file will create a new database, and so on. The database handle returned corresponds to a connection to the specified database; however, it is not yet open—it does not open the connection until you try to use it. The database handle is the sole object through which you work with the database.

To disconnect, use the close method of the database handle. This will automatically roll back any pending transactions.

Query Processing

The extension executes queries using the eval method, which can process one or more queries at a time. eval can be used in several ways. The first way is to iterate through all records in a script following the SQL code. The script will be executed once for each row returned in the result set. The fields for each row are set as local variables within the script. Here's an example:

There is another form in which you can assign the field values to an array. To do this, specify the array name after the SQL and before the script. Here's an example:

Both of the previous code snippets produce the following output:

---

---

If you don't provide a script, eval will return the result set. The result set is returned as one long list of values, leaving you to determine the record boundaries. You can see this behavior in the following statement:

This will return a list in variable $x that is six elements long:

This corresponds to three records, each of which has three fields (id, type_id, and name).

For non-select statements, eval returns information regarding modified records, as illustrated in Listing 8-23.

The code in Listing 8-23 produces the following output:

---

---

Transaction scope can be handled automatically within Tcl code using the transaction method. If all code inside the transaction method's script runs without error, the transaction method will commit; otherwise, it will invoke a rollback. For example, if you wanted to perform the code from Listing 8-23 in a single transaction, you would have to check the status of each command after its execution. If it failed, then you would roll back the transaction and abort any further commands. The more commands you have to run in the transaction, the messier the code will get. However, all of this can be done automatically with the transaction method, as illustrated in Listing 8-24.

Now, if any of the commands fail, transaction will roll back all commands without having to check any return codes. Transaction also works with your existing transactions, working within a transaction that's already started. It will work within the already-started transaction and not attempt a commit or rollback. If an error occurs, it just aborts the script, returning the appropriate error code.

User-Defined Functions

User-defined functions are created using the function method, which takes the name of the function and a Tcl method that implements the function. Listing 8-25 illustrates an implementation of hello_newman() in Tcl.

The code for listing 8-25 produces the following output:

---

---

PHP

Since the advent of PHP version 5, SQLite has been part of the PHP standard library. If you have PHP 5 or newer, you have SQLite installed on your system as well. Like many of the other languages we've discussed, PHP presents you with a wealth of interface options.

PHP 5.1 introduced a new database abstraction layer called PHP Data Objects (PDO), which has grown to become one of the most popular database abstraction layers for PHP. This API uses drivers to support a standard database interface. The PDO interface is an OO interface that is very similar to the OO interface in the SQLite extension. Since PDO is an abstraction layer, it is meant to accommodate many different databases and is therefore somewhat generic. Despite this, it is still possible for PDO drivers to provide access to database-specific features as well. As a result, the PDO drivers provide a complete OO interface that works well with all the features of SQLite.

Installation

Installation of PHP is quite straightforward but beyond the scope of this book. You can find detailed instructions on building and installing PHP in the documentation on the PHP web site: www.php.net.

Connections

There are two important issues to consider before you open a database: location and permissions. By default, the file path in PHP is relative to the directory in which the script is run (unless you provide a full path, relative to the root file system). So if you specify just a database name, you are opening or creating a database within the public area of your web site, and the security of that database file depends on how the web server is configured.

Since SQLite databases are normal operating system files just like HTML documents or images, it may be possible for someone to fetch them just like a regular document. This could be a potential security problem, depending on the sensitivity of your data. As a general security precaution, it is good idea to keep database files outside of public folders so that only PHP scripts can access them. As an alternative, more sophisticated security can be achieved by limiting access to the SQLite database file using .htaccess controls or equivalent security techniques.

Ultimately, whatever permissions are used must allow the web server process running PHP both read and write access to the database files. Both of these are administrative details that must be addressed on a case-by-case basis.

In PDO, connections are encapsulated in the PDO class. The constructor takes a single argument called a data source name (DSN). The DSN is a colon-delimited string composed of two parameters. The first parameter is the driver name, which is normally sqlite and corresponds to SQLite version 3. The second parameter is the path to the database file. If the connection attempt fails for any reason, the constructor will throw a PDOException. The following example connects to our SQLite foods.db database:

Queries

PDO is made up of two basic classes, PDO and PDOStatement. PDO represents a connection, which internally contains a sqlite3 structure, and PDOStatement represents a statement handle, which internally contains a sqlite3_stmt structure. The query methods in the PDO class closely follow the methods in the SQLite C API. There are three ways to execute queries:

• exec(): Executes queries that don't return any data. Returns the number of affected rows, or FALSE if there is an error. This mirrors sqlite3_exec().
• query(): Executes a query and returns a PDOStatement object representing the result set, or FALSE if there is an error.
• prepare(): Compiles a query and returns a PDOStatement object or FALSE if there is an error. This offers better performance than query() for statements that need to be executed multiple times, because it can be reset, avoiding the need to recompile the query.

Additionally, transaction management in PDO can be performed through a method invocation using beginTransaction(), commit(), and rollback(). Listing 8-26 shows a basic example of using the PDO class to open a database and perform basic queries within a transaction.

PDO uses the setAttribute() and getAttribute() methods to set various database connection parameters. The only parameter that applies to SQLite is PDO_ATTR_TIMEOUT, which sets the busy timeout.

The prepare() method uses a SQLite statement to navigate through a result set. It also supports both named and positional parameters. To bind a SQL parameter, there must be some associated variable in PHP to bind it to. PHP variables are bound to parameters using PDOStatement::bindParam(), which has the following declaration:

The parameter argument specifies the SQL parameter. For numbered parameters, this is an integer value. For named parameters, it is a string. The variable argument is a reference to a PHP variable to bind. The data_type argument specifies the data type of the variable. Finally, the length argument specifies the length of the value if the variable is a string. The value specifies the maximum length of the string to return. Listing 8-27 is a complete example of using positional parameters.

Named parameters work in a similar fashion. When you bind these parameters, you identify them by their names (rather than integers). Listing 8-28 is a modification of the previous example using named parameters.

PDO also allows you to bind columns of result sets to PHP variables. This is done using PDOStatement::bindColumn(), which has the following declaration:

The column argument refers to either the name of the column or its index in the SELECT clause. The param argument is a reference to a PHP variable, and the type argument specifies the type of the PHP variable. The following example binds two variables $name and $type_id to a result set:

User-Defined Functions and Aggregates

User-defined functions are implemented using sqliteCreateFunction(). Aggregates are implemented using sqliteCreateAggregate(). sqliteCreateFunction() has the following form:

The arguments are defined as follows:

• function_name: The name of the function as it is to appear in SQL
• callback: The PHP (callback) function to be invoked when the SQL function is called
• num_args: The number of arguments the function takes

The following example is a PHP implementation of hello_newman():

You create aggregates in a similar fashion using the sqliteCreateAggregate function, declared as follows:

The following code is a simple implementation of the previous SUM aggregate, called phpsum:

Summary

You've now experienced a brief survey of several different language extensions and how they work with SQLite. Although using SQLite with the C API is quite straightforward, using SQLite in language extensions is considerably easier. Many of the concepts are very similar, and many of the extensions map in a one-to-one fashion to the underlying SQLite C API. As you can see, there are many things in common even in cases where an extension conforms to a language-specific database API. All queries ultimately involve a connection object of some kind, which maps to an internal sqlite3 structure, and a statement or cursor object, which internally maps to a sqlite3_stmt structure.

These extensions make using SQLite convenient and easy, increasing accessibility to many more applications ranging from system administration to web site development. There has been an explosion in the number of extensions in recent years, and you can find out about the (literally) hundreds of available extensions on the SQLite wiki at www.sqlite.org/cvstrac/wiki?p=SqliteWrappers.