path: root/dbstore/sql/init.sql

-- enable foreign keys
PRAGMA foreign_keys = true;

-- set database version
PRAGMA user_version = 314159;

-- create cpes table
CREATE TABLE cpes (
  -- cpe unique ID
  cpe_id        INTEGER PRIMARY KEY,

  -- cpe uri
  cpe_uri       TEXT UNIQUE NOT NULL CHECK(
                  LENGTH(cpe_uri) > 5 AND
                  cpe_uri LIKE 'cpe:/%'
                ),

  -- cpe 2.3 formatting string
  cpe23         TEXT UNIQUE NOT NULL CHECK(
                  LENGTH(cpe23) > 28 AND
                  cpe23 LIKE 'cpe:2.3:%'
                )
);

-- create all fts table (cross join across all
-- titles and references, populated by triggers)
CREATE VIRTUAL TABLE cpe_fts_all USING fts5(
  -- parent CPE id
  cpe_id UNINDEXED,

  -- title
  title,

  -- reference URL
  href,

  -- reference name
  val,

  -- use porter stemming
  tokenize = 'porter'
);

-- create titles table
CREATE TABLE cpe_titles (
  -- title primary key
  cpe_title_id  INTEGER PRIMARY KEY,

  -- external CPE id
  cpe_id        INT NOT NULL
                REFERENCES cpes(cpe_id),

  -- language code
  lang          TEXT NOT NULL
                CHECK (LENGTH(lang) > 0),

  -- text value
  val           TEXT NOT NULL
                CHECK (LENGTH(val) > 0),

  -- prevent duplicate titles of the same name
  UNIQUE (cpe_id, lang)
);

-- create titles fts table
CREATE VIRTUAL TABLE cpe_fts_titles USING fts5(
  -- parent CPE id
  cpe_id UNINDEXED,

  -- title
  title,

  -- use porter stemming
  tokenize = 'porter'
);

-- create titles all fts insert trigger
CREATE TRIGGER tr_cpe_titles_insert_fts_all AFTER INSERT ON cpe_titles
  FOR EACH ROW WHEN (NEW.lang = 'en-US') BEGIN
    INSERT INTO cpe_fts_all(cpe_id, title, href, val)
      SELECT NEW.cpe_id,
             NEW.val,
             b.href,
             b.val

        FROM cpes a
        JOIN cpe_refs b
          ON (b.cpe_id = a.cpe_id)

       WHERE a.cpe_id = NEW.cpe_id;
  END;

-- create titles delete all fts trigger
CREATE TRIGGER tr_cpe_titles_delete_fts_all BEFORE DELETE ON cpe_titles
  FOR EACH ROW WHEN (OLD.lang = 'en-US') BEGIN
    DELETE FROM cpe_fts_all
     WHERE cpe_id = OLD.cpe_id
       AND title = OLD.val;
  END;

-- create titles all fts insert trigger
CREATE TRIGGER tr_cpe_titles_insert_fts_titles AFTER INSERT ON cpe_titles
  FOR EACH ROW WHEN (NEW.lang = 'en-US') BEGIN
    INSERT INTO cpe_fts_titles(cpe_id, title) VALUES (NEW.cpe_id, NEW.val);
  END;

-- create titles delete titles fts trigger
CREATE TRIGGER tr_cpe_titles_delete_fts_titles BEFORE DELETE ON cpe_titles
  FOR EACH ROW WHEN (OLD.lang = 'en-US') BEGIN
    DELETE FROM cpe_fts_titles
     WHERE cpe_id = OLD.cpe_id
       AND title = OLD.val;
  END;

-- create refs table
CREATE TABLE cpe_refs (
  -- reference unique id
  cpe_ref_id    INTEGER PRIMARY KEY,

  -- external CPE id
  cpe_id        INT NOT NULL
                REFERENCES cpes(cpe_id),

  -- url
  href          TEXT NOT NULL CHECK (
                  LENGTH(href) > 0 AND
                  (href LIKE 'http://%' OR href LIKE 'https://%')
                ),

  -- text description
  val           TEXT NOT NULL
                CHECK (LENGTH(val) > 0)
);

-- create refs fts table
CREATE VIRTUAL TABLE cpe_fts_refs USING fts5(
  -- parent CPE id
  cpe_id UNINDEXED,

  -- reference URL
  href,

  -- reference name
  val,

  -- use porter stemming
  tokenize = 'porter'
);

