1 # This module is part of debbugs, and is released
2 # under the terms of the GPL version 2, or any later version. See the
3 # file README and COPYING for more information.
4 # Copyright 2013 by Don Armstrong <don@donarmstrong.com>.
6 package Debbugs::DB::Load;
10 Debbugs::DB::Load -- Utility routines for loading the database
26 use vars qw($VERSION $DEBUG %EXPORT_TAGS @EXPORT_OK @EXPORT);
27 use base qw(Exporter);
30 ($VERSION) = q$Revision$ =~ /^Revision:\s+([^\s+])/;
31 $DEBUG = 0 unless defined $DEBUG;
34 %EXPORT_TAGS = (load_bug => [qw(load_bug handle_load_bug_queue load_bug_log)],
35 load_debinfo => [qw(load_debinfo)],
36 load_package => [qw(load_package)],
37 load_suite => [qw(load_suite)],
40 Exporter::export_ok_tags(keys %EXPORT_TAGS);
41 $EXPORT_TAGS{all} = [@EXPORT_OK];
44 use Params::Validate qw(validate_with :types);
46 use Debbugs::Status qw(read_bug split_status_fields);
49 use Debbugs::Common qw(make_list getparsedaddrs);
50 use Debbugs::Config qw(:config);
55 Routines to load bug; exported with :load_bug
61 load_bug(db => $schema,
62 data => split_status_fields($data),
66 Loads a bug's metadata into the database. (Does not load any messages)
70 =item db -- Debbugs::DB object
72 =item data -- Bug data (from read_bug) which has been split with split_status_fields
74 =item tags -- tag cache (hashref); optional
76 =item queue -- queue of operations to perform after bug is loaded; optional.
83 my %param = validate_with(params => \@_,
84 spec => {db => {type => OBJECT,
86 data => {type => HASHREF,
89 bug => {type => SCALAR,
92 tags => {type => HASHREF,
93 default => sub {return {}},
95 severities => {type => HASHREF,
96 default => sub {return {}},
99 queue => {type => HASHREF,
103 if (not exists $param{data} and not exists $param{bug}) {
104 croak "One of data or bug must be provided to load_bug";
106 if (not exists $param{data}) {
107 $param{data} = read_bug(bug => $param{bug});
109 my $data = $param{data};
110 my $tags = $param{tags};
111 my $queue = $param{queue};
112 my $severities = $param{severities};
114 if (not defined $queue) {
119 my $s_data = split_status_fields($data);
120 for my $tag (make_list($s_data->{keywords})) {
121 next unless defined $tag and length $tag;
122 # this allows for invalid tags. But we'll use this to try to
123 # find those bugs and clean them up
124 if (not exists $tags->{$tag}) {
125 $tags->{$tag} = $s->resultset('Tag')->
126 find_or_create({tag => $tag});
128 $tags{$tag} = $tags->{$tag};
130 my $severity = length($data->{severity}) ? $data->{severity} : $config{default_severity};
131 if (exists $severities->{$severity}) {
132 $severity = $severities->{$severity};
134 $severity = $s->resultset('Severity')->
135 find_or_create({severity => $severity});
138 {id => $data->{bug_num},
139 creation => DateTime->from_epoch(epoch => $data->{date}),
140 log_modified => DateTime->from_epoch(epoch => $data->{log_modified}),
141 last_modified => DateTime->from_epoch(epoch => $data->{last_modified}),
142 archived => $data->{archived},
143 (defined $data->{unarchived} and length($data->{unarchived}))?(unarchived => DateTime->from_epoch(epoch => $data->{unarchived})):(),
144 forwarded => $data->{forwarded} // '',
145 summary => $data->{summary} // '',
146 outlook => $data->{outlook} // '',
147 subject => $data->{subject} // '',
148 done_full => $data->{done} // '',
149 severity => $severity,
150 owner_full => $data->{owner} // '',
151 submitter_full => $data->{originator} // '',
156 submitter => 'originator',
158 for my $addr_type (keys %addr_map) {
159 my @addrs = getparsedaddrs($data->{$addr_map{$addr_type}} // '');
161 $bug->{$addr_type} = $s->resultset('Correspondent')->find_or_create({addr => lc($addrs[0]->address())});
162 # insert the full name as well
163 my $full_name = $addrs[0]->phrase();
164 $full_name =~ s/^\"|\"$//g;
165 $full_name =~ s/^\s+|\s+$//g;
166 if (length $full_name) {
168 update_or_create_related('correspondent_full_names',
169 {full_name=>$full_name,
170 last_seen => 'NOW()'});
173 my $b = $s->resultset('Bug')->update_or_create($bug) or
174 die "Unable to update or create bug $bug->{id}";
176 for my $ff (qw(found fixed)) {
177 my @elements = $s->resultset('BugVer')->search({bug => $data->{bug_num},
178 found => $ff eq 'found'?1:0,
180 my %elements_to_delete = map {($elements[$_]->ver_string(),$elements[$_])} 0..$#elements;
182 my @elements_to_keep;
183 for my $version (@{$data->{"${ff}_versions"}}) {
184 if (exists $elements_to_delete{$version}) {
185 push @elements_to_keep,$version;
187 $elements_to_add{$version} = 1;
190 for my $version (@elements_to_keep) {
191 delete $elements_to_delete{$version};
193 for my $element (keys %elements_to_delete) {
194 $elements_to_delete{$element}->delete();
196 for my $element (keys %elements_to_add) {
197 # find source package and source version id
198 my $ne = $s->resultset('BugVer')->new_result({bug => $data->{bug_num},
199 ver_string => $element,
200 found => $ff eq 'found'?1:0,
203 if (my ($src_pkg,$src_ver) = $element =~ m{^([^\/]+)/(.+)$}) {
204 my $src_pkg_e = $s->resultset('SrcPkg')->single({pkg => $src_pkg});
205 if (defined $src_pkg_e) {
206 $ne->src_pkg($src_pkg_e->id());
207 my $src_ver_e = $s->resultset('SrcVer')->single({src_pkg => $src_pkg_e->id(),
210 $ne->src_ver($src_ver_e->id()) if defined $src_ver_e;
218 my $t = $s->resultset('BugTag')->search({bug => $data->{bug_num}});
219 $t->delete() if defined $t;
220 $s->populate(BugTag => [[qw(bug tag)], map {[$data->{bug_num}, $_->id()]} values %tags]);
222 # because these bugs reference other bugs which might not exist
223 # yet, we can't handle them until we've loaded all bugs. queue
225 for my $merge_block (qw(merged block)) {
226 my $data_key = $merge_block;
227 $data_key .= 'with' if $merge_block eq 'merged';
228 if (@{$data->{$data_key}||[]}) {
229 my $count = $s->resultset('Bug')->search({id => [@{$data->{$data_key}}]})->count();
230 if ($count == @{$data->{$data_key}}) {
231 handle_load_bug_queue(db=>$s,
232 queue => {$merge_block,
233 {$data->{bug_num},[@{$data->{$data_key}}]}
236 $queue->{$merge_block}{$data->{bug_num}} = [@{$data->{$data_key}}];
241 if (not $can_queue and keys %{$queue}) {
242 handle_load_bug_queue(db => $s,queue => $queue);
245 # still need to handle merges, versions, etc.
248 =item handle_load_bug_queue
250 handle_load_bug_queue(db => $schema,queue => $queue);
252 Handles a queue of operations created by load bug. [These operations
253 are used to handle cases where a bug referenced by a loaded bug may
254 not exist yet. In cases where the bugs should exist, the queue is
255 cleared automatically by load_bug if queue is undefined.
259 sub handle_load_bug_queue{
260 my %param = validate_with(params => \@_,
261 spec => {db => {type => OBJECT,
263 queue => {type => HASHREF,
267 my $queue = $param{queue};
269 (merged => {set => 'BugMerged',
270 columns => [qw(bug merged)],
273 blocks => {set => 'BugBlock',
274 columns => [qw(bug blocks)],
278 for my $queue_type (keys %queue_types) {
279 for my $bug (%{$queue->{$queue_type}}) {
280 my $qt = $queue_types{$queue_type};
282 $s->resultset($qt->{set})->search({$qt->{bug},$bug})->delete();
283 $s->populate($qt->{set},[[@{$qt->{columns}}],
284 map {[$bug,$_]} @{$queue->{$queue_type}{$bug}}]) if
285 @{$queue->{$queue_type}{$bug}//[]};
292 =item load_bug_log -- load bug logs
294 load_bug_log(db => $s,
302 =item bug -- bug whose log should be loaded
309 my %param = validate_with(params => \@_,
310 spec => {db => {type => OBJECT,
312 bug => {type => SCALAR,
314 queue => {type => HASHREF,
320 my $log = Debbugs::Log->new(bug_num => $param{bug}) or
321 die "Unable to open log for $param{bug} for reading: $!";
322 while (my $record = $log->read_record()) {
323 next unless $record->{type} eq 'incoming-recv';
324 my ($msg_id) = $record->{text} =~ /^Message-Id:\s+<(.+)>/im;
325 next if defined $msg_id and exists $seen_msg_ids{$msg_id};
326 $seen_msg_ids{$msg_id} = 1 if defined $msg_id;
327 next if defined $msg_id and $msg_id =~ /handler\..+\.ack(?:info)?\@/;
328 my $message = parse($record->{text});
329 # search for a message with this message id in the database
331 # check to see if the subject, to, and from match. if so, it's
332 # probably the same message.
334 # if not, create a new message
336 # add correspondents if necessary
338 # link message to bugs if necessary
348 Commands to handle src and package version loading from debinfo files
354 load_debinfo($schema,$binname, $binver, $binarch, $srcname, $srcver);
361 my ($schema,$binname, $binver, $binarch, $srcname, $srcver) = @_;
362 my $sp = $schema->resultset('SrcPkg')->find_or_create({pkg => $srcname});
363 my $sv = $schema->resultset('SrcVer')->find_or_create({src_pkg=>$sp->id(),
365 my $arch = $schema->resultset('Arch')->find_or_create({arch => $binarch});
366 my $bp = $schema->resultset('BinPkg')->find_or_create({pkg => $binname});
367 $schema->resultset('BinVer')->find_or_create({bin_pkg_id => $bp->id(),
368 src_ver_id => $sv->id(),
369 arch_id => $arch->id(),
383 load_package($schema,$suite,$component,$arch,$pkg)
388 my ($schema,$suite,$pkgs,$p) = @_;
389 my $suite_id = $schema->resultset('Suite')->
390 find_or_create({codename => $suite})->id;
394 my $src_max_last_modified = $schema->resultset('SrcAssociation')->
395 search_rs({suite => $suite_id},
396 {order_by => {-desc => ['me.modified']},
401 my $bin_max_last_modified = $schema->resultset('BinAssociation')->
402 search_rs({suite => $suite_id},
403 {order_by => {-desc => ['me.modified']},
408 print STDERR time." handling packages\n";
409 for my $pkg_tuple (@{$pkgs}) {
410 my ($arch,$component,$pkg) = @{$pkg_tuple};
412 if ($arch eq 'source') {
413 my $source = $pkg->{Package};
414 my $source_ver = $pkg->{Version};
415 if (not exists $maint_cache{$pkg->{Maintainer}}) {
416 my @addrs = getparsedaddrs($pkg->{Maintainer} // '');
418 my $mc = $schema->resultset('Correspondent')->
419 find_or_create({addr => lc($addrs[0]->address())},
420 {key => 'correspondent_addr_idx'}
422 my $full_name = $addrs[0]->phrase();
423 $full_name =~ s/^\"|\"$//g;
424 $full_name =~ s/^\s+|\s+$//g;
425 # $sv->discard_changes;
426 my $maint = $schema->resultset('Maintainer')->
427 find_or_create({name => $pkg->{Maintainer},
428 correspondent => $mc->id},
429 {key => 'maintainer_name_idx'},
431 $mc->find_or_create_related('correspondent_full_names',
432 {full_name => $full_name},
433 {key => 'correspondent_full_name_correspondent_full_name_idx'}
436 $maint_cache{$pkg->{Maintainer}} = $maint;
439 if (not exists $source_cache{$source}{$source_ver}) {
440 my $sp = $schema->resultset('SrcPkg')->
441 find_or_create({pkg => $source});
442 my $sv = $sp->find_or_create_related('src_vers',
443 {ver => $source_ver});
444 $source_cache{$source}{$source_ver} = $sv;
445 if (exists $maint_cache{$pkg->{Maintainer}}) {
446 $source_cache{$source}{$source_ver}->
447 set_from_related('maintainer',
448 $maint_cache{$pkg->{Maintainer}}
450 $source_cache{$source}{$source_ver}->update;
453 $schema->resultset('SrcAssociation')->
454 update_or_create({suite => $suite_id,
455 source => $source_cache{$source}{$source_ver}->id,
458 {key => 'src_associations_source_suite'}
461 my $ar = $schema->resultset('Arch')->
462 find_or_create(arch => $arch);
463 my $bp = $schema->resultset('BinPkg')->
464 find_or_create({pkg => $pkg->{Package}});
465 my $source = $pkg->{Source} // $pkg->{Package};
466 my $source_ver = $pkg->{Version};
467 if ($source =~ /^\s*(\S+) \(([^\)]+)\)\s*$/) {
468 ($source,$source_ver) = ($1,$2);
470 if (not exists $source_cache{$source}{$source_ver}) {
471 my $sp = $schema->resultset('SrcPkg')->
472 find_or_create({pkg => $source});
473 my $sv = $sp->find_or_create_related('src_vers',
474 {ver => $source_ver});
475 $source_cache{$source}{$source_ver} = $sv;
477 my $bv = $bp->find_or_create_related('bin_vers',
478 {ver => $pkg->{Version},
479 src_ver => $source_cache{$source}{$source_ver}->id,
482 $schema->resultset('BinAssociation')->
483 update_or_create({suite => $suite_id,
487 {key => 'bin_associations_bin_suite'}
491 print STDERR time." deleting associations\n";
492 # delete old binary associations in this suite which have not recently been
494 $schema->resultset('BinAssociation')->
495 search_rs({suite => $suite_id,
496 modified => {'<',$bin_max_last_modified->modified()},
498 $bin_max_last_modified;
499 $schema->resultset('SrcAssociation')->
500 search_rs({suite => $suite_id,
501 modified => {'<',$src_max_last_modified->modified()},
503 $src_max_last_modified;
506 sub load_packages_dbi {
507 my ($schema,$suite,$pkgs,$p) = @_;
508 my $suite_id = $schema->resultset('Suite')->
509 find_or_create({codename => $suite})->id;
513 my $src_max_last_modified = $schema->resultset('SrcAssociation')->
514 search_rs({suite => $suite_id},
515 {order_by => {-desc => ['me.modified']},
520 my $bin_max_last_modified = $schema->resultset('BinAssociation')->
521 search_rs({suite => $suite_id},
522 {order_by => {-desc => ['me.modified']},
527 print STDERR time." handling packages\n";
530 my $dbi = $schema->storage()->dbh();
532 (insert_correspondent => <<'EOF',
534 INSERT INTO correspondent (addr) VALUES (?)
535 ON CONFLICT (addr) DO NOTHING RETURNING id
539 SELECT id FROM correspondent WHERE addr = ?
542 insert_maintainer => <<'EOF',
544 INSERT INTO maintainer (name,correspondent) VALUES (?,?)
545 ON CONFLICT (name) DO NOTHING RETURNING id
549 SELECT id FROM maintainer WHERE name = ?
552 insert_correspondent_full_name => <<'EOF',
554 INSERT INTO correspondent_full_name (correspondent,full_name)
555 VALUES (?,?) ON CONFLICT (correspondent,full_name) DO NOTHING RETURNING id
559 SELECT id FROM correspondent_full_name WHERE correspondent=? AND full_name = ?
562 insert_src_pkg => <<'EOF',
564 INSERT INTO src_pkg (pkg)
565 VALUES (?) ON CONFLICT (pkg,disabled) DO NOTHING RETURNING id
569 SELECT id FROM src_pkg where pkg = ? AND disabled = 'infinity'::timestamptz
572 insert_src_ver => <<'EOF',
573 INSERT INTO src_ver (src_pkg,ver,maintainer)
574 VALUES (?,?,?) ON CONFLICT (src_pkg,ver) DO
575 UPDATE SET maintainer = ?
578 insert_src_associations => <<'EOF',
579 INSERT INTO src_associations (suite,source)
580 VALUES (?,?) ON CONFLICT (suite,source) DO
581 UPDATE SET modified = NOW()
584 insert_bin_pkg => <<'EOF',
586 INSERT INTO bin_pkg (pkg)
587 VALUES (?) ON CONFLICT (pkg) DO NOTHING RETURNING id
591 SELECT id FROM bin_pkg where pkg = ?
594 insert_bin_ver => <<'EOF',
596 INSERT INTO bin_ver (bin_pkg,src_ver,arch,ver)
597 VALUES (?,?,?,?) ON CONFLICT (bin_pkg,arch,ver) DO NOTHING RETURNING id
601 SELECT id FROM bin_ver WHERE bin_pkg = ? AND arch = ? AND ver = ?
604 insert_bin_associations => <<'EOF',
605 INSERT INTO bin_associations (suite,bin)
606 VALUES (?,?) ON CONFLICT (suite,bin) DO
607 UPDATE SET modified = NOW()
611 _prepare_sql_statements($dbi,$st,\%s);
612 for my $pkg_tuple (@{$pkgs}) {
613 my ($arch,$component,$pkg) = @{$pkg_tuple};
615 sub _get_maintainer {
616 my ($addr,$dbi,$st,$schema) = @_;
618 $schema->resultset('Maintainer')->
619 search({name => $addr},
620 {result_class => 'DBIx::Class::ResultClass::HashRefInflator',
626 my @addrs = getparsedaddrs($addr // '');
630 $c_id = _select_one($dbi,$st,
631 'insert_correspondent',
632 lc($addrs[0]->address()),
633 lc($addrs[0]->address()),
635 my $full_name = $addrs[0]->phrase();
636 $full_name =~ s/^\"|\"$//g;
637 $full_name =~ s/^\s+|\s+$//g;
638 _select_one($dbi,$st,
639 'insert_correspondent_full_name',
647 _select_one($dbi,$st,
655 if ($arch eq 'source') {
656 my $source = $pkg->{Package};
657 my $source_ver = $pkg->{Version};
658 if (not exists $maint_cache{$pkg->{Maintainer}}) {
659 $maint_cache{$pkg->{Maintainer}} =
660 _get_maintainer($pkg->{Maintainer},$dbi,$st,$schema);
662 if (not exists $source_cache{$source}{$source_ver}) {
664 _select_one($dbi,$st,'insert_src_pkg',
669 _select_one($dbi,$st,'insert_src_ver',
672 $maint_cache{$pkg->{Maintainer}},
673 $maint_cache{$pkg->{Maintainer}});
674 $source_cache{$source}{$source_ver} = $sv_id;
676 _select_one($dbi,$st,'insert_src_associations',
678 $source_cache{$source}{$source_ver}
681 if (not exists $arch_cache{$arch}) {
682 my $ar = $schema->resultset('Arch')->
683 find_or_create(arch => $arch);
684 $arch_cache{$arch} = $ar->id;
687 _select_one($dbi,$st,
692 my $source = $pkg->{Source} // $pkg->{Package};
693 my $source_ver = $pkg->{Version};
694 if ($source =~ /^\s*(\S+) \(([^\)]+)\)\s*$/) {
695 ($source,$source_ver) = ($1,$2);
697 if (not exists $source_cache{$source}{$source_ver}) {
699 _select_one($dbi,$st,'insert_src_pkg',
703 if (not exists $maint_cache{$pkg->{Maintainer}}) {
704 $maint_cache{$pkg->{Maintainer}} =
705 _get_maintainer($pkg->{Maintainer},$dbi,$st,$schema);
708 _select_one($dbi,$st,'insert_src_ver',
711 $maint_cache{$pkg->{Maintainer}},
712 $maint_cache{$pkg->{Maintainer}});
713 $source_cache{$source}{$source_ver} = $sv_id;
716 _select_one($dbi,$st,'insert_bin_ver',
718 $source_cache{$source}{$source_ver},
726 _select_one($dbi,$st,'insert_bin_associations',
732 print STDERR time." deleting associations\n";
733 # delete old binary associations in this suite which have not recently been
735 $schema->resultset('BinAssociation')->
736 search_rs({suite => $suite_id,
737 modified => {'<',$bin_max_last_modified->modified()},
739 $bin_max_last_modified;
740 $schema->resultset('SrcAssociation')->
741 search_rs({suite => $suite_id,
742 modified => {'<',$src_max_last_modified->modified()},
744 $src_max_last_modified;
748 my ($dbh,$sth,$s,@bind_vals) = @_;
749 if (not defined $sth->{$s}) {
750 die "No such statement '$s'";
752 $sth->{$s}->execute(@bind_vals) or
753 die "Unable to select one: ".$dbh->errstr();
754 my $results = $sth->{$s}->fetchall_arrayref([0]);
755 $sth->{$s}->finish();
756 return (ref($results) and ref($results->[0]))?$results->[0][0]:undef;
759 sub _prepare_sql_statements {
760 my ($dbi,$st,$s) = @_;
761 for my $key (keys %{$s}) {
762 $st->{$key} = $dbi->prepare($s->{$key}) //
763 die "Unable to prepare sql statement: ".$dbi->errstr;
778 load_suite($schema,$codename,$suite,$version,$active);
783 my ($schema,$codename,$suite,$version,$active) = @_;
784 if (ref($codename)) {
785 ($codename,$suite,$version) =
786 @{$codename}{qw(Codename Suite Version)};
789 my $s = $schema->resultset('Suite')->find_or_create({codename => $codename});
790 $s->suite_name($suite);
791 $s->version($version);