From 6c8c4bf3f1e3109523f9ec6117abfdf0e04a0d99 Mon Sep 17 00:00:00 2001 From: "Steinar H. Gunderson" Date: Thu, 7 Jan 2016 00:27:52 +0100 Subject: [PATCH 1/1] Add a concept where an image can be a rendering of another, which means it is not shown by itself, but is used for all JPEG conversions of the other one. The intended use-case is a NEF+JPEG pair, where the NEF has the JPEG as a render. Note that there is nothing in place to actually populate this data yet. --- perl/Sesse/pr0n/Image.pm | 10 +++++++++- perl/Sesse/pr0n/Index.pm | 11 ++++++----- sql/pr0n.sql | 12 +++++++++++- sql/upgrade-v3.11.sql | 8 ++++++++ 4 files changed, 34 insertions(+), 7 deletions(-) create mode 100644 sql/upgrade-v3.11.sql diff --git a/perl/Sesse/pr0n/Image.pm b/perl/Sesse/pr0n/Image.pm index 4a71603..f12a158 100644 --- a/perl/Sesse/pr0n/Image.pm +++ b/perl/Sesse/pr0n/Image.pm @@ -42,10 +42,18 @@ sub handler { #} else { # Look it up in the database - my $ref = $dbh->selectrow_hashref('SELECT id,width,height FROM images WHERE event=? AND vhost=? AND filename=?', + my $ref = $dbh->selectrow_hashref('SELECT id,render_id,width,height FROM images WHERE event=? AND vhost=? AND filename=?', undef, $event, Sesse::pr0n::Common::get_server_name($r), $filename); return error($r, "Could not find $event/$filename", 404, "File not found") unless (defined($ref)); + if (defined($xres) && defined($yres) && defined($ref->{'render_id'}) && !$infobox) { + # We have a render, we're not asked for the original, and we do not have infobox. + $ref = $dbh->selectrow_hashref('SELECT id,filename,width,height FROM images WHERE id=?', + undef, $ref->{'render_id'}); + return error($r, "Could not find render of $event/$filename", 404, "File not found") unless (defined($ref)); + $filename = $ref->{'filename'}; + } + $id = $ref->{'id'}; $dbwidth = $ref->{'width'}; $dbheight = $ref->{'height'}; diff --git a/perl/Sesse/pr0n/Index.pm b/perl/Sesse/pr0n/Index.pm index 3f9e524..7d2ddf8 100644 --- a/perl/Sesse/pr0n/Index.pm +++ b/perl/Sesse/pr0n/Index.pm @@ -63,8 +63,8 @@ sub handler { $where = ' AND event=' . $dbh->quote($event); } - # Any NEF files => default to processing - my $ref = $dbh->selectrow_hashref("SELECT * FROM images WHERE vhost=? $where AND ( LOWER(filename) LIKE '%.nef' OR LOWER(filename) LIKE '%.cr2' ) LIMIT 1", + # Any NEF files with no non-NEF renders => default to processing + my $ref = $dbh->selectrow_hashref("SELECT * FROM images WHERE vhost=? $where AND ( LOWER(filename) LIKE '%.nef' OR LOWER(filename) LIKE '%.cr2' ) AND render_id IS NULL LIMIT 1", undef, Sesse::pr0n::Common::get_server_name($r)) and $defsettings{'xres'} = $defsettings{'yres'} = undef; @@ -156,13 +156,13 @@ sub handler { # } # Count the number of selected images. - $ref = $dbh->selectrow_hashref("SELECT COUNT(*) AS num_selected FROM images WHERE vhost=? $where AND selected=\'t\'", undef, Sesse::pr0n::Common::get_server_name($r)); + $ref = $dbh->selectrow_hashref("SELECT COUNT(*) AS num_selected FROM images WHERE vhost=? $where AND selected AND NOT is_render", undef, Sesse::pr0n::Common::get_server_name($r)); my $num_selected = $ref->{'num_selected'}; # Find all images related to this event. 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 vhost=? $where ORDER BY (date - INTERVAL '6 hours')::date $datesort,takenby,date,filename $limit") + my $q = $dbh->prepare("SELECT *, (date - INTERVAL '6 hours')::date AS day FROM images WHERE vhost=? $where AND NOT is_render ORDER BY (date - INTERVAL '6 hours')::date $datesort,takenby,date,filename $limit") or return dberror($r, "prepare()"); $q->execute(Sesse::pr0n::Common::get_server_name($r)) or return dberror($r, "image enumeration"); @@ -225,6 +225,7 @@ sub handler { COUNT(*) AS num FROM images WHERE vhost=? $where + AND NOT is_render GROUP BY 1,2 ORDER BY 1,2") or die "Couldn't prepare to find equipment: $!"; @@ -455,7 +456,7 @@ 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 vhost=? $where", + my $ref = $dbh->selectrow_hashref("SELECT count(*) AS num_images FROM images WHERE vhost=? $where AND NOT is_render", undef, Sesse::pr0n::Common::get_server_name($r)) or return dberror($r, "image enumeration"); my $num_images = $ref->{'num_images'}; diff --git a/sql/pr0n.sql b/sql/pr0n.sql index f3f2087..860fd47 100644 --- a/sql/pr0n.sql +++ b/sql/pr0n.sql @@ -34,8 +34,16 @@ CREATE TABLE images ( selected boolean DEFAULT false, model character varying, lens character varying, + is_render boolean NOT NULL DEFAULT false, -- Is this a render of another picture? (If so, don't show it separately.) + render_id integer, -- If not NULL, show this picture instead of ourselves for all JPEG-creation purposes. - FOREIGN KEY (vhost,event) REFERENCES events (vhost,event) + FOREIGN KEY (vhost,event) REFERENCES events (vhost,event), + FOREIGN KEY (vhost,event,render_id) REFERENCES images (vhost,event,id), + + -- Redundant with the primary key, but the foreign key needs it. + UNIQUE (vhost,event,id), + + CHECK (NOT (is_render AND (render_id IS NOT NULL))) ); CREATE UNIQUE INDEX unique_filenames ON images USING btree (vhost, event, filename); @@ -52,6 +60,8 @@ CREATE TABLE deleted_images ( selected boolean, model character varying, lens character varying + is_render boolean NOT NULL, + render_id integer, ); CREATE TABLE users ( diff --git a/sql/upgrade-v3.11.sql b/sql/upgrade-v3.11.sql new file mode 100644 index 0000000..7996fea --- /dev/null +++ b/sql/upgrade-v3.11.sql @@ -0,0 +1,8 @@ +alter table images add column is_render boolean NOT NULL DEFAULT false; +alter table images add column render_id integer; +CREATE UNIQUE INDEX unique_id_for_render ON images USING btree (vhost, event, id); +alter table images add foreign key (render_id,vhost,event) REFERENCES images (id,vhost,event); +alter table images add check (NOT (is_render AND (render_id IS NOT NULL))); + +alter table deleted_images add column is_render boolean NOT NULL DEFAULT false; +alter table deleted_images add column render_id integer; -- 2.39.2