db_mysql.inc.php 18 KB
Newer Older
tbrehm's avatar
tbrehm committed
1
<?php
2 3 4 5 6
/**
 * mySQL Database class
 * 
 * @author Till Brehm
 * @copyright  2005, Till Brehm, projektfarm Gmbh
7
 * @version 0.2
8 9
 * @package ISPConfig
 */
tbrehm's avatar
tbrehm committed
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
/*
Copyright (c) 2005, Till Brehm, projektfarm Gmbh
All rights reserved.

Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:

    * Redistributions of source code must retain the above copyright notice,
      this list of conditions and the following disclaimer.
    * Redistributions in binary form must reproduce the above copyright notice,
      this list of conditions and the following disclaimer in the documentation
      and/or other materials provided with the distribution.
    * Neither the name of ISPConfig nor the names of its contributors
      may be used to endorse or promote products derived from this software without
      specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/

38 39 40 41 42 43 44 45 46 47 48 49
class db
{
	private $dbHost = '';		// hostname of the MySQL server
	private $dbName = '';		// logical database name on that server
	private $dbUser = '';		// database authorized user
	private $dbPass = '';		// user's password
	private $linkId = 0;		// last result of mysql_connect()
	private $queryId = 0;		// last result of mysql_query()
	private $record	= array();	// last record fetched
    private $autoCommit = 1;    // Autocommit Transactions
	private $currentRow;		// current row number
	private $errorNumber = 0;	// last error number
pedro_morgan's avatar
pedro_morgan committed
50
	public $errorMessage = '';	// last error message
51
	private $errorLocation = '';// last error location
52
	public $show_error_messages = false;
tbrehm's avatar
tbrehm committed
53

54 55 56 57 58 59 60 61 62
	public function __construct()
    {
		global $conf;
		$this->dbHost = $conf['db_host'];
		$this->dbName = $conf['db_database'];
		$this->dbUser = $conf['db_user'];
		$this->dbPass = $conf['db_password'];
		//$this->connect();
	}
tbrehm's avatar
tbrehm committed
63

64 65 66 67 68 69 70 71 72
	/**  Error handler */
	public function updateError($location)
    {
		$this->errorNumber = mysql_errno();
		$this->errorMessage = mysql_error();
		$this->errorLocation = $location;
		if($this->errorNumber && $this->show_error_messages){
			echo('<br /><b>'.$this->errorLocation.'</b><br />'.$this->errorMessage);
			flush();
tbrehm's avatar
tbrehm committed
73
		}
74
	}
tbrehm's avatar
tbrehm committed
75

76 77 78 79 80 81
	public function connect()
    {
		if($this->linkId == 0){
			$this->linkId = mysql_connect($this->dbHost, $this->dbUser, $this->dbPass);
			if(!$this->linkId){
				$this->updateError('DB::connect()<br />mysql_connect');
tbrehm's avatar
tbrehm committed
82 83 84
				return false;
			}
		}
85 86
		return true;
	}
tbrehm's avatar
tbrehm committed
87

88 89 90 91
	public function query($queryString)
    {
		if(!$this->connect()){
			return false;
tbrehm's avatar
tbrehm committed
92
		}
93 94 95 96 97 98 99 100
		if(!mysql_select_db($this->dbName, $this->linkId)){
			$this->updateError('DB::connect()<br />mysql_select_db');
			return false;
		}
		$this->queryId = @mysql_query($queryString, $this->linkId);
		$this->updateError('DB::query('.$queryString.')<br />mysql_query');
		if(!$this->queryId){
			return false;
tbrehm's avatar
tbrehm committed
101
		}
102 103 104
		$this->currentRow = 0;
		return $this->queryId;
	}
tbrehm's avatar
tbrehm committed
105

106 107 108 109 110 111 112 113 114
	/** Returns all records as an array */
	public function queryAllRecords($queryString)
    {
		if(!$this->query($queryString)){
			return false;
		}
		$ret = array();
		while($line = $this->nextRecord()){
			$ret[] = $line;
tbrehm's avatar
tbrehm committed
115
		}
116 117
		return $ret;
	}
tbrehm's avatar
tbrehm committed
118

119 120 121 122 123
	/** Returns one row as an array */
	public function queryOneRecord($queryString)
    {
		if(!$this->query($queryString) || $this->numRows() == 0){
			return false;
tbrehm's avatar
tbrehm committed
124
		}
125 126 127 128 129 130 131 132 133 134
		return $this->nextRecord();
	}

	/** Returns the next record as an array */
	public function nextRecord()
    {
        $this->record = mysql_fetch_assoc($this->queryId);
		$this->updateError('DB::nextRecord()<br />mysql_fetch_array');
		if(!$this->record || !is_array($this->record)){
			return false;
135
		}
136 137 138 139 140 141 142 143 144 145 146 147 148 149
		$this->currentRow++;
		return $this->record;
	}

	/** Returns the number of rows returned by the last select query */
	public function numRows()
    {
		return mysql_num_rows($this->queryId);
	}

	public function affectedRows()
    {
		return mysql_affected_rows($this->linkId);
	}
150
		
151 152 153 154 155
	/** Returns the last mySQL insert_id() */
	public function insertID()
    {
		return mysql_insert_id($this->linkId);
	}
tbrehm's avatar
tbrehm committed
156
        
157 158 159 160 161
    /** Checks a variable - Depreciated, use quote() */
    public function check($formfield)
    {
        return $this->quote($formfield);
    }
tbrehm's avatar
tbrehm committed
162
		
163
	/** Escapes quotes in variable. mysql_real_escape_string() */
164
    public function quote($formfield)
165 166 167 168 169 170
    {	
		if(!$this->connect()){
			$this->updateError('WARNING: mysql_connect: Used addslashes instead of mysql_real_escape_string');
			return addslashes($formfield);
		}
        return mysql_real_escape_string($formfield, $this->linkId);
171
    }
tbrehm's avatar
tbrehm committed
172
		
173 174 175 176 177
	/** Unquotes a variable, strip_slashes() */
    public function unquote($formfield)
    {
        return stripslashes($formfield);
    }
tbrehm's avatar
tbrehm committed
178
		
179 180 181 182 183 184
	public function toLower($record)
    {
		if(is_array($record)){
			foreach($record as $key => $val) {
				$key = strtolower($key);
				$out[$key] = $val;
tbrehm's avatar
tbrehm committed
185 186
			}
		}
187 188
	    return $out;
	}
tbrehm's avatar
tbrehm committed
189
       
190 191
    // deprecated
	/*
192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207
    public function insert($tablename, $form, $debug = 0)
    {
        if(is_array($form)){
	        foreach($form as $key => $value){
                $sql_key .= "$key, ";
                $sql_value .= "'".$this->check($value)."', ";
            }
            $sql_key = substr($sql_key,0,strlen($sql_key) - 2);
            $sql_value = substr($sql_value,0,strlen($sql_value) - 2);
            $sql = "INSERT INTO $tablename (".$sql_key.') VALUES ('.$sql_value.')';
            //TODO: where has $debug come from !???
            if($debug == 1){ echo "SQL-Statement: $sql<br><br>"; }
            $this->query($sql);
            if($debug == 1){ echo 'mySQL Error Message: '.$this->errorMessage; }
        }
    }
208 209
    
	// Deprecated
210 211 212 213 214 215 216 217 218 219 220 221 222
    public function update($tablename, $form, $bedingung, $debug = 0)
    {
	    if(is_array($form)){
            foreach($form as $key => $value){
                $insql .= "$key = '".$this->check($value)."', ";
            }
            $insql = substr($insql, 0, strlen($insql) - 2);
            $sql = "UPDATE $tablename SET " . $insql . " WHERE $bedingung";
            if($debug == 1){ echo "SQL-Statement: $sql<br><br>"; }
            $this->query($sql);
            if($debug == 1){ echo 'mySQL Error Message: '.$this->errorMessage; }
        }
    }
223
	*/
