PHP Classes
elePHPant
Icontem

DB Schema Management: Dump and update MySQL database schemata

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Info   View files View files (5)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Last Updated Ratings Unique User Downloads Download Rankings  
2012-05-18 (3 years ago) RSS 2.0 feedNot yet rated by the usersTotal: 477 This week: 2All time: 5,542 This week: 570Up
Version License PHP version Categories  
dbmaintenece 1.0.0GNU General Publi...5.0PHP 5, Databases, Systems administration, C...
Description Author  

This class can create, dump and update MySQL database schemata.

It can connect to a given MySQL server and dump the schema of a database to a PHP script that defines the list of tables, fields and properties in an array.

The class can also recreate or update a MySQL database schema to a newer version using a previously generated schema definition PHP script file.

Picture of Larry Wakeman
Name: Larry Wakeman is available for providing paid consulting. Contact Larry Wakeman .
Classes: 4 packages by
Country: United States United States
Age: ???
All time rank: 1412192 in United States United States
Week rank: 555 Up56 in United States United States Up
Innovation award
Innovation award
Nominee: 2x

Winner: 1x

Details provided by the author  
This class is the dbMaintenence class.

I have been doing php/MySQL development for many years now and have started looking at distributing applications.  One or the 
issues that I have come up with the the creation and updating of the database schema.  I started developing this class on a project
that was creating a WordPress plugin that the client was hoping to resell.  I had been working with WP eCommerce and sae how they
managed their schema and developed this system that overcomes some of the shortcomming of that system.

This class can be used to:

1 - extract a database schema into a php script that defines the schema in an array.
2 - apply the extracted script to recreate the database schema.
3 - Allow the editting of the extracted script to impment changes to the schema.
4 - Allow users of the application to create changes to the schema that are separate to the extracted script.
5 - Support for pre and post processing scripts.
6 - Require a minimum schema version to apply the update.

This script will never drop a table.  If you need to drop a table that can be done in a pre or post processing script.  Table name changes
can be done in a preprocessing script.  Data can also be loaded with the post processing script.

The index.php file is an example script to show the use of this class.  To install, place the suppled files in a directory thatis web accessible.
Navigate to the index.php.  Fill in the form with the requested data, and click a button.  Before an extraction can be performed, enter the 
host, user name, password, database name for the database you want to extract.  Enter schema_version.php in the filename box.  The table 
prefix can be blank but it can be used to limit the extraction to tables and views that have a commen beginning to their names.  Click update.  
This will add a schema_version table to the database and enable the extracting of the database schema.  When that is complete,enter the name 
you want for your extract file and click extract.  You can then use this extract file on another database or edit it to apply changes to the database.

A note about views.  There are two issues with views.  The first is that if one view references another view in it's definition, you have to
edit the extract tile to move the view definition for thereferred view before the refferrring view definiton.  The other prpnlem is the where and 
order by clauses.  The column names in these clauses are not quoted, so you may have to quote (`) any column names that are SQL keywords.

To use the class, first you initialize it:
    include('dbMaintenence.php');     // load the class
    $maint = new dbMaintenence('hostname', 
                                                              'username', 
                                                              'password', 
                                                              'database',
                                                             [ 'dbprefix']);   // initialize the class

The other two functions to be used are the extract and update function as follows:
     $maint->extract('filename');       // create an extract file
     $maint->update('filename');       // update a databae

I added a copy that is designed to be used with WordPress, dbMaintenenve.wp.php.  It is functionally the same but used the wpdb object to interface with the database. The 
difference in the coding is with the object is the initializing of the class:
    include('dbMaintenence.php');     // load the class
    $maint = new dbMaintenence([ 'dbprefix']);   // initialize the class
    
The datbase parameters are picked up from the wpdb object. The prefix, if specified is appended to the WordPress db prefix.
  Files folder image Files  
File Role Description
Plain text file dbMaintenence.php Class Main class file
Plain text file dbMaintenence.wp.php Class WordPress version of the class
Accessible without login Plain text file index.php Example Example
Accessible without login Plain text file readme.txt Doc. Documentation
Accessible without login Plain text file schma_version.php Aux. Scema Version Table Definition

 Version Control Unique User Downloads Download Rankings  
 0%Total:477All time:5,542
 This week:2This week:570Up