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 From cd88414abcffd94cfce933cf578ecc640b691381 Mon Sep 17 00:00:00 2001 From: Alexis Degrugillier Date: Mon, 29 Sep 2014 18:54:03 -0400 Subject: Add an average per day for the 30 day period --- app/Controllers/statsController.php | 1 + app/Models/StatsDAO.php | 27 ++++++++++++++++++++++++--- app/Models/StatsDAOSQLite.php | 23 +++++++++++++++++++++++ app/views/stats/index.phtml | 16 +++++++++++++--- 4 files changed, 61 insertions(+), 6 deletions(-) (limited to 'app/Models/StatsDAOSQLite.php') diff --git a/app/Controllers/statsController.php b/app/Controllers/statsController.php index 256543f37..3069be34d 100644 --- a/app/Controllers/statsController.php +++ b/app/Controllers/statsController.php @@ -21,6 +21,7 @@ class FreshRSS_stats_Controller extends Minz_ActionController { Minz_View::appendScript(Minz_Url::display('/scripts/flotr2.min.js?' . @filemtime(PUBLIC_PATH . '/scripts/flotr2.min.js'))); $this->view->repartition = $statsDAO->calculateEntryRepartition(); $this->view->count = $statsDAO->calculateEntryCount(); + $this->view->average = $statsDAO->calculateEntryAverage(); $this->view->feedByCategory = $statsDAO->calculateFeedByCategory(); $this->view->entryByCategory = $statsDAO->calculateEntryByCategory(); $this->view->topFeed = $statsDAO->calculateTopFeed(); diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index 40505ab3e..08dd4cd5c 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -79,6 +79,27 @@ SQL; return $this->convertToSerie($count); } + /** + * Calculates entry average per day on a 30 days period. + * + * @return integer + */ + public function calculateEntryAverage() { + $period = self::ENTRY_COUNT_PERIOD; + + // 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 -{$period} DAY), '%Y%m%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d') +SQL; + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetch(PDO::FETCH_NAMED); + + return round($res['average'], 2); + } + /** * Initialize an array for the entry count. * @@ -160,7 +181,7 @@ SQL; public function calculateEntryAveragePerFeedPerHour($feed = null) { return $this->calculateEntryAveragePerFeedPerPeriod(1/24, $feed); } - + /** * Calculates the average number of article per day of week per feed * @@ -180,10 +201,10 @@ SQL; public function calculateEntryAveragePerFeedPerMonth($feed = null) { return $this->calculateEntryAveragePerFeedPerPeriod(30, $feed); } - + /** * Calculates the average number of article per feed - * + * * @param float $period number used to divide the number of day in the period * @param integer $feed id * @return integer diff --git a/app/Models/StatsDAOSQLite.php b/app/Models/StatsDAOSQLite.php index 3b1256de1..bb2336532 100644 --- a/app/Models/StatsDAOSQLite.php +++ b/app/Models/StatsDAOSQLite.php @@ -34,6 +34,29 @@ SQL; return $this->convertToSerie($count); } + /** + * Calculates entry average per day on a 30 days period. + * + * @return integer + */ + public function calculateEntryAverage() { + $period = self::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') +SQL; + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetch(PDO::FETCH_NAMED); + + return round($res['average'], 2); + } + protected function calculateEntryRepartitionPerFeedPerPeriod($period, $feed = null) { if ($feed) { $restrict = "WHERE e.id_feed = {$feed}"; diff --git a/app/views/stats/index.phtml b/app/views/stats/index.phtml index 31185fbe3..9b19cb560 100644 --- a/app/views/stats/index.phtml +++ b/app/views/stats/index.phtml @@ -93,12 +93,22 @@ function initStats() { return; } // Entry per day + var avg = []; + for (var i = -31; i <= 0; i++) { + avg.push([i, average?>]); + } Flotr.draw(document.getElementById('statsEntryPerDay'), - [count ?>], + [{ + data: count ?>, + bars: {horizontal: false, show: true} + },{ + data: avg, + lines: {show: true}, + label: average?> + }], { grid: {verticalLines: false}, - bars: {horizontal: false, show: true}, - xaxis: {noTicks: 6, showLabels: false, tickDecimals: 0}, + xaxis: {noTicks: 6, showLabels: false, tickDecimals: 0, min: -30.75, max: -0.25}, yaxis: {min: 0}, mouse: {relative: true, track: true, trackDecimals: 0, trackFormatter: function(obj) {return numberFormat(obj.y);}} }); -- cgit v1.2.3 From 264d05297c72e87b114a8e930db7eae7affe5690 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 21 Feb 2016 17:26:37 +0100 Subject: CSP for statistics https://github.com/FreshRSS/FreshRSS/issues/1075 --- app/Models/StatsDAO.php | 20 ++++---- app/Models/StatsDAOSQLite.php | 4 +- app/views/stats/index.phtml | 64 ++++------------------- app/views/stats/repartition.phtml | 103 ++++++++------------------------------ p/scripts/repartition.js | 72 ++++++++++++++++++++++++++ p/scripts/stats.js | 56 +++++++++++++++++++++ 6 files changed, 169 insertions(+), 150 deletions(-) create mode 100644 p/scripts/repartition.js create mode 100644 p/scripts/stats.js (limited to 'app/Models/StatsDAOSQLite.php') diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index 80caccc49..5ca333396 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -55,9 +55,9 @@ SQL; /** * Calculates entry count per day on a 30 days period. - * Returns the result as a JSON string. + * Returns the result as a JSON object. * - * @return string + * @return JSON object */ public function calculateEntryCount() { $count = $this->initEntryCountArray(); @@ -257,9 +257,9 @@ SQL; /** * Calculates feed count per category. - * Returns the result as a JSON string. + * Returns the result as a JSON object. * - * @return string + * @return JSON object */ public function calculateFeedByCategory() { $sql = <<initEntryCountArray(); diff --git a/app/views/stats/index.phtml b/app/views/stats/index.phtml index 18bcd4d99..c11b88999 100644 --- a/app/views/stats/index.phtml +++ b/app/views/stats/index.phtml @@ -82,58 +82,12 @@ - + + diff --git a/app/views/stats/repartition.phtml b/app/views/stats/repartition.phtml index b20d9bbd0..980b26a3d 100644 --- a/app/views/stats/repartition.phtml +++ b/app/views/stats/repartition.phtml @@ -30,20 +30,20 @@
- +
- - - - + + + + - - - - + + + + -
repartition['total']; ?>repartition['read']; ?>repartition['unread']; ?>repartition['favorite']; ?>repartition['total']; ?>repartition['read']; ?>repartition['unread']; ?>repartition['favorite']; ?>
+
@@ -62,76 +62,13 @@
- + + diff --git a/p/scripts/repartition.js b/p/scripts/repartition.js new file mode 100644 index 000000000..a391de2f2 --- /dev/null +++ b/p/scripts/repartition.js @@ -0,0 +1,72 @@ +"use strict"; +function initStats() { + if (!window.Flotr) { + if (window.console) { + console.log('FreshRSS waiting for Flotr…'); + } + window.setTimeout(initStats, 50); + return; + } + var jsonRepartition = document.getElementById('jsonRepartition'), + stats = JSON.parse(jsonRepartition.innerHTML); + jsonRepartition.outerHTML = ''; + // Entry per hour + Flotr.draw(document.getElementById('statsEntryPerHour'), + [{ + data: stats.repartitionHour, + bars: {horizontal: false, show: true} + }], + { + grid: {verticalLines: false}, + xaxis: {noTicks: 23, + tickFormatter: function(x) { + var x = parseInt(x); + return x + 1; + }, + min: -0.9, + max: 23.9, + tickDecimals: 0}, + yaxis: {min: 0}, + mouse: {relative: true, track: true, trackDecimals: 0, trackFormatter: function(obj) {return numberFormat(obj.y);}} + }); + // Entry per day of week + Flotr.draw(document.getElementById('statsEntryPerDayOfWeek'), + [{ + data: stats.repartitionDayOfWeek, + bars: {horizontal: false, show: true} + }], + { + grid: {verticalLines: false}, + xaxis: {noTicks: 6, + tickFormatter: function(x) { + var x = parseInt(x); + return stats.days[x]; + }, + min: -0.9, + max: 6.9, + tickDecimals: 0}, + yaxis: {min: 0}, + mouse: {relative: true, track: true, trackDecimals: 0, trackFormatter: function(obj) {return numberFormat(obj.y);}} + }); + // Entry per month + Flotr.draw(document.getElementById('statsEntryPerMonth'), + [{ + data: stats.repartitionMonth, + bars: {horizontal: false, show: true} + }], + { + grid: {verticalLines: false}, + xaxis: {noTicks: 12, + tickFormatter: function(x) { + var x = parseInt(x); + return stats.months[(x - 1)]; + }, + min: 0.1, + max: 12.9, + tickDecimals: 0}, + yaxis: {min: 0}, + mouse: {relative: true, track: true, trackDecimals: 0, trackFormatter: function(obj) {return numberFormat(obj.y);}} + }); + +} +initStats(); diff --git a/p/scripts/stats.js b/p/scripts/stats.js new file mode 100644 index 000000000..2e8ab6e27 --- /dev/null +++ b/p/scripts/stats.js @@ -0,0 +1,56 @@ +"use strict"; +function initStats() { + if (!window.Flotr) { + if (window.console) { + console.log('FreshRSS waiting for Flotr…'); + } + window.setTimeout(initStats, 50); + return; + } + var jsonStats = document.getElementById('jsonStats'), + stats = JSON.parse(jsonStats.innerHTML); + jsonStats.outerHTML = ''; + // Entry per day + var avg = []; + for (var i = -31; i <= 0; i++) { + avg.push([i, stats.average]); + } + Flotr.draw(document.getElementById('statsEntryPerDay'), + [{ + data: stats.dataCount, + bars: {horizontal: false, show: true} + },{ + data: avg, + lines: {show: true}, + label: stats.average, + }], + { + grid: {verticalLines: false}, + xaxis: {noTicks: 6, showLabels: false, tickDecimals: 0, min: -30.75, max: -0.25}, + yaxis: {min: 0}, + mouse: {relative: true, track: true, trackDecimals: 0, trackFormatter: function(obj) {return numberFormat(obj.y);}} + }); + // Feed per category + Flotr.draw(document.getElementById('statsFeedPerCategory'), + stats.feedByCategory, + { + grid: {verticalLines: false, horizontalLines: false}, + pie: {explode: 10, show: true, labelFormatter: function(){return '';}}, + xaxis: {showLabels: false}, + yaxis: {showLabels: false}, + mouse: {relative: true, track: true, trackDecimals: 0, trackFormatter: function(obj) {return obj.series.label + ' - '+ numberFormat(obj.y) + ' ('+ (obj.fraction * 100).toFixed(1) + '%)';}}, + legend: {container: document.getElementById('statsFeedPerCategoryLegend'), noColumns: 3} + }); + // Entry per category + Flotr.draw(document.getElementById('statsEntryPerCategory'), + stats.entryByCategory, + { + grid: {verticalLines: false, horizontalLines: false}, + pie: {explode: 10, show: true, labelFormatter: function(){return '';}}, + xaxis: {showLabels: false}, + yaxis: {showLabels: false}, + mouse: {relative: true, track: true, trackDecimals: 0, trackFormatter: function(obj) {return obj.series.label + ' - '+ numberFormat(obj.y) + ' ('+ (obj.fraction * 100).toFixed(1) + '%)';}}, + legend: {container: document.getElementById('statsEntryPerCategoryLegend'), noColumns: 3} + }); +} +initStats(); -- cgit v1.2.3 From ccb56bcbf3ef69228ae4147a76cf3059f519bbf3 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 11 Sep 2016 11:24:32 +0200 Subject: Simplify SQL in statistics Reduce the use of product-specific date functions. Improve performances. Remove redundant functions. --- app/Controllers/statsController.php | 36 +++++++++++++++++----- app/Models/StatsDAO.php | 61 ++++++------------------------------- app/Models/StatsDAOSQLite.php | 55 --------------------------------- 3 files changed, 39 insertions(+), 113 deletions(-) (limited to 'app/Models/StatsDAOSQLite.php') diff --git a/app/Controllers/statsController.php b/app/Controllers/statsController.php index 4a597ae7d..5d1dee72c 100644 --- a/app/Controllers/statsController.php +++ b/app/Controllers/statsController.php @@ -18,6 +18,27 @@ class FreshRSS_stats_Controller extends Minz_ActionController { Minz_View::prependTitle(_t('admin.stats.title') . ' · '); } + private function convertToSerie($data) { + $serie = array(); + + foreach ($data as $key => $value) { + $serie[] = array($key, $value); + } + + return $serie; + } + + private function convertToPieSerie($data) { + $serie = array(); + + foreach ($data as $value) { + $value['data'] = array(array(0, (int) $value['data'])); + $serie[] = $value; + } + + return $serie; + } + /** * This action handles the statistic main page. * @@ -33,10 +54,11 @@ class FreshRSS_stats_Controller extends Minz_ActionController { $statsDAO = FreshRSS_Factory::createStatsDAO(); Minz_View::appendScript(Minz_Url::display('/scripts/flotr2.min.js?' . @filemtime(PUBLIC_PATH . '/scripts/flotr2.min.js'))); $this->view->repartition = $statsDAO->calculateEntryRepartition(); - $this->view->count = $statsDAO->calculateEntryCount(); - $this->view->average = $statsDAO->calculateEntryAverage(); - $this->view->feedByCategory = $statsDAO->calculateFeedByCategory(); - $this->view->entryByCategory = $statsDAO->calculateEntryByCategory(); + $entryCount = $statsDAO->calculateEntryCount(); + $this->view->count = $this->convertToSerie($entryCount); + $this->view->average = round(array_sum(array_values($entryCount)) / count($entryCount), 2); + $this->view->feedByCategory = $this->convertToPieSerie($statsDAO->calculateFeedByCategory()); + $this->view->entryByCategory = $this->convertToPieSerie($statsDAO->calculateEntryByCategory()); $this->view->topFeed = $statsDAO->calculateTopFeed(); } @@ -118,11 +140,11 @@ class FreshRSS_stats_Controller extends Minz_ActionController { $this->view->days = $statsDAO->getDays(); $this->view->months = $statsDAO->getMonths(); $this->view->repartition = $statsDAO->calculateEntryRepartitionPerFeed($id); - $this->view->repartitionHour = $statsDAO->calculateEntryRepartitionPerFeedPerHour($id); + $this->view->repartitionHour = $this->convertToSerie($statsDAO->calculateEntryRepartitionPerFeedPerHour($id)); $this->view->averageHour = $statsDAO->calculateEntryAveragePerFeedPerHour($id); - $this->view->repartitionDayOfWeek = $statsDAO->calculateEntryRepartitionPerFeedPerDayOfWeek($id); + $this->view->repartitionDayOfWeek = $this->convertToSerie($statsDAO->calculateEntryRepartitionPerFeedPerDayOfWeek($id)); $this->view->averageDayOfWeek = $statsDAO->calculateEntryAveragePerFeedPerDayOfWeek($id); - $this->view->repartitionMonth = $statsDAO->calculateEntryRepartitionPerFeedPerMonth($id); + $this->view->repartitionMonth = $this->convertToSerie($statsDAO->calculateEntryRepartitionPerFeedPerMonth($id)); $this->view->averageMonth = $statsDAO->calculateEntryAveragePerFeedPerMonth($id); } } diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index 4f83ff577..28882baab 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -61,14 +61,15 @@ SQL; */ public function calculateEntryCount() { $count = $this->initEntryCountArray(); - $period = self::ENTRY_COUNT_PERIOD; + $midnight = mktime(0, 0, 0); + $oldest = $midnight - (self::ENTRY_COUNT_PERIOD * 86400); // 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 -{$period} DAY), '%Y%m%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d') +SELECT FLOOR((date - {$midnight}) / 86400) AS day, +COUNT(*) as count +FROM {$this->prefix}entry +WHERE date >= {$oldest} AND date < {$midnight} GROUP BY day ORDER BY day ASC SQL; @@ -80,28 +81,7 @@ SQL; $count[$value['day']] = (int) $value['count']; } - return $this->convertToSerie($count); - } - - /** - * Calculates entry average per day on a 30 days period. - * - * @return integer - */ - public function calculateEntryAverage() { - $period = self::ENTRY_COUNT_PERIOD; - - // 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 -{$period} DAY), '%Y%m%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d') -SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); - $res = $stm->fetch(PDO::FETCH_NAMED); - - return round($res['average'], 2); + return $count; } /** @@ -173,7 +153,7 @@ SQL; $repartition[(int) $value['period']] = (int) $value['count']; } - return $this->convertToSerie($repartition); + return $repartition; } /** @@ -276,7 +256,7 @@ SQL; $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_ASSOC); - return $this->convertToPieSerie($res); + return $res; } /** @@ -301,7 +281,7 @@ SQL; $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_ASSOC); - return $this->convertToPieSerie($res); + return $res; } /** @@ -351,27 +331,6 @@ SQL; return $stm->fetchAll(PDO::FETCH_ASSOC); } - protected function convertToSerie($data) { - $serie = array(); - - foreach ($data as $key => $value) { - $serie[] = array($key, $value); - } - - return $serie; - } - - protected function convertToPieSerie($data) { - $serie = array(); - - foreach ($data as $value) { - $value['data'] = array(array(0, (int) $value['data'])); - $serie[] = $value; - } - - return $serie; - } - /** * Gets days ready for graphs * diff --git a/app/Models/StatsDAOSQLite.php b/app/Models/StatsDAOSQLite.php index 9bfe8b20a..e09d18c77 100644 --- a/app/Models/StatsDAOSQLite.php +++ b/app/Models/StatsDAOSQLite.php @@ -2,61 +2,6 @@ class FreshRSS_StatsDAOSQLite extends FreshRSS_StatsDAO { - /** - * Calculates entry count per day on a 30 days period. - * Returns the result as a JSON object. - * - * @return JSON object - */ - public function calculateEntryCount() { - $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); - } - - /** - * Calculates entry average per day on a 30 days period. - * - * @return integer - */ - public function calculateEntryAverage() { - $period = self::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') -SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); - $res = $stm->fetch(PDO::FETCH_NAMED); - - return round($res['average'], 2); - } - protected function calculateEntryRepartitionPerFeedPerPeriod($period, $feed = null) { if ($feed) { $restrict = "WHERE e.id_feed = {$feed}"; -- cgit v1.2.3 From dbc68590da1d95c249f780e2d3ff4707f6f504e9 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Tue, 27 Sep 2016 23:35:39 +0200 Subject: A bit more PostgreSQL Simplified statistics https://github.com/FreshRSS/FreshRSS/pull/1250 --- CHANGELOG.md | 2 + README.fr.md | 2 +- README.md | 2 +- app/Models/StatsDAO.php | 30 +++++----- app/Models/StatsDAOPGSQL.php | 125 ------------------------------------------ app/Models/StatsDAOSQLite.php | 2 +- app/install.php | 2 +- 7 files changed, 21 insertions(+), 144 deletions(-) (limited to 'app/Models/StatsDAOSQLite.php') diff --git a/CHANGELOG.md b/CHANGELOG.md index f13922837..e88daba93 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -4,6 +4,8 @@ * API * Support for editing feeds and categories from client applications [#1254](https://github.com/FreshRSS/FreshRSS/issues/1254) +* Compatibility: + * Experimental support for PostgreSQL [#1195](https://github.com/FreshRSS/FreshRSS/pull/1195) * Features * Better control of number of entries per page or RSS feed [#1249](https://github.com/FreshRSS/FreshRSS/issues/1249) * Since X hours: `https://freshrss.example/i/?a=rss&hours=3` diff --git a/README.fr.md b/README.fr.md index 8dc0f6fec..65cef544f 100644 --- a/README.fr.md +++ b/README.fr.md @@ -35,7 +35,7 @@ Nous sommes une communauté amicale. * PHP 5.3.3+ (PHP 5.4+ recommandé, et PHP 5.5+ pour les performances, et PHP 7+ pour d’encore meilleures performances) * Requis : [DOM](http://php.net/dom), [XML](http://php.net/xml), [PDO_MySQL](http://php.net/pdo-mysql) ou [PDO_SQLite](http://php.net/pdo-sqlite) ou [PDO_PGSQL](http://php.net/pdo-pgsql), [cURL](http://php.net/curl) * Recommandés : [JSON](http://php.net/json), [GMP](http://php.net/gmp) (pour accès API sur plateformes < 64 bits), [IDN](http://php.net/intl.idn) (pour les noms de domaines internationalisés), [mbstring](http://php.net/mbstring) et/ou [iconv](http://php.net/iconv) (pour conversion d’encodages), [Zip](http://php.net/zip) (pour import/export), [zlib](http://php.net/zlib) (pour les flux compressés) -* MySQL 5.5.3+ (recommandé), ou SQLite 3.7.4+, ou PostgreSQL +* MySQL 5.5.3+ (recommandé), ou SQLite 3.7.4+, ou PostgreSQL (experimental) * Un navigateur Web récent tel Firefox, Internet Explorer 11 / Edge, Chrome, Opera, Safari. * Fonctionne aussi sur mobile diff --git a/README.md b/README.md index fc2c6fdb9..505a1d970 100644 --- a/README.md +++ b/README.md @@ -35,7 +35,7 @@ We are a friendly community. * PHP 5.3.3+ (PHP 5.4+ recommended, and PHP 5.5+ for performance, and PHP 7 for even higher performance) * Required extensions: [DOM](http://php.net/dom), [XML](http://php.net/xml), [PDO_MySQL](http://php.net/pdo-mysql) or [PDO_SQLite](http://php.net/pdo-sqlite) or [PDO_PGSQL](http://php.net/pdo-pgsql), [cURL](http://php.net/curl) * Recommended extensions: [JSON](http://php.net/json), [GMP](http://php.net/gmp) (for API access on platforms < 64 bits), [IDN](http://php.net/intl.idn) (for Internationalized Domain Names), [mbstring](http://php.net/mbstring) and/or [iconv](http://php.net/iconv) (for charset conversion), [Zip](http://php.net/zip) (for import/export), [zlib](http://php.net/zlib) (for compressed feeds) -* MySQL 5.5.3+ (recommended), or SQLite 3.7.4+, or PostgreSQL +* MySQL 5.5.3+ (recommended), or SQLite 3.7.4+, or PostgreSQL (experimental) * A recent browser like Firefox, Internet Explorer 11 / Edge, Chrome, Opera, Safari. * Works on mobile diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index e6998a6d7..fa682f488 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -41,8 +41,8 @@ SELECT COUNT(1) AS total, COUNT(1) - SUM(e.is_read) AS count_unreads, SUM(e.is_read) AS count_reads, SUM(e.is_favorite) AS count_favorites -FROM {$this->prefix}entry AS e -, {$this->prefix}feed AS f +FROM `{$this->prefix}entry` AS e +, `{$this->prefix}feed` AS f WHERE e.id_feed = f.id {$filter} SQL; @@ -68,7 +68,7 @@ SQL; $sql = <<prefix}entry +FROM `{$this->prefix}entry` WHERE date >= {$oldest} AND date < {$midnight} GROUP BY day ORDER BY day ASC @@ -138,7 +138,7 @@ SQL; $sql = <<prefix}entry AS e +FROM `{$this->prefix}entry` AS e {$restrict} GROUP BY period ORDER BY period ASC @@ -202,7 +202,7 @@ SQL; SELECT COUNT(1) AS count , MIN(date) AS date_min , MAX(date) AS date_max -FROM {$this->prefix}entry AS e +FROM `{$this->prefix}entry` AS e {$restrict} SQL; $stm = $this->bd->prepare($sql); @@ -246,8 +246,8 @@ SQL; $sql = <<prefix}category AS c, -{$this->prefix}feed AS f +FROM `{$this->prefix}category` AS c, +`{$this->prefix}feed` AS f WHERE c.id = f.category GROUP BY label ORDER BY data DESC @@ -269,9 +269,9 @@ SQL; $sql = <<prefix}category AS c, -{$this->prefix}feed AS f, -{$this->prefix}entry AS e +FROM `{$this->prefix}category` AS c, +`{$this->prefix}feed` AS f, +`{$this->prefix}entry` AS e WHERE c.id = f.category AND f.id = e.id_feed GROUP BY label @@ -295,9 +295,9 @@ SELECT f.id AS id , MAX(f.name) AS name , MAX(c.name) AS category , COUNT(e.id) AS count -FROM {$this->prefix}category AS c, -{$this->prefix}feed AS f, -{$this->prefix}entry AS e +FROM `{$this->prefix}category` AS c, +`{$this->prefix}feed` AS f, +`{$this->prefix}entry` AS e WHERE c.id = f.category AND f.id = e.id_feed GROUP BY f.id @@ -320,8 +320,8 @@ SELECT MAX(f.id) as id , MAX(f.name) AS name , MAX(date) AS last_date , COUNT(*) AS nb_articles -FROM {$this->prefix}feed AS f, -{$this->prefix}entry AS e +FROM `{$this->prefix}feed` AS f, +`{$this->prefix}entry` AS e WHERE f.id = e.id_feed GROUP BY f.id ORDER BY name diff --git a/app/Models/StatsDAOPGSQL.php b/app/Models/StatsDAOPGSQL.php index 0bde72e58..649dfa8b5 100644 --- a/app/Models/StatsDAOPGSQL.php +++ b/app/Models/StatsDAOPGSQL.php @@ -2,94 +2,6 @@ class FreshRSS_StatsDAOPGSQL extends FreshRSS_StatsDAO { - /** - * Calculates entry repartition for the selection. - * The repartition includes: - * - total entries - * - read entries - * - unread entries - * - favorite entries - * - * @param null|integer $feed feed id - * @param boolean $only_main - * @return array - */ - public function calculateEntryRepartitionPerFeed($feed = null, $only_main = false) { - $filter = ''; - if ($only_main) { - $filter .= 'AND f.priority = 10'; - } - if (!is_null($feed)) { - $filter .= "AND e.id_feed = {$feed}"; - } - $sql = <<prefix}entry" AS e -, "{$this->prefix}feed" AS f -WHERE e.id_feed = f.id -{$filter} -SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); - $res = $stm->fetchAll(PDO::FETCH_ASSOC); - - return $res[0]; - } - - /** - * Calculates entry count per day on a 30 days period. - * Returns the result as a JSON string. - * - * @return string - */ - public function calculateEntryCount() { - $count = $this->initEntryCountArray(); - $period = self::ENTRY_COUNT_PERIOD; - - // Get stats per day for the last 30 days - $sql = <<prefix}entry" AS e -WHERE to_timestamp(e.date) BETWEEN NOW() - INTERVAL '{$period} DAYS' AND NOW() - INTERVAL '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[$value['day']] = (int) $value['count']; - } - - return $this->convertToSerie($count); - } - - /** - * Calculates entry average per day on a 30 days period. - * - * @return integer - */ - public function calculateEntryAverage() { - $period = self::ENTRY_COUNT_PERIOD; - - // Get stats per day for the last 30 days - $sql = <<prefix}entry" AS e -WHERE to_timestamp(e.date) BETWEEN NOW() - INTERVAL '{$period} DAYS' AND NOW() - INTERVAL '1 DAY' -SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); - $res = $stm->fetch(PDO::FETCH_NAMED); - - return round($res['average'], 2); - } - /** * Calculates the number of article per hour of the day per feed * @@ -152,41 +64,4 @@ SQL; return $this->convertToSerie($repartition); } - /** - * Calculates the average number of article per feed - * - * @param float $period number used to divide the number of day in the period - * @param integer $feed id - * @return integer - */ - protected function calculateEntryAveragePerFeedPerPeriod($period, $feed = null) { - $restrict = ''; - if ($feed) { - $restrict = "WHERE e.id_feed = {$feed}"; - } - $sql = <<prefix}entry" AS e -{$restrict} -SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); - $res = $stm->fetch(PDO::FETCH_NAMED); - $date_min = new \DateTime(); - $date_min->setTimestamp($res['date_min']); - $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 $res['count'] / ($interval_in_days / $period); - } - } diff --git a/app/Models/StatsDAOSQLite.php b/app/Models/StatsDAOSQLite.php index e09d18c77..ec0cfa81a 100644 --- a/app/Models/StatsDAOSQLite.php +++ b/app/Models/StatsDAOSQLite.php @@ -11,7 +11,7 @@ class FreshRSS_StatsDAOSQLite extends FreshRSS_StatsDAO { $sql = <<prefix}entry AS e +FROM `{$this->prefix}entry` AS e {$restrict} GROUP BY period ORDER BY period ASC diff --git a/app/install.php b/app/install.php index 7f4e65034..1972379e5 100644 --- a/app/install.php +++ b/app/install.php @@ -714,7 +714,7 @@ function printStep3() { -- cgit v1.2.3 From c35111fe53ba2081abc735cb61e83987ade5ce86 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Thu, 29 Sep 2016 23:36:25 +0200 Subject: Fix SQLite --- app/Models/StatsDAO.php | 7 ++++++- app/Models/StatsDAOSQLite.php | 6 +++++- 2 files changed, 11 insertions(+), 2 deletions(-) (limited to 'app/Models/StatsDAOSQLite.php') diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index fa682f488..2ce4f2944 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -4,6 +4,10 @@ class FreshRSS_StatsDAO extends Minz_ModelPdo { const ENTRY_COUNT_PERIOD = 30; + protected function sqlFloor($s) { + return "FLOOR($s)"; + } + /** * Calculates entry repartition for all feeds and for main stream. * @@ -65,8 +69,9 @@ SQL; $oldest = $midnight - (self::ENTRY_COUNT_PERIOD * 86400); // Get stats per day for the last 30 days + $sqlDay = $this->sqlFloor("(date - $midnight) / 86400"); $sql = <<prefix}entry` WHERE date >= {$oldest} AND date < {$midnight} diff --git a/app/Models/StatsDAOSQLite.php b/app/Models/StatsDAOSQLite.php index ec0cfa81a..6cfc20463 100644 --- a/app/Models/StatsDAOSQLite.php +++ b/app/Models/StatsDAOSQLite.php @@ -2,6 +2,10 @@ class FreshRSS_StatsDAOSQLite extends FreshRSS_StatsDAO { + protected function sqlFloor($s) { + return "CAST(($s) AS INT)"; + } + protected function calculateEntryRepartitionPerFeedPerPeriod($period, $feed = null) { if ($feed) { $restrict = "WHERE e.id_feed = {$feed}"; @@ -26,7 +30,7 @@ SQL; $repartition[(int) $value['period']] = (int) $value['count']; } - return $this->convertToSerie($repartition); + return $repartition; } } -- cgit v1.2.3