# sqlite3 < ~/ctree.sql 
0|Hello world!
0 > 2|First response.
0 > 3|Second response.
0 > 3 > 5|Just another response to the second reply.
0 > 3 > 5 > 6|Go deeper.
0 > 4|Third response.

BEGIN TRANSACTION;

CREATE TABLE 'comments'
(
	'id' INTEGER PRIMARY KEY,
	'parent' INTEGER DEFAULT 0,
	'text' TEXT
);

INSERT INTO "comments" ("id","parent","text") VALUES ('1','0','Hello world!');
INSERT INTO "comments" ("id","parent","text") VALUES ('2','1','First response.');
INSERT INTO "comments" ("id","parent","text") VALUES ('3','1','Second response.');
INSERT INTO "comments" ("id","parent","text") VALUES ('4','1','Third response.');
INSERT INTO "comments" ("id","parent","text") VALUES ('5','3','Just another response to the second reply.');
INSERT INTO "comments" ("id","parent","text") VALUES ('6','5','Go deeper.');

COMMIT;

WITH RECURSIVE ctree (clevel, cpath, id, parent) AS
(
	SELECT
		0,
		0,
		id,
		parent
	FROM comments
	WHERE id = "1"
	UNION
	SELECT
		clevel + 1,
		cpath || " > " || comments.id,
		comments.id,
		comments.parent
	FROM comments
	JOIN ctree
		ON ctree.id = comments.parent
) 
SELECT cpath, text FROM ctree
JOIN comments
ON comments.id = ctree.id
ORDER BY ctree.cpath;