Ostatnimi czasy potrzebowałem danych z sąsiedniej tabeli przy UPDATE jedngo z pól w bazie danych. Danych do przetworzenia było sporo, więc zwracałem uwagę na wydajność zapytania. Aby zebrać potrzebne informacje, można użyć jednego ze sposobów:

1. Zebrać potrzebne dane za pomocą SELECT‘a, co sprawiłoby, że zajęta zostanie niepotrzebna pamięć w środowisku PHP podczas przypisania rezultatu do zmiennej.
2. Wykonać SET z podzapytaniem, ale potrzebnych mi było kilka kolumn z sąsiedniej tabeli, podzapytanie może zwrócić tylko jedną określoną wartość.
3. Wykonać JOIN przy update, czego niestety wówczas nie potrafiłem zrobić.

Kartkując manual nie natrafiłem się w standardowej dokumentacji na nic konkretnego, aż nie spojrzałem na bardzo przydatne komentarze użytkowników. Okazało się, że przy UPDATE można wykonywać dowolne JOIN‘y, schemat jest następujący:

UPDATE table JOIN another_table SET ...

W tym momencie mamy do dyspozycji wszystkie pola z dołączonej tabeli. Bardzo przydatne.

Przykład z życia.

Miałem za zadanie odznaczyć typy bukmacherskie na trafione, nietrafione, odwołane z przyczyn odwołania całego meczu piłkarskiego oraz te, które jeszcze nie mogą zostać oznaczone jako trafione lub nie, gdyż mecz się jeszcze nie odbył.

UPDATE typer_tickets_items LEFT JOIN typer_events ON(event_id = item_event) SET item_status = ( CASE WHEN event_status IS NULL THEN NULL # mecz nie zostal rozegrany WHEN event_status = -1 THEN -1 # mecz anulowany WHEN event_status = item_bet THEN 1 # typ trafiony ELSE 0 END # typ nietrafiony ) WHERE item_status IS NULL

Mam nadzieję, że komuś się przyda…

Pomagałem dzisiaj na IRCnet (#php.pl) w zapytaniu do bazy MySQL, które polegało na wybraniu rekordów z najmniejszą ceną pogrupowanych według wybranej kolumny. Bardzo szybko napisałem jak to ma wyglądać w PostgreSQL, ale niestety w MySQL użycie DISTINCT jest trochę ułomne. Zacznijmy...

W aplikacjach internetowych czasami musimy wykorzystać w jednym serwisie wiele baz danych, nieraz nawet wiele różnych baz danych. Mój wpis będzie prezentował proste przykłady użycia wielu baz z wykorzystaniem frameworka Symfony. Dla przykładu użyje bazy danych PostgreSQL oraz ORM Propela 1.3.

W przykładzie wykorzystam 2 tabele: “users” z bazy “db1″ (załóżmy, że to główna bazy serwisu z użytkownikami) i “requests” z bazy “db2″ (baza w której zapisywane są statystyki z żądań do aplikacji). Modele wygenerowane będą dla bazy PostgreSQL. W kolejnym etapie zaprezentuje w jaki sposób można załadować przykładowe dane pochodzące z data/fixtures.

Definiowanie baz w pliku databases.yml

Edytujemy config/databases.yml

all:
  propel:
    class:        sfPropelDatabase
    param:
      classname:  PropelPDO
      dsn:        pgsql:dbname=db1;host=panic user=db1 password=db1
      hostspec:   pgsql
      port:       5432
      encoding:   utf8

  stat:
    class:        sfPropelDatabase
    param:
      classname:  PropelPDO
      dsn:        pgsql:dbname=db2;host=panic user=db2 password=db2
      hostspec:   pgsql
      port:       5432
      encoding:   utf8

Pierwszym krokiem jest zdefiniowanie połączeń do baz danych. Główną nazwę połączenia będzie “propel” a drugą “stat”

Ustawienie pliku propel.ini

propel.database        = pgsql
propel.database.driver = pgsql
propel.database.url    = pgsql:dbname=db1;host=panic user=db1 password=db1

W pliku propel.ini ustawiamy połączenie z główną bazę danych. “propel.database” odpowiada za generowanie kodu SQL dla danych baz, np.: MySQL, PostgreSQL, Oracle. Jeśli chcemy generować SQL dla wybranego typu bazy, to musimy zmienić na odpowiednią wartość. Można to uzyskać z linii koment ustawiajac –phing-arg

Przykład dla MySQLa:

./symfony propel:build-all --no-confirmation --connection=stat --phing-arg="Dpropel.database=mysql"

Przykład dla Oracle:

./symfony propel:build-all --no-confirmation --connection=stat --phing-arg="Dpropel.database=oracle"

Definiowanie tabel w config/schema.yml

Jak w przykładzie z optymalizacją Propela dobrym nawykiem jest rozdzielenie różnego typu struktur na osobne pliki schema.yml. Główną definicja tabel można zapisać w pliku schema.yml a dodatkową strukturę bazy w osobnym pliku, np w pliku stat_schema.yml.

Plik config/schema.yml

propel:
  users:
    id:              { type: integer, required: true, primaryKey: true, autoincrement: true }
    created_at:      { type: timestamp }
    name:            { type: varchar, size: 255 }
    surname:         { type: varchar, size: 255 }

Plik config/stat_schema.yml

Czytaj dalej tutaj (rozwija treść wpisu)
Czytaj dalej na blogu autora...

Zwiń
Czytaj na blogu autora...

Czasami przy próbach zapisu danych do tabeli z referencjami wyskakuje wyjątek o niewiele mówiącej treści: “Cannot refresh row as parent is missing“. Ustalenie faktycznej przyczyny jest już trochę cięższe, ale da się to zrobić stosując lekką sztuczkę.

Sam wyjątek wygląda lakonicznie i nie dostarcza ciekawych danych:

Fatal error: Uncaught exception 'Zend_Db_Table_Row_Exception' with
message 'Cannot refresh row as parent is missing' in
E:\HTDOCS\recepcja\library\Zend\Db\Table\Row\Abstract.php:695
Stack trace:
#0 E:\HTDOCS\recepcja\library\Zend\Db\Table\Row\Abstract.php(429): Zend_Db_Table_Row_Abstract->_refresh()
#1 E:\HTDOCS\recepcja\library\Zend\Db\Table\Row\Abstract.php(372): Zend_Db_Table_Row_Abstract->_doInsert()
#2 E:\HTDOCS\recepcja\application\messages\controllers\IndexController.php(60): Zend_Db_Table_Row_Abstract->save()
#3 E:\HTDOCS\recepcja\library\Zend\Controller\Action.php(502): Messages_IndexController->addAction()
#4 E:\HTDOCS\recepcja\library\Zend\Controller\Dispatcher\Standard.php(293): Zend_Controller_Action->dispatch('addAction')
#5
E:\HTDOCS\recepcja\library\Zend\Controller\Front.php(914):
Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http),
Object(Zend_Controller_Response_Http))
#6 E:\HTDOCS\recepcja\public_html\index.php(153): Zend_Controller_Front->dispatch()
#7 {main} thrown in E:\HTDOCS\recepcja\library\Zend\Db\Table\Row\Abstract.php on line 695

