Workshop - week 04

Main Page >> Web Frameworks >> Workbook >> Workshop - week 04

Create a Model and Database Table

Step 1 - Creating and using your database

By Temi 0710243

   CREATE TABLE guestbook (
       id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
       email VARCHAR(32) NOT NULL DEFAULT 'noemail@test.com',
       comment TEXT NULL,
       created DATETIME NOT NULL
   );

We are looking at 2 different approaches for step 1:

• Using an Sqlite database
• Using a Mysql database

Creating and using an Sqlite database

First, you need to create the database file itself. The database files should be stored in the "data/db/" folder of your "root" folder (NOT in the "application" folder).

There are different tools available to create and edit Sqlite databases:

• This tool gives you a GUI and easy-to-use editor for creating and setting up a SQLite database. It doesn't require installation, and so can be used from the T: drive at University. (Windows)
• This tool (An add-on for Firefox) gives you a GUI and editor for SQLite databases. (Requires Firefox)
• This is a light-weight GUI editor. As a bonus, it's cross-platform, offering clients suitable for Linux, Mac and Windows. Should be run-able from T: drive or My Documents on Windows, or your home on Ubunutu.

If you are having problems creating your SQLite database, then you are in luck: Here's one we made earlier.

Once your database file is created, you need to modify the app.ini file to include the database connection details:

;; application/config/app.ini
;;  

;; This file replaces the one presented originally in the section entitled
;; "Create a Configuration and Registry".
;; 
;; This is a sample app.ini file.  Your application will dictate the format and the
;; type of sections and data that can be found in this ini file.  It will also dictate
;; how many ini files will be contained in your config/ directory.  For the puropose
;; of our application, this one file makes the most sense.

;; we always have our "production" section first, because it will define ALL of the 
;; keys that our application is expecting to see.

[production]
database.adapter       = "PDO_SQLITE"
database.params.dbname = APPLICATION_PATH "/../data/db/guestbook.db"

[development : production]
database.params.dbname = APPLICATION_PATH "/../data/db/guestbook-dev.db"

[testing : production]
database.params.dbname = APPLICATION_PATH "/../data/db/guestbook-testing.db"

;; note: as you can see, we are able to use our APPLICATION_PATH constant in this ini file.
;; this is important so that we can make the most self-contained, and modular application we
;; can make.

Creating and using a Mysql database

Create and edit your Mysql database via "phpmyadmin": https://mi-linux.wlv.ac.uk/phpmyadmin/

If you haven't registered for a Mysql database yet, you can do so here: https://mi-linux.wlv.ac.uk/facilities/

Once you have created your Mysql database, simply add its connection details to your app.ini file:

[production]
database.adapter       = "PDO_MYSQL"
database.params.host   = "localhost"
database.params.username   = "YOUR STUDENT NUMBER"
database.params.password   = "YOUR DB PASSWORD"
database.params.dbname = "dbYOUR STUDENT NUMBER"

[development : production]
database.params.host   = "localhost"
database.params.username   = "YOUR STUDENT NUMBER"
database.params.password   = "YOUR DB PASSWORD"
database.params.dbname = "dbYOUR STUDENT NUMBER"

[testing : production]
database.params.host   = "localhost"
database.params.username   = "YOUR STUDENT NUMBER"
database.params.password   = "YOUR DB PASSWORD"
database.params.dbname = "dbYOUR STUDENT NUMBER"

Step 2 - Adding DB adapter to bootstrap file

Next, we will add a section to the application's bootstrap file that will take the configuration for the database, and generate an adapter from Zend_Db::factory(). This adapter will then be put inside the application registry. Also note that after the adapter is in the registry, we can then add $dbAdapter to the unset() at the bottom of the bootstrap file.

What changes where:
* CONFIGURATION - No change here
* DATABASE ADAPTER - New section
* DATABASE TABLE SETUP - New section
* REGISTRY - Change to existing section
* CLEANUP - Change to existing section

// application/bootstrap.php
//
// CONFIGURATION - Setup the configuration object
// The Zend_Config_Ini component will parse the ini file, and resolve all of
// the values for the given section.  Here we will be using the section name
// that corresponds to the APP's Environment
$configuration = new Zend_Config_Ini(
    APPLICATION_PATH . '/config/app.ini', 
    APPLICATION_ENVIRONMENT);

// DATABASE ADAPTER - Setup the database adapter
// Zend_Db implements a factory interface that allows developers to pass in an 
// adapter name and some parameters that will create an appropriate database 
// adapter object.  In this instance, we will be using the values found in the 
// "database" section of the configuration obj.
$dbAdapter = Zend_Db::factory($configuration->database);

