Bài 23: Thay cấu trúc mà giữ dữ liệu

Câu lạc bộ muốn cho học viên chọn biệt danh để hiện trên màn hình lớp học. Thông tin mới cần chỗ lưu, nhưng tên thật, điểm và các lần đăng ký vẫn phải còn nguyên. Cô Mai không thể xóa bảng học viên rồi tạo lại vì các bảng khác đang liên kết tới từng người. Cô cần thay cấu trúc ngay trên dữ liệu đã có và kiểm kết quả trước khi dùng.

Thêm cột mới không cần tạo lại học viên

Migration là tập thay đổi cấu trúc cơ sở dữ liệu được quản lý theo phiên bản. Ví dụ, một phiên bản thêm cột biệt danh, phiên bản khác đổi tên cột. Mỗi thay đổi cần nêu điểm xuất phát, việc sẽ làm và trạng thái mong đợi sau khi chạy.

Lược đồ hiện tại của students chưa có cột biệt danh. Các học viên cũ cũng chưa cung cấp thông tin này. Ta chọn cột display_name TEXT cho phép NULL, tức là chưa có biệt danh, thay vì tự đoán tên mới cho từng người.

Lệnh ALTER TABLE thay cấu trúc một bảng đang có. Với ADD COLUMN, bạn ghi tên bảng, tên cột mới và kiểu của cột. Câu sau thêm cột rồi đọc lại dữ liệu để kiểm:

ALTER TABLE students ADD COLUMN display_name TEXT;
SELECT id, name, display_name
FROM students
ORDER BY id;

Kết quả vẫn có các mã và tên cũ. Cột display_name nhận NULL ở mọi dòng vì ta chưa cung cấp biệt danh hoặc khai báo giá trị mặc định. Không có học viên mới được tạo ra bởi việc thêm cột.

Chọn NULL có ý nghĩa cụ thể: người học chưa đặt biệt danh. Chuỗi rỗng lại là một giá trị chữ đã được lưu. Số không cũng không thể thay thế cho tình trạng chưa có tên, dù nó có thể giúp một kiểm tra đơn giản chạy qua.

Cột bắt buộc cần có dữ liệu hợp lệ cho người cũ

Giả sử yêu cầu đổi thành mọi học viên phải có biệt danh ngay. Đặt NOT NULL thể hiện yêu cầu đó, nhưng dữ liệu cũ chưa có giá trị để đáp ứng. Đây là vấn đề cần giải quyết trước khi chạy, không phải một lỗi nên bỏ qua.

Thử thêm cột bắt buộc nhưng không có giá trị mặc định vào bảng đang có học viên:

ALTER TABLE students ADD COLUMN display_name TEXT NOT NULL;

SQLite báo lỗi vì các dòng cũ sẽ thiếu giá trị. Câu lệnh thất bại nên không có chuyện một nửa học viên đã có cột còn nửa kia chưa có. Thay cấu trúc là thay quy định chung của bảng, không phải thêm từng ô rời.

Điền dữ liệu cũ (backfill) là bổ sung giá trị cho các dòng đã tồn tại theo một quy tắc được chấp thuận. Với biệt danh tự chọn, nguồn đúng thường là thông tin do học viên cung cấp. Tự tạo một chuỗi ngẫu nhiên chỉ để qua NOT NULL sẽ làm sai ý nghĩa dữ liệu.

Nếu tạm cho phép thiếu, giao diện có thể dùng tên thật khi chưa có biệt danh. Sau khi thu thập đủ, nhóm quản lý mới xem xét yêu cầu bắt buộc. Bước đổi ràng buộc tiếp theo cần kế hoạch riêng, không suy rằng sửa một từ trong câu lệnh là đủ.

Đọc mô tả cột để kiểm thay đổi đã áp dụng

Siêu dữ liệu (metadata) là thông tin mô tả dữ liệu, chẳng hạn tên cột và kiểu khai báo. Đọc các dòng học viên cho biết nội dung đang lưu. Đọc siêu dữ liệu cho biết bảng có cấu trúc nào, kể cả khi bảng không có dòng.

Trong SQLite, pragma_table_info('students') trả thông tin về các cột của bảng. Cột cid là vị trí cột, name là tên, type là kiểu khai báo. Cột notnull đánh dấu ràng buộc bắt buộc được khai báo trực tiếp.

Ta chuẩn bị một bản mẫu đã thêm biệt danh. Mỗi ví dụ có setup=with-display_name sẽ bắt đầu từ bản ấy, không dựa vào lần chạy trước của bạn:

Đọc tên, kiểu và yêu cầu có giá trị của các cột sau thay đổi:

SELECT name, type, "notnull" AS required
FROM pragma_table_info('students')
ORDER BY cid;

Dòng display_name có kiểu TEXT và required bằng 0. Điều đó khớp với thiết kế cho phép thiếu. Các tên cột cũ vẫn có mặt, giúp phát hiện việc vô tình đổi hoặc bỏ một cột mà chỉ xem tên học viên sẽ không thấy.

