#!/usr/local/bin/perl
#
# Conduit to fetch today's list of DVDs from OpenDB, convert them to a
# text list, and replace the "Andrew's DVD List" Memo entry.
#
# $Id: opendb2memo,v 1.1 2004/04/24 17:58:03 arensb Exp $

use strict;
use POSIX;
use DBI;
use Palm::Memo;
use ColdSync;

=head1 NAME

opendb2memo - Create a Palm memo with items from OpenDB

=cut

# Full POD at the end.

StartConduit("fetch");

# XXX - Ought to have a header that specifies the pathname to a
# protected file that contains all of the private information, such as
# database password.
# Get list of titles from the OpenDB database
our $itemtype = $HEADERS{"ItemType"} || "DVD";
our $username = $HEADERS{"Username"} || (getpwuid($>))[0];
our @titles = &get_opendb_titles(
		"database"	=> $HEADERS{"Database"} || "opendb",
		"hostname"	=> $HEADERS{"Hostname"} || "localhost",
		"port"		=> $HEADERS{"Port"},
		"username"	=> $username,
		"password"	=> $HEADERS{"Password"},
		"itemtype"	=> $itemtype,
		"owner"		=> $HEADERS{"Owner"} ||
				   $username,
	);

#### Update the record in the PDB
our $memo_id = $HEADERS{"MemoPattern"} ||
	"^$HEADERS{'PDA-Username'}'s $itemtype List";
	# /$memo_id/s is a pattern that uniquely identifies the memo
	# we want to update. It will be evaluated with the "s" flag.
our $memo_fmt = $HEADERS{"MemoTitle"} ||
	"$HEADERS{'PDA-Username'}'s $itemtype List, %b %d, %Y";
	# $memo_fmt is a string from which the memo title will be
	# generated (so $memo_id ought to match it).
	# This string will be passed to strftime(), so it may contain
	# %-escapes for the date and time.
our $memo_title = strftime($memo_fmt, localtime);

our $record;		# The record we're going to update
our $oldtext;		# Old text of record
our $newtext;		# New text of record, for comparison

# Find the record we're looking for
foreach my $r (@{$PDB->{"records"}})
{
	if ($r->{"data"} =~ /$memo_id/s)
	{
		$record = $r;
		last;
	}
}

if (!defined($record))
{
	# No such record yet. Create one.
	$record = $PDB->append_Record;
}

# See if the record needs to be updated
$oldtext = $record->{"data"};
$oldtext =~ s/^.*\n\n//;		# Remove title and blank line
$newtext = join("\n", @titles);

if ($oldtext ne $newtext)
{
	# Update the record
	my ($mday, $mon, $year) = (localtime)[4,3,5];

	# The new memo text consists of the title, followed by a blank
	# line, followed by the list of titles, in alphabetical order
	$record->{"data"} = $memo_title .
		"\n\n" .
		$newtext;
	$record->{"attributes"}{"Dirty"} = 1;
}

EndConduit;

# get_opendb_titles(arg => value, ...)
# Fetch the list of titles from the OpenDB database. The arguments are
# a list of argument => value pairs. Recognized arguments include:
#
#	database	The name of the OpenDB database. Defaults to
#			"opendb".
#	hostname	Host on which the database resides. Defaults to
#			"localhost".
#	port		MySQL port. Defaults to the MySQL default.
#	username	MySQL user name. Defaults to 'undef'.
#	password	MySQL password. Defaults to 'undef'.
#	itemtype	OpenDB item type. Defaults to "DVD".
#	owner		OpenDB item owner. Defaults to the same as the
#			"username" argument.
#
# The "owner" argument must be defined.

