| A friend of one of the authors has frequent table tennis tournaments at his workplace and has long considered building a web application to keep track of player rankings. The application, Scorekeeper , should maintain a list of games , who played in each game, the final scores for each game, and when the game took place. The application also should show how well players perform against others overall ”mostly for heckling purposes. The conceptual relationships are immediately apparent: a game has two scores and each score has a player and a game. It's easy to model this with a relational database. The next step is to build the GUI, right? Suppose that you write this application in the unfortunate style of many CGI programs in Perl's history. It's 1,500 lines long, and it contains giant conditional blocks or maybe something resembling a dispatch table. It might contain raw SQL statements, or it might use some kind of hand-rolled database abstraction. How hard is it to add a ladder system or add play-by-play recording? What if your friend suddenly wants a command-line client or a GTK interface?
Note: Your 1,500-line single-file program works, but can you prove it?
To make this program easier to extend and test, separate the backend database interaction, the display of the data, and the logic needed to control them. This pattern, sometimes referred to as Model-View-Controller, allows you to test your code more easily and leads to better code organization and reuse. How do I do that? The introduction described the relationships of the application, so the database structure is straightforward: every game, score, and player has a table. Each game has scores, and each score has a player associated with it. This lab uses SQLite, which provides a fully functional SQL database without running a server. Save the following SQL as schema.sql : BEGIN TRANSACTION; CREATE TABLE game ( id INTEGER PRIMARY KEY, date INTEGER ); CREATE TABLE score ( id INTEGER PRIMARY KEY, game INTEGER, player INTEGER, value INTEGER ); CREATE TABLE player ( id INTEGER PRIMARY KEY, name TEXT UNIQUE ); COMMIT;
Now, pipe the SQL file to the sqlite command, providing the path to the database file as the first argument: $ sqlite keeper.db < schema.sql
Note: If you need to start with an empty database, remove the keeper.db file and rerun the sqlite command .
You now have an empty SQLite database stored in keeper.db , and you can work with it using the sqlite utility. The rest of this lab uses only Perl modules to manipulate the Scorekeeper data. Save the following code as player.t : use Test::More tests => 18; use Test::Exception; use Test::Deep; use strict; use warnings; BEGIN { use_ok('Scorekeeper'); } my $a = Scorekeeper::Player->create( { name => 'PlayerA' } ); my $b = Scorekeeper::Player->create( { name => 'PlayerB' } ); my $c = Scorekeeper::Player->create( { name => 'PlayerC' } ); END { foreach my $player ( $a, $b, $c ) { $player->games->delete_all( ); $player->delete( ); } } dies_ok { Scorekeeper::Player->create( { name => $a->name( ) } ) } 'cannot create two players with the same name'; foreach my $tuple ( [ 11, 8 ], [ 9, 11 ], [ 11, 7 ], [ 10, 11 ], [ 11, 9 ] ) { my ( $score1, $score2 ) = @$tuple; my $g = Scorekeeper::Game->create( { } ); $g->add_to_scores( { player => $a, value => $score1 } ); $g->add_to_scores( { player => $b, value => $score2 } ); } my $g2 = Scorekeeper::Game->create( { } ); $g2->add_to_scores( { player => $a, value => 11 } ); $g2->add_to_scores( { player => $c, value => 8 } ); is( scalar( $a->games( ) ), 6 ); is( scalar( $b->games( ) ), 5 ); is( $a->wins( ), 4, "player A's wins" ); is( $b->wins( ), 2, "player B's wins" ); is( $c->wins( ), 0, "player C's wins" ); is( $a->losses( ), 2, "player A's losses" ); is( $b->losses( ), 3, "player B's losses" ); is( $c->losses( ), 1, "player C's losses" ); cmp_deeply( [ $a->opponents( ) ], bag( $b, $c ), "player A's opponents" ); is_deeply( [ $b->opponents( ) ], [$a], "player B's opponents" ); is_deeply( [ $c->opponents( ) ], [$a], "player C's opponents" ); is( $a->winning_percentage_against($b), 60, 'A vs B' ); is( $b->winning_percentage_against($a), 40, 'B vs A' ); is( $a->winning_percentage_against($c), 100, 'A vs C' ); is( $c->winning_percentage_against($a), 0, 'C vs A' ); is_deeply( [ Scorekeeper::Player->retrieve_all_ranked( ) ], [ $a, $b, $c ], 'players retrieved in the correct order of rank' );
One of Class::DBI 's many extensions is Class::DBI::Loader , which uses table and field names from the database to set up Class::DBI classes automatically. Another is Class::DBI::Loader::Relationship , which allows you to describe database relations as simple English sentences. The Scorekeeper module uses these modules to initialize classes for the database schema. Save the following as Scorekeeper.pm : package Scorekeeper; use strict; use warnings; use Class::DBI::Loader; use Class::DBI::Loader::Relationship; my $loader = Class::DBI::Loader->new( dsn => 'dbi:SQLite2:dbname=keeper.db', namespace => 'Scorekeeper', ); $loader->relationship( 'a game has scores' ); $loader->relationship( 'a player has games with scores' ); package Scorekeeper::Game; sub is_winner { my ( $self, $player ) = @_; my @scores = sort { return 0 unless $a and $b; $b->value( ) <=> $a->value( ) } $self->scores( ); return $player eq $scores[0]->player( ); } sub has_player { my ( $self, $player ) = @_; ( $player = = $_->player( ) ) && return 1 for $self->scores( ); return 0; } package Scorekeeper::Player; sub wins { my ($self) = @_; return scalar grep { $_->is_winner($self) } $self->games( ); } sub losses { my ($self) = @_; return scalar( $self->games( ) ) - $self->wins( ); } sub winning_percentage_against { my ( $self, $other ) = @_; my @all = grep { $_->has_player($other) } $self->games( ); my @won = grep { $_->is_winner($self) } @all; return @won / @all * 100; } sub retrieve_all_ranked { my ($self) = @_; return sort { $b->wins( ) <=> $a->wins( ) } $self->retrieve_all( ); } sub opponents { my ($self) = @_; my %seen; $seen{$_}++ for map { $_->player( ) } map { $_->scores( ) } $self->games( ); delete $seen{$self}; return grep { exists $seen{$_} } $self->retrieve_all( ); } 1;
Note: Replacing the "return if true for any" idiom in has__player() with the List::MoreUtils:: any() function will make the code much clearer. That module has many other wonderful functions, too .
Now run player.t with prove . All of the tests should pass: $ prove player.t player....ok All tests successful. Files=1, Tests=18, 1 wallclock secs ( 0.68 cusr + 0.08 csys = 0.76 CPU)
What just happened ? If you've written database code before, you may have spent a lot of time storing and retrieving data from various tables. If only there were a really slick way to turn these relationships into Perl classes without ever writing a single SQL statement! There are, in fact, a handful of modules that do just that, including Class::DBI . If you're not familiar with Class::DBI , this test file demonstrates how little code it takes to set up these relationships. When testing databases, it's a good idea to clean up any data left over after the tests end. To do this, the test file declares an END block containing statements to execute when the program ends, even if it dies. The END block iterates through every new player created and deletes any games and scores associated with that player and then the player itself, leaving no extra records in the database. (See "Testing Database Data" in Chapter 6 for more.)
Note: By default, deleting a Class:: DBI object also deletes its immediate relations .
The database schema specified that a player's name must be unique. To test this constraint, player.t attempts to create a fourth player in a dies_ok( ) block with the same name as player $a . If creating the player fails, as it should, dies_ok( ) will report a success. After adding some fake scores, player.t performs a couple of tests to see if the games( ) , wins( ) , losses( ) , and winning_percentage_against( ) methods return accurate values. The most interesting test uses Test::Deep 's cmp_deeply( ) to verify the opponents of $a are indeed the two other players that $a has played.
Note: cmp_deeply() and bag()can check the contents of an array without knowing the order of the items it contains .
The backend for Scorekeeper now has decent test coverage. You can be confident that any graphical view that you create for the Scorekeeper data will display accurate information. |
