Skip to end of metadata
Go to start of metadata

<h2>General Goals</h2>

<ul>
<li>SHOULD do less in each sub-component</li>
<li>SHOULD have a more clear and concise API in each sub-componet</li>
<li>SHOULD create a set of sub-components that have a small core feature-set, with many opt-in injectable features</li>
<li>SHOULD have more optional reuse of base ZF components: Zend\Log, Zend\EventManager</li>
<li>SHOULD create an API that is open to extension at all times</li>
<li>SHOULD favor composition over inheritance for better flexibility and dependency injection</li>
<li>SHOULD attempt to keep each components API as minimal as possible to allow for growth over the 2.0 time period</li>
<li>SHOULD NOT have a hard dependency on any 3rd party code (including ZF components)</li>
<li>SHOULD create a set of interfaces and best practices to facilitate plug-ability in all Zend\DB core components</li>
<li>SHOULD NOT include features in core components that can be solved by the Plug-in architecture</li>
<li>SHOULD NOT attempt to solve problems in the Model domain:
<ul>
<li>like mapping of column names in various components</li>
</ul>
</li>
<li>SHOULD follow ZF2 standard configuration practices</li>
</ul>

<h3>Motivation & Discussion</h3>

<p>Each class in the Zend\Db component should adhere to the single-responsibility principle. In ZF1's Db Component, there were some instances where adapter objects tried to ascertain metadata, where table objects attempted to convert column names, and so on. In ZF2, each class should be doing one job, and we should build up infrastructure so that this components set of classes can work together to attain a rich functionality, without breaking the single-responsibility principle.</p>

<p>Plugability is a major step forward for a database abstraction layer. Over the past few years, as the current iteration has become more popular, more features are requested. Each new feature request generally comes with it's own concerns that apply to the project as a whole: "How useful is the feature?", "How does this feature impact performance?", "How wide spread is the need for this feature?". Generally, features are added to the core component bloating the core component and adding a new set of code that has to be maintained. Plugins allow us to keep the core feature set and code base very small, but also allow us to provide new <strong>opt-in</strong> features. This shifts the onus onto the developer to decide with features they want to use, but also understand the performance impact they impart. Ultimately, this is a good thing since we can offer the feature, but the consumer can have full control over the inclusion of that feature into their project.</p>

<p>To attain optional reuse, we should attempt to provide a lightweight bridge class that allows plugging in external components, like Zend\EventManager or Zend\Log so that we can obtain a richer, yet decoupled, infrastructure.</p>

<h2>MUST have a Zend\Db\Adapter component</h2>

<ul>
<li>MUST create a distinct abstraction for interfacing with various PHP db drivers
<ul>
<li>MUST support drivers:
<ul>
<li>PDO/mysql</li>
<li>PDO/oracle</li>
<li>PDO/db2</li>
<li>PDO/postgres</li>
<li>PDO/sqlite</li>
<li>PDO/sqlserver</li>
<li>ext/mysqli</li>
<li>ext/ibmdb2</li>
<li>ext/oci8</li>
<li>ext/sqlsrv from MS</li>
<li>ext/postgres</li>
</ul>
</li>
<li>SHOULD support drivers
<ul>
<li>PDO/firebird</li>
<li>ext/mysql</li>
<li>ext/sqlite3</li>
</ul>
</li>
</ul>
</li>
<li>MUST contain a registry component to allow for storage of named connections</li>
<li>MUST support plug-ablity via callbacks at various times during runtime:
<ul>
<li>pre-prepare & pre-execute</li>
<li>post-prepare & post-execute</li>
<li>pre-connect & post-connect</li>
<li>pre-connect-close & post-connect-close</li>
</ul>
</li>
<li>MUST NOT contain any SQL</li>
<li>MUST be able to report to consumers the capabilities concerning statement execution and ability to prepare statements</li>
<li>MUST be able to run non-prepared queries</li>
<li>MUST support platform abstraction
<ul>
<li>MUST provide an API for quoting various elements</li>
<li>MUST provide an API for retrieving server version information</li>
</ul>
</li>
<li>SHOULD be able to successfully wrap ANY driver (past, present or future) regardless of its ability to parse and execute parameritized queries</li>
</ul>

<h3>Motivation & Discussion</h3>

