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