DaveDevelopment::Dave Marshall

There’s been some discussion recently on why active record:

1. Sucks
2. Sucks - but not like that;
3. Doesn’t suck

I like the active record pattern, so I don’t think it sucks, but I do think it’s used a little out of context sometimes.

If you’re building a small lightweight app, then I think using your Data access layer as the M in MVC is a logical thing to do. It’s quick, it’s easy and you can extend either your active record in Rails, or extend your Table DataGateway in the Zend Framework and you wont go far wrong.

As soon as your app gets a little more complex, you might want to start creating custom
models that contain more business logic than simply pulling and pushing to the database. If your application is complex enough, chances are your model will need to interact with more than one database table, if not database, so at this point, like Bill Karwin pointed out, your model should be using the DAL, not being the DAL. Loosening the coupling between model and DAL, should also help with automated testing the business logic, in that mock objects could replace the DAL.

The only problem is, I don’t know the best way to do it.

I’m currently learning the ways of the Zend Framework and would be interested to see how people think the best way to implement this kind of complex model. I’m currently leaning toward something like this. I’ve included a Zend_Form object, to show how the Persons model encapsulates more logic than just pushing to and from the database. I think the biggest benefit of Zend_Form is validating input, which I consider domain logic, so should be part of the model. But I’m not sure the best way to make things easily testable, without pushing into the realms of fancy Dependency Injection and what not, which I’m not all that familiar with.

File: application/models/Persons.php

<?php

class Persons
{
    public function findByEmail($email)
    {
        $table  = self::getTable();
        $select = $table->select()
                        ->where('email = ?', $email);
        return $table->fetchAll($select);
    }

    public static function getTable()
    {
        // add some dependency injection?
        return new Persons_Table();
    }   

    public static function getForm()
    {
        // add some dependency injection?
        return new Persons_Form();
    }

    // ...
}

File: application/models/Persons/Table.php

<?php

class Persons_Table extends Zend_Db_Table
{
    protected $_name = 'persons';

    // ...
}

File: application/models/Persons/Form.php

<?php

class Persons_Form extends Zend_Form
{
    // ...
}

At first it may seem that the Persons model just ends up acting as a proxy to the Persons_Form and Persons_Table, but once you start writing methods that use both together, you’ll start seeing fatter models and thin controllers, which is all good.

This really is a request for comments really, as I’m personally not sure about the best way to go about this. Would be interesting if any of the people using the MVC part of the Zend Framework in the real world go about this?

Started writing this post over a week ago, kind of lost interest, but I polished it off…

After reading a couple of posts this week arguing the corner of both Ruby on Rails and Symfony, I found myself thinking about which framework I like the most. I came to the conclusion that I don’t really have a favourite, just the one I’m currently using. I’ve used the two previously mentioned frameworks, CakePHP a long time ago and more recently the Zend Framework. I like them all. They all do similar things, sometimes in different ways, but having spent a lot of time maintaining and developing ‘frameworkless’ sites, I think they’re all mega. Admittedly, my PHP is far stronger than my Ruby, so I steer towards the PHP ones, but that has nothing to do with the frameworks themselves.

Anyway, this lead me to have a little look on Google Trends, to see how many people are searching for what on the framework front, I included the previously mentioned four and Django, which is very popular, but my Python is worse than my Ruby, so I’ve not used it. I know this is hardly conclusive, but it was worth a look.

The results are pretty much what I expected. Django looks like it’s a more common search term, but is starting to rise recently. Zend, CakePHP and Symfony are all slowly on the up, whereas Rails clearly had a big boom over the previous two years and is now settling down a bit.

Introduction

This How To will introduce some simple database migrations to your PHP application. Ruby on Rails is a popular web application framework, that provides a method of migrating (upgrading) the applications database programatically, keeping the database schema essentially version controlled. This allows individual developers to update their working databases and the databases on testing, staging or production machines to be updated with new versions of applications. The CakePHP framework has recently developed a migrations library simliar to rails, but this article focuses on using seperate tools to run database migrations, a build tool called Phing, along with a method for creating database migrations, dbdeploy.

Install Phing

I always use the beta or release candidate of phing and for the purposes of this article I suggest you do too. The best way to download and install phing is using PEAR. This can be done on Linux or Windows assuming you have the pear script in your PATH with three shell commands.

shell> pear channel-discover pear.phing.info
shell> pear config-set preferred_state beta
shell> pear install phing/phing

Example Application structure

As an example, we’re going to develop a simple application with the following directory structure.

