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

Diff of /Sprout/ERDB.pm

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

revision 1.6, Wed May 4 03:24:43 2005 UTC revision 1.20, Sun Sep 11 17:02:33 2005 UTC
# Line 2  Line 2 
2    
3          use strict;          use strict;
4          use Tracer;          use Tracer;
5          use DBKernel;      use DBrtns;
6          use Data::Dumper;          use Data::Dumper;
7          use XML::Simple;          use XML::Simple;
8          use DBQuery;          use DBQuery;
9          use DBObject;          use DBObject;
10          use Stats;          use Stats;
11          use Time::HiRes qw(gettimeofday);          use Time::HiRes qw(gettimeofday);
12        use FIG;
13    
14  =head1 Entity-Relationship Database Package  =head1 Entity-Relationship Database Package
15    
# Line 32  Line 33 
33  relation that contains two fields-- the feature ID (C<id>) and the alias name (C<alias>).  relation that contains two fields-- the feature ID (C<id>) and the alias name (C<alias>).
34  The B<FEATURE> entity also contains an optional virulence number. This is implemented  The B<FEATURE> entity also contains an optional virulence number. This is implemented
35  as a separate relation C<FeatureVirulence> which contains an ID (C<id>) and a virulence number  as a separate relation C<FeatureVirulence> which contains an ID (C<id>) and a virulence number
36  (C<virulence>). If the virulence of a feature I<ABC> is known to be 6, there will be one row in the  (C<virulence>). If the virulence of a feature I<ABC> is known to be 6, there will be one row in
37  C<FeatureVirulence> relation possessing the value I<ABC> as its ID and 6 as its virulence number.  the C<FeatureVirulence> relation possessing the value I<ABC> as its ID and 6 as its virulence
38  If the virulence of I<ABC> is not known, there will not be any rows for it in C<FeatureVirulence>.  number. If the virulence of I<ABC> is not known, there will not be any rows for it in
39    C<FeatureVirulence>.
40    
41  Entities are connected by binary relationships implemented using single relations possessing the  Entities are connected by binary relationships implemented using single relations possessing the
42  same name as the relationship itself and that has an I<arity> of 1-to-1 (C<11>), 1-to-many (C<1M>),  same name as the relationship itself and that has an I<arity> of 1-to-1 (C<11>), 1-to-many (C<1M>),
# Line 69  Line 71 
71  is described in the L</GenerateEntity> and L</GenerateConnection> methods, though it is not yet  is described in the L</GenerateEntity> and L</GenerateConnection> methods, though it is not yet
72  fully implemented.  fully implemented.
73    
74    =head2 XML Database Description
75    
76    =head3 Data Types
77    
78    The ERDB system supports the following data types. Note that there are numerous string
79    types depending on the maximum length. Some database packages limit the total number of
80    characters you have in an index key; to insure the database works in all environments,
81    the type of string should be the shortest one possible that supports all the known values.
82    
83    =over 4
84    
85    =item char
86    
87    single ASCII character
88    
89    =item int
90    
91    32-bit signed integer
92    
93    =item date
94    
95    64-bit unsigned integer, representing a PERL date/time value
96    
97    =item text
98    
99    long string; Text fields cannot be used in indexes or sorting and do not support the
100    normal syntax of filter clauses, but can be up to a billion character in length
101    
102    =item float
103    
104    double-precision floating-point number
105    
106    =item boolean
107    
108    single-bit numeric value; The value is stored as a 16-bit signed integer (for
109    compatability with certain database packages), but the only values supported are
110    0 and 1.
111    
112    =item key-string
113    
114    variable-length string, maximum 40 characters
115    
116    =item name-string
117    
118    variable-length string, maximum 80 characters
119    
120    =item medium-string
121    
122    variable-length string, maximum 160 characters
123    
124    =item string
125    
126    variable-length string, maximum 255 characters
127    
128    =back
129    
130    =head3 Global Tags
131    
132    The entire database definition must be inside a B<Database> tag. The display name of
133    the database is given by the text associated with the B<Title> tag. The display name
134    is only used in the automated documentation. It has no other effect. The entities and
135    relationships are listed inside the B<Entities> and B<Relationships> tags,
136    respectively. None of these tags have attributes.
137    
138        <Database>
139            <Title>... display title here...</Title>
140            <Entities>
141                ... entity definitions here ...
142            </Entities>
143            <Relationships>
144                ... relationship definitions here...
145            </Relationships>
146        </Database>
147    
148    Entities, relationships, indexes, and fields all allow a text tag called B<Notes>.
149    The text inside the B<Notes> tag contains comments that will appear when the database
150    documentation is generated. Within a B<Notes> tag, you may use C<[i]> and C<[/i]> for
151    italics, C<[b]> and C<[/b]> for bold, and C<[p]> for a new paragraph.
152    
153    =head3 Fields
154    
155    Both entities and relationships have fields described by B<Field> tags. A B<Field>
156    tag can have B<Notes> associated with it. The complete set of B<Field> tags for an
157    object mus be inside B<Fields> tags.
158    
159        <Entity ... >
160            <Fields>
161                ... Field tags ...
162            </Fields>
163        </Entity>
164    
165    The attributes for the B<Field> tag are as follows.
166    
167    =over 4
168    
169    =item name
170    
171    Name of the field. The field name should contain only letters, digits, and hyphens (C<->),
172    and the first character should be a letter. Most underlying databases are case-insensitive
173    with the respect to field names, so a best practice is to use lower-case letters only.
174    
175    =item type
176    
177    Data type of the field. The legal data types are given above.
178    
179    =item relation
180    
181    Name of the relation containing the field. This should only be specified for entity
182    fields. The ERDB system does not support optional fields or multi-occurring fields
183    in the primary relation of an entity. Instead, they are put into secondary relations.
184    So, for example, in the C<Genome> entity, the C<group-name> field indicates a special
185    grouping used to select a subset of the genomes. A given genome may not be in any
186    groups or may be in multiple groups. Therefore, C<group-name> specifies a relation
187    value. The relation name specified must be a valid table name. By convention, it is
188    usually the entity name followed by a qualifying word (e.g. C<GenomeGroup>). In an
189    entity, the fields without a relation attribute are said to belong to the
190    I<primary relation>. This relation has the same name as the entity itself.
191    
192    =back
193    
194    =head3 Indexes
195    
196    An entity can have multiple alternate indexes associated with it. The fields must
197    be from the primary relation. The alternate indexes assist in ordering results
198    from a query. A relationship can have up to two indexes-- a I<to-index> and a
199    I<from-index>. These order the results when crossing the relationship. For
200    example, in the relationship C<HasContig> from C<Genome> to C<Contig>, the
201    from-index would order the contigs of a ganome, and the to-index would order
202    the genomes of a contig. A relationship's index must specify only fields in
203    the relationship.
204    
205    The indexes for an entity must be listed inside the B<Indexes> tag. The from-index
206    of a relationship is specified using the B<FromIndex> tag; the to-index is specified
207    using the B<ToIndex> tag.
208    
209    Each index can contain a B<Notes> tag. In addition, it will have an B<IndexFields>
210    tag containing the B<IndexField> tags. These specify, in order, the fields used in
211    the index. The attributes of an B<IndexField> tag are as follows.
212    
213    =over 4
214    
215    =item name
216    
217    Name of the field.
218    
219    =item order
220    
221    Sort order of the field-- C<ascending> or C<descending>.
222    
223    =back
224    
225    The B<Index>, B<FromIndex>, and B<ToIndex> tags themselves have no attributes.
226    
227    =head3 Object and Field Names
228    
229    By convention entity and relationship names use capital casing (e.g. C<Genome> or
230    C<HasRegionsIn>. Most underlying databases, however, are aggressively case-insensitive
231    with respect to relation names, converting them internally to all-upper case or
232    all-lower case.
233    
234    If syntax or parsing errors occur when you try to load or use an ERDB database, the
235    most likely reason is that one of your objects has an SQL reserved word as its name.
236    The list of SQL reserved words keeps increasing; however, most are unlikely to show
237    up as a noun or declarative verb phrase. The exceptions are C<Group>, C<User>,
238    C<Table>, C<Index>, C<Object>, C<Date>, C<Number>, C<Update>, C<Time>, C<Percent>,
239    C<Memo>, C<Order>, and C<Sum>. This problem can crop up in field names as well.
240    
241    Every entity has a field called C<id> that acts as its primary key. Every relationship
242    has fields called C<from-link> and C<to-link> that contain copies of the relevant
243    entity IDs. These are essentially ERDB's reserved words, and should not be used
244    for user-defined field names.
245    
246    =head3 Entities
247    
248    An entity is described by the B<Entity> tag. The entity can contain B<Notes>, an
249    B<Indexes> tag containing one or more secondary indexes, and a B<Fields> tag
250    containing one or more fields. The attributes of the B<Entity> tag are as follows.
251    
252    =over 4
253    
254    =item name
255    
256    Name of the entity. The entity name, by convention, uses capital casing (e.g. C<Genome>
257    or C<GroupBlock>) and should be a noun or noun phrase.
258    
259    =item keyType
260    
261    Data type of the primary key. The primary key is always named C<id>.
262    
263    =back
264    
265    =head3 Relationships
266    
267    A relationship is described by the C<Relationship> tag. Within a relationship,
268    there can be a C<Notes> tag, a C<Fields> tag containing the intersection data
269    fields, a C<FromIndex> tag containing the from-index, and a C<ToIndex> tag containing
270    the to-index.
271    
272    The C<Relationship> tag has the following attributes.
273    
274    =over 4
275    
276    =item name
277    
278    Name of the relationship. The relationship name, by convention, uses capital casing
279    (e.g. C<ContainsRegionIn> or C<HasContig>), and should be a declarative verb
280    phrase, designed to fit between the from-entity and the to-entity (e.g.
281    Block C<ContainsRegionIn> Genome).
282    
283    =item from
284    
285    Name of the entity from which the relationship starts.
286    
287    =item to
288    
289    Name of the entity to which the relationship proceeds.
290    
291    =item arity
292    
293    Relationship type: C<1M> for one-to-many and C<MM> for many-to-many.
294    
295    =back
296    
297  =cut  =cut
298    
299  # GLOBALS  # GLOBALS
# Line 76  Line 301 
301  # Table of information about our datatypes. "sqlType" is the corresponding SQL datatype string.  # Table of information about our datatypes. "sqlType" is the corresponding SQL datatype string.
302  # "maxLen" is the maximum permissible length of the incoming string data used to populate a field  # "maxLen" is the maximum permissible length of the incoming string data used to populate a field
303  # of the specified type. "dataGen" is PERL string that will be evaluated if no test data generation  # of the specified type. "dataGen" is PERL string that will be evaluated if no test data generation
304   #string is specified in the field definition.  # string is specified in the field definition. "avgLen" is the average byte length for estimating
305  my %TypeTable = ( char =>        { sqlType => 'CHAR(1)',                        maxLen => 1,                    dataGen => "StringGen('A')" },  # record sizes.
306                                    int =>         { sqlType => 'INTEGER',                        maxLen => 20,                   dataGen => "IntGen(0, 99999999)" },  my %TypeTable = ( char =>    { sqlType => 'CHAR(1)',            maxLen => 1,            avgLen =>   1, dataGen => "StringGen('A')" },
307                                    string =>  { sqlType => 'VARCHAR(255)',               maxLen => 255,                  dataGen => "StringGen(IntGen(10,250))" },                    int =>     { sqlType => 'INTEGER',            maxLen => 20,           avgLen =>   4, dataGen => "IntGen(0, 99999999)" },
308                                    text =>        { sqlType => 'TEXT',                           maxLen => 1000000000,   dataGen => "StringGen(IntGen(80,1000))" },                    string =>  { sqlType => 'VARCHAR(255)',       maxLen => 255,          avgLen => 100, dataGen => "StringGen(IntGen(10,250))" },
309                                    date =>        { sqlType => 'BIGINT',                         maxLen => 80,                   dataGen => "DateGen(-7, 7, IntGen(0,1400))" },                    text =>    { sqlType => 'TEXT',               maxLen => 1000000000,   avgLen => 500, dataGen => "StringGen(IntGen(80,1000))" },
310                                    float =>       { sqlType => 'DOUBLE PRECISION',       maxLen => 40,                   dataGen => "FloatGen(0.0, 100.0)" },                    date =>    { sqlType => 'BIGINT',             maxLen => 80,           avgLen =>   8, dataGen => "DateGen(-7, 7, IntGen(0,1400))" },
311                                    boolean => { sqlType => 'SMALLINT',                   maxLen => 1,                    dataGen => "IntGen(0, 1)" },                    float =>   { sqlType => 'DOUBLE PRECISION',   maxLen => 40,           avgLen =>   8, dataGen => "FloatGen(0.0, 100.0)" },
312                      boolean => { sqlType => 'SMALLINT',           maxLen => 1,            avgLen =>   2, dataGen => "IntGen(0, 1)" },
313                               'key-string' =>                               'key-string' =>
314                                                           { sqlType => 'VARCHAR(40)',            maxLen => 40,                   dataGen => "StringGen(IntGen(10,40))" },                               { sqlType => 'VARCHAR(40)',        maxLen => 40,           avgLen =>  10, dataGen => "StringGen(IntGen(10,40))" },
315                                   'name-string' =>                                   'name-string' =>
316                                                           { sqlType => 'VARCHAR(80)',            maxLen => 80,                   dataGen => "StringGen(IntGen(10,80))" },                               { sqlType => 'VARCHAR(80)',        maxLen => 80,           avgLen =>  40, dataGen => "StringGen(IntGen(10,80))" },
317                                   'medium-string' =>                                   'medium-string' =>
318                                                           { sqlType => 'VARCHAR(160)',           maxLen => 160,                  dataGen => "StringGen(IntGen(10,160))" },                               { sqlType => 'VARCHAR(160)',       maxLen => 160,          avgLen =>  40, dataGen => "StringGen(IntGen(10,160))" },
319                                  );                                  );
320    
321  # Table translating arities into natural language.  # Table translating arities into natural language.
# Line 145  Line 371 
371    
372  =head3 ShowMetaData  =head3 ShowMetaData
373    
374  C<< $database->ShowMetaData($fileName); >>  C<< $erdb->ShowMetaData($fileName); >>
375    
376  This method outputs a description of the database. This description can be used to help users create  This method outputs a description of the database. This description can be used to help users create
377  the data to be loaded into the relations.  the data to be loaded into the relations.
# Line 300  Line 526 
526    
527  =head3 DumpMetaData  =head3 DumpMetaData
528    
529  C<< $database->DumpMetaData(); >>  C<< $erdb->DumpMetaData(); >>
530    
531  Return a dump of the metadata structure.  Return a dump of the metadata structure.
532    
# Line 315  Line 541 
541    
542  =head3 CreateTables  =head3 CreateTables
543    
544  C<< $datanase->CreateTables(); >>  C<< $erdb->CreateTables(); >>
545    
546  This method creates the tables for the database from the metadata structure loaded by the  This method creates the tables for the database from the metadata structure loaded by the
547  constructor. It is expected this function will only be used on rare occasions, when the  constructor. It is expected this function will only be used on rare occasions, when the
# Line 353  Line 579 
579    
580  =head3 CreateTable  =head3 CreateTable
581    
582  C<< $database->CreateTable($tableName, $indexFlag); >>  C<< $erdb->CreateTable($tableName, $indexFlag, $estimatedRows); >>
583    
584  Create the table for a relation and optionally create its indexes.  Create the table for a relation and optionally create its indexes.
585    
# Line 363  Line 589 
589    
590  Name of the relation (which will also be the table name).  Name of the relation (which will also be the table name).
591    
592  =item $indexFlag  =item indexFlag
593    
594  TRUE if the indexes for the relation should be created, else FALSE. If FALSE,  TRUE if the indexes for the relation should be created, else FALSE. If FALSE,
595  L</CreateIndexes> must be called later to bring the indexes into existence.  L</CreateIndexes> must be called later to bring the indexes into existence.
596    
597    =item estimatedRows (optional)
598    
599    If specified, the estimated maximum number of rows for the relation. This
600    information allows the creation of tables using storage engines that are
601    faster but require size estimates, such as MyISAM.
602    
603  =back  =back
604    
605  =cut  =cut
606    
607  sub CreateTable {  sub CreateTable {
608          # Get the parameters.          # Get the parameters.
609          my ($self, $relationName, $indexFlag) = @_;      my ($self, $relationName, $indexFlag, $estimatedRows) = @_;
610          # Get the database handle.          # Get the database handle.
611          my $dbh = $self->{_dbh};          my $dbh = $self->{_dbh};
612          # Get the relation data and determine whether or not the relation is primary.          # Get the relation data and determine whether or not the relation is primary.
# Line 398  Line 630 
630          # Insure the table is not already there.          # Insure the table is not already there.
631          $dbh->drop_table(tbl => $relationName);          $dbh->drop_table(tbl => $relationName);
632          Trace("Table $relationName dropped.") if T(2);          Trace("Table $relationName dropped.") if T(2);
633        # If there are estimated rows, create an estimate so we can take advantage of
634        # faster DB technologies.
635        my $estimation = undef;
636        if ($estimatedRows) {
637            $estimation = [$self->EstimateRowSize($relationName), $estimatedRows];
638        }
639          # Create the table.          # Create the table.
640          Trace("Creating table $relationName: $fieldThing") if T(2);          Trace("Creating table $relationName: $fieldThing") if T(2);
641          $dbh->create_table(tbl => $relationName, flds => $fieldThing);      $dbh->create_table(tbl => $relationName, flds => $fieldThing, estimates => $estimation);
642          Trace("Relation $relationName created in database.") if T(2);          Trace("Relation $relationName created in database.") if T(2);
643          # If we want to build the indexes, we do it here.          # If we want to build the indexes, we do it here.
644          if ($indexFlag) {          if ($indexFlag) {
# Line 410  Line 648 
648    
649  =head3 CreateIndex  =head3 CreateIndex
650    
651  C<< $database->CreateIndex($relationName); >>  C<< $erdb->CreateIndex($relationName); >>
652    
653  Create the indexes for a relation. If a table is being loaded from a large source file (as  Create the indexes for a relation. If a table is being loaded from a large source file (as
654  is the case in L</LoadTable>), it is best to create the indexes after the load. If that is  is the case in L</LoadTable>), it is sometimes best to create the indexes after the load.
655  the case, then L</CreateTable> should be called with the index flag set to FALSE, and this  If that is the case, then L</CreateTable> should be called with the index flag set to
656  method used after the load to create the indexes for the table.  FALSE, and this method used after the load to create the indexes for the table.
657    
658  =cut  =cut
659    
# Line 443  Line 681 
681    
682  =head3 LoadTables  =head3 LoadTables
683    
684  C<< my $stats = $database->LoadTables($directoryName, $rebuild); >>  C<< my $stats = $erdb->LoadTables($directoryName, $rebuild); >>
685    
686  This method will load the database tables from a directory. The tables must already have been created  This method will load the database tables from a directory. The tables must already have been created
687  in the database. (This can be done by calling L</CreateTables>.) The caller passes in a directory name;  in the database. (This can be done by calling L</CreateTables>.) The caller passes in a directory name;
# Line 513  Line 751 
751    
752  =head3 GetTableNames  =head3 GetTableNames
753    
754  C<< my @names = $database->GetTableNames; >>  C<< my @names = $erdb->GetTableNames; >>
755    
756  Return a list of the relations required to implement this database.  Return a list of the relations required to implement this database.
757    
# Line 530  Line 768 
768    
769  =head3 GetEntityTypes  =head3 GetEntityTypes
770    
771  C<< my @names = $database->GetEntityTypes; >>  C<< my @names = $erdb->GetEntityTypes; >>
772    
773  Return a list of the entity type names.  Return a list of the entity type names.
774    
# Line 545  Line 783 
783          return sort keys %{$entityList};          return sort keys %{$entityList};
784  }  }
785    
786    =head3 IsEntity
787    
788    C<< my $flag = $erdb->IsEntity($entityName); >>
789    
790    Return TRUE if the parameter is an entity name, else FALSE.
791    
792    =over 4
793    
794    =item entityName
795    
796    Object name to be tested.
797    
798    =item RETURN
799    
800    Returns TRUE if the specified string is an entity name, else FALSE.
801    
802    =back
803    
804    =cut
805    
806    sub IsEntity {
807        # Get the parameters.
808        my ($self, $entityName) = @_;
809        # Test to see if it's an entity.
810        return exists $self->{_metaData}->{Entities}->{$entityName};
811    }
812    
813  =head3 Get  =head3 Get
814    
815  C<< my $query = $database->Get(\@objectNames, $filterClause, $param1, $param2, ..., $paramN); >>  C<< my $query = $erdb->Get(\@objectNames, $filterClause, $param1, $param2, ..., $paramN); >>
816    
817  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.
818  The filter is a standard WHERE/ORDER BY clause with question marks as parameter markers and each  The filter is a standard WHERE/ORDER BY clause with question marks as parameter markers and each
# Line 555  Line 820 
820  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
821  $genus.  $genus.
822    
823  C<< $query = $sprout->Get(['Genome'], "Genome(genus) = ?", $genus); >>  C<< $query = $erdb->Get(['Genome'], "Genome(genus) = ?", $genus); >>
824    
825  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
826  parameter representing the parameter value. It would also be possible to code  parameter representing the parameter value. It would also be possible to code
827    
828  C<< $query = $sprout->Get(['Genome'], "Genome(genus) = \'$genus\'"); >>  C<< $query = $erdb->Get(['Genome'], "Genome(genus) = \'$genus\'"); >>
829    
830  however, this version of the call would generate a syntax error if there were any quote  however, this version of the call would generate a syntax error if there were any quote
831  characters inside the variable C<$genus>.  characters inside the variable C<$genus>.
# Line 572  Line 837 
837  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
838  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,
839    
840  C<< $query = $sprout->Get(['Genome', 'ComesFrom', 'Source'], "Genome(genus) = ?", $genus); >>  C<< $query = $erdb->Get(['Genome', 'ComesFrom', 'Source'], "Genome(genus) = ?", $genus); >>
841    
842  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
843  join path between the entities and relationships. The algorithm used is very simplistic.  join path between the entities and relationships. The algorithm used is very simplistic.
# Line 735  Line 1000 
1000                          $command .= " ORDER BY $orderClause";                          $command .= " ORDER BY $orderClause";
1001                  }                  }
1002          }          }
1003          Trace("SQL query: $command") if T(2);      Trace("SQL query: $command") if T(3);
1004          Trace("PARMS: '" . (join "', '", @params) . "'") if (T(3) && (@params > 0));      Trace("PARMS: '" . (join "', '", @params) . "'") if (T(4) && (@params > 0));
1005          my $sth = $dbh->prepare_command($command);          my $sth = $dbh->prepare_command($command);
1006          # Execute it with the parameters bound in.          # Execute it with the parameters bound in.
1007          $sth->execute(@params) || Confess("SELECT error" . $sth->errstr());          $sth->execute(@params) || Confess("SELECT error" . $sth->errstr());
# Line 747  Line 1012 
1012    
1013  =head3 GetList  =head3 GetList
1014    
1015  C<< my @dbObjects = $database->GetList(\@objectNames, $filterClause, $param1, $param2, ..., $paramN); >>  C<< my @dbObjects = $erdb->GetList(\@objectNames, $filterClause, $param1, $param2, ..., $paramN); >>
1016    
1017  Return a list of object descriptors for the specified objects as determined by the  Return a list of object descriptors for the specified objects as determined by the
1018  specified filter clause.  specified filter clause.
1019    
1020  This method is essentially the same as L</Get> except it returns a list of objects rather  This method is essentially the same as L</Get> except it returns a list of objects rather
1021  that a query object that can be used to get the results one record at a time.  than a query object that can be used to get the results one record at a time.
   
 =over 4  
1022    
1023  =over 4  =over 4
1024    
# Line 812  Line 1075 
1075    
1076  =head3 ComputeObjectSentence  =head3 ComputeObjectSentence
1077    
1078  C<< my $sentence = $database->ComputeObjectSentence($objectName); >>  C<< my $sentence = $erdb->ComputeObjectSentence($objectName); >>
1079    
1080  Check an object name, and if it is a relationship convert it to a relationship sentence.  Check an object name, and if it is a relationship convert it to a relationship sentence.
1081    
# Line 847  Line 1110 
1110    
1111  =head3 DumpRelations  =head3 DumpRelations
1112    
1113  C<< $database->DumpRelations($outputDirectory); >>  C<< $erdb->DumpRelations($outputDirectory); >>
1114    
1115  Write the contents of all the relations to tab-delimited files in the specified directory.  Write the contents of all the relations to tab-delimited files in the specified directory.
1116  Each file will have the same name as the relation dumped, with an extension of DTX.  Each file will have the same name as the relation dumped, with an extension of DTX.
# Line 889  Line 1152 
1152    
1153  =head3 InsertObject  =head3 InsertObject
1154    
1155  C<< my $ok = $database->InsertObject($objectType, \%fieldHash); >>  C<< my $ok = $erdb->InsertObject($objectType, \%fieldHash); >>
1156    
1157  Insert an object into the database. The object is defined by a type name and then a hash  Insert an object into the database. The object is defined by a type name and then a hash
1158  of field names to values. Field values in the primary relation are represented by scalars.  of field names to values. Field values in the primary relation are represented by scalars.
# Line 898  Line 1161 
1161  example, the following line inserts an inactive PEG feature named C<fig|188.1.peg.1> with aliases  example, the following line inserts an inactive PEG feature named C<fig|188.1.peg.1> with aliases
1162  C<ZP_00210270.1> and C<gi|46206278>.  C<ZP_00210270.1> and C<gi|46206278>.
1163    
1164  C<< $database->InsertObject('Feature', { id => 'fig|188.1.peg.1', active => 0, feature-type => 'peg', alias => ['ZP_00210270.1', 'gi|46206278']}); >>  C<< $erdb->InsertObject('Feature', { id => 'fig|188.1.peg.1', active => 0, feature-type => 'peg', alias => ['ZP_00210270.1', 'gi|46206278']}); >>
1165    
1166  The next statement inserts a C<HasProperty> relationship between feature C<fig|158879.1.peg.1> and  The next statement inserts a C<HasProperty> relationship between feature C<fig|158879.1.peg.1> and
1167  property C<4> with an evidence URL of C<http://seedu.uchicago.edu/query.cgi?article_id=142>.  property C<4> with an evidence URL of C<http://seedu.uchicago.edu/query.cgi?article_id=142>.
1168    
1169  C<< $database->InsertObject('HasProperty', { 'from-link' => 'fig|158879.1.peg.1', 'to-link' => 4, evidence = 'http://seedu.uchicago.edu/query.cgi?article_id=142'}); >>  C<< $erdb->InsertObject('HasProperty', { 'from-link' => 'fig|158879.1.peg.1', 'to-link' => 4, evidence = 'http://seedu.uchicago.edu/query.cgi?article_id=142'}); >>
1170    
1171  =over 4  =over 4
1172    
# Line 1028  Line 1291 
1291    
1292  =head3 LoadTable  =head3 LoadTable
1293    
1294  C<< my %results = $database->LoadTable($fileName, $relationName, $truncateFlag); >>  C<< my %results = $erdb->LoadTable($fileName, $relationName, $truncateFlag); >>
1295    
1296  Load data from a tab-delimited file into a specified table, optionally re-creating the table first.  Load data from a tab-delimited file into a specified table, optionally re-creating the table
1297    first.
1298    
1299  =over 4  =over 4
1300    
# Line 1048  Line 1312 
1312    
1313  =item RETURN  =item RETURN
1314    
1315  Returns a statistical object containing the number of records read and a list of the error messages.  Returns a statistical object containing the number of records read and a list of
1316    the error messages.
1317    
1318  =back  =back
1319    
# Line 1059  Line 1324 
1324          # Create the statistical return object.          # Create the statistical return object.
1325          my $retVal = _GetLoadStats();          my $retVal = _GetLoadStats();
1326          # Trace the fact of the load.          # Trace the fact of the load.
1327          Trace("Loading table $relationName from $fileName") if T(1);      Trace("Loading table $relationName from $fileName") if T(2);
1328          # Get the database handle.          # Get the database handle.
1329          my $dbh = $self->{_dbh};          my $dbh = $self->{_dbh};
1330          # Get the relation data.          # Get the relation data.
1331          my $relation = $self->_FindRelation($relationName);          my $relation = $self->_FindRelation($relationName);
1332          # Check the truncation flag.          # Check the truncation flag.
1333          if ($truncateFlag) {          if ($truncateFlag) {
1334                  Trace("Creating table $relationName") if T(1);          Trace("Creating table $relationName") if T(2);
1335            # Compute the row count estimate. We take the size of the load file,
1336            # divide it by the estimated row size, and then multiply by 1.5 to
1337            # leave extra room. We postulate a minimum row count of 1000 to
1338            # prevent problems with incoming empty load files.
1339            my $rowSize = $self->EstimateRowSize($relationName);
1340            my $fileSize = -s $fileName;
1341            my $estimate = FIG::max($fileSize * 1.5 / $rowSize, 1000);
1342                  # Re-create the table without its index.                  # Re-create the table without its index.
1343                  $self->CreateTable($relationName, 0);          $self->CreateTable($relationName, 0, $estimate);
1344            # If this is a pre-index DBMS, create the index here.
1345            if ($dbh->{_preIndex}) {
1346                eval {
1347                    $self->CreateIndex($relationName);
1348                };
1349                if ($@) {
1350                    $retVal->AddMessage($@);
1351                }
1352            }
1353          }          }
         # 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;  
         # Record the number of expected fields.  
         my $expectedFields = $fieldCount + ($primary ? 1 : 0);  
         # 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;  
         # Loop through the file.  
         while (<TABLEIN>) {  
                 # Chop off the new-line character.  
                 my $record = $_;  
                 chomp $record;  
         # Only proceed if the record is non-blank.  
         if ($record) {  
             # Escape all the backslashes found in the line.  
             $record =~ s/\\/\\\\/g;  
             # Eliminate any trailing tabs.  
             chop $record while substr($record, -1) eq "\t";  
             # 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) {  
                 $record .= "\t0";  
             }  
             # Write the record.  
             print TABLEOUT "$record\n";  
             # Count the record read.  
             my $count = $retVal->Add('records');  
             my $len = length $record;  
             Trace("Record $count written with $len characters.") if T(4);  
         }  
         }  
         # Close the files.  
         close TABLEIN;  
         close TABLEOUT;  
     Trace("Temporary file $tempName created.") if T(4);  
1354      # Load the table.      # Load the table.
1355          my $rv;          my $rv;
1356          eval {          eval {
1357                  $rv = $dbh->load_table(file => $tempName, tbl => $relationName);          $rv = $dbh->load_table(file => $fileName, tbl => $relationName);
1358          };          };
1359          if (!defined $rv) {          if (!defined $rv) {
1360          $retVal->AddMessage($@) if ($@);          $retVal->AddMessage($@) if ($@);
1361          $retVal->AddMessage("Table load failed for $relationName using $tempName.");          $retVal->AddMessage("Table load failed for $relationName using $fileName.");
1362                  Trace("Table load failed for $relationName.") if T(1);                  Trace("Table load failed for $relationName.") if T(1);
1363          } else {          } else {
1364                  # Here we successfully loaded the table. Trace the number of records loaded.                  # Here we successfully loaded the table. Trace the number of records loaded.
1365                  Trace("$retVal->{records} records read for $relationName.") if T(1);          Trace("$retVal->{records} records read for $relationName.") if T(2);
1366                  # If we're rebuilding, we need to create the table indexes.                  # If we're rebuilding, we need to create the table indexes.
1367                  if ($truncateFlag) {          if ($truncateFlag && ! $dbh->{_preIndex}) {
1368                          eval {                          eval {
1369                                  $self->CreateIndex($relationName);                                  $self->CreateIndex($relationName);
1370                          };                          };
# Line 1136  Line 1375 
1375          }          }
1376          # Commit the database changes.          # Commit the database changes.
1377          $dbh->commit_tran;          $dbh->commit_tran;
1378          # Delete the temporary file.      # Analyze the table to improve performance.
1379          unlink $tempName;      $dbh->vacuum_it($relationName);
1380          # Return the statistics.          # Return the statistics.
1381          return $retVal;          return $retVal;
1382  }  }
1383    
1384  =head3 GenerateEntity  =head3 GenerateEntity
1385    
1386  C<< my $fieldHash = $database->GenerateEntity($id, $type, \%values); >>  C<< my $fieldHash = $erdb->GenerateEntity($id, $type, \%values); >>
1387    
1388  Generate the data for a new entity instance. This method creates a field hash suitable for  Generate the data for a new entity instance. This method creates a field hash suitable for
1389  passing as a parameter to L</InsertObject>. The ID is specified by the callr, but the rest  passing as a parameter to L</InsertObject>. The ID is specified by the callr, but the rest
# Line 1202  Line 1441 
1441    
1442  =head3 GetEntity  =head3 GetEntity
1443    
1444  C<< my $entityObject = $sprout->GetEntity($entityType, $ID); >>  C<< my $entityObject = $erdb->GetEntity($entityType, $ID); >>
1445    
1446  Return an object describing the entity instance with a specified ID.  Return an object describing the entity instance with a specified ID.
1447    
# Line 1238  Line 1477 
1477    
1478  =head3 GetEntityValues  =head3 GetEntityValues
1479    
1480  C<< my @values = GetEntityValues($entityType, $ID, \@fields); >>  C<< my @values = $erdb->GetEntityValues($entityType, $ID, \@fields); >>
1481    
1482  Return a list of values from a specified entity instance.  Return a list of values from a specified entity instance.
1483    
# Line 1279  Line 1518 
1518          return @retVal;          return @retVal;
1519  }  }
1520    
1521    =head3 GetAll
1522    
1523    C<< my @list = $erdb->GetAll(\@objectNames, $filterClause, \@parameters, \@fields, $count); >>
1524    
1525    Return a list of values taken from the objects returned by a query. The first three
1526    parameters correspond to the parameters of the L</Get> method. The final parameter is
1527    a list of the fields desired from each record found by the query. The field name
1528    syntax is the standard syntax used for fields in the B<ERDB> system--
1529    B<I<objectName>(I<fieldName>)>-- where I<objectName> is the name of the relevant entity
1530    or relationship and I<fieldName> is the name of the field.
1531    
1532    The list returned will be a list of lists. Each element of the list will contain
1533    the values returned for the fields specified in the fourth parameter. If one of the
1534    fields specified returns multiple values, they are flattened in with the rest. For
1535    example, the following call will return a list of the features in a particular
1536    spreadsheet cell, and each feature will be represented by a list containing the
1537    feature ID followed by all of its aliases.
1538    
1539    C<< $query = $erdb->Get(['ContainsFeature', 'Feature'], "ContainsFeature(from-link) = ?", [$ssCellID], ['Feature(id)', 'Feature(alias)']); >>
1540    
1541    =over 4
1542    
1543    =item objectNames
1544    
1545    List containing the names of the entity and relationship objects to be retrieved.
1546    
1547    =item filterClause
1548    
1549    WHERE/ORDER BY clause (without the WHERE) to be used to filter and sort the query. The WHERE clause can
1550    be parameterized with parameter markers (C<?>). Each field used must be specified in the standard form
1551    B<I<objectName>(I<fieldName>)>. Any parameters specified in the filter clause should be added to the
1552    parameter list as additional parameters. The fields in a filter clause can come from primary
1553    entity relations, relationship relations, or secondary entity relations; however, all of the
1554    entities and relationships involved must be included in the list of object names.
1555    
1556    =item parameterList
1557    
1558    List of the parameters to be substituted in for the parameters marks in the filter clause.
1559    
1560    =item fields
1561    
1562    List of the fields to be returned in each element of the list returned.
1563    
1564    =item count
1565    
1566    Maximum number of records to return. If omitted or 0, all available records will be returned.
1567    
1568    =item RETURN
1569    
1570    Returns a list of list references. Each element of the return list contains the values for the
1571    fields specified in the B<fields> parameter.
1572    
1573    =back
1574    
1575    =cut
1576    #: Return Type @@;
1577    sub GetAll {
1578        # Get the parameters.
1579        my ($self, $objectNames, $filterClause, $parameterList, $fields, $count) = @_;
1580        # Translate the parameters from a list reference to a list. If the parameter
1581        # list is a scalar we convert it into a singleton list.
1582        my @parmList = ();
1583        if (ref $parameterList eq "ARRAY") {
1584            @parmList = @{$parameterList};
1585        } else {
1586            push @parmList, $parameterList;
1587        }
1588        # Create the query.
1589        my $query = $self->Get($objectNames, $filterClause, @parmList);
1590        # Set up a counter of the number of records read.
1591        my $fetched = 0;
1592        # Insure the counter has a value.
1593        if (!defined $count) {
1594            $count = 0;
1595        }
1596        # Loop through the records returned, extracting the fields. Note that if the
1597        # counter is non-zero, we stop when the number of records read hits the count.
1598        my @retVal = ();
1599        while (($count == 0 || $fetched < $count) && (my $row = $query->Fetch())) {
1600            my @rowData = $row->Values($fields);
1601            push @retVal, \@rowData;
1602            $fetched++;
1603        }
1604        # Return the resulting list.
1605        return @retVal;
1606    }
1607    
1608    =head3 EstimateRowSize
1609    
1610    C<< my $rowSize = $erdb->EstimateRowSize($relName); >>
1611    
1612    Estimate the row size of the specified relation. The estimated row size is computed by adding
1613    up the average length for each data type.
1614    
1615    =over 4
1616    
1617    =item relName
1618    
1619    Name of the relation whose estimated row size is desired.
1620    
1621    =item RETURN
1622    
1623    Returns an estimate of the row size for the specified relation.
1624    
1625    =back
1626    
1627    =cut
1628    #: Return Type $;
1629    sub EstimateRowSize {
1630        # Get the parameters.
1631        my ($self, $relName) = @_;
1632        # Declare the return variable.
1633        my $retVal = 0;
1634        # Find the relation descriptor.
1635        my $relation = $self->_FindRelation($relName);
1636        # Get the list of fields.
1637        for my $fieldData (@{$relation->{Fields}}) {
1638            # Get the field type and add its length.
1639            my $fieldLen = $TypeTable{$fieldData->{type}}->{avgLen};
1640            $retVal += $fieldLen;
1641        }
1642        # Return the result.
1643        return $retVal;
1644    }
1645    
1646  =head2 Internal Utility Methods  =head2 Internal Utility Methods
1647    
1648  =head3 GetLoadStats  =head3 GetLoadStats
# Line 1654  Line 2018 
2018  sub _LoadMetaData {  sub _LoadMetaData {
2019          # Get the parameters.          # Get the parameters.
2020          my ($filename) = @_;          my ($filename) = @_;
2021        Trace("Reading Sprout DBD from $filename.") if T(2);
2022          # Slurp the XML file into a variable. Extensive use of options is used to insure we          # Slurp the XML file into a variable. Extensive use of options is used to insure we
2023          # get the exact structure we want.          # get the exact structure we want.
2024          my $metadata = XML::Simple::XMLin($filename,          my $metadata = XML::Simple::XMLin($filename,
# Line 1681  Line 2046 
2046          for my $entityName (keys %{$entityList}) {          for my $entityName (keys %{$entityList}) {
2047                  my $entityStructure = $entityList->{$entityName};                  my $entityStructure = $entityList->{$entityName};
2048                  #                  #
2049                  # The first step is to run creating all the entity's default values. For C<Field> elements,          # The first step is to create all the entity's default values. For C<Field> elements,
2050                  # the relation name must be added where it is not specified. For relationships,                  # the relation name must be added where it is not specified. For relationships,
2051                  # the B<from-link> and B<to-link> fields must be inserted, and for entities an B<id>                  # the B<from-link> and B<to-link> fields must be inserted, and for entities an B<id>
2052                  # field must be added to each relation. Finally, each field will have a C<PrettySort> attribute                  # field must be added to each relation. Finally, each field will have a C<PrettySort> attribute
# Line 1860  Line 2225 
2225                  my @fromList = ();                  my @fromList = ();
2226                  my @toList = ();                  my @toList = ();
2227                  my @bothList = ();                  my @bothList = ();
2228                  Trace("Join table build for $entityName.") if T(3);          Trace("Join table build for $entityName.") if T(4);
2229                  for my $relationshipName (keys %{$relationshipList}) {                  for my $relationshipName (keys %{$relationshipList}) {
2230                          my $relationship = $relationshipList->{$relationshipName};                          my $relationship = $relationshipList->{$relationshipName};
2231                          # 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.
2232                          my $fromEntity = $relationship->{from};                          my $fromEntity = $relationship->{from};
2233                          my $toEntity = $relationship->{to};                          my $toEntity = $relationship->{to};
2234                          Trace("Join check for relationship $relationshipName from $fromEntity to $toEntity.") if T(3);              Trace("Join check for relationship $relationshipName from $fromEntity to $toEntity.") if T(4);
2235                          if ($fromEntity eq $entityName) {                          if ($fromEntity eq $entityName) {
2236                                  if ($toEntity eq $entityName) {                                  if ($toEntity eq $entityName) {
2237                                          # Here the relationship is recursive.                                          # Here the relationship is recursive.
2238                                          push @bothList, $relationshipName;                                          push @bothList, $relationshipName;
2239                                          Trace("Relationship $relationshipName put in both-list.") if T(3);                      Trace("Relationship $relationshipName put in both-list.") if T(4);
2240                                  } else {                                  } else {
2241                                          # Here the relationship comes from the entity.                                          # Here the relationship comes from the entity.
2242                                          push @fromList, $relationshipName;                                          push @fromList, $relationshipName;
2243                                          Trace("Relationship $relationshipName put in from-list.") if T(3);                      Trace("Relationship $relationshipName put in from-list.") if T(4);
2244                                  }                                  }
2245                          } elsif ($toEntity eq $entityName) {                          } elsif ($toEntity eq $entityName) {
2246                                  # Here the relationship goes to the entity.                                  # Here the relationship goes to the entity.
2247                                  push @toList, $relationshipName;                                  push @toList, $relationshipName;
2248                                  Trace("Relationship $relationshipName put in to-list.") if T(3);                  Trace("Relationship $relationshipName put in to-list.") if T(4);
2249                          }                          }
2250                  }                  }
2251                  # Create the nonrecursive joins. Note that we build two hashes for running                  # Create the nonrecursive joins. Note that we build two hashes for running
# Line 1926  Line 2291 
2291                                  # relationship can only be ambiguous with another recursive relationship,                                  # relationship can only be ambiguous with another recursive relationship,
2292                                  # and the incoming relationship from the outer loop is never recursive.                                  # and the incoming relationship from the outer loop is never recursive.
2293                                  for my $otherName (@bothList) {                                  for my $otherName (@bothList) {
2294                                          Trace("Setting up relationship joins to recursive relationship $otherName with $relationshipName.") if T(3);                      Trace("Setting up relationship joins to recursive relationship $otherName with $relationshipName.") if T(4);
2295                                          # Join from the left.                                          # Join from the left.
2296                                          $joinTable{"$relationshipName/$otherName"} =                                          $joinTable{"$relationshipName/$otherName"} =
2297                                                  "$linkField = $otherName.from_link";                                                  "$linkField = $otherName.from_link";
# Line 1941  Line 2306 
2306                  # 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
2307                  # possible to get the same effect using multiple queries.                  # possible to get the same effect using multiple queries.
2308                  for my $relationshipName (@bothList) {                  for my $relationshipName (@bothList) {
2309                          Trace("Setting up entity joins to recursive relationship $relationshipName with $entityName.") if T(3);              Trace("Setting up entity joins to recursive relationship $relationshipName with $entityName.") if T(4);
2310                          # Join to the entity from each direction.                          # Join to the entity from each direction.
2311                          $joinTable{"$entityName/$relationshipName"} =                          $joinTable{"$entityName/$relationshipName"} =
2312                                  "$entityName.id = $relationshipName.from_link";                                  "$entityName.id = $relationshipName.from_link";
# Line 1992  Line 2357 
2357          # index descriptor does not exist, it will be created automatically so we can add          # index descriptor does not exist, it will be created automatically so we can add
2358          # the field to it.          # the field to it.
2359          unshift @{$newIndex->{IndexFields}}, $firstField;          unshift @{$newIndex->{IndexFields}}, $firstField;
2360        # If this is a one-to-many relationship, the "To" index is unique.
2361        if ($relationshipStructure->{arity} eq "1M" && $indexKey eq "To") {
2362            $newIndex->{Unique} = 'true';
2363        }
2364          # Add the index to the relation.          # Add the index to the relation.
2365          _AddIndex("idx$relationshipName$indexKey", $relationStructure, $newIndex);          _AddIndex("idx$relationshipName$indexKey", $relationStructure, $newIndex);
2366  }  }
# Line 2083  Line 2452 
2452                  # Here we have a field list. Loop through its fields.                  # Here we have a field list. Loop through its fields.
2453                  my $fieldStructures = $structure->{Fields};                  my $fieldStructures = $structure->{Fields};
2454                  for my $fieldName (keys %{$fieldStructures}) {                  for my $fieldName (keys %{$fieldStructures}) {
2455                Trace("Processing field $fieldName of $defaultRelationName.") if T(4);
2456                          my $fieldData = $fieldStructures->{$fieldName};                          my $fieldData = $fieldStructures->{$fieldName};
2457                          # Get the field type.                          # Get the field type.
2458                          my $type = $fieldData->{type};                          my $type = $fieldData->{type};

Legend:
Removed from v.1.6  
changed lines
  Added in v.1.20

MCS Webmaster
ViewVC Help
Powered by ViewVC 1.0.3