Quản lý thời gian trong DB2 với tính nhất quán thời gian

Thực thi tính toàn vẹn tham chiếu dựa trên thời gian

Các tính năng về thời gian mới trong IBM® DB2® 10 cung cấp các khả năng phong phú về quản lý dữ liệu dựa trên thời gian. Ví dụ, có thể gán một phạm vi ngày tháng cho mỗi hàng dữ liệu để biểu thị khi một hàng được các ứng dụng hay việc kinh doanh của bạn cho là có hiệu lực. Các bảng đã cho phép theo dõi tính hiệu lực này được gọi là các bảng thời gian theo thời gian-ứng dụng và các khoảng thời gian kinh doanh của chúng có thể ở trong quá khứ, hiện tại hay tương lai. Với một bảng cha mẹ và một bảng con có một mối quan hệ khóa ngoài, khái niệm về tính toàn vẹn tham chiếu có thể được mở rộng để xem xét không chỉ giá trị khóa truyền thống mà còn là khoảng thời gian kinh doanh của bất kỳ hàng cụ thể nào. Bài này giải thích tính toàn vẹn tham chiếu thời gian này và cách có thể thực thi tính nhất quán dựa trên thời gian trong DB2. Nội dung của bài này áp dụng với DB2 cho z/OS® và DB2 cho Linux®, UNIX® và Windows®; tất cả đều bao gồm các mẫu ví dụ SQL đã được thử nghiệm trong DB2 10.1 cho LUW.

Matthias Nicola, Chuyên gia về hiệu năng CSDL, IBM Silicon Valley Laboratory

Matthias Nicola người lãnh đạo kỹ thuật về hiệu năng cơ sở dữ liệu XML tại Silicon Valley Lab của IBM. Công việc của ông tập trung vào tất cả các lĩnh vực về hiệu năng XML trong DB2, bao gồm XQuery, SQL/XML và tất cả các tính năng XML nguyên sơ trong DB2. Matthias Nicola cộng tác chặt chẽ với các đội phát triển XML DB2 cũng như với các khách hàng và các đối tác kinh doanh, là những người đang sử dụng XML, hỗ trợ họ trong việc thiết kế, triển khai thực hiện và tối ưu hóa các giải pháp XML. Trước khi gia nhập IBM, Matthias làm việc với hiệu năng kho dữ liệu cho Informix Software. Ông cũng đã có bốn năm làm việc trong các dự án nghiên cứu và các dự án công nghiệp trên cơ sở dữ liệu phân tán và nhân bản. Ông nhận bằng tiến sĩ khoa học máy tính vào năm 1999 ở Đại học Kỹ thuật Aachen, Đức.


Cấp độ đóng góp cho developerWorks của
        tác giả

Martin Sommerlandt, Chuyên gia DB2, IBM

Author Photo: Martin SommerlandtMartin Sommerlandt là nhà phát triển ứng dụng DB2 có chứng chỉ IBM và nhà quản trị cơ sở dữ liệu DB2 có chứng chỉ IBM. Trong thời gian làm việc tại IBM, ông là một kỹ sư hiệu năng tại Phòng thí nghiệm Thung lũng Silicon của IBM, chịu trách nhiệm phân tích và thử nghiệm hiệu năng của các tính năng DB2 mới, gồm các bảng thời gian.



15 11 2012

Giới thiệu về quản lý dữ liệu thời gian với DB2

DB2 10 cho z/OS và DB2 10 cho Linux, UNIX và Windows đưa vào việc quản lý dữ liệu dựa trên thời gian để cho phép bạn truy vấn và thao tác dữ liệu trong quá khứ, hiện tại và tương lai trong khi vẫn duy trì toàn bộ lịch sử về mọi thay đổi dữ liệu. Bộ sưu tập các tính năng về thời gian trong DB2 còn được gọi là Time Travel Query (Truy vấn theo hành trình thời gian).

DB2 hỗ trợ ba kiểu bảng thời gian:

  • Các bảng thời gian theo thời gian-hệ thống— Ở đây DB2 duy trì trong suốt một lịch sử về các hàng cũ đã được cập nhật hoặc bị xóa theo thời gian. Với các cấu kiện mới theo tiêu chuẩn ngôn ngữ SQL, người dùng có thể "quay lại đúng lúc" và truy vấn cơ sở dữ liệu tại bất kỳ thời điểm nào được chọn trong quá khứ. Điều này được dựa trên các các dấu thời gian hệ thống đã gán nội bộ mà DB2 sử dụng chúng để quản lý thời gian hệ thống, còn được gọi là thời gian giao dịch.
  • Các bảng thời gian theo thời gian-ứng dụng— Ở đây các ứng dụng cung cấp các ngày tháng hoặc các dấu thời gian để mô tả tính hiệu lực của dữ liệu kinh doanh. Các cấu kiện SQL mới cho phép các ứng dụng chèn, truy vấn, cập nhật và xóa dữ liệu trong quá khứ, hiện tại hay tương lai. DB2 tự động áp dụng các ràng buộc và các chia tách hàng để duy trì chính xác thời gian kinh doanh, còn được gọi là thời gian có hiệu lực.
  • Các bảng hai thời gian (bitemporal)— Quản lý cả thời gian hệ thống lẫn thời gian kinh doanh. Các bảng hai thời gian kết hợp tất cả các khả năng của các bảng thời gian theo thời gian-hệ thống và theo thời gian-ứng dụng. Cách kết hợp này cho phép các ứng dụng quản lý tính hiệu lực của dữ liệu kinh doanh trong khi DB2 vẫn duy trì một lịch sử đầy đủ về bất kỳ các lần cập nhật hay xóa nào.

Với phần còn lại của bài này, chúng tôi giả sử bạn đã quen với những điều cơ bản về các bảng thời gian theo thời gian-ứng dụng trong DB2. Việc thảo luận về tính toàn vẹn tham chiếu (RI) thời gian cũng áp dụng cho thời gian kinh doanh trong các bảng hai thời gian. Bạn nên biết cách tạo và sử dụng các bảng như vậy và hiểu cách DB2 có thể thực hiện các chia tách hàng khi bạn cập nhật hoặc xóa dữ liệu với phần thời gian kinh doanh đã quy định. Bài "Một vấn đề về Thời gian: Quản lý dữ liệu thời gian trong DB2" cung cấp một bài giới thiệu về các chủ đề này.


