Project

General

Profile

1
<?php
2
/**
3
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
4
 *
5
 * This program is free software: you can redistribute it and/or modify
6
 * it under the terms of the GNU General Public License as published by
7
 * the Free Software Foundation, either version 3 of the License, or
8
 * (at your option) any later version.
9
 *
10
 * This program is distributed in the hope that it will be useful,
11
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
13
 * GNU General Public License for more details.
14
 *
15
 * You should have received a copy of the GNU General Public License
16
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
17
 */
18
/**
19
 * WbDatabase.php
20
 *
21
 * @category     Core
22
 * @package      Core_database
23
 * @author       Werner v.d.Decken <wkl@isteam.de>
24
 * @author       Dietmar W. <dietmar.woellbrink@websitebaker.org>
25
 * @copyright    Werner v.d.Decken <wkl@isteam.de>
26
 * @license      http://www.gnu.org/licenses/gpl.html   GPL License
27
 * @version      0.0.9
28
 * @revision     $Revision: 1928 $
29
 * @lastmodified $Date: 2013-06-20 18:07:59 +0200 (Thu, 20 Jun 2013) $
30
 * @deprecated   from WB version number 2.9
31
 * @description  Mysql database wrapper for use with websitebaker up to version 2.8.4
32
 */
33

    
34
/* -------------------------------------------------------- */
35
@define('DATABASE_CLASS_LOADED', true);
36

    
37
class WbDatabase {
38

    
39
	private static $_oInstances = array();
40

    
41
	private $_db_handle  = null; // readonly from outside
42
	private $_db_name    = '';
43
	protected $sTablePrefix = '';
44
	protected $sCharset     = '';
45
	protected $connected    = false;
46
	protected $error        = '';
47
	protected $error_type   = '';
48
	protected $iQueryCount  = 0;
49

    
50
/* prevent from public instancing */
51
	protected function  __construct() {}
52
/* prevent from cloning */
53
	private function __clone() {}
54
/**
55
 * get a valid instance of this class
56
 * @param string $sIdentifier selector for several different instances
57
 * @return object
58
 */
59
	public static function getInstance($sIdentifier = 'core') {
60
		if( !isset(self::$_oInstances[$sIdentifier])) {
61
            $c = __CLASS__;
62
            self::$_oInstances[$sIdentifier] = new $c;
63
		}
64
		return self::$_oInstances[$sIdentifier];
65
	}
66
/**
67
 * disconnect and kills an existing instance
68
 * @param string $sIdentifier
69
 */
70
	public static function killInstance($sIdentifier) {
71
		if($sIdentifier != 'core') {
72
			if( isset(self::$_oInstances[$sIdentifier])) {
73
				self::$_oInstances[$sIdentifier]->disconnect();
74
				unset(self::$_oInstances[$sIdentifier]);
75
			}
76
		}
77
	}
78
/**
79
 * Connect to the database
80
 * @param string $url
81
 * @return bool
82
 *
83
 * Example for SQL-Url:  'mysql://user:password@demo.de[:3306]/datenbank'
84
 */
85
	public function doConnect($url = '') {
86
		$this->connected = false;
87
		if($url != '') {
88
			$aIni = parse_url($url);
89
			
90
			$scheme   = isset($aIni['scheme']) ? $aIni['scheme'] : 'mysql';
91
			$hostname = isset($aIni['host']) ? $aIni['host'] : '';
92
			$username = isset($aIni['user']) ? $aIni['user'] : '';
93
			$password = isset($aIni['pass']) ? $aIni['pass'] : '';
94
			$hostport = isset($aIni['port']) ? $aIni['port'] : '3306';
95
			$hostport = $hostport == '3306' ? '' : ':'.$hostport;
96
			$db_name  = ltrim(isset($aIni['path']) ? $aIni['path'] : '', '/\\');
97
			$sTmp = isset($aIni['query']) ? $aIni['query'] : '';
98
			$aQuery = explode('&', $sTmp);
99
			foreach($aQuery as $sArgument) {
100
				$aArg = explode('=', $sArgument);
101
				switch(strtolower($aArg[0])) {
102
					case 'charset':
103
						$this->sCharset = strtolower(preg_replace('/[^a-z0-9]/i', '', $aArg[1]));
104
						break;
105
					case 'tableprefix':
106
						$this->sTablePrefix = $aArg[1];
107
						break;
108
					default:
109
						break;
110
				}
111
			}
112
			$this->_db_name = $db_name;
113
		}else {
114
			throw new WbDatabaseException('Missing parameter: unable to connect database');
115
		}
116
		$this->_db_handle = @mysql_connect($hostname.$hostport,
117
		                                   $username,
118
		                                   $password,
119
		                                   true);
120
		if(!$this->_db_handle) {
121
			throw new WbDatabaseException('unable to connect \''.$scheme.'://'.
122
			                           $hostname.$hostport.'\'');
123
		} else {
124
			if(!@mysql_select_db($db_name, $this->_db_handle)) {
125
				throw new WbDatabaseException('unable to select database \''.$db_name.
126
				                           '\' on \''.$scheme.'://'.
127
				                           $hostname.$hostport.'\'');
128
			} else {
129
				if($this->sCharset) {
130
					@mysql_query('SET NAMES \''.$this->sCharset.'\'', $this->_db_handle);
131
				}
132
				$this->connected = true;
133
			}
134
		}
135
		return $this->connected;
136
	}
137

    
138
	// Disconnect from the database
139
	public function disconnect() {
140
		if($this->connected==true) {
141
			mysql_close($this->_db_handle);
142
			return true;
143
		} else {
144
			return false;
145
		}
146
	}
147

    
148
	// Run a query
149
	public function query($statement) {
150
		$this->iQueryCount++;
151
		$mysql = new mysql();
152
		$mysql->query($statement, $this->_db_handle);
153
		$this->set_error($mysql->error($this->_db_handle));
154
		if($mysql->error($this->_db_handle)) {
155
			return null;
156
		} else {
157
			return $mysql;
158
		}
159
	}
160

    
161
	// Gets the first column of the first row
162
	public function get_one( $statement )
163
	{
164
		$this->iQueryCount++;
165
		$fetch_row = mysql_fetch_array(mysql_query($statement, $this->_db_handle));
166
		$result = $fetch_row[0];
167
		$this->set_error(mysql_error($this->_db_handle));
168
		if(mysql_error($this->_db_handle)) {
169
			return null;
170
		} else {
171
			return $result;
172
		}
173
	}
174

    
175
	// Set the DB error
176
	public function set_error($message = null) {
177
		global $TABLE_DOES_NOT_EXIST, $TABLE_UNKNOWN;
178
		$this->error = $message;
179
		if(strpos($message, 'no such table')) {
180
			$this->error_type = $TABLE_DOES_NOT_EXIST;
181
		} else {
182
			$this->error_type = $TABLE_UNKNOWN;
183
		}
184
	}
185

    
186
	// Return true if there was an error
187
	public function is_error() {
188
		return (!empty($this->error)) ? true : false;
189
	}
190

    
191
	// Return the error
192
	public function get_error() {
193
		return $this->error;
194
	}
195
/**
196
 * Protect class from property injections
197
 * @param string name of property
198
 * @param mixed value
199
 * @throws WbDatabaseException
200
 */	
201
	public function __set($name, $value) {
202
		throw new WbDatabaseException('tried to set a readonly or nonexisting property ['.$name.']!! ');
203
	}
204
/**
205
 * default Getter for some properties
206
 * @param string name of the Property
207
 * @return mixed NULL on error or missing property
208
 */
209
	public function __get($sPropertyName)
210
	{
211
		switch ($sPropertyName):
212
			case 'db_handle':
213
			case 'DbHandle':
214
			case 'getDbHandle':
215
				$retval = $this->_db_handle;
216
				break;
217
			case 'LastInsertId':
218
			case 'getLastInsertId':
219
				$retval = mysql_insert_id($this->_db_handle);
220
				break;
221
			case 'db_name':
222
			case 'DbName':
223
			case 'getDbName':
224
				$retval = $this->_db_name;
225
				break;
226
			case 'TablePrefix':
227
			case 'getTablePrefix':
228
				$retval = $this->sTablePrefix;			
229
				break;
230
			case 'getQueryCount':
231
				$retval = $this->iQueryCount;
232
				break;
233
			default:
234
				$retval = null;
235
				break;
236
		endswitch;
237
		return $retval;
238
	} // __get()
239
/**
240
 * Escapes special characters in a string for use in an SQL statement
241
 * @param string $unescaped_string
242
 * @return string
243
 */
244
	public function escapeString($unescaped_string)
245
	{
246
		return mysql_real_escape_string($unescaped_string, $this->_db_handle);
247
	}
248
/**
249
 * Last inserted Id
250
 * @return bool|int false on error, 0 if no record inserted
251
 */	
252
	public function getLastInsertId()
253
	{
254
		return mysql_insert_id($this->_db_handle);
255
	}
256
/*
257
 * @param string full name of the table (incl. TABLE_PREFIX)
258
 * @param string name of the field to seek for
259
 * @return bool true if field exists
260
 */
261
	public function field_exists($table_name, $field_name)
262
	{
263
		$sql = 'DESCRIBE `'.$table_name.'` `'.$field_name.'` ';
264
		$query = $this->query($sql, $this->_db_handle);
265
		return ($query->numRows() != 0);
266
	}
267
/*
268
 * @param string full name of the table (incl. TABLE_PREFIX)
269
 * @param string name of the index to seek for
270
 * @return bool true if field exists
271
 */
272
	public function index_exists($table_name, $index_name, $number_fields = 0)
273
	{
274
		$number_fields = intval($number_fields);
275
		$keys = 0;
276
		$sql = 'SHOW INDEX FROM `'.$table_name.'`';
277
		if( ($res_keys = $this->query($sql, $this->_db_handle)) )
278
		{
279
			while(($rec_key = $res_keys->fetchRow()))
280
			{
281
				if( $rec_key['Key_name'] == $index_name )
282
				{
283
					$keys++;
284
				}
285
			}
286

    
287
		}
288
		if( $number_fields == 0 )
289
		{
290
			return ($keys != $number_fields);
291
		}else
292
		{
293
			return ($keys == $number_fields);
294
		}
295
	}
296

    
297
/*
298
 * @param string full name of the table (incl. TABLE_PREFIX)
299
 * @param string name of the field to add
300
 * @param string describes the new field like ( INT NOT NULL DEFAULT '0')
301
 * @return bool true if successful, otherwise false and error will be set
302
 */
303
	public function field_add($table_name, $field_name, $description)
304
	{
305
		if( !$this->field_exists($table_name, $field_name) )
306
		{ // add new field into a table
307
			$sql = 'ALTER TABLE `'.$table_name.'` ADD '.$field_name.' '.$description.' ';
308
			$query = $this->query($sql, $this->_db_handle);
309
			$this->set_error(mysql_error($this->_db_handle));
310
			if( !$this->is_error() )
311
			{
312
				return ( $this->field_exists($table_name, $field_name) ) ? true : false;
313
			}
314
		}else
315
		{
316
			$this->set_error('field \''.$field_name.'\' already exists');
317
		}
318
		return false;
319
	}
320

    
321
/*
322
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
323
 * @param string $field_name: name of the field to add
324
 * @param string $description: describes the new field like ( INT NOT NULL DEFAULT '0')
325
 * @return bool: true if successful, otherwise false and error will be set
326
 */
327
	public function field_modify($table_name, $field_name, $description)
328
	{
329
		$retval = false;
330
		if( $this->field_exists($table_name, $field_name) )
331
		{ // modify a existing field in a table
332
			$sql  = 'ALTER TABLE `'.$table_name.'` MODIFY `'.$field_name.'` '.$description;
333
			$retval = ( $this->query($sql, $this->_db_handle) ? true : false);
334
			$this->set_error(mysql_error());
335
		}
336
		return $retval;
337
	}
338

    
339
/*
340
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
341
 * @param string $field_name: name of the field to remove
342
 * @return bool: true if successful, otherwise false and error will be set
343
 */
344
	public function field_remove($table_name, $field_name)
345
	{
346
		$retval = false;
347
		if( $this->field_exists($table_name, $field_name) )
348
		{ // modify a existing field in a table
349
			$sql  = 'ALTER TABLE `'.$table_name.'` DROP `'.$field_name.'`';
350
			$retval = ( $this->query($sql, $this->_db_handle) ? true : false );
351
		}
352
		return $retval;
353
	}
354

    
355
/*
356
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
357
 * @param string $index_name: name of the new index (empty string for PRIMARY)
358
 * @param string $field_list: comma seperated list of fields for this index
359
 * @param string $index_type: kind of index (PRIMARY, UNIQUE, KEY, FULLTEXT)
360
 * @return bool: true if successful, otherwise false and error will be set
361
 */
362
     public function index_add($table_name, $index_name, $field_list, $index_type = 'KEY')
363
     {
364
        $retval = false;
365
        $field_list = explode(',', (str_replace(' ', '', $field_list)));
366
        $number_fields = sizeof($field_list);
367
        $field_list = '`'.implode('`,`', $field_list).'`';
368
        $index_name = $index_type == 'PRIMARY' ? $index_type : $index_name;
369
        if( $this->index_exists($table_name, $index_name, $number_fields) ||
370
            $this->index_exists($table_name, $index_name))
371
        {
372
            $sql  = 'ALTER TABLE `'.$table_name.'` ';
373
            $sql .= 'DROP INDEX `'.$index_name.'`';
374
            if( !$this->query($sql, $this->_db_handle)) { return false; }
375
        }
376
        $sql  = 'ALTER TABLE `'.$table_name.'` ';
377
        $sql .= 'ADD '.$index_type.' ';
378
        $sql .= $index_type == 'PRIMARY' ? 'KEY ' : '`'.$index_name.'` ';
379
        $sql .= '( '.$field_list.' ); ';
380
        if( $this->query($sql, $this->_db_handle)) { $retval = true; }
381
        return $retval;
382
    }
383

    
384
/*
385
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
386
 * @param string $field_name: name of the field to remove
387
 * @return bool: true if successful, otherwise false and error will be set
388
 */
389
	public function index_remove($table_name, $index_name)
390
	{
391
		$retval = false;
392
		if( $this->index_exists($table_name, $index_name) )
393
		{ // modify a existing field in a table
394
			$sql  = 'ALTER TABLE `'.$table_name.'` DROP INDEX `'.$index_name.'`';
395
			$retval = ( $this->query($sql, $this->_db_handle) ? true : false );
396
		}
397
		return $retval;
398
	}
399

    
400
/**
401
 * Import a standard *.sql dump file
402
 * @param string $sSqlDump link to the sql-dumpfile
403
 * @param string $sTablePrefix
404
 * @param bool     $bPreserve   set to true will ignore all DROP TABLE statements
405
 * @param string   $sEngine     can be 'MyISAM' or 'InnoDB'
406
 * @param string   $sCollation  one of the list of available collations
407
 * @return boolean true if import successful
408
 */
409
	public function SqlImport($sSqlDump,
410
	                          $sTablePrefix = '',
411
	                          $bPreserve    = true,
412
	                          $sEngine      = 'MyISAM',
413
	                          $sCollation   = 'utf8_unicode_ci')
414
	{
415
		$sCollation = ($sCollation != '' ? $sCollation : 'utf8_unicode_ci');
416
		$aCharset = preg_split('/_/', $sCollation, null, PREG_SPLIT_NO_EMPTY);
417
		$sEngine = 'ENGINE='.$sEngine.' DEFAULT CHARSET='.$aCharset[0].' COLLATE='.$sCollation;
418
		$sCollation = ' collate '.$sCollation;
419
		$retval = true;
420
		$this->error = '';
421
		$aSearch  = array('{TABLE_PREFIX}','{TABLE_ENGINE}', '{TABLE_COLLATION}');
422
		$aReplace = array($this->sTablePrefix, $sEngine, $sCollation);
423
		$sql = '';
424
		$aSql = file($sSqlDump);
425
//		$aSql[0] = preg_replace('/^\xEF\xBB\xBF/', '', $aSql[0]);
426
		$aSql[0] = preg_replace('/^[\xAA-\xFF]{3}/', '', $aSql[0]);
427
		while ( sizeof($aSql) > 0 ) {
428
			$sSqlLine = trim(array_shift($aSql));
429
			if (!preg_match('/^[-\/]+.*/', $sSqlLine)) {
430
				$sql = $sql.' '.$sSqlLine;
431
				if ((substr($sql,-1,1) == ';')) {
432
					$sql = trim(str_replace( $aSearch, $aReplace, $sql));
433
					if (!($bPreserve && preg_match('/^\s*DROP TABLE IF EXISTS/siU', $sql))) {
434
						if(!mysql_query($sql, $this->_db_handle)) {
435
							$retval = false;
436
							$this->error = mysql_error($this->_db_handle);
437
							unset($aSql);
438
							break;
439
						}
440
					}
441
					$sql = '';
442
				}
443
			}
444
		}
445
		return $retval;
446
	}
447

    
448
/**
449
 * retuns the type of the engine used for requested table
450
 * @param string $table name of the table, including prefix
451
 * @return boolean/string false on error, or name of the engine (myIsam/InnoDb)
452
 */
453
	public function getTableEngine($table)
454
	{
455
		$retVal = false;
456
		$mysqlVersion = mysql_get_server_info($this->_db_handle);
457
		$engineValue = (version_compare($mysqlVersion, '5.0') < 0) ? 'Type' : 'Engine';
458
		$sql = 'SHOW TABLE STATUS FROM `' . $this->_db_name . '` LIKE \'' . $table . '\'';
459
		if(($result = $this->query($sql, $this->_db_handle))) {
460
			if(($row = $result->fetchRow(MYSQL_ASSOC))) {
461
				$retVal = $row[$engineValue];
462
			}
463
		}
464
		return $retVal;
465
	}
466

    
467

    
468
} /// end of class database
469
// //////////////////////////////////////////////////////////////////////////////////// //
470
/**
471
 * WbDatabaseException
472
 *
473
 * @category     Core
474
 * @package      Core_database
475
 * @author       Werner v.d.Decken <wkl@isteam.de>
476
 * @copyright    Werner v.d.Decken <wkl@isteam.de>
477
 * @license      http://www.gnu.org/licenses/gpl.html   GPL License
478
 * @version      2.9.0
479
 * @revision     $Revision: 1928 $
480
 * @lastmodified $Date: 2013-06-20 18:07:59 +0200 (Thu, 20 Jun 2013) $
481
 * @description  Exceptionhandler for the WbDatabase and depending classes
482
 */
