Clever Geek Handbook

Component approach. SQL component of migrations to PHP

I haven't written on Habré yet about how I came to the idea of ​​forming components for my future projects or the current one instead of directly writing code. To put it very briefly, it was like this ... I wrote a lot of different projects, invented pseudo components and every time I came across the fact that in one project it is terribly convenient to use it, and in another it is terribly inconvenient. I tried to transfer "convenient" components to the project and it became still more inconvenient ... In short, my hands are not in the right place, my head is too ambitious ... Over time, I came to another thought: "We need to make repositories on GitHub with separate components, which will not depend on other components "... Everything was going well, but I got to the very component that wants to work with another component ... As a result, interfaces with methods came to the rescue.And now let's talk aboutthe SQL component of migrations in the way I see it.





So, most people, as well as my colleagues, are confident that migrations serve not only to update the database between developers, but also for operations with files, folders, and so on. For example, create a directory for all developers or something else for something there ...





Perhaps I could be wrong, but personally I am sure for myself that migrations are necessary exclusively for SQL database operations. To update files, you can use the same git or central init file, as it is in Yii2.





Idea

The migrations component, since it is exclusively for SQL operations, will be based on 2 SQL files. Yes, here now there will be a flurry of criticism about the entrance threshold and other things, but I will say right away that over the time we worked in the company, we switched from SQLBuilder to pure SQL, since it is faster. In addition, most modern IDEs can generate DDL for database operations. And just imagine, you need to create a table, fill it with data, and also change something in another table. On the one hand, you get a long code by the builder, on the other hand, you can use pure SQL in the same builder, or maybe this situation is mixed ... In short, then I realized and decided that in my component and approach to programming in general there will be as little duality as possible. Due to this, I decided to use only SQL code.





: , UP DOWN, . . .





 SqlMigration



, . . .





 ConsoleSqlMigration



,  SqlMigration



  .  parent::



  ().





 DatabaseInterface



  . :





  • schema -





  • table -





  • path -





() , (). .





SqlMigration



. , , - . :





  1. public function up(int $count = 0): array;







  2. public function down(int $count = 0): array;







  3. public function history(int $limit = 0): array;







  4. public function create(string $name): bool;







. , PHPDoc:





/**
	 *    
	 *
	 * @param int $count   (0 -  )
	 *
	 * @return array      .    :
	 * 1. ,     ,    
	 * 2.     :
	 * [
	 *  'success' => [...],
	 *  'error' => [...]
	 * ]
	 *  error       .
	 *
	 * @throws SqlMigrationException
	 */
	public function up(int $count = 0): array;
	
	/**
	 *    
	 *
	 * @param int $count   (0 -  )
	 *
	 * @return array      .    :
	 * 1. ,     ,    
	 * 2.     :
	 * [
	 *  'success' => [...],
	 *  'error' => [...]
	 * ]
	 *  error       .
	 *
	 * @throws SqlMigrationException
	 */
	public function down(int $count = 0): array;
	
	/**
	 *      
	 *
	 * @param int $limit    (null -  )
	 *
	 * @return array
	 */
	public function history(int $limit = 0): array;
	
	/**
	 *          
	 *
	 * @param string $name  
	 *
	 * @return bool  true,     .     
	 *
	 * @throws RuntimeException|SqlMigrationException
	 */
	public function create(string $name): bool;

SqlMigration



. . , :





/**
 *     
 */
public const UP = 'up';
public const DOWN = 'down';

. DatabaseInterface



. (DI) :





/**
 * SqlMigration constructor.
 *
 * @param DatabaseInterface $database     
 * @param array $settings  
 *
 * @throws SqlMigrationException
 */
public function __construct(DatabaseInterface $database, array $settings) {
	$this->database = $database;
	$this->settings = $settings;
	
	foreach (['schema', 'table', 'path'] as $settingsKey) {
		if (!array_key_exists($settingsKey, $settings)) {
			throw new SqlMigrationException(" {$settingsKey} .");
		}
	}
}

, . bool



:





/**
 *        
 *
 * @return bool  true,        .    
 * 
 *
 * @throws SqlMigrationException
 */
public function initSchemaAndTable(): bool {
	$schemaSql = <<<SQL
		CREATE SCHEMA IF NOT EXISTS {$this->settings['schema']};
	SQL;
	
	if (!$this->database->execute($schemaSql)) {
		throw new SqlMigrationException('   ');
	}
	
	$tableSql = <<<SQL
		CREATE TABLE IF NOT EXISTS {$this->settings['schema']}.{$this->settings['table']} (
			"name" varchar(180) COLLATE "default" NOT NULL,
			apply_time int4,
			CONSTRAINT {$this->settings['table']}_pk PRIMARY KEY ("name")
		) WITH (OIDS=FALSE)
	SQL;
	
	if (!$this->database->execute($tableSql)) {
		throw new SqlMigrationException('   ');
	}
	
	return true;
}

