aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorSergey Poznyakoff <gray@gnu.org.ua>2017-08-30 16:49:51 +0200
committerSergey Poznyakoff <gray@gnu.org.ua>2017-08-30 16:49:51 +0200
commit710988ccd5e60087f390d34a33956a2aa6b9596e (patch)
tree69ce78b5a72584110ec4dfc0cee073562de0496b
parentf4d18eefe7ba374799371ec96e93991e13a552fc (diff)
downloadsavane-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-xbackend/misc/sv_spamchecker502
-rw-r--r--db/mysql/table_bugs.structure3
-rw-r--r--db/mysql/table_cookbook.structure3
-rw-r--r--db/mysql/table_patch.structure3
-rw-r--r--db/mysql/table_support.structure3
-rw-r--r--db/mysql/table_task.structure3
-rw-r--r--db/mysql/table_trackers_spamcheck_queue.structure7
-rw-r--r--db/mysql/table_trackers_spamscore.structure3
-rw-r--r--frontend/php/include/spam.php15
-rw-r--r--frontend/php/include/trackers/data.php2
-rw-r--r--frontend/php/my/admin/resume.php21
-rw-r--r--lib/Savane/DB.pm40
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>&nbsp;&nbsp;'
+."<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 %_ = @_;

Return to:

Send suggestions and report system problems to the System administrator.