NamedParametersTest.php 6.74 KB
Newer Older
1 2
<?php

3
namespace Doctrine\DBAL\Tests\Functional;
4 5

use Doctrine\DBAL\Connection;
6 7
use Doctrine\DBAL\FetchMode;
use Doctrine\DBAL\ParameterType;
8
use Doctrine\DBAL\Schema\Table;
9
use Doctrine\DBAL\Tests\FunctionalTestCase;
Sergei Morozov's avatar
Sergei Morozov committed
10
use Throwable;
11 12
use const CASE_LOWER;
use function array_change_key_case;
13 14 15 16

/**
 * @group DDC-1372
 */
17
class NamedParametersTest extends FunctionalTestCase
18
{
19 20 21 22
    /**
     * @return iterable<int, array<int, mixed>>
     */
    public static function ticketProvider() : iterable
23
    {
24 25
        return [
            [
26
                'SELECT * FROM ddc1372_foobar f WHERE f.foo = :foo AND f.bar IN (:bar)',
27 28 29 30 31
                [
                    'foo' => 1,
                    'bar' => [1, 2, 3],
                ],
                [
32 33
                    'foo' => ParameterType::INTEGER,
                    'bar' => Connection::PARAM_INT_ARRAY,
34 35 36 37 38 39 40 41 42
                ],
                [
                    ['id' => 1, 'foo' => 1, 'bar' => 1],
                    ['id' => 2, 'foo' => 1, 'bar' => 2],
                    ['id' => 3, 'foo' => 1, 'bar' => 3],
                ],
            ],

            [
43
                'SELECT * FROM ddc1372_foobar f WHERE f.foo = :foo AND f.bar IN (:bar)',
44 45 46 47 48
                [
                    'foo' => 1,
                    'bar' => [1, 2, 3],
                ],
                [
49 50
                    'bar' => Connection::PARAM_INT_ARRAY,
                    'foo' => ParameterType::INTEGER,
51 52 53 54 55 56 57 58 59
                ],
                [
                    ['id' => 1, 'foo' => 1, 'bar' => 1],
                    ['id' => 2, 'foo' => 1, 'bar' => 2],
                    ['id' => 3, 'foo' => 1, 'bar' => 3],
                ],
            ],

            [
60
                'SELECT * FROM ddc1372_foobar f WHERE f.bar IN (:bar) AND f.foo = :foo',
61 62 63 64 65
                [
                    'foo' => 1,
                    'bar' => [1, 2, 3],
                ],
                [
66 67
                    'bar' => Connection::PARAM_INT_ARRAY,
                    'foo' => ParameterType::INTEGER,
68 69 70 71 72 73 74 75 76
                ],
                [
                    ['id' => 1, 'foo' => 1, 'bar' => 1],
                    ['id' => 2, 'foo' => 1, 'bar' => 2],
                    ['id' => 3, 'foo' => 1, 'bar' => 3],
                ],
            ],

            [
77
                'SELECT * FROM ddc1372_foobar f WHERE f.bar IN (:bar) AND f.foo = :foo',
78 79 80 81 82
                [
                    'foo' => 1,
                    'bar' => ['1', '2', '3'],
                ],
                [
83 84
                    'bar' => Connection::PARAM_STR_ARRAY,
                    'foo' => ParameterType::INTEGER,
85 86 87 88 89 90 91 92 93
                ],
                [
                    ['id' => 1, 'foo' => 1, 'bar' => 1],
                    ['id' => 2, 'foo' => 1, 'bar' => 2],
                    ['id' => 3, 'foo' => 1, 'bar' => 3],
                ],
            ],

            [
94
                'SELECT * FROM ddc1372_foobar f WHERE f.bar IN (:bar) AND f.foo IN (:foo)',
95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111
                [
                    'foo' => ['1'],
                    'bar' => [1, 2, 3, 4],
                ],
                [
                    'bar' => Connection::PARAM_STR_ARRAY,
                    'foo' => Connection::PARAM_INT_ARRAY,
                ],
                [
                    ['id' => 1, 'foo' => 1, 'bar' => 1],
                    ['id' => 2, 'foo' => 1, 'bar' => 2],
                    ['id' => 3, 'foo' => 1, 'bar' => 3],
                    ['id' => 4, 'foo' => 1, 'bar' => 4],
                ],
            ],

            [
112
                'SELECT * FROM ddc1372_foobar f WHERE f.bar IN (:bar) AND f.foo IN (:foo)',
113 114 115 116 117 118 119 120 121 122 123 124 125 126
                [
                    'foo' => 1,
                    'bar' => 2,
                ],
                [
                    'bar' => ParameterType::INTEGER,
                    'foo' => ParameterType::INTEGER,
                ],
                [
                    ['id' => 2, 'foo' => 1, 'bar' => 2],
                ],
            ],

            [
127
                'SELECT * FROM ddc1372_foobar f WHERE f.bar = :arg AND f.foo <> :arg',
128 129
                ['arg' => '1'],
                [
130
                    'arg' => ParameterType::STRING,
131 132 133 134 135
                ],
                [
                    ['id' => 5, 'foo' => 2, 'bar' => 1],
                ],
            ],
136

137
            [
138
                'SELECT * FROM ddc1372_foobar f WHERE f.bar NOT IN (:arg) AND f.foo IN (:arg)',
139 140 141 142 143 144 145 146 147 148 149 150
                [
                    'arg' => [1, 2],
                ],
                [
                    'arg' => Connection::PARAM_INT_ARRAY,
                ],
                [
                    ['id' => 3, 'foo' => 1, 'bar' => 3],
                    ['id' => 4, 'foo' => 1, 'bar' => 4],
                ],
            ],
        ];
151
    }
152

153
    protected function setUp() : void
154 155 156
    {
        parent::setUp();

Sergei Morozov's avatar
Sergei Morozov committed
157
        if ($this->connection->getSchemaManager()->tablesExist('ddc1372_foobar')) {
Sergei Morozov's avatar
Sergei Morozov committed
158 159
            return;
        }
160

Sergei Morozov's avatar
Sergei Morozov committed
161 162 163 164 165 166 167
        try {
            $table = new Table('ddc1372_foobar');
            $table->addColumn('id', 'integer');
            $table->addColumn('foo', 'string');
            $table->addColumn('bar', 'string');
            $table->setPrimaryKey(['id']);

Sergei Morozov's avatar
Sergei Morozov committed
168
            $sm = $this->connection->getSchemaManager();
Sergei Morozov's avatar
Sergei Morozov committed
169 170
            $sm->createTable($table);

Sergei Morozov's avatar
Sergei Morozov committed
171
            $this->connection->insert('ddc1372_foobar', [
Sergei Morozov's avatar
Sergei Morozov committed
172 173 174 175
                'id'  => 1,
                'foo' => 1,
                'bar' => 1,
            ]);
Sergei Morozov's avatar
Sergei Morozov committed
176
            $this->connection->insert('ddc1372_foobar', [
Sergei Morozov's avatar
Sergei Morozov committed
177 178 179 180
                'id'  => 2,
                'foo' => 1,
                'bar' => 2,
            ]);
Sergei Morozov's avatar
Sergei Morozov committed
181
            $this->connection->insert('ddc1372_foobar', [
Sergei Morozov's avatar
Sergei Morozov committed
182 183 184 185
                'id'  => 3,
                'foo' => 1,
                'bar' => 3,
            ]);
Sergei Morozov's avatar
Sergei Morozov committed
186
            $this->connection->insert('ddc1372_foobar', [
Sergei Morozov's avatar
Sergei Morozov committed
187 188 189 190
                'id'  => 4,
                'foo' => 1,
                'bar' => 4,
            ]);
Sergei Morozov's avatar
Sergei Morozov committed
191
            $this->connection->insert('ddc1372_foobar', [
Sergei Morozov's avatar
Sergei Morozov committed
192 193 194 195
                'id'  => 5,
                'foo' => 2,
                'bar' => 1,
            ]);
Sergei Morozov's avatar
Sergei Morozov committed
196
            $this->connection->insert('ddc1372_foobar', [
Sergei Morozov's avatar
Sergei Morozov committed
197 198 199 200 201
                'id'  => 6,
                'foo' => 2,
                'bar' => 2,
            ]);
        } catch (Throwable $e) {
202
            self::fail($e->getMessage());
203 204 205 206
        }
    }

    /**
Sergei Morozov's avatar
Sergei Morozov committed
207 208 209
     * @param mixed[] $params
     * @param int[]   $types
     * @param int[]   $expected
Sergei Morozov's avatar
Sergei Morozov committed
210 211
     *
     * @dataProvider ticketProvider
212
     */
213
    public function testTicket(string $query, array $params, array $types, array $expected) : void
214
    {
Sergei Morozov's avatar
Sergei Morozov committed
215
        $stmt   = $this->connection->executeQuery($query, $params, $types);
216
        $result = $stmt->fetchAll(FetchMode::ASSOCIATIVE);
217

218 219 220 221
        foreach ($result as $k => $v) {
            $result[$k] = array_change_key_case($v, CASE_LOWER);
        }

222
        self::assertEquals($result, $expected);
223
    }
224
}