Skip to end of metadata
Go to start of metadata

<h4>Requirements</h4>
<ul>
<li>Create Zend\DB\Registry
<ul>
<li>Motivation: need a place for Driver connections, default and otherwise</li>
<li>Details: A registry would allow for named connections
<ul>
<li>Use case: read db and write db</li>
<li>Use case: marking a single connection as the default</li>
</ul>
</li>
</ul>
</li>
<li>Pluggable architecture
<ul>
<li>Motivation: there are plenty of feature requests that don't have a prope foundation for them to be implemented. Also, too many features that would currently require a BC break. Having a plugin system would reduce the likelyhood of feature requests that would require a BC break.</li>
<li>Details:
<ul>
<li>Ability to plug into Driver at various points
<ul>
<li>connection time</li>
<li>pre & post execute time</li>
<li>pre & post query time</li>
</ul>
</li>
<li>Ability to plug into Query creation
<ul>
<li>plug into any SelectPart creation (see query abstraction for parts)</li>
<li>plug into pre & post assembly time</li>
</ul>
</li>
<li>For a common "plugin" interface, this shall make use of Zend\SignalSlot</li>
</ul>
</li>
</ul>
</li>
<li>Distinct abstractions
<ul>
<li>Motivation: Currently, the Adapter classes contain both the connection abstraction (PHP Extension) function calls as well as some of the Vendor specific query abstraction. This makes the adapters too rigid and too large to improve over time.</li>
<li>Details:
<ul>
<li>Driver abstraction
<ul>
<li>Ability to specify if direct query or parameritized query should be default</li>
<li>PHP Extension specific: pdo_*, mysqli, oci, sqlsrv, etc</li>
</ul>
</li>
<li>Query Abstraction
<ul>
<li>Insert, Update, Delete</li>
<li>Select: (column, from, where, group, having, order, limit, generic)</li>
<li>Vendor Specific Dialect: Mysql, Oracle, MSSQL, etc</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
<li>Addition of DDL query abstraction
<ul>
<li>Motivation: DDL queries have been requested time and time again. In addition, our unit testing suite requires the creation of tables, columns and other schema specfic assets to test against.</li>
<li>Details:
<ul>
<li>(This component will be used to build proper environment for testing)
<ul>
<li>Create, Alter, Drop, etc</li>
</ul>
</li>
<li>This will be part of the Query abstraction layer</li>
</ul>
</li>
</ul>
</li>
<li>Addition of a Metadata sub-component
<ul>
<li>Motivation: Currently, there is no formal way to encompass metadata in ZF. The adapter will provide describeTable() and Zend_Db_Table also does its own introspection over the schema. These types of lookups should be localized into a component that is capable of being both serialized and cached.</li>
<li>Details:
<ul>
<li>keep track of both introspected, and user provided schema information</li>
<li>these are cacheable objects</li>
<li>track relationships (FK constrains, Unique constraints)</li>
<li>track context of relationships (M2M, O2M, etc)</li>
</ul>
</li>
</ul>
</li>
<li>Separate Table & Row Gateway into sub-components of their own
<ul>
<li>Motivation: Currently, both the adapter and Zend_Db_Table have an interface for insert(), update(), and delete(). Since these are Table Gateway specfic concerns, that is the only place this type of interface should exist. Furthermore, Zend_Db_Table can be seen as a heavy component since, by default, it creates an object (Row Object) out of each row returned in the result set. For some use cases (like data warehousing), it would be beneficial to be able to use Zend_Db_Table object that returns array's of information for performances's sake.</li>
<li>Details:
<ul>
<li>Table Gateway
<ul>
<li>Optional coupling with Row Gateway</li>
<li>Give table gateway the default option to return array of data</li>
<li>Will take advantage of the metadata component</li>
<li>Will also have a plugin system: pre & post insert, update, delete time</li>
</ul>
</li>
<li>Row Gateway
<ul>
<li>Similar in nature to the current implementation</li>
<li>Will take advantage of the metadata component</li>
<li>Will also have a plugin system: pre & post insert, update, delete time</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
<li>Better testability in the Unit Tests:
<ul>
<li>Motivation: Currently, the existing unit tests are slow, and hard to work with.</li>
<li>Details:
<ul>
<li>Better separation of concerns
<ul>
<li>With a strong abstraction of driver and query, there is no need for dependent components to test each flavor of DB</li>
<li>Tests should utilize a standard schema for each test, unless something specific is needed which will be created and destroyed at the end of the unit test.</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
<li>Base Plugins:
<ul>
<li>Type Converter:
<ul>
<li>Ability of a plugin to use metadata about a database, and user preferences to cast values from the database to desired primitive values and object types</li>
</ul>
</li>
</ul>
</li>
</ul>

