From d0dd5ff69592ce332471c8ef49dd5bb3fd4ec3b2 Mon Sep 17 00:00:00 2001 From: yorhel Date: Thu, 14 Aug 2008 21:57:56 +0000 Subject: Deleted /util/OLD, because, seriously, that -is- old! git-svn-id: svn://vndb.org/vndb@86 1fe2e327-d9db-4752-bcf7-ef0cb4a1748b --- util/OLD/cleanimg.pl | 111 ----------------------- util/OLD/cron_daily.sh | 31 ------- util/OLD/cron_daily.sql | 15 --- util/OLD/relgraph.pl | 237 ------------------------------------------------ util/OLD/sitemap.pl | 97 -------------------- 5 files changed, 491 deletions(-) delete mode 100644 util/OLD/cleanimg.pl delete mode 100755 util/OLD/cron_daily.sh delete mode 100644 util/OLD/cron_daily.sql delete mode 100644 util/OLD/relgraph.pl delete mode 100644 util/OLD/sitemap.pl diff --git a/util/OLD/cleanimg.pl b/util/OLD/cleanimg.pl deleted file mode 100644 index 45032106..00000000 --- a/util/OLD/cleanimg.pl +++ /dev/null @@ -1,111 +0,0 @@ -#!/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 deleted file mode 100755 index 743a9839..00000000 --- a/util/OLD/cron_daily.sh +++ /dev/null @@ -1,31 +0,0 @@ -#!/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 deleted file mode 100644 index c30f30f1..00000000 --- a/util/OLD/cron_daily.sql +++ /dev/null @@ -1,15 +0,0 @@ --- 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 deleted file mode 100644 index a4ae486d..00000000 --- a/util/OLD/relgraph.pl +++ /dev/null @@ -1,237 +0,0 @@ -#!/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|< - - -
%s
%s %s
>|, - $_->[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 '\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/&/&/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 deleted file mode 100644 index 6fde2f80..00000000 --- a/util/OLD/sitemap.pl +++ /dev/null @@ -1,97 +0,0 @@ -#!/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++; -} -- cgit v1.2.3