On writing clean database code
In earlier articles we looked at the way we use “economic exchanges” to simplify complex code into a series of small understandable steps. We use similar ideas to build up business logic in our DBIx::Class model layer: combining multiple small predefined queries together to create more complex conditions for generating SQL queries against the database.
Read on below if you take the challenge to read “0011001010” language of our database experts who love to share knowledge with you again about clean code.
What is DBIx::Class?
DBIx::Class (often known more simply as DBIC) is the “Object/Relational Mapping” system we use in Tau Station. DBIC is not a really a full ORM in the traditional sense, but can be better described as:
- a stateful query generator
- a stateful result parser
- a convenient way to organise database business logic
We also make heavy use of DBIx::Class::Helpers (thanks Frew!) and you will see them used frequently in the following code samples. If you use DBIC without DBIx::Class::Helpers then I suggest you check them out.
The Bad and the Ugly
We often need a list of visible player characters which we might also restrict to those on a specific station, or perhaps just within a specific station area. We could use a single large query but since game requirements change we would have to maintain this long-term and continually risk introducing new unexpected bugs. Such a DBIC query might look something like this:
sub visible ($self) {
my $end = $self->context->now;
my $start = $end - period(
days => config('time', 'player_active_duration')
);
$_ = $schema->format_datetime($_) foreach $start, $end;
return $self->available->search(
{
$self->me('arrival') => { '<=', $end },
$self->me('last_activity') => { '>=', $start },
[
# citizen
{
$self->me('affiliation_id') =>
{ -ident => 'station.affiliation_id' }
},
# station affiliation does not require visa
{ -not_bool => 'affiliation.requires_visa' },
# valid visa on station
{
'station.affiliation_id' =>
{ -ident => 'visas.affiliation_id' },
'visas.expires' => { '>=', $now },
}
],
'hotel_room_occupied.hotel_room_id' => undef,
$self->me('current_ship_id') => undef,
},
{
join => [
'character_user',
'hotel_room_occupied',
'visas',
{
station_area => {
station => 'affiliation'
}
}
],
}
);
}This is much more complex than we’d like so…
DBIC to the rescue
Fortunately DBIC supports the concept of “chaining” which allows us to create a number of small, understandable and maintainable queries that can be chained together to create more complex queries. Its documentation has this example:
$schema->resultset('Book')
->good
->cheap
->recentEach of ‘good’, ‘cheap’ and ‘recent’ are implemented as individual methods known as ‘predefined queries’ and we can test them individually and re-use them elsewhere. Reduced code complexity and reusability all in one: success!
The Good
Our complex query can be handled in the same way and we construct it from small predefined queries…
A player’s character might be travelling from one station to another. We want only characters who have arrived at their destination:
sub arrived ($self) {
my $now = $schema->format_datetime(
$self->context->now
);
return $self->search(
{ $self->me('arrival') => { '<=', $now } },
);
}… and who have been active in the game recently:
sub active ($self) {
my $start = $schema->format_datetime(
$self->context->now
- config(qw/time player_active_duration/)
);
return $self->search(
{
$self->me('last_activity') => { '>=', $start }
},
);
}… and they must have a valid visa for their current location (this one is a little more complex):
sub with_valid_visa ($self) {
my $now = $schema->format_datetime(
$self->context->now
);
return $self->search(
[
# citizen
{
$self->me('affiliation_id') =>
{ -ident => 'station.affiliation_id' }
},
# station affiliation does not require visa
{ -not_bool => 'affiliation.requires_visa' },
# valid visa on station
{
'station.affiliation_id' =>
{ -ident => 'visas.affiliation_id' },
'visas.expires' => { '>=', $now },
}
],
{
join => [
'visas',
{
station_area => {
station => 'affiliation'
}
}
],
},
);
}… also remembering that characters who are in a hotel room cannot be seen:
sub outside_hotel_room ($self) {
return $self->search(
{
'hotel_room_occupied.hotel_room_id' => undef,
},
{
join => 'hotel_room_occupied',
}
);
}… and we cannot see anyone onboard their own ship:
sub not_onboard_ship ($self) {
return $self->search(
{
$self->me('current_ship_id') => undef
}
);
}Since we need all of those queries to be chained together to create the
‘visible’ query we create this new method:
sub visible ($self) {
return
$self->arrived->active->with_valid_visa
->outside_hotel_room->not_onboard_ship;
}… which would result in an SQL query along the lines of:
SELECT me.* FROM character me
LEFT JOIN visa visas ON visas.character_id = me.character_id
JOIN station_area station_area
ON station_area.station_area_id = me.station_area_id
JOIN station station ON station.station_id = station_area.station_id
JOIN affiliation affiliation
ON affiliation.affiliation_id = station.affiliation_id
LEFT JOIN hotel_room hotel_room_occupied
ON hotel_room_occupied.occupied_id = me.character_id
WHERE ( NOT affiliation.requires_visa
OR me.affiliation_id = station.affiliation_id
OR ( station.affiliation_id = visas.affiliation_id
AND visas.expires >= ?
)
)
AND me.last_activity >= ?
AND me.arrival <= ?
AND me.available
AND current_ship_id IS NULL
AND hotel_room_occupied.hotel_room_id IS NULLNow we can narrow down our predefined ‘visible’ search to those characters in the same station area as the current character:
my $visible_characters = $schema->resultset('Character')
->visible->search(
{
$self->me('station_area_id')
=> $character->station_area_id
}
);Using small predefined queries that we can chain together allows us to develop faster and write testable and maintainable code. We also find bugs more quickly and can fix them more easily without risk of breakage.
The above code is, of course, subject to change.
Originally published at taustation.space on August 17, 2017.
