Extending SQLite: extensions and virtual tables
You write custom functions, aggregates, virtual tables and loadable extensions in C and Python, and apply R*Tree, the session extension and settings for embedded use.
Level 501 · 14 lessons · 12 practice labs
Lessons
Functions
- Application-defined functions
- Custom aggregate and window functions
- A loadable extension in C
- Extension loading safety
- Short task: a distance function
Virtual tables
- The virtual table concept
- Querying a CSV file as a table
- Location queries with R*Tree
- Table-valued functions with generate_series and json_each
- Short task: a virtual table for a log file
Embedded use
- The session extension and changesets
- Compile options and custom builds
- Memory and I/O tuning
- Capstone: a field app database that syncs offline
Practice labs
- Add a function to SQL from Python: Register a function that simplifies Turkish characters with create_function and use it in searches.
- Write a custom aggregate: Write an aggregate that returns the median delivery time and build a per-city report.
- Compute distance with a C extension: Compile and load a C extension that computes haversine distance and use it in a query that finds the nearest depot.
- Check whether extension loading is enabled: Write a short script that checks load_extension is disabled on an app's SQLite connection.
- Show the risk of loading extensions from user input: Show how a load_extension call can be abused in a deliberately weak lab app, then close it.
- Build a hardened SQLite with compile options: Compile sqlite3 with options that drop unneeded features and prove the difference with pragma compile_options.
- Generate a series and find missing days: Generate every day of the month with generate_series and list the days with no sales.
- Query a CSV file like a table: Without importing, define a CSV virtual table and join an external price list with the products table.
- Find points in an area with R*Tree: Load thousands of parcel points into an R*Tree, find those inside a rectangle and compare speed with a plain table.
- List the differences between two databases: Use sqldiff to list changed rows between the field copy and the central copy.
- Apply a changeset to another copy: Record an offline tablet's changes as a changeset with the session extension and apply it to the central database.
- Merge conflicting changes by rule: Write a conflict handler for changesets from two sites that edit the same record and verify the result.
About this course
You write custom functions, aggregates, virtual tables and loadable extensions in C and Python, and apply R*Tree, the session extension and settings for embedded use.
What you will learn
- Functions
- Virtual tables
- Embedded use
Prerequisites
You should be experienced in the topics of the same path up to level 401.
Lab environment
Lessons run in your own Kali Linux terminal that opens in the browser. Nothing to install; the lab starts clean in every lesson.
Frequently asked questions
- Is this course right for my level?
- The course is at level 501; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.