Diff of /Sprout/ERDB.pm

-revision 1.83, Mon Jan 22 20:23:58 2007 UTC
+revision 1.92, Mon Jun 11 18:51:23 2007 UTC
 Line 6
      use Data::Dumper;
      use XML::Simple;
      use DBQuery;
-     use DBObject;
+     use ERDBObject;
      use Stats;
      use Time::HiRes qw(gettimeofday);
      use Digest::MD5 qw(md5_base64);
-     use FIG;
      use CGI;
  =head1 Entity-Relationship Database Package
-Line 228
+Line 227
  =head3 Indexes
- An entity can have multiple alternate indexes associated with it. The fields must
+ An entity can have multiple alternate indexes associated with it. The fields in an
- all be from the same relation. The alternate indexes assist in ordering results
+ index must all be from the same relation. The alternate indexes assist in searching
- from a query. A relationship can have up to two indexes-- a I<to-index> and a
+ on fields other than the entity ID. A relationship has at least two indexes-- a I<to-index> and a
- I<from-index>. These order the results when crossing the relationship. For
+ I<from-index> that order the results when crossing the relationship. For
  example, in the relationship C<HasContig> from C<Genome> to C<Contig>, the
  from-index would order the contigs of a ganome, and the to-index would order
- the genomes of a contig. A relationship's index must specify only fields in
+ the genomes of a contig. In addition, it can have zero or more alternate
+ indexes. A relationship's index must specify only fields in
  the relationship.
- The indexes for an entity must be listed inside the B<Indexes> tag. The from-index
+ The alternate indexes for an entity or relationship must be listed inside the B<Indexes> tag.
- of a relationship is specified using the B<FromIndex> tag; the to-index is specified
+ The from-index of a relationship is specified using the B<FromIndex> tag; the to-index is
- using the B<ToIndex> tag.
+ specified using the B<ToIndex> tag.
  Each index can contain a B<Notes> tag. In addition, it will have an B<IndexFields>
  tag containing the B<IndexField> tags. These specify, in order, the fields used in
 Line 303
  A relationship is described by the C<Relationship> tag. Within a relationship,
  there can be a C<Notes> tag, a C<Fields> tag containing the intersection data
- fields, a C<FromIndex> tag containing the from-index, and a C<ToIndex> tag containing
+ fields, a C<FromIndex> tag containing the from-index, a C<ToIndex> tag containing
- the to-index.
+ the to-index, and an C<Indexes> tag containing the alternate indexes.
  The C<Relationship> tag has the following attributes.
 Line 372
                   'medium-string' =>
                               { sqlType => 'VARCHAR(160)',       maxLen => 160,          avgLen =>  40, sort => "",
                                 indexMod =>   0, notes => "character string, 0 to 160 characters"},
+                  'long-string' =>
+                              { sqlType => 'VARCHAR(500)',       maxLen => 500,          avglen => 255, sort => "",
+                                indexMod =>   0, notes => "character string, 0 to 500 characters"},
                  );
  # Table translating arities into natural language.
-Line 654
+Line 657
      return Data::Dumper::Dumper($self->{_metaData});
  }
