I have a script that I wrote earlier this year that logs into my Vonage
account and downloads my call history and inserts it into a local
database. When I upgraded my machine from Sarge to Etch, the script
broke. The error messages I see are like so:
$ vonage-calls.pl -v
Connecting to database...
Getting login screen...
Submitting form...
Day too big - 38605 > 24855
Sec too small - 38605 < 74752
Sec too big - 38605 > 11647
Sleeping...
Getting call details page...
Adding call 11856261279 to the database...
ERROR: syntax error at or near "$2" at character 119
(Repeat these last two lines for each individual call)
I am certain that the Etch upgrade is what caused the problem, since the
script is run nightly by cron and the last night for which there is call
data in my database is the night before I upgraded to Etch.
I'd like to know if someone might be able to help point me in the right
direction. I am relatively certain that there is a bug in either the
HTTP::Date module or the Time::Local module. The actual "Day too big"
error message comes from Time::Local and following the chain of
dependencies of the modules accessed from my script, HTTP::Date is the
only one that calls on Time::Local. I used Postgres, and the database
can be created like so:
---------8<----->8---------
-- Database: calldb
-- DROP DATABASE calldb;
CREATE DATABASE calldb
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default;
-- Table: calls
-- DROP TABLE calls;
CREATE TABLE calls
(
trans_id int8 NOT NULL,
dtg timestamptz NOT NULL,
caller_name varchar(127) NOT NULL,
from_number varchar(127) NOT NULL,
to_number varchar(127) NOT NULL,
duration interval NOT NULL,
CONSTRAINT calls_pkey PRIMARY KEY (trans_id)
)
WITHOUT OIDS;
ALTER TABLE calls OWNER TO postgres;
GRANT ALL ON TABLE calls TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE calls TO callmgr;
---------8<----->8---------
This depends on the user accessing the calldb database to be called
callmgr. The Perl script itself is attached.
Regards,
-Roberto
--
Roberto C. Sánchez
http://people.connexer.com/~roberto
http://www.connexer.com
#!/usr/bin/perl -wT
# Retrieve the log of the last week's calls from Vonage and put them in a DB
# This script logs in to the Vonage website and download the log of calls
# for the last seven days. It then parses the calls and inserts each call
# into a database.
# Required Debian packages are:
# libwww-mechanize-perl
# libdbd-pg-perl
# History
# 05/12/2007 - Initial release
use strict;
use WWW::Mechanize;
use Getopt::Std;
use DBI;
###################################
# User Config Vars
my $vonage_user = 'user';
my $vonage_pass = 'pass';
my $db_user = 'user';
my $db_pass = 'pass';
my $db_name = 'calldb';
my $db_host = 'localhost';
# End Configuration
####################################
# Use -H to have the script download the *entire* call history
# Use -v to have verbose output
# Use -d to have all HTML dumped to a file after it is fetched.
our ($opt_H, $opt_v, $opt_d);
getopts('Hvd');
print "Connecting to database...\n" if $opt_v;
my $dbh = DBI->connect("dbi:Pg:dbname=$db_name", $db_user, $db_pass, {PrintError => 0})
or die $DBI::errstr;
my $sth = $dbh->prepare("INSERT INTO calls (trans_id, dtg, caller_name, from_number, to_number, duration) VALUES (?, TIMESTAMP WITH TIME ZONE ?, ?, ?, ?, ?)")
or die $dbh->errstr;
if ($opt_d) {
open (DEBUGFILE, ">>/tmp/vonage_debug")
or die "Unable to open debug output file.\n"
}
my $mech = WWW::Mechanize->new();
$mech->agent_alias('Linux Mozilla');
print "Getting login screen...\n" if $opt_v;
$mech->get('https://secure.vonage.com/vonage-web/public/login.htm');
die "Couldn't get to login form, got return code $mech->status\n"
if $mech->status != '200';
print "Submitting form...\n" if $opt_v;
$mech->submit_form(
form_name => 'logonForm',
fields => {
username => $vonage_user,
password => $vonage_pass,
}
);
die "Couldn't log in, got return code $mech->status\n"
if $mech->status != '200';
print "Sleeping...\n" if $opt_v;
sleep 2;
print "Getting call details page...\n" if $opt_v;
$mech->follow_link(text=>'Activity');
my $processed_call;
do {
undef $processed_call;
die "Couldn't fetch activity summary, got return code $mech->status\n"
if $mech->status != '200';
my $raw_html = $mech->content;
$raw_html =~ s/^\s+//gm;
print DEBUGFILE "$raw_html\n\n" if $opt_d;
my @raw_html = split /[\r\n]+/, $raw_html;
my $mode = 'searching';
my ($date, $time, $caller, $from, $to, $duration, $trans_id);
foreach (@raw_html) {
if ($mode eq 'searching') {
# Look for the start of the Received Calls table
$mode = 'received' if /Received Calls<\/td>/i;
} elsif ($mode eq 'received' or $mode eq 'placed') {
if (/[A-Z][a-z]{2} \d{2}, \d{4}/) {
$date = $&;
next;
}
if (/\d{2}:\d{2} [AP]M/) {
$time = $&;
next;
}
if (/icon_caller_id\.gif/i) {
if (/<b>([^<]*)<\/b>/i) {
$caller = $1;
next;
}
}
if (/<div class="phoneNumber">([^<]*)<\/div>/i) {
if (!$from) {
$from = $1;
} else {
$to = $1;
}
next;
}
if (/\d{2}:\d{2}:\d{2}/) {
$duration = $&;
next;
}
if (/<TD nowrap> (\d+) <\/TD>/i) {
$trans_id = $1;
# We now have all the info for a call, lets add it to the database.
print "Adding call $trans_id to the database...\n" if $opt_v;
$sth->execute($trans_id, $date . " " . $time, $caller, $from, $to, $duration);
if (($dbh->err) and !($dbh->errstr =~ /duplicate key/)) {
warn $dbh->errstr;
}
$processed_call = 'yes';
# Clear the variables for the next iteration.
$date = '';
$time = '';
$caller = '';
$from = '';
$to = '';
$duration = '';
$trans_id = '';
next;
}
last if /Icon Legend<\/td>/i;
$mode = 'placed' if /Placed Calls<\/td>/i;
next;
}
}
if ($processed_call) {
print "Getting next call details page...\n" if $opt_v;
$mech->follow_link(text=>'<<Previous 7 Days');
}
} while $opt_H and $processed_call;
$dbh->disconnect;
exit;
Attachment:
signature.asc
Description: Digital signature