Database   F
last analyzed

Complexity

Total Complexity 88

Size/Duplication

Total Lines 753
Duplicated Lines 0 %

Test Coverage

Coverage 95.73%

Importance

Changes 3
Bugs 0 Features 0
Metric Value
eloc 345
dl 0
loc 753
ccs 314
cts 328
cp 0.9573
rs 2
c 3
b 0
f 0
wmc 88

22 Methods

Rating   Name   Duplication   Size   Complexity  
A _getPrimaryKeyClauses() 0 9 2
A _getConfig() 0 7 1
A _getVersionedKeys() 0 6 2
C getInstance() 0 75 12
B create() 0 58 11
A exists() 0 8 2
A _exec() 0 6 1
A delete() 0 14 2
A _select() 0 9 2
A _getExpiredPastes() 0 14 3
A createComment() 0 28 4
C read() 0 57 14
B readComments() 0 33 8
A existsComment() 0 6 1
B _upgradeDatabase() 0 63 7
A _createConfigTable() 0 11 1
A _createCommentTable() 0 17 1
A _getAttachmentType() 0 3 2
A _createPasteTable() 0 16 1
A _getDataType() 0 3 2
A _sanitizeIdentifier() 0 3 1
B _getTableQuery() 0 44 8

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.3.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
     * cache for select queries
30
     *
31
     * @var array
32
     */
33
    private static $_cache = array();
34
35
    /**
36
     * instance of database connection
37
     *
38
     * @access private
39
     * @static
40
     * @var PDO
41
     */
42
    private static $_db;
43
44
    /**
45
     * table prefix
46
     *
47
     * @access private
48
     * @static
49
     * @var string
50
     */
51
    private static $_prefix = '';
52
53
    /**
54
     * database type
55
     *
56
     * @access private
57
     * @static
58
     * @var string
59
     */
60
    private static $_type = '';
61
62
    /**
63
     * get instance of singleton
64
     *
65
     * @access public
66
     * @static
67
     * @param  array $options
68
     * @throws Exception
69
     * @return Database
70
     */
71 69
    public static function getInstance(array $options)
72
    {
73
        // if needed initialize the singleton
74 69
        if (!(self::$_instance instanceof self)) {
75 38
            self::$_instance = new self;
76
        }
77
78
        // set table prefix if given
79 69
        if (array_key_exists('tbl', $options)) {
80 48
            self::$_prefix = $options['tbl'];
81
        }
82
83
        // initialize the db connection with new options
84
        if (
85 69
            array_key_exists('dsn', $options) &&
86 69
            array_key_exists('usr', $options) &&
87 69
            array_key_exists('pwd', $options) &&
88 69
            array_key_exists('opt', $options)
89
        ) {
90
            // set default options
91 69
            $options['opt'][PDO::ATTR_ERRMODE]          = PDO::ERRMODE_EXCEPTION;
92 69
            $options['opt'][PDO::ATTR_EMULATE_PREPARES] = false;
93 69
            $options['opt'][PDO::ATTR_PERSISTENT]       = true;
94 69
            $db_tables_exist                            = true;
95
96
            // setup type and dabase connection
97 69
            self::$_type = strtolower(
98 69
                substr($options['dsn'], 0, strpos($options['dsn'], ':'))
99
            );
100 69
            $tableQuery = self::_getTableQuery(self::$_type);
101 69
            self::$_db  = new PDO(
102 69
                $options['dsn'],
103 69
                $options['usr'],
104 69
                $options['pwd'],
105 69
                $options['opt']
106
            );
107
108
            // check if the database contains the required tables
109 69
            $tables = self::$_db->query($tableQuery)->fetchAll(PDO::FETCH_COLUMN, 0);
110
111
            // create paste table if necessary
112 69
            if (!in_array(self::_sanitizeIdentifier('paste'), $tables)) {
113 40
                self::_createPasteTable();
114 40
                $db_tables_exist = false;
115
            }
116
117
            // create comment table if necessary
118 69
            if (!in_array(self::_sanitizeIdentifier('comment'), $tables)) {
119 40
                self::_createCommentTable();
120 40
                $db_tables_exist = false;
121
            }
122
123
            // create config table if necessary
124 69
            $db_version = Controller::VERSION;
125 69
            if (!in_array(self::_sanitizeIdentifier('config'), $tables)) {
126 40
                self::_createConfigTable();
127
                // if we only needed to create the config table, the DB is older then 0.22
128 40
                if ($db_tables_exist) {
129 40
                    $db_version = '0.21';
130
                }
131
            } else {
132 60
                $db_version = self::_getConfig('VERSION');
133
            }
134
135
            // update database structure if necessary
136 69
            if (version_compare($db_version, Controller::VERSION, '<')) {
137 69
                self::_upgradeDatabase($db_version);
138
            }
139
        } else {
140 4
            throw new Exception(
141 4
                'Missing configuration for key dsn, usr, pwd or opt in the section model_options, please check your configuration file', 6
142
            );
143
        }
144
145 69
        return self::$_instance;
146
    }
