3 # shell script to update SQLite from version 1.29 to 1.30
6 echo "This script will update a bacula database from version 5 to 6."
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 "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."
24 echo "Sorry, I can't seem to locate a bacula database."
28 $bindir/sqlite $* bacula.db <<END-OF-DATA
31 CREATE 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)
37 INSERT INTO FileSet_backup SELECT * From FileSet;
40 CREATE 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)
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;
53 DROP TABLE FileSet_backup;
58 CREATE 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,
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
80 INSERT INTO Job_backup SELECT * From Job;
84 JobId INTEGER UNSIGNED NOT NULL,
85 Job VARCHAR(128) NOT NULL,
86 Name VARCHAR(128) NOT NULL,
87 PurgedFiles TINYINT DEFAULT 0,
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,
107 INSERT 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;
113 DROP TABLE Job_backup;
118 CREATE 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)
130 INSERT INTO JobMedia_backup SELECT * From JobMedia;
133 CREATE 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)
146 INSERT INTO JobMedia (
147 JobMediaId,JobId,MediaId,FirstIndex,
148 LastIndex,StartFile,EndFile,StartBlock,
150 SELECT * FROM JobMedia_backup;
151 DROP TABLE JobMedia_backup;
154 CREATE 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,
164 CREATE 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)
173 UPDATE Version SET VersionId=6;