Project

General

Profile

1 4 ryan
<?php
2 1362 Luisehahne
/**
3
 *
4
 * @category        framework
5
 * @package         database
6
 * @author          WebsiteBaker Project
7
 * @copyright       2004-2009, Ryan Djurovich
8
 * @copyright       2009-2011, Website Baker Org. e.V.
9
 * @link            http://www.websitebaker2.org/
10
 * @license         http://www.gnu.org/licenses/gpl.html
11
 * @platform        WebsiteBaker 2.8.x
12 1374 Luisehahne
 * @requirements    PHP 5.2.2 and higher
13 1362 Luisehahne
 * @version         $Id$
14 1457 Luisehahne
 * @filesource      $HeadURL$
15
 * @lastmodified    $Date$
16 1362 Luisehahne
 *
17
 */
18 4 ryan
/*
19
Database class
20
This class will be used to interface between the database
21
and the Website Baker code
22
*/
23 1496 DarkViper
/* -------------------------------------------------------- */
24
// Must include code to stop this file being accessed directly
25 1499 DarkViper
if(!defined('WB_PATH')) {
26
	require_once(dirname(__FILE__).'/globalExceptionHandler.php');
27
	throw new IllegalFileException();
28
}
29 1496 DarkViper
/* -------------------------------------------------------- */
30 4 ryan
define('DATABASE_CLASS_LOADED', true);
31
32 1362 Luisehahne
33 1686 darkviper
class WbDatabase {
34 1763 Luisehahne
35 1686 darkviper
	private static $_oInstances = array();
36 1680 darkviper
37
	private $_db_handle = null; // readonly from outside
38
	private $_scheme    = 'mysql';
39
	private $_hostname  = 'localhost';
40
	private $_username  = '';
41
	private $_password  = '';
42 1682 darkviper
	private $_hostport  = '3306';
43 1680 darkviper
	private $_db_name   = '';
44 1362 Luisehahne
	private $connected  = false;
45
	private $error      = '';
46
	private $error_type = '';
47 1662 darkviper
	private $iQueryCount= 0;
48 1362 Luisehahne
49 1683 darkviper
/* prevent from public instancing */
50 1686 darkviper
	protected function  __construct() {}
51 1683 darkviper
/* prevent from cloning */
52
	private function __clone() {}
53
/**
54 1686 darkviper
 * get a valid instance of this class
55
 * @param string $sIdentifier selector for several different instances
56
 * @return object
57
 */
58
	public static function getInstance($sIdentifier = 'core') {
59
		if( !isset(self::$_oInstances[$sIdentifier])) {
60
            $c = __CLASS__;
61
            self::$_oInstances[$sIdentifier] = new $c;
62
		}
63
		return self::$_oInstances[$sIdentifier];
64
	}
65
/**
66
 * disconnect and kills an existing instance
67
 * @param string $sIdentifier
68
 */
69
	public static function killInstance($sIdentifier) {
70
		if($sIdentifier != 'core') {
71
			if( isset(self::$_oInstances[$sIdentifier])) {
72
				self::$_oInstances[$sIdentifier]->disconnect();
73
				unset(self::$_oInstances[$sIdentifier]);
74
			}
75
		}
76
	}
77
/**
78 1683 darkviper
 * Connect to the database
79
 * @param string $url
80
 * @return bool
81
 *
82
 * Example for SQL-Url:  'mysql://user:password@demo.de[:3306]/datenbank'
83
 */
84
	public function doConnect($url = '') {
85 1680 darkviper
		if($url != '') {
86
			$aIni = parse_url($url);
87
			$this->_scheme   = isset($aIni['scheme']) ? $aIni['scheme'] : 'mysql';
88
			$this->_hostname = isset($aIni['host']) ? $aIni['host'] : '';
89
			$this->_username = isset($aIni['user']) ? $aIni['user'] : '';
90
			$this->_password = isset($aIni['pass']) ? $aIni['pass'] : '';
91
			$this->_hostport = isset($aIni['port']) ? $aIni['port'] : '3306';
92
			$this->_hostport = $this->_hostport == '3306' ? '' : ':'.$this->_hostport;
93
			$this->_db_name  = ltrim(isset($aIni['path']) ? $aIni['path'] : '', '/\\');
94
		}else {
95 1682 darkviper
			throw new RuntimeException('Missing parameter: unable to connect database');
96 1680 darkviper
		}
97
		$this->_db_handle = mysql_connect($this->_hostname.$this->_hostport,
98
		                                  $this->_username,
99
		                                  $this->_password);
100
		if(!$this->_db_handle) {
101
			throw new RuntimeException('unable to connect \''.$this->_scheme.'://'.
102
			                           $this->_hostname.$this->_hostport.'\'');
103 4 ryan
		} else {
104 1680 darkviper
			if(!mysql_select_db($this->_db_name)) {
105
				throw new RuntimeException('unable to select database \''.$this->_db_name.
106
				                           '\' on \''.$this->_scheme.'://'.
107
				                           $this->_hostname.$this->_hostport.'\'');
108 4 ryan
			} else {
109
				$this->connected = true;
110
			}
111
		}
112
		return $this->connected;
113
	}
114 1763 Luisehahne
115 4 ryan
	// Disconnect from the database
116 1686 darkviper
	public function disconnect() {
117 95 stefan
		if($this->connected==true) {
118 1680 darkviper
			mysql_close($this->_db_handle);
119 4 ryan
			return true;
120
		} else {
121
			return false;
122
		}
123
	}
124 1763 Luisehahne
125 4 ryan
	// Run a query
126 1686 darkviper
	public function query($statement) {
127 1662 darkviper
		$this->iQueryCount++;
128 4 ryan
		$mysql = new mysql();
129 1680 darkviper
		$mysql->query($statement, $this->_db_handle);
130
		$this->set_error($mysql->error($this->_db_handle));
131
		if($mysql->error($this->_db_handle)) {
132 4 ryan
			return null;
133
		} else {
134
			return $mysql;
135
		}
136
	}
137 1362 Luisehahne
138 4 ryan
	// Gets the first column of the first row
139 1686 darkviper
	public function get_one( $statement )
140 1362 Luisehahne
	{
141 1662 darkviper
		$this->iQueryCount++;
142 1680 darkviper
		$fetch_row = mysql_fetch_array(mysql_query($statement, $this->_db_handle));
143 4 ryan
		$result = $fetch_row[0];
144 1680 darkviper
		$this->set_error(mysql_error($this->_db_handle));
145
		if(mysql_error($this->_db_handle)) {
146 4 ryan
			return null;
147
		} else {
148
			return $result;
149
		}
150
	}
151 1763 Luisehahne
152 4 ryan
	// Set the DB error
153 1686 darkviper
	public function set_error($message = null) {
154 4 ryan
		global $TABLE_DOES_NOT_EXIST, $TABLE_UNKNOWN;
155
		$this->error = $message;
156
		if(strpos($message, 'no such table')) {
157
			$this->error_type = $TABLE_DOES_NOT_EXIST;
158
		} else {
159
			$this->error_type = $TABLE_UNKNOWN;
160
		}
161
	}
162 1763 Luisehahne
163 4 ryan
	// Return true if there was an error
164 1686 darkviper
	public function is_error() {
165 4 ryan
		return (!empty($this->error)) ? true : false;
166
	}
167 1763 Luisehahne
168 4 ryan
	// Return the error
169 1686 darkviper
	public function get_error() {
170 4 ryan
		return $this->error;
171
	}
172
173 1613 darkviper
/**
174
 * default Getter for some properties
175
 * @param string $sPropertyName
176
 * @return mixed NULL on error or missing property
177 1362 Luisehahne
 */
178 1613 darkviper
	public function __get($sPropertyName)
179 1362 Luisehahne
	{
180 1613 darkviper
		switch ($sPropertyName):
181
			case 'db_handle':
182
			case 'DbHandle':
183 1662 darkviper
			case 'getDbHandle':
184 1680 darkviper
				$retval = $this->_db_handle;
185 1613 darkviper
				break;
186
			case 'db_name':
187
			case 'DbName':
188 1662 darkviper
			case 'getDbName':
189 1680 darkviper
				$retval = $this->_db_name;
190 1613 darkviper
				break;
191 1662 darkviper
			case 'getQueryCount':
192
				$retval = $this->iQueryCount;
193
				break;
194 1613 darkviper
			default:
195
				$retval = null;
196
				break;
197
		endswitch;
198
		return $retval;
199
	} // __get()
200 1362 Luisehahne
201
/*
202
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
203
 * @param string $field_name: name of the field to seek for
204
 * @return bool: true if field exists
205
 */
206
	public function field_exists($table_name, $field_name)
207
	{
208
		$sql = 'DESCRIBE `'.$table_name.'` `'.$field_name.'` ';
209 1680 darkviper
		$query = $this->query($sql, $this->_db_handle);
210 1362 Luisehahne
		return ($query->numRows() != 0);
211
	}
212
213
/*
214
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
215
 * @param string $index_name: name of the index to seek for
216
 * @return bool: true if field exists
217
 */
218
	public function index_exists($table_name, $index_name, $number_fields = 0)
219
	{
220
		$number_fields = intval($number_fields);
221
		$keys = 0;
222
		$sql = 'SHOW INDEX FROM `'.$table_name.'`';
223 1680 darkviper
		if( ($res_keys = $this->query($sql, $this->_db_handle)) )
224 1362 Luisehahne
		{
225
			while(($rec_key = $res_keys->fetchRow()))
226
			{
227
				if( $rec_key['Key_name'] == $index_name )
228
				{
229
					$keys++;
230
				}
231
			}
232
233
		}
234
		if( $number_fields == 0 )
235
		{
236
			return ($keys != $number_fields);
237
		}else
238
		{
239
			return ($keys == $number_fields);
240
		}
241
	}
242 1763 Luisehahne
243 1362 Luisehahne
/*
244
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
245
 * @param string $field_name: name of the field to add
246
 * @param string $description: describes the new field like ( INT NOT NULL DEFAULT '0')
247
 * @return bool: true if successful, otherwise false and error will be set
248
 */
249
	public function field_add($table_name, $field_name, $description)
250
	{
251 1442 Luisehahne
		if( !$this->field_exists($table_name, $field_name) )
252 1362 Luisehahne
		{ // add new field into a table
253
			$sql = 'ALTER TABLE `'.$table_name.'` ADD '.$field_name.' '.$description.' ';
254 1680 darkviper
			$query = $this->query($sql, $this->_db_handle);
255
			$this->set_error(mysql_error($this->_db_handle));
256 1362 Luisehahne
			if( !$this->is_error() )
257
			{
258 1442 Luisehahne
				return ( $this->field_exists($table_name, $field_name) ) ? true : false;
259 1362 Luisehahne
			}
260
		}else
261
		{
262
			$this->set_error('field \''.$field_name.'\' already exists');
263
		}
264
		return false;
265
	}
266
267
/*
268
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
269
 * @param string $field_name: name of the field to add
270
 * @param string $description: describes the new field like ( INT NOT NULL DEFAULT '0')
271
 * @return bool: true if successful, otherwise false and error will be set
272
 */
273
	public function field_modify($table_name, $field_name, $description)
274
	{
275
		$retval = false;
276 1486 DarkViper
		if( $this->field_exists($table_name, $field_name) )
277 1362 Luisehahne
		{ // modify a existing field in a table
278 1486 DarkViper
			$sql  = 'ALTER TABLE `'.$table_name.'` MODIFY `'.$field_name.'` '.$description;
279 1680 darkviper
			$retval = ( $this->query($sql, $this->_db_handle) ? true : false);
280 1486 DarkViper
			$this->set_error(mysql_error());
281 1362 Luisehahne
		}
282 1486 DarkViper
		return $retval;
283 1362 Luisehahne
	}
284
285
/*
286
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
287
 * @param string $field_name: name of the field to remove
288
 * @return bool: true if successful, otherwise false and error will be set
289
 */
290
	public function field_remove($table_name, $field_name)
291
	{
292
		$retval = false;
293 1507 Luisehahne
		if( $this->field_exists($table_name, $field_name) )
294 1362 Luisehahne
		{ // modify a existing field in a table
295
			$sql  = 'ALTER TABLE `'.$table_name.'` DROP `'.$field_name.'`';
296 1680 darkviper
			$retval = ( $this->query($sql, $this->_db_handle) ? true : false );
297 1362 Luisehahne
		}
298
		return $retval;
299
	}
300
301
/*
302
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
303 1763 Luisehahne
 * @param string $index_name: name of the new index (empty string for PRIMARY)
304 1362 Luisehahne
 * @param string $field_list: comma seperated list of fields for this index
305 1763 Luisehahne
 * @param string $index_type: kind of index (PRIMARY, UNIQUE, KEY, FULLTEXT)
306 1362 Luisehahne
 * @return bool: true if successful, otherwise false and error will be set
307
 */
308 1763 Luisehahne
     public function index_add($table_name, $index_name, $field_list, $index_type = 'KEY')
309
     {
310
        $retval = false;
311
        $field_list = str_replace(' ', '', $field_list);
312
        $field_list = explode(',', $field_list);
313
        $number_fields = sizeof($field_list);
314
        $field_list = '`'.implode('`,`', $field_list).'`';
315
        $index_name = $index_type == 'PRIMARY' ? $index_type : $index_name;
316
        if( $this->index_exists($table_name, $index_name, $number_fields) ||
317
            $this->index_exists($table_name, $index_name))
318
        {
319
            $sql  = 'ALTER TABLE `'.$table_name.'` ';
320
            $sql .= 'DROP INDEX `'.$index_name.'`';
321
            if( !$this->query($sql, $this->_db_handle)) { return false; }
322
        }
323
        $sql  = 'ALTER TABLE `'.$table_name.'` ';
324
        $sql .= 'ADD '.$index_type.' ';
325
        $sql .= $index_type == 'PRIMARY' ? 'KEY ' : '`'.$index_name.'` ';
326
        $sql .= '( '.$field_list.' ); ';
327
        if( $this->query($sql, $this->_db_handle)) { $retval = true; }
328
        return $retval;
329
    }
330 1362 Luisehahne
331
/*
332
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
333
 * @param string $field_name: name of the field to remove
334
 * @return bool: true if successful, otherwise false and error will be set
335
 */
336
	public function index_remove($table_name, $index_name)
337
	{
338
		$retval = false;
339
		if( $this->index_exists($table_name, $index_name) )
340
		{ // modify a existing field in a table
341
			$sql  = 'ALTER TABLE `'.$table_name.'` DROP INDEX `'.$index_name.'`';
342 1680 darkviper
			$retval = ( $this->query($sql, $this->_db_handle) ? true : false );
343 1362 Luisehahne
		}
344
		return $retval;
345
	}
346 1763 Luisehahne
347 1586 darkviper
/**
348
 * Import a standard *.sql dump file
349
 * @param string $sSqlDump link to the sql-dumpfile
350
 * @param string $sTablePrefix
351 1591 darkviper
 * @param bool $bPreserve set to true will ignore all DROP TABLE statements
352 1586 darkviper
 * @param string $sTblEngine
353
 * @param string $sTblCollation
354
 * @return boolean true if import successful
355
 */
356
	public function SqlImport($sSqlDump,
357
	                          $sTablePrefix = '',
358 1591 darkviper
	                          $bPreserve = true,
359 1586 darkviper
	                          $sTblEngine = 'ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci',
360 1591 darkviper
	                          $sTblCollation = ' collate utf8_unicode_ci')
361 1586 darkviper
	{
362
		$retval = true;
363
		$this->error = '';
364
		$aSearch  = array('{TABLE_PREFIX}','{TABLE_ENGINE}', '{TABLE_COLLATION}');
365
		$aReplace = array($sTablePrefix, $sTblEngine, $sTblCollation);
366
		$sql = '';
367
		$aSql = file($sSqlDump);
368
		while ( sizeof($aSql) > 0 ) {
369
			$sSqlLine = trim(array_shift($aSql));
370 1591 darkviper
			if (!preg_match('/^[-\/]+.*/', $sSqlLine)) {
371 1592 darkviper
				$sql = $sql.' '.$sSqlLine;
372 1586 darkviper
				if ((substr($sql,-1,1) == ';')) {
373
					$sql = trim(str_replace( $aSearch, $aReplace, $sql));
374
					if (!($bPreserve && preg_match('/^\s*DROP TABLE IF EXISTS/siU', $sql))) {
375 1680 darkviper
						if(!mysql_query($sql, $this->_db_handle)) {
376 1586 darkviper
							$retval = false;
377 1680 darkviper
							$this->error = mysql_error($this->_db_handle);
378 1586 darkviper
							unset($aSql);
379
							break;
380
						}
381
					}
382
					$sql = '';
383
				}
384
			}
385
		}
386
		return $retval;
387
	}
388 1362 Luisehahne
389 1586 darkviper
/**
390
 * retuns the type of the engine used for requested table
391
 * @param string $table name of the table, including prefix
392
 * @return boolean/string false on error, or name of the engine (myIsam/InnoDb)
393
 */
394 1535 Luisehahne
	public function getTableEngine($table)
395
	{
396
		$retVal = false;
397 1680 darkviper
		$mysqlVersion = mysql_get_server_info($this->_db_handle);
398 1535 Luisehahne
		$engineValue = (version_compare($mysqlVersion, '5.0') < 0) ? 'Type' : 'Engine';
399 1770 Luisehahne
		$sql = 'SHOW TABLE STATUS FROM `' . $this->_db_name . '` LIKE \'' . $table . '\'';
400 1680 darkviper
		if(($result = $this->query($sql, $this->_db_handle))) {
401 1535 Luisehahne
			if(($row = $result->fetchRow(MYSQL_ASSOC))) {
402
				$retVal = $row[$engineValue];
403
			}
404
		}
405
		return $retVal;
406
	}
407
408
409 1362 Luisehahne
} /// end of class database
410
411 1549 Luisehahne
define('MYSQL_SEEK_FIRST', 0);
412
define('MYSQL_SEEK_LAST', -1);
413
414 4 ryan
class mysql {
415
416 1680 darkviper
	private $result = null;
417
	private $_db_handle = null;
418 4 ryan
	// Run a query
419 1680 darkviper
	function query($statement, $dbHandle) {
420
		$this->_db_handle = $dbHandle;
421
		$this->result = mysql_query($statement, $this->_db_handle);
422
		$this->error = mysql_error($this->_db_handle);
423 4 ryan
		return $this->result;
424
	}
425 1763 Luisehahne
426 4 ryan
	// Fetch num rows
427
	function numRows() {
428
		return mysql_num_rows($this->result);
429
	}
430 1011 Ruebenwurz
431
	// Fetch row  $typ = MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH
432
	function fetchRow($typ = MYSQL_BOTH) {
433
		return mysql_fetch_array($this->result, $typ);
434 4 ryan
	}
435 1011 Ruebenwurz
436 1549 Luisehahne
	function rewind()
437
	{
438
		return $this->seekRow();
439
	}
440
441
	function seekRow( $position = MYSQL_SEEK_FIRST )
442
	{
443
		$pmax = $this->numRows() - 1;
444
		$p = (($position < 0 || $position > $pmax) ? $pmax : $position);
445
		return mysql_data_seek($this->result, $p);
446
	}
447
448 4 ryan
	// Get error
449
	function error() {
450
		if(isset($this->error)) {
451
			return $this->error;
452
		} else {
453
			return null;
454
		}
455
	}
456
457
}
458 1763 Luisehahne
459 1364 Luisehahne
/* this function is placed inside this file temporarely until a better place is found */
460
/*  function to update a var/value-pair(s) in table ****************************
461
 *  nonexisting keys are inserted
462
 *  @param string $table: name of table to use (without prefix)
463
 *  @param mixed $key:    a array of key->value pairs to update
464
 *                        or a string with name of the key to update
465
 *  @param string $value: a sting with needed value, if $key is a string too
466
 *  @return bool:  true if any keys are updated, otherwise false
467
 */
