Tech Off Thread

10 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Database File System Library?

Back to Forum: Tech Off
  • User profile image
    ManipUni

    More precisely the child/parent or directory/file style hierarchy.

    What I want to avoid is reinventing the wheel - primarily because I want something rock solid and if you screw up a relationship model it is very difficult to recover the structure.

    I realise I could create new tables for each "directory," but I don't want 50,000 tables with one record. I could also use existing relationship bits in SQL to generate a relationship between tables but again, I don't want 50,000 tables.

    It ideally needs to work in any standard SQL database (MySQL, MSSQL, et al). And offer a simple "directory" store for one or more directories or records.

    e.g.
    User1 -> Stuff -> Record
    User2 -> Stuff -> Record2
    User2 -> Bits -> Record3
    User3 -> Record4

    So I can just query "What is in User1->Stuff?" And get back records that belong to that context - exactly like a file system (but obviously stored in a database).

  • User profile image
    davewill

    I don't understand.  A single table with a flexible set of parameter fields and a blob with the needed unique indexing across whichever parameter fields must be unique sounds like a simple enough solution.  But something tells me I need more info.

  • User profile image
    figuerres

    Yeah...wtf..theeditor.will.not.let.me..hit.space. 

  • User profile image
    figuerres

    any way it almost sounds like filestream ? or we need a better exp. of what you want to do.

  • User profile image
    Sven Groot

    Any particular reason why you can't just use a single table with the fields (id, name, parentId)?

  • User profile image
    spivonious

    Sounds like you would benefit from an object-oriented database, or at least an object-relational database. Otherwise as you said, the relational model could get ugly really fast. I would start out with a "directory" table and a "file" table.

  • User profile image
    AndyC

    Windows Installer is a good 'real world' example of this, it'd be a good place to look for a design.

  • User profile image
    spivonious

    Directory (id, name, parent_id, owner) with foreign key from parent_id to id

    File(id, name, directory_id) with foreign key from directory_id to directory.id

     

    Select * from file where (select owner from directory where id = file.directory_id) = 'user1'

  • User profile image
    W3bbo

    , ManipUni wrote

    More precisely the child/parent or directory/file style hierarchy.

    What I want to avoid is reinventing the wheel - primarily because I want something rock solid and if you screw up a relationship model it is very difficult to recover the structure.

    I realise I could create new tables for each "directory," but I don't want 50,000 tables with one record. I could also use existing relationship bits in SQL to generate a relationship between tables but again, I don't want 50,000 tables.

    It ideally needs to work in any standard SQL database (MySQL, MSSQL, et al). And offer a simple "directory" store for one or more directories or records.

    e.g.
    User1 -> Stuff -> Record
    User2 -> Stuff -> Record2
    User2 -> Bits -> Record3
    User3 -> Record4

    So I can just query "What is in User1->Stuff?" And get back records that belong to that context - exactly like a file system (but obviously stored in a database).

     

    Representing a heirarchial structure in a DB is easy, however you haven't adequately explained the nature of a 'record': is it an arbitrary blob, or something else?

     

    To represent a directed acyclic hierarchical structure you only need a simple table:

     

    Directories(int id, int? parentId, String name)

     

    As you can see it's a straightforward recursive structure where each directory points to its parent, which might be null if it's a top-level item. However if you need to search it quickly things will slow down because recursive queries in SQL are a right pain. BTW I understand working with LinqToSql for recursive structures is near-impossible, so you'd be better off using a LinqToSql method to return a table directly.

     

    Anyway, a hackish solution (that violates normal form) is to include a text field called "Path", so you'd have data like this:

     

    Directories(int id, int? parentId, String name, String path)

    1, NULL, '/', '/'

    2, 1, 'MySubfolder', '/MySubfolder'

    3, 2, 'SecondLevel', '/MySubfolder/SecondLevel'

     

    ...and so on. Because path is indexed it means lookups by name are super-quick, however renaming and moving directories becomes massively expensive (well, not massively. You can do it by doing UPDATE directories SET path = Replace('/OldName/', '/NewName') WHERE LEFT(path) = '/OldName') which happens in a single pass. You can also do deletions that way.

     

    Note that I'm not saying that using a relational DB table is necessarily the right way to store this kind of data. I'm a bit disappointed that the main DBMSs like MySQL and MSSQL are relational-only databases. It'd be cool if it could store non-tabular data, such as different types of graph, for example.

  • User profile image
    spivonious

    @W3bbo: I don't know about other RDBMSs, but Oracle supports the "connect by" syntax. Performance is actually very good in my experience, and the query is easy to read.

    For the example above,

    select * from table where owner = 'User1' connect by parent_folder = prior folder

     

    http://www.dba-oracle.com/t_connect_by.htm

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.