Aug 06 2009

Upgrade databáze a použití LAST_INSERT_ID()

Tag: MySQL,PHP,Zend FrameworkJens @ 10:40

Při vytváření nových verzí/revizí nějaké aplikace (ať již pomocí subversion či jiného nástroje) je často vhodné a někdy i potřebné aktualizovat strukturu databáze (přidání nových tabulek, sloupců, atd.) případně aktualizovat data (číselníky, systémové hodnoty) v databázi tak, aby nová změna v kódu byla kompatibilní se změnou struktury či dat v databázi (pro úplnost pouze doplním, že článek je napsán pro databázi MySQL 5.x).

Otázka je, jak tuto změnu provést tak, aby jsme při aktualizaci projektu na příslušnou revizi měli k dispozici i aktuální verzi databáze. Jedním z jednodušších řešení je, že spolu s commitem upraveného kódu (například nějakého modelu) commitneme i skript, který po aktualizaci projektu na příslušnou revizi spustíme a tím zajistíme upgrade databáze.

Tento skript může mít různé podoby, buď ho můžeme napsat přímo v PHP a nebo v případě jednodušší aktualizace použijeme pouze několik SQL příkazů. Nedávno jsem zrovna řešil poměrně jednoduchý problém, bylo třeba přidat skript do databáze šablonu i s jednou jazykovou mutací — to znamená: přidat jeden řádek do tabulky sablona a jeden řádek do tabulky sablona_mutace. Jelikož je však při definici ID šablon i mutací použit extra typ AUTO_INCREMENT, tak vložení druhého řádku s mutací bude záviset na ID vloženého řádku šablony. V SQL skriptu nemůžu přímo ID definovat, protože v době kdy se bude skript (upgrade) provádět může mnou vybrané ID šablony už být použito, proto je potřeba nechat sloupec NULL a MySQL vloží vlastní ID dle hodnoty AUTO_INCREMENT tabulky.

Řešením tohoto problému je použití funkce LAST_INSERT_ID(), po vložení řádku šablony definujeme cizí klíč ID šablony v řádku mutace právě pomocí této funkce. Výsledek by pak mohl vypadat nějak takto:

INSERT INTO sablona (id, nazev, kategorie, datum)
    VALUES (NULL, 'Neuskutečněná schůzka', 'system', NOW());

INSERT INTO sablona_mutace (id, sablona_id, jazyk_id, telo)
   VALUES (NULL, LAST_INSERT_ID(), 1, 'Schůzka ne neuskutečnila ...');

Takto jednouše by mohl vypada SQL skript, PHP skript kterým by jste udělali to samé například v Zend_Frameworku by mohl vypadat přibližně takto:

...
$sablona = array(
  'nazev' => 'Neuskutečněná schůzka',
  'kategorie' => 'system',
  'datum' => new Zend_Db_Expr('NOW()'),
);
$sablona_id = Sablona::getInstance()->insert($sablona);

$mutace = array(
  'sablona_id' => $sablona_id,
  'jazyk_id' => 1,
  'telo' => 'Schůzka ne neuskutečnila ...',
);
$mutace_id = SablonaMutace::getInstance()->insert($mutace);
...

Poznámka k PHP příkladu: modely jsou potomky třídy Zend_Db_Table a jsou vytvořeny jako singleton; hodnoty sloupce kategorie a jazyk_id v příkladu jsou magic konstanty což není úplně programátorsky čisté — je to pouze pro jednoduchost příkladu.