summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2015-10-21 02:19:15 +0200
committerYorhel <git@yorhel.nl>2015-10-21 02:28:48 +0200
commit9cf3eef0e0a7bde10a0e1bb8c94c38b058e5121d (patch)
tree9cbd3a439316bdfe3946cc80378e4f95a9d6e6db
parent718f4d0258049aa92f229c08d5ec7204dae3ffa6 (diff)
SQL: Fix editing + func.sql + triggers.sql + autocreate editing funcs
This changes quite a bit to the way the editing functions work. Because these functions are very repetitive and it's easy to keep things out of sync, I created a script to generate them automatically. I had to rename a few function and table names for consistency to make this work. Since database entries don't have a 'latest' column anymore, and since the order in which tables are updated doesn't have to be fixed, I dropped many of the SQL triggers and replaced them with a edit_committed() function which is called from edit_*_commit() and checks for stuff to be done. Don't forget to run 'make' before importing the update script.
-rw-r--r--.gitignore1
-rw-r--r--Makefile7
-rw-r--r--lib/VNDB/DB/Chars.pm10
-rw-r--r--lib/VNDB/DB/Misc.pm13
-rw-r--r--lib/VNDB/DB/Producers.pm6
-rw-r--r--lib/VNDB/DB/Releases.pm22
-rw-r--r--lib/VNDB/DB/Staff.pm14
-rw-r--r--lib/VNDB/Handler/Chars.pm4
-rw-r--r--lib/VNDB/Handler/Producers.pm8
-rw-r--r--lib/VNDB/Handler/Releases.pm4
-rw-r--r--lib/VNDB/Handler/Staff.pm8
-rw-r--r--lib/VNDB/Handler/VNEdit.pm14
-rw-r--r--util/sql/all.sql6
-rw-r--r--util/sql/func.sql760
-rw-r--r--util/sql/schema.sql26
-rw-r--r--util/sql/tableattrs.sql5
-rw-r--r--util/sql/triggers.sql41
-rwxr-xr-xutil/sqleditfunc.pl112
-rw-r--r--util/updates/update_2.25-sqlsplit.sql41
19 files changed, 463 insertions, 639 deletions
diff --git a/.gitignore b/.gitignore
index 356f07e8..79be4a79 100644
--- a/.gitignore
+++ b/.gitignore
@@ -14,4 +14,5 @@
/static/st
/static/robots.txt
/static/api
+/util/sql/editfunc.sql
/www/
diff --git a/Makefile b/Makefile
index 60023928..d914d504 100644
--- a/Makefile
+++ b/Makefile
@@ -48,7 +48,7 @@
update-2.10 update-2.11 update-2.12 update-2.13 update-2.14 update-2.15 update-2.16 update-2.17\
update-2.18 update-2.19 update-2.20 update-2.21 update-2.22 update-2.23
-all: dirs js skins robots data/config.pl
+all: dirs js skins robots data/config.pl util/sql/editfunc.sql
dirs: static/f/js static/ch static/cv static/sf static/st data/log www www/feeds www/api
@@ -60,6 +60,9 @@ skins: $(shell ls static/s | sed -e 's/\(.\+\)/static\/s\/\1\/style.css/g')
robots: dirs www/robots.txt static/robots.txt
+util/sql/editfunc.sql: util/sqleditfunc.pl util/sql/schema.sql
+ util/sqleditfunc.pl
+
static/ch static/cv static/sf static/st:
mkdir $@;
for i in $$(seq -w 0 1 99); do mkdir "$@/$$i"; done
@@ -138,7 +141,7 @@ define runpsql
endef
-sql-import:
+sql-import: util/sql/editfunc.sql
${runpsql} < util/sql/all.sql
diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm
index 1eaa732f..db9ae93b 100644
--- a/lib/VNDB/DB/Chars.pm
+++ b/lib/VNDB/DB/Chars.pm
@@ -165,15 +165,15 @@ sub dbCharRevisionInsert {
my %set = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (),
qw|name original alias desc image b_month b_day s_bust s_waist s_hip height weight bloodt gender main main_spoil|;
- $self->dbExec('UPDATE edit_char !H', \%set) if keys %set;
+ $self->dbExec('UPDATE edit_chars !H', \%set) if keys %set;
if($o->{traits}) {
- $self->dbExec('DELETE FROM edit_char_traits');
- $self->dbExec('INSERT INTO edit_char_traits (tid, spoil) VALUES (?,?)', $_->[0],$_->[1]) for (@{$o->{traits}});
+ $self->dbExec('DELETE FROM edit_chars_traits');
+ $self->dbExec('INSERT INTO edit_chars_traits (tid, spoil) VALUES (?,?)', $_->[0],$_->[1]) for (@{$o->{traits}});
}
if($o->{vns}) {
- $self->dbExec('DELETE FROM edit_char_vns');
- $self->dbExec('INSERT INTO edit_char_vns (vid, rid, spoil, role) VALUES(!l)', $_) for (@{$o->{vns}});
+ $self->dbExec('DELETE FROM edit_chars_vns');
+ $self->dbExec('INSERT INTO edit_chars_vns (vid, rid, spoil, role) VALUES(!l)', $_) for (@{$o->{vns}});
}
}
diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm
index efa0ab34..7c3a6e36 100644
--- a/lib/VNDB/DB/Misc.pm
+++ b/lib/VNDB/DB/Misc.pm
@@ -21,14 +21,13 @@ sub dbStats {
# Inserts a new revision into the database
-# Arguments: type [vrp], revision id, %options->{ editsum uid ihid ilock + 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 }
+# Arguments: type [vrp], itemid, rev, %options->{ editsum uid ihid ilock + db[item]RevisionInsert }
+# rev = changes.rev of the revision this edit is based on, undef to create a new DB item
+# Returns: { itemid, chid, rev }
sub dbItemEdit {
- my($self, $type, $oid, %o) = @_;
+ my($self, $type, $itemid, $rev, %o) = @_;
- my $fun = {qw|v vn r release p producer c char s staff|}->{$type};
- $self->dbExec('SELECT edit_!s_init(?)', $fun, $oid);
+ $self->dbExec('SELECT edit_!s_init(?, ?)', $type, $itemid, $rev);
$self->dbExec('UPDATE edit_revision !H', {
'requester = ?' => $o{uid}||$self->authInfo->{id},
'ip = ?' => $self->reqIP,
@@ -43,7 +42,7 @@ sub dbItemEdit {
$self->dbCharRevisionInsert( \%o) if $type eq 'c';
$self->dbStaffRevisionInsert( \%o) if $type eq 's';
- return $self->dbRow('SELECT * FROM edit_!s_commit()', $fun);
+ return $self->dbRow('SELECT * FROM edit_!s_commit()', $type);
}
diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm
index a9636282..929e333b 100644
--- a/lib/VNDB/DB/Producers.pm
+++ b/lib/VNDB/DB/Producers.pm
@@ -110,13 +110,13 @@ sub dbProducerRevisionInsert {
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('UPDATE edit_producers !H', \%set) if keys %set;
if($o->{relations}) {
- $self->dbExec('DELETE FROM edit_producer_relations');
+ $self->dbExec('DELETE FROM edit_producers_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;
+ $self->dbExec("INSERT INTO edit_producers_relations (pid, relation) VALUES $q", @q) if @q;
}
}
diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm
index 0a8f5363..eafb84c0 100644
--- a/lib/VNDB/DB/Releases.pm
+++ b/lib/VNDB/DB/Releases.pm
@@ -203,38 +203,38 @@ sub dbReleaseRevisionInsert {
my %set = map exists($o->{$_}) ? ("$_ = ?", $o->{$_}) : (),
qw|title original gtin catalog website released notes minage type
patch resolution voiced freeware doujin ani_story ani_ero|;
- $self->dbExec('UPDATE edit_release !H', \%set) if keys %set;
+ $self->dbExec('UPDATE edit_releases !H', \%set) if keys %set;
if($o->{languages}) {
- $self->dbExec('DELETE FROM edit_release_lang');
+ $self->dbExec('DELETE FROM edit_releases_lang');
my $q = join ',', map '(?)', @{$o->{languages}};
- $self->dbExec("INSERT INTO edit_release_lang (lang) VALUES $q", @{$o->{languages}}) if @{$o->{languages}};
+ $self->dbExec("INSERT INTO edit_releases_lang (lang) VALUES $q", @{$o->{languages}}) if @{$o->{languages}};
}
if($o->{producers}) {
- $self->dbExec('DELETE FROM edit_release_producers');
+ $self->dbExec('DELETE FROM edit_releases_producers');
my $q = join ',', map '(?,?,?)', @{$o->{producers}};
my @q = map +($_->[0], $_->[1]?1:0, $_->[2]?1:0), @{$o->{producers}};
- $self->dbExec("INSERT INTO edit_release_producers (pid, developer, publisher) VALUES $q", @q) if @q;
+ $self->dbExec("INSERT INTO edit_releases_producers (pid, developer, publisher) VALUES $q", @q) if @q;
}
if($o->{platforms}) {
- $self->dbExec('DELETE FROM edit_release_platforms');
+ $self->dbExec('DELETE FROM edit_releases_platforms');
my $q = join ',', map '(?)', @{$o->{platforms}};
- $self->dbExec("INSERT INTO edit_release_platforms (platform) VALUES $q", @{$o->{platforms}}) if @{$o->{platforms}};
+ $self->dbExec("INSERT INTO edit_releases_platforms (platform) VALUES $q", @{$o->{platforms}}) if @{$o->{platforms}};
}
if($o->{vn}) {
- $self->dbExec('DELETE FROM edit_release_vn');
+ $self->dbExec('DELETE FROM edit_releases_vn');
my $q = join ',', map '(?)', @{$o->{vn}};
- $self->dbExec("INSERT INTO edit_release_vn (vid) VALUES $q", @{$o->{vn}}) if @{$o->{vn}};
+ $self->dbExec("INSERT INTO edit_releases_vn (vid) VALUES $q", @{$o->{vn}}) if @{$o->{vn}};
}
if($o->{media}) {
- $self->dbExec('DELETE FROM edit_release_media');
+ $self->dbExec('DELETE FROM edit_releases_media');
my $q = join ',', map '(?,?)', @{$o->{media}};
my @q = map +($_->[0], $_->[1]), @{$o->{media}};
- $self->dbExec("INSERT INTO edit_release_media (medium, qty) VALUES $q", @q) if @q;
+ $self->dbExec("INSERT INTO edit_releases_media (medium, qty) VALUES $q", @q) if @q;
}
}
diff --git a/lib/VNDB/DB/Staff.pm b/lib/VNDB/DB/Staff.pm
index 8c6badd6..fb472c72 100644
--- a/lib/VNDB/DB/Staff.pm
+++ b/lib/VNDB/DB/Staff.pm
@@ -152,15 +152,15 @@ sub _enrich {
sub dbStaffRevisionInsert {
my($self, $o) = @_;
- $self->dbExec('DELETE FROM edit_staff_aliases');
+ $self->dbExec('DELETE FROM edit_staff_alias');
if($o->{aid}) {
$self->dbExec(q|
- INSERT INTO edit_staff_aliases (id, name, original) VALUES (?, ?, ?)|,
+ INSERT INTO edit_staff_alias (aid, name, original) VALUES (?, ?, ?)|,
$o->{aid}, $o->{name}, $o->{original});
} else {
$o->{aid} = $self->dbRow(q|
- INSERT INTO edit_staff_aliases (name, original) VALUES (?, ?) RETURNING id|,
- $o->{name}, $o->{original})->{id};
+ INSERT INTO edit_staff_alias (name, original) VALUES (?, ?) RETURNING aid|,
+ $o->{name}, $o->{original})->{aid};
}
my %staff = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (),
@@ -168,9 +168,9 @@ sub dbStaffRevisionInsert {
$self->dbExec('UPDATE edit_staff !H', \%staff) if %staff;
for my $a (@{$o->{aliases}}) {
if($a->{aid}) {
- $self->dbExec('INSERT INTO edit_staff_aliases (id, name, original) VALUES (!l)', [ @{$a}{qw|aid name orig|} ]);
+ $self->dbExec('INSERT INTO edit_staff_alias (aid, name, original) VALUES (!l)', [ @{$a}{qw|aid name orig|} ]);
} else {
- $self->dbExec('INSERT INTO edit_staff_aliases (name, original) VALUES (?, ?)', $a->{name}, $a->{orig});
+ $self->dbExec('INSERT INTO edit_staff_alias (name, original) VALUES (?, ?)', $a->{name}, $a->{orig});
}
}
}
@@ -183,7 +183,7 @@ sub dbStaffAliasIds {
SELECT DISTINCT sa.aid
FROM changes c
JOIN staff_alias_hist sa ON sa.chid = c.id
- WHERE c.type = \'s\' AND c.itemid = ?|, $sid);
+ WHERE c.type = 's' AND c.itemid = ?|, $sid);
}
1;
diff --git a/lib/VNDB/Handler/Chars.pm b/lib/VNDB/Handler/Chars.pm
index 524b5a04..98847b4c 100644
--- a/lib/VNDB/Handler/Chars.pm
+++ b/lib/VNDB/Handler/Chars.pm
@@ -344,8 +344,8 @@ sub edit {
$_->[1]||=undef for (@vns);
$frm->{vns} = \@vns;
- my $nrev = $self->dbItemEdit(c => !$copy && $id ? $r->{cid} : undef, %$frm);
- return $self->resRedirect("/c$nrev->{iid}.$nrev->{rev}", 'post');
+ my $nrev = $self->dbItemEdit(c => !$copy && $id ? ($r->{id}, $r->{rev}) : (undef, undef), %$frm);
+ return $self->resRedirect("/c$nrev->{itemid}.$nrev->{rev}", 'post');
}
}
diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm
index 628034c2..69000b27 100644
--- a/lib/VNDB/Handler/Producers.pm
+++ b/lib/VNDB/Handler/Producers.pm
@@ -247,16 +247,16 @@ sub edit {
$frm->{relations} = $relations;
$frm->{l_wp} = undef if !$frm->{l_wp};
- my $nrev = $self->dbItemEdit(p => $pid ? $p->{cid} : undef, %$frm);
+ my $nrev = $self->dbItemEdit(p => $pid||undef, $pid ? $p->{rev} : 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, $nrev->{iid}, $nrev->{rev});
+ _updreverse($self, \%old, \%new, $nrev->{itemid}, $nrev->{rev});
}
- return $self->resRedirect("/p$nrev->{iid}.$nrev->{rev}", 'post');
+ return $self->resRedirect("/p$nrev->{itemid}.$nrev->{rev}", 'post');
}
}
@@ -333,7 +333,7 @@ sub _updreverse {
my $r = $self->dbProducerGetRev(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 => $r->{cid},
+ $self->dbItemEdit(p => $i, $r->{rev},
relations => \@newrel,
editsum => "Reverse relation update caused by revision p$pid.$rev",
uid => 1,
diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm
index 1c97eced..78476db2 100644
--- a/lib/VNDB/Handler/Releases.pm
+++ b/lib/VNDB/Handler/Releases.pm
@@ -350,7 +350,7 @@ sub edit {
}
if(!$frm->{_err}) {
- my $nrev = $self->dbItemEdit(r => !$copy && $rid ? $r->{cid} : undef,
+ my $nrev = $self->dbItemEdit(r => !$copy && $rid ? ($r->{id}, $r->{rev}) : (undef, undef),
(map { $_ => $frm->{$_} } qw| type title original gtin catalog languages website released minage
notes platforms resolution editsum patch voiced freeware doujin ani_story ani_ero ihid ilock|),
vn => $new_vn,
@@ -358,7 +358,7 @@ sub edit {
media => $media,
);
- return $self->resRedirect("/r$nrev->{iid}.$nrev->{rev}", 'post');
+ return $self->resRedirect("/r$nrev->{itemid}.$nrev->{rev}", 'post');
}
}
diff --git a/lib/VNDB/Handler/Staff.pm b/lib/VNDB/Handler/Staff.pm
index f2e855ba..4bf6e63a 100644
--- a/lib/VNDB/Handler/Staff.pm
+++ b/lib/VNDB/Handler/Staff.pm
@@ -191,7 +191,7 @@ sub edit {
(map { $_ => $s->{$_} } qw|name original gender lang desc l_wp l_site l_twitter l_anidb ihid ilock|),
primary => $s->{aid},
aliases => [
- map +{ aid => $_->{id}, name => $_->{name}, orig => $_->{original} },
+ map +{ aid => $_->{aid}, name => $_->{name}, orig => $_->{original} },
sort { $a->{name} cmp $b->{name} || $a->{original} cmp $b->{original} } @{$s->{aliases}}
],
);
@@ -221,7 +221,7 @@ sub edit {
);
if(!$frm->{_err}) {
- my %old_aliases = $sid ? ( map +($_->{id} => 1), @{$self->dbStaffAliasIds($sid)} ) : ();
+ my %old_aliases = $sid ? ( map +($_->{aid} => 1), @{$self->dbStaffAliasIds($sid)} ) : ();
$frm->{primary} = 0 unless exists $old_aliases{$frm->{primary}};
# reset aid to zero for newly added aliases.
@@ -233,8 +233,8 @@ sub edit {
$frm->{desc} = $self->bbSubstLinks($frm->{desc});
return $self->resRedirect("/s$sid", 'post') if $sid && !form_compare(\%b4, $frm);
- my $nrev = $self->dbItemEdit ('s' => $sid ? $s->{cid} : undef, %$frm);
- return $self->resRedirect("/s$nrev->{iid}.$nrev->{rev}", 'post');
+ my $nrev = $self->dbItemEdit(s => $sid ? ($s->{id}, $s->{rev}) : (undef, undef), %$frm);
+ return $self->resRedirect("/s$nrev->{itemid}.$nrev->{rev}", 'post');
}
}
diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm
index c383be78..07d1a916 100644
--- a/lib/VNDB/Handler/VNEdit.pm
+++ b/lib/VNDB/Handler/VNEdit.pm
@@ -77,7 +77,7 @@ sub addform {
sub edit {
my($self, $vid, $rev, $nosubmit) = @_;
- my $v = $vid && $self->dbVNGet(id => $vid, what => 'extended screenshots relations anime credits changes', $rev ? (rev => $rev) : ())->[0];
+ my $v = $vid && $self->dbVNGetRev(id => $vid, what => 'extended screenshots relations anime credits changes', $rev ? (rev => $rev) : ())->[0];
return $self->resNotFound if $vid && !$v->{id};
$rev = undef if !$vid || $v->{lastrev};
@@ -176,7 +176,7 @@ sub edit {
return $self->resRedirect("/v$vid", 'post') if $vid && !form_compare(\%b4, $frm);
# perform the edit/add
- my $nrev = $self->dbItemEdit(v => $vid ? $v->{cid} : undef,
+ my $nrev = $self->dbItemEdit(v => $vid ? ($v->{id}, $v->{rev}) : (undef, undef),
(map { $_ => $frm->{$_} } qw|title original image alias desc length l_wp l_encubed l_renai editsum img_nsfw ihid ilock credits seiyuu screenshots|),
anime => [ keys %$anime ],
relations => $relations,
@@ -186,10 +186,10 @@ sub edit {
if(!$vid && $#$relations >= 0 || $vid && $frm->{vnrelations} ne $b4{vnrelations}) {
my %old = $vid ? (map +($_->{id} => [ $_->{relation}, $_->{official} ]), @{$v->{relations}}) : ();
my %new = map +($_->[1] => [ $_->[0], $_->[2] ]), @$relations;
- _updreverse($self, \%old, \%new, $nrev->{iid}, $nrev->{rev});
+ _updreverse($self, \%old, \%new, $nrev->{itemid}, $nrev->{rev});
}
- return $self->resRedirect("/v$nrev->{iid}.$nrev->{rev}", 'post');
+ return $self->resRedirect("/v$nrev->{itemid}.$nrev->{rev}", 'post');
}
}
@@ -422,7 +422,7 @@ sub _form {
# Update reverse relations and regenerate relation graph
# Arguments: %old. %new, vid, rev
-# %old,%new -> { vid2 => [ relation, official ], .. }
+# %old,%new -> { vid => [ relation, official ], .. }
# from the perspective of vid
# rev is of the related edit
sub _updreverse {
@@ -441,10 +441,10 @@ sub _updreverse {
# edit all related VNs
for my $i (keys %upd) {
- my $r = $self->dbVNGet(id => $i, what => 'relations')->[0];
+ my $r = $self->dbVNGetRev(id => $i, what => 'relations')->[0];
my @newrel = map $_->{id} != $vid ? [ $_->{relation}, $_->{id}, $_->{official} ] : (), @{$r->{relations}};
push @newrel, [ $upd{$i}[0], $vid, $upd{$i}[1] ] if $upd{$i};
- $self->dbItemEdit(v => $r->{cid},
+ $self->dbItemEdit(v => $r->{id}, $r->{rev},
relations => \@newrel,
editsum => "Reverse relation update caused by revision v$vid.$rev",
uid => 1, # Multi
diff --git a/util/sql/all.sql b/util/sql/all.sql
index 496479a2..b2b4fabc 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -9,7 +9,7 @@ CREATE TYPE board_type AS ENUM ('an', 'db', 'ge', 'v', 'p', 'u');
CREATE TYPE char_role AS ENUM ('main', 'primary', 'side', 'appears');
CREATE TYPE credit_type AS ENUM ('scenario', 'chardesign', 'art', 'music', 'songs', 'director', 'staff');
CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p', 'c', 's');
-CREATE TYPE edit_rettype AS (iid integer, cid integer, rev integer);
+CREATE TYPE edit_rettype AS (itemid integer, chid integer, rev integer);
CREATE TYPE gender AS ENUM ('unknown', 'm', 'f', 'b');
CREATE TYPE language AS ENUM ('ar', 'ca', 'cs', 'da', 'de', 'en', 'es', 'fi', 'fr', 'he', 'hu', 'id', 'it', 'ja', 'ko', 'nl', 'no', 'pl', 'pt-pt', 'pt-br', 'ro', 'ru', 'sk', 'sv', 'tr', 'uk', 'vi', 'zh');
CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
@@ -33,6 +33,10 @@ CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'sid
\i util/sql/func.sql
+-- auto-generated editing functions
+
+\i util/sql/editfunc.sql
+
-- constraints & indices
\i util/sql/tableattrs.sql
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 51711f77..e5e12eab 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -23,60 +23,56 @@ CREATE OR REPLACE FUNCTION strip_spoilers(t text) RETURNS text AS $$
$$ LANGUAGE sql IMMUTABLE;
--- update_vncache(id) - updates the c_* columns in the vn table
+-- update_vncache(id) - updates some c_* columns in the vn table
CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
UPDATE vn SET
- c_released = COALESCE((SELECT
- MIN(rr1.released)
- FROM releases_rev rr1
- JOIN releases r1 ON rr1.id = r1.latest
- JOIN releases_vn rv1 ON rr1.id = rv1.rid
- WHERE rv1.vid = vn.id
- AND rr1.type <> 'trial'
- AND r1.hidden = FALSE
- AND rr1.released <> 0
- GROUP BY rv1.vid
+ c_released = COALESCE((
+ SELECT MIN(r.released)
+ FROM releases r
+ JOIN releases_vn rv ON r.id = rv.id
+ WHERE rv.vid = $1
+ AND r.type <> 'trial'
+ AND r.hidden = FALSE
+ AND r.released <> 0
+ GROUP BY rv.vid
), 0),
c_olang = ARRAY(
SELECT lang
FROM releases_lang
- WHERE rid = (
- SELECT r.latest
+ WHERE id = (
+ SELECT r.id
FROM releases_vn rv
- JOIN releases r ON rv.rid = r.latest
- JOIN releases_rev rr ON rr.id = r.latest AND rr.rid = r.id
- WHERE rr.released > 0
+ JOIN releases r ON rv.id = r.id
+ WHERE r.released > 0
AND NOT r.hidden
AND rv.vid = $1
- ORDER BY rr.released
+ ORDER BY r.released
LIMIT 1
)
),
c_languages = ARRAY(
- SELECT rl2.lang
- FROM releases_rev rr2
- JOIN releases_lang rl2 ON rl2.rid = rr2.id
- JOIN releases r2 ON rr2.id = r2.latest
- JOIN releases_vn rv2 ON rr2.id = rv2.rid
- WHERE rv2.vid = vn.id
- AND rr2.type <> 'trial'
- AND rr2.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer
- AND r2.hidden = FALSE
- GROUP BY rl2.lang
- ORDER BY rl2.lang
+ SELECT rl.lang
+ FROM releases_lang rl
+ JOIN releases r ON r.id = rl.id
+ JOIN releases_vn rv ON r.id = rv.id
+ WHERE rv.vid = $1
+ AND r.type <> 'trial'
+ AND r.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer
+ AND r.hidden = FALSE
+ GROUP BY rl.lang
+ ORDER BY rl.lang
),
c_platforms = ARRAY(
- SELECT rp3.platform
- FROM releases_platforms rp3
- JOIN releases_rev rr3 ON rp3.rid = rr3.id
- JOIN releases r3 ON rp3.rid = r3.latest
- JOIN releases_vn rv3 ON rp3.rid = rv3.rid
- WHERE rv3.vid = vn.id
- AND rr3.type <> 'trial'
- AND rr3.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer
- AND r3.hidden = FALSE
- GROUP BY rp3.platform
- ORDER BY rp3.platform
+ SELECT rp.platform
+ FROM releases_platforms rp
+ JOIN releases r ON rp.id = r.id
+ JOIN releases_vn rv ON rp.id = rv.id
+ WHERE rv.vid = $1
+ AND r.type <> 'trial'
+ AND r.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer
+ AND r.hidden = FALSE
+ GROUP BY rp.platform
+ ORDER BY rp.platform
)
WHERE id = $1;
$$ LANGUAGE sql;
@@ -154,9 +150,9 @@ BEGIN
-- all char<->trait links of the latest revisions, including chars inherited from child traits
-- (also includes meta traits, because they could have a normal trait as parent)
WITH RECURSIVE traits_chars_all(lvl, tid, cid, spoiler, meta) AS (
- SELECT 15, tid, c.id, spoil, false
+ SELECT 15, tid, ct.id, spoil, false
FROM chars_traits ct
- JOIN chars c ON c.latest = ct.cid
+ JOIN chars c ON c.id = ct.id
WHERE NOT c.hidden
UNION ALL
SELECT lvl-1, tp.parent, tc.cid, tc.spoiler, t.meta
@@ -186,18 +182,18 @@ $$ LANGUAGE plpgsql;
-- revision insertion abstraction --
----------------------------------------------------------
+-- The two functions below are utility functions used by the item-specific functions in editfunc.sql
--- IMPORTANT: these functions will need to be updated on each change in the DB structure
--- of the relevant tables
-
-
--- create temporary table for generic revision info
-CREATE OR REPLACE FUNCTION edit_revtable(t dbentry_type, i integer) RETURNS void AS $$
+-- create temporary table for generic revision info, and returns the chid of the revision being edited (or NULL).
+CREATE OR REPLACE FUNCTION edit_revtable(xtype dbentry_type, xitemid integer, xrev integer) RETURNS integer AS $$
+DECLARE
+ ret integer;
+ x record;
BEGIN
BEGIN
CREATE TEMPORARY TABLE edit_revision (
type dbentry_type NOT NULL,
- iid integer,
+ itemid integer,
requester integer,
ip inet,
comments text,
@@ -207,15 +203,9 @@ BEGIN
EXCEPTION WHEN duplicate_table THEN
TRUNCATE edit_revision;
END;
- INSERT INTO edit_revision (type, iid, ihid, ilock) VALUES (t,
- ( SELECT vid FROM vn_rev WHERE id = i
- UNION SELECT rid FROM releases_rev WHERE id = i
- UNION SELECT cid FROM chars_rev WHERE id = i
- UNION SELECT pid FROM producers_rev WHERE id = i
- UNION SELECT sid FROM staff_rev WHERE id = i),
- COALESCE((SELECT ihid FROM changes WHERE id = i), FALSE),
- COALESCE((SELECT ilock FROM changes WHERE id = i), FALSE)
- );
+ SELECT INTO x id, ihid, ilock FROM changes c WHERE type = xtype AND itemid = xitemid AND rev = xrev;
+ INSERT INTO edit_revision (type, itemid, ihid, ilock) VALUES (xtype, xitemid, COALESCE(x.ihid, FALSE), COALESCE(x.ilock, FALSE));
+ RETURN x.id;
END;
$$ LANGUAGE plpgsql;
@@ -223,305 +213,145 @@ $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION edit_commit() RETURNS edit_rettype AS $$
DECLARE
- r edit_rettype;
- t dbentry_type;
- i integer;
+ ret edit_rettype;
+ xtype dbentry_type;
BEGIN
- SELECT type INTO t FROM edit_revision;
- SELECT iid INTO i FROM edit_revision;
+ SELECT type INTO xtype FROM edit_revision;
+ SELECT itemid INTO ret.itemid 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
- JOIN ( SELECT id FROM vn_rev WHERE t = 'v' AND vid = i
- UNION SELECT id FROM releases_rev WHERE t = 'r' AND rid = i
- UNION SELECT id FROM producers_rev WHERE t = 'p' AND pid = i
- UNION SELECT id FROM chars_rev WHERE t = 'c' AND cid = i
- UNION SELECT id FROM staff_rev WHERE t = 's' AND sid = i
- ) x(id) ON x.id = c.id
- ORDER BY c.id DESC
- LIMIT 1;
- END IF;
- -- insert change
- INSERT INTO changes (type, requester, ip, comments, ihid, ilock, rev)
- SELECT t, requester, ip, comments, ihid, ilock, r.rev
- FROM edit_revision
- RETURNING id INTO r.cid;
+ SELECT MAX(rev)+1 INTO ret.rev FROM changes WHERE type = xtype AND itemid = ret.itemid;
+ SELECT COALESCE(ret.rev, 1) INTO ret.rev;
-- 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;
- WHEN 'c' THEN INSERT INTO chars (latest) VALUES (0) RETURNING id INTO r.iid;
- WHEN 's' THEN INSERT INTO staff (latest) VALUES (0) RETURNING id INTO r.iid;
+ IF ret.itemid IS NULL THEN
+ CASE xtype
+ WHEN 'v' THEN INSERT INTO vn DEFAULT VALUES RETURNING id INTO ret.itemid;
+ WHEN 'r' THEN INSERT INTO releases DEFAULT VALUES RETURNING id INTO ret.itemid;
+ WHEN 'p' THEN INSERT INTO producers DEFAULT VALUES RETURNING id INTO ret.itemid;
+ WHEN 'c' THEN INSERT INTO chars DEFAULT VALUES RETURNING id INTO ret.itemid;
+ WHEN 's' THEN INSERT INTO staff DEFAULT VALUES RETURNING id INTO ret.itemid;
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 $$
-#variable_conflict use_variable
-BEGIN
- -- create tables, based on existing tables (so that the column types are always synchronised)
- BEGIN
- 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;
- CREATE TEMPORARY TABLE edit_vn_staff (LIKE vn_staff INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
- ALTER TABLE edit_vn_staff DROP COLUMN vid;
- CREATE TEMPORARY TABLE edit_vn_seiyuu (LIKE vn_seiyuu INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
- ALTER TABLE edit_vn_seiyuu DROP COLUMN vid;
- EXCEPTION WHEN duplicate_table THEN
- TRUNCATE edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots, edit_vn_staff, edit_vn_seiyuu;
- END;
- PERFORM edit_revtable('v', cid);
- -- new VN, load defaults
- IF cid IS NULL THEN
- INSERT INTO edit_vn DEFAULT VALUES;
- -- otherwise, load revision
- ELSE
- INSERT INTO edit_vn SELECT title, alias, img_nsfw, length, "desc", l_wp, 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, official FROM vn_relations WHERE vid1 = cid;
- INSERT INTO edit_vn_screenshots SELECT scr, nsfw, rid FROM vn_screenshots WHERE vid = cid;
- INSERT INTO edit_vn_staff SELECT aid, role, note FROM vn_staff WHERE vid = cid;
- INSERT INTO edit_vn_seiyuu SELECT aid, vs.cid, note FROM vn_seiyuu vs WHERE vid = cid;
- END IF;
-END;
-$$ LANGUAGE plpgsql;
-
-
-
-CREATE OR REPLACE FUNCTION edit_vn_commit() RETURNS edit_rettype AS $$
-DECLARE
- r edit_rettype;
-BEGIN
- IF (SELECT COUNT(*) FROM edit_vn) <> 1 THEN
- RAISE 'edit_vn must have exactly one row!';
- END IF;
- SELECT INTO r * FROM edit_commit();
- INSERT INTO vn_rev SELECT r.cid, r.iid, title, alias, img_nsfw, length, "desc", l_wp, 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, official FROM edit_vn_relations;
- INSERT INTO vn_screenshots SELECT r.cid, scr, nsfw, rid FROM edit_vn_screenshots;
- INSERT INTO vn_staff SELECT r.cid, aid, role, note FROM edit_vn_staff;
- INSERT INTO vn_seiyuu SELECT r.cid, aid, cid, note FROM edit_vn_seiyuu;
- UPDATE vn SET latest = r.cid WHERE id = r.iid;
- RETURN r;
-END;
-$$ LANGUAGE plpgsql;
-
-
-
-CREATE OR REPLACE FUNCTION edit_release_init(cid integer) RETURNS void AS $$
-BEGIN
- -- temp. tables
- BEGIN
- CREATE TEMPORARY TABLE edit_release (LIKE releases_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
- ALTER TABLE edit_release DROP COLUMN id;
- ALTER TABLE edit_release DROP COLUMN rid;
- CREATE TEMPORARY TABLE edit_release_lang (LIKE releases_lang INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
- ALTER TABLE edit_release_lang DROP COLUMN rid;
- CREATE TEMPORARY TABLE edit_release_media (LIKE releases_media INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
- ALTER TABLE edit_release_media DROP COLUMN rid;
- CREATE TEMPORARY TABLE edit_release_platforms (LIKE releases_platforms INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
- ALTER TABLE edit_release_platforms DROP COLUMN rid;
- CREATE TEMPORARY TABLE edit_release_producers (LIKE releases_producers INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
- ALTER TABLE edit_release_producers DROP COLUMN rid;
- CREATE TEMPORARY TABLE edit_release_vn (LIKE releases_vn INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
- ALTER TABLE edit_release_vn DROP COLUMN rid;
- EXCEPTION WHEN duplicate_table THEN
- TRUNCATE edit_release, edit_release_lang, edit_release_media, edit_release_platforms, edit_release_producers, edit_release_vn;
- END;
- PERFORM edit_revtable('r', cid);
- -- new release
- IF cid IS NULL THEN
- INSERT INTO edit_release DEFAULT VALUES;
- -- load revision
- ELSE
- INSERT INTO edit_release SELECT title, original, type, website, released, notes, minage, gtin, patch, catalog, resolution, voiced, freeware, doujin, ani_story, ani_ero FROM releases_rev WHERE id = cid;
- INSERT INTO edit_release_lang SELECT lang FROM releases_lang WHERE rid = cid;
- INSERT INTO edit_release_media SELECT medium, qty FROM releases_media WHERE rid = cid;
- INSERT INTO edit_release_platforms SELECT platform FROM releases_platforms WHERE rid = cid;
- INSERT INTO edit_release_producers SELECT pid, developer, publisher FROM releases_producers WHERE rid = cid;
- INSERT INTO edit_release_vn SELECT vid FROM releases_vn WHERE rid = cid;
END IF;
+ -- insert change
+ INSERT INTO changes (type, itemid, rev, requester, ip, comments, ihid, ilock)
+ SELECT type, ret.itemid, ret.rev, requester, ip, comments, ihid, ilock FROM edit_revision RETURNING id INTO ret.chid;
+ RETURN ret;
END;
$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION edit_release_commit() RETURNS edit_rettype AS $$
+-- Check for stuff to be done when an item has been changed
+CREATE OR REPLACE FUNCTION edit_committed(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
DECLARE
- r edit_rettype;
+ xoldchid integer;
BEGIN
- IF (SELECT COUNT(*) FROM edit_release) <> 1 THEN
- RAISE 'edit_release must have exactly one row!';
- ELSIF NOT EXISTS(SELECT 1 FROM edit_release_vn) THEN
- RAISE 'edit_release_vn must have at least one row!';
+ SELECT id INTO xoldchid FROM changes WHERE type = xtype AND itemid = xedit.itemid AND rev = xedit.rev-1;
+
+ -- Set producers.rgraph to NULL and notify when:
+ -- 1. There's a new producer entry with some relations
+ -- 2. The producer name/type/language has changed
+ -- 3. The producer relations have been changed
+ IF xtype = 'p' THEN
+ IF -- 1.
+ (xoldchid IS NULL AND EXISTS(SELECT 1 FROM producers_relations_hist WHERE chid = xedit.chid))
+ OR (xoldchid IS NOT NULL AND (
+ -- 2.
+ EXISTS(SELECT 1 FROM producers_hist p1, producers_hist p2 WHERE (p2.name <> p1.name OR p2.type <> p1.type OR p2.lang <> p1.lang) AND p1.chid = xoldchid AND p2.chid = xedit.chid)
+ -- 3.
+ OR EXISTS(SELECT pid, relation FROM producers_relations_hist WHERE chid = xoldchid EXCEPT SELECT pid, relation FROM producers_relations_hist WHERE chid = xedit.chid)
+ OR EXISTS(SELECT pid, relation FROM producers_relations_hist WHERE chid = xedit.chid EXCEPT SELECT pid, relation FROM producers_relations_hist WHERE chid = xoldchid)
+ ))
+ THEN
+ UPDATE producers SET rgraph = NULL WHERE id = xedit.itemid;
+ NOTIFY relgraph; -- This notify is not done by the producer_relgraph_notify trigger for new entries or if rgraph was already NULL
+ END IF;
END IF;
- SELECT INTO r * FROM edit_commit();
- INSERT INTO releases_rev SELECT r.cid, r.iid, title, original, type, website, released, notes, minage, gtin, patch, catalog, resolution, voiced, freeware, doujin, ani_story, ani_ero FROM edit_release;
- INSERT INTO releases_lang SELECT r.cid, lang FROM edit_release_lang;
- INSERT INTO releases_media SELECT r.cid, medium, qty FROM edit_release_media;
- INSERT INTO releases_platforms SELECT r.cid, platform FROM edit_release_platforms;
- INSERT INTO releases_producers SELECT pid, r.cid, developer, publisher FROM edit_release_producers;
- INSERT INTO releases_vn SELECT r.cid, vid FROM edit_release_vn;
- UPDATE releases SET latest = r.cid WHERE id = r.iid;
- RETURN r;
-END;
-$$ LANGUAGE plpgsql;
-
-
-CREATE OR REPLACE FUNCTION edit_producer_init(cid integer) RETURNS void AS $$
-BEGIN
- 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;
- EXCEPTION WHEN duplicate_table THEN
- TRUNCATE edit_producer, edit_producer_relations;
- END;
- PERFORM edit_revtable('p', cid);
- -- new producer
- IF cid IS NULL THEN
- INSERT INTO edit_producer DEFAULT VALUES;
- -- load revision
- ELSE
- 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;
+ -- Set vn.rgraph to NULL and notify when:
+ -- 1. There's a new vn entry with some relations
+ -- 2. The vn title has changed
+ -- 3. The vn relations have been changed
+ IF xtype = 'v' THEN
+ IF -- 1.
+ (xoldchid IS NULL AND EXISTS(SELECT 1 FROM vn_relations_hist WHERE chid = xedit.chid))
+ OR (xoldchid IS NOT NULL AND (
+ -- 2.
+ EXISTS(SELECT 1 FROM vn_hist v1, vn_hist v2 WHERE v2.title <> v1.title AND v1.chid = xoldchid AND v2.chid = xedit.chid)
+ -- 3.
+ OR EXISTS(SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xoldchid EXCEPT SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xedit.chid)
+ OR EXISTS(SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xedit.chid EXCEPT SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xoldchid)
+ ))
+ THEN
+ UPDATE vn SET rgraph = NULL WHERE id = xedit.itemid;
+ NOTIFY relgraph;
+ END IF;
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!';
+ -- Set c_search to NULL and notify when
+ -- 1. A new VN entry is created
+ -- 2. The vn title/original/alias has changed
+ IF xtype = 'v' THEN
+ IF -- 1.
+ xoldchid IS NULL OR
+ -- 2.
+ EXISTS(SELECT 1 FROM vn_hist v1, vn_hist v2 WHERE (v2.title <> v1.title OR v2.original <> v1.original OR v2.alias <> v1.alias) AND v1.chid = xoldchid AND v2.chid = xedit.chid)
+ THEN
+ UPDATE vn SET c_search = NULL WHERE id = xedit.itemid;
+ NOTIFY vnsearch;
+ END IF;
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;
- RETURN r;
-END;
-$$ LANGUAGE plpgsql;
-
-
-CREATE OR REPLACE FUNCTION edit_char_init(hid integer) RETURNS void AS $$
-BEGIN
- BEGIN
- CREATE TEMPORARY TABLE edit_char (LIKE chars_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
- ALTER TABLE edit_char DROP COLUMN id;
- ALTER TABLE edit_char DROP COLUMN cid;
- CREATE TEMPORARY TABLE edit_char_traits (LIKE chars_traits INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
- ALTER TABLE edit_char_traits DROP COLUMN cid;
- CREATE TEMPORARY TABLE edit_char_vns (LIKE chars_vns INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
- ALTER TABLE edit_char_vns DROP COLUMN cid;
- EXCEPTION WHEN duplicate_table THEN
- TRUNCATE edit_char, edit_char_traits, edit_char_vns;
- END;
- PERFORM edit_revtable('c', hid);
- -- new char
- IF hid IS NULL THEN
- INSERT INTO edit_char DEFAULT VALUES;
- -- load revision
- ELSE
- INSERT INTO edit_char SELECT name, original, alias, image, "desc", gender, s_bust, s_waist, s_hip, b_month, b_day, height, weight, bloodt, main, main_spoil FROM chars_rev WHERE id = hid;
- INSERT INTO edit_char_traits SELECT tid, spoil FROM chars_traits WHERE cid = hid;
- INSERT INTO edit_char_vns SELECT vid, rid, spoil, role FROM chars_vns WHERE cid = hid;
+ -- Set related vn.c_search columns to NULL and notify when
+ -- 1. A new release is created
+ -- 2. A release has been hidden or unhidden
+ -- 3. The release title/original has changed
+ -- 4. The releases_vn table differs from a previous revision
+ IF xtype = 'r' THEN
+ IF -- 1.
+ xoldchid IS NULL OR
+ -- 2.
+ EXISTS(SELECT 1 FROM changes c1, changes c2 WHERE c1.ihid IS DISTINCT FROM c2.ihid AND c1.id = xedit.chid AND c2.id = xoldchid) OR
+ -- 3.
+ EXISTS(SELECT 1 FROM releases_hist r1, releases_hist r2 WHERE (r2.title <> r1.title OR r2.original <> r1.original) AND r1.chid = xoldchid AND r2.chid = xedit.chid) OR
+ -- 4.
+ EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = xoldchid EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = xedit.chid) OR
+ EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = xedit.chid EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = xoldchid)
+ THEN
+ UPDATE vn SET c_search = NULL WHERE id IN(SELECT vid FROM releases_vn_hist WHERE chid IN(xedit.chid, xoldchid));
+ NOTIFY vnsearch;
+ END IF;
END IF;
-END;
-$$ LANGUAGE plpgsql;
-
-
-CREATE OR REPLACE FUNCTION edit_char_commit() RETURNS edit_rettype AS $$
-DECLARE
- r edit_rettype;
-BEGIN
- IF (SELECT COUNT(*) FROM edit_char) <> 1 THEN
- RAISE 'edit_char must have exactly one row!';
+ -- Call update_vncache() for related VNs when a release has been created or edited
+ -- (This could be made more specific, but update_vncache() is fast enough that it's not worth the complexity)
+ IF xtype = 'r' THEN
+ PERFORM update_vncache(vid) FROM (
+ SELECT DISTINCT vid FROM releases_vn_hist WHERE chid IN(xedit.chid, xoldchid)
+ ) AS v(vid);
END IF;
- SELECT INTO r * FROM edit_commit();
- INSERT INTO chars_rev SELECT r.cid, r.iid, name, original, alias, image, "desc", gender, s_bust, s_waist, s_hip, b_month, b_day, height, weight, bloodt, main, main_spoil FROM edit_char;
- INSERT INTO chars_traits SELECT r.cid, tid, spoil FROM edit_char_traits;
- INSERT INTO chars_vns SELECT r.cid, vid, rid, spoil, role FROM edit_char_vns;
- UPDATE chars SET latest = r.cid WHERE id = r.iid;
- RETURN r;
-END;
-$$ LANGUAGE plpgsql;
-
-
-CREATE OR REPLACE FUNCTION edit_staff_init(cid integer) RETURNS void AS $$
-BEGIN
- BEGIN
- CREATE TEMPORARY TABLE edit_staff (LIKE staff_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
- ALTER TABLE edit_staff DROP COLUMN id;
- ALTER TABLE edit_staff DROP COLUMN sid;
- CREATE TEMPORARY TABLE edit_staff_aliases (LIKE staff_alias INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
- ALTER TABLE edit_staff_aliases DROP COLUMN rid;
- EXCEPTION WHEN duplicate_table THEN
- TRUNCATE edit_staff, edit_staff_aliases;
- END;
- PERFORM edit_revtable('s', cid);
- -- new staff member
- IF cid IS NULL THEN
- INSERT INTO edit_staff (aid) VALUES (0);
- -- load revision
- ELSE
- INSERT INTO edit_staff SELECT aid, gender, lang, "desc", l_wp, l_site, l_twitter, l_anidb FROM staff_rev WHERE id = cid;
- INSERT INTO edit_staff_aliases SELECT id, name, original FROM staff_alias WHERE rid = cid;
+ -- Call notify_dbdel() if an entry has been deleted
+ -- Call notify_listdel() if a vn/release entry has been deleted
+ IF xoldchid IS NOT NULL
+ AND EXISTS(SELECT 1 FROM changes WHERE id = xoldchid AND NOT ihid)
+ AND EXISTS(SELECT 1 FROM changes WHERE id = xedit.chid AND ihid)
+ THEN
+ PERFORM notify_dbdel(xtype, xedit);
+ IF xtype = 'v' OR xtype = 'r' THEN
+ PERFORM notify_listdel(xtype, xedit);
+ END IF;
END IF;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION edit_staff_commit() RETURNS edit_rettype AS $$
-DECLARE
- r edit_rettype;
-BEGIN
- IF (SELECT COUNT(*) FROM edit_staff) <> 1 THEN
- RAISE 'edit_staff must have exactly one row!';
+ -- Call notify_dbedit() if a non-hidden entry has been edited
+ IF xoldchid IS NOT NULL AND EXISTS(SELECT 1 FROM changes WHERE id = xedit.chid AND NOT ihid)
+ THEN
+ PERFORM notify_dbedit(xtype, xedit);
END IF;
- SELECT INTO r * FROM edit_commit();
- INSERT INTO staff_alias (id, rid, name, original)
- SELECT id, r.cid, name, original FROM edit_staff_aliases;
- INSERT INTO staff_rev (id, sid, aid, gender, lang, "desc", l_wp, l_site, l_twitter, l_anidb)
- SELECT r.cid, r.iid, aid, gender, lang, "desc", l_wp, l_site, l_twitter, l_anidb FROM edit_staff;
- UPDATE staff SET latest = r.cid WHERE id = r.iid;
- RETURN r;
END;
$$ LANGUAGE plpgsql;
-
-
----------------------------------------------------------
-- trigger functions --
----------------------------------------------------------
@@ -627,18 +457,17 @@ CREATE OR REPLACE FUNCTION update_vnlist_rlist() RETURNS trigger AS $$
BEGIN
-- 1.
IF TG_TABLE_NAME = 'vnlists' THEN
- DELETE FROM rlists WHERE uid = OLD.uid AND rid IN(SELECT r.id
+ DELETE FROM rlists WHERE uid = OLD.uid AND rid IN(SELECT rv.id
-- fetch all related rows in rlists
FROM releases_vn rv
- JOIN releases r ON r.latest = rv.rid
- JOIN rlists rl ON rl.rid = r.id
+ JOIN rlists rl ON rl.rid = rv.id
WHERE rv.vid = OLD.vid AND rl.uid = OLD.uid
-- and test for a corresponding row in vnlists
AND NOT EXISTS(
SELECT 1
FROM releases_vn rvi
JOIN vnlists vl ON vl.vid = rvi.vid AND uid = OLD.uid
- WHERE rvi.rid = r.latest
+ WHERE rvi.id = rv.id
));
-- 2.
@@ -646,15 +475,13 @@ BEGIN
INSERT INTO vnlists (uid, vid) SELECT NEW.uid, rv.vid
-- all VNs linked to the release
FROM releases_vn rv
- JOIN releases r ON rv.rid = r.latest
- WHERE r.id = NEW.rid
+ WHERE rv.id = NEW.rid
-- but only if there are no corresponding rows in vnlists yet
AND NOT EXISTS(
SELECT 1
FROM releases_vn rvi
- JOIN releases ri ON rvi.rid = ri.latest
JOIN vnlists vl ON vl.vid = rvi.vid
- WHERE ri.id = NEW.rid AND vl.uid = NEW.uid
+ WHERE rvi.id = NEW.rid AND vl.uid = NEW.uid
);
END IF;
RETURN NULL;
@@ -670,33 +497,16 @@ $$ LANGUAGE plpgsql;
--- Update vn.rgraph column and send notify when a relation graph needs to be regenerated
--- 1. NOTIFY is sent on VN edit or insert or change in vn.rgraph, when rgraph = NULL and entries in vn_relations
--- vn.rgraph is set to NULL when:
--- 2. UPDATE on vn where c_released or c_languages has changed
--- 3. VN edit of which the title differs from previous revision
--- 4. VN edit with items in vn_relations that differ from previous
+-- 1. Send a notify when vn.rgraph is set to NULL, and there are related entries in vn_relations
+-- 2. Set rgraph to NULL when c_languages or c_released has changed
CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$
BEGIN
- -- 1.
- IF NEW.rgraph IS DISTINCT FROM OLD.rgraph OR NEW.latest IS DISTINCT FROM OLD.latest THEN
- IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM vn_relations WHERE vid1 = NEW.latest) THEN
+ IF EXISTS(SELECT 1 FROM vn_relations WHERE id = NEW.id) THEN
+ -- 1.
+ IF NEW.rgraph IS NULL THEN
NOTIFY relgraph;
- END IF;
- END IF;
- IF NEW.rgraph IS NOT NULL THEN
- IF
- -- 2.
- OLD.c_released IS DISTINCT FROM NEW.c_released
- OR OLD.c_languages IS DISTINCT FROM NEW.c_languages
- OR OLD.latest <> 0 AND OLD.latest IS DISTINCT FROM NEW.latest AND (
- -- 3.
- EXISTS(SELECT 1 FROM vn_rev v1, vn_rev v2 WHERE v2.title <> v1.title AND v1.id = OLD.latest AND v2.id = NEW.latest)
- -- 4. (not-really-readable method of comparing two query results)
- OR EXISTS(SELECT vid2, relation, official FROM vn_relations WHERE vid1 = OLD.latest EXCEPT SELECT vid2, relation, official FROM vn_relations WHERE vid1 = NEW.latest)
- OR (SELECT COUNT(*) FROM vn_relations WHERE vid1 = OLD.latest) <> (SELECT COUNT(*) FROM vn_relations WHERE vid1 = NEW.latest)
- )
- THEN
+ -- 2.
+ ELSE
UPDATE vn SET rgraph = NULL WHERE id = NEW.id;
END IF;
END IF;
@@ -705,30 +515,11 @@ END;
$$ LANGUAGE plpgsql;
-
--- Same as above for producers, with slight differences in the steps:
--- There is no 2, and
--- 3 = Producer edit of which the name, language or type differs from the previous revision
+-- Send a notify when producers.rgraph is set to NULL, and there are related entries in producers_relations
CREATE OR REPLACE FUNCTION producer_relgraph_notify() RETURNS trigger AS $$
BEGIN
- -- 1.
- IF NEW.rgraph IS DISTINCT FROM OLD.rgraph OR NEW.latest IS DISTINCT FROM OLD.latest THEN
- IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM producers_relations WHERE pid1 = NEW.latest) THEN
- NOTIFY relgraph;
- END IF;
- END IF;
- IF NEW.rgraph IS NOT NULL THEN
- -- 2.
- IF OLD.latest <> 0 AND OLD.latest IS DISTINCT FROM NEW.latest AND (
- -- 3.
- EXISTS(SELECT 1 FROM producers_rev p1, producers_rev p2 WHERE (p2.name <> p1.name OR p2.type <> p1.type OR p2.lang <> p1.lang) AND p1.id = OLD.latest AND p2.id = NEW.latest)
- -- 4. (not-really-readable method of comparing two query results)
- OR EXISTS(SELECT p1.pid2, p1.relation FROM producers_relations p1 WHERE p1.pid1 = OLD.latest EXCEPT SELECT p2.pid2, p2.relation FROM producers_relations p2 WHERE p2.pid1 = NEW.latest)
- OR (SELECT COUNT(*) FROM producers_relations WHERE pid1 = OLD.latest) <> (SELECT COUNT(*) FROM producers_relations WHERE pid1 = NEW.latest)
- )
- THEN
- UPDATE producers SET rgraph = NULL WHERE id = NEW.id;
- END IF;
+ IF EXISTS(SELECT 1 FROM producers_relations WHERE id = NEW.id) THEN
+ NOTIFY relgraph;
END IF;
RETURN NULL;
END;
@@ -754,80 +545,16 @@ $$ LANGUAGE plpgsql;
--- call update_vncache() when a release is added, edited, hidden or unhidden
-CREATE OR REPLACE FUNCTION release_vncache_update() RETURNS trigger AS $$
-BEGIN
- PERFORM update_vncache(vid) FROM (
- SELECT DISTINCT vid FROM releases_vn WHERE rid = OLD.latest OR rid = NEW.latest
- ) AS v(vid);
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-
-
--- update (vn|release|producer|char).(hidden|locked) on a new revision
--- NOTE: this is a /before/ trigger, it modifies NEW
-CREATE OR REPLACE FUNCTION update_hidlock() RETURNS trigger AS $$
-DECLARE
- r record;
-BEGIN
- SELECT INTO r ihid, ilock FROM changes WHERE id = NEW.latest;
- NEW.hidden := r.ihid;
- NEW.locked := r.ilock;
- RETURN NEW;
-END;
-$$ LANGUAGE plpgsql;
-
-
-
--- Check for updates to vn.c_search
--- 1. NOTIFY is sent when vn.c_search goes from non-NULL to NULL
--- vn.c_search is set to NULL when:
--- 2. VN add/edit of which the title/original/alias fields differ from previous revision
--- 3. Release gets hidden or unhidden
--- 4. Release add/edit of which the title/original/vn fields differ from the previous revision
+-- Send a vnsearch notification when the c_search column is set to NULL.
CREATE OR REPLACE FUNCTION vn_vnsearch_notify() RETURNS trigger AS $$
-BEGIN
- IF TG_TABLE_NAME = 'vn' THEN
- -- 1.
- IF NEW.c_search IS NULL THEN
- NOTIFY vnsearch;
- -- 2.
- ELSIF NEW.latest IS DISTINCT FROM OLD.latest THEN
- IF EXISTS(SELECT 1 FROM vn_rev v1, vn_rev v2
- WHERE v1.id = OLD.latest AND v2.id = NEW.latest
- AND (v1.title IS DISTINCT FROM v2.title OR v1.original IS DISTINCT FROM v2.original OR v1.alias IS DISTINCT FROM v2.alias)
- ) THEN
- UPDATE vn SET c_search = NULL WHERE id = NEW.id;
- END IF;
- END IF;
- ELSIF TG_TABLE_NAME = 'releases' THEN
- -- 3. & 4.
- IF NEW.hidden IS DISTINCT FROM OLD.hidden OR (
- NEW.latest IS DISTINCT FROM OLD.latest AND (
- EXISTS(
- SELECT 1 FROM releases_rev r1, releases_rev r2
- WHERE r1.id = OLD.latest AND r2.id = NEW.latest
- AND (r1.title IS DISTINCT FROM r2.title OR r1.original IS DISTINCT FROM r2.original)
- )
- OR EXISTS(SELECT vid FROM releases_vn WHERE rid = OLD.latest EXCEPT SELECT vid FROM releases_vn WHERE rid = NEW.latest)
- OR (SELECT COUNT(*) FROM releases_vn WHERE rid = OLD.latest) <> (SELECT COUNT(*) FROM releases_vn WHERE rid = NEW.latest)
- )) THEN
- UPDATE vn SET c_search = NULL WHERE id IN(SELECT vid FROM releases_vn WHERE rid = OLD.latest OR rid = NEW.latest);
- END IF;
- END IF;
- RETURN NULL;
-END;
+ BEGIN NOTIFY vnsearch; RETURN NULL; END;
$$ LANGUAGE plpgsql;
-
----------------------------------------------------------
-- notification functions --
--- (these are, in fact, also triggers) --
----------------------------------------------------------
@@ -854,107 +581,70 @@ END;
$$ LANGUAGE plpgsql;
--- called on UPDATE vn / producers / releases / chars when (NOT OLD.hidden AND NEW.hidden)
-CREATE OR REPLACE FUNCTION notify_dbdel() RETURNS trigger AS $$
-BEGIN
+
+-- called when an entry has been deleted
+CREATE OR REPLACE FUNCTION notify_dbdel(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
- SELECT DISTINCT 'dbdel'::notification_ntype,
- (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' WHEN 'staff' THEN 's' ELSE 'c' END)::notification_ltype,
- h.requester, NEW.id, h2.rev, x.title, h2.requester
- -- look for changes of the deleted entry
- -- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs
+ SELECT DISTINCT 'dbdel'::notification_ntype, xtype::text::notification_ltype, h.requester, xedit.itemid, xedit.rev, x.title, h2.requester
FROM changes h
- JOIN ( SELECT vr.id, vr2.title FROM vn_rev vr
- JOIN vn v ON v.id = vr.vid JOIN vn_rev vr2 ON vr2.id = v.latest
- WHERE TG_TABLE_NAME = 'vn' AND vr.vid = NEW.id
- UNION SELECT rr.id, rr2.title FROM releases_rev rr
- JOIN releases r ON r.id = rr.rid JOIN releases_rev rr2 ON rr2.id = r.latest
- WHERE TG_TABLE_NAME = 'releases' AND rr.rid = NEW.id
- UNION SELECT pr.id, pr2.name FROM producers_rev pr
- JOIN producers p ON p.id = pr.pid JOIN producers_rev pr2 ON pr2.id = p.latest
- WHERE TG_TABLE_NAME = 'producers' AND pr.pid = NEW.id
- UNION SELECT cr.id, cr2.name FROM chars_rev cr
- JOIN chars c ON c.id = cr.cid JOIN chars_rev cr2 ON cr2.id = c.latest
- WHERE TG_TABLE_NAME = 'chars' AND cr.cid = NEW.id
- UNION SELECT sr.id, sa.name FROM staff_rev sr
- JOIN staff s ON s.id = sr.sid JOIN staff_rev sr2 ON sr2.id = s.latest JOIN staff_alias sa ON sa.id = sr2.aid AND sa.rid = sr2.id
- WHERE TG_TABLE_NAME = 'staff' AND sr.sid = NEW.id
- ) x(id, title) ON h.id = x.id
-- join info about the deletion itself
- JOIN changes h2 ON h2.id = NEW.latest
- WHERE h.requester <> 1 -- exclude Multi
- -- exclude the user who deleted the entry
- AND h.requester <> h2.requester;
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
+ JOIN changes h2 ON h2.id = xedit.chid
+ -- Fetch the latest name/title of the entry
+ -- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs
+ JOIN ( SELECT v.title FROM vn v WHERE xtype = 'v' AND v.id = xedit.itemid
+ UNION SELECT r.title FROM releases r WHERE xtype = 'r' AND r.id = xedit.itemid
+ UNION SELECT p.name FROM producers p WHERE xtype = 'p' AND p.id = xedit.itemid
+ UNION SELECT c.name FROM chars c WHERE xtype = 'c' AND c.id = xedit.itemid
+ UNION SELECT sa.name FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE xtype = 's' AND s.id = xedit.itemid
+ ) x(title) ON true
+ WHERE h.type = xtype AND h.itemid = xedit.itemid
+ AND h.requester <> 1 -- exclude Multi
+ AND h.requester <> h2.requester; -- exclude the user who deleted the entry
+$$ LANGUAGE sql;
--- called on UPDATE vn / releases when (NOT OLD.hidden AND NEW.hidden)
-CREATE OR REPLACE FUNCTION notify_listdel() RETURNS trigger AS $$
-BEGIN
+
+-- Called when a non-deleted item has been edited.
+CREATE OR REPLACE FUNCTION notify_dbedit(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
- SELECT DISTINCT 'listdel'::notification_ntype,
- (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' ELSE 'r' END)::notification_ltype,
- u.uid, NEW.id, c.rev, x.title, c.requester
+ SELECT DISTINCT 'dbedit'::notification_ntype, xtype::text::notification_ltype, h.requester, xedit.itemid, xedit.rev, x.title, h2.requester
+ FROM changes h
+ -- join info about the edit itself
+ JOIN changes h2 ON h2.id = xedit.chid
+ -- Fetch the latest name/title of the entry
+ JOIN ( SELECT v.title FROM vn v WHERE xtype = 'v' AND v.id = xedit.itemid
+ UNION SELECT r.title FROM releases r WHERE xtype = 'r' AND r.id = xedit.itemid
+ UNION SELECT p.name FROM producers p WHERE xtype = 'p' AND p.id = xedit.itemid
+ UNION SELECT c.name FROM chars c WHERE xtype = 'c' AND c.id = xedit.itemid
+ UNION SELECT sa.name FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE xtype = 's' AND s.id = xedit.itemid
+ ) x(title) ON true
+ WHERE h.type = xtype AND h.itemid = xedit.itemid
+ AND h.requester <> h2.requester -- exclude the user who edited the entry
+ -- exclude users who don't want this notify
+ AND NOT EXISTS(SELECT 1 FROM users_prefs up WHERE uid = h.requester AND key = 'notify_nodbedit');
+$$ LANGUAGE sql;
+
+
+
+-- called when a VN/release entry has been deleted
+CREATE OR REPLACE FUNCTION notify_listdel(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
+ INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
+ SELECT DISTINCT 'listdel'::notification_ntype, xtype::text::notification_ltype, u.uid, xedit.itemid, xedit.rev, x.title, c.requester
-- look for users who should get this notify
FROM (
- -- voted on the VN
- SELECT uid FROM votes WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id
- -- VN in vnlist
- UNION SELECT uid FROM vnlists WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id
- -- VN in wishlist
- UNION SELECT uid FROM wlists WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id
- -- release in release list
- UNION SELECT uid FROM rlists WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id
+ SELECT uid FROM votes WHERE xtype = 'v' AND vid = xedit.itemid
+ UNION SELECT uid FROM vnlists WHERE xtype = 'v' AND vid = xedit.itemid
+ UNION SELECT uid FROM wlists WHERE xtype = 'v' AND vid = xedit.itemid
+ UNION SELECT uid FROM rlists WHERE xtype = 'r' AND rid = xedit.itemid
) u
-- fetch info about this edit
- JOIN changes c ON c.id = NEW.latest
+ JOIN changes c ON c.id = xedit.chid
JOIN (
- SELECT id, title FROM vn_rev WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id
- UNION SELECT id, title FROM releases_rev WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id
- ) x ON c.id = x.id
+ SELECT title FROM vn WHERE xtype = 'v' AND id = xedit.itemid
+ UNION SELECT title FROM releases WHERE xtype = 'r' AND id = xedit.itemid
+ ) x ON true
WHERE c.requester <> u.uid;
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-
--- called on UPDATE vn / producers / releases / chars when (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden)
--- this trigger is very similar to notify_dbdel()
-CREATE OR REPLACE FUNCTION notify_dbedit() RETURNS trigger AS $$
-BEGIN
- INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
- SELECT DISTINCT 'dbedit'::notification_ntype,
- (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' WHEN 'staff' THEN 's' ELSE 'c' END)::notification_ltype,
- h.requester, NEW.id, h2.rev, x.title, h2.requester
- -- look for changes of the edited entry
- FROM changes h
- JOIN ( SELECT vr.id, vr2.title FROM vn_rev vr
- JOIN vn v ON v.id = vr.vid JOIN vn_rev vr2 ON vr2.id = v.latest
- WHERE TG_TABLE_NAME = 'vn' AND vr.vid = NEW.id
- UNION SELECT rr.id, rr2.title FROM releases_rev rr
- JOIN releases r ON r.id = rr.rid JOIN releases_rev rr2 ON rr2.id = r.latest
- WHERE TG_TABLE_NAME = 'releases' AND rr.rid = NEW.id
- UNION SELECT pr.id, pr2.name FROM producers_rev pr
- JOIN producers p ON p.id = pr.pid JOIN producers_rev pr2 ON pr2.id = p.latest
- WHERE TG_TABLE_NAME = 'producers' AND pr.pid = NEW.id
- UNION SELECT cr.id, cr2.name FROM chars_rev cr
- JOIN chars c ON c.id = cr.cid JOIN chars_rev cr2 ON cr2.id = c.latest
- WHERE TG_TABLE_NAME = 'chars' AND cr.cid = NEW.id
- UNION SELECT sr.id, sa.name FROM staff_rev sr
- JOIN staff s ON s.id = sr.sid JOIN staff_rev sr2 ON sr2.id = s.latest JOIN staff_alias sa ON sa.id = sr2.aid AND sa.rid = sr2.id
- WHERE TG_TABLE_NAME = 'staff' AND sr.sid = NEW.id
- ) x(id, title) ON h.id = x.id
- -- join info about the deletion itself
- JOIN changes h2 ON h2.id = NEW.latest
- -- exclude the user who edited the entry
- WHERE h.requester <> h2.requester
- -- exclude users who don't want this notify
- AND NOT EXISTS(SELECT 1 FROM users_prefs up WHERE uid = h.requester AND key = 'notify_nodbedit');
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
+$$ LANGUAGE sql;
-- called on INSERT INTO threads_posts when (NEW.num = 1)
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index b43079dc..960b1978 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -1,6 +1,6 @@
-- Convention for database items with version control:
--
--- CREATE TABLE items (
+-- CREATE TABLE items ( -- dbentry_type=x
-- id SERIAL PRIMARY KEY,
-- locked boolean NOT NULL DEFAULT FALSE,
-- hidden boolean NOT NULL DEFAULT FALSE,
@@ -11,10 +11,12 @@
-- -- item-specific columns here
-- );
--
--- The history of the 'locked' and 'hidden' flags is recorded in the changes
--- table. It's possible for 'items' to have more item-specific columns than
--- 'items_hist'. Some columns are caches or otherwise autogenerated, and do not
--- need to be versioned.
+-- The '-- dbentry_type=x' comment is required, and is used by
+-- util/sqleditfunc.pl to generate the correct editing functions. The history
+-- of the 'locked' and 'hidden' flags is recorded in the changes table. It's
+-- possible for 'items' to have more item-specific columns than 'items_hist'.
+-- Some columns are caches or otherwise autogenerated, and do not need to be
+-- versioned.
--
-- item-related tables work roughly the same:
--
@@ -31,6 +33,10 @@
-- tables related to the item are just a cache/view into the latest versions.
-- All modifications to the item tables has to go through the edit_* functions
-- in func.sql, these are also responsible for keeping things synchronized.
+--
+-- Note: Every CREATE TABLE clause and each column should be on a separate
+-- line. This file is parsed by util/sqleditfunc.pl, and it doesn't implement a
+-- full SQL query parser.
-- affiliate_links
@@ -74,7 +80,7 @@ CREATE TABLE changes (
);
-- chars
-CREATE TABLE chars (
+CREATE TABLE chars ( -- dbentry_type=c
id SERIAL PRIMARY KEY,
locked boolean NOT NULL DEFAULT FALSE,
hidden boolean NOT NULL DEFAULT FALSE,
@@ -172,7 +178,7 @@ CREATE TABLE notifications (
);
-- producers
-CREATE TABLE producers (
+CREATE TABLE producers ( -- dbentry_type=p
id SERIAL PRIMARY KEY,
locked boolean NOT NULL DEFAULT FALSE,
hidden boolean NOT NULL DEFAULT FALSE,
@@ -224,7 +230,7 @@ CREATE TABLE quotes (
);
-- releases
-CREATE TABLE releases (
+CREATE TABLE releases ( -- dbentry_type=r
id SERIAL PRIMARY KEY,
locked boolean NOT NULL DEFAULT FALSE,
hidden boolean NOT NULL DEFAULT FALSE,
@@ -377,7 +383,7 @@ CREATE TABLE sessions (
);
-- staff
-CREATE TABLE staff (
+CREATE TABLE staff ( -- dbentry_type=s
id SERIAL PRIMARY KEY,
locked boolean NOT NULL DEFAULT FALSE,
hidden boolean NOT NULL DEFAULT FALSE,
@@ -574,7 +580,7 @@ CREATE TABLE users_prefs (
);
-- vn
-CREATE TABLE vn (
+CREATE TABLE vn ( -- dbentry_type=v
id SERIAL PRIMARY KEY,
locked boolean NOT NULL DEFAULT FALSE,
hidden boolean NOT NULL DEFAULT FALSE,
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index b3ac137f..d9b0f4c5 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -78,12 +78,12 @@ ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_chid_fke
ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_scr_fkey FOREIGN KEY (scr) REFERENCES screenshots (id);
ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
-ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid);
+ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
-ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid);
+ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
ALTER TABLE vnlists ADD CONSTRAINT vnlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE vnlists ADD CONSTRAINT vnlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
@@ -99,6 +99,7 @@ CREATE INDEX releases_producers_pid ON releases_producers (pid);
CREATE INDEX releases_vn_vid ON releases_vn (vid);
CREATE INDEX staff_alias_id ON staff_alias (id);
CREATE INDEX tags_vn_date ON tags_vn (date);
+CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid);
CREATE INDEX tags_vn_vid ON tags_vn (vid);
CREATE INDEX threads_posts_ts ON threads_posts USING gin(to_tsvector('english', strip_bb_tags(msg)));
CREATE INDEX vn_staff_aid ON vn_staff (aid);
diff --git a/util/sql/triggers.sql b/util/sql/triggers.sql
index 6988a62a..962610a7 100644
--- a/util/sql/triggers.sql
+++ b/util/sql/triggers.sql
@@ -1,9 +1,3 @@
-CREATE TRIGGER hidlock_update BEFORE UPDATE ON vn FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
-CREATE TRIGGER hidlock_update BEFORE UPDATE ON producers FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
-CREATE TRIGGER hidlock_update BEFORE UPDATE ON releases FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
-CREATE TRIGGER hidlock_update BEFORE UPDATE ON chars FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
-CREATE TRIGGER hidlock_update BEFORE UPDATE ON staff FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
-
CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
@@ -34,48 +28,21 @@ CREATE TRIGGER vn_anime_aid_edit BEFORE UPDATE ON vn_anime
CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW WHEN (NEW.lastfetch IS NULL) EXECUTE PROCEDURE anime_fetch_notify();
CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW
- WHEN (OLD.rgraph IS DISTINCT FROM NEW.rgraph
- OR OLD.latest IS DISTINCT FROM NEW.latest
- OR OLD.c_released IS DISTINCT FROM NEW.c_released
- OR OLD.c_languages IS DISTINCT FROM NEW.c_languages
+ WHEN ((OLD.rgraph IS NOT NULL AND NEW.rgraph IS NULL)
+ OR (NEW.rgraph IS NOT NULL AND (OLD.c_released IS DISTINCT FROM NEW.c_released OR OLD.c_languages IS DISTINCT FROM NEW.c_languages))
) EXECUTE PROCEDURE vn_relgraph_notify();
-CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW
- WHEN (OLD.rgraph IS DISTINCT FROM NEW.rgraph
- OR OLD.latest IS DISTINCT FROM NEW.latest
- ) EXECUTE PROCEDURE producer_relgraph_notify();
+CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.rgraph IS NOT NULL AND NEW.rgraph IS NULL) EXECUTE PROCEDURE producer_relgraph_notify();
CREATE TRIGGER insert_notify AFTER INSERT ON changes FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
CREATE TRIGGER insert_notify AFTER INSERT ON threads_posts FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
CREATE TRIGGER insert_notify AFTER INSERT ON traits FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
-CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW
- WHEN (OLD.latest IS DISTINCT FROM NEW.latest OR OLD.hidden IS DISTINCT FROM NEW.hidden)
- EXECUTE PROCEDURE release_vncache_update();
-
CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm();
-CREATE TRIGGER notify_dbdel AFTER UPDATE ON vn FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
-CREATE TRIGGER notify_dbdel AFTER UPDATE ON producers FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
-CREATE TRIGGER notify_dbdel AFTER UPDATE ON releases FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
-CREATE TRIGGER notify_dbdel AFTER UPDATE ON chars FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
-CREATE TRIGGER notify_dbdel AFTER UPDATE ON staff FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
-CREATE TRIGGER notify_listdel AFTER UPDATE ON vn FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel();
-CREATE TRIGGER notify_listdel AFTER UPDATE ON releases FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel();
-CREATE TRIGGER notify_dbedit AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
-CREATE TRIGGER notify_dbedit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
-CREATE TRIGGER notify_dbedit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
-CREATE TRIGGER notify_dbedit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
-CREATE TRIGGER notify_dbedit AFTER UPDATE ON staff FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
-CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW
- WHEN (OLD.c_search IS NOT NULL AND NEW.c_search IS NULL
- OR NEW.latest IS DISTINCT FROM OLD.latest
- ) EXECUTE PROCEDURE vn_vnsearch_notify();
-CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON releases FOR EACH ROW
- WHEN (NEW.hidden IS DISTINCT FROM OLD.hidden OR NEW.latest IS DISTINCT FROM OLD.latest)
- EXECUTE PROCEDURE vn_vnsearch_notify();
+CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.c_search IS NOT NULL AND NEW.c_search IS NULL) EXECUTE PROCEDURE vn_vnsearch_notify();
CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON vnlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER INSERT ON rlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
diff --git a/util/sqleditfunc.pl b/util/sqleditfunc.pl
new file mode 100755
index 00000000..773214f9
--- /dev/null
+++ b/util/sqleditfunc.pl
@@ -0,0 +1,112 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+use List::Util 'any';
+
+use Cwd 'abs_path';
+our $ROOT;
+BEGIN { ($ROOT = abs_path $0) =~ s{/util/sqleditfunc\.pl$}{}; }
+
+my %tabletypes; # table_name => dbentry_type
+my %tables; # table_name => [ column_names ]
+my %items; # item_name => { tables_without_hist => [ data_column_names ] }
+
+
+# Fills %tables
+sub readschema {
+ open my $F, '<', "$ROOT/util/sql/schema.sql" or die $!;
+ my $table = '';
+ while(<$F>) {
+ chomp;
+ if(/^\s*CREATE\s+TABLE\s+([^ ]+)/) {
+ $table = $1;
+ $tables{$table} = [];
+ $tabletypes{$table} = $1 if /--.*\s+dbentry_type=(.)/;
+ } elsif($table && /^\s+("?[^\( ]+"?)\s/ && !/^\s+PRIMARY\s+KEY/) {
+ push @{$tables{$table}}, $1;
+ }
+ }
+}
+
+
+sub gensql {
+ my($template, $item) = @_;
+
+ # table_name_without_hist => [ column_names_without_chid ]
+ my %ts = map +($_, [ grep !/^chid$/, @{$tables{"${_}_hist"}} ]), map /^${item}_/ && /^(.+)_hist$/ ? $1 : (), keys %tables;
+
+ my %replace = ( item => $item, itemtype => $tabletypes{$item} );
+ $replace{createtemptables} = join "\n", map sprintf(
+ " CREATE TEMPORARY TABLE edit_%s (LIKE %s INCLUDING DEFAULTS INCLUDING CONSTRAINTS);\n".
+ " ALTER TABLE edit_%1\$s DROP COLUMN %s;",
+ $_, $_ eq 'staff_alias' ? ($_, 'id') : ("${_}_hist", 'chid') # staff_alias copies from the non-_hist table, because it needs the sequence
+ ), sort keys %ts;
+ $replace{temptablenames} = join ', ', map "edit_$_", sort keys %ts;
+ $replace{loadtemptables} = join "\n", map sprintf(
+ " INSERT INTO edit_%s (%s) SELECT %2\$s FROM %1\$s_hist WHERE chid = xchid;",
+ $_, join ', ', @{$ts{$_}}), sort keys %ts;
+ $replace{copyfromtemp} = join "\n", map sprintf(
+ " DELETE FROM %1\$s WHERE id = r.itemid;\n".
+ " INSERT INTO %1\$s (id, %2\$s) SELECT r.itemid, %2\$s FROM edit_%1\$s;\n".
+ " INSERT INTO %1\$s_hist (chid, %2\$s) SELECT r.chid, %2\$s FROM edit_%1\$s;",
+ $_, join ', ', @{$ts{$_}}), grep $_ ne $item, sort keys %ts;
+ $replace{copymainfromtemp} = sprintf
+ " INSERT INTO %1\$s_hist (chid, %2\$s) SELECT r.chid, %2\$s FROM edit_%1\$s;\n".
+ " UPDATE %1\$s SET locked = (SELECT ilock FROM edit_revision), hidden = (SELECT ihid FROM edit_revision),\n".
+ " %3\$s FROM edit_%1\$s x WHERE id = r.itemid;",
+ $item, join(', ', @{$ts{$item}}), join(', ', map "$_ = x.$_", @{$ts{$item}});
+
+ $template =~ s/{([a-z]+)}/$replace{$1}/g;
+ $template;
+}
+
+
+readschema;
+my $template = join '', <DATA>;
+
+open my $F, '>', "$ROOT/util/sql/editfunc.sql" or die $!;
+print $F "-- Automatically generated by util/sqleditfunc.pl. DO NOT EDIT.\n";
+print $F gensql $template, $_ for sort keys %tabletypes;
+
+
+__DATA__
+
+CREATE OR REPLACE FUNCTION edit_{itemtype}_init(xid integer, xrev integer) RETURNS void AS $$
+DECLARE
+ xchid integer;
+BEGIN
+ -- create tables, based on existing tables
+ BEGIN
+{createtemptables}
+ EXCEPTION WHEN duplicate_table THEN
+ TRUNCATE {temptablenames};
+ END;
+ -- Create edit_revision table and get relevant change ID.
+ SELECT edit_revtable('{itemtype}', xid, xrev) INTO xchid;
+ -- new entry, load defaults
+ IF xchid IS NULL THEN
+ INSERT INTO edit_{item} DEFAULT VALUES;
+ -- otherwise, load revision
+ ELSE
+{loadtemptables}
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION edit_{itemtype}_commit() RETURNS edit_rettype AS $$
+DECLARE
+ r edit_rettype;
+BEGIN
+ IF (SELECT COUNT(*) FROM edit_{item}) <> 1 THEN
+ RAISE 'edit_{item} must have exactly one row!';
+ END IF;
+ SELECT INTO r * FROM edit_commit();
+{copyfromtemp}
+{copymainfromtemp}
+ PERFORM edit_committed('{itemtype}', r);
+ RETURN r;
+END;
+$$ LANGUAGE plpgsql;
+
diff --git a/util/updates/update_2.25-sqlsplit.sql b/util/updates/update_2.25-sqlsplit.sql
index eb1ce231..9104972a 100644
--- a/util/updates/update_2.25-sqlsplit.sql
+++ b/util/updates/update_2.25-sqlsplit.sql
@@ -31,6 +31,15 @@ ALTER TABLE vn_screenshots RENAME TO vn_screenshots_old;
ALTER TABLE vn_seiyuu RENAME TO vn_seiyuu_old;
ALTER TABLE vn_staff RENAME TO vn_staff_old;
+-- XXX: The names of these sequences depend on how the corresponding tables
+-- were generated. The names below are the ones in the production database.
+ALTER SEQUENCE changes_id_seq RENAME TO changes_id_seq_old;
+ALTER SEQUENCE chars_id_seq RENAME TO chars_id_seq_old;
+ALTER SEQUENCE producers_id_seq RENAME TO producers_id_seq_old;
+ALTER SEQUENCE releases_id_seq RENAME TO releases_id_seq_old;
+ALTER SEQUENCE staff_alias_id_seq RENAME TO staff_alias_id_seq_old;
+ALTER SEQUENCE staff_id_seq RENAME TO staff_id_seq_old;
+ALTER SEQUENCE vn_id_seq RENAME TO vn_id_seq_old;
\i util/sql/schema.sql
@@ -183,6 +192,14 @@ INSERT INTO vn_staff_hist SELECT vid, aid, role, note
FROM vn_staff_old;
+SELECT setval('changes_id_seq', nextval('changes_id_seq_old'));
+SELECT setval('chars_id_seq', nextval('chars_id_seq_old'));
+SELECT setval('producers_id_seq', nextval('producers_id_seq_old'));
+SELECT setval('releases_id_seq', nextval('releases_id_seq_old'));
+SELECT setval('staff_alias_aid_seq', nextval('staff_alias_id_seq_old')); -- note the change from id to aid
+SELECT setval('staff_id_seq', nextval('staff_id_seq_old'));
+SELECT setval('vn_id_seq', nextval('vn_id_seq_old'));
+
-- Dropping all tables with CASCADE causes all foreign key references to and
-- from the tables to be dropped as well. This is exactly what we want, so we
@@ -214,3 +231,27 @@ DROP TABLE vn_seiyuu_old CASCADE;
DROP TABLE vn_staff_old CASCADE;
\i util/sql/tableattrs.sql
+
+DROP FUNCTION edit_revtable(dbentry_type, integer);
+DROP FUNCTION edit_vn_init(integer);
+DROP FUNCTION edit_vn_commit();
+DROP FUNCTION edit_release_init(integer);
+DROP FUNCTION edit_release_commit();
+DROP FUNCTION edit_producer_init(integer);
+DROP FUNCTION edit_producer_commit();
+DROP FUNCTION edit_char_init(integer);
+DROP FUNCTION edit_char_commit();
+DROP FUNCTION edit_staff_init(integer);
+DROP FUNCTION edit_staff_commit();
+DROP FUNCTION release_vncache_update();
+DROP FUNCTION notify_dbdel();
+DROP FUNCTION notify_dbedit();
+DROP FUNCTION notify_listdel();
+DROP FUNCTION update_hidlock();
+
+DROP TYPE edit_rettype CASCADE;
+CREATE TYPE edit_rettype AS (itemid integer, chid integer, rev integer);
+
+\i util/sql/func.sql
+\i util/sql/editfunc.sql
+\i util/sql/triggers.sql