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 desired SQL after updating the application code.

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

How?

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

2. The procedure for naming files with SQL queries

0034.users_added_balance_column.sql

files are numbered so that the execution order is unambiguously saved

3. A special file format 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. A label is entered at the beginning of the file (matches the file name).

The essence of what is happening: the dbversions table stores the labels of all executed requests. Each time, the stored procedure checks to see if a given SQL query has been launched on this database.

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

#!/usr/bin/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 something to update or breaking the database.

Thanks to such an organization, the application "rises" on almost any machine in a few minutes - database dumps are not needed - the entire application is in the repository (SVN).