1#!/bin/sh
2#
3# shell script to update SQLite from version 1.34 to 1.35.5
4#
5echo " "
6echo "This script will update a Bacula SQLite database from version 7 to 8"
7echo "Depending on the size of your database,"
8echo "this script may take several minutes to run."
9echo " "
10
11bindir=/usr/bin
12
13# The location of your bacula working directory
14workdir=/var/lib/bacula
15
16
17cd $workdir
18
19if [ ! -r bacula.db -o ! -s bacula.db ];then
20       echo "Sorry, can't find a Bacula DB. Aborting."
21       exit 1
22fi
23
24DB_VER="`echo "select * from Version;" | $bindir/sqlite bacula.db | tail -1 2>/dev/null`"
25if [ -n "$DB_VER" ]; then
26
27       if [ "$DB_VER" = "8" ]; then
28               echo "The Catalog is already at version 8. Nothing to do!"
29               exit 0
30       elif [ "$DB_VER" -ne "7" ]; then
31               echo "Sorry, this script is designed to update a version 7 database"
32               echo "and you have a version $DB_VER database."
33               exit 1
34       fi
35
36else
37        echo "Sorry, I can't seem to locate a bacula database."
38        exit 1
39fi
40
41
42$bindir/sqlite $* bacula.db <<END_OF_DATA
43
44BEGIN TRANSACTION;
45CREATE TEMPORARY TABLE Media_backup (
46   MediaId INTEGER UNSIGNED AUTOINCREMENT,
47   VolumeName VARCHAR(128) NOT NULL,
48   Slot INTEGER DEFAULT 0,
49   PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
50   MediaType VARCHAR(128) NOT NULL,
51   FirstWritten DATETIME DEFAULT 0,
52   LastWritten DATETIME DEFAULT 0,
53   LabelDate DATETIME DEFAULT 0,
54   VolJobs INTEGER UNSIGNED DEFAULT 0,
55   VolFiles INTEGER UNSIGNED DEFAULT 0,
56   VolBlocks INTEGER UNSIGNED DEFAULT 0,
57   VolMounts INTEGER UNSIGNED DEFAULT 0,
58   VolBytes BIGINT UNSIGNED DEFAULT 0,
59   VolErrors INTEGER UNSIGNED DEFAULT 0,
60   VolWrites INTEGER UNSIGNED DEFAULT 0,
61   VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
62   VolStatus VARCHAR(20) NOT NULL,
63   Recycle TINYINT DEFAULT 0,
64   VolRetention BIGINT UNSIGNED DEFAULT 0,
65   VolUseDuration BIGINT UNSIGNED DEFAULT 0,
66   MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
67   MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
68   MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
69   InChanger TINYINT DEFAULT 0,
70   MediaAddressing TINYINT DEFAULT 0,
71   VolReadTime BIGINT UNSIGNED DEFAULT 0,
72   VolWriteTime BIGINT UNSIGNED DEFAULT 0,
73   EndFile INTEGER UNSIGNED DEFAULT 0,
74   EndBlock INTEGER UNSIGNED DEFAULT 0,
75   PRIMARY KEY(MediaId)
76   );
77
78INSERT INTO Media_backup SELECT 
79   MediaId, VolumeName, Slot, PoolId,
80   MediaType, FirstWritten, LastWritten,
81   LabelDate, VolJobs, VolFiles, VolBlocks,
82   VolMounts, VolBytes, VolErrors, VolWrites,
83   VolCapacityBytes, VolStatus, Recycle,
84   VolRetention, VolUseDuration, MaxVolJobs,
85   MaxVolFiles, MaxVolBytes, InChanger, MediaAddressing,
86   VolReadTime, VolWriteTime, 0, 0
87   FROM Media;
88
89
90DROP TABLE Media;
91
92CREATE TABLE Media (
93   MediaId INTEGER UNSIGNED AUTOINCREMENT,
94   VolumeName VARCHAR(128) NOT NULL,
95   Slot INTEGER DEFAULT 0,
96   PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
97   MediaType VARCHAR(128) NOT NULL,
98   FirstWritten DATETIME DEFAULT 0,
99   LastWritten DATETIME DEFAULT 0,
100   LabelDate DATETIME DEFAULT 0,
101   VolJobs INTEGER UNSIGNED DEFAULT 0,
102   VolFiles INTEGER UNSIGNED DEFAULT 0,
103   VolBlocks INTEGER UNSIGNED DEFAULT 0,
104   VolMounts INTEGER UNSIGNED DEFAULT 0,
105   VolBytes BIGINT UNSIGNED DEFAULT 0,
106   VolErrors INTEGER UNSIGNED DEFAULT 0,
107   VolWrites INTEGER UNSIGNED DEFAULT 0,
108   VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
109   VolStatus VARCHAR(20) NOT NULL,
110   Recycle TINYINT DEFAULT 0,
111   VolRetention BIGINT UNSIGNED DEFAULT 0,
112   VolUseDuration BIGINT UNSIGNED DEFAULT 0,
113   MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
114   MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
115   MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
116   InChanger TINYINT DEFAULT 0,
117   MediaAddressing TINYINT DEFAULT 0,
118   VolReadTime BIGINT UNSIGNED DEFAULT 0,
119   VolWriteTime BIGINT UNSIGNED DEFAULT 0,
120   EndFile INTEGER UNSIGNED DEFAULT 0,
121   EndBlock INTEGER UNSIGNED DEFAULT 0,
122   PRIMARY KEY(MediaId)
123   );
124
125INSERT INTO Media (
126   MediaId, VolumeName, Slot, PoolId,
127   MediaType, FirstWritten, LastWritten,
128   LabelDate, VolJobs, VolFiles, VolBlocks,
129   VolMounts, VolBytes, VolErrors, VolWrites,
130   VolCapacityBytes, VolStatus, Recycle,
131   VolRetention, VolUseDuration, MaxVolJobs,
132   MaxVolFiles, MaxVolBytes,
133   InChanger, MediaAddressing,
134   VolReadTime, VolWriteTime,	    
135   EndFile, EndBlock)
136   SELECT * FROM Media_backup;
137
138DROP TABLE Media_backup;
139
140CREATE INDEX inx9 ON File (JobId, PathId, FileNameId);
141
142COMMIT;
143
144UPDATE Filename SET Name='' WHERE Name=' ';
145
146DELETE FROM Version;
147INSERT INTO Version (VersionId) VALUES (8);
148
149END_OF_DATA
150