# Album Plugin: captions/db/digikam
# Author      : Kevin Barter
# Description : Reads an SQLite database to provide the caption, and optionally the tags
#             : The database is created with the program Digikam
#             : This plugin has only been tested with the database created by version 0.8.1
#             : of Digikam.  It should work with all versions of Digikam that create an SQLite 3 
#             : database, as long as the schema remains the same.

# Use         : Copy the top level digikam album with all of its subdirectories to a directory
#             : on your webserver.  Also copy the digikam3.db file to where the script can
#             : read it.  It can be in the same directory as your digikam photo album, but it 
#             : does not have to be.
#             : Here is what I used as command line:
#             : album -plugin captions/db/digikam 
#             : -digikam:db='/mnt/photos/digikam3.db' /var/www/localhost/htdocs/photos/

# Required    : Two additional perl modules are required for this plugin to function.
#             : DBI - the generic database interface
#             : DBD::SQLite - the database driver for SQLite 3

#             : I do not have much experience with perl, and am not familiar with what is or
#             : isn't proper naming conventions, practices, etc.  Any pointers/guidance on
#             : improving the code is appreciated.

# License     : This code is licensed under the terms of the LGPL.  If a copy of the license
#             : is not available with this source, it can be viewed at
#             : http://www.gnu.org/licenses/lgpl.html

# For info    : album -plugin_info captions/db/digikam
# For usage   : album -plugin_usage captions/db/digikam

use strict;

# My webhost does not have DBD:SQLite install, so I have installed it locally
# If you have the same problem, change to where you have installed the db driver
# use lib qw(/path/to/web/root/local/local/lib/perl/5.6.1);
my $DBI = album::attempt_require('DBI');
my $DBD_SQLITE = album::attempt_require('DBD::SQLite');

# Standard module, should be in all base PERL installations
use File::Basename;

sub start_plugin {
	my ( $opt, $plugin, $path ) = @_;

	my $ret = {
		author      => 'Kevin Barter',
		href        => 'http://digikam.TheBarters.com/',
		version     => '1.0',
		description => "Get the image caption from the Digikam description.

Options:
    -digikam:db         REQUIRED Location of the digikam3.db file.  If this
                        file is at /home/user/digikam3.db, then use
                        -digikam:db /home/user/digikam3.db

    -digikam:usetags    OPTIONAL Value can be X or nothing.  If set to X,
                        the caption will contain the description of the
                        photo, as well as all of the tags for the photo
",
	};

	unless ($DBI) {
		my $err = "DBI is required and not installed!\n";
		print STDERR "\n[Plugin: $plugin] $err\n";
		$ret->{description} .= "\n$err";
		return $ret;
	}

	unless ($DBD_SQLITE) {
		my $err = "DBD::SQLite is required and not installed!\n";
		print STDERR "\n[Plugin: $plugin] $err\n";
		$ret->{description} .= "\n$err";
		return $ret;
	}

	# Setup my hooks
	# Get photo caption from digikam
	album::hook( $opt, 'modify_caption', \&modify_caption );
	album::add_option( 1, 'db', $album::OPTION_STR,
		usage => "Location of the digikam database (with path)" );

	album::add_option( 1, 'usetags', $album::OPTION_BOOL,
		usage => "Show the tags for a photo as part of the caption" );

	# Get album caption from digikam
	album::hook( $opt, 'modify_dir_caption', \&modify_dir_caption );

	$ret;
}

sub modify_caption {
	my ( $opt, $data, $hookname, $dir, $pic, $cap ) = @_;

	my ( $caption, $image_id, $tag_name, $albumroot, $database );

	# Check for the required options

	album::fatal( $opt,
"Need to specify the full path to the digikam database (eg. /media/photos/digikam3.db) with -digikam:db"
	  )
	  unless album::option($opt,'db');

	# TODO Does this need to be done?  Perhaps only do it if there is a new caption?
	album::new_html( $opt, $data, $pic );

	$database = album::option($opt,'db');

#	Get the top directory (where the Digikam Album is stored)
	$albumroot = dirname($database);

#	Remove the part of the path from directory to make it relative to where the
#	Digikam Album is located
	$dir =~ s/($albumroot)//;

	( $caption, $image_id ) =
	  get_image_description( $dir, $pic, $database );

	if ( album::option($opt,'usetags') == 'X' ) {
		my @tags = get_image_tags( $image_id, $database );

		my $tags = join( "<BR/>", @tags );
		if ($caption) {
			$caption = $caption . "<BR/>" . $tags;
		}
		else {
			$caption = $tags;
		}
	}

	return $caption;
}

sub modify_dir_caption {
	my ( $opt, $data, $hookname, $dir, $pic, $cap ) = @_;

	my $albumroot = album::option($opt,'topdir');
	$dir =~ s/($albumroot)//;

	my $database = album::option($opt,'db');

	my $caption = get_album_caption( $dir, $database );

	if ($caption) {
		return $caption;
	}
	else {
		return $cap;
	}
}

my $dbh;
sub get_album_caption {
	my ( $dir, $database ) = @_;

	my ($caption);

	# Open the database
	unless ($dbh) { $dbh =
	  DBI->connect( "dbi:SQLite:$database", "", "",
		{ RaiseError => 1, AutoCommit => 1 } ) };

	my $sth = $dbh->prepare(q{select caption from Albums where url = ? });

	$sth->execute($dir);

	$sth->bind_columns( \$caption );

	while ( $sth->fetch() ) {
	}

	#$sth->finish();
	#$dbh->disconnect();

	return ($caption);
}

sub get_image_description {
	my ( $dir, $pic, $database ) = @_;

	my ( $caption, $image_id, $query );

	# Open the database
	unless ($dbh) { $dbh =
	  DBI->connect( "dbi:SQLite:$database", "", "",
		{ RaiseError => 1, AutoCommit => 1 } ) };

	$query = q{
		SELECT Images.caption, Images.id
		FROM Albums, Images 
		WHERE albums.url   = ?
		AND   Images.dirid = Albums.id 
		AND   Images.name  = ?
	  };

	my $sth = $dbh->prepare($query);

	$sth->execute( $dir, $pic );

	$sth->bind_columns( \$caption, \$image_id );

	while ( $sth->fetch() ) {
	}

	#$sth->finish();
	#$dbh->disconnect();

	return ( $caption, $image_id );
}

sub get_image_tags {
	my ( $image_id, $database ) = @_;

	my ( $tag_name, @tags );

	# Open the database
	unless ($dbh) { $dbh =
	  DBI->connect( "dbi:SQLite:$database", "", "",
		{ RaiseError => 1, AutoCommit => 1 } ) };

	# Get the tags for the image
	my $sth = $dbh->prepare(
		q{
		SELECT Tags.name 
		FROM ImageTags, Tags, Images 
		WHERE Images.id = ?
		AND   Images.id = ImageTags.imageid 
		AND   Tags.id   = ImageTags.tagid
	  }
	);

	$sth->execute($image_id);

	$sth->bind_columns( \$tag_name );

	while ( $sth->fetch() ) {
		push( @tags, $tag_name );
	}

	$sth->finish();
	#$dbh->disconnect();

	return @tags;
}

# Plugins always end with:
1;