الاستعلامات التعاودية (WITH RECURSIVE): التسلسلات الهرمية والمخطّطات

اجتَز الأشجار والمخطّطات باستخدام WITH RECURSIVE، متقنًا العمق وكشف الدورات.

11 دقيقة · 3 أسئلة

يتكوّن التعبير الجدولي المشترك (CTE) التعاودي من جزأين يربطهما UNION ALL: حدّ مرتكِز (anchor) يوفّر الصفوف الابتدائية، وحدّ تعاودي يشير إلى الـ CTE نفسه. تنفّذ SQLite الحد المرتكِز مرة واحدة، ثم تطبّق الحد التعاودي في حلقة على الصفوف الجديدة، حتى لا يُنتِج أيّ صف. لننشئ تسلسلًا هرميًّا من المديرين لتوضيح ذلك.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  manager_id INTEGER
);

INSERT INTO employees (id, name, manager_id)
VALUES
  (1, 'Ada',    NULL),
  (2, 'Grace',  1),
  (3, 'Alan',   2),
  (4, 'Edsger', 2);

لعرض المخطّط التنظيمي تحت Ada، يختار الحد المرتكِز الجذر بعمق 0، ويربط الحد التعاودي كل موظف بمديره الموجود مسبقًا في الـ CTE، مع زيادة العمق. وإن عمود depth الذي تحسبه بنفسك هو أبسط طريقة لقياس مستويات التسلسل الهرمي.

WITH RECURSIVE chain(id, name, depth) AS (
  SELECT id, name, 0
  FROM employees
  WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, c.depth + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT id, name, depth FROM chain;

يُنمذَج المخطّط بجدول حوافّ (src, dst). ويتّبع الاجتياز الآلية نفسها، لكن المخطّط قد يحتوي على دورات: فاتّباع 1 -> 2 -> 3 -> 1 يدور إلى الأبد. والعلاج هو تذكّر المسار المُجتاز في عمود نصّي ورفض أيّ عقدة سبقت زيارتها. وتبحث الدالة instr عن عقدة بين نقطتين داخل ذلك المسار.

CREATE TABLE edges (
  src INTEGER NOT NULL,
  dst INTEGER NOT NULL
);

INSERT INTO edges (src, dst) VALUES
  (1, 2), (2, 3), (3, 1), (2, 4);

WITH RECURSIVE walk(node, path) AS (
  SELECT 1, '.1.'
  UNION ALL
  SELECT e.dst,
         w.path || e.dst || '.'
  FROM edges e
  JOIN walk w ON e.src = w.node
  WHERE instr(w.path, '.' || e.dst || '.') = 0
)
SELECT node, path FROM walk;

اختبار المعرفة

تأكّد من أنك تذكّرت النقاط الأساسية في هذا الدرس.

1. في الـ CTE التعاودي، أيّ مُعامِل يربط الحد المرتكِز بالحد التعاودي؟
   • UNION ALL (أو UNION)
   • JOIN
   • INTERSECT
   • CROSS APPLY
2. لماذا يخاطر المخطّط ذو الدورات بتعاود لا نهائي مع UNION ALL؟
   • لأن UNION ALL يفرز النتائج بشكل منهجي
   • لأن العقد نفسها تُزار من جديد دون إزالة التكرار
   • لأن SQLite تعطّل الفهرس أثناء التعاود
   • لأن الحد المرتكِز يُعاد تقييمه في كل تمريرة
3. كيف تتجنّب حلقة لا نهائية عند اجتياز مخطّط ذي دورات في SQLite؟
   • إضافة ORDER BY في الحد التعاودي
   • تذكّر المسار المُجتاز واستبعاد العقد التي سبقت زيارتها
   • استخدام بند CYCLE القياسي
   • زيادة حجم ذاكرة التخزين المؤقت