class.t3lib_sqlengine.php

Go to the documentation of this file.

<?php
/***************************************************************
*  Copyright notice
*
*  (c) 2004 Kasper Skaarhoj (kasperYYYY@typo3.com)
*  All rights reserved
*
*  This script is part of the TYPO3 project. The TYPO3 project is
*  free software; you can redistribute it and/or modify
*  it under the terms of the GNU General Public License as published by
*  the Free Software Foundation; either version 2 of the License, or
*  (at your option) any later version.
*
*  The GNU General Public License can be found at
*  http://www.gnu.org/copyleft/gpl.html.
*  A copy is found in the textfile GPL.txt and important notices to the license
*  from the author is found in LICENSE.txt distributed with these scripts.
*
*
*  This script is distributed in the hope that it will be useful,
*  but WITHOUT ANY WARRANTY; without even the implied warranty of
*  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
*  GNU General Public License for more details.
*
*  This copyright notice MUST APPEAR in all copies of the script!
***************************************************************/
require_once(PATH_t3lib.'class.t3lib_sqlparser.php');


class t3lib_sqlengine extends t3lib_sqlparser {

      // array with data records: [table name][num.index] = records
   var $data = array();                // Data source storage


      // Internal, SQL Status vars:
   var $errorStatus = '';                 // Set with error message of last operation
   var $lastInsertedId = 0;               // Set with last inserted unique ID
   var $lastAffectedRows = 0;             // Set with last number of affected rows.





   function init($config, &$pObj)   {
   }

   function resetStatusVars() {
      $this->errorStatus = '';
      $this->lastInsertedId = 0;
      $this->lastAffectedRows = 0;
   }

   function processAccordingToConfig(&$value,$fInfo)  {
      $options = $this->parseFieldDef($fInfo['Type']);

      switch(strtolower($options['fieldType'])) {
         case 'int':
         case 'smallint':
         case 'tinyint':
         case 'mediumint':
            $value = intval($value);
            if ($options['featureIndex']['UNSIGNED']) {
               $value = t3lib_div::intInRange($value,0);
            }
         break;
         case 'double':
            $value = (double)$value;
         break;
         case 'varchar':
         case 'char':
            $value = substr($value,0,trim($options['value']));
         break;
         case 'text':
         case 'blob':
            $value = substr($value,0,65536);
         break;
         case 'tinytext':
         case 'tinyblob':
            $value = substr($value,0,256);
         break;
         case 'mediumtext':
         case 'mediumblob':
            // ??
         break;
      }
   }







   /********************************
    *
    * SQL queries
    * This is the SQL access functions used when this class is instantiated as a SQL handler with DBAL. Override these in derived classes.
    *
    ********************************/

   function exec_INSERTquery($table,$fields_values)   {

         // Initialize
      $this->resetStatusVars();

         // Reading Data Source if not done already.
      $this->readDataSource($table);

         // If data source is set:
      if (is_array($this->data[$table]))  {

         $fieldInformation = $this->admin_get_fields($table);     // Should cache this...!

            // Looking for unique keys:
         $saveArray = array();
         foreach($fieldInformation as $fInfo)   {

               // Field name:
            $fN = $fInfo['Field'];

               // Set value:
            $saveArray[$fN] = isset($fields_values[$fN]) ? $fields_values[$fN] : $options['Default'];

               // Process value:
            $this->processAccordingToConfig($saveArray[$fN], $fInfo);

               // If an auto increment field is found, find the largest current uid:
            if ($fInfo['Extra'] == 'auto_increment')  {

                  // Get all UIDs:
               $uidArray = array();
               foreach($this->data[$table] as $r)  {
                  $uidArray[] = $r[$fN];
               }

                  // If current value is blank or already in array, we create a new:
               if (!$saveArray[$fN] || in_array(intval($saveArray[$fN]), $uidArray))   {
                  if (count($uidArray))   {
                     $saveArray[$fN] = max($uidArray)+1;
                  } else $saveArray[$fN] = 1;
               }

                  // Update "last inserted id":
               $this->lastInsertedId = $saveArray[$fN];
            }
         }
#debug(array($fields_values,$saveArray));
            // Insert row in table:
         $this->data[$table][] = $saveArray;

            // Save data source
         $this->saveDataSource($table);

         return TRUE;
      } else $this->errorStatus = 'No data loaded.';

      return FALSE;
   }

