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
if(!defined('DB_URL')) {
31
	//define('DB_URL', DB_TYPE.'://'.DB_USERNAME.':'.DB_PASSWORD.'@'.DB_HOST.'/'.DB_NAME);
32
}
33
34
define('DATABASE_CLASS_LOADED', true);
35
36
class database {
37 1362 Luisehahne
38
	private $db_handle  = null; // readonly from outside
39 1613 darkviper
	private $db_name    = '';
40 1362 Luisehahne
	private $connected  = false;
41
42
	private $error      = '';
43
	private $error_type = '';
44
	private $message    = array();
45
46
47 4 ryan
	// Set DB_URL
48
	function database($url = '') {
49
		// Connect to database
50
		$this->connect();
51
		// Check for database connection error
52
		if($this->is_error()) {
53
			die($this->get_error());
54
		}
55
	}
56
57
	// Connect to the database
58
	function connect() {
59
		$status = $this->db_handle = mysql_connect(DB_HOST, DB_USERNAME, DB_PASSWORD);
60
		if(mysql_error()) {
61
			$this->connected = false;
62
			$this->error = mysql_error();
63
		} else {
64
			if(!mysql_select_db(DB_NAME)) {
65
				$this->connected = false;
66
				$this->error = mysql_error();
67
			} else {
68 1613 darkviper
				$this->db_name = DB_NAME;
69 4 ryan
				$this->connected = true;
70
			}
71
		}
72
		return $this->connected;
73
	}
74
75
	// Disconnect from the database
76
	function disconnect() {
77 95 stefan
		if($this->connected==true) {
78 4 ryan
			mysql_close();
79
			return true;
80
		} else {
81
			return false;
82
		}
83
	}
84
85
	// Run a query
86
	function query($statement) {
87
		$mysql = new mysql();
88
		$mysql->query($statement);
89 342 stefan
		$this->set_error($mysql->error());
90 4 ryan
		if($mysql->error()) {
91
			return null;
92
		} else {
93
			return $mysql;
94
		}
95
	}
96 1362 Luisehahne
97 4 ryan
	// Gets the first column of the first row
98 1362 Luisehahne
	function get_one( $statement )
99
	{
100
		$fetch_row = mysql_fetch_array(mysql_query($statement) );
101 4 ryan
		$result = $fetch_row[0];
102 342 stefan
		$this->set_error(mysql_error());
103 4 ryan
		if(mysql_error()) {
104
			return null;
105
		} else {
106
			return $result;
107
		}
108
	}
109
110
	// Set the DB error
111
	function set_error($message = null) {
112
		global $TABLE_DOES_NOT_EXIST, $TABLE_UNKNOWN;
113
		$this->error = $message;
114
		if(strpos($message, 'no such table')) {
115
			$this->error_type = $TABLE_DOES_NOT_EXIST;
116
		} else {
117
			$this->error_type = $TABLE_UNKNOWN;
118
		}
119
	}
120
121
	// Return true if there was an error
122
	function is_error() {
123
		return (!empty($this->error)) ? true : false;
124
	}
125
126
	// Return the error
127
	function get_error() {
128
		return $this->error;
129
	}
130
131 1613 darkviper
/**
132
 * default Getter for some properties
133
 * @param string $sPropertyName
134
 * @return mixed NULL on error or missing property
135 1362 Luisehahne
 */
136 1613 darkviper
	public function __get($sPropertyName)
137 1362 Luisehahne
	{
138 1613 darkviper
		switch ($sPropertyName):
139
			case 'db_handle':
140
			case 'DbHandle':
141
				$retval = $this->db_handle;
142
				break;
143
			case 'db_name':
144
			case 'DbName':
145
				$retval = $this->db_name;
146
				break;
147
			default:
148
				$retval = null;
149
				break;
150
		endswitch;
151
		return $retval;
152
	} // __get()
153 1362 Luisehahne
154
/*
155
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
156
 * @param string $field_name: name of the field to seek for
157
 * @return bool: true if field exists
158
 */
159
	public function field_exists($table_name, $field_name)
160
	{
161
		$sql = 'DESCRIBE `'.$table_name.'` `'.$field_name.'` ';
162
		$query = $this->query($sql);
163
		return ($query->numRows() != 0);
164
	}
165
166
/*
167
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
168
 * @param string $index_name: name of the index to seek for
169
 * @return bool: true if field exists
170
 */
171
	public function index_exists($table_name, $index_name, $number_fields = 0)
172
	{
173
		$number_fields = intval($number_fields);
174
		$keys = 0;
175
		$sql = 'SHOW INDEX FROM `'.$table_name.'`';
176
		if( ($res_keys = $this->query($sql)) )
177
		{
178
			while(($rec_key = $res_keys->fetchRow()))
179
			{
180
				if( $rec_key['Key_name'] == $index_name )
181
				{
182
					$keys++;
183
				}
184
			}
185
186
		}
187
		if( $number_fields == 0 )
188
		{
189
			return ($keys != $number_fields);
190
		}else
191
		{
192
			return ($keys == $number_fields);
193
		}
194
	}
195
/*
196
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
197
 * @param string $field_name: name of the field to add
198
 * @param string $description: describes the new field like ( INT NOT NULL DEFAULT '0')
199
 * @return bool: true if successful, otherwise false and error will be set
200
 */
201
	public function field_add($table_name, $field_name, $description)
202
	{
203 1442 Luisehahne
		if( !$this->field_exists($table_name, $field_name) )
204 1362 Luisehahne
		{ // add new field into a table
205
			$sql = 'ALTER TABLE `'.$table_name.'` ADD '.$field_name.' '.$description.' ';
206
			$query = $this->query($sql);
207
			$this->set_error(mysql_error());
208
			if( !$this->is_error() )
209
			{
210 1442 Luisehahne
				return ( $this->field_exists($table_name, $field_name) ) ? true : false;
211 1362 Luisehahne
			}
212
		}else
213
		{
214
			$this->set_error('field \''.$field_name.'\' already exists');
215
		}
216
		return false;
217
	}
218
219
/*
220
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
221
 * @param string $field_name: name of the field to add
222
 * @param string $description: describes the new field like ( INT NOT NULL DEFAULT '0')
223
 * @return bool: true if successful, otherwise false and error will be set
224
 */
225
	public function field_modify($table_name, $field_name, $description)
226
	{
227
		$retval = false;
228 1486 DarkViper
		if( $this->field_exists($table_name, $field_name) )
229 1362 Luisehahne
		{ // modify a existing field in a table
230 1486 DarkViper
			$sql  = 'ALTER TABLE `'.$table_name.'` MODIFY `'.$field_name.'` '.$description;
231
			$retval = ( $this->query($sql) ? true : false);
232
			$this->set_error(mysql_error());
233 1362 Luisehahne
		}
234 1486 DarkViper
		return $retval;
235 1362 Luisehahne
	}
236
237
/*
238
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
239
 * @param string $field_name: name of the field to remove
240
 * @return bool: true if successful, otherwise false and error will be set
241
 */
242
	public function field_remove($table_name, $field_name)
243
	{
244
		$retval = false;
245 1507 Luisehahne
		if( $this->field_exists($table_name, $field_name) )
246 1362 Luisehahne
		{ // modify a existing field in a table
247
			$sql  = 'ALTER TABLE `'.$table_name.'` DROP `'.$field_name.'`';
248
			$retval = ( $this->query($sql) ? true : false );
249
		}
250
		return $retval;
251
	}
252
253
/*
254
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
255
 * @param string $index_name: name of the new index
256
 * @param string $field_list: comma seperated list of fields for this index
257
 * @param string $index_type: kind of index (UNIQUE, PRIMARY, '')
258
 * @return bool: true if successful, otherwise false and error will be set
259
 */
260
	public function index_add($table_name, $index_name, $field_list, $index_type = '')
261
	{
262
		$retval = false;
263
		$field_list = str_replace(' ', '', $field_list);
264
		$field_list = explode(',', $field_list);
265
		$number_fields = sizeof($field_list);
266
		$field_list = '`'.implode('`,`', $field_list).'`';
267
		if( $this->index_exists($table_name, $index_name, $number_fields) ||
268
		    $this->index_exists($table_name, $index_name))
269
		{
270
			$sql  = 'ALTER TABLE `'.$table_name.'` ';
271
			$sql .= 'DROP INDEX `'.$index_name.'`';
272
			if( $this->query($sql))
273
			{
274
				$sql  = 'ALTER TABLE `'.$table_name.'` ';
275
				$sql .= 'ADD '.$index_type.' `'.$index_name.'` ( '.$field_list.' ); ';
276
				if( $this->query($sql)) { $retval = true; }
277
			}
278
		}
279
		return $retval;
280
	}
281
282
/*
283
 * @param string $table_name: full name of the table (incl. TABLE_PREFIX)
284
 * @param string $field_name: name of the field to remove
285
 * @return bool: true if successful, otherwise false and error will be set
286
 */
287
	public function index_remove($table_name, $index_name)
288
	{
289
		$retval = false;
290
		if( $this->index_exists($table_name, $index_name) )
291
		{ // modify a existing field in a table
292
			$sql  = 'ALTER TABLE `'.$table_name.'` DROP INDEX `'.$index_name.'`';
293
			$retval = ( $this->query($sql) ? true : false );
294
		}
295
		return $retval;
296
	}
297 1586 darkviper
/**
298
 * Import a standard *.sql dump file
299
 * @param string $sSqlDump link to the sql-dumpfile
300
 * @param string $sTablePrefix
301 1591 darkviper
 * @param bool $bPreserve set to true will ignore all DROP TABLE statements
302 1586 darkviper
 * @param string $sTblEngine
303
 * @param string $sTblCollation
304
 * @return boolean true if import successful
305
 */
306
	public function SqlImport($sSqlDump,
307
	                          $sTablePrefix = '',
308 1591 darkviper
	                          $bPreserve = true,
309 1586 darkviper
	                          $sTblEngine = 'ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci',
310 1591 darkviper
	                          $sTblCollation = ' collate utf8_unicode_ci')
311 1586 darkviper
	{
312
		$retval = true;
313
		$this->error = '';
314
		$aSearch  = array('{TABLE_PREFIX}','{TABLE_ENGINE}', '{TABLE_COLLATION}');
315
		$aReplace = array($sTablePrefix, $sTblEngine, $sTblCollation);
316
		$sql = '';
317
		$aSql = file($sSqlDump);
318
		while ( sizeof($aSql) > 0 ) {
319
			$sSqlLine = trim(array_shift($aSql));
320 1591 darkviper
			if (!preg_match('/^[-\/]+.*/', $sSqlLine)) {
321 1592 darkviper
				$sql = $sql.' '.$sSqlLine;
322 1586 darkviper
				if ((substr($sql,-1,1) == ';')) {
323
					$sql = trim(str_replace( $aSearch, $aReplace, $sql));
324
					if (!($bPreserve && preg_match('/^\s*DROP TABLE IF EXISTS/siU', $sql))) {
325
						if(!mysql_query($sql, $this->db_handle)) {
326
							$retval = false;
327
							$this->error = mysql_error($this->db_handle);
328
							unset($aSql);
329
							break;
330
						}
331
					}
332
					$sql = '';
333
				}
334
			}
335
		}
336
		return $retval;
337
	}
338 1362 Luisehahne
339 1586 darkviper
/**
340
 * retuns the type of the engine used for requested table
341
 * @param string $table name of the table, including prefix
342
 * @return boolean/string false on error, or name of the engine (myIsam/InnoDb)
343
 */
344 1535 Luisehahne
	public function getTableEngine($table)
345
	{
346
		$retVal = false;
347
		$mysqlVersion = mysql_get_server_info($this->db_handle);
348
		$engineValue = (version_compare($mysqlVersion, '5.0') < 0) ? 'Type' : 'Engine';
349 1613 darkviper
		$sql = "SHOW TABLE STATUS FROM " . $this->db_name . " LIKE '" . $table . "'";
350 1535 Luisehahne
		if(($result = $this->query($sql))) {
351
			if(($row = $result->fetchRow(MYSQL_ASSOC))) {
352
				$retVal = $row[$engineValue];
353
			}
354
		}
355
		return $retVal;
356
	}
357
358
359 1362 Luisehahne
} /// end of class database
360
361 1549 Luisehahne
define('MYSQL_SEEK_FIRST', 0);
362
define('MYSQL_SEEK_LAST', -1);
363
364 4 ryan
class mysql {
365
366
	// Run a query
367
	function query($statement) {
368
		$this->result = mysql_query($statement);
369
		$this->error = mysql_error();
370
		return $this->result;
371
	}
372
373
	// Fetch num rows
374
	function numRows() {
375
		return mysql_num_rows($this->result);
376
	}
377 1011 Ruebenwurz
378
	// Fetch row  $typ = MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH
379
	function fetchRow($typ = MYSQL_BOTH) {
380
		return mysql_fetch_array($this->result, $typ);
381 4 ryan
	}
382 1011 Ruebenwurz
383 1549 Luisehahne
	function rewind()
384
	{
385
		return $this->seekRow();
386
	}
387
388
	function seekRow( $position = MYSQL_SEEK_FIRST )
389
	{
390
		$pmax = $this->numRows() - 1;
391
		$p = (($position < 0 || $position > $pmax) ? $pmax : $position);
392
		return mysql_data_seek($this->result, $p);
393
	}
394
395 4 ryan
	// Get error
396
	function error() {
397
		if(isset($this->error)) {
398
			return $this->error;
399
		} else {
400
			return null;
401
		}
402
	}
403
404
}
405 1364 Luisehahne
/* this function is placed inside this file temporarely until a better place is found */
406
/*  function to update a var/value-pair(s) in table ****************************
407
 *  nonexisting keys are inserted
408
 *  @param string $table: name of table to use (without prefix)
409
 *  @param mixed $key:    a array of key->value pairs to update
410
 *                        or a string with name of the key to update
411
 *  @param string $value: a sting with needed value, if $key is a string too
412
 *  @return bool:  true if any keys are updated, otherwise false
413
 */
414
	function db_update_key_value($table, $key, $value = '')
415
	{
416
		global $database;
417
		if( !is_array($key))
418
		{
419
			if( trim($key) != '' )
420
			{
421
				$key = array( trim($key) => trim($value) );
422
			} else {
423
				$key = array();
424
			}
425
		}
426
		$retval = true;
427
		foreach( $key as $index=>$val)
428
		{
429
			$index = strtolower($index);
430
			$sql = 'SELECT COUNT(`setting_id`) FROM `'.TABLE_PREFIX.$table.'` WHERE `name` = \''.$index.'\' ';
431
			if($database->get_one($sql))
432
			{
433
				$sql = 'UPDATE ';
434
				$sql_where = 'WHERE `name` = \''.$index.'\'';
435
			}else {
436
				$sql = 'INSERT INTO ';
437
				$sql_where = '';
438
			}
439
			$sql .= '`'.TABLE_PREFIX.$table.'` ';
440
			$sql .= 'SET `name` = \''.$index.'\', ';
441
			$sql .= '`value` = \''.$val.'\' '.$sql_where;
442
			if( !$database->query($sql) )
443
			{
444
				$retval = false;
445
			}
446
		}
447
		return $retval;
448
	}