gitlabFork/ISPConfig3.git - Solinfo Gitblit

Marius Cramer

2015-05-21 7de1b7b5971c74d5b251501b27ea7919e80db923

commit \| author \| age
87a4a6	1	<?php
T	2	/*
f5b0ca	3	Copyright (c) 2005, Till Brehm, projektfarm Gmbh
N	4	All rights reserved.
87a4a6	5
f5b0ca	6	Redistribution and use in source and binary forms, with or without modification,
N	7	are permitted provided that the following conditions are met:
87a4a6	8
f5b0ca	9	* Redistributions of source code must retain the above copyright notice,
N	10	this list of conditions and the following disclaimer.
	11	* Redistributions in binary form must reproduce the above copyright notice,
	12	this list of conditions and the following disclaimer in the documentation
	13	and/or other materials provided with the distribution.
	14	* Neither the name of ISPConfig nor the names of its contributors
	15	may be used to endorse or promote products derived from this software without
	16	specific prior written permission.
87a4a6	17
f5b0ca	18	THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
N	19	ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
	20	WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
	21	IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
	22	INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
	23	BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
	24	DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
	25	OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
	26	NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
	27	EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
	28	*/
87a4a6	29
f5b0ca	30	class db extends mysqli
N	31	{
cd8bb8	32	/**#@+
MC	33	* @access private
	34	*/
	35	private $_iQueryId;
	36	private $_iConnId;
	37
	38	private $dbHost = ''; // hostname of the MySQL server
82e9b9	39	private $dbPort = ''; // port of the MySQL server
b1a6a5	40	private $dbName = ''; // logical database name on that server
MC	41	private $dbUser = ''; // database authorized user
	42	private $dbPass = ''; // user's password
	43	private $dbCharset = 'utf8';// Database charset
	44	private $dbNewLink = false; // Return a new linkID when connect is called again
	45	private $dbClientFlags = 0; // MySQL Client falgs
cd8bb8	46	/*#@-/
MC	47
	48	public $show_error_messages = false; // false in server, true in interface
	49
	50
	51	/* old things - unused now ////
b1a6a5	52	private $linkId = 0; // last result of mysqli_connect()
MC	53	private $queryId = 0; // last result of mysqli_query()
	54	private $record = array(); // last record fetched
	55	private $autoCommit = 1; // Autocommit Transactions
	56	private $currentRow; // current row number
	57	public $errorNumber = 0; // last error number
8d3466	58	*/
b1a6a5	59	public $errorMessage = ''; // last error message
8d3466	60	/*
b1a6a5	61	private $errorLocation = '';// last error location
MC	62	private $isConnected = false; // needed to know if we have a valid mysqli object from the constructor
cd8bb8	63	////
MC	64	*/
87a4a6	65
b1a6a5	66	// constructor
82e9b9	67	public function __construct($host = NULL , $user = NULL, $pass = NULL, $database = NULL, $port = NULL) {
b1a6a5	68	global $app, $conf;
1d8f7f	69
b1a6a5	70	$this->dbHost = $host ? $host : $conf['db_host'];
82e9b9	71	$this->dbPort = $port ? $port : $conf['db_port'];
b1a6a5	72	$this->dbName = $database ? $database : $conf['db_database'];
MC	73	$this->dbUser = $user ? $user : $conf['db_user'];
	74	$this->dbPass = $pass ? $pass : $conf['db_password'];
	75	$this->dbCharset = $conf['db_charset'];
	76	$this->dbNewLink = $conf['db_new_link'];
	77	$this->dbClientFlags = $conf['db_client_flags'];
1d8f7f	78
7de1b7	79	$this->_iConnId = mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass, '', (int)$this->dbPort);
b1a6a5	80	$try = 0;
cd8bb8	81	while((!is_object($this->_iConnId) \|\| mysqli_connect_error()) && $try < 5) {
MC	82	if($try > 0) sleep(1);
f5b0ca	83
b1a6a5	84	$try++;
7de1b7	85	$this->_iConnId = mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass, '', (int)$this->dbPort);
b1a6a5	86	}
f5b0ca	87
cd8bb8	88	if(!is_object($this->_iConnId) \|\| mysqli_connect_error()) {
MC	89	$this->_iConnId = null;
	90	$this->_sqlerror('Zugriff auf Datenbankserver fehlgeschlagen! / Database server not accessible!');
	91	return false;
	92	}
ccfc84	93	if(!((bool)mysqli_query( $this->_iConnId, 'USE `' . $this->dbName . '`'))) {
cd8bb8	94	$this->close();
MC	95	$this->_sqlerror('Datenbank nicht gefunden / Database not found');
	96	return false;
	97	}
f5b0ca	98
cd8bb8	99	$this->_setCharset();
b1a6a5	100	}
MC	101
	102	public function __destruct() {
cd8bb8	103	if($this->_iConnId) mysqli_close($this->_iConnId);
MC	104	}
	105
	106	public function close() {
	107	if($this->_iConnId) mysqli_close($this->_iConnId);
	108	$this->_iConnId = null;
b1a6a5	109	}
MC	110
	111	/* This allows our private variables to be "read" out side of the class */
	112	public function __get($var) {
	113	return isset($this->$var) ? $this->$var : NULL;
	114	}
	115
