better database maintainer representation

4 files changed, 84 insertions, 44 deletions

diff --git a/db.sql b/db.sql
index 17d9883..eb39f83 100644
--- a/db.sql
+++ b/db.sql
@@ -9,13 +9,17 @@ CREATE TABLE packages (
   loc TEXT NOT NULL,
   diffgz_name TEXT DEFAULT NULL,
   diffgz_size INTEGER DEFAULT NULL,
-  diffgz_md5sum TEXT DEFAULT NULL,
-  maintainer TEXT NOT NULL,
-  uploaders TEXT DEFAULT NULL
+  diffgz_md5sum TEXT DEFAULT NULL
 );
 
 CREATE UNIQUE INDEX idx_pkg_name_version ON packages (name,version);
 
+CREATE TABLE maintainers (
+  id INTEGER UNIQUE NOT NULL PRIMARY KEY,
+  email TEXT UNIQUE NOT NULL,
+  UNIQUE (email) ON CONFLICT IGNORE
+);
+
 CREATE TABLE suites (
   id INTEGER UNIQUE NOT NULL PRIMARY KEY,
   name TEXT UNIQUE NOT NULL,
@@ -39,6 +43,14 @@ CREATE TABLE package_rel_map (
   UNIQUE (package_id, suite_id, component_id) ON CONFLICT REPLACE
 );
 
+CREATE TABLE maint_rel_map (
+  package_id INTEGER NOT NULL
+    CONSTRAINT fk_package_id REFERENCES packages(id),
+  maintainer_id INTEGER NOT NULL
+    CONSTRAINT fk_maintainer_id REFERENCES maintainers(id) ON DELETE CASCADE,
+  UNIQUE (package_id, maintainer_id) ON CONFLICT REPLACE
+);
+
 -- sqlite doesn't actually implement FK constraints, therefore we
 -- help out a little and do it ourselves via triggers
 
@@ -55,6 +67,17 @@ CREATE TRIGGER fki_package_rel_map
       WHERE (SELECT id FROM components WHERE id =NEW.component_id) IS NULL;
   END;
 
+-- likewise for the package maintainer mapping
+
+CREATE TRIGGER fki_maint_rel_map
+  BEFORE INSERT ON [maint_rel_map]
+  FOR EACH ROW BEGIN
+    SELECT RAISE(ROLLBACK, 'INSERT FK violation: maint_rel_map/packages.id')
+      WHERE (SELECT id FROM packages WHERE id = NEW.package_id) IS NULL;
+    SELECT RAISE(ROLLBACK, 'INSERT FK violation: maint_rel_map/maintainers.id')
+      WHERE (SELECT id FROM maintainers WHERE id = NEW.maintainer_id) IS NULL;
+  END;
+
 -- the same but for updates
 
 CREATE TRIGGER fku_package_rel_map
@@ -68,6 +91,15 @@ CREATE TRIGGER fku_package_rel_map
       WHERE (SELECT id FROM suites WHERE id = NEW.suite_id) IS NULL;
   END;
 
+CREATE TRIGGER fku_maint_rel_map
+  BEFORE UPDATE ON [maint_rel_map]
+  FOR EACH ROW BEGIN
+    SELECT RAISE(ROLLBACK, 'UPDATE FK violation: maint_rel_map/packages.id')
+      WHERE (SELECT id FROM packages WHERE id = NEW.package_id) IS NULL;
+    SELECT RAISE(ROLLBACK, 'UPDATE FK violation: maint_rel_map/maintainers.id')
+     WHERE (SELECT id FROM maintainers WHERE id=NEW.maintainer_id) IS NULL;
+  END;
+
 -- when a package/component/suite id is deleted, clean up any references
 -- that would otherwise be orphaned
 
@@ -87,6 +119,7 @@ CREATE TRIGGER fkdc_package_rel_map_package_id_packages_id
   BEFORE DELETE ON packages
   FOR EACH ROW BEGIN
     DELETE FROM package_rel_map WHERE package_rel_map.package_id = OLD.id;
+    DELETE FROM maint_rel_map WHERE maint_rel_map.package_id = OLD.id;
   END;
 
 -- when a package relation mapping is deleted or updated, delete the package 
diff --git a/gen-patch-info.py b/gen-patch-info.py
index 537e0a8..426d9d8 100755
--- a/gen-patch-info.py
+++ b/gen-patch-info.py
@@ -45,8 +45,9 @@ if __name__ == '__main__':
       for p in a.sourcepackages(s, c, filter=packages):
         print "\t\tpackage:",p
         db.saveSourcePackage(p)
+        db.saveMaintainers(p.maintainers)
         db.relateSourcePackage(name=p.name, version=p.version, suite=s,
-                               component=c)
+                               component=c, maintainers=p.maintainers)
 
   db.prune()
   db.unmark()
