This thread is nine months old, so for the record, for other readers.
> On Saturday, 5 January 2019 21:30:01 UTC+11, yashba...@gmail.com
> 1. Get the tree structure of files/folders for which user has access.
Easy. The Relational Model is founded on the Hierarchical paradigm, tgus it handles hierarchies beautifully. There are three types of hierarchies, the one you need is the simplest: each item has just one parent.
For readers who are unfamiliar with the Relational Modelling Standard:
For a full Question and Answer interaction:
> 2. Check if user has access to a particular file. (In this case, permission has to be taken from file id itself if specified, otherwise we have to search for nearest ancestor).
Just add a table for Permission, and an FK to the given table. Use the concept of SQL Roles, so that permissions are set for a Role, not for each User.
At some point you will have to code a Function (some idiots call it UDF, idiots because everything that you place in the database is UD) to obtain the directory tree, ie. a concatenated Path, as opposed to individual Nodes.
For that, the Function has to be Recursive. All genuine SQL platforms support recursion, the freeware/shareware/vapourware/noware does not support recursion. Such platforms have bells and whistles but not the basic requirements of SQL (hence their use of the term is fraudulent), and no Server Architecture whatsoever.
In that Function, accumulate or update the Permission as you navigate the tree, such that at the end, the Permission will be that of the last ancestor.
> I was banging my head with Adjacency lists, Materialized paths and Nested sets.
Yes. putrid filth, from people who are evidently clueless about the RM, and SQL. Some of them are implementation poured in concrete, if a Node changes, the entire tree has to be updated. Some have masses of duplicates.