example/
 |-- db/
 |   `-- deltas/
 |-- deploy/
 |   `-- scripts/
 |-- library/
 `-- public/

The db directory contains sql files for using and manipulating our database and
the deploy directory contains our build scripts that set the migrations in motion. The library directory contains our application code and the public folder will contain scripts and files accessible directly from the web, but will not be the focus of this article.

Build scripts

This section shows you how to develop the build scripts that will run the database migrations. The first file we need to create is a simple configuration file and should be fairly self explanatory. The file is written as key=value, lines beginning with a # are comments. Open your editor and save the following text as deploy/build.properties.

# Property files contain key/value pairs
#key=value

# This dir must contain the local application
build.dir=../

# Credentials for the database migrations
db.host=localhost
db.user=user
db.pass=password
db.name=example

# paths to programs
progs.mysql=/usr/bin/mysql

The next file we are going to create is the deploy/build.xml file. This is the file that tells Phing what we want it to do. I’m not going to go into too much detail describing each part of the build file, there are some comments, but you should consult the Phing Documentation for further details and enhancements.

<?xml version="1.0" ?>
<project name="PurpleMonkey" basedir="." default="build">

    <!-- Sets the DSTAMP, TSTAMP and TODAY properties -->
    <tstamp/>

    <!-- Load our configuration -->
    <property file="./build.properties" />

    <!-- create our migration task -->
    <target name="migrate" description="Database Migrations">  

        <!-- load the dbdeploy task -->
        <taskdef name="dbdeploy" classname="phing.tasks.ext.dbdeploy.DbDeployTask"/>

        <!-- these two filenames will contain the generated SQL to do the deploy and roll it back-->
        <property name="build.dbdeploy.deployfile" value="deploy/scripts/deploy-${DSTAMP}${TSTAMP}.sql" />
        <property name="build.dbdeploy.undofile" value="deploy/scripts/undo-${DSTAMP}${TSTAMP}.sql" />

        <!-- generate the deployment scripts -->
        <dbdeploy
            url="mysql:host=${db.host};dbname=${db.name}"
            userid="${db.user}"
            password="${db.pass}"
            dir="${build.dir}/db/deltas"
            outputfile="${build.dir}/${build.dbdeploy.deployfile}"
            undooutputfile="${build.dir}/${build.dbdeploy.undofile}" />

        <!-- execute the SQL - Use mysql command line to avoid trouble with large files or many statements and PDO -->
        <exec
            command="${progs.mysql} -h${db.host} -u${db.user} -p${db.pass} ${db.name} &lt; ${build.dbdeploy.deployfile}"
            dir="${build.dir}"
            checkreturn="true" />
    </target>
</project>

That’s essentially all the magic we need. Now we just need to create our database.

Writing dbdeploy delta scripts

We haven’t actually created our database, so rather than create it the traditional way, we will actually use the migrations to create the initial schema. We’ve not actually decided what our example application does yet, but seeing as most tutorials make blogs, why don’t we give that a bash. We’ll start simple, one table with three columns called post.

Dbdeploy works by creating numbered delta files. Each delta files contains simple SQL to both deploy the change and roll it back. The basic layout of a delta file is like so.

--//

-- Run SQL to do the changes

--//@UNDO

-- RUN SQL to undo the changes

--//

We are creating our initial schema, so put the following content in db/deltas/1-create_initial_schema.sql

--//

CREATE TABLE `post` (
    `title` VARCHAR(255),
    `time_created` DATETIME,
    `content` MEDIUMTEXT,
);

--//@UNDO

DROP TABLE `post`;

--//

Migrating the database

We are one step away from running our first migration. To keep track of the current version of the database, dbdeploy requires a table in the database. This is the only time we will have to interact with the mysql client directly.

shell> mysql -hlocalhost -uroot -ppassword example
mysql> CREATE TABLE changelog (
  change_number BIGINT NOT NULL,
  delta_set VARCHAR(10) NOT NULL,
  start_dt TIMESTAMP NOT NULL,
  complete_dt TIMESTAMP NULL,
  applied_by VARCHAR(100) NOT NULL,
  description VARCHAR(500) NOT NULL
);
mysql> ALTER TABLE changelog ADD CONSTRAINT Pkchangelog PRIMARY KEY (change_number, delta_set);

We are now ready to run our first migration and create the initial schema for our application.

shell>cd deploy
shell>phing migrate

All being well, we now have a posts table in our database. But what about an author for our blog posts? We’ll have to add another table and a foreign key from the post table to author table. To do this we create another delta, we call this one db/deltas/2-create_author_and_link_to_post.sql

--//

CREATE TABLE `author` (
    `author_id` INT(10) unsigned auto_increment,
    `name` VARCHAR(255),
    PRIMARY KEY (`author_id`)
);

ALTER TABLE `post` ADD `author_id` INT(10) unsigned NULL;

--//@UNDO

ALTER TABLE `post` DROP `author_id`;

DROP TABLE `author`;

--//

Run our migrations again.

shell> cd deploy
shell> phing migrate

Conclusion

That’s pretty much it, you’ve seen how to create database deltas and use them to migrate your database, if you can’t be bothered to copy and paste things to try for yourself, download the example application.

There are plenty of caveats when it comes to version controlling databases, especially if you branch and merge your application code, some are detailed in the dbdeploy documentation

This tutorial is probably incomplete or wrong in plenty of ways, if you think you have something to point out, please leave your comments below

Been a long time since I’ve posted, taken a step back from freelance work as of late and I’ve been doing lots of other things. One of them is reading David Allen’s Getting Things Done. Thus far I’ve found it pretty interesting and just knowing that I should organise myself better has lead to some improvements. I’ve started using some software to keep track of things, it’s a Ruby on Rails application based on the principles of Getting Things Done, I just run it locally using webrick and it’s quite nice, definitely worth a look.

I’ve finally gotten around to finishing the basic design on the Broadband affiliate site project I’ve had ongoing for a while and figured it would be nice to have a backend to add/remove, de-activate all the different packages etc. So, I decided to go crazy and have a look at Ruby on Rails.

Now I’m not extremely well versed with patterns and so on, but I have a decent understanding of the MVC style architecture that seems to be popular these days and I’ve seen a few tutorials on Symfony, a PHP 5 web framework which I believe is fairly similar in nature to Rails, so I figured I’d get along okay. I feel I was pretty right, in a few hours over today and yesterday I’ve come out with a working database driven website and administration area running on Ruby on Rails.

Running Gentoo, I always try and use the portage package management tool, to be double sure on what I needed to do I had a little search on google which led me to this page, and these commands.

echo "dev-ruby/rails mysql fastcgi" >> /etc/portage/package.use
emerge -av rails
rails /path/to/app

That all went well and I was sat looking at a clean rails application. I fired up the builtin webserver, WEBrick, it worked, so I closed it again. Seeing as I use Apache on all my servers, I wanted to use it for the development aswell. I’ve never used FastCGI before, but what do you know there was an example virtual host definition in the rails README file.

  <VirtualHost *:80>
    ServerName rails
    DocumentRoot /path/application/public/
    ErrorLog /path/application/log/server.log

    <Directory /path/application/public/>
      Options ExecCGI FollowSymLinks
      AllowOverride all
      Allow from all
      Order allow,deny
    </Directory>
  </VirtualHost>

Once again, this worked a treat. I then set about creating my application, which by enlarge went along without many flaws, using a combination of the excellent Rails API, this Create a weblog in 15 minutes screencast and Rolling with Ruby on Rails as guidance. The biggest problem I had was creating initially trying to create the scaffolds. Not knowing RoR’s naming conventions, I’d foolishly named my database tables in the singular form, ie provider rather than providers. After overcoming this, I rolled on pretty nicely.

Using this page as guidance I used the login_generator to create a simple admin area, and the scaffolding stuff filled most of the admin pages for me, just a few tweaks here and there were required. The public face was even easier, basically allowing a few different ways to filter the list of broadband packages through one action, ‘list’.

As far as the language Ruby itself goes, I’ve barely learnt anything, basically because Rails does it all for me. The most complicated things I did code wise, was using a case statement to change the filtering on the public packages page and this simple function for turning bytes into a more readable form.

        def human_readable(number)
                count = 0
                while number/1024 > 1
                        number = number/1024
                        count += 1
                end

                iec = ['', 'K', 'M', 'G', 'T']
                return number.to_s + iec[count]
        end

My only gripe with this so far, is the speed. It does take forever to generate these simple pages, the built in webserver does seem to be a shade quicker, but I’d still rather use apache. I’m sure there’ll be some tweaks I can make to speed the FastCGI module up, but there’s no rush for that now.

Hopefully I’ll signup for a few affiliate programs and launch the site properly within the next few days, I don’t expect to make a fortune but it’s been a good little learning utility so far and I hope to use it to learn a few things about affiliate marketing.