From c76a318193cda63064625b2d92c719b7150d7d64 Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Sun, 15 Sep 2019 21:36:53 +0200
Subject: CLI to export/import any database to/from SQLite (#2496)

* CLI to export/import any database to/from SQLite

Require PHP 5.5+ https://github.com/FreshRSS/FreshRSS/pull/2495

* Travis

* Execution rights

* Fix wrong static fields

* Fix MySQL bad default buffering

https://stackoverflow.com/questions/6895098/pdo-mysql-memory-consumption-with-large-result-set/6935271#6935271
https://php.net/manual/ref.pdo-mysql

* Fix count on progression

* Avoid static DB information

To ease working with two DBs at the same time

* Less static, simplify

Needs some testing

* Small corrections

* Special case for SQLite to SQLite

* Modify special case for SQLite

* Remove special case for SQLite

More uniform logic for the 3 databases.
Fix wrong DROP TABLE for SQLite.

* Drop indexes

* Revert "Drop indexes"

This reverts commit f28d2bae0935745c1c74ea38f2ee083f3fd4bf9d.

* Fix deletion

* Fix classic export

* Update cli/README.md

Co-Authored-By: Marien Fressinaud <dev@marienfressinaud.fr>

* Addressing part of review

* Remove goto :cry:

* Travis

* Comment for SQLite case

* Fix missing fields when inserting
---
 app/Models/EntryDAO.php | 32 ++++++++++++++++++++++++--------
 1 file changed, 24 insertions(+), 8 deletions(-)

(limited to 'app/Models/EntryDAO.php')

diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index 1b2786a6a..b13c83d67 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -3,11 +3,11 @@
 class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 
 	public function isCompressed() {
-		return parent::$sharedDbType === 'mysql';
+		return true;
 	}
 
 	public function hasNativeHex() {
-		return parent::$sharedDbType !== 'sqlite';
+		return true;
 	}
 
 	public function sqlHexDecode($x) {
@@ -64,7 +64,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		}
 		$this->triedUpdateToUtf8mb4 = true;
 		$db = FreshRSS_Context::$system_conf->db;
-		if ($db['type'] === 'mysql') {
+		if ($this->bd->dbType() === 'mysql') {
 			include_once(APP_PATH . '/SQL/install.sql.mysql.php');
 			if (defined('SQL_UPDATE_UTF8MB4')) {
 				Minz_Log::warning('Updating MySQL to UTF8MB4...');	//v1.5.0
@@ -98,8 +98,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			$this->bd->commit();
 		}
 		try {
-			$db = FreshRSS_Context::$system_conf->db;
-			require_once(APP_PATH . '/SQL/install.sql.' . $db['type'] . '.php');
+			require_once(APP_PATH . '/SQL/install.sql.' . $this->bd->dbType() . '.php');
 			Minz_Log::warning('SQL CREATE TABLE entrytmp...');
 			if (defined('SQL_CREATE_TABLE_ENTRYTMP')) {
 				$sql = sprintf(SQL_CREATE_TABLE_ENTRYTMP, $this->prefix);
@@ -152,9 +151,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 
 	private $addEntryPrepared = null;
 
-	public function addEntry($valuesTmp) {
+	public function addEntry($valuesTmp, $useTmpTable = true) {
 		if ($this->addEntryPrepared == null) {
-			$sql = 'INSERT INTO `' . $this->prefix . 'entrytmp` (id, guid, title, author, '
+			$sql = 'INSERT INTO `' . $this->prefix . ($useTmpTable ? 'entrytmp' : 'entry') . '` (id, guid, title, author, '
 				. ($this->isCompressed() ? 'content_bin' : 'content')
 				. ', link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags) '
 				. 'VALUES(:id, :guid, :title, :author, '
@@ -178,7 +177,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			$valuesTmp['link'] = safe_ascii($valuesTmp['link']);
 			$this->addEntryPrepared->bindParam(':link', $valuesTmp['link']);
 			$this->addEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT);
-			$valuesTmp['lastSeen'] = time();
+			if (empty($valuesTmp['lastSeen'])) {
+				$valuesTmp['lastSeen'] = time();
+			}
 			$this->addEntryPrepared->bindParam(':last_seen', $valuesTmp['lastSeen'], PDO::PARAM_INT);
 			$valuesTmp['is_read'] = $valuesTmp['is_read'] ? 1 : 0;
 			$this->addEntryPrepared->bindParam(':is_read', $valuesTmp['is_read'], PDO::PARAM_INT);
@@ -637,6 +638,18 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		}
 	}
 
+	public function selectAll() {
+		$sql = 'SELECT id, guid, title, author, '
+			. ($this->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content')
+			. ', link, date, `lastSeen`, ' . $this->sqlHexEncode('hash') . ' AS hash, is_read, is_favorite, id_feed, tags '
+			. 'FROM `' . $this->prefix . 'entry`';
+		$stm = $this->bd->prepare($sql);
+		$stm->execute();
+		while ($row = $stm->fetch(PDO::FETCH_ASSOC)) {
+			yield $row;
+		}
+	}
+
 	public function searchByGuid($id_feed, $guid) {
 		// un guid est unique pour un flux donné
 		$sql = 'SELECT id, guid, title, author, '
@@ -991,6 +1004,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		$sql = 'SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id WHERE f.priority > 0'
 			. ' UNION SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id WHERE f.priority > 0 AND e.is_read=0';
 		$stm = $this->bd->prepare($sql);
+		if ($stm == false) {
+			return false;
+		}
 		$stm->execute();
 		$res = $stm->fetchAll(PDO::FETCH_COLUMN, 0);
 		rsort($res);
-- 
cgit v1.2.3