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.
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.
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!
