summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
Diffstat (limited to 'util')
-rwxr-xr-xutil/dbgraph.pl8
-rw-r--r--util/dump.sql120
-rwxr-xr-xutil/init.pl2
-rwxr-xr-xutil/jsgen.pl97
-rwxr-xr-xutil/lang.pl167
-rwxr-xr-xutil/skingen.pl18
-rw-r--r--util/updates/update_2.8.sql213
-rwxr-xr-xutil/vndb.pl19
8 files changed, 576 insertions, 68 deletions
diff --git a/util/dbgraph.pl b/util/dbgraph.pl
index 167680cd..0b72ceba 100755
--- a/util/dbgraph.pl
+++ b/util/dbgraph.pl
@@ -16,13 +16,13 @@ use warnings;
my %subgraphs = (
- 'Producers' => [qw| FFFFCC producers producers_rev |],
+ 'Producers' => [qw| FFFFCC producers producers_rev producers_relations |],
'Releases' => [qw| C8FFC8 releases releases_rev releases_media releases_platforms releases_producers releases_lang releases_vn |],
- 'Visual Novels' => [qw| FFE6BE vn vn_rev vn_relations vn_categories vn_anime vn_screenshots |],
- 'Users' => [qw| CCFFFF users votes rlists wlists |],
+ 'Visual Novels' => [qw| FFE6BE vn vn_rev vn_relations vn_anime vn_screenshots |],
+ 'Users' => [qw| CCFFFF users votes rlists wlists sessions |],
'Discussion board' => [qw| FFDCDC threads threads_boards threads_posts |],
'Tags' => [qw| FFC8C8 tags tags_aliases tags_parents tags_vn |],
- 'Misc' => [qw| F5F5F5 changes anime screenshots relgraph stats_cache quotes sessions |],
+ 'Misc' => [qw| F5F5F5 changes anime screenshots stats_cache quotes relgraphs |],
);
my %tables; # table_name => [ [ col1, pri ], ... ]
diff --git a/util/dump.sql b/util/dump.sql
index aaa1a5e9..0fdf5696 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -1,15 +1,16 @@
-
--- we don't use PgSQL's OIDS
-SET default_with_oids = false;
-
--- for the functions to work, the following query must
--- be executed on the database by a superuser:
--- CREATE PROCEDURAL LANGUAGE plpgsql
-
+-- plpgsql is required for our (trigger) functions
+CREATE LANGUAGE plpgsql;
+-- data types
+CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
+CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p');
+CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
+CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
+CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial');
+CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
-----------------------------------------
@@ -23,7 +24,7 @@ CREATE TABLE anime (
year smallint,
ann_id integer,
nfo_id varchar(200),
- type smallint,
+ type anime_type,
title_romaji,
title_kanji,
lastfetch timestamptz
@@ -32,7 +33,7 @@ CREATE TABLE anime (
-- changes
CREATE TABLE changes (
id SERIAL NOT NULL PRIMARY KEY,
- type smallint NOT NULL DEFAULT 0,
+ type dbentry_type NOT NULL,
rev integer NOT NULL DEFAULT 1,
added timestamptz NOT NULL DEFAULT NOW(),
requester integer NOT NULL DEFAULT 0,
@@ -46,7 +47,16 @@ CREATE TABLE producers (
id SERIAL NOT NULL PRIMARY KEY,
latest integer NOT NULL DEFAULT 0,
locked boolean NOT NULL DEFAULT FALSE,
- hidden boolean NOT NULL DEFAULT FALSE
+ hidden boolean NOT NULL DEFAULT FALSE,
+ rgraph integer
+);
+
+-- producers_relations
+CREATE TABLE producers_relations (
+ pid1 integer NOT NULL,
+ pid2 integer NOT NULL,
+ relation producer_relation NOT NULL,
+ PRIMARY KEY(pid1, pid2)
);
-- producers_rev
@@ -62,7 +72,6 @@ CREATE TABLE producers_rev (
alias varchar(500) NOT NULL DEFAULT ''
);
-
-- quotes
CREATE TABLE quotes (
vid integer NOT NULL,
@@ -70,7 +79,6 @@ CREATE TABLE quotes (
PRIMARY KEY(vid, quote)
);
-
-- releases
CREATE TABLE releases (
id SERIAL NOT NULL PRIMARY KEY,
@@ -89,7 +97,7 @@ CREATE TABLE releases_lang (
-- releases_media
CREATE TABLE releases_media (
rid integer NOT NULL DEFAULT 0,
- medium character(3) NOT NULL DEFAULT '',
+ medium medium NOT NULL,
qty smallint NOT NULL DEFAULT 1,
PRIMARY KEY(rid, medium, qty)
);
@@ -105,6 +113,9 @@ CREATE TABLE releases_platforms (
CREATE TABLE releases_producers (
rid integer NOT NULL,
pid integer NOT NULL,
+ developer boolean NOT NULL DEFAULT FALSE,
+ publisher boolean NOT NULL DEFAULT TRUE,
+ CHECK(developer OR publisher),
PRIMARY KEY(pid, rid)
);
@@ -114,7 +125,7 @@ CREATE TABLE releases_rev (
rid integer NOT NULL DEFAULT 0,
title varchar(250) NOT NULL DEFAULT '',
original varchar(250) NOT NULL DEFAULT '',
- type smallint NOT NULL DEFAULT 0,
+ type release_type NOT NULL DEFAULT 'complete',
website varchar(250) NOT NULL DEFAULT '',
released integer NOT NULL,
notes text NOT NULL DEFAULT '',
@@ -137,10 +148,10 @@ CREATE TABLE releases_vn (
PRIMARY KEY(rid, vid)
);
--- relgraph
-CREATE TABLE relgraph (
- id SERIAL NOT NULL PRIMARY KEY,
- cmap text NOT NULL DEFAULT ''
+-- relgraphs
+CREATE TABLE relgraphs (
+ id SERIAL PRIMARY KEY,
+ svg xml NOT NULL
);
-- rlists
@@ -245,6 +256,7 @@ CREATE TABLE threads_boards (
tid integer NOT NULL DEFAULT 0,
type character(2) NOT NULL DEFAULT 0,
iid integer NOT NULL DEFAULT 0,
+ lastread smallint NOT NULL,
PRIMARY KEY(tid, type, iid)
);
@@ -292,7 +304,7 @@ CREATE TABLE vn_anime (
CREATE TABLE vn_relations (
vid1 integer NOT NULL DEFAULT 0,
vid2 integer NOT NULL DEFAULT 0,
- relation integer NOT NULL DEFAULT 0,
+ relation vn_relation NOT NULL,
PRIMARY KEY(vid1, vid2)
);
@@ -352,6 +364,9 @@ CREATE TABLE wlists (
ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id);
ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes (id);
ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE producers ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
+ALTER TABLE producers_relations ADD FOREIGN KEY (pid1) REFERENCES producers_rev (id);
+ALTER TABLE producers_relations ADD FOREIGN KEY (pid2) REFERENCES producers (id);
ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id);
ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id);
@@ -380,7 +395,7 @@ ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads
ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id);
ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id);
ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (id);
+ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id);
ALTER TABLE vn_anime ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id);
@@ -405,31 +420,6 @@ ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn
-------------------------
--- update_rev(table, ids) - updates the rev column in the changes table
-CREATE FUNCTION update_rev(tbl text, ids text) RETURNS void AS $$
-DECLARE
- r RECORD;
- r2 RECORD;
- i integer;
- t text;
- e text;
-BEGIN
- SELECT INTO t SUBSTRING(tbl, 1, 1);
- e := '';
- IF ids <> '' THEN
- e := ' WHERE id IN('||ids||')';
- END IF;
- FOR r IN EXECUTE 'SELECT id FROM '||tbl||e LOOP
- i := 1;
- FOR r2 IN EXECUTE 'SELECT id FROM '||tbl||'_rev WHERE '||t||'id = '||r.id||' ORDER BY id ASC' LOOP
- UPDATE changes SET rev = i WHERE id = r2.id;
- i := i+1;
- END LOOP;
- END LOOP;
-END;
-$$ LANGUAGE plpgsql;
-
-
-- update_vncache(id) - updates the c_* columns in the vn table
CREATE FUNCTION update_vncache(id integer) RETURNS void AS $$
DECLARE
@@ -445,7 +435,7 @@ BEGIN
JOIN releases r1 ON rr1.id = r1.latest
JOIN releases_vn rv1 ON rr1.id = rv1.rid
WHERE rv1.vid = vn.id
- AND rr1.type <> 2
+ AND rr1.type <> ''trial''
AND r1.hidden = FALSE
AND rr1.released <> 0
GROUP BY rv1.vid
@@ -457,7 +447,7 @@ BEGIN
JOIN releases r2 ON rr2.id = r2.latest
JOIN releases_vn rv2 ON rr2.id = rv2.rid
WHERE rv2.vid = vn.id
- AND rr2.type <> 2
+ AND rr2.type <> ''trial''
AND rr2.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
AND r2.hidden = FALSE
GROUP BY rl2.lang
@@ -470,7 +460,7 @@ BEGIN
JOIN releases r3 ON rp3.rid = r3.latest
JOIN releases_vn rv3 ON rp3.rid = rv3.rid
WHERE rv3.vid = vn.id
- AND rr3.type <> 2
+ AND rr3.type <> ''trial''
AND rr3.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
AND r3.hidden = FALSE
GROUP BY rp3.platform
@@ -585,7 +575,7 @@ BEGIN
SELECT * FROM tags_vn UNION SELECT * FROM tag_vn_childs();
-- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry
CREATE OR REPLACE TEMPORARY VIEW tags_vn_grouped AS
- SELECT tag, vid, uid, MAX(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler
+ SELECT tag, vid, uid, MAX(vote)::real AS vote, AVG(spoiler)::real AS spoiler
FROM tags_vn_all GROUP BY tag, vid, uid;
-- grouped by (tag, vid) and serialized into a table
DROP INDEX IF EXISTS tags_vn_bayesian_tag;
@@ -781,6 +771,36 @@ $$ LANGUAGE plpgsql;
CREATE CONSTRAINT TRIGGER vn_relgraph_notify AFTER INSERT OR UPDATE ON vn DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify();
+-- Same as above for producers, with slight differences in the steps:
+-- There is no 2, and
+-- 3 = New producer revision of which the name, language or type differs from the previous revision (deferred)
+CREATE OR REPLACE FUNCTION producer_relgraph_notify() RETURNS trigger AS $$
+BEGIN
+ -- 1.
+ IF TG_TABLE_NAME = 'producers' THEN
+ IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM producers_relations WHERE pid1 = NEW.latest) THEN
+ NOTIFY relgraph;
+ END IF;
+ END IF;
+ IF TG_TABLE_NAME = 'producers' AND TG_OP = 'UPDATE' THEN
+ IF NEW.rgraph IS NOT NULL AND OLD.latest > 0 THEN
+ -- 3 & 4
+ IF OLD.latest <> NEW.latest AND (
+ EXISTS(SELECT 1 FROM producers_rev p1, producers_rev p2 WHERE (p2.name <> p1.name OR p2.type <> p1.type OR p2.lang <> p1.lang) AND p1.id = OLD.latest AND p2.id = NEW.latest)
+ OR EXISTS(SELECT p1.pid2, p1.relation FROM producers_relations p1 WHERE p1.pid1 = OLD.latest EXCEPT SELECT p2.pid2, p2.relation FROM producers_relations p2 WHERE p2.pid1 = NEW.latest)
+ OR EXISTS(SELECT p1.pid2, p1.relation FROM producers_relations p1 WHERE p1.pid1 = NEW.latest EXCEPT SELECT p2.pid2, p2.relation FROM producers_relations p2 WHERE p2.pid1 = OLD.latest)
+ ) THEN
+ UPDATE producers SET rgraph = NULL WHERE id = NEW.id;
+ END IF;
+ END IF;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE CONSTRAINT TRIGGER vn_relgraph_notify AFTER INSERT OR UPDATE ON producers DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify();
+
+
-- NOTIFY on insert into changes/posts/tags
CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$
BEGIN
diff --git a/util/init.pl b/util/init.pl
index 5c171705..ac533174 100755
--- a/util/init.pl
+++ b/util/init.pl
@@ -22,7 +22,7 @@ print "\n";
print "Creating directory structures...\n";
-for my $d (qw| cv rg st sf |) {
+for my $d (qw| cv st sf |) {
print " /static/$d\n";
mkdir "$ROOT/static/$d" or die "mkdir '$ROOT/static/$d': $!\n";
for my $i (0..99) {
diff --git a/util/jsgen.pl b/util/jsgen.pl
new file mode 100755
index 00000000..1ad8fb53
--- /dev/null
+++ b/util/jsgen.pl
@@ -0,0 +1,97 @@
+#!/usr/bin/perl
+
+package VNDB;
+
+use strict;
+use warnings;
+use Encode 'encode_utf8';
+use Cwd 'abs_path';
+eval { require JavaScript::Minifier::XS; };
+
+our($ROOT, %S);
+BEGIN { ($ROOT = abs_path $0) =~ s{/util/jsgen\.pl$}{}; }
+require $ROOT.'/data/global.pl';
+
+use lib "$ROOT/lib";
+use lib "$ROOT/yawf/lib";
+use LangFile;
+
+# The VNDB::L10N module is not really suited to be used outside the VNDB::*
+# framework, but it's the central location that defines which languages we have
+# and in what order to display them.
+use VNDB::L10N;
+
+
+my $jskeys_lang = join '|', VNDB::L10N::languages();
+my $jskeys = qr{^(?:
+ _lang_(?:$jskeys_lang)|
+ _js_.+|
+ _menu_emptysearch|
+ _vnpage_uopt_(?:10?vote|rel.+)|
+ _rlst_[vr]stat_.+|
+ _vnedit_rel_(?:isa|of|addbut|del|none|findformat|novn|double)|
+ _redit_form_med_.+|
+ _vnedit_scr_.+|
+ _tagv_(?:add|spoil\d|notfound|nometa|double)|
+ _redit_form_vn_(?:addbut|remove|none|vnformat|notfound|double)|
+ _redit_form_prod_(?:addbut|remove|none|pformat|notfound|double)|
+ _pedit_rel_(?:addbut|del|none|findformat|notfound|double)
+ )$}x;
+
+sub l10n {
+ # Using JSON::XS or something may be shorter and less error prone,
+ # although I would have less power over the output (mostly the quoting of the keys)
+
+ my $lang = LangFile->new(read => "$ROOT/data/lang.txt");
+ my @r;
+ push @r, 'L10N_STR = {';
+ my $cur; # undef = none/excluded, 1 = awaiting first TL line, 2 = after first TL line
+ my %lang;
+ while((my $l = $lang->read())) {
+ my $type = shift @$l;
+ if($type eq 'key') {
+ my $key = shift @$l;
+ push @r, ' }' if $cur;
+ $cur = $key =~ $jskeys ? 1 : undef;
+ if($cur) {
+ $r[$#r] .= ',' if $r[$#r] =~ /}$/;
+ # let's assume key names don't trigger a reserved word in JS
+ $key = qq{"$key"} if $key !~ /^[a-z_][a-z0-9_]*$/i;
+ push @r, qq| $key: {|;
+ }
+ }
+ $lang{$l->[0]} = 1 if $type eq 'tl';
+ if($type eq 'tl' && $cur) {
+ my($lang, $sync, $val) = @$l;
+ next if !$val;
+ $val =~ s/"/\\"/g;
+ $val =~ s/\n/\\n/g;
+ $r[$#r] .= ',' if $cur == 2;
+ $lang = qq{"$l->[0]"} if $lang =~ /^(?:as|do|if|in|is)$/; # reserved two-char words
+ push @r, qq| $lang: "$val"|;
+ $cur = 2;
+ }
+ }
+ push @r, ' }' if $cur;
+ push @r, '};';
+ push @r, 'L10N_LANG = [ '.join(', ', map qq{"$_"}, VNDB::L10N::languages()).' ];';
+ return join "\n", @r;
+}
+
+
+sub jsgen {
+ # JavaScript::Minifier::XS doesn't correctly handle perl's unicode,
+ # so just do everything in raw bytes instead.
+ my $js = encode_utf8(l10n()) . "\n";
+ $js .= sprintf "rlst_rstat = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{rlst_rstat}};
+ $js .= sprintf "rlst_vstat = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{rlst_vstat}};
+ open my $JS, '<', "$ROOT/data/script.js" or die $!;
+ $js .= join '', <$JS>;
+ close $JS;
+ open my $NEWJS, '>', "$ROOT/static/f/script.js" or die $!;
+ print $NEWJS $JavaScript::Minifier::XS::VERSION ? JavaScript::Minifier::XS::minify($js) : $js;
+ close $NEWJS;
+}
+
+jsgen;
+
diff --git a/util/lang.pl b/util/lang.pl
new file mode 100755
index 00000000..087dea2e
--- /dev/null
+++ b/util/lang.pl
@@ -0,0 +1,167 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+
+use Cwd 'abs_path';
+our $ROOT;
+BEGIN { ($ROOT = abs_path $0) =~ s{/util/lang\.pl$}{}; }
+
+use lib $ROOT.'/lib';
+use LangFile;
+
+my $langtxt = "$ROOT/data/lang.txt";
+
+
+sub usage {
+ print <<__;
+$0 stats
+ Prints some stats.
+
+$0 add <lang> [<file>]
+ Adds new (empty) translation lines for language <lang> to <file> (defaults to
+ the global lang.txt) for keys that don't have a TL line yet.
+
+$0 only <lang>[,..] <outfile>
+ Makes a copy of lang.txt to <outfile> and removes all translations except the
+ ones of langauge <lang> (comma-seperated list of tags)
+
+$0 merge <lang> <file>
+ Merges <file> into lang.txt, copying over all the translations of <lang> in
+ <file> while ignoring any other changes. Keys in <file> not present in
+ lang.txt are silently ignored. Keys in lang.txt but not in <file> remain
+ unaffected. Make sure each key in lang.txt already has a line for <lang>,
+ otherwise do an 'add' first.
+
+$0 reorder <lang1>,<lang2>,..
+ Re-orders the translation lines in lang.txt using the specified order.
+
+$0 stage <lang>
+ Puts all changes of <lang> into the git index, and leaves everything else untouched.
+__
+ exit;
+}
+
+
+sub stats {
+ my $r = LangFile->new(read => $langtxt);
+ my $keys = 0;
+ my %lang;
+ while(my $l = $r->read()) {
+ $keys++ if $l->[0] eq 'key';
+ if($l->[0] eq 'tl') {
+ $lang{$l->[1]} ||= [0,0];
+ $lang{$l->[1]}[0]++;
+ $lang{$l->[1]}[1]++ if $l->[2];
+ }
+ }
+ print "lang lines sync unsync\n";
+ printf "%3s %4d (%3d%%) %4d (%3d%%) %4d\n", $_,
+ $lang{$_}[0], $lang{$_}[0]/$keys*100, $lang{$_}[1], $lang{$_}[1]/$keys*100, $keys-$lang{$_}[1]
+ for keys %lang;
+ printf "Total keys: %d\n", $keys;
+}
+
+
+sub add {
+ my($lang, $file) = @_;
+ $file ||= $langtxt;
+ my $r = LangFile->new(read => $file);
+ my $w = LangFile->new(write => "$file~");
+ my $k = 0;
+ while((my $l = $r->read())) {
+ if($k && $l->[0] ne 'tl') {
+ $k = 0;
+ $w->write('tl', $lang, 0, '');
+ }
+ $k = 1 if $l->[0] eq 'key';
+ $k = 0 if $l->[0] eq 'tl' && $l->[1] eq $lang;
+ $w->write(@$l);
+ }
+ $r->close;
+ $w->close;
+ rename "$file~", $file or die $!;
+}
+
+
+sub only {
+ my($lang, $out) = @_;
+ my @lang = split /,/, $lang;
+ my $r = LangFile->new(read => $langtxt);
+ my $w = LangFile->new(write => $out);
+ while((my $l = $r->read())) {
+ $w->write(@$l) unless $l->[0] eq 'tl' && !grep $_ eq $l->[1], @lang;
+ }
+ $r->close;
+ $w->close;
+}
+
+
+sub merge {
+ my($lang, $file) = @_;
+
+ # read all translations in $lang in $file
+ my $trans = LangFile->new(read => $file);
+ my($key, %trans);
+ while((my $l = $trans->read)) {
+ $key = $l->[1] if $l->[0] eq 'key';
+ $trans{$key} = [ $l->[2], $l->[3] ] if $l->[0] eq 'tl' && $l->[1] eq $lang;
+ }
+ $trans->close;
+
+ # now update lang.txt
+ my $r = LangFile->new(read => $langtxt);
+ my $w = LangFile->new(write => "$langtxt~");
+ while((my $l = $r->read)) {
+ $key = $l->[1] if $l->[0] eq 'key';
+ ($l->[2], $l->[3]) = @{$trans{$key}} if $l->[0] eq 'tl' && $l->[1] eq $lang && $trans{$key};
+ $w->write(@$l);
+ }
+ $r->close;
+ $w->close;
+ rename "$langtxt~", $langtxt or die $!;
+}
+
+
+sub reorder {
+ my @lang = split /,/, shift;
+ my $r = LangFile->new(read => $langtxt);
+ my $w = LangFile->new(write => "$langtxt~");
+ my($key, %tl);
+ while((my $l = $r->read)) {
+ if($key && $l->[0] ne 'tl') {
+ $tl{$_} && $w->write(@{delete $tl{$_}}) for(@lang);
+ $w->write(@{$tl{$_}}) for sort keys %tl;
+ $key = undef;
+ %tl = ();
+ }
+ $key = $l->[1] if $l->[0] eq 'key';
+ $tl{$l->[1]} = $l if $l->[0] eq 'tl';
+ $w->write(@$l) unless $l->[0] eq 'tl';
+ }
+ $r->close;
+ $w->close;
+ rename "$langtxt~", $langtxt or die $!;
+}
+
+
+sub stage {
+ my $lang = shift;
+ chdir "$ROOT/data";
+ rename 'lang.txt', '.lang.txt.tmp' or die $!;
+ `git checkout lang.txt`;
+ merge $lang, '.lang.txt.tmp';
+ `git add lang.txt`;
+ rename '.lang.txt.tmp', 'lang.txt';
+}
+
+
+usage if !@ARGV;
+my $act = shift;
+stats if $act eq 'stats';
+add @ARGV if $act eq 'add';
+only @ARGV if $act eq 'only';
+merge @ARGV if $act eq 'merge';
+reorder @ARGV if $act eq 'reorder';
+stage @ARGV if $act eq 'stage';
+
diff --git a/util/skingen.pl b/util/skingen.pl
index e27ea4a1..73e4fdc7 100755
--- a/util/skingen.pl
+++ b/util/skingen.pl
@@ -5,13 +5,12 @@ package VNDB;
use strict;
use warnings;
use Cwd 'abs_path';
-use Data::Dumper 'Dumper';
use Image::Magick;
+eval { require CSS::Minifier::XS };
our($ROOT, %O);
BEGIN { ($ROOT = abs_path $0) =~ s{/util/skingen\.pl$}{}; }
-require $ROOT.'/data/global.pl';
if(@ARGV) {
@@ -78,19 +77,12 @@ sub writeskin { # $obj
# write the CSS
open my $CSS, '<', "$ROOT/data/style.css" or die $!;
+ my $css = join '', <$CSS>;
+ close $CSS;
+ $css =~ s/\$$_\$/$o->{$_}/g for (keys %$o);
open my $SKIN, '>', "$ROOT/static/s/$o->{_name}/style.css" or die $!;
- while((my $d = <$CSS>)) {
- if($O{debug}) {
- chomp $d;
- $d =~ s/^\s*/ /;
- $d =~ s{/\*.+\*/}{}; # NOTE: multiline comments or multiple comments per line won't work
- next if $d !~ /[^\s\t]/;
- }
- $d =~ s/\$$_\$/$o->{$_}/g for (keys %$o);
- print $SKIN $d;
- }
+ print $SKIN $CSS::Minifier::XS::VERSION ? CSS::Minifier::XS::minify($css) : $css;
close $SKIN;
- close $CSS;
}
diff --git a/util/updates/update_2.8.sql b/util/updates/update_2.8.sql
new file mode 100644
index 00000000..d8373210
--- /dev/null
+++ b/util/updates/update_2.8.sql
@@ -0,0 +1,213 @@
+
+-- !BEFORE! running this SQL file, make sure to kill Multi,
+-- After running this SQL file, also make sure to do a:
+-- $ rm -r static/rg/
+-- And start multi again
+
+-- VN Relation graphs are stored in the database as SVG - no cmaps and .png anymore
+UPDATE vn SET rgraph = NULL;
+ALTER TABLE vn DROP CONSTRAINT vn_rgraph_fkey;
+DROP TABLE relgraph;
+CREATE TABLE relgraphs (
+ id SERIAL PRIMARY KEY,
+ svg xml NOT NULL
+);
+ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
+
+
+-- VN relations stored as enum
+CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
+ALTER TABLE vn_relations ALTER COLUMN relation DROP DEFAULT;
+ALTER TABLE vn_relations ALTER COLUMN relation TYPE vn_relation USING
+ CASE
+ WHEN relation = 0 THEN 'seq'::vn_relation
+ WHEN relation = 1 THEN 'preq'
+ WHEN relation = 2 THEN 'set'
+ WHEN relation = 3 THEN 'alt'
+ WHEN relation = 4 THEN 'char'
+ WHEN relation = 5 THEN 'side'
+ WHEN relation = 6 THEN 'par'
+ WHEN relation = 7 THEN 'ser'
+ WHEN relation = 8 THEN 'fan'
+ ELSE 'orig'
+ END;
+
+
+-- producer relations
+CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
+CREATE TABLE producers_relations (
+ pid1 integer NOT NULL REFERENCES producers_rev (id),
+ pid2 integer NOT NULL REFERENCES producers (id),
+ relation producer_relation NOT NULL,
+ PRIMARY KEY(pid1, pid2)
+);
+ALTER TABLE producers ADD COLUMN rgraph integer REFERENCES relgraphs (id);
+
+CREATE OR REPLACE FUNCTION producer_relgraph_notify() RETURNS trigger AS $$
+BEGIN
+ -- 1.
+ IF TG_TABLE_NAME = 'producers' THEN
+ IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM producers_relations WHERE pid1 = NEW.latest) THEN
+ NOTIFY relgraph;
+ END IF;
+ END IF;
+ IF TG_TABLE_NAME = 'producers' AND TG_OP = 'UPDATE' THEN
+ IF NEW.rgraph IS NOT NULL AND OLD.latest > 0 THEN
+ -- 3 & 4
+ IF OLD.latest <> NEW.latest AND (
+ EXISTS(SELECT 1 FROM producers_rev p1, producers_rev p2 WHERE (p2.name <> p1.name OR p2.type <> p1.type OR p2.lang <> p1.lang) AND p1.id = OLD.latest AND p2.id = NEW.latest)
+ OR EXISTS(SELECT p1.pid2, p1.relation FROM producers_relations p1 WHERE p1.pid1 = OLD.latest EXCEPT SELECT p2.pid2, p2.relation FROM producers_relations p2 WHERE p2.pid1 = NEW.latest)
+ OR EXISTS(SELECT p1.pid2, p1.relation FROM producers_relations p1 WHERE p1.pid1 = NEW.latest EXCEPT SELECT p2.pid2, p2.relation FROM producers_relations p2 WHERE p2.pid1 = OLD.latest)
+ ) THEN
+ UPDATE producers SET rgraph = NULL WHERE id = NEW.id;
+ END IF;
+ END IF;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+CREATE CONSTRAINT TRIGGER vn_relgraph_notify AFTER INSERT OR UPDATE ON producers DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify();
+
+
+-- Anime types stored as enum
+CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
+ALTER TABLE anime ALTER COLUMN type TYPE anime_type USING
+ CASE
+ WHEN type = 0 THEN 'tv'::anime_type
+ WHEN type = 1 THEN 'ova'
+ WHEN type = 2 THEN 'mov'
+ WHEN type = 3 THEN 'oth'
+ WHEN type = 4 THEN 'web'
+ WHEN type = 5 THEN 'spe'
+ WHEN type = 6 THEN 'mv'
+ ELSE NULL
+ END;
+
+
+-- Release media stored as enum
+CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
+ALTER TABLE releases_media ALTER COLUMN medium DROP DEFAULT;
+ALTER TABLE releases_media ALTER COLUMN medium TYPE medium USING TRIM(both ' ' from medium)::medium;
+
+
+-- Differentiate between publishers and developers
+ALTER TABLE releases_producers ADD COLUMN developer boolean NOT NULL DEFAULT FALSE;
+ALTER TABLE releases_producers ADD COLUMN publisher boolean NOT NULL DEFAULT TRUE;
+ALTER TABLE releases_producers ADD CHECK(developer OR publisher);
+
+
+-- Keep track of last read post for PMs
+ALTER TABLE threads_boards ADD COLUMN lastread smallint;
+
+
+-- changes.type stored as enum
+CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p');
+ALTER TABLE changes ALTER COLUMN type DROP DEFAULT;
+ALTER TABLE changes ALTER COLUMN type TYPE dbentry_type USING
+ CASE
+ WHEN type = 0 THEN 'v'::dbentry_type
+ WHEN type = 1 THEN 'r'
+ WHEN type = 2 THEN 'p'
+ ELSE NULL -- not allowed to happen, otherwise FIX YOUR DATABASE!
+ END;
+
+
+-- releases_rev.type stored as enum
+CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial');
+ALTER TABLE releases_rev ALTER COLUMN type DROP DEFAULT;
+ALTER TABLE releases_rev ALTER COLUMN type TYPE release_type USING
+ CASE
+ WHEN type = 0 THEN 'complete'::release_type
+ WHEN type = 1 THEN 'partial'
+ WHEN type = 2 THEN 'trial'
+ ELSE NULL
+ END;
+ALTER TABLE releases_rev ALTER COLUMN type SET DEFAULT 'complete';
+
+CREATE OR REPLACE FUNCTION update_vncache(id integer) RETURNS void AS $$
+DECLARE
+ w text := '';
+BEGIN
+ IF id > 0 THEN
+ w := ' WHERE id = '||id;
+ END IF;
+ EXECUTE 'UPDATE vn SET
+ c_released = COALESCE((SELECT
+ MIN(rr1.released)
+ FROM releases_rev rr1
+ JOIN releases r1 ON rr1.id = r1.latest
+ JOIN releases_vn rv1 ON rr1.id = rv1.rid
+ WHERE rv1.vid = vn.id
+ AND rr1.type <> ''trial''
+ AND r1.hidden = FALSE
+ AND rr1.released <> 0
+ GROUP BY rv1.vid
+ ), 0),
+ c_languages = COALESCE(ARRAY_TO_STRING(ARRAY(
+ SELECT rl2.lang
+ FROM releases_rev rr2
+ JOIN releases_lang rl2 ON rl2.rid = rr2.id
+ JOIN releases r2 ON rr2.id = r2.latest
+ JOIN releases_vn rv2 ON rr2.id = rv2.rid
+ WHERE rv2.vid = vn.id
+ AND rr2.type <> ''trial''
+ AND rr2.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
+ AND r2.hidden = FALSE
+ GROUP BY rl2.lang
+ ORDER BY rl2.lang
+ ), ''/''), ''''),
+ c_platforms = COALESCE(ARRAY_TO_STRING(ARRAY(
+ SELECT rp3.platform
+ FROM releases_platforms rp3
+ JOIN releases_rev rr3 ON rp3.rid = rr3.id
+ JOIN releases r3 ON rp3.rid = r3.latest
+ JOIN releases_vn rv3 ON rp3.rid = rv3.rid
+ WHERE rv3.vid = vn.id
+ AND rr3.type <> ''trial''
+ AND rr3.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
+ AND r3.hidden = FALSE
+ GROUP BY rp3.platform
+ ORDER BY rp3.platform
+ ), ''/''), '''')
+ '||w;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+-- fix calculation of the tags_vn_bayesian.spoiler column
+
+CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
+BEGIN
+ -- all votes for all tags
+ CREATE OR REPLACE TEMPORARY VIEW tags_vn_all AS
+ SELECT * FROM tags_vn UNION SELECT * FROM tag_vn_childs();
+ -- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry
+ CREATE OR REPLACE TEMPORARY VIEW tags_vn_grouped AS
+ SELECT tag, vid, uid, MAX(vote)::real AS vote, AVG(spoiler)::real AS spoiler
+ FROM tags_vn_all GROUP BY tag, vid, uid;
+ -- grouped by (tag, vid) and serialized into a table
+ DROP INDEX IF EXISTS tags_vn_bayesian_tag;
+ TRUNCATE tags_vn_bayesian;
+ INSERT INTO tags_vn_bayesian
+ SELECT tag, vid, COUNT(uid) AS users, AVG(vote)::real AS rating,
+ (CASE WHEN AVG(spoiler) < 0.7 THEN 0 WHEN AVG(spoiler) > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler
+ FROM tags_vn_grouped
+ GROUP BY tag, vid
+ HAVING AVG(vote) > 0;
+ CREATE INDEX tags_vn_bayesian_tag ON tags_vn_bayesian (tag);
+ -- now perform the bayesian ranking calculation
+ UPDATE tags_vn_bayesian tvs SET rating =
+ ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users*rating)
+ / ((SELECT AVG(users)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users)::real;
+ -- and update the VN count in the tags table as well
+ UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_bayesian WHERE tag = id);
+ RETURN;
+END;
+$$ LANGUAGE plpgsql;
+SELECT tag_vn_calc();
+
+
+-- remove update_rev()
+DROP FUNCTION update_rev(text, text);
+
diff --git a/util/vndb.pl b/util/vndb.pl
index ed74a92f..4752c274 100755
--- a/util/vndb.pl
+++ b/util/vndb.pl
@@ -28,6 +28,10 @@ our(%O, %S);
$S{skins} = readskins();
+# automatically regenerate script.js when required and possible
+checkjs();
+
+
# load lang.dat
VNDB::L10N::loadfile();
@@ -113,3 +117,18 @@ sub readskins {
return \%skins;
}
+
+sub checkjs {
+ my $script = "$ROOT/static/f/script.js";
+ my $lastmod = [stat $script]->[9];
+ system "$ROOT/util/jsgen.pl" if
+ (!-e $script && -x "$ROOT/static/f")
+ || (-e $script && -w $script && (
+ $lastmod < [stat "$ROOT/data/script.js"]->[9]
+ || $lastmod < [stat "$ROOT/data/lang.txt"]->[9]
+ || (-e "$ROOT/data/config.pl" && $lastmod < [stat "$ROOT/data/config.pl"]->[9])
+ || $lastmod < [stat "$ROOT/data/global.pl"]->[9]
+ || $lastmod < [stat "$ROOT/util/jsgen.pl"]->[9]
+ ));
+}
+