3

My project is a collection of PHP scripts using MySQL as a database and needs to be installed locally using WAMP/LAMP/MAMP.

Previously I've been sending the users a link to a zipped archive and having them overwrite it, but since I took the plunge to GitHub, I've realized that there are far better ways; namely Service Hooks in GitHub. However, this would work fine as long as I don't alter the database in any way, which is a good possibility.

I've been toying with the idea of how I would implement this, but I can't find a clear solution. So far I've concluded with that I need to have a directory (say update/) which contains .sql files for each update. The PHP script will then check said directory for a file corresponding with the new version number (not sure how I will define a version number; I was thinking of using the commit ID, but that won't be available until after the commit, so...).

I would love some input on this!

John Doe
  • 298
  • 1
  • 3
  • 16
  • Does the SQL file need to have both the structure and the data? – Tarek Fadel Sep 09 '11 at 15:36
  • @TarekFadel It doesn't actually have to be an sql file. I was thinking more along the lines of a PHP file which will execute some SQL queries to update the current database with new structure, addons, fields etc. – John Doe Sep 09 '11 at 16:15

2 Answers2

2

Here's how I would tackle this (not the most elegant or performant):

  1. Add a flag in the DB with a version number
  2. Add a min-version number in your DB layer PHP file
  3. Check that the DB version is greater than the min-version
    • If it is: continue about your business
    • Else: Run the PHP file in update/ which would have a series of ALTER TABLE commands to be run on the DB server
      • Update the min-version number in the DB to the latest number
  4. All done

Alternately instead of querying the DB you can have a file which is generated by your DB interface PHP file (and ignored with .gitignore) which you can just as above.

Tarek Fadel
  • 1,909
  • 1
  • 14
  • 22
1

I would really recommend checking out Doctrine and its migration feature.

This does exactly what you are looking for, plus you get a very nice tool for working with all other aspects of your database handling.

Tobias Sjösten
  • 1,084
  • 9
  • 16