Choosing a local database for tagging, file paths, etc.

[StoleMyOwnCar]

TL;DR ME WANT BEST DATABASE TYPE TO STORE HIERARCHICAL DATA (FILES+PATHS) WITH TAGGING. LEANING TOWARDS TRYING NEO4J. WILL BUILD CUSTOM WEB-BASED FRONTEND FOR IT (PROBABLY SPA).

I asked this on OCN but they're not coming up with much:

I'm thinking of making a local database for basically helping to manage the several TB of files that my HDDs have on them. I have a decent amount of experience working with MySQL, but I've never done anything horribly complicated in it. Usually at most it's a pull from a pull or a few joins, or something. Nothing like a recursive query that depends on stored procedures and does joins all over the place or something like that... and also thinking about the way a standard DB like MySQL is set up, I'm not sure whether it would be the optimal solution for for this.

From what I've seen storing hierarchical information in a standard database can get kinda hairy, and I would probably be doing joins all over the place. So I'm kind of wondering whether it would be the optimal solution. Lately I've been reading up on other types of databases (ie NoSQL). I'm kind of considering trying out one of those. Maybe Neo4j? A graph approach to this seems to be interesting, and with files being able to contain tags grouped with the data and having interconnections where I want them. Seems like it would be a very flexible way to represent the files.

If there are some other already made solutions, I would like to hear about how they approached it. But that being said I'm kind of wanting to just gradually (read: very gradually; ie nothing else to do style) do this from scratch for two reasons:
1. Learning experience.
2. Flexibility to choose what methods I want to query the data with and what information I want to store and how.


