34. تتبّع سجل التعديلات باستخدام القوادح (Triggers)

من المتطلبات الشائعة لقواعد بيانات الإنتاج القدرة على تتبّع السجل التاريخي: كيف تغيرت البيانات بين تاريخين، ومن أجرى التغييرات، وأين حدثت؟ تتتبّع بعض أنظمة GIS التغييرات بتضمين إدارة التغيير في واجهة العميل، لكن ذلك يضيف كثيرًا من التعقيد إلى أدوات التحرير.

باستخدام قاعدة البيانات ونظام القوادح (triggers)، يمكن إضافة تتبّع السجل التاريخي إلى أي جدول، مع الإبقاء على وصول «تحرير مباشر» بسيط إلى الجدول الرئيسي.

يعمل تتبّع السجل التاريخي بالاحتفاظ بجدول للسجل يُدوِّن، لكل تعديل:

  • إذا أُنشئ سجل: متى أُضيف ومن أضافه.

  • إذا حُذف سجل: متى حُذف ومن حذفه.

  • إذا حُدِّث سجل: يُضاف سجل حذف (للحالة القديمة) وسجل إنشاء (للحالة الجديدة).

34.1. استخدام TSTZRANGE

يستخدم جدول السجل ميزة خاصة بـ PostgreSQL، وهي النوع "timestamp range" (نطاق الطوابع الزمنية)، لتخزين النطاق الزمني الذي كان فيه سجلٌّ ما هو السجل «الحي». ويُتوقع أن تكون جميع نطاقات الطوابع الزمنية في جدول السجل لمعلم معين غير متداخلة ولكن متجاورة.

يبدأ النطاق الخاص بسجل جديد عند now() وتكون نقطة نهايته مفتوحة، بحيث يغطي النطاق كل الزمن من الوقت الحالي إلى المستقبل.

SELECT tstzrange(current_timestamp, NULL);

               tstzrange
------------------------------------
 ["2021-06-01 14:49:40.910074-07",)

وبالمثل، يُحدَّث النطاق الزمني لسجل محذوف ليتضمن الوقت الحالي كنقطة نهاية للنطاق.

البحث في النطاقات الزمنية أبسط بكثير من البحث في زوج من الطوابع الزمنية، بسبب الطريقة التي يشمل بها النطاق الزمني المفتوح كل الزمن من نقطة البداية إلى ما لا نهاية. والمعامل الذي سنستخدمه هو معامل «الاحتواء» @> الخاص بالنطاقات.

-- Does the range of "ten minutes ago to the future" include now?
-- It should! :)
--
SELECT tstzrange(current_timestamp - '10m'::interval, NULL) @> current_timestamp;

يمكن فهرسة النطاقات بكفاءة عالية باستخدام فهرس GIST، تمامًا كالبيانات المكانية، كما سنوضح أدناه. وهذا يجعل استعلامات السجل التاريخي فعّالة جدًا.

34.2. بناء جدول السجل التاريخي

باستخدام هذه المعلومات يمكن إعادة بناء حالة الجدول المُحرَّر في أي لحظة زمنية. في هذا المثال، سنضيف تتبّع السجل التاريخي إلى جدولنا nyc_streets.

  • أولًا، أضف جدولًا جديدًا باسم nyc_streets_history. هذا هو الجدول الذي سنستخدمه لتخزين جميع معلومات التعديلات التاريخية. وبالإضافة إلى جميع حقول nyc_streets، نضيف خمسة حقول أخرى.

    • hid المفتاح الأساسي لجدول السجل التاريخي

    • created_by مستخدم قاعدة البيانات الذي تسبّب في إنشاء السجل

    • deleted_by مستخدم قاعدة البيانات الذي تسبّب في وسم السجل بأنه محذوف

    • valid_range النطاق الزمني الذي كان السجل خلاله «حيًا»

    لاحظ أننا لا نحذف فعليًا أي سجلات في جدول السجل التاريخي، بل نسجّل فقط الوقت الذي توقفت فيه عن كونها جزءًا من الحالة الحالية للجدول المُحرَّر.

    DROP TABLE IF EXISTS nyc_streets_history;
CREATE TABLE nyc_streets_history (
  hid SERIAL PRIMARY KEY,
  gid INTEGER,
  id FLOAT8,
  name VARCHAR(200),
  oneway VARCHAR(10),
  type VARCHAR(50),
  geom GEOMETRY(MultiLinestring,26918),
  valid_range TSTZRANGE,
  created_by VARCHAR(32),
  deleted_by VARCHAR(32)
);

CREATE INDEX nyc_streets_history_geom_x
  ON nyc_streets_history USING GIST (geom);

