About

SpiffyDb extends the basic functionality of Zend_Db_Table and Zend_Db_Table_Select and includes a Model/Mapper as shown in the Zend Framework Quick Start. These new classes include a hoist of new features and automate much of the model/mapper functionality that is common to them.

Features

• Automatic model getter/setters using the magic method __call.
• Validation/filtering of model values with methods such as isValid() and saveValid().
• New get() method (and helpers) which is used to generate joined queries.
• New methods query() and queryOne() which can be used with the new get() method to query prepared statements.
• Much more, see below for usage.

Getting SpiffyDb

You can checkout the latest source from the or download the latest version of SpiffyDb.

Examples

Below are a few examples of the new features in SpiffyDb. If you’re curious how something works post a comment and if it’s a good question I’ll add an example to this list. Our example is going to be geared around creating events for a game. The events table will be joined with a user table to determine the event owner, a guild table to specify which guild the event is for, and a character id to specify which character will be leading the event.

Setting Up Tables

For our example we’re going to setup a Table, Model, and Mapper for user events. Below is the SQL information relative to the table.

CREATE TABLE `event` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `guild_id` int(11) NOT NULL,
  `character_id` int(11) NOT NULL,
  `title` varchar(64) NOT NULL,
  `description` text NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `owner_id` (`character_id`),
  KEY `guild_i` (`guild_id`),
  KEY `user_id` (`user_id`)
);

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(16) NOT NULL,
  `password` char(100) NOT NULL,
  `email` char(100) NOT NULL,
  PRIMARY KEY  (`id`)
)

