sfYACPlugin - 0.0.5

Cache plugin with simple schema configuration

You are currently browsing
the website for symfony 1

Visit the Symfony2 website


« Back to the Plugins Home

Signin


Forgot your password?
Create an account

Tools

Stats

advanced search
Information Readme Releases Changelog Contribute
Show source

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.

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>