Fix typo in make_sqlite3_tables.in submitted by Carsten Leonhardt

[bacula/bacula] / bacula / src / cats / make_sqlite3_tables.in

diff --git a/bacula/src/cats/make_sqlite3_tables.in b/bacula/src/cats/make_sqlite3_tables.in
index 55548412a55f16699435184c3d1cc335c42beb13..c5f3b09c2edb4744923b278914b3dd03426f9281 100644 (file)
--- a/bacula/src/cats/make_sqlite3_tables.in
+++ b/bacula/src/cats/make_sqlite3_tables.in
@@ -2,8 +2,8 @@
 #
 # shell script to create Bacula SQLite tables
 #
-#  Author:  Kern Sibbald
-#  License: LGPLv3
+# Copyright (C) 2000-2017 Kern Sibbald
+# License: BSD 2-Clause; see file LICENSE-FOSS
 #
 
 bindir=@SQLITE_BINDIR@
@@ -98,9 +98,11 @@ CREATE TABLE Job (
    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 (
@@ -130,7 +132,8 @@ CREATE TABLE JobHisto (
    HasBase TINYINT DEFAULT 0,
    HasCache TINYINT DEFAULT 0,
    Reviewed TINYINT DEFAULT 0,
-   Comment TEXT
+   Comment TEXT,
+   FileTable TEXT DEFAULT 'File'
    );
 CREATE INDEX inx61 ON JobHisto (StartTime);
 
@@ -172,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,
@@ -203,16 +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,
@@ -235,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,
@@ -279,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,
@@ -301,6 +318,7 @@ CREATE TABLE Pool (
    PRIMARY KEY (PoolId)
    );
 
+CREATE INDEX pool_name_idx on Pool (Name);
 
 CREATE TABLE Client (
    ClientId INTEGER,
@@ -313,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,
@@ -333,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 
    );
@@ -386,6 +395,25 @@ CREATE TABLE PathVisibility
 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, 
@@ -435,16 +463,22 @@ 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 (@BDB_VERSION@);
-
+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