Quản trị Postgresql Schema

Trong PostgreSQL, schema là một khái niệm quan trọng để tổ chức và quản lý các đối tượng cơ sở dữ liệu. Schema cung cấp một cách để phân chia và sắp xếp các đối tượng cơ sở dữ liệu như bảng (tables), chỉ mục (indexes), view, hàm (functions), và trình kích hoạt (triggers) thành các nhóm logic. Điều này giúp dễ dàng quản lý và kiểm soát quyền truy cập hơn.

Đặc điểm của Schema

1. Tách biệt không gian tên (namespace): Schema cung cấp một không gian tên riêng biệt cho các đối tượng. Điều này có nghĩa là bạn có thể có nhiều đối tượng có cùng tên trong cùng một cơ sở dữ liệu miễn là chúng nằm trong các schema khác nhau.
2. Quản lý quyền truy cập: Bạn có thể kiểm soát quyền truy cập đến các đối tượng trong schema bằng cách cấp quyền cụ thể cho người dùng hoặc nhóm người dùng.
3. Tổ chức logic: Schema giúp tổ chức các đối tượng cơ sở dữ liệu theo cách logic. Ví dụ, bạn có thể tạo các schema khác nhau cho các mô-đun khác nhau của ứng dụng.

Truy cập vào Schema:

schema_name.object_name

public.staff

Nếu trong câu select mà không chỉ rõ table nằm trong schema nào => postgresql sẽ tự động tìm schema khớp và hiển thị kết quả đầu tiên trong schema hiện tại, nếu không tồn tại table, hoặc table nằm trong 1 schema khác không phải hiện tại thì hệ thống sẽ trả về lỗi.

## lệnh xem schema đang kết nối hiện tại:

SELECT current_schema();

Cách tạo Schema

Bạn có thể tạo một schema mới bằng lệnh CREATE SCHEMA trong PostgreSQL:

CREATE SCHEMA ten_schema;

CREATE SCHEMA my_schema;

Sử dụng Schema

Khi bạn tạo một đối tượng trong cơ sở dữ liệu, bạn có thể chỉ định schema mà đối tượng đó sẽ nằm trong:

CREATE TABLE my_schema.my_table ( id SERIAL PRIMARY KEY, name VARCHAR(100) );

Chuyển đổi giữa các Schema

Bạn có thể thay đổi schema hiện tại của phiên làm việc bằng cách sử dụng lệnh SET search_path:

SET search_path TO ten_schema;

Ví dụ

Tạo Schema và Bảng

##Tạo một schema mới:

CREATE SCHEMA sales;

##Tạo một bảng trong schema sales:

CREATE TABLE sales.customers ( customer_id SERIAL PRIMARY KEY, customer_name VARCHAR(100), contact_email VARCHAR(100) );

Truy cập Bảng trong Schema

Bạn có thể truy cập bảng trong schema bằng cách chỉ định tên schema trước tên bảng:

SELECT * FROM sales.customers;

Hoặc thay đổi search_path để truy cập trực tiếp mà không cần chỉ định schema:

SET search_path TO sales; SELECT * FROM customers;

Quản lý Quyền Truy cập

Bạn có thể cấp quyền truy cập cho người dùng hoặc nhóm người dùng cho các đối tượng trong schema:

GRANT USAGE ON SCHEMA sales TO user_name;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO user_name;

ALTER SCHEMA:

1. Đổi tên schema

ALTER SCHEMA old_schema_name RENAME TO new_schema_name;

ALTER SCHEMA sales RENAME TO marketing;

2. Đổi chủ sở hữu của schema

ALTER SCHEMA schema_name OWNER TO new_owner;

ALTER SCHEMA marketing OWNER TO new_user;

3. Thay đổi tìm kiếm đường dẫn (search path)

SET search_path TO schema1, schema2, …;

SET search_path TO marketing, public;

4. Thay đổi thuộc tính của các đối tượng trong schema

Để thay đổi các thuộc tính của các đối tượng trong schema, bạn có thể cần phải sử dụng các lệnh ALTER tương ứng cho từng loại đối tượng. Ví dụ, để đổi tên một bảng trong một schema:

ALTER TABLE schema_name.table_name RENAME TO new_table_name;

ALTER TABLE marketing.customers RENAME TO clients;

5. Di chuyển các đối tượng giữa các schema

Để di chuyển một bảng từ schema này sang schema khác, bạn có thể sử dụng lệnh ALTER TABLE … SET SCHEMA …:

ALTER TABLE schema_name.table_name SET SCHEMA new_schema_name;

ALTER TABLE marketing.clients SET SCHEMA public;

Tương tự, bạn có thể di chuyển các loại đối tượng khác (như views, indexes, sequences, etc.) bằng cách thay đổi cú pháp phù hợp:

##Di chuyển view:

ALTER VIEW schema_name.view_name SET SCHEMA new_schema_name;

##Di chuyển sequence:

ALTER SEQUENCE schema_name.sequence_name SET SCHEMA new_schema_name;

##Di chuyển index:

ALTER INDEX schema_name.index_name SET SCHEMA new_schema_name;

DROP SCHEMA:

Trong PostgreSQL, lệnh DROP SCHEMA được sử dụng để xóa một schema cùng với tất cả các đối tượng bên trong nó (như bảng, chỉ mục, view, hàm, và các đối tượng khác).

Cú pháp cơ bản

DROP SCHEMA schema_name [CASCADE | RESTRICT];

• schema_name: Tên của schema bạn muốn xóa.
• CASCADE: Tùy chọn này sẽ xóa schema cùng với tất cả các đối tượng phụ thuộc vào nó.
• RESTRICT: Tùy chọn này sẽ ngăn không cho schema bị xóa nếu có bất kỳ đối tượng nào phụ thuộc vào nó. Đây là tùy chọn mặc định.

Ví dụ

Xóa một schema không có đối tượng phụ thuộc

DROP SCHEMA temp_schema;

Nếu temp_schema không chứa bất kỳ đối tượng nào hoặc không có bất kỳ đối tượng nào phụ thuộc vào nó, lệnh này sẽ thực thi thành công.

Xóa một schema cùng với tất cả các đối tượng bên trong nó

DROP SCHEMA temp_schema CASCADE;

Lệnh này sẽ xóa temp_schema và tất cả các đối tượng bên trong nó.

Ngăn chặn việc xóa schema nếu có đối tượng phụ thuộc

Nếu bạn sử dụng tùy chọn RESTRICT, lệnh sẽ ngăn chặn việc xóa schema nếu có bất kỳ đối tượng nào phụ thuộc vào nó:

DROP SCHEMA temp_schema RESTRICT;

Nếu temp_schema có các đối tượng phụ thuộc, lệnh này sẽ không thực thi và PostgreSQL sẽ báo lỗi.