diff options
author | Yorhel <git@yorhel.nl> | 2022-02-11 10:55:08 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2022-02-11 10:55:10 +0100 |
commit | c57ca063582cb9b2500f18ad308e1794dc3cdc41 (patch) | |
tree | d28a82a489e83973c601226d75c5fbc15f0dcc1f /lib | |
parent | a2d2f0ab6e3048ccfac19fb368ea33a5018428e7 (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.pm | 22 | ||||
-rw-r--r-- | lib/Multi/IRC.pm | 10 |
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 |