Raised DBAL version from 1.1.0 to 1.1.1
authorXavier Perseguers <typo3@perseguers.ch>
Sun, 14 Feb 2010 18:21:57 +0000 (18:21 +0000)
committerXavier Perseguers <typo3@perseguers.ch>
Sun, 14 Feb 2010 18:21:57 +0000 (18:21 +0000)
git-svn-id: https://svn.typo3.org/TYPO3v4/Core/trunk@6904 709f56b5-9817-0410-a4d7-c38de5d9e867

19 files changed:
ChangeLog
typo3/sysext/dbal/ChangeLog
typo3/sysext/dbal/class.ux_t3lib_db.php
typo3/sysext/dbal/class.ux_t3lib_sqlparser.php
typo3/sysext/dbal/ext_emconf.php
typo3/sysext/dbal/handlers/class.tx_dbal_handler_openoffice.php
typo3/sysext/dbal/last_synched_target
typo3/sysext/dbal/lib/class.tx_dbal_sqlengine.php
typo3/sysext/dbal/lib/class.tx_dbal_tsparserext.php
typo3/sysext/dbal/res/oracle/indexed_search.diff [new file with mode: 0644]
typo3/sysext/dbal/res/oracle/realurl.diff [new file with mode: 0644]
typo3/sysext/dbal/res/oracle/scheduler.diff [new file with mode: 0644]
typo3/sysext/dbal/res/oracle/templavoila.diff [new file with mode: 0644]
typo3/sysext/dbal/res/postgresql/postgresql-compatibility.sql
typo3/sysext/dbal/tests/db_general_testcase.php
typo3/sysext/dbal/tests/db_mssql_testcase.php [new file with mode: 0644]
typo3/sysext/dbal/tests/db_oracle_testcase.php
typo3/sysext/dbal/tests/fixtures/mssql.config.php [new file with mode: 0644]
typo3/sysext/dbal/tests/sqlparser_general_testcase.php

index b452d10..7681fe1 100755 (executable)
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,5 +1,6 @@
 2010-02-14  Xavier Perseguers  <typo3@perseguers.ch>
 
+       * Raised DBAL version from 1.1.0 to 1.1.1
        * Fixed bug #12829: ALTER TABLE is not able to parse length restriction in index creation
 
 2010-02-14  Andreas Otto  <andreas@otto-hanika.de>
index 6230cc6..e85d57b 100644 (file)
@@ -1,3 +1,67 @@
+2010-02-14  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Set version to 1.1.1
+
+2010-02-14  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Added unit test for #12829: ALTER TABLE is not able to parse length restriction in index creation
+
+2010-02-13  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Added unit tests for #4493: WHERE func(expr) IN (n,...m) is broken
+       * Fixed bug #6196: IFNULL operator cannot be parsed
+       * Fixed bug #13528: Argument 1 passed to ux_t3lib_DB::_quoteFieldNames() must be an array, string given
+
+2010-02-11  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Follow-up of #13504: Using TRUNCATE instead of TRUNCATETABLE as public DB API
+
+2010-02-10  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Fixed bug #13504: Cannot parse TRUNCATE TABLE in install tool
+
+2010-02-09  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Added unit test for bug #13490: Where clause 0=0 cannot be parsed
+
+2010-02-07  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Added feature #12858: Provide an API to insert multiple rows at once
+       * Fixed bug #13453: Method compileINSERT of ux_t3lib_sqlparser should be protected
+       * Added feature #13209: MySQL extended INSERT statements cannot be parsed
+
+2010-02-06  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Added unit tests for bug #13430: Cannot parse INSERT when VALUES has no space before left parenthesis
+
+2010-02-05  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Fixed bug #13422: Invalid quoting of numeric column names
+
+2010-02-03  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Added unit tests for bug #13412: BETWEEN operator is not supported
+
+2010-01-24  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Fixed bug #13090: version warning with CSS styled content
+
+2010-01-14  Steffen Kamper  <info@sk-typo3.de>
+
+       * Fixed bug #13199: fileadminDir is hardcoded in several places (Thanks to Susanne Moog)
+
+2010-01-08  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Updated copyright year
+
+2010-01-05  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Added feature #13134: Support of SQL LOCATE function
+
+2010-01-03  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Added feature #13135: Support for CASE WHEN flow control
+
 2009-12-30  Xavier Perseguers  <typo3@perseguers.ch>
 
        * Set version to 1.1.0
index e27bdd0..d03e22c 100644 (file)
@@ -4,7 +4,7 @@
 *
 *  (c) 2004-2009 Kasper Skaarhoj (kasperYYYY@typo3.com)
 *  (c) 2004-2009 Karsten Dambekalns <karsten@typo3.org>
-*  (c) 2009 Xavier Perseguers <typo3@perseguers.ch>
+*  (c) 2009-2010 Xavier Perseguers <typo3@perseguers.ch>
 *  All rights reserved
 *
 *  This script is part of the TYPO3 project. The TYPO3 project is
