compared with
Current by Ralph Schindler
on Oct 29, 2006 23:43.

(show comment)
Key
This line was removed.
This word was removed. This word was added.
This line was added.

Changes (57)

View Page History
{info}Creating <ac:macro ac:name="info"><ac:rich-text-body><p>Creating a programmatic interface that offers the features below, without becoming more difficult than using raw SQL with PHP's PDO object, will not be a simple task. Contact the ZF DB Project Team, if you would like to help! We are keenly interested in everyone's feedback. Many developers today like to use an object-oriented interface to programmatically construct their SQL queries. Thus, we continue to try and satisfy this large group.</p></ac:rich-text-body></ac:macro>
{info}

PHP identifiers: {{[a-zA-Z_\x7f-\xff][a-zA-Z0-9_\x7f-\xff]*}}
<p>PHP identifiers: <code><ac:link><ri:page ri:content-title="a-zA-Z_\x7f-\xff" /></ac:link><ac:link><ri:page ri:content-title="a-zA-Z0-9_\x7f-\xff" /></ac:link>*</code> </p>

h1. Quoting Issues
<h1>Quoting Issues</h1>

Currently, the Zend Framework lacks consistent mechanisms facilitating quoting of table names, column names, and building queries with "expressions" such as "DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= :startOfThisMonth". We intend to address these issues.
<p>Currently, the Zend Framework lacks consistent mechanisms facilitating quoting of table names, column names, and building queries with &quot;expressions&quot; such as &quot;DATE_SUB(CURDATE(),INTERVAL 30 DAY) &lt;= :startOfThisMonth&quot;. We intend to address these issues.</p>

<p>To see what forms of quoting are supported, please see:
http://framework.zend.com/manual/en/zend.db.html#zend.db.adapter.quoting
<a class="external-link" href="http://framework.zend.com/manual/en/zend.db.html#zend.db.adapter.quoting">http://framework.zend.com/manual/en/zend.db.html#zend.db.adapter.quoting</a></p>

<p>The framework simulates prepared queries in some places, and the issue tracker lists several problems with the current implementation. Additionally, when using real prepared queries (not simulated), certain database-dependent restrictions apply. For example, with MySQL, see: http://dev.mysql.com/doc/refman/5.0/en/legal-names.html <a class="external-link" href="http://dev.mysql.com/doc/refman/5.0/en/legal-names.html">http://dev.mysql.com/doc/refman/5.0/en/legal-names.html</a></p>

"The <p>&quot;The markers are legal only in certain places in SQL statements. For example, they are allowed in the VALUES() list of an INSERT statement (to specify column values for a row), or in a comparison with a column in a WHERE clause to specify a comparison value. However, they are not allowed for identifiers (such as table or column names), or to specify both operands of a binary operator such as the = equal sign." sign.&quot; -- http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-prepare.html &ndash; <a class="external-link" href="http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-prepare.html">http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-prepare.html</a></p>

<p>The Framework should provide a unified, consistent, logical approach that helps web application developers column names, table names, and "protect" &quot;protect&quot; parameter values without abandoning the native support for prepared queries in the chosen DB. Wherever possible, parameter values should be protected using prepared queries supported by PHP's PDO, since PDO uses the native API for each database, allowing explicit, unambiguous transmission of individual parameter values, thus obviating the need for quoting them. API changes are required to satisfy these goals. All logical pieces of data used to construct the query must be stored along with their type, such that the SQL query can be constructed correctly whenever requested by the developer, and correctly submitted via the PHP DB driver's prepared query API (if available).</p>

<p>Prepared queries are more efficient and secure. The programmatic Zend_Db* APIs for constructing SQL should rely on native, prepared queries, when possible. Thus, whereever and whenever possible, PDO prepared queries are preferred over PDO quoting, and PDO quoting is preferred over relying on PHP code to perform the quoting. Note that http://www.php.net/manual/en/function.pdo-quote.php <a class="external-link" href="http://www.php.net/manual/en/function.pdo-quote.php">http://www.php.net/manual/en/function.pdo-quote.php</a> does not quote table or column names differently than anything else. Thus, PDO::quote() should not be used to quote table and column names for any DB using a different column or table name quotation syntax that differs from PDO::quote().</p>

