/usr/share/perl5/Class/DBI/Lite/Tutorial.pod is in libclass-dbi-lite-perl 1.026-1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 | =pod
=head1 NAME
Class::DBI::Lite::Tutorial - How To Use Class::DBI::Lite
=head1 QUICKSTART
The following examples are intended to work with MySQL version 5.1.x
=head2 Example Database
create table artists (
artist_id integer unsigned not null primary key auto_increment,
name varchar(100) not null,
) engine=innodb charset=utf8;
create table albums (
album_id integer unsigned not null primary key auto_increment,
arist_id integer unsigned not null,
name varchar(100) not null,
unique(artist_id, name),
foreign key fk_albums_artists (artist_id) references artists( artist_id ) on delete restrict
) engine=innodb charset=utf8;
=head2 Example Classes
First you must subclass C<Class::DBI::Lite::*> and define your database connection:
=head3 Your 'Model' Class:
File C<lib/App/db/model.pm>
package App::db::model;
use strict;
use warnings 'all';
use base 'Class::DBI::Lite::mysql';
__PACKAGE__->connection(
'DBI:mysql:dbname:hostname', 'username', 'password'
);
1;# return true:
=head3 Your 'Entity' Classes:
File C<lib/App/db/artist.pm>
package App::db::artist;
use strict;
use warnings 'all';
use base 'App::db::model';
__PACKAGE__->set_up_table('artists');
# Artists have many Albums, referenced by the field 'artist_id':
__PACKAGE__->has_many(
albums =>
'App::db::album' =>
'artist_id'
);
1;# return true:
File C<lib/App/db/album.pm>
package App::db::album;
use strict;
use warnings 'all';
use base 'App::db::model';
__PACKAGE__->set_up_table('albums');
# Albums have an artist, referenced by the field 'artist_id'
__PACKAGE__->belongs_to(
artist =>
'App::db::artist' =>
'artist_id'
);
1;# return true:
=head2 Example Usage
Using C<Class::DBI::Lite> is simple.
#!/usr/bin/perl -w
use strict;
use warnings 'all';
use App::db::artist;
use App::db::album;
# Now you're all set!
=head2 Table Relationships
If you have relationships between your classes, you can express them like this:
=head3 One-To-Many Relationships
__PACKAGE__->has_many(
<methodname> =>
<classname> =>
<their_fieldname>
);
=head3 One-To-One Relationships
__PACKAGE__->has_one(
<methodname> =>
<classname> =>
<my_fieldname>
);
...or...
__PACKAGE__->belongs_to(
<methodname> =>
<classname> =>
<my_fieldname>
);
So in our example we say:
# Artists have many Albums, referenced by the field 'artist_id':
__PACKAGE__->has_many(
albums =>
'App::db::album' =>
'artist_id'
);
Which means that given an instance of C<My::Artist> you can do this:
# Fetch the artist:
my $artist = App::db::artist->retrieve( 1 );
# Fetch the artist's albums:
my @albums = $artist->albums;
# Print the artist's name for each of these albums:
foreach my $album ( @albums ) {
print $album->artist->name;
}
# As of version 1.005 You can also do the following:
my @best_of = $artist->albums({name => { LIKE => '%Best of%'} });
my @sorted = $artist->albums(undef, { order_by => 'name DESC' } );
my @sorted_best = $artist->albums({
name => { LIKE => '%Best of%' }
}, {
order_by => 'name DESC'
});
my @top_five = $artist->albums({
name => { LIKE => '%Best of%' }
}, {
order_by => 'name DESC limit 0, 5'
});
That example would look like this if we were doing it with hand-coded SQL statements:
# **** THE OLD WAY: ****
use DBI;
my $dbh = DBI->connect('DBI:mysql:dbname:hostname', 'username', 'password' );
# Fetch the artist:
my $sth = $dbh->prepare("SELECT * FROM artists WHERE artist_id = ?");
$sth->execute( 1 );
my ($artist) = $sth->fetchrow_hashref;
$sth->finish();
# Fetch the artist's albums:
my @albums = ( );
$sth = $dbh->prepare("SELECT * FROM albums WHERE artist_id = ?");
$sth->execute( $artist->{artist_id} );
while( my $album = $sth->fetchrow_hashref ) {
push @albums, $album;
}
$sth->finish();
# Print the artist's name for each of these albums:
$sth = $dbh->prepare("SELECT * FROM artists WHERE artist_id = ?");
foreach my $album ( @albums ) {
$sth->execute( $album->{artist_id} );
my ($artist) = $sth->fetchrow_hashref;
print $artist->{name};
}
$sth->finish();
=head3 Creating
# Create an artist:
my $artist = App::db::artist->create( name => 'Bob Marley' );
# These both do the same:
print $artist->id;
print $artist->artist_id;
=head3 List Context vs Scalar Context
If you execute a search method in list context, you get an array. Executing a search
method in scalar context returns an iterator.
B<List Context>:
my @albums = App::db::album->search( name => 'Legend' );
my @albums = $artist->albums;
B<Scalar Context>:
my $albums = App::db::album->search( name => 'Legend' );
my $albums = $artist->albums;
Iterators can be worked through like this:
while( my $album = $albums->next ) {
# Work with $album:
print $album->name;
}
# How many items are in the iterator?
print $albums->count;
B<**NOTE:> Any C<has_many> extension methods are also considered 'search' methods,
so they will conform to this list/scalar context behavior as well.
=head3 Searching
Returns all results as objects of the correct type:
B<Basic Searching>
my @albums = App::db::album->search( name => 'Legend' );
my @albums = App::db::album->search(
artist_id => $artist->id,
);
B<Advanced Searching>
Advanced searching takes 1 or 2 parameters:
App::db::album->search_where( { <args> }, [<order_by and limits>] );
Examples:
my @albums = App::db::album->search_where({
artist_id => { IN => [ 1, 2, 3 ] }
});
my @albums = App::db::album->search_where({
name => { LIKE => 'Lege%' }
}, {
order_by => 'name DESC LIMIT 0, 10'
});
C<search_where> uses L<SQL::Abstract> to generate the SQL, so look there for more examples.
=head3 Counting
Sometimes you just need to know how many records match your query:
my $count = App::db::album->count_search( name => 'Bob Marley' );
Using C<count_where> you can make more interesting queries:
my $count = App::db::album->count_search_where({
name => { LIKE => 'Legen%' },
artist_id => { IN => [ 1, 2, 3 ] }
});
C<count_search_where> uses L<SQL::Abstract> to generate the SQL, so look there for more examples.
=head3 Updating
Example:
my $artist = App::db::artist->create( name => 'Bob Marley' );
# Change the name:
$artist->name( 'Bob' );
print $artist->name; # Bob
# Save the changes to the database:
$artist->update;
If you don't call C<update> after making changes to an object, you will get a warning that looks like this:
My::Artist #1 DESTROY'd without saving changes to name
To cause the object to forget about any unsaved changes you made to it, do this:
# Hit the reset button:
$artist->discard_changes;
=head3 Deleting
Removes the item from the database instantly:
$artist->delete;
It's the same as:
my $sth = $dbh->prepare("DELETE FROM artists WHERE artist_id = ?");
$sth->execute( 1 );
$sth->finish();
=head1 INTERMEDIATE
=head2 Event Triggers
You can program triggers from within your application code. These can be useful
but beware of mixing too much business logic in with your data logic.
=head3 before_create
__PACKAGE__->add_trigger( before_create => sub {
my ($self) = @_;
# Do something before we are created:
});
=head3 after_create
__PACKAGE__->add_trigger( after_create => sub {
my ($self) = @_;
# Do something now that we've been created:
});
=head3 before_update
__PACKAGE__->add_trigger( before_update => sub {
my ($self) = @_;
# Do something before we are updated:
});
=head3 after_update
__PACKAGE__->add_trigger( after_update => sub {
my ($self) = @_;
# Do something now that we've been updated:
});
=head3 before_delete
__PACKAGE__->add_trigger( before_delete => sub {
my ($self) = @_;
# Do something before we are deleted:
});
=head3 after_delete
__PACKAGE__->add_trigger( after_delete => sub {
my ($obj) = @_;
# Obj only contains { artist_id => 1 }
# Do something with $obj:
});
=head2 Field Triggers
Sometimes you just want to add a trigger to a specific field.
=head3 before_update_<fieldname>
package App::db::artist;
...
__PACKAGE__->add_trigger( before_update_name => sub {
my ($self, $old_value, $new_value) = @_;
warn "About to change this artist's name from '$old_value' to '$new_value'";
});
=head3 after_update_<fieldname>
package App::db::artist;
...
__PACKAGE__->add_trigger( after_update_name => sub {
my ($self, $old_value, $new_value) = @_;
warn "Finished changing this artist's name from '$old_value' to '$new_value'";
});
=head2 Transactions
This is how transactions are done with C<Class::DBI::Lite>:
# Safely update the name of every album:
eval {
App::db::artist->do_transaction( sub {
# Your transaction code goes here:
my $artist = App::db::artist->retrieve( 1 );
foreach my $album ( $artist->albums ) {
$album->name( $artist->name . ': ' . $album->name );
$album->update;
}
});
};
if( $@ ) {
# There was an error:
die $@;
}
else {
# Everything was OK:
}
=head2 Getting the Database Handle
You can get the normal database handle by calling C<db_Main> on any of your classes.
my $dbh = App::db::artist->db_Main;
=head2 Custom SQL Queries:
You can call the C<sth_to_objects> method to convert a prepared statement into
objects of a pre-defined type:
# Step 1: Prepare the statement:
my $sth = App::db::artist->db_Main->prepare("SELECT * FROM artists WHERE name LIKE ?");
# Step 2: Execute the statement:
$sth->execute( 'Bob%' );
# Step 3: Call sth_to_objects:
my @artists = App::db::artist->sth_to_objects( $sth );
=head1 ADVANCED TOPICS
=head2 Running under mod_perl
C<Class::DBI::Lite> is fully-tested and works perfectly under C<mod_perl>. Because
it uses L<Ima::DBI::Contextual> under the hood, you get all of its benefits.
=cut
|