SQLite ATTACH DATABASE: truy vấn chéo nhiều tệp

Một kết nối, nhiều tệp

Một kết nối SQLite không bị gắn chặt với một tệp duy nhất. Với ATTACH DATABASE, bạn có thể mở thêm các tệp .db khác bên cạnh tệp ban đầu, rồi truy vấn tất cả như thể chúng là các schema trong cùng một cơ sở dữ liệu. Đây là thứ gần nhất với "nhiều cơ sở dữ liệu trên một máy chủ" mà SQLite có.

Dạng cơ bản:

ATTACH DATABASE 'archive.db' AS archive;

CREATE TABLE archive.old_orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    total REAL
);

INSERT INTO archive.old_orders VALUES (1, 'Rosa', 49.99);

SELECT * FROM archive.old_orders;

Tệp archive.db sẽ được tạo nếu chưa tồn tại, giống như cơ sở dữ liệu chính. Từ giờ trong phiên làm việc này, mọi thứ có tiền tố archive. đều nằm trong tệp thứ hai đó. Mọi thứ có tiền tố main. (hoặc không có tiền tố) nằm trong tệp gốc.

Kết nối của bạn luôn có sẵn hai schema ngầm định: main (tệp bạn mở đầu tiên) và temp (vùng nháp cho các bảng tạm). Việc gắn thêm sẽ bổ sung các schema khác.

Cú pháp và vai trò của alias

ATTACH DATABASE 'path/to/file.db' AS alias_name;

Alias là tên schema bạn dùng để ghi rõ bảng thuộc về đâu. Nó chỉ có hiệu lực trong kết nối hiện tại: một kết nối khác gắn cùng tệp đó có thể chọn alias khác. Hãy chọn tên ngắn gọn và dễ hiểu (archive, analytics, cache) vì bạn sẽ gõ nó rất nhiều.

Vài điều đáng biết:

• Đường dẫn được tính tương đối so với thư mục làm việc của tiến trình, trừ khi là đường dẫn tuyệt đối.
• Chuỗi ':memory:' gắn một cơ sở dữ liệu trong bộ nhớ mới dưới alias đó.
• Alias không được trùng với main hoặc temp, và không được lặp lại giữa các lần gắn.

JOIN giữa các cơ sở dữ liệu

Đây là lý do chính khiến phần lớn mọi người dùng ATTACH. Khi hai tệp đã nằm trong cùng một kết nối, bạn có thể JOIN các bảng của chúng trong một truy vấn duy nhất:

ATTACH DATABASE ':memory:' AS archive;

CREATE TABLE main.customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE archive.orders (id INTEGER, customer_id INTEGER, total REAL);

INSERT INTO main.customers VALUES (1, 'Rosa'), (2, 'Boris');
INSERT INTO archive.orders VALUES (101, 1, 49.99), (102, 2, 12.50);

SELECT c.name, o.total
FROM main.customers AS c
JOIN archive.orders AS o ON o.customer_id = c.id;

Query planner xử lý cả hai schema giống hệt cách nó xử lý các bảng trong main. Chỉ mục trên các bảng được gắn vẫn được dùng. EXPLAIN QUERY PLAN hoạt động trên cả hai. Không có lượt đi về qua mạng nào: cả hai tệp đều được mở trong cùng một tiến trình.

Tính năng này thật sự hữu ích để tách dữ liệu nóng khỏi kho lưu trữ dữ liệu cũ, chia tệp riêng cho từng khách hàng (tenant), hoặc lấy dữ liệu tham chiếu từ một cơ sở dữ liệu tra cứu chỉ đọc.

Gắn ở chế độ chỉ đọc và trong bộ nhớ

Nếu cơ sở dữ liệu thứ hai là thứ bạn chỉ muốn đọc chứ không bao giờ sửa, chẳng hạn một bộ dữ liệu tham chiếu đi kèm ứng dụng, hãy gắn nó ở chế độ chỉ đọc bằng một URI:

ATTACH DATABASE 'file:reference.db?mode=ro' AS ref;

Dạng URI yêu cầu thư viện SQLite bật SQLITE_OPEN_URI (CLI và phần lớn thư viện kết nối cho các ngôn ngữ đều bật sẵn). Khi đó mọi lệnh INSERT, UPDATE hay DELETE nhắm vào ref.* sẽ báo lỗi trước khi đụng đến tệp.

Gắn cơ sở dữ liệu trong bộ nhớ cũng tiện không kém để chuẩn bị dữ liệu trung gian:

ATTACH DATABASE ':memory:' AS scratch;

CREATE TABLE scratch.tmp_results AS
SELECT customer_id, SUM(total) AS spend
FROM main.orders
GROUP BY customer_id;

SELECT * FROM scratch.tmp_results ORDER BY spend DESC LIMIT 5;

scratch biến mất khi kết nối đóng lại. Nó giống temp nhưng bạn tự quyết định vòng đời của nó.

