From 74fc254a08433f5d61fcc085ef95099d5dadaac9 Mon Sep 17 00:00:00 2001 From: Sean Finney Date: Sat, 28 Jun 2008 14:33:31 +0200 Subject: 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). --- patchtracker/DB.py | 14 ++++++-------- 1 file changed, 6 insertions(+), 8 deletions(-) (limited to 'patchtracker') 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=?), \ -- cgit v1.2.3