Tính toàn vẹn tham chiếu thời gian là gì?

Một ví dụ

Trong phần này, chúng tôi xem lại tính toàn vẹn tham chiếu truyền thống trong một cơ sở dữ liệu quan hệ rồi mở rộng thảo luận về tính toàn vẹn tham chiếu thời gian. Kịch bản của chúng tôi gồm một doanh nghiệp hư cấu nhận các sản phẩm từ các nhà cung cấp, bán các sản phẩm này cho khách hàng và đôi khi thực hiện các chương trình khuyến mãi nhằm cung cấp các sản phẩm được chọn với các mức giảm giá trong các khoảng thời gian hạn chế. Để giúp bạn hiểu cách bạn có thể thực hiện và thực thi tính toàn vẹn tham chiếu thời gian, chúng tôi sử dụng kịch bản ứng dụng và dữ liệu mẫu ví dụ này.

Tính toàn vẹn tham chiếu truyền thống

Liệt kê 1 cho thấy hai bảng liên quan đến việc quản lý kinh doanh này. Bảng cha mẹ, product_avail (sản phẩm có sẵn), gồm có một hàng cho mỗi sản phẩm có sẵn và xác định nhà cung cấp đang cung cấp sản phẩm này. Bảng con, promotion (khuyến mãi), có một hàng cho từng đề xuất riêng, được định nghĩa bằng một ID khuyến mãi, ID sản phẩm tương ứng và giá đã giảm. Cả hai bảng đều có thể có thêm các cột, nhưng để đơn giản, lúc này chỉ cần vài cột như vậy là đủ.

Liệt kê 1. Ví dụ đơn giản về tính toàn vẹn tham chiếu giữa bảng cha mẹ và bảng con
CREATE TABLE product_avail(
    prodID     INTEGER NOT NULL, 
    supplier   VARCHAR(32),
  PRIMARY KEY(prodID) );

CREATE TABLE promotion(
    promoID    INTEGER NOT NULL, 
    prodID     INTEGER NOT NULL, 
    price      DECIMAL(10,2), 
  PRIMARY KEY(promoID),
  FOREIGN KEY (prodID) REFERENCES product_avail(prodID) );

Do chỉ có thể đưa ra một chương trình khuyến mãi cho một sản phẩm có sẵn, nên mỗi hàng trong bảng khuyến mãi phải có một prodID (mã định danh sản phẩm) tồn tại trong bảng product_avail. Điều kiện này được gọi là tính toàn vẹn tham chiếu và được khai báo và được thực thi bởi ràng buộc khóa ngoài (FK) trên bảng khuyến mãi. Do ràng buộc này, bất kỳ nỗ lực nào để chèn hoặc cập nhật một chương trình khuyến mãi với một prodID không tồn tại trong bảng product_avail sẽ bị DB2 loại bỏ.

Giới thiệu về thời gian

Trong doanh nghiệp hư cấu của chúng tôi, các nhà cung cấp khác nhau có thể cung cấp các sản phẩm tương tự tại các thời điểm khác nhau trong năm. Tương tự như vậy, chúng tôi có thể chọn để chạy các chương trình khuyến mãi khác nhau cho cùng một sản phẩm tại các thời điểm khác nhau. Do thời gian là một chiều quan trọng trong kinh doanh, nên chúng tôi tạo ra các bảng trong Liệt kê 1 làm các bảng thời gian theo thời gian-ứng dụng, có nghĩa là chúng tôi đưa một khoảng thời gian BUSINESS_TIME (Thời gian kinh doanh) vào trong cả hai bảng (xem Liệt kê 2).

Khoảng thời gian BUSINESS_TIME đã bổ sung gồm một cặp các cột ngày tháng hoặc dấu thời gian cộng với khai báo PERIOD (Khoảng thời gian) để định nghĩa cặp cột này như là một khoảng thời gian. Chúng tôi cũng có thể chọn mở rộng định nghĩa khóa chính với các từ khóa tùy chọn BUSINESS_TIME WITHOUT OVERLAPS (Thời gian kinh doanh không chồng lên nhau) để cho biết rằng nhiều hàng có thể có cùng giá trị prodID miễn là các khoảng thời gian BUSINESS_TIME của những hàng này không chồng lên nhau. Hãy áp dụng sự mở rộng khóa ngoài cho bảng khuyến mãi.

Từ cột prodID trong bảng product_avail bây giờ có thể có các giá trị sao chép, nó có thể không còn được tham chiếu như là một khóa ngoài trong bảng khuyến mãi nữa. Chúng tôi sẽ cần thực thi tính toàn vẹn tham chiếu theo cách khác.

Liệt kê 2. Bây giờ các bảng trong Liệt kê 1 là các bảng thời gian theo thời gian-ứng dụng
CREATE TABLE product_avail(
    prodID       INTEGER NOT NULL,
    supplier     VARCHAR(32),
    avail_start DATE NOT NULL,
    avail_end DATE NOT NULL,
    PERIOD BUSINESS_TIME (avail_start, avail_end),
    PRIMARY KEY(prodID, BUSINESS_TIME WITHOUT OVERLAPS)  
);
                
CREATE TABLE promotion(
    promoID    INTEGER NOT NULL,
    prodID     INTEGER NOT NULL,
    price      DECIMAL(10,2),
    promo_start DATE NOT NULL,
    promo_end DATE NOT NULL,
    PERIOD BUSINESS_TIME (promo_start, promo_end),
    PRIMARY KEY(promoID, BUSINESS_TIME WITHOUT OVERLAPS)  
);

Hình 1 cho thấy các bảng được điền với thông tin về ba sản phẩm và hai chương trình khuyến mãi. Ví dụ, sản phẩm 9105 do nhà cung cấp là công ty A phân phối bắt đầu 01.01.2012 và kết thúc vào 01.06.2012, có nghĩa là ngày cuối cùng giao nhận sản phẩm là 31.05.2012. (Lưu ý rằng các khoảng thời gian luôn luôn được quy định theo cách bao hàm-loại trừ; các ngày tháng kết thúc đã quy định không còn là một phần của thời hạn hiệu lực nữa). Từ 01.06 trở đi, sản phẩm 9015 do công ty B cung cấp, kết thúc vào 01.09. Trong Tháng Chín và Tháng Mười, chúng tôi không nhận được nguồn cung cấp nào về sản phẩm 9015, nhưng trong Tháng Mười Một và Tháng Mười Hai, sản phẩm này lại có sẵn từ công ty A. Khi chúng tôi không có nhà cung cấp nào về một sản phẩm, thì sản phẩm đó được coi là tạm thời không có.

