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