This relates to the About GraphQL API? thread but it is not the same as the former is a frontend question while this one is more about the backend itself.
I have been looking at Graph Databases recently, e.g. Neo4J and ArangoDB to store a DAG data structure. They are the obvious candidate to traverse data structures with an unknown depth. While it is entirely possible to model a DAG with a Relational Dababase, I wanted to hear about the experiences of people having done it specifically with Shotgun?
Discussions we had a few days ago with Mason Jarratt were implying that some customers did try but without a lot of success. I don’t really know how they decided to model the DAG though.
We are looking at storing some tree structures in the filesystem. The directory layout itself is a tree, but there will also be xml/protobuf/whatever files for additional structure.
This structure will have a counterpart in Shotgun PublishedFiles linked to each other in a tree, which is basically modeling it in the relational database. (one cool thing is that linked entities have automatic backlinks)
Admittedly, our throughput needs are humble, so slow file access is not a concern.
Another point is that the Shotgun api has some limited tree querying (or join if you will), in the form of: sg.find("PublishedFile", [], "user.HumanUser.login")
Some things to consider: The problem with storing that kind of information in the filesystem, ignoring speed concerns, is that as soon as you take the filesystem out, e.g. for archival purposes, then you have lost your tree and you will need to restore the filesystem (or part of it) to retrieve access to the tree. Depending what you intend to do with the tree/graph, e.g. modeling dependencies, this might not be desirable. Another case is if you are working with a VCS such as Perforce/Aliebrain VFX, your tree being on the filesystem, i.e. in the VCS, it will not be available until you pull it down.
Not advocating using the filesystem, but the things you listed could also be viewed as advantages - you can easily archive/restore your trees, and you can control them through cvs
Really the biggest downside of the filesystem is disk access times. Of course with databases you can have a lot of other perks like controlled indexing, separate access control (which you can theoretically achieve in the filesystem), etc.
That said, I’d love to see GraphQL in Shotgun. That would be amazing.