Hình 1. Dữ liệu mẫu ví dụ
Hình ảnh hiển thị hai bảng với dữ liệu mẫu ví dụ

Khóa chính (prodID, BUSINESS_TIME WITHOUT OVERLAPS) bảo đảm rằng không có hai nhà cung cấp cùng phân phối sản phẩm giống nhau tại cùng một thời điểm. Nếu chúng ta muốn cho phép nhiều nhà cung cấp phân phối sản phẩm giống nhau tại cùng một thời điểm, chúng tôi có thể định nghĩa khóa chính là (prodID, supplier, BUSINESS_TIME WITHOUT OVERLAPS).

Bảng khuyến mãi trong Hình 1 định nghĩa hai chương trình khuyến mãi cho sản phẩm 9105. Chương trình khuyến mãi đầu tiên đưa ra sản phẩm có giá $ 19,95 từ 15.01.2012, kết thúc vào 15.03.2012. Đó là, ngày cuối cùng được giảm giá là 14.03.2012. Chương trình khuyến mãi thứ hai bắt đầu vào 01.05 và kết thúc vào 01.07.2012, với một mức giá $ 16,95. Tại tất cả các thời điểm khác, giá thường lệ cho sản phẩm 9105 vẫn có hiệu lực. Giá đó được lưu trữ ở chỗ khác.

Tính toàn vẹn tham chiếu thời gian

Các dữ liệu trong Hình 1 thực hiện một mối quan hệ khóa ngoài truyền thống giữa hai bảng vì mỗi chương trình khuyến mãi đều tham chiếu một ID sản phẩm đã tồn tại trong bảng product_avail. Tuy nhiên, doanh nghiệp của chúng tôi cũng phải bảo đảm rằng một chương trình khuyến mãi được cung cấp chỉ trong một khoảng thời gian khi các sản phẩm tương ứng thực sự có sẵn. Hãy tưởng tượng sự lộn xộn và sự bất mãn trong số các khách hàng nếu chúng tôi đã quảng cáo giảm giá cho một sản phẩm mà không có sẵn sản phẩm đó trong bất kỳ kho hàng nào của mình.

Nói chung, bạn có thể muốn thực thi một mối quan hệ theo thời gian giữa các khoảng thời gian kinh doanh của các hàng cha mẹ và các hàng con trong hai bảng liên quan. Các ví dụ về các mối quan hệ này gồm những điều sau đây:

  • Bằng nhau về khoảng thời gian— Khoảng thời gian kinh doanh của hàng con phải trùng với khoảng thời gian của một hàng cha mẹ duy nhất. Trong ví dụ của chúng tôi, điều kiện này sẽ làm cho khoảng thời gian của chương trình khuyến mãi luôn chồng lên khoảng thời gian trong đó sản phẩm có sẵn từ một nhà cung cấp cụ thể. Một khóa ngoài (FK) truyền thống dựa trên ID sản phẩm và các cột bắt đầu và kết thúc thời gian có thể tuân thủ mối quan hệ này.
  • Chính sách ngăn chặn thời gian— Khoảng thời gian kinh doanh của hàng con phải được chứa hoàn toàn trong các khoảng thời gian của một hoặc nhiều hàng cha mẹ.
  • Chính sách ngăn chặn thời gian, hàng cha mẹ duy nhất— Khoảng thời gian kinh doanh của hàng con phải được chứa trong khoảng thời gian của một hàng cha mẹ duy nhất.
  • Chồng lên— Khoảng thời gian kinh doanh của hàng con phải chồng lên khoảng thời gian của một hàng cha mẹ.
  • Bắt đầu-trong phạm vi— Khoảng thời gian kinh doanh của hàng con phải bắt đầu trong phạm vi của khoảng thời gian của một hàng cha mẹ.
  • Bắt đầu-sau— Khoảng thời gian kinh doanh của hàng con phải bắt đầu sau khoảng thời gian của một hàng cha mẹ.

Hình 2 hiển thị trực quan các khoảng thời gian kinh doanh của dữ liệu mẫu ví dụ trong Hình 1 và cho phép chúng tôi kiểm tra một số các mối quan hệ theo thời gian được liệt kê ở trên. Mỗi hàng của một bảng được biểu diễn bởi một hình chữ nhật có chiều dài đại diện cho độ rộng thời gian của khoảng thời gian kinh doanh của hàng đó. Các hàng của bảng product_avail được hiển thị bằng màu xanh dương, các hàng của bảng khuyến mãi có màu xanh lá cây.

Chúng tôi có thể đưa ra các nhận xét sau trong Hình 2:

  • Các chương trình khuyến mãi có promoID (mã định danh của chương trình khuyến mãi) 16 và 17 không thực hiện điều kiện bằng nhau về khoảng thời gian với các hàng cha mẹ của chúng vì các khoảng thời gian khuyến mãi ngắn hơn các khoảng thời gian của các hàng cha mẹ của chúng trong bảng product_avail.
  • Chương trình khuyến mãi 16 thực hiện điều kiện chính sách ngăn chặn thời gian — và thậm chí là chính sách ngăn chặn thời gian với một hàng cha mẹ duy nhất — vì khoảng thời gian đề xuất của nó được chứa trong một khoảng thời gian mà sản phẩm 9105 do nhà cung cấp là công ty A cung cấp.
  • Chương trình khuyến mãi 17 thỏa mãn chính sách ngăn chặn thời gian (với nhiều hàng cha mẹ) vì khoảng thời gian đề xuất của nó có chứa các Tháng Năm và Sáu, lúc đó sản phẩm được công ty A và B cung cấp, không có sự gián đoạn nào trong việc cung cấp. Chương trình khuyến mãi 17 không thỏa mãn chính sách ngăn chặn thời gian với một hàng cha mẹ.
  • Cả hai chương trình khuyến mãi 16 và 17 đều thực hiện các mối quan hệ chồng lên và bắt đầu-trong phạm vi với các hàng sản phẩm đầu tiên.
