title: Concatenating hierarchical file paths in sqlite3
body: <p>I have the following SQL Schema: </p>
  
  <pre><code> CREATE TABLE Folders(
       id INTEGER PRIMARY KEY, 
       path TEXT NOT NULL, 
       level INTEGER DEFAULT 0,
       parentId INTEGER)
  </code></pre>
  
  <p>an item with parentId != NULL is a root item and has the full path in "path". Subfolders are only saved by their name and a level that describes how far below the root folder they are. I now want to build the full paths for all items from this data.</p>
  
  <p>CTE is not available in SQLite so I created this query to fetch the full paths of items:</p>
  
  <pre><code>select f.id, ifnull(group_concat(p.path, '\')||'\', '')||f.path as path
    from folders f 
    left outer join (select * from folders order by parentId, level) p on (p.parentId=f.parentId or p.id=f.parentId) and p.level &lt; f.level
    group by f.id
  </code></pre>
  
  <p>However this seems to be highly inefficient when I look at the execution plan with "explain". I created an index on parentId, path and level but this does not seem to improve much.</p>
  
  <p>I tried to check <a href="http://www.sitepoint.com/article/hierarchical-data-database/" rel="nofollow">http://www.sitepoint.com/article/hierarchical-data-database/</a> but it has a redirect problem.</p>