Aparent am o poveste de spus. Ei bine și de data aceasta aparențele nu înșeală. M-am lovit de suficiente ori de problema arborilor stocați în baze de date, în special atunci când este vorba de o structură de categorii. Cum nu sunt un mare fan al procedurilor stocate și al trigger-elor, sunt de părere că impunând o constrângere de cheie străina problema se rezolvă mult mai elegant atunci când vine vorba să se șteargă toată ierarhia. MyISAM nu știe el de chei străine deci aici vine în ajutor InnoDB. InnoDB știe de chei străine, dar cel mai probabil prin transformarea tabelei problema arborelui nu se rezolvă de la sine deoarece nu se pot impune constrângerile de cheie străina.
Ideea este următoarea: pentru a impune o cheie străina folosind MySQL și InnoDB este nevoie ca structura arborelui să fie corecta și aceasta să convină lui InnoDB. A doua parte este partea spinoasă. Cel mai probabil un arbore corect definit are un lucru esențial ce îi lipsește. Se dă următoarea structură de bază:
CREATE TABLE `trees` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`parent_id` INT( 11 ) UNSIGNED NOT NULL DEFAULT '1',
`data` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
INDEX ( `parent_id` )
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci |
CREATE TABLE `trees` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`parent_id` INT( 11 ) UNSIGNED NOT NULL DEFAULT '1',
`data` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
INDEX ( `parent_id` )
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci
Precum se observă schema de date este corectă iar aparent nu pot apărea probleme. Se dă chiar următorul arbore:
1, 0, a
2, 0, b
3, 1, c
4, 1, d
5, 3, e
6, 2, f
Deși (aparent) la prima vedere structural este corect, dacă se rulează interogarea ce ar trebui sa pună constrângerea:
ALTER TABLE `trees` ADD FOREIGN KEY ( `parent_id` ) REFERENCES `trees` (
`id`
) ON DELETE CASCADE ; |
ALTER TABLE `trees` ADD FOREIGN KEY ( `parent_id` ) REFERENCES `trees` (
`id`
) ON DELETE CASCADE ;
o să dea o eroare de MySQL de toată frumusețea, ceva gen:
#1452 - Cannot add or update a child row: a foreign key constraint fails
([...]`, CONSTRAINT `[...]` FOREIGN KEY (`parent_id`)
REFERENCES `trees` (`id`) ON DELETE CASCADE)
Cei cu privirea mai ageră poate că s-au prins de soluție. Nu întâmplător i-am dat valoare implicită 1 lui parent_id, ba chiar mai mult, exemplul dat de mine nu este UN arbore, ci mai mulți arbori stocați în aceeași tabelă. Am dat acest exemplu pentru că am întâlnit de suficiente ori în practică asemenea implementări ce mai încolo îmi dădeau bătăi de cap. Ba chiar am moștenit o astfel de baza de date pentru un proiect. Ceea ce confirmă zicala care spune faptul că web developerii sug la SQL – iar cum sunt și eu în breasla lor mă auto-includ. Dar din când în când, mai există și momente de deșteptare.
Soluția sună cam asa: se face backup la date. Cheile primare nu se salvează, coloana parent_id ce va deveni cheie străina se incrementează cu o unitate dacă este vorba de o implementare precum cea din exemplul meu, dacă nu, atunci se adaptează. Se golește tabela. Se aplică iarăși codul de mai sus pentru crearea cheii străine (ALTER TABLE bla, bla, bla). În mod deloc surprinzător, aceasta va funcționa fără probleme. Nu va mai da eroarea #1452. Dar, pentru a continua, este imperativ ca această înregistrare să se găsească în tabelă:
INSERT INTO `trees` (
`id` ,
`parent_id` ,
`data`
)
VALUES (
'1', '1', 'root'
); |
INSERT INTO `trees` (
`id` ,
`parent_id` ,
`data`
)
VALUES (
'1', '1', 'root'
);
‘root’ nu e musai să fie ‘root’. Poate fi și ‘rădăcină’, dar cum personal scriu mai rar soft pentru România, prefer denumirile în Engleză. Aceasta trebuie să fie rădăcina arborelui. O înregistrare de genul (0, 0, ‘root’) este invalidă, deci nu va putea fi folosită pentru a valida implementarea originală. De aici se pot insera mai departe înregistrările, fără bătăi de cap atâta timp cât se păstrează integritatea relației cheie primară – cheie străină. De altfel, această înregistrare NU trebuie ștearsă. Dacă se șterge, se va șterge automat tot arborele. Este evident … dacă tai un copac de la rădăcina, cade cu totul. Dacă tai doar o creangă, restul copacului nu este afectat. În concluzie, dacă datele sunt manipulate corect, nu pot apărea probleme. Mai departe se poate insera noul arbore:
INSERT INTO `trees` (
`parent_id` ,
`data`
)
VALUES (
1, 'a'
), (
1, 'b'
), (
2, 'c'
), (
2, 'd',
), (
4, 'e'
), (
3, 'f'
); |
INSERT INTO `trees` (
`parent_id` ,
`data`
)
VALUES (
1, 'a'
), (
1, 'b'
), (
2, 'c'
), (
2, 'd',
), (
4, 'e'
), (
3, 'f'
);
Se va obține rezultatul dorit inițial. Iar constrângerea își face magia: spre exemplu dacă se șterge (2, 1, ‘a’), atunci automat se vor șterge și (4, 2, ‘c’), (5, 2, ‘d’) și (6, 4, ‘e’) pentru că ierarhic au ca părinte pe (2, 1, ‘a’).
Pentru că tot m-a prins microbul MVC, mai bine zis Kohana, o să dau ca exemplu un model ce tratează din punctul de vedere al aplicației problema de mai sus. Din moment ce în mod implicit Kohana este destul de restrictiv cu manipularea variabilelor, altfel spus chestii ce în mod normal PHP le tratează ca ‘Notice’ în Kohana pot da in ‘Runtime Error’, metodele sunt puțin mai stufoase în sensul că verifică integritatea înainte de a acționa. Exemple asupra a ceea ce am spus mai sus: nu se poate șterge sau actualiza o înregistrare ce este deja ștearsă. În mod normal aceste operații returnează 0, fie ca e vorba de ‘affected rows’ sau de ‘deleted rows’. Cel puțin așa se întâmplă în mod implicit în ambele situații descrise, nu am cercetat dacă este configurabil acest comportament și nici nu am de gând. Kohana prin strictețe impune modele sănătoase de programare ce nu și le însușește orice cocalar ce pune mâna pe PHP pentru faptul că variabilele încep cu $ și a auzit că poate să se simtă și el h4x0r pentru că poate programa ceva. Deci să îi dau bâte cu modelul:
class Tree_Model extends Model {
public function __construct($id = NULL)
{
parent::__construct($id);
}
public function add_child($parent_id, $data)
{
$check_record = $this->db->where('id', $parent_id)->get('trees');
if($check_record->count() > 0)
{
return $this->db->from('trees')->set(array('parent_id' => $parent_id, 'data' => $data))->insert();
}
else
{
return false;
}
}
public function update_child($id, $parent_id, $data)
{
$check_record1 = $this->db->where('id', $id)->get('trees');
$check_record2 = $this->db->where('id', $parent_id)->get('trees');
if($check_record1->count() === 1 AND $check_record2->count() > 0)
{
return $this->db->from('trees')->set(array('parent_id' => $parent_id, 'data' => $data))->where('id', $id)->update();
}
else
{
return false;
}
}
public function delete_child($id)
{
$check_record = $this->db->where('id', $id)->get('trees');
if($check_record->count() === 1 AND $id > 1)
{
return $this->db->from('trees')->where('id', $id)->delete();
}
else
{
return false;
}
}
} |
class Tree_Model extends Model {
public function __construct($id = NULL)
{
parent::__construct($id);
}
public function add_child($parent_id, $data)
{
$check_record = $this->db->where('id', $parent_id)->get('trees');
if($check_record->count() > 0)
{
return $this->db->from('trees')->set(array('parent_id' => $parent_id, 'data' => $data))->insert();
}
else
{
return false;
}
}
public function update_child($id, $parent_id, $data)
{
$check_record1 = $this->db->where('id', $id)->get('trees');
$check_record2 = $this->db->where('id', $parent_id)->get('trees');
if($check_record1->count() === 1 AND $check_record2->count() > 0)
{
return $this->db->from('trees')->set(array('parent_id' => $parent_id, 'data' => $data))->where('id', $id)->update();
}
else
{
return false;
}
}
public function delete_child($id)
{
$check_record = $this->db->where('id', $id)->get('trees');
if($check_record->count() === 1 AND $id > 1)
{
return $this->db->from('trees')->where('id', $id)->delete();
}
else
{
return false;
}
}
}
PS: clasa este portabilă și pentru alte baze de date având în vedere faptul că am folosit ‘Database Query Builder’. Mergea si cu ORM, dar prefer o abordare puțin mai directa a problemei. În ORM nu mă mai simt stăpân pe situație. Pentru cârcotași: având în vedere că aceste tipuri de structuri de date nu prea sunt dedicate modificărilor dese, impactul de performanță datorat numărului mărit de interogări pentru a verifică integritatea este minim și merită efortul pentru a face o abordare corectă în loc să se apeleze la reguli mai puțin stricte și a depinde de erorile eventuale returnate de către baza de date (FK constraint error).
Nota: nu pot corecta comportamentul cretin al WordPress pentru a afisa corect apostrof si ghilimele.