mssql - התייעצות כיצד לשמור שינויי עריכה בטבלאות

mekev

ע"מ לקבל את המירב מהתשובה
אמנע הפעם מלהציג את לבטי מחשבותי
אלא אשאל 'שאלה פתוחה'

מסד נתונים: MS-SQL
כיצד הינך מנהל רישום היסטוריית שינויי עריכה לנתוני הטבלאות (DELETE,UPDATE,INSERT)

OdedDvir

באופן עקרוני, תיעוד אמור להיות מבוצע על ידי כלי, אתה לא אמור להמציא את הגלגל מחדש (ראה לקמן)

תיעוד בשכבת ה-SQL
יש ל-SQLServer יש אפשרות מובנית לתיעוד על ידי שימוש במעקב שינויים
ראה כאן מדריך בנושא: https://www.sqlshack.com/creating-a-sql-server-audit-using-sql-server-change-tracking/
היתרון באפשרות הזו הוא: שהכל מתחיל ונגמר ב-SQL, ללא צורך בכלים נוספים
החיסרון בגישה הזו הוא: שהכל מתחיל ונגמר ב-SQL... קשה מאוד לתשאל את הנתונים במקרה שרוצים לחקור אירוע, התיעוד מבוצע לתוך הDB, ולא לתוך קבצי טקסט חיצוניים, ושיטה זו עלולה בקלות להוסיף המון רעשי רקע לפעילות השוטפת של ה-DB

