Ayende @ Rahien

Refunds available at head office

Find out the right directory

Another interesting challenge. Given the following API:

public interface IFileRepository
{
	void Save(IFile file);

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

public enum Recursion
{
	None,
	OneLevel,
	Full
}

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.

Comments

Bruno
03/28/2008 04:20 AM by
Bruno

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]

http://weblogs.asp.net/jezell/archive/2004/03/10/87010.aspx

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
03/28/2008 06:11 AM by
Ken Egozi

naive (and from the top of my head):

TABLE Files

(

Id INT IDENTITY,

Path WHATEVERISSTRING_INTHEDB

FileName WHATEVERISSTRING_INTHEDB

Content WHATEVERISBLOB

)

Unique index on (Path, FileName)

Save(IFile)

{

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
03/28/2008 10:16 AM by
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
03/28/2008 10:35 AM by
Ayende Rahien

Ken,

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

Ayende Rahien
03/28/2008 10:36 AM by
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
03/28/2008 10:42 AM by
Ayende Rahien

Rik,

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

Rik Hemsley
03/28/2008 11:20 AM by
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
03/28/2008 11:34 AM by
Ayende Rahien

Rik,

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

Joshua Cauble
03/28/2008 11:36 AM by
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
03/28/2008 11:53 AM by
Ayende Rahien

Argh!

No XML, please. NO XML.

Steve Campbell
03/28/2008 03:52 PM by
Steve Campbell

1) Save the file path as a string

2) Use LIKE to find recursive matches.

Ayende Rahien
03/28/2008 03:58 PM by
Ayende Rahien

How do you handle OneLevel

Bruno
03/28/2008 04:24 PM by
Bruno

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
03/28/2008 04:30 PM by
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
03/28/2008 04:33 PM by
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
03/28/2008 04:40 PM by
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/%/%/'

alberto
03/28/2008 04:44 PM by
alberto

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/%/%

alberto
03/28/2008 04:54 PM by
alberto

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
03/28/2008 05:50 PM by
Ayende Rahien

Alberto,

I simply did not think of this

Comments have been closed on this topic.