Merge branch 'sorting' into 0.9.x

10 files changed, 223 insertions, 120 deletions

diff --git a/actions/showstream.php b/actions/showstream.php
index 5a22bdf28..8a67d3fc9 100644
--- a/actions/showstream.php
+++ b/actions/showstream.php
@@ -317,7 +317,7 @@ class ProfileNoticeListItem extends DoFollowListItem
                            'class' => 'url');
 
             if (!empty($this->profile->fullname)) {
-                $attrs['title'] = $this->getFancyName();
+                $attrs['title'] = $this->profile->getFancyName();
             }
 
             $this->out->elementStart('span', 'repeat');
diff --git a/classes/Fave.php b/classes/Fave.php
index 3aa23e7b4..4a9cfaae0 100644
--- a/classes/Fave.php
+++ b/classes/Fave.php
@@ -85,6 +85,19 @@ class Fave extends Memcached_DataObject
         return $ids;
     }
 
+    /**
+     * Note that the sorting for this is by order of *fave* not order of *notice*.
+     *
+     * @fixme add since_id, max_id support?
+     *
+     * @param <type> $user_id
+     * @param <type> $own
+     * @param <type> $offset
+     * @param <type> $limit
+     * @param <type> $since_id
+     * @param <type> $max_id
+     * @return <type>
+     */
     function _streamDirect($user_id, $own, $offset, $limit, $since_id, $max_id)
     {
         $fav = new Fave();
diff --git a/classes/Notice.php b/classes/Notice.php
index a067cd374..629b7089d 100644
--- a/classes/Notice.php
+++ b/classes/Notice.php
@@ -654,7 +654,7 @@ class Notice extends Memcached_DataObject
         $notice->selectAdd(); // clears it
         $notice->selectAdd('id');
 
-        $notice->orderBy('id DESC');
+        $notice->orderBy('created DESC, id DESC');
 
         if (!is_null($offset)) {
             $notice->limit($offset, $limit);
@@ -668,13 +668,8 @@ class Notice extends Memcached_DataObject
             $notice->whereAdd('is_local !='. Notice::GATEWAY);
         }
 
-        if ($since_id != 0) {
-            $notice->whereAdd('id > ' . $since_id);
-        }
-
-        if ($max_id != 0) {
-            $notice->whereAdd('id <= ' . $max_id);
-        }
+        Notice::addWhereSinceId($notice, $since_id);
+        Notice::addWhereMaxId($notice, $max_id);
 
         $ids = array();
 
@@ -709,19 +704,14 @@ class Notice extends Memcached_DataObject
 
         $notice->conversation = $id;
 
-        $notice->orderBy('id DESC');
+        $notice->orderBy('created DESC, id DESC');
 
         if (!is_null($offset)) {
             $notice->limit($offset, $limit);
         }
 
-        if ($since_id != 0) {
-            $notice->whereAdd('id > ' . $since_id);
-        }
-
-        if ($max_id != 0) {
-            $notice->whereAdd('id <= ' . $max_id);
-        }
+        Notice::addWhereSinceId($notice, $since_id);
+        Notice::addWhereMaxId($notice, $max_id);
 
         $ids = array();
 
@@ -1695,10 +1685,10 @@ class Notice extends Memcached_DataObject
 
         $notice->repeat_of = $this->id;
 
-        $notice->orderBy('created'); // NB: asc!
+        $notice->orderBy('created, id'); // NB: asc!
 
-        if (!is_null($offset)) {
-            $notice->limit($offset, $limit);
+        if (!is_null($limit)) {
+            $notice->limit(0, $limit);
         }
 
         $ids = array();
@@ -1978,4 +1968,108 @@ class Notice extends Memcached_DataObject
         $d = new DateTime($dateStr, new DateTimeZone('UTC'));
         return $d->format(DATE_W3C);
     }
+
+    /**
+     * Look up the creation timestamp for a given notice ID, even
+     * if it's been deleted.
+     *
+     * @param int $id
+     * @return mixed string recorded creation timestamp, or false if can't be found
+     */
+    public static function getAsTimestamp($id)
+    {
+        if (!$id) {
+            return false;
+        }
+
+        $notice = Notice::staticGet('id', $id);
+        if ($notice) {
+            return $notice->created;
+        }
+
+        $deleted = Deleted_notice::staticGet('id', $id);
+        if ($deleted) {
+            return $deleted->created;
+        }
+
+        return false;
+    }
+
+    /**
+     * Build an SQL 'where' fragment for timestamp-based sorting from a since_id
+     * parameter, matching notices posted after the given one (exclusive).
+     *
+     * If the referenced notice can't be found, will return false.
+     *
+     * @param int $id
+     * @param string $idField
+     * @param string $createdField
+     * @return mixed string or false if no match
+     */
+    public static function whereSinceId($id, $idField='id', $createdField='created')
+    {
+        $since = Notice::getAsTimestamp($id);
+        if ($since) {
+            return sprintf("($createdField = '%s' and $idField > %d) or ($createdField > '%s')", $since, $id, $since);
+        }
+        return false;
+    }
+
+    /**
+     * Build an SQL 'where' fragment for timestamp-based sorting from a since_id
+     * parameter, matching notices posted after the given one (exclusive), and
+     * if necessary add it to the data object's query.
+     *
+     * @param DB_DataObject $obj
+     * @param int $id
+     * @param string $idField
+     * @param string $createdField
+     * @return mixed string or false if no match
+     */
+    public static function addWhereSinceId(DB_DataObject $obj, $id, $idField='id', $createdField='created')
+    {
+        $since = self::whereSinceId($id);
+        if ($since) {
+            $obj->whereAdd($since);
+        }
+    }
+
+    /**
+     * Build an SQL 'where' fragment for timestamp-based sorting from a max_id
+     * parameter, matching notices posted before the given one (inclusive).
+     *
+     * If the referenced notice can't be found, will return false.
+     *
+     * @param int $id
+     * @param string $idField
+     * @param string $createdField
+     * @return mixed string or false if no match
+     */
+    public static function whereMaxId($id, $idField='id', $createdField='created')
+    {
+        $max = Notice::getAsTimestamp($id);
+        if ($max) {
+            return sprintf("($createdField < '%s') or ($createdField = '%s' and $idField <= %d)", $max, $max, $id);
+        }
+        return false;
+    }
+
+    /**
+     * Build an SQL 'where' fragment for timestamp-based sorting from a max_id
+     * parameter, matching notices posted before the given one (inclusive), and
+     * if necessary add it to the data object's query.
+     *
+     * @param DB_DataObject $obj
+     * @param int $id
+     * @param string $idField
+     * @param string $createdField
+     * @return mixed string or false if no match
+     */
+    public static function addWhereMaxId(DB_DataObject $obj, $id, $idField='id', $createdField='created')
+    {
+        $max = self::whereMaxId($id);
+        if ($max) {
+            $obj->whereAdd($max);
+        }
+    }
 }