483
class WbDatabaseException extends AppException {}
484

    
485
define('MYSQL_SEEK_FIRST', 0);
486
define('MYSQL_SEEK_LAST', -1);
487

    
488
class mysql {
489

    
490
	private $result = null;
491
	private $_db_handle = null;
492
	// Run a query
493
	function query($statement, $dbHandle) {
494
		$this->_db_handle = $dbHandle;
495
		$this->result = @mysql_query($statement, $this->_db_handle);
496
		if($this->result === false) {
497
			if(DEBUG) {
498
				throw new WbDatabaseException(mysql_error($this->_db_handle));
499
			}else{
500
				throw new WbDatabaseException('Error in SQL-Statement');
501
			}
502
		}
503
		$this->error = mysql_error($this->_db_handle);
504
		return $this->result;
505
	}
506

    
507
	// Fetch num rows
508
	function numRows() {
509
		return mysql_num_rows($this->result);
510
	}
511

    
512
	// Fetch row  $typ = MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH
513
	function fetchRow($typ = MYSQL_BOTH) {
514
		return mysql_fetch_array($this->result, $typ);
515
	}
516

    
517
	function rewind()
518
	{
519
		return $this->seekRow();
520
	}
521

    
522
	function seekRow( $position = MYSQL_SEEK_FIRST )
523
	{
524
		$pmax = $this->numRows() - 1;
525
		$p = (($position < 0 || $position > $pmax) ? $pmax : $position);
526
		return mysql_data_seek($this->result, $p);
527
	}
528

    
529
	// Get error
530
	function error() {
531
		if(isset($this->error)) {
532
			return $this->error;
533
		} else {
534
			return null;
535
		}
536
	}
537

    
538
}
539
// //////////////////////////////////////////////////////////////////////////////////// //
540
/* this function is placed inside this file temporarely until a better place is found */
541
/*  function to update a var/value-pair(s) in table ****************************
542
 *  nonexisting keys are inserted
543
 *  @param string $table: name of table to use (without prefix)
544
 *  @param mixed $key:    a array of key->value pairs to update
545
 *                        or a string with name of the key to update
546
 *  @param string $value: a sting with needed value, if $key is a string too
547
 *  @return bool:  true if any keys are updated, otherwise false
548
 */
