Database   F
last analyzed

Complexity

Total Complexity 122

Size/Duplication

Total Lines 908
Duplicated Lines 0 %

Test Coverage

Coverage 89.87%

Importance

Changes 0
Metric Value
eloc 441
dl 0
loc 908
ccs 417
cts 464
cp 0.8987
rs 2
c 0
b 0
f 0
wmc 122

27 Methods

Rating   Name   Duplication   Size   Complexity  
B create() 0 53 10
A _sanitizeClob() 0 6 2
B _upgradeDatabase() 0 70 8
A setValue() 0 14 3
A getAllPastes() 0 5 1
A _createConfigTable() 0 13 2
C __construct() 0 71 13
A _getPrimaryKeyClauses() 0 13 3
A _createCommentTable() 0 34 2
A _getConfig() 0 11 3
A _exec() 0 16 5
B _getTableQuery() 0 45 8
A delete() 0 9 1
B _select() 0 23 7
A _getAttachmentType() 0 9 3
A _getVersionedKeys() 0 6 2
A createComment() 0 33 5
F read() 0 55 14
A _getExpiredPastes() 0 9 2
A _createPasteTable() 0 17 1
A _getDataType() 0 9 3
B getValue() 0 33 8
B readComments() 0 33 9
A existsComment() 0 10 2
A _sanitizeIdentifier() 0 3 1
A _getMetaType() 0 7 2
A exists() 0 11 2

How to fix   Complexity   

Complex Class

Complex classes like Database often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.

Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.

While breaking up the class, it is a good idea to analyze how other classes use Database, and based on these observations, apply Extract Interface, too.

1
<?php
2
/**
3
 * PrivateBin
4
 *
5
 * a zero-knowledge paste bin
6
 *
7
 * @link      https://github.com/PrivateBin/PrivateBin
8
 * @copyright 2012 Sébastien SAUVAGE (sebsauvage.net)
9
 * @license   https://www.opensource.org/licenses/zlib-license.php The zlib/libpng License
10
 * @version   1.7.1
11
 */
12
13
namespace PrivateBin\Data;
14
15
use Exception;
16
use PDO;
17
use PDOException;
18
use PrivateBin\Controller;
19
use PrivateBin\Json;
20
21
/**
22
 * Database
23
 *
24
 * Model for database access, implemented as a singleton.
25
 */
