diff options
author | Yorhel <git@yorhel.nl> | 2010-01-01 19:01:38 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2010-01-01 19:01:38 +0100 |
commit | 81380525f2c4385577019ae964039dcf3c791625 (patch) | |
tree | bb39439553ecbf76d34b971c688225d5d2047238 | |
parent | 0a4395eb4b186ea91e7a69d1556cb50352ade7f7 (diff) |
SQL: Revision insertion abstraction for producer entries
-rw-r--r-- | lib/VNDB/DB/Misc.pm | 2 | ||||
-rw-r--r-- | lib/VNDB/DB/Producers.pm | 25 | ||||
-rw-r--r-- | lib/VNDB/Handler/Producers.pm | 19 | ||||
-rw-r--r-- | util/updates/update_2.10.sql | 39 |
4 files changed, 58 insertions, 27 deletions
diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm index 4b14d485..02df0a2b 100644 --- a/lib/VNDB/DB/Misc.pm +++ b/lib/VNDB/DB/Misc.pm @@ -33,7 +33,7 @@ sub dbItemEdit { $o{uid}||$self->authInfo->{id}, $self->reqIP, $o{editsum}); $self->dbVNRevisionInsert( \%o) if $type eq 'v'; - #$self->dbProducerRevisionInsert(\%o) if $type eq 'p'; + $self->dbProducerRevisionInsert(\%o) if $type eq 'p'; $self->dbReleaseRevisionInsert( \%o) if $type eq 'r'; return $self->dbRow('SELECT * FROM edit_!s_commit()', $fun); diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm index 4a666350..f32f70db 100644 --- a/lib/VNDB/DB/Producers.pm +++ b/lib/VNDB/DB/Producers.pm @@ -100,22 +100,21 @@ sub dbProducerGet { } -# inserts a producer revision, called from dbItemEdit() or dbItemAdd() -# Arguments: global revision, item id, { columns in producers_rev + relations }, +# Updates the edit_* tables, used from dbItemEdit() +# Arguments: { columns in producers_rev + relations }, sub dbProducerRevisionInsert { - my($self, $cid, $pid, $o) = @_; + my($self, $o) = @_; - $self->dbExec(q| - INSERT INTO producers_rev (id, pid, name, original, website, l_wp, type, lang, "desc", alias) - VALUES (!l)|, - [ $cid, $pid, @$o{qw| name original website l_wp type lang desc alias|} ] - ); + my %set = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (), + qw|name original website l_wp type lang desc alias|; + $self->dbExec('UPDATE edit_producer !H', \%set) if keys %set; - $self->dbExec(q| - INSERT INTO producers_relations (pid1, pid2, relation) - VALUES (?, ?, ?)|, - $cid, $_->[1], $_->[0] - ) for (@{$o->{relations}}); + if($o->{relations}) { + $self->dbExec('DELETE FROM edit_producer_relations'); + my $q = join ',', map '(?,?)', @{$o->{relations}}; + my @q = map +($_->[1], $_->[0]), @{$o->{relations}}; + $self->dbExec("INSERT INTO edit_producer_relations (pid, relation) VALUES $q", @q) if @q; + } } diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm index 339fe8e0..cbc37a5c 100644 --- a/lib/VNDB/Handler/Producers.pm +++ b/lib/VNDB/Handler/Producers.pm @@ -186,19 +186,16 @@ sub edit { $frm->{relations} = $relations; $frm->{l_wp} = undef if !$frm->{l_wp}; - $rev = 1; - my $npid = $pid; - ($rev) = $self->dbItemEdit(p => $pid, %$frm) if $pid; - ($npid) = $self->dbItemAdd(p => %$frm) if !$pid; + my $nrev = $self->dbItemEdit(p => $pid ? $p->{cid} : undef, %$frm); # 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, $rev); + _updreverse($self, \%old, \%new, $nrev->{iid}, $nrev->{rev}); } - return $self->resRedirect("/p$npid.$rev", 'post'); + return $self->resRedirect("/p$nrev->{iid}.$nrev->{rev}", 'post'); } } @@ -256,8 +253,6 @@ sub edit { $self->htmlFooter; } -# !IMPORTANT!: Don't forget to update this function when -# adding/removing fields to/from producer entries! sub _updreverse { my($self, $old, $new, $pid, $rev) = @_; my %upd; @@ -270,19 +265,17 @@ sub _updreverse { $upd{$_} = $self->{prod_relations}{$$new{$_}}[1]; } } - return if !keys %upd; # edit all related producers for my $i (keys %upd) { - my $r = $self->dbProducerGet(id => $i, what => 'extended relations')->[0]; + my $r = $self->dbProducerGet(id => $i, what => 'relations')->[0]; my @newrel = map $_->{id} != $pid ? [ $_->{relation}, $_->{id} ] : (), @{$r->{relations}}; push @newrel, [ $upd{$i}, $pid ] if $upd{$i}; - $self->dbItemEdit(p => $i, + $self->dbItemEdit(p => $r->{cid}, relations => \@newrel, editsum => "Reverse relation update caused by revision p$pid.$rev", - uid => 1, # Multi - hardcoded - ( map { $_ => $r->{$_} } qw|type name original lang website desc alias| ) + uid => 1, ); } } diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql index 4ae4160b..2219bb57 100644 --- a/util/updates/update_2.10.sql +++ b/util/updates/update_2.10.sql @@ -362,3 +362,42 @@ END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION edit_producer_init(cid integer) RETURNS void AS $$ +BEGIN + CREATE TEMPORARY TABLE edit_producer (LIKE producers_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_producer DROP COLUMN id; + ALTER TABLE edit_producer DROP COLUMN pid; + CREATE TEMPORARY TABLE edit_producer_relations (LIKE producers_relations INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_producer_relations DROP COLUMN pid1; + ALTER TABLE edit_producer_relations RENAME COLUMN pid2 TO pid; + -- new producer + IF cid IS NULL THEN + PERFORM edit_revtable('p', NULL); + INSERT INTO edit_producer DEFAULT VALUES; + -- load revision + ELSE + PERFORM edit_revtable('p', (SELECT pid FROM producers_rev WHERE id = cid)); + INSERT INTO edit_producer SELECT type, name, original, website, lang, "desc", alias, l_wp FROM producers_rev WHERE id = cid; + INSERT INTO edit_producer_relations SELECT pid2, relation FROM producers_relations WHERE pid1 = cid; + END IF; +END; +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION edit_producer_commit() RETURNS edit_rettype AS $$ +DECLARE + r edit_rettype; +BEGIN + IF (SELECT COUNT(*) FROM edit_producer) <> 1 THEN + RAISE 'edit_producer must have exactly one row!'; + END IF; + SELECT INTO r * FROM edit_commit(); + INSERT INTO producers_rev SELECT r.cid, r.iid, type, name, original, website, lang, "desc", alias, l_wp FROM edit_producer; + INSERT INTO producers_relations SELECT r.cid, pid, relation FROM edit_producer_relations; + UPDATE producers SET latest = r.cid WHERE id = r.iid; + DROP TABLE edit_revision, edit_producer, edit_producer_relations; + RETURN r; +END; +$$ LANGUAGE plpgsql; + + |