summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
Diffstat (limited to 'util')
-rwxr-xr-xutil/dbgraph.pl2
-rwxr-xr-xutil/jsgen.pl5
-rw-r--r--util/sql/all.sql12
-rw-r--r--util/sql/func.sql66
-rw-r--r--util/sql/schema.sql40
-rw-r--r--util/updates/update_2.16.sql86
-rwxr-xr-xutil/vndb.pl44
7 files changed, 215 insertions, 40 deletions
diff --git a/util/dbgraph.pl b/util/dbgraph.pl
index a8f1cbe2..f7cb9923 100755
--- a/util/dbgraph.pl
+++ b/util/dbgraph.pl
@@ -23,7 +23,7 @@ my %subgraphs = (
'Producers' => [qw| FFFFCC producers producers_rev producers_relations |],
'Releases' => [qw| C8FFC8 releases releases_rev releases_media releases_platforms releases_producers releases_lang releases_vn |],
'Visual Novels' => [qw| FFE6BE vn vn_rev vn_relations vn_anime vn_screenshots |],
- 'Users' => [qw| CCFFFF users votes rlists wlists sessions notifications |],
+ 'Users' => [qw| CCFFFF users votes rlists wlists vnlists sessions notifications users_prefs |],
'Discussion board' => [qw| FFDCDC threads threads_boards threads_posts |],
'Tags' => [qw| FFC8C8 tags tags_aliases tags_parents tags_vn |],
'Misc' => [qw| F5F5F5 changes anime screenshots stats_cache quotes relgraphs |],
diff --git a/util/jsgen.pl b/util/jsgen.pl
index 4af20b27..58b0ac9a 100755
--- a/util/jsgen.pl
+++ b/util/jsgen.pl
@@ -101,10 +101,9 @@ sub resolutions {
sub jsgen {
l10n_load();
my $common = '';
- $common .= sprintf "rlst_rstat = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{rlst_rstat}};
- $common .= sprintf "rlst_vstat = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{rlst_vstat}};
+ $common .= sprintf "rlist_status = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{rlist_status}};
$common .= sprintf "cookie_prefix = '%s';\n", $S{cookie_prefix};
- $common .= sprintf "age_ratings = [ %s ];\n", join ',', map !defined $_ ? -1 : $_, @{$S{age_ratings}};
+ $common .= sprintf "age_ratings = [ %s ];\n", join ',', @{$S{age_ratings}};
$common .= sprintf "languages = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{languages}};
$common .= sprintf "platforms = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{platforms}};
$common .= sprintf "media = [ %s ];\n", join ', ', map qq{"$_"}, sort keys %{$S{media}};
diff --git a/util/sql/all.sql b/util/sql/all.sql
index 4408c6df..09a6d214 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -6,10 +6,11 @@
CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p');
CREATE TYPE edit_rettype AS (iid integer, cid integer, rev integer);
-CREATE TYPE language AS ENUM('cs', 'da', 'de', 'en', 'es', 'fi', 'fr', 'hu', 'it', 'ja', 'ko', 'nl', 'no', 'pl', 'pt', 'ru', 'sk', 'sv', 'tr', 'vi', 'zh');
+CREATE TYPE language AS ENUM ('cs', 'da', 'de', 'en', 'es', 'fi', 'fr', 'hu', 'it', 'ja', 'ko', 'nl', 'no', 'pl', 'pt-pt', 'pt-br', 'ru', 'sk', 'sv', 'tr', 'vi', 'zh');
CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce');
CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 't');
+CREATE TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'filter_vn', 'filter_release', 'show_nsfw', 'hide_list', 'notify_nodbedit', 'notify_announce');
CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial');
CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
@@ -96,14 +97,19 @@ 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 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();
+
-- Sequences used for ID generation of items not in the DB
CREATE SEQUENCE covers_seq;
-- Rows that are assumed to be available
-INSERT INTO users (id, username, mail, rank, notify_dbdel) VALUES (0, 'deleted', 'del@vndb.org', 0, false);
-INSERT INTO users (username, mail, rank, notify_dbdel) VALUES ('multi', 'multi@vndb.org', 0, false);
+INSERT INTO users (id, username, mail, rank) VALUES (0, 'deleted', 'del@vndb.org', 0);
+INSERT INTO users (username, mail, rank) VALUES ('multi', 'multi@vndb.org', 0);
+INSERT INTO users_prefs (uid, key, value) VALUES (0, 'notify_nodbedit', '1');
+INSERT INTO users_prefs (uid, key, value) VALUES (1, 'notify_nodbedit', '1');
INSERT INTO stats_cache (section, count) VALUES
('users', 1),
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 53af121c..0248c69a 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -455,6 +455,53 @@ $$ LANGUAGE plpgsql;
+-- For each row in rlists, there should be at least one corresponding row in
+-- vnlists for at least one of the VNs linked to that release.
+-- 1. When a row is deleted from vnlists, also remove all rows from rlists that
+-- would otherwise not have a corresponding row in vnlists
+-- 2. When a row is inserted to rlists and there is not yet a corresponding row
+-- in vnlists, add a row in vnlists (with status=unknown) for each vn linked
+-- to the release.
+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
+ -- 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
+ 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
+ ));
+
+ -- 2.
+ ELSE
+ 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
+ -- 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
+ );
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
-- Send a notify whenever anime info should be fetched
CREATE OR REPLACE FUNCTION anime_fetch_notify() RETURNS trigger AS $$
BEGIN NOTIFY anime; RETURN NULL; END;
@@ -702,14 +749,13 @@ BEGIN
-- 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
+ 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
+ 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
- -- there's also a special case which we're ignoring here:
- -- when a VN linked to a release in a user's release list is deleted
- -- normally, the releases are also deleted, so a notify is generated anyway
+ UNION SELECT uid FROM rlists WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id
) u
-- fetch info about this edit
JOIN changes c ON c.id = NEW.latest
@@ -744,12 +790,10 @@ BEGIN
) x(id, title) ON c.id = x.id
-- join info about the deletion itself
JOIN changes c2 ON c2.id = NEW.latest
- -- join info about the user who should get this notification
- JOIN users u ON u.id = c.requester
-- exclude the user who edited the entry
WHERE c.requester <> c2.requester
-- exclude users who don't want this notify
- AND u.notify_dbedit;
+ AND NOT EXISTS(SELECT 1 FROM users_prefs up WHERE uid = c.requester AND key = 'notify_nodbedit');
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
@@ -759,11 +803,11 @@ $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$
BEGIN
INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
- SELECT 'announce', 't', u.id, t.id, 1, t.title, NEw.uid
+ SELECT 'announce', 't', up.uid, t.id, 1, t.title, NEw.uid
FROM threads t
JOIN threads_boards tb ON tb.tid = t.id
-- get the users who want this announcement
- JOIN users u ON u.notify_announce
+ JOIN users_prefs up ON up.key = 'notify_announce'
WHERE t.id = NEW.tid
AND tb.type = 'an' -- announcement board
AND NOT t.hidden;
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 48367e4c..676d6400 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -156,8 +156,7 @@ CREATE TABLE relgraphs (
CREATE TABLE rlists (
uid integer NOT NULL DEFAULT 0,
rid integer NOT NULL DEFAULT 0,
- vstat smallint NOT NULL DEFAULT 0,
- rstat smallint NOT NULL DEFAULT 0,
+ status smallint NOT NULL DEFAULT 0,
added timestamptz NOT NULL DEFAULT NOW(),
PRIMARY KEY(uid, rid)
);
@@ -267,18 +266,20 @@ CREATE TABLE users (
rank smallint NOT NULL DEFAULT 3,
passwd bytea NOT NULL DEFAULT '',
registered timestamptz NOT NULL DEFAULT NOW(),
- show_nsfw boolean NOT NULL DEFAULT FALSE,
- show_list boolean NOT NULL DEFAULT TRUE,
c_votes integer NOT NULL DEFAULT 0,
c_changes integer NOT NULL DEFAULT 0,
- skin varchar(128) NOT NULL DEFAULT '',
- customcss text NOT NULL DEFAULT '',
ip inet NOT NULL DEFAULT '0.0.0.0',
c_tags integer NOT NULL DEFAULT 0,
salt character(9) NOT NULL DEFAULT '',
- ign_votes boolean NOT NULL DEFAULT FALSE,
- notify_dbedit boolean NOT NULL DEFAULT TRUE,
- notify_announce boolean NOT NULL DEFAULT FALSE
+ ign_votes boolean NOT NULL DEFAULT FALSE
+);
+
+-- users_prefs
+CREATE TABLE users_prefs (
+ uid integer NOT NULL,
+ key prefs_key NOT NULL,
+ value varchar NOT NULL,
+ PRIMARY KEY(uid, key)
);
-- vn
@@ -290,7 +291,7 @@ CREATE TABLE vn (
rgraph integer,
c_released integer NOT NULL DEFAULT 0,
c_languages language[] NOT NULL DEFAULT '{}',
- c_platforms varchar(32) NOT NULL DEFAULT '',
+ c_platforms varchar NOT NULL DEFAULT '',
c_popularity real,
c_rating real,
c_votecount integer NOT NULL DEFAULT 0,
@@ -340,6 +341,17 @@ CREATE TABLE vn_screenshots (
PRIMARY KEY(vid, scr)
);
+
+-- vnlists
+CREATE TABLE vnlists (
+ uid integer NOT NULL,
+ vid integer NOT NULL,
+ status smallint NOT NULL DEFAULT 0,
+ added TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ notes varchar NOT NULL DEFAULT '',
+ PRIMARY KEY(uid, vid)
+);
+
-- votes
CREATE TABLE votes (
vid integer NOT NULL DEFAULT 0,
@@ -394,6 +406,7 @@ ALTER TABLE threads ADD FOREIGN KEY (id, count) REFERENCES threads_p
ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads (id);
ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id);
+ALTER TABLE users_prefs ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id);
@@ -405,8 +418,15 @@ ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn
ALTER TABLE vn_screenshots ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
ALTER TABLE vn_screenshots ADD FOREIGN KEY (scr) REFERENCES screenshots (id);
ALTER TABLE vn_screenshots ADD FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE vnlists ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE vnlists ADD FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id);
+
+
+CREATE INDEX releases_vn_vid ON releases_vn (vid);
+CREATE INDEX tags_vn_date ON tags_vn (date);
+
diff --git a/util/updates/update_2.16.sql b/util/updates/update_2.16.sql
new file mode 100644
index 00000000..2d354f03
--- /dev/null
+++ b/util/updates/update_2.16.sql
@@ -0,0 +1,86 @@
+
+-- remove the NOT NULL from rr.minage and use -1 when unknown
+UPDATE releases_rev SET minage = -1 WHERE minage IS NULL;
+ALTER TABLE releases_rev ALTER COLUMN minage SET DEFAULT -1;
+ALTER TABLE releases_rev ALTER COLUMN minage DROP NOT NULL;
+
+
+-- speed up get-releases-by-vn queries
+CREATE INDEX releases_vn_vid ON releases_vn (vid);
+
+
+-- add vnlists table
+CREATE TABLE vnlists (
+ uid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE,
+ vid integer NOT NULL REFERENCES vn (id),
+ status smallint NOT NULL DEFAULT 0,
+ added TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ notes varchar NOT NULL DEFAULT '',
+ PRIMARY KEY(uid, vid)
+);
+
+
+-- load new function(s)
+\i util/sql/func.sql
+
+
+-- convert from rlists.vstat
+INSERT INTO vnlists (uid, vid, status, added) SELECT
+ i.uid, i.vid, COALESCE(MIN(CASE WHEN rl.vstat = 0 THEN NULL ELSE rl.vstat END), 0), MIN(rl.added)
+ FROM (
+ SELECT DISTINCT rl.uid, rv.vid
+ FROM rlists rl
+ JOIN releases r ON r.id = rl.rid
+ JOIN releases_vn rv ON rv.rid = r.latest
+ ) AS i(uid,vid)
+ JOIN rlists rl ON rl.uid = i.uid
+ JOIN releases r ON r.id = rl.rid
+ JOIN releases_vn rv ON rv.rid = r.latest AND rv.vid = i.vid
+ GROUP BY i.uid, i.vid;
+
+
+-- add constraints triggers
+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();
+
+-- remove rlists.vstat and rename rlists.rstat
+ALTER TABLE rlists DROP COLUMN vstat;
+ALTER TABLE rlists RENAME COLUMN rstat TO status;
+
+
+
+-- add users_prefs table
+CREATE TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'filter_vn', 'filter_release', 'show_nsfw', 'hide_list', 'notify_nodbedit', 'notify_announce');
+CREATE TABLE users_prefs (
+ uid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE,
+ key prefs_key NOT NULL,
+ value varchar NOT NULL,
+ PRIMARY KEY(uid, key)
+);
+
+-- convert from users.* to users_prefs
+INSERT INTO users_prefs (uid, key, value)
+ SELECT id, 'skin'::prefs_key, skin FROM users WHERE skin <> ''
+ UNION ALL
+ SELECT id, 'customcss', customcss FROM users WHERE customcss <> ''
+ UNION ALL
+ SELECT id, 'show_nsfw', '1' FROM users WHERE show_nsfw
+ UNION ALL
+ SELECT id, 'hide_list', '1' FROM users WHERE NOT show_list
+ UNION ALL
+ SELECT id, 'notify_nodbedit', '1' FROM users WHERE NOT notify_dbedit
+ UNION ALL
+ SELECT id, 'notify_announce', '1' FROM users WHERE notify_announce;
+
+-- remove unused columns from the user table
+ALTER TABLE users DROP COLUMN skin;
+ALTER TABLE users DROP COLUMN customcss;
+ALTER TABLE users DROP COLUMN show_nsfw;
+ALTER TABLE users DROP COLUMN show_list;
+ALTER TABLE users DROP COLUMN notify_dbedit;
+ALTER TABLE users DROP COLUMN notify_announce;
+
+
+-- remove size constraint on vn.c_platforms
+ALTER TABLE vn ALTER COLUMN c_platforms TYPE varchar;
+
diff --git a/util/vndb.pl b/util/vndb.pl
index 183cea4d..0fd6e08d 100755
--- a/util/vndb.pl
+++ b/util/vndb.pl
@@ -54,22 +54,42 @@ YAWF::init(
sub reqinit {
my $self = shift;
+ # check authentication cookies
+ $self->authInit;
+
# Determine language
- # if the cookie is set, use that. Otherwise, interpret the Accept-Language header or fall back to English.
- # if the cookie is set and is the same as either the Accept-Language header or the fallback, remove it
- my $conf = $self->reqCookie('l10n');
- $conf = '' if !$conf || !grep $_ eq $conf, VNDB::L10N::languages;
+ my $cookie = $self->reqCookie('l10n');
+ $cookie = '' if !$cookie || !grep $_ eq $cookie, VNDB::L10N::languages;
+ my $handle = VNDB::L10N->get_handle(); # falls back to English
+ my $browser = $handle->language_tag();
+ my $rmcookie = 0;
+
+ # when logged in, the setting is kept in the DB even if it's the same as what
+ # the browser requests. This is to ensure a user gets the same language even
+ # when switching PCs
+ if($self->authInfo->{id}) {
+ my $db = $self->authPref('l10n');
+ if($db && !grep $_ eq $db, VNDB::L10N::languages) {
+ $self->authPref(l10n => undef);
+ $db = '';
+ }
+ $rmcookie = 1 if $cookie;
+ if(!$db && $cookie && $cookie ne $browser) {
+ $self->authPref(l10n => $cookie);
+ $db = $cookie;
+ }
+ $handle = VNDB::L10N->get_handle($db) if $db && $db ne $browser;
+ }
- $self->{l10n} = VNDB::L10N->get_handle(); # this uses I18N::LangTags::Detect
+ else {
+ $rmcookie = 1 if $cookie && $cookie eq $browser;
+ $handle = VNDB::L10N->get_handle($cookie) if $cookie && $browser ne $cookie;
+ }
$self->resHeader('Set-Cookie', "l10n= ; expires=Sat, 01-Jan-2000 00:00:00 GMT; path=/; domain=$self->{cookie_domain}")
- if $conf && $self->{l10n}->language_tag() eq $conf;
- $self->{l10n} = VNDB::L10N->get_handle($conf) if $conf && $self->{l10n}->language_tag() ne $conf;
-
-
- # check authentication cookies
- $self->authInit;
+ if $rmcookie;
+ $self->{l10n} = $handle;
- # check for IE6
+ # check for IE
if($self->reqHeader('User-Agent') && $self->reqHeader('User-Agent') =~ /MSIE [67]/
&& !$self->reqCookie('ie-sucks') && $self->reqPath ne 'we-dont-like-ie') {
# act as if we're opening /we-dont-like-ie6 (ugly hack, until YAWF supports preventing URL handlers from firing)