. ( ):





/**
 *     
 *
 * @param string $name  
 *
 * @throws SqlMigrationException
 */
protected function validateName(string $name): void {
	if (!preg_match('/^[\w]+$/', $name)) {
		throw new SqlMigrationException('     ,    .');
	}
}

/**
 *     : m{   Ymd_His}_name
 *
 * @param string $name  
 *
 * @return string
 */
protected function generateName(string $name): string {
	return 'm' . gmdate('Ymd_His') . "_{$name}";
}

, . : m___ - , :





/**
 * @inheritDoc
 *
 * @throws RuntimeException|SqlMigrationException
 */
public function create(string $name): bool {
	$this->validateName($name);
	
	$migrationMame = $this->generateName($name);
	$path = "{$this->settings['path']}/{$migrationMame}";
	
	if (!mkdir($path, 0775, true) && !is_dir($path)) {
		throw new RuntimeException("  .  {$path}  ");
	}
	
	if (file_put_contents($path . '/up.sql', '') === false) {
		throw new RuntimeException("    {$path}/up.sql");
	}
	
	if (!file_put_contents($path . '/down.sql', '') === false) {
		throw new RuntimeException("    {$path}/down.sql");
	}
	
	return true;
}

, , . :





/**
 *    
 *
 * @param int $limit    (null -  )
 *
 * @return array
 */
protected function getHistoryList(int $limit = 0): array {
	$limitSql = $limit === 0 ? '' : "LIMIT {$limit}";
	$historySql = <<<SQL
		SELECT "name", apply_time
		FROM {$this->settings['schema']}.{$this->settings['table']}
		ORDER BY apply_time DESC, "name" DESC {$limitSql}
	SQL;
	
	return $this->database->queryAll($historySql);
}

, :





/**
 * @inheritDoc
 */
public function history(int $limit = 0): array {
	$historyList = $this->getHistoryList($limit);
	
	if (empty($historyList)) {
		return ['  '];
	}
	
	$messages = [];
	
	foreach ($historyList as $historyRow) {
		$messages[] = " {$historyRow['name']}  " . date('Y-m-d H:i:s', $historyRow['apply_time']);
	}
	
	return $messages;
}

, , , . , .





/**
 *     
 *
 * @param string $name  
 *
 * @return bool  true,      (   ).
 *     .
 *
 * @throws SqlMigrationException
 */
protected function addHistory(string $name): bool {
	$sql = <<<SQL
		INSERT INTO {$this->settings['schema']}.{$this->settings['table']} ("name", apply_time) VALUES(:name, :apply_time);
	SQL;
	
	if (!$this->database->execute($sql, ['name' => $name, 'apply_time' => time()])) {
		throw new SqlMigrationException("   {$name}");
	}
	
	return true;
}

/**
 *     
 *
 * @param string $name  
 *
 * @return bool  true,      (   ).
 *     .
 *
 * @throws SqlMigrationException
 */
protected function removeHistory(string $name): bool {
	$sql = <<<SQL
		DELETE FROM {$this->settings['schema']}.{$this->settings['table']} WHERE "name" = :name;
	SQL;
	
	if (!$this->database->execute($sql, ['name' => $name])) {
		throw new SqlMigrationException("   {$name}");
	}
	
	return true;
}

, . , .





/**
 *     
 *
 * @return array
 */
protected function getNotAppliedList(): array {
	$historyList = $this->getHistoryList();
	$historyMap = [];
	
	foreach ($historyList as $item) {
		$historyMap[$item['name']] = true;
	}
	
	$notApplied = [];
	$directoryList = glob("{$this->settings['path']}/m*_*_*");
	
	foreach ($directoryList as $directory) {
		if (!is_dir($directory)) {
			continue;
		}
		
		$directoryParts = explode('/', $directory);
		preg_match('/^(m(\d{8}_?\d{6})\D.*?)$/is', end($directoryParts), $matches);
		$migrationName = $matches[1];
		
		if (!isset($historyMap[$migrationName])) {
			$migrationDateTime = DateTime::createFromFormat('Ymd_His', $matches[2])->format('Y-m-d H:i:s');
			$notApplied[] = [
				'path' => $directory,
				'name' => $migrationName,
				'date_time' => $migrationDateTime
			];
		}
	}
	
	ksort($notApplied);
	
	return $notApplied;
}

: up down. , up down . , , . , ( ) (up/down - , ).





/**
 *  
 *
 * @param array $list  
 * @param int $count    
 * @param string $type   (up/down)
 *
 * @return array   
 *
 * @throws RuntimeException
 */