@@ -29,7 +29,7 @@
 /**
  * Contains a database abstraction layer class for TYPO3
  *
- * $Id: class.ux_t3lib_db.php 28106 2009-12-28 01:01:12Z xperseguers $
+ * $Id: class.ux_t3lib_db.php 29977 2010-02-13 13:18:32Z xperseguers $
  *
  * @author     Kasper Skaarhoj <kasper@typo3.com>
  * @author     Karsten Dambekalns <k.dambekalns@fishfarm.de>
@@ -490,6 +490,32 @@ class ux_t3lib_DB extends t3lib_DB {
        }
 
        /**
+        * Creates and executes an INSERT SQL-statement for $table with multiple rows.
+        * This method uses exec_INSERTquery() and is just a syntax wrapper to it.
+        *
+        * @param       string          Table name
+        * @param       array           Field names
+        * @param       array           Table rows. Each row should be an array with field values mapping to $fields
+        * @param       string/array            See fullQuoteArray()
+        * @return      mixed           Result from last handler, usually TRUE when success and FALSE on failure
+        */
+       public function exec_INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
+               if ((string)$this->handlerCfg[$this->lastHandlerKey]['type'] === 'native') {
+                       return parent::exec_INSERTmultipleRows($table, $fields, $rows, $no_quote_fields);
+               }
+
+               foreach ($rows as $row) {
+                       $fields_values = array();
+                       foreach ($fields as $key => $value) {
+                               $fields_values[$value] = $row[$key];
+                       }
+                       $res = $this->exec_INSERTquery($table, $fields_values, $no_quote_fields);
+               }
+
+               return $res;
+       }
+
+       /**
         * Updates a record from $table
         *
         * @param       string          Database tablename
@@ -728,6 +754,62 @@ class ux_t3lib_DB extends t3lib_DB {
        }
 
        /**
+        * Truncates a table.
+        * 
+        * @param       string          Database tablename
+        * @return      mixed           Result from handler
+        */
+       public function exec_TRUNCATEquery($table) {
+               if ($this->debug) {
+                       $pt = t3lib_div::milliseconds();
+               }
+
+                       // Do table/field mapping:
+               $ORIG_tableName = $table;
+               if ($tableArray = $this->map_needMapping($table)) {
+                               // Table name:
+                       if ($this->mapping[$table]['mapTableName']) {
+                               $table = $this->mapping[$table]['mapTableName'];
+                       }
+               }
+
+                       // Select API
+               $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_tableName);
+               switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
+                       case 'native':
+                               $this->lastQuery = $this->TRUNCATEquery($table);
+                               $sqlResult = mysql_query($this->lastQuery, $this->handlerInstance[$this->lastHandlerKey]['link']);
+                               break;
+                       case 'adodb':
+                               $this->lastQuery = $this->TRUNCATEquery($table);
+                               $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery, FALSE);
+                               break;
+                       case 'userdefined':
+                               $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_TRUNCATEquery($table,$where);
+                               break;
+               }
+
+               if ($this->printErrors && $this->sql_error()) {
+                       debug(array($this->lastQuery, $this->sql_error()));
+               }
+
+               if ($this->debug) {
+                       $this->debugHandler(
+                               'exec_TRUNCATEquery',
+                               t3lib_div::milliseconds() - $pt,
+                               array(
+                                       'handlerType' => $hType,
+                                       'args' => array($table),
+                                       'ORIG_from_table' => $ORIG_tableName
+                               )
+                       );
+               }
+
+                       // Return result:
+               return $sqlResult;
+       }
+
+       /**
         * Executes a query.
         * EXPERIMENTAL since TYPO3 4.4.
         * 
@@ -775,6 +857,10 @@ class ux_t3lib_DB extends t3lib_DB {
                                $table = $queryParts['TABLE'];
                                $whereClause = isset($queryParts['WHERE']) ? $this->SQLparser->compileWhereClause($queryParts['WHERE']) : '1=1';
                                return $this->exec_DELETEquery($table, $whereClause);
+
+                       case 'TRUNCATETABLE':
+                               $table = $queryParts['TABLE'];
+                               return $this->exec_TRUNCATEquery($table);
                }
        }
 
@@ -860,6 +946,39 @@ class ux_t3lib_DB extends t3lib_DB {
        }
 
        /**
+        * Creates an INSERT SQL-statement for $table with multiple rows.
+        * This method will create multiple INSERT queries concatenated with ';'
+        *
+        * @param       string          Table name
+        * @param       array           Field names
+        * @param       array           Table rows. Each row should be an array with field values mapping to $fields
+        * @param       string/array            See fullQuoteArray()
+        * @return      array           Full SQL query for INSERT as array of strings (unless $fields_values does not contain any elements in which case it will be FALSE). If BLOB fields will be affected and one is not running the native type, an array will be returned for each row, where 0 => plain SQL, 1 => fieldname/value pairs of BLOB fields.
+        */
+       public function INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
+               if ((string)$this->handlerCfg[$this->lastHandlerKey]['type'] === 'native') {
+                       return parent::INSERTmultipleRows($table, $fields, $rows, $no_quote_fields);
+               }
+
+               $result = array();
+
+               foreach ($rows as $row) {
+                       $fields_values = array();
+                       foreach ($fields as $key => $value) {
+                               $fields_values[$value] = $row[$key];
+                       }
+                       $rowQuery = $this->INSERTquery($table, $fields_values, $no_quote_fields);
+                       if (is_array($rowQuery)) {
+                               $result[] = $rowQuery;
+                       } else {
+                               $result[][0] = $rowQuery;
+                       }
+               }
+
+               return $result;
+       }
+
+       /**
         * Creates an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
         * Usage count/core: 6
         *
@@ -983,6 +1102,25 @@ class ux_t3lib_DB extends t3lib_DB {
                return $query;
        }
 
+       /**
+        * Creates a TRUNCATE TABLE SQL-statement
+        * 
+        * @param       string          See exec_TRUNCATEquery()
+        * @return      string          Full SQL query for TRUNCATE TABLE
+        */
+       public function TRUNCATEquery($table) {
+               $table = $this->quoteFromTables($table);
+
+                       // Call parent method to build actual query
+               $query = parent::TRUNCATEquery($table);
+
+               if ($this->debugOutput || $this->store_lastBuiltQuery) {
+                       $this->debug_lastBuiltQuery = $query;
+               }
+
+               return $query;
+       }
+
 
        /**************************************
        *
@@ -1026,6 +1164,9 @@ class ux_t3lib_DB extends t3lib_DB {
                if ($this->runningNative()) return $select_fields;
 
                $select_fields = $this->SQLparser->parseFieldList($select_fields);
+               if ($this->SQLparser->parse_error) {
+                       die($this->SQLparser->parse_error . ' in ' . __FILE__ . ' : ' . __LINE__);
+               }
                $select_fields = $this->_quoteFieldNames($select_fields);
 
                return $this->SQLparser->compileFieldList($select_fields);
@@ -1040,10 +1181,10 @@ class ux_t3lib_DB extends t3lib_DB {
         */
        protected function _quoteFieldNames(array $select_fields) {
                foreach ($select_fields as $k => $v) {
-                       if ($select_fields[$k]['field'] != '' && $select_fields[$k]['field'] != '*') {
+                       if ($select_fields[$k]['field'] != '' && $select_fields[$k]['field'] != '*' && !is_numeric($select_fields[$k]['field'])) {
                                $select_fields[$k]['field'] = $this->quoteName($select_fields[$k]['field']);
                        }
-                       if ($select_fields[$k]['table'] != '') {
+                       if ($select_fields[$k]['table'] != '' && !is_numeric($select_fields[$k]['table'])) {
                                $select_fields[$k]['table'] = $this->quoteName($select_fields[$k]['table']);
                        }
                        if ($select_fields[$k]['as'] != '') {
@@ -1053,6 +1194,17 @@ class ux_t3lib_DB extends t3lib_DB {
                                $select_fields[$k]['func_content.'][0]['func_content'] = $this->quoteFieldNames($select_fields[$k]['func_content.'][0]['func_content']);
                                $select_fields[$k]['func_content'] = $this->quoteFieldNames($select_fields[$k]['func_content']);
                        }
+                       if (isset($select_fields[$k]['flow-control'])) {
+                                       // Quoting flow-control statements
+                               if ($select_fields[$k]['flow-control']['type'] === 'CASE') {
+                                       if (isset($select_fields[$k]['flow-control']['case_field'])) {
+                                               $select_fields[$k]['flow-control']['case_field'] = $this->quoteFieldNames($select_fields[$k]['flow-control']['case_field']);
+                                       }
+                                       foreach ($select_fields[$k]['flow-control']['when'] as $key => $when) {
+                                               $select_fields[$k]['flow-control']['when'][$key]['when_value'] = $this->_quoteWhereClause($when['when_value']);
+                                       } 
+                               }
+                       }
                }
 
                return $select_fields;
@@ -1134,7 +1286,20 @@ class ux_t3lib_DB extends t3lib_DB {
                        if (is_array($where_clause[$k]['sub'])) {
                                $where_clause[$k]['sub'] = $this->_quoteWhereClause($where_clause[$k]['sub']);
                        } elseif (isset($v['func'])) {
-                               $where_clause[$k]['func']['subquery'] = $this->quoteSELECTsubquery($v['func']['subquery']);
+                               switch ($where_clause[$k]['func']['type']) {
+                                       case 'EXISTS':
+                                               $where_clause[$k]['func']['subquery'] = $this->quoteSELECTsubquery($v['func']['subquery']);
+                                               break;
+                                       case 'IFNULL':
+                                       case 'LOCATE':
+                                               if ($where_clause[$k]['func']['table'] != '') {
+                                                       $where_clause[$k]['func']['table'] = $this->quoteName($v['func']['table']);
+                                               }
+                                               if ($where_clause[$k]['func']['field'] != '') {
+                                                       $where_clause[$k]['func']['field'] = $this->quoteName($v['func']['field']);
+                                               }
+                                       break;
+                               }
                        } else {
                                if ($where_clause[$k]['table'] != '') {
                                        $where_clause[$k]['table'] = $this->quoteName($where_clause[$k]['table']);
@@ -2156,6 +2321,7 @@ class ux_t3lib_DB extends t3lib_DB {
                                        $this->map_genericQueryParsed($parsedQuery);
                                        break;
                                case 'INSERT':
+                               case 'TRUNCATETABLE':
                                        $this->map_genericQueryParsed($parsedQuery);
                                        break;
                                case 'CREATEDATABASE':
@@ -2184,8 +2350,11 @@ class ux_t3lib_DB extends t3lib_DB {
                                case 'adodb':
                                                // Compiling query:
                                        $compiledQuery =  $this->SQLparser->compileSQL($this->lastParsedAndMappedQueryArray);
-                                       if ($this->lastParsedAndMappedQueryArray['type']=='INSERT') {
-                                               return $this->exec_INSERTquery($this->lastParsedAndMappedQueryArray['TABLE'],$compiledQuery);
+                                       switch ($this->lastParsedAndMappedQueryArray['type']) {
+                                               case 'INSERT':
+                                                       return $this->exec_INSERTquery($this->lastParsedAndMappedQueryArray['TABLE'], $compiledQuery);
+                                               case 'TRUNCATETABLE':
+                                                       return $this->exec_TRUNCATEquery($this->lastParsedAndMappedQueryArray['TABLE']);
                                        }
                                        return $this->handlerInstance[$this->lastHandlerKey]->DataDictionary->ExecuteSQLArray($compiledQuery);
                                        break;
@@ -2559,7 +2728,7 @@ class ux_t3lib_DB extends t3lib_DB {
                        // Select fields:
                $expFields = $this->SQLparser->parseFieldList($select_fields);
                $this->map_sqlParts($expFields,$defaultTable);
-               $select_fields = $this->SQLparser->compileFieldList($expFields);
+               $select_fields = $this->SQLparser->compileFieldList($expFields, FALSE, FALSE);
 
                        // Group By fields
                $expFields = $this->SQLparser->parseFieldList($groupBy);
@@ -2586,20 +2755,69 @@ class ux_t3lib_DB extends t3lib_DB {
                if (is_array($sqlPartArray)) {
                        foreach ($sqlPartArray as $k => $v) {
 
+                               if (isset($sqlPartArray[$k]['type'])) {
+                                       switch ($sqlPartArray[$k]['type']) {
+                                               case 'flow-control':
+                                                       $temp = array($sqlPartArray[$k]['flow-control']);
+                                                       $this->map_sqlParts($temp, $defaultTable);      // Call recursively!
+                                                       $sqlPartArray[$k]['flow-control'] = $temp[0];
+                                                       break;
+                                               case 'CASE':
+                                                       if (isset($sqlPartArray[$k]['case_field'])) {
+                                                               $fieldArray = explode('.', $sqlPartArray[$k]['case_field']);
+                                                               if (count($fieldArray) == 1 && is_array($this->mapping[$defaultTable]['mapFieldNames']) && isset($this->mapping[$defaultTable]['mapFieldNames'][$fieldArray[0]])) {
+                                                                       $sqlPartArray[$k]['case_field'] = $this->mapping[$defaultTable]['mapFieldNames'][$fieldArray[0]];
+                                                               }
+                                                               elseif (count($fieldArray) == 2) {
+                                                                               // Map the external table
+                                                                       $table = $fieldArray[0];
+                                                                       if (isset($this->mapping[$fieldArray[0]]['mapTableName'])) {
+                                                                               $table = $this->mapping[$fieldArray[0]]['mapTableName'];
+                                                                       }
+                                                                               // Map the field itself
+                                                                       $field = $fieldArray[1];
+                                                                       if (is_array($this->mapping[$fieldArray[0]]['mapFieldNames']) && isset($this->mapping[$fieldArray[0]]['mapFieldNames'][$fieldArray[1]])) {
+                                                                               $field = $this->mapping[$fieldArray[0]]['mapFieldNames'][$fieldArray[1]];
+                                                                       }
+                                                                       $sqlPartArray[$k]['case_field'] = $table . '.' . $field;
+                                                               }
+                                                       }
+                                                       foreach ($sqlPartArray[$k]['when'] as $key => $when) {
+                                                               $this->map_sqlParts($sqlPartArray[$k]['when'][$key]['when_value'], $defaultTable);
+                                                       }
+                                                       break;
+                                       }
+                               }
+
                                        // Look for sublevel (WHERE parts only)
                                if (is_array($sqlPartArray[$k]['sub'])) {
                                        $this->map_sqlParts($sqlPartArray[$k]['sub'], $defaultTable);   // Call recursively!
                                } elseif (isset($sqlPartArray[$k]['func'])) {
-                                       $subqueryDefaultTable = $sqlPartArray[$k]['func']['subquery']['FROM'][0]['table'];
-                                       $this->map_sqlParts($sqlPartArray[$k]['func']['subquery']['SELECT'], $subqueryDefaultTable);
-                                       $this->map_sqlParts($sqlPartArray[$k]['func']['subquery']['FROM'], $subqueryDefaultTable);
-                                       $this->map_sqlParts($sqlPartArray[$k]['func']['subquery']['WHERE'], $subqueryDefaultTable);
+                                       switch ($sqlPartArray[$k]['func']['type']) {
+                                               case 'EXISTS':
+                                                       $subqueryDefaultTable = $sqlPartArray[$k]['func']['subquery']['FROM'][0]['table'];
+                                                       $this->map_sqlParts($sqlPartArray[$k]['func']['subquery']['SELECT'], $subqueryDefaultTable);
+                                                       $this->map_sqlParts($sqlPartArray[$k]['func']['subquery']['FROM'], $subqueryDefaultTable);
+                                                       $this->map_sqlParts($sqlPartArray[$k]['func']['subquery']['WHERE'], $subqueryDefaultTable);
+                                                       break;
+                                               case 'IFNULL':
+                                               case 'LOCATE':
+                                                               // For the field, look for table mapping (generic):
+                                                       $t = $sqlPartArray[$k]['func']['table'] ? $sqlPartArray[$k]['func']['table'] : $defaultTable;
+                                                       if (is_array($this->mapping[$t]['mapFieldNames']) && $this->mapping[$t]['mapFieldNames'][$sqlPartArray[$k]['func']['field']]) {
+                                                               $sqlPartArray[$k]['func']['field'] = $this->mapping[$t]['mapFieldNames'][$sqlPartArray[$k]['func']['field']];
+                                                       }
+                                                       if ($this->mapping[$t]['mapTableName']) {
+                                                               $sqlPartArray[$k]['func']['table'] = $this->mapping[$t]['mapTableName'];
+                                                       }
+                                                       break;
+                                       }
                                } else {
                                                // For the field, look for table mapping (generic):
                                        $t = $sqlPartArray[$k]['table'] ? $sqlPartArray[$k]['table'] : $defaultTable;
 
                                                // Mapping field name, if set:
-                                       if (is_array($this->mapping[$t]['mapFieldNames']) && $this->mapping[$t]['mapFieldNames'][$sqlPartArray[$k]['field']]) {
+                                       if (is_array($this->mapping[$t]['mapFieldNames']) && isset($this->mapping[$t]['mapFieldNames'][$sqlPartArray[$k]['field']])) {
                                                $sqlPartArray[$k]['field'] = $this->mapping[$t]['mapFieldNames'][$sqlPartArray[$k]['field']];
                                        }
 
@@ -2624,6 +2842,15 @@ class ux_t3lib_DB extends t3lib_DB {
                                                        $sqlPartArray[$k]['func_content.'][0]['func_content'] = $table . '.' . $field;
                                                        $sqlPartArray[$k]['func_content'] = $table . '.' . $field;
                                                }
+
+                                                       // Mapping flow-control statements
+                                               if (isset($sqlPartArray[$k]['flow-control'])) {                                                 
+                                                       if (isset($sqlPartArray[$k]['flow-control']['type'])) {
+                                                               $temp = array($sqlPartArray[$k]['flow-control']);
+                                                               $this->map_sqlParts($temp, $t); // Call recursively!
+                                                               $sqlPartArray[$k]['flow-control'] = $temp[0];
+                                                       }
+                                               }
                                        }
 
                                                // Do we have a function (e.g., CONCAT)
index 2957f3c..464e9d5 100644 (file)
@@ -4,7 +4,7 @@
 *
 *  (c) 2004-2009 Kasper Skaarhoj (kasperYYYY@typo3.com)
 *  (c) 2004-2009 Karsten Dambekalns <karsten@typo3.org>
-*  (c) 2009 Xavier Perseguers <typo3@perseguers.ch>
+*  (c) 2009-2010 Xavier Perseguers <typo3@perseguers.ch>
 *  All rights reserved
 *
 *  This script is part of the TYPO3 project. The TYPO3 project is
@@ -29,7 +29,7 @@
 /**
  * PHP SQL engine
  *
- * $Id: class.ux_t3lib_sqlparser.php 28103 2009-12-28 00:40:06Z xperseguers $
+ * $Id: class.ux_t3lib_sqlparser.php 29977 2010-02-13 13:18:32Z xperseguers $
  *
  * @author     Kasper Skaarhoj <kasperYYYY@typo3.com>
  * @author     Karsten Dambekalns <k.dambekalns@fishfarm.de>
@@ -52,12 +52,93 @@ class ux_t3lib_sqlparser extends t3lib_sqlparser {
         *
         * @param       array           Array of select fields, (made with ->parseFieldList())
         * @param       boolean         Whether comments should be compiled
+        * @param       boolean         Whether function mapping should take place
         * @return      string          Select field string
         * @see parseFieldList()
         */
-       public function compileFieldList($selectFields, $compileComments = TRUE) {
-                       // TODO: Handle SQL hints in comments according to current DBMS
-               return parent::compileFieldList($selectFields, FALSE);
+       public function compileFieldList($selectFields, $compileComments = TRUE, $functionMapping = TRUE) {
+               switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
+                       case 'native':
+                               $output = parent::compileFieldList($selectFields, $compileComments);
+                               break;
+                       case 'adodb':
+                               $output = '';
+                                       // Traverse the selectFields if any:
+                               if (is_array($selectFields)) {
+                                       $outputParts = array();
+                                       foreach ($selectFields as $k => $v) {
+               
+                                                       // Detecting type:
+                                               switch($v['type'])      {
+                                                       case 'function':
+                                                               $outputParts[$k] = $v['function'] . '(' . $v['func_content'] . ')';
+                                                               break;
+                                                       case 'flow-control':
+                                                               if ($v['flow-control']['type'] === 'CASE') {
+                                                                       $outputParts[$k] = $this->compileCaseStatement($v['flow-control'], $functionMapping);
+                                                               }
+                                                               break;
+                                                       case 'field':
+                                                               $outputParts[$k] = ($v['distinct'] ? $v['distinct'] : '') . ($v['table'] ? $v['table'] . '.' : '') . $v['field'];
+                                                               break;
+                                               }
+               
+                                                       // Alias:
+                                               if ($v['as']) {
+                                                       $outputParts[$k] .= ' ' . $v['as_keyword'] . ' ' . $v['as'];
+                                               }
+               
+                                                       // Specifically for ORDER BY and GROUP BY field lists:
+                                               if ($v['sortDir']) {
+                                                       $outputParts[$k] .= ' ' . $v['sortDir'];
+                                               }
+                                       }
+                                               // TODO: Handle SQL hints in comments according to current DBMS
+                                       if (/* $compileComments */ FALSE && $selectFields[0]['comments']) {
+                                               $output = $selectFields[0]['comments'] . ' ';
+                                       }
+                                       $output .= implode(', ', $outputParts);
+                               }
+                               break;
+               }       
+               return $output;
+       }
+       /**
+        * Compiles a CASE ... WHEN flow-control construct based on input array (made with ->parseCaseStatement())
+        *
+        * @param       array           Array of case components, (made with ->parseCaseStatement())
+        * @param       boolean         Whether function mapping should take place
+        * @return      string          case when string
+        * @see parseCaseStatement()
+        */
+       protected function compileCaseStatement(array $components, $functionMapping = TRUE) {
+               switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
+                       case 'native':
+                               $output = parent::compileCaseStatement($components);
+                               break;
+                       case 'adodb':
+                               $statement = 'CASE';
+                               if (isset($components['case_field'])) {
+                                       $statement .= ' ' . $components['case_field'];
+                               } elseif (isset($components['case_value'])) {
+                                       $statement .= ' ' . $components['case_value'][1] . $components['case_value'][0] . $components['case_value'][1];
+                               }
+                               foreach ($components['when'] as $when) {
+                                       $statement .= ' WHEN ';
+                                       $statement .= $this->compileWhereClause($when['when_value'], $functionMapping);
+                                       $statement .= ' THEN ';
+                                       $statement .= $when['then_value'][1] . $when['then_value'][0] . $when['then_value'][1];
+                               }
+                               if (isset($components['else'])) {
+                                       $statement .= ' ELSE ';
+                                       $statement .= $components['else'][1] . $components['else'][0] . $components['else'][1];
+                               }
+                               $statement .= ' END';
+                               $output = $statement;
+                               break;
+               }
+               return $output;
        }
 
        /**
@@ -82,27 +163,34 @@ class ux_t3lib_sqlparser extends t3lib_sqlparser {
         * Compiles an INSERT statement from components array
         *
         * @param array Array of SQL query components
-        * @return string SQL INSERT query
+        * @return string SQL INSERT query / array
         * @see parseINSERT()
         */
-       function compileINSERT($components) {
+       protected function compileINSERT($components) {
                switch ((string)$GLOBALS['TYPO3_DB']->handlerCfg[$GLOBALS['TYPO3_DB']->lastHandlerKey]['type']) {
                        case 'native':
                                $query = parent::compileINSERT($components);
                                break;
                        case 'adodb':
+                               $values = array();
+
                                if (isset($components['VALUES_ONLY']) && is_array($components['VALUES_ONLY'])) {
-                                       $fields = $GLOBALS['TYPO3_DB']->cache_fieldType[$components['TABLE']];
-                                       $fc = 0;
-                                       foreach ($fields as $fn => $fd) {
-                                               $query[$fn] = $components['VALUES_ONLY'][$fc++][0];
-                                       }
+                                       $valuesComponents = $components['EXTENDED'] === '1' ? $components['VALUES_ONLY'] : array($components['VALUES_ONLY']);
+                                       $tableFields = array_keys($GLOBALS['TYPO3_DB']->cache_fieldType[$components['TABLE']]);
                                } else {
-                                               // Initialize:
-                                       foreach ($components['FIELDS'] as $fN => $fV) {
-                                               $query[$fN]=$fV[0];
+                                       $valuesComponents = $components['EXTENDED'] === '1' ? $components['FIELDS'] : array($components['FIELDS']);
+                                       $tableFields = array_keys($valuesComponents[0]);
+                               }
+
+                               foreach ($valuesComponents as $valuesComponent) {
+                                       $fields = array();
+                                       $fc = 0;
+                                       foreach ($valuesComponent as $fV) {
+                                               $fields[$tableFields[$fc++]] = $fV[0];
                                        }
+                                       $values[] = $fields;
                                }
+                               $query = count($values) === 1 ? $values[0] : $values;
                                break;
                }
 
@@ -351,34 +439,79 @@ class ux_t3lib_sqlparser extends t3lib_sqlparser {
                                                        // Look for sublevel:
                                                if (is_array($v['sub'])) {
                                                        $output .= ' (' . trim($this->compileWhereClause($v['sub'], $functionMapping)) . ')';
-                                               } elseif (isset($v['func'])) {
-                                                       $output .= ' ' . trim($v['modifier']) . ' ' . $v['func']['type'] . ' (' . $this->compileSELECT($v['func']['subquery']) . ')';
+                                               } elseif (isset($v['func']) && $v['func']['type'] === 'EXISTS') {
+                                                       $output .= ' ' . trim($v['modifier']) . ' EXISTS (' . $this->compileSELECT($v['func']['subquery']) . ')';
                                                } else {
 
-                                                               // Set field/table with modifying prefix if any:
-                                                       $output .= ' ' . trim($v['modifier']) . ' ';
-
-                                                               // DBAL-specific: Set calculation, if any:
-                                                       if ($v['calc'] === '&' && $functionMapping) {
-                                                               switch(TRUE) {
-                                                                       case $GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8'):
-                                                                                       // Oracle only knows BITAND(x,y) - sigh
-                                                                               $output .= 'BITAND(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ',' . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1] . ')';
+                                                       if (isset($v['func']) && $v['func']['type'] === 'LOCATE') {
+                                                               $output .= ' ' . trim($v['modifier']);
+                                                               switch (TRUE) {
+                                                                       case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('mssql') && $functionMapping):
+                                                                               $output .= ' CHARINDEX(';
+                                                                               $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
+                                                                               $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
+                                                                               $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
+                                                                               $output .= ')';
+                                                                               break;
+                                                                       case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $functionMapping):
+                                                                               $output .= ' INSTR(';
+                                                                               $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
+                                                                               $output .= ', ' . $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
+                                                                               $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
+                                                                               $output .= ')';
+                                                                               break;
+                                                                       default:
+                                                                               $output .= ' LOCATE(';
+                                                                               $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
+                                                                               $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
+                                                                               $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
+                                                                               $output .= ')';
+                                                                               break;
+                                                               }
+                                                       } elseif (isset($v['func']) && $v['func']['type'] === 'IFNULL') {
+                                                               $output .= ' ' . trim($v['modifier']) . ' ';
+                                                               switch (TRUE) {
+                                                                       case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('mssql') && $functionMapping):
+                                                                               $output .= 'ISNULL';
+                                                                               break;
+                                                                       case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $functionMapping):
+                                                                               $output .= 'NVL';
                                                                                break;
                                                                        default:
-                                                                                       // MySQL, MS SQL Server, PostgreSQL support the &-syntax
-                                                                               $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc'] . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
+                                                                               $output .= 'IFNULL';
                                                                                break;
                                                                }
-                                                       } elseif ($v['calc']) {
-                                                               $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc'];
-                                                               if (isset($v['calc_table'])) {
-                                                                       $output .= trim(($v['calc_table'] ? $v['calc_table'] . '.' : '') . $v['calc_field']);
-                                                               } else {
-                                                                       $output .= $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
+                                                               $output .= '(';
+                                                               $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
+                                                               $output .= ', ' . $v['func']['default'][1] . $this->compileAddslashes($v['func']['default'][0]) . $v['func']['default'][1];
+                                                               $output .= ')';
+                                                       } else {
+
+                                                                       // Set field/table with modifying prefix if any:
+                                                               $output .= ' ' . trim($v['modifier']) . ' ';
+       
+                                                                       // DBAL-specific: Set calculation, if any:
+                                                               if ($v['calc'] === '&' && $functionMapping) {
+                                                                       switch(TRUE) {
+                                                                               case $GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8'):
+                                                                                               // Oracle only knows BITAND(x,y) - sigh
+                                                                                       $output .= 'BITAND(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ',' . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1] . ')';
+                                                                                       break;
+                                                                               default:
+                                                                                               // MySQL, MS SQL Server, PostgreSQL support the &-syntax
+                                                                                       $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc'] . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
+                                                                                       break;
+                                                                       }
+                                                               } elseif ($v['calc']) {
+                                                                       $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . $v['calc'];
+                                                                       if (isset($v['calc_table'])) {
+                                                                               $output .= trim(($v['calc_table'] ? $v['calc_table'] . '.' : '') . $v['calc_field']);
+                                                                       } else {
+                                                                               $output .= $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
+                                                                       }
+                                                               } elseif (!($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $v['comparator'] === 'LIKE' && $functionMapping)) {
+                                                                       $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']);
                                                                }
-                                                       } elseif (!($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $v['comparator'] === 'LIKE' && $functionMapping)) {
-                                                               $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']);
                                                        }
 
                                                                // Set comparator:
index 179c82f..4523afb 100644 (file)
@@ -3,7 +3,7 @@
 ########################################################################
 # Extension Manager/Repository config file for ext "dbal".
 #
-# Auto generated 30-12-2009 13:50
+# Auto generated 14-02-2010 18:58
 #
 # Manual updates:
 # Only the data in the array - everything else is removed by next
@@ -32,13 +32,13 @@ $EM_CONF[$_EXTKEY] = array(
        'author_company' => '',
        'CGLcompliance' => '',
        'CGLcompliance_note' => '',
-       'version' => '1.1.0',
-       '_md5_values_when_last_written' => 'a:32:{s:9:"ChangeLog";s:4:"443b";s:26:"class.ux_db_list_extra.php";s:4:"7b9e";s:21:"class.ux_t3lib_db.php";s:4:"e85d";s:28:"class.ux_t3lib_sqlparser.php";s:4:"2be5";s:16:"ext_autoload.php";s:4:"821a";s:21:"ext_conf_template.txt";s:4:"f5cf";s:12:"ext_icon.gif";s:4:"c9ba";s:17:"ext_localconf.php";s:4:"5280";s:14:"ext_tables.php";s:4:"8414";s:14:"ext_tables.sql";s:4:"1f95";s:27:"doc/class.tslib_fe.php.diff";s:4:"0083";s:14:"doc/manual.sxw";s:4:"b022";s:45:"handlers/class.tx_dbal_handler_openoffice.php";s:4:"d6c1";s:43:"handlers/class.tx_dbal_handler_rawmysql.php";s:4:"2f1b";s:40:"handlers/class.tx_dbal_handler_xmldb.php";s:4:"e363";s:31:"lib/class.tx_dbal_sqlengine.php";s:4:"3807";s:33:"lib/class.tx_dbal_tsparserext.php";s:4:"b005";s:14:"mod1/clear.gif";s:4:"cc11";s:13:"mod1/conf.php";s:4:"6e63";s:14:"mod1/index.php";s:4:"6944";s:18:"mod1/locallang.xml";s:4:"0b57";s:22:"mod1/locallang_mod.xml";s:4:"86ef";s:19:"mod1/moduleicon.gif";s:4:"2b8f";s:10:"res/README";s:4:"be19";s:43:"res/postgresql/postgresql-compatibility.sql";s:4:"5299";s:22:"tests/BaseTestCase.php";s:4:"f736";s:26:"tests/FakeDbConnection.php";s:4:"7bab";s:29:"tests/db_general_testcase.php";s:4:"d8fa";s:28:"tests/db_oracle_testcase.php";s:4:"6961";s:36:"tests/sqlparser_general_testcase.php";s:4:"de57";s:30:"tests/fixtures/oci8.config.php";s:4:"7179";s:34:"tests/fixtures/oci8.config.php.rej";s:4:"06ad";}',
+       'version' => '1.1.1',
+       '_md5_values_when_last_written' => 'a:37:{s:9:"ChangeLog";s:4:"3ce2";s:26:"class.ux_db_list_extra.php";s:4:"7b9e";s:21:"class.ux_t3lib_db.php";s:4:"95c8";s:28:"class.ux_t3lib_sqlparser.php";s:4:"a9d4";s:16:"ext_autoload.php";s:4:"821a";s:21:"ext_conf_template.txt";s:4:"f5cf";s:12:"ext_icon.gif";s:4:"c9ba";s:17:"ext_localconf.php";s:4:"5280";s:14:"ext_tables.php";s:4:"8414";s:14:"ext_tables.sql";s:4:"1f95";s:27:"doc/class.tslib_fe.php.diff";s:4:"0083";s:14:"doc/manual.sxw";s:4:"b022";s:45:"handlers/class.tx_dbal_handler_openoffice.php";s:4:"775f";s:43:"handlers/class.tx_dbal_handler_rawmysql.php";s:4:"2f1b";s:40:"handlers/class.tx_dbal_handler_xmldb.php";s:4:"e363";s:31:"lib/class.tx_dbal_sqlengine.php";s:4:"f1bb";s:33:"lib/class.tx_dbal_tsparserext.php";s:4:"862d";s:14:"mod1/clear.gif";s:4:"cc11";s:13:"mod1/conf.php";s:4:"6e63";s:14:"mod1/index.php";s:4:"6944";s:18:"mod1/locallang.xml";s:4:"0b57";s:22:"mod1/locallang_mod.xml";s:4:"86ef";s:19:"mod1/moduleicon.gif";s:4:"2b8f";s:10:"res/README";s:4:"be19";s:30:"res/oracle/indexed_search.diff";s:4:"ec81";s:23:"res/oracle/realurl.diff";s:4:"86da";s:25:"res/oracle/scheduler.diff";s:4:"7c06";s:27:"res/oracle/templavoila.diff";s:4:"1fd5";s:43:"res/postgresql/postgresql-compatibility.sql";s:4:"034c";s:22:"tests/BaseTestCase.php";s:4:"f736";s:26:"tests/FakeDbConnection.php";s:4:"7bab";s:29:"tests/db_general_testcase.php";s:4:"42f4";s:27:"tests/db_mssql_testcase.php";s:4:"106d";s:28:"tests/db_oracle_testcase.php";s:4:"e710";s:36:"tests/sqlparser_general_testcase.php";s:4:"cc6d";s:31:"tests/fixtures/mssql.config.php";s:4:"56c1";s:30:"tests/fixtures/oci8.config.php";s:4:"7179";}',
        'constraints' => array(
                'depends' => array(
                        'adodb' => '5.10.0-',
                        'php' => '5.2.0-0.0.0',
-                       'typo3' => '4.3.0-4.3.99',
+                       'typo3' => '4.3.0-0.0.0',
                ),
                'conflicts' => array(
                ),
index a4fa5a8..41a59c6 100644 (file)
@@ -27,7 +27,7 @@
 /**
  * Contains an example DBAL handler class
  *
- * $Id: class.tx_dbal_handler_openoffice.php 25889 2009-10-27 10:09:11Z xperseguers $
+ * $Id: class.tx_dbal_handler_openoffice.php 28898 2010-01-16 14:32:35Z xperseguers $
  *
  * @author     Kasper Skaarhoj <kasper@typo3.com>
  */
@@ -134,7 +134,7 @@ class tx_dbal_handler_openoffice extends tx_dbal_sqlengine {
                $this->unzip->putFileToArchive('content.xml', $content_xml['content']);
 
                        // Writing ZIP content back to zip-archive file:
-               $result = $this->unzip->compileZipFile('fileadmin/dbtest_output.sxc');
+               $result = $this->unzip->compileZipFile($GLOBALS['TYPO3_CONF_VARS']['BE']['fileadminDir'] . 'dbtest_output.sxc');
 
                debug($result);
 
index 3a4849d..013dfa0 100644 (file)
@@ -1 +1 @@
-https://svn.typo3.org/TYPO3v4/Extensions/dbal/tags/1.1.0/
+https://svn.typo3.org/TYPO3v4/Extensions/dbal/tags/1.1.1/
index 9ed5ec3..7f43b8f 100644 (file)
@@ -2,7 +2,7 @@
 /***************************************************************
 *  Copyright notice
 *
-*  (c) 2009 Xavier Perseguers <typo3@perseguers.ch>
+*  (c) 2009-2010 Xavier Perseguers <typo3@perseguers.ch>
 *  (c) 2004-2009 Kasper Skaarhoj <kasperYYYY@typo3.com>
 *  All rights reserved
 *
@@ -30,7 +30,7 @@
 /**
  * PHP SQL engine
  *
- * $Id: class.tx_dbal_sqlengine.php 27006 2009-11-25 22:08:07Z xperseguers $
+ * $Id: class.tx_dbal_sqlengine.php 28572 2010-01-08 17:13:29Z xperseguers $
  *
  * @author Kasper Skaarhoj <kasperYYYY@typo3.com>
  * @author Xavier Perseguers <typo3@perseguers.ch>
index e380aad..6271e72 100644 (file)
@@ -2,7 +2,7 @@
 /***************************************************************
 *  Copyright notice
 *
-*  (c) 2009 Xavier Perseguers <typo3@perseguers.ch>
+*  (c) 2009-2010 Xavier Perseguers <typo3@perseguers.ch>
 *  All rights reserved
 *
 *  This script is part of the TYPO3 project. The TYPO3 project is
@@ -29,7 +29,7 @@
 /**
  * Class that renders fields for the Extension Manager configuration.
  *
- * $Id: class.tx_dbal_tsparserext.php 27006 2009-11-25 22:08:07Z xperseguers $
+ * $Id: class.tx_dbal_tsparserext.php 28572 2010-01-08 17:13:29Z xperseguers $
  * @author Xavier Perseguers <typo3@perseguers.ch>
  *
  * @package TYPO3
diff --git a/typo3/sysext/dbal/res/oracle/indexed_search.diff b/typo3/sysext/dbal/res/oracle/indexed_search.diff
new file mode 100644 (file)
index 0000000..94c6061
--- /dev/null
@@ -0,0 +1,13 @@
+Index: ext_tables.sql
+===================================================================
+--- ext_tables.sql     (revision 6890)
++++ ext_tables.sql     (working copy)
+@@ -107,7 +107,7 @@
+ CREATE TABLE index_stat_search (
+   uid int(11) NOT NULL auto_increment,
+   searchstring varchar(255) DEFAULT '' NOT NULL,
+-  searchoptions blob,
++  searchoptions varchar(4000),
+   tstamp int(11) DEFAULT '0' NOT NULL,
+   feuser_id int(11) unsigned DEFAULT '0' NOT NULL,
+   cookie varchar(10) DEFAULT '' NOT NULL,
diff --git a/typo3/sysext/dbal/res/oracle/realurl.diff b/typo3/sysext/dbal/res/oracle/realurl.diff
new file mode 100644 (file)
index 0000000..dab08d6
--- /dev/null
@@ -0,0 +1,66 @@
+Index: ext_tables.sql
+===================================================================
+--- ext_tables.sql     (revision 29851)
++++ ext_tables.sql     (working copy)
+@@ -7,7 +7,7 @@
+       language_id int(11) DEFAULT '0' NOT NULL,
+       rootpage_id int(11) DEFAULT '0' NOT NULL,
+       mpvar tinytext NOT NULL,
+-      pagepath text NOT NULL,
++      pagepath varchar(4000) NOT NULL,
+       expire int(11) DEFAULT '0' NOT NULL,
+       PRIMARY KEY (cache_id),
+@@ -41,7 +41,7 @@
+ CREATE TABLE tx_realurl_chashcache (
+       spurl_hash char(32) DEFAULT '' NOT NULL,
+       chash_string varchar(32) DEFAULT '' NOT NULL,
+-      spurl_string text,
++      spurl_string varchar(4000),
+       PRIMARY KEY (spurl_hash),
+       KEY chash_string (chash_string)
+@@ -55,7 +55,7 @@
+ CREATE TABLE tx_realurl_urldecodecache (
+       url_hash char(32) DEFAULT '' NOT NULL,
+       spurl tinytext NOT NULL,
+-      content blob NOT NULL,
++      content varchar(4000) NOT NULL,
+       page_id int(11) DEFAULT '0' NOT NULL,
+       rootpage_id int(11) DEFAULT '0' NOT NULL,
+       tstamp int(11) DEFAULT '0' NOT NULL,
+@@ -73,7 +73,7 @@
+       url_hash char(32) DEFAULT '' NOT NULL,
+       origparams tinytext NOT NULL,
+       internalExtras tinytext NOT NULL,
+-      content text NOT NULL,
++      content varchar(4000) NOT NULL,
+       page_id int(11) DEFAULT '0' NOT NULL,
+       tstamp int(11) DEFAULT '0' NOT NULL,
+@@ -83,9 +83,9 @@
+ CREATE TABLE tx_realurl_errorlog (
+       url_hash int(11) DEFAULT '0' NOT NULL,
+-      url text NOT NULL,
+-      error text NOT NULL,
+-      last_referer text NOT NULL,
++      url varchar(4000) NOT NULL,
++      error varchar(4000) NOT NULL,
++      last_referer varchar(4000) NOT NULL,
+       counter int(11) DEFAULT '0' NOT NULL,
+       cr_date int(11) DEFAULT '0' NOT NULL,
+       tstamp int(11) DEFAULT '0' NOT NULL,
+@@ -97,9 +97,9 @@
+ CREATE TABLE tx_realurl_redirects (
+       url_hash int(11) DEFAULT '0' NOT NULL,
+-      url text NOT NULL,
+-      destination text NOT NULL,
+-      last_referer text NOT NULL,
++      url varchar(4000) NOT NULL,
++      destination varchar(4000) NOT NULL,
++      last_referer varchar(4000) NOT NULL,
+       counter int(11) DEFAULT '0' NOT NULL,
+       tstamp int(11) DEFAULT '0' NOT NULL,
+       has_moved int(11) DEFAULT '0' NOT NULL,
diff --git a/typo3/sysext/dbal/res/oracle/scheduler.diff b/typo3/sysext/dbal/res/oracle/scheduler.diff
new file mode 100644 (file)
index 0000000..17982a4
--- /dev/null
@@ -0,0 +1,15 @@
+Index: ext_tables.sql
+===================================================================
+--- ext_tables.sql     (revision 6890)
++++ ext_tables.sql     (working copy)
+@@ -10,8 +10,8 @@
+       lastexecution_time int(11) unsigned DEFAULT '0' NOT NULL,
+       lastexecution_failure text NOT NULL,
+       lastexecution_context char(3) DEFAULT '' NOT NULL,
+-      serialized_task_object blob,
+-      serialized_executions blob,
++      serialized_task_object varchar(4000),
++      serialized_executions varchar(4000),
+       PRIMARY KEY (uid),
+       KEY index_nextexecution (nextexecution)
+ );
diff --git a/typo3/sysext/dbal/res/oracle/templavoila.diff b/typo3/sysext/dbal/res/oracle/templavoila.diff
new file mode 100644 (file)
index 0000000..7a6de97
--- /dev/null
@@ -0,0 +1,13 @@
+Index: ext_tables.sql
+===================================================================
+--- ext_tables.sql     (revision 29620)
++++ ext_tables.sql     (working copy)
+@@ -24,7 +24,7 @@
+       title varchar(60) DEFAULT '' NOT NULL,
+       datastructure varchar(100) DEFAULT '' NOT NULL,
+       fileref tinytext,
+-      templatemapping blob,
++      templatemapping mediumtext,
+       previewicon tinytext,
+       description tinytext,
+       rendertype varchar(32) DEFAULT '' NOT NULL,
index ad94f10..f2f52ad 100644 (file)
 -- 
 -- The functions add compatibility operators for PostgreSQL to make sure comparison is possible and the SQL doesn't return an error.
 --
--- $Id: postgresql-compatibility.sql 25943 2009-10-28 13:26:30Z xperseguers $
+-- Note: You may consider having a look at project mysqlcompat on http://pgfoundry.org/projects/mysqlcompat
+--       and report in DBAL bugtracker if you need another compatibility operator added.
+--
+-- $Id: postgresql-compatibility.sql 29977 2010-02-13 13:18:32Z xperseguers $
 -- R. van Twisk <typo3@rvt.dds.nl>
 
 
@@ -57,6 +60,22 @@ CREATE OPERATOR ~~ (PROCEDURE = t3compat_operator_like, LEFTARG = text, RIGHTARG
 CREATE OPERATOR = (PROCEDURE = t3compat_operator_eq, LEFTARG = integer, RIGHTARG = text);
 CREATE OPERATOR = (PROCEDURE = t3compat_operator_eq, LEFTARG = text, RIGHTARG = integer);
 
+-- LOCATE()
+CREATE OR REPLACE FUNCTION locate(text, text, integer)
+RETURNS integer AS $$
+SELECT POSITION($1 IN SUBSTRING ($2 FROM $3)) + $3 - 1
+$$ IMMUTABLE STRICT LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION locate(text, text)
+RETURNS integer AS $$
+SELECT locate($1, $2, 1)
+$$ IMMUTABLE STRICT LANGUAGE SQL;
+
+-- IFNULL
+CREATE OR REPLACE FUNCTION ifnull(anyelement, anyelement)
+RETURNS anyelement AS $$
+SELECT COALESCE($1, $2)
+$$ IMMUTABLE STRICT LANGUAGE SQL;
 
 -- Remove Compatibility operators
 --
@@ -68,4 +87,7 @@ CREATE OPERATOR = (PROCEDURE = t3compat_operator_eq, LEFTARG = text, RIGHTARG =
 --DROP FUNCTION t3compat_operator_like(text, integer);
 --DROP FUNCTION t3compat_operator_eq(integer, text);
 --DROP FUNCTION t3compat_operator_eq(text, integer);
+--DROP FUNCTION locate(text, text);
+--DROP FUNCTION locate(text, text, integer);
+--DROP FUNCTION ifnull(anyelement, anyelement);
 
index 1bd4d6a..e5fd3bd 100644 (file)
@@ -28,7 +28,7 @@ require_once('BaseTestCase.php');
 /**
  * Testcase for class ux_t3lib_db.
  * 
- * $Id: db_general_testcase.php 27006 2009-11-25 22:08:07Z xperseguers $
+ * $Id: db_general_testcase.php 29978 2010-02-13 13:45:56Z xperseguers $
  *
  * @author Xavier Perseguers <typo3@perseguers.ch>
  *
@@ -193,5 +193,54 @@ class db_general_testcase extends BaseTestCase {
                $expected = 'INSERT INTO tx_test_dbal ( foo, foobar ) VALUES ( \'-9223372036854775808\', \'-9223372036854775808\' )';
                $this->assertEquals($expected, $query);
        }
+
+       /**
+        * @test
+        * http://bugs.typo3.org/view.php?id=12858
+        */
+       public function sqlForInsertWithMultipleRowsIsValid() {
+               $fields = array('uid', 'pid', 'title', 'body');
+               $rows = array(
+                       array('1', '2', 'Title #1', 'Content #1'),
+                       array('3', '4', 'Title #2', 'Content #2'),
+                       array('5', '6', 'Title #3', 'Content #3'),
+               );
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->INSERTmultipleRows('tt_content', $fields, $rows));
+
+               $expected = 'INSERT INTO tt_content (uid, pid, title, body) VALUES ';
+               $expected .= "('1', '2', 'Title #1', 'Content #1'), ";
+               $expected .= "('3', '4', 'Title #2', 'Content #2'), ";
+               $expected .= "('5', '6', 'Title #3', 'Content #3')";
+
+               $this->assertEquals($expected, $query);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=4493
+        */
+       public function minFunctionAndInOperatorCanBeParsed() {
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       '*',
+                       'pages',
+                       'MIN(uid) IN (1,2,3,4)'
+               ));
+               $expected = 'SELECT * FROM pages WHERE MIN(uid) IN (1,2,3,4)';
+               $this->assertEquals($expected, $query);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=4493
+        */
+       public function maxFunctionAndInOperatorCanBeParsed() {
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       '*',
+                       'pages',
+                       'MAX(uid) IN (1,2,3,4)'
+               ));
+               $expected = 'SELECT * FROM pages WHERE MAX(uid) IN (1,2,3,4)';
+               $this->assertEquals($expected, $query);
+       }
 }
 ?>
\ No newline at end of file
diff --git a/typo3/sysext/dbal/tests/db_mssql_testcase.php b/typo3/sysext/dbal/tests/db_mssql_testcase.php
new file mode 100644 (file)
index 0000000..027d85d
--- /dev/null
@@ -0,0 +1,245 @@
+<?php
+/***************************************************************
+*  Copyright notice
+*
+*  (c) 2009 Xavier Perseguers <typo3@perseguers.ch>
+*  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.
+*
+*  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('BaseTestCase.php');
+require_once('FakeDbConnection.php');
+
+/**
+ * Testcase for class ux_t3lib_db. Testing MS SQL database handling.
+ * 
+ * $Id$
+ *
+ * @author Xavier Perseguers <typo3@perseguers.ch>
+ *
+ * @package TYPO3
+ * @subpackage dbal
+ */
+class db_mssql_testcase extends BaseTestCase {
+
+       /**
+        * @var t3lib_db
+        */
+       protected $db;
+
+       /**
+        * @var array
+        */
+       protected $dbalConfig;
+
+       /**
+        * Prepares the environment before running a test.
+        */
+       public function setUp() {
+                       // Backup DBAL configuration
+               $this->dbalConfig = $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'];
+                       // Backup database connection
+               $this->db = $GLOBALS['TYPO3_DB'];
+                       // Reconfigure DBAL to use MS SQL
+               require('fixtures/mssql.config.php');
+
+               $className =  self::buildAccessibleProxy('ux_t3lib_db');
+               $GLOBALS['TYPO3_DB'] = new $className;
+               $parserClassName = self::buildAccessibleProxy('ux_t3lib_sqlparser');
+               $GLOBALS['TYPO3_DB']->SQLparser = new $parserClassName;
+
+                       // Initialize a fake MS SQL connection
+               FakeDbConnection::connect($GLOBALS['TYPO3_DB'], 'mssql');
+
+               $this->assertTrue($GLOBALS['TYPO3_DB']->handlerInstance['_DEFAULT']->isConnected());
+       }
+
+       /**
+        * Cleans up the environment after running a test.
+        */
+       public function tearDown() {
+                       // Clear DBAL-generated cache files
+               $GLOBALS['TYPO3_DB']->clearCachedFieldInfo();
+                       // Restore DBAL configuration
+               $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'] = $this->dbalConfig;
+                       // Restore DB connection
+               $GLOBALS['TYPO3_DB'] = $this->db;
+       }
+
+       /**
+        * Cleans a SQL query.
+        *  
+        * @param mixed $sql
+        * @return mixed (string or array)
+        */
+       private function cleanSql($sql) {
+               if (!is_string($sql)) {
+                       return $sql;
+               }
+
+               $sql = str_replace("\n", ' ', $sql);
+               $sql = preg_replace('/\s+/', ' ', $sql);
+               return trim($sql);
+       }
+
+       /**
+        * @test 
+        */
+       public function configurationIsUsingAdodbAndDriverMssql() {
+               $configuration = $GLOBALS['TYPO3_DB']->conf['handlerCfg'];
+               $this->assertTrue(is_array($configuration) && count($configuration) > 0, 'No configuration found');
+               $this->assertEquals('adodb', $configuration['_DEFAULT']['type']);
+               $this->assertTrue($GLOBALS['TYPO3_DB']->runningADOdbDriver('mssql') !== FALSE, 'Not using mssql driver');
+       }
+
+       /** 
+        * @test
+        */
+       public function tablesWithMappingAreDetected() {
+               $tablesWithMapping = array_keys($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping']);
+
+               foreach ($GLOBALS['TYPO3_DB']->cache_fieldType as $table => $fieldTypes) {
+                       $tableDef = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table);
+
+                       if (in_array($table, $tablesWithMapping)) {
+                               self::assertTrue(is_array($tableDef), 'Table ' . $table . ' was expected to need mapping');
+                       } else {
+                               self::assertFalse($tableDef, 'Table ' . $table . ' was not expected to need mapping');
+                       }
+               }
+       }
+
+       ///////////////////////////////////////
+       // Tests concerning remapping with
+       // external (non-TYPO3) databases
+       ///////////////////////////////////////
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13490
+        */
+       public function canRemapPidToZero() {
+               $selectFields = 'uid, FirstName, LastName';
+               $fromTables   = 'Members';
+               $whereClause  = 'pid=0 AND cruser_id=1';
+               $groupBy      = '';
+               $orderBy      = '';
+
+               $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
+
+               $expected = 'SELECT "MemberID", "FirstName", "LastName" FROM "Members" WHERE 0 = 0 AND 1 = 1';
+               $this->assertEquals($expected, $query);
+       }
+
+       ///////////////////////////////////////
+       // Tests concerning advanced operators
+       ///////////////////////////////////////
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13134
+        */
+       public function locateStatementIsProperlyQuoted() {
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       '*, CASE WHEN' .
+                               ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure)>0 THEN 2' .
+                               ' ELSE 1' . 
+                       ' END AS scope',
+                       'tx_templavoila_tmplobj',
+                       '1=1'
+               ));
+               $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "datastructure") > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
+               $this->assertEquals($expected, $query);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13134
+        */
+       public function locateStatementWithPositionIsProperlyQuoted() {
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       '*, CASE WHEN' .
+                               ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure, 4)>0 THEN 2' .
+                               ' ELSE 1' . 
+                       ' END AS scope',
+                       'tx_templavoila_tmplobj',
+                       '1=1'
+               ));
+               $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "datastructure", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
+               $this->assertEquals($expected, $query);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13134
+        */
+       public function locateStatementIsProperlyRemapped() {
+               $selectFields = '*, CASE WHEN' .
+                               ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure, 4)>0 THEN 2' .
+                               ' ELSE 1' . 
+                       ' END AS scope';
+               $fromTables   = 'tx_templavoila_tmplobj';
+               $whereClause  = '1=1';
+               $groupBy      = '';
+               $orderBy      = '';
+
+               $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
+
+               $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "ds", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
+               $this->assertEquals($expected, $query);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13134
+        */
+       public function locateStatementWithExternalTableIsProperlyRemapped() {
+               $selectFields = '*, CASE WHEN' .
+                               ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', tx_templavoila_tmplobj.datastructure, 4)>0 THEN 2' .
+                               ' ELSE 1' . 
+                       ' END AS scope';
+               $fromTables   = 'tx_templavoila_tmplobj';
+               $whereClause  = '1=1';
+               $groupBy      = '';
+               $orderBy      = '';
+
+               $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
+
+               $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "tx_templavoila_tmplobj"."ds", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
+               $this->assertEquals($expected, $query);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=6196
+        */
+       public function IfNullIsProperlyRemapped() {
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       '*',
+                       'tt_news_cat_mm',
+                       'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)'
+               ));
+               $expected = 'SELECT * FROM "tt_news_cat_mm" WHERE ISNULL("tt_news_cat_mm"."uid_foreign", 0) IN (21,22)';
+               $this->assertEquals($expected, $query);
+       }
+}
+?>
\ No newline at end of file
index 7202732..77ea8a3 100644 (file)
@@ -29,7 +29,7 @@ require_once('FakeDbConnection.php');
 /**
  * Testcase for class ux_t3lib_db. Testing Oracle database handling.
  * 
- * $Id: db_oracle_testcase.php 27623 2009-12-11 13:40:50Z xperseguers $
+ * $Id: db_oracle_testcase.php 29977 2010-02-13 13:18:32Z xperseguers $
  *
  * @author Xavier Perseguers <typo3@perseguers.ch>
  *
@@ -139,6 +139,73 @@ class db_oracle_testcase extends BaseTestCase {
                $this->assertEquals($expected, $query);
        }
 
+       /**
+        * @test
+        */
+       public function canCompileInsertWithFields() {
+               $parseString = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
+               $parseString .= "VALUES ('1', '0', '2', '0', 'Africa');";
+               $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseINSERT', $parseString);
+
+               $this->assertTrue(is_array($components), $components);
+               $insert = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('compileINSERT', $components);
+
+               $expected = array(
+                       'uid' => '1',
+                       'pid' => '0',
+                       'tr_iso_nr' => '2',
+                       'tr_parent_iso_nr' => '0',
+                       'tr_name_en' => 'Africa',
+               );
+               $this->assertEquals($expected, $insert);
+       }
+
+       /**
+        * @test
+        * http://bugs.typo3.org/view.php?id=13209
+        */
+       public function canCompileExtendedInsert() {
+               $parseString = "INSERT INTO static_territories VALUES ('1', '0', '2', '0', 'Africa'),('2', '0', '9', '0', 'Oceania')," .
+                       "('3', '0', '19', '0', 'Americas'),('4', '0', '142', '0', 'Asia');";
+               $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseINSERT', $parseString);
+
+               $this->assertTrue(is_array($components), $components);
+               $insert = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('compileINSERT', $components);
+
+               $this->assertEquals(4, count($insert));
+
+               for ($i = 0; $i < count($insert); $i++) {
+                       foreach (t3lib_div::trimExplode(',', 'uid,pid,tr_iso_nr,tr_parent_iso_nr,tr_name_en') as $field) {
+                               $this->assertTrue(isset($insert[$i][$field]), 'Could not find ' . $field . ' column');
+                       }
+               }
+       }
+
+       /**
+        * @test
+        * http://bugs.typo3.org/view.php?id=12858
+        */
+       public function sqlForInsertWithMultipleRowsIsValid() {
+               $fields = array('uid', 'pid', 'title', 'body');
+               $rows = array(
+                       array('1', '2', 'Title #1', 'Content #1'),
+                       array('3', '4', 'Title #2', 'Content #2'),
+                       array('5', '6', 'Title #3', 'Content #3'),
+               );
+               $query = $GLOBALS['TYPO3_DB']->INSERTmultipleRows('tt_content', $fields, $rows);
+
+               $expected[0] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'1\', \'2\', \'Title #1\', \'Content #1\' )';
+               $expected[1] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'3\', \'4\', \'Title #2\', \'Content #2\' )';
+               $expected[2] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'5\', \'6\', \'Title #3\', \'Content #3\' )';
+
+               $this->assertEquals(count($expected), count($query));
+               for ($i = 0; $i < count($query); $i++) {
+                       $this->assertTrue(is_array($query[$i]), 'Expected array: ' . $query[$i]);
+                       $this->assertEquals(1, count($query[$i]));
+                       $this->assertEquals($expected[$i], $this->cleanSql($query[$i][0]));
+               }
+       }
+
        ///////////////////////////////////////
        // Tests concerning quoting
        ///////////////////////////////////////
@@ -160,6 +227,16 @@ class db_oracle_testcase extends BaseTestCase {
 
        /**
         * @test
+        * http://bugs.typo3.org/view.php?id=13504
+        */
+       public function truncateQueryIsProperlyQuoted() {
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->TRUNCATEquery('be_users'));
+               $expected = 'TRUNCATE TABLE "be_users"';
+               $this->assertEquals($expected, $query);
+       }
+
+       /**
+        * @test
         * @see http://bugs.typo3.org/view.php?id=2438
         */
        public function distinctFieldIsProperlyQuoted() {
@@ -253,6 +330,20 @@ class db_oracle_testcase extends BaseTestCase {
                $this->assertEquals($expected, $query);
        }
 
+       /**
+        * @test
+        * http://bugs.typo3.org/view.php?id=13422
+        */
+       public function numericColumnsAreNotQuoted() {
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       '1',
+                       'be_users',
+                       'username = \'_cli_scheduler\' AND admin = 0 AND be_users.deleted = 0'
+               ));
+               $expected = 'SELECT 1 FROM "be_users" WHERE "username" = \'_cli_scheduler\' AND "admin" = 0 AND "be_users"."deleted" = 0';
+               $this->assertEquals($expected, $query);
+       }
+
        ///////////////////////////////////////
        // Tests concerning remapping
        ///////////////////////////////////////
@@ -588,5 +679,121 @@ class db_oracle_testcase extends BaseTestCase {
                $expected .= ')';
                $this->assertEquals($expected, $query);
        }
+
+       ///////////////////////////////////////
+       // Tests concerning advanced operators
+       ///////////////////////////////////////
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13135
+        */
+       public function caseStatementIsProperlyQuoted() {
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       'process_id, CASE active' .
+                               ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tx_crawler_process') .
+                               ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tx_crawler_process') .
+                               ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tx_crawler_process') . 
+                       ' END AS number',
+                       'tx_crawler_process',
+                       '1=1'
+               ));
+               $expected = 'SELECT "process_id", CASE "active" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" FROM "tx_crawler_process" WHERE 1 = 1';
+               $this->assertEquals($expected, $query);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13135
+        */
+       public function caseStatementIsProperlyRemapped() {
+               $selectFields = 'process_id, CASE active' .
+                               ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tx_crawler_process') .
+                               ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tx_crawler_process') .
+                               ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tx_crawler_process') . 
+                       ' END AS number';
+               $fromTables   = 'tx_crawler_process';
+               $whereClause  = '1=1';
+               $groupBy      = '';
+               $orderBy      = '';
+
+               $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
+
+               $expected = 'SELECT "ps_id", CASE "is_active" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" ';
+               $expected .= 'FROM "tx_crawler_ps" WHERE 1 = 1';
+               $this->assertEquals($expected, $query);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13135
+        */
+       public function caseStatementWithExternalTableIsProperlyRemapped() {
+               $selectFields = 'process_id, CASE tt_news.uid' .
+                               ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tt_news') .
+                               ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tt_news') .
+                               ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tt_news') . 
+                       ' END AS number';
+               $fromTables   = 'tx_crawler_process, tt_news';
+               $whereClause  = '1=1';
+               $groupBy      = '';
+               $orderBy      = '';
+
+               $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
+
+               $expected = 'SELECT "ps_id", CASE "ext_tt_news"."news_uid" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" ';
+               $expected .= 'FROM "tx_crawler_ps", "ext_tt_news" WHERE 1 = 1';
+               $this->assertEquals($expected, $query);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13134
+        */
+       public function locateStatementIsProperlyQuoted() {
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       '*, CASE WHEN' .
+                               ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure)>0 THEN 2' .
+                               ' ELSE 1' . 
+                       ' END AS scope',
+                       'tx_templavoila_tmplobj',
+                       '1=1'
+               ));
+               $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\') > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
+               $this->assertEquals($expected, $query);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13134
+        */
+       public function locateStatementWithPositionIsProperlyQuoted() {
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       '*, CASE WHEN' .
+                               ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure, 4)>0 THEN 2' .
+                               ' ELSE 1' . 
+                       ' END AS scope',
+                       'tx_templavoila_tmplobj',
+                       '1=1'
+               ));
+               $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\', 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
+               $this->assertEquals($expected, $query);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=6196
+        */
+       public function IfNullIsProperlyRemapped() {
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       '*',
+                       'tt_news_cat_mm',
+                       'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)'
+               ));
+               $expected = 'SELECT * FROM "tt_news_cat_mm" WHERE NVL("tt_news_cat_mm"."uid_foreign", 0) IN (21,22)';
+               $this->assertEquals($expected, $query);
+       }
 }
 ?>
\ No newline at end of file
diff --git a/typo3/sysext/dbal/tests/fixtures/mssql.config.php b/typo3/sysext/dbal/tests/fixtures/mssql.config.php
new file mode 100644 (file)
index 0000000..196230c
--- /dev/null
@@ -0,0 +1,38 @@
+<?php
+/**
+ * MS SQL configuration
+ * 
+ * $Id$
+ *
+ * @author Xavier Perseguers <typo3@perseguers.ch>
+ *
+ * @package TYPO3
+ * @subpackage dbal
+ */
+global $TYPO3_CONF_VARS;
+
+$TYPO3_CONF_VARS['EXTCONF']['dbal']['handlerCfg'] = array(
+       '_DEFAULT' => array( 
+               'type' => 'adodb', 
+               'config' => array(
+                       'driver' => 'mssql',
+                       'useNameQuote' => FALSE,
+               ),
+       ), 
+);
+
+$TYPO3_CONF_VARS['EXTCONF']['dbal']['mapping'] = array(
+       'tx_templavoila_tmplobj' => array(
+               'mapFieldNames' => array(
+                       'datastructure' => 'ds',
+               ),
+       ),
+       'Members' => array(
+               'mapFieldNames' => array(
+                       'pid' => '0',
+                       'cruser_id' => '1',
+                       'uid' => 'MemberID',
+               ),
+       ),
+);
+?>
\ No newline at end of file
index e127df1..4e6abbd 100644 (file)
@@ -28,7 +28,7 @@ require_once('BaseTestCase.php');
 /**
  * Testcase for class ux_t3lib_sqlparser
  * 
- * $Id: sqlparser_general_testcase.php 28103 2009-12-28 00:40:06Z xperseguers $
+ * $Id: sqlparser_general_testcase.php 30009 2010-02-14 17:41:47Z xperseguers $
  *
  * @author Xavier Perseguers <typo3@perseguers.ch>
  *
@@ -190,6 +190,153 @@ class sqlparser_general_testcase extends BaseTestCase {
                $this->assertEquals($expected, $actual);
        }
 
+       /**
+        * @test
+        * http://bugs.typo3.org/view.php?id=13504
+        */
+       public function canParseTruncateTable() {
+               $sql = 'TRUNCATE TABLE be_users';
+               $expected = $sql;
+               $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
+
+               $this->assertEquals($expected, $actual);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13412
+        */
+       public function canParseAndCompileBetweenOperator() {
+               $parseString = '((scheduled BETWEEN 1265068628 AND 1265068828 ) OR scheduled <= 1265068728) AND NOT exec_time AND NOT process_id AND page_id=1 AND parameters_hash = \'854e9a2a77\'';
+               $where = $this->fixture->parseWhereClause($parseString);
+
+               $this->assertTrue(is_array($where), $where);
+               $this->assertTrue(empty($parseString), 'parseString is not empty');
+
+               $whereClause = $this->cleanSql($this->fixture->compileWhereClause($where));
+               $expected = '((scheduled BETWEEN 1265068628 AND 1265068828) OR scheduled <= 1265068728) AND NOT exec_time AND NOT process_id AND page_id = 1 AND parameters_hash = \'854e9a2a77\'';
+               $this->assertEquals($expected, $whereClause);
+       }
+
+       /**
+        * @test
+        * http://bugs.typo3.org/view.php?id=13430
+        */
+       public function canParseInsertWithoutSpaceAfterValues() {
+               $parseString = "INSERT INTO static_country_zones VALUES('483', '0', 'NL', 'NLD', '528', 'DR', 'Drenthe', '');";
+               $components = $this->fixture->_callRef('parseINSERT', $parseString);
+
+               $this->assertTrue(is_array($components), $components);
+               $insert = $this->cleanSql($this->fixture->_callRef('compileINSERT', $components));
+               $expected = "INSERT INTO static_country_zones VALUES ('483', '0', 'NL', 'NLD', '528', 'DR', 'Drenthe', '')";
+               $this->assertEquals($expected, $insert);
+       }
+
+       /**
+        * @test
+        * http://bugs.typo3.org/view.php?id=13430
+        */
+       public function canParseInsertWithSpaceAfterValues() {
+               $parseString = "INSERT INTO static_country_zones VALUES ('483', '0', 'NL', 'NLD', '528', 'DR', 'Drenthe', '');";
+               $components = $this->fixture->_callRef('parseINSERT', $parseString);
+
+               $this->assertTrue(is_array($components), $components);
+               $insert = $this->cleanSql($this->fixture->_callRef('compileINSERT', $components));
+               $expected = "INSERT INTO static_country_zones VALUES ('483', '0', 'NL', 'NLD', '528', 'DR', 'Drenthe', '')";
+               $this->assertEquals($expected, $insert);
+       }
+
+       /**
+        * @test
+        */
+       public function canParseInsertWithFields() {
+               $parseString = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
+               $parseString .= "VALUES ('1', '0', '2', '0', 'Africa');";
+               $components = $this->fixture->_callRef('parseINSERT', $parseString);
+
+               $this->assertTrue(is_array($components), $components);
+               $insert = $this->cleanSql($this->fixture->_callRef('compileINSERT', $components));
+               $expected = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
+               $expected .= "VALUES ('1', '0', '2', '0', 'Africa')";
+               $this->assertEquals($expected, $insert);
+       }
+
+       /**
+        * @test
+        * http://bugs.typo3.org/view.php?id=13209
+        */
+       public function canParseExtendedInsert() {
+               $parseString = "INSERT INTO static_territories VALUES ('1', '0', '2', '0', 'Africa'),('2', '0', '9', '0', 'Oceania')," .
+                       "('3', '0', '19', '0', 'Americas'),('4', '0', '142', '0', 'Asia');";
+               $components = $this->fixture->_callRef('parseINSERT', $parseString);
+
+               $this->assertTrue(is_array($components), $components);
+               $insert = $this->cleanSql($this->fixture->_callRef('compileINSERT', $components));
+               $expected = "INSERT INTO static_territories VALUES ('1', '0', '2', '0', 'Africa'), ('2', '0', '9', '0', 'Oceania'), " .
+                       "('3', '0', '19', '0', 'Americas'), ('4', '0', '142', '0', 'Asia')";
+               $this->assertEquals($expected, $insert);
+       }
+
+       /**
+        * @test
+        * http://bugs.typo3.org/view.php?id=13209
+        */
+       public function canParseExtendedInsertWithFields() {
+               $parseString = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
+               $parseString .= "VALUES ('1', '0', '2', '0', 'Africa'),('2', '0', '9', '0', 'Oceania');";
+               $components = $this->fixture->_callRef('parseINSERT', $parseString);
+
+               $this->assertTrue(is_array($components), $components);
+               $insert = $this->cleanSql($this->fixture->_callRef('compileINSERT', $components));
+               $expected = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
+               $expected .= "VALUES ('1', '0', '2', '0', 'Africa'), ('2', '0', '9', '0', 'Oceania')";
+               $this->assertEquals($expected, $insert);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=6196
+        */
+       public function canParseIfNullOperator() {
+               $parseString = 'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)';
+               $whereParts = $this->fixture->parseWhereClause($parseString);
+
+               $this->assertTrue(is_array($whereParts), $whereParts);
+               $this->assertTrue(empty($parseString), 'parseString is not empty');
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=6196
+        */
+       public function canParseIfNullOperatorWithAdditionalClauses() {
+               $parseString = '1=1 AND IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22) AND tt_news.sys_language_uid IN (0,-1) ';
+               $parseString .= 'AND tt_news.pid > 0 AND tt_news.pid IN (61) AND tt_news.deleted=0 AND tt_news.t3ver_state<=0 ';
+               $parseString .= 'AND tt_news.hidden=0 AND tt_news.starttime<=1266065460 AND (tt_news.endtime=0 OR tt_news.endtime>1266065460) ';
+               $parseString .= 'AND (tt_news.fe_group=\'\' OR tt_news.fe_group IS NULL OR tt_news.fe_group=\'0\' ';
+               $parseString .= 'OR (tt_news.fe_group LIKE \'%,0,%\' OR tt_news.fe_group LIKE \'0,%\' OR tt_news.fe_group LIKE \'%,0\' ';
+               $parseString .= 'OR tt_news.fe_group=\'0\') OR (tt_news.fe_group LIKE \'%,-1,%\' OR tt_news.fe_group LIKE \'-1,%\' ';
+               $parseString .= 'OR tt_news.fe_group LIKE \'%,-1\' OR tt_news.fe_group=\'-1\'))';
+               $whereParts = $this->fixture->parseWhereClause($parseString);
+
+               $this->assertTrue(is_array($whereParts), $whereParts);
+               $this->assertTrue(empty($parseString), 'parseString is not empty');
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=6196
+        */
+       public function canCompileIfNullOperator() {
+               $parseString = 'SELECT * FROM tx_irfaq_q_cat_mm WHERE IFNULL(tx_irfaq_q_cat_mm.uid_foreign,0) = 1';
+               $components = $this->fixture->_callRef('parseSELECT', $parseString);
+
+               $this->assertTrue(is_array($components), $components);
+               $select = $this->cleanSql($this->fixture->_callRef('compileSELECT', $components));
+               $expected = 'SELECT * FROM tx_irfaq_q_cat_mm WHERE IFNULL(tx_irfaq_q_cat_mm.uid_foreign, 0) = 1';
+               $this->assertEquals($expected, $select);
+       }
+
        ///////////////////////////////////////
        // Tests concerning JOINs
        ///////////////////////////////////////
@@ -281,7 +428,7 @@ class sqlparser_general_testcase extends BaseTestCase {
         * @test
         * @see http://bugs.typo3.org/view.php?id=4466
         */
-       public function indexMayContainALengthRestriction() {
+       public function indexMayContainALengthRestrictionInCreateTable() {
                $parseString = '
                        CREATE TABLE tx_realurl_uniqalias (
                                uid int(11) NOT NULL auto_increment,
@@ -305,6 +452,16 @@ class sqlparser_general_testcase extends BaseTestCase {
                $this->assertTrue(is_array($createTables), $createTables);
        }
 
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=12829
+        */
+       public function indexMayContainALengthRestrictionInAlterTable() {
+               $parseString = 'ALTER TABLE tx_realurl_uniqalias ADD KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)';
+               $alterTables = $this->fixture->_callRef('parseALTERTABLE', $parseString);
+               $this->assertTrue(is_array($alterTables), $alterTables);
+       }
+
        ///////////////////////////////////////
        // Tests concerning subqueries
        ///////////////////////////////////////
@@ -356,5 +513,94 @@ class sqlparser_general_testcase extends BaseTestCase {
 
                $this->assertEquals($expected, $actual);
        }
+
+       ///////////////////////////////////////
+       // Tests concerning advanced operators
+       ///////////////////////////////////////
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13135
+        */
+       public function caseWithBooleanConditionIsSupportedInFields() {
+               $parseString = 'CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column';
+               $fieldList = $this->fixture->parseFieldList($parseString);
+
+               $this->assertTrue(is_array($fieldList), $fieldList);
+               $this->assertTrue(empty($parseString), 'parseString is not empty');
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13135
+        */
+       public function caseWithBooleanConditionIsProperlyCompiled() {
+               $sql = 'SELECT CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
+               $expected = 'SELECT CASE WHEN 1 > 0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
+               $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
+
+               $this->assertEquals($expected, $actual);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13135
+        */
+       public function caseWithMultipleWhenIsSupportedInFields() {
+               $parseString = 'CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number';
+               $fieldList = $this->fixture->parseFieldList($parseString);
+
+               $this->assertTrue(is_array($fieldList), $fieldList);
+               $this->assertTrue(empty($parseString), 'parseString is not empty');
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13135
+        */
+       public function caseWithMultipleWhenIsProperlyCompiled() {
+               $sql = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
+               $expected = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
+               $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
+
+               $this->assertEquals($expected, $actual);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13134
+        */
+       public function locateIsSupported() {
+               $sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure)>0';
+               $expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure) > 0';
+               $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
+
+               $this->assertEquals($expected, $actual);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13134
+        */
+       public function locateWithPositionIsSupported() {
+               $sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\'  , datastructure  ,10)>0';
+               $expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure, 10) > 0';
+               $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
+
+               $this->assertEquals($expected, $actual);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13134
+        * @see http://bugs.typo3.org/view.php?id=13135
+        */
+       public function locateWithinCaseIsSupported() {
+               $sql = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure)>0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
+               $expected = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure) > 0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
+               $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
+
+               $this->assertEquals($expected, $actual);
+       }
 }
 ?>
\ No newline at end of file