From: Steinar H. Gunderson <sesse@debian.org>
Date: Tue, 19 Jun 2007 13:15:53 +0000 (+0200)
Subject: Make some sorely needed changes to the SQL schema, and update the code
X-Git-Url: https://git.sesse.net/?p=pr0n;a=commitdiff_plain;h=6be9a26e71fc85e69cbbdbc95fc50d3569050921;ds=sidebyside

Make some sorely needed changes to the SQL schema, and update the code
accordingly (I really hope I fixed everything; I've done some testing, and
it seems largely OK). The biggest change is that the event primary key is
now (vhost,event) and not just (event), which is rippling through all the
tables; this means the vhosts are finally truly independent. Also clean up
to remove some old length restrictions from the MySQL days.
---

diff --git a/perl/Sesse/pr0n/Common.pm b/perl/Sesse/pr0n/Common.pm
index c34aa65..6fb5536 100644
--- a/perl/Sesse/pr0n/Common.pm
+++ b/perl/Sesse/pr0n/Common.pm
@@ -34,7 +34,7 @@ BEGIN {
 		require Sesse::pr0n::Config_local;
 	};
 
-	$VERSION     = "v2.30";
+	$VERSION     = "v2.40";
 	@ISA         = qw(Exporter);
 	@EXPORT      = qw(&error &dberror);
 	%EXPORT_TAGS = qw();
