[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.10, Wed Jul 9 15:59:57 2008 UTC revision 1.11, Thu Jul 10 19:35:34 2008 UTC
# Line 19  Line 19 
19  #  #
20  #  DBMS tables used:  #  DBMS tables used:
21  #  #
22  #     dlit  #     dlits
23  #     flds => "status char(1), md5_hash varchar(32), pubmed varchar(16), curator varchar(30), go_code varchar(15)"  #     flds => "status char(1), md5_hash varchar(32), pubmed varchar(16), curator varchar(30), go_code varchar(15)"
24  #  #
25  #     titles  #     titles
# Line 44  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 76  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
# Line 119  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 140  Line 147 
147  elsif ( $ref_id )  elsif ( $ref_id )
148  {  {
149      my $and_status = $show_just ? " AND ( dlits.status = '$show_just' )" : '';      my $and_status = $show_just ? " AND ( dlits.status = '$show_just' )" : '';
150      my $tuples = $rdbH->SQL( "SELECT DISTINCT status,md5_hash,curator      my $tuples = $rdbH->SQL( "SELECT DISTINCT status,md5_hash,curator "
151                                FROM dlits                             . "FROM dlits "
152                                WHERE ( pubmed = '$ref_id' )"                             . "WHERE ( pubmed = '$ref_id' )"
153                                . $and_status                                . $and_status
154                             );                             );
155      my @to_display = ();      my @to_display = ();
# Line 166  Line 173 
173  {  {
174      my $roleQ = quotemeta $role;      my $roleQ = quotemeta $role;
175      my $and_status = $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,dlits.curator      my $tuples = $rdbH->SQL( "SELECT DISTINCT dlits.status,dlits.md5_hash,dlits.pubmed,dlits.curator "
177                                FROM hash_role,dlits                             . "FROM hash_role,dlits "
178                                WHERE hash_role.role = '$roleQ' AND hash_role.md5_hash = dlits.md5_hash"                             . "WHERE ( hash_role.role = '$roleQ') AND ( hash_role.md5_hash = dlits.md5_hash )"
179                                . $and_status                                . $and_status
180                             );                             );
181      my @to_display = ();      my @to_display = ();
# Line 198  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 229  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,dlits.curator      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 )
# Line 254  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 270  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 338  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 478  Line 486 
486    
487          if ( $genes_in_pub{ $pubmed } > 1 )          if ( $genes_in_pub{ $pubmed } > 1 )
488          {          {
489              #  This link intentionally omits filtering by status              #  This link intentionally omits $show_just
490              my $href = "get_dlits.cgi?user=$user&ref_id=$pubmed";              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>)";              $pub_link .= "<BR />(<A HRef='$href' Target=_blank>$genes_in_pub{$pubmed} seqs</A>)";
492          }          }
493          if (($i % 15) == 14) { push(@$tab,$col_hdrs) }          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 ] );          push( @$tab, [ @codes, $prev_curator, &HTML::fid_link( $cgi, $peg ), $func, $gs, $pub_link, $title ] );
495      }      }
496    
# Line 544  Line 552 
552  }  }
553    
554    
555  #     dlit  #     dlits
556  #     flds => "status char(1), md5_hash varchar(32), pubmed varchar(16), curator varchar(30), go_code varchar(15)"  #     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 {  sub all_seqs_in_pub {
559      my ( $fig, $pubmed, $status ) = @_;      my ( $fig, $pubmed, $status ) = @_;
560      my $rdbH = $fig->db_handle;      my $rdbH = $fig->db_handle;
561      my $and_status = $status ? " AND ( status = '$status' )" : "";      my $and_status = $status ? " AND ( status = '$status' )" : "";
562      my $response = $rdbH->SQL( "SELECT DISTINCT md5_hash      my $response = $rdbH->SQL( "SELECT DISTINCT md5_hash "
563                                  FROM dlits                               . "FROM dlits "
564                                  WHERE ( pubmed = '$pubmed' )"                               . "WHERE ( pubmed = '$pubmed' )"
565                                      . $and_status                                      . $and_status
566                               );                               );
567      return ( ref $response ) ? map { $_->[0] } @$response : ();      return ( ref $response ) ? map { $_->[0] } @$response : ();
# Line 598  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.10  
changed lines
  Added in v.1.11

MCS Webmaster
ViewVC Help
Powered by ViewVC 1.0.3