Site update, database schema update (MySQL)

Problem - you need to update the site (aka "svn up") plus update the database schema - add tables, indexes, etc.

SQL database update requests are stored in the repository, you need to run the necessary SQL after updating the application code.



Complexity: 1) it is impossible for the same SQL to be executed twice. 2) it is necessary to execute queries in a certain sequence (it is impossible to make ALTER TABLE before creation).





')

How?

1. Regulations regarding SQL queries on changing the structure of the database - the developer knows that SQL will be executed on the LIVE server with all the consequences.



2. Regulations regarding the naming of files with SQL queries



0034.users_added_balance_column.sql



files are numbered in order to uniquely preserve the order of execution



3. A special format of files with SQL queries (template) is used:

SET @version= 'users_added_balance_column' ;



CREATE TABLE IF NOT EXISTS `dbversions` (`version` varchar (200) NOT NULL ,`dt_applied` datetime default NULL , UNIQUE KEY `version` (`version`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP PROCEDURE IF EXISTS prc_update;

DELIMITER //

CREATE PROCEDURE prc_update(version_to_check VARCHAR (200)) BEGIN SET @isversion=( SELECT `version` FROM `dbversions` WHERE `version`=version_to_check); IF ISNULL(@isversion) THEN

— INSERT SQL HERE (BELOW)



ALTER TABLE `user_groups` ADD INDEX ( `userId` );



ALTER TABLE `user_groups`

ADD CONSTRAINT `user_groups_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;



— END , DO NOT INSERT SQL BELOW THIS LINE

INSERT INTO `dbversions` SET `version`=version_to_check, dt_applied=NOW(); SET @echo_string = CONCAT( 'Executed ' , version_to_check);

ELSE SET @echo_string = CONCAT( 'Skipped ' , version_to_check); END IF ; SELECT @echo_string AS '' ;

END //

DELIMITER;

CALL prc_update(@version); DROP PROCEDURE IF EXISTS prc_update; SET @version= NULL ;



* This source code was highlighted with Source Code Highlighter .

That is, the “header” and “basement” are always used, which, in fact, guarantee that SQL will be executed once. At the beginning of the file the label is written (the same as the file name).



The essence of what is happening: the dbversions table stores the labels of all executed queries. The stored procedure checks each time whether the given SQL query was launched on the given database.



4. There is an updatedb.php script that runs all queries automatically (only those that were not executed due to the stored procedure in each .sql file are executed):

#!/usr/bin/php

<?php



require( dirname(__FILE__). '/../bootstrap_cli.php' );



$dir = dirname(__FILE__);



list($dbName, $dbUser, $dbPassword, $dbHost) = split( '/', Config::$databasesConnections['main']);



if (!empty($dbPassword)) {

$dbPassword_cmdln = '-p'.$dbPassword;

} else {

$dbPassword_cmdln = '';

}



foreach (glob($dir. '/*.sql') as $sqlFile) {



system( "mysql -u {$dbUser} {$dbPassword_cmdln} {$dbName} < {$sqlFile}" );

}



* This source code was highlighted with Source Code Highlighter .

All this allows you to quickly update the server (staging, live, working copy) - svn up, then updatedb.php - without fear of forgetting to update something or break the base.



Thanks to this organization, the application “rises” on almost any machine in a few minutes — there is no need for database dumps - the application is completely in the repository (SVN).