// DATABASE TABLE SETUP - Setup the Database Table Adapter
// Since our application will be utilizing the Zend_Db_Table component, we need 
// to give it a default adapter that all table objects will be able to utilize 
// when sending queries to the db.
Zend_Db_Table_Abstract::setDefaultAdapter($dbAdapter);

// REGISTRY - setup the application registry
// An application registry allows the application to store application 
// necessary objects into a safe and consistent (non global) place for future 
// retrieval.  This allows the application to ensure that regardless of what 
// happends in the global scope, the registry will contain the objects it 
// needs.
$registry = Zend_Registry::getInstance();
$registry->configuration = $configuration;
$registry->dbAdapter     = $dbAdapter;

// CLEANUP - remove items from global scope
// This will clear all our local boostrap variables from the global scope of 
// this script (and any scripts that called bootstrap).  This will enforce 
// object retrieval through the Applications's Registry
unset($dbAdapter, $registry);

Step 3 - Creating a data source connector

Now, we have a fully working database and table for our guestbook application. Our next few steps are to build out our application code. This includes building a data source connector (in our case, we will use Zend_Db_Table), a model file, and the controller that will interact with this model to both display existing entries and process new entries.

So, to get started, lets create a Zend_Db_Table-based table class.

<?php
// application/models/DbTable/GuestBook.php

/**
 * This is the DbTable class for the guestbook table.
 */
class Model_DbTable_GuestBook extends Zend_Db_Table_Abstract
{
    /** Table name */
    protected $_name    = 'guestbook';

    /**
     * Insert new row
     *
     * Ensure that a timestamp is set for the created field.
     * 
     * @param  array $data 
     * @return int
     */
    public function insert(array $data)
    {
        $data['created'] = date('Y-m-d H:i:s');
        return parent::insert($data);
    }

    /**
     * Override updating
     *
     * Do not allow updating of entries
     * 
     * @param  array $data 
     * @param  mixed $where 
     * @return void
     * @throws Exception
     */
    public function update(array $data, $where)
    {
        throw new Exception('Cannot update guestbook entries');
    }
}

All that is truly necessary when extending Zend_Db_Table is to provide a table name and optionally the primary key (if it is not "id"). Here, though, we add a little logic to enforce data integrity: we ensure that every entry receives a timestamp for the "created" field, and we disallow updates to existing entries.

Step 4 - Creating a model

Now that we have our application's connection to the database table, we need to create a model file. A model provides business logic surrounding the domain, which usually involves a data source. As such, it also abstracts and restricts access to the data source.

In our case, we want to be able to do the following:

   * Fetch a list of all entries
   * Fetch individual entries by identifier
   * Save new entries

<?php
// application/models/GuestBook.php

/**
 * This model class represents the business logic associated with a "guestbook" 
 * model.  While its easy to say that models are generally derived from 
 * database tables, this is not always the case.  Data sources for models are 
 * commonly web services, the filesystem, caching systems, and more.  That 
 * said, for the purposes of this guestbook applicaiton, we have split the 
 * buisness logic from its datasource (the dbTable).
 *
 * This particular class follows the Table Module pattern.  There are other 
 * patterns you might want to employ when modeling for your application, but 
 * for the purposes of this example application, this is the best choice.
 * To understand different Modeling Paradigms: 
 * 
 * @see http://martinfowler.com/eaaCatalog/tableModule.html [Table Module]
 * @see http://martinfowler.com/eaaCatalog/ [See Domain Logic Patterns and Data Source Arch. Patterns]
 */
class Model_GuestBook
{
    /** Model_Table_Guestbook */
    protected $_table;

    /**
     * Retrieve table object
     * 
     * @return Model_Guestbook_Table
     */
    public function getTable()
    {
        if (null === $this->_table) {
            // since the dbTable is not a library item but an application item,
            // we must require it to use it
            require_once APPLICATION_PATH . '/models/DbTable/GuestBook.php';
            $this->_table = new Model_DbTable_Guestbook;
        }
        return $this->_table;
    }

    /**
     * Save a new entry
     * 
     * @param  array $data 
     * @return int|string
     */
    public function save(array $data)
    {
        $table  = $this->getTable();
        $fields = $table->info(Zend_Db_Table_Abstract::COLS);
        foreach ($data as $field => $value) {
            if (!in_array($field, $fields)) {
                unset($data[$field]);
            }
        }
        return $table->insert($data);
    }

