Create a Model and Database Table

http://framework.zend.com/manual/1.12/en/learning.quickstart.create-model.html

 

 

Create a Model and Database Table

Before we get started, let's consider something: where will these classes live, and how will we find them? The default project we created instantiates an autoloader. We can attach other autoloaders to it so that it knows where to find different classes. Typically, we want our various MVC classes grouped under the same tree -- in this case, application/ -- and most often using a common prefix.

Zend_Controller_Front has a notion of "modules", which are individual mini-applications. Modules mimic the directory structure that thezf tool sets up under application/, and all classes inside them are assumed to begin with a common prefix, the module name. application/is itself a module -- the "default" or "application" module. As such, we'll want to setup autoloading for resources within this directory.

Zend_Application_Module_Autoloader provides the functionality needed to map the various resources under a module to the appropriate directories, and provides a standard naming mechanism as well. An instance of the class is created by default during initialization of the bootstrap object; your application bootstrap will by default use the module prefix "Application". As such, our models, forms, and table classes will all begin with the class prefix "Application_".

Now, let's consider what makes up a guestbook. Typically, they are simply a list of entries with a comment, timestamp, and, often, email address. Assuming we store them in a database, we may also want a unique identifier for each entry. We'll likely want to be able to save an entry, fetch individual entries, and retrieve all entries. As such, a simple guestbook model API might look something like this:

1. // application/models/Guestbook.php
2.  
3. class  Application_Model_Guestbook
4. {
5.     protected   $_comment;
6.     protected   $_created;
7.     protected   $_email;
8.     protected   $_id;
9.  
10.       public   function  __set ( $name,   $value );
11.       public   function  __get ( $name );
12.  
13.       public   function  setComment ( $text );
14.       public   function  getComment ( );
15.  
16.       public   function  setEmail ( $email );
17.       public   function  getEmail ( );
18.  
19.       public   function  setCreated ( $ts );
20.       public   function  getCreated ( );
21.  
22.       public   function  setId ( $id );
23.       public   function  getId ( );
24. }
25.  
26. class  Application_Model_GuestbookMapper
27. {
28.       public   function  save (Application_Model_Guestbook   $guestbook );
29.       public   function  find ( $id );
30.       public   function  fetchAll ( );
31. }

__get() and __set() will provide a convenience mechanism for us to access the individual entry properties, and proxy to the other getters and setters. They also will help ensure that only properties we whitelist will be available in the object.

find() and fetchAll() provide the ability to fetch a single entry or all entries, while save() takes care of saving an entry to the data store.

Now from here, we can start thinking about setting up our database.

First we need to initialize our Db resource. As with the Layout and View resource, we can provide configuration for the Dbresource. We can do this with the zf configure db-adapter command:

1. % zf configure db-adapter \
2. > 'adapter=PDO_SQLITE&dbname=APPLICATION_PATH "/../data/db/guestbook.db"' \
3. > production
4. A db configuration for the production has been written to the application config file.
5.  
6. % zf configure db-adapter \
7. > 'adapter=PDO_SQLITE&dbname=APPLICATION_PATH "/../data/db/guestbook-testing.db"' \
8. > testing
9. A db configuration for the production has been written to the application config file.
10.  
11. % zf configure db-adapter \
12. > 'adapter=PDO_SQLITE&dbname=APPLICATION_PATH "/../data/db/guestbook-dev.db"' \
13. > development
14. A db configuration for the production has been written to the application config file.

Now edit your application/configs/application.ini file, where you'll see the following lines were added in the appropriate sections.

1. ; application/configs/application.ini
2.  
3. [production]
4. ; ...
5. resources.db. adapter =   "PDO_SQLITE"
6. resources.db.params. dbname =  APPLICATION_PATH  "/../data/db/guestbook.db"
7.  
8. [testing : production]
9. ; ...
10. resources.db. adapter =   "PDO_SQLITE"
11. resources.db.params. dbname =  APPLICATION_PATH  "/../data/db/guestbook-testing.db"
12.  
13. [development : production]
14. ; ...
15. resources.db. adapter =   "PDO_SQLITE"
16. resources.db.params. dbname =  APPLICATION_PATH  "/../data/db/guestbook-dev.db"

Your final configuration file should look like the following:

