tests/t3lib/db/class.t3lib_db_preparedstatementTest.php

   1 <?php
   2 /***************************************************************
   3  *  Copyright notice
   4  *
   5  *  (c) 2010-2011 Helmut Hummel <helmut@typo3.org>
   6  *  All rights reserved
   7  *
   8  *  This script is part of the TYPO3 project. The TYPO3 project is
   9  *  free software; you can redistribute it and/or modify
  10  *  it under the terms of the GNU General Public License as published by
  11  *  the Free Software Foundation; either version 2 of the License, or
  12  *  (at your option) any later version.
  13  *
  14  *  The GNU General Public License can be found at
  15  *  http://www.gnu.org/copyleft/gpl.html.
  16  *
  17  *  This script is distributed in the hope that it will be useful,
  18  *  but WITHOUT ANY WARRANTY; without even the implied warranty of
  19  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  20  *  GNU General Public License for more details.
  21  *
  22  *  This copyright notice MUST APPEAR in all copies of the script!
  23  ***************************************************************/
  24
  25
  26 /**
  27  * Testcase for the prepared statement database class
  28  *
  29  * @author      Helmut Hummel <helmut@typo3.org>
  30  * @package TYPO3
  31  * @subpackage tests
  32  */
  33 class t3lib_db_PreparedStatementTest extends tx_phpunit_testcase {
  34
  35         /**
  36          * Backup and restore of the $GLOBALS array.
  37          *
  38          * @var boolean
  39          */
  40         protected $backupGlobalsArray = array();
  41
  42         /**
  43          * Mock object of t3lib_db
  44          *
  45          * @var PHPUnit_Framework_MockObject_MockObject
  46          */
  47         private $databaseStub;
  48
  49         /**
  50          * Create a new database mock object for every test
  51          * and backup the original global database object.
  52          *
  53          * @return void
  54          */
  55         protected function setUp() {
  56                 $this->backupGlobalsArray['TYPO3_DB'] = $GLOBALS['TYPO3_DB'];
  57                 $this->databaseStub = $this->setUpAndReturnDatabaseStub();
  58         }
  59
  60         /**
  61          * Restore global database object.
  62          *
  63          * @return void
  64          */
  65         protected function tearDown() {
  66                 $GLOBALS['TYPO3_DB'] = $this->backupGlobalsArray['TYPO3_DB'];
  67         }
  68
  69         //////////////////////
  70         // Utility functions
  71         //////////////////////
  72
  73         /**
  74          * Set up the stub to be able to get the result of the prepared statement.
  75          *
  76          * @return PHPUnit_Framework_MockObject_MockObject
  77          */
  78         private function setUpAndReturnDatabaseStub() {
  79                 $databaseLink = $GLOBALS['TYPO3_DB']->link;
  80                 $GLOBALS['TYPO3_DB'] = $this->getMock('t3lib_DB', array('exec_PREPAREDquery'), array(), '', FALSE, FALSE);
  81                 $GLOBALS['TYPO3_DB']->link = $databaseLink;
  82                 return $GLOBALS['TYPO3_DB'];
  83         }
  84
  85         /**
  86          * Create a object fo the subject to be tested.
  87          *
  88          * @param string $query
  89          * @return t3lib_db_PreparedStatement
  90          */
  91         private function createPreparedStatement($query) {
  92                 return new t3lib_db_PreparedStatement($query, 'pages');
  93         }
  94
  95         ////////////////////////////////////
  96         // Tests for the utility functions
  97         ////////////////////////////////////
  98
  99         /**
 100          * Checks if setUpAndReturnDatabaseStub() really returns
 101          * a mock of t3lib_DB.
 102          *
 103          * @test
 104          * @return void
 105          */
 106         public function setUpAndReturnDatabaseStubReturnsMockObjectOf_t3lib_DB() {
 107                 $this->assertTrue($this->setUpAndReturnDatabaseStub() instanceof t3lib_DB);
 108         }
 109
 110         /**
 111          * Checks if createPreparedStatement() really returns an instance of t3lib_db_PreparedStatement.
 112          *
 113          * @test
 114          * @return void
 115          */
 116         public function createPreparedStatementReturnsInstanceOfPreparedStatementClass() {
 117                 $this->assertTrue($this->createPreparedStatement('dummy') instanceof t3lib_db_PreparedStatement);
 118         }
 119
 120         ///////////////////////////////////////
 121         // Tests for t3lib_db_PreparedStatement
 122         ///////////////////////////////////////
 123
 124         /**
 125          * Data Provider for two tests, providing sample queries, parameters and expected result queries.
 126          *
 127          * @see parametersAreReplacedInQueryByCallingExecute
 128          * @see parametersAreReplacedInQueryWhenBoundWithBindValues
 129          * @return array
 130          */
 131         public function parametersAndQueriesDataProvider() {
 132                 return array(
 133                         'one named integer parameter' => array('SELECT * FROM pages WHERE pid=:pid', array(':pid' => 1), 'SELECT * FROM pages WHERE pid=1'),
 134                         'one unnamed integer parameter' => array('SELECT * FROM pages WHERE pid=?', array(1), 'SELECT * FROM pages WHERE pid=1'),
 135                         'one named integer parameter is replaced multiple times' => array('SELECT * FROM pages WHERE pid=:pid OR uid=:pid', array(':pid' => 1), 'SELECT * FROM pages WHERE pid=1 OR uid=1'),
 136                         'two named integer parameters are replaced' => array('SELECT * FROM pages WHERE pid=:pid OR uid=:uid', array(':pid' => 1, ':uid' => 10), 'SELECT * FROM pages WHERE pid=1 OR uid=10'),
 137                         'two unnamed integer parameters are replaced' => array('SELECT * FROM pages WHERE pid=? OR uid=?', array(1,1), 'SELECT * FROM pages WHERE pid=1 OR uid=1'),
 138                         'php bool TRUE parameter is replaced with 1' => array('SELECT * FROM pages WHERE deleted=?', array(TRUE), 'SELECT * FROM pages WHERE deleted=1'),
 139                         'php bool FALSE parameter is replaced with 0' => array('SELECT * FROM pages WHERE deleted=?', array(FALSE), 'SELECT * FROM pages WHERE deleted=0'),
 140                         'php null parameter is replaced with NULL' => array('SELECT * FROM pages WHERE deleted=?', array(NULL), 'SELECT * FROM pages WHERE deleted=NULL'),
 141                         'string parameter is wrapped in quotes' => array('SELECT * FROM pages WHERE title=?', array('Foo bar'), "SELECT * FROM pages WHERE title='Foo bar'"),
 142                         'string single quotes in parameter are properly escaped' => array('SELECT * FROM pages WHERE title=?', array("'Foo'"), "SELECT * FROM pages WHERE title='\\'Foo\\''"),
 143                 );
 144         }
 145
 146         /**
 147          * Checking if calling execute() with parameters, they are
 148          * properly relpaced in the query.
 149          *
 150          * @test
 151          * @dataProvider parametersAndQueriesDataProvider
 152          * @param string $query                         Query with unreplaced markers
 153          * @param array  $parameters            Array of parameters to be replaced in the query
 154          * @param string $expectedResult        Query with all markers replaced
 155          * @return void
 156          */
 157         public function parametersAreReplacedInQueryByCallingExecute($query, $parameters, $expectedResult) {
 158                 $statement = $this->createPreparedStatement($query);
 159                 $this->databaseStub->expects($this->any())
 160                         ->method('exec_PREPAREDquery')
 161                         ->with(
 162                                 $this->equalTo($expectedResult)
 163                         );
 164                 $statement->execute($parameters);
 165         }
 166
 167         /**
 168          * Checking if parameters bound to the statement by bindValues()
 169          * are properly replaced in the query.
 170          *
 171          * @test
 172          * @dataProvider parametersAndQueriesDataProvider
 173          * @param string $query                         Query with unreplaced markers
 174          * @param array  $parameters            Array of parameters to be replaced in the query
 175          * @param string $expectedResult        Query with all markers replaced
 176          * @return void
 177          */
 178         public function parametersAreReplacedInQueryWhenBoundWithBindValues($query, $parameters, $expectedResult) {
 179                 $statement = $this->createPreparedStatement($query);
 180                 $this->databaseStub->expects($this->any())
 181                         ->method('exec_PREPAREDquery')
 182                         ->with(
 183                                 $this->equalTo($expectedResult)
 184                         );
 185                 $statement->bindValues($parameters);
 186                 $statement->execute();
 187         }
 188
 189         /**
 190          * Data Provider with invalid parameters.
 191          *
 192          * @see invalidParameterTypesPassedToBindValueThrowsException
 193          * @return array
 194          */
 195         public function invalidParameterTypesPassedToBindValueThrowsExceptionDataProvider() {
 196                 return array(
 197                         'integer passed with param type NULL' => array(1, t3lib_db_PreparedStatement::PARAM_NULL),
 198                         'string passed with param type NULL' => array('1', t3lib_db_PreparedStatement::PARAM_NULL),
 199                         'bool passed with param type NULL' => array(TRUE, t3lib_db_PreparedStatement::PARAM_NULL),
 200                         'NULL passed with param type INT' => array(NULL, t3lib_db_PreparedStatement::PARAM_INT),
 201                         'string passed with param type INT' => array('1', t3lib_db_PreparedStatement::PARAM_INT),
 202                         'bool passed with param type INT' => array(TRUE, t3lib_db_PreparedStatement::PARAM_INT),
 203                         'NULL passed with param type BOOL' => array(NULL, t3lib_db_PreparedStatement::PARAM_BOOL),
 204                         'string passed with param type BOOL' => array('1', t3lib_db_PreparedStatement::PARAM_BOOL),
 205                         'integer passed with param type BOOL' => array(1, t3lib_db_PreparedStatement::PARAM_BOOL),
 206                 );
 207         }
 208
 209         /**
 210          * Checking if an exception is thrown if invalid parameters are
 211          * provided vor bindValue().
 212          *
 213          * @test
 214          * @expectedException InvalidArgumentException
 215          * @dataProvider invalidParameterTypesPassedToBindValueThrowsExceptionDataProvider
 216          * @param mixed   $parameter    Parameter to be replaced in the query
 217          * @param integer $type                 Type of the parameter value
 218          * @return void
 219          */
 220         public function invalidParameterTypesPassedToBindValueThrowsException($parameter, $type) {
 221                 $statement = $this->createPreparedStatement('');
 222                 $statement->bindValue(1, $parameter, $type);
 223         }
 224
 225         /**
 226          * Checking if formerly bound values are replaced by the values passed to execute().
 227          *
 228          * @test
 229          * @return void
 230          */
 231         public function parametersPassedToExecuteOverrulesFormerlyBoundValues() {
 232                 $query = 'SELECT * FROM pages WHERE pid=? OR uid=?';
 233                 $expectedResult = 'SELECT * FROM pages WHERE pid=30 OR uid=40';
 234                 $this->databaseStub->expects($this->any())
 235                         ->method('exec_PREPAREDquery')
 236                         ->with(
 237                                 $this->equalTo($expectedResult)
 238                         );
 239
 240                 $statement = $this->createPreparedStatement($query);
 241                 $statement->bindValues(array(10, 20));
 242                 $statement->execute(array(30, 40));
 243         }
 244
 245         /**
 246          * Data Provieder for invalid marker names.
 247          *
 248          * @see passingInvalidMarkersThrowsExeption
 249          * @return array
 250          */
 251         public function passingInvalidMarkersThrowsExeptionDataProvider() {
 252                 return array(
 253                         'using other prefix than colon' => array('SELECT * FROM pages WHERE pid=#pid', array('#pid' => 1)),
 254                         'using non alphanumerical character' => array('SELECT * FROM pages WHERE title=:stra≠e', array(':stra≠e' => 1)),
 255                         'no colon used' => array('SELECT * FROM pages WHERE pid=pid', array('pid' => 1)),
 256                         'colon at the end' => array('SELECT * FROM pages WHERE pid=pid:', array('pid:' => 1)),
 257                         'colon without alphanumerical character' => array('SELECT * FROM pages WHERE pid=:', array(':' => 1)),
 258                 );
 259         }
 260
 261         /**
 262          * Checks if an exception is thrown, if parameter have invalid marker named.
 263          *
 264          * @test
 265          * @expectedException InvalidArgumentException
 266          * @dataProvider passingInvalidMarkersThrowsExeptionDataProvider
 267          * @param string $query                         Query with unreplaced markers
 268          * @param array  $parameters            Array of parameters to be replaced in the query
 269          * @return void
 270          */
 271         public function passingInvalidMarkersThrowsExeption($query, $parameters) {
 272                 $statement = $this->createPreparedStatement($query);
 273                 $statement->execute($parameters);
 274         }
 275 }
 276
 277 ?>