From: Mischa POSLAWSKY <perl@shiar.org>
Date: Mon, 25 May 2020 03:25:31 +0000 (+0200)
Subject: word: maintain wordlist in postgres database
X-Git-Tag: v1.13~266
X-Git-Url: http://git.shiar.net/sheet.git/commitdiff_plain/89052f1b0f9c404f8c4a5183276fdbcfbbf38b80

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
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
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 698dbd6..0e03f45 100644
--- 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
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
-	)],
-);