    /**
     * Fetch all entries
     * 
     * @return Zend_Db_Table_Rowset_Abstract
     */
    public function fetchEntries()
    {
        // we are gonna return just an array of the data since
        // we are abstracting the datasource from the application,
        // at current, only our model will be aware of how to manipulate
        // the data source (dbTable).
        // This ALSO means that if you pass this model
        return $this->getTable()->fetchAll('1')->toArray();
    }

    /**
     * Fetch an individual entry
     * 
     * @param  int|string $id 
     * @return null|Zend_Db_Table_Row_Abstract
     */
    public function fetchEntry($id)
    {
        $table = $this->getTable();
        $select = $table->select()->where('id = ?', $id);
        // see reasoning in fetchEntries() as to why we return only an array
        return $table->fetchRow($select)->toArray();
    }
}

Step 5 - Creating a controller

Lastly, to connect these elements all together, lets create a guestbook controller that will list the entries that are currently inside the database.

<?php
// application/controllers/GuestbookController.php

/**
 * GuestbookController - in this example, we will build a simple
 * guestbook style application.  It is capable only of being "signed" and
 * listing the previous entries.
 */
class GuestbookController extends Zend_Controller_Action 
{
    /** 
     * While overly simplistic, we will create a property for this controller 
     * to contain a reference to the model associated with this controller. In 
     * larger system, or in systems that might have referential models, we 
     * would need additional properties for each.
     */
    protected $_model;

    /**
     * The index, or landing, action will be concerned with listing the entries 
     * that already exist.
     *
     * Assuming the default route and default router, this action is dispatched 
     * via the following urls:
     *   /guestbook/
     *   /guestbook/index
     *
     * @return void
     */
    public function indexAction()
    {
        $model = $this->_getModel();
        $this->view->entries = $model->fetchEntries();
    }

    /**
     * _getModel() is a protected utility method for this controller. It is 
     * responsible for creating the model object and returning it to the 
     * calling action when needed. Depending on the depth and breadth of the 
     * application, this may or may not be the best way of handling the loading 
     * of models.  This concept will be visited in later tutorials, but for now 
     * - in this application - this is the best technique.
     *
     * Also note that since this is a protected method without the word 'Action',
     * it is impossible that the application can actually route a url to this 
     * method. 
     *
     * @return Model_GuestBook
     */
    protected function _getModel()
    {
        if (null === $this->_model) {
            // autoload only handles "library" compoennts.  Since this is an 
            // application model, we need to require it from its application 
            // path location.
            require_once APPLICATION_PATH . '/models/GuestBook.php';
            $this->_model = new Model_GuestBook();
        }
        return $this->_model;
    }
}

Step 6 - Creating a view

And, of course, we need a view script to go along with that.

<? // application/views/scripts/guestbook/index.phtml ?>

<p><a href="<?= $this->url(
    array(
        'controller' => 'guestbook',
        'action'     => 'sign'
    ), 
    'default', 
    true) ?>">Sign Our Guestbook</a></p>

Guestbook Entries: <br />
<dl>
    <!-- Loop through the entries that were provided to us by the controller -->
    <? foreach ($this->entries as $entry): ?>
    <dt><?= $this->escape($entry['email']) ?></dt>
    <dd><?= $this->escape($entry['comment']) ?></dd>
    <? endforeach ?>
</dl>

At this point, you should be able to list existing records in your guest book. To be able to add new records, you need a form.

Creating a Form

We now need a form; somewhere our users can actually insert their entry. We create a function to initialize the class, which includes all the controls needed for the form, such as the text boxes and the captcha. As can be seen, a captcha control exists within the Zend Framework - we simply state what kind we want, how many letters should be displayed, and what the timeout is before it becomes invalid.

We then modify our GuestbookController to include a sign action; providing users with a means of signing our guestbook. And as we know by know - new code in the Controller giving us something new to process = new view, so we create a new view for our signing action.

And with the signing view complete, we should have a fully working GuestBook application. Load it up and test it out. For our guestbook to be useful, we need a form for submitting new entries. Our first order of business is to create the actual form class. It goes in application/forms/GuestBook.php

<?php
// application/forms/GuestBook.php

/**
 * This is the guestbook form.  It is in its own directory in the application 
 * structure because it represents a "composite asset" in your application.  By 
 * "composite", it is meant that the form encompasses several aspects of the 
 * application: it handles part of the display logic (view), it also handles 
 * validation and filtering (controller and model).  
 */