h3. Notes
<h3>Notes</h3>

<ol>
# Note <li>Note that with prepared statements in PDO, "The &quot;The parameters to prepared statements don't need to be quoted; the driver handles it for you." you.&quot; However, if we attempt to parse and emulate prepared queries (current practice), then everything must be quoted correctly, including all the nuances each DB has for table names, column names, different types of data, and SQL expressions containing functions.</li>
</ol>

h2. Solutions

If developers want to use programmatic means to construct SQL queries, then the fragments of SQL supplied to the programmtic mechnanisms must be tagged more clearly than currently in the Zend_Db* classes, or these mechanisms must become smarter and able to actually parse SQL correctly for each database supported. There are numerous JIRA issues rooted in the failure to do either of these two options sufficiently.
<h2>Solutions</h2>

h3. Delayed Aggregation
<p>If developers want to use programmatic means to construct SQL queries, then the fragments of SQL supplied to the programmtic mechnanisms must be tagged more clearly than currently in the Zend_Db* classes, or these mechanisms must become smarter and able to actually parse SQL correctly for each database supported. There are numerous JIRA issues rooted in the failure to do either of these two options sufficiently.</p>

If most Zend_Db* classes were changed from accepting raw SQL code (e.g. for WHERE clauses), to use instances of Zend_Db_Sql, then Zend_Db_Sql could provide mechanisms to create SQL fragments without loss of structural/semantic information by requiring the developer to explicitly tag all fragments (column names, table names, expressions, data, etc.). There are multiple ways the types of SQL fragments might be identified and combined into instances of Zend_Db_Sql. As a result, existing classes might be rewritten to expect instances of this new class:
<h3>Delayed Aggregation</h3>

{code}public function order(Zend_Db_Sql $sql, $order){code}
<p>If most Zend_Db* classes were changed from accepting raw SQL code (e.g. for WHERE clauses), to use instances of Zend_Db_Sql, then Zend_Db_Sql could provide mechanisms to create SQL fragments without loss of structural/semantic information by requiring the developer to explicitly tag all fragments (column names, table names, expressions, data, etc.). There are multiple ways the types of SQL fragments might be identified and combined into instances of Zend_Db_Sql. As a result, existing classes might be rewritten to expect instances of this new class:</p>

Basically Zend_Db_Sql would facilitate preserving as much structural and semantic information about the parts of a SQL fragment as possible for as long as possible. This approach allows for greater control and flexibility manipulating the parts later into the processes of creating genuine (not emulating) prepared queries for supporting Zend_Db_Adapter_*'s, and better control over emulation for other adapters.
<ac:macro ac:name="code"><ac:plain-text-body><![CDATA[public function order(Zend_Db_Sql $sql, $order)]]></ac:plain-text-body></ac:macro>

<p>Basically Zend_Db_Sql would facilitate preserving as much structural and semantic information about the parts of a SQL fragment as possible for as long as possible. This approach allows for greater control and flexibility manipulating the parts later into the processes of creating genuine (not emulating) prepared queries for supporting Zend_Db_Adapter_*'s, and better control over emulation for other adapters.</p>

h1. Mapping Identifiers between DB and PHP

There are numerous related issues in our JIRA issue tracker related to problems associated with referring to DB database, table, and column names from within PHP. Currently, PDO adapters force a lowercase, but other components of ZF force use of camelCase. Both of these practices will be removed from the ZF, and replaced by a flexible mapping. The default behavior in all cases should be "natural case", where no transformation occurs and the PHP programmatic name defaults to the same as the name used in the DB schema.
<h1>Mapping Identifiers between DB and PHP</h1>

The current approach (predates my arrival at Zend) to "quoting" data supplied via programmatic interfaces to the Zend_Db* related classes suffers from several flaws and has spawned numerous issues in our issue tracker. Essentially the Zend_Db classes attempt to emulate prepared queries, including parsing parameters and quoting data. Not surprisingly, semantic information is lost and several errors are introduced as Zend_Db classes mangle the data into strings containing SQL code.
<p>There are numerous related issues in our JIRA issue tracker related to problems associated with referring to DB database, table, and column names from within PHP. Currently, PDO adapters force a lowercase, but other components of ZF force use of camelCase. Both of these practices will be removed from the ZF, and replaced by a flexible mapping. The default behavior in all cases should be &quot;natural case&quot;, where no transformation occurs and the PHP programmatic name defaults to the same as the name used in the DB schema.</p>