26
class Database extends AbstractData
27
{
28
    /**
29
     * instance of database connection
30
     *
31
     * @access private
32
     * @var PDO
33
     */
34
    private $_db;
35
36
    /**
37
     * table prefix
38
     *
39
     * @access private
40
     * @var string
41
     */
42
    private $_prefix = '';
43
44
    /**
45
     * database type
46
     *
47
     * @access private
48
     * @var string
49
     */
50
    private $_type = '';
51
52
    /**
53
     * instantiates a new Database data backend
54
     *
55
     * @access public
56
     * @param  array $options
57
     * @throws Exception
58
     */
59 78
    public function __construct(array $options)
60
    {
61
        // set table prefix if given
62 78
        if (array_key_exists('tbl', $options)) {
63 55
            $this->_prefix = $options['tbl'];
64
        }
65
66
        // initialize the db connection with new options
67
        if (
68 78
            array_key_exists('dsn', $options) &&
69 78
            array_key_exists('usr', $options) &&
70 78
            array_key_exists('pwd', $options) &&
71 78
            array_key_exists('opt', $options)
72
        ) {
73
            // set default options
74 78
            $options['opt'][PDO::ATTR_ERRMODE]          = PDO::ERRMODE_EXCEPTION;
75 78
            $options['opt'][PDO::ATTR_EMULATE_PREPARES] = false;
76 78
            $options['opt'][PDO::ATTR_PERSISTENT]       = true;
77 78
            $db_tables_exist                            = true;
78
79
            // setup type and dabase connection
80 78
            $this->_type = strtolower(
81 78
                substr($options['dsn'], 0, strpos($options['dsn'], ':'))
82 78
            );
83
            // MySQL uses backticks to quote identifiers by default,
84
            // tell it to expect ANSI SQL double quotes
85 78
            if ($this->_type === 'mysql' && defined('PDO::MYSQL_ATTR_INIT_COMMAND')) {
86 1
                $options['opt'][PDO::MYSQL_ATTR_INIT_COMMAND] = "SET SESSION sql_mode='ANSI_QUOTES'";
87
            }
88 78
            $tableQuery = $this->_getTableQuery($this->_type);
89 78
            $this->_db  = new PDO(
90 78
                $options['dsn'],
91 78
                $options['usr'],
92 78
                $options['pwd'],
93 78
                $options['opt']
94 78
            );
95
96
            // check if the database contains the required tables
97 78
            $tables = $this->_db->query($tableQuery)->fetchAll(PDO::FETCH_COLUMN, 0);
98
99
            // create paste table if necessary
100 78
            if (!in_array($this->_sanitizeIdentifier('paste'), $tables)) {
101 44
                $this->_createPasteTable();
102 44
                $db_tables_exist = false;
103
            }
104
105
            // create comment table if necessary
106 78
            if (!in_array($this->_sanitizeIdentifier('comment'), $tables)) {
107 44
                $this->_createCommentTable();
108 44
                $db_tables_exist = false;
109
            }
110
111
            // create config table if necessary
112 78
            $db_version = Controller::VERSION;
113 78
            if (!in_array($this->_sanitizeIdentifier('config'), $tables)) {
114 45
                $this->_createConfigTable();
115
                // if we only needed to create the config table, the DB is older then 0.22
116 45
                if ($db_tables_exist) {
117 45
                    $db_version = '0.21';
118
                }
119
            } else {
120 71
                $db_version = $this->_getConfig('VERSION');
121
            }
122
123
            // update database structure if necessary
124 78
            if (version_compare($db_version, Controller::VERSION, '<')) {
125 78
                $this->_upgradeDatabase($db_version);
126
            }
127
        } else {
128 4
            throw new Exception(
129 4
                'Missing configuration for key dsn, usr, pwd or opt in the section model_options, please check your configuration file', 6
130 4
            );
131
        }
132
    }
133
134
    /**
135
     * Create a paste.
136
     *
137
     * @access public
138
     * @param  string $pasteid
139
     * @param  array  $paste
140
     * @return bool
141
     */
142 39
    public function create($pasteid, array $paste)
143
    {
144 39
        $expire_date      = 0;
145 39
        $opendiscussion   = $burnafterreading = false;
146 39
        $attachment       = $attachmentname   = null;
147 39
        $meta             = $paste['meta'];
148 39
        $isVersion1       = array_key_exists('data', $paste);
149 39
        list($createdKey) = $this->_getVersionedKeys($isVersion1 ? 1 : 2);
150 39
        $created          = (int) $meta[$createdKey];
151 39
        unset($meta[$createdKey], $paste['meta']);
152 39
        if (array_key_exists('expire_date', $meta)) {
153 24
            $expire_date = (int) $meta['expire_date'];
154 24
            unset($meta['expire_date']);
155
        }
156 39
        if (array_key_exists('opendiscussion', $meta)) {
157 2
            $opendiscussion = $meta['opendiscussion'];
158 2
            unset($meta['opendiscussion']);
159
        }
160 39
        if (array_key_exists('burnafterreading', $meta)) {
161 1
            $burnafterreading = $meta['burnafterreading'];
162 1
            unset($meta['burnafterreading']);
163
        }
164 39
        if ($isVersion1) {
165 2
            if (array_key_exists('attachment', $meta)) {
166 1
                $attachment = $meta['attachment'];
167 1
                unset($meta['attachment']);
168
            }
169 2
            if (array_key_exists('attachmentname', $meta)) {
170 1
                $attachmentname = $meta['attachmentname'];
171 2
                unset($meta['attachmentname']);
172
            }
173
        } else {
174 37
            $opendiscussion   = $paste['adata'][2];
175 37
            $burnafterreading = $paste['adata'][3];
176
        }
177
        try {
178 39
            return $this->_exec(
179 39
                'INSERT INTO "' . $this->_sanitizeIdentifier('paste') .
180 39
                '" VALUES(?,?,?,?,?,?,?,?,?)',
181 39
                array(
182 39
                    $pasteid,
183 39
                    $isVersion1 ? $paste['data'] : Json::encode($paste),
184 39
                    $created,
185 39
                    $expire_date,
186 39
                    (int) $opendiscussion,
187 39
                    (int) $burnafterreading,
188 39
                    Json::encode($meta),
189 39
                    $attachment,
190 39
                    $attachmentname,
191 39
                )
192 39
            );
193 3
        } catch (Exception $e) {
194 3
            return false;
195
        }
196
    }
197
198
    /**
199
     * Read a paste.
200
     *
201
     * @access public
202
     * @param  string $pasteid
203
     * @return array|false
204
     */
205 30
    public function read($pasteid)
206
    {
207
        try {
208 30
            $row = $this->_select(
209 30
                'SELECT * FROM "' . $this->_sanitizeIdentifier('paste') .
210 30
                '" WHERE "dataid" = ?', array($pasteid), true
211 30
            );
212
        } catch (Exception $e) {
213
            $row = false;
214
        }
215 30
        if ($row === false) {
216 2
            return false;
217
        }
218
        // create array
219 29
        $data       = Json::decode($row['data']);
220 29
        $isVersion2 = array_key_exists('v', $data) && $data['v'] >= 2;
221 29
        if ($isVersion2) {
222 24
            $paste            = $data;
223 24
            list($createdKey) = $this->_getVersionedKeys(2);
224
        } else {
225 5
            $paste            = array('data' => $row['data']);
226 5
            list($createdKey) = $this->_getVersionedKeys(1);
227
        }
228
229
        try {
230 29
            $row['meta'] = Json::decode($row['meta']);
231 1
        } catch (Exception $e) {
232 1
            $row['meta'] = array();
233
        }
234 29
        $row                        = self::upgradePreV1Format($row);
235 29
        $paste['meta']              = $row['meta'];
236 29
        $paste['meta'][$createdKey] = (int) $row['postdate'];
237 29
        $expire_date                = (int) $row['expiredate'];
238 29
        if ($expire_date > 0) {
239 16
            $paste['meta']['expire_date'] = $expire_date;
240
        }
241 29
        if ($isVersion2) {
242 24
            return $paste;
243
        }
244
245
        // support v1 attachments
246 5
        if (array_key_exists('attachment', $row) && !empty($row['attachment'])) {
247 2
            $paste['attachment'] = $row['attachment'];
248 2
            if (array_key_exists('attachmentname', $row) && !empty($row['attachmentname'])) {
249 2
                $paste['attachmentname'] = $row['attachmentname'];
250
            }
251
        }
252 5
        if ($row['opendiscussion']) {
253 2
            $paste['meta']['opendiscussion'] = true;
254
        }
255 5
        if ($row['burnafterreading']) {
256 1
            $paste['meta']['burnafterreading'] = true;
257
        }
258
259 5
        return $paste;
260
    }
261
262
    /**
263
     * Delete a paste and its discussion.
264
     *
265
     * @access public
266
     * @param  string $pasteid
267
     */
268 26
    public function delete($pasteid)
269
    {
270 26
        $this->_exec(
271 26
            'DELETE FROM "' . $this->_sanitizeIdentifier('paste') .
272 26
            '" WHERE "dataid" = ?', array($pasteid)
273 26
        );
274 26
        $this->_exec(
275 26
            'DELETE FROM "' . $this->_sanitizeIdentifier('comment') .
276 26
            '" WHERE "pasteid" = ?', array($pasteid)
277 26
        );
278
    }
279
280
    /**
281
     * Test if a paste exists.
282
     *
283
     * @access public
284
     * @param  string $pasteid
285
     * @return bool
286
     */
287 59
    public function exists($pasteid)
288
    {
289
        try {
290 59
            $row = $this->_select(
291 59
                'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('paste') .
292 59
                '" WHERE "dataid" = ?', array($pasteid), true
293 59
            );
294 1
        } catch (Exception $e) {
295 1
            return false;
296
        }
297 59
        return (bool) $row;
298
    }
299
300
    /**
301
     * Create a comment in a paste.
302
     *
303
     * @access public
304
     * @param  string $pasteid
305
     * @param  string $parentid
306
     * @param  string $commentid
307
     * @param  array  $comment
308
     * @return bool
309
     */
310 9
    public function createComment($pasteid, $parentid, $commentid, array $comment)
311
    {
312 9
        if (array_key_exists('data', $comment)) {
313 1
            $version = 1;
314 1
            $data    = $comment['data'];
315
        } else {
316 9
            $version = 2;
317 9
            $data    = Json::encode($comment);
318
        }
319 9
        list($createdKey, $iconKey) = $this->_getVersionedKeys($version);
320 9
        $meta                       = $comment['meta'];
321 9
        unset($comment['meta']);
322 9
        foreach (array('nickname', $iconKey) as $key) {
323 9
            if (!array_key_exists($key, $meta)) {
324 9
                $meta[$key] = null;
325
            }
326
        }
327
        try {
328 9
            return $this->_exec(
329 9
                'INSERT INTO "' . $this->_sanitizeIdentifier('comment') .
330 9
                '" VALUES(?,?,?,?,?,?,?)',
331 9
                array(
332 9
                    $commentid,
333 9
                    $pasteid,
334 9
                    $parentid,
335 9
                    $data,
336 9
                    $meta['nickname'],
337 9
                    $meta[$iconKey],
338 9
                    $meta[$createdKey],
339 9
                )
340 9
            );
341 1
        } catch (Exception $e) {
342 1
            return false;
343
        }
344
    }
345
346
    /**
347
     * Read all comments of paste.
348
     *
349
     * @access public
350
     * @param  string $pasteid
351
     * @return array
352
     */
353 18
    public function readComments($pasteid)
354
    {
355 18
        $rows = $this->_select(
356 18
            'SELECT * FROM "' . $this->_sanitizeIdentifier('comment') .
357 18
            '" WHERE "pasteid" = ?', array($pasteid)
358 18
        );
359
360
        // create comment list
361 18
        $comments = array();
362 18
        if (is_array($rows) && count($rows)) {
363 6
            foreach ($rows as $row) {
364 6
                $i    = $this->getOpenSlot($comments, (int) $row['postdate']);
365 6
                $data = Json::decode($row['data']);
366 6
                if (array_key_exists('v', $data) && $data['v'] >= 2) {
367 6
                    $version      = 2;
368 6
                    $comments[$i] = $data;
369
                } else {
370 1
                    $version      = 1;
371 1
                    $comments[$i] = array('data' => $row['data']);
372
                }
373 6
                list($createdKey, $iconKey) = $this->_getVersionedKeys($version);
374 6
                $comments[$i]['id']         = $row['dataid'];
375 6
                $comments[$i]['parentid']   = $row['parentid'];
376 6
                $comments[$i]['meta']       = array($createdKey => (int) $row['postdate']);
377 6
                foreach (array('nickname' => 'nickname', 'vizhash' => $iconKey) as $rowKey => $commentKey) {
378 6
                    if (array_key_exists($rowKey, $row) && !empty($row[$rowKey])) {
379 5
                        $comments[$i]['meta'][$commentKey] = $row[$rowKey];
380
                    }
381
                }
382
            }
383 6
            ksort($comments);
384
        }
385 18
        return $comments;
386
    }
387
388
    /**
389
     * Test if a comment exists.
390
     *
391
     * @access public
392
     * @param  string $pasteid
393
     * @param  string $parentid
394
     * @param  string $commentid
395
     * @return bool
396
     */
397 13
    public function existsComment($pasteid, $parentid, $commentid)
398
    {
399
        try {
400 13
            return (bool) $this->_select(
401 13
                'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('comment') .
402 13
                '" WHERE "pasteid" = ? AND "parentid" = ? AND "dataid" = ?',
403 13
                array($pasteid, $parentid, $commentid), true
404 13
            );
405 1
        } catch (Exception $e) {
406 1
            return false;
407
        }
408
    }
409
410
    /**
411
     * Save a value.
412
     *
413
     * @access public
414
     * @param  string $value
415
     * @param  string $namespace
416
     * @param  string $key
417
     * @return bool
418
     */
419 17
    public function setValue($value, $namespace, $key = '')
420
    {
421 17
        if ($namespace === 'traffic_limiter') {
422 4
            $this->_last_cache[$key] = $value;
423
            try {
424 4
                $value = Json::encode($this->_last_cache);
425
            } catch (Exception $e) {
426
                return false;
427
            }
428
        }
429 17
        return $this->_exec(
430 17
            'UPDATE "' . $this->_sanitizeIdentifier('config') .
431 17
            '" SET "value" = ? WHERE "id" = ?',
432 17
            array($value, strtoupper($namespace))
433 17
        );
434
    }
435
436
    /**
437
     * Load a value.
438
     *
439
     * @access public
440
     * @param  string $namespace
441
     * @param  string $key
442
     * @return string
443
     */
444 22
    public function getValue($namespace, $key = '')
445
    {
446 22
        $configKey = strtoupper($namespace);
447 22
        $value     = $this->_getConfig($configKey);
448 22
        if ($value === '') {
449
            // initialize the row, so that setValue can rely on UPDATE queries
450 17
            $this->_exec(
451 17
                'INSERT INTO "' . $this->_sanitizeIdentifier('config') .
452 17
                '" VALUES(?,?)',
453 17
                array($configKey, '')
454 17
            );
455
456
            // migrate filesystem based salt into database
457 17
            $file = 'data' . DIRECTORY_SEPARATOR . 'salt.php';
458 17
            if ($namespace === 'salt' && is_readable($file)) {
459 1
                $fs    = new Filesystem(array('dir' => 'data'));
460 1
                $value = $fs->getValue('salt');
461 1
                $this->setValue($value, 'salt');
462 1
                @unlink($file);
0 ignored issues
show
Security Best Practice introduced by
It seems like you do not handle an error condition for unlink(). This can introduce security issues, and is generally not recommended. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-unhandled  annotation

462
                /** @scrutinizer ignore-unhandled */ @unlink($file);

If you suppress an error, we recommend checking for the error condition explicitly:

// For example instead of
@mkdir($dir);

// Better use
if (@mkdir($dir) === false) {
    throw new \RuntimeException('The directory '.$dir.' could not be created.');
}
Loading history...
463 1
                return $value;
464
            }
465
        }
466 21
        if ($value && $namespace === 'traffic_limiter') {
467
            try {
468 1
                $this->_last_cache = Json::decode($value);
469
            } catch (Exception $e) {
470
                $this->_last_cache = array();
471
            }
472 1
            if (array_key_exists($key, $this->_last_cache)) {
473 1
                return $this->_last_cache[$key];
474
            }
475
        }
476 21
        return (string) $value;
477
    }
478
479
    /**
480
     * Returns up to batch size number of paste ids that have expired
481
     *
482
     * @access private
483
     * @param  int $batchsize
484
     * @return array
485
     */
486 12
    protected function _getExpiredPastes($batchsize)
487
    {
488 12
        $statement = $this->_db->prepare(
489 12
            'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('paste') .
490 12
            '" WHERE "expiredate" < ? AND "expiredate" != ? ' .
491 12
            ($this->_type === 'oci' ? 'FETCH NEXT ? ROWS ONLY' : 'LIMIT ?')
492 12
        );
493 12
        $statement->execute(array(time(), 0, $batchsize));
494 12
        return $statement->fetchAll(PDO::FETCH_COLUMN, 0);
495
    }
496
497
    /**
498
     * @inheritDoc
499
     */
500
    public function getAllPastes()
501
    {
502
        return $this->_db->query(
503
            'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('paste') . '"'
504
        )->fetchAll(PDO::FETCH_COLUMN, 0);
505
    }
506
507
    /**
508
     * execute a statement
509
     *
510
     * @access private
511
     * @param  string $sql
512
     * @param  array $params
513
     * @throws PDOException
514
     * @return bool
515
     */
516 60
    private function _exec($sql, array $params)
517
    {
518 60
        $statement = $this->_db->prepare($sql);
519 60
        foreach ($params as $key => &$parameter) {
520 60
            $position = $key + 1;
521 60
            if (is_int($parameter)) {
522 38
                $statement->bindParam($position, $parameter, PDO::PARAM_INT);
523 60
            } elseif (is_string($parameter) && strlen($parameter) >= 4000) {
524
                $statement->bindParam($position, $parameter, PDO::PARAM_STR, strlen($parameter));
525
            } else {
526 60
                $statement->bindParam($position, $parameter);
527
            }
528
        }
529 60
        $result = $statement->execute();
530 60
        $statement->closeCursor();
531 60
        return $result;
532
    }
533
534
    /**
535
     * run a select statement
536
     *
537
     * @access private
538
     * @param  string $sql
539
     * @param  array $params
540
     * @param  bool $firstOnly if only the first row should be returned
541
     * @throws PDOException
542
     * @return array|false
543
     */
544 78
    private function _select($sql, array $params, $firstOnly = false)
545
    {
546 78
        $statement = $this->_db->prepare($sql);
547 78
        $statement->execute($params);
548 78
        if ($firstOnly) {
549 78
            $result = $statement->fetch(PDO::FETCH_ASSOC);
550 18
        } elseif ($this->_type === 'oci') {
551
            // workaround for https://bugs.php.net/bug.php?id=46728
552
            $result = array();
553
            while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
554
                $result[] = array_map('PrivateBin\Data\Database::_sanitizeClob', $row);
555
            }
556
        } else {
557 18
            $result = $statement->fetchAll(PDO::FETCH_ASSOC);
558
        }
559 78
        $statement->closeCursor();
560 78
        if ($this->_type === 'oci' && is_array($result)) {
561
            // returned CLOB values are streams, convert these into strings
562
            $result = $firstOnly ?
563
                array_map('PrivateBin\Data\Database::_sanitizeClob', $result) :
564
                $result;
565
        }
566 78
        return $result;
567
    }
