library/smarty/demo/plugins/cacheresource.pdo.php
changeset 0 4869aea77e21
equal deleted inserted replaced
-1:000000000000 0:4869aea77e21
       
     1 <?php
       
     2 
       
     3 /**
       
     4  * PDO Cache Handler
       
     5  * Allows you to store Smarty Cache files into your db.
       
     6  * Example table :
       
     7  * CREATE TABLE `smarty_cache` (
       
     8  * `id` char(40) NOT NULL COMMENT 'sha1 hash',
       
     9  * `name` varchar(250) NOT NULL,
       
    10  * `cache_id` varchar(250) DEFAULT NULL,
       
    11  * `compile_id` varchar(250) DEFAULT NULL,
       
    12  * `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
       
    13  * `expire` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
       
    14  * `content` mediumblob NOT NULL,
       
    15  * PRIMARY KEY (`id`),
       
    16  * KEY `name` (`name`),
       
    17  * KEY `cache_id` (`cache_id`),
       
    18  * KEY `compile_id` (`compile_id`),
       
    19  * KEY `modified` (`modified`),
       
    20  * KEY `expire` (`expire`)
       
    21  * ) ENGINE=InnoDB
       
    22  * Example usage :
       
    23  *      $cnx    =   new PDO("mysql:host=localhost;dbname=mydb", "username", "password");
       
    24  *      $smarty->setCachingType('pdo');
       
    25  *      $smarty->loadPlugin('Smarty_CacheResource_Pdo');
       
    26  *      $smarty->registerCacheResource('pdo', new Smarty_CacheResource_Pdo($cnx, 'smarty_cache'));
       
    27  *
       
    28  * @author Beno!t POLASZEK - 2014
       
    29  */
       
    30 class Smarty_CacheResource_Pdo extends Smarty_CacheResource_Custom {
       
    31 
       
    32     protected $fetchStatements = Array('default' => 'SELECT %2$s
       
    33                                                                                     FROM %1$s 
       
    34                                                                                     WHERE 1 
       
    35                                                                                     AND id          = :id 
       
    36                                                                                     AND cache_id    IS NULL 
       
    37                                                                                     AND compile_id  IS NULL',
       
    38 
       
    39         'withCacheId' => 'SELECT %2$s
       
    40                                                                                 FROM %1$s 
       
    41                                                                                 WHERE 1 
       
    42                                                                                 AND id          = :id 
       
    43                                                                                 AND cache_id    = :cache_id 
       
    44                                                                                 AND compile_id  IS NULL',
       
    45 
       
    46         'withCompileId' => 'SELECT %2$s
       
    47                                                                                 FROM %1$s 
       
    48                                                                                 WHERE 1 
       
    49                                                                                 AND id          = :id 
       
    50                                                                                 AND compile_id  = :compile_id 
       
    51                                                                                 AND cache_id    IS NULL',
       
    52 
       
    53         'withCacheIdAndCompileId' => 'SELECT %2$s
       
    54                                                                                 FROM %1$s 
       
    55                                                                                 WHERE 1 
       
    56                                                                                 AND id          = :id 
       
    57                                                                                 AND cache_id    = :cache_id 
       
    58                                                                                 AND compile_id  = :compile_id');
       
    59     protected $insertStatement = 'INSERT INTO %s
       
    60 
       
    61                                                 SET id          =   :id, 
       
    62                                                     name        =   :name, 
       
    63                                                     cache_id    =   :cache_id, 
       
    64                                                     compile_id  =   :compile_id, 
       
    65                                                     modified    =   CURRENT_TIMESTAMP, 
       
    66                                                     expire      =   DATE_ADD(CURRENT_TIMESTAMP, INTERVAL :expire SECOND), 
       
    67                                                     content     =   :content 
       
    68 
       
    69                                                 ON DUPLICATE KEY UPDATE 
       
    70                                                     name        =   :name, 
       
    71                                                     cache_id    =   :cache_id, 
       
    72                                                     compile_id  =   :compile_id, 
       
    73                                                     modified    =   CURRENT_TIMESTAMP, 
       
    74                                                     expire      =   DATE_ADD(CURRENT_TIMESTAMP, INTERVAL :expire SECOND), 
       
    75                                                     content     =   :content';
       
    76 
       
    77     protected $deleteStatement = 'DELETE FROM %1$s WHERE %2$s';
       
    78     protected $truncateStatement = 'TRUNCATE TABLE %s';
       
    79 
       
    80     protected $fetchColumns = 'modified, content';
       
    81     protected $fetchTimestampColumns = 'modified';
       
    82 
       
    83     protected $pdo, $table, $database;
       
    84 
       
    85     /* 
       
    86      * Constructor 
       
    87      * 
       
    88      * @param PDO $pdo PDO : active connection 
       
    89      * @param string $table : table (or view) name 
       
    90      * @param string $database : optionnal - if table is located in another db 
       
    91      */
       
    92     public function __construct(PDO $pdo, $table, $database = null) {
       
    93 
       
    94         if (is_null($table)) {
       
    95             throw new SmartyException("Table name for caching can't be null");
       
    96         }
       
    97 
       
    98         $this->pdo = $pdo;
       
    99         $this->table = $table;
       
   100         $this->database = $database;
       
   101 
       
   102         $this->fillStatementsWithTableName();
       
   103     }
       
   104 
       
   105     /* 
       
   106      * Fills the table name into the statements. 
       
   107      * 
       
   108      * @return Current Instance 
       
   109      * @access protected 
       
   110      */
       
   111     protected function fillStatementsWithTableName() {
       
   112 
       
   113         foreach ($this->fetchStatements AS &$statement) {
       
   114             $statement = sprintf($statement, $this->getTableName(), '%s');
       
   115         }
       
   116 
       
   117         $this->insertStatement = sprintf($this->insertStatement, $this->getTableName());
       
   118         $this->deleteStatement = sprintf($this->deleteStatement, $this->getTableName(), '%s');
       
   119         $this->truncateStatement = sprintf($this->truncateStatement, $this->getTableName());
       
   120 
       
   121         return $this;
       
   122     }
       
   123 
       
   124     /* 
       
   125      * Gets the fetch statement, depending on what you specify 
       
   126      * 
       
   127      * @param string        $columns : the column(s) name(s) you want to retrieve from the database 
       
   128      * @param string        $id unique cache content identifier 
       
   129      * @param string|null   $cache_id cache id 
       
   130      * @param string|null   $compile_id compile id 
       
   131      * @access protected 
       
   132      */
       
   133     protected function getFetchStatement($columns, $id, $cache_id = null, $compile_id = null) {
       
   134 
       
   135         if (!is_null($cache_id) && !is_null($compile_id)) {
       
   136             $query = $this->fetchStatements['withCacheIdAndCompileId'] AND $args = Array('id' => $id, 'cache_id' => $cache_id, 'compile_id' => $compile_id);
       
   137         } elseif (is_null($cache_id) && !is_null($compile_id)) {
       
   138             $query = $this->fetchStatements['withCompileId'] AND $args = Array('id' => $id, 'compile_id' => $compile_id);
       
   139         } elseif (!is_null($cache_id) && is_null($compile_id)) {
       
   140             $query = $this->fetchStatements['withCacheId'] AND $args = Array('id' => $id, 'cache_id' => $cache_id);
       
   141         } else {
       
   142             $query = $this->fetchStatements['default'] AND $args = Array('id' => $id);
       
   143         }
       
   144 
       
   145         $query = sprintf($query, $columns);
       
   146 
       
   147         $stmt = $this->pdo->prepare($query);
       
   148 
       
   149         foreach ($args AS $key => $value) {
       
   150             $stmt->bindValue($key, $value);
       
   151         }
       
   152 
       
   153         return $stmt;
       
   154     }
       
   155 
       
   156     /**
       
   157      * fetch cached content and its modification time from data source
       
   158      *
       
   159      * @param  string $id unique cache content identifier
       
   160      * @param  string $name template name
       
   161      * @param  string|null $cache_id cache id
       
   162      * @param  string|null $compile_id compile id
       
   163      * @param  string $content cached content
       
   164      * @param  integer $mtime cache modification timestamp (epoch)
       
   165      *
       
   166      * @return void
       
   167      * @access protected
       
   168      */
       
   169     protected function fetch($id, $name, $cache_id = null, $compile_id = null, &$content, &$mtime) {
       
   170 
       
   171         $stmt = $this->getFetchStatement($this->fetchColumns, $id, $cache_id, $compile_id);
       
   172         $stmt->execute();
       
   173         $row = $stmt->fetch();
       
   174         $stmt->closeCursor();
       
   175 
       
   176         if ($row) {
       
   177             $content = $this->outputContent($row['content']);
       
   178             $mtime = strtotime($row['modified']);
       
   179         } else {
       
   180             $content = null;
       
   181             $mtime = null;
       
   182         }
       
   183     }
       
   184 
       
   185     /**
       
   186      * Fetch cached content's modification timestamp from data source
       
   187      * {@internal implementing this method is optional.
       
   188      *  Only implement it if modification times can be accessed faster than loading the complete cached content.}}
       
   189      *
       
   190      * @param  string $id unique cache content identifier
       
   191      * @param  string $name template name
       
   192      * @param  string|null $cache_id cache id
       
   193      * @param  string|null $compile_id compile id
       
   194      *
       
   195      * @return integer|boolean timestamp (epoch) the template was modified, or false if not found
       
   196      * @access protected
       
   197      */
       
   198     //    protected function fetchTimestamp($id, $name, $cache_id = null, $compile_id = null) {
       
   199     //        $stmt       =   $this->getFetchStatement($this->fetchTimestampColumns, $id, $cache_id, $compile_id);
       
   200     //        $stmt       ->  execute();
       
   201     //        $mtime      =   strtotime($stmt->fetchColumn());
       
   202     //        $stmt       ->  closeCursor();
       
   203     //        return $mtime;
       
   204     //    }
       
   205 
       
   206     /**
       
   207      * Save content to cache
       
   208      *
       
   209      * @param string $id unique cache content identifier
       
   210      * @param string $name template name
       
   211      * @param string|null $cache_id cache id
       
   212      * @param string|null $compile_id compile id
       
   213      * @param integer|null $exp_time seconds till expiration time in seconds or null
       
   214      * @param string $content content to cache
       
   215      *
       
   216      * @return boolean success
       
   217      * @access protected
       
   218      */
       
   219     protected function save($id, $name, $cache_id = null, $compile_id = null, $exp_time, $content) {
       
   220 
       
   221         $stmt = $this->pdo->prepare($this->insertStatement);
       
   222 
       
   223         $stmt->bindValue('id', $id);
       
   224         $stmt->bindValue('name', $name);
       
   225         $stmt->bindValue('cache_id', $cache_id, (is_null($cache_id)) ? PDO::PARAM_NULL : PDO::PARAM_STR);
       
   226         $stmt->bindValue('compile_id', $compile_id, (is_null($compile_id)) ? PDO::PARAM_NULL : PDO::PARAM_STR);
       
   227         $stmt->bindValue('expire', (int)$exp_time, PDO::PARAM_INT);
       
   228         $stmt->bindValue('content', $this->inputContent($content));
       
   229         $stmt->execute();
       
   230 
       
   231         return !!$stmt->rowCount();
       
   232     }
       
   233 
       
   234     /* 
       
   235      * Encodes the content before saving to database 
       
   236      * 
       
   237      * @param string $content 
       
   238      * @return string $content 
       
   239      * @access protected 
       
   240      */
       
   241     protected function inputContent($content) {
       
   242         return $content;
       
   243     }
       
   244 
       
   245     /* 
       
   246      * Decodes the content before saving to database 
       
   247      * 
       
   248      * @param string $content 
       
   249      * @return string $content 
       
   250      * @access protected 
       
   251      */
       
   252     protected function outputContent($content) {
       
   253         return $content;
       
   254     }
       
   255 
       
   256     /**
       
   257      * Delete content from cache
       
   258      *
       
   259      * @param string|null $name template name
       
   260      * @param string|null $cache_id cache id
       
   261      * @param string|null $compile_id compile id
       
   262      * @param  integer|null|-1 $exp_time seconds till expiration or null
       
   263      *
       
   264      * @return integer number of deleted caches
       
   265      * @access protected
       
   266      */
       
   267     protected function delete($name = null, $cache_id = null, $compile_id = null, $exp_time = null) {
       
   268 
       
   269         // delete the whole cache 
       
   270         if ($name === null && $cache_id === null && $compile_id === null && $exp_time === null) {
       
   271             // returning the number of deleted caches would require a second query to count them 
       
   272             $this->pdo->query($this->truncateStatement);
       
   273             return -1;
       
   274         }
       
   275         // build the filter 
       
   276         $where = array();
       
   277         // equal test name 
       
   278         if ($name !== null) {
       
   279             $where[] = 'name = ' . $this->pdo->quote($name);
       
   280         }
       
   281         // equal test cache_id and match sub-groups 
       
   282         if ($cache_id !== null) {
       
   283             $where[] = '(cache_id = ' . $this->pdo->quote($cache_id)
       
   284                 . ' OR cache_id LIKE ' . $this->pdo->quote($cache_id . '|%') . ')';
       
   285         }
       
   286         // equal test compile_id 
       
   287         if ($compile_id !== null) {
       
   288             $where[] = 'compile_id = ' . $this->pdo->quote($compile_id);
       
   289         }
       
   290         // for clearing expired caches 
       
   291         if ($exp_time === Smarty::CLEAR_EXPIRED) {
       
   292             $where[] = 'expire < CURRENT_TIMESTAMP';
       
   293         } // range test expiration time
       
   294         elseif ($exp_time !== null) {
       
   295             $where[] = 'modified < DATE_SUB(NOW(), INTERVAL ' . intval($exp_time) . ' SECOND)';
       
   296         }
       
   297         // run delete query 
       
   298         $query = $this->pdo->query(sprintf($this->deleteStatement, join(' AND ', $where)));
       
   299         return $query->rowCount();
       
   300     }
       
   301 
       
   302     /**
       
   303      * Gets the formatted table name
       
   304      *
       
   305      * @return string
       
   306      * @access protected
       
   307      */
       
   308     protected function getTableName() {
       
   309         return (is_null($this->database)) ? "`{$this->table}`" : "`{$this->database}`.`{$this->table}`";
       
   310     }
       
   311 }
       
   312