[livejournal] r23348: LJSUP-14246: Improve admin/spam_urls.bml...

U   trunk/bin/maint/clean_caches.pl
U   trunk/bin/upgrading/update-db-general.pl
U   trunk/cgi-bin/LJ/Talk/Post.pm
U   trunk/cgi-bin/LJ/Talk.pm

Modified: trunk/bin/maint/clean_caches.pl
===================================================================
--- trunk/bin/maint/clean_caches.pl	2012-11-22 11:56:13 UTC (rev 23347)
+++ trunk/bin/maint/clean_caches.pl	2012-11-23 07:28:18 UTC (rev 23348)
@@ -19,6 +19,7 @@
 
     print "-I- Cleaning commenturl.\n";
     $dbh->do("DELETE FROM commenturls WHERE timecreate < UNIX_TIMESTAMP() - 86400*30 LIMIT 50000");
+    $dbh->do("DELETE FROM commenturls2 WHERE timecreate < UNIX_TIMESTAMP() - 86400*30 LIMIT 50000");
 
     print "-I- Cleaning syslog table.\n";
     $dbh->do("DELETE FROM syslog WHERE log_time < UNIX_TIMESTAMP() - 86400 * 30 * 2");  ## 2 months

Modified: trunk/bin/upgrading/update-db-general.pl
===================================================================
--- trunk/bin/upgrading/update-db-general.pl	2012-11-22 11:56:13 UTC (rev 23347)
+++ trunk/bin/upgrading/update-db-general.pl	2012-11-23 07:28:18 UTC (rev 23348)
@@ -347,6 +347,24 @@
 )
 EOC
 
+register_tablecreate('commenturls2', <<'EOC');
+CREATE TABLE `commenturls2` (
+  `posterid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
+  `journalid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
+  `jtalkid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
+  `timecreate` INT(10) UNSIGNED NOT NULL DEFAULT '0',
+  `host` VARCHAR(255) NULL DEFAULT '',
+  `crc_host` INT(10) UNSIGNED NOT NULL DEFAULT '0',
+  `url` VARCHAR(255) NOT NULL DEFAULT '',
+  `crc_url` INT(10) UNSIGNED NOT NULL DEFAULT '0',
+  `ip` VARCHAR(15) NULL DEFAULT NULL,
+  `status` CHAR(1) NULL DEFAULT '',
+  KEY `host` (`crc_host`, `timecreate`),
+  KEY `status` (`status`, `timecreate`),
+  KEY `url` (`crc_url`, `timecreate`)
+)
+EOC
+
 register_tablecreate('comminterests', <<'EOC');
 CREATE TABLE `comminterests` (
   `userid` int(10) unsigned NOT NULL DEFAULT '0',

Modified: trunk/cgi-bin/LJ/Talk/Post.pm
===================================================================
--- trunk/cgi-bin/LJ/Talk/Post.pm	2012-11-22 11:56:13 UTC (rev 23347)
+++ trunk/cgi-bin/LJ/Talk/Post.pm	2012-11-23 07:28:18 UTC (rev 23348)
@@ -1,6 +1,7 @@
 package LJ::Talk::Post;
 use strict;
 
+use LJ::Admin::Spam::Urls;
 use LJ::EventLogRecord::NewComment;
 
 sub indent {
@@ -181,25 +182,16 @@
     }
 
     # record up to 25 (or $LJ::TALK_MAX_URLS) urls from a comment
-    my (%urls, $dbh);
-    if ($LJ::TALK_MAX_URLS &&
-        ( %urls = map { $_ => 1 } LJ::get_urls($comment->{body}) ) &&
-        ( $dbh = LJ::get_db_writer() )) # don't log if no db available
-    {
-        my (@bind, @vals);
-        my $ip = LJ::get_remote_ip();
-        while (my ($url, undef) = each %urls) {
-            push @bind, '(?,?,?,?,UNIX_TIMESTAMP(),?)';
-            push @vals, $posterid, $journalu->{userid}, $ip, $jtalkid, $url;
-            last if @bind >= $LJ::TALK_MAX_URLS;
-        }
-        my $bind = join(',', @bind);
-        my $sql = qq{
-            INSERT INTO commenturls
-                (posterid, journalid, ip, jtalkid, timecreate, url)
-            VALUES $bind
-        };
-        $dbh->do($sql, undef, @vals);
+    if ($LJ::TALK_MAX_URLS) {
+        my %urls = map { $_ => 1 } LJ::get_urls($comment->{body});
+
+        LJ::Admin::Spam::Urls::insert(
+            $posterid,
+            $journalu->{userid},
+            $jtalkid,
+            $comment->{state} eq 'B' ? 'S' : '',
+            keys %urls
+        );
     }
 
     # update the "replycount" summary field of the log table

Modified: trunk/cgi-bin/LJ/Talk.pm
===================================================================
--- trunk/cgi-bin/LJ/Talk.pm	2012-11-22 11:56:13 UTC (rev 23347)
+++ trunk/cgi-bin/LJ/Talk.pm	2012-11-23 07:28:18 UTC (rev 23348)
@@ -1,6 +1,15 @@
 package LJ::Talk;
 use strict;
 
+#########################
+# Types of state:
+# A - active
+# S - screened
+# D - deleted
+# B - spam
+# F - frozen
+#########################
+
 use Captcha::reCAPTCHA;
 use Carp qw(croak);
 use MIME::Words;