568
569
    /**
570
     * get version dependent key names
571
     *
572
     * @access private
573
     * @param  int $version
574
     * @return array
575
     */
576 42
    private function _getVersionedKeys($version)
577
    {
578 42
        if ($version === 1) {
579 6
            return array('postdate', 'vizhash');
580
        }
581 37
        return array('created', 'icon');
582
    }
583
584
    /**
585
     * get table list query, depending on the database type
586
     *
587
     * @access private
588
     * @param  string $type
589
     * @throws Exception
590
     * @return string
591
     */
592 78
    private function _getTableQuery($type)
593
    {
594
        switch ($type) {
595 78
            case 'ibm':
596 1
                $sql = 'SELECT "tabname" FROM "SYSCAT"."TABLES"';
597 1
                break;
598 78
            case 'informix':
599 1
                $sql = 'SELECT "tabname" FROM "systables"';
600 1
                break;
601 78
            case 'mssql':
602
                // U: tables created by the user
603 1
                $sql = 'SELECT "name" FROM "sysobjects" '
604 1
                     . 'WHERE "type" = \'U\' ORDER BY "name"';
605 1
                break;
606 78
            case 'mysql':
607 1
                $sql = 'SHOW TABLES';
608 1
                break;
609 78
            case 'oci':
610 1
                $sql = 'SELECT table_name FROM all_tables';
611 1
                break;
612 78
            case 'pgsql':
613 1
                $sql = 'SELECT c."relname" AS "table_name" '
614 1
                     . 'FROM "pg_class" c, "pg_user" u '
615 1
                     . 'WHERE c."relowner" = u."usesysid" AND c."relkind" = \'r\' '
616 1
                     . 'AND NOT EXISTS (SELECT 1 FROM "pg_views" WHERE "viewname" = c."relname") '
617 1
                     . "AND c.\"relname\" !~ '^(pg_|sql_)' "
618 1
                     . 'UNION '
619 1
                     . 'SELECT c."relname" AS "table_name" '
620 1
                     . 'FROM "pg_class" c '
621 1
                     . "WHERE c.\"relkind\" = 'r' "
622 1
                     . 'AND NOT EXISTS (SELECT 1 FROM "pg_views" WHERE "viewname" = c."relname") '
623 1
                     . 'AND NOT EXISTS (SELECT 1 FROM "pg_user" WHERE "usesysid" = c."relowner") '
624 1
                     . "AND c.\"relname\" !~ '^pg_'";
625 1
                break;
626 78
            case 'sqlite':
627 78
                $sql = 'SELECT "name" FROM "sqlite_master" WHERE "type"=\'table\' '
628 78
                     . 'UNION ALL SELECT "name" FROM "sqlite_temp_master" '
629 78
                     . 'WHERE "type"=\'table\' ORDER BY "name"';
630 78
                break;
631
            default:
632 1
                throw new Exception(
633 1
                    "PDO type $type is currently not supported.", 5
634 1
                );
635
        }
636 78
        return $sql;
637
    }