+ =head3 CreatePPO
+ C<< ERDB::CreatePPO($erdbXMLFile, $ppoXMLFile); >>
+ Create a PPO XML file from an ERDB data definition XML file. At the
+ current time, the PPO XML file can be used to create a database with
+ similar functionality. Eventually, the PPO will be able to use the
+ created XML to access the live ERDB database.
+ =over 4
+ =item erdbXMLFile
+ Name of the XML data definition file for the ERDB database. This
+ file must exist.
+ =item ppoXMLFile
+ Output file for the PPO XML definition. If this file exists, it
+ will be overwritten.
+ =back
+ =cut
+ sub CreatePPO {
+     # Get the parameters.
+     my ($erdbXMLFile, $ppoXMLFile) = @_;
+     # First, we want to slurp in the ERDB XML file in its raw form.
+     my $xml = ReadMetaXML($erdbXMLFile);
+     # Create a variable to hold all of the objects in the PPO project.
+     my @objects = ();
+     # Get the relationship hash.
+     my $relationships = $xml->{Relationships};
+     # Loop through the entities.
+     my $entities = $xml->{Entities};
+     for my $entityName (keys %{$entities}) {
+         # Get the entity's data structures.
+         my $entityObject = $entities->{$entityName};
+         # We put the object's fields in here, according to their type.
+         my (@object_refs, @scalars, @indexes, @arrays);
+         # Create the ID field for the entity. We get the key type from the
+         # entity object and compute the corresponding SQL type.
+         my $type = $TypeTable{$entityObject->{keyType}}->{sqlType};
+         push @scalars, { label => 'id', type => $type };
+         # Loop through the entity fields.
+         for my $fieldName ( keys %{$entityObject->{Fields}} ) {
+             # Get the field object.
+             my $fieldObject = $entityObject->{Fields}->{$fieldName};
+             # Convert it to a scalar tag.
+             my $scalar = _CreatePPOField($fieldName, $fieldObject);
+             # If we have a relation, this field is stored in an array.
+             # otherwise, it is a scalar. The array tag has scalars
+             # stored as an XML array. In ERDB, there is only ever one,
+             # but PPO can have more.
+             my $relation = $fieldObject->{relation};
+             if ($relation) {
+                 push @arrays, { scalar => [$scalar] };
+             } else {
+                 push @scalars, $scalar;
+             }
+         }
+         # Loop through the relationships. If this entity is the to-entity
+         # on a relationship of 1M arity, then it is implemented as a PPO
+         # object reference.
+         for my $relationshipName (keys %{$relationships}) {
+             # Get the relationship data.
+             my $relationshipData = $relationships->{$relationshipName};
+             # If we have a from for this entity and an arity of 1M, we
+             # have an object reference.
+             if ($relationshipData->{to} eq $entityName &&
+                 $relationshipData->{arity} eq '1M') {
+                 # Build the object reference tag.
+                 push @object_refs, { label => $relationshipName,
+                                      type => $relationshipData->{from} };
+             }
+         }
+         # Create the indexes.
+         my $indexList = $entityObject->{Indexes};
+         push @indexes, map { _CreatePPOIndex($_) } @{$indexList};
+         # Build the object XML tree.
+         my $object = { label => $entityName,
+                        object_ref => \@object_refs,
+                        scalar => \@scalars,
+                        index => \@indexes,
+                        array => \@arrays
+                       };
+         # Push the object onto the objects list.
+         push @objects, $object;
+     }
+     # Loop through the relationships, searching for MMs. The 1Ms were
+     # already handled by the entity search above.
+     for my $relationshipName (keys %{$relationships}) {
+         # Get this relationship's object.
+         my $relationshipObject = $relationships->{$relationshipName};
+         # Only proceed if it's many-to-many.
+         if ($relationshipObject->{arity} eq 'MM') {
+             # Create the tag lists for the relationship object.
+             my (@object_refs, @scalars, @indexes);
+             # The relationship will be created as an object with object
+             # references for its links to the participating entities.
+             my %links = ( from_link => $relationshipObject->{from},
+                           to_link => $relationshipObject->{to} );
+             for my $link (keys %links) {
+                 # Create an object_ref tag for this piece of the
+                 # relationship (from or to).
+                 my $object_ref = { label => $link,
+                                    type => $links{$link} };
+                 push @object_refs, $object_ref;
+             }
+             # Loop through the intersection data fields, creating scalar tags.
+             # There are no fancy array tags in a relationship.
+             for my $fieldName (keys %{$relationshipObject->{Fields}}) {
+                 my $fieldObject = $relationshipObject->{Fields}->{$fieldName};
+                 push @scalars, _CreatePPOField($fieldName, $fieldObject);
+             }
+             # Finally, the indexes: currently we cannot support the to-index and
+             # from-index in PPO, so we just process the alternate indexes.
+             my $indexList = $relationshipObject->{Indexes};
+             push @indexes, map { _CreatePPOIndex($_) } @{$indexList};
+             # Wrap up all the stuff about this relationship.
+             my $object = { label => $relationshipName,
+                            scalar => \@scalars,
+                            object_ref => \@object_refs,
+                            index => \@indexes
+                          };
+             # Push it into the object list.
+             push @objects, $object;
+         }
+     }
+     # Compute a title.
+     my $title;
+     if ($erdbXMLFile =~ /(\/|^)([^\/]+)DBD\.xml/) {
+         # Here we have a standard file name we can use for a title.
+         $title = $2;
+     } else {
+         # Here the file name is non-standard, so we carve up the
+         # database title.
+         $title = $xml->{Title}->{content};
+         $title =~ s/\s\.,//g;
+     }
+     # Wrap up the XML as a project.
+     my $ppoXML = { project => { label => $title,
+                                 object => \@objects }};
+     # Write out the results.
+     my $ppoString = XML::Simple::XMLout($ppoXML,
+                                         AttrIndent => 1,
+                                         KeepRoot => 1);
+     Tracer::PutFile($ppoXMLFile, [ $ppoString ]);
+ }
  =head3 FindIndexForEntity
  C<< my $indexFound = ERDB::FindIndexForEntity($xml, $entityName, $attributeName); >>
