1 <?php
2 /**
3 * DB.php
4 *
5 * @author Elvyrra SAS
6 * @license http://rem.mit-license.org/ MIT
7 */
8
9 namespace Hawk;
10
11 /**
12 * This class is a manager for MySQL connection and queries
13 *
14 * @package Core
15 */
16 class DB{
17 use Utils;
18
19 /**
20 * List of servers to connect
21 */
22 private static $servers = array();
23
24 /**
25 * List of connections instances
26 */
27 public static $instances = array();
28
29 /**
30 * PDO Object representing the connection to the server
31 */
32 private $connection;
33
34 /**
35 * The hosname of the connection
36 */
37 private $host,
38
39 /**
40 * The user connected to the database
41 */
42 $username,
43
44 /**
45 * The password of the user
46 */
47 $password,
48
49 /**
50 * The selected database
51 */
52 $dbname,
53
54
55 /**
56 * The charset
57 */
58 $charset = 'utf8',
59
60 /**
61 * The logged queries
62 */
63 $log = array();
64
65
66 // Status constants
67 const RETURN_STATUS = 0; // Returns the status of the query execution
68 const RETURN_ARRAY = 1; // Return each line of the query result in an array
69 const RETURN_OBJECT = 3; // Return each line of the query result in an object
70 const RETURN_LAST_INSERT_ID = 5; // Return The last inserted id
71 const RETURN_AFFECTED_ROWS = 6; // Return the rows affected by the query
72 const RETURN_QUERY = 7; // Return the query it self
73 const RETURN_CURSOR = 8; // Return a cursor
74
75 // Sort constants
76 const SORT_ASC = 'ASC'; // Sort ascending
77 const SORT_DESC = 'DESC'; // Sort descending
78
79
80
81 /**
82 * Constructor
83 *
84 * @param array $data - the connection properties, with keys host, dbname, username, password
85 */
86 public function __construct($data) {
87 $this->map($data);
88
89 if(strpos($this->host, ':') !== false) {
90 list($this->host, $this->port) = explode(':', $this->host, 2);
91 }
92 try {
93 $dns = "mysql:host=$this->host";
94 if(!empty($this->dbname)) {
95 $dns .= ";dbname=$this->dbname";
96 }
97 if(!empty($this->port)) {
98 $dns .= ";port=$this->port";
99 }
100
101 $this->connection = new \PDO($dns, $this->username, $this->password);
102 $this->connection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
103 $this->connection->setAttribute(\PDO::ATTR_EMULATE_PREPARES, 1);
104
105 $this->connection->query('SET NAMES "' . $this->charset . '"');
106 }
107 catch(\PDOException $e) {
108 throw new DBException($e->getMessage(), DBException::CONNECTION_ERROR, $this->host);
109 }
110 }
111
112
113 /**
114 * Add a configuration for a database connection
115 *
116 * @param string $name The instance name
117 * @param array $params The connection parameters.
118 * Each element of this array is an array itself, containing the connections data :
119 * - host : The database hostname / ip and port,
120 * - username : The user name
121 * - password : The user password
122 * - dbname : The database name,
123 * When openning the connection, if the first connection fails,
124 * a connection will be tried on the second element, and then.
125 * This is usefull for master / slave connections
126 */
127 public static function add($name, $params){
128 self::$servers[$name] = $params;
129 }
130
131 /**
132 * Get the open connection, or open it if not already open.
133 * This method manages master / slaves connections
134 *
135 * @param String $name The name of the instance
136 *
137 * @return DB the connected instance
138 */
139 public static function get($name){
140 if(isset(self::$instances[$name])) {
141 return self::$instances[$name];
142 }
143
144 $servers = self::$servers[$name];
145 foreach($servers as $i => $server){
146 try{
147 self::$instances[$name] = new self($server);
148 App::logger()->debug('Connection to db ' . $name . ' successfull');
149 // The connection succeed
150 break;
151 }
152 catch(DBException $e){
153 $log = 'Impossible to connect to db ' . $name . ' on instance ' . $i . ' : ' . $e->getMessage();
154 App::logger()->warning($log);
155 // The connection failed, try to connect to the next slave
156 if(!isset($servers[$i+1])) {
157 // the last slave connection failed
158 App::logger()->error('Impossible to connect to db ' . $name . ' : ' . $e->getMessage());
159 throw $e;
160 }
161 }
162 }
163
164 return self::$instances[$name];
165 }
166
167 /**
168 * Execute a SQL query on the SQL server, and get the result of execution
169 *
170 * @param string $sql The query to execute,
171 * @param array $binds The binded value to send to the server
172 * @param array $options The result options. This array can contains the following data :
173 * - 'return' (mixed) : The type of return (default DB::RETURN_STATUS). It can be set to :
174 * . DB::RETURN_STATUS : Returns the execution status
175 * . DB::RETURN_ARRAY : Each result row is an associative array, with columns as keys
176 * . DB::RETURN_OBJECT : Eeach result row is a StdClass instance, with columns as properties
177 * . DB::RETURN_LAST_INSERT_ID : Return the last inserted id
178 * . DB::RETURN_AFFECTED_ROWS : Returns the number of affected rows
179 * . DB::RETURN_QUERY : Returns the query itself
180 * . DB::RETURN_CURSOR : Returns a cursor to fetch the results
181 * . A classname : Each result row is an instance of the given classname, with columns as properties
182 * - 'onerow' (bool) : If true, the function will return the first row of the result set (default false)
183 * - 'index' (string) : Defines the column values to get as array result index (default null)
184 * - 'args' (array) : This can be used when you define a classname as 'return', to pass arguments to the class constructor
185 *
186 * @return mixed The execution result, depending on what type of return has been defined in $options parameter
187 */
188 public function query($sql, $binds = array(), $options = array()){
189 $default = array(
190 'return' => self::RETURN_STATUS,
191 'onerow' => false,
192 'index' => null,
193 'args' => array()
194 );
195
196 foreach($default as $name => $value){
197 if(!isset($options[$name])) {
198 $options[$name] = $value;
199 }
200 }
201
202 try {
203 // Prepare the query
204 $stmt = $this->connection->prepare($sql);
205
206 // Prepare query logging
207 $log = $sql;
208
209 // Bind values to the query
210 foreach($binds as $key => $bind){
211 $stmt->bindValue(":$key", $bind);
212 $log = str_replace(":$key", $this->connection->quote($bind), $log);
213 }
214
215 // Execute the query
216 $start = microtime(true);
217 $stmt->execute();
218 $end = microtime(true);
219
220 // Get the result
221 $result;
222 if(is_int($options['return'])) {
223 switch($options['return']){
224 // Return the query status
225 case self::RETURN_STATUS:
226 $result = true;
227 break;
228
229 case self::RETURN_ARRAY:
230 if($options['onerow']) {
231 // Return the first row as associative array
232 $result = $stmt->fetch(\PDO::FETCH_ASSOC);
233 }
234 else{
235 // Return an array of associative arrays
236 $data = array();
237 while($row = $stmt->fetch(\PDO::FETCH_ASSOC)){
238 if($options['index']) {
239 $data[$row[$options['index']]] = $row;
240 }
241 else{
242 $data[]= $row;
243 }
244 }
245 $result = $data;
246 }
247 break;
248
249 case self::RETURN_OBJECT :
250 if($options['onerow']) {
251 // Return the first row as an StdClass instance
252 $result = $stmt->fetch(\PDO::FETCH_OBJ);
253 }
254 else{
255 // Return an array of StdClass instances
256 $data = array();
257 while($row = $stmt->fetch(\PDO::FETCH_OBJ)){
258 if($options['index']) {
259 $index = $options['index'];
260 $data[$row->$index]= $row;
261 }
262 else{
263 $data[] = $row;
264 }
265 }
266 $result = $data;
267 }
268 break;
269
270 case self::RETURN_LAST_INSERT_ID :
271 // Return the last inserted array
272 $result = $this->connection->lastInsertId();
273 break;
274
275 case self::RETURN_AFFECTED_ROWS :
276 // Return the number of affected rows
277 $result = $stmt->rowCount();
278 break;
279
280 case self::RETURN_CURSOR :
281 // Return a cursor to fetch the results
282 $result = $stmt;
283 break;
284 }
285 }
286 else{
287 if($options['onerow']) {
288 // Return a model instance
289 $object = $stmt->fetchObject($options['return'], $options['args']);
290 $result = $object !== false ? $object : null;
291 }
292 else{
293 // Return an array of model instances
294 $data = array();
295 while($row = $stmt->fetchObject($options['return'], $options['args'])){
296 if($options['index']) {
297 $index = $options['index'];
298 $data[$row->$index] = $row;
299 }
300 else{
301 $data[] = $row;
302 }
303 }
304 $result = $data;
305 }
306 }
307
308 // Log the query
309 $this->addLog(str_replace(PHP_EOL, ' ', $log), $result, $start, $end);
310
311 return $result;
312 }
313 catch(\PDOException $e) {
314 $this->addLog($log, 'query failed', $start, microtime(true));
315 throw new DBException($e->getMessage(), DBException::QUERY_ERROR, $sql);
316 }
317 }
318
319
320 /**
321 * Select a database
322 *
323 * @param string $dbname The database to select
324 *
325 * @return boolean true if the database has been sucessfully selected, false in other cases
326 */
327 public function selectDb($dbname){
328 return $this->query('use ' . $dbname);
329 }
330
331 /**
332 * Build and execute a SELECT query on the selected database, and return the result
333 *
334 * @param array $query The parameters of the query. This parameter can have the following data :
335 * - 'from' string (required) : The table name where to search
336 * - 'fields' array (optionnal) : The fields to get. Each element of this array can be :
337 * - A single element of the column name
338 * - A key/value combination that will be parse to 'key as value' in the SQL query
339 * - 'where' string | DBExample (optionnal) : The search condition. Can be a SQL expression or a DBExample instance
340 * - 'group' array (optionnal) : The columns to group the result, each column in an array element
341 * - 'having' string (optinnal) : The 'HAVING' expression, formatted as a SQL expression
342 * - 'orderby' array (optionnal) : The result sort, where each key is a column name, and the values define the order (ASC, DESC)
343 * - 'start' int (optionnal) : The first row number to get the results
344 * - 'limit' int (optionnal) : The maximum number of rows to return
345 * - 'one' bool (optionnal) : If set to true, then the first row will be returned
346 * - 'binds' array (optionnal) : The binded values
347 * - 'index' string (optionnal) : If set, the result arrar will be indexed by the value of the column set by this property
348 * - 'return' mixed (default : DB::RETURN_ARRAY) : The return type (all possible values are defined on the method 'query')
349 *
350 * @return mixed The query result
351 */
352 public function select($query) {
353 $query = (object) $query;
354
355 if(!isset($query->from)) {
356 throw new DBException("'from' parameter is mandatory in select method", DBException::QUERY_ERROR, '');
357 }
358
359 /*** Treat the paramters ***/
360 if(empty($query->fields)) {
361 $query->fields = "*";
362 }
363 elseif(is_array($query->fields)) {
364 $tmp = array();
365 foreach($query->fields as $name => $alias){
366 if(is_numeric($name)) {
367 $tmp[] = $alias;
368 }
369 else{
370 $tmp[] = "$name as $alias";
371 }
372 }
373 $query->fields = implode(",", $tmp);
374 }
375
376 if(isset($query->where) && $query->where instanceof DBExample) {
377 $query->where = $query->where->parse($query->binds);
378 }
379 $where = !empty($query->where) ? "WHERE $query->where" : '';
380
381 $group = !empty($query->group) ? "GROUP BY ".implode(",", array_map(array($this, 'formatField'), $query->group)) : "";
382
383 $having = !empty($query->having) ? "HAVING $query->having" : '';
384
385 if(!empty($query->orderby)) {
386 $orders = array();
387 foreach($query->orderby as $field => $value){
388 $orders[] = self::formatField($field) . " " . $value;
389 }
390 $orderby = "ORDER BY " . implode(",", $orders);
391 }
392 else{
393 $orderby = "";
394 }
395
396 $limit = "";
397 if(!empty($query->one)) {
398 $limit = "LIMIT 1";
399 }
400 elseif(!empty($query->limit)) {
401 $limit = " LIMIT ".(!empty($query->start) ? "$query->start, " : ""). "$query->limit";
402 }
403
404 $sql = "SELECT $query->fields FROM $query->from $where $group $having $orderby $limit";
405
406 if(empty($query->binds)) {
407 $query->binds = array();
408 }
409
410 if(empty($query->return)) {
411 $query->return = self::RETURN_ARRAY;
412 }
413
414 return $this->query(
415 $sql, $query->binds, array(
416 'return' => $query->return,
417 'onerow' => !empty($query->one),
418 'index' => empty($query->index) ? '' : $query->index
419 )
420 );
421 }
422
423
424 /**
425 * Insert a row in a table
426 *
427 * @param string $table The table where to insert data
428 * @param array $insert The data to insert, where keys are the columns names, and values the values to insert
429 * @param string $flag A flag on the INSERT query (IGNORE or DELAYED)
430 * @param string $onduplicatekey The ON DUPLICATE KEY expression
431 *
432 * @return mixed The value of the last inserted id
433 */
434 public function insert($table, $insert = array(), $flag = '', $onduplicatekey = ''){
435 $keys = array();
436 $values = array();
437 $binds = array();
438
439 foreach($insert as $key => $value){
440 $uniq = uniqid();
441 $values[] = ':'.$uniq;
442 $keys[] = self::formatField($key);
443 $binds[$uniq] = $value;
444 }
445
446 $keys = implode(',', $keys);
447 $values = implode(',', $values);
448
449 $sql="INSERT $flag INTO ".$table." (".$keys.") VALUES (".$values.") " . ($onduplicatekey ? "ON DUPLICATE KEY UPDATE $onduplicatekey" : "");
450
451 return $this->query($sql, $binds, array('return' => self::RETURN_LAST_INSERT_ID));
452 }
453
454 /**
455 * Replace data in a table
456 *
457 * @param string $table the table where to replace data
458 * @param array $insert The data to insert, where keys are the columns names, and values the values to insert
459 *
460 * @return mixed The value of the last inserted id
461 */
462 public function replace($table, $insert = array()){
463 $keys = array();
464 $values = array();
465 $binds = array();
466
467 foreach($insert as $key => $value){
468 $uniq = uniqid();
469 $values[] = ':' . $uniq;
470 $keys[] = self::formatField($key);
471 $binds[$uniq] = $value;
472 }
473
474 $keys = implode(',', $keys);
475 $values = implode(' , ', $values);
476
477 $sql="REPLACE INTO ".$table." (".$keys.") VALUES (".$values.")";
478 return $this->query($sql, $binds, array('return' => self::RETURN_LAST_INSERT_ID));
479 }
480
481 /**
482 * Update records in a table
483 *
484 * @param string $table The table to update
485 * @param string|DBExample $where The condition to find rows to update
486 * @param array $update The columns to update, where keys are the columns names and values are the values to update
487 * @param array $binds The binded values, in case of $where is a SQL expression
488 *
489 * @return int The number of updated rows
490 */
491 public function update($table, $where = null, $update = array(), $binds = array()) {
492 if(!empty($where)) {
493 if($where instanceof DBExample) {
494 $where = $where->parse($binds);
495 }
496 $where = ' WHERE ' . $where;
497 }
498
499 $updates = array();
500 foreach($update as $key => $value) {
501 $bind = uniqid();
502 $updates[] = self::formatField($key) . " = :$bind";
503 $binds[$bind] = $value;
504 }
505
506 $sql = "UPDATE $table SET ". implode(',', $updates) . $where;
507
508 return $this->query($sql, $binds, array('return' => self::RETURN_AFFECTED_ROWS));
509 }
510
511 /**
512 * Delete records in a table
513 *
514 * @param string $table The table to update
515 * @param string|DBExample $where The condition to find rows to delete
516 * @param array $binds The binded values, in case of $where is a SQL expression
517 *
518 * @return int The number of deleted rows
519 */
520 public function delete($table, $where = null, $binds = array()) {
521 if(!empty($where)) {
522 if($where instanceof DBExample) {
523 $where = $where->parse($binds);
524 }
525 $where = 'WHERE ' . $where;
526 }
527
528 $sql = "DELETE FROM $table $where";
529
530 return $this->query($sql, $binds, array('return' => self::RETURN_AFFECTED_ROWS));
531 }
532
533 /**
534 * Count elements in a table
535 *
536 * @param string $table The table to count elements
537 * @param string|DBExample $where The condition to find rows to count
538 * @param array $binds The binded values, in case of $where is a SQL expression
539 * @param string $field To count a specific field
540 * @param array $group Groups rows before counting them
541 *
542 * @return int The number of found elements
543 */
544 public function count($table , $where = null, $binds = array(), $field = null, $group = array()) {
545 if($where instanceof DBExample) {
546 $where = $where->parse($binds);
547 }
548 if (!empty($where)) {
549 $where = 'WHERE ' . $where;
550 }
551
552 if(!empty($group)) {
553 $group = 'GROUP BY ' . implode(',', array_map(array(self, 'formatField'), $group));
554 }
555
556 if(empty($field)) {
557 $field = '*';
558 }
559 else{
560 $field = $field;
561 }
562
563 $sql = 'SELECT COUNT(' . $field . ') as counter FROM ' . $table .' ' . $where;
564
565 return $this->query($sql, $binds, array('return' => self::RETURN_OBJECT, 'onerow' => true))->counter;
566 }
567
568
569 /**
570 * Format a string to a SQL field format : [`table`.]`field`
571 *
572 * @param string $str The string to format
573 *
574 * @return string The formatted string
575 */
576 public static function formatField($str){
577 return preg_replace_callback(
578 '/^(\w+)(\.(\w+))?$/', function ($m) {
579 return '`' . $m[1] . '`' . (isset($m[2]) && isset($m[3]) ? '.`' . $m[3] . '`' : '');
580 }, $str
581 );
582 }
583
584
585 /**
586 * Log a query in the internal log system of this class. This method can be used to get all the executed queries to optimize your scripts
587 *
588 * @param string $query The query to log
589 * @param mixed $result The result of the query
590 * @param int $start The start time of the query execution in the script process
591 * @param int $end The end time of the query execution in the script process
592 */
593 private function addLog($query, $result, $start, $end){
594 $this->logs[] = array(
595 'query' => $query,
596 'start' => $start - SCRIPT_START_TIME,
597 'end' => $end - SCRIPT_START_TIME,
598 'time' => $end - $start
599 );
600 }
601
602
603 /**
604 * Get the DB logs
605 *
606 * @return array The logged queries
607 */
608 public function getLogs(){
609 return $this->logs;
610 }
611
612
613 /**
614 * Quote a string
615 *
616 * @param string $str The data to quote
617 * @param int $type The data type
618 *
619 * @return string The quoted string
620 */
621 public function quote($str, $type = \PDO::PARAM_STR){
622 return $this->connection->quote($str, $type);
623 }
624
625
626 /**
627 * Get the real name of a table, with the configured prefix
628 *
629 * @param string $table The base table name
630 * @param string $prefix If set, this prefix will replace the one configured for the application
631 *
632 * @return string The complete name of the table
633 */
634 public static function getFullTablename($table, $prefix = null){
635 if($prefix === null) {
636 $prefix = App::conf()->get('db.prefix');
637 }
638
639 return $prefix . $table;
640 }
641
642 }
643
644
645 /**
646 * This class manages the exceptions throwed by DB class
647 *
648 * @package Exceptions
649 */
650 class DBException extends \Exception{
651 const CONNECTION_ERROR = 1;
652 const QUERY_ERROR = 2;
653
654 /**
655 * Constructor
656 *
657 * @param string $message The exception message
658 * @param int $code The exception $code
659 * @param string $value The exception content
660 * @param Exception $previous The previous exception that throwed that one
661 */
662 public function __construct($message, $code, $value, $previous = null){
663 switch($code){
664 case self::CONNECTION_ERROR :
665 $message = "Impossible to connect to Database Server : $value, $message";
666 break;
667
668 case self::QUERY_ERROR:
669 $message = "An error was detected : $message in the Database Query : $value";
670 App::logger()->error($message);
671 break;
672
673 }
674
675 parent::__construct($message, $code, $previous);
676 }
677 }