Cấu trúc của bảng: kiểu dữ liệu và ràng buộc

Khi mô tả các trường của bảng, bạn cần chỉ định kiểu dữ liệu cho chúng, nhưng khái niệm về kiểu dữ liệu trong SQLite rất khác biệt so với MQL5.

MQL5 là một ngôn ngữ có kiểu mạnh: mỗi biến hoặc trường cấu trúc luôn giữ kiểu dữ liệu theo khai báo. Ngược lại, SQL là một ngôn ngữ có kiểu yếu: các kiểu mà chúng ta chỉ định trong mô tả bảng không gì khác ngoài một khuyến nghị. Chương trình có thể ghi một giá trị của bất kỳ kiểu nào vào bất kỳ "ô" nào (một trường trong bản ghi), và "ô" đó sẽ thay đổi kiểu của nó, điều này đặc biệt có thể được phát hiện bởi hàm MQL tích hợp sẵn DatabaseColumnType.

Tất nhiên, trong thực tế, hầu hết người dùng có xu hướng tuân thủ việc "tôn trọng" các kiểu cột.

Sự khác biệt đáng kể thứ hai trong cơ chế kiểu của SQL là sự hiện diện của một số lượng lớn từ khóa mô tả kiểu, nhưng tất cả các từ này cuối cùng quy về năm lớp lưu trữ. Là một phiên bản đơn giản hóa của SQL, SQLite trong hầu hết các trường hợp không phân biệt các từ khóa trong cùng một nhóm (ví dụ, trong mô tả một chuỗi với giới hạn độ dài VARCHAR(80), giới hạn này không được kiểm soát, và mô tả tương đương với lớp lưu trữ TEXT), vì vậy sẽ hợp lý hơn nếu mô tả kiểu bằng tên nhóm. Các kiểu cụ thể chỉ được giữ lại để tương thích với các DBMS khác (nhưng điều này không quan trọng với chúng ta).

Bảng sau liệt kê các kiểu MQL5 và "mối quan hệ" tương ứng của chúng (có nghĩa là các đặc điểm tổng quát của kiểu SQL).

Kiểu MQL5	Kiểu SQL tổng quát
NULL (không phải kiểu trong MQL5)	NULL (không có giá trị)
bool, char, short, int, long, uchar, ushort, uint, ulong, datetime, color, enum	INTEGER
float, double	REAL
(số thực có độ chính xác cố định, không có tương tự trong MQL5)	NUMERIC
string	TEXT
(dữ liệu "thô" tùy ý, tương tự mảng uchar[] hoặc các loại khác)	BLOB (binary large object), NONE

Khi ghi một giá trị vào cơ sở dữ liệu SQL, nó xác định kiểu của giá trị theo một số quy tắc:

• Không có dấu nháy, dấu chấm thập phân hoặc số mũ cho ra INTEGER
• Sự hiện diện của dấu chấm thập phân và số mũ có nghĩa là REAL
• Việc bao quanh bằng dấu nháy đơn hoặc đôi báo hiệu kiểu TEXT
• Giá trị NULL không có dấu nháy tương ứng với lớp NULL
• Các hằng số (constants) với dữ liệu nhị phân được ghi dưới dạng chuỗi thập lục phân có tiền tố x

Hàm SQL đặc biệt typeof cho phép bạn kiểm tra kiểu của một giá trị. Ví dụ, truy vấn sau có thể được chạy trong MetaEditor.

SELECT typeof(100), typeof(10.0), typeof('100'), typeof(x'1000'), typeof(NULL);

Nó sẽ xuất ra bảng kết quả:

integer        |        real        |        text        |        blob        |        null

Bạn không thể kiểm tra giá trị NULL bằng cách so sánh '=' (vì kết quả cũng sẽ cho NULL), bạn nên sử dụng toán tử đặc biệt NOT NULL.

SQLite áp đặt một số giới hạn đối với dữ liệu được lưu trữ: một số trong đó khó đạt được (và do đó chúng ta sẽ bỏ qua ở đây), nhưng những giới hạn khác có thể được xem xét khi thiết kế chương trình. Vì vậy, số lượng cột tối đa trong bảng là 2000, và kích thước của một hàng, BLOB, và nói chung một bản ghi không thể vượt quá một triệu byte. Giá trị tương tự được chọn làm giới hạn độ dài truy vấn SQL.

Về ngày và giờ, SQL về lý thuyết có thể lưu trữ chúng ở ba định dạng, nhưng chỉ định dạng đầu tiên khớp với datetime trong MQL5:

• INTEGER — số giây kể từ 1970.01.01 (còn được gọi là "kỷ nguyên Unix")
• REAL — số ngày (có phần thập phân) từ ngày 24 tháng 11 năm 4714 trước Công nguyên
• TEXT — ngày và giờ với độ chính xác đến mili giây ở định dạng "YYYY-MM-DD HH:mm:SS.sss", tùy chọn có múi giờ, được thêm hậu tố "[±]HH:mm" với độ lệch từ UTC

Kiểu lưu trữ ngày thực (còn gọi là ngày Julian, có hàm SQL tích hợp sẵn Julianday) thú vị ở chỗ nó cho phép lưu trữ thời gian chính xác đến mili giây. Về lý thuyết, điều này cũng có thể được thực hiện dưới dạng chuỗi định dạng 'YYYY-MM-DDTHH:mm:SS.sssZ', nhưng cách lưu trữ này rất không kinh tế. Việc chuyển đổi "ngày" thành số giây với phần thập phân, bắt đầu từ ngày quen thuộc 1970.01.01 00:00:00, được thực hiện theo công thức: julianday('now') - 2440587.5) * 86400.0. Now ở đây biểu thị thời gian UTC hiện tại nhưng có thể được thay đổi thành các giá trị khác được mô tả trong tài liệu SQLite. Hằng số 2440587.5 chính xác bằng số ngày "lịch" cho ngày "zero" được chỉ định — điểm bắt đầu của "kỷ nguyên Unix".

