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)

1 files changed, 41 insertions, 0 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;
+	}
 }