jcs.org
a tiny mvc framework for php using php-activerecord
1<?php
2include 'helpers/config.php';
3
4use ActiveRecord\SQLBuilder;
5use ActiveRecord\Table;
6
7class SQLBuilderTest extends DatabaseTest
8{
9 protected $table_name = 'authors';
10 protected $class_name = 'Author';
11 protected $table;
12
13 public function set_up($connection_name=null)
14 {
15 parent::set_up($connection_name);
16 $this->sql = new SQLBuilder($this->conn,$this->table_name);
17 $this->table = Table::load($this->class_name);
18 }
19
20 protected function cond_from_s($name, $values=null, $map=null)
21 {
22 return SQLBuilder::create_conditions_from_underscored_string($this->table->conn, $name, $values, $map);
23 }
24
25 public function assert_conditions($expected_sql, $values, $underscored_string, $map=null)
26 {
27 $cond = SQLBuilder::create_conditions_from_underscored_string($this->table->conn,$underscored_string,$values,$map);
28 $this->assert_sql_has($expected_sql,array_shift($cond));
29
30 if ($values)
31 $this->assert_equals(array_values(array_filter($values,function($s) { return $s !== null; })),array_values($cond));
32 else
33 $this->assert_equals(array(),$cond);
34 }
35
36 /**
37 * @expectedException ActiveRecord\ActiveRecordException
38 */
39 public function test_no_connection()
40 {
41 new SQLBuilder(null,'authors');
42 }
43
44 public function test_nothing()
45 {
46 $this->assert_equals('SELECT * FROM authors',(string)$this->sql);
47 }
48
49 public function test_where_with_array()
50 {
51 $this->sql->where("id=? AND name IN(?)",1,array('Tito','Mexican'));
52 $this->assert_sql_has("SELECT * FROM authors WHERE id=? AND name IN(?,?)",(string)$this->sql);
53 $this->assert_equals(array(1,'Tito','Mexican'),$this->sql->get_where_values());
54 }
55
56 public function test_where_with_hash()
57 {
58 $this->sql->where(array('id' => 1, 'name' => 'Tito'));
59 $this->assert_sql_has("SELECT * FROM authors WHERE id=? AND name=?",(string)$this->sql);
60 $this->assert_equals(array(1,'Tito'),$this->sql->get_where_values());
61 }
62
63 public function test_where_with_hash_and_array()
64 {
65 $this->sql->where(array('id' => 1, 'name' => array('Tito','Mexican')));
66 $this->assert_sql_has("SELECT * FROM authors WHERE id=? AND name IN(?,?)",(string)$this->sql);
67 $this->assert_equals(array(1,'Tito','Mexican'),$this->sql->get_where_values());
68 }
69
70 public function test_gh134_where_with_hash_and_null()
71 {
72 $this->sql->where(array('id' => 1, 'name' => null));
73 $this->assert_sql_has("SELECT * FROM authors WHERE id=? AND name IS ?",(string)$this->sql);
74 $this->assert_equals(array(1, null),$this->sql->get_where_values());
75 }
76
77 public function test_where_with_null()
78 {
79 $this->sql->where(null);
80 $this->assert_equals('SELECT * FROM authors',(string)$this->sql);
81 }
82
83 public function test_where_with_no_args()
84 {
85 $this->sql->where();
86 $this->assert_equals('SELECT * FROM authors',(string)$this->sql);
87 }
88
89 public function test_order()
90 {
91 $this->sql->order('name');
92 $this->assert_equals('SELECT * FROM authors ORDER BY name',(string)$this->sql);
93 }
94
95 public function test_limit()
96 {
97 $this->sql->limit(10)->offset(1);
98 $this->assert_equals($this->conn->limit('SELECT * FROM authors',1,10),(string)$this->sql);
99 }
100
101 public function test_select()
102 {
103 $this->sql->select('id,name');
104 $this->assert_equals('SELECT id,name FROM authors',(string)$this->sql);
105 }
106
107 public function test_joins()
108 {
109 $join = 'inner join books on(authors.id=books.author_id)';
110 $this->sql->joins($join);
111 $this->assert_equals("SELECT * FROM authors $join",(string)$this->sql);
112 }
113
114 public function test_group()
115 {
116 $this->sql->group('name');
117 $this->assert_equals('SELECT * FROM authors GROUP BY name',(string)$this->sql);
118 }
119
120 public function test_having()
121 {
122 $this->sql->having("created_at > '2009-01-01'");
123 $this->assert_equals("SELECT * FROM authors HAVING created_at > '2009-01-01'", (string)$this->sql);
124 }
125
126 public function test_all_clauses_after_where_should_be_correctly_ordered()
127 {
128 $this->sql->limit(10)->offset(1);
129 $this->sql->having("created_at > '2009-01-01'");
130 $this->sql->order('name');
131 $this->sql->group('name');
132 $this->sql->where(array('id' => 1));
133 $this->assert_sql_has($this->conn->limit("SELECT * FROM authors WHERE id=? GROUP BY name HAVING created_at > '2009-01-01' ORDER BY name",1,10), (string)$this->sql);
134 }
135
136 /**
137 * @expectedException ActiveRecord\ActiveRecordException
138 */
139 public function test_insert_requires_hash()
140 {
141 $this->sql->insert(array(1));
142 }
143
144 public function test_insert()
145 {
146 $this->sql->insert(array('id' => 1, 'name' => 'Tito'));
147 $this->assert_sql_has("INSERT INTO authors(id,name) VALUES(?,?)",(string)$this->sql);
148 }
149
150 public function test_insert_with_null()
151 {
152 $this->sql->insert(array('id' => 1, 'name' => null));
153 $this->assert_sql_has("INSERT INTO authors(id,name) VALUES(?,?)",$this->sql->to_s());
154 }
155
156 public function test_update_with_hash()
157 {
158 $this->sql->update(array('id' => 1, 'name' => 'Tito'))->where('id=1 AND name IN(?)',array('Tito','Mexican'));
159 $this->assert_sql_has("UPDATE authors SET id=?, name=? WHERE id=1 AND name IN(?,?)",(string)$this->sql);
160 $this->assert_equals(array(1,'Tito','Tito','Mexican'),$this->sql->bind_values());
161 }
162
163 public function test_update_with_limit_and_order()
164 {
165 if (!$this->conn->accepts_limit_and_order_for_update_and_delete())
166 $this->mark_test_skipped('Only MySQL & Sqlite accept limit/order with UPDATE operation');
167
168 $this->sql->update(array('id' => 1))->order('name asc')->limit(1);
169 $this->assert_sql_has("UPDATE authors SET id=? ORDER BY name asc LIMIT 1", $this->sql->to_s());
170 }
171
172 public function test_update_with_string()
173 {
174 $this->sql->update("name='Bob'");
175 $this->assert_sql_has("UPDATE authors SET name='Bob'", $this->sql->to_s());
176 }
177
178 public function test_update_with_null()
179 {
180 $this->sql->update(array('id' => 1, 'name' => null))->where('id=1');
181 $this->assert_sql_has("UPDATE authors SET id=?, name=? WHERE id=1",$this->sql->to_s());
182 }
183
184 public function test_delete()
185 {
186 $this->sql->delete();
187 $this->assert_equals('DELETE FROM authors',$this->sql->to_s());
188 }
189
190 public function test_delete_with_where()
191 {
192 $this->sql->delete('id=? or name in(?)',1,array('Tito','Mexican'));
193 $this->assert_equals('DELETE FROM authors WHERE id=? or name in(?,?)',$this->sql->to_s());
194 $this->assert_equals(array(1,'Tito','Mexican'),$this->sql->bind_values());
195 }
196
197 public function test_delete_with_hash()
198 {
199 $this->sql->delete(array('id' => 1, 'name' => array('Tito','Mexican')));
200 $this->assert_sql_has("DELETE FROM authors WHERE id=? AND name IN(?,?)",$this->sql->to_s());
201 $this->assert_equals(array(1,'Tito','Mexican'),$this->sql->get_where_values());
202 }
203
204 public function test_delete_with_limit_and_order()
205 {
206 if (!$this->conn->accepts_limit_and_order_for_update_and_delete())
207 $this->mark_test_skipped('Only MySQL & Sqlite accept limit/order with DELETE operation');
208
209 $this->sql->delete(array('id' => 1))->order('name asc')->limit(1);
210 $this->assert_sql_has("DELETE FROM authors WHERE id=? ORDER BY name asc LIMIT 1",$this->sql->to_s());
211 }
212
213 public function test_reverse_order()
214 {
215 $this->assert_equals('id ASC, name DESC', SQLBuilder::reverse_order('id DESC, name ASC'));
216 $this->assert_equals('id ASC, name DESC , zzz ASC', SQLBuilder::reverse_order('id DESC, name ASC , zzz DESC'));
217 $this->assert_equals('id DESC, name DESC', SQLBuilder::reverse_order('id, name'));
218 $this->assert_equals('id DESC', SQLBuilder::reverse_order('id'));
219 $this->assert_equals('', SQLBuilder::reverse_order(''));
220 $this->assert_equals(' ', SQLBuilder::reverse_order(' '));
221 $this->assert_equals(null, SQLBuilder::reverse_order(null));
222 }
223
224 public function test_create_conditions_from_underscored_string()
225 {
226 $this->assert_conditions('id=? AND name=? OR z=?',array(1,'Tito','X'),'id_and_name_or_z');
227 $this->assert_conditions('id=?',array(1),'id');
228 $this->assert_conditions('id IN(?)',array(array(1,2)),'id');
229 }
230
231 public function test_create_conditions_from_underscored_string_with_nulls()
232 {
233 $this->assert_conditions('id=? AND name IS NULL',array(1,null),'id_and_name');
234 }
235
236 public function test_create_conditions_from_underscored_string_with_missing_args()
237 {
238 $this->assert_conditions('id=? AND name IS NULL OR z IS NULL',array(1,null),'id_and_name_or_z');
239 $this->assert_conditions('id IS NULL',null,'id');
240 }
241
242 public function test_create_conditions_from_underscored_string_with_blank()
243 {
244 $this->assert_conditions('id=? AND name IS NULL OR z=?',array(1,null,''),'id_and_name_or_z');
245 }
246
247 public function test_create_conditions_from_underscored_string_invalid()
248 {
249 $this->assert_equals(null,$this->cond_from_s(''));
250 $this->assert_equals(null,$this->cond_from_s(null));
251 }
252
253 public function test_create_conditions_from_underscored_string_with_mapped_columns()
254 {
255 $this->assert_conditions('id=? AND name=?',array(1,'Tito'),'id_and_my_name',array('my_name' => 'name'));
256 }
257
258 public function test_create_hash_from_underscored_string()
259 {
260 $values = array(1,'Tito');
261 $hash = SQLBuilder::create_hash_from_underscored_string('id_and_my_name',$values);
262 $this->assert_equals(array('id' => 1, 'my_name' => 'Tito'),$hash);
263 }
264
265 public function test_create_hash_from_underscored_string_with_mapped_columns()
266 {
267 $values = array(1,'Tito');
268 $map = array('my_name' => 'name');
269 $hash = SQLBuilder::create_hash_from_underscored_string('id_and_my_name',$values,$map);
270 $this->assert_equals(array('id' => 1, 'name' => 'Tito'),$hash);
271 }
272
273 public function test_where_with_joins_prepends_table_name_to_fields()
274 {
275 $joins = 'INNER JOIN books ON (books.id = authors.id)';
276 // joins needs to be called prior to where
277 $this->sql->joins($joins);
278 $this->sql->where(array('id' => 1, 'name' => 'Tito'));
279
280 $this->assert_sql_has("SELECT * FROM authors $joins WHERE authors.id=? AND authors.name=?",(string)$this->sql);
281 }
282};
283?>