word: maintain wordlist in postgres database

A static perl file is insufficient considering future plans.
Start tracking names and image sources for possible collaboration;
the same include syntax is generated from that.

diff --git a/tools/mkwordlist b/tools/mkwordlist
new file mode 100755 (executable)
index 0000000..bec1362
--- /dev/null
+++ b/tools/mkwordlist
@@ -0,0 +1,8 @@
+#!/bin/sh
+psql sheet -XAt -c "
+       SELECT json_object_agg(coalesce(form, ''), forms)
+               FROM word RIGHT JOIN (
+                       SELECT cat id, array_agg(form ORDER BY form) forms
+                               FROM word WHERE ref IS NULL GROUP BY cat
+               ) sub USING (id)
+" | sed 's/:/=>/g'

diff --git a/tools/word.pg.sql b/tools/word.pg.sql
new file mode 100644 (file)
index 0000000..8a5fec6
--- /dev/null
+++ b/tools/word.pg.sql
@@ -0,0 +1,13 @@
+CREATE TABLE word (
+       form       text        NOT NULL,
+       cat        integer              REFERENCES word (id),
+       source     text,
+       thumb      text[],
+       created    timestamptz          DEFAULT now(),
+       id         serial      NOT NULL PRIMARY KEY
+);
+
+COMMENT ON COLUMN word.form       IS 'preferred textual representation';
+COMMENT ON COLUMN word.cat        IS 'hierarchical classification';
+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';

diff --git a/word.plp b/word.plp
index 698dbd6092f3c9ab1a492d1bdbf3cde4bdaa4fcf..0e03f45f0301fa57a738b3f919aa0dec5bbb6c9a 100644 (file)
--- a/word.plp
+++ b/word.plp
@@ -1,5 +1,6 @@
 <(common.inc.plp)><:
 
+my $wordlist = 'wordlist.inc.pl';
 Html({
        title => 'words cheat sheet',
        version => '1.0',
@@ -7,7 +8,7 @@ Html({
        keywords => [qw'
                language
        '],
-       data => ['wordlist.inc.pl'],
+       data => [$wordlist],
        raw => <<'EOT',
 <style>
 ul {
@@ -92,12 +93,12 @@ sub showimg {
 sub printimgs {
        say '<ul>';
        for my $row (@_) {
-               printf '<li%s>', defined $table{$row} && ' class="parent"';
+               printf '<li%s>', defined $table->{$row} && ' class="parent"';
                print showimg($row);
-               printimgs(@{$_}) for $table{$row} // ();
+               printimgs(@{$_}) for $table->{$row} // ();
                print '</li>';
        }
        say '</ul>';
 }
 
-printimgs($table{''});
+printimgs(@{$table->{''}});

diff --git a/wordlist.inc.pl b/wordlist.inc.pl
deleted file mode 100644 (file)
index 8ebfef3..0000000
--- a/wordlist.inc.pl
+++ /dev/null
@@ -1,48 +0,0 @@
-use utf8;
-(
-       '' => 'levensvorm',
-       levensvorm => [qw( dier schimmel plant protist bacterie )],
-       dier => [qw( zoogdier vogel reptiel amfibie vis ongewervelde )],
-       zoogdier => [qw(
-               egel eekhoorn vleermuis wasbeer vos wolf
-               hond kat otter zeehond zeeleeuw walrus walvis dolfijn
-               hert zebra giraf kameel nijlpaard olifant neushoorn tijger leeuw
-               mens aap gorilla koala kangoeroe beer ijsbeer panda
-               knaagdier
-       )],
-       knaagdier => [qw( konijn hamster muis rat )],
-       vogel => [qw(
-               kanarie spreeuw kolibrie zwaluw kraai duif specht valk uil meeuw
-               adelaar pelikaan flamingo ooievaar kraanvogel pinguïn struisvogel
-               gans zwaan pauw fazant kalkoen kaketoe papegaai
-               dinosaurus
-       )],
-       reptiel => [qw(
-               alligator krokodil hagedis leguaan schildpad zeeschildpad slang
-       )],
-       amfibie => [qw( kikker pad kikkerdril salamander )],
-       vis => [qw( aal haai zeepaardje rog goudvis zwaardvis karper )],
-       ongewervelde => [qw( geleedpotige zeester weekdier worm neteldier spons )],
-       neteldier => [qw( kwal koraal )],
-       weekdier => [qw( slak naaktslak inktvis octopus oester mossel )],
-       geleedpotige => [qw( insect spin kreeft duizendpoot )],
-       spin => [qw( schorpioen )],
-       kreeft => [qw( zeekreeft rivierkreeft krab garnaal pissebed )],
-       insect => [qw(
-               mier termiet kakkerlak oorworm bidsprinkhaan sprinkhaan krekel
-               libel vlieg mug vlo kever vlinder mot bij wesp
-       )],
-       vlinder => ['rups'],
-       kever => ['lieveheersbeestje'],
-       plant => [qw( boom cactus bloemplant zaad gras kruid )],
-       boom => [qw(
-               eik wilg populier eucalyptus beuk berk naaldboom/conifeer
-               esdoorn iep linde hulst palm
-       )],
-       'naaldboom/conifeer' => [qw( cipres den ceder spar lariks )],
-       bloemplant => [qw(
-               boterbloem zonnebloem madelief roos paardenbloem
-               tulp vingerhoedskruid kamperfoelie klaver
-               hyacint sleutelbloem lupine hei distel brandnetel
-       )],
-);