147
148
    /**
149
     * Create a paste.
150
     *
151
     * @access public
152
     * @param  string $pasteid
153
     * @param  array  $paste
154
     * @return bool
155
     */
156 38
    public function create($pasteid, array $paste)
157
    {
158
        if (
159 38
            array_key_exists($pasteid, self::$_cache)
160
        ) {
161 38
            if (false !== self::$_cache[$pasteid]) {
162 2
                return false;
163
            } else {
164 38
                unset(self::$_cache[$pasteid]);
165
            }
166
        }
167
168 38
        $expire_date      = 0;
169 38
        $opendiscussion   = $burnafterreading = false;
170 38
        $attachment       = $attachmentname   = null;
171 38
        $meta             = $paste['meta'];
172 38
        $isVersion1       = array_key_exists('data', $paste);
173 38
        list($createdKey) = self::_getVersionedKeys($isVersion1 ? 1 : 2);
174 38
        $created          = (int) $meta[$createdKey];
175 38
        unset($meta[$createdKey], $paste['meta']);
176 38
        if (array_key_exists('expire_date', $meta)) {
177 23
            $expire_date = (int) $meta['expire_date'];
178 23
            unset($meta['expire_date']);
179
        }
180 38
        if (array_key_exists('opendiscussion', $meta)) {
181 2
            $opendiscussion = $meta['opendiscussion'];
182 2
            unset($meta['opendiscussion']);
183
        }
184 38
        if (array_key_exists('burnafterreading', $meta)) {
185 1
            $burnafterreading = $meta['burnafterreading'];
186 1
            unset($meta['burnafterreading']);
187
        }
188 38
        if ($isVersion1) {
189 2
            if (array_key_exists('attachment', $meta)) {
190 1
                $attachment = $meta['attachment'];
191 1
                unset($meta['attachment']);
192
            }
193 2
            if (array_key_exists('attachmentname', $meta)) {
194 1
                $attachmentname = $meta['attachmentname'];
195 2
                unset($meta['attachmentname']);
196
            }
197
        } else {
198 36
            $opendiscussion   = $paste['adata'][2];
199 36
            $burnafterreading = $paste['adata'][3];
200
        }
201 38
        return self::_exec(
202 38
            'INSERT INTO ' . self::_sanitizeIdentifier('paste') .
203 38
            ' VALUES(?,?,?,?,?,?,?,?,?)',
204
            array(
205 38
                $pasteid,
206 38
                $isVersion1 ? $paste['data'] : Json::encode($paste),
207 38
                $created,
208 38
                $expire_date,
209 38
                (int) $opendiscussion,
210 38
                (int) $burnafterreading,
211 38
                Json::encode($meta),
212 38
                $attachment,
213 38
                $attachmentname,
214
            )
215
        );
216
    }
217
218
    /**
219
     * Read a paste.
220
     *
221
     * @access public
222
     * @param  string $pasteid
223
     * @return array|false
224
     */
225 54
    public function read($pasteid)
