Converting CTE to SQLiteData CommonTableExpression API

[fracturedsoftware]

I’m trying to convert the following CTE using the CommonTableExpression API but not really getting anywhere. It orders hierarchical rows in prefix order using a computed path column:

"""
WITH RECURSIVE tree AS (
SELECT *,
printf('%04d', position) AS path
FROM DBListItem
WHERE parentID IS NULL

                UNION ALL

                SELECT child.*,
                       tree.path || '.' || printf('%04d', child.position)
                FROM DBListItem AS child
                JOIN tree ON child.parentID = tree.id
            )
            SELECT id, parentID, position, name, isFolder
            
            FROM tree
            ORDER BY path
            """

Any help would be appreciated. Thanks!