# sfYACPlugin #


## 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 ##

* Installation

        $ ./symfony plugin:install sfYACPlugin

* Upgrading

        $ ./symfony plugin:upgrade sfYACPlugin

## Setup ##
First of all, copy sfCacheTaggingPlugin from sfYACPlugin/plugin directory and place it in
your application's plugins directory. All installation instruction are [here](http://www.symfony-project.org/plugins/sfCacheTaggingPlugin).

## 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.

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.
There are some rules in theie 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 offst 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.

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:

    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]

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 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


This plugin also caches queries with aggregate function 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: ~

## Contacts ##

  * @: Vadim Samokhin `` <samokhinvadim at gmail dot com> ``