diff options
author | Sergey Poznyakoff <gray@gnu.org.ua> | 2017-08-30 16:49:51 +0200 |
---|---|---|
committer | Sergey Poznyakoff <gray@gnu.org.ua> | 2017-08-30 16:49:51 +0200 |
commit | 710988ccd5e60087f390d34a33956a2aa6b9596e (patch) | |
tree | 69ce78b5a72584110ec4dfc0cee073562de0496b | |
parent | f4d18eefe7ba374799371ec96e93991e13a552fc (diff) | |
download | savane-gray-710988ccd5e60087f390d34a33956a2aa6b9596e.tar.gz savane-gray-710988ccd5e60087f390d34a33956a2aa6b9596e.tar.bz2 |
sv_spamchecker: add new modes
Two new modes are implemented: 'userscore' recomputes the spam score for
the list of users, and 'mark' flags all items and comments submitted by
the given user or users for further spam checking.
* backend/misc/sv_spamchecker: Two new operation modes: userscore and
mark.
* lib/Savane/DB.pm (db_insert): Provide alternative signature. It makes
it possible to specify the ON DUPLICATE clause.
* db/mysql/table_bugs.structure: Add a key for submitted_by
* db/mysql/table_cookbook.structure: Likewise.
* db/mysql/table_patch.structure: Likewise.
* db/mysql/table_support.structure: Likewise.
* db/mysql/table_task.structure: Likewise.
* db/mysql/table_trackers_spamcheck_queue.structure: Change definition
of the 'date' column. Add a unique key on artifact, item_id and comment_id.
* db/mysql/table_trackers_spamscore.structure: Add a uniqe key.
* frontend/php/include/spam.php (spam_get_user_score): Resume diagnosed
as spam adds 8 to the total score.
(spam_add_to_spamcheck_queue): Handle resume pseudo-tracker.
* frontend/php/my/admin/resume.php: if the user is not a member of any
group, schedule his resume for spam checking.
-rwxr-xr-x | backend/misc/sv_spamchecker | 502 | ||||
-rw-r--r-- | db/mysql/table_bugs.structure | 3 | ||||
-rw-r--r-- | db/mysql/table_cookbook.structure | 3 | ||||
-rw-r--r-- | db/mysql/table_patch.structure | 3 | ||||
-rw-r--r-- | db/mysql/table_support.structure | 3 | ||||
-rw-r--r-- | db/mysql/table_task.structure | 3 | ||||
-rw-r--r-- | db/mysql/table_trackers_spamcheck_queue.structure | 7 | ||||
-rw-r--r-- | db/mysql/table_trackers_spamscore.structure | 3 | ||||
-rw-r--r-- | frontend/php/include/spam.php | 15 | ||||
-rw-r--r-- | frontend/php/include/trackers/data.php | 2 | ||||
-rw-r--r-- | frontend/php/my/admin/resume.php | 21 | ||||
-rw-r--r-- | lib/Savane/DB.pm | 40 |
12 files changed, 457 insertions, 148 deletions
diff --git a/backend/misc/sv_spamchecker b/backend/misc/sv_spamchecker index 5f10fc6..748732d 100755 --- a/backend/misc/sv_spamchecker +++ b/backend/misc/sv_spamchecker @@ -24,6 +24,7 @@ use Time::Local; use Data::Dumper; use Mail::SpamAssassin::Client; use Savane::Conf; +use Carp; use constant TYPE_SPAM => 0; use constant TYPE_HAM => 1; @@ -38,6 +39,7 @@ my $sys_spamc_timeout = GetConf('backend.sv_spamchecker.timeout'); my $sys_spamc_username = GetConf('backend.sv_spamchecker.user'); my $sys_spamcheck_expiry = GetConf('backend.sv_spamchecker.expiry'); my $sys_spamcheck_spamassassin = GetConf('backend.sv_spamchecker.check'); +my $reporter_uid = 101; # FIXME! # End of configuration variables; my $verbose; @@ -73,122 +75,217 @@ sub parse_time_interval { return $result; } -sub check_queue { - my $now = strftime("%r", localtime); - debug("processing queue"); - foreach my $ent (GetDBHashArray("trackers_spamcheck_queue", - "1 ORDER BY priority DESC, date ASC", - "queue_id,artifact as tracker,item_id,comment_id")) { - - debug("checking entry: queue_id=$ent->{queue_id}, tracker=$ent->{tracker}, item_id=$ent->{item_id}, comment_id=$ent->{comment_id}"); - - DeleteDB("trackers_spamcheck_queue", - "queue_id='$ent->{queue_id}'") - unless dry_run(); - - # Extract real content - my $sender_ip; - my $subject; - my $text; - my $spamscore; - my $uid; - - if ($ent->{comment_id}) { - ($sender_ip, $text, $spamscore, $uid) = - GetDBSettings('ip,old_value,spamscore,mod_by', - $ent->{tracker}."_history", - 'bug_id=? AND field_name=? AND bug_history_id=? LIMIT 1', - $ent->{item_id}, - 'details', - $ent->{comment_id}); - # Build a subject from scratch - $subject = "Comment posted by $sender_ip"; +use constant { + U_ALL => 0, + U_IDS => 1, + U_NAMES => 2 +}; + +# update_user_spamscores([ARRAYREF, ARRAY_HAS_NAMES]) +sub update_user_spamscores { + my ($mode, $ar) = @_; + + my $cond = ''; + if ($mode != U_ALL) { + return unless $#{$ar} >= 0; + my $column; + if ($mode == U_IDS) { + $column = 'user_id'; + } elsif ($mode == U_NAMES) { + $column = 'user_name'; } else { - ($sender_ip, $subject, $text, $spamscore, $uid) = - GetDBSettings('ip,summary,details,spamscore,submitted_by', - $ent->{tracker}, - 'bug_id=? LIMIT 1', - $ent->{item_id}); + confess "wrong usage of update_user_spamscores"; } + $cond = ' AND u.' . $column . ' IN(' + . join(',', ('?') x @$ar) + . ')'; + } + + db_foreach(sub { + my $ref = shift; + return if dry_run(); + db_modify(q{UPDATE user SET spamscore=? WHERE user_id=?}, + int($ref->{score} + 0.5), $ref->{uid}); + }, + qq{ +SELECT u.user_id AS uid, + avg(IF(t.artifact = 'resume', 2 * t.score, t.score)) AS score + FROM user u, trackers_spamscore t + WHERE u.status='A' AND u.user_id=t.affected_user_id$cond + GROUP BY 1}, + @$ar); +} + +sub check_tracker_comment { + my ($tracker, $item_id, $comment_id, %opts) = @_; + + debug("checking entry: " + . (exists($opts{queue_id}) ? "queue_id=$opts{queue_id}, " : '') + . "tracker=$tracker, item_id=$item_id, comment_id=$comment_id"); + + if (exists($opts{queue_id})) { + db_delete(table => "trackers_spamcheck_queue", + cond => 'queue_id=?', + args => [ $opts{queue_id} ], + dry_run => dry_run()); + } + + # Extract real content + my $notify = $opts{notify} || 1; + + my $sender_ip; + my $subject; + my $text; + my $spamscore; + my $uid; + + if ($tracker eq 'resume') { + ($text, $spamscore) = GetDBSettings('people_resume,resume_spamscore', + 'user', + 'user_id=?', + $item_id); + $uid = $item_id; + $subject = "Resume submitted by $uid"; + } elsif ($comment_id) { + ($sender_ip, $text, $spamscore, $uid) = + GetDBSettings('ip,old_value,spamscore,mod_by', + $tracker."_history", + 'bug_id=? AND field_name=? AND bug_history_id=? LIMIT 1', + $item_id, + 'details', + $comment_id); + # Build a subject from scratch + $subject = "Comment posted by $sender_ip"; + } else { + ($sender_ip, $subject, $text, $spamscore, $uid) = + GetDBSettings('ip,summary,details,spamscore,submitted_by', + $tracker, + 'bug_id=? LIMIT 1', + $item_id); + } - my $tag = "$ent->{tracker}#$ent->{item_id}"; - $tag .= ", comment \#$ent->{comment_id}" if $ent->{comment_id}; + my $tag = "$tracker#$item_id"; + $tag .= "c$comment_id" if $comment_id; - if ($spamscore < 5) { - # The item was unflagged - SendTrackersDelayedNotification($ent->{tracker}, - $ent->{item_id}, - $ent->{comment_id}, - $spamscore); - logit('info', "$tag: was already at $spamscore"); - next; - } + if ($spamscore < 5) { + # The item was unflagged + SendTrackersDelayedNotification($tracker, + $item_id, + $comment_id, + $spamscore) + if $notify; + logit('info', "$tag: was already at $spamscore"); + return undef; + } - my $msg = GetTrackersContentAsMail($uid, - $sender_ip, - $ent->{tracker}, - $ent->{item_id}, - $ent->{comment_id}, - $now, - $subject, - $text); + my $msg = GetTrackersContentAsMail($uid, + $sender_ip, + $tracker, + $item_id, + $comment_id, + strftime("%r", localtime), + $subject, + $text); - # Check - my $result = $spamc->process($msg); - next unless defined $result; + # Check + my $result = $spamc->process($msg); + return undef unless defined $result; - my $score = $result->{score}; - debug("$ent->{queue_id}: spam=$result->{isspam}, score=$score"); + my $score = $result->{score}; - my $newscore; - if ($score <= 0) { - $newscore = $spamscore - 8; - } elsif ($score > 0.0 && $score <= 2.0) { - $newscore = $spamscore - 5; - } elsif ($score > 2.0 && $score <= 3.0) { - $newscore = $spamscore - 4; - } elsif ($score > 3.0 && $score <= 5.0) { - $newscore = $spamscore - 3; - } elsif ($score > 5.0 && $score <= 7.0) { - $newscore = $spamscore - 2; - } elsif ($score > 7.0 && $score <= 9.0) { - $newscore = $spamscore - 1; - } else { - $newscore = $spamscore; - } + my $newscore; + if ($score <= 0) { + $newscore = $spamscore - 8; + } elsif ($score > 0.0 && $score <= 2.0) { + $newscore = $spamscore - 5; + } elsif ($score > 2.0 && $score <= 3.0) { + $newscore = $spamscore - 4; + } elsif ($score > 3.0 && $score <= 5.0) { + $newscore = $spamscore - 3; + } elsif ($score > 5.0 && $score <= 7.0) { + $newscore = $spamscore - 2; + } elsif ($score > 7.0 && $score <= 9.0) { + $newscore = $spamscore - 1; + } else { + $newscore = $spamscore; + } - if ($newscore > 4) { - $stats{queue_spam}++; - } else { - $stats{queue_ham}++; - } - logit('info', "$tag: $score -> $newscore"); - next if dry_run(); + debug("$tag: spam=$result->{isspam}, SA score=$score, Savane score=$newscore"); + + if ($newscore > 4) { + $stats{queue_spam}++; + } else { + $stats{queue_ham}++; + } + logit('info', "$tag: $score -> $newscore"); + + return $uid if dry_run(); - if ($ent->{comment_id}) { - SetDBSettings($ent->{tracker}."_history", - 'bug_id=? AND field_name=? AND bug_history_id=? LIMIT 1', - 'spamscore=?', - $ent->{item_id}, 'details', $ent->{comment_id}, - $newscore); - } else { - SetDBSettings($ent->{tracker}, - 'bug_id=? LIMIT 1', - 'spamscore=?', $ent->{item_id}, $newscore); - } + if ($tracker eq 'resume') { + db_modify(q{ +UPDATE user SET resume_spamscore=? WHERE user_id=? +}, + $newscore, $item_id); + } elsif ($comment_id) { + db_modify(qq{ +UPDATE ${tracker}_history +SET spamscore=? +WHERE bug_id=? AND field_name=? AND bug_history_id=? + }, + $newscore, + $item_id, + 'details', + $comment_id); + } else { + db_modify(qq{ +UPDATE $tracker +SET spamscore=? +WHERE bug_id=? + }, + $newscore, $item_id); + } - # Update the scorespam table - InsertDB("trackers_spamscore", - "score,affected_user_id,reporter_user_id,artifact,item_id,comment_id", - "'$newscore', '$uid', '$uid', '$ent->{tracker}', '$ent->{item_id}', '$ent->{comment_id}'") - if $newscore > 0; + # Update the scorespam table + db_insert({ table => 'trackers_spamscore', + values => { + score => $newscore, + affected_user_id => $uid, + reporter_user_id => $reporter_uid, + artifact => $tracker, + item_id => $uid, + comment_id => 0 + }, + update => { + score => $newscore + } + }) if $newscore > 0; - # Notify - SendTrackersDelayedNotification($ent->{tracker}, - $ent->{item_id}, - $ent->{comment_id}, - $newscore); - } + # Notify + SendTrackersDelayedNotification($tracker, + $item_id, + $comment_id, + $newscore) + if $notify; + + return $uid; +} + +sub com_check { + debug("processing queue"); + my %affected_uids; + db_foreach(sub { + my $ent = shift; + my $uid = check_tracker_comment($ent->{tracker}, + $ent->{item_id}, + $ent->{comment_id}, + queue_id => $ent->{queue_id}); + $affected_uids{$uid}++ if defined($uid); + }, + q{ +SELECT queue_id,artifact as tracker,item_id,comment_id +FROM trackers_spamcheck_queue +ORDER BY priority DESC, date ASC}); + update_user_spamscores(U_IDS, [keys(%affected_uids)]); } sub learn_from_item { @@ -228,7 +325,7 @@ ON DUPLICATE KEY UPDATE isspam=? $isspam{$learntype}, $isspam{$learntype}); } -sub learn { +sub com_learn { debug("learning"); foreach my $tracker (TrackerNames()) { db_foreach(sub { @@ -269,31 +366,178 @@ WHERE hist.field_name='details' } } -my @opseq = ( - { name => 'queue', code => \&check_queue }, - { name => 'learn', code => \&learn } +sub com_userscore { + if (@_) { + update_user_spamscores(U_NAMES, [@_]); + } else { + update_user_spamscores(U_ALL); + } +} + +sub userlist_collect { + my $cond; + my @userlist; + my %userind; + + if ($#_ == -1) { + $cond = "user.status='A' AND user_group.group_id IS NULL"; + } else { + if ($#_ == 0) { + $cond = "user.user_name=?"; + } else { + $cond = "user.user_name IN (" . join(',', ('?') x (@_)) . ')'; + } + @userlist = map { user_name => $_ }, @_; + for (my $i = 0; $i <= $#_; $i++) { + $userind{$_[$i]} = $i; + } + } + + db_foreach(keys(%userind) == 0 ? + sub { + push @userlist, $_[0] + } : + sub { + my $ref = shift; + my $n = $userind{$ref->{user_name}}; + %{$userlist[$n]} = (%{$userlist[$n]}, %{$ref}); + }, + qq{ +SELECT DISTINCT user.user_id AS user_id, + user.user_name AS user_name, + user.realname AS realname, + user.user_name AS user_name, + user.realname AS realname, + user.people_resume AS resume, + user.people_view_skills AS view_skills, + user.add_date AS date +FROM user +LEFT JOIN user_group ON user_group.user_id=user.user_id +WHERE $cond}, + @_); + + if (@_) { + my @u = map { exists($_->{user_id}) ? () : $_->{user_name} } @userlist; + if (@u) { + abend(EX_USAGE, "the following users not found in the database: " + . join(',', @u)); + } + } + + return @userlist; +} + +sub com_mark { + my @ulist = userlist_collect(@_); + foreach my $user (@ulist) { + if ($user->{resume} ne '') { + db_insert({ table => 'trackers_spamcheck_queue', + values => { + artifact => 'resume', + item_id => $user->{user_id}, + comment_id => 0, + priority => 3 + }, + update => { + priority => 3, + } + }); + db_modify(q{UPDATE user SET resume_spamscore=? WHERE user_id=?}, + 8, $user->{user_id}); + } + + foreach my $t (TrackerNames()) { + db_foreach( + sub { + my $ref = shift; + db_insert({ table => 'trackers_spamcheck_queue', + values => { + artifact => $t, + item_id => $ref->{bug_id}, + comment_id => 0, + priority => 3 + }, + update => { + priority => 3, + } + }); + }, + qq{ +SELECT tracker.bug_id AS bug_id +FROM $t tracker +WHERE tracker.submitted_by=? +ORDER BY tracker.date + }, $user->{user_id}); + + db_foreach( + sub { + my $ref = shift; + db_insert('trackers_spamcheck_queue', + artifact => $t, + item_id => $ref->{bug_id}, + comment_id => $ref->{comment_id}, + priority => 3); + }, + qq{ +SELECT bug_history_id AS comment_id, + bug_id AS bug_id +FROM ${t}_history +WHERE field_name='details' AND mod_by=? +ORDER BY date}, + $user->{user_id}); + + db_modify(qq{UPDATE $t SET spamscore=8 WHERE submitted_by=?}, + $user->{user_id}); + db_modify(qq{UPDATE ${t}_history SET spamscore=8 WHERE field_name=? AND mod_by=?}, + 'details', $user->{user_id}); + } + } +} + +my %command = ( + queue => { code => \&com_check }, + learn => { code => \&com_learn, prereq => 'queue' }, + # Recompute user spamscore + userscore => { code => \&com_userscore, wantargs => 1 }, + mark => { code => \&com_mark, wantargs => 1 }, ); backend_setup(descr => 'Run spam check on messages in queue', cron => $sys_cron_spamchecker, options => { - "verbose|v" => \$verbose + "verbose|v" => \$verbose, }); -my %keys = map { $_->{name} => 1 } @opseq; -my %items; -if ($#ARGV >= 0) { - %items = map { - abend(EX_USAGE, "unrecognized operation: $_") - unless exists($keys{$_}); - $_ => 1 - } @ARGV; -} else { - %items = %keys; +abend(EX_USAGE, "no command") unless @ARGV; + +# Process arguments, reordering them to satisfy prereqs +for (my $i = 0; $i <= $#ARGV; $i++) { + my $arg = $ARGV[$i]; + abend(EX_USAGE, "no such command: $arg") unless exists $command{$arg}; + while (exists($command{$arg}{alias})) { + $arg = $command{$arg}{alias}; + die unless exists $command{$arg}; + } + $ARGV[$i] = $arg; + abend(EX_USAGE, "$arg: command already given") if $command{$arg}{used}; + if ($command{$arg}{prereq}) { + my ($j) = grep { $ARGV[$_] eq $command{$arg}{prereq} } 0..$i; + unless (defined($j) && $j < $#ARGV) { + ($j) = grep { $ARGV[$_] eq $command{$arg}{prereq} } $i..$#ARGV; + if (defined($j)) { + splice(@ARGV, $j, 1); + splice(@ARGV, $i, 0, $command{$arg}{prereq}); + $i++; + } + } + } + $command{$arg}{used}++; + last if $command{$arg}{wantargs}; } my $chk = GetConf('backend.sv_spamchecker.check'); +# FIXME: Not actually used unless (defined($chk)) { logit('notice', "backend.sv_spamchecker.check; exiting"); backend_done(); @@ -323,10 +567,12 @@ $spamc_options{username} = $sys_spamc_username; $spamc = new Mail::SpamAssassin::Client(\%spamc_options); abend(EX_UNAVAILABLE, "can't connect to spamd") unless $spamc->ping(); -foreach my $op (@opseq) { - if (exists($items{$op->{name}})) { - &{$op->{code}}; - delete $items{$op->{name}}; +while (my $arg = shift @ARGV) { + if ($command{$arg}{wantargs}) { + &{$command{$arg}{code}}(@ARGV); + last; + } else { + &{$command{$arg}{code}}; } } @@ -361,7 +607,9 @@ B<sv_spamchecker> [B<--syslog>] [B<queue>] [B<learn>] - +[B<userscore>] +[B<mark>] [I<USER>...] + B<sv_spamchecker> [B<-h>] [B<--help>] =head1 DESCRIPTION @@ -392,6 +640,12 @@ By default, both stages are run in the order they are described above. To instruct B<sv_spamchecker> to run only a particular stage, supply an additional argument: B<queue> or B<learn>. +Two special modes are available, which take optional list of user names +in the command line. When given the B<userscore> argument, B<sv_spamchecker> +recomputes total spam scores for the listed users. When given the B<mark> +argument, it flags the listed users for subsequent spam checking. If no +user names are given, both commands apply to all Savane users (use with care). + =head1 OPTIONS =over 8 diff --git a/db/mysql/table_bugs.structure b/db/mysql/table_bugs.structure index 70aa003..c88771b 100644 --- a/db/mysql/table_bugs.structure +++ b/db/mysql/table_bugs.structure @@ -92,6 +92,7 @@ CREATE TABLE `bugs` ( `custom_df4` int(11) NOT NULL DEFAULT '0', `custom_df5` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`bug_id`), - KEY `idx_bug_group_id` (`group_id`) + KEY `idx_bug_group_id` (`group_id`), + KEY `submitted_by` (`submitted_by`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; diff --git a/db/mysql/table_cookbook.structure b/db/mysql/table_cookbook.structure index 1534f6f..8619bd7 100644 --- a/db/mysql/table_cookbook.structure +++ b/db/mysql/table_cookbook.structure @@ -92,6 +92,7 @@ CREATE TABLE `cookbook` ( `custom_df4` int(11) NOT NULL DEFAULT '0', `custom_df5` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`bug_id`), - KEY `idx_bug_group_id` (`group_id`) + KEY `idx_bug_group_id` (`group_id`), + KEY `submitted_by` (`submitted_by`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; diff --git a/db/mysql/table_patch.structure b/db/mysql/table_patch.structure index 0752f53..829f05b 100644 --- a/db/mysql/table_patch.structure +++ b/db/mysql/table_patch.structure @@ -92,6 +92,7 @@ CREATE TABLE `patch` ( `custom_df4` int(11) NOT NULL DEFAULT '0', `custom_df5` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`bug_id`), - KEY `idx_bug_group_id` (`group_id`) + KEY `idx_bug_group_id` (`group_id`), + KEY `submitted_by` (`submitted_by`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; diff --git a/db/mysql/table_support.structure b/db/mysql/table_support.structure index 65bf658..c6e7b3c 100644 --- a/db/mysql/table_support.structure +++ b/db/mysql/table_support.structure @@ -92,6 +92,7 @@ CREATE TABLE `support` ( `custom_df4` int(11) NOT NULL DEFAULT '0', `custom_df5` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`bug_id`), - KEY `idx_bug_group_id` (`group_id`) + KEY `idx_bug_group_id` (`group_id`), + KEY `submitted_by` (`submitted_by`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; diff --git a/db/mysql/table_task.structure b/db/mysql/table_task.structure index de4acb0..38c66f6 100644 --- a/db/mysql/table_task.structure +++ b/db/mysql/table_task.structure @@ -92,6 +92,7 @@ CREATE TABLE `task` ( `custom_df4` int(11) NOT NULL DEFAULT '0', `custom_df5` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`bug_id`), - KEY `idx_bug_group_id` (`group_id`) + KEY `idx_bug_group_id` (`group_id`), + KEY `submitted_by` (`submitted_by`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; diff --git a/db/mysql/table_trackers_spamcheck_queue.structure b/db/mysql/table_trackers_spamcheck_queue.structure index 05e3fcb..1303802 100644 --- a/db/mysql/table_trackers_spamcheck_queue.structure +++ b/db/mysql/table_trackers_spamcheck_queue.structure @@ -23,7 +23,8 @@ CREATE TABLE `trackers_spamcheck_queue` ( `item_id` int(11) NOT NULL DEFAULT '0', `comment_id` int(11) NOT NULL DEFAULT '0', `priority` int(1) NOT NULL DEFAULT '1', - `date` int(11) NOT NULL DEFAULT '0', - PRIMARY KEY (`queue_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; + `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`queue_id`), + UNIQUE KEY `artifact` (`artifact`,`item_id`,`comment_id`) +) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; diff --git a/db/mysql/table_trackers_spamscore.structure b/db/mysql/table_trackers_spamscore.structure index 297f7a0..0dc1ddd 100644 --- a/db/mysql/table_trackers_spamscore.structure +++ b/db/mysql/table_trackers_spamscore.structure @@ -25,6 +25,7 @@ CREATE TABLE `trackers_spamscore` ( `artifact` varchar(16) NOT NULL DEFAULT '', `item_id` int(11) NOT NULL DEFAULT '0', `comment_id` int(11) NOT NULL DEFAULT '0', - PRIMARY KEY (`id`) + PRIMARY KEY (`id`), + UNIQUE KEY `artifact` (`artifact`,`item_id`,`comment_id`,`affected_user_id`,`reporter_user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; diff --git a/frontend/php/include/spam.php b/frontend/php/include/spam.php index fdbf1cc..6028be7 100644 --- a/frontend/php/include/spam.php +++ b/frontend/php/include/spam.php @@ -227,10 +227,14 @@ function spam_get_user_score ($user_id=0, $set_by_user_id=0) # We cannot do a count because it does not allow us to use GROUP BY $userscore = 0; - $result = db_execute("SELECT score FROM trackers_spamscore WHERE affected_user_id=? $set_by_user_id_sql GROUP BY reporter_user_id", + $result = db_execute("SELECT score, artifact FROM trackers_spamscore WHERE affected_user_id=? $set_by_user_id_sql GROUP BY reporter_user_id", array_merge(array($user_id), $set_by_user_id_params)); while ($entry = db_fetch_array($result)) - $userscore++; + { + $userscore++; + if ($entry['artifact'] == 'resume') + $userscore += 8; + } return $userscore; } @@ -326,7 +330,12 @@ function spam_add_to_spamcheck_queue ($item_id, $comment_id, $tracker, $group_id # there was no score yet. # (no discussion lock, update will generate notif if sent by users that # can skip this spam queue check - members, etc) - if ($comment_id) + if ($tracker == 'resume') + { + $result = db_execute("UPDATE user SET resume_spamscore=? WHERE user_id=?", + array($newscore, $item_id)); + } + else if ($comment_id) { $result = db_execute("UPDATE ".$tracker."_history SET spamscore=? WHERE bug_history_id=? AND field_name='details' AND bug_id=?", diff --git a/frontend/php/include/trackers/data.php b/frontend/php/include/trackers/data.php index 563cacd..2806dca 100644 --- a/frontend/php/include/trackers/data.php +++ b/frontend/php/include/trackers/data.php @@ -2279,7 +2279,7 @@ function trackers_data_create_item($group_id,$vfl,&$extra_addresses) if (!user_isloggedin()) { $user = 100; } else - { $user=user_getid(); } + { $user = user_getid(); } # make sure required fields are not empty if (trackers_check_empty_fields($vfl) == false) diff --git a/frontend/php/my/admin/resume.php b/frontend/php/my/admin/resume.php index 5df4577..1d2e9aa 100644 --- a/frontend/php/my/admin/resume.php +++ b/frontend/php/my/admin/resume.php @@ -31,7 +31,7 @@ if ( ! user_isloggedin()) } extract(sane_import('post', - array('update_profile', 'people_resume', 'people_view_skills', + array('update_profile', 'people_resume', 'people_view_skills', 'group_count', 'add_to_skill_inventory', 'update_skill_inventory', 'delete_from_skill_inventory', 'skill_id', 'skill_level_id', 'skill_year_id', 'skill_inventory_id'))); @@ -40,7 +40,8 @@ if ($update_profile) { if (!$people_resume) { - fb(_("Missing info: fill in all required fields"), 1); + if (!$people_view_skills) + fb(_("Missing info: fill in all required fields"), 1); } else { @@ -53,6 +54,10 @@ if ($update_profile) } else { + if ($group_count == 0) + { + spam_add_to_spamcheck_queue(user_getid(), 0, 'resume', 0, 3); + } fb(_("Updated successfully")); } } @@ -120,8 +125,14 @@ site_user_header(array('title'=>_("Edit Your Resume & Skills"),'context'=>'accou print '<p>'._("Details about your experience and skills will be available to logged in users in the hope they will be of interest.").'</p>'; - -$result = db_execute("SELECT * FROM user WHERE user_id=?", array(user_getid())); +$result = db_execute("SELECT user.user_name as user_name, ". + "user.people_view_skills as people_view_skills, ". + "user.people_resume as people_resume, ". + "count(user_group.group_id) AS group_count ". + "FROM user ". + "LEFT JOIN user_group ON user.user_id=user_group.user_id ". + "WHERE user.user_id=?", + array(user_getid())); if (!$result || db_numrows($result) < 1) { exit_error(_("No such user")); @@ -135,10 +146,12 @@ $viewableoptions = array("0" => _("No"), "1" => _("Yes")); +$group_count = db_result($result,0,'group_count'); print '<form action="'.$_SERVER['PHP_SELF'].'" method="post">' .'<h3>'._("Publicly Viewable").'</h3>' .'<span class="preinput">'._("Do you want your resume to be activated:").'</span> ' +."<input type=\"hidden\" name=\"group_count\" value=\"$group_count\" />" .html_build_select_box_from_array(array("0" => _("No"),"1" => _("Yes")), 'people_view_skills', db_result($result,0,'people_view_skills')); diff --git a/lib/Savane/DB.pm b/lib/Savane/DB.pm index 03f6853..0c8adf3 100644 --- a/lib/Savane/DB.pm +++ b/lib/Savane/DB.pm @@ -299,17 +299,44 @@ sub InsertDB { # db_insert(TABLE, COLUMN => VALUE, ...) sub db_insert { - my $table = shift; - confess "bad number of arguments" if ($@ % 2); + my $arg = shift; + confess "no arguments" unless defined($arg); + + my $table; my @colnames; my @values; - for (my $i = 0; $i <= $#_; $i += 2) { - push @colnames, $_[$i]; - push @values, $_[$i+1]; + my $update; + + if (ref($arg) eq 'HASH') { + $table = $arg->{table} or confess "table must be given"; + unless (exists($arg->{values}) && ref($arg->{values}) eq 'HASH') { + confess "bad values"; + } + while (my ($column, $value) = each %{$arg->{values}}) { + push @colnames, $column; + push @values, $value; + } + if (exists($arg->{update})) { + my @c; + while (my ($column, $value) = each %{$arg->{update}}) { + push @c, $column; + push @values, $value; + } + $update = ' ON DUPLICATE KEY UPDATE ' + . join(',', map { "$_=?" } @c); + } + } else { + $table = $arg; + confess "bad number of arguments" if ($@ % 2); + for (my $i = 0; $i <= $#_; $i += 2) { + push @colnames, $_[$i]; + push @values, $_[$i+1]; + } } + my $fields = join(',',@colnames); db_modify("INSERT INTO $table ($fields) VALUES (" . - join(',', ('?') x @colnames) . ")", + join(',', ('?') x @colnames) . ")" . $update, @values); } @@ -334,7 +361,6 @@ sub db_modify { return $res; } -# db_delete(TABLE, COND, ...) # db_delete(table => T, cond => C, args => A, dry_run => B) sub db_delete { local %_ = @_; |