diff --git a/patchtracker/DB.py b/patchtracker/DB.py
index 3a5be5c..0794903 100644
--- a/patchtracker/DB.py
+++ b/patchtracker/DB.py
@@ -5,33 +5,11 @@ import errno
 
 import patchtracker.SourceArchive as SourceArchive
 
-def srcpkg_factory(cursor, row):
-  d = {}
-  for idx, col in enumerate(cursor.description):
-    d[col[0]] = row[idx]
-
-  info = {}
-  info['Files'] = []
-  if d['diffgz_name']:
-    diffgz = {'name':d['diffgz_name'], 'size':d['diffgz_size'], 
-              'md5sum':d['diffgz_md5sum'] }
-    info['Files'].append(diffgz)
-
-  colmap = {'name':'Package','version':'Version','format':'Format',
-            'loc':'Directory','maintainer':'Maintainer','uploaders':'Uploaders'}
-            
-  for col,field in colmap.iteritems():
-    info[field] = d[col]
-  return SourceArchive.SourcePackage(info)
-
 def srcpkg_collection_factory(cursor, row):
   d = {}
   rest = {}
   for idx, col in enumerate(cursor.description):
-    if not d.has_key(col[0]):
-      d[col[0]] = row[idx]
-    else:
-      rest[col[0]] = row[idx]
+    d[col[0]] = row[idx]
 
   info = {}
   info['Files'] = []
@@ -40,12 +18,12 @@ def srcpkg_collection_factory(cursor, row):
               'md5sum':d['diffgz_md5sum'] }
     info['Files'].append(diffgz)
 
-  colmap = {'name':'Package','version':'Version','format':'Format',
-            'loc':'Directory','maintainer':'Maintainer','uploaders':'Uploaders'}
+  colmap = {'packagename':'Package','version':'Version','format':'Format',
+            'loc':'Directory','emails':'Maintainer'}
             
   for col,field in colmap.iteritems():
     info[field] = d[col]
-  return (SourceArchive.SourcePackage(info), rest)
+  return (SourceArchive.SourcePackage(info), d)
 
 
 class PatchTrackerDB:
@@ -70,11 +48,11 @@ class PatchTrackerDB:
     cursor = self.db.cursor()
     #print "creating new record for",srcpkg
     q = "INSERT OR IGNORE INTO packages \
-           (name,format,loc,version,diffgz_name,diffgz_size,diffgz_md5sum,maintainer,uploaders) \
-           VALUES (?,?,?,?,?,?,?,?,?)"
+           (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,srcpkg.maintainer,srcpkg.uploaders))
+                       srcpkg.diffgz_md5sum,))
 
   def saveSuite(self, suite):
     q = "INSERT INTO suites (name) VALUES (?)"
@@ -86,25 +64,42 @@ class PatchTrackerDB:
     cursor = self.db.cursor()
     cursor.execute(q, (component,))
 
-  def findCollection(self, package="%", version=None, email=None):
+  def saveMaintainers(self, maintainers):
+    q = "INSERT INTO maintainers (email) VALUES (?)"
+    cursor = self.db.cursor()
+    cursor.executemany(q, [(m,) for m in maintainers])
+
+  def findCollection(self, package=None, version=None, email=None):
     oldfactory = self.db.row_factory
     self.db.row_factory = srcpkg_collection_factory
     cursor = self.db.cursor()
     toc = SourceArchive.SourcePackageIndex()
