Aktualizace struktury databáze
Školení, která pořádám 
Pokud při vytváření aplikací používáte více než jednu databázi (např. jednu vývojovou a druhou ostrou), musíte veškeré změny ve struktuře tabulek dělat se všemi databázemi, obvykle v různý okamžik – na vývojové databázi hned, na ostré až při vydání upravené aplikace.
Já databázi navrhuji tak, že přímo píšu SQL skript, proto jsem do generátoru administračních rozhraní napsal utilitu, která porovná text skriptu s aktuálním stavem databáze a vypíše nebo provede příslušné změny. Tento skript pak lze spustit proti všem používaným databázím.
Pokud si rozhraní někdo raději nakliká v interaktivním nástroji, může postupovat stejně – vyexportovat strukturu databáze, zkopírovat ji na server a spustit zmíněný PHP skript. Postup je nicméně poměrně krkolomný, server navíc musí podporovat PHP. Do správce databáze phpMinAdmin jsem proto přidělal možnost přímo vyexportovat SQL skript, který provede potřebné změny (přidá nové sloupce, odebere staré, nastaví typ u změněných). Využívají se k tomu uložené procedury, tato funkce je tedy dostupná jen v MySQL 5. Vyjadřovací schopnosti uložených procedur nejsou právě bohaté, skript proto pro každou tabulku vytvoří vlastní uloženou proceduru, tu spustí a následně ji po sobě smaže. Výsledkem by měla být tabulka se stejnou strukturou jako ve vývojové databázi ale se zachováním dat v ostré databázi.
Vygenerovaná procedura pro jednu tabulku vypadá potom takhle:
CREATE PROCEDURE phpminadmin_alter () BEGIN
DECLARE _column_name, _collation_name, _column_type, after varchar(64) DEFAULT '';
DECLARE _column_default longtext;
DECLARE _is_nullable char(3);
DECLARE _extra varchar(20);
DECLARE _column_comment varchar(255);
DECLARE done, set_after bool DEFAULT 0;
DECLARE add_columns text DEFAULT ', ADD `id` int(11) NOT NULL auto_increment FIRST, ADD `interpret` int(11) NOT NULL AFTER `id`, ADD `title` varchar(50) COLLATE utf8_general_ci NOT NULL AFTER `interpret`, ADD `issued` year(4) AFTER `title`';
DECLARE columns CURSOR FOR SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, COLLATION_NAME, COLUMN_TYPE, EXTRA, COLUMN_COMMENT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'albums' ORDER BY ORDINAL_POSITION;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET @alter_table = '';
OPEN columns;
REPEAT
FETCH columns INTO _column_name, _column_default, _is_nullable, _collation_name, _column_type, _extra, _column_comment;
IF NOT done THEN
SET set_after = 1;
CASE _column_name
WHEN 'id' THEN
SET add_columns = REPLACE(add_columns, ', ADD `id` int(11) NOT NULL auto_increment FIRST', '');
IF NOT (_column_default <=> NULL) OR _is_nullable != 'NO' OR _collation_name != '' OR _column_type != 'int(11)' OR _extra != 'auto_increment' OR _column_comment != '' OR after != '' THEN
SET @alter_table = CONCAT(@alter_table, ', MODIFY `id` int(11) NOT NULL auto_increment FIRST');
END IF;
WHEN 'interpret' THEN
SET add_columns = REPLACE(add_columns, ', ADD `interpret` int(11) NOT NULL AFTER `id`', '');
IF NOT (_column_default <=> NULL) OR _is_nullable != 'NO' OR _collation_name != '' OR _column_type != 'int(11)' OR _extra != '' OR _column_comment != '' OR after != 'id' THEN
SET @alter_table = CONCAT(@alter_table, ', MODIFY `interpret` int(11) NOT NULL AFTER `id`');
END IF;
WHEN 'title' THEN
SET add_columns = REPLACE(add_columns, ', ADD `title` varchar(50) COLLATE utf8_general_ci NOT NULL AFTER `interpret`', '');
IF NOT (_column_default <=> NULL) OR _is_nullable != 'NO' OR _collation_name != 'utf8_general_ci' OR _column_type != 'varchar(50)' OR _extra != '' OR _column_comment != '' OR after != 'interpret' THEN
SET @alter_table = CONCAT(@alter_table, ', MODIFY `title` varchar(50) COLLATE utf8_general_ci NOT NULL AFTER `interpret`');
END IF;
WHEN 'issued' THEN
SET add_columns = REPLACE(add_columns, ', ADD `issued` year(4) AFTER `title`', '');
IF NOT (_column_default <=> NULL) OR _is_nullable != 'YES' OR _collation_name != '' OR _column_type != 'year(4)' OR _extra != '' OR _column_comment != '' OR after != 'title' THEN
SET @alter_table = CONCAT(@alter_table, ', MODIFY `issued` year(4) AFTER `title`');
END IF;
ELSE
SET @alter_table = CONCAT(@alter_table, ', DROP ', _column_name);
SET set_after = 0;
END CASE;
IF set_after THEN
SET after = _column_name;
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE columns;
IF @alter_table != '' OR add_columns != '' THEN
SET @alter_table = CONCAT('ALTER TABLE `albums`', SUBSTR(CONCAT(add_columns, @alter_table), 2));
PREPARE alter_command FROM @alter_table;
EXECUTE alter_command;
DROP PREPARE alter_command;
END IF;
END
Diskuse
Honza:
Zajimave, jak kazdy resime stejny (podobny) problem jinak...Osobne pouzivam system, ze mam jednotlive databazove objekty ulozene v textovych souborech v adresarove strukture, ktera je v Subversion. Edituji je v mem oblibenem editoru nebo PL/SQL Developeru. Kdyz pak chci aplikovat zmeny na existujici DB, staci spustit skriptik a voala, zmeny se provedou. Nemusim psat zavislosti mezi objekty (mam parser PL/SQL), nemusim se temer o nic starat. Samozrejme, vsechno ma sve vyhody i nevyhody. Ja napr. musim pro kazdou zmenu struktury tabulky napsat ALTER. Na druhou stranu jasne vidim, co se bude delat a mam to pod kontrolou. Az si budu jisty, ze dokazu spolehlive zjistit vsechny rozdily mezi 2 soubory (mezi 2 stejnymi tabulkami), tak udelam i genorovani Alter souboru, ale do te doby to radsi budu delat rucne.
Jan Havrda:
Pekne a ciste k teto zalezitosti pristupuje Ruby. System Migration vytvari pro kazdou zmenu soubor, ktery obsahuje jak popis pro upgrade databaze tak pro odstraneni zmen. Viz http://wiki.rubyonrails.org/rails/pages/UsingMigrations . Vytvoreni podobneho skriptu v PHP by nemel byt problem.
Jan Havrda: Richard Mihalovič:
Nastroje pre migraciu db pod JRE: liquibase, migrate4jNastroje pre migraciu db pod PHP: doctrine (http://www.doctrine-project.org/documentation/…-page#migration)
Diskuse je zrušena z důvodu spamu.
© 2005-2024 