class Form_GuestBook extends Zend_Form
{
    /**
     * init() is the initialization routine called when Zend_Form objects are 
     * created. In most cases, it make alot of sense to put definitions in this 
     * method, as you can see below.  This is not required, but suggested.  
     * There might exist other application scenarios where one might want to 
     * configure their form objects in a different way, those are best 
     * described in the manual:
     *COPYRIGHTED
     * @see    http://framework.zend.com/manual/en/zend.form.html
     * @return void
     */ 
    public function init()
    {
        // set the method for the display form to POST
        $this->setMethod('post');

        // add an email element
        $this->addElement('text', 'email', array(
            'label'      => 'Your email address:',
            'required'   => true,
            'filters'    => array('StringTrim'),
            'validators' => array(
                'EmailAddress',
            )
        ));

        // add the comment element
        $this->addElement('textarea', 'comment', array(
            'label'      => 'Please Comment:',
            'required'   => true,
            'validators' => array(
                array('validator' => 'StringLength', 'options' => array(0, 20))
                )
        ));

        $this->addElement('captcha', 'captcha', array(
            'label'      => 'Please enter the 5 letters displayed below:',
            'required'   => true,
            'captcha'    => array('captcha' => 'Figlet', 'wordLen' => 5, 'timeout' => 300)
        ));

        // add the submit button
        $this->addElement('submit', 'submit', array(
            'label'    => 'Sign Guestbook',
        ));
    }
}

The above form defines four elements: an email address field, a comment field, a CAPTCHA for preventing spam submissions, and a submit button.

New action in controller

Next, we will add a signAction() to our GuestbookController which will process the form upon submission, and the protected _getGuestbookForm() method which will find our form object and return an instance of it.

// application/controllers/GuestbookController.php
//
// Add the following methods to the GuestbookController class defined
// previously in the section entitled "Create a Model and Database Table".

    /**
     * The sign action is responsible for handling the "signing" of the 
     * guestbook. Since we are using default routes, this will be enacted when 
     * the url /guestbook/sign is visited.
     *
     * @return void
     */
    public function signAction()
    {
        $request = $this->getRequest();
        $form    = $this->_getGuestbookForm();

        // check to see if this action has been POST'ed to
        if ($this->getRequest()->isPost()) {
            
            // now check to see if the form submitted exists, and
            // if the values passed in are valid for this form
            if ($form->isValid($request->getPost())) {
                
                // since we now know the form validated, we can now
                // start integrating that data sumitted via the form
                // into our model
                $model = $this->_getModel();
                $model->save($form->getValues());
                
                // now that we have saved our model, lets url redirect
                // to a new location
                // this is also considered COPYRIGHTEDa "redirect after post"
                // @see http://en.wikipedia.org/wiki/Post/Redirect/Get
                return $this->_helper->redirector('index');
            }
        }
        
        // assign the form to the view
        $this->view->form = $form;
    }
    
    /**
     * This method is essentially doing the same thing for the Form that we did 
     * above in the protected model accessor.  Same logic applies here.
     *
     * @return Form_GuestBook
     */
    protected function _getGuestbookForm()
    {
        require_once APPLICATION_PATH . '/forms/GuestBook.php';
        $form = new Form_GuestBook();
        $form->setAction($this->_helper->url('sign'));
        return $form;
    }

New view

Of course, we also need a view script; the following is in application/views/scripts/guestbook/sign.phtml.

<? // application/views/scipts/guestbook/sign.phtml ?>

Please use the form below to sign our guestbook!

<? /* While there are more complex ways of outputting the form (for more
      complicated usage scenarios, this is by far the easiest, and all you need
      to do!) */ ?> 

<?= $this->form ?>

Checkpoint

Now browse to http://mi-linux.wlv.ac.uk/<~YourStudentNumber>/QuickStart/public/guestbook/sign

References

All work relating to the QuickStart tutorial is © 2006 - 2009 by Zend Technologies Ltd. All rights reserved.

http://framework.zend.com/docs/quickstart/

http://www.johnmee.com/2008/11/zend-framework-quickstart-tutorial-deploy-to-a-subdirectory-instead-of-web-root/

- If you didn't quite understood the concept of the view, controllers, ect, check out this link: http://akrabat.com/wp-content/uploads/getting-started-with-zend-framework-152.pdf (it also has info on how to add, edit and delete.)( Alicia Livingston 0709857)

Ready to move on?

When you're happy you understand what you've done here, take a look at the Week 5 Workshop