Maybe eventually make a gui frontend for it using... probably AJAX+JS+PHP (since I'm experienced with all three of those)... I'm lazy and this will pretty much be something I do during extremely bored periods, so this is just for now a discussion of what DB would be most suitable for such a task.

 

[mikeblas]

What's OCN?

If learning is your primary goal, I'm not sure how to make a recommendation. Just pick something up, try it out, and learn.

 

[StoleMyOwnCar]

Perhaps I worded it vaguely though. Learning isn't the only goal, I actually wanna use it. Lol.

OCN=our brothers and sisters over at overclock.net

 

[mikeblas]

OK. So, then, the next requirement is flexibility. NEO4J is graph only, really. You can kind of "denormalize" your data to bend it into a flatter graph, but it's not exactly the same as backing off to a real relational database.

You seem to have a pre-conception ("storing hierarchical information in a standard database can get kinda hairy"). If you stick with that preconception, you're not going to learn anything about it; you're just going to assume it's so. Understanding the subtleties has value, so why not come up with a relational solution and compare it to your graph-based solution?

After that, "lazy and this will pretty much be something I do during extremely bored periods" makes me figure your project isn't really going anywhere and it's hard to be motivated to help.

 

[StoleMyOwnCar]

OK. So, then, the next requirement is flexibility. NEO4J is graph only, really. You can kind of "denormalize" your data to bend it into a flatter graph, but it's not exactly the same as backing off to a real relational database.

Yes, it's a graph database, but if you treat files and folders as nodes with attributes and then just put down connections... well my real only question is how hard would it be to query for something compared to a standard relational database?

You seem to have a pre-conception ("storing hierarchical information in a standard database can get kinda hairy"). If you stick with that preconception, you're not going to learn anything about it; you're just going to assume it's so. Understanding the subtleties has value, so why not come up with a relational solution and compare it to your graph-based solution?

That's a nice sentiment, but the reason I don't merely call it a "pre-conception" like you is because I have actually done some cursory research and the results seem to be... displeasing:
http://stackoverflow.com/questions/...ng-hierarchical-data-in-a-relational-database
All of these methods seem to have trade-offs. I was wondering if there was another database type (likely in the NoSQL realm) that would more naturally fit the data so as to almost be made for the purpose. "Just roll it out and see how it goes" is pretty silly. That's not the way I code anyway, I spend time sharpening my knowledge and then go for the optimal course... not just a random one that I picked just to run into trouble later. How do you get far with that kind of mindset?

After that, "lazy and this will pretty much be something I do during extremely bored periods" makes me figure your project isn't really going anywhere and it's hard to be motivated to help.

That's a very silly mindset. This is a theoretical discussion first and foremost. I don't need your help in implementation, just discussing the theory. I would think that some of the CS majors and such on here would be interested in such a discussion regardless of whether it helps me out or not. I mean I see Hardforums people over in the News section debating end on end about various topics that their opinions have no ability to change... yet asking for some opinions and input on this, whether or not I actually bring it into fruition... is asking for too much?

 

[mikeblas]

Yes, it's a graph database, but if you treat files and folders as nodes with attributes and then just put down connections... well my real only question is how hard would it be to query for something compared to a standard relational database?

It'll be a little awkward, but not too hard.

That's a nice sentiment, but the reason I don't merely call it a "pre-conception" like you is because I have actually done some cursory research and the results seem to be... displeasing:
http://stackoverflow.com/questions/...ng-hierarchical-data-in-a-relational-database

Not sure what's displeasing about that; the answer is correct: all of the major players (and some of the minor players) have implemneted support for WITH, and that makes writing hierarchical queries a lot less painful and renders the workarounds in the question post obsolete.

All of these methods seem to have trade-offs. I was wondering if there was another database type (likely in the NoSQL realm) that would more naturally fit the data so as to almost be made for the purpose. "Just roll it out and see how it goes" is pretty silly. That's not the way I code anyway, I spend time sharpening my knowledge and then go for the optimal course... not just a random one that I picked just to run into trouble later. How do you get far with that kind of mindset?

Well, what's the purpose? "see how it goes" is a great way to learn; you're taking a comparatively shallow dive to familiarize yourself with new techniques, tools, and products. If you want to pick a product, implement something very completely along the "optimal course", you're going to take an inappropriately deep dive and not really make progress.

That's a very silly mindset. This is a theoretical discussion first and foremost. I don't need your help in implementation, just discussing the theory.

And I'm helping you discuss the theory. But why bother? Your laziness will take over, and nothing will come of it.

 

[StoleMyOwnCar]

It'll be a little awkward, but not too hard.

That's what I mean, whether or not there would be a database format that more naturally fits the hierarchical structure of tagged files and folders. "Basically n-ary tree nodes with attributes, tags and possible interconnections not related to hierarchy" is what I was thinking in my head when I thought about Neo4j. And then perhaps if the DB was designed to query simply based on a such a structure... and if I needed to rearrange data (ie move files), maybe it would be more easily done with some than others.

Not sure what's displeasing about that; the answer is correct: all of the major players (and some of the minor players) have implemneted support for WITH, and that makes writing hierarchical queries a lot less painful and renders the workarounds in the question post obsolete.

Well actually unless it has changed, MySQL (which is what I am most familiar with) does not. At least I see plenty of questions on Stackoverflow asking about it and they keep saying it still doesn't.

Well, what's the purpose? "see how it goes" is a great way to learn; you're taking a comparatively shallow dive to familiarize yourself with new techniques, tools, and products. If you want to pick a product, implement something very completely along the "optimal course", you're going to take an inappropriately deep dive and not really make progress.

If you really must have a purpose, have "produce something that is useful to me while learning some things and having some fun". I'm not aiming to go to masters/phd level DB intricacies with a simple personal project like this. Just learn something while producing something useful. I also would like to not later find out that the solution I chose has frustrating limitations that cause me to implement workaround fixes. That's not terribly fun.

And I'm helping you discuss the theory. But why bother? Your laziness will take over, and nothing will come of it.

Why bother with any theoretical discussion? Put "hypothetically speaking" everywhere, you'd be satisfied? "Hypothetically speaking, what if I were to implement a database for the purposes of hierarchical storage organization, what would be the ideal database storage choice, in light of the NoSQL options and current database solutions?" Would this manner of wording satisfy you more? If so then please treat it as such

 

[krogen]

Why bother having another data layer?

If you think about it, filesystems are databases, or, at least, have a lot of properties of databases.

You could use native system bindings to communicate / query the filesystem.

If you need to create virtual filesystems I'm sure there are solutions for that, too.

 

[StoleMyOwnCar]

A number of reasons, starting with the fact that I can't control how it arranges anything, nor can I control what information I want where, nor can I control where the structural information resides.

I answered a bunch of questions that might be of interest in the OCN side of this, where I actually did get a conversation kicked up with a person:
http://www.overclock.net/t/1497537/...base-for-tagging-file-paths-etc#post_22502445

 

[jimmyb]

There are many filesystem indexers and search tools which seem to cover some (if not all) of what you want; some are open source too.

 

[Dogs]

Well actually unless it has changed, MySQL (which is what I am most familiar with) does not. At least I see plenty of questions on Stackoverflow asking about it and they keep saying it still doesn't.

Right, he said major players.

 

[StoleMyOwnCar]

Right, he said major players.

http://db-engines.com/en/ranking

Because number 2 means it's a minor player? o_0
Does not compute.

There are many filesystem indexers and search tools which seem to cover some (if not all) of what you want; some are open source too.

Could you link some of these? I'm not terribly amazing at googling these down. I'm also not sure if they would perform as I would like. Would need to see. It would also be nice if they let me have access to the data and query it myself at the very least.

 

[jimmyb]

Could you link some of these? I'm not terribly amazing at googling these down. I'm also not sure if they would perform as I would like. Would need to see. It would also be nice if they let me have access to the data and query it myself at the very least.

http://en.wikipedia.org/wiki/List_of_search_engines#Desktop_search_engines

 

[StoleMyOwnCar]

http://en.wikipedia.org/wiki/List_of_search_engines#Desktop_search_engines

Should have known Wikipedia would have a list like that...

I briefly looked over the freeware options for Windows... I don't think they're quite what I was looking for (but some of them come close-ish). They don't look flexible enough. Looking briefly, none of them seem to mention whether I would get access to a database with all of this information and how it's arranged in the raw, either.

But that being said, they're already made. So it certainly could benefit me to at least start using one of those right now, since the problem exists and halfway solving is better than not solving. I didn't know there were already solutions for this kind of stuff... ._.

I'll need to look over them in more detail to see which one is closest to what I want for use right now. Copernic looks unsuitable. InSight is hard to tell because their web site is a cluster of crap and I don't know what the hell is going on. Tropes Zoom is VERY interesting for texts, and interesting in general. Something I will look into but not what I need here. Everything is what I am most likely to use because it looks like a no-frills solution to at least find things quickly. So it's either Everything or Insight. I'm still probably planning on working on my project in my free time, though. I'm pretty interested in trying to model this using Neo4j, as I mentioned in the OCN topic. It seems like it would be fun.

Thanks for this list though (granted it's not yours).

 

[krogen]

A number of reasons, starting with the fact that I can't control how it arranges anything, nor can I control what information I want where, nor can I control where the structural information resides.

Yes you can. Most filesystems give you the ability to create hard and symbolic links. There is no issue with representing structure differently.

 

[Dogs]

http://db-engines.com/en/ranking

Because number 2 means it's a minor player? o_0
Does not compute.

Read how they computed that number. They measure how much it's talked about, now how much it's actually used, nor how much it's actually used in large, sophisticated systems that demand more advanced features.

MySQL is used for a lot of little things...MyFirstWebsite type applications, and other simple things. But, you pointed it out yourself; MySQL doesn't support WITH. The lack of things like common-table expressions is a great example of precisely why MySQL isn't on the same level as the major databases.

 

[StoleMyOwnCar]

Yes you can. Most filesystems give you the ability to create hard and symbolic links. There is no issue with representing structure differently.

Yes, I know what hard links, soft links, and file junctions are: totally insufficient.

It's kind of a funny dichotomy here, between OCN and you guys. Over there, constructive questions and whatnot about the idea (well granted only one person responded). Over here a hell of a lot of negativity. About me attempting this. Hell. Everything.
Is it the black background? =\

This topic's pointless, forgive me for making it. I'll withdraw.

Read how they computed that number. They measure how much it's talked about, now how much it's actually used, nor how much it's actually used in large, sophisticated systems that demand more advanced features.

MySQL is used for a lot of little things...MyFirstWebsite type applications, and other simple things. But, you pointed it out yourself; MySQL doesn't support WITH. The lack of things like common-table expressions is a great example of precisely why MySQL isn't on the same level as the major databases.

I know how they computed that score. It also makes sense to me that the more something is talked about, the more it is likely being used, unless the application is proprietary. You're essentially assuming that every other solution with a "With" clause is being used to great effect in a proprietary setting and that MySQL is being used in no such settings.

That doesn't make sense to me, because the corporation I work for (and our group in general) uses it for plenty of our databases. You don't always need "with" and whatever other features other databases provide. And no it's not just for simple web sites, it houses quite a bit of information and has a lot of transactions (including various high-throughput information pipes). I believe there are also companies using it for their purposes.

We do have Oracle for some systems that are more complicated (and by the way a general PITA to work with no matter what you do), but just assuming that everything else is more prevalent or a "bigger player" simply because of some technical superiority is a bit silly.

But honestly this point of discussion is irrelevant here anyway. It's not what I'm here to discuss. I don't give a crap.

 

[krogen]

Yes, I know what hard links, soft links, and file junctions are: totally insufficient.

It's kind of a funny dichotomy here, between OCN and you guys. Over there, constructive questions and whatnot about the idea (well granted only one person responded). Over here a hell of a lot of negativity. About me attempting this. Hell. Everything.
Is it the black background? =\

This topic's pointless, forgive me for making it. I'll withdraw.

I am pointing out a simple, viable solution and the best you can say is "totally insufficient" without giving a single example of it's insufficiency?

Why exactly do you need to implement another data layer over a filesystem?

I'll give you one reason why DBs are probably a bad idea for this : data consistency. By performing a database query you will never be 100% sure if that data still actually exists on the drive. Going back to the filesystem totally destroys the purpose.

Also, don't take things so offensively. If someone's trying to derail the topic just don't take the bait.

 

[mikeblas]

http://db-engines.com/en/ranking

Because number 2 means it's a minor player? o_0
Does not compute.

Popularity in conversation doesn't mean quality. MySQL is not a serious database engine; it's missing lots of features compared to Oracle and SQL Server. Lots of progress in recent releases, but there are still tons of things it doesn't do that other products have covered. This seems like a partial answer to your question, then: not MySQL, since it has no constructs for hierarchical queries and limited facilities for overcoming that problem.

Why bother with any theoretical discussion? Put "hypothetically speaking" everywhere, you'd be satisfied? "Hypothetically speaking, what if I were to implement a database for the purposes of hierarchical storage organization, what would be the ideal database storage choice, in light of the NoSQL options and current database solutions?" Would this manner of wording satisfy you more? If so then please treat it as such

I'm sorry: there's lots of people who pop into this part of the forum, ask questions, get help, take little action as a result of the delivered advice, and never return. I'm frustrated with that, and it makes me cautious about spending time with people who don't appreciate it. "hypothetically speaking" doesn't really help. What's trumping me is "my lazy ass", "don't give a crap" and so on. Those sentiments make it seem easy to predict you'll fall into that same helpless, thankless category.

You've asked for "the ideal choice". "ideal" is a superlative; there's only one. The ideal choice is going to be dependent on lots of tradeoffs within the system you intend to design. Some of the tradeoffs in a big system are rigid, some are flexible, and some are based on arbitrary concerns. Without knowing those tradeoffs for your situation, nobody can responsibly make a recommendation about the ideal toolset.

 

[Dogs]

That doesn't make sense to me, because the corporation I work for (and our group in general) uses it for plenty of our databases. You don't always need "with" and whatever other features other databases provide. And no it's not just for simple web sites, it houses quite a bit of information and has a lot of transactions (including various high-throughput information pipes). I believe there are also companies using it for their purposes.

You don't always need the more advanced features...but they're there for a reason, and that's because if you're solving advanced or complicated problems, you often times do want those better features. You came into this thread making an assumption about RDBMS (that they can't really do what you want to do), but the reality is that the bigger RDBMS can do it just fine and the limiting reactant here is actually MySQL; MySQL is limited in its capabilities. MySQL isn't on par with the other players, a great example of this is right in front of your face, and yet you'd rather argue with us and say that we're not constructive because we're not telling you what you'd want to hear from us.

 

[StoleMyOwnCar]

You don't always need the more advanced features...but they're there for a reason, and that's because if you're solving advanced or complicated problems, you often times do want those better features. You came into this thread making an assumption about RDBMS (that they can't really do what you want to do), but the reality is that the bigger RDBMS can do it just fine and the limiting reactant here is actually MySQL; MySQL is limited in its capabilities. MySQL isn't on par with the other players, a great example of this is right in front of your face, and yet you'd rather argue with us and say that we're not constructive because we're not telling you what you'd want to hear from us.

No. My main point was what defines something as a "big player"? Prevalence? Feature set? All of the DB's get professional use, just for different things. Also if we're talking about featureset, I believe it took a while for Oracle to get a group_concat function (which they decided to make in their own little way, rather than just using MySQL's syntax...). >_>

Anyway going on that line of though, I'll reiterate: which one is or is not a big player is also irrelevant to this topic. I was never trying to argue against other RDBMS's not being able to support hierarchical data (otherwise why in world would I link a stackoverflow topic where they discuss the very thing, with solution types), but I was asking which one would do it most naturally (with regards to the way I've described in my OCN topic, in the comment I linked), in light of the NoSQL solutions available. That's it.

I am pointing out a simple, viable solution and the best you can say is "totally insufficient" without giving a single example of it's insufficiency?

Why exactly do you need to implement another data layer over a filesystem?

1. I want it all in one place, so I don't have to have the hard drives spin up to know what is on the drive (this is going in a small location on an SSD, so access times will be fast).
2. I want to query by (and define) arbitrary metadata
3. I want to define what things I can query by.
4. I want indices where I want them, not where the operating system wants them.
5. I want to arrange all of this in the manner I please, not the way it's arranged on the file system (again go read over my comment in the OCN topic with regards to that).

Hard links and soft links just let me rearrange data within the current defined structure. Folders of folders of subfiles, and a cluttered mess of icons. This isn't about putting another layer, but rather treating all of this as mere data. And I know it's data to begin with, I suppose I mean "a bunch of text" to be more specific.

I'll give you one reason why DBs are probably a bad idea for this : data consistency. By performing a database query you will never be 100% sure if that data still actually exists on the drive. Going back to the filesystem totally destroys the purpose.

That doesn't matter. Most of the purpose of this is to catalog a large amount of static data. I would assume if there is something I changed recently, I would know about it. I do also plan to design a crawler of sorts. The database eventually having the information is good enough.

Also, don't take things so offensively. If someone's trying to derail the topic just don't take the bait.

I'm not offended, though a wee bit annoyed.

I'm sorry: there's lots of people who pop into this part of the forum, ask questions, get help, take little action as a result of the delivered advice, and never return. I'm frustrated with that, and it makes me cautious about spending time with people who don't appreciate it. "hypothetically speaking" doesn't really help. What's trumping me is "my lazy ass", "don't give a crap" and so on. Those sentiments make it seem easy to predict you'll fall into that same helpless, thankless category.

I can understand that, but at this point this is all theory, because this is discussion before attempt. I made that very clear. It's not like "I am currently in the process of attempting this, I need advice on this" and you give me definitive advice and I just totally choose to ignore it. This is just hypothetical.

Furthermore:

You've asked for "the ideal choice". "ideal" is a superlative; there's only one. The ideal choice is going to be dependent on lots of tradeoffs within the system you intend to design. Some of the tradeoffs in a big system are rigid, some are flexible, and some are based on arbitrary concerns. Without knowing those tradeoffs for your situation, nobody can responsibly make a recommendation about the ideal toolset.

There is no definitive advice, so effectively my only option is to pick something and roll with it. Which I guess is gonna be Neo4j. I'm just interested in trying it out when modeling this, considering I think it might fit well with the structure type I defined in the OCN topic (which I linked some number of posts back).