1<?php
2/*
3 * vim:set softtabstop=4 shiftwidth=4 expandtab:
4 *
5 * LICENSE: GNU Affero General Public License, version 3 (AGPL-3.0-or-later)
6 * Copyright 2001 - 2020 Ampache.org
7 *
8 * This program is free software: you can redistribute it and/or modify
9 * it under the terms of the GNU Affero General Public License as published by
10 * the Free Software Foundation, either version 3 of the License, or
11 * (at your option) any later version.
12 *
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
16 * GNU Affero General Public License for more details.
17 *
18 * You should have received a copy of the GNU Affero General Public License
19 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
20 *
21 */
22
23declare(strict_types=0);
24
25namespace Ampache\Module\System;
26
27use Ampache\Config\AmpConfig;
28use PDO;
29use PDOException;
30use PDOStatement;
31
32/**
33 * This is the database abstraction class
34 * It duplicates the functionality of mysql_???
35 * with a few exceptions, the row and assoc will always
36 * return an array, simplifying checking on the far end
37 * it will also auto-connect as needed, and has a default
38 * database simplifying queries in most cases.
39 */
40class Dba
41{
42    public static $stats = array('query' => 0);
43
44    private static $_sql;
45    private static $_error;
46
47    /**
48     * query
49     * @param string $sql
50     * @param array $params
51     * @return PDOStatement|boolean
52     */
53    public static function query($sql, $params = array())
54    {
55        // json_encode throws errors about UTF-8 cleanliness, which we don't care about here.
56        debug_event(__CLASS__, $sql . ' ' . json_encode($params), 6);
57
58        // Be aggressive, be strong, be dumb
59        $tries = 0;
60        do {
61            $stmt = self::_query($sql, $params);
62        } while (!$stmt && $tries++ < 3);
63
64        return $stmt;
65    }
66
67    /**
68     * _query
69     * @param string $sql
70     * @param array $params
71     * @return PDOStatement|boolean
72     */
73    private static function _query($sql, $params)
74    {
75        $dbh = self::dbh();
76        if (!$dbh) {
77            debug_event(__CLASS__, 'Error: failed to get database handle', 1);
78
79            return false;
80        }
81
82        // Run the query
83        if (!empty($params)) {
84            $stmt = $dbh->prepare($sql);
85            $stmt->execute($params);
86        } else {
87            $stmt = $dbh->query($sql);
88        }
89
90        // Save the query, to make debug easier
91        self::$_sql = $sql;
92        self::$stats['query']++;
93
94        if (!$stmt) {
95            self::$_error = json_encode($dbh->errorInfo());
96            debug_event(__CLASS__, 'Error_query SQL: ' . $sql, 5);
97            debug_event(__CLASS__, 'Error_query MSG: ' . json_encode($dbh->errorInfo()), 1);
98            self::disconnect();
99        } else {
100            if ($stmt->errorCode() && $stmt->errorCode() != '00000') {
101                self::$_error = json_encode($stmt->errorInfo());
102                debug_event(__CLASS__, 'Error_query SQL: ' . $sql, 5);
103                debug_event(__CLASS__, 'Error_query MSG: ' . json_encode($stmt->errorInfo()), 1);
104                self::finish($stmt);
105                self::disconnect();
106
107                return false;
108            }
109        }
110
111        return $stmt;
112    }
113
114    /**
115     * read
116     * @param string $sql
117     * @param array $params
118     * @return PDOStatement|boolean
119     */
120    public static function read($sql, $params = array())
121    {
122        return self::query($sql, $params);
123    }
124
125    /**
126     * write
127     * @param string $sql
128     * @param array $params
129     * @return PDOStatement|boolean
130     */
131    public static function write($sql, $params = array())
132    {
133        return self::query($sql, $params);
134    }
135
136    /**
137     * escape
138     *
139     * This runs an escape on a variable so that it can be safely inserted
140     * into the sql
141     * @param $var
142     * @return string
143     */
144    public static function escape($var)
145    {
146        $dbh = self::dbh();
147        if (!$dbh) {
148            debug_event(__CLASS__, 'Wrong dbh.', 1);
149
150            return '';
151        }
152        $out_var = $dbh->quote(filter_var($var, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES));
153        // This is slightly less ugly than it was, but still ugly
154        return substr($out_var, 1, -1);
155    }
156
157    /**
158     * fetch_assoc
159     *
160     * This emulates the mysql_fetch_assoc.
161     * We force it to always return an array, albeit an empty one
162     * The optional finish parameter affects whether we automatically clean
163     * up the result set after the last row is read.
164     * @param $resource
165     * @param boolean $finish
166     * @return array
167     */
168    public static function fetch_assoc($resource, $finish = true)
169    {
170        if (!$resource) {
171            return array();
172        }
173
174        $result = $resource->fetch(PDO::FETCH_ASSOC);
175
176        if (!$result) {
177            if ($finish) {
178                self::finish($resource);
179            }
180
181            return array();
182        }
183
184        return $result;
185    }
186
187    /**
188     * fetch_row
189     *
190     * This emulates the mysql_fetch_row
191     * we force it to always return an array, albeit an empty one
192     * The optional finish parameter affects whether we automatically clean
193     * up the result set after the last row is read.
194     * @param $resource
195     * @param boolean $finish
196     * @return array
197     */
198    public static function fetch_row($resource, $finish = true)
199    {
200        if (!$resource) {
201            return array();
202        }
203
204        $result = $resource->fetch(PDO::FETCH_NUM);
205
206        if (!$result) {
207            if ($finish) {
208                self::finish($resource);
209            }
210
211            return array();
212        }
213
214        return $result;
215    }
216
217    /**
218     * @param $resource
219     * @param string $class
220     * @param boolean $finish
221     * @return array
222     */
223    public static function fetch_object($resource, $class = 'stdClass', $finish = true)
224    {
225        if (!$resource) {
226            return array();
227        }
228
229        $result = $resource->fetchObject($class);
230
231        if (!$result) {
232            if ($finish) {
233                self::finish($resource);
234            }
235
236            return array();
237        }
238
239        return $result;
240    }
241
242    /**
243     * num_rows
244     *
245     * This emulates the mysql_num_rows function which is really
246     * just a count of rows returned by our select statement, this
247     * doesn't work for updates or inserts.
248     * @param $resource
249     * @return integer
250     */
251    public static function num_rows($resource)
252    {
253        if ($resource) {
254            $result = $resource->rowCount();
255            if ($result) {
256                return $result;
257            }
258        }
259
260        return 0;
261    }
262
263    /**
264     * finish
265     *
266     * This closes a result handle and clears the memory associated with it
267     * @param $resource
268     */
269    public static function finish($resource)
270    {
271        if ($resource) {
272            $resource->closeCursor();
273        }
274    }
275
276    /**
277     * affected_rows
278     *
279     * This emulates the mysql_affected_rows function
280     * @param $resource
281     * @return integer
282     */
283    public static function affected_rows($resource)
284    {
285        if ($resource) {
286            $result = $resource->rowCount();
287            if ($result) {
288                return $result;
289            }
290        }
291
292        return 0;
293    }
294
295    /**
296     * _connect
297     *
298     * This connects to the database, used by the DBH function
299     * @return PDO|null
300     */
301    private static function _connect()
302    {
303        $username = AmpConfig::get('database_username');
304        $hostname = AmpConfig::get('database_hostname', '');
305        $password = AmpConfig::get('database_password');
306        $port     = AmpConfig::get('database_port');
307
308        if ($hostname === '') {
309            return null;
310        }
311
312        // Build the data source name
313        if (strpos($hostname, '/') === 0) {
314            $dsn = 'mysql:unix_socket=' . $hostname;
315        } else {
316            $dsn = 'mysql:host=' . $hostname;
317        }
318        if ($port) {
319            $dsn .= ';port=' . (int)($port);
320        }
321
322        try {
323            debug_event(__CLASS__, 'Database connection...', 5);
324            $dbh = new PDO($dsn, $username, $password);
325        } catch (PDOException $error) {
326            self::$_error = $error->getMessage();
327            debug_event(__CLASS__, 'Connection failed: ' . $error->getMessage(), 1);
328
329            return null;
330        }
331
332        return $dbh;
333    }
334
335    /**
336     * _setup_dbh
337     * @param null|PDO $dbh
338     * @param string $database
339     * @return boolean
340     */
341    private static function _setup_dbh($dbh, $database)
342    {
343        if (!$dbh) {
344            return false;
345        }
346
347        $charset = self::translate_to_mysqlcharset(AmpConfig::get('site_charset'));
348        $charset = $charset['charset'];
349        if ($dbh->exec('SET NAMES ' . $charset) === false) {
350            debug_event(__CLASS__, 'Unable to set connection charset to ' . $charset, 1);
351        }
352
353        if ($dbh->exec('USE `' . $database . '`') === false) {
354            self::$_error = json_encode($dbh->errorInfo());
355            debug_event(__CLASS__, 'Unable to select database ' . $database . ': ' . json_encode($dbh->errorInfo()),
356                1);
357        }
358
359        if (AmpConfig::get('sql_profiling')) {
360            $dbh->exec('SET profiling=1');
361            $dbh->exec('SET profiling_history_size=50');
362            $dbh->exec('SET query_cache_type=0');
363        }
364
365        return true;
366    }
367
368    /**
369     * check_database
370     *
371     * Make sure that we can connect to the database
372     * @return boolean
373     */
374    public static function check_database()
375    {
376        $dbh = self::_connect();
377
378        if (!$dbh || $dbh->errorCode()) {
379            if ($dbh) {
380                self::$_error = json_encode($dbh->errorInfo());
381            }
382
383            return false;
384        }
385
386        return true;
387    }
388
389    /**
390     * check_database_inserted
391     *
392     * Checks to make sure that you have inserted the database
393     * and that the user you are using has access to it.
394     * @return boolean
395     */
396    public static function check_database_inserted()
397    {
398        $sql        = "DESCRIBE session";
399        $db_results = self::read($sql);
400
401        if (!$db_results) {
402            return false;
403        }
404
405        // Make sure the table is there
406        if (self::num_rows($db_results) < 1) {
407            return false;
408        }
409
410        return true;
411    }
412
413    /**
414     * show_profile
415     *
416     * This function is used for debug, helps with profiling
417     */
418    public static function show_profile()
419    {
420        if (AmpConfig::get('sql_profiling')) {
421            print '<br/>Profiling data: <br/>';
422            $res = self::read('SHOW PROFILES');
423            print '<table>';
424            while ($row = self::fetch_row($res)) {
425                print '<tr><td>' . implode('</td><td>', $row) . '</td></tr>';
426            }
427            print '</table>';
428        }
429    }
430
431    /**
432     * dbh
433     *
434     * This is called by the class to return the database handle
435     * for the specified database, if none is found it connects
436     * @param string $database
437     * @return mixed|PDO|null
438     */
439    public static function dbh($database = '')
440    {
441        if (!$database) {
442            $database = AmpConfig::get('database_name');
443        }
444
445        // Assign the Handle name that we are going to store
446        $handle = 'dbh_' . $database;
447
448        if (!is_object(AmpConfig::get($handle))) {
449            $dbh = self::_connect();
450            self::_setup_dbh($dbh, $database);
451            AmpConfig::set($handle, $dbh, true);
452
453            return $dbh;
454        } else {
455            return AmpConfig::get($handle);
456        }
457    }
458
459    /**
460     * disconnect
461     *
462     * This nukes the dbh connection, this isn't used very often...
463     * @param string $database
464     * @return boolean
465     */
466    public static function disconnect($database = '')
467    {
468        if (!$database) {
469            $database = AmpConfig::get('database_name');
470        }
471
472        $handle = 'dbh_' . $database;
473
474        // Nuke it
475        debug_event(__CLASS__, 'Database disconnection.', 6);
476        AmpConfig::set($handle, null, true);
477
478        return true;
479    }
480
481    /**
482     * insert_id
483     * @return string|null
484     */
485    public static function insert_id()
486    {
487        $dbh = self::dbh();
488        if ($dbh) {
489            return $dbh->lastInsertId();
490        }
491
492        return null;
493    }
494
495    /**
496     * error
497     * this returns the error of the db
498     */
499    public static function error()
500    {
501        return self::$_error;
502    }
503
504    /**
505     * translate_to_mysqlcharset
506     *
507     * This translates the specified charset to a mysql charset.
508     * @param $charset
509     * @return array
510     */
511    public static function translate_to_mysqlcharset($charset)
512    {
513        // Translate real charset names into fancy MySQL land names
514        switch (strtoupper((string)$charset)) {
515            case 'CP1250':
516            case 'WINDOWS-1250':
517                $target_charset   = AmpConfig::get('database_charset', 'cp1250');
518                $target_collation = AmpConfig::get('database_collation', 'cp1250_general_ci');
519                break;
520            case 'ISO-8859':
521            case 'ISO-8859-2':
522                $target_charset   = AmpConfig::get('database_charset', 'latin2');
523                $target_collation = AmpConfig::get('database_collation', 'latin2_general_ci');
524                break;
525            case 'ISO-8859-1':
526            case 'CP1252':
527            case 'WINDOWS-1252':
528                $target_charset   = AmpConfig::get('database_charset', 'latin1');
529                $target_collation = AmpConfig::get('database_collation', 'latin1_general_ci');
530                break;
531            case 'EUC-KR':
532                $target_charset   = AmpConfig::get('database_charset', 'euckr');
533                $target_collation = AmpConfig::get('database_collation', 'euckr_korean_ci');
534                break;
535            case 'CP932':
536                $target_charset   = AmpConfig::get('database_charset', 'sjis');
537                $target_collation = AmpConfig::get('database_collation', 'sjis_japanese_ci');
538                break;
539            case 'KOI8-U':
540                $target_charset   = AmpConfig::get('database_charset', 'koi8u');
541                $target_collation = AmpConfig::get('database_collation', 'koi8u_general_ci');
542                break;
543            case 'KOI8-R':
544                $target_charset   = AmpConfig::get('database_charset', 'koi8r');
545                $target_collation = AmpConfig::get('database_collation', 'koi8r_general_ci');
546                break;
547            case 'UTF-8':
548            default:
549                $target_charset   = AmpConfig::get('database_charset', 'utf8mb4');
550                $target_collation = AmpConfig::get('database_collation', 'utf8mb4_unicode_ci');
551                break;
552        }
553
554        return array(
555            'charset' => $target_charset,
556            'collation' => $target_collation
557        );
558    }
559
560    /**
561     * optimize_tables
562     *
563     * This runs an optimize on the tables and updates the stats to improve
564     * join speed.
565     * This can be slow, but is a good idea to do from time to time. We do
566     * it in case the dba isn't doing it... which we're going to assume they
567     * aren't.
568     */
569    public static function optimize_tables()
570    {
571        $sql        = "SHOW TABLES";
572        $db_results = self::read($sql);
573
574        while ($row = self::fetch_row($db_results)) {
575            $sql = "OPTIMIZE TABLE `" . $row[0] . "`";
576            self::write($sql);
577
578            $sql = "ANALYZE TABLE `" . $row[0] . "`";
579            self::write($sql);
580        }
581    }
582}
583