From 103dbf934637d80e9624702d039ed2f7a153548d Mon Sep 17 00:00:00 2001
From: Paul Duncan <pabs@pablotron.org>
Date: Thu, 17 Feb 2022 23:19:32 -0500
Subject: dbstore/sql/init.sql: add cpe_match* tables

---
 dbstore/sql/init.sql | 103 +++++++++++++++++++++++++++++++++++++++++++++++++--
 1 file changed, 99 insertions(+), 4 deletions(-)

(limited to 'dbstore')

diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql
index af5aed3..5836642 100644
--- a/dbstore/sql/init.sql
+++ b/dbstore/sql/init.sql
@@ -1,12 +1,15 @@
 -- 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
   cpe_uri       TEXT UNIQUE NOT NULL CHECK(
                   LENGTH(cpe_uri) > 5 AND
                   cpe_uri LIKE 'cpe:/%'
@@ -74,7 +77,7 @@ CREATE VIRTUAL TABLE cpe_fts_titles USING fts5(
 -- 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) 
+    INSERT INTO cpe_fts_all(cpe_id, title, href, val)
       SELECT NEW.cpe_id,
              NEW.val,
              b.href,
@@ -147,7 +150,7 @@ CREATE VIRTUAL TABLE cpe_fts_refs USING fts5(
 -- 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) 
+    INSERT INTO cpe_fts_all(cpe_id, title, href, val)
       SELECT NEW.cpe_id,
              b.val,
              NEW.href,
@@ -185,4 +188,96 @@ CREATE TRIGGER tr_cpe_refs_delete_fts_refs BEFORE DELETE ON cpe_refs
        AND val = OLD.val;
   END;
 
-PRAGMA user_version = 314159;
+--
+-- 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)
+);
-- 
cgit v1.2.3