diff options
Diffstat (limited to 'util')
| -rwxr-xr-x | util/dbgraph.pl | 2 | ||||
| -rwxr-xr-x | util/skingen.pl | 76 | ||||
| -rw-r--r-- | util/sql/all.sql | 22 | ||||
| -rw-r--r-- | util/sql/func.sql | 312 | ||||
| -rw-r--r-- | util/sql/schema.sql | 38 | ||||
| -rw-r--r-- | util/updates/update_2.11.sql | 120 | ||||
| -rwxr-xr-x | util/vndb.pl | 32 |
7 files changed, 465 insertions, 137 deletions
diff --git a/util/dbgraph.pl b/util/dbgraph.pl index 10297b66..a8f1cbe2 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 |], + 'Users' => [qw| CCFFFF users votes rlists wlists sessions notifications |], '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/skingen.pl b/util/skingen.pl index 73e4fdc7..37504921 100755 --- a/util/skingen.pl +++ b/util/skingen.pl @@ -1,6 +1,5 @@ #!/usr/bin/perl -package VNDB; use strict; use warnings; @@ -8,81 +7,66 @@ use Cwd 'abs_path'; use Image::Magick; eval { require CSS::Minifier::XS }; - -our($ROOT, %O); +our $ROOT; BEGIN { ($ROOT = abs_path $0) =~ s{/util/skingen\.pl$}{}; } - -if(@ARGV) { - writeskin(readskin($_)) for (@ARGV); -} else { - /([^\/]+)$/ && writeskin(readskin($1)) for (glob($ROOT.'/static/s/*')); -} +use lib "$ROOT/lib"; +use SkinFile; -sub readskin { # skin name +sub writeskin { # $name my $name = shift; - my %o; - open my $F, '<', $ROOT.'/static/s/'.$name.'/conf' or die $!; - while(<$F>) { - chomp; - s/\r//g; - s{[\t\s]*//.+$}{}; - next if !/^([a-z0-9]+)[\t\s]+(.+)$/; - $o{$1} = $2; - } - close $F; - $o{_name} = $name; - return \%o; -} - - -sub writeskin { # $obj - my $o = shift; - - # fix image locations - $o->{$_} && ($o->{$_} = '/s/'.$o->{_name}.'/'.$o->{$_}) for (qw|imglefttop imgrighttop|); + my $skin = SkinFile->new("$ROOT/static/s", $name); + my %o = map +($_ => $skin->get($_)), $skin->get; # get the right top image - if($o->{imgrighttop}) { + if($o{imgrighttop}) { + my $path = "/s/$name/$o{imgrighttop}"; my $img = Image::Magick->new; - $img->Read($ROOT.'/static'.$o->{imgrighttop}); - $o->{_bgright} = sprintf 'background: url(%s) no-repeat; width: %dpx; height: %dpx', - $o->{imgrighttop}, $img->Get('width'), $img->Get('height'); + $img->Read("$ROOT/static$path"); + $o{_bgright} = sprintf 'background: url(%s) no-repeat; width: %dpx; height: %dpx', + $path, $img->Get('width'), $img->Get('height'); } else { - $o->{_bgright} = 'display: none'; + $o{_bgright} = 'display: none'; } # body background - if(!$o->{imglefttop}) { - $o->{_bodybg} = "background-color: $o->{bodybg}"; + if($o{imglefttop}) { + $o{_bodybg} = sprintf 'background: %s url(/s/%s/%s) no-repeat', $o{bodybg}, $name, $o{imglefttop}; } else { - $o->{_bodybg} = "background: $o->{bodybg} url($o->{imglefttop}) no-repeat"; + $o{_bodybg} = sprintf 'background-color: %s', $o{bodybg}; } # main title - $o->{_maintitle} = $o->{maintitle} ? "color: $o->{maintitle}" : 'display: none'; + $o{_maintitle} = $o{maintitle} ? "color: ".$o{maintitle} : 'display: none'; # create boxbg.png my $img = Image::Magick->new(size => '1x1'); - $img->Read('xc:'.$o->{boxbg}); - $img->Write(filename => $ROOT.'/static/s/'.$o->{_name}.'/boxbg.png'); - $o->{_boxbg} = '/s/'.$o->{_name}.'/boxbg.png'; + $img->Read("xc:$o{boxbg}"); + $img->Write(filename => "$ROOT/static/s/$name/boxbg.png"); + $o{_boxbg} = "/s/$name/boxbg.png"; # get the blend color $img = Image::Magick->new(size => '1x1'); - $img->Read('xc:'.$o->{bodybg}, 'xc:'.$o->{boxbg}); + $img->Read("xc:$o{bodybg}", "xc:$o{boxbg}"); $img = $img->Flatten(); - $o->{_blendbg} = '#'.join '', map sprintf('%02x', $_*255), $img->GetPixel(x=>1,y=>1); + $o{_blendbg} = '#'.join '', map sprintf('%02x', $_*255), $img->GetPixel(x=>1,y=>1); # write the CSS open my $CSS, '<', "$ROOT/data/style.css" or die $!; my $css = join '', <$CSS>; close $CSS; - $css =~ s/\$$_\$/$o->{$_}/g for (keys %$o); - open my $SKIN, '>', "$ROOT/static/s/$o->{_name}/style.css" or die $!; + $css =~ s/\$$_\$/$o{$_}/g for (keys %o); + open my $SKIN, '>', "$ROOT/static/s/$name/style.css" or die $!; print $SKIN $CSS::Minifier::XS::VERSION ? CSS::Minifier::XS::minify($css) : $css; close $SKIN; } +if(@ARGV) { + writeskin($_) for (@ARGV); +} else { + writeskin($_) for (SkinFile->new("$ROOT/static/s")->list); +} + + diff --git a/util/sql/all.sql b/util/sql/all.sql index 04e1dbf9..9a9eace0 100644 --- a/util/sql/all.sql +++ b/util/sql/all.sql @@ -9,7 +9,10 @@ CREATE LANGUAGE plpgsql; 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 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 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'); @@ -27,6 +30,10 @@ CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'sid -- triggers +CREATE TRIGGER hidlock_update BEFORE UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); +CREATE TRIGGER hidlock_update BEFORE UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); +CREATE TRIGGER hidlock_update BEFORE UPDATE ON releases FOR EACH ROW 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(); @@ -56,14 +63,25 @@ CREATE TRIGGER insert_notify AFTER INSERT ON tags CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW 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 EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_dbdel AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_dbdel AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_listdel AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE notify_listdel(); +CREATE TRIGGER notify_listdel AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE notify_listdel(); +CREATE TRIGGER notify_dbedit AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE notify_dbedit(); +CREATE TRIGGER notify_dbedit AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE notify_dbedit(); +CREATE TRIGGER notify_dbedit AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE notify_dbedit(); +CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_announce(); + -- 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) VALUES (0, 'deleted', 'del@vndb.org', 0); -INSERT INTO users (username, mail, rank) VALUES ('multi', 'multi@vndb.org', 0); +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 stats_cache (section, count) VALUES ('users', 1), diff --git a/util/sql/func.sql b/util/sql/func.sql index ed054207..f9f0e490 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -1,4 +1,16 @@ +-- A small note on the function naming scheme: +-- edit_* -> revision insertion abstraction functions +-- *_notify -> functions issuing a PgSQL NOTIFY statement +-- notify_* -> functions creating entries in the notifications table +-- update_* -> functions to update a cache +-- *_update ^ (I should probably rename these to +-- *_calc ^ the update_* scheme for consistency) +-- I like to keep the nouns in functions singular, in contrast to the table +-- naming scheme where nouns are always plural. But I'm not very consistent +-- with that, either. + + -- update_vncache(id) - updates the c_* columns in the vn table CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$ @@ -14,7 +26,7 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$ AND rr1.released <> 0 GROUP BY rv1.vid ), 0), - c_languages = COALESCE(ARRAY_TO_STRING(ARRAY( + c_languages = ARRAY( SELECT rl2.lang FROM releases_rev rr2 JOIN releases_lang rl2 ON rl2.rid = rr2.id @@ -26,7 +38,7 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$ AND r2.hidden = FALSE GROUP BY rl2.lang ORDER BY rl2.lang - ), '/'), ''), + ), c_platforms = COALESCE(ARRAY_TO_STRING(ARRAY( SELECT rp3.platform FROM releases_platforms rp3 @@ -38,8 +50,7 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$ AND rr3.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer AND r3.hidden = FALSE GROUP BY rp3.platform - ORDER BY rp3.platform - ), '/'), '') + ORDER BY rp3.platform), '/'), '') WHERE id = $1; $$ LANGUAGE sql; @@ -119,14 +130,26 @@ $$ LANGUAGE plpgsql; -- create temporary table for generic revision info CREATE OR REPLACE FUNCTION edit_revtable(t dbentry_type, i integer) RETURNS void AS $$ BEGIN - CREATE TEMPORARY TABLE edit_revision ( - type dbentry_type NOT NULL, - iid integer, - requester integer, - ip inet, - comments text + BEGIN + CREATE TEMPORARY TABLE edit_revision ( + type dbentry_type NOT NULL, + iid integer, + requester integer, + ip inet, + comments text, + ihid boolean, + ilock boolean + ); + 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 pid FROM producers_rev WHERE id = i), + COALESCE((SELECT ihid FROM changes WHERE id = i), FALSE), + COALESCE((SELECT ilock FROM changes WHERE id = i), FALSE) ); - INSERT INTO edit_revision (type, iid) VALUES (t, i); END; $$ LANGUAGE plpgsql; @@ -145,18 +168,16 @@ BEGIN r.rev := 1; ELSE SELECT c.rev+1 INTO r.rev FROM changes c - LEFT JOIN vn_rev vr ON c.id = vr.id - LEFT JOIN releases_rev rr ON c.id = rr.id - LEFT JOIN producers_rev pr ON c.id = pr.id - WHERE (t = 'v' AND vr.vid = i) - OR (t = 'r' AND rr.rid = i) - OR (t = 'p' AND pr.pid = i) + 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 + ) 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, rev) - SELECT t, requester, ip, comments, r.rev + 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; -- insert DB item @@ -178,23 +199,26 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION edit_vn_init(cid integer) RETURNS void AS $$ BEGIN -- create tables, based on existing tables (so that the column types are always synchronised) - 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; + 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; + EXCEPTION WHEN duplicate_table THEN + TRUNCATE edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots; + END; + PERFORM edit_revtable('v', cid); -- new VN, load defaults IF cid IS NULL THEN - PERFORM edit_revtable('v', NULL); INSERT INTO edit_vn DEFAULT VALUES; -- otherwise, load revision ELSE - PERFORM edit_revtable('v', (SELECT vid FROM vn_rev WHERE id = cid)); INSERT INTO edit_vn SELECT title, alias, img_nsfw, length, "desc", l_wp, l_vnn, 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 FROM vn_relations WHERE vid1 = cid; @@ -218,7 +242,6 @@ BEGIN INSERT INTO vn_relations SELECT r.cid, vid, relation FROM edit_vn_relations; INSERT INTO vn_screenshots SELECT r.cid, scr, nsfw, rid FROM edit_vn_screenshots; UPDATE vn SET latest = r.cid WHERE id = r.iid; - DROP TABLE edit_revision, edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots; RETURN r; END; $$ LANGUAGE plpgsql; @@ -228,26 +251,29 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION edit_release_init(cid integer) RETURNS void AS $$ BEGIN -- temp. tables - 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; + 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 - PERFORM edit_revtable('r', NULL); INSERT INTO edit_release DEFAULT VALUES; -- load revision ELSE - PERFORM edit_revtable('r', (SELECT rid FROM releases_rev WHERE id = cid)); 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; @@ -277,7 +303,6 @@ BEGIN 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; - DROP TABLE edit_revision, edit_release, edit_release_lang, edit_release_media, edit_release_platforms, edit_release_producers, edit_release_vn; RETURN r; END; $$ LANGUAGE plpgsql; @@ -286,19 +311,22 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION edit_producer_init(cid integer) RETURNS void AS $$ BEGIN - CREATE TEMPORARY TABLE edit_producer (LIKE producers_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_producer DROP COLUMN id; - ALTER TABLE edit_producer DROP COLUMN pid; - CREATE TEMPORARY TABLE edit_producer_relations (LIKE producers_relations INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_producer_relations DROP COLUMN pid1; - ALTER TABLE edit_producer_relations RENAME COLUMN pid2 TO pid; + 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 - PERFORM edit_revtable('p', NULL); INSERT INTO edit_producer DEFAULT VALUES; -- load revision ELSE - PERFORM edit_revtable('p', (SELECT pid FROM producers_rev WHERE id = cid)); INSERT INTO edit_producer SELECT type, name, original, website, lang, "desc", alias, l_wp FROM producers_rev WHERE id = cid; INSERT INTO edit_producer_relations SELECT pid2, relation FROM producers_relations WHERE pid1 = cid; END IF; @@ -318,7 +346,6 @@ BEGIN INSERT INTO producers_rev SELECT r.cid, r.iid, type, name, original, website, lang, "desc", alias, l_wp FROM edit_producer; INSERT INTO producers_relations SELECT r.cid, pid, relation FROM edit_producer_relations; UPDATE producers SET latest = r.cid WHERE id = r.iid; - DROP TABLE edit_revision, edit_producer, edit_producer_relations; RETURN r; END; $$ LANGUAGE plpgsql; @@ -540,3 +567,174 @@ BEGIN END; $$ LANGUAGE plpgsql; + + +-- update (vn|release|producer).(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 + IF OLD.latest IS DISTINCT FROM NEW.latest THEN + SELECT INTO r ihid, ilock FROM changes WHERE id = NEW.latest; + NEW.hidden := r.ihid; + NEW.locked := r.ilock; + END IF; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + + + + + +---------------------------------------------------------- +-- notification functions -- +-- (these are, in fact, also triggers) -- +---------------------------------------------------------- + + +-- called on INSERT INTO threads_posts +CREATE OR REPLACE FUNCTION notify_pm() RETURNS trigger AS $$ +BEGIN + INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) + SELECT 'pm', 't', tb.iid, t.id, NEW.num, t.title, NEw.uid + FROM threads t + JOIN threads_boards tb ON tb.tid = t.id + WHERE t.id = NEW.tid + AND tb.type = 'u' + AND tb.iid <> NEW.uid -- don't notify when posting in your own board + AND NOT EXISTS( -- don't notify when you haven't read an earlier post in the thread yet + SELECT 1 + FROM notifications n + WHERE n.uid = tb.iid + AND n.ntype = 'pm' + AND n.iid = t.id + AND n.read IS NULL + ); + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + +-- called on UPDATE vn / producers / releases +CREATE OR REPLACE FUNCTION notify_dbdel() RETURNS trigger AS $$ +BEGIN + -- item is deleted? + IF NOT OLD.hidden AND NEW.hidden THEN + 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' ELSE 'p' END)::notification_ltype, + c.requester, NEW.id, c2.rev, x.title, c2.requester + -- look for changes of the deleted entry + -- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs + FROM changes c + 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 + ) x(id, title) ON c.id = x.id + -- join info about the deletion itself + JOIN changes c2 ON c2.id = NEW.latest + WHERE c.requester <> 1 -- exclude Multi + -- exclude the user who deleted the entry + AND c.requester <> c2.requester; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + +-- called on UPDATE vn / releases +CREATE OR REPLACE FUNCTION notify_listdel() RETURNS trigger AS $$ +BEGIN + -- item is deleted? + IF NOT OLD.hidden AND NEW.hidden THEN + 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 + -- 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 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 + -- 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 + ) u + -- fetch info about this edit + JOIN changes c ON c.id = NEW.latest + 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; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + +-- called on UPDATE vn / producers / releases +-- this trigger is very similar to notify_dbdel() +CREATE OR REPLACE FUNCTION notify_dbedit() RETURNS trigger AS $$ +BEGIN + -- item is edited but not deleted? (deleted items are handled by the dbdel notify) + IF OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden THEN + 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' ELSE 'p' END)::notification_ltype, + c.requester, NEW.id, c2.rev, x.title, c2.requester + -- look for changes of the edited entry + FROM changes c + 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 + ) 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; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + +-- called on INSERT INTO threads_posts +CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$ +BEGIN + -- new thread? + IF NEW.num = 1 THEN + INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) + SELECT 'announce', 't', u.id, 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 + WHERE t.id = NEW.tid + AND tb.type = 'an' -- announcement board + AND NOT t.hidden; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + diff --git a/util/sql/schema.sql b/util/sql/schema.sql index d5286a94..a9da0cbb 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -20,7 +20,23 @@ CREATE TABLE changes ( added timestamptz NOT NULL DEFAULT NOW(), requester integer NOT NULL DEFAULT 0, ip inet NOT NULL DEFAULT '0.0.0.0', - comments text NOT NULL DEFAULT '' + comments text NOT NULL DEFAULT '', + ihid boolean NOT NULL DEFAULT FALSE, + ilock boolean NOT NULL DEFAULT FALSE +); + +-- notifications +CREATE TABLE notifications ( + id serial PRIMARY KEY NOT NULL, + uid integer NOT NULL, + date timestamptz NOT NULL DEFAULT NOW(), + read timestamptz, + ntype notification_ntype NOT NULL, + ltype notification_ltype NOT NULL, + iid integer NOT NULL, + subid integer, + c_title text NOT NULL, + c_byuser integer ); -- producers @@ -48,7 +64,7 @@ CREATE TABLE producers_rev ( name varchar(200) NOT NULL DEFAULT '', original varchar(200) NOT NULL DEFAULT '', website varchar(250) NOT NULL DEFAULT '', - lang varchar NOT NULL DEFAULT 'ja', + lang language NOT NULL DEFAULT 'ja', "desc" text NOT NULL DEFAULT '', alias varchar(500) NOT NULL DEFAULT '', l_wp varchar(150) @@ -72,7 +88,7 @@ CREATE TABLE releases ( -- releases_lang CREATE TABLE releases_lang ( rid integer NOT NULL, - lang varchar NOT NULL, + lang language NOT NULL, PRIMARY KEY(rid, lang) ); @@ -158,7 +174,8 @@ CREATE TABLE screenshots ( CREATE TABLE sessions ( uid integer NOT NULL, token bytea NOT NULL, - expiration timestamptz NOT NULL DEFAULT (now() + '1 year'::interval), + added timestamptz NOT NULL DEFAULT NOW(), + lastused timestamptz NOT NULL DEFAULT NOW(), PRIMARY KEY (uid, token) ); @@ -238,7 +255,6 @@ CREATE TABLE threads_boards ( tid integer NOT NULL DEFAULT 0, type character(2) NOT NULL DEFAULT 0, iid integer NOT NULL DEFAULT 0, - lastread smallint NOT NULL, PRIMARY KEY(tid, type, iid) ); @@ -259,7 +275,9 @@ CREATE TABLE users ( ip inet NOT NULL DEFAULT '0.0.0.0', c_tags integer NOT NULL DEFAULT 0, salt character(9) NOT NULL DEFAULT '', - ign_votes voolean NOT NULL DEFAULT FALSE + ign_votes boolean NOT NULL DEFAULT FALSE, + notify_dbedit boolean NOT NULL DEFAULT TRUE, + notify_announce boolean NOT NULL DEFAULT FALSE ); -- vn @@ -270,7 +288,7 @@ CREATE TABLE vn ( hidden boolean NOT NULL DEFAULT FALSE, rgraph integer, c_released integer NOT NULL DEFAULT 0, - c_languages varchar(32) NOT NULL DEFAULT '', + c_languages language[] NOT NULL DEFAULT '{}', c_platforms varchar(32) NOT NULL DEFAULT '', c_popularity real, c_rating real, @@ -339,10 +357,12 @@ CREATE TABLE wlists ( ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id); +ALTER TABLE notifications ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE notifications ADD FOREIGN KEY (c_byuser) REFERENCES users (id); ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE producers ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id); -ALTER TABLE producers_relations ADD FOREIGN KEY (pid1) REFERENCES producers_rev (id); -ALTER TABLE producers_relations ADD FOREIGN KEY (pid2) REFERENCES producers (id); +ALTER TABLE producers_relations ADD FOREIGN KEY (pid1) REFERENCES producers_rev (id); +ALTER TABLE producers_relations ADD FOREIGN KEY (pid2) REFERENCES producers (id); ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id); ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id); ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id); diff --git a/util/updates/update_2.11.sql b/util/updates/update_2.11.sql new file mode 100644 index 00000000..65571fa9 --- /dev/null +++ b/util/updates/update_2.11.sql @@ -0,0 +1,120 @@ + + +CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce'); +CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 't'); + +CREATE TABLE notifications ( + id serial PRIMARY KEY NOT NULL, + uid integer NOT NULL REFERENCES users (id), + date timestamptz NOT NULL DEFAULT NOW(), + read timestamptz, + ntype notification_ntype NOT NULL, + ltype notification_ltype NOT NULL, + iid integer NOT NULL, + subid integer, + c_title text NOT NULL, + c_byuser integer REFERENCES users (id) +); + +-- convert the "unread messages" count into notifications +INSERT INTO notifications (uid, date, ntype, ltype, iid, subid, c_title, c_byuser) + SELECT tb.iid, tp.date, 'pm', 't', t.id, tp.num, t.title, tp.uid + FROM threads_boards tb + JOIN threads t ON t.id = tb.tid + JOIN threads_posts tp ON tp.tid = t.id AND tp.num = COALESCE(tb.lastread, 1) + WHERE tb.type = 'u' AND NOT t.hidden AND (tb.lastread IS NULL OR t.count <> tb.lastread); + +-- ...and drop the now unused lastread column +ALTER TABLE threads_boards DROP COLUMN lastread; + +ALTER TABLE users ADD COLUMN notify_dbedit boolean NOT NULL DEFAULT true; +ALTER TABLE users ADD COLUMN notify_announce boolean NOT NULL DEFAULT false; +UPDATE users SET notify_dbedit = false WHERE id IN(0,1); + + +-- languages -> ENUM +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'); +ALTER TABLE producers_rev ALTER COLUMN lang DROP DEFAULT; +ALTER TABLE producers_rev ALTER COLUMN lang TYPE language USING CASE lang WHEN 'pt' THEN 'pt-pt' ELSE lang::language END; +ALTER TABLE producers_rev ALTER COLUMN lang SET DEFAULT 'ja'; +ALTER TABLE releases_lang ALTER COLUMN lang TYPE language USING CASE lang WHEN 'pt' THEN 'pt-pt' ELSE lang::language END; +-- c_languages is an now array of languages, rather than a serialized string +ALTER TABLE vn ALTER COLUMN c_languages DROP DEFAULT; +ALTER TABLE vn ALTER COLUMN c_languages TYPE language[] USING '{}'; +ALTER TABLE vn ALTER COLUMN c_languages SET DEFAULT '{}'; + + + +ALTER TABLE changes ADD COLUMN ihid boolean NOT NULL DEFAULT FALSE; +ALTER TABLE changes ADD COLUMN ilock boolean NOT NULL DEFAULT FALSE; + +\i util/sql/func.sql + +SELECT COUNT(*) FROM (SELECT update_vncache(id) FROM vn) x; + +CREATE TRIGGER hidlock_update BEFORE UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); +CREATE TRIGGER hidlock_update BEFORE UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); +CREATE TRIGGER hidlock_update BEFORE UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); + + +CREATE OR REPLACE FUNCTION tmp_edit_hidlock(t text, iid integer) RETURNS void AS $$ +BEGIN + IF t = 'v' THEN + PERFORM edit_vn_init(latest) FROM vn WHERE id = iid; + IF EXISTS(SELECT 1 FROM vn WHERE id = iid AND hidden) THEN + UPDATE edit_revision SET ihid = true, ip = '0.0.0.0', requester = 1, + comments = 'This visual novel was deleted before the update to VNDB 2.11, no reason specified.'; + ELSE + UPDATE edit_revision SET ilock = true, ip = '0.0.0.0', requester = 1, + comments = 'This visual novel was locked before the update to VNDB 2.11, no reason specified.'; + END IF; + PERFORM edit_vn_commit(); + ELSIF t = 'r' THEN + PERFORM edit_release_init(latest) FROM releases WHERE id = iid; + IF EXISTS(SELECT 1 FROM releases WHERE id = iid AND hidden) THEN + UPDATE edit_revision SET ihid = true, ip = '0.0.0.0', requester = 1, + comments = 'This release was deleted before the update to VNDB 2.11, no reason specified.'; + ELSE + UPDATE edit_revision SET ilock = true, ip = '0.0.0.0', requester = 1, + comments = 'This release was locked before the update to VNDB 2.11, no reason specified.'; + END IF; + PERFORM edit_release_commit(); + ELSE + PERFORM edit_producer_init(latest) FROM producers WHERE id = iid; + IF EXISTS(SELECT 1 FROM producers WHERE id = iid AND hidden) THEN + UPDATE edit_revision SET ihid = true, ip = '0.0.0.0', requester = 1, + comments = 'This producer was deleted before the update to VNDB 2.11, no reason specified.'; + ELSE + UPDATE edit_revision SET ilock = true, ip = '0.0.0.0', requester = 1, + comments = 'This producer was locked before the update to VNDB 2.11, no reason specified.'; + END IF; + PERFORM edit_producer_commit(); + END IF; +END; +$$ LANGUAGE plpgsql; + + SELECT 'v', COUNT(*) FROM (SELECT tmp_edit_hidlock('v', id) FROM vn WHERE (hidden OR locked)) x +UNION SELECT 'r', COUNT(*) FROM (SELECT tmp_edit_hidlock('r', id) FROM releases WHERE hidden OR locked) x +UNION SELECT 'p', COUNT(*) FROM (SELECT tmp_edit_hidlock('p', id) FROM producers WHERE hidden OR locked) x; +DROP FUNCTION tmp_edit_hidlock(text, integer); + + +-- keep track of when a session is last used +ALTER TABLE sessions ADD COLUMN lastused timestamptz NOT NULL DEFAULT NOW(); +ALTER TABLE sessions RENAME COLUMN expiration TO added; +UPDATE sessions SET added = added - '1 year'::interval; +ALTER TABLE sessions ALTER COLUMN added SET DEFAULT NOW(); + + +CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm(); +-- make sure to add these triggers AFTER performing the batch edit above +CREATE TRIGGER notify_dbdel AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_dbdel AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_dbdel AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_listdel AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE notify_listdel(); +CREATE TRIGGER notify_listdel AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE notify_listdel(); +CREATE TRIGGER notify_dbedit AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE notify_dbedit(); +CREATE TRIGGER notify_dbedit AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE notify_dbedit(); +CREATE TRIGGER notify_dbedit AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE notify_dbedit(); +CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_announce(); + diff --git a/util/vndb.pl b/util/vndb.pl index 32e1bdb4..29c2648c 100755 --- a/util/vndb.pl +++ b/util/vndb.pl @@ -18,6 +18,7 @@ use lib $ROOT.'/lib'; use YAWF ':html'; use VNDB::L10N; +use SkinFile; our(%O, %S); @@ -53,18 +54,14 @@ sub reqinit { my $self = shift; # Determine language - # if the cookie or parameter "l10n" is set, use that. - # otherwise, interpret the Accept-Language header or fall back to English + # 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->reqParam('l10n') || $self->reqCookie('l10n'); + my $conf = $self->reqCookie('l10n'); $conf = '' if !$conf || !grep $_ eq $conf, VNDB::L10N::languages; $self->{l10n} = VNDB::L10N->get_handle(); # this uses I18N::LangTags::Detect - if($self->{l10n}->language_tag() eq $conf && $self->reqCookie('l10n')) { - $self->resHeader('Set-Cookie', "l10n= ; expires=Sat, 01-Jan-2000 00:00:00 GMT; path=/; domain=$self->{cookie_domain}"); - } elsif($self->reqParam('l10n') && $conf && $conf ne ($self->reqCookie('l10n')||'') && $self->{l10n}->language_tag() ne $conf) { - $self->resHeader('Set-Cookie', "l10n=$conf; expires=Sat, 01-Jan-2030 00:00:00 GMT; path=/; domain=$self->{cookie_domain}"); - } + $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; @@ -104,21 +101,12 @@ sub readskins { my %skins; # dirname => skin name my @regen; my $lasttemplate = [stat "$ROOT/data/style.css"]->[9]; - for my $f (glob "$ROOT/static/s/*") { - next if !-e "$f/conf"; - my $n = $1 if $f =~ m{([^/]+)$}; - open my $F, '<', "$f/conf" or die $!; - while(<$F>) { - chomp; - s/\r//; - s{[\t\s]*//.*$}{}; - next if !/^name[\t\s]+(.+)$/; - $skins{$n} = $1; - last; - } - close $F; - next if !$skins{$n}; + my $skin = SkinFile->new("$ROOT/static/s"); + for my $n ($skin->list) { + $skins{$n} = [ $skin->get($n, 'name'), $skin->get($n, 'userid') ]; + next if !$skins{$n}[0]; + my $f = "$ROOT/static/s/$n"; my $css = -f "$f/style.css" && [stat "$f/style.css"]->[9] || 0; my $boxbg = -f "$f/boxbg.png" && [stat "$f/boxbg.png"]->[9] || 0; my $lastgen = $css < $boxbg ? $css : $boxbg; |