-    q = "SELECT * FROM packages AS p,package_rel_map AS m,suites AS s \
-           WHERE p.name LIKE ? AND p.id = m.package_id AND m.suite_id = s.id"
-    qargs = (package,)
+    # zomgroflcoptersql!!1!one!
+    q = "SELECT p.name as packagename, version, format, loc, diffgz_name, \
+                diffgz_size, diffgz_md5sum, s.name as suitename, \
+                (SELECT GROUP_CONCAT(email) FROM maintainers AS m, \
+                        maint_rel_map AS mm \
+                   WHERE mm.package_id = p.id \
+                     AND mm.maintainer_id = m.id) AS emails \
+           FROM packages AS p, package_rel_map AS pm, suites AS s \
+             WHERE pm.package_id = p.id AND pm.suite_id = s.id \
+               AND emails IS NOT NULL"
+
+    qargs = ()
+    if package:
+      q += " AND packagename LIKE ?"
+      qargs += (package,)
     if version:
       q += " AND p.version = ?"
       qargs += (version,)
     if email:
-      q += " AND ( p.maintainer like ? OR p.uploaders like ? )"
-      qargs += (email,email)
+      q += " AND emails LIKE ?"
+      qargs += ("%%<%s>%%"%(email),)
 
     cursor.execute(q, qargs)
     # use srcpkg_factory to fetch sourcepackages, once per suite
     for srcpkg,rest in cursor.fetchall():
-      toc.ins(srcpkg, rest["name"])
+      toc.ins(srcpkg, rest['suitename']) 
     self.db.row_factory = oldfactory
     return toc
 
@@ -127,7 +122,7 @@ class PatchTrackerDB:
     indices.sort()
     return indices
      
-  def relateSourcePackage(self, name, version, suite, component):
+  def relateSourcePackage(self, name, version, suite, component, maintainers):
     q = "INSERT INTO package_rel_map \
            (package_id,suite_id,component_id,marked) \
            VALUES ((SELECT id FROM packages WHERE name=? AND version=?), \
@@ -135,6 +130,11 @@ class PatchTrackerDB:
                   (SELECT id FROM components WHERE name=?), 1)"
     cursor = self.db.cursor()
     cursor.execute(q, (name, version, suite, component))
+    q2 = "INSERT INTO maint_rel_map \
+           (package_id,maintainer_id) \
+           VALUES ((SELECT id FROM packages WHERE name=? AND version=?), \
+                  (SELECT id FROM maintainers WHERE email=?))"
+    cursor.executemany(q2, [(name, version, m) for m in maintainers])
 
   def findDiffGz(self, pkgname, version):
     q = "SELECT diffgz_name,loc FROM packages WHERE name=? AND version=?"
diff --git a/patchtracker/SourceArchive.py b/patchtracker/SourceArchive.py
index 311587e..4edb559 100755
--- a/patchtracker/SourceArchive.py
+++ b/patchtracker/SourceArchive.py
@@ -97,15 +97,21 @@ class SourcePackage:
     self.diffgz_size = None
     self.diffgz_md5sum = None
     self.diffgz = None
-    self.uploaders = None
+    self.maintainers = None
     self.type = "Native"
     self.name = info['Package']  
     self.format = info['Format']  
     self.loc = info['Directory']  
     self.version = info['Version']  
-    self.maintainer = info['Maintainer']  
+    try:
+      self.maintainers = [unicode(info['Maintainer'])]
+    except UnicodeDecodeError:
+      self.maintainers = [unicode(info['Maintainer'], 'latin-1')]
     if info.has_key('Uploaders'):
-      self.uploaders = info['Uploaders']  
+      try:
+        self.maintainers.extend(unicode(info['Uploaders'].split(", ")))
+      except UnicodeDecodeError:
+        self.maintainers.extend(unicode(info['Uploaders'].split(", "), 'latin-1'))
     self.idx = getidx(self)
 
     for f in info['Files']: