First cut 1.27 see kes23Oct02

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

diff --git a/bacula/src/cats/make_sqlite_tables.in b/bacula/src/cats/make_sqlite_tables.in
index f811e69873cb539f47d02ccd1391d1016f1e6f27..e50288c488bd087306d89965048ac7ddc3cd3017 100644 (file)
--- a/bacula/src/cats/make_sqlite_tables.in
+++ b/bacula/src/cats/make_sqlite_tables.in
@@ -1,6 +1,6 @@
 #!/bin/sh
 #
-# shell script to create Bacula tables
+# shell script to create Bacula SQLite tables
 
 bindir=@SQL_BINDIR@
 cd @working_dir@
@@ -12,12 +12,16 @@ CREATE TABLE Filename (
   PRIMARY KEY(FilenameId) 
   );
 
+CREATE INDEX inx1 ON Filename (Name);
+
 CREATE TABLE Path (
    PathId INTEGER UNSIGNED AUTOINCREMENT,
    Path TEXT DEFAULT "",
    PRIMARY KEY(PathId) 
    );
 
+CREATE INDEX inx2 ON Path (Path);
+
 
 CREATE TABLE File (
    FileId INTEGER UNSIGNED AUTOINCREMENT,
@@ -25,11 +29,16 @@ CREATE TABLE File (
    JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
    PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
    FilenameId INTEGER REFERENCES Filename NOT NULL,
+   MarkId INTEGER UNSIGNED DEFAULT 0,
    LStat VARCHAR(255) NOT NULL,
    MD5 VARCHAR(25) NOT NULL,
    PRIMARY KEY(FileId) 
    );
 
+CREATE INDEX inx3 ON File (JobId);
+CREATE INDEX inx4 ON File (PathId);
+CREATE INDEX inx5 ON File (FileNameId);
+
 CREATE TABLE Job (
    JobId INTEGER UNSIGNED NOT NULL,
    Job VARCHAR(128) NOT NULL,
@@ -38,11 +47,11 @@ CREATE TABLE Job (
    Type CHAR NOT NULL,
    Level CHAR NOT NULL,
    ClientId INTEGER REFERENCES Client DEFAULT 0,
-   JobStatus CHAR,
+   JobStatus CHAR NOT NULL,
    SchedTime DATETIME NOT NULL,
    StartTime DATETIME DEFAULT 0,
    EndTime DATETIME DEFAULT 0,
-   StartDay INTEGER UNSIGNED DEFAULT 0,
+   JobTDate BIGINT UNSIGNED DEFAULT 0,
    VolSessionId INTEGER UNSIGNED DEFAULT 0,
    VolSessionTime INTEGER UNSIGNED DEFAULT 0,
    JobFiles INTEGER UNSIGNED DEFAULT 0,
@@ -54,6 +63,8 @@ CREATE TABLE Job (
    PRIMARY KEY(JobId) 
    );
 
+CREATE INDEX inx6 ON Job (Name);
+
 CREATE TABLE FileSet (
    FileSetId INTEGER UNSIGNED AUTOINCREMENT,
    FileSet VARCHAR(128) NOT NULL,
@@ -74,10 +85,13 @@ CREATE TABLE JobMedia (
    PRIMARY KEY(JobMediaId) 
    );
 
+CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
+
 
 CREATE TABLE Media (
    MediaId INTEGER UNSIGNED AUTOINCREMENT,
    VolumeName VARCHAR(128) NOT NULL,
+   Slot INTEGER DEFAULT 0,
    PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
    MediaType VARCHAR(128) NOT NULL,
    FirstWritten DATETIME DEFAULT 0,
@@ -93,18 +107,24 @@ CREATE TABLE Media (
    VolMaxBytes BIGINT UNSIGNED DEFAULT 0,
    VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
    VolStatus VARCHAR(20) NOT NULL,
-   Recycle VARCHAR(20) NOT NULL,
+   Recycle TINYINT DEFAULT 0,
+   VolRetention BIGINT UNSIGNED DEFAULT 0,
    PRIMARY KEY(MediaId)
    );
 
+CREATE INDEX inx8 ON Media (PoolId);
+
 CREATE TABLE Pool (
    PoolId INTEGER UNSIGNED AUTOINCREMENT,
    Name VARCHAR(128) NOT NULL,
-   NumVols INTEGER UNSIGNED NOT NULL,
-   MaxVols INTEGER UNSIGNED NOT NULL,
-   UseOnce TINYINT NOT NULL,
-   UseCatalog TINYINT NOT NULL,
-   AcceptAnyVolume TINYINT NOT NULL,
+   NumVols INTEGER UNSIGNED DEFAULT 0,
+   MaxVols INTEGER UNSIGNED DEFAULT 0,
+   UseOnce TINYINT DEFAULT 0,
+   UseCatalog TINYINT DEFAULT 1,
+   AcceptAnyVolume TINYINT DEFAULT 0,
+   VolRetention BIGINT UNSIGNED DEFAULT 0,
+   AutoPrune TINYINT DEFAULT 0,
+   Recycle TINYINT DEFAULT 0,
    PoolType VARCHAR(20) NOT NULL,
    LabelFormat VARCHAR(128) NOT NULL,
    UNIQUE (Name),
@@ -116,6 +136,9 @@ CREATE TABLE Client (
    ClientId INTEGER UNSIGNED AUTOINCREMENT,
    Name VARCHAR(128) NOT NULL,
    Uname VARCHAR(255) NOT NULL,   -- uname -a field
+   AutoPrune TINYINT DEFAULT 0,
+   FileRetention BIGINT UNSIGNED DEFAULT 0,
+   JobRetention  BIGINT UNSIGNED DEFAULT 0,
    UNIQUE (Name),
    PRIMARY KEY(ClientId)
    );
@@ -129,25 +152,25 @@ CREATE TABLE NextId (
 -- Initialize JobId to start at 1
 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
 
--- Experimental stuff below. Not used.
--- Invariant part of File
-CREATE TABLE BaseFile (
-   FileId INTEGER UNSIGNED AUTOINCREMENT,
-   PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
-   FilenameId INTEGER REFERENCES Filename NOT NULL,
-   LStat VARCHAR(255) NOT NULL,
-   MD5 VARCHAR(25) NOT NULL,
-   PRIMARY KEY(FileId) 
+CREATE TABLE Version (
+   VersionId INTEGER UNSIGNED NOT NULL 
    );
 
--- Variable part of File
-CREATE TABLE FileSave (
-   FileId INTEGER UNSIGNED REFERENCES BaseFile NOT NULL,
-   FileIndex INTEGER UNSIGNED NOT NULL,
-   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
-   PRIMARY KEY(FileId) 
+-- Initialize Version           
+INSERT INTO Version (VersionId) VALUES (2);
+
+CREATE TABLE Counters (
+   Counter TEXT NOT NULL,     
+   PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
+   MinValue INTEGER DEFAULT 0, 
+   MaxValue INTEGER DEFAULT 0,
+   CurrentValue INTEGER DEFAULT 0,
+   WrapCounter TEXT NOT NULL,
+   PRIMARY KEY (Counter)
    );
 
+PRAGMA default_synchronous = OFF;
+PRAGMA default_cache_size = 10000;
 
 END-OF-DATA
 exit 0