lib/CXGN/People/UserMap.pm

   1
   2
   3
   4 =head1 NAME
   5
   6 CXGN::People::UserMap - a class that manages the database end for user defined maps on SGN.
   7
   8 =head1 SYNOPSYS
   9
  10
  11 =head1 DESCRIPTION
  12
  13
  14
  15 =head1 AUTHOR(S)
  16
  17 Lukas Mueller <lam87@cornell.edu>
  18
  19 =head1 VERSION
  20
  21 1e-100
  22
  23 =head1 FUNCTIONS
  24
  25 This class implements the following functions:
  26
  27 =cut
  28
  29
  30 use strict;
  31
  32 package CXGN::People::UserMap;
  33
  34 use CXGN::DB::ModifiableI;
  35 use CXGN::Marker;
  36 use CXGN::People::UserMapData;
  37
  38 use base qw | CXGN::DB::ModifiableI |;
  39
  40
  41
  42 =head2 function new()
  43
  44   Synopsis:
  45   Arguments:
  46   Returns:
  47   Side effects:
  48   Description:
  49
  50 =cut
  51
  52 sub new {
  53     my $class = shift;
  54     my $dbh = shift;
  55     my $id = shift;
  56     my $self= $class->SUPER::new($dbh);
  57     $self->set_sql();
  58     $self->set_user_map_id($id);
  59
  60     if ($id) {
  61         $self->fetch();
  62
  63         # check if the map id supplied was legal...
  64         if (!$self->get_user_map_id()) {
  65             print STDERR "UserMap: An illegal ID was passed to the constructor.\n";
  66             return undef;
  67         }
  68         else {
  69             print STDERR "Returning map with id: ".$self->get_user_map_id()."\n";
  70         }
  71     }
  72     return $self;
  73 }
  74
  75 =head2 function fetch()
  76
  77   Synopsis:
  78   Arguments:    none
  79   Returns:
  80   Side_effects:
  81   Description:  populates the object from the database
  82
  83 =cut
  84
  85 sub fetch {
  86     my $self = shift;
  87     my $sth = $self->get_sql('fetch');
  88     $sth->execute($self->database_id($self->get_user_map_id()));
  89     while (my ($user_map_id, $short_name, $long_name, $abstract, $is_public, $parent1, $parent2, $sp_person_id, $obsolete, $modified_date, $create_date) = $sth->fetchrow_array()) {
  90         $self->set_user_map_id($user_map_id);
  91         $self->set_short_name($short_name);
  92         $self->set_long_name($long_name);
  93         $self->set_abstract($abstract);
  94         $self->set_is_public($is_public);
  95         $self->set_parent1($parent1);
  96         $self->set_parent2($parent2);
  97         $self->set_sp_person_id($sp_person_id);
  98         $self->set_obsolete($obsolete);
  99         $self->set_modification_date($modified_date);
 100         $self->set_create_date($create_date);
 101     }
 102 }
 103
 104 =head2 function store()
 105
 106   Synopsis:
 107   Arguments:
 108   Returns:
 109   Side effects:
 110   Description:
 111
 112 =cut
 113
 114 sub store {
 115     my $self = shift;
 116
 117     # adjust is public for database use
 118
 119     if ($self->get_user_map_id()) {
 120
 121                 my $sth = $self->get_sql("update");
 122                 $sth->execute(
 123                        $self->get_short_name(),
 124                        $self->get_long_name(),
 125                        $self->get_abstract(),
 126                        ($self->get_is_public() ? 1 : 0),
 127                        $self->get_parent1(),
 128                        $self->get_parent2(),
 129                        $self->get_sp_person_id(),
 130                        $self->get_obsolete(),
 131                        $self->database_id($self->get_user_map_id())
 132                 );
 133                 return $self->get_user_map_id();
 134     }
 135     else {
 136                 my $sth = $self->get_sql('insert');
 137                 $sth->execute(
 138                       $self->get_short_name(),
 139                       $self->get_long_name(),
 140                       $self->get_abstract(),
 141                       ($self->get_is_public() ? "t" : "f"),
 142                       $self->get_parent1(),
 143                       $self->get_parent2(),
 144                       $self->get_sp_person_id(),
 145                       $self->get_obsolete()
 146                 );
 147                 $sth = $self->get_sql('currval');
 148                 $sth->execute();
 149                 my ($id) = $sth->fetchrow_array();
 150                 $self->set_user_map_id("u".$id);
 151                 print STDERR "New user_map_id = ".$self->get_user_map_id()."\n";
 152                 return $self->get_user_map_id();
 153     }
 154 }
 155
 156 =head2 function delete
 157
 158   Synopsis:
 159   Arguments:
 160   Returns:
 161   Side effects:
 162   Description:
 163
 164 =cut
 165
 166 sub delete {
 167     my $self = shift;
 168     print STDERR " *** DELETING map ".$self->get_user_map_id()."\n";
 169     # obsolete the user_map entry
 170     my $sth = $self->get_sql('delete_map');
 171     $sth->execute($self->database_id($self->get_user_map_id()));
 172     # obsolete the user_map_data entries
 173     my $sth2 = $self->get_sql('delete_map_data');
 174     $sth2->execute($self->database_id($self->get_user_map_id()));
 175
 176 }
 177
 178 =head2 accessors set_user_map_id(), get_user_map_id()
 179
 180   Property:
 181   Setter Args:
 182   Getter Args:
 183   Getter Ret:
 184   Side Effects:
 185   Description:
 186
 187 =cut
 188
 189 sub get_user_map_id {
 190     my $self=shift;
 191     return $self->{user_map_id};
 192 }
 193
 194 sub set_user_map_id {
 195     my $self=shift;
 196     $self->{user_map_id}=shift;
 197 }
 198
 199 =head2 accessors set_parent1(), get_parent1()
 200
 201   Property:     the name of parent1 [string]
 202   Setter Args:
 203   Getter Args:
 204   Getter Ret:
 205   Side Effects:
 206   Description:
 207
 208 =cut
 209
 210 sub get_parent1 {
 211     my $self=shift;
 212     return $self->{parent1};
 213 }
 214
 215 sub set_parent1 {
 216     my $self=shift;
 217     $self->{parent1}=shift;
 218 }
 219
 220 =head2 accessors set_parent2(), get_parent2()
 221
 222   Property:     the name of parent2 [string]
 223   Setter Args:
 224   Getter Args:
 225   Getter Ret:
 226   Side Effects:
 227   Description:
 228
 229 =cut
 230
 231 sub get_parent2 {
 232     my $self=shift;
 233     return $self->{parent2};
 234 }
 235
 236 sub set_parent2 {
 237     my $self=shift;
 238     $self->{parent2}=shift;
 239 }
 240
 241 =head2 accessors set_short_name(), get_short_name()
 242
 243   Property:     the short name of the map.
 244   Side Effects:
 245   Description:
 246
 247 =cut
 248
 249 sub get_short_name {
 250     my $self=shift;
 251     return $self->{short_name};
 252 }
 253
 254 sub set_short_name {
 255     my $self=shift;
 256     $self->{short_name}=shift;
 257 }
 258
 259 =head2 accessors set_long_name(), get_long_name()
 260
 261   Property:     the long name of the map.
 262   Side Effects:
 263   Description:
 264
 265 =cut
 266
 267 sub get_long_name {
 268     my $self=shift;
 269     return $self->{long_name};
 270 }
 271
 272 sub set_long_name {
 273     my $self=shift;
 274     $self->{long_name}=shift;
 275 }
 276
 277 =head2 accessors set_abstract(), get_abstract()
 278
 279   Property:
 280   Setter Args:
 281   Getter Args:
 282   Getter Ret:
 283   Side Effects:
 284   Description:
 285
 286 =cut
 287
 288 sub get_abstract {
 289     my $self=shift;
 290     return $self->{abstract};
 291 }
 292
 293 sub set_abstract {
 294     my $self=shift;
 295     $self->{abstract}=shift;
 296 }
 297
 298 =head2 accessors set_is_public(), get_is_public()
 299
 300   Property:
 301   Setter Args:
 302   Getter Args:
 303   Getter Ret:
 304   Side Effects:
 305   Description:
 306
 307 =cut
 308
 309 sub get_is_public {
 310     my $self=shift;
 311     if (!exists($self->{is_public}) || !defined($self->{is_public}) || !$self->{is_public} || $self->{is_public} eq "f") {
 312         $self->{is_public}=0;
 313     }
 314     else {
 315         $self->{is_public}=1;
 316     }
 317
 318     return $self->{is_public};
 319 }
 320
 321 sub set_is_public {
 322     my $self=shift;
 323     my $is_public = shift;
 324     print STDERR "set_is_public: $is_public ...";
 325     if ($is_public eq "t") { $is_public=1; }
 326     if ($is_public eq "f" || !$is_public) { $is_public=0; }
 327
 328     print STDERR " set to $is_public!\n";
 329     $self->{is_public}=$is_public;
 330 }
 331
 332
 333 =head2 check_file()
 334
 335  Usage:
 336  Desc:
 337  Ret:
 338  Args:
 339  Side Effects:
 340  Example:
 341
 342 =cut
 343
 344 sub check_file {
 345     my $self = shift;
 346     my $filename = shift;
 347
 348     my ($FILE);
 349     open($FILE, "<$filename") || die "Can't open the file $filename";
 350     my $line_count = 1;
 351     my @error_lines = ();
 352
 353     # throw away the header line.
 354     #
 355     my $header = <$FILE>;
 356
 357     while (<$FILE>) {
 358         chomp;
 359         if (/^\#/) { next; }  # exclude commented lines
 360         if (/^$/) { next; }   # exclude empty lines
 361
 362         if (my $error = $self->check_line($_)) {
 363             push @error_lines, [$line_count, $error];
 364         }
 365         $line_count++;
 366     }
 367     return @error_lines;
 368 }
 369
 370 sub check_line {
 371     my $self = shift;
 372     my $line = shift;
 373     my ($marker_name, $marker_id, $linkage_group, $position, $confidence, $protocol) = $self->get_fields($line);
 374     my $error = "";
 375     if (!$marker_name) {
 376         $error .= "missing marker name ";
 377     }
 378     if (!$linkage_group) {
 379         $error .= "missing linkage group ";
 380     }
 381     if (!$position) {
 382         $error .= "missing position ";
 383     }
 384
 385     return $error;
 386 }
 387
 388 # this function returns marker_name, marker_id, linkage_group, position, confidence, protol. Can be overridden in subclass to parse other file formats.
 389 #
 390 sub get_fields {
 391     my $self = shift;
 392     my $line = shift;
 393     return my ($marker_name, $marker_id, $linkage_group, $position, $confidence, $protocol) = split /\t/, $line;
 394 }
 395
 396 =head2 get_map_stats_from_file()
 397
 398  Usage:
 399  Desc:
 400  Ret:
 401  Args:
 402  Side Effects:
 403  Example:
 404
 405 =cut
 406
 407 sub get_map_stats_from_file {
 408     my $self = shift;
 409     my $filename = shift;
 410
 411     my ($FILE);
 412
 413     open($FILE, "<$filename") || die "Can't open the file $filename";
 414
 415     my $line_count = 1;
 416     my @error_lines = ();
 417
 418     # throw away the header line.
 419     #
 420     my $header = <$FILE>;
 421     my %chr = ();
 422
 423     while (<$FILE>) {
 424         chomp;
 425         if (/^\#/) { next; }  # exclude commented lines
 426         if (/^$/) { next; }   # exclude empty lines
 427
 428         my ($marker_name, $marker_id, $linkage_group, $position, $confidence, $protocol) = $self->get_fields($_);
 429         $chr{$linkage_group}++;
 430         $line_count++;
 431     }
 432     return %chr;
 433
 434 }
 435
 436
 437 =head2 assign_markers()
 438
 439  Usage:        $map->assign_markers()
 440  Desc:         tries to find each marker in the user map
 441                by matching its name against markers in the sgn.marker
 442                table. Sets the marker_id of the sgn marker in the
 443                user_map_data table.
 444  Ret:          nothing
 445  Args:         none
 446  Side Effects: attributes marker_id\'s to markers in the user_map.
 447  Notes:        not clear how aliases should be handled.
 448  Example:
 449
 450 =cut
 451
 452 sub assign_markers {
 453     my $self = shift;
 454     print STDERR "Assigning markers for user map ".$self->get_user_map_id()."\n";
 455
 456     my $sth = $self->get_sql('select_markers');
 457     $sth->execute($self->database_id($self->get_user_map_id()));
 458
 459     my $marker_h = $self->get_sql('select_marker_by_alias');
 460     my $update_h = $self->get_sql('update_marker');
 461     while (my ($user_map_data_id, $marker_name) = $sth->fetchrow_array()) {
 462         $marker_h->execute($marker_name);
 463         my ($marker_id) = $marker_h->fetchrow_array();
 464         if ($marker_id) {
 465             $update_h->execute($marker_id, $user_map_data_id);
 466             print STDERR "Associated marker$marker_name with id $marker_id for row $user_map_data_id.\n";
 467         }
 468         else {
 469             print STDERR "$marker_name was not found in the SGN database.\n";
 470         }
 471     }
 472     print STDERR "Done assigning markers.\n";
 473 }
 474
 475
 476 =head2 import_map()
 477
 478   Usage:         $usermap->import("/Users/mueller/example.map",
 479                         $name, $sp_personid)
 480   Desc:          imports the map given in the file into the
 481                  SGN usermap tables
 482   Ret:           an empty list if successful, an array with line number and the
 483                  error found otherwise.
 484   Args:          a filename [string], the name of the map [string],
 485                  and a userid [int]
 486                  The file has the following columns, tab-delimited, with
 487                  one header line that will be ignored:
 488                  marker_name [string]
 489                  marker_id [integer]
 490                  linkage_group [string]
 491                  position [cM] [real]
 492                  confidence
 493                  protocol
 494   Side Effects:  changes the database contents!
 495   Example:
 496
 497 =cut
 498
 499  sub import_map {
 500      my $self = shift;
 501      my $filename = shift;
 502      my $name = shift;
 503      my $sp_person_id = shift;
 504
 505
 506      #print STDERR "Importing map...\n";
 507      #$self->set_name($name);
 508      $self->set_sp_person_id($sp_person_id);
 509      my $user_map_id = $self->store();
 510      #print STDERR "********* USER MAP ID = $user_map_id\n\n";
 511     my ($FILE);
 512     open($FILE, "<$filename") || die "Can't open the file $filename";
 513     my $line_count = 1;
 514      my $header_line = <$FILE>;  #exclude first header line.
 515     while (<$FILE>) {
 516         chomp;
 517         if (/^\#/) { next; }  # exclude commented lines
 518         if (/^$/) { next; }   # exclude empty lines
 519         if (my $error = $self->check_line($_)) {
 520             return ($line_count, $error);
 521         }
 522         my ($marker_name, $marker_id, $linkage_group, $position, $confidence, $protocol) = split /\t/;
 523
 524         my $user_map_db_id = $self->database_id($user_map_id);
 525         my $map_data = CXGN::People::UserMapData->new($self->get_dbh());
 526         $map_data->set_marker_name($marker_name);
 527         $map_data->set_user_map_id($user_map_id);
 528         $map_data->set_marker_id($marker_id);
 529         $map_data->set_linkage_group($linkage_group);
 530         $map_data->set_position($position);
 531         $map_data->set_confidence($confidence);
 532         $map_data->set_protocol($protocol);
 533         $map_data->set_user_map_id($user_map_db_id);
 534         $map_data->set_sp_person_id($sp_person_id);
 535         $map_data->set_obsolete('f');
 536         $map_data->store();
 537         $line_count++;
 538     }
 539     return ();
 540 }
 541
 542
 543
 544
 545 =head2 function create_schema()
 546
 547   Synopsis:     CXGN::Map::User::create_schema($dbh)
 548   Arguments:    a valid database handle
 549   Returns:      nothing
 550   Side effects: creates the sgn.user_map and sgn.user_map_data tables
 551                 with appropriate permissions.
 552   Description:
 553
 554 =cut
 555
 556 sub create_schema {
 557         #Didn't convert to CXGN::Class::DBI method due to creation infrequency
 558     my $dbh = shift;
 559     eval {
 560         my $sgn_base = $dbh->base_schema("sgn");
 561         $dbh ||= DBH();
 562         print STDERR "Generating table sgn_people.user_map...\n";
 563         my $create_user_map = "CREATE table sgn_people.user_map (
 564                              user_map_id serial primary key,
 565                              short_name varchar(40),
 566                              long_name varchar(100),
 567                              abstract text,
 568                              is_public boolean,
 569                              parent1_accession_id bigint REFERENCES $sgn_base.accession,
 570                              parent1 varchar(100),
 571                              parent2_accession_id bigint REFERENCES $sgn_base.accession,
 572                              parent2 varchar(100),
 573                              sp_person_id bigint REFERENCES sgn_people.sp_person,
 574                              obsolete boolean,
 575                              modified_date timestamp with time zone,
 576                              create_date timestamp with time zone
 577                            )";
 578         $dbh->do($create_user_map);
 579         print STDERR "adjusting access privileges for table user_map...\n";
 580         $dbh->do("GRANT select, update, insert ON sgn_people.user_map TO web_usr");
 581         print STDERR "...\n";
 582         $dbh->do("GRANT select, update, insert, delete ON sgn_people.user_map_user_map_id_seq TO web_usr");
 583
 584         print STDERR "Generating table sgn_people.user_map_data...\n";
 585         my $create_user_map_data =
 586             "CREATE table sgn_people.user_map_data (
 587                              user_map_data_id serial primary key,
 588                              user_map_id bigint REFERENCES sgn_people.user_map,
 589                              marker_name varchar(50),
 590                              protocol varchar(50),
 591                              marker_id bigint REFERENCES $sgn_base.marker,
 592                              linkage_group varchar(20),
 593                              position numeric(20,4),
 594                              confidence varchar(20),
 595                              sp_person_id bigint REFERENCES sgn_people.sp_person,
 596                              obsolete boolean,
 597                              modified_date timestamp with time zone,
 598                              create_date timestamp with time zone
 599                            )";
 600         $dbh->do($create_user_map_data);
 601         print STDERR "Adjusting access privileges for table user_map_data...\n";
 602         $dbh->do("GRANT select, update, insert ON sgn_people.user_map_data TO web_usr");
 603         $dbh->do("GRANT select, update, insert, delete ON sgn_people.user_map_data_user_map_data_id_seq TO web_usr");
 604         print STDERR "Done!\n";
 605     };
 606     if ($@) {
 607         print STDERR "Some frigging error occurred... rolling back...\n";
 608         $dbh->rollback();
 609     }
 610     else {
 611         $dbh->commit();
 612         print STDERR "tables are created and committed!\n";
 613     }
 614 }
 615
 616 sub database_id {
 617     my $self =shift;
 618     my $id = shift;
 619     if ($id =~/u(\d+)/) {
 620         return $1;
 621     }
 622     return $id;
 623 }
 624
 625 sub set_sql {
 626       my $self =shift;
 627
 628
 629       $self->{queries} = {
 630
 631                 fetch =>
 632
 633                         "
 634                                 SELECT
 635                                         user_map_id, short_name, long_name, abstract, is_public,
 636                                         parent1, parent2, sp_person_id, obsolete, modified_date,
 637                                         create_date
 638                                 FROM
 639                                         sgn_people.user_map
 640                                 WHERE
 641                                         user_map_id=?
 642                                         AND obsolete='f'
 643                         ",
 644
 645                 update =>
 646
 647                         "
 648                                 UPDATE sgn_people.user_map
 649                                 SET
 650                                         short_name =?,
 651                                         long_name =?,
 652                                         abstract =?,
 653                                         is_public=?,
 654                                         parent1 = ?,
 655                                         parent2 = ?,
 656                                         sp_person_id=?,
 657                                         obsolete=?,
 658                                         modified_date=now()
 659                                 WHERE
 660                                         user_map_id=?
 661                         ",
 662
 663                 insert =>
 664
 665                         "
 666                                 INSERT INTO sgn_people.user_map
 667                                         (short_name, long_name, abstract, is_public, parent1, parent2,
 668                                         sp_person_id, obsolete, modified_date, create_date )
 669                                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, now(), now())
 670                         ",
 671
 672                 currval =>
 673
 674                         " SELECT currval('sgn_people.user_map_user_map_id_seq') ",
 675
 676                 delete_map =>
 677
 678                         "
 679                                 UPDATE sgn_people.user_map
 680                                 SET
 681                                         obsolete='t'
 682                                 WHERE
 683                                         user_map_id=?
 684                         ",
 685
 686
 687                 delete_map_data =>
 688
 689                         "
 690                                 UPDATE sgn_people.user_map_data
 691                                 SET
 692                                         obsolete='t'
 693                                 WHERE
 694                                         user_map_id=?
 695                         ",
 696
 697                 select_markers =>
 698
 699                         "
 700                                 SELECT
 701                                         user_map_data_id,
 702                                         marker_name
 703                                 FROM
 704                                         sgn_people.user_map_data
 705                                 WHERE
 706                                         user_map_id=?
 707                                         AND obsolete='f'
 708                         ",
 709
 710                 select_marker_by_alias =>
 711
 712                         "
 713                                 SELECT
 714                                         marker_id
 715                                 FROM
 716                                         sgn.marker_alias
 717                                 WHERE alias=?
 718                         ",
 719
 720                 update_marker =>
 721
 722                         "
 723                                 UPDATE
 724                                         sgn_people.user_map_data
 725                                 SET
 726                                         marker_id=?
 727                                 WHERE
 728                                         user_map_data_id=?
 729                         ",
 730
 731         };
 732
 733         while(my($k,$v)=each%{$self->{queries}}){
 734                 $self->{query_handles}->{$k} = $self->get_dbh()->prepare($v);
 735         }
 736 }
 737
 738
 739 sub get_sql {
 740     my $self =shift;
 741     my $name = shift;
 742     return $self->{query_handles}->{$name};
 743 }
 744
 745
 746 1;