תיעוד בשכבת DAL
אם יש לך שכבת DataAccess, למשל API שמבצע את השליפה של הנתונים.
בדוטנט מומלץ להשתמש בכלי תיעוד כגון Serilog
שיטה זו היא העדיפה ביותר: אפשר לבנות לוגים מבניים Structured logs שניתנים לתחקור בקלות על ידי כלי אנליטיקה של לוגים, כמו SEQ
אפשר לתעד לקבצי json יומיים, לשלוט על כמות המידע שנצבר ולהגדיר מתי למחוק לוגים ישנים, ל-Serilog יש אפשרות לנקז את הלוגים שלו לכמה אפיקים במקביל. למשל: תוכל לשלוח בו זמנית את הלוגים שלך לקבצים בדיסק, לקונסול, וגם לשרת לוגים כמו SEQ או QRadar שנותנים לך המון אפשרויות להצגה נוחה של הפעילות, ניתוח ושאילתות פילוח (מי המשתמשים שנכנסו הכי הרבה בחודש האחרון, מה הזמן הממוצע שמשתמש מחובר למערכת וכו')
החיסרון? לא תמיד יש שכבת DAL מסודרת. אבל אם זה המקרה, אז יש כאן בעיה הרבה יותר גדולה מבעיית חוסר התיעוד.

להמציא את הגלגל
זה בפירוש לא מומלץ לכל מקרה, ואני בספק אם יש לזה מקום מעשי בכלל, אבל במקרה הנדיר שכן, אתה יכול לבנות כלי מהיר ומלוכלך לתיעוד ב-SQL:
SQL Server 2016 ומעלה:

CREATE PROCEDURE [dbo].[fn_GetJsonData]
-- =================================================
-- Description:	Get JSON data from a table row
-- =================================================
    @TableName NVARCHAR(max),
    @Id INT,
    @Columns NVARCHAR(max) = '*',
    @JsonData NVARCHAR(max) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Sql NVARCHAR(max) = 'SELECT @JsonData=(SELECT TOP 1 ' + @Columns + ' FROM ' + @TableName + ' WHERE Id=' + CAST(@Id AS VARCHAR) + ' FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER)'

    EXEC sp_executesql @Sql,
        N'@JsonData NVARCHAR(max) OUTPUT',
        @JsonData OUTPUT
END
GO

טבלת תיעוד פשוטה:

CREATE TABLE MySimpleAudit (
    Id INT identity(1, 1) PRIMARY KEY,
    TableName NVARCHAR(max),
    OriginalData NVARCHAR(max),
    NewData NVARCHAR(max),
    LogTimeStamp DATETIME2 DEFAULT getutcdate()
    )

כדי לתעד, אתה צריך לקרוא לפרוצדורה הנ"ל לפני השינוי ואחריו, ולתעד את השינויים לטבלה:


-- Init variables:
DECLARE @OriginalData NVARCHAR(max), @NewData NVARCHAR(max);

-- Get original data:
EXEC [dbo].[fn_GetJsonData] @TableName = 'Payments',
    @Id = 1234,
    @Columns = 'Id,Amount',
    @JsonData = @OriginalData OUTPUT

-- Do manipulations on the table:
-- ...........
---

-- Get Changes:

EXEC [dbo].[fn_GetJsonData] @TableName = 'Payments',
    @Id = 1234,
    @Columns = 'Id,Amount',
    @JsonData = @NewData OUTPUT

INSERT INTO [MySimpleAudit] (TableName, OriginalData, NewData)
VALUES ('Payments', @OriginalData, @NewData)

mekev

@OdedDvir
תודה על התשובה המפורטת והמושקעת!
עברתי עליה בעיון מספר פעמים

כד הוינא טליא
עשיתי פונקציה בקוד בצד לקוח שלפני ואחרי כל רישום לטבלה, מכניסה לטבלת היסטוריה את השורה/ות לפני ואחרי העדכון (בהחלט שגוי, אבל ככה עשיתי בפרויקטים ישנים, די דומה למה שהצעת לא לעשות...)

כיום אני מבצע שימוש בטריגרים בsql על בסיס הקוד הזה (יוצר 3 טריגרים לכל טבלה)

select *  from deleted d 
select *  from inserted i

כעת ראיתי שיש גם אפשרות של Temporal Tables
שנראת די מסקרנת
מישהו עובד עם זה?
מישהו יודע האם זה עובד טוב ויעיל או שזה תוקע / גורם מורכבות בטרנזקציות וכו'?

ובעיקר אני לומד
שהצורך שלי הוא בתיעוד של לוגים מבניים בצד שרת (node.js ekspres)
בנוסף כמובן לרישום בSQL

ספרייה מומלצת?
כמה ואיזה מידע מומלץ לשמור?

OdedDvir

@mekev משום מה הגיוני לי יותר (לתפוס מנהג השוטים ו)לענות לך על אחרון ראשון:

1. כמה ואיזה מידע לשמור?
התשובה הלא ממש מפתיעה היא שזה תלוי: באופי הנתונים, בתדירות שבהם הם משתנים, בנפח המידע ובתפוקה (עיבוד ביחס לזמן), ובמשאבים שעומדים לרשותך. תשמור את המקסימום מחלק המידע שאתה חייב לשמור, ואת המינימום מהחלק שאתה לא.

2. ספרייה מומלצת?
אני לא עובד עם node.js אבל נראה ש Pino ו-Winston ספריות פופולאריות מאוד. שתיהן תומכות בלוגים מובניים, אבל Pino דורשת פחות קונפיגורציה.

3. מישהו יודע האם (תיעוד ב-SQL) עובד טוב ויעיל או שזה תוקע / גורם מורכבות בטרנזקציות וכו'?
הייתי נוטה להתרחק מהכיוון של תיעוד ברמת ה-SQL, אא"כ מדובר במערכת קטנה מאוד עם מעט מאוד רעש, או לחילופין במערכת קריטית שיש בה צורך לתעד כל פיפס בזמן אמת. במקרה האחרון מסתמא הייתי רושם את התיעוד ל-db אחר.
אני לא מספיק מומחה לדחות בידיים את הרעיון, אבל על פניו 3 טריגרים על כל טבלה נשמע לי מאוד מרתיע.
הסיבה היא שלרוב צד ה-db הוא צוואר הבקבוק במערכת, ולתיעוד ב-SQL יש מחיר גבוה.
הגע עצמך, אם כל שינוי של שדה בטבלה משכפל את הרשומה לטבלה אחרת, זה אומר שנפח הנתונים שלך עשוי לגדול בצורה מעריכית, לדוגמא, אם ביצעת 2 פעמים (בטרנזקציות נפרדות) עריכה של שדה מספר הטלפון של משתמש - שיכפלת את כל הרשומה שלו 3 פעמים. אם ערכת את השם שלו - שיכפלת פעם נוספת. אפילו אם הקלדת את הרשומה שלו בצורה חלקית, כי לא היה לך בהתחלה את הכתובת שלו, כשתערוך את השדה של הכתובת - תשכפל שוב את כל הרשומה. מחקת ערך בשדה בודד - שכפלת שוב. אני חושב שאתה קולט את התבנית.

לוגים שמשכפלים רשומות שלמות בטבלה אכן לא משמיטים שום שינוי שבוצע במידע, אבל אני רוצה לומר לך את התפיסה שלי על לוגים: אין להם שום ערך אם אי אפשר לתשאל אותם. אני לא רוצה לדמיין את עצמי ולא אף יהודי טוב סורק טבלאות של מליון רשומות או כותב סלקטים מתוחכמים רק כדי לדעת מתי שינו את מספר הטלפון של פלוני, או מי מחק את הדוח לפני חודש.

לוגים מבניים בשכבת ה-DAL הם לדעתי הפתרון הטוב ביותר. אתה תרוויח גם יכולת לתעד חריגות ושאר אירועים שמתרחשים מחוץ לשכבת ה-DB או שאינם נוגעים בעדכון נתונים, כמו מה הדף הכי נצפה, התנהגות המשתמש עם האפליקציה, הזמן שלקח לבצע חישוב מסויים, וכו'.

mekev

@OdedDvir
בהמשך לדבריך
רק להאיר זווית נוספת

יש לחלק בין מסד נתונים של מידע אינפורמטיבי
לבין מידע עסקי

אני עובד ברשת סיטונאית וקמעונית גדולה
ויושב על מגוון מערכות מידע של החברות תוכנה הנחשבות והנפוצות בשוק

ופעמים רבות אני יושב מתוסכל עד עמקי נשמתי
למה אין תיעוד ושמירת היסטוריה לכל שינוי

(דוגמא אמיתית: לא נשמר היסטוריית מחיר עלות
והדוחות בתוכנה מבוססים על ההפרש בין המחיר במסמך המכירה למחיר עלות מכרטיס המוצר
ואז שמגיע שנות האינפלציה ויש שינוי במחיר עלות,
אתה שובר ת'ראש בקיר איך להוציא דוחות על רבעונים קודמים)

ה-מ-ו-ן מידע עסקי מתפספס ונמוג בעקבות חוסר בנתונים היסטוריים
כולל ובפרט כל מיני שינויים במערכת (החל מהגדרות, וכלה באכמ"ל..)
שבסופו של יום
אם היה תיעוד פשוט של רשומות לפני ואחרי החיים היו יותר קלים

יש לי אינספור דוגמאות חיות למידע שחסר
(גילוי נאות: מכאן מגיע הפרנויה שלי במערכות שאני כותב (מידע עסקי)
לשמור ולתעד את כל הרשומות ששונו, למה? כי יבוא יום שיהיה לזה משמעות)

כך שהסברא שנפח הנתונים עשוי לגדול בצורה מעריכית הינו נכון
ולכן לדוגמא במסד נתונים של פורום כלשהו
יש פחות הגיון לשמור כל שינוי של המשתמש
כי מה שחשוב זה הפוסט המוצג לגולש
ופחות רלוונטי ויש מה לעשות עם ערימת הטיוטות של המשתמש

אבל כאשר מדובר בנתונים עסקיים
גם כמות השינויים פר רשומה בדר"כ הינה פחותה
ואם יש שינוי - אז זה נתון חשוב שיש לו חשיבות לשמירה
(גם שדה טלפון וכתובת של לקוח, במידה וזה לא משתמש באתר
אלא לקוח שרכש ממך בהקפה, יכול להיות מידע מאוד רלוונטי כאשר הוא 'פורח' ואתה מנסה לאתר קצה חוט של איש קשר קודם)

@OdedDvir כתב בmssql - התייעצות כיצד לשמור שינויי עריכה בטבלאות:

במקרה האחרון מסתמא הייתי רושם את התיעוד ל-db אחר.
הסיבה היא שלרוב צד ה-db הוא צוואר הבקבוק במערכת, ולתיעוד ב-SQL יש מחיר גבוה.

מנסיון מהצד של משתמש במערכות שרשומות כמות רשומות נאה מאוד ביום

יוצרים גיבוי לטבלאות באותו מסד, ומבצעים גיבוי יומי לכל המסד
מבחינת המשאבים - בניהול נכון, מדובר על ניצול של אחוזים בודדים מהיכולת
(רוב הפעולות הם כתיבה, עדכון, מחיקה, שאילתות שליפה מבוססות על אינדקסים נכונים ועל אופטימיזציה(נושא נפרד))
ולכן זה שמדובר על צוואר בקבוק - בעיני זה מעלה, במקום לרוץ לקוד לזכור לשמור בכל מקום, ואם יש מישהו שנוגע בנתונים ממקום אחר או ישירות ממסד הנתונים מסיבה כלשהיא - לא יהיה לך תיעוד

OdedDvir

@mekev כתב בmssql - התייעצות כיצד לשמור שינויי עריכה בטבלאות:

(דוגמא אמיתית: לא נשמר היסטוריית מחיר עלות
והדוחות בתוכנה מבוססים על ההפרש בין המחיר במסמך המכירה למחיר עלות מכרטיס המוצר

בוא נפריד בין הצורך לשימור מידע היסטורי, לבין הצורך בתיעוד פעולות (Audit).
במקרה זה זו לא בעיה של חוסר תיעוד, זהו תכנון לקוי של המערכת. לרוב עדיף לבצע מחיקה לוגית של נתון (סימון הרשומה כארכיון) ולא מחיקה פיזית של הרשומה.

לגבי הצורך בתיעוד פעולות, הרישום ב-DB מספק פיתרון חלקי, כי הוא לא כולל פעולות שלא נוגעות בדאטא וכנ"ל.
איך תתעד בצורה הזו את כתובת ה-IP של מי שניגש למערכת? גם אם העברת את הנתון מהקליינט אל ה-db, איך תשלוף את זה ותצליב עם פעולה מסויימת בטבלה?

@mekev כתב בmssql - התייעצות כיצד לשמור שינויי עריכה בטבלאות:

מכאן מגיע הפרנויה שלי במערכות שאני כותב (מידע עסקי)
לשמור ולתעד את כל הרשומות ששונו, למה? כי יבוא יום שיהיה לזה משמעות

תהיה לזה משמעות, ובוודאי עדיפות על לא כלום, אבל יהיה לך קושי גדול בהפקת תועלת בצורה כזו, לעומת תיעוד בלוג מובנה.
אני חוזר שוב: ללוגים אין שום ערך אם אי אפשר לתשאל אותם.

mekev

@OdedDvir

בהמשך
ועם/בלי קשר
אנקדוטה מהדקות האחרונות

באתי להחליף כעת סיסמא באתר של בנק הפועלים
והוא לא אפשר לי להזין סיסמא חדשה שהיתה קיימת בעבר (אפילו בעבר הרחוק)

צא ולמד...
שאפילו נתון שכזה הם שומרים
(הגיוני לבדוק שהסיסמא החדשה לא זהה לנוכחית, אבל סיסמא לפני חמש שנים?)

A0533057932

@mekev כתב בmssql - התייעצות כיצד לשמור שינויי עריכה בטבלאות:

צא ולמד...
שאפילו נתון שכזה הם שומרים
(הגיוני לבדוק שהסיסמא החדשה לא זהה לנוכחית, אבל סיסמא לפני חמש שנים?)

מאד הגיוני
מאגרים דולפים כל הזמן
ואם זה סיסמה שכבר השתמשת בה יתכן מאד שהיא דלפה מאז
ולכן הם מחייבים חדשה

צדיק תמים

@mekev כתב בmssql - התייעצות כיצד לשמור שינויי עריכה בטבלאות:

צא ולמד...
שאפילו נתון שכזה הם שומרים
(הגיוני לבדוק שהסיסמא החדשה לא זהה לנוכחית, אבל סיסמא לפני חמש שנים?)

זה שמירה מסוג אחר לגמרי, אני בטוח שהם לא בודקים בטבלה כלשהיא של היסטוריית שינויים או גרסאות קודמות אלא שומרים באופן מפורש את הישנות

תחומים - פורום חרדי מקצועי

mssql - התייעצות כיצד לשמור שינויי עריכה בטבלאות