From 3bbd0e446f6a1a0c41a4db36d2841db36dc34004 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 6 Jul 2014 11:54:00 +0200 Subject: Prepare statistics for SQLite Temporarily disable 30-day statistics for SQLite https://github.com/marienfressinaud/FreshRSS/issues/100 https://github.com/marienfressinaud/FreshRSS/issues/90 --- app/Models/StatsDAOSQLite.php | 9 +++++++++ 1 file changed, 9 insertions(+) create mode 100644 app/Models/StatsDAOSQLite.php (limited to 'app/Models/StatsDAOSQLite.php') diff --git a/app/Models/StatsDAOSQLite.php b/app/Models/StatsDAOSQLite.php new file mode 100644 index 000000000..c923e5fd0 --- /dev/null +++ b/app/Models/StatsDAOSQLite.php @@ -0,0 +1,9 @@ +convertToSerie(array()); //TODO: Implement 30-day statistics for SQLite + } + +} -- cgit v1.2.3 From 68c0a827d221eeb398774d558f3d23b6e1c9e76c Mon Sep 17 00:00:00 2001 From: Alexis Degrugillier Date: Fri, 18 Jul 2014 20:35:03 -0400 Subject: Add statistics support for Sqlite Add statistics support for Sqlite by tweeking one query and rewrite an other. The rewrite implied a complete refactor of the MySql query as well. Now the code is more flexible and make less queries to the database. See #527 --- app/Models/StatsDAO.php | 68 ++++++++++++++++--------------------------- app/Models/StatsDAOSQLite.php | 30 ++++++++++++++++++- 2 files changed, 54 insertions(+), 44 deletions(-) (limited to 'app/Models/StatsDAOSQLite.php') diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index 62f238bd2..66f5104b3 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -2,6 +2,8 @@ class FreshRSS_StatsDAO extends Minz_ModelPdo { + const ENTRY_COUNT_PERIOD = 30; + /** * Calculates entry repartition for all feeds and for main stream. * The repartition includes: @@ -9,7 +11,7 @@ class FreshRSS_StatsDAO extends Minz_ModelPdo { * - read entries * - unread entries * - favorite entries - * + * * @return type */ public function calculateEntryRepartition() { @@ -50,50 +52,19 @@ SQL; /** * Calculates entry count per day on a 30 days period. * Returns the result as a JSON string. - * + * * @return string */ public function calculateEntryCount() { - $count = array(); + $count = $this->initEntryCountArray(); + $period = self::ENTRY_COUNT_PERIOD; - // Generates a list of 30 last day to be sure we always have 30 days. - // If we do not do that kind of thing, we'll end up with holes in the - // days if the user do not have a lot of feeds. - $sql = <<bd->prepare($sql); - $stm->execute(); - $res = $stm->fetchAll(PDO::FETCH_ASSOC); - foreach ($res as $value) { - $count[$value['day']] = 0; - } - - // Get stats per day for the last 30 days and applies the result on - // the array created with the last query. + // Get stats per day for the last 30 days $sql = <<prefix}entry AS e -WHERE FROM_UNIXTIME(e.date, '%Y%m%d') BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -30 DAY), '%Y%m%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d') +WHERE FROM_UNIXTIME(e.date, '%Y%m%d') BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -{$period} DAY), '%Y%m%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d') GROUP BY day ORDER BY day ASC SQL; @@ -108,10 +79,21 @@ SQL; return $this->convertToSerie($count); } + /** + * Initialize an array for the entry count. + * + * @return array + */ + protected function initEntryCountArray() { + return array_map(function () { + return 0; + }, array_flip(range(-self::ENTRY_COUNT_PERIOD, -1))); + } + /** * Calculates feed count per category. * Returns the result as a JSON string. - * + * * @return string */ public function calculateFeedByCategory() { @@ -134,7 +116,7 @@ SQL; /** * Calculates entry count per category. * Returns the result as a JSON string. - * + * * @return string */ public function calculateEntryByCategory() { @@ -158,7 +140,7 @@ SQL; /** * Calculates the 10 top feeds based on their number of entries - * + * * @return array */ public function calculateTopFeed() { @@ -172,7 +154,7 @@ FROM {$this->prefix}category AS c, {$this->prefix}entry AS e WHERE c.id = f.category AND f.id = e.id_feed -GROUP BY id +GROUP BY f.id ORDER BY count DESC LIMIT 10 SQL; @@ -180,10 +162,10 @@ SQL; $stm->execute(); return $stm->fetchAll(PDO::FETCH_ASSOC); } - + /** * Calculates the last publication date for each feed - * + * * @return array */ public function calculateFeedLastDate() { diff --git a/app/Models/StatsDAOSQLite.php b/app/Models/StatsDAOSQLite.php index c923e5fd0..dea590c92 100644 --- a/app/Models/StatsDAOSQLite.php +++ b/app/Models/StatsDAOSQLite.php @@ -2,8 +2,36 @@ class FreshRSS_StatsDAOSQLite extends FreshRSS_StatsDAO { + /** + * Calculates entry count per day on a 30 days period. + * Returns the result as a JSON string. + * + * @return string + */ public function calculateEntryCount() { - return $this->convertToSerie(array()); //TODO: Implement 30-day statistics for SQLite + $count = $this->initEntryCountArray(); + $period = parent::ENTRY_COUNT_PERIOD; + + // Get stats per day for the last 30 days + $sql = <<prefix}entry AS e +WHERE strftime('%Y%m%d', e.date, 'unixepoch') + BETWEEN strftime('%Y%m%d', 'now', '-{$period} days') + AND strftime('%Y%m%d', 'now', '-1 day') +GROUP BY day +ORDER BY day ASC +SQL; + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + + foreach ($res as $value) { + $count[(int)$value['day']] = (int) $value['count']; + } + + return $this->convertToSerie($count); } } -- cgit v1.2.3 From aa317eb2948b5caa5472bf307099efe850f7b314 Mon Sep 17 00:00:00 2001 From: Alexis Degrugillier Date: Thu, 24 Jul 2014 22:57:31 -0400 Subject: Add repartition statistic support in Sqlite --- app/Models/StatsDAO.php | 2 +- app/Models/StatsDAOSQLite.php | 28 +++++++++++++++++++++++++++- 2 files changed, 28 insertions(+), 2 deletions(-) (limited to 'app/Models/StatsDAOSQLite.php') diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index ee8d0d663..89be76a26 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -95,7 +95,7 @@ SQL; * @return string */ public function calculateEntryRepartitionPerFeedPerHour($feed = null) { - return $this->calculateEntryRepartitionPerFeedPerPeriod('%k', $feed); + return $this->calculateEntryRepartitionPerFeedPerPeriod('%H', $feed); } /** diff --git a/app/Models/StatsDAOSQLite.php b/app/Models/StatsDAOSQLite.php index dea590c92..6cb54ddf6 100644 --- a/app/Models/StatsDAOSQLite.php +++ b/app/Models/StatsDAOSQLite.php @@ -28,10 +28,36 @@ SQL; $res = $stm->fetchAll(PDO::FETCH_ASSOC); foreach ($res as $value) { - $count[(int)$value['day']] = (int) $value['count']; + $count[(int) $value['day']] = (int) $value['count']; } return $this->convertToSerie($count); } + protected function calculateEntryRepartitionPerFeedPerPeriod($period, $feed = null) { + if ($feed) { + $restrict = "WHERE e.id_feed = {$feed}"; + } else { + $restrict = ''; + } + $sql = <<prefix}entry AS e +{$restrict} +GROUP BY period +ORDER BY period ASC +SQL; + + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_NAMED); + + foreach ($res as $value) { + $repartition[(int) $value['period']] = (int) $value['count']; + } + + return $this->convertToSerie($repartition); + } + } -- cgit v1.2.3 From 8731de5c3ad4eea8ae30d1f6435c569ed31b8828 Mon Sep 17 00:00:00 2001 From: Marien Fressinaud Date: Fri, 19 Sep 2014 10:37:29 +0200 Subject: Fix repartition stats with 0 or 1 article. --- app/Models/StatsDAO.php | 8 +++++++- app/Models/StatsDAOSQLite.php | 1 + 2 files changed, 8 insertions(+), 1 deletion(-) (limited to 'app/Models/StatsDAOSQLite.php') diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index bd4271ba8..b8dc40592 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -209,8 +209,14 @@ SQL; $date_max = new \DateTime(); $date_max->setTimestamp($res['date_max']); $interval = $date_max->diff($date_min, true); + $interval_in_days = $interval->format('%a'); + if ($interval_in_days <= 0) { + // Surely only one article. + // We will return count / (period/period) == count. + $interval_in_days = $period; + } - return round($res['count'] / ($interval->format('%a') / ($period)), 2); + return round($res['count'] / ($interval_in_days / $period), 2); } /** diff --git a/app/Models/StatsDAOSQLite.php b/app/Models/StatsDAOSQLite.php index 6cb54ddf6..3b1256de1 100644 --- a/app/Models/StatsDAOSQLite.php +++ b/app/Models/StatsDAOSQLite.php @@ -53,6 +53,7 @@ SQL; $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_NAMED); + $repartition = array(); foreach ($res as $value) { $repartition[(int) $value['period']] = (int) $value['count']; } -- cgit v1.2.3