Baking a Cake – From the domain model to Cake models

Published on and tagged with baking a cake  cakephp  domain model  model  sql

Do you remember the domain model from a previous post? No? No problem, here it is again:

Domain model

The next logical step on the way from the domain model to the Cake models would be to refine the domain model to a database diagram. I say “would be” as it is something I almost never do, especially not for small projects. For me it isn’t worth the additional effort as a database diagram doesn’t add much value if you know some basic rules (Cake conventions). And well, I don’t have found a free tool for this task which convinced me ;-)

So I will go on and create the tables manually. For this purpose I will create a create.sql file in app/config/sql.

The first rule is that table names are plural. So we can write the SQL statement for the table for the “User” class (I omit the other classes as it is the same for them):

CREATE TABLE users (
);

The second rule is that each table has a primary key field with the name “id”. So we can modify the script from above to:

CREATE TABLE users (
    id INT(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

The third rule is optional, but you can add the fields “created” and “modified” to the table, which are later automatically populated by CakePHP:

CREATE TABLE users (
    id INT(11) NOT NULL AUTO_INCREMENT,
    created DATETIME, 
    modified DATETIME, 
    PRIMARY KEY (id)
);

The fourth rule affects 1:n relations: The “n” table must contain a field named “singular name of ‘1’ table” + “_id”. In our domain model the relation between User and Project is such a 1:n relation: one User can have many Projects. So we have to add the field “user_id” to the “projects” table:

CREATE TABLE projects (
    id INT(11) NOT NULL AUTO_INCREMENT,
    user_id INT(11) NOT NULL,
    created DATETIME, 
    modified DATETIME, 
    PRIMARY KEY (id)
);

The fifth rule affects n:n relations. Such relations cannot be mapped directly to the database, we have to introduce a join table. The name of this join table is “plural name of first ‘n’ table” + “_” + “plural name of second ‘n’ table” whereby the names are sorted alphabetically. In our domain model we have only one n:n relation, between User and Project. So the table name is “projects_users”. We also have to apply the fourth rule, as the relations between “projects” and “projects_users” resp. between “users” and “projects_users” are 1:n relations. And so we get:

CREATE TABLE projects_users (
    project_id INT(11) NOT NULL,
    user_id INT(11) NOT NULL
);

Now we can add the attributes from the conceptual classes as fields to the respective tables and write the drop statements. And we get:

CREATE TABLE users (
    id INT(11) NOT NULL AUTO_INCREMENT,
    created DATETIME, 
	modified DATETIME, 
    PRIMARY KEY (id)
);

CREATE TABLE projects (
    id INT(11) NOT NULL AUTO_INCREMENT,
    user_id INT(11) NOT NULL,
    created DATETIME, 
    modified DATETIME, 
    PRIMARY KEY (id)
);

CREATE TABLE messages (
    id INT(11) NOT NULL AUTO_INCREMENT,
    project_id INT(11) NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    created DATETIME, 
    modified DATETIME, 
    PRIMARY KEY (id)
);

CREATE TABLE feeds (
    id INT(11) NOT NULL AUTO_INCREMENT,
    user_id INT(11) NOT NULL,
    url VARCHAR(255) NOT NULL,
    created DATETIME, 
    modified DATETIME, 
    PRIMARY KEY (id)
);

CREATE TABLE projects_users (
    project_id INT(11) NOT NULL,
    user_id INT(11) NOT NULL
);

And in app/config/sql/drop.sql:

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS messages;
DROP TABLE IF EXISTS feeds;
DROP TABLE IF EXISTS projects_users;

To execute these SQL scripts I use a simple Ant script (app/config/sql/build.xml):

<?xml version="1.0"?>
<project name="n" default="default">
    <property name="driver" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql://localhost/n?characterEncoding=UTF-8"></property>
    <property name="user" value="root"></property>
    <property name="password" value=""></property>
	
    <target name="default">
        <sql driver="${driver}" password="${password}" url="${url}" userid="${user}" src="drop.sql" encoding="UTF-8"></sql>
        <sql driver="${driver}" password="${password}" url="${url}" userid="${user}" src="create.sql" encoding="UTF-8"></sql>
    </target>
</project>

After executing the Ant script, we are ready to bake the models. I will use the bake script (available in cake/scripts) to generate the User model, as I was asked to show how bake.php is used. I think it should be self-explanatory:

Baking model, part 1

Baking model, part 2

This will create the following model in app/models/user.php:

<?php
class User extends AppModel {
    var $name = 'User';

    //The Associations below have been created with all possible keys, those that are not needed can be removed
    var $hasMany = array(
        'Feed' => array('className' => 'Feed',
            'foreignKey' => 'user_id',
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'dependent' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''),
        'Project' => array('className' => 'Project',
            'foreignKey' => 'user_id',
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'dependent' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''),
    );

    var $hasAndBelongsToMany = array(
        'Project' => array('className' => 'Project',
            'joinTable' => 'projects_users',
            'foreignKey' => 'user_id',
            'associationForeignKey' => 'project_id',
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'unique' => '',
            'finderQuery' => '',
            'deleteQuery' => '',
            'insertQuery' => ''),
    );
}
?>

It generates also a test case for the model in app/tests/cases/models/user.test.php:

<?php 

loadModel('User');

class UserTestCase extends UnitTestCase {
    var $object = null;

    function setUp() {
        $this->object = new User();
    }

    function tearDown() {
        unset($this->object);
    }

    /*
    function testMe() {
        $result = $this->object->doSomething();
        $expected = 1;
        $this->assertEqual($result, $expected);
    }
    */
}
?>

As you see, when using the bake script you have to answer many questions. That’s a bit of a pain when you want to bake multiple models. And if you don’t want to write tests you have to remove them (that’s at least what I do with files which are not used). These are the reasons I prefer to write the models manually.

Here are the other models (please notice that I omit the $name variable as I am a) using PHP5 and b) lazy):

// app/models/feed.php
<?php

class Feed extends AppModel {
    var $belongsTo = array('User');
}
?>
// app/models/project.php
<?php

class Project extends AppModel {
    var $hasMany = array('Message');
    var $hasAndBelongsToMany = array('User');
}
?>
// app/models/message.php
<?php

class Message extends AppModel {
    var $belongsTo = array('Project');
}
?>

To test whether we defined the associations correctly, we can create for all models the corresponding controllers. Here the UsersController (the other controllers are similar):

// app/controllers/users_controller.php
<?php

class UsersController extends AppController {
    var $scaffold;
}
?>

With that, we have something to play, we can add, edit, and remove records. But it isn’t very useful yet ;-)

That’s it for today.

(other articles from this series)

8 comments baked

Bake a comment




(for code please use <code>...</code> [no escaping necessary])

© daniel hofstetter. Licensed under a Creative Commons License