Upgrading to PHP 5

User-Defined Functions

In addition to all the built-in SQL functions, such as lower( ) and upper( ), you can extend SQLite to include functions of your own written in PHP. These are known as user-defined functions, or UDFs for short. With a UDF, you embed logic into SQLite and avoid doing it yourself in PHP. This way, you take advantage of all the features inherent in a database, such as sorting and finding distinct entries.

There are two types of UDFs: standard and aggregate. Standard UDFs are one-to-one: when given a single row of data, they return a single result. Functions that change case, calculate cryptographic hashes, and compute the sales tax on an item in a shopping cart are all standard functions. In contrast, aggregate functions are many-to-one: when using an aggregate function, SQLite passes it multiple rows and receives only a single value.

Although it is not a UDF, the most popular aggregate function is count( ), which returns the number of rows passed to it. Besides count( ), most aggregate functions are related to statistics: finding the average, standard deviation, or the maximum or minimum value of a set of data points.

Standard Functions

UDFs are good for chopping up strings so you can perform nonstandard collations and groupings. For example, you want to sort through a list of URLs, maybe from a referrer log file, and create a list of unique hostnames sorted alphabetically. So, http://www.example.com/directory/index.html and http://www.example.com/page.html ...