226
    {
227 54
        if (array_key_exists($pasteid, self::$_cache)) {
228 26
            return self::$_cache[$pasteid];
229
        }
230
231 54
        self::$_cache[$pasteid] = false;
232 54
        $paste                  = self::_select(
233 54
            'SELECT * FROM ' . self::_sanitizeIdentifier('paste') .
234 54
            ' WHERE dataid = ?', array($pasteid), true
235
        );
236
237 54
        if ($paste === false) {
238 52
            return false;
239
        }
240
        // create array
241 37
        $data       = Json::decode($paste['data']);
242 37
        $isVersion2 = array_key_exists('v', $data) && $data['v'] >= 2;
243 37
        if ($isVersion2) {
244 34
            self::$_cache[$pasteid] = $data;
245 34
            list($createdKey)       = self::_getVersionedKeys(2);
246
        } else {
247 3
            self::$_cache[$pasteid] = array('data' => $paste['data']);
248 3
            list($createdKey)       = self::_getVersionedKeys(1);
249
        }
250
251
        try {
252 37
            $paste['meta'] = Json::decode($paste['meta']);
253
        } catch (Exception $e) {
254
            $paste['meta'] = array();
255
        }
256 37
        $paste                                       = self::upgradePreV1Format($paste);
257 37
        self::$_cache[$pasteid]['meta']              = $paste['meta'];
258 37
        self::$_cache[$pasteid]['meta'][$createdKey] = (int) $paste['postdate'];
259 37
        $expire_date                                 = (int) $paste['expiredate'];
260 37
        if ($expire_date > 0) {
261 22
            self::$_cache[$pasteid]['meta']['expire_date'] = $expire_date;
262
        }
263 37
        if ($isVersion2) {
264 34
            return self::$_cache[$pasteid];
265
        }
266
267
        // support v1 attachments
268 3
        if (array_key_exists('attachment', $paste) && strlen($paste['attachment'])) {
269 2
            self::$_cache[$pasteid]['attachment'] = $paste['attachment'];
270 2
            if (array_key_exists('attachmentname', $paste) && strlen($paste['attachmentname'])) {
271 2
                self::$_cache[$pasteid]['attachmentname'] = $paste['attachmentname'];
272
            }
273
        }
274 3
        if ($paste['opendiscussion']) {
275 2
            self::$_cache[$pasteid]['meta']['opendiscussion'] = true;
276
        }
277 3
        if ($paste['burnafterreading']) {
278 1
            self::$_cache[$pasteid]['meta']['burnafterreading'] = true;
279
        }
280
281 3
        return self::$_cache[$pasteid];
282
    }
283
284
    /**
285
     * Delete a paste and its discussion.
286
     *
287
     * @access public
288
     * @param  string $pasteid
289
     */
290 21
    public function delete($pasteid)
291
    {
292 21
        self::_exec(
293 21
            'DELETE FROM ' . self::_sanitizeIdentifier('paste') .
294 21
            ' WHERE dataid = ?', array($pasteid)
295
        );
296 21
        self::_exec(
297 21
            'DELETE FROM ' . self::_sanitizeIdentifier('comment') .
298 21
            ' WHERE pasteid = ?', array($pasteid)
299
        );
300
        if (
301 21
            array_key_exists($pasteid, self::$_cache)
302
        ) {
303 19
            unset(self::$_cache[$pasteid]);
304
        }
305 21
    }
306
307
    /**
308
     * Test if a paste exists.
309
     *
310
     * @access public
311
     * @param  string $pasteid
312
     * @return bool
313
     */
314 54
    public function exists($pasteid)
315
    {
316
        if (
317 54
            !array_key_exists($pasteid, self::$_cache)
318
        ) {
319 53
            self::$_cache[$pasteid] = $this->read($pasteid);
320
        }
321 54
        return (bool) self::$_cache[$pasteid];
322
    }
323
324
    /**
325
     * Create a comment in a paste.
326
     *
327
     * @access public
328
     * @param  string $pasteid
329
     * @param  string $parentid
330
     * @param  string $commentid
331
     * @param  array  $comment
332
     * @return bool
333
     */
334 8
    public function createComment($pasteid, $parentid, $commentid, array $comment)
