gitlabFork/ISPConfig3.git - Solinfo Gitblit

Marius Burkard

2016-07-01 49441bdd0f3ff75d5092d5b832b97ea722a66363

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