+972 52-548-6969
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 { 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
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?
naive (and from the top of my head):
TABLE Files
(
Id INT IDENTITY,
Path WHATEVER_IS_STRING_IN_THE_DB
FileName WHATEVER_IS_STRING_IN_THE_DB
Content WHATEVER_IS_BLOB
)
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) ...
}
GetFiles(path, recursive)
{
if (path.EndsWith("\") == false) path += "\";
if (recursive)
{
}
q.Add(Expression.Like("Path", path);
return q.List();
}
I'd start by using this method to represent the filesystem structure in the database: http://www.codeproject.com/KB/database/Modeling_DAGs_on_SQL_DBs.aspx
This will allow GetFiles to work recursively with only joins.
Ken,
Note that you need to support Recursion.OneLevel as well.
1/ Bruno, no I am not saving the file content, just the file meta data.
2/ There is a drastically simpler solution
Rik,
But this requires more than a single INSERT in the Save, no?
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.
Rik,
My implementation did it with a single insert. That is all
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.
Argh!
No XML, please. NO XML.
1) Save the file path as a string
2) Use LIKE to find recursive matches.
How do you handle OneLevel
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 :)
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.
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
The DB is SQL CE
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/%/%/'
<disclaimer>I'm persistence ignorant (literally :D), so maybe I'm totally missing the point</disclaimer>
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.)
Alberto,
I simply did not think of this
Comment preview