Caching of describeTable

[Shahar Evron]

Hi,

Maybe I just need to RTFM but this one is a quickie: Is there a caching
solution for Zend_Db_Adapter_xxx::describeTable() ?

That one gets called quite frequently, and is very unlikely to change in
production environemtns. Caching it somehow would be a smart idea.

If nobody has suggestions, I'll do some hacking later and post an
article about it.

TIA,

Shahar.

[Simon Mundy]

Hi Shahar

It's a hairy one for sure! I've seen a lot of XML/config solutions  
whereby you 'synch' your class definitions with your database (or  
similar) to negate the need for frequent calls to determine the table/
row structure. The current solution in Zend_Db_Table is definitely  
wasteful and I think it's where a lot of work is needed.

I had toyed around an idea whereby you could manually describe your  
table structure within the class by defining certain properties, thus  
overriding the need for the describe() method. If it didn't exist  
when the class was instantiated then the default describe methods  
could be used and, as you say, it would be great if you could pass an  
instance of a cache to a DB adapter so that you could retrieve the  
definition locally.

E.g.

MyClass extends Zend_Db_Table
{
     protected $_name = 'mytable';
     protected $_row = 'MyClass_Row';
     protected $_schema = array('id' => array('type' => 'integer',  
'null' => false),
                                'name' => array('type' => 'text',  
'null' => true),
                                'description' => array('type' =>  
'text'));
     protected $_primary = 'id'; // or could be array('name',  
'description') for multi-key tables
}

Obviously a quick'n'dirty idea, but it demonstrates how a developer  
could easily maintain their tables and dispense with more weighty  
solutions. It could also be possible to point a config file for table  
definitions if desired, or to simply pass a $config instance.

The reasoning behind this kind of 'manual' override is that despite  
some labour-intensive work by the developer in the first instance  
(manually tracking changes to table definitions and ensuring the  
class is up-to-date) it removes the need for all sorts of fancy  
(=intensive) ways of achieving the same end. If manual definitions  
are in place then it also allows for the developer to potentially  
create foreign key relationships, triggers, etc in pure PHP rather  
than necessarily rely on the DB engine (like MySQL 4.x MyISAM tables).

But if you were more keen to use a 'live' schema then you could leave  
the $_schema property undefined and you could retrieve that  
information in a similar fashion to the present solution (although  
utilising the caching mechanism).

The other instance I saw potential improvements was to allow each  
Zend_Db_Table class access to a registry of Db's so that each  
individual table instance didn't need to store an internal copy of  
the Db connection, bypassing the problems associated with serialised  
instances. In many (most?) cases the class really only accesses the  
database once to retrieve a row or rowset and then may only  
infrequently save back to the database if at all, so it seems  
unnecessary to store this supplementary information. At most you'd  
want to refer to a table structure from the row/rowset but that'd be it.

Perhaps a Zend_Db_Scheme or Zend_Db_Registry helper class may be of  
use here?

Anyhoo, look forward to your findings! Could you post them on the  
proposal as well as discussing ideas here so that we can track these  
ideas and comments?

Cheers

> Hi,
>
> Maybe I just need to RTFM but this one is a quickie: Is there a  
> caching
> solution for Zend_Db_Adapter_xxx::describeTable() ?
>
> That one gets called quite frequently, and is very unlikely to  
> change in
> production environemtns. Caching it somehow would be a smart idea.
>
> If nobody has suggestions, I'll do some hacking later and post an
> article about it.
>
> TIA,
>
> Shahar.

--

Simon Mundy | Director | PEPTOLAB

""" " "" """""" "" "" """"""" " "" """"" " """"" "  """""" "" "
202/258 Flinders Lane | Melbourne | Victoria | Australia | 3000
Voice [phone] | Mobile [phone] | Fax [phone]  
4124
http://www.peptolab.com

[Shahar Evron]

As a first and simple step, I think that allowing the user to specify
the table strcuture is a cheap and very nice to have option.

Let's face it - I'd rather specify my table structure myself (which will
almost never change in production) than having the class study it again
and again - even if it's cached (That is only once every n requests).

As a further solution, if I have time, I will try to find some simple
caching solution for it.

Shahar.

Simon Mundy wrote:

> Hi Shahar
>
> It's a hairy one for sure! I've seen a lot of XML/config solutions
> whereby you 'synch' your class definitions with your database (or
> similar) to negate the need for frequent calls to determine the
> table/row structure. The current solution in Zend_Db_Table is definitely
> wasteful and I think it's where a lot of work is needed.
>
> I had toyed around an idea whereby you could manually describe your
> table structure within the class by defining certain properties, thus
> overriding the need for the describe() method. If it didn't exist when
> the class was instantiated then the default describe methods could be
> used and, as you say, it would be great if you could pass an instance of
> a cache to a DB adapter so that you could retrieve the definition locally.
>
> E.g.
>
> MyClass extends Zend_Db_Table
> {
>     protected $_name = 'mytable';
>     protected $_row = 'MyClass_Row';
>     protected $_schema = array('id' => array('type' => 'integer', 'null'
> => false),
>                                'name' => array('type' => 'text', 'null'
> => true),
>                                'description' => array('type' => 'text'));
>     protected $_primary = 'id'; // or could be array('name',
> 'description') for multi-key tables
> }
>
> Obviously a quick'n'dirty idea, but it demonstrates how a developer
> could easily maintain their tables and dispense with more weighty
> solutions. It could also be possible to point a config file for table
> definitions if desired, or to simply pass a $config instance.
>
> The reasoning behind this kind of 'manual' override is that despite some
> labour-intensive work by the developer in the first instance (manually
> tracking changes to table definitions and ensuring the class is
> up-to-date) it removes the need for all sorts of fancy (=intensive) ways
> of achieving the same end. If manual definitions are in place then it
> also allows for the developer to potentially create foreign key
> relationships, triggers, etc in pure PHP rather than necessarily rely on
> the DB engine (like MySQL 4.x MyISAM tables).
>
> But if you were more keen to use a 'live' schema then you could leave
> the $_schema property undefined and you could retrieve that information
> in a similar fashion to the present solution (although utilising the
> caching mechanism).
>
> The other instance I saw potential improvements was to allow each
> Zend_Db_Table class access to a registry of Db's so that each individual
> table instance didn't need to store an internal copy of the Db
> connection, bypassing the problems associated with serialised instances.
> In many (most?) cases the class really only accesses the database once
> to retrieve a row or rowset and then may only infrequently save back to
> the database if at all, so it seems unnecessary to store this
> supplementary information. At most you'd want to refer to a table
> structure from the row/rowset but that'd be it.
>
> Perhaps a Zend_Db_Scheme or Zend_Db_Registry helper class may be of use
> here?
>
> Anyhoo, look forward to your findings! Could you post them on the
> proposal as well as discussing ideas here so that we can track these
> ideas and comments?
>
> Cheers
>
>> Hi,
>>
>> Maybe I just need to RTFM but this one is a quickie: Is there a caching
>> solution for Zend_Db_Adapter_xxx::describeTable() ?
>>
>> That one gets called quite frequently, and is very unlikely to change in
>> production environemtns. Caching it somehow would be a smart idea.
>>
>> If nobody has suggestions, I'll do some hacking later and post an
>> article about it.
>>
>> TIA,
>>
>> Shahar.
>
> --
>
> Simon Mundy | Director | PEPTOLAB
>
> """ " "" """""" "" "" """"""" " "" """"" " """"" "  """""" "" "
> 202/258 Flinders Lane | Melbourne | Victoria | Australia | 3000
> Voice [phone] | Mobile [phone] | Fax [phone]
> http://www.peptolab.com
>
>
>

[Darby Felton]

Another solution to the problem is using code generation, whereby
classes are created upon introspection of the database structure. User
classes extend the generated classes so that their code is not lost when
the parent classes are regenerated.

When the data schema changes, the developer simply reruns the code
generator.

This can be a reasonable solution for many situations, particularly for
prototype and rapid application development.

I know that Qcodo takes this approach: http://www.qcodo.com

Best regards,
Darby

Shahar Evron wrote:

> As a first and simple step, I think that allowing the user to specify
> the table strcuture is a cheap and very nice to have option.
>
> Let's face it - I'd rather specify my table structure myself (which will
> almost never change in production) than having the class study it again
> and again - even if it's cached (That is only once every n requests).
>
> As a further solution, if I have time, I will try to find some simple
> caching solution for it.
>
> Shahar.
>
> Simon Mundy wrote:
>> Hi Shahar
>>
>> It's a hairy one for sure! I've seen a lot of XML/config solutions
>> whereby you 'synch' your class definitions with your database (or
>> similar) to negate the need for frequent calls to determine the
>> table/row structure. The current solution in Zend_Db_Table is definitely
>> wasteful and I think it's where a lot of work is needed.
>>
>> I had toyed around an idea whereby you could manually describe your
>> table structure within the class by defining certain properties, thus
>> overriding the need for the describe() method. If it didn't exist when
>> the class was instantiated then the default describe methods could be
>> used and, as you say, it would be great if you could pass an instance of
>> a cache to a DB adapter so that you could retrieve the definition locally.
>>
>> E.g.
>>
>> MyClass extends Zend_Db_Table
>> {
>>     protected $_name = 'mytable';
>>     protected $_row = 'MyClass_Row';
>>     protected $_schema = array('id' => array('type' => 'integer', 'null'
>> => false),
>>                                'name' => array('type' => 'text', 'null'
>> => true),
>>                                'description' => array('type' => 'text'));
>>     protected $_primary = 'id'; // or could be array('name',
>> 'description') for multi-key tables
>> }
>>
>> Obviously a quick'n'dirty idea, but it demonstrates how a developer
>> could easily maintain their tables and dispense with more weighty
>> solutions. It could also be possible to point a config file for table
>> definitions if desired, or to simply pass a $config instance.
>>
>> The reasoning behind this kind of 'manual' override is that despite some
>> labour-intensive work by the developer in the first instance (manually
>> tracking changes to table definitions and ensuring the class is
>> up-to-date) it removes the need for all sorts of fancy (=intensive) ways
>> of achieving the same end. If manual definitions are in place then it
>> also allows for the developer to potentially create foreign key
>> relationships, triggers, etc in pure PHP rather than necessarily rely on
>> the DB engine (like MySQL 4.x MyISAM tables).
>>
>> But if you were more keen to use a 'live' schema then you could leave
>> the $_schema property undefined and you could retrieve that information
>> in a similar fashion to the present solution (although utilising the
>> caching mechanism).
>>
>> The other instance I saw potential improvements was to allow each
>> Zend_Db_Table class access to a registry of Db's so that each individual
>> table instance didn't need to store an internal copy of the Db
>> connection, bypassing the problems associated with serialised instances.
>> In many (most?) cases the class really only accesses the database once
>> to retrieve a row or rowset and then may only infrequently save back to
>> the database if at all, so it seems unnecessary to store this
>> supplementary information. At most you'd want to refer to a table
>> structure from the row/rowset but that'd be it.
>>
>> Perhaps a Zend_Db_Scheme or Zend_Db_Registry helper class may be of use
>> here?
>>
>> Anyhoo, look forward to your findings! Could you post them on the
>> proposal as well as discussing ideas here so that we can track these
>> ideas and comments?
>>
>> Cheers
>>
>>> Hi,
>>>
>>> Maybe I just need to RTFM but this one is a quickie: Is there a caching
>>> solution for Zend_Db_Adapter_xxx::describeTable() ?
>>>
>>> That one gets called quite frequently, and is very unlikely to change in
>>> production environemtns. Caching it somehow would be a smart idea.
>>>
>>> If nobody has suggestions, I'll do some hacking later and post an
>>> article about it.
>>>
>>> TIA,
>>>
>>> Shahar.
>> --
>>
>> Simon Mundy | Director | PEPTOLAB
>>
>> """ " "" """""" "" "" """"""" " "" """"" " """"" "  """""" "" "
>> 202/258 Flinders Lane | Melbourne | Victoria | Australia | 3000
>> Voice [phone] | Mobile [phone] | Fax [phone]
>> http://www.peptolab.com
>>
>>
>>
>
>

[GavinZend]

Also, this general approach is time-proven.  For example, Qcodo is
relatively mature,
and has been used in "heavy-weight" applications.  However, I think some
will find the KISS ORM more suitable for "light-weight" applications.

http://framework.zend.com/wiki/x/bx0

I see the Qcodo style nicely complementing the KISS ORM proposal to
satisfy the 80/20.

Cheers,
Gavin

Darby Felton wrote:

> Another solution to the problem is using code generation, whereby
> classes are created upon introspection of the database structure. User
> classes extend the generated classes so that their code is not lost when
> the parent classes are regenerated.
>
> When the data schema changes, the developer simply reruns the code
> generator.
>
> This can be a reasonable solution for many situations, particularly for
> prototype and rapid application development.
>
> I know that Qcodo takes this approach: http://www.qcodo.com
>
> Best regards,
> Darby
>  

[Andi Gutmans]

I think there are a few options here which we discussed in the past:
a) Use Zend_Cache to cache the info with a TTL.
b) Implement a very lightweight Zend_Db specific cache with TTL.
c) Auto-generate classes (i.e. manual update). Theoretically that and Cache
are similar solutions as Cache can also be manually generated.

Anyway, Bill is working right now to come with a suggestion for what the
scope of Zend_Db is and if we need an additional layer, what that would be.
This issue will definitely be taken into account.

Thanks.

Andi

> -----Original Message-----
> From: Shahar Evron [mailto:[hidden email]]
> Sent: Monday, January 01, 2007 6:15 AM
> To: [hidden email]
> Subject: [fw-db] Caching of describeTable
>
> Hi,
>
> Maybe I just need to RTFM but this one is a quickie: Is there
> a caching solution for Zend_Db_Adapter_xxx::describeTable() ?
>
> That one gets called quite frequently, and is very unlikely
> to change in production environemtns. Caching it somehow
> would be a smart idea.
>
> If nobody has suggestions, I'll do some hacking later and
> post an article about it.
>
> TIA,
>
> Shahar.
>

[Bill Karwin from Zend]

It's true that requiring a describe() on every page request is costly.  
I've seen reports that performance is poor for describe and other usage
of the INFORMATION_SCHEMA virtual tables, in MySQL.  Not surprising that
the feature was not designed for high performance, under the assumption
that will be queried infrequently.

Caching the metadata automagically is one solution.  TTL is probably of
the least expensive way to get some benefit out of the cache, but allow
for changes to occur without requiring manual intervention to update the
app's view of the metadata.  There should also be some method to purge
the cache before the TTL expires.  I'd hate to be that developer telling
the Marketing VP that we've changed the schema, but the app won't notice
the change for another two hours!

One should be able to tune the TTL down to zero or near zero during
development.  Schema changes are much more frequent during development,
and making the TTL tunable would be the most transparent way to handle this.

I don't think putting schema details into the code is the best
solution.  This would be equivalent to putting a schema description into
a config file, which is one thing we're committed to avoiding in Zend
Framework.  With that solution, it is difficult to keep the schema
description in sync with the actual schema.  Letting the Db component
refresh this for you and cache the results for you would be a better
solution.

Bill

Andi Gutmans wrote:

> I think there are a few options here which we discussed in the past:
> a) Use Zend_Cache to cache the info with a TTL.
> b) Implement a very lightweight Zend_Db specific cache with TTL.
> c) Auto-generate classes (i.e. manual update). Theoretically that and Cache
> are similar solutions as Cache can also be manually generated.
>
> Anyway, Bill is working right now to come with a suggestion for what the
> scope of Zend_Db is and if we need an additional layer, what that would be.
> This issue will definitely be taken into account.
>
> Thanks.
>
> Andi
>
>  
>> -----Original Message-----
>> From: Shahar Evron [mailto:[hidden email]]
>> Sent: Monday, January 01, 2007 6:15 AM
>> To: [hidden email]
>> Subject: [fw-db] Caching of describeTable
>>
>> Hi,
>>
>> Maybe I just need to RTFM but this one is a quickie: Is there
>> a caching solution for Zend_Db_Adapter_xxx::describeTable() ?
>>
>> That one gets called quite frequently, and is very unlikely
>> to change in production environemtns. Caching it somehow
>> would be a smart idea.
>>
>> If nobody has suggestions, I'll do some hacking later and
>> post an article about it.
>>
>> TIA,
>>
>> Shahar.
>>
>>    
>
>
>  

[GavinZend]

Where the schema information goes is more a matter of perspective and
preference, whether cached via Zend_Cache, or cached the Qcodo way.  
Qcodo has a much more robust API for managing changes and
programmatically interacting with the schema, than just a serialization
of describeTable()'s output cached in Zend_Cache, but then I'm bordering
on comparing an apple pie to an orange ;)

BTW, the solutions I've seen using TTL's have extra complexity to work
around distributed environments (e.g. more than one edge/web server).

Cheers,
Gavin

Bill Karwin wrote:

> It's true that requiring a describe() on every page request is
> costly.  I've seen reports that performance is poor for describe and
> other usage of the INFORMATION_SCHEMA virtual tables, in MySQL.  Not
> surprising that the feature was not designed for high performance,
> under the assumption that will be queried infrequently.
>
> Caching the metadata automagically is one solution.  TTL is probably
> of the least expensive way to get some benefit out of the cache, but
> allow for changes to occur without requiring manual intervention to
> update the app's view of the metadata.  There should also be some
> method to purge the cache before the TTL expires.  I'd hate to be that
> developer telling the Marketing VP that we've changed the schema, but
> the app won't notice the change for another two hours!
>
> One should be able to tune the TTL down to zero or near zero during
> development.  Schema changes are much more frequent during
> development, and making the TTL tunable would be the most transparent
> way to handle this.
>
> I don't think putting schema details into the code is the best
> solution.  This would be equivalent to putting a schema description
> into a config file, which is one thing we're committed to avoiding in
> Zend Framework.  With that solution, it is difficult to keep the
> schema description in sync with the actual schema.  Letting the Db
> component refresh this for you and cache the results for you would be
> a better solution.
>
> Bill
>
> Andi Gutmans wrote:
>> I think there are a few options here which we discussed in the past:
>> a) Use Zend_Cache to cache the info with a TTL.
>> b) Implement a very lightweight Zend_Db specific cache with TTL.
>> c) Auto-generate classes (i.e. manual update). Theoretically that and
>> Cache
>> are similar solutions as Cache can also be manually generated.
>>
>> Anyway, Bill is working right now to come with a suggestion for what the
>> scope of Zend_Db is and if we need an additional layer, what that
>> would be.
>> This issue will definitely be taken into account.
>>
>> Thanks.
>>
>> Andi
>>
>>  
>>> -----Original Message-----
>>> From: Shahar Evron [mailto:[hidden email]] Sent: Monday, January
>>> 01, 2007 6:15 AM
>>> To: [hidden email]
>>> Subject: [fw-db] Caching of describeTable
>>>
>>> Hi,
>>>
>>> Maybe I just need to RTFM but this one is a quickie: Is there a
>>> caching solution for Zend_Db_Adapter_xxx::describeTable() ?
>>>
>>> That one gets called quite frequently, and is very unlikely to
>>> change in production environemtns. Caching it somehow would be a
>>> smart idea.
>>>
>>> If nobody has suggestions, I'll do some hacking later and post an
>>> article about it.
>>>
>>> TIA,
>>>
>>> Shahar.

[Shahar Evron]

Personally I think there's a room for both solutions.

I think that in Zend_Db_Table_* classes, we just need to add an optional
parameter initialized to null, holding the table schema (let's call it
$_schema). If not defined, this parameter would be populated in runtime
by describeTable() as happens now - and this can be cached or not
cached, according to another parameter ($_schema_cache_ttl = 0 for
example).

If someone wants code generation a-la-Qcodo, they can do it by writing
some tool to automatically populate the $_schema property - but I don't
think we *must* provide such tool, at least not it the first stage.

In my opinion, we get both enough simplicity and enough flexibility this
way. I do think that allowing the developer to specify the table
structure in advance is a good idea - after all, performance wise on
production this would be better even than caching.

Shahar.

Gavin Vess wrote:

> Where the schema information goes is more a matter of perspective and
> preference, whether cached via Zend_Cache, or cached the Qcodo way.
> Qcodo has a much more robust API for managing changes and
> programmatically interacting with the schema, than just a serialization
> of describeTable()'s output cached in Zend_Cache, but then I'm bordering
> on comparing an apple pie to an orange ;)
>
> BTW, the solutions I've seen using TTL's have extra complexity to work
> around distributed environments (e.g. more than one edge/web server).
>
> Cheers,
> Gavin
>
> Bill Karwin wrote:
>> It's true that requiring a describe() on every page request is
>> costly.  I've seen reports that performance is poor for describe and
>> other usage of the INFORMATION_SCHEMA virtual tables, in MySQL.  Not
>> surprising that the feature was not designed for high performance,
>> under the assumption that will be queried infrequently.
>>
>> Caching the metadata automagically is one solution.  TTL is probably
>> of the least expensive way to get some benefit out of the cache, but
>> allow for changes to occur without requiring manual intervention to
>> update the app's view of the metadata.  There should also be some
>> method to purge the cache before the TTL expires.  I'd hate to be that
>> developer telling the Marketing VP that we've changed the schema, but
>> the app won't notice the change for another two hours!
>>
>> One should be able to tune the TTL down to zero or near zero during
>> development.  Schema changes are much more frequent during
>> development, and making the TTL tunable would be the most transparent
>> way to handle this.
>>
>> I don't think putting schema details into the code is the best
>> solution.  This would be equivalent to putting a schema description
>> into a config file, which is one thing we're committed to avoiding in
>> Zend Framework.  With that solution, it is difficult to keep the
>> schema description in sync with the actual schema.  Letting the Db
>> component refresh this for you and cache the results for you would be
>> a better solution.
>>
>> Bill
>>
>> Andi Gutmans wrote:
>>> I think there are a few options here which we discussed in the past:
>>> a) Use Zend_Cache to cache the info with a TTL.
>>> b) Implement a very lightweight Zend_Db specific cache with TTL.
>>> c) Auto-generate classes (i.e. manual update). Theoretically that and
>>> Cache
>>> are similar solutions as Cache can also be manually generated.
>>>
>>> Anyway, Bill is working right now to come with a suggestion for what the
>>> scope of Zend_Db is and if we need an additional layer, what that
>>> would be.
>>> This issue will definitely be taken into account.
>>>
>>> Thanks.
>>>
>>> Andi
>>>
>>>  
>>>> -----Original Message-----
>>>> From: Shahar Evron [mailto:[hidden email]] Sent: Monday, January
>>>> 01, 2007 6:15 AM
>>>> To: [hidden email]
>>>> Subject: [fw-db] Caching of describeTable
>>>>
>>>> Hi,
>>>>
>>>> Maybe I just need to RTFM but this one is a quickie: Is there a
>>>> caching solution for Zend_Db_Adapter_xxx::describeTable() ?
>>>>
>>>> That one gets called quite frequently, and is very unlikely to
>>>> change in production environemtns. Caching it somehow would be a
>>>> smart idea.
>>>>
>>>> If nobody has suggestions, I'll do some hacking later and post an
>>>> article about it.
>>>>
>>>> TIA,
>>>>
>>>> Shahar.
>

[Christopher Thompson-2]

I think at the lowest layer of this system, the schema details are in
the code no matter what you do. And I think it is probably the simplest
in many cases to define the schema in code because that definition is
adjacent to the code using it. Let's be honest -- syncing schema and
code is pretty trivial for a large number of PHP projects (80/20) and it
is done in code most of the time.

Automating keeping the code in sync with the actual schema really should
be an optional wrapper or plugin to the core db code. Whether the schema
data is hand updated, comes from DESCRIBE or TTL or is code generated by
a tool is a decision that is up to the programmer. DESCRIBE, TTL and
generated code all need a well defined target to which the data is
applied -- and that should be a clear manual configuration system in the
core code.

If the code can be wrapped in automation layers then ZF can provide
those that Zend feel are both most suitable and best practices, but it
enables others to build wrappers as they see fit.



Bill Karwin wrote:

> It's true that requiring a describe() on every page request is costly.  
> I've seen reports that performance is poor for describe and other usage
> of the INFORMATION_SCHEMA virtual tables, in MySQL.  Not surprising that
> the feature was not designed for high performance, under the assumption
> that will be queried infrequently.
>
> Caching the metadata automagically is one solution.  TTL is probably of
> the least expensive way to get some benefit out of the cache, but allow
> for changes to occur without requiring manual intervention to update the
> app's view of the metadata.  There should also be some method to purge
> the cache before the TTL expires.  I'd hate to be that developer telling
> the Marketing VP that we've changed the schema, but the app won't notice
> the change for another two hours!
>
> One should be able to tune the TTL down to zero or near zero during
> development.  Schema changes are much more frequent during development,
> and making the TTL tunable would be the most transparent way to handle
> this.
>
> I don't think putting schema details into the code is the best
> solution.  This would be equivalent to putting a schema description into
> a config file, which is one thing we're committed to avoiding in Zend
> Framework.  With that solution, it is difficult to keep the schema
> description in sync with the actual schema.  Letting the Db component
> refresh this for you and cache the results for you would be a better
> solution.
>
> Bill
>
> Andi Gutmans wrote:
>> I think there are a few options here which we discussed in the past:
>> a) Use Zend_Cache to cache the info with a TTL.
>> b) Implement a very lightweight Zend_Db specific cache with TTL.
>> c) Auto-generate classes (i.e. manual update). Theoretically that and
>> Cache
>> are similar solutions as Cache can also be manually generated.
>>
>> Anyway, Bill is working right now to come with a suggestion for what the
>> scope of Zend_Db is and if we need an additional layer, what that
>> would be.
>> This issue will definitely be taken into account.
>>
>> Thanks.
>>
>> Andi
>>
>>  
>>> -----Original Message-----
>>> From: Shahar Evron [mailto:[hidden email]] Sent: Monday, January
>>> 01, 2007 6:15 AM
>>> To: [hidden email]
>>> Subject: [fw-db] Caching of describeTable
>>>
>>> Hi,
>>>
>>> Maybe I just need to RTFM but this one is a quickie: Is there a
>>> caching solution for Zend_Db_Adapter_xxx::describeTable() ?
>>>
>>> That one gets called quite frequently, and is very unlikely to change
>>> in production environemtns. Caching it somehow would be a smart idea.
>>>
>>> If nobody has suggestions, I'll do some hacking later and post an
>>> article about it.
>>>
>>> TIA,
>>>
>>> Shahar.
>>>
>>>    
>>
>>
>>  
>
>
>

[Shahar Evron]

I just looked at the code and it seems that you can preset the value of
$this->_cols in classes than extend Zend_Db_Table, just like you specify
$_name and $_primary. Haven't tested, but it seems that doing this would
eliminate the DESCRIBE queries.

I think documenting this would get us half way. The next half is
considering caching, and possibly a code generation tool.

Shahar.

Shahar Evron wrote:

> Personally I think there's a room for both solutions.
>
> I think that in Zend_Db_Table_* classes, we just need to add an optional
> parameter initialized to null, holding the table schema (let's call it
> $_schema). If not defined, this parameter would be populated in runtime
> by describeTable() as happens now - and this can be cached or not
> cached, according to another parameter ($_schema_cache_ttl = 0 for
> example).
>
> If someone wants code generation a-la-Qcodo, they can do it by writing
> some tool to automatically populate the $_schema property - but I don't
> think we *must* provide such tool, at least not it the first stage.
>
> In my opinion, we get both enough simplicity and enough flexibility this
> way. I do think that allowing the developer to specify the table
> structure in advance is a good idea - after all, performance wise on
> production this would be better even than caching.
>
> Shahar.
>
> Gavin Vess wrote:
>> Where the schema information goes is more a matter of perspective and
>> preference, whether cached via Zend_Cache, or cached the Qcodo way.
>> Qcodo has a much more robust API for managing changes and
>> programmatically interacting with the schema, than just a serialization
>> of describeTable()'s output cached in Zend_Cache, but then I'm bordering
>> on comparing an apple pie to an orange ;)
>>
>> BTW, the solutions I've seen using TTL's have extra complexity to work
>> around distributed environments (e.g. more than one edge/web server).
>>
>> Cheers,
>> Gavin
>>
>> Bill Karwin wrote:
>>> It's true that requiring a describe() on every page request is
>>> costly.  I've seen reports that performance is poor for describe and
>>> other usage of the INFORMATION_SCHEMA virtual tables, in MySQL.  Not
>>> surprising that the feature was not designed for high performance,
>>> under the assumption that will be queried infrequently.
>>>
>>> Caching the metadata automagically is one solution.  TTL is probably
>>> of the least expensive way to get some benefit out of the cache, but
>>> allow for changes to occur without requiring manual intervention to
>>> update the app's view of the metadata.  There should also be some
>>> method to purge the cache before the TTL expires.  I'd hate to be that
>>> developer telling the Marketing VP that we've changed the schema, but
>>> the app won't notice the change for another two hours!
>>>
>>> One should be able to tune the TTL down to zero or near zero during
>>> development.  Schema changes are much more frequent during
>>> development, and making the TTL tunable would be the most transparent
>>> way to handle this.
>>>
>>> I don't think putting schema details into the code is the best
>>> solution.  This would be equivalent to putting a schema description
>>> into a config file, which is one thing we're committed to avoiding in
>>> Zend Framework.  With that solution, it is difficult to keep the
>>> schema description in sync with the actual schema.  Letting the Db
>>> component refresh this for you and cache the results for you would be
>>> a better solution.
>>>
>>> Bill
>>>
>>> Andi Gutmans wrote:
>>>> I think there are a few options here which we discussed in the past:
>>>> a) Use Zend_Cache to cache the info with a TTL.
>>>> b) Implement a very lightweight Zend_Db specific cache with TTL.
>>>> c) Auto-generate classes (i.e. manual update). Theoretically that and
>>>> Cache
>>>> are similar solutions as Cache can also be manually generated.
>>>>
>>>> Anyway, Bill is working right now to come with a suggestion for what the
>>>> scope of Zend_Db is and if we need an additional layer, what that
>>>> would be.
>>>> This issue will definitely be taken into account.
>>>>
>>>> Thanks.
>>>>
>>>> Andi
>>>>
>>>>  
>>>>> -----Original Message-----
>>>>> From: Shahar Evron [mailto:[hidden email]] Sent: Monday, January
>>>>> 01, 2007 6:15 AM
>>>>> To: [hidden email]
>>>>> Subject: [fw-db] Caching of describeTable
>>>>>
>>>>> Hi,
>>>>>
>>>>> Maybe I just need to RTFM but this one is a quickie: Is there a
>>>>> caching solution for Zend_Db_Adapter_xxx::describeTable() ?
>>>>>
>>>>> That one gets called quite frequently, and is very unlikely to
>>>>> change in production environemtns. Caching it somehow would be a
>>>>> smart idea.
>>>>>
>>>>> If nobody has suggestions, I'll do some hacking later and post an
>>>>> article about it.
>>>>>
>>>>> TIA,
>>>>>
>>>>> Shahar.
>

[Shahar Evron]

Hi All,

I wrote a small and simple code generator script that creates a bunch of
Zend_Db_Table classes for my database, each in a separate file. It's not
exactly polished but it does the job - a full description is available
here: http://prematureoptimization.org/blog/archives/14 and the script
is here: http://prematureoptimization.org/scripts/dbgenerator.phps

I'd be happy to know if anybody thinks this is useful - or have any
comments.

Shahar.

Shahar Evron wrote:

> I just looked at the code and it seems that you can preset the value of
> $this->_cols in classes than extend Zend_Db_Table, just like you specify
> $_name and $_primary. Haven't tested, but it seems that doing this would
> eliminate the DESCRIBE queries.
>
> I think documenting this would get us half way. The next half is
> considering caching, and possibly a code generation tool.
>
> Shahar.
>
> Shahar Evron wrote:
>> Personally I think there's a room for both solutions.
>>
>> I think that in Zend_Db_Table_* classes, we just need to add an optional
>> parameter initialized to null, holding the table schema (let's call it
>> $_schema). If not defined, this parameter would be populated in runtime
>> by describeTable() as happens now - and this can be cached or not
>> cached, according to another parameter ($_schema_cache_ttl = 0 for
>> example).
>>
>> If someone wants code generation a-la-Qcodo, they can do it by writing
>> some tool to automatically populate the $_schema property - but I don't
>> think we *must* provide such tool, at least not it the first stage.
>>
>> In my opinion, we get both enough simplicity and enough flexibility this
>> way. I do think that allowing the developer to specify the table
>> structure in advance is a good idea - after all, performance wise on
>> production this would be better even than caching.
>>
>> Shahar.
>>
>> Gavin Vess wrote:
>>> Where the schema information goes is more a matter of perspective and
>>> preference, whether cached via Zend_Cache, or cached the Qcodo way.
>>> Qcodo has a much more robust API for managing changes and
>>> programmatically interacting with the schema, than just a serialization
>>> of describeTable()'s output cached in Zend_Cache, but then I'm bordering
>>> on comparing an apple pie to an orange ;)
>>>
>>> BTW, the solutions I've seen using TTL's have extra complexity to work
>>> around distributed environments (e.g. more than one edge/web server).
>>>
>>> Cheers,
>>> Gavin
>>>
>>> Bill Karwin wrote:
>>>> It's true that requiring a describe() on every page request is
>>>> costly.  I've seen reports that performance is poor for describe and
>>>> other usage of the INFORMATION_SCHEMA virtual tables, in MySQL.  Not
>>>> surprising that the feature was not designed for high performance,
>>>> under the assumption that will be queried infrequently.
>>>>
>>>> Caching the metadata automagically is one solution.  TTL is probably
>>>> of the least expensive way to get some benefit out of the cache, but
>>>> allow for changes to occur without requiring manual intervention to
>>>> update the app's view of the metadata.  There should also be some
>>>> method to purge the cache before the TTL expires.  I'd hate to be that
>>>> developer telling the Marketing VP that we've changed the schema, but
>>>> the app won't notice the change for another two hours!
>>>>
>>>> One should be able to tune the TTL down to zero or near zero during
>>>> development.  Schema changes are much more frequent during
>>>> development, and making the TTL tunable would be the most transparent
>>>> way to handle this.
>>>>
>>>> I don't think putting schema details into the code is the best
>>>> solution.  This would be equivalent to putting a schema description
>>>> into a config file, which is one thing we're committed to avoiding in
>>>> Zend Framework.  With that solution, it is difficult to keep the
>>>> schema description in sync with the actual schema.  Letting the Db
>>>> component refresh this for you and cache the results for you would be
>>>> a better solution.
>>>>
>>>> Bill
>>>>
>>>> Andi Gutmans wrote:
>>>>> I think there are a few options here which we discussed in the past:
>>>>> a) Use Zend_Cache to cache the info with a TTL.
>>>>> b) Implement a very lightweight Zend_Db specific cache with TTL.
>>>>> c) Auto-generate classes (i.e. manual update). Theoretically that and
>>>>> Cache
>>>>> are similar solutions as Cache can also be manually generated.
>>>>>
>>>>> Anyway, Bill is working right now to come with a suggestion for what the
>>>>> scope of Zend_Db is and if we need an additional layer, what that
>>>>> would be.
>>>>> This issue will definitely be taken into account.
>>>>>
>>>>> Thanks.
>>>>>
>>>>> Andi
>>>>>
>>>>>  
>>>>>> -----Original Message-----
>>>>>> From: Shahar Evron [mailto:[hidden email]] Sent: Monday, January
>>>>>> 01, 2007 6:15 AM
>>>>>> To: [hidden email]
>>>>>> Subject: [fw-db] Caching of describeTable
>>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> Maybe I just need to RTFM but this one is a quickie: Is there a
>>>>>> caching solution for Zend_Db_Adapter_xxx::describeTable() ?
>>>>>>
>>>>>> That one gets called quite frequently, and is very unlikely to
>>>>>> change in production environemtns. Caching it somehow would be a
>>>>>> smart idea.
>>>>>>
>>>>>> If nobody has suggestions, I'll do some hacking later and post an
>>>>>> article about it.
>>>>>>
>>>>>> TIA,
>>>>>>
>>>>>> Shahar.
>

[Andi Gutmans]

This is very cool. We'll definitely take this into account as we look at the
improvements we need to make for DB and see if something like this is
suitable. Thanks a lot for sending this.
 
Andi

> -----Original Message-----
> From: Shahar Evron [mailto:[hidden email]]
> Sent: Friday, January 05, 2007 11:58 AM
> To: [hidden email]
> Cc: Gavin Vess; Bill Karwin
> Subject: Re: [fw-db] Caching of describeTable
>
> Hi All,
>
> I wrote a small and simple code generator script that creates
> a bunch of Zend_Db_Table classes for my database, each in a
> separate file. It's not exactly polished but it does the job
> - a full description is available
> here: http://prematureoptimization.org/blog/archives/14 and
> the script is here:
> http://prematureoptimization.org/scripts/dbgenerator.phps
>
> I'd be happy to know if anybody thinks this is useful - or
> have any comments.
>
> Shahar.
>
> Shahar Evron wrote:
> > I just looked at the code and it seems that you can preset
> the value
> > of $this->_cols in classes than extend Zend_Db_Table, just like you
> > specify $_name and $_primary. Haven't tested, but it seems
> that doing
> > this would eliminate the DESCRIBE queries.
> >
> > I think documenting this would get us half way. The next half is
> > considering caching, and possibly a code generation tool.
> >
> > Shahar.
> >
> > Shahar Evron wrote:
> >> Personally I think there's a room for both solutions.
> >>
> >> I think that in Zend_Db_Table_* classes, we just need to add an
> >> optional parameter initialized to null, holding the table schema
> >> (let's call it $_schema). If not defined, this parameter would be
> >> populated in runtime by describeTable() as happens now -
> and this can
> >> be cached or not cached, according to another parameter
> >> ($_schema_cache_ttl = 0 for example).
> >>
> >> If someone wants code generation a-la-Qcodo, they can do it by
> >> writing some tool to automatically populate the $_schema
> property -
> >> but I don't think we *must* provide such tool, at least
> not it the first stage.
> >>
> >> In my opinion, we get both enough simplicity and enough
> flexibility
> >> this way. I do think that allowing the developer to
> specify the table
> >> structure in advance is a good idea - after all,
> performance wise on
> >> production this would be better even than caching.
> >>
> >> Shahar.
> >>
> >> Gavin Vess wrote:
> >>> Where the schema information goes is more a matter of perspective
> >>> and preference, whether cached via Zend_Cache, or cached
> the Qcodo way.
> >>> Qcodo has a much more robust API for managing changes and
> >>> programmatically interacting with the schema, than just a
> >>> serialization of describeTable()'s output cached in
> Zend_Cache, but
> >>> then I'm bordering on comparing an apple pie to an orange ;)
> >>>
> >>> BTW, the solutions I've seen using TTL's have extra complexity to
> >>> work around distributed environments (e.g. more than one
> edge/web server).
> >>>
> >>> Cheers,
> >>> Gavin
> >>>
> >>> Bill Karwin wrote:
> >>>> It's true that requiring a describe() on every page request is
> >>>> costly.  I've seen reports that performance is poor for describe
> >>>> and other usage of the INFORMATION_SCHEMA virtual
> tables, in MySQL.  
> >>>> Not surprising that the feature was not designed for high
> >>>> performance, under the assumption that will be queried
> infrequently.
> >>>>
> >>>> Caching the metadata automagically is one solution.  TTL is
> >>>> probably of the least expensive way to get some benefit
> out of the
> >>>> cache, but allow for changes to occur without requiring manual
> >>>> intervention to update the app's view of the metadata.  There
> >>>> should also be some method to purge the cache before the TTL
> >>>> expires.  I'd hate to be that developer telling the Marketing VP
> >>>> that we've changed the schema, but the app won't notice
> the change for another two hours!
> >>>>
> >>>> One should be able to tune the TTL down to zero or near
> zero during
> >>>> development.  Schema changes are much more frequent during
> >>>> development, and making the TTL tunable would be the most
> >>>> transparent way to handle this.
> >>>>
> >>>> I don't think putting schema details into the code is the best
> >>>> solution.  This would be equivalent to putting a schema
> description
> >>>> into a config file, which is one thing we're committed
> to avoiding
> >>>> in Zend Framework.  With that solution, it is difficult
> to keep the
> >>>> schema description in sync with the actual schema.  
> Letting the Db
> >>>> component refresh this for you and cache the results for
> you would
> >>>> be a better solution.
> >>>>
> >>>> Bill
> >>>>
> >>>> Andi Gutmans wrote:
> >>>>> I think there are a few options here which we discussed
> in the past:
> >>>>> a) Use Zend_Cache to cache the info with a TTL.
> >>>>> b) Implement a very lightweight Zend_Db specific cache with TTL.
> >>>>> c) Auto-generate classes (i.e. manual update).
> Theoretically that
> >>>>> and Cache are similar solutions as Cache can also be manually
> >>>>> generated.
> >>>>>
> >>>>> Anyway, Bill is working right now to come with a suggestion for
> >>>>> what the scope of Zend_Db is and if we need an
> additional layer,
> >>>>> what that would be.
> >>>>> This issue will definitely be taken into account.
> >>>>>
> >>>>> Thanks.
> >>>>>
> >>>>> Andi
> >>>>>
> >>>>>  
> >>>>>> -----Original Message-----
> >>>>>> From: Shahar Evron [mailto:[hidden email]] Sent: Monday,
> >>>>>> January 01, 2007 6:15 AM
> >>>>>> To: [hidden email]
> >>>>>> Subject: [fw-db] Caching of describeTable
> >>>>>>
> >>>>>> Hi,
> >>>>>>
> >>>>>> Maybe I just need to RTFM but this one is a quickie:
> Is there a
> >>>>>> caching solution for Zend_Db_Adapter_xxx::describeTable() ?
> >>>>>>
> >>>>>> That one gets called quite frequently, and is very unlikely to
> >>>>>> change in production environemtns. Caching it somehow
> would be a
> >>>>>> smart idea.
> >>>>>>
> >>>>>> If nobody has suggestions, I'll do some hacking later
> and post an
> >>>>>> article about it.
> >>>>>>
> >>>>>> TIA,
> >>>>>>
> >>>>>> Shahar.
> >
>