X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=bacula%2Fsrc%2Fcats%2Fmake_sqlite3_tables.in;h=c5f3b09c2edb4744923b278914b3dd03426f9281;hb=1dd06081b18f4a39b5a13d91e90282fa2e9f3700;hp=b284fcb2c37a191b868f9e5491310fc1fc9eaaea;hpb=9cb39f481a136fdebe527e660348c34969944fc6;p=bacula%2Fbacula

diff --git a/bacula/src/cats/make_sqlite3_tables.in b/bacula/src/cats/make_sqlite3_tables.in
index b284fcb2c3..c5f3b09c2e 100644
--- a/bacula/src/cats/make_sqlite3_tables.in
+++ b/bacula/src/cats/make_sqlite3_tables.in
@@ -1,16 +1,20 @@
 #!/bin/sh
 #
 # shell script to create Bacula SQLite tables
+#
+# Copyright (C) 2000-2017 Kern Sibbald
+# License: BSD 2-Clause; see file LICENSE-FOSS
+#
 
-bindir=@SQL_BINDIR@
+bindir=@SQLITE_BINDIR@
+PATH="$bindir:$PATH"
 cd @working_dir@
-sqlite=@DB_TYPE@
 db_name=@db_name@
 
-${bindir}/${sqlite} $* ${db_name}.db <<END-OF-DATA
+sqlite3 $* ${db_name}.db <<END-OF-DATA
 CREATE TABLE Filename (
   FilenameId INTEGER,
-  Name TEXT DEFAULT "",
+  Name TEXT DEFAULT '',
   PRIMARY KEY(FilenameId) 
   );
 
@@ -18,43 +22,62 @@ CREATE INDEX inx1 ON Filename (Name);
 
 CREATE TABLE Path (
    PathId INTEGER,
-   Path TEXT DEFAULT "",
+   Path TEXT DEFAULT '',
    PRIMARY KEY(PathId) 
    );
 
 CREATE INDEX inx2 ON Path (Path);
 
-
+-- In File table
+-- FileIndex can be 0 for FT_DELETED files
+-- FileNameId can link to Filename.Name='' for directories
 CREATE TABLE File (
    FileId INTEGER,
    FileIndex INTEGER UNSIGNED NOT NULL,
    JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
    PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
    FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
+   DeltaSeq SMALLINT UNSIGNED DEFAULT 0,
    MarkId INTEGER UNSIGNED DEFAULT 0,
    LStat VARCHAR(255) NOT NULL,
    MD5 VARCHAR(255) NOT NULL,
    PRIMARY KEY(FileId) 
    );
-
 CREATE INDEX inx3 ON File (JobId);
-CREATE INDEX inx4 ON File (FilenameId, PathId);
+CREATE INDEX file_jpf_idx ON File (JobId, PathId, FilenameId);
 --
 -- Possibly add one or more of the following indexes
 --  if your Verifies are too slow.
 --
 -- CREATE INDEX inx4 ON File (PathId);
 -- CREATE INDEX inx5 ON File (FileNameId);
