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).

2 files changed, 24 insertions, 23 deletions

diff --git a/db.sql b/db.sql
index 456a86a..ec8e9b3 100644
--- a/db.sql
+++ b/db.sql
@@ -2,35 +2,38 @@
 -- comments) must be preceded and followed by a blank line.
 
 CREATE TABLE packages (
-  id int UNIQUE NOT NULL PRIMARY KEY,
-  name text NOT NULL,
-  version text NOT NULL,
-  format text NOT NULL,
-  loc text NOT NULL,
-  diffgz_name text DEFAULT NULL,
-  diffgz_size int DEFAULT NULL,
+  id INTEGER UNIQUE NOT NULL PRIMARY KEY,
+  name TEXT NOT NULL,
+  version TEXT NOT NULL,
+  format TEXT NOT NULL,
+  loc TEXT NOT NULL,
+  diffgz_name TEXT DEFAULT NULL,
+  diffgz_size INTEGER DEFAULT NULL,
   diffgz_md5sum varchar(32) DEFAULT NULL
 );
 
 CREATE UNIQUE INDEX idx_pkg_name_version ON packages (name,version);
 
 CREATE TABLE suites (
-  id int UNIQUE NOT NULL,
-  name text UNIQUE NOT NULL PRIMARY KEY
+  id INTEGER UNIQUE NOT NULL PRIMARY KEY,
+  name TEXT UNIQUE NOT NULL,
+  UNIQUE (name) ON CONFLICT IGNORE
 );
 
 CREATE TABLE components (
-  id int UNIQUE NOT NULL,
-  name text UNIQUE NOT NULL PRIMARY KEY
+  id INTEGER UNIQUE NOT NULL PRIMARY KEY,
+  name TEXT UNIQUE NOT NULL,
+  UNIQUE (name) ON CONFLICT IGNORE
 );
 
 CREATE TABLE package_rel_map (
-  package_id int NOT NULL
+  package_id INTEGER NOT NULL
     CONSTRAINT fk_package_id REFERENCES packages(id),
-  suite_id int NOT NULL
+  suite_id INTEGER NOT NULL
     CONSTRAINT fk_suite_id REFERENCES suites(id) ON DELETE CASCADE,
-  component_id int NOT NULL
-    CONSTRAINT fk_component_id REFERENCES components(id)
+  component_id INTEGER NOT NULL
+    CONSTRAINT fk_component_id REFERENCES components(id),
+  UNIQUE (package_id, suite_id, component_id) ON CONFLICT IGNORE
 );
 
 -- sqlite doesn't actually implement FK constraints, therefore we
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=?), \