class.tx_dbal_sqlengine.php

Go to the documentation of this file.

<?php
/***************************************************************
 *  Copyright notice
 *
 *  (c) 2009-2010 Xavier Perseguers <typo3@perseguers.ch>
 *  (c) 2004-2009 Kasper Skårhøj <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!
 ***************************************************************/


/**
 * PHP SQL engine
 *
 * $Id: class.tx_dbal_sqlengine.php 40828 2010-12-05 14:55:53Z xperseguers $
 *
 * @author Kasper Skårhøj <kasperYYYY@typo3.com>
 * @author Xavier Perseguers <typo3@perseguers.ch>
 */
/**
 * [CLASS/FUNCTION INDEX of SCRIPT]
 *
 *
 *
 *  106: class tx_dbal_sqlengine extends ux_t3lib_sqlparser
 *  128:     public function init($config, $pObj)
 *  136:     public function resetStatusVars()
 *  152:     private function processAccordingToConfig(&$value, $fInfo)
 *
 *            SECTION: SQL queries
 *  207:     public function exec_INSERTquery($table, $fields_values)
 *  275:     public function exec_UPDATEquery($table, $where, $fields_values)
 *  334:     public function exec_DELETEquery($table, $where)
 *  385:     public function exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit)
 *  428:     public function sql_query($query)
 *  439:     public function sql_error()
 *  448:     public function sql_insert_id()
 *  457:     public function sql_affected_rows()
 *  467:     public function quoteStr($str)
 *
 *            SECTION: SQL admin functions
 *  493:     public function admin_get_tables()
 *  504:     public function admin_get_fields($tableName)
 *  515:     public function admin_get_keys($tableName)
 *  526:     public function admin_query($query)
 *
 *            SECTION: Data Source I/O
 *  551:     public function readDataSource($table)
 *  563:     public function saveDataSource($table)
 *
 *            SECTION: SQL engine functions (PHP simulation of SQL) - still experimental
 *  593:     public function selectFromData($table, $where)
 *  631:     public function select_evalSingle($table,$config,&$itemKeys)
 *  750:     public function getResultSet($keys, $table, $fieldList)
 *
 *            SECTION: Debugging
 *  793:     public function debug_printResultSet($array)
 *
 *
 *  832: class tx_dbal_sqlengine_resultobj
 *  846:     public function sql_num_rows()
 *  855:     public function sql_fetch_assoc()
 *  866:     public function sql_fetch_row()
 *  884:     public function sql_data_seek($pointer)
 *  897:     public function sql_field_type()
 *
 * TOTAL FUNCTIONS: 27
 * (This index is automatically created/updated by the extension "extdeveval")
 *
 */


/**
 * PHP SQL engine / server
 * Basically this is trying to emulation SQL record selection by PHP, thus allowing SQL queries into alternative data storages managed by PHP.
 * EXPERIMENTAL!
 *
 * @author  Kasper Skårhøj <kasperYYYY@typo3.com>
 * @package TYPO3
 * @subpackage t3lib
 */
class tx_dbal_sqlengine extends ux_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.


    /**
     * Dummy function for initializing SQL handler. Create you own in derived classes.
     *
     * @param   array       Configuration array from handler
     * @param   object      Parent object
     * @return  void
     */
    public function init($config, $pObj) {
    }

    /**
     * Reset SQL engine status variables (insert id, affected rows, error status)
     *
     * @return  void
     */
    public function resetStatusVars() {
        $this->errorStatus = '';
        $this->lastInsertedId = 0;
        $this->lastAffectedRows = 0;
    }

    /**
     * Processing of update/insert values based on field type.
     *
     * The input value is typecast and trimmed/shortened according to the field
     * type and the configuration options from the $fInfo parameter.
     *
     * @param   mixed       $value The input value to process
     * @param   array       $fInfo Field configuration data
     * @return  mixed       The processed input value
     */
    private 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.
     *
     ********************************/

    /**
     * Execute an INSERT query
     *
     * @param   string      Table name
     * @param   array       Field values as key=>value pairs.
     * @return  boolean     TRUE on success and FALSE on failure (error is set internally)
     */
    public 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:
