[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.12, Thu Jul 10 19:52:23 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 194  Line 201 
201      push @$html, $cgi->h3( '<FONT Color=red>Please select a role.</FONT>' ) if $submit4;      push @$html, $cgi->h3( '<FONT Color=red>Please select a role.</FONT>' ) if $submit4;
202    
203      my $where1 = $show_just ? " AND (dlits.status = '$show_just')" : "";      my $where1 = $show_just ? " AND (dlits.status = '$show_just')" : "";
204      my $where2 = $curator   ? " AND (curr_role.curator = '$curator' AND curr_role.role = hash_role.role)" : "";      my($where2, $from_list);
205    
206        $from_list = "hash_role,dlits";
207        if ($curator)
208        {
209            $where2 =  " AND (curr_role.curator = '$curator' AND curr_role.role = hash_role.role)";
210            $from_list .= ",curr_role";
211        }
212    
213      my @roles = sort { lc $a cmp lc $b }  #  Make sort case insensitive      my @roles = sort { lc $a cmp lc $b }  #  Make sort case insensitive
214                  map { $_->[0] }                  map { $_->[0] }
215                  @{ $rdbH->SQL( "SELECT DISTINCT hash_role.role                  @{ $rdbH->SQL( "SELECT DISTINCT hash_role.role "
216                                  FROM hash_role,dlits,curr_role                               . "FROM $from_list "
217                                  WHERE hash_role.md5_hash = dlits.md5_hash $where1 $where2"                               . "WHERE hash_role.md5_hash = dlits.md5_hash $where1 $where2"
218                               ) };                               ) };
219      push( @$html, $cgi->start_form(-action => "get_dlits.cgi", -method => 'post'),      push( @$html, $cgi->start_form(-action => "get_dlits.cgi", -method => 'post'),
220                    $cgi->hidden( -name => 'user',     -value => $user ),                    $cgi->hidden( -name => 'user',     -value => $user ),
# Line 229  Line 243 
243  {  {
244      my $genome = $1;      my $genome = $1;
245      my $where  = $show_just ? " AND (dlits.status = '$show_just')" : '';      my $where  = $show_just ? " AND (dlits.status = '$show_just')" : '';
246      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 "
247                                FROM genome_hash,dlits                             . "FROM genome_hash,dlits "
248                                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"
249                             );                             );
250      my @to_display = ();      my @to_display = ();
251      foreach my $x ( @$tuples )      foreach my $x ( @$tuples )
# Line 254  Line 268 
268    
269      my $where   = $show_just ? " AND (dlits.status = '$show_just')" : "";      my $where   = $show_just ? " AND (dlits.status = '$show_just')" : "";
270    
271      my $genomes = $rdbH->SQL( "SELECT DISTINCT genome_hash.genome      my $genomes = $rdbH->SQL( "SELECT DISTINCT genome_hash.genome "
272                                 FROM genome_hash,dlits                              . "FROM genome_hash,dlits "
273                                 WHERE genome_hash.md5_hash = dlits.md5_hash $where"                              . "WHERE genome_hash.md5_hash = dlits.md5_hash $where"
274                              );                              );
275      my @genomes = sort { lc $a cmp lc $b }  #  Make sort case insensitive      my @genomes = sort { lc $a cmp lc $b }  #  Make sort case insensitive
276                    map { &compute_genome_label( $fig, $_->[0] ) }                    map { &compute_genome_label( $fig, $_->[0] ) }
# Line 270  Line 284 
284                                          -size   => 30                                          -size   => 30
285                                        ),                                        ),
286                    $cgi->br, &show_just_selector( $cgi ),                    $cgi->br, &show_just_selector( $cgi ),
287                    $cgi->br, $cgi->submit( 'Show Genome' ),                    $cgi->br, $cgi->submit( 'Show Genome' ), ' selected above.',
288                    $cgi->br, $cgi->submit( 'Show Roles' ),                    $cgi->br, $cgi->submit( 'Show Roles' ),
289                      ( $curator ? " for subsystem curator '$curator'." : '' ),
290                    $cgi->br, $cgi->submit( 'Change subsystem curator' ), " currently '$curator'.",                    $cgi->br, $cgi->submit( 'Change subsystem curator' ), " currently '$curator'.",
291                    $cgi->end_form                    $cgi->end_form
292           );           );
# Line 338  Line 353 
353      my( $fig, $pubmed ) = @_;      my( $fig, $pubmed ) = @_;
354    
355      my $rdbH  = $fig->db_handle;      my $rdbH  = $fig->db_handle;
356      my $retval = $rdbH->SQL( "SELECT title      my $retval = $rdbH->SQL( "SELECT title "
357                                FROM pubmed_titles                             . "FROM pubmed_titles "
358                                WHERE (pubmed = $pubmed)"                             . "WHERE (pubmed = $pubmed)"
359                             );                             );
360      return ( @$retval > 0 ) ? $retval->[0]->[0] : "";      return ( @$retval > 0 ) ? $retval->[0]->[0] : "";
361  }  }
# Line 478  Line 493 
493    
494          if ( $genes_in_pub{ $pubmed } > 1 )          if ( $genes_in_pub{ $pubmed } > 1 )
495          {          {
496              #  This link intentionally omits filtering by status              #  This link intentionally omits $show_just
497              my $href = "get_dlits.cgi?user=$user&ref_id=$pubmed";              my $href = "get_dlits.cgi?user=$user&ref_id=$pubmed";
498              $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>)";
499          }          }
500          if (($i % 15) == 14) { push(@$tab,$col_hdrs) }          if ( $i && ($i % 15) == 0 ) { push(@$tab,$col_hdrs) }
501          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 ] );
502      }      }
503    
# Line 544  Line 559 
559  }  }
560    
561    
562  #     dlit  #     dlits
563  #     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)"
564    
565  sub all_seqs_in_pub {  sub all_seqs_in_pub {
566      my ( $fig, $pubmed, $status ) = @_;      my ( $fig, $pubmed, $status ) = @_;
567      my $rdbH = $fig->db_handle;      my $rdbH = $fig->db_handle;
568      my $and_status = $status ? " AND ( status = '$status' )" : "";      my $and_status = $status ? " AND ( status = '$status' )" : "";
569      my $response = $rdbH->SQL( "SELECT DISTINCT md5_hash      my $response = $rdbH->SQL( "SELECT DISTINCT md5_hash "
570                                  FROM dlits                               . "FROM dlits "
571                                  WHERE ( pubmed = '$pubmed' )"                               . "WHERE ( pubmed = '$pubmed' )"
572                                      . $and_status                                      . $and_status
573                               );                               );
574      return ( ref $response ) ? map { $_->[0] } @$response : ();      return ( ref $response ) ? map { $_->[0] } @$response : ();
# Line 598  Line 613 
613  }  }
614    
615    
616    # Not sure why this was not done before
617    
618    sub add_pubmed_index {
619        my ( $rdbH, $html ) = @_;
620        return if ! $rdbH;
621        my $rtn = $rdbH->SQL( 'SHOW INDEX FROM dlits' );
622        push @$html, "<BR />Initial return value from 'SHOW INDEX FROM dlits' = $rtn\n";
623        if ( $rtn == 3 )
624        {
625            my $rtn = $rdbH->create_index( idx  => "pubmed_in_dlits_ix",
626                                           tbl  => "dlits",
627                                           type => "btree",
628                                           flds => "pubmed" );
629            $rtn = $rdbH->SQL( 'SHOW INDEX FROM dlits' );
630            push @$html, "<BR />Final return value from 'SHOW INDEX FROM dlits' = $rtn<BR />\n";
631        }
632        else
633        {
634            push @$html, "<BR />Index might already exist<BR />\n";
635        }
636    }

Legend:
Removed from v.1.10  
changed lines
  Added in v.1.12

MCS Webmaster
ViewVC Help
Powered by ViewVC 1.0.3