Member Avatar for 0xff

I need to make an app in Java that must add in a database all the files (with folder structure) from a hard drive. I need to add the metadata of this files as well.

What it will be the best aproach ? Using SQLite with multiple tables or Neo4j ??

  • 4 Contributors
  • 3 Replies
  • 228 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

Short answer: pick the tool that matches your query patterns. For a simple local catalog (list folders, search metadata, occasional subtree scans) an embedded SQL file (SQLite) is the lowest-friction, fast, single-file option. If you expect lots of ad-hoc traversals, many-to-many links (tags, hardlinks, shortcuts), graph queries or analytics (path-finding, recommendations), a graph DB like Neo4j models those operations directly and will simplify queries and code. This aligns with and — both choices are valid depending on needs. (sqlite.org)

SQLite pattern (recommended for a desktop Java catalog): store structure in two small tables (dirs, files) with parent_id foreign keys, index the parent/dir_id columns, and use a recursive CTE to build full paths. Example (SQLite):

WITH RECURSIVE p(id, name, path) AS (
  SELECT id, name, name FROM dirs WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, p.path || '/' || d.name
  FROM dirs d JOIN p ON d.parent_id = p.id
)
SELECT path FROM p WHERE id = ?;

Enable WAL and set a busy timeout for smoother concurrency (PRAGMA journal_mode=WAL;). (sqlite.org)

Neo4j pattern: model folders and files as nodes and use relationships (e.g., :CONTAINS) for edges. Cypher makes traversal and path queries concise:

MATCH p=(root:Dir)-[:CONTAINS*]->(f:File {name:'foo.txt'})
RETURN [n IN nodes(p) | n.name] AS path;

Choose Neo4j when relationship-centric queries and graph algorithms are core to the app. (neo4j.com)

Storage of file contents: a practical hybrid wins. SQLite can store BLOBs and is competitive for small blobs (and even faster than individual files in many tests), but for large binaries store content on disk or object storage and keep an immutable URI/hash in the DB. If you require transactional coupling of file bytes and metadata, DB-specific features (SQL Server FILESTREAM or similar) exist. Measure on your target hardware before deciding. (sqlite.org)

Operational notes and recommendation: nested-set techniques (as noted) are OK for read-heavy, mostly static snapshots but are costly to update. If this is a one-shot catalog of a drive, start with SQLite metadata + filesystem/object storage for binaries; switch to Neo4j only if you add relationship-heavy features later. If you expect heavy concurrent writers or distributed access, choose a client/server DB. (sqlite.org)

Recommended Answers

All 3 Replies

Member Avatar for ~s.o.s~

If you plan on chasing around a lot of parent-child links, RDBMS get's a bit cluky due to the impedance mismatch between the relational model and the inherent hierarchical nature of the file system entries. I would personally go with Neo4J.

Member Avatar for rubberman

I tend to agree with s.o.s about the clunky nature of SQL to model such hierachical structures. It can be done of course, but the file data would have to be BLOBS if you want the file data in the database. Depending upon their sizes, that could be an issue as well. All the directories could be in a single table with self-joins to reference their parents, and the containing directory in the file table would be a foreign key to the directory table. Since multiple directories, even in the same path, can have the same name, you would need another field for the primary directory key - ditto the files.

So, doable, but complex. Personally, I'm not familiar with Neo4J. I'll have to do some research to see if that would be a better option, but at this point I'll take s.o.s's opinion about that.

Member Avatar for Member #120589

Not a javaist, but there may be an alternative to the self-join model (adjacency model) - the nested sets model. Here's a link:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

I've had a few discussions about this over on the PHP forum. If you're interested, I can see if I can dig them out as I created a couple of visuals that may help. However, I only envisaged storing structure, not files too. It could get messy, heh heh.

Edited by Member #120589
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.