Beispiel #1
0
 public function testRemoveLogicFromStatement()
 {
     $clause = new Clause();
     $clause->setStatement('AND `loo` IS NOT NULL');
     $statement = $clause->removeLogicFromStatement();
     $this->assertEquals('`loo` IS NOT NULL', $clause->getStatement());
     $this->assertEquals('`loo` IS NOT NULL', $clause->statement());
     $clause = new Clause();
     $clause->setStatement('OR `loo` IS NOT NULL');
     $statement = $clause->removeLogicFromStatement();
     $this->assertEquals('`loo` IS NOT NULL', $clause->getStatement());
     $this->assertEquals('`loo` IS NOT NULL', $clause->statement());
 }
Beispiel #2
0
 public function filtersToSqlProvider()
 {
     $filterA = new Data_Filter();
     $criterions = array(FilterType::criteriaToCriterion('and-isActive-bool-field-true'), FilterType::criteriaToCriterion('or-size-gt-field-volume'), FilterType::criteriaToCriterion('and-bar-re-value-^moo$'));
     $filterA->setCriterions($criterions);
     $filterB = new Data_Filter();
     $criterions = array(FilterType::criteriaToCriterion('and-age-in-value-18,19,20,88'), FilterType::criteriaToCriterion('and-city-nbegins-value-London'));
     $filterB->setCriterions($criterions);
     $filterC = new Data_Filter();
     $criterions = array(FilterType::criteriaToCriterion('and-foo-nei-field-bar'), FilterType::criteriaToCriterion('or-loo-ne-value-7'));
     $filterC->setCriterions($criterions)->setLogic('or');
     $sql = '((`isActive` IS NOT NULL AND `isActive` != "") OR `size` > `volume` AND `bar` REGEXP :0_filter_2) AND (`age` IN (:1_filter_0_0 COLLATE utf8_bin, :1_filter_0_1 COLLATE utf8_bin, :1_filter_0_2 COLLATE utf8_bin, :1_filter_0_3 COLLATE utf8_bin) AND `city` NOT LIKE CONCAT(:1_filter_1, "%") COLLATE utf8_bin) OR (`foo` != CAST(`bar` AS CHAR) COLLATE utf8_general_ci OR `loo` != CAST(:2_filter_1 AS CHAR) COLLATE utf8_bin)';
     $params = array('0_filter_2' => '^moo$', '1_filter_0_0' => '18', '1_filter_0_1' => '19', '1_filter_0_2' => '20', '1_filter_0_3' => '88', '1_filter_1' => 'London', '2_filter_1' => '7');
     $clause = new Clause();
     $clause->setStatement($sql)->setParameters($params);
     $data[] = array(array($filterA, $filterB, $filterC), $clause);
     return $data;
 }
Beispiel #3
0
 /**
  * Turns array of Ucc\Data\Filter\Filter objects (also known as criteria) into SQL
  *
  * @param array     $filers     Array of Ucc\Data\Filter\Filter objects
  * @param array     $fieldMap   Array of field names and tables
  * @param string    $namespace  Prefix for query placeholders
  * @return Ucc\Data\Filter\Clause\Clause
  */
 public static function filtersToSqlClause(array $filters, $fieldMap = array())
 {
     // Default return values
     $sqlClause = new Clause();
     $sql = '';
     $params = array();
     foreach ($filters as $i => $filter) {
         $clause = self::filterToSqlClause($filter, $fieldMap = array(), $i . '_filter');
         if (!empty($sql)) {
             $sql .= ' ' . strtoupper($filter->getLogic()) . ' ';
         }
         $sql .= '(' . $clause->getStatement() . ')';
         // Add params
         $params[] = $clause->getParameters();
         $sqlClause->setParameters($clause->getParameters());
     }
     $sqlClause->setStatement($sql);
     return $sqlClause;
 }
