Change old get_Jobxxx to getJobxxx

[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 12c635fd5585f14771f51cc90b5b7e47b002aa63..78b97c7fef8192faeca439f8d2dcc03f377fcf47 100644 (file)
--- a/bacula/src/cats/make_sqlite3_tables.in
+++ b/bacula/src/cats/make_sqlite3_tables.in
@@ -4,12 +4,13 @@
 
 bindir=@SQL_BINDIR@
 cd @working_dir@
-sqlite=@DB_NAME@
+sqlite=@DB_TYPE@
+db_name=@db_name@
 
-${bindir}/${sqlite} $* bacula.db <<END-OF-DATA
+${bindir}/${sqlite} $* ${db_name}.db <<END-OF-DATA
 CREATE TABLE Filename (
   FilenameId INTEGER,
-  Name TEXT DEFAULT "",
+  Name TEXT DEFAULT '',
   PRIMARY KEY(FilenameId) 
   );
 
@@ -17,13 +18,15 @@ 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,
@@ -37,23 +40,22 @@ CREATE TABLE File (
    );
 
 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 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,
@@ -63,6 +65,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,
@@ -72,16 +75,66 @@ CREATE TABLE Job (
    HasBase TINYINT DEFAULT 0,
    PRIMARY KEY(JobId) 
    );
-
 CREATE INDEX inx6 ON Job (Name);
 
+-- 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
+   );
+CREATE INDEX inx61 ON JobHisto (StartTime);
+
 CREATE TABLE Location (
    LocationId INTEGER,
-   Location TINYBLOB NOT NULL,
-   Cost INTEGER,
+   Location TEXT NOT NULL,
+   Cost INTEGER DEFAULT 0,
+   Enabled TINYINT,
    PRIMARY KEY(LocationId)
    );
 
+CREATE TABLE LocationLog (
+   LocLogId INTEGER,
+   Date DATETIME NOT NULL,
+   Comment TEXT NOT NULL,
+   MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
+   LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
+   NewVolStatus VARCHAR(20) NOT NULL,
+   NewEnabled TINYINT NOT NULL,
+   PRIMARY KEY(LocLogId)
+);
+
+
+CREATE TABLE Log (
+   LogId INTEGER,
+   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
+   Time DATETIME NOT NULL,
+   LogText TEXT NOT NULL,
+   PRIMARY KEY(LogId) 
+   );
+CREATE INDEX LogInx1 ON Log (JobId);
+
 
 CREATE TABLE FileSet (
    FileSetId INTEGER,
@@ -113,7 +166,7 @@ CREATE TABLE Media (
    MediaId INTEGER,
    VolumeName VARCHAR(128) NOT NULL,
    Slot INTEGER DEFAULT 0,
-   PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
+   PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
    MediaType VARCHAR(128) NOT NULL,
    MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
    LabelType TINYINT DEFAULT 0,
@@ -132,6 +185,7 @@ CREATE TABLE Media (
    VolStatus VARCHAR(20) NOT NULL,
    Enabled TINYINT DEFAULT 1,
    Recycle TINYINT DEFAULT 0,
+   ActionOnPurge     TINYINT    DEFAULT 0,
    VolRetention BIGINT UNSIGNED DEFAULT 0,
    VolUseDuration BIGINT UNSIGNED DEFAULT 0,
    MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
@@ -150,6 +204,7 @@ CREATE TABLE Media (
    InitialWrite DATETIME DEFAULT 0,
    ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
    RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
+   Comment TEXT,
    PRIMARY KEY(MediaId)
    );
 
@@ -204,6 +259,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,
@@ -254,8 +310,10 @@ CREATE TABLE NextId (
    PRIMARY KEY (TableName)
    );
 
+
+
 -- Initialize JobId to start at 1
-INSERT INTO NextId (id, TableName) VALUES (1, "Job");
+INSERT INTO NextId (id, TableName) VALUES (1, 'Job');
 
 CREATE TABLE Version (
    VersionId INTEGER UNSIGNED NOT NULL 
@@ -322,16 +380,20 @@ 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');
 
 
 -- Initialize Version           
-INSERT INTO Version (VersionId) VALUES (10);
+INSERT INTO Version (VersionId) VALUES (11);
 
 
-PRAGMA default_synchronous = OFF;
-PRAGMA default_cache_size = 10000;
+PRAGMA default_cache_size = 100000;
+PRAGMA synchronous = NORMAL;
 
 END-OF-DATA
 
-chmod 640 bacula.db
+chmod 640 ${db_name}.db
 exit 0