cd8bb8	116	public function _build_query_string($sQuery = '') {
MC	117	$iArgs = func_num_args();
	118	if($iArgs > 1) {
	119	$aArgs = func_get_args();
b1a6a5	120
cd8bb8	121	if($iArgs == 3 && $aArgs[1] === true && is_array($aArgs[2])) {
MC	122	$aArgs = $aArgs[2];
	123	$iArgs = count($aArgs);
	124	} else {
	125	array_shift($aArgs); // delete the query string that is the first arg!
	126	}
f5b0ca	127
cd8bb8	128	$iPos = 0;
MC	129	$iPos2 = 0;
	130	foreach($aArgs as $sKey => $sValue) {
	131	$iPos2 = strpos($sQuery, '??', $iPos2);
	132	$iPos = strpos($sQuery, '?', $iPos);
	133
	134	if($iPos === false && $iPos2 === false) break;
	135
	136	if($iPos2 !== false && ($iPos === false \|\| $iPos2 <= $iPos)) {
	137	$sTxt = $this->escape($sValue);
	138
d7fe4d	139	if(strpos($sTxt, '.') !== false) {
MC	140	$sTxt = preg_replace('/^(.+)\.(.+)$/', '`$1`.`$2`', $sTxt);
	141	$sTxt = str_replace('.``', '.', $sTxt);
	142	} else $sTxt = '`' . $sTxt . '`';
cd8bb8	143
MC	144	$sQuery = substr_replace($sQuery, $sTxt, $iPos2, 2);
	145	$iPos2 += strlen($sTxt);
	146	$iPos = $iPos2;
b1a6a5	147	} else {
cd8bb8	148	if(is_int($sValue) \|\| is_float($sValue)) {
MC	149	$sTxt = $sValue;
3a11d2	150	} elseif(is_null($sValue) \|\| (is_string($sValue) && (strcmp($sValue, '#NULL#') == 0))) {
cd8bb8	151	$sTxt = 'NULL';
MC	152	} elseif(is_array($sValue)) {
3a11d2	153	if(isset($sValue['SQL'])) {
MC	154	$sTxt = $sValue['SQL'];
	155	} else {
	156	$sTxt = '';
	157	foreach($sValue as $sVal) $sTxt .= ',\'' . $this->escape($sVal) . '\'';
	158	$sTxt = '(' . substr($sTxt, 1) . ')';
	159	if($sTxt == '()') $sTxt = '(0)';
	160	}
cd8bb8	161	} else {
MC	162	$sTxt = '\'' . $this->escape($sValue) . '\'';
	163	}
	164
	165	$sQuery = substr_replace($sQuery, $sTxt, $iPos, 1);
	166	$iPos += strlen($sTxt);
	167	$iPos2 = $iPos;
	168	}
b1a6a5	169	}
MC	170	}
cd8bb8	171
MC	172	return $sQuery;
f5b0ca	173	}
b1a6a5	174
cd8bb8	175	/*#@-/
MC	176
	177
	178	/**#@+
	179	* @access private
	180	*/
	181	private function _setCharset() {
	182	mysqli_query($this->_iConnId, 'SET NAMES '.$this->dbCharset);
	183	mysqli_query($this->_iConnId, "SET character_set_results = '".$this->dbCharset."', character_set_client = '".$this->dbCharset."', character_set_connection = '".$this->dbCharset."', character_set_database = '".$this->dbCharset."', character_set_server = '".$this->dbCharset."'");
f5b0ca	184	}
N	185
cd8bb8	186	private function _query($sQuery = '') {
MC	187	global $app;
	188
a4a4ea	189	//if($this->isConnected == false) return false;
cd8bb8	190	if ($sQuery == '') {
MC	191	$this->_sqlerror('Keine Anfrage angegeben / No query given');
	192	return false;
	193	}
	194
b1a6a5	195	$try = 0;
MC	196	do {
	197	$try++;
cd8bb8	198	$ok = mysqli_ping($this->_iConnId);
b1a6a5	199	if(!$ok) {
82e9b9	200	if(!mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass, $this->dbName, (int)$this->dbPort)) {
132081	201	if($this->errorNumber == '111') {
MC	202	// server is not available
	203	if($try > 9) {
	204	if(isset($app) && isset($app->forceErrorExit)) {
	205	$app->forceErrorExit('Database connection failure!');
	206	}
	207	// if we reach this, the app object is missing or has no exit method, so we continue as normal
	208	}
	209	sleep(30); // additional seconds, please!
	210	}
	211
b1a6a5	212	if($try > 9) {
cd8bb8	213	$this->_sqlerror('DB::query -> reconnect');
b1a6a5	214	return false;
MC	215	} else {
	216	sleep(($try > 7 ? 5 : 1));
	217	}
	218	} else {
cd8bb8	219	$this->_setCharset();
b1a6a5	220	$ok = true;
MC	221	}
	222	}
	223	} while($ok == false);
