Team LiB
Previous Section Next Section

What Makes SQLite Unique?

To both appreciate and effectively use SQLite, it is important to understand the differences between it and a classic relational database package such as MySQL. Unlike MySQL, which operates using a client/server model, SQLite does not need a server to store data in a database. Rather, SQLite is a server in itself, enabling users to store and manipulate database files directly using SQL statements.

Starting in PHP 5.0, SQLite (both the extension and library) are bundled as a standard package. From a developer's perspective, this bundling of SQLite offers many powerful benefits. Essentially, SQLite completely removes any need to write proprietary flat-file storage systems, replacing them with a standardized SQL-based system. SQLite databases can also be created completely in memory, providing an entirely new set of techniques for data analysis when you're working with complex data sets. Considering that all this functionality is provided without the need for additional software, relational database servers, and so on, it is a powerful addition to PHP indeedif you can write a file to the file system, you can use SQLite in your scripts.

General Differences Between SQLite and MySQL

Although SQLite is very similar, in practice, to another database engine such as MySQL, a number of small details separate the two. These differences, if left unexplained, can lead to headaches when you're coding SQLite-based applications. For the purposes of this section, SQLite will be compared only to MySQL. Note that this section is only an overview of the differences; for a complete discussion of them, review the SQLite documentation at http://www.sqlite.org/.

SQLite Is Typeless

The first major difference between SQLite and MySQL is that SQLite is a typeless database engine. Unlike MySQL, which stores data differently in the database depending on the data type of the information to be stored, in SQLite these and all other data types can generally be used interchangeably.

This major difference between databases can lead to what may seem like malformed SQL statements being completely valid in SQLite. For instance, consider the following CREATE TABLE statement in which no data types are provided at all:

CREATE TABLE myvalues(id, name, value);

Although SQLite is technically typeless, it does allow types to be defined when you create a table. Many of the standard types you know from MySQL programming are supported, including CHAR, TEXT, BLOB, CLOB. If any of these are included in the column definition, the column is considered textual; otherwise, it is considered numeric.

Although SQLite looks for particular substrings within a data type to define how it will be treated, in SQLite any character label can be used as a data type. Specifically, a data type can be any string followed by a maximum of two optional integer parameters. Ultimately, under most circumstances this means that all the data types you are accustomed to in MySQL are availablealong with ones that you make up on your own. In the CREATE example statement that follows, a four-column table is created with column names id, name, address, and zip, using a variety of data types:

CREATE TABLE myvalues(id INTEGER,
                      name VARCHAR(255),
                      address ADDRESS TEXT,
                      zip NUMERIC(10, 5));

In SQLite, as previously mentioned, type definitions serve mostly as descriptive labels for the column they are associated with. Although any label followed by up to two numeric parameters may be used, for the purposes of consistency, you should use standard SQL data types. It is important to remember that when you're defining tables, all columns designed to hold strings must have one of the substrings provided earlier identifying it as textual data.

Another quirk in SQLite, when it comes to data types, is a special case used when you create an auto-incrementing key column similar to the use of the MySQL AUTO_INCREMENT type flag. In SQLite, defining a column as INTEGER PRIMARY KEY is equivalent to using AUTO_INCREMENT within MySQL, and it must contain a 32-bit signed integer (inserting a noninteger value will result in an error). Thus, the following MySQL and SQLite CREATE TABLE statements are functionally identical:

-- The MySQL version
CREATE TABLE autoinc(id INT AUTO_INCREMENT PRIMARY KEY);
-- The SQLite version
CREATE TABLE autoinc(id INTEGER PRIMARY KEY);

NOTE

To create an auto-incrementing primary key in SQLite, the data type INTEGER must be used. A common mistake developers make when using SQLite is attempting to use the INT data type as a shorthand version of INTEGERsomething that would be acceptable in MySQL.


How SQLite Deals with Textual and Numeric Types

As you know, when you're working with data within a SQLite database, the engine data is classified as either textual or numeric. This classification has a direct impact on how data will be compared and/or sorted when a query is executed. The data classification for any given value within SQLite can be determined using the typeof() function:

