dcsimg

Configuring Rose::DB::Object Metadata

Rose::DB::Object makes typical CRUD a breeze.

Last month, I introduced Rose::DB::Object as the current “best of breed” object-relational mapper for Perl programmers. With Rose::DB::Object, you can avoid nearly all of the boring, routine SQL crafting. This month, I’ll continue my presentation, by showing a simple database and explaning how to set up the metadata of Rose::DB::Object so that proper accesses can be made.

My example is a common one: a database of motion pictures, including a list of films, the people involved in making them, and the studios that produce the films. To keep it simple, I’ve discarded most of the unique-to-a-row data, keeping only a representative sample along with the linking columns. Thus for each person, I’ll use a name, birthdate, and a unique identifier (to distinguish name collisions). Each film has only a title and a release date, along with its unique identifier. To show that you don’t need to create unique identifiers for everything, let’s use the studio name as its own natural primary key.

Of course, unrelated data is uninteresting, so I’ll include the roles that each person played on a film (possibly more than one), including both a broad category (such as actor, director) along with specific details (the name of the character for an actor, for example). For the film-to-studio mapping, I’ll use a minimal, many-to-many mapping table consisting entirely of the two foreign key columns and name it in such a way that Rose::DB::Object will provide some additional support.

To begin, I create the minimal support structure for Rose::DB::Object to understand my database, shown in Listing One.

Lines 6 through 9 create the My::RDBO class, which is used as the base class for all of my Rose::DB::Object classes. Each of those classes represent a single row of the database. For each class, I’ll also have a class with ::Manager appended to perform tasks that are pertinent to the table as a whole. In this case, the only needed information piece is how to create a Rose::DB- derived object if one isn’t provided. The init_db method returns a new My::RDB object, defined in the remainder of this code snippet.

I fake up an entry in %INC for this class because apparently Rose::DB tries to require My::RDBO, even though the class is already defined. The %INC mangling keeps this from being an error. (Of course, by the time you read this, John Siracusa, the author of Rose::DB, will likely have also read this and fixed Rose::DB to no longer trigger an error on that.)

Line 15 sets up the name of the SQLite file that I’m using for testing. It concatenates the name of this file with .sqlite. Lines 17 to 20 define the default database parameters, including the just-computed name.

Lines 22 to 24 initialize the database, unless the database already exists. I’ve added 0 and for testing, forcing the database to be initialized while I change the database schema. Lines 26 to 67 describe how to initialize the database. For an SQLite database, I merely need to remove the single database file, and then connect to the database and issue the appropriate DDL statements.

Line 28 creates the My::RDB object. This object maintains the metadata about the desired database. For example, line 29 verifies that this is indeed the SQLite database that was defined earlier (or at least some SQLite database). Line 30 removes the existing database.

Lines 31 to 33 create a DBI database handle, commonly called $dbh in the DBI documentation, and set it up to be quiet on errors, because I’m going to print my own. Lines 34 and 35 (and the subsequent “here” document) provide the DDL to create the tables. Each blank-line-separated statement is executed independently, aborting the entire process on failure.

Lines 36 to 65 define the five tables, using standard SQL (as understood by SQLite). The tables and columns follow the default naming conventions of Rose::DB::Object::ConventionManager, which makes it easy to automatically extract the metadata, starting in line 69.

The implicit true value of the string ”use loader” triggers the Rose::DB::Object::Loader as the means of providing the metadata. Lines 71 to 75 create the loader, again defining the minimal overrides for the defaults to match the current scenario. Line 76 triggers the probing, resulting in metadata for the five tables and the corresponding ten classes (five for the rows, and five for the tables). The resulting 10-element list ends up in @classes.

At this point, I could begin creating rows and soliciting queries against the database. However, to see the handiwork that the probing has generated, let’s continue into line 78, which loops over each created class to dump the equivalent Perl code for the metadata. The result of that output (from running the program) is in Listing Two.

Ultimately, you can capture that Perl text into the correct modules (using make_modules instead of make_classes), and completely avoid the probing step. In the long run, this is the best strategy, but for rapid development, letting the metadata come from probing the database provides the most flexibility.

As you look at each of the generated classes, you can see how the metadata corresponds to the table schema. For example, in lines 3 to 27, we see that My::RDBO::Person describes the persons table (as noted in line 10). The three columns are described in lines 12 to 16, along with their type. Rose::DB::Object provides special support for date- type columns, pulling in the DateTime modules for easy manipulation. Line 18 correctly labels the id column as the primary key for this table.

LISTING ONE: An example of using Rose::DB
        01 #!/usr/bin/perl