549
	function db_update_key_value($table, $key, $value = '')
550
	{
551
		global $database;
552
		if( !is_array($key))
553
		{
554
			if( trim($key) != '' )
555
			{
556
				$key = array( trim($key) => trim($value) );
557
			} else {
558
				$key = array();
559
			}
560
		}
561
		$retval = true;
562
		foreach( $key as $index=>$val)
563
		{
564
			$index = strtolower($index);
565
			$sql = 'SELECT COUNT(`setting_id`) '
566
			     . 'FROM `'.TABLE_PREFIX.$table.'` '
567
			     . 'WHERE `name` = \''.$index.'\' ';
568
			if($database->get_one($sql))
569
			{
570
				$sql = 'UPDATE ';
571
				$sql_where = 'WHERE `name` = \''.$index.'\'';
572
			}else {
573
				$sql = 'INSERT INTO ';
574
				$sql_where = '';
575
			}
576
			$sql .= '`'.TABLE_PREFIX.$table.'` ';
577
			$sql .= 'SET `name` = \''.$index.'\', ';
578
			$sql .= '`value` = \''.$val.'\' '.$sql_where;
579
			if( !$database->query($sql) )
580
			{
581
				$retval = false;
582
			}
583
		}
584
		return $retval;
585
	}
(16-16/33)