veritabanı programlayamama konusunda en önemli örneklerden biri, hiyerarşik datalar ve veritabanları. bahsetmek istediğim konu, iç içe geçmiş yorumlar, ağaç şeklindeki menüler vs. gibi basamaklı uygulamalar ve bunların datasının veritabanında saklanması. sırayla kullanılabilecek modelleri inceleyip, örnekle gidip daha sonra kaynakların ekonomik kullanımı konusuna geçelim.örneğin şöyle bir sayfa oluşturmak istiyoruz :yorumlar :yorum 1yorum 1 cevapyorum 2yorum 3yorum 4yorum 4 cevapyorum 4 cevapa cevap
bunu yapmak için temelde 2 farklı veritabanı modeli kullanabilirsiniz, Adjacency List Model ve Nested Set Model ( çok üzgünüm bunların türkçe isimlerini bilemiyorum, ve bulamıyorum, umarım yorumlarda türkçe karşılıkları verilir, bende yazıyı güncellerim )Adjacency List Model
bu modelde, temel olarak, bir nesnenin bağlı olduğu diğer nesneleri göstermek için, parent/ebeveyn kolonu kullanıyoruz. muhtemelen bu şekilde uygulamalarla ya karşılaşmışsınızdır yada kendiniz yapmışsınızdır. hızlıca bir örnek verip diğer modele geçeceğiz.yorumlar tablosu şuna benzer birşey olsun,create table yorumlar (id int not null auto_increment,yorum text,parent_id int default 0,primary key (id));insert into yorumlar set yorum=’yorum 1′;insert into yorumlar set yorum=’yorum 2′;insert into yorumlar set yorum=’yorum 3′;insert into yorumlar set yorum=’yorum 4′;insert into yorumlar set parent_id=4, yorum=’yorum 4 cevap’;insert into yorumlar set parent_id=5, yorum=’yorum 4 cevapa cevap’;insert into yorumlar set parent_id=1, yorum=’yorum 1 cevap’;
select * from yorumlar;+—-+———————-+———–+| id | yorum | parent_id |+—-+———————-+———–+| 1 | yorum 1 | 0 || 2 | yorum 2 | 0 || 3 | yorum 3 | 0 || 4 | yorum 4 | 0 || 5 | yorum 4 cevap | 4 || 6 | yorum 4 cevapa cevap | 5 || 7 | yorum 1 cevap | 1 |+—-+———————-+———–+
şimdi bundan veri çekecek php scripti aşağı yukarı şöyle bir şey olacaktır: çıktısıda şöyle olur :yorum 1yorum 1 cevapyorum 2yorum 3yorum 4yorum 4 cevapyorum 4 cevapa cevap
rekursif şekilde select yapmanın bize maliyeti her farklı parent için 1 query göndermek olacaktır tabii. bir tartışma alanı düşünün, – örneğin digg deki yorumlar gibi – yüzlerce query göndermek zorunda kalırsınız. fakat yeni bir yorum ekleyen fonksiyon ziyadesiyle kolaydır.function insertComment($yorum, $parent_id){mysql_query(‘insert into yorumlar set parent_id=’.$parent_id.’, yorum=”‘.$yorum.'”‘);} malum bir sayfada yüzlerce sorgu göndermek ciddi sorunlara yol açacağı için diğer modele geçiyoruz, tabii yazının gerisini okumadan önce, sayfayı olduğu gibi cachelemek, yada yorumları cachelemek gibi çeşitli alternatifleri de düşünebilirsiniz. ama twitter örneğini unutmamak gerek, twitter devamlı sayfalar değiştiği için, page cache kullanamıyor mesela, devamlı cacheyi yazıp okuması, invalidate etmesi daha maliyetli olduğu için. herneyse, diğer modelimiz,Nested Set Model
bu modeli anlatması, biraz karışık olduğu için, umarım başarabilirim diyerek başlıyorum,create table yorumlar_n (id int not null auto_increment,yorum text,lft int ,rgt int ,primary key(id));
left ve right diyemediğimiz için, çoğu veritabanı sunucunda reserve kelimedir çünkü, lft ve rgt dedik. şimdi pekii bu lft ve rgt kolonları nedir diye soracak olursanız, şuna benzer bir şekilde veriyi dizdiğimizi düşünün, kumeleri hatirliyorsunuz degil mi ?____________________________________________________________________________________________________________| yorumlar || (yorum 1 ) (yorum 2) (yorum 3) (yorum 4 ) || (yorum 1 cevap) (yorum 4 cevap ) || (yorum 4 cevapa cevap) ||___________________________________________________________________________________________________________|
şimdi de bunları sol baştan başlayarak numaralandırıyoruz______________________________________________________________________________________________________| yorumlar || (yorum 1 ) (yorum 2) (yorum 3) (yorum 4 ) || | (yorum 1 cevap) | | | | | | (yorum 4 cevap ) | || | | | | | | | | | | (yorum 4 cevapa cevap) | | || | | | | | | | | | | | | | | |1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16|______________________________________________________________________________________________________|
şimdide veritabanına bu numaralara göre datayı ekliyorum,insert into yorumlar_n set yorum=’yorumlar’, lft=1,rgt=16;insert into yorumlar_n set yorum=’yorum 1′, lft=2,rgt=5;insert into yorumlar_n set yorum=’yorum 2′, lft=6,rgt=7;insert into yorumlar_n set yorum=’yorum 3′, lft=8,rgt=9;insert into yorumlar_n set yorum=’yorum 4′, lft=10,rgt=15;insert into yorumlar_n set yorum=’yorum 1 cevap’, lft=3,rgt=4;insert into yorumlar_n set yorum=’yorum 4 cevap’, lft=11,rgt=14;insert into yorumlar_n set yorum=’yorum 4 cevapa cevap’, lft=12,rgt=13;
farkettiyseniz, kimin kime bağlı olduğu yada id kolonuyla ilgilenmiyoruz. bu arada bu yönteme preorder tree traversal algoritması deniyor (üzgünüm bununda tam türkçe karşılığını bilemiyorum )şimdi gelelim burdan datayı nasıl çekeceğimize, sorgumuz şöyle olacak,mysql> select id, yorum, lft from yorumlar_n as node order by node.lft;+—-+———————-+——+| id | yorum | lft |+—-+———————-+——+| 1 | root | 1 || 2 | yorum 1 | 2 || 6 | yorum 1 cevap | 3 || 3 | yorum 2 | 6 || 4 | yorum 3 | 8 || 5 | yorum 4 | 10 || 7 | yorum 4 cevap | 11 || 8 | yorum 4 cevapa cevap | 12 |+—-+———————-+——+8 rows in set (0.00 sec)
bu modelde işin güzel tarafı şöyle birşeyde yapabilirsiniz, yorum 4 ve altındakileri çekmek için, tabloyu kendisiyle join ederek tek sorguda istediğiniz kısmı çekme imkanınız var.mysql> SELECT node.id, node.yorum-> FROM yorumlar_n AS node, yorumlar_n AS parent-> WHERE node.lft BETWEEN parent.lft AND parent.rgt-> and parent.id=5-> ORDER BY node.lft;+—-+———————-+| id | yorum |+—-+———————-+| 5 | yorum 4 || 7 | yorum 4 cevap || 8 | yorum 4 cevapa cevap |+—-+———————-+3 rows in set (0.00 sec)
kaçıncı levelda (depthte, derinlikte, basamakta) olduklarınıda birlikte çekmek istersek şöyle bir query yapabiliriz,SELECT node.yorum, (COUNT(parent.yorum) – 1) AS depthFROM yorumlar_n AS node,yorumlar_n AS parentWHERE node.lft BETWEEN parent.lft AND parent.rgtGROUP BY node.yorumORDER BY node.lft;
+———————-+——-+| yorum | depth |+———————-+——-+| root | 0 || yorum 1 | 1 || yorum 1 cevap | 2 || yorum 2 | 1 || yorum 3 | 1 || yorum 4 | 1 || yorum 4 cevap | 2 || yorum 4 cevapa cevap | 3 |+———————-+——-+8 rows in set (0.00 sec)
veya bir yorumun altındaki diğer yorumların sayısını çekmek istersek, basit aritmetik bilgimizle, şuna benzer birşey yapabilirizselect id, yorum, lft, (rgt – lft +1)/2 as yorum_say from yorumlar_n as node order by node.lft;
mysql> select id, yorum, lft, (rgt – lft +1)/2 as yorum_say from yorumlar_n as node order by node.lft;+—-+———————-+——+———–+| id | yorum | lft | yorum_say |+—-+———————-+——+———–+| 1 | yorumlar | 1 | 8.0000 || 2 | yorum 1 | 2 | 2.0000 || 6 | yorum 1 cevap | 3 | 1.0000 || 3 | yorum 2 | 6 | 1.0000 || 4 | yorum 3 | 8 | 1.0000 || 5 | yorum 4 | 10 | 3.0000 || 7 | yorum 4 cevap | 11 | 2.0000 || 8 | yorum 4 cevapa cevap | 12 | 1.0000 |+—-+———————-+——+———–+8 rows in set (0.00 sec)
burdaki (node.rgt – node.lft +1)/2 – 1 formülünü parçalayalım, bir yorumun altındaki her yorumun 2 değeri var, left ve right, o zaman, altındakilerin başlangıç ve bitiş noktaları arasındaki değerleri çıkartır, sonrada 2 ye bölersem, altındaki yorum sayısını bulurum. örneğin yorum 4 altındakileri bulmak için, 15 ten 10 u çıkartır 2 ye bölerim, saymaya 0 dan değil, 1 den başladığım için, 1 çıkartırım (15 – 10) – 1 / 2 = 2 yapar.SELECT node.yorum, (COUNT(parent.yorum) – 1) AS depth, (node.rgt – node.lft – 1)/2 as yorum_sayFROM yorumlar_n AS node,yorumlar_n AS parentWHERE node.lft BETWEEN parent.lft AND parent.rgtGROUP BY node.yorumORDER BY node.lft;
+———————-+——-+———–+| yorum | depth | yorum_say |+———————-+——-+———–+| yorumlar | 0 | 7.0000 || yorum 1 | 1 | 1.0000 || yorum 1 cevap | 2 | 0.0000 || yorum 2 | 1 | 0.0000 || yorum 3 | 1 | 0.0000 || yorum 4 | 1 | 2.0000 || yorum 4 cevap | 2 | 1.0000 || yorum 4 cevapa cevap | 3 | 0.0000 |+———————-+——-+———–+8 rows in set (0.00 sec)
şimdi kendimizi biraz daha zorlayıp, nested seti, adjacency list olarak göstermek istersek, şöyle bir query yazabiliriz,SELECT O.id, I.id as parent_idFROM yorumlar_n AS OLEFT OUTER JOINyorumlar_n AS ION B.lft= (SELECT MAX(lft)FROM yorumlar_n AS SWHERE O.lft > S.lftAND O.lft < S.rgt);
+—-+———–+| id | parent_id |+—-+———–+| 1 | NULL || 2 | 1 || 3 | 1 || 4 | 1 || 5 | 1 || 6 | 2 || 7 | 5 || 8 | 7 |+—-+———–+
şimdi bunun tam tersini yazıp adjacency listi nested viewa çevirmeden, ve nested sete yeni veri girmeden önce phpde görüntülemek için olan fonksiyonu yazalım, ve bu yazıyı toparlayalım. çıktısı aynı şekildedir :yorumlar (7)yorum 1 (1)yorum 1 cevap (0)yorum 2 (0)yorum 3 (0)yorum 4 (2)yorum 4 cevap (1)yorum 4 cevapa cevap (0)
aradaki fark tüm yorumları ve hatta yorumların altındaki yorum sayılarıyla birlikte sadece tek bir sorgu gönderirken, list kullanarak defalarca sorgu göndermek zorunda kalmıyoruz. örneğin 100 civarı yorum almış bir sayfayı düşünün, göndereceğiniz sorgu sayısı parent_id sayısı kadardır, birde buna yorum sayısı eklemek istediğinizi düşünün, 100lerce sorgu göndermek zorunda kalabiliriz.bir sonraki yazıda nested sete data ekleme ve çıkartma işlemleriyle devam edip, daha sonra adjacency list modele göre dezavantajlarından bahsedeceğiz. sonrada, her iki modeldeki denormalizasyon opsiyonlarına ve farklı algoritmalara değiniriz. umarım hiyerarşik yapıları kullanan birilerine yardımcı olur bu yazı.