diff --git a/classes/Notice_tag.php b/classes/Notice_tag.php
index 6eada7022..bb67c8f81 100644
--- a/classes/Notice_tag.php
+++ b/classes/Notice_tag.php
@@ -55,15 +55,10 @@ class Notice_tag extends Memcached_DataObject
         $nt->selectAdd();
         $nt->selectAdd('notice_id');
 
-        if ($since_id != 0) {
-            $nt->whereAdd('notice_id > ' . $since_id);
-        }
-
-        if ($max_id != 0) {
-            $nt->whereAdd('notice_id <= ' . $max_id);
-        }
+        Notice::addWhereSinceId($nt, $since_id, 'notice_id');
+        Notice::addWhereMaxId($nt, $max_id, 'notice_id');
 
-        $nt->orderBy('notice_id DESC');
+        $nt->orderBy('created DESC, notice_id DESC');
 
         if (!is_null($offset)) {
             $nt->limit($offset, $limit);
diff --git a/classes/Profile.php b/classes/Profile.php
index 332d51e20..fe1a070bd 100644
--- a/classes/Profile.php
+++ b/classes/Profile.php
@@ -215,26 +215,29 @@ class Profile extends Memcached_DataObject
     function _streamTaggedDirect($tag, $offset, $limit, $since_id, $max_id)
     {
         // XXX It would be nice to do this without a join
+        // (necessary to do it efficiently on accounts with long history)
 
         $notice = new Notice();
 
         $query =
           "select id from notice join notice_tag on id=notice_id where tag='".
           $notice->escape($tag) .
-          "' and profile_id=" . $notice->escape($this->id);
+          "' and profile_id=" . intval($this->id);
 
-        if ($since_id != 0) {
-            $query .= " and id > $since_id";
+        $since = Notice::whereSinceId($since_id, 'id', 'notice.created');
+        if ($since) {
+            $query .= " and ($since)";
         }
 
-        if ($max_id != 0) {
-            $query .= " and id <= $max_id";
+        $max = Notice::whereMaxId($max_id, 'id', 'notice.created');
+        if ($max) {
+            $query .= " and ($max)";
         }
 
-        $query .= ' order by id DESC';
+        $query .= ' order by notice.created DESC, id DESC';
 
         if (!is_null($offset)) {
-            $query .= " LIMIT $limit OFFSET $offset";
+            $query .= " LIMIT " . intval($limit) . " OFFSET " . intval($offset);
         }
 
         $notice->query($query);
@@ -252,58 +255,22 @@ class Profile extends Memcached_DataObject
     {
         $notice = new Notice();
 
-        // Temporary hack until notice_profile_id_idx is updated
-        // to (profile_id, id) instead of (profile_id, created, id).
-        // It's been falling back to PRIMARY instead, which is really
-        // very inefficient for a profile that hasn't posted in a few
-        // months. Even though forcing the index will cause a filesort,
-        // it's usually going to be better.
-        if (common_config('db', 'type') == 'mysql') {
-            $index = '';
-            $query =
-              "select id from notice force index (notice_profile_id_idx) ".
-              "where profile_id=" . $notice->escape($this->id);
-
-            if ($since_id != 0) {
-                $query .= " and id > $since_id";
-            }
-
-            if ($max_id != 0) {
-                $query .= " and id <= $max_id";
-            }
-
-            $query .= ' order by id DESC';
-
-            if (!is_null($offset)) {
-                $query .= " LIMIT $limit OFFSET $offset";
-            }
-
-            $notice->query($query);
-        } else {
-            $index = '';
-
-            $notice->profile_id = $this->id;
-
-            $notice->selectAdd();
-            $notice->selectAdd('id');
-
-            if ($since_id != 0) {
-                $notice->whereAdd('id > ' . $since_id);
-            }
+        $notice->profile_id = $this->id;
 
-            if ($max_id != 0) {
-                $notice->whereAdd('id <= ' . $max_id);
-            }
+        $notice->selectAdd();
+        $notice->selectAdd('id');
 
-            $notice->orderBy('id DESC');
+        Notice::addWhereSinceId($notice, $since_id);
+        Notice::addWhereMaxId($notice, $max_id);
 
-            if (!is_null($offset)) {
-                $notice->limit($offset, $limit);
-            }
+        $notice->orderBy('created DESC, id DESC');
 
-            $notice->find();
+        if (!is_null($offset)) {
+            $notice->limit($offset, $limit);
         }
 
+        $notice->find();
+
         $ids = array();
 
         while ($notice->fetch()) {
diff --git a/classes/Reply.php b/classes/Reply.php
index da8a4f685..371c16cf4 100644
--- a/classes/Reply.php
+++ b/classes/Reply.php
@@ -50,15 +50,10 @@ class Reply extends Memcached_DataObject
         $reply = new Reply();
         $reply->profile_id = $user_id;
 
-        if ($since_id != 0) {
-            $reply->whereAdd('notice_id > ' . $since_id);
-        }
-
-        if ($max_id != 0) {
-            $reply->whereAdd('notice_id <= ' . $max_id);
-        }
+        Notice::addWhereSinceId($reply, $since_id, 'notice_id', 'modified');
+        Notice::addWhereMaxId($reply, $max_id, 'notice_id', 'modified');
 
-        $reply->orderBy('notice_id DESC');
+        $reply->orderBy('modified DESC, notice_id DESC');
 
         if (!is_null($offset)) {
             $reply->limit($offset, $limit);
diff --git a/classes/User.php b/classes/User.php
index 1b1b971ec..47bf8a617 100644
--- a/classes/User.php
+++ b/classes/User.php
@@ -755,19 +755,14 @@ class User extends Memcached_DataObject
         $notice->profile_id = $this->id;
         $notice->whereAdd('repeat_of IS NOT NULL');
 
-        $notice->orderBy('id DESC');
+        $notice->orderBy('created DESC, id DESC');
 
         if (!is_null($offset)) {
             $notice->limit($offset, $limit);
         }
 
-        if ($since_id != 0) {
-            $notice->whereAdd('id > ' . $since_id);
-        }
-
-        if ($max_id != 0) {
-            $notice->whereAdd('id <= ' . $max_id);
-        }
+        Notice::addWhereSinceId($notice, $since_id);
+        Notice::addWhereMaxId($notice, $max_id);
 
         $ids = array();
 
@@ -800,17 +795,17 @@ class User extends Memcached_DataObject
           'FROM notice original JOIN notice rept ON original.id = rept.repeat_of ' .
           'WHERE original.profile_id = ' . $this->id . ' ';
 
-        if ($since_id != 0) {
-            $qry .= 'AND original.id > ' . $since_id . ' ';
+        $since = Notice::whereSinceId($since_id, 'original.id', 'original.created');
+        if ($since) {
+            $qry .= "AND ($since) ";
         }
 
-        if ($max_id != 0) {
-            $qry .= 'AND original.id <= ' . $max_id . ' ';
+        $max = Notice::whereMaxId($max_id, 'original.id', 'original.created');
+        if ($max) {
+            $qry .= "AND ($max) ";
         }
 
-        // NOTE: we sort by fave time, not by notice time!
-
-        $qry .= 'ORDER BY original.id DESC ';
+        $qry .= 'ORDER BY original.created, original.id DESC ';
 
         if (!is_null($offset)) {
             $qry .= "LIMIT $limit OFFSET $offset";
diff --git a/classes/User_group.php b/classes/User_group.php
index 60217e960..cffc78645 100644
--- a/classes/User_group.php
+++ b/classes/User_group.php
@@ -100,15 +100,10 @@ class User_group extends Memcached_DataObject
         $inbox->selectAdd();
         $inbox->selectAdd('notice_id');
 
-        if ($since_id != 0) {
-            $inbox->whereAdd('notice_id > ' . $since_id);
-        }
-
-        if ($max_id != 0) {
-            $inbox->whereAdd('notice_id <= ' . $max_id);
-        }
+        Notice::addWhereSinceId($inbox, $since_id, 'notice_id');
+        Notice::addWhereMaxId($inbox, $max_id, 'notice_id');
 
-        $inbox->orderBy('notice_id DESC');
+        $inbox->orderBy('created DESC, notice_id DESC');
 
         if (!is_null($offset)) {
             $inbox->limit($offset, $limit);
diff --git a/db/096to097.sql b/db/096to097.sql
new file mode 100644
index 000000000..209a3a881
--- /dev/null
+++ b/db/096to097.sql
@@ -0,0 +1,26 @@
+-- Add indexes for sorting changes in 0.9.7
+
+-- Allows sorting public timeline, api/statuses/repeats, and conversations by timestamp efficiently
+alter table notice
+    add index notice_created_id_is_local_idx (created,id,is_local),
+
+    add index notice_repeat_of_created_id_idx (repeat_of, created, id),
+    drop index notice_repeatof_idx,
+
+    add index notice_conversation_created_id_idx (conversation, created, id),
+    drop index notice_conversation_idx;
+
+-- Allows sorting tag-filtered public timeline by timestamp efficiently
+alter table notice_tag add index notice_tag_tag_created_notice_id_idx (tag, created, notice_id);
+
+-- Needed for sorting reply/mentions timelines
+alter table reply add index reply_profile_id_modified_notice_id_idx (profile_id, modified, notice_id);
+
+-- Needed for sorting group messages by timestamp
+alter table group_inbox add index group_inbox_group_id_created_notice_id_idx (group_id, created, notice_id);
+
+-- Helps make some reverse role lookups more efficient if there's a lot of assigned accounts
+alter table profile_role add index profile_role_role_created_profile_id_idx (role, created, profile_id);
+
+-- Fix for sorting a user's group memberships by order joined
+alter table group_member add index group_member_profile_id_created_idx (profile_id, created);
diff --git a/db/statusnet.sql b/db/statusnet.sql
index ac48e6253..4a24d016a 100644
--- a/db/statusnet.sql
+++ b/db/statusnet.sql
@@ -131,11 +131,21 @@ create table notice (
     location_ns integer comment 'namespace for location',
     repeat_of integer comment 'notice this is a repeat of' references notice (id),
 
+    -- For public timeline...
+    index notice_created_id_is_local_idx (created,id,is_local),
+
+    -- For profile timelines...
     index notice_profile_id_idx (profile_id,created,id),
-    index notice_conversation_idx (conversation),
-    index notice_created_idx (created),
+
+    -- For api/statuses/repeats...
+    index notice_repeat_of_created_id_idx (repeat_of, created, id),
+
+    -- For conversation views
+    index notice_conversation_created_id_idx (conversation, created, id),
+
+    -- Are these needed/used?
     index notice_replyto_idx (reply_to),
-    index notice_repeatof_idx (repeat_of),
+
     FULLTEXT(content)
 ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
 
@@ -156,7 +166,10 @@ create table reply (
     constraint primary key (notice_id, profile_id),
     index reply_notice_id_idx (notice_id),
     index reply_profile_id_idx (profile_id),
-    index reply_replied_id_idx (replied_id)
+    index reply_replied_id_idx (replied_id),
+
+    -- Needed for sorting reply/mentions timelines
+    index reply_profile_id_modified_notice_id_idx (profile_id, modified, notice_id)
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
@@ -301,7 +314,10 @@ create table notice_tag (
 
     constraint primary key (tag, notice_id),
     index notice_tag_created_idx (created),
-    index notice_tag_notice_id_idx (notice_id)
+    index notice_tag_notice_id_idx (notice_id),
+
+    -- For sorting tag-filtered public timeline
+    index notice_tag_tag_created_notice_id_idx (tag, created, notice_id)
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
 /* Synching with foreign services */
@@ -447,7 +463,10 @@ create table group_member (
 
     constraint primary key (group_id, profile_id),
     index group_member_profile_id_idx (profile_id),
-    index group_member_created_idx (created)
+    index group_member_created_idx (created),
+
+    -- To pull up a list of someone's groups in order joined
+    index group_member_profile_id_created_idx (profile_id, created)
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
@@ -468,7 +487,10 @@ create table group_inbox (
 
     constraint primary key (group_id, notice_id),
     index group_inbox_created_idx (created),
-    index group_inbox_notice_id_idx (notice_id)
+    index group_inbox_notice_id_idx (notice_id),
+
+    -- Needed for sorting group messages by timestamp
+    index group_inbox_group_id_created_notice_id_idx (group_id, created, notice_id)
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
@@ -608,7 +630,8 @@ create table profile_role (
     role    varchar(32) not null comment 'string representing the role',
     created datetime not null comment 'date the role was granted',
 
-    constraint primary key (profile_id, role)
+    constraint primary key (profile_id, role),
+    index profile_role_role_created_profile_id_idx (role, created, profile_id)
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;