SQL सर्वर और Linq 2 SQL बंडलों के लिए परिवर्तन इतिहास का आयोजन

अक्सर महत्वपूर्ण व्यावसायिक डेटा के साथ काम करते समय, किसी वस्तु में किसी भी बदलाव के इतिहास को रखने की इच्छा या आवश्यकता उत्पन्न होती है। इसके अलावा, किसी भी बैकअप सिस्टम की तरह, हमारा सिस्टम स्क्रैप की तरह सरल और विश्वसनीय होना चाहिए। मैंने इस तरह के एक उपकरण के निर्माण के लिए एक विषय समर्पित करने का फैसला किया।

1. समस्या का विवरण

तो चलिए पहले तैयार करते हैं कि हम आउटपुट में क्या हासिल करना चाहते हैं:

• प्रणाली को समझने, कार्यान्वयन और संचालन में यथासंभव सरल होना चाहिए।
• यह पूरी तरह से डेटाबेस के अंदर काम करना चाहिए - एक हमलावर के आवेदन में टूटने और डेटाबेस से आवेदन खाते तक पहुंच प्राप्त करने की स्थिति में, सिस्टम को काम करना और परिवर्तन करना जारी रखना चाहिए
• Linq2sql के लिए पारदर्शी होना चाहिए और संदर्भ के linq2sql परिभाषा के बाहर कोड को संशोधित किए बिना अनुप्रयोग में एम्बेडेड होना चाहिए
• मानक डेटाबेस तंत्र के आधार पर और किसी भी बाहरी अनुप्रयोगों या कार्यों की आवश्यकता नहीं होती है
• तत्काल नकल - कोई देरी नहीं
• पूरे डेटाबेस या किसी एकल रिकॉर्ड के किसी भी स्थिति में तुरंत रोलबैक करने की क्षमता
• (वैकल्पिक) लॉगिंग के लिए आवेदन से अतिरिक्त जानकारी देने की क्षमता

2. सामान्य रूप से एक सरल समाधान

मैं आपको खोजों के विवरण के साथ बोर नहीं करूंगा, मैं केवल परिणाम का वर्णन करूंगा।

हम निम्नलिखित दृष्टिकोण का उपयोग करेंगे: तालिकाओं के लिए एप्लिकेशन डायरेक्ट एक्सेस को बंद करें, केवल विशेष रूप से तैयार दृश्य देखने के लिए प्रदान करें। डेटा को अपडेट करने, हटाने और डालने के लिए, हम विशेष रूप से प्रशिक्षित ट्रिगर्स का उपयोग करेंगे, जो अलग-अलग तालिकाओं में परिवर्तनों के इतिहास को बचाएगा, इसके अलावा, हम मार्किंग द्वारा विलोपन का उपयोग करेंगे। चलो शुरू हो जाओ!

3. विस्तार से सरल समाधान

आप .NET को शामिल किए बिना केवल एक SQL सर्वर की मदद से अपनी योजना को लागू कर सकते हैं। टिप्पणी संग्रहीत करने के लिए एक तालिका पर विचार करें:

CREATE TABLE [dbo].[comments] (
[commentID] uniqueidentifier NOT NULL ,
[text] varchar (5000) NULL ,
[dt] datetime NULL ,
[userID] uniqueidentifier NULL ,
[topicID] uniqueidentifier NOT NULL ,
[enabled] bit NOT NULL DEFAULT ((1)) ,
[version] timestamp NOT NULL
)

* This source code was highlighted with Source Code Highlighter .

खेतों के बारे में अधिक:
सक्षम - टिप्पणी उपलब्धता ध्वज (याद रखें, हम चिह्न द्वारा विलोपन का उपयोग करते हैं)
संस्करण - टाइमस्टैम्प प्रकार का एक फ़ील्ड जो स्ट्रिंग के संस्करण को संग्रहीत करता है; यदि ऐसा कोई फ़ील्ड है, तो डेटा को अपडेट करते समय Linq2sql अधिक संक्षिप्त प्रश्न बनाता है (यह सत्यापित करने के लिए कि पुनर्लेखन योग्य स्ट्रिंग को पढ़ने के बाद नहीं बदला गया है, आपको सभी फ़ील्ड डेटाबेस, बस संस्करण में स्थानांतरित करने की आवश्यकता नहीं है)।
शेष पहले से ही स्पष्ट है - टिप्पणी फ़ील्ड।