cd8bb8	224
MC	225	$aArgs = func_get_args();
	226	$sQuery = call_user_func_array(array(&$this, '_build_query_string'), $aArgs);
	227
	228	$this->_iQueryId = mysqli_query($this->_iConnId, $sQuery);
	229	if (!$this->_iQueryId) {
	230	$this->_sqlerror('Falsche Anfrage / Wrong Query', false, 'SQL-Query = ' . $sQuery);
b1a6a5	231	return false;
MC	232	}
cd8bb8	233
MC	234	return is_bool($this->_iQueryId) ? $this->_iQueryId : new db_result($this->_iQueryId, $this->_iConnId);
b1a6a5	235	}
f5b0ca	236
cd8bb8	237	/*#@-/
MC	238
	239
	240
	241
	242
	243	/**
	244	* Executes a query
	245	*
	246	* Executes a given query string, has a variable amount of parameters:
	247	* - 1 parameter
	248	* executes the given query
	249	* - 2 parameters
	250	* executes the given query, replaces the first ? in the query with the second parameter
	251	* - 3 parameters
	252	* if the 2nd parameter is a boolean true, the 3rd parameter has to be an array containing all the replacements for every occuring ? in the query, otherwise the second parameter replaces the first ?, the third parameter replaces the second ? in the query
	253	* - 4 or more parameters
	254	* all ? in the query are replaced from left to right by the parameters 2 to x
	255	*
	256	* @access public
	257	* @param string $sQuery query string
	258	* @param mixed ... one or more parameters
	259	* @return db_result the result object of the query
	260	*/
	261
	262
	263	public function query($sQuery = '') {
	264	$aArgs = func_get_args();
	265	return call_user_func_array(array(&$this, '_query'), $aArgs);
b1a6a5	266	}
43b345	267
cd8bb8	268	/**
MC	269	* Execute a query and get first result array
	270	*
	271	* Executes a query and returns the first result row as an array
	272	* This is like calling $result = $db->query(), $result->get(), $result->free()
	273	* Use of this function @see query
	274	*
	275	* @access public
	276	* @param string $sQuery query to execute
	277	* @param ... further params (see query())
	278	* @return array result row or NULL if none found
	279	*/
	280	public function queryOneRecord($sQuery = '') {
	281	if(!preg_match('/limit \d+\s,\s\d+$/i', $sQuery)) $sQuery .= ' LIMIT 0,1';
	282
	283	$aArgs = func_get_args();
	284	$oResult = call_user_func_array(array(&$this, 'query'), $aArgs);
	285	if(!$oResult) return null;
	286
	287	$aReturn = $oResult->get();
	288	$oResult->free();
	289
	290	return $aReturn;
b1a6a5	291	}
21c641	292
cd8bb8	293	public function queryOne($sQuery = '') {
7ae5b0	294	return call_user_func_array(array(&$this, 'queryOneRecord'), func_get_args());
b1a6a5	295	}
f5b0ca	296
cd8bb8	297	public function query_one($sQuery = '') {
7ae5b0	298	return call_user_func_array(array(&$this, 'queryOneRecord'), func_get_args());
cd8bb8	299	}
MC	300
	301	/**
	302	* Execute a query and return all rows
	303	*
	304	* Executes a query and returns all result rows in an array
	305	* <strong>Use this with extreme care!!!</strong> Uses lots of memory on big result sets.
	306	*
	307	* @access public
	308	* @param string $sQuery query to execute
	309	* @param ... further params (see query())
	310	* @return array all the rows in the result set
	311	*/
	312	public function queryAllRecords($sQuery = '') {
	313	$aArgs = func_get_args();
	314	$oResult = call_user_func_array(array(&$this, 'query'), $aArgs);
	315	if(!$oResult) return array();
	316
	317	$aResults = array();
	318	while($aRow = $oResult->get()) {
	319	$aResults[] = $aRow;
	320	}
	321	$oResult->free();
	322
	323	return $aResults;
	324	}
	325
	326	public function queryAll($sQuery = '') {
7ae5b0	327	return call_user_func_array(array(&$this, 'queryAllRecords'), func_get_args());
cd8bb8	328	}
MC	329
	330	public function query_all($sQuery = '') {
7ae5b0	331	return call_user_func_array(array(&$this, 'queryAllRecords'), func_get_args());
cd8bb8	332	}
MC	333
	334	/**
	335	* Execute a query and return all rows as simple array
	336	*
	337	* Executes a query and returns all result rows in an array with elements
	338	* <strong>Only first column is returned</strong> Uses lots of memory on big result sets.
	339	*
	340	* @access public
	341	* @param string $sQuery query to execute
	342	* @param ... further params (see query())
	343	* @return array all the rows in the result set
	344	*/
	345	public function queryAllArray($sQuery = '') {
	346	$aArgs = func_get_args();
	347	$oResult = call_user_func_array(array(&$this, 'query'), $aArgs);
	348	if(!$oResult) return array();
	349
	350	$aResults = array();
	351	while($aRow = $oResult->get()) {
	352	$aResults[] = reset($aRow);
	353	}
	354	$oResult->free();
	355
	356	return $aResults;
	357	}
	358
	359	public function query_all_array($sQuery = '') {
7ae5b0	360	return call_user_func_array(array(&$this, 'queryAllArray'), func_get_args());
cd8bb8	361	}
MC	362
	363
	364
	365	/**
	366	* Get id of last inserted row
	367	*
	368	* Gives you the id of the last inserted row in a table with an auto-increment primary key
	369	*
	370	* @access public
	371	* @return int id of last inserted row or 0 if none
	372	*/
	373	public function insert_id() {
	374	$iRes = mysqli_query($this->_iConnId, 'SELECT LAST_INSERT_ID() as `newid`');
	375	if(!is_object($iRes)) return false;
	376
	377	$aReturn = mysqli_fetch_assoc($iRes);
	378	mysqli_free_result($iRes);
	379
	380	return $aReturn['newid'];
	381	}
	382
	383
	384
	385	/**
	386	* get affected row count
	387	*
	388	* Gets the amount of rows affected by the previous query
	389	*
	390	* @access public
	391	* @return int affected rows
	392	*/
	393	public function affected() {
	394	if(!is_object($this->_iConnId)) return 0;
	395	$iRows = mysqli_affected_rows($this->_iConnId);
	396	if(!$iRows) $iRows = 0;
	397	return $iRows;
	398	}
	399
	400
	401
	402	/**
385f54	403	* check if a utf8 string is valid
MC	404	*
	405	* @access public
	406	* @param string $string the string to check
	407	* @return bool true if it is valid utf8, false otherwise
	408	*/
	409	private function check_utf8($str) {
	410	$len = strlen($str);
	411	for($i = 0; $i < $len; $i++){
	412	$c = ord($str[$i]);
	413	if ($c > 128) {
	414	if (($c > 247)) return false;
	415	elseif ($c > 239) $bytes = 4;
	416	elseif ($c > 223) $bytes = 3;
	417	elseif ($c > 191) $bytes = 2;
	418	else return false;
	419	if (($i + $bytes) > $len) return false;
	420	while ($bytes > 1) {
	421	$i++;
	422	$b = ord($str[$i]);
	423	if ($b < 128 \|\| $b > 191) return false;
	424	$bytes--;
	425	}
	426	}
	427	}
	428	return true;
	429	} // end of check_utf8
	430
	431	/**
cd8bb8	432	* Escape a string for usage in a query
MC	433	*
	434	* @access public
	435	* @param string $sString query string to escape
	436	* @return string escaped string
	437	*/
	438	public function escape($sString) {
	439	global $app;
	440	if(!is_string($sString) && !is_numeric($sString)) {
	441	$app->log('NON-String given in escape function! (' . gettype($sString) . ')', LOGLEVEL_INFO);
563649	442	//$sAddMsg = getDebugBacktrace();
cd8bb8	443	$app->log($sAddMsg, LOGLEVEL_DEBUG);
MC	444	$sString = '';
	445	}
	446
385f54	447	$cur_encoding = mb_detect_encoding($sString);
cd8bb8	448	if($cur_encoding != "UTF-8") {
MC	449	if($cur_encoding != 'ASCII') {
385f54	450	$app->log('String ' . substr($sString, 0, 25) . '... is ' . $cur_encoding . '.', LOGLEVEL_INFO);
cd8bb8	451	if($cur_encoding) $sString = mb_convert_encoding($sString, 'UTF-8', $cur_encoding);
MC	452	else $sString = mb_convert_encoding($sString, 'UTF-8');
	453	}
385f54	454	} elseif(!$this->check_utf8($sString)) {
cd8bb8	455	$sString = utf8_encode($sString);
385f54	456	}
cd8bb8	457
MC	458	if($this->_iConnId) return mysqli_real_escape_string($this->_iConnId, $sString);
	459	else return addslashes($sString);
	460	}
	461
	462	/**
	463	*
	464	*
	465	* @access private
	466	*/
	467	private function _sqlerror($sErrormsg = 'Unbekannter Fehler', $sAddMsg = '') {
	468	global $app, $conf;
	469
	470	$mysql_error = (is_object($this->_iConnId) ? mysqli_error($this->_iConnId) : mysqli_connect_error());
	471	$mysql_errno = (is_object($this->_iConnId) ? mysqli_errno($this->_iConnId) : mysqli_connect_errno());
8d3466	472	$this->errorMessage = $mysql_error;
cd8bb8	473
563649	474	//$sAddMsg .= getDebugBacktrace();
cd8bb8	475
MC	476	if($this->show_error_messages && $conf['demo_mode'] === false) {
	477	echo $sErrormsg . $sAddMsg;
	478	} else if(is_object($app) && method_exists($app, 'log')) {
a1918e	479	$app->log($sErrormsg . $sAddMsg . ' -> ' . $mysql_errno . ' (' . $mysql_error . ')', LOGLEVEL_WARN);
cd8bb8	480	}
b1a6a5	481	}
MC	482
	483	public function affectedRows() {
cd8bb8	484	return $this->affected();
b1a6a5	485	}
MC	486
	487	// returns mySQL insert id
	488	public function insertID() {
cd8bb8	489	return $this->insert_id();
b1a6a5	490	}
MC	491
	492
