An Introduction to Rose::DB

1. An Introduction to Rose::DB

2. Object Relational Mapping • A way to map database tables/rows to Objects • Allows programmers to keep thinking in a perlish way • Perl is blessed with quite a few good ORMs: Tangram, Class::DBI, DBIx::Class, and now Rose::DB

3. Why should I use an ORM? ORM’s Are Tedium Removers Instead of doing this over and over: my $update_qry = “ UPDATE foo SET bar = ? WHERE id = ?”; my $selqry = “SELECT id, bar FROM foo WHERE bar < ?”; my $selsth = $dbh->prepare($selqry); my $updsth = $dbh->prepare($update_qry); $selsth->execute(10); while (my ($id, $bar) = $selsth->fetchrow_array) { $updsth->execute(complex_sub($bar), $id); } $selsth->finish; # ad nauseum You can do this: foreach $foo ( My::RDBO::Foo::Manager->get_foos( query => [ bar => [ lt => 10] ]) { $foo->bar(complex_sub($foo->bar)->save; }

4. Why Should I use an ORM? Works well with MVC philosophies • Separates Data Model from other parts of the program • Removes Temptation to Just put a quick SQL statement inside your main code. • Exposes chains of methods to templating systems like Template Toolkit Allows for Perl Implemented Virtual Fields You can place: sub full_name { my $self = shift; join(‘ ‘, $self->first_name, $self->last_name); } In the package implementing the class for a table just having first_name and last_name fields.

5. History Lesson: Why Rose::DB? • Since about 2002 Class::DBI was the ORM of choice for Perl. • Tony Bowdon, CDBI’s maintainer essentially took his toys and went home. • Left a Perl community in a strange situation where a major module would be left in a state where it couldn’t be updated. • Other ORMs were not nearly as efficient as CDBI.

6. How Rose::DB is different • It abstracts a single row of a table first, and then gives a mechanism to get sets of rows. • Most other ORM’s abstract the table and then give mechanisms to restrict the table. • Very efficient: • http://rose.sourceforge.net/wiki/index.php/RDBO/Benchmark results compare Class::DBI, DBIx::Class, Rose::DB • Shows that Rose::DB is three to sixteen times as efficient as it’s nearest competitor for everything from simple to complex queries, inserts, and updates. • http://rose.sourceforge.net/wiki/index.php/RDBO/Benchmark/DBI results compare these to raw DBI • Shows that Rose::DB is only 40% - 80% less efficient that raw DBI. • You get programmer efficiency and maintainability at the cost of program efficiency, but that cost isn’t “too bad”.

7. Tutorial: Setting Up a Database Create a Mongers/RDB.pm to describe the database connection. For an existing database this can be as simple as: package Mongers::RDB; use base qw(Rose::DB); use Rose::DB::Object::Loader; our $FILENAME = "mongerdb.sqlite"; __PACKAGE__->register_db( driver => 'sqlite', database => $FILENAME, ); my $loader = Rose::DB::Object::Loader->new( db_class => __PACKAGE__, base_class => 'Mongers::RDBO', class_prefix => 'Mongers::RDBO', with_foreign_keys => 1, with_relationships => 1, ); $loader->make_classes; # slightly fancier one used for examples though

8. Tutorial: Setting Up A Rose::DB::Object This truly is simple: package Mongers::RDBO; use base qw(Rose::DB::Object); use Mongers::RDB; sub init_db { shift; Mongers::RDB->new } 1; Now you can do: #!/usr/bin/perl use strict; use warnings; use Mongers::RDBO; And your database will be created (if needed) and all of the packages implementing ORM will be created in memory.

9. Tutorial: Populating the Database #!/usr/bin/perl use strict; use warnings; use Mongers::RDBO; # Create Some Applications Mongers::RDBO::Application->new( title => 'Crapinator', release_date => '2005-04-01' )->save; Mongers::RDBO::Application->new( title => 'Decrapinator', release_date => '2005-04-15' )->save; Mongers::RDBO::Application->new( title => 'Froboznicator', release_date=> '2007-07-19' )->save; # cf. addapps, addfolks, addshops

10. Tutorial: Populating With FK’s #!/usr/bin/perl use strict; use warnings; use Mongers::RDBO; use List::Util qw( first ); # Appz R Us Does the Crapinator link_app_to_shop( 'Crapinator', 'Appz R Us' ); # Appz R Us works with eXtreme Proggies on the DeCrapinator link_app_to_shop( 'Decrapinator', 'Appz R Us', 'eXtreme Proggies'); # It's the Froboznicator by SillySoft link_app_to_shop( 'Froboznicator', 'SillySoft' ); sub link_app_to_shop { my $app_name = shift || return; my @shop_names = @_; my $app = first { defined($_) } @{ Mongers::RDBO::Application::Manager->get_applications( query => [ title => $app_name ] ) }; my @shops = (); foreach ( @shop_names ) { my $shop = first { defined($_) } @{ Mongers::RDBO::Sweatshop::Manager->get_sweatshops( query => [ name => $_ ] ) }; push @shops, $shop if ($shop); } if ($app && @shops) { $app->sweatshops( \@shops ); $app->save; } } #cf link_roles too

11. Complex Query Example • Find the applications that were developed by someone born before 1950. • Cf. apps_by_oldtimers

12. Overhead • Lots of overhead for generating all those class packages in databases with large tables. • Modify Mongers::RDB.pm to write out packages to be loaded later • Run “perl –MMongers::RDBO –e 1” • Modify Mongers::RDB.pm to not use dynamic package creation. • View apps_by_oldtimers2

13. Can Also Add Class Methods • Say we want to display the Person’s age in years • We can add an “age” method to the Person RDBO.

14. Transactions my $db = $rdbo->db; $db->begin_work; if ($rdbo->save) { $db->commit; } else { $db->rollback; }

15. Other Goodies • Triggers for actions to occur before/after insert/update/delete/load – Allows arbitrary code to be run whenever certain database transactions occur • Useful for interacting with outside systems (LDAP, Web Services, etc.) and encrypted fields.