When I started to work in a virtual team comprising of 6 developers , I felt that the most time consuming task was syncing the database changes amongst team members. As we were using SVN , we didn’t have to worry about the files but the DB schema was always an issue. So I started to look into something solid which we could couple of with SVN client in order to make this process as smooth as possible. Blame it on my search capabilities or eye sight but honestly i didn’t come across something I liked. So i devised a mechanism which might be far from ideal but which is working well for us. Keep in mind that we all are working in Windows environment (as the client is using Windows server) , so some steps might be slightly different for people in the Linux camp.
Here is what I did :
Step 1:
I located a ready made script which would let me sync 2 databases. I found a well written class by “Diogo Resende” here
Step 2:
I modified the class , so that instead of actually syncing the databases , it would just return the queries which it is going to run in order to sync the databases.
Step 3:
Now I created an exact copy of my current database , lets call this database “test”. The called the copy i created “svn_test”. Keep in mind that my working database is still “test”. After this point I never touched “svn_test”.
Step 4:
I created a small database on my online server with a table called “changes” with change_id , change_set , submit_time fields. “change_id” was set to be primary and auto increment. I created a one page management script for this and placed the script on the remote server to be easily accessible by all. Lets say the location of this script is http://something.com/changes_list.php. This script lists all the records in descending order. I can edit , delete and add records from this script as well.
Step 5:
I created a PHP script called “pre_hook.php”, which would compare “test” and “svn_test” and return to me all the queries. These queries were stored in the remote database table called “changes” , it would store the last returned “change_id” in a file called “change-id.txt”. It would also generate a file called “changed.txt”, if there was no changes , nothing would be submited to the remote DB and the file “changed.txt” will be deleted if it were present. This script also created a file called “run.bat” , if there were any new queries , the script would insert explorer “http://something.com/changes_list.php“ in the “run.bat” file , otherwise it would just insert “exit” in it.
Step 6 :
I created another script called “post_hook.php” , it would check if “changed.txt” was present , if so it would replace the database “svn_test” with “test”.
Step 7:
I created yet another script called “post_update.php” , which would connect to the remote database and tried to find query the records which were entered after its last run. The script stores the highest “change_id” it got in a text file. So next time it would only try to query for records which are higher than the stored “change_id”. If the script got any new records from the remote database. It would parse the queries in each record and run them on database “test”.
Step 8:
As we all use Tortoise SVN , I created 4 hooks. A hook on “start commit” , this hook would run the “pre_hook.php” script. Hook 2 was created on “pre commit” which would run the “run.bat” file created by pre_hook.php. Hook 3 was created on “post commit”, this would run the “post_hook.php” script. Hook 4 was created to on “post update” , this would run the “post_update.php” script.
How it works (or is supposed to work):
After all the above steps. I goto the database “test” and make a change to xyz table. Now remember that “svn_test” DOESNT have this change. So now i continue to work on php scripts and I decide to commit to SVN. So what will happen is when the commit starts the “pre_hook.php” will gather all the schema changes (if any) , send them to the remote MySQL database , place the link to the remote script I deployed at http://something.com/changes_list.php inside the “run.bat” file. BEFORE actually sending the files to the SVN , the” run.bat” would be executed and it would show me the list of changes that were just submitted , so that i can view them manually as well and adjust if needed , which is rare . After the commit is complete , the “test” database is copied over “svn_test” by “post_hook.php”. Next time someone updates the repository , the “post_update.php” script will kick in after the update is complete and return all the new changes submitted to the remote database. Now that persons database has the changes I made to mine as well. The remote DB management script also lets us input other queries as we may please. So that on next update everyone can get the changes those queries reflect.
This system is not perfect, but it saves us plenty of time. I am still thinking/searching for a better solution , which takes care of the changes in the data as well but for now this is what I have.
Hoping that this would help someone else as well.
