gitlabFork/ISPConfig3.git - Solinfo Gitblit

Pascal Dreissen

2016-07-08 f1193b43f4c9fd132741d30f03f0b35841011989

commit \| author \| age
9200ad	1	<?php
T	2	/*
2af58c	3	Copyright (c) 2005, Till Brehm, projektfarm Gmbh
MC	4	All rights reserved.
9200ad	5
2af58c	6	Redistribution and use in source and binary forms, with or without modification,
MC	7	are permitted provided that the following conditions are met:
9200ad	8
2af58c	9	* Redistributions of source code must retain the above copyright notice,
MC	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.
9200ad	17
2af58c	18	THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
MC	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	*/
9200ad	29
2af58c	30	class db extends mysqli
436ed8	31	{
2af58c	32	/**#@+
MC	33	* @access private
	34	*/
	35	private $_iQueryId;
	36	private $_iConnId;
	37
	38	private $dbHost = ''; // hostname of the MySQL server
	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
	45	/*#@-/
	46
	47	public $show_error_messages = false; // false in server, true in interface
	48
	49
	50	/* old things - unused now ////
	51	private $linkId = 0; // last result of mysqli_connect()
	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
	60	////
	61	*/
436ed8	62
5eafbc	63	public function __destruct() {
MC	64	if($this->_iConnId) mysqli_close($this->_iConnId);
	65	}
	66
	67	private function do_connect() {
436ed8	68	global $conf;
5eafbc	69
MC	70	if($this->_iConnId) return true;
436ed8	71	$this->dbHost = $conf["mysql"]["host"];
305dda	72	$this->dbName = false;//$conf["mysql"]["database"];
436ed8	73	$this->dbUser = $conf["mysql"]["admin_user"];
R	74	$this->dbPass = $conf["mysql"]["admin_password"];
	75	$this->dbCharset = $conf["mysql"]["charset"];
2af58c	76	$this->dbNewLink = false;
MC	77	$this->dbClientFlags = null;
40c6e8	78
2af58c	79	$this->_iConnId = mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass);
MC	80	$try = 0;
	81	while((!is_object($this->_iConnId) \|\| mysqli_connect_error()) && $try < 5) {
	82	if($try > 0) sleep(1);
	83
	84	$try++;
	85	$this->_iConnId = mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass);
436ed8	86	}
2af58c	87
MC	88	if(!is_object($this->_iConnId) \|\| mysqli_connect_error()) {
	89	$this->_iConnId = null;
	90	$this->_sqlerror('Zugriff auf Datenbankserver fehlgeschlagen! / Database server not accessible!');
	91	return false;
	92	}
305dda	93
MC	94	if($this->dbName) $this->setDBName($this->dbName);
	95
	96	$this->_setCharset();
	97	}
	98
	99	public function setDBData($host, $user, $password) {
	100	$this->dbHost = $host;
	101	$this->dbUser = $user;
	102	$this->dbPass = $password;
	103	}
	104
	105	public function setDBName($name) {
	106	$this->dbName = $name;
772dad	107	$this->_iConnId = mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass);
2af58c	108	if(!((bool)mysqli_query( $this->_iConnId, 'USE `' . $this->dbName . '`'))) {
MC	109	$this->close();
	110	$this->_sqlerror('Datenbank nicht gefunden / Database not found');
	111	return false;
	112	}
