Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

Re: RFC: Databases made easy (and hard database tasks made possible)

by Your Mother (Bishop)
on Mar 20, 2011 at 07:09 UTC ( #894284=note: print w/replies, xml ) Need Help??

in reply to RFC: Databases made easy

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/ 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/ 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.

(Update: converted Pod links to list.)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://894284]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (4)
As of 2018-05-23 19:20 GMT
Find Nodes?
    Voting Booth?