Ayende @ Rahien

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

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 5,968 | Comments: 44,484

filter by tags archive

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

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

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

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

Ken,

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

Rik,

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

Rik,

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

Argh!

No XML, please. NO XML.

Steve Campbell

1) Save the file path as a string

2) Use LIKE to find recursive matches.

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

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

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

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

Alberto,

I simply did not think of this

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

  1. Pointer arithmetic and dynamic HTML generation FTW–at 2 AM - 3 hours from now

There are posts all the way to Jul 28, 2015

RECENT SERIES

  1. Career planning (6):
    24 Jul 2015 - The immortal choices aren't
  2. Production postmortem (4):
    23 Jul 2015 - The case of the native memory leak
  3. API Design (7):
    20 Jul 2015 - We’ll let the users sort it out
  4. What is new in RavenDB 3.5 (3):
    15 Jul 2015 - Exploring data in the dark
  5. The RavenDB Comic Strip (3):
    28 May 2015 - Part III – High availability & sleeping soundly
View all series

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats