Drzewka (for BSP) Opcje

[DeyV]

Postanowiłem pobawić się omawianym już kilkakrotnie systemem tworzenia drzew w bazie (np. http://forum.php.pl/viewtopic.php?t=2625 i http://www.depesz.pl/various-sqltrees.php - 5 sposóB) przy pomocy MySQL.
Początkowo wszystko było ok.
Dodawanie pozycji - banalne. Przeglądanie - w te i we wte - również.
Jednak trafiłem na problem. Zastanawiam się, jak efektownie usuwać gałęzie. Oczywiście można pobrać dane wszystkich potomków, i w pętli, lub przy pomocy IN usunąć je.
Jednak wszyscy mówili, że można to zrobić za pomocą pojedynczych zapytań. No i mam problem
Wymyśliłem takie zapytanie

DELETE FROM powiazania  
USING powiazania AS p  
LEFT JOIN powiazania as p2  
   ON ( p.first_id = p2.second_id )  
WHERE  p2.first_id = 1

jednak nie działa ono do końca poprawnie, gdyz za szybko usuwa sobie niektóre wiersze, i doprowadza to do tego, że niektóre pozostają.

Nie mam również żadnego pomysłu, jak zmontować przenoszenie gałęzi

Struktura bazy i przykładowe dane :arrow: http://mstudio.nq.pl/php_pl/inne/test_drzewko.sql

[uboottd]

Metoda faktycznie jest zaawansowana, ale tez bardzo wygodna. I faktycznie ma kilka pulapek. Po kolei:

1. Przyklad depesza ma bledy - musial pisac tabele z pamieci albo na szybko - kilka wartosc jest poprzestawianych. Przeanalizuj metode i jako cwiczenie wygeneruj poprawna zawartosc tabeli powiazania.

2. Da sie kasowac galezie jednym zapytaniem ( delete from kategorie where kategoria=costam ), ale obie tabele musza byc InnoDB i w powiazaniach zalozone klucze obce takie jak podal depesz.

3. Przenoszenie galezijest dosc skomplikowane i chyba szybsze bedzie zalozenie kopii w docelowym miejscu i skasowanie zrodla, ale jesli to nie mozliwe to teraz przychodzi mi do glowy cos takiego:
a) kasujesz wszystkie wiersze z powiazan gdzie first_id jest parentem dla poczatku przenoszonej galezi a second_id miesci sie w przenoszej galezi - w wyniku masz w tabeli dwa drzewa w tym momencie - jedno to glowne i drugie ktore powstalo z przenoszonej galezi
znajdujesz liste parentow w nowym miejscu i wstawiasz powiazania od kazdego nowego parenta do kazdego elementu przenoszonej galezi odpowiednio obliczajac glebokosc.

Uf. Nie wiem czy jest prostsza metoda, ale mam kaszel i goraczke wiec mysli mi sie troche ciezko

[DeyV]

1. Wydaje mi się, że dane podane przezemnie są już poprawne.
2. Coś mój MySQL (4.0.x) ma problem z założeniem kilku kluczy zewnętrznych dla jednej tabeli. A jeden to ...
Tak więc przydałaby się jakaś metoda nie korzystajaca z InnoDB

3. Kombinuję, ale jeszcze nie jestem zadowolony z efektów. Wiem, że są tu tacy, co już to przerobili, więc...

[Jabol]

Tutaj o kasowaniu gałęzi. Po prostu kasujesz wszystko co ma w first_id id kasownej gałęzi!

Mam taką wizje co do przenoszenia gałęzi (ten typ struktury drzewiastej jest chyba najlepszy, aczkolwiek znam pare łatwiejszych, ale mniej wydajnych, gdzie można to zrobić łatwiej).

Kasujesz wszystkie powiązania gałęzi z elementami zewnętrznymi (nie zawierającymi się w gałęzi).
Zmieniasz w powiązaniach id rodzica (first_id?) dla przenoszonej gałęzi (tylko dla tego katalogu, i nizcego więcej).
Tworzysz powiązania dla wyżej wymienionego katalogu (gałęzi).
Teraz robisz taką sztuczke, dla każdego elementu gałęzi robisz powiązanie w postaci:
Sprawdzasz zagłebienie całej gałęzi (przenoszonej) względem każdego elemntu (zewnętrznego), do którego powiązania ma gałąź (wcześniej robiłeś powiązania dla gałęzi).
Sprawdzasz zagłębienie elemntu (wewnątrz gałęzi), dla którego aktualnie robisz powiązanie względem całej gałęzi (tego nie należy kasować, gdyż to się nie zmienia).
Dodajesz powiąznie, gdzie depth to suma dwóch powyższych liczb.
I tak dla każdego elementu!