02 use strict;
03 use warnings;
04 
05 BEGIN {
06   package My::RDBO; $INC{"My/RDBO.pm"} = __FILE__;
07   use base qw(Rose::DB::Object);
08   sub init_db { shift; My::RDB->new }
09 }
10 
11 BEGIN {
12   package My::RDB; $INC{"My/RDB.pm"} = __FILE__;
13   use base qw(Rose::DB);
14 
15   our $FILENAME = __FILE__ . ".sqlite";
16 
17   __PACKAGE__->register_db
18     (driver => ’sqlite’,
19      database => $FILENAME,
20     );
21 
22   unless (0 and -f $FILENAME) {
23     __PACKAGE__->initialize_database;
24   }
25 
26   sub initialize_database {
27     my $class = shift;
28     my $db = $class->new;
29     die unless $db->driver eq "sqlite";
30     unlink $db->database;
31     my $dbh = $db->dbh;
32     $dbh->{RaiseError} = 0;
33     $dbh->{PrintError} = 0;
34     $dbh->do($_) or die "$DBI::errstr
35       for $_" for split /\n{2,}/, <<’END_OF_SQL’;
36 CREATE TABLE persons (
37   id INTEGER PRIMARY KEY AUTOINCREMENT,
38   name TEXT,
39   birthdate DATE
40 )
41 
42 CREATE TABLE films (
43   id INTEGER PRIMARY KEY AUTOINCREMENT,
44   title TEXT,
45   release_date DATE
46 )
47 
48 CREATE TABLE roles (
49   id INTEGER PRIMARY KEY AUTOINCREMENT,
50   person_id INTEGER REFERENCES persons(id),
51   film_id INTEGER REFERENCES films(id),
52   category TEXT,
53   detail TEXT
54 )
55 
56 CREATE TABLE studios (
57   name TEXT PRIMARY KEY
58 )
59 
60 CREATE TABLE film_studio_map (
61   film_id INTEGER REFERENCES films(id),
62   studio_name TEXT REFERENCES studios(name),
63   PRIMARY KEY (film_id, studio_name)
64 )
65 
66 END_OF_SQL
67   }
68 
69   if ("use loader") {
70     require Rose::DB::Object::Loader;
71     my $loader = Rose::DB::Object::Loader->new
72       (db_class => __PACKAGE__,
73        base_class => ’My::RDBO’,
74        class_prefix => ’My::RDBO’,
75       );
76     my @classes = $loader->make_classes;
77 
78     if ("show resulting classes") {
79       foreach my $class (@classes) {
80         print "#" x 70, "\n";
81         if ($class->isa(’Rose::DB::Object’)) {
82           print $class->meta->perl_class_definition;
83         } else {                # Rose::DB::Object::Manager subclasses
84           print $class->perl_class_definition, "\n";
85         }
86       }
87     }
88   }
89 }

Lines 20 to 26 describe the roles relationship. As the loader pieces together the foreign keys, it can discover the classic “one to many,” “many to one,” and (under certain circumstances) “many to many” relationships. This means that you can call the roles method on a person object and get back a list of the roles that they played as a series of roles objects. This request is translated into a database call to find all rows of the roles table that refer to the person table. You also get an add_roles method to add to the existing roles.

The My::RDBO::Person::Manager class comes next in lines 31 to 41. The make_manager_methods call defines a number of class-specific methods, such as delete_persons, get_persons, get_persons_count, and update_persons, to provide table-wide queries and updates.

