1<?php
2/* vim: set expandtab sw=4 ts=4 sts=4: */
3/**
4 * Functionality for the navigation tree
5 *
6 * @package PhpMyAdmin-Navigation
7 */
8namespace PhpMyAdmin\Navigation\Nodes;
9
10use PhpMyAdmin\Relation;
11use PhpMyAdmin\Url;
12use PhpMyAdmin\Util;
13
14/**
15 * Represents a database node in the navigation tree
16 *
17 * @package PhpMyAdmin-Navigation
18 */
19class NodeDatabase extends Node
20{
21    /**
22     * The number of hidden items in this database
23     *
24     * @var int
25     */
26    protected $hiddenCount = 0;
27
28    /**
29     * Initialises the class
30     *
31     * @param string $name     An identifier for the new node
32     * @param int    $type     Type of node, may be one of CONTAINER or OBJECT
33     * @param bool   $is_group Whether this object has been created
34     *                         while grouping nodes
35     */
36    public function __construct($name, $type = Node::OBJECT, $is_group = false)
37    {
38        parent::__construct($name, $type, $is_group);
39        $this->icon = Util::getImage(
40            's_db',
41            __('Database operations')
42        );
43
44        $script_name = Util::getScriptNameForOption(
45            $GLOBALS['cfg']['DefaultTabDatabase'],
46            'database'
47        );
48        $this->links = array(
49            'text'  => $script_name
50                . '?server=' . $GLOBALS['server']
51                . '&amp;db=%1$s',
52            'icon'  => 'db_operations.php?server=' . $GLOBALS['server']
53                . '&amp;db=%1$s&amp;',
54            'title' => __('Structure'),
55        );
56        $this->classes = 'database';
57    }
58
59    /**
60     * Returns the number of children of type $type present inside this container
61     * This method is overridden by the PhpMyAdmin\Navigation\Nodes\NodeDatabase
62     * and PhpMyAdmin\Navigation\Nodes\NodeTable classes
63     *
64     * @param string  $type         The type of item we are looking for
65     *                              ('tables', 'views', etc)
66     * @param string  $searchClause A string used to filter the results of
67     *                              the query
68     * @param boolean $singleItem   Whether to get presence of a single known
69     *                              item or false in none
70     *
71     * @return int
72     */
73    public function getPresence($type = '', $searchClause = '', $singleItem = false)
74    {
75        $retval = 0;
76        switch ($type) {
77        case 'tables':
78            $retval = $this->_getTableCount($searchClause, $singleItem);
79            break;
80        case 'views':
81            $retval = $this->_getViewCount($searchClause, $singleItem);
82            break;
83        case 'procedures':
84            $retval = $this->_getProcedureCount($searchClause, $singleItem);
85            break;
86        case 'functions':
87            $retval = $this->_getFunctionCount($searchClause, $singleItem);
88            break;
89        case 'events':
90            $retval = $this->_getEventCount($searchClause, $singleItem);
91            break;
92        default:
93            break;
94        }
95
96        return $retval;
97    }
98
99    /**
100     * Returns the number of tables or views present inside this database
101     *
102     * @param string  $which        tables|views
103     * @param string  $searchClause A string used to filter the results of
104     *                              the query
105     * @param boolean $singleItem   Whether to get presence of a single known
106     *                              item or false in none
107     *
108     * @return int
109     */
110    private function _getTableOrViewCount($which, $searchClause, $singleItem)
111    {
112        $db = $this->real_name;
113        if ($which == 'tables') {
114            $condition = 'IN';
115        } else {
116            $condition = 'NOT IN';
117        }
118
119        if (! $GLOBALS['cfg']['Server']['DisableIS']) {
120            $db     = $GLOBALS['dbi']->escapeString($db);
121            $query  = "SELECT COUNT(*) ";
122            $query .= "FROM `INFORMATION_SCHEMA`.`TABLES` ";
123            $query .= "WHERE `TABLE_SCHEMA`='$db' ";
124            $query .= "AND `TABLE_TYPE`" . $condition . "('BASE TABLE', 'SYSTEM VERSIONED') ";
125            if (! empty($searchClause)) {
126                $query .= "AND " . $this->_getWhereClauseForSearch(
127                    $searchClause,
128                    $singleItem,
129                    'TABLE_NAME'
130                );
131            }
132            $retval = (int)$GLOBALS['dbi']->fetchValue($query);
133        } else {
134            $query = "SHOW FULL TABLES FROM ";
135            $query .= Util::backquote($db);
136            $query .= " WHERE `Table_type`" . $condition . "('BASE TABLE', 'SYSTEM VERSIONED') ";
137            if (!empty($searchClause)) {
138                $query .= "AND " . $this->_getWhereClauseForSearch(
139                    $searchClause,
140                    $singleItem,
141                    'Tables_in_' . $db
142                );
143            }
144            $retval = $GLOBALS['dbi']->numRows(
145                $GLOBALS['dbi']->tryQuery($query)
146            );
147        }
148
149        return $retval;
150    }
151
152    /**
153     * Returns the number of tables present inside this database
154     *
155     * @param string  $searchClause A string used to filter the results of
156     *                              the query
157     * @param boolean $singleItem   Whether to get presence of a single known
158     *                              item or false in none
159     *
160     * @return int
161     */
162    private function _getTableCount($searchClause, $singleItem)
163    {
164        return $this->_getTableOrViewCount(
165            'tables',
166            $searchClause,
167            $singleItem
168        );
169    }
170
171    /**
172     * Returns the number of views present inside this database
173     *
174     * @param string  $searchClause A string used to filter the results of
175     *                              the query
176     * @param boolean $singleItem   Whether to get presence of a single known
177     *                              item or false in none
178     *
179     * @return int
180     */
181    private function _getViewCount($searchClause, $singleItem)
182    {
183        return $this->_getTableOrViewCount(
184            'views',
185            $searchClause,
186            $singleItem
187        );
188    }
189
190    /**
191     * Returns the number of procedures present inside this database
192     *
193     * @param string  $searchClause A string used to filter the results of
194     *                              the query
195     * @param boolean $singleItem   Whether to get presence of a single known
196     *                              item or false in none
197     *
198     * @return int
199     */
200    private function _getProcedureCount($searchClause, $singleItem)
201    {
202        $db = $this->real_name;
203        if (!$GLOBALS['cfg']['Server']['DisableIS']) {
204            $db = $GLOBALS['dbi']->escapeString($db);
205            $query = "SELECT COUNT(*) ";
206            $query .= "FROM `INFORMATION_SCHEMA`.`ROUTINES` ";
207            $query .= "WHERE `ROUTINE_SCHEMA` "
208                . Util::getCollateForIS() . "='$db'";
209            $query .= "AND `ROUTINE_TYPE`='PROCEDURE' ";
210            if (!empty($searchClause)) {
211                $query .= "AND " . $this->_getWhereClauseForSearch(
212                    $searchClause,
213                    $singleItem,
214                    'ROUTINE_NAME'
215                );
216            }
217            $retval = (int)$GLOBALS['dbi']->fetchValue($query);
218        } else {
219            $db = $GLOBALS['dbi']->escapeString($db);
220            $query = "SHOW PROCEDURE STATUS WHERE `Db`='$db' ";
221            if (!empty($searchClause)) {
222                $query .= "AND " . $this->_getWhereClauseForSearch(
223                    $searchClause,
224                    $singleItem,
225                    'Name'
226                );
227            }
228            $retval = $GLOBALS['dbi']->numRows(
229                $GLOBALS['dbi']->tryQuery($query)
230            );
231        }
232
233        return $retval;
234    }
235
236    /**
237     * Returns the number of functions present inside this database
238     *
239     * @param string  $searchClause A string used to filter the results of
240     *                              the query
241     * @param boolean $singleItem   Whether to get presence of a single known
242     *                              item or false in none
243     *
244     * @return int
245     */
246    private function _getFunctionCount($searchClause, $singleItem)
247    {
248        $db = $this->real_name;
249        if (!$GLOBALS['cfg']['Server']['DisableIS']) {
250            $db = $GLOBALS['dbi']->escapeString($db);
251            $query = "SELECT COUNT(*) ";
252            $query .= "FROM `INFORMATION_SCHEMA`.`ROUTINES` ";
253            $query .= "WHERE `ROUTINE_SCHEMA` "
254                . Util::getCollateForIS() . "='$db' ";
255            $query .= "AND `ROUTINE_TYPE`='FUNCTION' ";
256            if (!empty($searchClause)) {
257                $query .= "AND " . $this->_getWhereClauseForSearch(
258                    $searchClause,
259                    $singleItem,
260                    'ROUTINE_NAME'
261                );
262            }
263            $retval = (int)$GLOBALS['dbi']->fetchValue($query);
264        } else {
265            $db = $GLOBALS['dbi']->escapeString($db);
266            $query = "SHOW FUNCTION STATUS WHERE `Db`='$db' ";
267            if (!empty($searchClause)) {
268                $query .= "AND " . $this->_getWhereClauseForSearch(
269                    $searchClause,
270                    $singleItem,
271                    'Name'
272                );
273            }
274            $retval = $GLOBALS['dbi']->numRows(
275                $GLOBALS['dbi']->tryQuery($query)
276            );
277        }
278
279        return $retval;
280    }
281
282    /**
283     * Returns the number of events present inside this database
284     *
285     * @param string  $searchClause A string used to filter the results of
286     *                              the query
287     * @param boolean $singleItem   Whether to get presence of a single known
288     *                              item or false in none
289     *
290     * @return int
291     */
292    private function _getEventCount($searchClause, $singleItem)
293    {
294        $db = $this->real_name;
295        if (!$GLOBALS['cfg']['Server']['DisableIS']) {
296            $db = $GLOBALS['dbi']->escapeString($db);
297            $query = "SELECT COUNT(*) ";
298            $query .= "FROM `INFORMATION_SCHEMA`.`EVENTS` ";
299            $query .= "WHERE `EVENT_SCHEMA` "
300                . Util::getCollateForIS() . "='$db' ";
301            if (!empty($searchClause)) {
302                $query .= "AND " . $this->_getWhereClauseForSearch(
303                    $searchClause,
304                    $singleItem,
305                    'EVENT_NAME'
306                );
307            }
308            $retval = (int)$GLOBALS['dbi']->fetchValue($query);
309        } else {
310            $db = Util::backquote($db);
311            $query = "SHOW EVENTS FROM $db ";
312            if (!empty($searchClause)) {
313                $query .= "WHERE " . $this->_getWhereClauseForSearch(
314                    $searchClause,
315                    $singleItem,
316                    'Name'
317                );
318            }
319            $retval = $GLOBALS['dbi']->numRows(
320                $GLOBALS['dbi']->tryQuery($query)
321            );
322        }
323
324        return $retval;
325    }
326
327    /**
328     * Returns the WHERE clause for searching inside a database
329     *
330     * @param string  $searchClause A string used to filter the results of the query
331     * @param boolean $singleItem   Whether to get presence of a single known item
332     * @param string  $columnName   Name of the column in the result set to match
333     *
334     * @return string WHERE clause for searching
335     */
336    private function _getWhereClauseForSearch(
337        $searchClause,
338        $singleItem,
339        $columnName
340    ) {
341        $query = '';
342        if ($singleItem) {
343            $query .= Util::backquote($columnName) . " = ";
344            $query .= "'" . $GLOBALS['dbi']->escapeString($searchClause) . "'";
345        } else {
346            $query .= Util::backquote($columnName) . " LIKE ";
347            $query .= "'%" . $GLOBALS['dbi']->escapeString($searchClause)
348                . "%'";
349        }
350
351        return $query;
352    }
353
354    /**
355     * Returns the names of children of type $type present inside this container
356     * This method is overridden by the PhpMyAdmin\Navigation\Nodes\NodeDatabase
357     * and PhpMyAdmin\Navigation\Nodes\NodeTable classes
358     *
359     * @param string $type         The type of item we are looking for
360     *                             ('tables', 'views', etc)
361     * @param int    $pos          The offset of the list within the results
362     * @param string $searchClause A string used to filter the results of the query
363     *
364     * @return array
365     */
366    public function getData($type, $pos, $searchClause = '')
367    {
368        $retval = array();
369        switch ($type) {
370        case 'tables':
371            $retval = $this->_getTables($pos, $searchClause);
372            break;
373        case 'views':
374            $retval = $this->_getViews($pos, $searchClause);
375            break;
376        case 'procedures':
377            $retval = $this->_getProcedures($pos, $searchClause);
378            break;
379        case 'functions':
380            $retval = $this->_getFunctions($pos, $searchClause);
381            break;
382        case 'events':
383            $retval = $this->_getEvents($pos, $searchClause);
384            break;
385        default:
386            break;
387        }
388
389        // Remove hidden items so that they are not displayed in navigation tree
390        $cfgRelation = $this->relation->getRelationsParam();
391        if ($cfgRelation['navwork']) {
392            $hiddenItems = $this->getHiddenItems(substr($type, 0, -1));
393            foreach ($retval as $key => $item) {
394                if (in_array($item, $hiddenItems)) {
395                    unset($retval[$key]);
396                }
397            }
398        }
399
400        return $retval;
401    }
402
403    /**
404     * Return list of hidden items of given type
405     *
406     * @param string $type The type of items we are looking for
407     *                     ('table', 'function', 'group', etc.)
408     *
409     * @return array Array containing hidden items of given type
410     */
411    public function getHiddenItems($type)
412    {
413        $db = $this->real_name;
414        $cfgRelation = $this->relation->getRelationsParam();
415        if (! $cfgRelation['navwork']) {
416            return array();
417        }
418        $navTable = Util::backquote($cfgRelation['db'])
419            . "." . Util::backquote($cfgRelation['navigationhiding']);
420        $sqlQuery = "SELECT `item_name` FROM " . $navTable
421            . " WHERE `username`='" . $cfgRelation['user'] . "'"
422            . " AND `item_type`='" . $type
423            . "'" . " AND `db_name`='" . $GLOBALS['dbi']->escapeString($db)
424            . "'";
425        $result = $this->relation->queryAsControlUser($sqlQuery, false);
426        $hiddenItems = array();
427        if ($result) {
428            while ($row = $GLOBALS['dbi']->fetchArray($result)) {
429                $hiddenItems[] = $row[0];
430            }
431        }
432        $GLOBALS['dbi']->freeResult($result);
433
434        return $hiddenItems;
435    }
436
437    /**
438     * Returns the list of tables or views inside this database
439     *
440     * @param string $which        tables|views
441     * @param int    $pos          The offset of the list within the results
442     * @param string $searchClause A string used to filter the results of the query
443     *
444     * @return array
445     */
446    private function _getTablesOrViews($which, $pos, $searchClause)
447    {
448        if ($which == 'tables') {
449            $condition = 'IN';
450        } else {
451            $condition = 'NOT IN';
452        }
453        $maxItems = $GLOBALS['cfg']['MaxNavigationItems'];
454        $retval   = array();
455        $db       = $this->real_name;
456        if (! $GLOBALS['cfg']['Server']['DisableIS']) {
457            $escdDb = $GLOBALS['dbi']->escapeString($db);
458            $query  = "SELECT `TABLE_NAME` AS `name` ";
459            $query .= "FROM `INFORMATION_SCHEMA`.`TABLES` ";
460            $query .= "WHERE `TABLE_SCHEMA`='$escdDb' ";
461            $query .= "AND `TABLE_TYPE`" . $condition . "('BASE TABLE', 'SYSTEM VERSIONED') ";
462            if (! empty($searchClause)) {
463                $query .= "AND `TABLE_NAME` LIKE '%";
464                $query .= $GLOBALS['dbi']->escapeString($searchClause);
465                $query .= "%'";
466            }
467            $query .= "ORDER BY `TABLE_NAME` ASC ";
468            $query .= "LIMIT " . intval($pos) . ", $maxItems";
469            $retval = $GLOBALS['dbi']->fetchResult($query);
470        } else {
471            $query = " SHOW FULL TABLES FROM ";
472            $query .= Util::backquote($db);
473            $query .= " WHERE `Table_type`" . $condition . "('BASE TABLE', 'SYSTEM VERSIONED') ";
474            if (!empty($searchClause)) {
475                $query .= "AND " . Util::backquote(
476                    "Tables_in_" . $db
477                );
478                $query .= " LIKE '%" . $GLOBALS['dbi']->escapeString(
479                    $searchClause
480                );
481                $query .= "%'";
482            }
483            $handle = $GLOBALS['dbi']->tryQuery($query);
484            if ($handle !== false) {
485                $count = 0;
486                if ($GLOBALS['dbi']->dataSeek($handle, $pos)) {
487                    while ($arr = $GLOBALS['dbi']->fetchArray($handle)) {
488                        if ($count < $maxItems) {
489                            $retval[] = $arr[0];
490                            $count++;
491                        } else {
492                            break;
493                        }
494                    }
495                }
496            }
497        }
498
499        return $retval;
500    }
501
502    /**
503     * Returns the list of tables inside this database
504     *
505     * @param int    $pos          The offset of the list within the results
506     * @param string $searchClause A string used to filter the results of the query
507     *
508     * @return array
509     */
510    private function _getTables($pos, $searchClause)
511    {
512        return $this->_getTablesOrViews('tables', $pos, $searchClause);
513    }
514
515    /**
516     * Returns the list of views inside this database
517     *
518     * @param int    $pos          The offset of the list within the results
519     * @param string $searchClause A string used to filter the results of the query
520     *
521     * @return array
522     */
523    private function _getViews($pos, $searchClause)
524    {
525        return $this->_getTablesOrViews('views', $pos, $searchClause);
526    }
527
528    /**
529     * Returns the list of procedures or functions inside this database
530     *
531     * @param string $routineType  PROCEDURE|FUNCTION
532     * @param int    $pos          The offset of the list within the results
533     * @param string $searchClause A string used to filter the results of the query
534     *
535     * @return array
536     */
537    private function _getRoutines($routineType, $pos, $searchClause)
538    {
539        $maxItems = $GLOBALS['cfg']['MaxNavigationItems'];
540        $retval = array();
541        $db = $this->real_name;
542        if (!$GLOBALS['cfg']['Server']['DisableIS']) {
543            $escdDb = $GLOBALS['dbi']->escapeString($db);
544            $query = "SELECT `ROUTINE_NAME` AS `name` ";
545            $query .= "FROM `INFORMATION_SCHEMA`.`ROUTINES` ";
546            $query .= "WHERE `ROUTINE_SCHEMA` "
547                . Util::getCollateForIS() . "='$escdDb'";
548            $query .= "AND `ROUTINE_TYPE`='" . $routineType . "' ";
549            if (!empty($searchClause)) {
550                $query .= "AND `ROUTINE_NAME` LIKE '%";
551                $query .= $GLOBALS['dbi']->escapeString($searchClause);
552                $query .= "%'";
553            }
554            $query .= "ORDER BY `ROUTINE_NAME` ASC ";
555            $query .= "LIMIT " . intval($pos) . ", $maxItems";
556            $retval = $GLOBALS['dbi']->fetchResult($query);
557        } else {
558            $escdDb = $GLOBALS['dbi']->escapeString($db);
559            $query = "SHOW " . $routineType . " STATUS WHERE `Db`='$escdDb' ";
560            if (!empty($searchClause)) {
561                $query .= "AND `Name` LIKE '%";
562                $query .= $GLOBALS['dbi']->escapeString($searchClause);
563                $query .= "%'";
564            }
565            $handle = $GLOBALS['dbi']->tryQuery($query);
566            if ($handle !== false) {
567                $count = 0;
568                if ($GLOBALS['dbi']->dataSeek($handle, $pos)) {
569                    while ($arr = $GLOBALS['dbi']->fetchArray($handle)) {
570                        if ($count < $maxItems) {
571                            $retval[] = $arr['Name'];
572                            $count++;
573                        } else {
574                            break;
575                        }
576                    }
577                }
578            }
579        }
580
581        return $retval;
582    }
583
584    /**
585     * Returns the list of procedures inside this database
586     *
587     * @param int    $pos          The offset of the list within the results
588     * @param string $searchClause A string used to filter the results of the query
589     *
590     * @return array
591     */
592    private function _getProcedures($pos, $searchClause)
593    {
594        return $this->_getRoutines('PROCEDURE', $pos, $searchClause);
595    }
596
597    /**
598     * Returns the list of functions inside this database
599     *
600     * @param int    $pos          The offset of the list within the results
601     * @param string $searchClause A string used to filter the results of the query
602     *
603     * @return array
604     */
605    private function _getFunctions($pos, $searchClause)
606    {
607        return $this->_getRoutines('FUNCTION', $pos, $searchClause);
608    }
609
610    /**
611     * Returns the list of events inside this database
612     *
613     * @param int    $pos          The offset of the list within the results
614     * @param string $searchClause A string used to filter the results of the query
615     *
616     * @return array
617     */
618    private function _getEvents($pos, $searchClause)
619    {
620        $maxItems = $GLOBALS['cfg']['MaxNavigationItems'];
621        $retval = array();
622        $db = $this->real_name;
623        if (!$GLOBALS['cfg']['Server']['DisableIS']) {
624            $escdDb = $GLOBALS['dbi']->escapeString($db);
625            $query = "SELECT `EVENT_NAME` AS `name` ";
626            $query .= "FROM `INFORMATION_SCHEMA`.`EVENTS` ";
627            $query .= "WHERE `EVENT_SCHEMA` "
628                . Util::getCollateForIS() . "='$escdDb' ";
629            if (!empty($searchClause)) {
630                $query .= "AND `EVENT_NAME` LIKE '%";
631                $query .= $GLOBALS['dbi']->escapeString($searchClause);
632                $query .= "%'";
633            }
634            $query .= "ORDER BY `EVENT_NAME` ASC ";
635            $query .= "LIMIT " . intval($pos) . ", $maxItems";
636            $retval = $GLOBALS['dbi']->fetchResult($query);
637        } else {
638            $escdDb = Util::backquote($db);
639            $query = "SHOW EVENTS FROM $escdDb ";
640            if (!empty($searchClause)) {
641                $query .= "WHERE `Name` LIKE '%";
642                $query .= $GLOBALS['dbi']->escapeString($searchClause);
643                $query .= "%'";
644            }
645            $handle = $GLOBALS['dbi']->tryQuery($query);
646            if ($handle !== false) {
647                $count = 0;
648                if ($GLOBALS['dbi']->dataSeek($handle, $pos)) {
649                    while ($arr = $GLOBALS['dbi']->fetchArray($handle)) {
650                        if ($count < $maxItems) {
651                            $retval[] = $arr['Name'];
652                            $count++;
653                        } else {
654                            break;
655                        }
656                    }
657                }
658            }
659        }
660
661        return $retval;
662    }
663
664    /**
665     * Returns HTML for control buttons displayed infront of a node
666     *
667     * @return String HTML for control buttons
668     */
669    public function getHtmlForControlButtons()
670    {
671        $ret = '';
672        $cfgRelation = $this->relation->getRelationsParam();
673        if ($cfgRelation['navwork']) {
674            if ($this->hiddenCount > 0) {
675                $params = array(
676                    'showUnhideDialog' => true,
677                    'dbName' => $this->real_name,
678                );
679                $ret = '<span class="dbItemControls">'
680                    . '<a href="navigation.php" data-post="'
681                    . Url::getCommon($params, '') . '"'
682                    . ' class="showUnhide ajax">'
683                    . Util::getImage(
684                        'show',
685                        __('Show hidden items')
686                    )
687                    . '</a></span>';
688            }
689        }
690
691        return $ret;
692    }
693
694    /**
695     * Sets the number of hidden items in this database
696     *
697     * @param int $count hidden item count
698     *
699     * @return void
700     */
701    public function setHiddenCount($count)
702    {
703        $this->hiddenCount = $count;
704    }
705
706    /**
707     * Returns the number of hidden items in this database
708     *
709     * @return int hidden item count
710     */
711    public function getHiddenCount()
712    {
713        return $this->hiddenCount;
714    }
715}
716