1#!/bin/sh
2#
3# Shell script to update MySQL tables from version 1.36 to 1.38
4#
5echo " "
6echo "This script will update a Bacula MySQL database from version 8 to 9"
7echo "Depending on the size of your database,"
8echo "this script may take several minutes to run."
9echo " "
10bindir=/usr/bin
11
12if $bindir/mysql $* -f <<END-OF-DATA
13USE bacula;
14
15ALTER TABLE Media ADD COLUMN LabelType INTEGER UNSIGNED NOT NULL DEFAULT 0;
16ALTER TABLE Media ADD COLUMN StorageId INTEGER UNSIGNED DEFAULT 0;
17ALTER TABLE Media ADD COLUMN VolParts INTEGER UNSIGNED NOT NULL DEFAULT 0;
18
19ALTER TABLE Pool  ADD COLUMN LabelType INTEGER UNSIGNED NOT NULL DEFAULT 0;
20ALTER TABLE Pool  ADD COLUMN NextPoolId INTEGER UNSIGNED DEFAULT 0;
21ALTER TABLE Pool  ADD COLUMN MigrationHighBytes BIGINT UNSIGNED DEFAULT 0;
22ALTER TABLE Pool  ADD COLUMN MigrationLowBytes BIGINT UNSIGNED DEFAULT 0;
23ALTER TABLE Pool  ADD COLUMN MigrationTime BIGINT UNSIGNED DEFAULT 0;
24
25ALTER TABLE JobMedia ADD COLUMN Copy INTEGER UNSIGNED NOT NULL DEFAULT 0;
26ALTER TABLE JobMedia ADD COLUMN Stripe INTEGER UNSIGNED NOT NULL DEFAULT 0;
27
28
29CREATE TABLE MediaType (
30   MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
31   MediaType VARCHAR(128) NOT NULL,
32   ReadOnly TINYINT DEFAULT 0,
33   PRIMARY KEY(MediaTypeId)
34   );
35
36CREATE TABLE Storage (
37   StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
38   Name VARCHAR(128) NOT NULL,
39   AutoChanger TINYINT DEFAULT 0,
40   PRIMARY KEY(StorageId)
41   );
42
43CREATE TABLE Device (
44   DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
45   Name VARCHAR(128) NOT NULL,
46   MediaTypeId INTEGER UNSIGNED NOT NULL,
47   StorageId INTEGER UNSIGNED,
48   DevMounts INTEGER UNSIGNED DEFAULT 0,
49   DevReadBytes BIGINT UNSIGNED DEFAULT 0,
50   DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
51   DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
52   DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
53   DevReadTime BIGINT UNSIGNED DEFAULT 0,
54   DevWriteTime BIGINT UNSIGNED DEFAULT 0,
55   DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
56   DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
57   CleaningDate DATETIME DEFAULT 0,
58   CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
59   PRIMARY KEY(DeviceId)
60   );
61
62CREATE TABLE Status (
63   JobStatus CHAR(1) BINARY NOT NULL,
64   JobStatusLong BLOB, 
65   PRIMARY KEY (JobStatus)
66   );
67
68INSERT INTO Status (JobStatus,JobStatusLong) VALUES
69   ('C', 'Created, not yet running'),
70   ('R', 'Running'),
71   ('B', 'Blocked'),
72   ('T', 'Completed successfully'),
73   ('E', 'Terminated with errors'),
74   ('e', 'Non-fatal error'),
75   ('f', 'Fatal error'),
76   ('D', 'Verify found differences'),
77   ('A', 'Canceled by user'),
78   ('F', 'Waiting for Client'),
79   ('S', 'Waiting for Storage daemon'),
80   ('m', 'Waiting for new media'),
81   ('M', 'Waiting for media mount'),
82   ('s', 'Waiting for storage resource'),
83   ('j', 'Waiting for job resource'),
84   ('c', 'Waiting for client resource'),
85   ('d', 'Waiting on maximum jobs'),
86   ('t', 'Waiting on start time'),
87   ('p', 'Waiting on higher priority jobs');
88
89
90DELETE FROM Version;
91INSERT INTO Version (VersionId) VALUES (9);
92
93END-OF-DATA
94then
95   echo "Update of Bacula MySQL tables succeeded."
96else
97   echo "Update of Bacula MySQL tables failed."
98fi
99exit 0
100