Twitter-compatible API - since_id and before_id were polluting the cache and generating bad SQL

(Also cleaned out some extra whitespace.)

darcs-hash:20081104053359-462f3-2f6619bb942aa34b38dd82cb427878f83d4c332c.gz

2 files changed, 90 insertions, 107 deletions

diff --git a/classes/Notice.php b/classes/Notice.php
index a3886d165..961af0919 100644
--- a/classes/Notice.php
+++ b/classes/Notice.php
@@ -29,7 +29,7 @@ require_once INSTALLDIR.'/classes/Memcached_DataObject.php';
 
 define('NOTICE_CACHE_WINDOW', 61);
 
-class Notice extends Memcached_DataObject 
+class Notice extends Memcached_DataObject
 {
     ###START_AUTOCODE
     /* the code below is auto generated do not remove the above tag */
@@ -38,14 +38,14 @@ class Notice extends Memcached_DataObject
     public $id;                              // int(4)  primary_key not_null
     public $profile_id;                      // int(4)   not_null
     public $uri;                             // varchar(255)  unique_key
-    public $content;                         // varchar(140)  
-    public $rendered;                        // text()  
-    public $url;                             // varchar(255)  
+    public $content;                         // varchar(140)
+    public $rendered;                        // text()
+    public $url;                             // varchar(255)
     public $created;                         // datetime()   not_null
     public $modified;                        // timestamp()   not_null default_CURRENT_TIMESTAMP
-    public $reply_to;                        // int(4)  
-    public $is_local;                        // tinyint(1)  
-    public $source;                          // varchar(32)  
+    public $reply_to;                        // int(4)
+    public $is_local;                        // tinyint(1)
+    public $source;                          // varchar(32)
 
     /* Static get */
     function staticGet($k,$v=NULL) { return Memcached_DataObject::staticGet('Notice',$k,$v); }
@@ -63,14 +63,14 @@ class Notice extends Memcached_DataObject
 		$this->blowInboxes();
 		parent::delete();
 	}
-	
+
 	function saveTags() {
 		/* extract all #hastags */
 		$count = preg_match_all('/(?:^|\s)#([A-Za-z0-9_\-\.]{1,64})/', strtolower($this->content), $match);
 		if (!$count) {
 			return true;
 		}
-		
+
 		/* elide characters we don't want in the tag */
 		$match[1] = str_replace(array('-', '_', '.'), '', $match[1]);
 
@@ -92,7 +92,7 @@ class Notice extends Memcached_DataObject
 	}
 
 	static function saveNew($profile_id, $content, $source=NULL, $is_local=1, $reply_to=NULL, $uri=NULL) {
-		
+
 		$notice = new Notice();
 		$notice->profile_id = $profile_id;
 		$notice->is_local = $is_local;
@@ -102,7 +102,7 @@ class Notice extends Memcached_DataObject
 		$notice->rendered = common_render_content($notice->content, $notice);
 		$notice->source = $source;
 		$notice->uri = $uri;
-		
+
 		$id = $notice->insert();
 
 		if (!$id) {
@@ -122,13 +122,13 @@ class Notice extends Memcached_DataObject
 		}
 
 		# XXX: do we need to change this for remote users?
-		
+
 		common_save_replies($notice);
 		$notice->saveTags();
 
 		# Clear the cache for subscribed users, so they'll update at next request
 		# XXX: someone clever could prepend instead of clearing the cache
-		
+
 		if (common_config('memcached', 'enabled')) {
 			$notice->blowCaches();
 		}
@@ -159,20 +159,20 @@ class Notice extends Memcached_DataObject
 			unset($tag);
 		}
 	}
-	
+
 	function blowSubsCache() {
 		$cache = common_memcache();
 		if ($cache) {
 			$user = new User();
-			
+
 			$user->query('SELECT id ' .
 						 'FROM user JOIN subscription ON user.id = subscription.subscriber ' .
 						 'WHERE subscription.subscribed = ' . $this->profile_id);
-			
+
 			while ($user->fetch()) {
 				$cache->delete(common_cache_key('user:notices_with_friends:' . $user->id));
 			}
-			
+
 			$user->free();
 			unset($user);
 		}
@@ -225,22 +225,59 @@ class Notice extends Memcached_DataObject
 			unset($fave);
 		}
 	}
