Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

Comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

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

In reply to Re: RFC: Databases made easy (and hard database tasks made possible) by Your Mother
in thread RFC: Databases made easy by GrandFather

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and all is quiet...

    How do I use this? | Other CB clients
    Other Users?
    Others contemplating the Monastery: (4)
    As of 2018-05-26 13:01 GMT
    Find Nodes?
      Voting Booth?