<ul>
<li>The main motivation between the role of this component and the one below, the Query component, is to separate the the API's responsible for "how" consumers speak to the database (Adapter) and "what" they speak to the database (Query). This solves the current real world problem of modeling an adapter after two drivers that connect to the same vendor platform. For example ext/mysqli and PDOMySQL. Both have separate database interaction APIS, yet they both share the MySQL dialect. This separation will provide us with the appropriate amount of abstract to grow our adapters when new drivers are created in PHP that use the same SQL dialects.</li>
<li>There are some queries that current drivers simply cannot prepare; they must just be executed. That said, each adapter must be able to run prepared and straight queries and return a resultset for each. Being able to run non-prepared statements is a feature request that comes with many votes that has yet to be implemented in the current Zend_Db_Adapter implementation.</li>
<li>See main goals for notes on plugability.</li>
<li>The registry is an important sub-component of Zend\Db\Adapter since it allows you to store a collection of adapters & connections. IN some situations, it makes a lot of sense to have multiple connections open so that you can create a strategy around how to use them. For example, one might be able to create two connections to different database, and name them "read" and "writer". This then allows them to create a strategy in their appliction as to which one to use when talking to the data store.</li>
</ul>

<h3>Usage</h3>

<ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
use Zend\Db\Adapter;

$dbAdapter = new Adapter\Adapter(
new Adapter\Driver\Mysqli(new Adapter\DriverOptions($options)),
new Adapter\Platform\Mysql
);

// there will also be a factory

$dbAdapterFactory = new Adapter\AdapterFactory();
$dbAdapter = $dbAdapterFactory->create($options);

// general usage:

$statement = $dbAdapter->query($sql); // this will use the $queryMode option, by default set to Adapter\Adapter::QUERY_MODE_PREPARE
$result = $statement->execute($parameters); // $result is iterable, instance of DriverResult

// direct execute
$result = $dbAdapter->query($sql, Adapter\Adapter::QUERY_MODE_EXECUTE);
]]></ac:plain-text-body></ac:macro>

<h3>Implementation Details</h3>

<ul>
<li>Adapter will not have an interface</li>
<li>Adapter is composed of a Driver and a Platform object</li>
<li>Driver object: responsible for
<ul>
<li>connecting to php extension</li>
<li>reporting capablities of the extension</li>
<li>is composed of and is responsible for coordination of 3 objects:
<ul>
<li>Connection object: responsible for php extension binding to connection
<ul>
<li>there is a connection interface)</li>
</ul>
</li>
<li>Statement Object: responsible for php extension binding to statement functions
<ul>
<li>there is a statement interface)</li>
</ul>
</li>
<li>Result object: responsible for data iteration of results
<ul>
<li>there is a result interface, it implements Zend\Db\ResultSet\DataSource interface)</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
<li>Platform object: responsible for
<ul>
<li>knowing how to quote in a vendor rdbms specific way</li>
<li>knowing the "name" of the database</li>
</ul>
</li>
<li>Code</li>
</ul>

<h4>Interfaces</h4>

<ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
namespace Zend\Db {
class Adapter
{
const QUERY_MODE_EXECUTE = 'execute';
const QUERY_MODE_PREPARE = 'prepare';

const PREPARE_TYPE_POSITIONAL = 'positional';
const PREPARE_TYPE_NAMED = 'named';

public function __construct(Adapter\AbstractDriver $driver, Adapter\PlatformInterface $platform) {}
public function getDriver() {}
public function setQueryMode($queryMode) {}
public function getPlatform() {}
public function query($sql, $prepareOrExecute = self::QUERY_MODE_PREPARE) {}

}
}

namespace Zend\Db\Adapter\Driver {

interface DriverInterface

Unknown macro: { public function getConnection(); public function getStatement(); public function getResult(); }

interface NamedParameterDriver

Unknown macro: { public function formatNamedParameter($name) }

interface ConnectionInterface

Unknown macro: { public function setDriver(DriverInterface $driver); public function getDefaultCatalog(); public function getDefaultSchema(); public function getResource(); public function connect(); public function isConnected(); public function disconnect(); public function beginTransaction(); public function commit(); public function rollback(); public function execute($sql); // return result set public function prepare($sql); // must return StatementInterface object }

interface StatementInterface

Unknown macro: { public function setDriver(DriverInterface $driver); public function setResource($resource); public function getResource(); public function setSql($sql); public function getSql(); public function execute($parameters = null); }

interface ResultInterface extends Zend\Db\ResultSet\DataSourceInterface

Unknown macro: { public function setResource($resource); public function getResource(); }

}

namespace Zend\Db\Adapter\Platform {
interface PlatformInterface

Unknown macro: { public function getQuoteIdentifierSymbol(); public function quoteIdentifier($identifier); public function getQuoteValueSymbol(); public function quoteValue($value); }

}
]]></ac:plain-text-body></ac:macro>