SELECT typeof(123 + 456);
     numeric
SELECT typeof("foobar");
     text
SELECT typeof("foobar" + 123);
     numeric
SELECT typeof("foo" || 123);
     text

NOTE

In the previous example of the typeof() function, the '||' operator is not a Boolean OR as you may be accustomed to. Rather, the || operator is a string-concatenation operator. Thus, the expression '"foo" || 123' evaluates to the string "foo123".


As you can see, the SQLite engine is fairly intuitive when determining the classification of a given piece of data. When you use classification-specific operators, for instance, (addition, subtraction, and so on) the result is always numeric. Likewise, using a text-only operator, such as the string concatenation operator, always results in a textual classification.

Now that you are familiar with the use of classifications in SQLite, you'll use these classifications to define how two pieces of data will be compared and/or sorted.

Comparing Two Textual Columns

The first type of comparison is when both columns in question are considered textual data. In these cases, SQLite will do a byte-by-byte comparison of the relevant data to determine equality of two given columns. Hence, comparing two textual columns will be done in a case-sensitive fashion.

Comparing Two Numeric Columns

When you're dealing with values considered numeric by SQLite, the columns will be compared to each other as numeric values using classical math rules. For instance, the value 1 is greater than 14, but less than 5.

Comparing Two Columns of Conflicting Types

In the final case, when the two columns being compared are of different types (one is textual and the other is numeric), SQLite handles the situation by always considering the numeric column "less than" the textual column.

How SQLite Treats NULL Values

When dealing with the NULL value, every database seems to handle different operations slightly differently. In an attempt to be consistent with other databases, SQLite handles NULL values in the same fashion as Oracle, PostgreSQL, and DB2. The following list defines the behavior of SQLite during operations when the NULL value is encountered:

  • Adding NULL to a value evaluates to NULL.

  • Multiplying by NULL evaluates to NULL.

  • NULL values are distinct in a UNIQUE column.

  • NULL values are not distinct in SELECT DISTINCT.

  • NULL values are not distinct in a UNION.

  • Comparison of two NULL values is true.

  • Binary operators (NULL or 1) is true.

Accessing a Database from Multiple Processes

When working in a Web environment, it's quite possible that multiple instances of a particular PHP script will attempt to access a given SQLite database concurrently. However, unlike many large RDBMS systems such as MySQL, SQLite has a few limitations that must be considered. This issue is relevant only when data is being written to the database; performing SELECT queries can be done from multiple instances without concern.

When you're working with a database, it is important that any data being written to the database is completed before another write to the same data takes place. This is accomplished by "locking" the data within the database from being written until the last write is complete. In most large RDBMS packages such as MySQL, this locking is specific to either a certain table or even a single row within that table. When writing to a SQLite database, however, this is not the case. Rather, the entire database is locked, thus preventing any writing from taking place to any table until the current write operation is complete. This method of locking has serious implications when it comes to the scalability of SQLite.

Another noteworthy item related to SQLite and locking is the storage of SQLite databases on remote file systems accessed by services such as NFS in Unix-based operating systems or older Windows-based operating systems (specifically Windows 95, 98, or ME) where locking support is questionable.

In general, these SQLite limitations encourage nothing more than an evaluation of the way your PHP scripts plan to utilize it. For small data sets or a database where SELECT statements are much more common than INSERT or UPDATE statements, SQLite will perform at least comparably to, if not outperform, many larger RDBMS packages. Instead, these limitations reflect only the goals of SQLite itselfto be a lightweight and effective RDBMS client and server. Be wary of how you intend to use SQLite. Understanding the differences and limitations of SQLite compared to other RDBMS packages will go a long way to ensuring the proper choice is made.

In short, if you are writing frequently to your database or desire to access the database from multiple different machines, a larger RDBMS package such as MySQL is strongly recommended. However, for a fast, clean, and effective substitute for proprietary flat-file storage mechanisms, SQLite is incredibly useful.

    Team LiB
    Previous Section Next Section