1. ; application/configs/application.ini
2.  
3. [production]
4. phpSettings. display_startup_errors =  0
5. phpSettings. display_errors =  0
6. bootstrap. path =  APPLICATION_PATH  "/Bootstrap.php"
7. bootstrap. class =   "Bootstrap"
8. appnamespace =   "Application"
9. resources.frontController. controllerDirectory =  APPLICATION_PATH  "/controllers"
10. resources.frontController.params. displayExceptions =  0
11. resources.layout. layoutPath =  APPLICATION_PATH  "/layouts/scripts"
12. resources.view [ ]  =
13. resources.db. adapter =   "PDO_SQLITE"
14. resources.db.params. dbname =  APPLICATION_PATH  "/../data/db/guestbook.db"
15.  
16. [staging : production]
17.  
18. [testing : production]
19. phpSettings. display_startup_errors =  1
20. phpSettings. display_errors =  1
21. resources.db. adapter =   "PDO_SQLITE"
22. resources.db.params. dbname =  APPLICATION_PATH  "/../data/db/guestbook-testing.db"
23.  
24. [development : production]
25. phpSettings. display_startup_errors =  1
26. phpSettings. display_errors =  1
27. resources.db. adapter =   "PDO_SQLITE"
28. resources.db.params. dbname =  APPLICATION_PATH  "/../data/db/guestbook-dev.db"

Note that the database(s) will be stored in data/db/. Create those directories, and make them world-writeable. On unix-like systems, you can do that as follows:

1. % mkdir -p data/db; chmod -R a+rwX data

On Windows, you will need to create the directories in Explorer and set the permissions to allow anyone to write to the directory.

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.

1. -- scripts/schema.sqlite.sql
2. --
3. -- You will need load your database schema with this SQL.
4.  
5. CREATE   TABLE  guestbook   (
6.     id INTEGER   NOT   NULL   PRIMARY   KEY  AUTOINCREMENT,
7.     email VARCHAR ( 32 )   NOT   NULL   DEFAULT   'noemail@test.com',
8.     comment TEXT   NULL,
9.     created DATETIME   NOT   NULL
10. );
11.  
12. CREATE   INDEX   "id"   ON   "guestbook"   ( "id" );

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.

1. -- scripts/data.sqlite.sql
2. --
3. -- You can begin populating the database with the following SQL statements.
4.  
5. INSERT   INTO  guestbook   (email, comment, created )   VALUES
6.       ( 'ralph.schindler@zend.com',
7.       'Hello! Hope you enjoy this sample zf application!',
8.     DATETIME ( 'NOW' ) );
9. INSERT   INTO  guestbook   (email, comment, created )   VALUES
10.       ( 'foo@bar.com',
11.       'Baz baz baz, baz baz Baz baz baz - baz baz baz.',
12.     DATETIME ( 'NOW' ) );

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. Create the script as scripts/load.sqlite.php with the following contents:

1. // scripts/load.sqlite.php
2.  
3. /**
4. * Script for creating and loading database
5. */
6.  
7. // Initialize the application path and autoloading
8. defined ( 'APPLICATION_PATH' )
9.     ||   define ( 'APPLICATION_PATH',   realpath ( dirname ( __FILE__ )  .   '/../application' ) );
10. set_include_path ( implode (PATH_SEPARATOR,   array (
11.     APPLICATION_PATH .   '/../library',
12.       get_include_path ( ),
13. ) ) );
14. require_once   'Zend/Loader/Autoloader.php';
15. Zend_Loader_Autoloader:: getInstance ( );
16.  
17. // Define some CLI options
18. $getopt  =   new  Zend_Console_Getopt ( array (
19.       'withdata|w'  =>   'Load database with sample data',
20.       'env|e-s'    =>   'Application environment for which to create database (defaults to development)',
21.       'help|h'     =>   'Help -- usage message',
22. ) );
23. try   {
24.       $getopt-> parse ( );
25. }  catch   (Zend_Console_Getopt_Exception   $e )   {
26.       // Bad options passed: report usage
27.       echo   $e-> getUsageMessage ( );
28.       return   false;
29. }
30.  
31. // If help requested, report usage message
32. if   ( $getopt-> getOption ( 'h' ) )   {
33.       echo   $getopt-> getUsageMessage ( );
34.       return   true;
35. }
36.  
37. // Initialize values based on presence or absence of CLI options
38. $withData  =   $getopt-> getOption ( 'w' );
39. $env      =   $getopt-> getOption ( 'e' );
40. defined ( 'APPLICATION_ENV' )
41.     ||   define ( 'APPLICATION_ENV',   ( null  ===   $env )  ?   'development'  :   $env );
42.  
43. // Initialize Zend_Application
44. $application  =   new  Zend_Application (
45.     APPLICATION_ENV,
46.     APPLICATION_PATH .   '/configs/application.ini'
47. );
48.  
49. // Initialize and retrieve DB resource
50. $bootstrap  =   $application-> getBootstrap ( );
51. $bootstrap-> bootstrap ( 'db' );
52. $dbAdapter  =   $bootstrap-> getResource ( 'db' );
53.  
54. // let the user know whats going on (we are actually creating a
55. // database here)
56. if   ( 'testing'  != APPLICATION_ENV )   {
57.       echo   'Writing Database Guestbook in (control-c to cancel): '  . PHP_EOL;
58.       for   ( $x  =   5;   $x  >   0;   $x-- )   {
59.           echo   $x  .   "\r";   sleep ( 1 );
60.       }
61. }
62.  
63. // Check to see if we have a database file already
64. $options  =   $bootstrap-> getOption ( 'resources' );
65. $dbFile  =   $options [ 'db' ] [ 'params' ] [ 'dbname' ];
66. if   ( file_exists ( $dbFile ) )   {
67.       unlink ( $dbFile );
68. }
69.  
70. // this block executes the actual statements that were loaded from
71. // the schema file.
72. try   {
73.       $schemaSql  =   file_get_contents ( dirname ( __FILE__ )  .   '/schema.sqlite.sql' );
74.       // use the connection directly to load sql in batches
75.       $dbAdapter-> getConnection ( )-> exec ( $schemaSql );
76.       chmod ( $dbFile,   0666 );
77.  
78.       if   ( 'testing'  != APPLICATION_ENV )   {
79.           echo  PHP_EOL;
80.           echo   'Database Created';
81.           echo  PHP_EOL;
82.       }
83.  
84.       if   ( $withData )   {
85.           $dataSql  =   file_get_contents ( dirname ( __FILE__ )  .   '/data.sqlite.sql' );
86.           // use the connection directly to load sql in batches
87.           $dbAdapter-> getConnection ( )-> exec ( $dataSql );
88.           if   ( 'testing'  != APPLICATION_ENV )   {
89.               echo   'Data Loaded.';
90.               echo  PHP_EOL;
91.           }
92.       }
93.  
94. }  catch   (Exception   $e )   {
95.       echo   'AN ERROR HAS OCCURED:'  . PHP_EOL;
96.       echo   $e-> getMessage ( )  . PHP_EOL;
97.       return   false;
98. }
99.  
100. // generally speaking, this script will be run from the command line
101. return   true;

Now, let's execute this script. From a terminal or the DOS command line, do the following:

1. % php scripts/load.sqlite.php --withdata

You should see output like the following:

1. path/to/ZendFrameworkQuickstart/scripts$ php load.sqlite.php --withdata
2. Writing Database Guestbook in (control-c to cancel):
3. 1
4. Database Created
5. Data Loaded.

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 (in our case, we will use Zend_Db_Table), and a data mapper to connect that data source to our domain model. Finally we'll also create the controller that will interact with this model to both display existing entries and process new entries.

We'll use a » Table Data Gateway to connect to our data source; Zend_Db_Table provides this functionality. To get started, lets create aZend_Db_Table-based table class. Just as we've done for layouts and the database adapter, we can use the zf tool to assist, using the command create db-table. This takes minimally two arguments, the name by which you want to refer to the class, and the database table it maps to.

1. % zf create db-table Guestbook guestbook
2. Creating a DbTable at application/models/DbTable/Guestbook.php
3. Updating project profile 'zfproject.xml'

Looking at your directory tree, you'll now see that a new directory, application/models/DbTable/, was created, with the fileGuestbook.php. If you open that file, you'll see the following contents:

1. // application/models/DbTable/Guestbook.php
2.  
3. /**
4. * This is the DbTable class for the guestbook table.
5. */
6. class  Application_Model_DbTable_Guestbook   extends  Zend_Db_Table_Abstract
7. {
8.       /** Table name */
9.     protected   $_name    =   'guestbook';
10. }

Note the class prefix: Application_Model_DbTable. The class prefix for our module, "Application", is the first segment, and then we have the component, "Model_DbTable"; the latter is mapped to the models/DbTable/ directory of the module.

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").

Now let's create a » Data Mapper. A Data Mapper maps a domain object to the database. In our case, it will map our model,Application_Model_Guestbook, to our data source, Application_Model_DbTable_Guestbook. A typical API for a data mapper is as follows:

