1

For querying (and writing to) a hierarchical structure (like a folder containing files and other folders, up to n levels deep, where n is pretty small, probably around 10, and there would be lots of files... I'm totally guessing but there might be like 85% reads, 15% writes when restructuring or adding files), what would be a good database / schema to use? SQL with ..? NoSQL? GraphDB: OrientDB / Neo4j? I have a bit of experience with simple SQL using Postgres or MySQL (insert, select a from b where c, join / right outer join, etc) and otherwise generally have experience at application-level programming and with JSON.

If it matters, I plan on storing file paths to photos / videos and also storing related photo data (EXIF, etc) in either this database or a combo of databases (if that's a thing, like querying all photo paths from one db and then querying all data related to each of the files represented by those file paths in another db?)... I also was thinking I'd just use the underlying filesystem to read/write to/from photo/video files.

This is more of a personal project and I don't plan on scaling with distributed systems, etc. The actual photo / video storage space would probably span across several TiB of disk over time.

(Sorry if this seems vague. I'm just starting out and realized that my application suddenly seems a bit more complex since I feel the need to properly record my photo/video files and their place in the filesystem hierarchy in order to search over them and provide an API to my application to read/write from/to them (and to be able to re-order their positions in the filesystem hierarchy/tree).)

EDIT:

I didn't originally mention, but I plan on doing this self-hosted. I'm currently thinking that for my use-case (reading and possibly modifying hierarchy, and also inserting), I could simply use the OS filesystem and directly interface with it, starting out with syscalls (ie: ls, cd, mkdir, mv, chown, chmod, rm, login / exit, etc). This way I already get a decent tree structure to work with, backed by the OS, and I don't have to worry about syncing back with the original filesystem hierarchy on any modifications (since I'll be directly accessing and modifying some root directory on the filesystem)... I understand I'll also need to take precautions to harden security around my interface to the syscalls (basically I could start with parsing out any dangerous symbols, like ".." or "*" from an API query, but I'll look up better practices online, like implementing user-level OS restrictions)...

Edit 2:

But using this method alone, I don't get to associate other data with each file, like commenting or 'like'ing a photo/video, or adding thumbnails, etc, so I'll probably need at least some other kind of DB for that (possibly Mongodb?)...

Edit 3:

I'm now considering creating a replica of the folder hierarchy for each sub-folder in the original root folder. Then I'm thinking I'll keep a simple JSON file with a dictionary to store any extra data related to each photo / video file. Since I'm basically using a db for each folder, I decided to go with NoSQL / JSON as I might modify the schema of associated data for each image / video along the way, as I'm figuring things out. And I'm already using Node.js on the server-side, so JSON seems pretty simple to at least start out with. (I can also implement the concepts from EDIT above, and just create an API that synchronizes and maybe locks these two general data structures.)

Any suggestions?

  • I highly recommend that you purchase this book by Joe Celko which deals with trees and hierarchies in SQL, and the different solutions: amzn.to/2Rf7pPD – SQLRaptor Jan 7 at 20:20
0

If you don't need transactions or an RDBMS. The ideal method would be to sha3-512() the file's contents, and then store it based on the SHA. Then model a recursive table for the path linking it to the resource/file,

CREATE TABLE files (
    file_id        int   PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    orig_name      text,
    sha3           text  UNIQUE,
    mime           text,
    bs             int
);
INSERT INTO files (file_id, orig_name, sha3, mime, bs) VALUES
(1, 'dongs.jpg', 'd9be28047e605af3ff9a4b4013e0e05b47a1d6774c3beae9a1038da5bcc9b37e890dd42c1c7cba4cbf7ba5e5c55b104fba5944bfe7d9dbc491d14171da5bdd0b', 'image/jpg', 50042 );

Now for the path

CREATE TABLE virtual_directories (
    vd_id         int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    parent_vd_id  int REFERENCES virtual_directories,
    file_id       int REFERENCES files,
    name          text,
    CHECK ( file_id IS NOT NULL AND parent_vd_id IS NOT NULL or name IS NOT NULL )
);
INSERT INTO virtual_directories ( vd_id, parent_vd_id, file_id, name ) VALUES
  ( 1, null, null, '/'        ), -- root
  ( 2, 1,    null, 'pictures' ),
  ( 3, 2,    1,    'woot.jpg' );

Recursive Queries

Now you can query it like this,

WITH RECURSIVE t AS (
  SELECT t1.vd_id, t1.parent_vd_id, t1.file_id, ARRAY[t1.name] AS path
  FROM virtual_directories AS t1
  WHERE parent_vd_id IS NULL

  UNION ALL

  SELECT t2.vd_id, t2.parent_vd_id, t2.file_id, t.path || t2.name
  FROM t
  INNER JOIN virtual_directories AS t2 
  ON t2.parent_vd_id = t.vd_id

)
SELECT *
FROM t
WHERE file_id IS NOT NULL;

Or you can build the tree the other way which may be faster if you know the file id that you want.

CREATE FUNCTION array_reverse( arr anyarray)
RETURNS anyarray AS $$
  SELECT ARRAY(
    SELECT e
    FROM unnest(arr) WITH ORDINALITY AS e
    ORDER BY ordinality DESC
  );
$$ LANGUAGE sql
IMMUTABLE;

WITH RECURSIVE t AS (
  SELECT t1.vd_id, t1.parent_vd_id, t1.file_id, ARRAY[t1.name] AS path
  FROM virtual_directories AS t1
  WHERE file_id IS NOT NULL

  UNION ALL

  SELECT t2.vd_id, t2.parent_vd_id, t.file_id, t.path || t2.name
  FROM t
  INNER JOIN virtual_directories AS t2 
  ON t.parent_vd_id = t2.vd_id

)
SELECT file_id, array_to_string(array_reverse(path), '/')
FROM t
WHERE file_id IS NOT NULL
  AND vd_id = 1; -- path form root;
  • Wow. This is quite sexy. I didn't really understand the second recursive query syntax / I'm pretty beginner when it comes to dbs, but I may take a look at it later. For the time being, I've realized I can keep moving forward with my API and front-end, since I have a better idea of the DB design where I'll be going with this kind of concept (either using something like you did but with a JSON listing each directory and another JSON per, or maybe keeping it all in a couple SQL tables in one DB, which seems a lot cleaner and probably better in terms of performance, maintainability, and ACIDity).. – user3773048 Jan 7 at 22:04
  • I also didn't properly think about the time/space complexity of this approach with the first recursive query, but it might be something I consider at a later time. – user3773048 Jan 7 at 22:04
  • You either go from root to each file building the whole tree. Or you start at a file, or specific file, and go to the root. – Evan Carroll Jan 7 at 22:06
  • Oh, I see. That's a convenient way to think about when it comes to traversing.. Basically parent to child vs child to parent. I think root to each file more suits my general app. Thanks for that clarification. I think I'll be revisiting this post when I decide to move forward with aspects of my DB design and implementation. Thanks again. – user3773048 Jan 7 at 22:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.