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

2 files changed, 54 insertions, 44 deletions

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 = <<<SQL
-SELECT - (tens.val + units.val + 1) AS day
-FROM (
-    SELECT 0 AS val
-    UNION ALL SELECT 1
-    UNION ALL SELECT 2
-    UNION ALL SELECT 3
-    UNION ALL SELECT 4
-    UNION ALL SELECT 5
-    UNION ALL SELECT 6
-    UNION ALL SELECT 7
-    UNION ALL SELECT 8
-    UNION ALL SELECT 9
-) AS units
-CROSS JOIN (
-    SELECT 0 AS val
-    UNION ALL SELECT 10
-    UNION ALL SELECT 20
-) AS tens
-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']] = 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 = <<<SQL
 SELECT DATEDIFF(FROM_UNIXTIME(e.date), NOW()) AS day,
 COUNT(1) AS count
 FROM {$this->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;
@@ -109,9 +80,20 @@ SQL;
 	}
 
 	/**
+	 * 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 = <<<SQL
+SELECT round(julianday(e.date, 'unixepoch') - julianday('now')) AS day,
+COUNT(1) AS count
+FROM {$this->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);
 	}
 
 }