Jesus Christ, Yet Another Cache Plugin...
Yes it is. Most cache plugins suffer from cache validity issues, some of them don't
cache enough data and some of them -- from complexity. This plugin solves most problems:
you don't have to think about validity, it's simple in use and a lot of usual database queries might be cached
(I'm not talking about rather exotic queries using IFs, WHENs or CASEs more typical for billing or statistic systems).
After all, there is a common situation for some of you -- you are making your application
without thinking about caching, and when it's time -- voila, this plugin is at your service.
Under Its Skin
This plugin is based on great idea of tag-based caching and uses slightly modified sfCacheTaggingPlugin.
Also, some of Doctrine classes are modified -- mostly those ones where the DQL parsing is proccessed.
Installation/Upgrading
Setup
First of all, copy sfCacheTaggingPlugin from sfYACPlugin/plugin directory and place it in
your application's plugins directory. All installation instruction are here.
Usage
This plugin might be used in three typical cases:
- native DQL-query that is in your action;
- local and foreign relations, i.e. $category->getArticles() or $article->getAuthor()
- some modification of first item -- you can use it with a pager.
Native DQL usage
One-table queries
Take a look at first example of schema options:
Category:
tableName: Category_table
actAs:
Cachetaggable:
query1:
from: [Category]
where:
Category.id:
clause1:
operator: <=
right:
Category.author_id:
operator: =
right:
orderby:
fields: [Category.id ASC]
limit: ~
offset: ~
columns:
name_column as name:
type: string
size: 255
description:
type: string
size: 1000
auth_id as author_id:
type: integer
size: 4
relations:
Author:
local: author_id
foreign: id
class: sfGuardUser
foreignAlias: categories
type: one
onDelete: CASCADE
Articles:
class: Article
local: id
foreign: category_id
foreignAlias: Category
type: many
Photos:
class: Photo
local: id
foreign: category_id
foreignAlias: Category
type: many
There might be several queries cached of course, just add another key, call it as you like,
-- query2 for example ;)
Cache options have pretty straightforward names, so I think there is no need to explain them.
One thing to note: conditions in 'where' clause are cached only if they are glued with 'AND'
in respective DQL. In other words, queries with 'OR's in 'where' clause are not cached.
There are some rules in options defining:
if there are square brackets -- they shoud be there:
from: [Category]
The 'root' column names in 'where' clause (i.e. Category.id and Category.author_id)
should be on the left side in dql-query's where clause conditions, for example:
$q = Doctrine_Query::create()
...
->where('
cat.id <= ? AND cat.author_id = 1'
array(25)
)
...
The field name should be prefixed with the component name to avoid ambiguity.
The following DQL will satisfy these shema options:
$q = Doctrine_Query::create()
->from('Category cat')
->where('
cat.id <= ? AND cat.author_id = 1',
array(25)
)
->orderBy('id ASC')
->limit(10)
->offset(0);
but not
$q = Doctrine_Query::create()
->from('Category cat')
->where('
cat.id <= ? AND cat.author_id = 1',
array(25)
)
->orderBy('id ASC')
->limit(10);
because there is 'offset' condition mentioned in schema, but there is no offset in DQL,
and not
$q = Doctrine_Query::create()
->from('Category cat')
->where('
cat.id <= ? AND cat.author_id = 1 cat.id > 1',
array(25)
)
->orderBy('id ASC')
->limit(10)
->offset(0);
because there is no such condition in schema that Category.id should be greater than something.
It's also possible to specify concrete values that should be cached, for example:
Cachetaggable:
query1:
from: [Category]
where:
Category.id:
clause1:
operator: <=
right:
Category.author_id:
operator: =
right: 42
orderby:
fields: [Category.id ASC]
limit: ~
offset: ~
In this case only queries with cat.author_id = 42 will be cached.
Multiple-table queries
It's also possible to cache multiple tables queries. Here is an example:
$q = Doctrine_Query::create()
->from('Category c, c.Articles ca INNER JOIN ca.Comments cc')// #
->where('
c.id <= 54 AND cc.author_id = ca.author_id AND cc.author_id = 1 AND ca.author_id = 1'
)
->orderBy('id ASC');
The following schema options are:
Category:
tableName: Category_table_name
actAs:
Cachetaggable:
query1:
from: [Category LEFT JOIN Articles INNER JOIN Comments]
where:
Category.id:
clause1:
operator: <=
right:
Comment.author_id:
clause1:
operator: =
right: Article.author_id
clause2:
operator: =
right: ~
Article.author_id:
operator: =
right:
orderby:
fields: [Category.id ASC]
columns:
name_column as name:
type: string
size: 255
description:
type: string
size: 1000
auth_id as author_id:
type: integer
size: 4
relations:
Author:
local: author_id
foreign: id
class: sfGuardUser
foreignAlias: categories
type: one
onDelete: CASCADE
Articles:
class: Article
local: id
foreign: category_id
foreignAlias: Category
type: many
Photos:
class: Photo
local: id
foreign: category_id
foreignAlias: Category
type: many
The first thing to note: replace all commas with 'LEFT JOIN' (just like Doctrine does).
The components order in 'from' clause does matter, of course, in 'order' clause does either.
Also note that I select data from the only one root component (Category) and its relations, i.e.its properties:
Articles and Comments. But when it comes to component properies in, for example, 'where' clause,
I operate with component names, not relations -- naturally:
Comment.author_id:
clause1:
operator: =
right: Article.author_id
** ACHTUNG!**
All relations' components that are listed in 'from' clause must have actAs: Cachetaggable
in schema, i.e. Article and Comment components from previous example.
This plugin also caches queries with aggregate functions and 'group by' clauses:
$q = Doctrine_Query::create()
->select('SUM(c.author_id) as asdasdas, c.*, a.*, om.*')
->from('Category c, c.Articles a INNER JOIN a.Comments om')// #
->where('
c.id <= ? AND om.author_id = a.author_id AND om.author_id = ? AND a.author_id = 1',
array(54, 1)
)
->orderBy('id ASC')
->groupBy('c.id')
->limit(10)
->offset(0);
Schema options would look like that:
Cachetaggable:
query1:
select:
fieldset1: SUM(Category.author_id)
fieldset2: Category.*
fieldset3: Article.*
fieldset4: Comment.*
from: [Category LEFT JOIN Articles INNER JOIN Comments]
where:
Category.id:
clause1:
operator: <=
right:
Comment.author_id:
clause1:
operator: =
right: Article.author_id
clause2:
operator: =
right: ~
Article.author_id:
operator: =
right:
groupby: [Category.id]
orderby:
fields: [Category.id ASC]
limit: ~
offset: ~
If you want to 'DISTINCT SELECT', like that:
$q = Doctrine_Query::create()
->select('DISTINCT c.*, a.*, om.*')
->from('Category c, c.Articles a INNER JOIN a.Comments om')// #
->where('
c.id <= ? AND om.author_id = a.author_id AND om.author_id = ? AND a.author_id = 1',
array(54, 1)
)
->orderBy('id ASC')
->groupBy('c.id')
->limit(10)
->offset(0);
just add the keyword 'distinct' in 'select' clause:
Cachetaggable:
query1:
select:
distinct: distinct
fieldset2: Category.*
fieldset3: Article.*
fieldset4: Comment.*
from: [Category LEFT JOIN Articles INNER JOIN Comments]
where:
Category.id:
clause1:
operator: <=
right:
Comment.author_id:
clause1:
operator: =
right: Article.author_id
clause2:
operator: =
right: ~
Article.author_id:
operator: =
right:
groupby: [Category.id]
orderby:
fields: [Category.id ASC]
limit: ~
offset: ~
Doctrine relations queries
Foreign one-to-one relation DQL looks like this:
FROM Profile WHERE Profile.user_id = ?
So the schema options should look like following:
Profile:
actAs:
Cachetaggable:
q1:
from: ~
where:
Profile.user_id:
operator: =
right: ~
columns:
user_id:
type: integer
size: 4
name: string(255)
surname: string(255)
relations:
sfGuardUser:
local: user_id
foreign: id
type: one
onDelete: CASCADE
Foreign one-to-many relation DQL looks like
FROM Article WHERE Article.cat_id IN (?)
so the schema options are
Article:
actAs:
Cachetaggable:
q1:
from: [Article]
where:
Article.category_id:
operator: IN
right: ~
Local relation look the same, for example local one-to-one relation is something like
FROM sfGuardUser WHERE sfGuardUser.id = ?
so the schema options are quite the same with ones for foreign relation:
sfGuardUser:
actAs:
Timestampable: ~
Cachetaggable:
q1:
from: ~
where:
sfGuardUser.id:
operator: =
right: ~
q2:
from: ~
where:
sfGuardUser.id:
operator: =
right: ~
Many-to-many relation is not a problem either, as it is just a combination of foreign relations:
first one -- from FirstComponent to FirstComponent2SecondComponent, and the second -- from
FirstComponent2SecondComponent to SecondComponent. Note that FirstComponent2SecondComponent
should have actAs: Cachetaggable option in schema.
Using pager
The only thing you need to do is adding 'offset' and 'limit' options to your schema,
because sfDoctrinePager does it with all dqls internally.
One might compare the sql-queries number before and after caching in symfony panel.
But be aware that sometimes Doctrine explodes one dql to two sql queries. For example,
any left join is splitted on two parts. The first query is executed in Doctrine_Query::getSqlQuery()
and it is the same with the original DQL except one thing: it is executed with 'DISTINCT'.
So if there are no rows returned, additional clause is added to resulting sql:
YOUR_DQL_ROOT_COMPONENT_NAME.id IS NULL. Or if there are several rows returned,
the additional clause will look like YOUR_DQL_ROOT_COMPONENT_NAME.id IN (2, 4, 98). Just try it yourself.
And The Last Few Things
Don't use queries with a lot of joins. Don't use joins at all if you start a new project!
Database will execute better a lot of small queries by primary key or other index,
than one big query with joins, espesially if there is great amount of data in joined tables.
And the cache will serve much better. Say, there is a query to Category, its Articles and Articles' Comments.
And somebody edits the comment name. That's it -- cache is not valid anymore, database will execute this
query again -- not a primitive one. In case when there are queries only to single component,
changing comment's name will result in the following dql query:
FROM Comment WHERE Comment.id IN (?)
It will be executed way faster than the previous one.
And I should mention that native use of sfCacheTaggingPlugin is more flexible,
because for now schema cache options syntax is limited comparing to DQL. Take some time
to read about tags-based cache principle and consider sfCacheTaggingPlugin using.
Contacts
- @: Vadim Samokhin
<samokhinvadim at gmail dot com>
Vadim Samokhin 