Ayende @ Rahien

Ayende Rahien commented on Find out the right directory

Fri, 28 Mar 2008 17:50:37 GMT

Alberto, I simply did not think of this

alberto commented on Find out the right directory

Fri, 28 Mar 2008 16:54:04 GMT

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 commented on Find out the right directory

Fri, 28 Mar 2008 16:44:16 GMT

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

Steve Campbell commented on Find out the right directory

Fri, 28 Mar 2008 16:40:52 GMT

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

Ayende Rahien commented on Find out the right directory

Fri, 28 Mar 2008 16:36:01 GMT

The DB is SQL CE

Ayende Rahien commented on Find out the right directory

Fri, 28 Mar 2008 16:33:04 GMT

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 commented on Find out the right directory

Fri, 28 Mar 2008 16:30:45 GMT

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.

Bruno commented on Find out the right directory

Fri, 28 Mar 2008 16:24:32 GMT

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 :)

Ayende Rahien commented on Find out the right directory

Fri, 28 Mar 2008 15:58:13 GMT

How do you handle OneLevel

Steve Campbell commented on Find out the right directory

Fri, 28 Mar 2008 15:52:22 GMT

1) Save the file path as a string 2) Use LIKE to find recursive matches.

Ayende Rahien commented on Find out the right directory

Fri, 28 Mar 2008 11:53:55 GMT

Argh! No XML, please. NO XML.

Joshua Cauble commented on Find out the right directory

Fri, 28 Mar 2008 11:36:01 GMT

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 commented on Find out the right directory

Fri, 28 Mar 2008 11:34:14 GMT

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

Rik Hemsley commented on Find out the right directory

Fri, 28 Mar 2008 11:20:56 GMT

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 commented on Find out the right directory

Fri, 28 Mar 2008 10:42:06 GMT

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

Ayende Rahien commented on Find out the right directory

Fri, 28 Mar 2008 10:36:57 GMT

1/ Bruno, no I am not saving the file content, just the file meta data. 2/ There is a drastically simpler solution

Ayende Rahien commented on Find out the right directory

Fri, 28 Mar 2008 10:35:37 GMT

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

Rik Hemsley commented on Find out the right directory

Fri, 28 Mar 2008 10:16:19 GMT

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 Egozi commented on Find out the right directory

Fri, 28 Mar 2008 06:11:54 GMT

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) ... Parameter(@path, file.Path), } GetFiles(path, recursive) { if (path.EndsWith("\\") == false) path += "\\"; if (recursive) { path += "%"; } q.Add(Expression.Like("Path", path); return q.List(); }

Bruno commented on Find out the right directory

Fri, 28 Mar 2008 04:20:36 GMT

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?