Bringing perlish DB handling to the unwashed masses

DBIx::Perlish

Anton Berezin

tobez@tobez.org

Database handling

• queries, and
• "everything else"

Database handling

• queries ------- DBI, and
• "everything else" -------- SQL

SQL

SELECT * FROM users,groups
  WHERE
    users.groups_id = groups.id AND
    users.id = 42;

SQL = DSL

Within Perl program, SQL is a Domain-Specific Language.

SQL = DSL

Within Perl program, SQL is a Domain-Specific Language.

A *LARGE* one.

Perl + SQL

Switching back and forth is difficult.

Perl + SQL

Disrupts program flow

my $r = $dbh->selectall_arrayref(
   "SELECT * FROM users, groups
    WHERE
      users.groups_id = groups.id AND
      users.id = ?",
   {Slice=>{}}, 42);

ORMs

Object-relational mappers.

• have their merits
• have their problems, too

Getting rid of SQL

Replace DSL SQL with *another* DSL.

...which looks like Perl.

DBIx::Perlish

• syntactically - valid Perl
• semantically - declarative DSL for DB

DBIx::Perlish

my $uid = 42;
my @r = db_fetch {
    my $u : users;

    $u->groups_id == groups->id;
    $u->id == $uid;
};

Referring to tables

my $uid = 42;
my @r = db_fetch {
    my $u : users;
    my $g : groups;

    $u->groups_id == $g->id;
    $u->id == $uid;
};

Referring to tables

my $uid = 42;
my @r = db_fetch {
    users->groups_id == groups->id;
    users->id == $uid;
};

Referring to columns

$object->method;


$table->column;

Declarative

my @r = db_fetch {
   my $u : users;
}

Declarative - filters

my @r = db_fetch {
   users->id > 100;
}

Imperative - for loop

my @r;
for my $u (@users) {
   push @r, $u if $u->{id} > 100;
}

Declarative - grep

my @r = grep { $_->{id} > 100 } @users;

Implicit "AND"

my $uid = 42;
my @r = db_fetch {
    my $u : users;

    $u->groups_id == groups->id;
    $u->id == $uid;
};

All the normal expressions work

my $uid = 40;
my @r = db_fetch {
    users->id == $uid + 2;
};

Concatenation

my @r = db_fetch {
    my $u : users;
    return $u->first_name . " " . $u->last_name;
};

Interpolation

my @r = db_fetch {
    my $u : users;
    return "$u->first_name $u->last_name";
};

Return

No return == "SELECT *"

Use return if you want control of what is returned

Query result

Context determines how many *rows* you want:

• scalar context - one row
• list context - all rows

Query result

"Return" determines how many *columns* you want:

• no return - all columns
• return with one value - one column
• return with a list of N values - N columns

Scalar context, single-value return

my $name = db_fetch { return user->name };
print "The name of some user is $name\n";

List context, single-value return

my @names = db_fetch { return user->name };
print "The names of all users are @names\n";

Scalar context, more than one column returned

my $u = db_fetch { return user->id, user->name };
print "The name of a user with id $u->{id} is $u->{name}\n";

Note the *hash reference*.

List context, more than one column returned

my @u = db_fetch { return user->id, user->name };
for my $u (@u) {
   print "$u->{id}:\t$u->{name}\n";
}

Note the *array of hash references*.

Naming problem

my @r = db_fetch {
    my $u : users;
    return $u->id, "$u->first_name $u->last_name";
}

What are the names of the columns?

Naming problem solution

my @r = db_fetch {
    my $u : users;
    return $u->id,
       full_name => "$u->first_name $u->last_name";
}

Beyond db_fetch

• db_update
• db_delete
• db_insert

db_update

db_update {
   my $u : users;
   $u->id == 42;  # filter

   $u->first_name = "Ford";
   $u->last_name  = "Prefect";
};

db_update

db_update {
   my $u : users;
   $u->id == 42;  # filter

   $u = {
      first_name => "Ford",
      last_name  => "Prefect",
      age        => $u->age + 1,
   };
};

db_delete

db_delete {
   users->id < 20;
};

db_insert

db_insert 'users', {
   id         => 42,
   first_name => "Ford",
   last_name  => "Prefect",
};

What DB handle?

• explicit: DBIx::Perlish::init($dbh)
• implicit; by magic

$dbh

use DBI;
use DBIx::Perlish;

my $dbh = DBI->connect(...);

# this works just fine
my @rows = db_fetch { ... };

Regular expressions

my @r = db_fetch {
   users->name =~ /^ford/i;
};

"comes from" notation

my @r = db_fetch {
   my $u : users;
   $u->id == 1 ||
   $u->id == 2 ||
   $u->id == 3;
};

Boooooring

"comes from" notation

my @r = db_fetch {
   users->id  <- [1,2,3];
};

"comes from" notation

my @ids = (1,2,3);
my @r = db_fetch {
   users->id  <- @ids;
};

Sub-queries

db_delete {
    my $u : users;
    db_fetch {
        $u->id == bad_users->users_id;
    };
};

Sub-queries with "comes from"

db_delete {
    my $u : users;
    $u->id  <-  db_fetch {
        return bad_users->users_id;
    };
};

Result limiting

last;

... LIMIT 1;

Result limiting

last unless 5..20;

... OFFSET 5 LIMIT 16;

Parse-time conditionals

my $type = "ICBM";
db_fetch {
   my $p : products;
   $p->type eq $type if $type;
};

SELECT * FROM products where type = 'ICBM';

Parse-time conditionals

my $type = "";
db_fetch {
   my $p : products;
   $p->type eq $type if $type;
};

SELECT * FROM products;

Labels

• distinct:
• limit: 42;
• offset: 35;
• order_by: $t->name, desc => $t->age;
• group_by: $t->name, $t->age;

Implicit GROUP BY

db_fetch {
   my $t : tab;
   return $t->name, $t->type, count($t->age);
};

SELECT name, type, COUNT(age) FROM tab GROUP BY name, type

Compound queries

db_fetch {
   {
      my $t1 : tab1;
      $t1->id == 42;
   } union {
      my $t2 : tab2;
      $t2->id == 666;
   }
};

Joins

db_fetch {
    my $x : x;
    my $y : y;
    join $y * $x  <=
      db_fetch { $y->id == $x->id }
};

Joins

• * inner join
• & inner join
• x inner join
• + full outer join
• | full outer join
• < left outer join
• > right outer join

DBD specific things

Some functionality is implemented differently for different DBD drivers.

Regular expressions; pseudo-functions. More in the future (LIMIT/OFFSET, aggregates).

Thank you.

Thank you!

Any questions?