]> git.mxchange.org Git - friendica.git/blob - include/dba.php
was a bit confusing for me or I was not sleeping to much ...
[friendica.git] / include / dba.php
1 <?php
2 require_once("dbm.php");
3 require_once('include/datetime.php');
4
5 /**
6  * @class MySQL database class
7  *
8  * For debugging, insert 'dbg(1);' anywhere in the program flow.
9  * dbg(0); will turn it off. Logging is performed at LOGGER_DATA level.
10  * When logging, all binary info is converted to text and html entities are escaped so that
11  * the debugging stream is safe to view within both terminals and web pages.
12  *
13  */
14
15 class dba {
16
17         private $debug = 0;
18         private $db;
19         private $result;
20         private $driver;
21         public  $connected = false;
22         public  $error = false;
23
24         function __construct($server, $user, $pass, $db, $install = false) {
25                 $a = get_app();
26
27                 $stamp1 = microtime(true);
28
29                 $server = trim($server);
30                 $user = trim($user);
31                 $pass = trim($pass);
32                 $db = trim($db);
33
34                 if (!(strlen($server) && strlen($user))) {
35                         $this->connected = false;
36                         $this->db = null;
37                         return;
38                 }
39
40                 if ($install) {
41                         if (strlen($server) && ($server !== 'localhost') && ($server !== '127.0.0.1')) {
42                                 if (! dns_get_record($server, DNS_A + DNS_CNAME + DNS_PTR)) {
43                                         $this->error = sprintf(t('Cannot locate DNS info for database server \'%s\''), $server);
44                                         $this->connected = false;
45                                         $this->db = null;
46                                         return;
47                                 }
48                         }
49                 }
50
51                 if (class_exists('\PDO') && in_array('mysql', PDO::getAvailableDrivers())) {
52                         $this->driver = 'pdo';
53                         $connect = "mysql:host=".$server.";dbname=".$db;
54                         if (isset($a->config["system"]["db_charset"])) {
55                                 $connect .= ";charset=".$a->config["system"]["db_charset"];
56                         }
57                         $this->db = @new PDO($connect, $user, $pass);
58                         if (!$this->db->errorCode()) {
59                                 $this->connected = true;
60                         }
61                 } elseif (class_exists('mysqli')) {
62                         $this->driver = 'mysqli';
63                         $this->db = @new mysqli($server,$user,$pass,$db);
64                         if (!mysqli_connect_errno()) {
65                                 $this->connected = true;
66
67                                 if (isset($a->config["system"]["db_charset"])) {
68                                         $this->db->set_charset($a->config["system"]["db_charset"]);
69                                 }
70                         }
71                 } elseif (function_exists('mysql_connect')) {
72                         $this->driver = 'mysql';
73                         $this->db = mysql_connect($server,$user,$pass);
74                         if ($this->db && mysql_select_db($db,$this->db)) {
75                                 $this->connected = true;
76
77                                 if (isset($a->config["system"]["db_charset"])) {
78                                         mysql_set_charset($a->config["system"]["db_charset"], $this->db);
79                                 }
80                         }
81                 } else {
82                         // No suitable SQL driver was found.
83                         if (!$install) {
84                                 system_unavailable();
85                         }
86                 }
87
88                 if (!$this->connected) {
89                         $this->db = null;
90                         if (!$install) {
91                                 system_unavailable();
92                         }
93                 }
94                 $a->save_timestamp($stamp1, "network");
95         }
96
97         /**
98          * @brief Returns the MySQL server version string
99          * 
100          * This function discriminate between the deprecated mysql API and the current
101          * object-oriented mysqli API. Example of returned string: 5.5.46-0+deb8u1
102          *
103          * @return string
104          */
105         public function server_info() {
106                 switch ($this->driver) {
107                         case 'pdo':
108                                 $version = $this->db->getAttribute(PDO::ATTR_SERVER_VERSION);
109                                 break;
110                         case 'mysqli':
111                                 $version = $this->db->server_info;
112                                 break;
113                         case 'mysql':
114                                 $version = mysql_get_server_info($this->db);
115                                 break;
116                 }
117                 return $version;
118         }
119
120         /**
121          * @brief Returns the selected database name
122          *
123          * @return string
124          */
125         public function database_name() {
126                 $r = $this->q("SELECT DATABASE() AS `db`");
127
128                 return $r[0]['db'];
129         }
130
131         /**
132          * @brief Returns the number of rows
133          *
134          * @return integer
135          */
136         public function num_rows() {
137                 if (!$this->result) {
138                         return 0;
139                 }
140
141                 switch ($this->driver) {
142                         case 'pdo':
143                                 $rows = $this->result->rowCount();
144                                 break;
145                         case 'mysqli':
146                                 $rows = $this->result->num_rows;
147                                 break;
148                         case 'mysql':
149                                 $rows = mysql_num_rows($this->result);
150                                 break;
151                 }
152                 return $rows;
153         }
154
155         /**
156          * @brief Analyze a database query and log this if some conditions are met.
157          *
158          * @param string $query The database query that will be analyzed
159          */
160         public function log_index($query) {
161                 $a = get_app();
162
163                 if ($a->config["system"]["db_log_index"] == "") {
164                         return;
165                 }
166
167                 // Don't explain an explain statement
168                 if (strtolower(substr($query, 0, 7)) == "explain") {
169                         return;
170                 }
171
172                 // Only do the explain on "select", "update" and "delete"
173                 if (!in_array(strtolower(substr($query, 0, 6)), array("select", "update", "delete"))) {
174                         return;
175                 }
176
177                 $r = $this->q("EXPLAIN ".$query);
178                 if (!dbm::is_result($r)) {
179                         return;
180                 }
181
182                 $watchlist = explode(',', $a->config["system"]["db_log_index_watch"]);
183                 $blacklist = explode(',', $a->config["system"]["db_log_index_blacklist"]);
184
185                 foreach ($r AS $row) {
186                         if ((intval($a->config["system"]["db_loglimit_index"]) > 0)) {
187                                 $log = (in_array($row['key'], $watchlist) AND
188                                         ($row['rows'] >= intval($a->config["system"]["db_loglimit_index"])));
189                         } else {
190                                 $log = false;
191                         }
192
193                         if ((intval($a->config["system"]["db_loglimit_index_high"]) > 0) AND ($row['rows'] >= intval($a->config["system"]["db_loglimit_index_high"]))) {
194                                 $log = true;
195                         }
196
197                         if (in_array($row['key'], $blacklist) OR ($row['key'] == "")) {
198                                 $log = false;
199                         }
200
201                         if ($log) {
202                                 $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
203                                 @file_put_contents($a->config["system"]["db_log_index"], datetime_convert()."\t".
204                                                 $row['key']."\t".$row['rows']."\t".$row['Extra']."\t".
205                                                 basename($backtrace[1]["file"])."\t".
206                                                 $backtrace[1]["line"]."\t".$backtrace[2]["function"]."\t".
207                                                 substr($query, 0, 2000)."\n", FILE_APPEND);
208                         }
209                 }
210         }
211
212         public function q($sql, $onlyquery = false) {
213                 $a = get_app();
214
215                 if (!$this->db || !$this->connected) {
216                         return false;
217                 }
218
219                 $this->error = '';
220
221                 $connstr = ($this->connected() ? "Connected" : "Disonnected");
222
223                 $stamp1 = microtime(true);
224
225                 $orig_sql = $sql;
226
227                 if (x($a->config,'system') && x($a->config['system'], 'db_callstack')) {
228                         $sql = "/*".$a->callstack()." */ ".$sql;
229                 }
230
231                 $columns = 0;
232
233                 switch ($this->driver) {
234                         case 'pdo':
235                                 $result = @$this->db->query($sql);
236                                 // Is used to separate between queries that returning data - or not
237                                 if (!is_bool($result)) {
238                                         $columns = $result->columnCount();
239                                 }
240                                 break;
241                         case 'mysqli':
242                                 $result = @$this->db->query($sql);
243                                 break;
244                         case 'mysql':
245                                 $result = @mysql_query($sql,$this->db);
246                                 break;
247                 }
248                 $stamp2 = microtime(true);
249                 $duration = (float)($stamp2-$stamp1);
250
251                 $a->save_timestamp($stamp1, "database");
252
253                 if (strtolower(substr($orig_sql, 0, 6)) != "select") {
254                         $a->save_timestamp($stamp1, "database_write");
255                 }
256                 if (x($a->config,'system') && x($a->config['system'],'db_log')) {
257                         if (($duration > $a->config["system"]["db_loglimit"])) {
258                                 $duration = round($duration, 3);
259                                 $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
260                                 @file_put_contents($a->config["system"]["db_log"], datetime_convert()."\t".$duration."\t".
261                                                 basename($backtrace[1]["file"])."\t".
262                                                 $backtrace[1]["line"]."\t".$backtrace[2]["function"]."\t".
263                                                 substr($sql, 0, 2000)."\n", FILE_APPEND);
264                         }
265                 }
266
267                 switch ($this->driver) {
268                         case 'pdo':
269                                 $errorInfo = $this->db->errorInfo();
270                                 if ($errorInfo) {
271                                         $this->error = $errorInfo[2];
272                                         $this->errorno = $errorInfo[1];
273                                 }
274                                 break;
275                         case 'mysqli':
276                                 if ($this->db->errno) {
277                                         $this->error = $this->db->error;
278                                         $this->errorno = $this->db->errno;
279                                 }
280                                 break;
281                         case 'mysql':
282                                 if (mysql_errno($this->db)) {
283                                         $this->error = mysql_error($this->db);
284                                         $this->errorno = mysql_errno($this->db);
285                                 }
286                                 break;
287                 }
288                 if (strlen($this->error)) {
289                         logger('DB Error ('.$connstr.') '.$this->errorno.': '.$this->error);
290                 }
291
292                 if ($this->debug) {
293
294                         $mesg = '';
295
296                         if ($result === false) {
297                                 $mesg = 'false';
298                         } elseif ($result === true) {
299                                 $mesg = 'true';
300                         } else {
301                                 switch ($this->driver) {
302                                         case 'pdo':
303                                                 $mesg = $result->rowCount().' results'.EOL;
304                                                 break;
305                                         case 'mysqli':
306                                                 $mesg = $result->num_rows.' results'.EOL;
307                                                 break;
308                                         case 'mysql':
309                                                 $mesg = mysql_num_rows($result).' results'.EOL;
310                                                 break;
311                                 }
312                         }
313
314                         $str =  'SQL = ' . printable($sql) . EOL . 'SQL returned ' . $mesg
315                                 . (($this->error) ? ' error: ' . $this->error : '')
316                                 . EOL;
317
318                         logger('dba: ' . $str );
319                 }
320
321                 /**
322                  * If dbfail.out exists, we will write any failed calls directly to it,
323                  * regardless of any logging that may or may nor be in effect.
324                  * These usually indicate SQL syntax errors that need to be resolved.
325                  */
326
327                 if ($result === false) {
328                         logger('dba: ' . printable($sql) . ' returned false.' . "\n" . $this->error);
329                         if (file_exists('dbfail.out')) {
330                                 file_put_contents('dbfail.out', datetime_convert() . "\n" . printable($sql) . ' returned false' . "\n" . $this->error . "\n", FILE_APPEND);
331                         }
332                 }
333
334                 if (is_bool($result)) {
335                         return $result;
336                 }
337                 if ($onlyquery) {
338                         $this->result = $result;
339                         return true;
340                 }
341
342                 $r = array();
343                 switch ($this->driver) {
344                         case 'pdo':
345                                 while ($x = $result->fetch(PDO::FETCH_ASSOC)) {
346                                         $r[] = $x;
347                                 }
348                                 $result->closeCursor();
349                                 break;
350                         case 'mysqli':
351                                 while ($x = $result->fetch_array(MYSQLI_ASSOC)) {
352                                         $r[] = $x;
353                                 }
354                                 $result->free_result();
355                                 break;
356                         case 'mysql':
357                                 while ($x = mysql_fetch_array($result, MYSQL_ASSOC)) {
358                                         $r[] = $x;
359                                 }
360                                 mysql_free_result($result);
361                                 break;
362                 }
363
364                 // PDO doesn't return "true" on successful operations - like mysqli does
365                 // Emulate this behaviour by checking if the query returned data and had columns
366                 // This should be reliable enough
367                 if (($this->driver == 'pdo') AND (count($r) == 0) AND ($columns == 0)) {
368                         return true;
369                 }
370
371                 //$a->save_timestamp($stamp1, "database");
372
373                 if ($this->debug) {
374                         logger('dba: ' . printable(print_r($r, true)));
375                 }
376                 return($r);
377         }
378
379         public function qfetch() {
380                 $x = false;
381
382                 if ($this->result) {
383                         switch ($this->driver) {
384                                 case 'pdo':
385                                         $x = $this->result->fetch(PDO::FETCH_ASSOC);
386                                         break;
387                                 case 'mysqli':
388                                         $x = $this->result->fetch_array(MYSQLI_ASSOC);
389                                         break;
390                                 case 'mysql':
391                                         $x = mysql_fetch_array($this->result, MYSQL_ASSOC);
392                                         break;
393                         }
394                 }
395                 return($x);
396         }
397
398         public function qclose() {
399                 if ($this->result) {
400                         switch ($this->driver) {
401                                 case 'pdo':
402                                         $this->result->closeCursor();
403                                         break;
404                                 case 'mysqli':
405                                         $this->result->free_result();
406                                         break;
407                                 case 'mysql':
408                                         mysql_free_result($this->result);
409                                         break;
410                         }
411                 }
412         }
413
414         public function dbg($dbg) {
415                 $this->debug = $dbg;
416         }
417
418         public function escape($str) {
419                 if ($this->db && $this->connected) {
420                         switch ($this->driver) {
421                                 case 'pdo':
422                                         return substr(@$this->db->quote($str, PDO::PARAM_STR), 1, -1);
423                                 case 'mysqli':
424                                         return @$this->db->real_escape_string($str);
425                                 case 'mysql':
426                                         return @mysql_real_escape_string($str,$this->db);
427                         }
428                 }
429         }
430
431         function connected() {
432                 switch ($this->driver) {
433                         case 'pdo':
434                                 // Not sure if this really is working like expected
435                                 $connected = ($this->db->getAttribute(PDO::ATTR_CONNECTION_STATUS) != "");
436                                 break;
437                         case 'mysqli':
438                                 $connected = $this->db->ping();
439                                 break;
440                         case 'mysql':
441                                 $connected = mysql_ping($this->db);
442                                 break;
443                 }
444                 return $connected;
445         }
446
447         function insert_id() {
448                 switch ($this->driver) {
449                         case 'pdo':
450                                 $id = $this->db->lastInsertId();
451                                 break;
452                         case 'mysqli':
453                                 $id = $this->db->insert_id;
454                                 break;
455                         case 'mysql':
456                                 $id = mysql_insert_id($this->db);
457                                 break;
458                 }
459                 return $id;
460         }
461
462         function __destruct() {
463                 if ($this->db) {
464                         switch ($this->driver) {
465                                 case 'pdo':
466                                         $this->db = null;
467                                         break;
468                                 case 'mysqli':
469                                         $this->db->close();
470                                         break;
471                                 case 'mysql':
472                                         mysql_close($this->db);
473                                         break;
474                         }
475                 }
476         }
477 }
478
479 function printable($s) {
480         $s = preg_replace("~([\x01-\x08\x0E-\x0F\x10-\x1F\x7F-\xFF])~",".", $s);
481         $s = str_replace("\x00",'.',$s);
482         if (x($_SERVER,'SERVER_NAME')) {
483                 $s = escape_tags($s);
484         }
485         return $s;
486 }
487
488 // Procedural functions
489 function dbg($state) {
490         global $db;
491
492         if ($db) {
493                 $db->dbg($state);
494         }
495 }
496
497 function dbesc($str) {
498         global $db;
499
500         if ($db && $db->connected) {
501                 return($db->escape($str));
502         } else {
503                 return(str_replace("'","\\'",$str));
504         }
505 }
506
507 // Function: q($sql,$args);
508 // Description: execute SQL query with printf style args.
509 // Example: $r = q("SELECT * FROM `%s` WHERE `uid` = %d",
510 //                   'user', 1);
511 function q($sql) {
512         global $db;
513         $args = func_get_args();
514         unset($args[0]);
515
516         if ($db && $db->connected) {
517                 $stmt = @vsprintf($sql,$args); // Disabled warnings
518                 //logger("dba: q: $stmt", LOGGER_ALL);
519                 if ($stmt === false)
520                         logger('dba: vsprintf error: ' . print_r(debug_backtrace(),true), LOGGER_DEBUG);
521
522                 $db->log_index($stmt);
523
524                 return $db->q($stmt);
525         }
526
527         /**
528          *
529          * This will happen occasionally trying to store the
530          * session data after abnormal program termination
531          *
532          */
533         logger('dba: no database: ' . print_r($args,true));
534         return false;
535 }
536
537 /**
538  * @brief Performs a query with "dirty reads"
539  *
540  * By doing dirty reads (reading uncommitted data) no locks are performed
541  * This function can be used to fetch data that doesn't need to be reliable.
542  *
543  * @param $args Query parameters (1 to N parameters of different types)
544  * @return array Query array
545  */
546 function qu($sql) {
547         global $db;
548
549         $args = func_get_args();
550         unset($args[0]);
551
552         if ($db && $db->connected) {
553                 $stmt = @vsprintf($sql,$args); // Disabled warnings
554                 if ($stmt === false)
555                         logger('dba: vsprintf error: ' . print_r(debug_backtrace(),true), LOGGER_DEBUG);
556
557                 $db->log_index($stmt);
558
559                 $db->q("SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
560                 $retval = $db->q($stmt);
561                 $db->q("SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;");
562                 return $retval;
563         }
564
565         /**
566          *
567          * This will happen occasionally trying to store the
568          * session data after abnormal program termination
569          *
570          */
571         logger('dba: no database: ' . print_r($args,true));
572         return false;
573 }
574
575 /**
576  *
577  * Raw db query, no arguments
578  *
579  */
580 function dbq($sql) {
581         global $db;
582
583         if ($db && $db->connected) {
584                 $ret = $db->q($sql);
585         } else {
586                 $ret = false;
587         }
588         return $ret;
589 }
590
591 // Caller is responsible for ensuring that any integer arguments to
592 // dbesc_array are actually integers and not malformed strings containing
593 // SQL injection vectors. All integer array elements should be specifically
594 // cast to int to avoid trouble.
595 function dbesc_array_cb(&$item, $key) {
596         if (is_string($item))
597                 $item = dbesc($item);
598 }
599
600 function dbesc_array(&$arr) {
601         if (is_array($arr) && count($arr)) {
602                 array_walk($arr,'dbesc_array_cb');
603         }
604 }
605
606 function dba_timer() {
607         return microtime(true);
608 }