#1521: Doctrine Query Cache not storing/rebuilding query with 3 part relation
properly
-----------------------------------+----------------------------------------
Reporter: Jay.Klehr | Owner: romanb
Type: defect | Status: new
Priority: minor | Milestone: 1.0.3
Component: Query/Hydration | Version: 1.0.2
Keywords: query cache | Has_test: 0
Mystatus: Pending Core Response | Has_patch: 0
-----------------------------------+----------------------------------------
I've been playing with Doctrine's Query cache coupled with APC in an app
I'm working on and noticed that the cache isn't rebuilding (or storing)
the query components properly for certain queries.
Models:
{{{
class SecurityAcl extends Doctrine_Record
{
public function setTableDefinition()
{
$this->setTableName('security_acl');
$this->hasColumn('id', 'integer', 4, array('unsigned' => true,
'primary' => true, 'autoincrement' => true, 'type' => 'integer', 'length'
=> '4'));
$this->hasColumn('module_id', 'integer', 4, array('unsigned' =>
true, 'notnull' => true, 'type' => 'integer', 'length' => '4'));
$this->hasColumn('resource_id', 'integer', 4, array('unsigned' =>
true, 'notnull' => true, 'type' => 'integer', 'length' => '4'));
$this->hasColumn('privilege_id', 'integer', 4, array('unsigned' =>
true, 'notnull' => true, 'type' => 'integer', 'length' => '4'));
$this->index('unique', array('fields' => array(0 => 'module_id', 1
=> 'resource_id', 2 => 'privilege_id'), 'type' => 'unique'));
}
public function setUp()
{
$this->hasOne('SecurityAclPart as Module', array('local' =>
'module_id',
'foreign' =>
'id'));
$this->hasOne('SecurityAclPart as Resource', array('local' =>
'resource_id',
'foreign' =>
'id'));
$this->hasOne('SecurityAclPart as Privilege', array('local' =>
'privilege_id',
'foreign' =>
'id'));
$this->hasMany('SecurityGroup as Groups', array('refClass' =>
'SecurityGroupAcl',
'local' =>
'acl_id',
'foreign' =>
'group_id'));
}
}
class SecurityAclPart extends Doctrine_Record
{
public function setTableDefinition()
{
$this->setTableName('security_acl_part');
$this->hasColumn('id', 'integer', 4, array('unsigned' => true,
'primary' => true, 'autoincrement' => true, 'type' => 'integer', 'length'
=> '4'));
$this->hasColumn('name', 'string', 32, array('notnull' => true,
'type' => 'string', 'length' => '32'));
$this->index('unique', array('fields' => array(0 => 'name'),
'type' => 'unique'));
}
public function setUp()
{
$this->hasMany('SecurityAcl as Modules', array('local' => 'id',
'foreign' =>
'module_id'));
$this->hasMany('SecurityAcl as Resources', array('local' => 'id',
'foreign' =>
'resource_id'));
$this->hasMany('SecurityAcl as Privileges', array('local' => 'id',
'foreign' =>
'privilege_id'));
}
}
class SecurityGroup extends Doctrine_Record
{
public function setTableDefinition()
{
$this->setTableName('security_group');
$this->hasColumn('id', 'integer', 4, array('unsigned' => true,
'primary' => true, 'autoincrement' => true, 'type' => 'integer', 'length'
=> '4'));
$this->hasColumn('name', 'string', 32, array('notnull' => true,
'type' => 'string', 'length' => '32'));
$this->hasColumn('description', 'string', 255, array('type' =>
'string', 'length' => '255'));
$this->index('unique', array('fields' => array(0 => 'name'),
'type' => 'unique'));
}
public function setUp()
{
$this->hasMany('SecurityAcl as Acl', array('refClass' =>
'SecurityGroupAcl',
'local' => 'group_id',
'foreign' =>
'acl_id'));
}
}
class SecurityGroupAcl extends Doctrine_Record
{
public function setTableDefinition()
{
$this->setTableName('security_group_acl');
$this->hasColumn('group_id', 'integer', 4, array('unsigned' =>
true, 'primary' => true, 'type' => 'integer', 'length' => '4'));
$this->hasColumn('acl_id', 'integer', 4, array('unsigned' => true,
'primary' => true, 'type' => 'integer', 'length' => '4'));
}
public function setUp()
{
}
}
}}}
I've only included what's related. These were generated by Doctrine from
a schema file (these models are part of an ACL module for Zend Framework
that I found on the net (still an early build)).
Now, the query in question has the following DQL:
{{{
Doctrine_Query::create()
->from('SecurityAcl a')
->innerJoin('a.Module m')
->innerJoin('a.Resource r')
->innerJoin('a.Privilege p')
->leftJoin('a.Groups g INDEXBY g.id')
->orderby('m.name, r.name, p.name')
->execute();
}}}
Which generates the following SQL:
{{{
SELECT s.id AS s__id, s.module_id AS s__module_id, s.resource_id AS
s__resource_id, s.privilege_id AS s__privilege_id, s2.id AS s2__id,
s2.name AS s2__name, s3.id AS s3__id, s3.name AS s3__name, s4.id AS
s4__id, s4.name AS s4__name, s5.id AS s5__id, s5.name AS s5__name,
s5.description AS s5__description FROM security_acl s INNER JOIN
security_acl_part s2 ON s.module_id = s2.id INNER JOIN security_acl_part
s3 ON s.resource_id = s3.id INNER JOIN security_acl_part s4 ON
s.privilege_id = s4.id LEFT JOIN security_group_acl s6 ON s.id = s6.acl_id
LEFT JOIN security_group s5 ON s5.id = s6.group_id ORDER BY s2.name,
s3.name, s4.name
}}}
And to help debug this, I've outputted the "alias" and the _toString of
the table in Doctrine_Query_Abstract::_exec() (I foreached
$this->_queryComponents for this particular query for this output after
$query is found (either through cache or not)).
Here's that output from the non-cached version of this query:
{{{
a
Component : SecurityAcl
Table : security_acl
m
Component : SecurityAclPart
Table : security_acl_part
r
Component : SecurityAclPart
Table : security_acl_part
p
Component : SecurityAclPart
Table : security_acl_part
g
Component : SecurityGroup
Table : security_group
a.Groups.SecurityGroupAcl
Component : SecurityGroupAcl
Table : security_group_acl
}}}
The single line is the alias, and the "Component: Table:" parts are the
_toString output from the table object
($this->_queryComponents[$alias]!['table']).
Now, when the query is retrieved from the cache, the SQL is identical, but
these components are not. Here's the same output above, but from the
cached query:
{{{
a
Component : SecurityAcl
Table : security_acl
m
Component : SecurityAclPart
Table : security_acl_part
r
Component : SecurityAclPart
Table : security_acl_part
p
Component : SecurityAclPart
Table : security_acl_part
g
Component : SecurityGroup
Table : security_group
a.Groups.SecurityGroupAcl
Component : SecurityGroup
Table : security_group
}}}
Notice that the last alias' table is wrong.
I dug in the cache some, and pulled this out for the query in question
(unserialized array of what's stored in the cache):
{{{
Array
(
[0] => SELECT s.id AS s__id, s.module_id AS s__module_id,
s.resource_id AS s__resource_id, s.privilege_id AS s__privilege_id, s2.id
AS s2__id, s2.name AS s2__name, s3.id AS s3__id, s3.name AS s3__name,
s4.id AS s4__id, s4.name AS s4__name, s5.id AS s5__id, s5.name AS
s5__name, s5.description AS s5__description FROM security_acl s INNER JOIN
security_acl_part s2 ON s.module_id = s2.id INNER JOIN security_acl_part
s3 ON s.resource_id = s3.id INNER JOIN security_acl_part s4 ON
s.privilege_id = s4.id LEFT JOIN security_group_acl s6 ON s.id = s6.acl_id
LEFT JOIN security_group s5 ON s5.id = s6.group_id ORDER BY s2.name,
s3.name, s4.name
[1] => Array
(
[a] => Array
(
[0] => SecurityAcl
)
[m] => Array
(
[0] => a.Module
)
[r] => Array
(
[0] => a.Resource
)
[p] => Array
(
[0] => a.Privilege
)
[g] => Array
(
[0] => a.Groups
[1] => id
)
[a.Groups.SecurityGroupAcl] => Array
(
[0] => a.Groups.Groups
)
)
[2] => Array
(
[s] => a
[s2] => m
[s3] => r
[s4] => p
[s5] => g
[s6] => a.Groups.SecurityGroupAcl
)
)
}}}
I don't think that last alias is supposed to be "a.Groups.Groups".
As a test, I tried forcing the saved value for that last piece to
"a.Groups.SecurityGroupAcl", which stored fine, but then the part of
Doctrine_Query_Abstract that makes a query from the cache doesn't parse it
properly, and still makes it "a.Groups.Groups". I suppose I could
probably also force that and this would work properly, but I haven't tried
that yet.
I suspect this hasn't been noticed yet as it seems to only happen with
slightly more complex queries (possibly due to the alias having 3 parts,
rather than just 2).
Any ideas on a fix? I simplified some of the models some, removing
unnecessary relations, but that hasn't helped (what I've pasted above is
after the cleanup I've done). If it's something that can be fixed in the
models/query, I'm all ears, and I'll submit the feedback to the module's
author, but the query works alright as long as it doesn't get cached, so I
believe this to be a Doctrine issue.
I'm using PHP 5.2.5, Doctrine 1.0.2+ (revision 5009 of 1.0 trunk), Zend
Framework 1.6.1, APC 3.0.19.
--
Ticket URL: <http://trac.doctrine-project.org/ticket/1521>
Doctrine <http://www.phpdoctrine.org>
PHP Doctrine Object Relational Mapper
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"doctrine-svn" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.co.uk/group/doctrine-svn?hl=en-GB
-~----------~----~----~----~------~----~------~--~---