Thêm cột cũng cần giữ các ràng buộc cũ. Cột số xu vẫn phải khác âm sau khi thêm biệt danh. Hãy thử thêm một người có số xu âm trên bản đã thay cấu trúc:

INSERT INTO students(id, name, grade, city, coins, mentor)
VALUES (7, 'Minh', 6, 'Huế', -1, NULL);

Lỗi kiểm số xu cho thấy quy tắc cũ vẫn hoạt động trong ví dụ này. Chỉ thấy đủ tên cột chưa chứng minh các quy tắc còn nguyên. Với thay đổi lớn, cần thử hành vi của khóa, ràng buộc và các quan hệ liên quan.

Đổi tên cột giữ giá trị nhưng đổi cách truy vấn

RENAME COLUMN đổi tên một cột đang có. Nó không yêu cầu bạn nhập lại giá trị của cột. Tuy nhiên, câu truy vấn trong ứng dụng vẫn phải dùng đúng tên mới.

Ta thử đổi cột tên lớp thành label trong dữ liệu mẫu. Đây chỉ là tên được chọn để minh họa, không đổi ý nghĩa của lớp:

ALTER TABLE classes RENAME COLUMN name TO label;
SELECT id, label
FROM classes
ORDER BY id;

Các giá trị tên lớp còn nguyên và mã lớp không đổi. Những đăng ký đang trỏ tới mã lớp vẫn dùng cùng mã đó. Nhưng đoạn mã đọc classes.name sẽ cần sửa vì cột hiện có tên label.

Để nhìn rõ lỗi của một truy vấn cũ, ta chuẩn bị bản đã đổi tên:

Truy vấn dưới đây vẫn gọi tên cột cũ nên không còn chạy được:

SELECT id, name
FROM classes
ORDER BY id;

Lỗi thiếu cột khác với việc mất giá trị. Cần sửa nơi đọc và nơi ghi dùng tên cũ, rồi chạy lại các chức năng liên quan. SQLite có thể cập nhật một số tham chiếu trong lược đồ khi đổi tên, nhưng không sửa được chuỗi truy vấn nằm trong mã ứng dụng.

Kiểm trên bản sao và quản lý lần chạy lại

Một migration chạy không lỗi mới chứng minh câu lệnh đã được chấp nhận. Nó chưa chứng minh dữ liệu và ứng dụng còn đúng. Trước thay đổi thật, cần có bản sao có thể khôi phục và thử đúng kế hoạch trên bản sao ấy.

Quy trình sau tách rõ việc chuẩn bị, thực hiện và kiểm kết quả. Chỉ đánh dấu phiên bản đã áp dụng khi các bước cần thiết đã hoàn thành:

Vì sao phải theo dõi phiên bản? Nếu cột đã được thêm, chạy lại nguyên câu thêm cột sẽ tạo yêu cầu trùng tên. Thử lặp lại trên bản đã có biệt danh:

ALTER TABLE students ADD COLUMN display_name TEXT;

Lỗi không có nghĩa lần đầu thất bại. Người vận hành cần biết phiên bản nào đã chạy, thay vì đổi tên cột ngẫu nhiên hoặc xóa dữ liệu để chạy lại. Tự che mọi lỗi cũng có thể giấu việc cơ sở dữ liệu đang ở trạng thái ngoài dự kiến.

Khi đối chiếu, hãy kiểm cả nội dung lẫn quan hệ. Sau khi thêm cột biệt danh, truy vấn sau tìm đăng ký không còn học viên tương ứng:

SELECT e.student_id, e.class_id
FROM enrollments AS e
WHERE NOT EXISTS (
  SELECT 1
  FROM students AS s
  WHERE s.id = e.student_id
)
ORDER BY e.student_id, e.class_id;

Kết quả rỗng nghĩa là không tìm thấy liên kết mất đích theo phép kiểm này. Nó không thay kiểm tên, số xu hay các ràng buộc khác. Với việc chuyển sang bảng mới, còn phải đối chiếu cách ánh xạ từng cột và xử lý mọi dữ liệu không chuyển được.

Lỗi thường gặp

Tóm tắt

• Thêm cột cho phép thiếu giữ được dữ liệu cũ mà không tự tạo thông tin chưa có.
• Cột bắt buộc cần một kế hoạch hợp lệ cho những dòng đã tồn tại.
• Kiểm siêu dữ liệu giúp xác nhận cấu trúc, còn kiểm giá trị và hành vi giúp xác nhận dữ liệu.
• Đổi tên cột cần đi cùng việc rà các truy vấn sử dụng tên cũ.
• Bản sao, diễn tập và lịch sử phiên bản giúp thay đổi có thể kiểm chứng và khôi phục.

Kiểm tra nhanh

Đọc thêm

• SQLite: thêm cột, đổi tên cột và các giới hạn khi thay cấu trúc.
• SQLite: đọc thông tin cột của bảng.