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);
}
?>