3 # Copyright (C) 2000-2015 Kern Sibbald
4 # License: BSD 2-Clause; see file LICENSE-FOSS
6 # shell script to update SQLite from version 2.0 to 3.0
9 echo "This script will update a Bacula SQLite database from version 10 to 11"
10 echo " which is needed to convert from Bacula version 2.0.x to 3.0.x or higher"
11 echo "Depending on the size of your database,"
12 echo "this script may take several minutes to run."
15 bindir=@SQLITE_BINDIR@
20 sqlite3 $* ${db_name}.db <<END-OF-DATA
22 -- ALTER TABLE Job ADD COLUMN (ReadBytes BIGINT UNSIGNED DEFAULT 0);
25 CREATE TEMPORARY TABLE job_backup AS SELECT * FROM Job;
31 Job VARCHAR(128) NOT NULL,
32 Name VARCHAR(128) NOT NULL,
35 ClientId INTEGER REFERENCES Client DEFAULT 0,
36 JobStatus CHAR NOT NULL,
37 SchedTime DATETIME NOT NULL,
38 StartTime DATETIME DEFAULT 0,
39 EndTime DATETIME DEFAULT 0,
40 RealEndTime DATETIME DEFAULT 0,
41 JobTDate BIGINT UNSIGNED DEFAULT 0,
42 VolSessionId INTEGER UNSIGNED DEFAULT 0,
43 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
44 JobFiles INTEGER UNSIGNED DEFAULT 0,
45 JobBytes BIGINT UNSIGNED DEFAULT 0,
46 ReadBytes BIGINT UNSIGNED DEFAULT 0,
47 JobErrors INTEGER UNSIGNED DEFAULT 0,
48 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
49 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
50 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
51 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
52 PurgedFiles TINYINT DEFAULT 0,
53 HasBase TINYINT DEFAULT 0,
56 CREATE INDEX inx6 ON Job (Name);
58 INSERT INTO Job (JobId, Job, Name, Type, Level, ClientId, JobStatus,
59 SchedTime, StartTime, EndTime, RealEndTime, JobTDate, VolSessionId,
60 VolSessionTime, JobFiles, JobBytes, JobErrors, JobMissingFiles,
61 PoolId, FileSetId, PriorJobId, PurgedFiles, HasBase) SELECT
62 JobId, Job, Name, Type, Level, ClientId, JobStatus, SchedTime, StartTime,
63 EndTime, RealEndTime, JobTDate, VolSessionId, VolSessionTime, JobFiles,
64 JobBytes, JobErrors, JobMissingFiles, PoolId, FileSetId, PriorJobId,
65 PurgedFiles, HasBase FROM Job_backup;
67 DROP TABLE Job_backup;
70 -- ----------------------------------------------------------------
71 -- New ActionOnPurge field
73 CREATE TEMPORARY TABLE pool_backup AS SELECT * FROM Pool;
78 Name VARCHAR(128) NOT NULL,
79 NumVols INTEGER UNSIGNED DEFAULT 0,
80 MaxVols INTEGER UNSIGNED DEFAULT 0,
81 UseOnce TINYINT DEFAULT 0,
82 UseCatalog TINYINT DEFAULT 1,
83 AcceptAnyVolume TINYINT DEFAULT 0,
84 VolRetention BIGINT UNSIGNED DEFAULT 0,
85 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
86 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
87 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
88 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
89 AutoPrune TINYINT DEFAULT 0,
90 Recycle TINYINT DEFAULT 0,
91 ActionOnPurge TINYINT DEFAULT 0,
92 PoolType VARCHAR(20) NOT NULL,
93 LabelType TINYINT DEFAULT 0,
94 LabelFormat VARCHAR(128) NOT NULL,
95 Enabled TINYINT DEFAULT 1,
96 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
97 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
98 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
99 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
100 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
101 MigrationTime BIGINT UNSIGNED DEFAULT 0,
106 INSERT INTO Pool (PoolId, Name, NumVols, MaxVols, UseOnce, UseCatalog,
107 AcceptAnyVolume, VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles,
108 MaxVolBytes, AutoPrune, Recycle, PoolType, LabelType,
109 LabelFormat, Enabled, ScratchPoolId, RecyclePoolId, NextPoolId,
110 MigrationHighBytes, MigrationLowBytes, MigrationTime)
111 SELECT PoolId, Name, NumVols, MaxVols, UseOnce, UseCatalog, AcceptAnyVolume,
112 VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, AutoPrune,
113 Recycle, PoolType, LabelType, LabelFormat, Enabled,
114 ScratchPoolId, RecyclePoolId, NextPoolId, MigrationHighBytes,
115 MigrationLowBytes, MigrationTime FROM pool_backup;
117 DROP TABLE pool_backup;
119 -- ----------------------------------------------------------------
120 -- New ActionOnPurge field
122 CREATE TEMPORARY TABLE media_backup AS SELECT * FROM Media;
127 VolumeName VARCHAR(128) NOT NULL,
128 Slot INTEGER DEFAULT 0,
129 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
130 MediaType VARCHAR(128) NOT NULL,
131 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
132 LabelType TINYINT DEFAULT 0,
133 FirstWritten DATETIME DEFAULT 0,
134 LastWritten DATETIME DEFAULT 0,
135 LabelDate DATETIME DEFAULT 0,
136 VolJobs INTEGER UNSIGNED DEFAULT 0,
137 VolFiles INTEGER UNSIGNED DEFAULT 0,
138 VolBlocks INTEGER UNSIGNED DEFAULT 0,
139 VolMounts INTEGER UNSIGNED DEFAULT 0,
140 VolBytes BIGINT UNSIGNED DEFAULT 0,
141 VolParts INTEGER UNSIGNED DEFAULT 0,
142 VolErrors INTEGER UNSIGNED DEFAULT 0,
143 VolWrites INTEGER UNSIGNED DEFAULT 0,
144 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
145 VolStatus VARCHAR(20) NOT NULL,
146 Enabled TINYINT DEFAULT 1,
147 Recycle TINYINT DEFAULT 0,
148 ActionOnPurge TINYINT DEFAULT 0,
149 VolRetention BIGINT UNSIGNED DEFAULT 0,
150 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
151 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
152 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
153 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
154 InChanger TINYINT DEFAULT 0,
155 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
156 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
157 MediaAddressing TINYINT DEFAULT 0,
158 VolReadTime BIGINT UNSIGNED DEFAULT 0,
159 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
160 EndFile INTEGER UNSIGNED DEFAULT 0,
161 EndBlock INTEGER UNSIGNED DEFAULT 0,
162 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
163 RecycleCount INTEGER UNSIGNED DEFAULT 0,
164 InitialWrite DATETIME DEFAULT 0,
165 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
166 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
171 CREATE INDEX inx8 ON Media (PoolId);
174 MediaId, VolumeName, Slot, PoolId, MediaType, MediaTypeId,
175 LabelType, FirstWritten, LastWritten, LabelDate, VolJobs,
176 VolFiles, VolBlocks, VolMounts, VolBytes, VolParts, VolErrors,
177 VolWrites, VolCapacityBytes, VolStatus, Enabled, Recycle,
178 VolRetention, VolUseDuration, MaxVolJobs,
179 MaxVolFiles, MaxVolBytes, InChanger, StorageId, DeviceId,
180 MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock,
181 LocationId, RecycleCount, InitialWrite, ScratchPoolId,
182 RecyclePoolId, Comment)
183 SELECT MediaId, VolumeName, Slot, PoolId, MediaType, MediaTypeId,
184 LabelType, FirstWritten, LastWritten, LabelDate, VolJobs,
185 VolFiles, VolBlocks, VolMounts, VolBytes, VolParts, VolErrors,
186 VolWrites, VolCapacityBytes, VolStatus, Enabled, Recycle,
187 VolRetention, VolUseDuration, MaxVolJobs,
188 MaxVolFiles, MaxVolBytes, InChanger, StorageId, DeviceId,
189 MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock,
190 LocationId, RecycleCount, InitialWrite, ScratchPoolId,
191 RecyclePoolId, Comment FROM media_backup;
193 DROP TABLE media_backup;
195 UPDATE Version SET VersionId=11;
198 -- If you have already this table, you can remove it with:
199 -- DROP TABLE JobHistory;
201 -- Create a table like Job for long term statistics
202 CREATE TABLE JobHisto (
204 Job VARCHAR(128) NOT NULL,
205 Name VARCHAR(128) NOT NULL,
208 ClientId INTEGER REFERENCES Client DEFAULT 0,
209 JobStatus CHAR NOT NULL,
210 SchedTime DATETIME NOT NULL,
211 StartTime DATETIME DEFAULT 0,
212 EndTime DATETIME DEFAULT 0,
213 RealEndTime DATETIME DEFAULT 0,
214 JobTDate BIGINT UNSIGNED DEFAULT 0,
215 VolSessionId INTEGER UNSIGNED DEFAULT 0,
216 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
217 JobFiles INTEGER UNSIGNED DEFAULT 0,
218 JobBytes BIGINT UNSIGNED DEFAULT 0,
219 ReadBytes BIGINT UNSIGNED DEFAULT 0,
220 JobErrors INTEGER UNSIGNED DEFAULT 0,
221 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
222 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
223 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
224 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
225 PurgedFiles TINYINT DEFAULT 0,
226 HasBase TINYINT DEFAULT 0
228 CREATE INDEX inx61 ON JobHisto (StartTime);