@@ -220,7 +220,7 @@ sub update_image_info {
 
 		# update the last_picture cache as well (this should of course be done
 		# via a trigger, but this is less complicated :-) )
-		$dbh->do('UPDATE last_picture_cache SET last_picture=GREATEST(last_picture, ?) WHERE event=(SELECT event FROM images WHERE id=?)',
+		$dbh->do('UPDATE last_picture_cache SET last_picture=GREATEST(last_picture, ?) WHERE (vhost,event)=(SELECT vhost,event FROM images WHERE id=?)',
 			undef, $datetime, $id)
 			or die "Couldn't update last_picture in SQL: $!";
 	}
@@ -573,11 +573,11 @@ sub add_new_event {
 		return @errors;
 	}
 		
-	$dbh->do("INSERT INTO events (id,date,name,vhost) VALUES (?,?,?,?)",
+	$dbh->do("INSERT INTO events (event,date,name,vhost) VALUES (?,?,?,?)",
 		undef, $id, $date, $desc, $vhost)
 		or return ("Kunne ikke sette inn ny hendelse" . $dbh->errstr);
-	$dbh->do("INSERT INTO last_picture_cache (event,last_picture) VALUES (?,NULL)",
-		undef, $id)
+	$dbh->do("INSERT INTO last_picture_cache (vhost,event,last_picture) VALUES (?,?,NULL)",
+		undef, $vhost, $id)
 		or return ("Kunne ikke sette inn ny cache-rad" . $dbh->errstr);
 
 	return ();
diff --git a/perl/Sesse/pr0n/Image.pm b/perl/Sesse/pr0n/Image.pm
index e436428..d1743b4 100644
--- a/perl/Sesse/pr0n/Image.pm
+++ b/perl/Sesse/pr0n/Image.pm
@@ -46,8 +46,8 @@ sub handler {
 		# 	or error($r, "Could not find $event", 404, "File not found");
 	
 		# Look it up in the database
-		my $ref = $dbh->selectrow_hashref('SELECT id,width,height FROM images WHERE event=? AND filename=?',
-			undef, $event, $filename);
+		my $ref = $dbh->selectrow_hashref('SELECT id,width,height FROM images WHERE event=? AND vhost=? AND filename=?',
+			undef, $event, $r->get_server_name, $filename);
 		error($r, "Could not find $event/$filename", 404, "File not found") unless (defined($ref));
 
 		$id = $ref->{'id'};
diff --git a/perl/Sesse/pr0n/Index.pm b/perl/Sesse/pr0n/Index.pm
index e91d9c1..e331cc4 100644
--- a/perl/Sesse/pr0n/Index.pm
+++ b/perl/Sesse/pr0n/Index.pm
@@ -46,8 +46,8 @@ sub handler {
 	);
 	
 	# Any NEF files => default to processing
-	my $ref = $dbh->selectrow_hashref('SELECT * FROM images WHERE event=? AND LOWER(filename) LIKE \'%.nef\' LIMIT 1',
-		undef, $event)
+	my $ref = $dbh->selectrow_hashref('SELECT * FROM images WHERE event=? AND vhost=? AND LOWER(filename) LIKE \'%.nef\' LIMIT 1',
+		undef, $event, $r->get_server_name)
 		and $defsettings{'xres'} = $defsettings{'yres'} = undef;
 	
 	# Reduce the front page load when in overload mode.
@@ -82,7 +82,7 @@ sub handler {
 		$num = undef;
 	}
 
-	$ref = $dbh->selectrow_hashref('SELECT name,date,EXTRACT(EPOCH FROM last_update) AS last_update FROM events WHERE id=? AND vhost=?',
+	$ref = $dbh->selectrow_hashref('SELECT name,date,EXTRACT(EPOCH FROM last_update) AS last_update FROM events WHERE event=? AND vhost=?',
 		undef, $event, $r->get_server_name)
 		or error($r, "Could not find event $event", 404, "File not found");
 
@@ -96,16 +96,16 @@ sub handler {
 	}
 	
 	# Count the number of selected images.
-	$ref = $dbh->selectrow_hashref("SELECT COUNT(*) AS num_selected FROM images WHERE event=? AND selected=\'t\'", undef, $event);
+	$ref = $dbh->selectrow_hashref("SELECT COUNT(*) AS num_selected FROM images WHERE event=? AND vhost=? AND selected=\'t\'", undef, $event, $r->get_server_name);
 	my $num_selected = $ref->{'num_selected'};
 
 	# Find all images related to this event.
 	my $where = ($all == 0) ? ' AND selected=\'t\'' : '';
 	my $limit = (defined($start) && defined($num) && !$settings{'fullscreen'}) ? (" LIMIT $num OFFSET " . ($start-1)) : "";
 
-	my $q = $dbh->prepare("SELECT *, (date - INTERVAL '6 hours')::date AS day FROM images WHERE event=? $where ORDER BY (date - INTERVAL '6 hours')::date,takenby,date,filename $limit")
+	my $q = $dbh->prepare("SELECT *, (date - INTERVAL '6 hours')::date AS day FROM images WHERE event=? AND vhost=? $where ORDER BY (date - INTERVAL '6 hours')::date,takenby,date,filename $limit")
 		or dberror($r, "prepare()");
-	$q->execute($event)
+	$q->execute($event, $r->get_server_name)
 		or dberror($r, "image enumeration");
 
 	# Print the page itself
@@ -154,7 +154,7 @@ sub handler {
 				TRIM(model.value) AS model,
 				coalesce(TRIM(lens_spec.value), TRIM(lens.value)) AS lens,
 				COUNT(*) AS num
-			FROM ( SELECT * FROM images WHERE event=? $where ORDER BY (date - INTERVAL '6 hours')::date,takenby,date,filename $limit ) i
+			FROM ( SELECT * FROM images WHERE event=? AND vhost=? $where ORDER BY (date - INTERVAL '6 hours')::date,takenby,date,filename $limit ) i
 				LEFT JOIN exif_info model ON i.id=model.image
 				LEFT JOIN ( SELECT * FROM exif_info WHERE tag='Lens' ) lens ON i.id=lens.image
 				LEFT JOIN ( SELECT * FROM exif_info WHERE tag='LensSpec') lens_spec ON i.id=lens_spec.image
@@ -162,7 +162,7 @@ sub handler {
 			GROUP BY 1,2
 			ORDER BY 1,2")
 			or die "Couldn't prepare to find equipment: $!";
-		$eq->execute($event)
+		$eq->execute($event, $r->get_server_name)
 			or die "Couldn't find equipment: $!";
 
 		my @equipment = ();
@@ -441,8 +441,8 @@ sub print_nextprev {
 	return unless (defined($start) && defined($num));
 
 	# determine total number
-	my $ref = $dbh->selectrow_hashref("SELECT count(*) AS num_images FROM images WHERE event=? $where",
-		undef, $event)
+	my $ref = $dbh->selectrow_hashref("SELECT count(*) AS num_images FROM images WHERE event=? AND vhost=? $where",
+		undef, $event, $r->get_server_name)
 		or dberror($r, "image enumeration");
 	my $num_images = $ref->{'num_images'};
 
diff --git a/perl/Sesse/pr0n/Listing.pm b/perl/Sesse/pr0n/Listing.pm
index 473914e..02ac273 100644
--- a/perl/Sesse/pr0n/Listing.pm
+++ b/perl/Sesse/pr0n/Listing.pm
@@ -18,7 +18,7 @@ sub handler {
 
 #	my $q = $dbh->prepare('SELECT t1.id,t1.date,t1.name FROM events t1 LEFT JOIN images t2 ON t1.id=t2.event WHERE t1.vhost=? GROUP BY t1.id,t1.date,t1.name ORDER BY COALESCE(MAX(t2.date),\'1970-01-01 00:00:00\'),t1.id') or
 #		dberror($r, "Couldn't list events");
-	my $q = $dbh->prepare('SELECT id,date,name FROM events e JOIN last_picture_cache c ON e.id=c.event WHERE vhost=? ORDER BY last_picture DESC')
+	my $q = $dbh->prepare('SELECT event,date,name FROM events e JOIN last_picture_cache c USING (vhost,event) WHERE vhost=? ORDER BY last_picture DESC')
 		or dberror($r, "Couldn't list events");
 	$q->execute($r->get_server_name)
 		or dberror($r, "Couldn't get events");
@@ -29,7 +29,7 @@ sub handler {
 	$r->print("    <ul>\n");
 
 	while (my $ref = $q->fetchrow_hashref()) {
-		my $id = $ref->{'id'};
+		my $id = $ref->{'event'};
 		my $date = HTML::Entities::encode_entities(Encode::decode_utf8($ref->{'date'}));
 		my $name = HTML::Entities::encode_entities(Encode::decode_utf8($ref->{'name'}));
 		
diff --git a/perl/Sesse/pr0n/Rotate.pm b/perl/Sesse/pr0n/Rotate.pm
index be02312..c3769f5 100644
--- a/perl/Sesse/pr0n/Rotate.pm
+++ b/perl/Sesse/pr0n/Rotate.pm
@@ -37,7 +37,7 @@ sub handler {
 				if ($rotval == 90 || $rotval == 270) {
 					my $q = $dbh->do('UPDATE images SET height=width,width=height WHERE id=?', undef, $id)
 						or dberror($r, "Size clear of $id failed");
-					$dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE id=( SELECT event FROM images WHERE id=? )',
+					$dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE (vhost,event)=( SELECT vhost,event FROM images WHERE id=? )',
 						undef, $id)
 						or dberror($r, "Cache invalidation at $id failed");
 				}
@@ -46,7 +46,7 @@ sub handler {
 				{
 
 					eval {
-						$dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE id=( SELECT event FROM images WHERE id=? )',
+						$dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE (vhost,event)=( SELECT vhost,event FROM images WHERE id=? )',
 							undef, $id);
 						$dbh->do('INSERT INTO deleted_images SELECT * FROM images WHERE id=?',
 							undef, $id);
@@ -67,7 +67,7 @@ sub handler {
 	}
 	
 	my $event = $apr->param('event');
-	$dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE id=?', undef, $event)
+	$dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE vhost=? AND event=?', undef, $r->get_server_name, $event)
 		or dberror($r, "Cache invalidation failed");
 
 	Sesse::pr0n::Common::footer($r);
diff --git a/perl/Sesse/pr0n/Select.pm b/perl/Sesse/pr0n/Select.pm
index 21dca6a..781c598 100644
--- a/perl/Sesse/pr0n/Select.pm
+++ b/perl/Sesse/pr0n/Select.pm
@@ -23,28 +23,12 @@ sub handler {
 		local $dbh->{AutoCommit} = 0;
 		local $dbh->{RaiseError} = 1;
 
-		if (defined($apr->param('mode')) && $apr->param('mode') eq 'single') {
-			# single mode; enable one (FIXME: need to support disable too)
-			my $filename = $apr->param('filename');
-			$dbh->do('UPDATE images SET selected=\'t\' WHERE event=? AND filename=?', undef, $event, $filename);
-		} else {
-			# traditional multi-mode
-			$dbh->do('UPDATE images SET selected=\'f\' WHERE event=?', undef, $event);
-		
-			my @params = $apr->param();
-			my $key;
-			for $key (@params) {
-				if ($key =~ /^sel-(\d+)/ && $apr->param($key) eq 'on') {
-					my $id = $1;
-					my $q = $dbh->do('UPDATE images SET selected=\'t\' WHERE id=?', undef, $id)
-						or dberror($r, "Selection of $id failed: $!");
-					$r->print("    <p>Selected image ID `$id'.</p>\n");
-				}
-			}
-		}
+		# FIXME: need to support disable too
+		my $filename = $apr->param('filename');
+		$dbh->do('UPDATE images SET selected=\'t\' WHERE vhost=? AND event=? AND filename=?', undef, $r->get_server_name, $event, $filename);
 	}
 
-	$dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE id=?', undef, $event)
+	$dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE vhost=? AND event=?', undef, $r->get_server_name, $event)
 		or dberror($r, "Cache invalidation failed");
 
 	Sesse::pr0n::Common::footer($r);
diff --git a/perl/Sesse/pr0n/WebDAV.pm b/perl/Sesse/pr0n/WebDAV.pm
index c883a03..49be239 100644
--- a/perl/Sesse/pr0n/WebDAV.pm
+++ b/perl/Sesse/pr0n/WebDAV.pm
@@ -137,8 +137,8 @@ EOF
 			$r->headers_out->{'content-location'} = "/webdav/upload/$event/";
 			
 			# Check that we do indeed exist
-			my $ref = $dbh->selectrow_hashref('SELECT count(*) AS numev FROM events WHERE id=?',
-				undef, $event);
+			my $ref = $dbh->selectrow_hashref('SELECT count(*) AS numev FROM events WHERE vhost=? AND event=?',
+				undef, $r->get_server_name, $event);
 			if ($ref->{'numev'} != 1) {
 				$r->status(404);
 				$r->content_type('text/plain; charset=utf-8');
@@ -164,9 +164,9 @@ EOF
 
 			# List all the files within too, of course :-)
 			if ($depth >= 1) {
-				my $q = $dbh->prepare('SELECT * FROM images WHERE event=?') or
+				my $q = $dbh->prepare('SELECT * FROM images WHERE vhost=? AND event=?') or
 					dberror($r, "Couldn't list images");
-				$q->execute($event) or
+				$q->execute($r->get_server_name, $event) or
 					dberror($r, "Couldn't get events");
 		
 				while (my $ref = $q->fetchrow_hashref()) {
@@ -220,8 +220,8 @@ EOF
 			$r->headers_out->{'content-location'} = "/webdav/upload/$event/autorename/";
 			
 			# Check that we do indeed exist
-			my $ref = $dbh->selectrow_hashref('SELECT count(*) AS numev FROM events WHERE id=?',
-				undef, $event);
+			my $ref = $dbh->selectrow_hashref('SELECT count(*) AS numev FROM events WHERE vhost=? AND event=?',
+				undef, $r->get_server_name, $event);
 			if ($ref->{'numev'} != 1) {
 				$r->status(404);
 				$r->content_type('text/plain; charset=utf-8');
@@ -253,8 +253,8 @@ EOF
 			my ($fname, $size, $mtime);
 			
 			# check if we have a pending fake file for this
-			my $ref = $dbh->selectrow_hashref('SELECT count(*) AS numfiles FROM fake_files WHERE event=? AND filename=? AND expires_at > now()',
-				undef, $event, $filename);
+			my $ref = $dbh->selectrow_hashref('SELECT count(*) AS numfiles FROM fake_files WHERE event=? AND vhost=? AND filename=? AND expires_at > now()',
+				undef, $event, $r->get_server_name, $filename);
 			if ($ref->{'numfiles'} == 1) {
 				$fname = "/dev/null";
 				$size = 0;
@@ -296,16 +296,16 @@ EOF
 			my ($fname, $size, $mtime);
 			
 			# check if we have a pending fake file for this
-			my $ref = $dbh->selectrow_hashref('SELECT count(*) AS numfiles FROM fake_files WHERE event=? AND filename=? AND expires_at > now()',
-				undef, $event, $filename);
+			my $ref = $dbh->selectrow_hashref('SELECT count(*) AS numfiles FROM fake_files WHERE event=? AND vhost=? AND filename=? AND expires_at > now()',
+				undef, $event, $r->get_server_name, $filename);
 			if ($ref->{'numfiles'} == 1) {
 				$fname = "/dev/null";
 				$size = 0;
 				$mtime = time;
 			} else {
 				# check if we have a "shadow file" for this
-				my $ref = $dbh->selectrow_hashref('SELECT id FROM shadow_files WHERE event=? AND filename=? AND expires_at > now()',
-					undef, $event, $filename);
+				my $ref = $dbh->selectrow_hashref('SELECT id FROM shadow_files WHERE vhost=? AND event=? AND filename=? AND expires_at > now()',
+					undef, $r->get_server_name, $event, $filename);
 				if (defined($ref)) {
 				 	($fname, $size, $mtime) = Sesse::pr0n::Common::stat_image_from_id($r, $ref->{'id'});
 				}
@@ -359,8 +359,8 @@ EOF
 		my ($fname, $size, $mtime);
 
 		# check if we have a pending fake file for this
-		my $ref = $dbh->selectrow_hashref('SELECT count(*) AS numfiles FROM fake_files WHERE event=? AND filename=? AND expires_at > now()',
-			undef, $event, $filename);
+		my $ref = $dbh->selectrow_hashref('SELECT count(*) AS numfiles FROM fake_files WHERE event=? AND vhost=? AND filename=? AND expires_at > now()',
+			undef, $event, $r->get_server_name, $filename);
 		if ($ref->{'numfiles'} == 1) {
 			$fname = "/dev/null";
 			$size = 0;
@@ -368,8 +368,8 @@ EOF
 		} else {
 			# check if we have a "shadow file" for this
 			if (defined($autorename) && $autorename eq "autorename/") {
-				my $ref = $dbh->selectrow_hashref('SELECT id FROM shadow_files WHERE event=? AND filename=? AND expires_at > now()',
-					undef, $event, $filename);
+				my $ref = $dbh->selectrow_hashref('SELECT id FROM shadow_files WHERE host=? AND event=? AND filename=? AND expires_at > now()',
+					undef, $r->get_server_name, $event, $filename);
 				if (defined($ref)) {
 				 	($fname, $size, $mtime) = Sesse::pr0n::Common::stat_image_from_id($r, $ref->{'id'});
 				}
@@ -424,11 +424,11 @@ EOF
 		# make them happy
 		# 
 		if ($r->headers_in->{'content-length'} == 0) {
-			$dbh->do('DELETE FROM fake_files WHERE expires_at <= now() OR (event=? AND filename=?);',
-				undef, $event, $filename)
+			$dbh->do('DELETE FROM fake_files WHERE expires_at <= now() OR (event=? AND vhost=? AND filename=?);',
+				undef, $event, $r->get_server_name, $filename)
 				or dberror($r, "Couldn't prune fake_files");
-			$dbh->do('INSERT INTO fake_files (event,filename,expires_at) VALUES (?,?,now() + interval \'30 seconds\');',
-				undef, $event, $filename)
+			$dbh->do('INSERT INTO fake_files (vhost,event,filename,expires_at) VALUES (?,?,?,now() + interval \'30 seconds\');',
+				undef, $r->get_server_name, $event, $filename)
 				or dberror($r, "Couldn't add file");
 			$r->content_type('text/plain; charset="utf-8"');
 			$r->status(201);
@@ -446,8 +446,8 @@ EOF
 		
 		# Autorename if we need to
 		if (defined($autorename) && $autorename eq "autorename/") {
-			my $ref = $dbh->selectrow_hashref("SELECT COUNT(*) AS numfiles FROM images WHERE event=? AND filename=?",
-				undef, $event, $filename)
+			my $ref = $dbh->selectrow_hashref("SELECT COUNT(*) AS numfiles FROM images WHERE vhost=? AND event=? AND filename=?",
+				undef, $r->get_server_name, $event, $filename)
 				or dberror($r, "Couldn't check for existing files");
 			if ($ref->{'numfiles'} > 0) {
 				$r->log->info("Renaming $filename to $newid.jpeg");
@@ -463,13 +463,13 @@ EOF
 
 			# Try to insert this new file
 			eval {
-				$dbh->do('DELETE FROM fake_files WHERE event=? AND filename=?;',
-					undef, $event, $filename);
+				$dbh->do('DELETE FROM fake_files WHERE vhost=? AND event=? AND filename=?',
+					undef, $r->get_server_name, $event, $filename);
 					
-				$dbh->do('INSERT INTO images (id,event,uploadedby,takenby,filename) VALUES (?,?,?,?,?);',
-					undef, $newid, $event, $user, $takenby, $filename);
-				$dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE id=?',
-					undef, $event);
+				$dbh->do('INSERT INTO images (id,vhost,event,uploadedby,takenby,filename) VALUES (?,?,?,?,?,?)',
+					undef, $newid, $r->get_server_name, $event, $user, $takenby, $filename);
+				$dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE vhost=? AND event=?',
+					undef, $r->get_server_name, $event);
 
 				# Now save the file to disk
 				$fname = Sesse::pr0n::Common::get_disk_location($r, $newid);
@@ -509,11 +509,11 @@ EOF
 
 		# Insert a `shadow file' we can stat the next 30 secs
 		if (defined($autorename) && $autorename eq "autorename/") {
-			$dbh->do('DELETE FROM shadow_files WHERE expires_at <= now() OR (event=? AND filename=?);',
-				undef, $event, $filename)
+			$dbh->do('DELETE FROM shadow_files WHERE expires_at <= now() OR (vhost=? AND event=? AND filename=?);',
+				undef, $r->get_server_name, $event, $filename)
 				or dberror($r, "Couldn't prune shadow_files");
-			$dbh->do('INSERT INTO shadow_files (event,filename,id,expires_at) VALUES (?,?,?,now() + interval \'30 seconds\');',
-				undef, $event, $orig_filename, $newid)
+			$dbh->do('INSERT INTO shadow_files (vhost,event,filename,id,expires_at) VALUES (?,?,?,?,now() + interval \'30 seconds\');',
+				undef, $r->get_server_name, $event, $orig_filename, $newid)
 				or dberror($r, "Couldn't add shadow file");
 			$r->log->info("Added shadow entry for $event/$filename");
 		}
@@ -555,7 +555,7 @@ EOF
 		my $ne_desc = Sesse::pr0n::Common::guess_charset($apr->param('neweventdesc'));
 		if (defined($ne_id)) {
 			# Trying to add a new event, let's see if it already exists
-			my $q = $dbh->prepare('SELECT COUNT(*) AS cnt FROM events WHERE id=? AND vhost=?')
+			my $q = $dbh->prepare('SELECT COUNT(*) AS cnt FROM events WHERE event=? AND vhost=?')
 				or dberror($r, "Couldn't prepare event count");
 			$q->execute($ne_id, $r->get_server_name)
 				or dberror($r, "Couldn't execute event count");
@@ -585,8 +585,8 @@ EOF
 		
 		# Autorename if we need to
 		{
-			my $ref = $dbh->selectrow_hashref("SELECT COUNT(*) AS numfiles FROM images WHERE event=? AND filename=?",
-				undef, $event, $filename)
+			my $ref = $dbh->selectrow_hashref("SELECT COUNT(*) AS numfiles FROM images WHERE vhost=? AND event=? AND filename=?",
+				undef, $r->get_server_name, $event, $filename)
 				or dberror($r, "Couldn't check for existing files");
 			if ($ref->{'numfiles'} > 0) {
 				$r->log->info("Renaming $filename to $newid.jpeg");
@@ -602,10 +602,10 @@ EOF
 
 			# Try to insert this new file
 			eval {
-				$dbh->do('INSERT INTO images (id,event,uploadedby,takenby,filename) VALUES (?,?,?,?,?);',
-					undef, $newid, $event, $user, $takenby, $filename);
-				$dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE id=?',
-					undef, $event);
+				$dbh->do('INSERT INTO images (id,vhost,event,uploadedby,takenby,filename) VALUES (?,?,?,?,?);',
+					undef, $newid, $r->get_server_name, $event, $user, $takenby, $filename);
+				$dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE vhost=? AND event=?',
+					undef, $r->get_server_name, $event);
 
 				# Now save the file to disk
 				$fname = Sesse::pr0n::Common::get_disk_location($r, $newid);
@@ -702,11 +702,11 @@ EOF
 		}
 		
 		my ($event, $autorename, $filename) = ($1, $2, $3);
-		$dbh->do('DELETE FROM images WHERE event=? AND filename=?;',
-			undef, $event, $filename)
+		$dbh->do('DELETE FROM images WHERE vhost=? AND event=? AND filename=?',
+			undef, $r->get_server_name, $event, $filename)
 			or dberror($r, "Couldn't remove file");
-		$dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE id=?',
-			undef, $event)
+		$dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE vhost=? AND event=?',
+			undef, $r->get_server_name, $event)
 			or dberror($r, "Couldn't invalidate cache");
 		$r->status(200);
 		$r->print("OK");
diff --git a/sql/pr0n.sql b/sql/pr0n.sql
index 919ce23..c3b6689 100644
--- a/sql/pr0n.sql
+++ b/sql/pr0n.sql
@@ -1,61 +1,74 @@
 CREATE TABLE events (
-    id character varying NOT NULL PRIMARY KEY,
-    date character varying NOT NULL,
+    event character varying NOT NULL,
+    "date" character varying NOT NULL,
     name character varying NOT NULL,
     vhost character varying NOT NULL,
-    last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+    last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+
+    PRIMARY KEY (vhost, event)
 );
 
 -- In a separate table to avoid deadlocks.
 CREATE TABLE last_picture_cache ( 
-   event varchar PRIMARY KEY references events ( id ),
-   last_picture timestamp without time zone
+   vhost varchar NOT NULL,
+   event varchar NOT NULL,
+   last_picture timestamp without time zone,
+
+   PRIMARY KEY (vhost,event),
+   FOREIGN KEY (vhost,event) REFERNECES events(vhost,event)
 );
 
 CREATE TABLE images (
     id serial NOT NULL PRIMARY KEY,
-    event character varying NOT NULL REFERENCES events(id),
+    vhost character varying NOT NULL,
+    event character varying NOT NULL,
     filename character varying NOT NULL,
     width integer DEFAULT -1 NOT NULL,
     height integer DEFAULT -1 NOT NULL,
     uploadedby character varying NOT NULL,
-    date timestamp without time zone,
+    "date" timestamp without time zone,
     takenby character varying NOT NULL,
-    selected boolean DEFAULT false
+    selected boolean DEFAULT false,
+
+    FOREIGN KEY (vhost,event) REFERENCES events (vhost,event)
 );
-CREATE UNIQUE INDEX unique_filenames ON images USING btree (event, filename);
+CREATE UNIQUE INDEX unique_filenames ON images USING btree (vhost, event, filename);
 
 CREATE TABLE deleted_images (
     id integer NOT NULL,
-    event character varying(32) NOT NULL,
-    filename character varying(255) NOT NULL,
+    vhost character varying,
+    event character varying NOT NULL,
+    filename character varying NOT NULL,
     width integer DEFAULT -1 NOT NULL,
     height integer DEFAULT -1 NOT NULL,
-    uploadedby character varying(32),
-    date timestamp without time zone,
-    takenby character varying(32) NOT NULL,
+    uploadedby character varying,
+    "date" timestamp without time zone,
+    takenby character varying NOT NULL,
     selected boolean
 );
 
 CREATE TABLE fake_files (
-    event character varying(32) NOT NULL REFERENCES events(id),
-    filename character varying(255) NOT NULL,
+    vhost character varying NOT NULL,
+    event character varying NOT NULL,
+    filename character varying NOT NULL,
     expires_at timestamp without time zone NOT NULL,
 
-    PRIMARY KEY ( event, filename )
+    PRIMARY KEY ( vhost, event, filename ),
+    FOREIGN KEY (vhost,event) REFERENCES events (vhost,event)
 );
 
 CREATE TABLE shadow_files (
-    event character varying(32) NOT NULL,
-    filename character varying(255) NOT NULL,
+    vhost character varying NOT NULL,
+    event character varying NOT NULL,
+    filename character varying NOT NULL,
     id integer NOT NULL,
     expires_at timestamp without time zone NOT NULL
 );
 
 CREATE TABLE users (
-    username character varying(32) NOT NULL,
+    username character varying NOT NULL,
     sha1password character(28) NOT NULL,
-    vhost character varying(32) NOT NULL
+    vhost character varying NOT NULL
 );
 
 CREATE TABLE exif_info (
diff --git a/sql/upgrade-v2.40.sql b/sql/upgrade-v2.40.sql
new file mode 100644
index 0000000..9c4fd27
--- /dev/null
+++ b/sql/upgrade-v2.40.sql
@@ -0,0 +1,69 @@
+--
+-- Upgrades pre-v2.40 databases to 2.40 format -- basically, the unique event identifier
+-- has changed from (event) to (vhost,event) and we need to handle that.
+--
+
+ALTER TABLE events RENAME COLUMN id TO event;
+
+-- Create the new index that will eventually replace the old one
+CREATE UNIQUE INDEX events_pkey2 ON events ( vhost, event );
+
+-- Add a vhost column to the images table, populate it, and make a new foreign
+-- key constraint
+ALTER TABLE images ADD COLUMN vhost varchar;
+UPDATE images SET vhost=( SELECT vhost FROM events WHERE event=images.event );
+ALTER TABLE images ADD FOREIGN KEY (vhost, event) REFERENCES events (vhost,event);
+ALTER TABLE images ALTER COLUMN vhost SET NOT NULL;
+
+-- Same for fake_files
+ALTER TABLE fake_files ADD COLUMN vhost varchar;
+UPDATE fake_files SET vhost=( SELECT vhost FROM events WHERE event=fake_files.event );
+ALTER TABLE fake_files ADD FOREIGN KEY (vhost, event) REFERENCES events (vhost,event);
+ALTER TABLE fake_files ALTER COLUMN vhost SET NOT NULL;
+
+-- and last_picture_cache
+ALTER TABLE last_picture_cache ADD COLUMN vhost varchar;
+UPDATE last_picture_cache SET vhost=( SELECT vhost FROM events WHERE event=last_picture_cache.event );
+ALTER TABLE last_picture_cache ADD FOREIGN KEY (vhost, event) REFERENCES events (vhost,event);
+ALTER TABLE last_picture_cache ALTER COLUMN vhost SET NOT NULL;
+
+ALTER TABLE last_picture_cache DROP CONSTRAINT last_picture_cache_pkey;
+ALTER TABLE last_picture_cache ADD PRIMARY KEY (vhost,event);
+
+-- and deleted_images
+ALTER TABLE deleted_images ADD COLUMN vhost varchar;
+UPDATE deleted_images SET vhost=( SELECT vhost FROM events WHERE event=deleted_images.event );
+
+-- and shadow_files
+ALTER TABLE shadow_files ADD COLUMN vhost varchar;
+UPDATE shadow_files SET vhost=( SELECT vhost FROM events WHERE event=shadow_files.event );
+ALTER TABLE shadow_files ALTER COLUMN vhost SET NOT NULL;
+
+-- Drop the old index
+ALTER TABLE events DROP CONSTRAINT events_pkey CASCADE;
+
+-- Finally, fix up some unique constraints
+DROP INDEX unique_filenames;
+CREATE INDEX unique_filenames ON images (vhost,event,filename);
+
+ALTER TABLE fake_files DROP CONSTRAINT fake_files_pkey;
+ALTER TABLE fake_files ADD PRIMARY KEY (vhost,event,filename);
+
+-- And some old sillyness from waaay back (the MySQL days)
+ALTER TABLE deleted_images ALTER COLUMN event TYPE varchar;
+ALTER TABLE deleted_images ALTER COLUMN filename TYPE varchar;
+ALTER TABLE deleted_images ALTER COLUMN uploadedby TYPE varchar;
+ALTER TABLE deleted_images ALTER COLUMN takenby TYPE varchar;
+
+ALTER TABLE fake_files ALTER COLUMN event TYPE varchar;
+ALTER TABLE fake_files ALTER COLUMN filename TYPE varchar;
+
+ALTER TABLE shadow_files ALTER COLUMN event TYPE varchar;
+ALTER TABLE shadow_files ALTER COLUMN filename TYPE varchar;
+
+ALTER TABLE users ALTER COLUMN username TYPE varchar;
+ALTER TABLE users ALTER COLUMN vhost TYPE varchar;
+
+-- Reclaim space from the old indexes
+VACUUM FULL ANALYZE;
+