<h2>MUST have a Zend\Db\Sql component</h2>

<ul>
<li>MUST create a distinct abstraction layer for creating DML (Data Manipulation Language) for:
<ul>
<li>MUST support SELECT</li>
<li>MUST support INSERT</li>
<li>MUST support UPDATE</li>
<li>MUST support DELETE</li>
</ul>
</li>
<li>MUST create a distinct abstraction layer for creating DDL (Data Definition Language) for:
<ul>
<li>MUST support CREATE</li>
<li>MUST support ALTER</li>
<li>MUST support DROP</li>
<li>MUST support TRUNCATE</li>
<li>SHOULD support RENAME</li>
<li>SHOULD support COMMENT</li>
</ul>
</li>
<li>MUST support a distinct abstraction layer for creating TCL (Transaction Control Language) for:
<ul>
<li>MUST support COMMIT</li>
<li>MUST support ROLLBACK</li>
<li>SHOULD support SAVEPOINT</li>
<li>SHOULD support SET TRANSACTION</li>
</ul>
</li>
<li>MUST support Vendor specific variances when generating SQL</li>
<li>SHOULD support Vendor specific features for query generation via Vendor object API</li>
<li>MUST support the following Vendor specific Dialects:
<ul>
<li>MUST support vendor neutral SQL92 dialect</li>
<li>MUST support MySQL dialect</li>
<li>MUST support Postgres dialect</li>
<li>MUST support SQLite dialect</li>
<li>MUST support Oracle dialect</li>
<li>MUST support SQLServer dialect</li>
<li>MUST support DB2 on i5 dialect</li>
<li>MUST supoort DB2 (general) dialect</li>
<li>SHOULD support Firebird and Informix variant dialect</li>
</ul>
</li>
<li>MUST support the ability to serialize queries</li>
<li>MUST be able to produce full bound statements (bound in userland) as well as parameritized statements</li>
<li>MUST be able to interrogate the Adapter for parameritization capabilities and provide statements capable of being executed</li>
</ul>

<h3>Motivation & Discussion</h3>

<ul>
<li>See above in Zend\Db\Adapter for the motivation in Adapter/Query separation.</li>
<li>It is important that we are able to create, for as much as possible, a DDL query generation component. This will set the framework such that we can utilize this component during unit testing time (see more notes in the ZendTest\Db section). This also fulfills the long-standing request for a schema management component.</li>
</ul>

<h3>Usage</h3>

<ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
use Zend\Db\Adapter,
Zend\Db\Sql;

$insert = new Sql\Select();
$insert->into($table)>columns($columns)>values($values);

$dbAdapter->query($insert); // OR:
$dbAdapter->query($insert->toSql($dbAdapter)); // this is what the avove would do under the hood
]]></ac:plain-text-body></ac:macro>

<h2>MUST have a Zend\Db\ResultSet component</h2>

<ul>
<li>MUST model a database result set in a vendor and driver neutral way</li>
<li>SHOULD be capable of modeling rows as arrays or row objects</li>
<li>MUST support positional keys as well as column name keys in rows</li>
<li>MUST be pluggable at various points in runtime:
<ul>
<li>pre-populate and post-populate</li>
</ul>
</li>
<li>MUST be serializable and cacheable</li>
<li>SHOULD utilize all facilities of PHP to be able to always get row objects as array</li>
<li>SHOULD be capable of creating Row objects from arrays of data</li>
</ul>

<h3>Motivation & Discussion</h3>

<ul>
<li>This resultSet component should be capable of modeling the results as they come back from various databases. This allow consumers a flexible and standard way of storing their result sets. By having this component, consumers will be able to apply array operations to their collection. They will also be able to choose the type of the rows inside of the collect: they can be plain old PHP arrays, or they can be a particular object</li>
</ul>

<h3>Usage</h3>

<ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
$result = $statement->execute($parameters);

