Backport from Bacula Enterprise

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

diff --git a/bacula/src/cats/update_sqlite3_tables.in b/bacula/src/cats/update_sqlite3_tables.in
index 1dd95d55e384a29dbcbd1798bfbafa39db946375..0f1f15abf6c052041b39060111ab2b8c7dbd9d25 100644 (file)
--- a/bacula/src/cats/update_sqlite3_tables.in
+++ b/bacula/src/cats/update_sqlite3_tables.in
@@ -1,53 +1,112 @@
 #!/bin/sh
 #
-# shell script to update SQLite from version 2.0 to 3.0
+# Copyright (C) 2000-2015 Kern Sibbald
+# License: BSD 2-Clause; see file LICENSE-FOSS
 #
+# Shell script to update MySQL tables from Bacula Community version 
+#  5.0.x, 5.2.x, 7.0.x
+#
+echo " "
+echo "This script will update a Bacula MySQL database from version 12-14 to 15"
 echo " "
-echo "This script will update a Bacula SQLite database from version 10 to 11"
-echo " which is needed to convert from Bacula version 2.0.x to 3.0.x or higher"
-echo "Depending on the size of your database,"
-echo "this script may take several minutes to run."
+echo "Depending on the current version of your catalog, you may have to run this script"
+echo " multiple times"
 echo " "
 
-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
-BEGIN TRANSACTION;
-
--- Create a table like Job for long term statistics
-CREATE TABLE JobHistory (
-   JobId INTEGER,
-   Job VARCHAR(128) NOT NULL,
-   Name VARCHAR(128) NOT NULL,
-   Type CHAR NOT NULL,
-   Level CHAR NOT NULL,
-   ClientId INTEGER REFERENCES Client DEFAULT 0,
-   JobStatus CHAR 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,
-   JobErrors INTEGER UNSIGNED DEFAULT 0,
-   JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
-   PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
-   FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
-   PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
-   PurgedFiles TINYINT DEFAULT 0,
-   HasBase TINYINT DEFAULT 0,
-   PRIMARY KEY(JobId) 
+DBVERSION=`sqlite3 ${db_name}.db <<END
+SELECT VersionId FROM Version LIMIT 1;
+END
+`
+if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 14 ] ; then
+    echo " "
+    echo "The existing database is version $DBVERSION !!"
+    echo "This script can only update an existing version 12, 13, 14 or 1014 database to version 1015."
+    echo "Error. Cannot upgrade this database."
+    echo " "
+    exit 1
+fi
+
+if [ "$DBVERSION" = 12 ] ; then
+sqlite3 $* ${db_name}.db <<END-OF-DATA
+BEGIN;
+
+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 inx61 ON JobHistory (Name);
+CREATE INDEX restore_jobid_idx ON RestoreObject (JobId);
+
+UPDATE Version SET VersionId=13;
+COMMIT;
+
+END-OF-DATA
+DBVERSION=13
+fi
+
+if [ "$DBVERSION" = 13 ] ; then
+
+sqlite3 $* ${db_name}.db <<END-OF-DATA
+BEGIN;
 
-UPDATE Version SET VersionId=11;
+ALTER TABLE File ADD COLUMN DeltaSeq smallint default 0;
+UPDATE Version SET VersionId=14;
 
 COMMIT;
 
 END-OF-DATA
+
+DBVERSION=14
+
+fi
+
+if [ "$DBVERSION" = 14 ] ; then
+    sqlite3 $* ${db_name}.db  <<END-OF-DATA
+INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
+   ('I', 'Incomplete Job',25);
+ALTER TABLE Media ADD COLUMN VolABytes BIGINT UNSIGNED DEFAULT 0;
+ALTER TABLE Media ADD COLUMN VolAPadding BIGINT UNSIGNED DEFAULT 0;
+ALTER TABLE Media ADD COLUMN VolHoleBytes BIGINT UNSIGNED DEFAULT 0;
+ALTER TABLE Media ADD COLUMN VolHoles INTEGER UNSIGNED DEFAULT 0;
+ALTER TABLE Media CHANGE VolWrites VolWrites BIGINT UNSIGNED;
+
+CREATE TABLE Snapshot (
+  SnapshotId     INTEGER UNSIGNED AUTO_INCREMENT,
+  Name           TINYBLOB NOT NULL,
+  JobId          INTEGER UNSIGNED DEFAULT 0,
+  FileSetId INTEGER UNSIGNED DEFAULT 0;
+  CreateTDate    BIGINT   NOT NULL,
+  CreateDate     DATETIME NOT NULL,
+  ClientId       INTEGER DEFAULT 0,
+  Volume         TINYBLOB NOT NULL,
+  Device         TINYBLOB NOT NULL,
+  Type           TINYBLOB NOT NULL,
+  Retention      INTEGER DEFAULT 0,
+  Comment        BLOB,
+  primary key (SnapshotId)
+);
+
+CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device(255), Volume(255), Name(255));
+
+CREATE INDEX jobtdate_idx on JobHisto (JobTDate);
+
+UPDATE Version SET VersionId=15;
+END-OF-DATA
+DBVERSION=15
+fi
+
+END-OF-DATA