iOS & sqlite, returning structured of rows from table (like bookmark folder list in safari)

Hi all I'm struggling to think of a way to describe my problem so I'm going to use iOS' Safari as an example, as it matches almost exactly what I am trying to achieve.

In safari if you add or edit a bookmark, you are able to choose which folder the bookmark will be placed in. A UITableViewController is shown with a hierarchical list of folders, a child folder/subfolder is shown by having an increased UITableViewCell indent. Here is a screenshot to show what I am referring to: Screenshot

The bookmarks and folders are stored on a single table in an sqlite database like this: Screenshot

So it is easy to work out which folders are parents of which other folders using the id and parent columns.

What I can't work out, is how to get an array/dictionary/set which maintains these relationships (or contains the relevant data) so that I may use it in a UITableViewController?

My current methods (whi开发者_如何学Cch returns an NSMutableArray containing the folder names) executes a SQL statement that returns all folders where parent=0 or NULL, then for each returned folder, executes another SQL statement that gets that folders subfolders, but this only returns 2 levels of folders. I could add more statements, but I am only ever going to return as many levels as I have statements. I dont know whether there are going to be 0 or 1000 levels of subfolders, so I need to find a way of doing this where the code is not reliant on the data.

Hope this makes sense! Would really appreciate it if somebody could help or point me in the right direction - I feel like I could be missing something obvious here!

I would recommend using lineage for this.

Basically it means you have this in your database:

Node    ParentNode  Depth   Lineage
100     NULL        0       /
101     100         1       /100/
102     101         2       /100/101/
103     102         3       /100/101/102/
104     102         3       /100/101/102/
105     102         3       /100/101/102/

See this link for more info:

http://www.sqlteam.com/article/more-trees-hierarchies-in-sql