#! /usr/bin/perl

# Copyright (C) 2005  Nick Urbanik <nicku@nicku.org>
# $Id: limit-time-on-games-db.pl,v 1.4 2005/06/21 06:34:57 nicku Exp nicku $

# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.

# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.

use warnings;
use strict;

use Proc::ProcessTable;
use POSIX;
use DBI;
use Carp;

# Autoflush for log files:
$| = 1;

# forever
#         if time is midnight
#                 reset times for each group
#         foreach process
#                 foreach group
#                        if process matches user for the group
#                                foreach process name in the group
#                                        if process matches name
#                                                update time for the group
#                                                if group time limit exceeded
#                                                        terminate process
#         sleep sleeptime

# See recipe 17.17, page 706, Perl Cookbook.

use constant DIR => "/var/log/games";
use constant CONFIG_FILE => "/etc/games.rc";
use constant LOGFILE => DIR . "/games.log";
use constant TIMES_FILE => DIR . "/games.times";
# We kill the games by sending them a hangup signal:
use constant KILLSIG => "INT";

# The following three constants are used as keys to access parts of each
# piece of data in %config.  Use constants to help detect mistypings.
use constant LIMIT => "limit";
use constant USERS => "users";
use constant PROCS => "procs";

BEGIN{
    require "$ENV{HOME}/.gamesrc";
}

# unit is seconds:
use constant SAMPLING_INTERVAL => 30;
# If within SAMPLING_INTERVAL of NEARLY_UP before kill process, tell Linus.
use constant NEARLY_UP => 300;

our %config;
our $dbh;

sub logit(@) {
    my @msg = @_;
    #my $timestamp = localtime;
    my $timestamp = POSIX::strftime "%y/%m/%d %X", localtime;
    chomp @msg;
    print LOG $timestamp, ": ", @_, "\n";
}

sub dielog(@) {
    logit @_;
    exit 1;
}

dielog "CODE CANNOT COPE WITH SAMPLING_INTERVAL = @{[SAMPLING_INTERVAL]} > 59"
    if SAMPLING_INTERVAL > 59;

# The format of the configuration file is:
# groupname1 user limit_seconds processname1 processname2 ... processnamen
# groupname2 user limit_seconds processname1 processname2 ... processnamen
# ...

# Note that "group" in this program refers to something totally unrelated
# to a POSIX process group.  We just mean a set of programs that we place
# one particular time limit on.

# sub read_old_config() {
#     open CFG, '<', CONFIG_FILE
#         or dielog "Cannot open config file @{[CONFIG_FILE]}: $!";
#     while ( <CFG> ) {
#         chomp;
#         # Comments allowed.  Blank lines skipped.
#         s/#.*$//;
#         next if /^\s*$/;
#         logit "read from @{[CONFIG_FILE]}: $_";
#         my ( $group, $user, $seconds, @procs ) =  split;
#         $seconds =~ /^\d+$/
#             or dielog "Bad time limit in @{[CONFIG_FILE]}: '$_'\n";
#         $config{$group} = {
#                            user => $user,
#                            procs => [ @procs ],
#                            seconds => $seconds
#                           };
#         $playing_times{$group} = 0;
#         logit "stored: group=$group, user=$user, procs=@procs, ",
#             "seconds=$seconds" if DEBUG;
#     }
#     close CFG or dielog "Cannot close config file @{[CONFIG_FILE]}: $!";
# }

# procs groupname_1 processname_1 processname_2 ... processname_n
# users groupname_1 username_1 username_2 ... username_m
# limit groupname_1 seconds
# procs groupname_2 processname_1 processname_2 ... processname_n
# users groupname_2 username_1 username_2 ... username_m
# limit groupname_2 seconds
# ...

