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/Models/StatsDAOSQLite.php | 55 ------------------------------------------- 1 file changed, 55 deletions(-) (limited to 'app/Models/StatsDAOSQLite.php') 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