MySQL Inheritance via 1 Table - Permissions

Question

I have a table in our MySQL database called "folder". It stores the names of virtual folders for different users in our website. Structure below:

CREATE TABLE IF NOT EXISTS `folder` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `parentId` int(11) DEFAULT NULL,
  `folderName` varchar(255) NOT NULL,
  `isPublic` int(1) NOT NULL DEFAULT '0'
  PRIMARY KEY (`id`)
);

The website allows for any number of child folders within folders, so:

Base Folder
- Child Folder 1
--- Child Folder 2
etc

Each folder can have a public or private status. So:

Base Folder (Public)
- Child Folder 1 (Private)
--- Child Folder 2 (Private)

If "Child Folder 1" is set to "Private" it will always cause sub-folders to be private, no matter if they've been set as public in the dd.

My question is, how can I decide whether a specific folder is public or private via MySQL, considering the inheritance?

Take "Child Folder 2" as an example. To check if that is private or public we can do this:

SELECT isPublic FROM folder WHERE folderName = "Child Folder 2";

(just an example, in the code we use the folder ids :))

This doesn't account for inheritance though. So the parent folder may be set as private.

Any idea how I can loop through any number of parent folders until a private state is found or there's no more parent folders. Ideally I need to end up with a 1 or a 0 returned from the query, 1 being public and 0 private.

Thanks in advance, Red.

A simple SQL query will not work - you will have to do it using a recursive stored procedure or in the application — Dobromir Velev, Nov 05 '15 at 12:32

score 1 · Answer 1 · answered Nov 05 '15 at 15:16

Here is a simple stored procedure that can do this in MySQL. It has two arguments testid for the id of the folder and param1 which will store the result

DELIMITER //
CREATE PROCEDURE test_public(IN testid INT,OUT param1 INT)
BEGIN
DECLARE parent,public INT DEFAULT -1;

WHILE testid!=0 DO
SELECT f.parentID,f.isPublic INTO parent,public FROM folder f WHERE f.id = testid ;
IF (parent is not NULL and public is not NULL and parent!=0 and public!=0) THEN
  set testid=parent;
ELSE
  set testid=0;
END  IF;
END WHILE;

select public into param1;
END//
delimiter ;

/* Usage - @a will be 1 if the folder is public, 0 if it is private */
call test_public(3,@a);
select @a;

score 1 · Answer 2 · edited May 23 '17 at 11:51

1

I figured it out with some help from here - How to do the Recursive SELECT query in MySQL?

select MIN(IF(isPublic>0, 1, 0)), @pv := if(isPublic>0,parentId,0) as 'parentId' 
from (select * from folder order by id desc) t
join (select @pv := 270) tmp
where id = @pv

The result is a 1 for public or 0 for private. I used IF(isPublic>0, 1, 0) above as isPublic can actually be a 1 or 2 in my code.

edited May 23 '17 at 11:51

Community

1
1

answered Nov 05 '15 at 15:28

Rachel Rogers

41
7

1

this a nice one. you can try min(if(isPublic>0, 1, 0)) instead sum and count, should return the same result – Dobromir Velev Nov 05 '15 at 16:23
there is one more thing I noticed - this will query all records until the top disregarding the fact that a private folder might be found earlier. I suggested an edit @pv := if(isPublic>0,parentId,0) that will stop the recursion if a private folder is detected – Dobromir Velev Nov 06 '15 at 12:06

score 0 · Answer 3 · answered Nov 05 '15 at 12:52

set folder name for that you wanna to get check that

check_public($folder_name);
function check_public($folder_name, $parent_id = FALSE) {
    $query = "select * from folder where ";
    echo $query.=$parent_id ? ("id =" . $parent_id) : ("folderName ='" . $folder_name . "'");exit;
    $result = mysql_query($query);
    if (mysql_num_rows($result)) {
        $row = mysql_fetch_object($result);
        if ($row->isPublic && $row->parentId) {
            check_public($folder_name, $row->parentId);
        } else {
            $public = $row->isPublic ? "public" : "private";
            echo $folder_name . ' is ' . $public;
            exit;
        }
    } else {
        echo 'folder name is not amtched in database';
    }
}

Thanks for the answer however I can already do it in PHP. I was hoping to do it at the database level as it'll be a lot more efficient. — Rachel Rogers, Nov 05 '15 at 15:00

MySQL Inheritance via 1 Table - Permissions

3 Answers3