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
Jakub Vrána, Řešení problému, 12.9.2008, diskuse: 3 (nové: 0)

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.

ikona 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.

Richard Mihalovič:

Nastroje pre migraciu db pod JRE: liquibase, migrate4j
Nastroje pre migraciu db pod PHP: doctrine (http://www.doctrine-project.org/documentation/…-page#migration)

Vložit komentář

Používejte diakritiku. Vstup se chápe jako čistý text, ale URL budou převedeny na odkazy a PHP kód uzavřený do <?php ?> bude zvýrazněn. Pokud máte dotaz, který nesouvisí s článkem, zkuste raději diskusi o PHP, zde se odpovědi pravděpodobně nedočkáte.

Jméno: URL:

avatar © 2005-2018 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.