[Bio] / Sprout / ERDB.pm Repository:
ViewVC logotype

Diff of /Sprout/ERDB.pm

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

revision 1.92, Mon Jun 11 18:51:23 2007 UTC revision 1.105, Tue Sep 23 20:43:42 2008 UTC
# Line 11  Line 11 
11      use Time::HiRes qw(gettimeofday);      use Time::HiRes qw(gettimeofday);
12      use Digest::MD5 qw(md5_base64);      use Digest::MD5 qw(md5_base64);
13      use CGI;      use CGI;
14        use WikiTools;
15    
16  =head1 Entity-Relationship Database Package  =head1 Entity-Relationship Database Package
17    
# Line 101  Line 102 
102    
103  =item text  =item text
104    
105  long string; Text fields cannot be used in indexes or sorting and do not support the  long string; Text fields do not support the normal syntax of filter clauses,
106  normal syntax of filter clauses, but can be up to a billion character in length  but can be up to a billion character in length
107    
108    =item dna
109    
110    long string, used to store DNA and protein sequences
111    
112    =item image
113    
114    long string, used to store encoded image data
115    
116  =item float  =item float
117    
# Line 347  Line 356 
356                                 indexMod =>   0, notes => "signed 32-bit integer"},                                 indexMod =>   0, notes => "signed 32-bit integer"},
357                    counter => { sqlType => 'INTEGER UNSIGNED',   maxLen => 20,           avgLen =>   4, sort => "n",                    counter => { sqlType => 'INTEGER UNSIGNED',   maxLen => 20,           avgLen =>   4, sort => "n",
358                                 indexMod =>   0, notes => "unsigned 32-bit integer"},                                 indexMod =>   0, notes => "unsigned 32-bit integer"},
359                      image =>    { sqlType => 'TEXT',               maxLen => 1000000000,   avgLen => 100000, sort => "",
360                                   indexMod => 255, notes => "UUencoded image, suitable for import into GD, should never be indexed"},
361                      dna =>      { sqlType => 'TEXT',               maxLen => 1000000000,   avgLen => 100000, sort => "",
362                                   indexMod => 255, notes => "DNA or protein sequence, should never be indexed"},
363                    string =>  { sqlType => 'VARCHAR(255)',       maxLen => 255,          avgLen => 100, sort => "",                    string =>  { sqlType => 'VARCHAR(255)',       maxLen => 255,          avgLen => 100, sort => "",
364                                 indexMod =>   0, notes => "character string, 0 to 255 characters"},                                 indexMod =>   0, notes => "character string, 0 to 255 characters"},
365                    text =>    { sqlType => 'TEXT',               maxLen => 1000000000,   avgLen => 500, sort => "",                    text =>    { sqlType => 'TEXT',               maxLen => 1000000000,   avgLen => 500, sort => "",
# Line 389  Line 402 
402                                    Entities => 'Entity',                                    Entities => 'Entity',
403                                    Fields => 'Field',                                    Fields => 'Field',
404                                    Indexes => 'Index',                                    Indexes => 'Index',
405                                    IndexFields => 'IndexField'                                    IndexFields => 'IndexField',
406                                      Issues => 'Issue',
407                                      Shapes => 'Shape'
408                                  },                                  },
409                    KeyAttr =>    { Relationship => 'name',                    KeyAttr =>    { Relationship => 'name',
410                                    Entity => 'name',                                    Entity => 'name',
411                                    Field => 'name'                                    Field => 'name',
412                                      Shape => 'name'
413                                  },                                  },
414                    SuppressEmpty => 1,                    SuppressEmpty => 1,
415                   );                   );
416    
417  my %XmlInOpts  = (  my %XmlInOpts  = (
418                    ForceArray => ['Field', 'Index', 'IndexField', 'Relationship', 'Entity'],                    ForceArray => [qw(Field Index IndexField Relationship Entity Shape)],
419                    ForceContent => 1,                    ForceContent => 1,
420                    NormalizeSpace => 2,                    NormalizeSpace => 2,
421                   );                   );
# Line 408  Line 424 
424                    XMLDecl => 1,                    XMLDecl => 1,
425                   );                   );
426    
   
427  =head2 Public Methods  =head2 Public Methods
428    
429  =head3 new  =head3 new
430    
431  C<< my $database = ERDB->new($dbh, $metaFileName); >>      my $database = ERDB->new($dbh, $metaFileName);
432    
433  Create a new ERDB object.  Create a new ERDB object.
434    
# Line 433  Line 448 
448    
449  sub new {  sub new {
450      # Get the parameters.      # Get the parameters.
451      my ($class, $dbh, $metaFileName, $options) = @_;      my ($class, $dbh, $metaFileName, %options) = @_;
452      # Load the meta-data.      # Load the meta-data.
453      my $metaData = _LoadMetaData($metaFileName);      my $metaData = _LoadMetaData($metaFileName);
454      # Create the object.      # Create the object.
# Line 447  Line 462 
462    
463  =head3 ShowMetaData  =head3 ShowMetaData
464    
465  C<< $erdb->ShowMetaData($fileName); >>      $erdb->ShowMetaData($fileName);
466    
467  This method outputs a description of the database. This description can be used to help users create  This method outputs a description of the database. This description can be used to help users create
468  the data to be loaded into the relations.  the data to be loaded into the relations.
# Line 488  Line 503 
503    
504  =head3 DisplayMetaData  =head3 DisplayMetaData
505    
506  C<< my $html = $erdb->DisplayMetaData(); >>      my $html = $erdb->DisplayMetaData();
507    
508  Return an HTML description of the database. This description can be used to help users create  Return an HTML description of the database. This description can be used to help users create
509  the data to be loaded into the relations and form queries. The output is raw includable HTML  the data to be loaded into the relations and form queries. The output is raw includable HTML
# Line 644  Line 659 
659    
660  =head3 DumpMetaData  =head3 DumpMetaData
661    
662  C<< $erdb->DumpMetaData(); >>      $erdb->DumpMetaData();
663    
664  Return a dump of the metadata structure.  Return a dump of the metadata structure.
665    
# Line 657  Line 672 
672      return Data::Dumper::Dumper($self->{_metaData});      return Data::Dumper::Dumper($self->{_metaData});
673  }  }
674    
675    =head3 GenerateWikiData
676    
677        my @wikiLines = $erdb->GenerateWikiData();
678    
679    Build a description of the database for the wiki. The database will be
680    organized into a single page, with sections for each entity and relationship.
681    The return value is a list of text lines.
682    
683    =cut
684    
685    sub GenerateWikiData {
686        # Get the parameters.
687        my ($self) = @_;
688        # We'll build the wiki text in here.
689        my @retVal = ();
690        # Get the metadata object.
691        my $metadata = $self->{_metaData};
692        # Get the title string. This will become the page name.
693        my $title = $metadata->{Title}->{content};
694        # Get the entity and relationship lists.
695        my $entityList = $metadata->{Entities};
696        my $relationshipList = $metadata->{Relationships};
697        my $shapeList = $metadata->{Shapes};
698        # Start with the introductory text.
699        push @retVal, WikiTools::Heading(2, "Introduction");
700        if (my $notes = $metadata->{Notes}) {
701            push @retVal, WikiNote($notes->{content});
702        }
703        # Generate issue list.
704        if (my $issues = $metadata->{Issues}) {
705            push @retVal, WikiTools::Heading(3, 'Issues');
706            push @retVal, WikiTools::List(map { $_->{content} } @{$issues});
707        }
708        # Start the entity section.
709        push @retVal, WikiTools::Heading(2, "Entities");
710        # Loop through the entities. Note that unlike the situation with HTML, we
711        # don't need to generate the table of contents manually, just the data
712        # itself.
713        for my $key (sort keys %$entityList) {
714            # Create a header for this entity.
715            push @retVal, "", WikiTools::Heading(3, $key);
716            # Get the entity data.
717            my $entityData = $entityList->{$key};
718            # Plant the notes here, if there are any.
719            push @retVal, _ObjectNotes($entityData);
720            # Now we list the entity's relationships (if any). First, we build a list
721            # of the relationships relevant to this entity.
722            my @rels = ();
723            for my $rel (sort keys %$relationshipList) {
724                my $relStructure = $relationshipList->{$rel};
725                if ($relStructure->{from} eq $key || $relStructure->{to} eq $key) {
726                    # Get the relationship sentence.
727                    my $relSentence = _ComputeRelationshipSentence($rel, $relStructure);
728                    # Linkify it.
729                    my $linkedRel = WikiTools::LinkMarkup("#$rel", $rel);
730                    $relSentence =~ s/$rel/$linkedRel/;
731                    push @rels, $relSentence;
732                }
733            }
734            # Add the relationships as a Wiki list.
735            push @retVal, WikiTools::List(@rels);
736            # Get the entity's relations.
737            my $relationList = $entityData->{Relations};
738            # Loop through the relations, displaying them.
739            for my $relation (sort keys %{$relationList}) {
740                my $wikiString = _WikiRelationTable($relation, $relationList->{$relation});
741                push @retVal, $wikiString;
742            }
743        }
744        # Now the entities are documented. Next we do the relationships.
745        push @retVal, WikiTools::Heading(2, "Relationships");
746        for my $key (sort keys %$relationshipList) {
747            my $relationshipData = $relationshipList->{$key};
748            # Create the relationship heading.
749            push @retVal, WikiTools::Heading(3, $key);
750            # Describe the relationship arity. Note there's a bit of trickiness involving recursive
751            # many-to-many relationships. In a normal many-to-many we use two sentences to describe
752            # the arity (one for each direction). This is a bad idea for a recursive relationship,
753            # since both sentences will say the same thing.
754            my $arity = $relationshipData->{arity};
755            my $fromEntity = $relationshipData->{from};
756            my $toEntity = $relationshipData->{to};
757            my @listElements = ();
758            my $boldCode = WikiTools::BoldCode();
759            if ($arity eq "11") {
760                push @listElements, "Each $boldCode$fromEntity$boldCode relates to at most one $boldCode$toEntity$boldCode.";
761            } else {
762                push @listElements, "Each $boldCode$fromEntity$boldCode relates to multiple $boldCode${toEntity}s$boldCode.";
763                if ($arity eq "MM" && $fromEntity ne $toEntity) {
764                    push @listElements, "Each $boldCode$toEntity$boldCode relates to multiple $boldCode${fromEntity}s$boldCode.";
765                }
766            }
767            push @retVal, WikiTools::List(@listElements);
768            # Plant the notes here, if there are any.
769            push @retVal, _ObjectNotes($relationshipData);
770            # Finally, the relationship table.
771            my $wikiString = _WikiRelationTable($key, $relationshipData->{Relations}->{$key});
772            push @retVal, $wikiString;
773        }
774        # Now loop through the miscellaneous shapes.
775        if ($shapeList) {
776            push @retVal, WikiTools::Heading(2, "Miscellaneous");
777            for my $shape (sort keys %$shapeList) {
778                push @retVal, WikiTools::Heading(3, $shape);
779                my $shapeData = $shapeList->{$shape};
780                push @retVal, _ObjectNotes($shapeData);
781            }
782        }
783        # All done. Return the lines.
784        return @retVal;
785    }
786    
787    
788  =head3 CreatePPO  =head3 CreatePPO
789    
790  C<< ERDB::CreatePPO($erdbXMLFile, $ppoXMLFile); >>      ERDB::CreatePPO($erdbXMLFile, $ppoXMLFile);
791    
792  Create a PPO XML file from an ERDB data definition XML file. At the  Create a PPO XML file from an ERDB data definition XML file. At the
793  current time, the PPO XML file can be used to create a database with  current time, the PPO XML file can be used to create a database with
# Line 810  Line 938 
938    
939  =head3 FindIndexForEntity  =head3 FindIndexForEntity
940    
941  C<< my $indexFound = ERDB::FindIndexForEntity($xml, $entityName, $attributeName); >>      my $indexFound = ERDB::FindIndexForEntity($xml, $entityName, $attributeName);
942    
943  This method locates the entry in an entity's index list that begins with the  This method locates the entry in an entity's index list that begins with the
944  specified attribute name. If the entity has no index list, one will be  specified attribute name. If the entity has no index list, one will be
# Line 880  Line 1008 
1008    
1009  =head3 CreateTables  =head3 CreateTables
1010    
1011  C<< $erdb->CreateTables(); >>      $erdb->CreateTables();
1012    
1013  This method creates the tables for the database from the metadata structure loaded by the  This method creates the tables for the database from the metadata structure loaded by the
1014  constructor. It is expected this function will only be used on rare occasions, when the  constructor. It is expected this function will only be used on rare occasions, when the
# Line 904  Line 1032 
1032    
1033  =head3 CreateTable  =head3 CreateTable
1034    
1035  C<< $erdb->CreateTable($tableName, $indexFlag, $estimatedRows); >>      $erdb->CreateTable($tableName, $indexFlag, $estimatedRows);
1036    
1037  Create the table for a relation and optionally create its indexes.  Create the table for a relation and optionally create its indexes.
1038    
# Line 946  Line 1074 
1074          # Push the result into the field list.          # Push the result into the field list.
1075          push @fieldList, $fieldString;          push @fieldList, $fieldString;
1076      }      }
     # If this is a root table, add the "new_record" flag. It defaults to 0, so  
     if ($rootFlag) {  
         push @fieldList, "new_record $TypeTable{boolean}->{sqlType} NOT NULL DEFAULT 0";  
     }  
