[FEATURE] Execute native prepared queries
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Tests / Unit / Database / PreparedStatementTest.php
1 <?php
2 namespace TYPO3\CMS\Core\Tests\Unit\Database;
3
4 /***************************************************************
5 * Copyright notice
6 *
7 * (c) 2010-2013 Helmut Hummel <helmut@typo3.org>
8 * All rights reserved
9 *
10 * This script is part of the TYPO3 project. The TYPO3 project is
11 * free software; you can redistribute it and/or modify
12 * it under the terms of the GNU General Public License as published by
13 * the Free Software Foundation; either version 2 of the License, or
14 * (at your option) any later version.
15 *
16 * The GNU General Public License can be found at
17 * http://www.gnu.org/copyleft/gpl.html.
18 *
19 * This script is distributed in the hope that it will be useful,
20 * but WITHOUT ANY WARRANTY; without even the implied warranty of
21 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
22 * GNU General Public License for more details.
23 *
24 * This copyright notice MUST APPEAR in all copies of the script!
25 ***************************************************************/
26
27 /**
28 * Test case
29 *
30 * @author Helmut Hummel <helmut@typo3.org>
31 */
32 class PreparedStatementTest extends \TYPO3\CMS\Core\Tests\UnitTestCase {
33
34 /**
35 * @var \PHPUnit_Framework_MockObject_MockObject|\TYPO3\CMS\Core\Database\DatabaseConnection
36 */
37 protected $databaseStub;
38
39 /**
40 * Create a new database mock object for every test
41 * and backup the original global database object.
42 *
43 * @return void
44 */
45 protected function setUp() {
46 $this->databaseStub = $this->setUpAndReturnDatabaseStub();
47 }
48
49 //////////////////////
50 // Utility functions
51 //////////////////////
52 /**
53 * Set up the stub to be able to get the result of the prepared statement.
54 *
55 * @return \PHPUnit_Framework_MockObject_MockObject
56 */
57 private function setUpAndReturnDatabaseStub() {
58 $GLOBALS['TYPO3_DB'] = $this->getAccessibleMock(
59 'TYPO3\\CMS\\Core\\Database\\DatabaseConnection',
60 array('prepare_PREPAREDquery'),
61 array(),
62 '',
63 FALSE,
64 FALSE
65 );
66
67 return $GLOBALS['TYPO3_DB'];
68 }
69
70 /**
71 * Create a object fo the subject to be tested.
72 *
73 * @param string $query
74 * @return \TYPO3\CMS\Core\Database\PreparedStatement
75 */
76 private function createPreparedStatement($query) {
77 return new \TYPO3\CMS\Core\Database\PreparedStatement($query, 'pages');
78 }
79
80 ////////////////////////////////////
81 // Tests for the utility functions
82 ////////////////////////////////////
83
84 /**
85 * @test
86 * @return void
87 */
88 public function setUpAndReturnDatabaseStubReturnsMockObjectOfDatabaseConnection() {
89 $this->assertTrue($this->setUpAndReturnDatabaseStub() instanceof \TYPO3\CMS\Core\Database\DatabaseConnection);
90 }
91
92 /**
93 * @test
94 * @return void
95 */
96 public function createPreparedStatementReturnsInstanceOfPreparedStatementClass() {
97 $this->assertTrue($this->createPreparedStatement('dummy') instanceof \TYPO3\CMS\Core\Database\PreparedStatement);
98 }
99
100 ///////////////////////////////////////
101 // Tests for \TYPO3\CMS\Core\Database\PreparedStatement
102 ///////////////////////////////////////
103 /**
104 * Data Provider for two tests, providing sample queries, parameters and expected result queries.
105 *
106 * @see parametersAreReplacedInQueryByCallingExecute
107 * @see parametersAreReplacedInQueryWhenBoundWithBindValues
108 * @return array
109 */
110 public function parametersAndQueriesDataProvider() {
111 return array(
112 'one named integer parameter' => array('SELECT * FROM pages WHERE pid=:pid', array(':pid' => 1), 'SELECT * FROM pages WHERE pid=?'),
113 'one unnamed integer parameter' => array('SELECT * FROM pages WHERE pid=?', array(1), 'SELECT * FROM pages WHERE pid=?'),
114 '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=? OR uid=?'),
115 '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=? OR uid=?'),
116 'two unnamed integer parameters are replaced' => array('SELECT * FROM pages WHERE pid=? OR uid=?', array(1, 1), 'SELECT * FROM pages WHERE pid=? OR uid=?'),
117 );
118 }
119
120 /**
121 * Checking if calling execute() with parameters, they are
122 * properly replaced in the query.
123 *
124 * @test
125 * @dataProvider parametersAndQueriesDataProvider
126 * @param string $query Query with unreplaced markers
127 * @param array $parameters Array of parameters to be replaced in the query
128 * @param string $expectedResult Query with all markers replaced
129 * @return void
130 */
131 public function parametersAreReplacedByQuestionMarkInQueryByCallingExecute($query, $parameters, $expectedResult) {
132 $statement = $this->createPreparedStatement($query);
133 $this->databaseStub->expects($this->any())->method('prepare_PREPAREDquery')->with($this->equalTo($expectedResult));
134 $statement->execute($parameters);
135 }
136
137 /**
138 * Checking if parameters bound to the statement by bindValues()
139 * are properly replaced in the query.
140 *
141 * @test
142 * @dataProvider parametersAndQueriesDataProvider
143 * @param string $query Query with unreplaced markers
144 * @param array $parameters Array of parameters to be replaced in the query
145 * @param string $expectedResult Query with all markers replaced
146 * @return void
147 */
148 public function parametersAreReplacedInQueryWhenBoundWithBindValues($query, $parameters, $expectedResult) {
149 $statement = $this->createPreparedStatement($query);
150 $this->databaseStub->expects($this->any())->method('prepare_PREPAREDquery')->with($this->equalTo($expectedResult));
151 $statement->bindValues($parameters);
152 $statement->execute();
153 }
154
155 /**
156 * Data Provider with invalid parameters.
157 *
158 * @see invalidParameterTypesPassedToBindValueThrowsException
159 * @return array
160 */
161 public function invalidParameterTypesPassedToBindValueThrowsExceptionDataProvider() {
162 return array(
163 'integer passed with param type NULL' => array(1, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_NULL),
164 'string passed with param type NULL' => array('1', \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_NULL),
165 'bool passed with param type NULL' => array(TRUE, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_NULL),
166 'NULL passed with param type INT' => array(NULL, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_INT),
167 'string passed with param type INT' => array('1', \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_INT),
168 'bool passed with param type INT' => array(TRUE, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_INT),
169 'NULL passed with param type BOOL' => array(NULL, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_BOOL),
170 'string passed with param type BOOL' => array('1', \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_BOOL),
171 'integer passed with param type BOOL' => array(1, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_BOOL)
172 );
173 }
174
175 /**
176 * Checking if an exception is thrown if invalid parameters are
177 * provided vor bindValue().
178 *
179 * @test
180 * @expectedException \InvalidArgumentException
181 * @dataProvider invalidParameterTypesPassedToBindValueThrowsExceptionDataProvider
182 * @param mixed $parameter Parameter to be replaced in the query
183 * @param integer $type Type of the parameter value
184 * @return void
185 */
186 public function invalidParameterTypesPassedToBindValueThrowsException($parameter, $type) {
187 $statement = $this->createPreparedStatement('');
188 $statement->bindValue(1, $parameter, $type);
189 }
190
191 /**
192 * Data Provider for invalid marker names.
193 *
194 * @see passingInvalidMarkersThrowsExeption
195 * @return array
196 */
197 public function passingInvalidMarkersThrowsExceptionDataProvider() {
198 return array(
199 'using other prefix than colon' => array('SELECT * FROM pages WHERE pid=#pid', array('#pid' => 1)),
200 'using non alphanumerical character' => array('SELECT * FROM pages WHERE title=:stra≠e', array(':stra≠e' => 1)),
201 'no colon used' => array('SELECT * FROM pages WHERE pid=pid', array('pid' => 1)),
202 'colon at the end' => array('SELECT * FROM pages WHERE pid=pid:', array('pid:' => 1)),
203 'colon without alphanumerical character' => array('SELECT * FROM pages WHERE pid=:', array(':' => 1))
204 );
205 }
206
207 /**
208 * Checks if an exception is thrown, if parameter have invalid marker named.
209 *
210 * @test
211 * @expectedException \InvalidArgumentException
212 * @dataProvider passingInvalidMarkersThrowsExceptionDataProvider
213 * @param string $query Query with unreplaced markers
214 * @param array $parameters Array of parameters to be replaced in the query
215 * @return void
216 */
217 public function passingInvalidMarkersThrowsException($query, $parameters) {
218 $statement = $this->createPreparedStatement($query);
219 $statement->bindValues($parameters);
220 }
221
222 }