436ed8	113	}
40c6e8	114
2af58c	115	public function close() {
MC	116	if($this->_iConnId) mysqli_close($this->_iConnId);
	117	$this->_iConnId = null;
	118	}
	119
	120	/* This allows our private variables to be "read" out side of the class */
	121	public function __get($var) {
	122	return isset($this->$var) ? $this->$var : NULL;
	123	}
	124
	125	public function _build_query_string($sQuery = '') {
	126	$iArgs = func_num_args();
	127	if($iArgs > 1) {
	128	$aArgs = func_get_args();
	129
	130	if($iArgs == 3 && $aArgs[1] === true && is_array($aArgs[2])) {
	131	$aArgs = $aArgs[2];
	132	$iArgs = count($aArgs);
	133	} else {
	134	array_shift($aArgs); // delete the query string that is the first arg!
436ed8	135	}
2af58c	136
MC	137	$iPos = 0;
	138	$iPos2 = 0;
	139	foreach($aArgs as $sKey => $sValue) {
	140	$iPos2 = strpos($sQuery, '??', $iPos2);
	141	$iPos = strpos($sQuery, '?', $iPos);
	142
	143	if($iPos === false && $iPos2 === false) break;
	144
	145	if($iPos2 !== false && ($iPos === false \|\| $iPos2 <= $iPos)) {
	146	$sTxt = $this->escape($sValue);
	147
cf84b3	148	if(strpos($sTxt, '.') !== false) {
MC	149	$sTxt = preg_replace('/^(.+)\.(.+)$/', '`$1`.`$2`', $sTxt);
	150	$sTxt = str_replace('.``', '.', $sTxt);
	151	} else $sTxt = '`' . $sTxt . '`';
2af58c	152
MC	153	$sQuery = substr_replace($sQuery, $sTxt, $iPos2, 2);
	154	$iPos2 += strlen($sTxt);
	155	$iPos = $iPos2;
	156	} else {
	157	if(is_int($sValue) \|\| is_float($sValue)) {
	158	$sTxt = $sValue;
	159	} elseif(is_string($sValue) && (strcmp($sValue, '#NULL#') == 0)) {
	160	$sTxt = 'NULL';
	161	} elseif(is_array($sValue)) {
	162	$sTxt = '';
	163	foreach($sValue as $sVal) $sTxt .= ',\'' . $this->escape($sVal) . '\'';
	164	$sTxt = '(' . substr($sTxt, 1) . ')';
	165	if($sTxt == '()') $sTxt = '(0)';
	166	} else {
	167	$sTxt = '\'' . $this->escape($sValue) . '\'';
	168	}
	169
	170	$sQuery = substr_replace($sQuery, $sTxt, $iPos, 1);
	171	$iPos += strlen($sTxt);
	172	$iPos2 = $iPos;
	173	}
	174	}
	175	}
	176
	177	return $sQuery;
	178	}
	179
	180	/*#@-/
	181
	182
	183	/**#@+
	184	* @access private
	185	*/
	186	private function _setCharset() {
	187	mysqli_query($this->_iConnId, 'SET NAMES '.$this->dbCharset);
	188	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."'");
	189	}
	190
	191	private function _query($sQuery = '') {
d7ad8f	192	$this->do_connect();
40c6e8	193
2af58c	194	if ($sQuery == '') {
MC	195	$this->_sqlerror('Keine Anfrage angegeben / No query given');
	196	return false;
	197	}
	198
	199	$try = 0;
	200	do {
	201	$try++;
	202	$ok = mysqli_ping($this->_iConnId);
	203	if(!$ok) {
	204	if(!mysqli_connect($this->dbHost, $this->dbUser, $this->dbPass, $this->dbName)) {
	205	if($this->errorNumber == '111') {
	206	// server is not available
	207	if($try > 9) {
c6d4e8	208	$this->_sqlerror('DB::query -> error connecting');
MC	209	exit;
2af58c	210	}
MC	211	sleep(30); // additional seconds, please!
	212	}
	213
	214	if($try > 9) {
	215	$this->_sqlerror('DB::query -> reconnect');
	216	return false;
	217	} else {
	218	sleep(($try > 7 ? 5 : 1));
	219	}
	220	} else {
	221	$this->_setCharset();
	222	$ok = true;
	223	}
	224	}
	225	} while($ok == false);
	226
	227	$aArgs = func_get_args();
	228	$sQuery = call_user_func_array(array(&$this, '_build_query_string'), $aArgs);
	229
	230	$this->_iQueryId = mysqli_query($this->_iConnId, $sQuery);
	231	if (!$this->_iQueryId) {
	232	$this->_sqlerror('Falsche Anfrage / Wrong Query', false, 'SQL-Query = ' . $sQuery);
	233	return false;
	234	}
	235
	236	return is_bool($this->_iQueryId) ? $this->_iQueryId : new db_result($this->_iQueryId, $this->_iConnId);
	237	}
	238
	239	/*#@-/
	240
	241
	242
	243
	244
	245	/**
	246	* Executes a query
	247	*
	248	* Executes a given query string, has a variable amount of parameters:
	249	* - 1 parameter
	250	* executes the given query
	251	* - 2 parameters
	252	* executes the given query, replaces the first ? in the query with the second parameter
	253	* - 3 parameters
	254	* 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
	255	* - 4 or more parameters
	256	* all ? in the query are replaced from left to right by the parameters 2 to x
	257	*
	258	* @access public
	259	* @param string $sQuery query string
	260	* @param mixed ... one or more parameters
	261	* @return db_result the result object of the query
	262	*/
	263
	264
	265	public function query($sQuery = '') {
	266	$aArgs = func_get_args();
	267	return call_user_func_array(array(&$this, '_query'), $aArgs);
	268	}
	269
	270	/**
	271	* Execute a query and get first result array
	272	*
	273	* Executes a query and returns the first result row as an array
	274	* This is like calling $result = $db->query(), $result->get(), $result->free()
	275	* Use of this function @see query
	276	*
	277	* @access public
	278	* @param string $sQuery query to execute
	279	* @param ... further params (see query())
	280	* @return array result row or NULL if none found
	281	*/
	282	public function queryOneRecord($sQuery = '') {
	283	if(!preg_match('/limit \d+\s,\s\d+$/i', $sQuery)) $sQuery .= ' LIMIT 0,1';
	284
	285	$aArgs = func_get_args();
	286	$oResult = call_user_func_array(array(&$this, 'query'), $aArgs);
	287	if(!$oResult) return null;
	288
	289	$aReturn = $oResult->get();
	290	$oResult->free();
	291
	292	return $aReturn;
	293	}
	294
	295	public function queryOne($sQuery = '') {
	296	return call_user_func_array(array(&$this, 'queryOneRecord'), func_get_args());
	297	}
	298
	299	public function query_one($sQuery = '') {
	300	return call_user_func_array(array(&$this, 'queryOneRecord'), func_get_args());
	301	}
	302
	303	/**
	304	* Execute a query and return all rows
	305	*
	306	* Executes a query and returns all result rows in an array
	307	* <strong>Use this with extreme care!!!</strong> Uses lots of memory on big result sets.
	308	*
	309	* @access public
	310	* @param string $sQuery query to execute
	311	* @param ... further params (see query())
	312	* @return array all the rows in the result set
	313	*/
	314	public function queryAllRecords($sQuery = '') {
	315	$aArgs = func_get_args();
	316	$oResult = call_user_func_array(array(&$this, 'query'), $aArgs);
	317	if(!$oResult) return array();
	318
	319	$aResults = array();
	320	while($aRow = $oResult->get()) {
	321	$aResults[] = $aRow;
	322	}
	323	$oResult->free();
	324
	325	return $aResults;
	326	}
	327
	328	public function queryAll($sQuery = '') {
	329	return call_user_func_array(array(&$this, 'queryAllRecords'), func_get_args());
	330	}
	331
	332	public function query_all($sQuery = '') {
	333	return call_user_func_array(array(&$this, 'queryAllRecords'), func_get_args());
	334	}
	335
	336	/**
	337	* Execute a query and return all rows as simple array
	338	*
	339	* Executes a query and returns all result rows in an array with elements
	340	* <strong>Only first column is returned</strong> Uses lots of memory on big result sets.
	341	*
	342	* @access public
	343	* @param string $sQuery query to execute
	344	* @param ... further params (see query())
	345	* @return array all the rows in the result set
	346	*/
	347	public function queryAllArray($sQuery = '') {
	348	$aArgs = func_get_args();
	349	$oResult = call_user_func_array(array(&$this, 'query'), $aArgs);
	350	if(!$oResult) return array();
	351
	352	$aResults = array();
	353	while($aRow = $oResult->get()) {
	354	$aResults[] = reset($aRow);
	355	}
	356	$oResult->free();
	357
	358	return $aResults;
	359	}
	360
	361	public function query_all_array($sQuery = '') {
	362	return call_user_func_array(array(&$this, 'queryAllArray'), func_get_args());
	363	}
	364
	365
	366
	367	/**
	368	* Get id of last inserted row
	369	*
	370	* Gives you the id of the last inserted row in a table with an auto-increment primary key
	371	*
	372	* @access public
	373	* @return int id of last inserted row or 0 if none
	374	*/
	375	public function insert_id() {
	376	$iRes = mysqli_query($this->_iConnId, 'SELECT LAST_INSERT_ID() as `newid`');
	377	if(!is_object($iRes)) return false;
	378
	379	$aReturn = mysqli_fetch_assoc($iRes);
	380	mysqli_free_result($iRes);
	381
	382	return $aReturn['newid'];
	383	}
	384
	385
	386
	387	/**
	388	* get affected row count
	389	*
	390	* Gets the amount of rows affected by the previous query
	391	*
	392	* @access public
	393	* @return int affected rows
	394	*/
	395	public function affected() {
	396	if(!is_object($this->_iConnId)) return 0;
	397	$iRows = mysqli_affected_rows($this->_iConnId);
	398	if(!$iRows) $iRows = 0;
	399	return $iRows;
	400	}
	401
	402
	403
	404	/**
	405	* check if a utf8 string is valid
	406	*
	407	* @access public
	408	* @param string $string the string to check
	409	* @return bool true if it is valid utf8, false otherwise
	410	*/
	411	private function check_utf8($str) {
	412	$len = strlen($str);
	413	for($i = 0; $i < $len; $i++){
	414	$c = ord($str[$i]);
	415	if ($c > 128) {
	416	if (($c > 247)) return false;
	417	elseif ($c > 239) $bytes = 4;
	418	elseif ($c > 223) $bytes = 3;
	419	elseif ($c > 191) $bytes = 2;
	420	else return false;
	421	if (($i + $bytes) > $len) return false;
	422	while ($bytes > 1) {
	423	$i++;
	424	$b = ord($str[$i]);
	425	if ($b < 128 \|\| $b > 191) return false;
	426	$bytes--;
	427	}
	428	}
436ed8	429	}
R	430	return true;
2af58c	431	} // end of check_utf8
436ed8	432
2af58c	433	/**
MC	434	* Escape a string for usage in a query
	435	*
	436	* @access public
	437	* @param string $sString query string to escape
	438	* @return string escaped string
	439	*/
	440	public function escape($sString) {
	441	if(!is_string($sString) && !is_numeric($sString)) {
	442	$sString = '';
436ed8	443	}
2af58c	444
MC	445	$cur_encoding = mb_detect_encoding($sString);
	446	if($cur_encoding != "UTF-8") {
	447	if($cur_encoding != 'ASCII') {
	448	if($cur_encoding) $sString = mb_convert_encoding($sString, 'UTF-8', $cur_encoding);
	449	else $sString = mb_convert_encoding($sString, 'UTF-8');
436ed8	450	}
2af58c	451	} elseif(!$this->check_utf8($sString)) {
MC	452	$sString = utf8_encode($sString);
436ed8	453	}
2af58c	454
MC	455	if($this->_iConnId) return mysqli_real_escape_string($this->_iConnId, $sString);
	456	else return addslashes($sString);
436ed8	457	}
R	458
2af58c	459	/**
MC	460	*
	461	*
	462	* @access private
	463	*/
	464	private function _sqlerror($sErrormsg = 'Unbekannter Fehler', $sAddMsg = '') {
c6d4e8	465	global $conf;
2af58c	466
MC	467	$mysql_error = (is_object($this->_iConnId) ? mysqli_error($this->_iConnId) : mysqli_connect_error());
	468	$mysql_errno = (is_object($this->_iConnId) ? mysqli_errno($this->_iConnId) : mysqli_connect_errno());
	469
	470	//$sAddMsg .= getDebugBacktrace();
	471
	472	if($this->show_error_messages && $conf['demo_mode'] === false) {
	473	echo $sErrormsg . $sAddMsg;
c6d4e8	474	}
436ed8	475	}
R	476
2af58c	477	public function affectedRows() {
MC	478	return $this->affected();
436ed8	479	}
b1a6a5	480
436ed8	481	// returns mySQL insert id
2af58c	482	public function insertID() {
MC	483	return $this->insert_id();
436ed8	484	}
b1a6a5	485
2af58c	486
MC	487	//* Function to quote strings
	488	public function quote($formfield) {
	489	return $this->escape($formfield);
b1a6a5	490	}
MC	491
2af58c	492	//* Function to unquotae strings
MC	493	public function unquote($formfield) {
b1a6a5	494	return stripslashes($formfield);
MC	495	}
	496
