1 <?php

     2 

     3 /**

     4  * MySQL CacheResource

     5  * CacheResource Implementation based on the Custom API to use

     6  * MySQL as the storage resource for Smarty's output caching.

     7  * Table definition:

     8  * <pre>CREATE TABLE IF NOT EXISTS `output_cache` (

     9  *   `id` CHAR(40) NOT NULL COMMENT 'sha1 hash',

    10  *   `name` VARCHAR(250) NOT NULL,

    11  *   `cache_id` VARCHAR(250) NULL DEFAULT NULL,

    12  *   `compile_id` VARCHAR(250) NULL DEFAULT NULL,

    13  *   `modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    14  *   `content` LONGTEXT NOT NULL,

    15  *   PRIMARY KEY (`id`),

    16  *   INDEX(`name`),

    17  *   INDEX(`cache_id`),

    18  *   INDEX(`compile_id`),

    19  *   INDEX(`modified`)

    20  * ) ENGINE = InnoDB;</pre>

    21  *

    22  * @package CacheResource-examples

    23  * @author  Rodney Rehm

    24  */

    25 class Smarty_CacheResource_Mysql extends Smarty_CacheResource_Custom {

    26     // PDO instance

    27     protected $db;

    28     protected $fetch;

    29     protected $fetchTimestamp;

    30     protected $save;

    31 

    32     public function __construct() {

    33         try {

    34             $this->db = new PDO("mysql:dbname=test;host=127.0.0.1", "smarty");

    35         } catch (PDOException $e) {

    36             throw new SmartyException('Mysql Resource failed: ' . $e->getMessage());

    37         }

    38         $this->fetch = $this->db->prepare('SELECT modified, content FROM output_cache WHERE id = :id');

    39         $this->fetchTimestamp = $this->db->prepare('SELECT modified FROM output_cache WHERE id = :id');

    40         $this->save = $this->db->prepare('REPLACE INTO output_cache (id, name, cache_id, compile_id, content)

    41             VALUES  (:id, :name, :cache_id, :compile_id, :content)');

    42     }

    43 

    44     /**

    45      * fetch cached content and its modification time from data source

    46      *

    47      * @param  string $id unique cache content identifier

    48      * @param  string $name template name

    49      * @param  string $cache_id cache id

    50      * @param  string $compile_id compile id

    51      * @param  string $content cached content

    52      * @param  integer $mtime cache modification timestamp (epoch)

    53      *

    54      * @return void

    55      */

    56     protected function fetch($id, $name, $cache_id, $compile_id, &$content, &$mtime) {

    57         $this->fetch->execute(array('id' => $id));

    58         $row = $this->fetch->fetch();

    59         $this->fetch->closeCursor();

    60         if ($row) {

    61             $content = $row['content'];

    62             $mtime = strtotime($row['modified']);

    63         } else {

    64             $content = null;

    65             $mtime = null;

    66         }

    67     }

    68 

    69     /**

    70      * Fetch cached content's modification timestamp from data source

    71      *

    72      * @note implementing this method is optional. Only implement it if modification times can be accessed faster than loading the complete cached content.

    73      *

    74      * @param  string $id unique cache content identifier

    75      * @param  string $name template name

    76      * @param  string $cache_id cache id

    77      * @param  string $compile_id compile id

    78      *

    79      * @return integer|boolean timestamp (epoch) the template was modified, or false if not found

    80      */

    81     protected function fetchTimestamp($id, $name, $cache_id, $compile_id) {

    82         $this->fetchTimestamp->execute(array('id' => $id));

    83         $mtime = strtotime($this->fetchTimestamp->fetchColumn());

    84         $this->fetchTimestamp->closeCursor();

    85 

    86         return $mtime;

    87     }

    88 

    89     /**

    90      * Save content to cache

    91      *

    92      * @param  string $id unique cache content identifier

    93      * @param  string $name template name

    94      * @param  string $cache_id cache id

    95      * @param  string $compile_id compile id

    96      * @param  integer|null $exp_time seconds till expiration time in seconds or null

    97      * @param  string $content content to cache

    98      *

    99      * @return boolean      success

   100      */

   101     protected function save($id, $name, $cache_id, $compile_id, $exp_time, $content) {

   102         $this->save->execute(array(

   103             'id' => $id,

   104             'name' => $name,

   105             'cache_id' => $cache_id,

   106             'compile_id' => $compile_id,

   107             'content' => $content,

   108         ));

   109 

   110         return !!$this->save->rowCount();

   111     }

   112 

   113     /**

   114      * Delete content from cache

   115      *

   116      * @param  string $name template name

   117      * @param  string $cache_id cache id

   118      * @param  string $compile_id compile id

   119      * @param  integer|null $exp_time seconds till expiration or null

   120      *

   121      * @return integer      number of deleted caches

   122      */

   123     protected function delete($name, $cache_id, $compile_id, $exp_time) {

   124         // delete the whole cache

   125         if ($name === null && $cache_id === null && $compile_id === null && $exp_time === null) {

   126             // returning the number of deleted caches would require a second query to count them

   127             $query = $this->db->query('TRUNCATE TABLE output_cache');

   128 

   129             return -1;

   130         }

   131         // build the filter

   132         $where = array();

   133         // equal test name

   134         if ($name !== null) {

   135             $where[] = 'name = ' . $this->db->quote($name);

   136         }

   137         // equal test compile_id

   138         if ($compile_id !== null) {

   139             $where[] = 'compile_id = ' . $this->db->quote($compile_id);

   140         }

   141         // range test expiration time

   142         if ($exp_time !== null) {

   143             $where[] = 'modified < DATE_SUB(NOW(), INTERVAL ' . intval($exp_time) . ' SECOND)';

   144         }

   145         // equal test cache_id and match sub-groups

   146         if ($cache_id !== null) {

   147             $where[] = '(cache_id = ' . $this->db->quote($cache_id)

   148                 . ' OR cache_id LIKE ' . $this->db->quote($cache_id . '|%') . ')';

   149         }

   150         // run delete query

   151         $query = $this->db->query('DELETE FROM output_cache WHERE ' . join(' AND ', $where));

   152 

   153         return $query->rowCount();

   154     }

   155 }