5 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
6 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
7 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
8 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
9 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
10 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
11 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
12 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
13 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
14 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
15 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
17 * This software consists of voluntary contributions made by many individuals
18 * and is licensed under the LGPL. For more information, see
19 * <http://www.doctrine-project.org>.
22 namespace Doctrine\Tests\ORM\Query;
24 require_once __DIR__ . '/../../TestInit.php';
27 * Test case for testing the saving and referencing of query identifiers.
29 * @author Guilherme Blanco <guilhermeblanco@hotmail.com>
30 * @author Janne Vanhala <jpvanhal@cc.hut.fi>
31 * @author Konsta Vesterinen <kvesteri@cc.hut.fi>
32 * @license http://www.opensource.org/licenses/lgpl-license.php LGPL
33 * @link http://www.phpdoctrine.org
36 * @todo 1) [romanb] We might want to split the SQL generation tests into multiple
37 * testcases later since we'll have a lot of them and we might want to have special SQL
38 * generation tests for some dbms specific SQL syntaxes.
40 class DeleteSqlGenerationTest extends \Doctrine\Tests\OrmTestCase
44 protected function setUp() {
45 $this->_em = $this->_getTestEntityManager();
48 public function assertSqlGeneration($dqlToBeTested, $sqlToBeConfirmed)
51 $query = $this->_em->createQuery($dqlToBeTested);
52 parent::assertEquals($sqlToBeConfirmed, $query->getSql());
54 } catch (\Exception $e) {
55 $this->fail($e->getMessage());
59 public function testSupportsDeleteWithoutWhereAndFrom()
61 $this->assertSqlGeneration(
62 'DELETE Doctrine\Tests\Models\CMS\CmsUser u',
63 'DELETE FROM cms_users'
67 public function testSupportsDeleteWithoutWhere()
69 $this->assertSqlGeneration(
70 'DELETE FROM Doctrine\Tests\Models\CMS\CmsUser u',
71 'DELETE FROM cms_users'
75 public function testSupportsWhereClause()
77 $this->assertSqlGeneration(
78 'DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = ?1',
79 'DELETE FROM cms_users WHERE id = ?'
83 public function testSupportsWhereOrExpressions()
85 $this->assertSqlGeneration(
86 'DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username = ?1 OR u.name = ?2',
87 'DELETE FROM cms_users WHERE username = ? OR name = ?'
91 public function testSupportsWhereNestedConditionalExpressions()
93 $this->assertSqlGeneration(
94 'DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = ?1 OR ( u.username = ?2 OR u.name = ?3)',
95 'DELETE FROM cms_users WHERE id = ? OR (username = ? OR name = ?)'
98 //$this->assertSqlGeneration(
99 // 'DELETE FROM Doctrine\Tests\Models\CMS\CmsUser WHERE id = ?1',
100 // 'DELETE FROM cms_users WHERE id = ?'
104 public function testIsCaseAgnostic()
106 $this->assertSqlGeneration(
107 "delete from Doctrine\Tests\Models\CMS\CmsUser u where u.username = ?1",
108 "DELETE FROM cms_users WHERE username = ?"
112 public function testSupportsAndCondition()
114 $this->assertSqlGeneration(
115 "DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username = ?1 AND u.name = ?2",
116 "DELETE FROM cms_users WHERE username = ? AND name = ?"
120 public function testSupportsWhereNot()
122 $this->assertSqlGeneration(
123 "DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE NOT u.id != ?1",
124 "DELETE FROM cms_users WHERE NOT id <> ?"
128 public function testSupportsWhereNotWithParentheses()
130 $this->assertSqlGeneration(
131 "DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE NOT ( u.id != ?1 )",
132 "DELETE FROM cms_users WHERE NOT (id <> ?)"
136 public function testSupportsWhereNotWithAndExpression()
138 $this->assertSqlGeneration(
139 "DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE NOT ( u.id != ?1 AND u.username = ?2 )",
140 "DELETE FROM cms_users WHERE NOT (id <> ? AND username = ?)"
144 // ConditionalPrimary was already tested (see testSupportsWhereClause() and testSupportsWhereNot())
146 public function testSupportsGreaterThanComparisonClause()
148 // id = ? was already tested (see testDeleteWithWhere())
149 $this->assertSqlGeneration(
150 "DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id > ?1",
151 "DELETE FROM cms_users WHERE id > ?"
155 public function testSupportsGreaterThanOrEqualToComparisonClause()
157 $this->assertSqlGeneration(
158 "DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id >= ?1",
159 "DELETE FROM cms_users WHERE id >= ?"
163 public function testSupportsLessThanComparisonClause()
165 $this->assertSqlGeneration(
166 "DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id < ?1",
167 "DELETE FROM cms_users WHERE id < ?"
171 public function testSupportsLessThanOrEqualToComparisonClause()
173 $this->assertSqlGeneration(
174 "DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id <= ?1",
175 "DELETE FROM cms_users WHERE id <= ?"
179 public function testSupportsNotEqualToComparisonClause()
181 $this->assertSqlGeneration(
182 "DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id <> ?1",
183 "DELETE FROM cms_users WHERE id <> ?"
187 public function testSupportsNotEqualToComparisonClauseExpressedWithExclamationMark()
189 $this->assertSqlGeneration(
190 "DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id != ?1",
191 "DELETE FROM cms_users WHERE id <> ?"
195 public function testSupportsNotBetweenClause()
197 $this->assertSqlGeneration(
198 "DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id NOT BETWEEN ?1 AND ?2",
199 "DELETE FROM cms_users WHERE id NOT BETWEEN ? AND ?"
203 public function testSupportsBetweenClauseUsedWithAndClause()
205 $this->assertSqlGeneration(
206 "DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id BETWEEN ?1 AND ?2 AND u.username != ?3",
207 "DELETE FROM cms_users WHERE id BETWEEN ? AND ? AND username <> ?"
211 public function testSupportsNotLikeClause()
213 // "WHERE" Expression LikeExpression
214 $this->assertSqlGeneration(
215 'DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username NOT LIKE ?1',
216 'DELETE FROM cms_users WHERE username NOT LIKE ?'
220 public function testSupportsLikeClauseWithEscapeExpression()
222 $this->assertSqlGeneration(
223 "DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username LIKE ?1 ESCAPE '\\'",
224 "DELETE FROM cms_users WHERE username LIKE ? ESCAPE '\\'"
228 public function testSupportsIsNullClause()
230 // "WHERE" Expression NullComparisonExpression
231 $this->assertSqlGeneration(
232 'DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name IS NULL',
233 'DELETE FROM cms_users WHERE name IS NULL'
237 public function testSupportsIsNotNullClause()
239 $this->assertSqlGeneration(
240 'DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name IS NOT NULL',
241 'DELETE FROM cms_users WHERE name IS NOT NULL'
245 public function testSupportsAtomExpressionAsClause()
247 $this->assertSqlGeneration(
248 'DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE 1 = 1',
249 'DELETE FROM cms_users WHERE 1 = 1'
253 public function testSupportsParameterizedAtomExpression()
255 $this->assertSqlGeneration(
256 'DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE ?1 = 1',
257 'DELETE FROM cms_users WHERE ? = 1'
261 public function testSupportsInClause()
263 $this->assertSqlGeneration(
264 'DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id IN ( ?1, ?2, ?3, ?4 )',
265 'DELETE FROM cms_users WHERE id IN (?, ?, ?, ?)'
269 public function testSupportsNotInClause()
271 $this->assertSqlGeneration(
272 'DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id NOT IN ( ?1, ?2 )',
273 'DELETE FROM cms_users WHERE id NOT IN (?, ?)'
280 public function testSubselectTableAliasReferencing()
282 $this->assertSqlGeneration(
283 'DELETE Doctrine\Tests\Models\CMS\CmsUser u WHERE SIZE(u.groups) = 10',
284 'DELETE FROM cms_users WHERE (SELECT COUNT(*) FROM cms_users_groups c0_ WHERE c0_.user_id = cms_users.id) = 10'