gitlabFork/ISPConfig3.git - Solinfo Gitblit

Fixed wrong argument passing method in alias query functions

Marius Cramer

2013-11-29 7ae5b04f40c36b6a977f5811e7fa1a9560523856

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