summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--lib/VNDB/DB/Misc.pm73
-rw-r--r--lib/VNDB/DB/VN.pm55
-rw-r--r--lib/VNDB/Handler/VNEdit.pm24
-rw-r--r--util/updates/update_2.10.sql110
4 files changed, 160 insertions, 102 deletions
diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm
index a33ade8a..b1f79705 100644
--- a/lib/VNDB/DB/Misc.pm
+++ b/lib/VNDB/DB/Misc.pm
@@ -6,7 +6,7 @@ use warnings;
use Exporter 'import';
our @EXPORT = qw|
- dbStats dbItemEdit dbItemAdd dbRevisionGet dbItemMod dbRandomQuote
+ dbStats dbItemEdit dbRevisionGet dbItemMod dbRandomQuote
|;
@@ -20,67 +20,24 @@ sub dbStats {
}
-# Inserts a new revision and updates the item to point to this revision
-# Arguments: type [vrp], item ID, %options->{ editsum uid + db[item]RevisionInsert }
-# Returns: local revision, global revision
+# Inserts a new revision into the database
+# Arguments: type [vrp], revision id, %options->{ editsum uid + db[item]RevisionInsert }
+# revision id = changes.id of the revision this edit is based on, undef to create a new DB item
+# Returns: { iid, cid, rev }
sub dbItemEdit {
- my($self, $type, $iid, %o) = @_;
-
- my $table = {qw|v vn r releases p producers|}->{$type};
-
- my $c = $self->dbRow(q|
- INSERT INTO changes (type, requester, ip, comments, rev)
- VALUES (?, ?, ?, ?, (
- SELECT c.rev+1
- FROM changes c
- JOIN !s_rev ir ON ir.id = c.id
- WHERE ir.!sid = ?
- ORDER BY c.id DESC
- LIMIT 1
- ))
- RETURNING id, rev|,
- $type, $o{uid}||$self->authInfo->{id}, $self->reqIP, $o{editsum},
- $table, $type, $iid
- );
-
- $self->dbVNRevisionInsert( $c->{id}, $iid, \%o) if $type eq 'v';
- $self->dbProducerRevisionInsert($c->{id}, $iid, \%o) if $type eq 'p';
- $self->dbReleaseRevisionInsert( $c->{id}, $iid, \%o) if $type eq 'r';
-
- $self->dbExec(q|UPDATE !s SET latest = ? WHERE id = ?|, $table, $c->{id}, $iid);
- return ($c->{rev}, $c->{id});
-}
-
-
-# Comparable to dbItemEdit(), but creates a new item with a corresponding revision.
-# Argumments: type [vrp] + same option hash as dbItemEdit()
-# Returns: item id, global revision
-sub dbItemAdd {
- my($self, $type, %o) = @_;
-
- my $table = {qw|v vn r releases p producers|}->{$type};
-
- my $cid = $self->dbRow(q|
- INSERT INTO changes (type, requester, ip, comments)
- VALUES (?, ?, ?, ?)
- RETURNING id|,
- $type, $o{uid}||$self->authInfo->{id}, $self->reqIP, $o{editsum}
- )->{id};
-
- my $iid = $self->dbRow(q|
- INSERT INTO !s (latest)
- VALUES (0)
- RETURNING id|,
- $table
- )->{id};
+ my($self, $type, $oid, %o) = @_;
- $self->dbVNRevisionInsert( $cid, $iid, \%o) if $type eq 'v';
- $self->dbProducerRevisionInsert($cid, $iid, \%o) if $type eq 'p';
- $self->dbReleaseRevisionInsert( $cid, $iid, \%o) if $type eq 'r';
+ die "Only VNs are supported at this moment!" if $type ne 'v';
+ $self->dbExec('SELECT edit_!s_init(?)',
+ {qw|v vn r releases p producers|}->{$type}, $oid);
+ $self->dbExec('UPDATE edit_revision SET requester = ?, ip = ?, comments = ?',
+ $o{uid}||$self->authInfo->{id}, $self->reqIP, $o{editsum});
- $self->dbExec(q|UPDATE !s SET latest = ? WHERE id = ?|, $table, $cid, $iid);
+ $self->dbVNRevisionInsert( \%o) if $type eq 'v';
+ #$self->dbProducerRevisionInsert(\%o) if $type eq 'p';
+ #$self->dbReleaseRevisionInsert( \%o) if $type eq 'r';
- return ($iid, $cid);
+ return $self->dbRow('SELECT * FROM edit_vn_commit()');
}
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index 2c11d78e..b8cc0c42 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -171,37 +171,38 @@ sub dbVNGet {
}
-# inserts a visual novel revision, used from dbItemEdit() or dbItemAdd()
-# Arguments: global revision, item id, { columns in producers_rev + anime + relations + screenshots }
+# Updates the edit_* tables, used from dbItemEdit()
+# Arguments: { columns in producers_rev + anime + relations + screenshots }
# screenshots = [ [ scrid, nsfw, rid ], .. ]
# relations = [ [ rel, vid ], .. ]
# anime = [ aid, .. ]
sub dbVNRevisionInsert {
- my($self, $cid, $vid, $o) = @_;
-
- $o->{img_nsfw} = $o->{img_nsfw}?1:0;
- $self->dbExec(q|
- INSERT INTO vn_rev (id, vid, title, original, "desc", alias, image, img_nsfw, length, l_wp, l_encubed, l_renai, l_vnn)
- VALUES (!l)|,
- [ $cid, $vid, @$o{qw|title original desc alias image img_nsfw length l_wp l_encubed l_renai l_vnn|} ]);
-
- $self->dbExec(q|
- INSERT INTO vn_screenshots (vid, scr, nsfw, rid)
- VALUES (?, ?, ?, ?)|,
- $cid, $_->[0], $_->[1]?1:0, $_->[2]
- ) for (@{$o->{screenshots}});
-
- $self->dbExec(q|
- INSERT INTO vn_relations (vid1, vid2, relation)
- VALUES (?, ?, ?)|,
- $cid, $_->[1], $_->[0]
- ) for (@{$o->{relations}});
-
- $self->dbExec(q|
- INSERT INTO vn_anime (vid, aid)
- VALUES (?, ?)|,
- $cid, $_
- ) for (@{$o->{anime}});
+ my($self, $o) = @_;
+
+ $o->{img_nsfw} = $o->{img_nsfw}?1:0 if exists $o->{img_nsfw};
+ my %set = map exists($o->{$_}) ? (qq|"$_" = ?| => $o->{$_}) : (),
+ qw|title original desc alias image img_nsfw length l_wp l_encubed l_renai l_vnn|;
+ $self->dbExec('UPDATE edit_vn !H', \%set) if keys %set;
+
+ if($o->{screenshots}) {
+ $self->dbExec('DELETE FROM edit_vn_screenshots');
+ my $q = join ',', map '(?, ?, ?)', @{$o->{screenshots}};
+ my @val = map +($_->[0], $_->[1]?1:0, $_->[2]), @{$o->{screenshots}};
+ $self->dbExec("INSERT INTO edit_vn_screenshots (scr, nsfw, rid) VALUES $q", @val) if @val;
+ }
+
+ if($o->{relations}) {
+ $self->dbExec('DELETE FROM edit_vn_relations');
+ my $q = join ',', map '(?, ?)', @{$o->{relations}};
+ my @val = map +($_->[1], $_->[0]), @{$o->{relations}};
+ $self->dbExec("INSERT INTO edit_vn_relations (vid, relation) VALUES $q", @val) if @val;
+ }
+
+ if($o->{anime}) {
+ $self->dbExec('DELETE FROM edit_vn_anime');
+ my $q = join ',', map '(?)', @{$o->{anime}};
+ $self->dbExec("INSERT INTO edit_vn_anime (aid) VALUES $q", @{$o->{anime}}) if @{$o->{anime}};
+ }
}
diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm
index 21d92e8e..d5dfd878 100644
--- a/lib/VNDB/Handler/VNEdit.pm
+++ b/lib/VNDB/Handler/VNEdit.pm
@@ -69,8 +69,8 @@ sub edit {
return $self->resRedirect("/v$vid", 'post')
if $vid && !$self->reqUploadFileName('img') && !grep $frm->{$_} ne $b4{$_}, keys %b4;
- # execute the edit/add
- my %args = (
+ # perform the edit/add
+ my $nrev = $self->dbItemEdit(v => $vid ? $v->{cid} : undef,
(map { $_ => $frm->{$_} } qw|title original alias desc length l_wp l_encubed l_renai l_vnn editsum img_nsfw|),
anime => [ keys %$anime ],
relations => $relations,
@@ -78,18 +78,14 @@ sub edit {
screenshots => $screenshots,
);
- my($nvid, $nrev) = ($vid, 1);
- ($nrev) = $self->dbItemEdit(v => $vid, %args) if $vid;
- ($nvid) = $self->dbItemAdd(v => %args) if !$vid;
-
# update reverse relations & relation graph
if(!$vid && $#$relations >= 0 || $vid && $frm->{vnrelations} ne $b4{vnrelations}) {
my %old = $vid ? (map { $_->{id} => $_->{relation} } @{$v->{relations}}) : ();
my %new = map { $_->[1] => $_->[0] } @$relations;
- _updreverse($self, \%old, \%new, $nvid, $nrev);
+ _updreverse($self, \%old, \%new, $nrev->{iid}, $nrev->{rev});
}
- return $self->resRedirect("/v$nvid.$nrev", 'post');
+ return $self->resRedirect("/v$nrev->{iid}.$nrev->{rev}", 'post');
}
}
@@ -240,8 +236,6 @@ sub _form {
# %old,%new -> { vid2 => relation, .. }
# from the perspective of vid
# rev is of the related edit
-# !IMPORTANT!: Don't forget to update this function when
-# adding/removing fields to/from VN entries!
sub _updreverse {
my($self, $old, $new, $vid, $rev) = @_;
my %upd;
@@ -254,21 +248,17 @@ sub _updreverse {
$upd{$_} = $self->{vn_relations}{$$new{$_}}[1];
}
}
-
return if !keys %upd;
# edit all related VNs
for my $i (keys %upd) {
- my $r = $self->dbVNGet(id => $i, what => 'extended relations anime screenshots')->[0];
+ my $r = $self->dbVNGet(id => $i, what => 'relations')->[0];
my @newrel = map $_->{id} != $vid ? [ $_->{relation}, $_->{id} ] : (), @{$r->{relations}};
push @newrel, [ $upd{$i}, $vid ] if $upd{$i};
- $self->dbItemEdit(v => $i,
+ $self->dbItemEdit(v => $r->{cid},
relations => \@newrel,
editsum => "Reverse relation update caused by revision v$vid.$rev",
- uid => 1, # Multi - hardcoded
- anime => [ map $_->{id}, @{$r->{anime}} ],
- screenshots => [ map [ $_->{id}, $_->{nsfw}, $_->{rid} ], @{$r->{screenshots}} ],
- ( map { $_ => $r->{$_} } qw| title original desc alias img_nsfw length l_wp l_encubed l_renai l_vnn image | )
+ uid => 1, # Multi
);
}
}
diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql
index 47a05ac5..d955a77b 100644
--- a/util/updates/update_2.10.sql
+++ b/util/updates/update_2.10.sql
@@ -190,3 +190,113 @@ UPDATE users SET
GROUP BY requester
), 0);
+
+
+
+-- revision insertion abstraction
+-- IMPORTANT: these functions will need to be updated on each change in the DB structure
+-- of the relevant tables
+
+CREATE TYPE edit_rettype AS (iid integer, cid integer, rev integer);
+
+-- create temporary table for generic revision info
+CREATE OR REPLACE FUNCTION edit_revtable(t dbentry_type, i integer) RETURNS void AS $$
+BEGIN
+ CREATE TEMPORARY TABLE edit_revision (
+ type dbentry_type NOT NULL,
+ iid integer,
+ requester integer,
+ ip inet,
+ comments text
+ );
+ INSERT INTO edit_revision (type, iid) VALUES (t, i);
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION edit_commit() RETURNS edit_rettype AS $$
+DECLARE
+ r edit_rettype;
+ t dbentry_type;
+ i integer;
+BEGIN
+ SELECT type INTO t FROM edit_revision;
+ SELECT iid INTO i FROM edit_revision;
+ -- figure out revision number
+ IF i IS NULL THEN
+ r.rev := 1;
+ ELSE
+ SELECT c.rev+1 INTO r.rev FROM changes c
+ LEFT JOIN vn_rev vr ON c.id = vr.id
+ LEFT JOIN releases_rev rr ON c.id = rr.id
+ LEFT JOIN producers_rev pr ON c.id = pr.id
+ WHERE (t = 'v' AND vr.vid = i)
+ OR (t = 'r' AND rr.rid = i)
+ OR (t = 'p' AND pr.pid = i)
+ ORDER BY c.id DESC
+ LIMIT 1;
+ END IF;
+ -- insert change
+ INSERT INTO changes (type, requester, ip, comments, rev)
+ SELECT t, requester, ip, comments, r.rev
+ FROM edit_revision
+ RETURNING id INTO r.cid;
+ -- insert DB item
+ IF i IS NULL THEN
+ CASE t
+ WHEN 'v' THEN INSERT INTO vn (latest) VALUES (0) RETURNING id INTO r.iid;
+ WHEN 'r' THEN INSERT INTO releases (latest) VALUES (0) RETURNING id INTO r.iid;
+ WHEN 'p' THEN INSERT INTO producers (latest) VALUES (0) RETURNING id INTO r.iid;
+ END CASE;
+ ELSE
+ r.iid := i;
+ END IF;
+ RETURN r;
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION edit_vn_init(cid integer) RETURNS void AS $$
+BEGIN
+ -- create tables, based on existing tables (so that the column types are always synchronised)
+ CREATE TEMPORARY TABLE edit_vn (LIKE vn_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_vn DROP COLUMN id;
+ ALTER TABLE edit_vn DROP COLUMN vid;
+ CREATE TEMPORARY TABLE edit_vn_anime (LIKE vn_anime INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_vn_anime DROP COLUMN vid;
+ CREATE TEMPORARY TABLE edit_vn_relations (LIKE vn_relations INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_vn_relations DROP COLUMN vid1;
+ ALTER TABLE edit_vn_relations RENAME COLUMN vid2 TO vid;
+ CREATE TEMPORARY TABLE edit_vn_screenshots (LIKE vn_screenshots INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_vn_screenshots DROP COLUMN vid;
+ -- new VN, load defaults
+ IF cid IS NULL THEN
+ PERFORM edit_revtable('v', NULL);
+ INSERT INTO edit_vn DEFAULT VALUES;
+ -- otherwise, load revision
+ ELSE
+ PERFORM edit_revtable('v', (SELECT vid FROM vn_rev WHERE id = cid));
+ INSERT INTO edit_vn SELECT title, alias, img_nsfw, length, "desc", l_wp, l_vnn, image, l_encubed, l_renai, original FROM vn_rev WHERE id = cid;
+ INSERT INTO edit_vn_anime SELECT aid FROM vn_anime WHERE vid = cid;
+ INSERT INTO edit_vn_relations SELECT vid2, relation FROM vn_relations WHERE vid1 = cid;
+ INSERT INTO edit_vn_screenshots SELECT scr, nsfw, rid FROM vn_screenshots WHERE vid = cid;
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION edit_vn_commit() RETURNS edit_rettype AS $$
+DECLARE
+ r edit_rettype;
+BEGIN
+ SELECT INTO r * FROM edit_commit();
+ INSERT INTO vn_rev SELECT r.cid, r.iid, title, alias, img_nsfw, length, "desc", l_wp, l_vnn, image, l_encubed, l_renai, original FROM edit_vn;
+ INSERT INTO vn_anime SELECT r.cid, aid FROM edit_vn_anime;
+ INSERT INTO vn_relations SELECT r.cid, vid, relation FROM edit_vn_relations;
+ INSERT INTO vn_screenshots SELECT r.cid, scr, nsfw, rid FROM edit_vn_screenshots;
+ UPDATE vn SET latest = r.cid WHERE id = r.iid;
+ DROP TABLE edit_revision, edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots;
+ RETURN r;
+END;
+$$ LANGUAGE plpgsql;
+
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-20 07:07:48 +0000