gitlabFork/ISPConfig3.git - Solinfo Gitblit

Marius Burkard

2016-04-20 4569cae57f127afd093794310ccd290d2d9fdf36

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) {
8c517c	230	$this->_sqlerror('Falsche Anfrage / Wrong Query', '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') {
02e7ea	450	if(is_object($app) && method_exists($app, 'log')) $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;
e6c83e	557
b1a6a5	558	include 'lib/mysql_clientdb.conf';
e6c83e	559
b1a6a5	560	/* Connect to the database */
f1926a	561	$link = mysqli_connect($clientdb_host, $clientdb_user, $clientdb_password);
b1a6a5	562	if (!$link) {
8a8cc2	563	$app->log('Unable to connect to the database'.mysqli_connect_error(), LOGLEVEL_DEBUG);
b1a6a5	564	return;
MC	565	}
e6c83e	566
b1a6a5	567	/* Get database-size from information_schema */
e6c83e	568	$result = mysqli_query($link, "SELECT SUM(data_length+index_length) FROM information_schema.TABLES WHERE table_schema='".mysqli_real_escape_string($link, $database_name)."'");
MB	569	if(!$result) {
	570	$app->log('Unable to get the database-size for ' . $database_name . ': '.mysqli_error($link), LOGLEVEL_DEBUG);
b1a6a5	571	return;
MC	572	}
f1926a	573	$database_size = mysqli_fetch_row($result);
e6c83e	574	mysqli_close($link);
b1a6a5	575	return $database_size[0];
MC	576	}
	577
	578	//** Function to fill the datalog with a full differential record.
	579	public function datalogSave($db_table, $action, $primary_field, $primary_id, $record_old, $record_new, $force_update = false) {
	580	global $app, $conf;
	581
	582	// Insert backticks only for incomplete table names.
	583	if(stristr($db_table, '.')) {
	584	$escape = '';
	585	} else {
	586	$escape = '`';
	587	}
f5b0ca	588
43b345	589	if($force_update == true) {
T	590	//* We force a update even if no record has changed
b1a6a5	591	$diffrec_full = array('new' => $record_new, 'old' => $record_old);
43b345	592	$diff_num = count($record_new);
T	593	} else {
	594	//* get the difference record between old and new record
	595	$tmp = $this->diffrec($record_old, $record_new);
	596	$diffrec_full = $tmp['diff_rec'];
	597	$diff_num = $tmp['diff_num'];
	598	unset($tmp);
	599	}
f5b0ca	600
b1a6a5	601	// Insert the server_id, if the record has a server_id
MC	602	$server_id = (isset($record_old['server_id']) && $record_old['server_id'] > 0)?$record_old['server_id']:0;
	603	if(isset($record_new['server_id'])) $server_id = $record_new['server_id'];
f5b0ca	604
N	605
b1a6a5	606	if($diff_num > 0) {
cd8bb8	607	$diffstr = serialize($diffrec_full);
b1a6a5	608	if(isset($_SESSION)) {
cd8bb8	609	$username = $_SESSION['s']['user']['username'];
b1a6a5	610	} else {
MC	611	$username = 'admin';
	612	}
	613	$dbidx = $primary_field.':'.$primary_id;
	614
	615	if($action == 'INSERT') $action = 'i';
	616	if($action == 'UPDATE') $action = 'u';
	617	if($action == 'DELETE') $action = 'd';
cd8bb8	618	$sql = "INSERT INTO sys_datalog (dbtable,dbidx,server_id,action,tstamp,user,data) VALUES (?, ?, ?, ?, ?, ?, ?)";
MC	619	$app->db->query($sql, $db_table, $dbidx, $server_id, $action, time(), $username, $diffstr);
346f60	620	}
f5b0ca	621
b1a6a5	622	return true;
346f60	623	}
f5b0ca	624
b1a6a5	625	//** Inserts a record and saves the changes into the datalog
MC	626	public function datalogInsert($tablename, $insert_data, $index_field) {
	627	global $app;
f5b0ca	628
b1a6a5	629	if(is_array($insert_data)) {
43b345	630	$key_str = '';
T	631	$val_str = '';
2af58c	632	$params = array($tablename);
MC	633	$v_params = array();
43b345	634	foreach($insert_data as $key => $val) {
a6e3ae	635	$key_str .= '??,';
2af58c	636	$params[] = $key;
MC	637
	638	$val_str .= '?,';
	639	$v_params[] = $val;
43b345	640	}
b1a6a5	641	$key_str = substr($key_str, 0, -1);
MC	642	$val_str = substr($val_str, 0, -1);
43b345	643	$insert_data_str = '('.$key_str.') VALUES ('.$val_str.')';
2c1a63	644	$this->query("INSERT INTO ?? $insert_data_str", true, array_merge($params, $v_params));
43b345	645	} else {
2af58c	646	/* TODO: deprecate this method! */
43b345	647	$insert_data_str = $insert_data;
2af58c	648	$this->query("INSERT INTO ?? $insert_data_str", $tablename);
3a11d2	649	$app->log("deprecated use of passing values to datalogInsert() - table " . $tablename, 1);
43b345	650	}
2af58c	651
b1a6a5	652	$old_rec = array();
MC	653	$index_value = $this->insertID();
bc0978	654	$new_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
b1a6a5	655	$this->datalogSave($tablename, 'INSERT', $index_field, $index_value, $old_rec, $new_rec);
f5b0ca	656
b1a6a5	657	return $index_value;
MC	658	}
f5b0ca	659
b1a6a5	660	//** Updates a record and saves the changes into the datalog
MC	661	public function datalogUpdate($tablename, $update_data, $index_field, $index_value, $force_update = false) {
43b345	662	global $app;
b1a6a5	663
cd8bb8	664	$old_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
b1a6a5	665
MC	666	if(is_array($update_data)) {
2af58c	667	$params = array($tablename);
43b345	668	$update_data_str = '';
T	669	foreach($update_data as $key => $val) {
2af58c	670	$update_data_str .= '?? = ?,';
MC	671	$params[] = $key;
	672	$params[] = $val;
43b345	673	}
2af58c	674	$params[] = $index_field;
MC	675	$params[] = $index_value;
b1a6a5	676	$update_data_str = substr($update_data_str, 0, -1);
2af58c	677	$this->query("UPDATE ?? SET $update_data_str WHERE ?? = ?", true, $params);
43b345	678	} else {
2af58c	679	/* TODO: deprecate this method! */
43b345	680	$update_data_str = $update_data;
2af58c	681	$this->query("UPDATE ?? SET $update_data_str WHERE ?? = ?", $tablename, $index_field, $index_value);
3a11d2	682	$app->log("deprecated use of passing values to datalogUpdate() - table " . $tablename, 1);
43b345	683	}
f5b0ca	684
cd8bb8	685	$new_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
b1a6a5	686	$this->datalogSave($tablename, 'UPDATE', $index_field, $index_value, $old_rec, $new_rec, $force_update);
f5b0ca	687
b1a6a5	688	return true;
MC	689	}
f5b0ca	690
7b47c0	691	//** Deletes a record and saves the changes into the datalog
b1a6a5	692	public function datalogDelete($tablename, $index_field, $index_value) {
MC	693	global $app;
7b47c0	694
cd8bb8	695	$old_rec = $this->queryOneRecord("SELECT * FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
MC	696	$this->query("DELETE FROM ?? WHERE ?? = ?", $tablename, $index_field, $index_value);
b1a6a5	697	$new_rec = array();
MC	698	$this->datalogSave($tablename, 'DELETE', $index_field, $index_value, $old_rec, $new_rec);
	699
	700	return true;
	701	}
	702
	703	//** Deletes a record and saves the changes into the datalog
	704	public function datalogError($errormsg) {
	705	global $app;
	706
2af58c	707	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	708
MC	709	return true;
	710	}
f5b0ca	711
N	712
b1a6a5	713	public function freeResult($query)
MC	714	{
	715	if(is_object($query) && (get_class($query) == "mysqli_result")) {
	716	$query->free();
	717	return true;
	718	} else {
	719	return false;
	720	}
	721	}
f5b0ca	722
b1a6a5	723	/*
f5b0ca	724	$columns = array(action => add \| alter \| drop
N	725	name => Spaltenname
	726	name_new => neuer Spaltenname, nur bei 'alter' belegt
	727	type => 42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
	728	typeValue => Wert z.B. bei Varchar
	729	defaultValue => Default Wert
	730	notNull => true \| false
	731	autoInc => true \| false
	732	option => unique \| primary \| index)
	733
	734
	735	*/
	736
b1a6a5	737	public function createTable($table_name, $columns) {
MC	738	$index = '';
cd8bb8	739	$sql = "CREATE TABLE ?? (";
b1a6a5	740	foreach($columns as $col){
MC	741	$sql .= $col['name'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
f5b0ca	742
b1a6a5	743	if($col['defaultValue'] != '') $sql .= "DEFAULT '".$col['defaultValue']."' ";
MC	744	if($col['notNull'] == true) {
	745	$sql .= 'NOT NULL ';
	746	} else {
	747	$sql .= 'NULL ';
	748	}
	749	if($col['autoInc'] == true) $sql .= 'auto_increment ';
	750	$sql.= ',';
	751	// key Definitionen
	752	if($col['option'] == 'primary') $index .= 'PRIMARY KEY ('.$col['name'].'),';
	753	if($col['option'] == 'index') $index .= 'INDEX ('.$col['name'].'),';
	754	if($col['option'] == 'unique') $index .= 'UNIQUE ('.$col['name'].'),';
	755	}
	756	$sql .= $index;
	757	$sql = substr($sql, 0, -1);
	758	$sql .= ')';
cd8bb8	759	/* TODO: secure parameters */
MC	760	$this->query($sql, $table_name);
b1a6a5	761	return true;
f5b0ca	762	}
N	763
b1a6a5	764	/*
f5b0ca	765	$columns = array(action => add \| alter \| drop
N	766	name => Spaltenname
	767	name_new => neuer Spaltenname, nur bei 'alter' belegt
	768	type => 42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
	769	typeValue => Wert z.B. bei Varchar
	770	defaultValue => Default Wert
	771	notNull => true \| false
	772	autoInc => true \| false
	773	option => unique \| primary \| index)
	774
	775
	776	*/
b1a6a5	777	public function alterTable($table_name, $columns) {
MC	778	$index = '';
cd8bb8	779	$sql = "ALTER TABLE ?? ";
b1a6a5	780	foreach($columns as $col){
MC	781	if($col['action'] == 'add') {
	782	$sql .= 'ADD '.$col['name'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
	783	} elseif ($col['action'] == 'alter') {
	784	$sql .= 'CHANGE '.$col['name'].' '.$col['name_new'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
	785	} elseif ($col['action'] == 'drop') {
	786	$sql .= 'DROP '.$col['name'].' ';
	787	}
	788	if($col['action'] != 'drop') {
	789	if($col['defaultValue'] != '') $sql .= "DEFAULT '".$col['defaultValue']."' ";
	790	if($col['notNull'] == true) {
	791	$sql .= 'NOT NULL ';
	792	} else {
	793	$sql .= 'NULL ';
	794	}
	795	if($col['autoInc'] == true) $sql .= 'auto_increment ';
	796	$sql.= ',';
	797	// Index definitions
	798	if($col['option'] == 'primary') $index .= 'PRIMARY KEY ('.$col['name'].'),';
	799	if($col['option'] == 'index') $index .= 'INDEX ('.$col['name'].'),';
	800	if($col['option'] == 'unique') $index .= 'UNIQUE ('.$col['name'].'),';
	801	}
	802	}
	803	$sql .= $index;
	804	$sql = substr($sql, 0, -1);
cd8bb8	805	/* TODO: secure parameters */
b1a6a5	806	//die($sql);
cd8bb8	807	$this->query($sql, $table_name);
b1a6a5	808	return true;
f5b0ca	809	}
b1a6a5	810
MC	811	public function dropTable($table_name) {
	812	$this->check($table_name);
cd8bb8	813	$sql = "DROP TABLE ??";
MC	814	return $this->query($sql, $table_name);
f5b0ca	815	}
N	816
b1a6a5	817	// gibt Array mit Tabellennamen zur�ck
MC	818	public function getTables($database_name = '') {
cd8bb8	819	if(!is_object($this->_iConnId)) return false;
b1a6a5	820	if($database_name == '') $database_name = $this->dbName;
cd8bb8	821	$tb_names = $this->queryAllArray("SHOW TABLES FROM ??", $database_name);
b1a6a5	822	return $tb_names;
MC	823	}
f5b0ca	824
b1a6a5	825	// gibt Feldinformationen zur Tabelle zur�ck
MC	826	/*
f5b0ca	827	$columns = array(action => add \| alter \| drop
N	828	name => Spaltenname
	829	name_new => neuer Spaltenname, nur bei 'alter' belegt
	830	type => 42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
	831	typeValue => Wert z.B. bei Varchar
	832	defaultValue => Default Wert
	833	notNull => true \| false
	834	autoInc => true \| false
	835	option => unique \| primary \| index)
	836
	837
	838	*/
	839
b1a6a5	840	function tableInfo($table_name) {
f5b0ca	841
b1a6a5	842	global $go_api, $go_info;
MC	843	// Tabellenfelder einlesen
f5b0ca	844
cd8bb8	845	if($rows = $go_api->db->queryAllRecords('SHOW FIELDS FROM ??', $table_name)){
b1a6a5	846	foreach($rows as $row) {
cd8bb8	847	$name = $row['Field'];
MC	848	$default = $row['Default'];
	849	$key = $row['Key'];
	850	$extra = $row['Extra'];
	851	$isnull = $row['Null'];
	852	$type = $row['Type'];
f5b0ca	853
N	854
b1a6a5	855	$column = array();
f5b0ca	856
b1a6a5	857	$column['name'] = $name;
MC	858	//$column['type'] = $type;
	859	$column['defaultValue'] = $default;
	860	if(stristr($key, 'PRI')) $column['option'] = 'primary';
	861	if(stristr($isnull, 'YES')) {
	862	$column['notNull'] = false;
	863	} else {
	864	$column['notNull'] = true;
	865	}
	866	if($extra == 'auto_increment') $column['autoInc'] = true;
f5b0ca	867
N	868
b1a6a5	869	// Type in Metatype umsetzen
f5b0ca	870
b1a6a5	871	if(stristr($type, 'int(')) $metaType = 'int32';
MC	872	if(stristr($type, 'bigint')) $metaType = 'int64';
	873	if(stristr($type, 'char')) {
	874	$metaType = 'char';
	875	$tmp_typeValue = explode('(', $type);
	876	$column['typeValue'] = substr($tmp_typeValue[1], 0, -1);
	877	}
	878	if(stristr($type, 'varchar')) {
	879	$metaType = 'varchar';
	880	$tmp_typeValue = explode('(', $type);
	881	$column['typeValue'] = substr($tmp_typeValue[1], 0, -1);
	882	}
	883	if(stristr($type, 'text')) $metaType = 'text';
	884	if(stristr($type, 'double')) $metaType = 'double';
	885	if(stristr($type, 'blob')) $metaType = 'blob';
f5b0ca	886
N	887
b1a6a5	888	$column['type'] = $metaType;
f5b0ca	889
b1a6a5	890	$columns[] = $column;
MC	891	}
	892	return $columns;
	893	} else {
	894	return false;
	895	}
f5b0ca	896
N	897
b1a6a5	898	//$this->createTable('tester',$columns);
f5b0ca	899
b1a6a5	900	/*
f5b0ca	901	$result = mysql_list_fields($go_info["server"]["db_name"],$table_name);
N	902	$fields = mysql_num_fields ($result);
	903	$i = 0;
	904	$table = mysql_field_table ($result, $i);
	905	while ($i < $fields) {
	906	$name = mysql_field_name ($result, $i);
	907	$type = mysql_field_type ($result, $i);
	908	$len = mysql_field_len ($result, $i);
	909	$flags = mysql_field_flags ($result, $i);
	910	print_r($flags);
	911
	912	$columns = array(name => $name,
	913	type => "",
	914	defaultValue => "",
	915	isnull => 1,
	916	option => "");
	917	$returnvar[] = $columns;
	918
	919	$i++;
	920	}
	921	*/
	922
	923
	924
b1a6a5	925	}
f5b0ca	926
b1a6a5	927	public function mapType($metaType, $typeValue) {
MC	928	global $go_api;
	929	$metaType = strtolower($metaType);
	930	switch ($metaType) {
	931	case 'int16':
	932	return 'smallint';
	933	break;
	934	case 'int32':
	935	return 'int';
	936	break;
	937	case 'int64':
	938	return 'bigint';
	939	break;
	940	case 'double':
	941	return 'double';
	942	break;
	943	case 'char':
	944	return 'char';
	945	break;
	946	case 'varchar':
	947	if($typeValue < 1) die('Database failure: Lenght required for these data types.');
	948	return 'varchar('.$typeValue.')';
	949	break;
	950	case 'text':
	951	return 'text';
	952	break;
	953	case 'blob':
	954	return 'blob';
	955	break;
	956	}
	957	}
f5b0ca	958
b1a6a5	959	}
f5b0ca	960
cd8bb8	961	/**
MC	962	* database query result class
	963	*
	964	* @package pxFramework
	965	*
	966	*/
	967	class db_result {
	968
	969	/**
	970	*
	971	*
	972	* @access private
	973	*/
	974	private $_iResId = null;
	975	private $_iConnection = null;
	976
	977
	978
	979	/**
	980	*
	981	*
	982	* @access private
	983	*/
	984	public function db_result($iResId, $iConnection) {
	985	$this->_iResId = $iResId;
	986	$this->_iConnection = $iConnection;
	987	}
	988
	989
	990
	991	/**
	992	* get count of result rows
	993	*
	994	* Returns the amount of rows in the result set
	995	*
	996	* @access public
	997	* @return int amount of rows
	998	*/
	999	public function rows() {
	1000	if(!is_object($this->_iResId)) return 0;
	1001	$iRows = mysqli_num_rows($this->_iResId);
	1002	if(!$iRows) $iRows = 0;
	1003	return $iRows;
	1004	}
	1005
	1006
	1007
	1008	/**
	1009	* Get number of affected rows
	1010	*
	1011	* Returns the amount of rows affected by the previous query
	1012	*
	1013	* @access public
	1014	* @return int amount of affected rows
	1015	*/
	1016	public function affected() {
	1017	if(!is_object($this->_iConnection)) return 0;
	1018	$iRows = mysqli_affected_rows($this->_iConnection);
	1019	if(!$iRows) $iRows = 0;
	1020	return $iRows;
	1021	}
	1022
	1023
	1024
	1025	/**
	1026	* Frees the result set
	1027	*
	1028	* @access public
	1029	*/
	1030	public function free() {
	1031	if(!is_object($this->_iResId)) return;
	1032
	1033	mysqli_free_result($this->_iResId);
	1034	return;
	1035	}
	1036
	1037
	1038
	1039	/**
	1040	* Get a result row (associative)
	1041	*
	1042	* Returns the next row in the result set. To be used in a while loop like while($currow = $result->get()) { do something ... }
	1043	*
	1044	* @access public
	1045	* @return array result row
	1046	*/
	1047	public function get() {
	1048	$aItem = null;
	1049
	1050	if(is_object($this->_iResId)) {
	1051	$aItem = mysqli_fetch_assoc($this->_iResId);
	1052	if(!$aItem) $aItem = null;
	1053	}
	1054	return $aItem;
	1055	}
	1056
	1057
	1058
	1059	/**
	1060	* Get a result row (array with numeric index)
	1061	*
	1062	* @access public
	1063	* @return array result row
	1064	*/
	1065	public function getAsRow() {
	1066	$aItem = null;
	1067
	1068	if(is_object($this->_iResId)) {
	1069	$aItem = mysqli_fetch_row($this->_iResId);
	1070	if(!$aItem) $aItem = null;
	1071	}
	1072	return $aItem;
	1073	}
	1074
	1075	}
	1076
	1077	/**
	1078	* database query result class
	1079	*
	1080	* emulates a db result set out of an array so you can use array results and db results the same way
	1081	*
	1082	* @package pxFramework
	1083	* @see db_result
	1084	*
	1085	*
	1086	*/
	1087	class fakedb_result {
	1088
	1089	/**
	1090	*
	1091	*
	1092	* @access private
	1093	*/
	1094	private $aResultData = array();
	1095
	1096	/**
	1097	*
	1098	*
	1099	* @access private
	1100	*/
	1101	private $aLimitedData = array();
	1102
	1103
	1104
	1105	/**
	1106	*
	1107	*
	1108	* @access private
	1109	*/
	1110	public function fakedb_result($aData) {
	1111	$this->aResultData = $aData;
	1112	$this->aLimitedData = $aData;
	1113	reset($this->aLimitedData);
	1114	}
	1115
	1116
	1117
	1118	/**
	1119	* get count of result rows
	1120	*
	1121	* Returns the amount of rows in the result set
	1122	*
	1123	* @access public
	1124	* @return int amount of rows
	1125	*/
	1126	// Gibt die Anzahl Zeilen zurück
	1127	public function rows() {
	1128	return count($this->aLimitedData);
	1129	}
	1130
	1131
	1132
	1133	/**
	1134	* Frees the result set
	1135	*
	1136	* @access public
	1137	*/
	1138	// Gibt ein Ergebnisset frei
	1139	public function free() {
	1140	$this->aResultData = array();
	1141	$this->aLimitedData = array();
	1142	return;
	1143	}
	1144
	1145
	1146
	1147	/**
	1148	* Get a result row (associative)
	1149	*
	1150	* Returns the next row in the result set. To be used in a while loop like while($currow = $result->get()) { do something ... }
	1151	*
	1152	* @access public
	1153	* @return array result row
	1154	*/
	1155	// Gibt eine Ergebniszeile zurück
	1156	public function get() {
	1157	$aItem = null;
	1158
	1159	if(!is_array($this->aLimitedData)) return $aItem;
	1160
	1161	if(list($vKey, $aItem) = each($this->aLimitedData)) {
	1162	if(!$aItem) $aItem = null;
	1163	}
	1164	return $aItem;
	1165	}
	1166
	1167
	1168
	1169	/**
	1170	* Get a result row (array with numeric index)
	1171	*
	1172	* @access public
	1173	* @return array result row
	1174	*/
	1175	public function getAsRow() {
	1176	return $this->get();
	1177	}
	1178
	1179
	1180
	1181	/**
	1182	* Limit the result (like a LIMIT x,y in a SQL query)
	1183	*
	1184	* @access public
	1185	* @param int $iStart offset to start read
	1186	* @param int iLength amount of datasets to read
	1187	*/
	1188	public function limit_result($iStart, $iLength) {
	1189	$this->aLimitedData = array_slice($this->aResultData, $iStart, $iLength, true);
	1190	}
	1191
	1192	}
	1193
	1194
b1a6a5	1195	?>