7

So my partner and I are working on a practice management system and at first we didn't use any version control, then I persuaded him to use git. At the moment our system is that we have three branches: develop, master and release. Our git repo is on an external server, which has Apache, MySQL, etc. We do our daily coding on the develop branch (commits) and then we we have done some testing to make sure it's (semi) bug-free we merge it into the master branch. If we want to do something radical we just make a branch of master, mess around and if it works, good, we merge it back in, if not, we delete the branch.

Finally when we go for our weekly release we merge to the release branch to make sure we have a copy of the project as it was when we released. Unfortunately we don't track all files in git as it slows down git too much (e.g. images, especially since this is on an old server with a single-core 1.4 GHz AMD Sempron), as a git status usually changes from around 3s to about 1 minute; or because we can't (mysql databases). For us the slowdown is not workable so we just leave them out, and this has never seemed to be a problem because we haven't deleted any images, but we may start to soon as we start to optimize. So when we checkout previous commits sometimes the files aren't there and so it is broken (a big problem is we can't use version control on the database (because we don't know how), so because the schema has changed it is also broken if we go back).

And that's pretty much our workflow.

So after my background, my question is: Do you think that there are any improvements we could make to the above?

And on a side note: Does any know a reliable and easy-medium difficulty (i.e. not too hard) way to track mysql databases in git?

Improve this question

6 Answers 6

Reset to default
14

With distributed SCMs like git or mercurial (which is what bitbucket uses), branching is still possible but often unnecessary. What you do instead is (basic workflow):

  • Set up a repository in a location that is accessible for all developers (a network share, a server that people can ssh into, a third-party service like github or bitbucket). This will be your authoritative repository.
  • Clone this repository onto each developer's machine, and do whatever you have to to create a suitable development environment.
  • Work on your local copy; commit whenever you want to. Commits are local, so you won't hurt anyone. Be sure to pull from the central repo from time to time, so that your local copy doesn't go out of sync.
  • When you have something you think is ready for production, push from your local clone to the central repo.
  • On the central repo, whenever you have a version you want to mark as release candidate, tag it. This way, you can update any given clone to that exact version later on.

Since distributed SCMs allow unlimited clones to your repository, and each clone can serve as the master, you can build more complex workflows from this base. For example, you can have several central repositories: one for development, one for QA, one for production. You can use a dedicated clone just for a build server. You can use more than one clone locally; instead of branching, you just create another clone, and if it doesn't work out, you simply delete it.

Another nice benefit is that if the central repository breaks beyond repair, you'll still have several copies around; all you need to do is clone one developer's repo back to the central server and all is good (you do lose uncommitted changes on the central server though, but ideally, nobody should make changes there directly anyway, so the only thing left to do is for everyone to push their latest changes again).

Tracking databases is hard; normally, you want to track the structure, but not the data - data changes a lot, isn't really relevant for the code, and may contain sensitive information you better not make accessible that way. A common approach is to make a script that sets up the database in its current form, and maybe another that populates it with typical test data. Alternatively, you keep track of incremental change scripts - you'll have one script that creates the database as designed on day one, and then a bunch of scripts that, if applies in order, modify the database until you finally end up with the current state of things. Those are less reliable than tracking code changes though, and they require more manual work, so whatever you do, you want to have a good backup / restore plan for your production database.

Improve this answer
1
  • Thanks so much (I know how much effort it takes to right an answer like that)! There are some really great tips, so we must find a better way to set up a development environment on our clients. Again, thanks! Commented Sep 9, 2011 at 20:32
2

I think your concerns regarding git have been well-covered. The best VC for databases that I've seen is DB Ghost, or at least its concept. We are trying to adapt that to how we VC our DB as well in a preferably free manner.

Improve this answer
0
1

You can use mysqldump to track database state in git. Anything that your project uses (images and other binary data) should not be excluded (outside of compiled files, such as *.pyc). If system resources are an issue, look at using bitbucket as opposed to git. You can host an unlimited amount of private small projects for free (<= 5 developers I believe).

On another note, I rarely see anyone using branches anymore (that's soooo SVN ;)). Most people just work out of tip and tag it whenever they're happy with what they have.

Improve this answer
0

In a similar experience regarding database change tracking, we just handle structure of database. Using an ORM like NHibernate you can care about HBM mappings otherwise you can always maintain your database structure scripts.

Improve this answer
0

Here's a simple php script (I called it dbschema.php) that outputs the database schema in a simple format.

Run it as: ./dbschema db name db user db pass > schema.txt

Then put the output schema.txt under version control and you have a solution.

Everytime you change the schema, rerun the script.

The code is:

#!/usr/bin/php -q <? if (!isset($_SERVER["argv"][1])) die("ERROR: Need arguments\n"); if (!isset($_SERVER["argv"][3])) die("ERROR: Too few arguments\n"); $db_name=$_SERVER["argv"][1]; $db_user = $_SERVER["argv"][2]; $db_pass = $_SERVER["argv"][3]; mysql_connect('localhost', $db_user, $db_pass) or die ("ERROR: Could not connect to the database server\n"); $use_query="Use $db_name"; $use_result=mysql_query($use_query) or die("Error using " . $db_name . " | " . mysql_error()); // show the tables of the currently selected database $table_query="show tables"; $table_result=mysql_query($table_query) or die("ERROR: Error getting a list of tables from " . $db_name . " | " . mysql_error()); while($table_row=mysql_fetch_row($table_result)) { $table_name=$table_row[0]; echo "\n"."TABLE NAME " . $table_name . "\n"; // now describe the selected table $field_query="describe $table_name"; $field_result=mysql_query($field_query) or die("Error describing the table " . $table_name . " | " . mysql_error()); while($field_row=mysql_fetch_row($field_result)) { if (strlen($field_row[0]) >= 16){ $tab = "\t";} elseif (strlen($field_row[0]) >= 8){ $tab = "\t\t";} else{ $tab = "\t\t\t";} echo "$field_row[0]". $tab .$field_row[1]. "\n"; } } ?>
Improve this answer
0

I really like tdammers answer!

The one thing I would add is:

To address the issue of too many tracked files such as images, maybe those directories could be added to .gitignore and just handled separately. However this is not a great solution. You're probably better off buying a more powerful machine if you think machine power is an issue. Always consider Your Time X Your Hourly Rate in that cost calculation.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.