db "optimizations" fsvo "optimization"

stop the voodoo with home-rolled "auto increment" type stuff.

also, use sqlite "extensions" for conflict resolution (in schema for
all but the source package table, which has it in saveSourcePackage),
so that we don't need to check for pre-existing records.  the "OR REPLACE"
was doing this previously, but had wierd effects with modifying the
primary key, which would cause db inconsistency and *really* wierd effects
when combined with the triggers).

1 files changed, 6 insertions, 8 deletions

diff --git a/patchtracker/DB.py b/patchtracker/DB.py
index 443c40f..efa3c8e 100644
--- a/patchtracker/DB.py
+++ b/patchtracker/DB.py
@@ -69,22 +69,20 @@ class PatchTrackerDB:
   def saveSourcePackage(self, srcpkg):
     cursor = self.db.cursor()
     #print "creating new record for",srcpkg
-    q = "INSERT OR REPLACE INTO packages (id,name,format,loc,version,\
-                                 diffgz_name,diffgz_size,diffgz_md5sum)\
-             VALUES ((SELECT IFNULL(MAX(id)+1,0) FROM packages),?,?,?,?,?,?,?)"
+    q = "INSERT OR IGNORE INTO packages \
+           (name,format,loc,version,diffgz_name,diffgz_size,diffgz_md5sum) \
+           VALUES (?,?,?,?,?,?,?)"
     cursor.execute(q, (srcpkg.name,srcpkg.format,srcpkg.loc,
                        srcpkg.version,srcpkg.diffgz_name,srcpkg.diffgz_size,
                        srcpkg.diffgz_md5sum))
 
   def saveSuite(self, suite):
-    q = "INSERT OR REPLACE INTO suites (id,name) \
-           VALUES ((SELECT IFNULL(MAX(id)+1,0) FROM suites),?)"
+    q = "INSERT INTO suites (name) VALUES (?)"
     cursor = self.db.cursor()
     cursor.execute(q, (suite,))
 
   def saveComponent(self, component):
-    q = "INSERT OR REPLACE INTO components (id,name) \
-           VALUES ((SELECT IFNULL(MAX(id)+1,0) FROM components),?)"
+    q = "INSERT INTO components (name) VALUES (?)"
     cursor = self.db.cursor()
     cursor.execute(q, (component,))
 
@@ -114,7 +112,7 @@ class PatchTrackerDB:
     return self.findCollection(package="%")
      
   def relateSourcePackage(self, name, version, suite, component):
-    q = "INSERT OR REPLACE INTO package_rel_map \
+    q = "INSERT INTO package_rel_map \
            (package_id,suite_id,component_id) \
            VALUES ((SELECT id FROM packages WHERE name=? AND version=?), \
                   (SELECT id FROM suites WHERE name=?), \