Reference Columns

package OME::SemanticType;

use strict;
use OME;
our $VERSION = $OME::VERSION;

use OME::DBObject;
use base qw(OME::DBObject);

__PACKAGE__->newClass();
__PACKAGE__->setDefaultTable('semantic_types');
__PACKAGE__->setSequence('semantic_type_seq');
__PACKAGE__->addPrimaryKey('semantic_type_id');
__PACKAGE__->addColumn(name => 'name',
                       {
                        SQLType => 'varchar(64)',
                        NotNull => 1,
                        Unique  => 1,
                       });
__PACKAGE__->addColumn(granularity => 'granularity',
                       {
                        SQLType => 'char(1)',
                        NotNull => 1,
                        Check   => "(granularity in ('G','D','I','F'))",
                       });
__PACKAGE__->addColumn(description => 'description',{SQLType => 'text'});
__PACKAGE__->hasMany('semantic_elements',
                     'OME::SemanticType::Element' => 'semantic_type');

Has-many foreign key columns are defined by the hasMany method. Has-many columns are used to define one side of a one-to-many relationship. Has-many foreign keys do not create any database columns — rather, they define a query into the foreign key table. For instance, each semantic type is made up of a list of semantic elements. In the database, this is encoded as a one-to-many relationship between the SEMANTIC_TYPES and SEMANTIC_ELEMENTS tables. The OME::SemanticType::Element class contains a has-one foreign key column specifying which semantic type it belongs to. The hasMany call in listing 1 defines a query method which returns all of the semantic elements belonging to a given semantic type.

The first argument to hasMany is the query's alias, which is used as the name of the new query method. The second argument is the class which defines the foreign key table. The third argument specifies, by alias, which column in the foreign key table refers back to this class. It should be defined in the foreign key class as a has-one foreign key column. Therefore, the call in listing 1 says that the semantic_type column in the OME::SemanticType::Element class refers to an instance of the current package (OME::SemanticType). Further, it creates a method called semantic_elements which can be used to retrieve the OME::SemanticType::Elements that point to a specific OME::SemanticType.

Many-to-many relationships are not directly supported; as is usual in database design, a many-to-many relationship has to be modeled with a mapping table and two one-to-many relationships. This mapping table would have its own DBObject subclass. The OME::Project, OME::Dataset, and OME::Project::DatasetMap classes provide an example of a many-to-many relationship.

There are no examples of a has-one foreign key column in OME::SemanticType. However, the has-one column in OME::SemanticType::Element that corresponds to the one-to-many relationship just described is presented in listing 2.

__PACKAGE__->addColumn(semantic_type => 'semantic_type_id',
                       'OME::SemanticType',
                       {
                        SQLType => 'integer',
                        NotNull => 1,
                        Indexed => 1,
                        ForeignKey => 'semantic_types',
                       });

Has-one foreign key columns are defined by the addColumn method, just like standard columns. The difference is that there is an extra parameter required to define a has-one column — the foreign key class. This new parameter is inserted between the database column name and the SQL options. For has-one columns, the SQLType option should always be integer, and the ForeignKey option should be set to the name of the foreign key table (not class). (This ensures that an appropriate REFERENCES clause is added to the table.) The accessor/mutator method which is created has a slightly different behavior than the method created for a standard column. When called as an accessor, it will automatically load in an instance of the foreign key class for the row which is pointed to. When called as a mutator, it will accept either an object of the appropriate type, or an integer key value.