summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2010-01-01 11:36:35 +0100
committerYorhel <git@yorhel.nl>2010-01-01 11:42:37 +0100
commitf146569cc560d64cf91e4386e3291fb45fa90c88 (patch)
treed0874cd47b868bb210c885d6c6cf4b5ce1555906
parentc5bf1eec6899e143753d470f433df5f5bb980a4b (diff)
SQL: Made a start on revision insertion abstraction
This will make it easier to do automated edits, either from cron jobs, Multi, update scripts, or from within SQL triggers. So far only the VN related functions have been defined/updated, trying to edit/add releases or producers will not work at the moment. The functions for editing or adding a new database entry have been merged, as the procedure is rather similar. util/dump.sql will be updated later on.
-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;
+