wp-includes/SimplePie/Cache/MySQL.php

   1 <?php
   2 /**
   3  * SimplePie
   4  *
   5  * A PHP-Based RSS and Atom Feed Framework.
   6  * Takes the hard work out of managing a complete RSS/Atom solution.
   7  *
   8  * Copyright (c) 2004-2012, Ryan Parman, Geoffrey Sneddon, Ryan McCue, and contributors
   9  * All rights reserved.
  10  *
  11  * Redistribution and use in source and binary forms, with or without modification, are
  12  * permitted provided that the following conditions are met:
  13  *
  14  *      * Redistributions of source code must retain the above copyright notice, this list of
  15  *        conditions and the following disclaimer.
  16  *
  17  *      * Redistributions in binary form must reproduce the above copyright notice, this list
  18  *        of conditions and the following disclaimer in the documentation and/or other materials
  19  *        provided with the distribution.
  20  *
  21  *      * Neither the name of the SimplePie Team nor the names of its contributors may be used
  22  *        to endorse or promote products derived from this software without specific prior
  23  *        written permission.
  24  *
  25  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS
  26  * OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
  27  * AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS
  28  * AND CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
  29  * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
  30  * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  31  * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
  32  * OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
  33  * POSSIBILITY OF SUCH DAMAGE.
  34  *
  35  * @package SimplePie
  36  * @version 1.3.1
  37  * @copyright 2004-2012 Ryan Parman, Geoffrey Sneddon, Ryan McCue
  38  * @author Ryan Parman
  39  * @author Geoffrey Sneddon
  40  * @author Ryan McCue
  41  * @link http://simplepie.org/ SimplePie
  42  * @license http://www.opensource.org/licenses/bsd-license.php BSD License
  43  */
  44
  45 /**
  46  * Caches data to a MySQL database
  47  *
  48  * Registered for URLs with the "mysql" protocol
  49  *
  50  * For example, `mysql://root:password@localhost:3306/mydb?prefix=sp_` will
  51  * connect to the `mydb` database on `localhost` on port 3306, with the user
  52  * `root` and the password `password`. All tables will be prefixed with `sp_`
  53  *
  54  * @package SimplePie
  55  * @subpackage Caching
  56  */
  57 class SimplePie_Cache_MySQL extends SimplePie_Cache_DB
  58 {
  59         /**
  60          * PDO instance
  61          *
  62          * @var PDO
  63          */
  64         protected $mysql;
  65
  66         /**
  67          * Options
  68          *
  69          * @var array
  70          */
  71         protected $options;
  72
  73         /**
  74          * Cache ID
  75          *
  76          * @var string
  77          */
  78         protected $id;
  79
  80         /**
  81          * Create a new cache object
  82          *
  83          * @param string $location Location string (from SimplePie::$cache_location)
  84          * @param string $name Unique ID for the cache
  85          * @param string $type Either TYPE_FEED for SimplePie data, or TYPE_IMAGE for image data
  86          */
  87         public function __construct($location, $name, $type)
  88         {
  89                 $this->options = array(
  90                         'user' => null,
  91                         'pass' => null,
  92                         'host' => '127.0.0.1',
  93                         'port' => '3306',
  94                         'path' => '',
  95                         'extras' => array(
  96                                 'prefix' => '',
  97                         ),
  98                 );
  99                 $this->options = array_merge_recursive($this->options, SimplePie_Cache::parse_URL($location));
 100
 101                 // Path is prefixed with a "/"
 102                 $this->options['dbname'] = substr($this->options['path'], 1);
 103
 104                 try
 105                 {
 106                         $this->mysql = new PDO("mysql:dbname={$this->options['dbname']};host={$this->options['host']};port={$this->options['port']}", $this->options['user'], $this->options['pass'], array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
 107                 }
 108                 catch (PDOException $e)
 109                 {
 110                         $this->mysql = null;
 111                         return;
 112                 }
 113
 114                 $this->id = $name . $type;
 115
 116                 if (!$query = $this->mysql->query('SHOW TABLES'))
 117                 {
 118                         $this->mysql = null;
 119                         return;
 120                 }
 121
 122                 $db = array();
 123                 while ($row = $query->fetchColumn())
 124                 {
 125                         $db[] = $row;
 126                 }
 127
 128                 if (!in_array($this->options['extras']['prefix'] . 'cache_data', $db))
 129                 {
 130                         $query = $this->mysql->exec('CREATE TABLE `' . $this->options['extras']['prefix'] . 'cache_data` (`id` TEXT CHARACTER SET utf8 NOT NULL, `items` SMALLINT NOT NULL DEFAULT 0, `data` BLOB NOT NULL, `mtime` INT UNSIGNED NOT NULL, UNIQUE (`id`(125)))');
 131                         if ($query === false)
 132                         {
 133                                 $this->mysql = null;
 134                         }
 135                 }
 136
 137                 if (!in_array($this->options['extras']['prefix'] . 'items', $db))
 138                 {
 139                         $query = $this->mysql->exec('CREATE TABLE `' . $this->options['extras']['prefix'] . 'items` (`feed_id` TEXT CHARACTER SET utf8 NOT NULL, `id` TEXT CHARACTER SET utf8 NOT NULL, `data` TEXT CHARACTER SET utf8 NOT NULL, `posted` INT UNSIGNED NOT NULL, INDEX `feed_id` (`feed_id`(125)))');
 140                         if ($query === false)
 141                         {
 142                                 $this->mysql = null;
 143                         }
 144                 }
 145         }
 146
 147         /**
 148          * Save data to the cache
 149          *
 150          * @param array|SimplePie $data Data to store in the cache. If passed a SimplePie object, only cache the $data property
 151          * @return bool Successfulness
 152          */
 153         public function save($data)
 154         {
 155                 if ($this->mysql === null)
 156                 {
 157                         return false;
 158                 }
 159
 160                 if ($data instanceof SimplePie)
 161                 {
 162                         $data = clone $data;
 163
 164                         $prepared = self::prepare_simplepie_object_for_cache($data);
 165
 166                         $query = $this->mysql->prepare('SELECT COUNT(*) FROM `' . $this->options['extras']['prefix'] . 'cache_data` WHERE `id` = :feed');
 167                         $query->bindValue(':feed', $this->id);
 168                         if ($query->execute())
 169                         {
 170                                 if ($query->fetchColumn() > 0)
 171                                 {
 172                                         $items = count($prepared[1]);
 173                                         if ($items)
 174                                         {
 175                                                 $sql = 'UPDATE `' . $this->options['extras']['prefix'] . 'cache_data` SET `items` = :items, `data` = :data, `mtime` = :time WHERE `id` = :feed';
 176                                                 $query = $this->mysql->prepare($sql);
 177                                                 $query->bindValue(':items', $items);
 178                                         }
 179                                         else
 180                                         {
 181                                                 $sql = 'UPDATE `' . $this->options['extras']['prefix'] . 'cache_data` SET `data` = :data, `mtime` = :time WHERE `id` = :feed';
 182                                                 $query = $this->mysql->prepare($sql);
 183                                         }
 184
 185                                         $query->bindValue(':data', $prepared[0]);
 186                                         $query->bindValue(':time', time());
 187                                         $query->bindValue(':feed', $this->id);
 188                                         if (!$query->execute())
 189                                         {
 190                                                 return false;
 191                                         }
 192                                 }
 193                                 else
 194                                 {
 195                                         $query = $this->mysql->prepare('INSERT INTO `' . $this->options['extras']['prefix'] . 'cache_data` (`id`, `items`, `data`, `mtime`) VALUES(:feed, :count, :data, :time)');
 196                                         $query->bindValue(':feed', $this->id);
 197                                         $query->bindValue(':count', count($prepared[1]));
 198                                         $query->bindValue(':data', $prepared[0]);
 199                                         $query->bindValue(':time', time());
 200                                         if (!$query->execute())
 201                                         {
 202                                                 return false;
 203                                         }
 204                                 }
 205
 206                                 $ids = array_keys($prepared[1]);
 207                                 if (!empty($ids))
 208                                 {
 209                                         foreach ($ids as $id)
 210                                         {
 211                                                 $database_ids[] = $this->mysql->quote($id);
 212                                         }
 213
 214                                         $query = $this->mysql->prepare('SELECT `id` FROM `' . $this->options['extras']['prefix'] . 'items` WHERE `id` = ' . implode(' OR `id` = ', $database_ids) . ' AND `feed_id` = :feed');
 215                                         $query->bindValue(':feed', $this->id);
 216
 217                                         if ($query->execute())
 218                                         {
 219                                                 $existing_ids = array();
 220                                                 while ($row = $query->fetchColumn())
 221                                                 {
 222                                                         $existing_ids[] = $row;
 223                                                 }
 224
 225                                                 $new_ids = array_diff($ids, $existing_ids);
 226
 227                                                 foreach ($new_ids as $new_id)
 228                                                 {
 229                                                         if (!($date = $prepared[1][$new_id]->get_date('U')))
 230                                                         {
 231                                                                 $date = time();
 232                                                         }
 233
 234                                                         $query = $this->mysql->prepare('INSERT INTO `' . $this->options['extras']['prefix'] . 'items` (`feed_id`, `id`, `data`, `posted`) VALUES(:feed, :id, :data, :date)');
 235                                                         $query->bindValue(':feed', $this->id);
 236                                                         $query->bindValue(':id', $new_id);
 237                                                         $query->bindValue(':data', serialize($prepared[1][$new_id]->data));
 238                                                         $query->bindValue(':date', $date);
 239                                                         if (!$query->execute())
 240                                                         {
 241                                                                 return false;
 242                                                         }
 243                                                 }
 244                                                 return true;
 245                                         }
 246                                 }
 247                                 else
 248                                 {
 249                                         return true;
 250                                 }
 251                         }
 252                 }
 253                 else
 254                 {
 255                         $query = $this->mysql->prepare('SELECT `id` FROM `' . $this->options['extras']['prefix'] . 'cache_data` WHERE `id` = :feed');
 256                         $query->bindValue(':feed', $this->id);
 257                         if ($query->execute())
 258                         {
 259                                 if ($query->rowCount() > 0)
 260                                 {
 261                                         $query = $this->mysql->prepare('UPDATE `' . $this->options['extras']['prefix'] . 'cache_data` SET `items` = 0, `data` = :data, `mtime` = :time WHERE `id` = :feed');
 262                                         $query->bindValue(':data', serialize($data));
 263                                         $query->bindValue(':time', time());
 264                                         $query->bindValue(':feed', $this->id);
 265                                         if ($this->execute())
 266                                         {
 267                                                 return true;
 268                                         }
 269                                 }
 270                                 else
 271                                 {
 272                                         $query = $this->mysql->prepare('INSERT INTO `' . $this->options['extras']['prefix'] . 'cache_data` (`id`, `items`, `data`, `mtime`) VALUES(:id, 0, :data, :time)');
 273                                         $query->bindValue(':id', $this->id);
 274                                         $query->bindValue(':data', serialize($data));
 275                                         $query->bindValue(':time', time());
 276                                         if ($query->execute())
 277                                         {
 278                                                 return true;
 279                                         }
 280                                 }
 281                         }
 282                 }
 283                 return false;
 284         }
 285
 286         /**
 287          * Retrieve the data saved to the cache
 288          *
 289          * @return array Data for SimplePie::$data
 290          */
 291         public function load()
 292         {
 293                 if ($this->mysql === null)
 294                 {
 295                         return false;
 296                 }
 297
 298                 $query = $this->mysql->prepare('SELECT `items`, `data` FROM `' . $this->options['extras']['prefix'] . 'cache_data` WHERE `id` = :id');
 299                 $query->bindValue(':id', $this->id);
 300                 if ($query->execute() && ($row = $query->fetch()))
 301                 {
 302                         $data = unserialize($row[1]);
 303
 304                         if (isset($this->options['items'][0]))
 305                         {
 306                                 $items = (int) $this->options['items'][0];
 307                         }
 308                         else
 309                         {
 310                                 $items = (int) $row[0];
 311                         }
 312
 313                         if ($items !== 0)
 314                         {
 315                                 if (isset($data['child'][SIMPLEPIE_NAMESPACE_ATOM_10]['feed'][0]))
 316                                 {
 317                                         $feed =& $data['child'][SIMPLEPIE_NAMESPACE_ATOM_10]['feed'][0];
 318                                 }
 319                                 elseif (isset($data['child'][SIMPLEPIE_NAMESPACE_ATOM_03]['feed'][0]))
 320                                 {
 321                                         $feed =& $data['child'][SIMPLEPIE_NAMESPACE_ATOM_03]['feed'][0];
 322                                 }
 323                                 elseif (isset($data['child'][SIMPLEPIE_NAMESPACE_RDF]['RDF'][0]))
 324                                 {
 325                                         $feed =& $data['child'][SIMPLEPIE_NAMESPACE_RDF]['RDF'][0];
 326                                 }
 327                                 elseif (isset($data['child'][SIMPLEPIE_NAMESPACE_RSS_20]['rss'][0]))
 328                                 {
 329                                         $feed =& $data['child'][SIMPLEPIE_NAMESPACE_RSS_20]['rss'][0];
 330                                 }
 331                                 else
 332                                 {
 333                                         $feed = null;
 334                                 }
 335
 336                                 if ($feed !== null)
 337                                 {
 338                                         $sql = 'SELECT `data` FROM `' . $this->options['extras']['prefix'] . 'items` WHERE `feed_id` = :feed ORDER BY `posted` DESC';
 339                                         if ($items > 0)
 340                                         {
 341                                                 $sql .= ' LIMIT ' . $items;
 342                                         }
 343
 344                                         $query = $this->mysql->prepare($sql);
 345                                         $query->bindValue(':feed', $this->id);
 346                                         if ($query->execute())
 347                                         {
 348                                                 while ($row = $query->fetchColumn())
 349                                                 {
 350                                                         $feed['child'][SIMPLEPIE_NAMESPACE_ATOM_10]['entry'][] = unserialize($row);
 351                                                 }
 352                                         }
 353                                         else
 354                                         {
 355                                                 return false;
 356                                         }
 357                                 }
 358                         }
 359                         return $data;
 360                 }
 361                 return false;
 362         }
 363
 364         /**
 365          * Retrieve the last modified time for the cache
 366          *
 367          * @return int Timestamp
 368          */
 369         public function mtime()
 370         {
 371                 if ($this->mysql === null)
 372                 {
 373                         return false;
 374                 }
 375
 376                 $query = $this->mysql->prepare('SELECT `mtime` FROM `' . $this->options['extras']['prefix'] . 'cache_data` WHERE `id` = :id');
 377                 $query->bindValue(':id', $this->id);
 378                 if ($query->execute() && ($time = $query->fetchColumn()))
 379                 {
 380                         return $time;
 381                 }
 382                 else
 383                 {
 384                         return false;
 385                 }
 386         }
 387
 388         /**
 389          * Set the last modified time to the current time
 390          *
 391          * @return bool Success status
 392          */
 393         public function touch()
 394         {
 395                 if ($this->mysql === null)
 396                 {
 397                         return false;
 398                 }
 399
 400                 $query = $this->mysql->prepare('UPDATE `' . $this->options['extras']['prefix'] . 'cache_data` SET `mtime` = :time WHERE `id` = :id');
 401                 $query->bindValue(':time', time());
 402                 $query->bindValue(':id', $this->id);
 403                 if ($query->execute() && $query->rowCount() > 0)
 404                 {
 405                         return true;
 406                 }
 407                 else
 408                 {
 409                         return false;
 410                 }
 411         }
 412
 413         /**
 414          * Remove the cache
 415          *
 416          * @return bool Success status
 417          */
 418         public function unlink()
 419         {
 420                 if ($this->mysql === null)
 421                 {
 422                         return false;
 423                 }
 424
 425                 $query = $this->mysql->prepare('DELETE FROM `' . $this->options['extras']['prefix'] . 'cache_data` WHERE `id` = :id');
 426                 $query->bindValue(':id', $this->id);
 427                 $query2 = $this->mysql->prepare('DELETE FROM `' . $this->options['extras']['prefix'] . 'items` WHERE `feed_id` = :id');
 428                 $query2->bindValue(':id', $this->id);
 429                 if ($query->execute() && $query2->execute())
 430                 {
 431                         return true;
 432                 }
 433                 else
 434                 {
 435                         return false;
 436                 }
 437         }
 438 }