- Fix ANSI labels to put EOF1 and EOF2 after each file mark.

[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 c948ee2ce1b5586723c30a112bea1c0ecedbcb5c..83dfb0e117eecb4bb53bcecbf1a8c290425fd174 100644 (file)
--- a/bacula/src/cats/make_sqlite_tables.in
+++ b/bacula/src/cats/make_sqlite_tables.in
@@ -4,10 +4,11 @@
 
 bindir=@SQL_BINDIR@
 cd @working_dir@
+sqlite=@DB_NAME@
 
-$bindir/sqlite $* bacula.db <<END-OF-DATA
+${bindir}/${sqlite} $* bacula.db <<END-OF-DATA
 CREATE TABLE Filename (
-  FilenameId INTEGER UNSIGNED AUTOINCREMENT,
+  FilenameId INTEGER,
   Name TEXT DEFAULT "",
   PRIMARY KEY(FilenameId) 
   );
@@ -15,7 +16,7 @@ CREATE TABLE Filename (
 CREATE INDEX inx1 ON Filename (Name);
 
 CREATE TABLE Path (
-   PathId INTEGER UNSIGNED AUTOINCREMENT,
+   PathId INTEGER,
    Path TEXT DEFAULT "",
    PRIMARY KEY(PathId) 
    );
@@ -24,7 +25,7 @@ CREATE INDEX inx2 ON Path (Path);
 
 
 CREATE TABLE File (
-   FileId INTEGER UNSIGNED AUTOINCREMENT,
+   FileId INTEGER,
    FileIndex INTEGER UNSIGNED NOT NULL,
    JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
    PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
@@ -36,11 +37,17 @@ CREATE TABLE File (
    );
 
 CREATE INDEX inx3 ON File (JobId);
-CREATE INDEX inx4 ON File (PathId);
-CREATE INDEX inx5 ON File (FileNameId);
+CREATE INDEX inx4 ON File (FilenameId, PathId);
+--
+-- 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 UNSIGNED NOT NULL,
+   JobId INTEGER,
    Job VARCHAR(128) NOT NULL,
    Name VARCHAR(128) NOT NULL,
    Type CHAR NOT NULL,
@@ -67,7 +74,7 @@ CREATE TABLE Job (
 CREATE INDEX inx6 ON Job (Name);
 
 CREATE TABLE FileSet (
-   FileSetId INTEGER UNSIGNED AUTOINCREMENT,
+   FileSetId INTEGER,
    FileSet VARCHAR(128) NOT NULL,
    MD5 VARCHAR(25) NOT NULL,
    CreateTime DATETIME DEFAULT 0,
@@ -75,7 +82,7 @@ CREATE TABLE FileSet (
    );
 
 CREATE TABLE JobMedia (
-   JobMediaId INTEGER UNSIGNED AUTOINCREMENT,
+   JobMediaId INTEGER,
    JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
    MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
    FirstIndex INTEGER UNSIGNED NOT NULL,
@@ -92,11 +99,12 @@ CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
 
 
 CREATE TABLE Media (
-   MediaId INTEGER UNSIGNED AUTOINCREMENT,
+   MediaId INTEGER,
    VolumeName VARCHAR(128) NOT NULL,
    Slot INTEGER DEFAULT 0,
    PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
    MediaType VARCHAR(128) NOT NULL,
+   LabelType TINYINT DEFAULT 0,
    FirstWritten DATETIME DEFAULT 0,
    LastWritten DATETIME DEFAULT 0,
    LabelDate DATETIME DEFAULT 0,
@@ -105,6 +113,7 @@ CREATE TABLE Media (
    VolBlocks INTEGER UNSIGNED DEFAULT 0,
    VolMounts INTEGER UNSIGNED DEFAULT 0,
    VolBytes BIGINT UNSIGNED DEFAULT 0,
+   VolParts INTEGER UNSIGNED DEFAULT 0,
    VolErrors INTEGER UNSIGNED DEFAULT 0,
    VolWrites INTEGER UNSIGNED DEFAULT 0,
    VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
@@ -115,15 +124,54 @@ CREATE TABLE Media (
    MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
    MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
    MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
-   Drive INTEGER DEFAULT 0,
    InChanger TINYINT DEFAULT 0,
+   StorageId INTEGER UNSIGNED REFERENCES Storage,        
+   MediaAddressing TINYINT DEFAULT 0,
+   VolReadTime BIGINT UNSIGNED DEFAULT 0,
+   VolWriteTime BIGINT UNSIGNED DEFAULT 0,
+   EndFile INTEGER UNSIGNED DEFAULT 0,
+   EndBlock INTEGER UNSIGNED DEFAULT 0,
    PRIMARY KEY(MediaId)
    );
 
 CREATE INDEX inx8 ON Media (PoolId);
 
+CREATE TABLE MediaType (
+   MediaTypeId INTEGER,
+   MediaType VARCHAR(128) NOT NULL,
+   ReadOnly TINYINT DEFAULT 0,
+   PRIMARY KEY(MediaTypeId)
+   );
+
+CREATE TABLE Storage (
+   StorageId INTEGER,
+   Name VARCHAR(128) NOT NULL,
+   AutoChanger TINYINT DEFAULT 0,
+   PRIMARY KEY(StorageId)
+   );
+
+CREATE TABLE Device (
+   DeviceId INTEGER,
+   Name VARCHAR(128) NOT NULL,
+   MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
+   StorageId INTEGER UNSIGNED REFERENCES Storage,
+   DevMounts INTEGER UNSIGNED DEFAULT 0,
+   DevReadBytes BIGINT UNSIGNED DEFAULT 0,
+   DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
+   DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
+   DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
+   DevReadTime BIGINT UNSIGNED DEFAULT 0,
+   DevWriteTime BIGINT UNSIGNED DEFAULT 0,
+   DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
+   DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
+   CleaningDate DATETIME DEFAULT 0,
+   CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
+   PRIMARY KEY(DeviceId)
+   );
+
+
 CREATE TABLE Pool (
-   PoolId INTEGER UNSIGNED AUTOINCREMENT,
+   PoolId INTEGER,
    Name VARCHAR(128) NOT NULL,
    NumVols INTEGER UNSIGNED DEFAULT 0,
    MaxVols INTEGER UNSIGNED DEFAULT 0,
@@ -138,14 +186,22 @@ CREATE TABLE Pool (
    AutoPrune TINYINT DEFAULT 0,
    Recycle TINYINT DEFAULT 0,
    PoolType VARCHAR(20) NOT NULL,
+   LabelType TINYINT DEFAULT 0,
    LabelFormat VARCHAR(128) NOT NULL,
+   Enabled TINYINT DEFAULT 1,
+   ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
+   RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
+   NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
+   MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
+   MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
+   MigrationTime BIGINT UNSIGNED DEFAULT 0,
    UNIQUE (Name),
    PRIMARY KEY (PoolId)
    );
 
 
 CREATE TABLE Client (
-   ClientId INTEGER UNSIGNED AUTOINCREMENT,
+   ClientId INTEGER,
    Name VARCHAR(128) NOT NULL,
    Uname VARCHAR(255) NOT NULL,   -- uname -a field
    AutoPrune TINYINT DEFAULT 0,
@@ -156,7 +212,7 @@ CREATE TABLE Client (
    );
 
 CREATE TABLE BaseFiles (
-   BaseId INTEGER UNSIGNED AUTOINCREMENT,
+   BaseId INTEGER,
    BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
    JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
    FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
@@ -165,7 +221,7 @@ CREATE TABLE BaseFiles (
    );
 
 CREATE TABLE UnsavedFiles (
-   UnsavedId INTEGER UNSIGNED AUTOINCREMENT,
+   UnsavedId INTEGER,
    JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
    PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
    FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
@@ -187,7 +243,7 @@ CREATE TABLE Version (
    );
 
 -- Initialize Version           
-INSERT INTO Version (VersionId) VALUES (7);
+INSERT INTO Version (VersionId) VALUES (8);
 
 CREATE TABLE Counters (
    Counter TEXT NOT NULL,     
@@ -198,6 +254,13 @@ CREATE TABLE Counters (
    PRIMARY KEY (Counter)
    );
 
+CREATE TABLE CDImages (
+   MediaId INTEGER UNSIGNED NOT NULL,
+   LastBurn DATETIME NOT NULL,
+   PRIMARY KEY (MediaId)
+   );
+
+
 PRAGMA default_synchronous = OFF;
 PRAGMA default_cache_size = 10000;