da7c4bd944ed463e501e91b8ea83fa8585e0695a
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / Tests / DatabaseGeneralTest.php
1 <?php
2 namespace TYPO3\CMS\Dbal\Tests;
3
4 /**
5 * Testcase for class ux_t3lib_db.
6 *
7 * @author Xavier Perseguers <xavier@typo3.org>
8 * @package TYPO3
9 * @subpackage dbal
10 */
11 class DatabaseGeneralTest extends \TYPO3\CMS\Dbal\Tests\BaseTestCase {
12
13 /**
14 * @var t3lib_db
15 */
16 protected $db;
17
18 /**
19 * @var array
20 */
21 protected $loadedExtensions;
22
23 /**
24 * @var array
25 */
26 protected $temporaryFiles;
27
28 /**
29 * Prepares the environment before running a test.
30 */
31 public function setUp() {
32 // Backup list of loaded extensions
33 $this->loadedExtensions = $GLOBALS['TYPO3_LOADED_EXT'];
34 // Backup database connection
35 $this->db = $GLOBALS['TYPO3_DB'];
36 $this->temporaryFiles = array();
37 $className = self::buildAccessibleProxy('ux_t3lib_db');
38 $GLOBALS['TYPO3_DB'] = new $className();
39 $GLOBALS['TYPO3_DB']->lastHandlerKey = '_DEFAULT';
40 }
41
42 /**
43 * Cleans up the environment after running a test.
44 */
45 public function tearDown() {
46 // Clear DBAL-generated cache files
47 $GLOBALS['TYPO3_DB']->clearCachedFieldInfo();
48 // Delete temporary files
49 foreach ($this->temporaryFiles as $filename) {
50 unlink($filename);
51 }
52 // Restore DB connection
53 $GLOBALS['TYPO3_DB'] = $this->db;
54 // Restore list of loaded extensions
55 $GLOBALS['TYPO3_LOADED_EXT'] = $this->loadedExtensions;
56 }
57
58 /**
59 * Cleans a SQL query.
60 *
61 * @param mixed $sql
62 * @return mixed (string or array)
63 */
64 private function cleanSql($sql) {
65 if (!is_string($sql)) {
66 return $sql;
67 }
68 $sql = str_replace('
69 ', ' ', $sql);
70 $sql = preg_replace('/\\s+/', ' ', $sql);
71 return trim($sql);
72 }
73
74 /**
75 * Creates a fake extension with a given table definition.
76 *
77 * @param string $tableDefinition SQL script to create the extension's tables
78 * @return void
79 */
80 protected function createFakeExtension($tableDefinition) {
81 // Prepare a fake extension configuration
82 $ext_tables = \TYPO3\CMS\Core\Utility\GeneralUtility::tempnam('ext_tables');
83 \TYPO3\CMS\Core\Utility\GeneralUtility::writeFile($ext_tables, $tableDefinition);
84 $this->temporaryFiles[] = $ext_tables;
85 $GLOBALS['TYPO3_LOADED_EXT']['test_dbal'] = array(
86 'ext_tables.sql' => $ext_tables
87 );
88 // Append our test table to the list of existing tables
89 $GLOBALS['TYPO3_DB']->clearCachedFieldInfo();
90 $GLOBALS['TYPO3_DB']->_call('initInternalVariables');
91 }
92
93 /**
94 * @test
95 * @see http://bugs.typo3.org/view.php?id=12515
96 */
97 public function concatCanBeParsedAfterLikeOperator() {
98 $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)'));
99 $expected = 'SELECT * FROM sys_refindex, tx_dam_file_tracking WHERE sys_refindex.tablename = \'tx_dam_file_tracking\'';
100 $expected .= ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)';
101 $this->assertEquals($expected, $query);
102 }
103
104 /**
105 * @test
106 * @see http://bugs.typo3.org/view.php?id=10965
107 */
108 public function floatNumberCanBeStoredInDatabase() {
109 $this->createFakeExtension('
110 CREATE TABLE tx_test_dbal (
111 foo double default \'0\',
112 foobar integer default \'0\'
113 );
114 ');
115 $data = array(
116 'foo' => 99.12,
117 'foobar' => -120
118 );
119 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->INSERTquery('tx_test_dbal', $data));
120 $expected = 'INSERT INTO tx_test_dbal ( foo, foobar ) VALUES ( \'99.12\', \'-120\' )';
121 $this->assertEquals($expected, $query);
122 }
123
124 /**
125 * @test
126 * @see http://bugs.typo3.org/view.php?id=11093
127 */
128 public function positive64BitIntegerIsSupported() {
129 $this->createFakeExtension('
130 CREATE TABLE tx_test_dbal (
131 foo int default \'0\',
132 foobar bigint default \'0\'
133 );
134 ');
135 $data = array(
136 'foo' => 9223372036854775807,
137 'foobar' => 9223372036854775807
138 );
139 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->INSERTquery('tx_test_dbal', $data));
140 $expected = 'INSERT INTO tx_test_dbal ( foo, foobar ) VALUES ( \'9223372036854775807\', \'9223372036854775807\' )';
141 $this->assertEquals($expected, $query);
142 }
143
144 /**
145 * @test
146 * @see http://bugs.typo3.org/view.php?id=11093
147 */
148 public function negative64BitIntegerIsSupported() {
149 $this->createFakeExtension('
150 CREATE TABLE tx_test_dbal (
151 foo int default \'0\',
152 foobar bigint default \'0\'
153 );
154 ');
155 $data = array(
156 'foo' => -9.2233720368548E+18,
157 'foobar' => -9.2233720368548E+18
158 );
159 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->INSERTquery('tx_test_dbal', $data));
160 $expected = 'INSERT INTO tx_test_dbal ( foo, foobar ) VALUES ( \'-9223372036854775808\', \'-9223372036854775808\' )';
161 $this->assertEquals($expected, $query);
162 }
163
164 /**
165 * @test
166 */
167 public function sqlForInsertWithMultipleRowsIsValid() {
168 $fields = array('uid', 'pid', 'title', 'body');
169 $rows = array(
170 array('1', '2', 'Title #1', 'Content #1'),
171 array('3', '4', 'Title #2', 'Content #2'),
172 array('5', '6', 'Title #3', 'Content #3')
173 );
174 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->INSERTmultipleRows('tt_content', $fields, $rows));
175 $expected = 'INSERT INTO tt_content (uid, pid, title, body) VALUES ';
176 $expected .= '(\'1\', \'2\', \'Title #1\', \'Content #1\'), ';
177 $expected .= '(\'3\', \'4\', \'Title #2\', \'Content #2\'), ';
178 $expected .= '(\'5\', \'6\', \'Title #3\', \'Content #3\')';
179 $this->assertEquals($expected, $query);
180 }
181
182 /**
183 * @test
184 * @see http://bugs.typo3.org/view.php?id=4493
185 */
186 public function minFunctionAndInOperatorCanBeParsed() {
187 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery('*', 'pages', 'MIN(uid) IN (1,2,3,4)'));
188 $expected = 'SELECT * FROM pages WHERE MIN(uid) IN (1,2,3,4)';
189 $this->assertEquals($expected, $query);
190 }
191
192 /**
193 * @test
194 * @see http://bugs.typo3.org/view.php?id=4493
195 */
196 public function maxFunctionAndInOperatorCanBeParsed() {
197 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery('*', 'pages', 'MAX(uid) IN (1,2,3,4)'));
198 $expected = 'SELECT * FROM pages WHERE MAX(uid) IN (1,2,3,4)';
199 $this->assertEquals($expected, $query);
200 }
201
202 /**
203 * @test
204 * @see http://bugs.typo3.org/view.php?id=12535
205 */
206 public function likeBinaryOperatorIsKept() {
207 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery('*', 'tt_content', 'bodytext LIKE BINARY \'test\''));
208 $expected = 'SELECT * FROM tt_content WHERE bodytext LIKE BINARY \'test\'';
209 $this->assertEquals($expected, $query);
210 }
211
212 /**
213 * @test
214 * @see http://bugs.typo3.org/view.php?id=12535
215 */
216 public function notLikeBinaryOperatorIsKept() {
217 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery('*', 'tt_content', 'bodytext NOT LIKE BINARY \'test\''));
218 $expected = 'SELECT * FROM tt_content WHERE bodytext NOT LIKE BINARY \'test\'';
219 $this->assertEquals($expected, $query);
220 }
221
222 ///////////////////////////////////////
223 // Tests concerning prepared queries
224 ///////////////////////////////////////
225 /**
226 * @test
227 * @see http://bugs.typo3.org/view.php?id=15457
228 */
229 public function similarNamedParametersAreProperlyReplaced() {
230 $sql = 'SELECT * FROM cache WHERE tag = :tag1 OR tag = :tag10 OR tag = :tag100';
231 $parameterValues = array(
232 ':tag1' => 'tag-one',
233 ':tag10' => 'tag-two',
234 ':tag100' => 'tag-three'
235 );
236 $className = self::buildAccessibleProxy('TYPO3\\CMS\\Core\\Database\\PreparedStatement');
237 $query = $sql;
238 $precompiledQueryParts = array();
239 $statement = new $className($sql, 'cache');
240 $statement->bindValues($parameterValues);
241 $parameters = $statement->_get('parameters');
242 $statement->_callRef('replaceValuesInQuery', $query, $precompiledQueryParts, $parameters);
243 $expected = 'SELECT * FROM cache WHERE tag = \'tag-one\' OR tag = \'tag-two\' OR tag = \'tag-three\'';
244 $this->assertEquals($expected, $query);
245 }
246
247 }
248
249
250 ?>