Jest to może nizbyt wydajne, ale wydaje mi się przy małej pomocy php całkiem proste do zaimplementowania, a poza tym, w końcu gałęzie przenosie się dość żadko!

[DeyV]

Po prostu kasujesz wszystko co ma w first_id id kasownej gałęzi!

A sprawdziłeś dokładnie, jak wygląda ta struktura?
A co z elementami, nie zaczynającymi się od poziomu 'startowego' gałęzi, lecz pokazujące kolejne schody?
przykładowo dla jednego elementu na trzecim poziomie zagłebienie mamy dane:

1, 5, 3
2 , 5, 2
3 , 5, 1
5 , 5, 0

A to wpisy tylko dla jednego elementu.
Jeszcze raz zaznaczam, ze wiem jak to zrobić przy pomocy php - jednak chcę by robił mi to MySQL 4

A przenoszenie jeszcze postudiuję - teraz brak mi sił (ale za to powiem Wam, że Sobota to świetny czas na koncerty, a Sz. Wydra nawet umie śpiewać )

[uboottd]

Bez InnoDB moze byc ciezko.

Kod

CREATE TABLE kategorie (

  id int(11) NOT NULL auto_increment,

  parent int(11),

  nazwa varchar(100) default NULL,

  PRIMARY KEY  (id),

  KEY parent_ind (parent),

  FOREIGN KEY (`parent`) REFERENCES `kategorie` (`id`) ON DELETE CASCADE

) TYPE=InnoDB;



CREATE TABLE powiazania (

  first_id int(11) NOT NULL default '0',

  second_id int(11) NOT NULL default '0',

  depth int(11) NOT NULL default '0',

  PRIMARY KEY  (first_id,second_id),

  KEY f_ind (first_id),

  KEY s_ind (second_id),

  FOREIGN KEY (`second_id`) REFERENCES `test.kategorie` (`id`) ON DELETE CASCADE,

  FOREIGN KEY (`first_id`) REFERENCES `test.kategorie` (`id`) ON DELETE CASCADE

) TYPE=InnoDB;





INSERT INTO kategorie VALUES (1,NULL,'sql');

INSERT INTO kategorie VALUES (2,1,'postgresql');

INSERT INTO kategorie VALUES (3,1,'oracle');

INSERT INTO kategorie VALUES (4,2,'linux');

INSERT INTO kategorie VALUES (5,3,'sco');

INSERT INTO kategorie VALUES (6,3,'linux');

INSERT INTO kategorie VALUES (7,7,'windows');

INSERT INTO kategorie VALUES (8,6,'glibc1');

INSERT INTO kategorie VALUES (9,6,'glibc2');



INSERT INTO powiazania VALUES (1,1,0);

INSERT INTO powiazania VALUES (1,2,1);

INSERT INTO powiazania VALUES (1,3,1);

INSERT INTO powiazania VALUES (1,4,2);

INSERT INTO powiazania VALUES (1,5,2);

INSERT INTO powiazania VALUES (1,6,2);

INSERT INTO powiazania VALUES (1,7,2);

INSERT INTO powiazania VALUES (1,8,3);

INSERT INTO powiazania VALUES (1,9,3);

INSERT INTO powiazania VALUES (2,2,0);

INSERT INTO powiazania VALUES (2,4,1);

INSERT INTO powiazania VALUES (3,3,0);

INSERT INTO powiazania VALUES (3,5,1);

INSERT INTO powiazania VALUES (3,6,1);

INSERT INTO powiazania VALUES (3,7,1);

INSERT INTO powiazania VALUES (3,8,2);

INSERT INTO powiazania VALUES (3,9,2);

INSERT INTO powiazania VALUES (4,4,0);

INSERT INTO powiazania VALUES (5,5,0);

INSERT INTO powiazania VALUES (6,6,0);

INSERT INTO powiazania VALUES (6,8,1);

INSERT INTO powiazania VALUES (6,9,1);

INSERT INTO powiazania VALUES (7,7,0);

INSERT INTO powiazania VALUES (8,8,0);

INSERT INTO powiazania VALUES (9,9,0);

Sprawdzone - dane prawidlowe i struktura tez sie zaklada.

usuwanie z takiej bazy jest proste: delete from kategorie where id = jakis