LISTING TWO: The classes generated by Rose::DB::Object
001 package My::RDBO::Person;
002 
003 use strict;
004 
005 use base qw(My::RDBO);
006 
007 __PACKAGE__->meta->setup(
008     table   => ’persons’,
009 
010     columns => [
011         id        => { type => ’integer’ },
012         name      => { type => ’text’ },
013         birthdate => { type => ’date’ },
014     ],
015 
016     primary_key_columns => [ ’id’ ],
017 
018     relationships => [
019         roles => {
020             class      => ’My::RDBO::Role’,
021             column_map => { id => ’person_id’ },
022             type       => ’one to many’,
023         },
024     ],
025 );
026 
027 1;
028 
029 package My::RDBO::Person::Manager;
030 
031 use base qw(Rose::DB::Object::Manager);
032 
033 use My::RDBO::Person;
034 
035 sub object_class { ’My::RDBO::Person’ }
036 
037 __PACKAGE__->make_manager_methods(’persons’);
038 
039 1;
040 
041 
042 package My::RDBO::Film;
043 
044 use strict;
045 
046 use base qw(My::RDBO);
047 
048 __PACKAGE__->meta->setup(
049     table   => ’films’,
050 
051     columns => [
052         id           => { type => ’integer’ },
053         title        => { type => ’text’ },
054         release_date => { type => ’date’ },
055     ],
056 
057     primary_key_columns => [ ’id’ ],
058 
059     relationships => [
060         roles => {
061             class      => ’My::RDBO::Role’,
062             column_map => { id => ’film_id’ },
063             type       => ’one to many’,
064         },
065     
066         studios => {
067             column_map    => { film_id => ’id’ },
068             foreign_class => ’My::RDBO::Studio’,
069             map_class     => ’My::RDBO::FilmStudioMap’,
070             map_from      => ’film’,
071             map_to        => ’studio’,
072             type          => ’many to many’,
073         },
074     ],
075 );
076 
077 1;
078 
079 package My::RDBO::Film::Manager;
080 
081 use base qw(Rose::DB::Object::Manager);
082 
083 use My::RDBO::Film;
084 
085 sub object_class { ’My::RDBO::Film’ }
086 
087 __PACKAGE__->make_manager_methods(’films’);
088 
089 1;
090 
091 
092 package My::RDBO::Role;
093 
094 use strict;
095 
096 use base qw(My::RDBO);
097 
098 __PACKAGE__->meta->setup(
099     table   => ’roles’,
100 
101     columns => [
102         id        => { type => ’integer’ },
103         person_id => { type => ’integer’ },
104         film_id   => { type => ’integer’ },
105         category  => { type => ’text’ },
106         detail    => { type => ’text’ },
107     ],
108 
109     primary_key_columns => [ ’id’ ],
110 
111     foreign_keys => [
112         film => {
113             class       => ’My::RDBO::Film’,
114             key_columns => { film_id => ’id’ },
115         },
116     
117         person => {
118             class       => ’My::RDBO::Person’,
119             key_columns => { person_id => ’id’ },
120         },
121     ],
122 );
123 
124 1;
125 
126 package My::RDBO::Role::Manager;
127 
128 use base qw(Rose::DB::Object::Manager);
129 
130 use My::RDBO::Role;
131 
132 sub object_class { ’My::RDBO::Role’ }
133 
134 __PACKAGE__->make_manager_methods(’roles’);
135 
136 1;
137 
138 
139 package My::RDBO::Studio;
140 
141 use strict;
142 
143 use base qw(My::RDBO);
144 
145 __PACKAGE__->meta->setup(
146     table   => ’studios’,
147 
148     columns => [
149         name => { type => ’text’ },
150     ],
151 
152     primary_key_columns => [ ’name’ ],
153 
154     relationships => [
155         films => {
156             column_map    => { studio_name => ’name’ },
157             foreign_class => ’My::RDBO::Film’,
158             map_class     => ’My::RDBO::FilmStudioMap’,
159             map_from      => ’studio’,
160             map_to        => ’film’,
161             type          => ’many to many’,
162         },
163     ],
164 );
165 
166 1;
167 
168 package My::RDBO::Studio::Manager;
169 
170 use base qw(Rose::DB::Object::Manager);
171 
172 use My::RDBO::Studio;
173 
174 sub object_class { ’My::RDBO::Studio’ }
175 
176 __PACKAGE__->make_manager_methods(’studios’);
177 
178 1;
179 
180 
181 package My::RDBO::FilmStudioMap;
182 
183 use strict;
184 
185 use base qw(My::RDBO);
186 
187 __PACKAGE__->meta->setup(
188     table   => ’film_studio_map’,
189 
190     columns => [
191         film_id     => { type => ’integer’ },
192         studio_name => { type => ’text’ },
193     ],
194 
195     primary_key_columns => [ ’film_id’, ’studio_name’ ],
196 
197     foreign_keys => [
198         film => {
199             class       => ’My::RDBO::Film’,
200             key_columns => { film_id => ’id’ },
201         },
202     
203         studio => {
204             class       => ’My::RDBO::Studio’,
205             key_columns => { studio_name => ’name’ },
206         },
207     ],
208 );
209 
210 1;
211 
212 package My::RDBO::FilmStudioMap::Manager;
213 
214 use base qw(Rose::DB::Object::Manager);
215 
216 use My::RDBO::FilmStudioMap;
217 
218 sub object_class { ’My::RDBO::FilmStudioMap’ }
219 
220 __PACKAGE__->make_manager_methods(’film_studio_map’);
221 
222 1;

Similarly, the My::RDBO::Film class is defined in lines 44 to 77. And like the persons table, we have a “one to many” relationship with the roles table (lines 61 to 65). However, we also see the discovered many-to-many mapping with the studios table, described in lines 68 to 75. With this mapping, you get direct support for adding and deleting any arbitrary connection between films and studios as needed, as well as simple queries in both directions.

The My::RDBO::Role class is defined in lines 94 to 124. This table has foreign keys, which are described in lines 113 to 123. Calling film returns the appropriate film row object by linking through the film_id column, with person working similarly.

The My::RDBO::Studio class (lines 141 to 166) also participates in a many-to-many mapping, which you’ve already seen described in the film table class.

Finally, My::RDBO::FilmStudioMap (lines 183 to 210) completes the description, defining the foreign keys as needed.

That’s about all I have room for in this month’s installment. Next month, I’ll show some code that uses the resulting classes to create, report on, update, and delete table rows. Until then, enjoy!

Fatal error: Call to undefined function aa_author_bios() in /opt/apache/dms/b2b/linux-mag.com/site/www/htdocs/wp-content/themes/linuxmag/single.php on line 62