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