PostgreSQL жүйесінде материалдандырылған жол

PostgreSQL, MaterializedPath

Реляциялық дерекқорларда ағаш құрылымдарында ақпаратты сақтау қосымша шығындары бар тапсырма болып табылады. Мұндай қосымша шығындар сұраныстар санының көбеюі де, деректер құрылымын ұйымдастыруға қызмет ететін ақпарат көлемінің ұлғаюы да болуы мүмкін.

Ағаш құрылымдарын ұйымдастырудың жалпы тәсілдері:

Бұл деректер құрылымдарын қарастыру сайттағы мақалаларға түсініктемелерді қолдауды енгізу қажеттілігінен туындады.

Жақындар тізімі маған түсініктемелерді ұйымдастыру үшін біршама ыңғайсыз шешім болып көрінді, өйткені негізгі нысандарды табу және ағаш құрылымын құру кезінде қиындықтар болуы мүмкін, дегенмен тәсілдің өзі элементтерді тез жоюға және қосуға мүмкіндік береді.

Кірістірілген жиын - бұл шағын сайтта пікірлерді ұйымдастырудың өте қиын әдісі, тіпті Habr сияқты үлкен ресурстарға қарасаңыз да, мақалалар астында 5000 пікір жиі бола бермейді. Мақаладағы бірінші бос түсініктеме деп аталатын, бүкіл ағаш салынатын жасырын түбір элементін қосу қажет болатындығы мені де шатастырды. Неліктен қосымша нысандарды шығару керек? - Мен қателескен шығармын. Ал, «қарсы» соңғы дәлел жаңа пікірлерді қосу кезінде кілттерді қайта есептеу қажеттілігі болды. Жалпы, бұл құрылымның артықшылықтарына қарамастан, осы сайттың аясында және оның қазіргі жағдайы - пікірлерді сақтаудың бұл тәсілі торғайларға зеңбірек отына айналады.

