+2010-06-11 Xavier Perseguers <typo3@perseguers.ch>
+
+ * Fixed bug #14405: Rename the test cases from *_testcase to *Test
+
2010-06-03 Xavier Perseguers <typo3@perseguers.ch>
* Fixed bug #14479: Speed up Oracle layer by not defaulting to dbms_lob.instr for LIKE query (thanks to Michiel Roos)
--- /dev/null
+<?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');
+
+/**
+ * Testcase for class ux_t3lib_db.
+ *
+ * $Id$
+ *
+ * @author Xavier Perseguers <typo3@perseguers.ch>
+ *
+ * @package TYPO3
+ * @subpackage dbal
+ */
+class dbGeneralTest extends BaseTestCase {
+
+ /**
+ * @var t3lib_db
+ */
+ protected $db;
+
+ /**
+ * @var array
+ */
+ protected $loadedExtensions;
+
+ /**
+ * @var array
+ */
+ protected $temporaryFiles;
+
+ /**
+ * Prepares the environment before running a test.
+ */
+ public function setUp() {
+ // Backup list of loaded extensions
+ $this->loadedExtensions = $GLOBALS['TYPO3_LOADED_EXT'];
+ // Backup database connection
+ $this->db = $GLOBALS['TYPO3_DB'];
+ $this->temporaryFiles = array();
+
+ $className = self::buildAccessibleProxy('ux_t3lib_db');
+ $GLOBALS['TYPO3_DB'] = new $className;
+ $GLOBALS['TYPO3_DB']->lastHandlerKey = '_DEFAULT';
+ }
+
+ /**
+ * Cleans up the environment after running a test.
+ */
+ public function tearDown() {
+ // Clear DBAL-generated cache files
+ $GLOBALS['TYPO3_DB']->clearCachedFieldInfo();
+ // Delete temporary files
+ foreach ($this->temporaryFiles as $filename) unlink($filename);
+ // Restore DB connection
+ $GLOBALS['TYPO3_DB'] = $this->db;
+ // Restore list of loaded extensions
+ $GLOBALS['TYPO3_LOADED_EXT'] = $this->loadedExtensions;
+ }
+
+ /**
+ * 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);
+ }
+
+ /**
+ * Creates a fake extension with a given table definition.
+ *
+ * @param string $tableDefinition SQL script to create the extension's tables
+ * @return void
+ */
+ protected function createFakeExtension($tableDefinition) {
+ // Prepare a fake extension configuration
+ $ext_tables = t3lib_div::tempnam('ext_tables');
+ t3lib_div::writeFile($ext_tables, $tableDefinition);
+ $this->temporaryFiles[] = $ext_tables;
+
+ $GLOBALS['TYPO3_LOADED_EXT']['test_dbal'] = array(
+ 'ext_tables.sql' => $ext_tables
+ );
+
+ // Append our test table to the list of existing tables
+ $GLOBALS['TYPO3_DB']->clearCachedFieldInfo();
+ $GLOBALS['TYPO3_DB']->_call('initInternalVariables');
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12515
+ */
+ public function concatCanBeParsedAfterLikeOperator() {
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ '*',
+ 'sys_refindex, tx_dam_file_tracking',
+ 'sys_refindex.tablename = \'tx_dam_file_tracking\''
+ . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)'
+ ));
+ $expected = 'SELECT * FROM sys_refindex, tx_dam_file_tracking WHERE sys_refindex.tablename = \'tx_dam_file_tracking\'';
+ $expected .= ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=10965
+ */
+ public function floatNumberCanBeStoredInDatabase() {
+ $this->createFakeExtension('
+ CREATE TABLE tx_test_dbal (
+ foo double default \'0\',
+ foobar integer default \'0\'
+ );
+ ');
+ $data = array(
+ 'foo' => 99.12,
+ 'foobar' => -120,
+ );
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->INSERTquery('tx_test_dbal', $data));
+ $expected = 'INSERT INTO tx_test_dbal ( foo, foobar ) VALUES ( \'99.12\', \'-120\' )';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=11093
+ */
+ public function positive64BitIntegerIsSupported() {
+ $this->createFakeExtension('
+ CREATE TABLE tx_test_dbal (
+ foo int default \'0\',
+ foobar bigint default \'0\'
+ );
+ ');
+ $data = array(
+ 'foo' => 9223372036854775807,
+ 'foobar' => 9223372036854775807,
+ );
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->INSERTquery('tx_test_dbal', $data));
+ $expected = 'INSERT INTO tx_test_dbal ( foo, foobar ) VALUES ( \'9223372036854775807\', \'9223372036854775807\' )';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=11093
+ */
+ public function negative64BitIntegerIsSupported() {
+ $this->createFakeExtension('
+ CREATE TABLE tx_test_dbal (
+ foo int default \'0\',
+ foobar bigint default \'0\'
+ );
+ ');
+ $data = array(
+ 'foo' => -9223372036854775808,
+ 'foobar' => -9223372036854775808,
+ );
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->INSERTquery('tx_test_dbal', $data));
+ $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
--- /dev/null
+<?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 dbMssqlTest 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;
+
+ $this->assertFalse($GLOBALS['TYPO3_DB']->isConnected());
+
+ // Initialize a fake MS SQL connection
+ FakeDbConnection::connect($GLOBALS['TYPO3_DB'], 'mssql');
+
+ $this->assertTrue($GLOBALS['TYPO3_DB']->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
--- /dev/null
+<?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 Oracle database handling.
+ *
+ * $Id$
+ *
+ * @author Xavier Perseguers <typo3@perseguers.ch>
+ *
+ * @package TYPO3
+ * @subpackage dbal
+ */
+class dbOracleTest 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 Oracle
+ require('fixtures/oci8.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;
+
+ $this->assertFalse($GLOBALS['TYPO3_DB']->isConnected());
+
+ // Initialize a fake Oracle connection
+ FakeDbConnection::connect($GLOBALS['TYPO3_DB'], 'oci8');
+
+ $this->assertTrue($GLOBALS['TYPO3_DB']->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 configurationIsUsingAdodbAndDriverOci8() {
+ $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('oci8') !== FALSE, 'Not using oci8 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');
+ }
+ }
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12897
+ */
+ public function sqlHintIsRemoved() {
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ '/*! SQL_NO_CACHE */ content',
+ 'tx_realurl_urlencodecache',
+ '1=1'
+ ));
+ $expected = 'SELECT "content" FROM "tx_realurl_urlencodecache" WHERE 1 = 1';
+ $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
+ ///////////////////////////////////////
+
+ /**
+ * @test
+ */
+ public function selectQueryIsProperlyQuoted() {
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ 'uid', // select fields
+ 'tt_content', // from table
+ 'pid=1', // where clause
+ 'cruser_id', // group by
+ 'tstamp' // order by
+ ));
+ $expected = 'SELECT "uid" FROM "tt_content" WHERE "pid" = 1 GROUP BY "cruser_id" ORDER BY "tstamp"';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @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() {
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ 'COUNT(DISTINCT pid)', // select fields
+ 'tt_content', // from table
+ '1=1' // where clause
+ ));
+ $expected = 'SELECT COUNT(DISTINCT "pid") FROM "tt_content" WHERE 1 = 1';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=10411
+ * @remark Remapping is not expected here
+ */
+ public function multipleInnerJoinsAreProperlyQuoted() {
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ '*',
+ 'tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid = tt_news_cat_mm.uid_local',
+ '1=1'
+ ));
+ $expected = 'SELECT * FROM "tt_news_cat"';
+ $expected .= ' INNER JOIN "tt_news_cat_mm" ON "tt_news_cat"."uid"="tt_news_cat_mm"."uid_foreign"';
+ $expected .= ' INNER JOIN "tt_news" ON "tt_news"."uid"="tt_news_cat_mm"."uid_local"';
+ $expected .= ' WHERE 1 = 1';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=6198
+ */
+ public function stringsWithinInClauseAreProperlyQuoted() {
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ 'COUNT(DISTINCT tx_dam.uid) AS count',
+ 'tx_dam',
+ 'tx_dam.pid IN (1) AND tx_dam.file_type IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND tx_dam.deleted = 0'
+ ));
+ $expected = 'SELECT COUNT(DISTINCT "tx_dam"."uid") AS "count" FROM "tx_dam"';
+ $expected .= ' WHERE "tx_dam"."pid" IN (1) AND "tx_dam"."file_type" IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND "tx_dam"."deleted" = 0';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12515
+ * @remark Remapping is not expected here
+ */
+ public function concatAfterLikeOperatorIsProperlyQuoted() {
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ '*',
+ 'sys_refindex, tx_dam_file_tracking',
+ 'sys_refindex.tablename = \'tx_dam_file_tracking\''
+ . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)'
+ ));
+ $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
+ $expected .= ' AND (instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."file_path","tx_dam_file_tracking"."file_name"),1,1) > 0)';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12231
+ */
+ public function cachingFrameworkQueryIsProperlyQuoted() {
+ $currentTime = time();
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ 'content',
+ 'cache_hash',
+ 'identifier = ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('abbbabaf2d4b3f9a63e8dde781f1c106', 'cache_hash') .
+ ' AND (crdate + lifetime >= ' . $currentTime . ' OR lifetime = 0)'
+ ));
+ $expected = 'SELECT "content" FROM "cache_hash" WHERE "identifier" = \'abbbabaf2d4b3f9a63e8dde781f1c106\' AND ("crdate"+"lifetime" >= ' . $currentTime . ' OR "lifetime" = 0)';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12231
+ */
+ public function calculatedFieldsAreProperlyQuoted() {
+ $currentTime = time();
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ 'identifier',
+ 'cachingframework_cache_pages',
+ 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
+ ));
+ $expected = 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
+ $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
+ ///////////////////////////////////////
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=10411
+ * @remark Remapping is expected here
+ */
+ public function tablesAndFieldsAreRemappedInMultipleJoins() {
+ $selectFields = '*';
+ $fromTables = 'tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid = tt_news_cat_mm.uid_local';
+ $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 * FROM "ext_tt_news_cat"';
+ $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
+ $expected .= ' INNER JOIN "ext_tt_news" ON "ext_tt_news"."news_uid"="ext_tt_news_cat_mm"."local_uid"';
+ $expected .= ' WHERE 1 = 1';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=6953
+ */
+ public function fieldWithinSqlFunctionIsRemapped() {
+ $selectFields = 'tstamp, script, SUM(exec_time) AS calc_sum, COUNT(*) AS qrycount, MAX(errorFlag) AS error';
+ $fromTables = 'tx_dbal_debuglog';
+ $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 "tstamp", "script", SUM("exec_time") AS "calc_sum", COUNT(*) AS "qrycount", MAX("errorflag") AS "error" FROM "tx_dbal_debuglog" WHERE 1 = 1';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=6953
+ */
+ public function tableAndFieldWithinSqlFunctionIsRemapped() {
+ $selectFields = 'MAX(tt_news_cat.uid) AS biggest_id';
+ $fromTables = 'tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign';
+ $whereClause = 'tt_news_cat_mm.uid_local > 50';
+ $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 MAX("ext_tt_news_cat"."cat_uid") AS "biggest_id" FROM "ext_tt_news_cat"';
+ $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
+ $expected .= ' WHERE "ext_tt_news_cat_mm"."local_uid" > 50';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12515
+ * @remark Remapping is expected here
+ */
+ public function concatAfterLikeOperatorIsRemapped() {
+ $selectFields = '*';
+ $fromTables = 'sys_refindex, tx_dam_file_tracking';
+ $whereClause = 'sys_refindex.tablename = \'tx_dam_file_tracking\''
+ . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)';
+ $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 * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
+ $expected .= ' AND (instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."path","tx_dam_file_tracking"."filename"),1,1) > 0)';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=5708
+ */
+ public function fieldIsMappedOnRightSideOfAJoinCondition() {
+ $selectFields = 'cpg_categories.uid, cpg_categories.name';
+ $fromTables = 'cpg_categories, pages';
+ $whereClause = 'pages.uid = cpg_categories.pid AND pages.deleted = 0 AND 1 = 1';
+ $groupBy = '';
+ $orderBy = 'cpg_categories.pos';
+
+ $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 "cpg_categories"."uid", "cpg_categories"."name" FROM "cpg_categories", "my_pages" WHERE "my_pages"."page_uid" = "cpg_categories"."page_id"';
+ $expected .= ' AND "my_pages"."deleted" = 0 AND 1 = 1 ORDER BY "cpg_categories"."pos"';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=14372
+ */
+ public function fieldFromAliasIsRemapped() {
+ $selectFields = 'news.uid';
+ $fromTables = 'tt_news AS news';
+ $whereClause = 'news.uid = 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 "news"."news_uid" FROM "ext_tt_news" AS "news" WHERE "news"."news_uid" = 1';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * Trick here is that we already have a mapping for both table tt_news and table tt_news_cat
+ * (see tests/fixtures/oci8.config.php) which is used as alias name.
+ *
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=14372
+ */
+ public function fieldFromAliasIsRemappedWithoutBeingTricked() {
+ $selectFields = 'tt_news_cat.uid';
+ $fromTables = 'tt_news AS tt_news_cat';
+ $whereClause = 'tt_news_cat.uid = 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 "tt_news_cat"."news_uid" FROM "ext_tt_news" AS "tt_news_cat" WHERE "tt_news_cat"."news_uid" = 1';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=14372
+ */
+ public function aliasRemappingDoesNotAlterFurtherQueries() {
+ $selectFields = 'foo.uid';
+ $fromTables = 'tt_news AS foo';
+ $whereClause = 'foo.uid = 1';
+ $groupBy = '';
+ $orderBy = '';
+
+ // First call to possibly alter (in memory) the mapping from localconf.php
+ $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
+
+ $selectFields = 'uid';
+ $fromTables = 'foo';
+ $whereClause = 'uid = 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 "uid" FROM "foo" WHERE "uid" = 1';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=14372
+ */
+ public function fieldFromAliasInJoinIsRemapped() {
+ $selectFields = 'cat.uid, cat_mm.uid_local, news.uid';
+ $fromTables = 'tt_news_cat AS cat' .
+ ' INNER JOIN tt_news_cat_mm AS cat_mm ON cat.uid = cat_mm.uid_foreign' .
+ ' INNER JOIN tt_news AS news ON news.uid = cat_mm.uid_local';
+ $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 "cat"."cat_uid", "cat_mm"."local_uid", "news"."news_uid"';
+ $expected .= ' FROM "ext_tt_news_cat" AS "cat"';
+ $expected .= ' INNER JOIN "ext_tt_news_cat_mm" AS "cat_mm" ON "cat"."cat_uid"="cat_mm"."uid_foreign"';
+ $expected .= ' INNER JOIN "ext_tt_news" AS "news" ON "news"."news_uid"="cat_mm"."local_uid"';
+ $expected .= ' WHERE 1 = 1';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=14372
+ */
+ public function aliasRemappingWithInSubqueryDoesNotAffectMainQuery() {
+ $selectFields = 'foo.uid';
+ $fromTables = 'tt_news AS foo INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid';
+ $whereClause = 'tt_news_cat_mm.uid_foreign IN (SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0)';
+ $groupBy = '';
+ $orderBy = 'foo.uid';
+
+ $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 "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
+ $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"';
+ $expected .= ' WHERE "ext_tt_news_cat_mm"."uid_foreign" IN (';
+ $expected .= 'SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0';
+ $expected .= ')';
+ $expected .= ' ORDER BY "foo"."news_uid"';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=14372
+ */
+ public function aliasRemappingWithExistsSubqueryDoesNotAffectMainQuery() {
+ $selectFields = 'foo.uid';
+ $fromTables = 'tt_news AS foo INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid';
+ $whereClause = 'EXISTS (SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0)';
+ $groupBy = '';
+ $orderBy = 'foo.uid';
+
+ $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 "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
+ $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"';
+ $expected .= ' WHERE EXISTS (';
+ $expected .= 'SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0';
+ $expected .= ')';
+ $expected .= ' ORDER BY "foo"."news_uid"';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=14372
+ */
+ public function aliasRemappingSupportsNestedSubqueries() {
+ $selectFields = 'foo.uid';
+ $fromTables = 'tt_news AS foo';
+ $whereClause = 'uid IN (' .
+ 'SELECT foobar.uid_local FROM tt_news_cat_mm AS foobar WHERE uid_foreign IN (' .
+ 'SELECT uid FROM tt_news_cat WHERE deleted = 0' .
+ '))';
+ $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 "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
+ $expected .= ' WHERE "news_uid" IN (';
+ $expected .= 'SELECT "foobar"."local_uid" FROM "ext_tt_news_cat_mm" AS "foobar" WHERE "uid_foreign" IN (';
+ $expected .= 'SELECT "cat_uid" FROM "ext_tt_news_cat" WHERE "deleted" = 0';
+ $expected .= ')';
+ $expected .= ')';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=14372
+ */
+ public function remappingDoesNotMixUpAliasesInSubquery() {
+ $selectFields = 'pages.uid';
+ $fromTables = 'tt_news AS pages INNER JOIN tt_news_cat_mm AS cat_mm ON cat_mm.uid_local = pages.uid';
+ $whereClause = 'pages.pid IN (SELECT uid FROM pages WHERE deleted = 0 AND cat_mm.uid_local != 100)';
+ $groupBy = '';
+ $orderBy = 'pages.uid';
+
+ $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 "pages"."news_uid" FROM "ext_tt_news" AS "pages"';
+ $expected .= ' INNER JOIN "ext_tt_news_cat_mm" AS "cat_mm" ON "cat_mm"."local_uid"="pages"."news_uid"';
+ $expected .= ' WHERE "pages"."pid" IN (';
+ $expected .= 'SELECT "page_uid" FROM "my_pages" WHERE "deleted" = 0 AND "cat_mm"."local_uid" != 100';
+ $expected .= ')';
+ $expected .= ' ORDER BY "pages"."news_uid"';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=14479
+ */
+ public function likeIsRemappedAccordingToFieldType() {
+ $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ '*',
+ 'tt_content',
+ 'tt_content.bodytext LIKE \'foo%\''
+ ));
+ $expected = 'SELECT * FROM "tt_content" WHERE (dbms_lob.instr("tt_content"."bodytext", \'foo\',1,1) > 0)';
+ $this->assertEquals($expected, $select);
+
+ $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ '*',
+ 'fe_users',
+ 'fe_users.usergroup LIKE \'2\''
+ ));
+ $expected = 'SELECT * FROM "fe_users" WHERE (instr("fe_users"."usergroup", \'2\',1,1) > 0)';
+ $this->assertEquals($expected, $select);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=14479
+ */
+ public function instrIsUsedForCEOnPages() {
+ $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ '*',
+ 'tt_content',
+ 'uid IN (62) AND tt_content.deleted=0 AND tt_content.t3ver_state<=0' .
+ ' AND tt_content.hidden=0 AND (tt_content.starttime<=1264487640)' .
+ ' AND (tt_content.endtime=0 OR tt_content.endtime>1264487640)' .
+ ' AND (tt_content.fe_group=\'\' OR tt_content.fe_group IS NULL OR tt_content.fe_group=\'0\'' .
+ ' OR (tt_content.fe_group LIKE \'%,0,%\' OR tt_content.fe_group LIKE \'0,%\' OR tt_content.fe_group LIKE \'%,0\'' .
+ ' OR tt_content.fe_group=\'0\')' .
+ ' OR (tt_content.fe_group LIKE\'%,-1,%\' OR tt_content.fe_group LIKE \'-1,%\' OR tt_content.fe_group LIKE \'%,-1\'' .
+ ' OR tt_content.fe_group=\'-1\'))'
+ ));
+ $expected = 'SELECT * FROM "tt_content"';
+ $expected .= ' WHERE "uid" IN (62) AND "tt_content"."deleted" = 0 AND "tt_content"."t3ver_state" <= 0';
+ $expected .= ' AND "tt_content"."hidden" = 0 AND ("tt_content"."starttime" <= 1264487640)';
+ $expected .= ' AND ("tt_content"."endtime" = 0 OR "tt_content"."endtime" > 1264487640)';
+ $expected .= ' AND ("tt_content"."fe_group" = \'\' OR "tt_content"."fe_group" IS NULL OR "tt_content"."fe_group" = \'0\'';
+ $expected .= ' OR ((instr("tt_content"."fe_group", \',0,\',1,1) > 0)';
+ $expected .= ' OR (instr("tt_content"."fe_group", \'0,\',1,1) > 0)';
+ $expected .= ' OR (instr("tt_content"."fe_group", \',0\',1,1) > 0)';
+ $expected .= ' OR "tt_content"."fe_group" = \'0\')';
+ $expected .= ' OR ((instr("tt_content"."fe_group", \',-1,\',1,1) > 0)';
+ $expected .= ' OR (instr("tt_content"."fe_group", \'-1,\',1,1) > 0)';
+ $expected .= ' OR (instr("tt_content"."fe_group", \',-1\',1,1) > 0)';
+ $expected .= ' OR "tt_content"."fe_group" = \'-1\'))';
+ $this->assertEquals($expected, $select);
+ }
+
+ ///////////////////////////////////////
+ // Tests concerning DB management
+ ///////////////////////////////////////
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12670
+ */
+ public function notNullableColumnsWithDefaultEmptyStringAreCreatedAsNullable() {
+ $parseString = '
+ CREATE TABLE tx_realurl_uniqalias (
+ uid int(11) NOT NULL auto_increment,
+ tstamp int(11) DEFAULT \'0\' NOT NULL,
+ tablename varchar(60) DEFAULT \'\' NOT NULL,
+ field_alias varchar(255) DEFAULT \'\' NOT NULL,
+ field_id varchar(60) DEFAULT \'\' NOT NULL,
+ value_alias varchar(255) DEFAULT \'\' NOT NULL,
+ value_id int(11) DEFAULT \'0\' NOT NULL,
+ lang int(11) DEFAULT \'0\' NOT NULL,
+ expire int(11) DEFAULT \'0\' NOT NULL,
+
+ PRIMARY KEY (uid),
+ KEY tablename (tablename),
+ KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
+ KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
+ );
+ ';
+
+ $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString);
+ $this->assertTrue(is_array($components), 'Not an array: ' . $components);
+
+ $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components);
+ $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
+ $this->assertEquals(4, count($sqlCommands));
+
+ $expected = $this->cleanSql('
+ CREATE TABLE "tx_realurl_uniqalias" (
+ "uid" NUMBER(20) NOT NULL,
+ "tstamp" NUMBER(20) DEFAULT 0,
+ "tablename" VARCHAR(60) DEFAULT \'\',
+ "field_alias" VARCHAR(255) DEFAULT \'\',
+ "field_id" VARCHAR(60) DEFAULT \'\',
+ "value_alias" VARCHAR(255) DEFAULT \'\',
+ "value_id" NUMBER(20) DEFAULT 0,
+ "lang" NUMBER(20) DEFAULT 0,
+ "expire" NUMBER(20) DEFAULT 0,
+ PRIMARY KEY ("uid")
+ )
+ ');
+ $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=11142
+ * @see http://bugs.typo3.org/view.php?id=12670
+ */
+ public function defaultValueIsProperlyQuotedInCreateTable() {
+ $parseString = '
+ CREATE TABLE tx_test (
+ uid int(11) NOT NULL auto_increment,
+ lastname varchar(60) DEFAULT \'unknown\' NOT NULL,
+ firstname varchar(60) DEFAULT \'\' NOT NULL,
+ language varchar(2) NOT NULL,
+ tstamp int(11) DEFAULT \'0\' NOT NULL,
+
+ PRIMARY KEY (uid),
+ KEY name (name)
+ );
+ ';
+
+ $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString);
+ $this->assertTrue(is_array($components), 'Not an array: ' . $components);
+
+ $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components);
+ $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
+ $this->assertEquals(2, count($sqlCommands));
+
+ $expected = $this->cleanSql('
+ CREATE TABLE "tx_test" (
+ "uid" NUMBER(20) NOT NULL,
+ "lastname" VARCHAR(60) DEFAULT \'unknown\',
+ "firstname" VARCHAR(60) DEFAULT \'\',
+ "language" VARCHAR(2) DEFAULT \'\',
+ "tstamp" NUMBER(20) DEFAULT 0,
+ PRIMARY KEY ("uid")
+ )
+ ');
+ $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
+ }
+
+ ///////////////////////////////////////
+ // Tests concerning subqueries
+ ///////////////////////////////////////
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12758
+ */
+ public function inWhereClauseWithSubqueryIsProperlyQuoted() {
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ '*',
+ 'tx_crawler_queue',
+ 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)'
+ ));
+ $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "process_id" FROM "tx_crawler_process" WHERE "active" = 0 AND "deleted" = 0)';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12758
+ */
+ public function subqueryIsRemappedForInWhereClause() {
+ $selectFields = '*';
+ $fromTables = 'tx_crawler_queue';
+ $whereClause = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
+ $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 * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "ps_id" FROM "tx_crawler_ps" WHERE "is_active" = 0 AND "deleted" = 0)';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12800
+ */
+ public function cachingFrameworkQueryIsSupported() {
+ $currentTime = time();
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->DELETEquery(
+ 'cachingframework_cache_hash_tags',
+ 'identifier IN (' .
+ $GLOBALS['TYPO3_DB']->SELECTsubquery(
+ 'identifier',
+ 'cachingframework_cache_pages',
+ 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
+ ) .
+ ')'
+ ));
+ $expected = 'DELETE FROM "cachingframework_cache_hash_tags" WHERE "identifier" IN (';
+ $expected .= 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
+ $expected .= ')';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12800
+ */
+ public function cachingFrameworkQueryIsRemapped() {
+ $currentTime = time();
+ $table = 'cachingframework_cache_hash_tags';
+ $where = 'identifier IN (' .
+ $GLOBALS['TYPO3_DB']->SELECTsubquery(
+ 'identifier',
+ 'cachingframework_cache_pages',
+ 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
+ ) .
+ ')';
+
+ // Perform remapping (as in method exec_DELETEquery)
+ if ($tableArray = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table)) {
+ // Where clause:
+ $whereParts = $GLOBALS['TYPO3_DB']->SQLparser->parseWhereClause($where);
+ $GLOBALS['TYPO3_DB']->_callRef('map_sqlParts', $whereParts, $tableArray[0]['table']);
+ $where = $GLOBALS['TYPO3_DB']->SQLparser->compileWhereClause($whereParts, FALSE);
+
+ // Table name:
+ if ($GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName']) {
+ $table = $GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName'];
+ }
+ }
+
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->DELETEquery($table, $where));
+ $expected = 'DELETE FROM "cf_cache_hash_tags" WHERE "identifier" IN (';
+ $expected .= 'SELECT "identifier" FROM "cf_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
+ $expected .= ')';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12758
+ */
+ public function existsWhereClauseIsProperlyQuoted() {
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ '*',
+ 'tx_crawler_process',
+ 'active = 0 AND NOT EXISTS (' .
+ $GLOBALS['TYPO3_DB']->SELECTsubquery(
+ '*',
+ 'tx_crawler_queue',
+ 'tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)'
+ ) .
+ ')'
+ ));
+ $expected = 'SELECT * FROM "tx_crawler_process" WHERE "active" = 0 AND NOT EXISTS (';
+ $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_process"."process_id" AND "tx_crawler_queue"."exec_time" = 0';
+ $expected .= ')';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12758
+ */
+ public function subqueryIsRemappedForExistsWhereClause() {
+ $selectFields = '*';
+ $fromTables = 'tx_crawler_process';
+ $whereClause = 'active = 0 AND NOT EXISTS (' .
+ $GLOBALS['TYPO3_DB']->SELECTsubquery(
+ '*',
+ 'tx_crawler_queue',
+ 'tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0'
+ ) .
+ ')';
+ $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 * FROM "tx_crawler_ps" WHERE "is_active" = 0 AND NOT EXISTS (';
+ $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_ps"."ps_id" AND "tx_crawler_queue"."exec_time" = 0';
+ $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
--- /dev/null
+<?php
+/***************************************************************
+* Copyright notice
+*
+* (c) 2010 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 PostgreSQL database handling.
+ *
+ * $Id$
+ *
+ * @author Xavier Perseguers <typo3@perseguers.ch>
+ *
+ * @package TYPO3
+ * @subpackage dbal
+ */
+class dbPostgresqlTest 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 PostgreSQL
+ require('fixtures/postgresql.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;
+
+ $this->assertFalse($GLOBALS['TYPO3_DB']->isConnected());
+
+ // Initialize a fake PostgreSQL connection (using 'postgres7' as 'postgres' is remapped to it in AdoDB)
+ FakeDbConnection::connect($GLOBALS['TYPO3_DB'], 'postgres7');
+
+ $this->assertTrue($GLOBALS['TYPO3_DB']->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 configurationIsUsingAdodbAndDriverPostgres() {
+ $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('postgres') !== FALSE, 'Not using postgres 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');
+ }
+ }
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=2367
+ */
+ public function limitIsProperlyRemapped() {
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ '*',
+ 'be_users',
+ '1=1',
+ '',
+ '',
+ '20'
+ ));
+ $expected = 'SELECT * FROM "be_users" WHERE 1 = 1 LIMIT 20';
+ $this->assertEquals($expected, $query);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=2367
+ */
+ public function limitWithSkipIsProperlyRemapped() {
+ $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+ '*',
+ 'be_users',
+ '1=1',
+ '',
+ '',
+ '20,40'
+ ));
+ $expected = 'SELECT * FROM "be_users" WHERE 1 = 1 LIMIT 40 OFFSET 20';
+ $this->assertEquals($expected, $query);
+ }
+}
+?>
\ No newline at end of file
+++ /dev/null
-<?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');
-
-/**
- * Testcase for class ux_t3lib_db.
- *
- * $Id$
- *
- * @author Xavier Perseguers <typo3@perseguers.ch>
- *
- * @package TYPO3
- * @subpackage dbal
- */
-class db_general_testcase extends BaseTestCase {
-
- /**
- * @var t3lib_db
- */
- protected $db;
-
- /**
- * @var array
- */
- protected $loadedExtensions;
-
- /**
- * @var array
- */
- protected $temporaryFiles;
-
- /**
- * Prepares the environment before running a test.
- */
- public function setUp() {
- // Backup list of loaded extensions
- $this->loadedExtensions = $GLOBALS['TYPO3_LOADED_EXT'];
- // Backup database connection
- $this->db = $GLOBALS['TYPO3_DB'];
- $this->temporaryFiles = array();
-
- $className = self::buildAccessibleProxy('ux_t3lib_db');
- $GLOBALS['TYPO3_DB'] = new $className;
- $GLOBALS['TYPO3_DB']->lastHandlerKey = '_DEFAULT';
- }
-
- /**
- * Cleans up the environment after running a test.
- */
- public function tearDown() {
- // Clear DBAL-generated cache files
- $GLOBALS['TYPO3_DB']->clearCachedFieldInfo();
- // Delete temporary files
- foreach ($this->temporaryFiles as $filename) unlink($filename);
- // Restore DB connection
- $GLOBALS['TYPO3_DB'] = $this->db;
- // Restore list of loaded extensions
- $GLOBALS['TYPO3_LOADED_EXT'] = $this->loadedExtensions;
- }
-
- /**
- * 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);
- }
-
- /**
- * Creates a fake extension with a given table definition.
- *
- * @param string $tableDefinition SQL script to create the extension's tables
- * @return void
- */
- protected function createFakeExtension($tableDefinition) {
- // Prepare a fake extension configuration
- $ext_tables = t3lib_div::tempnam('ext_tables');
- t3lib_div::writeFile($ext_tables, $tableDefinition);
- $this->temporaryFiles[] = $ext_tables;
-
- $GLOBALS['TYPO3_LOADED_EXT']['test_dbal'] = array(
- 'ext_tables.sql' => $ext_tables
- );
-
- // Append our test table to the list of existing tables
- $GLOBALS['TYPO3_DB']->clearCachedFieldInfo();
- $GLOBALS['TYPO3_DB']->_call('initInternalVariables');
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12515
- */
- public function concatCanBeParsedAfterLikeOperator() {
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- '*',
- 'sys_refindex, tx_dam_file_tracking',
- 'sys_refindex.tablename = \'tx_dam_file_tracking\''
- . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)'
- ));
- $expected = 'SELECT * FROM sys_refindex, tx_dam_file_tracking WHERE sys_refindex.tablename = \'tx_dam_file_tracking\'';
- $expected .= ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=10965
- */
- public function floatNumberCanBeStoredInDatabase() {
- $this->createFakeExtension('
- CREATE TABLE tx_test_dbal (
- foo double default \'0\',
- foobar integer default \'0\'
- );
- ');
- $data = array(
- 'foo' => 99.12,
- 'foobar' => -120,
- );
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->INSERTquery('tx_test_dbal', $data));
- $expected = 'INSERT INTO tx_test_dbal ( foo, foobar ) VALUES ( \'99.12\', \'-120\' )';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=11093
- */
- public function positive64BitIntegerIsSupported() {
- $this->createFakeExtension('
- CREATE TABLE tx_test_dbal (
- foo int default \'0\',
- foobar bigint default \'0\'
- );
- ');
- $data = array(
- 'foo' => 9223372036854775807,
- 'foobar' => 9223372036854775807,
- );
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->INSERTquery('tx_test_dbal', $data));
- $expected = 'INSERT INTO tx_test_dbal ( foo, foobar ) VALUES ( \'9223372036854775807\', \'9223372036854775807\' )';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=11093
- */
- public function negative64BitIntegerIsSupported() {
- $this->createFakeExtension('
- CREATE TABLE tx_test_dbal (
- foo int default \'0\',
- foobar bigint default \'0\'
- );
- ');
- $data = array(
- 'foo' => -9223372036854775808,
- 'foobar' => -9223372036854775808,
- );
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->INSERTquery('tx_test_dbal', $data));
- $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
+++ /dev/null
-<?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;
-
- $this->assertFalse($GLOBALS['TYPO3_DB']->isConnected());
-
- // Initialize a fake MS SQL connection
- FakeDbConnection::connect($GLOBALS['TYPO3_DB'], 'mssql');
-
- $this->assertTrue($GLOBALS['TYPO3_DB']->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
+++ /dev/null
-<?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 Oracle database handling.
- *
- * $Id$
- *
- * @author Xavier Perseguers <typo3@perseguers.ch>
- *
- * @package TYPO3
- * @subpackage dbal
- */
-class db_oracle_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 Oracle
- require('fixtures/oci8.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;
-
- $this->assertFalse($GLOBALS['TYPO3_DB']->isConnected());
-
- // Initialize a fake Oracle connection
- FakeDbConnection::connect($GLOBALS['TYPO3_DB'], 'oci8');
-
- $this->assertTrue($GLOBALS['TYPO3_DB']->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 configurationIsUsingAdodbAndDriverOci8() {
- $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('oci8') !== FALSE, 'Not using oci8 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');
- }
- }
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12897
- */
- public function sqlHintIsRemoved() {
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- '/*! SQL_NO_CACHE */ content',
- 'tx_realurl_urlencodecache',
- '1=1'
- ));
- $expected = 'SELECT "content" FROM "tx_realurl_urlencodecache" WHERE 1 = 1';
- $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
- ///////////////////////////////////////
-
- /**
- * @test
- */
- public function selectQueryIsProperlyQuoted() {
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- 'uid', // select fields
- 'tt_content', // from table
- 'pid=1', // where clause
- 'cruser_id', // group by
- 'tstamp' // order by
- ));
- $expected = 'SELECT "uid" FROM "tt_content" WHERE "pid" = 1 GROUP BY "cruser_id" ORDER BY "tstamp"';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @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() {
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- 'COUNT(DISTINCT pid)', // select fields
- 'tt_content', // from table
- '1=1' // where clause
- ));
- $expected = 'SELECT COUNT(DISTINCT "pid") FROM "tt_content" WHERE 1 = 1';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=10411
- * @remark Remapping is not expected here
- */
- public function multipleInnerJoinsAreProperlyQuoted() {
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- '*',
- 'tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid = tt_news_cat_mm.uid_local',
- '1=1'
- ));
- $expected = 'SELECT * FROM "tt_news_cat"';
- $expected .= ' INNER JOIN "tt_news_cat_mm" ON "tt_news_cat"."uid"="tt_news_cat_mm"."uid_foreign"';
- $expected .= ' INNER JOIN "tt_news" ON "tt_news"."uid"="tt_news_cat_mm"."uid_local"';
- $expected .= ' WHERE 1 = 1';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=6198
- */
- public function stringsWithinInClauseAreProperlyQuoted() {
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- 'COUNT(DISTINCT tx_dam.uid) AS count',
- 'tx_dam',
- 'tx_dam.pid IN (1) AND tx_dam.file_type IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND tx_dam.deleted = 0'
- ));
- $expected = 'SELECT COUNT(DISTINCT "tx_dam"."uid") AS "count" FROM "tx_dam"';
- $expected .= ' WHERE "tx_dam"."pid" IN (1) AND "tx_dam"."file_type" IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND "tx_dam"."deleted" = 0';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12515
- * @remark Remapping is not expected here
- */
- public function concatAfterLikeOperatorIsProperlyQuoted() {
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- '*',
- 'sys_refindex, tx_dam_file_tracking',
- 'sys_refindex.tablename = \'tx_dam_file_tracking\''
- . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)'
- ));
- $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
- $expected .= ' AND (instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."file_path","tx_dam_file_tracking"."file_name"),1,1) > 0)';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12231
- */
- public function cachingFrameworkQueryIsProperlyQuoted() {
- $currentTime = time();
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- 'content',
- 'cache_hash',
- 'identifier = ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('abbbabaf2d4b3f9a63e8dde781f1c106', 'cache_hash') .
- ' AND (crdate + lifetime >= ' . $currentTime . ' OR lifetime = 0)'
- ));
- $expected = 'SELECT "content" FROM "cache_hash" WHERE "identifier" = \'abbbabaf2d4b3f9a63e8dde781f1c106\' AND ("crdate"+"lifetime" >= ' . $currentTime . ' OR "lifetime" = 0)';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12231
- */
- public function calculatedFieldsAreProperlyQuoted() {
- $currentTime = time();
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- 'identifier',
- 'cachingframework_cache_pages',
- 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
- ));
- $expected = 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
- $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
- ///////////////////////////////////////
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=10411
- * @remark Remapping is expected here
- */
- public function tablesAndFieldsAreRemappedInMultipleJoins() {
- $selectFields = '*';
- $fromTables = 'tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid = tt_news_cat_mm.uid_local';
- $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 * FROM "ext_tt_news_cat"';
- $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
- $expected .= ' INNER JOIN "ext_tt_news" ON "ext_tt_news"."news_uid"="ext_tt_news_cat_mm"."local_uid"';
- $expected .= ' WHERE 1 = 1';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=6953
- */
- public function fieldWithinSqlFunctionIsRemapped() {
- $selectFields = 'tstamp, script, SUM(exec_time) AS calc_sum, COUNT(*) AS qrycount, MAX(errorFlag) AS error';
- $fromTables = 'tx_dbal_debuglog';
- $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 "tstamp", "script", SUM("exec_time") AS "calc_sum", COUNT(*) AS "qrycount", MAX("errorflag") AS "error" FROM "tx_dbal_debuglog" WHERE 1 = 1';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=6953
- */
- public function tableAndFieldWithinSqlFunctionIsRemapped() {
- $selectFields = 'MAX(tt_news_cat.uid) AS biggest_id';
- $fromTables = 'tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign';
- $whereClause = 'tt_news_cat_mm.uid_local > 50';
- $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 MAX("ext_tt_news_cat"."cat_uid") AS "biggest_id" FROM "ext_tt_news_cat"';
- $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
- $expected .= ' WHERE "ext_tt_news_cat_mm"."local_uid" > 50';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12515
- * @remark Remapping is expected here
- */
- public function concatAfterLikeOperatorIsRemapped() {
- $selectFields = '*';
- $fromTables = 'sys_refindex, tx_dam_file_tracking';
- $whereClause = 'sys_refindex.tablename = \'tx_dam_file_tracking\''
- . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)';
- $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 * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
- $expected .= ' AND (instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."path","tx_dam_file_tracking"."filename"),1,1) > 0)';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=5708
- */
- public function fieldIsMappedOnRightSideOfAJoinCondition() {
- $selectFields = 'cpg_categories.uid, cpg_categories.name';
- $fromTables = 'cpg_categories, pages';
- $whereClause = 'pages.uid = cpg_categories.pid AND pages.deleted = 0 AND 1 = 1';
- $groupBy = '';
- $orderBy = 'cpg_categories.pos';
-
- $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 "cpg_categories"."uid", "cpg_categories"."name" FROM "cpg_categories", "my_pages" WHERE "my_pages"."page_uid" = "cpg_categories"."page_id"';
- $expected .= ' AND "my_pages"."deleted" = 0 AND 1 = 1 ORDER BY "cpg_categories"."pos"';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=14372
- */
- public function fieldFromAliasIsRemapped() {
- $selectFields = 'news.uid';
- $fromTables = 'tt_news AS news';
- $whereClause = 'news.uid = 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 "news"."news_uid" FROM "ext_tt_news" AS "news" WHERE "news"."news_uid" = 1';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * Trick here is that we already have a mapping for both table tt_news and table tt_news_cat
- * (see tests/fixtures/oci8.config.php) which is used as alias name.
- *
- * @test
- * @see http://bugs.typo3.org/view.php?id=14372
- */
- public function fieldFromAliasIsRemappedWithoutBeingTricked() {
- $selectFields = 'tt_news_cat.uid';
- $fromTables = 'tt_news AS tt_news_cat';
- $whereClause = 'tt_news_cat.uid = 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 "tt_news_cat"."news_uid" FROM "ext_tt_news" AS "tt_news_cat" WHERE "tt_news_cat"."news_uid" = 1';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=14372
- */
- public function aliasRemappingDoesNotAlterFurtherQueries() {
- $selectFields = 'foo.uid';
- $fromTables = 'tt_news AS foo';
- $whereClause = 'foo.uid = 1';
- $groupBy = '';
- $orderBy = '';
-
- // First call to possibly alter (in memory) the mapping from localconf.php
- $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
-
- $selectFields = 'uid';
- $fromTables = 'foo';
- $whereClause = 'uid = 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 "uid" FROM "foo" WHERE "uid" = 1';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=14372
- */
- public function fieldFromAliasInJoinIsRemapped() {
- $selectFields = 'cat.uid, cat_mm.uid_local, news.uid';
- $fromTables = 'tt_news_cat AS cat' .
- ' INNER JOIN tt_news_cat_mm AS cat_mm ON cat.uid = cat_mm.uid_foreign' .
- ' INNER JOIN tt_news AS news ON news.uid = cat_mm.uid_local';
- $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 "cat"."cat_uid", "cat_mm"."local_uid", "news"."news_uid"';
- $expected .= ' FROM "ext_tt_news_cat" AS "cat"';
- $expected .= ' INNER JOIN "ext_tt_news_cat_mm" AS "cat_mm" ON "cat"."cat_uid"="cat_mm"."uid_foreign"';
- $expected .= ' INNER JOIN "ext_tt_news" AS "news" ON "news"."news_uid"="cat_mm"."local_uid"';
- $expected .= ' WHERE 1 = 1';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=14372
- */
- public function aliasRemappingWithInSubqueryDoesNotAffectMainQuery() {
- $selectFields = 'foo.uid';
- $fromTables = 'tt_news AS foo INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid';
- $whereClause = 'tt_news_cat_mm.uid_foreign IN (SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0)';
- $groupBy = '';
- $orderBy = 'foo.uid';
-
- $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 "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
- $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"';
- $expected .= ' WHERE "ext_tt_news_cat_mm"."uid_foreign" IN (';
- $expected .= 'SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0';
- $expected .= ')';
- $expected .= ' ORDER BY "foo"."news_uid"';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=14372
- */
- public function aliasRemappingWithExistsSubqueryDoesNotAffectMainQuery() {
- $selectFields = 'foo.uid';
- $fromTables = 'tt_news AS foo INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid';
- $whereClause = 'EXISTS (SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0)';
- $groupBy = '';
- $orderBy = 'foo.uid';
-
- $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 "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
- $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat_mm"."local_uid"="foo"."news_uid"';
- $expected .= ' WHERE EXISTS (';
- $expected .= 'SELECT "foo"."cat_uid" FROM "ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0';
- $expected .= ')';
- $expected .= ' ORDER BY "foo"."news_uid"';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=14372
- */
- public function aliasRemappingSupportsNestedSubqueries() {
- $selectFields = 'foo.uid';
- $fromTables = 'tt_news AS foo';
- $whereClause = 'uid IN (' .
- 'SELECT foobar.uid_local FROM tt_news_cat_mm AS foobar WHERE uid_foreign IN (' .
- 'SELECT uid FROM tt_news_cat WHERE deleted = 0' .
- '))';
- $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 "foo"."news_uid" FROM "ext_tt_news" AS "foo"';
- $expected .= ' WHERE "news_uid" IN (';
- $expected .= 'SELECT "foobar"."local_uid" FROM "ext_tt_news_cat_mm" AS "foobar" WHERE "uid_foreign" IN (';
- $expected .= 'SELECT "cat_uid" FROM "ext_tt_news_cat" WHERE "deleted" = 0';
- $expected .= ')';
- $expected .= ')';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=14372
- */
- public function remappingDoesNotMixUpAliasesInSubquery() {
- $selectFields = 'pages.uid';
- $fromTables = 'tt_news AS pages INNER JOIN tt_news_cat_mm AS cat_mm ON cat_mm.uid_local = pages.uid';
- $whereClause = 'pages.pid IN (SELECT uid FROM pages WHERE deleted = 0 AND cat_mm.uid_local != 100)';
- $groupBy = '';
- $orderBy = 'pages.uid';
-
- $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 "pages"."news_uid" FROM "ext_tt_news" AS "pages"';
- $expected .= ' INNER JOIN "ext_tt_news_cat_mm" AS "cat_mm" ON "cat_mm"."local_uid"="pages"."news_uid"';
- $expected .= ' WHERE "pages"."pid" IN (';
- $expected .= 'SELECT "page_uid" FROM "my_pages" WHERE "deleted" = 0 AND "cat_mm"."local_uid" != 100';
- $expected .= ')';
- $expected .= ' ORDER BY "pages"."news_uid"';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=14479
- */
- public function likeIsRemappedAccordingToFieldType() {
- $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- '*',
- 'tt_content',
- 'tt_content.bodytext LIKE \'foo%\''
- ));
- $expected = 'SELECT * FROM "tt_content" WHERE (dbms_lob.instr("tt_content"."bodytext", \'foo\',1,1) > 0)';
- $this->assertEquals($expected, $select);
-
- $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- '*',
- 'fe_users',
- 'fe_users.usergroup LIKE \'2\''
- ));
- $expected = 'SELECT * FROM "fe_users" WHERE (instr("fe_users"."usergroup", \'2\',1,1) > 0)';
- $this->assertEquals($expected, $select);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=14479
- */
- public function instrIsUsedForCEOnPages() {
- $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- '*',
- 'tt_content',
- 'uid IN (62) AND tt_content.deleted=0 AND tt_content.t3ver_state<=0' .
- ' AND tt_content.hidden=0 AND (tt_content.starttime<=1264487640)' .
- ' AND (tt_content.endtime=0 OR tt_content.endtime>1264487640)' .
- ' AND (tt_content.fe_group=\'\' OR tt_content.fe_group IS NULL OR tt_content.fe_group=\'0\'' .
- ' OR (tt_content.fe_group LIKE \'%,0,%\' OR tt_content.fe_group LIKE \'0,%\' OR tt_content.fe_group LIKE \'%,0\'' .
- ' OR tt_content.fe_group=\'0\')' .
- ' OR (tt_content.fe_group LIKE\'%,-1,%\' OR tt_content.fe_group LIKE \'-1,%\' OR tt_content.fe_group LIKE \'%,-1\'' .
- ' OR tt_content.fe_group=\'-1\'))'
- ));
- $expected = 'SELECT * FROM "tt_content"';
- $expected .= ' WHERE "uid" IN (62) AND "tt_content"."deleted" = 0 AND "tt_content"."t3ver_state" <= 0';
- $expected .= ' AND "tt_content"."hidden" = 0 AND ("tt_content"."starttime" <= 1264487640)';
- $expected .= ' AND ("tt_content"."endtime" = 0 OR "tt_content"."endtime" > 1264487640)';
- $expected .= ' AND ("tt_content"."fe_group" = \'\' OR "tt_content"."fe_group" IS NULL OR "tt_content"."fe_group" = \'0\'';
- $expected .= ' OR ((instr("tt_content"."fe_group", \',0,\',1,1) > 0)';
- $expected .= ' OR (instr("tt_content"."fe_group", \'0,\',1,1) > 0)';
- $expected .= ' OR (instr("tt_content"."fe_group", \',0\',1,1) > 0)';
- $expected .= ' OR "tt_content"."fe_group" = \'0\')';
- $expected .= ' OR ((instr("tt_content"."fe_group", \',-1,\',1,1) > 0)';
- $expected .= ' OR (instr("tt_content"."fe_group", \'-1,\',1,1) > 0)';
- $expected .= ' OR (instr("tt_content"."fe_group", \',-1\',1,1) > 0)';
- $expected .= ' OR "tt_content"."fe_group" = \'-1\'))';
- $this->assertEquals($expected, $select);
- }
-
- ///////////////////////////////////////
- // Tests concerning DB management
- ///////////////////////////////////////
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12670
- */
- public function notNullableColumnsWithDefaultEmptyStringAreCreatedAsNullable() {
- $parseString = '
- CREATE TABLE tx_realurl_uniqalias (
- uid int(11) NOT NULL auto_increment,
- tstamp int(11) DEFAULT \'0\' NOT NULL,
- tablename varchar(60) DEFAULT \'\' NOT NULL,
- field_alias varchar(255) DEFAULT \'\' NOT NULL,
- field_id varchar(60) DEFAULT \'\' NOT NULL,
- value_alias varchar(255) DEFAULT \'\' NOT NULL,
- value_id int(11) DEFAULT \'0\' NOT NULL,
- lang int(11) DEFAULT \'0\' NOT NULL,
- expire int(11) DEFAULT \'0\' NOT NULL,
-
- PRIMARY KEY (uid),
- KEY tablename (tablename),
- KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
- KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
- );
- ';
-
- $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString);
- $this->assertTrue(is_array($components), 'Not an array: ' . $components);
-
- $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components);
- $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
- $this->assertEquals(4, count($sqlCommands));
-
- $expected = $this->cleanSql('
- CREATE TABLE "tx_realurl_uniqalias" (
- "uid" NUMBER(20) NOT NULL,
- "tstamp" NUMBER(20) DEFAULT 0,
- "tablename" VARCHAR(60) DEFAULT \'\',
- "field_alias" VARCHAR(255) DEFAULT \'\',
- "field_id" VARCHAR(60) DEFAULT \'\',
- "value_alias" VARCHAR(255) DEFAULT \'\',
- "value_id" NUMBER(20) DEFAULT 0,
- "lang" NUMBER(20) DEFAULT 0,
- "expire" NUMBER(20) DEFAULT 0,
- PRIMARY KEY ("uid")
- )
- ');
- $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=11142
- * @see http://bugs.typo3.org/view.php?id=12670
- */
- public function defaultValueIsProperlyQuotedInCreateTable() {
- $parseString = '
- CREATE TABLE tx_test (
- uid int(11) NOT NULL auto_increment,
- lastname varchar(60) DEFAULT \'unknown\' NOT NULL,
- firstname varchar(60) DEFAULT \'\' NOT NULL,
- language varchar(2) NOT NULL,
- tstamp int(11) DEFAULT \'0\' NOT NULL,
-
- PRIMARY KEY (uid),
- KEY name (name)
- );
- ';
-
- $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString);
- $this->assertTrue(is_array($components), 'Not an array: ' . $components);
-
- $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components);
- $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
- $this->assertEquals(2, count($sqlCommands));
-
- $expected = $this->cleanSql('
- CREATE TABLE "tx_test" (
- "uid" NUMBER(20) NOT NULL,
- "lastname" VARCHAR(60) DEFAULT \'unknown\',
- "firstname" VARCHAR(60) DEFAULT \'\',
- "language" VARCHAR(2) DEFAULT \'\',
- "tstamp" NUMBER(20) DEFAULT 0,
- PRIMARY KEY ("uid")
- )
- ');
- $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
- }
-
- ///////////////////////////////////////
- // Tests concerning subqueries
- ///////////////////////////////////////
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12758
- */
- public function inWhereClauseWithSubqueryIsProperlyQuoted() {
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- '*',
- 'tx_crawler_queue',
- 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)'
- ));
- $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "process_id" FROM "tx_crawler_process" WHERE "active" = 0 AND "deleted" = 0)';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12758
- */
- public function subqueryIsRemappedForInWhereClause() {
- $selectFields = '*';
- $fromTables = 'tx_crawler_queue';
- $whereClause = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
- $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 * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "ps_id" FROM "tx_crawler_ps" WHERE "is_active" = 0 AND "deleted" = 0)';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12800
- */
- public function cachingFrameworkQueryIsSupported() {
- $currentTime = time();
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->DELETEquery(
- 'cachingframework_cache_hash_tags',
- 'identifier IN (' .
- $GLOBALS['TYPO3_DB']->SELECTsubquery(
- 'identifier',
- 'cachingframework_cache_pages',
- 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
- ) .
- ')'
- ));
- $expected = 'DELETE FROM "cachingframework_cache_hash_tags" WHERE "identifier" IN (';
- $expected .= 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
- $expected .= ')';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12800
- */
- public function cachingFrameworkQueryIsRemapped() {
- $currentTime = time();
- $table = 'cachingframework_cache_hash_tags';
- $where = 'identifier IN (' .
- $GLOBALS['TYPO3_DB']->SELECTsubquery(
- 'identifier',
- 'cachingframework_cache_pages',
- 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
- ) .
- ')';
-
- // Perform remapping (as in method exec_DELETEquery)
- if ($tableArray = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table)) {
- // Where clause:
- $whereParts = $GLOBALS['TYPO3_DB']->SQLparser->parseWhereClause($where);
- $GLOBALS['TYPO3_DB']->_callRef('map_sqlParts', $whereParts, $tableArray[0]['table']);
- $where = $GLOBALS['TYPO3_DB']->SQLparser->compileWhereClause($whereParts, FALSE);
-
- // Table name:
- if ($GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName']) {
- $table = $GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName'];
- }
- }
-
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->DELETEquery($table, $where));
- $expected = 'DELETE FROM "cf_cache_hash_tags" WHERE "identifier" IN (';
- $expected .= 'SELECT "identifier" FROM "cf_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
- $expected .= ')';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12758
- */
- public function existsWhereClauseIsProperlyQuoted() {
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- '*',
- 'tx_crawler_process',
- 'active = 0 AND NOT EXISTS (' .
- $GLOBALS['TYPO3_DB']->SELECTsubquery(
- '*',
- 'tx_crawler_queue',
- 'tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)'
- ) .
- ')'
- ));
- $expected = 'SELECT * FROM "tx_crawler_process" WHERE "active" = 0 AND NOT EXISTS (';
- $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_process"."process_id" AND "tx_crawler_queue"."exec_time" = 0';
- $expected .= ')';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12758
- */
- public function subqueryIsRemappedForExistsWhereClause() {
- $selectFields = '*';
- $fromTables = 'tx_crawler_process';
- $whereClause = 'active = 0 AND NOT EXISTS (' .
- $GLOBALS['TYPO3_DB']->SELECTsubquery(
- '*',
- 'tx_crawler_queue',
- 'tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0'
- ) .
- ')';
- $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 * FROM "tx_crawler_ps" WHERE "is_active" = 0 AND NOT EXISTS (';
- $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_ps"."ps_id" AND "tx_crawler_queue"."exec_time" = 0';
- $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
+++ /dev/null
-<?php
-/***************************************************************
-* Copyright notice
-*
-* (c) 2010 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 PostgreSQL database handling.
- *
- * $Id$
- *
- * @author Xavier Perseguers <typo3@perseguers.ch>
- *
- * @package TYPO3
- * @subpackage dbal
- */
-class db_postgresql_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 PostgreSQL
- require('fixtures/postgresql.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;
-
- $this->assertFalse($GLOBALS['TYPO3_DB']->isConnected());
-
- // Initialize a fake PostgreSQL connection (using 'postgres7' as 'postgres' is remapped to it in AdoDB)
- FakeDbConnection::connect($GLOBALS['TYPO3_DB'], 'postgres7');
-
- $this->assertTrue($GLOBALS['TYPO3_DB']->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 configurationIsUsingAdodbAndDriverPostgres() {
- $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('postgres') !== FALSE, 'Not using postgres 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');
- }
- }
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=2367
- */
- public function limitIsProperlyRemapped() {
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- '*',
- 'be_users',
- '1=1',
- '',
- '',
- '20'
- ));
- $expected = 'SELECT * FROM "be_users" WHERE 1 = 1 LIMIT 20';
- $this->assertEquals($expected, $query);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=2367
- */
- public function limitWithSkipIsProperlyRemapped() {
- $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
- '*',
- 'be_users',
- '1=1',
- '',
- '',
- '20,40'
- ));
- $expected = 'SELECT * FROM "be_users" WHERE 1 = 1 LIMIT 40 OFFSET 20';
- $this->assertEquals($expected, $query);
- }
-}
-?>
\ No newline at end of file
--- /dev/null
+<?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');
+
+/**
+ * Testcase for class ux_t3lib_sqlparser
+ *
+ * $Id$
+ *
+ * @author Xavier Perseguers <typo3@perseguers.ch>
+ *
+ * @package TYPO3
+ * @subpackage dbal
+ */
+class sqlParserGeneralTest extends BaseTestCase {
+
+ /**
+ * @var ux_t3lib_sqlparser (extended to make protected methods public)
+ */
+ protected $fixture;
+
+ /**
+ * Prepares the environment before running a test.
+ */
+ public function setUp() {
+ $className = self::buildAccessibleProxy('ux_t3lib_sqlparser');
+ $this->fixture = new $className;
+ }
+
+ /**
+ * Cleans up the environment after running a test.
+ */
+ public function tearDown() {
+ unset($this->fixture);
+ }
+
+ /**
+ * 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 canExtractPartsOfAQuery() {
+ $parseString = "SELECT *\nFROM pages WHERE pid IN (1,2,3,4)";
+ $regex = '^SELECT[[:space:]]+(.*)[[:space:]]+';
+ $trimAll = TRUE;
+ $fields = $this->fixture->_callRef('nextPart', $parseString, $regex, $trimAll);
+
+ $this->assertEquals(
+ '*',
+ $fields
+ );
+ $this->assertEquals(
+ 'FROM pages WHERE pid IN (1,2,3,4)',
+ $parseString
+ );
+
+ $regex = '^FROM ([^)]+) WHERE';
+ $table = $this->fixture->_callRef('nextPart', $parseString, $regex);
+
+ $this->assertEquals(
+ 'pages',
+ $table
+ );
+ $this->assertEquals(
+ 'pages WHERE pid IN (1,2,3,4)',
+ $parseString
+ );
+ }
+
+ /**
+ * @test
+ */
+ public function canGetIntegerValue() {
+ $parseString = '1024';
+ $value = $this->fixture->_callRef('getValue', $parseString);
+ $expected = array(1024);
+
+ $this->assertEquals($expected, $value);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=13104
+ */
+ public function canGetStringValue() {
+ $parseString = '"some owner\\\'s string"';
+ $value = $this->fixture->_callRef('getValue', $parseString);
+ $expected = array('some owner\'s string', '"');
+
+ $this->assertEquals($expected, $value);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=13104
+ */
+ public function canGetStringValueWithSingleQuote() {
+ $parseString = "'some owner\'s string'";
+ $value = $this->fixture->_callRef('getValue', $parseString);
+ $expected = array('some owner\'s string', "'");
+
+ $this->assertEquals($expected, $value);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=13104
+ */
+ public function canGetStringValueWithDoubleQuote() {
+ $parseString = '"the \"owner\" is here"';
+ $value = $this->fixture->_callRef('getValue', $parseString);
+ $expected = array('the "owner" is here', '"');
+
+ $this->assertEquals($expected, $value);
+ }
+
+ /**
+ * @test
+ */
+ public function canGetListOfValues() {
+ $parseString = '( 1, 2, 3 ,4)';
+ $operator = 'IN';
+ $values = $this->fixture->_callRef('getValue', $parseString, $operator);
+ $expected = array(
+ array(1),
+ array(2),
+ array(3),
+ array(4)
+ );
+
+ $this->assertEquals($expected, $values);
+ }
+
+ /**
+ * @test
+ */
+ public function parseWhereClauseReturnsArray() {
+ $parseString = 'uid IN (1,2) AND (starttime < ' . time() . ' OR cruser_id + 10 < 20)';
+ $where = $this->fixture->parseWhereClause($parseString);
+
+ $this->assertTrue(is_array($where), $where);
+ $this->assertTrue(empty($parseString), 'parseString is not empty');
+ }
+
+ /**
+ * @test
+ */
+ public function canSelectAllFieldsFromPages() {
+ $sql = 'SELECT * FROM pages';
+ $expected = $sql;
+ $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
+
+ $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);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=14456
+ */
+ public function canParseAlterEngineStatement() {
+ $parseString = 'ALTER TABLE tx_realurl_pathcache ENGINE=InnoDB';
+ $components = $this->fixture->_callRef('parseALTERTABLE', $parseString);
+
+ $this->assertTrue(is_array($components), $components);
+ $alterTable = $this->cleanSql($this->fixture->_callRef('compileALTERTABLE', $components));
+ $expected = 'ALTER TABLE tx_realurl_pathcache ENGINE = InnoDB';
+ $this->assertTrue(is_array($alterTable), $alterTable);
+ $this->assertEquals($expected, $alterTable[0]);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=14496
+ */
+ public function canParseAlterCharacterSetStatement() {
+ $parseString = 'ALTER TABLE `index_phash` DEFAULT CHARACTER SET utf8';
+ $components = $this->fixture->_callRef('parseALTERTABLE', $parseString);
+
+ $this->assertTrue(is_array($components), $components);
+ $alterTable = $this->cleanSql($this->fixture->_callRef('compileALTERTABLE', $components));
+ $expected = 'ALTER TABLE index_phash DEFAULT CHARACTER SET utf8';
+ $this->assertTrue(is_array($alterTable), $alterTable);
+ $this->assertEquals($expected, $alterTable[0]);
+ }
+
+ ///////////////////////////////////////
+ // Tests concerning JOINs
+ ///////////////////////////////////////
+
+ /**
+ * @test
+ */
+ public function parseFromTablesWithInnerJoinReturnsArray() {
+ $parseString = 'be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
+ $tables = $this->fixture->parseFromTables($parseString);
+
+ $this->assertTrue(is_array($tables), $tables);
+ $this->assertTrue(empty($parseString), 'parseString is not empty');
+ }
+
+ /**
+ * @test
+ */
+ public function parseFromTablesWithLeftOuterJoinReturnsArray() {
+ $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id';
+ $tables = $this->fixture->parseFromTables($parseString);
+
+ $this->assertTrue(is_array($tables), $tables);
+ $this->assertTrue(empty($parseString), 'parseString is not empty');
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12596
+ */
+ public function parseFromTablesWithRightOuterJoinReturnsArray() {
+ $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid';
+ $tables = $this->fixture->parseFromTables($parseString);
+
+ $this->assertTrue(is_array($tables), $tables);
+ $this->assertTrue(empty($parseString), 'parseString is not empty');
+ }
+
+ /**
+ * @test
+ */
+ public function parseFromTablesWithMultipleJoinsReturnsArray() {
+ $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id INNER JOIN cache_pages cp ON cp.page_id = pages.uid';
+ $tables = $this->fixture->parseFromTables($parseString);
+
+ $this->assertTrue(is_array($tables), $tables);
+ $this->assertTrue(empty($parseString), 'parseString is not empty');
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12596
+ */
+ public function parseFromTablesWithMultipleJoinsAndParenthesesReturnsArray() {
+ $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid LEFT JOIN tx_powermail_fields ON tx_powermail_fieldsets.uid = tx_powermail_fields.fieldset';
+ $tables = $this->fixture->parseFromTables($parseString);
+
+ $this->assertTrue(is_array($tables), $tables);
+ $this->assertTrue(empty($parseString), 'parseString is not empty');
+ }
+
+ /**
+ * @test
+ */
+ public function canUseInnerJoinInSelect() {
+ $sql = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
+ $expected = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id=be_users.uid';
+ $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
+
+ $this->assertEquals($expected, $actual);
+ }
+
+ /**
+ * @test
+ */
+ public function canUseMultipleInnerJoinsInSelect() {
+ $sql = 'SELECT * FROM tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid = tt_news_cat_mm.uid_local';
+ $expected = 'SELECT * FROM tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid=tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid=tt_news_cat_mm.uid_local';
+ $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
+
+ $this->assertEquals($expected, $actual);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=14182
+ */
+ public function canParseMultipleJoinConditions() {
+ $sql = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.pid = T1.uid AND T2.size = 4 WHERE T1.cr_userid = 1';
+ $expected = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.pid=T1.uid AND T2.size=4 WHERE T1.cr_userid = 1';
+ $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
+
+ $this->assertEquals($expected, $actual);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=14182
+ */
+ public function canParseMultipleJoinConditionsWithLessThanOperator() {
+ $sql = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.size < 4 OR T2.pid = T1.uid WHERE T1.cr_userid = 1';
+ $expected = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.size<4 OR T2.pid=T1.uid WHERE T1.cr_userid = 1';
+ $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
+
+ $this->assertEquals($expected, $actual);
+ }
+
+ ///////////////////////////////////////
+ // Tests concerning DB management
+ ///////////////////////////////////////
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=4466
+ */
+ public function indexMayContainALengthRestrictionInCreateTable() {
+ $parseString = '
+ CREATE TABLE tx_realurl_uniqalias (
+ uid int(11) NOT NULL auto_increment,
+ tstamp int(11) DEFAULT \'0\' NOT NULL,
+ tablename varchar(60) DEFAULT \'\' NOT NULL,
+ field_alias varchar(255) DEFAULT \'\' NOT NULL,
+ field_id varchar(60) DEFAULT \'\' NOT NULL,
+ value_alias varchar(255) DEFAULT \'\' NOT NULL,
+ value_id int(11) DEFAULT \'0\' NOT NULL,
+ lang int(11) DEFAULT \'0\' NOT NULL,
+ expire int(11) DEFAULT \'0\' NOT NULL,
+
+ PRIMARY KEY (uid),
+ KEY tablename (tablename),
+ KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
+ KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
+ );
+ ';
+
+ $createTables = $this->fixture->_callRef('parseCREATETABLE', $parseString);
+ $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);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=2186
+ */
+ public function canParseUniqueIndexCreation() {
+ $sql = 'ALTER TABLE static_territories ADD UNIQUE uid (uid)';
+ $expected = $sql;
+ $alterTables = $this->fixture->_callRef('parseALTERTABLE', $sql);
+ $queries = $this->fixture->compileSQL($alterTables);
+
+ $this->assertTrue(is_array($queries), $queries);
+ $this->assertTrue(count($queries) == 1, $queries);
+ $this->assertEquals($expected, $queries[0]);
+ }
+
+ ///////////////////////////////////////
+ // Tests concerning subqueries
+ ///////////////////////////////////////
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12758
+ */
+ public function inWhereClauseSupportsSubquery() {
+ $parseString = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
+ $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=12758
+ */
+ public function inWhereClauseWithSubqueryIsProperlyCompiled() {
+ $sql = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
+ $expected = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active = 0 AND deleted = 0)';
+ $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
+
+ $this->assertEquals($expected, $actual);
+ }
+
+ /**
+ * @test
+ * @see http://bugs.typo3.org/view.php?id=12758
+ */
+ public function whereClauseSupportsExistsKeyword() {
+ $parseString = 'EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';
+ $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=12758
+ */
+ public function existsClauseIsProperlyCompiled() {
+ $sql = 'SELECT * FROM tx_crawler_process WHERE active = 0 AND NOT EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';
+ $expected = 'SELECT * FROM tx_crawler_process WHERE active = 0 AND NOT EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';
+ $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
+
+ $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
+++ /dev/null
-<?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');
-
-/**
- * Testcase for class ux_t3lib_sqlparser
- *
- * $Id$
- *
- * @author Xavier Perseguers <typo3@perseguers.ch>
- *
- * @package TYPO3
- * @subpackage dbal
- */
-class sqlparser_general_testcase extends BaseTestCase {
-
- /**
- * @var ux_t3lib_sqlparser (extended to make protected methods public)
- */
- protected $fixture;
-
- /**
- * Prepares the environment before running a test.
- */
- public function setUp() {
- $className = self::buildAccessibleProxy('ux_t3lib_sqlparser');
- $this->fixture = new $className;
- }
-
- /**
- * Cleans up the environment after running a test.
- */
- public function tearDown() {
- unset($this->fixture);
- }
-
- /**
- * 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 canExtractPartsOfAQuery() {
- $parseString = "SELECT *\nFROM pages WHERE pid IN (1,2,3,4)";
- $regex = '^SELECT[[:space:]]+(.*)[[:space:]]+';
- $trimAll = TRUE;
- $fields = $this->fixture->_callRef('nextPart', $parseString, $regex, $trimAll);
-
- $this->assertEquals(
- '*',
- $fields
- );
- $this->assertEquals(
- 'FROM pages WHERE pid IN (1,2,3,4)',
- $parseString
- );
-
- $regex = '^FROM ([^)]+) WHERE';
- $table = $this->fixture->_callRef('nextPart', $parseString, $regex);
-
- $this->assertEquals(
- 'pages',
- $table
- );
- $this->assertEquals(
- 'pages WHERE pid IN (1,2,3,4)',
- $parseString
- );
- }
-
- /**
- * @test
- */
- public function canGetIntegerValue() {
- $parseString = '1024';
- $value = $this->fixture->_callRef('getValue', $parseString);
- $expected = array(1024);
-
- $this->assertEquals($expected, $value);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=13104
- */
- public function canGetStringValue() {
- $parseString = '"some owner\\\'s string"';
- $value = $this->fixture->_callRef('getValue', $parseString);
- $expected = array('some owner\'s string', '"');
-
- $this->assertEquals($expected, $value);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=13104
- */
- public function canGetStringValueWithSingleQuote() {
- $parseString = "'some owner\'s string'";
- $value = $this->fixture->_callRef('getValue', $parseString);
- $expected = array('some owner\'s string', "'");
-
- $this->assertEquals($expected, $value);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=13104
- */
- public function canGetStringValueWithDoubleQuote() {
- $parseString = '"the \"owner\" is here"';
- $value = $this->fixture->_callRef('getValue', $parseString);
- $expected = array('the "owner" is here', '"');
-
- $this->assertEquals($expected, $value);
- }
-
- /**
- * @test
- */
- public function canGetListOfValues() {
- $parseString = '( 1, 2, 3 ,4)';
- $operator = 'IN';
- $values = $this->fixture->_callRef('getValue', $parseString, $operator);
- $expected = array(
- array(1),
- array(2),
- array(3),
- array(4)
- );
-
- $this->assertEquals($expected, $values);
- }
-
- /**
- * @test
- */
- public function parseWhereClauseReturnsArray() {
- $parseString = 'uid IN (1,2) AND (starttime < ' . time() . ' OR cruser_id + 10 < 20)';
- $where = $this->fixture->parseWhereClause($parseString);
-
- $this->assertTrue(is_array($where), $where);
- $this->assertTrue(empty($parseString), 'parseString is not empty');
- }
-
- /**
- * @test
- */
- public function canSelectAllFieldsFromPages() {
- $sql = 'SELECT * FROM pages';
- $expected = $sql;
- $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
-
- $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);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=14456
- */
- public function canParseAlterEngineStatement() {
- $parseString = 'ALTER TABLE tx_realurl_pathcache ENGINE=InnoDB';
- $components = $this->fixture->_callRef('parseALTERTABLE', $parseString);
-
- $this->assertTrue(is_array($components), $components);
- $alterTable = $this->cleanSql($this->fixture->_callRef('compileALTERTABLE', $components));
- $expected = 'ALTER TABLE tx_realurl_pathcache ENGINE = InnoDB';
- $this->assertTrue(is_array($alterTable), $alterTable);
- $this->assertEquals($expected, $alterTable[0]);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=14496
- */
- public function canParseAlterCharacterSetStatement() {
- $parseString = 'ALTER TABLE `index_phash` DEFAULT CHARACTER SET utf8';
- $components = $this->fixture->_callRef('parseALTERTABLE', $parseString);
-
- $this->assertTrue(is_array($components), $components);
- $alterTable = $this->cleanSql($this->fixture->_callRef('compileALTERTABLE', $components));
- $expected = 'ALTER TABLE index_phash DEFAULT CHARACTER SET utf8';
- $this->assertTrue(is_array($alterTable), $alterTable);
- $this->assertEquals($expected, $alterTable[0]);
- }
-
- ///////////////////////////////////////
- // Tests concerning JOINs
- ///////////////////////////////////////
-
- /**
- * @test
- */
- public function parseFromTablesWithInnerJoinReturnsArray() {
- $parseString = 'be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
- $tables = $this->fixture->parseFromTables($parseString);
-
- $this->assertTrue(is_array($tables), $tables);
- $this->assertTrue(empty($parseString), 'parseString is not empty');
- }
-
- /**
- * @test
- */
- public function parseFromTablesWithLeftOuterJoinReturnsArray() {
- $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id';
- $tables = $this->fixture->parseFromTables($parseString);
-
- $this->assertTrue(is_array($tables), $tables);
- $this->assertTrue(empty($parseString), 'parseString is not empty');
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12596
- */
- public function parseFromTablesWithRightOuterJoinReturnsArray() {
- $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid';
- $tables = $this->fixture->parseFromTables($parseString);
-
- $this->assertTrue(is_array($tables), $tables);
- $this->assertTrue(empty($parseString), 'parseString is not empty');
- }
-
- /**
- * @test
- */
- public function parseFromTablesWithMultipleJoinsReturnsArray() {
- $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id INNER JOIN cache_pages cp ON cp.page_id = pages.uid';
- $tables = $this->fixture->parseFromTables($parseString);
-
- $this->assertTrue(is_array($tables), $tables);
- $this->assertTrue(empty($parseString), 'parseString is not empty');
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12596
- */
- public function parseFromTablesWithMultipleJoinsAndParenthesesReturnsArray() {
- $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid LEFT JOIN tx_powermail_fields ON tx_powermail_fieldsets.uid = tx_powermail_fields.fieldset';
- $tables = $this->fixture->parseFromTables($parseString);
-
- $this->assertTrue(is_array($tables), $tables);
- $this->assertTrue(empty($parseString), 'parseString is not empty');
- }
-
- /**
- * @test
- */
- public function canUseInnerJoinInSelect() {
- $sql = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
- $expected = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id=be_users.uid';
- $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
-
- $this->assertEquals($expected, $actual);
- }
-
- /**
- * @test
- */
- public function canUseMultipleInnerJoinsInSelect() {
- $sql = 'SELECT * FROM tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid = tt_news_cat_mm.uid_local';
- $expected = 'SELECT * FROM tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid=tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid=tt_news_cat_mm.uid_local';
- $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
-
- $this->assertEquals($expected, $actual);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=14182
- */
- public function canParseMultipleJoinConditions() {
- $sql = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.pid = T1.uid AND T2.size = 4 WHERE T1.cr_userid = 1';
- $expected = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.pid=T1.uid AND T2.size=4 WHERE T1.cr_userid = 1';
- $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
-
- $this->assertEquals($expected, $actual);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=14182
- */
- public function canParseMultipleJoinConditionsWithLessThanOperator() {
- $sql = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.size < 4 OR T2.pid = T1.uid WHERE T1.cr_userid = 1';
- $expected = 'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.size<4 OR T2.pid=T1.uid WHERE T1.cr_userid = 1';
- $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
-
- $this->assertEquals($expected, $actual);
- }
-
- ///////////////////////////////////////
- // Tests concerning DB management
- ///////////////////////////////////////
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=4466
- */
- public function indexMayContainALengthRestrictionInCreateTable() {
- $parseString = '
- CREATE TABLE tx_realurl_uniqalias (
- uid int(11) NOT NULL auto_increment,
- tstamp int(11) DEFAULT \'0\' NOT NULL,
- tablename varchar(60) DEFAULT \'\' NOT NULL,
- field_alias varchar(255) DEFAULT \'\' NOT NULL,
- field_id varchar(60) DEFAULT \'\' NOT NULL,
- value_alias varchar(255) DEFAULT \'\' NOT NULL,
- value_id int(11) DEFAULT \'0\' NOT NULL,
- lang int(11) DEFAULT \'0\' NOT NULL,
- expire int(11) DEFAULT \'0\' NOT NULL,
-
- PRIMARY KEY (uid),
- KEY tablename (tablename),
- KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
- KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
- );
- ';
-
- $createTables = $this->fixture->_callRef('parseCREATETABLE', $parseString);
- $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);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=2186
- */
- public function canParseUniqueIndexCreation() {
- $sql = 'ALTER TABLE static_territories ADD UNIQUE uid (uid)';
- $expected = $sql;
- $alterTables = $this->fixture->_callRef('parseALTERTABLE', $sql);
- $queries = $this->fixture->compileSQL($alterTables);
-
- $this->assertTrue(is_array($queries), $queries);
- $this->assertTrue(count($queries) == 1, $queries);
- $this->assertEquals($expected, $queries[0]);
- }
-
- ///////////////////////////////////////
- // Tests concerning subqueries
- ///////////////////////////////////////
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12758
- */
- public function inWhereClauseSupportsSubquery() {
- $parseString = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
- $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=12758
- */
- public function inWhereClauseWithSubqueryIsProperlyCompiled() {
- $sql = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
- $expected = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active = 0 AND deleted = 0)';
- $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
-
- $this->assertEquals($expected, $actual);
- }
-
- /**
- * @test
- * @see http://bugs.typo3.org/view.php?id=12758
- */
- public function whereClauseSupportsExistsKeyword() {
- $parseString = 'EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';
- $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=12758
- */
- public function existsClauseIsProperlyCompiled() {
- $sql = 'SELECT * FROM tx_crawler_process WHERE active = 0 AND NOT EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';
- $expected = 'SELECT * FROM tx_crawler_process WHERE active = 0 AND NOT EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';
- $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
-
- $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
projects / Packages / TYPO3.CMS.git / commitdiff