Documentation TYPO3 par Ameos

class.t3lib_db.php

00001 <?php
00002 /***************************************************************
00003 *  Copyright notice
00004 *
00005 *  (c) 2004-2007 Kasper Skaarhoj (kasperYYYY@typo3.com)
00006 *  All rights reserved
00007 *
00008 *  This script is part of the TYPO3 project. The TYPO3 project is
00009 *  free software; you can redistribute it and/or modify
00010 *  it under the terms of the GNU General Public License as published by
00011 *  the Free Software Foundation; either version 2 of the License, or
00012 *  (at your option) any later version.
00013 *
00014 *  The GNU General Public License can be found at
00015 *  http://www.gnu.org/copyleft/gpl.html.
00016 *  A copy is found in the textfile GPL.txt and important notices to the license
00017 *  from the author is found in LICENSE.txt distributed with these scripts.
00018 *
00019 *
00020 *  This script is distributed in the hope that it will be useful,
00021 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
00022 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
00023 *  GNU General Public License for more details.
00024 *
00025 *  This copyright notice MUST APPEAR in all copies of the script!
00026 ***************************************************************/
00138 class t3lib_DB {
00139 
00140 
00141                 // Debug:
00142         var $debugOutput = FALSE;               // Set "TRUE" if you want database errors outputted.
00143         var $debug_lastBuiltQuery = '';         // Internally: Set to last built query (not necessarily executed...)
00144         var $store_lastBuiltQuery = FALSE;      // Set "TRUE" if you want the last built query to be stored in $debug_lastBuiltQuery independent of $this->debugOutput
00145 
00146                 // Default link identifier:
00147         var $link = FALSE;
00148 
00149 
00150 
00151 
00152         /************************************
00153          *
00154          * Query execution
00155          *
00156          * These functions are the RECOMMENDED DBAL functions for use in your applications
00157          * Using these functions will allow the DBAL to use alternative ways of accessing data (contrary to if a query is returned!)
00158          * They compile a query AND execute it immediately and then return the result
00159          * This principle heightens our ability to create various forms of DBAL of the functions.
00160          * Generally: We want to return a result pointer/object, never queries.
00161          * Also, having the table name together with the actual query execution allows us to direct the request to other databases.
00162          *
00163          **************************************/
00164 
00175         function exec_INSERTquery($table,$fields_values,$no_quote_fields=FALSE) {
00176                 $res = mysql_query($this->INSERTquery($table,$fields_values,$no_quote_fields), $this->link);
00177                 if ($this->debugOutput) $this->debug('exec_INSERTquery');
00178                 return $res;
00179         }
00180 
00192         function exec_UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)  {
00193                 $res = mysql_query($this->UPDATEquery($table,$where,$fields_values,$no_quote_fields), $this->link);
00194                 if ($this->debugOutput) $this->debug('exec_UPDATEquery');
00195                 return $res;
00196         }
00197 
00206         function exec_DELETEquery($table,$where)        {
00207                 $res = mysql_query($this->DELETEquery($table,$where), $this->link);
00208                 if ($this->debugOutput) $this->debug('exec_DELETEquery');
00209                 return $res;
00210         }
00211 
00225         function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')   {
00226                 $res = mysql_query($this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit), $this->link);
00227                 if ($this->debugOutput) $this->debug('exec_SELECTquery');
00228                 return $res;
00229         }
00230 
00250         function exec_SELECT_mm_query($select,$local_table,$mm_table,$foreign_table,$whereClause='',$groupBy='',$orderBy='',$limit='')  {
00251                 if($foreign_table == $local_table) {
00252                         $foreign_table_as = $foreign_table.uniqid('_join');
00253                 }
00254 
00255                 $mmWhere = $local_table ? $local_table.'.uid='.$mm_table.'.uid_local' : '';
00256                 $mmWhere.= ($local_table AND $foreign_table) ? ' AND ' : '';
00257                 $mmWhere.= $foreign_table ? ($foreign_table_as ? $foreign_table_as : $foreign_table).'.uid='.$mm_table.'.uid_foreign' : '';
00258 
00259                 return $GLOBALS['TYPO3_DB']->exec_SELECTquery(
00260                                         $select,
00261                                         ($local_table ? $local_table.',' : '').$mm_table.($foreign_table ? ','. $foreign_table.($foreign_table_as ? ' AS '.$foreign_table_as : '') : ''),
00262                                         $mmWhere.' '.$whereClause,              // whereClauseMightContainGroupOrderBy
00263                                         $groupBy,
00264                                         $orderBy,
00265                                         $limit
00266                                 );
00267         }
00268 
00278         function exec_SELECT_queryArray($queryParts)    {
00279                 return $this->exec_SELECTquery(
00280                                         $queryParts['SELECT'],
00281                                         $queryParts['FROM'],
00282                                         $queryParts['WHERE'],
00283                                         $queryParts['GROUPBY'],
00284                                         $queryParts['ORDERBY'],
00285                                         $queryParts['LIMIT']
00286                                 );
00287         }
00288 
00301         function exec_SELECTgetRows($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='',$uidIndexField='')       {
00302                 $res = $this->exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit);
00303                 if ($this->debugOutput) $this->debug('exec_SELECTquery');
00304 
00305                 if (!$this->sql_error())        {
00306                         $output = array();
00307 
00308                         if ($uidIndexField)     {
00309                                 while($tempRow = $this->sql_fetch_assoc($res))  {
00310                                         $output[$tempRow[$uidIndexField]] = $tempRow;
00311                                 }
00312                         } else {
00313                                 while($output[] = $this->sql_fetch_assoc($res));
00314                                 array_pop($output);
00315                         }
00316                         $this->sql_free_result($res);
00317                 }
00318                 return $output;
00319         }
00320 
00321 
00322 
00323 
00324 
00325 
00326 
00327 
00328 
00329 
00330 
00331         /**************************************
00332          *
00333          * Query building
00334          *
00335          **************************************/
00336 
00347         function INSERTquery($table,$fields_values,$no_quote_fields=FALSE)      {
00348 
00349                         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
00350                 if (is_array($fields_values) && count($fields_values))  {
00351 
00352                                 // quote and escape values
00353                         $fields_values = $this->fullQuoteArray($fields_values,$table,$no_quote_fields);
00354 
00355                                 // Build query:
00356                         $query = 'INSERT INTO '.$table.'
00357                                 (
00358                                         '.implode(',
00359                                         ',array_keys($fields_values)).'
00360                                 ) VALUES (
00361                                         '.implode(',
00362                                         ',$fields_values).'
00363                                 )';
00364 
00365                                 // Return query:
00366                         if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
00367                         return $query;
00368                 }
00369         }
00370 
00382         function UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)       {
00383 
00384                         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
00385                 if (is_string($where))  {
00386                         if (is_array($fields_values) && count($fields_values))  {
00387 
00388                                         // quote and escape values
00389                                 $nArr = $this->fullQuoteArray($fields_values,$table,$no_quote_fields);
00390 
00391                                 $fields = array();
00392                                 foreach ($nArr as $k => $v) {
00393                                         $fields[] = $k.'='.$v;
00394                                 }
00395 
00396                                         // Build query:
00397                                 $query = 'UPDATE '.$table.'
00398                                         SET
00399                                                 '.implode(',
00400                                                 ',$fields).
00401                                         (strlen($where)>0 ? '
00402                                         WHERE
00403                                                 '.$where : '');
00404 
00405                                         // Return query:
00406                                 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
00407                                 return $query;
00408                         }
00409                 } else {
00410                         die('<strong>TYPO3 Fatal Error:</strong> "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !');
00411                 }
00412         }
00413 
00423         function DELETEquery($table,$where)     {
00424                 if (is_string($where))  {
00425 
00426                                 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
00427                         $query = 'DELETE FROM '.$table.
00428                                 (strlen($where)>0 ? '
00429                                 WHERE
00430                                         '.$where : '');
00431 
00432                         if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
00433                         return $query;
00434                 } else {
00435                         die('<strong>TYPO3 Fatal Error:</strong> "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !');
00436                 }
00437         }
00438 
00452         function SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')        {
00453 
00454                         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
00455                         // Build basic query:
00456                 $query = 'SELECT '.$select_fields.'
00457                         FROM '.$from_table.
00458                         (strlen($where_clause)>0 ? '
00459                         WHERE
00460                                 '.$where_clause : '');
00461 
00462                         // Group by:
00463                 if (strlen($groupBy)>0) {
00464                         $query.= '
00465                         GROUP BY '.$groupBy;
00466                 }
00467                         // Order by:
00468                 if (strlen($orderBy)>0) {
00469                         $query.= '
00470                         ORDER BY '.$orderBy;
00471                 }
00472                         // Group by:
00473                 if (strlen($limit)>0)   {
00474                         $query.= '
00475                         LIMIT '.$limit;
00476                 }
00477 
00478                         // Return query:
00479                 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
00480                 return $query;
00481         }
00482 
00493         function listQuery($field, $value, $table)      {
00494                 $command = $this->quoteStr($value, $table);
00495                 $where = '('.$field.' LIKE \'%,'.$command.',%\' OR '.$field.' LIKE \''.$command.',%\' OR '.$field.' LIKE \'%,'.$command.'\' OR '.$field.'=\''.$command.'\')';
00496                 return $where;
00497         }
00498 
00507         function searchQuery($searchWords,$fields,$table)       {
00508                 $queryParts = array();
00509 
00510                 foreach($searchWords as $sw)    {
00511                         $like=' LIKE \'%'.$this->quoteStr($sw, $table).'%\'';
00512                         $queryParts[] = $table.'.'.implode($like.' OR '.$table.'.',$fields).$like;
00513                 }
00514                 $query = '('.implode(') AND (',$queryParts).')';
00515                 return $query ;
00516         }
00517 
00518 
00519 
00520 
00521 
00522 
00523 
00524 
00525 
00526 
00527 
00528 
00529 
00530 
00531 
00532 
00533         /**************************************
00534          *
00535          * Various helper functions
00536          *
00537          * Functions recommended to be used for
00538          * - escaping values,
00539          * - cleaning lists of values,
00540          * - stripping of excess ORDER BY/GROUP BY keywords
00541          *
00542          **************************************/
00543 
00553         function fullQuoteStr($str, $table)     {
00554                 return '\''.mysql_real_escape_string($str, $this->link).'\'';
00555         }
00556 
00566         function fullQuoteArray($arr, $table, $noQuote=FALSE)   {
00567                 if (is_string($noQuote))        {
00568                         $noQuote = explode(',',$noQuote);
00569                 } elseif (!is_array($noQuote))  {       // sanity check
00570                         $noQuote = FALSE;
00571                 }
00572 
00573                 foreach($arr as $k => $v)       {
00574                         if ($noQuote===FALSE || !in_array($k,$noQuote))     {
00575                                 $arr[$k] = $this->fullQuoteStr($v, $table);
00576                         }
00577                 }
00578                 return $arr;
00579         }
00580 
00593         function quoteStr($str, $table) {
00594                 return mysql_real_escape_string($str, $this->link);
00595         }
00596 
00605         function escapeStrForLike($str, $table) {
00606                 return preg_replace('/[_%]/','\\\$0',$str);
00607         }
00608 
00618         function cleanIntArray($arr)    {
00619                 foreach($arr as $k => $v)       {
00620                         $arr[$k] = intval($arr[$k]);
00621                 }
00622                 return $arr;
00623         }
00624 
00634         function cleanIntList($list)    {
00635                 return implode(',',t3lib_div::intExplode(',',$list));
00636         }
00637 
00648         function stripOrderBy($str)     {
00649                 return preg_replace('/^ORDER[[:space:]]+BY[[:space:]]+/i','',trim($str));
00650         }
00651 
00662         function stripGroupBy($str)     {
00663                 return preg_replace('/^GROUP[[:space:]]+BY[[:space:]]+/i','',trim($str));
00664         }
00665 
00674         function splitGroupOrderLimit($str)     {
00675                 $str = ' '.$str;        // Prepending a space to make sure "[[:space:]]+" will find a space there for the first element.
00676                         // Init output array:
00677                 $wgolParts = array(
00678                         'WHERE' => '',
00679                         'GROUPBY' => '',
00680                         'ORDERBY' => '',
00681                         'LIMIT' => ''
00682                 );
00683 
00684                         // Find LIMIT:
00685                 $reg = array();
00686                 if (preg_match('/^(.*)[[:space:]]+LIMIT[[:space:]]+([[:alnum:][:space:],._]+)$/i',$str,$reg))   {
00687                         $wgolParts['LIMIT'] = trim($reg[2]);
00688                         $str = $reg[1];
00689                 }
00690 
00691                         // Find ORDER BY:
00692                 $reg = array();
00693                 if (preg_match('/^(.*)[[:space:]]+ORDER[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i',$str,$reg))     {
00694                         $wgolParts['ORDERBY'] = trim($reg[2]);
00695                         $str = $reg[1];
00696                 }
00697 
00698                         // Find GROUP BY:
00699                 $reg = array();
00700                 if (preg_match('/^(.*)[[:space:]]+GROUP[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i',$str,$reg))     {
00701                         $wgolParts['GROUPBY'] = trim($reg[2]);
00702                         $str = $reg[1];
00703                 }
00704 
00705                         // Rest is assumed to be "WHERE" clause:
00706                 $wgolParts['WHERE'] = $str;
00707 
00708                 return $wgolParts;
00709         }
00710 
00711 
00712 
00713 
00714 
00715 
00716 
00717 
00718 
00719 
00720 
00721 
00722 
00723 
00724 
00725         /**************************************
00726          *
00727          * MySQL wrapper functions
00728          * (For use in your applications)
00729          *
00730          **************************************/
00731 
00742         function sql($db,$query)        {
00743                 $res = mysql_query($query, $this->link);
00744                 if ($this->debugOutput) $this->debug('sql');
00745                 return $res;
00746         }
00747 
00756         function sql_query($query)      {
00757                 $res = mysql_query($query, $this->link);
00758                 if ($this->debugOutput) $this->debug('sql_query');
00759                 return $res;
00760         }
00761 
00769         function sql_error()    {
00770                 return mysql_error($this->link);
00771         }
00772 
00781         function sql_num_rows($res)     {
00782                 $this->debug_check_recordset($res);
00783                 return mysql_num_rows($res);
00784         }
00785 
00794         function sql_fetch_assoc($res)  {
00795                 $this->debug_check_recordset($res);
00796                 return mysql_fetch_assoc($res);
00797         }
00798 
00808         function sql_fetch_row($res)    {
00809                 $this->debug_check_recordset($res);
00810                 return mysql_fetch_row($res);
00811         }
00812 
00821         function sql_free_result($res)  {
00822                 $this->debug_check_recordset($res);
00823                 return mysql_free_result($res);
00824         }
00825 
00833         function sql_insert_id()        {
00834                 return mysql_insert_id($this->link);
00835         }
00836 
00844         function sql_affected_rows()    {
00845                 return mysql_affected_rows($this->link);
00846         }
00847 
00857         function sql_data_seek($res,$seek)      {
00858                 $this->debug_check_recordset($res);
00859                 return mysql_data_seek($res,$seek);
00860         }
00861 
00871         function sql_field_type($res,$pointer)  {
00872                 $this->debug_check_recordset($res);
00873                 return mysql_field_type($res,$pointer);
00874         }
00875 
00886         function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password)   {
00887                 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['no_pconnect'])  {
00888                         $this->link = @mysql_connect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
00889                 } else {
00890                         $this->link = @mysql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
00891                 }
00892 
00893                 if (!$this->link) {
00894                         t3lib_div::sysLog('Could not connect to Mysql server '.$TYPO3_db_host.' with user '.$TYPO3_db_username.'.','Core',4);
00895                 } else {
00896                         $setDBinit = t3lib_div::trimExplode(chr(10), $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'],TRUE);
00897                         foreach ($setDBinit as $v)      {
00898                                 if (mysql_query($v, $this->link) === FALSE)     {
00899                                         t3lib_div::sysLog('Could not initialize DB connection with query "'.$v.'": '.mysql_error($this->link),'Core',3);
00900                                 }
00901                         }
00902                 }
00903                 return $this->link;
00904         }
00905 
00914         function sql_select_db($TYPO3_db)       {
00915                 $ret = @mysql_select_db($TYPO3_db, $this->link);
00916                 if (!$ret) {
00917                         t3lib_div::sysLog('Could not select Mysql database '.$TYPO3_db.': '.mysql_error(),'Core',4);
00918         }
00919                 return $ret;
00920         }
00921 
00922 
00923 
00924 
00925 
00926 
00927 
00928 
00929 
00930 
00931         /**************************************
00932          *
00933          * SQL admin functions
00934          * (For use in the Install Tool and Extension Manager)
00935          *
00936          **************************************/
00937 
00946         function admin_get_dbs()        {
00947                 $dbArr = array();
00948                 $db_list = mysql_list_dbs($this->link);
00949                 while ($row = mysql_fetch_object($db_list)) {
00950                         if ($this->sql_select_db($row->Database))       {
00951                                 $dbArr[] = $row->Database;
00952                         }
00953                 }
00954                 return $dbArr;
00955         }
00956 
00964         function admin_get_tables()     {
00965                 $whichTables = array();
00966                 $tables_result = mysql_list_tables(TYPO3_db, $this->link);
00967                 if (!mysql_error())     {
00968                         while ($theTable = mysql_fetch_assoc($tables_result)) {
00969                                 $whichTables[current($theTable)] = current($theTable);
00970                         }
00971                 }
00972                 return $whichTables;
00973         }
00974 
00983         function admin_get_fields($tableName)   {
00984                 $output = array();
00985 
00986                 $columns_res = mysql_query('SHOW columns FROM '.$tableName, $this->link);
00987                 while($fieldRow = mysql_fetch_assoc($columns_res))      {
00988                         $output[$fieldRow['Field']] = $fieldRow;
00989                 }
00990 
00991                 return $output;
00992         }
00993 
01001         function admin_get_keys($tableName)     {
01002                 $output = array();
01003 
01004                 $keyRes = mysql_query('SHOW keys FROM '.$tableName, $this->link);
01005                 while($keyRow = mysql_fetch_assoc($keyRes))     {
01006                         $output[] = $keyRow;
01007                 }
01008 
01009                 return $output;
01010         }
01011 
01019         function admin_query($query)    {
01020                 $res = mysql_query($query, $this->link);
01021                 if ($this->debugOutput) $this->debug('admin_query');
01022                 return $res;
01023         }
01024 
01025 
01026 
01027 
01028 
01029 
01030 
01031 
01032 
01033 
01034 
01035 
01036         /******************************
01037          *
01038          * Connecting service
01039          *
01040          ******************************/
01041 
01047         function connectDB()    {
01048                 if ($this->sql_pconnect(TYPO3_db_host, TYPO3_db_username, TYPO3_db_password))   {
01049                         if (!TYPO3_db)  {
01050                                 die('No database selected');
01051                                 exit;
01052                         } elseif (!$this->sql_select_db(TYPO3_db))      {
01053                                 die('Cannot connect to the current database, "'.TYPO3_db.'"');
01054                                 exit;
01055                         }
01056                 } else {
01057                         die('The current username, password or host was not accepted when the connection to the database was attempted to be established!');
01058                         exit;
01059                 }
01060         }
01061 
01062 
01063 
01064 
01065 
01066 
01067 
01068 
01069 
01070 
01071 
01072 
01073         /******************************
01074          *
01075          * Debugging
01076          *
01077          ******************************/
01078 
01085         function debug($func)   {
01086 
01087                 $error = $this->sql_error();
01088                 if ($error)             {
01089                         echo t3lib_div::view_array(array(
01090                                 'caller' => 't3lib_DB::'.$func,
01091                                 'ERROR' => $error,
01092                                 'lastBuiltQuery' => $this->debug_lastBuiltQuery,
01093                                 'debug_backtrace' => t3lib_div::debug_trail()
01094                         ));
01095                 }
01096         }
01097 
01104         function debug_check_recordset($res) {
01105                 if (!$res) {
01106                         $trace = FALSE;
01107                         $msg = 'Invalid database result resource detected';
01108                         $trace = debug_backtrace();
01109                         array_shift($trace);
01110                         $cnt = count($trace);
01111                         for ($i=0; $i<$cnt; $i++)       {
01112                                         // complete objects are too large for the log
01113                                 if (isset($trace['object']))    unset($trace['object']);
01114                         }
01115                         $msg .= ': function t3lib_DB->' . $trace[0]['function'] . ' called from file ' . substr($trace[0]['file'],strlen(PATH_site)+2) . ' in line ' . $trace[0]['line'];
01116                         t3lib_div::sysLog($msg.'. Use a devLog extension to get more details.', 'Core/t3lib_db', 3);
01117                         t3lib_div::devLog($msg.'.', 'Core/t3lib_db', 3, $trace);
01118 
01119                         return FALSE;
01120                 }
01121                 return TRUE;
01122         }
01123 
01124 }
01125 
01126 
01127 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_db.php'])        {
01128         include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_db.php']);
01129 }
01130 ?>


Généré par Les spécialistes TYPO3 avec  doxygen 1.4.6