Thanks *much* for doing this. This sort of beginning ramp in is what’s so hard to find (in quality examples anyway) when starting. You have have probably saved many strangers many tortured hours.
I’m going to ride on your coat-tails a bit and mimic your example with advanced tools. To show how facile and non-threatening, I hope, these basics can be in DBIx::Class; which can come off as unapproachable, unfriendly, and pointless when DB stuff—on the surface!—is so easy in Perl already.
A liberty I took was adding a primary key (id) to the example table. References:
I apologize for the dearth of explanation. The code is self-contained, should be somewhat self-explanatory, and will run as is if the module dependencies are met.
#!/usr/bin/env perl
use warnings;
use strict;
use DBIx::Class;
BEGIN {
# This would really be lib/My/Schema/People.pm
package My::Schema::People;
use strict;
use warnings;
use parent "DBIx::Class::Core";
__PACKAGE__->table("People");
__PACKAGE__->add_columns(
"id",
{ data_type => "integer", is_auto_increment => 1, is_nullable
+ => 0 },
"name",
{ data_type => "varchar", is_nullable => 1, size => 128 },
"age",
{ data_type => "integer", is_nullable => 1 },
);
__PACKAGE__->set_primary_key("id");
1;
# This would really be lib/My/Schema.pm
package My::Schema;
use strict;
use warnings;
use parent "DBIx::Class::Schema";
__PACKAGE__->load_classes( "People" );
1;
}
# use My::Schema;
my $db = "Test.sqlite";
# my $db = ":memory:"; is also good for testing.
my $schema = My::Schema->connect("dbi:SQLite:$db",
undef, undef,
{
RaiseError => 1,
AutoCommit => 1,
sqlite_unicode => 1,
});
unlink($db) if -e $db; # Make runs idempotent.
# Deploying the database. This is connection specific so it could
# deploy to MySQL, Pg, etc, instead of SQLite.
unless ( -e $db )
{
eval { $schema->deploy; 1 } == 1
or die "Couldn't deploy: $@";
}
my %people = (
Fred => 23,
Jane => 22,
Boyd => 27,
Tania => 28,
Julie => 27,
Kyle => 21,
);
my $created = eval {
# All or nothing. Transaction is managed by scope.
my $guard = $schema->txn_scope_guard;
for my $peep ( keys %people )
{
$schema->resultset("People")
->create({ name => $peep, age => $people{$peep} });
}
$guard->commit;
return $schema->resultset("People")->count;
};
die $@ if $@;
print "Added $created rows to the People table\n";
my $rs = $schema->resultset("People")
->search({ name => { LIKE => "%e" } });
# Chainable resultsets!
$rs = $rs->search({ age => { "<" => 25 } },
{ order_by => "age" });
for my $peep ( $rs->all )
{
printf("id:%d %-6s %3d\n",
$peep->id,
$peep->name,
$peep->age,);
}
__DATA__
Added 6 rows to the People table
id:4 Kyle 21
id:1 Jane 22
At first blush that schema set-up seems like a lot of work. Except for localizing namespace/load issues to put it all in one test script for ease of testing it was all automatic. Here’s how to do it without writing any code at all–
>echo "CREATE TABLE People (id INTEGER PRIMARY KEY, name VARCHAR(128),
+ age INTEGER);" | sqlite3 peeps
>dbicdump -o debug=1 -o dump_directory=./dbic-test My::Schema dbi:SQLi
+te:peeps
There is a learning curve but once up it, this sort of thing becomes magically easy.