// FIXME $options not defined
                $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];
                }
            }

            // Insert row in table:
            $this->data[$table][] = $saveArray;

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

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

        return FALSE;
    }

    /**
     * Execute UPDATE query on table
     *
     * @param   string      Table name
     * @param   string      WHERE clause
     * @param   array       Field values as key=>value pairs.
     * @return  boolean     TRUE on success and FALSE on failure (error is set internally)
     */
    public 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]);
                }

                // 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;
    }

    /**
     * Execute DELETE query
     *
     * @param   string      Table to delete from
     * @param   string      WHERE clause
     * @return  boolean     TRUE on success and FALSE on failure (error is set internally)
     */
    public 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;
    }

    /**
     * Execute SELECT query
     *
     * @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->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  object      Returns result object, but if errors, returns false
     */
    public function exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit) {

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

        // Create result object
        $sqlObj = t3lib_div::makeInstance('tx_dbal_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;
    }

    /**
     * Performs an SQL query on the "database"
     *
     * @param   string      Query to execute
     * @return  object      Result object or false if error
     */
    public function sql_query($query) {
        $res = t3lib_div::makeInstance('tx_dbal_sqlengine_resultobj');
        $res->result = array();
        return $res;
    }

    /**
     * Returns most recent error
     *
     * @return  string      Error message, if any
     */
    public function sql_error() {
        return $this->errorStatus;
    }

    /**
     * Returns most recently create unique ID (of INSERT queries)
     *
     * @return  integer     Last unique id created.
     */
    public function sql_insert_id() {
        return $this->lastInsertedId;
    }

    /**
     * Returns affected rows (of UPDATE and DELETE queries)
     *
     * @return  integer     Last amount of affected rows.
     */
    public function sql_affected_rows() {
        return $this->lastAffectedRows;
    }

    /**
     * Quoting strings for insertion in SQL queries
     *
     * @param   string      Input String
     * @return  string      String, with quotes escaped
     */
    public function quoteStr($str) {
        return addslashes($str);
    }


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

    /**
     * (DUMMY) Returns the list of tables from the database
     *
     * @return  array       Tables in an array (tablename is in both key and value)
     * @todo    Should return table details in value! see t3lib_db::admin_get_tables()
     */
    public function admin_get_tables() {
        $whichTables = array();
        return $whichTables;
    }

    /**
     * (DUMMY) Returns information about each field in the $table
     *
     * @param   string      Table name
     * @return  array       Field information in an associative array with fieldname => field row
     */
    public function admin_get_fields($tableName) {
        $output = array();
        return $output;
    }

    /**
     * (DUMMY) Returns information about each index key in the $table
     *
     * @param   string      Table name
     * @return  array       Key information in a numeric array
     */
    public function admin_get_keys($tableName) {
        $output = array();
        return $output;
    }

    /**
     * (DUMMY) mysql() wrapper function, used by the Install Tool and EM for all queries regarding management of the database!
     *
     * @param   string      Query to execute
     * @return  pointer     Result pointer
     */
    public function admin_query($query) {
        return $this->sql_query($query);
    }


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

    /**
     * Dummy function for setting table data. Create your own.
     * NOTICE: Handler to "table-locking" needs to be made probably!
     *
     * @param   string      Table name
     * @return  void
     * @todo    Table locking tools?
     */
    public function readDataSource($table) {
        $this->data[$table] = array();
    }

    /**
     * Dummy function for setting table data. Create your own.
     * NOTICE: Handler to "table-locking" needs to be made probably!
     *
     * @param   string      Table name
     * @return  void
     * @todo    Table locking tools?
     */
    public function saveDataSource($table) {
        debug($this->data[$table]);
    }


    /********************************
     *
     * SQL engine functions (PHP simulation of SQL) - still experimental
     *
     ********************************/

    /**
     * PHP simulation of SQL "SELECT"
     * Yet EXPERIMENTAL!
     *
     * @param   string      Table name
     * @param   array       Where clause parsed into array
     * @return  array       Array of keys pointing to result rows in $this->data[$table]
     */
    public 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++;
                }
// FIXME: unknown variable $itemKeys
                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;
    }

    /**
     * Evalutaion of a WHERE-clause-array.
     * Yet EXPERIMENTAL
     *
     * @param   string      Tablename
     * @param   array       WHERE-configuration array
     * @param   array       Data array to work on.
     * @return  void        Data array passed by reference
     * @see selectFromData()
     */
    public function select_evalSingle($table, $config, &$itemKeys) {
        $neg = preg_match('/^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(str_replace(array(' ', "\t", "\r", "\n"), '', $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]);
                }
            }
        }
    }

    /**
     * Returning result set based on result keys, table and field list
     *
     * @param   array       Result keys
     * @param   string      Tablename
     * @param   string      Fieldlist (commaseparated)
     * @return  array       Result array with "rows"
     */
    public 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
     *
     *************************/

    /**
     * Returns the result set (in array) as HTML table. For debugging.
     *
     * @param   array       Result set array (array of rows)
     * @return  string      HTML table
     */
    public 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';
    }
}


/**
 * PHP SQL engine, result object
 *
 * @author  Kasper Skårhøj <kasperYYYY@typo3.com>
 * @package TYPO3
 * @subpackage dbal
 */
class tx_dbal_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 = '';


    /**
     * Counting number of rows
     *
     * @return  integer
     */
    public function sql_num_rows() {
        return count($this->result);
    }

    /**
     * Fetching next row in result array
     *
     * @return  array       Associative array
     */
    public function sql_fetch_assoc() {
        $row = current($this->result);
        next($this->result);
        return $row;
    }

    /**
     * Fetching next row, numerical indices
     *
     * @return  array       Numerical array
     */
    public function sql_fetch_row() {
        $resultRow = $this->sql_fetch_assoc();

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

    /**
     * Seeking position in result
     *
     * @param   integer     Position pointer.
     * @return  boolean     Returns true on success
     */
    public function sql_data_seek($pointer) {
        reset($this->result);
        for ($a = 0; $a < $pointer; $a++) {
            next($this->result);
        }
        return TRUE;
    }

    /**
     * Returning SQL field type
     *
     * @return  string      Blank string, not supported (it seems)
     */
    public function sql_field_type() {
        return '';
    }
}


if (defined('TYPO3_MODE') && isset($GLOBALS['TYPO3_CONF_VARS'][TYPO3_MODE]['XCLASS']['ext/dbal/lib/class.tx_dbal_sqlengine.php'])) {
    include_once($GLOBALS['TYPO3_CONF_VARS'][TYPO3_MODE]['XCLASS']['ext/dbal/lib/class.tx_dbal_sqlengine.php']);
}

?>