Бірақ Материалданған жол бізге қажет нәрсе болып көрінді. Әрбір түсініктемеде толық жол бар. Сонымен қатар мақаланың астында бірнеше түсініктеме ағаштарын ұйымдастыруға болады. Яғни, бірінші деңгейде тұрған кез келген түсініктеме автоматты түрде оның ағашының түбірі болып саналады. Ал жаңа түсініктеме қосқан кезде болашақ ата-анадан толық жолды алып, оған тек жаңа түсініктеменің идентификаторын қосу керек. Сонымен қатар, белгілі бір дерекқорға қатысты және біз PostgreSQL туралы айтып отырмыз, жолды сақтау бүтін мәндердің массиві ретінде ұсынылуы мүмкін, өйткені PostgreSQL массивтерді қолдайды (Айтпақшы, мен көбірек MySQL -ге қарағанда PostgreSQL -мен жұмыс істеу туралы оқыңыз, соғұрлым бұл маған ұнайды). Сондай-ақ, түсініктеме ағаштарын таңдаған кезде осы массивтер бойынша сұрыптауға болады, бұл ағаш құрылымын сақтай отырып, барлық түсініктемелерді хронологиялық тәртіпте автоматты түрде реттеуге мүмкіндік береді, содан кейін [Django] үлгісінде (https://evileg.com) /ru/knowledge/django/ ) бетті қалыпты циклде ешқандай рекурсиясыз жасайды, себебі барлық түсініктемелер дерекқор құралдарымен дұрыс сұрыпталады.

PostgreSQL жүйесінде қолдану

Енді бізде келесі құрылымы бар бірнеше түсініктемелері бар дерексіз бет бар деп елестетіп көрейік:

id | name
-------------------
1  | comment\_1
2  |   comment\_1\_1
3  |     comment\_1\_1\_1
4  |   comment\_1\_2
5  | comment\_2
6  |     comment\_2\_1
7  |     comment\_2\_2
8  | comment\_3

Нәтижесінде түсініктеме құрылымы келесі сұрау арқылы қалыптасады:

CREATE TABLE comments (
  id serial primary key,
  path integer[] not null,
  content varchar(200) not null
);

Бұл құрылымның бірегей автоматты өсетін идентификаторы бар, оны хронологиялық идентификатордың бір түрі ретінде де қарастыруға болады, өйткені теориялық тұрғыдан алғанда, кейінгі уақыты бар түсініктеме дерекқорда төменгі идентификаторы бар түсініктемеден бұрын пайда бола алмайды.

Дерекқорды түсініктемелермен толтырыңыз:

INSERT INTO comments (path, content) VALUES 
  ('{1}', 'comment\_1'),
  ('{1,2}', 'comment\_1\_1'),
  ('{1,2,3}', 'comment\_1\_1\_1'),
  ('{1,4}', 'comment\_1\_2'),
  ('{5}', 'comment\_2'),
  ('{5,6}', 'comment\_2\_1'),
  ('{5,7}', 'comment\_2\_2'),
  ('{8}', 'comment\_3');

Нәтижесінде біз келесі кестені аламыз:

id | path    | content       
-------------------------
1  | {1}     | comment\_1    
2  | {1,2}   | comment\_1\_1  
3  | {1,2,3} | comment\_1\_1\_1
4  | {1,4}   | comment\_1\_2
5  | {5}     | comment\_2  
6  | {5,6}   | comment\_2\_1
7  | {5,7}   | comment\_2\_2
8  | {8}     | comment\_3  

еншілес элементтерді табыңыз

Еншілес элементтерді іздеу үшін массивді қайта анықтау операторын пайдалану керек && . Бұл операторды пайдалана отырып, олардың жолында негізгі элементтің идентификаторы бар элементтерді табамыз. ID = 1 элемент үшін барлық еншілес элементтерді таңдайық.

Сонымен қатар, біз барлық элементтерді ұя салу реті бойынша сұрыптай аламыз, бұл жолдың ұзындығы.

Төмендегі сұрауда :id = 1 .

SELECT * FROM comments
WHERE path && ARRAY[:id]
ORDER BY array\_length(path, 1);

Шығару кезінде біз келесі нәтижені аламыз:

 id |  path   |    content    
----+---------+---------------
  1 | {1}     | comment\_1
  2 | {1,2}   | comment\_1\_1
  4 | {1,4}   | comment\_1\_2
  3 | {1,2,3} | comment\_1\_1\_1
(4 rows)

Негізгі элементтерді табыңыз

Барлық негізгі элементтердің тізімін алу ағымдағы элемент жолының бөлігі болып табылатын барлық элементтерді таңдау болып табылады. Мысалы, біз ID = 3 , яғни түсініктеме_1_1_1. бар түсініктеменің негізгі элементтерінің тізімін аламыз.

Төмендегі сұрауда :id = 3.

SELECT * FROM comments
WHERE id != :id 
AND array[id] && (
  SELECT path 
  FROM comments 
  WHERE id = :id
)
ORDER BY path;

Нәтижесінде біз келесі нәтижені аламыз:

 id | path  |   content   
----+-------+-------------
  1 | {1}   | comment\_1
  2 | {1,2} | comment\_1\_1
(2 rows)

Элементтерді қосу

Жаңа жазбаны жасаған кезде, біз жаңа элементтің идентификаторы қосылатын негізгі элементтің ағымдағы жолы бойымен жолды қалыптастыруымыз керек. Негізгі элемент идентификаторы 4 болатын жаңа жазба қостық делік.

Келесі сұрауда :id = 4.

INSERT INTO comments (content, path)
values (
  'comment\_1\_2\_3', 
  (SELECT path FROM comments WHERE id = :id) || (select currval('comments\_id\_seq')::integer)
);

|| операторы негізгі элементтің жолын жаңа элементтің идентификаторымен біріктіреді. Бұл ретте кестені құру кезінде автоматты түрде жасалатын comments_id_seq тізбегінен жаңа ID мәнін алу үшін біз типті трансляциялауды орындауымыз керек, өйткені жолда Integer типті массив бар. [] , және currval biginteger түрін қайтарады.

Біз келесі кестені аламыз:

 id |  path   |    content    
----+---------+---------------
  1 | {1}     | comment\_1
  2 | {1,2}   | comment\_1\_1
  3 | {1,2,3} | comment\_1\_1\_1
  4 | {1,4}   | comment\_1\_2
  5 | {5}     | comment\_2
  6 | {5,6}   | comment\_2\_1
  7 | {5,7}   | comment\_2\_2
  8 | {8}     | comment\_3
  9 | {1,4,9} | comment\_1\_2\_3
(9 rows)

Элементті жою

Элементті жою барлық еншілес элементтерді жою арқылы орындалады, ол сонымен қатар массивтердің қиылысуы арқылы жүзеге асырылады.

Мысалы, идентификаторы 5-ке тең элементті алып тастаймыз. ( :id = 5 )

DELETE FROM comments
WHERE path && ARRAY[:id];

Нәтижесінде кесте келесідей болады:

 id |  path   |    content    
----+---------+---------------
  1 | {1}     | comment\_1
  2 | {1,2}   | comment\_1\_1
  3 | {1,2,3} | comment\_1\_1\_1
  4 | {1,4}   | comment\_1\_2
  8 | {8}     | comment\_3
  9 | {1,4,9} | comment\_1\_2\_3
(6 rows)

Элементті жылжыту

Соңында элементтердің бірін оның ұрпақтарымен бірге басқа элементке жылжытайық. Таңдау үшін көп нәрсе болмағандықтан, ID = 2 элементті ID 8-ге тең элементке жылжытамыз.

Жалпы, бір элементті екіншісіне жылжыту үшін осы элемент пен оның барлық еншілестері үшін ағымдағы ата-анаға сәйкес келетін жол бөлігін жаңа ата-анаға сәйкес келетін жолға ауыстыру қажет.

Жалғыз қиындық - элемент деңгейін алу үшін бізге жылжытуды бір сұранысқа айналдыруға көмектесетін сақталған процедураны жазу керек. Оны деңгей деп атаймыз және оған біз жылжытатын элементтің идентификаторын береміз.

CREATE OR REPLACE FUNCTION level(element\_id integer) RETURNS integer AS
$$
DECLARE result integer;
BEGIN
SELECT array\_length(path, 1) FROM comments WHERE comments.id = element\_id INTO result;
RETURN result;
END
$$ LANGUAGE plpgsql;

Сақталған процедура тіркелгеннен кейін біз оны деректерді тасымалдау сұрауында қолданамыз.

Бұл сұрау мыналарды қамтиды:

• :new_parent_id - болашақ ата-ананың бірегей идентификаторы, (8-ге тең);
• :жылжымалы_id – жылжытылатын элементтің бірегей идентификаторы (2-ге тең).

UPDATE comments
SET path = (
  SELECT path
  FROM comments
  WHERE id = :new\_parent\_id
) || path[(SELECT level(:movable\_id)) : array\_length(path, 1)]
WHERE path && ARRAY[:movable\_id];

Нәтижесінде біз келесі пішіндегі кестені аламыз:

 id |  path   |    content    
----+---------+---------------
  1 | {1}     | comment\_1
  4 | {1,4}   | comment\_1\_2
  8 | {8}     | comment\_3
  9 | {1,4,9} | comment\_1\_2\_3
  2 | {8,2}   | comment\_1\_1
  3 | {8,2,3} | comment\_1\_1\_1
(6 rows)