Hình 2. Hình 2. Bảng liệt kê thời gian của các khoảng thời gian của bảng product_avail (ở trên, màu xanh dương) và các khoảng thời gian của bảng khuyến mãi (ở dưới, màu xanh lá cây)
Hình ảnh hiển thị bảng liệt kê thời gian của hai khoảng thời gian

Căn cứ vào trường hợp sử dụng riêng của mình và các yêu cầu ứng dụng, bạn cần xác định mối quan hệ thời gian nào cần thực thi giữa các hàng cha mẹ và các hàng con.

Với ví dụ về sản phẩm và chương trình khuyến mãi của mình, chúng tôi chọn chính sách ngăn chặn thời gian (có một hoặc nhiều hàng cha mẹ) làm mối quan hệ mong muốn để định nghĩa tính toàn vẹn tham chiếu thời gian. Do đó, phần còn lại của bài này giả định chính sách ngăn chặn thời gian là khái niệm cho tính toàn vẹn tham chiếu thời gian.

Các vi phạm tính toàn vẹn tham chiếu thời gian

Bộ phận tiếp thị của chúng tôi dự kiến sẽ giảm doanh thu trong Tháng Tám và Tháng Chín và quyết định đưa sản phẩm 9105 ra bán chỉ với giá là 15,95 $ trong khoảng thời gian đó. Câu lệnh INSERT sau đây được ban hành cho chương trình khuyến mãi 18:

INSERT INTO promotion (promoID, prodID, price, promo_start, promo_end)
VALUES (18, 9105, '2012-08-01', '2012-10-01');

Chương trình khuyến mãi mới này không vi phạm tính toàn vẹn tham chiếu truyền thống bởi vì khóa ngoài 9105 vẫn tồn tại trong bảng product_avail. Tuy nhiên, bạn có thể thấy trong Hình 3 là chương trình khuyến mãi 18 vi phạm khái niệm đã chọn của chúng tôi về tính toàn vẹn tham chiếu thời gian, có nghĩa là, chính sách ngăn chặn thời gian. Tất cả chương trình khuyến mãi mới này diễn ra suốt Tháng Chín, nhưng chúng tôi không có nhà cung cấp sản phẩm 9105 nào trong Tháng Chín (và chúng tôi không còn sản phẩm nào trong kho).

Nếu chúng tôi đã chọn điều kiện chồng lên hoặc bắt đầu-trong phạm vi để định nghĩa tính toàn vẹn tham chiếu thời gian, thì chương trình khuyến mãi 18 sẽ không bị vi phạm.

Hình 3. Hình 3. Chương trình khuyến mãi 18 vi phạm tính toàn vẹn tham chiếu thời gian
Hình ảnh hiển thị một bảng liệt kê thời gian của hai khoảng thời gian

Chúng tôi có thể có khả năng nhận ra và tránh được sự vi phạm tính toàn vẹn tham chiếu thời gian này bằng cách phát hiện ra là ngày cuối cùng của chương trình khuyến mãi 18, 01.10.2012, nằm ngoài bất kỳ khoảng thời gian kinh doanh hiện có với sản phẩm 9105 trong bảng product_avail. Để khắc phục tình hình này, cần sửa đổi hoặc xóa chương trình khuyến mãi 18 khỏi bảng khuyến mãi.

Bây giờ hãy xem xét một chương trình khuyến mãi khác được nhập bằng câu lệnh INSERT sau cho chương trình khuyến mãi 19:

INSERT INTO promotion (promoID, prodID, price, promo_start, promo_end)
VALUES (19, 9105, '2012-08-01', '2012-12-01');

Chương trình khuyến mãi này bắt đầu từ 01.08.2012 và kết thúc vào 01.12.2012. Mặc dù ngày tháng bắt đầu và kết thúc của chương trình khuyến mãi này nằm trong các khoảng thời gian có sẵn sản phẩm hiện có, tính toàn vẹn tham chiếu thời gian (chính sách ngăn chặn thời gian) bị vi phạm do có một sự gián đoạn nhà cung cấp giữa hai tháng này. Như được minh họa trong Hình 4, chương trình khuyến mãi 19 có hiệu lực trong Tháng Tám, Tháng Chín, Tháng Mười và Tháng Mười Một, nhưng chúng tôi không có sự cung cấp sản phẩm nào trong Tháng Chín và Tháng Mười.

Hình 4. Chương trình khuyến mãi 18 vi phạm tính toàn vẹn tham chiếu thời gian
Hình ảnh hiển thị một bảng liệt kê thời gian của hai khoảng thời gian

Ví dụ về chương trình khuyến mãi 19 minh họa một thực tế quan trọng: Nếu các khoảng thời gian của hàng cha mẹ với một giá trị khóa cụ thể có thể có các sự gián đoạn thời gian, thì không thể phát hiện ra các vi phạm chính sách ngăn chặn chỉ bằng cách kiểm tra ngày tháng bắt đầu và ngày tháng kết thúc của một khoảng thời gian của hàng con có chứa trong các khoảng thời gian cha mẹ hiện có. Tuy nhiên, nếu bạn biết chắc rằng không có sự gián đoạn thời gian nào giữa các hàng cha mẹ có giá trị khóa giống nhau, thì chính sách ngăn chặn thời gian dễ thực thi hơn.

Chương trình khuyến mãi 17 minh họa một nhận xét quan trọng khác: để thực thi chính sách ngăn chặn thời gian với một hoặc nhiều hàng cha mẹ thì để kiểm tra xem cả hai ngày tháng bắt đầu và ngày tháng kết thúc của một khoảng thời gian của hàng con có được chứa trong cùng một khoảng thời gian của hàng cha mẹ không thường chưa đủ. Một bước kiểm tra như vậy sẽ thực thi chính sách ngăn chặn thời gian trong một hàng cha mẹ duy nhất và sẽ loại bỏ chương trình khuyến mãi 17.


Các tùy chọn để thực thi tính toàn vẹn tham chiếu thời gian

