डेटाबेस में पेड़ों का भंडारण। भाग एक, सैद्धांतिक

छह महीने पहले, मैंने लारवेल 3 ढांचे के लिए क्लोजटेबल बंडल लिखा था। लिखने का कारण बिल कारविन द्वारा PHP का उपयोग करके MySQL में पदानुक्रमित डेटा को संग्रहीत करने और संसाधित करने के तरीकों पर यह अद्भुत प्रस्तुति थी ।

So. पदानुक्रमित संरचनाओं के भंडारण और प्रसंस्करण के लिए कई डेटाबेस डिज़ाइन पैटर्न हैं:

• आसन्न सूची
• भौतिकतावादी पथ
• नेस्टेड सेट
• बंद करने की मेज

क्लोजर टेबल क्या है

इस डिजाइन पैटर्न का सार यह है कि संस्थाओं के बीच संबंध एक अलग तालिका में संग्रहीत किए जाते हैं, जबकि मुख्य तालिका में केवल संस्थाओं का डेटा होता है।

लिंक तालिका में कम से कम दो फ़ील्ड होने चाहिए:

• पूर्वज (पूर्वज) का संदर्भ
• वंशज का संदर्भ

आइए हम एक और सुपरपॉपर सीएमएस बनाने पर काम करते हैं और पाठ पृष्ठों के संपादन के लिए एक मॉड्यूल विकसित करना शुरू करते हैं। हमें दो टेबल चाहिए:

• pages में पेज की जानकारी होगी
• pages_treepath में पेज पदानुक्रम डेटा होगा

डीबी टेबल स्कीमा

एक उदाहरण के रूप में, हम निम्न पृष्ठ पदानुक्रम का उपयोग करते हैं:

वंशज चयन

यदि हम कंपनी के सेक्शन में सभी पेजों का चयन करना चाहते हैं तो हमें ऐसी एसक्यूएल क्वेरी मिलेगी।

 SELECT * FROM pages p JOIN pages_treepath t ON (p.id = t.descendant) WHERE t.ancestor = 1 

परिणाम की शाखा। तीर पृष्ठों के बीच लिंक को दर्शाता है।

"वंशज" का अर्थ है "वंशज", और "पूर्वज" का अर्थ है पूर्वज। तदनुसार, सभी बाल पृष्ठों को प्राप्त करने के लिए, हम pages_treepath लिंक तालिका को संलग्न करते हैं बशर्ते कि पृष्ठ id का वही अर्थ हो जो वंश के लिए लिंक है। इस मामले में, मूल पृष्ठ का लिंक ancestor 1 , "कंपनी के बारे में" पृष्ठ के पहचानकर्ता है।

पूर्वज नमूना

और अब नीचे से: चलो कॉर्पोरेट पृष्ठ पर सभी "माता-पिता" देखें।

 SELECT * FROM pages p JOIN pages_treepath t ON (p.id = t.ancestor) WHERE t.descendant = 11 

इस मामले में, विपरीत। हम पदानुक्रम में उच्चतर पृष्ठों की तलाश कर रहे हैं, इसलिए, हम इस शर्त के साथ लिंक की तालिका संलग्न करते हैं कि पृष्ठ id पूर्वज ancestor से लिंक के बराबर होनी चाहिए, और वंशज लिंक द्वारा चयन करें, हमारे मामले में, 11 बराबर।

एक नया आइटम डालें

आप एक नया रिक्ति जोड़ सकते हैं। ये मान हमारे मामले में प्रतिनिधित्व नहीं करते हैं, इसलिए आइए अनुरोध को ही देखें।

 INSERT INTO pages VALUES (12, '  ', '', '    ', '0000-00-00 00:00:00', '0000-00-00 00:00:00') INSERT INTO pages_treepath (ancestor, descendant) SELECT ancestor, 12 FROM pages_treepath WHERE descendant = 4 UNION ALL SELECT 12, 12 

पहले अनुरोध के साथ, सब कुछ स्पष्ट है - यह नए डेटा का एक सरल सम्मिलन है। लेकिन दूसरे अनुरोध को क्रम में क्रमबद्ध किया जाना चाहिए, तो आइए देखें कि यहां क्या होता है।


नई रिक्ति डालने के बाद तत्वों के बीच संबंध

 SELECT ancestor, 12 FROM pages_treepath WHERE descendant = 4 

इस अनुरोध को पूरा करने से, हमें लिंक की निम्न सूची मिलती है:

 -------------------------
 |  पूर्वज |  वंशज |
 -------------------------
 |  4 |  12 |
 |  1 |  12 |
 -------------------------