आइए परिवर्तन इतिहास को संग्रहीत करने के लिए उसके लिए एक तालिका बनाएं। मेरी राय में, परिवर्तन लॉग को एक अलग तालिका में संग्रहीत करना बेहतर है, मुख्य अव्यवस्था क्यों? भविष्य में, उदाहरण के लिए, आप एक अलग फ़ाइल (मुख्य mdf और mds से लॉग में) के साथ तालिका को "बेदखल" कर सकते हैं और डेटाबेस के वारहेड को एसएसडी डिस्क में स्थानांतरित कर सकते हैं, उदाहरण के लिए, यह डेटाबेस से पढ़ने की गति का लाभ देगा। उसी कारण से, मैं जानबूझकर सूचना अतिरेक के लिए जाता हूं। यदि आप "परिवर्तित फ़ील्ड - पुराने मूल्य - नए मूल्य" की तालिका का उपयोग करते हैं, तो एक विशिष्ट तिथि तक लाइन को रोल करने के लिए, आपको इस लाइन पर सभी परिवर्तनों को वापस करने की आवश्यकता है। खैर, या प्रत्येक सेल के लिए तारीख के बाद बहुत पहले चुनें। दोनों और एक और स्पष्ट रूप से केवल एक तैयार लाइन लेने की तुलना में अधिक कठिन है। और तथ्य यह है कि आधार बढ़ेगा इतना डरावना नहीं है। यदि, विशेष रूप से, आपके मामले में, ऐसा मॉडल फिट नहीं है, तो आप अभी भी अपने परिवर्तन संग्रहण मॉडल के लिए प्रश्नों को समायोजित करके विषय के सामान्य विचार को लागू कर सकते हैं।

CREATE TABLE [dbo].[history_comments] (
[HistoryItemID] uniqueidentifier NOT NULL ,
[HistorySavedDate] datetime NOT NULL ,
[HistorySiteUser] uniqueidentifier NULL ,
[commentID] uniqueidentifier NOT NULL ,
[text] varchar (5000) NULL ,
[dt] datetime NULL ,
[userID] uniqueidentifier NULL ,
[topicID] uniqueidentifier NOT NULL ,
[enabled] bit NOT NULL ,
[version] timestamp NOT NULL
)

* This source code was highlighted with Source Code Highlighter .

खेतों के बारे में अधिक:
HistoryItemID - अभिलेख पहचानकर्ता
HistorySavedDate - संग्रह को सहेजने की तिथि
HistorySiteUser - हमें इस क्षेत्र को "जटिल" समाधान में भविष्य की आवश्यकता होगी। अभी तक उस पर ध्यान मत दो।

जादू का अगला तत्व है:

CREATE VIEW [applicationLevel].[comments_view] AS
SELECT
dbo.comments.commentID,
dbo.comments.text,
dbo.comments.dt,
dbo.comments.userID,
dbo.comments.topicID,
dbo.comments.version

FROM
dbo.comments
WHERE
dbo.comments.enabled = 1

* This source code was highlighted with Source Code Highlighter .

अंतिम झटका बना रहा - तीन ट्रिगर बनाने के लिए।

CREATE TRIGGER [applicationLevel].[onCommentUpdate]
ON [applicationLevel].[comments_view]
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON
UPDATE [dbo].[comments]
SET
[comments].[text] = [inserted].[text],
[comments].[dt] = [inserted].[dt],
[comments].[userID] = [inserted].[userID],
[comments].[topicID] = [inserted].[topicID]
OUTPUT
NEWID(),
GETDATE(),
'00000000-0000-0000-0000-000000000000' ,
[inserted].[commentID],
[inserted].[text],
[inserted].[dt],
[inserted].[userID],
[inserted].[topicID],
[inserted].[enabled]
INTO [dbo].[history_comments]
(
[HistoryItemID],
[HistorySavedDate],
[HistorySiteUser],
[commentID],
[text],
[dt],
[userID],
[topicID],
[enabled]
)
FROM
[inserted]
WHERE
[comments].[commentID] = [inserted].[commentID]
AND [comments].[version] = [inserted].[version]
END

* This source code was highlighted with Source Code Highlighter .

Google डॉक्स पर दो और INSERT और DELETE ।

4. उप योग

