46 lines
2.4 KiB
SQL
46 lines
2.4 KiB
SQL
CREATE TABLE assessor_scanner_report_sections (
|
|
id text PRIMARY KEY CHECK (btrim(id) <> ''),
|
|
scan_run_id text NOT NULL REFERENCES assessor_scan_runs(id) ON DELETE CASCADE,
|
|
import_file_id text NOT NULL REFERENCES assessor_import_files(id) ON DELETE CASCADE,
|
|
tool_id text NOT NULL CHECK (btrim(tool_id) <> ''),
|
|
section_key text NOT NULL CHECK (btrim(section_key) <> ''),
|
|
section_state text NOT NULL CHECK (section_state IN (
|
|
'observed', 'explicitly-absent', 'empty', 'not-assessed', 'access-denied',
|
|
'license-restricted', 'unsupported', 'parse-failed', 'unknown'
|
|
)),
|
|
ordinal integer NOT NULL CHECK (ordinal >= 0),
|
|
data jsonb NOT NULL CHECK (jsonb_typeof(data) = 'object'),
|
|
UNIQUE (import_file_id, section_key)
|
|
);
|
|
|
|
CREATE INDEX assessor_scanner_report_sections_run_idx
|
|
ON assessor_scanner_report_sections(scan_run_id, ordinal, id);
|
|
CREATE INDEX assessor_scanner_report_sections_lookup_idx
|
|
ON assessor_scanner_report_sections(tool_id, section_key, section_state);
|
|
CREATE INDEX assessor_scanner_report_sections_data_idx
|
|
ON assessor_scanner_report_sections USING gin (data jsonb_path_ops);
|
|
|
|
CREATE TABLE assessor_environment_observations (
|
|
id text PRIMARY KEY CHECK (btrim(id) <> ''),
|
|
scan_run_id text NOT NULL REFERENCES assessor_scan_runs(id) ON DELETE CASCADE,
|
|
import_file_id text NOT NULL REFERENCES assessor_import_files(id) ON DELETE CASCADE,
|
|
tool_id text NOT NULL CHECK (btrim(tool_id) <> ''),
|
|
subject_type text NOT NULL CHECK (btrim(subject_type) <> ''),
|
|
property_key text NOT NULL CHECK (btrim(property_key) <> ''),
|
|
observation_state text NOT NULL CHECK (observation_state IN (
|
|
'observed', 'explicitly-absent', 'empty', 'not-assessed', 'access-denied',
|
|
'license-restricted', 'unsupported', 'parse-failed', 'unknown'
|
|
)),
|
|
observed_at timestamptz,
|
|
data jsonb NOT NULL CHECK (jsonb_typeof(data) = 'object')
|
|
);
|
|
|
|
CREATE INDEX assessor_environment_observations_run_idx
|
|
ON assessor_environment_observations(scan_run_id, property_key, id);
|
|
CREATE INDEX assessor_environment_observations_lookup_idx
|
|
ON assessor_environment_observations(tool_id, subject_type, property_key, observation_state);
|
|
CREATE INDEX assessor_environment_observations_observed_at_idx
|
|
ON assessor_environment_observations(observed_at) WHERE observed_at IS NOT NULL;
|
|
CREATE INDEX assessor_environment_observations_data_idx
|
|
ON assessor_environment_observations USING gin (data jsonb_path_ops);
|