335
    {
336 8
        if (array_key_exists('data', $comment)) {
337 1
            $version = 1;
338 1
            $data    = $comment['data'];
339
        } else {
340 8
            $version = 2;
341 8
            $data    = Json::encode($comment);
342
        }
343 8
        list($createdKey, $iconKey) = self::_getVersionedKeys($version);
344 8
        $meta                       = $comment['meta'];
345 8
        unset($comment['meta']);
346 8
        foreach (array('nickname', $iconKey) as $key) {
347 8
            if (!array_key_exists($key, $meta)) {
348 8
                $meta[$key] = null;
349
            }
350
        }
351 8
        return self::_exec(
352 8
            'INSERT INTO ' . self::_sanitizeIdentifier('comment') .
353 8
            ' VALUES(?,?,?,?,?,?,?)',
354
            array(
355 8
                $commentid,
356 8
                $pasteid,
357 8
                $parentid,
358 8
                $data,
359 8
                $meta['nickname'],
360 8
                $meta[$iconKey],
361 8
                $meta[$createdKey],
362
            )
363
        );
364
    }
365
366
    /**
367
     * Read all comments of paste.
368
     *
369
     * @access public
370
     * @param  string $pasteid
371
     * @return array
372
     */
373 17
    public function readComments($pasteid)
374
    {
375 17
        $rows = self::_select(
376 17
            'SELECT * FROM ' . self::_sanitizeIdentifier('comment') .
377 17
            ' WHERE pasteid = ?', array($pasteid)
378
        );
379
380
        // create comment list
381 17
        $comments = array();
382 17
        if (count($rows)) {
383 6
            foreach ($rows as $row) {
384 6
                $i    = $this->getOpenSlot($comments, (int) $row['postdate']);
385 6
                $data = Json::decode($row['data']);
386 6
                if (array_key_exists('v', $data) && $data['v'] >= 2) {
387 6
                    $version      = 2;
388 6
                    $comments[$i] = $data;
389
                } else {
390 1
                    $version      = 1;
391 1
                    $comments[$i] = array('data' => $row['data']);
392
                }
393 6
                list($createdKey, $iconKey) = self::_getVersionedKeys($version);
394 6
                $comments[$i]['id']         = $row['dataid'];
395 6
                $comments[$i]['parentid']   = $row['parentid'];
396 6
                $comments[$i]['meta']       = array($createdKey => (int) $row['postdate']);
397 6
                foreach (array('nickname' => 'nickname', 'vizhash' => $iconKey) as $rowKey => $commentKey) {
398 6
                    if (array_key_exists($rowKey, $row) && !empty($row[$rowKey])) {
399 6
                        $comments[$i]['meta'][$commentKey] = $row[$rowKey];
400
                    }
401
                }
402
            }
403 6
            ksort($comments);
404
        }
405 17
        return $comments;
406
    }
407
408
    /**
409
     * Test if a comment exists.
410
     *
411
     * @access public
412
     * @param  string $pasteid
413
     * @param  string $parentid
414
     * @param  string $commentid
415
     * @return bool
416
     */
417 11
    public function existsComment($pasteid, $parentid, $commentid)
418
    {
419 11
        return (bool) self::_select(
420 11
            'SELECT dataid FROM ' . self::_sanitizeIdentifier('comment') .
421 11
            ' WHERE pasteid = ? AND parentid = ? AND dataid = ?',
422 11
            array($pasteid, $parentid, $commentid), true
423
        );
424
    }
425
426
    /**
427
     * Returns up to batch size number of paste ids that have expired
428
     *
429
     * @access private
430
     * @param  int $batchsize
431
     * @return array
432
     */
433 13
    protected function _getExpiredPastes($batchsize)
434
    {
435 13
        $pastes = array();
436 13
        $rows   = self::_select(
437 13
            'SELECT dataid FROM ' . self::_sanitizeIdentifier('paste') .
438 13
            ' WHERE expiredate < ? AND expiredate != ? LIMIT ?',
439 13
            array(time(), 0, $batchsize)
440
        );
441 13
        if (count($rows)) {
442 2
            foreach ($rows as $row) {
443 2
                $pastes[] = $row['dataid'];
444
            }
445
        }
446 13
        return $pastes;
447
    }
448
449
    /**
450
     * execute a statement
451
     *
452
     * @access private
453
     * @static
454
     * @param  string $sql
455
     * @param  array $params
456
     * @throws PDOException
457
     * @return bool
458
     */
