1 files changed, 36 insertions, 3 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