Store git activity in MySQL with PHP

Git hooks are saving me so much time and providing me with interesting solutions to problems I didn't even know I had. I can't be the only person who this would be useful to, so give it a go.

As I said, I work on loads of sites, and keeping track of what's been done and where is sometimes a bit of a pain. I keep a todo list, but if I get an emergency email from someone, chances are that won't go through my todos. It will, however, be put into version control.

So this morning I had the bright idea to write a git hook that pushes relevant information to MySQL so that I can run activity reports later. All my bare git repositories are stored in a directory on our dedi, so it's just a matter of making sure each repository has the post-receive hook in. I do this by keeping the actual hook in the same directory as all my repositories, then symlinking the hook into the appropriate place with the following little script. Obviously, this assumes that your post-receive hook is in the same place as your repositories, and that you want this hook everywhere. But that's all true, so we're all good. Once you've run the linked script, you'll only have one hook to maintain and every time you create a new repository, you can just run the script again and everything will all be up-to-date.

Now for the hook. It's not beautiful PHP, but little scripts like this rarely are, in my experience.

Create this table:

`id` int(10) unsigned NOT NULL auto_increment,
`repo` varchar(255) NOT NULL,
`commit` varchar(40) NOT NULL,
`date` datetime NOT NULL,
`message` text NOT NULL,
UNIQUE KEY `commit` (`commit`)

Here's your script. chmod +x it.