224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240
	
	//** Function to fill the datalog with a full differential record.
	public function datalogSave($db_table, $action, $primary_field, $primary_id, $record_old, $record_new) {
		global $app,$conf;

		// Insert backticks only for incomplete table names.
		if(stristr($db_table,'.')) {
			$escape = '';
		} else {
			$escape = '`';
		}

		$diffrec_full = array();
		$diff_num = 0;

		if(is_array($record_old) && count($record_old) > 0) {
			foreach($record_old as $key => $val) {
241
				if(!isset($record_new[$key]) || $record_new[$key] != $val) {
242 243 244 245 246 247 248 249 250 251 252
					// Record has changed
					$diffrec_full['old'][$key] = $val;
					$diffrec_full['new'][$key] = $record_new[$key];
					$diff_num++;
				} else {
					$diffrec_full['old'][$key] = $val;
					$diffrec_full['new'][$key] = $val;
				}
			}
		} elseif(is_array($record_new)) {
			foreach($record_new as $key => $val) {
253
				if(isset($record_new[$key]) && @$record_old[$key] != $val) {
254 255
					// Record has changed
					$diffrec_full['new'][$key] = $val;
256
					$diffrec_full['old'][$key] = @$record_old[$key];
257 258 259 260 261 262 263 264 265 266 267
					$diff_num++;
				} else {
					$diffrec_full['new'][$key] = $val;
					$diffrec_full['old'][$key] = $val;
				}
			}
		}
		
		// Insert the server_id, if the record has a server_id
		$server_id = (isset($record_old["server_id"]) && $record_old["server_id"] > 0)?$record_old["server_id"]:0;
		if(isset($record_new["server_id"])) $server_id = $record_new["server_id"];
268
		
269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284

		if($diff_num > 0) {
			$diffstr = $app->db->quote(serialize($diffrec_full));
			$username = $app->db->quote($_SESSION["s"]["user"]["username"]);
			$dbidx = $primary_field.":".$primary_id;
						
			if($action == 'INSERT') $action = 'i';
			if($action == 'UPDATE') $action = 'u';
			if($action == 'DELETE') $action = 'd';
			$sql = "INSERT INTO sys_datalog (dbtable,dbidx,server_id,action,tstamp,user,data) VALUES ('".$db_table."','$dbidx','$server_id','$action','".time()."','$username','$diffstr')";
			$app->db->query($sql);
		}

		return true;
	}
	
285
	//** Inserts a record and saves the changes into the datalog
286 287 288 289 290 291 292 293 294 295 296 297 298
	public function datalogInsert($tablename, $insert_data, $index_field) {
		global $app;
		
		$old_rec = array();
		$this->query("INSERT INTO $tablename $insert_data");
		$index_value = $this->insertID();
		$new_rec = $this->queryOneRecord("SELECT * FROM $tablename WHERE $index_field = '$index_value'");
		$this->datalogSave($tablename, 'INSERT', $index_field, $index_value, $old_rec, $new_rec);
		
		return true;
	}
	
	//** Updates a record and saves the changes into the datalog
299 300 301 302 303 304 305 306 307 308
	public function datalogUpdate($tablename, $update_data, $index_field, $index_value) {
		global $app;
		
		$old_rec = $this->queryOneRecord("SELECT * FROM $tablename WHERE $index_field = '$index_value'");
		$this->query("UPDATE $tablename SET $update_data WHERE $index_field = '$index_value'");
		$new_rec = $this->queryOneRecord("SELECT * FROM $tablename WHERE $index_field = '$index_value'");
		$this->datalogSave($tablename, 'UPDATE', $index_field, $index_value, $old_rec, $new_rec);
		
		return true;
	}
309 310 311 312 313 314 315 316 317 318 319 320 321 322
	
	//** Deletes a record and saves the changes into the datalog
	public function datalogDelete($tablename, $index_field, $index_value) {
		global $app;
		
		$old_rec = $this->queryOneRecord("SELECT * FROM $tablename WHERE $index_field = '$index_value'");
		$this->query("DELETE FROM $tablename WHERE $index_field = '$index_value'");
		$new_rec = array();
		$this->datalogSave($tablename, 'DELETE', $index_field, $index_value, $old_rec, $new_rec);
		
		return true;
	}
	
	
323 324 325
       
    public function closeConn()
    {
326 327 328 329 330
    	if($this->linkId)
    	{
    		mysql_close($this->linkId);
    		return true;
    	} else { return false; }
331 332
    }
       
333 334 335 336 337 338 339 340
    public function freeResult($query) 
    {
    	if(mysql_free_result($query))
    	{
    		return true;
    	} else {
    		return false;
    	}
341
    }
342 343
    
	/*
344 345 346
    public function delete()
    {
    }
347 348 349
	*/
    
	/*
350 351 352 353
    public function Transaction($action)
    {
        //action = begin, commit oder rollback
    }
354
	*/
