1#!/bin/sh
2#
3# Copyright (C) 2000-2015 Kern Sibbald
4# License: BSD 2-Clause; see file LICENSE-FOSS
5#
6# Shell script to update MySQL tables from version 2.0 to 3.0
7#
8echo " "
9echo "This script will update a Bacula MySQL database from version 10 to 11"
10echo " which is needed to convert from Bacula version 2.0.x to 3.0.x or higher"
11echo " "
12bindir=@MYSQL_BINDIR@
13PATH="$bindir:$PATH"
14db_name=${db_name:-@db_name@}
15
16if mysql $* -f <<END-OF-DATA
17USE ${db_name};
18
19-- Fix bad index on Media table
20DROP INDEX inx8 ON Media;
21CREATE UNIQUE INDEX inx8 ON Media (VolumeName(128));
22ALTER TABLE File CHANGE FileId FileId BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
23ALTER TABLE BaseFiles CHANGE FileId FileId BIGINT UNSIGNED NOT NULL;
24ALTER TABLE Job ADD ReadBytes BIGINT UNSIGNED DEFAULT 0 AFTER JobBytes; 
25ALTER TABLE Media ADD ActionOnPurge TINYINT DEFAULT 0 AFTER Recycle;
26ALTER TABLE Pool ADD ActionOnPurge TINYINT DEFAULT 0 AFTER Recycle;
27
28DELETE FROM Version;
29INSERT INTO Version (VersionId) VALUES (11);
30
31-- If you have already this table, you can remove it with:
32-- DROP TABLE JobHistory;
33
34-- Create a table like Job for long term statistics
35CREATE TABLE JobHisto (
36   JobId INTEGER UNSIGNED NOT NULL,
37   Job TINYBLOB NOT NULL,
38   Name TINYBLOB NOT NULL,
39   Type BINARY(1) NOT NULL,
40   Level BINARY(1) NOT NULL,
41   ClientId INTEGER UNSIGNED DEFAULT 0,
42   JobStatus BINARY(1) NOT NULL,
43   SchedTime DATETIME DEFAULT 0,
44   StartTime DATETIME DEFAULT 0,
45   EndTime DATETIME DEFAULT 0,
46   RealEndTime DATETIME DEFAULT 0,
47   JobTDate BIGINT UNSIGNED DEFAULT 0,
48   VolSessionId INTEGER UNSIGNED DEFAULT 0,
49   VolSessionTime INTEGER UNSIGNED DEFAULT 0,
50   JobFiles INTEGER UNSIGNED DEFAULT 0,
51   JobBytes BIGINT UNSIGNED DEFAULT 0,
52   ReadBytes BIGINT UNSIGNED DEFAULT 0,
53   JobErrors INTEGER UNSIGNED DEFAULT 0,
54   JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
55   PoolId INTEGER UNSIGNED DEFAULT 0,
56   FileSetId INTEGER UNSIGNED DEFAULT 0,
57   PriorJobId INTEGER UNSIGNED DEFAULT 0,
58   PurgedFiles TINYINT DEFAULT 0,
59   HasBase TINYINT DEFAULT 0,
60   INDEX (StartTime)
61   );
62
63END-OF-DATA
64then
65   echo "Update of Bacula MySQL tables succeeded."
66else
67   echo "Update of Bacula MySQL tables failed."
68fi
69exit 0
70