638
639
    /**
640
     * get a value by key from the config table
641
     *
642
     * @access private
643
     * @param  string $key
644
     * @return string
645
     */
646 72
    private function _getConfig($key)
647
    {
648
        try {
649 72
            $row = $this->_select(
650 72
                'SELECT "value" FROM "' . $this->_sanitizeIdentifier('config') .
651 72
                '" WHERE "id" = ?', array($key), true
652 72
            );
653
        } catch (PDOException $e) {
654
            return '';
655
        }
656 72
        return $row ? $row['value'] : '';
657
    }
658
659
    /**
660
     * get the primary key clauses, depending on the database driver
661
     *
662
     * @access private
663
     * @param  string $key
664
     * @return array
665
     */
666 45
    private function _getPrimaryKeyClauses($key = 'dataid')
667
    {
668 45
        $main_key = $after_key = '';
669 45
        switch ($this->_type) {
670 45
            case 'mysql':
671 45
            case 'oci':
672
                $after_key = ", PRIMARY KEY (\"$key\")";
673
                break;
674
            default:
675 45
                $main_key = ' PRIMARY KEY';
676 45
                break;
677
        }
678 45
        return array($main_key, $after_key);
679
    }
680
681
    /**
682
     * get the data type, depending on the database driver
683
     *
684
     * PostgreSQL and OCI uses a different API for BLOBs then SQL, hence we use TEXT and CLOB
685
     *
686
     * @access private
687
     * @return string
688
     */
