Fix the architecture list and use LRUCache instead of too-full dictionaries to enable more caching.

1 files changed, 61 insertions, 36 deletions

diff --git a/dbgenerator/database.py b/dbgenerator/database.py
index a84c82b..27d929b 100644
--- a/dbgenerator/database.py
+++ b/dbgenerator/database.py
@@ -4,6 +4,7 @@
 """database module"""
 
 import os, sys
+from lrucache import LRUCache
 
 class SQLPackageDatabase(object):
     """we have to store our stuff somewhere
@@ -11,10 +12,19 @@ class SQLPackageDatabase(object):
     subclass and redefine init to provide
     at least self.cursor"""
 
-    arches = frozenset(['alpha', 'amd64', 'arm', 'hppa', 'ia64', 'mips', 'ppc', 'ppc64', 'sparc', 'x86', 'x86-fbsd'])
-    cache_arch = {}
-    cache_category = {}
-    schema_version = 53
+    # This should match /usr/portage/profiles/arch.list
+    arches = frozenset(['alpha', 'amd64', 'arm', 'hppa', 'ia64', 'm68k', 'mips', 'ppc', 'ppc64', 'ppc-macos', 's390', 'sh', 'sparc', 'sparc-fbsd', 'x86', 'x86-fbsd'])
+    # If you change the database structure below, you should increment this number
+    schema_version = 54
+    
+    # These are used to cache the various relations
+    # and avoid more SELECT queries just to find the relations
+    # Relies on the fact that related category/package/version items are
+    # visited in close proximity.
+    cache_arch = LRUCache(len(arches))
+    cache_category = LRUCache(3)
+    cache_cp = LRUCache(3)
+    cache_cpv = LRUCache(3)
 
     def __init__(self):
         raise NotImplementedError("don't use base class")
@@ -116,57 +126,69 @@ class SQLPackageDatabase(object):
             return -1
     
     def find_cpv(self, category, pn, pv):
+        cachekey = category+pn+pv
+        if cachekey in self.cache_cpv:
+            return self.cache_cpv[cachekey]
         self.cursor.execute('SELECT cpv FROM versions JOIN packages USING (cp) JOIN categories USING (c) WHERE categories.category = ? AND packages.pn = ? AND versions.pv = ?'.replace('?',self.placeholder), (category, pn, pv))
         entries = self.cursor.fetchall()
         try:
-             return entries[0][0]
+             cpv = entries[0][0]
+             self.cache_cpv[cachekey] = cpv
+             return cpv
         except IndexError:
             return -1
 
     def find_or_create_category(self, category):
+        cachekey = category
+        if cachekey in self.cache_category:
+            return self.cache_category[cachekey]
+        self.cursor.execute('SELECT c FROM categories WHERE category = ?'.replace('?',self.placeholder), ([category]))
+        entries = self.cursor.fetchall()
         try:
-            return self.cache_category[category]
-        except KeyError:
-            self.cursor.execute('SELECT c FROM categories WHERE category = ?'.replace('?',self.placeholder), ([category]))
-            try:
-                entries = self.cursor.fetchall()
-                categoryid = entries[0][0]
-                self.cache_category[category] = categoryid
-                return categoryid
-            except IndexError:
-                self.cursor.execute('INSERT INTO categories (category) VALUES (?)'.replace('?',self.placeholder), ([category]))
-                self.commit()
-                categoryid = self.cursor.lastrowid
-                self.cache_category[category] = categoryid
-                return categoryid
+            categoryid = entries[0][0]
+            self.cache_category[cachekey] = categoryid
+            return categoryid
+        except IndexError:
+            self.cursor.execute('INSERT INTO categories (category) VALUES (?)'.replace('?',self.placeholder), ([category]))
+            self.commit()
+            categoryid = self.cursor.lastrowid
+            self.cache_category[cachekey] = categoryid
+            return categoryid
     
     def find_or_create_arch(self, arch):
+        cachekey = arch
+        if cachekey in self.cache_arch:
+            return self.cache_arch[cachekey]
+        self.cursor.execute('SELECT archid FROM arches WHERE arch = ?'.replace('?',self.placeholder), ([arch]))
+        entries = self.cursor.fetchall()
         try:
-            return self.cache_arch[arch]
-        except KeyError:
-            self.cursor.execute('SELECT archid FROM arches WHERE arch = ?'.replace('?',self.placeholder), ([arch]))
-            try:
-                entries = self.cursor.fetchall()
-                archid = entries[0][0]
-                self.cache_arch[arch] = archid
-                return archid
-            except IndexError:
-                self.cursor.execute('INSERT INTO arches (arch) VALUES (?)'.replace('?',self.placeholder), ([arch]))
-                self.commit()
-                archid = self.cursor.lastrowid
-                self.cache_arch[arch] = archid
-                return archid
+            archid = entries[0][0]
+            self.cache_arch[cachekey] = archid
+            return archid
+        except IndexError:
+            self.cursor.execute('INSERT INTO arches (arch) VALUES (?)'.replace('?',self.placeholder), ([arch]))
+            self.commit()
+            archid = self.cursor.lastrowid
+            self.cache_arch[cachekey] = archid
+            return archid
 
     def find_or_create_cp(self, category, pn):
+        cachekey = category+pn
+        if cachekey in self.cache_cp:
+            return self.cache_cp[cachekey]
         c = self.find_or_create_category(category)
         self.cursor.execute('SELECT cp FROM packages WHERE c = ? AND pn = ?'.replace('?',self.placeholder), (c, pn))
+        entries = self.cursor.fetchall()
         try:
-            entries = self.cursor.fetchall()
-            return entries[0][0]
+            cp = entries[0][0]
+            self.cache_cp[cachekey] = cp
+            return cp
         except IndexError:
             self.cursor.execute('INSERT INTO packages (c,pn) VALUES (?,?)'.replace('?',self.placeholder), (c,pn))
             self.commit()
-            return self.cursor.lastrowid
+            cp = self.cursor.lastrowid
+            self.cache_cp[cachekey] = cp
+            return cp
      
     def schema_is_current(self):
         try:
@@ -224,3 +246,6 @@ class MySQLPackageDB(SQLPackageDatabase):
         if initdb:
             self.drop_structure()
             self.create_structure()
+    
+    def commit(self):
+        self.db.commit()