1 <?php
2
3 /**
4 * AfMysqlDbHelper class file
5 *
6 * @author Keith Burton <kburton@kappasoft.net>
7 * @package advancedfilters.dbhelpers
8 */
9
10 /**
11 * A generic database helper for MySQL.
12 */
13 class AfMysqlDbHelper extends AfBaseDbHelper
14 {
15 /**
16 * Alters a database expression so that strings are converted to decimals.
17 *
18 * Values that can't be converted are set to the specific non-numeric
19 * result value, which should be an integer or null.
20 *
21 * @param string $dbExpression the expression to update.
22 * @param integer $numDigits the maximum number of digits that the decimal
23 * number should contain.
24 * @param integer $decimalPlaces the number of decimal places that the
25 * resulting decimal should have.
26 * @param integer $nonNumericResultValue the integer value to use if an
27 * expression isn't recognised as a number. This can also be null.
28 * @return string the updated expression.
29 */
30 public function convertExpressionToDecimal($dbExpression, $numDigits,
31 $decimalPlaces, $nonNumericResultValue)
32 {
33 $numDigits = (int)$numDigits;
34 $decimalPlaces = (int)$decimalPlaces;
35 $nonNumericResultValue = $nonNumericResultValue === null
36 ? 'NULL' : (int)$nonNumericResultValue;
37
38 // If the required result for non-numeric expressions is zero, we can
39 // use MySQL's default behavior
40 if ($nonNumericResultValue === 0)
41 {
42 return "CAST(($dbExpression) "
43 . "AS DECIMAL($numDigits, $decimalPlaces))";
44 }
45
46 // Otherwise we should check the value looks numeric and cast only if
47 // it does, otherwise using the non numeric result value
48 return "IF(($dbExpression) REGEXP '^-?[0-9]*.?[0-9]+$', "
49 . "CAST(($dbExpression) AS DECIMAL($numDigits, $decimalPlaces))"
50 . ", $nonNumericResultValue)";
51 }
52
53 /**
54 * Checks regular expression syntax using the REGEXP keyword. Documentation
55 * for this syntax can be found on the MySQL website.
56 *
57 * This is checked against the database, as invalid syntax could otherwise
58 * cause an exception to be thrown when the data is fetched.
59 *
60 * @param CDbConnection $dbConnection the database connection object.
61 * @param string $regex the pattern to test.
62 * @return boolean true if the syntax is valid, false if not.
63 */
64 public function checkRegex($dbConnection, $regex)
65 {
66 $query = 'SELECT "1" REGEXP :regex';
67
68 try
69 {
70 $dbConnection->createCommand($query)
71 ->query(array(':regex'=>$regex));
72 }
73 catch (Exception $ex)
74 {
75 return false;
76 }
77
78 return true;
79 }
80
81 /**
82 * Adds a regex condition using the REGEXP keyword. Documentation for this
83 * syntax can be found on the MySQL website.
84 *
85 * @param CDbCriteria $criteria the criteria to update.
86 * @param string $columnExpression the column to search, or a valid
87 * expression.
88 * @param string $regex the pattern to match against.
89 * @param boolean $invertLogic whether the logic should be inverted to
90 * return the opposite query results.
91 */
92 public function addRegexCondition($criteria, $columnExpression,
93 $regex, $invertLogic)
94 {
95 $criteria->addSearchCondition($columnExpression,
96 $regex, false, 'AND', ($invertLogic ? 'NOT REGEXP' : 'REGEXP'));
97 }
98 }
99