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