I have put together an advanced SQL/SQLite tutorial focused on SQLite/SQL features facilitating the development of complex SQL queries and reduction of coupling between SQL/database and application business logic. It is a work in progress, but I would greatly appreciate any feedback.

This tutorial consists of three sections. The first section summarizes metadata/reflection pragmas available in SQLite and illustrates how CTEs can facilitate the integration of complementary information available from individual pragmas, including conversion of per object pragma functions to database-wide views.

The second section discusses several SQL design patterns, particularly approaches to manipulating strings containing structured data and providing loosely coupled JSON-based query interfaces. The JSON library facilitates the passage of structured data from the application to the database engine via a single query parameter. The latter accepts multiple arguments packed in a JSON-formatted string, providing a flexible interface and reducing the need for dynamic SQL generation.

The last section applies concepts from the first two sections to modeling a hierarchical category system using the Materialized Paths (MPs) pattern. I propose and discuss a set of design rules for such a system, which should follow file system logic in some aspects, but not others. The combination of CTEs, recursive CTEs, and JSON permits implementing the standard MPs functionality as a set of static parameterized SQL queries abstracting the database integrity features, such as foreign keys, from the application.

Very low contrast. You may want to make your text BLACK, not light-grey.

Usually designing a web page that uses zero-contrast text indicates the uselessness of the information content -- if you cannot do something useful, then at least make it immediately identifiable as useless.

This is weird. Would you kindly share a screenshot? Did you mean the code blocks? They are indeed bad.

I don't think the OP asked about how to fix it, I think what the he meant is that he doesn't understand why you are seeing those low-contrast colours, and asking for a screenshot (because presumably is doesn't look like that for him).

That said, I have to agree, it's hard to read the low-contrast text, it's like the UI indicating text that's been cut (waiting for you to confirm before vanishing it completely). The text isn't quite black, and the page isn't quite white, which means both the background and foreground colours are biased towards the center, aka lowered contrast.

Please make that text black on the site so it can be read without sweating - just in case the content is great.

Displays perfectly on an iPad

You are right. I was trying to figure out what was wrong. I use GitHub Pages with JustTheDocs extension. The present color scheme is default, and I am trying to figure out what needs to be fixed. The text is grey, but it reads fine on my computer. The code blocks have bad colors, I missed it before and I am trying to fix code colors.

My question is when you talk about poor colors, do you mean the code blocks or do you mean the plain text as well?

I was talking about the plain text, the immediate wall of text you are presented with when clicking the link.

That said, something must have changed - it looks perfectly good now.

I have fixed text color. Thank you for pointing out this issue.

Some bits of the SQL snippets still seem low contrast, but I don't know if that is deliberate or not.

---

A technique I use in a complex query is to pass the parameterised arguments to the query in and bind them to an args single-row "table" CTE at the start. Then I can join that in wherever I need and can refer to arguments properly by name in multiple places. The queries where I'm doing this are really quite complicated (with windowed views over hexagonal grids and stuff like that) but I think it is a technique that is in itself simple and can be used by others.

Doing it like that also makes it much easier to test the queries interactively, because all the places where you supply values for test purposes are gathered together at the front of the query instead of being spread out through the whole thing.

An example that illustrates:

WITH RECURSIVE
	args(machine_id, x, y, z, width, height, "depth") AS (
		VALUES (:machine_id, :x, :y, :z, :width, :height, :depth)),
	m AS (SELECT machines.* FROM machines
		JOIN args USING (machine_id)),
	-- The logical rectangle of interest
	rect(x, y, z, local_x, local_y, local_z) AS (
		SELECT x, y, z, 0, 0, 0 FROM args
		UNION
		SELECT (rect.x + 1) % m.width, rect.y, rect.z, local_x + 1, local_y, local_z
			FROM rect, m, args
			WHERE local_x + 1 < args.width
		UNION
		SELECT rect.x, (rect.y + 1) % m.height, rect.z, local_x, local_y + 1, local_z
			FROM rect, m, args
			WHERE local_y + 1 < args.height
		UNION
		SELECT rect.x, rect.y, (rect.z + 1) % m."depth", local_x, local_y, local_z + 1
			FROM rect, m, args
			WHERE local_z + 1 < args."depth"),
	-- Boards on the machine in the rectangle of interest
	bs AS (SELECT boards.* FROM boards
		JOIN m USING (machine_id)
		JOIN rect USING (x, y, z)
		WHERE may_be_allocated),
	-- Links between boards of interest
	ls AS (SELECT links.* FROM links
		WHERE links.board_1 IN (SELECT board_id FROM bs)
			AND links.board_2 IN (SELECT board_id FROM bs)
			AND links.live),
	-- Follow the connectivity graph; SQLite magic!
	connected(b) AS (
		SELECT board_id FROM bs JOIN args USING (x, y, z)
		UNION
		SELECT ls.board_2 FROM connected JOIN ls ON board_1 == b
		UNION
		SELECT ls.board_1 FROM connected JOIN ls ON board_2 == b)
SELECT
	bs.board_id
FROM bs JOIN connected ON bs.board_id = connected.b
ORDER BY bs.x ASC, bs.y ASC, bs.z ASC;

It probably could be done better, but it works and I can understand it enough explain to others (at least on my team) what's going on. (No, I'm not handing out the schema. It's... grown moderately large and that's not what I'm talking about here.)

I am still trying to figure out how to fix the colors of the code sections. I colored a few sections pretty much by hand. The syntax highlighting library used by GitHub is closed and not available on GitHub Pages.

Yes, this is a good approach. I have a section Surrogate Variables, which discusses this idea to some extent.

I also tried to rationalize rCTEs to make it look less magical.