-Line 743
+Line 897
      # Loop through the relations.
      for my $relationName (@relNames) {
          # Create a table for this relation.
-         $self->CreateTable($relationName);
+         $self->CreateTable($relationName, 1);
          Trace("Relation $relationName created.") if T(2);
      }
  }
-Line 863
+Line 1017
              my $oldString = $fieldList->[$i];
              if (length($oldString) > $maxLen) {
                  # Here it's too big, so we truncate it.
-                 Trace("Truncating field $i in relation $relName to $maxLen characters from \"$oldString\".") if T(1);
+                 Trace("Truncating field $i ($fieldTypes->[$i]->{name}) in relation $relName to $maxLen characters from \"$oldString\".") if T(1);
                  $fieldList->[$i] = substr $oldString, 0, $maxLen;
                  $retVal++;
              }
-Line 1845
+Line 1999
          for my $dir ('from', 'to') {
              if ($structure->{$dir} eq $originEntityName) {
                  # Delete all relationship instances on this side of the entity instance.
+                 Trace("Disconnecting in $dir direction with ID \"$originEntityID\".");
                  $dbh->SQL("DELETE FROM $relationshipName WHERE ${dir}_link = ?", 0, $originEntityID);
                  $found = 1;
              }
-Line 1911
+Line 2066
      $dbh->SQL($command, undef, @parms);
  }
+ =head3 DeleteLike
+ C<< my $deleteCount = $erdb->DeleteLike($relName, $filter, \@parms); >>
+ Delete all the relationship rows that satisfy a particular filter condition. Unlike a normal
+ filter, only fields from the relationship itself can be used.
+ =over 4
+ =item relName
+ Name of the relationship whose records are to be deleted.
+ =item filter
+ A filter clause (L</Get>-style) for the delete query.
+ =item parms
+ Reference to a list of parameters for the filter clause.
+ =item RETURN
+ Returns a count of the number of rows deleted.
+ =back
+ =cut
+ sub DeleteLike {
+     # Get the parameters.
+     my ($self, $objectName, $filter, $parms) = @_;
+     # Declare the return variable.
+     my $retVal;
+     # Insure the parms argument is an array reference if the caller left it off.
+     if (! defined($parms)) {
+         $parms = [];
+     }
+     # Insure we have a relationship. The main reason for this is if we delete an entity
+     # instance we have to yank out a bunch of other stuff with it.
+     if ($self->IsEntity($objectName)) {
+         Confess("Cannot use DeleteLike on $objectName, because it is not a relationship.");
+     } else {
+         # Create the SQL command suffix to get the desierd records.
+         my ($suffix) = $self->_SetupSQL([$objectName], $filter);
+         # Convert it to a DELETE command.
+         my $command = "DELETE $suffix";
+         # Execute the command.
+         my $dbh = $self->{_dbh};
+         my $result = $dbh->SQL($command, 0, @{$parms});
+         # Check the results. Note we convert the "0D0" result to a real zero.
+         # A failure causes an abnormal termination, so the caller isn't going to
+         # worry about it.
+         if (! defined $result) {
+             Confess("Error deleting from $objectName: " . $dbh->errstr());
+         } elsif ($result == 0) {
+             $retVal = 0;
+         } else {
+             $retVal = $result;
+         }
+     }
+     # Return the result count.
+     return $retVal;
+ }
  =head3 SortNeeded
  C<< my $parms = $erdb->SortNeeded($relationName); >>
