PHP Classes

DB Version Manager: Update database schema with commands read in files

Recommend this page to a friend!
  Info   View files View files (9)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
StarStarStarStar 66%Total: 324 This week: 1All time: 7,199 This week: 560Up
Version License PHP version Categories
dbversionmanager 0.9BSD License5XML, PHP 5, Databases, Files and Folders
Description 

Author

DBVersionManager is a free software to handle trace of database
changes which are needed during application development.
In projects which use database, there might be a problem with
moving database changes (alters, drops, inserts etc) between several environments.
(eg. there might be several developers environmets, test environment
and production environment, and each of them uses own database).
DBVersionManager tries to simplify port database changes between environments.
It can be used with any database which are supported by PDO.

DBVersionManager reads the sql commands from a given location.
Then it tries to execute this commands on selected database.
On the second hand DBVersionManager remember which files where
executed to preserve executing each command more then once.

DBVersionManager offers also a simple GUI to execute sql queries
on a given environment.
Using a GUI is recomended for correct naming convention and executing
queries in proper order.

This class can update database schema with commands read in files.

It can read files in a given directory and executes the SQL commands using PDO extension.

Picture of Przemek Berezowski
Name: Przemek Berezowski <contact>
Classes: 3 packages by
Country: Poland Poland
Age: 45
All time rank: 123232 in Poland Poland
Week rank: 411 Up14 in Poland Poland Up

Details

/** * DB version manager * * Copyright (c) 2011 Przemek Berezowski (przemek@otn.pl) * All rights reserved. * * * @package DBVersionManager * @copyright Copyright (c) 2011 Przemek Berezowski (przemek@otn.pl) * @version 0.9 * @license New BSD License */ Table of contents. 1. What is a DBVersionManager 2. How does it work 3. Prerequisties 4. Configuring 5. Using DBVersionManager 6. Notes +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1. What is a DBVersionManager +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ DBVersionManager is a free software to handle trace of database changes which are needed during application development. In projects which use database, there might be a problem with moving database changes (alters, drops, inserts etc) between several environments. (eg. there might be several developers environmets, test environment and production environment, and each of them uses own database). DBVersionManager tries to simplify port database changes between environments. It can be used with any database which are supported by PDO. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 2. How does it work +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ DBVersionManager reads the sql commands from a given location. Then it tries to execute this commands on selected database. On the second hand DBVersionManager remember which files where executed to preserve executing each command more then once. DBVersionManager offers also a simple GUI to execute sql queries on a given environment. Using a GUI is recomended for correct naming convention and executing queries in proper order. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 3. Prerequisties +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1. Files with sql commands generated by DBVersionManager should be stored in SVN or other version control system. 2. On *nix based environments, there should be proper write permissions on output/ directory 3. webserver with php support. 4. Database connection is built on PDO extension in php, so this extension should by enabled (default). Using PDO, allow to use DBVersionManager with any database engine which are supported by PDO. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 4. Configuring +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ There are two configurations files. 1. conf/configure.xml which stores information about profiles 2. profile.php which defines a constant with a name of current profile in a given environment. Variables stored in configure.xml <profile name="test"> <dsn>mysql:host=localhost;dbname=test1</dsn> <dbUser>user</dbUser> <dbPass></dbPass> <sqlPath>C:\\sql\Test</sqlPath> <sqlUseTransactions>true</sqlUseTransactions> </profile> Each profile is defining in profile node and recognizing by name attribute. dsn - should be a valid dsn connection string for PDO as described in http://pl.php.net/manual/en/pdo.connections.php. dbUser - user name to conect to a database dbPass - password for dbUser sqlPath - path in filesystem where files with sql queries are stored. This directory shoud be exachged in version control system. sqlUseTransactions - if queries form each sql file should be executed in transaction - recomended true. In conf/configure.xml there should be as many profiles defined as many environments there is in the project. To point DBVersionManager to the proper profile for a given environment change the value of CURRENT_PROFILE constant defined in profile.php +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 5. Using DBVersionManager +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Here is a scenario how to use it. First of all, put all files in a directory accessible by webserver, and setup your conf/config.xml with actual values. Then point your browser to http://yourhost/DBVersionManager/add.php Try to type in some sql command in textarea and click submit. DBVersionManager do two things: 1. Creates a files with name like YmdHis_PROFILENAME_N.sql in a directory defined in sqlPath in configure.xml for a profile. 2. Executes this command on a profile database. You should add generated sql file to version control and commit changes. Then, when your collegue updates his repository and points his browser to http://yourhost/DBVersionManager/update.php this file will be executed on his environment. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 6. Notes +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1. Sql files are executed in alphabetical order by a file name. If you prefer manualy add sql command to a file, please remember to give a file a proper name to execute in correct order. Otherway, execution might break. 2. Manualy added sql files can contain more then one sql command, Commands should be separated with ; and new line feed character. 3. When adding sql files manualy, plese remember to not mixed queries to modify structures (like alter, create etc) with queries with updates or deletes in one file. 4. If sql reports a bug in an sql command, while using sqlUseTransactions mode all queries from a file will be rollbacked. But if sqlUseTransactions is false, queries before problematic command will be executed. 5. If sql reports a bug, database updating process is stopped. 6. Please be careful while using this on production environment. There is no authentication features, so when you place it on production environment anybody can call this. Use at least webserver basic authentication feature. 6. Bugs and comment please report to przemek@otn.pl

  Files folder image Files  
File Role Description
Files folder imageconf (1 file)
Files folder imagelib (3 files)
Files folder imageoutput (1 file)
Accessible without login Plain text file add.php Example Adds a query to list and execute it
Accessible without login Plain text file profile.php Conf. App profile configuration
Accessible without login Plain text file readme.txt Doc. what is it and how does it work
Accessible without login Plain text file update.php Example Updates current profile database

  Files folder image Files  /  conf  
File Role Description
  Accessible without login Plain text file configure.xml Data Profiles configurations

  Files folder image Files  /  lib  
File Role Description
  Plain text file config_manager.php Class Manages configuration for profile
  Plain text file db_updater.php Class Manages database updates
  Plain text file tools.php Class Helper class

  Files folder image Files  /  output  
File Role Description
  Accessible without login Plain text file dummy.txt Data Directory for store application output

 Version Control Unique User Downloads Download Rankings  
 0%
Total:324
This week:1
All time:7,199
This week:560Up
 User Ratings  
 
 All time
Utility:83%StarStarStarStarStar
Consistency:83%StarStarStarStarStar
Documentation:83%StarStarStarStarStar
Examples:83%StarStarStarStarStar
Tests:-
Videos:-
Overall:66%StarStarStarStar
Rank:505