CLI optimize database (#1663)

CLI optimize database https://github.com/FreshRSS/FreshRSS/issues/1583
And VACUUM in SQLite https://github.com/FreshRSS/FreshRSS/issues/918
Add VACUUM for PostgreSQL (Not tested yet)

6 files changed, 91 insertions, 41 deletions

diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php
index 6ba5bca3e..f5469f2b7 100644
--- a/app/Models/DatabaseDAO.php
+++ b/app/Models/DatabaseDAO.php
@@ -80,4 +80,45 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo {
 
 		return $list;
 	}
+
+	public function size($all = false) {
+		$db = FreshRSS_Context::$system_conf->db;
+		$sql = 'SELECT SUM(data_length + index_length) FROM information_schema.TABLES WHERE table_schema=?';	//MySQL
+		$values = array($db['base']);
+		if (!$all) {
+			$sql .= ' AND table_name LIKE ?';
+			$values[] = $this->prefix . '%';
+		}
+		$stm = $this->bd->prepare($sql);
+		$stm->execute($values);
+		$res = $stm->fetchAll(PDO::FETCH_COLUMN, 0);
+		return $res[0];
+	}
+
+	public function optimize() {
+		$ok = true;
+
+		$sql = 'OPTIMIZE TABLE `' . $this->prefix . 'entry`';	//MySQL
+		$stm = $this->bd->prepare($sql);
+		$ok &= $stm != false;
+		if ($stm) {
+			$ok &= $stm->execute();
+		}
+
+		$sql = 'OPTIMIZE TABLE `' . $this->prefix . 'feed`';	//MySQL
+		$stm = $this->bd->prepare($sql);
+		$ok &= $stm != false;
+		if ($stm) {
+			$ok &= $stm->execute();
+		}
+
+		$sql = 'OPTIMIZE TABLE `' . $this->prefix . 'category`';	//MySQL
+		$stm = $this->bd->prepare($sql);
+		$ok &= $stm != false;
+		if ($stm) {
+			$ok &= $stm->execute();
+		}
+
+		return $ok;
+	}
 }
diff --git a/app/Models/DatabaseDAOPGSQL.php b/app/Models/DatabaseDAOPGSQL.php
index 2a18db970..1b3f7408d 100644
--- a/app/Models/DatabaseDAOPGSQL.php
+++ b/app/Models/DatabaseDAOPGSQL.php
@@ -40,4 +40,41 @@ class FreshRSS_DatabaseDAOPGSQL extends FreshRSS_DatabaseDAO {
 			'default' => $dao['default'],
 		);
 	}
+
+	public function size($all = true) {
+		$db = FreshRSS_Context::$system_conf->db;
+		$sql = 'SELECT pg_size_pretty(pg_database_size(?))';
+		$values = array($db['base']);
+		$stm = $this->bd->prepare($sql);
+		$stm->execute($values);
+		$res = $stm->fetchAll(PDO::FETCH_COLUMN, 0);
+		return $res[0];
+	}
+
+	public function optimize() {
+		$ok = true;
+
+		$sql = 'VACUUM `' . $this->prefix . 'entry`';
+		$stm = $this->bd->prepare($sql);
+		$ok &= $stm != false;
+		if ($stm) {
+			$ok &= $stm->execute();
+		}
+
+		$sql = 'VACUUM `' . $this->prefix . 'feed`';
+		$stm = $this->bd->prepare($sql);
+		$ok &= $stm != false;
+		if ($stm) {
+			$ok &= $stm->execute();
+		}
+
+		$sql = 'VACUUM `' . $this->prefix . 'category`';
+		$stm = $this->bd->prepare($sql);
+		$ok &= $stm != false;
+		if ($stm) {
+			$ok &= $stm->execute();
+		}
+
+		return $ok;
+	}
 }
diff --git a/app/Models/DatabaseDAOSQLite.php b/app/Models/DatabaseDAOSQLite.php
index 2e1df132e..d3aedb3c0 100644
--- a/app/Models/DatabaseDAOSQLite.php
+++ b/app/Models/DatabaseDAOSQLite.php
@@ -45,4 +45,17 @@ class FreshRSS_DatabaseDAOSQLite extends FreshRSS_DatabaseDAO {
 			'default' => $dao['dflt_value'],
 		);
 	}
+
+	public function size($all = false) {
+		return @filesize(join_path(DATA_PATH, 'users', $this->current_user, 'db.sqlite'));
+	}
+
+	public function optimize() {
+		$sql = 'VACUUM';
+		$stm = $this->bd->prepare($sql);
+		if ($stm) {
+			return $stm->execute();
+		}
+		return false;
+	}
 }
diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index bebafe500..e8b6dcdae 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -885,28 +885,6 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return array('all' => $all, 'unread' => $unread, 'read' => $all - $unread);
 	}
 
-	public function optimizeTable() {
-		$sql = 'OPTIMIZE TABLE `' . $this->prefix . 'entry`';	//MySQL
-		$stm = $this->bd->prepare($sql);
-		if ($stm) {
-			return $stm->execute();
-		}
-	}
-
-	public function size($all = false) {
-		$db = FreshRSS_Context::$system_conf->db;
-		$sql = 'SELECT SUM(data_length + index_length) FROM information_schema.TABLES WHERE table_schema=?';	//MySQL
-		$values = array($db['base']);
-		if (!$all) {
-			$sql .= ' AND table_name LIKE ?';
-			$values[] = $this->prefix . '%';
-		}
-		$stm = $this->bd->prepare($sql);
-		$stm->execute($values);
-		$res = $stm->fetchAll(PDO::FETCH_COLUMN, 0);
-		return $res[0];
-	}
-
 	public static function daoToEntry($dao) {
 		$entry = new FreshRSS_Entry(
 				$dao['id_feed'],
diff --git a/app/Models/EntryDAOPGSQL.php b/app/Models/EntryDAOPGSQL.php
index 405774abf..f09fe8e75 100644
--- a/app/Models/EntryDAOPGSQL.php
+++ b/app/Models/EntryDAOPGSQL.php
@@ -46,15 +46,4 @@ END $$;';
 		}
 		return $result;
 	}
-
-	public function size($all = true) {
-		$db = FreshRSS_Context::$system_conf->db;
-		$sql = 'SELECT pg_size_pretty(pg_database_size(?))';
-		$values = array($db['base']);
-		$stm = $this->bd->prepare($sql);
-		$stm->execute($values);
-		$res = $stm->fetchAll(PDO::FETCH_COLUMN, 0);
-		return $res[0];
-	}
-
 }
diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php
index 8dad54322..0f57dc1ba 100644
--- a/app/Models/EntryDAOSQLite.php
+++ b/app/Models/EntryDAOSQLite.php
@@ -261,12 +261,4 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
 		}
 		return $affected;
 	}
-
-	public function optimizeTable() {
-		//TODO: Search for an equivalent in SQLite
-	}
-
-	public function size($all = false) {
-		return @filesize(join_path(DATA_PATH, 'users', $this->current_user, 'db.sqlite'));
-	}
 }