summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authoryorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b>2008-08-14 21:57:56 +0000
committeryorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b>2008-08-14 21:57:56 +0000
commitd0dd5ff69592ce332471c8ef49dd5bb3fd4ec3b2 (patch)
treeaec746ab23ae98aa6c1ba5564c9ba6dd0d8da078 /util
parent1c71b23985ace088dd8fa01001521c3844a14c40 (diff)
Deleted /util/OLD, because, seriously, that -is- old!
git-svn-id: svn://vndb.org/vndb@86 1fe2e327-d9db-4752-bcf7-ef0cb4a1748b
Diffstat (limited to 'util')
-rw-r--r--util/OLD/cleanimg.pl111
-rwxr-xr-xutil/OLD/cron_daily.sh31
-rw-r--r--util/OLD/cron_daily.sql15
-rw-r--r--util/OLD/relgraph.pl237
-rw-r--r--util/OLD/sitemap.pl97
5 files changed, 0 insertions, 491 deletions
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|<<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
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++;
-}