ZF-745: Request solution for result set metadata

Description

Currently the Zend_Db_Adapter_Abstract interface supports fetching data values of a result set, but the metadata is not available. So there no convenient way to determine the original SQL datatype of a column; all column values are mapped to PHP scalars.

This is a feature request to add a method to Zend_Db_Adapter_Abstract, and implement in each respective subclass, to fetch a special result-set consisting of metadata of the result set.

Comments

Notes:

PDO has a method getColumnMeta() but it is noted as "experimental" in the PDO docs. Not all PDO drivers are guaranteed to support it. I haven't checked the status of support for this method in the PDO drivers we use in ZF.

The PDO getColumnMeta() method returns an associative array for a single column (specified by the 0-indexed ordinal number of the column), with the following keys: - native_type - driver:decl_type - flags - name - len - precision - pdo_type

MySQLi stmts have a result_metadata() method, so this could support result set metadata. The result_metadata() method returns a result-set object, which is an iterable collection of fields. Each field is an object. The documentation does not specify the members of this object.

The OCI API for Oracle has individual methods for metadata. So this could support result set metadata. The methods are: - oci_field_is_null() - oci_field_name() - oci_field_precision() - oci_field_scale() - oci_field_size() - oci_field_type_raw() - oci_field_type()

I would propose a new alternative fetch() method for Zend_Db_Adapter, to retrieve data combined with metadata in a canonical form. The adapter is responsible for assembling the metadata into this form.

Then a Zend_Db_Table_Row would also have a new method getMetadata() which returns an associative array mapping column names to a further associative array with attributes for the respective column. Zend_Db_Table_Row would always fetch the metadata for a query from the adapter. This might cause a slight performance cost depending on the adapter's implementation, but it is likely to be insignificant compared to the cost of the query itself.

Reset components to just Zend_Db.

I wrote some considerations about this and other things on my blog: http://tinyurl.com/2f38sq http://tinyurl.com/yr4lk9

metadata retrieving should be untied from the zend_db_table and should work using the getcolumnmeta function, that should be released as stable (not experimental like now).

a feature that needs to be added is the table name of the field, absolutely needed when writing general code

Please categorize/fix as needed.

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

Reassigning as Ralph is the maintainer of Zend_Db

How does Zend_Db_Adapter_Abstract::describeTable() not cover this use case?

-ralph

What are the data types, string lengths, nullability, etc. of columns returned by the following query:


SELECT CONCAT(a.first_name, ' ', a.last_name) AS full_name, 
  GROUP_CONCAT(b.bug_id) AS bug_id_list,
  COUNT(*) AS num_bugs,
  SUM(b.status = 'OPEN')/COUNT(*) AS percent_of_open_bugs
FROM bugs b JOIN accounts a ON a.account_id = b.reported_by
GROUP BY a.account_id;

Result set metadata is about query results, not tables on disk. Queries may have expressions of different data types than any that occur in the underlying tables. If you capture that query and create a temp table based on it, you can see what I mean:


CREATE TEMPORARY TABLE foo   SELECT ...query above...
DESCRIBE foo;
+----------------------+---------------+------+-----+---------+-------+
| Field                | Type          | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+-------+
| full_name            | varchar(41)   | YES  |     | NULL    |       | 
| bug_id_list          | longblob      | YES  |     | NULL    |       | 
| num_bugs             | bigint(21)    | NO   |     | 0       |       | 
| percent_of_open_bugs | decimal(27,4) | YES  |     | NULL    |       | 
+----------------------+---------------+------+-----+---------+-------+

I logged this feature request a long time ago because the {{PDOStatement}} interface had a new function {{getColumnMeta()}} that {{Zend_Db_Statement}} didn't implement.

However, that PDO statement is experimental and most (all?) PDO drivers don't implement it. Surfacing result set metadata through Zend_Db_Statement is therefore impossible, since the underlying drivers don't supply the information. So I would recommend postponing this Zend_Db feature request indefinitely, or simply close it as "won't fix."

Marking as wont fix.

Since the votes are low, and the underlying delivery mechanism is marked as experimental, I see no other option. Lets re-evaluate when the new PDO comes around. ;)