big commit with db and cgi handling updates

i know it's bad form to lump this all together.  oh well :p

gen-patch-info now does very little besides processing packages
and updating the relevant database entries.  pretty much everything
else is dynamically generated from pagehandler.py now.

1 files changed, 128 insertions, 4 deletions

diff --git a/db.sql b/db.sql
index a7fe50e..26c9667 100644
--- a/db.sql
+++ b/db.sql
@@ -1,4 +1,128 @@
-create table packages ( id int, package varchar(256), format varchar(32), diffgz varchar(1024), loc varchar(1024), type varchar(32), version varchar(256), updated int );
-create table releases ( id int, name varchar(32), md5sum varchar(32), updated int );
-create table components ( id int, name varchar(32), updated int );
-create table pkg_rel ( rel_id int, comp_id int, pkg_id int );
+CREATE TABLE packages (
+  id int UNIQUE NOT NULL,
+  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,
+  diffgz_md5sum varchar(32) DEFAULT NULL
+);
+
+CREATE TABLE suites (
+  id int UNIQUE NOT NULL,
+  name text UNIQUE NOT NULL PRIMARY KEY
+);
+
+CREATE TABLE components (
+  id int UNIQUE NOT NULL,
+  name text PRIMARY KEY
+);
+
+CREATE TABLE package_rel_map (
+  package_id int NOT NULL
+    CONSTRAINT fk_package_id REFERENCES packages(id),
+  suite_id int NOT NULL
+    CONSTRAINT fk_suite_id REFERENCES suites(id) ON DELETE CASCADE,
+  component_id int NOT NULL
+    CONSTRAINT fk_component_id REFERENCES components(id)
+);
+
+-- sqlite doesn't actually implement FK constraints, therefore we
+-- help out a little and do it ourselves via triggers
+
+-- before insert on the package relation mapping, catch invalid references
+
+CREATE TRIGGER fki_package_rel_map_suite_id_suites_id
+  BEFORE INSERT ON [package_rel_map]
+  FOR EACH ROW BEGIN
+    SELECT RAISE(ROLLBACK, 'INSERT on "package_rel_map" violates FK constraint on "suites.id"')
+    WHERE (SELECT id FROM suites WHERE id = NEW.suite_id) IS NULL;
+  END;
+
+CREATE TRIGGER fki_package_rel_map_package_id_packages_id
+  BEFORE INSERT ON [package_rel_map]
+  FOR EACH ROW BEGIN
+    SELECT RAISE(ROLLBACK, 'INSERT on "package_rel_map" violates FK constraint on "packages.id"')
+    WHERE (SELECT id FROM packages WHERE id = NEW.package_id) IS NULL;
+  END;
+
+CREATE TRIGGER fki_package_rel_map_component_id_components_id
+  BEFORE INSERT ON [package_rel_map]
+  FOR EACH ROW BEGIN
+    SELECT RAISE(ROLLBACK, 'INSERT on "package_rel_map" violates FK constraint on "components.id"')
+    WHERE (SELECT id FROM components WHERE id =NEW.component_id) IS NULL;
+  END;
+
+-- the same but for updates
+
+CREATE TRIGGER fku_package_rel_map_suite_id_suites_id
+  BEFORE UPDATE ON [package_rel_map]
+  FOR EACH ROW BEGIN
+    SELECT RAISE(ROLLBACK, 'update on "package_rel_map" violates FK constraint on "suites.id"')
+      WHERE (SELECT id FROM suites WHERE id = NEW.suite_id) IS NULL;
+  END;
+
+CREATE TRIGGER fku_package_rel_map_package_id_packages_id
+  BEFORE UPDATE ON [package_rel_map]
+  FOR EACH ROW BEGIN
+    SELECT RAISE(ROLLBACK, 'update on "package_rel_map" violates FK constraint on "packages.id"')
+      WHERE (SELECT id FROM packages WHERE id = NEW.package_id) IS NULL;
+  END;
+
+CREATE TRIGGER fku_package_rel_map_component_id_components_id
+  BEFORE UPDATE ON [package_rel_map]
+  FOR EACH ROW BEGIN
+    SELECT RAISE(ROLLBACK, 'update on "package_rel_map" violates FK constraint on "components.id"')
+     WHERE (SELECT id FROM components WHERE id=NEW.component_id) IS NULL;
+  END;
+
+-- when a package/component/suite id is deleted, clean up any references
+-- that would otherwise be orphaned
+
+CREATE TRIGGER fkdc_package_rel_map_suite_id_suites_id
+  BEFORE DELETE ON suites
+  FOR EACH ROW BEGIN
+    DELETE FROM package_rel_map WHERE package_rel_map.suite_id = OLD.id;
+  END;
+
+CREATE TRIGGER fkdc_package_rel_map_component_id_components_id
+  BEFORE DELETE ON components
+  FOR EACH ROW BEGIN
+    DELETE FROM package_rel_map WHERE package_rel_map.component_id = OLD.id;
+  END;
+
+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;
+  END;
+
+-- when a package relation mapping is deleted or updated, delete the package 
+-- record for the old relation if no other references to that package exist
+
+CREATE TRIGGER fkdc_packages_id_package_rel_map_package_id
+  BEFORE DELETE ON package_rel_map
+  FOR EACH ROW BEGIN
+    DELETE FROM packages WHERE packages.id = OLD.package_id
+      AND (SELECT package_id FROM package_rel_map 
+                     WHERE package_id = OLD.package_id AND rowid != OLD.rowid) 
+      IS NULL;
+  END;
+
+CREATE TRIGGER fku_package_rel_map_prune_packages_package_id
+  BEFORE UPDATE ON [package_rel_map]
+  FOR EACH ROW BEGIN
+    DELETE FROM packages WHERE packages.id = OLD.package_id
+      AND (OLD.package_id != NEW.package_id)
+      AND (SELECT package_id FROM package_rel_map 
+                     WHERE package_id = OLD.package_id AND rowid != OLD.rowid) 
+      IS NULL;
+  END;
+
+INSERT INTO packages VALUES (0,'','','','',NULL,NULL,NULL);
+
+INSERT INTO suites VALUES (0,'');
+
+INSERT INTO components VALUES (0,'');
+