cd8bb8	493	//* Function to quote strings
b1a6a5	494	public function quote($formfield) {
cd8bb8	495	return $this->escape($formfield);
b1a6a5	496	}
MC	497
cd8bb8	498	//* Function to unquotae strings
b1a6a5	499	public function unquote($formfield) {
MC	500	return stripslashes($formfield);
	501	}
	502
	503	public function toLower($record) {
	504	if(is_array($record)) {
	505	foreach($record as $key => $val) {
	506	$key = strtolower($key);
	507	$out[$key] = $val;
	508	}
	509	}
	510	return $out;
	511	}
	512
	513	public function diffrec($record_old, $record_new) {
	514	$diffrec_full = array();
	515	$diff_num = 0;
	516
	517	if(is_array($record_old) && count($record_old) > 0) {
	518	foreach($record_old as $key => $val) {
	519	// if(!isset($record_new[$key]) \|\| $record_new[$key] != $val) {
	520	if(@$record_new[$key] != $val) {
	521	// Record has changed
	522	$diffrec_full['old'][$key] = $val;
	523	$diffrec_full['new'][$key] = @$record_new[$key];
	524	$diff_num++;
	525	} else {
	526	$diffrec_full['old'][$key] = $val;
	527	$diffrec_full['new'][$key] = $val;
	528	}
	529	}
	530	} elseif(is_array($record_new)) {
	531	foreach($record_new as $key => $val) {
	532	if(isset($record_new[$key]) && @$record_old[$key] != $val) {
	533	// Record has changed
	534	$diffrec_full['new'][$key] = $val;
	535	$diffrec_full['old'][$key] = @$record_old[$key];
	536	$diff_num++;
	537	} else {
	538	$diffrec_full['new'][$key] = $val;
	539	$diffrec_full['old'][$key] = $val;
	540	}
	541	}
	542	}
	543
	544	return array('diff_num' => $diff_num, 'diff_rec' => $diffrec_full);
	545
	546	}
	547
	548	/**
	549	* Function to get the database-size
	550	* @param string $database_name
	551	* @return int - database-size in bytes
	552	*/
	553
	554
	555	public function getDatabaseSize($database_name) {
	556	global $app;
	557	include 'lib/mysql_clientdb.conf';
	558	/* Connect to the database */
	559	$link = mysql_connect($clientdb_host, $clientdb_user, $clientdb_password);
	560	if (!$link) {
	561	$app->log('Unable to connect to the database'.mysql_error($link), LOGLEVEL_DEBUG);
	562	return;
	563	}
	564	/* Get database-size from information_schema */
	565	$result=mysql_query("SELECT SUM(data_length+index_length) FROM information_schema.TABLES WHERE table_schema='".mysql_real_escape_string($database_name)."';", $link);
	566	$this->close;
	567	if (!$result) {
	568	$app->log('Unable to get the database-size'.mysql_error($link), LOGLEVEL_DEBUG);
	569	return;
	570	}
	571	$database_size = mysql_fetch_row($result);
	572	return $database_size[0];
	573	}
	574
	575	//** Function to fill the datalog with a full differential record.
	576	public function datalogSave($db_table, $action, $primary_field, $primary_id, $record_old, $record_new, $force_update = false) {
	577	global $app, $conf;
	578
	579	// Insert backticks only for incomplete table names.
	580	if(stristr($db_table, '.')) {
	581	$escape = '';
	582	} else {
	583	$escape = '`';
	584	}
f5b0ca	585
43b345	586	if($force_update == true) {
T	587	//* We force a update even if no record has changed
b1a6a5	588	$diffrec_full = array('new' => $record_new, 'old' => $record_old);
43b345	589	$diff_num = count($record_new);
T	590	} else {
	591	//* get the difference record between old and new record
	592	$tmp = $this->diffrec($record_old, $record_new);
	593	$diffrec_full = $tmp['diff_rec'];
	594	$diff_num = $tmp['diff_num'];
	595	unset($tmp);
	596	}
f5b0ca	597
b1a6a5	598	// Insert the server_id, if the record has a server_id
MC	599	$server_id = (isset($record_old['server_id']) && $record_old['server_id'] > 0)?$record_old['server_id']:0;
	600	if(isset($record_new['server_id'])) $server_id = $record_new['server_id'];
f5b0ca	601
N	602
b1a6a5	603	if($diff_num > 0) {
cd8bb8	604	$diffstr = serialize($diffrec_full);
b1a6a5	605	if(isset($_SESSION)) {
cd8bb8	606	$username = $_SESSION['s']['user']['username'];
b1a6a5	607	} else {
MC	608	$username = 'admin';
	609	}
	610	$dbidx = $primary_field.':'.$primary_id;
	611
	612	if($action == 'INSERT') $action = 'i';
	613	if($action == 'UPDATE') $action = 'u';
	614	if($action == 'DELETE') $action = 'd';
cd8bb8	615	$sql = "INSERT INTO sys_datalog (dbtable,dbidx,server_id,action,tstamp,user,data) VALUES (?, ?, ?, ?, ?, ?, ?)";
MC	616	$app->db->query($sql, $db_table, $dbidx, $server_id, $action, time(), $username, $diffstr);
346f60	617	}
f5b0ca	618
b1a6a5	619	return true;
346f60	620	}
f5b0ca	621
b1a6a5	622	//** Inserts a record and saves the changes into the datalog
MC	623	public function datalogInsert($tablename, $insert_data, $index_field) {
	624	global $app;
f5b0ca	625
b1a6a5	626	if(is_array($insert_data)) {
43b345	627	$key_str = '';
T	628	$val_str = '';
2af58c	629	$params = array($tablename);
MC	630	$v_params = array();
43b345	631	foreach($insert_data as $key => $val) {
a6e3ae	632	$key_str .= '??,';
2af58c	633	$params[] = $key;
MC	634
	635	$val_str .= '?,';
	636	$v_params[] = $val;
43b345	637	}
b1a6a5	638	$key_str = substr($key_str, 0, -1);
MC	639	$val_str = substr($val_str, 0, -1);
43b345	640	$insert_data_str = '('.$key_str.') VALUES ('.$val_str.')';
2af58c	641	$this->query("INSERT INTO ?? $insert_data_str", true, $params + $v_params);
43b345	642	} else {
2af58c	643	/* TODO: deprecate this method! */
43b345	644	$insert_data_str = $insert_data;
2af58c	645	$this->query("INSERT INTO ?? $insert_data_str", $tablename);
3a11d2	646	$app->log("deprecated use of passing values to datalogInsert() - table " . $tablename, 1);
43b345	647	}
2af58c	648
b1a6a5	649	$old_rec = array();
MC	650	$index_value = $this->insertID();
cd8bb8	651	$new_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ? = ?", $tablename, $index_field, $index_value);
b1a6a5	652	$this->datalogSave($tablename, 'INSERT', $index_field, $index_value, $old_rec, $new_rec);
f5b0ca	653
b1a6a5	654	return $index_value;
MC	655	}
f5b0ca	656
b1a6a5	657	//** Updates a record and saves the changes into the datalog
MC	658	public function datalogUpdate($tablename, $update_data, $index_field, $index_value, $force_update = false) {
43b345	659	global $app;
b1a6a5	660
cd8bb8	661	$old_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
b1a6a5	662
MC	663	if(is_array($update_data)) {
2af58c	664	$params = array($tablename);
43b345	665	$update_data_str = '';
T	666	foreach($update_data as $key => $val) {
2af58c	667	$update_data_str .= '?? = ?,';
MC	668	$params[] = $key;
	669	$params[] = $val;
43b345	670	}
2af58c	671	$params[] = $index_field;
MC	672	$params[] = $index_value;
b1a6a5	673	$update_data_str = substr($update_data_str, 0, -1);
2af58c	674	$this->query("UPDATE ?? SET $update_data_str WHERE ?? = ?", true, $params);
43b345	675	} else {
2af58c	676	/* TODO: deprecate this method! */
43b345	677	$update_data_str = $update_data;
2af58c	678	$this->query("UPDATE ?? SET $update_data_str WHERE ?? = ?", $tablename, $index_field, $index_value);
3a11d2	679	$app->log("deprecated use of passing values to datalogUpdate() - table " . $tablename, 1);
43b345	680	}
f5b0ca	681
cd8bb8	682	$new_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
b1a6a5	683	$this->datalogSave($tablename, 'UPDATE', $index_field, $index_value, $old_rec, $new_rec, $force_update);
f5b0ca	684
b1a6a5	685	return true;
MC	686	}
f5b0ca	687
7b47c0	688	//** Deletes a record and saves the changes into the datalog
b1a6a5	689	public function datalogDelete($tablename, $index_field, $index_value) {
MC	690	global $app;
7b47c0	691
cd8bb8	692	$old_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
MC	693	$this->query("DELETE FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
b1a6a5	694	$new_rec = array();
MC	695	$this->datalogSave($tablename, 'DELETE', $index_field, $index_value, $old_rec, $new_rec);
	696
	697	return true;
	698	}
	699
	700	//** Deletes a record and saves the changes into the datalog
	701	public function datalogError($errormsg) {
	702	global $app;
	703
2af58c	704	if(isset($app->modules->current_datalog_id) && $app->modules->current_datalog_id > 0) $this->query("UPDATE sys_datalog set error = ? WHERE datalog_id = ?", $errormsg, $app->modules->current_datalog_id);
b1a6a5	705
MC	706	return true;
	707	}
f5b0ca	708
N	709
b1a6a5	710	public function freeResult($query)
MC	711	{
	712	if(is_object($query) && (get_class($query) == "mysqli_result")) {
	713	$query->free();
	714	return true;
	715	} else {
	716	return false;
	717	}
	718	}
f5b0ca	719
b1a6a5	720	/*
f5b0ca	721	$columns = array(action => add \| alter \| drop
N	722	name => Spaltenname
	723	name_new => neuer Spaltenname, nur bei 'alter' belegt
	724	type => 42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
	725	typeValue => Wert z.B. bei Varchar
	726	defaultValue => Default Wert
	727	notNull => true \| false
	728	autoInc => true \| false
	729	option => unique \| primary \| index)
	730
	731
	732	*/
	733
b1a6a5	734	public function createTable($table_name, $columns) {
MC	735	$index = '';
cd8bb8	736	$sql = "CREATE TABLE ?? (";
b1a6a5	737	foreach($columns as $col){
MC	738	$sql .= $col['name'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
f5b0ca	739
b1a6a5	740	if($col['defaultValue'] != '') $sql .= "DEFAULT '".$col['defaultValue']."' ";
MC	741	if($col['notNull'] == true) {
	742	$sql .= 'NOT NULL ';
	743	} else {
	744	$sql .= 'NULL ';
	745	}
	746	if($col['autoInc'] == true) $sql .= 'auto_increment ';
	747	$sql.= ',';
	748	// key Definitionen
	749	if($col['option'] == 'primary') $index .= 'PRIMARY KEY ('.$col['name'].'),';
	750	if($col['option'] == 'index') $index .= 'INDEX ('.$col['name'].'),';
	751	if($col['option'] == 'unique') $index .= 'UNIQUE ('.$col['name'].'),';
	752	}
	753	$sql .= $index;
	754	$sql = substr($sql, 0, -1);
	755	$sql .= ')';
cd8bb8	756	/* TODO: secure parameters */
MC	757	$this->query($sql, $table_name);
b1a6a5	758	return true;
f5b0ca	759	}
N	760
b1a6a5	761	/*
f5b0ca	762	$columns = array(action => add \| alter \| drop
N	763	name => Spaltenname
	764	name_new => neuer Spaltenname, nur bei 'alter' belegt
	765	type => 42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
	766	typeValue => Wert z.B. bei Varchar
	767	defaultValue => Default Wert
	768	notNull => true \| false
	769	autoInc => true \| false
	770	option => unique \| primary \| index)
	771
	772
	773	*/
b1a6a5	774	public function alterTable($table_name, $columns) {
MC	775	$index = '';
cd8bb8	776	$sql = "ALTER TABLE ?? ";
b1a6a5	777	foreach($columns as $col){
MC	778	if($col['action'] == 'add') {
	779	$sql .= 'ADD '.$col['name'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
	780	} elseif ($col['action'] == 'alter') {
	781	$sql .= 'CHANGE '.$col['name'].' '.$col['name_new'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
	782	} elseif ($col['action'] == 'drop') {
	783	$sql .= 'DROP '.$col['name'].' ';
	784	}
	785	if($col['action'] != 'drop') {
	786	if($col['defaultValue'] != '') $sql .= "DEFAULT '".$col['defaultValue']."' ";
	787	if($col['notNull'] == true) {
	788	$sql .= 'NOT NULL ';
	789	} else {
	790	$sql .= 'NULL ';
	791	}
	792	if($col['autoInc'] == true) $sql .= 'auto_increment ';
	793	$sql.= ',';
	794	// Index definitions
	795	if($col['option'] == 'primary') $index .= 'PRIMARY KEY ('.$col['name'].'),';
	796	if($col['option'] == 'index') $index .= 'INDEX ('.$col['name'].'),';
	797	if($col['option'] == 'unique') $index .= 'UNIQUE ('.$col['name'].'),';
	798	}
	799	}
	800	$sql .= $index;
	801	$sql = substr($sql, 0, -1);
cd8bb8	802	/* TODO: secure parameters */
b1a6a5	803	//die($sql);
cd8bb8	804	$this->query($sql, $table_name);
b1a6a5	805	return true;
f5b0ca	806	}
b1a6a5	807
MC	808	public function dropTable($table_name) {
	809	$this->check($table_name);
cd8bb8	810	$sql = "DROP TABLE ??";
MC	811	return $this->query($sql, $table_name);
f5b0ca	812	}
N	813
b1a6a5	814	// gibt Array mit Tabellennamen zur�ck
MC	815	public function getTables($database_name = '') {
cd8bb8	816	if(!is_object($this->_iConnId)) return false;
b1a6a5	817	if($database_name == '') $database_name = $this->dbName;
cd8bb8	818	$tb_names = $this->queryAllArray("SHOW TABLES FROM ??", $database_name);
b1a6a5	819	return $tb_names;
MC	820	}
f5b0ca	821
b1a6a5	822	// gibt Feldinformationen zur Tabelle zur�ck
MC	823	/*
f5b0ca	824	$columns = array(action => add \| alter \| drop
N	825	name => Spaltenname
	826	name_new => neuer Spaltenname, nur bei 'alter' belegt
	827	type => 42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
	828	typeValue => Wert z.B. bei Varchar
	829	defaultValue => Default Wert
	830	notNull => true \| false
	831	autoInc => true \| false
	832	option => unique \| primary \| index)
	833
	834
	835	*/
	836
b1a6a5	837	function tableInfo($table_name) {
f5b0ca	838
b1a6a5	839	global $go_api, $go_info;
MC	840	// Tabellenfelder einlesen
f5b0ca	841
cd8bb8	842	if($rows = $go_api->db->queryAllRecords('SHOW FIELDS FROM ??', $table_name)){
b1a6a5	843	foreach($rows as $row) {
cd8bb8	844	$name = $row['Field'];
MC	845	$default = $row['Default'];
	846	$key = $row['Key'];
	847	$extra = $row['Extra'];
	848	$isnull = $row['Null'];
	849	$type = $row['Type'];
f5b0ca	850
N	851
b1a6a5	852	$column = array();
f5b0ca	853
b1a6a5	854	$column['name'] = $name;
MC	855	//$column['type'] = $type;
	856	$column['defaultValue'] = $default;
	857	if(stristr($key, 'PRI')) $column['option'] = 'primary';
	858	if(stristr($isnull, 'YES')) {
	859	$column['notNull'] = false;
	860	} else {
	861	$column['notNull'] = true;
	862	}
	863	if($extra == 'auto_increment') $column['autoInc'] = true;
f5b0ca	864
N	865
b1a6a5	866	// Type in Metatype umsetzen
f5b0ca	867
b1a6a5	868	if(stristr($type, 'int(')) $metaType = 'int32';
MC	869	if(stristr($type, 'bigint')) $metaType = 'int64';
	870	if(stristr($type, 'char')) {
	871	$metaType = 'char';
	872	$tmp_typeValue = explode('(', $type);
	873	$column['typeValue'] = substr($tmp_typeValue[1], 0, -1);
	874	}
	875	if(stristr($type, 'varchar')) {
	876	$metaType = 'varchar';
	877	$tmp_typeValue = explode('(', $type);
	878	$column['typeValue'] = substr($tmp_typeValue[1], 0, -1);
	879	}
	880	if(stristr($type, 'text')) $metaType = 'text';
	881	if(stristr($type, 'double')) $metaType = 'double';
	882	if(stristr($type, 'blob')) $metaType = 'blob';
f5b0ca	883
N	884
b1a6a5	885	$column['type'] = $metaType;
f5b0ca	886
b1a6a5	887	$columns[] = $column;
MC	888	}
	889	return $columns;
	890	} else {
	891	return false;
	892	}
f5b0ca	893
N	894
b1a6a5	895	//$this->createTable('tester',$columns);
f5b0ca	896
b1a6a5	897	/*
f5b0ca	898	$result = mysql_list_fields($go_info["server"]["db_name"],$table_name);
N	899	$fields = mysql_num_fields ($result);
	900	$i = 0;
	901	$table = mysql_field_table ($result, $i);
	902	while ($i < $fields) {
	903	$name = mysql_field_name ($result, $i);
	904	$type = mysql_field_type ($result, $i);
	905	$len = mysql_field_len ($result, $i);
	906	$flags = mysql_field_flags ($result, $i);
	907	print_r($flags);
	908
	909	$columns = array(name => $name,
	910	type => "",
	911	defaultValue => "",
	912	isnull => 1,
	913	option => "");
	914	$returnvar[] = $columns;
	915
	916	$i++;
	917	}
	918	*/
	919
	920
	921
b1a6a5	922	}
f5b0ca	923
b1a6a5	924	public function mapType($metaType, $typeValue) {
MC	925	global $go_api;
	926	$metaType = strtolower($metaType);
	927	switch ($metaType) {
	928	case 'int16':
	929	return 'smallint';
	930	break;
	931	case 'int32':
	932	return 'int';
	933	break;
	934	case 'int64':
	935	return 'bigint';
	936	break;
	937	case 'double':
	938	return 'double';
	939	break;
	940	case 'char':
	941	return 'char';
	942	break;
	943	case 'varchar':
	944	if($typeValue < 1) die('Database failure: Lenght required for these data types.');
	945	return 'varchar('.$typeValue.')';
	946	break;
	947	case 'text':
	948	return 'text';
	949	break;
	950	case 'blob':
	951	return 'blob';
	952	break;
	953	}
	954	}
f5b0ca	955
b1a6a5	956	}
f5b0ca	957
cd8bb8	958	/**
MC	959	* database query result class
	960	*
	961	* @package pxFramework
	962	*
	963	*/
	964	class db_result {
	965
	966	/**
	967	*
	968	*
	969	* @access private
	970	*/
	971	private $_iResId = null;
	972	private $_iConnection = null;
	973
	974
	975
	976	/**
	977	*
	978	*
	979	* @access private
	980	*/
	981	public function db_result($iResId, $iConnection) {
	982	$this->_iResId = $iResId;
	983	$this->_iConnection = $iConnection;
	984	}
	985
	986
	987
	988	/**
	989	* get count of result rows
	990	*
	991	* Returns the amount of rows in the result set
	992	*
	993	* @access public
	994	* @return int amount of rows
	995	*/
	996	public function rows() {
	997	if(!is_object($this->_iResId)) return 0;
	998	$iRows = mysqli_num_rows($this->_iResId);
	999	if(!$iRows) $iRows = 0;
	1000	return $iRows;
	1001	}
	1002
	1003
	1004
	1005	/**
	1006	* Get number of affected rows
	1007	*
	1008	* Returns the amount of rows affected by the previous query
	1009	*
	1010	* @access public
	1011	* @return int amount of affected rows
	1012	*/
	1013	public function affected() {
	1014	if(!is_object($this->_iConnection)) return 0;
	1015	$iRows = mysqli_affected_rows($this->_iConnection);
	1016	if(!$iRows) $iRows = 0;
	1017	return $iRows;
	1018	}
	1019
	1020
	1021
	1022	/**
	1023	* Frees the result set
	1024	*
	1025	* @access public
	1026	*/
	1027	public function free() {
	1028	if(!is_object($this->_iResId)) return;
	1029
	1030	mysqli_free_result($this->_iResId);
	1031	return;
	1032	}
	1033
	1034
	1035
	1036	/**
	1037	* Get a result row (associative)
	1038	*
	1039	* Returns the next row in the result set. To be used in a while loop like while($currow = $result->get()) { do something ... }
	1040	*
	1041	* @access public
	1042	* @return array result row
	1043	*/
	1044	public function get() {
	1045	$aItem = null;
	1046
	1047	if(is_object($this->_iResId)) {
	1048	$aItem = mysqli_fetch_assoc($this->_iResId);
	1049	if(!$aItem) $aItem = null;
	1050	}
	1051	return $aItem;
	1052	}
	1053
	1054
	1055
	1056	/**
	1057	* Get a result row (array with numeric index)
	1058	*
	1059	* @access public
	1060	* @return array result row
	1061	*/
	1062	public function getAsRow() {
	1063	$aItem = null;
	1064
	1065	if(is_object($this->_iResId)) {
	1066	$aItem = mysqli_fetch_row($this->_iResId);
	1067	if(!$aItem) $aItem = null;
	1068	}
	1069	return $aItem;
	1070	}
	1071
	1072	}
	1073
	1074	/**
	1075	* database query result class
	1076	*
	1077	* emulates a db result set out of an array so you can use array results and db results the same way
	1078	*
	1079	* @package pxFramework
	1080	* @see db_result
	1081	*
	1082	*
	1083	*/
	1084	class fakedb_result {
	1085
	1086	/**
	1087	*
	1088	*
	1089	* @access private
	1090	*/
	1091	private $aResultData = array();
	1092
	1093	/**
	1094	*
	1095	*
	1096	* @access private
	1097	*/
	1098	private $aLimitedData = array();
	1099
	1100
	1101
	1102	/**
	1103	*
	1104	*
	1105	* @access private
	1106	*/
	1107	public function fakedb_result($aData) {
	1108	$this->aResultData = $aData;
	1109	$this->aLimitedData = $aData;
	1110	reset($this->aLimitedData);
	1111	}
	1112
	1113
	1114
	1115	/**
	1116	* get count of result rows
	1117	*
	1118	* Returns the amount of rows in the result set
	1119	*
	1120	* @access public
	1121	* @return int amount of rows
	1122	*/
	1123	// Gibt die Anzahl Zeilen zurück
	1124	public function rows() {
	1125	return count($this->aLimitedData);
	1126	}
	1127
	1128
	1129
	1130	/**
	1131	* Frees the result set
	1132	*
	1133	* @access public
	1134	*/
	1135	// Gibt ein Ergebnisset frei
	1136	public function free() {
	1137	$this->aResultData = array();
	1138	$this->aLimitedData = array();
	1139	return;
	1140	}
	1141
	1142
	1143
	1144	/**
	1145	* Get a result row (associative)
	1146	*
	1147	* Returns the next row in the result set. To be used in a while loop like while($currow = $result->get()) { do something ... }
	1148	*
	1149	* @access public
	1150	* @return array result row
	1151	*/
	1152	// Gibt eine Ergebniszeile zurück
	1153	public function get() {
	1154	$aItem = null;
	1155
	1156	if(!is_array($this->aLimitedData)) return $aItem;
	1157
	1158	if(list($vKey, $aItem) = each($this->aLimitedData)) {
	1159	if(!$aItem) $aItem = null;
	1160	}
	1161	return $aItem;
	1162	}
	1163
	1164
	1165
	1166	/**
	1167	* Get a result row (array with numeric index)
	1168	*
	1169	* @access public
	1170	* @return array result row
	1171	*/
	1172	public function getAsRow() {
	1173	return $this->get();
	1174	}
	1175
	1176
	1177
	1178	/**
	1179	* Limit the result (like a LIMIT x,y in a SQL query)
	1180	*
	1181	* @access public
	1182	* @param int $iStart offset to start read
	1183	* @param int iLength amount of datasets to read
	1184	*/
	1185	public function limit_result($iStart, $iLength) {
	1186	$this->aLimitedData = array_slice($this->aResultData, $iStart, $iLength, true);
	1187	}
	1188
	1189	}
	1190
	1191
b1a6a5	1192	?>