Creating a Zend Framework Project [2/2]

application.ini

[production]
phpSettings.display_startup_errors = 0
phpSettings.display_errors = 0
includePaths.library = APPLICATION_PATH "/../library"
bootstrap.path = APPLICATION_PATH "/Bootstrap.php"
bootstrap.class = "Bootstrap"
resources.frontController.controllerDirectory = APPLICATION_PATH "/controllers"
resources.frontController.params.displayExceptions = 0

resources.dbDefinition.Accounts.name = "accounts"
resources.dbDefinition.Accounts.dependentTables[] = "Bugs"

resources.dbDefinition.Bugs.name = "bugs"
resources.dbDefinition.Bugs.dependentTables[] = "ZendDb_Model_DbTable_BugsProducts"
resources.dbDefinition.Bugs.referenceMap.Reporter.columns[] = "reported_by"
resources.dbDefinition.Bugs.referenceMap.Reporter.refTableClass = "ZendDb_Model_DbTable_Accounts"
resources.dbDefinition.Bugs.referenceMap.Reporter.refColumns[] = "account_name"
resources.dbDefinition.Bugs.referenceMap.Engineer.columns[] = "assigned_to"
resources.dbDefinition.Bugs.referenceMap.Engineer.refTableClass = "ZendDb_Model_DbTable_Accounts"
resources.dbDefinition.Bugs.referenceMap.Engineer.refColumns[] = "account_name"
resources.dbDefinition.Bugs.referenceMap.Verifier.columns[] = "verified_by"
resources.dbDefinition.Bugs.referenceMap.Verifier.refTableClass = "ZendDb_Model_DbTable_Accounts"
resources.dbDefinition.Bugs.referenceMap.Verifier.refColumns[] = "account_name"

resources.dbDefinition.Products.name = "products"
resources.dbDefinition.Products.dependentTables[] = "ZendDb_Model_DbTable_BugsProducts"

resources.dbDefinition.BugsProducts.name = "bugs_products"
resources.dbDefinition.BugsProducts.referenceMap.Bug.columns[] = "bug_id"
resources.dbDefinition.BugsProducts.referenceMap.Bug.refTableClass = "ZendDb_Model_DbTable_Bugs"
resources.dbDefinition.BugsProducts.referenceMap.Bug.refColumns[] = "bug_id"
resources.dbDefinition.BugsProducts.referenceMap.Product.columns[] = "product_id"
resources.dbDefinition.BugsProducts.referenceMap.Product.refTableClass = "ZendDb_Model_DbTable_Products"
resources.dbDefinition.BugsProducts.referenceMap.Product.refColumns[] = "product_id"

appnamespace = "ZendDb_"
[staging : production]

[testing : production]
phpSettings.display_startup_errors = 1
phpSettings.display_errors = 1

[development : production]
phpSettings.display_startup_errors = 1
phpSettings.display_errors = 1
resources.frontController.params.displayExceptions = 1

resources.db.adapter = "Pdo_Sqlite"
resources.db.params.dbname = APPLICATION_PATH "/../db/development"

Bootstrap.php

<?php
/**
 *
 * The Application Bootstrap
 * @author zircote
 * @package ZendDb
 *
 */
class Bootstrap extends Zend_Application_Bootstrap_Bootstrap
{
    /**
     *
     * @return Zend_Db_Table_Definition
     */
    protected function _initDbDefinition ()
    {
        /**
         *
         * load the application config values so we may use them.
         * @var Zend_Config
         */
        $options = $this->getOption('resources');
        /**
         *
         * instantiate the Db definition
         * @var Zend_Db_Table_Definition
         */
        $definition = new Zend_Db_Table_Definition($options['dbDefinition']);
        /**
         * place the Zend_Db_Table_Definition into a registry container for
         * access.
         */
        Zend_Registry::set('dbDefinition', $definition);
        return $definition;
    }
}

Bugs.php

<?php
class ZendDb_Model_DbTable_Bugs extends Zend_Db_Table_Abstract
{
    /**
     *
     * table name
     * @var string
     */
    protected $_name = 'bugs';
    /**
     *
     * table dependencies, this should be the class name of the model which
     * defines the dependency.
     * @var array
     */
    protected $_dependentTables = array('ZendDb_Model_DbTable_BugsProducts');
    /**
     *
     * Reference map to parent model defined as
     * - columns: column name (string)
     * - refTableClass: the classname of the model definine the table
     * - refColumns the parent objects column name representative of this
     *    reference.
     * onDelete: the action to take when the parent record is deleted, this
     *     is intended to serve as a replacement for RDBMS that do no support
     *     DRI, i.e. MyISAM, Sqlite, etc.
     * onUpdate: the action to take when the parent record is updated, see
     *    onDelete above.
     *
     * @var array
     */
    protected $_referenceMap = array(
        'Reporter' => array(
            'columns' => 'reported_by',
            'refTableClass' => 'ZendDb_Model_DbTable_Accounts',
            'refColumns' => 'account_name'
        ),
        'Engineer' => array(
            'columns' => 'assigned_to',
            'refTableClass' => 'ZendDb_Model_DbTable_Accounts',
            'refColumns' => 'account_name'
        ),
        'Verifier' => array(
            'columns' => array('verified_by'),
            'refTableClass' => 'ZendDb_Model_DbTable_Accounts',
            'refColumns' => array('account_name')
        )
    );
}