459 54
    private static function _exec($sql, array $params)
460
    {
461 54
        $statement = self::$_db->prepare($sql);
462 54
        $result    = $statement->execute($params);
463 54
        $statement->closeCursor();
464 54
        return $result;
465
    }
466
467
    /**
468
     * run a select statement
469
     *
470
     * @access private
471
     * @static
472
     * @param  string $sql
473
     * @param  array $params
474
     * @param  bool $firstOnly if only the first row should be returned
475
     * @throws PDOException
476
     * @return array|false
477
     */
478 68
    private static function _select($sql, array $params, $firstOnly = false)
479
    {
480 68
        $statement = self::$_db->prepare($sql);
481 68
        $statement->execute($params);
482 68
        $result = $firstOnly ?
483 68
            $statement->fetch(PDO::FETCH_ASSOC) :
484 68
            $statement->fetchAll(PDO::FETCH_ASSOC);
485 68
        $statement->closeCursor();
486 68
        return $result;
487
    }
488
489
    /**
490
     * get version dependent key names
491
     *
492
     * @access private
493
     * @static
494
     * @param  int $version
495
     * @return array
496
     */
497 39
    private static function _getVersionedKeys($version)
498
    {
499 39
        if ($version === 1) {
500 4
            return array('postdate', 'vizhash');
501
        }
502 36
        return array('created', 'icon');
503
    }
504
505
    /**
506
     * get table list query, depending on the database type
507
     *
508
     * @access private
509
     * @static
510
     * @param  string $type
511
     * @throws Exception
512
     * @return string
513
     */
514 69
    private static function _getTableQuery($type)
515
    {
516 69
        switch ($type) {
517 69
            case 'ibm':
518 1
                $sql = 'SELECT tabname FROM SYSCAT.TABLES ';
519 1
                break;
520 69
            case 'informix':
521 1
                $sql = 'SELECT tabname FROM systables ';
522 1
                break;
523 69
            case 'mssql':
524
                $sql = 'SELECT name FROM sysobjects '
525 1
                     . "WHERE type = 'U' ORDER BY name";
526 1
                break;
527 69
            case 'mysql':
528 1
                $sql = 'SHOW TABLES';
529 1
                break;
530 69
            case 'oci':
531 1
                $sql = 'SELECT table_name FROM all_tables';
532 1
                break;
533 69
            case 'pgsql':
534
                $sql = 'SELECT c.relname AS table_name '
535
                     . 'FROM pg_class c, pg_user u '
536
                     . "WHERE c.relowner = u.usesysid AND c.relkind = 'r' "
537
                     . 'AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) '
538
                     . "AND c.relname !~ '^(pg_|sql_)' "
539
                     . 'UNION '
540
                     . 'SELECT c.relname AS table_name '
541
                     . 'FROM pg_class c '
542
                     . "WHERE c.relkind = 'r' "
543
                     . 'AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) '
544
                     . 'AND NOT EXISTS (SELECT 1 FROM pg_user WHERE usesysid = c.relowner) '
545 1
                     . "AND c.relname !~ '^pg_'";
546 1
                break;
547 69
            case 'sqlite':
548
                $sql = "SELECT name FROM sqlite_master WHERE type='table' "
549
                     . 'UNION ALL SELECT name FROM sqlite_temp_master '
550 69
                     . "WHERE type='table' ORDER BY name";
551 69
                break;
552
            default:
553 1
                throw new Exception(
554 1
                    "PDO type $type is currently not supported.", 5
555
                );
556
        }
557 69
        return $sql;
558
    }
559
560
    /**
561
     * get a value by key from the config table
562
     *
563
     * @access private
564
     * @static
565
     * @param  string $key
566
     * @throws PDOException
567
     * @return string
568
     */
569 60
    private static function _getConfig($key)
570
    {
571 60
        $row = self::_select(
572 60
            'SELECT value FROM ' . self::_sanitizeIdentifier('config') .
573 60
            ' WHERE id = ?', array($key), true
574
        );
575 60
        return $row['value'];
576
    }
