diff options
| -rw-r--r-- | lib/VNDB/DB/Staff.pm | 7 | ||||
| -rw-r--r-- | util/sql/staff.sql | 1 |
2 files changed, 7 insertions, 1 deletions
diff --git a/lib/VNDB/DB/Staff.pm b/lib/VNDB/DB/Staff.pm index 3e26e360..2c8dae93 100644 --- a/lib/VNDB/DB/Staff.pm +++ b/lib/VNDB/DB/Staff.pm @@ -25,7 +25,12 @@ sub dbStaffGet { $o{id} ? ( ref $o{id} ? ('s.id IN(!l)' => [$o{id}]) : ('s.id = ?' => $o{id}) ) : (), $o{aid} ? ( ref $o{aid} ? ('sa.id IN(!l)' => [$o{aid}]) : ('sa.id = ?' => $o{aid}) ) : (), $o{search} ? - ( '(sa.name ILIKE ? OR sa.original ILIKE ?)', [ map '%%'.$o{search}.'%%', 1..2 ] ) : (), + $o{search} =~ /[\x{3000}-\x{9fff}\x{ff00}-\x{ff9f}]/ ? + # match against 'original' column only if search string contains any + # japanese character. + # note: more precise regex would be /[\p{Hiragana}\p{Katakana}\p{Han}]/ + ( q|(sa.original LIKE ? OR translate(sa.original,' ','') LIKE ?)| => [ '%'.$o{search}.'%', ($o{search} =~ s/\s+//gr).'%' ] ) : + ( '(sa.name ILIKE ? OR sa.original ILIKE ?)' => [ map '%%'.$o{search}.'%%', 1..2 ] ) : (), $o{char} ? ( 'LOWER(SUBSTR(sa.name, 1, 1)) = ?' => $o{char} ) : (), defined $o{char} && !$o{char} ? ( '(ASCII(sa.name) < 97 OR ASCII(sa.name) > 122) AND (ASCII(sa.name) < 65 OR ASCII(sa.name) > 90)' => 1 ) : (), diff --git a/util/sql/staff.sql b/util/sql/staff.sql index cf452bcc..972765ea 100644 --- a/util/sql/staff.sql +++ b/util/sql/staff.sql @@ -59,6 +59,7 @@ ALTER TABLE vn_seiyuu ADD FOREIGN KEY (vid) REFERENCES vn_rev CREATE INDEX vn_staff_vid ON vn_staff (vid); CREATE INDEX vn_staff_aid ON vn_staff (aid); +CREATE INDEX staff_alias_orig ON staff_alias (translate(original,' ','')); CREATE TRIGGER hidlock_update BEFORE UPDATE ON staff FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); |