यह बहुत अच्छी तरह से निकला। C # में कोड की एक भी पंक्ति को संशोधित किए बिना (और, सामान्य रूप से, SQL सर्वर के साथ चल रहे किसी भी अनुप्रयोग में), हमने यह सुनिश्चित किया कि सभी परिवर्तन सुरक्षित रूप से एक अलग तालिका में संग्रहीत किए जाते हैं जो कि लेख की शुरुआत में निर्धारित सभी आवश्यकताओं को पूरा करता है, सिवाय अंतिम को छोड़कर।

उसका क्या कसूर है? मुझे समझाने दें: डेटाबेस को आवेदन में क्या हो रहा है, इसके बारे में कुछ भी नहीं पता है, यह केवल आने वाले SQL प्रश्नों को देखता है। डेटाबेस यह निर्धारित नहीं कर सकता है कि किस सिस्टम उपयोगकर्ता ने यह अनुरोध किया है - इसके लिए वे सभी एक ही व्यक्ति पर हैं और एक ही खाते के अंतर्गत आते हैं - एप्लिकेशन का SQL खाता। लेकिन मैं परिवर्तनों की तिथि और सार के बारे में जानकारी के अलावा कम से कम उपयोगकर्ता आईडी को बचाना चाहूंगा। मुझे भी, अगर आपको याद है, तो इसके लिए एक क्षेत्र बनाया गया है - HistorySiteUser। हमें यह स्वीकार करते हुए खेद है कि इस कार्यान्वयन में हम ऐसा करने में सफल नहीं होंगे। खैर, हम आत्मा से जा रहे हैं और आगे पढ़ने के लिए जा रहे हैं।

5. विचार की शिकायत करना

अंतिम पैराग्राफ में, हमने तार्किक रूप से निष्कर्ष निकाला कि हम अकेले ट्रिगर्स के साथ नहीं कर सकते। नया समाधान यह होगा: हम आवेदन से हमारे विचार के अद्यतन, इनसेट, DELETE अधिकारों का चयन करते हैं (हम इसे लेख के पहले भाग में तालिका से पूरी तरह से अलग कर देते हैं), और संबंधित संग्रहीत कार्यविधियाँ लिखेंगे जिन्हें हम C # अनुप्रयोग से कॉल करेंगे, उन्हें सभी अतिरिक्त जानकारी मापदंडों में पारित करेंगे। जिसे हम परिवर्तनों के इतिहास में रखना चाहते हैं। प्रक्रिया तालिका को अपडेट करेगी, उत्परिवर्तनीय डेटा और उन सभी अतिरिक्त डेटा को बचाएगी जिन्हें हम विशिष्ट परिवर्तन ऑपरेशन के साथ जोड़ना चाहते हैं।

5. हमें अपनी योजना का एहसास है

सबसे पहले, चौथे पैराग्राफ में बनाए गए ट्रिगर्स को हटा दें और आवेदन से अधिकारों को हटा दें, देखने के लिए अद्यतन, इनसेट, DELETE कार्रवाई।

अब हम तीन संग्रहीत कार्यविधियाँ बनाते हैं:

CREATE PROCEDURE [applicationLevel].[comment_update]
@commentID AS uniqueidentifier ,
@version AS timestamp ,
@text AS varchar (5000) ,
@dt AS datetime ,
@userID AS uniqueidentifier ,
@topicID AS uniqueidentifier ,
@SiteUserID AS uniqueidentifier = '00000000-0000-0000-0000-000000000000'
AS
BEGIN
UPDATE [dbo].[comments]
SET
[comments].[text] = @text,
[comments].[dt] = @dt,
[comments].[userID] = @userID,
[comments].[topicID]= @topicID
OUTPUT
NEWID(),
GETDATE(),
@SiteUserID,
@commentID,
[inserted].[text],
[inserted].[dt],
[inserted].[userID],
[inserted].[topicID],
[inserted].[enabled]
INTO [dbo].[history_comments]
(
[HistoryItemID],
[HistorySavedDate],
[HistorySiteUser],
[commentID],
[text],
[dt],
[userID],
[topicID],
[enabled]
)
WHERE
[comments].[commentID] = @commentID
AND version = @version
END

* This source code was highlighted with Source Code Highlighter .

Google Dox पर दो और INSERT और DELETE फिर से।

अब हमें अपने C # एप्लिकेशन को थोड़ा ठीक करना होगा। हम dbml फ़ाइल में जाते हैं और सर्वर फ़ंक्शंस से माउस के साथ कार्यक्षेत्र तक खींचें। कार्यक्षेत्र में, हमारी तालिका (टिप्पणियां) पर राइट-क्लिक करें और कॉन्फ़िगर व्यवहार आइटम का चयन करें। हम वहां उचित कार्रवाई का चयन करते हैं, और इसके लिए हमारी आयातित प्रक्रिया है।


