v2rw: Convert doc pages + add framework for item fetching & display & revisions

This bumps the minimum Perl version to 5.26 in order to make use of
lexical subroutines - a feature I've been wanting for a while. This
should be the last version bump, 5.26 is the highest version in Ubuntu
LTS at the moment. Not that I use Ubuntu, but it's used by the Docker
container and it's a sensible reference.

I merged the 'maintabs' and 'hiddenmsg' features into the primary
framework_ function; It fits quite well there, removes a little bit
of boilerplate from the DB entry page code and reduces the reliance on
common "dbSomethingGet()" methods.

I was hoping I'd be able to reduce the boilerplate required for defining
revisions, but I don't think that's going to happen. What I did do was
reimplement the diffing to handle item and text diffs separately, with
sensible defaults for the old split/join/diff options. Diffing is now
performed on the raw structured data rather than on formatted HTML,
which, combined with the db_entry() functions, ought to be less brittle.

1 files changed, 84 insertions, 0 deletions

diff --git a/lib/VNWeb/DB.pm b/lib/VNWeb/DB.pm
index 58a972a8..b839baf3 100644
--- a/lib/VNWeb/DB.pm
+++ b/lib/VNWeb/DB.pm
@@ -6,11 +6,13 @@ use TUWF;
 use SQL::Interp ':all';
 use Carp 'carp';
 use Exporter 'import';
+use VNDB::Schema;
 
 our @EXPORT = qw/
     sql
     sql_join sql_comma sql_and sql_array sql_func sql_fromhex sql_tohex sql_fromtime sql_totime
     enrich enrich_merge enrich_flatten
+    db_entry
 /;
 
 
@@ -177,4 +179,86 @@ sub enrich_flatten {
 }
 
 
+
+# Database entry API: Intended to provide a low-level read/write interface for
+# versioned database entires. The same data structure is used for reading and
+# updating entries, and should support easy diffing/comparison.
+# Not very convenient for general querying & searching, those still need custom
+# queries.
+
+
+# Hash table, something like:
+# {
+#   v => {
+#       prefix => 'vn',
+#       base => { .. 'vn_hist' schema }
+#       tables => {
+#           anime => { .. 'vn_anime_hist' schema }
+#       },
+#   }, ..
+# }
+my $entry_types = do {
+    my $schema = VNDB::Schema::schema;
+    my %types = map +($_->{dbentry_type}, { prefix => $_->{name} }), grep $_->{dbentry_type}, values %$schema;
+    for my $t (values %$schema) {
+        my $n = $t->{name};
+        my($type) = grep $n =~ s/^$_->{prefix}_//, values %types;
+        next if !$type;
+        $type->{base} = $t if $n eq 'hist';
+        next if $n !~ s/_hist$//;
+        $type->{tables}{$n} = $t;
+    }
+    \%types;
+};
+
+
+# Returns everything for a specific entry ID. The top-level hash also includes
+# the following keys:
+#
+#   id, chid, rev, maxrev, hidden, locked, entry_hidden, entry_locked
+#
+# (Ordering of arrays is unspecified)
+#
+# TODO:
+# - Use non _hist tables if $maxrev == $rev (should be faster)
+# - Combine the enrich_merge() calls into a single query.
+sub db_entry {
+    my($type, $id, $rev) = @_;
+    my $t = $entry_types->{$type}||die;
+
+    my $maxrev = tuwf->dbVali('SELECT MAX(rev) FROM changes WHERE type =', \$type, ' AND itemid =', \$id);
+    return undef if !$maxrev;
+    $rev ||= $maxrev;
+    my $entry = tuwf->dbRowi(q{
+        SELECT itemid AS id, id AS chid, rev AS chrev, ihid AS hidden, ilock AS locked
+          FROM changes
+         WHERE}, { type => $type, itemid => $id, rev => $rev }
+    );
+    return undef if !$entry->{id};
+    $entry->{maxrev} = $maxrev;
+
+    if($maxrev == $rev) {
+        $entry->{entry_hidden} = $entry->{hidden};
+        $entry->{entry_locked} = $entry->{locked};
+    } else {
+        my $base = $t->{base}{name} =~ s/_hist$//r;
+        enrich_merge id => "SELECT id, hidden AS entry_hidden, locked AS entry_locked FROM \"$base\" WHERE id IN", $entry;
+    }
+
+    enrich_merge chid => sql(
+        SELECT => sql_comma(map "\"$_->{name}\"", $t->{base}{cols}->@*),
+        FROM => "\"$t->{base}{name}\"",
+        'WHERE chid IN'
+    ), $entry;
+
+    while(my($name, $tbl) = each $t->{tables}->%*) {
+        $entry->{$name} = tuwf->dbAlli(
+            SELECT => sql_comma(map "\"$_->{name}\"", grep $_->{name} ne 'chid', $tbl->{cols}->@*),
+            FROM => "\"$tbl->{name}\"",
+            WHERE => { chid => $entry->{chid} }
+        );
+    }
+    $entry
+}
+
 1;