File: UpdateSqlGenerationTest.php

package info (click to toggle)
doctrine 3.5.1%2Bdfsg-1
  • links: PTS, VCS
  • area: main
  • in suites:
  • size: 11,552 kB
  • sloc: php: 108,302; xml: 1,340; makefile: 35; sh: 14
file content (192 lines) | stat: -rw-r--r-- 7,026 bytes parent folder | download | duplicates (3)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
<?php

declare(strict_types=1);

namespace Doctrine\Tests\ORM\Query;

use Doctrine\DBAL\Types\Type as DBALType;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\Tests\DbalTypes\NegativeToPositiveType;
use Doctrine\Tests\OrmTestCase;
use PHPUnit\Framework\Attributes\Group;
use PHPUnit\Framework\Attributes\RequiresPhpunit;

/**
 * Test case for testing the saving and referencing of query identifiers.
 *
 * @link        http://www.phpdoctrine.org
 *
 * @todo        1) [romanb] We  might want to split the SQL generation tests into multiple
 *              testcases later since we'll have a lot of them and we might want to have special SQL
 *              generation tests for some dbms specific SQL syntaxes.
 */
#[RequiresPhpunit('< 12')]
class UpdateSqlGenerationTest extends OrmTestCase
{
    private EntityManagerInterface $entityManager;

    protected function setUp(): void
    {
        if (DBALType::hasType('negative_to_positive')) {
            DBALType::overrideType('negative_to_positive', NegativeToPositiveType::class);
        } else {
            DBALType::addType('negative_to_positive', NegativeToPositiveType::class);
        }

        $this->entityManager = $this->getTestEntityManager();
    }

    public function assertSqlGeneration($dqlToBeTested, $sqlToBeConfirmed): void
    {
        $query = $this->entityManager->createQuery($dqlToBeTested);

        parent::assertEquals($sqlToBeConfirmed, $query->getSql());

        $query->free();
    }

    public function testSupportsQueriesWithoutWhere(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.name = ?1',
            'UPDATE cms_users SET name = ?',
        );
    }

    public function testSupportsMultipleFieldsWithoutWhere(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.name = ?1, u.username = ?2',
            'UPDATE cms_users SET name = ?, username = ?',
        );
    }

    public function testSupportsWhereClauses(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.name = ?1 WHERE u.id = ?2',
            'UPDATE cms_users SET name = ? WHERE id = ?',
        );
    }

    public function testSupportsWhereClausesOnTheUpdatedField(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.name = ?1 WHERE u.name = ?2',
            'UPDATE cms_users SET name = ? WHERE name = ?',
        );
    }

    public function testSupportsMultipleWhereClause(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.name = ?1 WHERE u.name = ?2 AND u.status = ?3',
            'UPDATE cms_users SET name = ? WHERE name = ? AND status = ?',
        );
    }

    public function testSupportsInClause(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.name = ?1 WHERE u.id IN (1, 3, 4)',
            'UPDATE cms_users SET name = ? WHERE id IN (1, 3, 4)',
        );
    }

    public function testSupportsParametrizedInClause(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.name = ?1 WHERE u.id IN (?2, ?3, ?4)',
            'UPDATE cms_users SET name = ? WHERE id IN (?, ?, ?)',
        );
    }

    public function testSupportsNotInClause(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.name = ?1 WHERE u.id NOT IN (1, 3, 4)',
            'UPDATE cms_users SET name = ? WHERE id NOT IN (1, 3, 4)',
        );
    }

    public function testSupportsGreaterThanClause(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.status = ?1 WHERE u.id > ?2',
            'UPDATE cms_users SET status = ? WHERE id > ?',
        );
    }

    public function testSupportsGreaterThanOrEqualToClause(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.status = ?1 WHERE u.id >= ?2',
            'UPDATE cms_users SET status = ? WHERE id >= ?',
        );
    }

    public function testSupportsLessThanClause(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.status = ?1 WHERE u.id < ?2',
            'UPDATE cms_users SET status = ? WHERE id < ?',
        );
    }

    public function testSupportsLessThanOrEqualToClause(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.status = ?1 WHERE u.id <= ?2',
            'UPDATE cms_users SET status = ? WHERE id <= ?',
        );
    }

    public function testSupportsBetweenClause(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.status = ?1 WHERE u.id BETWEEN :from AND :to',
            'UPDATE cms_users SET status = ? WHERE id BETWEEN ? AND ?',
        );
    }

    public function testSingleValuedAssociationFieldInWhere(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\CMS\CmsPhonenumber p SET p.phonenumber = 1234 WHERE p.user = ?1',
            'UPDATE cms_phonenumbers SET phonenumber = 1234 WHERE user_id = ?',
        );
    }

    public function testSingleValuedAssociationFieldInSetClause(): void
    {
        $this->assertSqlGeneration(
            'update Doctrine\Tests\Models\CMS\CmsComment c set c.article = null where c.article=?1',
            'UPDATE cms_comments SET article_id = NULL WHERE article_id = ?',
        );
    }

    #[Group('DDC-980')]
    public function testSubselectTableAliasReferencing(): void
    {
        $this->assertSqlGeneration(
            "UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.status = 'inactive' WHERE SIZE(u.groups) = 10",
            "UPDATE cms_users SET status = 'inactive' WHERE (SELECT COUNT(*) FROM cms_users_groups c0_ WHERE c0_.user_id = cms_users.id) = 10",
        );
    }

    public function testCustomTypeValueSqlCompletelyIgnoredInUpdateStatements(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\CustomType\CustomTypeParent p SET p.customInteger = 1 WHERE p.id = 1',
            'UPDATE customtype_parents SET customInteger = 1 WHERE id = 1',
        );
    }

    public function testUpdateWithSubselectAsNewValue(): void
    {
        $this->assertSqlGeneration(
            'UPDATE Doctrine\Tests\Models\Company\CompanyFixContract fc SET fc.fixPrice = (SELECT ce2.salary FROM Doctrine\Tests\Models\Company\CompanyEmployee ce2 WHERE ce2.id = 2) WHERE fc.id = 1',
            "UPDATE company_contracts SET fixPrice = (SELECT c0_.salary FROM company_employees c0_ INNER JOIN company_persons c1_ ON c0_.id = c1_.id LEFT JOIN company_managers c2_ ON c0_.id = c2_.id WHERE c1_.id = 2) WHERE (id = 1) AND discr IN ('fix')",
        );
    }
}