5.9. Schemas

Schema 在台灣並沒有習慣的中文說法,所以仍使用原文,而不翻譯。

PostgreSQL 資料庫叢集(cluster)可以包含一個或多個資料庫。使用者和群組則是共用於叢集的層次,但沒有任何資料面是在資料庫之間能共用的。任何用戶端連到資料庫服務,都只能存取單一資料庫,你必須在連線時指定一個資料庫。

注意

在叢集內的使用者並不需要對每個資料庫都有使用權。使用者共用指的是它們不能有同名的情況,例如在同一個叢集內,不能有兩個使用者名稱都叫 joe。但系統可以只允許 joe 使用某些叢集內的資料庫。

一個資料庫可以包含一個或多個 schema,它會包含一些資料表。Schema 也可以包含一些資料庫物件,像是資料型別、函數、和運算子。同樣的物件名稱在不同的 schema 中是不會衝突的。舉例來說,schema1 和 myschema 都可以擁有一個叫作 mytable 的資料表。和資料庫不同, schema 並不是完全隔離的:使用者可以直接取用他們連接的資料庫中的任何 schema,只要他們擁有足夠的權限。

使用 schema 有幾個好處:

  • 允許多個使用者存取相同資料庫,而不會互相干擾。
  • 將資料庫物件建立邏輯上的管理層,它們會更有彈性。
  • 第三方的應用結構可以放在不同的 schema 中,避免有撞名的情況產生。

Schema 和作業系統裡的資料夾是類似的,只是它不能使用巢狀結構。

5.8.1. 建立 Schema

要建立 schema,請使用 CREATE SCHEMA 指令。給予一個自訂的名稱。例如:

  1. CREATE SCHEMA myschema;

要在 schema 中建立或存取某個物件,請使用句點(.)將兩者名稱串連起來:

  1. schema.table

這個形式在任何可以使用資料表的地方都是可以的,包含資料表結構更新指令,以及在接下來章節會討論到的資料處理指令。(我們只提到資料表的部份,但相同的概念用於其他資料庫物件都是一樣的,像是資料型別和函數。)

實際上,更一般化的語法是:

  1. database.schema.table

也可以這樣使用,但目前這只是為了符合 SQL 標準而已。如果你填上了資料庫的名稱,也必須填上你所連線的資料庫而已。

所以,要在新的 schema 中建立一個資料表,請使用:

  1. CREATE TABLE myschema.mytable (
  2. ...
  3. );

要移除一個 schema,它必須要是空的,也就是所有所屬物件都已經被移除了,請使用:

  1. DROP SCHEMA myschema;

但你也可以同步移除 schema 及其所屬物件,請使用:

  1. DROP SCHEMA myschema CASCADE;

這個部份的機制請參閱 5.13 節,會深入介紹移除時的問題。

通常你會想要建立一個 schema 給某個使用者使用(這是一種藉由命名空間規畫來限制使用者權限的方法)。可以使用下列語法:

  1. CREATE SCHEMA schema_name AUTHORIZATION user_name;

你甚至可以省略 schema 名稱,省略的話,schema 名稱會與使用者名稱相同。請參閱後續的 5.8.6 節來瞭解如何使用。

Schema 名稱以「pg_」開頭的,是系統的保留名稱,使用者不能使用這樣的名稱建立 schema。

5.8.2. 公開的 Schema

在前面我們所建立的資料表都沒有指定 schema 名稱。預設使用的 schema 是「public」,每一個資料庫都會有這個 schema。所以,下面兩種寫法是一樣的:

  1. CREATE TABLE products ( ... );

以及:

  1. CREATE TABLE public.products ( ... );

5.8.3. Schema 搜尋路徑

完整的名稱寫法是冗長而不容易使用的,通常最好不要把一些特別的 schema 名稱寫到應用程式裡。而資料表時常是以簡要的寫法引用,也就是只寫資料表本身的名稱。資料庫系統依據搜尋路徑的規則找到該資料表。在搜尋路徑上所遇到的第一個資料表就會被使用。如果整個搜尋路徑走完都沒有符合的資料表,那麼才會回報錯誤,即使該資料表名稱有出現在資料庫裡的其他 schema 中。

第一個會被搜尋的 schema,就是目前的 schema。除此之外也用於新的資料表建立,當 CREATE TABLE 未指定 schema 名稱的話,也會依搜尋路徑的 schema 建立。

要顯示目前的搜尋路徑,請使用下面的指令:

  1. SHOW search_path;

預設的情況是:

  1. search_path
  2. --------------
  3. "$user", public

第一個項目指的就是和目前使用者同名的 schema 會被使用,而如果沒有同名的,它就會被忽略。第二個項目則是先前介紹過的公開 schema。第一個被找到的 schema,就會是新建物件時預設的位置,這就是為什麼預設都會被建立在公開的 schema。當某個物件在使用(資料表結構調整、資料更新、或查詢指令)時沒有註明 schema 的話,那也會使用搜尋路徑來找到符合的物件。不過,預設上只會搜尋公開的 schema。

要設定新的搜尋路徑,請使用:

  1. SET search_path TO myschema,public;

