diff options
-rw-r--r-- | data/tpl/vnpage | 2 | ||||
-rw-r--r-- | data/tpl/vnpage_rg | 2 | ||||
-rw-r--r-- | lib/ChangeLog | 1 | ||||
-rw-r--r-- | lib/Multi/RG.pm | 21 | ||||
-rw-r--r-- | lib/VNDB/Releases.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/Util/DB.pm | 16 | ||||
-rw-r--r-- | lib/VNDB/VN.pm | 15 | ||||
-rw-r--r-- | lib/global.pl | 1 | ||||
-rw-r--r-- | util/dump.sql | 10 | ||||
-rwxr-xr-x | util/updates/update_1.22.pl | 28 | ||||
-rw-r--r-- | util/updates/update_1.22.sql | 14 |
11 files changed, 75 insertions, 39 deletions
diff --git a/data/tpl/vnpage b/data/tpl/vnpage index e77b5dc0..2f6edd4f 100644 --- a/data/tpl/vnpage +++ b/data/tpl/vnpage @@ -161,7 +161,7 @@ if($d{vn}{length} || $d{vn}{alias} || @links || $prod) { ]] @{$d{vn}{screenshots}} ? ( $d{page} eq 'scr' ? '<b>screenshots</b>' : '<a href="/v'.$d{vn}{id}.'/scr">screenshots</a>', ) : (), - $d{vn}{rgraph} ? ( + @{$d{vn}{relations}} ? ( $d{page} eq 'rg' ? '<b>relations</b>' : '<a href="/v'.$d{vn}{id}.'/rg">relations</a>', ) : (), ); diff --git a/data/tpl/vnpage_rg b/data/tpl/vnpage_rg index 832c4617..11790e42 100644 --- a/data/tpl/vnpage_rg +++ b/data/tpl/vnpage_rg @@ -4,7 +4,7 @@ Relation graph has not been generated yet... </p> [[ } else { ]] - [[= $d{vn}{rmap} ]] + [[= $d{vn}{cmap} ]] <p id="relations"> <img src="[[= sprintf "%s/rg/%02d/%d.gif", $p{st}, $d{vn}{rgraph}%100, $d{vn}{rgraph} ]]" usemap="#rgraph" alt="Relation graph for -[[: $d{vn}{title} ]]" /> </p> diff --git a/lib/ChangeLog b/lib/ChangeLog index 594f128b..7bd2becc 100644 --- a/lib/ChangeLog +++ b/lib/ChangeLog @@ -8,6 +8,7 @@ TODO: 1.22 - ? - Inverted vote graph + - Relation graph image maps are now stored in the DB 1.21 - 2008-08-16 (r90) - Added !vn and !uptime commands to Multi::IRC diff --git a/lib/Multi/RG.pm b/lib/Multi/RG.pm index d94eb484..141aca34 100644 --- a/lib/Multi/RG.pm +++ b/lib/Multi/RG.pm @@ -113,7 +113,7 @@ sub getrel { # vid if(!grep !$_->[4], values %{$_[HEAP]{nodes}}) { if(!keys %{$_[HEAP]{nodes}}) { $_[KERNEL]->call(core => log => 3, 'No relation graph for v%d', $_[HEAP]{vid}); - $Multi::SQL->do('UPDATE vn SET rgraph = 0 WHERE id = ?', undef, $_[HEAP]{vid}); + $Multi::SQL->do('UPDATE vn SET rgraph = NULL WHERE id = ?', undef, $_[HEAP]{vid}); $_[HEAP]{nodes}{$_[HEAP]{vid}} = []; $_[KERNEL]->yield('completegraph'); return; @@ -126,7 +126,7 @@ sub getrel { # vid sub builddot { my $gv = - qq|graph {\n|. + qq|graph rgraph {\n|. qq|\tratio = "compress"\n|. qq|\tnode [ fontname = "$_[HEAP]{font}", shape = "plaintext",|. qq| fontsize = $_[HEAP]{fsize}[0], style = "setlinewidth(0.5)" ]\n|. @@ -188,20 +188,19 @@ sub builddot { sub buildgraph { # get a new ID - my $gid = $Multi::SQL->prepare("SELECT nextval('relgraph_seq')"); + my $gid = $Multi::SQL->prepare("INSERT INTO relgraph (cmap) VALUES ('') RETURNING id"); $gid->execute; $gid = $gid->fetchrow_arrayref->[0]; $_[HEAP]{gid} = [ $gid, sprintf('%s/%02d/%d.gif', $_[HEAP]{imgdir}, $gid % 100, $gid), - sprintf('%s/%02d/%d.cmap', $_[HEAP]{datdir}, $gid % 100, $gid) ]; # roughly equivalent to: - # cat layout.txt | dot -Tgif -o graph.gif -Tcmap > graph.cmap + # cat layout.txt | dot -Tgif -o graph.gif -Tcmapx $_[HEAP]{proc} = POE::Wheel::Run->new( Program => $_[HEAP]{dot}, - ProgramArgs => [ '-Tgif', '-o', $_[HEAP]{gid}[1], '-Tcmap' ], + ProgramArgs => [ '-Tgif', '-o', $_[HEAP]{gid}[1], '-Tcmapx' ], StdioFilter => POE::Filter::Stream->new(), StdinEvent => 'proc_stdin', StdoutEvent => 'proc_stdout', @@ -215,16 +214,10 @@ sub buildgraph { sub savegraph { # save the image map - $_[HEAP]{cmap} =~ s{>\n}{ />\n}g; # make XML parsers happy - open my $F, '>', $_[HEAP]{gid}[2] or die $!; - print $F '<!-- V:'.join(',',keys %{$_[HEAP]{nodes}})." -->\n"; - print $F '<map id="rgraph" name="rgraph">'."\n"; - print $F $_[HEAP]{cmap}; - print $F '</map>'; - close $F; + $Multi::SQL->do('UPDATE relgraph SET cmap = ? WHERE id = ?', undef, + '<!-- V:'.join(',',keys %{$_[HEAP]{nodes}})." -->\n$_[HEAP]{cmap}", $_[HEAP]{gid}[0]); # proper chmod - chmod 0666, $_[HEAP]{gid}[2]; chmod 0666, $_[HEAP]{gid}[1]; # update the VN table diff --git a/lib/VNDB/Releases.pm b/lib/VNDB/Releases.pm index 947ed770..0f36c4fe 100644 --- a/lib/VNDB/Releases.pm +++ b/lib/VNDB/Releases.pm @@ -172,11 +172,11 @@ sub RHide { sub RVNCache { # @vids - calls update_vncache and regenerates relation graphs if needed my($self, @vns) = @_; - my $before = $self->DBGetVN(id => \@vns, order => 'v.id'); + my $before = $self->DBGetVN(id => \@vns, order => 'v.id', what => 'relations'); $self->DBVNCache(@vns); my $after = $self->DBGetVN(id => \@vns, order => 'v.id'); my @upd = map { - $before->[$_]{rgraph} && ( + @{$before->[$_]{relations}} && ( $before->[$_]{c_released} != $after->[$_]{c_released} || $before->[$_]{c_languages} ne $after->[$_]{c_languages} ) ? $before->[$_]{id} : (); diff --git a/lib/VNDB/Util/DB.pm b/lib/VNDB/Util/DB.pm index 4063eb2a..d25a1e5b 100644 --- a/lib/VNDB/Util/DB.pm +++ b/lib/VNDB/Util/DB.pm @@ -18,7 +18,7 @@ $VERSION = $VNDB::VERSION; DBGetVNList DBDelVNList DBGetWishList DBEditWishList DBDelWishList DBGetRList DBGetRLists DBEditRList DBDelRList - DBGetVN DBAddVN DBEditVN DBHideVN DBUndefRG DBVNCache + DBGetVN DBAddVN DBEditVN DBHideVN DBVNCache DBGetRelease DBAddRelease DBEditRelease DBHideRelease DBGetProducer DBGetProducerVN DBAddProducer DBEditProducer DBHideProducer DBGetThreads DBGetPosts DBAddPost DBEditPost DBEditThread DBAddThread @@ -773,11 +773,14 @@ sub DBGetVN { # %options->{ id rev char search order results page what cati cate $o{what} =~ /changes/ || $o{rev} ? ( 'JOIN changes c ON c.id = vr.id', 'JOIN users u ON u.id = c.requester' ) : (), + $o{what} =~ /relgraph/ ? ( + 'LEFT JOIN relgraph rg ON rg.id = v.rgraph' ) : (), ); - my $sel = 'v.id, v.locked, v.hidden, v.c_released, v.c_languages, v.c_platforms, vr.title, vr.id AS cid, v.rgraph'; + my $sel = 'v.id, v.locked, v.hidden, v.c_released, v.c_languages, v.c_platforms, vr.title, vr.id AS cid'; $sel .= ', vr.alias, vr.image AS image, vr.img_nsfw, vr.length, vr.desc, vr.l_wp, vr.l_encubed, vr.l_renai, vr.l_vnn' if $o{what} =~ /extended/; $sel .= ', c.added, c.requester, c.comments, v.latest, u.username, c.rev, c.causedby' if $o{what} =~ /changes/; + $sel .= ', v.rgraph, rg.cmap' if $o{what} =~ /relgraph/; my $r = $s->DBAll(qq| SELECT $sel @@ -970,14 +973,7 @@ sub DBVNCache { # @vids } -sub DBUndefRG { # ids - my($s, @id) = @_; - $s->DBExec(q| - UPDATE vn - SET rgraph = 0 - WHERE id IN(!l)|, - \@id); -} + #-----------------------------------------------------------------------------# diff --git a/lib/VNDB/VN.pm b/lib/VNDB/VN.pm index 666349f7..9c872619 100644 --- a/lib/VNDB/VN.pm +++ b/lib/VNDB/VN.pm @@ -20,14 +20,18 @@ sub VNPage { return $self->ResNotFound if $self->ReqParam('rev'); + my $what = 'extended relations categories anime screenshots'; + $what .= ' changes' if $rev; + $what .= ' relgraph' if $page eq 'rg'; + my $v = $self->DBGetVN( id => $id, - what => 'extended relations categories anime screenshots'.($rev ? ' changes' : ''), + what => $what, $rev ? ( rev => $rev ) : () )->[0]; return $self->ResNotFound if !$v->{id}; - my $c = $rev && $rev > 1 && $self->DBGetVN(id => $id, rev => $rev-1, what => 'extended changes relations categories anime screenshots')->[0]; + my $c = $rev && $rev > 1 && $self->DBGetVN(id => $id, rev => $rev-1, what => $what)->[0]; $v->{next} = $rev && $v->{latest} > $v->{cid} ? $rev+1 : 0; my $rel = $self->DBGetRelease(vid => $id, what => 'producers platforms'); @@ -41,12 +45,7 @@ sub VNPage { my $r = (grep $i->{rid} == $_->{id}, @$rel)[0]; $r->{rlist} = $i; } - - } elsif($page eq 'rg' && $v->{rgraph}) { - open(my $F, '<:utf8', sprintf '%s/%02d/%d.cmap', $self->{mappath}, $v->{rgraph}%100, $v->{rgraph}) || die $!; - $v->{rmap} = join('', (<$F>)); - close($F); - } + } $self->ResAddTpl(vnpage => { vote => $self->AuthInfo->{id} ? $self->DBGetVotes(uid => $self->AuthInfo->{id}, vid => $id)->[0] : {}, diff --git a/lib/global.pl b/lib/global.pl index ba5607d2..3e437704 100644 --- a/lib/global.pl +++ b/lib/global.pl @@ -41,7 +41,6 @@ our %VNDBopts = ( imgpath => '/www/vndb/static/cv', # cover images sfpath => '/www/vndb/static/sf', # full-size screenshots stpath => '/www/vndb/static/st', # screenshot thumbnails - mappath => '/www/vndb/data/rg', # image maps for the relation graphs docpath => '/www/vndb/data/docs', ); $VNDBopts{ranks}[0][1] = { (map{$_,1} map { keys %{$VNDBopts{ranks}[$_]} } 1..5) }; diff --git a/util/dump.sql b/util/dump.sql index 3294179e..abb04935 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -113,6 +113,12 @@ CREATE TABLE releases_vn ( PRIMARY KEY(rid, vid) ); +-- relgraph +CREATE TABLE relgraph ( + id SERIAL NOT NULL PRIMARY KEY, + cmap text NOT NULL DEFAULT '' +); + -- rlists CREATE TABLE rlists ( uid integer NOT NULL DEFAULT 0, @@ -177,7 +183,7 @@ CREATE TABLE vn ( latest integer NOT NULL DEFAULT 0, locked boolean NOT NULL DEFAULT FALSE, hidden boolean NOT NULL DEFAULT FALSE, - rgraph integer NOT NULL DEFAULT 0, + rgraph integer, c_released integer NOT NULL DEFAULT 0, c_languages varchar(32) NOT NULL DEFAULT '', c_platforms varchar(32) NOT NULL DEFAULT '' @@ -287,6 +293,7 @@ ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE threads_tags ADD FOREIGN KEY (tid) REFERENCES threads (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE vn_anime ADD FOREIGN KEY (vid) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE vn_categories ADD FOREIGN KEY (vid) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; @@ -399,7 +406,6 @@ $$ LANGUAGE plpgsql; -- Sequences used for ID generation of items not in the DB CREATE SEQUENCE covers_seq; -CREATE SEQUENCE relgraph_seq; INSERT INTO users (id, username, mail, rank) VALUES (0, 'deleted', 'del@vndb.org', 0); diff --git a/util/updates/update_1.22.pl b/util/updates/update_1.22.pl new file mode 100755 index 00000000..465c8c40 --- /dev/null +++ b/util/updates/update_1.22.pl @@ -0,0 +1,28 @@ +#!/usr/bin/perl + + +# update_1.22.sql must be executed before this script + + +use strict; +use warnings; +use DBI; + +BEGIN { require '/www/vndb/lib/global.pl' } + +my $sql = DBI->connect(@VNDB::DBLOGIN, + { PrintError => 1, RaiseError => 1, AutoCommit => 0 }); + +my $q = $sql->prepare('INSERT INTO relgraph (id, cmap) VALUES(?,?)'); +for (glob "/www/vndb/data/rg/*/*.cmap") { + my $id = $1 if /([0-9]+)\.cmap$/; + open my $F, '<', $_ or die $!; + $q->execute($id, join "\n", <$F>); + close $F; +} + +$sql->do('ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (id) DEFERRABLE INITIALLY DEFERRED'); +$sql->commit; + +# it's now safe to delete /data/rg + diff --git a/util/updates/update_1.22.sql b/util/updates/update_1.22.sql new file mode 100644 index 00000000..cc475b32 --- /dev/null +++ b/util/updates/update_1.22.sql @@ -0,0 +1,14 @@ + +-- store relation graph image maps in the database +CREATE TABLE relgraph ( + id SERIAL NOT NULL PRIMARY KEY, + cmap text NOT NULL DEFAULT '' +) WITHOUT OIDS; + +SELECT SETVAL('relgraph_id_seq', NEXTVAL('relgraph_seq')); +DROP SEQUENCE relgraph_seq; + +ALTER TABLE vn ALTER COLUMN rgraph DROP NOT NULL; +ALTER TABLE vn ALTER COLUMN rgraph SET DEFAULT NULL; +UPDATE vn SET rgraph = NULL WHERE rgraph = 0; + |