gitlabFork/ISPConfig3.git - Solinfo Gitblit

Marius Cramer

2014-02-24 a1918e5a22a600e3b88a9ab3959d05770216a99b

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