1#!/bin/sh
2#
3# shell script to update SQLite from version 1.32 to 1.33
4#
5echo " "
6echo "This script will update a bacula database from version 6 to 7."
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 "6" ]; then
19                echo "Sorry, this script is designed to update a version 6 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 Media_backup (
32   MediaId INTEGER UNSIGNED AUTOINCREMENT,
33   VolumeName VARCHAR(128) NOT NULL,
34   Slot INTEGER DEFAULT 0,
35   PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
36   MediaType VARCHAR(128) NOT NULL,
37   FirstWritten DATETIME DEFAULT 0,
38   LastWritten DATETIME DEFAULT 0,
39   LabelDate DATETIME DEFAULT 0,
40   VolJobs INTEGER UNSIGNED DEFAULT 0,
41   VolFiles INTEGER UNSIGNED DEFAULT 0,
42   VolBlocks INTEGER UNSIGNED DEFAULT 0,
43   VolMounts INTEGER UNSIGNED DEFAULT 0,
44   VolBytes BIGINT UNSIGNED DEFAULT 0,
45   VolErrors INTEGER UNSIGNED DEFAULT 0,
46   VolWrites INTEGER UNSIGNED DEFAULT 0,
47   VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
48   VolStatus VARCHAR(20) NOT NULL,
49   Recycle TINYINT DEFAULT 0,
50   VolRetention BIGINT UNSIGNED DEFAULT 0,
51   VolUseDuration BIGINT UNSIGNED DEFAULT 0,
52   MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
53   MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
54   MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
55   InChanger TINYINT DEFAULT 0,
56   MediaAddressing TINYINT DEFAULT 0,
57   VolReadTime BIGINT UNSIGNED DEFAULT 0,
58   VolWriteTime BIGINT UNSIGNED DEFAULT 0,
59   PRIMARY KEY(MediaId)
60   );
61
62INSERT INTO Media_backup SELECT 
63   MediaId, VolumeName, Slot, PoolId,
64   MediaType, FirstWritten, LastWritten,
65   LabelDate, VolJobs, VolFiles, VolBlocks,
66   VolMounts, VolBytes, VolErrors, VolWrites,
67   VolCapacityBytes, VolStatus, Recycle,
68   VolRetention, VolUseDuration, MaxVolJobs,
69   MaxVolFiles, MaxVolBytes, 0, 0, 0, 0
70   FROM Media;
71
72
73DROP TABLE Media;
74
75CREATE TABLE Media (
76   MediaId INTEGER UNSIGNED AUTOINCREMENT,
77   VolumeName VARCHAR(128) NOT NULL,
78   Slot INTEGER DEFAULT 0,
79   PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
80   MediaType VARCHAR(128) NOT NULL,
81   FirstWritten DATETIME DEFAULT 0,
82   LastWritten DATETIME DEFAULT 0,
83   LabelDate DATETIME DEFAULT 0,
84   VolJobs INTEGER UNSIGNED DEFAULT 0,
85   VolFiles INTEGER UNSIGNED DEFAULT 0,
86   VolBlocks INTEGER UNSIGNED DEFAULT 0,
87   VolMounts INTEGER UNSIGNED DEFAULT 0,
88   VolBytes BIGINT UNSIGNED DEFAULT 0,
89   VolErrors INTEGER UNSIGNED DEFAULT 0,
90   VolWrites INTEGER UNSIGNED DEFAULT 0,
91   VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
92   VolStatus VARCHAR(20) NOT NULL,
93   Recycle TINYINT DEFAULT 0,
94   VolRetention BIGINT UNSIGNED DEFAULT 0,
95   VolUseDuration BIGINT UNSIGNED DEFAULT 0,
96   MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
97   MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
98   MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
99   InChanger TINYINT DEFAULT 0,
100   MediaAddressing TINYINT DEFAULT 0,
101   VolReadTime BIGINT UNSIGNED DEFAULT 0,
102   VolWriteTime BIGINT UNSIGNED DEFAULT 0,
103   PRIMARY KEY(MediaId)
104   );
105
106INSERT INTO Media (
107   MediaId, VolumeName, Slot, PoolId,
108   MediaType, FirstWritten, LastWritten,
109   LabelDate, VolJobs, VolFiles, VolBlocks,
110   VolMounts, VolBytes, VolErrors, VolWrites,
111   VolCapacityBytes, VolStatus, Recycle,
112   VolRetention, VolUseDuration, MaxVolJobs,
113   MaxVolFiles, MaxVolBytes,
114   InChanger, MediaAddressing,
115   VolReadTime, VolWriteTime)
116   SELECT * FROM Media_backup;
117
118DROP TABLE Media_backup;
119
120CREATE INDEX inx8 ON Media (PoolId);
121
122CREATE TABLE Pool_backup (
123   PoolId INTEGER UNSIGNED AUTOINCREMENT,
124   Name VARCHAR(128) NOT NULL,
125   NumVols INTEGER UNSIGNED DEFAULT 0,
126   MaxVols INTEGER UNSIGNED DEFAULT 0,
127   UseOnce TINYINT DEFAULT 0,
128   UseCatalog TINYINT DEFAULT 1,
129   AcceptAnyVolume TINYINT DEFAULT 0,
130   VolRetention BIGINT UNSIGNED DEFAULT 0,
131   VolUseDuration BIGINT UNSIGNED DEFAULT 0,
132   MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
133   MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
134   MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
135   AutoPrune TINYINT DEFAULT 0,
136   Recycle TINYINT DEFAULT 0,
137   PoolType VARCHAR(20) NOT NULL,
138   LabelFormat VARCHAR(128) NOT NULL,
139   Enabled TINYINT DEFAULT 1,
140   ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
141   RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
142   UNIQUE (Name),
143   PRIMARY KEY (PoolId)
144   );
145
146INSERT INTO Pool_backup SELECT
147   PoolId,
148   Name,
149   NumVols,
150   MaxVols,
151   UseOnce,
152   UseCatalog,
153   AcceptAnyVolume,
154   VolRetention,
155   VolUseDuration,
156   MaxVolJobs,
157   MaxVolFiles,
158   MaxVolBytes,
159   AutoPrune,
160   Recycle,
161   PoolType,
162   LabelFormat, 1, 0, 0
163   FROM Pool;
164
165DROP TABLE Pool;
166
167CREATE TABLE Pool (
168   PoolId INTEGER UNSIGNED AUTOINCREMENT,
169   Name VARCHAR(128) NOT NULL,
170   NumVols INTEGER UNSIGNED DEFAULT 0,
171   MaxVols INTEGER UNSIGNED DEFAULT 0,
172   UseOnce TINYINT DEFAULT 0,
173   UseCatalog TINYINT DEFAULT 1,
174   AcceptAnyVolume TINYINT DEFAULT 0,
175   VolRetention BIGINT UNSIGNED DEFAULT 0,
176   VolUseDuration BIGINT UNSIGNED DEFAULT 0,
177   MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
178   MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
179   MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
180   AutoPrune TINYINT DEFAULT 0,
181   Recycle TINYINT DEFAULT 0,
182   PoolType VARCHAR(20) NOT NULL,
183   LabelFormat VARCHAR(128) NOT NULL,
184   Enabled TINYINT DEFAULT 1,
185   ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
186   RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
187   UNIQUE (Name),
188   PRIMARY KEY (PoolId)
189   );
190
191INSERT INTO Pool (
192   PoolId,
193   Name,
194   NumVols,
195   MaxVols,
196   UseOnce,
197   UseCatalog,
198   AcceptAnyVolume,
199   VolRetention,
200   VolUseDuration,
201   MaxVolJobs,
202   MaxVolFiles,
203   MaxVolBytes,
204   AutoPrune,
205   Recycle,
206   PoolType,
207   LabelFormat, Enabled, ScratchPoolId, RecyclePoolId)
208   SELECT * FROM Pool_backup;
209
210DROP TABLE Pool_backup;
211
212
213DROP TABLE BaseFiles;
214
215CREATE TABLE BaseFiles (
216   BaseId INTEGER UNSIGNED AUTOINCREMENT,
217   BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
218   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
219   FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
220   FileIndex INTEGER UNSIGNED,
221   PRIMARY KEY(BaseId)
222   );
223
224COMMIT;
225
226DELETE FROM Version;
227INSERT INTO Version (VersionId) VALUES (7);
228
229END-OF-DATA
230