OME::DBObject - OME's Object/Relational mapping class
The DBObject class provides an object/relationship mapping layer. It provides object-oriented access into a set of tables in a database. It is used by declaring DBObject subclasses to represent logically similar objects. Usually this corresponds to the notion of rows within a database table, but it is possible for a single DBObject subclass to span multiple tables. (In this case, each table must contain a primary key column, which is used to link rows in the separate tables.)
For more information on the use of the DBObject class, please see the (as-yet-unwritten) OME Database Introduction.
The DBObject class provides an object cache, whose main purpose is to prevent a single row in the database from being represented by more than one DBObject instance in memory at a single time. The following methods are available for controlling this cache.
$class->useSeparateCache();
Forces instances of $class to be stored in a separate cache. This is not particularly useful without also using the clearCache method. Calling this method directly on OME::DBObject is also not useful, since it just redeclares the global object cache. This is a irreversible operation over the lifetime of one Perl instance.
NOTE: Calling this method (either on OME::DBObject or a subclass) after objects have already been stored in the appropriate cache will cause that cache to be cleared. Calling this method on a subclass for the first time, after objects of that class have already been loaded from the database, will not cause those objects to be removed from the global cache. It will, however, orphan them, as the caching mechanism will no longer look in the global cache for this class.
$class->clearCache();
Causes the cache for $class to be emptied. The objects will be eligible for garbage collection, assuming there are no other references to them in other code. Subsequent objects of this class will be reloaded from the databases. If this method is called on OME::DBObject, or on a subclass which has not had useSeparateClass called on it, then the global cache will be emptied.
$class->clearAllCaches();
Causes all DBObject caches for to be emptied, including classes that are using separare caches.
Each DBObject subclass should use the methods in this section to declare its logical columns and the database tables that contain them.
package MyNewClass;
use base qw(OME::DBObject);
__PACKAGE__->newClass();
This should be the first method called for any new DBObject subclass. It ensures that the variables used to maintain the class's metadata are initialized to the appropriate values.
__PACKAGE__->setDefaultTable($table_name);
Sets the default table for this DBObject subclass. After this method has been called, later method calls which refer to database locations can use the "COLUMN" form rather than the "TABLE.COLUMN" form; the default table will be implied. Note that this implication takes place when the database locations are defined, not when they're used, so it is safe to use this method more than once.
__PACKAGE__->setSequence($sequence_name);
Subclasses which have primary keys should call this method to specify which database sequence is used to provide values for the primary key. New values are obtained by passing the $sequence_name parameter into the OME::Database::Delegate->getNextSequenceValue method.
__PACKAGE__->addPrimaryKey($location);
Declares a primary key column for this subclass. Subclasses which span multiple database tables should define a primary key column for each one.
The $location parameter should be in one of two forms: "TABLE.COLUMN" or "COLUMN". The second form can only be used if the setDefaultTable method has been called previously.
__PACKAGE__->addDeleteKey($location);
Declares a delete key column for this subclass. A delete key is basically a big huge hack, which allows DBObject classes which don't have primary keys to be deleted. Currently the only cases of this are the many-to-many mapping classes between projects, datasets, and images. They should be used with extreme prejudice, and will hopefully be replaced with something more robust in the near future.
The $location parameter should be in one of two forms: "TABLE.COLUMN" or "COLUMN". The second form can only be used if the setDefaultTable method has been called previously.
__PACKAGE__->addColumn($aliases,$location);
__PACKAGE__->addColumn($aliases,$location,$fkey);
__PACKAGE__->addColumn($aliases,$location,\%sql_options);
__PACKAGE__->addColumn($aliases,$location,$fkey,\%sql_options);
Adds a new logical data column to this subclass. It also creates new accessor/mutator methods for this subclass for reading and writing values for this column. These new accessors are instance methods which accept a single optional parameter. If called with a parameter, that instance's value for this column is set to the specified value. If called without a parameter, that instance's value is returned.
The $aliases parameter specifies the name(s) that this column to be referred to as by other code. This includes the keys to data hashes (for new* and find* methods) and the names of the accessors which are created. The parameter can either be a single alias (as a scalar), or an array reference of aliases. These aliases should be valid Perl method identifiers. Most often, at least one alias will be the same as the name of the underlying database column.
The $location parameter specifies where in the database this column is stored. It should be in one of two forms: "TABLE.COLUMN" or "COLUMN". The second form can only be used if the setDefaultTable method has been called previously.
If specified, the $fkey parameter (which must be a scalar) should specify the name of another DBObject subclass (the "foreign-key class"). This declares that this data column is a foreign-key column, and implies that it will contain an integer value. This integer value is interpreted as the primary key ID of an instance of the specified foreign-key class. The accessors which are created will automatically load in the appropriate foreign-key instance from the database, and return it, instead of the stored integer. The mutator version of these methods will accept either an integer primary key ID, or an instance of the foreign-key class (and no other class).
The $fkey parameter can also be used to store attributes, if the $fkey
parameter is of the form "@SemanticTypeName". In this case, the
inflation-deflation described above will be performed by the factory's
loadAttribute method, rather than the loadObject method.
If specified, the \%sql_options parameter (which must be a hash reference) specifies options which are used by the database delegate to create this column in the database. It specifies, among other things, the underlying SQL data type of the column. Currently, the following keys are supported:
The SQL type of the data column. Any standard SQL type is valid. Non-standard, database-specific types should be avoided.
Provides a default value for this column. It can be any value which can be substituted into a '?' slot in a standard DBI statement. This corresponds to the DEFAULT clause of the CREATE TABLE statement.
Should be a Boolean value (0 or 1), defaults to 0. Specifies whether this column has a NOT NULL constraint.
Should be a Boolean value (0 or 1), defaults to 0. Specifies whether this column has a UNIQUE constraint.
Should be a table name. This adds a foreign-key referential integrity restriction to the underlying database table. Most database products enforce this restriction, and do not allow values that do not correspond to existing rows in the foreign-key table. Note that this SQL option and the DBObject-specific foreign-key behavior specified by the $fkey parameter are not related: Specifying one does not automatically specify the other, and the $fkey behavior does not require this SQL option in order to work.
Adds a CHECK constraint to the underlying column. This value for this key should be a standard SQL expression, which is substituted directly into the CREATE TABLE statement.
Different logical columns (each specified by a single call to addColumn) can live in the same database location (specified by the $location parameter). (This is useful in case you want to provide an accessor for retrieving a foreign-key ID in addition to one for retrieving a foreign-key object.) In this case, exactly one of the logical columns should provide the \%sql_options parameter. If none of them do, the database location will never be created, and the data base operations will most likely generate "column missing" errors. If more than one does, the database delegate will try to create a table with two identically-named columns, which is a database error.
my $column_def = $class->getColumnDef($alias);
Returns the definition of the logical column with the specified alias. This is mostly for internal purposes, and is used to generate the appropriate SQL statements for the various database operations. However, it can also be useful for reflection purposes. The result is an array reference of the following form:
[$table_name,$column_name,$fkey_class,$sql_options]
These correspond to the $location (split into table and column), $fkey, and \%sql_options parameters to the addColumn method. If the $fkey or \%sql_options parameters weren't specified, their entries in the arrayref will be undef. If the $location was specified in "COLUMN" format, the $table_name entry will be the default table at the time the addColumn method was called.
my @columns = $class->getColumns();
Returns a list of column aliases:
my @aliases = $class->getColumnAliases( $alias );
Given an alias to a column, will return all other aliases to that column.
my $column_type = $class->getColumnType($alias);
Returns the type of the logical column with the specified alias. If the column exists, the type will be one of four values -- "normal", "has-one", "has-many", or "many-to-many". If the column does not exist, the method returns undef.
Alternative calling convention for internal use: $class->getColumnType( $alias, 1 ); will return undef for aliases that are valid, but have not yet been created aliases for inferred relations are created lazily with the AUTOLOAD method If that doesn't make sense to you, then you probably don't need to use this flag.
my $column_sql_type = $class->getColumnSQLType($alias);
Returns the SQL type of the alias. If the column does not exist or is a reference, the method returns undef.
__PACKAGE__->hasMany($aliases,$fkey_class,$fkey_alias);
Defines a has-many relationship for this subclass. This is the inverse of the addColumn method with an $fkey parameter specified. This implies the the $fkey_class DBObject subclass has a logical column with an alias of $fkey_alias. It further implies that that logical column was declared to be a foreign key which points to this class.
This method will then create an accessor method which returns all of the instances of $fkey_class whose $fkey_alias column points to the given instance of this class. If the accessor is called in list context, an array of those objects is returned; if called in scalar context, an iterator is returned (see the OME::Factory class for a description of iterators). The $aliases parameter is specified just as in the addColumn method; it allows you to provide multiple names for the accessor which is created.
Note that hasMany columns cannot be present in data hashes, either for creation or searching.
__PACKAGE__->manyToMany($aliases,$map_class,$map_alias,$map_linker);
Defines a many-to-many relationship for this subclass. This is
basically the same thing as a has-many relationship (defined by the
hasMany method), with an additional linking table. The linking
table still must be defined as its own DBObject subclass, but once
that is done, this method can be used to provide accessors which
transparently use the mapping table.
For instance, projects and datasets have a many-to-many relationship,
involving the OME::Project, OME::Dataset, and
OME::Project::DatasetMap classes. In OME::Project,
__PACKAGE__->manyToMany('datasets',
'OME::Project::DatasetMap',
'project','dataset');
causes a datasets accessor to be created (first parameter), which
loads in all of the OME::Project::DatasetMap instances (second
parameter) pointing to the current project (via its project alias,
third parameter). Instead of returning those
OME::Project::DatasetMap instances, though, the dataset accessor
(fourth parameter) is called on each, and the results of those
accessor calls are returned.
As with all other list-retrieval accessors, if the many-to-many accessor is called in scalar context, an iterator is returned instead of a list. Also, the accessor will accept search parameters in factory syntax. i.e. $dataset->images( name => [ 'like', 'foo%' ], ... );
Note that manyToMany columns cannot be present in data hashes, either for creation or searching.
my $searchPath = $class->getManyToManyAliasSearchPath( $alias );
my $searchTypeFormalName = $class->getAccessorReferenceType( $alias )->getFormalName();
my @objects = $factory->findObjects( $searchTypeFormalName, $searchPath => $obj->id, [other search parameters] );
is equivalent to:
my @objects = $obj->alias( [other search parameters] )
This method returns a search path that can be used for factory searches. In the simple case, the same goal can be accomplished through simpler means. The benefit of this method is that is allows normalization of code and more complex queries.
__PACKAGE__->getReferences();
Returns a hash of references this packages contains. The keys to the hash are aliases used to access the references. The values are the packages referenced.
__PACKAGE__->getHasMany();
Returns a hash of has-many references this packages contains.
The hash follows the format { accessor => package_referenced }
my $hash = __PACKAGE__->getAllHasMany();
Returns a merged hash from __PACKAGE__->getHasMany() and
__PACKAGE__->getInferredHasMany()
The hash follows the format { accessor => package_referenced }
__PACKAGE__->getInferredHasMany();
Infers what has-many relationships exist from this package to STs. The accessors returned can be used. They will be created dynamically via the AUTOLOAD method when you attempt to use them. Returns a hash of has-many references this packages contains.
The hash follows the format { accessor => package_referenced }
This method will return blank lists for all package defined DBObjects other than OME::Feature, OME::Image, OME::Dataset, and OME::ModuleExecution. Those are special cases because references from STs to those DBObjects are hardcoded in OME::SemanticType.
DO NOT call this method before finishing class definitions. If you do, all possible has-many relationships will be marked as inferred, which will result in unexpected behavior if relationships are later explicitly defined.
ST's have their own implementation of this defined in OME::SemanticType::Superclass
if( $class->__establishProposedRelationship( 'fooList' ) ) {
# $class has a method called fooList that can now be queried
} else {
# $class had no relationship to foo that was recorded in the DB
}
validates proposed relatioships, and if they are valid, then establishes them.
my $many_to_many_hash = __PACKAGE__->getManyToMany();
Returns a hash of many-to-many accessors this packages contains.
The hash follows the format { accessor => package_referenced }
__PACKAGE__->getPublishedManyRefs();
Returns a hash describing package accessors that return lists of object references. This is the "published" list of accessors; accessors to mapping classes are excluded in favor of accessors that retrieve objects on the other end of the mapping class.
The hash follows the format { accessor => package_referenced }
my @published_columns = $package->getPublishedCols();
Returns a list of package column accessors. This is the "published" list of accessors; accessors to foreign key ids are excluded in favor of accessors to foreign objects. Also, exclude the 'target' field for ST's because it duplicates the more specific dataset, image, or feature field.
__PACKAGE__->getAccessorReferenceType($alias);
Returns the package name of the object that will be returned by the alias method. If the alias returns a scalar or if the alias is unknown, this method will return undef.
The following instance methods are defined in DBObject and are available to all DBObject subclasses (in addition to the various accessors automatically created by the class definition methods).
my $id = $instance->id();
Returns the primary key value for instances whose subclasses have them defined.
$instance->storeObject();
The mutator methods created by the addColumn method only change the values which are in memory. They are not automatically flushed to the database. (This is mainly to allow you to modify several columns while only sending a single UPDATE statement to the database.) This method flushes any outstanding changes to the database.
This method does absolutely no transaction control whatsoever. You must manage that yourself with the transaction control methods in OME::Session. Note that Postgres, at least, does not automatically commit a transaction at the end of a program, so any uncommitted changes will not make it into the database.
$instance->deleteObject();
Deletes this object from the database. Be very careful before calling this! No referential integrity constraints are enforced; if the database engine does not want you to delete this object, you will get an error.
This method does absolutely no transaction control whatsoever. You must manage that yourself with the transaction control methods in OME::Session. Note that Postgres, at least, does not automatically commit a transaction at the end of a program, so any uncommitted changes will not make it into the database.
my $data_hash = $instance->getDataHash([$aliases]);
Returns the values of this instance as a hash. The keys of the hash
are the aliases of each logical column, the values of the hash are the
values of the logical columns. If the $aliases parameter is specified
and is an array reference, only those aliases in that arrayref will
appear in the data hash. Otherwise, all of the subclass's aliases
will appear. The integer primary key, if present in the subclass,
will always appear with a key of id.
my $data_hashes = MyNewClass->getDataHashes([$aliases],$list);
This is just a convenience method which calls getDataHash($aliases) on
each element of the list array reference. Its result is an array
reference of hash references, one data hash per input DBObject
instance.
$instance->refresh();
Refreshes this DBObject instance from the database. If any columns have been modified without calling storeObject, those changes will be overwritten. (In that sense, this method can be viewed as a "revert" method in addition to a "refresh" method.)
For the brave and/or foolhardy, the following methods are used internally to implement the database I/O functionality. They should almost never be called from outside the OME::DBObject and OME::Factory classes. And by "almost never", I mean "never ever".
my $instance = $class->__getCachedObject($id);
If an object with the specified primary key ID is in the specified class's object cache, it is returned. Otherwise, undef is returned.
$instance->__storeCachedObject();
Stores $instance in its class's object cache.
__activateSTColumn($type_name);
In order to access or search based on a column which is an attribute reference, the semantic type that it points to must be loaded in. This method does that. This method assumes that the OME::SemanticType class has already been loaded and defined, and that there is a valid activate session object.
my ($table,$column) = $class->__verifyLocation($location);
This method is used by the addPrimaryKey and addColumn methods to parse the $location parameter. It ensures that the location is in one of the two valid forms ("COLUMN" or "TABLE.COLUMN"). If it's in the "COLUMN" form, it ensures that a default table has been specified. The (possibly default) table and column values are then returned, after being converted to lowercase.
my ($first_table,$first_key) = $class->
__addJoins($columns_needed,$tables_used,$where_clauses);
For subclasses which span multiple tables, this method is responsible for adding the appropriate WHERE clauses to an SQL statement to join those tables.
The three parameters are references to the internal variables of the
SQL-generation methods. The $columns_needed parameter is an array
reference containing the column clauses of the statement (the part
between SELECT and FROM). A primary key column clause is
appended to this array if necessary. The $table_used parameter is a
hash reference, with the keys being the names of the tables that are
needed for the SQL statement. (There's no need to join tables which
don't appear in the statement.) All of these tables should be defined
by this DBObject subclass. This has is not modified. The
$where_clauses parameter is an array reference containing the WHERE
clauses of the statement. The necessary joins will be appended to
this list.
The return value is the first table and primary key that was found while iterating through the $tables_used hash. For subclasses with primary keys, then, "${first_table}.${first_key}" is guaranteed to be the database location of a primary key which appears in the SQL statement.
my ($local_column,$target_table_name,$target_column,$target_class) =
$class->__resolveReference($local_class,$target_alias);
Used by the __addForeignJoin method to resolve aliases that are references. References can be direct (has-one) references, indirect or reverse references (has-many) or infered references (inferred has-many).
my ($table,$column) = $class->__getPrimaryKeyLocation();
Gets the table and column that contains a primary key for this class. By default, this is the default table for the class and its primary key, though potentially, this could be any primary key definition if a default table is not defined. Returns an array containing the table name and the column name. If the class has no primary keys defined, returns undef.
my $result_key = $class->
__addForeignJoin($fk_number,$aliases,
$foreign_tables,$foreign_aliases,$join_clauses);
Used by the __getQueryLocation method to add a foreign-key join clause to an SQL statement. The $aliases parameter should be an array reference, and should contain the result of splitting a compound alias on periods. A compound alias allows you to follow an arbitrary number of references in a search clause. All but the last alias in the array must be a reference alias. (The last one can be, but does not have to be.) Each reference alias can be either an explicit has-many or has-one alias or it can be an inferred has-many of the form [MyClass]List (e.g. CategoryList). The examples provided in the "Search criteria" section of the OME::Factory documentation should make things clearer.
The $fk_number parameter should be a reference to an integer counter; it is used as a nonce to guarantee the uniqueness of any table re-names added to the FROM clause of the statement. The $foreign_tables parameter should be an array reference of the FROM clauses of the statement. Any new foreign-key tables needed to satisfy the foreign-key clause are added to this list. The $join_clauses parameter is an array reference of the WHERE clauses of the statement. The joins needed to add the foreign-key tables are added to this list.
The $foreign_aliases parameter is a hash reference. It will contain the statement-specific names of each foreign-key alias in the search query, taking into account any table-renaming needed to ensure the uniqueness of the table names in the statement.
After this method returns, $foreign_aliases->{$result_key} will be a two-element array reference. Joining these two elements with a period gives a statement location for the requested foreign-key alias. This statement location is suitable for using in a column, WHERE, ORDER BY, or GROUP BY clause.
my $location = $class->
__getQueryLocation($fkey_number,$foreign_tables,$foreign_aliases,
$where_clauses,$tables_used,$column_alias);
This method performs the meat of the SQL-generation methods. It is responsible for taking an arbitrary alias expression for this class ($column_alias) and turning it into a simple, statement-specific "TABLE.COLUMN" expression. This alias might possibly follow an arbitrary number of foreign-key relationships, as described in the "Search criteria" section of OME::Factory. The table portion of the returned location might not correspond to an actual database table name; table re-naming is used in the statement to ensure that if a single database table appears in the statement more than once, each copy has a distinct name. The location which is returned is suitable for using in a column, WHERE, ORDER BY, or GROUP BY clause.
All of the other parameters (besides $column_alias) are part of the internal state of the SQL-generation methods. The $fkey_number is a reference to an integer counter, which is a nonce used by the __addForeignKeyJoins method. The tables which appear in the statement appear in the $foreign_tables arrayref and the $tables_used hashref. (The first is for foreign-key tables, the second for tables for this subclass's data.) The $where_clauses parameter is an arrayref of the WHERE clauses for this statement. It is augmented as necessary to include the joins necessary for the tables in the statement. The $foreign_aliases parameter is a hashref which is used by the __addForeignJoins method to ensure that each foreign alias is only added to the statement once. (It's safest to assume that it's opaque...)
my ($sql,$id_available,$values) = $class->
__makeSelectSQL($columns_wanted,$criteria);
Creates an SQL statement suitable for retrieving instances of this subclass which match the specified search criteria. (Search criteria are described in the OME::Factory documentation.) If defined, the $columns_wanted parameter should be an array references of aliases for this subclass; the SQL statement will only contain enough column clauses to populate those logical columns. If it is not specified, everything will be filled in.
The $sql result will be the text of the SQL statement. The
$id_available result will be a Boolean indicating whether the SQL
statement returns a primary key. If so, it will be in an SQL column
named id. The $values result will be an array reference of the
values for the SQL statement. These should be used as the bind values
for the DBI call; this array is guaranteed to be in the same order as
any ?-placeholders which appear in the SQL statement.
Optionally, a third parameter can be passed in. If defined, the sql will combine the search criteria with OR's instead of AND's.
my $sqls = $instance->__makeUpdateSQLs();
Creates a series of UPDATE SQL statements suitable for saving the modified state of the specified DBObject instance to the database. It will only modify the logical columns which have changed since the instance was last loaded from or saved to the database. The $sqls result has the same format as the $sqls result of the __makeInsertSQLs method.
my $sqls = $instance->__makeDeleteSQLs();
Creates a series of DELETE SQL statements suitable for deleting the specified DBObject instance from the database. The $sqls result has the same format as the $sqls result of the __makeInsertSQLs method.
my $instance = $class->__createNewInstance($dbh,$data_hash);
Used by the OME::Factory->newObject and ->newAttribute methods to create a DBObject instance corresponding to a new entry in the database. The $dbh parameter should be an active connection to the database. The $data_hash parameter should contain the values for the new DBObject instance.
Note that this method is not truly atomic in the case of subclasses which span tables since we cannot rely on hierarchical transactions in the underlying database. (In other words, we cannot necessarily start a "subtransaction" without clobbering a transaction which is occuring outside of this method.) Therefore, if one of the INSERT statements fails, this method cannot roll back the INSERT statements which had succeeded to that point.
$instance->__writeToDatabase($dbh);
Saves the modified state of this instance to the database. It will only save the logical columns which have changed since the instance was last loaded from or saved to the database.
Note that this method is not truly atomic in the case of subclasses which span tables since we cannot rely on hierarchical transactions in the underlying database. (In other words, we cannot necessarily start a "subtransaction" without clobbering a transaction which is occuring outside of this method.) Therefore, if one of the UPDATE statements fails, this method cannot roll back the UPDATE statements which had succeeded to that point.
$instance->__writeToDatabase($dbh);
Deletes this instance from the database. No referential integrity constraints are checked; if the database doesn't want you to delete this object, you'll get an error.
Note that this method is not truly atomic in the case of subclasses which span tables since we cannot rely on hierarchical transactions in the underlying database. (In other words, we cannot necessarily start a "subtransaction" without clobbering a transaction which is occuring outside of this method.) Therefore, if one of the DELETE statements fails, this method cannot roll back the DELETE statements which had succeeded to that point.
$instance->__fillInstance($id_available,$columns_wanted,$sth_vals);
Fills in a new DBObject instance with the values fetched from a DBI
statement handle. The $id_available parameter should indicate whether
a primary key ID was retrieved by the statement. The $columns_wanted
should be an array of logical column aliases, in the order that they
appear in the statement. The $sth_vals parameter should be the array
reference returned by the fetch() method of the statement handle.
my $instance = $class->__newInstance($sth,$id_available,
[$columns_wanted]);
Creates a new DBObject instance corresponding to a row retrieved by a DBI statement handle. This statement will most likely have been generated by the __makeSelectSQL method. The $sth parameter should be an open statement handle. The $id_available parameter should indicate whether the first column is a primary key. If specified, the $columns_wanted parameter should be an arrayref of logical column aliases, and should be in the same order as the columns returned by the statement. If not specified, all of the columns defined by the class will be used.
This method will retrieve one row of information from the statement handle and create a DBObject instance to hold that data. If the statement does not have any more rows left to return, this method will return undef.
my $instance = $class->__newByID($dbh,$id,$columns_wanted);
Returns a new DBObject instance to represent an entry in the database with the given primary key ID. Obviously, this DBObject subclass must define a primary key for this method to be useful. (If it doesn't, an error will be thrown.) The $dbh parameter should be an active database handle. The $id parameter should be the primary key ID to load. If specified, the $columns_wanted parameter should be an arrayref of logical column aliases, and should be in the same order as the columns returned by the statement. If not specified, all of the columns defined by the class will be used.
Douglas Creager <dcreager@alum.mit.edu>, Open Microscopy Environment, MIT