sub read_config() {
    open CFG, '<', CONFIG_FILE
        or dielog "Cannot open config file @{[CONFIG_FILE]}: $!";
    while ( <CFG> ) {
        chomp;
        # Comments allowed.  Blank lines skipped.
        s/#.*$//;
        next if /^\s*$/;
        logit "read from @{[CONFIG_FILE]}: $_";
        my ( $line_type, $group, @data ) =  split;
        if ( $line_type eq LIMIT ) {
            $config{$group}{$line_type} = shift @data;
        } else {
            $config{$group}{$line_type} = [ @data ];
        }
    }
    close CFG or dielog "Cannot close config file @{[CONFIG_FILE]}: $!";
}

sub check_config() {
    foreach my $group ( sort keys %config ) {
        foreach my $line_type ( LIMIT, USERS, PROCS ) {
            dielog "No '$line_type' for group $group in configuration"
                unless exists $config{$group}{$line_type}
                    and defined $config{$group}{$line_type};
        }
        foreach my $line_type ( sort keys %{$config{$group}} ) {
            if ( $line_type eq LIMIT ) {
                $config{$group}{$line_type} =~ /^\d+$/
                    or dielog "Bad time limit '$config{$group}{$line_type}' ",
                        "in configuration\n";
            } elsif ( $line_type eq USERS ) {
                dielog "No users for group $group"
                    unless @{$config{$group}{$line_type}};
                foreach my $user ( @{$config{$group}{$line_type}} ) {
                    # See ~/RPM/BUILD/shadow-4.0.3/libmisc/chkname.c,
                    # https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=157577
                    dielog "Bad user name $user in group $group"
                        unless $user =~
                        /^[a-zA-Z0-9_.][a-zA-Z0-9_.-]{0,30}[a-zA-Z0-9_.$-]?$/;
                }
            } elsif ( $line_type eq PROCS ) {
                dielog "No process names for group $group"
                    unless @{$config{$group}{$line_type}};
                foreach my $proc ( @{$config{$group}{$line_type}} ) {
                    dielog "Bad process name $proc in group $group"
                        unless length $proc;
                }
            }
        }
    }
}

sub show_config(\%) {
    my ( $config ) = @_;
    my $config_string = "";
    foreach my $group ( sort keys %$config ) {
        foreach my $line_type ( sort keys %{$config{$group}} ) {
            $config_string .= "$line_type $group ";
            if ( $line_type eq LIMIT ) {
                $config_string .= "$config->{$group}{$line_type}\n";
            } else {
                $config_string .=
                    join( " ", @{$config->{$group}{$line_type}} ) . "\n";
            }
        }
    }
    return $config_string;
}

sub read_config_from_db($) {
    my ( $dbh ) = @_;
    my $statement = "
    SELECT groupname, limit_seconds
        FROM limits
    ";
    my $sth = $dbh->prepare( $statement );
    $sth->execute or die "Unable to execute $statement: $!";
    while ( my ( $group, $seconds ) = $sth->fetchrow_array ) {
        $config{$group}{LIMIT()} = $seconds;
    }
    $sth->finish;

    foreach my $group ( keys %config ) {
        $statement = "
        SELECT process_name
            FROM processes
            WHERE groupname = '$group'
        ";
        $sth = $dbh->prepare( $statement );
        $sth->execute or die "Unable to execute $statement: $!";
        $config{$group}{PROCS()} = [ () ];
        while ( my ( $procname ) = $sth->fetchrow_array ) {
            push @{$config{$group}{PROCS()}}, $procname;
        }
        $statement = "
        SELECT username
            FROM users
            WHERE groupname = '$group'
        ";
        $sth = $dbh->prepare( $statement );
        $sth->execute or die "Unable to execute $statement: $!";
        $config{$group}{USERS()} = [ () ];
        while ( my ( $user ) = $sth->fetchrow_array ) {
            push @{$config{$group}{USERS()}}, $user;
        }
        $sth->finish;
    }
}

sub today() {
    my ( $mday, $mon, $year ) = ( localtime )[ 3, 4, 5 ];
    return sprintf "%4d-%02d-%02d", $year + 1900, $mon + 1, $mday;
}

sub timestamp_is_today($) {
    my ( $timestamp ) = @_;
    logit "\$timestamp = '$timestamp'\n" if DEBUG;
    return unless $timestamp;
    my ( $date ) = split / /, $timestamp, 1;
    return $date eq today;
}

# update time for the process =
#         select playing_time for the process
#         add sampling interval to this time
#         write new record
#         commit

# $killit has the string value "true" or "false".
sub update_time_for_process($$$$) {
    my ( $dbh, $group, $proc_name, $killit ) = @_;
    dielog "'$killit' should be 'true' or 'false'"
        unless $killit eq "true" or $killit eq "false";
    my $statement = "
    SELECT l.processname_id, l.proc_playing_time, l.terminated, l.timestamp
        FROM logging l INNER JOIN processes p
            ON (l.processname_id = p.processname_id)
        WHERE groupname = '$group' AND process_name = '$proc_name'
        ORDER BY timestamp DESC LIMIT 1
    ";
    #        AND timestamp > 'today'
    my $sth = $dbh->prepare( $statement );
    $sth->execute or die "Unable to execute $statement: $!";
    my ( $proc_id, $playtime, $terminated, $timestamp ) = $sth->fetchrow_array;
    if ( ! $timestamp ) {
        # Then this processname_id has never been logged
        $statement = "
        SELECT processname_id
        FROM processes
        WHERE process_name = '$proc_name' AND groupname = '$group'
        ";
        my $sth = $dbh->prepare( $statement );
        $sth->execute or die "Unable to execute $statement: $!";
        ( $proc_id ) = $sth->fetchrow_array;
        dielog "Cannot find processname_id for proc $proc_name ",
            "and group $group" unless defined $proc_id and $proc_id =~ /^\d+$/;
        $playtime = 0;
        $timestamp = "";
    }
    $playtime = 0 if timestamp_is_today $timestamp;
    $playtime += SAMPLING_INTERVAL;
    $statement = "
    INSERT INTO logging
    ( processname_id, proc_playing_time, terminated, timestamp )
    VALUES ( $proc_id, $playtime, $killit, 'now' );
    ";
    $dbh->do( $statement ) or dielog "unable to execute $statement: $!";
    $dbh->commit;
}

# group time =
#         SELECT p.process_name, playing_time, timestamp
#         FROM logging AS l, processes as p
#         WHERE groupname = '$group' AND timestamp > $last_midnight

# sub read_times_orig() {
#     my $filetime = ( stat TIMES_FILE )[9];
#     my $update = 1;
#     if ( $filetime ) {
#         my ( $fday, $fmon, $fyear ) = ( localtime $filetime )[3, 4, 5];
#         my ( $day, $mon, $year ) = ( localtime )[3, 4, 5];
#         $update = 0
#             unless $day == $fday and $mon  == $fmon and $year == $fyear;
#         logit "$day == $fday and $mon  == $fmon and $year == $fyear"
#             if DEBUG > 2;
#     } else {
#         logit "@{[TIMES_FILE]} does not exist" if DEBUG > 1;
#         $update = 0;
#     }
#     if ( $update ) {
#         logit "Reading @{[TIMES_FILE]}" if DEBUG > 2;
#         open TIMES, "<", TIMES_FILE
#             or dielog "unable to open @{[TIMES_FILE]} for reading: $!";
#         while ( <TIMES> ) {
#             chomp;
#             my ( $group, $time_so_far ) = split;
#             $playing_times{$group} = $time_so_far;
#             logit "Read $group $time_so_far from ",
#                 "@{[TIMES_FILE]}" if DEBUG;
#         }
#         close TIMES or dielog "Unable to close @{[TIMES_FILE]}: $!";
#     } else {
#         logit "resetting playing times" if DEBUG > 2;
#         foreach my $group ( keys %config ) {
#             $playing_times{$group} = 0;
#         }
#     }
# }

# sub reset_times_orig() {
#     open TIMES, ">", TIMES_FILE
#         or dielog "unable to open @{[TIMES_FILE]} for writing";
#     foreach my $group ( keys %config ) {
#         logit "$group time = $playing_times{$group}" if $playing_times{$group};
#         $playing_times{$group} = 0;
#         print TIMES "$group 0\n";
#     }
#     close TIMES or dielog "Unable to close @{[TIMES_FILE]}: $!";
# }

sub reset_times($) {
    my ( $dbh ) = @_;
    my %reset;
    my $statement = "
    SELECT groupname, group_playing_time, date
        FROM times
    ";
    my $sth = $dbh->prepare( $statement );
    $sth->execute or dielog "Unable to execute $statement: $!";
    while ( my ( $group, $time, $date ) = $sth->fetchrow_array ) {
        if ( $date ne today ) {
            $reset{$group} = 1;
        }
    }
    foreach my $group ( keys %reset ) {
        $statement = "
        UPDATE times
        SET date = 'today', group_playing_time = 0
        WHERE groupname = '$group'
        ";
        $dbh->do( $statement ) or dielog "Unable to execute $statement";
    }
}

# sub format_timeleft($) {
#     my $timeleft = shift;
#     $timeleft = 0 if $timeleft < 0;
#     my $mins = $timeleft / 60;
#     my $sec = $timeleft % 60;
#     return sprintf "%02d:%02d", $mins, $sec;
# }

# sub get_timeleft_message($@) {
#     my ( $dbh, @groups ) = @_;
#     my $text = "Time Left for Games:";

#     foreach my $group ( @groups ) {
#         my $statement = "
#         SELECT date, limit_seconds, group_playing_time
#         FROM times NATURAL JOIN limits
#         WHERE groupname = '$group'
#         ";
#         my $sth = $dbh->prepare( $statement );
#         $sth->execute or die "Unable to execute $statement";
#         while ( my ( $date, $limit, $playtime ) = $sth->fetchrow_array ) {
#             my $timeleft = $limit;
#             $timeleft = $limit - $playtime if $date eq today;
#             $text .= "\n$group: " . format_timeleft $timeleft;
#         }
#     }
#     logit $text;
#     return $text;
# }

# use Tk;

# sub cb_func($) {
#     my $button = shift;
#     $$button->configure( -text => get_timeleft_message $dbh, keys %config );
# }

# sub report_nearly_timesup($$$) {
#     my ( $timeleft, $group );
#     ( $dbh, $timeleft, $group ) = @_;
#     $timeleft = format_timeleft $timeleft;
#     my $pid = fork;
#     dielog "Cannot fork: $!" unless defined $pid;
#     unless ( $pid ) {
#         # I am the child:
#         my $main = MainWindow->new;
#         my $message;
#         $message = $main->Message( -text => "Only $timeleft left for $group",
#                                    -format => "center"
#                                  );
#         $message->pack;
#         MainLoop;
#         exit 0;
#     }
# }

sub update_group_time($$) {
    my ( $dbh, $group ) = @_;
    my $statement = "
    SELECT group_playing_time, date
    FROM times
    WHERE groupname = '$group'
    ";
    my $sth = $dbh->prepare( $statement );
    $sth->execute or die "Unable to execute $statement";
    my ( $playtime, $date ) = $sth->fetchrow_array;
    logit "found playing time = $playtime, date = $date for group $group\n";
    $playtime = 0 if $date ne today;
    $playtime += SAMPLING_INTERVAL;
    $statement = "
    UPDATE times
        SET group_playing_time = $playtime, date = 'today'
        WHERE groupname = '$group'
    ";
    $dbh->do( $statement ) or dielog "unable to execute $statement";
    logit "have executed $statement" if DEBUG;
    #my $timeleft = $config{$group}{LIMIT()} - $playtime;
    #report_nearly_timesup $timeleft, $group
    #    if abs( $timeleft - NEARLY_UP ) < SAMPLING_INTERVAL;
    return $playtime > $config{$group}{LIMIT()} ? "true" : "false";
}

# sub update_times_orig() {
#     open TIMES, ">", TIMES_FILE
#         or dielog "unable to open @{[TIMES_FILE]} for writing";
#     foreach my $group ( keys %config ) {
#         print TIMES "$group $playing_times{$group}\n";
#     }
#     close TIMES or dielog "Unable to close @{[TIMES_FILE]}: $!";
# }

# increment the group time, see if time to kill
# Find the last record for this process in this group today
# Create a new record with an updated time
sub update_times($$$) {
    my ( $dbh, $group, $proc_name ) = @_;
    my $kill_it_now = update_group_time $dbh, $group;
    update_time_for_process $dbh, $group, $proc_name, $kill_it_now;
    return $kill_it_now eq "true" ? 1 : undef;
}

sub daemonise() {
    # chroot DIR or dielog "Cannot chroot to @{[DIR]}: $!";

    # Fork once, parent exits:
    my $pid = fork;
    exit if $pid;
    dielog "Cannot fork: $!" unless defined $pid;

    close STDIN or dielog "unable to close STDIN: $!";
    close STDOUT or dielog "unable to close STDIN: $!";
    open STDERR, ">>:unix", LOGFILE
        or dielog "Cannot reopen STDERR to @{[LOGFILE]}: $!";

    POSIX::setsid() or dielog "Cannot start a new session: $!";

}


-d DIR or mkdir DIR, 0750 or die "unable to create directory @{[DIR]}: $!";
open LOG, ">>:unix", LOGFILE or die "Unable to open logfile @{[LOGFILE]}: $!";

logit "Starting $0";

use Sys::Hostname;
our $dbhost = hostname eq DBHOST ? "localhost" : DBHOST;

daemonise;

our $time_to_die;

sub signal_handler() {
    $time_to_die = 1;
}

$SIG{TERM} = \&signal_handler;
# Ignore these signals:
foreach my $sig ( qw/INT HUP QUIT PIPE/ ) {
    $SIG{$sig} = 'IGNORE';
}

$dbh = DBI->connect( "dbi:Pg:dbname=@{[DBNAME]};host=$dbhost", USER, PW,
                       {AutoCommit => 0} )
    or die "Unable to connect to $dbhost: $!";

$dbh->{HandleError} = sub { confess(shift) };

read_config_from_db $dbh;
logit show_config %config;
#print show_config %config;

# Try to avoid counting time spent using a text editor such as gedit, emacs
# or vim as contributing to the time spent playing the game.
until ( $time_to_die ) {
    my ( $sec, $min, $hour ) = localtime;
    if ( $min == 0 and $hour == 0
         and abs( $sec - SAMPLING_INTERVAL ) < SAMPLING_INTERVAL ) {
        logit "resetting times; it is midnight";
        reset_times $dbh;
    }
    my $proc_table = new Proc::ProcessTable( 'cache_ttys' => 1 );
 PROCESS: foreach my $process ( @{$proc_table->table} ) {
        logit "examining  process ", $process->cmndline, " with uid ",
            $process->uid, " where getpwuid \$process->uid is ",
                scalar getpwuid( $process->uid ) if DEBUG > 2;
    GROUP: foreach my $group ( keys %config ) {
            logit "processing group $group" if DEBUG > 1;
        USER: foreach my $user ( @{$config{$group}{USERS()}} ) {
                if ( getpwuid $process->uid eq $user ) {
                    logit "checking process ", $process->cmndline,
                        " for user $user; all $user\'s processes ",
                            "should be listed" if DEBUG > 1;
                PROC: foreach my $proc_name ( @{$config{$group}{PROCS()}} ) {
                        logit "Looking for process $proc_name" if DEBUG > 2;
                        if ( $process->cmndline =~ /$proc_name/
                             and $process->cmndline !~ /gedit|emacs|vim/
                           ) {
                            logit "found process ", $process->cmndline,
                                " in group $group for $user ",
                                  "while looking for $proc_name" if DEBUG;
                            my $kill = update_times $dbh, $group, $proc_name;
                            if ( $kill ) {
                                logit "time is up for group $group.  Killing ",
                                    $process->cmndline;
                                kill KILLSIG, $process->pid
                                    or logit "Unable to kill ",
                                        $process->cmndline,
                                            " with signal @{[KILLSIG]}: $!";
                            }
                        }
                    }
                }
            }
        }
    }
    logit "sleeping for @{[SAMPLING_INTERVAL]} seconds...";
    sleep SAMPLING_INTERVAL;
    read_config_from_db $dbh;
}
logit "Terminating ", $$;

$dbh->commit;

$dbh->disconnect;

close LOG or die "Unable to close logfile @{[LOGFILE]}: $!";

__END__

Aim: implement software that limits time Linus plays games to any
given limit.

Configurable: number of groups of programs, each with its own limit
Uses a configuration file.

Works across all computers in home network

Efficient

Secure

Efficient:

Runs as a daemon

do sudo yum -y install perl-Proc-ProcessTable

For security and efficiency: use Net::SSH::Perl?
Yes.  To install it:
1. Download pari from http://pari.math.u-bordeaux.fr/download.html
2. Download Math::Pari using CPAN.
3. cd ~/tmp
4. unpack both source tarballs
5. cd pari-*
6. ./Configure
7. cd ../Math-*
8. perl Makefile.PL
9. make
10. make test
11. sudo make install
12. sudo perl -MCPAN -e shell
13. install Net::SSH::Perl

Could use cfengine? Possibly could implement a subset of the
requirements, but I think it will be just as hard as writing the
program.

security:

run as root (bad), or run as nickl/nicku, have a small suid script to
kill the games, given a key, runnable only by group nicku/nickl.  Not
secure to run as Linus!

Okay, how to record global running time for each group of programs for
the day?

assume single file only on nicku.org:

groupname1 user processname1 processname2 ... processnamen limit_seconds
groupname2 user processname1 processname2 ... processnamen limit_seconds
...

run server on boot as root on all machines Linus could play games on.
Server reads config file from nicku.org via ssh into data structure on
startup.

forever
        if time is midnight
                reset times for each group
        foreach process
                foreach group
                       if process matches user for the group
                               foreach process name in the group
                                       if process matches name
                                               update time for the group
                                               if group time limit exceeded
                                                       terminate process
        sleep sleeptime


Note that

reset times for each group

update time

and

time has met or exceeded limit

are made difficult due to the global nature of the data.

Would be nice to maintain a global log of the use of each program.

Start simple; write it so it just works on one machine, then extend to
multiple machines and handle the file locking and such.

ONE BIG PROBLEM WITH USING SSH:

The distributed programs need access to the private key through the
ssh agent.  This assumes that Linus has typed it in when the machine
was booted, which is not possible.

nicku.org can ssh to the other machines, but they cannot ssh to
nicku.org.

What can we do?

o Use DHCP log to find when other machines have booted
  (DHCPDISCOVER/DHCPOFFER)
o Transfer config to other machine (or do it manually, or with cfengine)
o determine if other machine is up with ping
o transfer group counts of seconds to the counts on other machines if
  they are not zero on nicku.org (do only once since other machine has
  booted)
o transfer group counts from other machines to counts on nicku.org

This last is hard to do accurately and efficiently.

forever
    if DHCPOFFER
        transfer config file from a central location on nicku.org
        if nicku.org times file is modified today
            foreach group
                if time on nicku.org > time on other machine for that group
                    update other machine's count for that group from nicku.org
                    update counts on nicku.org with highest counts
        sleep 30
        update counts on nicku.org with higher counts on other machine

No, that's all too complicated.
Better to use a network database (or even LDAP).
LDAP is not designed for this, since most operations will be write not read.
Use postgresql and DBI?

Data:

group user limit_seconds processname1 processname2 processname3 ...

Logging:

Aim to be able to find:

what time is spent playing what game,

what days the timeout was reached, what the timeout was

Average daily time spent playing each group

Average daily time spent playing any game from any group

Logging Data:

timestamp processname playing_time terminated

Normalising the data:

First Normal Form:

database has no repeating columns

Second Normal Form:

eliminate columns not dependent on part of a compound primary key

Third Normal Form:

eliminate columns that are not dependent on primary key.



group user limit_seconds processname1 processname2 processname3 ...

tablename: limits
primary key: group
-> group user limit_seconds

tablename: processes
primary key: processname_id
-> processname_id process_name group

timestamp processname playing_time terminated

tablename: logging
primary key: processname_id

-> timestamp processname_id playing_time terminated

DROP INDEX logging_timestamp_index;
DROP TABLE logging;
DROP TABLE times;
DROP TABLE processes;
DROP TABLE users;
DROP TABLE limits;

-- When times (group_playing_time) >= limits (limit_seconds) then we start
-- terminating any processes that belong to this group.
CREATE TABLE limits (
    groupname TEXT PRIMARY KEY,
    limit_seconds INTEGER NOT NULL
);

-- Want to be able to have one list of processess for a number of
-- different user accounts that the person may log in as.
-- The one person may have different user accounts on different machines,
-- or may have more than one account (such as linus and linusl)
CREATE TABLE users (
    username TEXT NOT NULL,
    groupname TEXT REFERENCES limits (groupname)
);

-- Note that process_name does not uniquely identify a row in this table.
-- The processname_id does.
-- However, for one group, there should be only one instance of
-- a process_name.
-- The same process_name may have many processname_id entries, one and only
-- one for each group.
CREATE TABLE processes (
    processname_id SERIAL PRIMARY KEY,
    process_name TEXT NOT NULL,
    groupname TEXT REFERENCES limits (groupname)
);

-- This table is not strictly necessary, since we could add the
-- logging (proc_playing_time) values for each process in the group.
-- However, it should be much simpler (and faster) to look this up after
-- the limit is reached.
-- date is the date on which this group_playing_time has been reached.
CREATE TABLE times (
    groupname TEXT REFERENCES limits (groupname),
    group_playing_time INTEGER NOT NULL,
    date DATE NOT NULL
);

-- We log a process for a group.
-- A group can have one instance of a process.
-- Different groups may have the same process.
-- proc_playing_time is the number of seconds that the users in the group
--     have been running this particular process today.

-- Typical actions:
-- find last record for this processname_id
-- Add SAMPLING_INTERVAL to proc_playing_time and insert a new record
CREATE TABLE logging (
    processname_id INTEGER REFERENCES processes (processname_id) NOT NULL,
    proc_playing_time INTEGER NOT NULL,
    terminated BOOLEAN NOT NULL,
    timestamp TIMESTAMP NOT NULL
);

CREATE INDEX logging_timestamp_index ON logging (timestamp);
GRANT SELECT ON times, limits, users TO PUBLIC;


Could have different limits for weekends, or specific days, e.g.,
holidays, but why put that logic into this application?  Why not let
another simple program run by cron handle that?

Note: using a database _could_ also remove some complexity from the
program, in that SQL statements could replace the need to reset times.
We do not necessarily need to maintain a time for each group; instead,
we could select the times for each process since midnight and return
the sum.  However, this would also complicate things somewhat.

read configuration from database into data structure
for each group
    reset times if last date is not today

forever
    if time is midnight
        reset times for each group
    foreach process
        foreach group
            foreach user in group that exists on this system
                if process matches user
                     foreach process name in the group
                         if process matches name
                             update time for the process
                             if group time limit exceeded
                                 terminate process
        sleep sleeptime

update time for the process =
        select time for the process
        add sampling interval to this time
        write back to the record
        commit


group time =
        SELECT p.process_name, playing_time, timestamp
        FROM logging AS l, processes as p
        WHERE groupname = '$group' AND timestamp > $last_midnight