diff options
| -rw-r--r-- | db.sql | 33 | ||||
| -rw-r--r-- | patchtracker/DB.py | 14 |
2 files changed, 24 insertions, 23 deletions
@@ -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=?), \ |