355 356 357 358 359 360 361 362 363 364 365 366 367 368
    
    /** Creates a database table with the following format for the $columns array   
    * <code>
    * $columns = array(action =>   add | alter | drop
    *                  name =>     Spaltenname
    *                  name_new => neuer Spaltenname, nur bei 'alter' belegt
    *                  type =>     42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
    *                  typeValue => Wert z.B. bei Varchar
    *                  defaultValue =>  Default Wert
    *                  notNull =>   true | false
    *                  autoInc =>   true | false
    *                  option =>   unique | primary | index)
    * </code>   
    */
369 370
	
	
371 372 373 374 375 376 377
    public function createTable($table_name, $columns)
    {
        $index = '';
        $sql = "CREATE TABLE $table_name (";
        foreach($columns as $col){
            $sql .= $col['name'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
            //* Set default value
378
            if(isset($col['defaultValue']) && $col['defaultValue'] != '') {
379 380 381 382 383 384 385 386
			    if($col['defaultValue'] == 'NULL' or $col['defaultValue'] == 'NOT NULL') {
				    $sql .= 'DEFAULT '.$col['defaultValue'].' ';
			    } else {
				    $sql .= "DEFAULT '".$col['defaultValue']."' ";
			    }
		    } elseif($col['defaultValue'] != false) {
			    $sql .= "DEFAULT '' ";
		    }
387
		    if(isset($col['defaultValue']) && $col['defaultValue'] != 'NULL' && $col['defaultValue'] != 'NOT NULL') {
388 389 390 391 392 393
                if($col['notNull'] == true) {
                    $sql .= 'NOT NULL ';
                } else {
                    $sql .= 'NULL ';
                }
		    }
394
            if(isset($col['autoInc']) && $col['autoInc'] == true){ $sql .= 'auto_increment '; }
395 396
            $sql.= ',';
            //* Index Definitions
397 398 399
            if(isset($col['option']) && $col['option'] == 'primary'){ $index .= 'PRIMARY KEY ('.$col['name'].'),'; }
            if(isset($col['option']) && $col['option'] == 'index'){   $index .= 'INDEX ('.$col['name'].'),'; }
            if(isset($col['option']) && $col['option'] == 'unique'){  $index .= 'UNIQUE ('.$col['name'].'),'; }
400
        }
tbrehm's avatar
tbrehm committed
401 402
       $sql .= $index;
       $sql = substr($sql,0,-1);
403
       $sql .= ')';  
tbrehm's avatar
tbrehm committed
404 405
       $this->query($sql);
       return true;
406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430
    }
       
    /** Changes a table definition. The format for the $columns array is 
    * <code>
    * $columns = array(action =>   add | alter | drop
    *                  name =>     Spaltenname
    *                 name_new => neuer Spaltenname, nur bei 'alter' belegt
    *                 type =>     42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
    *                 typeValue => Wert z.B. bei Varchar
    *                 defaultValue =>  Default Wert
    *                 notNull =>   true | false
    *                 autoInc =>   true | false
    *                 option =>   unique | primary | index)
    */
    public function alterTable($table_name,$columns)
    {
        $index = '';
        $sql = "ALTER TABLE $table_name ";
        foreach($columns as $col){
            if($col['action'] == 'add'){
                $sql .= 'ADD '.$col['name'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
            }elseif($col['action'] == 'alter') {
                $sql .= 'CHANGE '.$col['name'].' '.$col['name_new'].' '.$this->mapType($col['type'],$col['typeValue']).' ';
            }elseif($col['action'] == 'drop') {
                $sql .= 'DROP '.$col['name'].' ';
tbrehm's avatar
tbrehm committed
431 432 433 434 435 436 437 438 439 440
            }
            if($col["action"] != 'drop') {  
            if($col["defaultValue"] != "") $sql .= "DEFAULT '".$col["defaultValue"]."' ";
            if($col["notNull"] == true) {
                $sql .= "NOT NULL ";
            } else {
                $sql .= "NULL ";
            }
            if($col["autoInc"] == true) $sql .= "auto_increment ";
            $sql.= ",";
441 442 443 444
            //* Index definitions
            if($col['option'] == 'primary') $index .= 'PRIMARY KEY ('.$col['name'].'),';
            if($col['option'] == 'index') $index .= 'INDEX ('.$col['name'].'),';
            if($col['option'] == 'unique') $index .= 'UNIQUE ('.$col['name'].'),';
tbrehm's avatar
tbrehm committed
445
            }
446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466
        }
        $sql .= $index;
        $sql = substr($sql,0,-1);
        //die($sql);
        $this->query($sql);
        return true;
    }
       
    public function dropTable($table_name) 
    {
        $this->check($table_name);
        $sql = "DROP TABLE '". $table_name."'";
        return $this->query($sql);
    }
       
    /** Return an array of table names */
    public function getTables($database_name = '')
    {
		if($database_name == ''){
            $database_name = $this->dbName;
        }
467
        $result = @mysql_list_tables($database_name);
468
        $tb_names = array();
469 470
        for ($i = 0; $i < @mysql_num_rows($result); $i++) {
            $tb_names[$i] = @mysql_tablename($result, $i);
471 472 473
        }
        return $tb_names;       
    }
tbrehm's avatar
tbrehm committed
474 475
       
       
476 477 478 479
    public function tableInfo($table_name) {
        global $go_api,$go_info;
        //* Tabellenfelder einlesen ?
        if($rows = $go_api->db->queryAllRecords("SHOW FIELDS FROM $table_name")){
tbrehm's avatar
tbrehm committed
480
        foreach($rows as $row) {
481
            $name    = $row[0];
tbrehm's avatar
tbrehm committed
482
            $default = $row[4];
483 484 485 486
            $key     = $row[3];
            $extra   = $row[5];
            $isnull  = $row[2];
            $type    = $row[1];
tbrehm's avatar
tbrehm committed
487
        
488
            $column = array('name' => $name, 'defaultValue' => $default);
tbrehm's avatar
tbrehm committed
489
            //$column["type"] = $type;
490 491 492
            if(stristr($key, 'PRI')){ $column['option'] = 'primary'; }
            $column['notNull'] = stristr($isnull,'YES') ? false : true;
            if($extra == 'auto_increment'){ $column['autoInc'] = true; }         
tbrehm's avatar
tbrehm committed
493
            
494 495 496 497
            //* Get the Data and Metatype
            if( stristr($type, 'int(') ){    $metaType = 'int32'; }
            if( stristr($type, 'bigint') ){  $metaType = 'int64'; }
            if( stristr($type, 'char') ) {
tbrehm's avatar
tbrehm committed
498 499
                $metaType = 'char';
                $tmp_typeValue = explode('(',$type);
500
                $column['typeValue'] = substr($tmp_typeValue[1], 0, -1);  
tbrehm's avatar
tbrehm committed
501
            }
502
            if( stristr($type, 'varchar') ){
tbrehm's avatar
tbrehm committed
503 504
                $metaType = 'varchar';
                $tmp_typeValue = explode('(',$type);
505
                $column['typeValue'] = substr($tmp_typeValue[1], 0, -1);  
tbrehm's avatar
tbrehm committed
506
            }
507 508 509
            if(stristr($type, 'text'))   $metaType = 'text';
            if(stristr($type, 'double')) $metaType = 'double';
            if(stristr($type, 'blob'))   $metaType = 'blob';
tbrehm's avatar
tbrehm committed
510
            
511 512
            $column['type'] = $metaType;
            $columns[] = $column;
tbrehm's avatar
tbrehm committed
513 514 515 516 517
        }
            return $columns;
        } else {
            return false;
        }
518
    }
tbrehm's avatar
tbrehm committed
519
       
520 521 522 523 524
    public function mapType($metaType, $typeValue) {
        //TODO: ? this is not required ?? global $go_api;
        $metaType = strtolower($metaType);
        switch ($metaType) {
        case 'int16':
tbrehm's avatar
tbrehm committed
525
            return 'smallint';
526
        case 'int32':
tbrehm's avatar
tbrehm committed
527
            return 'int';
528
        case 'int64':
tbrehm's avatar
tbrehm committed
529
            return 'bigint';
530
        case 'double':
tbrehm's avatar
tbrehm committed
531
            return 'double';
532
        case 'char':
tbrehm's avatar
tbrehm committed
533
            return 'char';
534 535
        case 'varchar':
            if($typeValue < 1) die('Datenbank Fehler: F�r diesen Datentyp ist eine L�ngenangabe notwendig.');
tbrehm's avatar
tbrehm committed
536
            return 'varchar('.$typeValue.')';
537
        case 'text':
tbrehm's avatar
tbrehm committed
538
            return 'text';
539
        case 'blob':
tbrehm's avatar
tbrehm committed
540
            return 'blob';
541 542 543 544
        }
    }
	
}
tbrehm's avatar
tbrehm committed
545 546

?>