/***************************************************************
* Copyright notice
*
-* (c) 2004 Kasper Skaarhoj (kasper@typo3.com)
+* (c) 2004-2008 Kasper Skaarhoj (kasperYYYY@typo3.com)
* All rights reserved
*
* This script is part of the TYPO3 project. The TYPO3 project is
*
* $Id$
*
- * @author Kasper Skaarhoj <kasper@typo3.com>
+ * @author Kasper Skaarhoj <kasperYYYY@typo3.com>
*/
/**
* [CLASS/FUNCTION INDEX of SCRIPT]
*
*
*
- * 131: class t3lib_DB
+ * 138: class t3lib_DB
*
* SECTION: Query execution
- * 166: function exec_INSERTquery($table,$fields_values)
- * 182: function exec_UPDATEquery($table,$where,$fields_values)
- * 196: function exec_DELETEquery($table,$where)
- * 215: function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')
- * 240: function exec_SELECT_mm_query($select,$local_table,$mm_table,$foreign_table,$whereClause='',$groupBy='',$orderBy='',$limit='')
- * 261: function exec_SELECT_queryArray($queryParts)
+ * 175: function exec_INSERTquery($table,$fields_values,$no_quote_fields=FALSE)
+ * 192: function exec_UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)
+ * 206: function exec_DELETEquery($table,$where)
+ * 225: function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')
+ * 250: function exec_SELECT_mm_query($select,$local_table,$mm_table,$foreign_table,$whereClause='',$groupBy='',$orderBy='',$limit='')
+ * 278: function exec_SELECT_queryArray($queryParts)
+ * 301: function exec_SELECTgetRows($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='',$uidIndexField='')
*
* SECTION: Query building
- * 298: function INSERTquery($table,$fields_values)
- * 334: function UPDATEquery($table,$where,$fields_values)
- * 373: function DELETEquery($table,$where)
- * 402: function SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')
- * 443: function listQuery($field, $value, $table)
- * 457: function searchQuery($searchWords,$fields,$table)
+ * 346: function INSERTquery($table,$fields_values,$no_quote_fields=FALSE)
+ * 381: function UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)
+ * 422: function DELETEquery($table,$where)
+ * 451: function SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')
+ * 492: function listQuery($field, $value, $table)
+ * 506: function searchQuery($searchWords,$fields,$table)
*
* SECTION: Various helper functions
- * 503: function quoteStr($str, $table)
- * 516: function cleanIntArray($arr)
- * 532: function cleanIntList($list)
- * 546: function stripOrderBy($str)
- * 560: function stripGroupBy($str)
- * 572: function splitGroupOrderLimit($str)
+ * 552: function fullQuoteStr($str, $table)
+ * 569: function fullQuoteArray($arr, $table, $noQuote=FALSE)
+ * 596: function quoteStr($str, $table)
+ * 612: function escapeStrForLike($str, $table)
+ * 625: function cleanIntArray($arr)
+ * 641: function cleanIntList($list)
+ * 655: function stripOrderBy($str)
+ * 669: function stripGroupBy($str)
+ * 681: function splitGroupOrderLimit($str)
*
* SECTION: MySQL wrapper functions
- * 637: function sql($db,$query)
- * 651: function sql_query($query)
- * 664: function sql_error()
- * 676: function sql_num_rows($res)
- * 688: function sql_fetch_assoc($res)
- * 701: function sql_fetch_row($res)
- * 713: function sql_free_result($res)
- * 724: function sql_insert_id()
- * 735: function sql_affected_rows()
- * 748: function sql_data_seek($res,$seek)
- * 761: function sql_field_type($res,$pointer)
- * 775: function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password)
- * 788: function sql_select_db($TYPO3_db)
+ * 749: function sql($db,$query)
+ * 763: function sql_query($query)
+ * 776: function sql_error()
+ * 788: function sql_num_rows($res)
+ * 800: function sql_fetch_assoc($res)
+ * 813: function sql_fetch_row($res)
+ * 825: function sql_free_result($res)
+ * 836: function sql_insert_id()
+ * 847: function sql_affected_rows()
+ * 860: function sql_data_seek($res,$seek)
+ * 873: function sql_field_type($res,$pointer)
+ * 887: function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password)
+ * 915: function sql_select_db($TYPO3_db)
*
* SECTION: SQL admin functions
- * 816: function admin_get_dbs()
- * 834: function admin_get_tables()
- * 853: function admin_get_fields($tableName)
- * 871: function admin_get_keys($tableName)
- * 889: function admin_query($query)
+ * 947: function admin_get_dbs()
+ * 965: function admin_get_tables()
+ * 984: function admin_get_fields($tableName)
+ * 1002: function admin_get_keys($tableName)
+ * 1020: function admin_query($query)
+ *
+ * SECTION: Connecting service
+ * 1048: function connectDB()
*
* SECTION: Debugging
- * 916: function debug($func)
+ * 1086: function debug($func)
*
- * TOTAL FUNCTIONS: 37
+ * TOTAL FUNCTIONS: 42
* (This index is automatically created/updated by the extension "extdeveval")
*
*/
* In all TYPO3 scripts the global variable $TYPO3_DB is an instance of this class. Use that.
* Eg. $GLOBALS['TYPO3_DB']->sql_fetch_assoc()
*
- * @author Kasper Skaarhoj <kasper@typo3.com>
+ * @author Kasper Skaarhoj <kasperYYYY@typo3.com>
* @package TYPO3
* @subpackage t3lib
*/
// Debug:
- var $debugOutput = FALSE; // Set "TRUE" if you want database errors outputted.
+ var $debugOutput = FALSE; // Set "TRUE" if you want database errors outputted.
var $debug_lastBuiltQuery = ''; // Internally: Set to last built query (not necessarily executed...)
+ var $store_lastBuiltQuery = FALSE; // Set "TRUE" if you want the last built query to be stored in $debug_lastBuiltQuery independent of $this->debugOutput
+ var $explainOutput = 0; // Set this to 1 to get queries explained (devIPmask must match). Set the value to 2 to the same but disregarding the devIPmask. There is an alternative option to enable explain output in the admin panel under "TypoScript", which will produce much nicer output, but only works in FE.
// Default link identifier:
- var $link;
+ var $link = FALSE;
+
+ // Default character set, applies unless character set or collation are explicitely set
+ var $default_charset = 'utf8';
*
* @param string Table name
* @param array Field values as key=>value pairs. Values will be escaped internally. Typically you would fill an array like "$insertFields" with 'fieldname'=>'value' and pass it to this function as argument.
+ * @param string/array See fullQuoteArray()
* @return pointer MySQL result pointer / DBAL object
*/
- function exec_INSERTquery($table,$fields_values) {
- $res = mysql_query($this->INSERTquery($table,$fields_values), $this->link);
+ function exec_INSERTquery($table,$fields_values,$no_quote_fields=FALSE) {
+ $res = mysql_query($this->INSERTquery($table,$fields_values,$no_quote_fields), $this->link);
if ($this->debugOutput) $this->debug('exec_INSERTquery');
return $res;
}
* Usage count/core: 50
*
* @param string Database tablename
- * @param string WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->quoteStr() yourself!
+ * @param string WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
* @param array Field values as key=>value pairs. Values will be escaped internally. Typically you would fill an array like "$updateFields" with 'fieldname'=>'value' and pass it to this function as argument.
+ * @param string/array See fullQuoteArray()
* @return pointer MySQL result pointer / DBAL object
*/
- function exec_UPDATEquery($table,$where,$fields_values) {
- $res = mysql_query($this->UPDATEquery($table,$where,$fields_values), $this->link);
+ function exec_UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE) {
+ $res = mysql_query($this->UPDATEquery($table,$where,$fields_values,$no_quote_fields), $this->link);
if ($this->debugOutput) $this->debug('exec_UPDATEquery');
return $res;
}
* Usage count/core: 40
*
* @param string Database tablename
- * @param string WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->quoteStr() yourself!
+ * @param string WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
* @return pointer MySQL result pointer / DBAL object
*/
function exec_DELETEquery($table,$where) {
*
* @param string List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
* @param string Table(s) from which to select. This is what comes right after "FROM ...". Required value.
- * @param string Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->quoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
+ * @param string Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
* @param string Optional GROUP BY field(s), if none, supply blank string.
* @param string Optional ORDER BY field(s), if none, supply blank string.
* @param string Optional LIMIT value ([begin,]max), if none, supply blank string.
* @return pointer MySQL result pointer / DBAL object
*/
function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='') {
- $res = mysql_query($this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit), $this->link);
- if ($this->debugOutput) $this->debug('exec_SELECTquery');
+ $query = $this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit);
+ $res = mysql_query($query, $this->link);
+
+ if ($this->debugOutput) {
+ $this->debug('exec_SELECTquery');
+ }
+ if ($this->explainOutput) {
+ $this->explain($query, $from_table, $this->sql_num_rows($res));
+ }
+
return $res;
}
* @param string Tablename, local table
* @param string Tablename, relation table
* @param string Tablename, foreign table
- * @param string Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->quoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
+ * @param string Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT! You have to prepend 'AND ' to this parameter yourself!
* @param string Optional GROUP BY field(s), if none, supply blank string.
* @param string Optional ORDER BY field(s), if none, supply blank string.
* @param string Optional LIMIT value ([begin,]max), if none, supply blank string.
* @see exec_SELECTquery()
*/
function exec_SELECT_mm_query($select,$local_table,$mm_table,$foreign_table,$whereClause='',$groupBy='',$orderBy='',$limit='') {
+ if($foreign_table == $local_table) {
+ $foreign_table_as = $foreign_table.uniqid('_join');
+ }
+
$mmWhere = $local_table ? $local_table.'.uid='.$mm_table.'.uid_local' : '';
$mmWhere.= ($local_table AND $foreign_table) ? ' AND ' : '';
- $mmWhere.= $foreign_table ? $foreign_table.'.uid='.$mm_table.'.uid_foreign' : '';
+ $mmWhere.= $foreign_table ? ($foreign_table_as ? $foreign_table_as : $foreign_table).'.uid='.$mm_table.'.uid_foreign' : '';
+
return $GLOBALS['TYPO3_DB']->exec_SELECTquery(
$select,
- ($local_table ? $local_table.',' : '').$mm_table.($foreign_table ? ','.$foreign_table : ''),
+ ($local_table ? $local_table.',' : '').$mm_table.($foreign_table ? ','. $foreign_table.($foreign_table_as ? ' AS '.$foreign_table_as : '') : ''),
$mmWhere.' '.$whereClause, // whereClauseMightContainGroupOrderBy
$groupBy,
$orderBy,
);
}
+ /**
+ * Creates and executes a SELECT SQL-statement AND traverse result set and returns array with records in.
+ *
+ * @param string See exec_SELECTquery()
+ * @param string See exec_SELECTquery()
+ * @param string See exec_SELECTquery()
+ * @param string See exec_SELECTquery()
+ * @param string See exec_SELECTquery()
+ * @param string See exec_SELECTquery()
+ * @param string If set, the result array will carry this field names value as index. Requires that field to be selected of course!
+ * @return array Array of rows.
+ */
+ function exec_SELECTgetRows($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='',$uidIndexField='') {
+ $res = $this->exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit);
+ if ($this->debugOutput) $this->debug('exec_SELECTquery');
+
+ if (!$this->sql_error()) {
+ $output = array();
+
+ if ($uidIndexField) {
+ while($tempRow = $this->sql_fetch_assoc($res)) {
+ $output[$tempRow[$uidIndexField]] = $tempRow;
+ }
+ } else {
+ while($output[] = $this->sql_fetch_assoc($res));
+ array_pop($output);
+ }
+ $this->sql_free_result($res);
+ }
+ return $output;
+ }
*
* @param string See exec_INSERTquery()
* @param array See exec_INSERTquery()
+ * @param string/array See fullQuoteArray()
* @return string Full SQL query for INSERT (unless $fields_values does not contain any elements in which case it will be false)
- * @depreciated use exec_INSERTquery() instead if possible!
+ * @deprecated use exec_INSERTquery() instead if possible!
*/
- function INSERTquery($table,$fields_values) {
+ function INSERTquery($table,$fields_values,$no_quote_fields=FALSE) {
// Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
if (is_array($fields_values) && count($fields_values)) {
- // Add slashes old-school:
- foreach($fields_values as $k => $v) {
- $fields_values[$k] = $this->quoteStr($fields_values[$k], $table);
- }
+ // quote and escape values
+ $fields_values = $this->fullQuoteArray($fields_values,$table,$no_quote_fields);
// Build query:
$query = 'INSERT INTO '.$table.'
'.implode(',
',array_keys($fields_values)).'
) VALUES (
- "'.implode('",
- "',$fields_values).'"
+ '.implode(',
+ ',$fields_values).'
)';
// Return query:
- if ($this->debugOutput) $this->debug_lastBuiltQuery = $query;
+ if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
return $query;
}
}
* @param string See exec_UPDATEquery()
* @param string See exec_UPDATEquery()
* @param array See exec_UPDATEquery()
+ * @param array See fullQuoteArray()
* @return string Full SQL query for UPDATE (unless $fields_values does not contain any elements in which case it will be false)
- * @depreciated use exec_UPDATEquery() instead if possible!
+ * @deprecated use exec_UPDATEquery() instead if possible!
*/
- function UPDATEquery($table,$where,$fields_values) {
+ function UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE) {
// Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
if (is_string($where)) {
if (is_array($fields_values) && count($fields_values)) {
- // Add slashes old-school:
- $nArr = array();
- foreach($fields_values as $k => $v) {
- $nArr[] = $k.'="'.$this->quoteStr($v, $table).'"';
+ // quote and escape values
+ $nArr = $this->fullQuoteArray($fields_values,$table,$no_quote_fields);
+
+ $fields = array();
+ foreach ($nArr as $k => $v) {
+ $fields[] = $k.'='.$v;
}
// Build query:
$query = 'UPDATE '.$table.'
SET
'.implode(',
- ',$nArr).
+ ',$fields).
(strlen($where)>0 ? '
WHERE
'.$where : '');
// Return query:
- if ($this->debugOutput) $this->debug_lastBuiltQuery = $query;
+ if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
return $query;
}
} else {
* @param string See exec_DELETEquery()
* @param string See exec_DELETEquery()
* @return string Full SQL query for DELETE
- * @depreciated use exec_DELETEquery() instead if possible!
+ * @deprecated use exec_DELETEquery() instead if possible!
*/
function DELETEquery($table,$where) {
if (is_string($where)) {
WHERE
'.$where : '');
- if ($this->debugOutput) $this->debug_lastBuiltQuery = $query;
+ if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
return $query;
} else {
die('<strong>TYPO3 Fatal Error:</strong> "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !');
* @param string See exec_SELECTquery()
* @param string See exec_SELECTquery()
* @return string Full SQL query for SELECT
- * @depreciated use exec_SELECTquery() instead if possible!
+ * @deprecated use exec_SELECTquery() instead if possible!
*/
function SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='') {
}
// Return query:
- if ($this->debugOutput) $this->debug_lastBuiltQuery = $query;
+ if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
return $query;
}
*/
function listQuery($field, $value, $table) {
$command = $this->quoteStr($value, $table);
- $where = '('.$field.' LIKE "%,'.$command.',%" OR '.$field.' LIKE "'.$command.',%" OR '.$field.' LIKE "%,'.$command.'" OR '.$field.'="'.$command.'")';
+ $where = '('.$field.' LIKE \'%,'.$command.',%\' OR '.$field.' LIKE \''.$command.',%\' OR '.$field.' LIKE \'%,'.$command.'\' OR '.$field.'=\''.$command.'\')';
return $where;
}
$queryParts = array();
foreach($searchWords as $sw) {
- $like=' LIKE "%'.$this->quoteStr($sw, $table).'%"';
+ $like=' LIKE \'%'.$this->quoteStr($sw, $table).'%\'';
$queryParts[] = $table.'.'.implode($like.' OR '.$table.'.',$fields).$like;
}
$query = '('.implode(') AND (',$queryParts).')';
**************************************/
/**
+ * Escaping and quoting values for SQL statements.
+ * Usage count/core: 100
+ *
+ * @param string Input string
+ * @param string Table name for which to quote string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
+ * @return string Output string; Wrapped in single quotes and quotes in the string (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
+ * @see quoteStr()
+ */
+ function fullQuoteStr($str, $table) {
+ return '\''.mysql_real_escape_string($str, $this->link).'\'';
+ }
+
+ /**
+ * Will fullquote all values in the one-dimensional array so they are ready to "implode" for an sql query.
+ *
+ * @param array Array with values (either associative or non-associative array)
+ * @param string Table name for which to quote
+ * @param string/array List/array of keys NOT to quote (eg. SQL functions) - ONLY for associative arrays
+ * @return array The input array with the values quoted
+ * @see cleanIntArray()
+ */
+ function fullQuoteArray($arr, $table, $noQuote=FALSE) {
+ if (is_string($noQuote)) {
+ $noQuote = explode(',',$noQuote);
+ } elseif (!is_array($noQuote)) { // sanity check
+ $noQuote = FALSE;
+ }
+
+ foreach($arr as $k => $v) {
+ if ($noQuote===FALSE || !in_array($k,$noQuote)) {
+ $arr[$k] = $this->fullQuoteStr($v, $table);
+ }
+ }
+ return $arr;
+ }
+
+ /**
* Substitution for PHP function "addslashes()"
* Use this function instead of the PHP addslashes() function when you build queries - this will prepare your code for DBAL.
- * Usage count/core: 105
+ * NOTICE: You must wrap the output of this function in SINGLE QUOTES to be DBAL compatible. Unless you have to apply the single quotes yourself you should rather use ->fullQuoteStr()!
+ *
+ * Usage count/core: 20
*
* @param string Input string
* @param string Table name for which to quote string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
* @return string Output string; Quotes (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
+ * @see quoteStr()
*/
function quoteStr($str, $table) {
- return addslashes($str);
+ return mysql_real_escape_string($str, $this->link);
}
/**
- * Will convert all values in the one-dimentional array to integers.
+ * Escaping values for SQL LIKE statements.
+ *
+ * @param string Input string
+ * @param string Table name for which to escape string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
+ * @return string Output string; % and _ will be escaped with \ (or otherwise based on DBAL handler)
+ * @see quoteStr()
+ */
+ function escapeStrForLike($str, $table) {
+ return preg_replace('/[_%]/','\\\$0',$str);
+ }
+
+ /**
+ * Will convert all values in the one-dimensional array to integers.
* Useful when you want to make sure an array contains only integers before imploding them in a select-list.
* Usage count/core: 7
*
* @see exec_SELECTquery(), stripGroupBy()
*/
function stripOrderBy($str) {
- return eregi_replace('^ORDER[[:space:]]+BY[[:space:]]+','',trim($str));
+ return preg_replace('/^ORDER[[:space:]]+BY[[:space:]]+/i','',trim($str));
}
/**
* @see exec_SELECTquery(), stripOrderBy()
*/
function stripGroupBy($str) {
- return eregi_replace('^GROUP[[:space:]]+BY[[:space:]]+','',trim($str));
+ return preg_replace('/^GROUP[[:space:]]+BY[[:space:]]+/i','',trim($str));
}
/**
);
// Find LIMIT:
- if (eregi('^(.*)[[:space:]]+LIMIT[[:space:]]+([[:alnum:][:space:],._]+)$',$str,$reg)) {
+ $reg = array();
+ if (preg_match('/^(.*)[[:space:]]+LIMIT[[:space:]]+([[:alnum:][:space:],._]+)$/i',$str,$reg)) {
$wgolParts['LIMIT'] = trim($reg[2]);
$str = $reg[1];
}
// Find ORDER BY:
- if (eregi('^(.*)[[:space:]]+ORDER[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$',$str,$reg)) {
+ $reg = array();
+ if (preg_match('/^(.*)[[:space:]]+ORDER[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i',$str,$reg)) {
$wgolParts['ORDERBY'] = trim($reg[2]);
$str = $reg[1];
}
// Find GROUP BY:
- if (eregi('^(.*)[[:space:]]+GROUP[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$',$str,$reg)) {
+ $reg = array();
+ if (preg_match('/^(.*)[[:space:]]+GROUP[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i',$str,$reg)) {
$wgolParts['GROUPBY'] = trim($reg[2]);
$str = $reg[1];
}
/**
* Executes query
* mysql() wrapper function
- * DEPRECIATED - use exec_* functions from this class instead!
+ * DEPRECATED - use exec_* functions from this class instead!
* Usage count/core: 9
*
* @param string Database name
*/
function sql($db,$query) {
$res = mysql_query($query, $this->link);
- if ($this->debugOutput) $this->debug('sql');
+ if ($this->debugOutput) $this->debug('sql',$query);
return $res;
}
*/
function sql_query($query) {
$res = mysql_query($query, $this->link);
- if ($this->debugOutput) $this->debug('sql_query');
+ if ($this->debugOutput) $this->debug('sql_query',$query);
return $res;
}
}
/**
+ * Returns the error number on the last sql() execution
+ * mysql_errno() wrapper function
+ *
+ * @return int MySQL error number.
+ */
+ function sql_errno() {
+ return mysql_errno($this->link);
+ }
+
+ /**
* Returns the number of selected rows.
* mysql_num_rows() wrapper function
* Usage count/core: 85
*
* @param pointer MySQL result pointer (of SELECT query) / DBAL object
- * @return integer Number of resulting rows.
+ * @return integer Number of resulting rows
*/
function sql_num_rows($res) {
+ $this->debug_check_recordset($res);
return mysql_num_rows($res);
}
* @return array Associative array of result row.
*/
function sql_fetch_assoc($res) {
+ $this->debug_check_recordset($res);
return mysql_fetch_assoc($res);
}
* @return array Array with result rows.
*/
function sql_fetch_row($res) {
+ $this->debug_check_recordset($res);
return mysql_fetch_row($res);
}
* @return boolean Returns TRUE on success or FALSE on failure.
*/
function sql_free_result($res) {
+ $this->debug_check_recordset($res);
return mysql_free_result($res);
}
* @return boolean Returns TRUE on success or FALSE on failure.
*/
function sql_data_seek($res,$seek) {
+ $this->debug_check_recordset($res);
return mysql_data_seek($res,$seek);
}
* @return string Returns the name of the specified field index
*/
function sql_field_type($res,$pointer) {
+ $this->debug_check_recordset($res);
return mysql_field_type($res,$pointer);
}
* @return pointer Returns a positive MySQL persistent link identifier on success, or FALSE on error.
*/
function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password) {
- $this->link = mysql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
+ // mysql_error() is tied to an established connection
+ // if the connection fails we need a different method to get the error message
+ @ini_set('track_errors', 1);
+ @ini_set('html_errors', 0);
+ if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['no_pconnect']) {
+ $this->link = @mysql_connect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
+ } else {
+ $this->link = @mysql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
+ }
+ $error_msg = $php_errormsg;
+ @ini_restore('track_errors');
+ @ini_restore('html_errors');
+
+ if (!$this->link) {
+ t3lib_div::sysLog('Could not connect to MySQL server '.$TYPO3_db_host.' with user '.$TYPO3_db_username.': '.$error_msg,'Core',4);
+ } else {
+ $setDBinit = t3lib_div::trimExplode(chr(10), $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'],TRUE);
+ foreach ($setDBinit as $v) {
+ if (mysql_query($v, $this->link) === FALSE) {
+ t3lib_div::sysLog('Could not initialize DB connection with query "'.$v.'": '.mysql_error($this->link),'Core',3);
+ }
+ }
+ }
+
return $this->link;
}
* @return boolean Returns TRUE on success or FALSE on failure.
*/
function sql_select_db($TYPO3_db) {
- return mysql_select_db($TYPO3_db, $this->link);
+ $ret = @mysql_select_db($TYPO3_db, $this->link);
+ if (!$ret) {
+ t3lib_div::sysLog('Could not select MySQL database '.$TYPO3_db.': '.mysql_error(),'Core',4);
+ }
+ return $ret;
}
/**
* Listing databases from current MySQL connection. NOTICE: It WILL try to select those databases and thus break selection of current database.
- * This doesn't really make sense to transfer to a DBAL layer - this detection is also PRE-DBAL in many ways since it is only used as a service function in the 1-2-3 process of the Install Tool. In any case a lookup should be done in the _DEFAULT handler DBMS then.
+ * This is only used as a service function in the (1-2-3 process) of the Install Tool. In any case a lookup should be done in the _DEFAULT handler DBMS then.
* Use in Install Tool only!
* Usage count/core: 1
*
* In a DBAL this method should 1) look up all tables from the DBMS of the _DEFAULT handler and then 2) add all tables *configured* to be managed by other handlers
* Usage count/core: 2
*
- * @return array Tables in an array (tablename is in both key and value)
+ * @return array Array with tablenames as key and arrays with status information as value
*/
function admin_get_tables() {
$whichTables = array();
- $tables_result = mysql_list_tables(TYPO3_db, $this->link);
+
+ $tables_result = mysql_query('SHOW TABLE STATUS FROM `'.TYPO3_db.'`', $this->link);
if (!mysql_error()) {
while ($theTable = mysql_fetch_assoc($tables_result)) {
- $whichTables[current($theTable)] = current($theTable);
+ $whichTables[$theTable['Name']] = $theTable;
}
}
+
return $whichTables;
}
/**
* Returns information about each field in the $table (quering the DBMS)
* In a DBAL this should look up the right handler for the table and return compatible information
- * This function is important not only for the Install Tool but probably for DBALs as well since they might need to look up table specific information in order to construct correct queries. In such cases this information should probably be cached for quick delivery
+ * This function is important not only for the Install Tool but probably for DBALs as well since they might need to look up table specific information in order to construct correct queries. In such cases this information should probably be cached for quick delivery.
*
* @param string Table name
* @return array Field information in an associative array with fieldname => field row
function admin_get_fields($tableName) {
$output = array();
- $columns_res = mysql_query('SHOW columns FROM '.$tableName, $this->link);
+ $columns_res = mysql_query('SHOW COLUMNS FROM `'.$tableName.'`', $this->link);
while($fieldRow = mysql_fetch_assoc($columns_res)) {
- $output[$fieldRow["Field"]] = $fieldRow;
+ $output[$fieldRow['Field']] = $fieldRow;
}
return $output;
function admin_get_keys($tableName) {
$output = array();
- $keyRes = mysql_query('SHOW keys FROM '.$tableName, $this->link);
+ $keyRes = mysql_query('SHOW KEYS FROM `'.$tableName.'`', $this->link);
while($keyRow = mysql_fetch_assoc($keyRes)) {
$output[] = $keyRow;
}
}
/**
+ * Returns information about the character sets supported by the current DBM
+ * This function is important not only for the Install Tool but probably for DBALs as well since they might need to look up table specific information in order to construct correct queries. In such cases this information should probably be cached for quick delivery.
+ *
+ * This is used by the Install Tool to convert tables tables with non-UTF8 charsets
+ * Use in Install Tool only!
+ *
+ * @return array Array with Charset as key and an array of "Charset", "Description", "Default collation", "Maxlen" as values
+ */
+ function admin_get_charsets() {
+ $output = array();
+
+ $columns_res = mysql_query('SHOW CHARACTER SET', $this->link);
+ if ($columns_res) {
+ while (($row = mysql_fetch_assoc($columns_res))) {
+ $output[$row['Charset']] = $row;
+ }
+ }
+
+ return $output;
+ }
+
+ /**
* mysql() wrapper function, used by the Install Tool and EM for all queries regarding management of the database!
* Usage count/core: 10
*
*/
function admin_query($query) {
$res = mysql_query($query, $this->link);
- if ($this->debugOutput) $this->debug('admin_query');
+ if ($this->debugOutput) $this->debug('admin_query',$query);
return $res;
}
+
+
+ /******************************
+ *
+ * Connecting service
+ *
+ ******************************/
+
+ /**
+ * Connects to database for TYPO3 sites:
+ *
+ * @return void
+ */
+ function connectDB() {
+ if ($this->sql_pconnect(TYPO3_db_host, TYPO3_db_username, TYPO3_db_password)) {
+ if (!TYPO3_db) {
+ die('No database selected');
+ exit;
+ } elseif (!$this->sql_select_db(TYPO3_db)) {
+ die('Cannot connect to the current database, "'.TYPO3_db.'"');
+ exit;
+ }
+ } else {
+ die('The current username, password or host was not accepted when the connection to the database was attempted to be established!');
+ exit;
+ }
+ }
+
+
+
+
+
+
+
+
+
+
+
+
/******************************
*
* Debugging
* Debug function: Outputs error if any
*
* @param string Function calling debug()
+ * @param string Last query if not last built query
* @return void
*/
- function debug($func) {
+ function debug($func, $query='') {
$error = $this->sql_error();
- if ($error) {
- echo t3lib_div::view_array(array(
+ if ($error) {
+ debug(array(
'caller' => 't3lib_DB::'.$func,
'ERROR' => $error,
- 'lastBuiltQuery' => $this->debug_lastBuiltQuery,
- 'debug_backtrace' => function_exists('debug_backtrace') ? next(debug_backtrace()) : 'N/A'
- ));
+ 'lastBuiltQuery' => ($query ? $query : $this->debug_lastBuiltQuery),
+ 'debug_backtrace' => t3lib_div::debug_trail()
+ ), 'SQL debug');
}
}
+
+ /**
+ * Checks if recordset is valid and writes debugging inormation into devLog if not.
+ *
+ * @param resource $res Recordset
+ * @return boolean <code>false</code> if recordset is not valid
+ */
+ function debug_check_recordset($res) {
+ if (!$res) {
+ $trace = FALSE;
+ $msg = 'Invalid database result resource detected';
+ $trace = debug_backtrace();
+ array_shift($trace);
+ $cnt = count($trace);
+ for ($i=0; $i<$cnt; $i++) {
+ // complete objects are too large for the log
+ if (isset($trace['object'])) unset($trace['object']);
+ }
+ $msg .= ': function t3lib_DB->' . $trace[0]['function'] . ' called from file ' . substr($trace[0]['file'],strlen(PATH_site)+2) . ' in line ' . $trace[0]['line'];
+ t3lib_div::sysLog($msg.'. Use a devLog extension to get more details.', 'Core/t3lib_db', 3);
+ t3lib_div::devLog($msg.'.', 'Core/t3lib_db', 3, $trace);
+
+ return FALSE;
+ }
+ return TRUE;
+ }
+
+ /**
+ * Explain select queries
+ * If $this->explainOutput is set, SELECT queries will be explained here. Only queries with more than one possible result row will be displayed.
+ * The output is either printed as raw HTML output or embedded into the TS admin panel (checkbox must be enabled!)
+ *
+ * TODO: Feature is not DBAL-compliant
+ *
+ * @param string SQL query
+ * @param string Table(s) from which to select. This is what comes right after "FROM ...". Required value.
+ * @param integer Number of resulting rows
+ * @return boolean True if explain was run, false otherwise
+ */
+ protected function explain($query,$from_table,$row_count) {
+
+ if ((int)$this->explainOutput==1 || ((int)$this->explainOutput==2 && t3lib_div::cmpIP(t3lib_div::getIndpEnv('REMOTE_ADDR'), $GLOBALS['TYPO3_CONF_VARS']['SYS']['devIPmask']))) {
+ $explainMode = 1; // raw HTML output
+ } elseif ((int)$this->explainOutput==3 && is_object($GLOBALS['TT'])) {
+ $explainMode = 2; // embed the output into the TS admin panel
+ } else {
+ return false;
+ }
+
+ $error = $GLOBALS['TYPO3_DB']->sql_error();
+ $trail = t3lib_div::debug_trail();
+
+ $explain_tables = array();
+ $explain_output = array();
+ $res = $this->sql_query('EXPLAIN '.$query, $this->link);
+ if (is_resource($res)) {
+ while ($tempRow = $this->sql_fetch_assoc($res)) {
+ $explain_output[] = $tempRow;
+ $explain_tables[] = $tempRow['table'];
+ }
+ $this->sql_free_result($res);
+ }
+
+ $indices_output = array();
+ if ($explain_output[0]['rows']>1 || t3lib_div::inList('ALL',$explain_output[0]['type'])) { // Notice: Rows are skipped if there is only one result, or if no conditions are set
+ $debug = true; // only enable output if it's really useful
+
+ foreach ($explain_tables as $table) {
+ $res = $this->sql_query('SHOW INDEX FROM '.$table, $this->link);
+ if (is_resource($res)) {
+ while ($tempRow = $this->sql_fetch_assoc($res)) {
+ $indices_output[] = $tempRow;
+ }
+ $this->sql_free_result($res);
+ }
+ }
+ } else {
+ $debug = false;
+ }
+
+ if ($debug) {
+ if ($explainMode==1) {
+ t3lib_div::debug('QUERY: '.$query);
+ t3lib_div::debug(array('Debug trail:'=>$trail), 'Row count: '.$row_count);
+
+ if ($error) {
+ t3lib_div::debug($error);
+ }
+ if (count($explain_output)) {
+ t3lib_div::debug($explain_output);
+ }
+ if (count($indices_output)) {
+ t3lib_div::debugRows($indices_output);
+ }
+
+ } elseif ($explainMode==2) {
+ $data = array();
+ $data['query'] = $query;
+ $data['trail'] = $trail;
+ $data['row_count'] = $row_count;
+
+ if ($error) {
+ $data['error'] = $error;
+ }
+ if (count($explain_output)) {
+ $data['explain'] = $explain_output;
+ }
+ if (count($indices_output)) {
+ $data['indices'] = $indices_output;
+ }
+ $GLOBALS['TT']->setTSselectQuery($data);
+ }
+ return true;
+ }
+
+ return false;
+ }
+
}
projects / Packages / TYPO3.CMS.git / blobdiff