use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:SQLite:dbname=xr.SQLite');

$dbh->do(<<SQL);
CREATE TABLE Sessions (
    SessId INTEGER PRIMARY KEY AUTOINCREMENT,
    DoneDate TEXT,
    Topic TEXT
    )
SQL
$dbh->do(<<SQL);
CREATE TABLE SessPeople (
    SessId INTEGER,
    PersonId INTEGER,
    IsTeacher INTEGER
    )
SQL


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

$self->addSession(1, 2, 'Perl is fun',                   '2015-06-27');
$self->addSession(3, 4, 'SQL is fun',                    '2015-06-27');
$self->addSession(3, 5, 'SQL for fun and profit',        '2015-06-20');
$self->addSession(4, 1, 'PerlMonks is great',            '2015-06-20');
$self->addSession(2, 6, 'PerlMonks for fun and proffit', '2015-06-20');

my $active = $self->getActiveSessions(1, 2);

print "$_->[1], $_->[2]: '$_->[3]'\n" for @$active;


sub addSession {
    my ($self, $teacher, $learner, $topic) = @_;

    $self->{dbh}->do(<<SQL, {}, $topic);
INSERT INTO Sessions (Topic, DoneDate)
    VALUES (?, '')
SQL

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

    $self->{dbh}->do(<<SQL, {}, $sessId, $teacher);
INSERT INTO SessPeople (SessId, PersonId, IsTeacher)
    VALUES (?, ?, 1)
SQL
    $self->{dbh}->do(<<SQL, {}, $sessId, $learner);
INSERT INTO SessPeople (SessId, PersonId, IsTeacher)
    VALUES (?, ?, 0)
SQL
}


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

    return $self->{dbh}->selectall_arrayref(<<SQL, {}, @ids);
SELECT DISTINCT s.SessId, t.PersonId, l.PersonId, s.Topic FROM Sessions s
    JOIN SessPeople t ON t.SessId == s.SessId AND t.IsTeacher
    JOIN SessPeople l ON l.SessId == s.SessId AND NOT l.IsTeacher
    JOIN SessPeople p ON p.SessId == s.SessId
    WHERE p.PersonId IN ($idPlaces)
SQL
}