CREATE TABLE Session (
    SessID   INTEGER PRIMARY KEY AUTOINCREMENT,
    DoneDate TEXT,
    Topic    TEXT
  )

  CREATE TABLE Person (
    PersId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name   TEXT
  )

  CREATE TABLE Role (
    RoleId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name   TEXT
  )

  CREATE TABLE PersonRoles (
    PersID INTEGER,
    RoleID INTEGER,
    PRIMARY KEY(PersID, RoleID)
  )


  CREATE TABLE PersonSessions (
    SessID INTEGER,
    PersID INTEGER,
    PRIMARY KEY(SessID, PersID)
  )

##</code><code>##


sub getActiveSessions {
  my ($self, @ids) = @_;
  my $idPlaces = (join ', ', ('?') x @ids) || '';

  return $self->{'dbh'}->selectall_arrayref( <<SQL, {}, @ids );

  SELECT p.Name, r.Name, s.Topic, s.DoneDate
  FROM Person p
  JOIN PersonRoles pr ON pr.PersID = p.PersID
  JOIN Role r ON r.RoleID = pr.RoleID
  JOIN PersonSessions ps ON ps.PersID = p.PersID
  JOIN Session s ON s.SessID = ps.SessID
  WHERE p.PersID IN ($idPlaces)
  AND s.DoneDate = '2015-06-27'
SQL
}

say Dumper( $self->getActiveSessions(1..7) );


##</code><code>##

$VAR1 = [
          [
            'Doug',
            'Learner',
            'Fun with JSON',
            '2015-06-27'
          ],
          [
            'Bert',
            'Teacher',
            'Fun with Origami',
            '2015-06-27'
          ],
          [
            'Greg',
            'Learner',
            'Fun with Origami',
            '2015-06-27'
          ],
          [
            'Fred',
            'Learner',
            'Fun with Origami',
            '2015-06-27'
          ],
          [
            'Fred',
            'Learner',
            'Fun with JSON',
            '2015-06-27'
          ],
          [
            'Carl',
            'Teacher',
            'Fun with JSON',
            '2015-06-27'
          ]
        ];


##</code><code>##

use strict;
use warnings;
use DBI;
use feature qw/ say /;
use Data::Dumper;

my $dbh = DBI->connect('dbi:SQLite:monks.sqlite','','', {RaiseError => 1});

my $self = bless { dbh => $dbh };

$dbh->do(<<SQL);
  CREATE TABLE Session (
    SessID   INTEGER PRIMARY KEY AUTOINCREMENT,
    DoneDate TEXT,
    Topic    TEXT
  )
SQL

$dbh->do(<<SQL);
  CREATE TABLE Person (
    PersId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name   TEXT
  )
SQL

$dbh->do(<<SQL);
  CREATE TABLE Role (
    RoleId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name   TEXT
  )
SQL

$dbh->do(<<SQL); 
  CREATE TABLE PersonRoles (
    PersID INTEGER,
    RoleID INTEGER,
    PRIMARY KEY(PersID, RoleID)
  )
SQL

$dbh->do(<<SQL);
  CREATE TABLE PersonSessions (
    SessID INTEGER,
    PersID INTEGER,
    PRIMARY KEY(SessID, PersID)
  )
SQL

# make some roles
for ( qw/ Teacher Learner /) {
  $dbh->do( <<SQL, {}, $_ );
INSERT INTO Role (Name) VALUES (?) 
SQL
}

# make some sessions
my %sessions = ( 
  'Fun with Perl'    => '2015-06-20',
  'Fun with SQL'     => '2015-06-20',
  'Fun with JSON'    => '2015-06-27',
  'Fun with Origami' => '2015-06-27',
);

while (my ($topic, $done_date) = each %sessions) {
  $dbh->do( <<SQL, {}, $topic, $done_date );
INSERT INTO Session (Topic, DoneDate) VALUES (?,?)
SQL
}

# make some people
my %people = (
  'Adam' => { role => 1, sessions => [1,4] },
  'Bert' => { role => 1, sessions => [2] },
  'Carl' => { role => 1, sessions => [3] },
  'Doug' => { role => 2, sessions => [1,3] },
  'Eric' => { role => 2, sessions => [1,4] },
  'Fred' => { role => 2, sessions => [2,3] },
  'Greg' => { role => 2, sessions => [2,4] },
);

while ( my ($pers_name, $properties) = each %people ) {

  $dbh->do( <<SQL, {}, $pers_name );
    INSERT INTO Person (Name) VALUES (?)
SQL

  my $pers_id = $self->{dbh}->last_insert_id(undef, undef, undef, undef);

  $dbh->do( <<SQL, {}, $pers_id, $properties->{'role'} );
    INSERT INTO PersonRoles (PersID, RoleID) VALUES (?,?)
SQL

  for (@{ $properties->{'sessions'} }) {

    $dbh->do( <<SQL, {}, $_, $pers_id );
      INSERT INTO PersonSessions (SessID, PersID) VALUES (?,?)
SQL
  }
}

sub getActiveSessions {
  my ($self, @ids) = @_;
  my $idPlaces = (join ', ', ('?') x @ids) || '';

  return $self->{'dbh'}->selectall_arrayref( <<SQL, {}, @ids );

  SELECT p.Name, r.Name, s.Topic, s.DoneDate
  FROM Person p
  JOIN PersonRoles pr ON pr.PersID = p.PersID
  JOIN Role r ON r.RoleID = pr.RoleID
  JOIN PersonSessions ps ON ps.PersID = p.PersID
  JOIN Session s ON s.SessID = ps.SessID
  WHERE p.PersID IN ($idPlaces)
  AND s.DoneDate = '2015-06-27'
SQL
}


say Dumper( $self->getActiveSessions(1..7) );