require Sesse::pr0n::Config_local;
};
- $VERSION = "v2.30";
+ $VERSION = "v2.40";
@ISA = qw(Exporter);
@EXPORT = qw(&error &dberror);
%EXPORT_TAGS = qw();
# 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: $!";
}
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 ();
# 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'};
);
# 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.
$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");
}
# 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
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
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 = ();
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'};
# 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");
$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'}));
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");
}
{
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);
}
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);
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);
$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');
# 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()) {
$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');
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;
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'});
}
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;
} 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'});
}
# 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);
# 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");
# 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);
# 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");
}
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");
# 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");
# 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);
}
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");
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 (
--- /dev/null
+--
+-- 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;
+
projects / pr0n / commitdiff