1 <?php

     2 /**

     3  * Zend Framework

     4  *

     5  * LICENSE

     6  *

     7  * This source file is subject to the new BSD license that is bundled

     8  * with this package in the file LICENSE.txt.

     9  * It is also available through the world-wide-web at this URL:

    10  * http://framework.zend.com/license/new-bsd

    11  * If you did not receive a copy of the license and are unable to

    12  * obtain it through the world-wide-web, please send an email

    13  * to license@zend.com so we can send you a copy immediately.

    14  *

    15  * @category   Zend

    16  * @package    Zend_Cache

    17  * @subpackage Zend_Cache_Backend

    18  * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)

    19  * @license    http://framework.zend.com/license/new-bsd     New BSD License

    20  * @version    $Id: Sqlite.php 20096 2010-01-06 02:05:09Z bkarwin $

    21  */

    22 

    23 

    24 /**

    25  * @see Zend_Cache_Backend_Interface

    26  */

    27 require_once 'Zend/Cache/Backend/ExtendedInterface.php';

    28 

    29 /**

    30  * @see Zend_Cache_Backend

    31  */

    32 require_once 'Zend/Cache/Backend.php';

    33 

    34 /**

    35  * @package    Zend_Cache

    36  * @subpackage Zend_Cache_Backend

    37  * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)

    38  * @license    http://framework.zend.com/license/new-bsd     New BSD License

    39  */

    40 class Zend_Cache_Backend_Sqlite extends Zend_Cache_Backend implements Zend_Cache_Backend_ExtendedInterface

    41 {

    42     /**

    43      * Available options

    44      *

    45      * =====> (string) cache_db_complete_path :

    46      * - the complete path (filename included) of the SQLITE database

    47      *

    48      * ====> (int) automatic_vacuum_factor :

    49      * - Disable / Tune the automatic vacuum process

    50      * - The automatic vacuum process defragment the database file (and make it smaller)

    51      *   when a clean() or delete() is called

    52      *     0               => no automatic vacuum

    53      *     1               => systematic vacuum (when delete() or clean() methods are called)

    54      *     x (integer) > 1 => automatic vacuum randomly 1 times on x clean() or delete()

    55      *

    56      * @var array Available options

    57      */

    58     protected $_options = array(

    59         'cache_db_complete_path' => null,

    60         'automatic_vacuum_factor' => 10

    61     );

    62 

    63     /**

    64      * DB ressource

    65      *

    66      * @var mixed $_db

    67      */

    68     private $_db = null;

    69 

    70     /**

    71      * Boolean to store if the structure has benn checked or not

    72      *

    73      * @var boolean $_structureChecked

    74      */

    75     private $_structureChecked = false;

    76 

    77     /**

    78      * Constructor

    79      *

    80      * @param  array $options Associative array of options

    81      * @throws Zend_cache_Exception

    82      * @return void

    83      */

    84     public function __construct(array $options = array())

    85     {

    86         parent::__construct($options);

    87         if ($this->_options['cache_db_complete_path'] === null) {

    88             Zend_Cache::throwException('cache_db_complete_path option has to set');

    89         }

    90         if (!extension_loaded('sqlite')) {

    91             Zend_Cache::throwException("Cannot use SQLite storage because the 'sqlite' extension is not loaded in the current PHP environment");

    92         }

    93         $this->_getConnection();

    94     }

    95 

    96     /**

    97      * Destructor

    98      *

    99      * @return void

   100      */

   101     public function __destruct()

   102     {

   103         @sqlite_close($this->_getConnection());

   104     }

   105 

   106     /**

   107      * Test if a cache is available for the given id and (if yes) return it (false else)

   108      *

   109      * @param  string  $id                     Cache id

   110      * @param  boolean $doNotTestCacheValidity If set to true, the cache validity won't be tested

   111      * @return string|false Cached datas

   112      */

   113     public function load($id, $doNotTestCacheValidity = false)

   114     {

   115         $this->_checkAndBuildStructure();

   116         $sql = "SELECT content FROM cache WHERE id='$id'";

   117         if (!$doNotTestCacheValidity) {

   118             $sql = $sql . " AND (expire=0 OR expire>" . time() . ')';

   119         }

   120         $result = $this->_query($sql);

   121         $row = @sqlite_fetch_array($result);

   122         if ($row) {

   123             return $row['content'];

   124         }

   125         return false;

   126     }

   127 

   128     /**

   129      * Test if a cache is available or not (for the given id)

   130      *

   131      * @param string $id Cache id

   132      * @return mixed|false (a cache is not available) or "last modified" timestamp (int) of the available cache record

   133      */

   134     public function test($id)

   135     {

   136         $this->_checkAndBuildStructure();

   137         $sql = "SELECT lastModified FROM cache WHERE id='$id' AND (expire=0 OR expire>" . time() . ')';

   138         $result = $this->_query($sql);

   139         $row = @sqlite_fetch_array($result);

   140         if ($row) {

   141             return ((int) $row['lastModified']);

   142         }

   143         return false;

   144     }

   145 

   146     /**

   147      * Save some string datas into a cache record

   148      *

   149      * Note : $data is always "string" (serialization is done by the

   150      * core not by the backend)

   151      *

   152      * @param  string $data             Datas to cache

   153      * @param  string $id               Cache id

   154      * @param  array  $tags             Array of strings, the cache record will be tagged by each string entry

   155      * @param  int    $specificLifetime If != false, set a specific lifetime for this cache record (null => infinite lifetime)

   156      * @throws Zend_Cache_Exception

   157      * @return boolean True if no problem

   158      */

   159     public function save($data, $id, $tags = array(), $specificLifetime = false)

   160     {

   161         $this->_checkAndBuildStructure();

   162         $lifetime = $this->getLifetime($specificLifetime);

   163         $data = @sqlite_escape_string($data);

   164         $mktime = time();

   165         if ($lifetime === null) {

   166             $expire = 0;

   167         } else {

   168             $expire = $mktime + $lifetime;

   169         }

   170         $this->_query("DELETE FROM cache WHERE id='$id'");

   171         $sql = "INSERT INTO cache (id, content, lastModified, expire) VALUES ('$id', '$data', $mktime, $expire)";

   172         $res = $this->_query($sql);

   173         if (!$res) {

   174             $this->_log("Zend_Cache_Backend_Sqlite::save() : impossible to store the cache id=$id");

   175             return false;

   176         }

   177         $res = true;

   178         foreach ($tags as $tag) {

   179             $res = $this->_registerTag($id, $tag) && $res;

   180         }

   181         return $res;

   182     }

   183 

   184     /**

   185      * Remove a cache record

   186      *

   187      * @param  string $id Cache id

   188      * @return boolean True if no problem

   189      */

   190     public function remove($id)

   191     {

   192         $this->_checkAndBuildStructure();

   193         $res = $this->_query("SELECT COUNT(*) AS nbr FROM cache WHERE id='$id'");

   194         $result1 = @sqlite_fetch_single($res);

   195         $result2 = $this->_query("DELETE FROM cache WHERE id='$id'");

   196         $result3 = $this->_query("DELETE FROM tag WHERE id='$id'");

   197         $this->_automaticVacuum();

   198         return ($result1 && $result2 && $result3);

   199     }

   200 

   201     /**

   202      * Clean some cache records

   203      *

   204      * Available modes are :

   205      * Zend_Cache::CLEANING_MODE_ALL (default)    => remove all cache entries ($tags is not used)

   206      * Zend_Cache::CLEANING_MODE_OLD              => remove too old cache entries ($tags is not used)

   207      * Zend_Cache::CLEANING_MODE_MATCHING_TAG     => remove cache entries matching all given tags

   208      *                                               ($tags can be an array of strings or a single string)

   209      * Zend_Cache::CLEANING_MODE_NOT_MATCHING_TAG => remove cache entries not {matching one of the given tags}

   210      *                                               ($tags can be an array of strings or a single string)

   211      * Zend_Cache::CLEANING_MODE_MATCHING_ANY_TAG => remove cache entries matching any given tags

   212      *                                               ($tags can be an array of strings or a single string)

   213      *

   214      * @param  string $mode Clean mode

   215      * @param  array  $tags Array of tags

   216      * @return boolean True if no problem

   217      */

   218     public function clean($mode = Zend_Cache::CLEANING_MODE_ALL, $tags = array())

   219     {

   220         $this->_checkAndBuildStructure();

   221         $return = $this->_clean($mode, $tags);

   222         $this->_automaticVacuum();

   223         return $return;

   224     }

   225 

   226     /**

   227      * Return an array of stored cache ids

   228      *

   229      * @return array array of stored cache ids (string)

   230      */

   231     public function getIds()

   232     {

   233         $this->_checkAndBuildStructure();

   234         $res = $this->_query("SELECT id FROM cache WHERE (expire=0 OR expire>" . time() . ")");

   235         $result = array();

   236         while ($id = @sqlite_fetch_single($res)) {

   237             $result[] = $id;

   238         }

   239         return $result;

   240     }

   241 

   242     /**

   243      * Return an array of stored tags

   244      *

   245      * @return array array of stored tags (string)

   246      */

   247     public function getTags()

   248     {

   249         $this->_checkAndBuildStructure();

   250         $res = $this->_query("SELECT DISTINCT(name) AS name FROM tag");

   251         $result = array();

   252         while ($id = @sqlite_fetch_single($res)) {

   253             $result[] = $id;

   254         }

   255         return $result;

   256     }

   257 

   258     /**

   259      * Return an array of stored cache ids which match given tags

   260      *

   261      * In case of multiple tags, a logical AND is made between tags

   262      *

   263      * @param array $tags array of tags

   264      * @return array array of matching cache ids (string)

   265      */

   266     public function getIdsMatchingTags($tags = array())

   267     {

   268         $first = true;

   269         $ids = array();

   270         foreach ($tags as $tag) {

   271             $res = $this->_query("SELECT DISTINCT(id) AS id FROM tag WHERE name='$tag'");

   272             if (!$res) {

   273                 return array();

   274             }

   275             $rows = @sqlite_fetch_all($res, SQLITE_ASSOC);

   276             $ids2 = array();

   277             foreach ($rows as $row) {

   278                 $ids2[] = $row['id'];

   279             }

   280             if ($first) {

   281                 $ids = $ids2;

   282                 $first = false;

   283             } else {

   284                 $ids = array_intersect($ids, $ids2);

   285             }

   286         }

   287         $result = array();

   288         foreach ($ids as $id) {

   289             $result[] = $id;

   290         }

   291         return $result;

   292     }

   293 

   294     /**

   295      * Return an array of stored cache ids which don't match given tags

   296      *

   297      * In case of multiple tags, a logical OR is made between tags

   298      *

   299      * @param array $tags array of tags

   300      * @return array array of not matching cache ids (string)

   301      */

   302     public function getIdsNotMatchingTags($tags = array())

   303     {

   304         $res = $this->_query("SELECT id FROM cache");

   305         $rows = @sqlite_fetch_all($res, SQLITE_ASSOC);

   306         $result = array();

   307         foreach ($rows as $row) {

   308             $id = $row['id'];

   309             $matching = false;

   310             foreach ($tags as $tag) {

   311                 $res = $this->_query("SELECT COUNT(*) AS nbr FROM tag WHERE name='$tag' AND id='$id'");

   312                 if (!$res) {

   313                     return array();

   314                 }

   315                 $nbr = (int) @sqlite_fetch_single($res);

   316                 if ($nbr > 0) {

   317                     $matching = true;

   318                 }

   319             }

   320             if (!$matching) {

   321                 $result[] = $id;

   322             }

   323         }

   324         return $result;

   325     }

   326 

   327     /**

   328      * Return an array of stored cache ids which match any given tags

   329      *

   330      * In case of multiple tags, a logical AND is made between tags

   331      *

   332      * @param array $tags array of tags

   333      * @return array array of any matching cache ids (string)

   334      */

   335     public function getIdsMatchingAnyTags($tags = array())

   336     {

   337         $first = true;

   338         $ids = array();

   339         foreach ($tags as $tag) {

   340             $res = $this->_query("SELECT DISTINCT(id) AS id FROM tag WHERE name='$tag'");

   341             if (!$res) {

   342                 return array();

   343             }

   344             $rows = @sqlite_fetch_all($res, SQLITE_ASSOC);

   345             $ids2 = array();

   346             foreach ($rows as $row) {

   347                 $ids2[] = $row['id'];

   348             }

   349             if ($first) {

   350                 $ids = $ids2;

   351                 $first = false;

   352             } else {

   353                 $ids = array_merge($ids, $ids2);

   354             }

   355         }

   356         $result = array();

   357         foreach ($ids as $id) {

   358             $result[] = $id;

   359         }

   360         return $result;

   361     }

   362 

   363     /**

   364      * Return the filling percentage of the backend storage

   365      *

   366      * @throws Zend_Cache_Exception

   367      * @return int integer between 0 and 100

   368      */

   369     public function getFillingPercentage()

   370     {

   371         $dir = dirname($this->_options['cache_db_complete_path']);

   372         $free = disk_free_space($dir);

   373         $total = disk_total_space($dir);

   374         if ($total == 0) {

   375             Zend_Cache::throwException('can\'t get disk_total_space');

   376         } else {

   377             if ($free >= $total) {

   378                 return 100;

   379             }

   380             return ((int) (100. * ($total - $free) / $total));

   381         }

   382     }

   383 

   384     /**

   385      * Return an array of metadatas for the given cache id

   386      *

   387      * The array must include these keys :

   388      * - expire : the expire timestamp

   389      * - tags : a string array of tags

   390      * - mtime : timestamp of last modification time

   391      *

   392      * @param string $id cache id

   393      * @return array array of metadatas (false if the cache id is not found)

   394      */

   395     public function getMetadatas($id)

   396     {

   397         $tags = array();

   398         $res = $this->_query("SELECT name FROM tag WHERE id='$id'");

   399         if ($res) {

   400             $rows = @sqlite_fetch_all($res, SQLITE_ASSOC);

   401             foreach ($rows as $row) {

   402                 $tags[] = $row['name'];

   403             }

   404         }

   405         $this->_query('CREATE TABLE cache (id TEXT PRIMARY KEY, content BLOB, lastModified INTEGER, expire INTEGER)');

   406         $res = $this->_query("SELECT lastModified,expire FROM cache WHERE id='$id'");

   407         if (!$res) {

   408             return false;

   409         }

   410         $row = @sqlite_fetch_array($res, SQLITE_ASSOC);

   411         return array(

   412             'tags' => $tags,

   413             'mtime' => $row['lastModified'],

   414             'expire' => $row['expire']

   415         );

   416     }

   417 

   418     /**

   419      * Give (if possible) an extra lifetime to the given cache id

   420      *

   421      * @param string $id cache id

   422      * @param int $extraLifetime

   423      * @return boolean true if ok

   424      */

   425     public function touch($id, $extraLifetime)

   426     {

   427         $sql = "SELECT expire FROM cache WHERE id='$id' AND (expire=0 OR expire>" . time() . ')';

   428         $res = $this->_query($sql);

   429         if (!$res) {

   430             return false;

   431         }

   432         $expire = @sqlite_fetch_single($res);

   433         $newExpire = $expire + $extraLifetime;

   434         $res = $this->_query("UPDATE cache SET lastModified=" . time() . ", expire=$newExpire WHERE id='$id'");

   435         if ($res) {

   436             return true;

   437         } else {

   438             return false;

   439         }

   440     }

   441 

   442     /**

   443      * Return an associative array of capabilities (booleans) of the backend

   444      *

   445      * The array must include these keys :

   446      * - automatic_cleaning (is automating cleaning necessary)

   447      * - tags (are tags supported)

   448      * - expired_read (is it possible to read expired cache records

   449      *                 (for doNotTestCacheValidity option for example))

   450      * - priority does the backend deal with priority when saving

   451      * - infinite_lifetime (is infinite lifetime can work with this backend)

   452      * - get_list (is it possible to get the list of cache ids and the complete list of tags)

   453      *

   454      * @return array associative of with capabilities

   455      */

   456     public function getCapabilities()

   457     {

   458         return array(

   459             'automatic_cleaning' => true,

   460             'tags' => true,

   461             'expired_read' => true,

   462             'priority' => false,

   463             'infinite_lifetime' => true,

   464             'get_list' => true

   465         );

   466     }

   467 

   468     /**

   469      * PUBLIC METHOD FOR UNIT TESTING ONLY !

   470      *

   471      * Force a cache record to expire

   472      *

   473      * @param string $id Cache id

   474      */

   475     public function ___expire($id)

   476     {

   477         $time = time() - 1;

   478         $this->_query("UPDATE cache SET lastModified=$time, expire=$time WHERE id='$id'");

   479     }

   480 

   481     /**

   482      * Return the connection resource

   483      *

   484      * If we are not connected, the connection is made

   485      *

   486      * @throws Zend_Cache_Exception

   487      * @return resource Connection resource

   488      */

   489     private function _getConnection()

   490     {

   491         if (is_resource($this->_db)) {

   492             return $this->_db;

   493         } else {

   494             $this->_db = @sqlite_open($this->_options['cache_db_complete_path']);

   495             if (!(is_resource($this->_db))) {

   496                 Zend_Cache::throwException("Impossible to open " . $this->_options['cache_db_complete_path'] . " cache DB file");

   497             }

   498             return $this->_db;

   499         }

   500     }

   501 

   502     /**

   503      * Execute an SQL query silently

   504      *

   505      * @param string $query SQL query

   506      * @return mixed|false query results

   507      */

   508     private function _query($query)

   509     {

   510         $db = $this->_getConnection();

   511         if (is_resource($db)) {

   512             $res = @sqlite_query($db, $query);

   513             if ($res === false) {

   514                 return false;

   515             } else {

   516                 return $res;

   517             }

   518         }

   519         return false;

   520     }

   521 

   522     /**

   523      * Deal with the automatic vacuum process

   524      *

   525      * @return void

   526      */

   527     private function _automaticVacuum()

   528     {

   529         if ($this->_options['automatic_vacuum_factor'] > 0) {

   530             $rand = rand(1, $this->_options['automatic_vacuum_factor']);

   531             if ($rand == 1) {

   532                 $this->_query('VACUUM');

   533                 @sqlite_close($this->_getConnection());

   534             }

   535         }

   536     }

   537 

   538     /**

   539      * Register a cache id with the given tag

   540      *

   541      * @param  string $id  Cache id

   542      * @param  string $tag Tag

   543      * @return boolean True if no problem

   544      */

   545     private function _registerTag($id, $tag) {

   546         $res = $this->_query("DELETE FROM TAG WHERE name='$tag' AND id='$id'");

   547         $res = $this->_query("INSERT INTO tag (name, id) VALUES ('$tag', '$id')");

   548         if (!$res) {

   549             $this->_log("Zend_Cache_Backend_Sqlite::_registerTag() : impossible to register tag=$tag on id=$id");

   550             return false;

   551         }

   552         return true;

   553     }

   554 

   555     /**

   556      * Build the database structure

   557      *

   558      * @return false

   559      */

   560     private function _buildStructure()

   561     {

   562         $this->_query('DROP INDEX tag_id_index');

   563         $this->_query('DROP INDEX tag_name_index');

   564         $this->_query('DROP INDEX cache_id_expire_index');

   565         $this->_query('DROP TABLE version');

   566         $this->_query('DROP TABLE cache');

   567         $this->_query('DROP TABLE tag');

   568         $this->_query('CREATE TABLE version (num INTEGER PRIMARY KEY)');

   569         $this->_query('CREATE TABLE cache (id TEXT PRIMARY KEY, content BLOB, lastModified INTEGER, expire INTEGER)');

   570         $this->_query('CREATE TABLE tag (name TEXT, id TEXT)');

   571         $this->_query('CREATE INDEX tag_id_index ON tag(id)');

   572         $this->_query('CREATE INDEX tag_name_index ON tag(name)');

   573         $this->_query('CREATE INDEX cache_id_expire_index ON cache(id, expire)');

   574         $this->_query('INSERT INTO version (num) VALUES (1)');

   575     }

   576 

   577     /**

   578      * Check if the database structure is ok (with the good version)

   579      *

   580      * @return boolean True if ok

   581      */

   582     private function _checkStructureVersion()

   583     {

   584         $result = $this->_query("SELECT num FROM version");

   585         if (!$result) return false;

   586         $row = @sqlite_fetch_array($result);

   587         if (!$row) {

   588             return false;

   589         }

   590         if (((int) $row['num']) != 1) {

   591             // old cache structure

   592             $this->_log('Zend_Cache_Backend_Sqlite::_checkStructureVersion() : old cache structure version detected => the cache is going to be dropped');

   593             return false;

   594         }

   595         return true;

   596     }

   597 

   598     /**

   599      * Clean some cache records

   600      *

   601      * Available modes are :

   602      * Zend_Cache::CLEANING_MODE_ALL (default)    => remove all cache entries ($tags is not used)

   603      * Zend_Cache::CLEANING_MODE_OLD              => remove too old cache entries ($tags is not used)

   604      * Zend_Cache::CLEANING_MODE_MATCHING_TAG     => remove cache entries matching all given tags

   605      *                                               ($tags can be an array of strings or a single string)

   606      * Zend_Cache::CLEANING_MODE_NOT_MATCHING_TAG => remove cache entries not {matching one of the given tags}

   607      *                                               ($tags can be an array of strings or a single string)

   608      * Zend_Cache::CLEANING_MODE_MATCHING_ANY_TAG => remove cache entries matching any given tags

   609      *                                               ($tags can be an array of strings or a single string)

   610      *

   611      * @param  string $mode Clean mode

   612      * @param  array  $tags Array of tags

   613      * @return boolean True if no problem

   614      */

   615     private function _clean($mode = Zend_Cache::CLEANING_MODE_ALL, $tags = array())

   616     {

   617         switch ($mode) {

   618             case Zend_Cache::CLEANING_MODE_ALL:

   619                 $res1 = $this->_query('DELETE FROM cache');

   620                 $res2 = $this->_query('DELETE FROM tag');

   621                 return $res1 && $res2;

   622                 break;

   623             case Zend_Cache::CLEANING_MODE_OLD:

   624                 $mktime = time();

   625                 $res1 = $this->_query("DELETE FROM tag WHERE id IN (SELECT id FROM cache WHERE expire>0 AND expire<=$mktime)");

   626                 $res2 = $this->_query("DELETE FROM cache WHERE expire>0 AND expire<=$mktime");

   627                 return $res1 && $res2;

   628                 break;

   629             case Zend_Cache::CLEANING_MODE_MATCHING_TAG:

   630                 $ids = $this->getIdsMatchingTags($tags);

   631                 $result = true;

   632                 foreach ($ids as $id) {

   633                     $result = $this->remove($id) && $result;

   634                 }

   635                 return $result;

   636                 break;

   637             case Zend_Cache::CLEANING_MODE_NOT_MATCHING_TAG:

   638                 $ids = $this->getIdsNotMatchingTags($tags);

   639                 $result = true;

   640                 foreach ($ids as $id) {

   641                     $result = $this->remove($id) && $result;

   642                 }

   643                 return $result;

   644                 break;

   645             case Zend_Cache::CLEANING_MODE_MATCHING_ANY_TAG:

   646                 $ids = $this->getIdsMatchingAnyTags($tags);

   647                 $result = true;

   648                 foreach ($ids as $id) {

   649                     $result = $this->remove($id) && $result;

   650                 }

   651                 return $result;

   652                 break;

   653             default:

   654                 break;

   655         }

   656         return false;

   657     }

   658 

   659     /**

   660      * Check if the database structure is ok (with the good version), if no : build it

   661      *

   662      * @throws Zend_Cache_Exception

   663      * @return boolean True if ok

   664      */

   665     private function _checkAndBuildStructure()

   666     {

   667         if (!($this->_structureChecked)) {

   668             if (!$this->_checkStructureVersion()) {

   669                 $this->_buildStructure();

   670                 if (!$this->_checkStructureVersion()) {

   671                     Zend_Cache::throwException("Impossible to build cache structure in " . $this->_options['cache_db_complete_path']);

   672                 }

   673             }

   674             $this->_structureChecked = true;

   675         }

   676         return true;

   677     }

   678 

   679 }