From 7e5c12673d68d667a42e4cb1c0fa74ddf6e04b01 Mon Sep 17 00:00:00 2001 From: Sean Finney Date: Sun, 22 Jun 2008 18:14:43 +0200 Subject: 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. --- db.sql | 132 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-- 1 file changed, 128 insertions(+), 4 deletions(-) (limited to 'db.sql') 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,''); + -- cgit v1.2.3