Thường có bốn lựa chọn để xử lý toàn vẹn tham chiếu thời gian:

  1. Không thực thi. Không định nghĩa bất kỳ ràng buộc nào khi bạn biết tính toàn vẹn tham chiếu thời gian không liên quan với ứng dụng của bạn hoặc không bao giờ bị vi phạm.
  2. Thực thi trong ứng dụng. Một sự lựa chọn tốt nếu các ứng dụng của bạn đã thực thi tính toàn vẹn tham chiếu thời gian. Nếu không, nó có thể dẫn đến làm tăng thêm tính phức tạp ứng dụng và đặt một gánh nặng lên các nhà phát triển ứng dụng.
  3. Thực thi bằng cách sử dụng các trigger. Tạo các trigger cho các hoạt động chèn, cập nhật và xóa để thực thi tính toàn vẹn tham chiếu thời gian.
  4. Thực thi bằng cách sử dụng các thủ tục đã lưu. Tạo các thủ tục đã lưu để thực thi tính toàn vẹn tham chiếu thời gian cho nhiều hàng trong hàng loạt các hoạt động.

Trong phần còn lại của bài này, chúng tôi sẽ cung cấp một số ví dụ cho các tùy chọn 3 và 4, các trigger và các thủ tục đã lưu, để thực thi chính sách ngăn chặn thời gian giữa một hàng con và một hoặc nhiều hàng cha mẹ. Các mẫu mã ví dụ đã được thử nghiệm trên DB2 10 cho Linux, UNIX và Windows®, nhưng các khái niệm chung tương tự cũng áp dụng với DB2 cho z/OS®.


Thực thi tính toàn vẹn tham chiếu thời gian bằng các trigger

Để thực thi tính toàn vẹn tham chiếu thời gian, bạn có thể tạo các trigger cho một số hoặc tất cả các sự kiện sau:

  • Chèn các hàng vào bảng con.
    • Tính toàn vẹn tham chiếu thời gian có thể bị vi phạm nếu khoảng thời gian của một chương trình khuyến mãi sản phẩm mới không có trong một khoảng thời gian kinh doanh của sản phẩm đó. Một trigger có thể phát hiện ra tình trạng này và loại bỏ việc chèn thêm chương trình khuyến mãi.
  • Cập nhật các hàng trong bảng con
    • Tính toàn vẹn tham chiếu thời gian có thể bị vi phạm nếu khóa chính hoặc khoảng thời gian kinh doanh của một hàng con bị thay đổi. Trigger có thể loại bỏ việc cập nhật nếu cần.
  • Xóa các hàng khỏi bảng cha mẹ.
    • Tính toàn vẹn tham chiếu thời gian có thể bị vi phạm nếu một sản phẩm bị xóa trong khi chương trình khuyến mãi liên quan vẫn còn tồn tại. Trong trường hợp này, trigger có thể thực hiện một quy tắc xóa theo sự lựa chọn của bạn, chẳng hạn như:
      • Loại bỏ và khôi phục lại hoạt động xóa.
      • Xóa hết dữ liệu xếp chồng và loại bỏ các hàng liên quan trong bảng con.
      • Sửa đổi các hàng con theo một số cách khác do ứng dụng định nghĩa.
  • Cập nhật các hàng trong bảng cha mẹ.
    • Tính toàn vẹn tham chiếu thời gian có thể bị vi phạm nếu khóa chính hoặc khoảng thời gian kinh doanh của một hàng cha mẹ bị thay đổi. Trong trường hợp này, trigger hoặc có thể loại bỏ việc cập nhật hoặc nó có thể xóa hoặc sửa đổi các hàng liên quan trong bảng con.

Các phần sau sẽ cung cấp các trigger mẫu ví dụ cho các hoạt động chèn và cập nhật các hàng trong bảng con.

Các trigger tính toàn vẹn tham chiếu thời gian cơ bản (không có các gián đoạn thời gian nào)

Trong phần này chúng tôi giả định rằng các khoảng thời gian cho bất kỳ sản phẩm đã biết nào trong bảng cha mẹ (product_avail) không bao giờ có các gián đoạn thời gian. Giả định này hàm ý rằng sự vi phạm tính toàn vẹn tham chiếu thời gian của chương trình khuyến mãi 19 trong Hình 4 không thể xảy ra được và do đó trigger không cần kiểm tra trường hợp riêng biệt này. Điều này cho phép thực hiện trigger đơn giản. Sau đó chúng tôi thảo luận cách có thể mở rộng trigger này để xử lý các gián đoạn thời gian.

Liệt kê 3 hiển thị một trigger được thực hiện bất cứ khi nào một hàng mới được chèn vào trong bảng promotion (khuyến mãi). Trigger này kiểm tra tính toàn vẹn tham chiếu thời gian cho hàng mới, tức là nó kiểm tra xem khoảng thời gian của hàng khuyến mại mới có được chứa trong (các) khoảng thời gian của một hoặc nhiều hàng cha mẹ có ID sản phẩm giống nhau không.

Trigger thực hiện hai bước kiểm tra. Đầu tiên, nó kiểm tra xem ngày tháng bắt đầu của chương trình khuyến mãi đã chèn vào có chứa trong thời gian kinh doanh của một hàng trong bảng product_avail có một giá trị prodID trùng khớp không. Sau đó, nó sẽ kiểm tra một điều kiện tương tự cho ngày tháng cuối cùng của chương trình khuyến mãi đã chèn vào. Nếu một trong hai bước kiểm tra này không đúng (ví dụ, nếu tổng số đếm được của các hàng cha mẹ trùng khớp bằng không), thì tính toàn vẹn tham chiếu thời gian bị vi phạm và một lỗi được đưa ra. Nếu cả hai ngày tháng bắt đầu và ngày tháng kết thúc đều được chứa trong các khoảng thời gian của các hàng cha mẹ tương ứng, thì tính toàn vẹn tham chiếu thời gian được duy trì và hoạt động chèn hoàn thành. Nếu không, trigger loại bỏ hoạt động chèn này, báo hiệu một trạng thái SQL tùy chỉnh và đưa ra một thông báo rằng tính toàn vẹn tham chiếu thời gian bị vi phạm.

