9 |
use DBObject; |
use DBObject; |
10 |
use Stats; |
use Stats; |
11 |
use Time::HiRes qw(gettimeofday); |
use Time::HiRes qw(gettimeofday); |
12 |
|
use Digest::MD5 qw(md5_base64); |
13 |
|
use FIG; |
14 |
|
|
15 |
=head1 Entity-Relationship Database Package |
=head1 Entity-Relationship Database Package |
16 |
|
|
110 |
compatability with certain database packages), but the only values supported are |
compatability with certain database packages), but the only values supported are |
111 |
0 and 1. |
0 and 1. |
112 |
|
|
113 |
|
=item id-string |
114 |
|
|
115 |
|
variable-length string, maximum 25 characters |
116 |
|
|
117 |
=item key-string |
=item key-string |
118 |
|
|
119 |
variable-length string, maximum 40 characters |
variable-length string, maximum 40 characters |
130 |
|
|
131 |
variable-length string, maximum 255 characters |
variable-length string, maximum 255 characters |
132 |
|
|
133 |
|
=item hash-string |
134 |
|
|
135 |
|
variable-length string, maximum 22 characters |
136 |
|
|
137 |
=back |
=back |
138 |
|
|
139 |
|
The hash-string data type has a special meaning. The actual key passed into the loader will |
140 |
|
be a string, but it will be digested into a 22-character MD5 code to save space. Although the |
141 |
|
MD5 algorithm is not perfect, it is extremely unlikely two strings will have the same |
142 |
|
digest. Therefore, it is presumed the keys will be unique. When the database is actually |
143 |
|
in use, the hashed keys will be presented rather than the original values. For this reason, |
144 |
|
they should not be used for entities where the key is meaningful. |
145 |
|
|
146 |
=head3 Global Tags |
=head3 Global Tags |
147 |
|
|
148 |
The entire database definition must be inside a B<Database> tag. The display name of |
The entire database definition must be inside a B<Database> tag. The display name of |
325 |
text => { sqlType => 'TEXT', maxLen => 1000000000, avgLen => 500, dataGen => "StringGen(IntGen(80,1000))" }, |
text => { sqlType => 'TEXT', maxLen => 1000000000, avgLen => 500, dataGen => "StringGen(IntGen(80,1000))" }, |
326 |
date => { sqlType => 'BIGINT', maxLen => 80, avgLen => 8, dataGen => "DateGen(-7, 7, IntGen(0,1400))" }, |
date => { sqlType => 'BIGINT', maxLen => 80, avgLen => 8, dataGen => "DateGen(-7, 7, IntGen(0,1400))" }, |
327 |
float => { sqlType => 'DOUBLE PRECISION', maxLen => 40, avgLen => 8, dataGen => "FloatGen(0.0, 100.0)" }, |
float => { sqlType => 'DOUBLE PRECISION', maxLen => 40, avgLen => 8, dataGen => "FloatGen(0.0, 100.0)" }, |
328 |
boolean => { sqlType => 'SMALLINT', maxLen => 1, avgLen => 2, dataGen => "IntGen(0, 1)" }, |
boolean => { sqlType => 'SMALLINT', maxLen => 1, avgLen => 1, dataGen => "IntGen(0, 1)" }, |
329 |
|
'hash-string' => |
330 |
|
{ sqlType => 'VARCHAR(22)', maxLen => 22, avgLen => 22, dataGen => "SringGen(22)" }, |
331 |
|
'id-string' => |
332 |
|
{ sqlType => 'VARCHAR(25)', maxLen => 25, avgLen => 25, dataGen => "SringGen(22)" }, |
333 |
'key-string' => |
'key-string' => |
334 |
{ sqlType => 'VARCHAR(40)', maxLen => 40, avgLen => 10, dataGen => "StringGen(IntGen(10,40))" }, |
{ sqlType => 'VARCHAR(40)', maxLen => 40, avgLen => 10, dataGen => "StringGen(IntGen(10,40))" }, |
335 |
'name-string' => |
'name-string' => |
422 |
# Write the HTML heading stuff. |
# Write the HTML heading stuff. |
423 |
print HTMLOUT "<html>\n<head>\n<title>$title</title>\n"; |
print HTMLOUT "<html>\n<head>\n<title>$title</title>\n"; |
424 |
print HTMLOUT "</head>\n<body>\n"; |
print HTMLOUT "</head>\n<body>\n"; |
425 |
|
# Write the documentation. |
426 |
|
print HTMLOUT $self->DisplayMetaData(); |
427 |
|
# Close the document. |
428 |
|
print HTMLOUT "</body>\n</html>\n"; |
429 |
|
# Close the file. |
430 |
|
close HTMLOUT; |
431 |
|
} |
432 |
|
|
433 |
|
=head3 DisplayMetaData |
434 |
|
|
435 |
|
C<< my $html = $erdb->DisplayMetaData(); >> |
436 |
|
|
437 |
|
Return an HTML description of the database. This description can be used to help users create |
438 |
|
the data to be loaded into the relations and form queries. The output is raw includable HTML |
439 |
|
without any HEAD or BODY tags. |
440 |
|
|
441 |
|
=over 4 |
442 |
|
|
443 |
|
=item filename |
444 |
|
|
445 |
|
The name of the output file. |
446 |
|
|
447 |
|
=back |
448 |
|
|
449 |
|
=cut |
450 |
|
|
451 |
|
sub DisplayMetaData { |
452 |
|
# Get the parameters. |
453 |
|
my ($self) = @_; |
454 |
|
# Get the metadata and the title string. |
455 |
|
my $metadata = $self->{_metaData}; |
456 |
|
# Get the title string. |
457 |
|
my $title = $metadata->{Title}; |
458 |
|
# Get the entity and relationship lists. |
459 |
|
my $entityList = $metadata->{Entities}; |
460 |
|
my $relationshipList = $metadata->{Relationships}; |
461 |
|
# Declare the return variable. |
462 |
|
my $retVal = ""; |
463 |
|
# Open the output file. |
464 |
|
Trace("Building MetaData table of contents.") if T(4); |
465 |
# Here we do the table of contents. It starts as an unordered list of section names. Each |
# Here we do the table of contents. It starts as an unordered list of section names. Each |
466 |
# section contains an ordered list of entity or relationship subsections. |
# section contains an ordered list of entity or relationship subsections. |
467 |
print HTMLOUT "<ul>\n<li><a href=\"#EntitiesSection\">Entities</a>\n<ol>\n"; |
$retVal .= "<ul>\n<li><a href=\"#EntitiesSection\">Entities</a>\n<ol>\n"; |
468 |
# Loop through the Entities, displaying a list item for each. |
# Loop through the Entities, displaying a list item for each. |
469 |
foreach my $key (sort keys %{$entityList}) { |
foreach my $key (sort keys %{$entityList}) { |
470 |
# Display this item. |
# Display this item. |
471 |
print HTMLOUT "<li><a href=\"#$key\">$key</a></li>\n"; |
$retVal .= "<li><a href=\"#$key\">$key</a></li>\n"; |
472 |
} |
} |
473 |
# Close off the entity section and start the relationship section. |
# Close off the entity section and start the relationship section. |
474 |
print HTMLOUT "</ol></li>\n<li><a href=\"#RelationshipsSection\">Relationships</a>\n<ol>\n"; |
$retVal .= "</ol></li>\n<li><a href=\"#RelationshipsSection\">Relationships</a>\n<ol>\n"; |
475 |
# Loop through the Relationships. |
# Loop through the Relationships. |
476 |
foreach my $key (sort keys %{$relationshipList}) { |
foreach my $key (sort keys %{$relationshipList}) { |
477 |
# Display this item. |
# Display this item. |
478 |
my $relationshipTitle = _ComputeRelationshipSentence($key, $relationshipList->{$key}); |
my $relationshipTitle = _ComputeRelationshipSentence($key, $relationshipList->{$key}); |
479 |
print HTMLOUT "<li><a href=\"#$key\">$relationshipTitle</a></li>\n"; |
$retVal .= "<li><a href=\"#$key\">$relationshipTitle</a></li>\n"; |
480 |
} |
} |
481 |
# Close off the relationship section and list the join table section. |
# Close off the relationship section and list the join table section. |
482 |
print HTMLOUT "</ol></li>\n<li><a href=\"#JoinTable\">Join Table</a></li>\n"; |
$retVal .= "</ol></li>\n<li><a href=\"#JoinTable\">Join Table</a></li>\n"; |
483 |
# Close off the table of contents itself. |
# Close off the table of contents itself. |
484 |
print HTMLOUT "</ul>\n"; |
$retVal .= "</ul>\n"; |
485 |
# Now we start with the actual data. Denote we're starting the entity section. |
# Now we start with the actual data. Denote we're starting the entity section. |
486 |
print HTMLOUT "<a name=\"EntitiesSection\"></a><h2>Entities</h2>\n"; |
$retVal .= "<a name=\"EntitiesSection\"></a><h2>Entities</h2>\n"; |
487 |
# Loop through the entities. |
# Loop through the entities. |
488 |
for my $key (sort keys %{$entityList}) { |
for my $key (sort keys %{$entityList}) { |
489 |
Trace("Building MetaData entry for $key entity.") if T(4); |
Trace("Building MetaData entry for $key entity.") if T(4); |
490 |
# Create the entity header. It contains a bookmark and the entity name. |
# Create the entity header. It contains a bookmark and the entity name. |
491 |
print HTMLOUT "<a name=\"$key\"></a><h3>$key</h3>\n"; |
$retVal .= "<a name=\"$key\"></a><h3>$key</h3>\n"; |
492 |
# Get the entity data. |
# Get the entity data. |
493 |
my $entityData = $entityList->{$key}; |
my $entityData = $entityList->{$key}; |
494 |
# If there's descriptive text, display it. |
# If there's descriptive text, display it. |
495 |
if (my $notes = $entityData->{Notes}) { |
if (my $notes = $entityData->{Notes}) { |
496 |
print HTMLOUT "<p>" . _HTMLNote($notes->{content}) . "</p>\n"; |
$retVal .= "<p>" . _HTMLNote($notes->{content}) . "</p>\n"; |
497 |
} |
} |
498 |
# Now we want a list of the entity's relationships. First, we set up the relationship subsection. |
# Now we want a list of the entity's relationships. First, we set up the relationship subsection. |
499 |
print HTMLOUT "<h4>Relationships for <b>$key</b></h4>\n<ul>\n"; |
$retVal .= "<h4>Relationships for <b>$key</b></h4>\n<ul>\n"; |
500 |
# Loop through the relationships. |
# Loop through the relationships. |
501 |
for my $relationship (sort keys %{$relationshipList}) { |
for my $relationship (sort keys %{$relationshipList}) { |
502 |
# Get the relationship data. |
# Get the relationship data. |
506 |
# Get the relationship sentence and append the arity. |
# Get the relationship sentence and append the arity. |
507 |
my $relationshipDescription = _ComputeRelationshipSentence($relationship, $relationshipStructure); |
my $relationshipDescription = _ComputeRelationshipSentence($relationship, $relationshipStructure); |
508 |
# Display the relationship data. |
# Display the relationship data. |
509 |
print HTMLOUT "<li><a href=\"#$relationship\">$relationshipDescription</a></li>\n"; |
$retVal .= "<li><a href=\"#$relationship\">$relationshipDescription</a></li>\n"; |
510 |
} |
} |
511 |
} |
} |
512 |
# Close off the relationship list. |
# Close off the relationship list. |
513 |
print HTMLOUT "</ul>\n"; |
$retVal .= "</ul>\n"; |
514 |
# Get the entity's relations. |
# Get the entity's relations. |
515 |
my $relationList = $entityData->{Relations}; |
my $relationList = $entityData->{Relations}; |
516 |
# Create a header for the relation subsection. |
# Create a header for the relation subsection. |
517 |
print HTMLOUT "<h4>Relations for <b>$key</b></h4>\n"; |
$retVal .= "<h4>Relations for <b>$key</b></h4>\n"; |
518 |
# Loop through the relations, displaying them. |
# Loop through the relations, displaying them. |
519 |
for my $relation (sort keys %{$relationList}) { |
for my $relation (sort keys %{$relationList}) { |
520 |
my $htmlString = _ShowRelationTable($relation, $relationList->{$relation}); |
my $htmlString = _ShowRelationTable($relation, $relationList->{$relation}); |
521 |
print HTMLOUT $htmlString; |
$retVal .= $htmlString; |
522 |
} |
} |
523 |
} |
} |
524 |
# Denote we're starting the relationship section. |
# Denote we're starting the relationship section. |
525 |
print HTMLOUT "<a name=\"RelationshipsSection\"></a><h2>Relationships</h2>\n"; |
$retVal .= "<a name=\"RelationshipsSection\"></a><h2>Relationships</h2>\n"; |
526 |
# Loop through the relationships. |
# Loop through the relationships. |
527 |
for my $key (sort keys %{$relationshipList}) { |
for my $key (sort keys %{$relationshipList}) { |
528 |
Trace("Building MetaData entry for $key relationship.") if T(4); |
Trace("Building MetaData entry for $key relationship.") if T(4); |
530 |
my $relationshipStructure = $relationshipList->{$key}; |
my $relationshipStructure = $relationshipList->{$key}; |
531 |
# Create the relationship header. |
# Create the relationship header. |
532 |
my $headerText = _ComputeRelationshipHeading($key, $relationshipStructure); |
my $headerText = _ComputeRelationshipHeading($key, $relationshipStructure); |
533 |
print HTMLOUT "<h3><a name=\"$key\"></a>$headerText</h3>\n"; |
$retVal .= "<h3><a name=\"$key\"></a>$headerText</h3>\n"; |
534 |
# Get the entity names. |
# Get the entity names. |
535 |
my $fromEntity = $relationshipStructure->{from}; |
my $fromEntity = $relationshipStructure->{from}; |
536 |
my $toEntity = $relationshipStructure->{to}; |
my $toEntity = $relationshipStructure->{to}; |
540 |
# since both sentences will say the same thing. |
# since both sentences will say the same thing. |
541 |
my $arity = $relationshipStructure->{arity}; |
my $arity = $relationshipStructure->{arity}; |
542 |
if ($arity eq "11") { |
if ($arity eq "11") { |
543 |
print HTMLOUT "<p>Each <b>$fromEntity</b> relates to at most one <b>$toEntity</b>.\n"; |
$retVal .= "<p>Each <b>$fromEntity</b> relates to at most one <b>$toEntity</b>.\n"; |
544 |
} else { |
} else { |
545 |
print HTMLOUT "<p>Each <b>$fromEntity</b> relates to multiple <b>$toEntity</b>s.\n"; |
$retVal .= "<p>Each <b>$fromEntity</b> relates to multiple <b>$toEntity</b>s.\n"; |
546 |
if ($arity eq "MM" && $fromEntity ne $toEntity) { |
if ($arity eq "MM" && $fromEntity ne $toEntity) { |
547 |
print HTMLOUT "Each <b>$toEntity</b> relates to multiple <b>$fromEntity</b>s.\n"; |
$retVal .= "Each <b>$toEntity</b> relates to multiple <b>$fromEntity</b>s.\n"; |
548 |
} |
} |
549 |
} |
} |
550 |
print HTMLOUT "</p>\n"; |
$retVal .= "</p>\n"; |
551 |
# If there are notes on this relationship, display them. |
# If there are notes on this relationship, display them. |
552 |
if (my $notes = $relationshipStructure->{Notes}) { |
if (my $notes = $relationshipStructure->{Notes}) { |
553 |
print HTMLOUT "<p>" . _HTMLNote($notes->{content}) . "</p>\n"; |
$retVal .= "<p>" . _HTMLNote($notes->{content}) . "</p>\n"; |
554 |
} |
} |
555 |
# Generate the relationship's relation table. |
# Generate the relationship's relation table. |
556 |
my $htmlString = _ShowRelationTable($key, $relationshipStructure->{Relations}->{$key}); |
my $htmlString = _ShowRelationTable($key, $relationshipStructure->{Relations}->{$key}); |
557 |
print HTMLOUT $htmlString; |
$retVal .= $htmlString; |
558 |
} |
} |
559 |
Trace("Building MetaData join table.") if T(4); |
Trace("Building MetaData join table.") if T(4); |
560 |
# Denote we're starting the join table. |
# Denote we're starting the join table. |
561 |
print HTMLOUT "<a name=\"JoinTable\"></a><h3>Join Table</h3>\n"; |
$retVal .= "<a name=\"JoinTable\"></a><h3>Join Table</h3>\n"; |
562 |
# Create a table header. |
# Create a table header. |
563 |
print HTMLOUT _OpenTable("Join Table", "Source", "Target", "Join Condition"); |
$retVal .= _OpenTable("Join Table", "Source", "Target", "Join Condition"); |
564 |
# Loop through the joins. |
# Loop through the joins. |
565 |
my $joinTable = $metadata->{Joins}; |
my $joinTable = $metadata->{Joins}; |
566 |
my @joinKeys = keys %{$joinTable}; |
my @joinKeys = keys %{$joinTable}; |
568 |
# Separate out the source, the target, and the join clause. |
# Separate out the source, the target, and the join clause. |
569 |
$joinKey =~ m!^([^/]+)/(.+)$!; |
$joinKey =~ m!^([^/]+)/(.+)$!; |
570 |
my ($sourceRelation, $targetRelation) = ($1, $2); |
my ($sourceRelation, $targetRelation) = ($1, $2); |
571 |
Trace("Join with key $joinKey is from $sourceRelation to $targetRelation.") if T(4); |
Trace("Join with key $joinKey is from $sourceRelation to $targetRelation.") if T(Joins => 4); |
572 |
my $source = $self->ComputeObjectSentence($sourceRelation); |
my $source = $self->ComputeObjectSentence($sourceRelation); |
573 |
my $target = $self->ComputeObjectSentence($targetRelation); |
my $target = $self->ComputeObjectSentence($targetRelation); |
574 |
my $clause = $joinTable->{$joinKey}; |
my $clause = $joinTable->{$joinKey}; |
575 |
# Display them in a table row. |
# Display them in a table row. |
576 |
print HTMLOUT "<tr><td>$source</td><td>$target</td><td>$clause</td></tr>\n"; |
$retVal .= "<tr><td>$source</td><td>$target</td><td>$clause</td></tr>\n"; |
577 |
} |
} |
578 |
# Close the table. |
# Close the table. |
579 |
print HTMLOUT _CloseTable(); |
$retVal .= _CloseTable(); |
580 |
# Close the document. |
Trace("Built MetaData HTML.") if T(3); |
581 |
print HTMLOUT "</body>\n</html>\n"; |
# Return the HTML. |
582 |
# Close the file. |
return $retVal; |
|
close HTMLOUT; |
|
|
Trace("Built MetaData web page.") if T(3); |
|
583 |
} |
} |
584 |
|
|
585 |
=head3 DumpMetaData |
=head3 DumpMetaData |
611 |
sub CreateTables { |
sub CreateTables { |
612 |
# Get the parameters. |
# Get the parameters. |
613 |
my ($self) = @_; |
my ($self) = @_; |
614 |
my $metadata = $self->{_metaData}; |
# Get the relation names. |
615 |
my $dbh = $self->{_dbh}; |
my @relNames = $self->GetTableNames(); |
616 |
# Loop through the entities. |
# Loop through the relations. |
617 |
my $entityHash = $metadata->{Entities}; |
for my $relationName (@relNames) { |
|
for my $entityName (keys %{$entityHash}) { |
|
|
my $entityData = $entityHash->{$entityName}; |
|
|
# Tell the user what we're doing. |
|
|
Trace("Creating relations for entity $entityName.") if T(1); |
|
|
# Loop through the entity's relations. |
|
|
for my $relationName (keys %{$entityData->{Relations}}) { |
|
618 |
# Create a table for this relation. |
# Create a table for this relation. |
619 |
$self->CreateTable($relationName); |
$self->CreateTable($relationName); |
620 |
Trace("Relation $relationName created.") if T(1); |
Trace("Relation $relationName created.") if T(2); |
|
} |
|
|
} |
|
|
# Loop through the relationships. |
|
|
my $relationshipTable = $metadata->{Relationships}; |
|
|
for my $relationshipName (keys %{$metadata->{Relationships}}) { |
|
|
# Create a table for this relationship. |
|
|
Trace("Creating relationship $relationshipName.") if T(1); |
|
|
$self->CreateTable($relationshipName); |
|
621 |
} |
} |
622 |
} |
} |
623 |
|
|
690 |
} |
} |
691 |
} |
} |
692 |
|
|
693 |
|
=head3 VerifyFields |
694 |
|
|
695 |
|
C<< my $count = $erdb->VerifyFields($relName, \@fieldList); >> |
696 |
|
|
697 |
|
Run through the list of proposed field values, insuring that all the character fields are |
698 |
|
below the maximum length. If any fields are too long, they will be truncated in place. |
699 |
|
|
700 |
|
=over 4 |
701 |
|
|
702 |
|
=item relName |
703 |
|
|
704 |
|
Name of the relation for which the specified fields are destined. |
705 |
|
|
706 |
|
=item fieldList |
707 |
|
|
708 |
|
Reference to a list, in order, of the fields to be put into the relation. |
709 |
|
|
710 |
|
=item RETURN |
711 |
|
|
712 |
|
Returns the number of fields truncated. |
713 |
|
|
714 |
|
=back |
715 |
|
|
716 |
|
=cut |
717 |
|
|
718 |
|
sub VerifyFields { |
719 |
|
# Get the parameters. |
720 |
|
my ($self, $relName, $fieldList) = @_; |
721 |
|
# Initialize the return value. |
722 |
|
my $retVal = 0; |
723 |
|
# Get the relation definition. |
724 |
|
my $relData = $self->_FindRelation($relName); |
725 |
|
# Get the list of field descriptors. |
726 |
|
my $fieldTypes = $relData->{Fields}; |
727 |
|
my $fieldCount = scalar @{$fieldTypes}; |
728 |
|
# Loop through the two lists. |
729 |
|
for (my $i = 0; $i < $fieldCount; $i++) { |
730 |
|
# Get the type of the current field. |
731 |
|
my $fieldType = $fieldTypes->[$i]->{type}; |
732 |
|
# If it's a character field, verify the length. |
733 |
|
if ($fieldType =~ /string/) { |
734 |
|
my $maxLen = $TypeTable{$fieldType}->{maxLen}; |
735 |
|
my $oldString = $fieldList->[$i]; |
736 |
|
if (length($oldString) > $maxLen) { |
737 |
|
# Here it's too big, so we truncate it. |
738 |
|
Trace("Truncating field $i in relation $relName to $maxLen characters from \"$oldString\".") if T(1); |
739 |
|
$fieldList->[$i] = substr $oldString, 0, $maxLen; |
740 |
|
$retVal++; |
741 |
|
} |
742 |
|
} |
743 |
|
} |
744 |
|
# Return the truncation count. |
745 |
|
return $retVal; |
746 |
|
} |
747 |
|
|
748 |
|
=head3 DigestFields |
749 |
|
|
750 |
|
C<< $erdb->DigestFields($relName, $fieldList); >> |
751 |
|
|
752 |
|
Digest the strings in the field list that correspond to data type C<hash-string> in the |
753 |
|
specified relation. |
754 |
|
|
755 |
|
=over 4 |
756 |
|
|
757 |
|
=item relName |
758 |
|
|
759 |
|
Name of the relation to which the fields belong. |
760 |
|
|
761 |
|
=item fieldList |
762 |
|
|
763 |
|
List of field contents to be loaded into the relation. |
764 |
|
|
765 |
|
=back |
766 |
|
|
767 |
|
=cut |
768 |
|
#: Return Type ; |
769 |
|
sub DigestFields { |
770 |
|
# Get the parameters. |
771 |
|
my ($self, $relName, $fieldList) = @_; |
772 |
|
# Get the relation definition. |
773 |
|
my $relData = $self->_FindRelation($relName); |
774 |
|
# Get the list of field descriptors. |
775 |
|
my $fieldTypes = $relData->{Fields}; |
776 |
|
my $fieldCount = scalar @{$fieldTypes}; |
777 |
|
# Loop through the two lists. |
778 |
|
for (my $i = 0; $i < $fieldCount; $i++) { |
779 |
|
# Get the type of the current field. |
780 |
|
my $fieldType = $fieldTypes->[$i]->{type}; |
781 |
|
# If it's a hash string, digest it in place. |
782 |
|
if ($fieldType eq 'hash-string') { |
783 |
|
$fieldList->[$i] = $self->DigestKey($fieldList->[$i]); |
784 |
|
} |
785 |
|
} |
786 |
|
} |
787 |
|
|
788 |
|
=head3 DigestKey |
789 |
|
|
790 |
|
C<< my $digested = $erdb->DigestKey($keyValue); >> |
791 |
|
|
792 |
|
Return the digested value of a symbolic key. The digested value can then be plugged into a |
793 |
|
key-based search into a table with key-type hash-string. |
794 |
|
|
795 |
|
Currently the digesting process is independent of the database structure, but that may not |
796 |
|
always be the case, so this is an instance method instead of a static method. |
797 |
|
|
798 |
|
=over 4 |
799 |
|
|
800 |
|
=item keyValue |
801 |
|
|
802 |
|
Key value to digest. |
803 |
|
|
804 |
|
=item RETURN |
805 |
|
|
806 |
|
Digested value of the key. |
807 |
|
|
808 |
|
=back |
809 |
|
|
810 |
|
=cut |
811 |
|
|
812 |
|
sub DigestKey { |
813 |
|
# Get the parameters. |
814 |
|
my ($self, $keyValue) = @_; |
815 |
|
# Compute the digest. |
816 |
|
my $retVal = md5_base64($keyValue); |
817 |
|
# Return the result. |
818 |
|
return $retVal; |
819 |
|
} |
820 |
|
|
821 |
=head3 CreateIndex |
=head3 CreateIndex |
822 |
|
|
823 |
C<< $erdb->CreateIndex($relationName); >> |
C<< $erdb->CreateIndex($relationName); >> |
846 |
# Get the index's uniqueness flag. |
# Get the index's uniqueness flag. |
847 |
my $unique = (exists $indexData->{Unique} ? $indexData->{Unique} : 'false'); |
my $unique = (exists $indexData->{Unique} ? $indexData->{Unique} : 'false'); |
848 |
# Create the index. |
# Create the index. |
849 |
$dbh->create_index(idx => $indexName, tbl => $relationName, flds => $flds, unique => $unique); |
my $rv = $dbh->create_index(idx => $indexName, tbl => $relationName, |
850 |
|
flds => $flds, unique => $unique); |
851 |
|
if ($rv) { |
852 |
Trace("Index created: $indexName for $relationName ($flds)") if T(1); |
Trace("Index created: $indexName for $relationName ($flds)") if T(1); |
853 |
|
} else { |
854 |
|
Confess("Error creating index $indexName for $relationName using ($flds): " . $dbh->error_message()); |
855 |
|
} |
856 |
} |
} |
857 |
} |
} |
858 |
|
|
901 |
$directoryName =~ s!/\\$!!; |
$directoryName =~ s!/\\$!!; |
902 |
# Declare the return variable. |
# Declare the return variable. |
903 |
my $retVal = Stats->new(); |
my $retVal = Stats->new(); |
904 |
# Get the metadata structure. |
# Get the relation names. |
905 |
my $metaData = $self->{_metaData}; |
my @relNames = $self->GetTableNames(); |
906 |
# Loop through the entities. |
for my $relationName (@relNames) { |
|
for my $entity (values %{$metaData->{Entities}}) { |
|
|
# Loop through the entity's relations. |
|
|
for my $relationName (keys %{$entity->{Relations}}) { |
|
907 |
# Try to load this relation. |
# Try to load this relation. |
908 |
my $result = $self->_LoadRelation($directoryName, $relationName, $rebuild); |
my $result = $self->_LoadRelation($directoryName, $relationName, $rebuild); |
909 |
# Accumulate the statistics. |
# Accumulate the statistics. |
910 |
$retVal->Accumulate($result); |
$retVal->Accumulate($result); |
911 |
} |
} |
|
} |
|
|
# Loop through the relationships. |
|
|
for my $relationshipName (keys %{$metaData->{Relationships}}) { |
|
|
# Try to load this relationship's relation. |
|
|
my $result = $self->_LoadRelation($directoryName, $relationshipName, $rebuild); |
|
|
# Accumulate the statistics. |
|
|
$retVal->Accumulate($result); |
|
|
} |
|
912 |
# Add the duration of the load to the statistical object. |
# Add the duration of the load to the statistical object. |
913 |
$retVal->Add('duration', gettimeofday - $startTime); |
$retVal->Add('duration', gettimeofday - $startTime); |
914 |
# Return the accumulated statistics. |
# Return the accumulated statistics. |
915 |
return $retVal; |
return $retVal; |
916 |
} |
} |
917 |
|
|
918 |
|
|
919 |
=head3 GetTableNames |
=head3 GetTableNames |
920 |
|
|
921 |
C<< my @names = $erdb->GetTableNames; >> |
C<< my @names = $erdb->GetTableNames; >> |
950 |
return sort keys %{$entityList}; |
return sort keys %{$entityList}; |
951 |
} |
} |
952 |
|
|
953 |
|
=head3 IsEntity |
954 |
|
|
955 |
|
C<< my $flag = $erdb->IsEntity($entityName); >> |
956 |
|
|
957 |
|
Return TRUE if the parameter is an entity name, else FALSE. |
958 |
|
|
959 |
|
=over 4 |
960 |
|
|
961 |
|
=item entityName |
962 |
|
|
963 |
|
Object name to be tested. |
964 |
|
|
965 |
|
=item RETURN |
966 |
|
|
967 |
|
Returns TRUE if the specified string is an entity name, else FALSE. |
968 |
|
|
969 |
|
=back |
970 |
|
|
971 |
|
=cut |
972 |
|
|
973 |
|
sub IsEntity { |
974 |
|
# Get the parameters. |
975 |
|
my ($self, $entityName) = @_; |
976 |
|
# Test to see if it's an entity. |
977 |
|
return exists $self->{_metaData}->{Entities}->{$entityName}; |
978 |
|
} |
979 |
|
|
980 |
=head3 Get |
=head3 Get |
981 |
|
|
982 |
C<< my $query = $erdb->Get(\@objectNames, $filterClause, $param1, $param2, ..., $paramN); >> |
C<< my $query = $erdb->Get(\@objectNames, $filterClause, \@params); >> |
983 |
|
|
984 |
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. |
985 |
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 |
987 |
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 |
988 |
$genus. |
$genus. |
989 |
|
|
990 |
C<< $query = $erdb->Get(['Genome'], "Genome(genus) = ?", $genus); >> |
C<< $query = $erdb->Get(['Genome'], "Genome(genus) = ?", [$genus]); >> |
991 |
|
|
992 |
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 |
993 |
parameter representing the parameter value. It would also be possible to code |
parameter representing the parameter value. It would also be possible to code |
1004 |
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 |
1005 |
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, |
1006 |
|
|
1007 |
C<< $query = $erdb->Get(['Genome', 'ComesFrom', 'Source'], "Genome(genus) = ?", $genus); >> |
C<< $query = $erdb->Get(['Genome', 'ComesFrom', 'Source'], "Genome(genus) = ?", [$genus]); >> |
1008 |
|
|
1009 |
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 |
1010 |
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. |
1011 |
In particular, you can't specify any entity or relationship more than once, and if a |
In particular, if a relationship is recursive, the path is determined by the order in which |
1012 |
relationship is recursive, the path is determined by the order in which the entity |
the entity and the relationship appear. For example, consider a recursive relationship |
1013 |
and the relationship appear. For example, consider a recursive relationship B<IsParentOf> |
B<IsParentOf> which relates B<People> objects to other B<People> objects. If the join path is |
|
which relates B<People> objects to other B<People> objects. If the join path is |
|
1014 |
coded as C<['People', 'IsParentOf']>, then the people returned will be parents. If, however, |
coded as C<['People', 'IsParentOf']>, then the people returned will be parents. If, however, |
1015 |
the join path is C<['IsParentOf', 'People']>, then the people returned will be children. |
the join path is C<['IsParentOf', 'People']>, then the people returned will be children. |
1016 |
|
|
1017 |
|
If an entity or relationship is mentioned twice, the name for the second occurrence will |
1018 |
|
be suffixed with C<2>, the third occurrence will be suffixed with C<3>, and so forth. So, |
1019 |
|
for example, if we have C<['Feature', 'HasContig', 'Contig', 'HasContig']>, then the |
1020 |
|
B<to-link> field of the first B<HasContig> is specified as C<HasContig(to-link)>, while |
1021 |
|
the B<to-link> field of the second B<HasContig> is specified as C<HasContig2(to-link)>. |
1022 |
|
|
1023 |
=over 4 |
=over 4 |
1024 |
|
|
1025 |
=item objectNames |
=item objectNames |
1042 |
|
|
1043 |
C<< "Genome(genus) = ? ORDER BY Genome(species)" >> |
C<< "Genome(genus) = ? ORDER BY Genome(species)" >> |
1044 |
|
|
1045 |
|
Note that the case is important. Only an uppercase "ORDER BY" with a single space will |
1046 |
|
be processed. The idea is to make it less likely to find the verb by accident. |
1047 |
|
|
1048 |
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 |
1049 |
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 |
1050 |
relation. |
relation. |
1051 |
|
|
1052 |
=item param1, param2, ..., paramN |
Finally, you can limit the number of rows returned by adding a LIMIT clause. The LIMIT must |
1053 |
|
be the last thing in the filter clause, and it contains only the word "LIMIT" followed by |
1054 |
|
a positive number. So, for example |
1055 |
|
|
1056 |
|
C<< "Genome(genus) = ? ORDER BY Genome(species) LIMIT 10" >> |
1057 |
|
|
1058 |
|
will only return the first ten genomes for the specified genus. The ORDER BY clause is not |
1059 |
|
required. For example, to just get the first 10 genomes in the B<Genome> table, you could |
1060 |
|
use |
1061 |
|
|
1062 |
Parameter values to be substituted into the filter clause. |
C<< "LIMIT 10" >> |
1063 |
|
|
1064 |
|
=item params |
1065 |
|
|
1066 |
|
Reference to a list of parameter values to be substituted into the filter clause. |
1067 |
|
|
1068 |
=item RETURN |
=item RETURN |
1069 |
|
|
1075 |
|
|
1076 |
sub Get { |
sub Get { |
1077 |
# Get the parameters. |
# Get the parameters. |
1078 |
my ($self, $objectNames, $filterClause, @params) = @_; |
my ($self, $objectNames, $filterClause, $params) = @_; |
1079 |
# Construct the SELECT statement. The general pattern is |
# Process the SQL stuff. |
1080 |
# |
my ($suffix, $mappedNameListRef, $mappedNameHashRef) = |
1081 |
# SELECT name1.*, name2.*, ... nameN.* FROM name1, name2, ... nameN |
$self->_SetupSQL($objectNames, $filterClause); |
1082 |
# |
# Create the query. |
1083 |
my $dbh = $self->{_dbh}; |
my $command = "SELECT DISTINCT " . join(".*, ", @{$mappedNameListRef}) . |
1084 |
my $command = "SELECT DISTINCT " . join('.*, ', @{$objectNames}) . ".* FROM " . |
".* $suffix"; |
1085 |
join(', ', @{$objectNames}); |
my $sth = $self->_GetStatementHandle($command, $params); |
1086 |
# Check for a filter clause. |
# Now we create the relation map, which enables DBQuery to determine the order, name |
1087 |
if ($filterClause) { |
# and mapped name for each object in the query. |
1088 |
# Here we have one, so we convert its field names and add it to the query. First, |
my @relationMap = (); |
1089 |
# We create a copy of the filter string we can work with. |
for my $mappedName (@{$mappedNameListRef}) { |
1090 |
my $filterString = $filterClause; |
push @relationMap, [$mappedName, $mappedNameHashRef->{$mappedName}]; |
|
# 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) } @{$objectNames}; |
|
|
# 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. |
|
|
my %fromNames = (); |
|
|
for my $objectName (@sortedNames) { |
|
|
$fromNames{$objectName} = 1; |
|
|
} |
|
|
# We are ready to begin. We loop through the object names, replacing each |
|
|
# object name's field references by the corresponding SQL field reference. |
|
|
# Along the way, if we find a secondary relation, we will need to add it |
|
|
# to the FROM clause. |
|
|
for my $objectName (@sortedNames) { |
|
|
# Get the length of the object name plus 2. This is the value we add to the |
|
|
# size of the field name to determine the size of the field reference as a |
|
|
# whole. |
|
|
my $nameLength = 2 + length $objectName; |
|
|
# Get the object's field list. |
|
|
my $fieldList = $self->_GetFieldTable($objectName); |
|
|
# Find the field references for this object. |
|
|
while ($filterString =~ m/$objectName\(([^)]*)\)/g) { |
|
|
# At this point, $1 contains the field name, and the current position |
|
|
# is set immediately after the final parenthesis. We pull out the name of |
|
|
# the field and the position and length of the field reference as a whole. |
|
|
my $fieldName = $1; |
|
|
my $len = $nameLength + length $fieldName; |
|
|
my $pos = pos($filterString) - $len; |
|
|
# Insure the field exists. |
|
|
if (!exists $fieldList->{$fieldName}) { |
|
|
Confess("Field $fieldName not found for object $objectName."); |
|
|
} else { |
|
|
# Get the field's relation. |
|
|
my $relationName = $fieldList->{$fieldName}->{relation}; |
|
|
# Insure the relation is in the FROM clause. |
|
|
if (!exists $fromNames{$relationName}) { |
|
|
# Add the relation to the FROM clause. |
|
|
$command .= ", $relationName"; |
|
|
# Create its join sub-clause. |
|
|
push @joinWhere, "$objectName.id = $relationName.id"; |
|
|
# Denote we have it available for future fields. |
|
|
$fromNames{$relationName} = 1; |
|
|
} |
|
|
# Form an SQL field reference from the relation name and the field name. |
|
|
my $sqlReference = "$relationName." . _FixName($fieldName); |
|
|
# Put it into the filter string in place of the old value. |
|
|
substr($filterString, $pos, $len) = $sqlReference; |
|
|
# Reposition the search. |
|
|
pos $filterString = $pos + length $sqlReference; |
|
|
} |
|
|
} |
|
|
} |
|
|
# 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. |
|
|
my @objectList = @{$objectNames}; |
|
|
my $lastObject = shift @objectList; |
|
|
# Get the join table. |
|
|
my $joinTable = $self->{_metaData}->{Joins}; |
|
|
# Loop through the object list. |
|
|
for my $thisObject (@objectList) { |
|
|
# Look for a join. |
|
|
my $joinKey = "$lastObject/$thisObject"; |
|
|
if (!exists $joinTable->{$joinKey}) { |
|
|
# Here there's no join, so we throw an error. |
|
|
Confess("No join exists to connect from $lastObject to $thisObject."); |
|
|
} else { |
|
|
# Get the join clause and add it to the WHERE list. |
|
|
push @joinWhere, $joinTable->{$joinKey}; |
|
|
# Save this object as the last object for the next iteration. |
|
|
$lastObject = $thisObject; |
|
|
} |
|
|
} |
|
|
# Now we need to handle the whole ORDER BY thing. We'll put the order by clause |
|
|
# in the following variable. |
|
|
my $orderClause = ""; |
|
|
# Locate the ORDER BY verb (if any). |
|
|
if ($filterString =~ m/^(.*)ORDER BY/g) { |
|
|
# Here we have an ORDER BY verb. Split it off of the filter string. |
|
|
my $pos = pos $filterString; |
|
|
$orderClause = substr($filterString, $pos); |
|
|
$filterString = $1; |
|
|
} |
|
|
# Add the filter and the join clauses (if any) to the SELECT command. |
|
|
if ($filterString) { |
|
|
push @joinWhere, "($filterString)"; |
|
|
} |
|
|
if (@joinWhere) { |
|
|
$command .= " WHERE " . join(' AND ', @joinWhere); |
|
|
} |
|
|
# Add the sort clause (if any) to the SELECT command. |
|
|
if ($orderClause) { |
|
|
$command .= " ORDER BY $orderClause"; |
|
|
} |
|
1091 |
} |
} |
|
Trace("SQL query: $command") if T(3); |
|
|
Trace("PARMS: '" . (join "', '", @params) . "'") if (T(4) && (@params > 0)); |
|
|
my $sth = $dbh->prepare_command($command); |
|
|
# Execute it with the parameters bound in. |
|
|
$sth->execute(@params) || Confess("SELECT error" . $sth->errstr()); |
|
1092 |
# Return the statement object. |
# Return the statement object. |
1093 |
my $retVal = DBQuery::_new($self, $sth, @{$objectNames}); |
my $retVal = DBQuery::_new($self, $sth, \@relationMap); |
1094 |
return $retVal; |
return $retVal; |
1095 |
} |
} |
1096 |
|
|
1097 |
=head3 GetList |
=head3 GetFlat |
|
|
|
|
C<< my @dbObjects = $erdb->GetList(\@objectNames, $filterClause, $param1, $param2, ..., $paramN); >> |
|
1098 |
|
|
1099 |
Return a list of object descriptors for the specified objects as determined by the |
C<< my @list = $erdb->GetFlat(\@objectNames, $filterClause, \@parameterList, $field); >> |
|
specified filter clause. |
|
1100 |
|
|
1101 |
This method is essentially the same as L</Get> except it returns a list of objects rather |
This is a variation of L</GetAll> that asks for only a single field per record and |
1102 |
than a query object that can be used to get the results one record at a time. |
returns a single flattened list. |
1103 |
|
|
1104 |
=over 4 |
=over 4 |
1105 |
|
|
1109 |
|
|
1110 |
=item filterClause |
=item filterClause |
1111 |
|
|
1112 |
WHERE clause (without the WHERE) to be used to filter and sort the query. The WHERE clause can |
WHERE/ORDER BY clause (without the WHERE) to be used to filter and sort the query. The WHERE clause can |
1113 |
be parameterized with parameter markers (C<?>). Each field used in the WHERE clause must be |
be parameterized with parameter markers (C<?>). Each field used must be specified in the standard form |
1114 |
specified in the standard form B<I<objectName>(I<fieldName>)>. Any parameters specified |
B<I<objectName>(I<fieldName>)>. Any parameters specified in the filter clause should be added to the |
1115 |
in the filter clause should be added to the parameter list as additional parameters. The |
parameter list as additional parameters. The fields in a filter clause can come from primary |
1116 |
fields in a filter clause can come from primary entity relations, relationship relations, |
entity relations, relationship relations, or secondary entity relations; however, all of the |
1117 |
or secondary entity relations; however, all of the entities and relationships involved must |
entities and relationships involved must be included in the list of object names. |
|
be included in the list of object names. |
|
|
|
|
|
The filter clause can also specify a sort order. To do this, simply follow the filter string |
|
|
with an ORDER BY clause. For example, the following filter string gets all genomes for a |
|
|
particular genus and sorts them by species name. |
|
1118 |
|
|
1119 |
C<< "Genome(genus) = ? ORDER BY Genome(species)" >> |
=item parameterList |
1120 |
|
|
1121 |
The rules for field references in a sort order are the same as those for field references in the |
List of the parameters to be substituted in for the parameters marks in the filter clause. |
|
filter clause in general; however, odd things may happen if a sort field is from a secondary |
|
|
relation. |
|
1122 |
|
|
1123 |
=item param1, param2, ..., paramN |
=item field |
1124 |
|
|
1125 |
Parameter values to be substituted into the filter clause. |
Name of the field to be used to get the elements of the list returned. |
1126 |
|
|
1127 |
=item RETURN |
=item RETURN |
1128 |
|
|
1129 |
Returns a list of B<DBObject>s that satisfy the query conditions. |
Returns a list of values. |
1130 |
|
|
1131 |
=back |
=back |
1132 |
|
|
1133 |
=cut |
=cut |
1134 |
#: Return Type @% |
#: Return Type @; |
1135 |
sub GetList { |
sub GetFlat { |
1136 |
# Get the parameters. |
# Get the parameters. |
1137 |
my ($self, $objectNames, $filterClause, @params) = @_; |
my ($self, $objectNames, $filterClause, $parameterList, $field) = @_; |
1138 |
# Declare the return variable. |
# Construct the query. |
1139 |
|
my $query = $self->Get($objectNames, $filterClause, $parameterList); |
1140 |
|
# Create the result list. |
1141 |
|
my @retVal = (); |
1142 |
|
# Loop through the records, adding the field values found to the result list. |
1143 |
|
while (my $row = $query->Fetch()) { |
1144 |
|
push @retVal, $row->Value($field); |
1145 |
|
} |
1146 |
|
# Return the list created. |
1147 |
|
return @retVal; |
1148 |
|
} |
1149 |
|
|
1150 |
|
=head3 Delete |
1151 |
|
|
1152 |
|
C<< my $stats = $erdb->Delete($entityName, $objectID); >> |
1153 |
|
|
1154 |
|
Delete an entity instance from the database. The instance is deleted along with all entity and |
1155 |
|
relationship instances dependent on it. The idea of dependence here is recursive. An object is |
1156 |
|
always dependent on itself. An object is dependent if it is a 1-to-many or many-to-many |
1157 |
|
relationship connected to a dependent entity or the "to" entity connected to a 1-to-many |
1158 |
|
dependent relationship. |
1159 |
|
|
1160 |
|
=over 4 |
1161 |
|
|
1162 |
|
=item entityName |
1163 |
|
|
1164 |
|
Name of the entity type for the instance being deleted. |
1165 |
|
|
1166 |
|
=item objectID |
1167 |
|
|
1168 |
|
ID of the entity instance to be deleted. If the ID contains a wild card character (C<%>), |
1169 |
|
then it is presumed to by a LIKE pattern. |
1170 |
|
|
1171 |
|
=item testFlag |
1172 |
|
|
1173 |
|
If TRUE, the delete statements will be traced without being executed. |
1174 |
|
|
1175 |
|
=item RETURN |
1176 |
|
|
1177 |
|
Returns a statistics object indicating how many records of each particular table were |
1178 |
|
deleted. |
1179 |
|
|
1180 |
|
=back |
1181 |
|
|
1182 |
|
=cut |
1183 |
|
#: Return Type $%; |
1184 |
|
sub Delete { |
1185 |
|
# Get the parameters. |
1186 |
|
my ($self, $entityName, $objectID, $testFlag) = @_; |
1187 |
|
# Declare the return variable. |
1188 |
|
my $retVal = Stats->new(); |
1189 |
|
# Get the DBKernel object. |
1190 |
|
my $db = $self->{_dbh}; |
1191 |
|
# We're going to generate all the paths branching out from the starting entity. One of |
1192 |
|
# the things we have to be careful about is preventing loops. We'll use a hash to |
1193 |
|
# determine if we've hit a loop. |
1194 |
|
my %alreadyFound = (); |
1195 |
|
# These next lists will serve as our result stack. We start by pushing object lists onto |
1196 |
|
# the stack, and then popping them off to do the deletes. This means the deletes will |
1197 |
|
# start with the longer paths before getting to the shorter ones. That, in turn, makes |
1198 |
|
# sure we don't delete records that might be needed to forge relationships back to the |
1199 |
|
# original item. We have two lists-- one for TO-relationships, and one for |
1200 |
|
# FROM-relationships and entities. |
1201 |
|
my @fromPathList = (); |
1202 |
|
my @toPathList = (); |
1203 |
|
# This final hash is used to remember what work still needs to be done. We push paths |
1204 |
|
# onto the list, then pop them off to extend the paths. We prime it with the starting |
1205 |
|
# point. Note that we will work hard to insure that the last item on a path in the |
1206 |
|
# TODO list is always an entity. |
1207 |
|
my @todoList = ([$entityName]); |
1208 |
|
while (@todoList) { |
1209 |
|
# Get the current path. |
1210 |
|
my $current = pop @todoList; |
1211 |
|
# Copy it into a list. |
1212 |
|
my @stackedPath = @{$current}; |
1213 |
|
# Pull off the last item on the path. It will always be an entity. |
1214 |
|
my $entityName = pop @stackedPath; |
1215 |
|
# Add it to the alreadyFound list. |
1216 |
|
$alreadyFound{$entityName} = 1; |
1217 |
|
# Get the entity data. |
1218 |
|
my $entityData = $self->_GetStructure($entityName); |
1219 |
|
# The first task is to loop through the entity's relation. A DELETE command will |
1220 |
|
# be needed for each of them. |
1221 |
|
my $relations = $entityData->{Relations}; |
1222 |
|
for my $relation (keys %{$relations}) { |
1223 |
|
my @augmentedList = (@stackedPath, $relation); |
1224 |
|
push @fromPathList, \@augmentedList; |
1225 |
|
} |
1226 |
|
# Now we need to look for relationships connected to this entity. |
1227 |
|
my $relationshipList = $self->{_metaData}->{Relationships}; |
1228 |
|
for my $relationshipName (keys %{$relationshipList}) { |
1229 |
|
my $relationship = $relationshipList->{$relationshipName}; |
1230 |
|
# Check the FROM field. We're only interested if it's us. |
1231 |
|
if ($relationship->{from} eq $entityName) { |
1232 |
|
# Add the path to this relationship. |
1233 |
|
my @augmentedList = (@stackedPath, $entityName, $relationshipName); |
1234 |
|
push @fromPathList, \@augmentedList; |
1235 |
|
# Check the arity. If it's MM we're done. If it's 1M |
1236 |
|
# and the target hasn't been seen yet, we want to |
1237 |
|
# stack the entity for future processing. |
1238 |
|
if ($relationship->{arity} eq '1M') { |
1239 |
|
my $toEntity = $relationship->{to}; |
1240 |
|
if (! exists $alreadyFound{$toEntity}) { |
1241 |
|
# Here we have a new entity that's dependent on |
1242 |
|
# the current entity, so we need to stack it. |
1243 |
|
my @stackList = (@augmentedList, $toEntity); |
1244 |
|
push @fromPathList, \@stackList; |
1245 |
|
} else { |
1246 |
|
Trace("$toEntity ignored because it occurred previously.") if T(4); |
1247 |
|
} |
1248 |
|
} |
1249 |
|
} |
1250 |
|
# Now check the TO field. In this case only the relationship needs |
1251 |
|
# deletion. |
1252 |
|
if ($relationship->{to} eq $entityName) { |
1253 |
|
my @augmentedList = (@stackedPath, $entityName, $relationshipName); |
1254 |
|
push @toPathList, \@augmentedList; |
1255 |
|
} |
1256 |
|
} |
1257 |
|
} |
1258 |
|
# Create the first qualifier for the WHERE clause. This selects the |
1259 |
|
# keys of the primary entity records to be deleted. When we're deleting |
1260 |
|
# from a dependent table, we construct a join page from the first qualifier |
1261 |
|
# to the table containing the dependent records to delete. |
1262 |
|
my $qualifier = ($objectID =~ /%/ ? "LIKE ?" : "= ?"); |
1263 |
|
# We need to make two passes. The first is through the to-list, and |
1264 |
|
# the second through the from-list. The from-list is second because |
1265 |
|
# the to-list may need to pass through some of the entities the |
1266 |
|
# from-list would delete. |
1267 |
|
my %stackList = ( from_link => \@fromPathList, to_link => \@toPathList ); |
1268 |
|
# Now it's time to do the deletes. We do it in two passes. |
1269 |
|
for my $keyName ('to_link', 'from_link') { |
1270 |
|
# Get the list for this key. |
1271 |
|
my @pathList = @{$stackList{$keyName}}; |
1272 |
|
Trace(scalar(@pathList) . " entries in path list for $keyName.") if T(3); |
1273 |
|
# Loop through this list. |
1274 |
|
while (my $path = pop @pathList) { |
1275 |
|
# Get the table whose rows are to be deleted. |
1276 |
|
my @pathTables = @{$path}; |
1277 |
|
# Start the DELETE statement. We need to call DBKernel because the |
1278 |
|
# syntax of a DELETE-USING varies among DBMSs. |
1279 |
|
my $target = $pathTables[$#pathTables]; |
1280 |
|
my $stmt = $db->SetUsing(@pathTables); |
1281 |
|
# Now start the WHERE. The first thing is the ID field from the starting table. That |
1282 |
|
# starting table will either be the entity relation or one of the entity's |
1283 |
|
# sub-relations. |
1284 |
|
$stmt .= " WHERE $pathTables[0].id $qualifier"; |
1285 |
|
# Now we run through the remaining entities in the path, connecting them up. |
1286 |
|
for (my $i = 1; $i <= $#pathTables; $i += 2) { |
1287 |
|
# Connect the current relationship to the preceding entity. |
1288 |
|
my ($entity, $rel) = @pathTables[$i-1,$i]; |
1289 |
|
# The style of connection depends on the direction of the relationship. |
1290 |
|
$stmt .= " AND $entity.id = $rel.$keyName"; |
1291 |
|
if ($i + 1 <= $#pathTables) { |
1292 |
|
# Here there's a next entity, so connect that to the relationship's |
1293 |
|
# to-link. |
1294 |
|
my $entity2 = $pathTables[$i+1]; |
1295 |
|
$stmt .= " AND $rel.to_link = $entity2.id"; |
1296 |
|
} |
1297 |
|
} |
1298 |
|
# Now we have our desired DELETE statement. |
1299 |
|
if ($testFlag) { |
1300 |
|
# Here the user wants to trace without executing. |
1301 |
|
Trace($stmt) if T(0); |
1302 |
|
} else { |
1303 |
|
# Here we can delete. Note that the SQL method dies with a confessing |
1304 |
|
# if an error occurs, so we just go ahead and do it. |
1305 |
|
Trace("Executing delete from $target using '$objectID'.") if T(3); |
1306 |
|
my $rv = $db->SQL($stmt, 0, $objectID); |
1307 |
|
# Accumulate the statistics for this delete. The only rows deleted |
1308 |
|
# are from the target table, so we use its name to record the |
1309 |
|
# statistic. |
1310 |
|
$retVal->Add($target, $rv); |
1311 |
|
} |
1312 |
|
} |
1313 |
|
} |
1314 |
|
# Return the result. |
1315 |
|
return $retVal; |
1316 |
|
} |
1317 |
|
|
1318 |
|
=head3 GetList |
1319 |
|
|
1320 |
|
C<< my @dbObjects = $erdb->GetList(\@objectNames, $filterClause, \@params); >> |
1321 |
|
|
1322 |
|
Return a list of object descriptors for the specified objects as determined by the |
1323 |
|
specified filter clause. |
1324 |
|
|
1325 |
|
This method is essentially the same as L</Get> except it returns a list of objects rather |
1326 |
|
than a query object that can be used to get the results one record at a time. |
1327 |
|
|
1328 |
|
=over 4 |
1329 |
|
|
1330 |
|
=item objectNames |
1331 |
|
|
1332 |
|
List containing the names of the entity and relationship objects to be retrieved. |
1333 |
|
|
1334 |
|
=item filterClause |
1335 |
|
|
1336 |
|
WHERE clause (without the WHERE) to be used to filter and sort the query. The WHERE clause can |
1337 |
|
be parameterized with parameter markers (C<?>). Each field used in the WHERE clause must be |
1338 |
|
specified in the standard form B<I<objectName>(I<fieldName>)>. Any parameters specified |
1339 |
|
in the filter clause should be added to the parameter list as additional parameters. The |
1340 |
|
fields in a filter clause can come from primary entity relations, relationship relations, |
1341 |
|
or secondary entity relations; however, all of the entities and relationships involved must |
1342 |
|
be included in the list of object names. |
1343 |
|
|
1344 |
|
The filter clause can also specify a sort order. To do this, simply follow the filter string |
1345 |
|
with an ORDER BY clause. For example, the following filter string gets all genomes for a |
1346 |
|
particular genus and sorts them by species name. |
1347 |
|
|
1348 |
|
C<< "Genome(genus) = ? ORDER BY Genome(species)" >> |
1349 |
|
|
1350 |
|
The rules for field references in a sort order are the same as those for field references in the |
1351 |
|
filter clause in general; however, odd things may happen if a sort field is from a secondary |
1352 |
|
relation. |
1353 |
|
|
1354 |
|
=item params |
1355 |
|
|
1356 |
|
Reference to a list of parameter values to be substituted into the filter clause. |
1357 |
|
|
1358 |
|
=item RETURN |
1359 |
|
|
1360 |
|
Returns a list of B<DBObject>s that satisfy the query conditions. |
1361 |
|
|
1362 |
|
=back |
1363 |
|
|
1364 |
|
=cut |
1365 |
|
#: Return Type @% |
1366 |
|
sub GetList { |
1367 |
|
# Get the parameters. |
1368 |
|
my ($self, $objectNames, $filterClause, $params) = @_; |
1369 |
|
# Declare the return variable. |
1370 |
my @retVal = (); |
my @retVal = (); |
1371 |
# Perform the query. |
# Perform the query. |
1372 |
my $query = $self->Get($objectNames, $filterClause, @params); |
my $query = $self->Get($objectNames, $filterClause, $params); |
1373 |
# Loop through the results. |
# Loop through the results. |
1374 |
while (my $object = $query->Fetch) { |
while (my $object = $query->Fetch) { |
1375 |
push @retVal, $object; |
push @retVal, $object; |
1378 |
return @retVal; |
return @retVal; |
1379 |
} |
} |
1380 |
|
|
1381 |
|
=head3 GetCount |
1382 |
|
|
1383 |
|
C<< my $count = $erdb->GetCount(\@objectNames, $filter, \@params); >> |
1384 |
|
|
1385 |
|
Return the number of rows found by a specified query. This method would |
1386 |
|
normally be used to count the records in a single table. For example, in a |
1387 |
|
genetics database |
1388 |
|
|
1389 |
|
my $count = $erdb->GetCount(['Genome'], 'Genome(genus-species) LIKE ?', ['homo %']); |
1390 |
|
|
1391 |
|
would return the number of genomes for the genus I<homo>. It is conceivable, however, |
1392 |
|
to use it to return records based on a join. For example, |
1393 |
|
|
1394 |
|
my $count = $erdb->GetCount(['HasFeature', 'Genome'], 'Genome(genus-species) LIKE ?', |
1395 |
|
['homo %']); |
1396 |
|
|
1397 |
|
would return the number of features for genomes in the genus I<homo>. Note that |
1398 |
|
only the rows from the first table are counted. If the above command were |
1399 |
|
|
1400 |
|
my $count = $erdb->GetCount(['Genome', 'Feature'], 'Genome(genus-species) LIKE ?', |
1401 |
|
['homo %']); |
1402 |
|
|
1403 |
|
it would return the number of genomes, not the number of genome/feature pairs. |
1404 |
|
|
1405 |
|
=over 4 |
1406 |
|
|
1407 |
|
=item objectNames |
1408 |
|
|
1409 |
|
Reference to a list of the objects (entities and relationships) included in the |
1410 |
|
query. |
1411 |
|
|
1412 |
|
=item filter |
1413 |
|
|
1414 |
|
A filter clause for restricting the query. The rules are the same as for the L</Get> |
1415 |
|
method. |
1416 |
|
|
1417 |
|
=item params |
1418 |
|
|
1419 |
|
Reference to a list of the parameter values to be substituted for the parameter marks |
1420 |
|
in the filter. |
1421 |
|
|
1422 |
|
=item RETURN |
1423 |
|
|
1424 |
|
Returns a count of the number of records in the first table that would satisfy |
1425 |
|
the query. |
1426 |
|
|
1427 |
|
=back |
1428 |
|
|
1429 |
|
=cut |
1430 |
|
|
1431 |
|
sub GetCount { |
1432 |
|
# Get the parameters. |
1433 |
|
my ($self, $objectNames, $filter, $params) = @_; |
1434 |
|
# Declare the return variable. |
1435 |
|
my $retVal; |
1436 |
|
# Find out if we're counting an entity or a relationship. |
1437 |
|
my $countedField; |
1438 |
|
if ($self->IsEntity($objectNames->[0])) { |
1439 |
|
$countedField = "id"; |
1440 |
|
} else { |
1441 |
|
# For a relationship we count the to-link because it's usually more |
1442 |
|
# numerous. Note we're automatically converting to the SQL form |
1443 |
|
# of the field name (to_link vs. to-link). |
1444 |
|
$countedField = "to_link"; |
1445 |
|
} |
1446 |
|
# Create the SQL command suffix to get the desired records. |
1447 |
|
my ($suffix, $mappedNameListRef, $mappedNameHashRef) = $self->_SetupSQL($objectNames, |
1448 |
|
$filter); |
1449 |
|
# Prefix it with text telling it we want a record count. |
1450 |
|
my $firstObject = $mappedNameListRef->[0]; |
1451 |
|
my $command = "SELECT COUNT($firstObject.$countedField) $suffix"; |
1452 |
|
# Prepare and execute the command. |
1453 |
|
my $sth = $self->_GetStatementHandle($command, $params); |
1454 |
|
# Get the count value. |
1455 |
|
($retVal) = $sth->fetchrow_array(); |
1456 |
|
# Check for a problem. |
1457 |
|
if (! defined($retVal)) { |
1458 |
|
if ($sth->err) { |
1459 |
|
# Here we had an SQL error. |
1460 |
|
Confess("Error retrieving row count: " . $sth->errstr()); |
1461 |
|
} else { |
1462 |
|
# Here we have no result. |
1463 |
|
Confess("No result attempting to retrieve row count."); |
1464 |
|
} |
1465 |
|
} |
1466 |
|
# Return the result. |
1467 |
|
return $retVal; |
1468 |
|
} |
1469 |
|
|
1470 |
=head3 ComputeObjectSentence |
=head3 ComputeObjectSentence |
1471 |
|
|
1472 |
C<< my $sentence = $erdb->ComputeObjectSentence($objectName); >> |
C<< my $sentence = $erdb->ComputeObjectSentence($objectName); >> |
1544 |
} |
} |
1545 |
} |
} |
1546 |
|
|
1547 |
|
=head3 InsertValue |
1548 |
|
|
1549 |
|
C<< $erdb->InsertValue($entityID, $fieldName, $value); >> |
1550 |
|
|
1551 |
|
This method will insert a new value into the database. The value must be one |
1552 |
|
associated with a secondary relation, since primary values cannot be inserted: |
1553 |
|
they occur exactly once. Secondary values, on the other hand, can be missing |
1554 |
|
or multiply-occurring. |
1555 |
|
|
1556 |
|
=over 4 |
1557 |
|
|
1558 |
|
=item entityID |
1559 |
|
|
1560 |
|
ID of the object that is to receive the new value. |
1561 |
|
|
1562 |
|
=item fieldName |
1563 |
|
|
1564 |
|
Field name for the new value-- this includes the entity name, since |
1565 |
|
field names are of the format I<objectName>C<(>I<fieldName>C<)>. |
1566 |
|
|
1567 |
|
=item value |
1568 |
|
|
1569 |
|
New value to be put in the field. |
1570 |
|
|
1571 |
|
=back |
1572 |
|
|
1573 |
|
=cut |
1574 |
|
|
1575 |
|
sub InsertValue { |
1576 |
|
# Get the parameters. |
1577 |
|
my ($self, $entityID, $fieldName, $value) = @_; |
1578 |
|
# Parse the entity name and the real field name. |
1579 |
|
if ($fieldName =~ /^([^(]+)\(([^)]+)\)/) { |
1580 |
|
my $entityName = $1; |
1581 |
|
my $fieldTitle = $2; |
1582 |
|
# Get its descriptor. |
1583 |
|
if (!$self->IsEntity($entityName)) { |
1584 |
|
Confess("$entityName is not a valid entity."); |
1585 |
|
} else { |
1586 |
|
my $entityData = $self->{_metaData}->{Entities}->{$entityName}; |
1587 |
|
# Find the relation containing this field. |
1588 |
|
my $fieldHash = $entityData->{Fields}; |
1589 |
|
if (! exists $fieldHash->{$fieldTitle}) { |
1590 |
|
Confess("$fieldTitle not found in $entityName."); |
1591 |
|
} else { |
1592 |
|
my $relation = $fieldHash->{$fieldTitle}->{relation}; |
1593 |
|
if ($relation eq $entityName) { |
1594 |
|
Confess("Cannot do InsertValue on primary field $fieldTitle of $entityName."); |
1595 |
|
} else { |
1596 |
|
# Now we can create an INSERT statement. |
1597 |
|
my $dbh = $self->{_dbh}; |
1598 |
|
my $fixedName = _FixName($fieldTitle); |
1599 |
|
my $statement = "INSERT INTO $relation (id, $fixedName) VALUES(?, ?)"; |
1600 |
|
# Execute the command. |
1601 |
|
$dbh->SQL($statement, 0, $entityID, $value); |
1602 |
|
} |
1603 |
|
} |
1604 |
|
} |
1605 |
|
} else { |
1606 |
|
Confess("$fieldName is not a valid field name."); |
1607 |
|
} |
1608 |
|
} |
1609 |
|
|
1610 |
=head3 InsertObject |
=head3 InsertObject |
1611 |
|
|
1612 |
C<< my $ok = $erdb->InsertObject($objectType, \%fieldHash); >> |
C<< my $ok = $erdb->InsertObject($objectType, \%fieldHash); >> |
1769 |
|
|
1770 |
=item RETURN |
=item RETURN |
1771 |
|
|
1772 |
Returns a statistical object containing the number of records read and a list of |
Returns a statistical object containing a list of the error messages. |
|
the error messages. |
|
1773 |
|
|
1774 |
=back |
=back |
1775 |
|
|
1783 |
Trace("Loading table $relationName from $fileName") if T(2); |
Trace("Loading table $relationName from $fileName") if T(2); |
1784 |
# Get the database handle. |
# Get the database handle. |
1785 |
my $dbh = $self->{_dbh}; |
my $dbh = $self->{_dbh}; |
1786 |
|
# Get the input file size. |
1787 |
|
my $fileSize = -s $fileName; |
1788 |
# Get the relation data. |
# Get the relation data. |
1789 |
my $relation = $self->_FindRelation($relationName); |
my $relation = $self->_FindRelation($relationName); |
1790 |
# Check the truncation flag. |
# Check the truncation flag. |
1791 |
if ($truncateFlag) { |
if ($truncateFlag) { |
1792 |
Trace("Creating table $relationName") if T(2); |
Trace("Creating table $relationName") if T(2); |
1793 |
|
# Compute the row count estimate. We take the size of the load file, |
1794 |
|
# divide it by the estimated row size, and then multiply by 1.5 to |
1795 |
|
# leave extra room. We postulate a minimum row count of 1000 to |
1796 |
|
# prevent problems with incoming empty load files. |
1797 |
|
my $rowSize = $self->EstimateRowSize($relationName); |
1798 |
|
my $estimate = FIG::max($fileSize * 1.5 / $rowSize, 1000); |
1799 |
# Re-create the table without its index. |
# Re-create the table without its index. |
1800 |
$self->CreateTable($relationName, 0); |
$self->CreateTable($relationName, 0, $estimate); |
1801 |
# If this is a pre-index DBMS, create the index here. |
# If this is a pre-index DBMS, create the index here. |
1802 |
if ($dbh->{_preIndex}) { |
if ($dbh->{_preIndex}) { |
1803 |
eval { |
eval { |
1808 |
} |
} |
1809 |
} |
} |
1810 |
} |
} |
|
# Determine whether or not this is a primary relation. Primary relations have an extra |
|
|
# field indicating whether or not a given object is new or was loaded from the flat files. |
|
|
my $primary = $self->_IsPrimary($relationName); |
|
|
# Get the number of fields in this relation. |
|
|
my @fieldList = @{$relation->{Fields}}; |
|
|
my $fieldCount = @fieldList; |
|
|
# Start a database transaction. |
|
|
$dbh->begin_tran; |
|
|
# Open the relation file. We need to create a cleaned-up copy before loading. |
|
|
open TABLEIN, '<', $fileName; |
|
|
my $tempName = "$fileName.tbl"; |
|
|
open TABLEOUT, '>', $tempName; |
|
|
my $inputCount = 0; |
|
|
# Loop through the file. |
|
|
while (<TABLEIN>) { |
|
|
$inputCount++; |
|
|
# Chop off the new-line character. |
|
|
my $record = Tracer::Strip($_); |
|
|
# Only proceed if the record is non-blank. |
|
|
if ($record) { |
|
|
# Escape all the backslashes found in the line. |
|
|
$record =~ s/\\/\\\\/g; |
|
|
# Insure the number of fields is correct. |
|
|
my @fields = split /\t/, $record; |
|
|
while (@fields > $fieldCount) { |
|
|
my $extraField = $fields[$#fields]; |
|
|
delete $fields[$#fields]; |
|
|
if ($extraField) { |
|
|
Trace("Nonblank extra field value \"$extraField\" deleted from record $inputCount of $fileName.") if T(1); |
|
|
} |
|
|
} |
|
|
while (@fields < $fieldCount) { |
|
|
push @fields, ""; |
|
|
} |
|
|
# If this is a primary relation, add a 0 for the new-record flag (indicating that |
|
|
# this record is not new, but part of the original load). |
|
|
if ($primary) { |
|
|
push @fields, "0"; |
|
|
} |
|
|
# Write the record. |
|
|
$record = join "\t", @fields; |
|
|
print TABLEOUT "$record\n"; |
|
|
# Count the record written. |
|
|
my $count = $retVal->Add('records'); |
|
|
my $len = length $record; |
|
|
Trace("Record $count written with $len characters.") if T(4); |
|
|
} else { |
|
|
# Here we have a blank record. |
|
|
$retVal->Add('skipped'); |
|
|
} |
|
|
} |
|
|
# Close the files. |
|
|
close TABLEIN; |
|
|
close TABLEOUT; |
|
|
Trace("Temporary file $tempName created.") if T(2); |
|
1811 |
# Load the table. |
# Load the table. |
1812 |
my $rv; |
my $rv; |
1813 |
eval { |
eval { |
1814 |
$rv = $dbh->load_table(file => $tempName, tbl => $relationName); |
$rv = $dbh->load_table(file => $fileName, tbl => $relationName); |
1815 |
}; |
}; |
1816 |
if (!defined $rv) { |
if (!defined $rv) { |
1817 |
$retVal->AddMessage($@) if ($@); |
$retVal->AddMessage($@) if ($@); |
1818 |
$retVal->AddMessage("Table load failed for $relationName using $tempName."); |
$retVal->AddMessage("Table load failed for $relationName using $fileName."); |
1819 |
Trace("Table load failed for $relationName.") if T(1); |
Trace("Table load failed for $relationName.") if T(1); |
1820 |
} else { |
} else { |
1821 |
# Here we successfully loaded the table. Trace the number of records loaded. |
# Here we successfully loaded the table. |
1822 |
Trace("$retVal->{records} records read for $relationName.") if T(2); |
$retVal->Add("tables"); |
1823 |
|
my $size = -s $fileName; |
1824 |
|
Trace("$size bytes loaded into $relationName.") if T(2); |
1825 |
# If we're rebuilding, we need to create the table indexes. |
# If we're rebuilding, we need to create the table indexes. |
1826 |
if ($truncateFlag && ! $dbh->{_preIndex}) { |
if ($truncateFlag && ! $dbh->{_preIndex}) { |
1827 |
eval { |
eval { |
1831 |
$retVal->AddMessage($@); |
$retVal->AddMessage($@); |
1832 |
} |
} |
1833 |
} |
} |
|
# Analyze the table to help optimize tables. |
|
1834 |
} |
} |
1835 |
# Commit the database changes. |
# Analyze the table to improve performance. |
|
$dbh->commit_tran; |
|
1836 |
$dbh->vacuum_it($relationName); |
$dbh->vacuum_it($relationName); |
|
# Delete the temporary file. |
|
|
unlink $tempName; |
|
1837 |
# Return the statistics. |
# Return the statistics. |
1838 |
return $retVal; |
return $retVal; |
1839 |
} |
} |
1925 |
# Get the parameters. |
# Get the parameters. |
1926 |
my ($self, $entityType, $ID) = @_; |
my ($self, $entityType, $ID) = @_; |
1927 |
# Create a query. |
# Create a query. |
1928 |
my $query = $self->Get([$entityType], "$entityType(id) = ?", $ID); |
my $query = $self->Get([$entityType], "$entityType(id) = ?", [$ID]); |
1929 |
# Get the first (and only) object. |
# Get the first (and only) object. |
1930 |
my $retVal = $query->Fetch(); |
my $retVal = $query->Fetch(); |
1931 |
# Return the result. |
# Return the result. |
2038 |
# list is a scalar we convert it into a singleton list. |
# list is a scalar we convert it into a singleton list. |
2039 |
my @parmList = (); |
my @parmList = (); |
2040 |
if (ref $parameterList eq "ARRAY") { |
if (ref $parameterList eq "ARRAY") { |
2041 |
|
Trace("GetAll parm list is an array.") if T(4); |
2042 |
@parmList = @{$parameterList}; |
@parmList = @{$parameterList}; |
2043 |
} else { |
} else { |
2044 |
|
Trace("GetAll parm list is a scalar: $parameterList.") if T(4); |
2045 |
push @parmList, $parameterList; |
push @parmList, $parameterList; |
2046 |
} |
} |
|
# Create the query. |
|
|
my $query = $self->Get($objectNames, $filterClause, @parmList); |
|
|
# Set up a counter of the number of records read. |
|
|
my $fetched = 0; |
|
2047 |
# Insure the counter has a value. |
# Insure the counter has a value. |
2048 |
if (!defined $count) { |
if (!defined $count) { |
2049 |
$count = 0; |
$count = 0; |
2050 |
} |
} |
2051 |
|
# Add the row limit to the filter clause. |
2052 |
|
if ($count > 0) { |
2053 |
|
$filterClause .= " LIMIT $count"; |
2054 |
|
} |
2055 |
|
# Create the query. |
2056 |
|
my $query = $self->Get($objectNames, $filterClause, \@parmList); |
2057 |
|
# Set up a counter of the number of records read. |
2058 |
|
my $fetched = 0; |
2059 |
# Loop through the records returned, extracting the fields. Note that if the |
# Loop through the records returned, extracting the fields. Note that if the |
2060 |
# counter is non-zero, we stop when the number of records read hits the count. |
# counter is non-zero, we stop when the number of records read hits the count. |
2061 |
my @retVal = (); |
my @retVal = (); |
2064 |
push @retVal, \@rowData; |
push @retVal, \@rowData; |
2065 |
$fetched++; |
$fetched++; |
2066 |
} |
} |
2067 |
|
Trace("$fetched rows returned in GetAll.") if T(SQL => 4); |
2068 |
# Return the resulting list. |
# Return the resulting list. |
2069 |
return @retVal; |
return @retVal; |
2070 |
} |
} |
2071 |
|
|
2072 |
|
=head3 Exists |
2073 |
|
|
2074 |
|
C<< my $found = $sprout->Exists($entityName, $entityID); >> |
2075 |
|
|
2076 |
|
Return TRUE if an entity exists, else FALSE. |
2077 |
|
|
2078 |
|
=over 4 |
2079 |
|
|
2080 |
|
=item entityName |
2081 |
|
|
2082 |
|
Name of the entity type (e.g. C<Feature>) relevant to the existence check. |
2083 |
|
|
2084 |
|
=item entityID |
2085 |
|
|
2086 |
|
ID of the entity instance whose existence is to be checked. |
2087 |
|
|
2088 |
|
=item RETURN |
2089 |
|
|
2090 |
|
Returns TRUE if the entity instance exists, else FALSE. |
2091 |
|
|
2092 |
|
=back |
2093 |
|
|
2094 |
|
=cut |
2095 |
|
#: Return Type $; |
2096 |
|
sub Exists { |
2097 |
|
# Get the parameters. |
2098 |
|
my ($self, $entityName, $entityID) = @_; |
2099 |
|
# Check for the entity instance. |
2100 |
|
Trace("Checking existence of $entityName with ID=$entityID.") if T(4); |
2101 |
|
my $testInstance = $self->GetEntity($entityName, $entityID); |
2102 |
|
# Return an existence indicator. |
2103 |
|
my $retVal = ($testInstance ? 1 : 0); |
2104 |
|
return $retVal; |
2105 |
|
} |
2106 |
|
|
2107 |
=head3 EstimateRowSize |
=head3 EstimateRowSize |
2108 |
|
|
2109 |
C<< my $rowSize = $erdb->EstimateRowSize($relName); >> |
C<< my $rowSize = $erdb->EstimateRowSize($relName); >> |
2142 |
return $retVal; |
return $retVal; |
2143 |
} |
} |
2144 |
|
|
2145 |
|
=head3 GetFieldTable |
2146 |
|
|
2147 |
|
C<< my $fieldHash = $self->GetFieldTable($objectnName); >> |
2148 |
|
|
2149 |
|
Get the field structure for a specified entity or relationship. |
2150 |
|
|
2151 |
|
=over 4 |
2152 |
|
|
2153 |
|
=item objectName |
2154 |
|
|
2155 |
|
Name of the desired entity or relationship. |
2156 |
|
|
2157 |
|
=item RETURN |
2158 |
|
|
2159 |
|
The table containing the field descriptors for the specified object. |
2160 |
|
|
2161 |
|
=back |
2162 |
|
|
2163 |
|
=cut |
2164 |
|
|
2165 |
|
sub GetFieldTable { |
2166 |
|
# Get the parameters. |
2167 |
|
my ($self, $objectName) = @_; |
2168 |
|
# Get the descriptor from the metadata. |
2169 |
|
my $objectData = $self->_GetStructure($objectName); |
2170 |
|
# Return the object's field table. |
2171 |
|
return $objectData->{Fields}; |
2172 |
|
} |
2173 |
|
|
2174 |
|
=head2 Data Mining Methods |
2175 |
|
|
2176 |
|
=head3 GetUsefulCrossValues |
2177 |
|
|
2178 |
|
C<< my @attrNames = $sprout->GetUsefulCrossValues($sourceEntity, $relationship); >> |
2179 |
|
|
2180 |
|
Return a list of the useful attributes that would be returned by a B<Cross> call |
2181 |
|
from an entity of the source entity type through the specified relationship. This |
2182 |
|
means it will return the fields of the target entity type and the intersection data |
2183 |
|
fields in the relationship. Only primary table fields are returned. In other words, |
2184 |
|
the field names returned will be for fields where there is always one and only one |
2185 |
|
value. |
2186 |
|
|
2187 |
|
=over 4 |
2188 |
|
|
2189 |
|
=item sourceEntity |
2190 |
|
|
2191 |
|
Name of the entity from which the relationship crossing will start. |
2192 |
|
|
2193 |
|
=item relationship |
2194 |
|
|
2195 |
|
Name of the relationship being crossed. |
2196 |
|
|
2197 |
|
=item RETURN |
2198 |
|
|
2199 |
|
Returns a list of field names in Sprout field format (I<objectName>C<(>I<fieldName>C<)>. |
2200 |
|
|
2201 |
|
=back |
2202 |
|
|
2203 |
|
=cut |
2204 |
|
#: Return Type @; |
2205 |
|
sub GetUsefulCrossValues { |
2206 |
|
# Get the parameters. |
2207 |
|
my ($self, $sourceEntity, $relationship) = @_; |
2208 |
|
# Declare the return variable. |
2209 |
|
my @retVal = (); |
2210 |
|
# Determine the target entity for the relationship. This is whichever entity is not |
2211 |
|
# the source entity. So, if the source entity is the FROM, we'll get the name of |
2212 |
|
# the TO, and vice versa. |
2213 |
|
my $relStructure = $self->_GetStructure($relationship); |
2214 |
|
my $targetEntityType = ($relStructure->{from} eq $sourceEntity ? "to" : "from"); |
2215 |
|
my $targetEntity = $relStructure->{$targetEntityType}; |
2216 |
|
# Get the field table for the entity. |
2217 |
|
my $entityFields = $self->GetFieldTable($targetEntity); |
2218 |
|
# The field table is a hash. The hash key is the field name. The hash value is a structure. |
2219 |
|
# For the entity fields, the key aspect of the target structure is that the {relation} value |
2220 |
|
# must match the entity name. |
2221 |
|
my @fieldList = map { "$targetEntity($_)" } grep { $entityFields->{$_}->{relation} eq $targetEntity } |
2222 |
|
keys %{$entityFields}; |
2223 |
|
# Push the fields found onto the return variable. |
2224 |
|
push @retVal, sort @fieldList; |
2225 |
|
# Get the field table for the relationship. |
2226 |
|
my $relationshipFields = $self->GetFieldTable($relationship); |
2227 |
|
# Here we have a different rule. We want all the fields other than "from-link" and "to-link". |
2228 |
|
# This may end up being an empty set. |
2229 |
|
my @fieldList2 = map { "$relationship($_)" } grep { $_ ne "from-link" && $_ ne "to-link" } |
2230 |
|
keys %{$relationshipFields}; |
2231 |
|
# Push these onto the return list. |
2232 |
|
push @retVal, sort @fieldList2; |
2233 |
|
# Return the result. |
2234 |
|
return @retVal; |
2235 |
|
} |
2236 |
|
|
2237 |
|
=head3 FindColumn |
2238 |
|
|
2239 |
|
C<< my $colIndex = ERDB::FindColumn($headerLine, $columnIdentifier); >> |
2240 |
|
|
2241 |
|
Return the location a desired column in a data mining header line. The data |
2242 |
|
mining header line is a tab-separated list of column names. The column |
2243 |
|
identifier is either the numerical index of a column or the actual column |
2244 |
|
name. |
2245 |
|
|
2246 |
|
=over 4 |
2247 |
|
|
2248 |
|
=item headerLine |
2249 |
|
|
2250 |
|
The header line from a data mining command, which consists of a tab-separated |
2251 |
|
list of column names. |
2252 |
|
|
2253 |
|
=item columnIdentifier |
2254 |
|
|
2255 |
|
Either the ordinal number of the desired column (1-based), or the name of the |
2256 |
|
desired column. |
2257 |
|
|
2258 |
|
=item RETURN |
2259 |
|
|
2260 |
|
Returns the array index (0-based) of the desired column. |
2261 |
|
|
2262 |
|
=back |
2263 |
|
|
2264 |
|
=cut |
2265 |
|
|
2266 |
|
sub FindColumn { |
2267 |
|
# Get the parameters. |
2268 |
|
my ($headerLine, $columnIdentifier) = @_; |
2269 |
|
# Declare the return variable. |
2270 |
|
my $retVal; |
2271 |
|
# Split the header line into column names. |
2272 |
|
my @headers = ParseColumns($headerLine); |
2273 |
|
# Determine whether we have a number or a name. |
2274 |
|
if ($columnIdentifier =~ /^\d+$/) { |
2275 |
|
# Here we have a number. Subtract 1 and validate the result. |
2276 |
|
$retVal = $columnIdentifier - 1; |
2277 |
|
if ($retVal < 0 || $retVal > $#headers) { |
2278 |
|
Confess("Invalid column identifer \"$columnIdentifier\": value out of range."); |
2279 |
|
} |
2280 |
|
} else { |
2281 |
|
# Here we have a name. We need to find it in the list. |
2282 |
|
for (my $i = 0; $i <= $#headers && ! defined($retVal); $i++) { |
2283 |
|
if ($headers[$i] eq $columnIdentifier) { |
2284 |
|
$retVal = $i; |
2285 |
|
} |
2286 |
|
} |
2287 |
|
if (! defined($retVal)) { |
2288 |
|
Confess("Invalid column identifier \"$columnIdentifier\": value not found."); |
2289 |
|
} |
2290 |
|
} |
2291 |
|
# Return the result. |
2292 |
|
return $retVal; |
2293 |
|
} |
2294 |
|
|
2295 |
|
=head3 ParseColumns |
2296 |
|
|
2297 |
|
C<< my @columns = ERDB::ParseColumns($line); >> |
2298 |
|
|
2299 |
|
Convert the specified data line to a list of columns. |
2300 |
|
|
2301 |
|
=over 4 |
2302 |
|
|
2303 |
|
=item line |
2304 |
|
|
2305 |
|
A data mining input, consisting of a tab-separated list of columns terminated by a |
2306 |
|
new-line. |
2307 |
|
|
2308 |
|
=item RETURN |
2309 |
|
|
2310 |
|
Returns a list consisting of the column values. |
2311 |
|
|
2312 |
|
=back |
2313 |
|
|
2314 |
|
=cut |
2315 |
|
|
2316 |
|
sub ParseColumns { |
2317 |
|
# Get the parameters. |
2318 |
|
my ($line) = @_; |
2319 |
|
# Chop off the line-end. |
2320 |
|
chomp $line; |
2321 |
|
# Split it into a list. |
2322 |
|
my @retVal = split(/\t/, $line); |
2323 |
|
# Return the result. |
2324 |
|
return @retVal; |
2325 |
|
} |
2326 |
|
|
2327 |
=head2 Internal Utility Methods |
=head2 Internal Utility Methods |
2328 |
|
|
2329 |
|
=head3 SetupSQL |
2330 |
|
|
2331 |
|
Process a list of object names and a filter clause so that they can be used to |
2332 |
|
build an SQL statement. This method takes in a reference to a list of object names |
2333 |
|
and a filter clause. It will return a corrected filter clause, a list of mapped |
2334 |
|
names and the mapped name hash. |
2335 |
|
|
2336 |
|
This is an instance method. |
2337 |
|
|
2338 |
|
=over 4 |
2339 |
|
|
2340 |
|
=item objectNames |
2341 |
|
|
2342 |
|
Reference to a list of the object names to be included in the query. |
2343 |
|
|
2344 |
|
=item filterClause |
2345 |
|
|
2346 |
|
A string containing the WHERE clause for the query (without the C<WHERE>) and also |
2347 |
|
optionally the C<ORDER BY> and C<LIMIT> clauses. |
2348 |
|
|
2349 |
|
=item RETURN |
2350 |
|
|
2351 |
|
Returns a three-element list. The first element is the SQL statement suffix, beginning |
2352 |
|
with the FROM clause. The second element is a reference to a list of the names to be |
2353 |
|
used in retrieving the fields. The third element is a hash mapping the names to the |
2354 |
|
objects they represent. |
2355 |
|
|
2356 |
|
=back |
2357 |
|
|
2358 |
|
=cut |
2359 |
|
|
2360 |
|
sub _SetupSQL { |
2361 |
|
my ($self, $objectNames, $filterClause) = @_; |
2362 |
|
# Adjust the list of object names to account for multiple occurrences of the |
2363 |
|
# same object. We start with a hash table keyed on object name that will |
2364 |
|
# return the object suffix. The first time an object is encountered it will |
2365 |
|
# not be found in the hash. The next time the hash will map the object name |
2366 |
|
# to 2, then 3, and so forth. |
2367 |
|
my %objectHash = (); |
2368 |
|
# This list will contain the object names as they are to appear in the |
2369 |
|
# FROM list. |
2370 |
|
my @fromList = (); |
2371 |
|
# This list contains the suffixed object name for each object. It is exactly |
2372 |
|
# parallel to the list in the $objectNames parameter. |
2373 |
|
my @mappedNameList = (); |
2374 |
|
# Finally, this hash translates from a mapped name to its original object name. |
2375 |
|
my %mappedNameHash = (); |
2376 |
|
# Now we create the lists. Note that for every single name we push something into |
2377 |
|
# @fromList and @mappedNameList. This insures that those two arrays are exactly |
2378 |
|
# parallel to $objectNames. |
2379 |
|
for my $objectName (@{$objectNames}) { |
2380 |
|
# Get the next suffix for this object. |
2381 |
|
my $suffix = $objectHash{$objectName}; |
2382 |
|
if (! $suffix) { |
2383 |
|
# Here we are seeing the object for the first time. The object name |
2384 |
|
# is used as is. |
2385 |
|
push @mappedNameList, $objectName; |
2386 |
|
push @fromList, $objectName; |
2387 |
|
$mappedNameHash{$objectName} = $objectName; |
2388 |
|
# Denote the next suffix will be 2. |
2389 |
|
$objectHash{$objectName} = 2; |
2390 |
|
} else { |
2391 |
|
# Here we've seen the object before. We construct a new name using |
2392 |
|
# the suffix from the hash and update the hash. |
2393 |
|
my $mappedName = "$objectName$suffix"; |
2394 |
|
$objectHash{$objectName} = $suffix + 1; |
2395 |
|
# The FROM list has the object name followed by the mapped name. This |
2396 |
|
# tells SQL it's still the same table, but we're using a different name |
2397 |
|
# for it to avoid confusion. |
2398 |
|
push @fromList, "$objectName $mappedName"; |
2399 |
|
# The mapped-name list contains the real mapped name. |
2400 |
|
push @mappedNameList, $mappedName; |
2401 |
|
# Finally, enable us to get back from the mapped name to the object name. |
2402 |
|
$mappedNameHash{$mappedName} = $objectName; |
2403 |
|
} |
2404 |
|
} |
2405 |
|
# Begin the SELECT suffix. It starts with |
2406 |
|
# |
2407 |
|
# FROM name1, name2, ... nameN |
2408 |
|
# |
2409 |
|
my $suffix = "FROM " . join(', ', @fromList); |
2410 |
|
# Check for a filter clause. |
2411 |
|
if ($filterClause) { |
2412 |
|
# Here we have one, so we convert its field names and add it to the query. First, |
2413 |
|
# We create a copy of the filter string we can work with. |
2414 |
|
my $filterString = $filterClause; |
2415 |
|
# Next, we sort the object names by length. This helps protect us from finding |
2416 |
|
# object names inside other object names when we're doing our search and replace. |
2417 |
|
my @sortedNames = sort { length($b) - length($a) } @mappedNameList; |
2418 |
|
# We will also keep a list of conditions to add to the WHERE clause in order to link |
2419 |
|
# entities and relationships as well as primary relations to secondary ones. |
2420 |
|
my @joinWhere = (); |
2421 |
|
# The final preparatory step is to create a hash table of relation names. The |
2422 |
|
# table begins with the relation names already in the SELECT command. We may |
2423 |
|
# need to add relations later if there is filtering on a field in a secondary |
2424 |
|
# relation. The secondary relations are the ones that contain multiply- |
2425 |
|
# occurring or optional fields. |
2426 |
|
my %fromNames = map { $_ => 1 } @sortedNames; |
2427 |
|
# We are ready to begin. We loop through the object names, replacing each |
2428 |
|
# object name's field references by the corresponding SQL field reference. |
2429 |
|
# Along the way, if we find a secondary relation, we will need to add it |
2430 |
|
# to the FROM clause. |
2431 |
|
for my $mappedName (@sortedNames) { |
2432 |
|
# Get the length of the object name plus 2. This is the value we add to the |
2433 |
|
# size of the field name to determine the size of the field reference as a |
2434 |
|
# whole. |
2435 |
|
my $nameLength = 2 + length $mappedName; |
2436 |
|
# Get the real object name for this mapped name. |
2437 |
|
my $objectName = $mappedNameHash{$mappedName}; |
2438 |
|
Trace("Processing $mappedName for object $objectName.") if T(4); |
2439 |
|
# Get the object's field list. |
2440 |
|
my $fieldList = $self->GetFieldTable($objectName); |
2441 |
|
# Find the field references for this object. |
2442 |
|
while ($filterString =~ m/$mappedName\(([^)]*)\)/g) { |
2443 |
|
# At this point, $1 contains the field name, and the current position |
2444 |
|
# is set immediately after the final parenthesis. We pull out the name of |
2445 |
|
# the field and the position and length of the field reference as a whole. |
2446 |
|
my $fieldName = $1; |
2447 |
|
my $len = $nameLength + length $fieldName; |
2448 |
|
my $pos = pos($filterString) - $len; |
2449 |
|
# Insure the field exists. |
2450 |
|
if (!exists $fieldList->{$fieldName}) { |
2451 |
|
Confess("Field $fieldName not found for object $objectName."); |
2452 |
|
} else { |
2453 |
|
Trace("Processing $fieldName at position $pos.") if T(4); |
2454 |
|
# Get the field's relation. |
2455 |
|
my $relationName = $fieldList->{$fieldName}->{relation}; |
2456 |
|
# Now we have a secondary relation. We need to insure it matches the |
2457 |
|
# mapped name of the primary relation. First we peel off the suffix |
2458 |
|
# from the mapped name. |
2459 |
|
my $mappingSuffix = substr $mappedName, length($objectName); |
2460 |
|
# Put the mapping suffix onto the relation name to get the |
2461 |
|
# mapped relation name. |
2462 |
|
my $mappedRelationName = "$relationName$mappingSuffix"; |
2463 |
|
# Insure the relation is in the FROM clause. |
2464 |
|
if (!exists $fromNames{$mappedRelationName}) { |
2465 |
|
# Add the relation to the FROM clause. |
2466 |
|
if ($mappedRelationName eq $relationName) { |
2467 |
|
# The name is un-mapped, so we add it without |
2468 |
|
# any frills. |
2469 |
|
$suffix .= ", $relationName"; |
2470 |
|
push @joinWhere, "$objectName.id = $relationName.id"; |
2471 |
|
} else { |
2472 |
|
# Here we have a mapping situation. |
2473 |
|
$suffix .= ", $relationName $mappedRelationName"; |
2474 |
|
push @joinWhere, "$mappedRelationName.id = $mappedName.id"; |
2475 |
|
} |
2476 |
|
# Denote we have this relation available for future fields. |
2477 |
|
$fromNames{$mappedRelationName} = 1; |
2478 |
|
} |
2479 |
|
# Form an SQL field reference from the relation name and the field name. |
2480 |
|
my $sqlReference = "$mappedRelationName." . _FixName($fieldName); |
2481 |
|
# Put it into the filter string in place of the old value. |
2482 |
|
substr($filterString, $pos, $len) = $sqlReference; |
2483 |
|
# Reposition the search. |
2484 |
|
pos $filterString = $pos + length $sqlReference; |
2485 |
|
} |
2486 |
|
} |
2487 |
|
} |
2488 |
|
# The next step is to join the objects together. We only need to do this if there |
2489 |
|
# is more than one object in the object list. We start with the first object and |
2490 |
|
# run through the objects after it. Note also that we make a safety copy of the |
2491 |
|
# list before running through it. |
2492 |
|
my @mappedObjectList = @mappedNameList; |
2493 |
|
my $lastMappedObject = shift @mappedObjectList; |
2494 |
|
# Get the join table. |
2495 |
|
my $joinTable = $self->{_metaData}->{Joins}; |
2496 |
|
# Loop through the object list. |
2497 |
|
for my $thisMappedObject (@mappedObjectList) { |
2498 |
|
# Look for a join using the real object names. |
2499 |
|
my $lastObject = $mappedNameHash{$lastMappedObject}; |
2500 |
|
my $thisObject = $mappedNameHash{$thisMappedObject}; |
2501 |
|
my $joinKey = "$lastObject/$thisObject"; |
2502 |
|
if (!exists $joinTable->{$joinKey}) { |
2503 |
|
# Here there's no join, so we throw an error. |
2504 |
|
Confess("No join exists to connect from $lastMappedObject to $thisMappedObject."); |
2505 |
|
} else { |
2506 |
|
# Get the join clause. |
2507 |
|
my $unMappedJoin = $joinTable->{$joinKey}; |
2508 |
|
# Fix the names. |
2509 |
|
$unMappedJoin =~ s/$lastObject/$lastMappedObject/; |
2510 |
|
$unMappedJoin =~ s/$thisObject/$thisMappedObject/; |
2511 |
|
push @joinWhere, $unMappedJoin; |
2512 |
|
# Save this object as the last object for the next iteration. |
2513 |
|
$lastMappedObject = $thisMappedObject; |
2514 |
|
} |
2515 |
|
} |
2516 |
|
# Now we need to handle the whole ORDER BY / LIMIT thing. The important part |
2517 |
|
# here is we want the filter clause to be empty if there's no WHERE filter. |
2518 |
|
# We'll put the ORDER BY / LIMIT clauses in the following variable. |
2519 |
|
my $orderClause = ""; |
2520 |
|
# Locate the ORDER BY or LIMIT verbs (if any). We use a non-greedy |
2521 |
|
# operator so that we find the first occurrence of either verb. |
2522 |
|
if ($filterString =~ m/^(.*?)\s*(ORDER BY|LIMIT)/g) { |
2523 |
|
# Here we have an ORDER BY or LIMIT verb. Split it off of the filter string. |
2524 |
|
my $pos = pos $filterString; |
2525 |
|
$orderClause = $2 . substr($filterString, $pos); |
2526 |
|
$filterString = $1; |
2527 |
|
} |
2528 |
|
# Add the filter and the join clauses (if any) to the SELECT command. |
2529 |
|
if ($filterString) { |
2530 |
|
Trace("Filter string is \"$filterString\".") if T(4); |
2531 |
|
push @joinWhere, "($filterString)"; |
2532 |
|
} |
2533 |
|
if (@joinWhere) { |
2534 |
|
$suffix .= " WHERE " . join(' AND ', @joinWhere); |
2535 |
|
} |
2536 |
|
# Add the sort or limit clause (if any) to the SELECT command. |
2537 |
|
if ($orderClause) { |
2538 |
|
$suffix .= " $orderClause"; |
2539 |
|
} |
2540 |
|
} |
2541 |
|
# Return the suffix, the mapped name list, and the mapped name hash. |
2542 |
|
return ($suffix, \@mappedNameList, \%mappedNameHash); |
2543 |
|
} |
2544 |
|
|
2545 |
|
=head3 GetStatementHandle |
2546 |
|
|
2547 |
|
This method will prepare and execute an SQL query, returning the statement handle. |
2548 |
|
The main reason for doing this here is so that everybody who does SQL queries gets |
2549 |
|
the benefit of tracing. |
2550 |
|
|
2551 |
|
This is an instance method. |
2552 |
|
|
2553 |
|
=over 4 |
2554 |
|
|
2555 |
|
=item command |
2556 |
|
|
2557 |
|
Command to prepare and execute. |
2558 |
|
|
2559 |
|
=item params |
2560 |
|
|
2561 |
|
Reference to a list of the values to be substituted in for the parameter marks. |
2562 |
|
|
2563 |
|
=item RETURN |
2564 |
|
|
2565 |
|
Returns a prepared and executed statement handle from which the caller can extract |
2566 |
|
results. |
2567 |
|
|
2568 |
|
=back |
2569 |
|
|
2570 |
|
=cut |
2571 |
|
|
2572 |
|
sub _GetStatementHandle { |
2573 |
|
# Get the parameters. |
2574 |
|
my ($self, $command, $params) = @_; |
2575 |
|
# Trace the query. |
2576 |
|
Trace("SQL query: $command") if T(SQL => 3); |
2577 |
|
Trace("PARMS: '" . (join "', '", @{$params}) . "'") if (T(SQL => 4) && (@{$params} > 0)); |
2578 |
|
# Get the database handle. |
2579 |
|
my $dbh = $self->{_dbh}; |
2580 |
|
# Prepare the command. |
2581 |
|
my $sth = $dbh->prepare_command($command); |
2582 |
|
# Execute it with the parameters bound in. |
2583 |
|
$sth->execute(@{$params}) || Confess("SELECT error" . $sth->errstr()); |
2584 |
|
# Return the statement handle. |
2585 |
|
return $sth; |
2586 |
|
} |
2587 |
|
|
2588 |
=head3 GetLoadStats |
=head3 GetLoadStats |
2589 |
|
|
2590 |
Return a blank statistics object for use by the load methods. |
Return a blank statistics object for use by the load methods. |
2594 |
=cut |
=cut |
2595 |
|
|
2596 |
sub _GetLoadStats { |
sub _GetLoadStats { |
2597 |
return Stats->new('records'); |
return Stats->new(); |
2598 |
} |
} |
2599 |
|
|
2600 |
=head3 GenerateFields |
=head3 GenerateFields |
2789 |
return $objectData->{Relations}; |
return $objectData->{Relations}; |
2790 |
} |
} |
2791 |
|
|
|
=head3 GetFieldTable |
|
|
|
|
|
Get the field structure for a specified entity or relationship. |
|
|
|
|
|
This is an instance method. |
|
|
|
|
|
=over 4 |
|
|
|
|
|
=item objectName |
|
|
|
|
|
Name of the desired entity or relationship. |
|
|
|
|
|
=item RETURN |
|
|
|
|
|
The table containing the field descriptors for the specified object. |
|
|
|
|
|
=back |
|
|
|
|
|
=cut |
|
|
|
|
|
sub _GetFieldTable { |
|
|
# Get the parameters. |
|
|
my ($self, $objectName) = @_; |
|
|
# Get the descriptor from the metadata. |
|
|
my $objectData = $self->_GetStructure($objectName); |
|
|
# Return the object's field table. |
|
|
return $objectData->{Fields}; |
|
|
} |
|
|
|
|
2792 |
=head3 ValidateFieldNames |
=head3 ValidateFieldNames |
2793 |
|
|
2794 |
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 |
3136 |
my @fromList = (); |
my @fromList = (); |
3137 |
my @toList = (); |
my @toList = (); |
3138 |
my @bothList = (); |
my @bothList = (); |
3139 |
Trace("Join table build for $entityName.") if T(4); |
Trace("Join table build for $entityName.") if T(metadata => 4); |
3140 |
for my $relationshipName (keys %{$relationshipList}) { |
for my $relationshipName (keys %{$relationshipList}) { |
3141 |
my $relationship = $relationshipList->{$relationshipName}; |
my $relationship = $relationshipList->{$relationshipName}; |
3142 |
# Determine if this relationship has our entity in one of its link fields. |
# Determine if this relationship has our entity in one of its link fields. |
3143 |
my $fromEntity = $relationship->{from}; |
my $fromEntity = $relationship->{from}; |
3144 |
my $toEntity = $relationship->{to}; |
my $toEntity = $relationship->{to}; |
3145 |
Trace("Join check for relationship $relationshipName from $fromEntity to $toEntity.") if T(4); |
Trace("Join check for relationship $relationshipName from $fromEntity to $toEntity.") if T(Joins => 4); |
3146 |
if ($fromEntity eq $entityName) { |
if ($fromEntity eq $entityName) { |
3147 |
if ($toEntity eq $entityName) { |
if ($toEntity eq $entityName) { |
3148 |
# Here the relationship is recursive. |
# Here the relationship is recursive. |
3149 |
push @bothList, $relationshipName; |
push @bothList, $relationshipName; |
3150 |
Trace("Relationship $relationshipName put in both-list.") if T(4); |
Trace("Relationship $relationshipName put in both-list.") if T(metadata => 4); |
3151 |
} else { |
} else { |
3152 |
# Here the relationship comes from the entity. |
# Here the relationship comes from the entity. |
3153 |
push @fromList, $relationshipName; |
push @fromList, $relationshipName; |
3154 |
Trace("Relationship $relationshipName put in from-list.") if T(4); |
Trace("Relationship $relationshipName put in from-list.") if T(metadata => 4); |
3155 |
} |
} |
3156 |
} elsif ($toEntity eq $entityName) { |
} elsif ($toEntity eq $entityName) { |
3157 |
# Here the relationship goes to the entity. |
# Here the relationship goes to the entity. |
3158 |
push @toList, $relationshipName; |
push @toList, $relationshipName; |
3159 |
Trace("Relationship $relationshipName put in to-list.") if T(4); |
Trace("Relationship $relationshipName put in to-list.") if T(metadata => 4); |
3160 |
} |
} |
3161 |
} |
} |
3162 |
# Create the nonrecursive joins. Note that we build two hashes for running |
# Create the nonrecursive joins. Note that we build two hashes for running |
3172 |
# Create joins between the entity and this relationship. |
# Create joins between the entity and this relationship. |
3173 |
my $linkField = "$relationshipName.${linkType}_link"; |
my $linkField = "$relationshipName.${linkType}_link"; |
3174 |
my $joinClause = "$entityName.id = $linkField"; |
my $joinClause = "$entityName.id = $linkField"; |
3175 |
Trace("Entity join clause is $joinClause for $entityName and $relationshipName.") if T(4); |
Trace("Entity join clause is $joinClause for $entityName and $relationshipName.") if T(metadata => 4); |
3176 |
$joinTable{"$entityName/$relationshipName"} = $joinClause; |
$joinTable{"$entityName/$relationshipName"} = $joinClause; |
3177 |
$joinTable{"$relationshipName/$entityName"} = $joinClause; |
$joinTable{"$relationshipName/$entityName"} = $joinClause; |
3178 |
# Create joins between this relationship and the other relationships. |
# Create joins between this relationship and the other relationships. |
3193 |
# relationship and itself are prohibited. |
# relationship and itself are prohibited. |
3194 |
my $relJoinClause = "$otherName.${otherType}_link = $linkField"; |
my $relJoinClause = "$otherName.${otherType}_link = $linkField"; |
3195 |
$joinTable{$joinKey} = $relJoinClause; |
$joinTable{$joinKey} = $relJoinClause; |
3196 |
Trace("Relationship join clause is $relJoinClause for $joinKey.") if T(4); |
Trace("Relationship join clause is $relJoinClause for $joinKey.") if T(metadata => 4); |
3197 |
} |
} |
3198 |
} |
} |
3199 |
} |
} |
3202 |
# relationship can only be ambiguous with another recursive relationship, |
# relationship can only be ambiguous with another recursive relationship, |
3203 |
# and the incoming relationship from the outer loop is never recursive. |
# and the incoming relationship from the outer loop is never recursive. |
3204 |
for my $otherName (@bothList) { |
for my $otherName (@bothList) { |
3205 |
Trace("Setting up relationship joins to recursive relationship $otherName with $relationshipName.") if T(4); |
Trace("Setting up relationship joins to recursive relationship $otherName with $relationshipName.") if T(metadata => 4); |
3206 |
# Join from the left. |
# Join from the left. |
3207 |
$joinTable{"$relationshipName/$otherName"} = |
$joinTable{"$relationshipName/$otherName"} = |
3208 |
"$linkField = $otherName.from_link"; |
"$linkField = $otherName.from_link"; |
3217 |
# rise to situations where we can't create the path we want; however, it is always |
# rise to situations where we can't create the path we want; however, it is always |
3218 |
# possible to get the same effect using multiple queries. |
# possible to get the same effect using multiple queries. |
3219 |
for my $relationshipName (@bothList) { |
for my $relationshipName (@bothList) { |
3220 |
Trace("Setting up entity joins to recursive relationship $relationshipName with $entityName.") if T(4); |
Trace("Setting up entity joins to recursive relationship $relationshipName with $entityName.") if T(metadata => 4); |
3221 |
# Join to the entity from each direction. |
# Join to the entity from each direction. |
3222 |
$joinTable{"$entityName/$relationshipName"} = |
$joinTable{"$entityName/$relationshipName"} = |
3223 |
"$entityName.id = $relationshipName.from_link"; |
"$entityName.id = $relationshipName.from_link"; |
3231 |
return $metadata; |
return $metadata; |
3232 |
} |
} |
3233 |
|
|
3234 |
|
=head3 SortNeeded |
3235 |
|
|
3236 |
|
C<< my $flag = $erdb->SortNeeded($relationName); >> |
3237 |
|
|
3238 |
|
Return TRUE if the specified relation should be sorted during loading to remove duplicate keys, |
3239 |
|
else FALSE. |
3240 |
|
|
3241 |
|
=over 4 |
3242 |
|
|
3243 |
|
=item relationName |
3244 |
|
|
3245 |
|
Name of the relation to be examined. |
3246 |
|
|
3247 |
|
=item RETURN |
3248 |
|
|
3249 |
|
Returns TRUE if the relation needs a sort, else FALSE. |
3250 |
|
|
3251 |
|
=back |
3252 |
|
|
3253 |
|
=cut |
3254 |
|
#: Return Type $; |
3255 |
|
sub SortNeeded { |
3256 |
|
# Get the parameters. |
3257 |
|
my ($self, $relationName) = @_; |
3258 |
|
# Declare the return variable. |
3259 |
|
my $retVal = 0; |
3260 |
|
# Find out if the relation is a primary entity relation. |
3261 |
|
my $entityTable = $self->{_metaData}->{Entities}; |
3262 |
|
if (exists $entityTable->{$relationName}) { |
3263 |
|
my $keyType = $entityTable->{$relationName}->{keyType}; |
3264 |
|
Trace("Relation $relationName found in entity table with key type $keyType.") if T(3); |
3265 |
|
# If the key is not a hash string, we must do the sort. |
3266 |
|
if ($keyType ne 'hash-string') { |
3267 |
|
$retVal = 1; |
3268 |
|
} |
3269 |
|
} |
3270 |
|
# Return the result. |
3271 |
|
return $retVal; |
3272 |
|
} |
3273 |
|
|
3274 |
=head3 CreateRelationshipIndex |
=head3 CreateRelationshipIndex |
3275 |
|
|
3276 |
Create an index for a relationship's relation. |
Create an index for a relationship's relation. |