UPSERT wielu wierszy w MySQL

Dzisiaj zacznę odwrotnie niż zwykle czyli od prezentacji gotowego rozwiązania:

INSERT INTO user_counters (user_id, counter_type, counter_value, updated_at)
VALUES
    (:user_id1, :counter_type1, :counter_value1, :updated_at1),
    (:user_id2, :counter_type2, :counter_value2, :updated_at2),
    # ...
    (:user_idN, :counter_typeN, :counter_valueN, :updated_atN)
ON DUPLICATE KEY UPDATE
    counter_value = counter_value + VALUES(counter_value),
    updated_at = VALUES(updated_at);

To tak zwany UPSERT czyli zapytanie, które wstawia rekord(y) do bazy, a jeśli taki już istnieje (tzn. istnieje już taki klucz główny lub unikalny) to go zaktualizuje (wartość w kolumnie counter_value zostanie powiększona o nową wartość, a w kolumnie updated_at zastąpiona). To zapytanie jest jednak trochę lepsze od tych, które zazwyczaj podaje się jako przykład UPSERTA, ponieważ pozwala zapisać wiele (dziesięć, sto, tysiąc) wierszy za jednym zamachem. Sprawia to użyta w części UPDATE funkcja VALUES(), która pobiera wartości ze wskazanej kolumny w części INSERT.

Po co się w ogóle tak babrać w SQL-u jak zwierzę skoro Doctrine mógłby to zrobić za nas? Akurat tego nie zrobi. Przy użyciu Doctrine należałoby:

  1. Pobrać wszystkie pasujące rekordy (jeden SELECT).
  2. Przeiterować przez nie i:
    • zaktualizować wartości,
    • zidentyfikować brakujące rekordy.
  3. Utworzyć encje brakujących rekordów.
  4. Opróżnić bufor managera encji (flush, który zapewne wykona jeden INSERT i jeden UPDATE).

Jak widać zarówno po stronie aplikacji (PHP) jak i bazy to wyraźnie więcej pracy, która i tak pewnie poszłaby na marne, bo przy kilku pracujących jednocześnie instancjach aplikacji jest spora szansa na wyjątek UniqueConstraintViolationException, który zamknie managera encji.

UPSERT z wieloma wartościami rozwiązuje problem wydajności i problem naruszania ograniczeń, ale tworzy klikna innych:

  • Przy jednoczesnych instancjach aplikacji łatwo o deadlocki na bazie. Trzeba zastosować jakiś mechanizm blokowania.
  • Jeśli w ten sposób zapisujemy dane z encji Doctrine’a to ich stan może być nieaktualny.

INSERT … ON DUPLICATE KEY UPDATE nie jest częścią standardu SQL, to rozszerzenie dostępne w MySQL. W PostgrSQL jest podobny mechanizm, ale jego działanie jest nieco inne, więc może opiszę je przy innej okazji.

Wyłączanie filtrów w Doctrine2

Filtry w Doctrine to poniekąd pożyteczna funkcja, na filtrach jest oparte m.in miękkie kasowanie (ang. soft delete), które można podpiąć z paczki gedmo/doctrine-extensions. Filtry przeważnie są włączane na stałe w konfiguracji ORM-a w pliku config.yml, ale w razie potrzeby można je włączać i wyłączać w kodzie. Obiekt FilterCollection pobieramy z entity managera, a na nim można wykonać m.in. poniższe metody:

// sprawdzenie czy filtr jest zainstalowany
$em->getFilters()->has("soft-deleteable");

// sprawdzenie czy filtr jest włączony
$em->getFilters()->isEnabled("soft-deleteable");

// włączenie filtra
$em->getFilters()->enable("soft-deleteable");

// wyłączenie filtra
$em->getFilters()->disable("soft-deleteable");

Cała ta notka wzięła się stąd, że dłużej niż powinienem szukałem błędu w kodzie, a jego źródłem było właśnie miękkie kasowanie. Zapytanie, które powinno zwracać encję zwracało NULL, a taki przypadek nie był obsłużony w kodzie.

WHERE warunek vs LEFT JOIN warunek

Być może nie powinienem publicznie przyznawać się, że popełniłem tak podstawowy błąd w konstruowaniu zapytania SQL, ale po jego odkryciu szczerze się uśmiałem, a skoro śmiech to zdrowie to się nim podzielę.

W uproszczeniu: mam tabelkę z danymi klientów (client) i tabelkę produktów (product), pomiędzy którymi istnieje relacja jeden do wielu. Wyciąganie danych do pokazania jest zrealizowane w Doctrine w następujący sposób:

$qb
    ->select('customer, product')
    ->from($this->getEntityName(), 'customer')
    ->leftJoin('customer.products', 'product')
    ->where('customer.identifier = :identifier')
    ->setParameter('identifier', $identifier);

Dostałem za zadanie zmienić wyświetlanie danych w ten sposób by pokazywać jedynie aktywne produkty. Zrobiłem to tak:

$qb
    ->select('customer, product')
    ->from($this->getEntityName(), 'customer')
    ->leftJoin('customer.products', 'product')
    ->where('customer.identifier = :identifier')
    ->andWhere('product.isActive = 1')
    ->setParameter('identifier', $identifier);

Ku memu zdziwieniu w wypadku klienta, który ma tylko jeden nieaktywny produkt wynik zapytania był pusty. No jakże to?! Przecież klient jak najbardziej istnieje, a ponieważ nie ma produktów spełniających kryteria wyszukiwania (isActive = 1) to w miejscu danych produktu powinny być NULLe.

Swój błąd zrozumiałem dopiero, kiedy spojrzałem na generowane przez Doctrine zapytanie SQL. W uproszczeniu wygląda ono tak:

SELECT 
  c.name,
  p.product_type
FROM 
  customer c 
  LEFT JOIN product p ON c.id = p.customer_id 
WHERE 
  c.identifier = '65092900214'
  AND p.is_active = 1

Ponieważ warunek p.is_active = 1 sprawdzamy w części WHERE zapytania to eliminujemy z zapytania wszystkie wiersze, w których on nie jest spełniony. Zatem jeśli klient ma tylko jeden nieaktywny produkt to wynik zapytania będzie pusty.

Oczekiwany efekt (wiersz z NULLami w miejscu danych produktu) uzyskamy przenosząc warunek do części LEFT JOIN zapytania:

SELECT 
  c.name,
  p.product_type
FROM customer c
  LEFT JOIN product p ON c.id = p.customer_id 
  AND p.is_active = 1
WHERE 
  c.identifier = '65092900214'

Ponieważ warunek dotyczy złączenia to jeśli nie zostanie on spełniony to złączenie nie zostanie wykonane, ale dane klienta zostaną zwrócone. Dokładnie tak jak chciałem.

W Doctrine poprawne zapytanie należy zbudować tak:

$qb
    ->select('customer, product')
    ->from($this->getEntityName(), 'customer')
    ->leftJoin('customer.products', 'product', 'WITH', 'product.isActive = 1')
    ->where('customer.identifier = :identifier')
    ->setParameter('identifier', $identifier);

Checkbox kontra NOT NULL

W encji mamy pole legal1 typu boolean, które nie może być NULL-em.

    /**
     * @var boolean
     *
     * @ORM\Column(name="legal1", type="boolean", columnDefinition="TINYINT(1)")
     */
    private $legal1;

W formularzu pole jest prezentowane jako checkbox. Niby wszystko powinno działać, a jednak jeśli wyślemy formularz bez zaznaczania checkboxa to na encji nie jest wywoływana metoda setLegal1(), a w rezultacie Doctrine próbuje zapisać NULL jako wartość pola legal1, co oczywiście kończy się wyjątkiem.

Można kombinować na samej encji (np. zwracać z getLegal1() 0 jeśli wartość to NULL), ale chyba najbardziej eleganckim rozwiązaniem będzie zastosowanie transformera, który będzie tłumaczył wartości pomiędzy encją a formularzem. Prościutki transformer realizujący takie zadanie może wyglądać tak.

namespace MDurys\CommonBundle\Lib\DataTransformer;

use Symfony\Component\Form\DataTransformerInterface;

class BoolTransformer implements DataTransformerInterface
{
    public function transform($input)
    {
        return boolval($input);
    }

    public function reverseTransform($input)
    {
        return intval($input);
    }
}

W formularzu należy go zastosować jako model transfomer:

    $builder
        ->add(
            $builder->create('legal1', 'checkbox')->addModelTransformer(new BoolTransformer())
            )

Jego działanie polega na tym, że podczas tworzenia formularza zostanie wywołana metoda transform(), która wartość z bazy danych (0 lub 1) rzutuje na wartość boolowską. Z kolei podczas zapisywania formularza zostanie wywołana metoda reverseTransform(), która rzutuje wartość z formularza na liczbę całkowitą. Tym sposobem z NULL-a zrobi się 0.

Klucz obcy jako klucz główny w Doctrine2