577
578
    /**
579
     * get the primary key clauses, depending on the database driver
580
     *
581
     * @access private
582
     * @static
583
     * @param  string $key
584
     * @return array
585
     */
586 40
    private static function _getPrimaryKeyClauses($key = 'dataid')
587
    {
588 40
        $main_key = $after_key = '';
589 40
        if (self::$_type === 'mysql') {
590
            $after_key = ", PRIMARY KEY ($key)";
591
        } else {
592 40
            $main_key = ' PRIMARY KEY';
593
        }
594 40
        return array($main_key, $after_key);
595
    }
596
597
    /**
598
     * get the data type, depending on the database driver
599
     *
600
     * PostgreSQL uses a different API for BLOBs then SQL, hence we use TEXT
601
     *
602
     * @access private
603
     * @static
604
     * @return string
605
     */
606 40
    private static function _getDataType()
607
    {
608 40
        return self::$_type === 'pgsql' ? 'TEXT' : 'BLOB';
609
    }
610
611
    /**
612
     * get the attachment type, depending on the database driver
613
     *
614
     * PostgreSQL uses a different API for BLOBs then SQL, hence we use TEXT
615
     *
616
     * @access private
617
     * @static
618
     * @return string
619
     */
620 40
    private static function _getAttachmentType()
621
    {
622 40
        return self::$_type === 'pgsql' ? 'TEXT' : 'MEDIUMBLOB';
623
    }
624
625
    /**
626
     * create the paste table
627
     *
628
     * @access private
629
     * @static
630
     */
631 40
    private static function _createPasteTable()
632
    {
633 40
        list($main_key, $after_key) = self::_getPrimaryKeyClauses();
634 40
        $dataType                   = self::_getDataType();
635 40
        $attachmentType             = self::_getAttachmentType();
636 40
        self::$_db->exec(
637 40
            'CREATE TABLE ' . self::_sanitizeIdentifier('paste') . ' ( ' .
638 40
            "dataid CHAR(16) NOT NULL$main_key, " .
639 40
            "data $attachmentType, " .
640 40
            'postdate INT, ' .
641 40
            'expiredate INT, ' .
642 40
            'opendiscussion INT, ' .
643 40
            'burnafterreading INT, ' .
644 40
            'meta TEXT, ' .
645 40
            "attachment $attachmentType, " .
646 40
            "attachmentname $dataType$after_key );"
647
        );
648 40
    }
649
650
    /**
651
     * create the paste table
652
     *
653
     * @access private
654
     * @static
655
     */
656 40
    private static function _createCommentTable()
657
    {
658 40
        list($main_key, $after_key) = self::_getPrimaryKeyClauses();
659 40
        $dataType                   = self::_getDataType();
660 40
        self::$_db->exec(
661 40
            'CREATE TABLE ' . self::_sanitizeIdentifier('comment') . ' ( ' .
662 40
            "dataid CHAR(16) NOT NULL$main_key, " .
663 40
            'pasteid CHAR(16), ' .
664 40
            'parentid CHAR(16), ' .
665 40
            "data $dataType, " .
666 40
            "nickname $dataType, " .
667 40
            "vizhash $dataType, " .
668 40
            "postdate INT$after_key );"
669
        );
670 40
        self::$_db->exec(
671
            'CREATE INDEX IF NOT EXISTS comment_parent ON ' .
672 40
            self::_sanitizeIdentifier('comment') . '(pasteid);'
673
        );
674 40
    }
675
676
    /**
677
     * create the paste table
678
     *
679
     * @access private
680
     * @static
681
     */
682 40
    private static function _createConfigTable()
683
    {
684 40
        list($main_key, $after_key) = self::_getPrimaryKeyClauses('id');
685 40
        self::$_db->exec(
686 40
            'CREATE TABLE ' . self::_sanitizeIdentifier('config') .
687 40
            " ( id CHAR(16) NOT NULL$main_key, value TEXT$after_key );"
688
        );
689 40
        self::_exec(
690 40
            'INSERT INTO ' . self::_sanitizeIdentifier('config') .
691 40
            ' VALUES(?,?)',
692 40
            array('VERSION', Controller::VERSION)
693
        );
694 40
    }