-	
-	static function getStream($qry, $cachekey, $offset=0, $limit=20) {
-		
+
+	static function getStream($qry, $cachekey, $offset=0, $limit=20, $since_id=0, $before_id=0) {
+
 		if (common_config('memcached', 'enabled')) {
-			return Notice::getCachedStream($qry, $cachekey, $offset, $limit);
-		} else {
-			return Notice::getStreamDirect($qry, $offset, $limit);
+
+			# Skip the cache if this is a since_id or before_id qry
+			if ($since_id > 0 || $before_id > 0) {
+				return Notice::getStreamDirect($qry, $offset, $limit, $since_id, $before_id);
+			} else {
+				return Notice::getCachedStream($qry, $cachekey, $offset, $limit);
+			}
 		}
-	
+
+		return Notice::getStreamDirect($qry, $offset, $limit, $since_id, $before_id);
 	}
 
-	static function getStreamDirect($qry, $offset, $limit) {
-		
+	static function getStreamDirect($qry, $offset, $limit, $since_id, $before_id) {
+
+		$needAnd = FALSE;
+	  	$needWhere = TRUE;
+
+		if (preg_match('/\bWHERE\b/i', $qry)) {
+			$needWhere = FALSE;
+			$needAnd = TRUE;
+		}
+
+		if ($since_id > 0) {
+
+			if ($needWhere) {
+		    	$qry .= ' WHERE ';
+				$needWhere = FALSE;
+			} else {
+				$qry .= ' AND ';
+			}
+
+		    $qry .= ' notice.id > ' . $since_id;
+		}
+
+		if ($before_id > 0) {
+
+			if ($needWhere) {
+		    	$qry .= ' WHERE ';
+				$needWhere = FALSE;
+			} else {
+				$qry .= ' AND ';
+			}
+
+			$qry .= ' notice.id < ' . $before_id;
+		}
+
 		$qry .= ' ORDER BY notice.created DESC, notice.id DESC ';
-		
-		if(common_config('db','type')=='pgsql') {
+
+		if (common_config('db','type') == 'pgsql') {
 			$qry .= ' LIMIT ' . $limit . ' OFFSET ' . $offset;
 		} else {
 			$qry .= ' LIMIT ' . $offset . ', ' . $limit;
@@ -249,33 +286,33 @@ class Notice extends Memcached_DataObject
 		$notice = new Notice();
 
 		$notice->query($qry);
-		
+
 		return $notice;
 	}
-	
+
 	static function getCachedStream($qry, $cachekey, $offset, $limit) {
 
 		# If outside our cache window, just go to the DB
-		
+
 		if ($offset + $limit > NOTICE_CACHE_WINDOW) {
 			return Notice::getStreamDirect($qry, $offset, $limit);
 		}
 
 		# Get the cache; if we can't, just go to the DB
-		
+
 		$cache = common_memcache();
 
-		
+
 		if (!$cache) {
 			return Notice::getStreamDirect($qry, $offset, $limit);
 		}
 
 		# Get the notices out of the cache
-		
+
 		$notices = $cache->get(common_cache_key($cachekey));
-		
+
 		# On a cache hit, return a DB-object-like wrapper
-		
+
 		if ($notices !== FALSE) {
 			$wrapper = new NoticeWrapper(array_slice($notices, $offset, $limit));
 			return $wrapper;
@@ -284,15 +321,15 @@ class Notice extends Memcached_DataObject
 		# Otherwise, get the full cache window out of the DB
 
 		$notice = Notice::getStreamDirect($qry, 0, NOTICE_CACHE_WINDOW);
-		
+
 		# If there are no hits, just return the value
-		
+
 		if (!$notice) {
 			return $notice;
 		}
 
 		# Pack results into an array
-		
+
 		$notices = array();
 
 		while ($notice->fetch()) {
@@ -300,18 +337,18 @@ class Notice extends Memcached_DataObject
 		}
 
 		# Store the array in the cache for next time
-		
+
 		$result = $cache->set(common_cache_key($cachekey), $notices);
 
 		# return a wrapper of the array for use now
-		
+
 		$wrapper = new NoticeWrapper(array_slice($notices, $offset, $limit));
-		
+
 		return $wrapper;
 	}
 
 	function publicStream($offset=0, $limit=20, $since_id=0, $before_id=0) {
-		
+
 		$needAnd = FALSE;
       	$needWhere = TRUE;
 
@@ -323,54 +360,33 @@ class Notice extends Memcached_DataObject
 			$needAnd = TRUE;
 		}
 
-   		// NOTE: since_id and before_id are extensions to Twitter API
-        if ($since_id > 0) {
-            if ($needWhere)
-                $qry .= ' WHERE ';
-            if ($needAnd)
-				$qry .= ' AND ';
-            $qry .= ' notice.id > ' . $since_id . ' ';
-			$needAnd = FALSE;
-			$needWhere = FALSE;
-        }
-
-		if ($before_id > 0) {
-            if ($needWhere)
-                $qry .= ' WHERE ';
-			if ($needAnd)
-				$qry .= ' AND ';
-			$qry .= ' notice.id < ' . $before_id . ' ';
-			$needAnd = FALSE;
-			$needWhere = FALSE;
-		}
-
 		return Notice::getStream($qry,
 								 'public',
-								 $offset, $limit);
+								 $offset, $limit, $since_id, $before_id);
 	}
-	
+
 	function addToInboxes() {
 
 		$inbox = new Notice_inbox();
-		
+
 		$inbox->query('INSERT INTO notice_inbox (user_id, notice_id) ' .
 					  'SELECT user.id, ' . $this->id . ' ' .
 					  'FROM user JOIN subscription ON user.id = subscription.subscriber ' .
 					  'WHERE subscription.subscribed = ' . $this->profile_id);
-		
+
 		return;
 	}
 
 	# Delete from inboxes if we're deleted.
-	
+
 	function blowInboxes() {
 
 		$inbox = new Notice_inbox();
 		$inbox->notice_id = $this->id;
 		$inbox->delete();
-		
+
 		return;
 	}
-	
+
 }
 
diff --git a/classes/User.php b/classes/User.php
index 831b18f6f..bbc33f72a 100644
--- a/classes/User.php
+++ b/classes/User.php
@@ -297,20 +297,9 @@ class User extends Memcached_DataObject
 		  'FROM notice JOIN reply ON notice.id = reply.notice_id ' .
 		  'WHERE reply.profile_id = %d ';
 		
-        if ($since_id > 0) {
-            $qry .= ' AND notice.id > ' . $since_id . ' ';
-			$needAnd = FALSE;
-        }
-
-        // NOTE: before_id is an extension to Twitter API
-        if ($before_id > 0) {
-            $qry .= ' AND notice.id < ' . $before_id . ' ';
-			$needAnd = FALSE;
-        }
-
 		return Notice::getStream(sprintf($qry, $this->id),
 								 'user:replies:'.$this->id,
-								 $offset, $limit);
+								 $offset, $limit, $since_id, $before_id);
 	}
 	
 	function getNotices($offset=0, $limit=NOTICES_PER_PAGE, $since_id=0, $before_id=0) {
@@ -319,20 +308,9 @@ class User extends Memcached_DataObject
 		  'FROM notice ' .
 		  'WHERE profile_id = %d ';
 		
-        if ($since_id > 0) {
-            $qry .= ' AND notice.id > ' . $since_id . ' ';
-			$needAnd = FALSE;
-        }
-
-        // NOTE: before_id is an extension to Twitter API
-        if ($before_id > 0) {
-            $qry .= ' AND notice.id < ' . $before_id . ' ';
-			$needAnd = FALSE;
-        }
-
 		return Notice::getStream(sprintf($qry, $this->id),
 								 'user:notices:'.$this->id,
-								 $offset, $limit);
+								 $offset, $limit, $since_id, $before_id);
 	}
 	
 	function favoriteNotices($offset=0, $limit=NOTICES_PER_PAGE) {
@@ -352,20 +330,9 @@ class User extends Memcached_DataObject
 		  'FROM notice JOIN notice_inbox ON notice.id = notice_inbox.notice_id ' .
 		  'WHERE notice_inbox.user_id = %d ';
 
-        if ($since_id > 0) {
-            $qry .= ' AND notice.id > ' . $since_id . ' ';
-			$needAnd = FALSE;
-        }
-
-        // NOTE: before_id is an extension to Twitter API
-        if ($before_id > 0) {
-            $qry .= ' AND notice.id < ' . $before_id . ' ';
-			$needAnd = FALSE;
-        }
-
 		return Notice::getStream(sprintf($qry, $this->id),
 								 'user:notices_with_friends:' . $this->id,
-								 $offset, $limit);
+								 $offset, $limit, $since_id, $before_id);
 	}
 	
 	function blowFavesCache() {