1077      # Convert the field list into a comma-delimited string.      # Convert the field list into a comma-delimited string.
1078      my $fieldThing = join(', ', @fieldList);      my $fieldThing = join(', ', @fieldList);
1079      # Insure the table is not already there.      # Insure the table is not already there.
# Line 960  Line 1084 
1084      my $estimation = undef;      my $estimation = undef;
1085      if ($estimatedRows) {      if ($estimatedRows) {
1086          $estimation = [$self->EstimateRowSize($relationName), $estimatedRows];          $estimation = [$self->EstimateRowSize($relationName), $estimatedRows];
1087            Trace("$estimation->[1] rows of $estimation->[0] bytes each.") if T(3);
1088      }      }
1089      # Create the table.      # Create the table.
1090      Trace("Creating table $relationName: $fieldThing") if T(2);      Trace("Creating table $relationName: $fieldThing") if T(2);
# Line 974  Line 1099 
1099    
1100  =head3 VerifyFields  =head3 VerifyFields
1101    
1102  C<< my $count = $erdb->VerifyFields($relName, \@fieldList); >>      my $count = $erdb->VerifyFields($relName, \@fieldList);
1103    
1104  Run through the list of proposed field values, insuring that all the character fields are  Run through the list of proposed field values, insuring that all the character fields are
1105  below the maximum length. If any fields are too long, they will be truncated in place.  below the maximum length. If any fields are too long, they will be truncated in place.
# Line 1029  Line 1154 
1154    
1155  =head3 DigestFields  =head3 DigestFields
1156    
1157  C<< $erdb->DigestFields($relName, $fieldList); >>      $erdb->DigestFields($relName, $fieldList);
1158    
1159  Digest the strings in the field list that correspond to data type C<hash-string> in the  Digest the strings in the field list that correspond to data type C<hash-string> in the
1160  specified relation.  specified relation.
# Line 1069  Line 1194 
1194    
1195  =head3 DigestKey  =head3 DigestKey
1196    
1197  C<< my $digested = $erdb->DigestKey($keyValue); >>      my $digested = $erdb->DigestKey($keyValue);
1198    
1199  Return the digested value of a symbolic key. The digested value can then be plugged into a  Return the digested value of a symbolic key. The digested value can then be plugged into a
1200  key-based search into a table with key-type hash-string.  key-based search into a table with key-type hash-string.
# Line 1102  Line 1227 
1227    
1228  =head3 CreateIndex  =head3 CreateIndex
1229    
1230  C<< $erdb->CreateIndex($relationName); >>      $erdb->CreateIndex($relationName);
1231    
1232  Create the indexes for a relation. If a table is being loaded from a large source file (as  Create the indexes for a relation. If a table is being loaded from a large source file (as
1233  is the case in L</LoadTable>), it is sometimes best to create the indexes after the load.  is the case in L</LoadTable>), it is sometimes best to create the indexes after the load.
# Line 1158  Line 1283 
1283    
1284  =head3 GetSecondaryFields  =head3 GetSecondaryFields
1285    
1286  C<< my %fieldTuples = $erdb->GetSecondaryFields($entityName); >>      my %fieldTuples = $erdb->GetSecondaryFields($entityName);
1287    
1288  This method will return a list of the name and type of each of the secondary  This method will return a list of the name and type of each of the secondary
1289  fields for a specified entity. Secondary fields are stored in two-column tables  fields for a specified entity. Secondary fields are stored in two-column tables
# Line 1199  Line 1324 
1324    
1325  =head3 GetFieldRelationName  =head3 GetFieldRelationName
1326    
1327  C<< my $name = $erdb->GetFieldRelationName($objectName, $fieldName); >>      my $name = $erdb->GetFieldRelationName($objectName, $fieldName);
1328    
1329  Return the name of the relation containing a specified field.  Return the name of the relation containing a specified field.
1330    
# Line 1240  Line 1365 
1365    
1366  =head3 DeleteValue  =head3 DeleteValue
1367    
1368  C<< my $numDeleted = $erdb->DeleteValue($entityName, $id, $fieldName, $fieldValue); >>      my $numDeleted = $erdb->DeleteValue($entityName, $id, $fieldName, $fieldValue);
1369    
1370  Delete secondary field values from the database. This method can be used to delete all  Delete secondary field values from the database. This method can be used to delete all
1371  values of a specified field for a particular entity instance, or only a single value.  values of a specified field for a particular entity instance, or only a single value.
# Line 1323  Line 1448 
1448    
1449  =head3 LoadTables  =head3 LoadTables
1450    
1451  C<< my $stats = $erdb->LoadTables($directoryName, $rebuild); >>      my $stats = $erdb->LoadTables($directoryName, $rebuild);
1452    
1453  This method will load the database tables from a directory. The tables must already have been created  This method will load the database tables from a directory. The tables must already have been created
1454  in the database. (This can be done by calling L</CreateTables>.) The caller passes in a directory name;  in the database. (This can be done by calling L</CreateTables>.) The caller passes in a directory name;
# Line 1383  Line 1508 
1508    
1509  =head3 GetTableNames  =head3 GetTableNames
1510    
1511  C<< my @names = $erdb->GetTableNames; >>      my @names = $erdb->GetTableNames;
1512    
1513  Return a list of the relations required to implement this database.  Return a list of the relations required to implement this database.
1514    
# Line 1400  Line 1525 
1525    
1526  =head3 GetEntityTypes  =head3 GetEntityTypes
1527    
1528  C<< my @names = $erdb->GetEntityTypes; >>      my @names = $erdb->GetEntityTypes;
1529    
1530  Return a list of the entity type names.  Return a list of the entity type names.
1531    
# Line 1415  Line 1540 
1540      return sort keys %{$entityList};      return sort keys %{$entityList};
1541  }  }
1542    
1543    
1544    =head3 GetConnectingRelationships
1545    
1546        my @list = $erdb->GetConnectingRelationships($entityName);
1547    
1548    Return a list of the relationships connected to the specified entity.
1549    
1550    =over 4
1551    
1552    =item entityName
1553    
1554    Entity whose connected relationships are desired.
1555    
1556    =item RETURN
1557    
1558    Returns a list of the relationships that originate from the entity.
1559    If the entity is on the from end, it will return the relationship
1560    name. If the entity is on the to end it will return the converse of
1561    the relationship name.
1562    
1563    =back
1564    
1565    =cut
1566    
1567    sub GetConnectingRelationships {
1568        # Get the parameters.
1569        my ($self, $entityName) = @_;
1570        # Declare the return variable.
1571        my @retVal;
1572        # Get the relationship list.
1573        my $relationships = $self->{_metaData}->{Relationships};
1574        # Find the entity.
1575        my $entity = $self->{_metaData}->{Entities}->{$entityName};
1576        # Only proceed if the entity exists.
1577        if (! defined $entity) {
1578            Trace("Entity $entityName not found.") if T(3);
1579        } else {
1580            # Loop through the relationships.
1581            my @rels = keys %$relationships;
1582            Trace(scalar(@rels) . " relationships found in connection search.") if T(3);
1583            for my $relationshipName (@rels) {
1584                my $relationship = $relationships->{$relationshipName};
1585                if ($relationship->{from} eq $entityName) {
1586                    # Here we have a forward relationship.
1587                    push @retVal, $relationshipName;
1588                } elsif ($relationship->{to} eq $entityName) {
1589                    # Here we have a backward relationship. In this case, the
1590                    # converse relationship name is preferred if it exists.
1591                    my $converse = $relationship->{converse} || $relationshipName;
1592                    push @retVal, $converse;
1593                }
1594            }
1595        }
1596        # Return the result.
1597        return @retVal;
1598    }
1599    
1600    
1601  =head3 GetDataTypes  =head3 GetDataTypes
1602    
1603  C<< my %types = ERDB::GetDataTypes(); >>      my %types = ERDB::GetDataTypes();
1604    
1605  Return a table of ERDB data types. The table returned is a hash of hashes.  Return a table of ERDB data types. The table returned is a hash of hashes.
1606  The keys of the big hash are the datatypes. Each smaller hash has several  The keys of the big hash are the datatypes. Each smaller hash has several
# Line 1436  Line 1619 
1619    
1620  =head3 IsEntity  =head3 IsEntity
1621    
1622  C<< my $flag = $erdb->IsEntity($entityName); >>      my $flag = $erdb->IsEntity($entityName);
1623    
1624  Return TRUE if the parameter is an entity name, else FALSE.  Return TRUE if the parameter is an entity name, else FALSE.
1625    
# Line 1463  Line 1646 
1646    
1647  =head3 Get  =head3 Get
1648    
1649  C<< my $query = $erdb->Get(\@objectNames, $filterClause, \@params); >>      my $query = $erdb->Get(\@objectNames, $filterClause, \@params);
1650    
1651  This method returns a query object for entities of a specified type using a specified filter.  This method returns a query object for entities of a specified type using a specified filter.
1652  The filter is a standard WHERE/ORDER BY clause with question marks as parameter markers and each  The filter is a standard WHERE/ORDER BY clause with question marks as parameter markers and each
# Line 1471  Line 1654 
1654  following call requests all B<Genome> objects for the genus specified in the variable  following call requests all B<Genome> objects for the genus specified in the variable
1655  $genus.  $genus.
1656    
1657  C<< $query = $erdb->Get(['Genome'], "Genome(genus) = ?", [$genus]); >>      $query = $erdb->Get(['Genome'], "Genome(genus) = ?", [$genus]);
1658    
1659  The WHERE clause contains a single question mark, so there is a single additional  The WHERE clause contains a single question mark, so there is a single additional
1660  parameter representing the parameter value. It would also be possible to code  parameter representing the parameter value. It would also be possible to code
1661    
1662  C<< $query = $erdb->Get(['Genome'], "Genome(genus) = \'$genus\'"); >>      $query = $erdb->Get(['Genome'], "Genome(genus) = \'$genus\'");
1663    
1664  however, this version of the call would generate a syntax error if there were any quote  however, this version of the call would generate a syntax error if there were any quote
1665  characters inside the variable C<$genus>.  characters inside the variable C<$genus>.
# Line 1488  Line 1671 
1671  It is possible to specify multiple entity and relationship names in order to retrieve more than  It is possible to specify multiple entity and relationship names in order to retrieve more than
1672  one object's data at the same time, which allows highly complex joined queries. For example,  one object's data at the same time, which allows highly complex joined queries. For example,
1673    
1674  C<< $query = $erdb->Get(['Genome', 'ComesFrom', 'Source'], "Genome(genus) = ?", [$genus]); >>      $query = $erdb->Get(['Genome', 'ComesFrom', 'Source'], "Genome(genus) = ?", [$genus]);
1675    
1676  If multiple names are specified, then the query processor will automatically determine a  If multiple names are specified, then the query processor will automatically determine a
1677  join path between the entities and relationships. The algorithm used is very simplistic.  join path between the entities and relationships. The algorithm used is very simplistic.
# Line 1524  Line 1707 
1707  with an ORDER BY clause. For example, the following filter string gets all genomes for a  with an ORDER BY clause. For example, the following filter string gets all genomes for a
1708  particular genus and sorts them by species name.  particular genus and sorts them by species name.
1709    
1710  C<< "Genome(genus) = ? ORDER BY Genome(species)" >>      "Genome(genus) = ? ORDER BY Genome(species)"
1711    
1712  Note that the case is important. Only an uppercase "ORDER BY" with a single space will  Note that the case is important. Only an uppercase "ORDER BY" with a single space will
1713  be processed. The idea is to make it less likely to find the verb by accident.  be processed. The idea is to make it less likely to find the verb by accident.
# Line 1537  Line 1720 
1720  be the last thing in the filter clause, and it contains only the word "LIMIT" followed by  be the last thing in the filter clause, and it contains only the word "LIMIT" followed by
1721  a positive number. So, for example  a positive number. So, for example
1722    
1723  C<< "Genome(genus) = ? ORDER BY Genome(species) LIMIT 10" >>      "Genome(genus) = ? ORDER BY Genome(species) LIMIT 10"
1724    
1725  will only return the first ten genomes for the specified genus. The ORDER BY clause is not  will only return the first ten genomes for the specified genus. The ORDER BY clause is not
1726  required. For example, to just get the first 10 genomes in the B<Genome> table, you could  required. For example, to just get the first 10 genomes in the B<Genome> table, you could
1727  use  use
1728    
1729  C<< "LIMIT 10" >>      "LIMIT 10"
1730    
1731  =item params  =item params
1732    
# Line 1564  Line 1747 
1747      my ($suffix, $mappedNameListRef, $mappedNameHashRef) =      my ($suffix, $mappedNameListRef, $mappedNameHashRef) =
1748          $self->_SetupSQL($objectNames, $filterClause);          $self->_SetupSQL($objectNames, $filterClause);
1749      # Create the query.      # Create the query.
1750      my $command = "SELECT DISTINCT " . join(".*, ", @{$mappedNameListRef}) .      my $command = "SELECT " . join(".*, ", @{$mappedNameListRef}) .
1751          ".* $suffix";          ".* $suffix";
1752      my $sth = $self->_GetStatementHandle($command, $params);      my $sth = $self->_GetStatementHandle($command, $params);
1753      # Now we create the relation map, which enables DBQuery to determine the order, name      # Now we create the relation map, which enables DBQuery to determine the order, name
# Line 1582  Line 1765 
1765    
1766  =head3 Search  =head3 Search
1767    
1768  C<< my $query = $erdb->Search($searchExpression, $idx, \@objectNames, $filterClause, \@params); >>      my $query = $erdb->Search($searchExpression, $idx, \@objectNames, $filterClause, \@params);
1769    
1770  Perform a full text search with filtering. The search will be against a specified object  Perform a full text search with filtering. The search will be against a specified object
1771  in the object name list. That object will get an extra field containing the search  in the object name list. That object will get an extra field containing the search
# Line 1665  Line 1848 
1848              $self->_SetupSQL($objectNames, $filterClause, $matchClause);              $self->_SetupSQL($objectNames, $filterClause, $matchClause);
1849          # Create the query. Note that the match clause is inserted at the front of          # Create the query. Note that the match clause is inserted at the front of
1850          # the select fields.          # the select fields.
1851          my $command = "SELECT DISTINCT $matchClause, " . join(".*, ", @{$mappedNameListRef}) .          my $command = "SELECT $matchClause, " . join(".*, ", @{$mappedNameListRef}) .
1852              ".* $suffix";              ".* $suffix";
1853          my $sth = $self->_GetStatementHandle($command, \@myParams);          my $sth = $self->_GetStatementHandle($command, \@myParams);
1854          # Now we create the relation map, which enables DBQuery to determine the order, name          # Now we create the relation map, which enables DBQuery to determine the order, name
# Line 1679  Line 1862 
1862    
1863  =head3 GetFlat  =head3 GetFlat
1864    
1865  C<< my @list = $erdb->GetFlat(\@objectNames, $filterClause, \@parameterList, $field); >>      my @list = $erdb->GetFlat(\@objectNames, $filterClause, \@parameterList, $field);
1866    
1867  This is a variation of L</GetAll> that asks for only a single field per record and  This is a variation of L</GetAll> that asks for only a single field per record and
1868  returns a single flattened list.  returns a single flattened list.
# Line 1732  Line 1915 
1915    
1916  =head3 SpecialFields  =head3 SpecialFields
1917    
1918  C<< my %specials = $erdb->SpecialFields($entityName); >>      my %specials = $erdb->SpecialFields($entityName);
1919    
1920  Return a hash mapping special fields in the specified entity to the value of their  Return a hash mapping special fields in the specified entity to the value of their
1921  C<special> attribute. This enables the subclass to get access to the special field  C<special> attribute. This enables the subclass to get access to the special field
# Line 1774  Line 1957 
1957    
1958  =head3 Delete  =head3 Delete
1959    
1960  C<< my $stats = $erdb->Delete($entityName, $objectID, %options); >>      my $stats = $erdb->Delete($entityName, $objectID, %options);
1961    
1962  Delete an entity instance from the database. The instance is deleted along with all entity and  Delete an entity instance from the database. The instance is deleted along with all entity and
1963  relationship instances dependent on it. The definition of I<dependence> is recursive.  relationship instances dependent on it. The definition of I<dependence> is recursive.
# Line 1959  Line 2142 
2142    
2143  =head3 Disconnect  =head3 Disconnect
2144    
2145  C<< $erdb->Disconnect($relationshipName, $originEntityName, $originEntityID); >>      $erdb->Disconnect($relationshipName, $originEntityName, $originEntityID);
2146    
2147  Disconnect an entity instance from all the objects to which it is related. This  Disconnect an entity instance from all the objects to which it is related. This
2148  will delete each relationship instance that connects to the specified entity.  will delete each relationship instance that connects to the specified entity.
# Line 1998  Line 2181 
2181          # Loop through the ends of the relationship.          # Loop through the ends of the relationship.
2182          for my $dir ('from', 'to') {          for my $dir ('from', 'to') {
2183              if ($structure->{$dir} eq $originEntityName) {              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);  
2184                  $found = 1;                  $found = 1;
2185                    # Here we want to delete all relationship instances on this side of the
2186                    # entity instance.
2187                    Trace("Disconnecting in $dir direction with ID \"$originEntityID\".");
2188                    # We do this delete in batches to keep it from dragging down the
2189                    # server.
2190                    my $limitClause = ($FIG_Config::delete_limit ? "LIMIT $FIG_Config::delete_limit" : "");
2191                    my $done = 0;
2192                    while (! $done) {
2193                        # Do the delete.
2194                        my $rows = $dbh->SQL("DELETE FROM $relationshipName WHERE ${dir}_link = ? $limitClause", 0, $originEntityID);
2195                        # See if we're done. We're done if no rows were found or the delete is unlimited.
2196                        $done = ($rows == 0 || ! $limitClause);
2197                    }
2198              }              }
2199          }          }
2200          # Insure we found the entity on at least one end.          # Insure we found the entity on at least one end.
# Line 2013  Line 2206 
2206    
2207  =head3 DeleteRow  =head3 DeleteRow
2208    
2209  C<< $erdb->DeleteRow($relationshipName, $fromLink, $toLink, \%values); >>      $erdb->DeleteRow($relationshipName, $fromLink, $toLink, \%values);
2210    
2211  Delete a row from a relationship. In most cases, only the from-link and to-link are  Delete a row from a relationship. In most cases, only the from-link and to-link are
2212  needed; however, for relationships with intersection data values can be specified  needed; however, for relationships with intersection data values can be specified
# Line 2068  Line 2261 
2261    
2262  =head3 DeleteLike  =head3 DeleteLike
2263    
2264  C<< my $deleteCount = $erdb->DeleteLike($relName, $filter, \@parms); >>      my $deleteCount = $erdb->DeleteLike($relName, $filter, \@parms);
2265    
2266  Delete all the relationship rows that satisfy a particular filter condition. Unlike a normal  Delete all the relationship rows that satisfy a particular filter condition. Unlike a normal
2267  filter, only fields from the relationship itself can be used.  filter, only fields from the relationship itself can be used.
# Line 2133  Line 2326 
2326    
2327  =head3 SortNeeded  =head3 SortNeeded
2328    
2329  C<< my $parms = $erdb->SortNeeded($relationName); >>      my $parms = $erdb->SortNeeded($relationName);
2330    
2331  Return the pipe command for the sort that should be applied to the specified  Return the pipe command for the sort that should be applied to the specified
2332  relation when creating the load file.  relation when creating the load file.
# Line 2189  Line 2382 
2382      }      }
2383      # Now we parse the key names into sort parameters. First, we prime the return      # Now we parse the key names into sort parameters. First, we prime the return
2384      # string.      # string.
2385      my $retVal = "sort -t\"\t\" ";      my $retVal = "sort -S 1G -T\"$FIG_Config::temp\" -t\"\t\" ";
2386      # Get the relation's field list.      # Get the relation's field list.
2387      my @fields = @{$relationData->{Fields}};      my @fields = @{$relationData->{Fields}};
2388      # Loop through the keys.      # Loop through the keys.
# Line 2219  Line 2412 
2412                  # will stop the inner loop. Note that the field number is                  # will stop the inner loop. Note that the field number is
2413                  # 1-based in the sort command, so we have to increment the                  # 1-based in the sort command, so we have to increment the
2414                  # index.                  # index.
2415                  $fieldSpec = ($i + 1) . $modifier;                  my $realI = $i + 1;
2416                    $fieldSpec = "$realI,$realI$modifier";
2417              }              }
2418          }          }
2419          # Add this field to the sort command.          # Add this field to the sort command.
# Line 2231  Line 2425 
2425    
2426  =head3 GetList  =head3 GetList
2427    
2428  C<< my @dbObjects = $erdb->GetList(\@objectNames, $filterClause, \@params); >>      my @dbObjects = $erdb->GetList(\@objectNames, $filterClause, \@params);
2429    
2430  Return a list of object descriptors for the specified objects as determined by the  Return a list of object descriptors for the specified objects as determined by the
2431  specified filter clause.  specified filter clause.
# Line 2259  Line 2453 
2453  with an ORDER BY clause. For example, the following filter string gets all genomes for a  with an ORDER BY clause. For example, the following filter string gets all genomes for a
2454  particular genus and sorts them by species name.  particular genus and sorts them by species name.
2455    
2456  C<< "Genome(genus) = ? ORDER BY Genome(species)" >>      "Genome(genus) = ? ORDER BY Genome(species)"
2457    
2458  The rules for field references in a sort order are the same as those for field references in the  The rules for field references in a sort order are the same as those for field references in the
2459  filter clause in general; however, odd things may happen if a sort field is from a secondary  filter clause in general; however, odd things may happen if a sort field is from a secondary
# Line 2294  Line 2488 
2488    
2489  =head3 GetCount  =head3 GetCount
2490    
2491  C<< my $count = $erdb->GetCount(\@objectNames, $filter, \@params); >>      my $count = $erdb->GetCount(\@objectNames, $filter, \@params);
2492    
2493  Return the number of rows found by a specified query. This method would  Return the number of rows found by a specified query. This method would
2494  normally be used to count the records in a single table. For example, in a  normally be used to count the records in a single table. For example, in a
# Line 2311  Line 2505 
2505  would return the number of features for genomes in the genus I<homo>. Note that  would return the number of features for genomes in the genus I<homo>. Note that
2506  only the rows from the first table are counted. If the above command were  only the rows from the first table are counted. If the above command were
2507    
2508      my $count = $erdb->GetCount(['Genome', 'Feature'], 'Genome(genus-species) LIKE ?',      my $count = $erdb->GetCount(['Genome', 'HasFeature'], 'Genome(genus-species) LIKE ?',
2509                                  ['homo %']);                                  ['homo %']);
2510    
2511  it would return the number of genomes, not the number of genome/feature pairs.  it would return the number of genomes, not the number of genome/feature pairs.
# Line 2387  Line 2581 
2581    
2582  =head3 ComputeObjectSentence  =head3 ComputeObjectSentence
2583    
2584  C<< my $sentence = $erdb->ComputeObjectSentence($objectName); >>      my $sentence = $erdb->ComputeObjectSentence($objectName);
2585    
2586  Check an object name, and if it is a relationship convert it to a relationship sentence.  Check an object name, and if it is a relationship convert it to a relationship sentence.
2587    
# Line 2422  Line 2616 
2616    
2617  =head3 DumpRelations  =head3 DumpRelations
2618    
2619  C<< $erdb->DumpRelations($outputDirectory); >>      $erdb->DumpRelations($outputDirectory);
2620    
2621  Write the contents of all the relations to tab-delimited files in the specified directory.  Write the contents of all the relations to tab-delimited files in the specified directory.
2622  Each file will have the same name as the relation dumped, with an extension of DTX.  Each file will have the same name as the relation dumped, with an extension of DTX.
# Line 2464  Line 2658 
2658    
2659  =head3 InsertValue  =head3 InsertValue
2660    
2661  C<< $erdb->InsertValue($entityID, $fieldName, $value); >>      $erdb->InsertValue($entityID, $fieldName, $value);
2662    
2663  This method will insert a new value into the database. The value must be one  This method will insert a new value into the database. The value must be one
2664  associated with a secondary relation, since primary values cannot be inserted:  associated with a secondary relation, since primary values cannot be inserted:
# Line 2527  Line 2721 
2721    
2722  =head3 InsertObject  =head3 InsertObject
2723    
2724  C<< $erdb->InsertObject($objectType, \%fieldHash); >>      $erdb->InsertObject($objectType, \%fieldHash);
2725    
2726  Insert an object into the database. The object is defined by a type name and then a hash  Insert an object into the database. The object is defined by a type name and then a hash
2727  of field names to values. Field values in the primary relation are represented by scalars.  of field names to values. Field values in the primary relation are represented by scalars.
# Line 2536  Line 2730 
2730  example, the following line inserts an inactive PEG feature named C<fig|188.1.peg.1> with aliases  example, the following line inserts an inactive PEG feature named C<fig|188.1.peg.1> with aliases
2731  C<ZP_00210270.1> and C<gi|46206278>.  C<ZP_00210270.1> and C<gi|46206278>.
2732    
2733  C<< $erdb->InsertObject('Feature', { id => 'fig|188.1.peg.1', active => 0, feature-type => 'peg', alias => ['ZP_00210270.1', 'gi|46206278']}); >>      $erdb->InsertObject('Feature', { id => 'fig|188.1.peg.1', active => 0, feature-type => 'peg', alias => ['ZP_00210270.1', 'gi|46206278']});
2734    
2735  The next statement inserts a C<HasProperty> relationship between feature C<fig|158879.1.peg.1> and  The next statement inserts a C<HasProperty> relationship between feature C<fig|158879.1.peg.1> and
2736  property C<4> with an evidence URL of C<http://seedu.uchicago.edu/query.cgi?article_id=142>.  property C<4> with an evidence URL of C<http://seedu.uchicago.edu/query.cgi?article_id=142>.
2737    
2738  C<< $erdb->InsertObject('HasProperty', { 'from-link' => 'fig|158879.1.peg.1', 'to-link' => 4, evidence => 'http://seedu.uchicago.edu/query.cgi?article_id=142'}); >>      $erdb->InsertObject('HasProperty', { 'from-link' => 'fig|158879.1.peg.1', 'to-link' => 4, evidence => 'http://seedu.uchicago.edu/query.cgi?article_id=142'});
2739    
2740  =over 4  =over 4
2741    
# Line 2610  Line 2804 
2804                  push @missing, $fieldName;                  push @missing, $fieldName;
2805              }              }
2806          }          }
         # If we are the primary relation, add the new-record flag.  
         if ($relationName eq $newObjectType) {  
             push @valueList, 1;  
             push @fieldNameList, "new_record";  
         }  