2af58c	497	public function toLower($record) {
436ed8	498	if(is_array($record)) {
R	499	foreach($record as $key => $val) {
	500	$key = strtolower($key);
	501	$out[$key] = $val;
	502	}
	503	}
b1a6a5	504	return $out;
436ed8	505	}
b1a6a5	506
2af58c	507	/* TODO: rewrite SQL */
b1a6a5	508	function insert($tablename, $form, $debug = 0)
MC	509	{
	510	if(is_array($form)){
	511	foreach($form as $key => $value)
	512	{
	513	$sql_key .= "$key, ";
	514	$sql_value .= "'".$this->check($value)."', ";
436ed8	515	}
b1a6a5	516	$sql_key = substr($sql_key, 0, strlen($sql_key) - 2);
MC	517	$sql_value = substr($sql_value, 0, strlen($sql_value) - 2);
	518
	519	$sql = "INSERT INTO $tablename (" . $sql_key . ") VALUES (" . $sql_value .")";
	520
	521	if($debug == 1) echo "SQL-Statement: ".$sql."<br><br>";
	522	$this->query($sql);
	523	if($debug == 1) echo "mySQL Error Message: ".$this->errorMessage;
436ed8	524	}
b1a6a5	525	}
2af58c	526
MC	527	/* TODO: rewrite SQL */
b1a6a5	528	function update($tablename, $form, $bedingung, $debug = 0)
MC	529	{
	530
	531	if(is_array($form)){
	532	foreach($form as $key => $value)
	533	{
	534	$insql .= "$key = '".$this->check($value)."', ";
	535	}
	536	$insql = substr($insql, 0, strlen($insql) - 2);
	537	$sql = "UPDATE $tablename SET " . $insql . " WHERE $bedingung";
	538	if($debug == 1) echo "SQL-Statement: ".$sql."<br><br>";
	539	$this->query($sql);
	540	if($debug == 1) echo "mySQL Error Message: ".$this->errorMessage;
436ed8	541	}
b1a6a5	542	}
MC	543
	544
	545	/*
2af58c	546	$columns = array(action => add \| alter \| drop
MC	547	name => Spaltenname
	548	name_new => neuer Spaltenname, nur bei 'alter' belegt
	549	type => 42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
	550	typeValue => Wert z.B. bei Varchar
	551	defaultValue => Default Wert
	552	notNull => true \| false
	553	autoInc => true \| false
	554	option => unique \| primary \| index)
b1a6a5	555
MC	556
2af58c	557	*/
MC	558	/* TODO: rewrite SQL */
	559	public function createTable($table_name, $columns) {
	560	$index = '';
	561	$sql = "CREATE TABLE ?? (";
b1a6a5	562	foreach($columns as $col){
2af58c	563	$sql .= $col['name'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
b1a6a5	564
2af58c	565	if($col['defaultValue'] != '') $sql .= "DEFAULT '".$col['defaultValue']."' ";
MC	566	if($col['notNull'] == true) {
	567	$sql .= 'NOT NULL ';
	568	} else {
	569	$sql .= 'NULL ';
b1a6a5	570	}
2af58c	571	if($col['autoInc'] == true) $sql .= 'auto_increment ';
MC	572	$sql.= ',';
b1a6a5	573	// key Definitionen
2af58c	574	if($col['option'] == 'primary') $index .= 'PRIMARY KEY ('.$col['name'].'),';
MC	575	if($col['option'] == 'index') $index .= 'INDEX ('.$col['name'].'),';
	576	if($col['option'] == 'unique') $index .= 'UNIQUE ('.$col['name'].'),';
b1a6a5	577	}
MC	578	$sql .= $index;
	579	$sql = substr($sql, 0, -1);
2af58c	580	$sql .= ')';
MC	581	/* TODO: secure parameters */
	582	$this->query($sql, $table_name);
b1a6a5	583	return true;
MC	584	}
	585
	586	/*
2af58c	587	$columns = array(action => add \| alter \| drop
MC	588	name => Spaltenname
	589	name_new => neuer Spaltenname, nur bei 'alter' belegt
	590	type => 42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
	591	typeValue => Wert z.B. bei Varchar
	592	defaultValue => Default Wert
	593	notNull => true \| false
	594	autoInc => true \| false
	595	option => unique \| primary \| index)
b1a6a5	596
MC	597
2af58c	598	*/
MC	599	/* TODO: rewrite SQL */
	600	public function alterTable($table_name, $columns) {
	601	$index = '';
	602	$sql = "ALTER TABLE ?? ";
b1a6a5	603	foreach($columns as $col){
2af58c	604	if($col['action'] == 'add') {
MC	605	$sql .= 'ADD '.$col['name'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
	606	} elseif ($col['action'] == 'alter') {
	607	$sql .= 'CHANGE '.$col['name'].' '.$col['name_new'].' '.$this->mapType($col['type'], $col['typeValue']).' ';
	608	} elseif ($col['action'] == 'drop') {
	609	$sql .= 'DROP '.$col['name'].' ';
b1a6a5	610	}
2af58c	611	if($col['action'] != 'drop') {
MC	612	if($col['defaultValue'] != '') $sql .= "DEFAULT '".$col['defaultValue']."' ";
	613	if($col['notNull'] == true) {
	614	$sql .= 'NOT NULL ';
b1a6a5	615	} else {
2af58c	616	$sql .= 'NULL ';
b1a6a5	617	}
2af58c	618	if($col['autoInc'] == true) $sql .= 'auto_increment ';
MC	619	$sql.= ',';
	620	// Index definitions
	621	if($col['option'] == 'primary') $index .= 'PRIMARY KEY ('.$col['name'].'),';
	622	if($col['option'] == 'index') $index .= 'INDEX ('.$col['name'].'),';
	623	if($col['option'] == 'unique') $index .= 'UNIQUE ('.$col['name'].'),';
b1a6a5	624	}
MC	625	}
	626	$sql .= $index;
	627	$sql = substr($sql, 0, -1);
2af58c	628	/* TODO: secure parameters */
b1a6a5	629	//die($sql);
2af58c	630	$this->query($sql, $table_name);
b1a6a5	631	return true;
MC	632	}
	633
2af58c	634	public function dropTable($table_name) {
b1a6a5	635	$this->check($table_name);
2af58c	636	$sql = "DROP TABLE ??";
MC	637	return $this->query($sql, $table_name);
b1a6a5	638	}
MC	639
	640	// gibt Array mit Tabellennamen zur�ck
2af58c	641	public function getTables($database_name = '') {
MC	642	if(!is_object($this->_iConnId)) return false;
	643	if($database_name == '') $database_name = $this->dbName;
	644	$tb_names = $this->queryAllArray("SHOW TABLES FROM ??", $database_name);
b1a6a5	645	return $tb_names;
MC	646	}
	647
	648	// gibt Feldinformationen zur Tabelle zur�ck
	649	/*
2af58c	650	$columns = array(action => add \| alter \| drop
MC	651	name => Spaltenname
	652	name_new => neuer Spaltenname, nur bei 'alter' belegt
	653	type => 42go-Meta-Type: int16, int32, int64, double, char, varchar, text, blob
	654	typeValue => Wert z.B. bei Varchar
	655	defaultValue => Default Wert
	656	notNull => true \| false
	657	autoInc => true \| false
	658	option => unique \| primary \| index)
7b47c0	659
b1a6a5	660
2af58c	661	*/
MC	662	/* TODO: rewrite SQL */
b1a6a5	663	function tableInfo($table_name) {
MC	664
	665	global $go_api, $go_info;
	666	// Tabellenfelder einlesen
	667
2af58c	668	if($rows = $go_api->db->queryAllRecords('SHOW FIELDS FROM ??', $table_name)){
b1a6a5	669	foreach($rows as $row) {
2af58c	670	$name = $row['Field'];
MC	671	$default = $row['Default'];
	672	$key = $row['Key'];
	673	$extra = $row['Extra'];
	674	$isnull = $row['Null'];
	675	$type = $row['Type'];
b1a6a5	676
MC	677
	678	$column = array();
	679
2af58c	680	$column['name'] = $name;
MC	681	//$column['type'] = $type;
	682	$column['defaultValue'] = $default;
	683	if(stristr($key, 'PRI')) $column['option'] = 'primary';
	684	if(stristr($isnull, 'YES')) {
	685	$column['notNull'] = false;
b1a6a5	686	} else {
2af58c	687	$column['notNull'] = true;
b1a6a5	688	}
2af58c	689	if($extra == 'auto_increment') $column['autoInc'] = true;
b1a6a5	690
MC	691
	692	// Type in Metatype umsetzen
	693
2af58c	694	if(stristr($type, 'int(')) $metaType = 'int32';
MC	695	if(stristr($type, 'bigint')) $metaType = 'int64';
	696	if(stristr($type, 'char')) {
b1a6a5	697	$metaType = 'char';
MC	698	$tmp_typeValue = explode('(', $type);
2af58c	699	$column['typeValue'] = substr($tmp_typeValue[1], 0, -1);
b1a6a5	700	}
2af58c	701	if(stristr($type, 'varchar')) {
b1a6a5	702	$metaType = 'varchar';
MC	703	$tmp_typeValue = explode('(', $type);
2af58c	704	$column['typeValue'] = substr($tmp_typeValue[1], 0, -1);
b1a6a5	705	}
2af58c	706	if(stristr($type, 'text')) $metaType = 'text';
MC	707	if(stristr($type, 'double')) $metaType = 'double';
	708	if(stristr($type, 'blob')) $metaType = 'blob';
b1a6a5	709
MC	710
2af58c	711	$column['type'] = $metaType;
b1a6a5	712
MC	713	$columns[] = $column;
	714	}
	715	return $columns;
	716	} else {
	717	return false;
	718	}
	719
	720	}
	721
2af58c	722	public function mapType($metaType, $typeValue) {
b1a6a5	723	global $go_api;
MC	724	$metaType = strtolower($metaType);
	725	switch ($metaType) {
	726	case 'int16':
	727	return 'smallint';
	728	break;
	729	case 'int32':
	730	return 'int';
	731	break;
	732	case 'int64':
	733	return 'bigint';
	734	break;
	735	case 'double':
	736	return 'double';
	737	break;
	738	case 'char':
	739	return 'char';
	740	break;
	741	case 'varchar':
2af58c	742	if($typeValue < 1) die('Database failure: Lenght required for these data types.');
b1a6a5	743	return 'varchar('.$typeValue.')';
MC	744	break;
	745	case 'text':
	746	return 'text';
	747	break;
	748	case 'blob':
	749	return 'blob';
	750	break;
	751	}
	752	}
	753
436ed8	754	}
R	755
2af58c	756	/**
MC	757	* database query result class
	758	*
	759	* @package pxFramework
	760	*
	761	*/
	762	class db_result {
	763
	764	/**
	765	*
	766	*
	767	* @access private
	768	*/
	769	private $_iResId = null;
	770	private $_iConnection = null;
	771
	772
	773
	774	/**
	775	*
	776	*
	777	* @access private
	778	*/
f1926a	779	public function __construct($iResId, $iConnection) {
2af58c	780	$this->_iResId = $iResId;
MC	781	$this->_iConnection = $iConnection;
	782	}
	783
	784
	785
	786	/**
	787	* get count of result rows
	788	*
	789	* Returns the amount of rows in the result set
	790	*
	791	* @access public
	792	* @return int amount of rows
	793	*/
	794	public function rows() {
	795	if(!is_object($this->_iResId)) return 0;
	796	$iRows = mysqli_num_rows($this->_iResId);
	797	if(!$iRows) $iRows = 0;
	798	return $iRows;
	799	}
	800
	801
	802
	803	/**
	804	* Get number of affected rows
	805	*
	806	* Returns the amount of rows affected by the previous query
	807	*
	808	* @access public
	809	* @return int amount of affected rows
	810	*/
	811	public function affected() {
	812	if(!is_object($this->_iConnection)) return 0;
	813	$iRows = mysqli_affected_rows($this->_iConnection);
	814	if(!$iRows) $iRows = 0;
	815	return $iRows;
	816	}
	817
	818
	819
	820	/**
	821	* Frees the result set
	822	*
	823	* @access public
	824	*/
	825	public function free() {
	826	if(!is_object($this->_iResId)) return;
	827
	828	mysqli_free_result($this->_iResId);
	829	return;
	830	}
	831
	832
	833
	834	/**
	835	* Get a result row (associative)
	836	*
	837	* Returns the next row in the result set. To be used in a while loop like while($currow = $result->get()) { do something ... }
	838	*
	839	* @access public
	840	* @return array result row
	841	*/
	842	public function get() {
	843	$aItem = null;
	844
	845	if(is_object($this->_iResId)) {
	846	$aItem = mysqli_fetch_assoc($this->_iResId);
	847	if(!$aItem) $aItem = null;
	848	}
	849	return $aItem;
	850	}
	851
	852
	853
	854	/**
	855	* Get a result row (array with numeric index)
	856	*
	857	* @access public
	858	* @return array result row
	859	*/
	860	public function getAsRow() {
	861	$aItem = null;
	862
	863	if(is_object($this->_iResId)) {
	864	$aItem = mysqli_fetch_row($this->_iResId);
	865	if(!$aItem) $aItem = null;
	866	}
	867	return $aItem;
	868	}
	869
	870	}
	871
	872	/**
	873	* database query result class
	874	*
	875	* emulates a db result set out of an array so you can use array results and db results the same way
	876	*
	877	* @package pxFramework
	878	* @see db_result
	879	*
	880	*
	881	*/
	882	class fakedb_result {
	883
	884	/**
	885	*
	886	*
	887	* @access private
	888	*/
	889	private $aResultData = array();
	890
	891	/**
	892	*
	893	*
	894	* @access private
	895	*/
	896	private $aLimitedData = array();
	897
	898
	899
	900	/**
	901	*
	902	*
	903	* @access private
	904	*/
f1926a	905	public function __construct($aData) {
2af58c	906	$this->aResultData = $aData;
MC	907	$this->aLimitedData = $aData;
	908	reset($this->aLimitedData);
	909	}
	910
	911
	912
	913	/**
	914	* get count of result rows
	915	*
	916	* Returns the amount of rows in the result set
	917	*
	918	* @access public
	919	* @return int amount of rows
	920	*/
	921	// Gibt die Anzahl Zeilen zurück
	922	public function rows() {
	923	return count($this->aLimitedData);
	924	}
	925
	926
	927
	928	/**
	929	* Frees the result set
	930	*
	931	* @access public
	932	*/
	933	// Gibt ein Ergebnisset frei
	934	public function free() {
	935	$this->aResultData = array();
	936	$this->aLimitedData = array();
	937	return;
	938	}
	939
	940
	941
	942	/**
	943	* Get a result row (associative)
	944	*
	945	* Returns the next row in the result set. To be used in a while loop like while($currow = $result->get()) { do something ... }
	946	*
	947	* @access public
	948	* @return array result row
	949	*/
	950	// Gibt eine Ergebniszeile zurück
	951	public function get() {
	952	$aItem = null;
	953
	954	if(!is_array($this->aLimitedData)) return $aItem;
	955
	956	if(list($vKey, $aItem) = each($this->aLimitedData)) {
	957	if(!$aItem) $aItem = null;
	958	}
	959	return $aItem;
	960	}
	961
	962
	963
	964	/**
	965	* Get a result row (array with numeric index)
	966	*
	967	* @access public
	968	* @return array result row
	969	*/
	970	public function getAsRow() {
	971	return $this->get();
	972	}
	973
	974
	975
	976	/**
	977	* Limit the result (like a LIMIT x,y in a SQL query)
	978	*
	979	* @access public
	980	* @param int $iStart offset to start read
	981	* @param int iLength amount of datasets to read
	982	*/
	983	public function limit_result($iStart, $iLength) {
	984	$this->aLimitedData = array_slice($this->aResultData, $iStart, $iLength, true);
	985	}
	986
	987	}
	988
	989
20218c	990	?>