summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authoryorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b>2008-04-13 13:45:20 +0000
committeryorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b>2008-04-13 13:45:20 +0000
commitd7046f5d38004ff20739798c18f5796c31676546 (patch)
tree1639e6a8c3b74588bff7be6aaf6cf5e04e3bc63f /util
W00t, VNDB on SVN!
git-svn-id: svn://vndb.org/vndb@1 1fe2e327-d9db-4752-bcf7-ef0cb4a1748b
Diffstat (limited to 'util')
-rw-r--r--util/cleanimg.pl102
-rwxr-xr-xutil/cron_daily.sh31
-rw-r--r--util/cron_daily.sql15
-rwxr-xr-xutil/relgraph.pl237
-rwxr-xr-xutil/sitemap.pl94
-rwxr-xr-xutil/updates/update_1.1.pl18
-rw-r--r--util/updates/update_1.1.sql13
-rw-r--r--util/updates/update_1.10.sql92
-rw-r--r--util/updates/update_1.11.sql4
-rw-r--r--util/updates/update_1.12.sql34
-rw-r--r--util/updates/update_1.13.sql229
-rwxr-xr-xutil/updates/update_1.14.pl57
-rw-r--r--util/updates/update_1.14.sql76
-rw-r--r--util/updates/update_1.2.sql9
-rw-r--r--util/updates/update_1.4.sql37
-rw-r--r--util/updates/update_1.5.sql10
-rw-r--r--util/updates/update_1.6.sql21
-rw-r--r--util/updates/update_1.7.sql23
-rw-r--r--util/updates/update_1.8.sql27
-rw-r--r--util/updates/update_1.9.sql375
20 files changed, 1504 insertions, 0 deletions
diff --git a/util/cleanimg.pl b/util/cleanimg.pl
new file mode 100644
index 00000000..527fdb3a
--- /dev/null
+++ b/util/cleanimg.pl
@@ -0,0 +1,102 @@
+#!/usr/bin/perl
+
+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;
+
+our $ST;
+
+my $sql = DBI->connect('dbi:Pg:dbname=vndb', 'vndb', 'passwd',
+ { 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/cron_daily.sh b/util/cron_daily.sh
new file mode 100755
index 00000000..743a9839
--- /dev/null
+++ b/util/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/cron_daily.sql b/util/cron_daily.sql
new file mode 100644
index 00000000..c30f30f1
--- /dev/null
+++ b/util/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/relgraph.pl b/util/relgraph.pl
new file mode 100755
index 00000000..cdbd022b
--- /dev/null
+++ b/util/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;
+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 $tmpfile = '/tmp/vndb_graph.gif';
+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('dbi:Pg:dbname=vndb', 'vndb', 'passwd',
+ { 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] cmp $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]}gt$ids{$a->[1]}?$ids{$a->[1]}:$ids{$a->[0]})
+ cmp ($ids{$b->[0]}gt$ids{$b->[1]}?$ids{$b->[1]}:$ids{$b->[0]}) } @rel) {
+
+ if($ids{$_->[1]} gt $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.id = vr1.vid
+ JOIN vn v2 ON r.vid2 = v2.id
+ JOIN vn_rev vr2 ON v2.id = vr2.vid
+ WHERE (r.vid2 = ? OR vr1.vid = ?) AND v1.latest = vr1.id|
+ );
+ $s->execute($id, $id);
+ for my $r (@{$s->fetchall_arrayref({})}) {
+ 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/sitemap.pl b/util/sitemap.pl
new file mode 100755
index 00000000..64b0957a
--- /dev/null
+++ b/util/sitemap.pl
@@ -0,0 +1,94 @@
+#!/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;
+use DBI;
+use POSIX; # for ceil();
+use XML::Writer;
+use PerlIO::gzip;
+use DateTime;
+
+my $sql = DBI->connect('dbi:Pg:dbname=vndb', 'vndb', 'passwd',
+ { 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++;
+}
diff --git a/util/updates/update_1.1.pl b/util/updates/update_1.1.pl
new file mode 100755
index 00000000..c01e6625
--- /dev/null
+++ b/util/updates/update_1.1.pl
@@ -0,0 +1,18 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+use DBI;
+
+require '../lib/global.pl';
+
+my $sql = DBI->connect('dbi:Pg:dbname=vndb', 'vndb', 'passwd',
+ { RaiseError => 1, PrintError => 0, AutoCommit => 1, pg_enable_utf8 => 1 });
+
+my $q = $sql->prepare('SELECT id, rel_old, language FROM vnr'); $q->execute;
+for (@{$q->fetchall_arrayref({})}) {
+ my $rel = sprintf !$_->{rel_old} ? 'Original release' :
+ $_->{rel_old} == 1 ? '%s translation' : '%s rerelease', $VNDB::LANG->{$_->{language}};
+ $sql->do('UPDATE vnr SET relation = ? WHERE id = ?', undef, $rel, $_->{id});
+}
+$sql->do('ALTER TABLE vnr DROP COLUMN rel_old');
diff --git a/util/updates/update_1.1.sql b/util/updates/update_1.1.sql
new file mode 100644
index 00000000..0538e3d5
--- /dev/null
+++ b/util/updates/update_1.1.sql
@@ -0,0 +1,13 @@
+ALTER TABLE users ADD COLUMN pvotes smallint NOT NULL DEFAULT 1;
+ALTER TABLE users ADD COLUMN pfind smallint NOT NULL DEFAULT 1;
+
+UPDATE users
+ SET registered = 1191004915
+ WHERE registered = 0;
+UPDATE votes
+ SET date = 1191004915
+ WHERE date = 0;
+
+ALTER TABLE vnr RENAME COLUMN relation TO rel_old;
+ALTER TABLE vnr ADD COLUMN relation varchar(32) NOT NULL DEFAULT 'Original release';
+
diff --git a/util/updates/update_1.10.sql b/util/updates/update_1.10.sql
new file mode 100644
index 00000000..d68b0456
--- /dev/null
+++ b/util/updates/update_1.10.sql
@@ -0,0 +1,92 @@
+
+-- seperate releases_vn table
+CREATE TABLE releases_vn (
+ rid integer DEFAULT 0 NOT NULL,
+ vid integer DEFAULT 0 NOT NULL,
+ PRIMARY KEY(rid, vid)
+) WITHOUT OIDS;
+
+INSERT INTO releases_vn
+ SELECT rr.id AS rid, r.vid AS vid
+ FROM releases_rev rr
+ JOIN releases r ON rr.rid = r.id;
+
+ALTER TABLE releases DROP COLUMN vid;
+
+
+ALTER TABLE releases_rev ALTER COLUMN notes TYPE text;
+UPDATE producers_rev SET "desc" = '' WHERE "desc" = '0';
+
+
+
+
+-- Update rating calculation
+ALTER TABLE vn ALTER COLUMN c_votes TYPE character(10);
+ALTER TABLE vn ALTER COLUMN c_votes SET DEFAULT '00.00|0000';
+
+CREATE OR REPLACE FUNCTION calculate_rating() RETURNS void AS $$
+DECLARE
+ av RECORD;
+BEGIN
+ SELECT INTO av
+ COUNT(vote)::real / COUNT(DISTINCT vid)::real AS num_votes,
+ AVG(vote)::real AS rating
+ FROM votes;
+
+ UPDATE vn
+ SET c_votes = COALESCE((SELECT
+ TO_CHAR(CASE WHEN COUNT(uid) < 2 THEN 0 ELSE
+ ( (av.num_votes * av.rating) + SUM(vote)::real ) / (av.num_votes + COUNT(uid)::real ) END,
+ 'FM00D00'
+ )||'|'||TO_CHAR(
+ COUNT(votes.vote), 'FM0000'
+ )
+ FROM votes
+ WHERE votes.vid = vn.id
+ GROUP BY votes.vid
+ ), '00.00|0000');
+END
+$$ LANGUAGE plpgsql;
+SELECT calculate_rating();
+
+
+-- fix update_vncache
+DROP FUNCTION update_vncache(integer, integer);
+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
+ SUBSTRING(COALESCE(MIN(rr1.released), ''0000-00'') from 1 for 7)
+ 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 <> 2
+ GROUP BY rv1.vid
+ ), ''0000-00''),
+ c_languages = COALESCE(ARRAY_TO_STRING(ARRAY(
+ SELECT language
+ FROM releases_rev rr2
+ 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.released <= ''today''::date
+ GROUP BY rr2.language
+ ORDER BY rr2.language
+ ), ''/''), '''')
+ '||w;
+END;
+$$ LANGUAGE plpgsql;
+SELECT update_vncache(0);
+
+
+
+-- Add comments field to vnlists
+ALTER TABLE vnlists ADD COLUMN comments character varying(500) NOT NULL DEFAULT '';
+
diff --git a/util/updates/update_1.11.sql b/util/updates/update_1.11.sql
new file mode 100644
index 00000000..63a822a5
--- /dev/null
+++ b/util/updates/update_1.11.sql
@@ -0,0 +1,4 @@
+
+UPDATE vn_rev SET categories = categories << 6;
+
+ALTER TABLE vn_rev ADD COLUMN l_vnn integer NOT NULL DEFAULT 0;
diff --git a/util/updates/update_1.12.sql b/util/updates/update_1.12.sql
new file mode 100644
index 00000000..30238c6d
--- /dev/null
+++ b/util/updates/update_1.12.sql
@@ -0,0 +1,34 @@
+
+UPDATE vn_rev SET categories = categories << 2;
+
+DELETE FROM releases_vn rv WHERE NOT EXISTS(SELECT 1 FROM releases_rev rr WHERE rr.id = rv.rid);
+
+
+-- FOREIGN KEY CHECKING!
+ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_rev ADD FOREIGN KEY (rid) REFERENCES releases (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+--ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES releases_vn (rid) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases ADD FOREIGN KEY (latest) REFERENCES releases_rev (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_producers ADD FOREIGN KEY (pid) REFERENCES producers (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+
+ALTER TABLE vn_rev ADD FOREIGN KEY (id) REFERENCES changes (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_relations ADD FOREIGN KEY (vid2) REFERENCES vn (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+
+ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+
+ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vnlists ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vnlists ADD FOREIGN KEY (vid) REFERENCES vn (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+
diff --git a/util/updates/update_1.13.sql b/util/updates/update_1.13.sql
new file mode 100644
index 00000000..48a347e2
--- /dev/null
+++ b/util/updates/update_1.13.sql
@@ -0,0 +1,229 @@
+
+
+
+-- why did we still have this column?
+ALTER TABLE releases_rev DROP COLUMN relation;
+
+
+
+
+-- fix update_prev
+CREATE OR REPLACE FUNCTION update_prev(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 := 0;
+ FOR r2 IN EXECUTE 'SELECT id FROM '||tbl||'_rev WHERE '||t||'id = '||r.id||' ORDER BY id ASC' LOOP
+ UPDATE changes SET prev = i WHERE id = r2.id;
+ i := r2.id;
+ END LOOP;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+SELECT update_prev('vn',''), update_prev('releases',''), update_prev('producers','');
+
+
+
+
+-- change votes treshold to 3
+CREATE OR REPLACE FUNCTION calculate_rating() RETURNS void AS $$
+DECLARE
+ av RECORD;
+BEGIN
+ SELECT INTO av
+ COUNT(vote)::real / COUNT(DISTINCT vid)::real AS num_votes,
+ AVG(vote)::real AS rating
+ FROM votes;
+
+ UPDATE vn
+ SET c_votes = COALESCE((SELECT
+ TO_CHAR(CASE WHEN COUNT(uid) < 3 THEN 0 ELSE
+ ( (av.num_votes * av.rating) + SUM(vote)::real ) / (av.num_votes + COUNT(uid)::real ) END,
+ 'FM00D00'
+ )||'|'||TO_CHAR(
+ COUNT(votes.vote), 'FM0000'
+ )
+ FROM votes
+ WHERE votes.vid = vn.id
+ GROUP BY votes.vid
+ ), '00.00|0000');
+END
+$$ LANGUAGE plpgsql;
+SELECT calculate_rating();
+
+
+
+
+-- store release dates as integers
+ALTER TABLE releases_rev ALTER COLUMN released TYPE integer USING REPLACE(released, '-', '')::integer;
+UPDATE releases_rev SET released = 0 WHERE released IS NULL;
+ALTER TABLE releases_rev ALTER COLUMN released SET NOT NULL;
+
+ALTER TABLE vn ALTER COLUMN c_released SET DEFAULT 0;
+ALTER TABLE vn ALTER COLUMN c_released TYPE integer USING 0;
+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 <> 2
+ AND rr1.released <> 0
+ GROUP BY rv1.vid
+ ), 0),
+ c_languages = COALESCE(ARRAY_TO_STRING(ARRAY(
+ SELECT language
+ FROM releases_rev rr2
+ 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.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
+ GROUP BY rr2.language
+ ORDER BY rr2.language
+ ), ''/''), '''')
+ '||w;
+END;
+$$ LANGUAGE plpgsql;
+SELECT update_vncache(0);
+
+
+
+
+-- Rewrite category system
+CREATE TABLE vn_categories (
+ vid integer NOT NULL DEFAULT 0,
+ cat char(3) NOT NULL DEFAULT '',
+ lvl smallint NOT NULL DEFAULT 3,
+ PRIMARY KEY(vid, cat)
+) WITHOUT OIDS;
+
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'gaa', 1 FROM vn_rev WHERE (categories & (1<<0)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'gab', 1 FROM vn_rev WHERE (categories & (1<<1)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'gac', 3 FROM vn_rev WHERE (categories & (1<<2)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'grp', 3 FROM vn_rev WHERE (categories & (1<<3)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'gst', 3 FROM vn_rev WHERE (categories & (1<<4)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'gsi', 3 FROM vn_rev WHERE (categories & (1<<5)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'pli', 1 FROM vn_rev WHERE (categories & (1<<6)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'pbr', 1 FROM vn_rev WHERE (categories & (1<<7)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'eac', 3 FROM vn_rev WHERE (categories & (1<<8)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'eco', 3 FROM vn_rev WHERE (categories & (1<<9)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'edr', 3 FROM vn_rev WHERE (categories & (1<<10)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'efa', 3 FROM vn_rev WHERE (categories & (1<<11)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'eho', 3 FROM vn_rev WHERE (categories & (1<<12)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'emy', 3 FROM vn_rev WHERE (categories & (1<<13)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'ero', 3 FROM vn_rev WHERE (categories & (1<<14)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'esf', 3 FROM vn_rev WHERE (categories & (1<<15)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'esj', 3 FROM vn_rev WHERE (categories & (1<<16)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'esn', 3 FROM vn_rev WHERE (categories & (1<<17)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'tfu', 3 FROM vn_rev WHERE (categories & (1<<18)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'tpa', 3 FROM vn_rev WHERE (categories & (1<<19)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'tpr', 3 FROM vn_rev WHERE (categories & (1<<20)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'lea', 3 FROM vn_rev WHERE (categories & (1<<21)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'lfa', 3 FROM vn_rev WHERE (categories & (1<<22)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'lsp', 3 FROM vn_rev WHERE (categories & (1<<23)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'saa', 3 FROM vn_rev WHERE (categories & (1<<24)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'sbe', 3 FROM vn_rev WHERE (categories & (1<<25)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'sin', 3 FROM vn_rev WHERE (categories & (1<<26)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'slo', 3 FROM vn_rev WHERE (categories & (1<<27)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'ssh', 3 FROM vn_rev WHERE (categories & (1<<28)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'sya', 3 FROM vn_rev WHERE (categories & (1<<29)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'syu', 3 FROM vn_rev WHERE (categories & (1<<30)) > 0;
+INSERT INTO vn_categories (vid, cat, lvl) SELECT id, 'sra', 3 FROM vn_rev WHERE (categories & (1<<31)) < 0; -- MSB, mind you!
+ALTER TABLE vn_rev DROP COLUMN categories;
+
+
+
+-- Remove all previously defined constraints
+ALTER TABLE releases_rev DROP CONSTRAINT releases_rev_id_fkey;
+ALTER TABLE releases_rev DROP CONSTRAINT releases_rev_rid_fkey;
+ALTER TABLE releases DROP CONSTRAINT releases_latest_fkey;
+ALTER TABLE releases_vn DROP CONSTRAINT releases_vn_rid_fkey;
+ALTER TABLE releases_vn DROP CONSTRAINT releases_vn_vid_fkey;
+ALTER TABLE releases_platforms DROP CONSTRAINT releases_platforms_rid_fkey;
+ALTER TABLE releases_media DROP CONSTRAINT releases_media_rid_fkey;
+ALTER TABLE releases_producers DROP CONSTRAINT releases_producers_rid_fkey;
+ALTER TABLE releases_producers DROP CONSTRAINT releases_producers_pid_fkey;
+
+ALTER TABLE vn_rev DROP CONSTRAINT vn_rev_id_fkey;
+ALTER TABLE vn_rev DROP CONSTRAINT vn_rev_vid_fkey;
+ALTER TABLE vn DROP CONSTRAINT vn_latest_fkey;
+ALTER TABLE vn_relations DROP CONSTRAINT vn_relations_vid1_fkey;
+ALTER TABLE vn_relations DROP CONSTRAINT vn_relations_vid2_fkey;
+
+ALTER TABLE changes DROP CONSTRAINT changes_requester_fkey;
+ALTER TABLE votes DROP CONSTRAINT votes_uid_fkey;
+ALTER TABLE votes DROP CONSTRAINT votes_vid_fkey;
+ALTER TABLE vnlists DROP CONSTRAINT vnlists_uid_fkey;
+ALTER TABLE vnlists DROP CONSTRAINT vnlists_vid_fkey;
+
+
+-- And re-add them... LOLZ
+ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_rev ADD FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE INITIALLY DEFERRED;
+--ALTER TABLE releases_rev ADD FOREIGN KEY (id, NULL) REFERENCES releases_vn (rid, vid) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases ADD FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_producers ADD FOREIGN KEY (pid) REFERENCES producers (id) DEFERRABLE INITIALLY DEFERRED;
+
+ALTER TABLE vn_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_categories ADD FOREIGN KEY (vid) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_relations ADD FOREIGN KEY (vid2) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
+
+ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
+
+ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED;-- ON DELETE SET DEFAULT
+ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vnlists ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vnlists ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
+
+
+--ALTER TABLE releases_rev ADD COLUMN ref_vid_hack integer NULL DEFAULT NULL;
+--ALTER TABLE releases_rev ADD FOREIGN KEY (id, ref_vid_hack) REFERENCES releases_vn (rid, vid) ON DELETE CASCADE;
+
+-- TODO:
+-- - make sure that changes.id should always refer to a row in *_rev
+-- - make sure that there is always at least one row in releases_vn for every releases_rev
+
+-- deletion of items in *_rev should trigger deletion in changes
+--CREATE OR REPLACE FUNCTION changes_reference_del() RETURNS trigger AS $$
+--BEGIN
+-- DELETE FROM changes WHERE id = OLD.id;
+--END
+--$$ LANGUAGE PLPGSQL;
+
+--CREATE TRIGGER vn_rev_cdel AFTER DELETE ON vn_rev FOR EACH ROW EXECUTE PROCEDURE changes_reference_del();
+--CREATE TRIGGER releases_rev_cdel AFTER DELETE ON releases_rev FOR EACH ROW EXECUTE PROCEDURE changes_reference_del();
+--CREATE TRIGGER producers_rev_cdel AFTER DELETE ON producers_rev FOR EACH ROW EXECUTE PROCEDURE changes_reference_del();
+
+
+
+
diff --git a/util/updates/update_1.14.pl b/util/updates/update_1.14.pl
new file mode 100755
index 00000000..1bfc517a
--- /dev/null
+++ b/util/updates/update_1.14.pl
@@ -0,0 +1,57 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+use File::Path;
+use DBI;
+
+# script assumes:
+# /static has been created
+# /www/files has already been moved
+chdir '/www/vndb';
+
+
+# run the usual SQL update script
+system('psql -U vndb < util/updates/update_1.14.sql');
+
+# fix directories
+rmtree('data/rg');
+rmtree('www/rg');
+
+mkdir 'data/rg';
+mkdir 'static/cv';
+mkdir 'static/rg';
+chmod 0755, qw|data/rg static/cv static/rg|;
+
+for (0..49) {
+ $_ = sprintf "%02d",$_;
+ mkdir "data/rg/$_";
+ mkdir "static/rg/$_";
+ mkdir "static/cv/$_";
+ chmod 0777, "data/rg/$_", "static/rg/$_", "static/cv/$_";
+}
+
+
+# rename relation graphs
+system('util/relgraph.pl');
+
+
+# rename cover images
+my $sql = DBI->connect('dbi:Pg:dbname=vndb', 'vndb', 'passwd',
+ { RaiseError => 0, PrintError => 1, AutoCommit => 1, pg_enable_utf8 => 1 });
+$sql->do('CREATE SEQUENCE covers_seq');
+$sql->do('ALTER TABLE vn_rev ADD COLUMN image_id integer NOT NULL DEFAULT 0');
+my $q = $sql->prepare('SELECT DISTINCT ENCODE(image,\'hex\') FROM vn_rev WHERE image <> \'\'');
+$q->execute();
+for (@{$q->fetchall_arrayref([])}) {
+ $q = $sql->prepare('SELECT nextval(\'covers_seq\')');
+ $q->execute();
+ my($id) = $q->fetchrow_array();
+ rename
+ sprintf('www/img/%s/%s.jpg', substr($_->[0],0,1), $_->[0]),
+ sprintf('static/cv/%02d/%d.jpg', $id%50, $id);
+ $sql->do('UPDATE vn_rev SET image_id = ? WHERE image = DECODE(\''.$_->[0].'\', \'hex\')', undef, $id);
+}
+$sql->do('ALTER TABLE vn_rev DROP COLUMN image');
+$sql->do('ALTER TABLE vn_rev RENAME COLUMN image_id TO image');
+
diff --git a/util/updates/update_1.14.sql b/util/updates/update_1.14.sql
new file mode 100644
index 00000000..70ae8ef0
--- /dev/null
+++ b/util/updates/update_1.14.sql
@@ -0,0 +1,76 @@
+
+
+-- drop get_new_id()
+CREATE SEQUENCE vn_id_seq OWNED BY vn.id;
+SELECT setval('vn_id_seq', get_new_id('vn')-1);
+ALTER TABLE vn ALTER COLUMN id SET DEFAULT nextval('vn_id_seq');
+
+CREATE SEQUENCE releases_id_seq OWNED BY releases.id;
+SELECT setval('releases_id_seq', get_new_id('releases')-1);
+ALTER TABLE releases ALTER COLUMN id SET DEFAULT nextval('releases_id_seq');
+
+CREATE SEQUENCE producers_id_seq OWNED BY producers.id;
+SELECT setval('producers_id_seq', get_new_id('producers')-1);
+ALTER TABLE producers ALTER COLUMN id SET DEFAULT nextval('producers_id_seq');
+
+DROP FUNCTION get_new_id(text);
+
+
+
+-- relation graphs get ID numbers
+CREATE SEQUENCE relgraph_seq;
+ALTER TABLE vn ALTER COLUMN rgraph DROP NOT NULL;
+ALTER TABLE vn ALTER COLUMN rgraph DROP DEFAULT;
+ALTER TABLE vn ALTER COLUMN rgraph TYPE integer USING 0;
+ALTER TABLE vn ALTER COLUMN rgraph SET DEFAULT 0;
+ALTER TABLE vn ALTER COLUMN rgraph SET NOT NULL;
+
+
+-- cover images get ID numbers as well
+-- (handled in update_1.14.pl)
+
+
+
+-- 'hidden' flag to all items in the DB
+ALTER TABLE vn ADD COLUMN hidden smallint NOT NULL DEFAULT 0;
+ALTER TABLE producers ADD COLUMN hidden smallint NOT NULL DEFAULT 0;
+ALTER TABLE releases ADD COLUMN hidden smallint NOT NULL DEFAULT 0;
+
+
+-- update update_vncache to handle the hidden flag
+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 <> 2
+ AND r1.hidden = 0
+ AND rr1.released <> 0
+ GROUP BY rv1.vid
+ ), 0),
+ c_languages = COALESCE(ARRAY_TO_STRING(ARRAY(
+ SELECT language
+ FROM releases_rev rr2
+ 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.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
+ AND r2.hidden = 0
+ GROUP BY rr2.language
+ ORDER BY rr2.language
+ ), ''/''), '''')
+ '||w;
+END;
+$$ LANGUAGE plpgsql;
+SELECT update_vncache(0);
+
diff --git a/util/updates/update_1.2.sql b/util/updates/update_1.2.sql
new file mode 100644
index 00000000..c1b48b84
--- /dev/null
+++ b/util/updates/update_1.2.sql
@@ -0,0 +1,9 @@
+CREATE TABLE vnlists (
+ uid integer NOT NULL DEFAULT 0,
+ vid integer NOT NULL DEFAULT 0,
+ status smallint NOT NULL DEFAULT 0,
+ added bigint NOT NULL DEFAULT 0,
+ PRIMARY KEY(uid, vid)
+) WITHOUT OIDS;
+
+ALTER TABlE users ADD COLUMN plist smallint NOT NULL DEFAULT 1;
diff --git a/util/updates/update_1.4.sql b/util/updates/update_1.4.sql
new file mode 100644
index 00000000..783c0029
--- /dev/null
+++ b/util/updates/update_1.4.sql
@@ -0,0 +1,37 @@
+UPDATE vn_categories SET category = 'aaa' WHERE category = 'ami';
+
+--CREATE TABLE changes (
+-- id SERIAL NOT NULL PRIMARY KEY,
+-- "type" smallint DEFAULT 0 NOT NULL,
+-- rel integer DEFAULT 0 NOT NULL,
+-- vrel integer DEFAULT 0 NOT NULL,
+-- uid integer DEFAULT 0 NOT NULL,
+-- status smallint DEFAULT 0 NOT NULL,
+-- added bigint DEFAULT 0 NOT NULL,
+-- lastmod bigint DEFAULT 0 NOT NULL,
+-- changes bytea DEFAULT ''::bytea NOT NULL,
+-- comments text DEFAULT '' NOT NULL
+--);
+
+
+CREATE LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION get_new_id() RETURNS integer AS $$
+DECLARE
+ i integer := 1;
+ r RECORD;
+BEGIN
+ FOR r IN SELECT id FROM vn ORDER BY id ASC LOOP
+ IF i <> r.id THEN
+ EXIT;
+ END IF;
+ i := i+1;
+ END LOOP;
+ RETURN i;
+END;
+$$ LANGUAGE plpgsql;
+
+ALTER TABLE vn ALTER COLUMN id SET DEFAULT get_new_id();
+DROP SEQUENCE vn_id_seq;
+
+
+ALTER TABLE vnr ADD COLUMN notes varchar(250) DEFAULT '';
diff --git a/util/updates/update_1.5.sql b/util/updates/update_1.5.sql
new file mode 100644
index 00000000..970aa0e1
--- /dev/null
+++ b/util/updates/update_1.5.sql
@@ -0,0 +1,10 @@
+CREATE TABLE vn_relations (
+ vid1 integer NOT NULL,
+ vid2 integer NOT NULL,
+ relation smallint NOT NULL,
+ lastmod bigint NOT NULL,
+ PRIMARY KEY(vid1, vid2)
+);
+
+ALTER TABLE vn ADD COLUMN img_nsfw smallint NOT NULL DEFAULT 0;
+ALTER TABLE users ADD COLUMN pign_nsfw smallint NOT NULL DEFAULT 0;
diff --git a/util/updates/update_1.6.sql b/util/updates/update_1.6.sql
new file mode 100644
index 00000000..a1c8ea23
--- /dev/null
+++ b/util/updates/update_1.6.sql
@@ -0,0 +1,21 @@
+ALTER TABLE vnr DROP COLUMN rel_old;
+ALTER TABLE vnr ALTER COLUMN released DROP NOT NULL;
+ALTER TABLE vnr ALTER COLUMN released SET DEFAULT NULL;
+UPDATE vnr SET released = NULL WHERE released = '0000-00-00';
+
+ALTER TABLE vn RENAME COLUMN c_years TO c_released;
+UPDATE vn SET c_released = '0000-00';
+ALTER TABLE vn ALTER COLUMN c_released SET DEFAULT '0000-00';
+ALTER TABLE vn ALTER COLUMN c_released TYPE character(7);
+UPDATE vn SET
+ c_released = COALESCE((SELECT
+ SUBSTRING(COALESCE(MIN(released), '0000-00') from 1 for 7)
+ FROM vnr r1
+ WHERE r1.vid = vn.id
+ AND r1.r_rel = 0
+ GROUP BY r1.vid
+ ), '0000-00');
+
+
+ALTER TABLE vn_relations DROP COLUMN lastmod;
+ALTER TABLE vn ADD COLUMN rgraph bytea NOT NULL DEFAULT '';
diff --git a/util/updates/update_1.7.sql b/util/updates/update_1.7.sql
new file mode 100644
index 00000000..3ee6f5a2
--- /dev/null
+++ b/util/updates/update_1.7.sql
@@ -0,0 +1,23 @@
+ALTER TABLE producers ADD COLUMN "desc" text NOT NULL DEFAULT '';
+
+
+--ALTER TABLE users ADD COLUMN flags bit(4) NOT NULL DEFAULT B'1110';
+--UPDATE users SET flags = pvotes::bit || pfind::bit || plist::bit || pign_nsfw::bit;
+ALTER TABLE users ADD COLUMN flags integer NOT NULL DEFAULT 7;
+UPDATE users SET flags = pvotes + pfind*2 + plist*4 + pign_nsfw*8;
+
+--ALTER TABLE users DROP COLUMN pvotes;
+--ALTER TABLE users DROP COLUMN pfind;
+--ALTER TABLE users DROP COLUMN plist;
+--ALTER TABLE users DROP COLUMN pign_nsfw;
+
+
+--ALTER TABLE vn ADD COLUMN categories integer NOT NULL DEFAULT 0;
+--UPDATE vn SET categories =
+-- COALESCE((SELECT 1 FROM vn_categories WHERE vid = vn.id AND category = 'a18'), 0)
+-- +COALESCE((SELECT 2 FROM vn_categories WHERE vid = vn.id AND category = 'aaa'), 0)
+-- +COALESCE((SELECT 4 FROM vn_categories WHERE vid = vn.id AND category = 'ajo'), 0)
+-- +COALESCE((SELECT 8 FROM vn_categories WHERE vid = vn.id AND category = 'ako'), 0)
+-- +COALESCE((SELECT 16 FROM vn_categories WHERE vid = vn.id AND category = 'ase'), 0)
+-- +COALESCE((SELECT 32 FROM vn_categories WHERE vid = vn.id AND category = 'asj'), 0)
+-- +COALESCE((SELECT 64 FROM vn_categories WHERE vid = vn.id AND category = 'asn'), 0);
diff --git a/util/updates/update_1.8.sql b/util/updates/update_1.8.sql
new file mode 100644
index 00000000..b9e58ae6
--- /dev/null
+++ b/util/updates/update_1.8.sql
@@ -0,0 +1,27 @@
+ALTER TABLE vn ADD COLUMN length smallint NOT NULL DEFAULT 0;
+
+DELETE FROM vn_categories WHERE SUBSTR(category, 1, 1) = 'a';
+ALTER TABLE vnr ADD COLUMN minage smallint NOT NULL DEFAULT -1;
+
+ALTER TABLE vn ADD COLUMN l_wp varchar(150) NOT NULL DEFAULT '';
+ALTER TABLE vn ADD COLUMN l_cisv integer NOT NULL DEFAULT 0;
+
+
+UPDATE vn SET
+ c_released = COALESCE((
+ SELECT SUBSTRING(COALESCE(MIN(released), '0000-00') from 1 for 7)
+ FROM vnr r1
+ WHERE r1.vid = vn.id
+ AND r1.r_rel = 0
+ AND r1.relation NOT ILIKE 'trial'
+ GROUP BY r1.vid
+ ), '0000-00'),
+ c_languages = COALESCE(ARRAY_TO_STRING(ARRAY(
+ SELECT language
+ FROM vnr r2
+ WHERE r2.vid = vn.id
+ AND r2.r_rel = 0
+ AND r2.relation NOT ILIKE 'trial'
+ GROUP BY language
+ ORDER BY language
+ ), '/'), '');
diff --git a/util/updates/update_1.9.sql b/util/updates/update_1.9.sql
new file mode 100644
index 00000000..ad36ec2c
--- /dev/null
+++ b/util/updates/update_1.9.sql
@@ -0,0 +1,375 @@
+CREATE TABLE changes (
+ id SERIAL NOT NULL PRIMARY KEY,
+ "type" smallint NOT NULL DEFAULT 0,
+ added bigint NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW()),
+ requester integer NOT NULL DEFAULT 0,
+ ip inet NOT NULL DEFAULT '0.0.0.0',
+ comments text NOT NULL DEFAULT '',
+ prev integer NOT NULL DEFAULT 0,
+ causedby integer NOT NULL DEFAULT 0
+) WITHOUT OIDS;
+
+INSERT INTO users (id, username, mail, rank, registered)
+ VALUES (1, 'multi', 'multi@vndb.org', 0, EXTRACT(EPOCH FROM NOW()));
+
+CREATE OR REPLACE FUNCTION get_new_id(tbl text) RETURNS integer AS $$
+DECLARE
+ i integer := 1;
+ r RECORD;
+BEGIN
+ FOR r IN EXECUTE 'SELECT id FROM '||tbl||' ORDER BY id ASC' LOOP
+ IF i <> r.id THEN
+ EXIT;
+ END IF;
+ i := i + 1;
+ END LOOP;
+ RETURN i;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+
+-- V i s u a l N o v e l s
+
+
+ALTER TABLE vn RENAME TO vn_old;
+ALTER TABLE vn_relations RENAME TO vn_relations_old;
+
+CREATE TABLE vn (
+ id integer NOT NULL DEFAULT get_new_id('vn') PRIMARY KEY,
+ latest integer NOT NULL DEFAULT 0,
+ locked smallint NOT NULL DEFAULT 0,
+ rgraph bytea NOT NULL DEFAULT '',
+ c_released character(7) NOT NULL DEFAULT '0000-00',
+ c_languages varchar(32) NOT NULL DEFAULT '',
+ c_votes character(9) NOT NULL DEFAULT '00.0|0000'
+) WITHOUT OIDS;
+
+CREATE TABLE vn_rev (
+ id integer NOT NULL PRIMARY KEY,
+ vid integer NOT NULL DEFAULT 0,
+ title varchar(250) NOT NULL DEFAULT '',
+ alias varchar(500) NOT NULL DEFAULT '',
+ image bytea NOT NULL DEFAULT '',
+ img_nsfw smallint NOT NULL DEFAULT 0,
+ length smallint NOT NULL DEFAULT 0,
+ "desc" text NOT NULL DEFAULT '',
+ categories integer NOT NULL DEFAULT 0,
+ l_wp varchar(150) NOT NULL DEFAULT '',
+ l_cisv integer NOT NULL DEFAULT 0
+) WITHOUT OIDS;
+
+CREATE TABLE vn_relations (
+ vid1 integer NOT NULL DEFAULT 0,
+ vid2 integer NOT NULL DEFAULT 0,
+ relation integer NOT NULL DEFAULT 0,
+ PRIMARY KEY(vid1, vid2)
+) WITHOUT OIDS;
+
+CREATE OR REPLACE FUNCTION fill_vn() RETURNS void AS $$
+DECLARE
+ r RECORD;
+ r2 RECORD;
+ i integer;
+ rel integer;
+BEGIN
+ FOR r IN SELECT * FROM vn_old ORDER BY added LOOP
+ INSERT INTO changes ("type", added, requester, comments)
+ VALUES (0, r.added, 1, 'Automated import from VNDB 1.8');
+
+ SELECT currval('changes_id_seq') INTO i;
+
+ INSERT INTO vn_rev (id, vid, title, alias, image, img_nsfw, length, "desc", l_wp, l_cisv, categories)
+ VALUES (i, r.id, r.title, r.alias, r.image, r.img_nsfw, r.length, r.desc, r.l_wp, r.l_cisv, (
+ -- ZOMFG DENORMALIZATION LOL!
+ COALESCE((SELECT 1 FROM vn_categories WHERE vid = r.id AND category = 'eac'), 0)
+ +COALESCE((SELECT 2 FROM vn_categories WHERE vid = r.id AND category = 'eco'), 0)
+ +COALESCE((SELECT 4 FROM vn_categories WHERE vid = r.id AND category = 'edr'), 0)
+ +COALESCE((SELECT 8 FROM vn_categories WHERE vid = r.id AND category = 'efa'), 0)
+ +COALESCE((SELECT 16 FROM vn_categories WHERE vid = r.id AND category = 'eho'), 0)
+ +COALESCE((SELECT 32 FROM vn_categories WHERE vid = r.id AND category = 'emy'), 0)
+ +COALESCE((SELECT 64 FROM vn_categories WHERE vid = r.id AND category = 'ero'), 0)
+ +COALESCE((SELECT 128 FROM vn_categories WHERE vid = r.id AND category = 'esf'), 0)
+ +COALESCE((SELECT 256 FROM vn_categories WHERE vid = r.id AND category = 'eja'), 0)
+ +COALESCE((SELECT 512 FROM vn_categories WHERE vid = r.id AND category = 'ena'), 0)
+ +COALESCE((SELECT 1024 FROM vn_categories WHERE vid = r.id AND category = 'tfu'), 0)
+ +COALESCE((SELECT 2048 FROM vn_categories WHERE vid = r.id AND category = 'tpa'), 0)
+ +COALESCE((SELECT 4096 FROM vn_categories WHERE vid = r.id AND category = 'tpr'), 0)
+ +COALESCE((SELECT 8192 FROM vn_categories WHERE vid = r.id AND category = 'pea'), 0)
+ +COALESCE((SELECT 16384 FROM vn_categories WHERE vid = r.id AND category = 'pfw'), 0)
+ +COALESCE((SELECT 32768 FROM vn_categories WHERE vid = r.id AND category = 'psp'), 0)
+ +COALESCE((SELECT 65536 FROM vn_categories WHERE vid = r.id AND category = 'spa'), 0)
+ +COALESCE((SELECT 131072 FROM vn_categories WHERE vid = r.id AND category = 'sbe'), 0)
+ +COALESCE((SELECT 262144 FROM vn_categories WHERE vid = r.id AND category = 'sin'), 0)
+ +COALESCE((SELECT 524288 FROM vn_categories WHERE vid = r.id AND category = 'slo'), 0)
+ +COALESCE((SELECT 1048576 FROM vn_categories WHERE vid = r.id AND category = 'scc'), 0)
+ +COALESCE((SELECT 2097152 FROM vn_categories WHERE vid = r.id AND category = 'sya'), 0)
+ +COALESCE((SELECT 4194304 FROM vn_categories WHERE vid = r.id AND category = 'syu'), 0)
+ +COALESCE((SELECT 8388608 FROM vn_categories WHERE vid = r.id AND category = 'sra'), 0)
+ ));
+
+ INSERT INTO vn (id, latest, locked, rgraph, c_released, c_languages, c_votes)
+ VALUES (r.id, i, r.locked, r.rgraph, r.c_released, r.c_languages, r.c_votes);
+
+ FOR r2 IN SELECT * FROM vn_relations_old WHERE vid2 = r.id LOOP
+ INSERT INTO vn_relations (vid1, vid2, relation)
+ VALUES(i, r2.vid1, r2.relation);
+ END LOOP;
+ FOR r2 IN SELECT * FROM vn_relations_old WHERE vid1 = r.id LOOP
+ rel := r2.relation;
+ IF rel = 0 OR rel = 6 OR rel = 8 THEN
+ rel := rel+1;
+ END IF;
+ INSERT INTO vn_relations (vid1, vid2, relation)
+ VALUES(i, r2.vid2, rel);
+ END LOOP;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+SELECT fill_vn();
+DROP FUNCTION fill_vn();
+
+
+
+
+
+-- R e l e a s e s
+
+
+ALTER TABLE vnr RENAME TO vnr_old;
+
+CREATE TABLE releases (
+ id integer NOT NULL DEFAULT get_new_id('releases') PRIMARY KEY,
+ latest integer NOT NULL DEFAULT 0,
+ vid integer NOT NULL DEFAULT 0,
+ locked smallint NOT NULL DEFAULT 0
+) WITHOUT OIDS;
+
+CREATE TABLE releases_rev (
+ id integer NOT NULL PRIMARY KEY,
+ rid integer NOT NULL DEFAULT 0,
+ title varchar(250) NOT NULL DEFAULT '',
+ original varchar(250) NOT NULL DEFAULT '',
+ "type" smallint NOT NULL DEFAULT 0,
+ relation varchar(32) NOT NULL DEFAULT '', -- deprecated
+ language varchar NOT NULL DEFAULT 'ja',
+ website varchar(250) NOT NULL DEFAULT '',
+ released varchar(10),
+ notes varchar(250) NOT NULL DEFAULT '',
+ minage smallint NOT NULL DEFAULT -1
+) WITHOUT OIDS;
+
+ALTER TABLE vnr_media RENAME TO releases_media;
+ALTER TABLE vnr_platforms RENAME TO releases_platforms;
+ALTER TABLE vnr_producers RENAME TO releases_producers;
+ALTER TABLE releases_media RENAME vnrid TO rid;
+ALTER TABLE releases_platforms RENAME vnrid TO rid;
+ALTER TABLE releases_producers RENAME vnrid TO rid;
+ALTER TABLE releases_media ADD COLUMN tmp_upd smallint DEFAULT 0;
+ALTER TABLE releases_platforms ADD COLUMN tmp_upd smallint DEFAULT 0;
+ALTER TABLE releases_producers ADD COLUMN tmp_upd smallint DEFAULT 0;
+ALTER TABLE releases_platforms DROP CONSTRAINT vnv_platforms_pkey;
+ALTER TABLE releases_producers DROP CONSTRAINT vnv_companies_pkey;
+
+
+CREATE OR REPLACE FUNCTION fill_releases() RETURNS void AS $$
+DECLARE
+ r RECORD;
+ i integer;
+ t integer;
+ ti text;
+ tg text;
+BEGIN
+ FOR r IN SELECT * FROM vnr_old ORDER BY added LOOP
+ INSERT INTO changes ("type", added, requester, comments)
+ VALUES (1, r.added, 1, 'Automated import from VNDB 1.8');
+
+ SELECT currval('changes_id_seq') INTO i;
+
+ -- swap titles
+ ti := r.romaji;
+ tg := r.title;
+ IF ti = '' THEN
+ ti := r.title;
+ tg := '';
+ END IF;
+ -- determine type
+ t := 0;
+ IF r.relation ILIKE '%trial%' OR r.relation ILIKE '%demo%' THEN
+ t := 2;
+ END IF;
+
+ INSERT INTO releases_rev (id, rid, title, original, relation, language, website, released, notes, minage, "type")
+ VALUES (i, r.id, ti, tg, r.relation, r.language, r.website, r.released, r.notes, r.minage, t);
+
+ INSERT INTO releases (id, latest, vid)
+ VALUES (r.id, i, r.vid);
+
+ UPDATE releases_media SET rid = i, tmp_upd = 1 WHERE rid = r.id AND tmp_upd = 0;
+ UPDATE releases_producers SET rid = i, tmp_upd = 1 WHERE rid = r.id AND tmp_upd = 0;
+ UPDATE releases_platforms SET rid = i, tmp_upd = 1 WHERE rid = r.id AND tmp_upd = 0;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+SELECT fill_releases();
+DROP FUNCTION fill_releases();
+
+ALTER TABLE releases_media DROP COLUMN tmp_upd;
+ALTER TABLE releases_producers DROP COLUMN tmp_upd;
+ALTER TABLE releases_platforms DROP COLUMN tmp_upd;
+ALTER TABLE releases_producers ADD CONSTRAINT releases_producers_pkey PRIMARY KEY (pid, rid);
+ALTER TABLE releases_media ADD CONSTRAINT releases_media_pkey PRIMARY KEY (rid, medium, qty);
+ALTER TABLE releases_platforms ADD CONSTRAINT releases_platforms_pkey PRIMARY KEY (rid, platform);
+
+
+
+
+
+-- P r o d u c e r s
+
+
+ALTER TABLE producers RENAME TO producers_old;
+
+CREATE TABLE producers (
+ id integer NOT NULL DEFAULT get_new_id('producers') PRIMARY KEY,
+ latest integer NOT NULL DEFAULT 0,
+ locked smallint NOT NULL DEFAULT 0
+) WITHOUT OIDS;
+
+CREATE TABLE producers_rev (
+ id integer NOT NULL PRIMARY KEY,
+ pid integer NOT NULL DEFAULT 0,
+ "type" character(2) NOT NULL DEFAULT 'co',
+ name varchar(200) NOT NULL DEFAULT '',
+ original varchar(200) NOT NULL DEFAULT '',
+ website varchar(250) NOT NULL DEFAULT '',
+ lang varchar NOT NULL DEFAULT 'ja',
+ "desc" text NOT NULL DEFAULT ''
+) WITHOUT OIDS;
+
+CREATE OR REPLACE FUNCTION fill_producers() RETURNS void AS $$
+DECLARE
+ r RECORD;
+ i integer;
+BEGIN
+ FOR r IN SELECT * FROM producers_old ORDER BY added LOOP
+ INSERT INTO changes ("type", added, requester, comments)
+ VALUES (2, r.added, 1, 'Automated import from VNDB 1.8');
+
+ SELECT currval('changes_id_seq') INTO i;
+
+ INSERT INTO producers_rev (id, pid, "type", name, original, website, lang, "desc")
+ VALUES (i, r.id, r.type, r.name, r.original, r.website, r.lang, r.desc);
+
+ INSERT INTO producers (id, latest, locked)
+ VALUES (r.id, i, 0);
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+SELECT fill_producers();
+DROP FUNCTION fill_producers();
+
+
+
+
+
+
+
+DROP TABLE vn_old;
+DROP TABLE vn_relations_old;
+DROP TABLE vn_categories;
+DROP TABLE vnr_old;
+DROP TABLE producers_old;
+DROP FUNCTION get_new_id();
+
+
+UPDATE users SET rank = rank+1;
+ALTER TABLE users ALTER COLUMN rank SET DEFAULT 2;
+
+
+
+
+
+-- F u n c t i o n s
+
+
+-- ids = empty string or comma-seperated list of id's (as a string)
+CREATE OR REPLACE FUNCTION update_prev(tbl text, ids text) RETURNS void AS $$
+DECLARE
+ r RECORD;
+ r2 RECORD;
+ i integer;
+ t text;
+ e text;
+BEGIN
+ SELECT INTO t SUBSTRING(tbl, 0, 1);
+ e := '';
+ IF ids <> '' THEN
+ e := ' WHERE id IN('||ids||')';
+ END IF;
+ FOR r IN EXECUTE 'SELECT id FROM '||tbl||e LOOP
+ i := 0;
+ FOR r2 IN EXECUTE 'SELECT id FROM '||tbl||'_rev WHERE '||t||'id = '||r.id||' ORDER BY id ASC' LOOP
+ UPDATE changes SET prev = i WHERE id = r2.id;
+ i := r2.id;
+ END LOOP;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+-- /what/ bitflags: released, languages, votes
+-- Typical yorhel-code: ugly...
+CREATE OR REPLACE FUNCTION update_vncache(what integer, id integer) RETURNS void AS $$
+DECLARE
+ s text := '';
+ w text := '';
+BEGIN
+ IF what < 1 OR what > 7 THEN
+ RETURN;
+ END IF;
+ IF what & 1 = 1 THEN
+ s := 'c_released = COALESCE((SELECT
+ SUBSTRING(COALESCE(MIN(rr1.released), ''0000-00'') from 1 for 7)
+ FROM releases r1
+ JOIN releases_rev rr1 ON r1.latest = rr1.id
+ WHERE r1.vid = vn.id
+ AND rr1.type <> 2
+ GROUP BY r1.vid
+ ), ''0000-00'')';
+ END IF;
+ IF what & 2 = 2 THEN
+ IF s <> '' THEN
+ s := s||', ';
+ END IF;
+ s := s||'c_languages = COALESCE(ARRAY_TO_STRING(ARRAY(
+ SELECT language
+ FROM releases r2
+ JOIN releases_rev rr2 ON r2.latest = rr2.id
+ WHERE r2.vid = vn.id
+ AND rr2.type <> 2
+ GROUP BY rr2.language
+ ORDER BY rr2.language
+ ), ''/''), '''')';
+ END IF;
+ IF what & 4 = 4 THEN
+ IF s <> '' THEN
+ s := s||', ';
+ END IF;
+ s := s||'c_votes = COALESCE((SELECT
+ TO_CHAR(CASE WHEN COUNT(uid) < 2 THEN 0 ELSE AVG(vote) END, ''FM00D0'')||''|''||TO_CHAR(COUNT(uid), ''FM0000'')
+ FROM votes
+ WHERE vid = vn.id
+ GROUP BY vid
+ ), ''00.0|0000'')';
+ END IF;
+ IF id > 0 THEN
+ w := ' WHERE id = '||id;
+ END IF;
+ EXECUTE 'UPDATE vn SET '||s||w;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+