-Line 2051
+Line 2271
  =item RETURN
- Returns a list of B<DBObject>s that satisfy the query conditions.
+ Returns a list of B<ERDBObject>s that satisfy the query conditions.
  =back
-Line 2432
+Line 2652
                  if (!$retVal) {
                      my $errorString = $sth->errstr();
                      Confess("Error inserting into $relationName: $errorString");
+                 } else {
+                     Trace("Insert successful using $parameterList[0].") if T(3);
                  }
              }
          }
-Line 2547
+Line 2769
          # leave extra room. We postulate a minimum row count of 1000 to
          # prevent problems with incoming empty load files.
          my $rowSize = $self->EstimateRowSize($relationName);
-         my $estimate = FIG::max($fileSize * 1.5 / $rowSize, 1000);
+         my $estimate = $fileSize * 1.5 / $rowSize;
+         if ($estimate < 1000) {
+             $estimate = 1000;
+         }
          # Re-create the table without its index.
          $self->CreateTable($relationName, 0, $estimate);
          # If this is a pre-index DBMS, create the index here.
-Line 2769
+Line 2994
  =item RETURN
- Returns a B<DBObject> representing the desired entity instance, or an undefined value if no
+ Returns a B<ERDBObject> representing the desired entity instance, or an undefined value if no
  instance is found with the specified key.
  =back
-Line 2902
+Line 3127
  fields specified returns multiple values, they are flattened in with the rest. For
  example, the following call will return a list of the features in a particular
  spreadsheet cell, and each feature will be represented by a list containing the
- feature ID followed by all of its aliases.
+ feature ID followed by all of its essentiality determinations.
- C<< @query = $erdb->Get(['ContainsFeature', 'Feature'], "ContainsFeature(from-link) = ?", [$ssCellID], ['Feature(id)', 'Feature(alias)']); >>
+ C<< @query = $erdb->Get(['ContainsFeature', 'Feature'], "ContainsFeature(from-link) = ?", [$ssCellID], ['Feature(id)', 'Feature(essential)']); >>
  =over 4
-Line 3332
+Line 3557
      return $retVal;
  }
+ =head3 BeginTran
+ C<< $erdb->BeginTran(); >>
+ Start a database transaction.
+ =cut
+ sub BeginTran {
+     my ($self) = @_;
+     $self->{_dbh}->begin_tran();
+ }
+ =head3 CommitTran
+ C<< $erdb->CommitTran(); >>
+ Commit an active database transaction.
+ =cut
+ sub CommitTran {
+     my ($self) = @_;
+     $self->{_dbh}->commit_tran();
+ }
+ =head3 RollbackTran
+ C<< $erdb->RollbackTran(); >>
+ Roll back an active database transaction.
+ =cut
+ sub RollbackTran {
+     my ($self) = @_;
+     $self->{_dbh}->roll_tran();
+ }
+ =head3 UpdateField
+ C<< my $count = $erdb->UpdateField($objectNames, $fieldName, $oldValue, $newValue, $filter, $parms); >>
+ Update all occurrences of a specific field value to a new value. The number of rows changed will be
+ returned.
+ =over 4
+ =item fieldName
+ Name of the field in standard I<objectName>C<(>I<fieldName>C<)> format.
+ =item oldValue
+ Value to be modified. All occurrences of this value in the named field will be replaced by the
+ new value.
+ =item newValue
+ New value to be substituted for the old value when it's found.
+ =item filter
+ A standard ERDB filter clause (see L</Get>). The filter will be applied before any substitutions take place.
+ =item parms
+ Reference to a list of parameter values in the filter.
+ =item RETURN
+ Returns the number of rows modified.
+ =back
+ =cut
+ sub UpdateField {
+     # Get the parameters.
+     my ($self, $fieldName, $oldValue, $newValue, $filter, $parms) = @_;
+     # Get the object and field names from the field name parameter.
+     $fieldName =~ /^([^(]+)\(([^)]+)\)/;
+     my $objectName = $1;
+     my $realFieldName = _FixName($2);
+     # Add the old value to the filter. Note we allow the possibility that no
+     # filter was specified.
+     my $realFilter = "$fieldName = ?";
+     if ($filter) {
+         $realFilter .= " AND $filter";
+     }
+     # Format the query filter.
+     my ($suffix, $mappedNameListRef, $mappedNameHashRef) =
+         $self->_SetupSQL([$objectName], $realFilter);
+     # Create the query. Since there is only one object name, the mapped-name data is not
+     # necessary. Neither is the FROM clause.
+     $suffix =~ s/^FROM.+WHERE\s+//;
+     # Create the update statement.
+     my $command = "UPDATE $objectName SET $realFieldName = ? WHERE $suffix";
+     # Get the database handle.
+     my $dbh = $self->{_dbh};
+     # Add the old and new values to the parameter list. Note we allow the possibility that
+     # there are no user-supplied parameters.
+     my @params = ($newValue, $oldValue);
+     if (defined $parms) {
+         push @params, @{$parms};
+     }
+     # Execute the update.
+     my $retVal = $dbh->SQL($command, 0, @params);
+     # Make the funky zero a real zero.
+     if ($retVal == 0) {
+         $retVal = 0;
+     }
+     # Return the result.
+     return $retVal;
+ }
  =head2 Data Mining Methods
