Lệnh PRAGMA trong SQLite - SQLite căn bản và nâng cao

Lệnh PRAGMA trong SQLite là một lệnh đặc biệt được sử dụng để kiểm soát các biến môi trường và cờ (flag) trạng thái khác nhau trong môi trường SQLite.

1. Cú pháp lệnh PRAGMA trong SQLite

Để truy vấn giá trị PRAGMA hiện tại, chỉ cần cung cấp tên của pragma.

PRAGMA pragma_name;

Để đặt giá trị mới cho PRAGMA, hãy sử dụng cú pháp sau.

PRAGMA pragma_name = value;

Chế độ đặt có thể là tên hoặc số nguyên tương đương nhưng giá trị được trả về sẽ luôn là số nguyên.

2. Pragma  auto_vacuum trong SQLite

Pragma auto_vacuum trong SQLite được dùng để get hoặc set auto-vacuum mode. Cú pháp như sau:

PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = mode;

Mode có thể là bất kỳ giá trị sau đây:

3. Pragma  cache_size trong SQLite

Pragma cache_size được dùng để get hoặc set kích thước tối đa tạm thời của bộ đệm trang (page cache) trong bộ nhớ. Cú pháp như sau:

PRAGMA [database.]cache_size;
PRAGMA [database.]cache_size = pages;

Giá trị pages đại diện cho số lượng trang trong bộ đệm. Page cache có kích thước mặc định 2.000 trang và kích thước tối thiểu 10 trang.

4. Pragma case_sensitive_like trong SQLite

Pragma case_sensitive_like điều khiển chế độ phân biệt chữ hoa chữ thường của biểu thức LIKE. Theo mặc định, pragma này là false, có nghĩa là toán tử LIKE bỏ qua phân biệt hoa thường. Cú pháp như sau:

PRAGMA case_sensitive_like = [true|false];

Không có cách nào để truy vấn trạng thái hiện tại của pragma này.

5. Pragma count_changes trong SQLite

Pragma count_changes được dùng để get hoặc set giá trị trả về của các câu lệnh thao tác dữ liệu như INSERT, UPDATE và DELETE. Cú pháp như sau:

PRAGMA count_changes;
PRAGMA count_changes = [true|false];

Theo mặc định, pragma này là false và các câu lệnh này không trả lại bất cứ điều gì. Nếu được đặt thành true, mỗi câu lệnh ở trên sẽ trả về một bảng một cột, một hàng bao gồm một giá trị số nguyên duy nhất cho biết các hàng bị ảnh hưởng bởi thao tác.

6. Pragma database_list trong SQLite

Pragma database_list được sử dụng để liệt kê tất cả các attached databases. Cú pháp như sau:

PRAGMA database_list;

Pragma này sẽ trả về một bảng ba cột với một hàng trên mỗi cơ sở dữ liệu mở hoặc đính kèm với số thứ tự cơ sở dữ liệu, tên của nó và file được liên kết.

7. Pragma encoding trong SQLite 

Pragma encoding dùng để kiểm soát cách các chuỗi được mã hóa và lưu trữ trong một file cơ sở dữ liệu. Cú pháp như sau:

PRAGMA encoding;
PRAGMA encoding = format;

Giá trị định dạng có thể là một trong các giá trị định dạng như UTF-8, UTF-16le hoặc UTF-16be.

8. Pragma freelist_count trong SQLite

Pragma freelist_count trả về một số nguyên duy nhất cho biết có bao nhiêu trang cơ sở dữ liệu hiện được đánh dấu là free và available. Cú pháp như sau:

PRAGMA [database.]freelist_count;

Giá trị định dạng có thể là một trong giá trị định dạng như UTF-8, UTF-16le hoặc UTF-16be.

9. Pragma index_info trong SQLite

Pragma index_info trả về thông tin về một chỉ mục cơ sở dữ liệu. Cú pháp như sau:

PRAGMA [database.]index_info( index_name );

Tập kết quả sẽ chứa một hàng cho mỗi cột có trong chuỗi cột cho chỉ mục, chỉ mục cột có tên trong bảng và tên cột.

10. Pragma index_list trong SQLite

Pragma index_list liệt kê tất cả các chỉ mục được liên kết với một bảng. Cú pháp như sau:

PRAGMA [database.]index_list( table_name );

Tập kết quả sẽ chứa một hàng cho mỗi chỉ mục đưa ra chuỗi chỉ mục, tên chỉ mục và cờ cho biết liệu chỉ mục có duy nhất hay không.

11. Pragma journal_mode trong SQLite

Pragma journal_mode được dùng để get hoặc set journal mode kiểm soát cách lưu trữ và xử lý journal file. Cú pháp như sau:

PRAGMA journal_mode;
PRAGMA journal_mode = mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode = mode;

Có 5 journal mode được hỗ trợ trong SQLite được liệt kê trong bảng sau.

12. Pragma max_page_count trong SQLite

Pragma max_page_count được dùng để get hoặc set số lượng trang tối đa được phép cho cơ sở dữ liệu. Cú pháp như sau:

PRAGMA [database.]max_page_count;
PRAGMA [database.]max_page_count = max_page;

