Khởi tạo môi trường và kết nối PostgreSQL
Trước khi thực hiện các thao tác trên bảng Temporal, bạn cần đảm bảo đang ở trong terminal của Ubuntu 24.04 và đã kết nối vào instance PostgreSQL được cài đặt trong Phần 1.
Mở terminal và chuyển sang user postgres để truy cập trực tiếp vào database engine, đảm bảo quyền root cho các thao tác tạo bảng.
sudo -i -u postgres psql -d company_db
Kết quả mong đợi: Bạn sẽ thấy prompt thay đổi từ dấu $ thành dấu > (ví dụ: company_db=>), xác nhận bạn đã ở trong shell của PostgreSQL.
Thiết kế và tạo bảng Nhân sự Temporal
Cú pháp định nghĩa bảng với lịch sử tự động
Chúng ta sẽ tạo bảng employees với cơ chế Temporal Table. PostgreSQL sử dụng từ khóa SYSTEM VERSIONING để tự động quản lý lịch sử. Cột valid_from và valid_to sẽ được hệ thống tự sinh ra và cập nhật, không cần ghi trong câu lệnh INSERT.
Định nghĩa cột history_table để chỉ định nơi lưu trữ các phiên bản cũ khi dữ liệu bị thay đổi hoặc xóa. Đây là bước then chốt để tách biệt dữ liệu hiện tại và dữ liệu lịch sử.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10, 2),
position VARCHAR(50)
) WITH (
SYSTEM VERSIONING USING (
valid_from TIMESTAMP WITHOUT TIME ZONE,
valid_to TIMESTAMP WITHOUT TIME ZONE
),
history_table = employees_history
);
Kết quả mong đợi: Thông báo "CREATE TABLE" xuất hiện. Hệ thống tự động tạo thêm bảng employees_history với cấu trúc tương tự nhưng thêm các cột thời gian để lưu trữ các bản ghi cũ.
Chèn dữ liệu ban đầu và quan sát cơ chế
Thao tác INSERT dữ liệu hiện tại
Thực hiện chèn 3 nhân viên mẫu vào bảng employees. Lưu ý: Bạn KHÔNG cần khai báo giá trị cho các cột valid_from và valid_to. PostgreSQL sẽ tự động gán thời điểm hiện tại (current_timestamp) vào valid_from và giá trị vô cực ('infinity') vào valid_to.
INSERT INTO employees (employee_id, full_name, department, salary, position)
VALUES
(101, 'Nguyen Van A', 'IT', 15000000, 'Developer'),
(102, 'Tran Thi B', 'HR', 12000000, 'Recruiter'),
(103, 'Le Van C', 'Sales', 10000000, 'Sales Manager');
Kết quả mong đợi: Thông báo "INSERT 0 3" xác nhận 3 bản ghi đã được chèn thành công.
Verify: Kiểm tra dữ liệu hiện tại và bảng lịch sử
Truy vấn bảng chính để xem dữ liệu hiện tại. Sau đó truy vấn bảng lịch sử để đảm bảo nó đang trống (vì chưa có bản ghi nào bị thay đổi).
SELECT * FROM employees;
SELECT * FROM employees_history;
Kết quả mong đợi: Bảng employees hiện 3 dòng với valid_to là 'infinity'. Bảng employees_history trả về 0 dòng.
Thực hiện UPDATE và theo dõi phiên bản
Thao tác cập nhật thông tin lương
Giả sử nhân viên ID 101 được tăng lương. Khi thực hiện câu lệnh UPDATE, PostgreSQL sẽ tự động thực hiện hai hành động: (1) Cập nhật bản ghi trong bảng chính, (2) Di chuyển phiên bản cũ của bản ghi đó vào bảng employees_history và đóng khoảng thời gian valid_to của phiên bản cũ bằng thời điểm thực hiện UPDATE.
UPDATE employees
SET salary = 18000000, position = 'Senior Developer'
WHERE employee_id = 101;
Kết quả mong đợi: Thông báo "UPDATE 1" xác nhận 1 bản ghi đã được cập nhật.
Verify: Phân tích sự thay đổi trong bảng lịch sử
Kiểm tra lại bảng employees để thấy thông tin mới. Sau đó, truy vấn bảng employees_history để quan sát bản ghi cũ của nhân viên 101. Bạn sẽ thấy valid_to của bản ghi cũ đã thay đổi từ 'infinity' sang một timestamp cụ thể (thời điểm bạn chạy lệnh UPDATE).
SELECT employee_id, full_name, salary, valid_from, valid_to FROM employees WHERE employee_id = 101;
SELECT employee_id, full_name, salary, valid_from, valid_to FROM employees_history WHERE employee_id = 101 ORDER BY valid_from;
Kết quả mong đợi:
- Bảng employees: Hiển thị lương 18.000.000, valid_to vẫn là 'infinity'.
- Bảng employees_history: Hiển thị bản ghi cũ với lương 15.000.000, valid_to là timestamp vừa mới cập nhật.
Xóa dữ liệu và phân tích dòng thời gian
Thao tác DELETE để sa thải nhân viên
Thực hiện xóa nhân viên ID 102 khỏi bảng chính. Cơ chế Temporal hoạt động tương tự UPDATE: bản ghi hiện tại không bị mất đi hoàn toàn mà được "chuyển" sang bảng lịch sử, đóng lại khoảng thời gian tồn tại của nó tại thời điểm xóa.
DELETE FROM employees WHERE employee_id = 102;
Kết quả mong đợi: Thông báo "DELETE 1" xác nhận 1 bản ghi đã được xóa khỏi bảng chính.
Verify: Kiểm tra trạng thái dữ liệu sau khi xóa
Truy vấn bảng employees để xác nhận nhân viên 102 không còn hiện diện (vì bảng này chỉ chứa dữ liệu hiện tại). Sau đó, kiểm tra bảng employees_history để thấy bản ghi của nhân viên 102 vẫn tồn tại với trạng thái đã kết thúc (valid_to có giá trị thời gian cụ thể).
SELECT * FROM employees;
SELECT * FROM employees_history WHERE employee_id = 102;
Kết quả mong đợi:
- Bảng employees: Chỉ còn 2 nhân viên (101 và 103).
- Bảng employees_history: Chứa 2 bản ghi (một của 101 do UPDATE, một của 102 do DELETE). Bản ghi của 102 có valid_to là thời điểm xóa.
Tổng hợp và xác minh toàn bộ quy trình
Để đảm bảo toàn bộ cơ chế hoạt động chính xác, hãy chạy một câu lệnh thống kê đơn giản để đếm số lượng bản ghi trong cả hai bảng và so sánh với số lần thao tác đã thực hiện.
SELECT 'Current Data' as type, COUNT(*) as count FROM employees
UNION ALL
SELECT 'Historical Data' as type, COUNT(*) as count FROM employees_history;
Kết quả mong đợi: Bạn sẽ thấy 2 dòng kết quả. Dòng "Current Data" có giá trị 2 (số nhân viên còn lại), dòng "Historical Data" có giá trị 2 (số lần thay đổi: 1 lần tăng lương + 1 lần sa thải).
Điều hướng series:
Mục lục: Series: Triển khai Database Temporal với PostgreSQL và Ubuntu 24.04
« Phần 2: Khái niệm cơ bản và thiết kế bảng Temporal trong PostgreSQL
Phần 4: Truy vấn dữ liệu theo thời gian: Điểm và Khoảng thời gian »