summaryrefslogtreecommitdiff
path: root/lib
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2022-02-11 10:55:08 +0100
committerYorhel <git@yorhel.nl>2022-02-11 10:55:10 +0100
commitc57ca063582cb9b2500f18ad308e1794dc3cdc41 (patch)
treed28a82a489e83973c601226d75c5fbc15f0dcc1f /lib
parenta2d2f0ab6e3048ccfac19fb368ea33a5018428e7 (diff)
Update Multi::(IRC|API) to work with the new vn_titles schema
The API only exposes the legacy "title"/"original" fields for compatibility, exposing the full title list requires an API change and should be done eventually.
Diffstat (limited to 'lib')
-rw-r--r--lib/Multi/API.pm22
-rw-r--r--lib/Multi/IRC.pm10
2 files changed, 16 insertions, 16 deletions
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index c57afee1..d45abd4d 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -444,7 +444,7 @@ sub image_flagging {
# }
# filters => filters args for get_filters() (TODO: Document)
my %GET_VN = (
- sql => 'SELECT %s FROM vn v LEFT JOIN images i ON i.id = v.image WHERE NOT v.hidden AND (%s) %s',
+ sql => 'SELECT %s FROM vnt v LEFT JOIN images i ON i.id = v.image WHERE NOT v.hidden AND (%s) %s',
select => 'v.id',
proc => sub {
$_[0]{id} = idnum $_[0]{id};
@@ -460,7 +460,7 @@ my %GET_VN = (
},
flags => {
basic => {
- select => 'v.title, v.original, v.c_released, v.c_languages, v.olang, v.c_platforms',
+ select => 'v.title, v.alttitle AS original, v.c_released, v.c_languages, v.olang, v.c_platforms',
proc => sub {
$_[0]{original} ||= undef;
$_[0]{platforms} = splitarray delete $_[0]{c_platforms};
@@ -514,8 +514,8 @@ my %GET_VN = (
]],
},
relations => {
- fetch => [[ 'id', 'SELECT vr.id AS vid, v.id, vr.relation, v.title, v.original, vr.official FROM vn_relations vr
- JOIN vn v ON v.id = vr.vid WHERE vr.id IN(%s)',
+ fetch => [[ 'id', 'SELECT vr.id AS vid, v.id, vr.relation, v.title, v.alttitle AS original, vr.official FROM vn_relations vr
+ JOIN vnt v ON v.id = vr.vid WHERE vr.id IN(%s)',
sub { my($r, $n) = @_;
for my $i (@$r) {
$i->{relations} = [ grep $i->{id} eq $_->{vid}, @$n ];
@@ -591,13 +591,13 @@ my %GET_VN = (
[ str => 'v.title ILIKE :value:', {'~',1}, process => \'like' ],
],
original => [
- [ undef, "v.original :op: ''", {qw|= = != <>|} ],
- [ str => 'v.original :op: :value:', {qw|= = != <>|} ],
- [ str => 'v.original ILIKE :value:', {'~',1}, process => \'like' ]
+ [ undef, "v.alttitle :op: ''", {qw|= = != <>|} ],
+ [ str => 'v.alttitle :op: :value:', {qw|= = != <>|} ],
+ [ str => 'v.alttitle ILIKE :value:', {'~',1}, process => \'like' ]
],
firstchar => [
- [ 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' } ],
+ [ undef, ':op: match_firstchar(v.title, \'0\')', {'=', '', '!=', 'NOT'} ],
+ [ str => ':op: match_firstchar(v.title, :value:)', {'=', '', '!=', 'NOT'}, process => sub { shift =~ /^([a-z])$/ ? $1 : \'Invalid character' } ],
],
released => [
[ undef, 'v.c_released :op: 0', {qw|= = != <>|} ],
@@ -701,7 +701,7 @@ my %GET_RELEASE = (
]
},
vn => {
- fetch => [[ 'id', 'SELECT rv.id AS rid, rv.rtype, v.id, v.title, v.original FROM releases_vn rv JOIN vn v ON v.id = rv.vid
+ fetch => [[ 'id', 'SELECT rv.id AS rid, rv.rtype, v.id, v.title, v.alttitle AS original FROM releases_vn rv JOIN vnt v ON v.id = rv.vid
WHERE NOT v.hidden AND rv.id IN(%s)',
sub { my($n, $r) = @_;
for my $i (@$n) {
@@ -1074,7 +1074,7 @@ my %GET_STAFF = (
my %GET_QUOTE = (
- sql => "SELECT %s FROM quotes q JOIN vn v ON v.id = q.vid WHERE NOT v.hidden AND (%s) %s",
+ sql => "SELECT %s FROM quotes q JOIN vnt v ON v.id = q.vid WHERE NOT v.hidden AND (%s) %s",
select => "v.id, v.title, q.quote",
proc => sub {
$_[0]{id} = idnum $_[0]{id};
diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm
index 9fbc298f..6ac4ac59 100644
--- a/lib/Multi/IRC.pm
+++ b/lib/Multi/IRC.pm
@@ -20,7 +20,7 @@ use Encode 'decode_utf8', 'encode_utf8';
my $GETBOARDS = q{array_to_string(array(
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 vnt v ON tb.type = 'v' AND v.id = tb.iid
LEFT JOIN producers p ON tb.type = 'p' AND p.id = tb.iid
LEFT JOIN users u ON tb.type = 'u' AND u.id = tb.iid
WHERE tb.tid = t.id
@@ -269,7 +269,7 @@ sub handleid {
# plain vn/user/producer/thread/tag/trait/release
pg_cmd 'SELECT $1::vndbid AS id, '.(
$id =~ /^t/ ? 'title, '.$GETBOARDS.' FROM threads t WHERE NOT t.hidden AND NOT t.private AND t.id = $1' :
- $id =~ /^w/ ? 'v.title, u.username FROM reviews w JOIN vn v ON v.id = w.vid LEFT JOIN users u ON u.id = w.uid WHERE w.id = $1' :
+ $id =~ /^w/ ? 'v.title, u.username FROM reviews w JOIN vnt v ON v.id = w.vid LEFT JOIN users u ON u.id = w.uid WHERE w.id = $1' :
'title FROM item_info($1,NULL) x'),
[ $id ], $c if !$rev && $id =~ /^[dvprtugicsw]/;
@@ -322,7 +322,7 @@ sub notify {
review => q{
SELECT w.id, v.title, u.username, w.id AS lastid
FROM reviews w
- JOIN vn v ON v.id = w.vid
+ JOIN vnt v ON v.id = w.vid
LEFT JOIN users u ON u.id = w.uid
WHERE w.id > $1
ORDER BY w.id}
@@ -365,9 +365,9 @@ vn => [ 0, 0, sub {
my($nick, $chan, $q) = @_;
return $irc->send_msg(PRIVMSG => $chan, 'You forgot the search query, dummy~~!') if !$q;
- pg_cmd qq{
+ pg_cmd q{
SELECT id, title
- FROM vn
+ FROM vnt
WHERE NOT hidden AND c_search LIKE ALL (search_query($1))
ORDER BY title
LIMIT 6