# XXX - Ought to get the artist/author, for non-DVD items
sub get_opendb_titles
{
	my %args = @_;
	my $database = $args{"database"} || "opendb";
	my $hostname = $args{"hostname"} || "localhost";
	my $port     = $args{"port"};
	my $username = $args{"username"};
	my $password = $args{"password"};
	my $itemtype = $args{"itemtype"} || "DVD";
	my $owner    = $args{"owner"} || $username;
	my $dbh;
	my $sth;
	my $query;
	my @retval   = ();

	die "No owner" if !defined($owner);

	# Sanitize values
	$database =~ s/\W//ig;
	$hostname =~ s/[^-\w\.]//ig;
	$port =~ s/\D//g;
	$username =~ s/[^-\w]//ig;
	$password =~ s/;//g;

	# Connect to the database
	$dbh = DBI->connect("DBI:mysql:" .
			    "database=$database;" .
			    "host=$hostname" .
			    (defined($port) ? ";port=$port" : ""),
			    $username, $password);
	die $DBI::errstr if !defined($dbh);

	# Get list of titles from the database
	$sth = $dbh->prepare(<<EOT)
		SELECT	item.title as title
		FROM	item, item_instance
		WHERE	item_instance.item_id = item.id
		  AND	item.s_item_type = ?
		  AND	item_instance.owner_id = ?
		  AND	item.parent_id is NULL
		  AND	item_instance.s_status_type = 'A'
		ORDER BY title
EOT
	or die $DBI::errstr;

	$sth->execute($itemtype, $owner)
		or die $DBI::errstr;

	# Read the title list
	while (my ($title) = $sth->fetchrow_array)
	{
		# If the title is of the form "Matrix, The", put the
		# title back in "natural" order.
		if ($title =~ /^(.*),\s([A-Z][a-z]*)$/)
		{
			$title = "$2 $1";
		}
		push @retval, $title;
	}

	# Okay, we're done
	$dbh->disconnect;

	return @retval;
}

__END__

=head1 SYNOPSIS

Add something like the following to your F<.coldsyncrc> file:

	conduit fetch {
		type memo/DATA;
		path: "<...>/opendb2memo";
	    arguments:
		Database: opendb;
		Hostname: mysql-server.dom.ain;
		Port: 12345;
		Username: joebob;
		Password: "pA$$w0rd";
		ItemType: DVD;
		MemoPattern: "^Joe's DVD List";
		MemoTitle: "Joe's DVD List, %b %d, %Y";
	}

=head1 DESCRIPTION

The author finds it useful to have a Memo record listing the DVDs that
he owns. Since it's a plain old Memo entry, it can be beamed to
friends.

C<opendb2memo> is a ColdSync conduit for creating and updating this
memo from an OpenDB database. This is a Fetch conduit, so any changes
made on the Palm will be lost the next time you sync.

If no existing record is found on the Palm, C<opendb2memo> creates one.

=head1 ARGUMENTS

=over 4

=item Database

The name of OpenDB's MySQL database. Defaults to "opendb".

=item Hostname

The hostname on which to talk to MySQL. Defaults to "localhost".

=item Port

The port on which to talk to MySQL. Defaults to the MySQL default
port.

=item Username
=item Password

The MySQL user name and password.

B<Warning:> The password is stored as plaintext. The best thing to do
is to have a guest MySQL user who is allowed to read the OpenDB
database, but not modify it. If that is not possible, make sure that
your F<.coldsyncrc> file is not readable by others.

=item ItemType

The OpenDB item type to list. Defaults to "DVD". Other values might
include "CD" and "GAME" (if you have the appropriate patch installed).

=item MemoPattern

C<MemoPattern> is a Perl regular expression. C<opendb2memo> will
update the first record whose text matches this regular expression. If
omitted, defaults to "^<name>'s <item-type> List", where <name> is
your full name on the Palm, and <item-type> is the value of the
C<ItemType> argument. Example: C<^Joe Bob Smith's DVD List>.

B<Note:> C<MemoTitle> (see below) must match C<MemoPattern>.

=item MemoTitle

The title of the memo. The text of the memo will consist of this
title, a blank line, and the list of item titles, one per line.

C<MemoTitle> may contain C<strftime()> escapes, so that you can add a
timestamp to the memo (this can be useful to let your friends know
that they have an out-of-date list of your DVDs).

If omitted, defaults to "<name>'s <item-type> List, %b %d, %Y", where
<name> is your full name on the Palm, and <item-type> is the value of
the C<ItemType> argument.
Example: C<Joe Bob Smith's DVD List, %b %d, %Y>.

B<Note:> C<MemoTitle> must match C<MemoPattern> (see above).

=back

=head1 SEE ALSO

OpenDB: http://opendb.sourceforge.net/

=head1 BUGS

Security: MySQL username and password are listed in the file in the
clear.

Doesn't list the artist name, for CDs, or author, for books.

Although C<MemoPattern> can include newlines, C<MemoTitle> must fit on
one line.

=cut