#!/bin/sh
#
-# Shell script to update MySQL tables from version 1.32 to 1.33
+# Shell script to update MySQL tables from version 1.36 to 1.37.3
#
echo " "
+echo "This script will update a Bacula MySQL database from version 8 to 9"
echo "Depending on the size of your database,"
echo "this script may take several minutes to run."
echo " "
if $bindir/mysql $* -f <<END-OF-DATA
USE bacula;
-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;
+ALTER TABLE Media ADD COLUMN LabelType INTEGER UNSIGNED NOT NULL DEFAULT 0;
+ALTER TABLE Media ADD COLUMN StorageId INTEGER UNSIGNED DEFAULT 0 REFERENCES Storage;
+ALTER TABLE Media ADD COLUMN VolParts INTEGER UNSIGNED NOT NULL DEFAULT 0;
-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;
+ALTER TABLE Pool ADD COLUMN LabelType INTEGER UNSIGNED NOT NULL DEFAULT 0;
+ALTER TABLE Pool ADD COLUMN NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool;
+ALTER TABLE Pool ADD COLUMN MigrationHighBytes BIGINT UNSIGNED DEFAULT 0;
+ALTER TABLE Pool ADD COLUMN MigrationLowBytes BIGINT UNSIGNED DEFAULT 0;
+ALTER TABLE Pool ADD COLUMN MigrationTime BIGINT UNSIGNED DEFAULT 0;
-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)
+CREATE TABLE MediaType (
+ MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+ MediaType VARCHAR(128) NOT NULL,
+ ReadOnly TINYINT DEFAULT 0,
+ PRIMARY KEY(MediaTypeId)
);
-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)
+CREATE TABLE Storage (
+ StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+ Name VARCHAR(128) NOT NULL,
+ AutoChanger TINYINT DEFAULT 0,
+ PRIMARY KEY(StorageId)
);
-DROP TABLE BaseFiles;
-
-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 TABLE Device (
+ DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+ Name VARCHAR(128) NOT NULL,
+ MediaTypeId INTEGER UNSIGNED NOT NULL REFERENCES MediaType,
+ 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)
);
-DELETE FROM Version;
-INSERT INTO Version (VersionId) VALUES (7);
-
END-OF-DATA
then
echo "Update of Bacula MySQL tables succeeded."