Nested set

The "nested set index" is an index explicitly maintained by our codebase that allows quickly fetching pages for OurBigBook Web dynamic article tree in pre-order depth first, i.e. the conventional order in which the table of contents and articlees appear in a book. See also: stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database

This technique is also called "closure table" by some authors.

This index is, as the name indicates, an index, i.e. it duplicates information otherwise present in the OurBigBook Web Ref database table, which contains an adjacency list format instead, in the hope that it would be faster to pre-order depth first traverse.

This feature adds considerable complexity to the codebase. Also, updates can be considerably slow, as updating this index for a single article requires updating the index value for most or all other articles as well. We should bechmark it better vs recursive queries.

This index was partly introduced as a helper rather than as a pure speed up, as it is a bit hard to do pre order tree traversal in SQLite due to the lack of arrays. In PostgreSQL we can do it well: stackoverflow.com/questions/65247873/preorder-tree-traversal-using-recursive-ctes-in-sql/77276675#77276675