Liệt kê 3. Trigger để thực thi tính toàn vẹn tham chiếu thời gian lúc chèn một hàng con
CREATE TRIGGER promotion_insert_RI
BEFORE INSERT ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    IF (
        -- Is promotion.promo_start part of an existing product_avail period?
        (SELECT COUNT(*) count
        FROM product_avail p
        WHERE p.ProdID = new.ProdID
        AND p.avail_start <= new.promo_start 
        AND p.avail_end > new.promo_start)=0
    OR
        -- Is promotion.promo_end part of an existing product_avail period?
        (SELECT COUNT(*) count
        FROM product_avail p
        WHERE p.ProdID = new.ProdID
        AND p.avail_start < new.promo_end
        AND p.avail_end >= new.promo_end)=0
     ) --  promotion.promo_start AND promotion.promo_end must BOTH be 
        -- part of ANY period
   THEN SIGNAL SQLSTATE 'RI999' 
     SET MESSAGE_TEXT='PROMOTION
             PERIOD IS NOT FULLY CONTAINED IN EXISTING PRODUCT PERIODS!';
    END IF;
END@

Một trigger tương tự cũng cần thiết nếu chúng ta hy vọng có thể cập nhật các cột prodID, promo_start (thời gian bắt đầu khuyến mãi) hay promo_end (thời gian kết thúc khuyến mãi) của một chương trình khuyến mãi. Ví dụ, một lần cập nhật của các giá trị promo_start hay promo_end có thể di chuyển hoặc tăng thêm khoảng thời gian khuyến mãi và có khả năng vi phạm tính toàn vẹn tham chiếu thời gian. Logic của trigger giống như trong Liệt kê 4, trừ trigger bắt đầu hoạt động chỉ khi có bất kỳ cột nào trong ba cột này bị ảnh hưởng bởi một câu lệnh update (cập nhật).

Liệt kê 4. Trigger để thực thi tính toàn vẹn tham chiếu thời gian lúc cập nhật một hàng con
CREATE TRIGGER promotion_update_RI
BEFORE UPDATE OF prodID, promo_start, promo_end ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
        -- same trigger body as in Listing 3
END IF;
END@

Tiếp theo, hãy thảo luận về cách các trigger này có thể được cải tiến để xử lý chính xác với các sự gián đoạn thời gian trong các khoảng thời gian của hàng cha mẹ.


Các trigger về tính toàn vẹn tham chiếu thời gian để kiểm tra các gián đoạn thời gian

Nếu các hàng cha mẹ với một giá trị khóa cụ thể có thể có những gián đoạn giữa các khoảng thời gian kinh doanh của chúng, như trong ví dụ có sẵn sản phẩm của chúng tôi, thì việc kiểm tra tính toàn vẹn tham chiếu thời gian trong trigger cần phải công phu hơn. Trigger này cần được cải tiến để phát hiện ra "các vi phạm gián đoạn thời gian" chẳng hạn như chương trình khuyến mãi 19 trong Hình 4. Liệt kê 5Liệt kê 6 hiển thị hai cách thay thế để mã hóa một trigger dùng để kiểm tra chính sách ngăn chặn thời gian với việc kiểm tra sự gián đoạn thời gian cần thiết.

Trigger trong Liệt kê 5 chọn các hàng cha mẹ từ bảng product_avail có các khoảng thời gian chồng lên khoảng thời gian của hàng mới được chèn vào bảng khuyến mãi. Đối với mỗi hàng cha mẹ, một phép tự nối ngoài-bên trái (left-outer self-join) trên bảng product_avail lấy ra bất kỳ hàng cha mẹ "tiếp theo" nào mà khoảng thời gian của nó bắt đầu ở nơi khoảng thời gian trước đó kết thúc (current.avail_end = next.avail_start) (tức là, không có sự gián đoạn thời gian nào). Các hàng kết quả của phép nối ngoài-bên trái này có một giá trị NULL trong cột next.avail_start biểu diễn một sự gián đoạn thời gian trong các khoảng thời gian của các hàng cha mẹ. Các điều kiện khác trong trigger bảo đảm rằng các ngày tháng bắt đầu và kết thúc của chương trình khuyến mãi mới đều được chứa trong các khoảng thời gian cha mẹ hiện tại.

Nếu không có hàng cha mẹ nào trong bảng product_avail có giá trị prodID khớp với prodID của chương trình khuyến mãi mới được chèn vào, thì phép nối ngoài tạo ra một tập hợp kết quả rỗng. Trong trường hợp này SUM(start_check)+SUM(end_and_gap_check) trong mệnh đề SELECT mức cao nhất tạo ra NULL. Giá trị NULL này làm cho hàm COALESCE trả về -1 và và làm hỏng kiểm tra tính toàn vẹn nếu cần.

Liệt kê 5. Thực thi tính toàn vẹn tham chiếu thời gian lúc chèn một hàng con, khi xem xét các gián đoạn thời gian của hàng cha mẹ
CREATE OR REPLACE TRIGGER promotion_insert_RI_withGaps
BEFORE INSERT ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    IF ( SELECT COALESCE(SUM(start_check)+SUM(end_and_gap_check), -1) check 
        FROM (
        SELECT CASE WHEN new.promo_start
                    <
                    current.avail_start
                AND current.avail_start = 
                        (SELECT MIN(avail_start) 
                        FROM product_avail
                        WHERE prodID = new.prodID 
                        AND avail_start
                    < new.promo_end
                        AND avail_end >
                    new.promo_start)
                THEN -1
                  -- PROMOTION.PROMO_START is out of product_avail range!
                ELSE  0
                  -- PROMOTION.PROMO_START is in range of current or previous 
                  -- product_avail
                    period
            END start_check,
            CASE WHEN current.avail_end
                    <
                    new.promo_end
                THEN CASE WHEN next.avail_start IS NULL
                          THEN -1-- PROMOTION is out of product_avail range, 
                    -- or product_avail contains gaps!
                          ELSE  0-- PROMOTION.PROMO_END is out of current 
                    -- product_avail period, but a connecting
                    -- product_avail period
                    exists
                        END ELSE 0 --
                    PROMOTION.PROMO_END is in a product_avail
                    period
                END end_and_gap_check
            FROM product_avail
                    AS current
            LEFT OUTER JOIN product_avail AS
                    next
                        ON current.avail_end =
                    next.avail_start
                        AND current.prodID =
                    next.prodID
            WHERE current.prodID = new.prodID 
            AND current.avail_start
                    < new.promo_end 
            AND current.avail_end >
                    new.promo_start
        ) )<0 THEN SIGNAL SQLSTATE 'RI999' 
            SET MESSAGE_TEXT='PROMOTION
                    PERIOD VIOLATES TEMPORAL RI!';
    END IF;
