예제 #1
0
 /**
  * Extend SQL query with segment expressions
  * 
  * @param string select clause
  * @param array of table names (without prefix)
  * @param string where clause
  * @param string (optional) order by clause
  * @param string (optional) group by clause
  * @return string entire select query
  */
 public function getSelectQuery($select, $from, $where = false, $bind = array(), $orderBy = false, $groupBy = false)
 {
     $joinWithSubSelect = false;
     if (!is_array($from)) {
         $from = array($from);
     }
     if (!$this->isEmpty()) {
         $this->segment->parseSubExpressionsIntoSqlExpressions($from);
         $joins = $this->generateJoins($from);
         $from = $joins['sql'];
         $joinWithSubSelect = $joins['joinWithSubSelect'];
         $segmentSql = $this->segment->getSql();
         $segmentWhere = $segmentSql['where'];
         if (!empty($segmentWhere)) {
             if (!empty($where)) {
                 $where = "( {$where} )\n\t\t\t\tAND\n\t\t\t\t({$segmentWhere})";
             } else {
                 $where = $segmentWhere;
             }
         }
         $bind = array_merge($bind, $segmentSql['bind']);
     } else {
         $joins = $this->generateJoins($from);
         $from = $joins['sql'];
         $joinWithSubSelect = $joins['joinWithSubSelect'];
     }
     if ($joinWithSubSelect) {
         $sql = $this->buildWrappedSelectQuery($select, $from, $where, $orderBy, $groupBy);
     } else {
         $sql = $this->buildSelectQuery($select, $from, $where, $orderBy, $groupBy);
     }
     $return = array('sql' => $sql, 'bind' => $bind);
     //var_dump($return);
     return $return;
 }
예제 #2
0
 public function getSql()
 {
     if ($this->isEmpty()) {
         return array('sql' => '', 'bind' => array());
     }
     $this->segment->parseSubExpressionsIntoSqlExpressions();
     return $this->segment->getSql();
 }
예제 #3
0
 /**
  * @dataProvider getOperationSegmentExpressions
  * @group Core
  * @group SegmentExpression
  */
 public function testSegmentSqlWithOperations($expression, $expectedSql)
 {
     $segment = new Piwik_SegmentExpression($expression);
     $segment->parseSubExpressions();
     $segment->parseSubExpressionsIntoSqlExpressions();
     $processed = $segment->getSql();
     $expectedSql['join'] = '';
     $this->assertEquals($expectedSql, $processed);
 }
예제 #4
0
 public function test_SegmentSql_withOperations()
 {
     // Filter expression => SQL string + Bind values
     $expressionToSql = array('A==B%' => array('where' => " A = ? ", 'bind' => array('B%')), 'ABCDEF====B===' => array('where' => " ABCDEF = ? ", 'bind' => array('==B===')), 'A===B;CDEF!=C!=' => array('where' => " A = ? AND CDEF <> ? ", 'bind' => array('=B', 'C!=')), 'A==B,C==D' => array('where' => " (A = ? OR C = ? )", 'bind' => array('B', 'D')), 'A!=B;C==D' => array('where' => " A <> ? AND C = ? ", 'bind' => array('B', 'D')), 'A!=B;C==D,E!=Hello World!=' => array('where' => " A <> ? AND (C = ? OR E <> ? )", 'bind' => array('B', 'D', 'Hello World!=')), 'A>B' => array('where' => " A > ? ", 'bind' => array('B')), 'A<B' => array('where' => " A < ? ", 'bind' => array('B')), 'A<=B' => array('where' => " A <= ? ", 'bind' => array('B')), 'A>=B' => array('where' => " A >= ? ", 'bind' => array('B')), 'ABCDEF>=>=>=B===' => array('where' => " ABCDEF >= ? ", 'bind' => array('>=>=B===')), 'A>=<=B;CDEF>G;H>=I;J<K;L<=M' => array('where' => " A >= ? AND CDEF > ? AND H >= ? AND J < ? AND L <= ? ", 'bind' => array('<=B', 'G', 'I', 'K', 'M')), 'A>=B;C>=D,E<w_ow great!' => array('where' => " A >= ? AND (C >= ? OR E < ? )", 'bind' => array('B', 'D', 'w_ow great!')), 'A=@B_' => array('where' => " A LIKE ? ", 'bind' => array('%B\\_%')), 'A!@B%' => array('where' => " A NOT LIKE ? ", 'bind' => array('%B\\%%')));
     foreach ($expressionToSql as $expression => $expectedSql) {
         $segment = new Piwik_SegmentExpression($expression);
         $segment->parseSubExpressions();
         $segment->parseSubExpressionsIntoSqlExpressions();
         $processed = $segment->getSql();
         $expectedSql['join'] = '';
         $out = '<br/>' . var_export($processed, true) . "\n *DIFFERENT FROM*   " . var_export($expectedSql, true);
         $this->assertEqual($processed, $expectedSql, str_replace('%', '%%', $out));
     }
 }
예제 #5
0
 public function getSql( $fieldsAvailableInTable = array(), $joinedTableName = false)
 {
 	if($this->isEmpty())
 	{
 		return array('sql' => '', 'bind' => array(), 'sql_join_visits' => '');
 	}
     $this->segment->parseSubExpressionsIntoSqlExpressions($fieldsAvailableInTable, $joinedTableName);
     
     $return = $this->segment->getSql();
     
     $return['sql_join_visits'] = '';
     if(!empty($fieldsAvailableInTable))
     {
     	if(!$this->isSegmentAvailable($fieldsAvailableInTable))
     	{
     		$return['sql_join_visits'] = "LEFT JOIN ".Piwik_Common::prefixTable('log_visit')." AS log_visit USING(idvisit)";
     	}
     }
     return $return;
 }