PHP Classes

How Can PHP Synchronize MySQL Databases From Your Local Environment to the Production Quickly - PHP MySQL Scheman package blog

Recommend this page to a friend!
  All package blogs All package blogs   PHP MySQL Scheman PHP MySQL Scheman   Blog PHP MySQL Scheman package blog   RSS 1.0 feed RSS 2.0 feed   Blog How Can PHP Synchroni...  
  Post a comment Post a comment   See comments See comments (0)   Trackbacks (0)  

Author:

Updated on: 2021-10-21

Posted on: 2021-10-21

Viewers: 461 (October 2021)

Package: PHP MySQL Scheman

Sometimes you need to update your MySQL database in production to deploy schema changes to support new PHP application features.

The PHP MySQL Scheman package can help you to perform this task quickly.

Read this tutorial article on how to use this package to perform database schema synchronization in practice.




Loaded Article

In this article you will learn:

What is the Problem of Database Schema Synchronization

What Can be a Solution to Implement a Secure Database Schema Synchronization Process

What is the PHP MySQL Scheman Package

Installation

Basic Usage

Syncronization

Reverse Engineering

Download and Install the PHP MySQL Scheman Package Using PHP Composer


What is the Problem of Database Schema Synchronization

Usually, what we used to do when we need to update a database schema is to create an SQL script when we release a new application update and then we run the SQL script. The risk of doing without much testing is that we may miss some columns and our code may not work.

What Can be a Solution to Implement a Secure Database Schema Synchronization Process

One possible solution to this problem is to use any MySQL synchronization tools like SQL Examiner, dbForge, etc. And most of these tools are paid.

What is the PHP MySQL Scheman Package

The PHP MySQL scheman package, is a simple MySQL schema synchronization tool. It can execute SQL queries automatically based on the differences between two database schemata.

Installation

If you use Composer, you can install MySQL Scheman with the following command: composer require smart-php/mysql-scheman

Or alternatively, include a dependency for MySQL Scheman in your composer.json file. For example:

{
"require-dev": {
"smart-php/mysql-scheman": "dev-master"
}
}

Basic Usage

The first step is to create a configuration file either in JSON or XML where you can put your database configuration details.

config.xml

<config> 
<hostname>127.0.0.1</hostname>
<username>root</username>
<password>mypassword</password>
<database>test</database>
<driver>pdo</driver>
</config>

Next, you can create a schema file and add your tables and fields' information.

myscheman.xml

<?xml version="1.0"?>
<database name="shcool">
<table name="students">
<columns Field="id" Type="int(11)" Null="NO" Key="PRI" Default="" Extra="auto_increment" Comment=""/>
<columns Field="name" Type="varchar(255)" Null="NO" Key="" Default="" Extra="" Comment=""/>
</table>
<table name="teachers">
<columns Field="id" Type="int(11)" Null="NO" Key="PRI" Default="" Extra="auto_increment" Comment=""/>
<columns Field="name" Type="varchar(255)" Null="NO" Key="" Default="" Extra="" Comment=""/>
</table>
</database>

Syncronization

By running the --sync command MySQL Scheman will find the changes required in your server and create an SQL Query and it will execute. In the above example imagine if your database doesn't have table teachers. With sync, it will create a table in the database with fields mentioned in the schema file.

./vendor/bin/scheman --config config.xml --sync myscheman.xml

Reverse Engineering

For existing database structures MySQL Scheman supports reverse engineering of your current database schema. By using command export you can create myscheman.xml file with your existing database.

./vendor/bin/scheman --config config.xml --sync myscheman.xml

Download and Install the PHP MySQL Scheman Package Using PHP Composer

You can download or install the PHP MySQL Scheman package using PHP Composer tool by going to this download page to get the package code. That page also contains instructions on how to install package using PHP Composer from the PHP Classes site.




You need to be a registered user or login to post a comment

Login Immediately with your account on:

FacebookGmail
HotmailStackOverflow
GitHubYahoo


Comments:

No comments were submitted yet.




  Post a comment Post a comment   See comments See comments (0)   Trackbacks (0)  
  All package blogs All package blogs   PHP MySQL Scheman PHP MySQL Scheman   Blog PHP MySQL Scheman package blog   RSS 1.0 feed RSS 2.0 feed   Blog How Can PHP Synchroni...  
For more information send a message to info at phpclasses dot org.