add SemanticScuttle_Service_Bookmark::bookmarksExist() method to check for existance of multiple bookmarks at once for future sql optimization

git-svn-id: https://semanticscuttle.svn.sourceforge.net/svnroot/semanticscuttle/trunk@680 b3834d28-1941-0410-a4f8-b48e95affb8f

2 files changed, 177 insertions, 1 deletions

diff --git a/src/SemanticScuttle/Service/Bookmark.php b/src/SemanticScuttle/Service/Bookmark.php
index 10b0b8b..a1bb1a5 100644
--- a/src/SemanticScuttle/Service/Bookmark.php
+++ b/src/SemanticScuttle/Service/Bookmark.php
@@ -312,7 +312,7 @@ class SemanticScuttle_Service_Bookmark extends SemanticScuttle_DbService
      * @return boolean True when the bookmark with the given URL
      *                 exists for the user, false if not.
      */
-    function bookmarkExists($address = false, $uid = null)
+    public function bookmarkExists($address = false, $uid = null)
     {
         if (!$address) {
             return false;
@@ -347,6 +347,60 @@ class SemanticScuttle_Service_Bookmark extends SemanticScuttle_DbService
 
 
     /**
+     * Checks if the given addresses exist
+     *
+     * @param array   $addresses Array of addresses
+     * @param integer $uid       User ID the addresses shall belong to
+     *
+     * @return array Array with addresses as keys, true/false for existence
+     *               as value
+     */
+    public function bookmarksExist($addresses, $uid = null)
+    {
+        if (count($addresses) == 0) {
+            return array();
+        }
+
+        $hashes = array();
+        $sql = '(1';
+        foreach ($addresses as $key => $address) {
+            $hash = md5($this->normalize($address));
+            $hashes[$hash] = $address;
+            $sql .= ' OR bHash = "'
+                . $this->db->sql_escape($hash)
+                . '"';
+        }
+        $sql .= ')';
+        if ($uid !== null) {
+            $sql .= ' AND uId = ' . intval($uid);
+        }
+
+        $sql = 'SELECT bHash, COUNT(*) as "count" FROM '
+            . $this->getTableName()
+            . ' WHERE ' . $sql
+            . ' GROUP BY bHash';
+
+        if (!($dbresult = $this->db->sql_query($sql))) {
+            message_die(
+                GENERAL_ERROR, 'Could not get bookmark counts', '',
+                __LINE__, __FILE__, $sql, $this->db
+            );
+        }
+
+        $existence = array_combine(
+            $addresses,
+            array_fill(0, count($addresses), false)
+        );
+        while ($row = $this->db->sql_fetchrow($dbresult)) {
+            $existence[$hashes[$row['bHash']]] = $row['count'] > 0;
+        }
+
+        return $existence;
+    }
+
+
+
+    /**
      * Adds a bookmark to the database.
      *
      * @param string  $address     Full URL of the bookmark
diff --git a/tests/BookmarkTest.php b/tests/BookmarkTest.php
index 39a9974..74685c4 100644
--- a/tests/BookmarkTest.php
+++ b/tests/BookmarkTest.php
@@ -253,6 +253,128 @@ class BookmarkTest extends TestBase
 
 
     /**
+     * Tests if bookmarksExist() returns true when a bookmark
+     * exists
+     *
+     * @return void
+     */
+    public function testBookmarksExistTrueSingle()
+    {
+        $bid = $this->addBookmark();
+        $bookmark = $this->bs->getBookmark($bid);
+
+        $ret = $this->bs->bookmarksExist(array($bookmark['bAddress']));
+        $this->assertType('array', $ret);
+        $this->assertEquals(1, count($ret));
+        $this->assertTrue($ret[$bookmark['bAddress']]);
+    }
+
+
+
+    /**
+     * Tests if bookmarksExist() returns true when all bookmarks
+     * exist
+     *
+     * @return void
+     */
+    public function testBookmarksExistTrueMultiple()
+    {
+        $bid = $this->addBookmark();
+        $bookmark = $this->bs->getBookmark($bid);
+
+        $bid2 = $this->addBookmark();
+        $bookmark2 = $this->bs->getBookmark($bid2);
+
+
+        $ret = $this->bs->bookmarksExist(
+            array(
+                $bookmark['bAddress'],
+                $bookmark2['bAddress']
+            )
+        );
+        $this->assertType('array', $ret);
+        $this->assertEquals(2, count($ret));
+        $this->assertTrue($ret[$bookmark['bAddress']]);
+        $this->assertTrue($ret[$bookmark2['bAddress']]);
+    }
+
+
+
+    /**
+     * Tests if bookmarksExist() returns false when a bookmark
+     * does not exist
+     *
+     * @return void
+     */
+    public function testBookmarksExistFalseSingle()
+    {
+        $ret = $this->bs->bookmarksExist(array('does-not-exist'));
+        $this->assertType('array', $ret);
+        $this->assertEquals(1, count($ret));
+        $this->assertFalse($ret['does-not-exist']);
+    }
+
+
+
+    /**
+     * Tests if bookmarksExist() returns false when all bookmarks
+     * do not exist
+     *
+     * @return void
+     */
+    public function testBookmarksExistFalseMultiple()
+    {
+        $bms = array(
+            'does-not-exist',
+            'does-not-exist-2',
+            'does-not-exist-3',
+        );
+        $ret = $this->bs->bookmarksExist($bms);
+        $this->assertType('array', $ret);
+        $this->assertEquals(3, count($ret));
+        $this->assertFalse($ret['does-not-exist']);
+        $this->assertFalse($ret['does-not-exist-2']);
+        $this->assertFalse($ret['does-not-exist-3']);
+    }
+
+
+
+    /**
+     * Tests if bookmarksExist() returns true when some bookmarks
+     * exist.
+     *
+     * @return void
+     */
+    public function testBookmarksExistSome()
+    {
+        $bid = $this->addBookmark();
+        $bookmark = $this->bs->getBookmark($bid);
+
+        $bid2 = $this->addBookmark();
+        $bookmark2 = $this->bs->getBookmark($bid2);
+
+
+        $ret = $this->bs->bookmarksExist(
+            array(
+                $bookmark['bAddress'],
+                'does-not-exist',
+                $bookmark2['bAddress'],
+                'does-not-exist-2',
+                'does-not-exist-3'
+            )
+        );
+        $this->assertType('array', $ret);
+        $this->assertEquals(5, count($ret));
+        $this->assertTrue($ret[$bookmark['bAddress']]);
+        $this->assertTrue($ret[$bookmark2['bAddress']]);
+        $this->assertFalse($ret['does-not-exist']);
+        $this->assertFalse($ret['does-not-exist-2']);
+        $this->assertFalse($ret['does-not-exist-3']);
+    }
+
+
+
+    /**
      * Test if countBookmarks() works with no bookmarks
      *
      * @return void