--- CREATE INDEX inx9 ON File (JobId, PathId, FilenameId);
+
+
+CREATE TABLE RestoreObject (
+   RestoreObjectId INTEGER,
+   ObjectName TEXT DEFAULT '',
+   RestoreObject TEXT DEFAULT '',
+   PluginName TEXT DEFAULT '',
+   ObjectLength INTEGER DEFAULT 0,
+   ObjectFullLength INTEGER DEFAULT 0,
+   ObjectIndex INTEGER DEFAULT 0,
+   ObjectType INTEGER DEFAULT 0,
+   FileIndex INTEGER UNSIGNED DEFAULT 0,
+   ObjectCompression INTEGER DEFAULT 0,
+   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
+   PRIMARY KEY(RestoreObjectId)
+   );
+CREATE INDEX restore_jobid_idx ON RestoreObject (JobId);
+
 
 CREATE TABLE Job (
    JobId INTEGER,
    Job VARCHAR(128) NOT NULL,
    Name VARCHAR(128) NOT NULL,
-   Type CHAR NOT NULL,
-   Level CHAR NOT NULL,
+   Type CHAR(1) NOT NULL,
+   Level CHAR(1) NOT NULL,
    ClientId INTEGER REFERENCES Client DEFAULT 0,
-   JobStatus CHAR NOT NULL,
+   JobStatus CHAR(1) NOT NULL,
    SchedTime DATETIME NOT NULL,
    StartTime DATETIME DEFAULT 0,
    EndTime DATETIME DEFAULT 0,
@@ -64,6 +87,7 @@ CREATE TABLE Job (
    VolSessionTime INTEGER UNSIGNED DEFAULT 0,
    JobFiles INTEGER UNSIGNED DEFAULT 0,
    JobBytes BIGINT UNSIGNED DEFAULT 0,
+   ReadBytes BIGINT UNSIGNED DEFAULT 0,
    JobErrors INTEGER UNSIGNED DEFAULT 0,
    JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
    PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
@@ -71,9 +95,47 @@ CREATE TABLE Job (
    PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
    PurgedFiles TINYINT DEFAULT 0,
    HasBase TINYINT DEFAULT 0,
+   HasCache TINYINT DEFAULT 0,
+   Reviewed TINYINT DEFAULT 0,
+   Comment TEXT,
+   FileTable TEXT DEFAULT 'File',
    PRIMARY KEY(JobId) 
    );
 CREATE INDEX inx6 ON Job (Name);
+CREATE INDEX job_jobtdate_inx ON Job (JobTDate);
+
+-- Create a table like Job for long term statistics 
+CREATE TABLE JobHisto (
+   JobId INTEGER,
+   Job VARCHAR(128) NOT NULL,
+   Name VARCHAR(128) NOT NULL,
+   Type CHAR(1) NOT NULL,
+   Level CHAR(1) NOT NULL,
+   ClientId INTEGER DEFAULT 0,
+   JobStatus CHAR(1) NOT NULL,
+   SchedTime DATETIME NOT NULL,
+   StartTime DATETIME DEFAULT 0,
+   EndTime DATETIME DEFAULT 0,
+   RealEndTime DATETIME DEFAULT 0,
+   JobTDate BIGINT UNSIGNED DEFAULT 0,
+   VolSessionId INTEGER UNSIGNED DEFAULT 0,
+   VolSessionTime INTEGER UNSIGNED DEFAULT 0,
+   JobFiles INTEGER UNSIGNED DEFAULT 0,
+   JobBytes BIGINT UNSIGNED DEFAULT 0,
+   ReadBytes BIGINT UNSIGNED DEFAULT 0,
+   JobErrors INTEGER UNSIGNED DEFAULT 0,
+   JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
+   PoolId INTEGER UNSIGNED DEFAULT 0,
+   FileSetId INTEGER UNSIGNED DEFAULT 0,
+   PriorJobId INTEGER UNSIGNED DEFAULT 0,
+   PurgedFiles TINYINT DEFAULT 0,
+   HasBase TINYINT DEFAULT 0,
+   HasCache TINYINT DEFAULT 0,
+   Reviewed TINYINT DEFAULT 0,
+   Comment TEXT,
+   FileTable TEXT DEFAULT 'File'
+   );
+CREATE INDEX inx61 ON JobHisto (StartTime);
 
 CREATE TABLE Location (
    LocationId INTEGER,
@@ -113,6 +175,8 @@ CREATE TABLE FileSet (
    PRIMARY KEY(FileSetId)
    );
 
+CREATE INDEX fileset_name_idx on FileSet (FileSet);
+
 CREATE TABLE JobMedia (
    JobMediaId INTEGER,
    JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
@@ -124,7 +188,6 @@ CREATE TABLE JobMedia (
    StartBlock INTEGER UNSIGNED DEFAULT 0,
    EndBlock INTEGER UNSIGNED DEFAULT 0,
    VolIndex INTEGER UNSIGNED DEFAULT 0,
-   Copy     INTEGER UNSIGNED DEFAULT 0,
    PRIMARY KEY(JobMediaId) 
    );
 
@@ -145,15 +208,24 @@ CREATE TABLE Media (
    VolJobs INTEGER UNSIGNED DEFAULT 0,
    VolFiles INTEGER UNSIGNED DEFAULT 0,
    VolBlocks INTEGER UNSIGNED DEFAULT 0,
+   LastPartBytes BIGINT UNSIGNED DEFAULT 0,
    VolMounts INTEGER UNSIGNED DEFAULT 0,
    VolBytes BIGINT UNSIGNED DEFAULT 0,
+   VolABytes BIGINT UNSIGNED DEFAULT 0,
+   VolAPadding BIGINT UNSIGNED DEFAULT 0,
+   VolHoleBytes BIGINT UNSIGNED DEFAULT 0,
+   VolHoles INTEGER UNSIGNED DEFAULT 0,
+   VolType INTEGER UNSIGNED DEFAULT 0,
    VolParts INTEGER UNSIGNED DEFAULT 0,
+   VolCloudParts INTEGER UNSIGNED DEFAULT 0,
    VolErrors INTEGER UNSIGNED DEFAULT 0,
-   VolWrites INTEGER UNSIGNED DEFAULT 0,
+   VolWrites BIGINT UNSIGNED DEFAULT 0,
    VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
    VolStatus VARCHAR(20) NOT NULL,
    Enabled TINYINT DEFAULT 1,
    Recycle TINYINT DEFAULT 0,
+   ActionOnPurge     TINYINT	DEFAULT 0,
+   CacheRetention BIGINT UNSIGNED DEFAULT 0,
    VolRetention BIGINT UNSIGNED DEFAULT 0,
    VolUseDuration BIGINT UNSIGNED DEFAULT 0,
    MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
@@ -176,7 +248,10 @@ CREATE TABLE Media (
    PRIMARY KEY(MediaId)
    );
 
+CREATE UNIQUE INDEX Media_Volumename_Id ON Media (VolumeName);
 CREATE INDEX inx8 ON Media (PoolId);
+CREATE INDEX inx9 ON Media (StorageId);
+
 
 CREATE TABLE MediaType (
    MediaTypeId INTEGER,
@@ -220,6 +295,7 @@ CREATE TABLE Pool (
    UseOnce TINYINT DEFAULT 0,
    UseCatalog TINYINT DEFAULT 1,
    AcceptAnyVolume TINYINT DEFAULT 0,
+   CacheRetention BIGINT UNSIGNED DEFAULT 0,
    VolRetention BIGINT UNSIGNED DEFAULT 0,
    VolUseDuration BIGINT UNSIGNED DEFAULT 0,
    MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
@@ -227,6 +303,7 @@ CREATE TABLE Pool (
    MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
    AutoPrune TINYINT DEFAULT 0,
    Recycle TINYINT DEFAULT 0,
+   ActionOnPurge     TINYINT	DEFAULT 0,
    PoolType VARCHAR(20) NOT NULL,
    LabelType TINYINT DEFAULT 0,
    LabelFormat VARCHAR(128) NOT NULL,
@@ -241,6 +318,7 @@ CREATE TABLE Pool (
    PRIMARY KEY (PoolId)
    );
 
+CREATE INDEX pool_name_idx on Pool (Name);
 
 CREATE TABLE Client (
    ClientId INTEGER,
@@ -253,8 +331,10 @@ CREATE TABLE Client (
    PRIMARY KEY(ClientId)
    );
 
+CREATE UNIQUE INDEX client_name_idx on Client (Name);
+
 CREATE TABLE BaseFiles (
-   BaseId INTEGER,
+   BaseId BIGINT,
    BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
    JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
    FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
@@ -262,6 +342,8 @@ CREATE TABLE BaseFiles (
    PRIMARY KEY(BaseId)
    );
 
+CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
+
 CREATE TABLE UnsavedFiles (
    UnsavedId INTEGER,
    JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
@@ -271,17 +353,6 @@ CREATE TABLE UnsavedFiles (
    );
 
 
-CREATE TABLE NextId (
-   id INTEGER UNSIGNED DEFAULT 0,
-   TableName TEXT NOT NULL,
-   PRIMARY KEY (TableName)
-   );
-
-
-
--- Initialize JobId to start at 1
-INSERT INTO NextId (id, TableName) VALUES (1, "Job");
-
 CREATE TABLE Version (
    VersionId INTEGER UNSIGNED NOT NULL 
    );
@@ -302,65 +373,112 @@ CREATE TABLE CDImages (
    PRIMARY KEY (MediaId)
    );
 
+CREATE TABLE PathHierarchy
+(
+     PathId integer NOT NULL,
+     PPathId integer NOT NULL,
+     CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
+);
+
+CREATE INDEX pathhierarchy_ppathid 
+	  ON PathHierarchy (PPathId);
+
+CREATE TABLE PathVisibility
+(
+      PathId integer NOT NULL,
+      JobId integer NOT NULL,
+      Size int8 DEFAULT 0,
+      Files int4 DEFAULT 0,
+      CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
+);
+
+CREATE INDEX pathvisibility_jobid
+	  ON PathVisibility (JobId);
+
+
+CREATE TABLE Snapshot (
+  SnapshotId	  serial,
+  Name		  text NOT NULL,
+  JobId 	  integer default 0,
+  FileSetId	  integer default 0,
+  CreateTDate	  bigint default 0,
+  CreateDate	  datetime not null,
+  ClientId	  int default 0,
+  Volume	  text not null,
+  Device	  text not null,
+  Type		  text not null,
+  Retention	  int default 0,
+  Comment	  text,
+  primary key (SnapshotId)
+);
+
+CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device, Volume, Name);
 
 CREATE TABLE Status (
    JobStatus CHAR(1) NOT NULL,
    JobStatusLong BLOB, 
+   Severity INT,
    PRIMARY KEY (JobStatus)
    );
 
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('C', 'Created, not yet running');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('R', 'Running');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('B', 'Blocked');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('T', 'Completed successfully');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('E', 'Terminated with errors');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('e', 'Non-fatal error');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('f', 'Fatal error');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('D', 'Verify found differences');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('A', 'Canceled by user');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('F', 'Waiting for Client');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('S', 'Waiting for Storage daemon');
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('C', 'Created, not yet running',15);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('R', 'Running',15);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('B', 'Blocked',15);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('T', 'Completed successfully', 10);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('E', 'Terminated with errors', 25);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('e', 'Non-fatal error',20);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('f', 'Fatal error',100);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('D', 'Verify found differences',15);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('A', 'Canceled by user',90);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('F', 'Waiting for Client',15);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('S', 'Waiting for Storage daemon',15);
 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
    ('m', 'Waiting for new media');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('M', 'Waiting for media mount');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('s', 'Waiting for storage resource');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('j', 'Waiting for job resource');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('c', 'Waiting for client resource');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('d', 'Waiting on maximum jobs');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('t', 'Waiting on start time');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('p', 'Waiting on higher priority jobs');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('a', 'SD despooling attributes');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('i', 'Doing batch insert file records');
-
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('M', 'Waiting for media mount',15);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('s', 'Waiting for storage resource',15);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('j', 'Waiting for job resource',15);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('c', 'Waiting for client resource',15);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('d', 'Waiting on maximum jobs',15);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('t', 'Waiting on start time',15);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('p', 'Waiting on higher priority jobs',15);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('a', 'SD despooling attributes',15);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('i', 'Doing batch insert file records',15);
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('I', 'Incomplete Job',25);
 
 -- Initialize Version		 
-INSERT INTO Version (VersionId) VALUES (10);
-
+INSERT INTO Version (VersionId) VALUES (16);
 
 PRAGMA default_cache_size = 100000;
 PRAGMA synchronous = NORMAL;
 
 END-OF-DATA
 
+echo ""
+echo "WARNING: !!!! SQLite3 is no longer supported. !!!!"
+echo "WARNING: !!!! Please switch to MySQL or PostgreSQL !!!!"
+echo "WARNING: !!!!  as soon as possible. !!!!"
+echo ""
+
 chmod 640 ${db_name}.db
 exit 0