DB: Abstracted all ORDER BY clauses in the DB abstraction layer

The ORDER BY was previously specified using an 'order' argument, which
would then be directly inserted into the query. The new method is the
same as what I used for the public API: two 'sort' and 'reverse'
arguments. This should be less error-prone and more readable.

This changes quite a lot of code, so I hope I haven't forgotten to
update something along the way.

1 files changed, 20 insertions, 10 deletions

diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm
index 0f54686c..a86d7b12 100644
--- a/lib/VNDB/DB/ULists.pm
+++ b/lib/VNDB/DB/ULists.pm
@@ -35,14 +35,14 @@ sub dbVNListGet {
 }
 
 
-# %options->{ uid order char voted page results }
+# %options->{ uid char voted page results sort reverse }
+# sort: title vote
 # NOTE: this function is mostly copied from 1.x, may need some rewriting...
 sub dbVNListList {
   my($self, %o) = @_;
 
   $o{results} ||= 50;
   $o{page}    ||= 1;
-  $o{order}   ||= 'vr.title ASC';
   $o{voted}   ||= 0;  # -1: only non-voted, 0: all, 1: only voted
 
   # construct the global WHERE clause
@@ -58,6 +58,11 @@ sub dbVNListList {
   $where = '('.$where.') AND (ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' if defined $o{char} && !$o{char};
   $where = '('.$where.') AND vo.vote IS NULL' if $o{voted} == -1;
 
+  my $order = sprintf {
+    title => 'vr.title %s',
+    vote  => 'vo.vote %s NULLS LAST, vr.title ASC',
+  }->{ $o{sort}||'title' }, $o{reverse} ? 'DESC' : 'ASC';
+
   # execute query
   my($r, $np) = $self->dbPage(\%o, qq|
     SELECT vr.vid, vr.title, vr.original, v.c_released, v.c_languages, v.c_platforms, COALESCE(vo.vote, 0) AS vote
@@ -67,7 +72,7 @@ sub dbVNListList {
       WHERE $where
       ORDER BY !s|,
     $o{voted} == 1 ? '' : 'LEFT', $o{uid},   # JOIN if we only want votes, LEFT JOIN if we also want rlist items
-    $o{voted} != 1 ? $o{uid} : (), $o{order},
+    $o{voted} != 1 ? $o{uid} : (), $order
   );
 
   # fetch releases and link to VNs
@@ -142,11 +147,10 @@ sub dbVNListDel {
 }
 
 
-# %options->{ uid vid hide order results page what }
+# %options->{ uid vid hide results page what }
 # what: user, vn
 sub dbVoteGet {
   my($self, %o) = @_;
-  $o{order} ||= 'n.date DESC';
   $o{results} ||= 50;
   $o{page} ||= 1;
   $o{what} ||= '';
@@ -179,8 +183,8 @@ sub dbVoteGet {
       FROM votes n
       !s
       !W
-      ORDER BY !s|,
-    join(',', @select), join(' ', @join), \%where, $o{order}
+      ORDER BY n.date DESC|,
+    join(',', @select), join(' ', @join), \%where
   );
 
   return wantarray ? ($r, $np) : $r;
@@ -234,12 +238,12 @@ sub dbVoteDel {
 }
 
 
-# %options->{ uid vid wstat what order page results }
+# %options->{ uid vid wstat what page results sort reverse }
 # what: vn
+# sort: title added wstat
 sub dbWishListGet {
   my($self, %o) = @_;
 
-  $o{order} ||= 'wl.wstat ASC';
   $o{page} ||= 1;
   $o{results} ||= 50;
   $o{what} ||= '';
@@ -258,13 +262,19 @@ sub dbWishListGet {
     'JOIN vn_rev vr ON vr.id = v.latest';
   }
 
+  my $order = sprintf  {
+    title => 'vr.title %s',
+    added => 'wl.added %s',
+    wstat => 'wl.wstat %2$s, vr.title ASC',
+  }->{ $o{sort}||'wstat' }, $o{reverse} ? 'DESC' : 'ASC', $o{reverse} ? 'ASC' : 'DESC';
+
   my($r, $np) = $self->dbPage(\%o, q|
     SELECT !s
       FROM wlists wl
       !s
       !W
       ORDER BY !s|,
-    $select, join(' ', @join), \%where, $o{order},
+    $select, join(' ', @join), \%where, $order,
   );
 
   return wantarray ? ($r, $np) : $r;