24 lines
589 B
MySQL
24 lines
589 B
MySQL
|
WITH RECURSIVE folder_hierarchy AS (
|
||
|
|
-- Start with the given directory
|
||
|
|
SELECT
|
||
|
|
folder_id
|
||
|
|
FROM
|
||
|
|
folders
|
||
|
|
WHERE
|
||
|
|
folder_id = $1
|
||
|
|
|
||
|
|
UNION ALL
|
||
|
|
|
||
|
|
-- Recursively find all subdirectories
|
||
|
|
SELECT
|
||
|
|
f.folder_id
|
||
|
|
FROM
|
||
|
|
folders f
|
||
|
|
INNER JOIN
|
||
|
|
folder_hierarchy fh ON f.parent_folder_id = fh.folder_id
|
||
|
|
)
|
||
|
|
INSERT INTO permissions(user_id, folder_id, permission_type)
|
||
|
|
SELECT $2::integer as user_id, fh.folder_id::UUID as folder_id, $3
|
||
|
|
FROM folder_hierarchy fh
|
||
|
|
ON CONFLICT (user_id, folder_id) DO UPDATE
|
||
|
|
SET permission_type = $3
|