वही डिलीट और पेस्ट के लिए जाता है।

केवल एक छोटा सा अवशेष, siteUserId फ़ंक्शन के पैरामीटर को कैसे सेट करें? बहुत सरल: हमारे DataContexta के लिए एक वारिस बनाएं और इसमें फ़ंक्शन को फिर से परिभाषित करें। ऐसा करने के लिए, इनहेरिटेंस संशोधक का चयन करें: फ़ंक्शन के गुणों में वर्चुअल (यह एक्सेस सेट करने के लिए भी उपयोगी है: संरक्षित)

public class SafetyDatabaseDataContext : DatabaseDataContext
{
#region constructors
public SafetyDatabaseDataContext () : base () { }
public SafetyDatabaseDataContext ( string connection) : base (connection) { }
public SafetyDatabaseDataContext (System.Data.IDbConnection connection) : base (connection) { }
public SafetyDatabaseDataContext ( string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : base (connection, mappingSource) { }
public SafetyDatabaseDataContext (System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : base (connection, mappingSource) { }
#endregion

protected override int comment_update( Guid ? commentID, System.Data.Linq.Binary version, string text, DateTime ? dt, Guid ? userID, Guid ? topicID, Guid ? siteUserID)
{
return base .comment_update(commentID, version, text, dt, userID, topicID, siteUserID ?? HSession.UserIdOrEmpty);
}

protected override int comment_delete( Guid ? commentID, System.Data.Linq.Binary version, Guid ? siteUserID)
{
return base .comment_delete(commentID, version, siteUserID ?? HSession.UserIdOrEmpty);
}

protected override int comment_insert( Guid ? commentID, string text, DateTime ? dt, Guid ? userID, Guid ? topicID, Guid ? siteUserID)
{
return base .comment_insert(commentID, text, dt, userID, topicID, siteUserID ?? HSession.UserIdOrEmpty);
}
}

* This source code was highlighted with Source Code Highlighter .

हम सभी मानक कंस्ट्रक्टरों को परिभाषित करते हैं (याद रखें कि कंस्ट्रक्टर विरासत में नहीं मिले हैं) अपडेट करने, बदलने और हटाने के कार्यों को फिर से परिभाषित करें ताकि वे खाली मापदंडों (मेरे मामले में साइटसाइड) को पूरक करें। मैंने अपने लिए इसे मैन्युअल रूप से सेट करने का विकल्प छोड़ दिया (केवल तभी अपडेट किया गया जब साइट यूसरआईडी == नल) आप अपना स्वयं का व्यवहार तर्क चुन सकते हैं।

बस इतना ही, आपको इसे सेफ्टीडैटेबेसडैटाकाकोटेक्स्ट डेटाबेस के साथ काम करने के लिए हर जगह इसका उपयोग करना होगा (पूरी तरह से उस वर्ग के साथ संगत जो linq2sql विज़ार्ड बनाता है)। मेरे पास डेटा संदर्भ प्राप्त करने के लिए HDataBase.GetDataContext () फ़ंक्शन है और मैंने इसे अभी ठीक किया है

public static DatabaseDataContext GetDataContext()
{
return new DatabaseDataContext();
}

* This source code was highlighted with Source Code Highlighter .

पर

public static DatabaseDataContext GetDataContext()
{
return new SafetyDatabaseDataContext();
}

* This source code was highlighted with Source Code Highlighter .

6. निष्कर्ष

समाधान पूरी तरह से लेख की शुरुआत में निर्धारित सभी आवश्यकताओं को पूरा करता है। यह व्यावहारिक रूप से आवेदन के संशोधन की आवश्यकता नहीं है और आपको आवेदन स्तर पर परिवर्तन के साथ जुड़े इतिहास और डेटा को बचाने की अनुमति देता है। इसके अलावा, इतिहास के संचालन के लिए सभी ऑपरेशन डेटाबेस में किए जाते हैं और सभी जो कि एक संभावित हमलावर, जिसने एप्लिकेशन तक पहुंच प्राप्त की है, गलत हो सकता है, परिवर्तन से जुड़ा डेटा है (इस मामले में siteUserID), यह इतिहास को दरकिनार करते हुए कुछ बदलने के लिए काम नहीं करेगा।