From b154bc7a10e46dc9fa0406996507c4fd410366da Mon Sep 17 00:00:00 2001 From: Andrew Dolgov Date: Mon, 14 Apr 2025 12:59:00 +0300 Subject: initial attempt to remove mysql-related stuff from tt-rss --- classes/Db.php | 38 +++++++------------------------------- 1 file changed, 7 insertions(+), 31 deletions(-) (limited to 'classes/Db.php') diff --git a/classes/Db.php b/classes/Db.php index 8e0e7047b..92bbec8b4 100644 --- a/classes/Db.php +++ b/classes/Db.php @@ -9,9 +9,6 @@ class Db { ORM::configure('username', Config::get(Config::DB_USER)); ORM::configure('password', Config::get(Config::DB_PASS)); ORM::configure('return_result_sets', true); - if (Config::get(Config::DB_TYPE) == "mysql" && Config::get(Config::MYSQL_CHARSET)) { - ORM::configure('driver_options', array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES ' . Config::get(Config::MYSQL_CHARSET))); - } } /** @@ -29,13 +26,8 @@ class Db { public static function get_dsn(): string { $db_port = Config::get(Config::DB_PORT) ? ';port=' . Config::get(Config::DB_PORT) : ''; $db_host = Config::get(Config::DB_HOST) ? ';host=' . Config::get(Config::DB_HOST) : ''; - if (Config::get(Config::DB_TYPE) == "mysql" && Config::get(Config::MYSQL_CHARSET)) { - $db_charset = ';charset=' . Config::get(Config::MYSQL_CHARSET); - } else { - $db_charset = ''; - } - return Config::get(Config::DB_TYPE) . ':dbname=' . Config::get(Config::DB_NAME) . $db_host . $db_port . $db_charset; + return Config::get(Config::DB_TYPE) . ':dbname=' . Config::get(Config::DB_NAME) . $db_host . $db_port; } // this really shouldn't be used unless a separate PDO connection is needed @@ -53,20 +45,10 @@ class Db { $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); - if (Config::get(Config::DB_TYPE) == "pgsql") { - - $pdo->query("set client_encoding = 'UTF-8'"); - $pdo->query("set datestyle = 'ISO, european'"); - $pdo->query("set TIME ZONE 0"); - $pdo->query("set cpu_tuple_cost = 0.5"); - - } else if (Config::get(Config::DB_TYPE) == "mysql") { - $pdo->query("SET time_zone = '+0:0'"); - - if (Config::get(Config::MYSQL_CHARSET)) { - $pdo->query("SET NAMES " . Config::get(Config::MYSQL_CHARSET)); - } - } + $pdo->query("set client_encoding = 'UTF-8'"); + $pdo->query("set datestyle = 'ISO, european'"); + $pdo->query("set TIME ZONE 0"); + $pdo->query("set cpu_tuple_cost = 0.5"); return $pdo; } @@ -90,15 +72,12 @@ class Db { } public static function sql_random_function(): string { - if (Config::get(Config::DB_TYPE) == "mysql") { - return "RAND()"; - } return "RANDOM()"; } /** * Helper to build a query part comparing a field against a past datetime (determined by "$now - $some_interval") - * + * * The example below could be read as "last_digest_sent is older than 1 day ago". * ```php * Db::past_comparison_qpart('last_digest_sent', '<', 1, 'day'); @@ -106,7 +85,6 @@ class Db { * * @todo validate value of $unit and fail if invalid (or massage if practical)? * @link https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT - * @link https://dev.mysql.com/doc/refman/9.2/en/expressions.html#temporal-intervals * @param string $field the table field being checked * @param '<'|'>'|'<='|'>='|'=' $operator the comparison operator * @param positive-int $quantity the amount of $unit @@ -114,8 +92,6 @@ class Db { * @return string the query part string */ static function past_comparison_qpart(string $field, string $operator, int $quantity, string $unit): string { - if (Config::get(Config::DB_TYPE) == 'pgsql') - return "$field $operator NOW() - INTERVAL '$quantity $unit' "; - return "$field $operator DATE_SUB(NOW(), INTERVAL $quantity $unit) "; + return "$field $operator NOW() - INTERVAL '$quantity $unit' "; } } -- cgit v1.2.3-54-g00ecf From 7e403aae92166de6e83a5ab39738ea8ffe6eb713 Mon Sep 17 00:00:00 2001 From: Andrew Dolgov Date: Mon, 14 Apr 2025 15:21:10 +0300 Subject: further mysql/DB_TYPE related cleanup --- .docker/app/Dockerfile | 1 - classes/Config.php | 7 +++- classes/Db.php | 2 +- classes/Db_Migrations.php | 2 +- plugins/af_psql_trgm/init.php | 91 +++++++++++++------------------------------ 5 files changed, 34 insertions(+), 69 deletions(-) (limited to 'classes/Db.php') diff --git a/.docker/app/Dockerfile b/.docker/app/Dockerfile index 9ca767608..786179bd5 100644 --- a/.docker/app/Dockerfile +++ b/.docker/app/Dockerfile @@ -91,7 +91,6 @@ ENV TTRSS_XDEBUG_ENABLED="" ENV TTRSS_XDEBUG_HOST="" ENV TTRSS_XDEBUG_PORT="9000" -ENV TTRSS_DB_TYPE="pgsql" ENV TTRSS_DB_HOST="db" ENV TTRSS_DB_PORT="5432" diff --git a/classes/Config.php b/classes/Config.php index f8f9aaa18..1921796db 100644 --- a/classes/Config.php +++ b/classes/Config.php @@ -18,13 +18,16 @@ class Config { * * or config.php: * - * putenv('TTRSS_DB_TYPE=pgsql'); + * putenv('TTRSS_DB_HOST=my-patroni.example.com'); * * note lack of quotes and spaces before and after "=". * */ - /** database type: pgsql */ + /** this is kept for backwards/plugin compatibility, the only supported database is PostgreSQL + * + * @deprecated could be replaced with default (and only) value: `pgsql` + */ const DB_TYPE = "DB_TYPE"; /** database server hostname */ diff --git a/classes/Db.php b/classes/Db.php index 92bbec8b4..6310d11e3 100644 --- a/classes/Db.php +++ b/classes/Db.php @@ -27,7 +27,7 @@ class Db { $db_port = Config::get(Config::DB_PORT) ? ';port=' . Config::get(Config::DB_PORT) : ''; $db_host = Config::get(Config::DB_HOST) ? ';host=' . Config::get(Config::DB_HOST) : ''; - return Config::get(Config::DB_TYPE) . ':dbname=' . Config::get(Config::DB_NAME) . $db_host . $db_port; + return 'pgsql:dbname=' . Config::get(Config::DB_NAME) . $db_host . $db_port; } // this really shouldn't be used unless a separate PDO connection is needed diff --git a/classes/Db_Migrations.php b/classes/Db_Migrations.php index 62619b377..33bc64a32 100644 --- a/classes/Db_Migrations.php +++ b/classes/Db_Migrations.php @@ -23,7 +23,7 @@ class Db_Migrations { } function initialize(string $root_path, string $migrations_table, bool $base_is_latest = true, int $max_version_override = 0): void { - $this->base_path = "$root_path/" . Config::get(Config::DB_TYPE); + $this->base_path = "$root_path/pgsql"; $this->migrations_path = $this->base_path . "/migrations"; $this->migrations_table = $migrations_table; $this->base_is_latest = $base_is_latest; diff --git a/plugins/af_psql_trgm/init.php b/plugins/af_psql_trgm/init.php index 28a0ab933..5832320e8 100644 --- a/plugins/af_psql_trgm/init.php +++ b/plugins/af_psql_trgm/init.php @@ -63,41 +63,22 @@ class Af_Psql_Trgm extends Plugin { print "

$title

"; - if (Config::get(Config::DB_TYPE) == "pgsql") { - $sth = $this->pdo->prepare("SELECT ttrss_entries.id AS id, - feed_id, - ttrss_entries.title AS title, - updated, link, - ttrss_feeds.title AS feed_title, - SIMILARITY(ttrss_entries.title, :title) AS sm - FROM - ttrss_entries, ttrss_user_entries LEFT JOIN ttrss_feeds ON (ttrss_feeds.id = feed_id) - WHERE - ttrss_entries.id = ref_id AND - ttrss_user_entries.owner_uid = :owner_uid AND - ttrss_entries.id != :id AND - date_entered >= NOW() - INTERVAL '2 weeks' - ORDER BY - sm DESC, date_entered DESC - LIMIT 10"); - } else { - $sth = $this->pdo->prepare("SELECT ttrss_entries.id AS id, - feed_id, - ttrss_entries.title AS title, - updated, link, - ttrss_feeds.title AS feed_title, - (MATCH (ttrss_entries.title) AGAINST (:title) / LENGTH(ttrss_entries.title)) AS sm - FROM - ttrss_entries, ttrss_user_entries LEFT JOIN ttrss_feeds ON (ttrss_feeds.id = feed_id) - WHERE - ttrss_entries.id = ref_id AND - ttrss_user_entries.owner_uid = :owner_uid AND - ttrss_entries.id != :id AND - date_entered >= DATE_SUB(NOW(), INTERVAL 2 WEEK) - ORDER BY - sm DESC, date_entered DESC - LIMIT 10"); - } + $sth = $this->pdo->prepare("SELECT ttrss_entries.id AS id, + feed_id, + ttrss_entries.title AS title, + updated, link, + ttrss_feeds.title AS feed_title, + SIMILARITY(ttrss_entries.title, :title) AS sm + FROM + ttrss_entries, ttrss_user_entries LEFT JOIN ttrss_feeds ON (ttrss_feeds.id = feed_id) + WHERE + ttrss_entries.id = ref_id AND + ttrss_user_entries.owner_uid = :owner_uid AND + ttrss_entries.id != :id AND + date_entered >= NOW() - INTERVAL '2 weeks' + ORDER BY + sm DESC, date_entered DESC + LIMIT 10"); $sth->execute(['title' => $title, "owner_uid" => $owner_uid, "id" => $id]); @@ -157,12 +138,10 @@ class Af_Psql_Trgm extends Plugin { title="extension "> pdo->query("select 'similarity'::regproc"); + $res = $this->pdo->query("select 'similarity'::regproc"); - if (!$res || !$res->fetch()) { - print_error("pg_trgm extension not found."); - } + if (!$res || !$res->fetch()) { + print_error("pg_trgm extension not found."); } ?>
@@ -190,11 +169,7 @@ class Af_Psql_Trgm extends Plugin { name="similarity" value="">
- - - - - +
@@ -283,10 +258,8 @@ class Af_Psql_Trgm extends Plugin { function hook_article_filter($article) { - if (Config::get(Config::DB_TYPE) == "pgsql") { - $res = $this->pdo->query("select 'similarity'::regproc"); - if (!$res || !$res->fetch()) return $article; - } + $res = $this->pdo->query("select 'similarity'::regproc"); + if (!$res || !$res->fetch()) return $article; $enable_globally = $this->host->get($this, "enable_globally"); @@ -336,21 +309,11 @@ class Af_Psql_Trgm extends Plugin { return $article; } */ - if (Config::get(Config::DB_TYPE) == "pgsql") { - $sth = $this->pdo->prepare("SELECT MAX(SIMILARITY(title, :title)) AS ms - FROM ttrss_entries, ttrss_user_entries WHERE ref_id = id AND - date_entered >= NOW() - interval '1 day' AND - guid != :guid AND - owner_uid = :uid"); - } else { - $sth = $this->pdo->prepare("SELECT (MATCH(title) AGAINST (:title) / LENGTH(title)) AS ms - FROM ttrss_entries, ttrss_user_entries WHERE ref_id = id AND - date_entered >= DATE_SUB(NOW(), INTERVAL 1 DAY) AND - guid != :guid AND - owner_uid = :uid - ORDER BY ms DESC - LIMIT 1"); - } + $sth = $this->pdo->prepare("SELECT MAX(SIMILARITY(title, :title)) AS ms + FROM ttrss_entries, ttrss_user_entries WHERE ref_id = id AND + date_entered >= NOW() - interval '1 day' AND + guid != :guid AND + owner_uid = :uid"); $sth->execute(['title' => $title_escaped, 'guid' => $entry_guid, 'uid' => $owner_uid]); -- cgit v1.2.3-54-g00ecf From 3c138a71a1749537680a23aca6d4181b5b2820f0 Mon Sep 17 00:00:00 2001 From: Andrew Dolgov Date: Mon, 14 Apr 2025 15:27:58 +0300 Subject: add deprecation notice for sql_random_function() --- classes/Db.php | 1 + classes/RPC.php | 4 +--- plugins/cache_starred_images/init.php | 2 +- 3 files changed, 3 insertions(+), 4 deletions(-) (limited to 'classes/Db.php') diff --git a/classes/Db.php b/classes/Db.php index 6310d11e3..872c6b351 100644 --- a/classes/Db.php +++ b/classes/Db.php @@ -71,6 +71,7 @@ class Db { return self::$instance->pdo; } + /** @deprecated usages should be replaced with `RANDOM()` */ public static function sql_random_function(): string { return "RANDOM()"; } diff --git a/classes/RPC.php b/classes/RPC.php index bad51e4aa..031bef509 100644 --- a/classes/RPC.php +++ b/classes/RPC.php @@ -269,8 +269,6 @@ class RPC extends Handler_Protected { $updstart_thresh_qpart = 'AND (last_update_started IS NULL OR ' . Db::past_comparison_qpart('last_update_started', '<', 5, 'minute') . ')'; - $random_qpart = Db::sql_random_function(); - $pdo = Db::pdo(); // we could be invoked from public.php with no active session @@ -290,7 +288,7 @@ class RPC extends Handler_Protected { $owner_check_qpart $update_limit_qpart $updstart_thresh_qpart - ORDER BY $random_qpart LIMIT 30"; + ORDER BY RANDOM() LIMIT 30"; $res = $pdo->query($query); diff --git a/plugins/cache_starred_images/init.php b/plugins/cache_starred_images/init.php index cc1eef0dc..48c8df3db 100755 --- a/plugins/cache_starred_images/init.php +++ b/plugins/cache_starred_images/init.php @@ -59,7 +59,7 @@ class Cache_Starred_Images extends Plugin { site_url != '' AND ttrss_user_entries.owner_uid = ? AND plugin_data NOT LIKE '%starred_cache_images%' - ORDER BY ".Db::sql_random_function()." LIMIT 100"); + ORDER BY RANDOM() LIMIT 100"); if ($sth->execute([$this->host->get_owner_uid()])) { -- cgit v1.2.3-54-g00ecf