-- create refs insert trigger for all fts
CREATE TRIGGER tr_cpe_refs_insert_fts_all AFTER INSERT ON cpe_refs
  FOR EACH ROW BEGIN
    INSERT INTO cpe_fts_all(cpe_id, title, href, val)
      SELECT NEW.cpe_id,
             b.val,
             NEW.href,
             NEW.val

        FROM cpes a
        JOIN cpe_titles b
          ON (b.cpe_id = a.cpe_id)

       WHERE a.cpe_id = NEW.cpe_id
         AND b.lang = 'en-US';
  END;

-- create refs delete trigger for all fts
CREATE TRIGGER tr_cpe_refs_delete_fts_all BEFORE DELETE ON cpe_refs
  FOR EACH ROW BEGIN
    DELETE FROM cpe_fts_all
     WHERE cpe_id = OLD.cpe_id
       AND href = OLD.lang
       AND val = OLD.val;
  END;

-- create refs insert trigger for refs fts
CREATE TRIGGER tr_cpe_refs_insert_fts_refs AFTER INSERT ON cpe_refs
  FOR EACH ROW BEGIN
    INSERT INTO cpe_fts_refs(cpe_id, href, val) VALUES (NEW.cpe_id, NEW.href, NEW.val);
  END;

-- create refs delete trigger for refs fts
CREATE TRIGGER tr_cpe_refs_delete_fts_refs BEFORE DELETE ON cpe_refs
  FOR EACH ROW BEGIN
    DELETE FROM cpe_fts_refs
     WHERE cpe_id = OLD.cpe_id
       AND href = OLD.lang
       AND val = OLD.val;
  END;

--
-- cpe matches table
--
CREATE TABLE cpe_matches (
  -- cpe match unique ID
  cpe_match_id  INTEGER PRIMARY KEY,

  -- modern CPE 2.3 formatted string
  cpe23         TEXT UNIQUE NOT NULL CHECK(
                  LENGTH(cpe23) > 28 AND
                  cpe23 LIKE 'cpe:2.3:%'
                ),

  -- old CPE 2.2 URI
  cpe22_uri     TEXT NOT NULL
);

-- cpe match vulnerabilities
CREATE TABLE cpe_match_vulnerables (
  cpe_match_id  INTEGER PRIMARY KEY,

  -- true if this match is vulnerable, and false
  -- otherwise
  is_vulnerable BOOLEAN NOT NULL,

  -- add foreign key constraint
  FOREIGN KEY (cpe_match_id)
    REFERENCES cpe_matches(cpe_match_id)
);

--
-- cpe match version minimums (e.g. version starts)
--
CREATE TABLE cpe_match_version_mins (
  cpe_match_id  INTEGER PRIMARY KEY,

  -- valid values
  --
  -- * true: boundary includes this version (e.g. it was specified by
  --   versionStartIncluding)
  -- * false: boundary excludes this version (e.g., it was specified by
  --   versionStartExcluding)
  is_inclusive  BOOLEAN NOT NULL,

  -- minimum version
  min_version   TEXT NOT NULL
                CHECK (LENGTH(min_version) > 0),

  -- add foreign key constraint
  FOREIGN KEY (cpe_match_id)
    REFERENCES cpe_matches(cpe_match_id)
);

--
-- cpe match version maximums (e.g. version ends)
--
CREATE TABLE cpe_match_version_maxs (
  cpe_match_id  INTEGER PRIMARY KEY,

  -- valid values
  --
  -- * true: boundary includes this version (e.g. it was specified by
  --   versionEndIncluding)
  -- * false: boundary excludes this version (e.g., it was specified by
  --   versionEndExcluding)
  is_inclusive  BOOLEAN NOT NULL,

  -- maximum version
  max_version   TEXT NOT NULL
                CHECK (LENGTH(max_version) > 0),

  -- add foreign key constraint
  FOREIGN KEY (cpe_match_id)
    REFERENCES cpe_matches(cpe_match_id)
);

-- map of matching
CREATE TABLE cpe_match_cpes (
  -- CPE match ID
  cpe_match_id  INTEGER NOT NULL
                REFERENCES cpe_matches(cpe_match_id),

  -- CPE ID
  cpe_id        INTEGER NOT NULL
                REFERENCES cpes(cpe_id),

  -- nullable cpe22 URI
  -- normally we would want this in a separate table, but
  -- we don't care that much about this value
  cpe22_uri     TEXT,

  PRIMARY KEY (cpe_match_id, cpe_id)
);