Allow trivia rank to take an argument

10:09 <+Orillion> ALTER TABLE `pypsd`.`trivia_scores`
10:09 <+Orillion> ADD INDEX `idx` USING BTREE (`channel` ASC, `points` ASC);

Squashed commit of the following:

commit b53f2eaee188fcd5b68554262492197c96f78f71
Author: Adam <redacted>
Date:   Thu Oct 9 16:27:10 2014 -0400

    Whitespace removal

commit 06527500b15f3150a4d65388be4d829220541d92
Author: Michiel <redacted>
Date:   Wed Oct 1 16:34:17 2014 +0200

    Fixed type in function call.
    1 points is now 1 point, because we love correct grammar.

commit f4b08523734c0f64940702c962e219d8a00744cf
Author: Michiel <redacted>
Date:   Wed Oct 1 16:20:26 2014 +0200

    Obsolete check not as obsolete as it looks!

commit 712bbdb82f4b4aff894ca8543e143f02a86cd4fc
Author: Michiel <redacted>
Date:   Wed Oct 1 16:10:17 2014 +0200

    Index is now (channel, points)

commit a58c2e216a6d26cc72787ee5b017eb9b9b12a70d
Author: Michiel <redacted>
Date:   Wed Oct 1 16:04:30 2014 +0200

    Changed INDEX on trivia_scores from NICK to POINTS column

commit 00ab43b5db63b0aa041d9c34bb337a354a9bad8d
Author: Michiel <redacted>
Date:   Wed Oct 1 16:01:55 2014 +0200

    Removed obsolete check.

commit 6ce6a94510afbd657e75a8a64c8cfefd1dd26ed7
Author: Michiel <redacted>
Date:   Wed Oct 1 15:57:13 2014 +0200

    New SQL query for .rank.
    Added indices to Trivia Scores table.
    .rank now returns a notice if a nickname cannot be found.

commit dda59af7d490d1d29382e087fb1b93275d357591
Author: Ethan Jones <redacted>
Date:   Wed Oct 1 02:09:50 2014 -0500

    fixed trivia message return grammar

commit b170d604a459133288512b1336ea9824d3631281
Author: ethan jones <redacted>
Date:   Fri Sep 26 14:13:38 2014 -0400

    allows .rank to take an argument, i think

diff --git a/pyva/pyva/src/main/python/trivia/trivia.py b/pyva/pyva/src/main/python/trivia/trivia.py
index f59f6ee928fc5e599632f71c25a7bedd5b632456..19dccc52d0848885ca8c0bcfceba9b9aec495cc6 100644 (file)
--- a/pyva/pyva/src/main/python/trivia/trivia.py
+++ b/pyva/pyva/src/main/python/trivia/trivia.py
@@ -85,7 +85,7 @@ class trivia(
        def start(self):
                try:
                        self.dbp.execute("CREATE TABLE IF NOT EXISTS trivia_chans (id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200) NOT NULL, theme VARCHAR(64), UNIQUE KEY(name)) ENGINE=MyISAM")
-                       self.dbp.execute("CREATE TABLE IF NOT EXISTS trivia_scores (nick VARCHAR(30), points INT(10) DEFAULT '0', fastest_time FLOAT, highest_streak INT(10), channel INT(10)) ENGINE=MyISAM")
+                       self.dbp.execute("CREATE TABLE IF NOT EXISTS trivia_scores (nick VARCHAR(30) DEFAULT NULL, points INT(10) DEFAULT '0', fastest_time FLOAT DEFAULT NULL, highest_streak INT(10) DEFAULT NULL, channel INT(10) DEFAULT NULL, KEY `idx` (`channel`,`points`) USING BTREE) ENGINE=MyISAM DEFAULT CHARSET=latin1;")
                except Exception, err:
                        self.log.exception("Error creating table for trivia module (%s)" % err)
                        raise
@@ -265,19 +265,26 @@ class trivia(
 
                                        self.notice(sender, '; '.join(out))
                                elif command == 'rank':
-                                       self.dbp.execute("SELECT nick, points FROM trivia_scores WHERE channel = %s ORDER BY points DESC", (self.get_cid(channel),))
+                                       if arg != '':
+                                               querynick = arg.lower()
+                                       else:
+                                               querynick = sender.lower()
+                                       # Select user and user above this user in scoring list.
+                                       querystring = "SELECT x.nick, x.position, x.points, x.channel FROM (SELECT t.nick, t.points, t.channel, @rownum := @rownum + 1 AS position FROM trivia_scores AS t JOIN (SELECT @rownum := 0) r ON t.channel = %s ORDER BY t.points DESC) x WHERE x.nick >= %s ORDER BY x.position DESC LIMIT 2;"
+
+                                       self.dbp.execute(querystring, (self.get_cid(channel), querynick))
                                        rows = self.dbp.fetchall()
-                                       out = ""
-                                       # XXX: This is inefficient
-                                       for i, row in enumerate(rows):
-                                               if row[0].lower() == sender.lower():
-                                                       out = "You are currently ranked #%d with %d points" % (i+1, row[1])
-                                                       if i > 0:
-                                                               out += ", %d points behind %s" % (rows[i-1][1] - row[1], rows[i-1][0])
-                                                       out += "."
-
-                                       if out != "":
+                                       if len(rows) >= 1 and rows[0][0].lower() == querynick:
+                                               userdata = rows[0]
+                                               out = "%s is currently ranked #%d with %d %s" % (userdata[0], userdata[1], userdata[2], "point" if userdata[2] == 1 else "points")
+                                               if len(rows) == 2:
+                                                       otherdata = rows[1]
+                                                       diff = otherdata[2] - userdata[2]
+                                                       out += ", %d %s behind %s" % (diff, "point" if diff == 1 else "points", otherdata[0])
+                                               out += "."
                                                self.notice(sender, out)
+                                       else:
+                                               self.notice(sender, "Nickname not found.")
                                #elif command == 'next': # Defunct in orig trivia
                                        #pass
                                elif command == 'themes':