Transaction bao trùm mọi cơ sở dữ liệu đã gắn

Một cặp BEGIN/COMMIT duy nhất bao trùm các thao tác ghi vào main và mọi schema đã gắn. Hoặc tất cả cùng commit, hoặc tất cả cùng rollback: tính nguyên tử được giữ nguyên giữa các tệp:

ATTACH DATABASE ':memory:' AS archive;

CREATE TABLE main.orders (id INTEGER PRIMARY KEY, total REAL);
CREATE TABLE archive.orders (id INTEGER PRIMARY KEY, total REAL);

BEGIN;
    INSERT INTO archive.orders SELECT * FROM main.orders WHERE id < 100;
    DELETE FROM main.orders WHERE id < 100;
COMMIT;

Chuyển các hàng từ một bảng đang dùng sang tệp lưu trữ chính là loại thao tác cần đến sự đảm bảo này. Không có tính nguyên tử giữa các tệp, một sự cố giữa chừng sẽ để lại các hàng bị trùng, hoặc tệ hơn, các hàng bị mất.

Một lưu ý: khi có nhiều hơn một cơ sở dữ liệu đã gắn được ghi trong cùng một transaction, SQLite dùng một giao thức commit thận trọng hơn, cần đến một tệp journal tạm. Cách này chậm hơn commit trên một tệp, nhưng vẫn an toàn.

Tách cơ sở dữ liệu

Khi đã dùng xong một cơ sở dữ liệu được gắn, hãy bỏ nó ra:

DETACH DATABASE archive;

Tệp vẫn nằm nguyên trên đĩa: DETACH chỉ đóng handle trong kết nối hiện tại. Có hai hạn chế cần nhớ:

• Bạn không thể tách main hay temp.
• Bạn không thể tách một cơ sở dữ liệu đang ở trong một transaction hoặc còn câu lệnh đang mở trên nó.

Nếu quên tách thì cũng chẳng sao: đóng kết nối sẽ dọn dẹp mọi thứ.

Giới hạn và các lỗi thường gặp

Vài giới hạn thực tế đáng biết:

• Mức trần mặc định là 10 cơ sở dữ liệu được gắn trên mỗi kết nối (cộng thêm main và temp). Mức tối đa lúc biên dịch là 125. Chạm giới hạn, bạn sẽ thấy too many attached databases - max 10.
• Mỗi tệp được gắn dùng một bộ nhớ đệm trang (page cache) riêng. Gắn cả chục cơ sở dữ liệu lớn không hề miễn phí: RAM sẽ tăng lên.
• Bản thân ATTACH không chạy được bên trong transaction. Hãy chạy nó trước BEGIN, hoặc sau COMMIT.

Vài lỗi bạn có thể sẽ gặp:

-- File doesn't exist and the directory isn't writable:
Error: unable to open database: 'missing/path.db'

-- You tried to write to a read-only attachment:
Error: attempt to write a readonly database

-- You used the same alias twice:
Error: database archive is already in use

Phần lớn các lỗi này đọc là hiểu ngay. Lỗi "already in use" hay khiến người ta vấp: ATTACH không thay thế alias đã có; bạn phải DETACH trước.

Một mô hình thực tế: tách dữ liệu nóng và lạnh

Gộp tất cả lại: một quy trình lưu trữ nhỏ chuyển các đơn hàng cũ hơn một năm ra khỏi cơ sở dữ liệu chính:

ATTACH DATABASE ':memory:' AS archive;

CREATE TABLE main.orders (
    id INTEGER PRIMARY KEY,
    placed_at TEXT,
    total REAL
);
CREATE TABLE archive.orders AS SELECT * FROM main.orders WHERE 0;

INSERT INTO main.orders VALUES
    (1, '2022-06-01', 19.99),
    (2, '2024-11-15', 49.50),
    (3, '2025-08-22', 12.00);

BEGIN;
    INSERT INTO archive.orders
        SELECT * FROM main.orders WHERE placed_at < date('now', '-1 year');
    DELETE FROM main.orders WHERE placed_at < date('now', '-1 year');
COMMIT;

SELECT 'main' AS db, COUNT(*) FROM main.orders
UNION ALL
SELECT 'archive', COUNT(*) FROM archive.orders;

Các hàng cũ chuyển sang archive.orders, các hàng gần đây ở lại main. Báo cáo cần dữ liệu lịch sử có thể JOIN giữa cả hai; các truy vấn hằng ngày trên main.orders vẫn nhanh vì bảng nhỏ hơn. Cùng một kết nối, hai tệp, một transaction.

Tiếp theo: prepared statement

ATTACH là để cho một kết nối truy cập được nhiều dữ liệu hơn. Nhóm chủ đề tiếp theo nói về cách ứng dụng làm việc với SQLite an toàn và hiệu quả, bắt đầu từ prepared statement, nền tảng của việc gắn tham số và các truy vấn chống injection.