Merge lp:~andrewmccarthy/unity-lens-photos/shotwellspeedup into lp:unity-lens-photos

[Andrew McCarthy]

This branch is an attempt to increase the speed of searching Shotwell's database. The changes include:

- Calculating the list of "matching" event ids once, before looping through the photos
- Similarly, calculating a list of matching photo thumbnails once, before looping
- Break out of the photo loop immediately if the limit is reached
- Moving some code around to reduce duplicated or unnecessary effort

I didn't remove any now-unused methods in case they're of use in the future.

In my case (21500 photos, 1200 events, 185 tags) searches that were about 10-12 seconds are now 2-4 seconds.

Hope it's of some use!

[David Callé (davidc3)]

+1
Thanks a lot Andrew, I can clearly see the speed improvements.
Would you mind opening a bug about Shotwell search being slow and link it to this branch? I think this could make a nice SRU.

review: Approve

=== modified file 'src/shotwell_scope.py'
--- src/shotwell_scope.py	2012-10-15 15:46:12 +0000
+++ src/shotwell_scope.py	2012-10-19 22:26:21 +0000
@@ -120,8 +120,7 @@
                 limit = 100
             for i in self.shotwell(search, date, limit):
                 if not self.cancel_running_search:
-                    hex_id = "%x" % i[4]
-                    thumb_id = ("thumb" + "0"*(16 - len(hex_id)) + hex_id)
+                    thumb_id = "thumb%016x" % i[4]
                     try:
                         extension = i[1].split('.')[-1].lower ()
                     except:
@@ -277,8 +276,38 @@
         data_list = []
         db = self.getDB ()
         if db:
-            eventcursor = db.cursor()
             self.cancel_running_search = False
+
+            matching_events = []
+            matching_tags = set()
+            search_items = []
+            if search:
+                search_items = search.lower().split(" ")
+                # Make list of event ids for event names matching ALL search items
+                allevents = self.getAllEventNames(db)
+                for event in allevents:
+                    match = True
+                    for item in search_items:
+                        if item not in event[1].lower():
+                            match = False
+                            break
+                    if match:
+                        matching_events.append(event[0])
+                # Make list of thumbs where tags match all items
+                first = True
+                for item in search_items:
+                    newset = set()
+                    # Build set of all thumbs with tags matching this item
+                    for tag in self.tagdb:
+                        if item in tag[0].lower():
+                            newset.update(tag[1].split(','))
+                    if first:
+                        matching_tags = newset
+                        first = False
+                    else:
+                        # Keep only thumbs matching this and previous search items
+                        matching_tags.intersection_update(newset)
+
             try:
                 photos = self.getPhotos (db, date)
             except:
@@ -286,38 +315,30 @@
             i = 0
             for photo in photos:
                 if photo[16] != 4 and photo[16] != 8 and not self.cancel_running_search:
-                    item_list = []
-                    event_id = str(photo[10])
+                    pid = photo[0]
                     uri = photo[1]
-                    pid = photo[0]
-                    try:
-                        event = self.getEventNameForEventId (db, event_id, eventcursor)
-                    except:
-                        event = ''
-                    icon_hint = photo[1]
+                    event_id = photo[10]
                     title = photo[19]
                     if not title:
                         title = uri.split("/")[-1]
-                    timestamp = photo[6]
-                    if timestamp == 0:
-                        timestamp = photo[5]
-                    date = datetime.datetime.fromtimestamp(timestamp).strftime('%d %b %Y %H:%M')
                     match = False
-                    if not search:
+                    thumb_id = "thumb%016x" % pid
+                    if not search or event_id in matching_events or thumb_id in matching_tags:
                         match = True
                     else:
-                        match_list = []
-                        search_items = search.split(" ")
+                        match = True
                         for item in search_items:
-                            if (title.lower().find(item.lower())  > -1
-                                or event.lower().find(item.lower())  > -1
-                                or self.isInTagDB (item.lower (), pid)):
-                                match_list.append (True)
-                            else:
-                                match_list.append (False)
-                        if all(match_list):
-                            match = True
-                    if match and i < limit:
+                            if item not in title.lower():
+                                match = False
+                                break
+                    if match:
+                        icon_hint = photo[1]
+                        timestamp = photo[6]
+                        if timestamp == 0:
+                            timestamp = photo[5]
+                        date = datetime.datetime.fromtimestamp(timestamp).strftime('%d %b %Y %H:%M')
+
+                        item_list = []
                         item_list.append(title)
                         item_list.append(uri)
                         item_list.append(icon_hint)
@@ -325,20 +346,19 @@
                         item_list.append(pid)
                         data_list.append(item_list)
                         i += 1
-            eventcursor.close ()
+                        if i >= limit:
+                            break
         return data_list
 
-    def isInTagDB (self, term, photo):
-        for tag in self.tagdb:
+    def isInTagDB (self, tags, term, photo):
+        for tag in tags:
             if tag[0].lower().find(term)  > -1:
-                hex_id = "%x" % photo
-                thumb_id = ("thumb" + "0"*(16 - len(hex_id)) + hex_id)
+                thumb_id = "thumb%016x" % photo
                 if tag[1]:
-                    if tag[1].find(thumb_id) > -1:
+                    if thumb_id in tag[1]:
                         return True
         return False
 
-
     def getDB (self):
         """Check existence of our copy of Shotwell DB"""
         db = None
@@ -423,6 +443,14 @@
             event = raw_event[0]
         return str(event)
 
+    def getAllEventNames (self, db):
+        """Get all non-null event names with ids"""
+        sql='SELECT id, name FROM EventTable WHERE length(name)>0'
+        cursor = db.cursor()
+        events = cursor.execute(sql).fetchall()
+        cursor.close()
+        return events
+
 
     def getTitle(self, uri, title):
         """Get date from timestamp in db or from file"""
@@ -451,8 +479,7 @@
 
     def getTagsForPhotoId (self, db, photo):
         """Get all tags related to a photo"""
-        hex_id = "%x" % photo
-        thumb_id = ("thumb" + "0"*(16 - len(hex_id)) + hex_id)
+        thumb_id = "thumb%016x" % photo
         sql='SELECT name FROM TagTable WHERE photo_id_list LIKE ?'
         args=['%'+thumb_id+'%']
         cursor = db.cursor ()