-Line 3488
+Line 3830
  =head2 Virtual Methods
+ =head3 _CreatePPOIndex
+ C<< my $index = ERDB::_CreatePPOIndex($indexObject); >>
+ Convert the XML for an ERDB index to the XML structure for a PPO
+ index.
+ =over 4
+ ERDB XML structure for an index.
+ =item RETURN
+ PPO XML structure for the same index.
+ =back
+ =cut
+ sub _CreatePPOIndex {
+     # Get the parameters.
+     my ($indexObject) = @_;
+     # The incoming index contains a list of the index fields in the IndexFields
+     # member. We loop through it to create the index tags.
+     my @fields = map { { label => _FixName($_->{name}) } } @{$indexObject->{IndexFields}};
+     # Wrap the fields in attribute tags.
+     my $retVal = { attribute => \@fields };
+     # Return the result.
+     return $retVal;
+ }
+ =head3 _CreatePPOField
+ C<< my $fieldXML = ERDB::_CreatePPOField($fieldName, $fieldObject); >>
+ Convert the ERDB XML structure for a field to a PPO scalar XML structure.
+ =over 4
+ =item fieldName
+ Name of the scalar field.
+ =item fieldObject
+ ERDB XML structure describing the field.
+ =item RETURN
+ Returns a PPO XML structure for the same field.
+ =back
+ =cut
+ sub _CreatePPOField {
+     # Get the parameters.
+     my ($fieldName, $fieldObject) = @_;
+     # Get the field type.
+     my $type = $TypeTable{$fieldObject->{type}}->{sqlType};
+     # Fix up the field name.
+     $fieldName = _FixName($fieldName);
+     # Build the scalar tag.
+     my $retVal = { label => $fieldName, type => $type };
+     # Return the result.
+     return $retVal;
+ }
  =head3 CleanKeywords
  C<< my $cleanedString = $erdb->CleanKeywords($searchExpression); >>
-Line 3539
+Line 3949
  C<< my @relationMap = _RelationMap($mappedNameHashRef, $mappedNameListRef); >>
- Create the relation map for an SQL query. The relation map is used by B<DBObject>
+ Create the relation map for an SQL query. The relation map is used by B<ERDBObject>
  to determine how to interpret the results of the query.
  =over 4
-Line 3556
+Line 3966
  =item RETURN
  Returns a list of 2-tuples. Each tuple consists of an object name as used in the