689 45
    private function _getDataType()
690
    {
691 45
        switch ($this->_type) {
692 45
            case 'oci':
693
                return 'CLOB';
694 45
            case 'pgsql':
695
                return 'TEXT';
696
            default:
697 45
                return 'BLOB';
698
        }
699
    }
700
701
    /**
702
     * get the attachment type, depending on the database driver
703
     *
704
     * PostgreSQL and OCI use different APIs for BLOBs then SQL, hence we use TEXT and CLOB
705
     *
706
     * @access private
707
     * @return string
708
     */
709 45
    private function _getAttachmentType()
710
    {
711 45
        switch ($this->_type) {
712 45
            case 'oci':
713
                return 'CLOB';
714 45
            case 'pgsql':
715
                return 'TEXT';
716
            default:
717 45
                return 'MEDIUMBLOB';
718
        }
719
    }
720
721
    /**
722
     * get the meta type, depending on the database driver
723
     *
724
     * OCI doesn't accept TEXT so it has to be VARCHAR2(4000)
725
     *
726
     * @access private
727
     * @return string
728
     */
729 45
    private function _getMetaType()
730
    {
731 45
        switch ($this->_type) {
732 45
            case 'oci':
733
                return 'VARCHAR2(4000)';
734
            default:
735 45
                return 'TEXT';
736
        }
737
    }
