Data is important
Databases are important
A large proportion of Perl programs need to write to and/or read from databases
The basis for all modern Perl database access
You should be using DBI (or something based on DBI)
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
| DBI |
|---|
| DBD |
| Database API |
| Database |
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
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)
Execute the statement
$sth->execute
Still need to check for errors
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
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
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
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);
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
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
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); }
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
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;
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};
}
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
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)
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; }
SQL is tedious to write
Most SQL isn't hard
Most SQL is really predictable
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
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";
}
}
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);
Deleting an object
$celine_dion->delete;
Deleting many objects
Music::Artist->search(genre => 'Rap')->delete_all;
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
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');
Inflate attributes to objects
Triggers before/after events
Constraints
Define your own SQL
Class::DBI::FromCGI & Class::DBI::AsForm
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
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');
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
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)
);
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)
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
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
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
Class::Persist
DBIx::Class
Tangram
SPOPS
Alzabo
Module documentation (CPAN)
Programming the Perl DBI - Tim Bunce
Advanced Perl Programming - Simon Cozens
Any Questions?