The solution for ZF-1 will provide a large portion of the solution to this problem. In order to properly submit ZF "where" clauses and other data collected programmatically through an OO interface to a DB driver in a format suitable for prepared queries (if supported by that driver), all parts of the SQL query need to be kept separated, instead of combined into a string. If the parts collected via the programmatic interface are stored individually, the parts can then be quoted correctly (e.g. quoting for column names is not always the same for other identifiers, depending on the DB). Additional API methods could then be added to get and set the parts of the query.
<p>The current approach (predates my arrival at Zend) to &quot;quoting&quot; data supplied via programmatic interfaces to the Zend_Db* related classes suffers from several flaws and has spawned numerous issues in our issue tracker. Essentially the Zend_Db classes attempt to emulate prepared queries, including parsing parameters and quoting data. Not surprisingly, semantic information is lost and several errors are introduced as Zend_Db classes mangle the data into strings containing SQL code.</p>

Wherever possible, we will eliminate all the prepared-query-emulation logic from the adapters and Db classes in favor of using the native support for prepared queries provided by the PHP DB drivers. ZF developers must clearly break up their data and tag it by type (integers, floats, strings, functions, column names, table names, etc.), such that real, native prepared queries can be used by the PHP DB drivers underlying the ZF DB adapters.
<p>The solution for ZF-1 will provide a large portion of the solution to this problem. In order to properly submit ZF &quot;where&quot; clauses and other data collected programmatically through an OO interface to a DB driver in a format suitable for prepared queries (if supported by that driver), all parts of the SQL query need to be kept separated, instead of combined into a string. If the parts collected via the programmatic interface are stored individually, the parts can then be quoted correctly (e.g. quoting for column names is not always the same for other identifiers, depending on the DB). Additional API methods could then be added to get and set the parts of the query.</p>

The default transformation should be natural case (i.e. no transformation of case sensitivity or adding/removing underscores, etc.).
<p>Wherever possible, we will eliminate all the prepared-query-emulation logic from the adapters and Db classes in favor of using the native support for prepared queries provided by the PHP DB drivers. ZF developers must clearly break up their data and tag it by type (integers, floats, strings, functions, column names, table names, etc.), such that real, native prepared queries can be used by the PHP DB drivers underlying the ZF DB adapters.</p>

<p>The default transformation should be natural case (i.e. no transformation of case sensitivity or adding/removing underscores, etc.).</p>

http://framework.zend.com/issues/browse/ZF-50

h3. Constraints
<p><a class="external-link" href="http://framework.zend.com/issues/browse/ZF-50">http://framework.zend.com/issues/browse/ZF-50</a></p>

# The ZF is targeting those who want something *extremely* simple and easy to use. Thus, a degree of sophistication, complexity, and robustness can be sacrificed, if the result significantly reduces the learning curve and number of lines of code required to use the ZF. So the ZF version of a "pluggable architecture" might be vastly simpler than typical, but it results and benefits from a design by choice.
# In general, Zend_Db* should support existing DB schemas without forcing them to alter it to work with the ZF.
<h3>Constraints</h3>

h3. Requirements
<ol>
<li>The ZF is targeting those who want something <strong>extremely</strong> simple and easy to use. Thus, a degree of sophistication, complexity, and robustness can be sacrificed, if the result significantly reduces the learning curve and number of lines of code required to use the ZF. So the ZF version of a &quot;pluggable architecture&quot; might be vastly simpler than typical, but it results and benefits from a design by choice.</li>
<li>In general, Zend_Db* should support existing DB schemas without forcing them to alter it to work with the ZF.</li>
</ol>