Aby ustalić przyczynę musimy zagłębić się w wyrzucany wyjątek i odszukać w Stack trace miejsce w NASZYM kodzie gdzie ten wyjątek się zaczyna. Na powyższym przykładzie jest to #2

#2 E:\HTDOCS\recepcja\application\messages\controllers\IndexController.php(60): Zend_Db_Table_Row_Abstract->save()

Teraz musimy zajrzeć w ten kod i opatulić go własnym try/catch:

try {
     $someRow->save();
} catch ( Exception $e ) {
     echo '<pre>Data:      '; print_r( $someRow ); echo '</pre>';
     echo '<pre>Exception: '; print_r( $e ); echo '</pre>';
     exit();
}

Teraz zobaczymy znacznie więcej informacji:

Data:      Zend_Db_Table_Row Object
(
    [_data:protected] => Array
        (
            [patient_id] => Zend_Db_Statement_Exception Object
                (
                    [message:protected] => SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '34415-1' for key 2
---CUT---

I jak na dłoni widać faktyczną przyczynę wyjątku:  Integrity constraint violation: 1062 Duplicate entry ‘34415-1′ for key 2. A skoro wiemy gdzie jest problem, to możemy też jakoś mu zaradzić.

Podobnie jak w PHP, baza danych MySQL ma odpowiednik if, czyli przypadków (inaczej serii warunków, instrukcji warunkowych). Różnicą między implementacją CASE‘a w MySQL i ifa PHP jest to, że baza danych zwraca konkretną wartość z case’a, a nie wykonuje dowolnej ilości dowolnych akcji.

CASE Syntax:

Najprostsza struktura CASE’aprzedstawia się nastepująco:

CASE WHEN [conditions] THEN … ELSE … END

Składnia powinna rozpocząć się słowem kluczowym CASE, a zakończyć END. Pomiędzy znajdują się warunki WHEN oraz operacja zwrócenia odpowiedniej wartości, która po nich następuje THEN (mamy możliwość uwzględnić nieskończenie wiele warunków). Jeżeli żaden warunek nie zostanie spełniony możemy użyć opcjonalnie ELSE.

Przykłady z życia.

Wyobraźmy sobie, że mamy posortować listę aukcji przedmiotów na Allegro od najtańszych, do najdroższych. Należy założyć, że są 2 typy aukcji: kup teraz i licytacja. Pole licytacji w bazie danych zawiera największą zaproponowaną kwotę przez użytkowników w procesie licytacji, a cena kup teraz ustalana jest przez sprzedającego. Są to dwa różne pola w bazie danych, a jedno kryterium sortowania, dlatego trzeba scalić cenę w jedną, wybierając odpowiednią. Musimy przewidzieć sytuację, w której aukcja jest typu kup teraz oraz licytacji, wówczas jeżeli najwyższa oferta jest większa od ceny kup teraz, wówczas wybieramy pole z największa propozycją:

SELECT (CASE WHEN (auction_type = 'bidding' OR auction_price_bid > auction_price_buynow) THEN auction_price_bid ELSE auction_price_buynow END) AS auction_price

Stworzyliśmy pole auction_price, po którym można sortować aukcje od najtańszej do najdroższej i na odwrót.

Mam nadzieję, że krótki wpis przyda się początkującym. Nic więcej nie trzeba opisywać, temat wydaje się co najmniej trywialny.

Czytaj dalej tutaj (rozwija treść wpisu)
Czytaj dalej na blogu autora...

Zwiń
Czytaj na blogu autora...

Ostatnio zaprezentowałem MySQLową wersję implementacji drzewa tzw. metodą Depesza (patrz tutaj). Nie napisałem natomiast nic na temat praktycznego wykorzystania takiego drzewa. Dlatego dzisiaj ciąg dalszy, tym razem już bardziej praktyczny.

Aby wygodnie operować na naszej bazie, a zwłaszcza znacznie ułatwić sobie pobieranie list kategorii gotowych do wyświetlenia, potrzebujemy jeszcze 2 funkcji:

1 - ścieżka kategorii - Potrzebna nam do przygotowania posortowanej listy kategorii:

DROP FUNCTION IF EXISTS treepath;
CREATE FUNCTION treepath( in_leaf_id INTEGER ) RETURNS TEXT
BEGIN
	--
	-- Funckcja zwracająca ścieżkę kategorii dla zadanego ID kategorii
	--
	DECLARE reply TEXT;
	DECLARE t_name VARCHAR(127);

	-- deklarujemy kursor (zakręcona implementacja pgsqlowego FOR row IN query LOOP)
	DECLARE done INT DEFAULT 0;
	DECLARE cur1 CURSOR FOR
		SELECT tree.name FROM tree INNER JOIN tree_pos ON tree.id = tree_pos.parent_id WHERE tree_pos.tree_id =  in_leaf_id ORDER BY tree_pos.depth DESC;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

	OPEN cur1;
		REPEAT
			FETCH cur1 INTO t_name;
			IF NOT done THEN
					SET reply = CONCAT_WS( '/', reply, t_name );
			END IF;
		UNTIL done END REPEAT;
	CLOSE cur1;

	SET reply = CONCAT( '/', reply );

    RETURN reply;
END;

Przykład działania:

mysql> select treepath(8);
+-------------------------------------------+
| treepath(8)                               |
+-------------------------------------------+
| /Kategoria główna/Kat 1/Kat 1-2/Kat 1-2-1|
+-------------------------------------------+
1 row in set

2 - poziom zagnieżdżenia wybranej kategorii

DROP FUNCTION IF EXISTS item_depth;
CREATE FUNCTION item_depth( in_item_id INTEGER) RETURNS INTEGER
BEGIN
	--
	-- Funkcja zwraca poziom zagnieżdżenia dla zadanego ID kategorii
	--
	DECLARE reply INTEGER;

	SELECT depth INTO reply FROM tree_pos WHERE tree_id = in_item_id ORDER BY depth DESC LIMIT 1;

    RETURN reply;
END;

Ta natomiast banalna i pozornie niepotrzebna funkcja znacznie ułatwi nam zbudowanie podstawowego zapytania:

mysql> SELECT *, item_depth( id ) AS depth FROM tree ORDER BY treepath(id) ASC;
+----+-----------+------------------+-------+
| id | parent_id | name             | depth |
+----+-----------+------------------+-------+
|  1 | NULL      | Kategoria główna |     0 |
|  2 |         1 | Kat 1            |     1 |
|  4 |         2 | Kat 1-1          |     2 |
|  7 |         4 | Kat 1-1-1        |     3 |
|  9 |         4 | Kat 1-1-2        |     3 |
|  5 |         2 | Kat 1-2          |     2 |
|  8 |         5 | Kat 1-2-1        |     3 |
|  3 |         1 | Kat 2            |     1 |
|  6 |         3 | Kat 2-1          |     2 |
+----+-----------+------------------+-------+
9 rows in set

lub idąc dalej:

mysql> SELECT id, CONCAT( REPEAT('  ', item_depth( id ) ), '+- ', name) FROM tree ORDER BY treepath(id) ASC;
+----+-------------------------------------------------------+
| id | CONCAT( REPEAT('  ', item_depth( id ) ), '+- ', name) |
+----+-------------------------------------------------------+
|  1 | +- Kategoria główna                                 |
|  2 |   +- Kat 1                                            |
|  4 |     +- Kat 1-1                                        |
|  7 |       +- Kat 1-1-1                                    |
|  9 |       +- Kat 1-1-2                                    |
|  5 |     +- Kat 1-2                                        |
|  8 |       +- Kat 1-2-1                                    |
|  3 |   +- Kat 2                                            |
|  6 |     +- Kat 2-1                                        |
+----+-------------------------------------------------------+
9 rows in set

Z tak przygotowanymi danymi już chyba każdy sobie poradzi z ich wyświetlaniem.

Garść innych ciekawych informacji (jak np. modyfikacja kolejności) znajdziecie na stronie http://www.depesz.com/various/various-sqltrees-implementation.php z której czerpałem inspiracje.

Uzupełniony zrzut z testowej bazy łącznie z przykładowymi wpisami można znaleźć tutaj: tree_v2.sql. Natomiast sam opis struktury i triggerów znajdziecie tutaj.

Czytaj dalej tutaj (rozwija treść wpisu)
Czytaj dalej na blogu autora...

Zwiń
Czytaj na blogu autora...

W czasie pracy nad jednym z projektów po raz kolejny byłem zmuszony podejść do kwestii drzewa. Jednakże tym razem postanowiłem podejść do tego troszkę inaczej i zaimplementować drzewo bardziej rozbudowane niż typowe id | nazwa | parent_id. Wybór padł na tzw. metodę Depesza oraz próbę jej implementacji pod MySQL.

Nie będę się tutaj rozwodził nad zasadą działania czy listą wad/zalet gdyż zostało to już dość dobrze opisane:

• http://www.depesz.com/various/various-sqltrees.php (metoda 5)
• http://www.depesz.com/index.php/2007/07/02/drzewa-w-sqlu-metoda-pelnych-sciezek-metoda-nr-5-wg-starego-tekstu/
• http://www.scms.net.pl/tree/metoda_4.html

Poszukiwania “gotowca” dla MySQL nie przyniosły skutku. Jedyne co znajdywałem to rozwiązania dla PostgreSQL i ewentualnie prośby o gotowca dla MySQL. Tak więc ambicjonalnie (bez żadnego wcześniejszego doświadczenia w programowaniu baz danych) postanowiłem napisać (tudzież przepisać) coś takiego.

Diagram tabel i relacji

W czasie pracy/nauki bazowałem na wersji pod PostgreSQL napisanej przez Michała Szych - Implementacja metody 4. Dla uzupełnienia tutaj znajdziecie działający i sprawdzony zrzut z bazy danych (w kilku miejscach jest inny niż ten zaprezentowany na stronie).

Całość rozwiązania bazuje na kluczach obcych oraz triggerach przy dodawaniu i modyfikacji kategorii (diagram po prawej).

Klucze obce służą do kaskadowego usuwania kategorii - usuwając pojedyńczy rekord automatycznie usuwane są wpisy powiązań jak też ewentualni potomkowie (relacja wewnętrzna parent_id -> id).

Najważniejsze jednak zawarte jest w wyzwalaczach.

-- ----------------------------
-- Trigger structure for tree_insert
-- ----------------------------
DELIMITER ;;
CREATE TRIGGER `tree_insert` AFTER INSERT ON `tree` FOR EACH ROW BEGIN

INSERT INTO tree_pos (tree_id, parent_id, depth) VALUES (NEW.id, NEW.id, 0);

IF NEW.parent_id IS NOT NULL THEN
INSERT INTO tree_pos (tree_id, parent_id, depth)
SELECT NEW.id, parent_id, depth + 1 FROM tree_pos WHERE tree_id = NEW.parent_id;
END IF;

END;;
DELIMITER ;

Odpowiada on za stworzenie wszystkich połączeń przy dodawaniu nowej kategorii.  Jest on na tyle prosty że chyba nie trzeba tego tłumaczyć.

Drugi wyzwalacz - ON UPDATE jest już natomiast znacznie bardziej skomplikowany. Odpowiada on bowiem za przebudowywanie listy połączeń przy przenoszeniu kategorii. Sam trigger poniżej, w komentarzach znajduje się także wyjaśnienie co i dlaczego.

Czytaj dalej tutaj (rozwija treść wpisu)
Czytaj dalej na blogu autora...

Zwiń
Czytaj na blogu autora...