diff options
30 files changed, 1536 insertions, 1324 deletions
@@ -1,3 +1,17 @@ +2.6 - ? + - New screen resolutions: 1024x600 and 1600x1200 + - Rewritten authentication system + - New language: Vietnamese + - Complete rewrite of Multi + - Asynchronous communication with PostgreSQL + - Got rid of the shared memory + - No more $self->multiCmd in the VNDB code + - Extended IRC bot functionality + - Tag cache regenerated daily rather than hourly + - Added OpenSearch plugin + autodetection + - Converted font size units to px in the css + - Added double-post prevention + 2.5 - 2009-07-09 - Hide NSFW images in diff viewer (unless NSFW warnings are disabled) - Display related boards in recent posts tooltip op homepage diff --git a/data/global.pl b/data/global.pl index 7f3ab7a2..a4cf9fbf 100644 --- a/data/global.pl +++ b/data/global.pl @@ -20,7 +20,7 @@ our %S = (%S, site_title => 'Yet another VNDB clone', skin_default => 'angel', cookie_domain => '.vndb.org', - cookie_key => 'any-private-string-here', + global_salt => 'any-private-string-here', source_url => 'http://git.blicky.net/vndb.git/?h=master', admin_email => 'contact@vndb.org', sharedmem_key => 'VNDB', @@ -51,6 +51,7 @@ our %S = (%S, ru => q|Russian|, sv => q|Swedish|, tr => q|Turkish|, + vi => q|Vietnamese|, zh => q|Chinese|, }, producer_types => { @@ -125,15 +126,15 @@ our %S = (%S, }, 7 ], }, anime_types => [ - # VNDB AniDB - [ 'unknown', 'unknown', ], - [ 'TV', 'TV Series' ], - [ 'OVA', 'OVA' ], - [ 'Movie', 'Movie' ], - [ 'unknown', 'Other' ], - [ 'unknown', 'Web' ], - [ 'TV Special', 'TV Special' ], - [ 'unknown', 'Music Video' ], + # AniDB anime type starts counting at 1, 0 = unknown + # we start counting at 0, with NULL being unknown + 'TV Series', + 'OVA', + 'Movie', + 'Other', + 'Web', + 'TV Special', + 'Music Video', ], vn_relations => [ # Name, Reverse-- @@ -210,7 +211,9 @@ our %S = (%S, [ '640x480 (480p)', '4:3' ], [ '800x600', '4:3' ], [ '1024x768', '4:3' ], + [ '1600x1200', '4:3' ], [ '640x400', 'widescreen' ], + [ '1024x600', 'widescreen' ], [ '1024x640', 'widescreen' ], [ '1280x720 (720p)', 'widescreen' ], [ '1920x1080 (1080p)', 'widescreen' ], @@ -268,7 +271,6 @@ our %S = (%S, # Multi-specific options (Multi also uses some options in %S and %O) our %M = ( log_dir => $ROOT.'/data/log', - log_level => 3, # 3: dbg, 2: wrn, 1: err modules => { RG => {}, Image => {}, diff --git a/data/style.css b/data/style.css index dec6462b..e272ea7f 100644 --- a/data/style.css +++ b/data/style.css @@ -6,7 +6,7 @@ } body, td { - font: 8pt "Tahoma"; + font: 11px "Tahoma"; } body { $_bodybg$; @@ -47,7 +47,7 @@ img { } #header h1, #header h1 a { font-family: "Futura", "Century New Gothic", "Arial", Serif; - font-size: 19pt; + font-size: 23px; font-style: italic; border: none!important; $_maintitle$ @@ -88,7 +88,7 @@ p.locked { } b.grayedout { font-weight: normal; color: $grayedout$ } #maincontent h2 b { - font: 8pt "Tahoma"; + font: 10px "Tahoma"; font-weight: normal; } p.description { @@ -136,7 +136,7 @@ input.text, input.submit, select, textarea { background-color: $secbg$; color: $maintext$; border: 1px solid $secborder$; - font: 9pt "Tahoma"; + font: 11px "Tahoma"; margin: 1px; } optgroup option { @@ -159,7 +159,7 @@ fieldset.submit input { width: 200px; } fieldset.submit h2 { - font-size: 9pt!important; + font-size: 11px!important; } fieldset.submit textarea { margin: 0 20px 5px 20px; @@ -203,11 +203,11 @@ td.field label { #maincontent h1, #maincontent h2 { font-family: "Futura", "Century New Gothic", "Arial", Serif; font-weight: normal; - font-size: 11pt; + font-size: 13px; } #maincontent h1 { color: $boxtitle$; - font-size: 14pt; + font-size: 16px; margin: -5px 0 15px 0; } #maincontent h2.alttitle { @@ -232,7 +232,7 @@ td.field label { } #maincontent h2 { font-weight: bold; - font-size: 11pt; + font-size: 13px; margin: 10px 0 0 5px; } p.center { @@ -268,7 +268,7 @@ b.future { padding: 1px 3px; } #menulist h2, #menulist h2 a { - font-size: 8pt; + font-size: 11px; color: $maintext$; } #menulist dt { @@ -374,11 +374,11 @@ b.future { } #maincontent .mainbox.threelayout h1 { margin: -3px 0 1px 0; - font-size: 12pt; + font-size: 14px; font-weight: bold; } #maincontent .mainbox.threelayout h2 { - font-size: 10pt; + font-size: 12px; margin-top: 3px; } #maincontent .mainbox.threelayout a.right { @@ -514,7 +514,7 @@ div.thread i.deleted { } div.thread i.lastmod { float: right; - font-size: 7pt; + font-size: 10px; color: $grayedout$; margin: 0 -10px -5px 0; } @@ -547,7 +547,7 @@ div.discussions td.tc4 { width: 170px; } font-weight: bold; font-style: italic; color: $grayedout$; - font-size: 13pt; + font-size: 17px; margin: 20px 0 -20px 0; } @@ -570,7 +570,7 @@ div.vnimg i { display: block; width: 100%; text-align: center; - font-size: 7pt; + font-size: 10px; } div.vnimg p { text-align: center; @@ -615,7 +615,7 @@ div.vndetails td.relations dd { margin-left: 15px; } div.vndetails td.anime b { - font-size: 6pt; + font-size: 8px; font-weight: normal; padding-right: 4px; } @@ -682,7 +682,6 @@ a.addnew { margin: 2px; text-align: center; } -#screenshots td.scr div i { font-size: 7pt; } #screenshots td.scr img { border: 3px solid transparent; } #screenshots td.scr div.nsfw img { border: 3px solid $statnok$; } #screenshots td.scr a:hover img { border: 3px solid $border$; } @@ -723,47 +722,6 @@ a.addnew { /***** VN edit *****/ -#jt_box_categories ul { - list-style-type: none; - padding: 0; - margin: 0; -} -#jt_box_categories li { - display: block; - width: 170px; - float: left; - clear: none; - font-weight: bold; -} -#jt_box_categories li li { - display: list-item; - width: auto; - float: none; - clear: left; - font-weight: normal; - padding: 0 0 0 20px; - margin: 0; - cursor: pointer; - list-style-type: none; - padding: 0; -} -#maincontent #jt_box_categories li li a { - color: $grayedout$; -} -a.help { - vertical-align: super; - font-size: 6pt; - text-decoration: none; - font-weight: bold; - padding-left: 1px; -} -#maincontent #jt_box_categories li li a { - text-decoration: none; - display: block; - width: 160px; - border: none -} - #jt_box_relations table { margin-bottom: 10px; } #jt_box_relations h2 { margin: 0 0 3px 0px; } #jt_box_relations td { padding: 1px 2px; vertical-align: middle; } @@ -1085,7 +1043,7 @@ div.warning li, div.notice li { margin-left: 20px; } #maincontent div.warning h2, #maincontent div.notice h2 { - font-size: 9pt; + font-size: 11px; font-weight: bold; margin: 0; } @@ -1155,7 +1113,7 @@ div#iv_view { padding: 5px; text-align: center; } -#iv_view a { border: 0; font-weight: bold; font-size: 10pt; } +#iv_view a { border: 0; font-weight: bold; font-size: 12px } #iv_view img { cursor: pointer } #ivclose { float: right; padding-left: 10px } #ivnext { padding-left: 5px; } @@ -1226,6 +1184,8 @@ a .icons { cursor: pointer } .icons.msx { background-position: -32px -56px; } .icons.nes { background-position: -32px -70px; } +.icons.vi { background-position: -34px -88px; } + .icons.cs { background-position: -48px 0px; } .icons.da { background-position: -48px -11px; } .icons.de { background-position: -48px -22px; } diff --git a/lib/Multi/Anime.pm b/lib/Multi/Anime.pm index 1215a4a1..03134925 100644 --- a/lib/Multi/Anime.pm +++ b/lib/Multi/Anime.pm @@ -8,7 +8,6 @@ package Multi::Anime; use strict; use warnings; use POE 'Wheel::UDP', 'Filter::Stream'; -use Tie::ShareLite ':lock'; use Socket 'inet_ntoa'; use Time::HiRes 'time'; @@ -26,48 +25,45 @@ sub BANNED () { 555 } sub ANIDB_OUT_OF_SERVICE () { 601 } sub SERVER_BUSY () { 602 } -my @expected_codes = ( TIMEOUT, LOGIN_ACCEPTED, LOGIN_ACCEPTED_NEW_VER, ANIME, NO_SUCH_ANIME, NOT_LOGGED_IN, LOGIN_FIRST, INVALID_SESSION ); +my @handled_codes = ( + TIMEOUT, LOGIN_ACCEPTED, LOGIN_ACCEPTED_NEW_VER, ANIME, NO_SUCH_ANIME, NOT_LOGGED_IN, + LOGIN_FIRST,CLIENT_BANNED, INVALID_SESSION, BANNED, ANIDB_OUT_OF_SERVICE, SERVER_BUSY +); -sub spawn { - # The 'anime' command doesn't actually do anything, it just - # adds IDs to process to the internal queue, which is seperate - # from the global processing queue. - # This module -only- fetches anime information in daemon mode! - # Calling the anime command with an ID as argument will force - # the information to be refreshed. This is not recommended, - # just use 'anime' for normal usage. +sub spawn { my $p = shift; + my %o = @_; + + die "No AniDB user/pass configured!" if !$o{user} || !$o{pass}; + + my $addr = delete($o{PeerAddr}) || 'api.anidb.info'; + $addr = gethostbyname($addr) or die "Couldn't resolve domain"; + $addr = inet_ntoa($addr); + POE::Session->create( package_states => [ - $p => [qw| _start shutdown cmd_anime nextcmd receivepacket updateanime |], + $p => [qw| _start shutdown check_anime fetch_anime nextcmd receivepacket |], ], heap => { - # POE::Wheels::UDP options + # POE::Wheels::UDP options LocalAddr => '0.0.0.0', LocalPort => 9000, - PeerAddr => do { - if(!$Multi::DAEMONIZE) { - my $a = gethostbyname('api.anidb.info'); - die "ERROR: Couldn't resolve domain" if !defined $a; - inet_ntoa($a); - } else { - 0; - } - }, + PeerAddr => $addr, PeerPort => 9000, - # AniDB UDP API options + # AniDB UDP API options client => 'multi', clientver => 1, - # Misc settings + # Misc settings msgdelay => 10, timeout => 30, timeoutdelay => 0.4, # $delay = $msgdelay ^ (1 + $tm*$timeoutdelay) maxtimeoutdelay => 2*3600, # two hours - cachetime => 30*24*3600, # one month + check_delay => 3600, # one hour + cachetime => '1 month', - @_, + %o, w => undef, s => '', # session key, '' = not logged in tm => 0, # number of repeated timeouts @@ -81,79 +77,55 @@ sub spawn { sub _start { $_[KERNEL]->alias_set('anime'); - $_[KERNEL]->call(core => register => qr/^anime(?: ([0-9]+))?$/, 'cmd_anime'); - - # check for anime twice a day - $_[KERNEL]->post(core => addcron => '0 0,12 * * *', 'anime'); - $_[KERNEL]->sig('shutdown' => 'shutdown'); - - if(!$Multi::DAEMONIZE) { - # init the UDP 'connection' - $_[HEAP]{w} = POE::Wheel::UDP->new( - (map { $_ => $_[HEAP]{$_} } qw| LocalAddr LocalPort PeerAddr PeerPort |), - InputEvent => 'receivepacket', - Filter => POE::Filter::Stream->new(), - ); + $_[KERNEL]->sig(shutdown => 'shutdown'); - # start executing commands - $_[KERNEL]->delay(nextcmd => 0); #$_[HEAP]{msgdelay}); - } + # listen for 'anime' notifies + $_[KERNEL]->post(pg => listen => anime => 'check_anime'); + + # init the UDP 'connection' + $_[HEAP]{w} = POE::Wheel::UDP->new( + (map { $_ => $_[HEAP]{$_} } qw| LocalAddr LocalPort PeerAddr PeerPort |), + InputEvent => 'receivepacket', + Filter => POE::Filter::Stream->new(), + ); + + # look for something to do + $_[KERNEL]->yield('check_anime'); } sub shutdown { undef $_[HEAP]{w}; + $_[KERNEL]->post(pg => unlisten => 'anime'); + $_[KERNEL]->delay('check_anime'); $_[KERNEL]->delay('nextcmd'); $_[KERNEL]->delay('receivepacket'); + $_[KERNEL]->alias_remove('anime'); } -sub cmd_anime { # cmd, arg - my @push; - if(!$_[ARG1]) { - # only animes we have never fetched, or haven't been updated for a month - my $q = $Multi::SQL->prepare(q| - SELECT id - FROM anime - WHERE lastfetch < ? - AND lastfetch <> -1|); - $q->execute(int(time-$_[HEAP]{cachetime})); - push @push, map $_->[0], @{$q->fetchall_arrayref([])}; - $_[KERNEL]->call(core => log => 2, 'All anime info is up-to-date!') if !@push; - } else { - push @push, $_[ARG1]; - } +sub check_anime { + return if $_[HEAP]{aid}; + $_[KERNEL]->delay('check_anime'); + $_[KERNEL]->post(pg => query => 'SELECT id FROM anime WHERE lastfetch IS NULL OR lastfetch < NOW() - ?::interval LIMIT 1', + [ $_[HEAP]{cachetime} ], 'fetch_anime'); +} - if(@push) { - my $s = tie my %s, 'Tie::ShareLite', -key => $VNDB::S{sharedmem_key}, -create => 'yes', -destroy => 'no', -mode => 0666; - $s->lock(LOCK_EX); - my @q = $s{anime} ? @{$s{anime}} : (); - push @q, grep { - my $ia = $_; - !(scalar grep $ia == $_, @q) - } @push; - $s{anime} = \@q; - $s->unlock(); - } - $_[KERNEL]->post(core => finish => $_[ARG0]); +sub fetch_anime { # num, res + # nothing to do, check again later + return $_[KERNEL]->delay('check_anime', $_[HEAP]{check_delay}) if $_[ARG0] == 0; + + # otherwise, fetch info (if we aren't doing so already) + return if $_[HEAP]{aid}; + $_[HEAP]{aid} = $_[ARG1][0]{id}; + $_[KERNEL]->yield('nextcmd'); } sub nextcmd { - return if $_[HEAP]{lm}; - - my $s = tie my %s, 'Tie::ShareLite', -key => $VNDB::S{sharedmem_key}, -create => 'yes', -destroy => 'no', -mode => 0666; - my @q = $s{anime} ? @{$s{anime}} : (); - undef $s; - - if(!@q) { # nothing to do... - $_[KERNEL]->delay(nextcmd => $_[HEAP]{msgdelay}); - return; - } my %cmd; - - # not logged in, get a session + # not logged in, get a session if(!$_[HEAP]{s}) { %cmd = ( command => 'AUTH', @@ -164,21 +136,19 @@ sub nextcmd { clientver => $_[HEAP]{clientver}, enc => 'UTF-8', ); - $_[KERNEL]->call(core => log => 3, 'Authenticating with AniDB...'); + $_[KERNEL]->call(core => log => 'Authenticating with AniDB...'); } - - # logged in, get anime + # logged in, get anime else { - $_[HEAP]{aid} = $q[0]; %cmd = ( command => 'ANIME', - aid => $q[0], + aid => $_[HEAP]{aid}, acode => 3973121, # aid, ANN id, NFO id, year, type, romaji, kanji ); - $_[KERNEL]->call(core => log => 3, 'Fetching info for a%d', $q[0]); + $_[KERNEL]->call(core => log => 'Fetching info for a%d', $_[HEAP]{aid}); } - # send command + # send command my $cmd = delete $cmd{command}; $cmd{tag} = ++$_[HEAP]{tag}; $cmd{s} = $_[HEAP]{s} if $_[HEAP]{s}; @@ -189,111 +159,88 @@ sub nextcmd { } keys %cmd); $_[HEAP]{w}->put({ payload => [ $cmd ]}); - #$_[KERNEL]->call(core => log => 3, '> %s', $cmd); $_[KERNEL]->delay(receivepacket => $_[HEAP]{timeout}, { payload => [ $_[HEAP]{tag}.' 100 TIMEOUT' ] }); $_[HEAP]{lm} = time; } sub receivepacket { # input, wheelid - $_[KERNEL]->delay('receivepacket'); # disable the timeout + # parse message my @r = split /\n/, $_[ARG0]{payload}[0]; - my $delay = $_[HEAP]{msgdelay}; - my($tag, $code, $msg) = ($1, $2, $3) if $r[0] =~ /^([0-9]+) ([0-9]+) (.+)$/; - if(!grep $_ == $code, @expected_codes) { - $_[KERNEL]->call(core => log => 1, "Received an unexpected reply after %.2fs!\n < %s", - time-$_[HEAP]{lm}, join("\n < ", @r)); - } else { - $_[KERNEL]->call(core => log => 3, 'Received from AniDB after %.2fs: %d %s', - time-$_[HEAP]{lm}, $code, $msg); - } + # log + $_[KERNEL]->call(core => log => 'Received from AniDB after %.2fs: %d %s', + time-$_[HEAP]{lm}, $code, $msg); - # just handle anime data, even if the tag is not correct - if($code == ANIME) { - $_[KERNEL]->yield(updateanime => $_[HEAP]{aid}, $r[1]); - } + # tag incorrect, ignore message + return $_[KERNEL]->call(core => log => 'Ignoring incorrect tag') + if $tag != $_[HEAP]{tag}; - # tag incorrect, ignore message - if($tag != $_[HEAP]{tag}) { - $_[KERNEL]->call(core => log => 3, 'Ignoring incorrect tag') if $code != ANIME; - return; - } + # unhandled code, ignore as well + return $_[KERNEL]->call(core => log => 'Ignoring unhandled code') + if !grep $_ == $code, @handled_codes; + + # at this point, we have a message we can handle, so disable the timeout + $_[KERNEL]->delay('receivepacket'); + $_[HEAP]{lm} = 0; - # try again later + # received a timeout of some sorts, try again later if($code == TIMEOUT || $code == CLIENT_BANNED || $code == BANNED || $code == ANIDB_OUT_OF_SERVICE || $code == SERVER_BUSY) { $_[HEAP]{tm}++; - $delay = $_[HEAP]{msgdelay}**(1 + $_[HEAP]{tm}*$_[HEAP]{timeoutdelay}); + my $delay = $_[HEAP]{msgdelay}**(1 + $_[HEAP]{tm}*$_[HEAP]{timeoutdelay}); $delay = $_[HEAP]{maxtimeoutdelay} if $delay > $_[HEAP]{maxtimeoutdelay}; - $_[KERNEL]->call(core => log => 1, 'Delaying %.0fs.', $delay); + $_[KERNEL]->call(core => log => 'Delaying %.0fs.', $delay); + return $_[KERNEL]->delay(nextcmd => $_[HEAP]{msgdelay}); } - # oops, wrong id - if($code == NO_SUCH_ANIME) { - $_[KERNEL]->yield(updateanime => $_[HEAP]{aid}, 'notfound'); - } + # message wasn't a timeout, reset timeout counter + $_[HEAP]{tm} = 0; - # ok, we have a session now - if($code == LOGIN_ACCEPTED || $code == LOGIN_ACCEPTED_NEW_VER) { - $_[HEAP]{s} = $1 if $msg =~ /^\s*([a-zA-Z0-9]{4,8}) /; - } - - # oops, we should've logged in, get a new session + # our session isn't valid, discard it and call nextcmd to get a new one if($code == NOT_LOGGED_IN || $code == LOGIN_FIRST || $code == INVALID_SESSION) { $_[HEAP]{s} = ''; + return $_[KERNEL]->delay(nextcmd => $_[HEAP]{msgdelay}); } - $_[HEAP]{lm} = $_[HEAP]{aid} = 0; - $_[HEAP]{tm} = 0 if $delay == $_[HEAP]{msgdelay}; - $_[KERNEL]->delay(nextcmd => $delay); -} - - -sub updateanime { # aid, data|'notfound' - # aid, ANN id, NFO id, year, type, romaji, kanji, lastfetch - my @col = $_[ARG1] eq 'notfound' - ? ($_[ARG0], 0, 0, 0, 0, '', '', -1) - : (split(/\|/, $_[ARG1], 7), int time); + # we received a session ID, call nextcmd again to fetch anime info + if($code == LOGIN_ACCEPTED || $code == LOGIN_ACCEPTED_NEW_VER) { + $_[HEAP]{s} = $1 if $msg =~ /^\s*([a-zA-Z0-9]{4,8}) /; + return $_[KERNEL]->delay(nextcmd => $_[HEAP]{msgdelay}); + } - if($col[7] > 0) { + # we now know something about the anime we requested, update DB + if($code == NO_SUCH_ANIME) { + $_[KERNEL]->call(core => log => 'ERROR: No anime found with id = %d', $_[HEAP]{aid}); + $_[KERNEL]->post(pg => do => 'UPDATE anime SET lastfetch = NOW() WHERE id = ?', [ $_[HEAP]{aid} ]); + } else { + # aid, ANN id, NFO id, year, type, romaji, kanji + my @col = split(/\|/, $r[1], 7); for (@col) { $_ =~ s/<br \/>/\n/g; $_ =~ s/`/'/g; } + $col[1] = undef if !$col[1]; + $col[2] = undef if !$col[2] || $col[2] =~ /^0,/; $col[3] = $1 if $col[3] =~ /^([0-9]+)/; # remove multi-year stuff - for(0..$#{$VNDB::S{anime_types}}) { - $col[4] = $_ if lc($VNDB::S{anime_types}[$_][1]) eq lc($col[4]); - } - $col[4] = 0 if $col[4] !~ /^[0-9]+$/; - $col[2] = '' if $col[2] =~ /^0,/; + $col[3] = undef if !$col[3]; + $col[4] = (grep lc($VNDB::S{anime_types}[$_]) eq lc($col[4]), 0..$#{$VNDB::S{anime_types}})[0]; + $col[5] = undef if !$col[5]; + $col[6] = undef if !$col[6]; + $_[KERNEL]->post(pg => do => 'UPDATE anime + SET id = ?, ann_id = ?, nfo_id = ?, year = ?, type = ?, + title_romaji = ?,title_kanji = ?, lastfetch = NOW() + WHERE id = ?', + [ @col, $_[HEAP]{aid} ] + ); + $_[KERNEL]->call(core => log => 'Updated anime info for a%d', $_[HEAP]{aid}); + $_[KERNEL]->call(core => log => 'ERROR: a%d doesn\'t have a title or year!', $_[HEAP]{aid}) + if !$col[3] || !$col[5]; } - # try to UPDATE first - my $r = $Multi::SQL->do(q| - UPDATE anime - SET id = ?, ann_id = ?, nfo_id = ?, year = ?, type = ?, - title_romaji = ?, title_kanji = ?, lastfetch = ? - WHERE id = ?|, - undef, @col, $col[0]); - - # fall back to INSERT when nothing was updated - $Multi::SQL->do(q| - INSERT INTO anime - (id, ann_id, nfo_id, year, type, title_romaji, title_kanji, lastfetch) - VALUES (?, ?, ?, ?, ?, ?, ?, ?)|, - undef, @col) if $r < 1; - - # remove from queue - my $s = tie my %s, 'Tie::ShareLite', -key => $VNDB::S{sharedmem_key}, -create => 'yes', -destroy => 'no', -mode => 0666; - $s->lock(LOCK_EX); - my @q = grep $_ != $_[ARG0], ($s{anime} ? @{$s{anime}} : ()); - $s{anime} = \@q; - $s->unlock(); - - $col[7] > 0 - ? $_[KERNEL]->post(core => log => 2, 'Updated anime info for a%d', $col[0]) - : $_[KERNEL]->post(core => log => 1, 'Anime a%d not found!', $col[0]); + # this anime is handled, check for more + $_[HEAP]{aid} = 0; + $_[KERNEL]->delay(check_anime => $_[HEAP]{msgdelay}); } diff --git a/lib/Multi/Core.pm b/lib/Multi/Core.pm index 7b2ae24b..c40be430 100644 --- a/lib/Multi/Core.pm +++ b/lib/Multi/Core.pm @@ -7,134 +7,88 @@ package Multi::Core; use strict; use warnings; -use POE 'Component::Cron'; -use Tie::ShareLite ':lock'; -use Time::HiRes 'time', 'gettimeofday', 'tv_interval'; # overload time() -use DateTime::Event::Cron; # bug in PoCo::Cron (rt #35422, fixed in 0.019) +use POE; +use POE::Component::Pg; +use DBI; -sub spawn { +sub run { my $p = shift; + + # spawn our SQL handling session + my @db = @{$VNDB::O{db_login}}; + my(@dsn) = DBI->parse_dsn($db[0]); + $dsn[2] = ($dsn[2]?$dsn[2].',':'').'pg_enable_utf8=>1'; + $db[0] = "$dsn[0]:$dsn[1]($dsn[2]):$dsn[4]"; + POE::Component::Pg->spawn(alias => 'pg', dsn => $db[0], user => $db[1], password => $db[2]); + + # spawn the core session (which handles logging & external signals) POE::Session->create( package_states => [ - $p => [qw| _start register addcron heartbeat queue prepare execute finish log cmd_exit |], + $p => [qw| _start log pg_error sig_shutdown shutdown |], ], - heap => { cron => [], cmds => [], running => 0, starttime => 0 }, ); -} - - -sub _start { - $_[KERNEL]->alias_set('core'); - $_[KERNEL]->call(core => register => qr/^(exit|reload)$/, 'cmd_exit'); - $_[KERNEL]->yield(queue => $_) for (grep !/^-/, @ARGV); - $_[KERNEL]->yield(heartbeat => time) if $Multi::DAEMONIZE != 1; - $_[KERNEL]->yield('prepare'); -} - - -sub register { # regex, state - push @{$_[HEAP]{cmds}}, [ $_[ARG0], $_[SENDER], $_[ARG1] ]; - (my $p = $_[SENDER][2]{$_[CALLER_STATE]}[0]) =~ s/^Multi:://; # NOT PORTABLE - $_[KERNEL]->call(core => log => 3, "Command '%s' handled by %s::%s", $_[ARG0], $p, $_[ARG1]); -} - - -sub addcron { # cronline, cmd - return if $Multi::DAEMONIZE; # no cronjobs when we aren't a daemon! - push @{$_[HEAP]{cron}}, POE::Component::Cron->from_cron($_[ARG0], $_[SESSION], queue => $_[ARG1]); - $_[KERNEL]->call(core => log => 3, "Added cron: %s %s", $_[ARG0], $_[ARG1]); -} - - -sub heartbeat { # last beat - $_[KERNEL]->yield('prepare'); - $_[KERNEL]->call(core => log => 1, 'Heartbeat took %.2fs, possible block', time-$_[ARG0]) - if time > $_[ARG0]+3; - $_[KERNEL]->delay(heartbeat => 1, time) if $Multi::DAEMONIZE == 0; -} - -sub queue { # cmd - my $s = tie my %s, 'Tie::ShareLite', -key => $VNDB::S{sharedmem_key}, -create => 'yes', -destroy => 'no', -mode => 0666; - $s->lock(LOCK_EX); - my @q = ( ($s{queue} ? @{$s{queue}} : ()), $_[ARG0] ); - $s{queue} = \@q; - $s->unlock(); + # log warnings + $SIG{__WARN__} = sub {(local$_=shift)=~s/\r?\n//;$poe_kernel->call(core=>log=>'__WARN__: '.$_)}; - $_[KERNEL]->call(core => log => 3, "Queuing '%s'.", $_[ARG0]); - $_[KERNEL]->yield('prepare'); + $poe_kernel->run(); } -sub prepare { # determines whether to execute a new cmd - return if $Multi::STOP || $_[HEAP]{running}; - - my $s = tie my %s, 'Tie::ShareLite', -key => $VNDB::S{sharedmem_key}, -create => 'yes', -destroy => 'no', -mode => 0666; - $s->lock(LOCK_SH); - if($s{queue} && @{$s{queue}}) { - $_[KERNEL]->yield(execute => $s{queue}[0]); - $_[HEAP]{running} = 1; +sub _start { + $_[KERNEL]->alias_set('core'); + $_[KERNEL]->call(core => log => 'Starting Multi '.$VNDB::S{version}); + $_[KERNEL]->post(pg => register => error => 'pg_error'); + $_[KERNEL]->post(pg => 'connect'); + $_[KERNEL]->sig(INT => 'sig_shutdown'); + $_[KERNEL]->sig(TERM => 'sig_shutdown'); + $_[KERNEL]->sig('shutdown', 'shutdown'); + + # dynamically load and spawn modules + for (keys %{$VNDB::M{modules}}) { + my($mod, $args) = ($_, $VNDB::M{modules}{$_}); + next if !$args || ref($args) ne 'HASH'; + require "Multi/$mod.pm"; + # I'm surprised the strict pagma isn't complaining about this + "Multi::$mod"->spawn(%$args); } - $s->unlock(); } -sub execute { # cmd - $_[HEAP]{starttime} = [ gettimeofday ]; - my $cmd = (grep { $_[ARG0] =~ /$_->[0]/ } @{$_[HEAP]{cmds}})[0]; - if(!$cmd) { - $_[KERNEL]->call(core => log => 1, 'Unknown cmd: %s', $_[ARG0]); - $_[KERNEL]->yield(finish => $_[ARG0]); - return; - } - $_[KERNEL]->call(core => log => 2, 'Executing cmd: %s', $_[ARG0]); - $_[ARG0] =~ /$cmd->[0]/; # determine arguments (see perlvar for the magic) - my @arg = $#- ? map { substr $_[ARG0], $-[$_], $+[$_]-$-[$_] } 1..$#- : (); - $_[KERNEL]->post($cmd->[1] => $cmd->[2], $_[ARG0], @arg); -} - +sub log { # level, msg + (my $p = eval { $_[SENDER][2]{$_[CALLER_STATE]}[0] } || '') =~ s/^Multi:://; + my $msg = sprintf '%s::%s: %s', $p, $_[CALLER_STATE], + $_[ARG1] ? sprintf($_[ARG0], @_[ARG1..$#_]) : $_[ARG0]; -sub finish { # cmd - $_[HEAP]{running} = 0; - $_[KERNEL]->call(core => log => 2, "Unqueuing '%s' after %.2fs.", - $_[ARG0], tv_interval($_[HEAP]{starttime})); + open(my $F, '>>', $VNDB::M{log_dir}.'/multi.log'); + printf "[%s] %s\n", scalar localtime, $msg; + close $F; +} - my $s = tie my %s, 'Tie::ShareLite', -key => $VNDB::S{sharedmem_key}, -create => 'yes', -destroy => 'no', -mode => 0666; - $s->lock(LOCK_EX); - my @q = grep { $_ ne $_[ARG0] } $s{queue} ? @{$s{queue}} : (); - $s{queue} = \@q; - $s->unlock(); - $_[KERNEL]->yield('prepare'); +sub pg_error { # ARG: command, errmsg, [ query, params, orig_session, event-args ] + my $s = $_[ARG2] ? sprintf ' (Session: %s, Query: "%s", Params: %s, Args: %s)', + join(', ', $_[KERNEL]->alias_list($_[ARG4])), $_[ARG2], + join(', ', $_[ARG3] ? map qq|"$_"|, @{$_[ARG3]} : '[none]'), $_[ARG5]||'' : ''; + $_[KERNEL]->call(core => log => 'SQL Error for command %s: %s %s', $_[ARG0], $_[ARG1], $s); } -sub log { # level, msg - return if $_[ARG0] > $VNDB::M{log_level}; - - (my $p = eval { $_[SENDER][2]{$_[CALLER_STATE]}[0] } || '') =~ s/^Multi:://; - my $msg = sprintf '(%s) %s::%s: %s', - (qw|WRN ACT DBG|)[$_[ARG0]-1], $p, $_[CALLER_STATE], - $_[ARG2] ? sprintf($_[ARG1], @_[ARG2..$#_]) : $_[ARG1]; - - open(my $F, '>>', $VNDB::M{log_dir}.'/multi.log'); - printf $F "[%s] %s\n", scalar localtime, $msg; - close $F; +sub sig_shutdown { + # Multi modules should listen to the shutdown signal (but should never call sig_handled() on it!) + $_[KERNEL]->signal($_[SESSION], 'shutdown', 'SIG'.$_[ARG0]); + # consider this event as handled, so our process won't be killed directly + $_[KERNEL]->sig_handled(); } -sub cmd_exit { - $Multi::STOP = $_[ARG0] eq 'reload' ? 2 : 1; - $_[KERNEL]->call(core => finish => $_[ARG0]); - $_[KERNEL]->call(core => log => 2, 'Exiting...'); - - $_[KERNEL]->delay('heartbeat'); # stop the heartbeats - $_->delete() for (@{$_[HEAP]{cron}}); # stop scheduling cron jobs - $_[KERNEL]->signal($_[KERNEL], 'shutdown'); # Broadcast to other sessions +sub shutdown { + $_[KERNEL]->call(core => log => 'Shutting down (%s)', $_[ARG1]); + $_[KERNEL]->post(pg => 'shutdown'); + $_[KERNEL]->alias_remove('core'); } 1; - diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm index 02be04fb..2373178b 100644 --- a/lib/Multi/IRC.pm +++ b/lib/Multi/IRC.pm @@ -15,310 +15,288 @@ use POE qw| |; use POE::Component::IRC::Common ':ALL'; use URI::Escape 'uri_escape_utf8'; -use Net::HTTP; use constant { - ARG => ARG0, + USER => ARG0, DEST => ARG1, - NICK => ARG2 + ARG => ARG2, + MASK => ARG3, + + # long subquery used in several places + GETBOARDS => q{array_to_string(array( + SELECT tb.type||COALESCE(':'||COALESCE(u.username, vr.title, pr.name), '') + FROM threads_boards tb + LEFT JOIN vn v ON tb.type = 'v' AND v.id = tb.iid + LEFT JOIN vn_rev vr ON vr.id = v.latest + LEFT JOIN producers p ON tb.type = 'p' AND p.id = tb.iid + LEFT JOIN producers_rev pr ON pr.id = p.latest + LEFT JOIN users u ON tb.type = 'u' AND u.id = tb.iid + WHERE tb.tid = t.id + ORDER BY tb.type, tb.iid + ), ', ') AS boards}, }; +my $irc; -sub spawn { - return if $Multi::DAEMONIZE != 0; # we don't provide any commands, after all +sub spawn { my $p = shift; - my $irc = POE::Component::IRC::State->spawn( + $irc = POE::Component::IRC::State->spawn( alias => 'circ', NoDNS => 1, ); POE::Session->create( package_states => [ $p => [qw| - _start irc_001 irc_public irc_ctcp_action irc_msg irccmd vndbid ircnotify shutdown - cmd_info cmd_vndb cmd_list cmd_vn cmd_uptime cmd_notifications cmd_me cmd_say cmd_cmd cmd_eval + _start shutdown throttle_gc irc_001 irc_public irc_ctcp_action irc_msg + command idlequote reply notify + cmd_info cmd_list cmd_uptime cmd_vn cmd_vn_results cmd_p cmd_p_results cmd_quote cmd_quote_result + cmd_say cmd_me cmd_notifications cmd_eval cmd_die cmd_post vndbid formatid |], ], - heap => { irc => $irc, - o => { - user => 'Multi_test'.$$, - server => 'irc.synirc.net', - ircname => 'VNDB.org Multi', - channel => [ '#vndb' ], - @_ + heap => { + nick => 'Multi_test'.$$, + server => 'irc.synirc.net', + ircname => 'VNDB.org Multi', + channels => [ '#vndb' ], + masters => [ 'yorhel!*@*' ], + @_, + throttle => {}, + idlequotes => {}, + notify => {}, + lastrev => time, # using Multi's time for comparing to the DB time can fail + lastpost => time, # in some rare cases, but I doubt it'll be a problem. + lasttag => time, + commands => { + info => 0, # argument = authentication level/flags, + list => 0, # 0: everyone, + uptime => 0, # 1: only OPs in the first channel listed in @channels + vn => 0, # 2: only users matching the mask in @masters + p => 0, # |8: has to be addressed to the bot (e.g. 'Multi: eval' instead of '!eval') + quote => 0, + say => 1|8, + me => 1|8, + notifications => 1, + eval => 2|8, + die => 2|8, + post => 2|8, }, - log => {}, - privpers => {}, - notify => [], } ); } +# non-POE helper function +# Arguments: $_[HEAP], key, timeout, (optional) num +# no key = remove all keys with no activity in the last hour +# returns false if throttling isn't necessary for that key +sub throttle { + my($heap, $key, $tm, $num) = @_; + + # garbage collect + return ($heap->{throttle} = { + map $_->[$#$_] > time-3600 ? ($_, $heap->{throttle}{$_}) : (), keys %{$heap->{throttle}} + }) if !$key; + + $num ||= 1; + my $dat = $heap->{throttle}; + if(!$dat->{$key}) { + $dat->{$key} = [ time ]; + return 0; + } + $dat->{$key} = [ grep $_ > time-$tm, @{$dat->{$key}} ]; + return 1 if @{$dat->{$key}} >= $num; + push @{$dat->{$key}}, time; + return 0; +} + + sub _start { $_[KERNEL]->alias_set('irc'); - $_[KERNEL]->call(core => register => qr/^ircnotify ([vrptg][0-9]+(?:\.[0-9]+)?)$/, 'ircnotify'); - $_[HEAP]{irc}->plugin_add( + $irc->plugin_add( Logger => POE::Component::IRC::Plugin::Logger->new( Path => $VNDB::M{log_dir}, Private => 0, Public => 1, )); - $_[HEAP]{irc}->plugin_add( + $irc->plugin_add( Connector => POE::Component::IRC::Plugin::Connector->new() ); - $_[HEAP]{irc}->plugin_add( + $irc->plugin_add( CTCP => POE::Component::IRC::Plugin::CTCP->new( - version => $_[HEAP]{o}{ircname}.' v'.$VNDB::S{version}, - userinfo => $_[HEAP]{o}{ircname}, + version => $_[HEAP]{ircname}.' v'.$VNDB::S{version}, + userinfo => $_[HEAP]{ircname}, )); - if($_[HEAP]{o}{pass}) { + if($_[HEAP]{pass}) { require POE::Component::IRC::Plugin::NickServID; - $_[HEAP]{irc}->plugin_add( + $irc->plugin_add( NickServID => POE::Component::IRC::Plugin::NickServID->new( - Password => $_[HEAP]{o}{pass} - )) + Password => $_[HEAP]{pass} + )) } - if($_[HEAP]{o}{console}) { + if($_[HEAP]{console}) { require POE::Component::IRC::Plugin::Console; - $_[HEAP]{irc}->plugin_add( + $irc->plugin_add( Console => POE::Component::IRC::Plugin::Console->new( bindport => 3030, - password => $_[HEAP]{o}{console} - )) + password => $_[HEAP]{console} + )) } - $_[KERNEL]->post(circ => register => 'all'); - $_[KERNEL]->post(circ => connect => { - Nick => $_[HEAP]{o}{user}, + $irc->yield(register => 'all'); + $irc->yield(connect => { + Nick => $_[HEAP]{nick}, Username => 'u1', - Ircname => $_[HEAP]{o}{ircname}, - Server => $_[HEAP]{o}{server}, + Ircname => $_[HEAP]{ircname}, + Server => $_[HEAP]{server}, }); - # notifications in the main channel enabled by default - push @{$_[HEAP]{notify}}, $_[HEAP]{o}{channel}[0]; + $_[KERNEL]->post(pg => listen => + newrevision => 'notify', + newpost => 'notify', + newtag => 'notify', + ); + $_[HEAP]{notify}{$_[HEAP]{channels}[0]} = 1; - $_[KERNEL]->sig('shutdown' => 'shutdown'); + $_[KERNEL]->sig(shutdown => 'shutdown'); + $_[KERNEL]->delay(throttle_gc => 1800); + $_[KERNEL]->delay(idlequote => 300); } -sub irc_001 { - $_[KERNEL]->post(circ => join => $_) for (@{$_[HEAP]{o}{channel}}); - $_[KERNEL]->call(core => log => 2, 'Connected to IRC!'); +sub shutdown { + $irc->yield(shutdown => $_[ARG1]); + $_[KERNEL]->post(pg => unlisten => qw|newrevision newpost newtag|); + $_[KERNEL]->delay('throttle_gc'); + $_[KERNEL]->delay('idlequote'); + $_[KERNEL]->alias_remove('irc'); } -sub irc_public { - if($_[ARG2] =~ /^!/) { - (my $cmd = $_[ARG2]) =~ s/^!//; - my $nick = (split /!/, $_[ARG0])[0]; - $_[KERNEL]->call(irc => irccmd => $_[ARG1][0], $cmd, $nick); - } else { - $_[KERNEL]->call(irc => vndbid => $_[ARG1][0], $_[ARG2]); - } +sub throttle_gc { + throttle $_[HEAP]; + $_[KERNEL]->delay(throttle_gc => 1800); } -sub irc_ctcp_action { - $_[KERNEL]->call(irc => vndbid => $_[ARG1][0], $_[ARG2]); +sub irc_001 { + $irc->yield(join => $_) for (@{$_[HEAP]{channels}}); + $_[KERNEL]->call(core => log => 'Connected to IRC'); } -sub irc_msg { - my $nick = ( split /!/, $_[ARG0] )[0]; - $_[ARG2] =~ s/^!//; - if(!$_[KERNEL]->call(irc => irccmd => $nick => $_[ARG2])) { - $_[HEAP]{privpers}{$_} < time-3600 and delete $_[HEAP]{privpers}{$_} - for (keys %{$_[HEAP]{privpers}}); - $_[KERNEL]->post(circ => privmsg => $nick => 'I am not human, join #vndb or PM Yorhel if you need something.') - if !$_[HEAP]{privpers}{$nick}; - $_[HEAP]{privpers}{$nick} ||= time; - } +sub irc_public { # mask, dest, msg + $_[HEAP]{idlequotes}{$_[ARG1][0]} = 0; + return if $_[KERNEL]->call($_[SESSION] => command => @_[ARG0..$#_]); + $_[KERNEL]->call($_[SESSION] => vndbid => $_[ARG1], $_[ARG2]); } -sub irccmd { # dest, cmd, [nick] - my($dest, $cmd, $nick) = @_[ARG0..$#_]; - $nick ||= $_[ARG0]; +sub irc_ctcp_action { # mask, dest, msg + $_[KERNEL]->call($_[SESSION] => vndbid => $_[ARG1], $_[ARG2]); +} + - return 0 if $cmd !~ /^([a-z0-9A-Z_]+)(?: (.+))?$/; - my($f, $a) = (lc $1, $2||''); +sub irc_msg { # mask, dest, msg + return if $_[KERNEL]->call($_[SESSION] => command => $_[ARG0], [scalar parse_user($_[ARG0])], $_[ARG2]); - # check for a cmd_* function and call it (some scary magic, see perlmod) - my $sub; - { - no strict; - $sub = ${__PACKAGE__.'::'}{'cmd_'.$f}; - } - return 0 if !defined $sub; - local *SUB = $sub; - return 0 if !defined *SUB{CODE}; - $_[KERNEL]->yield('cmd_'.$f, $a, $dest, $nick); - return 1; + my $usr = parse_user($_[ARG0]); + $irc->yield(notice => $usr, 'I am not human, join #vndb or PM Yorhel if you need something.') + unless throttle $_[HEAP], "pm-$usr", 30; } -sub vndbid { # dest, msg, force - my $m = $_[ARG1]; +sub command { # mask, dest, msg + my($mask, $dest, $msg) = @_[ARG0..$#_]; - $_[HEAP]{log}{$_} < time-60 and delete $_[HEAP]{log}{$_} - for (keys %{$_[HEAP]{log}}); + my $me = $irc->nick_name(); + my $addressed = $dest->[0] !~ /^#/ || $msg =~ s/^\s*\Q$me\E[:,;.!?~]?\s*//; + return 0 if !$addressed && !($msg =~ s/^\s*!//); - # Four possible options: - # 1. [tvprug]+ -> item/user/thread/tag (nf) - # 2. [vprt]+.+ -> revision/reply (ef) - # 3. d+ -> documentation page (nf) - # 4. d+.+ -> documentation page # section (sf) + return 0 if $msg !~ /^([a-z]+)(?:\s+(.+))?$/; + my($cmd, $arg) = ($1, $2); + return 0 if !exists $_[HEAP]{commands}{$cmd} || ($_[HEAP]{commands}{$cmd} & 8) && !$addressed; - # nf (normal format): x+ : x, id, title - # sf (sub format): x+.+ : x, id, subid, title, action2, title2 - # ef (extended format): x+.+ : x, id, subid, action, title, action2, title2 - my $nf = BOLD.RED.'['.NORMAL.BOLD.'%s%d' .RED.']' .NORMAL.' %s ' .RED.'@'.NORMAL.LIGHT_GREY.' '.$VNDB::S{url}.'/%1$s%2$d'.NORMAL; - my $sf = BOLD.RED.'['.NORMAL.BOLD.'%s%d.%d'.RED.']' .NORMAL.' %s '.RED.'%s'.NORMAL.' %s '.RED.'@'.NORMAL.LIGHT_GREY.' '.$VNDB::S{url}.'/%1$s%2$d.%3$d'.NORMAL; - my $ef = BOLD.RED.'['.NORMAL.BOLD.'%s%d.%d'.RED.']'.NORMAL.RED.' %s'.NORMAL.' %s '.RED.'%s'.NORMAL.' %s '.RED.'@'.NORMAL.LIGHT_GREY.' '.$VNDB::S{url}.'/%1$s%2$d.%3$d'.NORMAL; - - # get a list of possible IDs (a la sub summary in defs.pl) - my @id; # [ type, id, ref ] - for (split /[, ]/, $m) { - next if length > 15 or m{[a-z]{3,6}://}i; # weed out URLs and too long things - push @id, /^(?:.*[^\w]|)([dvprt])([1-9][0-9]*)\.([1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, $2, $3 ] # matches 2 and 4 - : /^(?:.*[^\w]|)([dvprtug])([1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, $2, 0 ] : (); # matches 1 and 3 - } + my $usr = parse_user($mask); + return $_[KERNEL]->yield(reply => $dest, + $dest eq $_[HEAP]{channels}[0] ? 'Only OPs can do that!' : "Only $_[HEAP]{channel}[0] OPs can do that!", $usr) || 1 + if $_[HEAP]{commands}{$cmd} == 1 && !$irc->is_channel_operator($_[HEAP]{channels}[0], $usr); + return $_[KERNEL]->yield(reply => $dest, 'You are not my master!', $usr) || 1 + if $_[HEAP]{commands}{$cmd} == 2 && !grep matches_mask($_, $mask), @{$_[HEAP]{masters}}; - # loop through the matched IDs and search the database - for (@id) { - my($t, $id, $rev) = (@$_); - - next if $_[HEAP]{log}{$t.$id.'.'.$rev} && !$_[ARG2]; - $_[HEAP]{log}{$t.$id.'.'.$rev} = time; - - # option 1: item/user/thread/tag - if($t =~ /[vprtug]/ && !$rev) { - my $s = $Multi::SQL->prepare( - $t eq 'v' ? 'SELECT vr.title FROM vn_rev vr JOIN vn v ON v.latest = vr.id WHERE v.id = ?' : - $t eq 'u' ? 'SELECT u.username AS title FROM users u WHERE u.id = ?' : - $t eq 'p' ? 'SELECT pr.name AS title FROM producers_rev pr JOIN producers p ON p.latest = pr.id WHERE p.id = ?' : - $t eq 't' ? 'SELECT title FROM threads WHERE id = ?' : - $t eq 'g' ? 'SELECT name AS title FROM tags WHERE id = ?' : - 'SELECT rr.title FROM releases_rev rr JOIN releases r ON r.latest = rr.id WHERE r.id = ?' - ); - $s->execute($id); - my $r = $s->fetchrow_hashref; - $s->finish; - next if !$r || ref($r) ne 'HASH'; - $_[KERNEL]->post(circ => privmsg => $_[ARG0], sprintf $nf, - $t, $id, $r->{title}); - - # option 2: revision/reply - } elsif($t =~ /[vprt]/) { - my $s = $Multi::SQL->prepare( - $t eq 'v' ? 'SELECT vr.title, u.username FROM changes c JOIN vn_rev vr ON c.id = vr.id JOIN users u ON u.id = c.requester WHERE vr.vid = ? AND c.rev = ?' : - $t eq 'r' ? 'SELECT rr.title, u.username FROM changes c JOIN releases_rev rr ON c.id = rr.id JOIN users u ON u.id = c.requester WHERE rr.rid = ? AND c.rev = ?' : - $t eq 'p' ? 'SELECT pr.name, u.username FROM changes c JOIN producers_rev pr ON c.id = pr.id JOIN users u ON u.id = c.requester WHERE pr.pid = ? AND c.rev = ?' : - 'SELECT t.title, u.username FROM threads t JOIN threads_posts tp ON tp.tid = t.id JOIN users u ON u.id = tp.uid WHERE t.id = ? AND tp.num = ?' - ); - $s->execute($id, $rev); - my $r = $s->fetchrow_arrayref; - next if !$r || ref($r) ne 'ARRAY'; - $_[KERNEL]->post(circ => privmsg => $_[ARG0], sprintf $ef, $t, $id, $rev, - $rev == 1 ? 'New '.($t eq 'v' ? 'visual novel' : $t eq 'p' ? 'producer' : $t eq 'r' ? 'release': 'thread') - : ($t eq 't' ? 'Reply to' : 'Edit of'), $r->[0], 'By', $r->[1] - ); - - # option 3: documentation page - } elsif($t eq 'd') { - my $f = sprintf '/www/vndb/data/docs/%d', $id; - open my $F, '<', $f or next; - (my $title = <$F>) =~ s/^:TITLE://; - chomp($title); + return $_[KERNEL]->yield('cmd_'.$cmd, $usr, $dest, $arg, $mask) || 1; +} - if(!$rev) { - $_[KERNEL]->post(circ => privmsg => $_[ARG0], sprintf $nf, - 'd', $id, $title); - next; - } - # option 4: documentation page # section - my($sec, $sub); - while(<$F>) { - if(/^:SUB:/ && ++$sec == $rev) { - chomp; - ($sub = $_) =~ s/^:SUB://; - last; - } - } - next if !$sub; - $_[KERNEL]->post(circ => privmsg => $_[ARG0], sprintf $sf, - 'd', $id, $rev, $title, '->', $sub); - } +sub idlequote { + for (keys %{$_[HEAP]{idlequotes}}) { + next if --$_[HEAP]{idlequotes}{$_} > 0; + $_[KERNEL]->yield(cmd_quote => '', [$_]) if $_[HEAP]{idlequotes}{$_} == 0; + $_[HEAP]{idlequotes}{$_} = int(60+rand(300)); } + $_[KERNEL]->delay(idlequote => 60); } -sub ircnotify { # command, VNDBID - $_[KERNEL]->yield(vndbid => $_ => $_[ARG1] => 1) for (@{$_[HEAP]{notify}}); - $_[KERNEL]->post(core => finish => $_[ARG0]); +# convenience function +sub reply { # target, msg [, mask/user] + my $usr = $_[ARG0][0] =~ /^#/ && parse_user($_[ARG2]); + $irc->yield($_[ARG0][0] =~ /^#/ ? 'privmsg' : 'notice', $_[ARG0], ($usr ? "$usr, " : '').$_[ARG1]); } -sub shutdown { - $_[KERNEL]->post(circ => shutdown => 'Byebye!'); +sub notify { # name, pid, payload + my $k = $_[ARG0] eq 'newrevision' ? 'lastrev' : $_[ARG0] eq 'newpost' ? 'lastpost' : 'lasttag'; + my $t = $_[HEAP]{$k}; + $_[HEAP]{$k} = time; + + return if !keys %{$_[HEAP]{notify}}; + + my $q = $_[ARG0] eq 'newrevision' ? q|SELECT + CASE WHEN c.type = 0 THEN 'v' WHEN c.type = 1 THEN 'r' ELSE 'p' END AS type, c.rev, c.comments, + COALESCE(vr.vid, rr.rid, pr.pid) AS id, COALESCE(vr.title, rr.title, pr.name) AS title, u.username + FROM changes c + LEFT JOIN vn_rev vr ON c.type = 0 AND c.id = vr.id + LEFT JOIN releases_rev rr ON c.type = 1 AND c.id = rr.id + LEFT JOIN producers_rev pr ON c.type = 2 AND c.id = pr.id + JOIN users u ON u.id = c.requester + WHERE c.added > ? + ORDER BY c.added| + : $_[ARG0] eq 'newpost' ? q|SELECT + 't' AS type, tp.tid AS id, tp.num AS rev, t.title, u.username, |.GETBOARDS.q| + FROM threads_posts tp + JOIN threads t ON t.id = tp.tid + JOIN users u ON u.id = tp.uid + WHERE tp.date > ? + ORDER BY tp.date| + : q|SELECT + 'g' AS type, t.id, t.name AS title, u.username + FROM tags t + JOIN users u ON u.id = t.addedby + WHERE t.added > ? + ORDER BY t.added|; + + $_[KERNEL]->post(pg => query => $q, [ $t ], 'formatid', [ keys %{$_[HEAP]{notify}} ]); } -# cmd_* commands: $arg, $dest, $nick -sub cmd_info { - $_[KERNEL]->post(circ => privmsg => $_[DEST], - 'Hello, I am HMX-12 Multi v'.$VNDB::S{version}.' made by the great Yorhel!'); -} +# +# I R C C O M M A N D S +# -sub cmd_vndb { - $_[KERNEL]->post(circ => privmsg => $_[DEST], - 'VNDB ~ The Visual Novel Database ~ http://vndb.org/'); +sub cmd_info { + $_[KERNEL]->yield(reply => $_[DEST], + 'Hi! I am HMX-12 Multi '.$VNDB::S{version}.', the IRC bot of '.$VNDB::S{url}.'/, written by the great Yorhel!'); } sub cmd_list { - return if $_[DEST] ne $_[HEAP]{o}{channel}[0]; - $_[KERNEL]->post(circ => privmsg => $_[DEST], - $_[NICK].', this is not a warez channel!'); -} - - -sub cmd_vn { # $arg = search string - $_[ARG] =~ s/%//g; - return $_[KERNEL]->post(circ => privmsg => $_[DEST], 'You forgot the search query, idiot~~!.') if !$_[ARG]; - - my $q = $Multi::SQL->prepare(q| - SELECT v.id - FROM vn v - JOIN vn_rev vr ON vr.id = v.latest - WHERE vr.title ILIKE $1 - OR vr.alias ILIKE $1 - OR v.id IN( - SELECT rv.vid - FROM releases r - JOIN releases_rev rr ON rr.id = r.latest - JOIN releases_vn rv ON rv.rid = rr.id - WHERE rr.title ILIKE $1 - OR rr.original ILIKE $1 - ) - ORDER BY vr.id - LIMIT 6|); - $q->execute('%'.$_[ARG].'%'); - - my $res = $q->fetchall_arrayref([]); - return $_[KERNEL]->post(circ => privmsg => $_[DEST], - sprintf 'No results found for %s', $_[ARG]) if !@$res; - return $_[KERNEL]->post(circ => privmsg => $_[DEST], - sprintf 'Too many results found, see %s/v/search?q=%s', - $VNDB::S{url}, uri_escape_utf8($_[ARG])) if @$res > 5; - $_[KERNEL]->yield(vndbid => $_[DEST], join(' ', map 'v'.$_->[0], @$res), 1); + $_[KERNEL]->yield(reply => $_[DEST], + $_[DEST][0] =~ /^#/ ? 'This is not a warez channel!' : 'I am not a warez bot!', $_[USER]); } @@ -337,82 +315,245 @@ sub cmd_uptime { open my $R, '<', '/proc/uptime'; my $server = <$R> =~ /^\s*([0-9]+)/ ? $1 : 0; close $R; - my $multi = time - $^T; - my $http=0; - # this should actually be done asynchronously... but I don't expect it to timeout - if(my $req = Net::HTTP->new(Host => 'localhost', Timeout => 1)) { - $req->write_request(GET => '/server-status?auto'); - my $d; - $req->read_entity_body($d, 1024) if $req->read_response_headers; - $http = $1 if $d =~ /Uptime:\s*([0-9]+)/i; - } - - $_[KERNEL]->post(circ => privmsg => $_[DEST], $_) for (split /\n/, sprintf - "Uptimes:\n Server: %s\n Multi: %s\n HTTP: %s", map $age->($_), $server, $multi, $http); + $_[KERNEL]->yield(reply => $_[DEST], sprintf 'Server uptime: %s -- mine: %s', $age->($server), $age->($multi)); } -sub cmd_notifications { # $arg = '' or 'on' or 'off' - return unless &mymaster; - if($_[ARG] =~ /^on$/i) { - push @{$_[HEAP]{notify}}, $_[DEST] if !grep $_ eq $_[DEST], @{$_[HEAP]{notify}}; - $_[KERNEL]->post(circ => privmsg => $_[DEST], 'Notifications enabled.'); - } elsif($_[ARG] =~ /^off$/i) { - $_[HEAP]{notify} = [ grep $_ ne $_[DEST], @{$_[HEAP]{notify}} ]; - $_[KERNEL]->post(circ => privmsg => $_[DEST], 'Notifications disabled.'); - } else { - $_[KERNEL]->post(circ => privmsg => $_[DEST], sprintf 'Notifications %s, type !notifications %s to %s.', - (grep $_ eq $_[DEST], @{$_[HEAP]{notify}}) ? ('enabled', 'off', 'disable') : ('disabled', 'on', 'enable')); - } +sub cmd_vn { + (my $q = $_[ARG]||'') =~ s/%//g; + return $_[KERNEL]->yield(reply => $_[DEST], 'You forgot the search query, dummy~~!', $_[USER]) if !$q; + return $_[KERNEL]->yield(reply => $_[DEST], 'Stop abusing me, it\'s not like I enjoy spamming this channel!', $_[USER]) + if throttle $_[HEAP], "query-$_[USER]-$_[DEST][0]", 60, 5; + + $_[KERNEL]->post(pg => query => q| + SELECT 'v'::text AS type, v.id, vr.title + FROM vn v + JOIN vn_rev vr ON vr.id = v.latest + WHERE v.hidden = FALSE AND (vr.title ILIKE $1 + OR vr.alias ILIKE $1 + OR v.id IN( + SELECT rv.vid + FROM releases r + JOIN releases_rev rr ON rr.id = r.latest + JOIN releases_vn rv ON rv.rid = rr.id + WHERE rr.title ILIKE $1 + OR rr.original ILIKE $1 + )) + ORDER BY vr.title + LIMIT 6|, [ "%$q%" ], 'cmd_vn_results', \@_); +} + + +sub cmd_vn_results { # num, res, \@_ + return $_[KERNEL]->yield(reply => $_[ARG2][DEST], 'No visual novels found', $_[ARG2][USER]) if $_[ARG0] < 1; + return $_[KERNEL]->yield(reply => $_[ARG2][DEST], sprintf( + 'Too many results found, see %s/v/all?q=%s', $VNDB::S{url}, uri_escape_utf8($_[ARG2][ARG]) + ), $_[ARG2][USER]) if $_[ARG0] > 5; + $_[KERNEL]->yield(formatid => $_[ARG0], $_[ARG1], $_[ARG2][DEST]); +} + + +sub cmd_p { + (my $q = $_[ARG]||'') =~ s/%//g; + return $_[KERNEL]->yield(reply => $_[DEST], 'You forgot the search query, dummy~~!', $_[USER]) if !$q; + return $_[KERNEL]->yield(reply => $_[DEST], 'Stop abusing me, it\'s not like I enjoy spamming this channel!', $_[USER]) + if throttle $_[HEAP], "query-$_[USER]-$_[DEST][0]", 60, 5; + + $_[KERNEL]->post(pg => query => q| + SELECT 'p'::text AS type, p.id, pr.name AS title + FROM producers p + JOIN producers_rev pr ON pr.id = p.latest + WHERE p.hidden = FALSE AND (pr.name ILIKE $1 OR pr.original ILIKE $1 OR pr.alias ILIKE $1) + ORDER BY pr.name + LIMIT 6|, [ "%$q%" ], "cmd_p_results", \@_); +} + + +sub cmd_p_results { # num, res, \@_ + return $_[KERNEL]->yield(reply => $_[ARG2][DEST], 'No producers found', $_[ARG2][USER]) if $_[ARG0] < 1; + return $_[KERNEL]->yield(reply => $_[ARG2][DEST], sprintf( + 'Too many results found, see %s/p/all?q=%s', $VNDB::S{url}, uri_escape_utf8($_[ARG2][ARG]) + ), $_[ARG2][USER]) if $_[ARG0] > 5; + $_[KERNEL]->yield(formatid => $_[ARG0], $_[ARG1], $_[ARG2][DEST]); +} + + +sub cmd_quote { + $_[KERNEL]->post(pg => query => q|SELECT quote FROM quotes ORDER BY random() LIMIT 1|, undef, 'cmd_quote_result', $_[DEST]); +} + + +sub cmd_quote_result { # 1, res, dest + $_[KERNEL]->yield(reply => $_[ARG2] => $_[ARG1][0]{quote}) if $_[ARG0] > 0; } -sub cmd_say { # $arg = '[#chan ]text', no #chan = $dest - return unless &mymaster; +sub cmd_say { my $chan = $_[ARG] =~ s/^(#[a-zA-Z0-9-_.]+) // ? $1 : $_[DEST]; - $_[KERNEL]->post(circ => privmsg => $chan, $_[ARG]); + $irc->yield(privmsg => $chan, $_[ARG]); } -sub cmd_me { # same as cmd_say, but CTCP ACTION - return unless &mymaster; +sub cmd_me { my $chan = $_[ARG] =~ s/^(#[a-zA-Z0-9-_.]+) // ? $1 : $_[DEST]; - $_[KERNEL]->post(circ => ctcp => $chan, 'ACTION '.$_[ARG]); + $irc->yield(ctcp => $chan, 'ACTION '.$_[ARG]); } -sub cmd_cmd { # TODO: feedback? - return unless &mymaster; - $_[KERNEL]->post(core => queue => $_[ARG]); - $_[KERNEL]->post(circ => privmsg => $_[DEST] => sprintf "Executing %s", $_[ARG]); +sub cmd_notifications { # $arg = '' or 'on' or 'off' + if($_[ARG] && $_[ARG] =~ /^on$/i) { + $_[HEAP]{notify}{$_[DEST][0]} = 1; + $_[KERNEL]->yield(reply => $_[DEST], 'Notifications enabled.'); + } elsif($_[ARG] && $_[ARG] =~ /^off$/i) { + delete $_[HEAP]{notify}{$_[DEST][0]}; + $_[KERNEL]->yield(reply => $_[DEST], 'Notifications disabled.'); + } else { + $_[KERNEL]->yield(reply => $_[DEST], sprintf 'Notifications %s, type !notifications %s to %s.', + $_[HEAP]{notify}{$_[DEST][0]} ? ('enabled', 'off', 'disable') : ('disabled', 'on', 'enable')); + } } -sub cmd_eval { # the evil cmd - return unless &mymaster; - $_[KERNEL]->post(circ => privmsg => $_[DEST], 'eval: '.$_) +sub cmd_eval { + $_[KERNEL]->yield(reply => $_[DEST], 'eval: '.$_) for (split /\r?\n/, eval($_[ARG])||$@); } +sub cmd_die { + $irc->yield(ctcp => $_[DEST] => 'ACTION dies'); + $_[KERNEL]->signal(core => shutdown => "Killed on IRC by $_[USER]"); +} -# non-POE function, checks whether we should trust $nick -sub mymaster { # same @_ as the cmd_ functions - if(!$_[HEAP]{irc}->is_channel_operator($_[HEAP]{o}{channel}[0], $_[ARG2]) - && !$_[HEAP]{irc}->is_channel_owner($_[HEAP]{o}{channel}[0], $_[ARG2]) - && !$_[HEAP]{irc}->is_channel_admin($_[HEAP]{o}{channel}[0], $_[ARG2]) - || ($_[HEAP]{o}{master_users} && !grep lc($_) eq lc($_[ARG2]), @{$_[HEAP]{o}{master_users}}) - ) { - $_[KERNEL]->post(circ => privmsg => $_[ARG1], - ($_[ARG1]=~/^#/?$_[ARG2].', ':'').'You are not my master!'); - return 0; +sub cmd_post { + $_[KERNEL]->yield(reply => $_[DEST], $_[KERNEL]->post(split /\s+/, $_[ARG]) + ? 'Sent your message to the post office, it will be processed shortly!' + : "Oh no! The post office wouldn't accept your message! Wrong destination address?", $_[USER]); +} + + + + +# +# D B I T E M L I N K S +# + + +sub vndbid { # dest, msg + my($dest, $msg) = @_[ARG0, ARG1]; + + my @id; # [ type, id, ref ] + for (split /[, ]/, $msg) { + next if length > 15 or m{[a-z]{3,6}://}i; # weed out URLs and too long things + push @id, /^(?:.*[^\w]|)([dvprt])([1-9][0-9]*)\.([1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, $2, $3 ] # x+.+ + : /^(?:.*[^\w]|)([dvprtug])([1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, $2, 0 ] : (); # x+ + } + + for (@id) { + my($t, $id, $rev) = @$_; + next if throttle $_[HEAP], "$dest->[0].$t$id.$rev", 60; + + # plain vn/user/producer/thread/tag/release + $_[KERNEL]->post(pg => query => 'SELECT ?::text AS type, ?::integer AS id, '.( + $t eq 'v' ? 'vr.title FROM vn_rev vr JOIN vn v ON v.latest = vr.id WHERE v.id = ?' : + $t eq 'u' ? 'u.username AS title FROM users u WHERE u.id = ?' : + $t eq 'p' ? 'pr.name AS title FROM producers_rev pr JOIN producers p ON p.latest = pr.id WHERE p.id = ?' : + $t eq 't' ? 'title, '.GETBOARDS.' FROM threads t WHERE id = ?' : + $t eq 'g' ? 'name AS title FROM tags WHERE id = ?' : + 'rr.title FROM releases_rev rr JOIN releases r ON r.latest = rr.id WHERE r.id = ?'), + [ $t, $id, $id ], 'formatid', $dest + ) if !$rev && $t =~ /[vprtug]/; + + # edit/insert of vn/release/producer or discussion board post + $_[KERNEL]->post(pg => query => 'SELECT ?::text AS type, ?::integer AS id, ?::integer AS rev, '.( + $t eq 'v' ? 'vr.title, u.username, c.comments FROM changes c JOIN vn_rev vr ON c.id = vr.id JOIN users u ON u.id = c.requester WHERE vr.vid = ? AND c.rev = ?' : + $t eq 'r' ? 'rr.title, u.username, c.comments FROM changes c JOIN releases_rev rr ON c.id = rr.id JOIN users u ON u.id = c.requester WHERE rr.rid = ? AND c.rev = ?' : + $t eq 'p' ? 'pr.name AS title, u.username, c.comments FROM changes c JOIN producers_rev pr ON c.id = pr.id JOIN users u ON u.id = c.requester WHERE pr.pid = ? AND c.rev = ?' : + 't.title, u.username, '.GETBOARDS.' FROM threads t JOIN threads_posts tp ON tp.tid = t.id JOIN users u ON u.id = tp.uid WHERE t.id = ? AND tp.num = ?'), + [ $t, $id, $rev, $id, $rev], 'formatid', $dest + ) if $rev && $t =~ /[vprt]/; + + # documentation page (need to parse the doc pages manually here) + if($t eq 'd') { + my $f = sprintf $VNDB::ROOT.'/data/docs/%d', $id; + my($title, $sec, $sub) = (undef, 0); + open my $F, '<', $f or next; + while(<$F>) { + chomp; + $title = $1 if /^:TITLE:(.+)$/; + $sub = $1 if $rev && /^:SUB:(.+)$/ && ++$sec == $rev; + } + close $F; + next if $rev && !$sub; + $_[KERNEL]->yield(formatid => 1, [{type => 'd', id => $id, title => $title, rev => $rev, section => $sub}], $dest); + } } - return 1; } -1; +# formats and posts database items listed in @res, where each item is a hashref with: +# type database item in [dvprtug] +# id database id +# title main name or title of the DB entry +# rev (optional) revision, post number or section number +# username (optional) relevant username +# section (optional, for d+.+) section title +# boards (optional) board titles the thread has been posted in +# comments (optional) edit summary +sub formatid { + my($num, $res, $dest) = @_[ARG0..$#_]; + + # only the types for which creation/edit announcements matter + my %types = ( + v => 'visual novel', + p => 'producer', + r => 'release', + g => 'tag', + t => 'thread', + ); + + for (@$res) { + my $id = $_->{type}.$_->{id} . ($_->{rev} ? '.'.$_->{rev} : ''); + + # (always) [x+.+] + my @msg = ( + BOLD.RED.'['.NORMAL.BOLD.$id.RED.']'.NORMAL + ); + + # (only if username key is present) Edit of / New item / reply to / whatever + push @msg, RED.( + ($_->{rev}||1) == 1 ? 'New '.$types{$_->{type}} : + $_->{type} eq 't' ? 'Reply to' : 'Edit of' + ).NORMAL if $_->{username}; + + # (always) main title + push @msg, $_->{title}; + + # (only if boards key is present) Posted in [boards] + push @msg, RED.'Posted in'.NORMAL.' '.$_->{boards} if $_->{boards}; + + # (only if username key is present) By [username] + push @msg, RED.'By'.NORMAL.' '.$_->{username} if $_->{username}; + + # (only if comments key is present) Summary: + push @msg, RED.'Summary:'.NORMAL.' '.( + length $_->{comments} > 40 ? substr($_->{comments}, 0, 37).'...' : $_->{comments} + ) if defined $_->{comments}; + + # (for d+.+) -> section title + push @msg, RED.'->'.NORMAL.' '.$_->{section} if $_->{section}; + + # (always) @ URL + push @msg, RED.'@ '.NORMAL.LIGHT_GREY.$VNDB::S{url}.'/'.$id.NORMAL; + + # now post it + $_[KERNEL]->yield(reply => $dest, join ' ', @msg); + } +} + + +1; diff --git a/lib/Multi/Image.pm b/lib/Multi/Image.pm index fe3d411e..f947bf7d 100644 --- a/lib/Multi/Image.pm +++ b/lib/Multi/Image.pm @@ -9,7 +9,6 @@ use strict; use warnings; use POE; use Image::Magick; -use Image::MetaData::JPEG; use Time::HiRes 'time'; @@ -18,8 +17,7 @@ sub spawn { POE::Session->create( package_states => [ $p => [qw| _start - cmd_coverimage cv_process cv_update cv_finish - cmd_screenshot scr_process scr_update scr_clean scr_finish + _start shutdown cv_check cv_process scr_check scr_process |], ], heap => { @@ -28,6 +26,8 @@ sub spawn { cvpath => $VNDB::ROOT.'/static/cv', sfpath => $VNDB::ROOT.'/static/sf', stpath => $VNDB::ROOT.'/static/st', + check_delay => 3600, + @_, }, ); } @@ -36,212 +36,112 @@ sub spawn { sub _start { $_[KERNEL]->alias_set('image'); $_[KERNEL]->sig(shutdown => 'shutdown'); - $_[KERNEL]->call(core => register => qr/^coverimage(?: ([0-9]+)|)$/, 'cmd_coverimage'); - $_[KERNEL]->call(core => register => qr/^screenshot(?: ([0-9]+|all|clean))?$/, 'cmd_screenshot'); + $_[KERNEL]->post(pg => listen => coverimage => 'cv_check', screenshot => 'scr_check'); + $_[KERNEL]->yield('cv_check'); + $_[KERNEL]->yield('scr_check'); } -sub cmd_coverimage { - $_[HEAP]{curcmd} = $_[ARG0]; - - if($_[ARG1]) { - $_[HEAP]{todo} = [ $_[ARG1] ]; - } else { - my $q = $Multi::SQL->prepare('SELECT image FROM vn_rev WHERE image < 0'); - $q->execute(); - $_[HEAP]{todo} = [ map { -1*$_->[0]} @{$q->fetchall_arrayref([])} ]; - if(!@{$_[HEAP]{todo}}) { - $_[KERNEL]->call(core => log => 2, 'No images to process'); - $_[KERNEL]->yield('cv_finish'); - return; - } - } - $_[KERNEL]->yield(cv_process => $_[HEAP]{todo}[0]); +sub shutdown { + $_[KERNEL]->post(pg => unlisten => 'coverimage', 'screenshot'); + $_[KERNEL]->delay('cv_check'); + $_[KERNEL]->delay('scr_check'); + $_[KERNEL]->alias_remove('image'); } -sub cv_process { # id - my $start = time; +sub cv_check { + $_[KERNEL]->delay('cv_check'); + $_[KERNEL]->post(pg => query => 'SELECT image FROM vn_rev WHERE image < 0 LIMIT 1', undef, 'cv_process'); +} + - my $img = sprintf '%s/%02d/%d.jpg', $_[HEAP]{cvpath}, $_[ARG0]%100, $_[ARG0]; +sub cv_process { # num, res + return $_[KERNEL]->delay(cv_check => $_[HEAP]{check_delay}) if $_[ARG0] == 0; + my $id = -1*$_[ARG1][0]{image}; + my $start = time; + my $img = sprintf '%s/%02d/%d.jpg', $_[HEAP]{cvpath}, $id%100, $id; my $os = -s $img; + my $im = Image::Magick->new; $im->Read($img); $im->Set(magick => 'JPEG'); - my($w, $h) = ($im->Get('width'), $im->Get('height')); - my($ow, $oh) = ($w, $h); - if($w > $_[HEAP]{cvsize}[0] || $h > $_[HEAP]{cvsize}[1]) { - if($w/$h > $_[HEAP]{cvsize}[0]/$_[HEAP]{cvsize}[1]) { # width is the limiting factor - $h *= $_[HEAP]{cvsize}[0]/$w; - $w = $_[HEAP]{cvsize}[0]; - } else { - $w *= $_[HEAP]{cvsize}[1]/$h; - $h = $_[HEAP]{cvsize}[1]; - } - $im->Thumbnail(width => $w, height => $h); - } + my($old, $new) = do_resize($im, $_[HEAP]{cvsize}); $im->Set(quality => 80); $im->Write($img); - undef $im; - my $md = Image::MetaData::JPEG->new($img); - $md->drop_segments('METADATA'); - $md->save($img); + $_[KERNEL]->post(pg => do => 'UPDATE vn_rev SET image = image*-1 WHERE image = ?', [ -1*$id ]); + $_[KERNEL]->call(core => log => 'Processed cover image %d in %.2fs: %.2fkB (%dx%d) -> %.2fkB (%dx%d)', + $id, time-$start, $os/1024, $$old[0], $$old[1], (-s $img)/1024, $$new[0], $$new[1]); - $_[KERNEL]->call(core => log => 2, 'Processed cover image %d in %.2fs: %.2fkB (%dx%d) -> %.2fkB (%dx%d)', - $_[ARG0], time-$start, $os/1024, $ow, $oh, (-s $img)/1024, $w, $h); - $_[KERNEL]->yield(cv_update => $_[ARG0]); + $_[KERNEL]->yield('cv_check'); } -sub cv_update { # id - if($Multi::SQL->do('UPDATE vn_rev SET image = ? WHERE image = ?', undef, $_[ARG0], -1*$_[ARG0]) > 0) { - $_[KERNEL]->yield(cv_finish => $_[ARG0]); - } else { - $_[KERNEL]->call(core => log => 1, 'Image %d not present in the database!', $_[ARG0]); - $_[KERNEL]->yield(cv_finish => $_[ARG0]); - } -} - - -sub cv_finish { # [id] - if($_[ARG0]) { - $_[HEAP]{todo} = [ grep $_[ARG0]!=$_, @{$_[HEAP]{todo}} ]; - return $_[KERNEL]->yield(cv_process => $_[HEAP]{todo}[0]) - if @{$_[HEAP]{todo}}; - } - - $_[KERNEL]->post(core => finish => $_[HEAP]{curcmd}); - delete @{$_[HEAP]}{qw| curcmd todo |}; +sub scr_check { + $_[KERNEL]->delay('scr_check'); + $_[KERNEL]->post(pg => query => 'SELECT id FROM screenshots WHERE processed = false LIMIT 1', undef, 'scr_process'); } +sub scr_process { # num, res + return $_[KERNEL]->delay(scr_check => $_[HEAP]{check_delay}) if $_[ARG0] == 0; - -sub cmd_screenshot { - my($cmd, $id) = @_[ARG0, ARG1]; - $_[HEAP]{curcmd} = $_[ARG0]; - $_[HEAP]{id} = $_[ARG1]; - - if(!$id) { - my $q = $Multi::SQL->prepare('SELECT id FROM screenshots WHERE status = 0'); - $q->execute(); - $_[HEAP]{todo} = [ map { $_->[0]} @{$q->fetchall_arrayref([])} ]; - if(!@{$_[HEAP]{todo}}) { - $_[KERNEL]->call(core => log => 2, 'No screenshots to process'); - $_[KERNEL]->yield('scr_finish'); - return; - } - - } elsif($id eq 'clean') { - return $_[KERNEL]->yield('scr_clean'); - - } elsif($id eq 'all') { - my $q = $Multi::SQL->prepare('SELECT DISTINCT scr FROM vn_screenshots'); - $q->execute(); - $_[HEAP]{todo} = [ map $_->[0], @{$q->fetchall_arrayref([])} ]; - - } else { - $_[HEAP]{todo} = [ $_[ARG1] ]; - } - - $_[KERNEL]->yield(scr_process => $_[HEAP]{todo}[0]); -} - - -sub scr_process { # id + my $id = $_[ARG1][0]{id}; my $start = time; - - my $sf = sprintf '%s/%02d/%d.jpg', $_[HEAP]{sfpath}, $_[ARG0]%100, $_[ARG0]; - my $st = sprintf '%s/%02d/%d.jpg', $_[HEAP]{stpath}, $_[ARG0]%100, $_[ARG0]; - - # convert/compress full-size image + my $sf = sprintf '%s/%02d/%d.jpg', $_[HEAP]{sfpath}, $id%100, $id; + my $st = sprintf '%s/%02d/%d.jpg', $_[HEAP]{stpath}, $id%100, $id; my $os = -s $sf; + + # convert/compress full-size image my $im = Image::Magick->new; $im->Read($sf); $im->Set(magick => 'JPEG'); $im->Set(quality => 90); $im->Write($sf); - # create thumbnail - my($w, $h) = ($im->Get('width'), $im->Get('height')); - my($ow, $oh) = ($w, $h); - if($w/$h > $_[HEAP]{scrsize}[0]/$_[HEAP]{scrsize}[1]) { # width is the limiting factor - $h *= $_[HEAP]{scrsize}[0]/$w; - $w = $_[HEAP]{scrsize}[0]; - } else { - $w *= $_[HEAP]{scrsize}[1]/$h; - $h = $_[HEAP]{scrsize}[1]; - } - $im->Thumbnail(width => $w, height => $h); + # create thumbnail + my($old, $new) = do_resize($im, $_[HEAP]{scrsize}); $im->Set(quality => 90); $im->Write($st); - undef $im; - - # remove metadata in both files - my $md = Image::MetaData::JPEG->new($sf); - $md->drop_segments('METADATA'); - $md->save($sf); - $md = Image::MetaData::JPEG->new($st); - $md->drop_segments('METADATA'); - $md->save($st); - undef $md; - - $_[KERNEL]->call(core => log => 2, 'Processed screenshot #%d in %.2fs: %.1fkB -> %.1fkB (%dx%d), thumb: %.1fkB (%dx%d)', - $_[ARG0], time-$start, $os/1024, (-s $sf)/1024, $ow, $oh, (-s $st)/1024, $w, $h); - $_[KERNEL]->yield(scr_update => $_[ARG0], $ow, $oh); -} + $_[KERNEL]->post(pg => do => + 'UPDATE screenshots SET processed = true, width = ?, height = ? WHERE id = ?', + [ $$old[0], $$old[1], $id ] + ); + $_[KERNEL]->call(core => log => + 'Processed screenshot #%d in %.2fs: %.1fkB -> %.1fkB (%dx%d), thumb: %.1fkB (%dx%d)', + $id, time-$start, $os/1024, (-s $sf)/1024, $$old[0], $$old[1], (-s $st)/1024, $$new[0], $$new[1] + ); -sub scr_update { # id, width, height - if($Multi::SQL->do('UPDATE screenshots SET status = 1, width = ?, height = ? WHERE id = ?', undef, $_[ARG1], $_[ARG2], $_[ARG0]) > 0) { - $_[KERNEL]->yield(scr_finish => $_[ARG0]); - } else { - $_[KERNEL]->call(core => log => 1, 'Screenshot %d not present in the database!', $_[ARG0]); - $_[KERNEL]->yield(scr_finish => $_[ARG0]); - } + $_[KERNEL]->yield('scr_check'); } -sub scr_clean { - my $sql = ' FROM screenshots s WHERE NOT EXISTS(SELECT 1 FROM vn_screenshots vs WHERE vs.scr = s.id)'; - my $q = $Multi::SQL->prepare('SELECT s.id'.$sql); - $q->execute(); - - my($bytes, $items, $id) = (0, 0, 0); - while(($id) = $q->fetchrow_array) { - my $f = sprintf '%s/%02d/%d.jpg', $_[HEAP]{sfpath}, $id%100, $id; - my $t = sprintf '%s/%02d/%d.jpg', $_[HEAP]{stpath}, $id%100, $id; - $bytes += -s $f; - $bytes += -s $t; - $items++; - unlink $f; - unlink $t; - $_[KERNEL]->call(core => log => 3, 'Removing screenshot #%d', $id); - } - - $Multi::SQL->do('DELETE'.$sql); - $_[KERNEL]->call(core => log => 2, 'Removed %d unused screenshots, total of %.2fMB freed.', - $items, $bytes/1024/1024) if $items; - $_[KERNEL]->call(core => log => 2, 'No unused screenshots found') if !$items; - $_[KERNEL]->yield('scr_finish'); -} +# non-POE helper function +sub do_resize { # im, [ maxwidth, maxheight ] + my($im, $dim) = @_; -sub scr_finish { # [id] - if($_[ARG0]) { - $_[HEAP]{todo} = [ grep $_!=$_[ARG0], @{$_[HEAP]{todo}} ]; - return $_[KERNEL]->yield(scr_process => $_[HEAP]{todo}[0]) - if @{$_[HEAP]{todo}}; + my($w, $h) = ($im->Get('width'), $im->Get('height')); + $dim = [ $w, $h ] if !$dim; + my($ow, $oh) = ($w, $h); + if($w > $$dim[0] || $h > $$dim[1]) { + if($w/$h > $$dim[0]/$$dim[1]) { # width is the limiting factor + $h *= $$dim[0]/$w; + $w = $$dim[0]; + } else { + $w *= $$dim[1]/$h; + $h = $$dim[1]; + } } + $im->Thumbnail(width => $w, height => $h); - $_[KERNEL]->post(core => finish => $_[HEAP]{curcmd}); - delete @{$_[HEAP]}{qw| curcmd todo |}; + return ([$ow, $oh], [$w, $h]); } - 1; diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index d50f3ce9..9e268d68 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -3,58 +3,128 @@ # Multi::Maintenance - General maintenance functions # +# TODO: more logging? + package Multi::Maintenance; use strict; use warnings; use POE; use PerlIO::gzip; -use Time::HiRes 'gettimeofday', 'tv_interval'; sub spawn { - # WARNING: these maintenance tasks can block the process for a few seconds - my $p = shift; POE::Session->create( package_states => [ - $p => [qw| _start cmd_maintenance vncache usercache statscache revcache integrity unkanime logrotate vnpopularity tagcache |], + $p => [qw| + _start shutdown set_daily daily set_monthly monthly + vncache tagcache vnpopularity + usercache statscache revcache logrotate + |], ], + heap => { + daily => [qw|vncache tagcache vnpopularity|], + monthly => [qw|usercache statscache revcache logrotate|], + @_, + }, ); } sub _start { $_[KERNEL]->alias_set('maintenance'); - $_[KERNEL]->call(core => register => qr/^maintenance((?: (?:vncache|revcache|usercache|statscache|integrity|unkanime|logrotate|vnpopularity|tagcache))+)$/, 'cmd_maintenance'); - - # recalculate tag<->vn cache each hour (better do this once every 24 hours when the DB grows) - $_[KERNEL]->post(core => addcron => '0 * * * *', 'maintenance tagcache'); - # Perform some maintenance functions every day on 0:00 - $_[KERNEL]->post(core => addcron => '0 0 * * *', 'maintenance vncache integrity unkanime vnpopularity'); - # update caches and rotate logs every 1st day of the month at 0:05 - $_[KERNEL]->post(core => addcron => '5 0 1 * *' => 'maintenance usercache statscache revcache logrotate'); + $_[KERNEL]->sig(shutdown => 'shutdown'); + $_[KERNEL]->yield('set_daily'); + $_[KERNEL]->yield('set_monthly'); +} + + +sub shutdown { + $_[KERNEL]->delay('daily'); + $_[KERNEL]->delay('monthly'); + $_[KERNEL]->alias_remove('maintenance'); +} + + +sub set_daily { + # run daily each day at 0:00 GMT + # (GMT because we're calculating on the UNIX timestamp, I can easily add an + # offset if necessary, but it doesn't really matter what time this cron + # runs, as long as it's run on a daily basis) + $_[KERNEL]->alarm(daily => int(time/86400+1)*86400); +} + + +sub daily { + $_[KERNEL]->call(core => log => 'Running daily cron: %s', join ', ', @{$_[HEAP]{daily}}); + + # dispatch events that need to be run on a daily basis + $_[KERNEL]->call($_[SESSION], $_) for (@{$_[HEAP]{daily}}); + + # re-activate timer + $_[KERNEL]->call($_[SESSION], 'set_daily'); } -sub cmd_maintenance { - $_[KERNEL]->yield($_) - for (split /\s+/, $_[ARG1]); +sub set_monthly { + # Calculate the UNIX timestamp of 0:00 GMT of the first day of the next month. + # We do this by simply incrementing the timestamp with one day and checking gmtime() + # for a month change. This might not be very reliable, but should be enough for + # our purposes. + my $nextday = int(time/86400+1)*86400; + my $thismonth = (gmtime)[5]*100+(gmtime)[4]; # year*100 + month, for easy comparing + $nextday += 86400 while (gmtime $nextday)[5]*100+(gmtime $nextday)[4] <= $thismonth; + $_[KERNEL]->alarm(monthly => $nextday); +} + + +sub monthly { + $_[KERNEL]->call(core => log => 'Running monthly cron: %s', join ', ', @{$_[HEAP]{monthly}}); + + # dispatch events that need to be run on a monthly basis + $_[KERNEL]->call($_[SESSION], $_) for (@{$_[HEAP]{monthly}}); - $_[KERNEL]->post(core => finish => $_[ARG0]); + # re-activate timer + $_[KERNEL]->call($_[SESSION], 'set_monthly'); } + +# +# D A I L Y J O B S +# + + sub vncache { - $_[KERNEL]->call(core => log => 3 => 'Updating c_* columns in the vn table...'); - # takes ~5 seconds, better do this in the background... - $Multi::SQL->do('SELECT update_vncache(0)'); + # this takes about 30s to complete. We really need to search for an alternative + # method of keeping the c_* columns in the vn table up-to-date. + $_[KERNEL]->post(pg => do => 'SELECT update_vncache(0)'); +} + + +sub tagcache { + # this still takes "only" about 3 seconds max. Let's hope that doesn't increase too much. + $_[KERNEL]->post(pg => do => 'SELECT tag_vn_calc()'); } +sub vnpopularity { + # still takes at most 2 seconds. Againt, let's hope that doesn't increase... + $_[KERNEL]->post(pg => do => 'SELECT update_vnpopularity()'); +} + + + +# +# M O N T H L Y J O B S +# + + sub usercache { - $_[KERNEL]->call(core => log => 3 => 'Updating c_* columns in the users table...'); - $Multi::SQL->do(q|UPDATE users SET + # Shouldn't really be necessary, except c_changes could be slightly off when hiding/unhiding DB items + # Currently takes about 25 seconds to complete. + $_[KERNEL]->post(pg => do => q|UPDATE users SET c_votes = COALESCE( (SELECT COUNT(vid) FROM votes @@ -78,8 +148,9 @@ sub usercache { sub statscache { - $_[KERNEL]->call(core => log => 3 => 'Updating the stats_cache table...'); - $Multi::SQL->do($_) for( + # Shouldn't really be necessary, the triggers in PgSQL should keep these up-to-date nicely. + # But it takes less than 100ms to complete, anyway + $_[KERNEL]->post(pg => do => $_) for( q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM users)-1 WHERE section = 'users'|, q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM vn WHERE hidden = FALSE) WHERE section = 'vn'|, q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM releases WHERE hidden = FALSE) WHERE section = 'releases'|, @@ -92,66 +163,9 @@ sub statscache { sub revcache { - $_[KERNEL]->call(core => log => 3 => 'Updating rev column in the changes table...'); - # this can take a while, maybe split these up in 3 queries? - # ...or better yet, use asynchronous communication with PgSQL - $Multi::SQL->do(q|SELECT update_rev('vn', ''), update_rev('releases', ''), update_rev('producers', '')|); -} - - -sub integrity { - # checks for database inconsistencies not handled by the foreign key constraints: - # - releases without a VN relation - # - changes without an entry in the (vn|releases|producers)_rev table - # - threads without a tag or post - - my $q = $Multi::SQL->prepare(q| - SELECT 'r', id FROM releases_rev rr - WHERE NOT EXISTS(SELECT 1 FROM releases_vn rv WHERE rr.id = rv.rid) - UNION - SELECT c.type::varchar, id FROM changes c - WHERE (c.type = 0 AND NOT EXISTS(SELECT 1 FROM vn_rev vr WHERE vr.id = c.id)) - OR (c.type = 1 AND NOT EXISTS(SELECT 1 FROM releases_rev rr WHERE rr.id = c.id)) - OR (c.type = 2 AND NOT EXISTS(SELECT 1 FROM producers_rev pr WHERE pr.id = c.id)) - UNION - SELECT 't', id FROM threads t - WHERE NOT EXISTS(SELECT 1 FROM threads_posts tp WHERE tp.tid = t.id) - OR NOT EXISTS(SELECT 1 FROM threads_boards tb WHERE tb.tid = t.id)|); - $q->execute(); - my $r = $q->fetchall_arrayref([]); - if(@$r) { - $_[KERNEL]->call(core => log => 1, '!DATABASE INCONSISTENCIES FOUND!: %s', - join(', ', map { $_->[0].':'.$_->[1] } @$r)); - } else { - $_[KERNEL]->call(core => log => 3, 'No database inconsistencies found'); - } -} - - -sub unkanime { - # warn for VNs with a non-existing anidb id - # (maybe do an automated edit or something in the future) - - my $q = $Multi::SQL->prepare(q| - SELECT v.id, va.aid - FROM vn_anime va - JOIN vn v ON va.vid = v.latest - JOIN anime a ON va.aid = a.id - WHERE a.lastfetch < 0|); - $q->execute(); - my $r = $q->fetchall_arrayref([]); - my %aid = map { - my $a=$_; - $a->[1] => join(',', map { $a->[1] == $_->[1] ? $_->[0] : () } @$r) - } @$r; - - if(keys %aid) { - $_[KERNEL]->call(core => log => 1, '!NON-EXISTING RELATED ANIME FOUND!: %s', - join('; ', map { 'a'.$_.':v'.$aid{$_} } keys %aid) - ); - } else { - $_[KERNEL]->call(core => log => 3, 'No problems found with the related anime'); - } + # This -really- shouldn't be necessary... + # Currently takes about 25 seconds to complete + $_[KERNEL]->post(pg => do => q|SELECT update_rev('vn', ''), update_rev('releases', ''), update_rev('producers', '')|); } @@ -164,7 +178,7 @@ sub logrotate { my $f = /([^\/]+)$/ ? $1 : $_; my $n = sprintf '%s/%s.%04d-%02d-%02d.gz', $dir, $f, (localtime)[5]+1900, (localtime)[4]+1, (localtime)[3]; if(-f $n) { - $_[KERNEL]->call(core => log => 1, 'Logs already rotated earlier today!'); + $_[KERNEL]->call(core => log => 'Logs already rotated earlier today!'); return; } open my $I, '<', sprintf '%s/%s', $VNDB::M{log_dir}, $f; @@ -175,23 +189,9 @@ sub logrotate { open $I, '>', sprintf '%s/%s', $VNDB::M{log_dir}, $f; close $I; } -} - - -sub vnpopularity { - my $S = [gettimeofday]; - $Multi::SQL->do(q|SELECT update_vnpopularity()|); - $_[KERNEL]->call(core => log => 3 => '(Re)calculated vn.c_popularity in %.2fs', tv_interval($S)); -} - - -sub tagcache { - my $S = [gettimeofday]; - $Multi::SQL->do(q|SELECT tag_vn_calc()|); - $_[KERNEL]->call(core => log => 3 => '(Re)calculated tags_vn_stored in %.2fs', tv_interval($S)); + $_[KERNEL]->call(core => log => 'Logs rotated.'); } 1; - diff --git a/lib/Multi/RG.pm b/lib/Multi/RG.pm index d16d63b5..e427caf2 100644 --- a/lib/Multi/RG.pm +++ b/lib/Multi/RG.pm @@ -9,6 +9,7 @@ use strict; use warnings; use POE 'Wheel::Run', 'Filter::Stream'; use Encode 'encode_utf8'; +use Time::HiRes 'time'; sub spawn { @@ -16,8 +17,7 @@ sub spawn { POE::Session->create( package_states => [ $p => [qw| - _start cmd_relgraph - creategraph getrel builddot buildgraph savegraph completegraph + _start shutdown check_rg creategraph getrel builddot buildgraph savegraph proc_stdin proc_stdout proc_stderr proc_closed proc_child |], ], @@ -27,6 +27,7 @@ sub spawn { imgdir => '/www/vndb/static/rg', moy => [qw| Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec |], dot => '/usr/bin/dot', + check_delay => 3600, @_, } ); @@ -36,94 +37,71 @@ sub spawn { sub _start { $_[KERNEL]->alias_set('rg'); $_[KERNEL]->sig(CHLD => 'proc_child'); - $_[KERNEL]->call(core => register => qr/^relgraph ((?:[0-9]+)(?:\s+[0-9]+)*|all)$/, 'cmd_relgraph'); - - # regenerate all relation graphs once a month - $_[KERNEL]->post(core => addcron => '0 3 1 * *', 'relgraph all'); + $_[KERNEL]->sig(shutdown => 'shutdown'); + $_[KERNEL]->post(pg => listen => relgraph => 'check_rg'); + $_[KERNEL]->yield('check_rg'); } -sub cmd_relgraph { - $_[HEAP]{curcmd} = $_[ARG0]; +sub shutdown { + $_[KERNEL]->delay('check_rg'); + $_[KERNEL]->post(pg => unlisten => 'relgraph'); + $_[KERNEL]->alias_remove('rg'); +} - # determine vns to generate graphs for - if($_[ARG1] ne 'all') { - $_[HEAP]{todo} = [ split /\s/, $_[ARG1] ]; - } else { - my $q = $Multi::SQL->prepare('SELECT id FROM vn WHERE hidden = FALSE'); - $q->execute; - $_[HEAP]{todo} = [ map { $_->[0] } @{$q->fetchall_arrayref([])} ]; - } - # generate first graph - $_[KERNEL]->yield(creategraph => $_[HEAP]{todo}[0]); +sub check_rg { + return if $_[HEAP]{vid}; + $_[KERNEL]->call(pg => query => + 'SELECT v.id FROM vn v JOIN vn_relations vr ON vr.vid1 = v.latest WHERE rgraph IS NULL AND hidden = FALSE LIMIT 1', + undef, 'creategraph'); } -sub creategraph { # id - # Function order: - # creategraph (inits vars and initates getrel) - # getrel (recursive - fetches relation and vn data) - # if !rels - # completegraph (checks for other vids in the queue, exits otherwise) - # else - # builddot (creates input for graphviz) - # buildgraph (fetches graph ID and calls grapviz) - # savegraph (writes cmap, chmods files, updates database entries) - # completegraph - - $_[KERNEL]->call(core => log => 3, 'Processing graph for v%d', $_[ARG0]); - - $_[HEAP]{rels} = {}; # relations (key=vid1-vid2, value=relation) - $_[HEAP]{nodes} = {}; # nodes (key=vid, value=[ vid, title, date, lang, processed ]) - $_[HEAP]{vid} = $_[ARG0]; - $_[KERNEL]->yield(getrel => $_[ARG0]); +sub creategraph { # num, res + return $_[KERNEL]->delay('check_rg', $_[HEAP]{check_delay}) if $_[ARG0] == 0; + + $_[HEAP]{start} = time; + $_[HEAP]{vid} = $_[ARG1][0]{id}; + $_[HEAP]{rels} = {}; # relations (key=vid1-vid2, value=relation) + $_[HEAP]{nodes} = {}; # nodes (key=vid, value= 0:found, 1:processed) + + $_[KERNEL]->post(pg => query => + 'SELECT vid2 AS id, relation FROM vn v JOIN vn_relations vr ON vr.vid1 = v.latest WHERE v.id = ?', + [ $_[HEAP]{vid} ], 'getrel', $_[HEAP]{vid}); } -sub getrel { # vid - $_[KERNEL]->call(core => log => 3, 'Fetching relations for v%d', $_[ARG0]); +sub getrel { # num, res, vid + my $id = $_[ARG2]; + $_[HEAP]{nodes}{$id} = 1; - my $s = $Multi::SQL->prepare(q| - SELECT vr1.vid AS vid1, r.vid2, r.relation, vr1.title AS title1, vr2.title AS title2, - v1.c_released AS date1, v2.c_released AS date2, v1.c_languages AS lang1, v2.c_languages AS lang2 - FROM vn_relations r - JOIN vn_rev vr1 ON r.vid1 = vr1.id - JOIN vn v1 ON v1.latest = vr1.id - JOIN vn v2 ON r.vid2 = v2.id - JOIN vn_rev vr2 ON v2.latest = vr2.id - WHERE (r.vid2 = ? OR vr1.vid = ?)| - ); - $s->execute($_[ARG0], $_[ARG0]); - while(my $r = $s->fetchrow_hashref) { - $_[HEAP]{rels}{$r->{vid1}.'-'.$r->{vid2}} = reverserel($r->{relation}) if $r->{vid1} < $r->{vid2}; - $_[HEAP]{rels}{$r->{vid2}.'-'.$r->{vid1}} = $r->{relation} if $r->{vid1} > $r->{vid2}; - - for (1,2) { - my($vid, $title, $date, $lang) = @$r{ "vid$_", "title$_", "date$_", "lang$_" }; - if(!$_[HEAP]{nodes}{$vid}) { - $_[HEAP]{nodes}{$vid} = [ $vid, $title, $date, $lang, 0 ]; - $_[KERNEL]->yield(getrel => $vid) if $vid != $_[ARG0]; - } + for($_[ARG0] > 0 ? @{$_[ARG1]} : ()) { + $_[HEAP]{rels}{$id.'-'.$_->{id}} = reverserel($_->{relation}) if $id < $_->{id}; + $_[HEAP]{rels}{$_->{id}.'-'.$id} = $_->{relation} if $id > $_->{id}; + + if(!exists $_[HEAP]{nodes}{$_->{id}}) { + $_[HEAP]{nodes}{$_->{id}} = 0; + $_[KERNEL]->post(pg => query => + 'SELECT vid2 AS id, relation FROM vn v JOIN vn_relations vr ON vr.vid1 = v.latest WHERE v.id = ?', + [ $_->{id} ], 'getrel', $_->{id}); } - $_[HEAP]{nodes}{$_[ARG0]}[4]++; } - if(!grep !$_->[4], values %{$_[HEAP]{nodes}}) { - if(!keys %{$_[HEAP]{nodes}}) { - $_[KERNEL]->call(core => log => 3, 'No relation graph for v%d', $_[HEAP]{vid}); - $Multi::SQL->do('UPDATE vn SET rgraph = NULL WHERE id = ?', undef, $_[HEAP]{vid}); - $_[HEAP]{nodes}{$_[HEAP]{vid}} = []; - $_[KERNEL]->yield('completegraph'); - return; - } - $_[KERNEL]->call(core => log => 3, 'Fetched all relation data'); - $_[KERNEL]->yield('builddot') + # do we have all relations now? get VN info + if(!grep !$_, values %{$_[HEAP]{nodes}}) { + $_[KERNEL]->post(pg => query => + 'SELECT v.id, vr.title, v.c_released AS date, v.c_languages AS lang + FROM vn v JOIN vn_rev vr ON vr.id = v.latest + WHERE v.id IN('.join(', ', map '?', keys %{$_[HEAP]{nodes}}).')', + [ keys %{$_[HEAP]{nodes}} ], 'builddot'); } } -sub builddot { +sub builddot { # num, res + my $vns = $_[ARG1]; + my $gv = qq|graph rgraph {\n|. qq|\tratio = "compress"\n|. @@ -133,18 +111,18 @@ sub builddot { qq|\tedge [ labeldistance = 2.5, labelangle = -20, labeljust = 1, minlen = 2, dir = "both",|. qq| fontname = $_[HEAP]{font}, fontsize = $_[HEAP]{fsize}[1], arrowsize = 0.7, color = "#225588", fontcolor = "#cccccc" ]\n|; - # insert all nodes, ordered by release date - for (sort { $a->[2] <=> $b->[2] } values %{$_[HEAP]{nodes}}) { - my $date = sprintf '%08d', $_->[2]; + # insert all nodes, ordered by release date + for (sort { $a->{date} <=> $b->{date} } @$vns) { + my $date = sprintf '%08d', $_->{date}; $date =~ s#^([0-9]{4})([0-9]{2}).+#$1==0?'N/A':$1==9999?'TBA':(($2&&$2<13?($_[HEAP]{moy}[$2-1].' '):'').$1)#e; - my $title = $_->[1]; + my $title = $_->{title}; $title = substr($title, 0, 27).'...' if length($title) > 30; $title =~ s/&/&/g; $title =~ s/>/>/g; $title =~ s/</</g; - my $tooltip = $_->[1]; + my $tooltip = $_->{title}; $tooltip =~ s/\\/\\\\/g; $tooltip =~ s/"/\\"/g; @@ -154,99 +132,85 @@ sub builddot { q|<TR><TD COLSPAN="2" ALIGN="CENTER" CELLPADDING="2"><FONT POINT-SIZE="%d"> %s </FONT></TD></TR>|. q|<TR><TD> %s </TD><TD> %s </TD></TR>|. qq|</TABLE>> ]\n|, - $_->[0], $_->[0], encode_utf8($tooltip), $_[HEAP]{fsize}[2], encode_utf8($title), $date, $_->[3]||'N/A'; + $_->{id}, $_->{id}, encode_utf8($tooltip), $_[HEAP]{fsize}[2], encode_utf8($title), $date, $_->{lang}||'N/A'; } - # @rels = ([ vid1, vid2, relation, date1, date2 ], ..), for easier processing + # @rels = ([ vid1, vid2, relation, date1, date2 ], ..), for easier processing my @rels = map { /^([0-9]+)-([0-9]+)$/; - [ $1, $2, $_[HEAP]{rels}{$_}, $_[HEAP]{nodes}{$1}[2], $_[HEAP]{nodes}{$2}[2] ] + my $vn1 = (grep $1 == $_->{id}, @$vns)[0]; + my $vn2 = (grep $2 == $_->{id}, @$vns)[0]; + [ $1, $2, $_[HEAP]{rels}{$_}, $vn1->{date}, $vn2->{date} ] } keys %{$_[HEAP]{rels}}; - # insert all edges, ordered by release date again + # insert all edges, ordered by release date again for (sort { ($a->[3]>$a->[4]?$a->[4]:$a->[3]) <=> ($b->[3]>$b->[4]?$b->[4]:$b->[3]) } @rels) { - # [older game] -> [newer game] + # [older game] -> [newer game] if($_->[4] > $_->[3]) { ($_->[0], $_->[1]) = ($_->[1], $_->[0]); $_->[2] = reverserel($_->[2]); } - my $label = - $VNDB::S{vn_relations}[$_->[2]][1] ? qq|headlabel = "$VNDB::S{vn_relations}[$_->[2]][0]", taillabel = "$VNDB::S{vn_relations}[$_->[2]-1][0]"| : - $VNDB::S{vn_relations}[$_->[2]+1][1] ? qq|headlabel = "$VNDB::S{vn_relations}[$_->[2]][0]", taillabel = "$VNDB::S{vn_relations}[$_->[2]+1][0]"| - : qq|label = " $VNDB::S{vn_relations}[$_->[2]][0]"|; - + $VNDB::S{vn_relations}[$_->[2]][1] + ? qq|headlabel = "$VNDB::S{vn_relations}[$_->[2]][0]", taillabel = "$VNDB::S{vn_relations}[$_->[2]-1][0]"| : + $VNDB::S{vn_relations}[$_->[2]+1][1] + ? qq|headlabel = "$VNDB::S{vn_relations}[$_->[2]][0]", taillabel = "$VNDB::S{vn_relations}[$_->[2]+1][0]"| + : qq|label = " $VNDB::S{vn_relations}[$_->[2]][0]"|; $gv .= qq|\tv$$_[1] -- v$$_[0] [ $label ]\n|; } $gv .= "}\n"; - #print $gv; - $_[HEAP]{gv} = \$gv; - $_[KERNEL]->yield('buildgraph'); + + # get ID + $_[KERNEL]->post(pg => query => 'INSERT INTO relgraph (cmap) VALUES (\'\') RETURNING id', undef, 'buildgraph', \$gv); } -sub buildgraph { - # get a new ID - my $gid = $Multi::SQL->prepare("INSERT INTO relgraph (cmap) VALUES ('') RETURNING id"); - $gid->execute; - $gid = $gid->fetchrow_arrayref->[0]; - $_[HEAP]{gid} = [ - $gid, - sprintf('%s/%02d/%d.png', $_[HEAP]{imgdir}, $gid % 100, $gid), - ]; +sub buildgraph { # num, res, \$gv + $_[HEAP]{gid} = $_[ARG1][0]{id}; + $_[HEAP]{graph} = sprintf('%s/%02d/%d.png', $_[HEAP]{imgdir}, $_[ARG1][0]{id} % 100, $_[ARG1][0]{id}); + $_[HEAP]{cmap} = ''; # roughly equivalent to: # cat layout.txt | dot -Tpng -o graph.png -Tcmapx $_[HEAP]{proc} = POE::Wheel::Run->new( Program => $_[HEAP]{dot}, - ProgramArgs => [ '-Tpng', '-o', $_[HEAP]{gid}[1], '-Tcmapx' ], + ProgramArgs => [ '-Tpng', '-o', $_[HEAP]{graph}, '-Tcmapx' ], StdioFilter => POE::Filter::Stream->new(), StdinEvent => 'proc_stdin', StdoutEvent => 'proc_stdout', StderrEvent => 'proc_stderr', CloseEvent => 'proc_closed', ); - $_[HEAP]{proc}->put(${$_[HEAP]{gv}}); - $_[HEAP]{cmap} = ''; + $_[HEAP]{proc}->put(${$_[ARG2]}); } sub savegraph { - # save the image map - $Multi::SQL->do('UPDATE relgraph SET cmap = ? WHERE id = ?', undef, - '<!-- V:'.join(',',keys %{$_[HEAP]{nodes}})." -->\n$_[HEAP]{cmap}", $_[HEAP]{gid}[0]); + my $vids = join ',', sort map int, keys %{$_[HEAP]{nodes}}; - # proper chmod - chmod 0666, $_[HEAP]{gid}[1]; + # chmod graph + chmod 0666, $_[HEAP]{graph}; - # update the VN table - $Multi::SQL->do(sprintf q| - UPDATE vn - SET rgraph = %d - WHERE id IN(%s)|, - $_[HEAP]{gid}[0], join(',', keys %{$_[HEAP]{nodes}})); + # save the image map in the database + $_[KERNEL]->post(pg => do => 'UPDATE relgraph SET cmap = ? WHERE id = ?', + [ "<!-- V:$vids -->\n$_[HEAP]{cmap}", $_[HEAP]{gid} ]); - $_[KERNEL]->yield('completegraph'); -} + # update the VN table + $_[KERNEL]->post(pg => do => "UPDATE vn SET rgraph = ? WHERE id IN($vids)", [ $_[HEAP]{gid} ]); + # log + $_[KERNEL]->call(core => log => 'Generated relation graph in %.2fs, V: %s', time-$_[HEAP]{start}, $vids); -sub completegraph { - $_[KERNEL]->call(core => log => 3, 'Generated the relation graph for v%d', $_[HEAP]{vid}); + # clean up + delete @{$_[HEAP]}{qw| start vid nodes rels gid graph cmap proc |}; - # remove processed vns, and check for other graphs in the queue - $_[HEAP]{todo} = [ grep { !$_[HEAP]{nodes}{$_} } @{$_[HEAP]{todo}} ]; - if(@{$_[HEAP]{todo}}) { - $_[KERNEL]->yield(creategraph => $_[HEAP]{todo}[0]); - } else { - $_[KERNEL]->post(core => finish => $_[HEAP]{curcmd}); - delete @{$_[HEAP]}{qw| vid nodes rels curcmd gv todo gid cmap |}; - } + # check for more things to do + $_[KERNEL]->yield('check_rg'); } - # POE handlers for communication with GraphViz sub proc_stdin { $_[HEAP]{proc}->shutdown_stdin; @@ -255,11 +219,10 @@ sub proc_stdout { $_[HEAP]{cmap} .= $_[ARG0]; } sub proc_stderr { - $_[KERNEL]->call(core => log => 1, 'GraphViz STDERR: %s', $_[ARG0]); + $_[KERNEL]->call(core => log => 'GraphViz STDERR: %s', $_[ARG0]); } sub proc_closed { $_[KERNEL]->yield('savegraph'); - undef $_[HEAP]{proc}; } sub proc_child { 1; # do nothing, just make sure SIGCHLD is handled to reap the process @@ -267,8 +230,7 @@ sub proc_child { - -# Not a POE handler, just a small macro +# non-POE helper function sub reverserel { # relation return $VNDB::S{vn_relations}[$_[0]][1] ? $_[0]-1 : $VNDB::S{vn_relations}[$_[0]+1][1] ? $_[0]+1 : $_[0]; } diff --git a/lib/Multi/Sitemap.pm b/lib/Multi/Sitemap.pm index 5ae305c1..175b170a 100644 --- a/lib/Multi/Sitemap.pm +++ b/lib/Multi/Sitemap.pm @@ -10,17 +10,20 @@ use warnings; use POE; use XML::Writer; use PerlIO::gzip; -use DateTime; +use POSIX 'strftime'; +use Time::HiRes 'gettimeofday', 'tv_interval'; sub spawn { my $p = shift; POE::Session->create( package_states => [ - $p => [qw| _start cmd_sitemap staticpages vnpages releasepages producerpages finish addurl |], + $p => [qw| _start shutdown check_age generate addquery addurl finish |], ], heap => { output => $VNDB::ROOT.'/www/sitemap.xml.gz', + max_age => 24*3600, # seconds + check_delay => 3600, # seconds @_, } ); @@ -29,24 +32,31 @@ sub spawn { sub _start { $_[KERNEL]->alias_set('sitemap'); - $_[KERNEL]->call(core => register => qr/^sitemap$/, 'cmd_sitemap'); - - # Regenerate the sitemap every day on 0:00 - $_[KERNEL]->post(core => addcron => '0 0 * * *', 'sitemap'); + $_[KERNEL]->yield('check_age'); + $_[KERNEL]->sig(shutdown => 'shutdown'); } -sub cmd_sitemap { - # Function order: - # cmd_sitemap - # staticpages - # vnpages - # releasepages - # producerpages - # finish +sub shutdown { + $_[KERNEL]->delay('check_age'); + $_[KERNEL]->alias_remove('sitemap'); +} + + +sub check_age { + # check the last modified time of the sitemap, and if it's older than max_age, regenerate it + $_[KERNEL]->yield('generate') if !-f $_[HEAP]{output} || (stat $_[HEAP]{output})[9] < time-$_[HEAP]{max_age}; + + # check sitemap again later + $_[KERNEL]->delay(check_age => $_[HEAP]{check_delay}); +} + + +sub generate { + $_[KERNEL]->call(core => log => '(Re)generating sitemap'); - $_[HEAP]{cmd} = $_[ARG0]; $_[HEAP]{urls} = 0; + $_[HEAP]{start} = [ gettimeofday ]; open($_[HEAP]{io}, '>:gzip', $_[HEAP]{output}) || die $1; $_[HEAP]{xml} = new XML::Writer( @@ -56,76 +66,63 @@ sub cmd_sitemap { DATA_INDENT => 1 ); $_[HEAP]{xml}->xmlDecl(); - $_[HEAP]{xml}->comment(q|NOTE: All URL's that require you to login or that may contain usernames are left out.|); $_[HEAP]{xml}->startTag('urlset', xmlns => 'http://www.sitemaps.org/schemas/sitemap/0.9'); - $_[KERNEL]->yield('staticpages'); -} - - -sub staticpages { - $_[KERNEL]->call(core => log => 3, 'Adding static pages'); - + # / $_[KERNEL]->call(sitemap => addurl => '', 'daily'); + # /d+ /([0-9]+)$/ && $_[KERNEL]->call(sitemap => addurl => 'd'.$1, 'monthly', [stat $_]->[9]) for (glob "$VNDB::ROOT/data/docs/*"); + # /v/[browse] & /p/[browse] $_[KERNEL]->call(sitemap => addurl => $_, 'weekly') for (map { 'v/'.$_, 'p/'.$_ } 'a'..'z', 0, 'all'); - $_[KERNEL]->yield('vnpages'); -} - - -sub vnpages { - $_[KERNEL]->call(core => log => 3, 'Adding visual novel pages'); - - my $q = $Multi::SQL->prepare(q| - SELECT v.id, c.added, v.rgraph - FROM vn v - JOIN vn_rev vr ON vr.id = v.latest - JOIN changes c ON vr.id = c.id - |); - $q->execute; - $_[KERNEL]->call(sitemap => addurl => 'v'.$_->[0], 'weekly', $_->[1], 0.7) - while(local $_ = $q->fetchrow_arrayref); - - $_[KERNEL]->yield('releasepages'); -} - - -sub releasepages { - $_[KERNEL]->call(core => log => 3, 'Adding release pages'); - - my $q = $Multi::SQL->prepare(q| + # /v+ + $_[KERNEL]->post(pg => query => ' + SELECT v.id, c.added + FROM vn v + JOIN vn_rev vr ON vr.id = v.latest + JOIN changes c ON vr.id = c.id + WHERE v.hidden = FALSE + ORDER BY v.id', + undef, 'addquery', [ 'v', 0.7 ]); + + # /r+ + $_[KERNEL]->post(pg => query => ' SELECT r.id, c.added - FROM releases r - JOIN releases_rev rr ON rr.id = r.latest - JOIN changes c ON c.id = rr.id - |); - $q->execute; - $_[KERNEL]->call(sitemap => addurl => 'r'.$_->[0], 'weekly', $_->[1], 0.3) - while(local $_ = $q->fetchrow_arrayref); - - $_[KERNEL]->yield('producerpages'); + FROM releases r + JOIN releases_rev rr ON rr.id = r.latest + JOIN changes c ON c.id = rr.id + WHERE r.hidden = FALSE + ORDER BY r.id', + undef, 'addquery', [ 'r', 0.5 ]); + + # /p+ + $_[KERNEL]->post(pg => query => ' + SELECT p.id, c.added + FROM producers p + JOIN producers_rev pr ON pr.id = p.latest + JOIN changes c ON c.id = pr.id + WHERE p.hidden = FALSE + ORDER BY p.id', + undef, 'addquery', [ 'p', 0.3 ]); + + # /g+ + $_[KERNEL]->post(pg => query => ' + SELECT t.id, t.added + FROM tags t + WHERE state = 2 + ORDER BY t.id', + undef, 'addquery', [ 'g', 0.3, 1 ]); } -sub producerpages { - $_[KERNEL]->call(core => log => 3, 'Adding producer pages'); - - my $q = $Multi::SQL->prepare(q| - SELECT p.id, c.added - FROM producers p - JOIN producers_rev pr ON pr.id = p.latest - JOIN changes c ON c.id = pr.id - |); - $q->execute; - $_[KERNEL]->call(sitemap => addurl => 'p'.$_->[0], 'weekly', $_->[1]) - while(local $_ = $q->fetchrow_arrayref); - - $_[KERNEL]->yield('finish'); +sub addquery { # num, db-res, [ type, priority, finish ] + $_[KERNEL]->call(sitemap => addurl => $_[ARG2][0].$_->{id}, 'weekly', $_->{added}, $_[ARG2][1]) + for(@{$_[ARG1]}); + $_[KERNEL]->yield('finish') if $_[ARG2][2]; } @@ -133,9 +130,11 @@ sub finish { $_[HEAP]{xml}->endTag('urlset'); $_[HEAP]{xml}->end(); close $_[HEAP]{io}; - $_[KERNEL]->call(core => log => 2 => 'Wrote %d URLs in the sitemap', $_[HEAP]{urls}); - $_[KERNEL]->post(core => finish => $_[HEAP]{cmd}); - delete @{$_[HEAP]}{qw| xml io cmd urls |}; + + $_[KERNEL]->call(core => log => 'Wrote %d URLs (%.1f kB gzipped) to the sitemap in %.2f seconds', + $_[HEAP]{urls}, (-s $_[HEAP]{output})/1024, tv_interval($_[HEAP]{start})); + + delete @{$_[HEAP]}{qw| xml io start urls |}; } @@ -143,7 +142,7 @@ sub addurl { # loc, changefreq, lastmod, priority $_[HEAP]{xml}->startTag('url'); $_[HEAP]{xml}->dataElement(loc => $VNDB::S{url}.'/'.$_[ARG0]); $_[HEAP]{xml}->dataElement(changefreq => $_[ARG1]) if defined $_[ARG1]; - $_[HEAP]{xml}->dataElement(lastmod => DateTime->from_epoch(epoch => $_[ARG2])->ymd) if defined $_[ARG2]; + $_[HEAP]{xml}->dataElement(lastmod => strftime('%Y-%m-%d', gmtime $_[ARG2])) if defined $_[ARG2]; $_[HEAP]{xml}->dataElement(priority => $_[ARG3]) if defined $_[ARG3]; $_[HEAP]{xml}->endTag('url'); $_[HEAP]{urls}++; diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm index d5cdcc6c..7e280999 100644 --- a/lib/VNDB/DB/Users.pm +++ b/lib/VNDB/DB/Users.pm @@ -5,7 +5,7 @@ use strict; use warnings; use Exporter 'import'; -our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel|; +our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel dbSessionAdd dbSessionDel dbSessionCheck|; # %options->{ username passwd mail order uid ip registered search results page what } @@ -24,8 +24,6 @@ sub dbUserGet { my %where = ( $o{username} ? ( 'username = ?' => $o{username} ) : (), - $o{passwd} ? ( - 'passwd = decode(?, \'hex\')' => $o{passwd} ) : (), $o{firstchar} ? ( 'SUBSTRING(username from 1 for 1) = ?' => $o{firstchar} ) : (), !$o{firstchar} && defined $o{firstchar} ? ( @@ -45,7 +43,8 @@ sub dbUserGet { ); my @select = ( - 'u.*', + qw|id username mail rank salt registered c_votes c_changes show_nsfw show_list skin customcss ip c_tags|, + q|encode(passwd, 'hex') AS passwd|, $o{what} =~ /stats/ ? ( '(SELECT COUNT(*) FROM rlists WHERE uid = u.id) AS releasecount', '(SELECT COUNT(DISTINCT rv.vid) FROM rlists rl JOIN releases r ON rl.rid = r.id JOIN releases_vn rv ON rv.rid = r.latest WHERE uid = u.id) AS vncount', @@ -75,7 +74,7 @@ sub dbUserEdit { my %h; defined $o{$_} && ($h{$_.' = ?'} = $o{$_}) - for (qw| username mail rank show_nsfw show_list skin customcss |); + for (qw| username mail rank show_nsfw show_list skin customcss salt |); $h{'passwd = decode(?, \'hex\')'} = $o{passwd} if defined $o{passwd}; @@ -88,11 +87,11 @@ sub dbUserEdit { } -# username, md5(pass), mail, [ip] +# username, pass(ecrypted), salt, mail, [ip] sub dbUserAdd { my($s, @o) = @_; - $s->dbExec(q|INSERT INTO users (username, passwd, mail, ip, registered) VALUES(?, decode(?, 'hex'), ?, ?, ?)|, - @o[0..2], $o[3]||$s->reqIP, time); + $s->dbExec(q|INSERT INTO users (username, passwd, salt, mail, ip, registered) VALUES(?, decode(?, 'hex'), ?, ?, ?, ?)|, + @o[0..3], $o[4]||$s->reqIP, time); } @@ -100,10 +99,10 @@ sub dbUserAdd { sub dbUserDel { my($s, $id) = @_; $s->dbExec($_, $id) for ( - q|DELETE FROM vnlists WHERE uid = ?|, q|DELETE FROM rlists WHERE uid = ?|, q|DELETE FROM wlists WHERE uid = ?|, q|DELETE FROM votes WHERE uid = ?|, + q|DELETE FROM sessions WHERE uid = ?|, q|UPDATE changes SET requester = 0 WHERE requester = ?|, q|UPDATE threads_posts SET uid = 0 WHERE uid = ?|, q|DELETE FROM users WHERE id = ?| @@ -111,4 +110,41 @@ sub dbUserDel { } +# Adds a session to the database +# If no expiration is supplied the database default is used +# uid, 40 character session token, expiration time (timestamp) +sub dbSessionAdd { + my($s, @o) = @_; + if (defined $o[2]) { + $s->dbExec(q|INSERT INTO sessions (uid, token, expiration) VALUES(?, decode(?, 'hex'), ?)|, + @o); + } else { + $s->dbExec(q|INSERT INTO sessions (uid, token) VALUES(?, decode(?, 'hex'))|, + @o); + } +} + + +# Deletes session(s) from the database +# If no token is supplied, all sessions for the uid are destroyed +# uid, token (optional) +sub dbSessionDel { + my($s, @o) = @_; + my %where = ('uid = ?' => $o[0]); + $where{"token = decode(?, 'hex')"} = $o[1] if $o[1]; + $s->dbExec('DELETE FROM sessions !W', \%where); +} + + +# Queries the database for the validity of a session +# Returns 1 if corresponding session found, 0 if not +# uid, token +sub dbSessionCheck { + my($s, @o) = @_; + return $s->dbRow( + q|SELECT count(uid) AS count FROM sessions WHERE uid = ? AND token = decode(?, 'hex') LIMIT 1|, @o + )->{count}||0; +} + + 1; diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index 76cacf70..254e24e1 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -214,24 +214,11 @@ sub insert_rev { $cid, $_->[1], $_->[0] ) for (@{$o->{relations}}); - if(@{$o->{anime}}) { - $self->dbExec(q| - INSERT INTO vn_anime (vid, aid) - VALUES (?, ?)|, - $cid, $_ - ) for (@{$o->{anime}}); - - # insert unknown anime - my $a = $self->dbAll(q| - SELECT id FROM anime WHERE id IN(!l)|, - $o->{anime}); - $self->dbExec(q| - INSERT INTO anime (id) VALUES (?)|, $_ - ) for (grep { - my $ia = $_; - !(scalar grep $ia == $_->{id}, @$a) - } @{$o->{anime}}); - } + $self->dbExec(q| + INSERT INTO vn_anime (vid, aid) + VALUES (?, ?)|, + $cid, $_ + ) for (@{$o->{anime}}); } @@ -251,7 +238,7 @@ sub dbVNCache { # insert a new screenshot and return it's ID # (no arguments required, as Multi is responsible for filling the entry with information) sub dbScreenshotAdd { - return shift->dbRow(q|INSERT INTO screenshots (status) VALUES(0) RETURNING id|)->{id}; + return shift->dbRow(q|INSERT INTO screenshots (processed) VALUES(false) RETURNING id|)->{id}; } diff --git a/lib/VNDB/Handler/Discussions.pm b/lib/VNDB/Handler/Discussions.pm index 56bf9e83..d8aa2f63 100644 --- a/lib/VNDB/Handler/Discussions.pm +++ b/lib/VNDB/Handler/Discussions.pm @@ -210,8 +210,6 @@ sub edit { $self->dbPostEdit($tid, $num, %post) if $num; $nnum = $self->dbPostAdd($ntid, %post) if !$num; - $self->multiCmd("ircnotify t$ntid.$nnum") if !$num && !$frm->{hidden}; - return $self->resRedirect("/t$ntid".($nnum > 25 ? '/'.ceil($nnum/25) : '').'#'.$nnum, 'post'); } } diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm index 9ff2d315..ceae11ce 100644 --- a/lib/VNDB/Handler/Misc.pm +++ b/lib/VNDB/Handler/Misc.pm @@ -4,7 +4,7 @@ package VNDB::Handler::Misc; use strict; use warnings; -use YAWF ':html'; +use YAWF ':html', ':xml'; use VNDB::Func; @@ -15,6 +15,7 @@ YAWF::register( qr{nospam}, \&nospam, qr{([vrp])([1-9]\d*)/(lock|hide)}, \&itemmod, qr{we-dont-like-ie6}, \&ie6message, + qr{opensearch\.xml}, \&opensearch, # redirects for old URLs qr{(.*[^/]+)/+}, sub { $_[0]->resRedirect("/$_[1]", 'perm') }, @@ -323,7 +324,7 @@ sub itemmod { $self->dbItemMod($type, $iid, $act eq 'hide' ? (hidden => !$obj->{hidden}) : (locked => !$obj->{locked})); # update cached vn info when hiding an r+ page - $self->vnCacheUpdate(map $_->{vid}, @{$obj->{vn}}) + $self->dbVNCache(map $_->{vid}, @{$obj->{vn}}) if $type eq 'r' && $act eq 'hide'; $self->resRedirect("/$type$iid", 'temp'); @@ -372,5 +373,24 @@ sub ie6message { } +sub opensearch { + my $self = shift; + $self->resHeader('Content-Type' => 'application/opensearchdescription+xml'); + xml; + tag 'OpenSearchDescription', + xmlns => 'http://a9.com/-/spec/opensearch/1.1/', 'xmlns:moz' => 'http://www.mozilla.org/2006/browser/search/'; + tag 'ShortName', 'VNDB'; + tag 'LongName', 'VNDB.org visual novel search'; + tag 'Description', 'Search visual vovels on VNDB.org'; + tag 'Image', width => 16, height => 16, type => 'image/x-icon', $self->{url}.'/favicon.ico' + if -s "$VNDB::ROOT/www/favicon.ico"; + tag 'Url', type => 'text/html', method => 'get', template => $self->{url}.'/v/all?q={searchTerms}', undef; + tag 'Url', type => 'application/opensearchdescription+xml', rel => 'self', template => $self->{url}.'/opensearch.xml', undef; + tag 'Query', role => 'example', searchTerms => 'Tsukihime', undef; + tag 'moz:SearchForm', $self->{url}.'/v/all'; + end; +} + + 1; diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm index 5977ac1e..aa010e6e 100644 --- a/lib/VNDB/Handler/Producers.pm +++ b/lib/VNDB/Handler/Producers.pm @@ -121,8 +121,6 @@ sub edit { ($pid) = $self->dbProducerAdd(%$frm); } - $self->multiCmd("ircnotify p$pid.$rev"); - return $self->resRedirect("/p$pid.$rev", 'post'); } } diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm index cc3f56bc..db234aea 100644 --- a/lib/VNDB/Handler/Releases.pm +++ b/lib/VNDB/Handler/Releases.pm @@ -354,8 +354,7 @@ sub edit { ($rev) = $self->dbReleaseEdit($rid, %opts) if !$copy && $rid; ($rid) = $self->dbReleaseAdd(%opts) if $copy || !$rid; - $self->multiCmd("ircnotify r$rid.$rev"); - $self->vnCacheUpdate(@$new_vn, map $_->{vid}, @$vn); + $self->dbVNCache(@$new_vn, map $_->{vid}, @$vn); return $self->resRedirect("/r$rid.$rev", 'post'); } diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm index 6471d44f..dfcd041d 100644 --- a/lib/VNDB/Handler/Tags.pm +++ b/lib/VNDB/Handler/Tags.pm @@ -279,7 +279,6 @@ sub tagedit { ); if(!$tag) { $tag = $self->dbTagAdd(%opts); - $self->multiCmd("ircnotify g$tag"); } else { $self->dbTagEdit($tag, %opts, upddate => $frm->{state} == 2 && $t->{state} != 2); } diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm index d4755ae1..982d6089 100644 --- a/lib/VNDB/Handler/Users.pm +++ b/lib/VNDB/Handler/Users.pm @@ -4,7 +4,6 @@ package VNDB::Handler::Users; use strict; use warnings; use YAWF ':html'; -use Digest::MD5 'md5_hex'; use VNDB::Func; @@ -183,7 +182,9 @@ sub newpass { if(!$frm->{_err}) { my @chars = ( 'A'..'Z', 'a'..'z', 0..9 ); my $pass = join '', map $chars[int rand $#chars+1], 0..8; - $self->dbUserEdit($u->{id}, passwd => md5_hex($pass)); + my %o; + ($o{passwd}, $o{salt}) = $self->authPreparePass($pass); + $self->dbUserEdit($u->{id}, %o); my $body = <<'__'; Hello %s, @@ -258,7 +259,8 @@ sub register { push @{$frm->{_err}}, 'oneaday' if !$frm->{_err} && $self->dbUserGet(ip => $self->reqIP, registered => time-24*3600)->[0]{id}; if(!$frm->{_err}) { - $self->dbUserAdd($frm->{usrname}, md5_hex($frm->{usrpass}), $frm->{mail}); + my ($pass, $salt) = $self->authPreparePass($frm->{usrpass}); + $self->dbUserAdd($frm->{usrname}, $pass, $salt, $frm->{mail}); return $self->authLogin($frm->{usrname}, $frm->{usrpass}, '/'); } } @@ -330,10 +332,11 @@ sub edit { $o{mail} = $frm->{mail}; $o{skin} = $frm->{skin}; $o{customcss} = $frm->{customcss}; - $o{passwd} = md5_hex($frm->{usrpass}) if $frm->{usrpass}; + ($o{passwd}, $o{salt}) = $self->authPreparePass($frm->{usrpass}) if $frm->{usrpass}; $o{show_list} = $frm->{flags_list} ? 1 : 0; $o{show_nsfw} = $frm->{flags_nsfw} ? 1 : 0; $self->dbUserEdit($uid, %o); + $self->dbSessionDel($uid) if $frm->{usrpass}; return $self->resRedirect("/u$uid/edit?d=1", 'post') if $uid != $self->authInfo->{id} || !$frm->{usrpass}; return $self->authLogin($frm->{usrname}||$u->{username}, $frm->{usrpass}, "/u$uid/edit?d=1"); } diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm index f0f12e88..2b6e4ac5 100644 --- a/lib/VNDB/Handler/VNEdit.pm +++ b/lib/VNDB/Handler/VNEdit.pm @@ -87,13 +87,8 @@ sub edit { my %old = $vid ? (map { $_->{id} => $_->{relation} } @{$v->{relations}}) : (); my %new = map { $_->[1] => $_->[0] } @$relations; _updreverse($self, \%old, \%new, $nvid, $cid, $nrev); - } elsif($vid && @$relations && $frm->{title} ne $b4{title}) { - $self->multiCmd("relgraph $vid"); } - $self->multiCmd("ircnotify v$nvid.$nrev"); - $self->multiCmd('anime') if $vid && $frm->{anime} ne $b4{anime} || !$vid && $frm->{anime}; - return $self->resRedirect("/v$nvid.$nrev", 'post'); } } @@ -131,12 +126,11 @@ sub _uploadimage { return undef; } - # store the file and let multi handle it + # get image ID and move it to the correct location my $imgid = $self->dbVNImageId; my $new = sprintf '%s/static/cv/%02d/%d.jpg', $VNDB::ROOT, $imgid%100, $imgid; rename $tmp, $new or die $!; chmod 0666, $new; - $self->multiCmd("coverimage $imgid"); return -1*$imgid; } @@ -300,8 +294,6 @@ sub _updreverse { ( map { $_ => $r->{$_} } qw| title original desc alias categories img_nsfw length l_wp l_encubed l_renai l_vnn image | ) ); } - - $self->multiCmd('relgraph '.join(' ', $vid, keys %upd)); } @@ -371,7 +363,6 @@ sub scrxml { my $new = sprintf '%s/static/sf/%02d/%d.jpg', $VNDB::ROOT, $id%100, $id; rename $tmp, $new or die $!; chmod 0666, $new; - $self->multiCmd('screenshot'); } xml; diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm index 8a3087f2..ed8ad7c0 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -327,9 +327,9 @@ sub _anime { td 'Related anime'; td class => 'anime'; for (sort { ($a->{year}||9999) <=> ($b->{year}||9999) } @{$v->{anime}}) { - if($_->{lastfetch} < 1) { + if(!$_->{lastfetch} || !$_->{year} || !$_->{title_romaji}) { b; - txt $_->{lastfetch} < 0 ? '[unknown anidb id: ' : '[no information available at this time: '; + txt '[no information available at this time: '; a href => "http://anidb.net/a$_->{id}", $_->{id}; txt ']'; end; @@ -347,8 +347,8 @@ sub _anime { } txt '] '; end; - acronym title => $_->{title_kanji}, shorten $_->{title_romaji}, 50; - b ' ('.($self->{anime_types}[$_->{type}][0] eq 'unknown' ? '' : $self->{anime_types}[$_->{type}][0].', ').$_->{year}.')'; + acronym title => $_->{title_kanji}||$_->{title_romaji}, shorten $_->{title_romaji}, 50; + b ' ('.(defined $_->{type} ? $self->{anime_types}[$_->{type}].', ' : '').$_->{year}.')'; txt "\n"; } } diff --git a/lib/VNDB/Util/Auth.pm b/lib/VNDB/Util/Auth.pm index cb0751e9..b9724964 100644 --- a/lib/VNDB/Util/Auth.pm +++ b/lib/VNDB/Util/Auth.pm @@ -1,19 +1,18 @@ package VNDB::Util::Auth; -# This module is just a small improvement of the 1.x equivalent -# and is designed to work with the cookies and database of VNDB 1.x -# without modifications. A proper and more secure (incompatible) -# implementation should be written at some point. use strict; use warnings; use Exporter 'import'; use Digest::MD5 'md5_hex'; -use Crypt::Lite; +use Digest::SHA qw|sha1_hex sha256_hex|; +use Time::HiRes; +use Encode 'encode_utf8'; +use POSIX 'strftime'; -our @EXPORT = qw| authInit authLogin authLogout authInfo authCan |; +our @EXPORT = qw| authInit authLogin authLogout authInfo authCan authPreparePass |; # initializes authentication information and checks the vndb_auth cookie @@ -23,11 +22,11 @@ sub authInit { my $cookie = $self->reqCookie('vndb_auth'); return 0 if !$cookie; - my $str = Crypt::Lite->new()->decrypt($cookie, md5_hex($self->{cookie_key})); - return 0 if length($str) < 36; - my $pass = substr($str, 4, 32); - my $user = substr($str, 36); - _authCheck($self, $user, $pass); + return _rmcookie($self) if length($cookie) < 41; + my $token = substr($cookie, 0, 40); + my $uid = substr($cookie, 40); + return _rmcookie($self) if $uid !~ /^\d+$/ || !$self->dbSessionCheck($uid, $token); + $self->{_auth} = $self->dbUserGet(uid => $uid, what => 'mymessages')->[0]; } @@ -36,15 +35,21 @@ sub authInit { sub authLogin { my $self = shift; my $user = lc(scalar shift); - my $pass = md5_hex(shift); + my $pass = shift; my $to = shift; if(_authCheck($self, $user, $pass)) { - (my $cookie = Crypt::Lite->new()->encrypt("VNDB$pass$user", md5_hex($self->{cookie_key}))) =~ s/\r?\n//g; + my $token = sha1_hex(join('', Time::HiRes::gettimeofday()) . join('', map chr(rand(93)+33), 1..9)); + my $expiration = time + 31536000; # 1yr + my $cookie = $token . $self->{_auth}{id}; + $self->dbSessionAdd($self->{_auth}{id}, $token); + + my $expstr = strftime("%a, %d %b %Y %H:%M:%S GMT", gmtime($expiration)); $self->resRedirect($to, 'post'); - $self->resHeader('Set-Cookie', "vndb_auth=$cookie; expires=Sat, 01-Jan-2030 00:00:00 GMT; path=/; domain=$self->{cookie_domain}"); + $self->resHeader('Set-Cookie', "vndb_auth=$cookie; expires=$expstr; path=/; domain=$self->{cookie_domain}"); return 1; } + return 0; } @@ -52,8 +57,16 @@ sub authLogin { # clears authentication cookie and redirects to / sub authLogout { my $self = shift; + + my $cookie = $self->reqCookie('vndb_auth'); + if ($cookie && length($cookie) >= 41) { + my $token = substr($cookie, 0, 40); + my $uid = substr($cookie, 40); + $self->dbSessionDel($uid, $token); + } + $self->resRedirect('/', 'temp'); - $self->resHeader('Set-Cookie', "vndb_auth= ; expires=Sat, 01-Jan-2000 00:00:00 GMT; path=/; domain=$self->{cookie_domain}"); + _rmcookie($self); } @@ -75,20 +88,56 @@ sub authCan { # Checks for a valid login and writes information in _auth -# Arguments: user, md5_hex(pass) +# Arguments: user, pass # Returns: 1 if login is valid, 0 otherwise sub _authCheck { my($self, $user, $pass) = @_; - return 0 if - !$user || length($user) > 15 || length($user) < 2 - || !$pass || length($pass) != 32; + return 0 if !$user || length($user) > 15 || length($user) < 2 || !$pass; - my $d = $self->dbUserGet(username => $user, passwd => $pass, what => 'mymessages')->[0]; + my $d = $self->dbUserGet(username => $user, what => 'mymessages')->[0]; return 0 if !defined $d->{id} || !$d->{rank}; - $self->{_auth} = $d; - return 1; + if(_authEncryptPass($self, $pass, $d->{salt}) eq $d->{passwd}) { + $self->{_auth} = $d; + return 1; + } + if(md5_hex($pass) eq $d->{passwd}) { + $self->{_auth} = $d; + my %o; + ($o{passwd}, $o{salt}) = authPreparePass($self, $pass); + $self->dbUserEdit($d->{id}, %o); + return 1; + } + + return 0; +} + + +# Encryption algorithm for user passwords +# Arguments: self, pass, salt +# Returns: encrypted password (in hex) +sub _authEncryptPass{ + my($self, $pass, $salt, $bin) = @_; + return sha256_hex($self->{global_salt} . encode_utf8($pass) . encode_utf8($salt)); +} + + +# Prepares a plaintext password for database storage +# Arguments: pass +# Returns: list (pass, salt) +sub authPreparePass{ + my($self, $pass) = @_; + my $salt = join '', map chr(rand(93)+33), 1..9; + my $hash = _authEncryptPass($self, $pass, $salt); + return ($hash, $salt); +} + + +# removes the vndb_auth cookie +sub _rmcookie { + $_[0]->resHeader('Set-Cookie', + "vndb_auth= ; expires=Sat, 01-Jan-2000 00:00:00 GMT; path=/; domain=$_[0]->{cookie_domain}"); } diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm index 2cf3fc40..b5813330 100644 --- a/lib/VNDB/Util/LayoutHTML.pm +++ b/lib/VNDB/Util/LayoutHTML.pm @@ -21,6 +21,7 @@ sub htmlHeader { # %options->{ title, js, noindex, search } title $o{title}; Link rel => 'shortcut icon', href => '/favicon.ico', type => 'image/x-icon'; Link rel => 'stylesheet', href => $self->{url_static}.'/s/'.$skin.'/style.css?'.$self->{version}, type => 'text/css', media => 'all'; + Link rel => 'search', type => 'application/opensearchdescription+xml', title => 'VNDB VN Search', href => $self->{url}.'/opensearch.xml'; if($o{js}) { script type => 'text/javascript', src => $self->{url_static}.'/f/forms.js?'.$self->{version}; end; } diff --git a/lib/VNDB/Util/Misc.pm b/lib/VNDB/Util/Misc.pm deleted file mode 100644 index 139eb571..00000000 --- a/lib/VNDB/Util/Misc.pm +++ /dev/null @@ -1,50 +0,0 @@ - -package VNDB::Util::Misc; - -use strict; -use warnings; -use Exporter 'import'; -use Tie::ShareLite ':lock'; - -our @EXPORT = qw|multiCmd vnCacheUpdate|; - - -# Sends a command to Multi -# Argument: the commands to add to the queue, or none to send the queue to Multi -sub multiCmd { - my $self = shift; - - $self->{_multiCmd} = [] if !$self->{_multiCmd}; - return push @{$self->{_multiCmd}}, @_ if @_; - - return if !@{$self->{_multiCmd}}; - - my $s = tie my %s, 'Tie::ShareLite', -key => $self->{sharedmem_key}, -create => 'yes', -destroy => 'no', -mode => 0666; - $s->lock(LOCK_EX); - my @q = ( ($s{queue} ? @{$s{queue}} : ()), @{$self->{_multiCmd}} ); - $s{queue} = \@q; - $s->unlock(); - $self->{_multiCmd} = []; -} - - -# Recalculates the vn.c_* columns and regenerates the related relation graphs on any change -# Arguments: list of vids to be updated -sub vnCacheUpdate { - my($self, @vns) = @_; - - my $before = $self->dbVNGet(id => \@vns, order => 'v.id', what => 'relations'); - $self->dbVNCache(@vns); - my $after = $self->dbVNGet(id => \@vns, order => 'v.id'); - - my @upd = map { - @{$before->[$_]{relations}} && ( - $before->[$_]{c_released} != $after->[$_]{c_released} - || $before->[$_]{c_languages} ne $after->[$_]{c_languages} - ) ? $before->[$_]{id} : (); - } 0..$#$before; - $self->multiCmd('relgraph '.join(' ', @upd)) if @upd; -} - - -1; diff --git a/static/f/forms.js b/static/f/forms.js index 4fb02f1e..4e59796d 100644 --- a/static/f/forms.js +++ b/static/f/forms.js @@ -484,7 +484,7 @@ function scrCheckStatus() { var tr; for(var s=0;s<ls.length;s++) { for(i=0;i<l.length-1;i++) - if(l[i].scrId == ls[s].getAttribute('id') && ls[s].getAttribute('status') > 0) + if(l[i].scrId == ls[s].getAttribute('id') && ls[s].getAttribute('processed') == "1") tr = l[i]; if(!tr) continue; diff --git a/static/f/icons.png b/static/f/icons.png Binary files differindex 9430e7c6..47412c62 100644 --- a/static/f/icons.png +++ b/static/f/icons.png diff --git a/util/dbgraph.pl b/util/dbgraph.pl index b9800a0a..167680cd 100755 --- a/util/dbgraph.pl +++ b/util/dbgraph.pl @@ -17,12 +17,12 @@ use warnings; my %subgraphs = ( 'Producers' => [qw| FFFFCC producers producers_rev |], - 'Releases' => [qw| C8FFC8 releases releases_rev releases_media releases_platforms releases_producers releases_vn |], + '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_categories vn_anime vn_screenshots |], - 'Users' => [qw| CCFFFF users votes vnlists rlists wlists |], + 'Users' => [qw| CCFFFF users votes rlists wlists |], '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 relgraph stats_cache quotes |], + 'Misc' => [qw| F5F5F5 changes anime screenshots relgraph stats_cache quotes sessions |], ); my %tables; # table_name => [ [ col1, pri ], ... ] @@ -76,7 +76,7 @@ print qq|graph G {\n|. #qq| ratio = "compress"\n|. #qq| overlap = "false"\n|. - qq| rankdir = "LR"\n|. + #qq| rankdir = "LR"\n|. qq| node [ shape="plaintext" ]\n|. #qq| edge [ color="#cccccc" ]\n|. qq| labelloc="t"\n|. diff --git a/util/dump.sql b/util/dump.sql index 22e80099..3a470102 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -20,13 +20,13 @@ SET default_with_oids = false; -- anime CREATE TABLE anime ( id integer NOT NULL PRIMARY KEY, - year smallint NOT NULL DEFAULT 0, - ann_id integer NOT NULL DEFAULT 0, - nfo_id varchar(200) NOT NULL DEFAULT '', - type smallint NOT NULL DEFAULT 0, - title_romaji varchar(200) NOT NULL DEFAULT '', - title_kanji varchar(200) NOT NULL DEFAULT '', - lastfetch bigint NOT NULL DEFAULT 0 + year smallint, + ann_id integer, + nfo_id varchar(200), + type smallint, + title_romaji, + title_kanji, + lastfetch timestamptz ); -- changes @@ -156,11 +156,19 @@ CREATE TABLE rlists ( -- screenshots CREATE TABLE screenshots ( id SERIAL NOT NULL PRIMARY KEY, - status smallint NOT NULL DEFAULT 0, + processed boolean NOT NULL DEFAULT FALSE, width smallint NOT NULL DEFAULT 0, height smallint NOT NULL DEFAULT 0 ); +-- sessions +CREATE TABLE sessions ( + uid integer NOT NULL, + token bytea NOT NULL, + expiration timestamptz NOT NULL DEFAULT (now() + '1 year'::interval), + PRIMARY KEY (uid, token) +); + -- stats_cache CREATE TABLE stats_cache ( section varchar(25) NOT NULL PRIMARY KEY, @@ -248,7 +256,6 @@ CREATE TABLE users ( rank smallint NOT NULL DEFAULT 3, passwd bytea NOT NULL DEFAULT '', registered bigint NOT NULL DEFAULT 0, - flags integer NOT NULL DEFAULT 7, show_nsfw boolean NOT NULL DEFAULT FALSE, show_list boolean NOT NULL DEFAULT TRUE, c_votes integer NOT NULL DEFAULT 0, @@ -256,7 +263,8 @@ CREATE TABLE users ( 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 + c_tags integer NOT NULL DEFAULT 0, + salt character(9) NOT NULL DEFAULT '' ); -- vn @@ -321,16 +329,6 @@ CREATE TABLE vn_screenshots ( PRIMARY KEY(vid, scr) ); --- vnlists -CREATE TABLE vnlists ( - uid integer DEFAULT 0, - vid integer NOT NULL DEFAULT 0, - status smallint NOT NULL DEFAULT 0, - date bigint NOT NULL DEFAULT 0, - comments varchar(500) NOT NULL DEFAULT '', - PRIMARY KEY(uid, vid) -); - -- votes CREATE TABLE votes ( vid integer NOT NULL DEFAULT 0, @@ -358,53 +356,52 @@ CREATE TABLE wlists ( ----------------------------------------------- -ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id); +ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes (id); ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; +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); ALTER TABLE releases ADD FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_lang ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_producers ADD FOREIGN KEY (pid) REFERENCES producers (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_rev ADD FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE tags_aliases ADD FOREIGN KEY (tag) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE tags_parents ADD FOREIGN KEY (tag) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE tags_parents ADD FOREIGN KEY (parent) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE tags_vn ADD FOREIGN KEY (tag) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE tags_vn ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE tags_vn ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE releases_lang ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); +ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); +ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); +ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); +ALTER TABLE releases_producers ADD FOREIGN KEY (pid) REFERENCES producers (id); +ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes (id); +ALTER TABLE releases_rev ADD FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); +ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE sessions ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id); +ALTER TABLE tags_aliases ADD FOREIGN KEY (tag) REFERENCES tags (id); +ALTER TABLE tags_parents ADD FOREIGN KEY (tag) REFERENCES tags (id); +ALTER TABLE tags_parents ADD FOREIGN KEY (parent) REFERENCES tags (id); +ALTER TABLE tags_vn ADD FOREIGN KEY (tag) REFERENCES tags (id); +ALTER TABLE tags_vn ADD FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE tags_vn ADD FOREIGN KEY (uid) REFERENCES users (id); ALTER TABLE threads ADD FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads (id); +ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id); ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_anime ADD FOREIGN KEY (vid) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_categories ADD FOREIGN KEY (vid) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_relations ADD FOREIGN KEY (vid2) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_screenshots ADD FOREIGN KEY (vid) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_screenshots ADD FOREIGN KEY (scr) REFERENCES screenshots (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn_screenshots ADD FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vnlists ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vnlists ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (id); +ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id); +ALTER TABLE vn_anime ADD FOREIGN KEY (vid) REFERENCES vn_rev (id); +ALTER TABLE vn_categories ADD FOREIGN KEY (vid) REFERENCES vn_rev (id); +ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id); +ALTER TABLE vn_relations ADD FOREIGN KEY (vid2) REFERENCES vn (id); +ALTER TABLE vn_rev ADD FOREIGN KEY (id) REFERENCES changes (id); +ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn (id); +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 votes ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id); @@ -415,7 +412,7 @@ ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn -- F U N C T I O N S -- ------------------------- - + -- update_rev(table, ids) - updates the rev column in the changes table CREATE FUNCTION update_rev(tbl text, ids text) RETURNS void AS $$ DECLARE @@ -449,7 +446,7 @@ BEGIN IF id > 0 THEN w := ' WHERE id = '||id; END IF; - EXECUTE 'UPDATE vn SET + EXECUTE 'UPDATE vn SET c_released = COALESCE((SELECT MIN(rr1.released) FROM releases_rev rr1 @@ -698,6 +695,113 @@ CREATE TRIGGER threads_posts_stats_update AFTER INSERT OR UPDATE ON threads_post CREATE TRIGGER users_stats_update AFTER INSERT OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +-- insert rows into anime for new vn_anime.aid items +CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$ +BEGIN + IF NOT EXISTS(SELECT 1 FROM anime WHERE id = NEW.aid) THEN + INSERT INTO anime (id) VALUES (NEW.aid); + END IF; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER vn_anime_aid BEFORE INSERT OR UPDATE ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid(); + + +-- Send a notify whenever anime info should be fetched +CREATE OR REPLACE FUNCTION anime_fetch_notify() RETURNS trigger AS $$ +BEGIN + IF NEW.lastfetch IS NULL THEN + NOTIFY anime; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW EXECUTE PROCEDURE anime_fetch_notify(); + + +-- Send a notify when a new cover image is uploaded +CREATE OR REPLACE FUNCTION vn_rev_image_notify() RETURNS trigger AS $$ +BEGIN + IF NEW.image < 0 THEN + NOTIFY coverimage; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER vn_rev_image_notify AFTER INSERT OR UPDATE ON vn_rev FOR EACH ROW EXECUTE PROCEDURE vn_rev_image_notify(); + + +-- Send a notify when a screenshot needs to be processed +CREATE OR REPLACE FUNCTION screenshot_process_notify() RETURNS trigger AS $$ +BEGIN + IF NEW.processed = FALSE THEN + NOTIFY screenshot; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER screenshot_process_notify AFTER INSERT OR UPDATE ON screenshots FOR EACH ROW EXECUTE PROCEDURE screenshot_process_notify(); + + +-- Update vn.rgraph column and send notify when a relation graph needs to be regenerated +-- 1. NOTIFY is sent on an UPDATE or INSERT on vn with rgraph = NULL and with entries in vn_relations (deferred) +-- vn.rgraph is set to NULL when: +-- 2. UPDATE on vn where c_released or c_languages has changed (deferred, but doesn't have to be) +-- 3. New VN revision of which the title differs from previous revision (deferred) +-- 4. New VN revision with items in vn_relations that differ from previous revision (deferred) +CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$ +BEGIN + -- 1. + IF TG_TABLE_NAME = 'vn' THEN + IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM vn_relations WHERE vid1 = NEW.latest) THEN + NOTIFY relgraph; + END IF; + END IF; + IF TG_TABLE_NAME = 'vn' AND TG_OP = 'UPDATE' THEN + IF NEW.rgraph IS NOT NULL AND OLD.latest > 0 THEN + -- 2. + IF OLD.c_released <> NEW.c_released OR OLD.c_languages <> NEW.c_languages THEN + UPDATE vn SET rgraph = NULL WHERE id = NEW.id; + END IF; + -- 3 & 4 + IF OLD.latest <> NEW.latest AND ( + 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) + OR EXISTS(SELECT v1.vid2, v1.relation FROM vn_relations v1 WHERE v1.vid1 = OLD.latest EXCEPT SELECT v2.vid2, v2.relation FROM vn_relations v2 WHERE v2.vid1 = NEW.latest) + OR EXISTS(SELECT v1.vid2, v1.relation FROM vn_relations v1 WHERE v1.vid1 = NEW.latest EXCEPT SELECT v2.vid2, v2.relation FROM vn_relations v2 WHERE v2.vid1 = OLD.latest) + ) THEN + UPDATE vn SET rgraph = NULL WHERE id = NEW.id; + END IF; + END IF; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE CONSTRAINT TRIGGER vn_relgraph_notify AFTER INSERT OR UPDATE ON vn DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify(); + + +-- NOTIFY on insert into changes/posts/tags +CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$ +BEGIN + IF TG_TABLE_NAME = 'changes' THEN + NOTIFY newrevision; + ELSIF TG_TABLE_NAME = 'threads_posts' THEN + NOTIFY newpost; + ELSIF TG_TABLE_NAME = 'tags' THEN + NOTIFY newtag; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +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(); + diff --git a/util/multi.pl b/util/multi.pl index e234534d..010951a2 100755 --- a/util/multi.pl +++ b/util/multi.pl @@ -1,36 +1,17 @@ #!/usr/bin/perl -# Usage: -# ./multi.pl [-c] [-s] [-a] [cmd1] [cmd2] .. -# -c Do not daemonize, just execute the commands specified -# on the command line and exit. -# -s Same as -c, but also execute commands in the shared -# memory processing queue. -# -a Don't do anything, just add the commands specified on -# the command line to the shared memory processing queue. - # # Multi - core namespace for initialisation and global variables # -# NOTE: in case of errors, clearing the shared memory might work: -# $ ipcrm -S 0x42444e56 -M 0x42444e56 - package Multi; use strict; use warnings; -no warnings 'once'; -use Tie::ShareLite ':lock'; -use Time::HiRes; -use POE; -use DBI; use Cwd 'abs_path'; -# loading & initialization - our $ROOT; BEGIN { ($ROOT = abs_path $0) =~ s{/util/multi\.pl$}{}; *VNDB::ROOT = \$ROOT } use lib $VNDB::ROOT.'/lib'; @@ -38,42 +19,6 @@ use lib $VNDB::ROOT.'/lib'; use Multi::Core; require $VNDB::ROOT.'/data/global.pl'; -our $STOP = 0; -our $DAEMONIZE = (grep /^-c$/, @ARGV) ? 1 : (grep /^-s$/, @ARGV) ? 2 : 0; - - - -# only add commands with the -a argument - -if(grep /^-a$/, @ARGV) { - my $s = tie my %s, 'Tie::ShareLite', -key => $VNDB::S{sharedmem_key}, -create => 'yes', -destroy => 'no', -mode => 0666; - $s->lock(LOCK_EX); - my @q = ( ($s{queue} ? @{$s{queue}} : ()), (grep !/^-/, @ARGV) ); - $s{queue} = \@q; - $s->unlock(); - exit; -} - -# one shared pgsql connection for all sessions -our $SQL = DBI->connect(@{$VNDB::O{db_login}}, - { PrintError => 1, RaiseError => 0, AutoCommit => 1, pg_enable_utf8 => 1 }); - - -Multi::Core->spawn(); - -# dynamically load and spawn modules -for (keys %{$VNDB::M{modules}}) { - my($mod, $args) = ($_, $VNDB::M{modules}{$_}); - next if !$args || ref($args) ne 'HASH'; - require "Multi/$mod.pm"; - # I'm surprised the strict pagma isn't complaining about this - "Multi::$mod"->spawn(%$args); -} - -$SIG{__WARN__} = sub {(local$_=shift)=~s/\r?\n//;$poe_kernel->call(core=>log=>1,'__WARN__: '.$_)}; - -$poe_kernel->run(); -exec $0, grep /^-/, @ARGV if $STOP == 2; - +Multi::Core->run(); diff --git a/util/updates/update_2.6.sql b/util/updates/update_2.6.sql new file mode 100644 index 00000000..06722cc2 --- /dev/null +++ b/util/updates/update_2.6.sql @@ -0,0 +1,261 @@ + + +-- Create table for session data storage +CREATE TABLE sessions ( + uid integer NOT NULL REFERENCES users(id), + token bytea NOT NULL, + expiration timestamptz NOT NULL DEFAULT (NOW() + '1 year'::interval), + PRIMARY KEY (uid, token) +); + +-- Add column to users for salt storage +ALTER TABLE users ADD COLUMN salt character(9) NOT NULL DEFAULT ''::bpchar; + + + +-- The anime table: +-- - use timestamp data type for anime.lastfetch +-- - allow NULL for all columns except id +ALTER TABLE anime ALTER COLUMN lastfetch DROP NOT NULL; +ALTER TABLE anime ALTER COLUMN lastfetch DROP DEFAULT; +UPDATE anime SET lastfetch = NULL WHERE lastfetch <= 0; +ALTER TABLE anime ALTER COLUMN lastfetch TYPE timestamptz USING 'epoch'::timestamptz + lastfetch * interval '1 second'; + +ALTER TABLE anime ALTER COLUMN ann_id DROP NOT NULL; +ALTER TABLE anime ALTER COLUMN ann_id DROP DEFAULT; +UPDATE anime SET ann_id = NULL WHERE ann_id = 0; + +ALTER TABLE anime ALTER COLUMN nfo_id DROP NOT NULL; +ALTER TABLE anime ALTER COLUMN nfo_id DROP DEFAULT; +UPDATE anime SET nfo_id = NULL WHERE nfo_id = ''; + +ALTER TABLE anime ALTER COLUMN title_kanji DROP NOT NULL; +ALTER TABLE anime ALTER COLUMN title_kanji DROP DEFAULT; +UPDATE anime SET title_kanji = NULL WHERE title_kanji = ''; + +ALTER TABLE anime ALTER COLUMN title_romaji DROP NOT NULL; +ALTER TABLE anime ALTER COLUMN title_romaji DROP DEFAULT; +UPDATE anime SET title_romaji = NULL WHERE title_romaji = ''; + +ALTER TABLE anime ALTER COLUMN type DROP NOT NULL; +ALTER TABLE anime ALTER COLUMN type DROP DEFAULT; +UPDATE anime SET type = NULL WHERE type = 0; +UPDATE anime SET type = type-1; + +ALTER TABLE anime ALTER COLUMN year DROP NOT NULL; +ALTER TABLE anime ALTER COLUMN year DROP DEFAULT; +UPDATE anime SET year = NULL WHERE year = 0; + + + +-- screenshots.status (smallint) -> screenshots.processed (boolean) +ALTER TABLE screenshots RENAME COLUMN status TO processed; +ALTER TABLE screenshots ALTER COLUMN processed DROP DEFAULT; +ALTER TABLE screenshots ALTER COLUMN processed TYPE boolean USING processed::int::boolean; +ALTER TABLE screenshots ALTER COLUMN processed SET DEFAULT FALSE; + + + +-- two new resolutions have been added, array indexes have changed +UPDATE releases_rev SET resolution = resolution + 1 WHERE resolution >= 5; +UPDATE releases_rev SET resolution = resolution + 1 WHERE resolution >= 7; + + + +-- remove the DEFERRED attribute on all foreign key checks on which it isn't necessary +-- (note: these queries all assume the foreign keys have their default names, as given +-- by PostgreSQL. This shouldn't be a problem, provided if you haven't touched them.) +ALTER TABLE changes DROP CONSTRAINT changes_requester_fkey; +ALTER TABLE changes DROP CONSTRAINT changes_causedby_fkey; +ALTER TABLE producers_rev DROP CONSTRAINT producers_rev_id_fkey; +ALTER TABLE producers_rev DROP CONSTRAINT producers_rev_pid_fkey; +ALTER TABLE quotes DROP CONSTRAINT quotes_vid_fkey; +ALTER TABLE releases_lang DROP CONSTRAINT releases_lang_rid_fkey; +ALTER TABLE releases_media DROP CONSTRAINT releases_media_rid_fkey; +ALTER TABLE releases_platforms DROP CONSTRAINT releases_platforms_rid_fkey; +ALTER TABLE releases_producers DROP CONSTRAINT releases_producers_rid_fkey; +ALTER TABLE releases_producers DROP CONSTRAINT releases_producers_pid_fkey; +ALTER TABLE releases_rev DROP CONSTRAINT releases_rev_id_fkey; +ALTER TABLE releases_rev DROP CONSTRAINT releases_rev_rid_fkey; +ALTER TABLE releases_vn DROP CONSTRAINT releases_vn_rid_fkey; +ALTER TABLE releases_vn DROP CONSTRAINT releases_vn_vid_fkey; +ALTER TABLE rlists DROP CONSTRAINT rlists_uid_fkey; +ALTER TABLE rlists DROP CONSTRAINT rlists_rid_fkey; +ALTER TABLE tags DROP CONSTRAINT tags_addedby_fkey; +ALTER TABLE tags_aliases DROP CONSTRAINT tags_aliases_tag_fkey; +ALTER TABLE tags_parents DROP CONSTRAINT tags_parents_tag_fkey; +ALTER TABLE tags_parents DROP CONSTRAINT tags_parents_parent_fkey; +ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_tag_fkey; +ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_vid_fkey; +ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_uid_fkey; +ALTER TABLE threads_posts DROP CONSTRAINT threads_posts_tid_fkey; +ALTER TABLE threads_posts DROP CONSTRAINT threads_posts_uid_fkey; +ALTER TABLE threads_boards DROP CONSTRAINT threads_tags_tid_fkey; -- threads_boards used to be called threads_tags +ALTER TABLE vn DROP CONSTRAINT vn_rgraph_fkey; +ALTER TABLE vn_anime DROP CONSTRAINT vn_anime_aid_fkey; +ALTER TABLE vn_anime DROP CONSTRAINT vn_anime_vid_fkey; +ALTER TABLE vn_categories DROP CONSTRAINT vn_categories_vid_fkey; +ALTER TABLE vn_relations DROP CONSTRAINT vn_relations_vid1_fkey; +ALTER TABLE vn_relations DROP CONSTRAINT vn_relations_vid2_fkey; +ALTER TABLE vn_rev DROP CONSTRAINT vn_rev_id_fkey; +ALTER TABLE vn_rev DROP CONSTRAINT vn_rev_vid_fkey; +ALTER TABLE vn_screenshots DROP CONSTRAINT vn_screenshots_vid_fkey; +ALTER TABLE vn_screenshots DROP CONSTRAINT vn_screenshots_scr_fkey; +ALTER TABLE vn_screenshots DROP CONSTRAINT vn_screenshots_rid_fkey; +ALTER TABLE votes DROP CONSTRAINT votes_uid_fkey; +ALTER TABLE votes DROP CONSTRAINT votes_vid_fkey; +ALTER TABLE wlists DROP CONSTRAINT wlists_uid_fkey; +ALTER TABLE wlists DROP CONSTRAINT wlists_vid_fkey; + +ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id); +ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes (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); +ALTER TABLE releases_lang ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); +ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); +ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); +ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); +ALTER TABLE releases_producers ADD FOREIGN KEY (pid) REFERENCES producers (id); +ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes (id); +ALTER TABLE releases_rev ADD FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); +ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id); +ALTER TABLE tags_aliases ADD FOREIGN KEY (tag) REFERENCES tags (id); +ALTER TABLE tags_parents ADD FOREIGN KEY (tag) REFERENCES tags (id); +ALTER TABLE tags_parents ADD FOREIGN KEY (parent) REFERENCES tags (id); +ALTER TABLE tags_vn ADD FOREIGN KEY (tag) REFERENCES tags (id); +ALTER TABLE tags_vn ADD FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE tags_vn ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads (id); +ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id); +ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (id); +ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id); +ALTER TABLE vn_anime ADD FOREIGN KEY (vid) REFERENCES vn_rev (id); +ALTER TABLE vn_categories ADD FOREIGN KEY (vid) REFERENCES vn_rev (id); +ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id); +ALTER TABLE vn_relations ADD FOREIGN KEY (vid2) REFERENCES vn (id); +ALTER TABLE vn_rev ADD FOREIGN KEY (id) REFERENCES changes (id); +ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn (id); +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 votes ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id); + + + +-- automatically insert rows into the anime table for unknown aids +-- when inserted into vn_anime +CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$ +BEGIN + IF NOT EXISTS(SELECT 1 FROM anime WHERE id = NEW.aid) THEN + INSERT INTO anime (id) VALUES (NEW.aid); + END IF; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER vn_anime_aid BEFORE INSERT OR UPDATE ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid(); + + +-- Send a notify whenever anime info should be fetched +CREATE OR REPLACE FUNCTION anime_fetch_notify() RETURNS trigger AS $$ +BEGIN + IF NEW.lastfetch IS NULL THEN + NOTIFY anime; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW EXECUTE PROCEDURE anime_fetch_notify(); + + +-- Send a notify when a new cover image is uploaded +CREATE OR REPLACE FUNCTION vn_rev_image_notify() RETURNS trigger AS $$ +BEGIN + IF NEW.image < 0 THEN + NOTIFY coverimage; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER vn_rev_image_notify AFTER INSERT OR UPDATE ON vn_rev FOR EACH ROW EXECUTE PROCEDURE vn_rev_image_notify(); + + +-- Send a notify when a screenshot needs to be processed +CREATE OR REPLACE FUNCTION screenshot_process_notify() RETURNS trigger AS $$ +BEGIN + IF NEW.processed = FALSE THEN + NOTIFY screenshot; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER screenshot_process_notify AFTER INSERT OR UPDATE ON screenshots FOR EACH ROW EXECUTE PROCEDURE screenshot_process_notify(); + + +-- Update vn.rgraph column and send notify when a relation graph needs to be regenerated +-- 1. NOTIFY is sent on an UPDATE or INSERT on vn with rgraph = NULL and with entries in vn_relations (deferred) +-- vn.rgraph is set to NULL when: +-- 2. UPDATE on vn where c_released or c_languages has changed (deferred, but doesn't have to be) +-- 3. New VN revision of which the title differs from previous revision (deferred) +-- 4. New VN revision with items in vn_relations that differ from previous revision (deferred) +CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$ +BEGIN + -- 1. + IF TG_TABLE_NAME = 'vn' THEN + IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM vn_relations WHERE vid1 = NEW.latest) THEN + NOTIFY relgraph; + END IF; + END IF; + IF TG_TABLE_NAME = 'vn' AND TG_OP = 'UPDATE' THEN + IF NEW.rgraph IS NOT NULL AND OLD.latest > 0 THEN + -- 2. + IF OLD.c_released <> NEW.c_released OR OLD.c_languages <> NEW.c_languages THEN + UPDATE vn SET rgraph = NULL WHERE id = NEW.id; + END IF; + -- 3 & 4 + IF OLD.latest <> NEW.latest AND ( + 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) + OR EXISTS(SELECT v1.vid2, v1.relation FROM vn_relations v1 WHERE v1.vid1 = OLD.latest EXCEPT SELECT v2.vid2, v2.relation FROM vn_relations v2 WHERE v2.vid1 = NEW.latest) + OR EXISTS(SELECT v1.vid2, v1.relation FROM vn_relations v1 WHERE v1.vid1 = NEW.latest EXCEPT SELECT v2.vid2, v2.relation FROM vn_relations v2 WHERE v2.vid1 = OLD.latest) + ) THEN + UPDATE vn SET rgraph = NULL WHERE id = NEW.id; + END IF; + END IF; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE CONSTRAINT TRIGGER vn_relgraph_notify AFTER INSERT OR UPDATE ON vn DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify(); + + +-- NOTIFY on insert into changes/posts/tags +CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$ +BEGIN + IF TG_TABLE_NAME = 'changes' THEN + NOTIFY newrevision; + ELSIF TG_TABLE_NAME = 'threads_posts' THEN + NOTIFY newpost; + ELSIF TG_TABLE_NAME = 'tags' THEN + NOTIFY newtag; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +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(); + diff --git a/util/vndb.pl b/util/vndb.pl index 8a9a83e5..455a1c23 100755 --- a/util/vndb.pl +++ b/util/vndb.pl @@ -36,7 +36,6 @@ YAWF::init( namespace => 'VNDB', object_data => \%S, pre_request_handler => \&reqinit, - post_request_handler => \&reqdone, error_404_handler => \&handle404, ); @@ -58,13 +57,6 @@ sub reqinit { } -sub reqdone { - my $self = shift; - $self->dbCommit; - $self->multiCmd; -} - - sub handle404 { my $self = shift; $self->resStatus(404); |