CREATE TABLE IF NOT EXISTS `character` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `name` varchar(32) NOT NULL,
  `created` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`),
);

Assumptions

The project used for this demonstration is setup as follows:

Base_Folder
- application
- models
- DbTable – library
- public

I am going to assume that your project is setup similar and if that is not the case your mileage may vary. I also assume that autoloaders are setup correctly so that Default_Model_Event would map to base_folder/models/Event.php. It is not the scope of this document to provide information on autoloaders or project structure.

Ideas

See Domain Model, Table Data Gateway, and Data Mapper for information on why I created this library the way I did.

Note: While I believe I understand the concepts of the ideas above I do not consider myself an expert. If you feel I have done something wrong or have worded something incorrectly please correct me by posting a comment. Credit is given where credit is due.

Creating the Event Table Gateway

The Zend Framework provides a table gateway through Zend_Db_Table. SpiffyDb extends this class to provide additional functionality. The table gateway provides the table name and a reference map of joins if any exist. For more information please see Zend_Db_Table Relationships.

// application/models/DbTable/Event.php

 array(
                        'columns' => 'character_id',
                        'joinColumns' => array('name'),
                        'refTableClass' => 'Default_Model_DbTable_Character',
                        'refColumns' => array('id')),
                'Guild' => array(
                        'columns' => 'guild_id',
                        'refTableClass' => 'Default_Model_DbTable_Guild',
                        'refColumns' => array('id')),
                'User' => array(
                        'columns' => 'user_id',
                        'refTableClass' => 'Default_Model_DbTable_User',
                        'refColumns' => array('id')));
}

Creating the Event Domain Model

Domain models allow us to provide an object-oriented approach to manipulating data stored in the backend. The model is used by the mapper to gather data which is fed to the gateway which then manipulates the database. You should not place any SQL logic inside the model itself.

// application/models/Event.php

 array(
                        'column' => 'id', // overrides the default behaviour and sets the table column to 'id'
                        'filters' => 'Int',
                        'validators' => array(
                                'Int',
                                array('GreaterThan', 0)
                        )
                ),
                'userId' => array(
                        'filters' => 'Int',
                        'validators' => array(
                                'Int',
                                array('GreaterThan', 0)
                        )
                ),
                'guildId' => array(
                        'filters' => 'Int',
                        'validators' => array(
                                'Int',
                                array('GreaterThan', 0)
                        )
                ),
                'title' => array(
                        'filters' => 'StringTrim',
                        'validators' => array(
                                array('StringLength', false, array(5, 64))
                        )
                ),
                'description' => array(
                        'filters' => 'StringTrim'
                ),
                'date' => array(
                        'filters' => 'StringTrim',
                        'validators' => 'NotEmpty'
                ), 

                // I generally placed all fields from joined tables below. When you execute a joined
                // query (see below for more information) the following data will be populated automatically
                // if it is found in the returned rowset. By using 'onSave' => self::IGNORE we tell the table gateway
                // to ignore that information when saving this model's data.

                // Joined from character table
                'characterName' => array(
                        'onSave' => self::IGNORE
                ),

                // Joined from guild table
                'guildName' => array(
                        'onSave' => self::IGNORE
                ),

                // Joined from user table
                'userName' => array(
                        'onSave' => self::IGNORE
                )
        );

        // All domain logic functions should be placed here

        // Returns a query resultSet of all events joined with character, guild, and user
        // and sorted by title.
        public function getAll()
        {
                $this->mapper()->withCharacter()->withGuild()->withUser()->orderTitle();

                return $this->query();
        }

        // Returns $this with the model being populated by a single rowset. Includes joined information
        // from the user table sorted by title.
        public function getOneByUserId($userId)
        {
                $this->mapper()->withUser()->byUserId($userId)->orderTitle();
                $this->queryOne();

                return $this;
        }

        // Filters based on $_POST and $_GET parameters and only allows user_id and guild_id to be filtered.
        public function getAllUsingParams()
        {
                $this->mapper()->withCharacter()->withGuild()->withUser()->byParams(array('user_id', 'guild_id'));
                return $this->query():
        }
}

Creating the Event Mapper

The mapper is what will hold all our SQL logic and will map the information from the domain model to the table gateway which will handle database manipulation.

// application/models/EventMapper.php

 'name'))
        {
                $this->get()->by(array('c' => 'Character'), $cols);
                return $this;
        }

        // Perform a join and select only guild.name as guild_name
        public function withGuild($cols = array('guild_name' => 'name'))
        {
                $this->get()->by(array('g' => 'Guild'), $cols);
                return $this;
        }

        // Perform a join and select only user.name as user_name
        public function withUser($cols = array('user_name' => 'username'))
        {
                $this->get()->by(array('u' => 'User'), $cols);
                return $this;
        }

        // Return only events matching a particular userId
        public function byUserId($userId)
        {
                $this->get()->where('user_id = ?', $userId);
                return $this;
        }

        // Order by title
        public function orderTitle()
        {
                $this->get()->order('title');
                return $this;
        }
}

Using the Event Model, Mapper, and Gateway

Now that we have setup the required classes we can being manipulating data and using the functionality of SpiffyDb.

Finding a Single Record

find(1); // finds with no join

echo 'I found an event with title: ' . $event->Title . ' and owned by user ' . $event->userId;

// or

$event->getOneByUserId(5); // finds with join

echo 'I found an event with title: ' . $event->Title . ' and owned by user ' . $event->userName;

Finding all Records Joined Using Character, Guild, and User

getAll();

foreach($resultSet as $row)
{
        echo 'I am an event with title: ' . $event->Title;
}

Filtering a Query Based on Parameters

In some instances you may want to filter a query using the parameters passed via $_GET or $_POST. I have provided a helper method which will do this for you. See the getAllUsingParams() method of the Event model above for details.

More Information

All of the regular Zend_Db_Table features will still function normally.

Filtering and Validation

In order to promote code reuse SpiffyDb provides filtering and validation through the domain model itself. See the Event Model above for information on setting up the filters and validators for your model.

Using Filtering

Retrieving a filtered value is as simple as using the filter() method. If you want to filter data sent via a $_GET you can do something like:

Note: Filtering is done automatically anytime you set a value using the setValue() method. This includes using the magic __set method! To disable this functionality you need to pass a second parameter of false to the setValue() method.

filter('id', $this->_getParam('id'));
echo 'Unfiltered: ' . $this->_getParam('id') . ' Filtered: ' . $id;

Using Validation

Validation is handy when you want to ensure that the model has valid data before proceeding. Using the isValid() function makes use of the Zend_Input_Filter for validation. If you want to check that an id is valid before continuing you could do something like:

_getParam('id');
if ($event->isValid('id', $id)) {
        // Okay, safe to do something
}

Changelog

1.1.0 on Friday, October 2nd, 2009

• Initial release