wbond / flourish (http://flourishlib.com/)
Flourish is a PHP unframework — a general-purpose, object-oriented library. It's architecture is modular and thus not strictly MVC. It focuses on being secure, broadly compatible, portable, well documented and easy to use.
| commit 733: | 718fe2bd60fe |
| parent 732: | 6f3e97513957 |
| branch: | trunk |
[svn r734] Fixed ticket #347 - fRecordSet::build() can now compare columns by using the =:, !=:, <:, <=:, >:, >=: operators
Changed (Δ3.5 KB):
raw changeset »
classes/fORMDatabase.php (66 lines added, 32 lines removed)
classes/fRecordSet.php (20 lines added, 2 lines removed)
Up to file-list classes/fORMDatabase.php:
10 |
10 |
* @package Flourish |
11 |
11 |
* @link http://flourishlib.com/fORMDatabase |
12 |
12 |
* |
13 |
* @version 1.0.0b1 |
|
13 |
* @version 1.0.0b19 |
|
14 |
* @changes 1.0.0b19 Added the ability to compare columns with the `=:`, `!:`, `<:`, `<=:`, `>:` and `>=:` operators [wb, 2009-12-08] |
|
14 |
15 |
* @changes 1.0.0b18 Fixed a bug affecting where conditions with columns that are not null but have a default value [wb, 2009-11-03] |
15 |
16 |
* @changes 1.0.0b17 Added support for multiple databases [wb, 2009-10-28] |
16 |
17 |
* @changes 1.0.0b16 Internal Backwards Compatibility Break - Renamed methods and significantly changed parameters and functionality for SQL statements to use value placeholders, identifier escaping and to handle schemas [wb, 2009-10-22] |
| … | … | @@ -133,9 +134,9 @@ class fORMDatabase |
133 |
134 |
} |
134 |
135 |
|
135 |
136 |
list($table, $column) = self::getTableAndColumn($schema, $table, $column); |
136 |
||
137 |
if ($placeholder === NULL) { |
|
138 |
|
|
137 |
||
138 |
if ($placeholder === NULL && !in_array($operator, array('=:', '!=:', '<:', '<=:', '>:', '>=:'))) { |
|
139 |
$placeholder = $schema->getColumnInfo($table, $column, 'placeholder'); |
|
139 |
140 |
} |
140 |
141 |
|
141 |
142 |
// More than one value |
| … | … | @@ -225,6 +226,29 @@ class fORMDatabase |
225 |
226 |
} |
226 |
227 |
|
227 |
228 |
switch ($operator) { |
229 |
case '!:': |
|
230 |
$operator = '<>:'; |
|
231 |
case '=:': |
|
232 |
case '<:': |
|
233 |
case '<=:': |
|
234 |
case '>:': |
|
235 |
case '>=:': |
|
236 |
$params[0] .= $escaped_column . ' '; |
|
237 |
$params[0] .= substr($operator, 0, -1) . ' '; |
|
238 |
||
239 |
// If the column to match is a function, split the function |
|
240 |
// name off so we can escape the column name |
|
241 |
$prefix = ''; |
|
242 |
$suffix = ''; |
|
243 |
if (preg_match('#^([^(]+\()\s*([^\s]+)\s*(\))$#D', $value, $parts)) { |
|
244 |
$prefix = $parts[1]; |
|
245 |
$value = $parts[2]; |
|
246 |
$suffix = $parts[3]; |
|
247 |
} |
|
248 |
||
249 |
$params[0] .= $prefix . $db->escape('%r', (strpos($value, '.') === FALSE) ? $table . '.' . $value : $value) . $suffix; |
|
250 |
break; |
|
251 |
||
228 |
252 |
case '=': |
229 |
253 |
if ($value === NULL) { |
230 |
254 |
$operator = 'IS'; |
| … | … | @@ -299,13 +323,16 @@ class fORMDatabase |
299 |
323 |
} |
300 |
324 |
|
301 |
325 |
// Splits the operator off of the end of the expression |
302 |
if (in_array(substr($expression, - |
|
326 |
if (in_array(substr($expression, -3), array('!=:', '>=:', '<=:', '<>:'))) { |
|
327 |
$operator = strtr( |
|
328 |
substr($expression, -3), |
|
329 |
array('<>:' => '!:', '!=:' => '!:') |
|
330 |
); |
|
331 |
$expression = substr($expression, 0, -3); |
|
332 |
} elseif (in_array(substr($expression, -2), array('<=', '>=', '!=', '<>', '=:', '!:', '<:', '>:'))) { |
|
303 |
333 |
$operator = strtr( |
304 |
334 |
substr($expression, -2), |
305 |
array( |
|
306 |
'<>' => '!', |
|
307 |
'!=' => '!' |
|
308 |
) |
|
335 |
array('<>' => '!', '!=' => '!') |
|
309 |
336 |
); |
310 |
337 |
$expression = substr($expression, 0, -2); |
311 |
338 |
} else { |
| … | … | @@ -463,13 +490,16 @@ class fORMDatabase |
463 |
490 |
$params[0] .= ' AND '; |
464 |
491 |
} |
465 |
492 |
|
466 |
if (in_array(substr($column, - |
|
493 |
if (in_array(substr($column, -3), array('!=:', '>=:', '<=:', '<>:'))) { |
|
494 |
$operator = strtr( |
|
495 |
substr($column, -3), |
|
496 |
array('<>:' => '!:', '!=:' => '!:') |
|
497 |
); |
|
498 |
$column = substr($column, 0, -3); |
|
499 |
} elseif (in_array(substr($column, -2), array('<=', '>=', '!=', '<>', '!~', '&~', '><', '=:', '!:', '<:', '>:'))) { |
|
467 |
500 |
$operator = strtr( |
468 |
501 |
substr($column, -2), |
469 |
array( |
|
470 |
'<>' => '!', |
|
471 |
'!=' => '!' |
|
472 |
) |
|
502 |
array('<>' => '!', '!=' => '!') |
|
473 |
503 |
); |
474 |
504 |
$column = substr($column, 0, -2); |
475 |
505 |
} else { |
| … | … | @@ -501,13 +531,16 @@ class fORMDatabase |
501 |
531 |
$operators = array(); |
502 |
532 |
|
503 |
533 |
foreach ($columns as &$_column) { |
504 |
if (in_array(substr($_column, - |
|
534 |
if (in_array(substr($_column, -3), array('!=:', '>=:', '<=:', '<>:'))) { |
|
535 |
$operators[] = strtr( |
|
536 |
substr($_column, -3), |
|
537 |
array('<>:' => '!:', '!=:' => '!:') |
|
538 |
); |
|
539 |
$_column = substr($_column, 0, -3); |
|
540 |
} elseif (in_array(substr($_column, -2), array('<=', '>=', '!=', '<>', '!~', '&~', '=:', '!:', '<:', '>:'))) { |
|
505 |
541 |
$operators[] = strtr( |
506 |
542 |
substr($_column, -2), |
507 |
array( |
|
508 |
'<>' => '!', |
|
509 |
'!=' => '!' |
|
510 |
) |
|
543 |
array('<>' => '!', '!=' => '!') |
|
511 |
544 |
); |
512 |
545 |
$_column = substr($_column, 0, -2); |
513 |
546 |
} elseif (!ctype_alnum(substr($_column, -1))) { |
| … | … | @@ -517,17 +550,17 @@ class fORMDatabase |
517 |
550 |
} |
518 |
551 |
$operators[] = $operator; |
519 |
552 |
|
520 |
// Make sure every column is qualified by a table name |
|
521 |
$new_columns = array(); |
|
522 |
foreach ($columns as $column) { |
|
523 |
if (strpos($column, '.') === FALSE) { |
|
524 |
|
|
553 |
if (sizeof($operators) == 1) { |
|
554 |
||
555 |
// Make sure every column is qualified by a table name |
|
556 |
$new_columns = array(); |
|
557 |
foreach ($columns as $column) { |
|
558 |
if (strpos($column, '.') === FALSE) { |
|
559 |
$column = $table . '.' . $column; |
|
560 |
} |
|
561 |
$new_columns[] = $column; |
|
525 |
562 |
} |
526 |
$new_columns[] = $column; |
|
527 |
} |
|
528 |
$columns = $new_columns; |
|
529 |
||
530 |
|
|
563 |
$columns = $new_columns; |
|
531 |
564 |
|
532 |
565 |
// Handle fuzzy searches |
533 |
566 |
if ($operator == '~') { |
| … | … | @@ -1216,9 +1249,10 @@ class fORMDatabase |
1216 |
1249 |
{ |
1217 |
1250 |
$having_conditions = array(); |
1218 |
1251 |
|
1219 |
foreach ($where_conditions as $column => $value) |
|
1220 |
{ |
|
1221 |
|
|
1252 |
foreach ($where_conditions as $column => $value) { |
|
1253 |
$column_has_aggregate = preg_match('#^(count\(|max\(|avg\(|min\(|sum\()#i', $column); |
|
1254 |
$is_column_compare_with_aggregate = substr($column, -1) == ':' && preg_match('#^(count\(|max\(|avg\(|min\(|sum\()#i', $value); |
|
1255 |
if ($column_has_aggregate || $is_column_compare_with_aggregate) { |
|
1222 |
1256 |
$having_conditions[$column] = $value; |
1223 |
1257 |
unset($where_conditions[$column]); |
1224 |
1258 |
} |
Up to file-list classes/fRecordSet.php:
9 |
9 |
* @package Flourish |
10 |
10 |
* @link http://flourishlib.com/fRecordSet |
11 |
11 |
* |
12 |
* @version 1.0.0b3 |
|
12 |
* @version 1.0.0b31 |
|
13 |
* @changes 1.0.0b31 Added the ability to compare columns in ::build() with the `=:`, `!:`, `<:`, `<=:`, `>:` and `>=:` operators [wb, 2009-12-08] |
|
13 |
14 |
* @changes 1.0.0b30 Fixed a bug affecting where conditions with columns that are not null but have a default value [wb, 2009-11-03] |
14 |
15 |
* @changes 1.0.0b29 Updated code for the new fORMDatabase and fORMSchema APIs [wb, 2009-10-28] |
15 |
16 |
* @changes 1.0.0b28 Fixed ::prebuild() and ::precount() to work across all databases, changed SQL statements to use value placeholders, identifier escaping and schema support [wb, 2009-10-22] |
| … | … | @@ -66,6 +67,14 @@ class fRecordSet implements Iterator, Co |
66 |
67 |
* 'column<=' => VALUE // column <= VALUE |
67 |
68 |
* 'column>' => VALUE // column > VALUE |
68 |
69 |
* 'column>=' => VALUE // column >= VALUE |
70 |
* 'column=:' => 'other_column' // column = other_column |
|
71 |
* 'column!:' => 'other_column' // column <> other_column |
|
72 |
* 'column!=:' => 'other_column' // column <> other_column |
|
73 |
* 'column<>:' => 'other_column' // column <> other_column |
|
74 |
* 'column<:' => 'other_column' // column < other_column |
|
75 |
* 'column<=:' => 'other_column' // column <= other_column |
|
76 |
* 'column>:' => 'other_column' // column > other_column |
|
77 |
* 'column>=:' => 'other_column' // column >= other_column |
|
69 |
78 |
* 'column=' => array(VALUE, VALUE2, ... ) // column IN (VALUE, VALUE2, ... ) |
70 |
79 |
* 'column!' => array(VALUE, VALUE2, ... ) // column NOT IN (VALUE, VALUE2, ... ) |
71 |
80 |
* 'column!=' => array(VALUE, VALUE2, ... ) // column NOT IN (VALUE, VALUE2, ... ) |
| … | … | @@ -113,7 +122,8 @@ class fRecordSet implements Iterator, Co |
113 |
122 |
* |
114 |
123 |
* In addition to using plain column names for where conditions, it is also |
115 |
124 |
* possible to pass an aggregate function wrapped around a column in place |
116 |
* of a column name, but only for certain comparison types |
|
125 |
* of a column name, but only for certain comparison types. //Note that for |
|
126 |
* column comparisons, the function may be placed on either column or both.// |
|
117 |
127 |
* |
118 |
128 |
* {{{ |
119 |
129 |
* 'function(column)=' => VALUE, // function(column) = VALUE |
| … | … | @@ -126,6 +136,14 @@ class fRecordSet implements Iterator, Co |
126 |
136 |
* 'function(column)<=' => VALUE // function(column) <= VALUE |
127 |
137 |
* 'function(column)>' => VALUE // function(column) > VALUE |
128 |
138 |
* 'function(column)>=' => VALUE // function(column) >= VALUE |
139 |
* 'function(column)=:' => 'other_column' // function(column) = other_column |
|
140 |
* 'function(column)!:' => 'other_column' // function(column) <> other_column |
|
141 |
* 'function(column)!=:' => 'other_column' // function(column) <> other_column |
|
142 |
* 'function(column)<>:' => 'other_column' // function(column) <> other_column |
|
143 |
* 'function(column)<:' => 'other_column' // function(column) < other_column |
|
144 |
* 'function(column)<=:' => 'other_column' // function(column) <= other_column |
|
145 |
* 'function(column)>:' => 'other_column' // function(column) > other_column |
|
146 |
* 'function(column)>=:' => 'other_column' // function(column) >= other_column |
|
129 |
147 |
* 'function(column)=' => array(VALUE, VALUE2, ... ) // function(column) IN (VALUE, VALUE2, ... ) |
130 |
148 |
* 'function(column)!' => array(VALUE, VALUE2, ... ) // function(column) NOT IN (VALUE, VALUE2, ... ) |
131 |
149 |
* 'function(column)!=' => array(VALUE, VALUE2, ... ) // function(column) NOT IN (VALUE, VALUE2, ... ) |