Beispiel #4
0
 /**
  * Turns Criterion into IN Sql Clause
  *
  * @param   Criterion   $criterion      Criterion to process
  * @param   string      $placeHolder    Placeholder for parameter name
  * @param   array       $fieldMap       Array representing field map
  */
 public static function criterionToInClause(Criterion $criterion, $placeHolder = 'filter_0', $fieldMap = array())
 {
     // Create local operand and collate
     $op = false;
     $collate = false;
     $clause = new Clause();
     switch ($criterion->op()) {
         case 'in':
             $op = 'IN';
             $collate = 'utf8_bin';
             break;
         case 'nin':
             $op = 'NOT IN';
             $collate = 'utf8_bin';
             break;
         case 'ini':
             $op = 'IN';
             $collate = 'utf8_general_ci';
             break;
         case 'nini':
             $op = 'NOT IN';
             $collate = 'utf8_general_ci';
             break;
     }
     if ($op && $collate) {
         if ($criterion->type() == Criterion::CRITERION_TYPE_VALUE) {
             // Values should be in a comma separated list.
             $values = explode(',', $criterion->value());
             // We must add a parameter and a placeholder for each value.
             $placeHolders = array();
             foreach ($values as $key => $value) {
                 $clause->setParameter($placeHolder . '_' . $key, $value);
                 $placeHolders[] = ':' . $placeHolder . '_' . $key . ' COLLATE ' . $collate;
             }
             // The comparand for this operation is the list of values.
             $comparand = '(' . implode(', ', $placeHolders) . ')';
         } elseif ($criterion->type() == Criterion::CRITERION_TYPE_FIELD) {
             // Field names should be in a comma separated list.
             $fList = explode(',', $criterion->value());
             foreach (array_keys($fList) as $key) {
                 $fList[$key] = self::getSafeFieldName($fList[$key], $fieldMap) . ' COLLATE ' . $collate;
             }
             // The comparand for this operation is the list of field names.
             $comparand = '(' . implode(', ', $fList) . ')';
         }
         // Escape, quote and qualify the field name for security.
         $field = self::getSafeFieldName($criterion->key(), $fieldMap);
         // Build the final clause.
         // Use end wild-card character for "begins with".
         $clause->setStatement(self::addLogic($criterion) . ' ' . $field . ' ' . $op . ' ' . $comparand);
     }
     return $clause;
 }
Beispiel #5
0
 public function inNiniCriterionsProvider()
 {
     $inNiniValueAndCriterion = new Criterion();
     $inNiniValueAndCriterion->setLogic('and')->setKey('foo')->setOperand('nini')->setType('value')->setValue('abc,def,xyz,123');
     $inNiniValueAndClause = new Clause();
     $inNiniValueAndClause->setStatement('AND `foo` NOT IN (:filter_0_0 COLLATE utf8_general_ci, :filter_0_1 COLLATE utf8_general_ci, :filter_0_2 COLLATE utf8_general_ci, :filter_0_3 COLLATE utf8_general_ci)')->setParameters(array('filter_0_0' => 'abc', 'filter_0_1' => 'def', 'filter_0_2' => 'xyz', 'filter_0_3' => '123'));
     $inNiniValueOrCriterion = new Criterion();
     $inNiniValueOrCriterion->setLogic('or')->setKey('foo')->setOperand('nini')->setType('value')->setValue('abc,def,xyz,123');
     $inNiniValueOrClause = new Clause();
     $inNiniValueOrClause->setStatement('OR `foo` NOT IN (:filter_0_0 COLLATE utf8_general_ci, :filter_0_1 COLLATE utf8_general_ci, :filter_0_2 COLLATE utf8_general_ci, :filter_0_3 COLLATE utf8_general_ci)')->setParameters(array('filter_0_0' => 'abc', 'filter_0_1' => 'def', 'filter_0_2' => 'xyz', 'filter_0_3' => '123'));
     $inNiniTypeAndCriterion = new Criterion();
     $inNiniTypeAndCriterion->setLogic('and')->setKey('foo')->setOperand('nini')->setType('field')->setValue('bar,loo,foo');
     $inNiniTypeAndClause = new Clause();
     $inNiniTypeAndClause->setStatement('AND `foo` NOT IN (`bar` COLLATE utf8_general_ci, `loo` COLLATE utf8_general_ci, `foo` COLLATE utf8_general_ci)');
     $inNiniTypeOrCriterion = new Criterion();
     $inNiniTypeOrCriterion->setLogic('or')->setKey('foo')->setOperand('nini')->setType('field')->setValue('bar,loo,foo');
     $inNiniTypeOrClause = new Clause();
     $inNiniTypeOrClause->setStatement('OR `foo` NOT IN (`bar` COLLATE utf8_general_ci, `loo` COLLATE utf8_general_ci, `foo` COLLATE utf8_general_ci)');
     return array(array($inNiniValueAndCriterion, $inNiniValueAndClause), array($inNiniValueOrCriterion, $inNiniValueOrClause), array($inNiniTypeAndCriterion, $inNiniTypeAndClause), array($inNiniTypeOrCriterion, $inNiniTypeOrClause));
 }