Ayende @ Rahien

My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:


+972 52-548-6969

, @ Q c

Posts: 6,131 | Comments: 45,568

filter by tags archive

Find out the right directory

time to read 1 min | 67 words

Another interesting challenge. Given the following API:

public interface IFileRepository
	void Save(IFile file);

	IFile[] GetFiles(string path, Recursion recursion);

public enum Recursion

And assuming that you are saving to a relational database, how are you going to build the GetFiles method?

Oh, and just to make things interesting, Save(IFile) may only perform a single SQL INSERT statement.



1) void SaveFile(IFile)

Are you saving the file as a BLOB? If it´s true and you are using SQL SERVER you´ll need more than just one INSERT statement on T-SQL to do that.

2) IFile[] GetFiles(string, Recursion)

a) If it´s a small dataset and you won´t exceed 32 nesting levels you can use a recursive Table User Defined Function that joins itself.

b) If it´s a large dataSet there is a nice feature in SQL SERVER 2005 (ANSI 99) that enable you to do recursive queries: http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/recursivequeriesinsql1999andsqlserver2005/1846/

c) If it´s a large dataSet and you don´t have SQL SERVER 2005, you could use a temp table, a while statement and a stack(using a table variable) to emulate recursion inside the stored procedure body.

d) [Unconventional Ways To Avoid SQL Recursion]


It seens a good option, may be the better option regarding performance, but you need to keep the "path" column updated.

What have you done?

Ken Egozi

naive (and from the top of my head):








Unique index on (Path, FileName)



string path = Path.GetPath(file.FullFileName);

string fileName = Path.GetFileNAmeWithoutPath(file.FullFileName);

if (path.EndsWith("\") == false) path += "\";

... INSERT INTO Files (@path, @content) ...

 Parameter(@path, file.Path),


GetFiles(path, recursive)


if (path.EndsWith("\") == false) path += "\";

if (recursive)


path += "%";


q.Add(Expression.Like("Path", path);

return q.List();


Rik Hemsley

I'd start by using this method to represent the filesystem structure in the database: http://www.codeproject.com/KB/database/ModelingDAGsonSQLDBs.aspx

This will allow GetFiles to work recursively with only joins.

Ayende Rahien


Note that you need to support Recursion.OneLevel as well.

Ayende Rahien

1/ Bruno, no I am not saving the file content, just the file meta data.

2/ There is a drastically simpler solution

Ayende Rahien


But this requires more than a single INSERT in the Save, no?

Rik Hemsley

What's the reason for the single INSERT restriction? If it must stay, you will have to go for the 'materialized paths' approach as mentioned by a couple of people above.

Ayende Rahien


My implementation did it with a single insert. That is all

Joshua Cauble

It's simple use XML to the DB. Both sql 2000 and Sql 2005 both support SQL data sets. It would allow you to get you data however you need to via a procedure call and it would return a XML object that you can then put into your object format. For the Save It's the same way just have you code build up the XML object and call you Save proc on the database with it.

Since it's xml you could even send it to a webservice or some other solution it doesn't even have to be sql server.

Especially since you are only saving metadata.

Ayende Rahien


No XML, please. NO XML.

Steve Campbell

1) Save the file path as a string

2) Use LIKE to find recursive matches.


Simpler than SQL SERVER 2005 recursive query approach and without update the "path" column after inserts/updates ?

That´s a great piece of SQL.

Interesting, show me and I´ll call you ninja.

You´re already a .net ninja, but I´d call you a SQL ninja :)

Steve Campbell

OneLevel does add complexity to the simple file-path approach. Depending on volume, it can be done outside of SQL (filter the full recursive results), or with more complex substring operations inside of SQL. I'm too lazy to write an example, but I'm confident it would work.

Ayende Rahien

Volume is several hundreds of thousands of rows.

You don't want that outside of the DB. Oh, and the DB doesn't support complex functions

Steve Campbell

Ok, now you're forcing me to think. Not nice.

Paths are stored with trailing backslash. Then...

Exact is:

= 'foo/bar/'

Recursive is:

LIKE 'foo/bar/%'

OneLevel is:

LIKE 'foo/bar/%/' AND NOT LIKE 'foo/bar/%/%/'


I'm persistence ignorant (literally :D), so maybe I'm totally missing the point

But, for the 1 level recursion, wouldn't this work?:

SELECT foo FROM bar WHERE foo.Path LIKE /whatever/% AND foo.Path NOT LIKE /whatever/%/%


Dammit, beaten again. :D

At least it seems I was right this time. (I'm still puzzled int that you guys considered this ninja stuff, it was so obvious to me I still think I must be utterly missing the point.)

Ayende Rahien


I simply did not think of this

Comment preview

Comments have been closed on this topic.


  1. RavenDB Conference 2016–Slides - 2 hours from now
  2. Proposed solution to the low level interview question - about one day from now

There are posts all the way to Jun 02, 2016


  1. The design of RavenDB 4.0 (14):
    26 May 2016 - The client side
  2. RavenDB 3.5 whirl wind tour (14):
    25 May 2016 - Got anything to declare, ya smuggler?
  3. Tasks for the new comer (2):
    15 Apr 2016 - Quartz.NET with RavenDB
  4. Code through the looking glass (5):
    18 Mar 2016 - And a linear search to rule them
  5. Find the bug (8):
    29 Feb 2016 - When you can't rely on your own identity
View all series



Main feed Feed Stats
Comments feed   Comments Feed Stats