1: <?php
2:
3: /**
4: * This file contains the MySQL 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 MySQL in CONTENIDO.
19: *
20: * Configurable via global $cfg['db']['connection'] configuration as follows:
21: * <pre>
22: * - host (string) Hostname or ip (with port, e.g. "example.com:3307")
23: * - database (string) Database name
24: * - user (string) User name
25: * - password (string) User password
26: * - charset (string) Optional, connection charset
27: * see http://php.net/manual/en/function.mysql-connect.php
28: * </pre>
29: *
30: * @package Core
31: * @subpackage Database
32: */
33: class cDbDriverMysql extends cDbDriverAbstract {
34:
35: /**
36: * Abstract method for checking database driver base functions.
37: * If this check fails, the database connection will not be established.
38: *
39: * @see cDbDriverAbstract::check()
40: * @return bool
41: */
42: public function check() {
43: return function_exists("mysql_connect");
44: }
45:
46: /**
47: * Connects to the database.
48: *
49: * @see cDbDriverAbstract::connect()
50: * @return object|resource|int|NULL
51: * value depends on used driver and is NULL in case of an error
52: */
53: public function connect() {
54: if (isset($this->_dbCfg['connection'])) {
55: $connectConfig = $this->_dbCfg['connection'];
56: }
57: if (empty($connectConfig) || !isset($connectConfig['host']) || !isset($connectConfig['user']) || !isset($connectConfig['password'])) {
58: $this->_handler->halt('Database connection settings incomplete');
59:
60: return NULL;
61: }
62:
63: // establish connection, select database
64: $dbHandler = mysql_connect($connectConfig['host'], $connectConfig['user'], $connectConfig['password']);
65: if (!$dbHandler || !is_resource($dbHandler)) {
66: $this->_handler->halt('Error during establishing a connection with database.');
67:
68: return NULL;
69: }
70:
71: if (isset($connectConfig['database'])) {
72: if (!mysql_select_db($connectConfig['database'], $dbHandler)) {
73: $this->_handler->halt('Can not use database ' . $connectConfig['database']);
74:
75: return NULL;
76: } else {
77: //set connection charset
78: if (isset($connectConfig['charset']) && $connectConfig['charset'] != '') {
79: if (!mysql_set_charset($connectConfig['charset'], $dbHandler)) {
80: $this->_handler->halt('Could not set database charset to ' . $connectConfig['charset']);
81:
82: return NULL;
83: }
84: }
85: }
86: }
87:
88: return $dbHandler;
89: }
90:
91: /**
92: * Builds a insert query.
93: * String values in passed fields parameter will be escaped automatically.
94: *
95: * @see cDbDriverAbstract::buildInsert()
96: * @param string $tableName
97: * The table name
98: * @param array $fields
99: * Associative array of fields to insert
100: * @return string
101: * The INSERT SQL query
102: */
103: public function buildInsert($tableName, array $fields) {
104: $fieldList = '';
105: $valueList = '';
106:
107: foreach ($fields as $field => $value) {
108: $fieldList .= '`' . $field . '`, ';
109: if (is_int($value)) {
110: $valueList .= $value . ', ';
111: } else {
112: $valueList .= "'" . $this->escape($value) . "', ";
113: }
114: }
115:
116: $fieldList = substr($fieldList, 0, -2);
117: $valueList = substr($valueList, 0, -2);
118:
119: return sprintf('INSERT INTO `%s` (%s) VALUES (%s)', $tableName, $fieldList, $valueList);
120: }
121:
122: /**
123: * Builds a update query. String values in passed fields and whereClauses
124: * parameter will be escaped automatically.
125: *
126: * @see cDbDriverAbstract::buildUpdate()
127: * @param string $tableName
128: * The table name
129: * @param array $fields
130: * Assoziative array of fields to update
131: * @param array $whereClauses
132: * Assoziative array of field in where clause.
133: * Multiple entries will be concatenated with AND.
134: * @return string
135: * The UPDATE query
136: */
137: public function buildUpdate($tableName, array $fields, array $whereClauses) {
138: $updateList = '';
139: $whereList = '';
140:
141: foreach ($fields as $field => $value) {
142: $updateList .= '`' . $field . '`=';
143: if (is_int($value)) {
144: $updateList .= $value . ', ';
145: } else {
146: $updateList .= "'" . $this->escape($value) . "', ";
147: }
148: }
149:
150: foreach ($whereClauses as $field => $value) {
151: $whereList .= '`' . $field . '`=';
152: if (is_int($value)) {
153: $whereList .= $value . ' AND ';
154: } else {
155: $whereList .= "'" . $this->escape($value) . "' AND ";
156: }
157: }
158:
159: $updateList = substr($updateList, 0, -2);
160: $whereList = substr($whereList, 0, -5);
161:
162: return sprintf('UPDATE `%s` SET %s WHERE %s', $tableName, $updateList, $whereList);
163: }
164:
165: /**
166: * Executes the query.
167: *
168: * @see cDbDriverAbstract::query()
169: * @param string $statement
170: * The query to execute
171: */
172: public function query($query) {
173: $linkId = $this->_handler->getLinkId();
174: $queryId = @mysql_query($query, $linkId);
175:
176: $this->_handler->setQueryId($queryId);
177: $this->_handler->setRow(0);
178: $this->_handler->setErrorNumber($this->getErrorNumber());
179: $this->_handler->setErrorMessage($this->getErrorMessage());
180: }
181:
182: /**
183: * Moves the result to the next record, if exists and returns the status of
184: * the movement
185: *
186: * @see cDbDriverAbstract::nextRecord()
187: * @return int
188: * Flag about move status 1 on success or 0
189: */
190: public function nextRecord() {
191: $queryId = $this->_handler->getQueryId();
192: $record = @mysql_fetch_array($queryId);
193:
194: $this->_handler->setRecord($record);
195: $this->_handler->incrementRow();
196: $this->_handler->setErrorNumber($this->getErrorNumber());
197: $this->_handler->setErrorMessage($this->getErrorMessage());
198:
199: return is_array($record);
200: }
201:
202: /**
203: * This method returns the current result set as object or NULL if no result
204: * set is left. If optional param $className is set, the result object is an
205: * instance of class $className.
206: *
207: * @see cDbDriverAbstract::getResultObject()
208: * @param string $className [optional]
209: * @return Ambigous <NULL, object, false>
210: */
211: public function getResultObject($className = NULL) {
212: $result = NULL;
213: $queryId = $this->_handler->getQueryId();
214:
215: if (is_resource($queryId)) {
216: if ($className == NULL) {
217: $result = mysql_fetch_object($queryId);
218: } else {
219: $result = mysql_fetch_object($queryId, $className);
220: }
221: }
222:
223: return $result;
224: }
225:
226: /**
227: * Returns number of affected rows from last executed query (update, delete)
228: *
229: * @see cDbDriverAbstract::affectedRows()
230: * @return int
231: * Number of affected rows
232: */
233: public function affectedRows() {
234: $linkId = $this->_handler->getLinkId();
235:
236: return ($linkId) ? mysql_affected_rows($linkId) : 0;
237: }
238:
239: /**
240: * Returns the number of rows from last executed select query.
241: *
242: * @see cDbDriverAbstract::numRows()
243: * @return int
244: * The number of rows from last select query result
245: */
246: public function numRows() {
247: $queryId = $this->_handler->getQueryId();
248:
249: return ($queryId) ? mysql_num_rows($queryId) : 0;
250: }
251:
252: /**
253: * Returns the number of fields (columns) from current record set
254: *
255: * @see cDbDriverAbstract::numFields()
256: * @return int
257: * Number of fields
258: */
259: public function numFields() {
260: $queryId = $this->_handler->getQueryId();
261:
262: return ($queryId) ? mysql_num_fields($queryId) : 0;
263: }
264:
265: /**
266: * Discard the query result
267: *
268: * @see cDbDriverAbstract::free()
269: */
270: public function free() {
271: @mysql_free_result($this->_handler->getQueryId());
272: $this->_handler->setQueryId(0);
273: }
274:
275: /**
276: * Escape string for using in SQL-Statement.
277: *
278: * @see cDbDriverAbstract::escape()
279: * @param string $string
280: * The string to escape
281: * @return string
282: * Escaped string
283: */
284: public function escape($string) {
285: $linkId = $this->_handler->getLinkId();
286:
287: return mysql_real_escape_string($string, $linkId);
288: }
289:
290: /**
291: * Moves the cursor (position inside current result sets).
292: *
293: * @see cDbDriverAbstract::seek()
294: * @param int $pos [optional]
295: * The positon to move to inside the current result set
296: * @return int
297: */
298: public function seek($pos = 0) {
299: $queryId = $this->_handler->getQueryId();
300:
301: $status = @mysql_data_seek($queryId, $pos);
302: if ($status) {
303: $this->_handler->setRow($pos);
304: } else {
305: return 0;
306: }
307:
308: return 1;
309: }
310:
311: /**
312: * Parses the table structure and generates metadata from it.
313: *
314: * Due to compatibility problems with table we changed the behavior
315: * of metadata(). Depending on $full, metadata returns the following values:
316: *
317: * - full is false (default):
318: * $result[]:
319: * [0]["table"] table name
320: * [0]["name"] field name
321: * [0]["type"] field type
322: * [0]["len"] field length
323: * [0]["flags"] field flags
324: *
325: * - full is true
326: * $result[]:
327: * ["num_fields"] number of metadata records
328: * [0]["table"] table name
329: * [0]["name"] field name
330: * [0]["type"] field type
331: * [0]["len"] field length
332: * [0]["flags"] field flags
333: * ["meta"][field name] index of field named "field name"
334: * This last one could be used if you have a field name, but no index.
335: * Test: if (isset($result['meta']['myfield'])) { ...
336: *
337: * @see cDbDriverAbstract::getMetaData()
338: * @param string $tableName
339: * The table to get metadata or empty string to retrieve metadata
340: * of all tables.
341: * @param bool $full [optional]
342: * Flag to load full metadata.
343: * @return array
344: * Depends on used database and on parameter $full
345: */
346: public function getMetaData($tableName, $full = false) {
347: $res = array();
348:
349: $this->query(sprintf('SELECT * FROM `%s` LIMIT 1', $tableName));
350: $id = $this->_handler->getQueryId();
351: if (!$id) {
352: $this->_handler->halt('Metadata query failed.');
353:
354: return false;
355: }
356:
357: // made this IF due to performance (one if is faster than $count if's)
358: $count = @mysql_num_fields($id);
359: for ($i = 0; $i < $count; $i++) {
360: $res[$i]['table'] = @mysql_field_table($id, $i);
361: $res[$i]['name'] = @mysql_field_name($id, $i);
362: $res[$i]['type'] = @mysql_field_type($id, $i);
363: $res[$i]['len'] = @mysql_field_len($id, $i);
364: $res[$i]['flags'] = @mysql_field_flags($id, $i);
365: if ($full) {
366: $res['meta'][$res[$i]['name']] = $i;
367: }
368: }
369: if ($full) {
370: $res['num_fields'] = $count;
371: }
372:
373: $this->free();
374:
375: return $res;
376: }
377:
378: /**
379: * Fetches all table names.
380: *
381: * @see cDbDriverAbstract::getTableNames()
382: * @return array
383: */
384: public function getTableNames() {
385: $return = array();
386:
387: if ($this->query('SHOW TABLES')) {
388: while ($this->nextRecord()) {
389: $record = $this->getRecord();
390: $return[] = array(
391: 'table_name' => $record[0], 'tablespace_name' => $this->_dbCfg['connection']['database'], 'database' => $this->_dbCfg['connection']['database'],
392: );
393: }
394:
395: $this->free();
396: }
397:
398: return $return;
399: }
400:
401: /**
402: * Fetches server information.
403: *
404: * @see cDbDriverAbstract::getServerInfo()
405: * @return array
406: */
407: public function getServerInfo() {
408: $linkId = $this->_handler->getLinkId();
409:
410: if (is_resource($linkId)) {
411: $arr = array();
412: $arr['description'] = mysql_get_server_info($linkId);
413:
414: return $arr;
415: }
416:
417: return NULL;
418: }
419:
420: /**
421: * Returns error code of last occured error by using databases interface.
422: *
423: * @see cDbDriverAbstract::getErrorNumber()
424: * @return int
425: */
426: public function getErrorNumber() {
427: $linkId = $this->_handler->getLinkId();
428:
429: if (is_resource($linkId)) {
430: return mysql_errno($linkId);
431: } else {
432: return mysql_errno();
433: }
434: }
435:
436: /**
437: * Returns error message of last occured error by using databases interface.
438: *
439: * @see cDbDriverAbstract::getErrorMessage()
440: * @return string
441: */
442: public function getErrorMessage() {
443: $linkId = $this->_handler->getLinkId();
444:
445: if (is_resource($linkId)) {
446: return mysql_error($linkId);
447: } else {
448: return mysql_error();
449: }
450: }
451:
452: /**
453: * Closes the connection and frees the query id.
454: *
455: * @see cDbDriverAbstract::disconnect()
456: */
457: public function disconnect() {
458: mysql_close($this->_handler->getLinkId());
459: }
460:
461: }
462: