K0brik

#!/usr/bin/perl -w

# Channel cutter script to run with cron when it's cutting time
# (c) dion.rasmussen@gmail.com

use warnings;
use strict;
use DBI;

my $chan_length = 100;

# Connect the DB
#my $dsn = "DBI:Pg:dbname=$ENV{USER}";
my $dsn = "DBI:Pg:dbname=www-data";

my $dbh = DBI->connect($dsn);

# Select the channel lengths to be cutted
my $sth = $dbh->prepare(q/SELECT * FROM (select cut_id,chid,count(*) - ? AS to_cut FROM ccast GROUP BY cut_id,chid) foo WHERE to_cut > 0/);

# Select at which time offset
my $offset_sth = $dbh->prepare(q/SELECT max(time) AS offset FROM (SELECT time FROM ccast WHERE chid =? AND cut_id = ? ORDER BY time LIMIT ?) foo/);

# Delete < time offset
my $delete_sth = $dbh->prepare(q/DELETE FROM ccast WHERE chid =? AND cut_id = ? AND time < ?/);

$sth->execute($chan_length);

while (my $row = $sth->fetchrow_hashref) {

        $offset_sth->execute($row->{chid}, $row->{cut_id}, $row->{to_cut});

        while (my $ss_row = $offset_sth->fetchrow_hashref) {

                print "$row->{chid}, $row->{cut_id}: $ss_row->{offset}\n"; # debug test-point

                # $delete_sth->execute($row->{chid}, $row->{cut_id}, $ss_row->{offset});

        }
}