END @

Cách tiếp cận tốt nhất để hiểu logic trong trigger này là vẽ các hình ảnh như Hình 4 và duyệt qua các điều kiện và các biểu thức CASE.

Liệt kê 5 chỉ là một trong giải pháp có thể và bạn có thể tìm ra nhiều cách khác theo SQL để mã hóa các điều kiện tương tự. Một lựa chọn thay thế được thể hiện trong Liệt kê 6. Phần thân trigger trong Liệt kê 6 kiểm tra ba điều kiện. Câu lệnh SELECT đầu tiên kiểm tra xem ngày tháng bắt đầu của chương trình khuyến mãi được chèn vào có nằm trong khoảng thời gian kinh doanh của một hàng sản phẩm với giá trị khóa tương tự không. Câu lệnh SELECT thứ hai xác minh xem ngày tháng cuối cùng của chương trình khuyến mại mới này có nằm trong khoảng thời gian của một hàng sản phẩm trùng khớp không. Còn câu lệnh SELECT thứ ba kiểm tra các gián đoạn giữa các khoảng thời gian của các hàng cha mẹ liên quan trong bảng product_avail.

Liệt kê 6. Thực thi tính toàn vẹn tham chiếu thời gian lúc chèn một hàng con, khi xem xét các gián đoạn thời gian cha mẹ
CREATE OR REPLACE TRIGGER promotion_insert_RI_withGaps
BEFORE INSERT ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    IF ( 
      -- verify that a product row exists with prodID = promtion.prodID 
      -- and whose period contains promo_start
        (SELECT COUNT(*)
        FROM product_avail prod
        WHERE new.prodID = prod.prodID
        AND prod.avail_start <= new.promo_start
        AND new.promo_start < prod.avail_end)   < 0 )
    OR ( 
      -- verify that a product row exists with prodID = promotion.prodID 
      -- and whose period contains promo_end
        (SELECT COUNT(*)
        FROM product_avail prod
        WHERE new.prodID = prod.prodID
        AND prod.avail_start < new.promo_end
        AND new.promo_end <= prod.avail_end)   < 0 )
    OR ( 
      -- check for any gaps between the relevant product rows that 
      -- have prodID = promotion.prodID
        (SELECT COUNT(*)
        FROM product_avail prod
        WHERE new.prodID = prod.prodID
        AND new.promo_start < prod.avail_end
        AND prod.avail_end < new.promo_end
        AND NOT EXISTS (
                SELECT *
                FROM product_avail prod2
                WHERE prod2.prodID = prod.prodID
                    AND prod2.avail_start <= prod.avail_end
                    AND prod.avail_end < prod2.avail_end) )   > 0 )
    THEN SIGNAL SQLSTATE 'RI999' 
        SET MESSAGE_TEXT='PROMOTION
                    PERIOD VIOLATES TEMPORAL RI!';
    END IF;
END @

Thực thi tính toàn vẹn tham chiếu thời gian bằng các thủ tục đã lưu

Nếu bạn đang thêm, thay đổi hoặc xóa nhiều hàng trong một bảng, thì việc có thể có hiệu quả hơn là kiểm tra tính toàn vẹn tham chiếu thời gian bằng các hoạt động dựa trên tập hợp trong một thủ tục đã lưu còn hơn là bằng một trigger được kích hoạt một lần cho mỗi hàng bị ảnh hưởng. Ví dụ, khi bạn thực hiện các hoạt động tải hay nhập khẩu, hoặc một loạt các cập nhật hay xóa, bạn có thể muốn sử dụng một thủ tục đã lưu để kiểm tra tính toàn vẹn tham chiếu thời gian.

Ví dụ, thủ tục trong Liệt kê 7 kiểm tra tính toàn vẹn tham chiếu thời gian giữa bảng product_avail và bảng khuyến mãi. Nó giả định rằng các khoảng thời gian cho một sản phẩm cụ thể trong bảng product_avail không chứa các gián đoạn thời gian nào. Logic của tính toàn vẹn tham chiếu thời gian cốt lõi trong thủ tục này là tương tự với logic trong trigger trong Liệt kê 3.

Khi tạo một thủ tục như vậy, bạn có thể chọn cách bạn muốn xử lý tính toàn vẹn tham chiếu thời gian. Việc xử lý vi phạm trong Liệt kê 7 đi theo một cách tiếp cận đơn giản: Nếu có ít nhất một hàng khuyến mãi vi phạm tính toàn vẹn tham chiếu thời gian, thì thủ tục này báo hiệu một trạng thái SQL tùy chỉnh và đưa ra một thông báo là tính toàn vẹn tham chiếu thời gian bị vi phạm.

Liệt kê 7. Thủ tục đã lưu để kiểm tra tính toàn vẹn tham chiếu thời gian riêng rẽ
CREATE PROCEDURE check_temporal_integrity()
BEGIN
    IF (SELECT COUNT(*) FROM (
        SELECT *  
                -- This SELECT obtains all promotion rows that violate temporal RI 
        FROM (
            SELECT prodID, promo_start, promo_end,
                -- check whether a product row contains promotion.promo_start
                (SELECT COUNT(*) FROM product_avail p
                  WHERE p.ProdID = promotion.ProdID
                    AND p.avail_start <= promotion.promo_start
                    AND p.avail_end > promotion.promo_start) c1,
                -- check whether a product_avail row contains promotion.promo_end
                (SELECT COUNT(*) FROM product_avail p
                   WHERE p.ProdID = promotion.ProdID
                      AND p.avail_start < promotion.promo_end
                      AND p.avail_end >= promotion.promo_end) c2
            FROM promotion) S
            -- count all promotion rows, for which either the first or the 
                second check fails
        WHERE c1 = 0 OR c2 =
                    0
    ) T) > 0
    THEN SIGNAL SQLSTATE 'RI999' 
        SET MESSAGE_TEXT='AT
                    LEAST ONE PROMOTION VIOLATES TEMPORAL
                    RI!';
    ELSE SIGNAL SQLSTATE 'RI000' 
        SET MESSAGE_TEXT='TEMPORAL
                    RI HAS BEEN SUCCESSFULLY VERIFIED!';
    END IF;
