Bringing perlish DB handling to the unwashed masses

DBIx::Perlish

Anton Berezin

tobez@tobez.org

Database handling

Database handling:

 

Database handling

Database handling:

 

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.

Getting rid of SQL

Replace DSL SQL with *another* DSL.

...which looks like Perl.

DBIx::Perlish

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

Get all users:

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

Declarative - filters

Get all users with id over 100:

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

Imperative - for loop

Contrast that with "normal" Perl:

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

Declarative - grep

Perl also has filters:

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:

Query result

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

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_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?

$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;
};
Also used for sub-queries.

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;
    };
};
This form must return a single value.

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

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
Aggregates: avg(), count(), max(), min()

Compound queries


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

Joins


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

Joins

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?