mysql

Database Migrations Using Phinx Database migrations can transform your database in many ways such as creating new tables, inserting rows, adding indexes and modifying columns. It avoids the use of writing MYSQL by hand and instead offers a powerful API for creating migrations using PHP code. Advantages of using Phinx Phinx keeps track of which migrations have been run so you can worry less about the state of your database and instead focus on building better software Each migration is represented by a PHP class in a unique file. We can write our migrations using the Phinx PHP API, or raw SQL. Phinx has an easy installation process and easy to use command line instructions and easy to Integrate with various other PHP tools (Phing, PHPUnit) and web frameworks. Installating Phinx Phinx should be installed using Composer. Composer is a tool for dependency management in PHP. We need to require the dependency in composer.json. php composer.phar require robmorgan/phinx Then run Composer: php composer.phar install --no-dev Now Create a folder in your database directory called migrations with adequate permissions. It is where we write our migrations. In engelsystem it is created in db directory Phinx can now be executed from within your project: php vendor/bin/phinx init Writing Migrations For SQL files Creating a New Migration Let’s start by creating a new Phinx migration. Run Phinx using the create command. This will create a new migration in the format YYYYMMDDHHMMSS_my_new_migration.php where the first 14 characters are replaced with the current timestamp down to the second. This will create a skeleton file with a single method. $ php vendor/bin/phinx create MyNewMigration The File looks something like this Explaining the File The AbstractMigration Class Abstraction class provides the necessary support to create your database migrations. All Phinx migrations extend from the AbstractMigration class. Phinx provides different methods in the abstraction class like change, up and down method. The Change Method This is the default migration method. I will explain how to write the change method for an example MYSQL query. For example following MYSQL query can also be executed using Phinx change method. MYSQL Query ALTER TABLE `AngelTypes` ADD `requires_driver_license` BOOLEAN NOT NULL; Equivalent change method public function change() { $table = $this->table('AngelTypes'); $table->addColumn('requires_driver_license', 'boolean', array('null' => 'false')) ->update(); } The Up Method We should use the up method to transform the database with your intended changes. For example following MYSQL query to create a new settings table can be executed using equivalent up method. MYSQL Query DROP TABLE IF EXISTS `Settings`; CREATE TABLE IF NOT EXISTS `Settings` ( `event_name` varchar(255) DEFAULT NULL, `buildup_start_date` int(11) DEFAULT NULL, `event_start_date` int(11) DEFAULT NULL, `event_end_date` int(11) DEFAULT NULL, `teardown_end_date` int(11) DEFAULT NULL, `event_welcome_msg` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; Equivalent up method public function up() { $table = $this->table('Settings'); $table->addColumn('event_name', 'string', array('limit' => 255)) ->addColumn('buildup_start_date', 'integer', array('limit' => 11)) ->addColumn('event_start_date', 'integer', array('limit' => 11)) ->addColumn('event_end_date', 'integer', array('limit' => 11)) ->addColumn('teardown_end_date', 'integer', array( 'limit' => 11)) ->addColumn('event_welcome_msg', 'string', array('limit' => 255)) ->save(); }We have now created…