738
739
    /**
740
     * create the paste table
741
     *
742
     * @access private
743
     */
744 44
    private function _createPasteTable()
745
    {
746 44
        list($main_key, $after_key) = $this->_getPrimaryKeyClauses();
747 44
        $dataType                   = $this->_getDataType();
748 44
        $attachmentType             = $this->_getAttachmentType();
749 44
        $metaType                   = $this->_getMetaType();
750 44
        $this->_db->exec(
751 44
            'CREATE TABLE "' . $this->_sanitizeIdentifier('paste') . '" ( ' .
752 44
            "\"dataid\" CHAR(16) NOT NULL$main_key, " .
753 44
            "\"data\" $attachmentType, " .
754 44
            '"postdate" INT, ' .
755 44
            '"expiredate" INT, ' .
756 44
            '"opendiscussion" INT, ' .
757 44
            '"burnafterreading" INT, ' .
758 44
            "\"meta\" $metaType, " .
759 44
            "\"attachment\" $attachmentType, " .
760 44
            "\"attachmentname\" $dataType$after_key )"
761 44
        );
762
    }
763
764
    /**
765
     * create the paste table
766
     *
767
     * @access private
768
     */
769 44
    private function _createCommentTable()
770
    {
771 44
        list($main_key, $after_key) = $this->_getPrimaryKeyClauses();
772 44
        $dataType                   = $this->_getDataType();
773 44
        $this->_db->exec(
774 44
            'CREATE TABLE "' . $this->_sanitizeIdentifier('comment') . '" ( ' .
775 44
            "\"dataid\" CHAR(16) NOT NULL$main_key, " .
776 44
            '"pasteid" CHAR(16), ' .
777 44
            '"parentid" CHAR(16), ' .
778 44
            "\"data\" $dataType, " .
779 44
            "\"nickname\" $dataType, " .
780 44
            "\"vizhash\" $dataType, " .
781 44
            "\"postdate\" INT$after_key )"
782 44
        );
783 44
        if ($this->_type === 'oci') {
784
            $this->_db->exec(
785
                'declare
786
                    already_exists  exception;
787
                    columns_indexed exception;
788
                    pragma exception_init( already_exists, -955 );
789
                    pragma exception_init(columns_indexed, -1408);
790
                begin
791
                    execute immediate \'create index "comment_parent" on "' . $this->_sanitizeIdentifier('comment') . '" ("pasteid")\';
792
                exception
793
                    when already_exists or columns_indexed then
794
                    NULL;
795
                end;'
796
            );
797
        } else {
798
            // CREATE INDEX IF NOT EXISTS not supported as of Oracle MySQL <= 8.0
799 44
            $this->_db->exec(
800 44
                'CREATE INDEX "' .
801 44
                $this->_sanitizeIdentifier('comment_parent') . '" ON "' .
802 44
                $this->_sanitizeIdentifier('comment') . '" ("pasteid")'
803 44
            );
804
        }
805
    }