Giá trị mặc định là 1.073.741.823 là một giga-page, có nghĩa là nếu kích thước trang 1 KB mặc định, điều này cho phép cơ sở dữ liệu tăng lên đến một terabyte.

13. Pragma page_count trong SQLite

Pragma page_count trả về số lượng trang hiện tại trong cơ sở dữ liệu. Cú pháp như sau:

PRAGMA [database.]page_count;

Kích thước của file cơ sở dữ liệu phải là page_count * page_size.

14. Pragma page_size trong SQLite 

Pragma page_size được dùng để get hoặc set kích thước của các trang cơ sở dữ liệu. Cú pháp như sau:

PRAGMA [database.]page_size;
PRAGMA [database.]page_size = bytes;

Theo mặc định, kích thước được phép là 512, 1024, 2048, 4096, 8192, 16384 và 32768 byte. Cách duy nhất để thay đổi kích thước trang trên cơ sở dữ liệu hiện tại là đặt kích thước trang và sau đó VACUUM ngay lập tức cơ sở dữ liệu.

17. Pragma parser_trace trong SQLite

Pragma parser_trace kiểm soát việc in trạng thái debug khi nó phân tích các lệnh SQL. Cú pháp như sau:

PRAGMA parser_trace = [true|false];

Theo mặc định, nó được đặt thành false nhưng khi được bật bằng cách đặt thành true, trình phân tích cú pháp SQL sẽ in trạng thái của nó khi phân tích cú pháp các lệnh SQL.

18. Pragma recursive_triggers trong SQLite

Pragma recursive_triggers được dùng để get hoặc set chức năng kích hoạt đệ quy. Cú pháp như sau:

PRAGMA recursive_triggers;
PRAGMA recursive_triggers = [true|false];

19. Pragma schema_version trong SQLite

Pragma schema_version được dùng để get hoặc set giá trị phiên bản lược đồ được lưu trữ trong tiêu đề cơ sở dữ liệu. Cú pháp như sau:

PRAGMA [database.]schema_version;
PRAGMA [database.]schema_version = number;

Đây là giá trị nguyên được ký 32 bit, theo dõi các thay đổi của lược đồ. Bất cứ khi nào một lệnh thay đổi lược đồ được thực thi (như, CREATE... hoặc DROP ...), giá trị này sẽ tăng lên.

20. Pragma secure_delete trong SQLite

Pragma secure_delete được sử dụng để kiểm soát cách xóa nội dung khỏi cơ sở dữ liệu. Cú pháp như sau:

PRAGMA secure_delete;
PRAGMA secure_delete = [true|false];
PRAGMA database.secure_delete;
PRAGMA database.secure_delete = [true|false];

Giá trị mặc định cho cờ xóa an toàn thường tắt, nhưng điều này có thể được thay đổi với tùy chọn SQLITE_SECURE_DELETE.

21. Pragma sql_trace trong SQLite

Pragma sql_trace được sử dụng để theo dỏi kết quả SQL trên màn hình. Cú pháp như sau:

PRAGMA sql_trace;
PRAGMA sql_trace = [true|false];

SQLite phải được biên dịch với chỉ thị SQLITE_DEBUG để đưa vào pragma này.

22. Pragma synchronous trong SQLite

Pragma synchronous  được sử dụng để get hoặc set chế độ đồng bộ hóa disk hiện tại, điều khiển SQLite sẽ ghi dữ liệu mạnh mẽ như thế nào vào bộ nhớ vật lý. Cú pháp như sau:

PRAGMA [database.]synchronous;
PRAGMA [database.]synchronous = mode;

SQLite hỗ trợ các chế độ synchronization được liệt kê trong bảng sau:

23. Pragma temp_store trong SQLite

 Pragma temp_store được sử dụng để get hoặc set chế độ lưu trữ được sử dụng bởi các file cơ sở dữ liệu tạm thời. Cú pháp như sau:

PRAGMA temp_store;
PRAGMA temp_store = mode;

SQLite hỗ trợ các chế độ lưu trữ sau.

24. Pragma temp_store_directory trong SQLite

Pragma temp_store_directory được sử dụng để get hoặc set vị trí được sử dụng cho các file cơ sở dữ liệu tạm thời. Cú pháp như sau:

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';

25. Pragma user_version trong SQLite

Pragma user_version được sử dụng để get hoặc set giá trị phiên bản do người dùng xác định được lưu trữ trong tiêu đề cơ sở dữ liệu. Cú pháp như sau:

PRAGMA [database.]user_version;
PRAGMA [database.]user_version = number;

Đây là giá trị số nguyên có chữ ký 32 bit, có thể được nhà phát triển đặt cho mục đích theo dõi phiên bản.

26. Pragma writable_schema trong SQLite

Pragma writable_schema được sử dụng để get hoặc set khả năng sửa đổi các bảng hệ thống. Cú pháp như sau:

PRAGMA writable_schema;
PRAGMA writable_schema = [true|false];

Nếu pragma này được đặt, các bảng bắt đầu bằng sqlite_ có thể được tạo và sửa đổi, bao gồm bảng sqlite_master. Hãy cẩn thận trong khi sử dụng pragma vì nó có thể dẫn đến lead hoàn toàn cơ sở dữ liệu.