Workshop - week 04
Main Page >> Web Frameworks >> Web Frameworks - Workbook >> Workshop - week 04
Create a Model and Database Table
Next, we need somewhere to store our data. We modify the app.ini file to include the database connection details (We are setting up a SQLite database - it is not a database you will necessarily be familiar with. Rather than having a database program running on a server, SQLite can have an entire database stored within a file - like using an XML file, but with all the benefits of SQL queries.)
We then need to modify the bootstrap.php file, to include the new connection details to the database we are about to create.
First we will put our db connection information inside the application's ini file.
;; 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.
--- Edit by N. Banford (0506508) --- 5/2/09 If you have trouble with SQLite or just want to use MySQL instead, the code for a MySQL connection in your 'app.ini' file is the following:
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"
--- End of Edit --
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.
<source lang="php"> // application/bootstrap.php // // Add the following code prior to the comment marked "Step 5" in // 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); </source>
At this point we have a connection to a database; in our case, its a connection to a Sqlite database located inside our application/data/ directory. So, let's design a simple table that will hold our guestbook entries.
<source lang="sql"> -- scripts/schema.sqlite.sql -- -- You will need to load your database schema with this SQL.
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
);
CREATE INDEX "id" ON "guestbook" ("id"); </source>
And, so that we can have some working data out of the box, lets create a few rows of information to make our application interesting.
<source lang="sql"> -- scripts/data.sqlite.sql -- -- You can begin populating the database with the following SQL statements.
INSERT INTO guestbook (email, comment, created) VALUES
('ralph.schindler@zend.com',
'Hello! Hope you enjoy this sample zf application!',
DATETIME('NOW'));
INSERT INTO guestbook (email, comment, created) VALUES
('foo@bar.com',
'Baz baz baz, baz baz Baz baz baz - baz baz baz.',
DATETIME('NOW'));
</source>
Now that we have both the schema and some data defined. Lets get a script together that we can now execute to build this database. Naturally, this is not needed in production, but this script will help developers build out the database requirements locally so they can have the fully working application.
<source lang="php"> <?php /**
* scripts/load.sqlite.php * * Script for creating and loading database; execute this script with the PHP * interpreter to prepare your SQLite database. */
// use bootstrap (contains prepared db adapter and prepared table // component) set_include_path(dirname(__FILE__) . '/../library' . PATH_SEPARATOR . get_include_path()); require_once 'Zend/Loader.php'; Zend_Loader::registerAutoload();
include_once dirname(__FILE__) . '/../application/bootstrap.php';
// if any parameter is passed after the script name (like 1 or --withdata) // load the data file after the schema has loaded. $withData = isset($_SERVER['argv'][1]);
// pull the adapter out of the application registry $dbAdapter = Zend_Registry::getInstance()->dbAdapter;
// let the user know whats going on (we are actually creating a // database here) echo 'Writing Database Guestbook in (control-c to cancel): ' . PHP_EOL; for ($x = 5; $x > 0; $x--) {
echo $x . "\r"; sleep(1);
}
// this block executes the actual statements that were loaded from // the schema file. try {
$schemaSql = file_get_contents('./schema.sqlite.sql');
// use the connection directly to load sql in batches
$dbAdapter->getConnection()->exec($schemaSql);
echo PHP_EOL;
echo 'Database Created';
echo PHP_EOL;
if ($withData) {
$dataSql = file_get_contents('./data.sqlite.sql');
// use the connection directly to load sql in batches
$dbAdapter->getConnection()->exec($dataSql);
echo 'Data Loaded.';
echo PHP_EOL;
}
} catch (Exception $e) {
echo 'AN ERROR HAS OCCURED:' . PHP_EOL; echo $e->getMessage() . PHP_EOL; return false;
}
// generally speaking, this script will be run from the command line
return true;
</source>
path/to/ZendFrameworkQuickstart/scripts$ php load.sqlite.php Writing Database Guestbook in (control-c to cancel): 1 Database Created
This PHP file will be run just once, and will create our database by executing the first script, and fill it with data as outlined by our second script. Once scripts/load.sqlite.php has been executed successfully, the file can be deleted - but don't do so until you have a fully working application, in case you need to execute the database setup again.
Pro Tip: Remember that the directories that the database is being set up within - data/db/ - need to have the correct permissions set. At this stage, CHMOD it to 777 (read, write and execute for all) to help get the application running.
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.
<source lang="php"> <?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');
}
} </source>
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.
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
<source lang="php"> <?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();
}
} </source>
Lastly, to connect these elements all together, lets create a guestbook controller that will both list the entries that are currently inside the database.
<source lang="php"> <?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();
}
/**
* The sign action is responsible for handling the "signing" of the
* guestbook.
*
* Assuming the default route and default router, this action is dispatched
* via the following url:
* /guestbook/sign
*
* @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 a "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;
}
/**
* _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;
}
/**
* 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->_request->getBaseUrl() . $this->_helper->url('sign'));
return $form;
}
} </source>
And, of course, we need a view script to go along with that.
<source lang="php"> <? // application/views/scripts/guestbook/index.phtml ?>
<a href="<?= $this->url( array( 'controller' => 'guestbook', 'action' => 'sign' ), 'default', true) ?>">Sign Our Guestbook</a>
Guestbook Entries:
-
<? foreach ($this->entries as $entry): ?>
- <?= $this->escape($entry['email']) ?>
- <?= $this->escape($entry['comment']) ?> <? endforeach ?>
</source>
Create 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
<source lang="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',
));
}
} </source>
The above form defines four elements: an email address field, a comment field, a CAPTCHA for preventing spam submissions, and a submit button.
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.
<source lang="php"> // 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;
}
</source>
Of course, we also need a view script; the following is in application/views/scripts/guestbook/sign.phtml.
<source lang="php"> <? // 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 ?> </source>
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.