806
807
    /**
808
     * create the paste table
809
     *
810
     * @access private
811
     */
812 45
    private function _createConfigTable()
813
    {
814 45
        list($main_key, $after_key) = $this->_getPrimaryKeyClauses('id');
815 45
        $charType                   = $this->_type === 'oci' ? 'VARCHAR2(16)' : 'CHAR(16)';
816 45
        $textType                   = $this->_getMetaType();
817 45
        $this->_db->exec(
818 45
            'CREATE TABLE "' . $this->_sanitizeIdentifier('config') .
819 45
            "\" ( \"id\" $charType NOT NULL$main_key, \"value\" $textType$after_key )"
820 45
        );
821 45
        $this->_exec(
822 45
            'INSERT INTO "' . $this->_sanitizeIdentifier('config') .
823 45
            '" VALUES(?,?)',
824 45
            array('VERSION', Controller::VERSION)
825 45
        );
826
    }
827
828
    /**
829
     * sanitizes CLOB values used with OCI
830
     *
831
     * From: https://stackoverflow.com/questions/36200534/pdo-oci-into-a-clob-field
832
     *
833
     * @access public
834
     * @static
835
     * @param  int|string|resource $value
836
     * @return int|string
837
     */
838 1
    public static function _sanitizeClob($value)
839
    {
840 1
        if (is_resource($value)) {
841 1
            $value = stream_get_contents($value);
842
        }
843 1
        return $value;
844
    }
