3 namespace Doctrine\Tests\DBAL;
5 use Doctrine\DBAL\Connection;
6 use Doctrine\DBAL\SQLParserUtils;
8 require_once __DIR__ . '/../TestInit.php';
14 class SQLParserUtilsTest extends \Doctrine\Tests\DbalTestCase
16 static public function dataGetPlaceholderPositions()
20 array('SELECT * FROM Foo', true, array()),
21 array('SELECT * FROM Foo', false, array()),
24 array('SELECT ?', true, array(7)),
25 array('SELECT * FROM Foo WHERE bar IN (?, ?, ?)', true, array(32, 35, 38)),
26 array('SELECT ? FROM ?', true, array(7, 14)),
27 array('SELECT "?" FROM foo', true, array()),
28 array("SELECT '?' FROM foo", true, array()),
29 array('SELECT "?" FROM foo WHERE bar = ?', true, array(32)),
30 array("SELECT '?' FROM foo WHERE bar = ?", true, array(32)),
33 array('SELECT :foo FROM :bar', false, array(7 => 'foo', 17 => 'bar')),
34 array('SELECT * FROM Foo WHERE bar IN (:name1, :name2)', false, array(32 => 'name1', 40 => 'name2')),
35 array('SELECT ":foo" FROM Foo WHERE bar IN (:name1, :name2)', false, array(37 => 'name1', 45 => 'name2')),
36 array("SELECT ':foo' FROM Foo WHERE bar IN (:name1, :name2)", false, array(37 => 'name1', 45 => 'name2')),
37 array('SELECT :foo_id', false, array(7 => 'foo_id')), // Ticket DBAL-231
42 * @dataProvider dataGetPlaceholderPositions
44 * @param type $isPositional
45 * @param type $expectedParamPos
47 public function testGetPlaceholderPositions($query, $isPositional, $expectedParamPos)
49 $actualParamPos = SQLParserUtils::getPlaceholderPositions($query, $isPositional);
50 $this->assertEquals($expectedParamPos, $actualParamPos);
53 static public function dataExpandListParameters()
56 // Positional: Very simple with one needle
58 "SELECT * FROM Foo WHERE foo IN (?)",
59 array(array(1, 2, 3)),
60 array(Connection::PARAM_INT_ARRAY),
61 'SELECT * FROM Foo WHERE foo IN (?, ?, ?)',
63 array(\PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT)
65 // Positional: One non-list before d one after list-needle
67 "SELECT * FROM Foo WHERE foo = ? AND bar IN (?)",
68 array("string", array(1, 2, 3)),
69 array(\PDO::PARAM_STR, Connection::PARAM_INT_ARRAY),
70 'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?)',
71 array("string", 1, 2, 3),
72 array(\PDO::PARAM_STR, \PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT)
74 // Positional: One non-list after list-needle
76 "SELECT * FROM Foo WHERE bar IN (?) AND baz = ?",
77 array(array(1, 2, 3), "foo"),
78 array(Connection::PARAM_INT_ARRAY, \PDO::PARAM_STR),
79 'SELECT * FROM Foo WHERE bar IN (?, ?, ?) AND baz = ?',
80 array(1, 2, 3, "foo"),
81 array(\PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_STR)
83 // Positional: One non-list before and one after list-needle
85 "SELECT * FROM Foo WHERE foo = ? AND bar IN (?) AND baz = ?",
86 array(1, array(1, 2, 3), 4),
87 array(\PDO::PARAM_INT, Connection::PARAM_INT_ARRAY, \PDO::PARAM_INT),
88 'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?) AND baz = ?',
90 array(\PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT)
92 // Positional: Two lists
94 "SELECT * FROM Foo WHERE foo IN (?, ?)",
95 array(array(1, 2, 3), array(4, 5)),
96 array(Connection::PARAM_INT_ARRAY, Connection::PARAM_INT_ARRAY),
97 'SELECT * FROM Foo WHERE foo IN (?, ?, ?, ?, ?)',
99 array(\PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT)
101 // Positional : Empty "integer" array DDC-1978
103 "SELECT * FROM Foo WHERE foo IN (?)",
104 array('foo'=>array()),
105 array('foo'=>Connection::PARAM_INT_ARRAY),
106 'SELECT * FROM Foo WHERE foo IN (?)',
110 // Positional : Empty "str" array DDC-1978
112 "SELECT * FROM Foo WHERE foo IN (?)",
113 array('foo'=>array()),
114 array('foo'=>Connection::PARAM_STR_ARRAY),
115 'SELECT * FROM Foo WHERE foo IN (?)',
119 // Named parameters : Very simple with param int
121 "SELECT * FROM Foo WHERE foo = :foo",
123 array('foo'=>\PDO::PARAM_INT),
124 'SELECT * FROM Foo WHERE foo = ?',
126 array(\PDO::PARAM_INT)
129 // Named parameters : Very simple with param int and string
131 "SELECT * FROM Foo WHERE foo = :foo AND bar = :bar",
132 array('bar'=>'Some String','foo'=>1),
133 array('foo'=>\PDO::PARAM_INT,'bar'=>\PDO::PARAM_STR),
134 'SELECT * FROM Foo WHERE foo = ? AND bar = ?',
135 array(1,'Some String'),
136 array(\PDO::PARAM_INT, \PDO::PARAM_STR)
139 // Named parameters : Very simple with one needle
141 "SELECT * FROM Foo WHERE foo IN (:foo)",
142 array('foo'=>array(1, 2, 3)),
143 array('foo'=>Connection::PARAM_INT_ARRAY),
144 'SELECT * FROM Foo WHERE foo IN (?, ?, ?)',
146 array(\PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT)
148 // Named parameters: One non-list before d one after list-needle
150 "SELECT * FROM Foo WHERE foo = :foo AND bar IN (:bar)",
151 array('foo'=>"string", 'bar'=>array(1, 2, 3)),
152 array('foo'=>\PDO::PARAM_STR, 'bar'=>Connection::PARAM_INT_ARRAY),
153 'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?)',
154 array("string", 1, 2, 3),
155 array(\PDO::PARAM_STR, \PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT)
157 // Named parameters: One non-list after list-needle
159 "SELECT * FROM Foo WHERE bar IN (:bar) AND baz = :baz",
160 array('bar'=>array(1, 2, 3), 'baz'=>"foo"),
161 array('bar'=>Connection::PARAM_INT_ARRAY, 'baz'=>\PDO::PARAM_STR),
162 'SELECT * FROM Foo WHERE bar IN (?, ?, ?) AND baz = ?',
163 array(1, 2, 3, "foo"),
164 array(\PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_STR)
166 // Named parameters: One non-list before and one after list-needle
168 "SELECT * FROM Foo WHERE foo = :foo AND bar IN (:bar) AND baz = :baz",
169 array('bar'=>array(1, 2, 3),'foo'=>1, 'baz'=>4),
170 array('bar'=>Connection::PARAM_INT_ARRAY, 'foo'=>\PDO::PARAM_INT, 'baz'=>\PDO::PARAM_INT),
171 'SELECT * FROM Foo WHERE foo = ? AND bar IN (?, ?, ?) AND baz = ?',
172 array(1, 1, 2, 3, 4),
173 array(\PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT)
175 // Named parameters: Two lists
177 "SELECT * FROM Foo WHERE foo IN (:a, :b)",
178 array('b'=>array(4, 5),'a'=>array(1, 2, 3)),
179 array('a'=>Connection::PARAM_INT_ARRAY, 'b'=>Connection::PARAM_INT_ARRAY),
180 'SELECT * FROM Foo WHERE foo IN (?, ?, ?, ?, ?)',
181 array(1, 2, 3, 4, 5),
182 array(\PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT)
184 // Named parameters : With the same name arg type string
186 "SELECT * FROM Foo WHERE foo <> :arg AND bar = :arg",
187 array('arg'=>"Some String"),
188 array('arg'=>\PDO::PARAM_STR),
189 'SELECT * FROM Foo WHERE foo <> ? AND bar = ?',
190 array("Some String","Some String"),
191 array(\PDO::PARAM_STR,\PDO::PARAM_STR,)
193 // Named parameters : With the same name arg
195 "SELECT * FROM Foo WHERE foo IN (:arg) AND NOT bar IN (:arg)",
196 array('arg'=>array(1, 2, 3)),
197 array('arg'=>Connection::PARAM_INT_ARRAY),
198 'SELECT * FROM Foo WHERE foo IN (?, ?, ?) AND NOT bar IN (?, ?, ?)',
199 array(1, 2, 3, 1, 2, 3),
200 array(\PDO::PARAM_INT,\PDO::PARAM_INT, \PDO::PARAM_INT,\PDO::PARAM_INT,\PDO::PARAM_INT, \PDO::PARAM_INT)
203 // Named parameters : Same name, other name in between DBAL-299
205 "SELECT * FROM Foo WHERE (:foo = 2) AND (:bar = 3) AND (:foo = 2)",
206 array('foo'=>2,'bar'=>3),
207 array('foo'=>\PDO::PARAM_INT,'bar'=>\PDO::PARAM_INT),
208 'SELECT * FROM Foo WHERE (? = 2) AND (? = 3) AND (? = 2)',
210 array(\PDO::PARAM_INT, \PDO::PARAM_INT, \PDO::PARAM_INT)
212 // Named parameters : Empty "integer" array DDC-1978
214 "SELECT * FROM Foo WHERE foo IN (:foo)",
215 array('foo'=>array()),
216 array('foo'=>Connection::PARAM_INT_ARRAY),
217 'SELECT * FROM Foo WHERE foo IN (?)',
221 // Named parameters : Two empty "str" array DDC-1978
223 "SELECT * FROM Foo WHERE foo IN (:foo) OR bar IN (:bar)",
224 array('foo'=>array(), 'bar'=>array()),
225 array('foo'=>Connection::PARAM_STR_ARRAY, 'bar'=>Connection::PARAM_STR_ARRAY),
226 'SELECT * FROM Foo WHERE foo IN (?) OR bar IN (?)',
234 * @dataProvider dataExpandListParameters
238 * @param type $expectedQuery
239 * @param type $expectedParams
240 * @param type $expectedTypes
242 public function testExpandListParameters($q, $p, $t, $expectedQuery, $expectedParams, $expectedTypes)
244 list($query, $params, $types) = SQLParserUtils::expandListParameters($q, $p, $t);
246 $this->assertEquals($expectedQuery, $query, "Query was not rewritten correctly.");
247 $this->assertEquals($expectedParams, $params, "Params dont match");
248 $this->assertEquals($expectedTypes, $types, "Types dont match");