468
	function db_update_key_value($table, $key, $value = '')
469
	{
470
		global $database;
471
		if( !is_array($key))
472
		{
473
			if( trim($key) != '' )
474
			{
475
				$key = array( trim($key) => trim($value) );
476
			} else {
477
				$key = array();
478
			}
479
		}
480
		$retval = true;
481
		foreach( $key as $index=>$val)
482
		{
483
			$index = strtolower($index);
484 1680 darkviper
			$sql = 'SELECT COUNT(`setting_id`) '
485
			     . 'FROM `'.TABLE_PREFIX.$table.'` '
486
			     . 'WHERE `name` = \''.$index.'\' ';
487 1364 Luisehahne
			if($database->get_one($sql))
488
			{
489
				$sql = 'UPDATE ';
490
				$sql_where = 'WHERE `name` = \''.$index.'\'';
491
			}else {
492
				$sql = 'INSERT INTO ';
493
				$sql_where = '';
494
			}
495
			$sql .= '`'.TABLE_PREFIX.$table.'` ';
496
			$sql .= 'SET `name` = \''.$index.'\', ';
497
			$sql .= '`value` = \''.$val.'\' '.$sql_where;
498
			if( !$database->query($sql) )
499
			{
500
				$retval = false;
501
			}
502
		}
503
		return $retval;
504
	}