Trong một số dự án tôi từng trải qua, việc theo dõi lại những hành động đã xảy ra trong cơ sở dữ liệu là một việc làm hết sức quan trọng, giải pháp của nó rất nhiều, khó khăn cũng rất nhiều, hôm nay, tôi giới thiệu một cách tiếp cận khá đơn giản mà cực kỳ hiệu quả, nếu bài viết này có ích với bạn, xin đừng ngần ngại đóng góp ý kiến của bạn dưới bài viết này.
Bạn sẽ theo dõi những thay đổi trong database như thế nào, khi người dùng xóa, sửa dữ liệu. Bạn sẽ có một vài cách tiếp cận sau:
- Tạo ra một cột tên là isDeleted: thoạt nhìn phải công nhận ý tưởng này rất tốt, bất cứ khi nào dữ liệu trên cột bị xóa nó sẽ không xóa bỏ hoàn toàn mà chỉ đánh dấu mà thôi, cách giải quyết này sẽ giải quyết được vấn đề delete, tuy nhiên nó vấp phải vấn đề về ràng buộc dữ liệu. Hãy tưởng tượng tôi có một bảng username tôi sẽ tổ chức như sau:
ID-UserName-Password. và cột isDeleted. Và bạn đã hiểu chuyện gì trong này USERNAME phải là duy nhất trong hệ thống. Nó chỉ được đang ký lại khi một người đã hủy nó đi hoặc chưa tồn tại.
Bây giờ tôi xóa username =xyz, nghĩa là username =xyz là isDeleted, sau đó tôi tiếp tục insert username là xyz.
Lúc này vấn đề tôi đã phải ràng buộc toàn vẹn trên database là nằm trên cột isDeleted, Constraint của tôi phải ràng buộc username và isDeleted là duy nhất, tuyệt, nhưng riêng trong chuyện này thế đã là không ổn, bạn đã phải tính tới chuyện tạo một constrain cho một cột không tham gia vào bussiness của hệ thống, điều này lẽ ra nên tránh.
Mặt khác, chuyện gì sẽ xảy ra nếu tôi insert username=zyz, sau đó xóa, rồi tạo lại, rồi lại xóa.
Vấn đề bây giờ bạn phải luôn kiểm tra trước khi insert dữ liệu, có bao giờ bạn tự hỏi, vậy constraint trong database đã sinh ra để làm gì không??
- Tạo một bản sao database: nếu đã làm qua Oracle bạn đều biết có một loại audit table mà oracle hỗ trợ để quản lý việc insert, delete , update. Không nhất thiết phải Oracle, trong database khác bạn cũng có thể dễ dàng cài đặt chức năng này, đơn giản như sau:
Tạo một Database log y hệt database gốc, mỗi bảng thêm một cột là action cho update, delete (insert là tùy chọn của bạn)
Tạo trigger cho từng bảng, khi có thay đổi trên database gốc, nó sẽ insert vào bảng log với sự kiện tượng ứng.
Cách giải quyết này theo tôi là rất tốt: thứ nhất nó không làm nặng nề database gốc của chúng ta, khi dữ liệu bị xóa đi, nó sẽ chuyển sang database log và không làm phình to database gốc và dễ hiểu như thế khi truy vấn database gốc sẽ cho tốc độ tốt hơn vì ít dữ liệu hơn.
Vấn đề của nó là khó quản lý, bạn phải viết chương trình quản lý cho từng bảng, cực đấy chứ nhỉ. - Sử dụng một bảng duy nhất làm bảng Audit.
Đoạn script để tạo bảng này như sau:
CREATE TABLE Audit
(Type CHAR(1),
TableName VARCHAR(128),
PK VARCHAR(1000),
FieldName VARCHAR(128),
OldValue VARCHAR(1000),
NewValue VARCHAR(1000),
UpdateDate datetime,
UserName VARCHAR(128))
Với cách tiếp cận này, tôi sẽ giải thích các field như sau:
- AuditID :là một id tự tăng.
- Type: một action nó có thể là D (Delete) I (Insert) U (Update).
- TableName : action xảy ra trên bảng nào.
- PrimaryKeyField : khóa chính của dòng bị xóa (với bảng 1 khóa chính -Theo Agile, nếu bạn mong muốn khác đi, hãy customize code)
- PrimaryKeyValue: giá trị của cột chứa khóa chính.
- FieldName : Cột bị xảy ra action.
- OldValue : Giá trị cũ trước khi bị thay đổi.
- NewValue : Giá trị mới sau khi bị thay đổi.
- UpdateDate : Ngày giờ xảy ra action.
- UserName : người dùng (Tôi sẽ sử dụng user của hệ thống, hãy sử dụng username trên một table khác như bạn muốn)
Nhìn vào bảng kết quả chắc bạn đã hình dung được vấn đề.
-- Set up the tables
-- Firstly, we create the audit table.
-- There will only need to be one of these in a databaseIF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[Audit]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE Audit
(Type CHAR(1),
TableName VARCHAR(128),
PK VARCHAR(1000),
FieldName VARCHAR(128),
OldValue VARCHAR(1000),
NewValue VARCHAR(1000),
UpdateDate datetime,
UserName VARCHAR(128))
GO-- now we will illustrate the use of this tool
-- by creating a dummy test table called TrigTest.IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[trigtest]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[trigtest]
GO
CREATE TABLE trigtest
(i INT NOT NULL,
j INT NOT NULL,
s VARCHAR(10),
t VARCHAR(10))
GO--note that for this system to work there must be a primary key to the table
--but then a table without a primary key isn't really a table is it?
ALTER TABLE trigtest ADD CONSTRAINT pk PRIMARY KEY (i, j)
GO--and now create the trigger itself. This has to be created for every
-table you want to monitorCREATE TRIGGER tr_trigtest ON trigtest FOR INSERT, UPDATE, DELETE
ASDECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
@PKCols VARCHAR(1000) ,
@sql VARCHAR(2000),
@UpdateDate VARCHAR(21) ,
@UserName VARCHAR(128) ,
@Type CHAR(1) ,
@PKSelect VARCHAR(1000)--You will need to change @TableName to match the table to be audited
SELECT @TableName = 'trigtest'-- date and user
SELECT @UserName = SYSTEM_USER ,
@UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112)
+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)-- Action
IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
+ ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+'+','')
+ '''<' + COLUMN_NAME
+ '=''+convert(varchar(100),
coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAMEIF @PKCols IS NULL
BEGIN
RAISERROR('no PK on table %s', 16, -1, @TableName)
RETURN
ENDSELECT @field = 0,
@maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = POWER(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0 OR @Type IN ('I','D')
BEGIN
SELECT @fieldname = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @field
SELECT @sql = '
insert Audit ( Type,
TableName,
PK,
FieldName,
OldValue,
NewValue,
UpdateDate,
UserName)
select ''' + @Type + ''','''
+ @TableName + ''',' + @PKSelect
+ ',''' + @fieldname + ''''
+ ',convert(varchar(1000),d.' + @fieldname + ')'
+ ',convert(varchar(1000),i.' + @fieldname + ')'
+ ',''' + @UpdateDate + ''''
+ ',''' + @UserName + ''''
+ ' from #ins i full outer join #del d'
+ @PKCols
+ ' where i.' + @fieldname + ' <> d.' + @fieldname
+ ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)'
+ ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
EXEC (@sql)
END
ENDGO
-------------------------------------------------------
Đoạn mã trên sẽ làm việc hoàn hảo, nó sẽ đi vào sơ đồ của hệ thống và tìm ra tất cả những bảng có trong schema, sau đó tạo từng trigger theo một template nhất định- Hãy giới hạn table hay column bằng cách customize lại code này.
Lưu ý: Đoạn mã này thực hiện trên Microsoft SQL Server và sử dụng trigger hãy sửa đổi cho phù hợp trên những database khác. Điều này không thể thực hiện trên CSDL không hỗ trợ trigger.
Lợi ích : tiếp cận thông qua chỉ một table, điều này mang đến sự thuận tiện và dễ dàng khi quản trị, nếu hệ thống tiếp tục sinh sôi ra các bảng, đó không phải là vấn đề.
Bất lợi : Một chút về vấn đề Perfomance, với các Database trung bình và nhỏ, việc audit là bình thường, tuy nhiên nếu database lớn khi sử dụng nhiều câu Insert và Delete sẽ tạo ra những dữ liệu khổng lổ trên từng dòng (vì nó lưu 1 field trên một dòng audit ).
Trong mọi loại database dù lớn hay nhỏ, nếu chỉ sử dụng để tracking Update action, đây là một cách tiếp cận tốt nhất. Với Delete, hãy customize lại mã để sử dụng tối thiểu trường cần phải tracking hoặc có thể áp dụng phương pháp logging thứ 2 dựa trên đoạn mã này.
(Vắng nhà một thời gian dài, hy vọng bài viết trở lại này giúp ích cho các bạn)
Bài viết rất rõ dàng và dễ hiểu. Có thời gian ghé thăm blog mình nhé. Mình đặt logo của blog cậu rồi
ReplyDelete