[Bio] / FigWebServices / get_dlits.cgi Repository:
ViewVC logotype

Diff of /FigWebServices/get_dlits.cgi

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 1.7, Tue Jul 8 01:39:58 2008 UTC revision 1.11, Thu Jul 10 19:35:34 2008 UTC
# Line 16  Line 16 
16  # http://www.theseed.org/LICENSE.TXT.  # http://www.theseed.org/LICENSE.TXT.
17  #  #
18    
19    #
20    #  DBMS tables used:
21    #
22    #     dlits
23    #     flds => "status char(1), md5_hash varchar(32), pubmed varchar(16), curator varchar(30), go_code varchar(15)"
24    #
25    #     titles
26    #     flds => "pubmed varchar(16), title varchar(1000)"
27    #
28    #     hash_role
29    #     flds => "md5_hash char(32), role varchar(1000)"
30    #
31    #     curr_role
32    #     flds => "curator varchar(30), role varchar(1000)"
33    #
34    #     genome_hash
35    #     flds => "genome varchar(32), md5_hash char(32)"
36    #
37    
38  use FIG;  use FIG;
39  my $fig = new FIG;  my $fig = new FIG;
# Line 26  Line 44 
44  use CGI;  use CGI;
45  my $cgi = new CGI;  my $cgi = new CGI;
46    
47    use Data::Dumper;
48    
49  if (0)  if (0)
50  {  {
# Line 58  Line 77 
77  my($genome);  my($genome);
78    
79  my $html = [];  my $html = [];
80  unshift @$html, "<TITLE>Get Dlits</TITLE>\n";  push @$html, "<TITLE>Get Dlits</TITLE>\n";
81    
82  my $user      = $cgi->param('user');           #  Current user  my $user      = $cgi->param('user');           #  Current user
83  my $curator   = $cgi->param('curator') || '';  #  Filter roles by subsystem curator  my $curator   = $cgi->param('curator') || '';  #  Filter roles by subsystem curator
84  my $genomeD   = $cgi->param('genomeD');        #  Find lit by genome  my $genomeD   = $cgi->param('genomeD');        #  Find lit by genome
85    my $ref_id    = $cgi->param('ref_id');         #  Show data by reference
86  my $role      = $cgi->param('role');           #  Find lit by role  my $role      = $cgi->param('role');           #  Find lit by role
87  my $show_just = $cgi->param('show_just');      #  Filter lit by status  my $show_just = $cgi->param('show_just');      #  Filter lit by status
88     $show_just = ($show_just eq "all") ? '' : $show_just;     $show_just = ($show_just eq "all") ? '' : $show_just;
# Line 71  Line 91 
91    
92  my ( $submit1, $submit2, $submit3, $submit4, $submit5 );  my ( $submit1, $submit2, $submit3, $submit4, $submit5 );
93  if    ( $cgi->param( 'Process Changes' )          ) { $submit5 = 1 }  if    ( $cgi->param( 'Process Changes' )          ) { $submit5 = 1 }
94  if    ( $cgi->param( 'Change subsystem curator' ) ) {}  if    ( $ref_id                                   ) {}
95    elsif ( $cgi->param( 'Change subsystem curator' ) ) {}
96  elsif ( $cgi->param( 'Show Genomes' )             ) { $submit1 = 1 }  elsif ( $cgi->param( 'Show Genomes' )             ) { $submit1 = 1 }
97  elsif ( $cgi->param( 'Show Roles' )               ) { $submit2 = 1 }  elsif ( $cgi->param( 'Show Roles' )               ) { $submit2 = 1 }
98  elsif ( $cgi->param( 'Show Genome' )              ) { $submit3 = 1 }  elsif ( $cgi->param( 'Show Genome' )              ) { $submit3 = 1 }
# Line 99  Line 120 
120      $done = 1;      $done = 1;
121  }  }
122    
123    
124    #  This is a one-time special case to add an index to the dlits table
125    
126    if ( $user eq 'master:gjo' && $cgi->param('add_pubmed_index') ) { &add_pubmed_index( $rdbH, $html ) }
127    
128    
129  #  Process a page of curated literature ----------------------------------------  #  Process a page of curated literature ----------------------------------------
130    
131  if ( $submit5 && ! $done )  if ( $submit5 && ! $done )
# Line 115  Line 142 
142    
143  if ( $done ) { }  if ( $done ) { }
144    
145    #  All sequences in a reference ------------------------------------------------
146    
147    elsif ( $ref_id )
148    {
149        my $and_status = $show_just ? " AND ( dlits.status = '$show_just' )" : '';
150        my $tuples = $rdbH->SQL( "SELECT DISTINCT status,md5_hash,curator "
151                               . "FROM dlits "
152                               . "WHERE ( pubmed = '$ref_id' )"
153                               . $and_status
154                               );
155        my @to_display = ();
156        foreach my $x ( @$tuples )
157        {
158            my( $status, $hash, $prev_curator ) = @$x;
159            my @pegs = grep { $fig->is_real_feature($_) }
160                       $fig->pegs_with_md5( $hash );
161            if ( @pegs > 0 )
162            {
163                push( @to_display, [ $status, $pegs[0], $ref_id, $prev_curator ] );
164            }
165        }
166    
167        &display_set( $fig, $cgi, $html, \@to_display, "Genes linked to Reference: $ref_id", 'Show Reference' ) ;
168    }
169    
170  #  Literature selected by role -------------------------------------------------  #  Literature selected by role -------------------------------------------------
171    
172  elsif ( $submit4 && $role )  elsif ( $submit4 && $role )
173  {  {
174      my $roleQ = quotemeta $role;      my $roleQ = quotemeta $role;
175      my $where = $show_just ? " AND (dlits.status = '$show_just')" : '';      my $and_status = $show_just ? " AND ( dlits.status = '$show_just' )" : '';
176      my $tuples = $rdbH->SQL( "SELECT DISTINCT dlits.status,dlits.md5_hash,dlits.pubmed      my $tuples = $rdbH->SQL( "SELECT DISTINCT dlits.status,dlits.md5_hash,dlits.pubmed,dlits.curator "
177                                FROM hash_role,dlits,pubmed_titles                             . "FROM hash_role,dlits "
178                                WHERE hash_role.role = '$roleQ' AND hash_role.md5_hash = dlits.md5_hash $where"                             . "WHERE ( hash_role.role = '$roleQ') AND ( hash_role.md5_hash = dlits.md5_hash )"
179                                  . $and_status
180                             );                             );
181      my @to_display = ();      my @to_display = ();
182      foreach my $x (@$tuples)      foreach my $x (@$tuples)
183      {      {
184          my($status,$hash,$pubmed) = @$x;          my( $status, $hash, $pubmed, $prev_curator ) = @$x;
185          my @pegs = $fig->pegs_with_md5($hash);          my @pegs = grep { $fig->is_real_feature($_) }
186                       $fig->pegs_with_md5( $hash );
187          if (@pegs > 0)          if (@pegs > 0)
188          {          {
189              push(@to_display,[$status,$pegs[0],$pubmed]);              push( @to_display, [ $status, $pegs[0], $pubmed, $prev_curator ] );
190          }          }
191      }      }
192    
193      &display_set($fig,$cgi,$html,\@to_display,"Genes for Role: $role",'Show Role');      &display_set($fig,$cgi,$html,\@to_display,"Genes for Role: $role",'Show Role');
194  }  }
195    
# Line 150  Line 205 
205    
206      my @roles = sort { lc $a cmp lc $b }  #  Make sort case insensitive      my @roles = sort { lc $a cmp lc $b }  #  Make sort case insensitive
207                  map { $_->[0] }                  map { $_->[0] }
208                  @{ $rdbH->SQL( "SELECT DISTINCT hash_role.role                  @{ $rdbH->SQL( "SELECT DISTINCT hash_role.role "
209                                  FROM hash_role,dlits,curr_role                               . "FROM hash_role,dlits,curr_role "
210                                  WHERE hash_role.md5_hash = dlits.md5_hash $where1 $where2"                               . "WHERE hash_role.md5_hash = dlits.md5_hash $where1 $where2"
211                               ) };                               ) };
212      push( @$html, $cgi->start_form(-action => "get_dlits.cgi", -method => 'post'),      push( @$html, $cgi->start_form(-action => "get_dlits.cgi", -method => 'post'),
213                    $cgi->hidden(-name => 'user',      -value => $user),                    $cgi->hidden(-name => 'user',      -value => $user),
# Line 181  Line 236 
236  {  {
237      my $genome = $1;      my $genome = $1;
238      my $where  = $show_just ? " AND (dlits.status = '$show_just')" : '';      my $where  = $show_just ? " AND (dlits.status = '$show_just')" : '';
239      my $tuples = $rdbH->SQL( "SELECT DISTINCT dlits.status,dlits.md5_hash,dlits.pubmed      my $tuples = $rdbH->SQL( "SELECT DISTINCT dlits.status,dlits.md5_hash,dlits.pubmed,dlits.curator "
240                                FROM genome_hash,dlits                             . "FROM genome_hash,dlits "
241                                WHERE genome_hash.genome = '$genome' AND genome_hash.md5_hash = dlits.md5_hash $where"                             . "WHERE genome_hash.genome = '$genome' AND genome_hash.md5_hash = dlits.md5_hash $where"
242                             );                             );
243      my @to_display = ();      my @to_display = ();
244      foreach my $x ( @$tuples )      foreach my $x ( @$tuples )
245      {      {
246          my ( $status, $hash, $pubmed ) = @$x;          my ( $status, $hash, $pubmed, $prev_curator ) = @$x;
247          foreach my $peg ( $fig->pegs_with_md5($hash) )          push @to_display, map  { [ $status, $_, $pubmed, $prev_curator ] }
248          {                            grep { &FIG::genome_of($_) eq $genome && $fig->is_real_feature($_) }
249              if ( &FIG::genome_of($peg) eq $genome )                            $fig->pegs_with_md5($hash);
             {  
                 push(@to_display,[ $status, $peg, $pubmed ]);  
             }  
         }  
250      }      }
251    
252      &display_set( $fig, $cgi, $html, \@to_display, "Genes for $genomeD", 'Show Genome' );      &display_set( $fig, $cgi, $html, \@to_display, "Genes for $genomeD", 'Show Genome' );
253  }  }
254    
# Line 209  Line 261 
261    
262      my $where   = $show_just ? " AND (dlits.status = '$show_just')" : "";      my $where   = $show_just ? " AND (dlits.status = '$show_just')" : "";
263    
264      my $genomes = $rdbH->SQL( "SELECT DISTINCT genome_hash.genome      my $genomes = $rdbH->SQL( "SELECT DISTINCT genome_hash.genome "
265                                 FROM genome_hash,dlits                              . "FROM genome_hash,dlits "
266                                 WHERE genome_hash.md5_hash = dlits.md5_hash $where"                              . "WHERE genome_hash.md5_hash = dlits.md5_hash $where"
267                              );                              );
268      my @genomes = sort { lc $a cmp lc $b }  #  Make sort case insensitive      my @genomes = sort { lc $a cmp lc $b }  #  Make sort case insensitive
269                    map { &compute_genome_label( $fig, $_->[0] ) }                    map { &compute_genome_label( $fig, $_->[0] ) }
# Line 225  Line 277 
277                                          -size   => 30                                          -size   => 30
278                                        ),                                        ),
279                    $cgi->br, &show_just_selector( $cgi ),                    $cgi->br, &show_just_selector( $cgi ),
280                    $cgi->br, $cgi->submit( 'Show Genome' ),                    $cgi->br, $cgi->submit( 'Show Genome' ), ' selected above.',
281                    $cgi->br, $cgi->submit( 'Show Roles' ),                    $cgi->br, $cgi->submit( 'Show Roles' ),
282                      ( $curator ? " for subsystem curator '$curator'." : '' ),
283                    $cgi->br, $cgi->submit( 'Change subsystem curator' ), " currently '$curator'.",                    $cgi->br, $cgi->submit( 'Change subsystem curator' ), " currently '$curator'.",
284                    $cgi->end_form                    $cgi->end_form
285           );           );
# Line 293  Line 346 
346      my($fig,$pubmed) = @_;      my($fig,$pubmed) = @_;
347    
348      my $rdbH  = $fig->db_handle;      my $rdbH  = $fig->db_handle;
349      my $retval = $rdbH->SQL( "SELECT title      my $retval = $rdbH->SQL( "SELECT title "
350                                FROM pubmed_titles                             . "FROM pubmed_titles "
351                                WHERE (pubmed = $pubmed)"                             . "WHERE (pubmed = $pubmed)"
352                             );                             );
353      return ( @$retval > 0 ) ? $retval->[0]->[0] : "";      return ( @$retval > 0 ) ? $retval->[0]->[0] : "";
354  }  }
# Line 317  Line 370 
370      foreach $_ ( @$to_display ) { $_->[0] =~ s/^\s*$/ /; }  # fix empty status strings      foreach $_ ( @$to_display ) { $_->[0] =~ s/^\s*$/ /; }  # fix empty status strings
371      my @tuples = sort { ( $status_code{$a->[0]} <=> $status_code{$b->[0]} )      my @tuples = sort { ( $status_code{$a->[0]} <=> $status_code{$b->[0]} )
372                       or &FIG::by_fig_id( $a->[1], $b->[1] )                       or &FIG::by_fig_id( $a->[1], $b->[1] )
373                       or ( $a->[2] <=> $b->[2] )                       or ( $a->[2] <=> $b->[2] )  # Numeric sort of PMID
374                        }                        }
375                   @$to_display;                   @$to_display;
376      my $total_tuples = @tuples;      my $total_tuples = @tuples;
377    
378      my $from;                    #  Offset to first item displayed      my $from;                    #  Offset to first item displayed
379      my $lines_left = 100;        #  Really the lines per page      my $n_per_page = 100;        #  Really the lines per page
380    
381      if ( $cgi->param( 'Go to item numbers' ) )      if ( $cgi->param( 'Go to item numbers' ) )
382      {      {
# Line 332  Line 385 
385      else      else
386      {      {
387          $from  = $cgi->param( 'from_line' ) || 0;          $from  = $cgi->param( 'from_line' ) || 0;
388          $from -= $lines_left if $cgi->param( 'Previous page' );          $from -= $n_per_page if $cgi->param( 'Previous page' );
389          $from += $lines_left if $cgi->param( 'Next page' );          $from += $n_per_page if $cgi->param( 'Next page' );
390      }      }
391    
392      #  Cut down the array to that to be displayed      #  Cut down the array to that to be displayed
# Line 343  Line 396 
396          if ( $from < 0 ) { $from = 0 }          if ( $from < 0 ) { $from = 0 }
397          if ( $from > $total_tuples )          if ( $from > $total_tuples )
398          {          {
399              $from = $lines_left * int( ( $total_tuples - 1 ) / $lines_left );              $from = $n_per_page * int( ( $total_tuples - 1 ) / $n_per_page );
400          }          }
401          splice( @tuples, 0, $from );          splice( @tuples, 0, $from );
402          splice( @tuples, $lines_left );          splice( @tuples, $n_per_page );
403      }      }
404      else      else
405      {      {
# Line 366  Line 419 
419      push( @$html, $cgi->hidden( -name => 'user', -value => $user, -override => 1 ) );      push( @$html, $cgi->hidden( -name => 'user', -value => $user, -override => 1 ) );
420    
421      #  Filter roles by subsystem curator      #  Filter roles by subsystem curator
422      my $curator = $cgi->param('curator') || '';      my $curator = $cgi->param('curator');
423      push( @$html, $cgi->hidden( -name => 'curator', -value => $curator, -override => 1 ) ) if $curator;      push( @$html, $cgi->hidden( -name => 'curator', -value => $curator, -override => 1 ) ) if $curator;
424    
425      #  Find lit by genome      #  Find lit by genome
426      my $genomeD = $cgi->param('genomeD');      my $genomeD = $cgi->param('genomeD');
427      push( @$html, $cgi->hidden( -name => 'genomeD', -value => $genomeD, -override => 1 ) ) if $genomeD;      push( @$html, $cgi->hidden( -name => 'genomeD', -value => $genomeD, -override => 1 ) ) if $genomeD;
428    
429        #  Show sequences associated with a reference
430        my $ref_id = $cgi->param('ref_id');
431        push( @$html, $cgi->hidden( -name => 'ref_id', -value => $ref_id, -override => 1 ) ) if $ref_id;
432    
433      #  Find lit by role      #  Find lit by role
434      my $role = $cgi->param('role');      my $role = $cgi->param('role');
435      push( @$html, $cgi->hidden( -name => 'role', -value => $role, -override => 1 ) ) if $role;      push( @$html, $cgi->hidden( -name => 'role', -value => $role, -override => 1 ) ) if $role;
# Line 385  Line 442 
442    
443      push( @$html, $cgi->br );      push( @$html, $cgi->br );
444    
445        #  If this is not a table built around one reference, we want to append
446        #  links to all proteins associated with the reference.  We will
447        #  compute this on a unique set of the references:
448    
449        my %genes_in_pub;
450        if ( ! $ref_id )
451        {
452            foreach ( @tuples )
453            {
454                my $pub = $_->[2];
455                next if defined $genes_in_pub{ $pub };
456                #  This count intentionally omits filtering by status
457                $genes_in_pub{ $pub } = scalar ( &all_seqs_in_pub( $fig, $pub ) );
458            }
459        }
460    
461      #  Build the status selection table      #  Build the status selection table
462    
463      my $col_hdrs = [' ','G','N','R','D','PEG','Function','Genus/Species','PubMed','Title'];      my $col_hdrs = [' ','G','N','R','D','Curator','PEG','Function','Genus/Species','PubMed','Title'];
464      my $tab = [];      my $tab = [];
465      my $i;      my $i;
466      for ($i=0; ($i < @tuples); $i++)      for ($i=0; ($i < @tuples); $i++)
467      {      {
468          my $tuple = $tuples[$i];          my $tuple = $tuples[$i];
469          my( $status, $peg, $pubmed ) = @$tuple;          my( $status, $peg, $pubmed, $prev_curator ) = @$tuple;
470          next if (! $fig->is_real_feature($peg));          next if ( ! $fig->is_real_feature($peg) );   # This should never fail
471    
472            $prev_curator =~ s/^master://i;  #  Remove master from displayed curator
473          my $gs = $fig->genus_species( &FIG::genome_of( $peg ) );          my $gs = $fig->genus_species( &FIG::genome_of( $peg ) );
474          $gs =~ s/^(\S+\s+\S+).*$/$1/;          $gs =~ s/^(\S+\s+\S+).*$/$1/;
475          my $title = &title_of( $fig, $pubmed );          my $title = &title_of( $fig, $pubmed );
# Line 405  Line 480 
480                                         -nolabels => 1                                         -nolabels => 1
481                                       );                                       );
482    
483          if (($i % 15) == 14) { push(@$tab,$col_hdrs) }          my $pub_link = &pubmed_link( $pubmed );
484          push( @$tab, [ @codes, &HTML::fid_link($cgi,$peg), $func, $gs, pubmed_link($pubmed), $title ] );  
485            #  Does this paper cover more than one protein?
486    
487            if ( $genes_in_pub{ $pubmed } > 1 )
488            {
489                #  This link intentionally omits $show_just
490                my $href = "get_dlits.cgi?user=$user&ref_id=$pubmed";
491                $pub_link .= "<BR />(<A HRef='$href' Target=_blank>$genes_in_pub{$pubmed} seqs</A>)";
492            }
493            if ( $i && ($i % 15) == 0 ) { push(@$tab,$col_hdrs) }
494            push( @$tab, [ @codes, $prev_curator, &HTML::fid_link( $cgi, $peg ), $func, $gs, $pub_link, $title ] );
495      }      }
496    
497      push( @$html,&HTML::make_table( $col_hdrs, $tab, $tab_title ) );      push( @$html,&HTML::make_table( $col_hdrs, $tab, $tab_title ) );
# Line 415  Line 500 
500    
501      #  Navigation controls through long lists      #  Navigation controls through long lists
502    
503      if ( defined( $from ) && ( $total_tuples > $lines_left ) )      if ( defined( $from ) && ( $total_tuples > $n_per_page ) )
504      {      {
505          my $i1 = $from + 1;          my $i1 = $from + 1;
506          my $i2 = $from + $lines_left;          my $i2 = $from + $n_per_page;
507          $i2 = $total_tuples if $i2 > $total_tuples;          $i2 = $total_tuples if $i2 > $total_tuples;
508          push( @$html, $cgi->hidden( -name => 'from_line', -value => $from, -override => 1 ) );          push( @$html, $cgi->hidden( -name => 'from_line', -value => $from, -override => 1 ) );
509          push( @$html, $cgi->br, "Currently displaying items $i1 - $i2 of $total_tuples.", $cgi->br );          push( @$html, $cgi->br, "Currently displaying items $i1 - $i2 of $total_tuples.", $cgi->br );
# Line 427  Line 512 
512    
513          my @offsets;          my @offsets;
514          my %labels;          my %labels;
515          for ( my $i = 0; $i < $total_tuples; $i += $lines_left )          for ( my $i = 0; $i < $total_tuples; $i += $n_per_page )
516          {          {
517              push @offsets, $i;              push @offsets, $i;
518              my $imax = $i + $lines_left;              my $imax = $i + $n_per_page;
519              $imax = $total_tuples if $imax > $total_tuples;              $imax = $total_tuples if $imax > $total_tuples;
520              $labels{ $i } = ( $i+1 ) . " - $imax";              $labels{ $i } = ( $i+1 ) . " - $imax";
521          }          }
# Line 449  Line 534 
534    
535      push( @$html, $cgi->br, $cgi->submit( 'Process Changes' ), "entered on this page." );      push( @$html, $cgi->br, $cgi->submit( 'Process Changes' ), "entered on this page." );
536    
537      push( @$html, $cgi->br, $cgi->submit( 'Show Genomes' ), "discarding any changes made on this page." );      #  Display some general navigation buttons.  However, these are not
538        #  consistent with the intended use of the publication-based view.
539    
540        if ( ! $ref_id )
541        {
542            push( @$html, $cgi->br, $cgi->submit( 'Show Genomes' ), "discarding any changes made on this page." );
543      if ( $curator )      if ( $curator )
544      {      {
545          push( @$html, $cgi->br, $cgi->submit( 'Show Roles' ),          push( @$html, $cgi->br, $cgi->submit( 'Show Roles' ),
546                                  "for subsystem curator '$curator', discarding any changes made on this page." );                                  "for subsystem curator '$curator', discarding any changes made on this page." );
547      }      }
   
548      push( @$html, $cgi->br, $cgi->submit( 'Change subsystem curator' ), " currently '$curator', discarding any changes made on this page." );      push( @$html, $cgi->br, $cgi->submit( 'Change subsystem curator' ), " currently '$curator', discarding any changes made on this page." );
549        }
550    
551      push( @$html, $cgi->end_form );      push( @$html, $cgi->end_form );
552  }  }
553    
554    
555    #     dlits
556    #     flds => "status char(1), md5_hash varchar(32), pubmed varchar(16), curator varchar(30), go_code varchar(15)"
557    
558    sub all_seqs_in_pub {
559        my ( $fig, $pubmed, $status ) = @_;
560        my $rdbH = $fig->db_handle;
561        my $and_status = $status ? " AND ( status = '$status' )" : "";
562        my $response = $rdbH->SQL( "SELECT DISTINCT md5_hash "
563                                 . "FROM dlits "
564                                 . "WHERE ( pubmed = '$pubmed' )"
565                                 . $and_status
566                                 );
567        return ( ref $response ) ? map { $_->[0] } @$response : ();
568    }
569    
570    
571  sub desc {  sub desc {
572      my( $html ) = @_;      my( $html ) = @_;
573    
# Line 501  Line 606 
606  }  }
607    
608    
609    # Not sure why this was not done before
610    
611    sub add_pubmed_index {
612        my ( $rdbH, $html ) = @_;
613        return if ! $rdbH;
614        my $rtn = $rdbH->SQL( 'SHOW INDEX FROM dlits' );
615        push @$html, "<BR />Initial return value from 'SHOW INDEX FROM dlits' = $rtn\n";
616        if ( $rtn == 3 )
617        {
618            my $rtn = $rdbH->create_index( idx  => "pubmed_in_dlits_ix",
619                                           tbl  => "dlits",
620                                           type => "btree",
621                                           flds => "pubmed" );
622            $rtn = $rdbH->SQL( 'SHOW INDEX FROM dlits' );
623            push @$html, "<BR />Final return value from 'SHOW INDEX FROM dlits' = $rtn<BR />\n";
624        }
625        else
626        {
627            push @$html, "<BR />Index might already exist<BR />\n";
628        }
629    }

Legend:
Removed from v.1.7  
changed lines
  Added in v.1.11

MCS Webmaster
ViewVC Help
Powered by ViewVC 1.0.3