ebl Add '*' when volume is online when displaying volume list in restore.

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

diff --git a/bacula/src/cats/update_mysql_tables.in b/bacula/src/cats/update_mysql_tables.in
old mode 100755 (executable)
new mode 100644 (file)
index 6fcafa7..e78a135
--- a/bacula/src/cats/update_mysql_tables.in
+++ b/bacula/src/cats/update_mysql_tables.in
@@ -1,95 +1,61 @@
 #!/bin/sh
 #
-# Shell script to update MySQL tables from version 1.36 to 1.38  
+# Shell script to update MySQL tables from version 2.0 to 3.0  
 #
 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 "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 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;
+-- 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 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;
-
-ALTER TABLE JobMedia ADD COLUMN Copy INTEGER UNSIGNED NOT NULL DEFAULT 0;
-ALTER TABLE JobMedia ADD COLUMN Stripe INTEGER UNSIGNED NOT NULL DEFAULT 0;
-
-
-CREATE TABLE MediaType (
-   MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-   MediaType VARCHAR(128) NOT NULL,
-   ReadOnly TINYINT DEFAULT 0,
-   PRIMARY KEY(MediaTypeId)
-   );
-
-CREATE TABLE Storage (
-   StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-   Name VARCHAR(128) NOT NULL,
-   AutoChanger TINYINT DEFAULT 0,
-   PRIMARY KEY(StorageId)
-   );
+DELETE FROM Version;
+INSERT INTO Version (VersionId) VALUES (11);
 
-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)
-   );
+-- If you have already this table, you can remove it with:
+-- DROP TABLE JobHistory;
 
-CREATE TABLE Status (
-   JobStatus CHAR(1) BINARY NOT NULL,
-   JobStatusLong BLOB, 
-   PRIMARY KEY (JobStatus)
+-- 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)
    );
 
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('C', 'Created, not yet running'),
-   ('R', 'Running'),
-   ('B', 'Blocked'),
-   ('T', 'Completed successfully'),
-   ('E', 'Terminated with errors'),
-   ('e', 'Non-fatal error'),
-   ('f', 'Fatal error'),
-   ('D', 'Verify found differences'),
-   ('A', 'Canceled by user'),
-   ('F', 'Waiting for Client'),
-   ('S', 'Waiting for Storage daemon'),
-   ('m', 'Waiting for new media'),
-   ('M', 'Waiting for media mount'),
-   ('s', 'Waiting for storage resource'),
-   ('j', 'Waiting for job resource'),
-   ('c', 'Waiting for client resource'),
-   ('d', 'Waiting on maximum jobs'),
-   ('t', 'Waiting on start time'),
-   ('p', 'Waiting on higher priority jobs');
-
-
-DELETE FROM Version;
-INSERT INTO Version (VersionId) VALUES (9);
-
 END-OF-DATA
 then
    echo "Update of Bacula MySQL tables succeeded."