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