संयोजन द्वारा पिछली क्वेरी में एक और जोड़ें:

 SELECT ancestor, 12 FROM pages_treepath WHERE descendant = 4 UNION ALL SELECT 12, 12 

पेज की लिंक-लिंक को स्वयं लिंक की सूची में जोड़ा जाएगा:

 -------------------------
 |  पूर्वज |  वंशज |
 -------------------------
 |  4 |  12 |
 |  1 |  12 |
 |  12 |  12 |
 -------------------------

जैसा कि आप देख सकते हैं, यह SELECT क्वेरी आपको नए पेज और उसके सभी पूर्वजों के बीच लिंक स्थापित करने की अनुमति देती है। ancestor हमेशा ancestor का संदर्भ होता है, descendant का संदर्भ होता है। एक INSERT क्वेरी, जो पहले लिखी गई थी, परिणाम को pages_treepath तालिका में सम्मिलित करती है।

आइटम हटाएं

और अब हम एक वेब डिजाइनर की रिक्ति को बंद कर देंगे।

 DELETE FROM pages_treepath WHERE descendant = 6 DELETE FROM pages WHERE id = 6 

यहां सब कुछ सरल है। सबसे पहले, हम उन सभी लिंक को हटाते हैं जहां वंशज का लिंक 6 (वेब ​​डिज़ाइनर पेज) है, और फिर हम पेज को ही डिलीट कर देते हैं।

एक नेस्टेड पेड़ हटाएं

अचानक, ऐसा हुआ कि कुछ समय के लिए अब एबीसी ने साइटों को विकसित करना बंद कर दिया। हमें इसी उपधारा को हटाने के लिए इस तरह के अनुरोध को निष्पादित करने की आवश्यकता है:

 DELETE FROM pages WHERE id IN ( SELECT descendant FROM ( SELECT descendant FROM pages p JOIN pages_treepath t ON p.id = t.descendant WHERE t.ancestor = 7 ) AS tmptable ) DELETE FROM pages_treepath WHERE descendant IN ( SELECT descendant FROM ( SELECT descendant FROM pages_treepath WHERE ancestor = 7 ) AS tmptable ) 

पिछले अनुरोध के विपरीत, यह कुछ हद तक जटिल है और पृष्ठ स्वयं पहले हटाए जाते हैं और उसके बाद उनके बीच का कनेक्शन (चूंकि बाद वाले सक्रिय रूप से पहले हटाने के दौरान उपयोग किए जाते हैं)।

प्रश्नों की जटिलता आंशिक रूप से इस तथ्य के कारण है कि MySQL क्वेरी को WHERE साथ रिकॉर्ड को हटाने की अनुमति नहीं देता है, जिसमें एक ही तालिका से SELECT चयन होता है। MySQL के मामले में, हमें एक अस्थायी तालिका में SELECT क्वेरीज़ डालने के लिए मजबूर किया जाता है। सामान्य तौर पर, हमारे प्रश्न इस तरह दिखेंगे:

 DELETE FROM pages WHERE id IN ( SELECT descendant FROM pages p JOIN pages_treepath t ON p.id = t.descendant WHERE t.ancestor = 7 ) DELETE FROM pages_treepath WHERE descendant IN ( SELECT descendant FROM pages_treepath WHERE ancestor = 7 ) 

यदि आप सावधानीपूर्वक pages तालिका से DELETE क्वेरी में नेस्टेड SELECT क्वेरी को देखते हैं, तो आप पाएंगे कि हमने पहले ही एक समान क्वेरी पर विचार किया है। यह केवल पृष्ठ पहचानकर्ता में पिछले एक से भिन्न होता है। चयन के परिणामस्वरूप, हमें साइट सेक्शन के सभी चाइल्ड पेज मिलते हैं (सेक्शन सहित), और फिर हम प्राप्त किए गए पहचानकर्ताओं के साथ सभी पेजों को हटा देते हैं।

पृष्ठों को हटाए जाने के बाद, यह उन दोनों के बीच के लिंक को हटाने के लिए रहता है। ऐसा करने के लिए, हम वंशज descendant सभी लिंक descendant , जहां पूर्वज का लिंक साइट पेज के पहचानकर्ता के बराबर है।

घोंसले का स्तर

आप संबंध तालिका में एक फ़ील्ड जोड़ सकते हैं जो तत्वों के घोंसले के स्तर को नियंत्रित करता है। यह क्षेत्र आपको तत्काल पूर्वजों या तत्काल वंशजों के चयन के लिए सरल अनुरोध करने की अनुमति देगा। उदाहरण के लिए:

 SELECT * FROM pages p JOIN pages_treepath t ON (p.id = t.descendant) WHERE t.ancestor = 4 AND t.level = 2 

डीबी टेबल स्कीमा

जारी रखा जाए।