845
846
    /**
847
     * sanitizes identifiers
848
     *
849
     * @access private
850
     * @param  string $identifier
851
     * @return string
852
     */
853 78
    private function _sanitizeIdentifier($identifier)
854
    {
855 78
        return preg_replace('/[^A-Za-z0-9_]+/', '', $this->_prefix . $identifier);
856
    }
857
858
    /**
859
     * upgrade the database schema from an old version
860
     *
861
     * @access private
862
     * @param  string $oldversion
863
     */
864 1
    private function _upgradeDatabase($oldversion)
865
    {
866 1
        $dataType       = $this->_getDataType();
867 1
        $attachmentType = $this->_getAttachmentType();
868
        switch ($oldversion) {
869 1
            case '0.21':
870
                // create the meta column if necessary (pre 0.21 change)
871
                try {
872 1
                    $this->_db->exec(
873 1
                        'SELECT "meta" FROM "' . $this->_sanitizeIdentifier('paste') . '" ' .
874 1
                        ($this->_type === 'oci' ? 'FETCH NEXT 1 ROWS ONLY' : 'LIMIT 1')
875 1
                    );
876
                } catch (PDOException $e) {
877
                    $this->_db->exec('ALTER TABLE "' . $this->_sanitizeIdentifier('paste') . '" ADD COLUMN "meta" TEXT');
878
                }
879
                // SQLite only allows one ALTER statement at a time...
880 1
                $this->_db->exec(
881 1
                    'ALTER TABLE "' . $this->_sanitizeIdentifier('paste') .
882 1
                    "\" ADD COLUMN \"attachment\" $attachmentType"
883 1
                );
884 1
                $this->_db->exec(
885 1
                    'ALTER TABLE "' . $this->_sanitizeIdentifier('paste') . "\" ADD COLUMN \"attachmentname\" $dataType"
886 1
                );
887
                // SQLite doesn't support MODIFY, but it allows TEXT of similar
888
                // size as BLOB, so there is no need to change it there
889 1
                if ($this->_type !== 'sqlite') {
890
                    $this->_db->exec(
891
                        'ALTER TABLE "' . $this->_sanitizeIdentifier('paste') .
892
                        "\" ADD PRIMARY KEY (\"dataid\"), MODIFY COLUMN \"data\" $dataType"
893
                    );
894
                    $this->_db->exec(
895
                        'ALTER TABLE "' . $this->_sanitizeIdentifier('comment') .
896
                        "\" ADD PRIMARY KEY (\"dataid\"), MODIFY COLUMN \"data\" $dataType, " .
897
                        "MODIFY COLUMN \"nickname\" $dataType, MODIFY COLUMN \"vizhash\" $dataType"
898
                    );
899
                } else {
900 1
                    $this->_db->exec(
901 1
                        'CREATE UNIQUE INDEX IF NOT EXISTS "' .
902 1
                        $this->_sanitizeIdentifier('paste_dataid') . '" ON "' .
903 1
                        $this->_sanitizeIdentifier('paste') . '" ("dataid")'
904 1
                    );
905 1
                    $this->_db->exec(
906 1
                        'CREATE UNIQUE INDEX IF NOT EXISTS "' .
907 1
                        $this->_sanitizeIdentifier('comment_dataid') . '" ON "' .
908 1
                        $this->_sanitizeIdentifier('comment') . '" ("dataid")'
909 1
                    );
910
                }
911
                // CREATE INDEX IF NOT EXISTS not supported as of Oracle MySQL <= 8.0
912 1
                $this->_db->exec(
913 1
                    'CREATE INDEX "' .
914 1
                    $this->_sanitizeIdentifier('comment_parent') . '" ON "' .
915 1
                    $this->_sanitizeIdentifier('comment') . '" ("pasteid")'
916 1
                );
917
                // no break, continue with updates for 0.22 and later
918
            case '1.3':
919
                // SQLite doesn't support MODIFY, but it allows TEXT of similar
920
                // size as BLOB and PostgreSQL uses TEXT, so there is no need
921
                // to change it there
922 1
                if ($this->_type !== 'sqlite' && $this->_type !== 'pgsql') {
923
                    $this->_db->exec(
924
                        'ALTER TABLE "' . $this->_sanitizeIdentifier('paste') .
925
                        "\" MODIFY COLUMN \"data\" $attachmentType"
926
                    );
927
                }
928
                // no break, continue with updates for all newer versions
929
            default:
930 1
                $this->_exec(
931 1
                    'UPDATE "' . $this->_sanitizeIdentifier('config') .
932 1
                    '" SET "value" = ? WHERE "id" = ?',
933 1
                    array(Controller::VERSION, 'VERSION')
934 1
                );
935
        }
936
    }
937
}
938