diff options
Diffstat (limited to 'util')
-rwxr-xr-x | util/dbgraph.pl | 2 | ||||
-rwxr-xr-x | util/jsgen.pl | 5 | ||||
-rw-r--r-- | util/sql/all.sql | 12 | ||||
-rw-r--r-- | util/sql/func.sql | 66 | ||||
-rw-r--r-- | util/sql/schema.sql | 40 | ||||
-rw-r--r-- | util/updates/update_2.16.sql | 86 | ||||
-rwxr-xr-x | util/vndb.pl | 44 |
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) |