Tylko zwroc uwage na dodane pole parent - tylko dzieki niemu jest to mozliwe ( a przynajmniej dopoki nie mozna uzywac ORDER BY w zapytaniu DELETE FROM ... USING ...

[DeyV]

Usuwanie - chyba udało mi się wymyślić

Przy pomocy 2 zapytań:
1. usuwamy wpisy z kategorie:

Kod

  

DELETE FROM `kategorie`

    USING `kategorie`

LEFT JOIN `powiazania`

    ON `powiazania`.`second_id` = `kategorie`.`id`

WHERE `powiazania`.`first_id` = '$usuwane_id'

Natomiast problematyczne zapytanie usuwajace powiązania

Kod

DELETE FROM `powiazania`

USING `powiazania`

LEFT JOIN `kategorie`

ON `powiazania`.`second_id` = `kategorie`.`id`

WHERE `kategorie`.`id` IS  NULL

Czyli usuwamy wszystkie powiązania nie mające już swoich odpowiedników.

Co sądzicie o takim rozwiazaniu?

[uboottd]

Na pierwszy i drugi rzut oka powinno dzialac. Jednak tu klucze obce o dziwo beda ci przeszkadzac jakbys chcial je jednak miec.

Dwa zapytania... wziales pod uwage problemy z jednoczesnym wykonaniem tego kodu przez dwoch userow ? Wydaje mi sie ze nie powinno stanowic to problemu w przypadku dwoch kasowan, ale np kasowania i przesuwania chyba moze Ci rozjechac baze - w zaleznosci od tego jak robisz reszte.

[Jabol]

co do dwóch userów, to zawsze mozna zrobic pliki, które jeżeli są, oznaczają aktualnią kasację z bazy, albo np.

Kod

LOCK TABLE

[Jabol]

jak w pgsql'u zastopic konstrukcje KEY, ktora nie dziala (wywoluje blad). Moze by mi sie to udalo zrobic, gdym lepiej zrozumial (jakby ktos wytlmaczyl) do czego jest ta kolumna i z czego sie ona bierze (przeciez nie jest zdefiniowana)?

Ps. Czy naprawde nie lepsza bylaby tabela id, name, parent? Czemu nie (domyslam sie odpowiedzi)?

[uboottd]

Szukaj, Czytaj, a osiągniesz sukces!

KEY => INDEX

[Jabol]

a skąd jest to pole parent, czy to jest tak, że po protu go nie dodałeś do kodu, czy to jakaś sztuczka, bo z tego co ja wiem, to Indeksy robi się na polach.

[uboottd]

O i to juz jest dobre pytanie
Faktycznie zapomnialem/starcilo sie przy myszkowaniu.

[Jabol]

Po dlozszym zastanowieniu doszlem, ze pliki powinny byc przechowywane w innej tabeli ale jako "odnosniki do" tabeli kategorie (tabele przydaloby sie inaczej nazwac). To jednak wymaga dodania tabeli i pola do tabeli kategorie:
type set('d', 'f'); i tabela[sql:1:d9c4d6587e]CREATE TABLE files (
id INT(11) AUTO_INCREMENT REFERENCES kategorie (id) ON DELETE CASCADE,
content LONGTEXT,
PRIMARY KEY (id)
) TYPE=InnoDB;[/sql:1:d9c4d6587e]Teraz takie jeszcze pytanko. Jak zrobic CONSTRAINT'a, zeby sprowdzal, czy typ w tabeli jest ustanowiony na 'f'?

Ps. A tak wogole, to co o tym myslicie?

[chfast]

Wiem, że temat już stary, ale nie chce zakładać nowego...

Jak najlepiej dodawać dane do takiej struktury? Używam kluczy obcych.

Przy okazji:

Coś mój MySQL (4.0.x) ma problem z założeniem kilku kluczy zewnętrznych dla jednej tabeli.

Zrób indexy tych pól w tabeli referencji, które zawierają klucz obcy.

[Jabol]

Jak najlepiej dodawać dane do takiej struktury? Używam kluczy obcych.

Zrób tak:[sql:1:9935eca99f]INSERT INTO categories( name, parent ) VALUES ( nazwa, id_parenta );
-- wybierasz sobie id tego co włożyłeś dajmy na to do id_current
INSERT INTO asociations ( first_id, second_id, depth ) SELECT first_id, id_current, depth + 1 FROM asociations WHERE second_id = id_parenta;
INSERT INTO asociations ( first_id, second_id, depth ) VALUES ( id_current, id_current, 0 );
-- jezeli jest zwykłym plikiem i używasz mojego dodatku
INSERT INTO files ( id, content ) VALUES ( id_current, text_do_dodania );
-- jeżeli nie jest zwykłym plikiem to już gotowe[/sql:1:9935eca99f]oczywiście musisz podstawić odpowiednie wartości za:
nazwa - nazwa nowego pliku/katalogu
id_parent - id ojca w gałęzi
id_current - musisz je wyciągnąć po dodaniu pierwszego wpisu do bazy[sql:1:9935eca99f]SELECT max( id ) FROM categories;[/sql:1:9935eca99f]
text_do_dodania - jeżeli plikiem nie jest plikiem zwykłym to nic, bo nie robisz tego inserta a jeżeli jest to zawartość tego pliku.

jakbyś miał jeszcze jakieś pytania to pisz bo właściwie wszystkie operacje mam już tutaj opracowane.

[chfast]

Super!

Nie znałem tej konstrukcji: [sql:1:33dcca6d0a]INSERT INTO ... SELECT ...[/sql:1:33dcca6d0a] a ona tu jest lepsza niż moja dotychczasowa pętelka w php

Przy okazji: do czego przydaje ci się pole parent w categories?

I jeszcze prosze o propozycje rozwiązań:
1. Przenoszenie gałęzi.
2. Tworzenia mapy całej struktury.

[Jabol]

Przy okazji: do czego przydaje ci się pole parent w categories?

do usuwania, dużo łatwiej, wystarczy dac[sql:1:bed2e80e3d]DELETE FROM categories WHERE id = id_do_usunięcia;[/sql:1:bed2e80e3d]

I jeszcze prosze o propozycje rozwiązań:
1. Przenoszenie gałęzi.
2. Tworzenia mapy całej struktury.

1.No więc jest to bardziej skomplikowane i bez php/innego języka się nie obejdziesz
No więc najpierw zapytanie ustalmy stale:
iddp - id do przesunięcia
idnp - id nowego parenta
Najpierw wywołujesz zapytaine [sql:1:bed2e80e3d]SELECT max( depth ) AS max, second_id FROM asociations WHERE first_id = iddp GROUP BY second_id;[/sql:1:bed2e80e3d] Dla każdego wiersza zwróconego przez to zapytanie robisz cos takiego: [sql:1:bed2e80e3d]DELETE FROM asociations WHERE second_id = wiersz.second_id AND depth > wiersz.max;[/sql:1:bed2e80e3d] To pierwsza część już zrobiona, teraz zmieniamy informacje w categories [sql:1:bed2e80e3d] UPDATE categories SET parent = idnp WHERE id = iddp [/sql:1:bed2e80e3d] i teraz dalej - towrzymy zależności[sql:1:bed2e80e3d]INSERT INTO asociations ( first_id, second_id, depth ) SELECT first_id, iddp, depth + 1 FROM asociations WHERE second_id = idnp; [/sql:1:bed2e80e3d] i teraz taki numer jak wyżej - dla każdego z poniższego zapytania [sql:1:bed2e80e3d] SELECT second_id, depth FROM asociations WHERE first_id = iddp AND depth <> 0; [/sql:1:bed2e80e3d] wywołujesz coś takiego ( zakładając, że powyższe zapytanie zwróciło zmienną 'wiersz' ) [sql:1:bed2e80e3d] INSERT INTO asociations ( first_id, second_id, depth ) SELECT first_id, wiersz.second_id, depth + wiersz.depth FROM asociations WHERE second_id = iddp AND depth <> 0; [/sql:1:bed2e80e3d] mam nadzieję, że rozumiesz
jeżeli chcesz zobaczyć przykłąd to gdzieś podawałem przykład w plpgsql'u - języku bardzo łatwym do zrozumienia.
2. nie rozumiem za bardzo o czym mówisz

[chfast]

Jeszcze odnośnie dodawania nowego pliku.
Wykonuje zapytanie: [sql:1:7bb2f5d84d]INSERT INTO file_ref (anc_id, desc_id, depth) SELECT anc_id, 4, depth+1 FROM file_ref WHERE desc_id = 3[/sql:1:7bb2f5d84d]
i otrzymuje błąd:

Kod

Error 1066: Not unique table/alias: 'file_ref'.

Dlaczego?

A przy okazji..
Maksymalna liczba rekordów w tablicy referencji przy n plikach wynosi:

Kod

l(n) = n(n-1)/2  (bez depth=0)

l(n) = n(n+1)/2  (z depth=0)

[Jabol]

Jeszcze odnośnie dodawania nowego pliku.
Wykonuje zapytanie: [sql:1:2465817b6a]INSERT INTO file_ref (anc_id, desc_id, depth) SELECT anc_id, 4, depth+1 FROM file_ref WHERE desc_id = 3[/sql:1:2465817b6a]
i otrzymuje błąd:

Kod

Error 1066: Not unique table/alias: 'file_ref'.

A co to jest za tabela ? - pamiętaj - jeżeli robisz własne usprawnienia to podawaj jakie bo przecież w głowie Ci nie siedzimy.