News for hackers. Stuff that matters.
Re:SQLite now allows multiple recursive SELECT statements in... by sradman on Wednesday October 21, 2020 @07:14AM (#24846632)
Recursive Common Table Expressions (CTE) are hard to understand until you've worked through at least one real-world example. The SQLite documentation on the WITH Clause has a couple of simple examples [1] for an Org Chart and a Family; both represent hierarchical data structures.

The Fossil discussion involves the SCM's representation of a File System, probably the most common hierarchical data structure we regularly encounter. Hierarchical data structures are notoriously hard in SQL. Recursive CTEs are not easy but they are useful and efficient for hierarchical data.

[1] https://www.sqlite.org/draft/lang_with.html#hierarchical_que...

Re:SQLite now allows multiple recursive SELECT statements in... by sradman on Wednesday October 21, 2020 @07:32AM (#24846738)
Another good writeup is included in the SQL Anywhere documentation [1] which has a good example of a parts explosion problem [2]. I don't know if the syntax works in SQLite but it might be a good exercise to get it to work.

[1] http://dcx.sap.com/1200/en/dbusage/ug-commontblexpr.html

[2] http://dcx.sap.com/1200/en/dbusage/parts-explosion-cte-sqlug...