From 6daab15dc03a82ac6f782edbe4c509e2fedf5746 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 5 Dec 2009 18:51:14 +0100 Subject: SQL: Removed changes.causedby This column was used to differentiate between automated edits and user edits, but that later changed to checking for changes.requester = 1. The column has since never really been used, and due to a bug introduced in VNDB 2.0, it has never been updated, either. Meaning it's not even accurate for any database changes made after december 2008... --- ChangeLog | 1 + lib/VNDB/DB/Misc.pm | 2 +- lib/VNDB/DB/VN.pm | 2 +- lib/VNDB/Handler/Producers.pm | 10 ++++------ lib/VNDB/Handler/VNEdit.pm | 15 +++++++-------- util/dump.sql | 4 +--- util/updates/update_2.10.sql | 11 +++++++++++ 7 files changed, 26 insertions(+), 19 deletions(-) diff --git a/ChangeLog b/ChangeLog index 2fe5e568..1aa307db 100644 --- a/ChangeLog +++ b/ChangeLog @@ -10,6 +10,7 @@ git - ? - Merged db[VN|Producer|Release][Edit|Add] into dbItemEdit and dbItemAdd - Removed the use of CONSTRAINT TRIGGERs - Added maxlength check on the website fields for releases and producers + - Removed changes.causedby 2.9 - 2009-11-16 - Fixed another bug with the calculation of tags_vn_bayesian.spoiler diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm index ec8d375e..a33ade8a 100644 --- a/lib/VNDB/DB/Misc.pm +++ b/lib/VNDB/DB/Misc.pm @@ -134,7 +134,7 @@ sub dbRevisionGet { ); my @select = ( - qw|c.id c.type c.requester c.comments c.rev c.causedby|, + qw|c.id c.type c.requester c.comments c.rev|, q|extract('epoch' from c.added) as added|, $o{what} =~ /user/ ? 'u.username' : (), $o{what} =~ /item/ ? ( diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index bc99f17c..2c11d78e 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -86,7 +86,7 @@ sub dbVNGet { $o{what} =~ /extended/ ? ( qw|vr.alias vr.image vr.img_nsfw vr.length vr.desc vr.l_wp vr.l_encubed vr.l_renai vr.l_vnn| ) : (), $o{what} =~ /changes/ ? ( - qw|c.requester c.comments v.latest u.username c.rev c.causedby|, q|extract('epoch' from c.added) as added|) : (), + qw|c.requester c.comments v.latest u.username c.rev|, q|extract('epoch' from c.added) as added|) : (), $o{what} =~ /relgraph/ ? 'vg.svg' : (), $o{what} =~ /rating/ ? (qw|v.c_popularity v.c_rating v.c_votecount|) : (), $o{what} =~ /ranking/ ? ( diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm index 18fde2f6..d3f570ab 100644 --- a/lib/VNDB/Handler/Producers.pm +++ b/lib/VNDB/Handler/Producers.pm @@ -187,15 +187,14 @@ sub edit { $frm->{l_wp} = undef if !$frm->{l_wp}; $rev = 1; my $npid = $pid; - my $cid; - ($rev, $cid) = $self->dbItemEdit(p => $pid, %$frm) if $pid; - ($npid, $cid) = $self->dbItemAdd(p => %$frm) if !$pid; + ($rev) = $self->dbItemEdit(p => $pid, %$frm) if $pid; + ($npid) = $self->dbItemAdd(p => %$frm) if !$pid; # update reverse relations if(!$pid && $#$relations >= 0 || $pid && $frm->{prodrelations} ne $b4{prodrelations}) { my %old = $pid ? (map { $_->{id} => $_->{relation} } @{$p->{relations}}) : (); my %new = map { $_->[1] => $_->[0] } @$relations; - _updreverse($self, \%old, \%new, $npid, $cid, $rev); + _updreverse($self, \%old, \%new, $npid, $rev); } return $self->resRedirect("/p$npid.$rev", 'post'); @@ -259,7 +258,7 @@ sub edit { # !IMPORTANT!: Don't forget to update this function when # adding/removing fields to/from producer entries! sub _updreverse { - my($self, $old, $new, $pid, $cid, $rev) = @_; + my($self, $old, $new, $pid, $rev) = @_; my %upd; # compare %old and %new @@ -281,7 +280,6 @@ sub _updreverse { $self->dbItemEdit(p => $i, relations => \@newrel, editsum => "Reverse relation update caused by revision p$pid.$rev", - causedby => $cid, uid => 1, # Multi - hardcoded ( map { $_ => $r->{$_} } qw|type name original lang website desc alias| ) ); diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm index 531f39d6..4e69ce68 100644 --- a/lib/VNDB/Handler/VNEdit.pm +++ b/lib/VNDB/Handler/VNEdit.pm @@ -78,15 +78,15 @@ sub edit { screenshots => $screenshots, ); - my($nvid, $nrev, $cid) = ($vid, 1); - ($nrev, $cid) = $self->dbItemEdit(v => $vid, %args) if $vid; - ($nvid, $cid) = $self->dbItemAdd(v =>%args) if !$vid; + 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, $cid, $nrev); + _updreverse($self, \%old, \%new, $nvid, $nrev); } return $self->resRedirect("/v$nvid.$nrev", 'post'); @@ -236,14 +236,14 @@ sub _form { # Update reverse relations and regenerate relation graph -# Arguments: %old. %new, vid, cid, rev +# Arguments: %old. %new, vid, rev # %old,%new -> { vid2 => relation, .. } # from the perspective of vid -# cid, rev are of the related edit +# 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, $cid, $rev) = @_; + my($self, $old, $new, $vid, $rev) = @_; my %upd; # compare %old and %new @@ -265,7 +265,6 @@ sub _updreverse { $self->dbItemEdit(v => $i, relations => \@newrel, editsum => "Reverse relation update caused by revision v$vid.$rev", - causedby => $cid, uid => 1, # Multi - hardcoded anime => [ map $_->{id}, @{$r->{anime}} ], screenshots => [ map [ $_->{id}, $_->{nsfw}, $_->{rid} ], @{$r->{screenshots}} ], diff --git a/util/dump.sql b/util/dump.sql index 2e2b6cf7..66db690c 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -38,8 +38,7 @@ CREATE TABLE changes ( added timestamptz NOT NULL DEFAULT NOW(), requester integer NOT NULL DEFAULT 0, ip inet NOT NULL DEFAULT '0.0.0.0', - comments text NOT NULL DEFAULT '', - causedby integer + comments text NOT NULL DEFAULT '' ); -- producers @@ -365,7 +364,6 @@ CREATE TABLE wlists ( ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id); -ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes (id); ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE producers ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id); ALTER TABLE producers_relations ADD FOREIGN KEY (pid1) REFERENCES producers_rev (id); diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql index e034b6e8..ad2dd1c7 100644 --- a/util/updates/update_2.10.sql +++ b/util/updates/update_2.10.sql @@ -182,3 +182,14 @@ $$ LANGUAGE plpgsql; CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE release_vncache_update(); +-- remove changes.causedby and give the affected changes to Multi +UPDATE changes SET requester = 1 WHERE causedby IS NOT NULL; +ALTER TABLE changes DROP COLUMN causedby; +UPDATE users SET + c_changes = COALESCE(( + SELECT COUNT(id) + FROM changes + WHERE requester = users.id + GROUP BY requester + ), 0); + -- cgit v1.2.3