foreach ($result as $row) {
echo $row['column_name'];
// OR
echo $row->column_name;
}
]]></ac:plain-text-body></ac:macro>

<h2>MUST have a Zend\Db\Metadata component</h2>

<ul>
<li>MUST create a component that is capable of interrogating a database for schema information</li>
<li>MUST be able to describe schema to consumers in a vendor neutral way</li>
<li>MUST contain the various Vendor specific queries (Created by Zend\Db\Query) for interrogating database</li>
<li>MUST be cacheable via Zend\Cache</li>
<li>MUST be serializable</li>
<li>MUST be able to describe the capabilities</li>
</ul>

<h3>Motivation & Discussion</h3>

<ul>
<li>The main motivation here is to be able to capture information about the structure of the database and present it in a standardized API for other components and consumers to be able to interrogate. Currently, we do this to some extent in the describeTable() calls of Zend_Db_Adapter. By having this standard API, developers will be able to harness this to be able to create more interesting ORM or simply better Modeling solutions.</li>
</ul>

<h3>Usage</h3>

<ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
use Zend\Db\Metdata;

// scanner usage, API is flat

$metadataScanner = new Metadata\MetadataScanner(
new Metadata\Scanner\InformationSchemaScanner($dbAdapter);
);
$metadataScanner->getTables($schema, $catalog); // $schema / $catalog are optional
$metadataScanner->getColumns($table);
$metadataScanner->getConstraints($table);
$metadataScanner->getColumns($table);
$metadataScanner->getViews($table);
$metadataScanner->getTriggers($table);
$metadataScanner->getColumnInformation($column, $table);

// tree usage, API is heirarchical

$metadataTree = $metadataScanner->createMetadataTree();
$metadataTree->some_table->columns();
$metadataTree->another_table->column_name->type;

file_put_contents('db-metadata.php', '<?php return ' . var_export($metadataTree->toArray()));
]]></ac:plain-text-body></ac:macro>

<h2>MUST have a Zend\Db\TableGateway component</h2>

<ul>
<li>MUST implement a component that subscribes to the Table Gateway pattern described in PoEAA
<ul>
<li>API: insert(), update(), delete(), select()</li>
</ul>
</li>
<li>MUST accept Zend\Db\Query objects</li>
<li>MUST be vendor and driver neutral</li>
<li>SHOULD be pluggable at various points in the runtime:
<ul>
<li>pre-insert & post-insert</li>
<li>pre-update & post-update</li>
<li>pre-delete & post-delete</li>
<li>pre-select & post-select</li>
</ul>
</li>
<li>MUST return Zend\Db\ResultSet object</li>
</ul>

<ul>
<li>Currently, we have table like operations located in two places, both the Zend_Db_Adapter as well as the Zend_Db_Table. The benefit of having them in the adapter is that you do not have to subscribe to Zend_Db_Table, which has its performance implications as well as its hard coupling with Zend_Db_Table_Row & Rowset. By creating a more lightweight Table Gateway component, the "table API" can be contained in one place and it will be a more attractive API to adopt for those "table only" operations. Furthermore, the "table API" is out of place currently in Zend_Db_Adapter.</li>
</ul>

<h3>Usage</h3>

<ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
$table = new TableGateway($dbAdapter, 'table_name');
$table->insert($values);
$table->delete($where);
$table->update($values, $where);
$resultSet = $table->select(new Predicate\Between('total', 5, 10));
]]></ac:plain-text-body></ac:macro>

<h2>MUST have a Zend\Db\RowGateway component</h2>

<ul>
<li>MUST implement a component that subscribes to the Row Gateway pattern described in PoEAA</li>
<li>MUST be able to accept Zend\Db\Metadata to understand the database schema in order to process row operations</li>
<li>SHOULD be injectable into any Zend\Db\ResultSet such that any row can become a Row Gateway</li>
<li>SHOULD be pluggable at various point in the runtime:
<ul>
<li>pre-save & post-save</li>
<li>pre-delete & post-delete</li>
<li>pre-populate & post-populate</li>
</ul>
</li>
</ul>

<h3>Motivation & Discussion</h3>

<ul>
<li>Currently, the Row Gateway is hard coupled to the Zend_Db_Table component. If Row were decoupled from TableGateway, that would mean it can be used in any situation where a developer has retrieved a row from the database.. even in situations where they have issued SQL directly as a statement or to the adapter (prepared or not). This would allow the Zend\Db\ResultSet component to cast any row to a RowGateway object that supports the RowGateway pattern thus giving the developer the opportunity to have row objects delete() and save() themselves to/in persistent storage.</li>
<li>Moreover, the RowGateway should also have the ability to be pluggable. This allows developers the maximum amount of flexibility in cases where they would like to use these row objects as the model or the subject of a model, OR the target for a mapper.</li>
</ul>

<h3>Usage</h3>

<ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
$table = new TableGateway($dbAdapter, 'table_name');
$resultSet = $table->select(new Predicate\Between('total', 5, 10));
$resultSet->setReturnPrototype(new RowGateway($adapter));
$row = $resultSet->current();

$row->set('column', $value);
$row->save();
]]></ac:plain-text-body></ac:macro>

<h3>Interface</h3>

<ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
use Zend\Db\Adapter\Adapter;

interface RowGatewayInterface
{
public function setAdapter(Adapter $adapter);
public function save();
public function delete();
}
]]></ac:plain-text-body></ac:macro>

<h2>MUST have a ZendTest\Db component</h2>

<ul>
<li>MUST create a base schema that the majority of unit tests can share</li>
<li>MUST have cleanup mechanism so that schema's can return to their original state without having to "rebuild" entities</li>
<li>MUST have the capability to dynamically create schema when base schema does not model a particular problem</li>
<li>SHOULD create better facilities for mock adapters to be used by components that are driver and dialect agnostic</li>
</ul>

<h3>Motivation & Discussion</h3>

<ul>
<li>Currently, on some systems, it takes upwards of 4 hours to run the full test suite. Other systems might be quicker, but slowness is a noticeable quality across all major databases. This is primarily due to the fact that DDL operations, specifically creating and dropping schemas is extremely expensive. That said, it is important that we have a base schema that most unit tests can share, and a schema we can get back to a "clean" state. Furthermore, for tests that the base schema does not satisfy, there will be operations they can use to create a temporary schema that will be destroyed during cleanup of that particular test.</li>
<li>By creating a query and adapter abstraction layer that is well tested, and by creating components that are both driver/adapter neutral and vendor query neutral, we can avoid having to create mutliple tests for each flavor of database we intend to test on. For example, currently Zend_Db_Table has an implementation of all its tests for each database platform that Zend_Db supports. This in and of itself creates a maintenance nightmare as for each new unit test that is created, it actually creates n tests where n is the number of currently supported platforms. In most cases, developers do not have the means to be able to test some platforms, thus they can never be sure if their solution is completely safe across all platoforms. Since we are creating Zend\Db\TableGateway and Zend\Db\RowGateway with standard API's that are vendor neutral, this will get rid of the need for developers to have to test against various plaforms in their consuming components (like Zend\Paginator).</li>
</ul>

Labels:
None
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. Dec 01, 2011

    <p>First, I have to say this looks awesome. I am especially looking forward to the Metadata component, the DDL support in the Sql component, decoupling RowGateway from TableGateway, and the plug-in support.<br />
    I'd like some clarification on metadata handling:</p>
    <ul>
    <li>will the functionality provided by Zend\Db\Metdata\MetadataScanner be accessible from instances of Zend\Db\TableGateway?<br />
    I.e.:
    <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
    $table = new TableGateway($dbAdapter, 'table_name');
    $constraints = $table->getConstraints();
    ]]></ac:plain-text-body></ac:macro></li>
    <li>In Zend\Db\Metdata\MetadataScanner functions expecting $table, is this expected to be an instance of Zend\Db\TableGateway or will a string be accepted too?
    <ul>
    <li>if a string is accepted, these functions should accept a schema and catalog too</li>
    </ul>
    </li>
    <li>Will Zend\Db\Metdata\MetadataScanner support a setting default schema and/or catalog?</li>
    </ul>

    <p>I think Zend\Db\Metdata\MetadataScanner::getViews() should have the same signature as getTables:</p>
    <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
    $metadataScanner->getViews($schema, $catalog); // $schema / $catalog are optional
    ]]></ac:plain-text-body></ac:macro>
    <p>not:</p>
    <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
    $metadataScanner->getViews($table);
    ]]></ac:plain-text-body></ac:macro>

    <p>Thanks.</p>