SQL: Standardize & simplify first-char matching

6 files changed, 6 insertions, 11 deletions

diff --git a/lib/VNWeb/Chars/List.pm b/lib/VNWeb/Chars/List.pm
index e44e77ea..5e44a606 100644
--- a/lib/VNWeb/Chars/List.pm
+++ b/lib/VNWeb/Chars/List.pm
@@ -109,8 +109,7 @@ TUWF::get qr{/c(?:/(?<char>all|[a-z0]))?}, sub {
     my $where = sql_and
         'NOT c.hidden', $opt->{f}->sql_where(),
         $opt->{q} ? sql 'c.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (),
-        defined($opt->{ch}) && $opt->{ch} ? sql('LOWER(SUBSTR(c.name, 1, 1)) =', \$opt->{ch}) : (),
-        defined($opt->{ch}) && !$opt->{ch} ? sql('(ASCII(c.name) <', \97, 'OR ASCII(c.name) >', \122, ') AND (ASCII(c.name) <', \65, 'OR ASCII(c.name) >', \90, ')') : ();
+        defined($opt->{ch}) ? sql 'match_firstchar(c.name, ', \$opt->{ch}, ')' : ();
 
     my $time = time;
     my($count, $list);
diff --git a/lib/VNWeb/Producers/List.pm b/lib/VNWeb/Producers/List.pm
index 741e74f6..9697cd66 100644
--- a/lib/VNWeb/Producers/List.pm
+++ b/lib/VNWeb/Producers/List.pm
@@ -41,8 +41,7 @@ TUWF::get qr{/p(?:/(?<char>all|[a-z0]))?}, sub {
 
     my $where = sql_and 'NOT p.hidden', $opt->{f}->sql_where(),
         $opt->{q} ? sql 'p.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (),
-        defined($opt->{ch}) && $opt->{ch} ? sql('LOWER(SUBSTR(p.name, 1, 1)) =', \$opt->{ch}) : (),
-        defined($opt->{ch}) && !$opt->{ch} ? sql('(ASCII(p.name) <', \97, 'OR ASCII(p.name) >', \122, ') AND (ASCII(p.name) <', \65, 'OR ASCII(p.name) >', \90, ')') : ();
+        defined($opt->{ch}) ? sql 'match_firstchar(p.name, ', \$opt->{ch}, ')' : ();
 
     my $time = time;
     my($count, $list);
diff --git a/lib/VNWeb/Staff/List.pm b/lib/VNWeb/Staff/List.pm
index 77f930cd..9c4e6789 100644
--- a/lib/VNWeb/Staff/List.pm
+++ b/lib/VNWeb/Staff/List.pm
@@ -55,8 +55,7 @@ TUWF::get qr{/s(?:/(?<char>all|[a-z0]))?}, sub {
         $opt->{n} ? 's.aid = sa.aid' : (),
         'NOT s.hidden', $opt->{f}->sql_where(),
         $opt->{q} ? sql 'sa.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (),
-        defined($opt->{ch}) && $opt->{ch} ? sql('LOWER(SUBSTR(sa.name, 1, 1)) =', \$opt->{ch}) : (),
-        defined($opt->{ch}) && !$opt->{ch} ? sql('(ASCII(sa.name) <', \97, 'OR ASCII(sa.name) >', \122, ') AND (ASCII(sa.name) <', \65, 'OR ASCII(sa.name) >', \90, ')') : ();
+        defined($opt->{ch}) ? sql 'match_firstchar(sa.name, ', \$opt->{ch}, ')' : ();
 
     my $time = time;
     my($count, $list);
diff --git a/lib/VNWeb/ULists/List.pm b/lib/VNWeb/ULists/List.pm
index 833b3556..0df4ac41 100644
--- a/lib/VNWeb/ULists/List.pm
+++ b/lib/VNWeb/ULists/List.pm
@@ -270,8 +270,7 @@ sub listing_ {
         !$own ? sql('uv.vid IN(SELECT vid FROM ulist_vns_labels WHERE uid =', \$uid, 'AND lbl IN(SELECT id FROM ulist_labels WHERE uid =', \$uid, 'AND NOT private))') : (),
         @where_vns ? sql_or(@where_vns) : (),
         $opt->{q} ? sql 'v.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (),
-        defined($opt->{ch}) && $opt->{ch} ? sql('LOWER(SUBSTR(v.title, 1, 1)) =', \$opt->{ch}) : (),
-        defined($opt->{ch}) && !$opt->{ch} ? sql('(ASCII(v.title) <', \97, 'OR ASCII(v.title) >', \122, ') AND (ASCII(v.title) <', \65, 'OR ASCII(v.title) >', \90, ')') : ();
+        defined($opt->{ch}) ? sql 'match_firstchar(v.title, ', \$opt->{ch}, ')' : ();
 
     my $count = tuwf->dbVali('SELECT count(*) FROM ulist_vns uv JOIN vn v ON v.id = uv.vid WHERE', $where);
 
diff --git a/lib/VNWeb/User/List.pm b/lib/VNWeb/User/List.pm
index a67d5805..c7e8d288 100644
--- a/lib/VNWeb/User/List.pm
+++ b/lib/VNWeb/User/List.pm
@@ -67,7 +67,7 @@ TUWF::get qr{/u/(?<char>[0a-z]|all)}, sub {
     )->data;
 
     my @where = (
-        $char eq 'all' ? () : $char eq '0' ? "ascii(lower(username)) not between ascii('a') and ascii('z')" : "lower(username) like '$char%'",
+        $char eq 'all' ? () : sql('match_firstchar(username, ', \$char, ')'),
         $opt->{q} ? sql_or(
             auth->permUsermod && $opt->{q} =~ /@/ ? sql('id IN(SELECT y FROM user_emailtoid(', \$opt->{q}, ') x(y))') : (),
             $opt->{q} =~ /^u?([0-9]{1,6})$/ ? sql 'id =', \"u$1" : (),
diff --git a/lib/VNWeb/VN/List.pm b/lib/VNWeb/VN/List.pm
index 5c08727c..62106c4d 100644
--- a/lib/VNWeb/VN/List.pm
+++ b/lib/VNWeb/VN/List.pm
@@ -264,8 +264,7 @@ TUWF::get qr{/v(?:/(?<char>all|[a-z0]))?}, sub {
     my $where = sql_and
         'NOT v.hidden', $opt->{f}->sql_where(),
         $opt->{q} ? sql 'v.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (),
-        defined($opt->{ch}) && $opt->{ch} ? sql('LOWER(SUBSTR(v.title, 1, 1)) =', \$opt->{ch}) : (),
-        defined($opt->{ch}) && !$opt->{ch} ? sql('(ASCII(v.title) <', \97, 'OR ASCII(v.title) >', \122, ') AND (ASCII(v.title) <', \65, 'OR ASCII(v.title) >', \90, ')') : ();
+        defined($opt->{ch}) ? sql 'match_firstchar(v.title, ', \$opt->{ch}, ')' : ();
 
     my $time = time;
     my($count, $list);