summaryrefslogtreecommitdiff
path: root/lib/Multi
diff options
context:
space:
mode:
Diffstat (limited to 'lib/Multi')
-rw-r--r--lib/Multi/API.pm176
-rw-r--r--lib/Multi/Feed.pm16
-rw-r--r--lib/Multi/IRC.pm73
-rw-r--r--lib/Multi/Maintenance.pm31
-rw-r--r--lib/Multi/RG.pm12
5 files changed, 145 insertions, 163 deletions
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;
}