查詢分層文件權限

我有一個表格代表一些文件及其路徑，另一個表格代表使用者和文件的文件共享。

下面是簡化的表格文件。例子：

• 我有一個包含一些文件的目錄 A（路徑 /A）。
• 目錄 B 在 A 內部，它的路徑是 /A/B，它包含一些文件。
• 文件 C（路徑 /A/B/C）和 D（路徑 /A/B/D）在 B 中。

下面是簡化的表file_sharings。如果一個使用者有一個path=’/A’的文件共享並且readable=true，這意味著他可以讀取所有以’/A’開頭的路徑的文件

現在我在這個文件中有它（這只是一個例子，它可能更複雜）：

因此，這意味著使用者 1 可以看到目錄 A 中的所有內容，但他看不到目錄 B 和文件 C，但他可以看到文件 D（例如，我們可以使用 google 驅動器的相同方式）

我想要的是查詢使用者 1 可以看到的所有文件/目錄。

目前我有這樣的事情：

SELECT *
FROM files
WHERE path LIKE ANY (SELECT fs.path || '%'
   FROM file_sharings fs
   WHERE fs.user_id='1'
   AND fs.readable=true)
AND (path NOT LIKE ANY (SELECT fs.path || '%'
   FROM file_sharings fs
   WHERE fs.user_id='1'
   AND fs.readable=false)
);

但它只返回：

/A

如果我做：

SELECT *
FROM files
WHERE path LIKE ANY (SELECT fs.path || '%'
   FROM file_sharings fs
   WHERE fs.user_id='1'
   AND fs.readable=true)
AND (path NOT IN (SELECT fs.path
   FROM file_sharings fs
   WHERE fs.user_id='1'
   AND fs.readable=false)
);

…它返回：

/A
/A/B/C
/A/B/D

我不知道如何擁有使用者 1 可以讀取的所有文件，除了他無法讀取的文件。這裡我必須有/Aand /A/B/D，但實際上樹文件可能更複雜，但原則與這個簡單範例中的相同。

在現實生活中這很容易，但我堅持創造良好的要求：D

這裡有一個dbfiddle

當我在數據庫中沒有行時，這意味著使用者沒有可讀權限。

當我有一個可讀 = true 的行時，這意味著使用者對文件和子項（儲存庫中的文件）具有可讀權限。

當我有一個可讀 = false 的行時，這意味著該文件及其子文件對使用者是明確禁止的。

我這樣做是因為我不想在將根目錄共享給另一個使用者時創建十億行。

file_id是一個外鍵，僅用於在我將文件/儲存庫移動到另一個文件/儲存庫時重新計算 file_sharings 路徑。

所以我可以對文件擁有可讀權限，而它在數據庫中沒有明確

這是 user1 的小提琴範例的圖片：

有了這個要求：

SELECT *
FROM files
WHERE path LIKE ANY (SELECT fs.path || '%'
   FROM file_sharings fs
   WHERE fs.user_id='1'
   AND fs.readable=true)
AND (path NOT LIKE ANY (SELECT fs.path || '%'
   FROM file_sharings fs
   WHERE fs.user_id='1'
   AND fs.readable=false)
);

它返回A1和。我也應該有。B2``C3``C2

對於第二個請求：

SELECT *
FROM files
WHERE path LIKE ANY (SELECT fs.path || '%'
   FROM file_sharings fs
   WHERE fs.user_id='1'
   AND fs.readable=true)
AND (path NOT IN (SELECT fs.path
   FROM file_sharings fs
   WHERE fs.user_id='1'
   AND fs.readable=false)
);

它返回A1、B2、C3和。我不應該有。C2``C1``C1

您沒有聲明除 之外的任何約束NOT NULL，因此我假設了合理的約束，如下面的小提琴所示。

我不明白 的作用file_id，所以我完全忽略了它。（似乎與問題正交。）

您的邏輯歸結為：每個文件根據最長匹配路徑具有權限。因此，將每個文件與其中的行匹配file_sharings並LIKE選擇路徑最長的行。只有帶有readable標誌的文件才能存活：

SELECT (f).*
FROM  (
  SELECT DISTINCT ON (f.id)
         f, s.readable
  FROM   files f
  JOIN   file_sharings s ON f.path LIKE s.path || '%'
  WHERE  s.user_id = 1
  ORDER  BY f.id, s.path DESC  -- choose the longest matching path !
  ) sub
WHERE  readable
ORDER  BY (f).path;

產生您想要的結果。

db<>在這裡擺弄

我簡化了您的小提琴，添加了所需的UNIQUE約束，並修復了與問題同步的數據錯誤。

在過濾子查詢之前，必須選擇子查詢中最長路徑的匹配項readable。

關於DISTINCT ON：

• 在每個 GROUP BY 組中選擇第一行？

根據未公開的基數和數據分佈，可能有一些方法可以提高性能……

在旁邊

為方便起見，我在子查詢中選擇了整行f- 這樣我就不必在 external 中拼出表的所有列file名SELECT。

這是一個沒有這個技巧的更詳細的形式：

SELECT id, name, path
FROM  (
  SELECT DISTINCT ON (f.id)
         f.*, s.readable
  FROM   files f
  JOIN   file_sharings s ON f.path LIKE s.path || '%'
  WHERE  s.user_id = 1
  ORDER  BY f.id, s.path DESC  -- choose the longest matching path !
  ) sub
WHERE  readable
ORDER  BY path;

在更改稍後的表定義後會出現一個細微的差異file（除了短程式碼） ：第一個查詢繼續返回表定義中列出的所有列（結果隨更改的表而變化），而第二個查詢繼續返回明確列出的列。“後期綁定”與“早期綁定”。

引用自：https://dba.stackexchange.com/questions/308552