Diff of /Sprout/ERDB.pm

-revision 1.47, Sun Jun 18 05:14:56 2006 UTC
+revision 1.70, Fri Oct 13 21:45:11 2006 UTC
 Line 91
 -bit signed integer
+ =item counter
+-bit unsigned integer
  =item date
 -bit unsigned integer, representing a PERL date/time value
-Line 186
+Line 190
  Name of the field. The field name should contain only letters, digits, and hyphens (C<->),
  and the first character should be a letter. Most underlying databases are case-insensitive
- with the respect to field names, so a best practice is to use lower-case letters only.
+ with the respect to field names, so a best practice is to use lower-case letters only. Finally,
+ the name C<search-relevance> has special meaning for full-text searches and should not be
+ used as a field name.
  =item type
-Line 205
+Line 211
  entity, the fields without a relation attribute are said to belong to the
  I<primary relation>. This relation has the same name as the entity itself.
+ =item searchable
+ If specified, then the field is a candidate for full-text searching. A single full-text
+ index will be created for each relation with at least one searchable field in it.
+ For best results, this option should only be used for string or text fields.
  =back
  =head3 Indexes
-Line 318
+Line 330
  # "maxLen" is the maximum permissible length of the incoming string data used to populate a field
  # of the specified type. "dataGen" is PERL string that will be evaluated if no test data generation
  # string is specified in the field definition. "avgLen" is the average byte length for estimating
- # record sizes.
+ # record sizes. "sort" is the key modifier for the sort command.
- my %TypeTable = ( char =>    { sqlType => 'CHAR(1)',            maxLen => 1,            avgLen =>   1, dataGen => "StringGen('A')" },
+ my %TypeTable = ( char =>    { sqlType => 'CHAR(1)',            maxLen => 1,            avgLen =>   1, sort => "",  dataGen => "StringGen('A')" },
-                   int =>     { sqlType => 'INTEGER',            maxLen => 20,           avgLen =>   4, dataGen => "IntGen(0, 99999999)" },
+                   int =>     { sqlType => 'INTEGER',            maxLen => 20,           avgLen =>   4, sort => "n", dataGen => "IntGen(0, 99999999)" },
-                   string =>  { sqlType => 'VARCHAR(255)',       maxLen => 255,          avgLen => 100, dataGen => "StringGen(IntGen(10,250))" },
+                   counter => { sqlType => 'INTEGER UNSIGNED',   maxLen => 20,           avgLen =>   4, sort => "n", dataGen => "IntGen(0, 99999999)" },
-                   text =>    { sqlType => 'TEXT',               maxLen => 1000000000,   avgLen => 500, dataGen => "StringGen(IntGen(80,1000))" },
+                   string =>  { sqlType => 'VARCHAR(255)',       maxLen => 255,          avgLen => 100, sort => "",  dataGen => "StringGen(IntGen(10,250))" },
-                   date =>    { sqlType => 'BIGINT',             maxLen => 80,           avgLen =>   8, dataGen => "DateGen(-7, 7, IntGen(0,1400))" },
+                   text =>    { sqlType => 'TEXT',               maxLen => 1000000000,   avgLen => 500, sort => "",  dataGen => "StringGen(IntGen(80,1000))" },
-                   float =>   { sqlType => 'DOUBLE PRECISION',   maxLen => 40,           avgLen =>   8, dataGen => "FloatGen(0.0, 100.0)" },
+                   date =>    { sqlType => 'BIGINT',             maxLen => 80,           avgLen =>   8, sort => "n", dataGen => "DateGen(-7, 7, IntGen(0,1400))" },
-                   boolean => { sqlType => 'SMALLINT',           maxLen => 1,            avgLen =>   1, dataGen => "IntGen(0, 1)" },
+                   float =>   { sqlType => 'DOUBLE PRECISION',   maxLen => 40,           avgLen =>   8, sort => "g", dataGen => "FloatGen(0.0, 100.0)" },
+                   boolean => { sqlType => 'SMALLINT',           maxLen => 1,            avgLen =>   1, sort => "n", dataGen => "IntGen(0, 1)" },
                   'hash-string' =>
-                              { sqlType => 'VARCHAR(22)',        maxLen => 22,           avgLen =>  22, dataGen => "SringGen(22)" },
+                              { sqlType => 'VARCHAR(22)',        maxLen => 22,           avgLen =>  22, sort => "",  dataGen => "SringGen(22)" },
                   'id-string' =>
-                              { sqlType => 'VARCHAR(25)',        maxLen => 25,           avgLen =>  25, dataGen => "SringGen(22)" },
+                              { sqlType => 'VARCHAR(25)',        maxLen => 25,           avgLen =>  25, sort => "",  dataGen => "SringGen(22)" },
                   'key-string' =>
-                              { sqlType => 'VARCHAR(40)',        maxLen => 40,           avgLen =>  10, dataGen => "StringGen(IntGen(10,40))" },
+                              { sqlType => 'VARCHAR(40)',        maxLen => 40,           avgLen =>  10, sort => "",  dataGen => "StringGen(IntGen(10,40))" },
                   'name-string' =>
-                              { sqlType => 'VARCHAR(80)',        maxLen => 80,           avgLen =>  40, dataGen => "StringGen(IntGen(10,80))" },
+                              { sqlType => 'VARCHAR(80)',        maxLen => 80,           avgLen =>  40, sort => "",  dataGen => "StringGen(IntGen(10,80))" },
                   'medium-string' =>
-                              { sqlType => 'VARCHAR(160)',       maxLen => 160,          avgLen =>  40, dataGen => "StringGen(IntGen(10,160))" },
+                              { sqlType => 'VARCHAR(160)',       maxLen => 160,          avgLen =>  40, sort => "",  dataGen => "StringGen(IntGen(10,160))" },
                  );
  # Table translating arities into natural language.
-Line 684
+Line 697
      Trace("Creating table $relationName: $fieldThing") if T(2);
      $dbh->create_table(tbl => $relationName, flds => $fieldThing, estimates => $estimation);
      Trace("Relation $relationName created in database.") if T(2);
-     # If we want to build the indexes, we do it here.
+     # If we want to build the indexes, we do it here. Note that the full-text search
+     # index will not be built until the table has been loaded.
      if ($indexFlag) {
          $self->CreateIndex($relationName);
      }
-Line 844
+Line 858
          my @fieldList = _FixNames(@{$indexData->{IndexFields}});
          my $flds = join(', ', @fieldList);
          # Get the index's uniqueness flag.
-         my $unique = (exists $indexData->{Unique} ? $indexData->{Unique} : 'false');
+         my $unique = (exists $indexData->{Unique} ? 'unique' : undef);
          # Create the index.
          my $rv = $dbh->create_index(idx => $indexName, tbl => $relationName,
-                                     flds => $flds, unique => $unique);
+                                     flds => $flds, kind => $unique);
          if ($rv) {
              Trace("Index created: $indexName for $relationName ($flds)") if T(1);
          } else {
-Line 1094
+Line 1108
      return $retVal;
  }
+ =head3 Search
+ C<< my $query = $erdb->Search($searchExpression, $idx, \@objectNames, $filterClause, \@params); >>
+ Perform a full text search with filtering. The search will be against a specified object
+ in the object name list. That object will get an extra field containing the search
+ relevance. Note that except for the search expression, the parameters of this method are
+ the same as those for L</Get> and follow the same rules.
+ =over 4
+ =item searchExpression
+ Boolean search expression for the text fields of the target object.
+ =item idx
+ Index in the I<$objectNames> list of the table to be searched in full-text mode.
+ =item objectNames
+ List containing the names of the entity and relationship objects to be retrieved.
+ =item filterClause
+ WHERE clause (without the WHERE) to be used to filter and sort the query. The WHERE clause can
+ be parameterized with parameter markers (C<?>). Each field used in the WHERE clause must be
+ specified in the standard form B<I<objectName>(I<fieldName>)>. Any parameters specified
+ in the filter clause should be added to the parameter list as additional parameters. The
+ fields in a filter clause can come from primary entity relations, relationship relations,
+ or secondary entity relations; however, all of the entities and relationships involved must
+ be included in the list of object names.
+ =item params
+ Reference to a list of parameter values to be substituted into the filter clause.
+ =item RETURN
+ Returns a query object for the specified search.
+ =back
+ =cut
+ sub Search {
+     # Get the parameters.
+     my ($self, $searchExpression, $idx, $objectNames, $filterClause, $params) = @_;
+     # Declare the return variable.
+     my $retVal;
+     # Create a safety copy of the parameter list.
+     my @myParams = @{$params};
+     # Get the first object's structure so we have access to the searchable fields.
+     my $object1Name = $objectNames->[$idx];
+     my $object1Structure = $self->_GetStructure($object1Name);
+     # Get the field list.
+     if (! exists $object1Structure->{searchFields}) {
+         Confess("No searchable index for $object1Name.");
+     } else {
+         # Get the field list.
+         my @fields = @{$object1Structure->{searchFields}};
+         # We need two match expressions, one for the filter clause and one in the
+         # query itself. Both will use a parameter mark, so we need to push the
+         # search expression onto the front of the parameter list twice.
+         unshift @myParams, $searchExpression, $searchExpression;
+         # Build the match expression.
+         my @matchFilterFields = map { "$object1Name." . _FixName($_) } @fields;
+         my $matchClause = "MATCH (" . join(", ", @matchFilterFields) . ") AGAINST (? IN BOOLEAN MODE)";
+         # Process the SQL stuff.
+         my ($suffix, $mappedNameListRef, $mappedNameHashRef) =
+             $self->_SetupSQL($objectNames, $filterClause, $matchClause);
+         # Create the query. Note that the match clause is inserted at the front of
+         # the select fields.
+         my $command = "SELECT DISTINCT $matchClause, " . join(".*, ", @{$mappedNameListRef}) .
+             ".* $suffix";
+         my $sth = $self->_GetStatementHandle($command, \@myParams);
+         # Now we create the relation map, which enables DBQuery to determine the order, name
+         # and mapped name for each object in the query.
+         my @relationMap = _RelationMap($mappedNameHashRef, $mappedNameListRef);
+         # Return the statement object.
+         $retVal = DBQuery::_new($self, $sth, \@relationMap, $object1Name);
+     }
+     return $retVal;
+ }
  =head3 GetFlat
  C<< my @list = $erdb->GetFlat(\@objectNames, $filterClause, \@parameterList, $field); >>
-Line 1315
+Line 1414
      return $retVal;
  }
+ =head3 SortNeeded
+ C<< my $parms = $erdb->SortNeeded($relationName); >>
+ Return the pipe command for the sort that should be applied to the specified
+ relation when creating the load file.
+ For example, if the load file should be sorted ascending by the first
+ field, this method would return
+     sort -k1 -t"\t"
+ If the first field is numeric, the method would return
+     sort -k1n -t"\t"
+ Unfortunately, due to a bug in the C<sort> command, we cannot eliminate duplicate
+ keys using a sort.
+ =over 4
+ =item relationName
+ Name of the relation to be examined.
+ =item
+ Returns the sort command to use for sorting the relation, suitable for piping.
+ =back
+ =cut
+ #: Return Type $;
+ sub SortNeeded {
+     # Get the parameters.
+     my ($self, $relationName) = @_;
+     # Declare a descriptor to hold the names of the key fields.
+     my @keyNames = ();
+     # Get the relation structure.
+     my $relationData = $self->_FindRelation($relationName);
+     # Find out if the relation is a primary entity relation,
+     # a relationship relation, or a secondary entity relation.
+     my $entityTable = $self->{_metaData}->{Entities};
+     my $relationshipTable = $self->{_metaData}->{Relationships};
+     if (exists $entityTable->{$relationName}) {
+         # Here we have a primary entity relation.
+         push @keyNames, "id";
+     } elsif (exists $relationshipTable->{$relationName}) {
+         # Here we have a relationship. We sort using the FROM index.
+         my $relationshipData = $relationshipTable->{$relationName};
+         my $index = $relationData->{Indexes}->{"idx${relationName}From"};
+         push @keyNames, @{$index->{IndexFields}};
+     } else {
+         # Here we have a secondary entity relation, so we have a sort on the ID field.
+         push @keyNames, "id";
+     }
+     # Now we parse the key names into sort parameters. First, we prime the return
+     # string.
+     my $retVal = "sort -t\"\t\" ";
+     # Get the relation's field list.
+     my @fields = @{$relationData->{Fields}};
+     # Loop through the keys.
+     for my $keyData (@keyNames) {
+         # Get the key and the ordering.
+         my ($keyName, $ordering);
+         if ($keyData =~ /^([^ ]+) DESC/) {
+             ($keyName, $ordering) = ($1, "descending");
+         } else {
+             ($keyName, $ordering) = ($keyData, "ascending");
+         }
+         # Find the key's position and type.
+         my $fieldSpec;
+         for (my $i = 0; $i <= $#fields && ! $fieldSpec; $i++) {
+             my $thisField = $fields[$i];
+             if ($thisField->{name} eq $keyName) {
+                 # Get the sort modifier for this field type. The modifier
+                 # decides whether we're using a character, numeric, or
+                 # floating-point sort.
+                 my $modifier = $TypeTable{$thisField->{type}}->{sort};
+                 # If the index is descending for this field, denote we want
+                 # to reverse the sort order on this field.
+                 if ($ordering eq 'descending') {
+                     $modifier .= "r";
+                 }
+                 # Store the position and modifier into the field spec, which
+                 # will stop the inner loop. Note that the field number is
+                 # 1-based in the sort command, so we have to increment the
+                 # index.
+                 $fieldSpec = ($i + 1) . $modifier;
+             }
+         }
+         # Add this field to the sort command.
+         $retVal .= " -k$fieldSpec";
+     }
+     # Return the result.
+     return $retVal;
+ }
  =head3 GetList
  C<< my @dbObjects = $erdb->GetList(\@objectNames, $filterClause, \@params); >>
-Line 1431
+Line 1628
  sub GetCount {
      # Get the parameters.
      my ($self, $objectNames, $filter, $params) = @_;
+     # Insure the params argument is an array reference if the caller left it off.
+     if (! defined($params)) {
+         $params = [];
+     }
      # Declare the return variable.
      my $retVal;
      # Find out if we're counting an entity or a relationship.
-Line 1544
+Line 1745
      }
  }
+ =head3 InsertValue
+ C<< $erdb->InsertValue($entityID, $fieldName, $value); >>
+ This method will insert a new value into the database. The value must be one
+ associated with a secondary relation, since primary values cannot be inserted:
+ they occur exactly once. Secondary values, on the other hand, can be missing
+ or multiply-occurring.
+ =over 4
+ =item entityID
+ ID of the object that is to receive the new value.
+ =item fieldName
+ Field name for the new value-- this includes the entity name, since
+ field names are of the format I<objectName>C<(>I<fieldName>C<)>.
+ =item value
+ New value to be put in the field.
+ =back
+ =cut
+ sub InsertValue {
+     # Get the parameters.
+     my ($self, $entityID, $fieldName, $value) = @_;
+     # Parse the entity name and the real field name.
+     if ($fieldName =~ /^([^(]+)\(([^)]+)\)/) {
+         my $entityName = $1;
+         my $fieldTitle = $2;
+         # Get its descriptor.
+         if (!$self->IsEntity($entityName)) {
+             Confess("$entityName is not a valid entity.");
+         } else {
+             my $entityData = $self->{_metaData}->{Entities}->{$entityName};
+             # Find the relation containing this field.
+             my $fieldHash = $entityData->{Fields};
+             if (! exists $fieldHash->{$fieldTitle}) {
+                 Confess("$fieldTitle not found in $entityName.");
+             } else {
+                 my $relation = $fieldHash->{$fieldTitle}->{relation};
+                 if ($relation eq $entityName) {
+                     Confess("Cannot do InsertValue on primary field $fieldTitle of $entityName.");
+                 } else {
+                     # Now we can create an INSERT statement.
+                     my $dbh = $self->{_dbh};
+                     my $fixedName = _FixName($fieldTitle);
+                     my $statement = "INSERT INTO $relation (id, $fixedName) VALUES(?, ?)";
+                     # Execute the command.
+                     $dbh->SQL($statement, 0, $entityID, $value);
+                 }
+             }
+         }
+     } else {
+         Confess("$fieldName is not a valid field name.");
+     }
+ }
  =head3 InsertObject
  C<< my $ok = $erdb->InsertObject($objectType, \%fieldHash); >>
-Line 1560
+Line 1824
  The next statement inserts a C<HasProperty> relationship between feature C<fig|158879.1.peg.1> and
  property C<4> with an evidence URL of C<http://seedu.uchicago.edu/query.cgi?article_id=142>.
- C<< $erdb->InsertObject('HasProperty', { 'from-link' => 'fig|158879.1.peg.1', 'to-link' => 4, evidence = 'http://seedu.uchicago.edu/query.cgi?article_id=142'}); >>
+ C<< $erdb->InsertObject('HasProperty', { 'from-link' => 'fig|158879.1.peg.1', 'to-link' => 4, evidence => 'http://seedu.uchicago.edu/query.cgi?article_id=142'}); >>
  =over 4
-Line 1760
+Line 2024
          my $size = -s $fileName;
          Trace("$size bytes loaded into $relationName.") if T(2);
          # If we're rebuilding, we need to create the table indexes.
-         if ($truncateFlag && ! $dbh->{_preIndex}) {
+         if ($truncateFlag) {
+             # Indexes are created here for PostGres. For PostGres, indexes are
+             # best built at the end. For MySQL, the reverse is true.
+             if (! $dbh->{_preIndex}) {
              eval {
                  $self->CreateIndex($relationName);
              };
-Line 1768
+Line 2035
                  $retVal->AddMessage($@);
              }
          }
+             # The full-text index (if any) is always built last, even for MySQL.
+             # First we need to see if this table has a full-text index. Only
+             # primary relations are allowed that privilege.
+             if ($self->_IsPrimary($relationName)) {
+                 # Get the relation's entity/relationship structure.
+                 my $structure = $self->_GetStructure($relationName);
+                 # Check for a searchable fields list.
+                 if (exists $structure->{searchFields}) {
+                     # Here we know that we need to create a full-text search index.
+                     # Get an SQL-formatted field name list.
+                     my $fields = join(", ", $self->_FixNames(@{$structure->{searchFields}}));
+                     # Create the index.
+                     $dbh->create_index(tbl => $relationName, idx => "search_idx_$relationName",
+                                        flds => $fields, kind => 'fulltext');
+                 }
+             }
+         }
      }
      # Analyze the table to improve performance.
+     Trace("Analyzing and compacting $relationName.") if T(3);
      $dbh->vacuum_it($relationName);
+     Trace("$relationName load completed.") if T(3);
      # Return the statistics.
      return $retVal;
  }
-Line 1869
+Line 2155
      return $retVal;
  }
+ =head3 GetChoices
+ C<< my @values = $erdb->GetChoices($entityName, $fieldName); >>
+ Return a list of all the values for the specified field that are represented in the
+ specified entity.
+ Note that if the field is not indexed, then this will be a very slow operation.
+ =over 4
+ =item entityName
+ Name of an entity in the database.
+ =item fieldName
+ Name of a field belonging to the entity. This is a raw field name without
+ the standard parenthesized notation used in most calls.
+ =item RETURN
+ Returns a list of the distinct values for the specified field in the database.
+ =back
+ =cut
+ sub GetChoices {
+     # Get the parameters.
+     my ($self, $entityName, $fieldName) = @_;
+     # Declare the return variable.
+     my @retVal;
+     # Get the entity data structure.
+     my $entityData = $self->_GetStructure($entityName);
+     # Get the field.
+     my $fieldHash = $entityData->{Fields};
+     if (! exists $fieldHash->{$fieldName}) {
+         Confess("$fieldName not found in $entityName.");
+     } else {
+         # Get the name of the relation containing the field.
+         my $relation = $fieldHash->{$fieldName}->{relation};
+         # Fix up the field name.
+         my $realName = _FixName($fieldName);
+         # Get the database handle.
+         my $dbh = $self->{_dbh};
+         # Query the database.
+         my $results = $dbh->SQL("SELECT DISTINCT $realName FROM $relation");
+         # Clean the results. They are stored as a list of lists, and we just want the one list.
+         @retVal = sort map { $_->[0] } @{$results};
+     }
+     # Return the result.
+     return @retVal;
+ }
  =head3 GetEntityValues
  C<< my @values = $erdb->GetEntityValues($entityType, $ID, \@fields); >>
- Return a list of values from a specified entity instance.
+ Return a list of values from a specified entity instance. If the entity instance
+ does not exist, an empty list is returned.
  =over 4
-Line 2001
+Line 2343
          push @retVal, \@rowData;
          $fetched++;
      }
+     Trace("$fetched rows returned in GetAll.") if T(SQL => 4);
      # Return the resulting list.
      return @retVal;
  }
+ =head3 Exists
+ C<< my $found = $sprout->Exists($entityName, $entityID); >>
+ Return TRUE if an entity exists, else FALSE.
+ =over 4
+ =item entityName
+ Name of the entity type (e.g. C<Feature>) relevant to the existence check.
+ =item entityID
+ ID of the entity instance whose existence is to be checked.
+ =item RETURN
+ Returns TRUE if the entity instance exists, else FALSE.
+ =back
+ =cut
+ #: Return Type $;
+ sub Exists {
+     # Get the parameters.
+     my ($self, $entityName, $entityID) = @_;
+     # Check for the entity instance.
+     Trace("Checking existence of $entityName with ID=$entityID.") if T(4);
+     my $testInstance = $self->GetEntity($entityName, $entityID);
+     # Return an existence indicator.
+     my $retVal = ($testInstance ? 1 : 0);
+     return $retVal;
+ }
  =head3 EstimateRowSize
  C<< my $rowSize = $erdb->EstimateRowSize($relName); >>
-Line 2072
+Line 2450
      return $objectData->{Fields};
  }
+ =head2 Data Mining Methods
  =head3 GetUsefulCrossValues
  C<< my @attrNames = $sprout->GetUsefulCrossValues($sourceEntity, $relationship); >>
-Line 2133
+Line 2513
      return @retVal;
  }
+ =head3 FindColumn
+ C<< my $colIndex = ERDB::FindColumn($headerLine, $columnIdentifier); >>
+ Return the location a desired column in a data mining header line. The data
+ mining header line is a tab-separated list of column names. The column
+ identifier is either the numerical index of a column or the actual column
+ name.
+ =over 4
+ =item headerLine
+ The header line from a data mining command, which consists of a tab-separated
+ list of column names.
+ =item columnIdentifier
+ Either the ordinal number of the desired column (1-based), or the name of the
+ desired column.
+ =item RETURN
+ Returns the array index (0-based) of the desired column.
+ =back
+ =cut
+ sub FindColumn {
+     # Get the parameters.
+     my ($headerLine, $columnIdentifier) = @_;
+     # Declare the return variable.
+     my $retVal;
+     # Split the header line into column names.
+     my @headers = ParseColumns($headerLine);
+     # Determine whether we have a number or a name.
+     if ($columnIdentifier =~ /^\d+$/) {
+         # Here we have a number. Subtract 1 and validate the result.
+         $retVal = $columnIdentifier - 1;
+         if ($retVal < 0 || $retVal > $#headers) {
+             Confess("Invalid column identifer \"$columnIdentifier\": value out of range.");
+         }
+     } else {
+         # Here we have a name. We need to find it in the list.
+         for (my $i = 0; $i <= $#headers && ! defined($retVal); $i++) {
+             if ($headers[$i] eq $columnIdentifier) {
+                 $retVal = $i;
+             }
+         }
+         if (! defined($retVal)) {
+             Confess("Invalid column identifier \"$columnIdentifier\": value not found.");
+         }
+     }
+     # Return the result.
+     return $retVal;
+ }
+ =head3 ParseColumns
+ C<< my @columns = ERDB::ParseColumns($line); >>
+ Convert the specified data line to a list of columns.
+ =over 4
+ =item line
+ A data mining input, consisting of a tab-separated list of columns terminated by a
+ new-line.
+ =item RETURN
+ Returns a list consisting of the column values.
+ =back
+ =cut
+ sub ParseColumns {
+     # Get the parameters.
+     my ($line) = @_;
+     # Chop off the line-end.
+     chomp $line;
+     # Split it into a list.
+     my @retVal = split(/\t/, $line);
+     # Return the result.
+     return @retVal;
+ }
  =head2 Internal Utility Methods
- =head3 SetupSQL
+ =head3 _RelationMap
+ C<< my @relationMap = _RelationMap($mappedNameHashRef, $mappedNameListRef); >>
+ Create the relation map for an SQL query. The relation map is used by B<DBObject>
+ to determine how to interpret the results of the query.
+ =over 4
+ =item mappedNameHashRef
+ Reference to a hash that maps modified object names to real object names.
+ =item mappedNameListRef
+ Reference to a list of modified object names in the order they appear in the
+ SELECT list.
+ =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
+ 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
+ clause will use a mapped name (generally the actual relation name with a numeric
+ suffix) that does not match the actual relation name.
+ =back
+ =cut
+ sub _RelationMap {
+     # Get the parameters.
+     my ($mappedNameHashRef, $mappedNameListRef) = @_;
+     # Declare the return variable.
+     my @retVal = ();
+     # Build the map.
+     for my $mappedName (@{$mappedNameListRef}) {
+         push @retVal, [$mappedName, $mappedNameHashRef->{$mappedName}];
+     }
+     # Return it.
+     return @retVal;
+ }
+ =head3 _SetupSQL
  Process a list of object names and a filter clause so that they can be used to
  build an SQL statement. This method takes in a reference to a list of object names
-Line 2155
+Line 2671
  A string containing the WHERE clause for the query (without the C<WHERE>) and also
  optionally the C<ORDER BY> and C<LIMIT> clauses.
+ =item matchClause
+ An optional full-text search clause. If specified, it will be inserted at the
+ front of the WHERE clause. It should already be SQL-formatted; that is, the
+ field names should be in the form I<table>C<.>I<fieldName>.
  =item RETURN
  Returns a three-element list. The first element is the SQL statement suffix, beginning
-Line 2167
+Line 2689
  =cut
  sub _SetupSQL {
-     my ($self, $objectNames, $filterClause) = @_;
+     my ($self, $objectNames, $filterClause, $matchClause) = @_;
      # Adjust the list of object names to account for multiple occurrences of the
      # same object. We start with a hash table keyed on object name that will
      # return the object suffix. The first time an object is encountered it will
-Line 2216
+Line 2738
      # FROM name1, name2, ... nameN
      #
      my $suffix = "FROM " . join(', ', @fromList);
+     # Now for the WHERE. First, we need a place for the filter string.
+     my $filterString = "";
+     # We will also keep a list of conditions to add to the WHERE clause in order to link
+     # entities and relationships as well as primary relations to secondary ones.
+     my @joinWhere = ();
      # Check for a filter clause.
      if ($filterClause) {
          # Here we have one, so we convert its field names and add it to the query. First,
          # We create a copy of the filter string we can work with.
-         my $filterString = $filterClause;
+         $filterString = $filterClause;
          # Next, we sort the object names by length. This helps protect us from finding
          # object names inside other object names when we're doing our search and replace.
          my @sortedNames = sort { length($b) - length($a) } @mappedNameList;
-         # We will also keep a list of conditions to add to the WHERE clause in order to link
-         # entities and relationships as well as primary relations to secondary ones.
-         my @joinWhere = ();
          # The final preparatory step is to create a hash table of relation names. The
          # table begins with the relation names already in the SELECT command. We may
          # need to add relations later if there is filtering on a field in a secondary
-Line 2294
+Line 2818
                  }
              }
          }
+     }
          # The next step is to join the objects together. We only need to do this if there
          # is more than one object in the object list. We start with the first object and
          # run through the objects after it. Note also that we make a safety copy of the
-         # list before running through it.
+     # list before running through it, because we shift off the first object before
+     # processing the rest.
          my @mappedObjectList = @mappedNameList;
          my $lastMappedObject = shift @mappedObjectList;
          # Get the join table.
-Line 2326
+Line 2852
          # here is we want the filter clause to be empty if there's no WHERE filter.
          # We'll put the ORDER BY / LIMIT clauses in the following variable.
          my $orderClause = "";
+     # This is only necessary if we have a filter string in which the ORDER BY
+     # and LIMIT clauses can live.
+     if ($filterString) {
          # Locate the ORDER BY or LIMIT verbs (if any). We use a non-greedy
          # operator so that we find the first occurrence of either verb.
          if ($filterString =~ m/^(.*?)\s*(ORDER BY|LIMIT)/g) {
-Line 2334
+Line 2863
              $orderClause = $2 . substr($filterString, $pos);
              $filterString = $1;
          }
-         # Add the filter and the join clauses (if any) to the SELECT command.
+     }
+     # All the things that are supposed to be in the WHERE clause of the
+     # SELECT command need to be put into @joinWhere so we can string them
+     # together. We begin with the match clause. This is important,
+     # because the match clause's parameter mark must precede any parameter
+     # marks in the filter string.
+     if ($matchClause) {
+         push @joinWhere, $matchClause;
+     }
+     # Add the filter string. We put it in parentheses to avoid operator
+     # precedence problems with the match clause or the joins.
          if ($filterString) {
              Trace("Filter string is \"$filterString\".") if T(4);
              push @joinWhere, "($filterString)";
          }
+     # String it all together into a big filter clause.
          if (@joinWhere) {
              $suffix .= " WHERE " . join(' AND ', @joinWhere);
          }
-         # Add the sort or limit clause (if any) to the SELECT command.
+     # Add the sort or limit clause (if any).
          if ($orderClause) {
              $suffix .= " $orderClause";
          }
-     }
      # Return the suffix, the mapped name list, and the mapped name hash.
      return ($suffix, \@mappedNameList, \%mappedNameHash);
  }
- =head3 GetStatementHandle
+ =head3 _GetStatementHandle
  This method will prepare and execute an SQL query, returning the statement handle.
  The main reason for doing this here is so that everybody who does SQL queries gets
-Line 2394
+Line 2933
      return $sth;
  }
- =head3 GetLoadStats
+ =head3 _GetLoadStats
  Return a blank statistics object for use by the load methods.
-Line 2406
+Line 2945
      return Stats->new();
  }
- =head3 GenerateFields
+ =head3 _GenerateFields
  Generate field values from a field structure and store in a specified table. The field names
  are first sorted by pass count, certain pre-defined fields are removed from the list, and
-Line 2480
+Line 3019
      }
  }
- =head3 DumpRelation
+ =head3 _DumpRelation
  Dump the specified relation's to the specified output file in tab-delimited format.
-Line 2530
+Line 3069
      close DTXOUT;
  }
- =head3 GetStructure
+ =head3 _GetStructure
  Get the data structure for a specified entity or relationship.
-Line 2569
+Line 3108
      return $retVal;
  }
- =head3 GetRelationTable
+ =head3 _GetRelationTable
  Get the list of relations for a specified entity or relationship.
-Line 2598
+Line 3139
      return $objectData->{Relations};
  }
- =head3 ValidateFieldNames
+ =head3 _ValidateFieldNames
  Determine whether or not the field names are valid. A description of the problems with the names
  will be written to the standard error output. If there is an error, this method will abort. This is
-Line 2653
+Line 3194
      }
  }
- =head3 LoadRelation
+ =head3 _LoadRelation
  Load a relation from the data in a tab-delimited disk file. The load will only take place if a disk
  file with the same name as the relation exists in the specified directory.
-Line 2713
+Line 3254
      return $retVal;
  }
- =head3 LoadMetaData
+ =head3 _LoadMetaData
  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
-Line 3040
+Line 3581
      return $metadata;
  }
- =head3 SortNeeded
+ =head3 _CreateRelationshipIndex
- C<< my $flag = $erdb->SortNeeded($relationName); >>
- Return TRUE if the specified relation should be sorted during loading to remove duplicate keys,
- else FALSE.
- =over 4
- =item relationName
- Name of the relation to be examined.
- =item RETURN
- Returns TRUE if the relation needs a sort, else FALSE.
- =back
- =cut
- #: Return Type $;
- sub SortNeeded {
-     # Get the parameters.
-     my ($self, $relationName) = @_;
-     # Declare the return variable.
-     my $retVal = 0;
-     # Find out if the relation is a primary entity relation.
-     my $entityTable = $self->{_metaData}->{Entities};
-     if (exists $entityTable->{$relationName}) {
-         my $keyType = $entityTable->{$relationName}->{keyType};
-         Trace("Relation $relationName found in entity table with key type $keyType.") if T(3);
-         # If the key is not a hash string, we must do the sort.
-         if ($keyType ne 'hash-string') {
-             $retVal = 1;
-         }
-     }
-     # Return the result.
-     return $retVal;
- }
- =head3 CreateRelationshipIndex
  Create an index for a relationship's relation.
-Line 3125
+Line 3626
      _AddIndex("idx$relationshipName$indexKey", $relationStructure, $newIndex);
  }
- =head3 AddIndex
+ =head3 _AddIndex
  Add an index to a relation structure.
-Line 3171
+Line 3672
      $relationStructure->{Indexes}->{$indexName} = $newIndex;
  }
- =head3 FixupFields
+ =head3 _FixupFields
  This method fixes the field list for an entity or relationship. It will add the caller-specified
  relation name to fields that do not have a name and set the C<PrettySort> value as specified.
