The Problem
If you do not have subversion installed already, you can borrow parts from the subversion + trac installation tutorial. Once you have subversion installed, its important to integrate it in your web development workflow. Simply running SVN commit everytime you complete a feature is not enough. So let us see how we can fully integrate subversion into our web development. This workflow also includes database versioning (using a simple PHP script).
The Solution
Before we begin, scroll down and download the database backup and version control script that I have written in PHP.
The Bulletproof Method
This process is slightly lengthy and complicated, but once you get the hang of it, your web development process will be greatly streamlined. Also, you will be able to test your deployed versions before making them live. This method allows you to switch between versions really really quickly. It is advised for all heavy traffic websites.
Assumption: You have a heavy traffic website. You release version upgrades to your site regularly. Once in a while, you also modify the database schema. You would like to test your builds on the live server before letting your users view it.
Phase 1: Launching of version 1.0
Development Server
1. Design the database schema and program the first version of your script.
2. Before committing the script to the repository perform the following steps:
a. Create a folder called db and assign appropriate permissions
b. Run dbbvc.php in your browser and create a baseline. The baseline can be created with data or without data as per your requirements. This will create db/1.sql file which is basically mysqldump of the current database.
3. Once that is done, you can safely commit your script (to the trunk folder). Be sure to commit the db folder, along with 1.sql.
Note: When committing to your repository, make sure you do not commit any configuration files. All configuration files should go in as config.default.php etc. (This will help you as your server config.php will not get updated when you run svn update on the server.) As a rule, any file that differs from your development to production server, must be a .default file.
Production Server
Now suppose you want to launch this version you have committed.
4. First copy your trunk folder to a suitable version in your tags directory (e.g. tags/1.0) using the svn copy.
5. Now go to your server and use svn export to export the contents of tag/1.0 to /yourserver/www/1.0
6. Now create a folder called static in your www. The static folder is used to store those folders which will have user uploaded data.
For example, you may have /yourserver/www/1.0/images/profilepics
a. First create a folder called profilepics in www/static
b. Delete the /yourserver/www/1.0/images/profilepics folder
c. Create a symbolic link in your images folder using the following command:
sudo ln -s /yourserver/static/profilepics /yourserver/www/1.0
7. Once that is done, create your database and modify your config.php as per your server settings. Now run dbbvc.php in your server and select Backup & Version Upgrade. This will create add all the schema/data to your database.
8. Now make sure your server supports mod_rewrite and add the following file to the /yourserver/www folder.
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^$ VERSION/ [L]
RewriteRule (.*) VERSION/$1 [L]
</IfModule>
<IfModule !mod_rewrite.c>
ErrorDocument 404 index.php
</IfModule>
In our example we replace VERSION with 1.0 in the above code.
9. Now create a .htaccess file in the /yourserver/www/1.0 folder with the following contents. (just to be safe, not necessary)
<IfModule mod_rewrite.c> RewriteEngine Off </IfModule>
10. Once that is done, you can point your browser to your server url and it should work! If you encounter any errors, then feel free to post a comment.
Phase 2: Launching of subsequent versions
Development Server
11. Now let us say you are ready to roll out a couple of changes on the server. Also, let us assume you have made modifications to the database schema. Now everytime you make changes to the schema, you must create a sequential file (2.sql,3.sql…) in the db folder. If you forget to create this file and have already modified the database schema, you can use MySQL Workbench to reverse engineer and find out the changes.
12. Now you can safely commit this version to trunk. Be sure to include your 2.sql… files also. You may use a single .sql file containing all the db changes for that revision.
13. Once that is done, create a tag of your new version and head over to the production server.
Production Server
14. Now svn export the tags folder to /yourserver/www/1.1 and create your symlinks
15. Duplicate your database (create a new database for this tag and import the current database)
16. Point your browser to dbbvc.php, use the Backup & Version Upgrade option. This will upgrade the database to the latest version.
17. Now you can test your script, once your a ready to do the switch, all you have to do is edit /yourserver/www/.htaccess and change the VERSION to your latest version.
Now, the above steps may appear to be an overkill for a small website which can afford a bit of downtime. Also, you may not have permission to create symlinks and thus can’t use the above method. For that let us have a look at a semi bulletproof svn workflow.
The Semi Bulletproof Method
Phase 1: Launching of version 1.0
Development Server
Follow the same steps as the bulletproof method (Steps 1,2,3).
Deployment Server
1. Make sure you have created your tag, and use svn checkout to checkout the contents of your tags/1.0 folder to /yourserver/www folder
2. Edit your config.php file, create your database and run dbbvc.php to import your database.
3. Create .htaccess file in your /yourserver/www folder with the following contents:
<IfModule mod_rewrite.c> RewriteEngine On RewriteRule ^(.*)(.svn)(.*)$ http://www.yourserver.com [L] </IfModule>
4. Now you can point your browser to yourserver.com and it should work!
Phase 2: Launching of subsequent versions
Development Server
Follow the same steps as the bulletproof method (Steps 11,12,13).
Production Server
5. Now use the svn switch command to update the contents of your /yourserver/www folder.
6. Point your browser to dbbvc.php and update your database (if you have made any changes to schema).
Although the above method is simpler, and will work for most of your projects, there will be a definite downtime during your revision update and db schema update. During this upgrade, you may want to redirect all users to a temporary page using .htaccess.
Download the database versioning script (dbbvc)
Download the Database Version Control script. You will need to rename config.default.php to config.php and update the database details or you may edit dbbvc.php and require your own config.php/db.php.
Comments/Suggestions?
Do let me know your suggestions on how we can improve this workflow or anything else you want to add.
Spread The Word
If you like what you are reading, then please help spread the word by re-tweeting, blogging and dzone upvoting or use the ShareThis button below. Thank you.