summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--data/tpl/vnpage2
-rw-r--r--data/tpl/vnpage_rg2
-rw-r--r--lib/ChangeLog1
-rw-r--r--lib/Multi/RG.pm21
-rw-r--r--lib/VNDB/Releases.pm4
-rw-r--r--lib/VNDB/Util/DB.pm16
-rw-r--r--lib/VNDB/VN.pm15
-rw-r--r--lib/global.pl1
-rw-r--r--util/dump.sql10
-rwxr-xr-xutil/updates/update_1.22.pl28
-rw-r--r--util/updates/update_1.22.sql14
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;
+