2807          # Only proceed if there are no missing fields.          # Only proceed if there are no missing fields.
2808          if (@missing > 0) {          if (@missing > 0) {
2809              Trace("Relation $relationName for $newObjectType skipped due to missing fields: " .              Trace("Relation $relationName for $newObjectType skipped due to missing fields: " .
# Line 2664  Line 2853 
2853    
2854  =head3 UpdateEntity  =head3 UpdateEntity
2855    
2856  C<< $erdb->UpdateEntity($entityName, $id, \%fields); >>      $erdb->UpdateEntity($entityName, $id, \%fields);
2857    
2858  Update the values of an entity. This is an unprotected update, so it should only be  Update the values of an entity. This is an unprotected update, so it should only be
2859  done if the database resides on a database server.  done if the database resides on a database server.
# Line 2722  Line 2911 
2911    
2912  =head3 LoadTable  =head3 LoadTable
2913    
2914  C<< my $results = $erdb->LoadTable($fileName, $relationName, $truncateFlag); >>      my $results = $erdb->LoadTable($fileName, $relationName, %options);
2915    
2916  Load data from a tab-delimited file into a specified table, optionally re-creating the table  Load data from a tab-delimited file into a specified table, optionally re-creating the table
2917  first.  first.
# Line 2737  Line 2926 
2926    
2927  Name of the relation to be loaded. This is the same as the table name.  Name of the relation to be loaded. This is the same as the table name.
2928    
2929  =item truncateFlag  =item options
2930    
2931  TRUE if the table should be dropped and re-created, else FALSE  A hash of load options.
2932    
2933  =item RETURN  =item RETURN
2934    
# Line 2747  Line 2936 
2936    
2937  =back  =back
2938    
2939    The permissible options are as follows.
2940    
2941    =over 4
2942    
2943    =item truncate
2944    
2945    If TRUE, then the table will be erased before loading.
2946    
2947    =item mode
2948    
2949    Mode in which the load should operate, either C<low_priority> or C<concurrent>.
2950    This option is only applicable to a MySQL database.
2951    
2952    =item partial
2953    
2954    If TRUE, then it is assumed that this is a partial load, and the table will not
2955    be analyzed and compacted at the end.
2956    
2957    =back
2958    
2959  =cut  =cut
2960  sub LoadTable {  sub LoadTable {
2961      # Get the parameters.      # Get the parameters.
2962      my ($self, $fileName, $relationName, $truncateFlag) = @_;      my ($self, $fileName, $relationName, %options) = @_;
2963      # Create the statistical return object.      # Create the statistical return object.
2964      my $retVal = _GetLoadStats();      my $retVal = _GetLoadStats();
2965      # Trace the fact of the load.      # Trace the fact of the load.
# Line 2762  Line 2971 
2971      # Get the relation data.      # Get the relation data.
2972      my $relation = $self->_FindRelation($relationName);      my $relation = $self->_FindRelation($relationName);
2973      # Check the truncation flag.      # Check the truncation flag.
2974      if ($truncateFlag) {      if ($options{truncate}) {
2975          Trace("Creating table $relationName") if T(2);          Trace("Creating table $relationName") if T(2);
2976          # Compute the row count estimate. We take the size of the load file,          # Compute the row count estimate. We take the size of the load file,
2977          # divide it by the estimated row size, and then multiply by 1.5 to          # divide it by the estimated row size, and then multiply by 2 to
2978          # leave extra room. We postulate a minimum row count of 1000 to          # leave extra room. We postulate a minimum row count of 1000 to
2979          # prevent problems with incoming empty load files.          # prevent problems with incoming empty load files.
2980          my $rowSize = $self->EstimateRowSize($relationName);          my $rowSize = $self->EstimateRowSize($relationName);
2981          my $estimate = $fileSize * 1.5 / $rowSize;          my $estimate = $fileSize * 8 / $rowSize;
2982          if ($estimate < 1000) {          if ($estimate < 1000) {
2983              $estimate = 1000;              $estimate = 1000;
2984          }          }
# Line 2788  Line 2997 
2997      # Load the table.      # Load the table.
2998      my $rv;      my $rv;
2999      eval {      eval {
3000          $rv = $dbh->load_table(file => $fileName, tbl => $relationName);          $rv = $dbh->load_table(file => $fileName, tbl => $relationName, style => $options{mode});
3001      };      };
3002      if (!defined $rv) {      if (!defined $rv) {
3003          $retVal->AddMessage($@) if ($@);          $retVal->AddMessage($@) if ($@);
# Line 2799  Line 3008 
3008          $retVal->Add("tables");          $retVal->Add("tables");
3009          my $size = -s $fileName;          my $size = -s $fileName;
3010          Trace("$size bytes loaded into $relationName.") if T(2);          Trace("$size bytes loaded into $relationName.") if T(2);
3011            $retVal->Add("bytes", $size);
3012          # If we're rebuilding, we need to create the table indexes.          # If we're rebuilding, we need to create the table indexes.
3013          if ($truncateFlag) {          if ($options{truncate}) {
3014              # Indexes are created here for PostGres. For PostGres, indexes are              # Indexes are created here for PostGres. For PostGres, indexes are
3015              # best built at the end. For MySQL, the reverse is true.              # best built at the end. For MySQL, the reverse is true.
3016              if (! $dbh->{_preIndex}) {              if (! $dbh->{_preIndex}) {
# Line 2821  Line 3031 
3031          }          }
3032      }      }
3033      # Analyze the table to improve performance.      # Analyze the table to improve performance.
3034        if (! $options{partial}) {
3035      Trace("Analyzing and compacting $relationName.") if T(3);      Trace("Analyzing and compacting $relationName.") if T(3);
3036      $dbh->vacuum_it($relationName);          $self->Analyze($relationName);
3037        }
3038      Trace("$relationName load completed.") if T(3);      Trace("$relationName load completed.") if T(3);
3039      # Return the statistics.      # Return the statistics.
3040      return $retVal;      return $retVal;
3041  }  }
3042    
3043    =head3 Analyze
3044    
3045        $erdb->Analyze($tableName);
3046    
3047    Analyze and compact a table in the database. This is useful after a load
3048    to improve the performance of the indexes.
3049    
3050    =over 4
3051    
3052    =item tableName
3053    
3054    Name of the table to be analyzed and compacted.
3055    
3056    =back
3057    
3058    =cut
3059    
3060    sub Analyze {
3061        # Get the parameters.
3062        my ($self, $tableName) = @_;
3063        # Analyze the table.
3064        $self->{_dbh}->vacuum_it($tableName);
3065    }
3066    
3067    =head3 TruncateTable
3068    
3069        $erdb->TruncateTable($table);
3070    
3071    Delete all rows from a table quickly. This uses the built-in SQL
3072    C<TRUNCATE> statement, which effectively drops and re-creates a table
3073    with all its settings intact.
3074    
3075    =over 4
3076    
3077    =item table
3078    
3079    Name of the table to be cleared.
3080    
3081    =back
3082    
3083    =cut
3084    
3085    sub TruncateTable {
3086        # Get the parameters.
3087        my ($self, $table) = @_;
3088        # Get the database handle.
3089        my $dbh = $self->{_dbh};
3090        # Execute a truncation comment.
3091        $dbh->SQL("TRUNCATE TABLE $table");
3092    }
3093    
3094    
3095  =head3 CreateSearchIndex  =head3 CreateSearchIndex
3096    
3097  C<< $erdb->CreateSearchIndex($objectName); >>      $erdb->CreateSearchIndex($objectName);
3098    
3099  Check for a full-text search index on the specified entity or relationship object, and  Check for a full-text search index on the specified entity or relationship object, and
3100  if one is required, rebuild it.  if one is required, rebuild it.
# Line 2867  Line 3131 
3131    
3132  =head3 DropRelation  =head3 DropRelation
3133    
3134  C<< $erdb->DropRelation($relationName); >>      $erdb->DropRelation($relationName);
3135    
3136  Physically drop a relation from the database.  Physically drop a relation from the database.
3137    
# Line 2895  Line 3159 
3159    
3160  =head3 MatchSqlPattern  =head3 MatchSqlPattern
3161    
3162  C<< my $matched = ERDB::MatchSqlPattern($value, $pattern); >>      my $matched = ERDB::MatchSqlPattern($value, $pattern);
3163    
3164  Determine whether or not a specified value matches an SQL pattern. An SQL  Determine whether or not a specified value matches an SQL pattern. An SQL
3165  pattern has two wild card characters: C<%> that matches multiple characters,  pattern has two wild card characters: C<%> that matches multiple characters,
# Line 2978  Line 3242 
3242    
3243  =head3 GetEntity  =head3 GetEntity
3244    
3245  C<< my $entityObject = $erdb->GetEntity($entityType, $ID); >>      my $entityObject = $erdb->GetEntity($entityType, $ID);
3246    
3247  Return an object describing the entity instance with a specified ID.  Return an object describing the entity instance with a specified ID.
3248    
# Line 3008  Line 3272 
3272      my $query = $self->Get([$entityType], "$entityType(id) = ?", [$ID]);      my $query = $self->Get([$entityType], "$entityType(id) = ?", [$ID]);
3273      # Get the first (and only) object.      # Get the first (and only) object.
3274      my $retVal = $query->Fetch();      my $retVal = $query->Fetch();
3275        if (T(3)) {
3276            if ($retVal) {
3277                Trace("Entity $entityType \"$ID\" found.");
3278            } else {
3279                Trace("Entity $entityType \"$ID\" not found.");
3280            }
3281        }
3282      # Return the result.      # Return the result.
3283      return $retVal;      return $retVal;
3284  }  }
3285    
3286  =head3 GetChoices  =head3 GetChoices
3287    
3288  C<< my @values = $erdb->GetChoices($entityName, $fieldName); >>      my @values = $erdb->GetChoices($entityName, $fieldName);
3289    
3290  Return a list of all the values for the specified field that are represented in the  Return a list of all the values for the specified field that are represented in the
3291  specified entity.  specified entity.
# Line 3069  Line 3340 
3340    
3341  =head3 GetEntityValues  =head3 GetEntityValues
3342    
3343  C<< my @values = $erdb->GetEntityValues($entityType, $ID, \@fields); >>      my @values = $erdb->GetEntityValues($entityType, $ID, \@fields);
3344    
3345  Return a list of values from a specified entity instance. If the entity instance  Return a list of values from a specified entity instance. If the entity instance
3346  does not exist, an empty list is returned.  does not exist, an empty list is returned.
# Line 3113  Line 3384 
3384    
3385  =head3 GetAll  =head3 GetAll
3386    
3387  C<< my @list = $erdb->GetAll(\@objectNames, $filterClause, \@parameters, \@fields, $count); >>      my @list = $erdb->GetAll(\@objectNames, $filterClause, \@parameters, \@fields, $count);
3388    
3389  Return a list of values taken from the objects returned by a query. The first three  Return a list of values taken from the objects returned by a query. The first three
3390  parameters correspond to the parameters of the L</Get> method. The final parameter is  parameters correspond to the parameters of the L</Get> method. The final parameter is
# Line 3129  Line 3400 
3400  spreadsheet cell, and each feature will be represented by a list containing the  spreadsheet cell, and each feature will be represented by a list containing the
3401  feature ID followed by all of its essentiality determinations.  feature ID followed by all of its essentiality determinations.
3402    
3403  C<< @query = $erdb->Get(['ContainsFeature', 'Feature'], "ContainsFeature(from-link) = ?", [$ssCellID], ['Feature(id)', 'Feature(essential)']); >>      @query = $erdb->Get(['ContainsFeature', 'Feature'], "ContainsFeature(from-link) = ?", [$ssCellID], ['Feature(id)', 'Feature(essential)']);
3404    
3405  =over 4  =over 4
3406    
# Line 3200  Line 3471 
3471          push @retVal, \@rowData;          push @retVal, \@rowData;
3472          $fetched++;          $fetched++;
3473      }      }
     Trace("$fetched rows returned in GetAll.") if T(SQL => 4);  
3474      # Return the resulting list.      # Return the resulting list.
3475      return @retVal;      return @retVal;
3476  }  }
3477    
3478  =head3 Exists  =head3 Exists
3479    
3480  C<< my $found = $sprout->Exists($entityName, $entityID); >>      my $found = $sprout->Exists($entityName, $entityID);
3481    
3482  Return TRUE if an entity exists, else FALSE.  Return TRUE if an entity exists, else FALSE.
3483    
# Line 3242  Line 3512 
3512    
3513  =head3 EstimateRowSize  =head3 EstimateRowSize
3514    
3515  C<< my $rowSize = $erdb->EstimateRowSize($relName); >>      my $rowSize = $erdb->EstimateRowSize($relName);
3516    
3517  Estimate the row size of the specified relation. The estimated row size is computed by adding  Estimate the row size of the specified relation. The estimated row size is computed by adding
3518  up the average length for each data type.  up the average length for each data type.
# Line 3280  Line 3550 
3550    
3551  =head3 GetFieldTable  =head3 GetFieldTable
3552    
3553  C<< my $fieldHash = $self->GetFieldTable($objectnName); >>      my $fieldHash = $self->GetFieldTable($objectnName);
3554    
3555  Get the field structure for a specified entity or relationship.  Get the field structure for a specified entity or relationship.
3556    
# Line 3309  Line 3579 
3579    
3580  =head3 SplitKeywords  =head3 SplitKeywords
3581    
3582  C<< my @keywords = ERDB::SplitKeywords($keywordString); >>      my @keywords = ERDB::SplitKeywords($keywordString);
3583    
3584  This method returns a list of the positive keywords in the specified  This method returns a list of the positive keywords in the specified
3585  keyword string. All of the operators will have been stripped off,  keyword string. All of the operators will have been stripped off,
# Line 3358  Line 3628 
3628    
3629  =head3 ValidateFieldName  =head3 ValidateFieldName
3630    
3631  C<< my $okFlag = ERDB::ValidateFieldName($fieldName); >>      my $okFlag = ERDB::ValidateFieldName($fieldName);
3632    
3633  Return TRUE if the specified field name is valid, else FALSE. Valid field names must  Return TRUE if the specified field name is valid, else FALSE. Valid field names must
3634  be hyphenated words subject to certain restrictions.  be hyphenated words subject to certain restrictions.
# Line 3413  Line 3683 
3683    
3684  =head3 ReadMetaXML  =head3 ReadMetaXML
3685    
3686  C<< my $rawMetaData = ERDB::ReadDBD($fileName); >>      my $rawMetaData = ERDB::ReadDBD($fileName);
3687    
3688  This method reads a raw database definition XML file and returns it.  This method reads a raw database definition XML file and returns it.
3689  Normally, the metadata used by the ERDB system has been processed and  Normally, the metadata used by the ERDB system has been processed and
# Line 3446  Line 3716 
3716    
3717  =head3 GetEntityFieldHash  =head3 GetEntityFieldHash
3718    
3719  C<< my $fieldHashRef = ERDB::GetEntityFieldHash($structure, $entityName); >>      my $fieldHashRef = ERDB::GetEntityFieldHash($structure, $entityName);
3720    
3721  Get the field hash of the named entity in the specified raw XML structure.  Get the field hash of the named entity in the specified raw XML structure.
3722  The field hash may not exist, in which case we need to create it.  The field hash may not exist, in which case we need to create it.
# Line 3488  Line 3758 
3758    
3759  =head3 WriteMetaXML  =head3 WriteMetaXML
3760    
3761  C<< ERDB::WriteMetaXML($structure, $fileName); >>      ERDB::WriteMetaXML($structure, $fileName);
3762    
3763  Write the metadata XML to a file. This method is the reverse of L</ReadMetaXML>, and is  Write the metadata XML to a file. This method is the reverse of L</ReadMetaXML>, and is
3764  used to update the database definition. It must be used with care, however, since it  used to update the database definition. It must be used with care, however, since it
# Line 3527  Line 3797 
3797  Except for C<[p]>, all the codes are closed by slash-codes. So, for  Except for C<[p]>, all the codes are closed by slash-codes. So, for
3798  example, C<[b]Feature[/b]> displays the string C<Feature> in boldface.  example, C<[b]Feature[/b]> displays the string C<Feature> in boldface.
3799    
3800  C<< my $realHtml = ERDB::HTMLNote($dataString); >>      my $realHtml = ERDB::HTMLNote($dataString);
3801    
3802  =over 4  =over 4
3803    
# Line 3557  Line 3827 
3827      return $retVal;      return $retVal;
3828  }  }
3829    
3830    =head3 WikiNote
3831    
3832    Convert a note or comment to Wiki text by replacing some bulletin-board codes with HTML. The codes
3833    supported are C<[b]> for B<bold>, C<[i]> for I<italics>, and C<[p]> for a new paragraph.
3834    Except for C<[p]>, all the codes are closed by slash-codes. So, for
3835    example, C<[b]Feature[/b]> displays the string C<Feature> in boldface.
3836    
3837        my $wikiText = ERDB::WikiNote($dataString);
3838    
3839    =over 4
3840    
3841    =item dataString
3842    
3843    String to convert to Wiki text.
3844    
3845    =item RETURN
3846    
3847    An Wiki text string derived from the input string.
3848    
3849    =back
3850    
3851    =cut
3852    
3853    sub WikiNote {
3854        # Get the parameter.
3855        my ($dataString) = @_;
3856        # HTML-escape the text.
3857        my $retVal = CGI::escapeHTML($dataString);
3858        # Substitute the bulletin board codes.
3859        my $italic = WikiTools::ItalicCode();
3860        $retVal =~ s/\[\/?i\]/$italic/g;
3861        my $bold = WikiTools::BoldCode();
3862        $retVal =~ s/\[\/?b\]/$bold/g;
3863        # Paragraph breaks are the same no matter which Wiki you're using.
3864        $retVal =~ s!\[p\]!\n\n!g;
3865        # Now we do the links, which are complicated by the need to know two
3866        # things: the target URL and the text.
3867        while ($retVal =~ /\[link\s+([^\]]+)\]([^\[]+)\[\/link\]/g) {
3868            # Replace the matched string with the Wiki markup for links. Note that
3869            # $-[0] is the starting position of the match for the entire expression,
3870            # and $+[0] is past the ending position.
3871            substr $retVal, $-[0], $+[0] - $-[0], WikiTools::LinkMarkup($1, $2);
3872        }
3873        # Return the result.
3874        return $retVal;
3875    }
3876    
3877  =head3 BeginTran  =head3 BeginTran
3878    
3879  C<< $erdb->BeginTran(); >>      $erdb->BeginTran();
3880    
3881  Start a database transaction.  Start a database transaction.
3882    
# Line 3573  Line 3890 
3890    
3891  =head3 CommitTran  =head3 CommitTran
3892    
3893  C<< $erdb->CommitTran(); >>      $erdb->CommitTran();
3894    
3895  Commit an active database transaction.  Commit an active database transaction.
3896    
# Line 3586  Line 3903 
3903    
3904  =head3 RollbackTran  =head3 RollbackTran
3905    
3906  C<< $erdb->RollbackTran(); >>      $erdb->RollbackTran();
3907    
3908  Roll back an active database transaction.  Roll back an active database transaction.
3909    
# Line 3599  Line 3916 
3916    
3917  =head3 UpdateField  =head3 UpdateField
3918    
3919  C<< my $count = $erdb->UpdateField($objectNames, $fieldName, $oldValue, $newValue, $filter, $parms); >>      my $count = $erdb->UpdateField($objectNames, $fieldName, $oldValue, $newValue, $filter, $parms);
3920    
3921  Update all occurrences of a specific field value to a new value. The number of rows changed will be  Update all occurrences of a specific field value to a new value. The number of rows changed will be
3922  returned.  returned.
# Line 3679  Line 3996 
3996    
3997  =head3 GetUsefulCrossValues  =head3 GetUsefulCrossValues
3998    
3999  C<< my @attrNames = $sprout->GetUsefulCrossValues($sourceEntity, $relationship); >>      my @attrNames = $sprout->GetUsefulCrossValues($sourceEntity, $relationship);
4000    
4001  Return a list of the useful attributes that would be returned by a B<Cross> call  Return a list of the useful attributes that would be returned by a B<Cross> call
4002  from an entity of the source entity type through the specified relationship. This  from an entity of the source entity type through the specified relationship. This
# Line 3740  Line 4057 
4057    
4058  =head3 FindColumn  =head3 FindColumn
4059    
4060  C<< my $colIndex = ERDB::FindColumn($headerLine, $columnIdentifier); >>      my $colIndex = ERDB::FindColumn($headerLine, $columnIdentifier);
4061    
4062  Return the location a desired column in a data mining header line. The data  Return the location a desired column in a data mining header line. The data
4063  mining header line is a tab-separated list of column names. The column  mining header line is a tab-separated list of column names. The column
# Line 3798  Line 4115 
4115    
4116  =head3 ParseColumns  =head3 ParseColumns
4117    
4118  C<< my @columns = ERDB::ParseColumns($line); >>      my @columns = ERDB::ParseColumns($line);
4119    
4120  Convert the specified data line to a list of columns.  Convert the specified data line to a list of columns.
4121    
# Line 3832  Line 4149 
4149    
4150  =head3 _CreatePPOIndex  =head3 _CreatePPOIndex
4151    
4152  C<< my $index = ERDB::_CreatePPOIndex($indexObject); >>      my $index = ERDB::_CreatePPOIndex($indexObject);
4153    
4154  Convert the XML for an ERDB index to the XML structure for a PPO  Convert the XML for an ERDB index to the XML structure for a PPO
4155  index.  index.
4156    
4157  =over 4  =over 4
4158    
4159    =item indexObject
4160    
4161  ERDB XML structure for an index.  ERDB XML structure for an index.
4162    
4163  =item RETURN  =item RETURN
# Line 3863  Line 4182 
4182    
4183  =head3 _CreatePPOField  =head3 _CreatePPOField
4184    
4185  C<< my $fieldXML = ERDB::_CreatePPOField($fieldName, $fieldObject); >>      my $fieldXML = ERDB::_CreatePPOField($fieldName, $fieldObject);
4186    
4187  Convert the ERDB XML structure for a field to a PPO scalar XML structure.  Convert the ERDB XML structure for a field to a PPO scalar XML structure.
4188    
# Line 3900  Line 4219 
4219    
4220  =head3 CleanKeywords  =head3 CleanKeywords
4221    
4222  C<< my $cleanedString = $erdb->CleanKeywords($searchExpression); >>      my $cleanedString = $erdb->CleanKeywords($searchExpression);
4223    
4224  Clean up a search expression or keyword list. This is a virtual method that may  Clean up a search expression or keyword list. This is a virtual method that may
4225  be overridden by the subclass. The base-class method removes extra spaces  be overridden by the subclass. The base-class method removes extra spaces
# Line 3937  Line 4256 
4256    
4257  =head3 GetSourceObject  =head3 GetSourceObject
4258    
4259  C<< my $source = $erdb->GetSourceObject($entityName); >>      my $source = $erdb->GetSourceObject($entityName);
4260    
4261  Return the object to be used in loading special attributes of the specified entity. The  Return the object to be used in loading special attributes of the specified entity. The
4262  algorithm for loading special attributes is stored in the C<DataGen> elements of the  algorithm for loading special attributes is stored in the C<DataGen> elements of the
# Line 3947  Line 4266 
4266    
4267  =head3 _RelationMap  =head3 _RelationMap
4268    
4269  C<< my @relationMap = _RelationMap($mappedNameHashRef, $mappedNameListRef); >>      my @relationMap = _RelationMap($mappedNameHashRef, $mappedNameListRef);
4270    
4271  Create the relation map for an SQL query. The relation map is used by B<ERDBObject>  Create the relation map for an SQL query. The relation map is used by B<ERDBObject>
4272  to determine how to interpret the results of the query.  to determine how to interpret the results of the query.
# Line 4260  Line 4579 
4579  sub _GetStatementHandle {  sub _GetStatementHandle {
4580      # Get the parameters.      # Get the parameters.
4581      my ($self, $command, $params) = @_;      my ($self, $command, $params) = @_;
4582        Confess("Invalid parameter list.") if (! defined($params) || ref($params) ne 'ARRAY');
4583      # Trace the query.      # Trace the query.
4584      Trace("SQL query: $command") if T(SQL => 3);      Trace("SQL query: $command") if T(SQL => 3);
4585      Trace("PARMS: '" . (join "', '", @{$params}) . "'") if (T(SQL => 4) && (@{$params} > 0));      Trace("PARMS: '" . (join "', '", @{$params}) . "'") if (T(SQL => 4) && (@{$params} > 0));
# Line 4497  Line 4817 
4817      # be a null string.      # be a null string.
4818      if ($fileName ne "") {      if ($fileName ne "") {
4819          # Load the relation from the file.          # Load the relation from the file.
4820          $retVal = $self->LoadTable($fileName, $relationName, $rebuild);          $retVal = $self->LoadTable($fileName, $relationName, truncate => $rebuild);
4821      } elsif ($rebuild) {      } elsif ($rebuild) {
4822          # Here we are rebuilding, but no file exists, so we just re-create the table.          # Here we are rebuilding, but no file exists, so we just re-create the table.
4823          $self->CreateTable($relationName, 1);          $self->CreateTable($relationName, 1);
# Line 4509  Line 4829 
4829    
4830  =head3 _LoadMetaData  =head3 _LoadMetaData
4831    
4832  C<< my $metadata = ERDB::_LoadMetaData($filename); >>      my $metadata = ERDB::_LoadMetaData($filename);
4833    
4834  This method loads the data describing this database from an XML file into a metadata structure.  This method loads the data describing this database from an XML file into a metadata structure.
4835  The resulting structure is a set of nested hash tables containing all the information needed to  The resulting structure is a set of nested hash tables containing all the information needed to
# Line 4867  Line 5187 
5187    
5188  =head3 _ProcessIndexes  =head3 _ProcessIndexes
5189    
5190  C<< ERDB::_ProcessIndexes($indexList, $relation); >>      ERDB::_ProcessIndexes($indexList, $relation);
5191    
5192  Build the data structures for the specified indexes in the specified relation.  Build the data structures for the specified indexes in the specified relation.
5193    
# Line 5244  Line 5564 
5564      return $retVal;      return $retVal;
5565  }  }
5566    
5567  =head2 HTML Documentation Utility Methods  =head2 Documentation Utility Methods
5568    
5569  =head3 _ComputeRelationshipSentence  =head3 _ComputeRelationshipSentence
5570    
# Line 5276  Line 5596 
5596      # Get the parameters.      # Get the parameters.
5597      my ($relationshipName, $relationshipStructure) = @_;      my ($relationshipName, $relationshipStructure) = @_;
5598      # Format the relationship sentence.      # Format the relationship sentence.
5599      my $result = "$relationshipStructure->{from} <b>$relationshipName</b> $relationshipStructure->{to}";      my $result = "$relationshipStructure->{from} $relationshipName $relationshipStructure->{to}";
5600      # Compute the arity.      # Compute the arity.
5601      my $arityCode = $relationshipStructure->{arity};      my $arityCode = $relationshipStructure->{arity};
5602      my $arity = $ArityTable{$arityCode};      my $arity = $ArityTable{$arityCode};
# Line 5321  Line 5641 
5641      return $result;      return $result;
5642  }  }
5643    
5644    =head3 _WikiRelationTable
5645    
5646    Generate the Wiki text for a particular relation. The relation's data will be formatted as a
5647    table with three columns-- the field name, the field type, and the field description.
5648    
5649    This is a static method.
5650    
5651    =over 4
5652    
5653    =item relationName
5654    
5655    Name of the relation being formatted.
5656    
5657    =item relationData
5658    
5659    Hash containing the relation's fields and indexes.
5660    
5661    =item RETURN
5662    
5663    Returns a Wiki string that can be used to display the relation name and all of its fields.
5664    
5665    =back
5666    
5667    =cut
5668    
5669    sub _WikiRelationTable {
5670        # Get the parameters.
5671        my ($relationName, $relationData) = @_;
5672        # We'll create a list of lists in here, then call WikiTools::Table to
5673        # convert it into a table.
5674        my @rows = ();
5675        # Push in the header row.
5676        push @rows, [qw(Field Type Description)];
5677        # Loop through the fields.
5678        for my $field (@{$relationData->{Fields}}) {
5679            # Create this field's row. We always have a name and type.
5680            my @row = ($field->{name}, $field->{type});
5681            # If we have a description, add it as the third column.
5682            if (exists $field->{Notes}) {
5683                push @row, WikiNote($field->{Notes}->{content});
5684            }
5685            # Push this row onto the table list.
5686            push @rows, \@row;
5687        }
5688        # Store the rows as a Wiki table with a level-4 heading.
5689        my $retVal = join("\n\n", WikiTools::Heading(4, "$relationName Table"),
5690                          WikiTools::Table(@rows));
5691        # Now we show the relation's indexes. These are formatted as another
5692        # table.
5693        @rows = ();
5694        # Push in the header row.
5695        push @rows, [qw(Index Unique Fields Notes)];
5696        # Get the index hash.
5697        my $indexTable = $relationData->{Indexes};
5698        # Loop through the indexes. For an entity, there is always at least one index.
5699        # For a relationship, there are at least two. The upshot is we don't need to
5700        # worry about accidentally generating a frivolous table here.
5701        for my $indexName (sort keys %$indexTable) {
5702            my $indexData = $indexTable->{$indexName};
5703            # Determine whether or not the index is unique.
5704            my $unique = ((exists $indexData->{Unique} && $indexData->{Unique} eq "true") ?
5705                          "yes" : "");
5706            # Get the field list.
5707            my $fields = join(', ', @{$indexData->{IndexFields}});
5708            # Get the note text.
5709            my $description = "";
5710            if (my $note = $indexData->{Notes}) {
5711                $description = WikiNote($note->{content});
5712            }
5713            # Format this row.
5714            my @row = ($indexName, $unique, $fields, $description);
5715            push @rows, \@row;
5716        }
5717        # Add the index list to the result.
5718        $retVal .= "\n\n" . WikiTools::Table(@rows);
5719    }
5720    
5721  =head3 _ShowRelationTable  =head3 _ShowRelationTable
5722    
5723  Generate the HTML string for a particular relation. The relation's data will be formatted as an HTML  Generate the HTML string for a particular relation. The relation's data will be formatted as an HTML
# Line 5494  Line 5891 
5891      return $htmlString;      return $htmlString;
5892  }  }
5893    
5894    =head3 _ObjectNotes
5895    
5896        my @noteParagraphs = _ObjectNotes($objectData);
5897    
5898    Return a list of the notes and asides for an entity or relationship in
5899    Wiki format.
5900    
5901    =over 4
5902    
5903    =item objectData
5904    
5905    The metadata for the desired entity or relationship.
5906    
5907    =item RETURN
5908    
5909    Returns a list of text paragraphs in Wiki markup form.
5910    
5911    =back
5912    
5913    =cut
5914    
5915    sub _ObjectNotes {
5916        # Get the parameters.
5917        my ($objectData) = @_;
5918        # Declare the return variable.
5919        my @retVal;
5920        # Loop through the types of notes.
5921        for my $noteType (qw(Notes Asides)) {
5922            my $text = $objectData->{$noteType};
5923            if ($text) {
5924                push @retVal, "", WikiNote($text->{content});
5925            }
5926        }
5927        # Return the result.
5928        return @retVal;
5929    }
5930    
5931  1;  1;

Legend:
Removed from v.1.92  
changed lines
  Added in v.1.105

MCS Webmaster
ViewVC Help
Powered by ViewVC 1.0.3