- query followed by the actual name of that object. This enables the B<DBObject> to
+ query followed by the actual name of that object. This enables the B<ERDBObject> to
  determine the order of the tables in the query and which object name belongs to each
  mapped object name. Most of the time these two values are the same; however, if a
  relation occurs twice in the query, the relation name in the field list and WHERE
-Line 4099
+Line 4509
  =head3 _LoadMetaData
+ C<< my $metadata = ERDB::_LoadMetaData($filename); >>
  This method loads the data describing this database from an XML file into a metadata structure.
  The resulting structure is a set of nested hash tables containing all the information needed to
  load or use the database. The schema for the XML file is F<ERDatabase.xml>.
-Line 4248
+Line 4660
              if ($found == 0) {
                  push @{$indexList}, { IndexFields => [ {name => 'id', order => 'ascending'} ] };
              }
-             # Now we need to convert the relation's index list to an index table. We begin by creating
+             # Attach all the indexes to the relation.
-             # an empty table in the relation structure.
+             _ProcessIndexes($indexList, $relation);
-             $relation->{Indexes} = { };
-             # Loop through the indexes.
-             my $count = 0;
-             for my $index (@{$indexList}) {
-                 # Add this index to the index table.
-                 _AddIndex("idx$count", $relation, $index);
-                 # Increment the counter so that the next index has a different name.
-                 $count++;
-             }
          }
          # Finally, we add the relation structure to the entity.
          $entityStructure->{Relations} = $relationTable;
-Line 4294
+Line 4697
          my $thisRelation = { Fields => _ReOrderRelationTable($relationshipStructure->{Fields}),
                               Indexes => { } };
          $relationshipStructure->{Relations} = { $relationshipName => $thisRelation };
+         # Add the alternate indexes (if any). This MUST be done before the FROM and
+         # TO indexes, because it erases the relation's index list.
+         if (exists $relationshipStructure->{Indexes}) {
+             _ProcessIndexes($relationshipStructure->{Indexes}, $thisRelation);
+         }
+         # Add the relation to the master table.
          # Create the FROM and TO indexes.
          _CreateRelationshipIndex("From", $relationshipName, $relationshipStructure);
          _CreateRelationshipIndex("To", $relationshipName, $relationshipStructure);
-         # Add the relation to the master table.
          $masterRelationTable{$relationshipName} = $thisRelation;
      }
      # Now store the master relation table in the metadata structure.
-Line 4456
+Line 4865
      _AddIndex("idx$indexKey", $relationStructure, $newIndex);
  }
+ =head3 _ProcessIndexes
+ C<< ERDB::_ProcessIndexes($indexList, $relation); >>
+ Build the data structures for the specified indexes in the specified relation.
+ =over 4
+ =item indexList
+ Reference to a list of indexes. Each index is a hash reference containing an optional
+ C<Notes> value that describes the index and an C<IndexFields> value that is a reference
+ to a list of index field structures. An index field structure, in turn, is a reference
+ to a hash that contains a C<name> attribute for the field name and an C<order>
+ attribute that specifies either C<ascending> or C<descending>. In this sense the
+ index list encapsulates the XML C<Indexes> structure in the database definition.
+ =item relation
+ The structure that describes the current relation. The new index descriptors will
+ be stored in the structure's C<Indexes> member. Any previous data in the structure
+ will be lost.
+ =back
+ =cut
+ sub _ProcessIndexes {
+     # Get the parameters.
+     my ($indexList, $relation) = @_;
+     # Now we need to convert the relation's index list to an index table. We begin by creating
+     # an empty table in the relation structure.
+     $relation->{Indexes} = { };
+     # Loop through the indexes.
+     my $count = 0;
+     for my $index (@{$indexList}) {
+         # Add this index to the index table.
+         _AddIndex("idx$count", $relation, $index);
+         # Increment the counter so that the next index has a different name.
+         $count++;
+     }
+ }
  =head3 _AddIndex
  Add an index to a relation structure.

 Legend:



Removed from v.1.83
 


changed lines


 
Added in v.1.92
 Legend:



Removed from v.1.83
 


changed lines


 
Added in v.1.92
-Removed from v.1.83
+Added in v.1.92

MCS Webmaster	ViewVC Help
Powered by ViewVC 1.0.3