Cách thiết lập đường dẫn tìm kiếm schema (lược đồ)

Các đối tượng như bảng (table) được tạo trong schema và các schema khác nhau có thể tạo một bảng có cùng tên, khi đó muốn truy cập bảng cần chỉ định theo định dạng tên_schema.tên_table để xác định được bảng đó thuộc schema nào.

Ví dụ: nếu bạn có một bảng students trong schemaA và cũng có một bảng students cùng tên trong schemaB, nếu truy vấn một cách bình thường như bên dưới thì không thể biết đang muốn lấy nội dung của bảng students thuộc schema nào.

SELECT * FROM students;

Trong trường hợp này cần phải chỉ định rõ ràng theo định dạng tên_schema.tên_table là schemaA.students hoặc schemaB.students

SELECT * FROM schemaA.students;

Tuy nhiên, nếu cứ chỉ định rõ ràng như vậy sẽ mất thời gian và công sức để mô tả tên_schema mỗi lần truy vấn. Vì vậy, nếu muốn bỏ qua tên_schema, bạn có thể sử dụng đường dẫn tìm kiếm schema. Nhiều schema có thể được đặt trong đường dẫn tìm kiếm schema.

schemaA, schemaB,...

Khi đó, nếu tên_schema bị bỏ qua và chỉ mô tả tên_table thì tên_table sẽ được kiểm tra từ đầu đường dẫn tìm kiếm schema cho dù nó có tồn tại trong schema hay không. Nếu một bảng phù hợp được tìm thấy, nó được xác định là tên bảng trong schema đó.

Để kiểm tra giá trị được đặt trong đường dẫn tìm kiếm schema sẽ thực hiện lệnh sau:

SHOW search_path;

Chúng ta nhận được kết quả:

"$user", public

Các schema trong đường dẫn tìm kiếm lược đồ được phân tách bằng dấu phẩy , “$user” là đại diện cho schema có cùng tên với tên role (user đang login vào database thực hiện truy vấn) hiện tại. Ở đây hiện đang kết nối với user (role) là postgres, nên kết quả nhận được ở trên với mô tả dưới đây là như nhau:

postgres, public

Chúng ta sẽ tìm hiểu cách sử dụng đường dẫn tìm kiếm schema khi thao tác với bảng bằng lệnh SELECT, CREATE.

Đầu tiên, lệnh truy vấn dữ liệu bảng SELECT:

Nếu bạn bỏ qua tên_schema và chỉ mô tả tên_table khi bạn muốn truy xuất dữ liệu từ bảng,… schema được liệt kê trong đường dẫn tìm kiếm schema sẽ được kiểm tra từ đầu đến cuối để xem có tồn tại bảng không.

Giả sử đường dẫn tìm kiếm schema như sau:

schemaA, schemaB, schemaC

và bạn cố gắng lấy dữ liệu bằng cách bỏ qua tên_schema và chỉ sử dụng tên bảng:

SELECT * FROM students;

Khi lệnh thực thi sẽ kiểm tra xem bảng students có trong schemaA hay không. Nếu tìm thấy, sẽ lấy dữ liệu dưới dạng schemaA.students. Nếu không tìm thấy, sẽ tiếp tục lặp qua hết các schemaB, schemaC đã được đặt trong đường dẫn tìm kiếm schema. Nếu vẫn không thể được tìm thấy cho đến khi kết thúc lặp, một lỗi sẽ xảy ra.

Ngay cả khi trong cơ sở dữ liệu có một lược đồ khác schemaA, schemaB, schemaC mà không được liệt kê trong đường dẫn tìm kiếm schema thì nó sẽ bỏ qua, không được kiểm tra.

Tiếp theo, lệnh tạo mới bảng CREATE:

Nếu tên_schema bị bỏ qua và tên_table được mô tả thì sẽ kiểm tra xem danh sách schema được mô tả trong đường dẫn tìm kiếm schema có thực sự tồn tại không. Nếu không sẽ xảy ra lỗi, nếu có thì sẽ tạo bảng trong schema đã tìm thấy đó.

Giả sử đường dẫn tìm kiếm schema như sau:

myschema, myschema1, myschema2

Sẽ thực hiện kiểm tra một cách tuần tự từ lược đồ myschema và nếu tìm thấy một lược đồ thực sự tồn tại, lược đồ đó trở thành lược đồ hiện tại.

Mặc định public là lược đồ hiện tại khi tạo bảng mà không chỉ định tên_schema

CREATE TABLE students(id int, name text);

Trường hợp có chỉ định tên_schema khi tạo bảng:

CREATE TABLE myschema.students(id int, name text);

Nếu bạn chỉ muốn thay đổi đường dẫn tìm kiếm lược đồ tạm thời trong phiên hiện tại thì hãy sử dụng lệnh SET để thực hiện.

SET search_path TO tên_schema1,tên_schema2,...;

Ví dụ: nếu bạn muốn thiết lâp đường dẫn tìm kiếm lược đồ tạm thời trong phiên hiện tại thành myschema, public thì làm như sau:

SET search_path TO myschema,public;

Đường dẫn tìm kiếm lược đồ đã thay đổi:

và lược đồ hiện tại cũng đã thay đổi thành myschema, không còn là public nữa.

SELECT current_schema();

Bây giờ chúng ta thử tạo một bảng mới mytable mà không chỉ định tên_schema thì sẽ như thế nào!??

CREATE TABLE mytable(id int, name text);

Lúc này, khi muốn truy cập bảng mytable bạn có thể sử dụng 1 trong 2 cách sau:

SELECT * FROM mytable;

hoặc là:

SELECT * FROM myschema.mytable;

Schema public giờ là thành phần thứ 2 trong đường dẫn tìm kiếm. Vì vậy để truy cập bảng students đã tạo ở mục 3. Cách sử dụng đường dẫn tìm kiếm schema, bạn phải chỉ định đầy đủ theo định dạng tên_schema.tên_table như sau:

SELECT * FROM public.students;

Trường hợp bạn muốn thay đổi vĩnh viễn đường dẫn tìm kiếm lược đồ, hãy sử dụng lệnh ALTER ROLE.

ALTER ROLE user SET search_path TO tên_schema1,tên_schema2,...;

Ví dụ: nếu bạn muốn thay đổi vĩnh viễn đường dẫn tìm kiếm lược đồ thành myschema, public thì làm như sau:

ALTER ROLE postgres SET search_path TO myschema,public;

Nếu bạn muốn quay lại để giá trị mặc định như ban đầu thì:

ALTER ROLE postgres SET search_path TO default;

Để thay đổi đường dẫn tìm kiếm lược đồ mặc định (default schema search path) cho tất cả người dùng (all users) thì chúng ta sửa đổi file cấu hình PostgreSQL postgresql.conf. (Xem bài viết cách cài đặt file postgresql.conf để biết chi tiết về vị trí và cách cấu hình file postgresql.conf).

Sau khi mở file postgresql.conf, bạn tìm đến vị trí sau:

Hiện tại, đang comment cài đặt liên quan đến search_path. Vì vậy hãy xóa dấu # ở đầu, sau đó đặt giá trị bạn muốn đặt làm đường dẫn tìm kiếm lược đồ mặc định vào là sẽ thay đổi đường dẫn tìm kiếm lược đồ mặc định cho tất cả users.

Sau khi thiết lập bạn có thể kiểm tra thay đổi bằng cách thực thi lệnh SHOW search_path;