From e3e5954394f4523850c78e80e496f1b916622677 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 29 Sep 2019 16:22:50 +0200 Subject: PDO refactoring for code simplification (#2522) * PDO refactor * Automatic prefix when using the syntax `_tableName` * Uniformity: MySQL is now PDO::ATTR_EMULATE_PREPARES = false just like SQLite and PostgreSQL, with consequences such as only one statement per query * Use PDO methods exec(), query(), prepare() + execute() in a more efficient way * Remove auto-update SQL code for versions older than FreshRSS 1.5 (3 years old) * The name of the default category is set in PHP instead of in the DB (simplies SQL and allows changing the name according to the FreshRSS language) * Rename `->bd` to `->pdo` (less of a frenshism, and more informative) * Fix some requests, which were not compatible with MySQL prepared statements * Whitespace * Fix syntax for PostgreSQL sequences + MySQL install * Minor formatting * Fix lastInsertId for PostgreSQL * Use PHP 5.6+ const Take advantage of https://github.com/FreshRSS/FreshRSS/pull/2527 https://www.php.net/manual/en/migration56.new-features.php * A bit of forgotten PHP 5.6 simplification for cURL * Forgotten $s * Mini fix custom user config https://github.com/FreshRSS/FreshRSS/pull/2490/files#r326290346 * More work on install.php but not finished * install.php working * More cleaning of PDO in install * Even more simplification Take advantage of PDO->exec() to run multiple statements * Disallow changing the name of the default category https://github.com/FreshRSS/FreshRSS/pull/2522#discussion_r326967724 --- app/Models/DatabaseDAOPGSQL.php | 28 ++++++++++++---------------- 1 file changed, 12 insertions(+), 16 deletions(-) (limited to 'app/Models/DatabaseDAOPGSQL.php') diff --git a/app/Models/DatabaseDAOPGSQL.php b/app/Models/DatabaseDAOPGSQL.php index 8582b5719..60aceacc2 100644 --- a/app/Models/DatabaseDAOPGSQL.php +++ b/app/Models/DatabaseDAOPGSQL.php @@ -13,18 +13,18 @@ class FreshRSS_DatabaseDAOPGSQL extends FreshRSS_DatabaseDAOSQLite { $db = FreshRSS_Context::$system_conf->db; $dbowner = $db['user']; $sql = 'SELECT * FROM pg_catalog.pg_tables where tableowner=?'; - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); $values = array($dbowner); $stm->execute($values); $res = $stm->fetchAll(PDO::FETCH_ASSOC); $tables = array( - $this->prefix . 'category' => false, - $this->prefix . 'feed' => false, - $this->prefix . 'entry' => false, - $this->prefix . 'entrytmp' => false, - $this->prefix . 'tag' => false, - $this->prefix . 'entrytag' => false, + $this->pdo->prefix() . 'category' => false, + $this->pdo->prefix() . 'feed' => false, + $this->pdo->prefix() . 'entry' => false, + $this->pdo->prefix() . 'entrytmp' => false, + $this->pdo->prefix() . 'tag' => false, + $this->pdo->prefix() . 'entrytag' => false, ); foreach ($res as $value) { $tables[array_pop($value)] = true; @@ -35,8 +35,8 @@ class FreshRSS_DatabaseDAOPGSQL extends FreshRSS_DatabaseDAOSQLite { public function getSchema($table) { $sql = 'select column_name as field, data_type as type, column_default as default, is_nullable as null from INFORMATION_SCHEMA.COLUMNS where table_name = ?'; - $stm = $this->bd->prepare($sql); - $stm->execute(array($this->prefix . $table)); + $stm = $this->pdo->prepare($sql); + $stm->execute(array($this->pdo->prefix() . $table)); return $this->listDaoToSchema($stm->fetchAll(PDO::FETCH_ASSOC)); } @@ -53,7 +53,7 @@ class FreshRSS_DatabaseDAOPGSQL extends FreshRSS_DatabaseDAOSQLite { $db = FreshRSS_Context::$system_conf->db; $sql = 'SELECT pg_size_pretty(pg_database_size(?))'; $values = array($db['base']); - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); $stm->execute($values); $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); return $res[0]; @@ -64,12 +64,8 @@ class FreshRSS_DatabaseDAOPGSQL extends FreshRSS_DatabaseDAOSQLite { $tables = array('category', 'feed', 'entry', 'entrytmp', 'tag', 'entrytag'); foreach ($tables as $table) { - $sql = 'VACUUM `' . $this->prefix . $table . '`'; - $stm = $this->bd->prepare($sql); - $ok &= $stm != false; - if ($stm) { - $ok &= $stm->execute(); - } + $sql = 'VACUUM `_' . $table . '`'; + $ok &= ($this->pdo->exec($sql) !== false); } return $ok; } -- cgit v1.2.3 From 3b65f2e5867b3da176be9e976267633eeccf6184 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 6 Oct 2019 22:17:28 +0200 Subject: Fix PostgreSQL and SQLite DB size estimation (#2562) --- app/Models/DatabaseDAOPGSQL.php | 23 +++++++++++++++++------ app/Models/DatabaseDAOSQLite.php | 10 +++++++++- app/Models/EntryDAO.php | 3 +++ 3 files changed, 29 insertions(+), 7 deletions(-) (limited to 'app/Models/DatabaseDAOPGSQL.php') diff --git a/app/Models/DatabaseDAOPGSQL.php b/app/Models/DatabaseDAOPGSQL.php index 60aceacc2..1a6b3599e 100644 --- a/app/Models/DatabaseDAOPGSQL.php +++ b/app/Models/DatabaseDAOPGSQL.php @@ -49,12 +49,23 @@ class FreshRSS_DatabaseDAOPGSQL extends FreshRSS_DatabaseDAOSQLite { ); } - 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->pdo->prepare($sql); - $stm->execute($values); + public function size($all = false) { + if ($all) { + $db = FreshRSS_Context::$system_conf->db; + $sql = 'SELECT pg_database_size(:base)'; + $stm = $this->pdo->prepare($sql); + $stm->bindParam(':base', $db['base']); + $stm->execute(); + } else { + $sql = "SELECT " + . "pg_total_relation_size('{$this->pdo->prefix()}category') + " + . "pg_total_relation_size('{$this->pdo->prefix()}feed') + " + . "pg_total_relation_size('{$this->pdo->prefix()}entry') + " + . "pg_total_relation_size('{$this->pdo->prefix()}entrytmp') + " + . "pg_total_relation_size('{$this->pdo->prefix()}tag') + " + . "pg_total_relation_size('{$this->pdo->prefix()}entrytag')"; + $stm = $this->pdo->query($sql); + } $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); return $res[0]; } diff --git a/app/Models/DatabaseDAOSQLite.php b/app/Models/DatabaseDAOSQLite.php index 11900979e..b1473ab09 100644 --- a/app/Models/DatabaseDAOSQLite.php +++ b/app/Models/DatabaseDAOSQLite.php @@ -54,7 +54,15 @@ class FreshRSS_DatabaseDAOSQLite extends FreshRSS_DatabaseDAO { } public function size($all = false) { - return @filesize(join_path(DATA_PATH, 'users', $this->current_user, 'db.sqlite')); + $sum = 0; + if ($all) { + foreach (glob(DATA_PATH . '/users/*/db.sqlite') as $filename) { + $sum += @filesize($filename); + } + } else { + $sum = @filesize(DATA_PATH . '/users/' . $this->current_user . '/db.sqlite'); + } + return $sum; } public function optimize() { diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index cf896eafd..5ff3a5b70 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -951,6 +951,9 @@ SQL; $sql .= ' WHERE f.priority > ' . intval($minPriority); } $stm = $this->pdo->query($sql); + if ($stm == false) { + return false; + } $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); return isset($res[0]) ? $res[0] : 0; } -- cgit v1.2.3