(我們在這邊暫時忽略掉 $user,因為還沒有立即性的需要。)然後我們就可以試著存取資料表而不用加上 schema:

  1. DROP TABLE mytable;

因為 myschema 在搜尋路徑裡是第一個項目,所以新的物件就會被建立在該處。

我們也可以這樣寫:

  1. SET search_path TO myschema;

這樣的話,不指定的話就不再能夠再使用公開的 schema 了。「public」schema 並沒有比較特別,除了它一開始就會存在之外,它也可以被移除。

請參閱 9.25 節,將會介紹其他設定 schema 搜尋路徑的方式。

搜尋路徑也用於資料型別、函數、及運算子的搜尋,就如同在資料表上的行為一樣。資料型別和函數名稱完整的寫法也和資料表相同。如果你需要特別指出運算子的完整路徑的話,它比較特別,你必須這樣寫:

  1. OPERATOR(schema.operator)

這是為了避免語法上的混淆。如下所示:

  1. SELECT 3 OPERATOR(pg_catalog.+) 4;

實務上我們都還是依賴路徑搜尋來使用運算子,這樣可以避免使用冗長且低可讀性的程式碼。

5.8.4. Schemas 與權限

預設的情況,使用者無法存取任何不屬於他們的 schema 中的物件。要允許存取的話,該 schema 的擁有者必須要授予 USAGE 權限給其他使用者。要允許其他使用者使用某個 schema 中的物件,通常需要額外給予適當的權限。

使用者想要在其他使用者的 schema 中建立新物件的話,就必須要授予 CREATE 的權限。注意,預設上,所有的使用者在 public schema 中,都具備 CREATE 和 USAGE 權限。這使得所有的使用者在連線到某個資料庫之後,就可以在 public schema 上新增物件。如果你不希望這樣,你可以移除這些權限:

  1. REVOKE CREATE ON SCHEMA public FROM PUBLIC;

前面的「public」指的是 schema,是一個物件識別器;而後面的「PUBLIC」指的是所有使用者,是一個關鍵字。所以使用不同的大小寫,可以再複習 4.1.1 節的內容。

5.8.5. 系統資訊 Schema

除了 public 以及使用者自行建立的 schema 之外,每一個資料庫還有一個稱作 pg_catalog 的 schema,它包含了系統資訊的資料表和內建的資料型別、函數、及運算子。 pg_catlog 永遠都都是搜尋路徑裡的有效項目。它沒有明確地顯示在搜尋路徑裡,但卻是隱含優先搜尋,在那些明定的搜尋項目之前。這是為了確保內建的物件的名稱都能被找到。然而,你可以把 pg_catlog 放在搜尋路徑的最後面,如果你希望自訂的同名物件能優先被使用的話。

系統用的資料表都以「pg_」開頭,為的就是確保不會有衝突的情況出現,以免將來新的系統資料表和你現在所定義的資料表同名。(以預設的搜尋路徑來說,一個簡單的資料表使用,會直接被同名的系統資料表取代。)系統資料表會一直遵循這個命名規則,就不會產生衝突,只要使用者不使用「 pg_」開頭的命名方式就好了。

5.8.6. 使用樣版

Schema 可以在許多方面協助你組織你的資料。有一些巧妙的樣版值得推薦,也很方便以預設的方式支援:

  • 如果你沒有建立任何 schema 的話,那麼所有使用者就是隱含著都使用 public schema。這種情況指的是都沒有設定任何 schema,而主要推薦給在一個資料庫中,只有一個使用者的情況。這樣的樣版設定也適合之後轉換到無 schema 設計的資料庫環境。
  • 你可以為每一個使用者建立一個同名的 schema。回想一下先前介紹的預設搜尋路徑,第一個項目就是 $user,表示該使用者的名稱。所以,每一個使用者有一個專屬的 schema,預設上,他們就只存取他們所擁有的 schema。 如果你使用這個情境樣版,你也許會需要移除 public schema 的權限,甚至直接移除它,讓使用者真正被隔離在他們自己的 schema 中。
  • 要安裝共享的應用程式(每個人共享資料表,有一些第三方提供的延伸套件,或其他的東西。),把他們放到不同的 schema 裡,然後記得要設定好適當的存取權限。使用者可以使用完整的名稱來存取這些共享的應用程式,或把他們加入到搜尋路徑中,由使用者自己來決定。

5.8.7. 可攜性

在標準 SQL 中,在同一個 schema 中的物件,分別被不同使用者擁有,是不被允許的。然而,有一些實作系統甚至不允許使用者建立和自己不同名的 schema。事實上,schema 和使用者的概念,對於只支援基本 schema 的資料庫系統本身而言,幾乎是相同的。所以,許多使用者會認為完整名稱指的是 user_name.table_name。這也就是為什麼 PostgreSQL 建議你這樣為每一個使用者建立他們同名的 schema。

再者,在標準 SQL 裡,也沒有所謂 public schema 的概念。極致相容標準的話,你就不應該使用,或移除 public schema。

當然,也有些 SQL 資料庫並沒有實作 schema,或提供其他跨資料庫存取的命名方式。如果你需要和這些系統共同運作,要提高可攜性的方式就是不要使用任何 schema。