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,'');