diff options
| -rw-r--r-- | Bugzilla/Attachment.pm | 15 | ||||
| -rwxr-xr-x | Bugzilla/Bug.pm | 14 | ||||
| -rw-r--r-- | Bugzilla/DB.pm | 31 | ||||
| -rw-r--r-- | Bugzilla/DB/Oracle.pm | 16 | ||||
| -rw-r--r-- | Bugzilla/Flag.pm | 11 | ||||
| -rw-r--r-- | Bugzilla/Milestone.pm | 2 | ||||
| -rw-r--r-- | Bugzilla/Object.pm | 5 | ||||
| -rw-r--r-- | Bugzilla/Search.pm | 8 | ||||
| -rwxr-xr-x | buglist.cgi | 24 | ||||
| -rwxr-xr-x | process_bug.cgi | 8 |
10 files changed, 93 insertions, 41 deletions
diff --git a/Bugzilla/Attachment.pm b/Bugzilla/Attachment.pm index f853cff04..dafdf70ad 100644 --- a/Bugzilla/Attachment.pm +++ b/Bugzilla/Attachment.pm @@ -99,13 +99,14 @@ sub _retrieve { 'attachments.isprivate AS isprivate' ); my $columns = join(", ", @columns); - - my $records = Bugzilla->dbh->selectall_arrayref("SELECT $columns - FROM attachments - WHERE attach_id IN (" . - join(",", @$ids) . ") - ORDER BY attach_id", - { Slice => {} }); + my $dbh = Bugzilla->dbh; + my $records = $dbh->selectall_arrayref( + "SELECT $columns + FROM attachments + WHERE " + . Bugzilla->dbh->sql_in('attach_id', $ids) + . " ORDER BY attach_id", + { Slice => {} }); return $records; } diff --git a/Bugzilla/Bug.pm b/Bugzilla/Bug.pm index 20e87c25c..227cb1f6e 100755 --- a/Bugzilla/Bug.pm +++ b/Bugzilla/Bug.pm @@ -635,8 +635,8 @@ sub update_cc { my ($removed, $added) = diff_arrays(\@old_cc, \@new_cc); if (scalar @$removed) { - $dbh->do('DELETE FROM cc WHERE bug_id = ? AND who IN (' . - join(',', @$removed) . ')', undef, $self->id); + $dbh->do('DELETE FROM cc WHERE bug_id = ? AND ' + . $dbh->sql_in('who', $removed), undef, $self->id); } foreach my $user_id (@$added) { $dbh->do('INSERT INTO cc (bug_id, who) VALUES (?,?)', @@ -722,8 +722,8 @@ sub update_keywords { my ($removed, $added) = diff_arrays(\@old_ids, \@new_ids); if (scalar @$removed) { - $dbh->do('DELETE FROM keywords WHERE bug_id = ? AND keywordid IN (' - . join(',', @$removed) . ')', undef, $self->id); + $dbh->do('DELETE FROM keywords WHERE bug_id = ? AND ' + . $dbh->sql_in('keywordid', $removed), undef, $self->id); } foreach my $keyword_id (@$added) { $dbh->do('INSERT INTO keywords (bug_id, keywordid) VALUES (?,?)', @@ -798,8 +798,8 @@ sub remove_from_db { WHERE bug_id = ?", undef, $bug_id); if (scalar(@$attach_ids)) { - $dbh->do("DELETE FROM attach_data WHERE id IN (" . - join(",", @$attach_ids) . ")"); + $dbh->do("DELETE FROM attach_data WHERE " + . $dbh->sql_in('id', $attach_ids)); } # Several of the previous tables also depend on attach_id. @@ -3018,7 +3018,7 @@ sub CountOpenDependencies { my $sth = $dbh->prepare( "SELECT blocked, COUNT(bug_status) " . "FROM bugs, dependencies " . - "WHERE blocked IN (" . (join "," , @bug_list) . ") " . + "WHERE " . $dbh->sql_in('blocked', \@bug_list) . "AND bug_id = dependson " . "AND bug_status IN (" . join(', ', map {$dbh->quote($_)} BUG_STATE_OPEN) . ") " . $dbh->sql_group_by('blocked')); diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm index 40cd3e3a1..2a71bcd75 100644 --- a/Bugzilla/DB.pm +++ b/Bugzilla/DB.pm @@ -391,6 +391,11 @@ sub sql_string_concat { return '(' . join(' || ', @params) . ')'; } +sub sql_in { + my ($self, $column_name, $in_list_ref) = @_; + return " $column_name IN (" . join(',', @$in_list_ref) . ") "; +} + sub sql_fulltext_search { my ($self, $column, $text) = @_; @@ -1918,6 +1923,32 @@ will not be usually used unless it was created as LOWER(column). =back +=item C<sql_in> + +=over + +=item B<Description> + +Returns SQL syntax for the C<IN ()> operator. + +Only necessary where an C<IN> clause can have more than 1000 items. + +=item B<Params> + +=over + +=item C<$column_name> - Column name (e.g. C<bug_id>) + +=item C<$in_list_ref> - an arrayref containing values for C<IN ()> + +=back + +=item B<Returns> + +Formatted SQL for the C<IN> operator. + +=back + =item C<bz_lock_tables> =over diff --git a/Bugzilla/DB/Oracle.pm b/Bugzilla/DB/Oracle.pm index f89a72ab0..169550087 100644 --- a/Bugzilla/DB/Oracle.pm +++ b/Bugzilla/DB/Oracle.pm @@ -165,6 +165,22 @@ sub sql_position { my ($self, $fragment, $text) = @_; return "INSTR($text, $fragment)"; } + +sub sql_in { + my ($self, $column_name, $in_list_ref) = @_; + my @in_list = @$in_list_ref; + return $self->SUPER::sql_in($column_name, $in_list_ref) if $#in_list < 1000; + my @in_str; + while (@in_list) { + my $length = $#in_list + 1; + my $splice = $length > 1000 ? 1000 : $length; + my @sub_in_list = splice(@in_list, 0, $splice); + push(@in_str, + $self->SUPER::sql_in($column_name, \@sub_in_list)); + } + return "( " . join(" OR ", @in_str) . " )"; +} + sub _fix_empty { my ($string) = @_; $string = '' if $string eq EMPTY_STRING; diff --git a/Bugzilla/Flag.pm b/Bugzilla/Flag.pm index 3a4d03df6..adc92cfd4 100644 --- a/Bugzilla/Flag.pm +++ b/Bugzilla/Flag.pm @@ -295,11 +295,12 @@ sub validate { my $not = ($attach_id) ? "" : "NOT"; my $invalid_data = - $dbh->selectrow_array("SELECT 1 FROM flags - WHERE id IN (" . join(',', @flag_ids) . ") - AND ($field != ? OR attach_id IS $not NULL) " . - $dbh->sql_limit(1), - undef, $field_id); + $dbh->selectrow_array( + "SELECT 1 FROM flags + WHERE " + . $dbh->sql_in('id', \@flag_ids) + . " AND ($field != ? OR attach_id IS $not NULL) " + . $dbh->sql_limit(1), undef, $field_id); if ($invalid_data) { ThrowCodeError('invalid_flag_association', diff --git a/Bugzilla/Milestone.pm b/Bugzilla/Milestone.pm index dda04c4f8..fc44cf1af 100644 --- a/Bugzilla/Milestone.pm +++ b/Bugzilla/Milestone.pm @@ -144,7 +144,7 @@ sub remove_from_db { my $timestamp = $dbh->selectrow_array('SELECT NOW()'); $dbh->do('UPDATE bugs SET target_milestone = ?, delta_ts = ? - WHERE bug_id IN (' . join(', ', @$bug_ids) . ')', + WHERE ' . $dbh->sql_in('bug_id', $bug_ids), undef, ($self->product->default_milestone, $timestamp)); require Bugzilla::Bug; diff --git a/Bugzilla/Object.pm b/Bugzilla/Object.pm index 2cf671bc2..23a88855f 100644 --- a/Bugzilla/Object.pm +++ b/Bugzilla/Object.pm @@ -145,8 +145,9 @@ sub new_from_list { push(@detainted_ids, $id); } $objects = $dbh->selectall_arrayref( - "SELECT $columns FROM $table WHERE $id_field IN (" - . join(',', @detainted_ids) . ") ORDER BY $order", {Slice=>{}}); + "SELECT $columns FROM $table WHERE " + . $dbh->sql_in($id_field, \@detainted_ids) + . "ORDER BY $order", {Slice=>{}}); } else { return []; } diff --git a/Bugzilla/Search.pm b/Bugzilla/Search.pm index b30f74911..8fa27143b 100644 --- a/Bugzilla/Search.pm +++ b/Bugzilla/Search.pm @@ -352,8 +352,8 @@ sub init { $extra .= " AND actcheck.added = $sql_chvalue"; } push(@supptables, "LEFT JOIN bugs_activity AS actcheck " . - "ON $extra AND actcheck.fieldid IN (" . - join(",", @actlist) . ")"); + "ON $extra AND " + . $dbh->sql_in('actcheck.fieldid', \@actlist)); } # Now that we're done using @list to determine if there are any @@ -1082,7 +1082,7 @@ sub init { push(@list, $q); } if (@list) { - $term = "$ff IN (" . join (',', @list) . ")"; + $term = $dbh->sql_in($ff, \@list); } }, ",anywordssubstr" => sub { @@ -1519,7 +1519,7 @@ sub build_subselect { my $dbh = Bugzilla->dbh; my $list = $dbh->selectcol_arrayref($q); return "1=2" unless @$list; # Could use boolean type on dbs which support it - return "$outer IN (" . join(',', @$list) . ")"; + return $dbh->sql_in($outer, $list); } sub GetByWordList { diff --git a/buglist.cgi b/buglist.cgi index 89b164dd8..493f4b3a9 100755 --- a/buglist.cgi +++ b/buglist.cgi @@ -1084,7 +1084,7 @@ if (@bugidlist) { "LEFT JOIN group_control_map " . "ON group_control_map.product_id = bugs.product_id " . "AND group_control_map.group_id = bug_group_map.group_id " . - "WHERE bugs.bug_id IN (" . join(',',@bugidlist) . ") " . + "WHERE " . $dbh->sql_in('bugs.bug_id', \@bugidlist) . $dbh->sql_group_by('bugs.bug_id')); $sth->execute(); while (my ($bug_id, $min_membercontrol) = $sth->fetchrow_array()) { @@ -1161,19 +1161,23 @@ if ($dotweak) { my @bug_statuses = map {$dbh->quote($_)} keys %$bugstatuses; my $bug_status_ids = $dbh->selectcol_arrayref('SELECT id FROM bug_status - WHERE value IN (' . join(', ', @bug_statuses) .')'); + WHERE ' . $dbh->sql_in('value', \@bug_statuses)); # This query collects new statuses which are common to all current bug statuses. # It also accepts transitions where the bug status doesn't change. $bug_status_ids = - $dbh->selectcol_arrayref('SELECT DISTINCT new_status - FROM status_workflow sw1 - WHERE NOT EXISTS (SELECT * FROM status_workflow sw2 - WHERE sw2.old_status != sw1.new_status - AND sw2.old_status IN (' . join(', ', @$bug_status_ids) . ') - AND NOT EXISTS (SELECT * FROM status_workflow sw3 - WHERE sw3.new_status = sw1.new_status - AND sw3.old_status = sw2.old_status))'); + $dbh->selectcol_arrayref( + 'SELECT DISTINCT new_status + FROM status_workflow sw1 + WHERE NOT EXISTS + (SELECT * FROM status_workflow sw2 + WHERE sw2.old_status != sw1.new_status + AND ' + . $dbh->sql_in('sw2.old_status', $bug_status_ids) + . ' AND NOT EXISTS + (SELECT * FROM status_workflow sw3 + WHERE sw3.new_status = sw1.new_status + AND sw3.old_status = sw2.old_status))'); $vars->{'current_bug_statuses'} = [keys %$bugstatuses]; $vars->{'new_bug_statuses'} = Bugzilla::Status->new_from_list($bug_status_ids); diff --git a/process_bug.cgi b/process_bug.cgi index 71be09168..35d41ee00 100755 --- a/process_bug.cgi +++ b/process_bug.cgi @@ -327,11 +327,9 @@ if ($cgi->param('product') ne $cgi->param('dontchange')) { $product = Bugzilla::Product::check_product(scalar $cgi->param('product')); @newprod_ids = ($product->id); } else { - @newprod_ids = @{$dbh->selectcol_arrayref("SELECT DISTINCT product_id - FROM bugs - WHERE bug_id IN (" . - join(',', @idlist) . - ")")}; + @newprod_ids = @{$dbh->selectcol_arrayref( + "SELECT DISTINCT product_id FROM bugs WHERE " + . $dbh->sql_in('bug_id', \@idlist))}; if (scalar(@newprod_ids) == 1) { $product = new Bugzilla::Product($newprod_ids[0]); } |