# For the purposes of this wiki page, _PHP programmatic names_ are restricted to valid PHP identifiers: {{[a-zA-Z_\x7f-\xff][a-zA-Z0-9_\x7f-\xff]*}} If we forgoe this constraint, then {{$row->columnName}} will not work, if the column name used by the DB is not a legal PHP identifier.
# Each adapter will support proper quoting of identifiers, including column names and table names (some DBs do not use the same quoting syntax for all identifiers).
# Each ZF DB adapter will support pluggable filters that can transform names (identifiers) used in DB schemas to programmatic names (identifiers) used in both the ZF and userland code written for the ZF.
# The pluggable system will support using different transformation filters for each of the following tasks:
## database instance names to/from PHP compatible programmatic names (a single RDBMS system/cluster might contain several database instances accessible via a single socket connection, such as with {{mysqli_select_db()}}.
## table names to/from PHP compatible programmatic names
## column names to/from PHP compatible programmatic names
# Natural case will be the default (no transformation, exact case) for all ZF DB adapters.
# A developer will be able to choose their preferred way of programmatically referring to table and column names. We are currently working on the details of how the DB adapters might work together with plugins to transform table and column names.
# The developer will be able to override the default names of primary keys and such, as used by Zend_Db_Table.
# Zend_Db_Table will allow the developer to We've had problems with forcing case conversions with other DBs, and would like to find ways to support whatever case/spelling/punctuation users want to use. Perhaps there should be Zend_Db_Table setting in the Zend registry to specify a default primary key?
# Since some Zend DB adapters will allow switching between database instances using the same DB adapter instance (e.g. {{mysqli_select_db()}}), the DB adapters should allow dynamic registration of tranformation filter plugins.

h3. Notes
<h3>Requirements</h3>

<ol>
<li>For the purposes of this wiki page, <em>PHP programmatic names</em> are restricted to valid PHP identifiers: <code><ac:link><ri:page ri:content-title="a-zA-Z_\x7f-\xff" /></ac:link><ac:link><ri:page ri:content-title="a-zA-Z0-9_\x7f-\xff" /></ac:link>*</code> If we forgoe this constraint, then <code>$row-&gt;columnName</code> will not work, if the column name used by the DB is not a legal PHP identifier.</li>
<li>Each adapter will support proper quoting of identifiers, including column names and table names (some DBs do not use the same quoting syntax for all identifiers).</li>
<li>Each ZF DB adapter will support pluggable filters that can transform names (identifiers) used in DB schemas to programmatic names (identifiers) used in both the ZF and userland code written for the ZF.</li>
<li>The pluggable system will support using different transformation filters for each of the following tasks:
<ol>
<li>database instance names to/from PHP compatible programmatic names (a single RDBMS system/cluster might contain several database instances accessible via a single socket connection, such as with <code>mysqli_select_db()</code>.</li>
<li>table names to/from PHP compatible programmatic names</li>
<li>column names to/from PHP compatible programmatic names</li>
</ol>
</li>
<li>Natural case will be the default (no transformation, exact case) for all ZF DB adapters.</li>
<li>A developer will be able to choose their preferred way of programmatically referring to table and column names. We are currently working on the details of how the DB adapters might work together with plugins to transform table and column names.</li>
<li>The developer will be able to override the default names of primary keys and such, as used by Zend_Db_Table.</li>
<li>Zend_Db_Table will allow the developer to We've had problems with forcing case conversions with other DBs, and would like to find ways to support whatever case/spelling/punctuation users want to use. Perhaps there should be Zend_Db_Table setting in the Zend registry to specify a default primary key?</li>
<li>Since some Zend DB adapters will allow switching between database instances using the same DB adapter instance (e.g. <code>mysqli_select_db()</code>), the DB adapters should allow dynamic registration of tranformation filter plugins.</li>
</ol>


<h3>Notes</h3>
<ol>
# Some <li>Some developers have UTF8 column names for existing databases (e.g. MySQL), and are trying to use these databases with the ZF.</li>
# Simultaneously, we're also working on trying to find a "preferred" way of dealing with "plugins" in the ZF: http://framework.zend.com/wiki/x/WRk
# Always good to have another head to think about problems and solutions :)
<li>Simultaneously, we're also working on trying to find a &quot;preferred&quot; way of dealing with &quot;plugins&quot; in the ZF: <a class="external-link" href="http://framework.zend.com/wiki/x/WRk">http://framework.zend.com/wiki/x/WRk</a></li>
<li>Always good to have another head to think about problems and solutions <ac:emoticon ac:name="smile" /></li>
</ol>