diff options
author | yorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b> | 2008-04-13 13:45:20 +0000 |
---|---|---|
committer | yorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b> | 2008-04-13 13:45:20 +0000 |
commit | d7046f5d38004ff20739798c18f5796c31676546 (patch) | |
tree | 1639e6a8c3b74588bff7be6aaf6cf5e04e3bc63f /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.pl | 102 | ||||
-rwxr-xr-x | util/cron_daily.sh | 31 | ||||
-rw-r--r-- | util/cron_daily.sql | 15 | ||||
-rwxr-xr-x | util/relgraph.pl | 237 | ||||
-rwxr-xr-x | util/sitemap.pl | 94 | ||||
-rwxr-xr-x | util/updates/update_1.1.pl | 18 | ||||
-rw-r--r-- | util/updates/update_1.1.sql | 13 | ||||
-rw-r--r-- | util/updates/update_1.10.sql | 92 | ||||
-rw-r--r-- | util/updates/update_1.11.sql | 4 | ||||
-rw-r--r-- | util/updates/update_1.12.sql | 34 | ||||
-rw-r--r-- | util/updates/update_1.13.sql | 229 | ||||
-rwxr-xr-x | util/updates/update_1.14.pl | 57 | ||||
-rw-r--r-- | util/updates/update_1.14.sql | 76 | ||||
-rw-r--r-- | util/updates/update_1.2.sql | 9 | ||||
-rw-r--r-- | util/updates/update_1.4.sql | 37 | ||||
-rw-r--r-- | util/updates/update_1.5.sql | 10 | ||||
-rw-r--r-- | util/updates/update_1.6.sql | 21 | ||||
-rw-r--r-- | util/updates/update_1.7.sql | 23 | ||||
-rw-r--r-- | util/updates/update_1.8.sql | 27 | ||||
-rw-r--r-- | util/updates/update_1.9.sql | 375 |
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/&/&/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; + + + + |