Jest to jak najbardziej możliwe i bardzo ładnie opisane w dokumentacji Doctrine2. Przykład poniżej.

namespace Acme\Bundle\ShopBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="customer_preferences")
 */
class CustomerPreferences
{
    /**
     * @ORM\Id
     * @ORM\OneToOne(targetEntity="Acme\Bundle\ShopBundle\Entity\Customer")
     * @ORM\JoinColumn(name="customer_id", referencedColumnName="id")
     */
    private $customer;

    // ...
}

W tabeli wygenerowanej z tej encji pole customer_id będzie zarówno kluczem obcym wskazującym na pole id w tabeli customer jak i kluczem głównym.

Z jednej strony wydaje się to oczywiste i intuicyjne, ale parę razy przekonałem się, że rozwiązania w Doctrine2 nie zawsze są oczywiste i intuicyjne. Na szczęście akurat w tym wypadku są.

Zapisywanie osadzonych formularzy w Symfony

Zapisywanie osadzonych formularzy w Symfony działa inaczej niż intuicyjnie zakładałem. Co ciekawsze, już raz się z tym kiedyś zetknąłem, ale było to na tyle dawno, że zapomniałem rozwiązania.

Załóżmy, że w zwykłym formularzu dziedziczącym z klasy sfForm osadzam formularz oparty na modelu z bazy danych czyli dziedziczący z sfFormDoctrine.

class OnePageCheckoutForm extends sfForm
{
  public function configure()
  {
    $this->widgetSchema['same_address'] = new sfWidgetFormInputCheckbox();
    $this->validatorSchema['same_address'] = new sfValidatorBoolean();

    $this->embedForm('customer', new CustomerForm());
    $this->embedForm('shipment_address', new AddressForm());
    $this->getWidgetSchema()->setNameFormat('checkout_form[%s]');
  }
}

W kontrolerze chcę zapisać dane z osadzonych formularzy, ale oczywiście klasa OnePageCheckoutForm nie ma metody save(). W pierwszym odruchu napisałem mniej więcej coś takiego:

public function executeCheckout(sfWebRequest $request)
{
  if (!$this->getUser()->hasCart())
  {
    return $this->redirect('@cart');
  }
  $this->cart = $this->getUser()->getCart();

  $this->form = new OnePageCheckoutForm();
  if ($request->getMethod() == 'POST')
  {
    $this->form->bind($request->getParameter($this->form->getName()), $request->getFiles($this->form->getName()));
    if ($this->form->isValid())
    {
      $values = $this->form->getValues();
      $this->form->getEmbeddedForm('customer')->save();
      $this->form->getEmbeddedForm('shipment_address')->save();

      $this->cart->setCustomerId($this->form->getEmbeddedForm('customer')->getObject()->getId());
      $this->cart->save();
      $this->redirect('@cart_payment_process');
    }
  }
}

Ku memu zaskoczeniu zobaczyłem wyjątek, z którego wynikało, że zapis formularza customer się nie powiódł, bo formularz nie zawiera danych. Dla pewności wrzuciłem w kod kilka var_dumpów:

// wynik: dane dla całego formularza łącznie z osadzonymi
var_dump($this->form->getValues());
// wynik: false
var_dump($this->form->getEmbeddedForm('customer')->isBound());
// wynik: pusta tablica
var_dump($this->form->getEmbeddedForm('customer')->getObject()->toArray());

Jak się okazało, formularz klasy sfForm nie aktualizuje obiektów w osadzonych formularzach, trzeba to wykonać samemu:

$this->form->getEmbeddedForm('customer')
  ->updateObject($this->form->getValue('customer'));

Można to zrobić w kontrolerze, ja ze względów konwencjonalno-estetycznych dopisałem do klasy OnePageCheckoutForm metody updateEmbeddedForms() i saveEmbeddedForms(), które są z grubsza kopią tak samo nazwanych metod z klasy sfFormObject.

Generowanie losowych kodów rabatowych

Wczoraj pisałem moduł obsługujący kody rabatowe w sklepie internetowym. Wedle specyfikacji kody mogą być jedno- lub wielokrotnego użytku. Te pierwsze mogą być hurtowo generowane w panelu administracyjnym. Na chłopski rozum taki jednorazowy kod powinien być:

  • losowy, żeby pomysłowi klienci nie mogli zgadywać kodów
  • w miarę krótki, żeby wpisywanie go nie było kłopotliwe
  • pozbawiony podobnych do siebie znaków takich jak O i 0, ze względów wspomnianych w poprzednim punkcie

