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

Chia sẻ: Nhung Nhung | Ngày: | Loại File: PDF | Số trang:16

Thêm vào BST

Báo xấu

61
lượt xem 6
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Quản lý thời gian trong DB2 với tính nhất quán thời gian

Quản lý thời gian trong DB2 với tính nhất quán thời gian 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. Về đầu trang 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ụ 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)
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
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 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. Về đầu trang 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®. Về đầu trang 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. o 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 o 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ẹ.
o 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ẹ. o 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)=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ẹ. Về đầu trang 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ê 5 và Liệ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 ) )
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
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) 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) 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@ Về đầu trang 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.