From 7c1b5e322cca0134f57b3a436129985ba9170b9f Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Tue, 2 Aug 2016 22:49:35 +0200 Subject: PostgreSQL draft https://github.com/FreshRSS/FreshRSS/issues/416 Based on @Damstre work https://github.com/FreshRSS/FreshRSS/pull/1071 Not tested --- app/Models/StatsDAOPGSQL.php | 192 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 192 insertions(+) create mode 100644 app/Models/StatsDAOPGSQL.php (limited to 'app/Models/StatsDAOPGSQL.php') diff --git a/app/Models/StatsDAOPGSQL.php b/app/Models/StatsDAOPGSQL.php new file mode 100644 index 000000000..0bde72e58 --- /dev/null +++ b/app/Models/StatsDAOPGSQL.php @@ -0,0 +1,192 @@ +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 + * + * @param integer $feed id + * @return string + */ + public function calculateEntryRepartitionPerFeedPerHour($feed = null) { + return $this->calculateEntryRepartitionPerFeedPerPeriod('hour', $feed); + } + + /** + * Calculates the number of article per day of week per feed + * + * @param integer $feed id + * @return string + */ + public function calculateEntryRepartitionPerFeedPerDayOfWeek($feed = null) { + return $this->calculateEntryRepartitionPerFeedPerPeriod('day', $feed); + } + + /** + * Calculates the number of article per month per feed + * + * @param integer $feed + * @return string + */ + public function calculateEntryRepartitionPerFeedPerMonth($feed = null) { + return $this->calculateEntryRepartitionPerFeedPerPeriod('month', $feed); + } + + /** + * Calculates the number of article per period per feed + * + * @param string $period format string to use for grouping + * @param integer $feed id + * @return string + */ + protected function calculateEntryRepartitionPerFeedPerPeriod($period, $feed = null) { + $restrict = ''; + if ($feed) { + $restrict = "WHERE e.id_feed = {$feed}"; + } + $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); + } + + /** + * 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); + } + +} -- 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/StatsDAOPGSQL.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 c16a973d453078a037a2a6a6e8e3d17ffd3bf2e6 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 1 Oct 2016 09:24:46 +0200 Subject: PostgreSQL schema update --- app/Models/StatsDAOPGSQL.php | 2 +- app/SQL/install.sql.pgsql.php | 12 +++--------- 2 files changed, 4 insertions(+), 10 deletions(-) (limited to 'app/Models/StatsDAOPGSQL.php') diff --git a/app/Models/StatsDAOPGSQL.php b/app/Models/StatsDAOPGSQL.php index 649dfa8b5..1effbb64b 100644 --- a/app/Models/StatsDAOPGSQL.php +++ b/app/Models/StatsDAOPGSQL.php @@ -61,7 +61,7 @@ SQL; $repartition[(int) $value['period']] = (int) $value['count']; } - return $this->convertToSerie($repartition); + return $repartition; } } diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php index 04e35af68..c2c32ef59 100644 --- a/app/SQL/install.sql.pgsql.php +++ b/app/SQL/install.sql.pgsql.php @@ -24,7 +24,7 @@ $SQL_CREATE_TABLES = array( "ttl" INT NOT NULL DEFAULT -2, "cache_nbEntries" int DEFAULT 0, "cache_nbUnreads" int DEFAULT 0, - FOREIGN KEY (category) REFERENCES "%1$scategory" ("id") ON DELETE SET NULL ON UPDATE CASCADE + FOREIGN KEY ("category") REFERENCES "%1$scategory" ("id") ON DELETE SET NULL ON UPDATE CASCADE );', 'CREATE INDEX name_index ON "%1$sfeed" ("name");', 'CREATE INDEX priority_index ON "%1$sfeed" ("priority");', @@ -44,19 +44,13 @@ $SQL_CREATE_TABLES = array( "is_favorite" smallint NOT NULL DEFAULT 0, "id_feed" SMALLINT, "tags" varchar(1023), - FOREIGN KEY (id_feed) REFERENCES "%1$sfeed" (id) ON DELETE CASCADE ON UPDATE CASCADE + FOREIGN KEY ("id_feed") REFERENCES "%1$sfeed" ("id") ON DELETE CASCADE ON UPDATE CASCADE, + UNIQUE ("id_feed","guid") );', 'CREATE INDEX is_favorite_index ON "%1$sentry" ("is_favorite");', 'CREATE INDEX is_read_index ON "%1$sentry" ("is_read");', 'CREATE INDEX entry_lastSeen_index ON "%1$sentry" ("lastSeen");', - -'CREATE OR REPLACE RULE check_constraints_on_entry AS ON INSERT TO "%1$sentry" WHERE EXISTS(SELECT 1 FROM "%1$sentry" WHERE guid=NEW.guid) DO INSTEAD NOTHING;', -'CREATE OR REPLACE RULE check_constraints_on_feed AS ON INSERT TO "%1$sfeed" WHERE EXISTS(SELECT 1 FROM "%1$sfeed" WHERE url=NEW.url) DO INSTEAD NOTHING;', -'CREATE OR REPLACE RULE check_constraints_on_category AS ON INSERT TO "%1$scategory" WHERE EXISTS(SELECT 1 FROM "%1$scategory" WHERE name=NEW.name) DO INSTEAD NOTHING;', -'CREATE OR REPLACE RULE check_constraints_on_category as on update to "%1$scategory" WHERE EXISTS(SELECT 1 FROM "%1$scategory" WHERE name=NEW.name) DO INSTEAD NOTHING;', - - 'INSERT INTO "%1$scategory" (id, name) VALUES(1, \'%2$s\');', 'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) VALUES(\'http://freshrss.org/feeds/all.atom.xml\', 1, \'FreshRSS.org\', \'http://freshrss.org/\', \'FreshRSS, a free, self-hostable aggregator…\', 86400);', 'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) VALUES(\'https://github.com/FreshRSS/FreshRSS/releases.atom\', 1, \'FreshRSS @ GitHub\', \'https://github.com/FreshRSS/FreshRSS/\', \'FreshRSS releases @ GitHub\', 86400);', -- cgit v1.2.3