SQLite Functions and Virtual Tables
SQLite provides functions that enable the user to define functions and virtual tables in C. While the API calls themselves are fairly well documented, I found examples somewhat hard to come by. Furthermore, because there's no central archive of user-written, user-defined SQLite functions, many SQLite users are surely writing and re-writing the same or very similar ones. I thought starting such a repository could be my small contribution to the SQLite project, and a way to illustrate what may be some novel uses for it.
Virtual Tables
- Shell file glob expansion
- Calls glob(3) on a provided pattern.
- Returns column of names matching the pattern.
- Directory listing
- Calls opendir(3) and readdir(3) to
list the filenames in a directory.
- CSV file
- Parses a comma-separated value file as a table. BSD license.
User-Defined Functions
- Regular Expressions
- Calls regcomp(3) and regexec(3) on a user-provided pattern for a column or constant
- File Metadata
- Calls stat(2) and strmode(3) on a filename, displays one field from the stat struct.
- These functions are included in readdir.c, discussed above in Virtual Tables.
- Math functions
- Although the SQLite website doesn't make finding it very easy, there is a Contributor's Page, which includes math and string functions contributed by Liam Healy.
Undocumented requirements
I bumped into two issues writing my first functions.
- SQLITE_EXTENSION_INIT1 and SQLITE_EXTENSION_INIT2. These macros define and set the magic sqlite3_api pointer. They are defined in sqlite3ext.h.
- The sqlite3_extension_init is called by the load_extension function to load a shared library containing SQLite extensions. It's documented on the Core Functions page in the SQL language definition, but not among the SQLite functions. Recent versions of the sqlite3 interactive shell support this via the .load meta-command, but that command wasn't listed in the manual as of version 3.7.13.
Notes
The files provided here compile on NetBSD using BSD make.
A word about the license: except as noted, there is none. As of now, all files provided here were written by me (James K. Lowden) for your edification only. If you're interested in using them or contributing some functions of your own, please contact me.