diff options
-rw-r--r-- | plugins/Live/Model.php | 220 | ||||
-rw-r--r-- | plugins/Live/tests/System/APITest.php (renamed from plugins/Live/tests/Integration/APITest.php) | 0 | ||||
-rw-r--r-- | plugins/Live/tests/System/ModelTest.php | 123 | ||||
-rw-r--r-- | tests/PHPUnit/Integration/SegmentTest.php | 30 |
4 files changed, 261 insertions, 112 deletions
diff --git a/plugins/Live/Model.php b/plugins/Live/Model.php index 2353450572..2d438bff9b 100644 --- a/plugins/Live/Model.php +++ b/plugins/Live/Model.php @@ -36,105 +36,10 @@ class Model */ public function queryLogVisits($idSite, $period, $date, $segment, $countVisitorsToFetch, $visitorId, $minTimestamp, $filterSortOrder) { - $where = $whereBind = array(); - - list($whereClause, $idSites) = $this->getIdSitesWhereClause($idSite); - - $where[] = $whereClause; - $whereBind = $idSites; - - if (strtolower($filterSortOrder) !== 'asc') { - $filterSortOrder = 'DESC'; - } - - $orderBy = "idsite, visit_last_action_time " . $filterSortOrder; - $orderByParent = "sub.visit_last_action_time " . $filterSortOrder; - - if (!empty($visitorId)) { - $where[] = "log_visit.idvisitor = ? "; - $whereBind[] = @Common::hex2bin($visitorId); - } - - if (!empty($minTimestamp)) { - $where[] = "log_visit.visit_last_action_time > ? "; - $whereBind[] = date("Y-m-d H:i:s", $minTimestamp); - } - - // If no other filter, only look at the last 24 hours of stats - if (empty($visitorId) - && empty($countVisitorsToFetch) - && empty($period) - && empty($date) - ) { - $period = 'day'; - $date = 'yesterdaySameTime'; - } - - // SQL Filter with provided period - if (!empty($period) && !empty($date)) { - $currentSite = new Site($idSite); - $currentTimezone = $currentSite->getTimezone(); - - $dateString = $date; - if ($period == 'range') { - $processedPeriod = new Range('range', $date); - if ($parsedDate = Range::parseDateRange($date)) { - $dateString = $parsedDate[2]; - } - } else { - $processedDate = Date::factory($date); - if ($date == 'today' - || $date == 'now' - || $processedDate->toString() == Date::factory('now', $currentTimezone)->toString() - ) { - $processedDate = $processedDate->subDay(1); - } - $processedPeriod = Period\Factory::build($period, $processedDate); - } - $dateStart = $processedPeriod->getDateStart()->setTimezone($currentTimezone); - $where[] = "log_visit.visit_last_action_time >= ?"; - $whereBind[] = $dateStart->toString('Y-m-d H:i:s'); - - if (!in_array($date, array('now', 'today', 'yesterdaySameTime')) - && strpos($date, 'last') === false - && strpos($date, 'previous') === false - && Date::factory($dateString)->toString('Y-m-d') != Date::factory('now', $currentTimezone)->toString() - ) { - $dateEnd = $processedPeriod->getDateEnd()->setTimezone($currentTimezone); - $where[] = " log_visit.visit_last_action_time <= ?"; - $dateEndString = $dateEnd->addDay(1)->toString('Y-m-d H:i:s'); - $whereBind[] = $dateEndString; - } - } + list($sql, $bind) = $this->makeLogVisitsQueryString($idSite, $period, $date, $segment, $countVisitorsToFetch, $visitorId, $minTimestamp, $filterSortOrder); - if (count($where) > 0) { - $where = join(" - AND ", $where); - } else { - $where = false; - } - - $segment = new Segment($segment, $idSite); - - // Subquery to use the indexes for ORDER BY - $select = "log_visit.*"; - $from = "log_visit"; - $groupBy = false; - $subQuery = $segment->getSelectQuery($select, $from, $where, $whereBind, $orderBy, $groupBy); - - $sqlLimit = $countVisitorsToFetch >= 1 ? " LIMIT 0, " . (int)$countVisitorsToFetch : ""; - - // Group by idvisit so that a visitor converting 2 goals only appears once - $sql = " - SELECT sub.* FROM ( - " . $subQuery['sql'] . " - $sqlLimit - ) AS sub - GROUP BY sub.idvisit - ORDER BY $orderByParent - "; try { - $data = Db::fetchAll($sql, $subQuery['bind']); + $data = Db::fetchAll($sql, $bind); return $data; } catch (Exception $e) { echo $e->getMessage(); @@ -265,4 +170,125 @@ class Model } return $visitorId; } + + /** + * @param $idSite + * @param $period + * @param $date + * @param $segment + * @param $countVisitorsToFetch + * @param $visitorId + * @param $minTimestamp + * @param $filterSortOrder + * @return array + * @throws Exception + */ + public function makeLogVisitsQueryString($idSite, $period, $date, $segment, $countVisitorsToFetch, $visitorId, $minTimestamp, $filterSortOrder) + { + $where = $whereBind = array(); + + list($whereClause, $idSites) = $this->getIdSitesWhereClause($idSite); + + $where[] = $whereClause; + $whereBind = $idSites; + + if (!empty($visitorId)) { + $where[] = "log_visit.idvisitor = ? "; + $whereBind[] = @Common::hex2bin($visitorId); + } + + if (!empty($minTimestamp)) { + $where[] = "log_visit.visit_last_action_time > ? "; + $whereBind[] = date("Y-m-d H:i:s", $minTimestamp); + } + + // If no other filter, only look at the last 24 hours of stats + if (empty($visitorId) + && empty($countVisitorsToFetch) + && empty($period) + && empty($date) + ) { + $period = 'day'; + $date = 'yesterdaySameTime'; + } + + // SQL Filter with provided period + if (!empty($period) && !empty($date)) { + $currentSite = $this->makeSite($idSite); + $currentTimezone = $currentSite->getTimezone(); + + $dateString = $date; + if ($period == 'range') { + $processedPeriod = new Range('range', $date); + if ($parsedDate = Range::parseDateRange($date)) { + $dateString = $parsedDate[2]; + } + } else { + $processedDate = Date::factory($date); + if ($date == 'today' + || $date == 'now' + || $processedDate->toString() == Date::factory('now', $currentTimezone)->toString() + ) { + $processedDate = $processedDate->subDay(1); + } + $processedPeriod = Period\Factory::build($period, $processedDate); + } + $dateStart = $processedPeriod->getDateStart()->setTimezone($currentTimezone); + $where[] = "log_visit.visit_last_action_time >= ?"; + $whereBind[] = $dateStart->toString('Y-m-d H:i:s'); + + if (!in_array($date, array('now', 'today', 'yesterdaySameTime')) + && strpos($date, 'last') === false + && strpos($date, 'previous') === false + && Date::factory($dateString)->toString('Y-m-d') != Date::factory('now', $currentTimezone)->toString() + ) { + $dateEnd = $processedPeriod->getDateEnd()->setTimezone($currentTimezone); + $where[] = " log_visit.visit_last_action_time <= ?"; + $dateEndString = $dateEnd->addDay(1)->toString('Y-m-d H:i:s'); + $whereBind[] = $dateEndString; + } + } + + if (count($where) > 0) { + $where = join(" + AND ", $where); + } else { + $where = false; + } + + if (strtolower($filterSortOrder) !== 'asc') { + $filterSortOrder = 'DESC'; + } + $segment = new Segment($segment, $idSite); + + // Subquery to use the indexes for ORDER BY + $select = "log_visit.*"; + $from = "log_visit"; + $groupBy = false; + $limit = $countVisitorsToFetch >= 1 ? (int)$countVisitorsToFetch : 0; + $orderBy = "idsite, visit_last_action_time " . $filterSortOrder; + $orderByParent = "sub.visit_last_action_time " . $filterSortOrder; + + $subQuery = $segment->getSelectQuery($select, $from, $where, $whereBind, $orderBy, $groupBy, $limit); + + $bind = $subQuery['bind']; + // Group by idvisit so that a visitor converting 2 goals only appears once + $sql = " + SELECT sub.* FROM ( + " . $subQuery['sql'] . " + ) AS sub + GROUP BY sub.idvisit + ORDER BY $orderByParent + "; + return array($sql, $bind); + } + + /** + * @param $idSite + * @return Site + */ + protected function makeSite($idSite) + { + return new Site($idSite); + } }
\ No newline at end of file diff --git a/plugins/Live/tests/Integration/APITest.php b/plugins/Live/tests/System/APITest.php index 2c85088a19..2c85088a19 100644 --- a/plugins/Live/tests/Integration/APITest.php +++ b/plugins/Live/tests/System/APITest.php diff --git a/plugins/Live/tests/System/ModelTest.php b/plugins/Live/tests/System/ModelTest.php new file mode 100644 index 0000000000..d5dea34767 --- /dev/null +++ b/plugins/Live/tests/System/ModelTest.php @@ -0,0 +1,123 @@ +<?php +/** + * Piwik - free/libre analytics platform + * + * @link http://piwik.org + * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later + */ + +namespace Piwik\Plugins\Live\tests\Integration; + +use Piwik\Access; +use Piwik\Common; +use Piwik\Plugins\Live\Model; +use Piwik\Tests\Framework\Fixture; +use Piwik\Tests\Framework\Mock\FakeAccess; +use Piwik\Tests\Framework\TestCase\SystemTestCase; +use Piwik\Tests\Integration\SegmentTest; + +/** + * @group LiveModelTest + * @group Plugins + */ +class ModelTest extends SystemTestCase +{ + function setUp() + { + $this->setSuperUser(); + Fixture::createWebsite('2010-01-01'); + } + + public function test_makeLogVisitsQueryString() + { + $model = new Model(); + list($sql, $bind) = $model->makeLogVisitsQueryString( + $idSite = 1, + $period = 'month', + $date = '2010-01-01', + $segment = false, + $countVisitorsToFetch = 100, + $visitorId = false, + $minTimestamp = false, + $filterSortOrder = false + ); + $expectedSql = ' SELECT sub.* FROM + ( + SELECT log_visit.* + FROM piwiktests_log_visit AS log_visit + WHERE log_visit.idsite in (?) + AND log_visit.visit_last_action_time >= ? + AND log_visit.visit_last_action_time <= ? + ORDER BY idsite, visit_last_action_time DESC + LIMIT 100 + ) AS sub + GROUP BY sub.idvisit + ORDER BY sub.visit_last_action_time DESC + '; + $expectedBind = array( + '1', + '2010-01-01 00:00:00', + '2010-02-01 00:00:00', + ); + $this->assertEquals(SegmentTest::removeExtraWhiteSpaces($expectedSql), SegmentTest::removeExtraWhiteSpaces($sql)); + $this->assertEquals(SegmentTest::removeExtraWhiteSpaces($expectedBind), SegmentTest::removeExtraWhiteSpaces($bind)); + } + + + public function test_makeLogVisitsQueryString_whenSegment() + { + $model = new Model(); + list($sql, $bind) = $model->makeLogVisitsQueryString( + $idSite = 1, + $period = 'month', + $date = '2010-01-01', + $segment = 'customVariablePageName1==Test', + $countVisitorsToFetch = 100, + $visitorId = 'abc', + $minTimestamp = false, + $filterSortOrder = false + ); + $expectedSql = ' SELECT sub.* FROM + ( + + SELECT log_inner.* + FROM ( + SELECT log_visit.* + FROM piwiktests_log_visit AS log_visit + LEFT JOIN piwiktests_log_link_visit_action AS log_link_visit_action + ON log_link_visit_action.idvisit = log_visit.idvisit + WHERE ( log_visit.idsite in (?) + AND log_visit.idvisitor = ? + AND log_visit.visit_last_action_time >= ? + AND log_visit.visit_last_action_time <= ? ) + AND ( log_link_visit_action.custom_var_k1 = ? ) + GROUP BY log_visit.idvisit + ORDER BY NULL + LIMIT 100 + ) AS log_inner + ORDER BY idsite, visit_last_action_time DESC + LIMIT 100 + ) AS sub + GROUP BY sub.idvisit + ORDER BY sub.visit_last_action_time DESC + '; + $expectedBind = array( + '1', + Common::hex2bin('abc'), + '2010-01-01 00:00:00', + '2010-02-01 00:00:00', + 'Test', + ); + $this->assertEquals(SegmentTest::removeExtraWhiteSpaces($expectedSql), SegmentTest::removeExtraWhiteSpaces($sql)); + $this->assertEquals(SegmentTest::removeExtraWhiteSpaces($expectedBind), SegmentTest::removeExtraWhiteSpaces($bind)); + } + + + protected function setSuperUser() + { + $pseudoMockAccess = new FakeAccess(); + FakeAccess::$superUser = true; + Access::setSingletonInstance($pseudoMockAccess); + } + +}
\ No newline at end of file diff --git a/tests/PHPUnit/Integration/SegmentTest.php b/tests/PHPUnit/Integration/SegmentTest.php index e0fd9cd3c4..864420a161 100644 --- a/tests/PHPUnit/Integration/SegmentTest.php +++ b/tests/PHPUnit/Integration/SegmentTest.php @@ -36,11 +36,11 @@ class SegmentTest extends IntegrationTestCase parent::tearDown(); } - protected function _filterWhiteSpaces($valueToFilter) + static public function removeExtraWhiteSpaces($valueToFilter) { if (is_array($valueToFilter)) { foreach ($valueToFilter as $key => $value) { - $valueToFilter[$key] = $this->_filterWhiteSpaces($value); + $valueToFilter[$key] = self::removeExtraWhiteSpaces($value); } return $valueToFilter; } else { @@ -117,11 +117,11 @@ class SegmentTest extends IntegrationTestCase $segment = new Segment($segment, $idSites = array()); $sql = $segment->getSelectQuery($select, $from, false); - $this->assertEquals($this->_filterWhiteSpaces($expected), $this->_filterWhiteSpaces($sql)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($sql)); // calling twice should give same results $sql = $segment->getSelectQuery($select, array($from)); - $this->assertEquals($this->_filterWhiteSpaces($expected), $this->_filterWhiteSpaces($sql)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($sql)); $this->assertEquals(32, strlen($segment->getHash())); } @@ -150,7 +150,7 @@ class SegmentTest extends IntegrationTestCase ( log_visit.custom_var_k1 = ? AND log_visit.visitor_returning = ? )", "bind" => array(1, 'Test', 0)); - $this->assertEquals($this->_filterWhiteSpaces($expected), $this->_filterWhiteSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } public function test_getSelectQuery_whenJoinVisitOnAction() @@ -178,7 +178,7 @@ class SegmentTest extends IntegrationTestCase ( log_link_visit_action.custom_var_k1 = ? AND log_visit.visitor_returning = ? )", "bind" => array(1, 'Test', 0)); - $this->assertEquals($this->_filterWhiteSpaces($expected), $this->_filterWhiteSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } public function test_getSelectQuery_whenJoinActionOnVisit() @@ -214,7 +214,7 @@ class SegmentTest extends IntegrationTestCase ) AS log_inner", "bind" => array(1, 'Test', 0)); - $this->assertEquals($this->_filterWhiteSpaces($expected), $this->_filterWhiteSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } public function test_getSelectQuery_whenJoinConversionOnAction() @@ -242,7 +242,7 @@ class SegmentTest extends IntegrationTestCase ( log_link_visit_action.custom_var_k1 = ? AND log_conversion.idgoal = ? AND log_link_visit_action.custom_var_k2 = ? )", "bind" => array(1, 'Test', 1, 'Test2')); - $this->assertEquals($this->_filterWhiteSpaces($expected), $this->_filterWhiteSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } public function test_getSelectQuery_whenJoinActionOnConversion() @@ -270,7 +270,7 @@ class SegmentTest extends IntegrationTestCase ( ( log_conversion.idgoal IS NULL OR log_conversion.idgoal <> ? ) AND log_link_visit_action.custom_var_k1 = ? AND log_conversion.idgoal = ? )", "bind" => array(1, 2, 'Test', 1)); - $this->assertEquals($this->_filterWhiteSpaces($expected), $this->_filterWhiteSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } public function test_getSelectQuery_whenJoinConversionOnVisit() @@ -305,7 +305,7 @@ class SegmentTest extends IntegrationTestCase ) AS log_inner", "bind" => array(1, 1)); - $this->assertEquals($this->_filterWhiteSpaces($expected), $this->_filterWhiteSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } public function test_getSelectQuery_whenJoinConversionOnly() @@ -332,7 +332,7 @@ class SegmentTest extends IntegrationTestCase ( log_conversion.idgoal = ? )", "bind" => array(1, 1)); - $this->assertEquals($this->_filterWhiteSpaces($expected), $this->_filterWhiteSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } public function test_getSelectQuery_whenJoinVisitOnConversion() @@ -360,7 +360,7 @@ class SegmentTest extends IntegrationTestCase ( (log_conversion.idgoal = ? OR HOUR(log_visit.visit_last_action_time) = ? ))", "bind" => array(1, 1, 12)); - $this->assertEquals($this->_filterWhiteSpaces($expected), $this->_filterWhiteSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } /** @@ -391,7 +391,7 @@ class SegmentTest extends IntegrationTestCase HOUR(log_visit.visit_last_action_time) = ? AND log_conversion.idgoal = ? ", "bind" => array(12, 1)); - $this->assertEquals($this->_filterWhiteSpaces($expected), $this->_filterWhiteSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } /** @@ -429,7 +429,7 @@ class SegmentTest extends IntegrationTestCase ) AS log_inner", "bind" => array(1, 12, 'Test')); - $this->assertEquals($this->_filterWhiteSpaces($expected), $this->_filterWhiteSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } /** @@ -496,6 +496,6 @@ class SegmentTest extends IntegrationTestCase LIMIT 33", "bind" => array(1, 'Test')); - $this->assertEquals($this->_filterWhiteSpaces($expected), $this->_filterWhiteSpaces($query)); + $this->assertEquals($this->removeExtraWhiteSpaces($expected), $this->removeExtraWhiteSpaces($query)); } } |