#!/usr/bin/env php <?php
$repo = rtrim(array_shift($pwd),'/');
$repo = substr($repo,strrpos($repo,'/') + 1);
$db = new PDO('mysql:dbname=DB;host=','USERNAME','PASSWORD');
exec('git log --all --pretty=format:"%H%n%ct%n%s%n%b%n<><><>"',$capture,$log);
if ($capture){
    // preprocess the log
    $commits = array();
    $current = array();
    foreach ($capture as $row){
        if (trim($row) === '<><><>') {
            $commits[] = $current;
            $current = array();
        } else {
            $current[] = $row;
    $v = array();
    $b = array();
    foreach ($commits as $commit){
        $sha = $commit[0];
        $m = $commit[2] . (trim($commit[3]) === '' ? '' : "\n\n. implode("\n",array_slice($commit,3)));
        $d = date('Y-m-d H:i:s',$commit[1]);
        $v[] = '(?,?,?,?)';
        $b[] = $repo;
        $b[] = $sha;
        $b[] = $m;
        $b[] = $d;
    $stmt = $db->prepare('insert ignore into log (repo,commit,message,`date`) values. implode(',',$v));
    try {
        if ($stmt) {
            if (!$stmt->execute($b)) throw new PDOException;;
        } else Throw new PDOException;
    } catch (PDOException $e) {
        mail('EMAIL','Commit did not reach db',$e->getMessage());

So basically we're extracting the log data we need, doing some funky stuff to handle multi-line commit messages (I like to store lots of details as my subject messages tend to be a bit vague!). Other than that, if you're familiar with PHP, the above should be pretty self-explanatory. If it's not, hit the comments and I'll explain things.

I've only been using this a little while, but it seems to work very well. If you use it and stumble across any bugs, I'd love to know about them!

Update: I've today realised that git log only logs the currently-selected branch, or master on a bare repo so I've added the --all switch to git log so I can get the logs for every branch. Most of it's just "Merged blah" but that means it can be filtered easily and I'd rather have everything and need to filter than be missing something important.

PHP 5.3, iconv, OSX, Symbols Missing _libiconv

I'm not sure how many people will have this problem, but I did so this will remind me.

PHP 5.3 release, for some reason, does not like my default install of iconv. Firstly, I made the grave mistake of failing to point PHP properly at an install in /usr/local/, so replaced my OS installation of iconv with a new one. This was all well and good until I started opening apps that needed specific parts of OSX's default iconv. So, an OSX restore later, we're at square one. PHP keeps telling me various crap when I'm trying to compile that it didn't work with iconv because it's the wrong version, but would fail during make because it couldn't find the correct symbols. <!--more--> I had compiled and installed iconv (./configure --prefix=/usr/local/iconv --enable-static) then pointed PHP at that (--with-iconv=/usr/local/iconv) and it was failing during make with Undefined Symbols: "_libiconv" then some stack trace of php string functions. Great. After much Googling and cobbling together my own way to do this with trial and error and a mixture of other people's part-baked solutions, I have come up with the following method:

  1. install iconv: ./configure --prefix=/usr/local/iconv --enable-static && make && sudo make install
  2. configure PHP with the following prefix: env LIBS="-lresolv -liconv" ./configure ......

    Mine is:

    env LIBS="-lresolv -liconv" ./configure \ --enable-pdo \ --with-apxs2=/usr/local/apache2/bin/apxs \ --with-mysql=/usr/local/mysql \ --with-pdo-mysql=/usr/local/mysql \ --prefix=/usr/local/php \ --enable-mbstring \ --with-mysql=/usr/local/mysql \ --enable-cli \ --with-curl \ --with-gd \ --with-png-dir=/usr/local/png \ --with-jpeg-dir=/usr/local/jpg \ --with-freetype-dir=/usr/local/freetype \ --with-zlib-dir=/usr/local/zlib \ --with-mcrypt=/usr/local/mcrypt \ --with-iconv=/usr/local/iconv

  3. After configure has finished running, hopefully there has been no problem. If there has I have no idea how to fix it. Normally this runs smooth as hell for me, this has taken me about 6 solid hours to fix and I can't be remotely fixing everyone else's, sorry.

    You'll need to be modifying the Makefile generated from your ./configure. Search for the following string (without quotes) "libs/libphp$" and replace that and the next line with the following:


  4. make
  5. You can run make test if you want, but it'll fail everything it doesn't skip
  6. sudo make install
  7. Restart apache
  8. Everything should work fine now. If it didn't, I'm sorry - I don't know why. If it does, hoorah! You just saved yourself 6 hours. You owe me a beer!

Again, I must reiterate, if this doesn't work for you I probably won't be able to fix this or even help you slightly. This is just what worked for me. There are plenty of things you can try with a bit of Google-fu, though.

299: mitb: razor


I'm really enjoying playing with this Raynox. You need a steady hand, though, and after 3 hours of trying to get the PHP 5.3 RC to play nice with MySQL (lucky for me all of my local databases are for development only and I have all the create scripts!) my composure is a little, well, I have crappy composure anyway.

This weekend will hopefully be fun. I want to go and see Hsiao Ning, but I bet the weather will suck.

Working days

I'm all for reusable code. Whenever I'm coding, I'm constantly wondering if there's a way that I can further abstract what I've done so I can use it somewhere else. Because of this, I came across a problem yesterday that got the better of me a little.

I'm working on a project at the moment that requires me to have an awareness of "working" days. Specifically, when a product is sold, the customer has three working days to cancel their order. This would be all well and good if there was 365 working days in the year, and all companies were the same. I started out with this assumption, but was quickly confounded.

Before I explain how I solved (well, only partially - it would require a calendar app to fully solve it to my satisfaction!) it, I'll explain a little about how some classes come to be in my workflow.<!--more-->

When working in PHP, I have around 5 or 6 classes that I take everywhere with me. One for generating a class that represents every database table, one for DOM generation, one for database access, one for laying out application-specific database entity grouping (how hard can one try to avoid saying the word "model"?!) and one that acts as a sort of "other" class and includes functionality that doesn't fit elsewhere. This is a single class (called "core", if you're interested) that contains a bunch of un-interrelated static functions. It acts as a breeding ground for functionality that gets bigger and bigger (either in the file or my head) before I give up and move it to its own domain. An example of this was back when I started this way of working and my database access functionality was in here. In a perfect world, only my __autoload() (best function ever, by the way) function would be here but, as I said - in a perfect world!

So, I have my core::nWorkingDaysPassed function, and all is well. The first argument is the date you're counting from, the second is the date you're counting to (defaults to now), the third is the number of days you want to count (defaults to 3 because that's what I'm working on now) and the 4th is an array of days of the week that are in your working week, defaulting to Monday through Saturday, again because of what I'm working on now.

I've already spotted a problem with this function - it has too many arguments. I don't like passing a lot of arguments to a function, and I think this is probably to do with my grounding in PHP - the argument orders in PHP's core functionality is so varying that it's incredibly difficult to remember what they are, even for functions you use every day! Luckily the documentation is good enough to counteract this. By the time I've finished this thought, I've spotted another problem - it doesn't take public holidays into account. To take public holidays into account, it's going to need to be an object of its own. I can't get that much functionality into one function. Because of time constraints, and authorisation from the client, holidays are no-longer a concern (as they're open, thankfully!).

On to solving my problem. It'll be a lot easier to write this in pseudo-code. I know this because I just deleted the verbose version!

normalweek = ('mo','tu','we','th','fr','sa','su')
workingweek = normalweek
for each workingweek as day:
    if day is not in argumentworkingweek:
        remove day from workingweek
weeksspanned = argumentdays / 7 rounded up to nearest 1
allweeks = merge weeksspanned*normalweeks
i = first instance of argumentfrom weekday in allweeks
j = i + argumentdays
while i < j:
    argumentfrom = argumentfrom + 1 day
    if allweeks[i] is in workingweek: # normal working day
    else: # shouldn't count towards our total
        append a day to allweeks # so we don't run out!
if argumentfrom >= argumentto: # that amount of days has passed
    return True
    return False
I realise that commenting pseudo-code largely invalidates the need for pseudo-code but it started to look like Python and I didn't want to spoil it!

Looking back at it now, I don't know why it caused such a headache but I'm just one of those people who has problems with the difference between dates. I hope this helps someone, though I doubt it will!

PHP, mail() and OSX Leopard

So I couldn't figure out any way of doing this, and I can't stand stuff like this beating me so I've been messing about with it for the last hour and it's finally working so I thought I'd share what I've done. Maybe this will get spidered, but mostly it'll be here for my future reference!

The long and short of this situation is that OSX includes sendmail, but it seems to be some sort of alias for postfix, so you should actually be configuring postfix. Right now, I should probably clear up that I'm by no means an expert on any of this and it's all been trial and error for me so far so if it doesn't work for you, I can try and help but I can't promise anything.<!--more-->

There are 4 files I used for the following:

  • /etc/hostconfig
  • /etc/postfix/ (or - thanks Mike Birch)
  • php.ini (this could be anywhere depending on your installation, mine's in /usr/local/php5/lib/)
  • /var/log/mail.log
firstly, sudo nano -w /etc/hostconfig and add the following line:
then sudo nano -w /etc/postfix/, find the myhostname variable (by default it's host.domain.tld), uncomment it and change it to your domain (if you're on a machine that doesn't have a DNS, you can make it a domain that you're responsible for so that it doesn't get shut down at the receiving end, but please don't make it or something like that!)

now, open php.ini and look for the sendmail_path variable, uncomment it, make its value sendmail -t -i, save then restart apache. I'm not really sure if this is 100% necessary as there's a comment above that says this is the default value anyway, but it can't hurt!

now open a terminal window and execute the next couple of commands:

% sudo postfix start
% tail -f /var/log/mail.log
finally, create a file called mail.php (or whatever!) and add the following to it:
  '[email protected]', // your email address
  'Test', // email subject
  'This is an email', // email body
  "From: Me <[email protected]>rn" // additional headers
obviously replace [email protected] with your email address and [email protected] with a valid email address (domain at least, as some mail servers will bounce your email if the sender's domain isn't real). Now navigate to your mail.php file (likely http://localhost/mail.php) and watch your terminal window to see that it's been sent successfully. If it hasn't, let me know if you fixed it and I'll update this - it's annoying to me that there isn't really an answer to this question that I can find so the more comprehensive this is, the more helpful!.

Useful bit, added by Terry Thorne in the comments:

I just thought I’d add for those looking to do this who find that their ISP blocks port 25 you have to route through their server. On Snow Leopard edit /etc/postfix/ and add at the end: mydomain = myorigin = $mydomain relayhost = Obviously replacing with the domain of your ISP (usually the suffix of your email address) and the with your ISP’s smtp server addresss Taken from: