There have been a lot of solutions to solve the hierarchy in a relational database. I'm pretty sure that I've tried them all, but ultimately they all fell short for one reason or another. Nested Sets are a beautiful answer, but fall short if you have too much data. Materialized paths are great except that they don't maintain ordered siblings. Many others require complex joins, special triggers, or stored procedures that you can't use in SQLite, or would require you to do something different in each database you support.

Through much trial an error, I solved the problem with a mechanis…