create-models.sh

prompt> zf configure db-adapter "adapter= Pdo_Sqlitee&dbname=../db/development" development
A db configuration for the development section has been written to the application config file.

prompt> zf create dbtable Accounts accounts
Creating a DbTable at /Users/zircote/Workspace/ZendDb/application/models/DbTable/Accounts.php
Updating project profile '/Users/zircote/Workspace/ZendDb/.zfproject.xml'

prompt> zf create dbtable Bugs bugsCreating a DbTable at /Users/zircote/Workspace/ZendDb/application/models/DbTable/Bugs.php
Updating project profile '/Users/zircote/Workspace/ZendDb/.zfproject.xml'

prompt> zf create dbtable BugsProducts bugs_products
Creating a DbTable at /Users/zircote/Workspace/ZendDb/application/models/DbTable/BugsProducts.php
Updating project profile '/Users/zircote/Workspace/ZendDb/.zfproject.xml'

prompt> zf create dbtable Products products
Creating a DbTable at /Users/zircote/Workspace/ZendDb/application/models/DbTable/Products.php
Updating project profile '/Users/zircote/Workspace/ZendDb/.zfproject.xml'

development.sql

CREATE TABLE accounts (
  account_name      VARCHAR(100) NOT NULL PRIMARY KEY
);
 
CREATE TABLE products (
  product_id        INTEGER NOT NULL PRIMARY KEY,
  product_name      VARCHAR(100)
);
 
CREATE TABLE bugs (
  bug_id            INTEGER NOT NULL PRIMARY KEY,
  bug_description   VARCHAR(100),
  bug_status        VARCHAR(20),
  reported_by       VARCHAR(100) REFERENCES accounts(account_name),
  assigned_to       VARCHAR(100) REFERENCES accounts(account_name),
  verified_by       VARCHAR(100) REFERENCES accounts(account_name)
);
 
CREATE TABLE bugs_products (
  bug_id            INTEGER NOT NULL REFERENCES bugs,
  product_id        INTEGER NOT NULL REFERENCES products,
  PRIMARY KEY       (bug_id, product_id)
);

INSERT INTO accounts values ('zircote');
INSERT INTO accounts values ('zombified');
INSERT INTO accounts values ('caroro');
INSERT INTO products (product_id,product_name) values (1,'Skulk');
INSERT INTO products (product_id,product_name) values (2,'EveLib');
INSERT INTO products (product_id,product_name) values  (3,'Amarium');
INSERT INTO bugs ('bug_description','bug_status','reported_by','assigned_to','verified_by') VALUES ('minor problem with build status', 'EMERG','zircote','caroro','zombified');
INSERT INTO bugs_products VALUES (1,2);
INSERT INTO bugs_products VALUES (2,3);
INSERT INTO bugs_products VALUES (3,1);

Models.php

<?php
class ZendDb_Model_DbTable_Accounts extends Zend_Db_Table
{
    public function __construct(){
        parent::__construct('Accounts', Zend_Registry::get('dbDefinition'));
    }
}

class ZendDb_Model_DbTable_Bugs extends Zend_Db_Table
{
    public function __construct(){
        parent::__construct('Bugs', Zend_Registry::get('dbDefinition'));
    }
}

class ZendDb_Model_DbTable_BugsProducts extends Zend_Db_Table
{
    public function __construct ()
    {
        parent::__construct('BugsProducts', Zend_Registry::get('dbDefinition'));
    }
}

class ZendDb_Model_DbTable_Products extends Zend_Db_Table
{
    public function __construct ()
    {
        parent::__construct('Products', Zend_Registry::get('dbDefinition'));
    }
}

setup-db.sh

prompt> mkdir db
prompt> cat > db/development.sql
# Contents of development.sql 
^c
prompt> sqlite3 --init db/development
sqlite> .read ./db/development.sql
sqlite> .tables
accounts       bugs           bugs_products  products
sqlite> .quit

using-the-models.php

<?php

        $products = new ZendDb_Model_DbTable_Products();
        $products = $products->fetchAll();
        foreach ($products as $product) {
            echo '==============', PHP_EOL;
            echo 'Product', PHP_EOL;
            print_r($product->toArray());
            $bp = $product->findManyToManyRowset(
                'ZendDb_Model_DbTable_Bugs',  // Find This data
                'ZendDb_Model_DbTable_BugsProducts', // Joined on this table
                'Product');// using this rule
            foreach ($bp as $bug) {
                echo 'Bugs:', PHP_EOL;
                print_r($bug->toArray());
                $reporter = $bug->findParentRow(
                    'ZendDb_Model_DbTable_Accounts',
                    'Reporter');
                echo '$engineer:', PHP_EOL;
                print_r($reporter->toArray());
                $engineer = $bug->findParentRow(
                    'ZendDb_Model_DbTable_Accounts',
                    'Engineer');
                echo '$verifier:', PHP_EOL;
                print_r($engineer->toArray());
                $verifier = $bug->findParentRow(
                    'ZendDb_Model_DbTable_Accounts',
                    'Verifier');
                echo '$verifier:', PHP_EOL;
                print_r($verifier->toArray());
            }
        }
        echo '==============', PHP_EOL;