Example #1
0
 public function testCheckPass()
 {
     $supplied = array('and-id-eq-value-12');
     $criterion = new Criterion();
     $criterion->setLogic('and')->setKey('id')->setOperand('eq')->setType('value')->setValue('12');
     $filter = new Filter();
     $filter->addCriterion($criterion);
     $expected = $filter;
     $requirements = array('fields' => array('id'));
     $actual = FilterType::check($supplied, $requirements);
     // Compare actual and existing params
     $this->assertInstanceOf(get_class($filter), $actual);
     $this->assertEquals($expected, $actual);
 }
Example #2
0
 public function testCheckFiltersWithOneFilterPass()
 {
     $supplied = array('and-id-eq-value-12');
     $criterion = new Criterion();
     $criterion->setLogic('and')->setKey('id')->setOperand('eq')->setType('value')->setValue('12');
     $filterA = new Filter();
     $filterA->addCriterion($criterion);
     $expected = array($filterA);
     $requirements = array('fields' => array('id'));
     $actual = PseudoTypes::checkFilters($supplied, $requirements);
     // Compare actual and existing params
     $this->assertInternalType('array', $actual);
     $this->assertInstanceOf(get_class($filterA), $actual[0]);
     $this->assertEquals($expected, $actual);
 }
Example #3
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;
 }
Example #4
0
 /**
  * Checks if the value is of a given type and
  * passes the value the requirements specified.
  *
  * @param   array   $value          Value to be checked
  * @param   array   $requirements   Additional constraints
  * @return  Filter  Cleared value
  * @throws  InvalidDataTypeException | InvalidDataValueException
  */
 public static function check($value, array $requirements = array())
 {
     if (!isset($requirements['fields'])) {
         $error = 'allowable list of fields constraint has not been specified for a filter';
         throw new InvalidDataTypeException($error);
     }
     if (!is_array($value)) {
         $error = 'value must be a list of filters';
         throw new InvalidDataTypeException($error);
     }
     // reserve space for cleared filters
     $filter = new Filter();
     // Iterate through the list of filters and check each filter individually
     foreach ($value as $index => $criteria) {
         $criterion = self::criteriaToCriterion($criteria, $requirements, $index);
         $filter->addCriterion($criterion);
     }
     return $filter;
 }
Example #5
0
 /**
  * Builds SQL from filters (WHERE or HAVING parts)
  *
  * @param array     $filters        Array of Ucc\Data\Filter\Filter objects
  * @param array     $fieldMap       Array representing filed map
  * @param boolean   $singleTable    Marker to indicate single table queries.
  * @return  string
  */
 public static function getFilterSql($filters = array(), $fieldMap = array(), $singleTable = false)
 {
     $ret = array('paramiters' => array());
     $havingFilters = array();
     $whereFilters = array();
     $table = '';
     // Check if filters is a single filter
     if (is_a($filters, 'Ucc\\Data\\Filter\\Filter')) {
         $filters = array($filters);
     }
     foreach ($filters as $i => $filter) {
         // get Criterions
         $criterions = $filter->getCriterions();
         $havingFilter = new Data_Filter();
         $whereFilter = new Data_Filter();
         foreach ($criterions as $criterion) {
             if (isset($fieldMap[$criterion->key()])) {
                 // Get table name from field map
                 $table = $fieldMap[$criterion->key()];
                 // Check for wildecard
             } elseif (isset($fieldMap['*'])) {
                 $table = $fieldMap['*'];
             }
             if (!$singleTable || $singleTable === $table) {
                 // Allow pseudo tables 'HAVING'
                 if ($table == 'having') {
                     $havingFilter->addCriterion($criterion);
                 } else {
                     $whereFilter->addCriterion($criterion);
                 }
             }
         }
         $havingCriterions = $havingFilter->getCriterions();
         if (!empty($havingCriterions)) {
             $havingFilters[$i] = $havingFilter;
         }
         $whereFilters[$i] = $whereFilter;
     }
     $where = Filter::filtersToSqlClause($whereFilters, $fieldMap);
     $having = Filter::filtersToSqlClause($havingFilters, $fieldMap);
     $whereStatemet = $where->getStatement();
     $havingStatement = $having->getStatement();
     if (!empty($whereStatemet) && $whereStatemet != '()') {
         $ret['where'] = 'WHERE ' . $whereStatemet;
         $ret['paramiters'] = array_merge($ret['paramiters'], $where->getParameters());
     }
     if (!empty($havingStatement)) {
         $ret['having'] = 'HAVING ' . $havingStatement;
         $ret['paramiters'] = array_merge($ret['paramiters'], $having->getParameters());
     }
     return $ret;
 }
