Move to arches as a seperate table.

1 files changed, 45 insertions, 9 deletions

diff --git a/dbgenerator/database.py b/dbgenerator/database.py
index 9c247b2..caa91f3 100644
--- a/dbgenerator/database.py
+++ b/dbgenerator/database.py
@@ -12,6 +12,8 @@ class SQLPackageDatabase(object):
     at least self.cursor"""
 
     arches = frozenset(['alpha', 'amd64', 'arm', 'hppa', 'ia64', 'mips', 'ppc', 'ppc64', 'sparc', 'x86', 'x86-fbsd'])
+    cache_arch = {}
+    cache_category = {}
 
     def __init__(self):
         raise NotImplementedError("don't use base class")
@@ -50,11 +52,20 @@ class SQLPackageDatabase(object):
 
         self.cursor.execute("""CREATE TABLE keywords (
                                 cpv INTEGER,
-                                arch VARCHAR(16),
+                                arch INTEGER,
                                 mode CHAR(2),
                                 PRIMARY KEY (cpv, arch)
                             )""")
 
+        self.cursor.execute("""CREATE TABLE arches (
+                                archid INTEGER PRIMARY KEY AUTOINCREMENT,
+                                arch VARCHAR(16),
+                                UNIQUE(arch)
+                            )""")
+
+        for arch in self.arches:
+            self.find_or_create_arch(arch)                    
+
         self.db.commit()
 
     def drop_structure(self):
@@ -69,9 +80,10 @@ class SQLPackageDatabase(object):
         self.cursor.execute('DELETE FROM keywords WHERE cpv = ?'.replace('?',self.placeholder), ([cpv]))
         sql = 'INSERT INTO keywords (cpv, arch, mode) VALUES (?, ?, ?)'.replace('?',self.placeholder)
         for arch in self.arches:
+            archid = self.find_or_create_arch(arch)
             if arch not in keyword_dict:
                 keyword_dict[arch] = ""
-            self.cursor.execute(sql, (cpv, arch, keyword_dict[arch]))
+            self.cursor.execute(sql, (cpv, archid, keyword_dict[arch]))
         self.commit()
 
     def add_metadata(self, category, pn, description, homepage, license, changelog):
@@ -107,14 +119,38 @@ class SQLPackageDatabase(object):
             return -1
 
     def find_or_create_category(self, category):
-        self.cursor.execute('SELECT c FROM categories WHERE category = ?'.replace('?',self.placeholder), ([category]))
         try:
-            entries = self.cursor.fetchall()
-            return entries[0][0]
-        except IndexError:
-            self.cursor.execute('INSERT INTO categories (category) VALUES (?)'.replace('?',self.placeholder), ([category]))
-            self.commit()
-            return self.cursor.lastrowid
+            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
+    
+    def find_or_create_arch(self, arch):
+        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
 
     def find_or_create_cp(self, category, pn):
         c = self.find_or_create_category(category)