SQLite allows a developer to expand the SQL environment by creating custom SQL functions. Although these functions are used in SQL statements, the code to implement the function is written in C.
SQLite supports three kinds of custom functions.
Simple
scalar functions are the first type. These take some set of
parameters and return a single value. An example would be the built-in function
abs(), which takes a single numeric
parameter and returns the absolute value of that number.
The second type of function is an
aggregate function, or aggregator.
These are SQL functions, such as sum() or
avg(), that are used in conjunction with
GROUP BY clauses to summarize or
otherwise aggregate a series of values together into a final result.
The last type of custom function is a
collation. Collations are used to define a custom sort
orders for an index or an ORDER BY clause.
Conceptually, collation functions are quite simple: they take two text values and
return a greater than, less than, or equal status. In practice, collations can
become quite complex, especially when dealing with natural language
strings.
This chapter will also take a look at how to package up a set of custom features into an SQLite extension. Extensions are a standard way to package custom functions, aggregations, collations, virtual tables (see Chapter 10), or any other custom feature. Extensions are a handy and standardized way to bundle up sets of related functions or customizations into SQL function libraries.
Extensions can be statically linked into an application, or
they can be built into
loadable extensions. Loadable extensions act as “plug-ins”
for the SQLite library. Loadable extensions are a particularly useful way to load
your custom functions into sqlite3, providing
the ability to test queries or debug problems in the same SQL environment that is
found in your application.
The source code to the examples found in this chapter can be found in the book download. Downloading the source will make it much easier to build the examples and try them out. See Example Code Download for more information on where to find the source code.