PostgreSQL模式介紹

標籤：

一個PostgreSQL資料庫叢集包含一個或多個已命名資料庫。使用者和使用者組在整個叢集範圍內是共用的，但是其它資料並不共用。任何與伺服器串連的客戶都只能訪問那個在串連請求裡聲明的資料庫。

注意: 叢集中的使用者並不一定要有訪問叢集內所有資料庫的許可權。共用使用者名稱的意思是不能有重名使用者。假定同一個叢集裡有兩個資料庫和一個joe使用者，系統可以配置成只允許joe 訪問其中的一個資料庫。

一個資料庫包含一個或多個已命名的模式，模式又包含表。模式還可以包含其它對象，包括資料類型、函數、操作符等。同一個對象名可以在不同的模式裡使用而不會導致衝突；比如，schema1和myschema都可以包含一個名為mytable的表。和資料庫不同，模式不是嚴格分離的：只要有許可權，一個使用者可以訪問他所串連的資料庫中的任意模式中的對象。

我們需要模式的原因有好多：

• 允許多個使用者使用一個資料庫而不會干擾其它使用者。

• 把資料庫物件組織成邏輯組，讓它們更便於管理。

• 第三方的應用可以放在不同的模式中，這樣它們就不會和其它對象的名字衝突。

模式類似於作業系統層次的目錄，只不過模式不能嵌套。

1. 建立模式

要建立一個模式，使用CREATE SCHEMA命令。給出你選擇的模式名字。比如：

CREATE SCHEMA myschema;

要建立或者訪問在模式中的對象，寫出一個受修飾的名字，這個名字包含模式名以及表名，它們之間用一個句點分開：

schema.table

這個方式在任何需要表名字的地方都可用，包括後面章節討論的表修改命令和資料訪問命令。出於簡化，我們將只討論表，這個概念適用於所有其它已命名物件類型，比如資料類型和函數。

實際上，更一般的文法

database.schema.table

也可以使用，但目前它只是為了和 SQL 標準形式上相容。如果你寫了一個資料庫名，那麼它必須和你當前串連的資料庫同名。

要在新模式裡建立一個表，用

CREATE TABLE myschema.mytable ( ...);

如果一個模式是空的(所有它裡面的對象都已經刪除)，那麼刪除一個模式的命令如下：

DROP SCHEMA myschema;

要刪除一個模式及其包含的所有對象，可以使用：

DROP SCHEMA myschema CASCADE;

 

通常你想建立一個他人擁有的模式(因為這是一種限制使用者在定義良好的模式中的活動的方法)。其文法如下：

CREATE SCHEMA schemaname AUTHORIZATION username;

你甚至可以省略模式名字，這時模式名將和使用者名稱同名。

以pg_開頭的模式名是保留給系統使用的，使用者不能建立這樣的名字。

2. Public 模式

在前面的小節裡，我們沒有聲明任何模式名字就建立了表。預設時，這樣的表(以及其它對象)都自動放到一個叫做"public"的模式中去了。每個新資料庫都包含一個這樣的模式。因此，下面的命令是等效的：

CREATE TABLE products ( ... );

和：

CREATE TABLE public.products ( ... );

3. 模式搜尋路徑

全稱的名字寫起來非常費勁，並且我們最好不要在應用裡直接寫上特定的模式名。因此，表通常都是用未修飾的名字引用的，這樣的名字裡只有表名字。系統通過尋找一個搜尋路徑來判斷一個表究竟是哪個表，這個路徑是一個需要尋找的模式名列表。在搜尋路徑裡找到的第一個表將被使用。如果在搜尋路徑中沒有找到表，那麼就報告一個錯誤(即使在資料庫裡的其它模式中存在此表也如此)。

在搜尋路徑中的第一個模式叫做"當前模式"。除了是搜尋的第一個模式之外，它還是在CREATE TABLE沒有聲明模式名的時候，建立表的預設所在地。

要顯示當前搜尋路徑，使用下面的命令：

SHOW search_path;

在預設的設定中，返回下面的東西：

 search_path-------------- "$user",public

第一個元素宣告搜尋和目前使用者同名的模式。因為還沒有這樣的模式存在，所以這條記錄被忽略。第二個元素指向我們已經看過的公用模式。

搜尋路徑中第一個存在的模式是建立新對象的預設位置。這就是為什麼預設的對象都會建立在 public 模式裡的原因。如果在其它環境中引用對象且沒有模式修飾，那麼系統會遍曆搜尋路徑，直到找到一個匹配的對象。因此，在預設的配置裡，任何未修飾的訪問只能引用 public 模式。

要設定模式的搜尋路徑，可以用(省略了$user是因為並不立即需要它)

SET search_path TO myschema,public;

