Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
Total | |
88.94% |
386 / 434 |
|
48.15% |
13 / 27 |
CRAP | |
0.00% |
0 / 1 |
Database | |
88.94% |
386 / 434 |
|
48.15% |
13 / 27 |
155.57 | |
0.00% |
0 / 1 |
__construct | |
97.56% |
40 / 41 |
|
0.00% |
0 / 1 |
14 | |||
create | |
100.00% |
39 / 39 |
|
100.00% |
1 / 1 |
9 | |||
read | |
93.33% |
28 / 30 |
|
0.00% |
0 / 1 |
14.06 | |||
delete | |
100.00% |
8 / 8 |
|
100.00% |
1 / 1 |
1 | |||
exists | |
100.00% |
7 / 7 |
|
100.00% |
1 / 1 |
2 | |||
createComment | |
100.00% |
28 / 28 |
|
100.00% |
1 / 1 |
6 | |||
readComments | |
100.00% |
23 / 23 |
|
100.00% |
1 / 1 |
9 | |||
existsComment | |
100.00% |
7 / 7 |
|
100.00% |
1 / 1 |
2 | |||
setValue | |
80.00% |
8 / 10 |
|
0.00% |
0 / 1 |
3.07 | |||
getValue | |
90.91% |
20 / 22 |
|
0.00% |
0 / 1 |
8.05 | |||
_getExpiredPastes | |
100.00% |
7 / 7 |
|
100.00% |
1 / 1 |
2 | |||
getAllPastes | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
2 | |||
_exec | |
91.67% |
11 / 12 |
|
0.00% |
0 / 1 |
5.01 | |||
_select | |
60.00% |
9 / 15 |
|
0.00% |
0 / 1 |
10.14 | |||
_getVersionedKeys | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
_getTableQuery | |
100.00% |
29 / 29 |
|
100.00% |
1 / 1 |
9 | |||
_getConfig | |
71.43% |
5 / 7 |
|
0.00% |
0 / 1 |
3.21 | |||
_getPrimaryKeyClauses | |
77.78% |
7 / 9 |
|
0.00% |
0 / 1 |
4.18 | |||
_getDataType | |
66.67% |
4 / 6 |
|
0.00% |
0 / 1 |
4.59 | |||
_getAttachmentType | |
66.67% |
4 / 6 |
|
0.00% |
0 / 1 |
4.59 | |||
_getMetaType | |
75.00% |
3 / 4 |
|
0.00% |
0 / 1 |
3.14 | |||
_createPasteTable | |
100.00% |
15 / 15 |
|
100.00% |
1 / 1 |
1 | |||
_createCommentTable | |
78.26% |
18 / 23 |
|
0.00% |
0 / 1 |
2.04 | |||
_createConfigTable | |
100.00% |
12 / 12 |
|
100.00% |
1 / 1 |
2 | |||
_sanitizeClob | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
_sanitizeIdentifier | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
_upgradeDatabase | |
73.44% |
47 / 64 |
|
0.00% |
0 / 1 |
14.70 |
1 | <?php declare(strict_types=1); |
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 | */ |
11 | |
12 | namespace PrivateBin\Data; |
13 | |
14 | use Exception; |
15 | use PDO; |
16 | use PDOException; |
17 | use PrivateBin\Controller; |
18 | use PrivateBin\Json; |
19 | |
20 | /** |
21 | * Database |
22 | * |
23 | * Model for database access, implemented as a singleton. |
24 | */ |
25 | class Database extends AbstractData |
26 | { |
27 | /** |
28 | * instance of database connection |
29 | * |
30 | * @access private |
31 | * @var PDO |
32 | */ |
33 | private $_db; |
34 | |
35 | /** |
36 | * table prefix |
37 | * |
38 | * @access private |
39 | * @var string |
40 | */ |
41 | private $_prefix = ''; |
42 | |
43 | /** |
44 | * database type |
45 | * |
46 | * @access private |
47 | * @var string |
48 | */ |
49 | private $_type = ''; |
50 | |
51 | /** |
52 | * instantiates a new Database data backend |
53 | * |
54 | * @access public |
55 | * @param array $options |
56 | * @throws Exception |
57 | */ |
58 | public function __construct(array $options) |
59 | { |
60 | // set table prefix if given |
61 | if (array_key_exists('tbl', $options)) { |
62 | $this->_prefix = $options['tbl']; |
63 | } |
64 | |
65 | // initialize the db connection with new options |
66 | if ( |
67 | array_key_exists('dsn', $options) && |
68 | array_key_exists('usr', $options) && |
69 | array_key_exists('pwd', $options) && |
70 | array_key_exists('opt', $options) |
71 | ) { |
72 | // set default options |
73 | $options['opt'][PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION; |
74 | $options['opt'][PDO::ATTR_EMULATE_PREPARES] = false; |
75 | if (!array_key_exists(PDO::ATTR_PERSISTENT, $options['opt'])) { |
76 | $options['opt'][PDO::ATTR_PERSISTENT] = true; |
77 | } |
78 | $db_tables_exist = true; |
79 | |
80 | // setup type and dabase connection |
81 | $this->_type = strtolower( |
82 | substr($options['dsn'], 0, strpos($options['dsn'], ':')) |
83 | ); |
84 | // MySQL uses backticks to quote identifiers by default, |
85 | // tell it to expect ANSI SQL double quotes |
86 | if ($this->_type === 'mysql' && defined('PDO::MYSQL_ATTR_INIT_COMMAND')) { |
87 | $options['opt'][PDO::MYSQL_ATTR_INIT_COMMAND] = "SET SESSION sql_mode='ANSI_QUOTES'"; |
88 | } |
89 | $tableQuery = $this->_getTableQuery($this->_type); |
90 | $this->_db = new PDO( |
91 | $options['dsn'], |
92 | $options['usr'], |
93 | $options['pwd'], |
94 | $options['opt'] |
95 | ); |
96 | |
97 | // check if the database contains the required tables |
98 | $tables = $this->_db->query($tableQuery)->fetchAll(PDO::FETCH_COLUMN, 0); |
99 | |
100 | // create paste table if necessary |
101 | if (!in_array($this->_sanitizeIdentifier('paste'), $tables)) { |
102 | $this->_createPasteTable(); |
103 | $db_tables_exist = false; |
104 | } |
105 | |
106 | // create comment table if necessary |
107 | if (!in_array($this->_sanitizeIdentifier('comment'), $tables)) { |
108 | $this->_createCommentTable(); |
109 | $db_tables_exist = false; |
110 | } |
111 | |
112 | // create config table if necessary |
113 | $db_version = Controller::VERSION; |
114 | if (!in_array($this->_sanitizeIdentifier('config'), $tables)) { |
115 | $this->_createConfigTable(); |
116 | // if we only needed to create the config table, the DB is older then 0.22 |
117 | if ($db_tables_exist) { |
118 | $db_version = '0.21'; |
119 | } |
120 | } else { |
121 | $db_version = $this->_getConfig('VERSION'); |
122 | } |
123 | |
124 | // update database structure if necessary |
125 | if (version_compare($db_version, Controller::VERSION, '<')) { |
126 | $this->_upgradeDatabase($db_version); |
127 | } |
128 | } else { |
129 | throw new Exception( |
130 | 'Missing configuration for key dsn, usr, pwd or opt in the section model_options, please check your configuration file', 6 |
131 | ); |
132 | } |
133 | } |
134 | |
135 | /** |
136 | * Create a paste. |
137 | * |
138 | * @access public |
139 | * @param string $pasteid |
140 | * @param array $paste |
141 | * @return bool |
142 | */ |
143 | public function create($pasteid, array &$paste) |
144 | { |
145 | $expire_date = 0; |
146 | $opendiscussion = $burnafterreading = false; |
147 | $attachment = $attachmentname = null; |
148 | $meta = $paste['meta']; |
149 | $isVersion1 = array_key_exists('data', $paste); |
150 | if (array_key_exists('expire_date', $meta)) { |
151 | $expire_date = (int) $meta['expire_date']; |
152 | unset($meta['expire_date']); |
153 | } |
154 | if (array_key_exists('opendiscussion', $meta)) { |
155 | $opendiscussion = $meta['opendiscussion']; |
156 | unset($meta['opendiscussion']); |
157 | } |
158 | if (array_key_exists('burnafterreading', $meta)) { |
159 | $burnafterreading = $meta['burnafterreading']; |
160 | unset($meta['burnafterreading']); |
161 | } |
162 | if ($isVersion1) { |
163 | if (array_key_exists('attachment', $meta)) { |
164 | $attachment = $meta['attachment']; |
165 | unset($meta['attachment']); |
166 | } |
167 | if (array_key_exists('attachmentname', $meta)) { |
168 | $attachmentname = $meta['attachmentname']; |
169 | unset($meta['attachmentname']); |
170 | } |
171 | } else { |
172 | $opendiscussion = $paste['adata'][2]; |
173 | $burnafterreading = $paste['adata'][3]; |
174 | } |
175 | try { |
176 | return $this->_exec( |
177 | 'INSERT INTO "' . $this->_sanitizeIdentifier('paste') . |
178 | '" VALUES(?,?,?,?,?,?,?,?)', |
179 | array( |
180 | $pasteid, |
181 | $isVersion1 ? $paste['data'] : Json::encode($paste), |
182 | $expire_date, |
183 | (int) $opendiscussion, |
184 | (int) $burnafterreading, |
185 | Json::encode($meta), |
186 | $attachment, |
187 | $attachmentname, |
188 | ) |
189 | ); |
190 | } catch (Exception $e) { |
191 | return false; |
192 | } |
193 | } |
194 | |
195 | /** |
196 | * Read a paste. |
197 | * |
198 | * @access public |
199 | * @param string $pasteid |
200 | * @return array|false |
201 | */ |
202 | public function read($pasteid) |
203 | { |
204 | try { |
205 | $row = $this->_select( |
206 | 'SELECT * FROM "' . $this->_sanitizeIdentifier('paste') . |
207 | '" WHERE "dataid" = ?', array($pasteid), true |
208 | ); |
209 | } catch (Exception $e) { |
210 | $row = false; |
211 | } |
212 | if ($row === false) { |
213 | return false; |
214 | } |
215 | // create array |
216 | $data = Json::decode($row['data']); |
217 | $isVersion2 = array_key_exists('v', $data) && $data['v'] >= 2; |
218 | $paste = $isVersion2 ? $data : array('data' => $row['data']); |
219 | |
220 | try { |
221 | $row['meta'] = Json::decode($row['meta']); |
222 | } catch (Exception $e) { |
223 | $row['meta'] = array(); |
224 | } |
225 | $row = self::upgradePreV1Format($row); |
226 | $paste['meta'] = $row['meta']; |
227 | $expire_date = (int) $row['expiredate']; |
228 | if ($expire_date > 0) { |
229 | $paste['meta']['expire_date'] = $expire_date; |
230 | } |
231 | if ($isVersion2) { |
232 | return $paste; |
233 | } |
234 | |
235 | // support v1 attachments |
236 | if (array_key_exists('attachment', $row) && !empty($row['attachment'])) { |
237 | $paste['attachment'] = $row['attachment']; |
238 | if (array_key_exists('attachmentname', $row) && !empty($row['attachmentname'])) { |
239 | $paste['attachmentname'] = $row['attachmentname']; |
240 | } |
241 | } |
242 | if ($row['opendiscussion']) { |
243 | $paste['meta']['opendiscussion'] = true; |
244 | } |
245 | if ($row['burnafterreading']) { |
246 | $paste['meta']['burnafterreading'] = true; |
247 | } |
248 | |
249 | return $paste; |
250 | } |
251 | |
252 | /** |
253 | * Delete a paste and its discussion. |
254 | * |
255 | * @access public |
256 | * @param string $pasteid |
257 | */ |
258 | public function delete($pasteid) |
259 | { |
260 | $this->_exec( |
261 | 'DELETE FROM "' . $this->_sanitizeIdentifier('paste') . |
262 | '" WHERE "dataid" = ?', array($pasteid) |
263 | ); |
264 | $this->_exec( |
265 | 'DELETE FROM "' . $this->_sanitizeIdentifier('comment') . |
266 | '" WHERE "pasteid" = ?', array($pasteid) |
267 | ); |
268 | } |
269 | |
270 | /** |
271 | * Test if a paste exists. |
272 | * |
273 | * @access public |
274 | * @param string $pasteid |
275 | * @return bool |
276 | */ |
277 | public function exists($pasteid) |
278 | { |
279 | try { |
280 | $row = $this->_select( |
281 | 'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('paste') . |
282 | '" WHERE "dataid" = ?', array($pasteid), true |
283 | ); |
284 | } catch (Exception $e) { |
285 | return false; |
286 | } |
287 | return (bool) $row; |
288 | } |
289 | |
290 | /** |
291 | * Create a comment in a paste. |
292 | * |
293 | * @access public |
294 | * @param string $pasteid |
295 | * @param string $parentid |
296 | * @param string $commentid |
297 | * @param array $comment |
298 | * @return bool |
299 | */ |
300 | public function createComment($pasteid, $parentid, $commentid, array &$comment) |
301 | { |
302 | if (array_key_exists('data', $comment)) { |
303 | $version = 1; |
304 | $data = $comment['data']; |
305 | } else { |
306 | try { |
307 | $version = 2; |
308 | $data = Json::encode($comment); |
309 | } catch (Exception $e) { |
310 | return false; |
311 | } |
312 | } |
313 | list($createdKey, $iconKey) = $this->_getVersionedKeys($version); |
314 | $meta = $comment['meta']; |
315 | unset($comment['meta']); |
316 | foreach (array('nickname', $iconKey) as $key) { |
317 | if (!array_key_exists($key, $meta)) { |
318 | $meta[$key] = null; |
319 | } |
320 | } |
321 | try { |
322 | return $this->_exec( |
323 | 'INSERT INTO "' . $this->_sanitizeIdentifier('comment') . |
324 | '" VALUES(?,?,?,?,?,?,?)', |
325 | array( |
326 | $commentid, |
327 | $pasteid, |
328 | $parentid, |
329 | $data, |
330 | $meta['nickname'], |
331 | $meta[$iconKey], |
332 | $meta[$createdKey], |
333 | ) |
334 | ); |
335 | } catch (Exception $e) { |
336 | return false; |
337 | } |
338 | } |
339 | |
340 | /** |
341 | * Read all comments of paste. |
342 | * |
343 | * @access public |
344 | * @param string $pasteid |
345 | * @return array |
346 | */ |
347 | public function readComments($pasteid) |
348 | { |
349 | $rows = $this->_select( |
350 | 'SELECT * FROM "' . $this->_sanitizeIdentifier('comment') . |
351 | '" WHERE "pasteid" = ?', array($pasteid) |
352 | ); |
353 | |
354 | // create comment list |
355 | $comments = array(); |
356 | if (is_array($rows) && count($rows)) { |
357 | foreach ($rows as $row) { |
358 | $i = $this->getOpenSlot($comments, (int) $row['postdate']); |
359 | $data = Json::decode($row['data']); |
360 | if (array_key_exists('v', $data) && $data['v'] >= 2) { |
361 | $version = 2; |
362 | $comments[$i] = $data; |
363 | } else { |
364 | $version = 1; |
365 | $comments[$i] = array('data' => $row['data']); |
366 | } |
367 | list($createdKey, $iconKey) = $this->_getVersionedKeys($version); |
368 | $comments[$i]['id'] = $row['dataid']; |
369 | $comments[$i]['parentid'] = $row['parentid']; |
370 | $comments[$i]['meta'] = array($createdKey => (int) $row['postdate']); |
371 | foreach (array('nickname' => 'nickname', 'vizhash' => $iconKey) as $rowKey => $commentKey) { |
372 | if (array_key_exists($rowKey, $row) && !empty($row[$rowKey])) { |
373 | $comments[$i]['meta'][$commentKey] = $row[$rowKey]; |
374 | } |
375 | } |
376 | } |
377 | ksort($comments); |
378 | } |
379 | return $comments; |
380 | } |
381 | |
382 | /** |
383 | * Test if a comment exists. |
384 | * |
385 | * @access public |
386 | * @param string $pasteid |
387 | * @param string $parentid |
388 | * @param string $commentid |
389 | * @return bool |
390 | */ |
391 | public function existsComment($pasteid, $parentid, $commentid) |
392 | { |
393 | try { |
394 | return (bool) $this->_select( |
395 | 'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('comment') . |
396 | '" WHERE "pasteid" = ? AND "parentid" = ? AND "dataid" = ?', |
397 | array($pasteid, $parentid, $commentid), true |
398 | ); |
399 | } catch (Exception $e) { |
400 | return false; |
401 | } |
402 | } |
403 | |
404 | /** |
405 | * Save a value. |
406 | * |
407 | * @access public |
408 | * @param string $value |
409 | * @param string $namespace |
410 | * @param string $key |
411 | * @return bool |
412 | */ |
413 | public function setValue($value, $namespace, $key = '') |
414 | { |
415 | if ($namespace === 'traffic_limiter') { |
416 | $this->_last_cache[$key] = $value; |
417 | try { |
418 | $value = Json::encode($this->_last_cache); |
419 | } catch (Exception $e) { |
420 | return false; |
421 | } |
422 | } |
423 | return $this->_exec( |
424 | 'UPDATE "' . $this->_sanitizeIdentifier('config') . |
425 | '" SET "value" = ? WHERE "id" = ?', |
426 | array($value, strtoupper($namespace)) |
427 | ); |
428 | } |
429 | |
430 | /** |
431 | * Load a value. |
432 | * |
433 | * @access public |
434 | * @param string $namespace |
435 | * @param string $key |
436 | * @return string |
437 | */ |
438 | public function getValue($namespace, $key = '') |
439 | { |
440 | $configKey = strtoupper($namespace); |
441 | $value = $this->_getConfig($configKey); |
442 | if ($value === '') { |
443 | // initialize the row, so that setValue can rely on UPDATE queries |
444 | $this->_exec( |
445 | 'INSERT INTO "' . $this->_sanitizeIdentifier('config') . |
446 | '" VALUES(?,?)', |
447 | array($configKey, '') |
448 | ); |
449 | |
450 | // migrate filesystem based salt into database |
451 | $file = 'data' . DIRECTORY_SEPARATOR . 'salt.php'; |
452 | if ($namespace === 'salt' && is_readable($file)) { |
453 | $fs = new Filesystem(array('dir' => 'data')); |
454 | $value = $fs->getValue('salt'); |
455 | $this->setValue($value, 'salt'); |
456 | @unlink($file); |
457 | return $value; |
458 | } |
459 | } |
460 | if ($value && $namespace === 'traffic_limiter') { |
461 | try { |
462 | $this->_last_cache = Json::decode($value); |
463 | } catch (Exception $e) { |
464 | $this->_last_cache = array(); |
465 | } |
466 | if (array_key_exists($key, $this->_last_cache)) { |
467 | return $this->_last_cache[$key]; |
468 | } |
469 | } |
470 | return (string) $value; |
471 | } |
472 | |
473 | /** |
474 | * Returns up to batch size number of paste ids that have expired |
475 | * |
476 | * @access private |
477 | * @param int $batchsize |
478 | * @return array |
479 | */ |
480 | protected function _getExpiredPastes($batchsize) |
481 | { |
482 | $statement = $this->_db->prepare( |
483 | 'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('paste') . |
484 | '" WHERE "expiredate" < ? AND "expiredate" != ? ' . |
485 | ($this->_type === 'oci' ? 'FETCH NEXT ? ROWS ONLY' : 'LIMIT ?') |
486 | ); |
487 | $statement->execute(array(time(), 0, $batchsize)); |
488 | return $statement->fetchAll(PDO::FETCH_COLUMN, 0); |
489 | } |
490 | |
491 | /** |
492 | * @inheritDoc |
493 | */ |
494 | public function getAllPastes() |
495 | { |
496 | return $this->_db->query( |
497 | 'SELECT "dataid" FROM "' . $this->_sanitizeIdentifier('paste') . '"' |
498 | )->fetchAll(PDO::FETCH_COLUMN, 0); |
499 | } |
500 | |
501 | /** |
502 | * execute a statement |
503 | * |
504 | * @access private |
505 | * @param string $sql |
506 | * @param array $params |
507 | * @throws PDOException |
508 | * @return bool |
509 | */ |
510 | private function _exec($sql, array $params) |
511 | { |
512 | $statement = $this->_db->prepare($sql); |
513 | $position = 1; |
514 | foreach ($params as &$parameter) { |
515 | if (is_int($parameter)) { |
516 | $statement->bindParam($position, $parameter, PDO::PARAM_INT); |
517 | } elseif (is_string($parameter) && strlen($parameter) >= 4000) { |
518 | $statement->bindParam($position, $parameter, PDO::PARAM_STR, strlen($parameter)); |
519 | } else { |
520 | $statement->bindParam($position, $parameter); |
521 | } |
522 | ++$position; |
523 | } |
524 | $result = $statement->execute(); |
525 | $statement->closeCursor(); |
526 | return $result; |
527 | } |
528 | |
529 | /** |
530 | * run a select statement |
531 | * |
532 | * @access private |
533 | * @param string $sql |
534 | * @param array $params |
535 | * @param bool $firstOnly if only the first row should be returned |
536 | * @throws PDOException |
537 | * @return array|false |
538 | */ |
539 | private function _select($sql, array $params, $firstOnly = false) |
540 | { |
541 | $statement = $this->_db->prepare($sql); |
542 | $statement->execute($params); |
543 | if ($firstOnly) { |
544 | $result = $statement->fetch(PDO::FETCH_ASSOC); |
545 | } elseif ($this->_type === 'oci') { |
546 | // workaround for https://bugs.php.net/bug.php?id=46728 |
547 | $result = array(); |
548 | while ($row = $statement->fetch(PDO::FETCH_ASSOC)) { |
549 | $result[] = array_map('PrivateBin\Data\Database::_sanitizeClob', $row); |
550 | } |
551 | } else { |
552 | $result = $statement->fetchAll(PDO::FETCH_ASSOC); |
553 | } |
554 | $statement->closeCursor(); |
555 | if ($this->_type === 'oci' && is_array($result)) { |
556 | // returned CLOB values are streams, convert these into strings |
557 | $result = $firstOnly ? |
558 | array_map('PrivateBin\Data\Database::_sanitizeClob', $result) : |
559 | $result; |
560 | } |
561 | return $result; |
562 | } |
563 | |
564 | /** |
565 | * get version dependent key names |
566 | * |
567 | * @access private |
568 | * @param int $version |
569 | * @return array |
570 | */ |
571 | private function _getVersionedKeys($version) |
572 | { |
573 | if ($version === 1) { |
574 | return array('postdate', 'vizhash'); |
575 | } |
576 | return array('created', 'icon'); |
577 | } |
578 | |
579 | /** |
580 | * get table list query, depending on the database type |
581 | * |
582 | * @access private |
583 | * @param string $type |
584 | * @throws Exception |
585 | * @return string |
586 | */ |
587 | private function _getTableQuery($type) |
588 | { |
589 | switch ($type) { |
590 | case 'ibm': |
591 | $sql = 'SELECT "tabname" FROM "SYSCAT"."TABLES"'; |
592 | break; |
593 | case 'informix': |
594 | $sql = 'SELECT "tabname" FROM "systables"'; |
595 | break; |
596 | case 'mssql': |
597 | // U: tables created by the user |
598 | $sql = 'SELECT "name" FROM "sysobjects" ' |
599 | . 'WHERE "type" = \'U\' ORDER BY "name"'; |
600 | break; |
601 | case 'mysql': |
602 | $sql = 'SHOW TABLES'; |
603 | break; |
604 | case 'oci': |
605 | $sql = 'SELECT table_name FROM all_tables'; |
606 | break; |
607 | case 'pgsql': |
608 | $sql = 'SELECT "tablename" FROM "pg_catalog"."pg_tables" ' |
609 | . 'WHERE "schemaname" NOT IN (\'pg_catalog\', \'information_schema\')'; |
610 | break; |
611 | case 'sqlite': |
612 | $sql = 'SELECT "name" FROM "sqlite_master" WHERE "type"=\'table\' ' |
613 | . 'UNION ALL SELECT "name" FROM "sqlite_temp_master" ' |
614 | . 'WHERE "type"=\'table\' ORDER BY "name"'; |
615 | break; |
616 | default: |
617 | throw new Exception( |
618 | "PDO type $type is currently not supported.", 5 |
619 | ); |
620 | } |
621 | return $sql; |
622 | } |
623 | |
624 | /** |
625 | * get a value by key from the config table |
626 | * |
627 | * @access private |
628 | * @param string $key |
629 | * @return string |
630 | */ |
631 | private function _getConfig($key) |
632 | { |
633 | try { |
634 | $row = $this->_select( |
635 | 'SELECT "value" FROM "' . $this->_sanitizeIdentifier('config') . |
636 | '" WHERE "id" = ?', array($key), true |
637 | ); |
638 | } catch (PDOException $e) { |
639 | return ''; |
640 | } |
641 | return $row ? $row['value'] : ''; |
642 | } |
643 | |
644 | /** |
645 | * get the primary key clauses, depending on the database driver |
646 | * |
647 | * @access private |
648 | * @param string $key |
649 | * @return array |
650 | */ |
651 | private function _getPrimaryKeyClauses($key = 'dataid') |
652 | { |
653 | $main_key = $after_key = ''; |
654 | switch ($this->_type) { |
655 | case 'mysql': |
656 | case 'oci': |
657 | $after_key = ", PRIMARY KEY (\"$key\")"; |
658 | break; |
659 | default: |
660 | $main_key = ' PRIMARY KEY'; |
661 | break; |
662 | } |
663 | return array($main_key, $after_key); |
664 | } |
665 | |
666 | /** |
667 | * get the data type, depending on the database driver |
668 | * |
669 | * PostgreSQL and OCI uses a different API for BLOBs then SQL, hence we use TEXT and CLOB |
670 | * |
671 | * @access private |
672 | * @return string |
673 | */ |
674 | private function _getDataType() |
675 | { |
676 | switch ($this->_type) { |
677 | case 'oci': |
678 | return 'CLOB'; |
679 | case 'pgsql': |
680 | return 'TEXT'; |
681 | default: |
682 | return 'BLOB'; |
683 | } |
684 | } |
685 | |
686 | /** |
687 | * get the attachment type, depending on the database driver |
688 | * |
689 | * PostgreSQL and OCI use different APIs for BLOBs then SQL, hence we use TEXT and CLOB |
690 | * |
691 | * @access private |
692 | * @return string |
693 | */ |
694 | private function _getAttachmentType() |
695 | { |
696 | switch ($this->_type) { |
697 | case 'oci': |
698 | return 'CLOB'; |
699 | case 'pgsql': |
700 | return 'TEXT'; |
701 | default: |
702 | return 'MEDIUMBLOB'; |
703 | } |
704 | } |
705 | |
706 | /** |
707 | * get the meta type, depending on the database driver |
708 | * |
709 | * OCI doesn't accept TEXT so it has to be VARCHAR2(4000) |
710 | * |
711 | * @access private |
712 | * @return string |
713 | */ |
714 | private function _getMetaType() |
715 | { |
716 | switch ($this->_type) { |
717 | case 'oci': |
718 | return 'VARCHAR2(4000)'; |
719 | default: |
720 | return 'TEXT'; |
721 | } |
722 | } |
723 | |
724 | /** |
725 | * create the paste table |
726 | * |
727 | * @access private |
728 | */ |
729 | private function _createPasteTable() |
730 | { |
731 | list($main_key, $after_key) = $this->_getPrimaryKeyClauses(); |
732 | $dataType = $this->_getDataType(); |
733 | $attachmentType = $this->_getAttachmentType(); |
734 | $metaType = $this->_getMetaType(); |
735 | $this->_db->exec( |
736 | 'CREATE TABLE "' . $this->_sanitizeIdentifier('paste') . '" ( ' . |
737 | "\"dataid\" CHAR(16) NOT NULL$main_key, " . |
738 | "\"data\" $attachmentType, " . |
739 | '"expiredate" INT, ' . |
740 | '"opendiscussion" INT, ' . |
741 | '"burnafterreading" INT, ' . |
742 | "\"meta\" $metaType, " . |
743 | "\"attachment\" $attachmentType, " . |
744 | "\"attachmentname\" $dataType$after_key )" |
745 | ); |
746 | } |
747 | |
748 | /** |
749 | * create the paste table |
750 | * |
751 | * @access private |
752 | */ |
753 | private function _createCommentTable() |
754 | { |
755 | list($main_key, $after_key) = $this->_getPrimaryKeyClauses(); |
756 | $dataType = $this->_getDataType(); |
757 | $this->_db->exec( |
758 | 'CREATE TABLE "' . $this->_sanitizeIdentifier('comment') . '" ( ' . |
759 | "\"dataid\" CHAR(16) NOT NULL$main_key, " . |
760 | '"pasteid" CHAR(16), ' . |
761 | '"parentid" CHAR(16), ' . |
762 | "\"data\" $dataType, " . |
763 | "\"nickname\" $dataType, " . |
764 | "\"vizhash\" $dataType, " . |
765 | "\"postdate\" INT$after_key )" |
766 | ); |
767 | if ($this->_type === 'oci') { |
768 | $this->_db->exec( |
769 | 'declare |
770 | already_exists exception; |
771 | columns_indexed exception; |
772 | pragma exception_init( already_exists, -955 ); |
773 | pragma exception_init(columns_indexed, -1408); |
774 | begin |
775 | execute immediate \'create index "comment_parent" on "' . $this->_sanitizeIdentifier('comment') . '" ("pasteid")\'; |
776 | exception |
777 | when already_exists or columns_indexed then |
778 | NULL; |
779 | end;' |
780 | ); |
781 | } else { |
782 | // CREATE INDEX IF NOT EXISTS not supported as of Oracle MySQL <= 8.0 |
783 | $this->_db->exec( |
784 | 'CREATE INDEX "' . |
785 | $this->_sanitizeIdentifier('comment_parent') . '" ON "' . |
786 | $this->_sanitizeIdentifier('comment') . '" ("pasteid")' |
787 | ); |
788 | } |
789 | } |
790 | |
791 | /** |
792 | * create the paste table |
793 | * |
794 | * @access private |
795 | */ |
796 | private function _createConfigTable() |
797 | { |
798 | list($main_key, $after_key) = $this->_getPrimaryKeyClauses('id'); |
799 | $charType = $this->_type === 'oci' ? 'VARCHAR2(16)' : 'CHAR(16)'; |
800 | $textType = $this->_getMetaType(); |
801 | $this->_db->exec( |
802 | 'CREATE TABLE "' . $this->_sanitizeIdentifier('config') . |
803 | "\" ( \"id\" $charType NOT NULL$main_key, \"value\" $textType$after_key )" |
804 | ); |
805 | $this->_exec( |
806 | 'INSERT INTO "' . $this->_sanitizeIdentifier('config') . |
807 | '" VALUES(?,?)', |
808 | array('VERSION', Controller::VERSION) |
809 | ); |
810 | } |
811 | |
812 | /** |
813 | * sanitizes CLOB values used with OCI |
814 | * |
815 | * From: https://stackoverflow.com/questions/36200534/pdo-oci-into-a-clob-field |
816 | * |
817 | * @access public |
818 | * @static |
819 | * @param int|string|resource $value |
820 | * @return int|string |
821 | */ |
822 | public static function _sanitizeClob($value) |
823 | { |
824 | if (is_resource($value)) { |
825 | $value = stream_get_contents($value); |
826 | } |
827 | return $value; |
828 | } |
829 | |
830 | /** |
831 | * sanitizes identifiers |
832 | * |
833 | * @access private |
834 | * @param string $identifier |
835 | * @return string |
836 | */ |
837 | private function _sanitizeIdentifier($identifier) |
838 | { |
839 | return preg_replace('/[^A-Za-z0-9_]+/', '', $this->_prefix . $identifier); |
840 | } |
841 | |
842 | /** |
843 | * upgrade the database schema from an old version |
844 | * |
845 | * @access private |
846 | * @param string $oldversion |
847 | */ |
848 | private function _upgradeDatabase($oldversion) |
849 | { |
850 | $dataType = $this->_getDataType(); |
851 | $attachmentType = $this->_getAttachmentType(); |
852 | if (version_compare($oldversion, '0.21', '<=')) { |
853 | // create the meta column if necessary (pre 0.21 change) |
854 | try { |
855 | $this->_db->exec( |
856 | 'SELECT "meta" FROM "' . $this->_sanitizeIdentifier('paste') . '" ' . |
857 | ($this->_type === 'oci' ? 'FETCH NEXT 1 ROWS ONLY' : 'LIMIT 1') |
858 | ); |
859 | } catch (PDOException $e) { |
860 | $this->_db->exec('ALTER TABLE "' . $this->_sanitizeIdentifier('paste') . '" ADD COLUMN "meta" TEXT'); |
861 | } |
862 | // SQLite only allows one ALTER statement at a time... |
863 | $this->_db->exec( |
864 | 'ALTER TABLE "' . $this->_sanitizeIdentifier('paste') . |
865 | "\" ADD COLUMN \"attachment\" $attachmentType" |
866 | ); |
867 | $this->_db->exec( |
868 | 'ALTER TABLE "' . $this->_sanitizeIdentifier('paste') . "\" ADD COLUMN \"attachmentname\" $dataType" |
869 | ); |
870 | // SQLite doesn't support MODIFY, but it allows TEXT of similar |
871 | // size as BLOB, so there is no need to change it there |
872 | if ($this->_type !== 'sqlite') { |
873 | $this->_db->exec( |
874 | 'ALTER TABLE "' . $this->_sanitizeIdentifier('paste') . |
875 | "\" ADD PRIMARY KEY (\"dataid\"), MODIFY COLUMN \"data\" $dataType" |
876 | ); |
877 | $this->_db->exec( |
878 | 'ALTER TABLE "' . $this->_sanitizeIdentifier('comment') . |
879 | "\" ADD PRIMARY KEY (\"dataid\"), MODIFY COLUMN \"data\" $dataType, " . |
880 | "MODIFY COLUMN \"nickname\" $dataType, MODIFY COLUMN \"vizhash\" $dataType" |
881 | ); |
882 | } else { |
883 | $this->_db->exec( |
884 | 'CREATE UNIQUE INDEX IF NOT EXISTS "' . |
885 | $this->_sanitizeIdentifier('paste_dataid') . '" ON "' . |
886 | $this->_sanitizeIdentifier('paste') . '" ("dataid")' |
887 | ); |
888 | $this->_db->exec( |
889 | 'CREATE UNIQUE INDEX IF NOT EXISTS "' . |
890 | $this->_sanitizeIdentifier('comment_dataid') . '" ON "' . |
891 | $this->_sanitizeIdentifier('comment') . '" ("dataid")' |
892 | ); |
893 | } |
894 | // CREATE INDEX IF NOT EXISTS not supported as of Oracle MySQL <= 8.0 |
895 | $this->_db->exec( |
896 | 'CREATE INDEX "' . |
897 | $this->_sanitizeIdentifier('comment_parent') . '" ON "' . |
898 | $this->_sanitizeIdentifier('comment') . '" ("pasteid")' |
899 | ); |
900 | } |
901 | if (version_compare($oldversion, '1.3', '<=')) { |
902 | // SQLite doesn't support MODIFY, but it allows TEXT of similar |
903 | // size as BLOB and PostgreSQL uses TEXT, so there is no need |
904 | // to change it there |
905 | if ($this->_type !== 'sqlite' && $this->_type !== 'pgsql') { |
906 | $this->_db->exec( |
907 | 'ALTER TABLE "' . $this->_sanitizeIdentifier('paste') . |
908 | "\" MODIFY COLUMN \"data\" $attachmentType" |
909 | ); |
910 | } |
911 | } |
912 | if (version_compare($oldversion, '1.7.1', '<=')) { |
913 | $supportsDropColumn = true; |
914 | if ($this->_type === 'sqlite') { |
915 | try { |
916 | $row = $this->_select('SELECT sqlite_version() AS "v"', array(), true); |
917 | $supportsDropColumn = version_compare($row['v'], '3.35.0', '>='); |
918 | } catch (PDOException $e) { |
919 | $supportsDropColumn = false; |
920 | } |
921 | } |
922 | if ($supportsDropColumn) { |
923 | $this->_db->exec( |
924 | 'ALTER TABLE "' . $this->_sanitizeIdentifier('paste') . |
925 | '" DROP COLUMN "postdate"' |
926 | ); |
927 | } |
928 | } |
929 | $this->_exec( |
930 | 'UPDATE "' . $this->_sanitizeIdentifier('config') . |
931 | '" SET "value" = ? WHERE "id" = ?', |
932 | array(Controller::VERSION, 'VERSION') |
933 | ); |
934 | } |
935 | } |