1. // application/models/GuestbookMapper.php
2.  
3. class  Application_Model_GuestbookMapper
4. {
5.       public   function  save ( $model );
6.       public   function  find ( $id,   $model );
7.       public   function  fetchAll ( );
8. }

In addition to these methods, we'll add methods for setting and retrieving the Table Data Gateway. To create the initial class, use the zfCLI tool:

1. % zf create model GuestbookMapper
2. Creating a model at application/models/GuestbookMapper.php
3. Updating project profile '.zfproject.xml'

Now, edit the class Application_Model_GuestbookMapper found in application/models/GuestbookMapper.php to read as follows:

1. // application/models/GuestbookMapper.php
2.  
3. class  Application_Model_GuestbookMapper
4. {
5.     protected   $_dbTable;
6.  
7.       public   function  setDbTable ( $dbTable )
8.       {
9.           if   ( is_string ( $dbTable ) )   {
10.               $dbTable  =   new   $dbTable ( );
11.           }
12.           if   (! $dbTable  instanceof Zend_Db_Table_Abstract )   {
13.             throw   new  Exception ( 'Invalid table data gateway provided' );
14.           }
15.           $this->_dbTable =   $dbTable;
16.           return   $this;
17.       }
18.  
19.       public   function  getDbTable ( )
20.       {
21.           if   ( null  ===   $this->_dbTable )   {
22.               $this-> setDbTable ( 'Application_Model_DbTable_Guestbook' );
23.           }
24.           return   $this->_dbTable;
25.       }
26.  
27.       public   function  save (Application_Model_Guestbook   $guestbook )
28.       {
29.           $data  =   array (
30.               'email'   =>   $guestbook-> getEmail ( ),
31.               'comment'  =>   $guestbook-> getComment ( ),
32.               'created'  =>   date ( 'Y-m-d H:i:s' ),
33.           );
34.  
35.           if   ( null  ===   ( $id  =   $guestbook-> getId ( ) ) )   {
36.               unset ( $data [ 'id' ] );
37.               $this-> getDbTable ( )-> insert ( $data );
38.           }   else   {
39.               $this-> getDbTable ( )-> update ( $data,   array ( 'id = ?'  =>   $id ) );
40.           }
41.       }
42.  
43.       public   function  find ( $id, Application_Model_Guestbook   $guestbook )
44.       {
45.           $result  =   $this-> getDbTable ( )-> find ( $id );
46.           if   ( 0  ==   count ( $result ) )   {
47.               return;
48.           }
49.           $row  =   $result-> current ( );
50.           $guestbook-> setId ( $row-> id )
51.                   -> setEmail ( $row-> email )
52.                   -> setComment ( $row-> comment )
53.                   -> setCreated ( $row-> created );
54.       }
55.  
56.       public   function  fetchAll ( )
57.       {
58.           $resultSet  =   $this-> getDbTable ( )-> fetchAll ( );
59.           $entries   =   array ( );
60.           foreach   ( $resultSet   as   $row )   {
61.               $entry  =   new  Application_Model_Guestbook ( );
62.               $entry-> setId ( $row-> id )
63.                   -> setEmail ( $row-> email )
64.                   -> setComment ( $row-> comment )
65.                   -> setCreated ( $row-> created );
66.               $entries [ ]  =   $entry;
67.           }
68.           return   $entries;
69.       }
70. }

Now it's time to create our model class. We'll do so, once again, using the zf create model command:

1. % zf create model Guestbook
2. Creating a model at application/models/Guestbook.php
3. Updating project profile '.zfproject.xml'

We'll modify this empty PHP class to make it easy to populate the model by passing an array of data either to the constructor or asetOptions() method. The final model class, located in application/models/Guestbook.php, should look like this:

