DigiKam to PHP Photo Album - Denormalization

Denormoralizing the data streamlines the queries necessary to deliver the albums and photos on the web page. In this process, I insert the data into a MySql database then create "views" (actually, the denormalized data in tables) with the data that I'll be using on the web pages.

Insert into MySql

I first use a bash script to dump the sqllite data into MySql. This script is a bit of a hack, but it works well enough for my purposes at the moment. Get it here.

This script uses a script that I found on the internet to do most of the conversion from sqlite3 to mysql. The script is sqlite3_to_mysql.pl.

The approach taken by this script is a little excessive - it begins by dropping tables, then re-importing them from the "migrated" sqlite3 database.

#!/bin/bash

cd /myata/Photos_tools
echo "Dumping sqlite data"
sqlite3 ../digikam4.db .dump > digikam-dump.txt
echo "Converting sqlite data to mysql format"
cat digikam-dump.txt | ./sqlite3-to-mysql.py > into_mysql
echo "Dropping tables"
grep "CREATE TABLE" into_mysql |
	sed 's/^CREATE TABLE \(.*\)$/DROP TABLE IF EXISTS \1;/' |
	mysql --batch -u<userid> -p<password> digikam4
echo "Importing Data"
  ( grep -v hash_index into_mysql |
	  grep -v image_name_index |
	  grep -v uuid_index |
	  sed -e '{N; s/,\n.*UNIQUE.*);/);/;P;D }' |
	  sed '0,/TRIGGER/s/CREATE TRIGGER/\/*/1' ; echo "*/"
	) | mysql --batch -u<userid> -p<password> digikam4 

Create Views

Once I have the data in MySql, I use PHP to create the "views" that I need to deliver the pages in my PhotoGalleries. Get this script here: updateMysqlViews. This script uses my "album library" code to communicate with the database.

<?php
  include "../album_library.php";

	/**
	 * This SQL sniplit is used in a number of places, so it was easier to contain it centrally
	 * I generate views for both non-private and private images
	 */
function getPublicSubQuery( $privateFlag ) {
			if( $privateFlag == 1 ) {
			$sql = "
    (
      SELECT i.id
      FROM Images i
    )";
		} else {
			$sql = "
    (
      SELECT i.id
      FROM Images i
      INNER JOIN ImageTags it
        ON it.imageid = i.id
      INNER JOIN Tags t
        ON t.id = it.tagid
        AND t.name = 'Public'
    )";
		}

		return $sql;
	}

	function updateAlbumsView($db, $privateFlag) {
		$tableName = "AlbumsView";
		if( $privateFlag == 1 ) {
			$tableName .= "_private";
		}

		$sql = "CREATE TABLE {$tableName}_temp AS
		SELECT
			a.id as album_id, a.relativePath as album_path, a.caption as album_caption, a.collection, a.icon, a.date as album_date,
			i.id as image_id, i.name as image_name,
			icon_album.relativePath as image_path,
			ittl.comment as image_title,
			ic.comment as image_caption,
			ii.creationDate, i.modificationDate,
			md.make, md.model, md.lens, md.aperture, md.focalLength, md.exposureTime, md.sensitivity, md.meteringMode,
			pos.latitudeNumber, pos.longitudeNumber
		FROM Albums a
		INNER JOIN Images i
			ON i.id = a.icon
		INNER JOIN Albums icon_album
			ON icon_album.id = i.album
		LEFT OUTER JOIN ImageComments ittl
			ON ittl.imageid = i.id
			AND ittl.type = 3 
		LEFT OUTER JOIN ImageComments ic
			ON ic.imageid = i.id
			AND ic.type = 1
		LEFT OUTER JOIN ImageInformation ii
			ON ii.imageid = i.id
		LEFT OUTER JOIN ImageMetadata md
			ON md.imageid = i.id
		LEFT OUTER JOIN ImagePositions pos
			ON pos.imageid = i.id
		WHERE i.id IN " . getPublicSubQuery($privateFlag);

		$db->executeSql( $sql, null );

		$sql = "DROP TABLE {$tableName}";
		$db->executeSql( $sql, null );

		$sql = "RENAME TABLE {$tableName}_temp TO {$tableName}";
		$db->executeSql( $sql, null );
	}
	
	function updateImagesView($db, $privateFlag) {
		$tableName = "ImagesView";
		if( $privateFlag == 1 ) {
			$tableName .= "_private";
		}
		$sql = "CREATE TABLE {$tableName}_temp AS
		SELECT
			a.id as album_id, a.relativePath as album_path, a.caption as album_caption, a.collection, a.date as album_date,
			i.id as image_id, i.name as image_name,
			a.relativePath as image_path,
			ittl.comment as image_title,
			ic.comment as image_caption,
			ii.creationDate, i.modificationDate,
			md.make, md.model, md.lens, md.aperture, md.focalLength, md.exposureTime, md.sensitivity, md.meteringMode,
			pos.latitudeNumber, pos.longitudeNumber
		FROM Albums a
		INNER JOIN Images i
			ON i.album = a.id
		LEFT OUTER JOIN ImageComments ittl
			ON ittl.imageid = i.id
			AND ittl.type = 3 
		LEFT OUTER JOIN ImageComments ic
			ON ic.imageid = i.id
			AND ic.type = 1
		LEFT OUTER JOIN ImageInformation ii
			ON ii.imageid = i.id
		LEFT OUTER JOIN ImageMetadata md
			ON md.imageid = i.id
		LEFT OUTER JOIN ImagePositions pos
			ON pos.imageid = i.id
		WHERE upper(i.name) like '%JPG'
			AND i.id IN " . getPublicSubQuery($privateFlag);

		$db->executeSql($sql, null);

		$sql = "DROP TABLE {$tableName}";
		$db->executeSql($sql, null);

		$sql = "RENAME TABLE ${tableName}_temp TO ${tableName}";
		$db->executeSql($sql, null);

		$tableName = "ImageTagsView";
		if( $privateFlag == 1 ) {
			$tableName .= "_private";
		}
    $sql = "CREATE TABLE {$tableName}_temp AS
		SELECT i.album as album_id, i.id as image_id, t.id as tag_id, t.name as tag_name
		FROM Images i
		INNER JOIN ImageTags it
      ON it.imageid = i.id
    INNER JOIN Tags t
      ON t.id = it.tagid
      AND t.pid not in (
				SELECT id FROM Tags WHERE name IN ( '_Digikam_Internal_Tags_', 'Visibility' )
			)
		WHERE upper(i.name) like '%JPG'
			AND i.id IN " . getPublicSubQuery($privateFlag);

		$db->executeSql($sql, null);

		$sql = "DROP TABLE $tableName";
		$db->executeSql($sql, null);

		$sql = "RENAME TABLE {$tableName}_temp TO $tableName";
		$db->executeSql($sql, null);

	}
	
	$db = new DatabaseConnection();
	#create album and image views for both public and private views of the data
	for( $i = 0 ; $i <= 1 ; $i++ ) {
		updateAlbumsView($db, $i);
		updateImagesView($db, $i);
	}

?>