Re:The lack of proper “alter table” support in SQLite
by saurik on Sunday June 16, 2013 @04:43AM
(#5887883)
A sub-select is not a recursive CTE; think more along the lines of "find me all children of this Hacker News post #" where the scheme is simply "id | parent-id | poster | text": to do this requires recursion. I could totally be misunderstanding the reason you bring them up, however.
Re:The lack of proper “alter table” support in SQLite
by rogerbinns on Sunday June 16, 2013 @10:38AM
(#5888563)
I brought up subselects because they are a solution some of the time to some of the problems.
The other important thing SQLite has available is user defined functions. Because SQLite is running in the same process as your query code, you can add your own scalar and aggregate functions that do anything you want.
It also has a virtual table mechanism so you can provide tables of any shape and contents you want based on other data.