1. // application/models/Guestbook.php
2.  
3. class  Application_Model_Guestbook
4. {
5.     protected   $_comment;
6.     protected   $_created;
7.     protected   $_email;
8.     protected   $_id;
9.  
10.       public   function  __construct ( array   $options  =   null )
11.       {
12.           if   ( is_array ( $options ) )   {
13.               $this-> setOptions ( $options );
14.           }
15.       }
16.  
17.       public   function  __set ( $name,   $value )
18.       {
19.           $method  =   'set'  .   $name;
20.           if   ( ( 'mapper'  ==   $name )  || ! method_exists ( $this,   $method ) )   {
21.             throw   new  Exception ( 'Invalid guestbook property' );
22.           }
23.           $this-> $method ( $value );
24.       }
25.  
26.       public   function  __get ( $name )
27.       {
28.           $method  =   'get'  .   $name;
29.           if   ( ( 'mapper'  ==   $name )  || ! method_exists ( $this,   $method ) )   {
30.             throw   new  Exception ( 'Invalid guestbook property' );
31.           }
32.           return   $this-> $method ( );
33.       }
34.  
35.       public   function  setOptions ( array   $options )
36.       {
37.           $methods  =   get_class_methods ( $this );
38.           foreach   ( $options   as   $key  =>   $value )   {
39.               $method  =   'set'  .   ucfirst ( $key );
40.               if   ( in_array ( $method,   $methods ) )   {
41.                   $this-> $method ( $value );
42.               }
43.           }
44.           return   $this;
45.       }
46.  
47.       public   function  setComment ( $text )
48.       {
49.           $this->_comment =   (string )   $text;
50.           return   $this;
51.       }
52.  
53.       public   function  getComment ( )
54.       {
55.           return   $this->_comment;
56.       }
57.  
58.       public   function  setEmail ( $email )
59.       {
60.           $this->_email =   (string )   $email;
61.           return   $this;
62.       }
63.  
64.       public   function  getEmail ( )
65.       {
66.           return   $this->_email;
67.       }
68.  
69.       public   function  setCreated ( $ts )
70.       {
71.           $this->_created =   $ts;
72.           return   $this;
73.       }
74.  
75.       public   function  getCreated ( )
76.       {
77.           return   $this->_created;
78.       }
79.  
80.       public   function  setId ( $id )
81.       {
82.           $this->_id =   (int )   $id;
83.           return   $this;
84.       }
85.  
86.       public   function  getId ( )
87.       {
88.           return   $this->_id;
89.       }
90. }

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

To create a new controller, use the zf create controller command:

1. % zf create controller Guestbook
2. Creating a controller at
3.     application/controllers/GuestbookController.php
4. Creating an index action method in controller Guestbook
5. Creating a view script for the index action method at
6.     application/views/scripts/guestbook/index.phtml
7. Creating a controller test file at
8.     tests/application/controllers/GuestbookControllerTest.php
9. Updating project profile '.zfproject.xml'

This will create a new controller, GuestbookController, in application/controllers/GuestbookController.php, with a single action method, indexAction(). It will also create a view script directory for the controller, application/views/scripts/guestbook/, with a view script for the index action.

We'll use the "index" action as a landing page to view all guestbook entries.

Now, let's flesh out the basic application logic. On a hit to indexAction(), we'll display all guestbook entries. This would look like the following:

1. // application/controllers/GuestbookController.php
2.  
3. class  GuestbookController   extends  Zend_Controller_Action
4. {
5.       public   function  indexAction ( )
6.       {
7.           $guestbook  =   new  Application_Model_GuestbookMapper ( );
8.           $this-> view-> entries  =   $guestbook-> fetchAll ( );
9.       }
10. }

And, of course, we need a view script to go along with that. Edit application/views/scripts/guestbook/index.phtml to read as follows:

1. <!-- application/views/scripts/guestbook/index.phtml -->
2.  
3. <p><a href= "<?php echo $this->url(
4.     array(
5.         'controller' => 'guestbook',
6.         'action'     => 'sign'
7.     ),
8.     'default',
9.     true) ?>">Sign Our Guestbook</a></p>
10.  
11. Guestbook Entries: <br />
12. <dl>
13.       <?php   foreach   ( $this-> entries   as   $entry ):   ?>
14.     <dt><?php   echo   $this-> escape ( $entry-> email )  ?></dt>
15.     <dd><?php   echo   $this-> escape ( $entry-> comment )  ?></dd>
16.       <?php   endforeach   ?>
17. </dl>

Note: Checkpoint
Now browse to "http://localhost/guestbook". You should see the following in your browser: 

Note: Using the data loader script
The data loader script introduced in this section (scripts/load.sqlite.php) can be used to create the database for each environment you have defined, as well as to load it with sample data. Internally, it utilizes Zend_Console_Getopt, which allows it to provide a number of command line switches. If you pass the "-h" or "--help" switch, it will give you the available options: 

1. Usage: load.sqlite.php   [  options   ]
2. --withdata|-w         Load database with sample data
3. --env|-e   [    ]         Application environment   for  which to create database
4.                         (defaults to development )
5. --help|-h             Help -- usage message ) ] ]

The "-e" switch allows you to specify the value to use for the constant   APPLICATION_ENV  -- which in turn allows you to create a SQLite database for each environment you define. Be sure to run the script for the environment you choose for your application when deploying.