1#!/bin/sh
2#
3# shell script to update SQLite from version 1.29 to 1.30
4#
5echo " "
6echo "This script will update a bacula database from version 5 to 6."
7echo "Depending on the size of your database,"
8echo "this script may take several minutes to run."
9echo " "
10
11# The location of the sqlite program
12bindir=/usr/lib/sqlite
13# The location of your bacula working directory
14cd /var/bacula
15
16if [ -s bacula.db ];then
17	DB_VER=`echo "select * from Version;" | $bindir/sqlite bacula.db | tail -n 1 2>/dev/null`
18	if [ -n "$DB_VER" ] && [ "$DB_VER" -ne "5" ]; then
19		echo "Sorry, this script is designed to update a version 5 database"
20		echo "and you have a version $DB_VER database."
21		exit 1
22	fi
23else
24	echo "Sorry, I can't seem to locate a bacula database."
25	exit 1
26fi
27
28$bindir/sqlite $* bacula.db <<END-OF-DATA
29
30BEGIN TRANSACTION;
31CREATE TEMPORARY TABLE FileSet_backup (
32   FileSetId INTEGER UNSIGNED AUTOINCREMENT,
33   FileSet VARCHAR(128) NOT NULL,
34   MD5 VARCHAR(25) NOT NULL,
35   PRIMARY KEY(FileSetId)
36   );
37INSERT INTO FileSet_backup SELECT * From FileSet;
38
39DROP TABLE FileSet;
40CREATE TABLE FileSet (
41   FileSetId INTEGER UNSIGNED AUTOINCREMENT,
42   FileSet VARCHAR(128) NOT NULL,
43   MD5 VARCHAR(25) NOT NULL,
44   CreateTime DATETIME DEFAULT 0,
45   PRIMARY KEY(FileSetId)
46   );
47INSERT INTO FileSet (
48   FileSetId, FileSet, MD5, CreateTime) 
49   SELECT FileSet_backup.FileSetId,FileSet,MD5,StartTime FROM FileSet_backup,Job
50     WHERE FileSet_backup.FileSetId=Job.FileSetId GROUP by FileSet_backup.FileSetId
51     ORDER BY Job.FileSetId;
52
53DROP TABLE FileSet_backup;
54COMMIT;
55
56
57BEGIN TRANSACTION;
58CREATE TEMPORARY TABLE Job_backup (
59   JobId INTEGER UNSIGNED NOT NULL,
60   Job VARCHAR(128) NOT NULL,
61   Name VARCHAR(128) NOT NULL,
62   PurgedFiles TINYINT DEFAULT 0,
63   Type CHAR NOT NULL,
64   Level CHAR NOT NULL,
65   ClientId INTEGER REFERENCES Client DEFAULT 0,
66   JobStatus CHAR NOT NULL,
67   SchedTime DATETIME NOT NULL,
68   StartTime DATETIME DEFAULT 0,
69   EndTime DATETIME DEFAULT 0,
70   JobTDate BIGINT UNSIGNED DEFAULT 0,
71   VolSessionId INTEGER UNSIGNED DEFAULT 0,
72   VolSessionTime INTEGER UNSIGNED DEFAULT 0,
73   JobFiles INTEGER UNSIGNED DEFAULT 0,
74   JobBytes BIGINT UNSIGNED DEFAULT 0,
75   JobErrors INTEGER UNSIGNED DEFAULT 0,
76   JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
77   PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
78   FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0 
79   );
80INSERT INTO Job_backup SELECT * From Job;
81
82DROP TABLE Job;
83CREATE TABLE Job (
84   JobId INTEGER UNSIGNED NOT NULL,
85   Job VARCHAR(128) NOT NULL,
86   Name VARCHAR(128) NOT NULL,
87   PurgedFiles TINYINT DEFAULT 0,
88   Type CHAR NOT NULL,
89   Level CHAR NOT NULL,
90   ClientId INTEGER REFERENCES Client DEFAULT 0,
91   JobStatus CHAR NOT NULL,
92   SchedTime DATETIME NOT NULL,
93   StartTime DATETIME DEFAULT 0,
94   EndTime DATETIME DEFAULT 0,
95   JobTDate BIGINT UNSIGNED DEFAULT 0,
96   VolSessionId INTEGER UNSIGNED DEFAULT 0,
97   VolSessionTime INTEGER UNSIGNED DEFAULT 0,
98   JobFiles INTEGER UNSIGNED DEFAULT 0,
99   JobBytes BIGINT UNSIGNED DEFAULT 0,
100   JobErrors INTEGER UNSIGNED DEFAULT 0,
101   JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
102   PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
103   FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
104   HasBase TINYINT DEFAULT 0,
105   PRIMARY KEY(JobId) 
106   );
107INSERT INTO Job (JobId,Job,Name,PurgedFiles,
108   Type,Level,ClientId,JobStatus,SchedTime,
109   StartTime,EndTime,JobTDate,VolSessionId,
110   VolSessionTime,JobFiles,JobBytes,JobErrors,
111   JobMissingFiles,PoolId,FileSetId)						  
112   SELECT * FROM Job_backup;
113DROP TABLE Job_backup;
114COMMIT;
115
116
117BEGIN TRANSACTION;
118CREATE TEMPORARY TABLE JobMedia_backup (
119   JobMediaId INTEGER UNSIGNED AUTOINCREMENT,
120   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
121   MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
122   FirstIndex INTEGER UNSIGNED NOT NULL,
123   LastIndex INTEGER UNSIGNED NOT NULL,
124   StartFile INTEGER UNSIGNED DEFAULT 0,
125   EndFile INTEGER UNSIGNED DEFAULT 0,
126   StartBlock INTEGER UNSIGNED DEFAULT 0,
127   EndBlock INTEGER UNSIGNED DEFAULT 0,
128   PRIMARY KEY(JobMediaId) 
129   );
130INSERT INTO JobMedia_backup SELECT * From JobMedia;
131
132DROP TABLE JobMedia;
133CREATE TABLE JobMedia (
134   JobMediaId INTEGER UNSIGNED AUTOINCREMENT,
135   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
136   MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
137   FirstIndex INTEGER UNSIGNED NOT NULL,
138   LastIndex INTEGER UNSIGNED NOT NULL,
139   StartFile INTEGER UNSIGNED DEFAULT 0,
140   EndFile INTEGER UNSIGNED DEFAULT 0,
141   StartBlock INTEGER UNSIGNED DEFAULT 0,
142   EndBlock INTEGER UNSIGNED DEFAULT 0,
143   VolIndex INTEGER UNSIGNED DEFAULT 0,
144   PRIMARY KEY(JobMediaId) 
145   );
146INSERT INTO JobMedia (
147   JobMediaId,JobId,MediaId,FirstIndex,
148   LastIndex,StartFile,EndFile,StartBlock,
149   EndBlock)
150   SELECT * FROM JobMedia_backup;
151DROP TABLE JobMedia_backup;
152COMMIT;
153
154CREATE TABLE BaseFiles (
155   BaseId INTEGER UNSIGNED AUTOINCREMENT,
156   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
157   FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
158   FileIndex INTEGER UNSIGNED,
159   PRIMARY KEY(BaseId)
160   );
161
162DROP TABLE Counters;
163
164CREATE TABLE Counters (
165   Counter TEXT NOT NULL,     
166   MinValue INTEGER DEFAULT 0, 
167   MaxValue INTEGER DEFAULT 0,
168   CurrentValue INTEGER DEFAULT 0,
169   WrapCounter TEXT NOT NULL,
170   PRIMARY KEY (Counter)
171   );
172
173UPDATE Version SET VersionId=6;
174
175END-OF-DATA
176