   function exec_UPDATEquery($table,$where,$fields_values)  {

         // Initialize:
      $this->resetStatusVars();

         // Reading Data Source if not done already.
      $this->readDataSource($table);

         // If anything is there:
      if (is_array($this->data[$table]))  {

            // Parse WHERE clause:
         $where = $this->parseWhereClause($where);

         if (is_array($where))   {

               // Field information
            $fieldInformation = $this->admin_get_fields($table);     // Should cache this...!

               // Traverse fields to update:
            foreach($fields_values as $fName => $fValue) {
               $this->processAccordingToConfig($fields_values[$fName],$fieldInformation[$fName]);
            }
#debug($fields_values);
               // Do query, returns array with keys to the data array of the result:
            $itemKeys = $this->selectFromData($table,$where);

               // Set "last affected rows":
            $this->lastAffectedRows = count($itemKeys);

               // Update rows:
            if ($this->lastAffectedRows)  {
                  // Traverse result set here:
               foreach($itemKeys as $dataArrayKey) {

                     // Traverse fields to update:
                  foreach($fields_values as $fName => $fValue) {
                     $this->data[$table][$dataArrayKey][$fName] = $fValue;
                  }
               }

               // Save data source
               $this->saveDataSource($table);
            }

            return TRUE;
         } else $this->errorStatus = 'WHERE clause contained errors: '.$where;
      } else $this->errorStatus = 'No data loaded.';

      return FALSE;
   }

   function exec_DELETEquery($table,$where)  {

         // Initialize:
      $this->resetStatusVars();

         // Reading Data Source if not done already.
      $this->readDataSource($table);

         // If anything is there:
      if (is_array($this->data[$table]))  {

            // Parse WHERE clause:
         $where = $this->parseWhereClause($where);

         if (is_array($where))   {

               // Do query, returns array with keys to the data array of the result:
            $itemKeys = $this->selectFromData($table,$where);

               // Set "last affected rows":
            $this->lastAffectedRows = count($itemKeys);

               // Remove rows:
            if ($this->lastAffectedRows)  {
                  // Traverse result set:
               foreach($itemKeys as $dataArrayKey) {
                  unset($this->data[$table][$dataArrayKey]);
               }

                  // Saving data source
               $this->saveDataSource($table);
            }

            return TRUE;
         } else $this->errorStatus = 'WHERE clause contained errors: '.$where;
      } else $this->errorStatus = 'No data loaded.';

      return FALSE;
   }

   function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit) {

         // Initialize:
      $this->resetStatusVars();

         // Create result object
      $sqlObj = t3lib_div::makeInstance('t3lib_sqlengine_resultobj');
      $sqlObj->result = array(); // Empty result as a beginning

         // Get table list:
      $tableArray = $this->parseFromTables($from_table);
      $table = $tableArray[0]['table'];

         // Reading Data Source if not done already.
      $this->readDataSource($table);

         // If anything is there:
      if (is_array($this->data[$table]))  {

            // Parse WHERE clause:
         $where = $this->parseWhereClause($where_clause);
         if (is_array($where))   {

               // Do query, returns array with keys to the data array of the result:
            $itemKeys = $this->selectFromData($table,$where);

               // Finally, read the result rows into this variable:
            $sqlObj->result = $this->getResultSet($itemKeys,$table,'*');
               // Reset and return result:
            reset($sqlObj->result);
            return $sqlObj;
         } else $this->errorStatus = 'WHERE clause contained errors: '.$where;
      }  else $this->errorStatus = 'No data loaded: '.$this->errorStatus;

      return FALSE;
   }

   function sql_query($query) {
      $res = t3lib_div::makeInstance('t3lib_sqlengine_resultobj');
      $res->result = array();
      return $res;
   }

   function sql_error() {
      return $this->errorStatus;
   }

   function sql_insert_id() {
      return $this->lastInsertedId;
   }

   function sql_affected_rows() {
      return $this->lastAffectedRows;
   }

   function quoteStr($str) {
      return addslashes($str);
   }










   /**************************************
    *
    * SQL admin functions
    * (For use in the Install Tool and Extension Manager)
    *
    **************************************/

   function admin_get_tables()   {
      $whichTables = array();
      return $whichTables;
   }

   function admin_get_fields($tableName)  {
      $output = array();
      return $output;
   }

   function admin_get_keys($tableName) {
      $output = array();
      return $output;
   }

   function admin_query($query)  {
      return $this->sql_query($query);
   }








   /********************************
    *
    * Data Source I/O
    *
    ********************************/

   function readDataSource($table)  {
      $this->data[$table] = array();
   }

   function saveDataSource($table)  {
      debug($this->data[$table]);
   }













   /********************************
    *
    * SQL engine functions
    *
    ********************************/

   function selectFromData($table,$where) {

      $output = array();
      if (is_array($this->data[$table]))  {

            // All keys:
         $OR_index = 0;

         foreach($where as $config) {

            if (strtoupper($config['operator'])=='OR')   {
               $OR_index++;
            }

            if (!isset($itemKeys[$OR_index]))   $itemKeys[$OR_index] = array_keys($this->data[$table]);

            $this->select_evalSingle($table,$config,$itemKeys[$OR_index]);
         }

         foreach($itemKeys as $uidKeys)   {
            $output = array_merge($output, $uidKeys);
         }
         $output = array_unique($output);
      }

      return $output;
   }

   function select_evalSingle($table,$config,&$itemKeys) {
      $neg = ereg('^AND[[:space:]]+NOT$',trim($config['operator']));

      if (is_array($config['sub'])) {
         $subSelKeys = $this->selectFromData($table,$config['sub']);
         if ($neg)   {
            foreach($itemKeys as $kk => $vv) {
               if (in_array($vv,$subSelKeys))   {
                  unset($itemKeys[$kk]);
               }
            }
         } else {
            $itemKeys = array_intersect($itemKeys, $subSelKeys);
         }
      } else {
         $comp = strtoupper(ereg_replace('[[:space:]]','',$config['comparator']));
         $mod = strtoupper($config['modifier']);
         switch($comp)  {
            case 'NOTLIKE':
            case 'LIKE':
               $like_value = strtolower($config['value'][0]);
               if (substr($like_value,0,1)=='%')   {
                  $wildCard_begin = TRUE;
                  $like_value = substr($like_value,1);
               }
               if (substr($like_value,-1)=='%') {
                  $wildCard_end = TRUE;
                  $like_value = substr($like_value,0,-1);
               }
            break;
            case 'NOTIN':
            case 'IN':
               $in_valueArray = array();
               foreach($config['value'] as $vParts)   {
                  $in_valueArray[] = (string)$vParts[0];
               }
            break;
         }

         foreach($itemKeys as $kk => $v)  {
            $field_value = $this->data[$table][$v][$config['field']];

               // Calculate it:
            if ($config['calc']=='&')  {
               $field_value&=intval($config['calc_value']);
            }

               // Compare it:
            switch($comp)  {
               case '<=':
                  $bool = $field_value <= $config['value'][0];
               break;
               case '>=':
                  $bool = $field_value >= $config['value'][0];
               break;
               case '<':
                  $bool = $field_value < $config['value'][0];
               break;
               case '>':
                  $bool = $field_value > $config['value'][0];
               break;
               case '=':
                  $bool = !strcmp($field_value,$config['value'][0]);
               break;
               case '!=':
                  $bool = strcmp($field_value,$config['value'][0]);
               break;
               case 'NOTIN':
               case 'IN':
                  $bool = in_array((string)$field_value, $in_valueArray);
                  if ($comp=='NOTIN')  $bool = !$bool;
               break;
               case 'NOTLIKE':
               case 'LIKE':
                  if (!strlen($like_value))  {
                     $bool = TRUE;
                  } elseif ($wildCard_begin && !$wildCard_end) {
                     $bool = !strcmp(substr(strtolower($field_value),-strlen($like_value)),$like_value);
                  } elseif (!$wildCard_begin && $wildCard_end) {
                     $bool = !strcmp(substr(strtolower($field_value),0,strlen($like_value)),$like_value);
                  } elseif ($wildCard_begin && $wildCard_end)  {
                     $bool = strstr($field_value,$like_value);
                  } else {
                     $bool = !strcmp(strtolower($field_value),$like_value);
                  }
                  if ($comp=='NOTLIKE')   $bool = !$bool;
               break;
               default:
                  $bool = $field_value ? TRUE : FALSE;
               break;
            }

               // General negation:
            if ($neg)   $bool = !$bool;

               // Modify?
            switch($mod)   {
               case 'NOT':
               case '!':
                  $bool = !$bool;
               break;
            }

               // Action:
            if (!$bool) {
               unset($itemKeys[$kk]);
            }
         }

#        echo $this->debug_printResultSet($this->getResultSet($itemKeys,$table,'uid,tstamp'));
      }
   }

   function getResultSet($keys, $table, $fieldList)   {
      $fields = t3lib_div::trimExplode(',',$fieldList);

      $output = array();
      foreach($keys as $kValue)  {
         if ($fieldList=='*') {
            $output[$kValue] = $this->data[$table][$kValue];
         } else {
            foreach($fields as $fieldName)   {
               $output[$kValue][$fieldName] = $this->data[$table][$kValue][$fieldName];
            }
         }
      }

      return $output;
   }

















   /*************************
    *
    * Debugging
    *
    *************************/

   function debug_printResultSet($array)  {

      if (count($array))   {
         $tRows=array();
         $fields = array_keys(current($array));
               $tCell[]='
                     <td>IDX</td>';
            foreach($fields as $fieldName)   {
               $tCell[]='
                     <td>'.htmlspecialchars($fieldName).'</td>';
            }
            $tRows[]='<tr>'.implode('',$tCell).'</tr>';


         foreach($array as $index => $rec)   {

            $tCell=array();
            $tCell[]='
                  <td>'.htmlspecialchars($index).'</td>';
            foreach($fields as $fieldName)   {
               $tCell[]='
                     <td>'.htmlspecialchars($rec[$fieldName]).'</td>';
            }
            $tRows[]='<tr>'.implode('',$tCell).'</tr>';
         }

         return '<table border="1">'.implode('',$tRows).'</table>';
      } else 'Empty resultset';
   }
}


class t3lib_sqlengine_resultobj {

      // Result array, must contain the fields in the order they were selected in the SQL statement (for sql_fetch_row())
   var $result = array();

   var $TYPO3_DBAL_handlerType = '';
   var $TYPO3_DBAL_tableList = '';


   function sql_num_rows() {
      return count($this->result);
   }

   function sql_fetch_assoc() {
      $row = current($this->result);
      next($this->result);
      return $row;
   }

   function sql_fetch_row()   {
      $resultRow = $this->sql_fetch_assoc();

      if (is_array($resultRow))  {
         $numArray = array();
         foreach($resultRow as $value) {
            $numArray[]=$value;
         }
         return $numArray;
      }
   }

   function sql_data_seek($pointer) {
      reset($this->result);
      for ($a=0;$a<$pointer;$a++)   {
         next($this->result);
      }
      return TRUE;
   }

   function sql_field_type()  {
      return '';
   }
}



if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlengine.php'])  {
   include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlengine.php']);
}
?>

---