#!/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

##</code><code>##

>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:SQLite:peeps