plugins/af_psql_trgm/init.php

   1 <?php
   2 class Af_Psql_Trgm extends Plugin {
   3
   4         /* @var PluginHost $host */
   5         private $host;
   6
   7         function about() {
   8                 return array(1.0,
   9                         "Marks similar articles as read (requires pg_trgm)",
  10                         "fox");
  11         }
  12
  13         function save() {
  14                 $similarity = (float) $_POST["similarity"];
  15                 $min_title_length = (int) $_POST["min_title_length"];
  16                 $enable_globally = checkbox_to_sql_bool($_POST["enable_globally"]);
  17
  18                 if ($similarity < 0) $similarity = 0;
  19                 if ($similarity > 1) $similarity = 1;
  20
  21                 if ($min_title_length < 0) $min_title_length = 0;
  22
  23                 $similarity = sprintf("%.2f", $similarity);
  24
  25                 $this->host->set($this, "similarity", $similarity);
  26                 $this->host->set($this, "min_title_length", $min_title_length);
  27                 $this->host->set($this, "enable_globally", $enable_globally);
  28
  29                 echo T_sprintf("Data saved (%s, %d)", $similarity, $enable_globally);
  30         }
  31
  32         function init($host) {
  33                 $this->host = $host;
  34
  35                 $host->add_hook($host::HOOK_ARTICLE_FILTER, $this);
  36                 $host->add_hook($host::HOOK_PREFS_TAB, $this);
  37                 $host->add_hook($host::HOOK_PREFS_EDIT_FEED, $this);
  38                 $host->add_hook($host::HOOK_PREFS_SAVE_FEED, $this);
  39                 $host->add_hook($host::HOOK_ARTICLE_BUTTON, $this);
  40
  41         }
  42
  43         function get_js() {
  44                 return file_get_contents(__DIR__ . "/init.js");
  45         }
  46
  47         function showrelated() {
  48                 $id = (int) $_REQUEST['param'];
  49                 $owner_uid = $_SESSION["uid"];
  50
  51                 $sth = $this->pdo->prepare("SELECT title FROM ttrss_entries, ttrss_user_entries
  52                         WHERE ref_id = id AND id = ? AND owner_uid = ?");
  53                 $sth->execute([$id, $owner_uid]);
  54
  55                 if ($row = $sth->fetch()) {
  56
  57                         $title = $row['title'];
  58
  59                         print "<h2>$title</h2>";
  60
  61                         $sth = $this->pdo->prepare("SELECT ttrss_entries.id AS id,
  62                                 feed_id,
  63                                 ttrss_entries.title AS title,
  64                                 updated, link,
  65                                 ttrss_feeds.title AS feed_title,
  66                                 SIMILARITY(ttrss_entries.title, '$title') AS sm
  67                         FROM
  68                                 ttrss_entries, ttrss_user_entries LEFT JOIN ttrss_feeds ON (ttrss_feeds.id = feed_id)
  69                         WHERE
  70                                 ttrss_entries.id = ref_id AND
  71                                 ttrss_user_entries.owner_uid = ? AND
  72                                 ttrss_entries.id != ? AND
  73                                 date_entered >= NOW() - INTERVAL '2 weeks'
  74                         ORDER BY
  75                                 sm DESC, date_entered DESC
  76                         LIMIT 10");
  77
  78                         $sth->execute([$owner_uid, $id]);
  79
  80                         print "<ul class=\"browseFeedList\" style=\"border-width : 1px\">";
  81
  82                         while ($line = $sth->fetch()) {
  83                                 print "<li>";
  84                                 print "<div class='insensitive small' style='margin-left : 20px; float : right'>" .
  85                                         smart_date_time(strtotime($line["updated"]))
  86                                         . "</div>";
  87
  88                                 $sm = sprintf("%.2f", $line['sm']);
  89                                 print "<img src='images/score_high.png' title='$sm'
  90                                 style='vertical-align : middle'>";
  91
  92                                 $article_link = htmlspecialchars($line["link"]);
  93                                 print " <a target=\"_blank\" rel=\"noopener noreferrer\" href=\"$article_link\">".
  94                                         $line["title"]."</a>";
  95
  96                                 print " (<a href=\"#\" onclick=\"viewfeed({feed:".$line["feed_id"]."})\">".
  97                                         htmlspecialchars($line["feed_title"])."</a>)";
  98
  99                                 print " <span class='insensitive'>($sm)</span>";
 100
 101                                 print "</li>";
 102                         }
 103
 104                         print "</ul>";
 105
 106                 }
 107
 108                 print "<div style='text-align : center'>";
 109                 print "<button dojoType=\"dijit.form.Button\" onclick=\"dijit.byId('trgmRelatedDlg').hide()\">".__('Close this window')."</button>";
 110                 print "</div>";
 111
 112
 113         }
 114
 115         function hook_article_button($line) {
 116                 return "<img src=\"plugins/af_psql_trgm/button.png\"
 117                         style=\"cursor : pointer\" style=\"cursor : pointer\"
 118                         onclick=\"showTrgmRelated(".$line["id"].")\"
 119                         class='tagsPic' title='".__('Show related articles')."'>";
 120         }
 121
 122         function hook_prefs_tab($args) {
 123                 if ($args != "prefFeeds") return;
 124
 125                 print "<div dojoType=\"dijit.layout.AccordionPane\" title=\"".__('Mark similar articles as read')."\">";
 126
 127                 if (DB_TYPE != "pgsql") {
 128                         print_error("Database type not supported.");
 129                 } else {
 130
 131                         $res = $this->pdo->query("select 'similarity'::regproc");
 132
 133                         if (!$res->fetch()) {
 134                                 print_error("pg_trgm extension not found.");
 135                         }
 136
 137                         $similarity = $this->host->get($this, "similarity");
 138                         $min_title_length = $this->host->get($this, "min_title_length");
 139                         $enable_globally = $this->host->get($this, "enable_globally");
 140
 141                         if (!$similarity) $similarity = '0.75';
 142                         if (!$min_title_length) $min_title_length = '32';
 143
 144                         print "<form dojoType=\"dijit.form.Form\">";
 145
 146                         print "<script type=\"dojo/method\" event=\"onSubmit\" args=\"evt\">
 147                                 evt.preventDefault();
 148                                 if (this.validate()) {
 149                                         console.log(dojo.objectToQuery(this.getValues()));
 150                                         new Ajax.Request('backend.php', {
 151                                                 parameters: dojo.objectToQuery(this.getValues()),
 152                                                 onComplete: function(transport) {
 153                                                         notify_info(transport.responseText);
 154                                                 }
 155                                         });
 156                                         //this.reset();
 157                                 }
 158                                 </script>";
 159
 160                         print_hidden("op", "pluginhandler");
 161                         print_hidden("method", "save");
 162                         print_hidden("plugin", "af_psql_trgm");
 163
 164                         print "<p>" . __("PostgreSQL trigram extension returns string similarity as a floating point number (0-1). Setting it too low might produce false positives, zero disables checking.") . "</p>";
 165                         print_notice("Enable the plugin for specific feeds in the feed editor.");
 166
 167                         print "<h3>" . __("Global settings") . "</h3>";
 168
 169                         print "<table>";
 170
 171                         print "<tr><td width=\"40%\">" . __("Minimum similarity:") . "</td>";
 172                         print "<td>
 173                                 <input dojoType=\"dijit.form.ValidationTextBox\"
 174                                 placeholder=\"0.75\"
 175                                 required=\"1\" name=\"similarity\" value=\"$similarity\"></td></tr>";
 176                         print "<tr><td width=\"40%\">" . __("Minimum title length:") . "</td>";
 177                         print "<td>
 178                                 <input dojoType=\"dijit.form.ValidationTextBox\"
 179                                 placeholder=\"32\"
 180                                 required=\"1\" name=\"min_title_length\" value=\"$min_title_length\"></td></tr>";
 181                         print "<tr><td width=\"40%\">" . __("Enable for all feeds:") . "</td>";
 182                         print "<td>";
 183                         print_checkbox("enable_globally", $enable_globally);
 184                         print "</td></tr>";
 185
 186                         print "</table>";
 187
 188                         print "<p>"; print_button("submit", __("Save"));
 189                         print "</form>";
 190
 191                         $enabled_feeds = $this->host->get($this, "enabled_feeds");
 192                         if (!array($enabled_feeds)) $enabled_feeds = array();
 193
 194                         $enabled_feeds = $this->filter_unknown_feeds($enabled_feeds);
 195                         $this->host->set($this, "enabled_feeds", $enabled_feeds);
 196
 197                         if (count($enabled_feeds) > 0) {
 198                                 print "<h3>" . __("Currently enabled for (click to edit):") . "</h3>";
 199
 200                                 print "<ul class=\"browseFeedList\" style=\"border-width : 1px\">";
 201                                 foreach ($enabled_feeds as $f) {
 202                                         print "<li>" .
 203                                                 "<img src='images/pub_set.png'
 204                                                         style='vertical-align : middle'> <a href='#'
 205                                                         onclick='editFeed($f)'>" .
 206                                                 Feeds::getFeedTitle($f) . "</a></li>";
 207                                 }
 208                                 print "</ul>";
 209                         }
 210                 }
 211
 212                 print "</div>";
 213         }
 214
 215         function hook_prefs_edit_feed($feed_id) {
 216                 print "<div class=\"dlgSec\">".__("Similarity (pg_trgm)")."</div>";
 217                 print "<div class=\"dlgSecCont\">";
 218
 219                 $enabled_feeds = $this->host->get($this, "enabled_feeds");
 220                 if (!array($enabled_feeds)) $enabled_feeds = array();
 221
 222                 $key = array_search($feed_id, $enabled_feeds);
 223                 $checked = $key !== FALSE ? "checked" : "";
 224
 225                 print "<hr/><input dojoType=\"dijit.form.CheckBox\" type=\"checkbox\" id=\"trgm_similarity_enabled\"
 226                         name=\"trgm_similarity_enabled\"
 227                         $checked>&nbsp;<label for=\"trgm_similarity_enabled\">".__('Mark similar articles as read')."</label>";
 228
 229                 print "</div>";
 230         }
 231
 232         function hook_prefs_save_feed($feed_id) {
 233                 $enabled_feeds = $this->host->get($this, "enabled_feeds");
 234                 if (!is_array($enabled_feeds)) $enabled_feeds = array();
 235
 236                 $enable = checkbox_to_sql_bool($_POST["trgm_similarity_enabled"]);
 237                 $key = array_search($feed_id, $enabled_feeds);
 238
 239                 if ($enable) {
 240                         if ($key === FALSE) {
 241                                 array_push($enabled_feeds, $feed_id);
 242                         }
 243                 } else {
 244                         if ($key !== FALSE) {
 245                                 unset($enabled_feeds[$key]);
 246                         }
 247                 }
 248
 249                 $this->host->set($this, "enabled_feeds", $enabled_feeds);
 250         }
 251
 252         function hook_article_filter($article) {
 253
 254                 if (DB_TYPE != "pgsql") return $article;
 255
 256                 $res = $this->pdo->query("select 'similarity'::regproc");
 257                 if (!$res->fetch()) return $article;
 258
 259                 $enable_globally = $this->host->get($this, "enable_globally");
 260
 261                 if (!$enable_globally) {
 262                         $enabled_feeds = $this->host->get($this, "enabled_feeds");
 263                         $key = array_search($article["feed"]["id"], $enabled_feeds);
 264                         if ($key === FALSE) return $article;
 265                 }
 266
 267                 $similarity = (float) $this->host->get($this, "similarity");
 268                 if ($similarity < 0.01) return $article;
 269
 270                 $min_title_length = (int) $this->host->get($this, "min_title_length");
 271                 if (mb_strlen($article["title"]) < $min_title_length) return $article;
 272
 273                 $owner_uid = $article["owner_uid"];
 274                 $entry_guid = $article["guid_hashed"];
 275                 $title_escaped = $article["title"];
 276
 277                 // trgm does not return similarity=1 for completely equal strings
 278
 279                 $sth = $this->pdo->prepare("SELECT COUNT(id) AS nequal
 280                   FROM ttrss_entries, ttrss_user_entries WHERE ref_id = id AND
 281                   date_entered >= NOW() - interval '3 days' AND
 282                   title = ? AND
 283                   guid != ? AND
 284                   owner_uid = ?");
 285                 $sth->execute([$title_escaped, $entry_guid, $owner_uid]);
 286
 287                 $row = $sth->fetch();
 288                 $nequal = $row['nequal'];
 289
 290                 Debug::log("af_psql_trgm: num equals: $nequal", Debug::$LOG_EXTENDED);
 291
 292                 if ($nequal != 0) {
 293                         $article["force_catchup"] = true;
 294                         return $article;
 295                 }
 296
 297                 $sth = $this->pdo->prepare("SELECT MAX(SIMILARITY(title, ?)) AS ms
 298                   FROM ttrss_entries, ttrss_user_entries WHERE ref_id = id AND
 299                   date_entered >= NOW() - interval '1 day' AND
 300                   guid != ? AND
 301                   owner_uid = ?");
 302                 $sth->execute([$title_escaped, $entry_guid, $owner_uid]);
 303
 304                 $row = $sth->fetch();
 305                 $similarity_result = $row['ms'];
 306
 307                 Debug::log("af_psql_trgm: similarity result: $similarity_result", Debug::$LOG_EXTENDED);
 308
 309                 if ($similarity_result >= $similarity) {
 310                         $article["force_catchup"] = true;
 311                 }
 312
 313                 return $article;
 314
 315         }
 316
 317         function api_version() {
 318                 return 2;
 319         }
 320
 321         private function filter_unknown_feeds($enabled_feeds) {
 322                 $tmp = array();
 323
 324                 foreach ($enabled_feeds as $feed) {
 325
 326                         $sth = $this->pdo->prepare("SELECT id FROM ttrss_feeds WHERE id = ? AND owner_uid = ?");
 327                         $sth->execute([$feed, $_SESSION['uid']]);
 328
 329                         if ($row = $sth->fetch()) {
 330                                 array_push($tmp, $feed);
 331                         }
 332                 }
 333
 334                 return $tmp;
 335         }
 336
 337 }