37 lines
No EOL
2.1 KiB
SQL
37 lines
No EOL
2.1 KiB
SQL
create table reports
|
|
(
|
|
report_id varchar(36) default uuid() not null comment 'The UUID V4 for the report''s ID'
|
|
primary key,
|
|
reference_document varchar(36) not null comment 'The reference document to which this report came from',
|
|
type varchar(32) default 'OTHER' not null comment 'The standard report type',
|
|
reporting_peer varchar(256) null comment 'The peer that submitted the report, if empty will assume the client is the reporter.',
|
|
target_peer varchar(36) not null comment 'The target peer that the report is against at',
|
|
scan_mode varchar(32) null comment 'The scan mode that was used to conduct the automated report',
|
|
confidence_score float null comment 'The confidence score of the scanner if the report was automated',
|
|
reason text null comment 'The reason for the report',
|
|
submission_timestamp bigint default unix_timestamp() null comment 'The Unix Timestamp for when this report was submitted to the database',
|
|
constraint reports_report_id_uindex
|
|
unique (report_id),
|
|
constraint reports_discovered_peers_federated_address_fk
|
|
foreign key (reporting_peer) references peers (federated_address),
|
|
constraint reports_discovered_peers_federated_address_fk2
|
|
foreign key (target_peer) references peers (federated_address),
|
|
constraint reports_query_documents_document_id_fk
|
|
foreign key (reference_document) references query_documents (document_id)
|
|
)
|
|
comment 'The table for storing reports made against peers';
|
|
|
|
create index reports_reference_document_index
|
|
on reports (reference_document);
|
|
|
|
create index reports_reporting_peer_index
|
|
on reports (reporting_peer);
|
|
|
|
create index reports_submission_timestamp_index
|
|
on reports (submission_timestamp);
|
|
|
|
create index reports_target_peer_index
|
|
on reports (target_peer);
|
|
|
|
create index reports_type_index
|
|
on reports (type); |