word/edit: image metadata in combined json column

Convert existing columns to a freely extensible postgres object:

	UPDATE word SET image = json_strip_nulls(json_build_object(
		'source', source, 'convert', thumb, 'aspect', 1.5
	)) WHERE source IS NOT NULL;

diff --git a/editor.css b/editor.css
index 5c29523388b1163de6c2d65bb448a3d8482874b7..f37693528647f54fd91da3e85f651fd6d1593ea4 100644 (file)
--- a/editor.css
+++ b/editor.css
@@ -38,7 +38,7 @@ input[type=number] {
 select {
        padding: .3rem .2rem; /* TODO: input */
 }
-#thumbpreview {
+#convertpreview {
        width: 300px;
        align-self: start;
        flex-shrink: 0;

diff --git a/tools/mkwordthumb b/tools/mkwordthumb
index 6d1ac671c642c7e169bf02d3e92f9180a078d493..2404324d415815fffdb0e570f5fcf511d05ee3ab 100755 (executable)
--- a/tools/mkwordthumb
+++ b/tools/mkwordthumb
@@ -4,6 +4,7 @@ use warnings;
 use lib '.';
 use Shiar_Sheet::ImagePrep;
 use Shiar_Sheet::DB;
+use JSON ();
 
 our $VERSION = '1.00';
 
@@ -14,6 +15,8 @@ my $query = $db->select(word => '*', \%filter);
 while (my $row = $query->hash) {
        my $image = Shiar_Sheet::ImagePrep->new("data/word/org/$row->{id}.jpg");
        eval {
-               $image->convert("data/word/en/$row->{id}.jpg", $row->{thumb});
+               my $meta = eval { JSON->new->decode($row->{image} // '{}') }
+                       or die ["Invalid JSON metadata in image column.", $@];
+               $image->convert("data/word/en/$row->{id}.jpg", $meta->{convert});
        } or warn "$row->{id}: @{$@}";
 }

diff --git a/tools/word.pg.sql b/tools/word.pg.sql
index eabf17deb8b6e2bc4b6bbdc7b382ed68a9eff7ed..4e5aca6d12a9017e21372e5b3d2b860cd9916af3 100644 (file)
--- a/tools/word.pg.sql
+++ b/tools/word.pg.sql
@@ -17,8 +17,8 @@ CREATE TABLE word (
                                        CHECK (prio >= 0 OR ref IS NOT NULL),
        grade      integer,
        cover      boolean     NOT NULL DEFAULT FALSE,
-       source     text                 CHECK (source ~ '^https?://'),
-       thumb      text[],
+       image      jsonb                CHECK (image->>'source' ~ '^https?://'
+                                          AND jsonb_typeof(image->'convert') = 'array'),
        wptitle    text,
        story      text,
        creator    integer              REFERENCES login (id),
@@ -35,8 +35,7 @@ COMMENT ON COLUMN word.ref        IS 'reference to equivalent en translation';
 COMMENT ON COLUMN word.prio       IS 'difficulty level or importance; lower values have precedence';
 COMMENT ON COLUMN word.grade      IS 'ascending hierarchical order, preceding default alphabetical';
 COMMENT ON COLUMN word.cover      IS 'highlight if selected';
-COMMENT ON COLUMN word.source     IS 'URI of downloaded image';
-COMMENT ON COLUMN word.thumb      IS 'ImageMagick convert options to create thumbnail from source image';
+COMMENT ON COLUMN word.image      IS 'metadata of illustrations, including downloaded URI and ImageMagick convert options';
 COMMENT ON COLUMN word.wptitle    IS 'reference Wikipedia article';
 COMMENT ON COLUMN word.story      IS 'paragraph defining or describing the entity, wikipedia intro';
 COMMENT ON COLUMN word.updated    IS 'last significant change';
@@ -71,12 +70,11 @@ CREATE OR REPLACE VIEW _word_ref AS
                coalesce(r.prio,    w.prio   ) prio,
                coalesce(r.grade,   w.grade  ) grade,
                coalesce(r.cover,   w.cover  ) cover,
-               coalesce(r.source,  w.source ) source,
-               coalesce(r.thumb,   w.thumb  ) thumb,
+               coalesce(r.image,   w.image  ) image,
                coalesce(r.wptitle, w.wptitle) wptitle,
                coalesce(r.story,   w.story  ) story,
                r.creator, r.created, r.updated,
-               CASE WHEN r.source IS NULL THEN w.id ELSE r.id END id -- image id
+               CASE WHEN r.image IS NULL THEN w.id ELSE r.id END id -- image id
        FROM word r
        LEFT JOIN word w ON w.id = r.ref;
 

diff --git a/writer.js b/writer.js
index e9b86fe01ab1e038364c17f79db774a891f6a210..78b104500bce2dabf02a8f7d1aa7bd1b060756fe 100644 (file)
--- a/writer.js
+++ b/writer.js
@@ -102,7 +102,7 @@ document.addEventListener('DOMContentLoaded', () => {
                };
        }
 
-       let thumbpreview = document.getElementById('thumbpreview');
+       let thumbpreview = document.getElementById('convertpreview');
        if (thumbpreview && imgpreview) {
                thumbpreview.onclick = e => {
                        let imgselect = imgpreview; /* TODO clone */
@@ -117,7 +117,7 @@ document.addEventListener('DOMContentLoaded', () => {
                                return pos;
                        };
                        imgselect.onclick = e => {
-                               let imgoption = document.getElementById('thumb');
+                               let imgoption = document.getElementById('convert');
                                imgoption.value += (imgoption.value && '-') + imgselect.onmousemove(e);
                                imgselect.hidden = true;
                                imgselect.classList.remove('popup');

diff --git a/writer.plp b/writer.plp
index c6d5898b77a93870ba9e3f1fbace448b44c13e89..cd2e2bf1d6369529ad52a1186dcb14792ea82f2f 100644 (file)
--- a/writer.plp
+++ b/writer.plp
@@ -12,6 +12,7 @@ EOT
 
 use List::Util qw( pairs pairkeys );
 use Shiar_Sheet::FormRow;
+use JSON;
 
 my $db = eval {
        require Shiar_Sheet::DB;
@@ -99,10 +100,10 @@ my %wordcol = (
        form    => {-label => 'Title'},
        alt     => {-label => 'Synonyms', -multiple => 1},
        wptitle => {-label => 'Wikipedia'},
-       source  => {-label => 'Image', -src => sub {
+       source  => {-label => 'Image', -json => 'image', -src => sub {
                return "data/word/org/$_[0]->{id}.jpg";
        }},
-       thumb   => {-label => 'Convert options', -multiple => 1, -src => sub {
+       convert => {-label => 'Convert options', -json => 'image', -multiple => 1, -src => sub {
                return "data/word/en/$_[0]->{id}.jpg";
        }},
        story   => {-label => 'Story', type => 'textarea', hidden => 'hidden'},
@@ -114,7 +115,7 @@ if (my $search = $fields{q}) {
        say '<h1>Search</h1><ul>';
        printf("<li><small>%s</small> %s %s</li>\n",
                $_->{id}, showlink($_->{form}, "/writer/$_->{id}"),
-               sprintf('<img src="/%s" style="height:3ex; width:auto" />', $wordcol{thumb}->{-src}->($_)) x defined $_->{thumb}
+               sprintf('<img src="/%s" style="height:3ex; width:auto" />', $wordcol{convert}->{-src}->($_)) x defined $_->{image}
        ) for $results->hashes;
        say "</ul>\n";
        exit;
@@ -137,12 +138,22 @@ elsif (defined $post{form}) {{
                return Encode::decode_utf8($_[0]);
        }
 
-       my $replace = $row;
-       $row = {map { $_ =>
-               ref $wordcol{$_} eq 'HASH' && $wordcol{$_}->{-multiple} ?
-                       [ map { parseinput($_) } $post{'@'.$_}->@* ] :
-               scalar parseinput($post{$_})
-       } keys %wordcol};
+       my $replace = $row;  # currently stored
+       $row = {};  # proposed update
+       while (my ($col, $colinfo) = each %wordcol) {
+               ref $colinfo eq 'HASH' or $colinfo = {};
+               my @val = map { parseinput($_) } $post{'@'.$col}->@*;
+               my $val = $colinfo->{-multiple} && @val ? \@val : $val[-1];
+               if (my $jsoncol = $colinfo->{-json}) {
+                       defined $val and
+                       $row->{$jsoncol}->{$col} = $val;  # hash will be encoded
+               }
+               else {
+                       $row->{$col} = $val;
+               }
+       }
+       my $imagecol = $row->{image};  # backup image subcolumns
+       ref $_ eq 'HASH' and $_ = encode_json($_) for values %{$row};
 
        if (!$row->{form}) {
                if ($row->{ref} ne 'delete') {
@@ -191,17 +202,17 @@ elsif (defined $post{form}) {{
        require Shiar_Sheet::ImagePrep;
        my $image = Shiar_Sheet::ImagePrep->new($wordcol{source}->{-src}->($row));
        my $reimage = eval {
-               ($row->{source} // '') ne ($replace->{source} // '') or return;
-               $image->download($row->{source});
+               ($imagecol->{source} // '') ne ($replace->{source} // '') or return;
+               $image->download($imagecol->{source});
        };
        !$@ or Alert(["Source image not found", $@]);
 
-       $reimage ||= $row->{thumb} ~~ $replace->{thumb};  # different convert
+       $reimage ||= $row->{image} ~~ $replace->{image};  # different source
        $reimage ||= $row->{cover} ~~ $replace->{cover};  # resize
        $reimage++ if $fields{rethumb};  # force refresh
        if ($reimage) {
                eval {
-                       $image->convert($wordcol{thumb}->{-src}->($row), $row->{thumb});
+                       $image->convert($wordcol{convert}->{-src}->($row), $imagecol->{convert});
                } or do {
                        my ($warn, @details) = ref $@ ? @{$@} : $@;
                        Alert([ "Thumbnail image not generated", $warn ], @details);
@@ -214,6 +225,14 @@ else {
        $row->{prio} = defined $row->{ref} ? undef : 1 unless exists $row->{prio};
 }
 
+eval {
+       my $imagerow = $row->{image} && decode_json(delete $row->{image}) || {};
+       while (my ($col, $val) = each %{$imagerow}) {
+               $row->{$col} = $val;
+       }
+       1;
+} or Alert("Error decoding image metadata", $@);
+
 my $title = $row->{id} ? "entry <small>#$row->{id}</small>" : 'new entry';
 bless $row, 'Shiar_Sheet::FormRow';
 :>