END@

Đáng lẽ chỉ cần đếm các hàng vi phạm, bạn cũng có thể trả về các giá trị khóa chính của chúng, chèn chúng vào một bảng ngoại lệ hoặc thực hiện bất kỳ hành động tùy chỉnh nào đáp ứng các yêu cầu ứng dụng của bạn. Ví dụ, thủ tục trong Liệt kê 8 sao chép các hàng khuyến mại vi phạm vào một bảng riêng biệt và xóa chúng khỏi bảng khuyến mãi.

Liệt kê 8. Thủ tục đã lưu trữ để sao chép và xóa các hàng vi phạm tính toàn vẹn tham chiếu thời gian
CREATE PROCEDURE check_temporal_integrity()
BEGIN
    INSERT INTO promo_exception
       SELECT promoID, prodID, price, promo_start, promo_end
       FROM (
            SELECT promoID, prodID, price, promo_start, promo_end,
                --
                    check whether a product row contains
                    promotion.promo_start
                (SELECT COUNT(*) FROM product_avail p
                  WHERE p.ProdID = promotion.ProdID
                    AND p.avail_start <= promotion.promo_start
                    AND p.avail_end > promotion.promo_start) c1,
                --
                    check whether a product_avail row contains
                    promotion.promo_end
                (SELECT COUNT(*) FROM product_avail p
                  WHERE p.ProdID = promotion.ProdID
                    AND p.avail_start < promotion.promo_end
                    AND p.avail_end >= promotion.promo_end) c2
                FROM promotion
            ) S
            -- select all promotion rows, for which either the first 
             -- or the second check fails
            WHERE c1 = 0 OR c2 = 0;
                
    DELETE FROM promotion
    WHERE promoID IN (SELECT promoID FROM promo_exception);
END@

Tóm tắt

Bài này đã giải thích khái niệm về tính toàn vẹn tham chiếu thời gian và mô tả cách có thể sử dụng các trigger và các thủ tục đã lưu để thực thi nó. Tính toàn vẹn tham chiếu thời gian giữa hai bảng thời gian theo thời gian-ứng dụng có hai điều kiện. Đầu tiên, đối với mỗi hàng trong bảng con, có một hoặc nhiều hàng trùng khớp trong bảng cha mẹ có giá trị khóa giống nhau. Thứ hai, khoảng thời gian kinh doanh của bất kỳ hàng con nào phải thỏa mãn một mối quan hệ thời gian với khoảng thời gian kinh doanh của các hàng cha mẹ liên quan. Ví dụ, bạn có thể muốn làm cho các khoảng thời gian con giống hệt với các khoảng thời gian cha mẹ hoặc là chúng được chứa trong một hoặc nhiều khoảng thời gian cha mẹ. Kịch bản ứng dụng khác nhau có thể đòi hỏi thực thi các mối quan hệ thời gian khác nhau.

Thực thi tính toàn vẹn tham chiếu thời gian ở mức ứng dụng rất quan trọng. Nó có thể được thực hiện ở mức cơ sở dữ liệu bằng các trigger hoặc các thủ tục đã lưu. Các mẫu ví dụ trong bài này có thể dùng làm một điểm khởi đầu cho bạn khi viết các trigger hoặc các thủ tục riêng của mình để kiểm tra tính toàn vẹn thời gian.

Tài nguyên

Học tập

Lấy sản phẩm và công nghệ

  • Xây dựng dự án phát triển tiếp theo của bạn với phần mềm dùng thử của IBM, có sẵn để tải về trực tiếp từ developerWorks.
  • Bây giờ bạn có thể sử dụng DB2 miễn phí. Hãy tải DB2 Express-C, một phiên bản miễn phí của Ấn bản DB2 Express (DB2 Express Edition) cho cộng đồng cung cấp các tính năng dữ liệu cốt lõi giống như DB2 Express Edition và cung cấp một cơ sở vững chắc để xây dựng và triển khai các ứng dụng.

Thảo luận

Bình luận

developerWorks: Đăng nhập

Các trường được đánh dấu hoa thị là bắt buộc (*).


Bạn cần một ID của IBM?
Bạn quên định danh?


Bạn quên mật khẩu?
Đổi mật khẩu

Bằng việc nhấn Gửi, bạn đã đồng ý với các điều khoản sử dụng developerWorks Điều khoản sử dụng.

 


Ở lần bạn đăng nhập đầu tiên vào trang developerWorks, một hồ sơ cá nhân của bạn được tạo ra. Thông tin trong bản hồ sơ này (tên bạn, nước/vùng lãnh thổ, và tên cơ quan) sẽ được trưng ra cho mọi người và sẽ đi cùng các nội dung mà bạn đăng, trừ khi bạn chọn việc ẩn tên cơ quan của bạn. Bạn có thể cập nhật tài khoản trên trang IBM bất cứ khi nào.

Thông tin gửi đi được đảm bảo an toàn.

Chọn tên hiển thị của bạn



Lần đầu tiên bạn đăng nhập vào trang developerWorks, một bản trích ngang được tạo ra cho bạn, bạn cần phải chọn một tên để hiển thị. Tên hiển thị của bạn sẽ đi kèm theo các nội dung mà bạn đăng tải trên developerWorks.

Tên hiển thị cần có từ 3 đến 30 ký tự. Tên xuất hiện của bạn phải là duy nhất trên trang Cộng đồng developerWorks và vì lí do an ninh nó không phải là địa chỉ email của bạn.

Các trường được đánh dấu hoa thị là bắt buộc (*).

(Tên hiển thị cần có từ 3 đến 30 ký tự)

Bằng việc nhấn Gửi, bạn đã đồng ý với các điều khoản sử dụng developerWorks Điều khoản sử dụng.

 


Thông tin gửi đi được đảm bảo an toàn.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=70
Zone=Information Management
ArticleID=844724
ArticleTitle=Quản lý thời gian trong DB2 với tính nhất quán thời gian
publish-date=11152012