1: <?php
  2: 
  3: /**
  4:  * This file contains the MySQLi database driver class.
  5:  *
  6:  * @package Core
  7:  * @subpackage Database
  8:  * @author Dominik Ziegler
  9:  * @copyright four for business AG <www.4fb.de>
 10:  * @license http://www.contenido.org/license/LIZENZ.txt
 11:  * @link http://www.4fb.de
 12:  * @link http://www.contenido.org
 13:  */
 14: 
 15: defined('CON_FRAMEWORK') || die('Illegal call: Missing framework initialization - request aborted.');
 16: 
 17: /**
 18:  * This class contains functions for database interaction based on MySQLi in
 19:  * CONTENIDO.
 20:  *
 21:  * Configurable via global $cfg['db']['connection'] configuration as follows:
 22:  * <pre>
 23:  * - host (string) Hostname or ip
 24:  * - database (string) Database name
 25:  * - user (string) User name
 26:  * - password (string) User password
 27:  * - options (array) Optional, MySQLi options array
 28:  * - socket (int) Optional, socket
 29:  * - port (int) Optional, port
 30:  * - flags (int) Optional, flags
 31:  * - charset (string) Optional, connection charset
 32:  * see http://www.php.net/manual/en/mysqli.real-connect.php
 33:  * </pre>
 34:  *
 35:  * @package Core
 36:  * @subpackage Database
 37:  */
 38: class cDbDriverMysqli extends cDbDriverAbstract {
 39: 
 40:     /**
 41:      * List of data types.
 42:      * @var array
 43:      */
 44:     protected $_dataTypes = array(
 45:         0 => 'decimal',
 46:         1 => 'tinyint',
 47:         2 => 'smallint',
 48:         3 => 'int',
 49:         4 => 'float',
 50:         5 => 'double',
 51:         7 => 'timestamp',
 52:         8 => 'bigint',
 53:         9 => 'mediumint',
 54:         10 => 'date',
 55:         11 => 'time',
 56:         12 => 'datetime',
 57:         13 => 'year',
 58:         252 => 'blob', // text, blob, tinyblob,mediumblob, etc...
 59:         253 => 'string', // varchar and char
 60:         254 => 'enum'
 61:     );
 62: 
 63:     /**
 64:      * Abstract method for checking database driver base functions.
 65:      * If this check fails, the database connection will not be established.
 66:      *
 67:      * @see cDbDriverAbstract::check()
 68:      * @return bool
 69:      */
 70:     public function check() {
 71:         return extension_loaded('mysqli');
 72:     }
 73: 
 74:     /**
 75:      * Connects to the database.
 76:      *
 77:      * @see cDbDriverAbstract::connect()
 78:      * @return object|resource|int|NULL
 79:      *         value depends on used driver and is NULL in case of an error.
 80:      */
 81:     public function connect() {
 82:         $dbHandler = @mysqli_init();
 83:         if (!$dbHandler || $dbHandler->connect_error != "" || $dbHandler->error != "") {
 84:             $this->_handler->halt('Can not initialize database connection.');
 85:             return NULL;
 86:         }
 87: 
 88:         if (isset($this->_dbCfg['connection'])) {
 89:             $connectConfig = $this->_dbCfg['connection'];
 90:         }
 91:         if (empty($connectConfig) || !isset($connectConfig['host']) || !isset($connectConfig['user']) || !isset($connectConfig['password'])) {
 92:             $this->_handler->halt('Database connection settings incomplete');
 93:             return NULL;
 94:         }
 95: 
 96:         // set existing option flags
 97:         if (isset($connectConfig['options']) && is_array($connectConfig['options'])) {
 98:             foreach ($connectConfig['options'] as $optKey => $optVal) {
 99:                 mysqli_options($dbHandler, $optKey, $optVal);
100:             }
101:         }
102: 
103:         if (($iPos = strpos($connectConfig['host'], ':')) !== false) {
104:             $hostData = explode(':', $connectConfig['host']);
105:             $connectConfig['host'] = $hostData[0];
106:             if (is_numeric($hostData[1])) {
107:                 $connectConfig['port'] = $hostData[1];
108:             } else {
109:                 $connectConfig['socket'] = $hostData[1];
110:             }
111:         }
112: 
113:         if (!isset($connectConfig['port'])) {
114:             $connectConfig['port'] = NULL;
115:         }
116:         if (!isset($connectConfig['socket'])) {
117:             $connectConfig['socket'] = NULL;
118:         }
119: 
120:         if (!isset($connectConfig['flags'])) {
121:             $connectConfig['flags'] = NULL;
122:         }
123:         if (!isset($connectConfig['database'])) {
124:             $connectConfig['database'] = NULL;
125:         }
126: 
127:         $res = mysqli_real_connect($dbHandler, $connectConfig['host'], $connectConfig['user'], $connectConfig['password'], $connectConfig['database'], $connectConfig['port'], $connectConfig['socket'], $connectConfig['flags']);
128: 
129:         // check if connection could be established
130:         if (false === $res) {
131:             $this->_handler->halt('MySQLi _connect() Error connecting to database ' . $connectConfig['database']);
132:             return NULL;
133:         }
134: 
135:         if ($res && $dbHandler && $connectConfig['database']) {
136:             if (!@mysqli_select_db($dbHandler, $connectConfig['database'])) {
137:                 $this->_handler->halt('MySQLi _connect() Cannot use database ' . $connectConfig['database']);
138:                 return NULL;
139:             } else {
140:                 // set connection charset
141:                 if (isset($connectConfig['charset']) && $connectConfig['charset'] != '') {
142:                     if (!@mysqli_set_charset($dbHandler, $connectConfig['charset'])) {
143:                         $this->_handler->halt('Could not set database charset to ' . $connectConfig['charset']);
144:                         return NULL;
145:                     }
146:                 }
147:             }
148:         }
149: 
150:         return $dbHandler;
151:     }
152: 
153:     /**
154:      * Builds a insert query.
155:      * String values in passed fields parameter will be escaped automatically.
156:      *
157:      * @see cDbDriverAbstract::buildInsert()
158:      * @param string $tableName
159:      *         The table name
160:      * @param array $fields
161:      *         Associative array of fields to insert
162:      * @return string
163:      *         The INSERT SQL query
164:      */
165:     public function buildInsert($tableName, array $fields) {
166:         $fieldList = '';
167:         $valueList = '';
168: 
169:         foreach ($fields as $field => $value) {
170:             $fieldList .= '`' . $field . '`, ';
171:             if (is_int($value)) {
172:                 $valueList .= $value . ', ';
173:             } else {
174:                 $valueList .= "'" . $this->escape($value) . "', ";
175:             }
176:         }
177: 
178:         $fieldList = substr($fieldList, 0, -2);
179:         $valueList = substr($valueList, 0, -2);
180:         return sprintf('INSERT INTO `%s` (%s) VALUES (%s)', $tableName, $fieldList, $valueList);
181:     }
182: 
183:     /**
184:      * Builds a update query. String values in passed fields and whereClauses
185:      * parameter will be escaped automatically.
186:      *
187:      * @see cDbDriverAbstract::buildUpdate()
188:      * @param string $tableName
189:      *         The table name
190:      * @param array $fields
191:      *         Assoziative array of fields to update
192:      * @param array $whereClauses
193:      *         Assoziative array of field in where clause.
194:      *         Multiple entries will be concatenated with AND.
195:      * @return string
196:      *         The UPDATE query
197:      */
198:     public function buildUpdate($tableName, array $fields, array $whereClauses) {
199:         $updateList = '';
200:         $whereList = '';
201: 
202:         foreach ($fields as $field => $value) {
203:             $updateList .= '`' . $field . '`=';
204:             if (is_int($value)) {
205:                 $updateList .= $value . ', ';
206:             } else {
207:                 $updateList .= "'" . $this->escape($value) . "', ";
208:             }
209:         }
210: 
211:         foreach ($whereClauses as $field => $value) {
212:             $whereList .= '`' . $field . '`=';
213:             if (is_int($value)) {
214:                 $whereList .= $value . ' AND ';
215:             } else {
216:                 $whereList .= "'" . $this->escape($value) . "' AND ";
217:             }
218:         }
219: 
220:         $updateList = substr($updateList, 0, -2);
221:         $whereList = substr($whereList, 0, -5);
222: 
223:         return sprintf('UPDATE `%s` SET %s WHERE %s', $tableName, $updateList, $whereList);
224:     }
225: 
226:     /**
227:      * Executes the query.
228:      *
229:      * @see cDbDriverAbstract::query()
230:      * @param string $query
231:      *         The query to execute
232:      */
233:     public function query($query) {
234:         $linkId = $this->_handler->getLinkId();
235:         $queryId = mysqli_query($linkId, $query);
236: 
237:         $this->_handler->setQueryId($queryId);
238:         $this->_handler->setRow(0);
239:         $this->_handler->setErrorNumber($this->getErrorNumber());
240:         $this->_handler->setErrorMessage($this->getErrorMessage());
241:     }
242: 
243:     /**
244:      * Moves the result to the next record, if exists and returns the status of
245:      * the movement
246:      *
247:      * @see cDbDriverAbstract::nextRecord()
248:      * @return int
249:      *         Flag about move status 1 on success or 0
250:      */
251:     public function nextRecord() {
252:         $queryId = $this->_handler->getQueryId();
253:         $record = mysqli_fetch_array($queryId, MYSQLI_BOTH);
254: 
255:         $this->_handler->setRecord($record);
256:         $this->_handler->incrementRow();
257:         $this->_handler->setErrorNumber($this->getErrorNumber());
258:         $this->_handler->setErrorMessage($this->getErrorMessage());
259: 
260:         return is_array($record);
261:     }
262: 
263:     /**
264:      * This method returns the current result set as object or NULL if no result
265:      * set is left. If optional param $className is set, the result object is an
266:      * instance of class $className.
267:      *
268:      * @see cDbDriverAbstract::getResultObject()
269:      * @param string $className [optional]
270:      * @return Ambigous <NULL, object, false>
271:      */
272:     public function getResultObject($className = NULL) {
273:         $result = NULL;
274:         $queryId = $this->_handler->getQueryId();
275: 
276:         if ($queryId) {
277:             if ($className == NULL) {
278:                 $result = mysqli_fetch_object($queryId);
279:             } else {
280:                 $result = mysqli_fetch_object($queryId, $className);
281:             }
282:         }
283: 
284:         return $result;
285:     }
286: 
287:     /**
288:      * Returns number of affected rows from last executed query (update, delete)
289:      *
290:      * @see cDbDriverAbstract::affectedRows()
291:      * @return int
292:      *         Number of affected rows
293:      */
294:     public function affectedRows() {
295:         $linkId = $this->_handler->getLinkId();
296:         return ($linkId) ? mysqli_affected_rows($linkId) : 0;
297:     }
298: 
299:     /**
300:      * Returns the number of rows from last executed select query.
301:      *
302:      * @see cDbDriverAbstract::numRows()
303:      * @return int
304:      *         The number of rows from last select query result
305:      */
306:     public function numRows() {
307:         $queryId = $this->_handler->getQueryId();
308:         return ($queryId) ? mysqli_num_rows($queryId) : 0;
309:     }
310: 
311:     /**
312:      * Returns the number of fields (columns) from current record set
313:      *
314:      * @see cDbDriverAbstract::numFields()
315:      * @return int
316:      *         Number of fields
317:      */
318:     public function numFields() {
319:         $queryId = $this->_handler->getQueryId();
320:         return ($queryId) ? mysqli_num_fields($queryId) : 0;
321:     }
322: 
323:     /**
324:      * Discard the query result
325:      *
326:      * @todo check if $this should be returned
327:      * @return void|cDbDriverMysqli
328:      *         If aggregated handler has no query id, this object is returned,
329:      *         otherwise void.
330:      * @see cDbDriverAbstract::free()
331:      */
332:     public function free() {
333:         if (!is_object($this->_handler->getQueryId())) {
334:             return $this;
335:         }
336: 
337:         mysqli_free_result($this->_handler->getQueryId());
338:         $this->_handler->setQueryId(0);
339:     }
340: 
341:     /**
342:      * Escape string for using in SQL-Statement.
343:      *
344:      * @see cDbDriverAbstract::escape()
345:      * @param string $string
346:      *         The string to escape
347:      * @return string
348:      *         Escaped string
349:      */
350:     public function escape($string) {
351:         $linkId = $this->_handler->getLinkId();
352:         return mysqli_real_escape_string($linkId, $string);
353:     }
354: 
355:     /**
356:      * Moves the cursor (position inside current result sets).
357:      *
358:      * @see cDbDriverAbstract::seek()
359:      * @param int $pos [optional]
360:      *         The positon to move to inside the current result set
361:      * @return int
362:      */
363:     public function seek($pos = 0) {
364:         $queryId = $this->_handler->getQueryId();
365: 
366:         $status = mysqli_data_seek($queryId, $pos);
367:         if ($status) {
368:             $this->_handler->setRow($pos);
369:         } else {
370:             return 0;
371:         }
372: 
373:         return 1;
374:     }
375: 
376:     /**
377:      * Parses the table structure and generates metadata from it.
378:      *
379:      * Due to compatibility problems with table we changed the behavior
380:      * of metadata(). Depending on $full, metadata returns the following values:
381:      *
382:      * - full is false (default):
383:      * $result[]:
384:      * [0]["table"] table name
385:      * [0]["name"] field name
386:      * [0]["type"] field type
387:      * [0]["len"] field length
388:      * [0]["flags"] field flags
389:      *
390:      * - full is true
391:      * $result[]:
392:      * ["num_fields"] number of metadata records
393:      * [0]["table"] table name
394:      * [0]["name"] field name
395:      * [0]["type"] field type
396:      * [0]["len"] field length
397:      * [0]["flags"] field flags
398:      * ["meta"][field name] index of field named "field name"
399:      * This last one could be used if you have a field name, but no index.
400:      * Test: if (isset($result['meta']['myfield'])) { ...
401:      *
402:      * @see cDbDriverAbstract::getMetaData()
403:      * @param string $tableName
404:      *         The table to get metadata or empty string to retrieve metadata
405:      *         of all tables.
406:      * @param bool $full [optional]
407:      *         Flag to load full metadata.
408:      * @return array
409:      *         Depends on used database and on parameter $full
410:      */
411:     public function getMetaData($tableName, $full = false) {
412:         $res = array();
413: 
414:         $this->query(sprintf('SELECT * FROM `%s` LIMIT 1', $tableName));
415: 
416:         $id = $this->_handler->getQueryId();
417:         if (!$id) {
418:             $this->_handler->halt('Metadata query failed.');
419:             return false;
420:         }
421: 
422:         // made this IF due to performance (one if is faster than $count if's)
423:         $count = mysqli_num_fields($id);
424:         for ($i = 0; $i < $count; $i++) {
425:             $finfo = mysqli_fetch_field($id);
426:             $res[$i]['table'] = $finfo->table;
427:             $res[$i]['name'] = $finfo->name;
428:             $res[$i]['type'] = $this->_dataTypes[$finfo->type];
429:             $res[$i]['len'] = $finfo->max_length;
430:             $res[$i]['flags'] = $finfo->flags;
431:             if ($full) {
432:                 $res['meta'][$res[$i]['name']] = $i;
433:             }
434:         }
435:         if ($full) {
436:             $res['num_fields'] = $count;
437:         }
438: 
439:         $this->free();
440: 
441:         return $res;
442:     }
443: 
444:     /**
445:      * Fetches all table names.
446:      *
447:      * @see cDbDriverAbstract::getTableNames()
448:      * @return array
449:      */
450:     public function getTableNames() {
451:         $return = array();
452:         if ($this->query('SHOW TABLES')) {
453:             while ($this->nextRecord()) {
454:                 $record = $this->getRecord();
455:                 $return[] = array(
456:                     'table_name' => $record[0],
457:                     'tablespace_name' => $this->_dbCfg['connection']['database'],
458:                     'database' => $this->_dbCfg['connection']['database']
459:                 );
460:             }
461: 
462:             $this->free();
463:         }
464:         return $return;
465:     }
466: 
467:     /**
468:      * Fetches server information.
469:      *
470:      * @see cDbDriverAbstract::getServerInfo()
471:      * @return array
472:      */
473:     public function getServerInfo() {
474:         $linkId = $this->_handler->getLinkId();
475: 
476:         if ($linkId) {
477:             $arr = array();
478:             $arr['description'] = mysqli_get_server_info($linkId);
479:             return $arr;
480:         }
481: 
482:         return NULL;
483:     }
484: 
485:     /**
486:      * Returns error code of last occured error by using databases interface.
487:      *
488:      * @see cDbDriverAbstract::getErrorNumber()
489:      * @return int
490:      */
491:     public function getErrorNumber() {
492:         $linkId = $this->_handler->getLinkId();
493: 
494:         if ($linkId) {
495:             return @mysqli_errno($linkId);
496:         } else {
497:             return @mysqli_connect_errno();
498:         }
499:     }
500: 
501:     /**
502:      * Returns error message of last occured error by using databases interface.
503:      *
504:      * @see cDbDriverAbstract::getErrorMessage()
505:      * @return string
506:      */
507:     public function getErrorMessage() {
508:         $linkId = $this->_handler->getLinkId();
509: 
510:         if ($linkId) {
511:             return @mysqli_error($linkId);
512:         } else {
513:             return @mysqli_connect_error();
514:         }
515:     }
516: 
517:     /**
518:      * Closes the connection and frees the query id.
519:      *
520:      * @see cDbDriverAbstract::disconnect()
521:      */
522:     public function disconnect() {
523:         mysqli_close($this->_handler->getLinkId());
524:     }
525: 
526: }
527: