[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.55, Sat Jun 24 23:49:10 2006 UTC revision 1.70, Fri Oct 13 21:45:11 2006 UTC
# Line 91  Line 91 
91    
92  32-bit signed integer  32-bit signed integer
93    
94    =item counter
95    
96    32-bit unsigned integer
97    
98  =item date  =item date
99    
100  64-bit unsigned integer, representing a PERL date/time value  64-bit unsigned integer, representing a PERL date/time value
# Line 186  Line 190 
190    
191  Name of the field. The field name should contain only letters, digits, and hyphens (C<->),  Name of the field. The field name should contain only letters, digits, and hyphens (C<->),
192  and the first character should be a letter. Most underlying databases are case-insensitive  and the first character should be a letter. Most underlying databases are case-insensitive
193  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,
194    the name C<search-relevance> has special meaning for full-text searches and should not be
195    used as a field name.
196    
197  =item type  =item type
198    
# Line 205  Line 211 
211  entity, the fields without a relation attribute are said to belong to the  entity, the fields without a relation attribute are said to belong to the
212  I<primary relation>. This relation has the same name as the entity itself.  I<primary relation>. This relation has the same name as the entity itself.
213    
214    =item searchable
215    
216    If specified, then the field is a candidate for full-text searching. A single full-text
217    index will be created for each relation with at least one searchable field in it.
218    For best results, this option should only be used for string or text fields.
219    
220  =back  =back
221    
222  =head3 Indexes  =head3 Indexes
# Line 318  Line 330 
330  # "maxLen" is the maximum permissible length of the incoming string data used to populate a field  # "maxLen" is the maximum permissible length of the incoming string data used to populate a field
331  # of the specified type. "dataGen" is PERL string that will be evaluated if no test data generation  # of the specified type. "dataGen" is PERL string that will be evaluated if no test data generation
332  # string is specified in the field definition. "avgLen" is the average byte length for estimating  # string is specified in the field definition. "avgLen" is the average byte length for estimating
333  # record sizes.  # record sizes. "sort" is the key modifier for the sort command.
334  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')" },
335                    int =>     { sqlType => 'INTEGER',            maxLen => 20,           avgLen =>   4, dataGen => "IntGen(0, 99999999)" },                    int =>     { sqlType => 'INTEGER',            maxLen => 20,           avgLen =>   4, sort => "n", dataGen => "IntGen(0, 99999999)" },
336                    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)" },
337                    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))" },
338                    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))" },
339                    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))" },
340                    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)" },
341                      boolean => { sqlType => 'SMALLINT',           maxLen => 1,            avgLen =>   1, sort => "n", dataGen => "IntGen(0, 1)" },
342                   'hash-string' =>                   'hash-string' =>
343                               { sqlType => 'VARCHAR(22)',        maxLen => 22,           avgLen =>  22, dataGen => "SringGen(22)" },                               { sqlType => 'VARCHAR(22)',        maxLen => 22,           avgLen =>  22, sort => "",  dataGen => "SringGen(22)" },
344                   'id-string' =>                   'id-string' =>
345                               { sqlType => 'VARCHAR(25)',        maxLen => 25,           avgLen =>  25, dataGen => "SringGen(22)" },                               { sqlType => 'VARCHAR(25)',        maxLen => 25,           avgLen =>  25, sort => "",  dataGen => "SringGen(22)" },
346                   'key-string' =>                   'key-string' =>
347                               { 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))" },
348                   'name-string' =>                   'name-string' =>
349                               { 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))" },
350                   'medium-string' =>                   'medium-string' =>
351                               { 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))" },
352                  );                  );
353    
354  # Table translating arities into natural language.  # Table translating arities into natural language.
# Line 684  Line 697 
697      Trace("Creating table $relationName: $fieldThing") if T(2);      Trace("Creating table $relationName: $fieldThing") if T(2);
698      $dbh->create_table(tbl => $relationName, flds => $fieldThing, estimates => $estimation);      $dbh->create_table(tbl => $relationName, flds => $fieldThing, estimates => $estimation);
699      Trace("Relation $relationName created in database.") if T(2);      Trace("Relation $relationName created in database.") if T(2);
700      # 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
701        # index will not be built until the table has been loaded.
702      if ($indexFlag) {      if ($indexFlag) {
703          $self->CreateIndex($relationName);          $self->CreateIndex($relationName);
704      }      }
# Line 844  Line 858 
858          my @fieldList = _FixNames(@{$indexData->{IndexFields}});          my @fieldList = _FixNames(@{$indexData->{IndexFields}});
859          my $flds = join(', ', @fieldList);          my $flds = join(', ', @fieldList);
860          # Get the index's uniqueness flag.          # Get the index's uniqueness flag.
861          my $unique = (exists $indexData->{Unique} ? $indexData->{Unique} : 'false');          my $unique = (exists $indexData->{Unique} ? 'unique' : undef);
862          # Create the index.          # Create the index.
863          my $rv = $dbh->create_index(idx => $indexName, tbl => $relationName,          my $rv = $dbh->create_index(idx => $indexName, tbl => $relationName,
864                                      flds => $flds, unique => $unique);                                      flds => $flds, kind => $unique);
865          if ($rv) {          if ($rv) {
866              Trace("Index created: $indexName for $relationName ($flds)") if T(1);              Trace("Index created: $indexName for $relationName ($flds)") if T(1);
867          } else {          } else {
# Line 1094  Line 1108 
1108      return $retVal;      return $retVal;
1109  }  }
1110    
1111    =head3 Search
1112    
1113    C<< my $query = $erdb->Search($searchExpression, $idx, \@objectNames, $filterClause, \@params); >>
1114    
1115    Perform a full text search with filtering. The search will be against a specified object
1116    in the object name list. That object will get an extra field containing the search
1117    relevance. Note that except for the search expression, the parameters of this method are
1118    the same as those for L</Get> and follow the same rules.
1119    
1120    =over 4
1121    
1122    =item searchExpression
1123    
1124    Boolean search expression for the text fields of the target object.
1125    
1126    =item idx
1127    
1128    Index in the I<$objectNames> list of the table to be searched in full-text mode.
1129    
1130    =item objectNames
1131    
1132    List containing the names of the entity and relationship objects to be retrieved.
1133    
1134    =item filterClause
1135    
1136    WHERE clause (without the WHERE) to be used to filter and sort the query. The WHERE clause can
1137    be parameterized with parameter markers (C<?>). Each field used in the WHERE clause must be
1138    specified in the standard form B<I<objectName>(I<fieldName>)>. Any parameters specified
1139    in the filter clause should be added to the parameter list as additional parameters. The
1140    fields in a filter clause can come from primary entity relations, relationship relations,
1141    or secondary entity relations; however, all of the entities and relationships involved must
1142    be included in the list of object names.
1143    
1144    =item params
1145    
1146    Reference to a list of parameter values to be substituted into the filter clause.
1147    
1148    =item RETURN
1149    
1150    Returns a query object for the specified search.
1151    
1152    =back
1153    
1154    =cut
1155    
1156    sub Search {
1157        # Get the parameters.
1158        my ($self, $searchExpression, $idx, $objectNames, $filterClause, $params) = @_;
1159        # Declare the return variable.
1160        my $retVal;
1161        # Create a safety copy of the parameter list.
1162        my @myParams = @{$params};
1163        # Get the first object's structure so we have access to the searchable fields.
1164        my $object1Name = $objectNames->[$idx];
1165        my $object1Structure = $self->_GetStructure($object1Name);
1166        # Get the field list.
1167        if (! exists $object1Structure->{searchFields}) {
1168            Confess("No searchable index for $object1Name.");
1169        } else {
1170            # Get the field list.
1171            my @fields = @{$object1Structure->{searchFields}};
1172            # We need two match expressions, one for the filter clause and one in the
1173            # query itself. Both will use a parameter mark, so we need to push the
1174            # search expression onto the front of the parameter list twice.
1175            unshift @myParams, $searchExpression, $searchExpression;
1176            # Build the match expression.
1177            my @matchFilterFields = map { "$object1Name." . _FixName($_) } @fields;
1178            my $matchClause = "MATCH (" . join(", ", @matchFilterFields) . ") AGAINST (? IN BOOLEAN MODE)";
1179            # Process the SQL stuff.
1180            my ($suffix, $mappedNameListRef, $mappedNameHashRef) =
1181                $self->_SetupSQL($objectNames, $filterClause, $matchClause);
1182            # Create the query. Note that the match clause is inserted at the front of
1183            # the select fields.
1184            my $command = "SELECT DISTINCT $matchClause, " . join(".*, ", @{$mappedNameListRef}) .
1185                ".* $suffix";
1186            my $sth = $self->_GetStatementHandle($command, \@myParams);
1187            # Now we create the relation map, which enables DBQuery to determine the order, name
1188            # and mapped name for each object in the query.
1189            my @relationMap = _RelationMap($mappedNameHashRef, $mappedNameListRef);
1190            # Return the statement object.
1191            $retVal = DBQuery::_new($self, $sth, \@relationMap, $object1Name);
1192        }
1193        return $retVal;
1194    }
1195    
1196  =head3 GetFlat  =head3 GetFlat
1197    
1198  C<< my @list = $erdb->GetFlat(\@objectNames, $filterClause, \@parameterList, $field); >>  C<< my @list = $erdb->GetFlat(\@objectNames, $filterClause, \@parameterList, $field); >>
# Line 1315  Line 1414 
1414      return $retVal;      return $retVal;
1415  }  }
1416    
1417    =head3 SortNeeded
1418    
1419    C<< my $parms = $erdb->SortNeeded($relationName); >>
1420    
1421    Return the pipe command for the sort that should be applied to the specified
1422    relation when creating the load file.
1423    
1424    For example, if the load file should be sorted ascending by the first
1425    field, this method would return
1426    
1427        sort -k1 -t"\t"
1428    
1429    If the first field is numeric, the method would return
1430    
1431        sort -k1n -t"\t"
1432    
1433    Unfortunately, due to a bug in the C<sort> command, we cannot eliminate duplicate
1434    keys using a sort.
1435    
1436    =over 4
1437    
1438    =item relationName
1439    
1440    Name of the relation to be examined.
1441    
1442    =item
1443    
1444    Returns the sort command to use for sorting the relation, suitable for piping.
1445    
1446    =back
1447    
1448    =cut
1449    #: Return Type $;
1450    sub SortNeeded {
1451        # Get the parameters.
1452        my ($self, $relationName) = @_;
1453        # Declare a descriptor to hold the names of the key fields.
1454        my @keyNames = ();
1455        # Get the relation structure.
1456        my $relationData = $self->_FindRelation($relationName);
1457        # Find out if the relation is a primary entity relation,
1458        # a relationship relation, or a secondary entity relation.
1459        my $entityTable = $self->{_metaData}->{Entities};
1460        my $relationshipTable = $self->{_metaData}->{Relationships};
1461        if (exists $entityTable->{$relationName}) {
1462            # Here we have a primary entity relation.
1463            push @keyNames, "id";
1464        } elsif (exists $relationshipTable->{$relationName}) {
1465            # Here we have a relationship. We sort using the FROM index.
1466            my $relationshipData = $relationshipTable->{$relationName};
1467            my $index = $relationData->{Indexes}->{"idx${relationName}From"};
1468            push @keyNames, @{$index->{IndexFields}};
1469        } else {
1470            # Here we have a secondary entity relation, so we have a sort on the ID field.
1471            push @keyNames, "id";
1472        }
1473        # Now we parse the key names into sort parameters. First, we prime the return
1474        # string.
1475        my $retVal = "sort -t\"\t\" ";
1476        # Get the relation's field list.
1477        my @fields = @{$relationData->{Fields}};
1478        # Loop through the keys.
1479        for my $keyData (@keyNames) {
1480            # Get the key and the ordering.
1481            my ($keyName, $ordering);
1482            if ($keyData =~ /^([^ ]+) DESC/) {
1483                ($keyName, $ordering) = ($1, "descending");
1484            } else {
1485                ($keyName, $ordering) = ($keyData, "ascending");
1486            }
1487            # Find the key's position and type.
1488            my $fieldSpec;
1489            for (my $i = 0; $i <= $#fields && ! $fieldSpec; $i++) {
1490                my $thisField = $fields[$i];
1491                if ($thisField->{name} eq $keyName) {
1492                    # Get the sort modifier for this field type. The modifier
1493                    # decides whether we're using a character, numeric, or
1494                    # floating-point sort.
1495                    my $modifier = $TypeTable{$thisField->{type}}->{sort};
1496                    # If the index is descending for this field, denote we want
1497                    # to reverse the sort order on this field.
1498                    if ($ordering eq 'descending') {
1499                        $modifier .= "r";
1500                    }
1501                    # Store the position and modifier into the field spec, which
1502                    # will stop the inner loop. Note that the field number is
1503                    # 1-based in the sort command, so we have to increment the
1504                    # index.
1505                    $fieldSpec = ($i + 1) . $modifier;
1506                }
1507            }
1508            # Add this field to the sort command.
1509            $retVal .= " -k$fieldSpec";
1510        }
1511        # Return the result.
1512        return $retVal;
1513    }
1514    
1515  =head3 GetList  =head3 GetList
1516    
1517  C<< my @dbObjects = $erdb->GetList(\@objectNames, $filterClause, \@params); >>  C<< my @dbObjects = $erdb->GetList(\@objectNames, $filterClause, \@params); >>
# Line 1431  Line 1628 
1628  sub GetCount {  sub GetCount {
1629      # Get the parameters.      # Get the parameters.
1630      my ($self, $objectNames, $filter, $params) = @_;      my ($self, $objectNames, $filter, $params) = @_;
1631        # Insure the params argument is an array reference if the caller left it off.
1632        if (! defined($params)) {
1633            $params = [];
1634        }
1635      # Declare the return variable.      # Declare the return variable.
1636      my $retVal;      my $retVal;
1637      # Find out if we're counting an entity or a relationship.      # Find out if we're counting an entity or a relationship.
# Line 1623  Line 1824 
1824  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
1825  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>.
1826    
1827  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'}); >>
1828    
1829  =over 4  =over 4
1830    
# Line 1823  Line 2024 
2024          my $size = -s $fileName;          my $size = -s $fileName;
2025          Trace("$size bytes loaded into $relationName.") if T(2);          Trace("$size bytes loaded into $relationName.") if T(2);
2026          # If we're rebuilding, we need to create the table indexes.          # If we're rebuilding, we need to create the table indexes.
2027          if ($truncateFlag && ! $dbh->{_preIndex}) {          if ($truncateFlag) {
2028                # Indexes are created here for PostGres. For PostGres, indexes are
2029                # best built at the end. For MySQL, the reverse is true.
2030                if (! $dbh->{_preIndex}) {
2031              eval {              eval {
2032                  $self->CreateIndex($relationName);                  $self->CreateIndex($relationName);
2033              };              };
# Line 1831  Line 2035 
2035                  $retVal->AddMessage($@);                  $retVal->AddMessage($@);
2036              }              }
2037          }          }
2038                # The full-text index (if any) is always built last, even for MySQL.
2039                # First we need to see if this table has a full-text index. Only
2040                # primary relations are allowed that privilege.
2041                if ($self->_IsPrimary($relationName)) {
2042                    # Get the relation's entity/relationship structure.
2043                    my $structure = $self->_GetStructure($relationName);
2044                    # Check for a searchable fields list.
2045                    if (exists $structure->{searchFields}) {
2046                        # Here we know that we need to create a full-text search index.
2047                        # Get an SQL-formatted field name list.
2048                        my $fields = join(", ", $self->_FixNames(@{$structure->{searchFields}}));
2049                        # Create the index.
2050                        $dbh->create_index(tbl => $relationName, idx => "search_idx_$relationName",
2051                                           flds => $fields, kind => 'fulltext');
2052                    }
2053                }
2054            }
2055      }      }
2056      # Analyze the table to improve performance.      # Analyze the table to improve performance.
2057        Trace("Analyzing and compacting $relationName.") if T(3);
2058      $dbh->vacuum_it($relationName);      $dbh->vacuum_it($relationName);
2059        Trace("$relationName load completed.") if T(3);
2060      # Return the statistics.      # Return the statistics.
2061      return $retVal;      return $retVal;
2062  }  }
# Line 1932  Line 2155 
2155      return $retVal;      return $retVal;
2156  }  }
2157    
2158    =head3 GetChoices
2159    
2160    C<< my @values = $erdb->GetChoices($entityName, $fieldName); >>
2161    
2162    Return a list of all the values for the specified field that are represented in the
2163    specified entity.
2164    
2165    Note that if the field is not indexed, then this will be a very slow operation.
2166    
2167    =over 4
2168    
2169    =item entityName
2170    
2171    Name of an entity in the database.
2172    
2173    =item fieldName
2174    
2175    Name of a field belonging to the entity. This is a raw field name without
2176    the standard parenthesized notation used in most calls.
2177    
2178    =item RETURN
2179    
2180    Returns a list of the distinct values for the specified field in the database.
2181    
2182    =back
2183    
2184    =cut
2185    
2186    sub GetChoices {
2187        # Get the parameters.
2188        my ($self, $entityName, $fieldName) = @_;
2189        # Declare the return variable.
2190        my @retVal;
2191        # Get the entity data structure.
2192        my $entityData = $self->_GetStructure($entityName);
2193        # Get the field.
2194        my $fieldHash = $entityData->{Fields};
2195        if (! exists $fieldHash->{$fieldName}) {
2196            Confess("$fieldName not found in $entityName.");
2197        } else {
2198            # Get the name of the relation containing the field.
2199            my $relation = $fieldHash->{$fieldName}->{relation};
2200            # Fix up the field name.
2201            my $realName = _FixName($fieldName);
2202            # Get the database handle.
2203            my $dbh = $self->{_dbh};
2204            # Query the database.
2205            my $results = $dbh->SQL("SELECT DISTINCT $realName FROM $relation");
2206            # Clean the results. They are stored as a list of lists, and we just want the one list.
2207            @retVal = sort map { $_->[0] } @{$results};
2208        }
2209        # Return the result.
2210        return @retVal;
2211    }
2212    
2213  =head3 GetEntityValues  =head3 GetEntityValues
2214    
2215  C<< my @values = $erdb->GetEntityValues($entityType, $ID, \@fields); >>  C<< my @values = $erdb->GetEntityValues($entityType, $ID, \@fields); >>
2216    
2217  Return a list of values from a specified entity instance.  Return a list of values from a specified entity instance. If the entity instance
2218    does not exist, an empty list is returned.
2219    
2220  =over 4  =over 4
2221    
# Line 2326  Line 2605 
2605    
2606  =head2 Internal Utility Methods  =head2 Internal Utility Methods
2607    
2608  =head3 SetupSQL  =head3 _RelationMap
2609    
2610    C<< my @relationMap = _RelationMap($mappedNameHashRef, $mappedNameListRef); >>
2611    
2612    Create the relation map for an SQL query. The relation map is used by B<DBObject>
2613    to determine how to interpret the results of the query.
2614    
2615    =over 4
2616    
2617    =item mappedNameHashRef
2618    
2619    Reference to a hash that maps modified object names to real object names.
2620    
2621    =item mappedNameListRef
2622    
2623    Reference to a list of modified object names in the order they appear in the
2624    SELECT list.
2625    
2626    =item RETURN
2627    
2628    Returns a list of 2-tuples. Each tuple consists of an object name as used in the
2629    query followed by the actual name of that object. This enables the B<DBObject> to
2630    determine the order of the tables in the query and which object name belongs to each
2631    mapped object name. Most of the time these two values are the same; however, if a
2632    relation occurs twice in the query, the relation name in the field list and WHERE
2633    clause will use a mapped name (generally the actual relation name with a numeric
2634    suffix) that does not match the actual relation name.
2635    
2636    =back
2637    
2638    =cut
2639    
2640    sub _RelationMap {
2641        # Get the parameters.
2642        my ($mappedNameHashRef, $mappedNameListRef) = @_;
2643        # Declare the return variable.
2644        my @retVal = ();
2645        # Build the map.
2646        for my $mappedName (@{$mappedNameListRef}) {
2647            push @retVal, [$mappedName, $mappedNameHashRef->{$mappedName}];
2648        }
2649        # Return it.
2650        return @retVal;
2651    }
2652    
2653    
2654    =head3 _SetupSQL
2655    
2656  Process a list of object names and a filter clause so that they can be used to  Process a list of object names and a filter clause so that they can be used to
2657  build an SQL statement. This method takes in a reference to a list of object names  build an SQL statement. This method takes in a reference to a list of object names
# Line 2346  Line 2671 
2671  A string containing the WHERE clause for the query (without the C<WHERE>) and also  A string containing the WHERE clause for the query (without the C<WHERE>) and also
2672  optionally the C<ORDER BY> and C<LIMIT> clauses.  optionally the C<ORDER BY> and C<LIMIT> clauses.
2673    
2674    =item matchClause
2675    
2676    An optional full-text search clause. If specified, it will be inserted at the
2677    front of the WHERE clause. It should already be SQL-formatted; that is, the
2678    field names should be in the form I<table>C<.>I<fieldName>.
2679    
2680  =item RETURN  =item RETURN
2681    
2682  Returns a three-element list. The first element is the SQL statement suffix, beginning  Returns a three-element list. The first element is the SQL statement suffix, beginning
# Line 2358  Line 2689 
2689  =cut  =cut
2690    
2691  sub _SetupSQL {  sub _SetupSQL {
2692      my ($self, $objectNames, $filterClause) = @_;      my ($self, $objectNames, $filterClause, $matchClause) = @_;
2693      # Adjust the list of object names to account for multiple occurrences of the      # Adjust the list of object names to account for multiple occurrences of the
2694      # same object. We start with a hash table keyed on object name that will      # same object. We start with a hash table keyed on object name that will
2695      # return the object suffix. The first time an object is encountered it will      # return the object suffix. The first time an object is encountered it will
# Line 2407  Line 2738 
2738      # FROM name1, name2, ... nameN      # FROM name1, name2, ... nameN
2739      #      #
2740      my $suffix = "FROM " . join(', ', @fromList);      my $suffix = "FROM " . join(', ', @fromList);
2741        # Now for the WHERE. First, we need a place for the filter string.
2742        my $filterString = "";
2743        # We will also keep a list of conditions to add to the WHERE clause in order to link
2744        # entities and relationships as well as primary relations to secondary ones.
2745        my @joinWhere = ();
2746      # Check for a filter clause.      # Check for a filter clause.
2747      if ($filterClause) {      if ($filterClause) {
2748          # Here we have one, so we convert its field names and add it to the query. First,          # Here we have one, so we convert its field names and add it to the query. First,
2749          # We create a copy of the filter string we can work with.          # We create a copy of the filter string we can work with.
2750          my $filterString = $filterClause;          $filterString = $filterClause;
2751          # Next, we sort the object names by length. This helps protect us from finding          # Next, we sort the object names by length. This helps protect us from finding
2752          # object names inside other object names when we're doing our search and replace.          # object names inside other object names when we're doing our search and replace.
2753          my @sortedNames = sort { length($b) - length($a) } @mappedNameList;          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 = ();  
2754          # The final preparatory step is to create a hash table of relation names. The          # The final preparatory step is to create a hash table of relation names. The
2755          # table begins with the relation names already in the SELECT command. We may          # table begins with the relation names already in the SELECT command. We may
2756          # need to add relations later if there is filtering on a field in a secondary          # need to add relations later if there is filtering on a field in a secondary
# Line 2485  Line 2818 
2818                  }                  }
2819              }              }
2820          }          }
2821        }
2822          # The next step is to join the objects together. We only need to do this if there          # The next step is to join the objects together. We only need to do this if there
2823          # is more than one object in the object list. We start with the first object and          # is more than one object in the object list. We start with the first object and
2824          # run through the objects after it. Note also that we make a safety copy of the          # run through the objects after it. Note also that we make a safety copy of the
2825          # list before running through it.      # list before running through it, because we shift off the first object before
2826        # processing the rest.
2827          my @mappedObjectList = @mappedNameList;          my @mappedObjectList = @mappedNameList;
2828          my $lastMappedObject = shift @mappedObjectList;          my $lastMappedObject = shift @mappedObjectList;
2829          # Get the join table.          # Get the join table.
# Line 2517  Line 2852 
2852          # here is we want the filter clause to be empty if there's no WHERE filter.          # here is we want the filter clause to be empty if there's no WHERE filter.
2853          # We'll put the ORDER BY / LIMIT clauses in the following variable.          # We'll put the ORDER BY / LIMIT clauses in the following variable.
2854          my $orderClause = "";          my $orderClause = "";
2855        # This is only necessary if we have a filter string in which the ORDER BY
2856        # and LIMIT clauses can live.
2857        if ($filterString) {
2858          # Locate the ORDER BY or LIMIT verbs (if any). We use a non-greedy          # Locate the ORDER BY or LIMIT verbs (if any). We use a non-greedy
2859          # operator so that we find the first occurrence of either verb.          # operator so that we find the first occurrence of either verb.
2860          if ($filterString =~ m/^(.*?)\s*(ORDER BY|LIMIT)/g) {          if ($filterString =~ m/^(.*?)\s*(ORDER BY|LIMIT)/g) {
# Line 2525  Line 2863 
2863              $orderClause = $2 . substr($filterString, $pos);              $orderClause = $2 . substr($filterString, $pos);
2864              $filterString = $1;              $filterString = $1;
2865          }          }
2866          # Add the filter and the join clauses (if any) to the SELECT command.      }
2867        # All the things that are supposed to be in the WHERE clause of the
2868        # SELECT command need to be put into @joinWhere so we can string them
2869        # together. We begin with the match clause. This is important,
2870        # because the match clause's parameter mark must precede any parameter
2871        # marks in the filter string.
2872        if ($matchClause) {
2873            push @joinWhere, $matchClause;
2874        }
2875        # Add the filter string. We put it in parentheses to avoid operator
2876        # precedence problems with the match clause or the joins.
2877          if ($filterString) {          if ($filterString) {
2878              Trace("Filter string is \"$filterString\".") if T(4);              Trace("Filter string is \"$filterString\".") if T(4);
2879              push @joinWhere, "($filterString)";              push @joinWhere, "($filterString)";
2880          }          }
2881        # String it all together into a big filter clause.
2882          if (@joinWhere) {          if (@joinWhere) {
2883              $suffix .= " WHERE " . join(' AND ', @joinWhere);              $suffix .= " WHERE " . join(' AND ', @joinWhere);
2884          }          }
2885          # Add the sort or limit clause (if any) to the SELECT command.      # Add the sort or limit clause (if any).
2886          if ($orderClause) {          if ($orderClause) {
2887              $suffix .= " $orderClause";              $suffix .= " $orderClause";
2888          }          }
     }  
2889      # Return the suffix, the mapped name list, and the mapped name hash.      # Return the suffix, the mapped name list, and the mapped name hash.
2890      return ($suffix, \@mappedNameList, \%mappedNameHash);      return ($suffix, \@mappedNameList, \%mappedNameHash);
2891  }  }
2892    
2893  =head3 GetStatementHandle  =head3 _GetStatementHandle
2894    
2895  This method will prepare and execute an SQL query, returning the statement handle.  This method will prepare and execute an SQL query, returning the statement handle.
2896  The main reason for doing this here is so that everybody who does SQL queries gets  The main reason for doing this here is so that everybody who does SQL queries gets
# Line 2585  Line 2933 
2933      return $sth;      return $sth;
2934  }  }
2935    
2936  =head3 GetLoadStats  =head3 _GetLoadStats
2937    
2938  Return a blank statistics object for use by the load methods.  Return a blank statistics object for use by the load methods.
2939    
# Line 2597  Line 2945 
2945      return Stats->new();      return Stats->new();
2946  }  }
2947    
2948  =head3 GenerateFields  =head3 _GenerateFields
2949    
2950  Generate field values from a field structure and store in a specified table. The field names  Generate field values from a field structure and store in a specified table. The field names
2951  are first sorted by pass count, certain pre-defined fields are removed from the list, and  are first sorted by pass count, certain pre-defined fields are removed from the list, and
# Line 2671  Line 3019 
3019      }      }
3020  }  }
3021    
3022  =head3 DumpRelation  =head3 _DumpRelation
3023    
3024  Dump the specified relation's to the specified output file in tab-delimited format.  Dump the specified relation's to the specified output file in tab-delimited format.
3025    
# Line 2721  Line 3069 
3069      close DTXOUT;      close DTXOUT;
3070  }  }
3071    
3072  =head3 GetStructure  =head3 _GetStructure
3073    
3074  Get the data structure for a specified entity or relationship.  Get the data structure for a specified entity or relationship.
3075    
# Line 2760  Line 3108 
3108      return $retVal;      return $retVal;
3109  }  }
3110    
3111  =head3 GetRelationTable  
3112    
3113    =head3 _GetRelationTable
3114    
3115  Get the list of relations for a specified entity or relationship.  Get the list of relations for a specified entity or relationship.
3116    
# Line 2789  Line 3139 
3139      return $objectData->{Relations};      return $objectData->{Relations};
3140  }  }
3141    
3142  =head3 ValidateFieldNames  =head3 _ValidateFieldNames
3143    
3144  Determine whether or not the field names are valid. A description of the problems with the names  Determine whether or not the field names are valid. A description of the problems with the names
3145  will be written to the standard error output. If there is an error, this method will abort. This is  will be written to the standard error output. If there is an error, this method will abort. This is
# Line 2844  Line 3194 
3194      }      }
3195  }  }
3196    
3197  =head3 LoadRelation  =head3 _LoadRelation
3198    
3199  Load a relation from the data in a tab-delimited disk file. The load will only take place if a disk  Load a relation from the data in a tab-delimited disk file. The load will only take place if a disk
3200  file with the same name as the relation exists in the specified directory.  file with the same name as the relation exists in the specified directory.
# Line 2904  Line 3254 
3254      return $retVal;      return $retVal;
3255  }  }
3256    
3257  =head3 LoadMetaData  =head3 _LoadMetaData
3258    
3259  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.
3260  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 3231  Line 3581 
3581      return $metadata;      return $metadata;
3582  }  }
3583    
3584  =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  
3585    
3586  Create an index for a relationship's relation.  Create an index for a relationship's relation.
3587    
# Line 3316  Line 3626 
3626      _AddIndex("idx$relationshipName$indexKey", $relationStructure, $newIndex);      _AddIndex("idx$relationshipName$indexKey", $relationStructure, $newIndex);
3627  }  }
3628    
3629  =head3 AddIndex  =head3 _AddIndex
3630    
3631  Add an index to a relation structure.  Add an index to a relation structure.
3632    
# Line 3362  Line 3672 
3672      $relationStructure->{Indexes}->{$indexName} = $newIndex;      $relationStructure->{Indexes}->{$indexName} = $newIndex;
3673  }  }
3674    
3675  =head3 FixupFields  =head3 _FixupFields
3676    
3677  This method fixes the field list for an entity or relationship. It will add the caller-specified  This method fixes the field list for an entity or relationship. It will add the caller-specified
3678  relation name to fields that do not have a name and set the C<PrettySort> value as specified.  relation name to fields that do not have a name and set the C<PrettySort> value as specified.
# Line 3400  Line 3710 
3710          # Here it doesn't, so we create a new one.          # Here it doesn't, so we create a new one.
3711          $structure->{Fields} = { };          $structure->{Fields} = { };
3712      } else {      } else {
3713          # 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
3714            # create a list for stashing them.
3715            my @textFields = ();
3716            # Loop through the fields.
3717          my $fieldStructures = $structure->{Fields};          my $fieldStructures = $structure->{Fields};
3718          for my $fieldName (keys %{$fieldStructures}) {          for my $fieldName (keys %{$fieldStructures}) {
3719              Trace("Processing field $fieldName of $defaultRelationName.") if T(4);              Trace("Processing field $fieldName of $defaultRelationName.") if T(4);
# Line 3414  Line 3727 
3727                  # The data generator will use the default for the field's type.                  # The data generator will use the default for the field's type.
3728                  $fieldData->{DataGen} = { content => $TypeTable{$type}->{dataGen} };                  $fieldData->{DataGen} = { content => $TypeTable{$type}->{dataGen} };
3729              }              }
3730                # Check for searchability.
3731                if ($fieldData->{searchable}) {
3732                    # Only allow this for a primary relation.
3733                    if ($fieldData->{relation} ne $defaultRelationName) {
3734                        Confess("Field $fieldName of $defaultRelationName is in secondary relations and cannot be searchable.");
3735                    } else {
3736                        push @textFields, $fieldName;
3737                    }
3738                }
3739              # Plug in the defaults for the optional data generation parameters.              # Plug in the defaults for the optional data generation parameters.
3740              Tracer::MergeOptions($fieldData->{DataGen}, { testCount => 1, pass => 0 });              Tracer::MergeOptions($fieldData->{DataGen}, { testCount => 1, pass => 0 });
3741              # Add the PrettySortValue.              # Add the PrettySortValue.
3742              $fieldData->{PrettySort} = (($type eq "text") ? $textPrettySortValue : $prettySortValue);              $fieldData->{PrettySort} = (($type eq "text") ? $textPrettySortValue : $prettySortValue);
3743          }          }
3744            # If there are searchable fields, remember the fact.
3745            if (@textFields) {
3746                $structure->{searchFields} = \@textFields;
3747            }
3748      }      }
3749  }  }
3750    
3751  =head3 FixName  =head3 _FixName
3752    
3753  Fix the incoming field name so that it is a legal SQL column name.  Fix the incoming field name so that it is a legal SQL column name.
3754    
# Line 3451  Line 3777 
3777      return $fieldName;      return $fieldName;
3778  }  }
3779    
3780  =head3 FixNames  =head3 _FixNames
3781    
3782  Fix all the field names in a list.  Fix all the field names in a list.
3783    
# Line 3482  Line 3808 
3808      return @result;      return @result;
3809  }  }
3810    
3811  =head3 AddField  =head3 _AddField
3812    
3813  Add a field to a field list.  Add a field to a field list.
3814    
# Line 3517  Line 3843 
3843      $fieldList->{$fieldName} = $fieldStructure;      $fieldList->{$fieldName} = $fieldStructure;
3844  }  }
3845    
3846  =head3 ReOrderRelationTable  =head3 _ReOrderRelationTable
3847    
3848  This method will take a relation table and re-sort it according to the implicit ordering of the  This method will take a relation table and re-sort it according to the implicit ordering of the
3849  C<PrettySort> property. Instead of a hash based on field names, it will return a list of fields.  C<PrettySort> property. Instead of a hash based on field names, it will return a list of fields.
# Line 3578  Line 3904 
3904    
3905  }  }
3906    
3907  =head3 IsPrimary  =head3 _IsPrimary
3908    
3909  Return TRUE if a specified relation is a primary relation, else FALSE. A relation is primary  Return TRUE if a specified relation is a primary relation, else FALSE. A relation is primary
3910  if it has the same name as an entity or relationship.  if it has the same name as an entity or relationship.
# Line 3614  Line 3940 
3940      return $retVal;      return $retVal;
3941  }  }
3942    
3943  =head3 FindRelation  =head3 _FindRelation
3944    
3945  Return the descriptor for the specified relation.  Return the descriptor for the specified relation.
3946    
# Line 3645  Line 3971 
3971    
3972  =head2 HTML Documentation Utility Methods  =head2 HTML Documentation Utility Methods
3973    
3974  =head3 ComputeRelationshipSentence  =head3 _ComputeRelationshipSentence
3975    
3976  The relationship sentence consists of the relationship name between the names of the  The relationship sentence consists of the relationship name between the names of the
3977  two related entities and an arity indicator.  two related entities and an arity indicator.
# Line 3683  Line 4009 
4009      return $result;      return $result;
4010  }  }
4011    
4012  =head3 ComputeRelationshipHeading  =head3 _ComputeRelationshipHeading
4013    
4014  The relationship heading is the L<relationship sentence|/ComputeRelationshipSentence> with the entity  The relationship heading is the L<relationship sentence|/ComputeRelationshipSentence> with the entity
4015  names hyperlinked to the appropriate entity sections of the document.  names hyperlinked to the appropriate entity sections of the document.
# Line 3720  Line 4046 
4046      return $result;      return $result;
4047  }  }
4048    
4049  =head3 ShowRelationTable  =head3 _ShowRelationTable
4050    
4051  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
4052  table with three columns-- the field name, the field type, and the field description.  table with three columns-- the field name, the field type, and the field description.
# Line 3781  Line 4107 
4107      $htmlString .= "</ul>\n";      $htmlString .= "</ul>\n";
4108  }  }
4109    
4110  =head3 OpenFieldTable  =head3 _OpenFieldTable
4111    
4112  This method creates the header string for the field table generated by L</ShowMetaData>.  This method creates the header string for the field table generated by L</ShowMetaData>.
4113    
# Line 3806  Line 4132 
4132      return _OpenTable($tablename, 'Field', 'Type', 'Description');      return _OpenTable($tablename, 'Field', 'Type', 'Description');
4133  }  }
4134    
4135  =head3 OpenTable  =head3 _OpenTable
4136    
4137  This method creates the header string for an HTML table.  This method creates the header string for an HTML table.
4138    
# Line 3846  Line 4172 
4172      return $htmlString;      return $htmlString;
4173  }  }
4174    
4175  =head3 CloseTable  =head3 _CloseTable
4176    
4177  This method returns the HTML for closing a table.  This method returns the HTML for closing a table.
4178    
# Line 3858  Line 4184 
4184      return "</table></p>\n";      return "</table></p>\n";
4185  }  }
4186    
4187  =head3 ShowField  =head3 _ShowField
4188    
4189  This method returns the HTML for displaying a row of field information in a field table.  This method returns the HTML for displaying a row of field information in a field table.
4190    
# Line 3893  Line 4219 
4219      return $htmlString;      return $htmlString;
4220  }  }
4221    
4222  =head3 HTMLNote  =head3 _HTMLNote
4223    
4224  Convert a note or comment to HTML by replacing some bulletin-board codes with HTML. The codes  Convert a note or comment to HTML by replacing some bulletin-board codes with HTML. The codes
4225  supported are C<[b]> for B<bold>, C<[i]> for I<italics>, and C<[p]> for a new paragraph.  supported are C<[b]> for B<bold>, C<[i]> for I<italics>, and C<[p]> for a new paragraph.

Legend:
Removed from v.1.55  
changed lines
  Added in v.1.70

MCS Webmaster
ViewVC Help
Powered by ViewVC 1.0.3