-Line 3209
+Line 3710
          # Here it doesn't, so we create a new one.
          $structure->{Fields} = { };
      } else {
-         # Here we have a field list. Loop through its fields.
+         # Here we have a field list. We need to track the searchable fields, so we
+         # create a list for stashing them.
+         my @textFields = ();
+         # Loop through the fields.
          my $fieldStructures = $structure->{Fields};
          for my $fieldName (keys %{$fieldStructures}) {
              Trace("Processing field $fieldName of $defaultRelationName.") if T(4);
-Line 3223
+Line 3727
                  # The data generator will use the default for the field's type.
                  $fieldData->{DataGen} = { content => $TypeTable{$type}->{dataGen} };
              }
+             # Check for searchability.
+             if ($fieldData->{searchable}) {
+                 # Only allow this for a primary relation.
+                 if ($fieldData->{relation} ne $defaultRelationName) {
+                     Confess("Field $fieldName of $defaultRelationName is in secondary relations and cannot be searchable.");
+                 } else {
+                     push @textFields, $fieldName;
+                 }
+             }
              # Plug in the defaults for the optional data generation parameters.
              Tracer::MergeOptions($fieldData->{DataGen}, { testCount => 1, pass => 0 });
              # Add the PrettySortValue.
              $fieldData->{PrettySort} = (($type eq "text") ? $textPrettySortValue : $prettySortValue);
          }
+         # If there are searchable fields, remember the fact.
+         if (@textFields) {
+             $structure->{searchFields} = \@textFields;
+         }
      }
  }
- =head3 FixName
+ =head3 _FixName
  Fix the incoming field name so that it is a legal SQL column name.
-Line 3260
+Line 3777
      return $fieldName;
  }
- =head3 FixNames
+ =head3 _FixNames
  Fix all the field names in a list.
-Line 3291
+Line 3808
      return @result;
  }
- =head3 AddField
+ =head3 _AddField
  Add a field to a field list.
-Line 3326
+Line 3843
      $fieldList->{$fieldName} = $fieldStructure;
  }
- =head3 ReOrderRelationTable
+ =head3 _ReOrderRelationTable
  This method will take a relation table and re-sort it according to the implicit ordering of the
  C<PrettySort> property. Instead of a hash based on field names, it will return a list of fields.
-Line 3387
+Line 3904
  }
- =head3 IsPrimary
+ =head3 _IsPrimary
  Return TRUE if a specified relation is a primary relation, else FALSE. A relation is primary
  if it has the same name as an entity or relationship.
-Line 3423
+Line 3940
      return $retVal;
  }
- =head3 FindRelation
+ =head3 _FindRelation
  Return the descriptor for the specified relation.
-Line 3454
+Line 3971
  =head2 HTML Documentation Utility Methods
- =head3 ComputeRelationshipSentence
+ =head3 _ComputeRelationshipSentence
  The relationship sentence consists of the relationship name between the names of the
  two related entities and an arity indicator.
-Line 3492
+Line 4009
      return $result;
  }
- =head3 ComputeRelationshipHeading
+ =head3 _ComputeRelationshipHeading
  The relationship heading is the L<relationship sentence|/ComputeRelationshipSentence> with the entity
  names hyperlinked to the appropriate entity sections of the document.
-Line 3529
+Line 4046
      return $result;
  }
- =head3 ShowRelationTable
+ =head3 _ShowRelationTable
  Generate the HTML string for a particular relation. The relation's data will be formatted as an HTML
  table with three columns-- the field name, the field type, and the field description.
-Line 3590
+Line 4107
      $htmlString .= "</ul>\n";
  }
- =head3 OpenFieldTable
+ =head3 _OpenFieldTable
  This method creates the header string for the field table generated by L</ShowMetaData>.
-Line 3615
+Line 4132
      return _OpenTable($tablename, 'Field', 'Type', 'Description');
  }
- =head3 OpenTable
+ =head3 _OpenTable
  This method creates the header string for an HTML table.
-Line 3655
+Line 4172
      return $htmlString;
  }
- =head3 CloseTable
+ =head3 _CloseTable
  This method returns the HTML for closing a table.
-Line 3667
+Line 4184
      return "</table></p>\n";
  }
- =head3 ShowField
+ =head3 _ShowField
  This method returns the HTML for displaying a row of field information in a field table.
-Line 3702
+Line 4219
      return $htmlString;
  }
- =head3 HTMLNote
+ =head3 _HTMLNote
  Convert a note or comment to HTML by replacing some bulletin-board codes with HTML. The codes
  supported are C<[b]> for B<bold>, C<[i]> for I<italics>, and C<[p]> for a new paragraph.

 Legend:



Removed from v.1.47
 


changed lines


 
Added in v.1.70
 Legend:



Removed from v.1.47
 


changed lines


 
Added in v.1.70
-Removed from v.1.47
+Added in v.1.70

MCS Webmaster	ViewVC Help
Powered by ViewVC 1.0.3