ERD design tools

Create a Zend Framework project according to Using S2Container with Zend_Controller.

Create the s2erd.php file that sets S2Erd information in the application/configs direcotry.

The Table class of PHP can be defined directly from the data of the ERD design tool by setting an auto loader for S2Erd.
While the table definition is unfixed, you can read the table class definition by this auto loader. After fixing the table definition, output the Table class definition to the file by Zend_Tool command.

Run the zf command of Zend_Tool, and confirm whether the following command is added.

% zf.sh --help
  . . .
  . . .
  . . .

  ERMModel
    zf create erm-model erm-file[=<project/var/db/project.erm>]

  MWBModel
    zf create mwb-model schema[=mydb] mwb-file[=<project/var/db/project.mwb>]

%

Run the command for the ERD design tool used. the table class definition file is output to the application/models/DbTable directory. When "--pretend" is added as an option of the zf command, the table class definition is output to a standard output.

The data file of the following ERD design tool is prepared in the var/db directory as a sample. Please refer to the sample data base in the Zend_Db document for the table definition.

The MWBModel command is executed as an example.

% ls application/models/DbTable
% zf create mwb-model s2

MySQL Workbench File:   /project/var/db/project.mwb

create : application/models/DbTable/Abstract.php
create : application/models/DbTable/Accounts.php
create : application/models/DbTable/Bugs.php
create : application/models/DbTable/BugsProducts.php
create : application/models/DbTable/Products.php

% cat application/models/DbTable/Bugs.php
<?php
/**
 * @S2Component('autoBinding' => 'none')
 */
class Model_DbTable_Bugs extends Model_DbTable_Abstract {

    const PNAME = 'bugs';
    const LNAME = 'bugs';
    const COMMENT = '';

    public static $FIELDS = array(
        'bug_id' => array(
              'pname' => 'bug_id',
              'lname' => 'bug_id',
              'type' => 'INT',
              'type_opt' => null,
              'length' => -1,
              'scale' => -1,
              'precision' => -1,
              'nn' => true,
              'pk' => true,
              'fk' => false,
              'default' => '',
              'comment' => '',
              'options' => null),

              . . . . . 
              . . . . . 
              . . . . . 

        'verified_by' => array(
              'pname' => 'verified_by',
              'lname' => 'verified_by',
              'type' => 'VARCHAR',
              'type_opt' => null,
              'length' => 100,
              'scale' => -1,
              'precision' => -1,
              'nn' => false,
              'pk' => false,
              'fk' => true,
              'default' => '',
              'comment' => '',
              'options' => null));

    public static $FILTERS = array(
        'bug_id' => array('Int'));

    public static $VALIDATORS = array(
        'bug_id' => array('presence' => 'required', 'Int'),
        'bug_description' => array('allowEmpty' => 'true', array('StringLength', 0, 100, 'UTF-8')),
        'bug_status' => array('allowEmpty' => 'true', array('StringLength', 0, 20, 'UTF-8')),
        'reported_by' => array('allowEmpty' => 'true', array('StringLength', 0, 100, 'UTF-8')),
        'assigned_to' => array('allowEmpty' => 'true', array('StringLength', 0, 100, 'UTF-8')),
        'verified_by' => array('allowEmpty' => 'true', array('StringLength', 0, 100, 'UTF-8')));

    protected $_name = 'bugs';
    protected $_primary = array('bug_id');
    protected $_sequence = false;

    protected $_dependentTables = array('Model_DbTable_BugsProducts');

    protected $_referenceMap = array(
        'ReportedBy' => array(
           'columns' => array('reported_by'),
           'refTableClass' => 'Model_DbTable_Accounts',
           'refColumns' => array('account_name')),
        'AssignedTo' => array(
           'columns' => array('assigned_to'),
           'refTableClass' => 'Model_DbTable_Accounts',
           'refColumns' => array('account_name')),
        'VerifiedBy' => array(
           'columns' => array('verified_by'),
           'refTableClass' => 'Model_DbTable_Accounts',
           'refColumns' => array('account_name')));

}
%

As a sample, create the UnitTest of the Table class of the accounts table. UnitTest file is AccountsTest.php in the tests/application/models/DbTable directory.

class Model_DbTable_AccountsTest extends PHPUnit_Framework_TestCase {

    public function testFetchAll() {
        $this->assertEquals(5, count($this->model->fetchAll()));
    }

    public function setUp() {
        $this->model = s2get('Model_DbTable_Accounts');
    }

    public function tearDown() {
        $this->model = null;
    }
}

The result of UnitTest becomes the next.

% phpunit application/models/DbTable
PHPUnit 3.4.2 by Sebastian Bergmann.

.

Time: 1 second

OK (1 test, 1 assertion)
%

Because the class definition is dynamically defined by Autoload of S2Erd, you can use Model_DbTable_Accounts class. (actually source file not exists.)

As a sample, create the service class that uses the Table model and the DB adaptor, in the application/services directory as Sample.php.

class Service_Sample {

    public function setModel(Model_DbTable_Bugs $model) {
        $this->model = $model;
    }

    public function setAdapter(Zend_Db_Adapter_Abstract $adapter) {
        $this->adapter = $adapter;
    }
    
    public function fetchAllBugs() {
        return $this->model->fetchAll()->toArray();
    }

    public function fetchProductBugDescriptions() {
        $select = $this->adapter->select()->from(array('bp' => Model_DbTable_BugsProducts::PNAME), array());
        $select->joinLeft(array('b' => Model_DbTable_Bugs::PNAME), 'b.bug_id = bp.bug_id', array('bug_description'));
        $select->joinLeft(array('p' => Model_DbTable_Products::PNAME), 'p.product_id = bp.product_id', array('product_name'));
        return $this->adapter->fetchAll($select);
    }
}

Create the UnitTest of Sample class in tests/application/services directory as SampleTest.php.

class Service_SampleTest extends PHPUnit_Framework_TestCase {

    public function testFetchAllBugs() {
        $this->assertEquals(3, count($this->service->fetchAllBugs()));
    }

    public function testFetchAllBugsWithAccounts() {
        $rows = $this->service->fetchProductBugDescriptions();
        $this->assertEquals(6, count($rows));
    }

    public function setUp() {
        require(APPLICATION_PATH . '/dicons/dicon.php'); 
        $this->service = s2get('Service_Sample');
    }

    public function tearDown() {
        $this->service = null;
    }
}

The result of UnitTest becomes the next.

% phpunit application/services
PHPUnit 3.4.2 by Sebastian Bergmann.

..

Time: 0 seconds

OK (2 tests, 2 assertions)
%

Please refer to "Using S2Container in the Action method" for how to use the service class in the action controller.