Cache Markdown-to-HTML in the database for doc pages

Strips about 500ms off the page generation time for d11. The previous
in-memory cache kinda worked, too, but it still often happens that the
cache is empty.

This cache also applies for docs_hist, so browsing through earlier
revisions is now faster as well.

3 files changed, 25 insertions, 2 deletions

diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index f2cc611c..4ac331c0 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -188,14 +188,16 @@ CREATE TABLE docs ( -- dbentry_type=d
   locked     boolean NOT NULL DEFAULT FALSE,
   hidden     boolean NOT NULL DEFAULT FALSE,
   title      varchar(200) NOT NULL DEFAULT '', -- [pub]
-  content    text NOT NULL DEFAULT '' -- [pub]
+  content    text NOT NULL DEFAULT '', -- [pub]
+  html       text -- cache, can be manually updated with util/update-docs-html-cache.pl
 );
 
 -- docs_hist
 CREATE TABLE docs_hist (
   chid       integer  NOT NULL PRIMARY KEY,
   title      varchar(200) NOT NULL DEFAULT '',
-  content    text NOT NULL DEFAULT ''
+  content    text NOT NULL DEFAULT '',
+  html       text -- cache
 );
 
 -- login_throttle
diff --git a/util/update-docs-html-cache.pl b/util/update-docs-html-cache.pl
new file mode 100755
index 00000000..8ef0696e
--- /dev/null
+++ b/util/update-docs-html-cache.pl
@@ -0,0 +1,16 @@
+#!/usr/bin/perl
+
+use v5.24;
+use warnings;
+use DBI;
+use Cwd 'abs_path';
+
+my $ROOT;
+BEGIN { ($ROOT = abs_path $0) =~ s{/util/update-docs-html-cache\.pl$}{}; }
+use lib "$ROOT/lib";
+use VNDB::Func 'md2html';
+
+my $db = DBI->connect('dbi:Pg:dbname=vndb', 'vndb', undef, { RaiseError => 1, AutoCommit => 0 });
+$db->do('UPDATE docs_hist SET html = ? WHERE chid = ?', undef, md2html($_->[1]), $_->[0]) for $db->selectall_array('SELECT chid, content FROM docs_hist');
+$db->do('UPDATE docs      SET html = ? WHERE id   = ?', undef, md2html($_->[1]), $_->[0]) for $db->selectall_array('SELECT id,   content FROM docs'     );
+$db->commit;
diff --git a/util/updates/2020-02-06-docs-html-cache.sql b/util/updates/2020-02-06-docs-html-cache.sql
new file mode 100644
index 00000000..e7b1540e
--- /dev/null
+++ b/util/updates/2020-02-06-docs-html-cache.sql
@@ -0,0 +1,5 @@
+-- Run 'make' before this script
+-- Run 'util/update-docs-html-cache.pl' after this script
+ALTER TABLE docs      ADD COLUMN html text;
+ALTER TABLE docs_hist ADD COLUMN html text;
+\i util/sql/editfunc.sql