From 5732efd60c1fb50778b8d940e05b15d22bb27ada Mon Sep 17 00:00:00 2001
From: Eric Bollengier <eric@baculasystems.com>
Date: Fri, 12 Aug 2022 10:38:30 +0200
Subject: [PATCH] Add new SQLite3 fields in make_sqlite3_tables

---
 bacula/src/cats/make_sqlite3_tables.in | 38 ++++++++++++++++++++++++++
 1 file changed, 38 insertions(+)

diff --git a/bacula/src/cats/make_sqlite3_tables.in b/bacula/src/cats/make_sqlite3_tables.in
index 2f8b497f7..e36f7e513 100644
--- a/bacula/src/cats/make_sqlite3_tables.in
+++ b/bacula/src/cats/make_sqlite3_tables.in
@@ -13,6 +13,35 @@ db_name=@db_name@
 
 sqlite3 $* ${db_name}.db <<END-OF-DATA
 -- --------------------------------------------------------------
+
+CREATE TABLE MalwareMD5
+(
+    MD5     char(22)    -- Same as File
+);
+CREATE INDEX malwaremd5_idx on MalwareMD5 (MD5);
+
+CREATE TABLE MalwareSHA256
+(
+    MD5     char(65)    -- Same as File
+);
+CREATE INDEX malwaresha256_idx on MalwareSHA256 (MD5);
+
+CREATE TABLE FileEvents 
+(
+   Id   bigserial,
+   Time  DATETIME DEFAULT current_timestamp, -- Time of the detection
+   SourceJobId   int,        -- Can be the Verify job id for example, or the jobid during a restore/backup
+   JobId         int,        -- JobId where the file was found. Used for pruning
+   FileIndex     int,        -- File reference
+   Type          char,       -- Event type (antivirus, malware scanning (M), lost file)
+   Description   text,       -- Description of the event
+   Severity      int,        -- level of severity. (0 OK, 100 Important)
+   Source        text        -- Information about the source of the event
+);
+
+CREATE INDEX FileEvents_jobid_idx ON FileEvents (JobId, FileIndex);
+CREATE INDEX FileEvents_sourcejobid_idx ON FileEvents (SourceJobId);
+
 -- MetaData Index
 
 CREATE TABLE MetaEmail
@@ -203,6 +232,7 @@ CREATE TABLE Job (
    StartTime DATETIME DEFAULT 0,
    EndTime DATETIME DEFAULT 0,
    RealEndTime DATETIME DEFAULT 0,
+   RealStartTime DATETIME DEFAULT 0, -- Time of the job for Virtual Full
    JobTDate BIGINT UNSIGNED DEFAULT 0,
    VolSessionId INTEGER UNSIGNED DEFAULT 0,
    VolSessionTime INTEGER UNSIGNED DEFAULT 0,
@@ -221,6 +251,14 @@ CREATE TABLE Job (
    Reviewed TINYINT DEFAULT 0,
    Comment TEXT,
    FileTable TEXT DEFAULT 'File',
+   isVirtualFull     TINYINT     default 0,   -- Result of a VirtualFull?
+   CompressRatio     float       default 0,   -- Compression Ratio
+   Rate              float       default 0,   -- Job Rate B/s
+   LastReadStorageId integer     default 0,   -- Id of the last Storage used to Read
+   LastReadDevice    text        default '',  -- Name of the last Read Device
+   WriteStorageId    integer     default 0,   -- Id of the Storage used to Write
+   WriteDevice       text        default '',  -- Name of the Write Device
+   StatusInfo        text        default '',  -- Info to determine the error status
    PRIMARY KEY(JobId) 
    );
 CREATE INDEX inx6 ON Job (Name);
-- 
2.47.3