libraries/Bookmark.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Handles bookmarking SQL queries
   5  *
   6  * @package PhpMyAdmin
   7  */
   8 namespace PMA\libraries;
   9
  10 use PMA\libraries\Util;
  11 use PMA\libraries\DatabaseInterface;
  12
  13 /**
  14  * Handles bookmarking SQL queries
  15  *
  16  * @package PhpMyAdmin
  17  */
  18 class Bookmark
  19 {
  20     /**
  21      * ID of the bookmark
  22      *
  23      * @var int
  24      */
  25     private $_id;
  26     /**
  27      * Database the bookmark belongs to
  28      *
  29      * @var string
  30      */
  31     private $_database;
  32     /**
  33      * The user to whom the bookmark belongs, empty for public bookmarks
  34      *
  35      * @var string
  36      */
  37     private $_user;
  38     /**
  39      * Label of the bookmark
  40      *
  41      * @var string
  42      */
  43     private $_label;
  44     /**
  45      * SQL query that is bookmarked
  46      *
  47      * @var string
  48      */
  49     private $_query;
  50
  51     /**
  52      * Returns the ID of the bookmark
  53      *
  54      * @return int
  55      */
  56     public function getId()
  57     {
  58         return $this->_id;
  59     }
  60
  61     /**
  62      * Returns the database of the bookmark
  63      *
  64      * @return string
  65      */
  66     public function getDatabase()
  67     {
  68         return $this->_database;
  69     }
  70
  71     /**
  72      * Returns the user whom the bookmark belongs to
  73      *
  74      * @return string
  75      */
  76     public function getUser()
  77     {
  78         return $this->_user;
  79     }
  80
  81     /**
  82      * Returns the label of the bookmark
  83      *
  84      * @return string
  85      */
  86     public function getLabel()
  87     {
  88         return $this->_label;
  89     }
  90
  91     /**
  92      * Returns the query
  93      *
  94      * @return string
  95      */
  96     public function getQuery()
  97     {
  98         return $this->_query;
  99     }
 100
 101     /**
 102      * Adds a bookmark
 103      *
 104      * @return boolean whether the INSERT succeeds or not
 105      *
 106      * @access public
 107      *
 108      * @global resource $controllink the controluser db connection handle
 109      */
 110     public function save()
 111     {
 112         global $controllink;
 113
 114         $cfgBookmark = self::getParams();
 115         if (empty($cfgBookmark)) {
 116             return false;
 117         }
 118
 119         $query = "INSERT INTO " . Util::backquote($cfgBookmark['db'])
 120             . "." . Util::backquote($cfgBookmark['table'])
 121             . " (id, dbase, user, query, label) VALUES (NULL, "
 122             . "'" . $GLOBALS['dbi']->escapeString($this->_database) . "', "
 123             . "'" . $GLOBALS['dbi']->escapeString($this->_user) . "', "
 124             . "'" . $GLOBALS['dbi']->escapeString($this->_query) . "', "
 125             . "'" . $GLOBALS['dbi']->escapeString($this->_label) . "')";
 126         return $GLOBALS['dbi']->query($query, $controllink);
 127     }
 128
 129     /**
 130      * Deletes a bookmark
 131      *
 132      * @return bool true if successful
 133      *
 134      * @access public
 135      *
 136      * @global resource $controllink the controluser db connection handle
 137      */
 138     public function delete()
 139     {
 140         global $controllink;
 141
 142         $cfgBookmark = self::getParams();
 143         if (empty($cfgBookmark)) {
 144             return false;
 145         }
 146
 147         $query  = "DELETE FROM " . Util::backquote($cfgBookmark['db'])
 148             . "." . Util::backquote($cfgBookmark['table'])
 149             . " WHERE id = " . $this->_id;
 150         return $GLOBALS['dbi']->tryQuery($query, $controllink);
 151     }
 152
 153     /**
 154      * Returns the number of variables in a bookmark
 155      *
 156      * @return number number of variables
 157      */
 158     public function getVariableCount()
 159     {
 160         $matches = array();
 161         preg_match_all("/\[VARIABLE[0-9]*\]/", $this->_query, $matches, PREG_SET_ORDER);
 162         return count($matches);
 163     }
 164
 165     /**
 166      * Replace the placeholders in the bookmark query with variables
 167      *
 168      * @param  array $variables array of variables
 169      *
 170      * @return string query with variables applied
 171      */
 172     public function applyVariables($variables)
 173     {
 174         // remove comments that encloses a variable placeholder
 175         $query = preg_replace(
 176             '|/\*(.*\[VARIABLE[0-9]*\].*)\*/|imsU',
 177             '${1}',
 178             $this->_query
 179         );
 180         // replace variable placeholders with values
 181         $number_of_variables = $this->getVariableCount();
 182         for ($i = 1; $i <= $number_of_variables; $i++) {
 183             $var = '';
 184             if (! empty($variables[$i])) {
 185                 $var = $GLOBALS['dbi']->escapeString($variables[$i]);
 186             }
 187             $query = str_replace('[VARIABLE' . $i . ']', $var, $query);
 188             // backward compatibility
 189             if ($i == 1) {
 190                 $query = str_replace('[VARIABLE]', $var, $query);
 191             }
 192         }
 193         return $query;
 194     }
 195
 196     /**
 197      * Defines the bookmark parameters for the current user
 198      *
 199      * @return array the bookmark parameters for the current user
 200      * @access  public
 201      */
 202     public static function getParams()
 203     {
 204         static $cfgBookmark = null;
 205
 206         if (null !== $cfgBookmark) {
 207             return $cfgBookmark;
 208         }
 209
 210         $cfgRelation = PMA_getRelationsParam();
 211         if ($cfgRelation['bookmarkwork']) {
 212             $cfgBookmark = array(
 213                 'user'  => $GLOBALS['cfg']['Server']['user'],
 214                 'db'    => $cfgRelation['db'],
 215                 'table' => $cfgRelation['bookmark'],
 216             );
 217         } else {
 218             $cfgBookmark = false;
 219         }
 220
 221         return $cfgBookmark;
 222     }
 223
 224     /**
 225      * Creates a Bookmark object from the parameters
 226      *
 227      * @param array   $bkm_fields the properties of the bookmark to add; here,
 228      *                            $bkm_fields['bkm_sql_query'] is urlencoded
 229      * @param boolean $all_users  whether to make the bookmark available
 230      *                            for all users
 231      *
 232      * @return Bookmark|false
 233      */
 234     public static function createBookmark($bkm_fields, $all_users = false)
 235     {
 236         if (!(isset($bkm_fields['bkm_sql_query'])
 237             && strlen($bkm_fields['bkm_sql_query']) > 0
 238             && isset($bkm_fields['bkm_label'])
 239             && strlen($bkm_fields['bkm_label']) > 0)
 240         ) {
 241             return false;
 242         }
 243
 244         $bookmark = new Bookmark();
 245         $bookmark->_database = $bkm_fields['bkm_database'];
 246         $bookmark->_label = $bkm_fields['bkm_label'];
 247         $bookmark->_query = $bkm_fields['bkm_sql_query'];
 248         $bookmark->_user = $all_users ? '' : $bkm_fields['bkm_user'];
 249
 250         return $bookmark;
 251     }
 252
 253     /**
 254      * Gets the list of bookmarks defined for the current database
 255      *
 256      * @param string|bool $db the current database name or false
 257      *
 258      * @return Bookmark[] the bookmarks list
 259      *
 260      * @access public
 261      *
 262      * @global resource $controllink the controluser db connection handle
 263      */
 264     public static function getList($db = false)
 265     {
 266         global $controllink;
 267
 268         $cfgBookmark = self::getParams();
 269         if (empty($cfgBookmark)) {
 270             return array();
 271         }
 272
 273         $query = "SELECT * FROM " . Util::backquote($cfgBookmark['db'])
 274             . "." . Util::backquote($cfgBookmark['table'])
 275             . " WHERE `user` = ''"
 276             . " OR `user` = '" . $GLOBALS['dbi']->escapeString($cfgBookmark['user']) . "'";
 277         if ($db !== false) {
 278             $query .= " AND dbase = '" . $GLOBALS['dbi']->escapeString($db) . "'";
 279         }
 280         $query .= " ORDER BY label";
 281
 282         $result = $GLOBALS['dbi']->fetchResult(
 283             $query,
 284             null,
 285             null,
 286             $controllink,
 287             DatabaseInterface::QUERY_STORE
 288         );
 289
 290         if (! empty($result)) {
 291             $bookmarks = array();
 292             foreach ($result as $row) {
 293                 $bookmark = new Bookmark();
 294                 $bookmark->_id = $row['id'];
 295                 $bookmark->_database = $row['dbase'];
 296                 $bookmark->_user = $row['user'];
 297                 $bookmark->_label = $row['label'];
 298                 $bookmark->_query = $row['query'];
 299                 $bookmarks[] = $bookmark;
 300             }
 301
 302             return $bookmarks;
 303         }
 304
 305         return array();
 306     }
 307
 308     /**
 309      * Retrieve a specific bookmark
 310      *
 311      * @param string  $db                  the current database name
 312      * @param mixed   $id                  an identifier of the bookmark to get
 313      * @param string  $id_field            which field to look up the identifier
 314      * @param boolean $action_bookmark_all true: get all bookmarks regardless
 315      *                                     of the owning user
 316      * @param boolean $exact_user_match    whether to ignore bookmarks with no user
 317      *
 318      * @return Bookmark the bookmark
 319      *
 320      * @access  public
 321      *
 322      * @global  resource $controllink the controluser db connection handle
 323      *
 324      */
 325     public static function get($db, $id, $id_field = 'id',
 326         $action_bookmark_all = false, $exact_user_match = false
 327     ) {
 328         global $controllink;
 329
 330         $cfgBookmark = self::getParams();
 331         if (empty($cfgBookmark)) {
 332             return null;
 333         }
 334
 335         $query = "SELECT * FROM " . Util::backquote($cfgBookmark['db'])
 336             . "." . Util::backquote($cfgBookmark['table'])
 337             . " WHERE dbase = '" . $GLOBALS['dbi']->escapeString($db) . "'";
 338         if (! $action_bookmark_all) {
 339             $query .= " AND (user = '"
 340                 . $GLOBALS['dbi']->escapeString($cfgBookmark['user']) . "'";
 341             if (! $exact_user_match) {
 342                 $query .= " OR user = ''";
 343             }
 344             $query .= ")";
 345         }
 346         $query .= " AND " . Util::backquote($id_field)
 347             . " = " . $GLOBALS['dbi']->escapeString($id) . " LIMIT 1";
 348
 349         $result = $GLOBALS['dbi']->fetchSingleRow($query, 'ASSOC', $controllink);
 350         if (! empty($result)) {
 351             $bookmark = new Bookmark();
 352             $bookmark->_id = $result['id'];
 353             $bookmark->_database = $result['dbase'];
 354             $bookmark->_user = $result['user'];
 355             $bookmark->_label = $result['label'];
 356             $bookmark->_query = $result['query'];
 357             return $bookmark;
 358         }
 359
 360         return null;
 361     }
 362 }