Ngoài kiểu, mỗi trường có thể có một hoặc nhiều ràng buộc, được ghi bằng các từ khóa đặc biệt sau kiểu. Một ràng buộc mô tả các giá trị mà trường có thể nhận và thậm chí cho phép tự động hoàn thành theo mục đích được xác định trước của trường.

Hãy xem xét các ràng buộc chính.

... DEFAULT expression

Khi thêm một bản ghi mới, nếu giá trị trường không được chỉ định, hệ thống sẽ tự động nhập giá trị (hằng số) được chỉ định ở đây hoặc tính toán biểu thức (hàm).

... CHECK ( boolean_expression )

Khi thêm một bản ghi mới, hệ thống sẽ kiểm tra rằng biểu thức, có thể chứa tên trường làm biến, là đúng. Nếu biểu thức sai, bản ghi sẽ không được chèn và hệ thống sẽ trả về lỗi.

... UNIQUE

Hệ thống kiểm tra rằng tất cả các bản ghi trong bảng có giá trị khác nhau cho trường này. Việc cố gắng thêm một mục với giá trị đã tồn tại sẽ dẫn đến lỗi và việc thêm sẽ không xảy ra.

Để theo dõi tính duy nhất, hệ thống ngầm tạo một chỉ mục cho trường được chỉ định.

... PRIMARY KEY

Trường được đánh dấu bằng thuộc tính này được hệ thống sử dụng để xác định các bản ghi trong bảng và liên kết đến chúng từ các bảng khác (đây là cách các mối quan hệ quan hệ được hình thành, đặt tên cho các cơ sở dữ liệu quan hệ như SQLite). Rõ ràng, tính năng này cũng bao gồm một chỉ mục duy nhất.

Nếu bảng không có trường kiểu INTEGER với thuộc tính PRIMARY KEY, hệ thống tự động ngầm tạo một cột như vậy có tên rowid. Nếu bảng của bạn có một trường số nguyên được khai báo là khóa chính, thì nó cũng có sẵn dưới bí danh rowid.

Nếu một bản ghi với rowid bị bỏ qua hoặc NULL được thêm vào bảng, SQLite sẽ tự động gán cho nó số nguyên tiếp theo (64-bit, tương ứng với long trong MQL5), lớn hơn rowid tối đa trong bảng 1 đơn vị. Giá trị ban đầu là 1.

Thông thường, bộ đếm chỉ tăng dần từng đơn vị, nhưng nếu số lượng bản ghi từng được chèn vào một bảng (và có thể sau đó bị xóa) vượt quá long, bộ đếm sẽ nhảy về đầu và hệ thống sẽ cố gắng tìm các số trống. Nhưng điều này khó xảy ra. Ví dụ, nếu bạn ghi các tick vào một bảng với tốc độ trung bình 1 tick mỗi mili giây, thì việc tràn sẽ xảy ra sau 292 triệu năm.

Chỉ có thể có một khóa chính, nhưng nó có thể bao gồm nhiều cột, điều này được thực hiện bằng cú pháp khác với các ràng buộc trực tiếp trong mô tả bảng.

CREATE TABLE table_name (
  column_name type [ restrictions ]
  [, column_name type [ restrictions ] ...]
  , PRIMARY KEY ( column_name [, column_name ...] ) );

Quay lại với các ràng buộc.

... AUTOINCREMENT

Ràng buộc này chỉ có thể được chỉ định như một bổ sung cho PRIMARY KEY, đảm bảo rằng các định danh luôn tăng dần. Điều này có nghĩa là bất kỳ ID nào trước đó, ngay cả những ID đã sử dụng trên các mục bị xóa, sẽ không được chọn lại. Tuy nhiên, cơ chế này được triển khai trong SQLite kém hiệu quả hơn so với PRIMARY KEY đơn giản về tài nguyên tính toán và do đó không được khuyến nghị sử dụng.

... NOT NULL

Ràng buộc này cấm thêm một bản ghi vào bảng mà trường này không được điền. Theo mặc định, khi không có ràng buộc, bất kỳ trường không duy nhất nào cũng có thể bị bỏ qua trong bản ghi được thêm và sẽ được đặt thành NULL.

... CURRENT_TIME
... CURRENT_DATE
... CURRENT_TIMESTAMP

Các hướng dẫn này cho phép tự động điền vào một trường với thời gian (không có ngày), ngày (không có thời gian), hoặc thời gian UTC đầy đủ tại thời điểm bản ghi được chèn (với điều kiện câu lệnh SQL INSERT không ghi rõ bất cứ điều gì vào trường này, ngay cả NULL). SQLite không biết cách tự động phát hiện thời gian thay đổi của một bản ghi theo cách tương tự — để làm điều này, bạn sẽ phải viết một trình kích hoạt (nằm ngoài phạm vi của cuốn sách).

Thật không may, các ràng buộc nhóm CURRENT_TIMESTAMP được triển khai trong SQLite với một thiếu sót: dấu thời gian không được áp dụng nếu trường là NULL. Điều này phân biệt SQLite với các động cơ SQL khác và với cách SQLite xử lý NULL trong các trường khóa chính. Hóa ra, để gắn nhãn tự động, bạn không thể ghi toàn bộ đối tượng vào cơ sở dữ liệu, mà cần chỉ định rõ ràng tất cả các trường ngoại trừ trường có ngày và giờ. Để giải quyết vấn đề, chúng ta cần một tùy chọn thay thế trong đó hàm SQL STRFTIME('%s') được thay thế trong truy vấn đã biên dịch cho các cột tương ứng.