Example #6
0
 public function expandSimpleQueryProvider()
 {
     // test WHERE and SORT
     $data = array();
     $options = array();
     $query = new Query();
     $sql = 'SELECT * FROM `products` ';
     $expectedSql = 'SELECT * FROM `products` WHERE (`name` LIKE CONCAT("%", :0_filter_0, "%") COLLATE utf8_general_ci OR `name` LIKE CONCAT("%", :0_filter_1, "%") COLLATE utf8_general_ci AND `price` > :0_filter_2) GROUP BY `name`,`price` ORDER BY `name` ASC,`price` DESC';
     $expectedParams = array('0_filter_0' => 'galaxy', '0_filter_1' => '4s', '0_filter_2' => '100');
     $query->setStatement($sql);
     $expected = new Query();
     $expected->setStatement($expectedSql)->setParameters($expectedParams);
     $sort1 = new Sort();
     $sort1->setField('name');
     $sort2 = new Sort();
     $sort2->setField('price')->setDirection('DESC');
     $filterA = new Filter();
     $criterions = array(FilterType::criteriaToCriterion('and-name-inci-value-galaxy'), FilterType::criteriaToCriterion('or-name-inci-value-4s'), FilterType::criteriaToCriterion('and-price-gt-value-100'));
     $filterA->setCriterions($criterions);
     $options = array('sort' => array($sort1, $sort2), 'group' => array('name', 'price'), 'filter' => array($filterA));
     $data[] = array($query, $options, $expected);
     // test HAVING
     $query = new Query();
     $query->setStatement($sql);
     $sql = 'SELECT * FROM `products` ';
     $expectedSql = 'SELECT * FROM `products` HAVING (`name` = CAST(`price` AS CHAR) COLLATE utf8_bin OR `clicks` > `price`)';
     $expected = new Query();
     $expected->setStatement($expectedSql);
     $fieldMap = array('name' => 'having');
     $filterA = new Filter();
     $criterions = array(FilterType::criteriaToCriterion('and-name-eq-field-price'), FilterType::criteriaToCriterion('or-clicks-gt-field-price'));
     $filterA->setCriterions($criterions);
     $options = array('filter' => array($filterA));
     $data[] = array($query, $options, $expected, $fieldMap);
     // test * in field map
     $query = new Query();
     $query->setStatement($sql);
     $sql = 'SELECT * FROM `products` ';
     $expectedSql = 'SELECT * FROM `products` HAVING (`name` = CAST(`price` AS CHAR) COLLATE utf8_bin OR `clicks` > `price`)';
     $expected = new Query();
     $expected->setStatement($expectedSql);
     $fieldMap = array('*' => 'having');
     $filterA = new Filter();
     $criterions = array(FilterType::criteriaToCriterion('and-name-eq-field-price'), FilterType::criteriaToCriterion('or-clicks-gt-field-price'));
     $filterA->setCriterions($criterions);
     $options = array('filter' => array($filterA));
     $data[] = array($query, $options, $expected, $fieldMap);
     // test LIMIT and offset
     $query = new Query();
     $query->setStatement($sql);
     $sql = 'SELECT * FROM `products` ';
     $expectedSql = 'SELECT * FROM `products` WHERE (`name` = CAST(`price` AS CHAR) COLLATE utf8_bin OR `clicks` > `price`) LIMIT 10,20';
     $expected = new Query();
     $expected->setStatement($expectedSql);
     $filterA = new Filter();
     $criterions = array(FilterType::criteriaToCriterion('and-name-eq-field-price'), FilterType::criteriaToCriterion('or-clicks-gt-field-price'));
     $filterA->setCriterions($criterions);
     $options = array('filter' => array($filterA), 'limit' => 20, 'offset' => 10);
     $data[] = array($query, $options, $expected);
     // test Single Filter
     $sql = 'SELECT * FROM `orders` ';
     $querySingle = new Query();
     $querySingle->setStatement($sql);
     $expectedSql = 'SELECT * FROM `orders` WHERE (`id` > :0_filter_0 AND `id` >= :0_filter_1) LIMIT 2,100';
     $expectedParams = array('0_filter_0' => '1', '0_filter_1' => '2');
     $expected = new Query();
     $expected->setStatement($expectedSql)->setParameters($expectedParams);
     $filterSingle = new Filter();
     $criterions = array(FilterType::criteriaToCriterion('and-id-gt-value-1'), FilterType::criteriaToCriterion('and-id-ge-value-2'));
     $filterSingle->setCriterions($criterions);
     $options = array('filter' => $filterSingle, 'limit' => 100, 'offset' => 2);
     $data[] = array($querySingle, $options, $expected);
     return $data;
 }
Example #7
0
 public function testGetCriterions()
 {
     $filter = new Filter();
     $this->assertTrue(is_array($filter->getCriterions()));
 }