From 08a0d819efd244c8b3dacfd810b7322ad8df73fb Mon Sep 17 00:00:00 2001 From: Yorhel Date: Wed, 21 Oct 2015 13:10:19 +0200 Subject: SQL: Fix Multi to use the new DB schema That should be the last thing to convert to the new schema. --- lib/Multi/API.pm | 176 +++++++++++++++++++++++------------------------ lib/Multi/Feed.pm | 16 ++--- lib/Multi/IRC.pm | 73 +++++++++----------- lib/Multi/Maintenance.pm | 31 +++------ lib/Multi/RG.pm | 12 ++-- 5 files changed, 145 insertions(+), 163 deletions(-) (limited to 'lib') diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm index d6034160..f75ea4f9 100644 --- a/lib/Multi/API.pm +++ b/lib/Multi/API.pm @@ -359,8 +359,8 @@ sub splitarray { # } # filters => filters args for get_filters() (TODO: Document) my %GET_VN = ( - sql => 'SELECT %s FROM vn v JOIN vn_rev vr ON v.latest = vr.id WHERE NOT v.hidden AND (%s) %s', - select => 'v.id, v.latest', + sql => 'SELECT %s FROM vn v WHERE NOT v.hidden AND (%s) %s', + select => 'v.id', proc => sub { delete $_[0]{latest}; $_[0]{id} *= 1 @@ -368,12 +368,12 @@ my %GET_VN = ( sortdef => 'id', sorts => { id => 'v.id %s', - title => 'vr.title %s', + title => 'v.title %s', released => 'v.c_released %s', }, flags => { basic => { - select => 'vr.title, vr.original, v.c_released, v.c_languages, v.c_olang, v.c_platforms', + select => 'v.title, v.original, v.c_released, v.c_languages, v.c_olang, v.c_platforms', proc => sub { $_[0]{original} ||= undef; $_[0]{platforms} = splitarray delete $_[0]{c_platforms}; @@ -383,7 +383,7 @@ my %GET_VN = ( }, }, details => { - select => 'vr.image, vr.img_nsfw, vr.alias AS aliases, vr.length, vr.desc AS description, vr.l_wp, vr.l_encubed, vr.l_renai', + select => 'v.image, v.img_nsfw, v.alias AS aliases, v.length, v.desc AS description, v.l_wp, v.l_encubed, v.l_renai', proc => sub { $_[0]{aliases} ||= undef; $_[0]{length} *= 1; @@ -407,12 +407,12 @@ my %GET_VN = ( }, }, anime => { - fetch => [[ 'latest', 'SELECT va.vid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji - FROM anime a JOIN vn_anime va ON va.aid = a.id WHERE va.vid IN(%s)', + fetch => [[ 'id', 'SELECT va.id AS vid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji + FROM anime a JOIN vn_anime va ON va.aid = a.id WHERE va.id IN(%s)', sub { my($r, $n) = @_; # link for my $i (@$r) { - $i->{anime} = [ grep $i->{latest} == $_->{vid}, @$n ]; + $i->{anime} = [ grep $i->{id} == $_->{vid}, @$n ]; } # cleanup for (@$n) { @@ -425,16 +425,16 @@ my %GET_VN = ( ]], }, relations => { - fetch => [[ 'latest', 'SELECT vl.vid1, v.id, vl.relation, vr.title, vr.original FROM vn_relations vl - JOIN vn v ON v.id = vl.vid2 JOIN vn_rev vr ON vr.id = v.latest WHERE vl.vid1 IN(%s) AND NOT v.hidden', + fetch => [[ 'id', 'SELECT vr.id AS vid, v.id, vr.relation, v.title, v.original FROM vn_relations vr + JOIN vn v ON v.id = vr.vid WHERE vr.id IN(%s)', sub { my($r, $n) = @_; for my $i (@$r) { - $i->{relations} = [ grep $i->{latest} == $_->{vid1}, @$n ]; + $i->{relations} = [ grep $i->{id} == $_->{vid}, @$n ]; } for (@$n) { $_->{id} *= 1; $_->{original} ||= undef; - delete $_->{vid1}; + delete $_->{vid}; } } ]], @@ -460,17 +460,17 @@ my %GET_VN = ( [ inta => 'v.id :op:(:value:)', {'=' => 'IN', '!= ' => 'NOT IN'}, range => [1,1e6], join => ',' ], ], title => [ - [ str => 'vr.title :op: :value:', {qw|= = != <>|} ], - [ str => 'vr.title ILIKE :value:', {'~',1}, process => \'like' ], + [ str => 'v.title :op: :value:', {qw|= = != <>|} ], + [ str => 'v.title ILIKE :value:', {'~',1}, process => \'like' ], ], original => [ - [ undef, "vr.original :op: ''", {qw|= = != <>|} ], - [ str => 'vr.original :op: :value:', {qw|= = != <>|} ], - [ str => 'vr.original ILIKE :value:', {'~',1}, process => \'like' ] + [ undef, "v.original :op: ''", {qw|= = != <>|} ], + [ str => 'v.original :op: :value:', {qw|= = != <>|} ], + [ str => 'v.original ILIKE :value:', {'~',1}, process => \'like' ] ], firstchar => [ - [ undef, '(:op: ((ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)))', {'=', '', '!=', 'NOT'} ], - [ str => 'LOWER(SUBSTR(vr.title, 1, 1)) :op: :value:' => {qw|= = != <>|}, process => sub { shift =~ /^([a-z])$/ ? $1 : \'Invalid character' } ], + [ undef, '(:op: ((ASCII(v.title) < 97 OR ASCII(v.title) > 122) AND (ASCII(v.title) < 65 OR ASCII(v.title) > 90)))', {'=', '', '!=', 'NOT'} ], + [ str => 'LOWER(SUBSTR(v.title, 1, 1)) :op: :value:' => {qw|= = != <>|}, process => sub { shift =~ /^([a-z])$/ ? $1 : \'Invalid character' } ], ], released => [ [ undef, 'v.c_released :op: 0', {qw|= = != <>|} ], @@ -498,13 +498,13 @@ my %GET_VN = ( ); my %GET_RELEASE = ( - sql => 'SELECT %s FROM releases r JOIN releases_rev rr ON rr.id = r.latest WHERE NOT hidden AND (%s) %s', - select => 'r.id, r.latest', + sql => 'SELECT %s FROM releases r WHERE NOT hidden AND (%s) %s', + select => 'r.id', sortdef => 'id', sorts => { id => 'r.id %s', - title => 'rr.title %s', - released => 'rr.released %s', + title => 'r.title %s', + released => 'r.released %s', }, proc => sub { delete $_[0]{latest}; @@ -512,7 +512,7 @@ my %GET_RELEASE = ( }, flags => { basic => { - select => 'rr.title, rr.original, rr.released, rr.type, rr.patch, rr.freeware, rr.doujin', + select => 'r.title, r.original, r.released, r.type, r.patch, r.freeware, r.doujin', proc => sub { $_[0]{original} ||= undef; $_[0]{released} = formatdate($_[0]{released}); @@ -520,16 +520,16 @@ my %GET_RELEASE = ( $_[0]{freeware} = $_[0]{freeware} =~ /^t/ ? TRUE : FALSE; $_[0]{doujin} = $_[0]{doujin} =~ /^t/ ? TRUE : FALSE; }, - fetch => [[ 'latest', 'SELECT rid, lang FROM releases_lang WHERE rid IN(%s)', + fetch => [[ 'id', 'SELECT id, lang FROM releases_lang WHERE id IN(%s)', sub { my($n, $r) = @_; for my $i (@$n) { - $i->{languages} = [ map $i->{latest} == $_->{rid} ? $_->{lang} : (), @$r ]; + $i->{languages} = [ map $i->{id} == $_->{id} ? $_->{lang} : (), @$r ]; } }, ]], }, details => { - select => 'rr.website, rr.notes, rr.minage, rr.gtin, rr.catalog', + select => 'r.website, r.notes, r.minage, r.gtin, r.catalog', proc => sub { $_[0]{website} ||= undef; $_[0]{notes} ||= undef; @@ -538,30 +538,30 @@ my %GET_RELEASE = ( $_[0]{catalog} ||= undef; }, fetch => [ - [ 'latest', 'SELECT rid, platform FROM releases_platforms WHERE rid IN(%s)', + [ 'id', 'SELECT id, platform FROM releases_platforms WHERE id IN(%s)', sub { my($n, $r) = @_; for my $i (@$n) { - $i->{platforms} = [ map $i->{latest} == $_->{rid} ? $_->{platform} : (), @$r ]; + $i->{platforms} = [ map $i->{id} == $_->{id} ? $_->{platform} : (), @$r ]; } } ], - [ 'latest', 'SELECT rid, medium, qty FROM releases_media WHERE rid IN(%s)', + [ 'id', 'SELECT id, medium, qty FROM releases_media WHERE id IN(%s)', sub { my($n, $r) = @_; for my $i (@$n) { - $i->{media} = [ grep $i->{latest} == $_->{rid}, @$r ]; + $i->{media} = [ grep $i->{id} == $_->{id}, @$r ]; } for (@$r) { - delete $_->{rid}; + delete $_->{id}; $_->{qty} = $VNDB::S{media}{$_->{medium}} ? $_->{qty}*1 : undef; } } ], ] }, vn => { - fetch => [[ 'latest', 'SELECT rv.rid, v.id, vr.title, vr.original FROM releases_vn rv JOIN vn v ON v.id = rv.vid - JOIN vn_rev vr ON vr.id = v.latest WHERE NOT v.hidden AND rv.rid IN(%s)', + fetch => [[ 'id', 'SELECT rv.id AS rid, v.id, v.title, v.original FROM releases_vn rv JOIN vn v ON v.id = rv.vid + WHERE NOT v.hidden AND rv.id IN(%s)', sub { my($n, $r) = @_; for my $i (@$n) { - $i->{vn} = [ grep $i->{latest} == $_->{rid}, @$r ]; + $i->{vn} = [ grep $i->{id} == $_->{rid}, @$r ]; } for (@$r) { $_->{id}*=1; @@ -572,11 +572,11 @@ my %GET_RELEASE = ( ]], }, producers => { - fetch => [[ 'latest', 'SELECT rp.rid, rp.developer, rp.publisher, p.id, pr.type, pr.name, pr.original FROM releases_producers rp - JOIN producers p ON p.id = rp.pid JOIN producers_rev pr ON pr.id = p.latest WHERE NOT p.hidden AND rp.rid IN(%s)', + fetch => [[ 'id', 'SELECT rp.id AS rid, rp.developer, rp.publisher, p.id, p.type, p.name, p.original FROM releases_producers rp + JOIN producers p ON p.id = rp.pid WHERE NOT p.hidden AND rp.id IN(%s)', sub { my($n, $r) = @_; for my $i (@$n) { - $i->{producers} = [ grep $i->{latest} == $_->{rid}, @$r ]; + $i->{producers} = [ grep $i->{id} == $_->{rid}, @$r ]; } for (@$r) { $_->{id}*=1; @@ -595,47 +595,47 @@ my %GET_RELEASE = ( [ inta => 'r.id :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', range => [1,1e6] ], ], vn => [ - [ 'int' => 'rr.id IN(SELECT rv.rid FROM releases_vn rv WHERE rv.vid = :value:)', {'=',1}, range => [1,1e6] ], + [ 'int' => 'r.id IN(SELECT rv.id FROM releases_vn rv WHERE rv.vid = :value:)', {'=',1}, range => [1,1e6] ], ], producer => [ - [ 'int' => 'rr.id IN(SELECT rp.rid FROM releases_producers rp WHERE rp.pid = :value:)', {'=',1}, range => [1,1e6] ], + [ 'int' => 'r.id IN(SELECT rp.id FROM releases_producers rp WHERE rp.pid = :value:)', {'=',1}, range => [1,1e6] ], ], title => [ - [ str => 'rr.title :op: :value:', {qw|= = != <>|} ], - [ str => 'rr.title ILIKE :value:', {'~',1}, process => \'like' ], + [ str => 'r.title :op: :value:', {qw|= = != <>|} ], + [ str => 'r.title ILIKE :value:', {'~',1}, process => \'like' ], ], original => [ - [ undef, "rr.original :op: ''", {qw|= = != <>|} ], - [ str => 'rr.original :op: :value:', {qw|= = != <>|} ], - [ str => 'rr.original ILIKE :value:', {'~',1}, process => \'like' ] + [ undef, "r.original :op: ''", {qw|= = != <>|} ], + [ str => 'r.original :op: :value:', {qw|= = != <>|} ], + [ str => 'r.original ILIKE :value:', {'~',1}, process => \'like' ] ], released => [ - [ undef, 'rr.released :op: 0', {qw|= = != <>|} ], - [ str => 'rr.released :op: :value:', {qw|= = != <> > > < < <= <= >= >=|}, process => \&parsedate ], + [ undef, 'r.released :op: 0', {qw|= = != <>|} ], + [ str => 'r.released :op: :value:', {qw|= = != <> > > < < <= <= >= >=|}, process => \&parsedate ], ], - patch => [ [ bool => 'rr.patch = :value:', {'=',1} ] ], - freeware => [ [ bool => 'rr.freeware = :value:', {'=',1} ] ], - doujin => [ [ bool => 'rr.doujin = :value:', {'=',1} ] ], + patch => [ [ bool => 'r.patch = :value:', {'=',1} ] ], + freeware => [ [ bool => 'r.freeware = :value:', {'=',1} ] ], + doujin => [ [ bool => 'r.doujin = :value:', {'=',1} ] ], type => [ - [ str => 'rr.type :op: :value:', {qw|= = != <>|}, + [ str => 'r.type :op: :value:', {qw|= = != <>|}, process => sub { !grep($_ eq $_[0], @{$VNDB::S{release_types}}) ? \'No such release type' : $_[0] } ], ], gtin => [ - [ 'int' => 'rr.gtin :op: :value:', {qw|= = != <>|}, process => sub { length($_[0]) > 14 ? \'Too long GTIN code' : $_[0] } ], + [ 'int' => 'r.gtin :op: :value:', {qw|= = != <>|}, process => sub { length($_[0]) > 14 ? \'Too long GTIN code' : $_[0] } ], ], catalog => [ - [ str => 'rr.catalog :op: :value:', {qw|= = != <>|} ], + [ str => 'r.catalog :op: :value:', {qw|= = != <>|} ], ], languages => [ - [ str => 'rr.id :op:(SELECT rl.rid FROM releases_lang rl WHERE rl.lang = :value:)', {'=' => 'IN', '!=' => 'NOT IN'}, process => \'lang' ], - [ stra => 'rr.id :op:(SELECT rl.rid FROM releases_lang rl WHERE rl.lang IN(:value:))', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', process => \'lang' ], + [ str => 'r.id :op:(SELECT rl.id FROM releases_lang rl WHERE rl.lang = :value:)', {'=' => 'IN', '!=' => 'NOT IN'}, process => \'lang' ], + [ stra => 'r.id :op:(SELECT rl.id FROM releases_lang rl WHERE rl.lang IN(:value:))', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', process => \'lang' ], ], }, ); my %GET_PRODUCER = ( - sql => 'SELECT %s FROM producers p JOIN producers_rev pr ON p.latest = pr.id WHERE NOT p.hidden AND (%s) %s', - select => 'p.id, p.latest', + sql => 'SELECT %s FROM producers p WHERE NOT p.hidden AND (%s) %s', + select => 'p.id', proc => sub { delete $_[0]{latest}; $_[0]{id} *= 1 @@ -643,17 +643,17 @@ my %GET_PRODUCER = ( sortdef => 'id', sorts => { id => 'p.id %s', - name => 'pr.name %s', + name => 'p.name %s', }, flags => { basic => { - select => 'pr.type, pr.name, pr.original, pr.lang AS language', + select => 'p.type, p.name, p.original, p.lang AS language', proc => sub { $_[0]{original} ||= undef; }, }, details => { - select => 'pr.website, pr.l_wp, pr.desc AS description, pr.alias AS aliases', + select => 'p.website, p.l_wp, p.desc AS description, p.alias AS aliases', proc => sub { $_[0]{description} ||= undef; $_[0]{aliases} ||= undef; @@ -664,16 +664,16 @@ my %GET_PRODUCER = ( }, }, relations => { - fetch => [[ 'latest', 'SELECT pl.pid1, p.id, pl.relation, pr.name, pr.original FROM producers_relations pl - JOIN producers p ON p.id = pl.pid2 JOIN producers_rev pr ON pr.id = p.latest WHERE pl.pid1 IN(%s) AND NOT p.hidden', + fetch => [[ 'id', 'SELECT pl.id AS pid, p.id, pl.relation, p.name, p.original FROM producers_relations pl + JOIN producers p ON p.id = pl.pid WHERE pl.id IN(%s)', sub { my($n, $r) = @_; for my $i (@$n) { - $i->{relations} = [ grep $i->{latest} == $_->{pid1}, @$r ]; + $i->{relations} = [ grep $i->{id} == $_->{pid}, @$r ]; } for (@$r) { $_->{id}*=1; $_->{original} ||= undef; - delete $_->{pid1}; + delete $_->{pid}; } }, ]], @@ -685,31 +685,31 @@ my %GET_PRODUCER = ( [ inta => 'p.id :op:(:value:)', {'=' => 'IN', '!= ' => 'NOT IN'}, join => ',', range => [1,1e6] ], ], name => [ - [ str => 'pr.name :op: :value:', {qw|= = != <>|} ], - [ str => 'pr.name ILIKE :value:', {'~',1}, process => \'like' ], + [ str => 'p.name :op: :value:', {qw|= = != <>|} ], + [ str => 'p.name ILIKE :value:', {'~',1}, process => \'like' ], ], original => [ - [ undef, "pr.original :op: ''", {qw|= = != <>|} ], - [ str => 'pr.original :op: :value:', {qw|= = != <>|} ], - [ str => 'pr.original ILIKE :value:', {'~',1}, process => \'like' ] + [ undef, "p.original :op: ''", {qw|= = != <>|} ], + [ str => 'p.original :op: :value:', {qw|= = != <>|} ], + [ str => 'p.original ILIKE :value:', {'~',1}, process => \'like' ] ], type => [ - [ str => 'pr.type :op: :value:', {qw|= = != <>|}, + [ str => 'p.type :op: :value:', {qw|= = != <>|}, process => sub { !grep($_ eq $_[0], @{$VNDB::S{producer_types}}) ? \'No such producer type' : $_[0] } ], ], language => [ - [ str => 'pr.lang :op: :value:', {qw|= = != <>|}, process => \'lang' ], - [ stra => 'pr.lang :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', process => \'lang' ], + [ str => 'p.lang :op: :value:', {qw|= = != <>|}, process => \'lang' ], + [ stra => 'p.lang :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', process => \'lang' ], ], search => [ - [ str => '(pr.name ILIKE :value: OR pr.original ILIKE :value: OR pr.alias ILIKE :value:)', {'~',1}, process => \'like' ], + [ str => '(p.name ILIKE :value: OR p.original ILIKE :value: OR p.alias ILIKE :value:)', {'~',1}, process => \'like' ], ], }, ); my %GET_CHARACTER = ( - sql => 'SELECT %s FROM chars c JOIN chars_rev cr ON c.latest = cr.id WHERE NOT c.hidden AND (%s) %s', - select => 'c.id, c.latest', + sql => 'SELECT %s FROM chars c WHERE NOT c.hidden AND (%s) %s', + select => 'c.id', proc => sub { delete $_[0]{latest}; $_[0]{id} *= 1 @@ -717,11 +717,11 @@ my %GET_CHARACTER = ( sortdef => 'id', sorts => { id => 'c.id %s', - name => 'cr.name %s', + name => 'c.name %s', }, flags => { basic => { - select => 'cr.name, cr.original, cr.gender, cr.bloodt, cr.b_day, cr.b_month', + select => 'c.name, c.original, c.gender, c.bloodt, c.b_day, c.b_month', proc => sub { $_[0]{original} ||= undef; $_[0]{gender} = undef if $_[0]{gender} eq 'unknown'; @@ -730,7 +730,7 @@ my %GET_CHARACTER = ( }, }, details => { - select => 'cr.alias AS aliases, cr.image, cr."desc" AS description', + select => 'c.alias AS aliases, c.image, c."desc" AS description', proc => sub { $_[0]{aliases} ||= undef; $_[0]{image} = $_[0]{image} ? sprintf '%s/ch/%02d/%d.jpg', $VNDB::S{url_static}, $_[0]{image}%100, $_[0]{image} : undef; @@ -738,16 +738,16 @@ my %GET_CHARACTER = ( }, }, meas => { - select => 'cr.s_bust AS bust, cr.s_waist AS waist, cr.s_hip AS hip, cr.height, cr.weight', + select => 'c.s_bust AS bust, c.s_waist AS waist, c.s_hip AS hip, c.height, c.weight', proc => sub { $_[0]{$_} = $_[0]{$_} ? $_[0]{$_}*1 : undef for(qw|bust waist hip height weight|); }, }, traits => { - fetch => [[ 'latest', 'SELECT cid, tid, spoil FROM chars_traits WHERE cid IN(%s)', + fetch => [[ 'id', 'SELECT id, tid, spoil FROM chars_traits WHERE id IN(%s)', sub { my($n, $r) = @_; for my $i (@$n) { - $i->{traits} = [ map [ $_->{tid}*1, $_->{spoil}*1 ], grep $i->{latest} == $_->{cid}, @$r ]; + $i->{traits} = [ map [ $_->{tid}*1, $_->{spoil}*1 ], grep $i->{id} == $_->{id}, @$r ]; } }, ]], @@ -759,19 +759,19 @@ my %GET_CHARACTER = ( [ inta => 'c.id :op:(:value:)', {'=' => 'IN', '!= ' => 'NOT IN'}, range => [1,1e6], join => ',' ], ], name => [ - [ str => 'cr.name :op: :value:', {qw|= = != <>|} ], - [ str => 'cr.name ILIKE :value:', {'~',1}, process => \'like' ], + [ str => 'c.name :op: :value:', {qw|= = != <>|} ], + [ str => 'c.name ILIKE :value:', {'~',1}, process => \'like' ], ], original => [ - [ undef, "cr.original :op: ''", {qw|= = != <>|} ], - [ str => 'cr.original :op: :value:', {qw|= = != <>|} ], - [ str => 'cr.original ILIKE :value:', {'~',1}, process => \'like' ] + [ undef, "c.original :op: ''", {qw|= = != <>|} ], + [ str => 'c.original :op: :value:', {qw|= = != <>|} ], + [ str => 'c.original ILIKE :value:', {'~',1}, process => \'like' ] ], search => [ - [ str => '(cr.name ILIKE :value: OR cr.original ILIKE :value: OR cr.alias ILIKE :value:)', {'~',1}, process => \'like' ], + [ str => '(c.name ILIKE :value: OR c.original ILIKE :value: OR c.alias ILIKE :value:)', {'~',1}, process => \'like' ], ], vn => [ - [ 'int' => 'cr.id IN(SELECT cv.cid FROM chars_vns cv WHERE cv.vid = :value:)', {'=',1}, range => [1,1e6] ], + [ 'int' => 'c.id IN(SELECT cv.id FROM chars_vns cv WHERE cv.vid = :value:)', {'=',1}, range => [1,1e6] ], ], }, ); diff --git a/lib/Multi/Feed.pm b/lib/Multi/Feed.pm index d2161aec..2c4144db 100644 --- a/lib/Multi/Feed.pm +++ b/lib/Multi/Feed.pm @@ -44,16 +44,16 @@ sub generate { # changes pg_cmd q{ - SELECT '/'||c.type||COALESCE(vr.vid, rr.rid, pr.pid, cr.cid, sr.sid)||'.'||c.rev AS id, - COALESCE(vr.title, rr.title, pr.name, cr.name, sa.name) AS title, extract('epoch' from c.added) AS updated, + SELECT '/'||c.type||COALESCE(v.id, r.id, p.id, c.id, s.id)||'.'||c.rev AS id, + COALESCE(v.title, r.title, p.name, ca.name, sa.name) AS title, extract('epoch' from c.added) AS updated, u.username, u.id AS uid, c.comments AS summary FROM changes c - LEFT JOIN vn_rev vr ON c.type = 'v' AND c.id = vr.id - LEFT JOIN releases_rev rr ON c.type = 'r' AND c.id = rr.id - LEFT JOIN producers_rev pr ON c.type = 'p' AND c.id = pr.id - LEFT JOIN chars_rev cr ON c.type = 'c' AND c.id = cr.id - LEFT JOIN staff_rev sr ON c.type = 's' AND c.id = sr.id - LEFT JOIN staff_alias sa ON sa.rid = sr.id AND sa.id = sr.aid + LEFT JOIN vn v ON c.type = 'v' AND c.itemid = v.id + LEFT JOIN releases r ON c.type = 'r' AND c.itemid = r.id + LEFT JOIN producers p ON c.type = 'p' AND c.itemid = p.id + LEFT JOIN chars ca ON c.type = 'c' AND c.itemid = ca.id + LEFT JOIN staff s ON c.type = 's' AND c.itemid = s.id + LEFT JOIN staff_alias sa ON sa.id = s.id AND sa.aid = s.aid JOIN users u ON u.id = c.requester WHERE c.requester <> 1 ORDER BY c.id DESC diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm index b583e090..6c6159e3 100644 --- a/lib/Multi/IRC.pm +++ b/lib/Multi/IRC.pm @@ -18,12 +18,10 @@ use Encode 'decode_utf8', 'encode_utf8'; # long subquery used in several places my $GETBOARDS = q{array_to_string(array( - SELECT tb.type||COALESCE(':'||COALESCE(u.username, vr.title, pr.name), '') + SELECT tb.type||COALESCE(':'||COALESCE(u.username, v.title, p.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 @@ -279,24 +277,24 @@ sub handleid { # plain vn/user/producer/thread/tag/trait/release pg_cmd 'SELECT $1::text AS type, $2::integer AS id, '.( - $t eq 'v' ? 'vr.title FROM vn_rev vr JOIN vn v ON v.latest = vr.id WHERE v.id = $2' : + $t eq 'v' ? 'v.title FROM vn v WHERE v.id = $2' : $t eq 'u' ? 'u.username AS title FROM users u WHERE u.id = $2' : - $t eq 'p' ? 'pr.name AS title FROM producers_rev pr JOIN producers p ON p.latest = pr.id WHERE p.id = $2' : - $t eq 'c' ? 'cr.name AS title FROM chars_rev cr JOIN chars c ON c.latest = cr.id WHERE c.id = $2' : - $t eq 's' ? 'sa.name AS title FROM staff_rev sr JOIN staff s ON s.latest = sr.id JOIN staff_alias sa ON sa.id = sr.aid AND sa.rid = s.latest WHERE s.id = $2' : + $t eq 'p' ? 'p.name AS title FROM producers p WHERE p.id = $2' : + $t eq 'c' ? 'c.name AS title FROM chars c WHERE c.id = $2' : + $t eq 's' ? 'sa.name AS title FROM staff s JOIN staff_alias sa ON sa.aid = s.aid AND sa.id = s.id WHERE s.id = $2' : $t eq 't' ? 'title, '.$GETBOARDS.' FROM threads t WHERE id = $2' : $t eq 'g' ? 'name AS title FROM tags WHERE id = $2' : $t eq 'i' ? 'name AS title FROM traits WHERE id = $2' : - 'rr.title FROM releases_rev rr JOIN releases r ON r.latest = rr.id WHERE r.id = $2'), + 'r.title FROM releases r WHERE r.id = $2'), [ $t, $id ], $c if !$rev && $t =~ /[vprtugics]/; # edit/insert of vn/release/producer or discussion board post pg_cmd 'SELECT $1::text AS type, $2::integer AS id, $3::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 = $2 AND c.rev = $3' : - $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 = $2 AND c.rev = $3' : - $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 = $2 AND c.rev = $3' : - $t eq 'c' ? 'cr.name AS title, u.username, h.comments FROM changes h JOIN chars_rev cr ON h.id = cr.id JOIN users u ON u.id = h.requester WHERE cr.cid = $2 AND h.rev = $3' : - $t eq 's' ? 'sa.name AS title, u.username, c.comments FROM changes c JOIN staff_rev sr ON c.id = sr.id JOIN users u ON u.id = c.requester JOIN staff_alias sa ON sa.id = sr.aid AND sa.rid = sr.id WHERE sr.sid = $2 AND c.rev = $3' : + $t eq 'v' ? 'vh.title, u.username, c.comments FROM changes c JOIN vn_hist vh ON c.id = vh.chid JOIN users u ON u.id = c.requester WHERE c.type = \'v\' AND c.itemid = $2 AND c.rev = $3' : + $t eq 'r' ? 'rh.title, u.username, c.comments FROM changes c JOIN releases_hist rh ON c.id = rh.chid JOIN users u ON u.id = c.requester WHERE c.type = \'r\' AND c.itemid = $2 AND c.rev = $3' : + $t eq 'p' ? 'ph.name AS title, u.username, c.comments FROM changes c JOIN producers_hist ph ON c.id = ph.chid JOIN users u ON u.id = c.requester WHERE c.type = \'p\' AND c.itemid = $2 AND c.rev = $3' : + $t eq 'c' ? 'ch.name AS title, u.username, c.comments FROM changes c JOIN chars_hist ch ON c.id = ch.chid JOIN users u ON u.id = c.requester WHERE c.type = \'c\' AND c.itemid = $2 AND c.rev = $3' : + $t eq 's' ? 'sah.name AS title, u.username, c.comments FROM changes c JOIN staff_hist sh ON c.id = sh.chid JOIN users u ON u.id = c.requester JOIN staff_alias_hist sah ON sah.chid = c.id AND sah.aid = sh.aid WHERE c.type = \'s\' AND c.itemid = $2 AND c.rev = $3' : '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 = $2 AND tp.num = $3'), [ $t, $id, $rev], $c if $rev && $t =~ /[vprtcs]/; @@ -341,15 +339,15 @@ sub notify { my $q = { rev => q{ - SELECT c.type, c.rev, c.comments, c.id AS lastid, - COALESCE(vr.vid, rr.rid, pr.pid, cr.cid, sr.sid) AS id, COALESCE(vr.title, rr.title, pr.name, cr.name, sa.name) AS title, u.username + SELECT c.type, c.rev, c.comments, c.id AS lastid, c.itemid AS id, + COALESCE(vh.title, rh.title, ph.name, ch.name, sah.name) AS title, u.username FROM changes c - LEFT JOIN vn_rev vr ON c.type = 'v' AND c.id = vr.id - LEFT JOIN releases_rev rr ON c.type = 'r' AND c.id = rr.id - LEFT JOIN producers_rev pr ON c.type = 'p' AND c.id = pr.id - LEFT JOIN chars_rev cr ON c.type = 'c' AND c.id = cr.id - LEFT JOIN staff_rev sr ON c.type = 's' AND c.id = sr.id - LEFT JOIN staff_alias sa ON c.type = 's' AND sa.id = sr.aid AND sa.rid = c.id + LEFT JOIN vn_hist vh ON c.type = 'v' AND c.id = vh.chid + LEFT JOIN releases_hist rh ON c.type = 'r' AND c.id = rh.chid + LEFT JOIN producers_hist ph ON c.type = 'p' AND c.id = ph.chid + LEFT JOIN chars_hist ch ON c.type = 'c' AND c.id = ch.chid + LEFT JOIN staff_hist sh ON c.type = 's' AND c.id = sh.chid + LEFT JOIN staff_alias_hist sah ON c.type = 's' AND sah.aid = sh.aid AND sah.chid = c.id JOIN users u ON u.id = c.requester WHERE c.id > $1 AND c.requester <> 1 ORDER BY c.id}, @@ -409,13 +407,12 @@ vn => [ 0, 0, sub { return $irc->send_msg(PRIVMSG => $chan, "Couldn't do anything with that search query, you might want to add quotes or use longer words.") if !@q; - my $w = join ' AND ', map "v.c_search LIKE \$$_", 1..@q; + my $w = join ' AND ', map "c_search LIKE \$$_", 1..@q; pg_cmd qq{ - SELECT 'v'::text AS type, v.id, vr.title - FROM vn v - JOIN vn_rev vr ON vr.id = v.latest - WHERE NOT v.hidden AND $w - ORDER BY vr.title + SELECT 'v'::text AS type, id, title + FROM vn + WHERE NOT hidden AND $w + ORDER BY title LIMIT 6 }, [ map "%$_%", @q ], sub { my $res = shift; @@ -431,11 +428,10 @@ p => [ 0, 0, sub { my($nick, $chan, $q) = @_; return $irc->send_msg(PRIVMSG => $chan, 'You forgot the search query, dummy~~!') if !$q; pg_cmd q{ - SELECT 'p'::text AS type, p.id, pr.name AS title + SELECT 'p'::text AS type, id, 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 + WHERE hidden = FALSE AND (name ILIKE $1 OR original ILIKE $1 OR alias ILIKE $1) + ORDER BY name LIMIT 6 }, [ "%$q%" ], sub { my $res = shift; @@ -449,18 +445,17 @@ p => [ 0, 0, sub { scr => [ 0, 0, sub { my($nick, $chan, $q) = @_; - return $irc->send_msg(PRIVMSG => $chan. + return $irc->send_msg(PRIVMSG => $chan, q|Sorry, I failed to comprehend which screenshot you'd like me to lookup for you,| .q| please understand that Yorhel was not willing to supply me with mind reading capabilities.|) - if $q !~ /([0-9]+)\.jpg/; + if !$q || $q !~ /([0-9]+)\.jpg/; $q = $1; pg_cmd q{ - SELECT 'v'::text AS type, v.id, vr.title - FROM vn v - JOIN vn_rev vr ON vr.id = v.latest - JOIN vn_rev vr2 ON vr2.vid = v.id - JOIN vn_screenshots vs ON vs.vid = vr2.id - WHERE vs.scr = $1 LIMIT 1 + SELECT 'v'::text AS type, v.id, v.title + FROM changes c + JOIN vn_screenshots_hist vsh ON vsh.chid = c.id + JOIN vn v ON v.id = c.itemid + WHERE vsh.scr = $1 LIMIT 1 }, [ $q ], sub { my $res = shift; return if pg_expect $res, 1; diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index 23e17fff..dfa708d6 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -53,17 +53,15 @@ sub log_res { my %dailies = ( - # takes about 500ms to 5s to complete, depending on how many releases have - # been released within the past 5 days + # takes about 50ms to 500ms to complete, depending on how many releases have been released within the past 5 days vncache_inc => q| SELECT update_vncache(id) FROM ( SELECT DISTINCT rv.vid FROM releases r - JOIN releases_rev rr ON rr.id = r.latest - JOIN releases_vn rv ON rv.rid = r.latest - WHERE rr.released > TO_CHAR(NOW() - '5 days'::interval, 'YYYYMMDD')::integer - AND rr.released <= TO_CHAR(NOW(), 'YYYYMMDD')::integer + JOIN releases_vn rv ON rv.id = r.id + WHERE r.released > TO_CHAR(NOW() - '5 days'::interval, 'YYYYMMDD')::integer + AND r.released <= TO_CHAR(NOW(), 'YYYYMMDD')::integer ) AS r(id)|, # takes about 9 seconds max, still OK @@ -75,7 +73,7 @@ my %dailies = ( # takes about 30 seconds vnpopularity => 'SELECT update_vnpopularity()', - # takes about 25 seconds, can be performed in ranges as well when necessary + # takes about 1 second, can be performed in ranges as well when necessary vnrating => q| UPDATE vn SET c_rating = (SELECT ( @@ -126,9 +124,7 @@ sub daily { my %monthlies = ( - # This takes about 4 to 5 minutes to complete, and should only be necessary - # in the event that the daily vncache_inc cron hasn't been running for 5 - # subsequent days. + # This only takes about 3 seconds to complete vncache_full => 'SELECT update_vncache(id) FROM vn', # These shouldn't really be necessary, the triggers in PgSQL should keep @@ -202,18 +198,9 @@ sub vnsearch_check { return if pg_expect $res, 1 or !$res->rows; my $id = $res->value(0,0); - pg_cmd q|SELECT vr.title, vr.original, vr.alias - FROM vn v - JOIN vn_rev vr ON vr.id = v.latest - WHERE v.id = $1 - UNION - SELECT rr.title, rr.original, NULL - FROM releases r - JOIN releases_rev rr ON rr.id = r.latest - JOIN releases_vn rv ON rv.rid = r.latest - WHERE rv.vid = $1 - AND NOT r.hidden - |, [ $id ], sub { vnsearch_update($id, @_) }; + pg_cmd q|SELECT title, original, alias FROM vn WHERE id = $1 + UNION SELECT r.title, r.original, NULL FROM releases r JOIN releases_vn rv ON rv.id = r.id WHERE rv.vid = $1 AND NOT r.hidden|, + [ $id ], sub { vnsearch_update($id, @_) }; }; } diff --git a/lib/Multi/RG.pm b/lib/Multi/RG.pm index 30101c53..177a82be 100644 --- a/lib/Multi/RG.pm +++ b/lib/Multi/RG.pm @@ -40,9 +40,9 @@ sub check_rg { AE::log debug => 'Checking for new graphs to create.'; pg_cmd q| - SELECT 'v', v.id FROM vn v JOIN vn_relations vr ON vr.vid1 = v.latest WHERE rgraph IS NULL AND hidden = FALSE + SELECT 'v', v.id FROM vn v JOIN vn_relations vr ON vr.id = v.id WHERE v.rgraph IS NULL AND v.hidden = FALSE UNION - SELECT 'p', p.id FROM producers p JOIN producers_relations pr ON pr.pid1 = p.latest WHERE rgraph IS NULL AND hidden = FALSE + SELECT 'p', p.id FROM producers p JOIN producers_relations pr ON pr.id = p.id WHERE p.rgraph IS NULL AND p.hidden = FALSE LIMIT 1|, undef, \&creategraph; } @@ -69,8 +69,8 @@ sub getrelid { my $id = shift; AE::log debug => "Fetching relations for $C{type}$id"; pg_cmd $C{type} eq 'v' - ? 'SELECT vid2, relation, official FROM vn v JOIN vn_relations vr ON vr.vid1 = v.latest WHERE v.id = $1' - : 'SELECT pid2, relation FROM producers p JOIN producers_relations pr ON pr.pid1 = p.latest WHERE p.id = $1', + ? 'SELECT vid, relation, official FROM vn_relations WHERE id = $1' + : 'SELECT pid, relation FROM producers_relations WHERE id = $1', [ $id ], sub { getrel($id, @_) }; } @@ -104,8 +104,8 @@ sub getrel { # id, res, time my $ids = join(', ', map '$'.$_, 1..@ids); AE::log debug => "Fetching node information for $C{type}:".join ', ', @ids; pg_cmd $C{type} eq 'v' - ? "SELECT v.id, vr.title, v.c_released AS date, array_to_string(v.c_languages, '/') AS lang FROM vn v JOIN vn_rev vr ON vr.id = v.latest WHERE v.id IN($ids) ORDER BY v.c_released" - : "SELECT p.id, pr.name, pr.lang, pr.type FROM producers p JOIN producers_rev pr ON pr.id = p.latest WHERE p.id IN($ids) ORDER BY pr.name", + ? "SELECT id, title, c_released AS date, array_to_string(c_languages, '/') AS lang FROM vn WHERE id IN($ids) ORDER BY c_released" + : "SELECT id, name, lang, type FROM producers WHERE id IN($ids) ORDER BY name", [ @ids ], \&builddot; } -- cgit v1.2.3