Added integration test for the model, to test the SQL query generated

3 files changed, 246 insertions, 97 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