protected function execute(array $list, int $count, string $type): array {
	$migrationInfo = [];
	
	for ($index = 0; $index < $count; $index++) {
		$migration = $list[$index];
		$migration['path'] = array_key_exists('path', $migration) ? $migration['path'] :
			"{$this->settings['path']}/{$migration['name']}";
		$migrationContent = file_get_contents("{$migration['path']}/{$type}.sql");
		
		if ($migrationContent === false) {
			throw new RuntimeException(' / ');
		}
		
		try {
			if (!empty($migrationContent)) {
				$this->database->beginTransaction();
				$this->database->execute($migrationContent);
				$this->database->commit();
			}
			
			if ($type === self::UP) {
				$this->addHistory($migration['name']);
			} else {
				$this->removeHistory($migration['name']);
			}
			
			$migrationInfo['success'][] = $migration;
		} catch (SqlMigrationException | PDOException $exception) {
			$migrationInfo['error'][] = array_merge($migration, ['errorMessage' => $exception->getMessage()]);
			
			break;
		}
	}
	
	return $migrationInfo;
}

:









  2. $migration['path'] = array_key_exists('path', $migration) ? $migration['path'] : "{$this->settings['path']}/{$migration['name']}";







  3. ( ): $migrationContent = file_get_contents("{$migration['path']}/{$type}.sql");







  4. . UP - , .





  5. ( , ).





, . () up down:





/**
 * @inheritDoc
 */
public function up(int $count = 0): array {
	$executeList = $this->getNotAppliedList();
	
	if (empty($executeList)) {
		return [];
	}
	
	$executeListCount = count($executeList);
	$executeCount = $count === 0 ? $executeListCount : min($count, $executeListCount);
	
	return $this->execute($executeList, $executeCount, self::UP);
}

/**
 * @inheritDoc
 */
public function down(int $count = 0): array {
	$executeList = $this->getHistoryList();
	
	if (empty($executeList)) {
		return [];
	}
	
	$executeListCount = count($executeList);
	$executeCount = $count === 0 ? $executeListCount : min($count, $executeListCount);
	
	return $this->execute($executeList, $executeCount, self::DOWN);
}

. , . , , . - API . , , , :





<?php

declare(strict_types = 1);

namespace mepihindeveloper\components;

use mepihindeveloper\components\exceptions\SqlMigrationException;
use mepihindeveloper\components\interfaces\DatabaseInterface;
use RuntimeException;

/**
 * Class ConsoleSqlMigration
 *
 *      SQL       ()
 *
 * @package mepihindeveloper\components
 */
class ConsoleSqlMigration extends SqlMigration {
	
	public function __construct(DatabaseInterface $database, array $settings) {
		parent::__construct($database, $settings);
		
		try {
			$this->initSchemaAndTable();
			
			Console::writeLine('       ', Console::FG_GREEN);
		} catch (SqlMigrationException $exception) {
			Console::writeLine($exception->getMessage(), Console::FG_RED);
			
			exit;
		}
	}
	
	public function up(int $count = 0): array {
		$migrations = parent::up($count);
		
		if (empty($migrations)) {
			Console::writeLine("   ");
			
			exit;
		}
		
		foreach ($migrations['success'] as $successMigration) {
			Console::writeLine(" {$successMigration['name']}  ", Console::FG_GREEN);
		}
		
		if (array_key_exists('error', $migrations)) {
			foreach ($migrations['error'] as $errorMigration) {
				Console::writeLine("   {$errorMigration['name']}", Console::FG_RED);
			}
			
			exit;
		}
		
		return $migrations;
	}
	
	public function down(int $count = 0): array {
		$migrations = parent::down($count);
		
		if (empty($migrations)) {
			Console::writeLine("   ");
			
			exit;
		}
		
		if (array_key_exists('error', $migrations)) {
			foreach ($migrations['error'] as $errorMigration) {
				Console::writeLine("   {$errorMigration['name']} : " .
					PHP_EOL .
					$errorMigration['errorMessage'],
					Console::FG_RED);
			}
			
			exit;
		}
		
		foreach ($migrations['success'] as $successMigration) {
			Console::writeLine(" {$successMigration['name']}  ", Console::FG_GREEN);
		}
		
		return $migrations;
	}
	
	public function create(string $name): bool {
		try {
			parent::create($name);
			
			Console::writeLine(" {$name}  ");
		} catch (RuntimeException | SqlMigrationException $exception) {
			Console::writeLine($exception->getMessage(), Console::FG_RED);
			
			return false;
		}
		
		return true;
	}
	
	public function history(int $limit = 0): array {
		$historyList = parent::history($limit);
		
		foreach ($historyList as $historyRow) {
			Console::writeLine($historyRow);
		}
		
		return $historyList;
	}
}

, DI , . GitHub Composer.







More articles:


All Articles