Ticket #1863: MagnaDB.php

File MagnaDB.php, 38.1 KB (added by Torsten Riemer, 5 years ago)
Line 
1<?php
2/**
3 * 888888ba dP .88888. dP
4 * 88 `8b 88 d8' `88 88
5 * 88aaaa8P' .d8888b. .d888b88 88 .d8888b. .d8888b. 88 .dP .d8888b.
6 * 88 `8b. 88ooood8 88' `88 88 YP88 88ooood8 88' `"" 88888" 88' `88
7 * 88 88 88. ... 88. .88 Y8. .88 88. ... 88. ... 88 `8b. 88. .88
8 * dP dP `88888P' `88888P8 `88888' `88888P' `88888P' dP `YP `88888P'
9 *
10 * m a g n a l i s t e r
11 * boost your Online-Shop
12 *
13 * -----------------------------------------------------------------------------
14 * $Id$
15 *
16 * (c) 2010 - 2013 RedGecko GmbH -- http://www.redgecko.de
17 * Released under the MIT License (Expat)
18 * -----------------------------------------------------------------------------
19 */
20
21defined('_VALID_XTC') or die('Direct Access to this location is not allowed.');
22
23
24define('MAGNADB_ENABLE_LOGGING', MAGNA_DEBUG && false);
25
26abstract class MagnaDBDriver {
27 protected $charset = '';
28
29 abstract public function __construct($access);
30
31 abstract public function isConnected();
32 abstract public function connect();
33 abstract public function close();
34 abstract public function getLastErrorMessage();
35 abstract public function getLastErrorNumber();
36 abstract public function getServerInfo();
37 abstract public function setCharset($charset);
38 abstract public function query($query);
39 abstract public function escape($str);
40 abstract public function affectedRows();
41 abstract public function getInsertId();
42 abstract public function isResult($m);
43 abstract public function numRows($result);
44 abstract public function fetchArray($result);
45 abstract public function freeResult($result);
46
47 public function getDriverDetails() {
48 $access = $this->access;
49 unset($access['user']);
50 unset($access['pass']);
51 return $access;
52 }
53
54 /**
55 * mimics mysql_real_escape_string
56 */
57 public static function fallbackEscape($str) {
58 return str_replace(
59 array('\\', "\0", "\n", "\r", "'", '"', "\x1a"),
60 array('\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z' ),
61 $str
62 );
63 }
64}
65
66class MagnaDBDriverMysqli extends MagnaDBDriver {
67 protected $oInstance = null;
68
69 protected $access = array(
70 'type' => '', // [pipe|socket|tcpip]
71 'host' => '',
72 'user' => '',
73 'pass' => '',
74 'port' => '', // will only be explicitly set for tcpip connections
75 'sock' => '', // will only be explicitly set for non tcpip connections, includes windows pipes
76 'persistent' => false,
77 );
78
79 public function __construct($access) {
80 $this->access = array_merge($this->access, $access);
81 $this->detectConnectionType();
82 }
83
84 protected function detectConnectionType() {
85 if (strpos($this->access['host'], '\\') !== false) {
86 $this->access['type'] = 'pipe'; // Windows named pipe based connection. e.g. \\.\pipe\MySQL
87 $this->access['sock'] = $this->access['host'];
88 $this->access['host'] = '.';
89 } else if (strpos($this->access['host'], '.sock') !== false) {
90 // modified >= 2.0.5.0:
91 $blMsock3 = false;
92 if (is_file(DIR_FS_INC.'get_database_version.inc.php')) {
93 require_once(DIR_FS_INC.'get_database_version.inc.php');
94 $modified_shop_version = get_database_version();
95 if ($shop_version['plain'] >= '2.0.5.0') {
96 $blMsock3 = true;
97 }
98 }
99 $this->access['type'] = 'socket'; // Unix domain sockets use the file system as their address name space.
100 $msock = array();
101 if (preg_match('/^([^\:]+)\:(.*)$/', $this->access['host'], $msock)) {
102 $this->access['host'] = $msock[1];
103 $this->access['sock'] = $msock[2];
104 if ($blMsock3) {
105 $this->access['sock'] = $msock[3];
106 }
107 } else {
108 $this->access['sock'] = $this->access['host'];
109 $this->access['host'] = '';
110 }
111 } else {
112 $this->access['type'] = 'tcpip';
113 $mport = array();
114 if (preg_match('/^[^\:]+\:([0-9]+)$/', $this->access['host'], $mport)) {
115 $this->access['port'] = (int)$mport[1];
116 $this->access['host'] = str_replace(':'.$this->access['port'], '', $this->access['host']);
117 } else {
118 $this->access['port'] = (int)ini_get('mysqli.default_port');
119 }
120 if (empty($this->access['port'])) {
121 $this->access['port'] = 3306;
122 }
123 }
124 // for non tcpip connections
125 if (empty($this->access['port'])) {
126 $this->access['port'] = (int)ini_get('mysqli.default_port');
127 }
128 }
129
130 public function isConnected() {
131 try {
132 // there seems to be no other way than to surpress the error message
133 // in order to detect that the connection has been closed,
134 // which is a shame.
135 //*/
136 return is_object($this->oInstance) && @$this->oInstance->ping();
137
138 /*/
139 ob_start();
140 $status = is_object($this->oInstance) && $this->oInstance->ping();
141 $err = ob_get_clean();
142 echo $err;
143
144 if (!empty($err)) {
145 var_dump($status);
146 echo print_m(MagnaDB::gi()->stripObjectsAndResources(debug_backtrace(true)));
147 die();
148 }
149
150 return $status;
151 //*/
152 } catch (Exception $e) {
153 var_dump($e);
154 return false;
155 }
156 }
157
158 public function connect() {
159 ob_start();
160 switch ($this->access['type']) {
161 case 'socket':
162 case 'pipe': {
163 $this->oInstance = new mysqli(
164 ($this->access['persistent'] ? 'p:' : '').$this->access['host'],
165 $this->access['user'], $this->access['pass'], '', (int)$this->access['port'],
166 $this->access['sock']
167 );
168 break;
169 }
170 case 'tcpip':
171 default: {
172 $this->oInstance = new mysqli(
173 ($this->access['persistent'] ? 'p:' : '').$this->access['host'],
174 $this->access['user'], $this->access['pass'], '', (int)$this->access['port']
175 );
176 break;
177 }
178 }
179 $warn = ob_get_clean();
180
181 if (!$this->isConnected()) {
182 if (($this->access['type'] == 'tcpip') && ($this->access['host'] == 'localhost')) {
183 // Fix for broken estugo php config.
184 //
185 // From: http://stackoverflow.com/questions/13870362/php-mysql-test-database-server
186 //
187 // This seems to be a common issue, as googling for it yields quite a few results.
188 // I experienced this on my two linux boxes as well (never under Windows though) and
189 // at some point I resolved to just use 127.0.0.1 on all dev servers. Basically,
190 // localhost makes the connection to the MySQL server use a socket, but your
191 // configuration doesn't point to the socket file correctly.
192
193 $this->access['host'] = '127.0.0.1';
194 return $this->connect();
195 }
196 echo $warn;
197 return;
198 }
199
200 if (!empty($this->charset)) {
201 $this->setCharset($this->charset);
202 }
203 }
204
205 public function close() {
206 $success = false;
207 if (is_object($this->oInstance) && is_callable(array($this->oInstance, 'close'))) {
208 $success = $this->oInstance->close();
209 }
210 $this->oInstance = null;
211 return $success;
212 }
213
214 public function getLastErrorMessage() {
215 if (is_object($this->oInstance) && isset($this->oInstance->error)) {
216 return $this->oInstance->error;
217 }
218 return '';
219 }
220
221 public function getLastErrorNumber() {
222 if (is_object($this->oInstance) && isset($this->oInstance->errno)) {
223 return $this->oInstance->errno;
224 }
225 return 0;
226 }
227
228 public function getServerInfo() {
229 if ($this->isConnected()) {
230 return $this->oInstance->server_info;
231 }
232 return false;
233 }
234
235 public function setCharset($charset) {
236 $this->charset = $charset;
237 if ($this->isConnected()) {
238 $this->oInstance->set_charset($this->charset);
239 }
240 }
241
242 public function query($query) {
243 if ($this->isConnected()) {
244 return $this->oInstance->query($query);
245 }
246 return false;
247 }
248
249 public function escape($str) {
250 if ($this->isConnected()) {
251 return $this->oInstance->real_escape_string($str);
252 }
253 return self::fallbackEscape($str);
254 }
255
256 public function affectedRows() {
257 if (is_object($this->oInstance) && isset($this->oInstance->affected_rows)) {
258 return $this->oInstance->affected_rows;
259 }
260 // re-establishing a connection doesn't make sense here as the new connection
261 // can't return the affected row count of the old connection.
262 return false;
263 }
264
265 public function getInsertId() {
266 if ($this->isConnected()) {
267 return $this->oInstance->insert_id;
268 }
269 // same reason as in $this->affectedRows();
270 return false;
271 }
272
273 public function isResult($m) {
274 return $m instanceof mysqli_result;
275 }
276
277 public function numRows($result) {
278 return $result->num_rows;
279 }
280
281 public function fetchArray($result) {
282 return $result->fetch_array(MYSQLI_ASSOC);
283 }
284
285 public function freeResult($result) {
286 if (!is_object($result)) {
287 return;
288 }
289 return $result->free_result();
290 }
291}
292
293class MagnaDBDriverMysql extends MagnaDBDriver {
294 protected $rLink = null;
295 protected $resourceValid = false;
296 protected $resourceIsShared = false;
297
298 protected $access = array(
299 'host' => '',
300 'user' => '',
301 'pass' => '',
302 'persistent' => false,
303 );
304
305 public function __construct($access) {
306 $this->access = array_merge($this->access, $access);
307 }
308
309 public function isConnected() {
310 return is_resource($this->rLink) && mysql_ping($this->rLink);
311 }
312
313 public function connect($force = false) {
314 global $db_link;
315
316 $this->rLink = $this->access['persistent']
317 ? mysql_pconnect($this->access['host'], $this->access['user'], $this->access['pass'])
318 : mysql_connect($this->access['host'], $this->access['user'], $this->access['pass']);
319
320 $this->resourceValid = is_resource($this->rLink);
321
322 // Passiert nur beim reconnect.
323 if ($this->resourceIsShared && ($db_link !== $this->rLink)) {
324 $db_link = $this->rLink;
325 }
326
327 // Passiert nur beim initial connect.
328 if ($db_link === $this->rLink) {
329 $this->resourceIsShared = true;
330 }
331
332 if (!empty($this->charset)) {
333 $this->setCharset($this->charset);
334 }
335 }
336
337 public function close() {
338 $success = false;
339 if (is_resource($this->rLink)) {
340 $success = mysql_close($this->rLink);
341 }
342
343 $this->resourceValid = false;
344
345 return $success;
346 }
347
348 public function getLastErrorMessage() {
349 if (is_resource($this->rLink)) {
350 return mysql_error($this->rLink);
351 }
352 return '';
353 }
354
355 public function getLastErrorNumber() {
356 if (is_resource($this->rLink)) {
357 return mysql_errno($this->rLink);
358 }
359 return 0;
360 }
361
362 public function getServerInfo() {
363 if ($this->isConnected()) {
364 return mysql_get_server_info($this->rLink);
365 }
366 return false;
367 }
368
369 public function setCharset($charset) {
370 $this->charset = $charset;
371 if ($this->isConnected()) {
372 if (function_exists('mysql_set_charset')) {
373 mysql_set_charset($this->charset, $this->rLink);
374 } else {
375 $this->query('SET NAMES '.$this->charset);
376 }
377 }
378 return false;
379 }
380
381 public function query($query) {
382 if ($this->isConnected()) {
383 return mysql_query($query, $this->rLink);
384 }
385 return false;
386 }
387
388 public function escape($str) {
389 if ($this->isConnected()) {
390 return mysql_real_escape_string($str, $this->rLink);
391 }
392 return self::fallbackEscape($str);
393 }
394
395 public function affectedRows() {
396 if ($this->isConnected()) {
397 mysql_affected_rows($this->rLink);
398 }
399 // re-establishing a connection doesn't make sense here as the new connection
400 // can't return the affected row count of the old connection.
401 return false;
402 }
403
404 public function getInsertId() {
405 if ($this->isConnected()) {
406 return mysql_insert_id($this->rLink);
407 }
408 // same reason as in $this->affectedRows();
409 return false;
410 }
411
412 public function isResult($m) {
413 return is_resource($m);
414 }
415
416 public function numRows($result) {
417 return mysql_num_rows($result);
418 }
419
420 public function fetchArray($result) {
421 return mysql_fetch_array($result, MYSQL_ASSOC);
422 }
423
424 public function freeResult($result) {
425 return mysql_free_result($result);
426 }
427}
428
429class MagnaDB {
430 protected static $instance = null;
431 protected $destructed = false;
432
433 protected $driver = null; // instanceof mysqli or mysql driver
434
435 protected $access = array (
436 'host' => '',
437 'user' => '',
438 'pass' => '',
439 'persistent' => false,
440 );
441 protected $database = '';
442
443 protected $query = '';
444 protected $error = '';
445 protected $result = null;
446 protected $inTransaction = false;
447
448 protected $sqlErrors = array();
449
450 protected $start = 0;
451 protected $count = 0;
452 protected $querytime = 0;
453 protected $doLogQueryTimes = true;
454 protected $timePerQuery = array();
455
456 protected $availabeTables = array();
457
458 protected $escapeStrings = false;
459
460 protected $sessionLifetime;
461
462 protected $showDebugOutput = MAGNA_DEBUG;
463
464 /* Caches */
465 protected $tableColumnsCache = array();
466 protected $columnExistsInTableCache = array();
467
468 /**
469 * Class constructor
470 */
471 protected function __construct() {
472 $this->start = microtime(true);
473 $this->count = 0;
474 $this->querytime = 0;
475 $this->setEscapeStrings();
476
477 $this->access['host'] = DB_SERVER;
478 $this->access['user'] = DB_SERVER_USERNAME;
479 $this->access['pass'] = DB_SERVER_PASSWORD;
480 $this->access['persistent'] = (defined('USE_PCONNECT') && (strtolower(USE_PCONNECT) == 'true'));
481
482 $driverClass = $this->selectDriver();
483 $this->driver = new $driverClass($this->access);
484
485 $this->database = DB_DATABASE;
486
487 $this->timePerQuery[] = array (
488 'query' => 'Driver: "'.get_class($this->driver).'" ('.$this->getDriverDetails().')',
489 'time' => 0
490 );
491
492 $this->selfConnect(false, true);
493
494 if (MAGNADB_ENABLE_LOGGING) {
495 $dbt = @debug_backtrace();
496 if (!empty($dbt)) {
497 foreach ($dbt as $step) {
498 if (strpos($step['file'], 'magnaCallback') !== false) {
499 $dbt = true;
500 unset($step);
501 break;
502 }
503 }
504 }
505 if ($dbt !== true) {
506 file_put_contents(dirname(__FILE__).'/db_guery.log', "### Query Log ".date("Y-m-d H:i:s")." ###\n\n");
507 }
508 unset($dbt);
509 }
510
511 $this->reloadTables();
512
513 $this->initSession();
514 }
515
516 /**
517 * disables / enables stripslashes in escape()
518 * @param bool $bl
519 * @param null $bl default value
520 * @return \MagnaDB
521 */
522 public function setEscapeStrings ($bl = null) {
523 // magic quotes are deprecated as of php 5.4
524 $this->escapeStrings = $bl === null ? get_magic_quotes_gpc() : $bl;
525 return $this;
526 }
527
528 protected function selectDriver() {
529 // we prefer mysqli only for php 5.3 or greater as this version introduces persistent connections
530 $driver = (function_exists('mysqli_query') && defined('PHP_VERSION_ID') && (PHP_VERSION_ID >= 50300))
531 ? 'MagnaDBDriverMysqli'
532 : 'MagnaDBDriverMysql';
533
534 // "Modified" specific hack, will be refactored soon!
535 if (defined('DB_MYSQL_TYPE') && (DB_MYSQL_TYPE === 'mysql')) {
536 $driver = 'MagnaDBDriverMysql';
537 }
538 return $driver;
539 }
540
541 protected function getDriverDetails() {
542 $data = $this->driver->getDriverDetails();
543 $info = '';
544 foreach ($data as $key => $value) {
545 $info .= '"'.$key.'": "'.$value.'", ';
546 }
547 $info = rtrim($info, ', ').'';
548 return $info;
549 }
550
551 /**
552 * @return MagnaDB Singleton - gets Instance
553 */
554 public static function gi() {
555 if (self::$instance == NULL) {
556 self::$instance = new self();
557 }
558 return self::$instance;
559 }
560
561 protected function __clone() {}
562
563 public function __destruct() {
564 if (!is_object($this) || !isset($this->destructed) || $this->destructed) {
565 return;
566 }
567 $this->destructed = true;
568
569 if (!defined('MAGNALISTER_PASSPHRASE') && !defined('MAGNALISTER_PLUGIN')) {
570 /* Only when this class is instantiated from magnaCallback
571 and the plugin isn't activated yet.
572 */
573 $this->closeConnection();
574 return;
575 }
576
577 $this->sessionRefresh();
578
579 if (MAGNA_DEBUG && $this->showDebugOutput && function_exists('microtime2human')
580 && (
581 !defined('MAGNA_CALLBACK_MODE') || (MAGNA_CALLBACK_MODE != 'UTILITY')
582 ) && (stripos($_SERVER['PHP_SELF'].serialize($_GET), 'ajax') === false)
583 ) {
584 echo '<!-- Final Stats :: QC:'.$this->getQueryCount().'; QT:'.microtime2human($this->getRealQueryTime()).'; -->';
585 }
586 $this->closeConnection();
587 }
588
589 public function selectDatabase($db) {
590 $this->query('USE `'.$db.'`');
591 }
592
593 protected function isConnected() {
594 return $this->driver->isConnected();
595 }
596
597 protected function selfConnect($forceReconnect = false, $initialConnect = false) {
598 # Wenn keine Verbindung im klassischen Sinne besteht, selbst eine herstellen.
599 if ($this->driver->isConnected() && !$forceReconnect) {
600 return false;
601 }
602
603 // Try to connect...
604 $error = '';
605 $errno = 0;
606
607 $attempts = 0;
608 $maxAttempts = $initialConnect ? 2 : 100;
609 do {
610 $this->driver->connect();
611 if (!$this->isConnected()) {
612 $errno = $this->driver->getLastErrorNumber();
613 $error = $this->driver->getLastErrorMessage();
614 } else {
615 break;
616 }
617 $this->closeConnection(true);
618
619 usleep(100000); // 100ms
620 # Retry if '2006 MySQL server has gone away'
621 } while (++$attempts < $maxAttempts);
622
623 if (!$initialConnect
624 && isset($_GET['MLDEBUG']) && ($_GET['MLDEBUG'] === 'true')
625 && isset($_GET['LEVEL']) && (strtolower($_GET['LEVEL']) == 'high')
626 ) {
627 echo "\n<<<< MagnaDB :: reconnect >>>>\n";
628 }
629
630 if (!$this->isConnected()) {
631 // called in the destructor: Just leave. No need to close connection, it's lost
632 if ($this->destructed) {
633 exit();
634 }
635
636 // die is bad behaviour. But meh..
637 die(
638 '<span style="color:#000000;font-weight:bold;">
639 <small style="color:#ff0000;font-weight:bold;">[SQL Error]</small><br>
640 Establishing a connection to the database failed.<br><br>
641 <pre style="font-weight:normal">Giving up after '.$attempts.' attempts. Last error message received:'."\n".'('.$errno.') '.$error.'</pre>
642 <pre style="font-weight:normal">'.htmlspecialchars(
643 print_r($this->stripObjectsAndResources(array_slice(debug_backtrace(true), 4)), true)
644 ).'</pre>
645 </span>'
646 );
647 }
648 $vers = $this->driver->getServerInfo();
649 if (substr($vers, 0, 1) > 4) {
650 $this->query("SET SESSION sql_mode=''");
651 }
652 $this->selectDatabase($this->database);
653
654 return true;
655 }
656
657 protected function closeConnection($force = false) {
658 if ( $force
659 || ($this->isConnected() && !(defined('USE_PCONNECT') && (strtolower(USE_PCONNECT) == 'true')))
660 ) {
661 if (is_object($this->driver)) {
662 $this->driver->close();
663 }
664 }
665 }
666
667 protected function prepareError() {
668 $errNo = $this->driver->getLastErrorNumber();
669 if ($errNo == 0) {
670 return '';
671 }
672 return $this->driver->getLastErrorMessage().' ('.$errNo.')';
673 }
674
675 public function logQueryTimes($b) {
676 $this->doLogQueryTimes = $b;
677 }
678
679 public function stripObjectsAndResources($a, $lv = 0) {
680 if (empty($a) || ($lv >= 10)) return $a;
681 //echo print_m($a, trim(var_dump_pre($lv, true)));
682 $aa = array();
683 foreach ($a as $k => $value) {
684 $toString = '';
685 // echo var_dump_pre($value, 'value');
686 if (!is_object($value) && !is_array($value)) {
687 $toString = $value.'';
688 }
689 if (is_object($value)) {
690 $value = 'OBJECT ('.get_class($value).')';
691 } else if (is_resource($value) || (strpos($toString, 'Resource') !== false)) {
692 if (is_resource($value)) {
693 $value = 'RESOURCE ('.get_resource_type($value).')';
694 } else {
695 $value = $toString.' (Unknown)';
696 }
697 } else if (is_array($value)) {
698 $value = $this->stripObjectsAndResources($value, $lv + 1);
699 } else if (is_string($value)) {
700 if (defined('DIR_FS_DOCUMENT_ROOT')) {
701 $value = str_replace(dirname(DIR_FS_DOCUMENT_ROOT), '', $value);
702 }
703 }
704 if ($k == 'args') {
705 if (is_string($value) && (strlen($value) > 5000)) {
706 $value = substr($value, 0, 5000).'[...]';
707 }
708 }
709 if (($value === $this->access['pass']) && ($this->access['pass'] != null)) {
710 $aa = '*****';
711 break;
712 }
713 $aa[$k] = $value;
714 }
715 return $aa;
716 }
717
718 protected function fatalError($query, $errno, $error, $fatal = false) {
719 $backtrace = $this->stripObjectsAndResources(debug_backtrace(true));
720 $this->sqlErrors[] = array (
721 'Query' => rtrim(trim($query, "\n")),
722 'Error' => $error,
723 'ErrNo' => $errno,
724 'Backtrace' => $backtrace
725 );
726
727 if ($fatal) {
728 die(
729 '<span style="color:#000000;font-weight:bold;">
730 ' . $errno . ' - ' . $error . '<br /><br />
731 <pre>' . $query . '</pre><br /><br />
732 <pre style="font-weight:normal">'.htmlspecialchars(
733 print_r($backtrace, true)
734 ).'</pre><br /><br />
735 <small style="color:#ff0000;font-weight:bold;">[SQL Error]</small>
736 </span>'
737 );
738 }
739 }
740
741 protected function execQuery($query) {
742 $i = 8;
743
744 $errno = 0;
745
746 $this->selfConnect();
747
748 do {
749 $errno = 0;
750 $result = $this->driver->query($query);
751 if ($result === false) {
752 $errno = $this->driver->getLastErrorNumber();
753 }
754 //if (defined('MAGNALISTER_PLUGIN')) echo 'mmysql_query errorno: '.var_export($errno, true)."\n";
755 if (($errno === false) || ($errno == 2006)) {
756 $this->closeConnection(true);
757 usleep(100000); // 100ms
758 $this->selfConnect(true);
759 }
760 # Retry if '2006 MySQL server has gone away'
761 } while (($errno == 2006) && (--$i >= 0));
762
763 if ($errno != 0) {
764 $this->fatalError($query, $errno, $this->driver->getLastErrorMessage());
765 }
766
767 return $result;
768 }
769
770 /**
771 * Send a query
772 */
773 public function query($query, $verbose = false) {
774 /* {Hook} "MagnaDB_Query": Enables you to extend, modify or log query that goes to the database .<br>
775 Variables that can be used: <ul><li>$query: The SQL string</li></ul>
776 */
777 if (function_exists('magnaContribVerify') && (($hp = magnaContribVerify('MagnaDB_Query', 1)) !== false)) {
778 require($hp);
779 }
780
781 $this->query = $query;
782 if ($verbose || false) {
783 echo function_exists('print_m') ? print_m($this->query)."\n" : $this->query."\n";
784 }
785 if (MAGNADB_ENABLE_LOGGING) {
786 file_put_contents(dirname(__FILE__).'/db_guery.log', "### ".$this->count."\n".$this->query."\n\n", FILE_APPEND);
787 }
788 $t = microtime(true);
789 $this->result = $this->execQuery($this->query);
790 $t = microtime(true) - $t;
791 $this->querytime += $t;
792 if ($this->doLogQueryTimes) {
793 $this->timePerQuery[] = array (
794 'query' => $this->query,
795 'time' => $t
796 );
797 }
798 ++$this->count;
799 //echo print_m(debug_backtrace());
800 if (!$this->result) {
801 $this->error = $this->prepareError();
802 return false;
803 }
804
805 return $this->result;
806 }
807
808
809 /**
810 * Set the isolation level for the next transaction. So call *BEFORE* beginTransation()!
811 * @param $level: Can be either READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ or SERIALIZABLE
812 */
813 public function setTransactionLevel($level, $commit = false) {
814 try {
815 $this->query('SET TRANSACTION ISOLATION LEVEL '.$level);
816 } catch (DatabaseException $dbe) {
817 if ($dbe->getMySqlErrNo() == 1568) {
818 if ($commit) {
819 $this->commit();
820 } else {
821 $this->rollback();
822 }
823 } else {
824 throw $dbe;
825 }
826 }
827 }
828
829 public function getTransactionLevel() {
830 return $this->fetchOne('SELECT @@session.tx_isolation');
831 }
832
833 /**
834 * Begins a transaction. The parameter sets the isolation level. If omitted it won't be set for this
835 * transaction. The global isolation level of mysql will be used.
836 */
837 public function beginTransaction($level = false, $commit = false) {
838 if ($level !== false) {
839 $this->setTransactionLevel($level, $commit);
840 }
841 $this->query('BEGIN');
842 $this->inTransaction = true;
843 }
844
845 public function commit() {
846 $this->query('COMMIT');
847 $this->inTransaction = false;
848 }
849
850 public function rollback() {
851 $this->query('ROLLBACK');
852 $this->inTransaction = false;
853 }
854
855 public function isInTransaction() {
856 return $this->inTransaction;
857 }
858
859
860 public function setCharset($charset) {
861 $this->driver->setCharset($charset);
862 }
863
864 protected function sessionGarbageCollector() {
865 if ($this->tableExists(TABLE_MAGNA_SESSION)) {
866 $this->query("DELETE FROM ".TABLE_MAGNA_SESSION." WHERE expire < '".(time() - $this->sessionLifetime)."' AND session_id <> '0'");
867 }
868 if (defined('MAGNALISTER_PLUGIN') && MAGNALISTER_PLUGIN && $this->tableExists(TABLE_MAGNA_SELECTION)) {
869 $this->query("DELETE FROM ".TABLE_MAGNA_SELECTION." WHERE expires < '".gmdate('Y-m-d H:i:d', (time() - $this->sessionLifetime))."'");
870 }
871 }
872
873 protected function sessionRead() {
874 $result = $this->fetchOne('
875 SELECT data FROM '.TABLE_MAGNA_SESSION.'
876 WHERE session_id = "'.session_id().'"
877 AND expire > "'.time().'"
878 ', true);
879 if (!empty($result)) {
880 return @unserialize($result);
881 }
882 return array();
883 }
884
885 protected function shopSessionRead() {
886 /* This "Session" is for all Backend users and it _never_ expires! */
887 $result = $this->fetchOne('
888 SELECT data FROM '.TABLE_MAGNA_SESSION.'
889 WHERE session_id = "0"
890 ', true);
891
892 if (!empty($result)) {
893 return @unserialize($result);
894 }
895 return array();
896 }
897
898 protected function initSession() {
899 global $_MagnaSession, $_MagnaShopSession;
900
901 if (!defined('TABLE_MAGNA_SESSION')) define('TABLE_MAGNA_SESSION', 'magnalister_session');
902 if ($this->tableExists(TABLE_MAGNA_SESSION)) {
903 $this->sessionLifetime = (int)ini_get("session.gc_maxlifetime");
904 $this->sessionGarbageCollector();
905
906 $_MagnaSession = $this->sessionRead();
907 $_MagnaShopSession = $this->shopSessionRead();
908 }
909 }
910
911 protected function sessionStore($data, $sessionID) {
912 if (empty($sessionID) && ($sessionID != '0')) return;
913
914 $isPluginContext = defined('MAGNALISTER_PLUGIN') && MAGNALISTER_PLUGIN;
915
916 // only update the session if this class was used from the plugin context
917 // OR if the dirty bit is set. Avoid session updates otherwise.
918 if (!($isPluginContext || (isset($data['__dirty']) && ($data['__dirty'] === true)))) {
919 return;
920 }
921 // remove the dirty bit.
922 if (isset($data['__dirty'])) {
923 unset($data['__dirty']);
924 }
925 if ($this->recordExists(TABLE_MAGNA_SESSION, array('session_id' => $sessionID))) {
926 $this->update(TABLE_MAGNA_SESSION, array(
927 'data' => serialize($data),
928 'expire' => (time() + (($sessionID == '0') ? 0 : $this->sessionLifetime))
929 ), array(
930 'session_id' => $sessionID
931 ));
932 } else if (!empty($data)) {
933 $this->insert(TABLE_MAGNA_SESSION, array(
934 'session_id' => $sessionID,
935 'data' => serialize($data),
936 'expire' => (time() + (($sessionID == '0') ? 0 : $this->sessionLifetime))
937 ), true);
938 }
939 }
940
941 protected function sessionRefresh() {
942 global $_MagnaSession, $_MagnaShopSession;
943
944 if ($this->tableExists(TABLE_MAGNA_SESSION)) {
945 $this->sessionStore($_MagnaSession, session_id());
946 $this->sessionStore($_MagnaShopSession, '0');
947 }
948
949 // only refresh selection data in magnalister_selection if this class was used from the plugin context
950 if (defined('MAGNALISTER_PLUGIN') && MAGNALISTER_PLUGIN && $this->tableExists(TABLE_MAGNA_SELECTION)) {
951 $this->update(TABLE_MAGNA_SELECTION, array(
952 'expires' => gmdate('Y-m-d H:i:d', (time() + $this->sessionLifetime))
953 ), array(
954 'session_id' => session_id()
955 ));
956 }
957 }
958
959 public function escape($object) {
960 if (is_array($object)) {
961 $object = array_map(array($this, 'escape'), $object);
962 } else if (is_string($object)) {
963 $tObject = $this->escapeStrings ? stripslashes($object) : $object;
964 if ($this->isConnected()) {
965 $object = $this->driver->escape($tObject);
966 } else {
967 $object = MagnaDBDriver::fallbackEscape($tObject);
968 }
969 }
970 return $object;
971 }
972
973 /**
974 * Get number of rows
975 */
976 public function numRows($result = null) {
977 if ($result === null) {
978 $result = $this->result;
979 }
980
981 if ($result === false) {
982 return false;
983 }
984
985 return $this->driver->numRows($result);
986 }
987
988 /**
989 * Get number of changed/affected rows
990 */
991 public function affectedRows() {
992 return $this->driver->affectedRows();
993 }
994
995 /**
996 * Get number of found rows
997 */
998 public function foundRows() {
999 return $this->fetchOne("SELECT FOUND_ROWS()");
1000 }
1001
1002 /**
1003 * Get a single value
1004 */
1005 public function fetchOne($query) {
1006 $this->result = $this->query($query);
1007
1008 if (!$this->result) {
1009 return false;
1010 }
1011
1012 if ($this->numRows($this->result) > 1) {
1013 $this->error = __METHOD__.' can only return a single value (multiple rows returned).';
1014 return false;
1015
1016 } else if ($this->numRows($this->result) < 1) {
1017 $this->error = __METHOD__.' cannot return a value (zero rows returned).';
1018 return false;
1019 }
1020
1021 $return = $this->fetchNext($this->result);
1022 if (!is_array($return) || empty($return)) {
1023 return false;
1024 }
1025 $return = array_shift($return);
1026 if ($return === null) {
1027 return false;
1028 }
1029 return $return;
1030 }
1031
1032 /**
1033 * Get next row of a result
1034 */
1035 public function fetchNext($result = null) {
1036 if ($result === null) {
1037 $result = $this->result;
1038 }
1039
1040 if ($this->numRows($result) < 1) {
1041 return false;
1042 } else {
1043 $row = $this->driver->fetchArray($result);
1044 if (!$row) {
1045 $this->error = $this->prepareError();
1046 return false;
1047 }
1048 }
1049
1050 return $row;
1051 }
1052
1053 /**
1054 * Fetch a row
1055 */
1056 public function fetchRow($query) {
1057 $this->result = $this->query($query);
1058
1059 return $this->fetchNext($this->result);
1060 }
1061
1062 public function fetchArray($query, $singleField = false) {
1063 if ($this->driver->isResult($query)) {
1064 $this->result = $query;
1065 } else if (is_string($query)) {
1066 $this->result = $this->query($query);
1067 }
1068
1069 if (!$this->result) {
1070 return false;
1071 }
1072
1073 $array = array();
1074
1075 while ($row = $this->fetchNext($this->result)) {
1076 if ($singleField && (count($row) == 1)) {
1077 $array[] = array_pop($row);
1078 } else {
1079 $array[] = $row;
1080 }
1081 }
1082
1083 return $array;
1084 }
1085
1086 protected function reloadTables() {
1087 $this->availabeTables = $this->fetchArray('SHOW TABLES', true);
1088 }
1089
1090 public function tableExists($table, $purge = false) {
1091 if ($purge) {
1092 $this->reloadTables();
1093 }
1094 /* {Hook} "MagnaDB_TableExists": Enables you to modify the $table variable before the check for existance is performed in
1095 case your shop uses a contrib, that messes with the table prefixes.
1096 */
1097 if (function_exists('magnaContribVerify') && (($hp = magnaContribVerify('MagnaDB_TableExists', 1)) !== false)) {
1098 require($hp);
1099 }
1100 return in_array($table, $this->availabeTables);
1101 }
1102
1103 public function getAvailableTables($pattern = '', $purge = false) {
1104 if ($purge) {
1105 $this->reloadTables();
1106 }
1107 if (empty($pattern)) {
1108 return $this->availabeTables;
1109 }
1110 $tbls = array();
1111 foreach ($this->availabeTables as $t) {
1112 if (preg_match($pattern, $t)) {
1113 $tbls[] = $t;
1114 }
1115 }
1116 return $tbls;
1117 }
1118
1119 public function tableEmpty($table) {
1120 return ($this->fetchOne('SELECT * FROM '.$table.' LIMIT 1') === false);
1121 }
1122
1123 public function mysqlVariableValue($variable) {
1124 $showVariablesLikeVariable = $this->fetchRow("SHOW VARIABLES LIKE '$variable'");
1125 if ($showVariablesLikeVariable) {
1126 return $showVariablesLikeVariable['Value'];
1127 }
1128 # nicht false zurueckgeben, denn dies koennte ein gueltiger Variablenwert sein
1129 return null;
1130 }
1131
1132 public function mysqlSetHigherTimeout($timeoutToSet = 3600) {
1133 if ($this->mysqlVariableValue('wait_timeout') < $timeoutToSet) {
1134 $this->query("SET wait_timeout = $timeoutToSet");
1135 }
1136 if ($this->mysqlVariableValue('interactive_timeout') < $timeoutToSet) {
1137 $this->query("SET interactive_timeout = $timeoutToSet");
1138 }
1139 }
1140
1141 public function tableEncoding($table) {
1142 $showCreateTable = $this->fetchRow('SHOW CREATE TABLE `'.$table.'`');
1143 if (preg_match("/CHARSET=([^\s]*).*/", $showCreateTable['Create Table'], $matched)) {
1144 return $matched[1];
1145 }
1146 $charSet = $this->mysqlVariableValue('character_set_database');
1147 if (empty($charSet)) return false;
1148 return $charSet;
1149 }
1150
1151
1152 public function getTableColumns($table) {
1153 if (isset($this->tableColumnsCache[$table])) {
1154 return $this->tableColumnsCache[$table];
1155 }
1156 $columns = $this->fetchArray('DESC '.$table);
1157 if (!is_array($columns) || empty($columns)) {
1158 return false;
1159 }
1160 $this->tableColumnsCache[$table] = array();
1161 foreach ($columns as $column_description) {
1162 $this->tableColumnsCache[$table][] = $column_description['Field'];
1163 }
1164 return $this->tableColumnsCache[$table];
1165 }
1166
1167 public function columnExistsInTable($column, $table) {
1168 if (isset($this->columnExistsInTableCache[$table][$column])) {
1169 return $this->columnExistsInTableCache[$table][$column];
1170 }
1171 $columns = $this->fetchArray('DESC '.$table);
1172 if (!is_array($columns) || empty($columns)) {
1173 return false;
1174 }
1175 foreach ($columns as $column_description) {
1176 if ($column_description['Field'] == $column) {
1177 $this->columnExistsInTableCache[$table][$column] = true;
1178 return true;
1179 }
1180 }
1181 $this->columnExistsInTableCache[$table][$column] = false;
1182 return false;
1183 }
1184
1185 public function isSingleColumnNotPrimaryKey($ckey, $table) {
1186 $keys = $this->fetchArray('SHOW KEYS FROM '.$table.' WHERE Key_name = Column_name');
1187 foreach ($keys as $k) {
1188 if ($k['Column_name'] == $ckey) {
1189 return true;
1190 }
1191 }
1192 return false;
1193 }
1194
1195 public function columnType($column, $table) {
1196 $columns = $this->fetchArray('DESC '.$table);
1197 foreach($columns as $column_description) {
1198 if($column_description['Field'] == $column) return $column_description['Type'];
1199 }
1200 return false;
1201 }
1202
1203 public function recordExists($table, $conditions, $getQuery = false) {
1204 if (!is_array($conditions) || empty($conditions)) {
1205 trigger_error(sprintf("%s: Second parameter has to be an array may not be empty!", __FUNCTION__), E_USER_WARNING);
1206 }
1207 $fields = array();
1208 $values = array();
1209 foreach ($conditions as $f => $v) {
1210 $values[] = '`'.$f."` = '".$this->escape($v)."'";
1211 }
1212 if ($getQuery) {
1213 $q = 'SELECT * FROM `'.$table.'` WHERE '.implode(' AND ', $values);
1214 return $q;
1215 }else{
1216 $q = 'SELECT 1 FROM `'.$table.'` WHERE '.implode(' AND ', $values).' LIMIT 1';
1217 }
1218 $result = $this->fetchOne($q);
1219 if ($result !== false) {
1220 return true;
1221 }
1222 return false;
1223 }
1224
1225 /**
1226 * Insert an array of values
1227 */
1228 public function insert($tableName, $data, $replace = false) {
1229 if (!is_array($data)) {
1230 $this->error = __METHOD__.' expects an array as 2nd argument.';
1231 return false;
1232 }
1233
1234 $cols = '(';
1235 $values = '(';
1236 foreach ($data as $key => $value) {
1237 $cols .= "`" . $key . "`, ";
1238
1239 if ($value === null) {
1240 $values .= 'NULL, ';
1241 } else if (is_int($value) || is_float($value) || is_double($value)) {
1242 $values .= $value . ", ";
1243 } else if (strtoupper($value) == 'NOW()') {
1244 $values .= "NOW(), ";
1245 } else {
1246 $values .= "'" . $this->escape($value) . "', ";
1247 }
1248 }
1249 $cols = rtrim($cols, ", ") . ")";
1250 $values = rtrim($values, ", ") . ")";
1251 #if (function_exists('print_m')) echo print_m(($replace ? 'REPLACE' : 'INSERT').' INTO `'.$tableName.'` '.$cols.' VALUES '.$values);
1252 return $this->query(($replace ? 'REPLACE' : 'INSERT').' INTO `'.$tableName.'` '.$cols.' VALUES '.$values);
1253 }
1254
1255 /**
1256 * Insert an array of values
1257 */
1258 public function batchinsert($tableName, $data, $replace = false) {
1259 if (!is_array($data)) {
1260 $this->error = __METHOD__.' expects an array as 2nd argument.';
1261 return false;
1262 }
1263 $state = true;
1264
1265 $cols = '(';
1266 foreach ($data[0] as $key => $val) {
1267 $cols .= "`" . $key . "`, ";
1268 }
1269 $cols = rtrim($cols, ", ") . ")";
1270
1271 $block = array_chunk($data, 20);
1272
1273 foreach ($block as $data) {
1274 $values = '';
1275 foreach ($data as $subset) {
1276 $values .= ' (';
1277 foreach ($subset as $value) {
1278 if ($value === null) {
1279 $values .= 'NULL, ';
1280 } else if (is_int($value) || is_float($value) || is_double($value)) {
1281 $values .= $value . ", ";
1282 } else if (strtoupper($value) == 'NOW()') {
1283 $values .= "NOW(), ";
1284 } else {
1285 $values .= "'" . $this->escape($value) . "', ";
1286 }
1287 }
1288 $values = rtrim($values, ", ") . "),\n";
1289 }
1290 $values = rtrim($values, ",\n");
1291
1292 //echo ($replace ? 'REPLACE' : 'INSERT').' INTO `'.$tableName.'` '.$cols.' VALUES '.$values;
1293 $state = $state && $this->query(($replace ? 'REPLACE' : 'INSERT').' INTO `'.$tableName.'` '.$cols.' VALUES '.$values);
1294 }
1295 return $state;
1296 }
1297
1298 /**
1299 * Get last auto-increment value
1300 */
1301 public function getLastInsertID() {
1302 return $this->driver->getInsertId();
1303 }
1304
1305 /**
1306 * Update row(s)
1307 */
1308 public function update($tableName, $data, $wherea = array(), $add = '', $verbose = false) {
1309 if (!is_array($data) || !is_array($wherea)) {
1310 $this->error = __METHOD__.' expects two arrays as 2nd and 3rd arguments.';
1311 return false;
1312 }
1313
1314 $values = "";
1315 $where = "";
1316
1317 foreach ($data as $key => $value) {
1318 $values .= "`" . $key . "` = ";
1319
1320 if ($value === null) {
1321 $values .= 'NULL, ';
1322 } else if (is_int($value) || is_float($value) || is_double($value)) {
1323 $values .= $value . ", ";
1324 } else if (strtoupper($value) == 'NOW()') {
1325 $values .= "NOW(), ";
1326 } else {
1327 $values .= "'" . $this->escape($value) . "', ";
1328 }
1329 }
1330 $values = rtrim($values, ", ");
1331
1332 if (!empty($wherea)) {
1333 foreach ($wherea as $key => $value) {
1334 $where .= "`" . $key . "` ";
1335
1336 if ($value === null) {
1337 $where .= 'IS NULL AND ';
1338 } else if (is_int($value) || is_float($value) || is_double($value)) {
1339 $where .= '= '.$value . " AND ";
1340 } else if (strtoupper($value) == 'NOW()') {
1341 $where .= "= NOW() AND ";
1342 } else {
1343 $where .= "= '" . $this->escape($value) . "' AND ";
1344 }
1345 }
1346 $where = rtrim($where, "AND ");
1347 } else {
1348 $where = '1=1';
1349 }
1350 return $this->query('UPDATE `'.$tableName.'` SET '.$values.' WHERE '.$where.' '.$add, $verbose);
1351 }
1352
1353 /**
1354 * Delete row(s)
1355 */
1356 public function delete($table, $wherea, $add = null) {
1357 if (!is_array($wherea)) {
1358 $this->error = __METHOD__.' expects an array as 2nd argument.';
1359 return false;
1360 }
1361
1362 $where = "";
1363
1364 foreach ($wherea as $key => $value) {
1365 $where .= "`" . $key . "` ";
1366
1367 if ($value === null) {
1368 $where .= 'IS NULL AND ';
1369 } else if (is_int($value) || is_float($value) || is_double($value)) {
1370 $where .= '= '.$value . " AND ";
1371 } else {
1372 $where .= "= '" . $this->escape($value) . "' AND ";
1373 }
1374 }
1375
1376 $where = rtrim($where, "AND ");
1377
1378 $query = "DELETE FROM `".$table."` WHERE ".$where." ".$add;
1379 return $this->query($query);
1380 }
1381
1382 public function freeResult($result = null) {
1383 if ($result === null) {
1384 $result = $this->result;
1385 }
1386 $this->driver->freeResult($result);
1387 return true;
1388 }
1389
1390 /**
1391 * Unescapes strings / arrays of strings
1392 */
1393 public function unescape($object) {
1394 return is_array($object)
1395 ? array_map(array($this, 'unescape'), $object)
1396 : stripslashes($object);
1397 }
1398
1399 public function getTableCols($table) {
1400 $cols = array();
1401 if (!$this->tableExists($table)) {
1402 return $cols;
1403 }
1404 $colsQuery = $this->query('SHOW COLUMNS FROM `'.$table.'`');
1405 while ($row = $this->fetchNext($colsQuery)) {
1406 $cols[] = $row['Field'];
1407 }
1408 $this->freeResult($colsQuery);
1409 return $cols;
1410 }
1411
1412 /**
1413 * Get last executed query
1414 */
1415 public function getLastQuery() {
1416 return $this->query;
1417 }
1418
1419 /**
1420 * Get last error
1421 */
1422 public function getLastError() {
1423 return $this->error;
1424 }
1425
1426 /**
1427 * Gets all SQL errors.
1428 */
1429 public function getSqlErrors() {
1430 return $this->sqlErrors;
1431 }
1432
1433 /**
1434 * Get time consumed for all queries / operations (milliseconds)
1435 */
1436 public function getQueryTime() {
1437 return round((microtime(true) - $this->start) * 1000, 2);
1438 }
1439
1440 public function getTimePerQuery() {
1441 return $this->timePerQuery;
1442 }
1443
1444 /**
1445 * Get number of queries executed
1446 */
1447 public function getQueryCount() {
1448 return $this->count;
1449 }
1450
1451 public function getRealQueryTime() {
1452 return $this->querytime;
1453 }
1454
1455 public function setShowDebugOutput($b) {
1456 $this->showDebugOutput = $b;
1457 }
1458
1459}