Databases and Perl

Databases and Perl

Dave Cross

Databases and Perl

  • Data is important

  • Databases are important

  • A large proportion of Perl programs need to write to and/or read from databases

DBI.pm

  • The basis for all modern Perl database access

  • You should be using DBI (or something based on DBI)

How DBI Works

  • Program uses DBI.pm

  • Create a connection to a particular type of database

  • DBD module gets loaded

  • DBD translates from DBI API to database specific calls

How DBI Works (Diagram)

DBI
DBD
Database API
Database

How DBI Works (Code)

  •   use DBI;
      my $dbh = DBI->connect("dbi:mysql:$some_stuff",
                         $user, $pass);

  • In theory you can port a program to another database

  • Just change the connection line

Selecting Data

  • Prepare the SQL statement

  •   my $sth = $dbh->prepare('select name, genre
                           from   artist');
  •   my $sth = $dbh->prepare("select title,
                           from   song
                           where  artist = '$id'");

  • Check return values (syntax errors)

Selecting Data

  • Execute the statement

  •   $sth->execute

  • Still need to check for errors

Selecting Data

  • Fetch the returned data

  •   while (my @row = $sth->fetchrow_array){
    print "@row\n";
  }

  • Fields are returned in the same order as they are defined in the query

Selecting Data

  • Other fetch methods are available:

    • fetchrow_arrayref

    • fetchrow_hashref (keys are column names)

    • fetchall_arrayref

    • fetch (alias for fetchrow_arrayref)

  • Many more added each week

Some Caveats

  • If you're using a fetch method that returns an array

    • Never use "select *"

    • For (hopefully) obvious reasons

  • If you're using a fetch method that returns a hash

    • Ensure all your columns have (unique) names

    • For (hopefully) obvious reasons

Inserting, Updating & Deleting Data

  • Statements that don't return data can be executed the same way

  •   my $sql = "update table1 set col1 = '$val'
             where id_col = $id"; 
      my $sth  = $dbh->prepare($sql);
      $sth->execute;

  • But there's a shortcut

  •   $rows = $dbh->do($sql);

Binding Data

  •   while (<FILE>) {
    chomp;
    my @data = split;
        my $sql = "insert tab values ($data[0],
                                  $data[1],
                                  $data[2]");
        $dbh->do($sql);
  }

  • Recompiles the SQL every time

  • Very inefficient

Binding Data

  • Prepare statement once, use many times

  •   my $sql = "insert tab values (?, ?, ?)";
      my $sth = $dbh->prepare($sql);
      while (<FILE>) {
    my @data = split;
        bind_param(1, $data[0]);
    bind_param(2, $data[1]);
    bind_param(3, $data[2]);
        $sth->execute;
  }

  • Bonus - binding handles quoting for you

Binding Data

  • Even easier - extra parameters to execute

  •   my $sql = "insert tab
             values (?, ?, ?)";
      my $sth = $dbh->prepare($sql);
      while (<FILE>) {
    chomp;
        my @data = split;
        $sth->execute(@data);
  }

Binding by Name

  • Having unnamed placeholders can get confusing

  •   my $sql = 'insert into complex_table
             values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
                     ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
                     ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';

  • Good chance of getting the variables in the wrong order

  • By the way - there's a basic maintainability error in that SQL

Binding by Name

  • Bind by name instead

  •   my $sql = 'insert into complex_table
                    (id, code, name, addr, email, url
                     # etc, etc
                    )
             values (:id, :code, :name, :addr, :email, :url,
                      # etc, etc
                    );
      my $sth = $sql->prepare($sql);
      $sth->bind_param(':id', $id);
  $sth->bind_param(':code', $code);
  # etc
      $sth->execute;

Even Easier Binding by Name

  • Store your data in a hash

  •   my %complex_data = (id   => 42,
                      code => 'H2G2',
                      # etc
                      );
      # and later...
  foreach my $col (keys %complex_data) {
    $sth->bind_param(":$col", $complex_data{$col};
  }

Downside of Binding by Name

  • Most DBDs don't support it

  • Which is a bit of a bugger

  • Oracle does

  • So does PostgreSQL (tho' the docs discourage its use)

  • Check your DBD documentation

  • Email your friendly neighbourhood DBD author

Some Tips

  • Make your life as easy as possible

  • Don't hard-code connection data

    • Config file, command line options, environment variables

  • Send all of your data access through one function

  • Store SQL queries externally and reference them by name

  • (Use named parameters if you can)

Sample Code

  my $dbh;
  sub run_sql {
    my ($sql_statement, %args) = @_;
    my $sql = get_sql($sql_statement);
    $dbh = get_dbh() unless $dbh;
    my $sth = $dbh->prepare($sql);
    foreach my $col (keys %args) {
      $sth->bind_param(":$col", $args{$col});
    }
    return $sth->execute;
  }

Who Hates SQL?

  • SQL is tedious to write

  • Most SQL isn't hard

  • Most SQL is really predictable

Predictable SQL

  • Select the id and name from this table

  • Select all the details of this row

  • Select something about related tables

  • Update this row with these values

  • Insert a new record with these values

  • Delete this record

Introducing Class::DBI

  • Class::DBI allows you to write code like this

  •   use Music::Artist;
      foreach my $art (Music::Artist->retrieve_all) {
    print $art->id, ' ', $art->name, "\n";
  }

  • Or like this

  •   my $art = Music::Artist->retrieve($id);
  if ($art) {
    foreach my $song ($art->songs) {
      print $song->title, "\n";
    }
  }

More Class::DBI Examples

  • Update an object

  •   $art->name('Archduke Ferdinand');
  $foo->update;

  • Insert an object

  •   $art = Music::Artist->insert(\%new_art);

  • How about this

  •   $art = Music::Artist->find_or_create(\%new_art);

More Class::DBI::Examples

  • Deleting an object

  •   $celine_dion->delete;

  • Deleting many objects

  •   Music::Artist->search(genre => 'Rap')->delete_all;

The Man Behind the Curtain

  • Class::DBI is an Object Relational Mapper

  • It maps a relational database table onto a class

  • It maps a relational database row onto an object

  • You tell it about your database tables

  • It creates the SQL

    • For the most common cases

A Class::DBI Class

  package Music::DBI;
  use base 'Class::DBI';
  # Don't hard-code these!!
  Music::DBI->connection('dbi:mysql:musicdb', 'username',
                         'password');
  package Music::Artist;
  use base 'Music::DBI';
  Music::Artist->table('artist');
  Music::Artist->columns(All => qw/id name genre/);
  Music::Artist->has_many(songs => 'Music::Song');
  package Music::Song;
  use base 'Music::DBI';
  Music::Song->table('song');
  Music::Artist->columns(All => qw/id title artist/);
  Music::Artist->has_a(artist => 'Music::Artist');

Other Class::DBI Features

  • Inflate attributes to objects

  • Triggers before/after events

  • Constraints

  • Define your own SQL

  • Class::DBI::FromCGI & Class::DBI::AsForm

Improving on Class::DBI

  • Class::DBI makes life a lot easier

  • You very rarely need to type actual SQL

  • Laziness is a virtue

  • Therefore more laziness is better

  • Let's take another look at that class definition

A Class::DBI Class

  package Music::DBI;
  use base 'Class::DBI';
  # Don't hard-code these!!
  Music::DBI->connection('dbi:mysql:musicdb', 'username',
                         'password');
  package Music::Artist;
  use base 'Music::DBI';
  Music::Artist->table('artist');
  Music::Artist->columns(All => qw/id name genre/);
  Music::Artist->has_many(songs => 'Music::Song');
  package Music::Song;
  use base 'Music::DBI';
  Music::Song->table('song');
  Music::Artist->columns(All => qw/id title artist/);
  Music::Artist->has_a(artist => 'Music::Artist');

More Laziness Needed

  • Bear in mind this is a very simple system

  • You'll usually need a lot more tables with a lot more columns

  • We specify the relations twice (once in each direction)

  • We specify all of the column names

  • We specify the table name

  • We have already entered all this information once

Database Metadata

  CREATE TABLE artist (
    id     INTEGER PRIMARY KEY AUTO_INCREMENT,
    name   VARCHAR(50),
    genre  VARCHAR(5)
  );
  CREATE TABLE song (
    id     INTEGER PRIMARY KEY AUTO_INCREMENT,
    title  VARCHAR(50),
    artist INTEGER FOREIGN KEY REFERENCES artist(id)
  );

Don't Repeat Yourself

  •   CREATE TABLE song (
    id     INTEGER PRIMARY KEY AUTO_INCREMENT,
    title  VARCHAR(50),
    artist INTEGER FOREIGN KEY REFERENCES artist(id)
  );
  •   package Music::Song;
  use base 'Music::DBI';
  Music::Song->table('song');
  Music::Artist->columns(All => qw/id title artist/);
  Music::Artist->has_a(artist => 'Music::Artist');

  • Repetition is bad

  • Don't do it (unless you absolutely have to)

Class::DBI::Loader

  • Most of the information we need to build the class is already in the database metadata

  • Let's let the computer do all that tedious work

  • We'll just step in if it needs our help

  • Class::DBI::Loader does this for us

A Class::DBI::Loader Class

  •   package Music;
      use Class::DBI::Loader;
      Class::DBI::Loader->new(dsn           => 'dbi:mysql:musicdb',
                          user          => 'username',
                          password      => 'password',
                          relationships => 1,
                          namespace     => 'Music');

  • Scans the database and creates objects as appropriate

  • An object for each table (name based on table name and namespace)

  • Attributes for each column

  • Relationships for each foreign key

Advantages of Class::DBI::Loader

  • Makes it very easy to get simple database systems up quickly

  • No need to repeat database metadata

  • No need to write simple SQL

  • Power to override default behaviour when required

Other ORM Systems Are Available

  • Class::Persist

  • DBIx::Class

  • Tangram

  • SPOPS

  • Alzabo

Further Information

Databases and Perl

  • Any Questions?