#!/bin/sh
#
-# Shell script to update MySQL tables from version 1.32 to 1.33
+# Shell script to update MySQL tables from version 2.0 to 3.0
#
echo " "
-echo "Depending on the size of your database,"
-echo "this script may take several minutes to run."
+echo "This script will update a Bacula MySQL 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 " "
bindir=@SQL_BINDIR@
+db_name=@db_name@
if $bindir/mysql $* -f <<END-OF-DATA
-USE bacula;
+USE ${db_name};
-ALTER TABLE Media ADD COLUMN InChanger TINYINT NOT NULL DEFAULT 0;
-ALTER TABLE Media ADD COLUMN MediaAddressing TINYINT NOT NULL DEFAULT 0;
-ALTER TABLE Media ADD COLUMN VolReadTime BIGINT UNSIGNED NOT NULL DEFAULT 0;
-ALTER TABLE Media ADD COLUMN VolWriteTime BIGINT UNSIGNED NOT NULL DEFAULT 0;
+-- Fix bad index on Media table
+DROP INDEX inx8 ON Media;
+CREATE UNIQUE INDEX inx8 ON Media (VolumeName(128));
+ALTER TABLE File CHANGE FileId FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
+ALTER TABLE BaseFiles CHANGE FileId FileId BIGINT UNSIGNED NOT NULL;
+ALTER TABLE Job ADD ReadBytes BIGINT UNSIGNED DEFAULT 0 AFTER JobBytes;
+ALTER TABLE Media ADD ActionOnPurge TINYINT DEFAULT 0 AFTER Recycle;
+ALTER TABLE Pool ADD ActionOnPurge TINYINT DEFAULT 0 AFTER Recycle;
-ALTER TABLE Pool ADD COLUMN Enabled TINYINT DEFAULT 1;
-ALTER TABLE Pool ADD COLUMN ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool;
-ALTER TABLE Pool ADD COLUMN RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool;
-
-DROP TABLE BaseFiles;
-
-
-CREATE TABLE BaseFiles (
- BaseId INTEGER UNSIGNED AUTO_INCREMENT,
- JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
- FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
- FileIndex INTEGER UNSIGNED,
- PRIMARY KEY(BaseId)
- );
-
-DROP TABLE UnsavedFiles;
-
-CREATE TABLE UnsavedFiles (
- UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
- JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
- PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
- FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
- PRIMARY KEY (UnsavedId)
- );
+DELETE FROM Version;
+INSERT INTO Version (VersionId) VALUES (11);
-DROP TABLE BaseFiles;
+-- If you have already this table, you can remove it with:
+-- DROP TABLE JobHistory;
-CREATE TABLE BaseFiles (
- BaseId INTEGER UNSIGNED AUTO_INCREMENT,
- BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
- JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
- FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
- FileIndex INTEGER UNSIGNED,
- PRIMARY KEY(BaseId)
+-- Create a table like Job for long term statistics
+CREATE TABLE JobHisto (
+ JobId INTEGER UNSIGNED NOT NULL,
+ Job TINYBLOB NOT NULL,
+ Name TINYBLOB NOT NULL,
+ Type BINARY(1) NOT NULL,
+ Level BINARY(1) NOT NULL,
+ ClientId INTEGER DEFAULT 0,
+ JobStatus BINARY(1) NOT NULL,
+ SchedTime DATETIME DEFAULT 0,
+ 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,
+ INDEX (StartTime)
);
-DELETE FROM Version;
-INSERT INTO VERSION (VersionId) VALUES (7);
-
END-OF-DATA
then
echo "Update of Bacula MySQL tables succeeded."