Labels:
None
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. Jun 23, 2010

    <p>Will Zend\Db support insertion of multiple rows? </p>

    <p>I can see your original comment on <a class="external-link" href="http://zendframework.com/issues/browse/ZF-2322">http://zendframework.com/issues/browse/ZF-2322</a>, Ralph, which suggests that the work be done in userland. In my opinion it would be cleaner and more concise for Zend\Db::insert to accept multiple rows and if necessary do the loop internally to insert rows one by one.</p>

    <p>If your application runs on different databases, you would benefit from the optimised code in those that support the feature rather than resorting to the lowest common denominator.</p>

    <p>With regard to the rest of the proposal I'm excited about the introduction of plugins and automatic casting of values to objects. These features will be invaluable.</p>

    1. Jun 23, 2010

      <p>Honestly, I'll have to do more research on multi-row support. I am not a fan of picking out highly specialized features of a specific vendors's database platform and making them part of an Abstraction layer. The goal of the abstraction layer is really to support those features that are between the lowest common denominator and those that are "highly specialized".</p>

      <p>If multi-row insert is something you see in more than just MySQL, then I'd say there is a good argument for including that kind of feature into Zend\DB. But if MySQL is the only one who has specialized SQL to handle it, it doesn't make alot of sense.</p>

      <p>You can ALWAYS pull out the resource/extension and use the native PHP methods to do this work, particularly if your job is data warehousing and very much tied to a specific flavor of rdbms platform.</p>

      <p>Once a prototype is built, lets revisit this.</p>

      1. Jun 24, 2010

        <p>Unfortunately some vendors have their own syntax but a quick google suggests that MySQL, Oracle and Postgres all (in recent versions) support the feature properly and that it can be emulated in MSSQL with UNION SELECT statements to produce a highly performant multi row insert.</p>

        <p>I accept your point that this can be revisited later but hopefully this will be a useful reminder. </p>

  2. Jun 23, 2010

    <p>Would be great if you can add support for relationships via $_GET, $_SET magic methods, Let me explain it with code:</p>

    <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
    //using $_GET
    $comments = $user->comments;

    //is more readable and cleaner than:

    $comments = $user->findDependentRowset('Comments');

    //using $_SET
    $child->parent = $parent;

    //instead of:

    $child->parent_id = $parent->id;
    ]]></ac:plain-text-body></ac:macro>

    <p>All of this can be done using the very same $_referenceMap variable used today.</p>

    <p>Also it would be cool to camel-case the table's fields:</p>

    <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
    // Field name in database: comment_date
    $date = $comment->comment_date;

    //this does the same
    $date = $comment->commentDate;
    ]]></ac:plain-text-body></ac:macro>

    <p>It's just a matter of replace uppercased letters for _ and the letter in lowercase:</p>

    <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
    $prmField = !isset($this->fields[$prmField]) ? strtolower(preg_replace('/([A-Z])/', '$1', $prmField)) : $prmField;
    ]]></ac:plain-text-body></ac:macro>

    1. Jun 23, 2010

      <p>Ah ok, you are talking about __get() & __set().</p>

      <p>I'd actually argue that these concerns have little to do with Zend\DB. These types of problems are really part of the "modeling" problem. Ideally, I'd like to see a set of tools created in a Zend\Model namespace. Something like a generic Container, perhaps a "Base model" for those that like to extend the models of of some base container. Naming of properties is something I'd argue should be solved in there.</p>

      <p>Some elements of Zend\Model could extend or have requirements on Zend\DB subcomponents to do their work. But to have modeling concerns pushed into Zend\DB, or even coupled that was is the wrong direction of dependencies & coupling IMO.</p>

      1. Jun 23, 2010

        <p>Am I that old? sorry, yes, I meant __get() and __set(). Well if there is going to be a Zend\Model based on Zend\Db this might be there instead of here. Btw, is it going to be Zend\DB or Zend\Db ???</p>

    2. Jun 23, 2010

      <p>comment_date => commentDate is possible with custom inflection:
      <a class="external-link" href="http://framework.zend.com/manual/en/zend.db.table.row.html#zend.db.table.row.extending.inflection">http://framework.zend.com/manual/en/zend.db.table.row.html#zend.db.table.row.extending.inflection</a></p>

  3. Jun 23, 2010

    <p>Talking about read db and write db, I wrote a db balancer here a couple of months ago for our comment system, we called the databases, master and slave connections, master for read/write, slave just from read, also for each connection we added a weight parameter and have an internal registry of how many connection each database has in order to have a proper balance between databases, this registry uses memcache because our comment application runs in several servers. Well, some just ideas for multiple db support</p>

  4. Jun 24, 2010

    <p>My thoughts after looking through the thousands of lines of Zend projects we have in production...</p>

    <p>1. It would be nice to have some kind of "if duplicate, then update" functionality across all db platforms, seem to write those lines of code so many times.<br />
    2. A second vote for the multiple rows at once idea. I would extend that to an adapter (or statement? one statement, many rows) level construct that would accept an rowset array to due bulk inserts, almost all platforms have a way of doing this significantly faster than one row at a time.</p>

    <p>Those are my top two that seem to wind up in my Zend_Table_* subclasses.</p>

    <p>--d</p>

    1. Jun 25, 2010

      <p>I wonder if point 1 can be solved using a plugin? Then ZF can ship a plugin for this use case without bloating the core adapter.</p>

      1. Jun 26, 2010

        <p>Just wrote this again today, this is pretty minimal, so not sure if there is a lot of bloat, plugins have load time:</p>
        <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
        $t = new Zend_Db_Table($table);
        ($t->find($primaryKey)>count())?$t>update($data, $where):$t->insert($data);
        ]]></ac:plain-text-body></ac:macro>
        <p>Two DB transactions, where a "ON DUPLICATE UPDATE" or equivalent would do it in one. Keeping DB independence of the code set and improving performance. Maybe put this in Zend_Db_Adapter_Abstract, and override in Zend_Db_Adapter classes?</p>

        <p>I think the core issue here is that using Zend_Db is easy, one can hire/train folks to do it. What increases cost is having to step outside of Zend Framework and write raw SQL (more expensive, platform dependent, must maintain), to meet requirements of bulk data loads and updates that we see often. Some of our applications deal with tens of thousands of records (ie:encrypted messages/legal evidence documents) at a time, so the difference is a few minutes vs. an hour. We use ZF as it saves a ton of time and cost, the more ZF can do, the more we all win.</p>

        <p>Maybe someone with knowledge on statements and PDO can find an elegant way to do all of this.</p>

  5. Jun 27, 2010

    <p>1. Support for default dbs for different tasks in would be great.</p>

    <p>Situation:</p>

    <ul>
    <li>one db for read purposes</li>
    <li>one db for write purposes</li>
    </ul>

    <p>In direct Zend Db use this is not a problem since you can chose the adapter. In zend db table this uses one adapter.</p>

    <p>I suggest modifying the default db adapter on zend_Db_Table to:</p>

    <ul>
    <li>default db adapter (both read/write)</li>
    <li>default write db adapter</li>
    <li>default read db adapter</li>
    </ul>

    <p>Edit: I assume this is the route you wanted to go based on the registry reqs, but wanted to put it out there explicitly just to be sure <ac:emoticon ac:name="wink" /></p>

    <p>2. Support for database pools would also be nice.</p>

    <p>Example:</p>

    <ul>
    <li>4 dbs configured that can be read from</li>
    </ul>

    <p>Although... When thinking about it, it's probably better to take care of this on dns (one hostname, multiple ips in round robin) or load balancer level.</p>

    1. Jun 28, 2010

      <p>The structure of DB nodes could be more complicated like this:
      <a class="external-link" href="http://dev.mysql.com/doc/mysql-cluster-excerpt/5.1/en/mysql-cluster-replication-multi-master.html">http://dev.mysql.com/doc/mysql-cluster-excerpt/5.1/en/mysql-cluster-replication-multi-master.html</a><br />
      -> have more than one nodes with read/write access<br />
      -> have more than one nodes with read-only access</p>

      <p>It could be really useful to have a ConnectionManager to solve this.<br />
      Additionally if a query fails with "connection lost" another connection (of a defined pool) could be opened automatically as fault back.</p>

      <blockquote><p>Although... When thinking about it, it's probably better to take care of this on dns (one hostname, multiple ips in round robin) or load balancer level.</p></blockquote>
      <p>With round robin you have a problem if one of your db-node is down because the db-clients / proxies will cache dns routes.</p>

  6. Nov 16, 2011

    <p>Why don't add this useful method ? <a class="external-link" href="http://devzone.zend.com/1675/class-for-managing-nested-set-data/">http://devzone.zend.com/1675/class-for-managing-nested-set-data/</a><br />
    for hierarchy and tree data. </p>