695
696
    /**
697
     * sanitizes identifiers
698
     *
699
     * @access private
700
     * @static
701
     * @param  string $identifier
702
     * @return string
703
     */
704 69
    private static function _sanitizeIdentifier($identifier)
705
    {
706 69
        return preg_replace('/[^A-Za-z0-9_]+/', '', self::$_prefix . $identifier);
707
    }
708
709
    /**
710
     * upgrade the database schema from an old version
711
     *
712
     * @access private
713
     * @static
714
     * @param  string $oldversion
715
     */
716 1
    private static function _upgradeDatabase($oldversion)
717
    {
718 1
        $dataType       = self::_getDataType();
719 1
        $attachmentType = self::_getAttachmentType();
720
        switch ($oldversion) {
721 1
            case '0.21':
722
                // create the meta column if necessary (pre 0.21 change)
723
                try {
724 1
                    self::$_db->exec('SELECT meta FROM ' . self::_sanitizeIdentifier('paste') . ' LIMIT 1;');
725 1
                } catch (PDOException $e) {
726 1
                    self::$_db->exec('ALTER TABLE ' . self::_sanitizeIdentifier('paste') . ' ADD COLUMN meta TEXT;');
727
                }
728
                // SQLite only allows one ALTER statement at a time...
729 1
                self::$_db->exec(
730 1
                    'ALTER TABLE ' . self::_sanitizeIdentifier('paste') .
731 1
                    " ADD COLUMN attachment $attachmentType;"
732
                );
733 1
                self::$_db->exec(
734 1
                    'ALTER TABLE ' . self::_sanitizeIdentifier('paste') . " ADD COLUMN attachmentname $dataType;"
735
                );
736
                // SQLite doesn't support MODIFY, but it allows TEXT of similar
737
                // size as BLOB, so there is no need to change it there
738 1
                if (self::$_type !== 'sqlite') {
739
                    self::$_db->exec(
740
                        'ALTER TABLE ' . self::_sanitizeIdentifier('paste') .
741
                        " ADD PRIMARY KEY (dataid), MODIFY COLUMN data $dataType;"
742
                    );
743
                    self::$_db->exec(
744
                        'ALTER TABLE ' . self::_sanitizeIdentifier('comment') .
745
                        " ADD PRIMARY KEY (dataid), MODIFY COLUMN data $dataType, " .
746
                        "MODIFY COLUMN nickname $dataType, MODIFY COLUMN vizhash $dataType;"
747
                    );
748
                } else {
749 1
                    self::$_db->exec(
750
                        'CREATE UNIQUE INDEX IF NOT EXISTS paste_dataid ON ' .
751 1
                        self::_sanitizeIdentifier('paste') . '(dataid);'
752
                    );
753 1
                    self::$_db->exec(
754
                        'CREATE UNIQUE INDEX IF NOT EXISTS comment_dataid ON ' .
755 1
                        self::_sanitizeIdentifier('comment') . '(dataid);'
756
                    );
757
                }
758 1
                self::$_db->exec(
759
                    'CREATE INDEX IF NOT EXISTS comment_parent ON ' .
760 1
                    self::_sanitizeIdentifier('comment') . '(pasteid);'
761
                );
762
                // no break, continue with updates for 0.22 and later
763
            case '1.3':
764
                // SQLite doesn't support MODIFY, but it allows TEXT of similar
765
                // size as BLOB and PostgreSQL uses TEXT, so there is no need
766
                // to change it there
767 1
                if (self::$_type !== 'sqlite' && self::$_type !== 'pgsql') {
768
                    self::$_db->exec(
769
                        'ALTER TABLE ' . self::_sanitizeIdentifier('paste') .
770
                        " MODIFY COLUMN data $attachmentType;"
771
                    );
772
                }
773
                // no break, continue with updates for 1.3.1 and later
774
            default:
775 1
                self::_exec(
776 1
                    'UPDATE ' . self::_sanitizeIdentifier('config') .
777 1
                    ' SET value = ? WHERE id = ?',
778 1
                    array(Controller::VERSION, 'VERSION')
779
                );
780
        }
781 1
    }
782
}
783