Stupid comments - like I can keep track of everything I change...

git-svn-id: svn://vndb.org/vndb@5 1fe2e327-d9db-4752-bcf7-ef0cb4a1748b

5 files changed, 491 insertions, 0 deletions

diff --git a/util/OLD/cleanimg.pl b/util/OLD/cleanimg.pl
new file mode 100644
index 00000000..45032106
--- /dev/null
+++ b/util/OLD/cleanimg.pl
@@ -0,0 +1,111 @@
+#!/usr/bin/perl
+
+
+
+#
+#       O L D   -   D O   N O T   U S E !
+#
+
+
+use strict;
+use warnings;
+use Time::HiRes 'gettimeofday', 'tv_interval';
+BEGIN {
+  our $ST = [ gettimeofday ];
+}
+use DBI;
+use Image::Magick;
+use Image::MetaData::JPEG;
+use File::Copy 'cp', 'mv';
+use Digest::MD5;
+
+require '/www/vndb/lib/global.pl';
+
+our $ST;
+
+my $sql = DBI->connect(@VNDB::DBLOGIN,
+    { RaiseError => 1, PrintError => 0, AutoCommit => 1, pg_enable_utf8 => 1 });
+
+my $imgpath = '/www/vndb/static/img';
+my $tmpimg = '/tmp/vndb-clearimg.jpg';
+
+imgscan();
+
+printf "Finished in %.3f seconds\n", tv_interval($ST);
+
+sub imgscan {
+  print "Scanning images...\n";
+  my $done = 0;
+  for my $c ('0'..'9', 'a'..'f') {
+    opendir(my $D, "$imgpath/$c") || die "$imgpath/$c: $!";
+    for my $f (readdir($D)) {
+      my $cur = "$imgpath/$c/$f";
+      next if !-s $cur || $f !~ /^(.+)\.jpg$/;
+      my $cmd5 = $1;
+
+     # delete unused images
+      if($f =~ /^tmp/ || $f =~ /\.jpg\.jpg$/) {
+        printf "Deleting temp image %s/%s\n", $c, $f;
+        unlink $cur or die $!;
+        next;
+      }
+      my $q = $sql->prepare('SELECT 1 FROM vn_rev WHERE image = DECODE(?, \'hex\')');
+      $q->execute($cmd5);
+      my $d = $q->fetchrow_arrayref();
+      if(!$d || ref($d) ne 'ARRAY' || $d->[0] <= 0) {
+        printf "Deleting %s/%s\n", $c, $f;
+        unlink $cur or die $!;
+        $done++;
+        next;
+      }
+      $q->finish();
+
+     # remove metadata
+      my $i = Image::MetaData::JPEG->new($cur);
+      $i->drop_segments('METADATA');
+      $i->save($tmpimg);
+      if(-s $tmpimg < (-s $cur)-32) {
+        printf "Removed metadata from %s/%s: %.2f to %.2f kB\n", $c, $f, (-s $cur)/1024, (-s $tmpimg)/1024;
+        cp $tmpimg, $cur;
+      }
+
+     # compress large images
+      if(-s $cur > 20*1024) { # > 20 KB
+        $i = Image::Magick->new;
+        $i->Read($cur);
+        $i->Set(quality => 80);
+        $i->Write($tmpimg);
+        undef $i;
+        #if(-s $tmpimg > 35*1024) { # extremely large images get a quality of 65
+        #  $i = Image::Magick->new;
+        #  $i->Read($cur);
+        #  $i->Set(quality => 65);
+        #  $i->Write($tmpimg);
+        #  undef $i;
+        #}
+        if(-s $tmpimg < (-s $cur)-1024) {
+          printf "Compressed %s/%s from %.2f to %.2f kB\n", $c, $f, (-s $cur)/1024, (-s $tmpimg)/1024;
+          cp $tmpimg, $cur or die $!;
+          $done++;
+        }
+      }
+
+     # rename file if MD5 is different
+      open(my $T, '<:raw:bytes', $cur) || die $!;
+      my $md5 = Digest::MD5->new()->addfile($T)->hexdigest;
+      close($T);
+      if($md5 ne $cmd5) {
+        $sql->do('UPDATE vn_rev SET image = DECODE(?, \'hex\') WHERE image = DECODE(?, \'hex\')', undef, $md5, $cmd5);
+        mv $cur, sprintf "%s/%s/%s.jpg", $imgpath, substr($md5, 0, 1), $md5 or die $!;
+        printf "Renamed %s/%s to %s/%s\n", $c, $cmd5, substr($md5, 0, 1), $md5;
+      }
+    }
+    closedir($D);
+  }
+  unlink $tmpimg;
+  print "Everything seems to be ok\n" if !$done;
+}
+
+
+
+1;
diff --git a/util/OLD/cron_daily.sh b/util/OLD/cron_daily.sh
new file mode 100755
index 00000000..743a9839
--- /dev/null
+++ b/util/OLD/cron_daily.sh
@@ -0,0 +1,31 @@
+#!/bin/bash
+
+# we want to run as user 'yorhel'
+if [ `id -nu` != 'yorhel' ]; then
+  su yorhel -c "$0"
+  exit;
+fi
+
+cd /www/vndb/util
+
+SQL='psql -e vndb -U vndb';
+
+echo '
+
+   =================================================================================
+=================== VNDB cron running at '`date`' ==================
+=== Executing SQL statements'
+echo '\timing
+\i cron_daily.sql' | $SQL
+
+echo '=== Creating/updating sitemap';
+./sitemap.pl
+#echo '=== Cleaning up images';
+#./cleanimg.pl
+#echo '=== Creating relation graphs';
+#./relgraph.pl
+echo '=== VACUUM FULL ANALYZE';
+vacuumdb -U yorhel --full --analyze vndb  >/dev/null 2>&1
+
+echo '=== VNDB cron finished at '`date`' ===';
+
diff --git a/util/OLD/cron_daily.sql b/util/OLD/cron_daily.sql
new file mode 100644
index 00000000..c30f30f1
--- /dev/null
+++ b/util/OLD/cron_daily.sql
@@ -0,0 +1,15 @@
+-- update c_* columns in vn
+SELECT update_vncache(0), calculate_rating();
+
+-- update changes.prev columns
+SELECT update_prev('vn', ''), update_prev('releases', ''), update_prev('producers', '');
+
+-- check...
+ SELECT 'r', id FROM releases_rev rr
+  WHERE NOT EXISTS(SELECT 1 FROM releases_vn rv WHERE rr.id = rv.rid)
+UNION
+ SELECT c.type::varchar, id FROM changes c
+  WHERE (c.type = 0 AND NOT EXISTS(SELECT 1 FROM vn_rev vr WHERE vr.id = c.id))
+     OR (c.type = 1 AND NOT EXISTS(SELECT 1 FROM releases_rev rr WHERE rr.id = c.id))
+     OR (c.type = 2 AND NOT EXISTS(SELECT 1 FROM producers_rev pr WHERE pr.id = c.id));
+
diff --git a/util/OLD/relgraph.pl b/util/OLD/relgraph.pl
new file mode 100644
index 00000000..a4ae486d
--- /dev/null
+++ b/util/OLD/relgraph.pl
@@ -0,0 +1,237 @@
+#!/usr/bin/perl
+
+our $S;
+
+open(STDERR, ">&STDOUT"); # warnings and errors can be captured easily this way
+$ENV{PATH} = '/usr/bin';  # required for GraphViz
+
+use strict;
+use warnings;
+no warnings 'once';
+use Text::Unidecode;
+#use Time::HiRes 'gettimeofday', 'tv_interval';
+#BEGIN { $S = [ gettimeofday ]; }
+#END   { printf "Done in %.2f s\n", tv_interval($S); }
+
+use Digest::MD5 'md5_hex';
+use Time::CTime;
+use GraphViz;
+use DBI;
+use POSIX 'floor';
+
+require '/www/vndb/lib/global.pl';
+
+
+my $font = 's'; #Comic Sans MSssss';
+my @fsize = ( 9, 7, 10 ); # nodes, edges, node_title
+my $destdir = '/www/vndb/static/rg';
+my $datdir = '/www/vndb/data/rg';
+my $DEBUG = 0;
+
+
+my %nodes_all = (
+  fontname => $font,
+  shape => 'plaintext',
+  fontsize => $fsize[0],
+  style => "setlinewidth(0.5)",
+);
+
+my %edge_all = (
+  labeldistance => 2.5,
+  labelangle => -20,
+  labeljust => 'l',
+  dir => 'both',
+  minlen => 2,
+  fontname => $font,
+  fontsize => $fsize[1],
+  arrowsize => 0.7,
+  color => '#69a89a',
+#  constraint => 0,
+);
+
+my @edge_rel = map {
+  {
+    %edge_all,
+    $VNDB::VRELW->{$_} ? (
+      headlabel => $VNDB::VREL->[$_],
+      taillabel => $VNDB::VREL->[$_-1],
+    ) : $VNDB::VRELW->{$_+1} ? (
+      headlabel => $VNDB::VREL->[$_],
+      taillabel => $VNDB::VREL->[$_+1],
+    ) : (
+      label => ' '.$VNDB::VREL->[$_],
+    ),
+  };
+} 0..$#$VNDB::VREL;
+
+
+
+
+my $sql = DBI->connect(@VNDB::DBLOGIN, { RaiseError => 1, PrintError => 0, AutoCommit => 0, pg_enable_utf8 => 1 });
+my %ids; my %nodes;
+my %rels; # "v1-v2" => 1
+my @done;
+
+
+
+sub createGraph { # vid
+  my $id = shift;
+  %ids = ();
+  %nodes = ();
+  %rels = ();
+
+  return 0 if grep { $id == $_ } @done;
+
+  my $g = GraphViz->new(
+#    width => 700/96,
+    height => 2000/96,
+    ratio => 'compress',
+  );
+
+  getRel($g, $id);
+  if(!keys %rels) {
+    push @done, $id;
+    $sql->do(q|UPDATE vn SET rgraph = 0 WHERE id = ?|, undef, $id);
+    return 0;
+  }
+  
+ # correct order!
+  for (sort { $a->[2] <=> $b->[2] } values %nodes) {
+    $DEBUG && printf "ADD: %d\n", $_->[0];
+    $_->[2] =~ s#^([0-9]{4})([0-9]{2}).+#$1==0?'N/A':$1==9999?'TBA':(($2&&$2>0?($Time::CTime::MoY[$2-1].' '):'').$1)#e;
+    $g->add_node($_->[0], %nodes_all, URL => '/v'.$_->[0], tooltip => $_->[1], label => sprintf
+      qq|<<TABLE CELLSPACING="0" CELLPADDING="1" BORDER="0" CELLBORDER="1" BGCOLOR="#f0f0f0">
+         <TR><TD COLSPAN="2" ALIGN="CENTER" CELLPADDING="3"><FONT POINT-SIZE="$fsize[2]">  %s  </FONT></TD></TR>
+         <TR><TD> %s </TD><TD> %s </TD></TR>
+      </TABLE>>|,
+      $_->[1], $_->[2], $_->[3]);
+  }
+
+ # make sure to sort the edges on node release dates
+  my @rel = map { [ split(/-/, $_), $rels{$_} ] } keys %rels;
+  for (sort { ($ids{$a->[0]} > $ids{$a->[1]} ? $ids{$a->[1]} : $ids{$a->[0]})
+          cmp ($ids{$b->[0]} > $ids{$b->[1]} ? $ids{$b->[1]} : $ids{$b->[0]}) } @rel) {
+
+   # [older game] -> [newer game]
+    if($ids{$_->[1]} > $ids{$_->[0]}) {
+      ($_->[0], $_->[1]) = ($_->[1], $_->[0]);
+      $_->[2] = reverseRel($_->[2]);
+    }
+    $g->add_edge($_->[1] => $_->[0], %{$edge_rel[$_->[2]]});
+    $DEBUG && printf "ADD %d -> %d\n", $_->[1], $_->[0];
+  }
+
+  $DEBUG && print "IMAGE\n";
+
+ # get a new number
+  my $gid = $sql->prepare("SELECT nextval('relgraph_seq')");
+  $gid->execute;
+  $gid = $gid->fetchrow_arrayref->[0];
+  my $fn = sprintf '/%02d/%d.', $gid % 50, $gid;
+
+ # save the image & image map
+  my $d = $g->as_gif($destdir.$fn.'gif');
+  chmod 0666, $destdir.$fn.'gif';
+
+  $DEBUG && print "CMAP\n";
+  open my $F, '>', $datdir.$fn.'cmap' or die $!;
+  print $F '<!-- V:'.join(',',keys %nodes)." -->\n";
+  ($d = $g->as_cmapx) =~ s/(id|name)="[^"]+"/$1="rgraph"/g;
+  print $F $d;
+  close $F;
+  chmod 0666, $datdir.$fn.'cmap';
+
+  $DEBUG && print "UPDATE\n";
+ # update the VNs
+  $sql->do(sprintf q|
+    UPDATE vn
+      SET rgraph = %d
+      WHERE id IN(%s)|,
+    $gid, join(',', keys %ids));
+  $DEBUG && print "FIN\n";
+
+  push @done, keys %ids;
+  return 1;
+}
+
+
+sub getRel { # gobj, vid
+  my($g, $id) = @_;
+  #$ids{$id} = 0; # false but defined
+  $DEBUG && printf "GET: %d\n", $id;
+  my $s = $sql->prepare(q|
+    SELECT vr1.vid AS vid1, r.vid2, r.relation, vr1.title AS title1, vr2.title AS title2,
+      v1.c_released AS date1, v2.c_released AS date2, v1.c_languages AS lang1, v2.c_languages AS lang2
+    FROM vn_relations r
+    JOIN vn_rev vr1 ON r.vid1 = vr1.id
+    JOIN vn v1 ON v1.latest = vr1.id
+    JOIN vn v2 ON r.vid2 = v2.id
+    JOIN vn_rev vr2 ON v2.latest = vr2.id
+    WHERE (r.vid2 = ? OR vr1.vid = ?)|
+  );
+  $s->execute($id, $id);
+  for my $r (@{$s->fetchall_arrayref({})}) {
+    $DEBUG && printf "  %d: %d - %d\n", $id, $r->{vid1}, $r->{vid2};
+    if($r->{vid1} < $r->{vid2}) {
+      $rels{$r->{vid1}.'-'.$r->{vid2}} = reverseRel($r->{relation});
+    } else {
+      $rels{$r->{vid2}.'-'.$r->{vid1}} = $r->{relation};
+    }
+
+    for (1,2) {
+      my($cid, $title, $date, $lang) = ($r->{'vid'.$_}, $r->{'title'.$_}, $r->{'date'.$_}, $r->{'lang'.$_});
+      $title = unidecode($title);
+      $title = substr($title, 0, 27).'...' if length($title) > 30;
+      $title =~ s/&/&amp;/g;
+      $date = sprintf('%08d', $date);
+      $nodes{$cid} = [ $cid, $title, $date, $lang ];
+
+      if(!defined $ids{$cid}) {
+        $ids{$cid} = $date;
+        getRel($g, $cid) if $id != $cid;
+      }
+    }
+  }
+}
+
+sub reverseRel { # rel
+  return $VNDB::VRELW->{$_[0]} ? $_[0]-1 : $VNDB::VRELW->{$_[0]+1} ? $_[0]+1 : $_[0];
+}
+
+
+
+if(@ARGV) {
+  #print join('-',@ARGV);
+  createGraph($_) for (@ARGV);
+  $sql->commit;
+} else {
+  require Time::HiRes;
+  my $S = [ Time::HiRes::gettimeofday() ];
+
+ # regenerate all
+  my $s = $sql->prepare(q|SELECT id FROM vn|);
+  $s->execute();
+  my $i = $s->fetchall_arrayref([]);
+  for my $id (@$i) {
+    print "Processed $id->[0]\n" if createGraph($id->[0]);
+  }
+
+ # delete unused
+ # opendir(my $D, $destdir) || die $!;
+ # for (readdir($D)) {
+ #   next if !/^([0-9a-fA-F]{32})\.gif$/;
+ #   my $s = $sql->prepare(q|SELECT 1 AS yes FROM vn WHERE rgraph = DECODE(?, 'hex')|);
+ #   $s->execute($1);
+ #   if(!$s->fetchall_arrayref({})->[0]{yes}) {
+ #     printf "Deleting %s\n", $1;
+ #     unlink "$datdir/$1.cmap" or die $!;
+ #     unlink "$destdir/$1.gif" or die $!;
+ #   }
+ # }
+ # closedir($D);
+
+  $sql->commit;
+
+  printf "Done in %.3f s\n", Time::HiRes::tv_interval($S);
+}
+
diff --git a/util/OLD/sitemap.pl b/util/OLD/sitemap.pl
new file mode 100644
index 00000000..6fde2f80
--- /dev/null
+++ b/util/OLD/sitemap.pl
@@ -0,0 +1,97 @@
+#!/usr/bin/perl
+
+my $sitemapfile = '/www/vndb/www/sitemap.xml.gz';
+my $baseurl = 'http://vndb.org';
+my %chfr = qw( a always   h hourly  d daily  w weekly  m monthly  y yearly  n never );
+
+
+# the code
+use strict;
+use warnings;
+no warnings 'once';
+use DBI;
+use POSIX; # for ceil();
+use XML::Writer;
+use PerlIO::gzip;
+use DateTime;
+
+require '/www/vndb/lib/global.pl';
+
+my $sql = DBI->connect(@VNDB::DBLOGIN,
+    { RaiseError => 1, PrintError => 0, AutoCommit => 1, pg_enable_utf8 => 1 });
+
+my $urls = 0;
+my $x;
+
+sitemap();
+
+sub sitemap {
+  print "Creating sitemap...\n";
+ # open file and start writing
+  open(my $IO, '>:gzip', $sitemapfile) || die $1;
+  $x = new XML::Writer(OUTPUT => $IO, ENCODING => 'UTF-8', DATA_MODE => 1, DATA_INDENT => 1);
+  $x->xmlDecl();
+  $x->comment(q|NOTE: All URL's that require you to login or that may contain usernames are left out.|);
+  $x->startTag('urlset', xmlns => 'http://www.sitemaps.org/schemas/sitemap/0.9');
+
+ # some default pages
+  _sm_add(@$_) foreach (
+    [ '/', 'd' ],
+    [ '/faq', 'm' ],
+  );
+
+ # some browse pages
+  _sm_add('/v/'.$_, 'w') for ('a'..'z', 'all', 'cat');
+  _sm_add('/p/'.$_, 'w') for ('a'..'z', 'all');
+
+ # visual novels
+  my $q = $sql->prepare(q|
+    SELECT v.id, c.added, v.rgraph
+    FROM vn v
+    JOIN vn_rev vr ON vr.id = v.latest
+    JOIN changes c ON vr.id = c.id
+  |); $q->execute;
+  while($_ = $q->fetchrow_arrayref) {
+    _sm_add('/v'.$_->[0], 'w', $_->[1], 0.7);
+#    _sm_add('/v'.$_->[0].'/stats', 'w');
+    _sm_add('/v'.$_->[0].'/rg', 'w', $_->[1]) if $_->[2];
+  }
+
+ # producers
+  $q = $sql->prepare(q|
+    SELECT p.id, c.added
+    FROM producers p
+    JOIN producers_rev pr ON pr.id = p.latest
+    JOIN changes c ON c.id = pr.id
+  |); $q->execute;
+  _sm_add('/p'.$_->[0], 'w', $_->[1]) while $_ = $q->fetchrow_arrayref;
+
+ # releases
+  $q = $sql->prepare(q|
+    SELECT r.id, c.added
+    FROM releases r
+    JOIN releases_rev rr ON rr.id = r.latest
+    JOIN changes c ON c.id = rr.id
+  |); $q->execute;
+  _sm_add('/r'.$_->[0], 'w', $_->[1], 0.3) while $_ = $q->fetchrow_arrayref;
+
+
+ # and stop writing
+  $x->endTag('urlset');
+  $x->end();
+  close($IO);
+  printf "Sitemap created, %d urls added\n", $urls;
+}
+
+
+
+sub _sm_add {
+  my($loc, $cf, $lastmod, $pri) = @_;
+  $x->startTag('url');
+   $x->dataElement('loc', $baseurl . $loc);
+   $x->dataElement('changefreq', $chfr{$cf}?$chfr{$cf}:$cf) if defined $cf;
+   $x->dataElement('lastmod', DateTime->from_epoch(epoch => $lastmod)->ymd) if defined $lastmod;
+   $x->dataElement('priority', $pri) if defined $pri;
+  $x->endTag('url');
+  $urls++;
+}