CREATE INDEX nyc_streets_history_tstz_x
  ON nyc_streets_history USING GIST (valid_range);

  • بعد ذلك، نستورد الحالة الحالية للجدول النشط nyc_streets إلى جدول السجل التاريخي، لتكون لدينا نقطة بداية نتتبّع منها السجل. لاحظ أننا نملأ وقت الإنشاء ومستخدم الإنشاء، لكننا نترك نهاية النطاق الزمني ومعلومات الحذف بقيمة NULL.

    INSERT INTO nyc_streets_history
  (gid, id, name, oneway, type, geom, valid_range, created_by)
   SELECT gid, id, name, oneway, type, geom,
     tstzrange(now(), NULL),
     current_user
   FROM nyc_streets;

  • نحتاج الآن إلى ثلاثة قوادح على الجدول النشط، لعمليات INSERT و DELETE و UPDATE. ننشئ أولًا دوال القوادح، ثم نربطها بالجدول كقوادح.

    عند الإدراج، نضيف ببساطة سجلًا جديدًا إلى جدول السجل التاريخي مع وقت/مستخدم الإنشاء.

    CREATE OR REPLACE FUNCTION nyc_streets_insert() RETURNS trigger AS
  $$
    BEGIN
      INSERT INTO nyc_streets_history
        (gid, id, name, oneway, type, geom, valid_range, created_by)
      VALUES
        (NEW.gid, NEW.id, NEW.name, NEW.oneway, NEW.type, NEW.geom,
         tstzrange(current_timestamp, NULL), current_user);
      RETURN NEW;
    END;
  $$
  LANGUAGE plpgsql;

CREATE TRIGGER nyc_streets_insert_trigger
AFTER INSERT ON nyc_streets
  FOR EACH ROW EXECUTE PROCEDURE nyc_streets_insert();

    عند الحذف، نَسِم ببساطة سجل السجل التاريخي النشط حاليًا (ذلك الذي وقت حذفه NULL) بأنه محذوف.

    CREATE OR REPLACE FUNCTION nyc_streets_delete() RETURNS trigger AS
  $$
    BEGIN
      UPDATE nyc_streets_history
        SET valid_range = tstzrange(lower(valid_range), current_timestamp),
            deleted_by = current_user
        WHERE valid_range @> current_timestamp AND gid = OLD.gid;
      RETURN NULL;
    END;
  $$
  LANGUAGE plpgsql;


CREATE TRIGGER nyc_streets_delete_trigger
AFTER DELETE ON nyc_streets
  FOR EACH ROW EXECUTE PROCEDURE nyc_streets_delete();

    عند التحديث، نَسِم أولًا سجل السجل التاريخي النشط بأنه محذوف، ثم ندرج سجلًا جديدًا للحالة المحدَّثة.

    CREATE OR REPLACE FUNCTION nyc_streets_update() RETURNS trigger AS
$$
  BEGIN

    UPDATE nyc_streets_history
      SET valid_range = tstzrange(lower(valid_range), current_timestamp),
          deleted_by = current_user
      WHERE valid_range @> current_timestamp AND gid = OLD.gid;

    INSERT INTO nyc_streets_history
        (gid, id, name, oneway, type, geom, valid_range, created_by)
      VALUES
        (NEW.gid, NEW.id, NEW.name, NEW.oneway, NEW.type, NEW.geom,
         tstzrange(current_timestamp, NULL), current_user);

    RETURN NEW;

  END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER nyc_streets_update_trigger
AFTER UPDATE ON nyc_streets
  FOR EACH ROW EXECUTE PROCEDURE nyc_streets_update();

34.3. تحرير الجدول

الآن وقد فُعِّل جدول السجل التاريخي، يمكننا إجراء تعديلات على الجدول الرئيسي ومشاهدة إدخالات السجل وهي تظهر في جدول السجل التاريخي.

لاحظ قوة هذا الأسلوب في تتبّع السجل التاريخي المعتمد على قاعدة البيانات: أيًا كانت الأداة المستخدمة لإجراء التعديلات، سواء سطر أوامر SQL، أو أداة JDBC على الويب، أو أداة مكتبية مثل QGIS، فإن السجل التاريخي يُتتبَّع باتساق.

34.3.1. التعديلات عبر SQL

لنغيّر اسم الشارعين المسمَّيين "Cumberland Walk" إلى الاسم الأكثر أناقة "Cumberland Wynde":

سيؤدي تحديث الشارعين إلى وسم الشارعين الأصليين بأنهما محذوفان في جدول السجل التاريخي، مع وقت حذف هو الآن، وإضافة شارعين جديدين بالاسم الجديد مع وقت إضافة هو الآن. يمكنك فحص السجلات التاريخية:

34.4. الاستعلام من جدول السجل التاريخي

الآن وقد أصبح لدينا جدول للسجل التاريخي، ما فائدته؟ إنه مفيد للسفر عبر الزمن! للسفر إلى وقت معين T، عليك إنشاء استعلام يتضمن:

  • جميع السجلات التي أُنشئت قبل T ولم تُحذف بعد؛ وكذلك

  • جميع السجلات التي أُنشئت قبل T لكنها حُذفت بعد T.

يمكننا استخدام هذا المنطق لإنشاء استعلام، أو عرض (view)، لحالة البيانات في الماضي. وبما أن جميع تعديلاتك التجريبية حدثت على الأرجح خلال الدقائق القليلة الماضية، لننشئ عرضًا (view) لجدول السجل التاريخي يُظهر حالة الجدول قبل 10 دقائق، قبل أن تبدأ التحرير (أي البيانات الأصلية).

-- Records with a valid range that includes 10 minutes ago
-- are the ones valid at that moment.

CREATE OR REPLACE VIEW nyc_streets_ten_min_ago AS
  SELECT * FROM nyc_streets_history
    WHERE valid_range @> (now() - '10min'::interval)

يمكننا أيضًا إنشاء عروض (views) تُظهر فقط ما أضافه مستخدم معين، على سبيل المثال:

CREATE OR REPLACE VIEW nyc_streets_postgres AS
  SELECT * FROM nyc_streets_history
    WHERE created_by = 'postgres';

34.5. انظر أيضًا