然後我們就可以不使用模式修飾來訪問表了：

DROP TABLE mytable;

同樣，因為myschema是路徑中的第一個元素，新對象預設時將建立在這裡。

我們也可以寫成：

SET search_path TO myschema;

然後我們如果不明確修飾的話，就不能再訪問 public 模式了。public 模式沒有任何特殊之處，只不過它預設時就存在。我們也可以刪除它。

搜尋路徑對於資料類型名、函數名、操作符名的運作方式和表名完全相同。資料類型和函數名可以像表名一樣加以修飾。如果你需要在運算式裡寫一個有模式修飾的操作符，你必須這麼寫：

OPERATOR(schema.operator)

這樣是為了避免文法歧義。下面是一個例子：

SELECT 3 OPERATOR(pg_catalog.+) 4;

實踐中我們通常依賴搜尋路徑尋找操作符，這樣就不用寫這麼難看的東西了。

4. 模式和許可權

預設時，使用者無法訪問模式中不屬於他們所有的對象。為了讓他們能夠訪問，模式的所有者需要在模式上賦予他們USAGE許可權。為了讓使用者使用模式中的對象，我們可能需要賦予適合該對象的額外許可權。

使用者也可以在別人的模式裡建立對象。要允許這麼做，需要被賦予在該模式上的CREATE許可權。請注意，預設時每個人都在public模式上有CREATE和USAGE許可權。這樣就允許所有可以串連到指定資料庫上的使用者在這裡建立對象。如果你不打算這麼做，可以撤銷這個許可權：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

第一個"public"是模式，第二個"public"意思是"所有使用者"。第一句裡它是個標識符，而第二句裡是個關鍵字，所以有不同的大小寫。

5. 系統資料表模式

除了public和使用者建立的模式之外，每個資料庫都包含一個pg_catalog模式，它包含系統資料表和所有內建資料類型、函數、操作符。pg_catalog總是搜尋路徑中的一部分。如果它沒有明確出現在路徑中，那麼它隱含地在所有路徑之前搜尋。這樣就保證了內建名字總是可以被搜尋。不過，你可以明確地把pg_catalog放在搜尋路徑之後，如果你想使用使用者自訂的名字覆蓋內建的名字的話。

自從系統資料表名以pg_開頭開始，最好避免使用這樣的名字，以保證自己將來不會和新版本衝突：那些版本也許會定義一些和你的表同名的表(在預設搜尋路徑中，一個對你的表的無修飾引用將解析為系統資料表)。系統資料表將繼續遵循以pg_開頭的傳統，因此，只要你的表不是以pg_開頭，就不會和無修飾的使用者表名字衝突。

6. 使用方式

模式可以用多種方式組織資料。下面是一些建議使用的模式，它們也很容易在預設配置中得到支援：

• 如果沒有建立任何模式，那麼所有使用者隱含都訪問 public 模式。這樣就類比了沒有模式的時候的情景。這種設定建議主要用在只有一個使用者或者資料庫裡只有幾個可信使用者的情形。這樣的設定也允許我們平滑地從無模式的環境過渡。

• 你可以為每個使用者建立一個模式，名字和使用者相同。要記得預設的搜尋路徑從$user開始，它會解析為使用者名稱。因此，如果每個使用者都有一個獨立的模式，那麼他們預設時訪問他們自己的模式。

  如果你使用了這樣的設定，那麼你可能還想撤銷對 public 模式的訪問(或者刪除它)，這樣，使用者就真的限制於他們自己的模式了。

• 要安裝共用的應用(被所有人使用的表、第三方提供的額外函數等等)，我們可以把它們放到獨立的模式中。只要記得給需要訪問它們的使用者賦予合適的許可權就可以了。然後使用者就可以通過用一個模式名修飾來使用這些額外的對象，或者他們可以把額外的模式放到他們的搜尋路徑中。

7. 移植性

在 SQL 標準裡，在同一個模式裡的對象被不同的使用者所有的概念是不存在的。而且，有些實現不允許你建立和它們的所有者不同名的模式。實際上，模式的概念和使用者在那些只實現了標準中規定的基本模式支援的資料庫系統裡幾乎是一樣的。因此，許多使用者考慮對名字加以修飾，使它們真正由username.tablename 組成。如果你為每個使用者都建立了一個模式，這實際上就是PostgreSQL的行為。

同樣，在 SQL 標準裡也沒有public模式的概念。為了最大限度地遵循標準，你不應該使用(可能甚至是應該刪除)public模式。

當然，有些資料庫系統可能根本沒有模式，或者是通過允許跨資料庫訪問來提供模式的功能。如果你需要在這些系統上幹活，那麼為了最大限度的移植性，應該根本不使用模式。

PostgreSQL模式介紹