1<?php
2namespace DALMP;
3
4/**
5 * Database - Abstraction Layer for MySQL
6 *
7 * @author Nicolas Embriz <nbari@dalmp.com>
8 * @package DALMP
9 * @license BSD License
10 * @version 3.0.3
11 */
12class Database
13{
14    /**
15     * Contains database object
16     *
17     * @access protected
18     * @var object
19     */
20    protected $DB;
21
22    /**
23     * Contains the database parameters DSN.
24     *
25     * @access protected
26     * @var array
27     */
28    protected $dsn = array();
29
30    /**
31     * query result
32     *
33     * @access protected
34     * @var mixed
35     */
36    protected $_rs = null;
37
38    /**
39     * prepared statement object or false if an error occurred.
40     *
41     * @access protected
42     * @var mixed
43     */
44    protected $_stmt = null;
45
46    /**
47     * cache DALMP\Cache instance
48     *
49     * @access private
50     * @var mixed
51     */
52    public $cache = null;
53
54    /**
55     * If enabled, logs all queries and executions.
56     *
57     * @access private
58     * @var boolean
59     */
60    private $debug = false;
61
62    /**
63     * Holds the fetchMode.
64     *
65     * @access private
66     * @var mixed
67     */
68    private $fetchMode = MYSQLI_BOTH;
69
70    /**
71     * Holds the num of rows returned.
72     *
73     * @access private
74     * @var int
75     */
76    private $numOfRows;
77
78    /**
79     * Holds the num of rows affected by INSERT, UPDATE, or DELETE query.
80     *
81     * @access private
82     * @var int
83     */
84    private $numOfRowsAffected;
85
86    /**
87     * Holds the num of fields returned.
88     *
89     * @access private
90     * @var int
91     */
92    private $numOfFields;
93
94    /**
95     * Contains the prepared statments parameters
96     *
97     * @access private
98     * @var array
99     */
100    private $stmtParams = array();
101
102    /**
103     * transaction status
104     *
105     * @access private
106     * @var array
107     */
108    private $trans = array();
109
110    /**
111     * Constructor
112     *
113     * @param DSN   $dsn
114     * @param array $ssl
115     */
116    public function __construct($dsn = null, $ssl = null)
117    {
118        if ($dsn) {
119            $dsn = parse_url($dsn);
120            $this->dsn['charset'] = isset($dsn['scheme']) ? (($dsn['scheme'] == 'mysql') ? null : $dsn['scheme']) : 'utf8';
121            if (isset($dsn['host'])) {
122                $host = explode('=', $dsn['host']);
123                if ($host[0] == 'unix_socket') {
124                    $this->dsn['host'] = null;
125                    $this->dsn['socket'] = str_replace('\\', '/', $host[1]);
126                } else {
127                    $this->dsn['host'] = rawurldecode($dsn['host']);
128                    $this->dsn['socket'] = null;
129                }
130            } else {
131                $this->dsn['host'] = '127.0.0.1';
132            }
133            $this->dsn['port'] = isset($dsn['port']) ? $dsn['port'] : 3306;
134            $this->dsn['user'] = isset($dsn['user']) ? rawurldecode($dsn['user']) : null;
135            $this->dsn['pass'] = isset($dsn['pass']) ? rawurldecode($dsn['pass']) : null;
136            $this->dsn['dbName'] = isset($dsn['path']) ? rawurldecode(substr($dsn['path'], 1)) : null;
137            $this->dsn['cache'] = isset($dsn['query']) ? $dsn['query'] : null;
138            $this->dsn['ssl'] = $ssl;
139        } else {
140            throw new \InvalidArgumentException('DSN missing: charset://username:password@host:port/database');
141        }
142    }
143
144    /**
145     * Opens a connection to a mysql server
146     */
147    public function connect()
148    {
149        if ($this->isConnected()) {
150            if ($this->debug) $this->debug->log(__METHOD__, 'still connected');
151            return;
152        }
153
154        if (!extension_loaded('mysqli')) {
155            die('The Mysqli extension is required');
156        }
157
158        $mysqli = mysqli_init();
159
160        if (!$mysqli) {
161            die('mysqli_init failed');
162        }
163
164        if (!$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, defined('DALMP_CONNECT_TIMEOUT') ? DALMP_CONNECT_TIMEOUT : 5)) {
165            throw new \Exception('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed');
166        }
167
168        if (defined('DALMP_MYSQLI_INIT_COMMAND')) {
169            if (!$mysqli->options(MYSQLI_INIT_COMMAND, DALMP_MYSQLI_INIT_COMMAND)) {
170                throw new \Exception('Setting MYSQLI_INIT_COMMAND failed');
171            }
172        }
173
174        if (is_array($this->dsn['ssl'])) {
175            if ($this->debug) $this->debug->log('DSN', 'SSL', $this->dsn['ssl']);
176            $mysqli->ssl_set($this->dsn['ssl']['key'], $this->dsn['ssl']['cert'], $this->dsn['ssl']['ca'], $this->dsn['ssl']['capath'], $this->dsn['ssl']['cipher']);
177        }
178
179        if ($this->debug) $this->debug->log(__METHOD__, 'connecting');
180
181        $rs = @$mysqli->real_connect($this->dsn['host'], $this->dsn['user'], $this->dsn['pass'], $this->dsn['dbName'], $this->dsn['port'], $this->dsn['socket']);
182        if ($mysqli->connect_errno || $rs === false) {
183            if ($this->debug) $this->debug->log(__METHOD__, 'ERROR', 'mysqli connection error');
184            throw new \Exception(mysqli_connect_error(), mysqli_connect_errno());
185        }
186
187        if ($this->dsn['charset']) {
188            if (!$mysqli->set_charset($this->dsn['charset'])) {
189                trigger_error("Error loading character set {$this->dsn['charset']}", E_USER_NOTICE);
190            }
191        }
192
193        $this->DB = $mysqli;
194    }
195
196    /**
197     * debuger
198     *
199     * @param boolean $log2file
200     * @param mixed   $debugFile
201     */
202    public function debug($log2file = false, $debugFile = false)
203    {
204        if ($log2file == 'off') {
205            if (is_object($this->debug)) {
206                $this->debug->getLog();
207                $this->debug = false;
208            }
209        } else {
210            $debugFile = $debugFile ?: (defined('DALMP_DEBUG_FILE') ? DALMP_DEBUG_FILE : '/tmp/dalmp.log');
211            $this->debug = new Logger($log2file, $debugFile);
212            $this->debug->log('DSN', $this->dsn);
213            if ($this->isConnected()) {
214                $this->debug->log('DALMP', mysqli_get_host_info($this->DB), 'protocol version: ' . mysqli_get_proto_info($this->DB), 'character set: ' . mysqli_character_set_name($this->DB));
215            }
216        }
217
218        return;
219    }
220
221    /**
222     * isConnected
223     *
224     * @return boolean
225     */
226    public function isConnected()
227    {
228        return $this->DB instanceof \mysqli;
229    }
230
231    /**
232     * Closes a previously opened database connection
233     */
234    public function closeConnection()
235    {
236        if ($this->debug) $this->debug->log(__METHOD__);
237        $this->isConnected() && $this->DB->close();
238        $this->DB = null;
239    }
240
241    /**
242     * Frees the memory associated with a result
243     */
244    public function Close()
245    {
246        if ($this->debug) $this->debug->log(__METHOD__);
247        return ($this->isConnected()) && $this->_rs->close();
248    }
249
250    /**
251     * Frees stored result memory for the given statement handle &
252     * Closes a prepared statement
253     */
254    public function PClose()
255    {
256        if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__);
257        if ($this->isConnected()) {
258            $this->_stmt->free_result();
259
260            return $this->_stmt->close();
261        } else {
262            return false;
263        }
264    }
265
266    /**
267     * getNumOfRows
268     *
269     * @return int num of rows
270     */
271    public function getNumOfRows()
272    {
273        return $this->numOfRows;
274    }
275
276    /**
277     * getNumOfRowsAffected
278     *
279     * @return int num of rows affected
280     */
281    public function getNumOfRowsAffected()
282    {
283        return $this->numOfRowsAffected;
284    }
285
286    /**
287     * getNumOfFields
288     *
289     * @return int num of fields
290     */
291    public function getNumOfFields()
292    {
293        return $this->numOfFields;
294    }
295
296    /**
297     * Get the column names
298     *
299     * @param $table;
300     * @return array or false if no table set
301     */
302    public function getColumnNames($table = null)
303    {
304        return ($table) ? $this->getCol("DESCRIBE $table") : false;
305    }
306
307    /**
308     * Sets the Fetch Mode
309     *
310     * @chainable
311     * @param ASSOC = MYSQLI_ASSOC, NUM = MYSQLI_NUM, null = MYSQLI_BOTH.
312     */
313    public function FetchMode($mode = null)
314    {
315        switch (strtoupper($mode)) {
316        case 'NUM':
317            $this->fetchMode = MYSQLI_NUM;
318            break;
319
320        case 'ASSOC':
321            $this->fetchMode = MYSQLI_ASSOC;
322            break;
323
324        default :
325            $this->fetchMode = MYSQLI_BOTH;
326        }
327
328        if ($this->debug) $this->debug->log(__METHOD__, $mode, $this->fetchMode);
329        return $this;
330    }
331
332    /**
333     * Prepare arguments
334     *
335     * @param  string $args
336     * @return array  with arguments;
337     */
338    public function Prepare()
339    {
340        if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__, func_get_args());
341
342        switch (func_num_args()) {
343        case 1:
344            $param = func_get_arg(0);
345            $clean = true;
346            break;
347
348        case 2:
349            $key = func_get_arg(0);
350            $param = func_get_arg(1);
351            if (in_array($key, array('i', 'd', 's', 'b'), true)) {
352                $this->stmtParams[] = array($key => $param);
353            } else {
354                $clean = true;
355            }
356            break;
357
358        default :
359            return $this->stmtParams;
360        }
361
362        if (isset($clean)) {
363            if (is_numeric($param)) {
364                $param = !strcmp(intval($param), $param) ? (int) $param : (!strcmp(floatval($param), $param) ? (float) $param : $param);
365            }
366            $key = is_int($param) ? 'i' : (is_float($param) ? 'd' : (is_string($param) ? 's' : 'b'));
367
368            return $this->stmtParams[] = array($key => $param);
369        }
370    }
371
372    /**
373     * Prepared Statements
374     *
375     * example: PGetAll('SELECT * FROM users WHERE name=? AND id=?', 'name', 1, 'db1')
376     * user also can define  the corresponding type of the bind variables (i, d, s, b): http://www.php.net/manual/en/mysqli-stmt.bind-param.php
377     * example: PGetAll('SELECT * FROM table WHERE name=? AND id=?', array('s'=>'99.3', 7)); or use the Prepare() method
378     *
379     * @param SQL    $sql
380     * @param string $params
381     */
382    public function PExecute()
383    {
384        $args = func_get_args();
385        if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__, $args);
386
387        !$this->isConnected() && $this->connect();
388
389        $sql = array_shift($args);
390        $this->_stmt = $this->DB->prepare($sql);
391
392        if (!$this->_stmt) {
393            $this->closeConnection();
394            trigger_error('ERROR -> ' . __METHOD__ . ": Please check your sql statement, unable to prepare: $sql with args: " . json_encode($args), E_USER_ERROR);
395        }
396
397        $params = array();
398        $types = null;
399
400        $args = is_array(current($args)) ? current($args) : $args;
401
402        if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__, 'args:',$args);
403
404        if (!empty($args)) {
405
406            foreach ($args as $key => $param) {
407                $params[] = &$args[$key];
408
409                if (!in_array($key, array('i', 'd', 's', 'b'), true)) {
410
411                    if (is_numeric($param)) {
412                        $param = !strcmp(intval($param), $param) ? (int) $param : (!strcmp(floatval($param), $param) ? (float) $param : $param);
413                    }
414
415                    if (is_null($param)) {
416                        $key = 's';
417                    } else {
418                        $key = is_int($param) ? 'i' : (is_float($param) ? 'd' : (is_string($param) ? 's' : 'b'));
419                    }
420                }
421
422                if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__, "key: $key param: $param");
423                $types .= $key;
424            }
425
426            array_unshift($params, $types);
427
428            if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__, "sql: $sql params:", $params);
429
430            call_user_func_array(array($this->_stmt, 'bind_param'), $params);
431        }
432
433        /**
434         * if you get erros like 'Illegal mix of collations
435         * (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)'
436         * try to set your table fiels to: "character set: UTF8"
437         * and "collation: utf8_unicode_ci"
438         */
439        if ($this->_stmt->execute()) {
440            $this->_stmt->store_result();
441            if (is_object($this->_stmt->result_metadata())) {
442                $this->numOfRows = $this->_stmt->num_rows;
443                $this->numOfFields = $this->_stmt->field_count;
444                if (!$this->_stmt->num_rows) {
445                    return false;
446                }
447            }
448
449            $this->numOfRowsAffected = $this->_stmt->affected_rows;
450
451            /**
452             * An integer greater than zero indicates the number of rows affected
453             * or retrieved. Zero indicates that no records where updated for an
454             * UPDATE/DELETE statement, no rows matched the WHERE clause in the query
455             * or that no query has yet been executed. -1 indicates that the query has
456             * returned an error. NULL indicates an invalid argument was supplied to the
457             * function.
458             */
459            if ($this->_stmt->affected_rows > 0) {
460                return true;
461            } elseif ($this->_stmt->affected_rows == -1) {
462                return false;
463            } else {
464                return $this->_stmt->affected_rows;
465            }
466        } else {
467            if (array_key_exists('error', $this->trans)) {
468                $this->trans['error']++;
469            }
470
471            if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__, 'ERROR', "sql: $sql  params: ", $params, " Errorcode:" . $this->DB->errno);
472
473            throw new \ErrorException(__METHOD__ . 'ERROR -> ' . $this->DB->error . " - sql: $sql with params: " . json_encode($params));
474        }
475    }
476
477    /**
478     * Prepared Statements query
479     *
480     * @param array $row
481     */
482    public function Pquery(&$row)
483    {
484        if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__);
485        $meta = $this->_stmt->result_metadata();
486        $columns = array();
487
488        while ($column = $meta->fetch_field()) {
489            $columns[] = &$row[$column->name];
490        }
491        call_user_func_array(array($this->_stmt, 'bind_result'), $columns);
492
493        return $this->_stmt->fetch();
494    }
495
496    /**
497     * _pFetch
498     *
499     * @access protected
500     * @return array
501     */
502    protected function _pFetch($get = null)
503    {
504        if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__, $get);
505
506        if (!$this->_stmt->num_rows) {
507            $this->PClose();
508
509            return false;
510        }
511
512        $meta = $this->_stmt->result_metadata();
513        $columns = array();
514        $results = array();
515
516        while (($column = $meta->fetch_field()) !== false) {
517            $columns[$column->name] = &$results[$column->name];
518        }
519
520        call_user_func_array(array($this->_stmt, 'bind_result'), $columns);
521
522        $rs = array();
523
524        switch ($get) {
525        case 'one':
526            while ($this->_stmt->fetch()) {
527                $rs = array_shift($columns);
528                break;
529            }
530            $rs = is_array($rs) ? reset($rs) : $rs;
531            break;
532
533        case 'col':
534            while ($this->_stmt->fetch()) {
535                $rs[] = reset($columns);
536            }
537            break;
538
539        case 'assoc':
540            if ($this->numOfFields < 2) {
541                if ($this->debug) { $this->debug->log('PreparedStatements', __METHOD__, 'ERROR', $get, 'num of columns < 2'); }
542
543            return false;
544            }
545            if ($this->numOfFields == 2) {
546                while ($this->_stmt->fetch()) {
547                    $rs[reset($columns)] = next($columns);
548                }
549            } else {
550                while ($this->_stmt->fetch()) {
551                    $rs[reset($columns)] = array_slice($columns, 1);
552                }
553            }
554            break;
555
556        default :
557            while ($this->_stmt->fetch()) {
558                $tmp = array();
559                foreach ($columns as $key => $val) {
560                    switch ($this->fetchMode) {
561                    case MYSQLI_NUM:
562                        $tmp[] = $val;
563                        break;
564
565                    case MYSQLI_ASSOC:
566                        $tmp[$key] = $val;
567                        break;
568
569                    default :
570                        $tmp[] = $val;
571                        $tmp[$key] = $val;
572                        break;
573                    }
574                }
575                array_push($rs, $tmp);
576                if ($get == 'row') {
577                    $rs = array_shift($rs);
578                    break;
579                }
580            }
581        }
582
583        $this->PClose();
584
585        return empty($rs) ? false : $rs;
586    }
587
588    /**
589     * _fetch
590     *
591     * @access protected
592     * @return array of strings that corresponds to the fetched row or false
593     */
594    protected function _fetch()
595    {
596        if ($this->debug) $this->debug->log(__METHOD__);
597        return $this->_rs->fetch_array($this->fetchMode) ?: false;
598    }
599
600    /**
601     * Auto Execute
602     *
603     * @param  string $table
604     * @param  array  $fields
605     * @param  string $mode
606     * @param  string $where
607     * @return true   or false on error
608     */
609    public function AutoExecute($table, array $fields, $mode = 'INSERT', $where = null)
610    {
611        if ($this->debug) $this->debug->log(__METHOD__, 'args:', $table, $fields, $mode, $where);
612
613        $mode = (strtoupper($mode) == 'INSERT') ? 'INSERT' : 'UPDATE';
614
615        if ($mode == 'UPDATE' && !$where) {
616            if ($this->debug) $this->debug->log( __METHOD__, 'ERROR', 'WHERE clause missing');
617            throw new \InvalidArgumentException(__METHOD__ . ' WHERE clause missing');
618        }
619
620        if ($columnNames = $this->getColumnNames($table)) {
621            $data = array();
622            $placeholder = '';
623            foreach ($columnNames as $col) {
624                if (isset($fields[$col])) {
625                    $data["`$col`"] = $fields[$col];
626                    $placeholder.= '?,';
627                }
628            }
629            if (empty($data)) {
630                if ($this->debug) $this->debug->log(__METHOD__, 'ERROR', "no matching fields on table: $table with fields:", $fields);
631                return false;
632            }
633        } else {
634            return false;
635        }
636
637        if ($mode == 'INSERT') {
638            $fields = implode(', ', array_keys($data));
639            $placeholder = rtrim($placeholder, ',');
640            $query = array_values($data);
641            $sql = "INSERT INTO $table ($fields) VALUES($placeholder)";
642        } else {
643            $fields = implode('=?, ', array_keys($data));
644            $fields.= '=?';
645            $query = array_values($data);
646            $sql = "UPDATE $table SET $fields WHERE $where";
647        }
648
649        return call_user_func_array(array($this, 'PExecute'), array($sql, $query));
650    }
651
652    /**
653     * multiple insert
654     *
655     * @param  string  $table
656     * @param  array   $col_name        example array('col1', 'col2')
657     * @param  array   $multiple_values example array(array('val1', 'val2'))
658     * @return boolean
659     */
660    public function multipleInsert($table, array $col_name, array $multiple_values)
661    {
662        $num_of_fields = count($col_name);
663        if ($num_of_fields != count(end($multiple_values))) {
664            throw new \InvalidArgumentException('number of values do not match number of columns');
665        }
666
667        $pvalues = '';
668        $values = array();
669
670        foreach ($multiple_values as $value) {
671            $placeholder ='(';
672            for ($i = 0; $i < $num_of_fields; $i++) {
673                $values[] = isset($value[$i]) ? $value[$i] : null;
674                $placeholder .= '?,';
675            }
676            $pvalues .= rtrim($placeholder, ',') . '),';
677            $placeholder = null;
678        }
679
680        $pvalues = rtrim($pvalues,',');
681
682        $columns = array_map(create_function('$n', 'return "`$n`";'), $col_name);
683
684        $sql = "INSERT INTO $table (" . implode(',', $columns) . ") VALUES $pvalues";
685
686        if ($this->debug) $this->debug->log(__METHOD__, $sql, $values);
687        return call_user_func_array(array($this, 'PExecute'), array($sql, $values));
688    }
689
690    /**
691     * Execute SQL statement
692     *
693     * @param  strign $sql
694     * @return true   or false if there was an error in executing the sql.
695     */
696    public function Execute($sql)
697    {
698        if ($this->debug) $this->debug->log(__METHOD__, "sql: $sql");
699
700        !$this->isConnected() && $this->connect();
701
702        if ($rs = $this->DB->query($sql)) {
703            if (is_object($rs)) {
704                $this->_rs = $rs;
705                $this->numOfRows = $this->_rs->num_rows;
706                $this->numOfFields = $this->_rs->field_count;
707                if ($this->debug) $this->debug->log(__METHOD__, 'returned object', "#rows: $this->numOfRows #fields: $this->numOfFields");
708                if (!$this->numOfRows) {
709                    $this->Close();
710
711                    return false;
712                }
713            }
714
715            $this->numOfRowsAffected = $this->DB->affected_rows;
716
717            /**
718             * An integer greater than zero indicates the number of rows affected or
719             * retrieved. Zero indicates that no records were updated for an UPDATE
720             * statement, no rows matched the WHERE clause in the query or that no query
721             * has yet been executed. -1 indicates that the query returned an error.
722             */
723            if ($this->DB->affected_rows > 0) {
724                return true;
725            } elseif ($this->DB->affected_rows == -1) {
726                return false;
727            } else {
728                return $this->DB->affected_rows;
729            }
730        } else {
731            if (array_key_exists('error', $this->trans)) {
732                $this->trans['error']++;
733            }
734            if ($this->debug) $this->debug->log(__METHOD__, 'ERROR', "sql: $sql Errorcode: " . $this->DB->errno);
735            throw new \ErrorException(__METHOD__ . ' ERROR -> ' . $this->DB->error . " - sql: $sql");
736        }
737    }
738
739    /**
740     * Query
741     *
742     * @see _fetch
743     * @return array or null
744     */
745    public function query()
746    {
747        if ($this->debug) $this->debug->log(__METHOD__);
748        return $this->_fetch();
749    }
750
751    /**
752     * Export to CSV
753     *
754     * @param  string $sql
755     * @return csv
756     */
757    public function csv()
758    {
759        $args = func_get_args();
760        if ($this->debug) $this->debug->log(__METHOD__, $args);
761
762        switch (func_num_args()) {
763        case 1:
764            if (call_user_func_array(array($this, 'Execute'), $args)) {
765                $row = $this->_rs->fetch_array(MYSQLI_ASSOC);
766                $fp = fopen('php://output', 'w');
767                fputcsv($fp, array_keys($row));
768                $this->_rs->data_seek(0);
769                while ($row = $this->_rs->fetch_array(MYSQLI_NUM)) {
770                    fputcsv($fp, $row);
771                }
772                $this->Close();
773                fclose($fp);
774            }
775            break;
776
777        default:
778            if (call_user_func_array(array($this, 'PExecute'), $args)) {
779                $meta = $this->_stmt->result_metadata();
780                $columns = array();
781                $results = array();
782                while (($column = $meta->fetch_field()) !== false) {
783                    $columns[$column->name] = &$results[$column->name];
784                }
785                $fp = fopen('php://output', 'w');
786                fputcsv($fp, array_keys($columns));
787                call_user_func_array(array($this->_stmt, 'bind_result'), $columns);
788                while ($this->_stmt->fetch()) {
789                    fputcsv($fp, $columns);
790                }
791                $this->PClose();
792                fclose($fp);
793            }
794        }
795    }
796
797    /**
798     * maps the result to an object
799     *
800     * @param sting sql the query string
801     * @param string class_name of the class to instantiate
802     * @param array optional array of parameters to pass to the constructor for class_name objects.
803     * @see mysqli_result::fetch_object
804     * @return object or false
805     */
806    public function map($sql, $class_name=null, $params=array())
807    {
808        if ($this->debug) $this->debug->log(__METHOD__, "sql: $sql");
809        if ($this->Execute($sql)) {
810            return ($class_name) ? $this->_rs->fetch_object($class_name, $params) : $this->_rs->fetch_object();
811        } else {
812            return false;
813        }
814    }
815
816    /**
817     * Fetch a result row as an associative, a numeric array, or both
818     *
819     * @param  SQL   $sql
820     * @return array or false
821     */
822    public function getAll($sql)
823    {
824        if ($this->debug) $this->debug->log(__METHOD__, "sql: $sql");
825        if ($this->Execute($sql)) {
826            $rows = array();
827            while ($row = $this->_fetch()) {
828                $rows[] = $row;
829            }
830            $this->Close();
831
832            return $rows;
833        } else {
834            return false;
835        }
836    }
837
838    /**
839     * Get a Row
840     *
841     * @param  SQL $sql
842     * @return the first row as an array or false.
843     */
844    public function getRow($sql)
845    {
846        if ($this->debug) $this->debug->log(__METHOD__, "sql: $sql");
847        if ($this->Execute($sql)) {
848            $row = $this->_fetch();
849            $this->Close();
850
851            return $row;
852        } else {
853            return false;
854        }
855    }
856
857    /**
858     * Get a Column
859     *
860     * @param  SQL $sql
861     * @return the first column as an array, or false.
862     */
863    public function getCol($sql)
864    {
865        if ($this->debug) $this->debug->log(__METHOD__, "sql: $sql");
866        if ($this->Execute($sql)) {
867            $col = array();
868            while ($row = $this->_rs->fetch_row()) {
869                $col[] = reset($row);
870            }
871            $this->Close();
872
873            return $col;
874        } else {
875            return false;
876        }
877    }
878
879    /**
880     * Get One
881     *
882     * @param  SQL $sql
883     * @return the first field of the first row, or false.
884     */
885    public function getOne($sql)
886    {
887        if ($this->debug) $this->debug->log(__METHOD__, "sql: $sql");
888        if ($this->Execute($sql)) {
889            $row = $this->_rs->fetch_row();
890            $this->Close();
891
892            return reset($row);
893        } else {
894            return false;
895        }
896    }
897
898    /**
899     * Get an associative array using the first column as keys
900     *
901     * @param  SQL         $sql
902     * @return associative array, false if columns < 2, or no records found.
903     */
904    public function getASSOC($sql)
905    {
906        if ($this->debug) $this->debug->log(__METHOD__, "sql: $sql");
907        if ($this->Execute($sql)) {
908            $cols = $this->numOfFields;
909            if ($cols < 2) {
910                return false;
911            }
912            $this->fetchMode = MYSQLI_ASSOC;
913            $assoc = array();
914            if ($cols == 2) {
915                while ($row = $this->_fetch()) {
916                    $assoc[reset($row)] = next($row);
917                }
918            } else {
919                while ($row = $this->_fetch()) {
920                    $assoc[reset($row)] = array_slice($row, 1);
921                }
922            }
923            $this->Close();
924
925            return $assoc;
926        } else {
927            return false;
928        }
929    }
930
931    /**
932     * Start the transaction
933     */
934    public function StartTrans()
935    {
936        if (empty($this->trans)) {
937            if ($this->debug) $this->debug->log('transactions', __METHOD__, $this->trans);
938            $this->trans = array('level' => 0, 'error' => 0);
939
940            return $this->Execute('BEGIN');
941        } else {
942            $this->trans['level']++;
943            if ($this->debug) $this->debug->log('transactions', __METHOD__, array('transaction level' => $this->trans['level']));
944            return $this->Execute(sprintf('SAVEPOINT level%d', $this->trans['level']));
945        }
946    }
947
948    /**
949     * Complete the transaction
950     *
951     * @return boolean
952     */
953    public function CompleteTrans()
954    {
955        if ($this->debug) $this->debug->log('transactions', __METHOD__, $this->trans);
956        if (empty($this->trans)) {
957            return false;
958        } else {
959            if ($this->trans['error'] > 0) {
960                if ($this->debug) $this->debug->log('transactions', __METHOD__, 'ERROR', array('error in level' => $this->trans['level']));
961                if ($this->trans['level'] > 0) {
962                    $this->Execute(sprintf('ROLLBACK TO SAVEPOINT level%d', $this->trans['level']));
963                    $this->trans['level']--;
964                } else {
965                    $this->Execute('ROLLBACK');
966                }
967
968                return false;
969            }
970            if ($this->trans['level'] == 0) {
971                $this->trans = array();
972
973                return ($this->Execute('COMMIT') === false) ? false : true;
974            } else {
975                $rs = $this->Execute(sprintf('RELEASE SAVEPOINT level%d', $this->trans['level']));
976                $this->trans['level']--;
977
978                return ($rs === false) ? false : true;
979            }
980        }
981    }
982
983    /**
984     * Rollback the transaction
985     *
986     * @return false if there was an error executing the ROLLBACK.
987     */
988    public function RollBackTrans()
989    {
990        if ($this->debug) $this->debug->log('transactions', __METHOD__, $this->trans);
991        if (isset($this->trans['level']) && $this->trans['level'] > 0) {
992            $rs = $this->Execute(sprintf('ROLLBACK TO SAVEPOINT level%d', $this->trans['level']));
993            $this->trans['level']--;
994
995            return $rs;
996        } else {
997            return $this->Execute('ROLLBACK');
998        }
999    }
1000
1001    /**
1002     * Insert_Id
1003     *
1004     * @return int the auto generated id used in the last query
1005     */
1006    public function Insert_Id()
1007    {
1008        if ($this->debug) $this->debug->log(__METHOD__);
1009        return $this->DB->insert_id;
1010    }
1011
1012    /**
1013     * ErrorMsg
1014     *
1015     * @return string description of the last error
1016     */
1017    public function ErrorMsg()
1018    {
1019        return $this->DB->error;
1020    }
1021
1022    /**
1023     * ErrorNum
1024     *
1025     * @return int error code
1026     */
1027    public function ErrorNum()
1028    {
1029        return $this->DB->errno;
1030    }
1031
1032    /**
1033     * Quotes a string
1034     *
1035     * @param string $value
1036     */
1037    public function qstr($value)
1038    {
1039        if ($this->debug) $this->debug->log(__METHOD__, func_get_args());
1040        if (is_int($value) || is_float($value)) {
1041            $rs = $value;
1042        } else {
1043            !$this->isConnected() && $this->connect();
1044            $rs = $this->DB->real_escape_string($value);
1045        }
1046        if ($this->debug) $this->debug->log(__METHOD__, "returned: $rs");
1047        return $rs;
1048    }
1049
1050    /**
1051     * renumber
1052     *
1053     * @param  string  $table
1054     * @param  int     $col
1055     * @return boolean
1056     */
1057    public function renumber($table, $col = 'id')
1058    {
1059        if (isset($table)) {
1060            return $this->Execute('SET @var_dalmp=0') ? ($this->Execute("UPDATE $table SET $col = (@var_dalmp := @var_dalmp +1)") ? $this->Execute("ALTER TABLE $table AUTO_INCREMENT = 1") : false) : false;
1061        } else {
1062            return false;
1063        }
1064    }
1065
1066    /**
1067     * forceTruncate - force truncate of a table
1068     *
1069     * @param  string  $table
1070     * @return boolean
1071     */
1072    public function forceTruncate($table)
1073    {
1074        return (false !== $this->Execute('SET FOREIGN_KEY_CHECKS = 0') && false !== $this->Execute(sprintf('TRUNCATE %s', $table)));
1075    }
1076
1077    /**
1078     * useCache
1079     *
1080     * @param DALMP\Cache $cache
1081     */
1082    public function useCache(Cache $cache)
1083    {
1084        if ($this->debug) $this->debug->log(__METHOD__, $cache);
1085        $this->cache = $cache;
1086    }
1087
1088    /**
1089     * DALMP\Cache fabric method
1090     *
1091     * @return DAMLP\Cache instance
1092     */
1093    public function Cache()
1094    {
1095        if ($this->debug) $this->debug->log(__METHOD__, 'Creating cache using DSN');
1096        if ($this->cache instanceof Cache) {
1097            return $this->cache;
1098        } else {
1099            list($type, $host, $port, $compress) = @explode(':', $this->dsn['cache']) + array(null, null, null, null);
1100            $type = strtolower($type);
1101            switch ($type) {
1102            case 'memcache':
1103                $cache = new Cache\Memcache($host, $port, 1, $compress);
1104                break;
1105
1106            case 'redis':
1107                $cache = new Cache\Redis($host, $port, $compress);
1108                break;
1109
1110            case 'disk':
1111            default:
1112                $cache = new Cache\Disk($host);
1113                break;
1114            }
1115            $this->cache = new Cache($cache);
1116        }
1117
1118        return $this->cache;
1119    }
1120
1121    /**
1122     * general method for caching
1123     *
1124     * @param  string   $fetch_method
1125     * @param  int      $expire
1126     * @param  string   $sql
1127     * @param  string   $key
1128     * @param  string   $group
1129     * @return boolean;
1130     */
1131    protected function _Cache()
1132    {
1133        $args = func_get_args();
1134
1135        if ($this->debug) $this->debug->log(__METHOD__, 'Args', $args);
1136
1137        $fetch = array_shift($args);
1138        $expire = (int) (reset($args)) ? array_shift($args) : 3600;
1139        $sql = array_shift($args);
1140        $key = isset($args[0]) ? $args[0] : $fetch;
1141
1142        if (strncmp($key, 'group:', 6) == 0) {
1143            $group = $key;
1144            $key = $fetch;
1145        } else {
1146            $group = (isset($args[1]) and (strncmp($args[1], 'group:', 6) == 0)) ? $args[1] : null;
1147        }
1148
1149        $skey = defined('DALMP_SITE_KEY') ? DALMP_SITE_KEY : 'DALMP';
1150        $hkey = sha1($skey . $sql . $key);
1151
1152        if ($this->debug) $this->debug->log(__METHOD__, 'Parsed Args', array('fetch method' => $fetch, 'expire' => $expire, 'sql' => $sql, 'key' => $key, 'group' => $group), array('Cache key' => $hkey));
1153
1154        is_null($this->cache) && $this->Cache();
1155
1156        if ($this->cache instanceof Cache && $cache = $this->cache->get($hkey)) {
1157            if ($this->debug) $this->debug->log(__METHOD__, 'serving from cache');
1158            return $cache;
1159        } else {
1160            switch ($fetch) {
1161            case 'all':
1162                $cache = $this->getAll($sql);
1163                break;
1164            case 'row':
1165                $cache = $this->getRow($sql);
1166                break;
1167            case 'col':
1168                $cache = $this->getCol($sql);
1169                break;
1170            case 'one':
1171                $cache = $this->getOne($sql);
1172                break;
1173            case 'assoc':
1174                $cache = $this->getASSOC($sql);
1175                break;
1176            }
1177
1178            if ($this->cache instanceof Cache) {
1179                $this->_setCache($hkey, $cache, $expire, $group);
1180            } else {
1181                trigger_error('Cache instance not defined, use the method useCache($cache) to set a cache engine.', E_USER_WARNING);
1182            }
1183
1184            if ($this->debug) $this->debug->log(__METHOD__, 'Set', array('key' => $hkey, 'expire' => $expire, 'group' => $group));
1185            return $cache;
1186        }
1187    }
1188
1189    /**
1190     * method for caching prepared statements
1191     *
1192     * @param  string   $fetch_method
1193     * @param  int      $expire
1194     * @param  string   $sql
1195     * @param  string   $key
1196     * @param  string   $group
1197     * @return boolean;
1198     */
1199    protected function _CacheP()
1200    {
1201        $args = func_get_args();
1202
1203        if ($this->debug) $this->debug->log(__METHOD__, 'Args', $args);
1204
1205        $fetch = array_shift($args);
1206        $expire = (int) (reset($args)) ? array_shift($args) : 3600;
1207        $sql = array_shift($args);
1208
1209        // expected params
1210        $eparams = count(explode('?', $sql, -1));
1211        $targs = count($args);
1212        $args = is_array(current($args)) ? current($args) : $args;
1213        if ($targs > $eparams) {
1214            if (($targs - $eparams) == 1) {
1215                $key = array_pop($args);
1216                $params = $args;
1217                if (strncmp($key, 'group:', 6) == 0) {
1218                    $group = $key;
1219                    $key = $fetch . implode('|', array_merge(array_keys($args), $args));
1220                } else {
1221                    $group = null; // only key no group
1222                }
1223            } else {
1224                $group = array_pop($args);
1225                $group = (strncmp($group, 'group:', 6) == 0) ? $group : null;
1226                $key = array_pop($args);
1227                $params = $args;
1228            }
1229        } else {
1230            $key = $fetch . implode('|', array_merge(array_keys($args), $args));
1231            $params = $args;
1232            $group = null;
1233        }
1234
1235        array_unshift($args, $sql);
1236
1237        $skey = defined('DALMP_SITE_KEY') ? DALMP_SITE_KEY : 'DALMP';
1238        $hkey = sha1($skey . $sql . $key);
1239
1240        if ($this->debug) $this->debug->log(__METHOD__, 'Parsed Args', array('fetch method' => $fetch, 'expire' => $expire, 'sql' => $sql, 'key' => $key, 'group' => $group), array('Cache key' => $hkey));
1241
1242        is_null($this->cache) && $this->Cache();
1243
1244        if ($this->cache instanceof Cache && $cache = $this->cache->Get($hkey)) {
1245            if ($this->debug) $this->debug->log(__METHOD__, 'serving from cache');
1246            return $cache;
1247        } else {
1248            $nargs = array();
1249            foreach (array_keys($args) as $akey) {
1250                if (!is_int($akey)) {
1251                    $nargs['dalmp'][$akey] = $args[$akey];
1252                } else {
1253                    $nargs[] = $args[$akey];
1254                }
1255            }
1256            call_user_func_array(array($this, 'PExecute'), $nargs);
1257            $cache = $this->_pFetch($fetch);
1258
1259            if ($this->cache instanceof Cache) {
1260                $this->_setCache($hkey, $cache, $expire, $group);
1261            } else {
1262                trigger_error('Cache instance not defined, use the method useCache($cache) to set a cache engine.', E_USER_WARNING);
1263            }
1264
1265            if ($this->debug) $this->debug->log(__METHOD__, 'Set', array('key' => $hkey, 'expire' => $expire, 'group' => $group));
1266            return $cache;
1267        }
1268    }
1269
1270    /**
1271     * _setCache - store data in cache
1272     *
1273     * @access protected
1274     * @param  string  $hkey   The key that will be associated with the item.
1275     * @param  data    $cache  The variable to store
1276     * @param  int     $expire Expiration time of the item
1277     * @param  string  $group  group:name (to group cache keys) usefull when flushing the cache
1278     * @return boolean
1279     */
1280    protected function _setCache($hkey, $cache, $expire = 3600, $group = null)
1281    {
1282        is_null($this->cache) && $this->Cache();
1283
1284        if ($group) {
1285            $skey = defined('DALMP_SITE_KEY') ? DALMP_SITE_KEY : 'DALMP';
1286            $gkey = sha1($skey . $group);
1287
1288            if ($gCache = $this->cache->Get($gkey)) {
1289                foreach ($gCache as $key => $exp) {
1290                    if ($exp < time()) {
1291                        unset($gCache[$key]);
1292                    }
1293                }
1294            } else {
1295                $gCache = array();
1296            }
1297
1298            $gCache[$hkey] = time() + $expire;
1299
1300            if (!($this->cache->Set($hkey, $cache, $expire) && $this->cache->Set($gkey, $gCache, 0))) {
1301                throw new \UnexpectedValueException('Can not store data on cache');
1302            }
1303        } else {
1304            if (!$this->cache->Set($hkey, $cache, $expire)) {
1305                throw new \UnexpectedValueException('Can not store data on cache');
1306            }
1307        }
1308
1309        return true;
1310    }
1311
1312    /**
1313     * Cache flush
1314     *
1315     * @param  string  $sql, SQL, cache group or null
1316     * @param  string  $key
1317     * @return boolean
1318     */
1319    public function CacheFlush($sql = null, $key = null)
1320    {
1321        is_null($this->cache) && $this->Cache();
1322
1323        if (is_null($sql)) {
1324            if ($this->debug) $this->debug->log(__METHOD__, 'Flushing all cache');
1325            return $this->cache->Flush();
1326        }
1327
1328        $skey = defined('DALMP_SITE_KEY') ? DALMP_SITE_KEY : 'DALMP';
1329        $hkey = sha1($skey . $sql . $key);
1330
1331        if (strncmp($sql, 'group:', 6) == 0) {
1332            $gkey = sha1($skey . $sql);
1333            if ($group = $this->cache->get($gkey)) {
1334                if ($this->debug) $this->debug->log(__METHOD__, 'group', array('group' => $sql, 'Cache group key' => $gkey));
1335                foreach ($group as $key => $timeout) {
1336                    $this->cache->Delete($key);
1337                }
1338            }
1339        }
1340
1341        if ($this->debug) $this->debug->log(__METHOD__, 'Delete', array('sql' => $sql, 'key' => $hkey));
1342        return $this->cache->Delete($hkey);
1343    }
1344
1345    /**
1346     * @return string server Version
1347     */
1348    public function getServerVersion()
1349    {
1350        $version = $this->DB->server_version;
1351        $major = (int) ($version / 10000);
1352        $minor = (int) ($version % 10000 / 100);
1353        $revision = (int) ($version % 100);
1354
1355        return $major . '.' . $minor . '.' . $revision;
1356    }
1357
1358    /**
1359     * @return string Client Version
1360     */
1361    public function getClientVersion()
1362    {
1363        $version = $this->DB->client_version;
1364        $major = (int) ($version / 10000);
1365        $minor = (int) ($version % 10000 / 100);
1366        $revision = (int) ($version % 100);
1367
1368        return $major . '.' . $minor . '.' . $revision;
1369    }
1370
1371    /**
1372     * Universally Unique Identifier v4
1373     *
1374     * @param  int   $b
1375     * @return UUID, if $b returns binary(16)
1376     */
1377    public function UUID($b=null)
1378    {
1379        if ($this->debug) $this->debug->log(__METHOD__);
1380        if (function_exists('uuid_create')) {
1381            $uuid = uuid_create();
1382        } else {
1383            $uuid = sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
1384                mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff),
1385                mt_rand(0, 0x0fff) | 0x4000,
1386                mt_rand(0, 0x3fff) | 0x8000,
1387                mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff)
1388            );
1389        }
1390
1391        return $b ? pack('H*', str_replace('-', '', $uuid)) : $uuid;
1392    }
1393
1394    /**
1395     * X execute/call custom methods
1396     *
1397     * @return mysqli object
1398     */
1399    public function X()
1400    {
1401        if ($this->debug) $this->debug->log(__METHOD__);
1402        !$this->isConnected() && $this->connect();
1403
1404        return $this->DB;
1405    }
1406
1407    /**
1408     * magic method for Pget, Cacheget, and CachePge(all, row, col, one, assoc)
1409     */
1410    public function __call($name, $args)
1411    {
1412        $n = strtolower($name);
1413
1414        $method = function ($subject) {
1415            ($m = preg_match('/^(pget|cacheget|cachepget)/i', $subject, $matches)) && $m = $matches[0];
1416
1417            return $m;
1418        };
1419
1420        $get = function ($m) use ($n) {
1421            $method = explode($m, $n) + array(null, null);
1422
1423            return in_array($method[1], array('all', 'row', 'col', 'one', 'assoc')) ? $method[1] : false;
1424        };
1425
1426        switch ($method($n)) {
1427        case 'pget':
1428            if ($func = $get('pget')) {
1429                if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__);
1430                return call_user_func_array(array($this, 'PExecute'), $args) ? $this->_pFetch($func) : false;
1431            }
1432            break;
1433
1434        case 'cacheget':
1435            if ($func = $get('cacheget')) {
1436                if ($this->debug) $this->debug->log('Cache', __METHOD__);
1437                array_unshift($args, $func);
1438
1439                return call_user_func_array(array($this, '_Cache'), $args);
1440            }
1441            break;
1442
1443        case 'cachepget':
1444            if ($func = $get('cachepget')) {
1445                if ($this->debug) $this->debug->log('CacheP', __METHOD__);
1446                array_unshift($args, $func);
1447
1448                return call_user_func_array(array($this, '_CacheP'), $args);
1449            }
1450            break;
1451        }
1452        throw new \Exception("DALMP DB method ({$name}) does not exist", 0);
1453    }
1454
1455    /**
1456     * usage: echo $db;
1457     *
1458     * @return database stats
1459     */
1460    public function __toString()
1461    {
1462        if ($this->isConnected()) {
1463            $status = 'DALMP :: ';
1464            $status .= 'Character set: ' . $this->DB->character_set_name();
1465            $status .= ', ' . $this->DB->host_info;
1466            $status .= ', Server version: ' . $this->getServerVersion();
1467            $status .= ', Client version: ' . $this->getClientVersion();
1468            $status .= ', System status: ' . $this->DB->stat();
1469        } else {
1470            $status = 'no connections available';
1471        }
1472
1473        return $status;
1474    }
1475
1476    /**
1477     * destructor
1478     */
1479    public function __destruct()
1480    {
1481        if ($this->debug) $this->debug->getLog();
1482        return $this->closeConnection();
1483    }
1484
1485}
1486