Mój pomysł na generowanie takich kodów wygląda następująco:

public static function generateRandom()
{
  return str_pad(strtr(strtoupper(base_convert(rand(1000, 2176782335), 10, 36)), '0O', '1A'), 6, 'X');
}

Powyższa metoda generuje losowe kody złożone z 6 alfanumerycznych znaków (wielkie litery i cyfry) z pominięciem zera i litery O. Przykładowy kod: 3LFPHP. Tytułem wyjaśnienia: 2176782335 to ZZZZZZ w systemie trzydziestoszóstkowym.

Nie robiłem żadnych testów, ale przeczucie mówi mi, że takie rozwiązanie jest szybsze niż losowanie po jednym znaku z tablicy dozwolonych znaków.

Kody powinny być także unikalne, a powyższa metoda tego nie zapewnia. Gdyby tych kodów trzeba było generować tysiące na minutę pewnie pokusiłbym się o jakiś bardziej wyszukany algorytm, np. w jednej transakcji wygenerować tablicę z liczbą kodów ciut większą niż zadana, jednym zapytaniem sprawdzić które z nich już występują w bazie, odfiltrować je z tablicy, a resztę wstawić drugim zapytaniem. W tym konkretnym sklepie nie ma takiej potrzeby, więc wybrałem prostsze rozwiązanie.

public static function generateCodes(Voucher $voucher, $number)
{
  for ($i = 1; $i <= $number; $i++)
  {
    do
    {
      try
      {
        $code = new VoucherCode();
        $code->setVoucher($voucher)
          ->setCode(VoucherCode::generateRandom())
          ->save();
      }
      catch (Doctrine_Connection_Mysql_Exception $e)
      {
      }
    } while ($code->isNew())
  }
}

Kolumna code w tabeli ma założony unikalny indeks (przydaje się do wyszukiwania), więc próba wstawienia takiej samej wartości powoduje wyjątek, który łapię i generuję nowe wartości dopóki wstawianie rekordu do tablicy nie zakończy się powodzeniem.

Doctrine Query Cache w Symfony

Dzisiaj o odkrywaniu Ameryki w konserwie. Właśnie takie miałem wrażenie, gdy dłubiąc w projekcie trafiłem na rozdział na temat cache w dokumentacji Doctrine. Wcześniej tam nie trafiłem, bo całe keszowanie robiłem w Symfony – wszak do pamięci podręcznej najlepiej wrzucać efekt końcowy. Dodatkowo w dokumentacji Symfony nie rzuciło mi się w oczy nic na temat cacheowania w Doctrine.

Nie zamierzam streszczać dokumentacji Doctrine. W kontekście tej notki ważne jest, że Doctrine może zapamiętywać w cache dwa rodzaje danych:

  • skompilowane zapytania
  • wyniki zapytań

Dzięki zapamiętywaniu zapytań czasochłonny proces parsowania zapytania i budowania docelowej SQL-ki jest wykonywany tylko raz, kolejne wywołania będą korzystać z danych zapamiętanych w pamięci podręcznej. Problem aktualności danych w cache nie istnieje ponieważ zmiana zapytania spowoduje automatyczne wygenerowanie nowych danych. Korzyści są niebagatelne, użycie proste jak konstrukcja cepa, a problemów nie ma. Dlatego zgodnie z dokumentacją i zdrowym rozsądkiem, pamięć podręczna zapytań powinna być zawsze włączona, nawet w środowisku testowym.

Włączenie cache dla Doctrine w projekcie Symfony jest banalne i sprowadza się do dodania króciutkiej metody w klasie config/ProjectConfiguration.class.php.

public function configureDoctrine(Doctrine_Manager $manager)
{
    $manager->setAttribute(Doctrine_Core::ATTR_QUERY_CACHE, new Doctrine_Cache_Apc());
}

Powyższy kod spowoduje, że skompilowane zapytania będą zapamiętywane w pamięci APC. Oprócz APC Doctrine może współpracować z serwerem memcached lub inną bazą danych (np. SQLite).

Cache można włączyć nie tylko na poziomie managera, równie dobrze można zrobić to w samym zapytaniu:

$q = Doctrine_Query::create()
    ->useQueryCache(new Doctrine_Cache_Apc());

Jak wspomniałem wcześniej, pamięć podręczna zapytań powinna być włączona zawsze. Na poziomie konkretnego zapytania więcej sensu ma włączanie zapamiętywania wyników:

$q = Doctrine_Query::create()
    ->useResultCache(new Doctrine_Cache_Apc());