Forum PHP.pl > Drzewka (for BSP)

Pomoc - Szukaj - Użytkownicy - Kalendarz

Forum PHP.pl > Forum > Bazy danych > MySQL

DeyV

30.05.2003, 11:45:07

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

Cytat

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

30.05.2003, 22:44:52

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

31.05.2003, 13:45:09

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

31.05.2003, 15:38:52

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

31.05.2003, 22:37:28

Cytat

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:

Cytat

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

2.06.2003, 16:51:22

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

2.06.2003, 20:47:42

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

3.06.2003, 10:36:07

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

3.06.2003, 14:45:02

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

10.07.2003, 16:45:10

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

10.07.2003, 18:00:38

Cytat

Szukaj, Czytaj, a osiągniesz sukces!

KEY => INDEX

Jabol

10.07.2003, 18:02:07

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

10.07.2003, 18:05:11

O i to juz jest dobre pytanie

Faktycznie zapomnialem/starcilo sie przy myszkowaniu.

Jabol

12.07.2003, 19:58:54

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

2.11.2003, 00:10:12

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

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

Przy okazji:

Cytat

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

2.11.2003, 08:47:43

Cytat

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

2.11.2003, 10:27:45

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

2.11.2003, 11:04:14

Cytat

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]

Cytat

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

2.11.2003, 12:24:16

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

2.11.2003, 12:34:27

Cytat

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.

chfast

2.11.2003, 17:03:31

Cytat

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.

Używam tylko innych nazw:
file_ref - tabela z referencjami
file_name - tabela z nazwami plików
anc_id - id przodka (first_id)
desc_id - id potomka (second_id)

Jabol

2.11.2003, 18:05:55

ups... nie korzystasz przypadkiem z jakiejś wersji mysql < 4.0.15 ?

chfast

2.11.2003, 18:53:40

Cytat

ups... nie korzystasz przypadkiem z jakiejś wersji mysql < 4.0.15 ?

Wręcz przeciwnie: 4.1 (bo potrzebowałem podzapytań). Ale może problem tkwi w tym, że to wersja alpha. Albo tabela musi mieć jakieś specjalne właściwości.
Zaczyna mnie to powoli denerwować. Człowiek nie może spokojnie pracować, bo ciągle wyskakują jakieś problemy związane z wersją oprogramowania. :?

Jabol

2.11.2003, 18:59:45

Cytat

maybe http://postgresql.org ? ja osobiście nawet tego wszystkiego na mysqlu nie testowałem...

chfast

2.11.2003, 19:13:20

Cytat

maybe http://postgresql.org ?

Chętnie. Ale mam problemy z instalacją pod windowsem. Nie chce mi sie postmaster uruchomić. Pisze ze brak bazy "chfast". Jednocześnie wcześniej uruchomienie initdb zwraca informacje o poprawnym utworzeniu bazy... nie wiem o co w tym wszystkim chodzi.

Wracając jeszcze do wątku dodawania plików:

Cytat

napisz triggera który w momencie wstawienia jednego rekordu do tabeli
z powiazaniami sam wstawi reszte.

Ja osobiście nie mam pojęcia o co chodzi (bo nie mam posrgresa), ale może tobie coś pomoże.

Jabol

2.11.2003, 19:20:32

Cytat

maybe http://postgresql.org ?

tutaj mogę Ci pomóc. initdb inicjalizuje bazę danych jako taką, a do utworzenia właściwej bazy dajesz `createdb chfast`. Oczywiście najpierw musisz zainicjalizować `initdb`, potem uruchomić `postmaster -D /directory > logfile 2>&1 &` ( nie wiem jak to na windzie działa )
i dopiero potem `createdb` - potem już można działać

chfast

2.11.2003, 21:40:25

Sorry za off-topic, ale robie tak:

initdb c:/usr/pgsql/data
postmaster -D c:/usr/pgsql/data

I przy poleceniu postmaster otrzymuje błąd:

Kod

FATAL 1: Database "chfast" does not exists in the system catalog.

PS. Do czego służą post_svc i pg_ctl ?

chfast

3.11.2003, 16:03:36

Cytat

2. nie rozumiem za bardzo o czym mówisz

Chodzi mi o wyświetlenie całej struktury w postaci drzewka. Można to potem wykorzystać do tworzenia mapy serwisu. Pewnie przydałoby się również przy debugowaniu. Ale zostawiam to sobie na koniec...

Jabol

3.11.2003, 17:03:23

co do pierwszego to może spróbuj najpierw spróbuj stworzyć bazę a dopiero potem uruchomić postamstera.
pg_ctl służy do zarządzania bazą - np pg_ctl -d c:/ble/ble/ble {stop|start}
to drugie to nie wiem

co do drugiego to nie wystarczy Ci np. coś takiego[sql:1:ce9e95749b]create view struct as select get_path( id ), id from categories;[/sql:1:ce9e95749b]to działa niestety tylko na postgresqlu a funkcja get_path jest napisana w plpgsql'u...

GeoS

3.11.2003, 19:41:13

Ja ze swojej strony dorzuce jedno pytanie: czy widzial ktos drzewko o dowolnej ilosci galezi danego poziomu, ale z ich hierarchizacja?

Chodzi mi o to, ze uzytkownik moze ustalac kolejnosc galezi na danym poziomie.

Bede wdzieczny za jakiekolwiek linki, bo do podobnych materialow nie dotarlem.

chfast

3.11.2003, 23:25:39

Cytat

id_current - musisz je wyciągnąć po dodaniu pierwszego wpisu do bazy[sql:1:9d201d6da3]SELECT max( id ) FROM categories;[/sql:1:9d201d6da3]

Zamiast tego lepiej użyć:
1. Funkcji LAST_INSERT_ID() (istnieje w MySQL, pewnie w pgSQL też) lub
2. Funkcji mysql_insert_id() (php).

To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.