plugins/af_psql_trgm/init.php

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