-- db schema for patch tracker.  note that every distinct command (omitting
-- comments) must be preceded and followed by a blank line.

CREATE TABLE packages (
  id INTEGER UNIQUE NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  version TEXT NOT NULL,
  format TEXT NOT NULL,
  loc TEXT NOT NULL,
  diffgz_name TEXT DEFAULT NULL,
  diffgz_size INTEGER DEFAULT NULL,
  diffgz_md5sum TEXT DEFAULT NULL
);

CREATE UNIQUE INDEX idx_pkg_name_version ON packages (name,version);

CREATE TABLE suites (
  id INTEGER UNIQUE NOT NULL PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  UNIQUE (name) ON CONFLICT IGNORE
);

CREATE TABLE components (
  id INTEGER UNIQUE NOT NULL PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  UNIQUE (name) ON CONFLICT IGNORE
);

CREATE TABLE package_rel_map (
  package_id INTEGER NOT NULL
    CONSTRAINT fk_package_id REFERENCES packages(id),
  suite_id INTEGER NOT NULL
    CONSTRAINT fk_suite_id REFERENCES suites(id) ON DELETE CASCADE,
  component_id INTEGER NOT NULL
    CONSTRAINT fk_component_id REFERENCES components(id),
  marked INTEGER NOT NULL DEFAULT 1,
  UNIQUE (package_id, suite_id, component_id) ON CONFLICT REPLACE
);

-- 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;