3 # shell script to update SQLite from version 1.22 to 1.23
6 echo "This script will update a bacula database from version 4 to 5."
7 echo "Depending on the size of your database,"
8 echo "this script may take several minutes to run."
11 # The location of the sqlite program
12 bindir=/usr/lib/sqlite
13 # The location of your bacula working directory
16 if [ -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 "4" ]; then
19 echo "Sorry, this script is designed to update a version 4 database"
20 echo "and you have a version $DB_VER database."
24 echo "Sorry, I can't seem to locate a bacula database."
28 $bindir/sqlite bacula.db <<END-OF-DATA
31 CREATE 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 VolMaxBytes BIGINT UNSIGNED DEFAULT 0,
48 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
49 VolStatus VARCHAR(20) NOT NULL,
50 Recycle TINYINT DEFAULT 0,
51 VolRetention BIGINT UNSIGNED DEFAULT 0,
54 INSERT INTO Media_backup SELECT * FROM Media;
57 MediaId INTEGER UNSIGNED AUTOINCREMENT,
58 VolumeName VARCHAR(128) NOT NULL,
59 Slot INTEGER DEFAULT 0,
60 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
61 MediaType VARCHAR(128) NOT NULL,
62 FirstWritten DATETIME DEFAULT 0,
63 LastWritten DATETIME DEFAULT 0,
64 LabelDate DATETIME DEFAULT 0,
65 VolJobs INTEGER UNSIGNED DEFAULT 0,
66 VolFiles INTEGER UNSIGNED DEFAULT 0,
67 VolBlocks INTEGER UNSIGNED DEFAULT 0,
68 VolMounts INTEGER UNSIGNED DEFAULT 0,
69 VolBytes BIGINT UNSIGNED DEFAULT 0,
70 VolErrors INTEGER UNSIGNED DEFAULT 0,
71 VolWrites INTEGER UNSIGNED DEFAULT 0,
72 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
73 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
74 VolStatus VARCHAR(20) NOT NULL,
75 Recycle TINYINT DEFAULT 0,
76 VolRetention BIGINT UNSIGNED DEFAULT 0,
77 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
78 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
79 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
83 MediaId,VolumeName,Slot,PoolId,MediaType,
84 FirstWritten,LastWritten,LabelDate,
85 VolJobs,VolFiles,VolBlocks,VolMounts,
86 VolBytes,VolErrors,VolWrites,MaxVolBytes,
87 VolCapacityBytes,VolStatus,Recycle,
89 SELECT * FROM Media_backup;
90 DROP TABLE Media_backup;
95 CREATE TEMPORARY TABLE Pool_backup (
96 PoolId INTEGER UNSIGNED AUTOINCREMENT,
97 Name VARCHAR(128) NOT NULL,
98 NumVols INTEGER UNSIGNED DEFAULT 0,
99 MaxVols INTEGER UNSIGNED DEFAULT 0,
100 UseOnce TINYINT DEFAULT 0,
101 UseCatalog TINYINT DEFAULT 1,
102 AcceptAnyVolume TINYINT DEFAULT 0,
103 VolRetention BIGINT UNSIGNED DEFAULT 0,
104 AutoPrune TINYINT DEFAULT 0,
105 Recycle TINYINT DEFAULT 0,
106 PoolType VARCHAR(20) NOT NULL,
107 LabelFormat VARCHAR(128) NOT NULL,
111 INSERT INTO Pool_backup SELECT * FROM Pool;
114 PoolId INTEGER UNSIGNED AUTOINCREMENT,
115 Name VARCHAR(128) NOT NULL,
116 NumVols INTEGER UNSIGNED DEFAULT 0,
117 MaxVols INTEGER UNSIGNED DEFAULT 0,
118 UseOnce TINYINT DEFAULT 0,
119 UseCatalog TINYINT DEFAULT 1,
120 AcceptAnyVolume TINYINT DEFAULT 0,
121 VolRetention BIGINT UNSIGNED DEFAULT 0,
122 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
123 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
124 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
125 MaxVolBytes INTEGER UNSIGNED DEFAULT 0,
126 AutoPrune TINYINT DEFAULT 0,
127 Recycle TINYINT DEFAULT 0,
128 PoolType VARCHAR(20) NOT NULL,
129 LabelFormat VARCHAR(128) NOT NULL,
135 PoolId,Name,NumVols,MaxVols,UseOnce,
136 UseCatalog,AcceptAnyVolume,VolRetention,
137 AutoPrune,Recycle,PoolType,LabelFormat
139 SELECT * FROM Pool_backup;
140 DROP TABLE Pool_backup;
143 UPDATE Version SET VersionId=5;